RMAN-06136: Oracle error from auxiliary database: ORA-01503: CREATE CONTROLFILE failed

 Dear All,

In this post i am going to show you RMAN Duplicate database clone errors fixup on Oracle database 19C .

while im doing RAC to Non RAC clone i faced this error on Target server .

[oracle@uatdb01 ~]$ rman auxiliary /

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Mar 6 09:06:18 2024

Version 19.22.0.0.0

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

connected to auxiliary database: UATDB (not mounted)

RMAN> duplicate database to 'UATDB' backup location '/u01/Backup' nofilenamecheck;

Starting Duplicate Db at 06-MAR-24

searching for database ID

found backup of database ID 1143261986

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

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

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

RMAN-03002: failure of Duplicate Db command at 03/06/2024 09:09:00

RMAN-05501: aborting duplication of target database

RMAN-06136: Oracle error from auxiliary database: ORA-01503: CREATE CONTROLFILE failed

ORA-00349: failure obtaining block size for '+DATA'

ORA-29701: unable to connect to Cluster Synchronization Service

ORA-29701: unable to connect to Cluster Synchronization Service

ORA-29701: unable to connect to Cluster Synchronization Service

Solution for the above error :

Add below values in parameter file on target server ..  update the correct path for your datafile and logfiles location in target side parameter .. that will resolve the below error .

*.cluster_database=FALSE

#*.control_files='/u01/app/oracle/oradata/UATDB/control01.ctl','/u01/app/oracle/fast_recovery_area/UATDB/control02.ctl'
*.db_file_name_convert='+DATA/RACSDB/DATAFILE','/u01/app/oracle/oradata/UATDB'
*.log_file_name_convert='+DATA/RACSDB/ONLINELOG','/u01/app/oracle/oradata/UATDB','+ARCH/RACSDB/ONLINELOG','/u01/app/oracle/oradata/UATDB'

After that restart the duplicate command 

RMAN> duplicate database to 'UATDB' backup location '/u01/Backup' nofilenamecheck;

Starting Duplicate Db at 06-MAR-24

searching for database ID

found backup of database ID 1143261986

contents of Memory Script:

{

   sql clone "create spfile from memory";

}

executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:

{

   shutdown clone immediate;

   startup clone nomount;

}

executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area    1912602056 bytes

Fixed Size                     8941000 bytes

Variable Size                436207616 bytes

Database Buffers            1459617792 bytes

Redo Buffers                   7835648 bytes

contents of Memory Script:

{

   sql clone "alter system set  control_files =

  ''/u01/app/oracle/fast_recovery_area/UATDB/controlfile/o1_mf_lyhstm01_.ctl'' comment=

 ''Set by RMAN'' scope=spfile";

   sql clone "alter system set  db_name =

 ''RACSDB'' comment=

 ''Modified by RMAN duplicate'' scope=spfile";

   sql clone "alter system set  db_unique_name =

 ''UATDB'' comment=

 ''Modified by RMAN duplicate'' scope=spfile";

   shutdown clone immediate;

   startup clone force nomount

   restore clone primary controlfile from  '/u01/Backup/c-1143261986-20240306-00';

   alter clone database mount;

}

executing Memory Script

sql statement: alter system set  control_files =   ''/u01/app/oracle/fast_recovery_area/UATDB/controlfile/o1_mf_lyhstm01_.ctl'' comment= ''Set by RMAN'' scope=spfile

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

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

Oracle instance shut down

Oracle instance started

Total System Global Area    1912602056 bytes

Fixed Size                     8941000 bytes

Variable Size                436207616 bytes

Database Buffers            1459617792 bytes

Redo Buffers                   7835648 bytes

Starting restore at 06-MAR-24

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=34 device type=DISK

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

output file name=/u01/app/oracle/fast_recovery_area/UATDB/controlfile/o1_mf_lyhstm01_.ctl

Finished restore at 06-MAR-24

database mounted

released channel: ORA_AUX_DISK_1

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=34 device type=DISK

RMAN-05158: WARNING: auxiliary (tempfile) file name +DATA/RACSDB/TEMPFILE/temp.264.1159692461 conflicts with a file used by the target database

RMAN-05529: warning: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to disk group only.

contents of Memory Script:

{

   set until scn  7634718;

   set newname for datafile  1 to

 "/u01/app/oracle/oradata/UATDB/system.257.1159692337";

   set newname for datafile  3 to

 "/u01/app/oracle/oradata/UATDB/sysaux.258.1159692373";

   set newname for datafile  4 to

 "/u01/app/oracle/oradata/UATDB/undotbs1.259.1159692387";

   set newname for datafile  5 to

 "/u01/app/oracle/oradata/UATDB/undotbs2.265.1159692665";

   set newname for datafile  7 to

 "/u01/app/oracle/oradata/UATDB/users.260.1159692389";

   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

Starting restore at 06-MAR-24

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 /u01/app/oracle/oradata/UATDB/system.257.1159692337

channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/UATDB/sysaux.258.1159692373

channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/UATDB/undotbs1.259.1159692387

channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/UATDB/undotbs2.265.1159692665

channel ORA_AUX_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/UATDB/users.260.1159692389

channel ORA_AUX_DISK_1: reading from backup piece /u01/Backup/1143261986-20240306-022l0f0a_2_1_1

channel ORA_AUX_DISK_1: piece handle=/u01/Backup/1143261986-20240306-022l0f0a_2_1_1 tag=TAG20240306T075553

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:15

Finished restore at 06-MAR-24

contents of Memory Script:

{

   switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy RECID=6 STAMP=1162891149 file name=/u01/app/oracle/oradata/UATDB/system.257.1159692337

datafile 3 switched to datafile copy

input datafile copy RECID=7 STAMP=1162891149 file name=/u01/app/oracle/oradata/UATDB/sysaux.258.1159692373

datafile 4 switched to datafile copy

input datafile copy RECID=8 STAMP=1162891149 file name=/u01/app/oracle/oradata/UATDB/undotbs1.259.1159692387

datafile 5 switched to datafile copy

input datafile copy RECID=9 STAMP=1162891149 file name=/u01/app/oracle/oradata/UATDB/undotbs2.265.1159692665

datafile 7 switched to datafile copy

input datafile copy RECID=10 STAMP=1162891149 file name=/u01/app/oracle/oradata/UATDB/users.260.1159692389

contents of Memory Script:

{

   set until scn  7634718;

   recover

   clone database

    delete archivelog

   ;

}

executing Memory Script

executing command: SET until clause

Starting recover at 06-MAR-24

using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archived log restore to default destination

channel ORA_AUX_DISK_1: restoring archived log

archived log thread=1 sequence=46

channel ORA_AUX_DISK_1: restoring archived log

archived log thread=2 sequence=36

channel ORA_AUX_DISK_1: reading from backup piece /u01/Backup/1143261986-20240306-032l0f2d_3_1_1

channel ORA_AUX_DISK_1: piece handle=/u01/Backup/1143261986-20240306-032l0f2d_3_1_1 tag=TAG20240306T075701

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03

archived log file name=/u01/app/oracle/fast_recovery_area/UATDB/archivelog/2024_03_06/o1_mf_1_46_lyhsxpdr_.arc thread=1 sequence=46

archived log file name=/u01/app/oracle/fast_recovery_area/UATDB/archivelog/2024_03_06/o1_mf_2_36_lyhsxpgb_.arc thread=2 sequence=36

channel clone_default: deleting archived log(s)

archived log file name=/u01/app/oracle/fast_recovery_area/UATDB/archivelog/2024_03_06/o1_mf_1_46_lyhsxpdr_.arc RECID=2 STAMP=1162891151

channel clone_default: deleting archived log(s)

archived log file name=/u01/app/oracle/fast_recovery_area/UATDB/archivelog/2024_03_06/o1_mf_2_36_lyhsxpgb_.arc RECID=1 STAMP=1162891150

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

Finished recover at 06-MAR-24

Oracle instance started

Total System Global Area    1912602056 bytes

Fixed Size                     8941000 bytes

Variable Size                436207616 bytes

Database Buffers            1459617792 bytes

Redo Buffers                   7835648 bytes

contents of Memory Script:

{

   sql clone "alter system set  db_name =

 ''UATDB'' comment=

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

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

}

executing Memory Script

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

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance started

Total System Global Area    1912602056 bytes

Fixed Size                     8941000 bytes

Variable Size                436207616 bytes

Database Buffers            1459617792 bytes

Redo Buffers                   7835648 bytes

sql statement: CREATE CONTROLFILE REUSE SET DATABASE "UATDB" RESETLOGS ARCHIVELOG

  MAXLOGFILES     16

  MAXLOGMEMBERS      3

  MAXDATAFILES      100

  MAXINSTANCES     8

  MAXLOGHISTORY      292

 LOGFILE

  GROUP     1 ( '/u01/app/oracle/oradata/UATDB/group_1.257.1159692457', '/u01/app/oracle/oradata/UATDB/group_1.262.1159692455' ) SIZE 200 M  REUSE,

  GROUP     2 ( '/u01/app/oracle/oradata/UATDB/group_2.258.1159692457', '/u01/app/oracle/oradata/UATDB/group_2.263.1159692455' ) SIZE 200 M  REUSE

 DATAFILE

  '/u01/app/oracle/oradata/UATDB/system.257.1159692337'

 CHARACTER SET AL32UTF8

sql statement: ALTER DATABASE ADD LOGFILE

  INSTANCE 'i2'

  GROUP     3 ( '/u01/app/oracle/oradata/UATDB/group_3.266.1159692729', '/u01/app/oracle/oradata/UATDB/group_3.259.1159692729' ) SIZE 200 M  REUSE,

  GROUP     4 ( '/u01/app/oracle/oradata/UATDB/group_4.267.1159692729', '/u01/app/oracle/oradata/UATDB/group_4.260.1159692729' ) SIZE 200 M  REUSE

contents of Memory Script:

{

   set newname for tempfile  1 to

 "+DATA";

   switch clone tempfile all;

   catalog clone datafilecopy  "/u01/app/oracle/oradata/UATDB/sysaux.258.1159692373",

 "/u01/app/oracle/oradata/UATDB/undotbs1.259.1159692387",

 "/u01/app/oracle/oradata/UATDB/undotbs2.265.1159692665",

 "/u01/app/oracle/oradata/UATDB/users.260.1159692389";

   switch clone datafile all;

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +DATA in control file

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/UATDB/sysaux.258.1159692373 RECID=1 STAMP=1162891171

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/UATDB/undotbs1.259.1159692387 RECID=2 STAMP=1162891171

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/UATDB/undotbs2.265.1159692665 RECID=3 STAMP=1162891171

cataloged datafile copy

datafile copy file name=/u01/app/oracle/oradata/UATDB/users.260.1159692389 RECID=4 STAMP=1162891171

datafile 3 switched to datafile copy

input datafile copy RECID=1 STAMP=1162891171 file name=/u01/app/oracle/oradata/UATDB/sysaux.258.1159692373

datafile 4 switched to datafile copy

input datafile copy RECID=2 STAMP=1162891171 file name=/u01/app/oracle/oradata/UATDB/undotbs1.259.1159692387

datafile 5 switched to datafile copy

input datafile copy RECID=3 STAMP=1162891171 file name=/u01/app/oracle/oradata/UATDB/undotbs2.265.1159692665

datafile 7 switched to datafile copy

input datafile copy RECID=4 STAMP=1162891171 file name=/u01/app/oracle/oradata/UATDB/users.260.1159692389

contents of Memory Script:

{

   Alter clone database open resetlogs;

}

executing Memory Script

database opened

Cannot remove created server parameter file

Finished Duplicate Db at 06-MAR-24

RMAN> 

Do the post restore steps .. 

Thanks,
Srini

Rman backup Management

 Dear's

An essential part of a backup and recovery strategy is managing backups after you create them. Backup management includes deleting obsolete backups and performing periodic checks to ensure that backups are available and usable.

A backup recorded in the Oracle Recovery Manager (RMAN) repository has one of the following status values:

Available, meaning that the backup is still present on disk or tape, as recorded in the repository

Expired, meaning that the backup no longer exists on disk or tape, but is still listed in the repository

Unavailable, meaning that the backup is temporarily not available for data recovery operations (because, for example, it is stored on a tape that is stored offsite or on a disk that is currently not mounted)

Backups can also be obsolete. An obsolete backup is, based on the currently configured retention policy, no longer needed to satisfy data recovery goals.

Maintenance tasks that you can perform in RMAN include the following:

Viewing details about your backups

Cross-checking your repository, which means checking whether backups listed in the repository exist and are accessible, and marking as expired any backups not accessible at the time of the cross-check

Deleting the record of expired backups from your RMAN repository

Deleting obsolete backups from the repository and from the backup media

Validating backups to ensure that a given backup is available and not corrupted

Cross-Checking Backups  :

LIST BACKUP SUMMARY;

CROSSCHECK BACKUPSET 1345;

CROSSCHECK DATAFILECOPY 1,5;

CROSSCHECK BACKUP;

Deleting Expired Backups  : 

Deleting expired backups removes from the Oracle Recovery Manager (RMAN) repository those backups that are listed as EXPIRED. Expired backups are those found to be inaccessible during a cross-check. No attempt is made to delete the files containing the backup from disk or tape; this action updates only the RMAN repository.

CROSSCHECK BACKUPSET;

DELETE EXPIRED BACKUP;

LIST BACKUP SUMMARY;

DELETE OBSOLETE;

SELECT * FROM V$RECOVERY_FILE_DEST;

SELECT * FROM V$RECOVERY_AREA_USAGE;

RMAN Issue and solutions

RMAN CLONE / DUPLICATE command failed with RMAN-06403 RMAN-04006 RMAN-04015 ORA-19710

Solution

- Ensure that the character set of TARGET database and catalog database are same
- Set the environment variable ORA_NLS10 (ORA_NLS33 for 8i and 9i) at OS level
  If you are using a Oracle8, 8i or 9i version then set the ORA_NLS33 for Oracle10g and higher, set
  environment variable to:
  ORA_NLS10 = $ORACLE_HOME/ocommon/nls/admin/data
- Restart the Listener and Instance after setting the ORA-NLS10.

Note:  The error ORA-19710, can also occur due to incompatibilities in listener versions while connected to target and catalog database. 
Explanation : There are distinct differences between an 8i, 9i and 10g listener process and the functionality that each process will recognise ; this will also be true of  the shadow processes that are spawned by each listener as they will inherit the same runtime environment and attributes of the listener.  Where you are having issues with connections to target database running at a different release to that of the listener, the recommendation is to use a
separate listener process and to connect to each target using its respective listener.



Rman Database Clone Terminated Unsuccessful Ora-01667

Symptoms

RMAN duplicate fails opening the clone database:

RMAN-00601: fatal error in recovery manager
RMAN-03004: fatal error during execution of command
RMAN-10039: error encountered while polling for RPC completion on channel clone_default
RMAN-10006: error running SQL statement: select act from x$ksusex where sid=:1 and serial=:2
RMAN-10002: ORACLE error: ORA-03113: end-of-file on communication channel
RMAN-03002: failure of Duplicate Db command at 12/13/2006 13:03:25
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-01092: ORACLE instance terminated.
Disconnection forced

Solution

Option a)
---------
Recreate the controlfile with a higher value for MAXDATAFILES  and
follow Note 360962.1 (Manual Completion of a Failed RMAN Duplicate)to complete the duplicate process.


Option b)
---------
If RMAN Duplicated failed at end, when the 'resetlogs' are already done,
we need to recreate control file with NORESETLOGS option pointing to redo log files location in auxiliary database.

Steps to resolve the issue:

1. In the control file recreation, use NORESETLOGS & mention auxiliary redo log files location.

    CREATE CONTROLFILE REUSE SET DATABASE 'XXX' NORESETLOGS ARCHIVELOG... etc

2. Recover & open auxiliary database.

    recover database;
    alter database open;

RMAN RESTORE / DUPLICATE FAILS AT OPEN (CLONE) ORA-01139 / ORA-16433, IF WRONG UNDO TABLESPACE IS SPECIFIED FOR THE RESTORE DB / or AUXILIARY 

Symptoms

RMAN Duplicate fails to open CLONE with ORA-01139 / ORA-16433 when the wrong undo tablespace is specified in auxiliary pfile:

contents of Memory Script:
{
  Alter clone database open resetlogs;
}
executing Memory Script
}
executing Memory Script

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00601: fatal error in recovery manager
RMAN-03004: fatal error during execution of command
RMAN-10041: Could not re-create polling channel context following failure.
RMAN-10024: error setting up for rpc polling
RMAN-10005: error opening cursor
RMAN-10002: ORACLE error: ORA-03114: not connected to ORACLE
RMAN-03002: failure of Duplicate Db command at 10/10/2012 14:27:10
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-01092: ORACLE instance ter
minated. Disconnection forced
ORA-30012: undo tablespace 'UNDOTBS1X' does not exist or of wrong type

 All attempts to clear the issue with manual recovery fail:
Using SQLPlus

SQL> recover database using backup controlfile until cancel;
ORA-00283: recovery session canceled due to errors
ORA-16433: The database must be opened in read/write mode.
SQL> alter database backup controlfile to trace;
alter database backup controlfile to trace
*
ERROR at line 1:
ORA-16433: The database must be opened in read/write mode.

Using RMAN

RMAN> recover clone database;
RMAN-11003: failure during parse/execution of SQL statement: alter database
recover if needed
 start until cancel using backup controlfile
ORA-00283: recovery session canceled due to errors
ORA-16433: The database must be opened in read/write mode.

Solution

Recreate the controlfile using an edited version of the create controlfile script generated from the target:  How to Recreate a Controlfile (Doc ID 735106.1)

The auxiliary alert log shows that a resetlogs was done and will identify the online log for sequence# 1:

Thread 1 opened at log sequence 1
Current log# 1 seq# 1 mem# 0: C:\APP\DUP\REDO01.LOG

To  complete the process :

- correct undo_tablespace parameter in auxiliary pfile
- remount auxiliary with the newly created controlfile
- recover and open with resetlogs

SQL> recover database using backup controlfile;
ORA-00279: change 2023230 generated at 10/10/2012 14:27:01 needed for thread 1
ORA-00289: suggestion :
C:\APP\FAST_RECOVERY_AREA\DUP\ARCHIVELOG\2012_10_10\O1_MF_1_1_%U_.ARC
ORA-00280: change 2023230 for thread 1 is in sequence #1

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
C:\APP\DUP\REDO01.LOG
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

 

Note: To avoid this error in the future, ensure that you specify the same UNDO_TABLESPACE parameter for the auxiliary as is currently set for the target database.  If duplicating a RAC target database, you can specify any of the target instance's undo_tablespace for the auxiliary.

 

Note:
-----
o The same error ORA-01139 / ORA-16433 is as well reported, if wrong UNDO tablespace is specified
  and then trying to open database resetlogs after an RMAN RESTORE and RECOVER.

 

Thanks

Srini

 

RMAN usefull comands ....

Full Database Restore
$ORACLE_HOME/bin/rman target / nocatalog
RMAN> shutdown abort;
RMAN> startup mount;
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open;
database opened
Tablespace Restore (online)
$ORACLE_HOME/bin/rman target / nocatalog
RMAN> sql ‘alter tablespace users offline’;
RMAN> restore tablespace users;
RMAN> recover tablespace users;
RMAN> sql ‘alter tablespace users online’;
* A SYSTEM tablespace cannot be recovered with the database online.
Tablespace Restore (offline)
$ORACLE_HOME/bin/rman target / nocatalog
RMAN> shutdown abort;
RMAN> startup mount;
RMAN> restore tablespace users;
RMAN> recover tablespace users;
RMAN> alter database open;
database opened
Restoring a Specific Datafile
$ORACLE_HOME/bin/rman target / nocatalog
RMAN> shutdown abort;
RMAN> startup mount;
RMAN> restore datafile ‘/oradata/DB1/dbf/users01.dbf’;
RMAN> recover datafile ‘/oradata/DB1/dbf/users01.dbf’;
RMAN> alter database open;
database opened
Control File Restoration
Prerequisite: In your rman backup directory determine the latest control file backup.
Default Format: c-nnnnnnnnnn-nnnnnnnn-nn
$ORACLE_HOME/bin/rman target / nocatalog
RMAN> shutdown abort;
RMAN> startup nomount;
RMAN> set dbid = 1184749195
RMAN> restore controlfile from ‘/oradata/DB1/rman/c-1184749195-20060626-02’
RMAN> alter database mount;
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open resetlogs;
database opened
Database Point-In-Time-Recovery (PITR)
Also known as time-based incomplete recovery.
$ORACLE_HOME/bin/rman target / nocatalog
RMAN> shutdown abort;
RMAN> startup mount;
RMAN> restore database until time “to_date(’09/03/07 13:00:00′, ‘MM/DD/YY HH24:MI:SS’)”;
RMAN> recover database until time “to_date(’09/03/07 13:00:00′, ‘MM/DD/YY HH24:MI:SS’)”;
RMAN> alter database open resetlogs;
database opened
* Make sure you perform a full backup after this operation!
Restore to Another System
Prerequisites
Ideally ensure destination system configured exactly like source.
Same OS version and patch level.
Same drives (C:, D:, S: etc.).
CPU and RAM same or better.
The same version of Oracle is installed on the target system as the source.
Ensure the ORACLE_HOME and ORACLE_SID environment variables are set.
Ensure the listener is running.
Copy RMAN backupset files to the destination system rman directory.
If Windows:
Create the password file.
orapwd file=orapwDB1 password=mypassword
Creates the file %ORACLE_HOME%\dbs\orapwDB1
Copy %ORACLE_HOME%\dbs\orapwDB1 to %ORACLE_HOME%\database.
In some instances of a restore like this it may look for the file here.
Create or start the Windows database instance service.
oradim -new -sid DB1 -intpwd mypassword -startmode MANUAL
Creates the file: %ORACLE_HOME%\database\PWDDB1.ORA
Ensure the drive\path to the admin (adump,bdump,cdump,udump), data and redo directories on the source and destination systems are identical.
Example:
Admin Dump Directories
mkdir C:\oracle\product\10.2.0\admin
mkdir C:\oracle\product\10.2.0\admin\DB1
mkdir C:\oracle\product\10.2.0\admin\DB1\adump
mkdir C:\oracle\product\10.2.0\admin\DB1\bdump
mkdir C:\oracle\product\10.2.0\admin\DB1\cdump
mkdir C:\oracle\product\10.2.0\admin\DB1\udump
Data Directories
mkdir D:\oradata
mkdir D:\oradata\DB1
Redo and Archive Log Directories
mkdir D:\oradata\DB1\recovery1
mkdir D:\oradata\DB1\recovery2
Procedure
Restore SPFILE and Control File
%ORACLE_HOME%\bin\rman target / nocatalog
RMAN> set dbid 161080442
RMAN> startup nomount;
Creates the file: %ORACLE_HOME%\database\hc_db1.dat
RMAN> restore spfile from ‘R:\rman\C-161080442-20080313-00’;
Creates the file: %ORACLE_HOME%\database\SPFILEDB1.ORA
RMAN> startup force nomount
RMAN> restore controlfile from ‘R:\rman\C-161080442-20080313-00’;
RMAN> shutdown immediate
RMAN> exit
Restore and Recover the Data
%ORACLE_HOME%\bin\rman target / nocatalog
RMAN> startup mount;
RMAN> restore database;
For a large database this step may take some time.
RMAN> recover database;
If you do not have\need the very last log(s) you can disregard any error messages.
ORA-00310: archived log contains sequence 100; sequence 101 required…
RMAN> alter database open resetlogs;
database opened

Thanks
Srini

Explanation of RMAN Commands

Explanation of RMAN Commands

  TARGET DATABASE – A Target Database is the primary database that will be backed up for standby database creation. In RMAN’s terminology, the term target database identifies the database that is undergoing a backup, restore or recovery operation by Recovery Manager.

AUXILIARY DATABASE – An Auxiliary Database is a standby database that will be created as a result of the duplication of the target database. In RMAN’s terminology, Auxiliary instance identifies an instance which RMAN connects in order to execute the duplicate command.

CHANNEL – A Channel is a communication pipeline between a RMAN executable and a target or auxiliary database. A channel consists of a server session on the target or auxiliary database and a data stream from the database to the backup device or vice-versa. RMAN console sends commands to the database using this channel, and the server session running on the database executes the command on behalf of Recovery Manager. Some degree of parallelism during the backup or restore operation can be achieved using multiple channels.

AUTOMATIC CHANNEL ALLOCATION  – RMAN Channels can be configured to use a set of default attributes for each operation when a channel is not allocated manually. This set of channels is persistent and can be configured using the CONFIGURE command. When such a set of channels is pre-defined, it is called automatic channel allocation. By default, RMAN configures a channel of device type, DISK, to be used for automatic channel allocation.

MANUAL CHANNEL ALLOCATION   - As the name suggests, a channel can be configured manually for special needs such as increasing the degree of parallelism. Channels can be allocated manually by using the ALLOCATE CHANNEL command in the RUN block of RMAN statement.

DUPLICATE COMMAND – To restore a backup set or image copy, the DUPLICATE command can be used. The duplicate command always performs a complete restoration of the target database. Using this command, a database can be restored on the local host sharing with the target database or on remote host. A recovery catalog is not needed to use the duplicate command.  It can be used with the control file of the target database serving as the repository.

BACKUP SET – Recovery Manager backs up the datafiles, control file, archived log files, and server parameter files in a RMAN specific format called backup pieces. A set of one or more such backup pieces makes up a backup set. A backup set is created using the BACKUP command.

IMAGE COPY – As opposed to the backup set, an image copy is not a RMAN specific format. It is a replica of an actual file. Image copies are created using the COPY command.

SET NEWNAME   – This command can be used to rename the data files to be restored to a new location. It is equivalent to the db_file_name_convert  parameter of the server initialization parameter file. Combination of the SET NEWNAME and SWITCH command is the equivalent of the ALTER DATABASE RENAME FILE statement.

CONFIGURE AUXNAME   – CONFIGURE AUXNAME is equivalent to the SET NEWNAME  command, except that the CONFIGURE AUXNAME is persistent, whereas, the SET NEWNAME command must be used every time the DBA wants to rename a data file. It is necessary to connect to the recovery catalog in order to use the CONFIGURE AUXNAME command.

DORECOVER   – When the DUPLICATE command is specified with the DORECOVER option, it starts recovery after restoration. The recovery is performed using all available archived redo logs and incremental backups. This is the recommended option while creating standby databases using recovery manager. This will save the extra step of recovering the standby database once it is created.

NOFILENAMECHECK  – It is an option for the DUPLICATE command. When NOFILENAMECHECK is used with the DUPLICATE command, RMAN does not validate the filenames during restoration. If the primary database and the standby database are on the same host, this option should not be used.

Thanks
Srini

Using RMAN Effectively In A Dataguard Environment

Data Guard enables and automates the management of a disaster recovery solution for Oracle databases located on the same campus or across the continent. Data Guard consists of a production database (also known as the primary database) and one or more standby database(s), which are transactionally consistent copies of the production database.

RMAN is a tool integrated with the Oracle Database that satisfies the demands of high performance, manageable backup and recovery. RMAN is designed to work intimately with the server, providing block-level corruption detection during backup and restore. RMAN optimizes performance and space consumption during backup with file multiplexing and compression, and operates with leading backup software systems via the supplied Media Management Library (MML) API.

RMAN brings rich functionality such as online backups, incremental backups, block media recovery, automation of backup management tasks, and integration with 3rd party media management systems into the Data Guard configuration. Since RMAN and Data Guard are part of the integrated Oracle High Availability technology stack, RMAN backups can be seamlessly offloaded to a physical standby database, allowing customers to gain more value out of their disaster recovery investment. Backups do not impact normal Data Guard operation they can be taken while the standby database is in recovery or read-only mode. Backups can be used to recover either primary or standby database servers.

Data Guard and RMAN were both designed with the Oracle database architecture in mind. Together, they offer the most reliable and tightly integrated solution to achieve superior levels of Oracle database availability supporting your mission critical applications. Data Guard and RMAN are both fully supported features of the Oracle Database Enterprise Edition (RMAN is also provided with Oracle Database Standard Edition).

This article basically talks about how RMAN can be used in a dataguard environment effectively. The following topics will be discussed here.

++ Creating a standby database using RMAN.
++ Automatic maintenance of the archivelogs on the standby database using RMAN.
++ Rolling forward a Standby database using RMAN incremental backups.
++ Using RMAN to backup the Standby databases.
++ Reinstating a Physical Standby Using RMAN Backups Instead of Flashback.

Details

Creating a standby database using RMAN.

Apart from manually creating the standby database, RMAN can also be used to create the standby database. There are several advantages to using RMAN to create a standby database:

* RMAN creates standby databases using backups of the primary database, restoring datafiles to  the standby site from backups. Thus, the primary database is not affected during the creation of standby databases.
* RMAN automates renaming of files including Oracle Managed Files (OMF), and directory structures.
* RMAN restores archived redo log files from backups and performs recovery to catch up the standby database to the primary database.

The procedure for preparing a standby database with RMAN is basically the same as for preparing a duplicate database.Nevertheless, you need to amend the duplication procedures described in the following articles to account for the issues specific to a standby database. 

Note 374069.1 Creating a Standby Database on a new host

Note 789370.1 Creating Physical Standby using RMAN Duplicate Without Shutting down The Primary

There are a few other documents related to creation of a standby, but are describing a specific situation

ASM related :
  Note 838828.1 Step-By-Step Guide To Create Physical Standby On Normal File System For ASM Primary using RMAN
  Note 837102.1 Step By Step Guide On Creating Physical Standby Using RMAN Duplicate In ASM Filesystem For ASM Primary

Miscellaneous :
   Note 469493.1 Step By Step Guide To Create Physical Standby Database Using RMAN Backup and Restore
   Note 1075908.1 Step by Step Guide on Creating Physical Standby Using RMAN DUPLICATE...FROM ACTIVE DATABASE

Automatic maintenance of the archivelogs on the standby database using RMAN.

By default, archived redo log files in a flash recovery area that were backed up to a tertiary device or made obsolete (as defined by the RMAN retention policy) are eligible for deletion. The archived redo log files that are backed up or obsolete can eventually be deleted automatically to make space if the disk space in the flash recovery area becomes full. However, you can change this default deletion policy using the following RMAN command:

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO [CLEAR | NONE | APPLIED ON STANDBY];

The necessary steps to implement the above feature is clearly provided in the following article.

Note 728053.1 Configure RMAN to purge archivelogs after applied on standby
Note 394261.1 How to Ensure that RMAN Does NOT Delete Archived Logs That Have Not Yet Shipped to Standby

Rolling forward a Standby database using rman incremental backups.

In some situations, RMAN incremental backups can be used to synchronize a physical standby database with the primary database. Using the RMAN BACKUP INCREMENTAL FROM SCN command, you can create a backup on the primary database that starts at the standby database's current SCN, which can then be used to roll the standby database forward in time.

RMAN incremental backups will be useful in the following situations

* Physical Standby Database Lags Far Behind the Primary Database
* Physical Standby Database Has Nologging Changes On a Subset of Datafiles
* Physical Standby Database Has Widespread Nologging Changes
The steps that have to be implemented in the above situations are clearly described in the following articles.

Note.290814.1 Rolling a Standby Forward using an RMAN Incremental Backup in 10g
Note.290817.1 Rolling a Standby Forward using an RMAN Incremental Backup in 9i
Note 958181.1 Rolling a Standby Forward using an RMAN Incremental Backup To Fix The Nologging Changes
Note 836986.1 Steps to perform for Rolling forward a standby database using RMAN incremental backup when primary and standby are in ASM filesystem

Miscellaneous :
Note 605234.1   How to Copy ASM datafile From Primary Database to Standby Database on ASM using RMAN
Note 1531031.1 Steps to perform for Rolling forward a standby database using RMAN incremental backup when datafile is added to primary

 

Using Rman backups to backup standby database

RMAN can back up the standby database and its associated archived redo logs. Standby backups of datafiles and archived redo logs are fully interchangeable with primary database backups. In other words, you can run the RESTORE command
to restore a backup of a standby datafile to the primary database, and you can restore a backup of a primary datafile to the standby database. The standby control file and primary control file, however, are not interchangeable.

The following articles provide extensive information about rman backups in the standby environemnt.

Note 602299.1 Benefits and Usage of RMAN with Standby Databases
Note 203980.1 How To Use RMAN For Backup And Restore In A Standby Database Environment
Note 1419923.1 Howto make a consistent RMAN backup in an Standby database in Active DataGuard mode

Reinstating a Physical Standby Using Backups Instead of Flashback.

After a Data Guard failover to a physical standby database, using flashback database is still the MAA recommendation to reinstate the original primary database since it is the simplest and fastest approach. However, if for some reason the flashback data is not available or flashback database was not enabled before the failure, you can use the steps provided in this document to reinstate the original primary database using existing backups instead of flashback database.

The steps are clearly depicted in the following article. 

Note 416310.1 Reinstating a Physical Standby Using Backups Instead of Flashback

Known issues

Note 357759.1 Known RMAN - Dataguard Problems

Note 1385911.1 RMAN-06131 error occurred during rman backup on standby database in dataguard environment
Note 344529.1 Rman Backup on Dataguard Site Fails Rman-06059 Attempting to Backup a Log Previously Backed Up and Deleted

 

Thanks

Srini

RMAN DUPLICATE CHANGES DB_NAME DB_UNIQUE_NAME TO UPPERCASE FOR CLONE DATABASE (Doc ID 2050095.1)

Symptoms

 RMAN DUPLICATE always changed DB_NAME / DB_UNIQUE_NAME parameter value to UPPERCASE, even with below attempts:

+ In aux init<SID>.ora, mention db_name = db_unique_name = "lower_case_name"

+ In DUPLICATE command, mention :

DUPLICATE TARGET DATABASE TO "lower_case_db_name" ......

+ Setting explicit value for spfile clause of DUPLICATE command:

DUPLICATE TARGET DATABASE TO "lower_case_db_name".....
....
SPFILE
..
..
  set db_name="lower_case_db_name"
  set db_unique_name="lower_case_db_unique_name"

+ Also, single quote (') or double quotes (") doesn't make any difference in results.

Changes

 RMAN DUPLICATE is performed.

Example Script with lower case name for db_name and db_unique_name:

RMAN> run {
2> allocate channel tgt1 device type disk ;
3> allocate auxiliary channel aux1 device type disk ;
4> allocate auxiliary channel aux2 device type disk ;
5> debug on ;
6> duplicate target database to "cdup1212" from active database using
backupset
7> spfile
8>       parameter_value_convert 'cprm', 'cdup'
9>   set db_file_name_convert='cprm', 'cdup'
10>   set log_file_name_convert='cprm', 'cdup'
11>   set db_name="cdup1212"
12>   set db_unique_name="cdup1212"
13>   reset log_archive_config
14>   reset standby_file_management
15>   reset fal_client
16>   reset fal_server
17>   reset log_archive_dest_2
18>   reset log_archive_dest_state_2
19> ;
20> debug off ;
21> }

+ Excerpt of output:

...
...
RMAN-08161: contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''CPRM1212'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''CDUP1212'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone from service  'CPRM1212_TNS' primary controlfile;
   alter clone database mount;
}
RMAN-08162: executing Memory Script

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

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

RMAN-06402: Oracle instance shut down

RMAN-06196: Oracle instance started
....
....

Cause

 Due to many dependencies, RMAN needs to convert DB_NAME and DB_UNIQUE_NAME to UPPER case. This is confirmed by Development via Bug 21193086 raised for this issue.

Solution

Change the dependent automation scripts / procedures to adapt for UPPERCASE name for DB_NAME and DB_UNIQUE_NAME in cloned database.

Thanks
Srini

Rman Database Clone Terminated Unsuccessful Ora-01667 (Doc ID 403214.1)

Symptoms

RMAN duplicate fails opening the clone database:

RMAN-00601: fatal error in recovery manager
RMAN-03004: fatal error during execution of command
RMAN-10039: error encountered while polling for RPC completion on channel clone_default
RMAN-10006: error running SQL statement: select act from x$ksusex where sid=:1 and serial=:2
RMAN-10002: ORACLE error: ORA-03113: end-of-file on communication channel
RMAN-03002: failure of Duplicate Db command at 12/13/2006 13:03:25
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-01092: ORACLE instance terminated.
Disconnection forced

.

Cause

Bug 4771560 OPEN RESETLOGS TERMINATES WITH ORA-1667

ORA-01667: cannot add any more tablespaces: limit of 'n' exceeded
           where n being maxdatafiles attribute in the control file.


From the bug:

This problem was discovered during RMAN DUPLICATE when MAXDATAFILES was set
to the actual number of datafiles.
.
The database code will expand the controlfile during file add and increase
MAXDATAFILES if DB_FILES parameter allows it, but the recovery/open process
won't, and will raise an ORA-1667 trying to add a tablespace, and no slots
are available.

.

Solution

Option a)
---------
Recreate the controlfile with a higher value for MAXDATAFILES  and
follow Note 360962.1 (Manual Completion of a Failed RMAN Duplicate)to complete the duplicate process.


Option b)
---------
If RMAN Duplicated failed at end, when the 'resetlogs' are already done,
we need to recreate control file with NORESETLOGS option pointing to redo log files location in auxiliary database.

Steps to resolve the issue:

1. In the control file recreation, use NORESETLOGS & mention auxiliary redo log files location.

    CREATE CONTROLFILE REUSE SET DATABASE 'XXX' NORESETLOGS ARCHIVELOG... etc

2. Recover & open auxiliary database.

    recover database;
    alter database open;

Thanks
Srini

RMAN RESTORE / DUPLICATE FAILS AT OPEN (CLONE) ORA-01139 / ORA-16433, IF WRONG UNDO TABLESPACE IS SPECIFIED FOR THE RESTORE DB / or AUXILIARY (Doc ID 1536368.1)

Symptoms

RMAN Duplicate fails to open CLONE with ORA-01139 / ORA-16433 when the wrong undo tablespace is specified in auxiliary pfile:

contents of Memory Script:
{
  Alter clone database open resetlogs;
}
executing Memory Script
}
executing Memory Script

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00601: fatal error in recovery manager
RMAN-03004: fatal error during execution of command
RMAN-10041: Could not re-create polling channel context following failure.
RMAN-10024: error setting up for rpc polling
RMAN-10005: error opening cursor
RMAN-10002: ORACLE error: ORA-03114: not connected to ORACLE
RMAN-03002: failure of Duplicate Db command at 10/10/2012 14:27:10
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-01092: ORACLE instance ter
minated. Disconnection forced
ORA-30012: undo tablespace 'UNDOTBS1X' does not exist or of wrong type

 All attempts to clear the issue with manual recovery fail:
Using SQLPlus

SQL> recover database using backup controlfile until cancel;
ORA-00283: recovery session canceled due to errors
ORA-16433: The database must be opened in read/write mode.
SQL> alter database backup controlfile to trace;
alter database backup controlfile to trace
*
ERROR at line 1:
ORA-16433: The database must be opened in read/write mode.

Using RMAN

RMAN> recover clone database;
RMAN-11003: failure during parse/execution of SQL statement: alter database
recover if needed
 start until cancel using backup controlfile
ORA-00283: recovery session canceled due to errors
ORA-16433: The database must be opened in read/write mode.

 

Cause

Auxiliary pfile specifies an invalid undo_tablespace name that is not present in the database that is being duplicated.
This causes the auxiliary to fail immediately after opening with  resetlogs.
<Bug 14744052>   AFTER FAILURE OF DUPICATE COMMAND DURING OPEN RESETLOGS, CLONE RAISES ORA-16433
Closed:  Not a Bug
A flag is set in the auxiliary pfile during resetlogs which is then unset by the dictionary check when resetlogs completes successfaully.  As resetlogs did not complete the flag is  still set causing the ORA-16433 to be raised whenever the controlfile is subsquently accessed.  There is no way to clear this flag.

Solution

Recreate the controlfile using an edited version of the create controlfile script generated from the target:  How to Recreate a Controlfile (Doc ID 735106.1)

The auxiliary alert log shows that a resetlogs was done and will identify the online log for sequence# 1:

Thread 1 opened at log sequence 1
Current log# 1 seq# 1 mem# 0: C:\APP\DUP\REDO01.LOG

To  complete the process :

- correct undo_tablespace parameter in auxiliary pfile
- remount auxiliary with the newly created controlfile
- recover and open with resetlogs

SQL> recover database using backup controlfile;
ORA-00279: change 2023230 generated at 10/10/2012 14:27:01 needed for thread 1
ORA-00289: suggestion :
C:\APP\FAST_RECOVERY_AREA\DUP\ARCHIVELOG\2012_10_10\O1_MF_1_1_%U_.ARC
ORA-00280: change 2023230 for thread 1 is in sequence #1

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
C:\APP\DUP\REDO01.LOG
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

Note: To avoid this error in the future, ensure that you specify the same UNDO_TABLESPACE parameter for the auxiliary as is currently set for the target database.  If duplicating a RAC target database, you can specify any of the target instance's undo_tablespace for the auxiliary.

Note:
-----
o The same error ORA-01139 / ORA-16433 is as well reported, if wrong UNDO tablespace is specified
  and then trying to open database resetlogs after an RMAN RESTORE and RECOVER.

 

Thanks

Srini

RMAN CLONE / DUPLICATE command failed with RMAN-06403 RMAN-04006 RMAN-04015 ORA-19710 (Doc ID 302922.1)

Cause

When the target database is not mounted, RMAN sets the target database character set to the value specified in the users environment.

Solution

- Ensure that the character set of TARGET database and catalog database are same
- Set the environment variable ORA_NLS10 (ORA_NLS33 for 8i and 9i) at OS level
  If you are using a Oracle8, 8i or 9i version then set the ORA_NLS33 for Oracle10g and higher, set
  environment variable to:
  ORA_NLS10 = $ORACLE_HOME/ocommon/nls/admin/data
- Restart the Listener and Instance after setting the ORA-NLS10.

Note:  The error ORA-19710, can also occur due to incompatibilities in listener versions while connected to target and catalog database.
Explanation : There are distinct differences between an 8i, 9i and 10g listener process and the functionality that each process will recognise ; this will also be true of  the shadow processes that are spawned by each listener as they will inherit the same runtime environment and attributes of the listener.  Where you are having issues with connections to target database running at a different release to that of the listener, the recommendation is to use a
separate listener process and to connect to each target using its respective listener.

Thanks
Srini

RMAN or HOT Cloning Steps of Database

 

RMAN Cloning in 10g :-

1.Create same(target) tree structure in clone server

2. windows: c:\>set oracle_sid=<clone db name>

Or

Linux $ export ORACLE_SID=<clone db name>

3.create pwd file

Windows :c:\>orapwd file=%ORACLE_HOME%\database\PWD<clonename>.ora password=sys

Linux:$orapwd file=$ORACLE_HOME/dbs/orapw<db name> password=sys

4.copy the target pfile and past clone server and edit name and add the db_file_name_convert, log_file_name_convert ..

5.Configure TNS and listener and checking the status use the command of ping

6.startup pfile nomount

{

7.open new terminal

8. $export ORACLE_SID=<target db name>

9.rman target

10.RMAN>backup database;

11.RMAN>backup current controlfile;

12. RMAN>backup archivelog all;

RMAN>connect auxiliary username/pwd@<clone net service name>;

RMAN>duplicate target database to ‘<clone db name>’;

Take time for minimum 1 ½ hr

}

OR

{

13. open new terminal

14. $export ORACLE_SID=<clone db>

15.rman target username/pwd@<net service name (target)> auxiliary /

16.RMAN>duplicate target database to ‘<clone name in caps>’;

Take time for minimum 1 ½ hr or after restore controlfile and datafile give ctl+c

}

17. shut immediate for target db and copy and past archivelog files

18.shu immediate for clone

19.startup mount clone

20. alter database open resetlogs; in clone

21.alter database open; in clone

 

 

Thanks

Srini