Instance crash recovery, Why,When,How in Oracle 10g

Whenever, Accidently power failure causes server shutdown, Shutdown abort and any other reason that cause database to go down in inconsistent way requires instance recovery. Before we go deep into this we need to understand working of SMON and SCN.

What is this SMON:- SMON is one of the MUST HAVE back ground process. And this is the process that recovers database from instance crash.  SMON checks the SCN in all the data files headers and compare this with SCN stored in Control file. If these two matches means database is in consistent state and can be opened.

What is System Change Number (SCN):- This is the number  (always increasing) incremented whenever a Commit has happened or  AQ, SMON,PMON has completed their work. To get current SCN we need to use DBMS_FLASHBACK.Get_System_Change_Number. SCN is also used in Flashback Query

Eg Select * from scott.emp as of scn 1232097

SCN is checked whenever Oracle starts up the database. SMON compares the SCN of Checkpoint_Change#(v$database.Chechpoint_Change# with v$datafile.checkpoint_change# and v$datafile_header.checkpoint_change#) and if all the SCN are same then database is in consistent state and can be opened.  

SCN plays a vital role for providing consistent reads. Basically, it works as follows: The query reads a db block. Beside other information block  also contains the information regarding when last time block was changed.. If this SCN is greater than the SCN that was in place when (our) query began, it means that the block was changed after we have started our query. So we have to find an older version of the block. If this block is found in the rollback segments, we use it for our query.

The v$ views use change# to report SCN values. Hence, it can be argued that SCN means System Change Number, not System Commit Number.

When exporting, an SCN can be specified with the exp_flashback_scn export parameter.

More on SCN

There are 3 SCNs basically in control file

1.            Checkpoint SCN

2.            Stop SCN

3.            Thread checkpoint SCN

Checkpoint SCN is the datafile checkpoint SCN when checkpoint happens for datafile. This checkpoint SCN is recorded in datafile header as well.

Stop SCN is the SCN which gets recoreded in control file when datafile is taken in begin backup mode or when datafile is taken offline. This is the checkpoint at a point when datafile header is freezed.

Thread Checkpoint SCN is the one related to online redo log files. This SCN gets generated when ever transaction get recoreded in online redo log file. When we shut down database with normal or immediate option, all these SCN are synchronized and made equal.

/

Eg.

SQL> select banner from v$version;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE    10.2.0.1.0      Production

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 – Production

Now! Check the Database checkpoint  change number using V$DATABASE. With this number (CHECKPOINT_CHAGNE#) , all the number (CHECKPOINT_CHANGE#) of datafile header should match.

SQL> select name,checkpoint_change# from v$database;

NAME      CHECKPOINT_CHANGE#

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

SEED                 1151635

SQL> select name,checkpoint_change# from v$datafile;

Name                                                                                                    Checkpoint_Change#        Last_Change#

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

D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\SYSTEM01.DBF                   1151635  1151635

D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\UNDOTBS01.DBF                               1151635  1151635

D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\SYSAUX01.DBF                   1151635  1151635

D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\USERS01.DBF                      1151635  1151635

D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\EXAMPLE01.DBF                                1151635  1151635

D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\RMAN_TBS01.DBF            1151635  1151635

Now, The CHECKPOINT_CHANGE# of Database and Datafile Header are same and more importantly Start SCN (Checkpoint_change#) and Stop SCN (Last_Change#) are same means START and STOP SCN are same and this confirms that our database is consistent state and does not requires any kind of recovery.

So when you use shutdown immediate or Shutdown normal, Database goes down with consistent state. However, this is not the case with “Shutdown Abort” statement.

To understand fully, We need to understand actual working of “Shutdown” process,  SCN ,Checkpoint_Change#, Last_Change#.

Shutdown Process:

Shutdown Immediate/Shutdown Normal: This is the most clean method to take database down. Using this statement, DB goes down in consistent state. Following are the stages:

Shutdown Immediate:

SQL> select open_mode from v$database;

OPEN_MODE

----------

READ WRITE

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

Internally, First Oracle closes all the Background (SMON, PMON, CHKPT, DBWR, CJQ0,MMNL,MMON) processes . Secondly if Archival is enabled then it stops. Thirdly, All  uncommitted transaction are rolled back, further logons stops. Checkpoint  happens, All the blocks ( Changed, Committed)  are flushed to Hard disk.  If any trigger (System Trigger) is written on BEFORE SHUTDOWN system event than it fires.

Instances withers away, All Locks, Enaques  (TX) are released. “ALTER DATABASE CLOSE NORMAL” and “ALTER DATABASE DISMOUNT” statements are executed. After all these happenings our database is now SHUT.

In this scenario, Before closing down Oracle does CHECKPOINT that makes database state consistent. Means no Online REDO logs will be required to open database. Once you open the database Checkpoint_Change#  of Database (Controlfile),Datafile(s) and Datafile_Headers will be same.

Checkpoint change number of controlfile.

SQL> Select Checkpoint_Change#,name from v$database;

CHECKPOINT_CHANGE#                                               NAME

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

1202269                                                                SEED

Checkpoint change number of Datafile(s).

SQL>Select Checkpoint_Change#,name,last_change# from v$datafile;

CHECKPOINT_CHAGNE#   NAME                                                                                                                               Last_Change#  

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

1202269                                D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\SYSTEM01.DBF   1151635

1202269                                D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\UNDOTBS01.DBF               1151635

1202269                                D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\SYSAUX01.DBF   1151635

1202269                                D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\USERS01.DBF        1151635

1202269                                D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\EXAMPLE01.DBF                  1151635

1202269                                D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\RMAN_TBS01.DBF1151635

Checkpoint Change number in Header of Datafile(s).

SQL>Select dh.checkpoint_change#,df.name from v$datafile_header dh,v$datafile df

where df.file#=dh.file#

CHECKPOINT_CHAGNE#   NAME                                                               CHECKPOINT_CHAGNE#                                                                              

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

1202269                                D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\SYSTEM01.DBF  

1202269                                D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\UNDOTBS01.DBF              

1202269                                D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\SYSAUX01.DBF  

1202269                                D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\USERS01.DBF     

1202269                                D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\EXAMPLE01.DBF               

1202269                                D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\RMAN_TBS01.DBF           

In this process, Every data file has the same CHECKPOINT_CHANGE# number as DATABASE has. So database does not need any kind of recovery (INSTANCE) recovery.

Now! See how SHUTDOWN ABORT just like a crash and why it needs recovery.

SQL> shutdown abort;

ORACLE instance shut down.

In  this scenario, Database has not done anything to make things consistent instead it is just crashed.

However, unlike Media recovery where we need not to apply Archived REDO logs all the changes stored in Online REDO Log files will be re-applied and instance will recover itself.

In Other words START SCN != STOP SCN so recovery is required.

SQL> startup mount;

ORACLE instance started.

Total System Global Area  612368384 bytes

Fixed Size                  1250428 bytes

Variable Size             230689668 bytes

Database Buffers          373293056 bytes

Redo Buffers                7135232 bytes

Database mounted.

SQL> Select name,Checkpoint_Change# from v$database;

NAME      CHECKPOINT_CHANGE#

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

SEED                 1260508

SQL>Select Checkpoint_Change#,name,last_change# from v$datafile;

CHECKPOINT_CHAGNE#   NAME                                                                                                                               Last_Change#  

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

1260508                                D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\SYSTEM01.DBF                   Null

1260508                                D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\UNDOTBS01.DBF                              Null

1260508                                D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\SYSAUX01.DBF                   Null

1260508                                D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\USERS01.DBF                      Null

1260508                                D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\EXAMPLE01.DBF                               Null

1260508                                D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\RMAN_TBS01.DBF            Null

Next time DBA starts up the database, it with out asking any recovery will automatically apply contents of Online REDO log files (ROLLFORD) and will open the database.

SQL> startup

ORACLE instance started.

Total System Global Area  612368384 bytes

Fixed Size                  1250428 bytes

Variable Size             234883972 bytes

Database Buffers          369098752 bytes

Redo Buffers                7135232 bytes

Database mounted.

Database opened.

I have tried to put the instance recovery process in clear and precise words, but still if anything is missing then any correction, suggestion are most welcome.

System datafile recovery

System datafile lost:-- In this scenario,Hardware failure (Media failure) causes loss of System01.dbf file. And when DBA tries to start the database gets following error:

SQL> startup

ORACLE instance started.

Total System Global Area  612368384 bytes

Fixed Size                  1250428 bytes

Variable Size             176163716 bytes

Database Buffers          427819008 bytes

Redo Buffers                7135232 bytes

Database mounted.

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

ORA-01110: data file 1: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\SYSTEM01.DBF'

Now! To see any message/note regarding this we can check DBWR trace file located at

D:\oracle\product\10.2.0\admin\seed\bdump location. The naming convention of trace file is like

SID_DBWR_<srno>.trc. This trace file contains following message:
Dump file d:\oracle\product\10.2.0\admin\seed\bdump\seed_dbw0_1100.trc

Tue Nov 15 15:43:10 2011

ORACLE V10.2.0.1.0 - Production vsnsta=0

vsnsql=14 vsnxtr=3

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

Windows Server 2003 Version V5.2 Service Pack 2

CPU                 : 2 - type 586, 1 Physical Cores

Process Affinity    : 0x00000000

Memory (Avail/Total): Ph:552M/2037M, Ph+PgF:1134M/3935M, VA:1302M/2047M

Instance name: seed

Redo thread mounted by this instance: 1

Oracle process number: 5

Windows thread id: 1100, image: ORACLE.EXE (DBW0)

*** SERVICE NAME:() 2011-11-15 15:43:10.613

*** SESSION ID:(167.1) 2011-11-15 15:43:10.613

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

ORA-01110: data file 1: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\SYSTEM01.DBF'

ORA-27041: unable to open file

OSD-04002: unable to open file

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

Recovery step in this scenario are:

1:- Take DBF offline;

2:- Restore DBF from Backup;

3:- Recover  datafile;

4:- Take DBF online.

Before we proceed with Recovery, We check the existence of backup of Datafile 1(System01.dbf).

C:\ rman target / catalog rman_user/rmRecovery Manager: Release 10.2.0.1.0 - Production on Tue Nov 15 16:56:22 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: SEED (DBID=848131167, not open)

connected to recovery catalog databasean_user@seed_82

RMAN> list backup of datafile 1;

List of Backup Sets

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

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

1248    Full    134.63M    DISK        00:00:41     15-NOV-11

        BP Key: 1249   Status: AVAILABLE  Compressed: YES  Tag: FULL_DB_BKUP

        Piece Name: D:\ORACLE\RMAN_BKUP\SEED\SEED_%R_01MRNTAO.BKP

  List of Datafiles in backup set 1248

  File LV Type Ckp SCN    Ckp Time  Name

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

  1       Full 1150785    15-NOV-11 D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\SYSTEM01.DBF

Now! We can see that this particular backupset can be used for Recovery of System data file.

Step1:- Take System01.dbf (1) offline

RMAN> Sql "Alter Database Datafile 1 Offline";

sql statement: Alter Database Datafile 1 Offline

Step 2:- Restore Datafile 1 from backup

RMAN> Restore datafile 1;

Starting restore at 15-NOV-11

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=155 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00001 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\SYSTEM01.DBF

channel ORA_DISK_1: reading from backup piece D:\ORACLE\RMAN_BKUP\SEED\SEED_%R_01MRNTAO.BKP

channel ORA_DISK_1: restored backup piece 1

piece handle=D:\ORACLE\RMAN_BKUP\SEED\SEED_%R_01MRNTAO.BKP tag=FULL_DB_BKUP

channel ORA_DISK_1: restore complete, elapsed time: 00:00:56

Finished restore at 15-NOV-11

Step 3:- Recover the datafile

RMAN> Recover datafile 1;

Starting recover at 15-NOV-11

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:02

Finished recover at 15-NOV-11

Step 4:- Take datafile online

RMAN> Sql "Alter database datafile 1 online";

sql statement: Alter database datafile 1 online

Note:- The timing of this recovery varies and depends upon the DB size,Number of Archive REDO log to apply.

Any comment/Suggestion will be welcome.

Uniqueness using Non-Unique Index

How to add Primary Key index on table having duplicate data.

Many a times at our work location(s) it has been argued that only Unique Index can support Uniqueness in Table column.

Well! That’s not the case. A Non-Unique Index can also support Uniqueness in Column of Table.

I have demonstrated this in following statements.

 A table with Unique values.

SQL> Create table scott.test2 as select level id1 from dual connect by level<12;

  2

SQL> /

Table created.

Updated table to contain some duplicate values in ID1 Column.

SQL> Update scott.test2 set id1=3 where id1 in (1,2,4,5);

  2

SQL> /

4 rows updated.

SQL> commit;

Commit complete.

Now! Table Scott.TEST2 contains duplicate data in its ID1 Columns.

SQL> select * from scott.test2;

       ID1

----------

         3

         3              (Duplicate Data)

         3

         3

         3

         6

         7

         8

         9

        10

        11

11 rows selected.

Created a non-Unique(Default! If Create Statement is given by User) Index on SCOTT.TEST2.ID1 Column.

SQL> Create index t2ix on scott.test2(id1) compute statistics

  2  ;

Index created.

SQL>  select Index_name,Table_name,Table_owner,UNIQUENESS from dba_indexes where index_name='T2IX';

INDEX_NAME                     TABLE_NAME             TABLE_OWNER                    UNIQUENES

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

T2IX                                        TEST2                                   SCOTT                          NONUNIQUE

Trying to add Primary Key on SCOTT.TEST2.ID1 column but getting obvious error.

SQL> ALTER TABLE SCOTT.TEST2

  2  ADD CONSTRAINT T2_PK PRIMARY KEY(ID1);

ADD CONSTRAINT T2_PK PRIMARY KEY(ID1)

               *

ERROR at line 2:

ORA-02437: cannot validate (SCOTT.T2_PK) - primary key violated

Same error.

SQL> ALTER TABLE SCOTT.TEST2

  2  ADD CONSTRAINT T2_PK PRIMARY KEY(ID1) USING INDEX T2IX;

ADD CONSTRAINT T2_PK PRIMARY KEY(ID1) USING INDEX T2IX

               *

ERROR at line 2:

ORA-02437: cannot validate (SCOTT.T2_PK) - primary key violated

However! Trick lies in ENABLE and NOVALIDATE clauses.

Enable:- New Constraint is implemented. New Data will only be inserted if its Unique.

NoValidate:- Old data not to be  checked. Just ignore the old duplicate data.

SQL> ALTER TABLE SCOTT.TEST2

  2  ADD CONSTRAINT T2_PK PRIMARY KEY(ID1) USING INDEX T2IX ENABLE NOVALIDATE;

Table altered.

SQL> Insert into scott.test2 values(3)

  2  ;

Insert into scott.test2 values(3)

*

ERROR at line 1:

ORA-00001: unique constraint (SCOTT.T2_PK) violated

SQL> Select Owner,Constraint_name,Constraint_type,Table_name,Index_name from dba_constraints where table_name='TEST2'

  2  ;

OWNER                          CONSTRAINT_NAME                C TABLE_NAME                     INDEX_NAME

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

SCOTT                                            T2_PK                                               P TEST2                          T2IX

This is easy and Simple.

Any suggestion/Correction are always most welcome.

Database File recovery

Data File recovery is normal scenario of Dba Activities. Many a times it happens that we take any Data File Offline and when I try to makeit Online we get following error:

ALTER DATABASE DATAFILE 4 ONLINE;

Error starting at line 27 in command:

ALTER DATABASE DATAFILE 4 ONLINE

Error report:

SQL Error: ORA-01122: database file 4 failed verification check

ORA-01110: data file 4: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\USERS01.DBF'

ORA-01200: actual file size of 640 is smaller than correct size of 960 blocks

01122. 00000 -  "database file %s failed verification check"

*Cause:    The information in this file is inconsistent with information

           from the control file. See accompanying message for reason.

*Action:   Make certain that the db files and control files are the correct

           files for this database.

In above scenario , My Database is in Archive log and it is 10.2 release.   Problem is once I take any DBF offline and try to make it Online again, It asks for Media Recovery. The reason for asking MR is the change in SCN. SCN is changed between time of Offline and time to take DBF Online again. And once the SCN is changed, to bring effected DBF we need to provide Archived Changed Vector and any changes residing in Online REDO log files.

However! In this case nothing to worry about. Because I have my database backup intact. All I need to do is Restore and Recover Data file number 4. 

Before we proceed further take Datafile 4 Offline and remove it from HDD or take it to another  part of HDD.

Although! Datafile number 4 was offline, I could not cut/paste it from its location to any other location. If we miss to do this, than while restoring the DBF, we get “DBF already in use error and Restore operation fails”. In case you face  error on deleting it from OS level than you have to take DB down for just to remover DBF from its default location. Once we are done with these steps then remaining part of Restore/Recover is just the same.

RMAN> RUN

2> {

3> ALLOCATE CHANNEL CH1 DEVICE TYPE DISK FORMAT 'D:\BKUP\DG_FILES\SEED_%U.BKP';

4> SQL 'ALTER DATABASE DATAFILE 4 OFFLINE';

5> RESTORE DATAFILE 4;

6> }

allocated channel: CH1

channel CH1: sid=137 devtype=DISK

sql statement: ALTER DATABASE DATAFILE 4 OFFLINE

Starting restore at 01-AUG-11

channel CH1: starting datafile backupset restore

channel CH1: specifying datafile(s) to restore from backup set

restoring datafile 00004 to D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\USERS01.DBF

channel CH1: reading from backup piece D:\BKUP\DG_FILES\SEED_DG_1DMIB1F7_1_1.BKP

channel CH1: restored backup piece 1

piece handle=D:\BKUP\DG_FILES\SEED_DG_1DMIB1F7_1_1.BKP tag=TAG20110725T141711

channel CH1: restore complete, elapsed time: 00:00:04

Finished restore at 01-AUG-11

released channel: CH1

RMAN> Recover datafile 4;

Starting recover at 01-AUG-11

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=137 devtype=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: sid=135 devtype=DISK

starting media recovery

archive log thread 1 sequence 13 is already on disk as file D:\BKUP\SEED\SEED_ARC_00013_0756558498_001.ARC

archive log thread 1 sequence 14 is already on disk as file D:\BKUP\SEED\SEED_ARC_00014_0756558498_001.ARC

archive log thread 1 sequence 15 is already on disk as file D:\BKUP\SEED\SEED_ARC_00015_0756558498_001.ARC

archive log thread 1 sequence 16 is already on disk as file D:\BKUP\SEED\SEED_ARC_00016_0756558498_001.ARC

archive log thread 1 sequence 17 is already on disk as file D:\BKUP\SEED\SEED_ARC_00017_0756558498_001.ARC

archive log thread 1 sequence 18 is already on disk as file D:\BKUP\SEED\SEED_ARC_00018_0756558498_001.ARC

archive log thread 1 sequence 19 is already on disk as file D:\BKUP\SEED\SEED_ARC_00019_0756558498_001.ARC

archive log thread 1 sequence 20 is already on disk as file D:\BKUP\SEED\SEED_ARC_00020_0756558498_001.ARC

archive log thread 1 sequence 21 is already on disk as file D:\BKUP\SEED\SEED_ARC_00021_0756558498_001.ARC

archive log thread 1 sequence 22 is already on disk as file D:\BKUP\SEED\SEED_ARC_00022_0756558498_001.ARC

archive log thread 1 sequence 23 is already on disk as file D:\BKUP\SEED\SEED_ARC_00023_0756558498_001.ARC

archive log thread 1 sequence 24 is already on disk as file D:\BKUP\SEED\SEED_ARC_00024_0756558498_001.ARC

archive log thread 1 sequence 25 is already on disk as file D:\BKUP\SEED\SEED_ARC_00025_0756558498_001.ARC

archive log thread 1 sequence 26 is already on disk as file D:\BKUP\SEED\SEED_ARC_00026_0756558498_001.ARC

archive log thread 1 sequence 27 is already on disk as file D:\BKUP\SEED\SEED_ARC_00027_0756558498_001.ARC

archive log thread 1 sequence 28 is already on disk as file D:\BKUP\SEED\SEED_ARC_00028_0756558498_001.ARC

archive log thread 1 sequence 29 is already on disk as file D:\BKUP\SEED\SEED_ARC_00029_0756558498_001.ARC

archive log thread 1 sequence 30 is already on disk as file D:\BKUP\SEED\SEED_ARC_00030_0756558498_001.ARC

archive log thread 1 sequence 31 is already on disk as file D:\BKUP\SEED\SEED_ARC_00031_0756558498_001.ARC

archive log thread 1 sequence 32 is already on disk as file D:\BKUP\SEED\SEED_ARC_00032_0756558498_001.ARC

archive log thread 1 sequence 33 is already on disk as file D:\BKUP\SEED\SEED_ARC_00033_0756558498_001.ARC

archive log thread 1 sequence 34 is already on disk as file D:\BKUP\SEED\SEED_ARC_00034_0756558498_001.ARC

archive log thread 1 sequence 35 is already on disk as file D:\BKUP\SEED\SEED_ARC_00035_0756558498_001.ARC

archive log thread 1 sequence 36 is already on disk as file D:\BKUP\SEED\SEED_ARC_00036_0756558498_001.ARC

archive log filename=D:\BKUP\SEED\SEED_ARC_00013_0756558498_001.ARC thread=1 sequence=13

archive log filename=D:\BKUP\SEED\SEED_ARC_00014_0756558498_001.ARC thread=1 sequence=14

archive log filename=D:\BKUP\SEED\SEED_ARC_00015_0756558498_001.ARC thread=1 sequence=15

archive log filename=D:\BKUP\SEED\SEED_ARC_00016_0756558498_001.ARC thread=1 sequence=16

archive log filename=D:\BKUP\SEED\SEED_ARC_00017_0756558498_001.ARC thread=1 sequence=17

archive log filename=D:\BKUP\SEED\SEED_ARC_00018_0756558498_001.ARC thread=1 sequence=18

archive log filename=D:\BKUP\SEED\SEED_ARC_00019_0756558498_001.ARC thread=1 sequence=19

archive log filename=D:\BKUP\SEED\SEED_ARC_00020_0756558498_001.ARC thread=1 sequence=20

archive log filename=D:\BKUP\SEED\SEED_ARC_00021_0756558498_001.ARC thread=1 sequence=21

archive log filename=D:\BKUP\SEED\SEED_ARC_00022_0756558498_001.ARC thread=1 sequence=22

archive log filename=D:\BKUP\SEED\SEED_ARC_00023_0756558498_001.ARC thread=1 sequence=23

archive log filename=D:\BKUP\SEED\SEED_ARC_00024_0756558498_001.ARC thread=1 sequence=24

archive log filename=D:\BKUP\SEED\SEED_ARC_00025_0756558498_001.ARC thread=1 sequence=25

archive log filename=D:\BKUP\SEED\SEED_ARC_00026_0756558498_001.ARC thread=1 sequence=26

archive log filename=D:\BKUP\SEED\SEED_ARC_00027_0756558498_001.ARC thread=1 sequence=27

archive log filename=D:\BKUP\SEED\SEED_ARC_00028_0756558498_001.ARC thread=1 sequence=28

archive log filename=D:\BKUP\SEED\SEED_ARC_00029_0756558498_001.ARC thread=1 sequence=29

archive log filename=D:\BKUP\SEED\SEED_ARC_00030_0756558498_001.ARC thread=1 sequence=30

archive log filename=D:\BKUP\SEED\SEED_ARC_00031_0756558498_001.ARC thread=1 sequence=31

archive log filename=D:\BKUP\SEED\SEED_ARC_00032_0756558498_001.ARC thread=1 sequence=32

archive log filename=D:\BKUP\SEED\SEED_ARC_00033_0756558498_001.ARC thread=1 sequence=33

archive log filename=D:\BKUP\SEED\SEED_ARC_00034_0756558498_001.ARC thread=1 sequence=34

media recovery complete, elapsed time: 00:00:14

Finished recover at 01-AUG-11

RMAN>

Alter database data ile 4 online;

After this just take backup of Data File or if possible full database backup.

Control file backup and recovery

Control File backup and Recovery:

Control file is repository of Oracle Database. Every information about Database structure is stored here. This can be called as Metadata of Database. Control file stores SCN information (Checkpoint SCN for consistent opening of DB), Data file information, REDO information, RMAN setting information (if configured),Archive log mode ,DBID,DBNAME, Temporary Table space information. By now you would have understood that Control file is actually everything that makes Database working and without control file there will not be any database.

All of the above information makes it very crucial to have your Control file intact. And prepare flawless strategy of Backup and Recovery of Control file.  Given proper consideration and well thought planning about safety of Control file makes life of DBA  easy. Following are scenarios of Control file Backup and recovery using RMAN utility:

Environment:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Prod

PL/SQL Release 10.2.0.1.0 - Production

"CORE   10.2.0.1.0             Production"

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 – Production

In scenario 1, I have used RMAN with Recovery Catalog database connectivity.

C:\>Rman Target sys/seed@seed_54 catalog rman_user/rman_user@dupdb

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Jul 13 18:36:57 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: SEED (DBID=837440993)

connected to recovery catalog database

RMAN> Show all;

starting full resync of recovery catalog

full resync complete

RMAN configuration parameters are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1;

CONFIGURE BACKUP OPTIMIZATION ON;

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'D:\bkup\seed\CTL_BKUP\SEED_CTL_FILE_%F';

CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET;

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT   'D:\bkup\seed\SEED_BKUP_%U.BKP';

CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT   'D:\bkup\seed\SEED_BKUP_%U.BKP';

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\SNCFSEED.ORA'; # default

These two Control file backup specific configuration(Configuration setting Yellow Colored) makes it sure that whenever Database is Backed up then current control file goes along with Backup.

Secondly , Whenever we make change in Database , eg. Tablespace added, Tablespace Dropped (2 changes in Database), Then two backup of control file goes automatically to 'D:\bkup\seed\CTL_BKUP\SEED_CTL_FILE_%F';  location.

Now I shutdown the database and remove the Control files to demonstrate Recovery of Control file.

After removing Control files(s) on OS level when I try to open the Database, I get following error:

SQL> Startup

ORACLE instance started.

Total System Global Area  612368384 bytes

Fixed Size                  1250428 bytes

Variable Size             230689668 bytes

Database Buffers          373293056 bytes

Redo Buffers                7135232 bytes

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

Our database now is in NOMOUNT stage.

Now to recover it I connect to RMAN

RMAN> exit

Recovery Manager complete.

Scenario 1 :-

Control file is lost but we have Recovery Catalog Database connectivity and we have all the backup piece(s) in tact and valid. Backup piece(s) are at their default location (mentioned in RMAN configuration).

C:\>Rman Target sys/seed@seed_54 catalog rman_user/rman_user@dupdb

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Jul 13 18:56:43 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: seed (not mounted)

connected to recovery catalog database

We  list out Backup Piece of Control file :

RMAN> List backup of controlfile;

List of Backup Sets

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

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

46      Full    1.03M      DISK        00:00:01     11-JUL-11

        BP Key: 54   Status: AVAILABLE  Compressed: YES  Tag: TAG20110711T110624

        Piece Name: D:\BKUP\SEED\SEED_BKUP_01MH5P1G_1_1.BKP

  Control File Included: Ckp SCN: 662627       Ckp time: 11-JUL-11

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

47      Full    6.80M      DISK        00:00:01     11-JUL-11

        BP Key: 55   Status: AVAILABLE  Compressed: NO  Tag: TAG20110711T110627

        Piece Name: D:\BKUP\SEED\CTL_BKUP\SEED_CTL_FILE_C-837440993-20110711-00

  Control File Included: Ckp SCN: 662633       Ckp time: 11-JUL-11

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

53      Full    6.80M      DISK        00:00:01     12-JUL-11

        BP Key: 61   Status: AVAILABLE  Compressed: NO  Tag: TAG20110712T162012

        Piece Name: D:\BKUP\SEED\CTL_BKUP\SEED_CTL_FILE_C-837440993-20110712-00

  Control File Included: Ckp SCN: 759685       Ckp time: 12-JUL-11

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

336     Full    6.80M      DISK        00:00:03     13-JUL-11

        BP Key: 337   Status: AVAILABLE  Compressed: NO  Tag: TAG20110713T175948

        Piece Name: D:\BKUP\SEED\CTL_BKUP\SEED_CTL_FILE_C-837440993-20110713-00

  Control File Included: Ckp SCN: 806881       Ckp time: 13-JUL-11

Now! We have seen Backup piece(s) containing  Control file. All we need to do is Restore the Control File. As database is connected to Recovery Catalog, So not much problem is there. Comparatively easy. All we need to do is Restore control from Auto backup , Mount the Database and Recover database and in end Open Database in RESETLOGS mode.

RMAN> Restore Controlfile;

Starting restore at 13-JUL-11

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=156 devtype=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: sid=155 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: reading from backup piece D:\BKUP\SEED\CTL_BKUP\SEED_CTL_FILE_C-837440993-20110713-00

channel ORA_DISK_1: restored backup piece 1

piece handle=D:\BKUP\SEED\CTL_BKUP\SEED_CTL_FILE_C-837440993-20110713-00 tag=TAG20110713T175948

channel ORA_DISK_1: restore complete, elapsed time: 00:00:05

output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\CONTROL01.CTL

output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\CONTROL02.CTL

output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\CONTROL03.CTL

Finished restore at 13-JUL-11

RMAN> Alter Database Mount;

database mounted

released channel: ORA_DISK_1

released channel: ORA_DISK_2

RMAN> Recover Database;

Starting recover at 13-JUL-11

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=155 devtype=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: sid=156 devtype=DISK

starting media recovery

archive log thread 1 sequence 1 is already on disk as file D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\REDO02.LOG

archive log thread 1 sequence 2 is already on disk as file D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\REDO01.LOG

archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\REDO02.LOG thread=1 sequence=1

archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\REDO01.LOG thread=1 sequence=2

archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\REDO01.LOG thread=1 sequence=2

media recovery complete, elapsed time: 00:00:03

Finished recover at 13-JUL-11

RMAN> Alter Database Open Resetlogs;

database opened

new incarnation of database registered in recovery catalog

starting full resync of recovery catalog

full resync complete

Now! Control file is recovered and Database is opened . Restart the database and take backup.  Recovering Control file  using Recovery Catalog Database is pretty simple and straight forward. We have all the information required for Restoration of File.

Scenario 2:

Control file as well as Recovery Catalog Database is lost. Backup piece(s) are at their default location and available.

To demonstrate I delete all the Control File from OS  using OS utility. Now when I try to open the Database , I get following error (Same like scenario 1):

SQL> startup

ORACLE instance started.

Total System Global Area  612368384 bytes

Fixed Size                  1250428 bytes

Variable Size             247466884 bytes

Database Buffers          356515840 bytes

Redo Buffers                7135232 bytes

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

Now problem is, When I connect to RMAN using Catalog Database, Due to unavailability of Database, I get following error:

C:\>rman target sys/seed@seed_54 catalog rman_user/rman_user@dupdb

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Jul 14 15:13:18 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: seed (not mounted)

RMAN-00571: ===========================================================

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

RMAN-00571: ===========================================================

RMAN-00554: initialization of internal recovery manager package failed

RMAN-04004: error from recovery catalog database: ORA-12518: TNS:listener could not hand off client connection.

Now! I start RMAN without  Recovery Catalog Database.

C:\>rman target sys/seed@seed_54 nocatalog

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Jul 14 15:47:33 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

connected to target database: seed (not mounted)

using target database control file instead of recovery catalog

Unlike scenario 1, When I try to see backup piece(s) containing Control File(s), I get following message.

RMAN> list backup of controlfile;

RMAN-00571: ===========================================================

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

RMAN-00571: ===========================================================

RMAN-03002: failure of list command at 07/14/2011 18:31:48

ORA-01507: database not mounted

It is understandable; Database is not in MOUNT stage so we cannot see the Backup information from Control file. As we are unable to see Backup information,  cannot proceed with restoration of Control File. If we use any of “RESTORE CONTROLFILE” Command, than statement fails.

RMAN> restore controlfile;

Starting restore at 14-JUL-11

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=156 devtype=DISK

RMAN-00571: ===========================================================

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

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 07/14/2011 18:39:15

RMAN-06563: control file or SPFILE must be restored using FROM AUTOBACKUP

RMAN> restore controlfile from autobackup;

Starting restore at 14-JUL-11

using channel ORA_DISK_1

channel ORA_DISK_1: looking for autobackup on day: 20110714

channel ORA_DISK_1: looking for autobackup on day: 20110713

channel ORA_DISK_1: looking for autobackup on day: 20110712

channel ORA_DISK_1: looking for autobackup on day: 20110711

channel ORA_DISK_1: looking for autobackup on day: 20110710

channel ORA_DISK_1: looking for autobackup on day: 20110709

channel ORA_DISK_1: looking for autobackup on day: 20110708

channel ORA_DISK_1: no autobackup in 7 days found

RMAN-00571: ===========================================================

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

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 07/14/2011 18:39:28

RMAN-06172: no autobackup found or specified handle is not a valid copy or piece

Even if, We SET proper FORMAT of Control File auto backup using following RUN Block, We fail.

RMAN> RUN

2> {

3> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'D:\bkup\seed\CTL_BKUP\SEED_CTL_FILE_%F';

4> RESTORE CONTROLFILE;

5> }

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

Starting restore at 15-JUL-11

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=156 devtype=DISK

RMAN-00571: ===========================================================

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

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 07/15/2011 10:36:18

RMAN-06563: control file or SPFILE must be restored using FROM AUTOBACKUP

RMAN> RUN

2> {

3> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'D:\bkup\seed\CTL_BKUP\SEED_CTL_FILE_%F';

4> RESTORE CONTROLFILE FROM AUTOBACKUP;

5> }

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

Starting restore at 15-JUL-11

using channel ORA_DISK_1

autobackup search outside recovery area not attempted because DBID was not set

RMAN-00571: ===========================================================

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

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 07/15/2011 10:36:32

RMAN-06172: no autobackup found or specified handle is not a valid copy or piece

There is no problem within RUN block, It is just that Oracle is not aware of Backup Piece location  of the Database. Here we need to provide the DBID of the Database. In case we have lost Control file and Recovery Catalog Database connectivity, we must know the DBID of Database. Because using DBID of the Database, Oracle comes to know the where about of Database Backup Piece(s) and once it knows the location of backup piece(s), it uses them for restoration and recovery.

RMAN> set dbid=837440993

executing command: SET DBID

Now after providing , DBID procedure of Control file recovery is simple. But unlike scenario 1, We cannot simply use “RECOVER CONTROLFILE” or “RECOVER CONTROLFILE FROM AUTOBACKUP” . Both commands produce error.

RMAN> Restore Controlfile;

Starting restore at 15-JUL-11

using channel ORA_DISK_1

RMAN-00571: ===========================================================

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

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 07/15/2011 10:45:20

RMAN-06563: control file or SPFILE must be restored using FROM AUTOBACKUP

RMAN> Restore Controlfile from Autobackup;

Starting restore at 15-JUL-11

using channel ORA_DISK_1

channel ORA_DISK_1: looking for autobackup on day: 20110715

channel ORA_DISK_1: looking for autobackup on day: 20110714

channel ORA_DISK_1: looking for autobackup on day: 20110713

channel ORA_DISK_1: looking for autobackup on day: 20110712

channel ORA_DISK_1: looking for autobackup on day: 20110711

channel ORA_DISK_1: looking for autobackup on day: 20110710

channel ORA_DISK_1: looking for autobackup on day: 20110709

channel ORA_DISK_1: no autobackup in 7 days found

RMAN-00571: ===========================================================

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

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 07/15/2011 10:45:32

RMAN-06172: no autobackup found or specified handle is not a valid copy or piece

To Restore control file in such situation, We need to provide FORMAT of Control File AUTOBACKUP.

RMAN> RUN

2> {

3> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'D:\bkup\seed\CTL_BKUP\SEED_CTL_FILE_%F';

4> RESTORE CONTROLFILE FROM AUTOBACKUP;

5> }

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

Starting restore at 15-JUL-11

using channel ORA_DISK_1

channel ORA_DISK_1: looking for autobackup on day: 20110715

channel ORA_DISK_1: looking for autobackup on day: 20110714

channel ORA_DISK_1: autobackup found: D:\bkup\seed\CTL_BKUP\SEED_CTL_FILE_c-837440993-20110714-02

channel ORA_DISK_1: control file restore from autobackup complete

output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\CONTROL01.CTL

output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\CONTROL02.CTL

output filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\CONTROL03.CTL

Finished restore at 15-JUL-11

As we can see that, using RUN block our restoration of Control file is successful.

RMAN> Alter Database Mount;

database mounted

released channel: ORA_DISK_1

RMAN> Recover database;

Starting recover at 15-JUL-11

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=155 devtype=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: sid=154 devtype=DISK

starting media recovery

archive log thread 1 sequence 3 is already on disk as file D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\REDO03.LOG

archive log thread 1 sequence 4 is already on disk as file D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\REDO01.LOG

archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\REDO03.LOG thread=1 sequence=3

archive log filename=D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\REDO01.LOG thread=1 sequence=4

media recovery complete, elapsed time: 00:00:05

Finished recover at 15-JUL-11

RMAN> Alter database open Resetlogs;

database opened

It is pretty simple and straight. All you need to provide DBID and location of Control File backup piece. 

The procedure of recovering Control file from its default AUTOBACK FORMAT location or any other location where user manually has transferred is simple. All we need to do is  SET CONTOLFILE AUTOBACKUP FORMAT             to user defined location. Eg. In scenario 2 I used following RUN block

RMAN> RUN

2> {

3> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'D:\bkup\seed\CTL_BKUP\SEED_CTL_FILE_%F';  ß Default Location

4> RESTORE CONTROLFILE FROM AUTOBACKUP;

5> }

In case, User has transferred backup piece(s) to any other location lets say D:\bkup  and Then all we need to do is change the RUN block eg.

RMAN> RUN

2> {

3> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'D:\bkup \SEED_CTL_FILE_%F';  ß changed Location

4> RESTORE CONTROLFILE FROM AUTOBACKUP;

5> }

Any comment/Query/Correction is most welcome....