ARCHIVELOG模式下用户管理恢复控制文件（2）——使用trace文件重建控制文件！

来源：互联网发布：公安局网络安全管理岗编辑：程序博客网时间：2024/05/28 18:44

首先生成控制文件的sql脚本

SQL> alter database backup controlfile to trace as '/u01/app/oracle/controlfile/control.sql';数据库已更改。

其实就是生成了一个控制文件的脚本，去掉注释以后这个sql脚本的内容如下：

--#1. NORESETLOGS caseSTARTUP NOMOUNT;CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  ARCHIVELOG    MAXLOGFILES 16    MAXLOGMEMBERS 3    MAXDATAFILES 100    MAXINSTANCES 8    MAXLOGHISTORY 292LOGFILE  GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log'  SIZE 50M,  GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log'  SIZE 50M,  GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log'  SIZE 50MDATAFILE  '/u01/app/oracle/oradata/orcl/system01.dbf',  '/u01/app/oracle/oradata/orcl/undotbs01.dbf',  '/u01/app/oracle/oradata/orcl/sysaux01.dbf',  '/u01/app/oracle/oradata/orcl/users01.dbf',  '/u01/app/oracle/oradata/orcl/tb1.dbf'CHARACTER SET ZHS16GBK;RECOVER DATABASE;ALTER SYSTEM ARCHIVE LOG ALL;ALTER DATABASE OPEN;ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf' SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;--#2. RESETLOGS caseSTARTUP NOMOUNT;CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS  ARCHIVELOG    MAXLOGFILES 16    MAXLOGMEMBERS 3    MAXDATAFILES 100    MAXINSTANCES 8    MAXLOGHISTORY 292LOGFILE  GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log'  SIZE 50M,  GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log'  SIZE 50M,  GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log'  SIZE 50MDATAFILE  '/u01/app/oracle/oradata/orcl/system01.dbf',  '/u01/app/oracle/oradata/orcl/undotbs01.dbf',  '/u01/app/oracle/oradata/orcl/sysaux01.dbf',  '/u01/app/oracle/oradata/orcl/users01.dbf',  '/u01/app/oracle/oradata/orcl/tb1.dbf'CHARACTER SET ZHS16GBK;RECOVER DATABASE USING BACKUP CONTROLFILE;ALTER DATABASE OPEN RESETLOGS;ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf' SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

然后删除数据库模拟控制控制文件丢失

SQL> conn /as sysdba已连接到空闲例程。SQL> startupORACLE 例程已经启动。Total System Global Area  167772160 bytesFixed Size                  1266392 bytesVariable Size              62917928 bytesDatabase Buffers          100663296 bytesRedo Buffers                2924544 bytes数据库装载完毕。数据库已经打开。SQL> conn u1/u1已连接。SQL> select * from t;        ID VALUE---------- ----------         1 aSQL> insert into t values(2,'b');已创建 1 行。SQL> commit;提交完成。SQL> select * from t;        ID VALUE---------- ----------         1 a         2 bSQL> ! rm -rf /u01/app/oracle/oradata/orcl/*.ctlSQL> conn /as sysdba已连接。SQL> shutdown abortORACLE 例程已经关闭。

1、下面就删除控制文件用这个脚本来重建控制文件。使用noresetlogs。

SQL> conn /as sysdba已连接到空闲例程。SQL> startup nomountORACLE 例程已经启动。Total System Global Area  167772160 bytesFixed Size                  1266392 bytesVariable Size              62917928 bytesDatabase Buffers          100663296 bytesRedo Buffers                2924544 bytesSQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  ARCHIVELOG  2      MAXLOGFILES 16  3      MAXLOGMEMBERS 3  4      MAXDATAFILES 100  5      MAXINSTANCES 8  6      MAXLOGHISTORY 292  7  LOGFILE  8    GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log'  SIZE 50M,  9    GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log'  SIZE 50M, 10    GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log'  SIZE 50M 11  DATAFILE 12    '/u01/app/oracle/oradata/orcl/system01.dbf', 13    '/u01/app/oracle/oradata/orcl/undotbs01.dbf', 14    '/u01/app/oracle/oradata/orcl/sysaux01.dbf', 15    '/u01/app/oracle/oradata/orcl/users01.dbf', 16    '/u01/app/oracle/oradata/orcl/tb1.dbf' 17  CHARACTER SET ZHS16GBK;控制文件已创建。SQL> select status from v$instance;STATUS------------MOUNTEDSQL> select * from v$recover_file;     FILE# ONLINE  ONLINE_ ERROR                                                                CHANGE# TIME---------- ------- ------- ----------------------------------------------------------------- ---------- --------------         1 ONLINE  ONLINE                                                                        474731 10-10? -11         2 ONLINE  ONLINE                                                                        474731 10-10? -11         3 ONLINE  ONLINE                                                                        474731 10-10? -11         4 ONLINE  ONLINE                                                                        474731 10-10? -11         5 ONLINE  ONLINE                                                                        474731 10-10? -11SQL> alter database open;alter database open*第 1 行出现错误:ORA-01113: ?? 1 ??????ORA-01110: ???? 1: '/u01/app/oracle/oradata/orcl/system01.dbf'SQL> RECOVER DATABASE;完成介质恢复。SQL> ALTER SYSTEM ARCHIVE LOG ALL;系统已更改。SQL> ALTER DATABASE OPEN;数据库已更改。SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf' SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;表空间已更改。SQL> select status from v$instance;STATUS------------OPENSQL> select * from v$recover_file;未选定行SQL> conn u1/u1已连接。SQL> select * from t;        ID VALUE---------- ----------         1 a

2、重复前面的实验环境。使用resetlogs。

SQL> conn /as sysdba已连接到空闲例程。SQL> startup nomount;ORACLE 例程已经启动。Total System Global Area  167772160 bytesFixed Size                  1266392 bytesVariable Size              62917928 bytesDatabase Buffers          100663296 bytesRedo Buffers                2924544 bytesSQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS  ARCHIVELOG  2      MAXLOGFILES 16  3      MAXLOGMEMBERS 3  4      MAXDATAFILES 100  5      MAXINSTANCES 8  6      MAXLOGHISTORY 292  7  LOGFILE  8    GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log'  SIZE 50M,  9    GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log'  SIZE 50M, 10    GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log'  SIZE 50M 11  DATAFILE 12    '/u01/app/oracle/oradata/orcl/system01.dbf', 13    '/u01/app/oracle/oradata/orcl/undotbs01.dbf', 14    '/u01/app/oracle/oradata/orcl/sysaux01.dbf', 15    '/u01/app/oracle/oradata/orcl/users01.dbf', 16    '/u01/app/oracle/oradata/orcl/tb1.dbf' 17  CHARACTER SET ZHS16GBK;控制文件已创建。SQL> select status from v$instance;STATUS------------MOUNTEDSQL> select * from v$recover_file;     FILE# ONLINE  ONLINE_ ERROR                                                                CHANGE# TIME---------- ------- ------- ----------------------------------------------------------------- ---------- --------------         1 ONLINE  ONLINE                                                                        474731 10-10? -11         2 ONLINE  ONLINE                                                                        474731 10-10? -11         3 ONLINE  ONLINE                                                                        474731 10-10? -11         4 ONLINE  ONLINE                                                                        474731 10-10? -11         5 ONLINE  ONLINE                                                                        474731 10-10? -11SQL> select * from v$log;    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------------         1          1          0   52428800          1 YES UNUSED                       0         3          1          0   52428800          1 YES CURRENT                      0         2          1          0   52428800          1 YES UNUSED                       0SQL> select member from v$logfile;MEMBER--------------------------------------------------------------------------------/u01/app/oracle/oradata/orcl/redo03.log/u01/app/oracle/oradata/orcl/redo02.log/u01/app/oracle/oradata/orcl/redo01.logSQL> RECOVER DATABASE USING BACKUP CONTROLFILE;ORA-00279: ?? 474731 (? 10/10/2011 02:31:58 ??) ???? 1 ????ORA-00289: ??: /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2011_10_10/o1_mf_1_14_%u_.arcORA-00280: ?? 474731 (???? 1) ??? #14 ?指定日志: {<RET>=suggested | filename | AUTO | CANCEL}/u01/app/oracle/oradata/orcl/redo03.logORA-00310: ???????? 12; ???? 14ORA-00334: ????: '/u01/app/oracle/oradata/orcl/redo03.log'SQL> RECOVER DATABASE USING BACKUP CONTROLFILE;ORA-00279: ?? 474731 (? 10/10/2011 02:31:58 ??) ???? 1 ????ORA-00289: ??: /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2011_10_10/o1_mf_1_14_%u_.arcORA-00280: ?? 474731 (???? 1) ??? #14 ?指定日志: {<RET>=suggested | filename | AUTO | CANCEL}/u01/app/oracle/oradata/orcl/redo01.logORA-00310: ???????? 13; ???? 14ORA-00334: ????: '/u01/app/oracle/oradata/orcl/redo01.log'SQL> RECOVER DATABASE USING BACKUP CONTROLFILE;ORA-00279: ?? 474731 (? 10/10/2011 02:31:58 ??) ???? 1 ????ORA-00289: ??: /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2011_10_10/o1_mf_1_14_%u_.arcORA-00280: ?? 474731 (???? 1) ??? #14 ?指定日志: {<RET>=suggested | filename | AUTO | CANCEL}/u01/app/oracle/oradata/orcl/redo02.log已应用的日志。完成介质恢复。SQL> select * from v$recover_file;未选定行SQL> ALTER DATABASE OPEN RESETLOGS;数据库已更改。SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf' SIZE 20971520  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;表空间已更改。SQL> select status from v$instance;STATUS------------OPENSQL> conn u1/u1已连接。SQL> select * from t;        ID VALUE---------- ----------         1 a         2 b

指定RESETLOGS会执行下列操作：归档当前的在线重做日志文件（如果能访问到的话），然后清空内容并将日志文件序号重置为1（如果在线重做日志文件不存在，则重建）。重置控制文件中关于在线日志文件的元数据。更新数据文件和在线重做日志文件中的RESETLOGS SCN和重置时间信息。

SQL> conn /as sysdba已连接。SQL> alter system switch logfile;系统已更改。SQL> /系统已更改。SQL> /系统已更改。SQL> select * from v$log;    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------------         1          1          4   52428800          1 NO  CURRENT                 474954 10-10月-11         2          1          2   52428800          1 YES INACTIVE                474948 10-10月-11         3          1          3   52428800          1 YES INACTIVE                474950 10-10月-11