June 13, 2008 – Persistent Storage Solutions

In this scenario we will be recovering the database after loss of current control file. We assume that database is in archive log mode and all the datafile, online redo log files and archive log files are intact.

Same steps holds good even if the database is in no archive log mode.

1) Database is in archive log mode here. But archivelog mode does not matter. These steps holds good even if the database is not in archive log mode.

bash-2.05$ sqlplus “/as sysdba”

SQL*Plus: Release 10.2.0.1.0 – Production on Thu Jun 12 05:51:04 2008

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /dy/oracle/product/db10g/archive/htmldb
Oldest online log sequence 71
Next log sequence to archive 73
Current log sequence 73
SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options

2) Remove the control files from file system.

bash-2.05$ ls
htmldb redo02.log system01.dbf test_control2
htmldb01.dbf redo03.log temp01.dbf undotbs01.dbf
redo01.log sysaux01.dbf test_control1
bash-2.05$ rm test_control*

After doing this database will crash after some time, when ever any background process needs to update the control file, it wont find the same and DB will crash.

3) Create a “create control file script”. This can be obtained using “Alter database backup controlfile to trace” command if used some time back. Its a good practice to backup the control file to trace regularly when ever the structure if changed (Example when ever a datafile is added).

Create control file command is stored in crdb.sql

bash-2.05$ cat crdb.sql
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE “HTMLDB” NORESETLOGS ARCHIVELOG
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ‘/dy/oracle/product/db10g/dbf/redo01.log’ SIZE 100M,
GROUP 2 ‘/dy/oracle/product/db10g/dbf/redo02.log’ SIZE 100M,
GROUP 3 ‘/dy/oracle/product/db10g/dbf/redo03.log’ SIZE 100M
— STANDBY LOGFILE
DATAFILE
‘/dy/oracle/product/db10g/dbf/system01.dbf’,
‘/dy/oracle/product/db10g/dbf/undotbs01.dbf’,
‘/dy/oracle/product/db10g/dbf/sysaux01.dbf’,
‘/dy/oracle/product/db10g/dbf/htmldb01.dbf’
CHARACTER SET US7ASCII
;

RECOVER DATABASE
ALTER SYSTEM ARCHIVE LOG ALL;
ALTER DATABASE OPEN;
ALTER TABLESPACE TEMPTS1 ADD TEMPFILE ‘/dy/oracle/product/db10g/dbf/temp01.dbf’
SIZE 20971520 REUSE AUTOEXTEND OFF;

4) Run the “Create control file” script crdb.sql.

bash-2.05$ sqlplus “/as sysdba”

SQL*Plus: Release 10.2.0.1.0 – Production on Thu Jun 12 05:55:43 2008

Connected to an idle instance.

SQL> @crdb.sql
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size 1984184 bytes
Variable Size 243276104 bytes
Database Buffers 822083584 bytes
Redo Buffers 6397952 bytes

Control file created.

Media recovery complete.

System altered.

Database altered.

Tablespace altered.

SQL>

We are done. Database is now up and running. Note that this is a complete recovery. There is no loss of data, because all the data files and redo log files were intact.

Hope this helps !!

We are considering the scenario where we have the database in archive log mode and we have lost one of the active redo log file.

lets say that right now database is working fine and we are just starting it. All the files (Controlfiles, datafiles and online redo log files are intact).

1) Starting the database

SQL> startup
ORACLE instance started.

2) Archive log status

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /dy/oracle/product/db10g/archive/htmldb
Oldest online log sequence     0
Next log sequence to archive   1
Current log sequence           1
SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /dy/oracle/product/db10g/archive/htmldb
Oldest online log sequence     1
Next log sequence to archive   1
Current log sequence           3
3) Redo log group 2 is the current redo log and other two are active, meaning that they are required for recovery. When we say they are required for recovery means that the content has not been flushed to datafiles from database buffer cache. DBWR is yet to do that. This happens when checkpoint has just started and DBWR is yet to perform its job.

But at the same time you can see that these active redo log files has been archived by ARCH process. This is important for us.

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
———- ———- ———- ———- ———- — —————-
FIRST_CHANGE# FIRST_TIM
————- ———
1 1 2 104857600 1 YES ACTIVE
4384517 13-JUN-08

2 1 3 104857600 1 NO CURRENT
4384519 13-JUN-08

3 1 1 104857600 1 YES ACTIVE
4379112 13-JUN-08

SQL> select * from v$logfile;

GROUP# STATUS TYPE
———- ——- ——-
MEMBER
——————————————————————————–
IS_
—
1 ONLINE
/dy/oracle/product/db10g/dbf/redo01.log
NO

3 ONLINE
/dy/oracle/product/db10g/dbf/redo03.log
NO

GROUP# STATUS TYPE
———- ——- ——-
MEMBER
——————————————————————————–
IS_
—

2 ONLINE
/dy/oracle/product/db10g/dbf/redo02.log
NO

4) Lets remove one of the active redo log file.

bash-2.05$ rm /dy/oracle/product/db10g/dbf/redo03.log

5) Shut abort

bash-2.05$ sqlplus “/as sysdba”

SQL*Plus: Release 10.2.0.1.0 – Production on Fri Jun 13 02:16:22 2008

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> shut abort
ORACLE instance shut down.

6) Startup the database. This will fail as it wont be able to recover because it will not be able to access redo log group 3.

SQL> startup
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  1984184 bytes
Variable Size             243276104 bytes
Database Buffers          822083584 bytes
Redo Buffers                6397952 bytes
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: ‘/dy/oracle/product/db10g/dbf/redo03.log’
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3

7) Now we can recover database until cancel because just “recover database” will not be able to recover since the active redo log file is missing. Even though this file is archived, database wont be aware of this.

SQL> recover database until cancel;
ORA-00279: change 4380738 generated at 06/13/2008 02:12:35 needed for thread 1
ORA-00289: suggestion :
/dy/oracle/product/db10g/archive/htmldb/ARCH_657248900_1_1.ARC
ORA-00280: change 4380738 for thread 1 is in sequence #1

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
ORA-00279: change 4384517 generated at 06/13/2008 02:15:30 needed for thread 1
ORA-00289: suggestion :
/dy/oracle/product/db10g/archive/htmldb/ARCH_657248900_1_2.ARC
ORA-00280: change 4384517 for thread 1 is in sequence #2
ORA-00278: log file
‘/dy/oracle/product/db10g/archive/htmldb/ARCH_657248900_1_1.ARC’ no longer
needed for this recovery

ORA-00279: change 4384519 generated at 06/13/2008 02:15:31 needed for thread 1
ORA-00289: suggestion :
/dy/oracle/product/db10g/archive/htmldb/ARCH_657248900_1_3.ARC
ORA-00280: change 4384519 for thread 1 is in sequence #3
ORA-00278: log file
‘/dy/oracle/product/db10g/archive/htmldb/ARCH_657248900_1_2.ARC’ no longer
needed for this recovery

ORA-00308: cannot open archived log
‘/dy/oracle/product/db10g/archive/htmldb/ARCH_657248900_1_3.ARC’
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3

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: ‘/dy/oracle/product/db10g/dbf/system01.dbf’

Here it has applied all the archived log files, but still this is not recovered completely. This is where you should supply the current online redo log file.

8) Recover until cancel again and supply current redo log file which is redo log 2.

SQL> recover database until cancel;
ORA-00279: change 4384519 generated at 06/13/2008 02:15:31 needed for thread 1
ORA-00289: suggestion :
/dy/oracle/product/db10g/archive/htmldb/ARCH_657248900_1_3.ARC
ORA-00280: change 4384519 for thread 1 is in sequence #3

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/dy/oracle/product/db10g/dbf/redo02.log
Log applied.
Media recovery complete.

9) Once media recovery is complete, open database in reset log mode.

SQL> alter database open resetlogs;

Database altered.

SQL>

We were able to recover here after loosing the active redo log file, because that active redo log file was archived by archiver process.

This is a complete recovery and there is no data loss.

Hope this helps !!

Day: June 13, 2008

Recovering database after loss of current controlfile

Recovering database after loosing active redo log file after archiving