oracle study之案例--重建数据库控制文件
系统环境：
操作系统： linux rh6
数据库： oracle 11gr2
案例分析：
数据库中所有的控制文件被意外破坏，非归档的库，在有trace备份的情况下，重建控制文件。
1、控制文件trace脚本
[oracle@rh6 ~]$ cat crctr.sql create controlfile reuse database test3 noresetlogs noarchivelog maxlogfiles 10 maxlogmembers 5 maxdatafiles 300 maxinstances 1 maxloghistory 292logfile group 1 '/u01/app/oracle/oradata/test3/redo01a.log' size 100m blocksize 512, group 2 '/u01/app/oracle/oradata/test3/redo02a.log' size 100m blocksize 512-- standby logfiledatafile '/u01/app/oracle/oradata/test3/system01.dbf', '/u01/app/oracle/oradata/test3/sysaux01.dbf', '/u01/app/oracle/oradata/test3/undotbs01.dbf', '/u01/app/oracle/oradata/test3/users01.dbf'character set zhs16gbk；
2、启动instance到nomount，重建controlfile
10:59:05 sys@ test3 >startup nomount;oracle instance started.total system global area 313860096 bytesfixed size 1336232 bytesvariable size 213912664 bytesdatabase buffers 92274688 bytesredo buffers 6336512 bytes10:59:41 sys@ test3 >@/home/oracle/crctr.sqlcontrol file created.
3、告警日志
......create controlfile reuse database test3 noresetlogs noarchivelog maxlogfiles 10 maxlogmembers 5 maxdatafiles 300 maxinstances 1 maxloghistory 292logfile group 1 '/u01/app/oracle/oradata/test3/redo01a.log' size 100m blocksize 512, group 2 '/u01/app/oracle/oradata/test3/redo02a.log' size 100m blocksize 512-- standby logfiledatafile '/u01/app/oracle/oradata/test3/system01.dbf', '/u01/app/oracle/oradata/test3/sysaux01.dbf', '/u01/app/oracle/oradata/test3/undotbs01.dbf', '/u01/app/oracle/oradata/test3/users01.dbf'character set zhs16gbkwarning: default temporary tablespace not specified in create database commanddefault temporary tablespace will be necessary for a locally managed database in future releasewed jan 07 11:00:02 2015successful mount of redo thread 1, with mount id 991126251completed: create controlfile reuse database test3 noresetlogs noarchivelog maxlogfiles 10 maxlogmembers 5 maxdatafiles 300 maxinstances 1 maxloghistory 292logfile group 1 '/u01/app/oracle/oradata/test3/redo01a.log' size 100m blocksize 512, group 2 '/u01/app/oracle/oradata/test3/redo02a.log' size 100m blocksize 512-- standby logfiledatafile '/u01/app/oracle/oradata/test3/system01.dbf', '/u01/app/oracle/oradata/test3/sysaux01.dbf', '/u01/app/oracle/oradata/test3/undotbs01.dbf', '/u01/app/oracle/oradata/test3/users01.dbf'character set zhs16gbkwed jan 07 11:00:59 2015......
3、查看数据库状态
11:00:03 sys@ test3 >select status from v$instance;status------------mounted11:00:27 sys@ test3 >select file#,name ,checkpoint_change# from v$datafile; file# name checkpoint_change#---------- -------------------------------------------------- ------------------ 1 /u01/app/oracle/oradata/test3/system01.dbf 333365 2 /u01/app/oracle/oradata/test3/sysaux01.dbf 333365 3 /u01/app/oracle/oradata/test3/undotbs01.dbf 333365 4 /u01/app/oracle/oradata/test3/users01.dbf 33336511:00:46 sys@ test3 >select file#,name ,checkpoint_change# from v$datafile_header; file# name checkpoint_change#---------- -------------------------------------------------- ------------------ 1 /u01/app/oracle/oradata/test3/system01.dbf 333365 2 /u01/app/oracle/oradata/test3/sysaux01.dbf 333365 3 /u01/app/oracle/oradata/test3/undotbs01.dbf 333365 4 /u01/app/oracle/oradata/test3/users01.dbf 333365
4、打开数据库
11:00:54 sys@ test3 >alter database open;alter database open*error at line 1:ora-01113: file 1 needs media recoveryora-01110: data file 1: '/u01/app/oracle/oradata/test3/system01.dbf'---打开数据库报错，需要做“media recovery”执行介质恢复：由于本库为非归档模式，只能通过current redolog来恢复查看当前日志组：[oracle@rh6 ~]$ sqlplus '/as sysdba'sql*plus: release 11.2.0.1.0 production on wed jan 7 11:02:12 2015copyright (c) 1982, 2009, oracle. all rights reserved.connected to:oracle database 11g enterprise edition release 11.2.0.1.0 - productionwith the partitioning, olap, data mining and real application testing options11:02:12 sys@ test3 >select member from v$logfile;member------------------------------------------------------------------------------------------------------------------------/u01/app/oracle/oradata/test3/redo01a.log/u01/app/oracle/oradata/test3/redo02a.log11:02:22 sys@ test3 >select group#,sequence#,status from v$log; group# sequence# status---------- ---------- ---------------- 2 12 inactive 1 13 current11:00:59 sys@ test3 >recover database until cancel;ora-00279: change 333365 generated at 01/07/2015 10:30:26 needed for thread 1ora-00289: suggestion : /u01/app/oracle/product/11.2.0/db_1/dbs/arch1_13_868275293.dbfora-00280: change 333365 for thread 1 is in sequence #1311:01:42 specify log: {=suggested | filename | auto | cancel}/u01/app/oracle/oradata/test3/redo01a.loglog applied.media recovery complete.---恢复完成！11:02:46 sys@ test3 >alter database open;alter database open*error at line 1:ora-01589: must use resetlogs or noresetlogs option for database openelapsed: 00:00:00.0111:02:52 sys@ test3 >alter database open resetlogs;database altered.---database open成功！
查看告警日志：
alter database openerrors in file /u01/app/oracle/diag/rdbms/test3/test3/trace/test3_ora_3294.trc:ora-01113: file 1 needs media recoveryora-01110: data file 1: '/u01/app/oracle/oradata/test3/system01.dbf'ora-1113 signalled during: alter database open...wed jan 07 11:01:40 2015alter database recover database until cancel media recovery startserial media recovery startedora-279 signalled during: alter database recover database until cancel ...wed jan 07 11:02:44 2015alter database recover logfile '/u01/app/oracle/oradata/test3/redo01a.log' media recovery log /u01/app/oracle/oradata/test3/redo01a.logincomplete recovery applied all redo ever generated.recovery completed through change 334001 time 01/07/2015 10:51:13media recovery complete (test3)completed: alter database recover logfile '/u01/app/oracle/oradata/test3/redo01a.log' alter database openerrors in file /u01/app/oracle/diag/rdbms/test3/test3/trace/test3_ora_3294.trc:ora-01589: must use resetlogs or noresetlogs option for database openora-1589 signalled during: alter database open...wed jan 07 11:03:04 2015alter database open resetlogsresetlogs after complete recovery through change 334001resetting resetlogs activation id 990996637 (0x3b11689d)errors in file /u01/app/oracle/diag/rdbms/test3/test3/trace/test3_ora_3294.trc:ora-00367: checksum error in log file headerora-00322: log 1 of thread 1 is not current copyora-00312: online log 1 thread 1: '/u01/app/oracle/oradata/test3/redo01a.log'wed jan 07 11:03:05 2015errors in file /u01/app/oracle/diag/rdbms/test3/test3/trace/test3_m000_3336.trc:ora-00316: log 1 of thread 1, type 0 in header is not log fileora-00312: online log 1 thread 1: '/u01/app/oracle/oradata/test3/redo01a.log'errors in file /u01/app/oracle/diag/rdbms/test3/test3/trace/test3_ora_3294.trc:ora-00367: checksum error in log file headerora-00322: log 2 of thread 1 is not current copyora-00312: online log 2 thread 1: '/u01/app/oracle/oradata/test3/redo02a.log'errors in file /u01/app/oracle/diag/rdbms/test3/test3/trace/test3_m000_3336.trc:ora-00316: log 2 of thread 1, type 0 in header is not log fileora-00312: online log 2 thread 1: '/u01/app/oracle/oradata/test3/redo02a.log'wed jan 07 11:03:18 2015setting recovery target incarnation to 2wed jan 07 11:03:20 2015checker run found 4 new persistent data failureswed jan 07 11:03:21 2015assigning activation id 991126251 (0x3b1362eb)thread 1 opened at log sequence 1 current log# 1 seq# 1 mem# 0: /u01/app/oracle/oradata/test3/redo01a.logsuccessful open of redo thread 1wed jan 07 11:03:22 2015mttr advisory is disabled because fast_start_mttr_target is not setwed jan 07 11:03:23 2015smon: enabling cache recoverysuccessfully onlined undo tablespace 2.dictionary check beginningtablespace 'tempts1' #3 found in data dictionary,but not in the controlfile. adding to controlfile.dictionary check completeverifying file header compatibility for 11g tablespace encryption..verifying 11g file header compatibility for tablespace encryption completedsmon: enabling tx recovery*********************************************************************warning: the following temporary tablespaces contain no files. this condition can occur when a backup controlfile has been restored. it may be necessary to add files to these tablespaces. that can be done using the sql statement: alter tablespace add tempfile alternatively, if these temporary tablespaces are no longer needed, then they can be dropped. empty temporary tablespace: tempts1*********************************************************************database characterset is zhs16gbkno resource manager plan activereplication_dependency_tracking turned off (no async multimaster replication found)starting background process qmncwed jan 07 11:03:27 2015qmnc started with pid=19, os id=3341 logstdby: validating controlfile with logical metadatalogstdby: validation completecompleted: alter database open resetlogswed jan 07 11:13:27 2015starting background process smcowed jan 07 11:13:27 2015smco started with pid=22, os id=3382
---至此，通过trace脚本，重建控制文件成功！