首页 > 代码库 > 使用rman的level0的备份文件迁移oracle数据库-不完全恢复
使用rman的level0的备份文件迁移oracle数据库-不完全恢复
使用rman的level0的备份文件迁移oracle数据库-不完全恢复-20160811
将oracle数据库从一台机器A迁移到另外的一台机器B（同为linux平台），设置为不同的路径，不同的实例名
源端：
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/db_1
ORACLE_SID=SCPDB
数据文件位置：/u01/app/oracle/oradata/SCPDB/datafile/
目标端：
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/db_1
ORALCE_SID=SCPDB01
数据文件位置：/u01/app/oracle/oradata/SCPDB01/datafile/
把备份上传到目标端/u01/rmanSCPDB/
备份集信息：
[oracle@SCP01 /]$ ll /u01/rmanSCPDB/*
-rw-r--r--. 1 oracle oinstall 10256384 Aug 10 18:18 /u01/rmanSCPDB/ctl_auto_c-2612507248-20160712-00.bk
-rw-r--r--. 1 oracle oinstall 1089299968 Aug 10 18:18 /u01/rmanSCPDB/o12c_full_level0_SCPDB201607122erahka5_1_1.bk
-rw-r--r--. 1 oracle oinstall 2024849408 Aug 10 18:19 /u01/rmanSCPDB/o12c_full_level0_SCPDB201607122frahkan_1_1.bk
-rw-r--r--. 1 oracle oinstall 12288 Aug 10 18:19 /u01/rmanSCPDB/o12c_full_level0_SCPDB201607122grahkbg_1_1.bk
以下都在目标端使用oracle用户操作
cd $ORACLE_BASE
mkdir flash_recovery_area
mkdir -p admin/SCPDB01
cd admin/SCPDB01
mkdir {a,b,c,d,u}dump
开始恢复，此时恢复的实例名为SCPDB
export ORACLE_SID=SCPDB
rman target /
===================================================
==在rman启动数据库到nomount, ===
==还原spfile，使用nomount打开数据库 ===
==编辑一个最简单的pfile，只有db_name即可： ===
==vi initSCPDB.ora ===
==cat initSCPDB.ora ===
==db_name=SCPDB ===
== ===
==也可以不用创建pfile，直接启动到nomount ===
===================================================
RMAN> startup nomount
RMAN> restore spfile from ‘/u01/rmanSCPDB/ctl_auto_c-2612507248-20160712-00.bk‘;
RMAN> shutdown immediate
编辑参数文件，使参数中的相关路径变更为新路径
注意：ORACLE_SID=SCPDB
sqlplus / as sysdba
create pfile from spfile;
编辑生成的$ORACLE_HOME/dbs/initSCPDB.ora文件，
根据需要修改相应的参数，如增加sga大小等
其次修改pfile文件中的相关路径使之指向新的位置，即参数文件中所有含SCPDB目录的应修改为SCPDB01
*.audit_file_dest=‘/u01/app/oracle/admin/SCPDB01/adump‘
*.control_files=‘/u01/app/oracle/oradata/SCPDB01/controlfile/o1_mf_clozc9vs_.ctl‘,‘/u01/app/oracle/fast_recovery_area/SCPDB01/controlfile/o1_mf_clozcb1k_.ctl‘
*.log_archive_dest_1=‘location=/u01/archscp01‘
注意db_name参数不变，为原来的db_name,
待恢复完成后使用nid修改
使用pfile文件启动到nomount状态
目标端的一些参数
*.__data_transfer_cache_size=0
*.__db_cache_size=1808M
*.__java_pool_size=16M
*.__large_pool_size=32M
*.__oracle_base=‘/u01/app/oracle‘ # ORACLE_BASE set from environment
*.__pga_aggregate_target=800M
*.__sga_target=2384M
*.__shared_io_pool_size=80M
*.__shared_pool_size=432M
*.__streams_pool_size=0
*.pga_aggregate_target=790m
*.sga_target=2384M
源端的一些参数
SCPDB.__data_transfer_cache_size=0
SCPDB.__db_cache_size=15904800768
SCPDB.__java_pool_size=469762048
SCPDB.__large_pool_size=1140850688
SCPDB.__oracle_base=‘/u01/app/oracle‘#ORACLE_BASE set from environment
SCPDB.__pga_aggregate_target=6777995264
SCPDB.__sga_target=20333985792
SCPDB.__shared_io_pool_size=536870912
SCPDB.__shared_pool_size=2214592512
SCPDB.__streams_pool_size=0
---还原控制文件
export ORACLE_SID=SCPDB
rman target /
RMAN> startup nomount
RMAN> restore controlfile from ‘/u01/backup/ctl_auto_c-2612507248-20160809-00.bk‘;
---切换到mount状态，注，db_name务必保持原db_name，否则切换到mount时提示
---ORA-01103: database name ‘SCPDB‘ in control file is not ‘SCPDB01‘
RMAN> alter database mount;
RMAN> exit
---开启第二个窗口，修改日志文件位置
export ORACLE_SID=SCPDB
sqlplus / as sysdba
SQL> select ‘alter database rename file ‘‘‘||member||‘‘‘ to ‘‘‘||replace(member,‘SCPDB‘,‘SCPDB01‘)||‘‘‘;‘ from v$logfile;
alter database rename file ‘/u01/app/oracle/fast_recovery_area/SCPDB/onlinelog/redo08a.log‘ to ‘/u01/app/oracle/fast_recovery_area/SCPDB01/onlinelog/redo08a.log‘;
alter database rename file ‘/u01/app/oracle/oradata/SCPDB/onlinelog/redo08b.log‘ to ‘/u01/app/oracle/oradata/SCPDB01/onlinelog/redo08b.log‘;
alter database rename file ‘/u01/app/oracle/fast_recovery_area/SCPDB/onlinelog/redo09a.log‘ to ‘/u01/app/oracle/fast_recovery_area/SCPDB01/onlinelog/redo09a.log‘;
alter database rename file ‘/u01/app/oracle/oradata/SCPDB/onlinelog/redo09b.log‘ to ‘/u01/app/oracle/oradata/SCPDB01/onlinelog/redo09b.log‘;
alter database rename file ‘/u01/app/oracle/fast_recovery_area/SCPDB/onlinelog/redo05a.log‘ to ‘/u01/app/oracle/fast_recovery_area/SCPDB01/onlinelog/redo05a.log‘;
alter database rename file ‘/u01/app/oracle/oradata/SCPDB/onlinelog/redo05b.log‘ to ‘/u01/app/oracle/oradata/SCPDB01/onlinelog/redo05b.log‘;
alter database rename file ‘/u01/app/oracle/fast_recovery_area/SCPDB/onlinelog/redo06a.log‘ to ‘/u01/app/oracle/fast_recovery_area/SCPDB01/onlinelog/redo06a.log‘;
alter database rename file ‘/u01/app/oracle/oradata/SCPDB/onlinelog/redo06b.log‘ to ‘/u01/app/oracle/oradata/SCPDB01/onlinelog/redo06b.log‘;
alter database rename file ‘/u01/app/oracle/fast_recovery_area/SCPDB/onlinelog/redo07a.log‘ to ‘/u01/app/oracle/fast_recovery_area/SCPDB01/onlinelog/redo07a.log‘;
alter database rename file ‘/u01/app/oracle/oradata/SCPDB/onlinelog/redo07b.log‘ to ‘/u01/app/oracle/oradata/SCPDB01/onlinelog/redo07b.log‘;
---执行上述语句
SQL> alter database rename file ‘/u01/app/oracle/oradata/SCPDB/datafile/o1_mf_temp_cto6lh07_.tmp‘ to ‘/u01/app/oracle/oradata/SCPDB01/datafile/o1_mf_temp_cto6lh07_.tmp‘;
.......
---回到第一个窗口中
export ORACLE_SID=SCPDB
rman target /
---使用catalog start with指定备份文件位置
RMAN> catalog start with ‘/u01/backup/‘;
---还原及恢复数据库，由于使用了不同的位置，因此我们需要使用set newname 子句，如下面的示例
run{
allocate channel ch1 device type disk;
set newname for datafile 1 to ‘/u01/app/oracle/oradata/SCPDB01/datafile/system.dbf‘;
set newname for datafile 3 to ‘/u01/app/oracle/oradata/SCPDB01/datafile/sysaux.dbf‘;
set newname for datafile 4 to ‘/u01/app/oracle/oradata/SCPDB01/datafile/undotbs1.dbf‘;
set newname for datafile 6 to ‘/u01/app/oracle/oradata/SCPDB01/datafile/users.dbf‘;
set newname for datafile 2 to ‘/u01/app/oracle/oradata/SCPDB01/datafile/TBS_NotifyDB.dbf‘;
set newname for datafile 5 to ‘/u01/app/oracle/oradata/SCPDB01/datafile/TBS_gmscp.dbf‘;
set newname for datafile 7 to ‘/u01/app/oracle/oradata/SCPDB01/datafile/tbsp_index01.dbf‘;
restore database;
release channel ch1;
switch datafile all;
}
=====================================================================================
----也可以使用
set newname for database to ‘/u01/app/oracle/oradata/SCPDB01/datafile/%b‘;
run{
allocate channel ch1 device type disk;
set newname for database to ‘/u01/app/oracle/oradata/SCPDB01/datafile/%b‘;
restore database;
release channel ch1;
switch datafile all;
}
=====================================================================================
RMAN> recover database;
---Recover过程中会收到如下错误提示
archived log thread=1 sequence=614
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 08/11/2016 14:21:27
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 614 and starting SCN of 8223302
RMAN> recover database until sequence 614;
RMAN> alter database open resetlogs;
---修改临时文件位置
export ORACLE_SID=SCPDB
sqlplus / as sysdba
SQL> shutdown immediate;
SQL> startup mount
SQL> select ‘alter database rename file ‘‘‘||name||‘‘‘ to ‘‘‘||replace(name,‘SCPDB‘,‘SCPDB01‘)||‘‘‘;‘ from v$tempfile;
alter database rename file ‘/u01/app/oracle/oradata/SCPDB/datafile/gmscpTemp01.dbf‘ to ‘/u01/app/oracle/oradata/SCPDB01/datafile/gmscpTemp01.dbf‘;
alter database rename file ‘/u01/app/oracle/oradata/SCPDB/datafile/NotifyDBTemp01.dbf‘ to ‘/u01/app/oracle/oradata/SCPDB01/datafile/NotifyDBTemp01.dbf‘;
alter database rename file ‘/u01/app/oracle/oradata/SCPDB/datafile/o1_mf_temp_ctr6mjvh_.tmp‘ to ‘/u01/app/oracle/oradata/SCPDB01/datafile/o1_mf_temp_ctr6mjvh_.tmp‘;
SQL> alter database open;
---修改实例名，以及数据库名
export ORACLE_SID=SCPDB
sqlplus / as sysdba
SQL> shutdown immediate;
SQL> startup mount
SQL> !nid target=sys/oracle dbname=SCPDB01 logfile=/tmp/change_name2SCPDB01.log
SQL> create spfile from pfile;
SQL> startup mount;
启动数据库，这个时候会报数据库名称不一致的错误，不去管他(ORA-01103: database name ‘SCPDB‘ in control file is not ‘SCPDB01‘)
SQL> alter system set db_name=‘SCPDB01‘ scope=spfile;
SQL> create pfile from spfile;
SQL> shutdown immediate;
[oracle@SCP01 ~]$ export ORACLE_SID=SCPDB01
[oracle@SCP01 ~]$ sqlplus / as sysdba
SQL> startup pfile=‘/u01/app/oracle/product/12.1.0.2/db_1/dbs/initSCPDB.ora‘；
SQL> create spfile from pfile=‘/u01/app/oracle/product/12.1.0.2/db_1/dbs/initSCPDB.ora‘；
SQL> shutdown immediate;
SQL> startup mount;
SQL> create pfile from spfile;
SQL> alter database open resetlogs;
SQL> alter system register;
SQL> alter user system identified by oracle;
相关参考：
http://blog.sina.com.cn/s/blog_67be3b4501017zz9.html
http://blog.csdn.net/leshami/article/details/8076841
http://www.2cto.com/database/201408/327614.html
配置rman并备份
RMAN> show all;
RMAN configuration parameters for database with db_unique_name SCPDB01 are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘/u01/backup/level0/ctl_auto_%F.bk‘;
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘/u01/backup/level0/o12c_full_level0_%d%T%U.bk‘;
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM ‘AES128‘; # default
CONFIGURE COMPRESSION ALGORITHM ‘BASIC‘ AS OF RELEASE ‘DEFAULT‘ OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/u01/app/oracle/product/12.1.0.2/db_1/dbs/snapcf_SCPDB01.f‘; # default
RMAN> backup database plus archivelog delete input;
使用rman的level0的备份文件迁移oracle数据库-不完全恢复