Oracle RMAN 恢复数据库到不同主机

一、RMAN 备份的内容RMAN做数据库全备时包含了数据文件、归档日志、控制文件和参数文件和备份日志，如下：arch_20160223_08qukp2t_1_1 arch_20160223_0bqukp92_1_1 ctl_c-3234695588-20160223-01 rmanbak-20160223-0852.log scp_20160223_09qukp2u_1_1 scp_20160223_0aqukp2u_1_1二、测试环境OS：CentOS release 6.4 （Final）Database：Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production原平台与当前平台环境一致，但是oracle数据库目录结构不一致。三、开始恢复1、确认原数据库的DBID（通过RMAN的备份日志，或者通过RMAN备份的控制文件名来识别），同时确认一下原数据库的实例名；2、将RMAN备份的内容拷贝到目标数据库上；3、设置好环境变量：[oracle@dg1 ~]$ export NLS_DATA_FORMAT="YYYY-MM-DD HH24:MI:SS"
[oracle@dg1 ~]$ export ORACLE_SID=scp
[oracle@dg1 ~]$ rman target /Recovery Manager: Release 12.1.0.2.0 - Production on Fri May 6 22:18:52 20164、装载数据库并进行恢复 A、装载数据库RMAN> set dbid=3234695588;executing command: SET DBIDRMAN> startup nomount;startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file "/u01/app/oracle/product/12.1.0.2/dbs/initscp.ora"starting Oracle instance without parameter file for retrieval of spfile
Oracle instance startedB、先恢复spfile文件（或者是pfile文件，可以修改各项参数），因为在spfile 文件中包含了控制文件的位置RMAN> restore spfile to pfile "$ORACLE_HOME/dbs/initscp.ora" from "/home/OracleBack/rmanbak/ctl_c-3234695588-20160223-01";OR：RMAN> restore spfile to "$ORACLE_HOME/dbs/spfilescp.ora" from "/home/OracleBack/rmanbak/ctl_c-3234695588-20160223-01";C、根据pfile文件中的相关参数去修改本地系统目录，或者修改这个pfile文件来匹配本地系统中的路径和目录，我们选择后者需要修改：oracle_base、*.audit_file_dest、*.control_files、*.db_recovery_file_dest、*.db_recovery_file_dest_size、*.diagnostic_dest、*.log_archive_dest_1、memory_target、undo_tablespace等。并在当前系统中创建好相关的目录和权限。[oracle@dg1 dbs]$ vim initscp.orascp.__data_transfer_cache_size=0
scp.__db_cache_size=822083584
scp.__java_pool_size=16777216
scp.__large_pool_size=33554432
scp.__oracle_base="/u01/app/oracle"#ORACLE_BASE set from environment
scp.__pga_aggregate_target=654311424
scp.__sga_target=1241513984
scp.__shared_io_pool_size=50331648
scp.__shared_pool_size=301989888
scp.__streams_pool_size=0
*.audit_file_dest="/u01/app/oracle/admin/scp/adump"
*.audit_trail="db"
*.compatible="12.1.0.2.0"
*.control_files="/u01/app/oracle/oradata/scp/control01.ctl","/u01/app/oracle/fast_recovery_area/scp/control02.ctl"
*.db_block_size=8192
*.db_domain=""
*.db_name="scp"
*.db_recovery_file_dest="/u01/app/oracle/fast_recovery_area"
*.db_recovery_file_dest_size=4560m
*.diagnostic_dest="/u01/app/oracle"
*.dispatchers="（PROTOCOL=TCP）（SERVICE=scpXDB）"
*.log_archive_dest_1="LOCATION=/OracleArch"
*.memory_target=1800m
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile="EXCLUSIVE"
*.undo_tablespace="UNDOTBS1"D、从修改后的pfile文件启动数据库，进行控制文件的恢复RMAN> shutdown abort;
RMAN> startup nomount pfile="$ORACLE_HOME/dbs/initscp.ora";
RMAN> restore controlfile from "/home/OracleBack/rmanbak/ctl_c-3234695588-20160223-01";Starting restore at 06-MAY-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=243 device type=DISKchannel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/home/OracleData/scp/control01.ctl
output file name=/home/OracleArch/fast_recovery_area/control02.ctl
Finished restore at 06-MAY-16--------------------------------------推荐阅读 --------------------------------------RMAN备份时遭遇ORA-19571 http://www.linuxidc.com/Linux/2015-07/120409.htmRMAN 配置归档日志删除策略 http://www.linuxidc.com/Linux/2013-11/92670.htmOracle基础教程之通过RMAN复制数据库 http://www.linuxidc.com/Linux/2013-07/87072.htmRMAN备份策略制定参考内容 http://www.linuxidc.com/Linux/2013-03/81695.htmRMAN备份学习笔记 http://www.linuxidc.com/Linux/2013-03/81892.htmOracle数据库备份加密 RMAN加密 http://www.linuxidc.com/Linux/2013-03/80729.htmRMAN备份时遇到ORA-19588 http://www.linuxidc.com/Linux/2015-07/120410.htm--------------------------------------分割线 --------------------------------------E、控制文件恢复后就可以mount数据库了RMAN> alter database mount;Statement processed
released channel: ORA_DISK_1F、至此，控制文件已经恢复，数据库已经mount，所有的RMAN配置参数均已设置，您应该验证路径以确保它们适用于该主机。RMAN> show all;RMAN configuration parameters for database with db_unique_name SCP are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO "/orabackup/RmanBackupSet/20160223/ctl_%F";
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 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/dbs/snapcf_scp.f"; # defaultG、为了能让RMAN找到恢复文件的位置，我们有两种途径可以实现：一是修改RMAN配置以符合当前备份文件所在位置，其次是将RMAN备份文件拷贝到配置文件中设定的位置（需要参考RMAN的备份日志）。在第一个方法中，为了让RMAN知道备份文件位置 /home/OracleBack/rmanbak ，我们使用catalog命令:RMAN> catalog start with "/home/OracleBack/rmanbak";Starting implicit crosscheck backup at 06-MAY-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=243 device type=DISK
Crosschecked 4 objects
Finished implicit crosscheck backup at 06-MAY-16Starting implicit crosscheck copy at 06-MAY-16
using channel ORA_DISK_1
Finished implicit crosscheck copy at 06-MAY-16searching for all files in the recovery area
cataloging files...
no files catalogedsearching for all files that match the pattern /home/OracleBack/rmanbakList of Files Unknown to the Database
=====================================
File Name: /home/OracleBack/rmanbak/scp_20160223_09qukp2u_1_1
File Name: /home/OracleBack/rmanbak/arch_20160223_0bqukp92_1_1
File Name: /home/OracleBack/rmanbak/scp_20160223_0aqukp2u_1_1
File Name: /home/OracleBack/rmanbak/ctl_c-3234695588-20160223-01
File Name: /home/OracleBack/rmanbak/arch_20160223_08qukp2t_1_1Do you really want to catalog the above files （enter YES or NO）？ yes
cataloging files...
cataloging doneList of Cataloged Files
=======================
File Name: /home/OracleBack/rmanbak/scp_20160223_09qukp2u_1_1
File Name: /home/OracleBack/rmanbak/arch_20160223_0bqukp92_1_1
File Name: /home/OracleBack/rmanbak/scp_20160223_0aqukp2u_1_1
File Name: /home/OracleBack/rmanbak/ctl_c-3234695588-20160223-01
File Name: /home/OracleBack/rmanbak/arch_20160223_08qukp2t_1_1H、对备份集做交叉检查，否则还原数据库时可能会报错RMAN> crosscheck backup;using channel ORA_DISK_1
crosschecked backup piece: found to be "EXPIRED"
backup piece handle=/orabackup/RmanBackupSet/20160223/arch_20160223_08qukp2t_1_1 RECID=8 STAMP=904553565
crosschecked backup piece: found to be "AVAILABLE"
backup piece handle=/home/OracleBack/rmanbak/arch_20160223_08qukp2t_1_1 RECID=16 STAMP=911172456
crosschecked backup piece: found to be "EXPIRED"
backup piece handle=/orabackup/RmanBackupSet/20160223/scp_20160223_0aqukp2u_1_1 RECID=9 STAMP=904553567
crosschecked backup piece: found to be "AVAILABLE"
backup piece handle=/home/OracleBack/rmanbak/scp_20160223_0aqukp2u_1_1 RECID=14 STAMP=911172456
crosschecked backup piece: found to be "EXPIRED"
backup piece handle=/orabackup/RmanBackupSet/20160223/scp_20160223_09qukp2u_1_1 RECID=10 STAMP=904553567
crosschecked backup piece: found to be "AVAILABLE"
backup piece handle=/home/OracleBack/rmanbak/scp_20160223_09qukp2u_1_1 RECID=12 STAMP=911172456
crosschecked backup piece: found to be "EXPIRED"
backup piece handle=/orabackup/RmanBackupSet/20160223/arch_20160223_0bqukp92_1_1 RECID=11 STAMP=904553762
crosschecked backup piece: found to be "AVAILABLE"
backup piece handle=/home/OracleBack/rmanbak/arch_20160223_0bqukp92_1_1 RECID=13 STAMP=911172456
crosschecked backup piece: found to be "AVAILABLE"
backup piece handle=/home/OracleBack/rmanbak/ctl_c-3234695588-20160223-01 RECID=15 STAMP=911172456
Crosschecked 9 objectsI、通过控制文件获得表空间及数据文件列表RMAN> report schema;RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name SCPList of Permanent Datafiles
===========================
File Size（MB） Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 0 SYSTEM *** /u01/app/oracle/oradata/scp/system01.dbf
2 0 ZYTK_AC *** /u01/app/oracle/oradata/scp/zytk_ac01.dbf
3 0 SYSAUX *** /u01/app/oracle/oradata/scp/sysaux01.dbf
4 0 UNDOTBS1 *** /u01/app/oracle/oradata/scp/undotbs01.dbf
5 0 EXAMPLE *** /u01/app/oracle/oradata/scp/example01.dbf
6 0 USERS *** /u01/app/oracle/oradata/scp/users01.dbf
7 0 ZYTK_AC *** /u01/app/oracle/oradata/scp/zytk_ac02.dbf
8 0 ZYTK_ID *** /u01/app/oracle/oradata/scp/zytk_id01.dbf
9 0 ZYTK_ID *** /u01/app/oracle/oradata/scp/zytk_id02.dbf
10 0 ZYTK_OP *** /u01/app/oracle/oradata/scp/zytk_op01.dbf
11 0 ZYTK_OP *** /u01/app/oracle/oradata/scp/zytk_op02.dbf
12 0 ZYTK_TEST01 *** /u01/app/oracle/oradata/scp/zytk_test01.dbfList of Temporary Files
=======================
File Size（MB） Tablespace Maxsize（MB） Tempfile Name
---- -------- -------------------- ----------- --------------------
1 60 TEMP 32767 /u01/app/oracle/oradata/scp/temp01.dbf注意：restore 的时候不会对temp 表空间进行restore。所以等restore 之后，我们需要手工创建temp表空间。J、为了将数据文件恢复到不同于原来的目录结构中，必须编写脚本，重新命名数据文件的位置，最后进行全库的恢复。（如果数据文件路径和原来的相同则直接还原数据库）run{
set newname for datafile 1 to "/home/OracleData/scp/system01.dbf";
set newname for datafile 2 to "/home/OracleData/scp/zytk_ac01.dbf";
set newname for datafile 3 to "/home/OracleData/scp/sysaux01.dbf";
set newname for datafile 4 to "/home/OracleData/scp/undotbs01.dbf";
set newname for datafile 5 to "/home/OracleData/scp/example01.dbf";
set newname for datafile 6 to "/home/OracleData/scp/users01.dbf";
set newname for datafile 7 to "/home/OracleData/scp/zytk_ac02.dbf";
set newname for datafile 8 to "/home/OracleData/scp/zytk_id01.dbf";
set newname for datafile 9 to "/home/OracleData/scp/zytk_id02.dbf";
set newname for datafile 10 to "/home/OracleData/scp/zytk_op01.dbf";
set newname for datafile 11 to "/home/OracleData/scp/zytk_op02.dbf";
set newname for datafile 12 to "/home/OracleData/scp/zytk_test01.dbf";
restore database;
switch datafile all;
}对switch datafile all命令的说明：--对于nocatalog 模式下，rman备份的信息是保存在控制文件里的，包括数据文件的路径信息。这里的 switch datafile all 的作用，就是更新控制文件里的信息。如果不更新控制文件的话，则进行recover操作时还在原来的位置找文件。K、还原数据库以后，进行 recover 操作RMAN> recover database;Starting recover at 06-MAY-16
using channel ORA_DISK_1starting media recoverychannel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=148
channel ORA_DISK_1: reading from backup piece /home/OracleBack/rmanbak/arch_20160223_0bqukp92_1_1
channel ORA_DISK_1: piece handle=/home/OracleBack/rmanbak/arch_20160223_0bqukp92_1_1 tag=ZYTK_DB_FULL
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/home/OracleArch/ArchiveLog/1_148_899483175.dbf thread=1 sequence=148
unable to find archived log
archived log thread=1 sequence=149
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/06/2016 23:53:58
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 149 and starting SCN of 3507749更多详情见请继续阅读下一页的精彩内容： http://www.linuxidc.com/Linux/2016-05/131325p2.htm

MySQL优化 - 开启MySQL慢查询日志及分析工具mysqldumpslowRMAN备份报 RMAN-06059 错误相关资讯 RMAN恢复数据库本文评论查看全部评论（0）