OracleStudy case -- re-build the Database Control File System Environment: Operating System: LinuxRH6 Database: Oracle11gR2 case analysis: all the control files in the database are accidentally damaged, non-archive databases, in the case of trace backup, recreate the control file. 1. Control File trace script [oracle @ rh6 ~] $ Catcrctr. sqlCREA
Case Study of Oracle Study-rebuilding the Database Control File System Environment: Operating System: Linux RH6 Database: Oracle 11gR2 Case Study: All control files in the database are accidentally damaged, non-archive databases, in the case of trace backup, recreate the control file. 1. Control File trace script [oracle @ rh6 ~] $ Catcrctr. sqlCREA
Oracle Study case -- rebuilding Database Control Files
System Environment:
Operating System: Linux RH6
Database: Oracle 11gR2
Case Analysis:
All control files in the database are accidentally damaged. Non-archive databases rebuild control files when trace backup is performed.
1. Control File trace script
[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. Start Instance to nomount and recreate 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. alarm logs
......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. view the database status
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. Open the database
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' --- when an error is reported in the database, media recovery is required to restore the media. Because the database is not in archive mode, you can only use current redolog to restore and view the current log group: [oracle @ rh6 ~] $ Sqlplus '/as sysdba' SQL * Plus: Release 11.2.0.1.0 Production on Wed Jan 7 11:02:12 2015 Copyright (c) 1982,200 9, 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 logs/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. --- recovery is 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. SYS @ test3> alter database open resetlogs; Database altered. --- the Database is open successfully!
View alarm logs:
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
--- At this point, the control file is successfully rebuilt through the trace script!