Rman Case Studies

(1)

RMAN RECOVERY CASE STUDIES

Infosys Technologies Ltd. Electronics City, Hosur Road

Bangalore – 560 100 INDIA

T +91 80 28520 261 F +91 80 28520 362

www.infosys.com Document Name RMAN Recovery

case studies

Version.Rev 1.0

Created By Ranjan Kumar Patel Reviewed by Basavaraj Banakar

Approved By Date

REVISION HISTORY

Ver. Date Author Authorized

by Date Description

1.0 13.11.200

7 Ranjan Kumar Patel Basavaraj Banakar Different case studies for recovery using RMAN TABLE OF CONTENTS ABSTRACT AUDIENCE INFORMATION INPUT

____________________________________________________________________

_ RMAN Recovery case studies Page 1 of 34

Ver 1.0

(2)

PREREQUISITE CASE DESCRIPTION 1. ABSTRACT ... 2 2. AUDIENCE ... 2 3. INFORMATION ... 2

3.1 INPUT

...

3 3.2 PREREQUISTES

...

3 3.3 CASE DESCRIPTIONS

...

3

1. ABSTRACT

The backups for all the databases are very important are for DBAs and for the organization as well. DBAs can recover the whole database back to point of failure to allow normal activity after any database disaster. But sometimes we can not perform the recovery although we have the backups available because we are not sure which recovery process to follow i.e. tablespace level, datafile level or database level and that to complete or incomplete recovery. So we should be very sure as to which process of recovery to follow and the recovery should be very quick.

It is not a good idea to perform manual backup and recovery in most of the cases. We have to take care of lots of things as to where to store during back up and which file to restore etc. RMAN will take care of all those tasks by its own and requires no manual intervention. This document will give you some case studies for RMAN recovery. 2. AUDIENCE

Oracle DBA’s.

3. INFORMATION

o

Operating System: UNIX , WINDOWS

o

Database server: Oracle 9i & 10g

____________________________________________________________________

_ RMAN Recovery case studies Page 2 of 34

Ver 1.0

(3)

3.1INPUT

Following values will be used in this example. Machine Name: - Server1

Instance Name: - TEST User Name : - RMAN 3.2PREREQUISTES

You should have a database used as a catalog for RMAN. RMAN user must be created before taking any backups.

3.3 CASE DESCRIPTIONS Creating catalog

C:\Documents and Settings\ranjankumar_patel>rman catalog rman/rman@test

RMAN> create catalog; Catalog created

Register Target Database

C:\Documents and Settings\ranjankumar_patel>rman catalog rman/rman@test target s

ys/[email protected] RMAN> register database;

database registered in recovery catalog starting full resync of recovery catalog full resync complete

Changing the Configuration Default Configuration RMAN> show all

To take backup of controlfile automatically RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON; Not to take backup if the backup is already there RMAN> CONFIGURE BACKUP OPTIMIZATION ON; NOTE:

____________________________________________________________________

_ RMAN Recovery case studies Page 3 of 34

Ver 1.0

(4)

We don’t have to use OPEN RESETLOGS when we do an incomplete recovery in 10g. For 9i and previous version we have to use this command after incomplete recovery. After this command the log sequence number becomes to 1 i.e. the database is a fresh one and it can not go back to the previous database mode.

CASE1

How to recover System Tablespace Scenario

Our database is in ARCHIVELOG Mode. We take full RMAN backup everyday night. Now we found in the morning that the disk where the system datafile was there is crashed. There are two ways to recover the system tablespace.

• Tablespace Level

• Datafile Level

Taking the Full Backup of the Target

rman catalog rman/rman target sys/[email protected] RMAN> RUN {

2> ALLOCATE CHANNEL ch1 TYPE DISK

3> FORMAT='D:\RMAN_BKP\DF_%t_%s_%D'; 4> BACKUP DATABASE PLUS ARCHIVELOG; 5> RELEASE CHANNEL ch1;

6> }

Now the file system or disk drive where System datafile was there crashed. So we don’t have the system datafile to start the database. We can not make the System tablespace offline unlike others tablespace, so that we can

recover them from open mode.

Datafile Level Recovery

While starting up the target Database we will get following error. SQL> startup

ORACLE instance started.

Total System Global Area 135338868 bytes Fixed Size 453492 bytes

Variable Size 109051904 bytes Database Buffers 25165824 bytes Redo Buffers 667648 bytes Database mounted.

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file

____________________________________________________________________

_ RMAN Recovery case studies Page 4 of 34

Ver 1.0

(5)

ORA-01110: data file 1: 'D:\ORACLE\ORADATA\TEST\SYSTEM01.DBF'

Recovery using RMAN Step 1 Connect to RMAN

rman catalog rman/rman target sys/[email protected] Step 2 Run the following script

RMAN> run {

2> ALLOCATE CHANNEL ch1 TYPE DISK; 3> restore datafile 1;

4> recover datafile 1;

5> sql 'alter database open'; 6> }

The target database is in OPEN mode Step 3 Test the database is open or not SQL> select open_mode from v$database; OPEN_MODE

---READ WRITE

Tablespace Level Recovery Step 1: Connect to RMAN

rman catalog rman/rman target sys/[email protected] Step 2 Run the following script

RMAN> run {

2> ALLOCATE CHANNEL ch1 TYPE DISK; 3> restore tablespace system;

4> recover tablespace system; 5> sql 'alter database open'; 6> }

The target database is in OPEN mode Step 3: Test the database is open or not SQL> select open_mode from v$database; OPEN_MODE

---READ WRITE

____________________________________________________________________

_ RMAN Recovery case studies Page 5 of 34

Ver 1.0

(6)

CASE2

How to recover Non-System Tablespace Scenario

Our database is in ARCHIVELOG Mode. We take full RMAN backup everyday night. We take incremental back everyday. Now we found in the morning that the disk where the user datafile was there is crashed.

Proceedings:

1. Create a table before full backup. 2. Take the full backup (level 0) 3. Create another table

4. Take the level 1 backup 5. Create another table 6. Shutdown the database

7. Remove the datafile of Users tablespace at the OS level 8. Recover the datafile using RMAN

Idea behind the Recovery:

We can restore the datafile from level 0, add the changes from level 1, then recover by applying online redo logs and archived redo logs.

Before taking level 0 backup

CREATE TABLE T1( NO NUMBER(4)) TABLESPACE USERS; insert into t1 values(1); 3 times

select * from t2 t2 ---1 1 1 Commit;

Taking the Level 0 backup RMAN> RUN {

3> format='D:\RMAN_BKP\full_DF_%t_%s_%D'; 4> BACKUP

5> incremental level 0 database; 6> RELEASE CHANNEL ch1; 7> }

____________________________________________________________________

_ RMAN Recovery case studies Page 6 of 34

Ver 1.0

(7)

Before taking the Level 1 Backup

CREATE TABLE T2( NO NUMBER(4)) TABLESPACE USERS insert into t2 values(2); ---4 times

select * from t2 t2 --2 2 2 2 Commit;

Taking the Level 1 backup RMAN> RUN {

3> format='D:\RMAN_BKP\lev1_DF_%t_%s_%D'; 4> BACKUP

After taking the Incremental Backup

CREATE TABLE T3 (NO NUMBER(4))TABLESPACE USERS; insert into t3 values(3); --5 times

select * from t3 ; t3 --3 3 3 3 3 Commit;

Removed the user01.dbf datafile from the OS level

Error

SQL> startup

____________________________________________________________________

_ RMAN Recovery case studies Page 7 of 34

Ver 1.0

(8)

ORA-01157: cannot identify/lock data file 9 - see DBWR trace file ORA-01110: data file 9: 'D:\ORACLE\ORADATA\TEST\USERS01.DBF'

Recovery

Step 1: Connect to RMAN Step 2: Run the following script RMAN> run {

4> recover datafile 9;

5> sql 'alter database open';

6> sql 'alter tablespace users online'; 7> }

select * from t1 select * from t2 select * from t3

t1 t2 t3 --- -- --1 2 3 1 2 3 1 2 3 2 3 3 Case 3 (Part I)

Recovery using Backup Control File (Using control file from last backup) Scenario

We normally take a full backup with controlfile everyday. Now after taking the full backup we create a tablespace. Then we lost all online control files. The recovery possibly will be an incomplete one.

Priority:

We should always try to do recovery with the current control if possible; the second best option would be to create a new control file. Using the backup controlfile should be the last option to use as the database needs to be started up with the RESETLOGS option.

____________________________________________________________________

_ RMAN Recovery case studies Page 8 of 34

Ver 1.0

(9)

Recovery:

We will restore all the control files. Then we will do a recover database.

Then we will make the datafile of the new tablespace “DROP OFFLINE” Then open the database with OPEN RESETLOGS.

Reset the incarnation.

Take the Full Backup, as the incarnation will differ. 1. Incarnation before the loss of controlfile. RMAN> list incarnation of database;

List of Database Incarnations

DB Key Inc Key DB Name DB ID CUR Reset SCN Reset Time --- --- --- --- --- ---

---1 2 TEST 1915188144 YES 190578 12-MAR-07

2. We have the full backup of database with controlfile 3. We created one tablespace “data” datafile

“D:\ORACLE\ORADATA\TEST\DATA01.DBF” to make an entry in the controlfile after the backup.

4. Remove all the controlfiles at the OS level.

5. Recovery Steps, error during the start up of the database. SQL> startup

Variable Size 109051904 bytes Database Buffers 25165824 bytes Redo Buffers 667648 bytes

ORA-00205: error in identifying controlfile, check alert log for more info

Restore the backup control files RMAN> run {

2> allocate channel c1 type disk;

3> restore controlfile to 'D:\Oracle\oradata\test\CONTROL01.CTL'; 4> restore controlfile to 'D:\Oracle\oradata\test\CONTROL02.CTL';

____________________________________________________________________

_ RMAN Recovery case studies Page 9 of 34

Ver 1.0

(10)

5> restore controlfile to 'D:\Oracle\oradata\test\CONTROL03.CTL'; 6> release channel c1;

7> }

Error in opening the database SQL> alter database open; alter database open

*

ERROR at line 1:

ORA-01507: database not mounted Mount the database

SQL> alter database mount; Database altered.

Error in opening the database SQL> alter database open; alter database open

*

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

Error in recovery of the full database

As the recovery is using the backup control file where the entry for tablespace “data” was not there, so is the following warning.

RMAN> recover database; Starting recover at 27-JUN-07 using channel ORA_DISK_1 using channel ORA_DISK_2 starting media recovery

archive log thread 1 sequence 105 is already on disk as file D:\ORACLE\ORADATA\T

EST\REDO02.LOG

archive log filename=D:\ORACLE\ORADATA\TEST\REDO02.LOG thread=1 sequence=105

Oracle Error:

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01245: offline file 11 will be lost if RESETLOGS is done

____________________________________________________________________

_ RMAN Recovery case studies Page 10 of 34

Ver 1.0

(11)

ORA-01110: data file 11: 'D:\ORACLE\ORADATA\TEST\DATA01.DBF'

media recovery complete Finished recover at 27-JUN-07

Error in opening the database using RESETLOGS SQL> alter database open RESETLOGS;

alter database open RESETLOGS *

ERROR at line 1:

ORA-01245: offline file 11 will be lost if RESETLOGS is done

ORA-01110: data file 11: 'D:\ORACLE\ORADATA\TEST\DATA01.DBF'

Make the datafile in drop offline mode SQL> alter database datafile 11 offline for drop; Database altered.

Opening the database using RESETLOGS SQL> alter database open RESETLOGS; Database altered.

The datafile 11 corresponding to tablespace “data” is still there but we can not use that. Its in “ OFFLINE DROP” mode

SQL> select file_name from dba_data_files; FILE_NAME ---D:\ORACLE\ORADATA\TEST\SYSTEM01.DBF D:\ORACLE\ORADATA\TEST\UNDOTBS01.DBF D:\ORACLE\ORADATA\TEST\CWMLITE01.DBF D:\ORACLE\ORADATA\TEST\DRSYS01.DBF D:\ORACLE\ORADATA\TEST\EXAMPLE01.DBF D:\ORACLE\ORADATA\TEST\INDX01.DBF D:\ORACLE\ORADATA\TEST\ODM01.DBF D:\ORACLE\ORADATA\TEST\TOOLS01.DBF D:\ORACLE\ORADATA\TEST\USERS01.DBF D:\ORACLE\ORADATA\TEST\XDB01.DBF D:\ORACLE\ORADATA\TEST\DATA01.DBF 11 rows selected.

We can not use the datafile 11

____________________________________________________________________

_ RMAN Recovery case studies Page 11 of 34

Ver 1.0

(12)

SQL> create table test_raj(no number) tablespace data; create table test_raj(no number) tablespace data

*

ERROR at line 1:

ORA-01658: unable to create INITIAL extent for segment in tablespace DATA

We can check the the same datafile is in v$recover_file view SQL>select * from v$recover_file

FILE# ONLINE ONLINE_STATUS ERROR CHANGE# TIME --- --- --- - ---

---11 OFFLINE OFFLINE UNKNOWN ERROR 17313372 6/27/2007

3:55:54 1 row selected

RMAN> list incarnation of database; List of Database Incarnations

DB Key Inc Key DB Name DB ID CUR Reset SCN Reset Time --- --- --- --- ---

---1 2 TEST 1915188144 YES 190578 12-MAR-07 After crashing the above database intentionally and performing an incomplete recovery, we opened the database using “ALTER

DATABASE OPEN RESETLOGS”. At this point the recovery catalog needs to be reset as below.

RMAN> reset database;

new incarnation of database registered in recovery catalog starting full resync of recovery catalog

full resync complete

RMAN> list incarnation of database; starting full resync of recovery catalog full resync complete

List of Database Incarnations

DB Key Inc Key DB Name DB ID CUR Reset SCN Reset Time --- --- --- --- ---

---1 2 TEST 1915188144 NO 190578 12-MAR-07 1 3016 TEST 1915188144 YES 17313373 27-JUN-07

____________________________________________________________________

_ RMAN Recovery case studies Page 12 of 34

Ver 1.0

(13)

Now the Inc Key 3016 will be used for future recovery as shown by “CUR” Column. “CUR” column shows NO for Inc key 2, which was created before the ALTER DATABASE OPEN.

Note: After incomplete recovery we need to backup the whole database immediately.

Information regarding OFFLINE DROPPED or OFFLINED datafile If you have OFFLINE DROPPED or OFFLINED one of the datafile in a tablespace hot backup will not work. You will get the following error: SQL> alter tablespace rajiv1 begin backup;

alter tablespace rajiv1 begin backup *

ERROR at line 1:

ORA-01128: cannot start online backup - file 3 is offline ORA-01110: data file 3: 'D:\RAJIV1.DBF'

3.3.1 3.3.2 Fix

- When we run a “begin backup” command every file (part of tablespace) has to be ONLINE.

- There is no direct way to remove the datafile, once you have added any datafile

to the tablespace it will be there unless you drop the tablespace. - In other words, 'OFFLINE DROP' does no more good than 'OFFLINE'. - The ALTER DATABASE DATAFILE OFFLINE DROP command is not meant to allow you to remove a datafile. This is the only alternative in case of database in noarchivelog mode.

- Once the datafile is offline, Oracle no longer attempts to access it, but it is still considered part of that tablespace. This datafile is marked only as offline in the controlfile and there is no SCN comparison done between the controlfile and the datafile during startup (This also allows you to startup a database with a non-critical datafile missing). The entry for that datafile is not deleted from the controlfile to give us the opportunity to recover that datafile.

Case 3 (Part-II)

Recover using backup Controlfile

(Manually creating Controlfile or From Trace file) Scenario

____________________________________________________________________

_ RMAN Recovery case studies Page 13 of 34

Ver 1.0

(14)

We normally take a full backup with controlfile everyday. Now after taking the full backup we create a tablespace. Then we lost all online control files. The recovery possible will be an incomplete one.

Priority:

We should always try to do recovery with the current control if possible; the second best option would be to create a new control file. Using the backup controlfile should be the last option to use as the database needs to be started up with the RESETLOGS option.

Recovery:

We will generate the SQL for creating the controlfile and edit. We will create a controlfile from the above command.

Then we will do a recover database. If complete recovery not possible then we will do an incomplete recovery.

Then open the database with OPEN RESETLOGS. Reset the incarnation.

Take the Full Backup, as the incarnation will differ. Information about creating Controlfile

When the database is up and running, we will take a backup of control file to trace, so that we can recreate the controlfile when necessary. Either we can create the controlfile manually or from the trace file. It’s not a good practice to manually create a controlfile because we have to know each and every location of datafile, logfile etc. To create Control File following is the command.

CREATE CONTROLFILE [REUSE] DATABASE name

[LOGFILE filespec [, filespec] ...] RESETLOGS | NORESETLOGS [MAXLOGFILES integer]

[DATAFILE filespec [, filespec] ...] [MAXDATAFILES integer]

[MAXINSTANCES integer]

[ARCHIVELOG | NOARCHIVELOG] [SHARED | EXCLUSIVE]

Following is the command to generate the SQL for creating the controlfile. ALTER DATABASE BACKUP CONTROLFILE TO TRACE

Go to the “UDUMP” location, copy and paste the SQL to create a .sql file for creating the Control File . The contents in TRACE file will be like following # The following commands will create a new control file and use it

____________________________________________________________________

_ RMAN Recovery case studies Page 14 of 34

Ver 1.0

(15)

# to open the database.

# The contents of online logs will be lost and all backups will # be invalidated. Use this only if online logs are damaged. STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "TEST" RESETLOGS ARCHIVELOG -- SET STANDBY TO MAXIMIZE PERFORMANCE

MAXLOGFILES 50 MAXLOGMEMBERS 5 MAXDATAFILES 100 MAXINSTANCES 1 MAXLOGHISTORY 226 LOGFILE

GROUP 1 'D:\ORACLE\ORADATA\TEST\REDO01.LOG' SIZE 100M, GROUP 2 'D:\ORACLE\ORADATA\TEST\REDO02.LOG' SIZE 100M, GROUP 3 'D:\ORACLE\ORADATA\TEST\REDO03.LOG' SIZE 100M -- STANDBY LOGFILE DATAFILE 'D:\ORACLE\ORADATA\TEST\SYSTEM01.DBF', 'D:\ORACLE\ORADATA\TEST\UNDOTBS01.DBF', 'D:\ORACLE\ORADATA\TEST\CWMLITE01.DBF', 'D:\ORACLE\ORADATA\TEST\DRSYS01.DBF', 'D:\ORACLE\ORADATA\TEST\EXAMPLE01.DBF', 'D:\ORACLE\ORADATA\TEST\INDX01.DBF', 'D:\ORACLE\ORADATA\TEST\ODM01.DBF', 'D:\ORACLE\ORADATA\TEST\TOOLS01.DBF', 'D:\ORACLE\ORADATA\TEST\USERS01.DBF', 'D:\ORACLE\ORADATA\TEST\XDB01.DBF', 'D:\ORACLE\ORADATA\TEST\DATA01.DBF' CHARACTER SET WE8MSWIN1252

;

# Configure RMAN configuration record 1 VARIABLE RECNO NUMBER;

EXECUTE :RECNO :=

SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');

EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','DISK PARALLELISM 2');

EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('BACKUP OPTIMIZATION','ON');

EXECUTE :RECNO :=

SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CHANNEL','DEVICE TYPE DISK FORMAT ''d:\RMAN_BKP\DF_%t_%s_%D''');

# Recovery is required if any of the datafiles are restored backups,

____________________________________________________________________

_ RMAN Recovery case studies Page 15 of 34

Ver 1.0

(16)

# or if the last shutdown was not normal or immediate. RECOVER DATABASE USING BACKUP CONTROLFILE # Database can now be opened zeroing the online logs. ALTER DATABASE OPEN RESETLOGS;

# No tempfile entries found to add. #

Archiving is disabled Archiving is disabled

Remove the unnecessary comments. The files should look like the following. Make sure that no syntax error is there. Let’s take the following file as “ctl.sql” file

CTL.SQL

CREATE CONTROLFILE REUSE DATABASE "TEST" RESETLOGS ARCHIVELOG -- SET STANDBY TO MAXIMIZE PERFORMANCE

MAXLOGFILES 50 MAXLOGMEMBERS 5 MAXDATAFILES 100 MAXINSTANCES 1 MAXLOGHISTORY 226 LOGFILE

GROUP 1 'D:\ORACLE\ORADATA\TEST\REDO01.LOG' SIZE 100M, GROUP 2 'D:\ORACLE\ORADATA\TEST\REDO02.LOG' SIZE 100M, GROUP 3 'D:\ORACLE\ORADATA\TEST\REDO03.LOG' SIZE 100M -- STANDBY LOGFILE DATAFILE 'D:\ORACLE\ORADATA\TEST\SYSTEM01.DBF', 'D:\ORACLE\ORADATA\TEST\UNDOTBS01.DBF', 'D:\ORACLE\ORADATA\TEST\CWMLITE01.DBF', 'D:\ORACLE\ORADATA\TEST\DRSYS01.DBF', 'D:\ORACLE\ORADATA\TEST\EXAMPLE01.DBF', 'D:\ORACLE\ORADATA\TEST\INDX01.DBF', 'D:\ORACLE\ORADATA\TEST\ODM01.DBF', 'D:\ORACLE\ORADATA\TEST\TOOLS01.DBF', 'D:\ORACLE\ORADATA\TEST\USERS01.DBF', 'D:\ORACLE\ORADATA\TEST\XDB01.DBF' CHARACTER SET WE8MSWIN1252

;

Now after taking the backup of Controlfile to trace. Suppose we lost all our controlfiles. Recovery should be as following.

Start the database in nomount state SQL> startup nomount;

Create the controlfile by executing the .sql file (manual or from trace)

____________________________________________________________________

_ RMAN Recovery case studies Page 16 of 34

Ver 1.0

(17)

SQL> @D:\ctl.sql Control file created.

Now try to do an incomplete recovery of the database SQL> recover database until cancel using backup controlfile;

ORA-00279: change 17504261 generated at 06/28/2007 14:24:10 needed for thread 1

ORA-00289: suggestion : D:\ORACLE\ORADATA\ARCH1\ARC00006.001 ORA-00280: change 17504261 for thread 1 is in sequence #6

Specify log: {<RET>=suggested | filename | AUTO | CANCEL} AUTO

ORA-00308: cannot open archived log

'D:\ORACLE\ORADATA\ARCH1\ARC00006.001'

ORA-27041: unable to open file OSD-04002: unable to open file

O/S-Error: (OS 2) The system cannot find the file specified. ORA-00308: cannot open archived log

'D:\ORACLE\ORADATA\ARCH1\ARC00006.001' ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) The system cannot find the file specified.

The above is a warning saying we don’t have the ARC00006.001 file. Now we can open the database with “RESETLOGS” option.

SQL> alter database open resetlogs; Database altered.

List of incarnation

DB Key Inc Key DB Name DB ID CUR Reset SCN Reset Time

--- --- --- --- --- ---

---1 2 TEST ---19---15---188---144 NO 190578 12-MAR-07 1 3016 TEST 1915188144 YES 17313373 27-JUN-07 After opening the database in RESETLOGS we have to reset the database

RMAN> reset database; New list of incarnation

____________________________________________________________________

_ RMAN Recovery case studies Page 17 of 34

Ver 1.0

(18)

DB Key Inc Key DB Name DB ID CUR Reset SCN Reset Time --- --- --- --- --- --- ---1 2 TEST 1915188144 NO 190578 12-MAR-07 1 3016 TEST 1915188144 NO 17313373 27-JUN-07 1 3475 TEST 1915188144 YES 17504262 29-JUN-07

Take the full backup of the database after incomplete recovery RUN {

ALLOCATE CHANNEL ch1 TYPE DISK

FORMAT='D:\RMAN_BKP\DF_%t_%s_%D'; BACKUP DATABASE PLUS ARCHIVELOG; RELEASE CHANNEL ch1;

} Case 4

Incomplete Recovery (Using RESETLOGS)

We take cold backup everyday using RMAN by shutting down the database. So now if we take a cold backup at point A and at point B, there is a media failure and we lost all our online logfiles. Since we have lost all our log files, we have to do an incomplete recovery. We have used recover database until cancel and we can recover database until the last archived log file. And then we opened the database USING OPEN RESETLOGS option. Here the log sequence will be reset to 1 at point B. Now after few days at point C, another media failure occurred and we lost a datafile. Now all the online log files and the control files are intact. Now there are two possible cases.

1.

We restored the backup datafile from point A and tried to recover, but got error saying datafile is from a point before B. We restored the backup control file and tried to recover the database. We got error saying datafile 1 is from a point after B, when we restore the datafile 1 from the backup, it showed the other datafiles are from a point after B. So after restoring the database when we tried to recover the database, we can go up to the point B, not beyond that because Oracle didn’t recognize the log files after point B.

2. At point C, we took datafile offline, open the database, export the data from that tablespace (all the objects in that datafile will be in accessible), drop and recreate the tablespace and then import all the data taken from the export. Then shutdown the database and take a cold backup. So the data we entered in that tablespace has been lost.

____________________________________________________________________

_ RMAN Recovery case studies Page 18 of 34

Ver 1.0

(19)

We will consider the first case only. Scenario

A B C

We have a cold backup we had a incomplete recovery We faced

till this point. Here. We forgot to take a another

complete backup here. We used situation,

RESETLOGS option. Where

we

had to do an

incomplete recovery. Important point before and after using “OPEN RESETLOGS” option If RESETLOGS is done at a point in time, we can not restore the backup of database from before the RESETLOGS and recover through the RESETLOG POINT. This means that anytime the database is open with RESETLOGS option, we should take another backup immediately.

Proceedings



Take a full backup of the database (Point A)  Create a table “ranjan1” (USERS tablespace)  Switch the log file.

To have some data in the archive

 Insert one row and switch the log file log after full back up.

 Shutdown the database  Mount the database



Simulate an incomplete recovery (open the database using OPEN RESETLOGS option) (Point B)  Insert some rows in the table and switch the log file.

 This DML will be stored in the first archived log i.e. log seq 1  Shut down the database.



Remove the datafile of the USERS tablespace at the OS level. (simulate Media failure) (Point C)  Now we will try to start the database (Error: datafile can not be read)

 Recovery

• When tried recover the database after resetting the database with RMAN, Oracle could not recover the database saying target database incarnation is not current.

____________________________________________________________________

_ RMAN Recovery case studies Page 19 of 34

Ver 1.0

(20)

• So we have to reset the database to older incarnation

• Shutdown the database and open the database in nomount mode

• Restore the Controlfiles from the backup

• Mount the database

• Restore all the data files.

• Recover until the last SCN (Equal to SCN before using OPENRESTLOGS.)

• Reset the database. Important points done in this test:

Take full backup before deleting any datafile. After the backup create a table and then insert one row and switch the logfile each time to have the DDL and DML commands in archived log files. (Point A)

Full backup RMAN> RUN {

Create table “Ranjan1”, so that this table DDL will be in log seq 1 SQL> create table ranjan1 (no number) tablespace users;

Table created.

SQL> archive log list;

Database log mode Archive Mode Automatic archival Enabled

Archive destination D:\Oracle\oradata\arch1 Oldest online log sequence 1

Next log sequence to archive 1 Current log sequence 1 Switching the redo logfile. SQL> alter system switch logfile; System altered.

Next log sequence to archive 2 Current log sequence 2

____________________________________________________________________

_ RMAN Recovery case studies Page 20 of 34

Ver 1.0

(21)

Switching the redo logfile. SQL> alter system switch logfile; System altered.

Next log sequence to archive 3 Current log sequence 3 Insert a value 3 in the table SQL> insert into ranjan1 values(3); 1 row created.

SQL> commit; Commit complete.

Switching the redo logfile, to keep the DML in log seg 3. SQL> alter system switch logfile;

System altered. SQL> archive log list;

Next log sequence to archive 4 Current log sequence 4 Contents of the table Ranjan1 SQL> select * from ranjan1; NO

3

To see the last but one SCN archived

SQL> select archivelog_change#-1 from v$database; ARCHIVELOG_CHANGE#-1

18256317

SCN for each datafile at the datafile header, which must be same for all datafiles before doing incomplete recovery or resetlogs.

SQL> select file#, checkpoint_change# from v$datafile_header; FILE# CHECKPOINT_CHANGE#

---

---____________________________________________________________________

_ RMAN Recovery case studies Page 21 of 34

Ver 1.0

(22)

1 18256339 2 18256339 3 18256339 4 18256339 5 18256339 6 18256339 7 18256339 8 18256339 9 18256339 10 18256339 10 rows selected.

Shutdown the database SQL> shutdown immediate;

Simulate an incomplete Recovery Startup in mount mode

SQL> startup mount;

Incomplete recovery, as the last log sequence is 4 so we will recover after log seq 5.

RMAN> run{

2> allocate channel ch1 type disk;

3> recover database until sequence 5 thread 1; 4>

5> }

Opening the database in “OPEN RESETLOGS” mode, it will reset the log seq counter to 1.

SQL> alter database open resetlogs; Database altered.

Insert a row into ranjan1 table after the resetlogs, so that this DML will be stored in log seq 1 i.e. after resetlogs.

SQL> insert into ranjan1 values(10); 1 row created.

SQL> commit; Commit complete. SQL> archive log list;

____________________________________________________________________

_ RMAN Recovery case studies Page 22 of 34

Ver 1.0

(23)

Archive destination D:\Oracle\oradata\arch1

Oldest online log sequence 0

Next log sequence to archive 1 Current log sequence 1

Swithch the log file. SQL> alter system switch logfile; System altered. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination D:\Oracle\oradata\arch1 Oldest online log sequence 1

Next log sequence to archive 2 Current log sequence 2

SCN for each datafile at the datafile header, which must be same for all datafiles after doing incomplete recovery or resetlogs. SQL> select file#,checkpoint_change# from v$datafile_header; FILE# CHECKPOINT_CHANGE# --- 1 18256586 2 18256586 3 18256586 4 18256586 5 18256586 6 18256586 7 18256586 8 18256586 9 18256586 10 18256586 10 rows selected.

Shutdown the database SQL> shutdown immediate; Recovery

SQL> startup mount; ORACLE instance started.

____________________________________________________________________

_ RMAN Recovery case studies Page 23 of 34

Ver 1.0

(24)

Database mounted.

SQL> alter database open; alter database open

*

ERROR at line 1:

Tried recover the database RMAN> run {

2> ALLOCATE CHANNEL ch1 TYPE DISK; 3> recover database;

4> } RMAN-00571:

================================================ ===========

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571:

================================================ ===========

RMAN-03002: failure of recover command at 07/03/2007 11:25:59

RMAN-06004: ORACLE error from recovery catalog database: RMAN-20003: target data

base incarnation not found in recovery catalog

We have to reset the database for a new incarnation as we had an incomplete recovery.

List of incarnation before resetting the database. 3986 is the old incarnation before resetlogs.

RMAN> list incarnation; List of Database Incarnations

DB Key Inc Key DB Name DB ID CUR Reset SCN Reset Time --- --- --- --- --- --- ---1 2 TEST 1915188144 NO 190578 12-MAR-07 1 3016 TEST 1915188144 NO 17313373 27-JUN-07 1 3475 TEST 1915188144 NO 17504262 29-JUN-07 1 3828 TEST 1915188144 NO 18244075 03-JUL-07

____________________________________________________________________

_ RMAN Recovery case studies Page 24 of 34

Ver 1.0

(25)

1 3561 TEST 1915188144 NO 18244413 02-JUL-07 1 3986 TEST 1915188144 YES 18253967 03-JUL-07 1 3867 TEST 1915188144 NO 18255353 03-JUL-07 Reset database

RMAN> reset database;

List of incarnation after resetting the database. 4027 is the new incarnation after resetlogs.

RMAN> list incarnation; List of Database Incarnations

DB Key Inc Key DB Name DB ID CUR Reset SCN Reset Time --- --- --- --- --- ---1 2 TEST 1915188144 NO 190578 12-MAR-07 1 3016 TEST 1915188144 NO 17313373 27-JUN-07 1 3475 TEST 1915188144 NO 17504262 29-JUN-07 1 3828 TEST 1915188144 NO 18244075 03-JUL-07 1 3561 TEST 1915188144 NO 18244413 02-JUL-07 1 3986 TEST 1915188144 NO 18253967 03-JUL-07 1 3867 TEST 1915188144 NO 18255353 03-JUL-07 1 4027 TEST 1915188144 YES 18256585 03-JUL-07

For restoring and recovering the data from old incarnation (Till point A), we have to use the older incarnation i.e. 3986

Resetting to older incarnation

RMAN> RESET DATABASE TO INCARNATION 3986; database reset to incarnation 3986 in recovery catalog Now tried to recover the database

RMAN> run {

2> ALLOCATE CHANNEL ch1 TYPE DISK; 3> recover database;

____________________________________________________________________

_ RMAN Recovery case studies Page 25 of 34

Ver 1.0

(26)

4> } RMAN-00571:

================================================ ===========

RMAN-00571:

================================================ ===========

RMAN-06004: ORACLE error from recovery catalog database: RMAN-20011: target data

base incarnation is not current in recovery catalog

Tried restoring and recovering the whole database RMAN> run {

2> ALLOCATE CHANNEL ch1 TYPE DISK; 3> restore database; 4> recover database; 5> 6> } RMAN-00571: ================================================ ===========

RMAN-00571:

================================================ ===========

RMAN-03002: failure of restore command at 07/03/2007 11:33:43

RMAN-06004: ORACLE error from recovery catalog database: RMAN-20011: target data

base incarnation is not current in recovery catalog

RMAN> run {

2> ALLOCATE CHANNEL ch1 TYPE DISK;

3> recover database until sequence 3 thread 1; 4> }

RMAN-00571:

================================================ ===========

RMAN-00571:

================================================ ===========

____________________________________________________________________

_ RMAN Recovery case studies Page 26 of 34

Ver 1.0

(27)

RMAN-06094: datafile 9 must be restored

So we have to restore the backup control file because the older incarnation has a different control file. And then we have to recover. Shutdown the database

SQL> shutdown immediate;

Remove the controlfile from its location at the OS level(this is optional)

Starting the database in nomount mode because there is no controlfile. So database can not be mounted.

Starting the database SQL> startup mount; ORACLE instance started.

ORA-00205: error in identifying controlfile, check alert log for more info

Alert log file has the following information

ORA-00202: controlfile: 'D:\Oracle\oradata\test\CONTROL01.CTL' ORA-27041: unable to open file

OSD-04002: unable to open file

O/S-Error: (OS 2) The system cannot find the file specified. Tue Jul 03 11:44:27 2007

ORA-205 signalled during: ALTER DATABASE MOUNT...

Restoring the controlfiles RMAN> run {

2> allocate channel c1 type disk;

3> restore controlfile to 'D:\Oracle\oradata\test\CONTROL01.CTL'; 4> restore controlfile to 'D:\Oracle\oradata\test\CONTROL02.CTL'; 5> restore controlfile to 'D:\Oracle\oradata\test\CONTROL03.CTL'; 6> release channel c1;

7> }

Mount the database SQL> alter database mount;

____________________________________________________________________

_ RMAN Recovery case studies Page 27 of 34

Ver 1.0

(28)

Tried recovering the database. Got the following error “for restoring the file 9”.

RMAN> run{

2> allocate channel ch1 type disk;

3> recover database until sequence 5 thread 1; 4>

5> }

RMAN-00571:

================================================ ===========

RMAN-00571:

================================================ ===========

RMAN-03002: failure of recover command at 07/03/2007 11:51:10 RMAN-06094: datafile 9 must be restored

RMAN-06166: datafile 8 cannot be recovered …….

Restore datafile 9 RMAN> run {

RMAN-00571:

================================================ ===========

RMAN-00571:

================================================ ===========

RMAN-03002: failure of recover command at 07/03/2007 11:55:14 RMAN-06163: some datafiles cannot be recovered, aborting the RECOVER command

RMAN-06166: datafile 10 cannot be recovered RMAN-06166: datafile 8 cannot be recovered …..

We have to restore all the datafile from the backup and then do an incomplete recovery of the database. During recovery Oracle asking for a log. Its not an error, its just a warning. So after this if we do a “OPEN RESETLOGS” till the last available log.

RMAN> run {

____________________________________________________________________

_ RMAN Recovery case studies Page 28 of 34

Ver 1.0

(29)

2> ALLOCATE CHANNEL ch1 TYPE DISK; 3> restore database;

RMAN-00571:

================================================ ===========

RMAN-00571:

================================================ ===========

RMAN-03002: failure of recover command at 07/03/2007 12:53:45

RMAN-06054: media recovery requesting unknown log: thread 1 scn

18256318

Check for last SCN in header of all datafiles

SQL> select file#,checkpoint_change# from v$datafile_header; FILE# CHECKPOINT_CHANGE# --- 1 18257590 2 18257590 3 18257590 4 18257590 5 18257590 6 18257590 7 18257590 8 18257590 9 18255586 10 18257590 10 rows selected. Do an “OPEN RESETLOGS” SQL> alter database open resetlogs; Database altered. So we have the 1st_{row entered before point B} SQL> select * from ranjan1; NO 3

Do a reset database after “OPEN RESETLOGS” and take a full backup. RMAN> reset database;

____________________________________________________________________

_ RMAN Recovery case studies Page 29 of 34

Ver 1.0

(30)

List of incarnation RMAN> list incarnation; List of Database Incarnations

DB Key Inc Key DB Name DB ID CUR Reset SCN Reset Time --- --- --- --- --- --- ---1 3986 TEST 1915188144 NO 18253967 03-JUL-07 1 3867 TEST 1915188144 NO 18255353 03-JUL-07 1 4144 TEST 1915188144 YES 18256319 03-JUL-07 1 4027 TEST 1915188144 NO 18256585 03-JUL-07 CASE 5

Recovery of a new datafile, when this new datafile is not backed up in the last backup

Situation:

We took backup everyday at night. One day we found there is some space issue with a tablespace USERS. So we add a new datafile to this tablespace. We thought we will take full backup in the night so didn’t back up the new datafile. Now a media failure occurred and we lost the new datafile. Since we don’t have the new datafile in the yesterday’s backup, following is the way to recover.

Assumptions:

We have all the archived logs and online redo logs intact. Recovery

• Open the database in mount state.

• Create the same datafile which is lost.

• For recovery we can use current control file or backup control file having the entry for the new datafile.

• Recover the datafile so that the online redo logs and archived logs will be applied.

• Open the database Steps involved in this document

• Full backup taken

____________________________________________________________________

_ RMAN Recovery case studies Page 30 of 34

Ver 1.0

(31)

• Added a new datafile to USERS tablespace.

• Switched the log.

• Created a table TT3 in the same tablespace.

• Switched the log.

• Inserted one row to TT3 and switched again.

• Shutdown the database.

• Removed the new datafile added to simulate a media failure.

• Startup the database in mount mode we can use current control file or backup control file having the entry for the new datafile.

• Create the same datafile.

• Recover the datafile using RMAN.

• Open the database. Take full backup

RMAN> RUN {

Add a datafile to the tablespace USERS SQL> alter tablespace users add datafile

'D:\ORACLE\ORADATA\TEST\USERS02.DBF' size 30m autoextend on; Tablespace altered.

See the log sequence SQL> archive log list;

Next log sequence to archive 13 Current log sequence 13 Switch the log file

SQL> alter system switch logfile; System altered.

Archive destination D:\Oracle\oradata\arch1

____________________________________________________________________

_ RMAN Recovery case studies Page 31 of 34

Ver 1.0

(32)

Oldest online log sequence 12 Next log sequence to archive 13 Current log sequence 14 Create a table in tablespace users

SQL> create table tt3( no number) tablespace users; Table created.

Switch the log file

Next log sequence to archive 15 Current log sequence 15 SQL> insert into tt3 values(4); 1 row created.

SQL> commit; Commit complete. Switch the log file

Next log sequence to archive 16 Current log sequence 16 Content of the table TT3 SQL> select * from tt3; NO

4

Shut down the database and remove the datafile

'D:\ORACLE\ORADATA\TEST\USERS02.DBF' of the USERS tablespace.

____________________________________________________________________

_ RMAN Recovery case studies Page 32 of 34

Ver 1.0

(33)

Recovery

Startup the database SQL> startup

Recreate the datafile which is lost. The file name should be same as the lost file.

SQL> alter database create datafile

'D:\ORACLE\ORADATA\TEST\USERS02.DBF' ; Database altered.

Recover the datafile, so that the archive logs and the online redo logs will be applied.

RMAN> run {

2> ALLOCATE CHANNEL ch1 TYPE DISK;

3> recover datafile 'D:\ORACLE\ORADATA\TEST\USERS02.DBF'; 4> release channel ch1;

5> }

Open the database SQL> alter database open; Database altered.

Contents of the table TT3 SQL> select * from tt3; NO

4

____________________________________________________________________

_ RMAN Recovery case studies Page 33 of 34

Ver 1.0

(34)