Tackle RMAN Clone or Refresh Failures

1. Login to <TARGET_DB>:

Check the status of the database, if the database is in mount state then the target database refresh may be failed due to missing archivelogs.

select name,open_mode from v$database;

If the database is not mounted then check with backup team if the backups for <TARGET_DB> refresh are in sync. Then try re running the refresh job.

If the database is in mount state then the TARGET DATABASE refresh may be failed due to missing archivelogs , check for the archivelogs sequence which need to be applied:

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 10678792531688 generated at 01/18/2012 19:52:12 needed for
thread 1
ORA-00289: suggestion : /ORACLE/<TARGET_DB>/arch/<TARGET_DB>_1_238367_544811370.log
ORA-00280: change 10678792531688 for thread 1 is in sequence #238367

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

2. On SOURCE_DB database server:

Login to rman

$rman target /

Recovery Manager: Release 11.1.0.7.0 - Production on Wed Jan 18 23:47:30 2012

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

connected to target database: SOURCE_DB (DBID=3843628774)

RMAN> list backup of archivelog from sequence 239737;

List of Backup Sets

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

BS Key  Size       Device Type Elapsed Time Completion Time

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

124369  80.41M     DISK        00:00:17     18-JAN-12

        BP Key: 192593   Status: AVAILABLE  Compressed: YES  Tag: TAG20120118T195538

        Piece Name: /ORACLE/<Custom Stage Location>/SOURCE_DB/archivelog_SOURCE_DB_etcdpr1__backup_1201181955_lin13kdr_1_1

  List of Archived Logs in backup set 124369

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

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

  1    238367  10678792411202 18-JAN-12 10678792775133 18-JAN-12

RMAN> exit

Recovery Manager complete.

[SOURCE_DB]/ORACLE/SOURCE_DB/arch>

$cd /ORACLE/<Custom Stage Location>/SOURCE_DB/

[SOURCE_DB]/ORACLE/<Custom Stage Location>/SOURCE_DB>

3. Copy the rman backup archivelog file to /ORACLE/<CUSTOM STAGE LOCATION>/patches/11g

$cp archivelog_SOURCE_DB_etcdpr1__backup_1201181955_lin13kdr_1_1 /ORACLE/<CUSTOM STAGE LOCATION>/patches/11g

[SOURCE_DB]/ORACLE/<Custom Stage Location>/SOURCE_DB>

4. Login to <TARGET_DB>:

pbr - ora<Custom Stage Location>

select <TARGET_DB>

[edcdqs1 : <TARGET_DB> : 11g]  /ORACLE/<Custom Stage Location>/SOURCE_DB>

$cd $PATCH

5. Connect to rman:

$rman target /

Recovery Manager: Release 11.1.0.7.0 - Production on Wed Jan 18 23:52:03 2012

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

connected to target database: <TARGET_DB> (DBID=3843628774, not open)

6. Add the backup piece to catalog:

RMAN> catalog backuppiece '/ORACLE/<CUSTOM STAGE LOCATION>/patches/11g/archivelog_SOURCE_DB_etcdpr1__backup_1201181955_lin13kdr_1_1';

using target database control file instead of recovery catalog

cataloged backup piece

backup piece handle=/ORACLE/<CUSTOM STAGE LOCATION>/patches/11g/archivelog_SOURCE_DB_etcdpr1__backup_1201181955_lin13kdr_1_1 RECID=1 STAMP=772933957

7. Restore archivelog from backup:

RMAN> restore archivelog sequence 238367;

Starting restore at 18-JAN-12

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=2192 device type=DISK

channel ORA_DISK_1: starting archived log restore to default destination

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=238367

channel ORA_DISK_1: reading from backup piece /ORACLE/<CUSTOM STAGE LOCATION>/patches/11g/archivelog_SOURCE_DB_etcdpr1__backup_1201181955_lin13kdr_1_1

channel ORA_DISK_1: piece handle=/ORACLE/<CUSTOM STAGE LOCATION>/patches/11g/archivelog_SOURCE_DB_etcdpr1__backup_1201181955_lin13kdr_1_1 tag=TAG20120118T195538

channel ORA_DISK_1: restored backup piece 1

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

Finished restore at 18-JAN-12

8. Recover the database (give the name of archivelog file including the path):

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 10678792531688 generated at 01/18/2012 19:52:12 needed for
thread 1
ORA-00289: suggestion : /ORACLE/<TARGET_DB>/arch/<TARGET_DB>_1_238367_544811370.log
ORA-00280: change 10678792531688 for thread 1 is in sequence #238367

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/ORACLE/<TARGET_DB>/arch/<TARGET_DB>_1_238367_544811370.log
ORA-00279: change 10678792775133 generated at 01/18/2012 19:55:20 needed for
thread 1
ORA-00289: suggestion : /ORACLE/<TARGET_DB>/arch/<TARGET_DB>_1_238368_544811370.log
ORA-00280: change 10678792775133 for thread 1 is in sequence #238368
ORA-00278: log file '/ORACLE/<TARGET_DB>/arch/<TARGET_DB>_1_238367_544811370.log' no
longer needed for this recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/ORACLE/<TARGET_DB>/arch/<TARGET_DB>_1_238368_544811370.log
ORA-00279: change 10678792976709 generated at 01/18/2012 19:59:34 needed for
thread 1
ORA-00289: suggestion : /ORACLE/<TARGET_DB>/arch/<TARGET_DB>_1_238369_544811370.log
ORA-00280: change 10678792976709 for thread 1 is in sequence #238369
ORA-00278: log file '/ORACLE/<TARGET_DB>/arch/<TARGET_DB>_1_238368_544811370.log' no
longer needed for this recovery

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.

9. Open the database using resetlogs option:

SQL> alter database open resetlogs;

10. Once database is open, add tempfiles to TEMP Tablespace[Akash1] :

<Custom Steps -- Please create Temp files manually according to your environment.>

cd /ORACLE/<TARGET_DB>/admin/create

sqlplus "/as sysdba"

@add_tempfiles.sql

ALTER TABLESPACE TEMP ADD TEMPFILE '/ORACLE/<TARGET_DB>/system/temp06.dbf'

     SIZE 4096M REUSE AUTOEXTEND ON NEXT 33554432  MAXSIZE 4096M;

ALTER TABLESPACE TEMP ADD TEMPFILE '/ORACLE/<TARGET_DB>/system/temp05.dbf'

     SIZE 4096M REUSE AUTOEXTEND ON NEXT 33554432  MAXSIZE 4096M;

ALTER TABLESPACE TEMP ADD TEMPFILE '/ORACLE/<TARGET_DB>/system/temp01.dbf'

     SIZE 4096M REUSE AUTOEXTEND ON NEXT 134217728  MAXSIZE 8192M;

ALTER TABLESPACE TEMP ADD TEMPFILE '/ORACLE/<TARGET_DB>/system/temp02.dbf'

     SIZE 4096M REUSE AUTOEXTEND ON NEXT 134217728  MAXSIZE 8192M;

ALTER TABLESPACE TEMP ADD TEMPFILE '/ORACLE/<TARGET_DB>/system/temp03.dbf'

     SIZE 4096M REUSE AUTOEXTEND ON NEXT 134217728  MAXSIZE 4096M;

ALTER TABLESPACE TEMP ADD TEMPFILE '/ORACLE/<TARGET_DB>/system/temp04.dbf'

     SIZE 4096M REUSE AUTOEXTEND ON NEXT 33554432  MAXSIZE 4096M;

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

Author - Akash Pramanik

Mail - akash007.pramanik@gmail.com

#NOTE - Any one may copy and use for reference and training purpose. Please take care that this is a generic document. Please contact me for applying into any production environment, for environment specific Documents.

---

 [Akash1]This is a custom step. Please create Temp files manually according to your environment.