RMAN 整库备份异机恢复流程
今天在试验机进行了一次 RMAN 备份恢复演练，本次演练假定的情况比较简单，备份数据库没有彻底崩溃，我可以查找到备份数据中控制文件，数据文件，重做日志文件等文件的保存路径，并知晓备份数据库的 RMAN 整库备份文件的保存路径。具体的演练过程如下： 将备份的 RMAN 文...显示全部
今天在试验机进行了一次 RMAN 备份恢复演练，本次演练假定的情况比较简单，备份数据库没有彻底崩溃，我可以查找到备份数据中控制文件，数据文件，重做日志文件等文件的保存路径，并知晓备份数据库的 RMAN 整库备份文件的保存路径。具体的演练过程如下：
将备份的 RMAN 文件放到 /home/oracle/tpsdbbk/ 下 (服务器备份时候的路径)
记录备份数据库的 SID, DBID 以及控制文件，数据文件，重做日志文件，归档文件的保存路径。
[oracle@hqtpsuatdb ~]$ env | grep ORACLE
ORACLE_SID=tps
ORACLE_BASE=/app/oracle
ORACLE_TERM=xterm
ORACLE_HOME=/app/oracle/product/10.2.0/db_1
SQL> select name,dbid from v$database;
NAME DBID
——— ———-
TPS 1381265952
SQL> SELECT name FROM v$datafile;
NAME
—————————————————————–
/app/oracle/oradata/tps/system01.dbf
/app/oracle/oradata/tps/undotbs01.dbf
/app/oracle/oradata/tps/sysaux01.dbf
/app/oracle/oradata/tps/users01.dbf
/app/oracle/oradata/tps/tps.dbf
/app/oracle/oradata/tps/tps_ndx.dbf
/app/oracle/oradata/tps/cmdata.dbf
/app/oracle/oradata/tps/xmaster.dbf
8 rows selected.
SQL> SELECT name FROM v$controlfile;
NAME
—————————————————————–
/app/oracle/oradata/tps/control01.ctl
/app/oracle/oradata/tps/control02.ctl
/app/oracle/oradata/tps/control03.ctl
SQL> SELECT member FROM v$logfile;
MEMBER
—————————————————————–
/app/oracle/oradata/tps/redo03.log
/app/oracle/oradata/tps/redo02.log
/app/oracle/oradata/tps/redo01.log
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/archivelog
Oldest online log sequence 911
Next log sequence to archive 913
Current log sequence 913
SQL>
根据上一步查询的信息，在恢复的主机上创建相应文件夹和设置 SID 和 DBID。
[oracle@newtest ~]$ mkdir -p /app/oracle/oradata/tps/
[oracle@newtest ~]$ mkdir -p /home/oracle/archivelog/
[oracle@newtest ~]$ rman
Recovery Manager: Release 10.2.0.4.0 – Production on Wed May 18 11:22:07 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
RMAN> set dbid=1381265952
executing command: SET DBID
从 RMAN 备份中恢复 pfile
RMAN> connect target /
connected to target database (not started)
RMAN> startup nomount;
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file
‘/home/oracle/product/10.2.0/db_1/dbs/inittps.ora’
starting Oracle instance without parameter file for retrival of spfile
Oracle instance started
Total System Global Area 159383552 bytes
Fixed Size 2082400 bytes
Variable Size 67111328 bytes
Database Buffers 83886080 bytes
Redo Buffers 6303744 bytes
RMAN> restore spfile from ‘/home/oracle/tpsdbbk/CF_TPS_20110504_c-1381265952-20110504-05′;
Starting restore at 18-MAY-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK
channel ORA_DISK_1: autobackup found: /home/oracle/tpsdbbk/CF_TPS_20110504_c-1381265952-20110504-05
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 18-MAY-11
RMAN> restore spfile to pfile ‘/home/oracle/product/10.2.0/db_1/dbs/inittps.ora’ from
‘/home/oracle/tpsdbbk/CF_TPS_20110504_c-1381265952-20110504-05′;
Starting restore at 18-MAY-11
using channel ORA_DISK_1
channel ORA_DISK_1: autobackup found: /home/oracle/tpsdbbk/CF_TPS_20110504_c-1381265952-20110504-05
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 18-MAY-11
RMAN> shutdown immediate;
查看 pfile 文件创建对应缺失文件夹
[oracle@newtest ~]$ vi /home/oracle/product/10.2.0/db_1/dbs/inittps.ora
tps.__db_cache_size=452984832
tps.__java_pool_size=16777216
tps.__large_pool_size=16777216
tps.__shared_pool_size=721420288
tps.__streams_pool_size=0
*.audit_file_dest=’/app/oracle/admin/tps/adump’
*.background_dump_dest=’/app/oracle/admin/tps/bdump’
*.compatible=’10.2.0.3.0′
*.control_files=’/app/oracle/oradata/tps/control01.ctl’,'/app/oracle/oradata/tps/control02.ctl’,'/app/oracle/oradata/tps/control03.ctl’
*.core_dump_dest=’/app/oracle/admin/tps/cdump’
*.db_block_size=8192
*.db_domain=”
*.db_file_multiblock_read_count=16
*.db_name=’tps’
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=tpsXDB)’
*.job_queue_processes=10
*.log_archive_dest_1=’location=/home/oracle/archivelog’
*.open_cursors=300
*.pga_aggregate_target=402653184
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.sga_target=1209008128
*.undo_management=’AUTO’
*.undo_tablespace=’UNDOTBS1′
*.user_dump_dest=’/app/oracle/admin/tps/udump’
[oracle@newtest ~]$ mkdir -p /app/oracle/admin/tps/udump
[oracle@newtest ~]$ mkdir -p /app/oracle/admin/tps/udump
[oracle@newtest ~]$ mkdir -p /app/oracle/admin/tps/adump
[oracle@newtest ~]$ mkdir -p /app/oracle/admin/tps/bdump
[oracle@newtest ~]$ mkdir -p /app/oracle/admin/tps/cdump
[oracle@newtest ~]$
根据生成的 pfile 创建 spfile
RMAN> sql ‘create spfile from pfile’;
sql statement: create spfile from pfile
RMAN> shutdown immediate;
database dismounted
Oracle instance shut down
从 RMAN 备份中恢复控制文件
RMAN> startup nomount;
connected to target database (not started)
Oracle instance started
Total System Global Area 1224736768 bytes
Fixed Size 2083560 bytes
Variable Size 754976024 bytes
Database Buffers 452984832 bytes
Redo Buffers 14692352 bytes
RMAN> restore controlfile from ‘/home/oracle/tpsdbbk/CF_TPS_20110504_c-1381265952-20110504-05′;
Starting restore at 18-MAY-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/app/oracle/oradata/tps/control01.ctl
output filename=/app/oracle/oradata/tps/control02.ctl
output filename=/app/oracle/oradata/tps/control03.ctl
Finished restore at 18-MAY-11
从 RMAN 备份中恢复数据文件
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> restore database;
Starting restore at 18-MAY-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00003 to /app/oracle/oradata/tps/sysaux01.dbf
restoring datafile 00004 to /app/oracle/oradata/tps/users01.dbf
restoring datafile 00005 to /app/oracle/oradata/tps/tps.dbf
restoring datafile 00006 to /app/oracle/oradata/tps/tps_ndx.dbf
restoring datafile 00008 to /app/oracle/oradata/tps/xmaster.dbf
channel ORA_DISK_1: reading from backup piece
/home/oracle/tpsdbbk/FULL_TPS_20110504_0pmbflq8_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/tpsdbbk/FULL_TPS_20110504_0pmbflq8_1_1 tag=TAG20110504T093504
channel ORA_DISK_1: restore complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /app/oracle/oradata/tps/system01.dbf
restoring datafile 00002 to /app/oracle/oradata/tps/undotbs01.dbf
restoring datafile 00007 to /app/oracle/oradata/tps/cmdata.dbf
channel ORA_DISK_1: reading from backup piece
/home/oracle/tpsdbbk/FULL_TPS_20110504_0qmbflrb_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/tpsdbbk/FULL_TPS_20110504_0qmbflrb_1_1 tag=TAG20110504T093504
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 18-MAY-11
RMAN> recover database;
Starting recover at 18-MAY-11
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=852
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=853
channel ORA_DISK_1: reading from backup piece
/home/oracle/tpsdbbk/ARCH_TPS_20110504_0tmbfltm_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/tpsdbbk/ARCH_TPS_20110504_0tmbfltm_1_1 tag=TAG20110504T093654
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archive log filename=/home/oracle/archivelog/1_852_732192740.dbf thread=1 sequence=852
archive log filename=/home/oracle/archivelog/1_853_732192740.dbf thread=1 sequence=853
unable to find archive log
archive log thread=1 sequence=854
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/18/2011 14:25:50
RMAN-06054: media recovery requesting unknown log: thread 1 seq 854 lowscn 5965663469345
RMAN>
通过已经恢复的控制文件和数据文件来演算出重做日志文件
SQL> alter system set “_allow_resetlogs_corruption”=TRUE scope=spfile;
SQL> alter database open resetlogs;
SQL> alter system set “_allow_resetlogs_corruption”=false scope=spfile;收起
将备份的 RMAN 文件放到 /home/oracle/tpsdbbk/ 下 (服务器备份时候的路径)
记录备份数据库的 SID, DBID 以及控制文件，数据文件，重做日志文件，归档文件的保存路径。
[oracle@hqtpsuatdb ~]$ env | grep ORACLE
ORACLE_SID=tps
ORACLE_BASE=/app/oracle
ORACLE_TERM=xterm
ORACLE_HOME=/app/oracle/product/10.2.0/db_1
SQL> select name,dbid from v$database;
NAME DBID
——— ———-
TPS 1381265952
SQL> SELECT name FROM v$datafile;
NAME
—————————————————————–
/app/oracle/oradata/tps/system01.dbf
/app/oracle/oradata/tps/undotbs01.dbf
/app/oracle/oradata/tps/sysaux01.dbf
/app/oracle/oradata/tps/users01.dbf
/app/oracle/oradata/tps/tps.dbf
/app/oracle/oradata/tps/tps_ndx.dbf
/app/oracle/oradata/tps/cmdata.dbf
/app/oracle/oradata/tps/xmaster.dbf
8 rows selected.
SQL> SELECT name FROM v$controlfile;
NAME
—————————————————————–
/app/oracle/oradata/tps/control01.ctl
/app/oracle/oradata/tps/control02.ctl
/app/oracle/oradata/tps/control03.ctl
SQL> SELECT member FROM v$logfile;
MEMBER
—————————————————————–
/app/oracle/oradata/tps/redo03.log
/app/oracle/oradata/tps/redo02.log
/app/oracle/oradata/tps/redo01.log
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/archivelog
Oldest online log sequence 911
Next log sequence to archive 913
Current log sequence 913
SQL>
根据上一步查询的信息，在恢复的主机上创建相应文件夹和设置 SID 和 DBID。
[oracle@newtest ~]$ mkdir -p /app/oracle/oradata/tps/
[oracle@newtest ~]$ mkdir -p /home/oracle/archivelog/
[oracle@newtest ~]$ rman
Recovery Manager: Release 10.2.0.4.0 – Production on Wed May 18 11:22:07 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
RMAN> set dbid=1381265952
executing command: SET DBID
从 RMAN 备份中恢复 pfile
RMAN> connect target /
connected to target database (not started)
RMAN> startup nomount;
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file
‘/home/oracle/product/10.2.0/db_1/dbs/inittps.ora’
starting Oracle instance without parameter file for retrival of spfile
Oracle instance started
Total System Global Area 159383552 bytes
Fixed Size 2082400 bytes
Variable Size 67111328 bytes
Database Buffers 83886080 bytes
Redo Buffers 6303744 bytes
RMAN> restore spfile from ‘/home/oracle/tpsdbbk/CF_TPS_20110504_c-1381265952-20110504-05′;
Starting restore at 18-MAY-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=36 devtype=DISK
channel ORA_DISK_1: autobackup found: /home/oracle/tpsdbbk/CF_TPS_20110504_c-1381265952-20110504-05
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 18-MAY-11
RMAN> restore spfile to pfile ‘/home/oracle/product/10.2.0/db_1/dbs/inittps.ora’ from
‘/home/oracle/tpsdbbk/CF_TPS_20110504_c-1381265952-20110504-05′;
Starting restore at 18-MAY-11
using channel ORA_DISK_1
channel ORA_DISK_1: autobackup found: /home/oracle/tpsdbbk/CF_TPS_20110504_c-1381265952-20110504-05
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 18-MAY-11
RMAN> shutdown immediate;
查看 pfile 文件创建对应缺失文件夹
[oracle@newtest ~]$ vi /home/oracle/product/10.2.0/db_1/dbs/inittps.ora
tps.__db_cache_size=452984832
tps.__java_pool_size=16777216
tps.__large_pool_size=16777216
tps.__shared_pool_size=721420288
tps.__streams_pool_size=0
*.audit_file_dest=’/app/oracle/admin/tps/adump’
*.background_dump_dest=’/app/oracle/admin/tps/bdump’
*.compatible=’10.2.0.3.0′
*.control_files=’/app/oracle/oradata/tps/control01.ctl’,'/app/oracle/oradata/tps/control02.ctl’,'/app/oracle/oradata/tps/control03.ctl’
*.core_dump_dest=’/app/oracle/admin/tps/cdump’
*.db_block_size=8192
*.db_domain=”
*.db_file_multiblock_read_count=16
*.db_name=’tps’
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=tpsXDB)’
*.job_queue_processes=10
*.log_archive_dest_1=’location=/home/oracle/archivelog’
*.open_cursors=300
*.pga_aggregate_target=402653184
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.sga_target=1209008128
*.undo_management=’AUTO’
*.undo_tablespace=’UNDOTBS1′
*.user_dump_dest=’/app/oracle/admin/tps/udump’
[oracle@newtest ~]$ mkdir -p /app/oracle/admin/tps/udump
[oracle@newtest ~]$ mkdir -p /app/oracle/admin/tps/udump
[oracle@newtest ~]$ mkdir -p /app/oracle/admin/tps/adump
[oracle@newtest ~]$ mkdir -p /app/oracle/admin/tps/bdump
[oracle@newtest ~]$ mkdir -p /app/oracle/admin/tps/cdump
[oracle@newtest ~]$
根据生成的 pfile 创建 spfile
RMAN> sql ‘create spfile from pfile’;
sql statement: create spfile from pfile
RMAN> shutdown immediate;
database dismounted
Oracle instance shut down
从 RMAN 备份中恢复控制文件
RMAN> startup nomount;
connected to target database (not started)
Oracle instance started
Total System Global Area 1224736768 bytes
Fixed Size 2083560 bytes
Variable Size 754976024 bytes
Database Buffers 452984832 bytes
Redo Buffers 14692352 bytes
RMAN> restore controlfile from ‘/home/oracle/tpsdbbk/CF_TPS_20110504_c-1381265952-20110504-05′;
Starting restore at 18-MAY-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/app/oracle/oradata/tps/control01.ctl
output filename=/app/oracle/oradata/tps/control02.ctl
output filename=/app/oracle/oradata/tps/control03.ctl
Finished restore at 18-MAY-11
从 RMAN 备份中恢复数据文件
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> restore database;
Starting restore at 18-MAY-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00003 to /app/oracle/oradata/tps/sysaux01.dbf
restoring datafile 00004 to /app/oracle/oradata/tps/users01.dbf
restoring datafile 00005 to /app/oracle/oradata/tps/tps.dbf
restoring datafile 00006 to /app/oracle/oradata/tps/tps_ndx.dbf
restoring datafile 00008 to /app/oracle/oradata/tps/xmaster.dbf
channel ORA_DISK_1: reading from backup piece
/home/oracle/tpsdbbk/FULL_TPS_20110504_0pmbflq8_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/tpsdbbk/FULL_TPS_20110504_0pmbflq8_1_1 tag=TAG20110504T093504
channel ORA_DISK_1: restore complete, elapsed time: 00:01:15
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /app/oracle/oradata/tps/system01.dbf
restoring datafile 00002 to /app/oracle/oradata/tps/undotbs01.dbf
restoring datafile 00007 to /app/oracle/oradata/tps/cmdata.dbf
channel ORA_DISK_1: reading from backup piece
/home/oracle/tpsdbbk/FULL_TPS_20110504_0qmbflrb_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/tpsdbbk/FULL_TPS_20110504_0qmbflrb_1_1 tag=TAG20110504T093504
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 18-MAY-11
RMAN> recover database;
Starting recover at 18-MAY-11
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=852
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=853
channel ORA_DISK_1: reading from backup piece
/home/oracle/tpsdbbk/ARCH_TPS_20110504_0tmbfltm_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/tpsdbbk/ARCH_TPS_20110504_0tmbfltm_1_1 tag=TAG20110504T093654
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archive log filename=/home/oracle/archivelog/1_852_732192740.dbf thread=1 sequence=852
archive log filename=/home/oracle/archivelog/1_853_732192740.dbf thread=1 sequence=853
unable to find archive log
archive log thread=1 sequence=854
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/18/2011 14:25:50
RMAN-06054: media recovery requesting unknown log: thread 1 seq 854 lowscn 5965663469345
RMAN>
通过已经恢复的控制文件和数据文件来演算出重做日志文件
SQL> alter system set “_allow_resetlogs_corruption”=TRUE scope=spfile;
SQL> alter database open resetlogs;
SQL> alter system set “_allow_resetlogs_corruption”=false scope=spfile;收起