Recover database after missing online redo logs and all controlfiles.

This example is based on,
You have lost all your current and backup of controlfiles.
You have avaiable your current data files.
You have lost all your online active , current redo log files.
You have lost your spfile and pfile.

In the scenario, I will show the procedure of how to recover database if you lose your all copies of controlfiles and all copies of redo log groups and members are lost.

1.
Let's start by deleting online redo log files and controlfile of my running database.

SQL> select MEMBER from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/oradata2/shaikdba/shaikdba/redo02.log
/oradata2/shaikdba/shaikdba/redo01.log
/oradata2/shaikdba/shaikdba/redo03.log

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/oradata2/shaikdba/shaikdba/control01.ctl
/oradata2/shaikdba/shaikdba/control02.ctl
/oradata2/shaikdba/shaikdba/control03.ctl

2.Delete all copies of controlfiles and online redo log files. Here with one command I have deleted. But before deleting with one such command be conscious that this command will not delete other important files. You can delete files one by one.
SQL> !rm /oradata2/shaikdba/shaikdba/control0*

SQL> !rm /oradata2/shaikdba/shaikdba/redo0*

3.Now it is task to recover my database. This procedure will begin by creating a new controlfile. If you have previously issued ALTER DATABASE BACKUP CONTROLFILE TO TRACE then use that file and modify. If you don't have any backup of controlfile then you have to create a new one as below.
Note that as you have lost your online redo log files you have to specify RESETLOGS option.

-bash-3.00$ vi /oradata2/ctl.ctl
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "SHAIKDBA" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oradata2/shaikdba/shaikdba/redo01.log' SIZE 50M,
GROUP 2 '/oradata2/shaikdba/shaikdba/redo02.log' SIZE 50M,
GROUP 3 '/oradata2/shaikdba/shaikdba/redo03.log' SIZE 50M
DATAFILE
'/oradata2/shaikdba/shaikdba/system01.dbf',
'/oradata2/shaikdba/shaikdba/undotbs01.dbf',
'/oradata2/shaikdba/shaikdba/sysaux01.dbf',
'/oradata2/shaikdba/shaikdba/users01.dbf'
CHARACTER SET WE8ISO8859P1
;
5.Save the script and run it inside SQL*plus.
SQL> shutdown abort
ORACLE instance shut down.

6. The recovery state I performed is to create an pfile.
-bash-3.00$ vi /oradata2/1.pfile
compatible=10.2.0.1.0
db_name=shaikdba
_allow_resetlogs_corruption = true
sga_target=400M
control_files='/oradata2/shaikdba/shaikdba/control01.ctl','/oradata2/shaikdba/shaikdba
/control02.ctl'

7.The next step is to create a controlfile.
-bash-3.00$ vi /oradata2/ctl.ctl
STARTUP NOMOUNT pfile='/oradata2/1.pfile'
CREATE CONTROLFILE REUSE DATABASE "SHAIKDBA" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oradata2/shaikdba/shaikdba/redo01.log' SIZE 50M,
GROUP 2 '/oradata2/shaikdba/shaikdba/redo02.log' SIZE 50M,
GROUP 3 '/oradata2/shaikdba/shaikdba/redo03.log' SIZE 50M
DATAFILE
'/oradata2/shaikdba/shaikdba/system01.dbf',
'/oradata2/shaikdba/shaikdba/undotbs01.dbf',
'/oradata2/shaikdba/shaikdba/sysaux01.dbf',
'/oradata2/shaikdba/shaikdba/users01.dbf'
CHARACTER SET WE8ISO8859P1
;

8.Create controlfile and mount the database.
SQL> @/oradata2/ctl.ctl
ORACLE instance started.

Total System Global Area 419430400 bytes
Fixed Size 2021216 bytes
Variable Size 121637024 bytes
Database Buffers 289406976 bytes
Redo Buffers 6365184 bytes

Control file created.
--Recover database if necessary.This might necessary if you restore backup from a previous. Then recover database using backup controlfile until cancel and then --cancel

9.Normal Shutdown the database.
SQL> shut immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

10.Start the database with the pfile.
SQL> startup pfile='/oradata2/1.pfile';
ORACLE instance started.

Total System Global Area 419430400 bytes
Fixed Size 2021216 bytes
Variable Size 121637024 bytes
Database Buffers 289406976 bytes
Redo Buffers 6365184 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

11. Open the database with the resetlogs option.
SQL> alter database open resetlogs;
Database altered.

Note that once the database is opened, then we must immediately rebuild the database. Database rebuild means

1) perform a full-database export,
2) create a brand new and separate database, and finally
3) import the export dump.
Before you try this option, ensure that you have a good and valid backup of the current database.

This is necessary to rebuild because after _allow_resetlogs_corruption the dictionary information may be corrupted.

Comments

Post a Comment

Popular posts from this blog

ORA-04062: timestamp of procedure has been changed

Problem Description In the database I have created one procedure named a as below. create or replace procedure a(a number) as begin insert into t1 values(1); commit; end; / Now after creating database link using remote database machine whenever I access this procedure "A" it executes successfully and I get value "1" in table t1. Like below in example where orastdby_m is the database link, shaik is the schema name and value 1 is the argument value though argument value is not used in the procedure. SQL> exec shaik.a@orastdby_m(1); PL/SQL procedure successfully completed. Now in the source database machine I changed the procedure as below. Though you can change anything like any literal; adding space or remove space. I changed value to be inserted from 1 to 2. create or replace procedure a(a number) as begin insert into t1 values(2); commit; end; / Now in the other database whenever I execute the procedure using database link it throws error ORA-04062. But subseq...
Read more

ORA-00939: too many arguments -when case arguments exceed 255

Problem Description In all Oracle 10.1g and oracle 10.2.0.1, 10.2.0.2, 10.2.0.3 whenever there is more than 255 arguments inside CASE statements then following error is returned. ORA-00939: too many arguments for function But surprisingly on Oracle 9.2 database the same code does not return any error. Cause of the Problem As we see the same code return error in 10g but it is well on 9.2. In fact this is problem of oracle 9.2 that it does not return any error. It should return error if there is more than 255 arguments inside CASE statements. In oracle 9.2 this is bug number 2909927 and this bug is fixed in oracle 10.1 and 10.2. That's why in 10.2 and 10.1 error returns if arguments exceed 255. Up to Oracle 10.2.0.3 and earlier version, the arguments of CASE statement is limit to 255 and it does not support exceed over 255 and hence error return if it exceed. However in Oracle 10.2.0.4 and later the arguments limit of CASE statement has increased to 32767. Solution of the Problem Opt...
Read more

ORA-31655: no data or metadata objects selected for job

Problem Description You are going to do data pump export operation in order to export objects based on filtering via EXCLUDE or INCLUDE parameter of expdp. In this example you wanted tables 'ACCOUNT_GROUP','ADDRESS','AREA_GROUP' and all the tables starting with word CV(like 'CV', 'CV_EXPERIENCE', 'CV_EDUCATION' etc) Your parameter file is like below. userid=smilebd/a directory=d dumpfile=b.dmp include =TABLE:"IN('ACCOUNT_GROUP','ADDRESS','AREA_GROUP')" include =TABLE:"LIKE 'CV%' " And you invoke expdp as expdp parfile=d:\parfile.txt from command line. But it fails with below message on my windows PC. C:\>expdp parfile=d:\parfile.txt Export: Release 10.1.0.4.2 - Production on Thursday, 29 January, 2009 14:53 Copyright (c) 2003, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc tion With the Partitioning, OLAP and Data Mining ...
Read more