一次恢复测试过程记录
作者 ：OoNiceDream【转载时请务必以超链接形式标明文章原始出处和作者信息】
链接：http://www.dbaroad.me/archives/2009/07/recover_database_test.html
链接：http://www.dbaroad.me/archives/2009/07/recover_database_test.html
近期对本地网某系统做了一次恢复测试，将整个恢复过程简单记录一下：
1、启动数据库到nomount状态
$export ORACLE_SID=ora9i $ rman target / catalog rman/rman@rman Recovery Manager: Release 9.2.0.7.0 - 64bit Production Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved. connected to target database (not started)? connected to recovery catalog database RMAN> startup nomount; startup failed: ORA-01078: failure in processing system parameters LRM-00109: could not open parameter file '/oracle/app/oracle/product/9207/dbs/initora9i.ora' trying to start the Oracle instance without parameter files ... Oracle instance started Total System Global Area 165103464 bytes Fixed Size 726888 bytes Variable Size 113246208 bytes Database Buffers 50331648 bytes Redo Buffers 798720 bytes RMAN> list incarnation; List of Database Incarnations DB Key Inc Key DB Name DB ID CUR Reset SCN Reset Time ------- ------- -------- ---------------- --- ---------- ---------- 24147 24148 ORA9I 630965103 YES 1 21-JAN-06 14940 14941 ORDKH 686354273 YES 152289 10-JUL-04 1 2 ORYX 1049948757 YES 1 16-MAY-04 143561 143562 ORJF 2492331128 YES 1 27-APR-02 RMAN> set dbid 630965103; executing command: SET DBID |
这里需要set dbid,要不RMAN就不知道恢复的是哪个数据库了,会报：
RMAN> list backup of database; RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of list command at 07/16/2009 15:31:58 RMAN-06004: ORACLE error from recovery catalog database: RMAN-20001: target database not found in recovery catalog |
2、恢复SPFILE，并修改相关参数
RMAN> run { 2> allocate channel ch00 type 'sbt_tape'; 3> restore spfile; 4> release channel ch00; 5> } allocated channel: ch00 channel ch00: sid=9 devtype=SBT_TAPE channel ch00: VERITAS NetBackup for Oracle - Release 6.0 (2006031019) Starting restore at 14-JUL-09 channel ch00: starting datafile backupset restore channel ch00: restoring SPFILE output filename=/oracle/app/oracle/product/9207/dbs/spfileora9i.ora channel ch00: restored backup piece 1 piece handle=/hc114/bk_full_4552_1_692155260 tag=HOT_DB_BK_LEVEL0 params=NULL channel ch00: restore complete Finished restore at 14-JUL-09 released channel: ch00 RMAN> RMAN> shutdown immediate; Oracle instance shut down $ sqlplus "/as sysdba" SQL*Plus: Release 9.2.0.7.0 - Production on Tue Jul 14 18:19:55 2009 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to an idle instance. SQL> create pfile from spfile; File created |
修改参数文件:
1、修改控制文件路径，为下一步恢复控制文件做准备 2、修改归档路径，为恢复归档做准备。 这里如果没有修改，而原参数文件中的归档路径在要恢复的机子上又不存在， 可以在恢复归档时指定路径，使用：set archivelog destination命令。 3、修改各类dump_dest路径，或者把dump_dest路径下的相关目录创建好 4、create spfile from pfile; |
3、恢复控制文件
RMAN> startup nomount; connected to target database (not started) Oracle instance started Total System Global Area 1897352800 bytes Fixed Size 728672 bytes Variable Size 1358954496 bytes Database Buffers 536870912 bytes Redo Buffers 798720 bytes RMAN> run { 2> allocate channel ch00 type 'sbt_tape'; 3> allocate channel ch01 type 'sbt_tape'; 4> allocate channel ch02 type 'sbt_tape'; 5> restore controlfile; 6> release channel ch00; 7> release channel ch01; 8> release channel ch02; 9> } released channel: ORA_DISK_1 allocated channel: ch00 channel ch00: sid=14 devtype=SBT_TAPE channel ch00: VERITAS NetBackup for Oracle - Release 6.0 (2006031019) allocated channel: ch01 channel ch01: sid=15 devtype=SBT_TAPE channel ch01: VERITAS NetBackup for Oracle - Release 6.0 (2006031019) allocated channel: ch02 channel ch02: sid=16 devtype=SBT_TAPE channel ch02: VERITAS NetBackup for Oracle - Release 6.0 (2006031019) Starting restore at 14-JUL-09 channel ch00: starting datafile backupset restore channel ch00: restoring controlfile output filename=/data01/oradata/rlvol1 channel ch00: restored backup piece 1 piece handle=/hc114/cntrl_4555_1_692157097 tag=TAG20090714T015137 params=NULL channel ch00: restore complete replicating controlfile input filename=/data01/oradata/rlvol1 output filename=/data01/oradata/rlvol2 output filename=/data01/oradata/rlvol3 Finished restore at 14-JUL-09 released channel: ch00 released channel: ch01 released channel: ch02 |
4、创建密码文件并启动数据库到mount状态
5、恢复数据文件
更改数据文件路径，可以使用以下查询：
SQL> select 'set newname for datafile '||chr(39)||name||chr(39)||' to ' 2 ||chr(39)||'/data01/oradata'||name||chr(39)||';' 3 from v$datafile; |
restore数据文件：
run { allocate channel ch00 type 'sbt_tape'; allocate channel ch01 type 'sbt_tape'; allocate channel ch02 type 'sbt_tape'; set newname for datafile '/dev/vg01/rlvol4' to '/data01/oradata/dev/vg01/rlvol4'; set newname for datafile '/dev/vg01/rlvol6' to '/data01/oradata/dev/vg01/rlvol6'; set newname for datafile '/dev/vg01/rlvol20' to '/data01/oradata/dev/vg01/rlvol20'; set newname for datafile '/dev/vg01/rlvol21' to '/data01/oradata/dev/vg01/rlvol21'; set newname for datafile '/dev/vg01/rlvol22' to '/data01/oradata/dev/vg01/rlvol22'; set newname for datafile '/dev/vg01/rlvol23' to '/data01/oradata/dev/vg01/rlvol23'; set newname for datafile '/dev/vg01/rlvol24' to '/data01/oradata/dev/vg01/rlvol24'; set newname for datafile '/dev/vg01/rlvol25' to '/data01/oradata/dev/vg01/rlvol25'; set newname for datafile '/dev/vg01/rlvol26' to '/data01/oradata/dev/vg01/rlvol26'; set newname for datafile '/dev/vg01/rlvol27' to '/data01/oradata/dev/vg01/rlvol27'; set newname for datafile '/dev/vg01/rlvol28' to '/data01/oradata/dev/vg01/rlvol28'; set newname for datafile '/dev/vg01/rlvol29' to '/data01/oradata/dev/vg01/rlvol29'; set newname for datafile '/dev/vg01/rlvol30' to '/data01/oradata/dev/vg01/rlvol30'; set newname for datafile '/dev/vg01/rlvol31' to '/data01/oradata/dev/vg01/rlvol31'; set newname for datafile '/dev/vg01/rlvol32' to '/data01/oradata/dev/vg01/rlvol32'; set newname for datafile '/dev/vg01/rlvol33' to '/data01/oradata/dev/vg01/rlvol33'; set newname for datafile '/dev/vg01/rlvol35' to '/data01/oradata/dev/vg01/rlvol35'; set newname for datafile '/dev/vg01/rlvol36' to '/data01/oradata/dev/vg01/rlvol36'; set newname for datafile '/dev/vg01/rlvol37' to '/data01/oradata/dev/vg01/rlvol37'; set newname for datafile '/dev/vg01/rlvol38' to '/data01/oradata/dev/vg01/rlvol38'; set newname for datafile '/dev/vg01/rlvol39' to '/data01/oradata/dev/vg01/rlvol39'; set newname for datafile '/dev/vg01/rlvol40' to '/data01/oradata/dev/vg01/rlvol40'; set newname for datafile '/dev/vg01/rlvol41' to '/data01/oradata/dev/vg01/rlvol41'; set newname for datafile '/dev/vg01/rlvol42' to '/data01/oradata/dev/vg01/rlvol42'; set newname for datafile '/dev/vg01/rlvol43' to '/data01/oradata/dev/vg01/rlvol43'; set newname for datafile '/dev/vg01/rlvol44' to '/data01/oradata/dev/vg01/rlvol44'; set newname for datafile '/dev/vg01/rlvol45' to '/data01/oradata/dev/vg01/rlvol45'; set newname for datafile '/dev/vg01/rlvol46' to '/data01/oradata/dev/vg01/rlvol46'; set newname for datafile '/dev/vg02/rlvol1' to '/data01/oradata/dev/vg02/rlvol1'; set newname for datafile '/dev/vg02/rlvol2' to '/data01/oradata/dev/vg02/rlvol2'; set newname for datafile '/dev/vg02/rlvol3' to '/data01/oradata/dev/vg02/rlvol3'; set newname for datafile '/dev/vg02/rlvol4' to '/data01/oradata/dev/vg02/rlvol4'; set newname for datafile '/dev/vg02/rlvol5' to '/data01/oradata/dev/vg02/rlvol5'; set newname for datafile '/dev/vg02/rlvol6' to '/data01/oradata/dev/vg02/rlvol6'; set newname for datafile '/dev/vg02/rlvol7' to '/data01/oradata/dev/vg02/rlvol7'; set newname for datafile '/dev/vg02/rlvol8' to '/data01/oradata/dev/vg02/rlvol8'; set newname for datafile '/dev/vg02/rlvol9' to '/data01/oradata/dev/vg02/rlvol9'; set newname for datafile '/dev/vg02/rlvol10' to '/data01/oradata/dev/vg02/rlvol10'; set newname for datafile '/dev/vg02/rlvol11' to '/data01/oradata/dev/vg02/rlvol11'; set newname for datafile '/dev/vg02/rlvol12' to '/data01/oradata/dev/vg02/rlvol12'; set newname for datafile '/dev/vg02/rlvol14' to '/data01/oradata/dev/vg02/rlvol14'; set newname for datafile '/dev/vg02/rlvol15' to '/data01/oradata/dev/vg02/rlvol15'; set newname for datafile '/dev/vg02/rlvol20' to '/data01/oradata/dev/vg02/rlvol20'; set newname for datafile '/dev/vg02/rlvol21' to '/data01/oradata/dev/vg02/rlvol21'; set newname for datafile '/dev/vg02/rlvol22' to '/data01/oradata/dev/vg02/rlvol22'; set newname for datafile '/dev/vg02/rlvol23' to '/data01/oradata/dev/vg02/rlvol23'; set newname for datafile '/dev/vg02/rlvol24' to '/data01/oradata/dev/vg02/rlvol24'; set newname for datafile '/dev/vg02/rlvol25' to '/data01/oradata/dev/vg02/rlvol25'; set newname for datafile '/dev/vg02/rlvol26' to '/data01/oradata/dev/vg02/rlvol26'; set newname for datafile '/dev/vg02/rlvol27' to '/data01/oradata/dev/vg02/rlvol27'; set newname for datafile '/dev/vg02/rlvol28' to '/data01/oradata/dev/vg02/rlvol28'; set newname for datafile '/dev/vg02/rlvol29' to '/data01/oradata/dev/vg02/rlvol29'; set newname for datafile '/dev/vg02/rlvol30' to '/data01/oradata/dev/vg02/rlvol30'; set newname for datafile '/dev/vg02/rlvol31' to '/data01/oradata/dev/vg02/rlvol31'; set newname for datafile '/dev/vg02/rlvol32' to '/data01/oradata/dev/vg02/rlvol32'; set newname for datafile '/dev/vg02/rlvol33' to '/data01/oradata/dev/vg02/rlvol33'; set newname for datafile '/dev/vg02/rlvol34' to '/data01/oradata/dev/vg02/rlvol34'; set newname for datafile '/dev/vg02/rlvol35' to '/data01/oradata/dev/vg02/rlvol35'; set newname for datafile '/dev/vg02/rlvol36' to '/data01/oradata/dev/vg02/rlvol36'; set newname for datafile '/dev/vg02/rlvol37' to '/data01/oradata/dev/vg02/rlvol37'; set newname for datafile '/dev/vg02/rlvol38' to '/data01/oradata/dev/vg02/rlvol38'; set newname for datafile '/dev/vg02/rlvol39' to '/data01/oradata/dev/vg02/rlvol39'; set newname for datafile '/dev/vg02/rlvol40' to '/data01/oradata/dev/vg02/rlvol40'; set newname for datafile '/dev/vg02/rlvol41' to '/data01/oradata/dev/vg02/rlvol41'; set newname for datafile '/dev/vg02/rlvol42' to '/data01/oradata/dev/vg02/rlvol42'; restore database; switch datafile all; release channel ch00; release channel ch01; release channel ch02; } |
6、更改Redo Log路径
$ sqlplus "/as sysdba" SQL*Plus: Release 9.2.0.7.0 - Production on Tue Jul 14 21:57:11 2009 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.7.0 - Production SQL> col member for a50 SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER ---------- ------- ------- -------------------------------------------------- 1 ONLINE /dev/vg01/rlvol7 2 ONLINE /dev/vg01/rlvol8 3 ONLINE /dev/vg01/rlvol9 SQL> alter database rename file '/dev/vg01/rlvol7' to '/data01/oradata/dev/vg01/rlvol7'; Database altered. SQL> alter database rename file '/dev/vg01/rlvol8' to '/data01/oradata/dev/vg01/rlvol8'; Database altered. SQL> alter database rename file '/dev/vg01/rlvol9' to '/data01/oradata/dev/vg01/rlvol9'; Database altered. SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER ---------- ------- ------- -------------------------------------------------- 1 ONLINE /data01/oradata/dev/vg01/rlvol7 2 ONLINE /data01/oradata/dev/vg01/rlvol8 3 ONLINE /data01/oradata/dev/vg01/rlvol9 |
7、恢复Archive Log
查询需要恢复哪些Archive Log，这里我们可以借助X$KCVFH视图：
SQL> r 1 select fhtsn ts#, 2 HXFIL file#, 3 substr(HXFNM, 1, 50) File_name, 4 FHTYP Type, 5 FHSCN SCN, 6 FHSTA status, 7 FHRBA_SEQ Sequence 8* from X$KCVFH TS# FILE# FILE_NAME TYPE SCN STATUS SEQUENCE --- ---------- ----------------------------------- ---- ---------------- ------ ---------- 0 1 /data01/oradata/dev/vg01/rlvol4 3 10863751638258 0 6531 1 2 /data01/oradata/dev/vg01/rlvol6 3 10863748248144 64 6531 3 3 /data01/oradata/dev/vg01/rlvol20 3 10863748248398 0 6531 3 4 /data01/oradata/dev/vg01/rlvol21 3 10863751488856 0 6531 3 5 /data01/oradata/dev/vg01/rlvol22 3 10863751638258 0 6531 4 6 /data01/oradata/dev/vg01/rlvol23 3 10863748248144 0 6531 4 7 /data01/oradata/dev/vg01/rlvol24 3 10863748248398 0 6531 4 8 /data01/oradata/dev/vg01/rlvol25 3 10863751488856 0 6531 5 9 /data01/oradata/dev/vg01/rlvol26 3 10863751638258 0 6531 5 10 /data01/oradata/dev/vg01/rlvol27 3 10863748248144 0 6531 5 11 /data01/oradata/dev/vg01/rlvol28 3 10863748248398 0 6531 6 12 /data01/oradata/dev/vg01/rlvol29 3 10863751488856 0 6531 6 13 /data01/oradata/dev/vg01/rlvol30 3 10863751638258 0 6531 6 14 /data01/oradata/dev/vg01/rlvol31 3 10863748248144 0 6531 7 15 /data01/oradata/dev/vg01/rlvol32 3 10863748248398 0 6531 8 16 /data01/oradata/dev/vg01/rlvol33 3 10863751488856 0 6531 10 17 /data01/oradata/dev/vg01/rlvol35 3 10863751638258 0 6531 11 18 /data01/oradata/dev/vg01/rlvol36 3 10863748248144 0 6531 12 19 /data01/oradata/dev/vg01/rlvol37 3 10863748248144 0 6531 12 20 /data01/oradata/dev/vg01/rlvol38 3 10863751638258 0 6531 13 21 /data01/oradata/dev/vg01/rlvol39 3 10863748248144 0 6531 13 22 /data01/oradata/dev/vg01/rlvol40 3 10863748248398 0 6531 14 23 /data01/oradata/dev/vg01/rlvol41 3 10863751488856 0 6531 14 24 /data01/oradata/dev/vg01/rlvol42 3 10863751638258 0 6531 15 25 /data01/oradata/dev/vg01/rlvol43 3 10863748248144 0 6531 15 26 /data01/oradata/dev/vg01/rlvol44 3 10863748248398 64 6531 16 27 /data01/oradata/dev/vg01/rlvol45 3 10863751488856 0 6531 17 28 /data01/oradata/dev/vg01/rlvol46 3 10863751638258 0 6531 19 29 /data01/oradata/dev/vg02/rlvol1 3 10863748248144 64 6531 19 30 /data01/oradata/dev/vg02/rlvol2 3 10863748248398 64 6531 20 31 /data01/oradata/dev/vg02/rlvol3 3 10863751488856 64 6531 20 32 /data01/oradata/dev/vg02/rlvol4 3 10863751638258 0 6531 21 33 /data01/oradata/dev/vg02/rlvol5 3 10863748248144 0 6531 21 34 /data01/oradata/dev/vg02/rlvol6 3 10863748248398 0 6531 21 35 /data01/oradata/dev/vg02/rlvol7 3 10863751488856 0 6531 21 36 /data01/oradata/dev/vg02/rlvol8 3 10863751638258 0 6531 22 37 /data01/oradata/dev/vg02/rlvol9 3 10863748248144 64 6531 22 38 /data01/oradata/dev/vg02/rlvol10 3 10863748248398 0 6531 22 39 /data01/oradata/dev/vg02/rlvol11 3 10863751488856 0 6531 22 40 /data01/oradata/dev/vg02/rlvol12 3 10863751638258 0 6531 10 41 /data01/oradata/dev/vg02/rlvol14 3 10863748248398 0 6531 10 42 /data01/oradata/dev/vg02/rlvol15 3 10863751488856 0 6531 24 43 /data01/oradata/dev/vg02/rlvol20 3 10863748248398 0 6531 14 44 /data01/oradata/dev/vg02/rlvol21 3 10863751488856 0 6531 15 45 /data01/oradata/dev/vg02/rlvol22 3 10863751638258 0 6531 19 46 /data01/oradata/dev/vg02/rlvol23 3 10863748248144 0 6531 10 47 /data01/oradata/dev/vg02/rlvol24 3 10863748248398 0 6531 10 48 /data01/oradata/dev/vg02/rlvol25 3 10863751488856 0 6531 14 49 /data01/oradata/dev/vg02/rlvol26 3 10863751638258 0 6531 15 50 /data01/oradata/dev/vg02/rlvol27 3 10863748248144 0 6531 25 51 /data01/oradata/dev/vg02/rlvol28 3 10863748248398 0 6531 8 52 /data01/oradata/dev/vg02/rlvol29 3 10863751638258 0 6531 25 53 /data01/oradata/dev/vg02/rlvol30 3 10863751488856 0 6531 22 54 /data01/oradata/dev/vg02/rlvol31 3 10863751638258 0 6531 10 55 /data01/oradata/dev/vg02/rlvol32 3 10863748248144 0 6531 4 56 /data01/oradata/dev/vg02/rlvol33 3 10863748248398 0 6531 3 57 /data01/oradata/dev/vg02/rlvol34 3 10863751488856 0 6531 5 58 /data01/oradata/dev/vg02/rlvol35 3 10863751638258 0 6531 6 59 /data01/oradata/dev/vg02/rlvol36 3 10863748248144 0 6531 10 60 /data01/oradata/dev/vg02/rlvol37 3 10863748248398 0 6531 15 61 /data01/oradata/dev/vg02/rlvol38 3 10863751488856 0 6531 15 62 /data01/oradata/dev/vg02/rlvol39 3 10863751638258 0 6531 14 63 /data01/oradata/dev/vg02/rlvol40 3 10863748248144 0 6531 14 64 /data01/oradata/dev/vg02/rlvol41 3 10863748248398 0 6531 20 65 /data01/oradata/dev/vg02/rlvol42 3 10863751488856 0 6531 65 rows selected. |
这里需要从Sequence为6531的日志开始恢复，再用list backup of archivelog all;查看哪些归档需要恢复：
RMAN> run { 2> allocate channel ch00 type 'sbt_tape'; 3> allocate channel ch01 type 'sbt_tape'; 4> restore archivelog sequence between 6531 and 6532; 5> release channel ch00; 6> release channel ch01; 7> } allocated channel: ch00 channel ch00: sid=12 devtype=SBT_TAPE channel ch00: VERITAS NetBackup for Oracle - Release 6.0 (2006031019) allocated channel: ch01 channel ch01: sid=14 devtype=SBT_TAPE channel ch01: VERITAS NetBackup for Oracle - Release 6.0 (2006031019) Starting restore at 14-JUL-09 channel ch00: starting archive log restore to default destination channel ch00: restoring archive log archive log thread=1 sequence=6531 channel ch00: restoring archive log archive log thread=1 sequence=6532 channel ch00: restored backup piece 1 piece handle=/hc114/arch_4554_1_692156950 tag=TAG20090714T014910 params=NULL channel ch00: restore complete Finished restore at 14-JUL-09 released channel: ch00 released channel: ch01 RMAN> |
8、recover后打开数据库
SQL> recover database using backup controlfile; ORA-00279: change 10863748248144 generated at 07/14/2009 00:53:57 needed for thread 1 ORA-00289: suggestion : /data01/oradata/archive/1_6531.dbf ORA-00280: change 10863748248144 for thread 1 is in sequence #6531 Specify log: {<ret>=suggested | filename | AUTO | CANCEL} auto ORA-00279: change 10863757719766 generated at 07/14/2009 01:48:46 needed for thread 1 ORA-00289: suggestion : /data01/oradata/archive/1_6532.dbf ORA-00280: change 10863757719766 for thread 1 is in sequence #6532 ORA-00278: log file '/data01/oradata/archive/1_6531.dbf' no longer needed for this recovery ORA-00279: change 10863757719802 generated at 07/14/2009 01:49:09 needed for thread 1 ORA-00289: suggestion : /data01/oradata/archive/1_6533.dbf ORA-00280: change 10863757719802 for thread 1 is in sequence #6533 ORA-00278: log file '/data01/oradata/archive/1_6532.dbf' no longer needed for this recovery ORA-00308: cannot open archived log '/data01/oradata/archive/1_6533.dbf' ORA-27037: unable to obtain file status HPUX-ia64 Error: 2: No such file or directory Additional information: 3 SQL> recover database until cancel using backup controlfile; ORA-00279: change 10863757719802 generated at 07/14/2009 01:49:09 needed for thread 1 ORA-00289: suggestion : /data01/oradata/archive/1_6533.dbf ORA-00280: change 10863757719802 for thread 1 is in sequence #6533 Specify log: {</ret><ret>=suggested | filename | AUTO | CANCEL} cancel Media recovery cancelled. SQL> alter database open resetlogs; Database altered. </ret> |
9、添加TEMP文件
SQL> alter tablespace temp add tempfile 2 '/data01/oradata/dev/temp01.dbf' size 10240M autoextend off; |
以上就是整个不完全恢复过程，如果再应用一下在线日志，便是完全恢复了。整个恢复顺序跟数据库启动过程差不多。
— The End —
关键字: 备份恢复
站内搜索