Database creation Using RMAN backup in Oracle 10g

It is quite possible that we fall in a situation where we have to re-create a Database using the Backup (RMAN backup). It is not so rare scenario. The new Database can reside on new Server or existing server. Basic thing is,

We need to have a new database in Nomount state and Name of database should be Same.  Means  DB_NAME

Parameter should be like it was in old database. However, We can have different DB_UNIQUE_NAME for new database.

Another thing is, we cannot re-recreate a database on Windows machine using RMAN backup taken on Linux/Solaris OS and vice versa. Cross platform thing does not work here.

Following are the steps to recreate database from RMAN Backup.

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

1: In this database name will be same. but Instance name will be different.

2:- C:\Documents and Settings\20001>oradim -new -sid findb -intpwd seed -startmode m

               

3:- Create pfile. either copy any existing or create new one.

                keep db_name as FINDB  <-- found in init.ora file of OLD database

4:-create directory structure on OS level.

5:-follow steps

C:\Documents and Settings\20001>set oracle_sid=findb

C:\Documents and Settings\20001>sqlplus sys as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 2 14:47:37 2012

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

Enter password:

Connected to an idle instance.

SQL> startup nomount pfile=%oracle_home%\database\initFINDB.ora

ORACLE instance started.

Total System Global Area  612368384 bytes

Fixed Size                  1250428 bytes

Variable Size             239078276 bytes

Database Buffers          364904448 bytes

Redo Buffers                7135232 bytes

SQL> create spfile from pfile;

File created.

6: Put the RMAN backup to the new server on which you want to re-create the database.

7: Connect to RMAN

  

C:\Documents and Settings\20001>Set Oracle_sid=FINDB

C:\Documents and Settings\20001>rman target sys/seed

Recovery Manager: Release 10.2.0.1.0 - Production on Mon Jan 2 14:50:33 2012

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

connected to target database: FINDB (not mounted)

As at this stage, we can see that show all shows all the vanilla setting of RMAN configuration.

RMAN> show all;

using target database control file instead of recovery catalog

RMAN configuration parameters are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

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

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

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

RMAN> Set DBID=496261842

executing command: SET DBID

Our next step is to re-store the controlfile(s) from  the BACKUPSET. Command to restore Control file  dependes weather we are going to use AUTOBACKUP FORMAT or restoring from a particular backup set. Here we have little complexity. If we have the same directory structure on new server like we had on original server from where we had taken backup.

Eg. Following commands show the location (E:\RMAN_BKUP\FINDB\FINDB_CTLFILE_C-496261842-20111220-00) of control file  backup in backupset and on new server I do not have this location. So RESTORE FROM AUTOBACKUP would not work. So  I will use RESTORE    FROM statement.

RMAN> list backup of controlfile;

List of Backup Sets

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

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

2       Full    13.80M     DISK        00:00:02     20-DEC-11

        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20111220T144031

        Piece Name: E:\RMAN_BKUP\FINDB\FINDB_CTLFILE_C-496261842-20111220-00

  Control File Included: Ckp SCN: 9376006      Ckp time: 20-DEC-11

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

4       Full    13.80M     DISK        00:00:03     20-DEC-11

        BP Key: 4   Status: AVAILABLE  Compressed: NO  Tag: TAG20111220T144425

        Piece Name: E:\RMAN_BKUP\FINDB\FINDB_CTLFILE_C-496261842-20111220-01

  Control File Included: Ckp SCN: 9376116      Ckp time: 20-DEC-11

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

6       Full    13.80M     DISK        00:00:02     20-DEC-11

        BP Key: 6   Status: AVAILABLE  Compressed: NO  Tag: TAG20111220T144814

        Piece Name: E:\RMAN_BKUP\FINDB\FINDB_CTLFILE_C-496261842-20111220-02

  Control File Included: Ckp SCN: 9376236      Ckp time: 20-DEC-11

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

10      Full    13.80M     DISK        00:00:02     20-DEC-11

        BP Key: 10   Status: AVAILABLE  Compressed: NO  Tag: TAG20111220T151429

        Piece Name: E:\RMAN_BKUP\FINDB\FINDB_CTLFILE_C-496261842-20111220-03

  Control File Included: Ckp SCN: 9377372      Ckp time: 20-DEC-11

8:

RMAN> Run

2> {

3> Set Controlfile Autobackup Format For Device Type Disk to 'F:\Rman_Bkup\FINDB_851_91\FINDB_CTL_%F';

4> RESTORE CONTROLFILE FROM 'F:\Rman_Bkup\FINDB_851_91\FINDB_CTLFILE_C-496261842-20111222-00';

5> }

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

Starting restore at 02-JAN-12

using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file

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

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

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

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

Finished restore at 02-JAN-12

Setting DBID is very important before we start recovery process. If we  do not set DBID and try to use above run block than we get following error

RMAN> run

2> {

3> Set Controlfile Autobackup Format For Device Type Disk to 'F:\BKUP\Seed_Bkup\SEED_CTL_%F';

4> Restore Controlfile from Autobackup;

5> }

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

Starting restore at 27-DEC-11

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=157 devtype=DISK

recovery area destination: D:\oracle\product\10.2.0/flash_recovery_area

database name (or database unique name) used for search: ORCL

channel ORA_DISK_1: no autobackups found in the recovery area

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 12/27/2011 14:30:26

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

9:- Alter database mount;

RMAN> Alter database Mount;

database mounted

released channel: ORA_DISK_1

10:- if Database backup set expired then crosscheck backup once.

                RMAN> list backup of datafile 3;

List of Backup Sets

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

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

30      Full    35.00M     DISK        00:00:18     21-DEC-11

        BP Key: 33   Status: EXPIRED  Compressed: YES  Tag: FULL_DB_BKP

        Piece Name: D:\BKUP\SEED_BKUP\SEED_11MUP6LQ_1_1.BKP

  List of Datafiles in backup set 30

  File LV Type Ckp SCN    Ckp Time  Name

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

  3       Full 16375812   21-DEC-11 D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\SYSAUX01.DBF

       RMAN> Crosscheck Backup

       RMAN> list backup of datafile 4;

List of Backup Sets

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

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

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

31      Full    32.96M     DISK        00:00:12     21-DEC-11

        BP Key: 34   Status: AVAILABLE  Compressed: YES  Tag: FULL_DB_BKP

        Piece Name: D:\BKUP\SEED_BKUP\SEED_12MUP6MJ_1_1.BKP

  List of Datafiles in backup set 31

  File LV Type Ckp SCN    Ckp Time  Name

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

  4       Full 16375848   21-DEC-11 D:\ORACLE\PRODUCT\10.2.0\ORADATA\SEED\USERS01.DBF

11:- Restore database; <-- of backup is at its default location;

  Now! Restore of database also has some of complexity. After Mounting the database, we check the backup sets and  using “List Backup” command , we can see the location ,time and every other detail of backup set but the status of every backup set will be ‘EXPIRED’ because BACKUPSET are not on their default location. So we need to use catalog log command to update the BACKUPSET information in control file so that it actually reads our backup set. One easy method of doing so is

RMAN> CATALOG START WITH 'F:\Rman_Bkup\FINDB_851_91\FINDB_BKP_0' NOPROMPT;

searching for all files that match the pattern F:\Rman_Bkup\FINDB_851_91\FINDB_BKP_0

List of Files Unknown to the Database

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

File Name: F:\RMAN_BKUP\FINDB_851_91\FINDB_BKP_07MUMK7R_1_1.BKP

File Name: F:\RMAN_BKUP\FINDB_851_91\FINDB_BKP_08MUMKJE_1_1.BKP

File Name: F:\RMAN_BKUP\FINDB_851_91\FINDB_BKP_09MUMKQD_1_1.BKP

File Name: F:\RMAN_BKUP\FINDB_851_91\FINDB_BKP_0BMUS65U_1_1.BKP

cataloging files...

cataloging done

List of Cataloged Files

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

File Name: F:\RMAN_BKUP\FINDB_851_91\FINDB_BKP_07MUMK7R_1_1.BKP

File Name: F:\RMAN_BKUP\FINDB_851_91\FINDB_BKP_08MUMKJE_1_1.BKP

File Name: F:\RMAN_BKUP\FINDB_851_91\FINDB_BKP_09MUMKQD_1_1.BKP

File Name: F:\RMAN_BKUP\FINDB_851_91\FINDB_BKP_0BMUS65U_1_1.BKP

Now, RMAN can use, newly cataloged backup set.

RMAN> Run

2> {

3> Allocate Channel ch1 device type disk format 'F:\Rman_Bkup\FINDB_851_91\FINDB_BKP_%U.BKP';

4> SET NEWNAME FOR DATAFILE 'E:\ORACLE\ORADATA\FINDB\PSINDEX2.DBF' TO 'F:\oracle\findb\PSINDEX2.DBF';

5> SET NEWNAME FOR DATAFILE 'E:\ORACLE\ORADATA\FINDB\PSINDEX.DBF' TO 'F:\oracle\findb\PSINDEX.DBF';

6> RESTORE DATABASE;

7> SWITCH DATAFILE ALL;

8> RESTORE DATABASE;

9> ALTER DATABASE OPEN RESETLOGS;

10> }

Any suggestion,query,correction will be highly appriciated.

Thanks and Regards
Jai

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.