Restoration of Oracle log files and control files corruption

Restoration of Oracle log files and control files corruption:
 
Recovery steps:
 
1. Add _ allow_resetlogs_appsuption = true, _ uploupted_rollback_segments = true, _ offline_rollback_segments = true, start to nomount
 
2. Use the backup of the control file to re-build the script. Because the current status redo is corrupted, resetlogs is required for reconstruction.
 
3. After reconstruction, use alter database open resetlogs; to open the database. In this case, the data of related transactions in the redo will be lost.
 

 

Back up control files and simulate faults:
 
SQL> set linesize 150
 
SQL> alter database backup controlfile to trace as '/home/oracle/ctl. ctl' reuse;
 
Database altered.
 
SQL> insert into tb1 values (1 );
 
1 row created.
 
SQL> COMMIT;
 
Commit complete.
 
SQL> select * from v $ log;
 

 

GROUP # THREAD # SEQUENCE # bytes members arc status FIRST_CHANGE # FIRST_TIME
 
-----------------------------------------------------------------------------------------------------
 
1 1 1 536870912 1 yes inactive 1.2269E + 11 16:08:03
 
2 1 2 536870912 1 yes inactive 1.2269E + 11 2013-05-03 16:23:48
 
3 1 3 536870912 1 no current 1.2269E + 11 2013-05-11 07:26:45
 
4 1 0 536870912 1 yes unused 0
 
5 1 0 536870912 1 yes unused 0
 
SQL> shutdown abort;
 
Oracle @ readerlogdb-> rm-f redo03.log
 
Oracle @ readerlogdb-> rm-f control01.ctl control02.ctl control03.ctl
 

 

Start to nomount by adding relevant parameters:
 
SQL> conn/as sysdba
 
Connected to an idle instance.
 
SQL> startup;
 
ORACLE instance started.
 
Total System Global Area 5049942016 bytes
 
Fixed Size 2090880 bytes
 
Variable Size 1375733888 bytes
 
Database Buffers 3657433088 bytes
 
Redo Buffers 14684160 bytes
 
ORA-00205: error in identifying control file, check alert log for more info
 

 

SQL> create pfile = '/home/oracle/pfile. ora' from spfile;
 
Add the following three lines to the pfile:
 
*. _ Allow_resetlogs_uption = true
 
*. _ Uploupted_rollback_segments = true
 
*. _ Offline_rollback_segments = true
 

 

Differences between NORESETLOGS and RESETLOGS in this experiment:
 
SQL> CREATE CONTROLFILE REUSE DATABASE "SKYREAD" NORESETLOGS FORCE LOGGING ARCHIVELOG
 
MAXLOGFILES 20
 
MAXLOGMEMBERS 5
 
MAXDATAFILES 1000
 
MAXINSTANCES 8
 
# MAXLOGHISTORY 2337
 
LOGFILE
 
GROUP 1'/database/oradata/skyread/redo01.log' SIZE 512 M,
 
GROUP 2'/database/oradata/skyread/redo02.log 'SIZE 512 M,
 
GROUP 3 '/database/oradata/skyread/redo03.log' SIZE 512 M,
 
GROUP 4'/database/oradata/skyread/redo04.log 'SIZE 512 M,
 
GROUP 5'/database/oradata/skyread/redo05.log' SIZE 512 M
 
DATAFILE
 
'/Database/oradata/skyread/system01.dbf ',
 
'/Database/oradata/skyread/tbs_test.dbf ',
 
'/Database/oradata/skyread/sysaux01.dbf ',
 
'/Database/oradata/skyread/users01.dbf ',
 
'/Database2/oradata/skyread/tbs_mrpmusic01.dbf ',
 
'/Database/oradata/skyread/sf01.dbf ',
 
'/Database2/oradata/skyread/undotbs02'
 
Character set UTF8;
 

 

Create controlfile reuse database "SKYREAD" NORESETLOGS FORCE LOGGING ARCHIVELOG
 
*
 
ERROR at line 1:
 
ORA-01503: create controlfile failed
 
ORA-01565: error in identifying file '/database/oradata/skyread/redo03.log'
 
ORA-27037: unable to obtain file status
 
Linux-x86_64 Error: 2: No such file or directory
 
Additional information: 3
 

 

SQL> CREATE CONTROLFILE REUSE DATABASE "SKYREAD" RESETLOGS FORCE LOGGING ARCHIVELOG
 
MAXLOGFILES 20
 
MAXLOGMEMBERS 5
 
MAXDATAFILES 1000
 
MAXINSTANCES 8
 
# MAXLOGHISTORY 2337
 
LOGFILE
 
GROUP 1'/database/oradata/skyread/redo01.log' SIZE 512 M,
 
GROUP 2'/database/oradata/skyread/redo02.log 'SIZE 512 M,
 
GROUP 3 '/database/oradata/skyread/redo03.log' SIZE 512 M,
 
GROUP 4'/database/oradata/skyread/redo04.log 'SIZE 512 M,
 
GROUP 5'/database/oradata/skyread/redo05.log' SIZE 512 M
 
DATAFILE
 
'/Database/oradata/skyread/system01.dbf ',
 
'/Database/oradata/skyread/tbs_test.dbf ',
 
'/Database/oradata/skyread/sysaux01.dbf ',
 
'/Database/oradata/skyread/users01.dbf ',
 
'/Database2/oradata/skyread/tbs_mrpmusic01.dbf ',
 
'/Database/oradata/skyread/sf01.dbf ',
 
'/Database2/oradata/skyread/undotbs02'
 
Character set UTF8;
 

 

After recreating the control file, reset the log to open the database, but lose the previously inserted data:
 
SQL> alter database open resetlogs;
 
Database altered.
 
SQL> select * from tb1;
 
At this time, the 1 inserted in the tb1 table does not exist.