SQL> startup nomount
ORACLE instance started.
Total System Global Area 395387088 bytes
Fixed Size 454864 bytes
Variable Size 285212672 bytes
Database Buffers 109051904 bytes
Redo Buffers 667648 bytes
SQL> @C:\Oracle\admin\devel\scripts\crea_ctrl_20_oct_2010.sql
Control file created.
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination c:\oracle\ora92\RDBMS
Oldest online log sequence 0
Current log sequence 0
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 505595471 generated at 10/19/2010 06:44:30
needed for thread 1
ORA-00289: suggestion : C:\ORACLE\ORA92\RDBMS\ARC04700.001
ORA-00280: change 505595471 for thread 1 is in sequence #4700
Specify log: {=suggested | filename | AUTO | CANCEL}
H:\prod_hot_bkup\1_4700.DBF
ORA-00279: change 505596642 generated at 10/19/2010 07:04:31
needed for thread 1
ORA-00289: suggestion : C:\ORACLE\ORA92\RDBMS\ARC04701.001
ORA-00280: change 505596642 for thread 1 is in sequence #4701
ORA-00278: log file 'H:\prod_hot_bkup\1_4700.DBF' no longer
needed for this recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
H:\prod_hot_bkup\1_4701.DBF
ORA-00279: change 505596644 generated at 10/19/2010 07:04:31
needed for thread 1
ORA-00289: suggestion : C:\ORACLE\ORA92\RDBMS\ARC04702.001
ORA-00280: change 505596644 for thread 1 is in sequence #4702
ORA-00278: log file 'H:\prod_hot_bkup\1_4701.DBF' no longer
needed for this recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
H:\prod_hot_bkup\1_4702.DBF
ORA-00279: change 505596648 generated at 10/19/2010 07:04:37
needed for thread 1
ORA-00289: suggestion : C:\ORACLE\ORA92\RDBMS\ARC04703.001
ORA-00280: change 505596648 for thread 1 is in sequence #4703
ORA-00278: log file 'H:\prod_hot_bkup\1_4702.DBF' no longer
needed for this recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
Issue #1
--------
The database engine is unable to locate the logfile, because the directory path cannot be found. So i try to drop the logfile groups to create new ones on the available directory paths.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00344: unable to re-create online log
'I:\ORACLE\ORADATA\devel\REDO01A.LOG'
ORA-27040: skgfrcre: create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database drop logfile group 2;
alter database drop logfile group 2
*
ERROR at line 1:
ORA-01567: dropping log 2 would leave less than 2 log files in thread 1
ORA-00312: online log 2 thread 1: 'D:\ORACLE\ORADATA\devel\REDO02A.LOG'
ORA-00312: online log 2 thread 1: 'H:\ORACLE\ORADATA\devel\REDO02B.LOG'
SQL> alter database add logfile group 1
2> ('E:\ORACLE\ORADATA\devel\REDO01A.LOG') size 200M;
Database altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database add logfile group 2
2> ('E:\ORACLE\ORADATA\devel\REDO02A.LOG') size 200M;
Database altered.
Issue #2
--------
One cannot drop an online redo logfile and cannot switch a logfile in mount state either to drop it.
SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-01623: log 3 is current log for thread 1 - cannot drop
ORA-00312: online log 3 thread 1: 'D:\ORACLE\ORADATA\devel\REDO03A.LOG'
ORA-00312: online log 3 thread 1: 'H:\ORACLE\ORADATA\devel\REDO03B.LOG'
SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-01109: database not open
SQL> select open_mode from v$database;
OPEN_MODE
----------
MOUNTED
Querying V$ views for the logfiles gives an insight of the state of the logfile groups. Then the locations of the logfile groups are changed but the STALE contents do not get cleared neither does it allow to drop one of the members of a logfile groups. As a next step to resolve the issue, the contents of the current online logfile group are cleared so the file is clean from corruption.
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ---------------
1 1 0 209715200 1 YES UNUSED
2 1 0 209715200 1 YES UNUSED
3 1 0 209715200 2 YES INVALIDATED
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- -------------------------------------------
3 STALE ONLINE D:\ORACLE\ORADATA\devel\REDO03A.LOG
3 STALE ONLINE H:\ORACLE\ORADATA\devel\REDO03B.LOG
2 ONLINE E:\ORACLE\ORADATA\devel\REDO02A.LOG
1 ONLINE E:\ORACLE\ORADATA\devel\REDO01A.LOG
SQL> alter database rename file 'D:\ORACLE\ORADATA\devel\REDO03A.LOG' to
2> 'E:\ORACLE\ORADATA\devel\REDO03A.LOG';
Database altered.
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- ---------------------------------------
3 STALE ONLINE E:\ORACLE\ORADATA\devel\REDO03A.LOG
3 STALE ONLINE H:\ORACLE\ORADATA\devel\REDO03B.LOG
2 ONLINE E:\ORACLE\ORADATA\devel\REDO02A.LOG
1 ONLINE E:\ORACLE\ORADATA\devel\REDO01A.LOG
SQL> alter database drop logfile member 'H:\ORACLE\ORADATA\devel\REDO03B.LOG';
alter database drop logfile member 'H:\ORACLE\ORADATA\devel\REDO03B.LOG'
*
ERROR at line 1:
ORA-00362: member is required to form a valid logfile in group 3
ORA-01517: log member: 'H:\ORACLE\ORADATA\devel\REDO03B.LOG'
SQL> alter database clear unarchived logfile group 3;
Database altered.
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- --------------------------------------
3 ONLINE E:\ORACLE\ORADATA\devel\REDO03A.LOG
3 ONLINE H:\ORACLE\ORADATA\devel\REDO03B.LOG
2 ONLINE E:\ORACLE\ORADATA\devel\REDO02A.LOG
1 ONLINE E:\ORACLE\ORADATA\devel\REDO01A.LOG
Yet, the third logfile group cannot be dropped because it is the current logfile and the database needs to be opened to switch the logfile.
SQL> alter database drop logfile member 'H:\ORACLE\ORADATA\devel\REDO03B.LOG';
alter database drop logfile member 'H:\ORACLE\ORADATA\devel\REDO03B.LOG'
*
ERROR at line 1:
ORA-01609: log 3 is the current log for thread 1 - cannot drop members
ORA-00312: online log 3 thread 1: 'E:\ORACLE\ORADATA\devel\REDO03A.LOG'
ORA-00312: online log 3 thread 1: 'H:\ORACLE\ORADATA\devel\REDO03B.LOG'
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
Issue #3
--------
The ORACLE instance terminated, reason being the mismatch in the UNDO tablespace name. After the correction was made, the database would fail to open yet again. The database would not proceed further with either of the 'alter database open' and 'alter database open resetlogs' commands.
The sequence number had been reset to '1' when the last time the database was opened with a 'resetlogs' but the database could not come up to ensure a complete recovery as evident from the below results. So the database would require some more recovering but this time it starts afresh from sequence '1'
ALERT LOG
---------
Wed Oct 20 12:51:30 2010
Errors in file c:\oracle\admin\devel\udump\devel_ora_7580.trc:
ORA-30012: undo tablespace 'UNDOTBS1' does not exist or of wrong type
devel_ora_7580.trc
------------------
ORA-30012: undo tablespace 'UNDOTBS1' does not exist or of wrong type
SQL> startup mount;
ORACLE instance started.
Total System Global Area 395387088 bytes
Fixed Size 454864 bytes
Variable Size 285212672 bytes
Database Buffers 109051904 bytes
Redo Buffers 667648 bytes
Database mounted.
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER
---------- ------- ------- ------------------------------------------
3 ONLINE E:\ORACLE\ORADATA\devel\REDO03A.LOG
3 ONLINE H:\ORACLE\ORADATA\devel\REDO03B.LOG
2 ONLINE E:\ORACLE\ORADATA\devel\REDO02A.LOG
1 ONLINE E:\ORACLE\ORADATA\devel\REDO01A.LOG
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'E:\ORACLE\ORADATA\devel\SYSTEM01.DBF'
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 505596650 generated at 10/20/2010 12:51:29 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\ORA92\RDBMS\ARC00001.001
ORA-00280: change 505596650 for thread 1 is in sequence #1
Specify log: {=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: 'E:\ORACLE\ORADATA\devel\SYSTEM01.DBF'
ORA-01112: media recovery not started
SQL> recover database
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done
The database recovery was started again and this time the online redo log that was created from the last incomplete recovery was applied to achieve a successful recovery and the database was opened with a 'resetlogs' keyword to reset the sequence number one more time.
SQL> recover database using backup controlfile;
ORA-00279: change 505596650 generated at 10/20/2010 12:51:29 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\ORA92\RDBMS\ARC00001.001
ORA-00280: change 505596650 for thread 1 is in sequence #1
Specify log: {=suggested | filename | AUTO | CANCEL}
H:\oracle\oradata\devel\REDO03B.LOG
Log applied.
Media recovery complete.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
Database altered.