Khurram Siddiqui

Recovery Window

2009-10-12T23:48:00.000-07:00

Setting RMAN recovery window to 2 days make any backup older than 2 days become obsolete is a myth. See below the scenario which make you understand what need to consider when you are setting RMAN recovery window.

For Non Incremental Level backup
================================

Assume no backup exist before 13th of Oct
Assume recovery window is 2
POR = Point of recoverability

-----------------------------------------------------------------------------------------------------------
date           backup type        status               POR (Current date-recovery window)
-----------------------------------------------------------------------------------------------------------
13-OCT-2009    Full backup        No obsolete found    13-OCT-2009-2 = 11-OCT-2009
15-OCT-2009    Full backup        No obsolete found    15-OCT-2009-2 = 13-OCT-2009
16-OCT-2009    Full backup        No obsolete found    16-OCT-2009-2 = 14-OCT-2009

See above table data on 16-OCT-2009 , POR is 14-OCT-2009 and you dont have any backup on 14th of oct within yours backup schedule, in this case 13th Oct backup will enable you POR of 14th Oct , 13th Oct backup will not be marked obsoleted What would be happen on 17th of october lets see

-----------------------------------------------------------------------------------------------------------
date           backup type        status               POR (Current date-recovery window=POR)
-----------------------------------------------------------------------------------------------------------
13-OCT-2009    Full backup        No obsolete found    13-OCT-2009-2 = 11-OCT-2009
15-OCT-2009    Full backup        No obsolete found    15-OCT-2009-2 = 13-OCT-2009
16-OCT-2009    Full backup        No obsolete found    16-OCT-2009-2 = 14-OCT-2009
17-OCT-2009    No   backup        13th Oct Full backup 17-OCT-2009-2 = 15-OCT-2009
                                  will be marked 
                                  obsoleted

See above table data on 17-OCT-2009 , POR is 15-OCT-2009 you have backup on 15th of oct within yours backup schedule, in this case 13th Oct backup will not be needed for the point of recoverability for 15th of Oct ,15th Oct backup already exist for the POR,15th Oct backup will provide you POR and hence 13th Oct backup will be marked obsoleted.

For Incremental Level backup
================================

Assume no backup exist before 13th of Oct
Assume recovery window is 2
POR = Point of recoverability

-----------------------------------------------------------------------------------------------------------
date           backup type   status               POR (Current date-recovery window)
-----------------------------------------------------------------------------------------------------------
13-OCT-2009    L0            No obsolete found    13-OCT-2009-2 = 11-OCT-2009
14-OCT-2009    L1            No obsolete found    14-OCT-2009-2 = 12-OCT-2009
15-OCT-2009    L1            No obsolete found    15-OCT-2009-2 = 13-OCT-2009
16-OCT-2009    L1            No obsolete found    16-OCT-2009-2 = 14-OCT-2009

See above table data on 16-OCT-2009 , POR is 14-OCT-2009 you have backup on 14th of oct within yours backup schedule but this backup is L1 not L0 , in this case 13th Oct backup will be needed for the point of recoverability for 14th of Oct ,13th Oct backup will not be marked obsoleted.

-----------------------------------------------------------------------------------------------------------
date           backup type   status               POR (Current date-recovery window)
-----------------------------------------------------------------------------------------------------------
13-OCT-2009    L0            No obsolete found    13-OCT-2009-2 = 11-OCT-2009
14-OCT-2009    L1            No obsolete found    14-OCT-2009-2 = 12-OCT-2009
15-OCT-2009    L1            No obsolete found    15-OCT-2009-2 = 13-OCT-2009
16-OCT-2009    L1            No obsolete found    16-OCT-2009-2 = 14-OCT-2009
17-OCT-2009    L1            No obsolete found    17-OCT-2009-2 = 15-OCT-2009

See above table data on 17-OCT-2009 , POR is 15-OCT-2009 you have backup on 15th of oct within yours backup schedule but this backup is L1 not L0 , in this case 13th Oct backup will be needed for the point of recoverability for 15th of Oct ,13th Oct backup will not be marked obsoleted yet.

-----------------------------------------------------------------------------------------------------------
date           backup type   status               POR (Current date-recovery window)
-----------------------------------------------------------------------------------------------------------
13-OCT-2009    L0            No obsolete found    13-OCT-2009-2 = 11-OCT-2009
14-OCT-2009    L1            No obsolete found    14-OCT-2009-2 = 12-OCT-2009
15-OCT-2009    L1            No obsolete found    15-OCT-2009-2 = 13-OCT-2009
16-OCT-2009    L1            No obsolete found    16-OCT-2009-2 = 14-OCT-2009
17-OCT-2009    L1            No obsolete found    17-OCT-2009-2 = 15-OCT-2009
18-OCT-2009    L1            No obsolete found    18-OCT-2009-2 = 16-OCT-2009
19-OCT-2009    L1            No obsolete found    19-OCT-2009-2 = 17-OCT-2009
20-OCT-2009    L0            No obsolete found    20-OCT-2009-2 = 18-OCT-2009
21-OCT-2009    No backup     No obsolete found    21-OCT-2009-2 = 19-OCT-2009
22-OCT-2009    No backup     obsolete found       22-OCT-2009-2 = 20-OCT-2009
                             13th L0 and its 
                             alls L1

See above table data on 22-OCT-2009 , POR is 20-OCT-2009 you have L0 backup on 20th of oct within yours backup schedule and this backup is L0 not L1 , in this case 20th Oct backup will be needed for the point of recoverability for 20th of Oct ,before 20th L0 backup 13th L0 backup will marked obsoleted and hence alls its incremental level backup will be marked obsoleted.

How much recovery required

2009-10-01T23:33:00.000-07:00

Question :

While recovering database how can we know that upto how much archives we have to apply for getting the database sync(sync all datafiles headers with same SCN).

Where I can get the info while recovering the database;

Answer :

It depends on failure scenario , the two scenario comes into mind and not able to claim that in others scenario what happened.The following two scenario here.

Scenario 1

I lost all data file but my control file is intact at the same location where it was , before lost datafiles i have backed up the data files, controlfile and archive logs.

If you are just restoring the data files and not control files then you may know how much archive log sequence required for yours recovery after the backup taken using archived log repository.

RMAN> backup database;
 
Starting backup at 02-OCT-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=128 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/his/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/his/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/his/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/his/users01.dbf
channel ORA_DISK_1: starting piece 1 at 02-OCT-09
channel ORA_DISK_1: finished piece 1 at 02-OCT-09
piece handle=/u01/app/oracle/flash_recovery_area/HIS/backupset/2009_10_02/o1_mf_nnndf_TAG20091002T112226_5dc712wg_.bkp tag=TAG20091002T112226 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
Finished backup at 02-OCT-09
 
Starting Control File and SPFILE Autobackup at 02-OCT-09
piece handle=/u01/app/oracle/flash_recovery_area/HIS/autobackup/2009_10_02/o1_mf_s_699189791_5dc72j13_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 02-OCT-09
 
RMAN> sql 'alter system archive log current';
 
using target database control file instead of recovery catalog
sql statement: alter system archive log current
 
RMAN> sql 'alter system archive log current';
 
sql statement: alter system archive log current
 
RMAN> sql 'alter system archive log current';
 
sql statement: alter system archive log current
 
RMAN> exit
 
 
Recovery Manager complete.
[oracle@khurram his]$ sqlplus / as sysdba
 
SQL*Plus: Release 11.1.0.6.0 - Production on Fri Oct 2 11:24:04 2009
 
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> select name,sequence#
  2    from v$archived_log
  3   where status='A'
  4  / 
 
NAME                                                                                 SEQUENCE#
--------------------------------------------------------------------------------      ----------
/u01/app/oracle/product/11.1.0/db_1/dbs/arch1_2_699187272.dbf                           2
/u01/app/oracle/flash_recovery_area/HIS/archivelog/2009_10_02/o1_mf_1_2_5dc73qw2_.arc   2
/u01/app/oracle/product/11.1.0/db_1/dbs/arch1_3_699187272.dbf                           3
/u01/app/oracle/flash_recovery_area/HIS/archivelog/2009_10_02/o1_mf_1_3_5dc73vgm_.arc   3
/u01/app/oracle/product/11.1.0/db_1/dbs/arch1_4_699187272.dbf                           4
/u01/app/oracle/flash_recovery_area/HIS/archivelog/2009_10_02/o1_mf_1_4_5dc740d7_.arc   4
 
 
6 rows selected.

You may know if yours control file intact (not lost or restored from backup), you may know how much recovery will go so far by knowing archived logs repository.From above v$archived_log archveilog repository you are seeing that after backup there are sequence required for the archive logs are 2,3 and 4 for recovering the restored backup which you have taken as above.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@khurram his]$ cd /u01/app/oracle/oradata/his/*.dbf
bash: cd: /u01/app/oracle/oradata/his/sysaux01.dbf: Not a directory
[oracle@khurram his]$ rm -rf  /u01/app/oracle/oradata/his/*.dbf
[oracle@khurram his]$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Fri Oct 2 11:28:01 2009

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1620115456 bytes
Fixed Size                  2144864 bytes
Variable Size             922748320 bytes
Database Buffers          687865856 bytes
Redo Buffers                7356416 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@khurram his]$ rman target /

Recovery Manager: Release 11.1.0.6.0 - Production on Fri Oct 2 11:29:30 2009

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

connected to target database: HIS (DBID=3219691467, not open)

RMAN> restore database;

Starting restore at 02-OCT-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=154 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/his/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/his/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/his/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/his/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/HIS/backupset/2009_10_02/o1_mf_nnndf_TAG20091002T112226_5dc712wg_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/HIS/backupset/2009_10_02/o1_mf_nnndf_TAG20091002T112226_5dc712wg_.bkp tag=TAG20091002T112226
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 02-OCT-09

RMAN> recover database;

Starting recover at 02-OCT-09
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/flash_recovery_area/HIS/archivelog/2009_10_02/o1_mf_1_2_5dc73qw2_.arc
archived log for thread 1 with sequence 3 is already on disk as file /u01/app/oracle/flash_recovery_area/HIS/archivelog/2009_10_02/o1_mf_1_3_5dc73vgm_.arc
archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/flash_recovery_area/HIS/archivelog/2009_10_02/o1_mf_1_4_5dc740d7_.arc
archived log file name=/u01/app/oracle/flash_recovery_area/HIS/archivelog/2009_10_02/o1_mf_1_2_5dc73qw2_.arc thread=1 sequence=2
media recovery complete, elapsed time: 00:00:01
Finished recover at 02-OCT-09

as you can see that 2,3,4 were required during recovery process.

Scenario 2

I lost all data file as well control file , i have backed up the data files, controlfile and archive logs.

Its like a disatsre you lost alls data file and as well controlfile , if you backed the controlfile then remember during controlfile backup it will have file type flag 4 that tells Oracle it is a backup control file. The Stop SCN for alls data file marked to 0xffff.ffffffff (infinity).File type flag in the backup control file tells to oracle that it cannot rely on its redo thread,which means recovering will go on and on , controlfile will not aware how much should i go for redo it will never stop if you are able to supply redo.

It will always ask you more subsequent redo if you already supplied to it during process that's why it let you open the database in reset logs.

Lets see after restoring the controlfile and datafiles , you will see SQL tool recovery mechanism ask you recover database using backup controlfile which shows that controlfile does not know where to end the applying redo chain.

Here an excerpt which shows that restored controlfile does not know till how long the redo should be applied, its the RMAN robustness which does not ask you manually to supply redo, RMAN search the alls redo from archivelogs and if possible you have redo within redo logs.

RMAN> connect target sys/sys
 
connected to target database: HIS (DBID=3219691467)
 
RMAN> configure controlfile autobackup on;
 
using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
 
RMAN> list backup;
 
 
RMAN> 
 
RMAN> backup database;
 
Starting backup at 02-OCT-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/his/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/his/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/his/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/his/users01.dbf
channel ORA_DISK_1: starting piece 1 at 02-OCT-09
channel ORA_DISK_1: finished piece 1 at 02-OCT-09
piece handle=/u01/app/oracle/flash_recovery_area/HIS/backupset/2009_10_02/o1_mf_nnndf_TAG20091002T092724_5dc09dlc_.bkp tag=TAG20091002T092724 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
Finished backup at 02-OCT-09
 
Starting Control File and SPFILE Autobackup at 02-OCT-09
piece handle=/u01/app/oracle/flash_recovery_area/HIS/autobackup/2009_10_02/o1_mf_s_699182899_5dc0c49t_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 02-OCT-09
 
RMAN> sql 'alter system archive log current';
 
sql statement: alter system archive log current
 
RMAN> sql 'alter system archive log current';
 
sql statement: alter system archive log current
 
RMAN> sql 'alter system archive log current';
 
sql statement: alter system archive log current
 
RMAN> list backup;
 
 
List of Backup Sets
===================
 
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1       Full    981.55M    DISK        00:00:52     02-OCT-09      
        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20091002T092724
        Piece Name: /u01/app/oracle/flash_recovery_area/HIS/backupset/2009_10_02/o1_mf_nnndf_TAG20091002T092724_5dc09dlc_.bkp
  List of Datafiles in backup set 1
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 894968     02-OCT-09 /u01/app/oracle/oradata/his/system01.dbf
  2       Full 894968     02-OCT-09 /u01/app/oracle/oradata/his/sysaux01.dbf
  3       Full 894968     02-OCT-09 /u01/app/oracle/oradata/his/undotbs01.dbf
  4       Full 894968     02-OCT-09 /u01/app/oracle/oradata/his/users01.dbf
 
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2       Full    9.36M      DISK        00:00:01     02-OCT-09      
        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20091002T092819
        Piece Name: /u01/app/oracle/flash_recovery_area/HIS/autobackup/2009_10_02/o1_mf_s_699182899_5dc0c49t_.bkp
  SPFILE Included: Modification time: 02-OCT-09
  SPFILE db_unique_name: HIS
  Control File Included: Ckp SCN: 894991       Ckp time: 02-OCT-09
 
RMAN> shutdown abort
 
Oracle instance shut down
 
RMAN> exit
 
Recovery Manager complete.
 
[oracle@khurram ~]$ rm -rf /u01/app/oracle/oradata/his/*
[oracle@khurram ~]$ rman target /

Recovery Manager: Release 11.1.0.6.0 - Production on Fri Oct 2 09:32:37 2009

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

connected to target database (not started)

RMAN> startup nomount

Oracle instance started

Total System Global Area    1620115456 bytes

Fixed Size                     2144864 bytes
Variable Size                922748320 bytes
Database Buffers             687865856 bytes
Redo Buffers                   7356416 bytes

RMAN> restore controlfile from autobackup;

Starting restore at 02-OCT-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=153 device type=DISK

recovery area destination: /u01/app/oracle/flash_recovery_area
database name (or database unique name) used for search: HIS
channel ORA_DISK_1: AUTOBACKUP /u01/app/oracle/flash_recovery_area/HIS/autobackup/2009_10_02/o1_mf_s_699182899_5dc0c49t_.bkp found in the recovery area
AUTOBACKUP search with format "%F" not attempted because DBID was not set
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/oracle/flash_recovery_area/HIS/autobackup/2009_10_02/o1_mf_s_699182899_5dc0c49t_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/u01/app/oracle/oradata/his/control01.ctl
output file name=/u01/app/oracle/oradata/his/control02.ctl
output file name=/u01/app/oracle/oradata/his/control03.ctl
Finished restore at 02-OCT-09

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> restore database;

Starting restore at 02-OCT-09
Starting implicit crosscheck backup at 02-OCT-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=153 device type=DISK
Crosschecked 1 objects
Finished implicit crosscheck backup at 02-OCT-09

Starting implicit crosscheck copy at 02-OCT-09
using channel ORA_DISK_1
Finished implicit crosscheck copy at 02-OCT-09

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/flash_recovery_area/HIS/autobackup/2009_10_02/o1_mf_s_699182899_5dc0c49t_.bkp
File Name: /u01/app/oracle/flash_recovery_area/HIS/archivelog/2009_10_02/o1_mf_1_5_5dc0d1m8_.arc
File Name: /u01/app/oracle/flash_recovery_area/HIS/archivelog/2009_10_02/o1_mf_1_3_5dc0cx6p_.arc
File Name: /u01/app/oracle/flash_recovery_area/HIS/archivelog/2009_10_02/o1_mf_1_4_5dc0czx3_.arc

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/his/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/his/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/his/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/his/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/HIS/backupset/2009_10_02/o1_mf_nnndf_TAG20091002T092724_5dc09dlc_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/HIS/backupset/2009_10_02/o1_mf_nnndf_TAG20091002T092724_5dc09dlc_.bkp tag=TAG20091002T092724
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 02-OCT-09

[oracle@khurram ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Fri Oct 2 09:34:51 2009

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> recover database
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

Here come to RMAN and see that even alls archived log are still there for applying but due to restored controlfile from backup flag type 4 will ask you more and more redo log.

[oracle@khurram ~]$ rman target /

Recovery Manager: Release 11.1.0.6.0 - Production on Fri Oct 2 09:35:19 2009

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

connected to target database: HIS (DBID=3219691467, not open)

RMAN> recover database
2> ;

Starting recover at 02-OCT-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=151 device type=DISK

starting media recovery

archived log for thread 1 with sequence 3 is already on disk as file /u01/app/oracle/flash_recovery_area/HIS/archivelog/2009_10_02/o1_mf_1_3_5dc0cx6p_.arc
archived log for thread 1 with sequence 4 is already on disk as file /u01/app/oracle/flash_recovery_area/HIS/archivelog/2009_10_02/o1_mf_1_4_5dc0czx3_.arc
archived log for thread 1 with sequence 5 is already on disk as file /u01/app/oracle/flash_recovery_area/HIS/archivelog/2009_10_02/o1_mf_1_5_5dc0d1m8_.arc
archived log file name=/u01/app/oracle/flash_recovery_area/HIS/archivelog/2009_10_02/o1_mf_1_3_5dc0cx6p_.arc thread=1 sequence=3
archived log file name=/u01/app/oracle/flash_recovery_area/HIS/archivelog/2009_10_02/o1_mf_1_4_5dc0czx3_.arc thread=1 sequence=4
archived log file name=/u01/app/oracle/flash_recovery_area/HIS/archivelog/2009_10_02/o1_mf_1_5_5dc0d1m8_.arc thread=1 sequence=5
unable to find archived log
archived log thread=1 sequence=6
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 10/02/2009 09:35:26
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 6 and starting SCN of 895024

Ulimately you have to open the database with resetlog.

Backup Plus Archivleog

2009-09-29T01:05:00.000-07:00

Question :

RMAN will do backup of current redolog by moving the log to archivelog if we did 'backup database plus archivelog'. Do RMAN will backup the redolog if the backup command is without archivelog?

Answer :

Whenever you backup archivelog log ,log switch occur for the current log.

SQL> select group#,status
  2    from v$log
  3  /

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 INACTIVE
         3 CURRENT

RMAN> backup archivelog all;

Starting backup at 29-SEP-09
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=366 RECID=396 STAMP=698845462
channel ORA_DISK_1: starting piece 1 at 29-SEP-09
channel ORA_DISK_1: finished piece 1 at 29-SEP-09
piece handle=/u01/app/oracle/flash_recovery_area/MMI/backupset/2009_09_29/o1_mf_annnn_TAG20090929T114422_5d3c69j7_.bkp tag=TAG20090929T114422 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 29-SEP-09

SQL> select group#,status
  2    from v$log
  3  /


    GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 INACTIVE
         3 ACTIVE

You can see above that during archivelog backup process log switch occur.

SQL> select group#,status
  2    from v$log
  3  /

    GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 INACTIVE
         3 ACTIVE

RMAN> backup database plus archivelog;


Starting backup at 29-SEP-09
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=367 RECID=398 STAMP=698845610
channel ORA_DISK_1: starting piece 1 at 29-SEP-09
channel ORA_DISK_1: finished piece 1 at 29-SEP-09
piece handle=/u01/app/oracle/flash_recovery_area/MMI/backupset/2009_09_29/o1_mf_annnn_TAG20090929T114650_5d3cbxg3_.bkp tag=TAG20090929T114650 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 29-SEP-09

Starting backup at 29-SEP-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/mmi/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/mmi/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/mmi/undotbs03.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/mmi/users01.dbf
channel ORA_DISK_1: starting piece 1 at 29-SEP-09
channel ORA_DISK_1: finished piece 1 at 29-SEP-09
piece handle=/u01/app/oracle/flash_recovery_area/MMI/backupset/2009_09_29/o1_mf_nnndf_TAG20090929T114654_5d3cc1yz_.bkp tag=TAG20090929T114654 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 29-SEP-09
channel ORA_DISK_1: finished piece 1 at 29-SEP-09
piece handle=/u01/app/oracle/flash_recovery_area/MMI/backupset/2009_09_29/o1_mf_ncsnf_TAG20090929T114654_5d3cdm4z_.bkp tag=TAG20090929T114654 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 29-SEP-09

Starting backup at 29-SEP-09
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=368 RECID=400 STAMP=698845668
channel ORA_DISK_1: starting piece 1 at 29-SEP-09
channel ORA_DISK_1: finished piece 1 at 29-SEP-09
piece handle=/u01/app/oracle/flash_recovery_area/MMI/backupset/2009_09_29/o1_mf_annnn_TAG20090929T114748_5d3cdqpv_.bkp tag=TAG20090929T114748 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 29-SEP-09

SQL> select group#,status
  2    from v$log
  3  /


    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 ACTIVE
         3 CURRENT

As you can see "backup database plus archivelog" comes up with archivelog backup which inherit the log switch occurrence of archivelog backup.

SQL> select group#,status
  2    from v$log
  3  /

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 ACTIVE
         3 CURRENT


RMAN> backup database;

Starting backup at 29-SEP-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/mmi/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/mmi/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/mmi/undotbs03.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/mmi/users01.dbf
channel ORA_DISK_1: starting piece 1 at 29-SEP-09
channel ORA_DISK_1: finished piece 1 at 29-SEP-09
piece handle=/u01/app/oracle/flash_recovery_area/MMI/backupset/2009_09_29/o1_mf_nnndf_TAG20090929T115013_5d3ck8jy_.bkp tag=TAG20090929T115013 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 29-SEP-09
channel ORA_DISK_1: finished piece 1 at 29-SEP-09
piece handle=/u01/app/oracle/flash_recovery_area/MMI/backupset/2009_09_29/o1_mf_ncsnf_TAG20090929T115013_5d3clstc_.bkp tag=TAG20090929T115013 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 29-SEP-09


SQL> select group#,status
  2    from v$log
  3  /

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 INACTIVE
         3 CURRENT

As you can only just backup database will not trigger a log switch. But keep in mind during online backup there may be some background process or some online users production activity cause massive redo which filled the redo within the span of backup process time and then trigger a log switch does not mean that "backup database" cause a log switch.

Recovered SCN

2009-09-16T01:44:00.000-07:00

Question :
after performing recovery, how can we know till what time the target database is being recovered.


RMAN> backup database;

Starting backup at 16-SEP-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/mmi/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/mmi/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/mmi/undotbs01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/mmi/mydatafile.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/mmi/users01.dbf
channel ORA_DISK_1: starting piece 1 at 16-SEP-09
channel ORA_DISK_1: finished piece 1 at 16-SEP-09
piece handle=/u01/app/oracle/flash_recovery_area/MMI/backupset/2009_09_16/o1_mf_nnndf_TAG20090916T134511_5c19dv9q_.bkp tag=TAG20090916T134511 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
Finished backup at 16-SEP-09

Starting Control File and SPFILE Autobackup at 16-SEP-09
piece handle=/u01/app/oracle/flash_recovery_area/MMI/autobackup/2009_09_16/o1_mf_s_697729560_5c19gcmc_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 16-SEP-09

RMAN> sql 'alter system archive log current';

sql statement: alter system archive log current

RMAN> sql 'alter system archive log current';

sql statement: alter system archive log current

RMAN> sql 'alter system archive log current';

sql statement: alter system archive log current


RMAN> shutdown abort
 
Oracle instance shut down

RMAN> exit

Recovery Manager complete.

[oracle@khurram ~]$ rm -rf /u01/app/oracle/oradata/mmi/*
[oracle@khurram ~]$ rman target /

Recovery Manager: Release 11.1.0.6.0 - Production on Wed Sep 16 13:47:35 2009

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

connected to target database (not started)

RMAN> startup nomount

Oracle instance started

Total System Global Area    1620115456 bytes

Fixed Size                     2144864 bytes
Variable Size                721421728 bytes
Database Buffers             889192448 bytes
Redo Buffers                   7356416 bytes

RMAN> restore controlfile from autobackup;

Starting restore at 16-SEP-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=153 device type=DISK

recovery area destination: /u01/app/oracle/flash_recovery_area
database name (or database unique name) used for search: MMI
channel ORA_DISK_1: AUTOBACKUP /u01/app/oracle/flash_recovery_area/MMI/autobackup/2009_09_16/o1_mf_s_697729560_5c19gcmc_.bkp found in the recovery area
AUTOBACKUP search with format "%F" not attempted because DBID was not set
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/oracle/flash_recovery_area/MMI/autobackup/2009_09_16/o1_mf_s_697729560_5c19gcmc_.bkp
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/u01/app/oracle/oradata/mmi/control01.ctl
output file name=/u01/app/oracle/oradata/mmi/control02.ctl
output file name=/u01/app/oracle/oradata/mmi/control03.ctl
Finished restore at 16-SEP-09

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

From another terminal come at SQL

[oracle@khurram ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 - Production on Wed Sep 16 13:49:14 2009

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> set linesize 1000
SQL> select substr(name,1,50) fname,checkpoint_change#,last_change#,status
  2    from v$datafile;

FNAME                                              CHECKPOINT_CHANGE# LAST_CHANGE# STATUS
-------------------------------------------------- ------------------ ------------ -------
/u01/app/oracle/oradata/mmi/system01.dbf                      1350307              SYSTEM
/u01/app/oracle/oradata/mmi/sysaux01.dbf                      1350307              ONLINE
/u01/app/oracle/oradata/mmi/undotbs01.dbf                     1350307              ONLINE
/u01/app/oracle/oradata/mmi/users01.dbf                       1350307              ONLINE
/u01/app/oracle/oradata/mmi/mydatafile.dbf                    1350307              ONLINE

here you can see that within control file repository the data files START SCN (CHECKPOINT_SCHANGE#) is 1350307,whenever checkpoints occur, Oracle writes a START SCN to the data file headers as well it writes the START SCN within controlfile for each datafile.The control file also maintains another SCN value for each data file with START SCN is the STOP SCN, this STOP SCN is usually null (infinity) when yours database status is open and datafile status is online , the null status shows that recovery will start from START SCN and will goes to the end of redo thread.The STO SCN here is LAST_CHANGE#

RMAN> restore database;

Starting restore at 16-SEP-09
Starting implicit crosscheck backup at 16-SEP-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=152 device type=DISK
Crosschecked 1 objects
Finished implicit crosscheck backup at 16-SEP-09

Starting implicit crosscheck copy at 16-SEP-09
using channel ORA_DISK_1
Finished implicit crosscheck copy at 16-SEP-09

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/flash_recovery_area/MMI/autobackup/2009_09_16/o1_mf_s_697729560_5c19gcmc_.bkp
File Name: /u01/app/oracle/flash_recovery_area/MMI/archivelog/2009_09_16/o1_mf_1_1_5c19gzs5_.arc
File Name: /u01/app/oracle/flash_recovery_area/MMI/archivelog/2009_09_16/o1_mf_1_2_5c19h2m0_.arc
File Name: /u01/app/oracle/flash_recovery_area/MMI/archivelog/2009_09_16/o1_mf_1_3_5c19h4tk_.arc

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/mmi/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/mmi/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/mmi/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/mmi/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/mmi/mydatafile.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/MMI/backupset/2009_09_16/o1_mf_nnndf_TAG20090916T134511_5c19dv9q_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/MMI/backupset/2009_09_16/o1_mf_nnndf_TAG20090916T134511_5c19dv9q_.bkp tag=TAG20090916T134511
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 16-SEP-09

SQL> select substr(name,1,50) fname,checkpoint_change#,last_change#,status
  2    from v$datafile;

FNAME                                              CHECKPOINT_CHANGE# LAST_CHANGE# STATUS
-------------------------------------------------- ------------------ ------------ -------
/u01/app/oracle/oradata/mmi/system01.dbf                      1350307              SYSTEM
/u01/app/oracle/oradata/mmi/sysaux01.dbf                      1350307              ONLINE
/u01/app/oracle/oradata/mmi/undotbs01.dbf                     1350307              ONLINE
/u01/app/oracle/oradata/mmi/users01.dbf                       1350307              ONLINE
/u01/app/oracle/oradata/mmi/mydatafile.dbf                    1350307              ONLINE

Now do recovery

RMAN> recover database;

Starting recover at 16-SEP-09
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 1 is already on disk as file /u01/app/oracle/flash_recovery_area/MMI/archivelog/2009_09_16/o1_mf_1_1_5c19gzs5_.arc
archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/flash_recovery_area/MMI/archivelog/2009_09_16/o1_mf_1_2_5c19h2m0_.arc
archived log for thread 1 with sequence 3 is already on disk as file /u01/app/oracle/flash_recovery_area/MMI/archivelog/2009_09_16/o1_mf_1_3_5c19h4tk_.arc
archived log file name=/u01/app/oracle/flash_recovery_area/MMI/archivelog/2009_09_16/o1_mf_1_1_5c19gzs5_.arc thread=1 sequence=1
archived log file name=/u01/app/oracle/flash_recovery_area/MMI/archivelog/2009_09_16/o1_mf_1_2_5c19h2m0_.arc thread=1 sequence=2
archived log file name=/u01/app/oracle/flash_recovery_area/MMI/archivelog/2009_09_16/o1_mf_1_3_5c19h4tk_.arc thread=1 sequence=3
unable to find archived log
archived log thread=1 sequence=4
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 09/16/2009 13:51:44
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 4 and starting SCN of 1350423

SQL> select substr(name,1,50) fname,checkpoint_change#,last_change#,status
  2    from v$datafile;

FNAME                                              CHECKPOINT_CHANGE# LAST_CHANGE# STATUS
-------------------------------------------------- ------------------ ------------ -------
/u01/app/oracle/oradata/mmi/system01.dbf                      1350423              SYSTEM
/u01/app/oracle/oradata/mmi/sysaux01.dbf                      1350423              ONLINE
/u01/app/oracle/oradata/mmi/undotbs01.dbf                     1350423              ONLINE
/u01/app/oracle/oradata/mmi/users01.dbf                       1350423              ONLINE
/u01/app/oracle/oradata/mmi/mydatafile.dbf                    1350423              ONLINE

Here you can see that within controlfile repositry there is datafile header START SCN which was 1350307 after restore,when you started recovery now its SCN is 1350423 which shows that yours recovery process start from SCN 1350307 and ends at 1350423.

coreletion between report need backup and retention policy

2009-09-15T01:58:00.000-07:00

Question :

Daily i used take full backup using rman,after that when query report need backup it showing all datafile list, i don't understand how come it showing all the datafile list thought i take full backup please help me in this issues.

Answer :

It relates to yours retention policy if you would have retention policy to 2 and you have one backupsets of USERS tablespace then it will require one more for tablespace users to mature yours retention policy.

RMAN> list backup
2> ;


List of Backup Sets
===================


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
5       595.00K    DISK        00:00:03     15-SEP-09      
        BP Key: 5   Status: AVAILABLE  Compressed: NO  Tag: TAG20090915T110249
        Piece Name: /u01/app/oracle/flash_recovery_area/MMI/backupset/2009_09_15/o1_mf_annnn_TAG20090915T110249_5bycjdm6_.bkp

  List of Archived Logs in backup set 5
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    17      1320409    15-SEP-09 1320963    15-SEP-09

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6       Full    1005.51M   DISK        00:01:14     15-SEP-09      
        BP Key: 6   Status: AVAILABLE  Compressed: NO  Tag: TAG20090915T110253
        Piece Name: /u01/app/oracle/flash_recovery_area/MMI/backupset/2009_09_15/o1_mf_nnndf_TAG20090915T110253_5bycjk2h_.bkp
  List of Datafiles in backup set 6
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 1320969    15-SEP-09 /u01/app/oracle/oradata/mmi/system01.dbf
  2       Full 1320969    15-SEP-09 /u01/app/oracle/oradata/mmi/sysaux01.dbf
  3       Full 1320969    15-SEP-09 /u01/app/oracle/oradata/mmi/undotbs01.dbf
  4       Full 1320969    15-SEP-09 /u01/app/oracle/oradata/mmi/users01.dbf
  5       Full 1320969    15-SEP-09 /u01/app/oracle/oradata/mmi/mydatafile.dbf

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
7       10.00K     DISK        00:00:03     15-SEP-09      
        BP Key: 7   Status: AVAILABLE  Compressed: NO  Tag: TAG20090915T110412
        Piece Name: /u01/app/oracle/flash_recovery_area/MMI/backupset/2009_09_15/o1_mf_annnn_TAG20090915T110412_5byclzbb_.bkp

  List of Archived Logs in backup set 7
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    18      1320963    15-SEP-09 1321005    15-SEP-09

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8       Full    9.39M      DISK        00:00:00     15-SEP-09      
        BP Key: 8   Status: AVAILABLE  Compressed: NO  Tag: TAG20090915T110416
        Piece Name: /u01/app/oracle/flash_recovery_area/MMI/autobackup/2009_09_15/o1_mf_s_697633458_5bycm3q8_.bkp
  SPFILE Included: Modification time: 15-SEP-09
  SPFILE db_unique_name: MMI
  Control File Included: Ckp SCN: 1321012      Ckp time: 15-SEP-09


RMAN> show retention policy;

RMAN configuration parameters for database with db_unique_name MMI are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default


RMAN> report need backup;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of files with less than 1 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------



RMAN> configure retention policy to redundancy 2
2> ;

new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
new RMAN configuration parameters are successfully stored

RMAN> report need backup
2> ;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 2
Report of files with less than 2 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------
1    1     /u01/app/oracle/oradata/mmi/system01.dbf
2    1     /u01/app/oracle/oradata/mmi/sysaux01.dbf
3    1     /u01/app/oracle/oradata/mmi/undotbs01.dbf
4    1     /u01/app/oracle/oradata/mmi/users01.dbf
5    1     /u01/app/oracle/oradata/mmi/mydatafile.dbf

RMAN> configure retention policy to redundancy 1
2> ;

old RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
new RMAN configuration parameters are successfully stored

RMAN> report need backup
2> ;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of files with less than 1 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------

RMAN>

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

2009-08-12T23:16:00.000-07:00

This demonstration relate to RMAN role connection with control file, whenever you add any datafile within tablespace or add any new tablespace within database , this added datafile or table space information goes to within controlfile.Later on if you will restore whole database and yours backup piece does not contain this newly added datafile , controlfile will give an hint to RMAN for adding this newly datafile during restoring the whole database.

i.e

1)Take a full backup;
2)create tablespace;
3)shutdown immediate;
4)delete alls datafile
5)restore database

at step 5 restore will recreate the datafile in conjunction with RMAN and controlfile.Lets see

RMAN> backup database;

Starting backup at 12-AUG-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=140 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/mmi/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/mmi/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/mmi/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/mmi/users01.dbf
channel ORA_DISK_1: starting piece 1 at 12-AUG-09
channel ORA_DISK_1: finished piece 1 at 12-AUG-09
piece handle=/u01/app/oracle/flash_recovery_area/MMI/backupset/2009_08_12/o1_mf_nnndf_TAG20090812T145015_58542s82_.bkp tag=TAG20090812T145015 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 12-AUG-09
channel ORA_DISK_1: finished piece 1 at 12-AUG-09
piece handle=/u01/app/oracle/flash_recovery_area/MMI/backupset/2009_08_12/o1_mf_ncsnf_TAG20090812T145015_585446c6_.bkp tag=TAG20090812T145015 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 12-AUG-09


SQL> create tablespace mytablespace datafile '/u01/app/oracle/oradata/mmi/mydatafile.dbf' size 10M
  2  /

Tablespace created.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Delete alls datafile using OS

RMAN> connect target sys/sys

connected to target database (not started)

RMAN> startup mount

Oracle instance started
database mounted

Total System Global Area    1620115456 bytes

Fixed Size                     2144864 bytes
Variable Size                922748320 bytes
Database Buffers             687865856 bytes
Redo Buffers                   7356416 bytes


RMAN> restore database;

Starting restore at 12-AUG-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=154 device type=DISK

creating datafile file number=5 name=/u01/app/oracle/oradata/mmi/mydatafile.dbf
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/mmi/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/mmi/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/mmi/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/mmi/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/MMI/backupset/2009_08_12/o1_mf_nnndf_TAG20090812T145015_58542s82_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/MMI/backupset/2009_08_12/o1_mf_nnndf_TAG20090812T145015_58542s82_.bkp tag=TAG20090812T145015
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 12-AUG-09

Stop here dont make recovery here go to /u01/app/oracle/oradata/mmi you will see there is datafile named mydatafile.dbf will be created.The control file knows about the datafile even though datafile is not backed up, if controlfile knows about datafile then restore creates the datafile in the original location with RMAN tool. During user managed restore , controlfile will not play this role with yours OS copying command and you will encounter following error during recovery like.. ORA-01157: cannot identify/lock data file 6 - see DBWR trace file ORA-01110: data file 6: '/u01/app/oracle/oradata/mmi/mydatafile.dbf' the reason for above error is that you are doing restore and recovery by using user managed backup and recovery,control file knows about the data file even though data file is not backed up, if control file knows about data file then restore creates the data file in the original location in corrdination with RMAN but with user manged restore and recovery controlfile not able to coordinate with OS utility copy and paste. In short during user managed restore , control file will not play this role with yours OS copying command .It is RMAN which coordinate with control file.

Media recovery start point

2009-04-10T05:18:00.000-07:00

Media Recovery will always start from the most earliest restored datafile header CKPT SCN.

SQL> select controlfile_change#,checkpoint_change#
  2    from v$database
  3  /

CONTROLFILE_CHANGE# CHECKPOINT_CHANGE#
------------------- ------------------
             675815             675666

SQL> select substr(name,1,51) fname,checkpoint_change#
  2    from v$datafile_header
  3  /

FNAME                                               CHECKPOINT_CHANGE#
--------------------------------------------------- ------------------
F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\SYSTEM01.DBF              675666
F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\UNDOTBS01.DBF             675666
F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\SYSAUX01.DBF              675666
F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\USERS01.DBF               675666
F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\USERS02.DBF               675666

Shutdown immediate the database and then make a cold copy of user01.dbf datafiles to folder A

F:\oracle\product\10.1.0\oradata>cd prod

F:\oracle\product\10.1.0\oradata\prod>mkdir A

F:\oracle\product\10.1.0\oradata\prod>copy users01.dbf A
        1 file(s) copied.

Start the database

SQL> startup
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145750508 bytes
Database Buffers           25165824 bytes
Redo Buffers                 262144 bytes
Database mounted.
Database opened.

SQL> select controlfile_change#,checkpoint_change#
  2    from v$database
  3  /

CONTROLFILE_CHANGE# CHECKPOINT_CHANGE#
------------------- ------------------
             676283             676131

SQL> select substr(name,1,51) fname,checkpoint_change#
  2    from v$datafile_header
  3  /

FNAME                                               CHECKPOINT_CHANGE#
--------------------------------------------------- ------------------
F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\SYSTEM01.DBF              676131
F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\UNDOTBS01.DBF             676131
F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\SYSAUX01.DBF              676131
F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\USERS01.DBF               676131
F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\USERS02.DBF               676131

shutdown immediate again the database and make copy of system01.dbf datafiles to folder B,every shutdown immediate itself make a checkpoint and increment the CKPT SCN at datafile header with controlfile.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.


F:\oracle\product\10.1.0\oradata\prod>mkdir B

F:\oracle\product\10.1.0\oradata\prod>copy system01.dbf B
        1 file(s) copied.

Start the database

SQL> startup
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145750508 bytes
Database Buffers           25165824 bytes
Redo Buffers                 262144 bytes
Database mounted.
Database opened.
SQL> select controlfile_change#,checkpoint_change#
  2    from v$database
  3  /

CONTROLFILE_CHANGE# CHECKPOINT_CHANGE#
------------------- ------------------
             676623             676473

SQL> select substr(name,1,51) fname,checkpoint_change#
  2    from v$datafile_header
  3  /

FNAME                                               CHECKPOINT_CHANGE#
--------------------------------------------------- ------------------
F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\SYSTEM01.DBF              676473
F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\UNDOTBS01.DBF             676473
F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\SYSAUX01.DBF              676473
F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\USERS01.DBF               676473
F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\USERS02.DBF               676473

Shutdown immediate the database again and then make a cold copy of user02.dbf datafiles to folder C.

F:\oracle\product\10.1.0\oradata\prod>mkdir C

F:\oracle\product\10.1.0\oradata\prod>copy users02.dbf C
        1 file(s) copied.

F:\oracle\product\10.1.0\oradata\prod>

Dont startup the database let it be remain shutdown,copy users01.dbf from folder A,system01 from folder B and users02 from folder C to the folder F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD respectivley. After copying those file to prod folder from A,B and C startup the database

==========================================================================
Copy user01.dbf from folder A (F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\A) 
to folder PROD (F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD)
==========================================================================

F:\oracle\product\10.1.0\oradata\prod>copy F:\oracle\product\10.1.0\oradata\prod\A\users01.dbf  
                                      F:\oracle\product\10.1.0\oradata\prod\
Overwrite F:\oracle\product\10.1.0\oradata\prod\USERS01.DBF? (Yes/No/All): yes
        1 file(s) copied.

===========================================================================
Copy system01.dbf from folder B (F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\B) 
to folder PROD (F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD)
===========================================================================

F:\oracle\product\10.1.0\oradata\prod>copy F:\oracle\product\10.1.0\oradata\prod
\B\system01.dbf  F:\oracle\product\10.1.0\oradata\prod\
Overwrite F:\oracle\product\10.1.0\oradata\prod\SYSTEM01.DBF? (Yes/No/All): yes
        1 file(s) copied.

===========================================================================
Copy users02.dbf from folder C (F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\C) 
to folder PROD (F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD)
===========================================================================

F:\oracle\product\10.1.0\oradata\prod>copy F:\oracle\product\10.1.0\oradata\prod
\C\users02.dbf  F:\oracle\product\10.1.0\oradata\prod\
Overwrite F:\oracle\product\10.1.0\oradata\prod\USERS02.DBF? (Yes/No/All): yes
        1 file(s) copied.

SQL> startup
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145750508 bytes
Database Buffers           25165824 bytes
Redo Buffers                 262144 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\SYSTEM01.DBF'


SQL> select controlfile_change#,checkpoint_change#
  2    from v$database
  3  /

CONTROLFILE_CHANGE# CHECKPOINT_CHANGE#
------------------- ------------------
             678259             678859

SQL> select substr(name,1,51) fname,checkpoint_change#
  2    from v$datafile_header
  3  /

FNAME                                               CHECKPOINT_CHANGE#
--------------------------------------------------- ------------------
F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\SYSTEM01.DBF              676472
F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\UNDOTBS01.DBF             678859
F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\SYSAUX01.DBF              678859
F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\USERS01.DBF               676130
F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\USERS02.DBF               678859

See above you will find that 676130 of users01.dbf is the earliest/oldest CKPT SCN with others datafile system01.dbf 676472 and users02.dbf 678859 recovery will start from that point 676130 which is earliest and oldest within restored datafiles.

SQL> recover database until cancel
ORA-00279: change 676130 generated at 04/11/2009 04:02:23 needed for thread 1
ORA-00289: suggestion :
F:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\PROD\ARCHIVELOG\2009_04_11\O1_MF_1_
1_%U_.ARC
ORA-00280: change 676130 for thread 1 is in sequence #1


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

Oracle Locks

2009-03-06T02:46:00.000-08:00

User must request a resource (i.e row,table,index etc) before using it and must release that resource after using it.
Without following above approach there would be collision among user requests which result data to be in inconsistent.To avoid data inconsistency a lock mechanisim adopted within different database,Every database has its own locking mechanism.
Oracle uses two modes in a multiuser database either its on table or row

Exclusive lock mode
Exclusive lock mode never allowed the associates resource from being shared.This lock mode is obtained to modify data.Only one exclusive lock can be placed on a resource (such as a row or a table).

i.e

From one session i acquire the exclusive lock on resource (emp table)

Session a

SQL> lock table emp in exclusive mode nowait
  2  /

Table(s) Locked.

Note : The optional keyword NOWAIT tells Oracle not to wait for a table if it has been locked by another user.

From another session i try to acquire the exclusive lock on same resource (emp table) as "session a" above already acquired.

Session b

SQL> lock table emp in exclusive mode nowait
  2  /
lock table emp in exclusive mode nowait
           *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

Even you cannot get shared lock over that table which has already exclusive mode lock

SQL> lock table emp in row share mode nowait
  2  /
lock table emp in row share mode nowait
           *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

Exclusive lock can be acquired at table level as well at row level.But oralce automatically acquire exclusive lock at row level which is the beauty of oracle to create data consistent as well maintain concurrency.

Share lock mode
Share lock mode allows the associated resource to be shared subject to cirumstances.

i.e

From one session i acquire the share lock on resource (emp table)

Session c

SQL> lock table emp in row share mode nowait
  2  /

Table(s) Locked.

From another session i try to acquire the same resource exclusive lock on same resoruces (emp table) which has already above taken by "session c".

Session d

SQL> lock table emp in row share mode nowait
  2  /

Table(s) Locked.

Share locks dont allow to acquire exclusive lock over the associates resource.

i.e

From one session i acquire the share lock on resource (emp table)

Session e 

SQL> lock table emp in row share mode nowait
  2  /

Table(s) Locked.

From another session i try to acquire the same exclusive lock on same resoruces (emp table) which has already taken by above "session e".

Session f 

SQL> lock table emp in exclusive mode nowait
  2  /
lock table emp in exclusive mode nowait
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

Now you are aware about locking mode.

Locks Types

DML locks

DML locks are locks which are acquired automatically by Oracle to protect data in tables and indexes Whenever you issue a DML statement to modify data i.e SELECT..FOR UPDATE,INSERT, UPDATE, MERGE, and DELETE statments.DML locks prevent destructive interference of simultaneous conflicting DML or DDL operations.
DML statments automatically acquire lock on two levels

i) Row-Level Locks (TX)
ii)Table Level Locks(TM)

These DML locks are always and only (exclusive) at row level but it can be shared as well as exclusive at table level.

Here TX specify lock at row level while TM is at table level.

i) Row Level Locks (TX)
Row-level locks are primarily used to prevent two transactions from modifying the same row. When a transaction needs to modify a row, a row lock is acquired.Any INSERT, DELETE, UPDATE, or SELECT FOR UPDATE statements will automatically issue an exclusive lock on the rows affected by the transaction. This exclusive lock at row means that other transactions can’t modify the affected rows until the original transaction commits or rolls back, thereby releasing the exclusive locks.

ii) Table Level Lock (TM)
Whenever you acquire any row level lock there is ultimately a table level is also acquired to prvent others session to alter, drop this table whose rows are being modified.TM Per table locks are acquired during the execution of a transaction when referencing a table with a DML statement so that the object is not dropped or altered during the execution of the transaction.

Here is an exceprt from SQL to verify that DML acquires lock at both row and table level

Session 1

SQL> update emp
  2     set sal=sal+1
  3   where empno=7566
  4  /

1 row updated.

Session 2

SQL> update emp
  2     set sal=sal+1
  3   where empno=7566
  4  /
     .
     .

hanged

Session 3

SQL> drop table emp
  2  /
drop table emp
           *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

Above excerpt from SQL gives now a little fog but uptil now you might be clear that DML acquires lock at both row and table level ,to be more clear about this statment that "DML acquires lock at both row and table level" we need to explore oracle dynamic view v$lock.

SQL> conn sys/sys@prod as sysdba
Connected.
SQL> desc v$lock
 Name       Null?    Type
 ---------- -------- ----------------------------
 ADDR                RAW(4)
 KADDR               RAW(4)
 SID                 NUMBER
 TYPE                VARCHAR2(2)
 ID1                 NUMBER
 ID2                 NUMBER
 LMODE               NUMBER
 REQUEST             NUMBER
 CTIME               NUMBER
 BLOCK               NUMBER

From this view you just need to understand the columns TYPE (Lock type),LMODE (Lock mode) and REQUEST

As we have already seen that there are two types of DML locks

Row Level (TX)
Table Level (TM)
Type will show you the locks type either its (TX or TM)

Lock Mode (LMODE)

Lock mode tell you strength of lock ,oracle placed numbered for each lock mode (0 to 6) according to its strength.The higher the number the more its stronger.Remember follows lock mode can be at row as well table level.
6 - Exclusive (X)
5 - Shared Row Exclusive (SRX)
4 - Share (S)
3 - Row Exclusive (RX)
2 - Row Share (RS)
1 - null (NULL)
0 - none
Lets explore only 6 and 3 right now for eliminating little fog that "DML acquires lock at both row and table level",After that we will explore others in next blog.

6 - Exclusive (X) The most restrictive locking mode; permits queries on the locked table but prohibits any DML by any other users. Above statment says that you cannot acquire that lock on that same resource (Table,Rows) which is already acquired by another session. Like if you have table level exclusive lock then no other session can get any lock on that table,same if you have exclusive lock on rows then no other session can acquire lock on that rows ,yes you can get any other lock other then that rows which have already exclusive lock. This mode is automatically obtained when using the DROP TABLE statement.

3 - Row Exclusive (RX) A row exclusive table lock generally indicates that the transaction holding the lock has made one or more updates to rows in the table. A row exclusive table lock is acquired automatically for a table modified by the following types of statements.

INSERT INTO table ... ;

UPDATE table ... ;

DELETE FROM table ... ;

LOCK TABLE table IN ROW EXCLUSIVE MODE;

Permitted Operations : A row exclusive table lock held by a transaction allows other transactions to query, insert, update,delete, or lock rows concurrently in the same table. Therefore, row exclusive table locks allow multiple transactions to obtain simultaneous row exclusive and row share table locks for the same table. Prohibited Operations : A row exclusive table lock held by a transaction prevents other transactions from manually locking the table for exclusive reading or writing. Therefore, other transactions cannot concurrently lock the table using the following statements:

LOCK TABLE table IN SHARE MODE;

LOCK TABLE table IN SHARE EXCLUSIVE MODE;

LOCK TABLE table IN EXCLUSIVE MODE;

Here is an excerpt from SQL to see both lock mode

SQL> update emp    
  2     set sal=sal+1    
  3   where empno=7566    
  4  /            
            
            
1 row updated.

SQL> show user
USER is "SYS"

SQL> select username
  2        ,v$lock.sid
  3        ,v$lock.type "lock type"
  4        ,lmode       "lock mode"
  5    from v$lock,v$session
  6   where v$lock.sid = v$session.sid
  7     and v$session.username='SCOTT'
  8   order by v$lock.sid
  9  /

USERNAME                              SID lo  lock mode
------------------------------ ---------- -- ----------
SCOTT                                 161 TX          6
SCOTT                                 161 TM          3

As you can see DML locks within session for an update statment acquired TX (Row Level) lock with lock mode 6 which is an exclusive (the most restrictive/aggresive lock).You are also seeing that this DML lock also acquire TM (Table Level) with lock mode 3 which is row exclusive this lock will prevent other session to drop that table.

I will post with others mode in next blog

Offline datafile always require recovery when it turns to be online

2009-02-24T23:08:00.001-08:00

There is a little confusion about offline datafile that it is always required recovery when it turns to be online,unlike with offline tablespace which never requires recovery after turn it to be online under some cirumstances.To understand Why it happens requires a little attention towards checkpoint event in oracle. Whenever checkpoints occur, Oracle writes a START SCN to the data file headers as well it writes the START SCN within controlfile for each datafile.The control file also maintains another SCN value for each data file with START SCN is the STOP SCN,this STOP SCN is usually null (infinity) when yours database status is open and datafile status is online, the null status shows that recovery will start from START SCN and will goes to the end of redo thread. Here is an excerpt which is showing from SQL

SQL> select substr(name,1,50) fname,checkpoint_change#,last_change#,status
  2    from v$datafile
  3  /

FNAME                          CHECKPOINT_CHANGE# LAST_CHANGE# STATUS
------------------------------ ------------------ ------------ -------
F:\ORACLE\..\PROD\SYSTEM01.DBF             371976              SYSTEM
F:\ORACLE\..\PROD\UNDOTBS01.DBF            371976              ONLINE
F:\ORACLE\..\PROD\SYSAUX01.DBF             371976              ONLINE
F:\ORACLE\..\PROD\USERS01.DBF              371976              ONLINE
F:\ORACLE\..\PROD\UNDO0201.DBF             371976              ONLINE

Here CHECKPOINT_CHANGE# is START SCN 371976 and LAST_CHANGE# is STOP SCN for each datafile header within controlfile. As you can see the LAST_CHANGE# (STOP SCN) is null which shows the data file is in open state (online).Whenever you shutdown the database normally a checkpoint occurs and the LAST_CHANGE# (STOP SCN) is updated with CHECKPOINT_CHANGE# (START SCN) which tell the mechanism that no recovery required whenver the database started again. Here is an excerpt which i am showing from SQL

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145750508 bytes
Database Buffers           25165824 bytes
Redo Buffers                 262144 bytes
Database mounted.

SQL> select substr(name,1,50) fname,checkpoint_change#,last_change#,status
  2    from v$datafile
  3  /

FNAME                          CHECKPOINT_CHANGE# LAST_CHANGE# STATUS
------------------------------ ------------------ ------------ -------
F:\ORACLE\..\PROD\SYSTEM01.DBF             372600       372600 SYSTEM
F:\ORACLE\..\PROD\UNDOTBS01.DBF            372600       372600 ONLINE
F:\ORACLE\..\PROD\SYSAUX01.DBF             372600       372600 ONLINE
F:\ORACLE\..\PROD\USERS01.DBF              372600       372600 ONLINE
F:\ORACLE\..\PROD\UNDO0201.DBF             372600       372600 ONLINE

See above CHECKPOINT_CHANGE# (START SCN)=LAST_CHANGE# (STOP SCN). Whenever there is mismatch between CHECKPOINT_CHANGE# (START SCN) and LAST_CHANGE# (STOP SCN) then oracle requires recovery. Here is an excerpt which is showing from SQL

SQL> shutdown abort
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145750508 bytes
Database Buffers           25165824 bytes
Redo Buffers                 262144 bytes
Database mounted.

SQL> select substr(name,1,50) fname,checkpoint_change#,last_change#,status
  2    from v$datafile
  3  /

FNAME                           CHECKPOINT_CHANGE# LAST_CHANGE# STATUS
------------------------------- ------------------ ------------ -------
F:\ORACLE\..\PROD\SYSTEM01.DBF              372601              SYSTEM
F:\ORACLE\..\PROD\UNDOTBS01.DBF             372601              ONLINE
F:\ORACLE\..\PROD\SYSAUX01.DBF              372601              ONLINE
F:\ORACLE\..\PROD\USERS01.DBF               372601              ONLINE
F:\ORACLE\..\PROD\UNDO0201.DBF              372601              ONLINE

See above LAST_CHANGE# (STOP SCN) is null after shutting the database abnormally ,it is set to NULL (i.e NO cehckpoint STOP SCN) updated with CHECKPOINT_CHANGE# (START SCN). when you open the database after that event instance recovery started automatically. Now come to the point for which we started the topic that offline datafile always requires recovery whenver we turn it back to online while the same not happen with tablespace in some circumstances.The reason is that when we offline the tablespace a checkpoint occurs for alls datafile within this tablespaces and synchronized the controlfile's datafile header LAST_CHANGE# (STOP SCN) with CHECKPOINT_CHANGE# (START SCN).Upon turning back to online the datafile within tablespace will not require recovery. You can offline tablespace with three parameters

Normal : You use Normal parameter when yours any datafile don't contain any error the database takes a checkpoint for all datafiles of the tablespace as it takes them offline. NORMAL is the default.Media recovery dont require when bring back to online.

Temporary : You use Temporary parameter when one of yours datafile contains error media recovery require if yours datafile which is turn to be offline using TEMPORARY parameter has some error,if not then no media recovery requires.

Immediate : You use Immediate parameter when yours database in archivelog and after taking the tablespace offline using Immediate parameter then no checkpoint occurs and the tablespace datafile require recovery when it will bring back to online

Here is an excerpt which is showing from SQL.

SQL> select substr(name,1,50) fname,checkpoint_change#,last_change#,status
  2    from v$datafile
  3  /

FNAME                           CHECKPOINT_CHANGE# LAST_CHANGE# STATUS
------------------------------- ------------------ ------------ -------
F:\ORACLE\..\PROD\SYSTEM01.DBF              392812              SYSTEM
F:\ORACLE\..\PROD\UNDOTBS01.DBF             392812              ONLINE
F:\ORACLE\..\PROD\SYSAUX01.DBF              392812              ONLINE
F:\ORACLE\..\PROD\USERS01.DBF               392812              ONLINE
F:\ORACLE\..\PROD\UNDO0201.DBF              392812              ONLINE

SQL> alter tablespace users offline normal
  2  /

Tablespace altered.

SQL> select substr(name,1,50) fname,checkpoint_change#,last_change#,status
  2    from v$datafile
  3  /

FNAME                          CHECKPOINT_CHANGE# LAST_CHANGE# STATUS
------------------------------ ----------------- ------------ -------
F:\ORACLE\..\PROD\SYSTEM01.DBF            392812              SYSTEM
F:\ORACLE\..\PROD\UNDOTBS01.DBF           392812              ONLINE
F:\ORACLE\..\PROD\SYSAUX01.DBF            392812              ONLINE
F:\ORACLE\..\PROD\USERS01.DBF             393058       393058 OFFLINE
F:\ORACLE\..\PROD\UNDO0201.DBF            392812              ONLINE

SQL> alter system switch logfile
  2  /

System altered.

SQL> /

System altered.

SQL> select substr(name,1,50) fname,checkpoint_change#,last_change#,status
  2    from v$datafile
  3  /

FNAME                           CHECKPOINT_CHANGE# LAST_CHANGE# STATUS
------------------------------- ------------------ ------------ -------
F:\ORACLE\..\PROD\SYSTEM01.DBF              393076              SYSTEM
F:\ORACLE\..\PROD\UNDOTBS01.DBF             393076              ONLINE
F:\ORACLE\..\PROD\SYSAUX01.DBF              393076              ONLINE
F:\ORACLE\..\PROD\USERS01.DBF               393058       393058 OFFLINE
F:\ORACLE\..\PROD\UNDO0201.DBF              393076              ONLINE

You can see that CHECKPOINT_CHANGE# (START SCN)=LAST_CHANGE# (STOP SCN) for the tabelspace USERS datafile F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\USERS01.DBF (399058).

SQL> alter tablespace users online
  2  /

Tablespace altered.

SQL> select substr(name,1,50) fname,checkpoint_change#,last_change#,status
  2    from v$datafile
  3  /

FNAME                            CHECKPOINT_CHANGE# LAST_CHANGE# STATUS
-------------------------------- ------------------ ------------ -------
F:\ORACLE\..\PROD\SYSTEM01.DBF               393076              SYSTEM
F:\ORACLE\..\PROD\UNDOTBS01.DBF              393076              ONLINE
F:\ORACLE\..\PROD\SYSAUX01.DBF               393076              ONLINE
F:\ORACLE\..\PROD\USERS01.DBF                393121              ONLINE
F:\ORACLE\..\PROD\UNDO0201.DBF               393076              ONLINE

But its not the case with datafile scenario when it turns to be offline and then bring back to online,it will always require recovery cause there is no checkpoint occurence process occurs for the datafile and in turn LAST_CHANGE# (STOP SCN) dont synchronize with CHECKPOINT_CHANGE# (START SCN).Oracle will always check this two datafile SCN (START AND STOP) whenver yours database started or you turn back the offline tablespace or datafile to be online. Here is an excerpt which is showing from SQL.

SQL> alter database datafile 'F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\USERS01.DBF' offline
  2  /

Database altered.

SQL> select substr(name,1,50) fname,checkpoint_change#,last_change#,status
  2    from v$datafile
  3  /

FNAME                           CHECKPOINT_CHANGE# LAST_CHANGE# STATUS
------------------------------- ------------------ ------------ -------
F:\ORACLE\..\PROD\SYSTEM01.DBF              393076              SYSTEM
F:\ORACLE\..\PROD\UNDOTBS01.DBF             393076              ONLINE
F:\ORACLE\..\PROD\SYSAUX01.DBF              393076              ONLINE
F:\ORACLE\..\PROD\USERS01.DBF               393121       393263 RECOVER
F:\ORACLE\..\PROD\UNDO0201.DBF              393076              ONLINE

You can see that CHECKPOINT_CHANGE# (START SCN)<>LAST_CHANGE# (STOP SCN) for the tabelspace USERS datafile F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\USERS01.DBF.Both are not synchronized at the time of datafile to turn it to offline,when you will turn back to online it will require recovery ,notice the file status is RECOVER which is not the case with at the time of tablespaces to make it offline. Here is an excerpt which is showing from SQL.

SQL> alter database datafile 'F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\USERS01.DBF' online
  2  /
alter database datafile 'F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\USERS01.DBF' online
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: 'F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\USERS01.DBF'


SQL> recover datafile 4
Media recovery complete.
SQL> alter database datafile 'F:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\USERS01.DBF' online
  2  /

Database altered.

SQL> select substr(name,1,50) fname,checkpoint_change#,last_change#,status
  2    from v$datafile
  3  /

FNAME                           CHECKPOINT_CHANGE# LAST_CHANGE# STATUS
------------------------------- ------------------ ------------ -------
F:\ORACLE\..\PROD\SYSTEM01.DBF              393076              SYSTEM
F:\ORACLE\..\PROD\UNDOTBS01.DBF             393076              ONLINE
F:\ORACLE\..\PROD\SYSAUX01.DBF              393076              ONLINE
F:\ORACLE\..\PROD\USERS01.DBF               393452              ONLINE
F:\ORACLE\..\PROD\UNDO0201.DBF              393076              ONLINE

Sysman Password Lock

2009-01-27T21:04:00.000-08:00

Whenever I start the service for DBConsole through command line, the account sysman get locks .It happens even though after unlocking the sysman user again and again.When i try to open the OEM console page it shows up the database is currently unavailable why?Although it connets from SQL and show me status of database open but dont know why it shows unavailable at OEM console page,It happens always whenever i try to open my OEM dbconsole it opens the page like

It happens due to changes of sysman password via sql , whenever you try to start emctl dbconsole it will lock that user why cause Oracle associate the Oracle Management Service with a repository which are defined within emoms.properties configuration file, the password within emoms.properties file conflict the password with data dictionary object password which cause to lock that sysman user and will always show up the above page whenever I start em console page

To get rid from this page you have to also edit emomos.properties (OMS repositry) whenever you change sysman or dbnsmp user password. Here couple of steps to achieve it

Stop the Management Service. Open the emoms.properties file.

ORACLE_HOME/localhostname_SIDname/sysman/config/emoms.properties (UNIX) ORACLE_HOME\localhostname_SIDname\sysman\config\emoms.properties (Windows)

Change the emdRepPwdEncrypted property to FALSE. Change the emdRepPwd property to the new password. Save the changes and close the emoms.properties file. Start the dbconsole Note : When the Management Service starts, it opens the emoms.properties file, encrypts the password, and changes the emdRepPwdEncrypted property to TRUE. Open the EM console

Log switch trigger for recovery at standby database

2008-10-11T00:01:00.000-07:00

I have already posted blog how standby redo apply and from this thread i got a comment by soemone else from within that thread,i feel better i should post another blog in reply that comment rather replying for that comments on that thread. Comments are

Hi, So does that mean you have only partial records at STANDBY when you cancel reovery process by not allowing complete logs to be applied? you have setting of "log_archive_dest_2 string SERVICE=stby LGWR SYNC VALID_FOR= (ONLINE_LOGFILES,PRIMARY_ROLE)" that means it'll write directly to STANDBY DB online logs.So as soon as logswitch happens at Primary Db, it should happen on Standby DB (not necessory) as well.so every record should be applied to STANDBY DB.So why in your case Partial records are applied? just because you have canceld recovery process in middle?

Yes due to canceling recovery process at standby cause to apply partial records partial committed records those which were in standby redo log and applied at standby DB after log switched but those which were in standby redo log but not applied to standby database cause it was in redo log and didn’t applied due to not Log switches occurring). Redo logs applied to a physical standby database when a log switch occurs, they don’t Applied as they arrive on the standby site. As MRP Apply (MRP process) or SQL Apply (LSP process) applies the redo data to the Standby database using standby archived logs. See figure

Log switch trigger MRP for applying the redo data to the standby database using Archived standby log. If you just enter suffice amount of data which does not cause Log switch at primary database then this redo propagated at standby redoes log files But will not applied until or unless you or itself log switch happened at primary Database which in turn also switched standby log at standby database. Before going to demonstrate I would think better to answer yours question that yes It’s necessary a log switch at physical standby trigger to log switch at standby redo Log file. For that you can see demonstration as i am doing within my lap top. Remember in this demo real-time apply is not enabled, which data Guard recovers redo data directly from the current standby redo log file as it is being filled up by the RFS process.


At standby

SQL> show user
USER is "SYS"

SQL> startup mount
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145488364 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes
Database mounted.

SQL> alter database recover managed standby database disconnect from session
  2  /

Database altered.

SQL> select database_role,switchover_status,protection_mode,force_logging
  2    from v$database
  3  /

DATABASE_ROLE    SWITCHOVER_STATUS    PROTECTION_MODE      FOR
---------------- -------------------- -------------------- ---
PHYSICAL STANDBY TO PRIMARY           MAXIMUM PERFORMANCE  YES

SQL> select process,client_process,status,thread#,sequence#,block#,blocks
  2    from v$managed_standby
  3  /

PROCESS   CLIENT_P STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- -------- ------------ ---------- ---------- ---------- ----------
ARCH      ARCH     CONNECTED             0          0          0          0
ARCH      ARCH     CONNECTED             0          0          0          0
MRP0      N/A      WAIT_FOR_LOG          1         31          0          0
RFS       UNKNOWN  ATTACHED              0          0          0          0

At primary

SQL> show user
USER is "SYS"

SQL> select process,client_process,status,thread#,sequence#,block#,blocks
  2    from v$managed_standby
  3  /


PROCESS   CLIENT_P STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- -------- ------------ ---------- ---------- ---------- ----------
ARCH      ARCH     CLOSING               1         30      16385       1481
ARCH      ARCH     CLOSING               1         30      16385       1481


SQL> select database_role,switchover_status,protection_mode,force_logging
  2    from v$database
  3  /

DATABASE_ROLE    SWITCHOVER_STATUS    PROTECTION_MODE      FOR
---------------- -------------------- -------------------- ---
PRIMARY          SESSIONS ACTIVE      MAXIMUM PERFORMANCE  YES


SQL> create table test (a number)
  2  /

Table created.

SQL> begin
  2    for i in 1..10000  loop
  3      insert into lalo values (i);
  4      commit;
  5    end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

Come at standby database

SQL> select process,client_process,status,thread#,sequence#,block#,blocks
  2    from v$managed_standby
  3  /

PROCESS   CLIENT_P STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- -------- ------------ ---------- ---------- ---------- ----------
ARCH      ARCH     CONNECTED             0          0          0          0
ARCH      ARCH     CLOSING               1         31       4097       1256
MRP0      N/A      WAIT_FOR_LOG          1         32          0          0
RFS       ARCH     RECEIVING             0          0          0          0
RFS       LGWR     WRITING               1         32       9770        100
RFS       UNKNOWN  RECEIVING             0          0          0          0

6 rows selected.

See MRP still waiting (wait_for_log) for sequence 32 for recovery while RFS is
writing in progress via LGWR for 32 unless you dont make log switch at primary
database then MRP will not apply redo to standby database.


Come at Primary database and make log switch 

Come at primary database

SQL> alter system switch logfile
  2  /

System altered.

Come at standby database

SQL> select process,client_process,status,thread#,sequence#,block#,blocks
  2    from v$managed_standby
  3  /


PROCESS   CLIENT_P STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- -------- ------------ ---------- ---------- ---------- ----------
ARCH      ARCH     CONNECTED             0          0          0          0
ARCH      ARCH     CLOSING               1         32       8193       1806
MRP0      N/A      WAIT_FOR_LOG          1         33          0          0
RFS       ARCH     RECEIVING             0          0          0          0
RFS       LGWR     WRITING               1         33          1          1
RFS       UNKNOWN  RECEIVING             0          0          0          0

See MRP now waiting (wait_for_log) for sequence 33 for recovery while RFS is writing in progress
via LGWR for sequence 33 unless you don’t make log switch at primary Database then MRP will not
apply redo to standby database.

Come at primary database

Lets see if i create that table that may cause at primary database to make log switch 

SQL> create table test_1 (a number)
  2  /

Table created.

Come at standby database

SQL> select process,client_process,status,thread#,sequence#,block#,blocks
  2    from v$managed_standby
  3  /

PROCESS   CLIENT_P STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- -------- ------------ ---------- ---------- ---------- ----------
ARCH      ARCH     CONNECTED             0          0          0          0
ARCH      ARCH     CLOSING               1         32       8193       1806
MRP0      N/A      WAIT_FOR_LOG          1         33          0          0
RFS       ARCH     RECEIVING             0          0          0          0
RFS       LGWR     WRITING               1         33        265          2
RFS       UNKNOWN  RECEIVING             0          0          0          0

6 rows selected.

No, no log switch occur at primary database still MRP waiting (wait_for_log) For sequence 33 for
recovery while RFS is writing in progress via LGWR for sequence 33, unless you don’t make log
switch implicitly/explicitly at primary database then MRP will not apply redo to standby database.

Let’s see if I cancel recovery and see table test_1 redo applied to physical standby Database

SQL> recover managed standby database cancel
Media recovery complete.
SQL> alter database open
  2  /

Database altered.

SQL> desc test_1
ERROR:
ORA-04043: object test_1 does not exist

Nop..

Put again it in recovery mode

SQL> alter database recover managed standby database disconnect from session
  2  /

Database altered.

Come at Primary database and make log switch 

SQL> alter system switch logfile
  2  /

System altered.

Come at standby database

SQL> select process,client_process,status,thread#,sequence#,block#,blocks
  2    from v$managed_standby
  3  /

PROCESS   CLIENT_P STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- -------- ------------ ---------- ---------- ---------- ----------
ARCH      ARCH     CONNECTED             0          0          0          0
ARCH      ARCH     CLOSING               1         33          1        455
MRP0      N/A      WAIT_FOR_LOG          1         34          0          0
RFS       ARCH     RECEIVING             0          0          0          0
RFS       LGWR     WRITING               1         34          7          3
RFS       UNKNOWN  RECEIVING             0          0          0          0

6 rows selected.

See MRP now waiting (wait_for_log) for sequence 34 for recovery while RFS is writing in progress
via LGWR for sequence 34, sequence 33 has been applied it applied redo via MRP so fast which you
cannot see right now but you can observe if you make huge transaction at primary database and
monitor physical standby database you will see MRP0 with status APPLYING_LOG.

Cancel recovery and see test_1 table redo applied via MRP after making log switch

SQL> recover managed standby database cancel
Media recovery complete.
SQL> alter database open
  2  /

Database altered.

SQL> desc test_1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  NUMBER

RMAN-06025: no backup of log thread 1 seq

2008-10-07T23:02:00.000-07:00

RMAN> run
2> {
3> RESTORE VALIDATE CHECK LOGICAL  ARCHIVELOG ALL;
4> }

Starting restore at 08-OCT-08
using channel ORA_DISK_1

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 10/08/2008 11:40:16
RMAN-06026: some targets not found - aborting restore
RMAN-06025: no backup of log thread 1 seq 31 lowscn 374582 found to restore
RMAN-06025: no backup of log thread 1 seq 30 lowscn 374398 found to restore
RMAN-06025: no backup of log thread 1 seq 29 lowscn 374337 found to restore
RMAN-06025: no backup of log thread 1 seq 28 lowscn 374159 found to restore
RMAN-06025: no backup of log thread 1 seq 27 lowscn 374073 found to restore
RMAN-06025: no backup of log thread 1 seq 26 lowscn 373814 found to restore
RMAN-06025: no backup of log thread 1 seq 25 lowscn 373756 found to restore
RMAN-06025: no backup of log thread 1 seq 24 lowscn 373318 found to restore
RMAN-06025: no backup of log thread 1 seq 23 lowscn 373184 found to restore
RMAN-06025: no backup of log thread 1 seq 22 lowscn 373004 found to restore
RMAN-06025: no backup of log thread 1 seq 21 lowscn 372946 found to restore
RMAN-06025: no backup of log thread 1 seq 20 lowscn 371604 found to restore
RMAN-06025: no backup of log thread 1 seq 19 lowscn 371598 found to restore
RMAN-06025: no backup of log thread 1 seq 18 lowscn 371198 found to restore
RMAN-06025: no backup of log thread 1 seq 17 lowscn 351033 found to restore
RMAN-06025: no backup of log thread 1 seq 16 lowscn 330912 found to restore
RMAN-06025: no backup of log thread 1 seq 15 lowscn 330901 found to restore
RMAN-06025: no backup of log thread 1 seq 14 lowscn 330754 found to restore
RMAN-06025: no backup of log thread 1 seq 13 lowscn 330749 found to restore
RMAN-06025: no backup of log thread 1 seq 12 lowscn 330449 found to restore
RMAN-06025: no backup of log thread 1 seq 11 lowscn 330444 found to restore
RMAN-06025: no backup of log thread 1 seq 10 lowscn 330119 found to restore
RMAN-06025: no backup of log thread 1 seq 9 lowscn 330114 found to restore
RMAN-06025: no backup of log thread 1 seq 8 lowscn 330052 found to restore
RMAN-06025: no backup of log thread 1 seq 7 lowscn 330046 found to restore
RMAN-06025: no backup of log thread 1 seq 6 lowscn 329222 found to restore

SQL> select sequence# from v$archived_log
  2  /


 SEQUENCE# S
---------- -
         6 D
         7 D
         8 D
         9 D
        10 D
        11 D
        12 D
        13 D
        14 D
        15 D
        16 D
        17 D
        18 D
        19 D
        20 D
        21 D
        22 D
        23 D
        24 D
        25 D
        26 D
        27 D
        28 D
        29 D
        30 D
        31 D
        32 A
        33 A
        34 A
        35 A

30 rows selected.

As you can see log sequence 6 to 31 either deleted by RMAN or deleted by OS and then crosschecked or these logs never backed up but deleted by OS and then crosschecked.Restore archivelog all will try to restore all archivelogs that are within repository (i.e controlfile). As you never backed up log sequence 6 to 31 but RMAN will try to restore those log sequence.

Workaround 1

RMAN> list archivelog all
2> ;


List of Archived Log Copies
Key     Thrd Seq     S Low Time  Name
------- ---- ------- - --------- ----
27      1    32      A 07-OCT-08 C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\POP\ARCHIVELOG\2008_10_07\O1_MF_1_32_4GQB1ZSD_.ARC
28      1    33      A 07-OCT-08 C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\POP\ARCHIVELOG\2008_10_07\O1_MF_1_33_4GQB51GN_.ARC
29      1    34      A 07-OCT-08 C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\POP\ARCHIVELOG\2008_10_08\O1_MF_1_34_4GRCZ11G_.ARC
30      1    35      A 08-OCT-08 C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\POP\ARCHIVELOG\2008_10_08\O1_MF_1_35_4GRM3Y0Z_.ARC


RMAN> run
2> {
3> RESTORE VALIDATE CHECK LOGICAL  ARCHIVELOG from sequence 32;
4> }

Starting restore at 08-OCT-08
using channel ORA_DISK_1

channel ORA_DISK_1: scanning archive log C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\POP\ARCHIVELOG\2008_10_07\O1_MF_1_32_4GQB1ZSD_.ARC
channel ORA_DISK_1: scanning archive log C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\POP\ARCHIVELOG\2008_10_07\O1_MF_1_33_4GQB51GN_.ARC
channel ORA_DISK_1: scanning archive log C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\POP\ARCHIVELOG\2008_10_08\O1_MF_1_34_4GRCZ11G_.ARC
channel ORA_DISK_1: scanning archive log C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\POP\ARCHIVELOG\2008_10_08\O1_MF_1_35_4GRM3Y0Z_.ARC
Finished restore at 08-OCT-08

Workaround 2

You can go with recvoery catalog for detail see metalink note 235973.1

How standby redo apply

2008-09-25T13:24:00.000-07:00

A log switch on the primary database triggers a log switch on the standby database, Causing ARCn processes on the standby database to archive the standby redo log Files To archive redo log files on the standby database. Then, Redo Apply (MRP process) Or SQL Apply (LSP process) applies the redo data to the standby database. If real-Time apply is enabled, Data Guard recovers redo data directly from the current standby redo log file as it is being filled up by the RFS process. What you can conclude from above statement is that Recovery at standby database via MRP happen after log switch at standby, unless you don’t log switch you will not get Recovery (log applied data) at standby. When you explicitly log switch then MRP start its process to roll forward, roll back. Here I am going to demonstrate you that after inserting even number record I am committing the data, it’s a procedure which insert 1000000 records, during this insertion lot of log switch occurs after every log Switch occurring obliged MRP to apply redo at data file from its standby redo log Files. But the commit is also stamping to data in data file which you may validate from yours data file. What I will do I will cancel the managed recovery after 4 to 5 log switching before Completion of this procedure and will see what MRP awaked after 4 to 5 log switch And applied the standby redo logs to my data files with committed stamped. At standby I have enabled managed recovery as a background detached process.

SQL>  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

Database altered.

No proces doing something cause at PROD (primary database) no any activity going on 
except MRP waiting for next log 924.

SQL> select process,status,thread#,sequence#,block#,blocks
  2    from v$managed_standby
  3  /

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
MRP0      WAIT_FOR_LOG          1        924          0          0
RFS       RECEIVING             0          0          0          0



Come to Prod server...

SQL> show parameter log_archive_dest_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2                   string      SERVICE=stby LGWR SYNC VALID_FOR= 
                                                 (ONLINE_LOGFILES,PRIMARY_ROLE) 
                                                 DB_UNIQUE_NAME=STBY


SQL> create table lalo (a number)
  2  /

Table created.


SQL> begin
  2   for i in 1..1000000
  3   loop
  4      insert into lalo values (i);
  5      if mod(i,2)=0 then
  6      commit;
  7      end if;
  8    end loop;
  9  end;
 10  .
SQL> /


At Standby see 

SQL> select process,status,thread#,sequence#,block#,blocks
  2    from v$managed_standby
  3  /

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH      CONNECTED             0          0          0          0
ARCH      CLOSING               1        930      18433        622
MRP0      APPLYING_LOG          1        927      15132      19054
RFS       RECEIVING             0          0          0          0
RFS       WRITING               1        931      18699        356
RFS       RECEIVING             0          0          0          0

6 rows selected.

SQL>  RECOVER MANAGED STANDBY DATABASE CANCEL
Media recovery complete.

SQL> ALTER DATABASE OPEN
  2  /

Database altered.

SQL> CONN SCOTT/TIGER@STBY
Connected.
SQL> select count(*)
  2    from lalo
  3  /

  COUNT(*)
----------
    194162

Undo tablespace size

2008-07-31T02:28:00.000-07:00

Redo Log sizing

2008-07-31T02:22:00.000-07:00

Coming very soon..

How oracle makes media recovery

2008-07-09T23:45:00.000-07:00

Oracle Instance shutdown with command 
SQL > Shutdown immediate

Oracle Instance shutdown with command 
SQL > SHUTDOWN ABORT


Let me know How Oracle SCN concept will work in above mentioned scenario ? 

Oracle first check controlfile checkpoint SCN number ,if checkpoint SCN in the 
datafile header earlier then controlfile checkpoint SCN then oracle need 
redo,recovery is overall the process of applying redo to data files.In more 
depth recovery will synchronize the checkpoint SCN in the data files header to 
checkpoint SCN in control file header Or in broader term we can say goal of 
recovery is to make sure the CKPT SCN in each datafile header matches the CKPT 
SCN in the control file.

How SCN Synronization will happen ? 

When you shutdown database with the immediate/normal/transactional clause then 
oralce trigger a checkpoint where oracle makes the control files and datafiles 
consistent to the same checpoint SCN during a database checkpoint.


How Oracle Database will understand what file Needs Restoration and Recovery ? 

For restoration database file oracle ,at startup oracle probe controlfile 
(metadata) of yours database ,if it does not find the database file physically 
where it knows then it needs restoration of that concerned database file.Lets 
say you take online backup,if yours database file which is not available then 
restoration comes into play,yours restored file is some time back then now 
yours existence others database file specifiaclly with control file,controlfile 
checkpoint SCN header will be ahead with restored lost database file,oracle 
decide requires recovery.

What the meaning of SCN Synronization,If Database synronize Control File SCN 
number with DBF File SCN then how it will work when we loose Control File?

Always restoration of controlfile require recovery why..

As i understand it does not apply the redo to data files but an appearance just 
like to apply redo which is not actual (you can also check this appearance by 
restoring controlfile and then make recovery at sql by recover database until 
cancel and then dont apply redo at first attempt and apply cancel),it does like 
that cause old control file which doesn't know where the redo chain ends ,it 
may comes into situation where redo log ahead to old backup control file this 
appearance of recovery make datafile and controlfile consistent by 
compeling/forcing open resetlogs.

OR 

restored control file which doesn't know where the redo chain ends ,it may 
comes into situation where redo log ahead to old backup control file, after 
restoring controlfile which does not know the current log sequence number of 
redo log ,the existing current redo log sequence number does not match to 
restored controlfile for current log sequence number ,the controlfile 
checkpoint scn # would be earlier to datafile checkpoint scn #,this appearance 
of recovery make datafile and controlfile consistent as well restored 
controlfile adopt the new resetting log sequence number by compeling/forcing 
open resetlogs.

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

I can’t understand yet why online backup is inconsistent? I know only a restored Backup which has
data files SCN header (i.e. CKPT SCN) does not match to control files SCN header (i.e. CKPT SCN)
considered to be inconsistent backup?

You are right but more or less a backup in which some of the files in the backup contain changes
that were made after the files were check pointed. This type of backup needs recovery before it
can be made consistent.

Inconsistent backups are usually created by taking online database backups; that is the database
is open while the files are being backed up.

RMAN backup at blocks level ,whenever backup start a checkpoint also triggered  say in time "x"
at the same time "x" RMAN started to take blocks backup e.g. b1,b2,b3.

Say block b1 SCN would be 100 and CKPT SCN at the time "x" of backup started is 500, there is no
guarantee that block b1 SCN would be same throught the backup completion. It may be change and at
every change block b1 SCN will be incremented, and at some time block b1 SCN may go
beyond/greater then CKPT SCN 500, if that’s the case then  yours block b1 backup would be
inconsistent.


Here see a demo

RMAN> backup database
2> ;


Meanwhile backup is going on the other hand i am doing some actitivity

SQL*Plus: Release 10.1.0.2.0 - Production on Wed Jul 16 14:56:53 2008

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options



SQL> create table v as select * from all_objects
  2  /

Table created.

SQL> create table v1 as select * from all_objects
  2  /

Table created.

SQL> create table v2 as select * from all_objects
  2  /

Table created.

SQL> create table v3 as select * from all_objects
  2  /

Table created.


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Delete alls datafiles here except redo log and control files after shutting down database..

After that start up database

SQL> startup
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145488364 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\SDS\SYSTEM01.DBF'

What i will do i will restore database files from inconsistent/online/hot backup.


C:\>rman

Recovery Manager: Release 10.1.0.2.0 - Production

Copyright (c) 1995, 2004, Oracle.  All rights reserved.

RMAN> connect target sys/sys@sds

connected to target database: SDS (DBID=2656018210)

RMAN> restore database
2> ;

Starting restore at 16-JUL-08
.
.
.
Finished restore at 16-JUL-08


After restore check yours inconsistent backup would have fuzzy block

SQL> select checkpoint_change#,absolute_fuzzy_change#,recovery_fuzzy_change#
  2    from v$datafile_copy
  3  /


CHECKPOINT_CHANGE# ABSOLUTE_FUZZY_CHANGE# RECOVERY_FUZZY_CHANGE#
------------------ ---------------------- ----------------------
            682999                      0                      0
            682999                      0                      0
            682999                      0                      0
            682999                      0                      0
            684218                      0                      0
            684218                      0                      0
            684218                      0                      0
            684218                      0                      0
            684218                      0                      0
            684218                      0                      0
            684218                      0                      0

CHECKPOINT_CHANGE# ABSOLUTE_FUZZY_CHANGE# RECOVERY_FUZZY_CHANGE#
------------------ ---------------------- ----------------------
            684218                      0                      0
            816371                      0                      0
            816371                      0                      0
            816371                      0                      0
            816371                 816527               0
16 rows selected.

SQL> 

If yours any block during backup get higher SCN then checkpoint SCN it would be considered fuzzy
block which make yours whole database backup inconsistent, oracle always requires recovery (redo)
in this situation to make inconsistent restored backup to consistent.

As far as concern about yours question that you didn’t do any activity so there would not be any
absolute fuzzy change number (block SCN > CKTP SCN), you are right but Oracle will always require
recovery

FRA Capacity Planning

2008-07-02T03:38:00.000-07:00

Kindly suggest some tips and how to determine my FRA?

First oracle recommended tip is to always keep yours FRA far away from yours Database location
possibly keep it at hard another hard drive, if possible keep this hard drive with separate
controller.

For FRA sizing decide how many days database backup you could maintain within Yours hard disk,
this could be determined by yours SLA ,usually there is maintained two days backup within hard
disk either it is FRA or non FRA,the rest of database backup as well current two days backup
should be transferred to tape cartridge. Typically what I do within my environment could be
helpful for you.

I keep at least last two current days backup for the entire database at hard hard disk within
FRA,beyond 2 days and these last two current days backup I move to tape cartridge, according to
business rule I maintain 30 tape cartridge for 30 days backup to revert back in case of any harm,
I rotate the same cartridge which lapse after 30 days.

i.e JAN tape1..tape30
    FEB tape1..tape30
    MAR tape1..tape30

You can dedicate yours FRA space by cheking yours database file size not with actual data by 

select sum(bytes) from v$datafile
/

Lets say if yours database file size about 80GB then you can either dedicate
2*80+20% extra for yours FRA.I maintain my retention policy for redundancy 2
days.After taking 3rd backup my first backup obsolete and before that any
archivelog also obsolete and can be reclaimable by deleting obsolete via RMAN.

For archivelogs pace you can observe 

select sum(blocks*block_size) archivebytes 
  from v$archived_log
where completion_time > sysdate-1 and status='A'

If you maintain yours archivelog within FRA then you also need to accomodate space for archivelog
within FRA,its recommended to put alls backup stuff within one single place which provide you FRA.

If you decide to put yours archivelog within FRA then yours estimation would 
become (2*80+archivebyte) x+20% x.

The above clue will determine you what should be the size of FRA.


Why last two current day’s backup should be at hard disk (FRA flash recovery Area) why not last
one current backup or why not last three current backup or why not last x backup or why is keep
backup at hard disk at all when I move it to tape cartridge, why to waste space when company
gives me tape cartridge space for each day backup?

the reason is that if you don’t keep any backup at hard disk then it will increase yours downtime
in case of hard disk failure or any failure which should not be for a good DBA,you will have to
have move last current backup from tape cartridge to hard disk (i.e. FRA) which will take x
hours/mins to be restored at hard disk (i.e.FRA), keeping at least one last current backup at
hard disk will save yours time from tape to hard disk. But if you keep 2 last current backup then
it would be safer in case of any mishap with last current backup, you will be able to restore
prior backup to last current one backup and then apply two days log for example

If you keep two last current backup at hard disk (FRA)

JAN 1
JAN 2
JAN 3<----------hard disk fail

You will restore Jan 2 backup but unfortunately yours 2nd Jan backup corrupted for any reason
then you can go with 1st Jan backup, you will restore 1st Jan backup apply archive log till
before the hard disk failure.


If you don’t keep last two backup just keep only last current backup ,in any case yours last
current backup corrupted then again you will have to have move last current backup from tape
cartridge to hard disk (i.e. FRA) which will take x hours/mins to be restored at hard disk (i.e.
FRA),if you came across that tape cartridge last current moved backup also not restorable from
tape cartridge then you will go with previous current backup and apply archivelogs till before
hard disk failure, think isn’t good if you save x hours/min to move backup from tape to hard disk
to make yourself and yours company downtime impact for the cost of purchasing more space for
backup.

Nevertheless if you can accommodate last 3 backups or more then that then I don’t think so there
is any harm but don’t rely at all at yours hard disk backup, move backup also to reliable tape
cartridge media.


Note : Above clue is not for flashback database technology.

ORA-12705: Cannot access NLS data files or invalid environment specified / ORA-01092: ORACLE instance terminated. Disconnection forced

2008-06-16T07:30:00.000-07:00


[oraprod@dbprod ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.2.0 - Production on Mon Jun 16 19:27:00 2008
Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

ERROR:
ORA-12705: Cannot access NLS data files or invalid environment specified

[oraprod@dbprod 10.2.0]$ unset NLS_LANG
[oraprod@dbprod 10.2.0]$ sqlplus sys/sys as sysdba

SQL*Plus: Release 10.2.0.2.0 - Production on Mon Jun 16 16:05:42 2008

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  1264892 bytes
Variable Size             654312196 bytes
Database Buffers          406847488 bytes
Redo Buffers               11317248 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced

   
Check yours alert log file

Errors in file /orahome/PROD/db/tech_st/10.2.0/admin/PROD_dbprod/udump/prod_ora_27138.trc:
ORA-12701: CREATE DATABASE character set is not known
Mon Jun 16 14:38:01 2008
Error 12701 happened during db open, shutting down database
USER: terminating instance due to error 12701
Instance terminated by USER, pid = 27138
ORA-1092 signalled during: ALTER DATABASE OPEN...
Mon Jun 16 14:48:21 2008

The above error comes after running autoconfig on database side.

Cause
-----
One of parameter NLS_LANG or ORA_NLS10 is not set correctly.

Solution
--------
check ora_nls10 path


[oraprod@dbprod udump]$ echo $ORA_NLS10
/orahome/PROD/db/tech_st/10.2.0/ocommon/nls/admin/data


Path should be (10g rel 2) ORA_NLS10="/orahome/PROD/db/tech_st/10.2.0/nls/data/9idata"
it would more appropriate to set the path in environment variable file.


[oraprod@dbprod 10.2.0]$ vi PROD_dbprod.env

#ORA_NLS10="/orahome/PROD/db/tech_st/10.2.0/ocommon/nls/admin/data"
ORA_NLS10="/orahome/PROD/db/tech_st/10.2.0/nls/data/9idata"


Save and then open the terminal again.


or you can change at shell

[oraprod@dbprod 10.2.0]$ echo $ORA_NLS10
/orahome/PROD/db/tech_st/10.2.0/ocommon/nls/admin/data
[oraprod@dbprod 10.2.0]$ ORA_NLS10=/orahome/PROD/db/tech_st/10.2.0/nls/data/9idata
[oraprod@dbprod 10.2.0]$ echo $ORA_NLS10
/orahome/PROD/db/tech_st/10.2.0/nls/data/9idata
[oraprod@dbprod 10.2.0]$ sqlplus sys/sys as sysdba

SQL*Plus: Release 10.2.0.2.0 - Production on Mon Jun 16 16:10:46 2008

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  1264892 bytes
Variable Size             654312196 bytes
Database Buffers          406847488 bytes
Redo Buffers               11317248 bytes
Database mounted.
Database opened.

How it is easy to enable archivelog in 10g

2008-05-23T00:25:00.000-07:00


log_archive_start parameter is for enabling automatic archiving in 9i and "alter 
database archivelog" for changing database log mode to archive mode.

both log_archive_start parameter to TRUE within parameter file and "alter database 
archivelog" command should be executed within 9i box for turning on the archiving 
properly.But in 10g its two in one you can enable archivelog when yours database in 
mount state by executing "alter database archivelog" only.

Here i try to find out how easy to enable archivelog in 10g rather in 9i.First i 
will show you how to enable archivelog mode in 9i then i will show you how easy it 
is in 10g.


SQL> select banner from v$version
  2  /

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE    9.2.0.1.0       Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

SQL> 

SQL> archive log list
   
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            c:\sdsarchive
Oldest online log sequence     18
Next log sequence to archive   20
Current log sequence           20

SQL> show parameter log_archive_start

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_start                    boolean     TRUE

   
The parameter log_archive_start is true within init parameter file now  make it 
false and then start database from this parameter file.



*.log_archive_start=FALSE

SQL> shutdown immediate

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup pfile=c:\initSDS.ora
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.
Database opened.


SQL> archive log list
   
Database log mode              Archive Mode
Automatic archival             Disabled
Archive destination            c:\sdsarchive
Oldest online log sequence     18
Next log sequence to archive   20
Current log sequence           20


SQL> show parameter log_archive_start

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_start                    boolean     FALSE
SQL> 


SQL> alter system switch logfile
  2  /

System altered.

though "Database log mode" is in "Archive mode" but its "Automatic archival" is  
disabled due to this upon log switching redo log is not getting archived within 
archive destination at c:\sdsarchive ,this automatic archival is enabled by 
log_archive_start parameter to TRUE.

Now change log_archive_start parameter from FALSE to TRUE in the parameter file and 
start database with this parameter file.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.


*.log_archive_start=TRUE


SQL> startup pfile=c:\initSDS.ora
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.
Database opened.

SQL> archive log list
   
Database log mode              Archive Mode
Automatic archival             Enabled 
Archive destination            c:\sdsarchive
Oldest online log sequence     20
Next log sequence to archive   22
Current log sequence           22


SQL> show parameter log_archive_start

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_start                    boolean     TRUE

SQL> alter system switch logfile
  2  /

System altered.
   
Now redo log get archived within  destination c:\sdsarchive ,


SQL>shutdown immediate
SQL>startup mount
SQL> alter database noarchivelog
  2  /

Database altered.

SQL> alter database open
  2  /

Database altered.

SQL> archive log list
   
Database log mode              No Archive Mode
Automatic archival             Enabled
Archive destination            c:\sdsarchive
Oldest online log sequence     21
Current log sequence           23

SQL> alter system switch logfile
  2  /

System altered.

SQL> /

System altered.

You will not see any archive file within floder c:\sdsarchive with no archive mode 
but enabling automatic archival by using parameter log_archive_start to TRUE, either 
one of them database log mode to noarchivelog or log_archive_start set to false will 
not let archived the redo logs,both are necessary for enabling the archive mode 
properly in 9i.

But the case is different within 10g ,in 10g log_archive_start is deprecated if you 
set this parameter TRUE within parameter file then at startup you will get error ORA-
32006.

SQL> conn sys/sys as sysdba
Connected.
SQL> select name from v$database
  2  /

NAME
---------
PROD

SQL> select banner from v$version
  2  /

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod
PL/SQL Release 10.1.0.2.0 - Production
CORE    10.1.0.2.0      Production
TNS for 32-bit Windows: Version 10.1.0.2.0 - Production
NLSRTL Version 10.1.0.2.0 - Production

SQL> startup pfile=C:\oracle\product\10.1.0\admin\PROD\pfile\init.ora.412200817533
   
ORA-32006: LOG_ARCHIVE_START initialization parameter has been deprecated
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145488364 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes
Database mounted.
Database opened.

SQL> archive log list
   
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     4
Next log sequence to archive   6
Current log sequence           6
SQL> 

log_archive_start has no effect in 10g either you set it TRUE or FALSE.In 10g you 
can turn on archive log mode and automatic archival enabled only with a single 
command in mount mode is "alter database archivelog"

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145488364 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes
Database mounted.

SQL> archive log list
   
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     14
Next log sequence to archive   16
Current log sequence           16

SQL> alter database noarchivelog
  2  /

Database altered.

SQL> archive log list
   
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     14
Current log sequence           16

SQL> alter database archivelog
  2  /

Database altered.

SQL> archive log list
   
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     14
Next log sequence to archive   16
Current log sequence           16
SQL>

ORA-01152: file 1 was not restored from a sufficiently old backup

2008-05-16T04:56:00.000-07:00

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select name 
  2    from v$database
  3  /

NAME
---------
PROD

SQL> select status
  2    from v$instance
  3  /

STATUS
------------
MOUNTED

SQL> alter database open
  2  /
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> alter database open resetlogs
  2  /
alter database open resetlogs
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\SYSTEM01.DBF'


SQL> recover database using backup controlfile until cancel
ORA-00283: recovery session canceled due to errors
ORA-01111: name for data file 5 is unknown - rename to correct file
ORA-01110: data file 5: 'C:\WINDOWS\SYSTEM32\UNNAMED00005'
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01111: name for data file 5 is unknown - rename to correct file
ORA-01110: data file 5: 'C:\WINDOWS\SYSTEM32\UNNAMED00005'

   
Check Alert Log file you will get the original name of yours database file


   
Wed May 14 18:21:53 2008
alter database recover logfile 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\REDO03.LOG'
Media Recovery Log C:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\REDO03.LOG
File #5 added to control file as 'UNNAMED00005'. Originally created as:
'C:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\MYDATA.DBF'


SQL> alter database rename file 'C:\WINDOWS\SYSTEM32\UNNAMED00005' to 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\MYDATA.DBF'
  2  /
alter database rename file 'C:\WINDOWS\SYSTEM32\UNNAMED00005' to 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\MYDATA.DBF'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01141: error renaming data file 5 - new file
'C:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\MYDATA.DBF' not found
ORA-01111: name for data file 5 is unknown - rename to correct file
ORA-01110: data file 5: 'C:\WINDOWS\SYSTEM32\UNNAMED00005'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.

   
you physically deleted the mydata.dbf file from 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\MYDATA.DBF'



   
what you will do you will copy the users01.dbf to mydata.dbf by using os 
utility and then rename the unnamed00005 to mydata.dbf


SQL> alter database rename file 'C:\WINDOWS\SYSTEM32\UNNAMED00005' to 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\MYDATA.DBF'
  2  /

Database altered.

   
Its wrong trick to copy user01.dbf to mydata.dbf by os 



SQL> recover database using backup controlfile until cancel
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 5: 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\MYDATA.DBF'
ORA-01122: database file 5 failed verification check
ORA-01110: data file 5: 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\MYDATA.DBF'
ORA-01251: Unknown File Header Version read for file number 5

   
Delete the mydata.dbf by os.And try to recover database 



SQL> recover database using backup controlfile until cancel
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 5: 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\MYDATA.DBF'
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\MYDATA.DBF'

   
Now you can see file mydata.dbf is missing now right trick 
is to create mydata.dbf from SQL.



SQL> alter database create datafile 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\MYDATA.DBF' 
  2  /

Database altered.

SQL> recover database using backup controlfile until cancel
ORA-00279: change 456977 generated at 05/14/2008 17:23:49 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\PROD\ARCHIVELOG\2008_05_16\O1_MF_1_1_%U_.ARC
ORA-00280: change 456977 for thread 1 is in sequence #1


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

ORA-00279: change 458022 generated at 05/14/2008 18:01:07 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\PROD\ARCHIVELOG\2008_05_16\O1_MF_1_2_%U_.ARC
ORA-00280: change 458022 for thread 1 is in sequence #2
ORA-00278: log file 'C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\PROD\ARCHIVELOG\2008_05_14\O1_MF_1_1_42ORNO5P_.ARC' no longer needed for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
C:\oracle\product\10.1.0\oradata\Prod\REDO01.log
Log applied.
Media recovery complete.

As i applied redo for the REOD01.log file it was current in my case thats why it is giving Media recovery complete.

SQL> alter database open resetlogs
  2  /

Database altered.

SQL> select substr(name,1,100) name,status
  2    from v$datafile
  3  /

NAME                                                   STATUS
------------------------------------------------------ -------
C:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\SYSTEM01.DBF     SYSTEM
C:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\UNDOTBS01.DBF    ONLINE
C:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\SYSAUX01.DBF     ONLINE
C:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\USERS01.DBF      ONLINE
C:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\MYDATA.DBF       ONLINE

SQL>

How to perform Incomplete Recovery

2008-05-14T00:11:00.000-07:00

RMAN> backup database;


user is sys


SQL> select count(*) from t
  2  /

  COUNT(*)
----------
         0

SQL> begin
  2    for i in 1..50
  3    loop
  4     insert into t values (i);
  5    end loop;
  6  end;
  7  .
SQL> /

PL/SQL procedure successfully completed.

SQL> commit
  2  /

Commit complete.

SQL> select count(*) from t
  2  /

  COUNT(*)
----------
        50

SQL> select to_char(sysdate,'DD-MM-YYYY HH24:MI:SS')
  2    from dual
  3  /

TO_CHAR(SYSDATE,'DD
-------------------
13-05-2008 16:09:26

SQL> delete from t where a between 1 and 25
  2  /

25 rows deleted.

SQL> commit
  2  /

Commit complete.

SQL> select count(*) from t
  2  /

  COUNT(*)
----------
        25

RMAN> run
2> {
3> shutdown immediate;
4> startup mount;
5> set until time "to_date('13-05-2008 16:09:26','DD-MM-YYYY HH24:MI:SS')";
6> recover database;
7> }

database closed
database dismounted
Oracle instance shut down

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area     171966464 bytes

Fixed Size                      787988 bytes
Variable Size                145488364 bytes
Database Buffers              25165824 bytes
Redo Buffers                    524288 bytes

executing command: SET until clause

Starting recover at 13-MAY-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=160 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/13/2008 16:13:00
RMAN-06555: datafile 1 must be restored from backup created before 13-MAY-08

 
Yours datafiles are newer than 13-MAY-2008 16:09:26 which contains the committed 
data ,for incomplete recovery how will you apply redo??

To perform incomplete media recovery, you must restore all datafiles from backups 
created prior to the time to which you want to recover.

RMAN> run
2> {
3> shutdown immediate;
4> startup mount;
5> set until time "to_date('13-05-2008 16:09:26','DD-MM-YYYY HH24:MI:SS')";
6> restore database;
7> recover database;
8> }

database dismounted
Oracle instance shut down

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area     171966464 bytes

Fixed Size                      787988 bytes
Variable Size                145488364 bytes
Database Buffers              25165824 bytes
Redo Buffers                    524288 bytes

executing command: SET until clause

Starting restore at 13-MAY-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=160 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 C:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\SYSTEM01.DBF
restoring datafile 00002 to C:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\UNDOTBS01.DB
restoring datafile 00003 to C:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\SYSAUX01.DBF
restoring datafile 00004 to C:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\USERS01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\PROD\BACKUPSET\2008_
_13\O1_MF_NNNDF_TAG20080513T155846_42LX37BF_.BKP tag=TAG20080513T155846
channel ORA_DISK_1: restore complete
Finished restore at 13-MAY-08

Starting recover at 13-MAY-08
using channel ORA_DISK_1

starting media recovery
media recovery complete

Finished recover at 13-MAY-08


RMAN> alter database open resetlogs
2> ;

database opened

SQL> conn sys/sys as sysdba
Connected.
SQL> desc t
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------------------
 A                                                  NUMBER

SQL> select count(*)
  2    from t
  3  /

  COUNT(*)
----------
        50

SQL>

How to run trace?

2008-05-14T00:01:00.000-07:00

login as: oraprod
oraprod@192.168.3.8's password:
Last login: Mon May 12 04:50:10 2008 from trainee1.parazelsus.pk
[oraprod@dbprod ~]$ sqlplus scott/tiger

SQL*Plus: Release 10.2.0.2.0 - Production on Tue May 13 23:44:24 2008

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options



SQL> conn sys/sys as sysdba
Connected.
SQL> grant alter session to scott
  2  /

Grant succeeded.

SQL> conn scott/tiger
Connected.

SQL> set linesize 1000
SQL> alter session set sql_trace=true
  2  /

Session altered.

SQL> select * from emp
  2  /

     EMPNO ENAME                          JOB                                MGR HIREDATE               SAL       COMM     DEPTNO
---------- ------------------------------ --------------------------- ---------- --------------- ---------- ---------- ----------
      7369 SMITH                          CLERK                             7902 17-DEC-80              800                    20
      7499 ALLEN                          SALESMAN                          7698 20-FEB-81             1600        300         30
      7521 WARD                           SALESMAN                          7698 22-FEB-81             1250        500         30
      7566 JONES                          MANAGER                           7839 02-APR-81             2975                    20
      7654 MARTIN                         SALESMAN                          7698 28-SEP-81             1250       1400         30
      7698 BLAKE                          MANAGER                           7839 01-MAY-81             2850                    30
      7782 CLARK                          MANAGER                           7839 09-JUN-81             2450                    10
      7788 SCOTT                          ANALYST                           7566 09-DEC-82             3000                    20
      7839 KING                           PRESIDENT                              17-NOV-81             5000                    10
      7844 TURNER                         SALESMAN                          7698 08-SEP-81             1500          0         30
      7876 ADAMS                          CLERK                             7788 12-JAN-83             1100                    20
      7900 JAMES                          CLERK                             7698 03-DEC-81              950                    30
      7902 FORD                           ANALYST                           7566 03-DEC-81             3000                    20
      7934 MILLER                         CLERK                             7782 23-JAN-82             1300                    10

14 rows selected.

SQL> alter session set sql_trace=false
  2  /

Session altered.

SQL> disconnect
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> exit
[oraprod@dbprod ~]$ cd /orahome/PROD/db/tech_st/10.2.0/admin/PROD_dbprod/udump
[oraprod@dbprod udump]$ ls -lrt
total 8928
.
.
.
.
-rw-r--r--  1 oraprod dba     845 Apr 15 17:53 prod_ora_4267.trc
-rw-r-----  1 oraprod dba     689 Apr 15 19:54 prod_ora_28473.trc
-rw-r--r--  1 oraprod dba     718 Apr 15 19:54 prod_ora_28499.trc
-rw-r--r--  1 oraprod dba     827 Apr 15 21:55 prod_ora_30662.trc
-rw-r--r--  1 oraprod dba 4471139 Apr 16 22:51 prod_ora_24132.trc
-rw-r--r--  1 oraprod dba     718 Apr 16 22:51 prod_ora_24610.trc
-rw-r-----  1 oraprod dba     661 Apr 16 22:51 prod_ora_24586.trc
-rw-r--r--  1 oraprod dba     986 Apr 16 22:52 prod_ora_24612.trc
-rw-r-----  1 oraprod dba     659 Apr 17 10:51 prod_ora_2315.trc
-rw-r--r--  1 oraprod dba     716 Apr 17 10:51 prod_ora_2339.trc
-rw-r--r--  1 oraprod dba     782 Apr 17 10:51 prod_ora_2341.trc
-rw-r--r--  1 oraprod dba     845 Apr 17 14:19 prod_ora_6153.trc
-rw-r-----  1 oraprod dba     661 May  8 07:13 prod_ora_27379.trc
-rw-r--r--  1 oraprod dba     718 May  8 07:13 prod_ora_27403.trc
-rw-r--r--  1 oraprod dba     784 May  8 07:13 prod_ora_27405.trc
-rw-r--r--  1 oraprod dba   27354 May 13 23:46 prod_ora_4613.trc

[oraprod@dbprod udump]$ tkprof prod_ora_4613.trc prod_ora_4613.txt explain=scott/tiger sys=no

TKPROF: Release 10.2.0.2.0 - Production on Tue May 13 23:48:25 2008

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


[oraprod@dbprod udump]$ ls -lrt
total 8936
.
.
.
.
-rw-r-----  1 oraprod dba     661 Apr 16 22:51 prod_ora_24586.trc
-rw-r--r--  1 oraprod dba     986 Apr 16 22:52 prod_ora_24612.trc
-rw-r-----  1 oraprod dba     659 Apr 17 10:51 prod_ora_2315.trc
-rw-r--r--  1 oraprod dba     716 Apr 17 10:51 prod_ora_2339.trc
-rw-r--r--  1 oraprod dba     782 Apr 17 10:51 prod_ora_2341.trc
-rw-r--r--  1 oraprod dba     845 Apr 17 14:19 prod_ora_6153.trc
-rw-r-----  1 oraprod dba     661 May  8 07:13 prod_ora_27379.trc
-rw-r--r--  1 oraprod dba     718 May  8 07:13 prod_ora_27403.trc
-rw-r--r--  1 oraprod dba     784 May  8 07:13 prod_ora_27405.trc
-rw-r--r--  1 oraprod dba   27354 May 13 23:46 prod_ora_4613.trc
-rw-r--r--  1 oraprod dba    7245 May 13 23:48 prod_ora_4613.txt

[oraprod@dbprod udump]$ vi prod_ora_4613.txt

Recovery with an old controlfile/recover database using backup controlfile

2008-05-10T06:50:00.000-07:00

Question :

I have a full backup wıth control file and archıvelogs which is taken by rman yesterday.Today my db ıs crashed at the evenıng and control fıle ıs corrupted. When I restore yesterdays controlfıle from backupset,and restore all yesterday's datafiles,can I apply todays archivelogs and apply to these datafiles? Do I lose any data in this scenerio?

Answer :

When you take online/hot backup then yours backup is inconsistent , upon recovery oracle makes inconsistent backups consistent by applying all archived and online redo logs ,oracle makes recovery by reading the earliest/oldest SCN in any of the datafile headers and apply the changes from the logs back into the datafile. yes indeed controlfile has repositry about alls backup stuff like database backup as well archivelog backup.If you take backup of archivelog then it goes to control file entry ,upon restoration activity oracle reads the controlfile to ask where archivelog exist to be restored.If yours todays archivelogs are on disk not in controlfile repositry then oracle will apply these todays archivelog regardless controlfile knows or not,If the RMAN repository or controlfile indicates that no copies of a needed log sequence number exist on disk, then RMAN looks in backups and restores archived redo logs as needed to perform the media recovery,there is no concern controlfile for applying archivelogs at all,if you have todays archivelogs exist then you will lose data only which is in current redo log thats why multiplexing redo log came into ours dbackup plan.

Question :

but control file is older it doesnt store the info of new archivelogs.How can I add?

Answer :

Contorolfile older issue raise when you have controlfile for yesterday and datafile for today,if you restore yesterdays controlfle and restore the datafile from this yesterday controlfile and then started recovery to this yesterday controlfile then there would be no any issue to recover the database till before crash using todays archivelogs.Here you have to remember one thing you will lose only data for current redo log ,if you have redo log keep intact by multiplexing then you can recover this multiplexed current redo log data also. If yesterdays controlfile dont know todays database physical activity i.e adding datafile then archivelog data for this todays datafile applying will raise the error during recovery. Here i am going to prove that yesterdays controlfile will not be an issue for tracking the todays archivelog during recovery process and it will apply safely.

SQL> select sysdate from dual
  2  / 
 
SYSDATE
---------
19-APR-08
 
SQL> create table a as select * from all_objects
  2  / 
 
Table created.
 
SQL> create table b as select * from all_objects
  2  / 
 
Table created.
 
SQL> create table c as select * from all_objects
  2  / 
 
Table created.
 
SQL> create table d as select * from all_objects
  2  / 
 
Table created.
 
C:\oracle\product\10.1.0\flash_recovery_area\ORCL1\ARCHIVELOG\2008_04_19>dir
 Volume in drive C is khurram
 Volume Serial Number is F49D-FF2B
 
 Directory of C:\oracle\product\10.1.0\flash_recovery_area\ORCL1\ARCHIVELOG\2008
_04_19
 
04/19/2008  02:37 PM    <DIR>          .
04/19/2008  02:37 PM    <DIR>          ..
04/19/2008  02:36 PM         9,754,112 O1_MF_1_7_40MH8FC9_.ARC
04/19/2008  02:37 PM         9,753,088 O1_MF_1_8_40MHCDZ4_.ARC
               2 File(s)     19,507,200 bytes
               2 Dir(s)  58,626,342,912 bytes free
 
RMAN> run
2> {
3> allocate channel t1 type disk;
4> backup database;
5> release channel t1;
6> allocate channel t2 type disk;
7> backup archivelog all;
8> release channel t2;
9> }
 
released channel: ORA_DISK_1
allocated channel: t1
channel t1: sid=125 devtype=DISK
 
Starting backup at 19-APR-08
channel t1: starting full datafile backupset
channel t1: specifying datafile(s) in backupset
input datafile fno=00001 name=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\SYSTEM01.DBF
input datafile fno=00003 name=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\SYSAUX01.DBF
input datafile fno=00004 name=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\USERS01.DBF
input datafile fno=00002 name=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\UNDOTBS01.DBF
channel t1: starting piece 1 at 19-APR-08
channel t1: finished piece 1 at 19-APR-08
piece handle=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\BACKUPSET\2008_04_19\O1_MF_NNNDF_TAG20080419T144156_40MHMB71_.BKP comment=NONE
channel t1: backup set complete, elapsed time: 00:01:56
Finished backup at 19-APR-08
 
Starting Control File and SPFILE Autobackup at 19-APR-08
piece handle=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\AUTOBACKUP\2008_04_19\O1_MF_S_652459435_40MHPXCT_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 19-APR-08
 
released channel: t1
 
allocated channel: t2
channel t2: sid=125 devtype=DISK
 
Starting backup at 19-APR-08
current log archived
channel t2: starting archive log backupset
channel t2: specifying archive log(s) in backup set
input archive log thread=1 sequence=7 recid=102 stamp=652458975
input archive log thread=1 sequence=8 recid=103 stamp=652459070
input archive log thread=1 sequence=9 recid=104 stamp=652459454
channel t2: starting piece 1 at 19-APR-08
channel t2: finished piece 1 at 19-APR-08
piece handle=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\BACKUPSET\2008_04_19\O1_MF_ANNNN_TAG20080419T144414_40MHQJNO_.BKP comment=NONE
channel t2: backup set complete, elapsed time: 00:00:08
Finished backup at 19-APR-08
 
Starting Control File and SPFILE Autobackup at 19-APR-08
piece handle=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\AUTOBACKUP\2008_04_19\O1_MF_S_652459464_40MHQT1N_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 19-APR-08
 
released channel: t2
 
SQL> conn sys/sys@orcl1 as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> 
 
C:\oracle\product\10.1.0\flash_recovery_area\ORCL1\ARCHIVELOG\2008_04_19>dir
 Volume in drive C is khurram
 Volume Serial Number is F49D-FF2B
 
 Directory of C:\oracle\product\10.1.0\flash_recovery_area\ORCL1\ARCHIVELOG\2008
_04_19
 
04/19/2008  02:44 PM    <DIR>          .
04/19/2008  02:44 PM    <DIR>          ..
04/19/2008  02:36 PM         9,754,112 O1_MF_1_7_40MH8FC9_.ARC
04/19/2008  02:37 PM         9,753,088 O1_MF_1_8_40MHCDZ4_.ARC
04/19/2008  02:44 PM         2,507,776 O1_MF_1_9_40MHQFYX_.ARC
               3 File(s)     22,014,976 bytes
               2 Dir(s)  58,033,848,320 bytes free
 
SQL> conn sys/sys@orcl1 as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

   
---------------------------------------------------------------------
change the server date from 19-APR-2008 to 20-APR-2008
---------------------------------------------------------------------

 
SQL> startup
ORACLE instance started.
 
Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145488364 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes
Database mounted.
Database opened.
SQL> select sysdate from dual
  2  / 
 
SYSDATE
---------
20-APR-08
 
SQL> conn scott/tiger@orcl1
Connected.
SQL> create table e as select * from all_objects
  2  / 
 
Table created.
 
SQL> create table f as select * from all_objects
  2  / 
 
Table created.
 
SQL> create table g as select * from all_objects
  2  / 
 
Table created.
 
SQL> create table h  as select * from all_objects
  2  / 
 
Table created.
 
C:\oracle\product\10.1.0\flash_recovery_area\ORCL1\ARCHIVELOG\2008_04_20>dir
 Volume in drive C is khurram
 Volume Serial Number is F49D-FF2B
 
 Directory of C:\oracle\product\10.1.0\flash_recovery_area\ORCL1\ARCHIVELOG\2008
_04_20
 
04/20/2008  02:53 PM    <DIR>          .
04/20/2008  02:53 PM    <DIR>          ..
04/20/2008  02:51 PM         9,756,160 O1_MF_1_10_40P4KWQ8_.ARC
04/20/2008  02:52 PM         9,751,552 O1_MF_1_11_40P4MH79_.ARC
04/20/2008  02:53 PM         9,750,016 O1_MF_1_12_40P4NK9V_.ARC
               3 File(s)     29,257,728 bytes
               2 Dir(s)  57,978,466,304 bytes free
 
SQL> conn sys/sys@orcl1 as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
 
C:\oracle\product\10.1.0\oradata\orcl1>dir
 Volume in drive C is khurram
 Volume Serial Number is F49D-FF2B
 
 Directory of C:\oracle\product\10.1.0\oradata\orcl1
 
04/18/2008  06:31 PM    <DIR>          .
04/18/2008  06:31 PM    <DIR>          ..
04/20/2008  02:55 PM         2,965,504 CONTROL01.CTL
04/20/2008  02:55 PM         2,965,504 CONTROL02.CTL
04/20/2008  02:55 PM         2,965,504 CONTROL03.CTL
04/20/2008  02:55 PM        10,486,272 REDO01.LOG
04/20/2008  02:55 PM        10,486,272 REDO02.LOG
04/20/2008  02:55 PM        10,486,272 REDO03.LOG
04/20/2008  02:55 PM       251,666,432 SYSAUX01.DBF
04/20/2008  02:55 PM       461,381,632 SYSTEM01.DBF
04/20/2008  02:55 PM        26,222,592 UNDOTBS01.DBF
04/20/2008  02:55 PM        53,747,712 USERS01.DBF
              10 File(s)    833,373,696 bytes
               2 Dir(s)  57,978,585,088 bytes free

   
----------------------------------------------------------------------------
Delete alls database files
----------------------------------------------------------------------------
 
C:\oracle\product\10.1.0\oradata\orcl1>del *.*
C:\oracle\product\10.1.0\oradata\orcl1\*.*, Are you sure (Y/N)? Y
 
C:\oracle\product\10.1.0\oradata\orcl1>dir
 Volume in drive C is khurram
 Volume Serial Number is F49D-FF2B
 
 Directory of C:\oracle\product\10.1.0\oradata\orcl1
 
04/20/2008  02:56 PM    <DIR>          .
04/20/2008  02:56 PM    <DIR>          ..
               0 File(s)              0 bytes
               2 Dir(s)  58,811,949,056 bytes free
 
C:\oracle\product\10.1.0\oradata\orcl1>
 
SQL> startup
ORACLE instance started.
 
Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145488364 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes
ORA-00205: error in identifying controlfile, check alert log for more info
 
C:\>rman
 
Recovery Manager: Release 10.1.0.2.0 - Production
 
Copyright (c) 1995, 2004, Oracle.  All rights reserved.
 
RMAN> connect target sys/sys@orcl1
 
connected to target database: orcl1 (not mounted)
 
RMAN> restore controlfile from autobackup
2> ;
 
Starting restore at 20-APR-08
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK
 
recovery area destination: C:\oracle\product\10.1.0\flash_recovery_area
database name (or lock name space) used for search: ORCL1
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\AUTOBACKUP\2008_04_19\O1_MF_S_652459464_40MHQT1N_.BKP
channel ORA_DISK_1: controlfile restore from autobackup complete
output filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\CONTROL01.CTL
output filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\CONTROL02.CTL
output filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\CONTROL03.CTL
Finished restore at 20-APR-08
 
RMAN> startup mount
 
database is already started
database mounted
 
RMAN> restore database
2> ;
 
ORACLE error from target database:
ORA-19922: there is no parent row with id 0 and level 0
ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 5038
ORA-06512: at line 1
 
RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows
Starting restore at 20-APR-08
Starting implicit crosscheck backup at 20-APR-08
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 20-APR-08
 
Starting implicit crosscheck copy at 20-APR-08
using channel ORA_DISK_1
Finished implicit crosscheck copy at 20-APR-08
 
searching for all files in the recovery area
cataloging files...
cataloging done
 
List of Cataloged Files
=======================
File Name: C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2008_04_20\O1_MF_1_10_40P4KWQ8_.ARC
File Name: C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2008_04_20\O1_MF_1_11_40P4MH79_.ARC
File Name: C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2008_04_20\O1_MF_1_12_40P4NK9V_.ARC
File Name: C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\AUTOBACKUP\2008_04_19\O1_MF_S_652459464_40MHQT1N_.BKP
 
using channel ORA_DISK_1
 
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\SYSTEM01.DBF
restoring datafile 00002 to C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\UNDOTBS01.DBF
restoring datafile 00003 to C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\SYSAUX01.DBF
restoring datafile 00004 to C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\USERS01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\BACKUPSET\2008_04_19\O1_MF_NNNDF_TAG20080419T144156_40MHMB71_.BKP tag=TAG20080419T144156
channel ORA_DISK_1: restore complete
Finished restore at 20-APR-08
 
RMAN> recover database
2> ;
 
Starting recover at 20-APR-08
using channel ORA_DISK_1
 
starting media recovery
 
archive log thread 1 sequence 9 is already on disk as file C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2008_04_19\O1_MF_1_9_40MHQFYX_.ARC
archive log thread 1 sequence 10 is already on disk as file C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2008_04_20\O1_MF_1_10_40P4KWQ8_.ARC
archive log thread 1 sequence 11 is already on disk as file C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2008_04_20\O1_MF_1_11_40P4MH79_.ARC
archive log thread 1 sequence 12 is already on disk as file C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2008_04_20\O1_MF_1_12_40P4NK9V_.ARC
archive log filename=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2008_04_19\O1_MF_1_9_40MHQFYX_.ARC thread=1 sequence=9
archive log filename=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2008_04_20\O1_MF_1_10_40P4KWQ8_.ARC thread=1 sequence=10
archive log filename=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2008_04_20\O1_MF_1_11_40P4MH79_.ARC thread=1 sequence=11
archive log filename=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2008_04_20\O1_MF_1_12_40P4NK9V_.ARC thread=1 sequence=12
unable to find archive log
archive log thread=1 sequence=13
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 04/20/2008 15:01:51
RMAN-06054: media recovery requesting unknown log: thread 1 seq 13 lowscn 698325
 
 
RMAN> report obsolete
2> ;
 
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of obsolete backups and copies
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           142    19-APR-08
  Backup Piece       142    19-APR-08          C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\AUTOBACKUP\2008_04_19\O1_MF_S_652459435_40MHPXCT_.BKP
Archive Log          102    19-APR-08          C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2008_04_19\O1_MF_1_7_40MH8FC9_.ARC
Archive Log          103    19-APR-08          C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVELOG\2008_04_19\O1_MF_1_8_40MHCDZ4_.ARC
 
RMAN> alter database open resetlogs
2> ;
 
database opened
 
RMAN>

As you can see i have redundency 1 thats why at recovery these archivelogs are not
getting applied,nevertheless these 19th April archivlogs are no more required to 
apply logs but as you can see 20th april logs are being applied.
 
when i connect to sql i got alls table a,b,c,d,e,f,g but not h cause h data was in 
redolog and was not archived before smoking alls database files.

SQL> conn scott/tiger@orcl1
Connected.
SQL> desc a
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(30)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                          NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(19)
 CREATED                                            DATE
 LAST_DDL_TIME                                      DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)
 
SQL> desc b
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(30)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                          NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(19)
 CREATED                                            DATE
 LAST_DDL_TIME                                      DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)
 
SQL> desc c
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(30)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                          NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(19)
 CREATED                                            DATE
 LAST_DDL_TIME                                      DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)
 
SQL> desc d
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(30)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                          NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(19)
 CREATED                                            DATE
 LAST_DDL_TIME                                      DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)
 
SQL> desc e
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(30)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                          NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(19)
 CREATED                                            DATE
 LAST_DDL_TIME                                      DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)
 
SQL> desc f
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(30)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                          NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(19)
 CREATED                                            DATE
 LAST_DDL_TIME                                      DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)
 
SQL> desc g
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(30)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                          NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(19)
 CREATED                                            DATE
 LAST_DDL_TIME                                      DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)
 
SQL> desc h
ERROR:
ORA-04043: object h does not exist

You can see table a,b,c,d recoverd which is for 19th April data and e,f,g recoverd 
cause 20th April archivelogs applied to 19th April backupset with 19th April 
controlfile.



Question :



But as i have old controlfile then whats the use of recover database using backup 
controlfile as i know recover database using backup controlfile is used when you 
have old controlfile.



Answer :



Yes you are right but you are missing obvious that using old controlfile recovery by
using recover database using backup controlfile command is used when you have old
controlfile and datafiles are current.Lets see a scenario which will depictyou a 
clear picture when to use recover database using backup controlfile.

Lets suppose i am taking cold backup daily ,my procedure is to shutdown the database
and then copy alls database file to somewhere safe daily at the end of day ,one day
i lost/corrupt the controlfile, i dont have current controlfile i have only 
controlfile backup which is backed up as cold last day.In this case i have two
options either i create the controlfile by using the create controlfile command then
perform recovery if required and start up the database.Other option is to restore
the last cold backed up controlfile and recover database using backup controlfile.

If you try to recover database without using backup controlfile command then you
will get the error.




ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\SYSTEM01.DBF'
ORA-01207: file is more recent than controlfile - old controlfile


ORA-1122:  database file 1 failed verification check
 
Cause:    The information in this file (system01.dbf) is inconsistent with information from the control file. 
 
Action:   Make sure that the db files and control files are the correct files for this database. 
  
 
ORA-1207  file is more recent than control file - old control file 

Cause:    The control file change sequence number in the data file is greater than the number in the control file. This 
          implies that the wrong control file is being used. Note that repeatedly causing this error can make it stop 
          happening without correcting the real problem.  Every attempt to open the database will advance the control 
          file change sequence number until it is great enough. 
 
Action:   Use the current control file or do backup controlfile recovery to make the control file current. Be sure to 
          follow all restrictions on doing a backup controlfile recovery.

SQL> conn sys/sys as sysdba
Connected.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     4
Next log sequence to archive   6
Current log sequence           6

SQL> select group#,members,status 
  2    from v$log
  3  /

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------
         1          1 CURRENT
         2          1 UNUSED
         3          1 UNUSED

SQL> 

SQL> conn scott/tiger
Connected.
SQL> create table x1 as select * from all_objects
  2  /

Table created.

SQL> create table x2 as select * from all_objects
  2  /

Table created.

SQL> conn sys/sys as sysdba
Connected.

SQL> alter system switch logfile
  2  /

System altered.

SQL> select group#,members,status 
  2    from v$log
  3  /

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------
         1          1 ACTIVE
         2          1 CURRENT
         3          1 UNUSED

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> 

Copy the database folder to somewhere safe place

 C:\oracle\product\10.1.0\oradata>copy prod  coldbackup
prod\CONTROL01.CTL
prod\CONTROL02.CTL
prod\CONTROL03.CTL
prod\REDO01.LOG
prod\REDO02.LOG
prod\REDO03.LOG
prod\SYSAUX01.DBF
prod\SYSTEM01.DBF
prod\TEMP01.DBF
prod\UNDOTBS01.DBF
prod\USERS01.DBF
       11 file(s) copied.

C:\oracle\product\10.1.0\oradata>


Now startup the database.

SQL> startup
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145488364 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes
Database mounted.
Database opened.


You have taken cold backup, now perform some activity


SQL> select group#,members,status 
  2    from v$log
  3  /

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------
         1          1 INACTIVE
         2          1 CURRENT
         3          1 UNUSED


SQL> conn scott/tiger
Connected.
SQL> 
SQL> create table x3 as select * from all_objects
  2  /

Table created.

SQL> alter system switch logfile
  2  /

System altered.

SQL> select group#,members,status 
  2    from v$log
  3  /

    GROUP#    MEMBERS STATUS
---------- ---------- ----------------
         1          1 INACTIVE
         2          1 ACTIVE
         3          1 CURRENT



Now delete or corrupt the controlfile ,i am deleting here alls controlfile here



SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> 

C:\oracle\product\10.1.0\oradata\Prod>del *.ctl

C:\oracle\product\10.1.0\oradata\Prod>dir *.ctl
 Volume in drive C is khurram
 Volume Serial Number is F49D-FF2B

 Directory of C:\oracle\product\10.1.0\oradata\Prod

File Not Found


Now startup the database


SQL> startup
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145488364 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes
ORA-00205: error in identifying controlfile, check alert log for more info

SQL> shutdown abort
ORACLE instance shut down.
SQL> 


Copy the controlfile from coldbackup to yours Prod database file folder.



C:\oracle\product\10.1.0\oradata>copy c:\oracle\product\10.1.0\oradata\coldbackup\*.ctl  C:\oracle\product\10.1.0\oradata\prod
c:\oracle\product\10.1.0\oradata\coldbackup\CONTROL01.CTL
c:\oracle\product\10.1.0\oradata\coldbackup\CONTROL02.CTL
c:\oracle\product\10.1.0\oradata\coldbackup\CONTROL03.CTL
        3 file(s) copied.

C:\oracle\product\10.1.0\oradata>


Now the controlfiles from cold backup to prod folder copied succesfully ,start the database


SQL> startup
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145488364 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes
Database mounted.
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\SYSTEM01.DBF'
ORA-01207: file is more recent than controlfile - old controlfile


SQL> recover database

ORA-00283: recovery session canceled due to errors
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\PROD\SYSTEM01.DBF'
ORA-01207: file is more recent than controlfile - old controlfile


SQL> recover database using backup controlfile until cancel

ORA-00279: change 333130 generated at 05/10/2008 16:42:05 needed for thread 1
ORA-00289: suggestion :
C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\PROD\ARCHIVELOG\2008_05_10\O1_MF_1_2_%U_.ARC
ORA-00280: change 333130 for thread 1 is in sequence #2


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

ORA-00279: change 334273 generated at 05/10/2008 16:48:51 needed for thread 1
ORA-00289: suggestion :
C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\PROD\ARCHIVELOG\2008_05_10\O1_MF_1_3_%U_.ARC
ORA-00280: change 334273 for thread 1 is in sequence #3
ORA-00278: log file
'C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\PROD\ARCHIVELOG\2008_05_10\O1_MF_1_2_42C2X57F_.ARC' no longer needed for this recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
C:\oracle\product\10.1.0\oradata\Prod\REDO03.log
Log applied.
Media recovery complete.

SQL> alter database open resetlogs
  2  /

Database altered.







Question :
But You showed me up without any new added datafile with old controlfile recovery 
and yours scenario with cold backup ,my case is hot backup and i have new datafile 
but old controlfile backup ,i lost old control file but datafiles exist on disk
presumably some data files are added in database file before losing controlfile and 
old controlfile does not know about it how will i make recovery in this scenario


Answer :

The process would be the same in yours scenario as i mentioned above, you will have 
to make recovery with "recover database using backup controlfile" when you have old 
controlfile and new datafiles.Lets see here


Recovery Manager: Release 10.1.0.2.0 - Production

Copyright (c) 1995, 2004, Oracle.  All rights reserved.

RMAN> connect target sys/sys@uat

connected to target database: UAT (DBID=2679299118)

RMAN> list backup of controlfile
2> ;

using target database controlfile instead of recovery catalog

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8       Full    2M         DISK        00:00:03     16-MAY-08
        BP Key: 8   Status: AVAILABLE  Compressed: NO  Tag: TAG20080516T002026
        Piece Name: C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\UAT\BACKUPSET\2008_05_16\O1_MF_NCSNF_TAG20080516T002026_42S3B7SW_.BKP
  Controlfile Included: Ckp SCN: 408253       Ckp time: 16-MAY-08


SQL> conn sys/sys@uat as sysdba
Connected.


SQL> select ts#,name
  2    from v$tablespace
  3  /

       TS# NAME
---------- ------------------------------
         0 SYSTEM
         1 UNDOTBS1
         2 SYSAUX
         4 USERS
         3 TEMP

SQL> select ts#,file#,substr(name,1,50) name,status,enabled
  2    from v$datafile
  3  /

       TS#      FILE# NAME                                               STATUS  ENABLED
---------- ---------- -------------------------------------------------- ------- ----------
         0          1 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\SYSTEM01.DBF  SYSTEM  READ WRITE
         1          2 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\UNDOTBS01.DBF ONLINE  READ WRITE
         2          3 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\SYSAUX01.DBF  ONLINE  READ WRITE
         4          4 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\USERS01.DBF   ONLINE  READ WRITE

SQL> create tablespace newtablespace datafile 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\newdatafile01.dbf' size 100M
  2  /

Tablespace created.

   
make sure added tablespace datafile is online and enabled for read and write.


SQL> select ts#,name
  2    from v$tablespace
  3  /

       TS# NAME
---------- ------------------------------
         0 SYSTEM
         1 UNDOTBS1
         2 SYSAUX
         4 USERS
         3 TEMP
         6 NEWTABLESPACE

6 rows selected.

SQL> 
SQL> select ts#,file#,substr(name,1,50) name,status,enabled
  2    from v$datafile
  3  /

       TS#      FILE# NAME                                               STATUS  ENABLED
---------- ---------- -------------------------------------------------- ------- ----------
         0          1 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\SYSTEM01.DBF  SYSTEM  READ WRITE
         1          2 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\UNDOTBS01.DBF ONLINE  READ WRITE
         2          3 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\SYSAUX01.DBF  ONLINE  READ WRITE
         4          4 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\USERS01.DBF   ONLINE  READ WRITE
         6          5 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\NEWDATAFILE01 ONLINE  READ WRITE


   
make default this newtablespace for the user scott



SQL> alter user scott default tablespace newtablespace
  2  /

User altered.

SQL> conn scott/tiger@uat
Connected.
SQL> create table v1 as select * from all_objects
  2  /

Table created.

SQL> create table v2 as select * from all_objects
  2  /

Table created.

SQL> conn sys/sys@uat as sysdba
Connected.
SQL> alter system switch logfile
  2  /

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> 

Here i drop the controld files 

C:\oracle\product\10.1.0\oradata\uat>dir *.ctl
 Volume in drive C is khurram
 Volume Serial Number is F49D-FF2B

 Directory of C:\oracle\product\10.1.0\oradata\uat

05/26/2008  02:10 PM         2,899,968 CONTROL01.CTL
05/26/2008  02:10 PM         2,899,968 CONTROL02.CTL
05/26/2008  02:10 PM         2,899,968 CONTROL03.CTL
               3 File(s)      8,699,904 bytes
               0 Dir(s)  43,394,301,952 bytes free

C:\oracle\product\10.1.0\oradata\uat>del *.ctl

C:\oracle\product\10.1.0\oradata\uat>dir *.ctl
 Volume in drive C is khurram
 Volume Serial Number is F49D-FF2B

 Directory of C:\oracle\product\10.1.0\oradata\uat

File Not Found

C:\>rman

Recovery Manager: Release 10.1.0.2.0 - Production

Copyright (c) 1995, 2004, Oracle.  All rights reserved.

RMAN> connect target sys/sys@uat

connected to target database (not started)

RMAN> startup

Oracle instance started
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 05/26/2008 14:18:02
ORA-00205: error in identifying controlfile, check alert log for more info

RMAN> restore controlfile from 'C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\UAT
\BACKUPSET\2008_05_16\O1_MF_NCSNF_TAG20080516T002026_42S3B7SW_.BKP'
2> ;

Starting restore at 26-MAY-08
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=160 devtype=DISK

channel ORA_DISK_1: restoring controlfile
channel ORA_DISK_1: restore complete
output filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\CONTROL01.CTL
output filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\CONTROL02.CTL
output filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\CONTROL03.CTL
Finished restore at 26-MAY-08

RMAN>
   
you can see the controlfile restored for the date of 16th May,2008 and datafile are 
for today dated 26th May ,2008 and one of datafile named newdatafile01.dbf has been 
added today dated 26th MAy,2008,above restored controlfile doesnt aware about new 
added data file newdatafile01.dbf which exist on disk.


C:\oracle\product\10.1.0\oradata\uat>dir *.dbf
 Volume in drive C is khurram
 Volume Serial Number is F49D-FF2B

 Directory of C:\oracle\product\10.1.0\oradata\uat

05/26/2008  02:10 PM       104,865,792 NEWDATAFILE01.DBF
05/26/2008  02:10 PM       262,152,192 SYSAUX01.DBF
05/26/2008  02:10 PM       461,381,632 SYSTEM01.DBF
05/25/2008  09:42 AM        20,979,712 TEMP01.DBF
05/26/2008  02:10 PM        26,222,592 UNDOTBS01.DBF
05/26/2008  02:10 PM         5,251,072 USERS01.DBF
               6 File(s)    880,852,992 bytes
               0 Dir(s)  43,392,053,248 bytes free

C:\oracle\product\10.1.0\oradata\uat>

RMAN> startup mount

database is already started
database mounted

RMAN>

SQL> select * from v$recover_file
  2  /

no rows selected

   
now you have new old controlfile and new datafiles do 


RMAN>recover database

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/26/2008 14:43:26
ORA-01119: error in creating database file 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT
\NEWDATAFILE01.DBF'
ORA-27038: created file already exists
OSD-04010:  option specified, file already exists

SQL> recover database
ORA-01153: an incompatible media recovery is active

   
an incompatible media recovery is active cause newdatafile01.dbf does not known by old controlfile.


SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145488364 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes
Database mounted.
SQL> recover database
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done


SQL> recover database using backup controlfile
ORA-00283: recovery session canceled due to errors
ORA-01111: name for data file 5 is unknown - rename to correct file
ORA-01110: data file 5: 'C:\WINDOWS\SYSTEM32\UNNAMED00005'
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01111: name for data file 5 is unknown - rename to correct file
ORA-01110: data file 5: 'C:\WINDOWS\SYSTEM32\UNNAMED00005'


SQL> conn sys/sys@uat as sysdba
Connected.
SQL> recover database
ORA-01153: an incompatible media recovery is active

SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145488364 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes
Database mounted.

SQL> recover database
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

SQL> recover database using backup controlfile
ORA-00283: recovery session canceled due to errors
ORA-01111: name for data file 5 is unknown - rename to correct file
ORA-01110: data file 5: 'C:\WINDOWS\SYSTEM32\UNNAMED00005'
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01111: name for data file 5 is unknown - rename to correct file
ORA-01110: data file 5: 'C:\WINDOWS\SYSTEM32\UNNAMED00005'

   
You can also check the name of datafile unnamed00005 from alert log file


Mon May 26 14:43:23 2008
alter database recover logfile 'C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\UAT\ARCHIVELOG\2008_05_26\O1_MF_1_71_43NXG29C_.ARC'
Media Recovery Log C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\UAT\ARCHIVELOG\2008_05_26\O1_MF_1_71_43NXG29C_.ARC
File #5 added to control file as 'UNNAMED00005'. Originally created as:
'C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\NEWDATAFILE01.DBF'
Errors with log C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\UAT\ARCHIVELOG\2008_05_26\O1_MF_1_71_43NXG29C_.ARC

SQL> select ts#,file#,substr(name,1,55) name,status,enabled
  2    from v$datafile
  3  /

TS#  FILE#  NAME                                                   STATUS  ENABLED
---- ------ -------------------------------------------------     ------- ---------  
0         1 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\SYSTEM01.DBF      SYSTEM  READ WRITE
1         2 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\UNDOTBS01.DBF     ONLINE  READ WRITE
2         3 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\SYSAUX01.DBF      ONLINE  READ WRITE
4         4 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\USERS01.DBF       ONLINE  READ WRITE
6         5 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\NEWDATAFILE01.DBF RECOVER READ WRITE

SQL> alter database rename file 'C:\WINDOWS\SYSTEM32\UNNAMED00005' 
     to 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\NEWDATAFILE01.DBF'
  2  /

Database altered.

SQL> select ts#,file#,substr(name,1,55) name,status,enabled
  2    from v$datafile
  3  /

TS# FILE#  NAME                                                   STATUS ENABLED
--- ----- ------------------------------------------------------ ------ ---------  
0   1     C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\SYSTEM01.DBF       SYSTEM  READ WRITE
1   2     C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\UNDOTBS01.DBF      ONLINE  READ WRITE
2   3     C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\SYSAUX01.DBF       ONLINE  READ WRITE
4   4     C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\USERS01.DBF        ONLINE  READ WRITE
6   5 C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\NEWDATAFILE01.DBF     RECOVER  READ WRITE


   
Try to recover the datafile at RMAN as well sqlplus


C:\>rman

Recovery Manager: Release 10.1.0.2.0 - Production

Copyright (c) 1995, 2004, Oracle.  All rights reserved.

RMAN> connect target sys/sys@uat

connected to target database: UAT (DBID=2679299118)

RMAN> recover datafile 5
2> ;

Starting recover at 26-MAY-08
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/26/2008 15:12:30
RMAN-06067: RECOVER DATABASE required with a backup or created controlfile

SQL> recover datafile 5
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

   
As you can see the recovery should be done by using recover database using backup controlfile.


SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145488364 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes
Database mounted.


SQL> select group#,status
  2    from v$log
  3  /

    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 INACTIVE
         3 CURRENT

SQL> recover database using backup controlfile
ORA-00279: change 1011489 generated at 05/26/2008 12:51:02 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\UAT\ARCHIVELOG\2008_05_26\O1_MF_1_71_%U_.ARC
ORA-00280: change 1011489 for thread 1 is in sequence #71


Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 1014306 generated at 05/26/2008 13:33:06 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\UAT\ARCHIVELOG\2008_05_26\O1_MF_1_72_%U_.ARC
ORA-00280: change 1014306 for thread 1 is in sequence #72
ORA-00278: log file 'C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\UAT\ARCHIVELOG\2008_05_26\O1_MF_1_71_43NXG29C_.ARC' no longer needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 1016418 generated at 05/26/2008 14:00:33 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\UAT\ARCHIVELOG\2008_05_26\O1_MF_1_73_%U_.ARC
ORA-00280: change 1016418 for thread 1 is in sequence #73
ORA-00278: log file 'C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\UAT\ARCHIVELOG\2008_05_26\O1_MF_1_72_43NZ1NQ7_.ARC' no longer needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 1017543 generated at 05/26/2008 14:08:47 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\UAT\ARCHIVELOG\2008_05_26\O1_MF_1_74_%U_.ARC
ORA-00280: change 1017543 for thread 1 is in sequence #74
ORA-00278: log file 'C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\UAT\ARCHIVELOG\2008_05_26\O1_MF_1_73_43NZJZG7_.ARC' no longer needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00279: change 1017771 generated at 05/26/2008 14:09:55 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\UAT\ARCHIVELOG\2008_05_26\O1_MF_1_75_%U_.ARC
ORA-00280: change 1017771 for thread 1 is in sequence #75
ORA-00278: log file 'C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\UAT\ARCHIVELOG\2008_05_26\O1_MF_1_74_43NZM4B6_.ARC' no longer needed for this recovery


Specify log: {=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log 'C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\UAT\ARCHIVELOG\2008_05_26\O1_MF_1_75_%U_.ARC'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.

   
Do recovery again at this time dont specify to apply the log at  O1_MF_1_75_%U_.ARC ,only apply alls redo log file instead applying O1_MF_1_75_%U_.ARC.


SQL> recover database using backup controlfile
ORA-00279: change 1017771 generated at 05/26/2008 14:09:55 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\UAT\ARCHIVELOG\2008_05_26\O1_MF_1_75_%U_.ARC
ORA-00280: change 1017771 for thread 1 is in sequence #75


Specify log: {=suggested | filename | AUTO | CANCEL}
   
C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\REDO03.log<---------------------------

ORA-00310: archived log contains sequence 73; sequence 75 required
ORA-00334: archived log: 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\REDO03.LOG'

SQL> select ts#,file#,substr(name,1,55) name,status,enabled
  2    from v$datafile
  3  /

TS#  FILE# NAME                                                   STATUS  ENABLED
---- ----- --------------------------------------------------     ------- --------
0    1     C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\SYSTEM01.DBF      SYSTEM  READ WRITE
1    2     C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\UNDOTBS01.DBF     ONLINE  READ WRITE
2    3     C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\SYSAUX01.DBF      ONLINE  READ WRITE
4    4     C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\USERS01.DBF       ONLINE  READ WRITE
6    5     C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\NEWDATAFILE01.DBF RECOVER READ WRITE

   
now use redo01.log file

SQL> recover database using backup controlfile
ORA-00279: change 1017771 generated at 05/26/2008 14:09:55 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\UAT\ARCHIVELOG\2008_05_26\O1_MF_1_75_%U_.ARC
ORA-00280: change 1017771 for thread 1 is in sequence #75


Specify log: {=suggested | filename | AUTO | CANCEL}
   
C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\REDO01.LOG<---------------------------

ORA-00310: archived log contains sequence 74; sequence 75 required
ORA-00334: archived log: 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\REDO01.LOG'

SQL> select ts#,file#,substr(name,1,55) name,status,enabled
  2    from v$datafile
  3  /

TS# FILE#  NAME                                                    STATUS  ENABLED
--- ------ ------------------------------------------------------- ------- --------
0   1      C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\SYSTEM01.DBF       SYSTEM  READ WRITE
1   2      C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\UNDOTBS01.DBF      ONLINE  READ WRITE
2   3      C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\SYSAUX01.DBF       ONLINE  READ WRITE
4   4      C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\USERS01.DBF        ONLINE  READ WRITE
6   5      C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\NEWDATAFILE01.DBF  RECOVER READ WRITE

   
now use redo02.log file

SQL> recover database using backup controlfile
ORA-00279: change 1017771 generated at 05/26/2008 14:09:55 needed for thread 1
ORA-00289: suggestion : C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\UAT\ARCHIVELOG\2008_05_26\O1_MF_1_75_%U_.ARC
ORA-00280: change 1017771 for thread 1 is in sequence #75


Specify log: {=suggested | filename | AUTO | CANCEL}
  
C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\REDO02.LOG<------------------

Log applied.
Media recovery complete.

SQL> select ts#,file#,substr(name,1,55) name,status,enabled
  2    from v$datafile
  3  /

TS# FILE# NAME                                                   STATUS  ENABLED
--- ----- ----------------------------------------------------   ------- ----------
0   1     C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\SYSTEM01.DBF      SYSTEM  READ WRITE
1   2     C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\UNDOTBS01.DBF     ONLINE  READ WRITE
2   3     C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\SYSAUX01.DBF      ONLINE  READ WRITE
4   4     C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\USERS01.DBF       ONLINE  READ WRITE
6   5     C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\NEWDATAFILE01.DBF ONLINE  READ WRITE

SQL> alter database open resetlogs
  2  /

Database altered.

SQL> select ts#,file#,substr(name,1,55) name,status,enabled
  2    from v$datafile
  3  /

TS#  FILE# NAME                                                    STATUS  ENABLED
---- ----- ------------------------------------------------------- ------- ---------
0    1     C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\SYSTEM01.DBF       SYSTEM  READ WRITE
1    2     C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\UNDOTBS01.DBF      ONLINE  READ WRITE
2    3     C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\SYSAUX01.DBF       ONLINE  READ WRITE
4    4     C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\USERS01.DBF        ONLINE  READ WRITE
6    5     C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\NEWDATAFILE01.DBF  ONLINE  READ WRITE

SQL> alter database open resetlogs
  2  /

Database altered.

SQL> select ts#,file#,substr(name,1,55) name,status,enabled
  2    from v$datafile
  3  /

TS# FILE# NAME                                                    STATUS  ENABLED
--- ----- --------------------------------------------------      ------- ----------
0   1     C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\SYSTEM01.DBF       SYSTEM  READ WRITE
1   2     C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\UNDOTBS01.DBF      ONLINE  READ WRITE
2   3     C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\SYSAUX01.DBF       ONLINE  READ WRITE
4   4     C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\USERS01.DBF        ONLINE  READ WRITE
6   5     C:\ORACLE\PRODUCT\10.1.0\ORADATA\UAT\NEWDATAFILE01.DBF  ONLINE  READ WRITE


SQL>

how it can improve the performance of db if i make open_cursor to 500 ?

2008-05-09T03:19:00.000-07:00

> how it can improve the performance of db if i make open_cursor to 500 ?

It will not  increase the performance by increasing open_cursor value rather it shows how yours application  can open the cursor within a session,there is no relation of open_cursor parameter to performance directly.Nevertheless If you enable the session 
cached_cursor then it will let you show the cached cursor which will remain throught yours session and will imporve 
performance.

SQL>  SHOW PARAMETER session_cached_cursors

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- -----------------------------
session_cached_cursors               integer                          0

SQL> SELECT sql_text
  2   FROM v$open_cursor
  3  WHERE sql_text LIKE '%KHURRAM%'
  4  /

no rows selected

SQL> DECLARE
  2    v  NUMBER:=7566;
  3    CURSOR c1 IS SELECT * FROM emp KHURRAM;
  4    c2     c1%ROWTYPE;
  5  BEGIN
  6   OPEN c1;
  7    LOOP
  8     FETCH c1 INTO c2;
  9     EXIT WHEN c1%NOTFOUND;
 10    END LOOP;
 11   CLOSE c1;
 12  END;
 13  /

PL/SQL procedure successfully completed.

SQL> /

PL/SQL procedure successfully completed.

SQL> /

PL/SQL procedure successfully completed.

SQL> SELECT sql_text
  2   FROM v$open_cursor
  3  WHERE sql_text LIKE '%KHURRAM%'
  4  /

no rows selected


SQL> ALTER SESSION SET session_cached_cursors=100
  2  /

Session altered.

SQL> SHOW PARAMETER session_cached_cursors

NAME                                 TYPE                             VALUE
------------------------------------ -------------------------------- -----------------------------
session_cached_cursors               integer                          100
SQL> SELECT sql_text
  2   FROM v$open_cursor
  3  WHERE sql_text LIKE '%KHURRAM%'
  4  /

no rows selected

I am running the 3 times cause oracle checks library cache to  determine whether more than 3 parse requests have been 
issued on a given statement.if so,then oracle assumes that the session cursor associated with the statement should be 
cached and moves the cursor into session cursor cache.

SQL> DECLARE
  2    v  NUMBER:=7566;
  3    CURSOR c1 IS SELECT * FROM emp KHURRAM;
  4    c2     c1%ROWTYPE;
  5  BEGIN
  6   OPEN c1;
  7    LOOP
  8     FETCH c1 INTO c2;
  9     EXIT WHEN c1%NOTFOUND;
 10    END LOOP;
 11   CLOSE c1;
 12  END;
 13  /

PL/SQL procedure successfully completed.

SQL> /

PL/SQL procedure successfully completed.

SQL> /

PL/SQL procedure successfully completed.

SQL> SELECT sql_text
  2   FROM v$open_cursor
  3  WHERE sql_text LIKE '%KHURRAM%'
  4  /

SQL_TEXT
------------------------------------------------------------
SELECT * FROM EMP KHURRAM

SQL>

can i use the retention policy without using the Flash Recovery area?

2008-04-15T02:51:00.000-07:00

can i use the retention policy without using the Flash Recovery area? yes Lets see here..

RMAN>list backup summary

C:\>dir *.bus
 Volume in drive C is khurram
 Volume Serial Number is F49D-FF2B

 Directory of C:\

File Not Found

C:\>dir *.arc
 Volume in drive C is khurram
 Volume Serial Number is F49D-FF2B

 Directory of C:\

File Not Found


RMAN> crosscheck backup
2> ;

using channel ORA_DISK_1

RMAN> list backup summary
2> ;


RMAN> show retention policy
2> ;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

RMAN> run {
2>   allocate channel ch0 type disk;
3>   backup as compressed backupset database format
4>   'c:\rman_%s_%d_%T.bus'
5>   plus archivelog format 'c:\arch_%s_%d_%T.arc';
6>   release channel ch0;
7>   allocate channel ch1 device type disk format 'c:\arch_%s_%d_%T.arc';
8>   release channel ch1;
9>   delete force noprompt obsolete;
10>   allocate channel ch2 device type disk format 'c:\rman_%s_%d_%T.bus';
11>   release channel ch2;
12>   delete force noprompt obsolete;
13> }

released channel: ORA_DISK_1
allocated channel: ch0
channel ch0: sid=146 devtype=DISK


Starting backup at 15-APR-08
current log archived
channel ch0: starting compressed archive log backupset
channel ch0: specifying archive log(s) in backup set
input archive log thread=1 sequence=24 recid=79 stamp=652108461
input archive log thread=1 sequence=25 recid=80 stamp=652112650
channel ch0: starting piece 1 at 15-APR-08
channel ch0: finished piece 1 at 15-APR-08
piece handle=C:\ARCH_130_ORCL1_20080415.ARC comment=NONE
channel ch0: backup set complete, elapsed time: 00:00:05
Finished backup at 15-APR-08

Starting backup at 15-APR-08
channel ch0: starting compressed full datafile backupset
channel ch0: specifying datafile(s) in backupset
input datafile fno=00001 name=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\SYSTEM01.DB
F
input datafile fno=00003 name=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\SYSAUX01.DB
F
input datafile fno=00004 name=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\USERS01.DBF

input datafile fno=00002 name=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\UNDOTBS01.D
BF
channel ch0: starting piece 1 at 15-APR-08
channel ch0: finished piece 1 at 15-APR-08
piece handle=C:\RMAN_131_ORCL1_20080415.BUS comment=NONE
channel ch0: backup set complete, elapsed time: 00:00:45
Finished backup at 15-APR-08

Starting backup at 15-APR-08
current log archived
channel ch0: starting compressed archive log backupset
channel ch0: specifying archive log(s) in backup set
input archive log thread=1 sequence=26 recid=81 stamp=652112703
channel ch0: starting piece 1 at 15-APR-08
channel ch0: finished piece 1 at 15-APR-08
piece handle=C:\ARCH_132_ORCL1_20080415.ARC comment=NONE
channel ch0: backup set complete, elapsed time: 00:00:02
Finished backup at 15-APR-08

Starting Control File and SPFILE Autobackup at 15-APR-08
piece handle=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\AUTOBACKUP\2008_
04_15\O1_MF_S_652112718_408X3ZVJ_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 15-APR-08

released channel: ch0

allocated channel: ch1
channel ch1: sid=146 devtype=DISK

released channel: ch1

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=146 devtype=DISK
Deleting the following obsolete backups and copies:
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           128    15-APR-08
  Backup Piece       128    15-APR-08          C:\ARCH_130_ORCL1_20080415.ARC
Archive Log          79     15-APR-08          C:\ORACLE\PRODUCT\10.1.0\FLASH_RE
COVERY_AREA\ORCL1\ARCHIVELOG\2008_04_15\O1_MF_1_24_408RYXDF_.ARC
Archive Log          80     15-APR-08          C:\ORACLE\PRODUCT\10.1.0\FLASH_RE
COVERY_AREA\ORCL1\ARCHIVELOG\2008_04_15\O1_MF_1_25_408X1SCR_.ARC
deleted backup piece
backup piece handle=C:\ARCH_130_ORCL1_20080415.ARC recid=128 stamp=652112652
deleted archive log
archive log filename=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVEL
OG\2008_04_15\O1_MF_1_24_408RYXDF_.ARC recid=79 stamp=652108461
deleted archive log
archive log filename=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\ARCHIVEL
OG\2008_04_15\O1_MF_1_25_408X1SCR_.ARC recid=80 stamp=652112650
Deleted 3 objects


released channel: ORA_DISK_1
allocated channel: ch2
channel ch2: sid=146 devtype=DISK

released channel: ch2

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=146 devtype=DISK
no obsolete backups found

RMAN>

C:\>dir *.bus
 Volume in drive C is khurram
 Volume Serial Number is F49D-FF2B

 Directory of C:\

04/15/2008  02:24 PM        96,813,056 RMAN_131_ORCL1_20080415.BUS
               1 File(s)     96,813,056 bytes
               0 Dir(s)  61,960,724,480 bytes free

C:\>dir *.arc
 Volume in drive C is khurram
 Volume Serial Number is F49D-FF2B

 Directory of C:\

04/15/2008  02:25 PM            56,832 ARCH_132_ORCL1_20080415.ARC
               1 File(s)         56,832 bytes
               0 Dir(s)  61,960,724,480 bytes free

C:\>


RMAN> run {
2>   allocate channel ch0 type disk;
3>   backup as compressed backupset database format
4>   'c:\rman_%s_%d_%T.bus'
5>   plus archivelog format 'c:\arch_%s_%d_%T.arc';
6>   release channel ch0;
7>  }

released channel: ORA_DISK_1
allocated channel: ch0
channel ch0: sid=146 devtype=DISK


Starting backup at 15-APR-08
current log archived
channel ch0: starting compressed archive log backupset
channel ch0: specifying archive log(s) in backup set
input archive log thread=1 sequence=26 recid=81 stamp=652112703
input archive log thread=1 sequence=27 recid=82 stamp=652112873
channel ch0: starting piece 1 at 15-APR-08
channel ch0: finished piece 1 at 15-APR-08
piece handle=C:\ARCH_134_ORCL1_20080415.ARC comment=NONE
channel ch0: backup set complete, elapsed time: 00:00:02
Finished backup at 15-APR-08

Starting backup at 15-APR-08
channel ch0: starting compressed full datafile backupset
channel ch0: specifying datafile(s) in backupset
input datafile fno=00001 name=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\SYSTEM01.DB
F
input datafile fno=00003 name=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\SYSAUX01.DB
F
input datafile fno=00004 name=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\USERS01.DBF

input datafile fno=00002 name=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\UNDOTBS01.D
BF
channel ch0: starting piece 1 at 15-APR-08
channel ch0: finished piece 1 at 15-APR-08
piece handle=C:\RMAN_135_ORCL1_20080415.BUS comment=NONE
channel ch0: backup set complete, elapsed time: 00:00:45
Finished backup at 15-APR-08

Starting backup at 15-APR-08
current log archived
channel ch0: starting compressed archive log backupset
channel ch0: specifying archive log(s) in backup set
input archive log thread=1 sequence=28 recid=83 stamp=652112925
channel ch0: starting piece 1 at 15-APR-08
channel ch0: finished piece 1 at 15-APR-08
piece handle=C:\ARCH_136_ORCL1_20080415.ARC comment=NONE
channel ch0: backup set complete, elapsed time: 00:00:02
Finished backup at 15-APR-08

Starting Control File and SPFILE Autobackup at 15-APR-08
piece handle=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\AUTOBACKUP\2008_
04_15\O1_MF_S_652112928_408XBKCZ_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 15-APR-08

released channel: ch0

RMAN> report obsolete
2> ;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of obsolete backups and copies
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           129    15-APR-08
  Backup Piece       129    15-APR-08          C:\RMAN_131_ORCL1_20080415.BUS
Backup Set           130    15-APR-08
  Backup Piece       130    15-APR-08          C:\ARCH_132_ORCL1_20080415.ARC
Backup Set           131    15-APR-08
  Backup Piece       131    15-APR-08          C:\ORACLE\PRODUCT\10.1.0\FLASH_RE
COVERY_AREA\ORCL1\AUTOBACKUP\2008_04_15\O1_MF_S_652112718_408X3ZVJ_.BKP
Backup Set           132    15-APR-08
  Backup Piece       132    15-APR-08          C:\ARCH_134_ORCL1_20080415.ARC
Archive Log          81     15-APR-08          C:\ORACLE\PRODUCT\10.1.0\FLASH_RE
COVERY_AREA\ORCL1\ARCHIVELOG\2008_04_15\O1_MF_1_26_408X3HRP_.ARC
Archive Log          82     15-APR-08          C:\ORACLE\PRODUCT\10.1.0\FLASH_RE
COVERY_AREA\ORCL1\ARCHIVELOG\2008_04_15\O1_MF_1_27_408X8RXN_.ARC

RMAN>


i have archivelog which is at FRA ,it also apply there and as well yours own defined backupsets path.

disaster recovery

2008-04-09T07:24:00.001-07:00

SQL*Plus: Release 10.1.0.2.0 - Production on Wed Apr 9 16:23:47 2008

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select name,dbid from v$database
  2  /

NAME            DBID
--------- ----------
ORCL      1179381888

SQL> show parameter db_recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      C:\oracle\product\10.1.0\flash_recovery_area
db_recovery_file_dest_size           big integer 2G
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145488364 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes
Database mounted.
SQL> alter database archivelog
  2  /

Database altered.

SQL> alter database open
  2  /

Database altered.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     3
Next log sequence to archive   5
Current log sequence           5
SQL> conn scott/tiger
Connected.
SQL> create table t as select * from all_objects
  2  /

Table created.

C:\>rman

Recovery Manager: Release 10.1.0.2.0 - Production

Copyright (c) 1995, 2004, Oracle.  All rights reserved.

RMAN> connect target sys/sys

connected to target database: ORCL (DBID=1179381888)

RMAN> configure controlfile autobackup on
2> ;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

RMAN>

RMAN> run
2> {
3> allocate channel t1 type disk;
4> backup database;
5> release channel t1;
6> allocate channel t2 type disk;
7> backup archivelog all;
8> release channel t2;
9> }

using target database controlfile instead of recovery catalog
allocated channel: t1
channel t1: sid=136 devtype=DISK
.
.
.

RMAN>

-take alls FRA backupsets to somewhere else safe i.e tape cartridge.
-Delete alls database files including spfile,pfile and password file
-Delete also cdump,udump,bdump,create.

Now you have been in disaster scenario.Now what you have to do is install the same version of oralce with same
directory structure and instance.

You may create the old instance by OS laterz after installing the oracle.


C:\>oradim -new -sid orcl
Instance created.

after instance creation ,create password file by using orapwd utility

C:\>orapwd file=C:\oracle\product\10.1.0\Db_2\database\PWDorcl.ora password=sys

put alls backup files from FRA which is in tape cartridge to its respected location.

Connect to RMAN

C:\>rman

Recovery Manager: Release 10.1.0.2.0 - Production

Copyright (c) 1995, 2004, Oracle.  All rights reserved.

RMAN> connect target sys/sys

connected to target database (not started)

RMAN>

RMAN> startup force nomount

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file 'C:\ORACLE\PRODUCT\10.1.0\DB_2\DATABASE
\INITORCL.ORA'

trying to start the Oracle instance without parameter files ...
Oracle instance started

Total System Global Area     113246208 bytes

Fixed Size                      787708 bytes
Variable Size                 61602564 bytes
Database Buffers              50331648 bytes
Redo Buffers                    524288 bytes

RMAN>
RMAN> restore spfile from 'C:\oracle\product\10.1.0\flash_recovery_area\ORCL\AUT
OBACKUP\2008_04_09\O1_MF_S_651605789_3ZSG2GHX_.BKP'
2> ;

Starting restore at 09-APR-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=29 devtype=DISK

channel ORA_DISK_1: autobackup found: C:\oracle\product\10.1.0\flash_recovery_ar
ea\ORCL\AUTOBACKUP\2008_04_09\O1_MF_S_651605789_3ZSG2GHX_.BKP
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 09-APR-08

RMAN> shutdown immediate

Oracle instance shut down

RMAN> startup

connected to target database (not started)
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 04/09/2008 18:13:56
RMAN-04014: startup failed: ORA-02778: Name given for the log directory is invalid

RMAN> ^C


create bdump,udump,cdump and create folder at C:\oracle\product\10.1.0\admin\orcl

C:\>rman

Recovery Manager: Release 10.1.0.2.0 - Production

Copyright (c) 1995, 2004, Oracle.  All rights reserved.

RMAN> connect target sys/sys

connected to target database (not started)

RMAN> startup

Oracle instance started
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 04/09/2008 18:17:41
ORA-00205: error in identifying controlfile, check alert log for more info

RMAN> restore controlfile from autobackup
2> ;

Starting restore at 09-APR-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=160 devtype=DISK

recovery area destination: C:\oracle\product\10.1.0\flash_recovery_area
database name (or lock name space) used for search: ORCL
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AR
EA\ORCL\AUTOBACKUP\2008_04_09\O1_MF_S_651605800_3ZSG2SLS_.BKP
channel ORA_DISK_1: controlfile restore from autobackup complete
output filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL01.CTL
output filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL02.CTL
output filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\CONTROL03.CTL
Finished restore at 09-APR-08

RMAN> startup mount

database is already started
database mounted

RMAN> restore database
2> ;

ORACLE error from target database:
ORA-19922: there is no parent row with id 0 and level 0
ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 5038
ORA-06512: at line 1

RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT row
s
Starting restore at 09-APR-08
Starting implicit crosscheck backup at 09-APR-08
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=160 devtype=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 09-APR-08

RMAN> recover database
2> ;

Starting recover at 09-APR-08
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 8 is already on disk as file C:\ORACLE\PRODUCT\10.
1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2008_04_09\O1_MF_1_8_3ZSG2M7M_.ARC
archive log filename=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELO
G\2008_04_09\O1_MF_1_8_3ZSG2M7M_.ARC thread=1 sequence=8
unable to find archive log
archive log thread=1 sequence=9
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 04/09/2008 18:31:10
RMAN-06054: media recovery requesting unknown log: thread 1 seq 9 lowscn 332376

RMAN> alter database open resetlogs
2> ;

database opened

RMAN> exit


Recovery Manager complete.

C:\>

how to recover database with previous incarnation

2008-03-18T08:27:00.000-07:00

Assume this demo is in 9i not in 10g


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145488364 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes
Database mounted.
SQL> alter database archivelog
  2  /

Database altered.

SQL> alter database open
  2  /

Database altered.


SQL> create table t (a number)
  2  /

Table created.


RMAN> backup database
2> ;

Starting backup at 12-DEC-07
.
.

Finished backup at 12-DEC-07

RMAN>



SQL> select to_char(sysdate,'DD-MM-YYYY:HH24:MI:SS')
  2    from dual
  3  /

TO_CHAR(SYSDATE,'DD
-------------------
12-12-2007 15:09:42

SQL> drop table t
  2  /

Table dropped.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145488364 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes
Database mounted.
SQL> 

RMAN> connect target sys/sys

connected to target database: ORCL1 (DBID=1025591162)

RMAN> run
2> {
3>  set until time "to_date('12-12-2007 15:09:42','DD-MM-YYYY HH24:MI:SS')";
4>  restore database;
5>  recover database;
6> }

executing command: SET until clause
using target database controlfile instead of recovery catalog

.
.

Finished restore at 12-DEC-07

Starting recover at 12-DEC-07
using channel ORA_DISK_1

starting media recovery
media recovery complete

Finished recover at 12-DEC-07



RMAN> alter database open resetlogs
2> ;

database opened

RMAN> list incarnation
2> ;

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       ORCL1    1025591162       PARENT  1          09-MAR-04
2       2       ORCL1    1025591162       PARENT  318842     12-DEC-07<---------set this
3       3       ORCL1    1025591162       CURRENT 331575     12-DEC-07<-----its current

SQL> conn sys/sys as sysdba
Connected.

SQL> desc t
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  NUMBER


SQL> drop table t
  2  /

Table dropped.


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145488364 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes
Database mounted.
SQL> 


RMAN> connect target sys/sys

connected to target database: ORCL1 (DBID=1025591162)

RMAN> run
2> {
3>  set until time "to_date('12-12-2007 15:09:42','DD-MM-YYYY HH24:MI:SS')";
4>  restore database;
5>  recover database;
6> }

executing command: SET until clause
using target database controlfile instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of set command at 12/12/2007 15:31:22
RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time


RMAN> shutdown immediate

using target database controlfile instead of recovery catalog
database dismounted
Oracle instance shut down

RMAN> startup nomount

connected to target database (not started)
Oracle instance started

Total System Global Area     171966464 bytes

Fixed Size                      787988 bytes
Variable Size                145488364 bytes
Database Buffers              25165824 bytes
Redo Buffers                    524288 bytes


RMAN> restore controlfile from 'C:\oracle\..\ORCL1\BACKUPSET\2007_12_12\O1_MF_NCSNF_TAG20071212T150529_3OZDPGX9_.BKP'
2> ;

Starting restore at 12-DEC-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=160 devtype=DISK

channel ORA_DISK_1: restoring controlfile
channel ORA_DISK_1: restore complete
output filename=C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\CONTROL01.CTL
output filename=C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\CONTROL02.CTL
output filename=C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\CONTROL03.CTL
Finished restore at 12-DEC-07

RMAN> startup mount

database is already started
database mounted

RMAN> reset database to incarnation 2
2> ;

Starting implicit crosscheck backup at 12-DEC-07
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=160 devtype=DISK
Crosschecked 1 objects
Finished implicit crosscheck backup at 12-DEC-07

Starting implicit crosscheck copy at 12-DEC-07
using channel ORA_DISK_1
Finished implicit crosscheck copy at 12-DEC-07

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: C:\ORACLE\FLASH_RECOVERY_AREA\ORCL1\ORCL1\ARCHIVELOG\2007_12_12\O1_MF
_1_5_3OZFHKNJ_.ARC
File Name: C:\ORACLE\FLASH_RECOVERY_AREA\ORCL1\ORCL1\BACKUPSET\2007_12_12\O1_MF_
NCSNF_TAG20071212T150529_3OZDPGX9_.BKP

database reset to incarnation 2



RMAN> run
2> {
3>  set until time "to_date('12-12-2007 15:09:42','DD-MM-YYYY HH24:MI:SS')";
4>  restore database;
5>  recover database;
6> }

executing command: SET until clause

ORACLE error from target database:
ORA-19922: there is no parent row with id 0 and level 0
ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 5038
ORA-06512: at line 1

RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT row
s
Starting restore at 12-DEC-07
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\SYSTEM01.DBF

restoring datafile 00002 to C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\UNDOTBS01.DB
F
restoring datafile 00003 to C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\SYSAUX01.DBF

restoring datafile 00004 to C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\USERS01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\ORCL1\ORCL1\BACKUPSET\2007_12_12\O1_M
F_NNNDF_TAG20071212T150529_3OZDMCGN_.BKP tag=TAG20071212T150529
channel ORA_DISK_1: restore complete
Finished restore at 12-DEC-07

Starting recover at 12-DEC-07
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 5 is already on disk as file C:\ORACLE\FLASH_RECOV
ERY_AREA\ORCL1\ORCL1\ARCHIVELOG\2007_12_12\O1_MF_1_5_3OZFHKNJ_.ARC
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\ORCL1\ORCL1\ARCHIVELOG\2007_1
2_12\O1_MF_1_5_3OZFHKNJ_.ARC thread=1 sequence=5
media recovery complete
Finished recover at 12-DEC-07


RMAN> alter database open resetlogs
2> ;

database opened

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> desc t
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  NUMBER

SQL> select name from v$database
  2  /

NAME
---------
ORCL1

How to recover user errors

2008-02-12T04:00:00.001-08:00

There is traditional way to recover the user errors i.e drop table by doing incomplete recovery till the time before dropped the table,in oracle 9i you can recover the user errors by flashback technologies flashback drop by using recycle bin,here i am not considering the space issues of recyclebin.

SQL*Plus: Release 10.1.0.2.0 - Production on Wed Dec 12 15:02:09 2007

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145488364 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes
Database mounted.
SQL> alter database archivelog
  2  /

Database altered.

SQL> alter database open
  2  /

Database altered.


SQL> create table t (a number)
  2  /

Table created.

Recovery Manager: Release 10.1.0.2.0 - Production

Copyright (c) 1995, 2004, Oracle.  All rights reserved.

RMAN> connect target sys/sys

connected to target database: ORCL1 (DBID=1025591162)

RMAN> backup database
2> ;

Starting backup at 12-DEC-07
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=139 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\SYSTEM01.D
BF
input datafile fno=00003 name=C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\SYSAUX01.D
BF
input datafile fno=00002 name=C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\UNDOTBS01.
DBF
input datafile fno=00004 name=C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\USERS01.DB
F
channel ORA_DISK_1: starting piece 1 at 12-DEC-07
channel ORA_DISK_1: finished piece 1 at 12-DEC-07
piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\ORCL1\ORCL1\BACKUPSET\2007_12_12\O1_M
F_NNNDF_TAG20071212T150529_3OZDMCGN_.BKP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:36
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current controlfile in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 12-DEC-07
channel ORA_DISK_1: finished piece 1 at 12-DEC-07
piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\ORCL1\ORCL1\BACKUPSET\2007_12_12\O1_M
F_NCSNF_TAG20071212T150529_3OZDPGX9_.BKP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:20
Finished backup at 12-DEC-07

RMAN>



SQL> select to_char(sysdate,'DD-MM-YYYY:HH24:MI:SS')
  2    from dual
  3  /

TO_CHAR(SYSDATE,'DD
-------------------
12-12-2007 15:09:42

SQL> drop table t
  2  /

Table dropped.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145488364 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes
Database mounted.
SQL> 

Recovery Manager: Release 10.1.0.2.0 - Production

Copyright (c) 1995, 2004, Oracle.  All rights reserved.

RMAN> connect target sys/sys

connected to target database: ORCL1 (DBID=1025591162)

RMAN> run
2> {
3>  set until time "to_date('12-12-2007 15:09:42','DD-MM-YYYY HH24:MI:SS')";
4>  restore database;
5>  recover database;
6> }

executing command: SET until clause
using target database controlfile instead of recovery catalog

Starting restore at 12-DEC-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 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 C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\SYSTEM01.DBF

restoring datafile 00002 to C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\UNDOTBS01.DB
F
restoring datafile 00003 to C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\SYSAUX01.DBF

restoring datafile 00004 to C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\USERS01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\ORCL1\ORCL1\BACKUPSET\2007_12_12\O1_M
F_NNNDF_TAG20071212T150529_3OZDMCGN_.BKP tag=TAG20071212T150529
channel ORA_DISK_1: restore complete
Finished restore at 12-DEC-07

Starting recover at 12-DEC-07
using channel ORA_DISK_1

starting media recovery
media recovery complete

Finished recover at 12-DEC-07



RMAN> alter database open resetlogs
2> ;

database opened

RMAN> list incarnation
2> ;


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       ORCL1    1025591162       PARENT  1          09-MAR-04
2       2       ORCL1    1025591162       PARENT  318842     12-DEC-07<---------set this
3       3       ORCL1    1025591162       CURRENT 331575     12-DEC-07&lt;-----its current





SQL> conn sys/sys as sysdba
Connected.

SQL> desc t
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  NUMBER


SQL> drop table t
  2  /

Table dropped.


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145488364 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes
Database mounted.
SQL> 

Recovery Manager: Release 10.1.0.2.0 - Production

Copyright (c) 1995, 2004, Oracle.  All rights reserved.

RMAN> connect target sys/sys

connected to target database: ORCL1 (DBID=1025591162)

RMAN> run
2> {
3>  set until time "to_date('12-12-2007 15:09:42','DD-MM-YYYY HH24:MI:SS')";
4>  restore database;
5>  recover database;
6> }

executing command: SET until clause
using target database controlfile instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of set command at 12/12/2007 15:31:22
RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time


RMAN> shutdown immediate

using target database controlfile instead of recovery catalog
database dismounted
Oracle instance shut down

RMAN> startup nomount

connected to target database (not started)
Oracle instance started

Total System Global Area     171966464 bytes

Fixed Size                      787988 bytes
Variable Size                145488364 bytes
Database Buffers              25165824 bytes
Redo Buffers                    524288 bytes


RMAN> restore controlfile from 'C:\oracle\flash_recovery_area\orcl1\ORCL1\BACKUPSET\2007_12_12\O1_MF_NCSNF_TAG20071212T150529_3OZDPGX9_.BKP'
2> ;

Starting restore at 12-DEC-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=160 devtype=DISK

channel ORA_DISK_1: restoring controlfile
channel ORA_DISK_1: restore complete
output filename=C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\CONTROL01.CTL
output filename=C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\CONTROL02.CTL
output filename=C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\CONTROL03.CTL
Finished restore at 12-DEC-07

RMAN> startup mount

database is already started
database mounted

RMAN> reset database to incarnation 2
2> ;

Starting implicit crosscheck backup at 12-DEC-07
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=160 devtype=DISK
Crosschecked 1 objects
Finished implicit crosscheck backup at 12-DEC-07

Starting implicit crosscheck copy at 12-DEC-07
using channel ORA_DISK_1
Finished implicit crosscheck copy at 12-DEC-07

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: C:\ORACLE\FLASH_RECOVERY_AREA\ORCL1\ORCL1\ARCHIVELOG\2007_12_12\O1_MF
_1_5_3OZFHKNJ_.ARC
File Name: C:\ORACLE\FLASH_RECOVERY_AREA\ORCL1\ORCL1\BACKUPSET\2007_12_12\O1_MF_
NCSNF_TAG20071212T150529_3OZDPGX9_.BKP

database reset to incarnation 2



RMAN> run
2> {
3>  set until time "to_date('12-12-2007 15:09:42','DD-MM-YYYY HH24:MI:SS')";
4>  restore database;
5>  recover database;
6> }

executing command: SET until clause

ORACLE error from target database:
ORA-19922: there is no parent row with id 0 and level 0
ORA-06512: at "SYS.X$DBMS_BACKUP_RESTORE", line 5038
ORA-06512: at line 1

RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT row
s
Starting restore at 12-DEC-07
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\SYSTEM01.DBF

restoring datafile 00002 to C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\UNDOTBS01.DB
F
restoring datafile 00003 to C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\SYSAUX01.DBF

restoring datafile 00004 to C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\USERS01.DBF
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\ORACLE\FLASH_RECOVERY_AREA\ORCL1\ORCL1\BACKUPSET\2007_12_12\O1_M
F_NNNDF_TAG20071212T150529_3OZDMCGN_.BKP tag=TAG20071212T150529
channel ORA_DISK_1: restore complete
Finished restore at 12-DEC-07

Starting recover at 12-DEC-07
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 5 is already on disk as file C:\ORACLE\FLASH_RECOV
ERY_AREA\ORCL1\ORCL1\ARCHIVELOG\2007_12_12\O1_MF_1_5_3OZFHKNJ_.ARC
archive log filename=C:\ORACLE\FLASH_RECOVERY_AREA\ORCL1\ORCL1\ARCHIVELOG\2007_1
2_12\O1_MF_1_5_3OZFHKNJ_.ARC thread=1 sequence=5
media recovery complete
Finished recover at 12-DEC-07


RMAN> alter database open resetlogs
2> ;

database opened

Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> desc t
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 A                                                  NUMBER

SQL> select name from v$database
  2  /

NAME
---------
ORCL1

You can recover the same table by using flashback technologies its by default
enabled at 10g ,you can recover table from recycle bin unless you dont purge it, Recycle Bin catches receives database objects and its dependent objects i.e PK,
Index etc when you drop table.


SQL> desc dept

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DEPTNO                                    NOT NULL NUMBER(2)
 DNAME                                              VARCHAR2(14)
 LOC                                                VARCHAR2(13)

SQL> show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
EMP              BIN$z6oBZ3FARF+b2GD99UVNkQ==$0 TABLE        2008-02-11:23:19:28
SQL> drop table dept
  2  /

Table dropped.

SQL> show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
DEPT             BIN$aAEWEykwReeRQG4USzgP8w==$0 TABLE        2008-02-12:15:59:12
EMP              BIN$z6oBZ3FARF+b2GD99UVNkQ==$0 TABLE        2008-02-11:23:19:28


SQL> select object_name,original_name
  2    from recyclebin
  3  /

OBJECT_NAME                    ORIGINAL_NAME
------------------------------ --------------------------------
BIN$5c4ILi0WRlq9WhqKxCr/1A==$0 PK_EMP
BIN$z6oBZ3FARF+b2GD99UVNkQ==$0 EMP
BIN$m4hxC9lWQe67YFtifqXGNg==$0 PK_DEPT
BIN$aAEWEykwReeRQG4USzgP8w==$0 DEPT

SQL> flashback table "BIN$aAEWEykwReeRQG4USzgP8w==$0" to before drop
  2  /

Flashback complete.

SQL> desc dept
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DEPTNO                                    NOT NULL NUMBER(2)
 DNAME                                              VARCHAR2(14)
 LOC                                                VARCHAR2(13)

Recovery of a lost datafile without backup

2008-02-07T22:02:00.001-08:00

Its the demo for recovery of a lost datafile without backup,but you should have database in archivelog in order to make complete recovery,otherwise in noarchivelog mode you may lose some data.Every steps is self explainatory so i feel need'nt to add some textual description during demo.

SQL*Plus: Release 10.1.0.2.0 - Production on Fri Feb 8 10:35:24 2008

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select name from v$database
  2  /

NAME
---------
ORCL



SQL> create tablespace my_tablespace datafile 'C:\oracle\product\10.1.0\oradata\orcl\my_datafile.dbf' size 500M
  2  /

Tablespace created.

SQL> set linesize 1000
SQL> select * from v$tablespace
  2  /

       TS# NAME                           INC BIG FLA
---------- ------------------------------ --- --- ---
         0 SYSTEM                         YES NO  YES
         1 UNDOTBS1                       YES NO  YES
         2 SYSAUX                         YES NO  YES
         4 USERS                          YES NO  YES
         3 TEMP                           YES NO  YES
         6 EXAMPLE                        YES NO  YES
         9 MY_TABLESPACE                  YES NO  YES

7 rows selected.



SQL> alter user scott default tablespace my_tablespace
  2  /

User altered.

SQL> conn scott/tiger@orcl
Connected.

SQL> create table my_table as select * from all_objects
  2  /

Table created.

SQL> desc my_table

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(30)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                          NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(19)
 CREATED                                            DATE
 LAST_DDL_TIME                                      DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)
                                                                                        

SQL> conn sys/sys@orcl as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.


Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\>cd C:\oracle\product\10.1.0\oradata\orcl

C:\oracle\product\10.1.0\oradata\orcl>dir *.dbf
 Volume in drive C has no label.
 Volume Serial Number is F49D-FF2B

 Directory of C:\oracle\product\10.1.0\oradata\orcl

02/08/2008  10:41 AM       157,294,592 EXAMPLE01.DBF
02/08/2008  10:41 AM       524,296,192 MY_DATAFILE.DBF
02/08/2008  10:41 AM       283,123,712 SYSAUX01.DBF
02/08/2008  10:41 AM       482,353,152 SYSTEM01.DBF
02/07/2008  11:43 PM       104,865,792 TEMP01.DBF
02/08/2008  10:41 AM        31,465,472 UNDOTBS01.DBF
02/08/2008  10:41 AM        56,369,152 USERS01.DBF
               7 File(s)  1,639,768,064 bytes
               0 Dir(s)  63,114,174,464 bytes free


C:\oracle\product\10.1.0\oradata\orcl>del my_datafile.dbf

C:\oracle\product\10.1.0\oradata\orcl>dir *.dbf
 Volume in drive C has no label.
 Volume Serial Number is F49D-FF2B

 Directory of C:\oracle\product\10.1.0\oradata\orcl

02/08/2008  10:41 AM       157,294,592 EXAMPLE01.DBF
02/08/2008  10:41 AM       283,123,712 SYSAUX01.DBF
02/08/2008  10:41 AM       482,353,152 SYSTEM01.DBF
02/07/2008  11:43 PM       104,865,792 TEMP01.DBF
02/08/2008  10:41 AM        31,465,472 UNDOTBS01.DBF
02/08/2008  10:41 AM        56,369,152 USERS01.DBF
               6 File(s)  1,115,471,872 bytes
               0 Dir(s)  63,638,204,416 bytes free

C:\oracle\product\10.1.0\oradata\orcl>

SQL> startup
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145488364 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\MY_DATAFILE.DBF'


SQL> select * from v$recover_file
  2  /

     FILE# ONLINE  ONLINE_ ERROR              CHANGE#    TIME
---------- ------- ------- ------------------ ---------- ---------
         6 ONLINE  ONLINE  FILE NOT FOUND     0

SQL>alter database create datafile 
  2  'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\MY_DATAFILE.DBF' as
  3  'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\MY_DATAFILE01.DBF';

Database altered.

C:\oracle\product\10.1.0\oradata\orcl>dir my_*.dbf
 Volume in drive C has no label.
 Volume Serial Number is F49D-FF2B

 Directory of C:\oracle\product\10.1.0\oradata\orcl

02/08/2008  10:53 AM       524,296,192 MY_DATAFILE01.DBF
               1 File(s)    524,296,192 bytes
               0 Dir(s)  63,110,365,184 bytes free

C:\oracle\product\10.1.0\oradata\orcl>

SQL> recover tablespace my_tablespace
Media recovery complete.

SQL> alter database open
  2  /

Database altered.


SQL> conn scott/tiger@orcl
Connected.
SQL> desc my_table
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(30)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                          NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(19)
 CREATED                                            DATE
 LAST_DDL_TIME                                      DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)

SQL>

How statistics affect optimizer

2008-01-31T07:46:00.000-08:00

Statistics are EXACT measurements of the data itself,how large a table is and how useful an index
is.Remember out dated statistics cause optimizer to not functioning realistically.Out-of-date
statistics would have the same effect on all types of databases.

statistics contain number of rows in table,blocks used by table,empty blocks (i.e HWM),average
row length,indexes leaf blocks its blevel,number of buckets (i.e histogram).

i made a little observation regarding histograms stats that how statisitics affect optimizer.

SQL> create table t as select rownum rn,'A' status from all_objects
  2  /

Table created.

SQL> select count(*)
  2    from t
  3   group by status
  4  /

  COUNT(*)
----------
     38959

SQL> select status,count(*)
  2    from t
  3   group by status
  4  /

S   COUNT(*)
- ----------
A      38959

SQL> update t set status='B'
  2   where rn>=19479
  3  /

19481 rows updated.


SQL> select status,count(*)
  2    from t
  3   group by status
  4  /

S   COUNT(*)
- ----------
A      19478
B      19481

SQL> update t set status='C' where rn>=29218
  2  /

9742 rows updated.

SQL> select status,count(*)
  2    from t
  3   group by status
  4  /

S   COUNT(*)
- ----------
A      19478
B       9739
C       9742

SQL> commit
  2  /

Commit complete.


SQL> create index t_status_ndx on t (status)
  2  /

Index created.



SQL> select status,count(*)
  2    from t
  3   group by status
  4  /

S   COUNT(*)
- ----------
A      19478
B       9739
C       9742

SQL> delete plan_table
  2  /

2 rows deleted.

SQL> commit
  2  /

Commit complete.

SQL> exec dbms_stats.delete_table_stats('SCOTT','T')

PL/SQL procedure successfully completed.

SQL> explain plan for
  2   select *
  3     from t
  4    where status='A'
  5  /

Explained.

SQL> select * from table(dbms_xplan.display())
  2  /

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 749696591

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 21272 |   332K|    19  (11)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    | 21272 |   332K|    19  (11)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

   1 - filter("STATUS"='A')

Note
-----
  - dynamic sampling used for this statement<-- Dynamic sampling reads 
                                                       a small number of 
                                                       number of blocks in a  
                                                       table to make a best 
                                                       guess at statistics.


17 rows selected.

SQL> exec dbms_stats.gather_table_stats('SCOTT','T')

PL/SQL procedure successfully completed.

SQL> delete plan_table
  2  /

2 rows deleted.

SQL> commit
  2  /

Commit complete.

SQL> explain plan for
  2   select *
  3     from t
  4    where status='A'
  5  /

Explained.

SQL> select * from table(dbms_xplan.display())
  2  /

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 749696591

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 19257 |   112K|    19  (11)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    | 19257 |   112K|    19  (11)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

   1 - filter("STATUS"='A')

13 rows selected.

SQL> delete plan_table
  2  /

2 rows deleted.

SQL> commit
  2  /

Commit complete.

SQL> exec dbms_stats.gather_index_stats('SCOTT','T_STATUS_NDX')

PL/SQL procedure successfully completed.

SQL> explain plan for
  2   select *
  3     from t
  4    where status='A'
  5  /

Explained.

SQL> select * from table(dbms_xplan.display())
  2  /

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 749696591

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 19257 |   112K|    19  (11)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    | 19257 |   112K|    19  (11)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

   1 - filter("STATUS"='A')

13 rows selected.

SQL> exec dbms_stats.gather_table_stats('SCOTT','T',method_opt=>'for all columns size auto')

PL/SQL procedure successfully completed.

SQL> delete plan_table
  2  /

2 rows deleted.

SQL> commit
  2  /

Commit complete.

SQL> explain plan for
  2   select *
  3     from t
  4    where status='A'
  5  /

Explained.

SQL> select * from table(dbms_xplan.display())
  2  /

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 749696591

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 19478 |   114K|    19  (11)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    | 19478 |   114K|    19  (11)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------

   1 - filter("STATUS"='A')

13 rows selected.


You can see 19478 is an exact row in table for status 'A'

SQL> select status,count(*)
  2    from t
  3   group by status
  4  /

S   COUNT(*)
- ----------
A      19478
B       9739
C       9742

how retention policy affects archive log

2008-01-31T07:40:00.000-08:00

RMAN> show retention policy
2> ;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;

RMAN> report obsolete
2> ;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 2
no obsolete backups found

RMAN> list backup
2> ;

AS documentation says

Besides affecting datafile and control file backups, the retention 
policy affects archived logs and archived log backups.First,RMAN decides which 
datafile and control file backups are obsolete. Then, RMAN considers as obsolete
all archived log backups that are older than the oldest datafile or control file 
backup that must be retained

Now i connect to SQL and create two big table for filling the redo log file and then
get it archived.Please consider it here that i am getting archived before any taking
backup i.e backup database in order to make archive older then the oldest datafile.

C:\>dir C:\oracle\product\10.1.0\flash_recovery_area\ORCL\ARCHIVELOG\2008_03_17
 Volume in drive C is khurram
 Volume Serial Number is F49D-FF2B

 Directory of C:\oracle\product\10.1.0\flash_recovery_area\ORCL\ARCHIVELOG\2008_03_17

03/17/2008  03:44 PM    <DIR>          .
03/17/2008  03:44 PM    <DIR>          ..
03/17/2008  03:44 PM         9,750,528 O1_MF_1_15_3XWLVK6T_.ARC
               1 File(s)      9,750,528 bytes
               2 Dir(s)  62,714,875,904 bytes free

Now i take backup

RMAN> backup database
2> ;

Starting backup at 17-MAR-08
using channel ORA_DISK_1

RMAN> report obsolete
2> ;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 2
no obsolete backups found

RMAN> backup database
2> ;

Starting backup at 17-MAR-08
using channel ORA_DISK_1

RMAN> report obsolete
2> ;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 2
Report of obsolete backups and copies
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Archive Log          402    17-MAR-08          C:\ORACLE\PRODUCT\10.1.0\FLASH_RE
COVERY_AREA\ORCL\ARCHIVELOG\2008_03_17\O1_MF_1_15_3XWLVK6T_.ARC


You can see the obsolete archived files which is the older then the oldest file 
which has been obsolete.It also does'nt make sense to keep the archived log files 
which is older then the oldest file cause it will no longer be useful for recovery 
process.

now this time what i did i created the archived log files after first backup in 
order to not to make it older then the oldest datafile backup.

RMAN> delete obsolete
2> ;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 2
using channel ORA_DISK_1
Deleting the following obsolete backups and copies:
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Archive Log          402    17-MAR-08          C:\ORACLE\PRODUCT\10.1.0\FLASH_RE
COVERY_AREA\ORCL\ARCHIVELOG\2008_03_17\O1_MF_1_15_3XWLVK6T_.ARC

Do you really want to delete the above objects (enter YES or NO)? yes
deleted archive log
archive log filename=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELO
G\2008_03_17\O1_MF_1_15_3XWLVK6T_.ARC recid=402 stamp=649611842
Deleted 1 objects


RMAN> delete backup
2> ;

using channel ORA_DISK_1

RMAN> list backup
2> ;


C:\>dir C:\oracle\product\10.1.0\flash_recovery_area\ORCL\ARCHIVELOG\2008_03_17
 Volume in drive C is khurram
 Volume Serial Number is F49D-FF2B

 Directory of C:\oracle\product\10.1.0\flash_recovery_area\ORCL\ARCHIVELOG\2008_
03_17

03/17/2008  03:59 PM    <DIR>          .
03/17/2008  03:59 PM    <DIR>          ..
               0 File(s)              0 bytes
               2 Dir(s)  62,724,440,064 bytes free


RMAN> backup database
2> ;

Starting backup at 17-MAR-08
using channel ORA_DISK_1

RMAN> report obsolete
2> ;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 2
no obsolete backups found

now i come to at SQL and make redo log file filled by creating 2 big tables in order to get it
archived.


SQL> create table a3 as select * from all_objects
  2  /

Table created.

SQL> create table a4 as select * from all_objects
  2  /

Table created.

C:\>dir C:\oracle\product\10.1.0\flash_recovery_area\ORCL\ARCHIVELOG\2008_03_17
 Volume in drive C is khurram
 Volume Serial Number is F49D-FF2B

 Directory of C:\oracle\product\10.1.0\flash_recovery_area\ORCL\ARCHIVELOG\2008_
03_17

03/17/2008  04:09 PM    <DIR>          .
03/17/2008  04:09 PM    <DIR>          ..
03/17/2008  04:09 PM         9,751,552 O1_MF_1_16_3XWNCGRS_.ARC
               1 File(s)      9,751,552 bytes
               2 Dir(s)  62,563,205,120 bytes free

RMAN> backup database
2> ;

Starting backup at 17-MAR-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset

RMAN> report obsolete
2> ;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 2
no obsolete backups found

You can see no archived logs get obsolete yet cause the archivelog O1_MF_1_16_3
XWNCGRS_.ARC is not older then oldest datafile backup.

But this archivelog file will get obsolete if its beyond the retention policy,lets 
see how ,just take one more backup ,as i have already taken two time backup and 
the moment i go to take third backup it will cross ours retetnion policy from the 
period of 2.


RMAN> backup database
2> ;

RMAN> report obsolete
2> ;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 2
Report of obsolete backups and copies
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           240    17-MAR-08
  Backup Piece       231    17-MAR-08          C:\ORACLE\PRODUCT\10.1.0\FLASH_RE
COVERY_AREA\ORCL\BACKUPSET\2008_03_17\O1_MF_NNNDF_TAG20080317T160604_3XWN4WTB_.B
KP
Backup Set           241    17-MAR-08
  Backup Piece       232    17-MAR-08          C:\ORACLE\PRODUCT\10.1.0\FLASH_RE
COVERY_AREA\ORCL\BACKUPSET\2008_03_17\O1_MF_NCSNF_TAG20080317T160604_3XWN6Z95_.B
KP
Archive Log          403    17-MAR-08          C:\ORACLE\PRODUCT\10.1.0\FLASH_RE
COVERY_AREA\ORCL\ARCHIVELOG\2008_03_17\O1_MF_1_16_3XWNCGRS_.ARC

How to clone database within same server

2008-01-31T06:54:00.000-08:00

SQL> select instance_name from v$instance
  2  /

INSTANCE_NAME
----------------
orcl

SQL> select * from tab
  2  /

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
EMP                            TABLE
BONUS                          TABLE
SALGRADE                       TABLE
TEST1                          TABLE
TEST                           TABLE
TEST2                          TABLE

7 rows selected.

SQL> create table primary_table as select * from all_objects
  2  .
SQL> /

Table created.

SQL> 

==================================================================
Backup production database with archivelog 
==================================================================

Recovery Manager: Release 10.1.0.2.0 - Production

Copyright (c) 1995, 2004, Oracle.  All rights reserved.

RMAN> connect target sys/sys@orcl

connected to target database: ORCL (DBID=1171886541)


RMAN> backup database plus archivelog delete input
2> ;


Starting backup at 29-JAN-08
current log archived
using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=151 devtype=DISK
channel ORA_DISK_1: starting compressed archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=25 recid=82 stamp=645234594
input archive log thread=1 sequence=26 recid=83 stamp=645234661
input archive log thread=1 sequence=27 recid=84 stamp=645234727
input archive log thread=1 sequence=28 recid=85 stamp=645274841
input archive log thread=1 sequence=29 recid=86 stamp=645274906
input archive log thread=1 sequence=30 recid=87 stamp=645284174
input archive log thread=1 sequence=31 recid=88 stamp=645284257
channel ORA_DISK_1: starting piece 1 at 29-JAN-08
channel ORA_DISK_1: finished piece 1 at 29-JAN-08
piece handle=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2008_01_29\O1_MF_ANNNN_TAG20080129T133739_3SXSGO8V_.BKP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08
channel ORA_DISK_1: deleting archive log(s)
archive log filename=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2008_01_28\O1_MF_1_25_3SW8YJTC_.ARC recid=82 stamp=645234594
archive log filename=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2008_01_28\O1_MF_1_26_3SW90NPY_.ARC recid=83 stamp=645234661
archive log filename=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2008_01_28\O1_MF_1_27_3SW92Q8S_.ARC recid=84 stamp=645234727
archive log filename=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2008_01_29\O1_MF_1_28_3SXJ857Y_.ARC recid=85 stamp=645274841
archive log filename=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2008_01_29\O1_MF_1_29_3SXJB85Y_.ARC recid=86 stamp=645274906
archive log filename=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2008_01_29\O1_MF_1_30_3SXSCX8F_.ARC recid=87 stamp=645284174
archive log filename=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2008_01_29\O1_MF_1_31_3SXSGKNL_.ARC recid=88 stamp=645284257
Finished backup at 29-JAN-08

Starting backup at 29-JAN-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSTEM01.DBF
input datafile fno=00003 name=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\SYSAUX01.DBF
input datafile fno=00005 name=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\EXAMPLE01.DBF
input datafile fno=00002 name=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\UNDOTBS01.DBF
input datafile fno=00004 name=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF
channel ORA_DISK_1: starting piece 1 at 29-JAN-08
channel ORA_DISK_1: finished piece 1 at 29-JAN-08
piece handle=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2008_01_29\O1_MF_NNNDF_TAG20080129T133749_3SXSGY3H_.BKP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting compressed full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current controlfile in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 29-JAN-08
channel ORA_DISK_1: finished piece 1 at 29-JAN-08
piece handle=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2008_01_29\O1_MF_NCSNF_TAG20080129T133749_3SXSJPO0_.BKP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:09
Finished backup at 29-JAN-08

Starting backup at 29-JAN-08
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=32 recid=89 stamp=645284335
channel ORA_DISK_1: starting piece 1 at 29-JAN-08
channel ORA_DISK_1: finished piece 1 at 29-JAN-08
piece handle=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2008_01_29\O1_MF_ANNNN_TAG20080129T133855_3SXSK0L2_.BKP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archive log(s)
archive log filename=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2008_01_29\O1_MF_1_32_3SXSJZ1Y_.ARC recid=89 stamp=645284335
Finished backup at 29-JAN-08

Create duplicate database hirarchy with DBCA as same file structure with production database hirarchy


===================================
Add Listener in listener.ora file
===================================

# listener.ora Network Configuration File: C:\oracle\product\10.1.0\Db_2\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL)
      (ORACLE_HOME = %ORACLE_HOME%)
      (SID_NAME = ORCL)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = orcl1)
      (ORACLE_HOME = %oracle_home%)
      (SID_NAME = ORCL1)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = pz-khurrams)(PORT = 1521))
  )

=========================================================
Add tnames string in tnsnames.ora for duplicate database
=========================================================

ORCL1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = pz-khurrams)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl1)
    )
  )


ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = pz-khurrams)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl)
    )
  )

=============================================================================================================
Edit the pfile of duplicate database ORCL1 and make some changes and after all changes just save not save as
=============================================================================================================

DB_FILE_NAME_CONVERT=("C:\oracle\product\10.1.0\oradata\orcl\","C:\oracle\product\10.1.0\oradata\orcl1\orcl1")
LOG_FILE_NAME_CONVERT=("c:\oracle\product\10.1.0\oradata\orcl","C:\oracle\product\10.1.0\oradata\orcl1\orcl1")

=============================================================================================================
Connect to duplicate database ORCL1 and create spfile from edited pfile
=============================================================================================================

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> create spfile from pfile='C:\oracle\product\10.1.0\admin\orcl1\pfile\init.ora.0292008145243'
  2  /

File created.

SQL> startup nomount

ORACLE instance started.
Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145488364 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes

SQL> select instance_name from v$instance
  2  /

INSTANCE_NAME
----------------
orcl1


=============================================================================================================
Connect to target production database ORCL and mount the database
=============================================================================================================

SQL> conn sys/sys@orcl as sysdba
Connected.

SQL> shutdown immediate

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.
Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145488364 bytes
Database Buffers           25165824 bytes
Redo Buffers                 524288 bytes
Database mounted.

SQL> select instance_name from v$instance
  2  /

INSTANCE_NAME
----------------
orcl


=============================================================================================================
Go to dos prompt 
=============================================================================================================


C:\>set oracle_sid=orcl

C:\>rman target / auxiliary sys/sys@orcl1

Recovery Manager: Release 10.1.0.2.0 - Production

Copyright (c) 1995, 2004, Oracle.  All rights reserved.

connected to target database: ORCL (DBID=1171886541)
connected to auxiliary database: orcl1 (not mounted)

RMAN> run
2> {
3> allocate auxiliary channel aux1 device type disk;
4> duplicate target database to 'orcl1';
5> }

using target database controlfile instead of recovery catalog
allocated channel: aux1
channel aux1: sid=160 devtype=DISK

Starting Duplicate Db at 29-JAN-08

contents of Memory Script:
{
   set until scn  1104818;
   set newname for datafile  1 to
 "C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1SYSTEM01.DBF";
   set newname for datafile  2 to
 "C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1UNDOTBS01.DBF";
   set newname for datafile  3 to
 "C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1SYSAUX01.DBF";
   set newname for datafile  4 to
 "C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1USERS01.DBF";
   set newname for datafile  5 to
 "C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1EXAMPLE01.DBF";
   restore
   check readonly
   clone database
   ;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 29-JAN-08

channel aux1: starting datafile backupset restore
channel aux1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1SYSTEM01.DBF
restoring datafile 00002 to C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1UNDOTBS01.DBF
restoring datafile 00003 to C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1SYSAUX01.DBF
restoring datafile 00004 to C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1USERS01.DBF
restoring datafile 00005 to C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1EXAMPLE01.DBF
channel aux1: restored backup piece 1
piece handle=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2008_01_29\O1_MF_NNNDF_TAG20080129T133749_3SXSGY3H_.BKP tag=TAG20080129T133749
channel aux1: restore complete
Finished restore at 29-JAN-08
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "orcl1" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      454
 LOGFILE
  GROUP  1 ( 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1\REDO01.LOG' ) SIZE 10 M  REUSE,
  GROUP  2 ( 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1\REDO02.LOG' ) SIZE 10 M  REUSE,
  GROUP  3 ( 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1\REDO03.LOG' ) SIZE 10 M  REUSE
 DATAFILE
  'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1SYSTEM01.DBF'
 CHARACTER SET WE8MSWIN1252


contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 2 switched to datafile copy
input datafilecopy recid=1 stamp=645290815 filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1UNDOTBS01.DBF
datafile 3 switched to datafile copy
input datafilecopy recid=2 stamp=645290815 filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1SYSAUX01.DBF
datafile 4 switched to datafile copy
input datafilecopy recid=3 stamp=645290815 filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1USERS01.DBF
datafile 5 switched to datafile copy
input datafilecopy recid=4 stamp=645290815 filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1EXAMPLE01.DBF

contents of Memory Script:
{
   set until scn  1104818;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 29-JAN-08

starting media recovery

channel aux1: starting archive log restore to default destination
channel aux1: restoring archive log
archive log thread=1 sequence=32
channel aux1: restored backup piece 1
piece handle=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2008_01_29\O1_MF_ANNNN_TAG20080129T133855_3SXSK0L2_.BKP tag=TAG20080129T133855
channel aux1: restore complete
archive log filename=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\ORCL1\ARCHIVELOG\2008_01_29\O1_MF_1_32_3SXZVRPT_.ARC thread=1 sequence=32
channel clone_default: deleting archive log(s)
archive log filename=C:\ORACLE\PRODUCT\10.1.0\FLASH_RECOVERY_AREA\ORCL1\ORCL1\ARCHIVELOG\2008_01_29\O1_MF_1_32_3SXZVRPT_.ARC recid=1 stamp=645290824
media recovery complete
Finished recover at 29-JAN-08

contents of Memory Script:
{
   shutdown clone;
   startup clone nomount ;
}
executing Memory Script

database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     171966464 bytes

Fixed Size                      787988 bytes
Variable Size                145488364 bytes
Database Buffers              25165824 bytes
Redo Buffers                    524288 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "orcl1" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      454
 LOGFILE
  GROUP  1 ( 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1\REDO01.LOG' ) SIZE 10 M  REUSE,
  GROUP  2 ( 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1\REDO02.LOG' ) SIZE 10 M  REUSE,
  GROUP  3 ( 'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1\REDO03.LOG' ) SIZE 10 M  REUSE
 DATAFILE
  'C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1SYSTEM01.DBF'
 CHARACTER SET WE8MSWIN1252


contents of Memory Script:
{
   catalog clone datafilecopy  "C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1UNDOTBS01.DBF";
   catalog clone datafilecopy  "C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1SYSAUX01.DBF";
   catalog clone datafilecopy  "C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1USERS01.DBF";
   catalog clone datafilecopy  "C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1EXAMPLE01.DBF";
   switch clone datafile all;
}
executing Memory Script

cataloged datafile copy
datafile copy filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1UNDOTBS01.DBF recid=1 stamp=645290915

cataloged datafile copy
datafile copy filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1SYSAUX01.DBF recid=2 stamp=645290916

cataloged datafile copy
datafile copy filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1USERS01.DBF recid=3 stamp=645290916

cataloged datafile copy
datafile copy filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1EXAMPLE01.DBF recid=4 stamp=645290916

datafile 2 switched to datafile copy
input datafilecopy recid=1 stamp=645290915 filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1UNDOTBS01.DBF
datafile 3 switched to datafile copy
input datafilecopy recid=2 stamp=645290916 filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1SYSAUX01.DBF
datafile 4 switched to datafile copy
input datafilecopy recid=3 stamp=645290916 filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1USERS01.DBF
datafile 5 switched to datafile copy
input datafilecopy recid=4 stamp=645290916 filename=C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL1\ORCL1EXAMPLE01.DBF

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 29-JAN-08

RMAN>

SQL> conn scott/tiger@orcl1
Connected.
SQL> select * from tab
  2  /

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
DEPT                           TABLE
EMP                            TABLE
BONUS                          TABLE
SALGRADE                       TABLE
TEST1                          TABLE
TEST                           TABLE
TEST2                          TABLE
PRIMARY_TABLE                  TABLE

8 rows selected.

SQL>