Author: Arju
Working as a database administrator you must have faced a scenario where all your controlfiles, online redo logs, spfile and pfile are lost and you need to recover your database as early as possible so as to maintain the high availability of data. In this article we will discuss such recovery scenario whereby you can recover your database even if all controlfile and redo logs are lost.
Step - I: Deleting all Files
Online Redo Log Files and Controlfile of Running Database:
First of all we will find the online redo log files and controlfile of the running database.
SQL> SELECT MEMBER FROM V$LOGFILE;
MEMBER
------------------------------
/ORADATA1/DB01/REDO01.LOG
/ORADATA1/DB01/REDO02.LOG
/ORADATA1/DB01/REDO03.LOG |
SQL> SELECT NAME FROM V$CONTROLFILE;
NAME
-------------------------------
/ORADATA1/DB01/CONTROL01.CTL
/ORADATA1/DB01/CONTROL02.CTL
/ORADATA1/DB01/CONTROL03.CT |
Online Redo Log Files and Controlfile of Running Database:
Now we will delete all copies of controlfiles and online redo log files one by one.
SQL> !RM /ORADATA1/DB01/CONTROL01.CTL
SQL> !RM /ORADATA1/DB01/CONTROL02.CTL
SQL> !RM /ORADATA1/DB01/CONTROL03.CTL |
SQL> !RM /ORADATA1/DB01/REDO01.LOG
SQL> !RM /ORADATA1/DB01/REDO02.LOG
SQL> !RM /ORADATA1/DB01/REDO03.LOG |
Step - II: Recovering Database
Create New Controlfile:
If ALTER DATABASE BACKUP CONTROLFILE TO TRACE command has been issued then you can use that file and modify it. However if no such command is issued then you will have to create new controlfile. Note that we will have to specify RESETLOGS option since all our online redo log files have been lost.
-BASH-3.00$ VI /ORADATA1/NEWCTL.CTL
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "DB01" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/ORADATA1/DB01/REDO01.LOG' SIZE 50M,
GROUP 2 '/ORADATA1/DB01/REDO02.LOG' SIZE 50M,
GROUP 3 '/ORADATA1/DB01/REDO03.LOG' SIZE 50M
DATAFILE
'/ORADATA1/DB01/SYSTEM01.DBF',
'/ORADATA1/DB01/UNDOTBS01.DBF',
'/ORADATA1/DB01/SYSAUX01.DBF',
'/ORADATA1/DB01/USERS01.DBF'
CHARACTER SET WE8ISO8859P1; |
Now we will run inside SQL*plus.
SQL> @/ORADATA1/NEWCTL.CTL |
Shutdown Abort Oracle Instance:
SQL> SHUTDOWN ABORT
ORACLE INSTANCE SHUT DOWN. |
Create pfile:
Now we will create the pfile.
-BASH-3.00$ VI /ORADATA1/MYPFILE01.PFILE
COMPATIBLE=10.2.0.1.0
DB_NAME=DB01
_ALLOW_RESETLOGS_CORRUPTION = TRUE
SGA_TARGET=400M
CONTROL_FILES='/ORADATA1/DB01/CONTROL01.CTL','/ORADATA1/ DB01/CONTROL02.CTL' |
Create Controlfile:
Now we will create the controlfile.
-BASH-3.00$ VI /ORADATA1/NEWCTL.CTL
STARTUP NOMOUNT
PFILE='/ORADATA1/MYPFILE01.PFILE'
CREATE CONTROLFILE REUSE DATABASE "DB01" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/ORADATA1/DB01/REDO01.LOG' SIZE 50M,
GROUP 2 '/ORADATA1/DB01/REDO02.LOG' SIZE 50M,
GROUP 3 '/ORADATA1/DB01/REDO03.LOG' SIZE 50M
DATAFILE
'/ORADATA1/DB01/SYSTEM01.DBF',
'/ORADATA1/DB01/UNDOTBS01.DBF',
'/ORADATA1/DB01/SYSAUX01.DBF',
'/ORADATA1/DB01/USERS01.DBF'
CHARACTER SET WE8ISO8859P1; |
Now we will run this script inside SQL*plus and mount the database.
SQL> @/ORADATA1/NEWCTL.CTL
ORACLE INSTANCE STARTED.
TOTAL SYSTEM GLOBAL AREA 413872600 BYTES
FIXED SIZE 2284376 BYTES
VARIABLE SIZE 192701474 BYTES
DATABASE BUFFERS 209264186 BYTES
REDO BUFFERS 6291744 BYTES
CONTROL FILE CREATED. |
Create Controlfile:
If we are restoring backup from previous then we might need to recover the database using BACKUP CONTROLFILE UNTIL CANCEL and then cancel.
Shutdown Oracle Instance and Dismount:
Now we will shutdown the database normal and then dismount the database.
SQL> SHUT IMMEDIATE;
ORA-01109: DATABASE NOT OPEN |
DATABASE DISMOUNTED.
ORACLE INSTANCE SHUT DOWN. |
Start Database with pfile:
Now we will start database with pfile.
SQL> STARTUP PFILE='/ORADATA1/MYPFILE01.PFILE';
ORACLE INSTANCE STARTED.
TOTAL SYSTEM GLOBAL AREA 413872600 BYTES
FIXED SIZE 2284376 BYTES
VARIABLE SIZE 192701474 BYTES
DATABASE BUFFERS 209264186 BYTES
REDO BUFFERS 6291744 BYTES
DATABASE MOUNTED.
ORA-01589: MUST USE RESETLOGS OR NORESETLOGS OPTION FOR DATABASE OPEN |
Open Database with RESETLOGS:
SQL> ALTER DATABASE OPEN RESETLOGS;
DATABASE ALTERED. |
Rebuild Database:
Finally we will rebuild the database immediately by either performing a full-database export or by creating a new and separate database then importing the export dump. It is necessary to rebuild database because the information in our data dictionary may be corrupted after setting _ALLOW_RESETLOGS_CORRUPTION=TRUE.
Note: Before running any option, please make sure that you have maintained a valid backup of your current database. Read Again!!
More Oracle Articles, Database Articles and DBA Tips
Database Security: Step by step guideline
Oracle Security Guide: Beware of all DBAs having full OS access!!
Great Tips for Removing Jobs from Job Queue Automatically!!
DBA Tips when Oracle Account is Locked!!
The power of Oracle Advisory Utilities for predicting I/O reduction!!
|
