【Oracle】RAC的多实例数据迁移至单机的多实例。

时间：2023-01-17 14:49:46

思路:一般的思路可以通过RMAN进行数据的恢复。由于数据库可以停机，因此，这次试用数据泵(expdp，impdp)进行数据

的导入导出。

1.源数据库导出

通过编写导出shell脚本导出数据，如下:

expdp_mangedb_full.sh的脚本:

[oracle@arprac02 scripts]$ cat expdp_mangedb_full.sh

#!/bin/bash

source ~/.bash_profile

export ORACLE_SID=managedb2

export DATE=$(date +%Y%m%d%H)

expdp system/oracle directory=expbk logfile=managedb76_full_$DATE.log dumpfile=managedb76_full_$DATE.dmp full=y

expdp_servdb_full.sh的脚本:

[oracle@arprac02 scripts]$ cat expdp_servdb_full.sh

#!/bin/bash

source ~/.bash_profile

export ORACLE_SID=servdb2

export DATE=$(date +%Y%m%d%H)

expdp system/oracle directory=expbk logfile=servdb76_full_$DATE.log dumpfile=servdb76_full_$DATE.dmp full=y

以上，导出多实例的全库数据。

2.数据导入

确定导入指定的数据库实例，这里实践的是servdb数据库实例.全库的导入:它会导入表空间的建表过程，建立用户的过程，建立角色，建立同义词，建立表，视图，存储过程，dblink，job等等所有该有的东西。所以，如果导入过程中有报错，可以分析问题，逐一解决。

赋权建立导入导出备份目录

[oracle@db u01]$ mkdir dmpbackup

数据库中，赋权目录

export ORACLE_SID=servdb

SQL> select INSTANCE_NAME from v$instance;

SQL>  create directory expbk as '/u01/dmpbackup';

SQL>  grant read,write on directory expbk to system;

导入的dmp文件权限放开

[root@db ~]# chmod -R 777 /u01/dmpbackup/servdb76_full_2020031115.dmp

导入数据

export ORACLE_SID=servdb

impdp system/oracle directory=expbk dumpfile=servdb76_full_2020031322.dmp logfile=servdb76_full_20200313.log EXCLUDE=SCHEMA:\"IN \(\'SYSMAN\', \'SYSTEM\'\)\" EXCLUDE=STATISTICS

这里会有一个报错，log如下:

Failing sql is:

CREATE UNDO TABLESPACE "UNDOTBS2" DATAFILE '+DATA/servdb/datafile/undotbs2.280.826877779' SIZE 209715200 AUTOEXTEND ON NEXT 5242880 MAXSIZE 32767M BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE

ORA-31684: Object type TABLESPACE:"USERS" already exists

ORA-39083: Object type TABLESPACE failed to create with error:

ORA-01276: Cannot add file +DATA/servdb/datafile/no1task.329.827069307.  File has an Oracle Managed Files file name.

Failing sql is:

CREATE TABLESPACE "NO1TASK" DATAFILE '+DATA/servdb/datafile/no1task.329.827069307' SIZE 4194304000 AUTOEXTEND ON NEXT 268435456 MAXSIZE 32767M,'+DATA/servdb/datafile/no1task.6801.910947551' SIZE 32212254720 AUTOEXTEND ON NEXT 536870912

MAXSIZE 32767M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL

ORA-39083: Object type TABLESPACE failed to create with error:

ORA-01276: Cannot add file +DATA/servdb/datafile/mobileapp.397.827404615.  File has an Oracle Managed Files file name.

Failing sql is:

CREATE TABLESPACE "MOBILEAPP" DATAFILE '+DATA/servdb/datafile/mobileapp.397.827404615' SIZE 1048576000 AUTOEXTEND ON NEXT 134217728 MAXSIZE 32767M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPAC

E MANAGEMENT AUTO

ORA-39083: Object type TABLESPACE failed to create with error:

ORA-01276: Cannot add file +DATA/servdb/datafile/otf_platform.3693.842997461.  File has an Oracle Managed Files file name.

Failing sql is:

CREATE TABLESPACE "OTF_PLATFORM" DATAFILE '+DATA/servdb/datafile/otf_platform.3693.842997461' SIZE 2097152000 AUTOEXTEND ON NEXT 134217728 MAXSIZE 32767M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGME

NT SPACE MANAGEMENT AUTO

ORA-39083: Object type TABLESPACE failed to create with error:

ORA-01276: Cannot add file +DATA/servdb/datafile/ds_model.32447.845631431.  File has an Oracle Managed Files file name.

Failing sql is:

CREATE TABLESPACE "DS_MODEL" DATAFILE '+DATA/servdb/datafile/ds_model.32447.845631431' SIZE 2097152000 AUTOEXTEND ON NEXT 134217728 MAXSIZE 32767M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPAC

E MANAGEMENT AUTO

ORA-39083: Object type TABLESPACE failed to create with error:

ORA-01276: Cannot add file +DATA/servdb/datafile/otfys_platform.385.878320405.  File has an Oracle Managed Files file name.

Failing sql is:

CREATE TABLESPACE "OTFYS_PLATFORM" DATAFILE '+DATA/servdb/datafile/otfys_platform.385.878320405' SIZE 2097152000 AUTOEXTEND ON NEXT 67108864 MAXSIZE 32767M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEG

MENT SPACE MANAGEMENT AUTO

ORA-39083: Object type TABLESPACE failed to create with error:

ORA-01276: Cannot add file +DATA/servdb/datafile/otfys_platform135.61091.1032087535.  File has an Oracle Managed Files file name.

Failing sql is:

CREATE TABLESPACE "OTFYS_PLATFORM135" DATAFILE '+DATA/servdb/datafile/otfys_platform135.61091.1032087535' SIZE 67108864 AUTOEXTEND ON NEXT 33554432 MAXSIZE 10240M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOC

ATE SEGMENT SPACE MANAGEMENT AUTO

解决思路:手动建立表空间，[这里有一个细节，最后名字要一样，表空间必须保证足够大，要不然还会报错]

CREATE UNDO TABLESPACE "UNDOTBS2" DATAFILE '/u01/app/oradata/servdb/undotbs2.280.826877779' SIZE 209715200 AUTOEXTEND ON NEXT 5242880 MAXSIZE 32767M;

CREATE TABLESPACE "NO1TASK" DATAFILE '/u01/app/oradata/servdb/no1task.329.827069307' SIZE 2048M AUTOEXTEND ON NEXT 268435456 MAXSIZE 32767M,'/u01/app/oradata/servdb/no1task.6801.910947551' SIZE 2048M AUTOEXTEND ON NEXT 536870912 MAXSIZE 32767M;

CREATE TABLESPACE "MOBILEAPP" DATAFILE '/u01/app/oradata/servdb/mobileapp.397.827404615' SIZE 1024M AUTOEXTEND ON NEXT 128M MAXSIZE 32767M;

CREATE TABLESPACE "OTF_PLATFORM" DATAFILE '/u01/app/oradata/servdb/otf_platform.3693.842997461' SIZE 2048M AUTOEXTEND ON NEXT 128M MAXSIZE 32767M;

CREATE TABLESPACE "DS_MODEL" DATAFILE '/u01/app/oradata/servdb/ds_model.32447.845631431' SIZE 2097152000 AUTOEXTEND ON NEXT 134217728 MAXSIZE 32767M;

CREATE TABLESPACE "OTFYS_PLATFORM" DATAFILE '/u01/app/oradata/servdb/otfys_platform.385.878320405' SIZE 2097152000 AUTOEXTEND ON NEXT 67108864 MAXSIZE 32767M;

CREATE TABLESPACE "OTFYS_PLATFORM135" DATAFILE '/u01/app/oradata/servdb/otfys_platform135.61091.1032087535' SIZE 67108864 AUTOEXTEND ON NEXT 33554432 MAXSIZE 10240M;

这里，不记叙重复性的事情。后面由于三个示例，需要修改一下归档日志的位置。

三个数据库实例，

servdb实例的位置为【/u01/app/arch/servdb】

managedb实例的位置为【/u01/app/arch/managedb】

irc3db实例的位置为【/u01/app/arch/irc3db】

mkdir -p /u01/app/arch/servdb

mkdir -p /u01/app/arch/managedb

mkdir -p /u01/app/arch/irc3db

export ORACLE_SID=servdb

$ sqlplus "/as sysdba"

SQL>alter system set log_archive_dest_1='location=/u01/app/arch/servdb' scope =both;

export ORACLE_SID=managedb

$ sqlplus "/as sysdba"

SQL>alter system set log_archive_dest_1='location=/u01/app/arch/managedb' scope =both;

重新开启归档模式

shutdown immediate;

startup mount;

alter database archivelog;

alter database open;

相关文章