Oracle 11g 表空间的误删除前传, 有关控制文件的备份和恢复（预热）

--控制文件备份到跟踪文件 sys@TESTDB11>alter database backup controlfile to trace;   Database altered.   --查看导出到哪个跟踪文件了 sys@TESTDB11>select value from v$diag_info where name = 'Default Trace File';   VALUE ---------------------------------------------------------------------------- /u01/app/oracle/diag/rdbms/testdb11/TestDB11/trace/TestDB11_ora_1656.trc   --查看跟踪文件的内容 [oracle@S1011:/export/home/oracle]$ more /u01/app/oracle/diag/rdbms/testdb11/TestDB11/trace/TestDB11_ora_1656.trc   CREATE CONTROLFILE REUSE DATABASE "TESTDB11" NORESETLOGS  ARCHIVELOG     MAXLOGFILES 16     MAXLOGMEMBERS 3     MAXDATAFILES 100     MAXINSTANCES 8     MAXLOGHISTORY 292 LOGFILE   GROUP 1 '/u01/app/oracle/oradata/TestDB11/redo01.log'  SIZE 50M BLOCKSIZE 512,   GROUP 2 '/u01/app/oracle/oradata/TestDB11/redo02.log'  SIZE 50M BLOCKSIZE 512,   GROUP 3 '/u01/app/oracle/oradata/TestDB11/redo03.log'  SIZE 50M BLOCKSIZE 512 -- STANDBY LOGFILE DATAFILE   '/oradata/system01.dbf',   '/oradata/sysaux01.dbf',   '/oradata/undotbs01.dbf',   '/oradata/users01.dbf',   '/oradata/example01.dbf' CHARACTER SET AL32UTF8 ;   --查看库中所有的控制文件 sys@TESTDB11>select name from v$controlfile;   NAME ----------------------------------------------------------------------------------------------------------- /u01/app/oracle/oradata/TestDB11/control01.ctl /u01/app/oracle/fast_recovery_area/TestDB11/control02.ctl   --关库 sys@TESTDB11>shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down.   --将所有控制文件移走 [oracle@S1011:/export/home/oracle]$ mv /u01/app/oracle/oradata/TestDB11/control01.ctl /tmp [oracle@S1011:/export/home/oracle]$ mv /u01/app/oracle/fast_recovery_area/TestDB11/control02.ctl /tmp   --现在只能启动到nomount状态 sys@TESTDB11>startup ORACLE instance started.   Total System Global Area  855982080 bytes Fixed Size                  2230792 bytes Variable Size             641730040 bytes Database Buffers          209715200 bytes Redo Buffers                2306048 bytes ORA-00205: error in identifying control file, check alert log for more info     sys@TESTDB11>select status from v$instance;   STATUS ------------ STARTED   --重建控制文件 sys@TESTDB11>CREATE CONTROLFILE REUSE DATABASE "TESTDB11" NORESETLOGS  ARCHIVELOG   2      MAXLOGFILES 16   3      MAXLOGMEMBERS 3   4      MAXDATAFILES 100   5      MAXINSTANCES 8   6      MAXLOGHISTORY 292 LOGFILE   GROUP 1 '/u01/app/oracle/oradata/TestDB11/redo01.log'  SIZE 50M BLOCKSIZE 512,   GROUP 2 '/u01/app/oracle/oradata/TestDB11/redo02.log'  SIZE 50M BLOCKSIZE 512,   GROUP 3 '/u01/app/oracle/oradata/TestDB11/redo03.log'  SIZE 50M BLOCKSIZE 512 -- STANDBY LOGFILE DATAFILE   '/oradata/system01.dbf',   '/oradata/sysaux01.dbf',   '/oradata/undotbs01.dbf',   '/oradata/users01.dbf',   '/oradata/example01.dbf' CHARACTER SET AL32UTF8  19  ;   Control file created.   --此时状态已经为mount了 sys@TESTDB11>select status from v$instance;   STATUS ------------ MOUNTED   --启库 sys@TESTDB11>alter database open;   Database altered.     --还有一种方式就是热备份脚本的最后一句，备份为二进制 sys@TESTDB11>alter database backup controlfile to '/pooldisk02/backup01/inconsistent/control01.bak1' reuse;   Database altered. --查看备份出来的这个二进制文件的内容 [oracle@S1011:/export/home/oracle]$ strings /pooldisk02/backup01/inconsistent/control01.bak1 }|{z TESTDB11 TAG20130813T041706 1TESTDB11 1TESTDB11 TestDB11 1,}& 1,}& :+v- a+v- CEv- :+v- a+v- CEv- /u01/app/oracle/oradata/TestDB11/redo02.log /u01/app/oracle/oradata/TestDB11/redo01.log /u01/app/oracle/oradata/TestDB11/redo03.log /oradata/example01.dbf /oradata/users01.dbf /oradata/undotbs01.dbf /oradata/sysaux01.dbf /oradata/system01.dbf pooldisk02/backup01/inconsistent/control01.bak1 /u01/app/oracle/oradata/TestDB11/redo02.log /u01/app/oracle/oradata/TestDB11/redo01.log /u01/app/oracle/oradata/TestDB11/redo03.log /oradata/example01.dbf /oradata/users01.dbf /oradata/undotbs01.dbf /oradata/sysaux01.dbf /oradata/system01.dbf /pooldisk02/backup01/inconsistent/control01.bak1 SYSTEM SYSAUX UNDOTBS1 USERS EXAMPLE TEMP SYSTEM SYSAUX UNDOTBS1 USERS EXAMPLE 1,}& /archive1/1_93_813665348.dbf 1,}& /archive2/1_93_813665348.dbf 0,}& /archive1/1_94_813665348.dbf 0,}& /archive2/1_94_813665348.dbf 1,}& /archive1/1_93_813665348.dbf 1,}& /archive2/1_93_813665348.dbf 0,}& /archive1/1_94_813665348.dbf 0,}& /archive2/1_94_813665348.dbf /archive1/1_95_813665348.dbf /archive2/1_95_813665348.dbf TestDB11 ORACLE_HOME UNNAMED_INSTANCE_2 UNNAMED_INSTANCE_3 UNNAMED_INSTANCE_4 UNNAMED_INSTANCE_5 UNNAMED_INSTANCE_6 UNNAMED_INSTANCE_7 UNNAMED_INSTANCE_8 UNNAMED_INSTANCE_1 UNNAMED_INSTANCE_2 UNNAMED_INSTANCE_3 UNNAMED_INSTANCE_4 UNNAMED_INSTANCE_5 UNNAMED_INSTANCE_6 UNNAMED_INSTANCE_7 UNNAMED_INSTANCE_8 ACM unit testing operation LSB Database Guard Supplemental Log Data DDL LSB Role Change Support RFS block and kill across RAC RAC-wide SGA   --关库 sys@TESTDB11>shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down.   --移除2个控制文件 [oracle@S1011:/export/home/oracle]$ mv /u01/app/oracle/oradata/TestDB11/control01.ctl /tmp [oracle@S1011:/export/home/oracle]$ mv /u01/app/oracle/fast_recovery_area/TestDB11/control02.ctl /tmp   --启库 sys@TESTDB11>startup ORACLE instance started.   Total System Global Area  855982080 bytes Fixed Size                  2230792 bytes Variable Size             641730040 bytes Database Buffers          209715200 bytes Redo Buffers                2306048 bytes ORA-00205: error in identifying control file, check alert log for more info     sys@TESTDB11>select status from v$instance;   STATUS ------------ STARTED   --看一下控制文件的位置 sys@TESTDB11>show parameter control_file   NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ control_file_record_keep_time        integer     7 control_files                        string      /u01/app/oracle/oradata/TestDB                                                  11/control01.ctl, /u01/app/ora                                                  cle/fast_recovery_area/TestDB1                                                  1/control02.ctl                                                  --还原回来 sys@TESTDB11>! cp /pooldisk02/backup01/inconsistent/control01.bak1 /u01/app/oracle/oradata/TestDB11/control01.ctl   sys@TESTDB11>! cp /pooldisk02/backup01/inconsistent/control01.bak1 /u01/app/oracle/fast_recovery_area/TestDB11/control02.ctl   --用历史的控制文件mount sys@TESTDB11>alter database mount;   Database altered.   --查看控制控制文件中记录的检查点的信息 sys@TESTDB11>col name for a30 sys@TESTDB11>select name, checkpoint_change# from v$datafile;   NAME                           CHECKPOINT_CHANGE# ------------------------------ ------------------ /oradata/system01.dbf                     2525453 /oradata/sysaux01.dbf                     2525453 /oradata/undotbs01.dbf                    2525453 /oradata/users01.dbf                      2525453 /oradata/example01.dbf                    2525453   --查看数据文件中记录的检查点信息（比控制文件中的新） sys@TESTDB11>select name, checkpoint_change# from v$datafile_header;   NAME                           CHECKPOINT_CHANGE# ------------------------------ ------------------ /oradata/system01.dbf                     2526071 /oradata/sysaux01.dbf                     2526071 /oradata/undotbs01.dbf                    2526071 /oradata/users01.dbf                      2526071 /oradata/example01.dbf                    2526071   --此时开库提示 sys@TESTDB11>alter database open; alter database open * ERROR at line 1: ORA-01589: must use RESETLOGS or NORESETLOGS option for database open     --尝试使用resetlogs开库 sys@TESTDB11>alter database open resetlogs; alter database open resetlogs * ERROR at line 1: ORA-01152: file 1 was not restored from a sufficiently old backup ORA-01110: data file 1: '/oradata/system01.dbf'     --尝试直接恢复 sys@TESTDB11>recover database; ORA-00283: recovery session canceled due to errors ORA-01610: recovery using the BACKUP CONTROLFILE option must be done   --指定使用历史的控制文件进行恢复,但归档日志没归档出来 sys@TESTDB11>recover database using backup controlfile; ORA-00279: change 2525716 generated at 08/13/2013 04:13:38 needed for thread 1 ORA-00289: suggestion : /archive2/1_96_813665348.dbf ORA-00280: change 2525716 for thread 1 is in sequence #96     Specify log: {<RET>=suggested | filename | AUTO | CANCEL} auto ORA-00308: cannot open archived log '/archive2/1_96_813665348.dbf' ORA-27037: unable to obtain file status Solaris-AMD64 Error: 2: No such file or directory Additional information: 3     ORA-00308: cannot open archived log '/archive2/1_96_813665348.dbf' ORA-27037: unable to obtain file status Solaris-AMD64 Error: 2: No such file or directory Additional information: 3   --确定需要的96号日志还没有归档出来 sys@TESTDB11>select sequence#, group#, status from v$log;    SEQUENCE#     GROUP# STATUS ---------- ---------- ----------------         94          1 INACTIVE         96          3 CURRENT         95          2 INACTIVE         --确定是哪个联机重做日志文件 sys@TESTDB11>col member for a50 sys@TESTDB11>select group#, member from v$logfile;       GROUP# MEMBER ---------- --------------------------------------------------          2 /u01/app/oracle/oradata/TestDB11/redo02.log          1 /u01/app/oracle/oradata/TestDB11/redo01.log          3 /u01/app/oracle/oradata/TestDB11/redo03.log          --再次进行恢复,指定使用特定的日志文件 sys@TESTDB11>recover database using backup controlfile; ORA-00279: change 2525716 generated at 08/13/2013 04:13:38 needed for thread 1 ORA-00289: suggestion : /archive2/1_96_813665348.dbf ORA-00280: change 2525716 for thread 1 is in sequence #96     Specify log: {<RET>=suggested | filename | AUTO | CANCEL} /u01/app/oracle/oradata/TestDB11/redo03.log                     -- 直接输入需要的联机重做日志 Log applied. Media recovery complete.   sys@TESTDB11>alter database open resetlogs;   Database altered.   --查看已经备份的归档日志 sys@TESTDB11>select sequence#, name from v$archived_log;    SEQUENCE# NAME ---------- ------------------------------         93 /archive1/1_93_813665348.dbf         93 /archive2/1_93_813665348.dbf         94 /archive1/1_94_813665348.dbf         94 /archive2/1_94_813665348.dbf         95 /archive1/1_95_813665348.dbf         95 /archive2/1_95_813665348.dbf         94 /archive1/1_94_813665348.dbf         94 /archive2/1_94_813665348.dbf         95 /archive1/1_95_813665348.dbf         95 /archive2/1_95_813665348.dbf         96 /archive1/1_96_813665348.dbf         96 /archive2/1_96_813665348.dbf   12 rows selected.   --对整个库进行非一致性备份 sys@TESTDB11>@backup_script/backup02.sql

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17013648/viewspace-1153856/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17013648/viewspace-1153856/