亲宝软件园·资讯

展开

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

东瑜 人气:0
思路:一般的思路可以通过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 '/u01https://img.qb5200.com/download-x/dmpbackup'; SQL> grant read,write on directory expbk to system; ``` 导入的dmp文件权限放开 ``` [root@db ~]# chmod -R 777 /u01https://img.qb5200.com/download-x/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/servdbhttps://img.qb5200.com/download-x/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/servdbhttps://img.qb5200.com/download-x/datafile/no1task.329.827069307. File has an Oracle Managed Files file name. Failing sql is: CREATE TABLESPACE "NO1TASK" DATAFILE '+DATA/servdbhttps://img.qb5200.com/download-x/datafile/no1task.329.827069307' SIZE 4194304000 AUTOEXTEND ON NEXT 268435456 MAXSIZE 32767M,'+DATA/servdbhttps://img.qb5200.com/download-x/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/servdbhttps://img.qb5200.com/download-x/datafile/mobileapp.397.827404615. File has an Oracle Managed Files file name. Failing sql is: CREATE TABLESPACE "MOBILEAPP" DATAFILE '+DATA/servdbhttps://img.qb5200.com/download-x/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/servdbhttps://img.qb5200.com/download-x/datafile/otf_platform.3693.842997461. File has an Oracle Managed Files file name. Failing sql is: CREATE TABLESPACE "OTF_PLATFORM" DATAFILE '+DATA/servdbhttps://img.qb5200.com/download-x/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/servdbhttps://img.qb5200.com/download-x/datafilehttps://img.qb5200.com/download-x/ds_model.32447.845631431. File has an Oracle Managed Files file name. Failing sql is: CREATE TABLESPACE "DS_MODEL" DATAFILE '+DATA/servdbhttps://img.qb5200.com/download-x/datafilehttps://img.qb5200.com/download-x/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/servdbhttps://img.qb5200.com/download-x/datafile/otfys_platform.385.878320405. File has an Oracle Managed Files file name. Failing sql is: CREATE TABLESPACE "OTFYS_PLATFORM" DATAFILE '+DATA/servdbhttps://img.qb5200.com/download-x/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/servdbhttps://img.qb5200.com/download-x/datafile/otfys_platform135.61091.1032087535. File has an Oracle Managed Files file name. Failing sql is: CREATE TABLESPACE "OTFYS_PLATFORM135" DATAFILE '+DATA/servdbhttps://img.qb5200.com/download-x/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/servdbhttps://img.qb5200.com/download-x/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; ```

加载全部内容

相关教程
猜你喜欢
用户评论