Restoring rman backup witout source database and catalog database from tape

Restoring Rman backup without source database connection and catalog database connection from tape

If we lost Source database and the catalog database and left out with only the tape backup ..then we need to restore the database from the tape  ..Follow the below Steps to Restore the database the.. for restoring in the new host configure the MML [Data Protector] with the new host ie install the dp client in the new host and make sure the connectivity is established

Step 1 Install the New database binaries [software only installation ] in the new Host

Install the database in the new server as the software only install the same database version [binaries should be installed] in the same o/s architecture .. Once the database is Installed Create the new listener and service[tnsname's] create the Pfile with the necessary parameter's as same as the original database [if original database is Rac and the restoring database is non rac then change the Rac parameter's in the pfile] and make the necessary changes like log_file_name_covert and db_file_name_convert in the pfile
we can create the pfile as we details of the previous database .. or else you can restore the spfile from the backup and use it  with the necessary modification

Step 2 Start the database in the No mount stage

Start the database in the No mount stage
syntax
startup pfile='<location of the pfile>' nomount 

SQL> startup pfile='/prod01/app/oraprod/product/11.2.0/dbhome_1/dbs/initOMPROD.ora' nomount
ORACLE instance started.

Total System Global Area 1.0689E+10 bytes
Fixed Size                  2192008 bytes
Variable Size             369102200 bytes
Database Buffers         1.0301E+10 bytes
Redo Buffers               16969728 bytes
SQL>

create the database password file orapw<sid>
Once the database is started in the no mount stage first we need to restore the control file first and the start the restore of the database

Step 3 Check the backup in the data Protector

Login to the Data Protector or any of the MML software's  used and check the backup piece's for the tape library

You can find the backup details in the internal database ---->sessions----->and the backup details .. you can find the message file related to this file

Check the channels in the backup we need to use the same channels in the restore command 

Step 4 Restore the Control file from the backup piece 

Locate the control file in the backup and then restore the control file from the  backup piece
start up the database in the no mount stage and Connect the rman as target 
Here we should not connect with catalog or auxiliary  because we don't have source or catalog database

Syntax
run
{
 allocate channel 'dev_0' type 'sbt_tape' parms 'SBT_LIBRARY=<sbt_location>
set until time "to_date(<date>,'YYYY-MM-DD HH24:MI:SS')";
restore controlfile from <backup Piece of the Control_file>
}

Legends

Sbt_location = as same as the location parameter which is specified in the Dp while taking the backup
Set until time = specify the time until which the database is to be restored
Backup piece of control file  = check the backup piece name from the Data Protector backup

RMAN> run
2> {
3> allocate channel 'dev_0' type 'sbt_tape' parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=OMPROD,OB2BARLIST=DB01New)';
4> set until time "to_date('2013-10-07:1:44:00','YYYY-MM-DD HH24:MI:SS')";
5> restore controlfile from 'c-3864922897-20131008-00';
6> }

using target database control file instead of recovery catalog
allocated channel: dev_0
channel dev_0: SID=756 device type=SBT_TAPE
channel dev_0: Data Protector A.07.00/72
executing command: SET until clause
Starting restore at 06-JAN-14
channel dev_0: restoring control file
channel dev_0: restore complete, elapsed time: 00:29:37
output file name=/u02/app/oracle/oradata/controlfile01.ctl
output file name=/u02/app/oracle/oradata/controlfile02.ctl
Finished restore at 06-JAN-14
released channel: dev_0

Step 5 Restore the database

For restoration we need to Mount the database Once the restoration of the control file  is complete then we need to mount the database
After mounting  the database start the restore script

Run
{
allocate channel<channel_name> type 'sbt_tape' parms 'SBT_LIBRARY=<location of the sbt_tape>
set newname for datafile  <filenumber>  to '<restoration path>'
---
---
Set newname for datafile <last_file_number> to '<restoration_path>'
Set until time "to_date( date ,date format)
restore database;
recover database;
}

Legends
location of the sbt_tape=  path to the Sbt tape library
filenumber=datafile number
lastfilenumber=the last datafile number [we need to specify the set new name command for all the datafile's in the backup you can check the data files in the back by check the messages tab in the data protector ]
restoration path =The path where the data file is going to be restored

Run
{
allocate channel 'dev_0' type 'sbt_tape' parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV(OB2BARTYPE=Oracle8,OB2APPNAME=OMPROD,OB2BARLIST=DB01New)';
set newname for datafile    1    to                     '/u01/oradata/<filename>;
----
----
----
-----
set newname for datafile    82   to                     '/u01/oradata/<filename>;
set until time "to_date('2013-10-07:1:44:00','YYYY-MM-DD HH24:MI:SS')";
restore database;
recover database;
}

Once the restore is completed  then the recover command will give error due to the patch of the data file in the control file is different [it will point to the original database data file's path in the control file ]

channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 02:01:43
Finished restore at 27-JAN-14

Starting recover at 27-JAN-14

starting media recovery
media recovery failed
released channel: dev_0
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/27/2014 05:21:44
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed
 start until time 'OCT 07 2013 01:44:00' using backup controlfile
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 1: '+DATA/omprod/datafile/system.316.825482365'
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '+DATA/omprod/datafile/system.316.825482365'

We are getting the above error due to the o control file has it's previous database [source database]data file path [as the original database is rac with asm]




So we need to recreate the control file with the correct path





Step  6 Recreate the control file



Trace the control file to create the control file script

alter database backup control file to trace


it will Create the trace file which contains script to recreate the control file change the path  of the data file's and the log file to recover  the database

CREATE CONTROLFILE REUSE DATABASE "OMPROD" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 1024
    MAXLOGMEMBERS 5
    MAXDATAFILES 512
    MAXINSTANCES 8
    MAXLOGHISTORY 7303
LOGFILE
  GROUP 1 (
    '/u01/oradata/group_1.342.825482805.log',
    '/u01/oradata/group_1.261.825482807.log'
  ) SIZE 500M BLOCKSIZE 1024,
  GROUP 2 (
    '/u01/oradata/group_2.343.825482811.log',
    '/u01/oradata/group_2.262.825482813.log'
  ) SIZE 500M BLOCKSIZE 1024,
  GROUP 3 (
    '/u01/oradata/group_3.340.825482819.log',
    '/u01/oradata/group_3.257.825482823.log'
  ) SIZE 500M BLOCKSIZE 1024,
  GROUP 4 (
    '/u01/oradata/group_4.341.825482825.log',
    '/u01/oradata/group_4.258.825482829.log'
  ) SIZE 500M BLOCKSIZE 1024
-- STANDBY LOGFILE
DATAFILE
'/u01/oradata/<file_name>.dbf',
  '/u01/oradata/<file_name>.dbf',
 ------
------
------
/u01/oradata/<file_name>.dbf'
CHARACTER SET UTF8
;
Modify the control file creation script and recreate the control file  before creating the controlfile we need to shutdown the database and startup nomount the database ....
Once the database is started in the nomount stage run the controlfile creation script to create the controlfile ..Once the contolfile is created the database is will in mount stage

Step 7 Catalog the archivelog backup Piece   

We need to catalog the archivelog backup piece for restoring the  necessary archive log

We need to Configure two Parameter's 

CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=OMPROD,OB2BARLIST=DB01New)';
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE'

CATALOG DEVICE TYPE 'SBT_TAPE' BACKUPPIECE '<name of backup piece contains the necessary archive log in tape >

RMAN> CATALOG DEVICE TYPE 'SBT_TAPE' BACKUPPIECE 'DB01-New<OMPROD_784:828148601:1>.dbf';

allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=506 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Data Protector A.07.00/72
cataloged backup piece
backup piece handle=DB01-New<OMPROD_784:828148601:1>.dbf RECID=1 STAMP=837986306

if the necessary archive log is in one or more files then we need to catalog the file one by one catalog one backup file and then run the recover command .. the recover command will restore the archivelog in the default archivelog location and still it needs the more archivelog then it will error out again ..with the new archivelog number which is necessary so now we need to find the new archivelog number file and catalog it and re run the recover command ..

Step 8 Recover the database

Run the recover command after allocating the channel

RMAN> run
2> {
3> allocate channel 'dev_0' type 'sbt_tape' parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=OMPROD,OB2BARLIST=DB01New)';
4> recover database until time "to_date('2013-10-07:1:44:00','YYYY-MM-DD HH24:MI:SS')";
5> }

released channel: ORA_SBT_TAPE_1
allocated channel: dev_0
channel dev_0: SID=506 device type=SBT_TAPE
channel dev_0: Data Protector A.07.00/72

Starting recover at 27-JAN-14

starting media recovery

archived log for thread 2 with sequence 1014 is already on disk as file /prod01/app/oraprod/fast_recovery_area/OMPROD/archivelog/2014_01_27/o1_mf_2_1014_9gf7zs4w_.arc
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=786
channel dev_0: restoring archived log
archived log thread=2 sequence=1015
channel dev_0: reading from backup piece DB01-New<OMPROD_791:828236299:1>.dbf
channel dev_0: piece handle=DB01-New<OMPROD_791:828236299:1>.dbf tag=TAG20131008T013818
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:06:06
archived log file name=/prod01/app/oraprod/fast_recovery_area/OMPROD/archivelog/2014_01_27/o1_mf_1_786_9gf97ylf_.arc thread=1 sequence=786
archived log file name=/prod01/app/oraprod/fast_recovery_area/OMPROD/archivelog/2014_01_27/o1_mf_2_1014_9gf7zs4w_.arc thread=2 sequence=1014
archived log file name=/prod01/app/oraprod/fast_recovery_area/OMPROD/archivelog/2014_01_27/o1_mf_2_1015_9gf97ymf_.arc thread=2 sequence=1015
channel default: deleting archived log(s)
archived log file name=/prod01/app/oraprod/fast_recovery_area/OMPROD/archivelog/2014_01_27/o1_mf_1_786_9gf97ylf_.arc RECID=4 STAMP=837988248
channel default: deleting archived log(s)
archived log file name=/prod01/app/oraprod/fast_recovery_area/OMPROD/archivelog/2014_01_27/o1_mf_2_1015_9gf97ymf_.arc RECID=3 STAMP=837988247
media recovery complete, elapsed time: 00:00:13
Finished recover at 27-JAN-14
released channel: dev_0

Once the recover command is complete then we need to  open the database with the reset log's .. the log files which is specified in the control file will be create .. before opening of the database by the rest log's command 

SQL> alter database open resetlogs;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

We have not create the temp table space so we will find the error in the archive log for the temp table space
We need to create the new temp table space and assign as the default temp table space and drop the Previous table space  

For the regular Cloning activities we can follow the duplicate the database when the catalog and source database are available but when both are not available only the tape backup is aaliable then follow the above process  
you might get the below error's in the alert log while this Process  of restoration

Catalog the rman backup piece in sbt_tape with DataProtector

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent

RMAN-03002: failure of recover command at 01/27/2014 22:09:43
RMAN-06054:media recovery requesting unknown archived log for thread 1 with sequence 786 and starting SCN of 22193647084

We have the tape backup of the database with archive log.. But we lost the recover catalog and the Original database of the backup..So we had started the restore session with the tape backup available with the new server in different host
Once the restore of the tape backup had  been completed successfully but the recover fails with the above error

Step 1 :=Run the recover command after successful restore of the database

Connect the rman as target  and run the below script to restore from the data protector[M.M.L]

RMAN> run
2> {
3>  allocate channel 'dev_0' type 'sbt_tape' parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=OMPROD,OB2BARLIST=DB01New)';
4> set until time "to_date('2013-10-07:1:44:00','YYYY-MM-DD HH24:MI:SS')";
5> recover database;
6> }
using target database control file instead of recovery catalog
allocated channel: dev_0
channel dev_0: SID=256 device type=SBT_TAPE
channel dev_0: Data Protector A.07.00/72
executing command: SET until clause
Starting recover at 27-JAN-14
starting media recovery
unable to find archived log
archived log thread=1 sequence=785
Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/oradata/system.316.825482365.dbf'
released channel: dev_0
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/27/2014 09:32:0

Step 2 :=Check the database status by using the below query and run the database restore preview

This error occurred Because of the  database needs the some of the archive log to Complete restore of the system until the Specified point in time which we have mentioned in while restore and recover

To find until which sequence number we need to restore we can find by using the below script in the database which is in the mount stage

checking the database restore Preview
run 
{ 
 allocate channel 'dev_0' type 'sbt_tape' parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=OMPROD,OB2BARLIST=DB01New)';
restore database preview  until time "to_date('2013-10-07:1:44:00','YYYY-MM-DD HH24:MI:SS')";
}

check the database incarnation

RMAN> list incarnation of database;
list incarnation of database;
using target database control file instead of recovery catalog

List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 OMPROD 3864922897 CURRENT 21234847062 06-SEP-13
 
SQL> select * from v$database_incarnation;

    INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TIME      PRIOR_RESETLOGS_CHANGE# PRIOR_RESETLOGS_TIM STATUS      RESETLOGS_ID PRIOR_INCARNATION# FLASHBACK_DATABASE_ALLOWE
D
---------------- ----------------- ------------------- ----------------------- ------------------- ------- ---------------- ------------------ -------------------------
-
               1       21234847062 06-09-2013 01:43:14               102590762 18-01-2008 03:33:51 CURRENT        825385394                  0 NO


SQL> select max(fhafs) "Minimum PITR SCN" from x$kcvfh;

Minimum PITR SCN
----------------
22193643139------- Until this scn number we need to recover the database

To Find the Archive log  Number use the below query which will display the SCN and archive log  number

SQL> SELECT hxfil file_num,
  2  fhscn scn,
  3  fhsta status ,
  4  fhthr thread,
  5  fhrba_seq sequence
  6  FROM x$kcvfh
  7  order by scn;

To check the database current status
SQL> SELECT name,
  2  file#,
  3  status,
  4  error,
  5  creation_change#,
  6  TO_CHAR(creation_time, 'DD-MM-YYYY HH24:MI:SS') as creation_time,
  7  TO_CHAR(checkpoint_change#, '999999999999999') as checkpoint_change#,
  8  TO_CHAR(checkpoint_time, 'DD-MM-YYYY HH24:MI:SS') as checkpoint_time,
  9  TO_CHAR(resetlogs_change#, '999999999999999') as resetlogs_change#,
 10  TO_CHAR(resetlogs_time, 'DD-MM-YYYY HH24:MI:SS') as resetlogs_time,
 11  TO_CHAR(bytes, '9,999,999,999,990') as bytes
 12  FROM v$datafile_header
 13  ORDER BY checkpoint_change#;



Step 3  :=Check the Data Protector Internal database to find the backup piece which contains  the archive log  


Go to the data Protector and check the particular backup Piece which contains the archive log sequence number

Open the Dp----internaldatabase----- sessions----backup<with date>----right check and go to the messages tab and you will find the details message's when the backup had been ran and where the archive log is store

The message fine will be like below



RMAN-08540: channel dev_0: backup set complete, elapsed time: 00:01:45
RMAN-08009: channel dev_0: starting archived log backup set
RMAN-08014: channel dev_0: specifying archived log(s) in backup set
RMAN-08504: input archived log thread=2 sequence=1007 RECID=1831 STAMP=828132023
RMAN-08504: input archived log thread=2 sequence=1008 RECID=1832 STAMP=828134415
RMAN-08504: input archived log thread=2 sequence=1009 RECID=1835 STAMP=828137780
RMAN-08504: input archived log thread=1 sequence=782 RECID=1834 STAMP=828134857
RMAN-08504: input archived log thread=1 sequence=783 RECID=1838 STAMP=828137786
RMAN-08504: input archived log thread=2 sequence=1010 RECID=1836 STAMP=828137783
RMAN-08504: input archived log thread=2 sequence=1011 RECID=1837 STAMP=828137783
RMAN-08504: input archived log thread=1 sequence=784 RECID=1841 STAMP=828143453
RMAN-08504: input archived log thread=2 sequence=1012 RECID=1839 STAMP=828141110
RMAN-08504: input archived log thread=2 sequence=1013 RECID=1840 STAMP=828143453
RMAN-08504: input archived log thread=1 sequence=785 RECID=1843 STAMP=828148493
RMAN-08504: input archived log thread=2 sequence=1014 RECID=1842 STAMP=828148491
RMAN-08038: channel dev_0: starting piece 1 at 07-OCT-13
[Normal] From: OB2BAR_SBT_CHANNEL@db01.<domain>.com "OMPROD"  Time: 10/7/2013 1:16:42 AM
    Starting OB2BAR Backup: db01.<domain>.com:DB01-New<OMPROD_784:828148601:1>.dbf "Oracle8"
Here the necessary archive log file is in the backup Piece DB01-New<OMPROD_784:828148601:1>.dbf

Once we find the necessary file we need to catalog the backup in rman and then restore and recover the archive log from the tape backup

Step 4 :=CATALOG BACKUP PIECE FROM THE SBT_TAPE IN RMAN

How to catalog the rman backup piece from the tape

To catalog the backup First we need to Configure the separate channel  with the tape drive

Syntax

 CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS  'SBT_LIBRARY=<Path for the Dp conenction>

CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS  'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=OMPROD,OB2BARLIST=DB01New)'; 

The dp connection path can be find in the Dp backup message file wile allocating the channel

allocate channel 'dev_0' type 'sbt_tape'parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=OMPROD,OB2BARLIST=DB01-New)' 

We need to use the same path from this message file in the SBT_LIBRARY 

we need to set the default device type to tape

CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE'; 

Once both the configuration parameter's are set in the Dp then we need to catalog the backup

Syntax 

Catalog Device type 'Sbt_Tape' backupPiece '<backup piece name from the data protector>

 

RMAN> CATALOG DEVICE TYPE 'SBT_TAPE' BACKUPPIECE 'DB01-New<OMPROD_784:828148601:1>.dbf';

Step 5 :=Run Recover Command to recover the database

Recover the database by using the recover command  in the rman connect the the rman as target with database in the mount stage and run the recover command 

The below command is used for the recover the database 

run

{

allocate channel 'dev_0' type 'sbt_tape' parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV(OB2BARTYPE=Oracle8,OB2APPNAME=OMPROD,OB2BARLIST=DB01New)';

set until time "to_date('2013-10-07:1:44:00','YYYY-MM-DD HH24:MI:SS')";

recover database;

}

Legends

Run--- is used in Rman to run the set of rman command's 

Allocate channel --- we need to allocate the channel which will establish the communication with the database and the backup medium through this channel the backup,restore and recover happens.. since in this process we are using the tape we need to specify the sbt_library location<path> ..this path will be communicate to the physical tape library with the media management library software 

Set until time ----- It is used to set the time until we need to recover

RMAN> run
2> {
3> allocate channel 'dev_0' type 'sbt_tape' parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=OMPROD,OB2BARLIST=DB01New)';
4> recover database until time "to_date('2013-10-07:1:44:00','YYYY-MM-DD HH24:MI:SS')";
5> }

released channel: ORA_SBT_TAPE_1
allocated channel: dev_0
channel dev_0: SID=506 device type=SBT_TAPE
channel dev_0: Data Protector A.07.00/72

Starting recover at 27-JAN-14

starting media recovery

channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=785
channel dev_0: restoring archived log
archived log thread=2 sequence=1014
channel dev_0: reading from backup piece DB01-New<OMPROD_784:828148601:1>.dbf
channel dev_0: piece handle=DB01-New<OMPROD_784:828148601:1>.dbf tag=TAG20131007T011454
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:05:25
archived log file name=/prod01/app/oraprod/fast_recovery_area/OMPROD/archivelog/2014_01_27/o1_mf_1_785_9gf7zs2n_.arc thread=1 sequence=785
archived log file name=/prod01/app/oraprod/fast_recovery_area/OMPROD/archivelog/2014_01_27/o1_mf_2_1014_9gf7zs4w_.arc thread=2 sequence=1014
channel default: deleting archived log(s)
archived log file name=/prod01/app/oraprod/fast_recovery_area/OMPROD/archivelog/2014_01_27/o1_mf_1_785_9gf7zs2n_.arc RECID=2 STAMP=837986910
unable to find archived log
archived log thread=1 sequence=786
released channel: dev_0
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/27/2014 22:09:43
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 786 and starting SCN of 22193647084

Now the archivelog's had been restored in the fast_recover_area but still the database need's more archivelog to recover  the database so again we need to catalog the another file from the data protector backup and then start recover command

In the above senario we need the log sequence 786 that is in the below backup Piece 

catalog the backup next backup piece which Contains the archive log

We need to catalog the backup piece one by one until the database recover had been completely successfully .. once the catalog of the backup piece is create then the rman looks over that particular file and then start's the recover command ...

 

RMAN> CATALOG DEVICE TYPE 'SBT_TAPE' BACKUPPIECE 'DB01-New<OMPROD_784:828148601:1>.dbf';

allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=506 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Data Protector A.07.00/72
cataloged backup piece
backup piece handle=DB01-New<OMPROD_784:828148601:1>.dbf RECID=1 STAMP=837986306

Once the particular backup file is cataloged then we need to run the recover command again 

RMAN> run
2> {
3> allocate channel 'dev_0' type 'sbt_tape' parms 'SBT_LIBRARY=/opt/omni/lib/libob2oracle8_64bit.so,ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=OMPROD,OB2BARLIST=DB01New)';
4> recover database until time "to_date('2013-10-07:1:44:00','YYYY-MM-DD HH24:MI:SS')";
5> }

released channel: ORA_SBT_TAPE_1
allocated channel: dev_0
channel dev_0: SID=506 device type=SBT_TAPE
channel dev_0: Data Protector A.07.00/72

Starting recover at 27-JAN-14

starting media recovery

archived log for thread 2 with sequence 1014 is already on disk as file /prod01/app/oraprod/fast_recovery_area/OMPROD/archivelog/2014_01_27/o1_mf_2_1014_9gf7zs4w_.arc
channel dev_0: starting archived log restore to default destination
channel dev_0: restoring archived log
archived log thread=1 sequence=786
channel dev_0: restoring archived log
archived log thread=2 sequence=1015
channel dev_0: reading from backup piece DB01-New<OMPROD_791:828236299:1>.dbf
channel dev_0: piece handle=DB01-New<OMPROD_791:828236299:1>.dbf tag=TAG20131008T013818
channel dev_0: restored backup piece 1
channel dev_0: restore complete, elapsed time: 00:06:06
archived log file name=/prod01/app/oraprod/fast_recovery_area/OMPROD/archivelog/2014_01_27/o1_mf_1_786_9gf97ylf_.arc thread=1 sequence=786
archived log file name=/prod01/app/oraprod/fast_recovery_area/OMPROD/archivelog/2014_01_27/o1_mf_2_1014_9gf7zs4w_.arc thread=2 sequence=1014
archived log file name=/prod01/app/oraprod/fast_recovery_area/OMPROD/archivelog/2014_01_27/o1_mf_2_1015_9gf97ymf_.arc thread=2 sequence=1015
channel default: deleting archived log(s)
archived log file name=/prod01/app/oraprod/fast_recovery_area/OMPROD/archivelog/2014_01_27/o1_mf_1_786_9gf97ylf_.arc RECID=4 STAMP=837988248
channel default: deleting archived log(s)
archived log file name=/prod01/app/oraprod/fast_recovery_area/OMPROD/archivelog/2014_01_27/o1_mf_2_1015_9gf97ymf_.arc RECID=3 STAMP=837988247
media recovery complete, elapsed time: 00:00:13
Finished recover at 27-JAN-14
released channel: dev_0

Now the Recover of the database had been completely successfully and  we need to open the database by using the reset logs;

Step 6 := Open the database by using the reset logs 

SQL> alter database open resetlogs;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

 

Rman catalog creation in 11gr2 database

RMAN CATALOG CREATION IN 11GR2 DATABASE

The catalog database acts as the centralized inventory of  database's and stores the backup details of all the database which is registered with it .. there are two methods the Rman can be operated the control file and the Recovery catalog
For creating the recovery catalog database we need to create the new database or we can use the existing database .. But the safe parctice is to create the new database which is dedicated for the CATALOG ..

The benefit of using the recover catalog is all the backup information of the database is stored in recover catalog database tables and we can query the status of the backup and the details of the backup from the recovery catalog database

Step 1

Create the Separate database which is going to host the Rman catalog database

Step 2

Create the Separate  table space which will hold all the catalog schema tables in it 

SQL> CREATE TABLESPACE <tables pace_name>
  2  DATAFILE '<datafile_name>' size 10240M
  3  Autoextend on;
Tablespace created.

Step3 

Create the new user which will the Owner of the Rman catalog

Create user <username> identified by <password>

Default tablespace <tablespace_name>

Temporary tablespace <temporary tablespace name>

Quota <percentage of allocated quota> on <table space>

 Step 4

Grant the recovery catalog owner role to the rcat user

Step5

Fom the O/S Prompt connect to the rman

Step 6 

Connect to the recover catalog by using the new user name which we have created 

Step 7

Connect to the recover catalog and register the database

RMAN-20020: database incarnation not set

RMAN-06004: ORACLE error from recovery catalog database: RMAN-20020: database incarnation not set

When we try to back the resync the catalog with the target database if we get this message then we have to reset the database to set the incarnation to the current

it can be cauased when we open the database with reset logs or if the  upgrade the catalog and connect with the target database

The database incarnations are used to identify the database backup belongs to which period .. for ex. day 1 when we take the back of the database then a incarnation will be generated and stored .. if we restore the database and the open it again then the new incarnation number will be generated .. now if we want to restore the backup which is take on the day one then we must set the database incarnation to the first incarnation number by using the RESET DATABASE TO INCARNATION                        the database control file will also hold the incarnation number in it




Step 1

connect to  rman and connect to the target database with recovery catalog

[ora@fa ~]$ rman
Recovery Manager: Release 11.2.0.3.0 - Production on Tue May 14 13:35:00 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
 

RMAN> connect target /
connected to target database: <SID> (DBID=<database_id>)
 

RMAN> connect catalog rcat/<password>@<catalog database service name>
connected to recovery catalog database

Step2

RMAN> resync catalog
2> ;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of resync command on default channel at 05/14/2013 13:36:02
RMAN-20020: database incarnation not set

RMAN> backup datafile <file_number>;
Starting backup at 14-MAY-13
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 05/14/2013 13:37:14
RMAN-03014: implicit resync of recovery catalog failed
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20020: database incarnation not set

Step 3

once you got the incarnation not set check the list of incarnation the database has

RMAN> list incarnation ;
List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
29      360     FAHDB    728861041        PARENT  1          05-MAR-12
29      361     FAHDB    728861041        PARENT  14640435   08-JUN-12
29      98      FAHDB    728861041        PARENT  14882874888 22-JUN-12
29      30      FAHDB    728861041        ORPHAN  15574861795 11-JUL-12
29      321     FAHDB    728861041        CURRENT 15996977185 22-AUG-12
1       17      ORACAT   2821584587       PARENT  1          05-SEP-10
1       2       ORACAT   2821584587       CURRENT 972274     12-MAY-13

SQL> select * from v$controlfile_record_section where TYPE ='DATABASE INCARNATION';

TYPE                         RECORD_SIZE RECORDS_TOTAL RECORDS_USED FIRST_INDEX LAST_INDEX LAST_RECID
---------------------------- ----------- ------------- ------------ ----------- ---------- ----------
DATABASE INCARNATION                  56           292            4           1          4          4

we can check the no of database incarnations in the database control file ...

Step 4

To sync the incarnation number with the catalog issue the Reset the database  command in the rman 

and test the backup

RMAN> reset database;

database incarnation already registered

RMAN> backup datafile 3;
Starting backup at 14-MAY-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=4256 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=00003 name=+DATA/fahdb/datafile/<datafile_number>
channel ORA_DISK_1: starting piece 1 at 14-MAY-13
channel ORA_DISK_1: finished piece 1 at 14-MAY-13
piece handle=+FLASH/fahdb/backupset/2013_05_14/nnndf0_tag20130514t135843_0.282.815407125 tag=TAG20130514T135843 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 14-MAY-13
Starting Control File and SPFILE Autobackup at 14-MAY-13
piece handle=+FLASH/fahdb/autobackup/2013_05_14/s_815407132.326.815407133 comment=NONE
Finished Control File and SPFILE Autobackup at 14-MAY-13

RMAN> resync catalog ;
starting full resync of recovery catalog
full resync complete

PL/SQL package RCAT.DBMS_RCVCAT version 11.02.00.02 in RCVCAT database is not current

PL/SQL package RCAT.DBMS_RCVCAT version 11.02.00.02 in RCVCAT database is not current

when we are trying to connect the Rman catalog database we are getting  the error it is cause because The particular version of the source database catalog is not sync with the current catalog..
The Cause of this Error is due to the recover catalog database or the target database must be upgraded .. or the recover catalog database is changed

Step 1

[ora@fah ~]$ rman

Recovery Manager: Release 11.2.0.3.0 - Production on Mon May 13 17:18:58 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
RMAN> connect target /
connected to target database: <SID> (DBID=<>)

RMAN> connect catalog rcat/<password>@ORACAT<Domain_name>
connected to recovery catalog database

PL/SQL package RCAT.DBMS_RCVCAT version 11.02.00.02 in RCVCAT database is not current

PL/SQL package RCAT.DBMS_RCVMAN version 11.02.00.02 in RCVCAT database is not current

Step 2

For check the issue if we give the Register database or Resync catalog thou will get the below error message

RMAN> register database;

DBGSQL:     RCVCAT> begin dbms_rcvman.dumpPkgState('RCVMAN after sqlerror'); end;

DBGSQL:        sqlcode = 6550

DBGSQL:     RCVCAT> begin dbms_rcvcat.dumpPkgState('RCVCAT after sqlerror');end;

DBGSQL:        sqlcode = 6550

database registered in recovery catalog

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03008: error while performing automatic resync of recovery catalog
RMAN-10015: error compiling PL/SQL program
RMAN-10014: PL/SQL error 0 on line 1655 column 12: Statement ignored
RMAN-10014: PL/SQL error 306 on line 1655 column 12: wrong number or types of arguments in call to 'CHECKTABLESPACE'

RMAN> resync catalog;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of resync command on default channel at 05/13/2013 17:19:38
RMAN-10015: error compiling PL/SQL program
RMAN-10014: PL/SQL error 0 on line 1655 column 12: Statement ignored
RMAN-10014: PL/SQL error 306 on line 1655 column 12: wrong number or types of arguments in call to 'CHECKTABLESPACE'

Step 3

We need to give the upgrade catalog command to sync the new change in the binaries to reflect in the catalog database

RMAN> upgrade catalog;

recovery catalog owner is RCAT
enter UPGRADE CATALOG command again to confirm catalog upgrade

RMAN> upgrade catalog;

recovery catalog upgraded to version 11.02.00.03
DBMS_RCVMAN package upgraded to version 11.02.00.03
DBMS_RCVCAT package upgraded to version 11.02.00.03



Step 4
Connect the target database with the rman and catalog now you can issue the command report schema

[oratest@fahtestapp ~]$ rman

Recovery Manager: Release 11.2.0.3.0 - Production on Mon May 13 17:21:20 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
RMAN> connect target /
connected to target database: <SID> (DBID=<dbid_number>)
RMAN> connect catalog rcat/<password>@ORACAT.<Domain_name>
connected to recovery catalog database

RMAN> report schema;
Report of database schema for database with db_unique_name <SID>
List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
---
----
----
List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
---
---

ORA-19566: exceeded limit of 0 corrupt blocks for file

ORA-19566: exceeded limit of 0 corrupt blocks for file

When we try to the backup a data file or database in RMAN if any  block's is corrupted then rman will throw this error
RMAN will not take the backup the corrupted data file . rman will take the backup of the non corrupted blocks only .. the difference between the user managed backup and rman backup is rman will take only used block's ..but if Any  blocks are corrupted then it will Not backup that file ..

If you are backup up the full database you can exclude the particular corrupted data file from the backup script and then  format the block's and then take the backup.. if the corrupted file is the important file like system data file then try to format first

Here In this scenario we don't have backup of the data file which the block had been corrupted ..as per the below example the system data file 9 has the block corruption.. and we need to recover the blocks which are corrupted with out backup of the  data file    
Step 1

when we try to backup the particular  data file we are getting the error mentioned below

RMAN> backup datafile 352;

Starting backup at 29-OCT-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=373 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=00352 name=/p01/app/PROD/db/apps_st/data/system09.dbf
channel ORA_DISK_1: starting piece 1 at 29-OCT-12
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 10/29/2012 12:25:04
ORA-19566: exceeded limit of 0 corrupt blocks for file /p01/app/PROD/db/apps_st/data/system09.dbf

Step 2

To verify the blocks corrupted we  need to run the dbv command line utility 

Syntax dbv file='location of the file' 

oraprod@proddb ~]$ dbv  file=/p01/app/PROD/db/apps_st/data/system09.dbf

DBVERIFY: Release 11.1.0.7.0 - Production on Mon Oct 29 15:58:05 2012

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

DBVERIFY - Verification starting : FILE = /p01/app/PROD/db/apps_st/data/system09.dbf
Page 165665 is marked corrupt
Corrupt block relative dba: 0x58028721 (file 352, block 165665)
Completely zero block found during dbv:
......................................
......................................
Page 191995 is marked corrupt
Corrupt block relative dba: 0x5802edfb (file 352, block 191995)
Completely zero block found during dbv:

Page 191996 is marked corrupt
Corrupt block relative dba: 0x5802edfc (file 352, block 191996)
Completely zero block found during dbv:

Page 191997 is marked corrupt
Corrupt block relative dba: 0x5802edfd (file 352, block 191997)
Completely zero block found during dbv:

Page 191998 is marked corrupt
Corrupt block relative dba: 0x5802edfe (file 352, block 191998)
Completely zero block found during dbv:

Page 191999 is marked corrupt
Corrupt block relative dba: 0x5802edff (file 352, block 191999)
Completely zero block found during dbv:

Page 192000 is marked corrupt
Corrupt block relative dba: 0x5802ee00 (file 352, block 192000)
Completely zero block found during dbv:

..................
..................

DBVERIFY - Verification complete

Total Pages Examined         : 192000
Total Pages Processed (Data) : 131617
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 30473
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 1908
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 1666
Total Pages Marked Corrupt   : 26336
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 3721895981 (1388.3721895981)

Once you conform through dbv the blocks are corrupted then you need to run the Rman validating script 

Rman script to  validate

run {
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
backup check logical validate database;
release channel d1;
release channel d2;
release channel d3;
}

it  will produce the error in the alert log for all the corrupted blocks it will validate all the blocks corrupted and populate into the v$database_block_corruption Once the Rman command completes then you can proceed to the next step ..it is mandatory to complete this step

Step 3

Check the no of blocks corrupted

SQL> Select * from v$database_block_corruption ;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTI
---------- ---------- ---------- ------------------ ---------
       352     165660      26341                  0 ALL ZERO

note the  it is the data file number which is effected  and the block number

Step 4

Check the corrupted block belongs to any object


 SYNTEX :select segment_name, segment_type, owner
       from dba_extents
      where file_id = <data_file_name>
        and  < corrupted block number >between block_id
            and block_id + blocks -1;



SQL> select segment_name, segment_type, owner
       from dba_extents
      where file_id = 352
        and  165660 between block_id
            and block_id + blocks -1;

It will return now rows selected and then proceed with the steps if it returns now row selected then the particular block  does not belongs to any object and it is empty
To conform the empty block run the below query

SYNTAX


Select * from dba_free_space where file_id= < corrupted data file number>
     and < corrupted block number>between block_id and block_id + blocks -1;



SQL>Select * from dba_free_space where file_id= 352
     and 165660 between block_id and block_id + blocks -1;


Step 5

Run the below sql command to find the details of the Corrupted  block here we save the below command as the block_curropt.sql


set lines 200 pages 10000
col segment_name format a30

SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
, greatest(e.block_id, c.block#) corr_start_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
- greatest(e.block_id, c.block#) + 1 blocks_corrupted
, null description
FROM dba_extents e, v$database_block_corruption c
WHERE e.file_id = c.file#
AND e.block_id <= c.block# + c.blocks - 1
AND e.block_id + e.blocks - 1 >= c.block#
UNION
SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
, header_block corr_start_block#
, header_block corr_end_block#
, 1 blocks_corrupted
, 'Segment Header' description
FROM dba_segments s, v$database_block_corruption c
WHERE s.header_file = c.file#
AND s.header_block between c.block# and c.block# + c.blocks - 1
UNION
SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
, greatest(f.block_id, c.block#) corr_start_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
- greatest(f.block_id, c.block#) + 1 blocks_corrupted
, 'Free Block' description
FROM dba_free_space f, v$database_block_corruption c
WHERE f.file_id = c.file#
AND f.block_id <= c.block# + c.blocks - 1
AND f.block_id + f.blocks - 1 >= c.block#
ORDER BY file#, corr_start_block#;

Once you conform the blocks are not belonging to any Object then proceed with the next step

Step 6

Create a table from the normal user [here we use the scott ]

SQL> create table om (

       n number,

       c varchar2(4000)

     ) nologging tablespace system    ;

Table created.

Table should be created on the Corrupted  data file's table space [here system data file's blocks are Corrupted so we use the data file as system ]


SQL> select segment_name,tablespace_name from user_segments
      where segment_name='S' ;  2

SEGMENT_NAME                   TABLESPACE_NAME
------------------------------ ------------------------------
OM                          SYSTEM

Step 7

Create the trigger for the table which we have created on the corrupted data file


SQL> CREATE OR REPLACE TRIGGER scott.corrupt_trigger
  2    AFTER INSERT ON scott.om
  3    REFERENCING OLD AS p_old NEW AS new_p
  4    FOR EACH ROW
  5  DECLARE
  6    corrupt EXCEPTION;
  7  BEGIN
  IF (dbms_rowid.rowid_block_number(:new_p.rowid)=&blocknumber)
 and (dbms_rowid.rowid_relative_fno(:new_p.rowid)=&filenumber) THEN
  8    9   10       RAISE corrupt;
  END IF;
 11   12  EXCEPTION
 13    WHEN corrupt THEN
 14       RAISE_APPLICATION_ERROR(-20000, 'Corrupt block has been formatted');
 15  END;
 16  /
Enter value for blocknumber: 165660
old   8:   IF (dbms_rowid.rowid_block_number(:new_p.rowid)=&blocknumber)
new   8:   IF (dbms_rowid.rowid_block_number(:new_p.rowid)=165660)
Enter value for filenumber: 352
old   9:  and (dbms_rowid.rowid_relative_fno(:new_p.rowid)=&filenumber) THEN
new   9:  and (dbms_rowid.rowid_relative_fno(:new_p.rowid)=352) THEN

Trigger created.


Step 8

Allocate the space to the newly create table

SYNTAX Select BYTES from dba_free_space where file_id=<file_number> and < corrupted block_number >between block_id and block_id + blocks -1;


SQL> Select BYTES from dba_free_space where file_id=352 and 192000 between block_id and block_id + blocks -1;

     BYTES
----------
 771612672


Make sure the data file is not in auto extend on if it is on then disable it allocate the above query's answer[free_space] to the newly created object


SQL> alter table scott.om
     allocate extent (DATAFILE '/p01/app/PROD/db/apps_st/data/system09.dbf' SIZE 735M);  2

Table altered.

once the table is assigned to the particular data file check the detail's


SQL> select segment_name,tablespace_name from user_segments
      where segment_name='OM' ;

SEGMENT_NAME                   TABLESPACE_NAME
------------------------------ ------------------------------
OM                             SYSTEM

Step 9

Inset values in  to the table  by using the below loop
The data is inserted in to the table for formatting the block


SQL> BEGIN
FOR i IN 1..100000000 LOOP
INSERT /*+ APPEND */ INTO scott.om select i, lpad('REFORMAT',3092, 'R') from dual;
commit ;
END LOOP;
END;
   /

ERROR at line 1:
ORA-20000: Corrupt block has been formatted
ORA-06512: at "SCOTT.CORRUPT_TRIGGER", line 10
ORA-04088: error during execution of trigger 'SCOTT.CORRUPT_TRIGGER'
ORA-06512: at line 4

SQL> commit;

we need to commit the data which is inserted into the table

After committing the data switch log file's

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system checkpoint;

System altered.


Once the data is inserted in to table then check the block corruption

 Step 10


[oraprod@proddb]$ dbv   file = /p01/app/PROD/db/apps_st/data/system09.dbf

DBVERIFY: Release 11.1.0.7.0 - Production on Mon Oct 29 16:29:06 2012

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

DBVERIFY - Verification starting : FILE = /p01/app/PROD/db/apps_st/data/system09.dbf


DBVERIFY - Verification complete

Total Pages Examined         : 97920
Total Pages Processed (Data) : 63873
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 30473
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 1908
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 1666
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 3721899454 (1388.3721899454)


[oraprod@proddb trace]$ rman

Recovery Manager: Release 11.1.0.7.0 - Production on Mon Oct 29 16:30:13 2012

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

RMAN> connect target /

connected to target database: PROD (DBID=209199817)

RMAN> backup datafile 352;

Starting backup at 29-OCT-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=265 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=00352 name=/p01/app/PROD/db/apps_st/data/system09.dbf
channel ORA_DISK_1: starting piece 1 at 29-OCT-12
channel ORA_DISK_1: finished piece 1 at 29-OCT-12
piece handle=/p01/app/PROD/db/tech_st/11.1.0/dbs/ndnovt7p_1_1 tag=TAG20121029T163153 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 29-OCT-12

Starting Control File Autobackup at 29-OCT-12
piece handle=/p01/app/PROD/db/tech_st/11.1.0/dbs/c-209199817-20121029-00 comment=NONE
Finished Control File Autobackup at 29-OCT-12

RMAN> exit

Recovery Manager complete.

[oraprod@proddb ]$ sqlplus / as sysdba
SQL*Plus: Release 11.1.0.7.0 - Production on Mon Oct 29 16:33:17 2012
Copyright (c) 1982, 2008, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from v$database_block_corruption;

no rows selected

Step 10

Drop the table and the trigger which we have created

NOTE

There are some other options which you can try

we can shrink the data file according to the free block's


SQL> Select BYTES from dba_free_space where file_id=352 and 192000 between block_id and block_id + blocks -1;

     BYTES
----------
 771612672

here we around 735 MB of free space and the original data file size is 1500 here we need to  minus the original  size for the of the file from the free space

so it will be 1500-735 =765

Resize the data file to the 765 and check

if you have the backup and the archive log then from the Rman you can issue the below command to recover the blog

blockrecover corruption list;

it will automatically recover the corrupted blocks

Rman Duplicate Database creation with the recovery catalog

Duplicating the database buy using the Rman 

In this environment we have used the fahdb database as the target database and the ORACAT database as the catalog database
and the  fahdb as the auxiliary database

If the target database was in the ASM file system then you have to create the backup in the shared [NFS]  file system  which can be accessed by both the PROD and the Auxiliary database's


[oracat@fahtestdb ~]$ rman

Recovery Manager: Release 11.2.0.2.0 - Production on Tue Jul 17 22:49:57 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect target sys/<password>@fahdb

connected to target database: FAHDB (DBID=728861041)

RMAN> connect catalog <username>/<password>@ORACAT

connected to recovery catalog database

RMAN> connect auxiliary sys/<password>@fahdb_test

connected to auxiliary database: FAHDB (not mounted)

This command can be given in the single line also  #   rman connect target <usernane>/password@SID catalog <username>/password@SID Auxiliary <username>/password@SID

The auxiliary database should be in the no mount stage and all the database server must Tns ping each other and you should make the Tns entry of catalog database And the PROD and the auxiliary  database in all three database's and then in the 11g now there was a new future introduced for the active database cloning just you can clone the active database to the new environment but here we are using the traditional method od writing the script to clone in the active cloning if you need to have the same name from the test and the PROD database then it will not allow.. the auxiliary database should be in the different name



For this we need to prepare the script and using the simple command we can do that 

Recovery Manager: Release 11.2.0.2.0 - Production on Wed Jul 11 17:30:49 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: FAHDB (DBID=728861041)
connected to recovery catalog database
connected to auxiliary database: FAHDB (not mounted)

RMAN> @omrman.sql

RMAN> run
2> {
3>
4> set newname for datafile    1        to      '/u06/app/oracle/oradata/datafile/system.dbf';
5> set newname for datafile    2        to       '/u06/app/oracle/oradata/datafile/sysaux.dbf';
6> set newname for datafile    3        to      '/u06/app/oracle/oradata/datafile/fusion_undots.dbf';
7> set newname for datafile    4        to      '/u06/app/oracle/oradata/datafile/fusion_ts_tx_data01.dbf';
8> set newname for datafile    5        to      '/u06/app/oracle/oradata/datafile/fusion_ts_tx_data02.dbf';
9> set newname for datafile    6        to      '/u06/app/oracle/oradata/datafile/fusion_ts_tx_data03.dbf';
10> set newname for datafile    7      to      '/u06/app/oracle/oradata/datafile/fusion_ts_tx_data04.dbf';
11> set newname for datafile    8       to  '/u06/app/oracle/oradata/datafile/fusion_ts_tx_data05.dbf';
12> set newname for datafile    9        to  '/u06/app/oracle/oradata/datafile/fusion_ts_tx_data06.dbf';
13> set newname for datafile    10      to      '/u06/app/oracle/oradata/datafile/fusion_ts_tx_data07.dbf';
14> set newname for datafile    11      to      '/u06/app/oracle/oradata/datafile/fusion_ts_tx_data08.dbf';
15> set newname for datafile    12      to      '/u06/app/oracle/oradata/datafile/fusion_ts_tx_data09.dbf';
16> set newname for datafile    13      to      '/u06/app/oracle/oradata/datafile/fusion_ts_tx_data10.dbf';
17> set newname for datafile    14      to      '/u06/app/oracle/oradata/datafile/fusion_ts_tx_idx01.dbf';
18> set newname for datafile    15      to      '/u06/app/oracle/oradata/datafile/fusion_ts_tx_idx02.dbf';
19> set newname for datafile    16      to      '/u06/app/oracle/oradata/datafile/fusion_ts_tx_idx03.dbf';
20> set newname for datafile    17      to       '/u06/app/oracle/oradata/datafile/fusion_ts_tools.dbf';
21> set newname for datafile    18      to      '/u06/app/oracle/oradata/datafile/search_index.dbf';
22> set newname for datafile    19  to   '/u06/app/oracle/oradata/datafile/fusion_ias_orasdpm_aq.dbf';
23> set newname for datafile    20      to      '/u06/app/oracle/oradata/datafile/fusion_ts_archive.dbf';
24> set newname for datafile    21      to      '/u06/app/oracle/oradata/datafile/fusion_ts_dq.dbf';
25> set newname for datafile    22  to  '/u06/app/oracle/oradata/datafile/fusion_ts_interface.dbf';
26> set newname for datafile    23  to  '/u06/app/oracle/oradata/datafile/fusion_ts_nologging.dbf';
27> set newname for datafile    24      to      '/u06/app/oracle/oradata/datafile/biacmts.dbf';
28> set newname for datafile    25      to      '/u06/app/oracle/oradata/datafile/fusion_ts_seed.dbf';
29> set newname for datafile    26      to      '/u06/app/oracle/oradata/datafile/fusion_dyn_ts.dbf';
30> set newname for datafile    27      to      '/u06/app/oracle/oradata/datafile/fusion_ts_queues.dbf';
31> set newname for datafile    28      to      '/u06/app/oracle/oradata/datafile/fusion_ts_aq.dbf';
32> set newname for datafile    29      to      '/u06/app/oracle/oradata/datafile/search_data.dbf';
33> set newname for datafile    30      to      '/u06/app/oracle/oradata/datafile/fusion_ts_media.dbf';
34> set newname for datafile    31      to      '/u06/app/oracle/oradata/datafile/fusion_ts_summary.dbf';
35>
36>
37>
38> duplicate target database to 'fahdb' --- if you are not mentioning the time then it will restore from the latest backup
39> LOGFILE
40> GROUP 1 ('/u06/app/oracle/oradata/redo01.log','/u06/app/oracle/oradata/redo01a.log') SIZE 2048M ,
41> GROUP 2 ('/u06/app/oracle/oradata/redo02.log','/u06/app/oracle/oradata/redo02a.log') SIZE 2048M ,
42> GROUP 3 ('/u06/app/oracle/oradata/redo03.log','/u06/app/oracle/oradata/redo03a.log') SIZE 2048M ,
43> GROUP 4 ('/u06/app/oracle/oradata/redo04.log','/u06/app/oracle/oradata/redo04a.log') SIZE 2048M ;
44>
45> }
executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting Duplicate Db at 11-JUL-12
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=2828 device type=DISK

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''FAHDB'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''FAHDB'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile;
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''FAHDB'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''FAHDB'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area   19241058304 bytes

Fixed Size                     2234616 bytes
Variable Size               3087009544 bytes
Database Buffers           16106127360 bytes
Redo Buffers                  45686784 bytes

Starting restore at 11-JUL-12
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=2828 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /shared/oracle/c-728861041-20120711-01
channel ORA_AUX_DISK_1: piece handle=/shared/oracle/c-728861041-20120711-01 tag=TAG20120711T172737
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/u06/app/oracle/oradata/datafile/control01.ctl
output file name=/u06/app/oracle/oradata/datafile/control02.ctl
Finished restore at 11-JUL-12

database mounted

contents of Memory Script:
{
   set until scn  15574861794;
   set newname for datafile  1 to
 "/u06/app/oracle/oradata/datafile/system.dbf";
   set newname for datafile  2 to
 "/u06/app/oracle/oradata/datafile/sysaux.dbf";
   set newname for datafile  3 to
 "/u06/app/oracle/oradata/datafile/fusion_undots.dbf";
   set newname for datafile  4 to
 "/u06/app/oracle/oradata/datafile/fusion_ts_tx_data01.dbf";
   set newname for datafile  5 to
 "/u06/app/oracle/oradata/datafile/fusion_ts_tx_data02.dbf";
   set newname for datafile  6 to
 "/u06/app/oracle/oradata/datafile/fusion_ts_tx_data03.dbf";
   set newname for datafile  7 to
 "/u06/app/oracle/oradata/datafile/fusion_ts_tx_data04.dbf";
   set newname for datafile  8 to
 "/u06/app/oracle/oradata/datafile/fusion_ts_tx_data05.dbf";
   set newname for datafile  9 to
 "/u06/app/oracle/oradata/datafile/fusion_ts_tx_data06.dbf";
   set newname for datafile  10 to
 "/u06/app/oracle/oradata/datafile/fusion_ts_tx_data07.dbf";
   set newname for datafile  11 to
 "/u06/app/oracle/oradata/datafile/fusion_ts_tx_data08.dbf";
   set newname for datafile  12 to
 "/u06/app/oracle/oradata/datafile/fusion_ts_tx_data09.dbf";
   set newname for datafile  13 to
 "/u06/app/oracle/oradata/datafile/fusion_ts_tx_data10.dbf";
   set newname for datafile  14 to
 "/u06/app/oracle/oradata/datafile/fusion_ts_tx_idx01.dbf";
   set newname for datafile  15 to
 "/u06/app/oracle/oradata/datafile/fusion_ts_tx_idx02.dbf";
   set newname for datafile  16 to
 "/u06/app/oracle/oradata/datafile/fusion_ts_tx_idx03.dbf";
   set newname for datafile  17 to
 "/u06/app/oracle/oradata/datafile/fusion_ts_tools.dbf";
   set newname for datafile  18 to
 "/u06/app/oracle/oradata/datafile/search_index.dbf";
   set newname for datafile  19 to
 "/u06/app/oracle/oradata/datafile/fusion_ias_orasdpm_aq.dbf";
   set newname for datafile  20 to
 "/u06/app/oracle/oradata/datafile/fusion_ts_archive.dbf";
   set newname for datafile  21 to
 "/u06/app/oracle/oradata/datafile/fusion_ts_dq.dbf";
   set newname for datafile  22 to
 "/u06/app/oracle/oradata/datafile/fusion_ts_interface.dbf";
   set newname for datafile  23 to
 "/u06/app/oracle/oradata/datafile/fusion_ts_nologging.dbf";
   set newname for datafile  24 to
 "/u06/app/oracle/oradata/datafile/biacmts.dbf";
   set newname for datafile  25 to
 "/u06/app/oracle/oradata/datafile/fusion_ts_seed.dbf";
   set newname for datafile  26 to
 "/u06/app/oracle/oradata/datafile/fusion_dyn_ts.dbf";
   set newname for datafile  27 to
 "/u06/app/oracle/oradata/datafile/fusion_ts_queues.dbf";
   set newname for datafile  28 to
 "/u06/app/oracle/oradata/datafile/fusion_ts_aq.dbf";
   set newname for datafile  29 to
 "/u06/app/oracle/oradata/datafile/search_data.dbf";
   set newname for datafile  30 to
 "/u06/app/oracle/oradata/datafile/fusion_ts_media.dbf";
   set newname for datafile  31 to
 "/u06/app/oracle/oradata/datafile/fusion_ts_summary.dbf";
   restore
   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

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 11-JUL-12
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u06/app/oracle/oradata/datafile/system.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u06/app/oracle/oradata/datafile/sysaux.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u06/app/oracle/oradata/datafile/fusion_undots.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u06/app/oracle/oradata/datafile/fusion_ts_tx_data01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u06/app/oracle/oradata/datafile/fusion_ts_tx_data02.dbf
channel ORA_AUX_DISK_1: restoring datafile 00006 to /u06/app/oracle/oradata/datafile/fusion_ts_tx_data03.dbf
channel ORA_AUX_DISK_1: restoring datafile 00007 to /u06/app/oracle/oradata/datafile/fusion_ts_tx_data04.dbf
channel ORA_AUX_DISK_1: restoring datafile 00008 to /u06/app/oracle/oradata/datafile/fusion_ts_tx_data05.dbf
channel ORA_AUX_DISK_1: restoring datafile 00009 to /u06/app/oracle/oradata/datafile/fusion_ts_tx_data06.dbf
channel ORA_AUX_DISK_1: restoring datafile 00010 to /u06/app/oracle/oradata/datafile/fusion_ts_tx_data07.dbf
channel ORA_AUX_DISK_1: restoring datafile 00011 to /u06/app/oracle/oradata/datafile/fusion_ts_tx_data08.dbf
channel ORA_AUX_DISK_1: restoring datafile 00012 to /u06/app/oracle/oradata/datafile/fusion_ts_tx_data09.dbf
channel ORA_AUX_DISK_1: restoring datafile 00013 to /u06/app/oracle/oradata/datafile/fusion_ts_tx_data10.dbf
channel ORA_AUX_DISK_1: restoring datafile 00014 to /u06/app/oracle/oradata/datafile/fusion_ts_tx_idx01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00015 to /u06/app/oracle/oradata/datafile/fusion_ts_tx_idx02.dbf
channel ORA_AUX_DISK_1: restoring datafile 00016 to /u06/app/oracle/oradata/datafile/fusion_ts_tx_idx03.dbf
channel ORA_AUX_DISK_1: restoring datafile 00017 to /u06/app/oracle/oradata/datafile/fusion_ts_tools.dbf
channel ORA_AUX_DISK_1: restoring datafile 00018 to /u06/app/oracle/oradata/datafile/search_index.dbf
channel ORA_AUX_DISK_1: restoring datafile 00019 to /u06/app/oracle/oradata/datafile/fusion_ias_orasdpm_aq.dbf
channel ORA_AUX_DISK_1: restoring datafile 00020 to /u06/app/oracle/oradata/datafile/fusion_ts_archive.dbf
channel ORA_AUX_DISK_1: restoring datafile 00021 to /u06/app/oracle/oradata/datafile/fusion_ts_dq.dbf
channel ORA_AUX_DISK_1: restoring datafile 00022 to /u06/app/oracle/oradata/datafile/fusion_ts_interface.dbf
channel ORA_AUX_DISK_1: restoring datafile 00023 to /u06/app/oracle/oradata/datafile/fusion_ts_nologging.dbf
channel ORA_AUX_DISK_1: restoring datafile 00024 to /u06/app/oracle/oradata/datafile/biacmts.dbf
channel ORA_AUX_DISK_1: restoring datafile 00025 to /u06/app/oracle/oradata/datafile/fusion_ts_seed.dbf
channel ORA_AUX_DISK_1: restoring datafile 00026 to /u06/app/oracle/oradata/datafile/fusion_dyn_ts.dbf
channel ORA_AUX_DISK_1: restoring datafile 00027 to /u06/app/oracle/oradata/datafile/fusion_ts_queues.dbf
channel ORA_AUX_DISK_1: restoring datafile 00028 to /u06/app/oracle/oradata/datafile/fusion_ts_aq.dbf
channel ORA_AUX_DISK_1: restoring datafile 00029 to /u06/app/oracle/oradata/datafile/search_data.dbf
channel ORA_AUX_DISK_1: restoring datafile 00030 to /u06/app/oracle/oradata/datafile/fusion_ts_media.dbf
channel ORA_AUX_DISK_1: restoring datafile 00031 to /u06/app/oracle/oradata/datafile/fusion_ts_summary.dbf
channel ORA_AUX_DISK_1: reading from backup piece /shared/oracle/1jnfr8su_1_1
channel ORA_AUX_DISK_1: piece handle=/shared/oracle/1jnfr8su_1_1 tag=TAG20120711T165347
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 01:03:25
Finished restore at 11-JUL-12

contents of Memory Script:

{

   switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy RECID=33 STAMP=788316450 file name=/u06/app/oracle/oradata/datafile/system.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=34 STAMP=788316450 file name=/u06/app/oracle/oradata/datafile/sysaux.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=35 STAMP=788316450 file name=/u06/app/oracle/oradata/datafile/fusion_undots.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=36 STAMP=788316450 file name=/u06/app/oracle/oradata/datafile/fusion_ts_tx_data01.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=37 STAMP=788316451 file name=/u06/app/oracle/oradata/datafile/fusion_ts_tx_data02.dbf

datafile 6 switched to datafile copy

input datafile copy RECID=38 STAMP=788316451 file name=/u06/app/oracle/oradata/datafile/fusion_ts_tx_data03.dbf

datafile 7 switched to datafile copy

input datafile copy RECID=39 STAMP=788316451 file name=/u06/app/oracle/oradata/datafile/fusion_ts_tx_data04.dbf

datafile 8 switched to datafile copy

input datafile copy RECID=40 STAMP=788316451 file name=/u06/app/oracle/oradata/datafile/fusion_ts_tx_data05.dbf

datafile 9 switched to datafile copy

input datafile copy RECID=41 STAMP=788316451 file name=/u06/app/oracle/oradata/datafile/fusion_ts_tx_data06.dbf

datafile 10 switched to datafile copy

input datafile copy RECID=42 STAMP=788316451 file name=/u06/app/oracle/oradata/datafile/fusion_ts_tx_data07.dbf

datafile 11 switched to datafile copy

input datafile copy RECID=43 STAMP=788316452 file name=/u06/app/oracle/oradata/datafile/fusion_ts_tx_data08.dbf

datafile 12 switched to datafile copy

input datafile copy RECID=44 STAMP=788316452 file name=/u06/app/oracle/oradata/datafile/fusion_ts_tx_data09.dbf

datafile 13 switched to datafile copy

input datafile copy RECID=45 STAMP=788316452 file name=/u06/app/oracle/oradata/datafile/fusion_ts_tx_data10.dbf

datafile 14 switched to datafile copy

input datafile copy RECID=46 STAMP=788316452 file name=/u06/app/oracle/oradata/datafile/fusion_ts_tx_idx01.dbf

datafile 15 switched to datafile copy

input datafile copy RECID=47 STAMP=788316452 file name=/u06/app/oracle/oradata/datafile/fusion_ts_tx_idx02.dbf

datafile 16 switched to datafile copy

input datafile copy RECID=48 STAMP=788316452 file name=/u06/app/oracle/oradata/datafile/fusion_ts_tx_idx03.dbf

datafile 17 switched to datafile copy

input datafile copy RECID=49 STAMP=788316453 file name=/u06/app/oracle/oradata/datafile/fusion_ts_tools.dbf

datafile 18 switched to datafile copy

input datafile copy RECID=50 STAMP=788316453 file name=/u06/app/oracle/oradata/datafile/search_index.dbf

datafile 19 switched to datafile copy

input datafile copy RECID=51 STAMP=788316453 file name=/u06/app/oracle/oradata/datafile/fusion_ias_orasdpm_aq.dbf

datafile 20 switched to datafile copy

input datafile copy RECID=52 STAMP=788316453 file name=/u06/app/oracle/oradata/datafile/fusion_ts_archive.dbf

datafile 21 switched to datafile copy

input datafile copy RECID=53 STAMP=788316453 file name=/u06/app/oracle/oradata/datafile/fusion_ts_dq.dbf

datafile 22 switched to datafile copy

input datafile copy RECID=54 STAMP=788316453 file name=/u06/app/oracle/oradata/datafile/fusion_ts_interface.dbf

datafile 23 switched to datafile copy

input datafile copy RECID=55 STAMP=788316454 file name=/u06/app/oracle/oradata/datafile/fusion_ts_nologging.dbf

datafile 24 switched to datafile copy

input datafile copy RECID=56 STAMP=788316454 file name=/u06/app/oracle/oradata/datafile/biacmts.dbf

datafile 25 switched to datafile copy

input datafile copy RECID=57 STAMP=788316454 file name=/u06/app/oracle/oradata/datafile/fusion_ts_seed.dbf

datafile 26 switched to datafile copy

input datafile copy RECID=58 STAMP=788316454 file name=/u06/app/oracle/oradata/datafile/fusion_dyn_ts.dbf

datafile 27 switched to datafile copy

input datafile copy RECID=59 STAMP=788316454 file name=/u06/app/oracle/oradata/datafile/fusion_ts_queues.dbf

datafile 28 switched to datafile copy

input datafile copy RECID=60 STAMP=788316454 file name=/u06/app/oracle/oradata/datafile/fusion_ts_aq.dbf

datafile 29 switched to datafile copy

input datafile copy RECID=61 STAMP=788316455 file name=/u06/app/oracle/oradata/datafile/search_data.dbf

datafile 30 switched to datafile copy

input datafile copy RECID=62 STAMP=788316455 file name=/u06/app/oracle/oradata/datafile/fusion_ts_media.dbf

datafile 31 switched to datafile copy

input datafile copy RECID=63 STAMP=788316455 file name=/u06/app/oracle/oradata/datafile/fusion_ts_summary.dbf

contents of Memory Script:

{

   set until scn  15560381088;

   recover

   clone database

    delete archivelog

   ;

}

executing Memory Script

executing command: SET until clause

Starting recover at 11-JUL-12

using channel ORA_AUX_DISK_1

starting media recovery

Oracle instance started

Total System Global Area   19241058304 bytes

Fixed Size                     2234616 bytes

Variable Size               3087009544 bytes

Database Buffers           16106127360 bytes

Redo Buffers                  45686784 bytes

contents of Memory Script:

{

   sql clone "alter system set  db_name =

 ''FAHDB'' comment=

 ''Reset to original value by RMAN'' scope=spfile";

   sql clone "alter system reset  db_unique_name scope=spfile";

   shutdown clone immediate;

}

executing Memory Script

sql statement: alter system set  db_name =  ''FAHDB'' comment= ''Reset to original value by RMAN'' scope=spfile

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down

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

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

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

RMAN-03002: failure of Duplicate Db command at 07/11/2012 00:47:55

RMAN-05501: aborting duplication of target database

RMAN-03015: error occurred in stored script Memory Script

RMAN-06053: unable to perform media recovery because of missing log

RMAN-06025: no backup of archived log for thread 1 with sequence 25 and starting SCN of 15560381085 found to restore

RMAN-06025: no backup of archived log for thread 1 with sequence 24 and starting SCN of 15560381071 found to restore

RMAN-06025: no backup of archived log for thread 1 with sequence 23 and starting SCN of 15560381058 found to restore

RMAN-06025: no backup of archived log for thread 1 with sequence 22 and starting SCN of 15560381055 found to restore

RMAN-06025: no backup of archived log for thread 1 with sequence 21 and starting SCN of 15560268398 found to restore

RMAN>

RMAN> **end-of-file**

so if you are getting the error message for the archive log then you can  copy from the prod to the  auxiliary archive log location

[oratest@fahtestapp ~]$ sqlplus

SQL*Plus: Release 11.2.0.2.0 Production on Wed Jul 11 01:04:27 2012

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

Enter user-name: sys as sysdba

Enter password:

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining

and Real Application Testing options

SQL> alter database mount;

Database altered.

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-19838: Cannot use this control file to open database

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-00279: change 15560374524 generated at 07/10/2012 18:04:15 needed for

thread 1

ORA-00289: suggestion :

/u06/app/oracle/fast_recovery_area/FAHDB/archivelog/2012_07_11/o1_mf_1_21_%u_.ar

c

ORA-00280: change 15560374524 for thread 1 is in sequence #21

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

auto

ORA-00308: cannot open archived log

'/u06/app/oracle/fast_recovery_area/FAHDB/archivelog/2012_07_11/o1_mf_1_21_%u_.a

rc'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

ORA-00308: cannot open archived log

'/u06/app/oracle/fast_recovery_area/FAHDB/archivelog/2012_07_11/o1_mf_1_21_%u_.a

rc'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

[oratest@fahtestapp ~]$ mkdir /u06/app/oracle/fast_recovery_area/FAHDB/archivelog/2012_07_11/ -p

Manually  copy the files from the prod to the specific directory and recover the database 

SQL> recover database using backup controlfile;

ORA-00279: change 15560374524 generated at 07/10/2012 18:04:15 needed for

thread 1

ORA-00289: suggestion :

/u06/app/oracle/fast_recovery_area/FAHDB/archivelog/2012_07_11/thread_1_seq_21.3

53.788294345

ORA-00280: change 15560374524 for thread 1 is in sequence #21

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

auto

ORA-00279: change 15560381055 generated at 07/10/2012 18:39:05 needed for

thread 1

ORA-00289: suggestion :

/u06/app/oracle/fast_recovery_area/FAHDB/archivelog/2012_07_11/thread_1_seq_22.3

54.788294347

ORA-00280: change 15560381055 for thread 1 is in sequence #22

ORA-00278: log file

'/u06/app/oracle/fast_recovery_area/FAHDB/archivelog/2012_07_11/thread_1_seq_21.

353.788294345' no longer needed for this recovery

ORA-00279: change 15560381058 generated at 07/10/2012 18:39:07 needed for

thread 1

ORA-00289: suggestion :

/u06/app/oracle/fast_recovery_area/FAHDB/archivelog/2012_07_11/thread_1_seq_23.3

55.788294351

ORA-00280: change 15560381058 for thread 1 is in sequence #23

ORA-00278: log file

'/u06/app/oracle/fast_recovery_area/FAHDB/archivelog/2012_07_11/thread_1_seq_22.

354.788294347' no longer needed for this recovery

ORA-00279: change 15560381071 generated at 07/10/2012 18:39:10 needed for

thread 1

ORA-00289: suggestion :

/u06/app/oracle/fast_recovery_area/FAHDB/archivelog/2012_07_11/thread_1_seq_24.3

56.788294355

ORA-00280: change 15560381071 for thread 1 is in sequence #24

ORA-00278: log file

'/u06/app/oracle/fast_recovery_area/FAHDB/archivelog/2012_07_11/thread_1_seq_23.

355.788294351' no longer needed for this recovery

ORA-00279: change 15560381085 generated at 07/10/2012 18:39:14 needed for

thread 1

ORA-00289: suggestion :

/u06/app/oracle/fast_recovery_area/FAHDB/archivelog/2012_07_11/thread_1_seq_25.3

57.788294359

ORA-00280: change 15560381085 for thread 1 is in sequence #25

ORA-00278: log file

'/u06/app/oracle/fast_recovery_area/FAHDB/archivelog/2012_07_11/thread_1_seq_24.

again login with the database and mount the database and recreate the Control file with the proper locations of the files and open the database

SQL> alter database backup controlfile to trace as '/shared/oracle/controfile';


open the Trace file of the control file and find the script for creating the control file and you can recreate it by using the script in it 

SQL> startup nomount

ORACLE instance started.

Total System Global Area 1.9241E+10 bytes

Fixed Size                  2234616 bytes

Variable Size            3087009544 bytes

Database Buffers         1.6106E+10 bytes

Redo Buffers               45686784 bytes

SQL> CREATE CONTROLFILE REUSE DATABASE "FAHDB" RESETLOGS  ARCHIVELOG

  2      MAXLOGFILES 16

  3      MAXLOGMEMBERS 3

  4      MAXDATAFILES 100

  5      MAXINSTANCES 8

  6      MAXLOGHISTORY 292

  7  LOGFILE

  8    GROUP 1 (

  9      '/u06/app/oracle/oradata/redo01.log',

 10      '/u06/app/oracle/oradata/redo01a.log'

 11    ) SIZE 2048M BLOCKSIZE 512,

 12    GROUP 2 (

 13      '/u06/app/oracle/oradata/redo02.log',

 14      '/u06/app/oracle/oradata/redo02a.log'

 15    ) SIZE 2048M BLOCKSIZE 512,

 16    GROUP 3 (

    '/u06/app/oracle/oradata/redo03.log',

 17   18      '/u06/app/oracle/oradata/redo03a.log'

 19    ) SIZE 2048M BLOCKSIZE 512,

 20    GROUP 4 (

 21      '/u06/app/oracle/oradata/redo04.log',

 22      '/u06/app/oracle/oradata/redo04a.log'

 23    ) SIZE 2048M BLOCKSIZE 512

 24  -- STANDBY LOGFILE

 25  DATAFILE

 26    '/u06/app/oracle/oradata/datafile/system.dbf',

  '/u06/app/oracle/oradata/datafile/sysaux.dbf',

 27   28    '/u06/app/oracle/oradata/datafile/fusion_undots.dbf',

 29    '/u06/app/oracle/oradata/datafile/fusion_ts_tx_data01.dbf',

 30    '/u06/app/oracle/oradata/datafile/fusion_ts_tx_data02.dbf',

 31    '/u06/app/oracle/oradata/datafile/fusion_ts_tx_data03.dbf',

 32    '/u06/app/oracle/oradata/datafile/fusion_ts_tx_data04.dbf',

 33    '/u06/app/oracle/oradata/datafile/fusion_ts_tx_data05.dbf',

 34    '/u06/app/oracle/oradata/datafile/fusion_ts_tx_data06.dbf',

 35    '/u06/app/oracle/oradata/datafile/fusion_ts_tx_data07.dbf',

 36    '/u06/app/oracle/oradata/datafile/fusion_ts_tx_data08.dbf',

 37    '/u06/app/oracle/oradata/datafile/fusion_ts_tx_data09.dbf',

 38    '/u06/app/oracle/oradata/datafile/fusion_ts_tx_data10.dbf',

  '/u06/app/oracle/oradata/datafile/fusion_ts_tx_idx01.dbf',

  '/u06/app/oracle/oradata/datafile/fusion_ts_tx_idx02.dbf',

  '/u06/app/oracle/oradata/datafile/fusion_ts_tx_idx03.dbf',

 39   40   41   42    '/u06/app/oracle/oradata/datafile/fusion_ts_tools.dbf',

 43    '/u06/app/oracle/oradata/datafile/search_index.dbf',

 44    '/u06/app/oracle/oradata/datafile/fusion_ias_orasdpm_aq.dbf',

 45    '/u06/app/oracle/oradata/datafile/fusion_ts_archive.dbf',

 46    '/u06/app/oracle/oradata/datafile/fusion_ts_dq.dbf',

 47    '/u06/app/oracle/oradata/datafile/fusion_ts_interface.dbf',

 48    '/u06/app/oracle/oradata/datafile/fusion_ts_nologging.dbf',

 49    '/u06/app/oracle/oradata/datafile/biacmts.dbf',

 50    '/u06/app/oracle/oradata/datafile/fusion_ts_seed.dbf',

 51    '/u06/app/oracle/oradata/datafile/fusion_dyn_ts.dbf',

  '/u06/app/oracle/oradata/datafile/fusion_ts_queues.dbf',

 52   53    '/u06/app/oracle/oradata/datafile/fusion_ts_aq.dbf',

 54    '/u06/app/oracle/oradata/datafile/search_data.dbf',

 55    '/u06/app/oracle/oradata/datafile/fusion_ts_media.dbf',

 56    '/u06/app/oracle/oradata/datafile/fusion_ts_summary.dbf'

 57  CHARACTER SET AL32UTF8

;

 58

Control file created.

SQL> alter database open resetlogs;

Database altered.

And After restoring the backup it will go through the recovery process automatically and open the database if you get any  error after restore of the database then you have to manually recover the database for recovering the database manual  by using the archive log
and in the process we need to create the new control file by taking the trace of the existing control file ..

Note: recovering until SCN is not advisable because of the data files the SCN may not changed during the backup and it may give the error messages like

Oracle instance shut down

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

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

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

RMAN-03002: failure of Duplicate Db command at 07/10/2012 21:27:11

RMAN-05501: aborting duplication of target database

RMAN-03015: error occurred in stored script Memory Script

RMAN-06026: some targets not found - aborting restore

RMAN-06023: no backup or copy of datafile 17 found to restore

and it you check the backup you can find the database copies also in it but the SCN number of the particular datafile may be lower than you specified and it will go to the previous backup if present to read it if there is no backup present then it will give the error message

so for cloning it is advisable with the UNTIL TIME with your own date format's