OracleStudy case -- rebuilding the Database Control File

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!