Category Archives: RMAN

Backup Based RMAN Duplicate Database

Backup Based RMAN Duplicate Database

Contents
______________________________________________________________________________________________________

1. Overview
2. Environment
3. Backup Source Database
4. Create PFILE from source db and Edit
5. Create required Directories
6. Copy Password for Clone Database
7. Add oratab entry
8. Startup Nomount TESTARC
9. Run RMAN Duplicate
10. Verify DB ID for Clone DB (TESTARC)
11. Add TNS entry
12. Verify connectivity
______________________________________________________________________________________________________


1. Overview

++ Duplicate a database from its backups without connecting to source database.

++ This technique is advantageous where network connections from the auxiliary host to the source database are restricted or prone to intermittent disruptions. 

++ In duplication without a TARGET connection, the source database is unaffected by the duplication.

++ RMAN duplication, we will connect rman to the auxiliary instance and run duplicate command.

++ In this Demo we are doing RMAN Cloning on same server.

++ Please plan Hardware requirement such as Storage,CPU,RAM before perform duplicate. 


2. Environment

Hostname			: rac1.rajasekhar.com
Source DB Name		        : TEST
Duplicate DB Name	        : TESTARC
Version				: 11.2.0.4
Filesystem			: Normal
Non-RAC				: Single Instance
Oracle Home			: /u01/app/oracle/product/11.2.0.4


3. Backup Source Database

SQL> select name,open_mode,dbid from v$database;

NAME      OPEN_MODE                  DBID
--------- -------------------- ----------
TEST      READ WRITE           2289714844 <----

SQL>

[oracle@rac1 ~]$ cat rmanclone.sh

ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0.4
BACKUP_LOG_PATH=/home/oracle
export ORACLE_BASE
export ORACLE_HOME
export ORACLE_SID=TEST
export BACKUP_LOG_PATH
LOG_FILE=${BACKUP_LOG_PATH}/clone_db.log
$ORACLE_HOME/bin/rman msglog=${LOG_FILE} <<EOF
connect target /
run {
allocate channel t1 type disk;
allocate channel t2 type disk;
allocate channel t3 type disk;
backup database format '/u02/oracle/backup/database_%d_%u_%s';
release channel t1;
release channel t2;
release channel t3;
}
sql 'alter system archive log current';
run {
allocate channel a1 type disk;
allocate channel a2 type disk;
allocate channel a3 type disk;
backup archivelog all format '/u02/oracle/backup/arch_%d_%u_%s';
release channel a1;
release channel a2;
release channel a3;
}
run {
allocate channel c1 type disk;
backup current controlfile format '/u02/oracle/backup/Control_%d_%u_%s';
release channel c1;
}
exit;
EOF

[oracle@rac1 ~]$

[oracle@rac1 ~]$ chmod 775 rmanclone.sh
[oracle@rac1 ~]$ nohup ./rmanclone.sh &
[oracle@rac1 ~]$ jobs -l
[1]+ 25183 Running                 nohup ./rmanclone.sh &
[oracle@rac1 ~]$ 
[oracle@rac1 ~]$ disown

Output:

[oracle@rac1 ~]$ cat clone_db.log

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Nov 9 07:35:48 2018

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

RMAN>
connected to target database: TEST (DBID=2289714844)

RMAN> 2> 3> 4> 5> 6> 7> 8> 9>
using target database control file instead of recovery catalog
allocated channel: t1
channel t1: SID=34 device type=DISK

allocated channel: t2
channel t2: SID=156 device type=DISK

allocated channel: t3
channel t3: SID=33 device type=DISK

Starting backup at 09-NOV-18
channel t1: starting full datafile backup set
channel t1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u02/oracle/oradata/TEST/system01.dbf
input datafile file number=00004 name=/u02/oracle/oradata/TEST/users01.dbf
channel t1: starting piece 1 at 09-NOV-18
channel t2: starting full datafile backup set
channel t2: specifying datafile(s) in backup set
input datafile file number=00002 name=/u02/oracle/oradata/TEST/sysaux01.dbf
input datafile file number=00003 name=/u02/oracle/oradata/TEST/undotbs01.dbf
channel t2: starting piece 1 at 09-NOV-18
channel t3: starting full datafile backup set
channel t3: specifying datafile(s) in backup set
including current control file in backup set
channel t3: starting piece 1 at 09-NOV-18
channel t3: finished piece 1 at 09-NOV-18
piece handle=/u02/oracle/backup/database_TEST_0athp2um_10 tag=TAG20181109T073550 comment=NONE
channel t3: backup set complete, elapsed time: 00:00:01
channel t3: starting full datafile backup set
channel t3: specifying datafile(s) in backup set
including current SPFILE in backup set
channel t3: starting piece 1 at 09-NOV-18
channel t3: finished piece 1 at 09-NOV-18
piece handle=/u02/oracle/backup/database_TEST_0bthp2uo_11 tag=TAG20181109T073550 comment=NONE
channel t3: backup set complete, elapsed time: 00:00:01
channel t1: finished piece 1 at 09-NOV-18
piece handle=/u02/oracle/backup/database_TEST_08thp2um_8 tag=TAG20181109T073550 comment=NONE
channel t1: backup set complete, elapsed time: 00:00:27
channel t2: finished piece 1 at 09-NOV-18
piece handle=/u02/oracle/backup/database_TEST_09thp2um_9 tag=TAG20181109T073550 comment=NONE
channel t2: backup set complete, elapsed time: 00:00:27
Finished backup at 09-NOV-18

released channel: t1

released channel: t2

released channel: t3

RMAN>
sql statement: alter system archive log current

RMAN> 2> 3> 4> 5> 6> 7> 8> 9>
allocated channel: a1
channel a1: SID=34 device type=DISK

allocated channel: a2
channel a2: SID=156 device type=DISK

allocated channel: a3
channel a3: SID=33 device type=DISK

Starting backup at 09-NOV-18
current log archived
channel a1: starting archived log backup set
channel a1: specifying archived log(s) in backup set
input archived log thread=1 sequence=3 RECID=1 STAMP=991586176
input archived log thread=1 sequence=4 RECID=2 STAMP=991586178
input archived log thread=1 sequence=5 RECID=3 STAMP=991586190
input archived log thread=1 sequence=6 RECID=4 STAMP=991590072
channel a1: starting piece 1 at 09-NOV-18
channel a2: starting archived log backup set
channel a2: specifying archived log(s) in backup set
input archived log thread=1 sequence=7 RECID=5 STAMP=991590214
input archived log thread=1 sequence=8 RECID=6 STAMP=991590217
input archived log thread=1 sequence=9 RECID=7 STAMP=991590220
input archived log thread=1 sequence=10 RECID=9 STAMP=991590841
input archived log thread=1 sequence=11 RECID=14 STAMP=991590922
input archived log thread=1 sequence=12 RECID=16 STAMP=991590926
input archived log thread=1 sequence=13 RECID=18 STAMP=991590929
channel a2: starting piece 1 at 09-NOV-18
channel a3: starting archived log backup set
channel a3: specifying archived log(s) in backup set
input archived log thread=1 sequence=14 RECID=20 STAMP=991599494
input archived log thread=1 sequence=15 RECID=21 STAMP=991599606
input archived log thread=1 sequence=16 RECID=24 STAMP=991717144
input archived log thread=1 sequence=17 RECID=26 STAMP=991717147
input archived log thread=1 sequence=18 RECID=28 STAMP=991717527
input archived log thread=1 sequence=19 RECID=30 STAMP=991717529
input archived log thread=1 sequence=20 RECID=32 STAMP=991717532
channel a3: starting piece 1 at 09-NOV-18
channel a2: finished piece 1 at 09-NOV-18
piece handle=/u02/oracle/backup/arch_TEST_0dthp2vj_13 tag=TAG20181109T073618 comment=NONE
channel a2: backup set complete, elapsed time: 00:00:01
channel a2: starting archived log backup set
channel a2: specifying archived log(s) in backup set
input archived log thread=1 sequence=21 RECID=35 STAMP=991720817
input archived log thread=1 sequence=22 RECID=37 STAMP=991726578
channel a2: starting piece 1 at 09-NOV-18
channel a3: finished piece 1 at 09-NOV-18
piece handle=/u02/oracle/backup/arch_TEST_0ethp2vj_14 tag=TAG20181109T073618 comment=NONE
channel a3: backup set complete, elapsed time: 00:00:01
channel a3: starting archived log backup set
channel a3: specifying archived log(s) in backup set
input archived log thread=1 sequence=23 RECID=38 STAMP=991726578
channel a3: starting piece 1 at 09-NOV-18
channel a1: finished piece 1 at 09-NOV-18
piece handle=/u02/oracle/backup/arch_TEST_0cthp2vj_12 tag=TAG20181109T073618 comment=NONE
channel a1: backup set complete, elapsed time: 00:00:01
channel a3: finished piece 1 at 09-NOV-18
piece handle=/u02/oracle/backup/arch_TEST_0gthp2vk_16 tag=TAG20181109T073618 comment=NONE
channel a3: backup set complete, elapsed time: 00:00:01
channel a2: finished piece 1 at 09-NOV-18
piece handle=/u02/oracle/backup/arch_TEST_0fthp2vk_15 tag=TAG20181109T073618 comment=NONE
channel a2: backup set complete, elapsed time: 00:00:03
Finished backup at 09-NOV-18

released channel: a1

released channel: a2

released channel: a3

RMAN> 2> 3> 4> 5>
allocated channel: c1
channel c1: SID=34 device type=DISK

Starting backup at 09-NOV-18
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current control file in backup set
channel c1: starting piece 1 at 09-NOV-18
channel c1: finished piece 1 at 09-NOV-18
piece handle=/u02/oracle/backup/Control_TEST_0hthp2vo_17 tag=TAG20181109T073624 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 09-NOV-18

released channel: c1

RMAN>

Recovery Manager complete.
[oracle@rac1 ~]$

-- Verify backup 
[oracle@rac1 ~]$ cd /u02/oracle/backup/
[oracle@rac1 backup]$ ls -ltr
total 1170168
-rw-r-----. 1 oracle dba   9928704 Nov  9 07:35 database_TEST_0athp2um_10
-rw-r-----. 1 oracle dba     98304 Nov  9 07:35 database_TEST_0bthp2uo_11
-rw-r-----. 1 oracle dba 381927424 Nov  9 07:36 database_TEST_09thp2um_9
-rw-r-----. 1 oracle dba 671031296 Nov  9 07:36 database_TEST_08thp2um_8
-rw-r-----. 1 oracle dba    475136 Nov  9 07:36 arch_TEST_0dthp2vj_13
-rw-r-----. 1 oracle dba  34190848 Nov  9 07:36 arch_TEST_0ethp2vj_14
-rw-r-----. 1 oracle dba  44929024 Nov  9 07:36 arch_TEST_0cthp2vj_12
-rw-r-----. 1 oracle dba      2560 Nov  9 07:36 arch_TEST_0gthp2vk_16
-rw-r-----. 1 oracle dba  45725184 Nov  9 07:36 arch_TEST_0fthp2vk_15
-rw-r-----. 1 oracle dba   9928704 Nov  9 07:36 Control_TEST_0hthp2vo_17
[oracle@rac1 backup]$

 


4. Create PFILE from source db and Edit

SQL> create pfile='/home/oracle/initTESTARC.ora' from spfile;

File created.

SQL>

[oracle@rac1 ~]$ cat initTESTARC.ora

TESTARC.__db_cache_size=385875968
TESTARC.__java_pool_size=16777216
TESTARC.__large_pool_size=33554432
TESTARC.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
TESTARC.__pga_aggregate_target=620756992
TESTARC.__sga_target=620756992
TESTARC.__shared_io_pool_size=0
TESTARC.__shared_pool_size=167772160
TESTARC.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/TESTARC/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u02/oracle/oradata/TESTARC/control01.ctl','/u02/oracle/archive/fast_recovery_area/TESTARC/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='/u02/oracle/oradata/TEST','/u02/oracle/oradata/TESTARC'
*.db_name='TESTARC'
*.db_recovery_file_dest='/u02/oracle/TESTARC/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.db_unique_name='TESTARC'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=TESTARCXDB)'
*.log_archive_dest_1='LOCATION=/u02/oracle/archive/TESTARC'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=30
*.log_file_name_convert='/u02/oracle/oradata/TEST','/u02/oracle/oradata/TESTARC'
*.memory_target=1231028224
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

[oracle@rac1 ~]$


5. Create required Directories

[oracle@rac1 ~]$ mkdir -p /u01/app/oracle/admin/TESTARC/adump
[oracle@rac1 ~]$ mkdir -p /u02/oracle/oradata/TESTARC
[oracle@rac1 ~]$ mkdir -p /u02/oracle/TESTARC/fast_recovery_area
[oracle@rac1 ~]$ mkdir -p /u02/oracle/archive/TESTARC
[oracle@rac1 ~]$


6. Copy Password for Clone Database

[oracle@rac1 ~]$ cd /u01/app/oracle/product/11.2.0.4/dbs/
[oracle@rac1 dbs]$ ls -ltr orapwTEST
-rw-r-----. 1 oracle dba 1536 Nov  7 11:40 orapwTEST
[oracle@rac1 dbs]$
[oracle@rac1 dbs]$ cp orapwTEST orapwTESTARC
[oracle@rac1 dbs]$
[oracle@rac1 dbs]$ ls -ltr orapwTEST*
-rw-r-----. 1 oracle dba 1536 Nov  7 11:40 orapwTEST
-rw-r-----. 1 oracle dba 1536 Nov  9 07:58 orapwTESTARC
[oracle@rac1 dbs]$


7. Add oratab entry

[oracle@rac1 ~]$ echo "TESTARC:/u01/app/oracle/product/11.2.0.4:N" >> /etc/oratab
[oracle@rac1 ~]$ cat /etc/oratab | grep -i TESTARC
TESTARC:/u01/app/oracle/product/11.2.0.4:N  <-----
[oracle@rac1 ~]$


8. Startup Nomount TESTARC

[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [TEST] ? TESTARC
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac1 ~]$

[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 9 08:11:25 2018

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

Connected to an idle instance.

SQL> startup nomount pfile='/home/oracle/initTESTARC.ora';
ORACLE instance started.

Total System Global Area 1235959808 bytes
Fixed Size                  2252784 bytes
Variable Size             872415248 bytes
Database Buffers          352321536 bytes
Redo Buffers                8970240 bytes
SQL>
SQL> create spfile from pfile='/home/oracle/initTESTARC.ora';

File created.

SQL> shut immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL>
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1235959808 bytes
Fixed Size                  2252784 bytes
Variable Size             872415248 bytes
Database Buffers          352321536 bytes
Redo Buffers                8970240 bytes
SQL>
SQL> !ps -ef | grep pmon
oracle   18200     1  0 04:58 ?        00:00:02 ora_pmon_TEST
oracle   26741     1  0 08:16 ?        00:00:00 ora_pmon_TESTARC  <----- oracle 26781 26652 0 08:16 pts/0 00:00:00 /bin/bash -c ps -ef | grep pmon oracle 26783 26781 0 08:16 pts/0 00:00:00 grep pmon SQL>


9. Run RMAN Duplicate

[oracle@rac1 ~]$ cat clone_TESTARC_db.sh

ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0.4
BACKUP_LOG_PATH=/home/oracle
export ORACLE_BASE
export ORACLE_HOME
export ORACLE_SID=TESTARC
export BACKUP_LOG_PATH
LOG_FILE=${BACKUP_LOG_PATH}/clone_testarc_db.log
/u01/app/oracle/product/11.2.0.4/bin/rman msglog=${LOG_FILE} <<EOF
connect auxiliary /
run {
allocate auxiliary channel t1 type disk;
allocate auxiliary channel t2 type disk;
allocate auxiliary channel t3 type disk;
duplicate target database to TESTARC backup location '/u02/oracle/backup';
}
exit
EOF
[oracle@rac1 ~]$
[oracle@rac1 ~]$ chmod 775 clone_TESTARC_db.sh
[oracle@rac1 ~]$
[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [TESTARC] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac1 ~]$
[oracle@rac1 ~]$ nohup ./clone_TESTARC_db.sh &
[1] 28021
nohup: ignoring input and appending output to `nohup.out'
[oracle@rac1 ~]$
[oracle@rac1 ~]$ jobs -l
[1]+ 28021 Running                 nohup ./clone_TESTARC_db.sh &
[oracle@rac1 ~]$
[oracle@rac1 ~]$ disown
[oracle@rac1 ~]$

Output:

[oracle@rac1 ~]$ cat clone_testarc_db.log

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Nov 9 08:39:08 2018

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

RMAN>
connected to auxiliary database: TESTARC (not mounted)

RMAN> 2> 3> 4> 5> 6>
allocated channel: t1
channel t1: SID=134 device type=DISK

allocated channel: t2
channel t2: SID=10 device type=DISK

allocated channel: t3
channel t3: SID=135 device type=DISK

Starting Duplicate Db at 09-NOV-18

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

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

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

Oracle instance shut down

Oracle instance started

Total System Global Area    1235959808 bytes

Fixed Size                     2252784 bytes
Variable Size                872415248 bytes
Database Buffers             352321536 bytes
Redo Buffers                   8970240 bytes
allocated channel: t1
channel t1: SID=133 device type=DISK
allocated channel: t2
channel t2: SID=10 device type=DISK
allocated channel: t3
channel t3: SID=134 device type=DISK

Starting restore at 09-NOV-18

channel t2: skipped, AUTOBACKUP already found
channel t3: skipped, AUTOBACKUP already found
channel t1: restoring control file
channel t1: restore complete, elapsed time: 00:00:08
output file name=/u02/oracle/oradata/TESTARC/control01.ctl
output file name=/u02/oracle/archive/fast_recovery_area/TESTARC/control02.ctl
Finished restore at 09-NOV-18

database mounted

contents of Memory Script:
{
   set until scn  980479;
   set newname for datafile  1 to
 "/u02/oracle/oradata/TESTARC/system01.dbf";
   set newname for datafile  2 to
 "/u02/oracle/oradata/TESTARC/sysaux01.dbf";
   set newname for datafile  3 to
 "/u02/oracle/oradata/TESTARC/undotbs01.dbf";
   set newname for datafile  4 to
 "/u02/oracle/oradata/TESTARC/users01.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

Starting restore at 09-NOV-18

channel t1: starting datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
channel t1: restoring datafile 00001 to /u02/oracle/oradata/TESTARC/system01.dbf
channel t1: restoring datafile 00004 to /u02/oracle/oradata/TESTARC/users01.dbf
channel t1: reading from backup piece /u02/oracle/backup/database_TEST_08thp2um_8
channel t2: starting datafile backup set restore
channel t2: specifying datafile(s) to restore from backup set
channel t2: restoring datafile 00002 to /u02/oracle/oradata/TESTARC/sysaux01.dbf
channel t2: restoring datafile 00003 to /u02/oracle/oradata/TESTARC/undotbs01.dbf
channel t2: reading from backup piece /u02/oracle/backup/database_TEST_09thp2um_9
channel t2: piece handle=/u02/oracle/backup/database_TEST_09thp2um_9 tag=TAG20181109T073550
channel t2: restored backup piece 1
channel t2: restore complete, elapsed time: 00:00:25
channel t1: piece handle=/u02/oracle/backup/database_TEST_08thp2um_8 tag=TAG20181109T073550
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:00:35
Finished restore at 09-NOV-18

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=991730407 file name=/u02/oracle/oradata/TESTARC/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=991730407 file name=/u02/oracle/oradata/TESTARC/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=991730407 file name=/u02/oracle/oradata/TESTARC/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=991730407 file name=/u02/oracle/oradata/TESTARC/users01.dbf

contents of Memory Script:
{
   set until scn  980479;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 09-NOV-18

starting media recovery

channel t1: starting archived log restore to default destination
channel t1: restoring archived log
archived log thread=1 sequence=22
channel t1: reading from backup piece /u02/oracle/backup/arch_TEST_0fthp2vk_15
channel t2: starting archived log restore to default destination
channel t2: restoring archived log
archived log thread=1 sequence=23
channel t2: reading from backup piece /u02/oracle/backup/arch_TEST_0gthp2vk_16
channel t1: piece handle=/u02/oracle/backup/arch_TEST_0fthp2vk_15 tag=TAG20181109T073618
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:00:01
archived log file name=/u02/oracle/archive/TESTARC/1_22_991568414.dbf thread=1 sequence=22
channel clone_default: deleting archived log(s)
archived log file name=/u02/oracle/archive/TESTARC/1_22_991568414.dbf RECID=2 STAMP=991730408
channel t2: piece handle=/u02/oracle/backup/arch_TEST_0gthp2vk_16 tag=TAG20181109T073618
channel t2: restored backup piece 1
channel t2: restore complete, elapsed time: 00:00:01
archived log file name=/u02/oracle/archive/TESTARC/1_23_991568414.dbf thread=1 sequence=23
channel clone_default: deleting archived log(s)
archived log file name=/u02/oracle/archive/TESTARC/1_23_991568414.dbf RECID=1 STAMP=991730408
media recovery complete, elapsed time: 00:00:00
Finished recover at 09-NOV-18
Oracle instance started

Total System Global Area    1235959808 bytes

Fixed Size                     2252784 bytes
Variable Size                889192464 bytes
Database Buffers             335544320 bytes
Redo Buffers                   8970240 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''TESTARC'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

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

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1235959808 bytes

Fixed Size                     2252784 bytes
Variable Size                889192464 bytes
Database Buffers             335544320 bytes
Redo Buffers                   8970240 bytes
allocated channel: t1
channel t1: SID=133 device type=DISK
allocated channel: t2
channel t2: SID=10 device type=DISK
allocated channel: t3
channel t3: SID=134 device type=DISK
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "TESTARC" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP   1 ( '/u02/oracle/oradata/TESTARC/redo01.log' ) SIZE 50 M  REUSE,
  GROUP   2 ( '/u02/oracle/oradata/TESTARC/redo02.log' ) SIZE 50 M  REUSE,
  GROUP   3 ( '/u02/oracle/oradata/TESTARC/redo03.log' ) SIZE 50 M  REUSE
 DATAFILE
  '/u02/oracle/oradata/TESTARC/system01.dbf'
 CHARACTER SET WE8MSWIN1252


contents of Memory Script:
{
   set newname for tempfile  1 to
 "/u02/oracle/oradata/TESTARC/temp01.dbf";
   switch clone tempfile all;
   catalog clone datafilecopy  "/u02/oracle/oradata/TESTARC/sysaux01.dbf",
 "/u02/oracle/oradata/TESTARC/undotbs01.dbf",
 "/u02/oracle/oradata/TESTARC/users01.dbf";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u02/oracle/oradata/TESTARC/temp01.dbf in control file

cataloged datafile copy
datafile copy file name=/u02/oracle/oradata/TESTARC/sysaux01.dbf RECID=1 STAMP=991730440
cataloged datafile copy
datafile copy file name=/u02/oracle/oradata/TESTARC/undotbs01.dbf RECID=2 STAMP=991730440
cataloged datafile copy
datafile copy file name=/u02/oracle/oradata/TESTARC/users01.dbf RECID=3 STAMP=991730440

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=991730440 file name=/u02/oracle/oradata/TESTARC/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=991730440 file name=/u02/oracle/oradata/TESTARC/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=991730440 file name=/u02/oracle/oradata/TESTARC/users01.dbf
Reenabling controlfile options for auxiliary database
Executing: alter database force logging

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

database opened
Finished Duplicate Db at 09-NOV-18
released channel: t1
released channel: t2
released channel: t3

RMAN>

Recovery Manager complete.
[oracle@rac1 ~]$


10. Verify DB ID for Clone DB (TESTARC)

SQL> select name,open_mode,dbid from v$database;

NAME      OPEN_MODE                  DBID
--------- -------------------- ----------
TESTARC   READ WRITE           3854160199 <----

SQL>


11. Add TNS entry

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
TESTARC   READ WRITE

++ We are using non default port number 1621, hence added local_listener

SQL> alter system set local_listener='LISTENER_11G' scope=both;  

System altered.

SQL> 

TESTARC =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.101)(PORT = 1621))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = TESTARC)
    )
  )


12. Verify connectivity

[oracle@rac1 ~]$ sqlplus sys/SYS@TESTARC as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 9 10:37:39 2018

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


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

SQL>

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com

RMAN ACTIVE DUPLICATION ASM TO ASM

RMAN ACTIVE DUPLICATION ASM TO ASM

AIM: RMAN Cloning using ACTIVE duplicate command from BR8PROD to BR8DEV without downtime.

0. Overview
1. Environment
2. Create Pfile and Edit (RAC1)
3. Copy password file/initBR8DEV.ora to auxiliary side
4. Create required directories (On the auxiliary host)
5. Add oratab entry (On the auxiliary host)
6. Add both TNS Entries on both sides (***Mandatory)
7. Static Registration on LISTENER (On the auxiliary host)
8. Start the auxilary instance (On the auxiliary host)
9. TNS Verification
10. RMAN Connectvity Verification (On the auxiliary host)
11. Run RMAN Duplicate (On the auxiliary host)
12. Verify
13. Create the spfile in ASM disk


0. Overview

It is a new feature in Oracle 11g where cloning is done from one database to another database without any outage or downtime of the source (Primary) database.

Earlier we use to clone the database using cold back or rman backup. But RMAN Active duplication feature allows a database to be duplicated directly from its live source database instead of using its backup.

RMAN directly reads the data from the database using source (Primary) database CONTROLFILE.


1. Environment

Source database:

Database name: BR8PROD
Archivelog Mode: ON
RAC: No (Oracle Restart)
Version: 11.2.0.4
Hostname: rac1.rajasekhar.com
Filesystem: ASM
Diskgroup: +DATA1,FRA
GI_HOME: /u01/app/11.2.0/grid
ORACLE_HOME:/u01/app/oracle/product/11.2.0/dbhome_1

SQL> select name, open_mode, dbid, created from v$database;

NAME      OPEN_MODE                  DBID CREATED
--------- -------------------- ---------- --------------------
BR8PROD   READ WRITE           4192214970 23-NOV-2016 01:43:22

SQL> select instance_name,host_name from v$instance;

INSTANCE_NAME    HOST_NAME
---------------- ------------------------------------
BR8PROD          rac1.rajasekhar.com


SQL> select name from v$datafile
union
select name from v$controlfile
union
select member from v$logfile
union
select name from v$tempfile
/  2    3    4    5    6    7    8

NAME
--------------------------------------------------
+DATA1/br8prod/controlfile/current.260.928633403
+DATA1/br8prod/datafile/example.265.928633413
+DATA1/br8prod/datafile/sysaux.257.928633345
+DATA1/br8prod/datafile/system.256.928633345
+DATA1/br8prod/datafile/undotbs1.258.928633345
+DATA1/br8prod/datafile/users.259.928633345
+DATA1/br8prod/onlinelog/group_1.261.928633405
+DATA1/br8prod/onlinelog/group_2.262.928633405
+DATA1/br8prod/onlinelog/group_3.263.928633407
+DATA1/br8prod/tempfile/temp.264.928633411

10 rows selected.

SQL> select * from global_name;

GLOBAL_NAME
--------------------------------
BR8PROD.RAJASEKHAR.COM

SQL>

Target database (On the auxiliary host):


Database name: BR8DEV
Archivelog Mode: ON
RAC: No (Oracle Restart)
Version: 11.2.0.4
Hostname: rac2.rajasekhar.com
Filesystem: ASM
Diskgroup: +DATA,ARCH
GI_HOME: /u01/app/11.2.0/grid
ORACLE_HOME: /u01/app/oracle/product/11.2.0.4/db_1


2. Create Pfile and Edit (RAC1)

SQL> show parameter pfile

NAME      TYPE        VALUE
--------- ----------- ------------------------------
spfile    string      +DATA1/br8prod/spfilebr8prod.ora

SQL> create pfile='/home/oracle/initBR8PROD.ora' from spfile;

File created.

SQL>


[oracle@rac1 ~]$ cat /home/oracle/initBR8PROD.ora

BR8PROD.__db_cache_size=486539264
BR8PROD.__java_pool_size=16777216
BR8PROD.__large_pool_size=33554432
BR8PROD.__pga_aggregate_target=520093696
BR8PROD.__sga_target=754974720
BR8PROD.__shared_io_pool_size=0
BR8PROD.__shared_pool_size=201326592
BR8PROD.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/BR8PROD/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='+DATA1/br8prod/controlfile/current.260.928633403'
*.db_block_size=8192
*.db_create_file_dest='+DATA1'
*.db_domain='RAJASEKHAR.COM'
*.db_name='BR8PROD'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=BR8PRODXDB)'
*.log_archive_dest_1='LOCATION=+FRA'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=1262485504
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

[oracle@rac1 ~]$


Edited Parameter File for Auxiliary Database:

cat initBR8DEV.ora

BR8DEV.__db_cache_size=486539264
BR8DEV.__java_pool_size=16777216
BR8DEV.__large_pool_size=33554432
BR8DEV.__pga_aggregate_target=520093696
BR8DEV.__sga_target=754974720
BR8DEV.__shared_io_pool_size=0
BR8DEV.__shared_pool_size=201326592
BR8DEV.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/BR8DEV/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='+DATA','+ARCH'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain='RAJASEKHAR.COM'
*.db_name='BR8DEV'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=BR8DEVXDB)'
*.log_archive_dest_1='LOCATION=+ARCH'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=1262485504
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.db_file_name_convert='+DATA1','+DATA'
*.log_file_name_convert='+DATA1','+DATA'


3. Copy password file/initBR8DEV.ora to auxiliary side

[oracle@rac1 ~]$ scp initBR8DEV.ora oracle@rac2:/u01/app/oracle/product/11.2.0.4/db_1/dbs
initBR8DEV.ora                 100%  789     0.8KB/s   00:00
[oracle@rac1 ~]$

[oracle@rac1 dbs]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1/dbs
[oracle@rac1 dbs]$ scp orapwBR8PROD oracle@rac2:/u01/app/oracle/product/11.2.0.4/db_1/dbs
orapwBR8PROD                    100% 1536     1.5KB/s   00:00
[oracle@rac1 dbs]$

[oracle@rac2 dbs]$ mv orapwBR8PROD orapwBR8DEV
[oracle@rac2 dbs]$ ls -ltr orapwBR8DEV
-rw-r----- 1 oracle oinstall 1536 Nov 23 13:56 orapwBR8DEV
[oracle@rac2 dbs]$


4. Create required directories (On the auxiliary host)

[oracle@rac2 ~]$ mkdir -p /u01/app/oracle/admin/BR8DEV/adump
[oracle@rac2 ~]$ mkdir -p /u01/app/oracle
[oracle@rac2 ~]$


5. Add oratab entry (On the auxiliary host)

[oracle@rac2 ~]$ cat /etc/oratab | grep -i BR8DEV
BR8DEV:/u01/app/oracle/product/11.2.0.4/db_1:N
[oracle@rac2 ~]$


6. Add both TNS Entries on both sides (***Mandatory)

RAC1:

BR8DEV =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = BR8DEV.rajasekhar.com)
    )
  )

BR8PROD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = BR8PROD.RAJASEKHAR.COM)
    )
  )

RAC2:

BR8DEV =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = BR8DEV.rajasekhar.com)
    )
  )


BR8PROD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = BR8PROD.RAJASEKHAR.COM)
    )
  )


7. Static Registration on LISTENER (On the auxiliary host)

On RAC2 – Add below entry to listener.ora on auxiliary side

SID_LIST_LISTENER =
  (SID_LIST =
      (SID_DESC =
      (SID_NAME = BR8DEV)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/db_1)
      (GLOBAL_DBNAME = BR8DEV.RAJASEKHAR.COM)
    )
  )

Before

[oracle@rac2 ~]$ cat /u01/app/11.2.0/grid/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/11.2.0/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1522))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent
[oracle@rac2 ~]$

[oracle@rac2 ~]$ ps -ef | grep tns
root         9     2  0 13:33 ?        00:00:00 [netns]
oracle    3428     1  0 13:36 ?        00:00:00 /u01/app/11.2.0/grid/bin/tnslsnr LISTENER -inherit
oracle    4197  3369  0 14:35 pts/1    00:00:00 grep tns
[oracle@rac2 ~]$
[oracle@rac2 ~]$ lsnrctl status LISTENER

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 23-NOV-2016 14:35:09

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                23-NOV-2016 13:36:25
Uptime                    0 days 0 hr. 58 min. 44 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/rac2/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac2.rajasekhar.com)(PORT=1522)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@rac2 ~]$

After

[oracle@rac2 ~]$ cat /u01/app/11.2.0/grid/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/11.2.0/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
      (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1522))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent

SID_LIST_LISTENER =
  (SID_LIST =
      (SID_DESC =
      (SID_NAME = BR8DEV)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/db_1)
      (GLOBAL_DBNAME = BR8DEV.RAJASEKHAR.COM)
    )
  )

[oracle@rac2 ~]$

[oracle@rac2 ~]$ lsnrctl reload LISTENER

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 23-NOV-2016 14:35:33

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1522)))
The command completed successfully
[oracle@rac2 ~]$
[oracle@rac2 admin]$ lsnrctl status LISTENER

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 23-NOV-2016 14:51:35

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                23-NOV-2016 13:36:25
Uptime                    0 days 1 hr. 15 min. 10 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/rac2/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac2.rajasekhar.com)(PORT=1522)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "BR8DEV.RAJASEKHAR.COM" has 1 instance(s).
  Instance "BR8DEV", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@rac2 admin]$


8. Start the auxiliary instance (On the auxiliary host)

[oracle@rac2 ~]$ . oraenv 
BR8DEV
[oracle@rac2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Nov 23 14:44:39 2016

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

Connected to an idle instance.

SQL> startup nomount pfile='/home/oracle/initBR8DEV.ora';
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2252864 bytes
Variable Size             805310400 bytes
Database Buffers          452984832 bytes
Redo Buffers                8818688 bytes
SQL> 


9. TNS Verification

From RAC1:

[oracle@rac1 ~]$ tnsping BR8PROD

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 23-NOV-2016 20:30:39

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = BR8PROD.RAJASEKHAR.COM)))
OK (0 msec)
[oracle@rac1 ~]$
[oracle@rac1 ~]$ tnsping BR8DEV

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 23-NOV-2016 20:30:49

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = BR8DEV.rajasekhar.com)))
OK (0 msec)
[oracle@rac1 ~]$

From RAC2:

[oracle@rac2 ~]$ tnsping BR8DEV

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 23-NOV-2016 14:58:46

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac2.rajasekhar.com)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = BR8DEV.rajasekhar.com)))
OK (0 msec)
[oracle@rac2 ~]$
[oracle@rac2 ~]$ tnsping BR8PROD

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 23-NOV-2016 14:58:53

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.rajasekhar.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = BR8PROD.RAJASEKHAR.COM)))
OK (0 msec)
[oracle@rac2 ~]$

[oracle@rac2 ~]$ sqlplus sys/sys@BR8DEV as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Nov 23 14:59:22 2016

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


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

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options


[oracle@rac2 ~]$ sqlplus sys/sys@BR8PROD as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Nov 23 14:59:34 2016

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


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

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
[oracle@rac2 ~]$


10. RMAN Connectivity Verification (On the auxiliary host)

restore_connectivity.sh

ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/db_1
ORACLE_SID=BR8DEV
BACKUP_LOG_PATH=/home/oracle
export ORACLE_BASE
export ORACLE_HOME
export ORACLE_SID
export BACKUP_LOG_PATH
LOG_FILE=${BACKUP_LOG_PATH}/restore_connectivity.log
$ORACLE_HOME/bin/rman msglog=${LOG_FILE} <<EOF
connect target sys/sys@BR8PROD
connect auxiliary sys/sys@BR8DEV
run{
allocate channel t1 type disk;
allocate auxiliary channel a1 device type disk;
release channel t1;
}
exit;
EOF

Please click here to download script <-----

[oracle@rac2 ~]$ chmod 775 restore_connectivity.sh

[oracle@rac2 ~]$ ./restore_connectivity.sh

RMAN> RMAN> RMAN> 2> 3> 4> 5> RMAN> [oracle@rac2 ~]$

Output

[oracle@rac2 ~]$ cat restore_connectivity.log

Recovery Manager: Release 11.2.0.4.0 - Production on Wed Nov 23 15:09:25 2016

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

RMAN>
connected to target database: BR8PROD (DBID=4192214970)

RMAN>
connected to auxiliary database: BR8DEV (not mounted)

RMAN> 2> 3> 4> 5>
using target database control file instead of recovery catalog
allocated channel: t1
channel t1: SID=25 device type=DISK

allocated channel: a1
channel a1: SID=23 device type=DISK

released channel: t1
released channel: a1

RMAN>

Recovery Manager complete.
[oracle@rac2 ~]$


11. Run RMAN Duplicate (On the auxiliary host)

[oracle@rac2 ~]$ cat restore.sh
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/11.2.0.4/db_1
ORACLE_SID=BR8DEV
BACKUP_LOG_PATH=/home/oracle
export ORACLE_BASE
export ORACLE_HOME
export ORACLE_SID
export BACKUP_LOG_PATH
LOG_FILE=${BACKUP_LOG_PATH}/restore_db.log
$ORACLE_HOME/bin/rman msglog=${LOG_FILE} <<EOF
connect target sys/sys@BR8PROD
connect auxiliary sys/sys@BR8DEV
run{
allocate channel t1 type disk;
allocate auxiliary channel a1 device type disk;
duplicate target database to BR8DEV from active database nofilenamecheck;
release channel t1;
}
exit;
EOF
[oracle@rac2 ~]$

Please click here to download the script <----

[oracle@rac2 ~]$ chmod 775 restore.sh
[oracle@rac2 ~]$

[oracle@rac2 ~]$ nohup ./restore.sh &
[1] 5483
[oracle@rac2 ~]$ nohup: appending output to `nohup.out'

[oracle@rac2 ~]$ jobs -l
[1]+  5483 Running                 nohup ./restore.sh &
[oracle@rac2 ~]$

tail -f restore_db.log <--- you can monitor progress

output

[oracle@rac2 ~]$ cat restore_db.log

Recovery Manager: Release 11.2.0.4.0 - Production on Wed Nov 23 16:09:06 2016

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

RMAN>
connected to target database: BR8PROD (DBID=4192214970)

RMAN>
connected to auxiliary database: BR8DEV (not mounted)

RMAN> 2> 3> 4> 5> 6>
using target database control file instead of recovery catalog
allocated channel: t1
channel t1: SID=50 device type=DISK

allocated channel: a1
channel a1: SID=24 device type=DISK

Starting Duplicate Db at 23-NOV-16

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    1269366784 bytes

Fixed Size                     2252864 bytes
Variable Size                822087616 bytes
Database Buffers             436207616 bytes
Redo Buffers                   8818688 bytes
allocated channel: a1
channel a1: SID=23 device type=DISK

contents of Memory Script:
{
   sql clone "alter system set  control_files =
  ''+DATA/br8dev/controlfile/current.260.928685355'', ''+ARCH/br8dev/controlfile/current.262.928685355'' comment=
 ''Set by RMAN'' scope=spfile";
   sql clone "alter system set  db_name =
 ''BR8PROD'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''BR8DEV'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   backup as copy current controlfile auxiliary format  '+DATA/br8dev/controlfile/current.261.928685355';
   restore clone controlfile to  '+ARCH/br8dev/controlfile/current.263.928685355' from
 '+DATA/br8dev/controlfile/current.261.928685355';
   sql clone "alter system set  control_files =
  ''+DATA/br8dev/controlfile/current.261.928685355'', ''+ARCH/br8dev/controlfile/current.263.928685355'' comment=
 ''Set by RMAN'' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  control_files =   ''+DATA/br8dev/controlfile/current.260.928685355'', ''+ARCH/br8dev/controlfile/current.262.928685355'' comment= ''Set by RMAN'' scope=spfile

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

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

Oracle instance shut down

Oracle instance started

Total System Global Area    1269366784 bytes

Fixed Size                     2252864 bytes
Variable Size                822087616 bytes
Database Buffers             436207616 bytes
Redo Buffers                   8818688 bytes
allocated channel: a1
channel a1: SID=23 device type=DISK

Starting backup at 23-NOV-16
channel t1: starting datafile copy
copying current control file
output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_BR8PROD.f tag=TAG20161123T160920 RECID=2 STAMP=928685361
channel t1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 23-NOV-16

Starting restore at 23-NOV-16

channel a1: copied control file copy
Finished restore at 23-NOV-16

sql statement: alter system set  control_files =   ''+DATA/br8dev/controlfile/current.261.928685355'', ''+ARCH/br8dev/controlfile/current.263.928685355'' comment= ''Set by RMAN'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1269366784 bytes

Fixed Size                     2252864 bytes
Variable Size                822087616 bytes
Database Buffers             436207616 bytes
Redo Buffers                   8818688 bytes
allocated channel: a1
channel a1: SID=23 device type=DISK

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

contents of Memory Script:
{
   set newname for datafile  1 to
 "+data";
   set newname for datafile  2 to
 "+data";
   set newname for datafile  3 to
 "+data";
   set newname for datafile  4 to
 "+data";
   set newname for datafile  5 to
 "+data";
   backup as copy reuse
   datafile  1 auxiliary format
 "+data"   datafile
 2 auxiliary format
 "+data"   datafile
 3 auxiliary format
 "+data"   datafile
 4 auxiliary format
 "+data"   datafile
 5 auxiliary format
 "+data"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 23-NOV-16
channel t1: starting datafile copy
input datafile file number=00001 name=+DATA1/br8prod/datafile/system.256.928633345
output file name=+DATA/br8dev/datafile/system.262.928685383 tag=TAG20161123T160941
channel t1: datafile copy complete, elapsed time: 00:00:46
channel t1: starting datafile copy
input datafile file number=00002 name=+DATA1/br8prod/datafile/sysaux.257.928633345
output file name=+DATA/br8dev/datafile/sysaux.263.928685427 tag=TAG20161123T160941
channel t1: datafile copy complete, elapsed time: 00:00:35
channel t1: starting datafile copy
input datafile file number=00005 name=+DATA1/br8prod/datafile/example.265.928633413
output file name=+DATA/br8dev/datafile/example.264.928685463 tag=TAG20161123T160941
channel t1: datafile copy complete, elapsed time: 00:00:25
channel t1: starting datafile copy
input datafile file number=00003 name=+DATA1/br8prod/datafile/undotbs1.258.928633345
output file name=+DATA/br8dev/datafile/undotbs1.265.928685487 tag=TAG20161123T160941
channel t1: datafile copy complete, elapsed time: 00:00:07
channel t1: starting datafile copy
input datafile file number=00004 name=+DATA1/br8prod/datafile/users.259.928633345
output file name=+DATA/br8dev/datafile/users.266.928685495 tag=TAG20161123T160941
channel t1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 23-NOV-16

sql statement: alter system archive log current

contents of Memory Script:
{
   backup as copy reuse
   archivelog like  "+FRA/br8prod/archivelog/2016_11_23/thread_1_seq_5.260.928685495" auxiliary format
 "+ARCH"   ;
   catalog clone start with  "+ARCH";
   switch clone datafile all;
}
executing Memory Script

Starting backup at 23-NOV-16
channel t1: starting archived log copy
input archived log thread=1 sequence=5 RECID=3 STAMP=928685496
output file name=+ARCH/br8dev/archivelog/2016_11_23/thread_1_seq_5.264.928685497 RECID=0 STAMP=0
channel t1: archived log copy complete, elapsed time: 00:00:03
Finished backup at 23-NOV-16

searching for all files that match the pattern +ARCH

List of Files Unknown to the Database
=====================================
File Name: +arch/BR8DEV/ARCHIVELOG/2016_11_23/thread_1_seq_5.264.928685497
File Name: +arch/BR8DEV/CONTROLFILE/Current.256.928682009
File Name: +arch/BR8DEV/CONTROLFILE/Current.257.928682009
File Name: +arch/BR8DEV/CONTROLFILE/Current.258.928683095
File Name: +arch/BR8DEV/CONTROLFILE/Current.259.928683095
File Name: +arch/BR8DEV/CONTROLFILE/Current.260.928684163
File Name: +arch/BR8DEV/CONTROLFILE/Current.261.928684163
File Name: +arch/BR8DEV/CONTROLFILE/Current.262.928685355
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: +arch/BR8DEV/ARCHIVELOG/2016_11_23/thread_1_seq_5.264.928685497

List of Files Which Where Not Cataloged
=======================================
File Name: +arch/BR8DEV/CONTROLFILE/Current.256.928682009
  RMAN-07517: Reason: The file header is corrupted
File Name: +arch/BR8DEV/CONTROLFILE/Current.257.928682009
  RMAN-07517: Reason: The file header is corrupted
File Name: +arch/BR8DEV/CONTROLFILE/Current.258.928683095
  RMAN-07517: Reason: The file header is corrupted
File Name: +arch/BR8DEV/CONTROLFILE/Current.259.928683095
  RMAN-07517: Reason: The file header is corrupted
File Name: +arch/BR8DEV/CONTROLFILE/Current.260.928684163
  RMAN-07517: Reason: The file header is corrupted
File Name: +arch/BR8DEV/CONTROLFILE/Current.261.928684163
  RMAN-07517: Reason: The file header is corrupted
File Name: +arch/BR8DEV/CONTROLFILE/Current.262.928685355
  RMAN-07517: Reason: The file header is corrupted

datafile 1 switched to datafile copy
input datafile copy RECID=2 STAMP=928685500 file name=+DATA/br8dev/datafile/system.262.928685383
datafile 2 switched to datafile copy
input datafile copy RECID=3 STAMP=928685500 file name=+DATA/br8dev/datafile/sysaux.263.928685427
datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=928685500 file name=+DATA/br8dev/datafile/undotbs1.265.928685487
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=928685500 file name=+DATA/br8dev/datafile/users.266.928685495
datafile 5 switched to datafile copy
input datafile copy RECID=6 STAMP=928685500 file name=+DATA/br8dev/datafile/example.264.928685463

contents of Memory Script:
{
   set until scn  996825;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 23-NOV-16

starting media recovery

archived log for thread 1 with sequence 5 is already on disk as file +ARCH/br8dev/archivelog/2016_11_23/thread_1_seq_5.264.928685497
archived log file name=+ARCH/br8dev/archivelog/2016_11_23/thread_1_seq_5.264.928685497 thread=1 sequence=5
media recovery complete, elapsed time: 00:00:00
Finished recover at 23-NOV-16
Oracle instance started

Total System Global Area    1269366784 bytes

Fixed Size                     2252864 bytes
Variable Size                822087616 bytes
Database Buffers             436207616 bytes
Redo Buffers                   8818688 bytes

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''BR8DEV'' comment=
 ''Reset to original value by RMAN'' scope=spfile";
   sql clone "alter system reset  db_unique_name scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

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

sql statement: alter system reset  db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    1269366784 bytes

Fixed Size                     2252864 bytes
Variable Size                822087616 bytes
Database Buffers             436207616 bytes
Redo Buffers                   8818688 bytes
allocated channel: a1
channel a1: SID=23 device type=DISK
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "BR8DEV" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP   1 ( '+data' ) SIZE 50 M  REUSE,
  GROUP   2 ( '+data' ) SIZE 50 M  REUSE,
  GROUP   3 ( '+data' ) SIZE 50 M  REUSE
 DATAFILE
  '+DATA/br8dev/datafile/system.262.928685383'
 CHARACTER SET WE8MSWIN1252


contents of Memory Script:
{
   set newname for tempfile  1 to
 "+data";
   switch clone tempfile all;
   catalog clone datafilecopy  "+DATA/br8dev/datafile/sysaux.263.928685427",
 "+DATA/br8dev/datafile/undotbs1.265.928685487",
 "+DATA/br8dev/datafile/users.266.928685495",
 "+DATA/br8dev/datafile/example.264.928685463";
   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=+DATA/br8dev/datafile/sysaux.263.928685427 RECID=1 STAMP=928685514
cataloged datafile copy
datafile copy file name=+DATA/br8dev/datafile/undotbs1.265.928685487 RECID=2 STAMP=928685514
cataloged datafile copy
datafile copy file name=+DATA/br8dev/datafile/users.266.928685495 RECID=3 STAMP=928685514
cataloged datafile copy
datafile copy file name=+DATA/br8dev/datafile/example.264.928685463 RECID=4 STAMP=928685514

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=928685514 file name=+DATA/br8dev/datafile/sysaux.263.928685427
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=928685514 file name=+DATA/br8dev/datafile/undotbs1.265.928685487
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=928685514 file name=+DATA/br8dev/datafile/users.266.928685495
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=928685514 file name=+DATA/br8dev/datafile/example.264.928685463

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

database opened
Finished Duplicate Db at 23-NOV-16

released channel: t1
released channel: a1

RMAN>

Recovery Manager complete.
[oracle@rac2 ~]$


12. Verify

SQL> select name, open_mode, dbid, created from v$database;

NAME      OPEN_MODE                  DBID CREATED
--------- -------------------- ---------- --------------------
BR8DEV    READ WRITE           3533049546 23-NOV-2016 16:11:54

SQL> select name from v$datafile
union
select name from v$controlfile
union
select member from v$logfile
union
select name from v$tempfile
/   2    3    4    5    6    7    8

NAME
--------------------------------------------------------------------------------
+ARCH/br8dev/controlfile/current.263.928685355
+DATA/br8dev/controlfile/current.261.928685355
+DATA/br8dev/datafile/example.264.928685463
+DATA/br8dev/datafile/sysaux.263.928685427
+DATA/br8dev/datafile/system.262.928685383
+DATA/br8dev/datafile/undotbs1.265.928685487
+DATA/br8dev/datafile/users.266.928685495
+DATA/br8dev/onlinelog/group_1.267.928685515
+DATA/br8dev/onlinelog/group_2.268.928685515
+DATA/br8dev/onlinelog/group_3.269.928685517
+DATA/br8dev/tempfile/temp.270.928685523

11 rows selected.

SQL>


13. Create the spfile in ASM disk.

SQL> show parameter pfile

NAME     TYPE        VALUE
-------- ----------- ------------------------------
spfile   string      /u01/app/oracle/product/11.2.0
                     .4/db_1/dbs/spfileBR8DEV.ora
SQL>

[oracle@rac2 ~]$ ls -ltr /u01/app/oracle/product/11.2.0.4/db_1/dbs/spfileBR8DEV.ora
ls: /u01/app/oracle/product/11.2.0.4/db_1/dbs/spfileBR8DEV.ora: No such file or directory
[oracle@rac2 ~]$

[oracle@rac2 ~]$ . oraenv
ORACLE_SID = [+ASM] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@rac2 ~]$ asmcmd
ASMCMD> cd DATA/BR8DEV
ASMCMD> ls -l
Type  Redund  Striped  Time             Sys  Name
                                        Y    CONTROLFILE/
                                        Y    DATAFILE/
                                        Y    ONLINELOG/
                                        Y    TEMPFILE/
ASMCMD> mkdir PARAMETERFILE
ASMCMD> cd PARAMETERFILE
ASMCMD> pwd
+DATA/BR8DEV/PARAMETERFILE
ASMCMD>

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2252864 bytes
Variable Size             805310400 bytes
Database Buffers          452984832 bytes
Redo Buffers                8818688 bytes
SQL>
SQL> show parameter pfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string
SQL>
SQL> create spfile='+DATA/BR8DEV/PARAMETERFILE/spfileBR8DEV.ora' from PFILE;

File created.

SQL>

ASMCMD> pwd
+DATA/BR8DEV/PARAMETERFILE
ASMCMD> ls -l
Type           Redund  Striped  Time             Sys  Name
PARAMETERFILE  UNPROT  COARSE   NOV 23 23:00:00  Y    spfile.271.928710147
                                                 N    spfileBR8DEV.ora => +DATA/BR8DEV/PARAMETERFILE/spfile.271.928710147
ASMCMD>

[oracle@rac2 dbs]$ cp initBR8DEV.ora initBR8DEV.ora.bkp

modify initBR8DEV.ora with spfile location 

[oracle@rac2 dbs]$ cat initBR8DEV.ora
SPFILE='+DATA/BR8DEV/PARAMETERFILE/spfileBR8DEV.ora'
[oracle@rac2 dbs]$

SQL> shut immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL>
SQL> startup;
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2252864 bytes
Variable Size             805310400 bytes
Database Buffers          452984832 bytes
Redo Buffers                8818688 bytes
ORA-00205: error in identifying control file, check alert log for more info


SQL> show parameter pfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/br8dev/parameterfile/spf
                                                 ilebr8dev.ora
SQL>

SQL> show parameter control

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      +DATA, +ARCH
control_management_pack_access       string      DIAGNOSTIC+TUNING
SQL>
SQL> ALTER SYSTEM SET CONTROL_FILES='+ARCH/br8dev/controlfile/current.263.928685355','+DATA/br8dev/controlfile/current.261.928685355' scope=spfile;

System altered.

SQL>

SQL> shut immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 1269366784 bytes
Fixed Size                  2252864 bytes
Variable Size             805310400 bytes
Database Buffers          452984832 bytes
Redo Buffers                8818688 bytes
Database mounted.
Database opened.
SQL>
SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
BR8DEV    READ WRITE

SQL>

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala

CATALOG

CATALOG DATABASE

I. How to Create an RMAN Recovery Catalog Database
II. How to do manual-backup-registration
III. How to export the Catalog
IV. Query RMAN CATALOG
V. Get CATALOG Version
V(1). Resync catalog
VI. Unregister database from catalog db
VII. DROP CATALOG
VIII. Useful views


I. How to Create an RMAN Recovery Catalog Database

Create the rman user that will be the owner of the rman catalog, on the catalog database. The catalog database is usually a small database it contains and maintains the metadata of all rman backups performed using the catalog.

Step 0: Environment

Target Database: w148p, Version: 11.2.0.3.0
Catalog Database: cat, Version: 11.2.0.3.0 , catalog user: rman

Step 1: Create user and grant privs

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
CAT       READ WRITE

SQL> CREATE USER rman IDENTIFIED BY cat
TEMPORARY TABLESPACE temp
DEFAULT TABLESPACE users
QUOTA UNLIMITED ON users;  2    3    4

User created.

SQL> GRANT RECOVERY_CATALOG_OWNER TO rman;

Grant succeeded.

SQL>

Step 2: Create Recovery Catalog

[oracle@rac2 ~]$ . oraenv
ORACLE_SID = [cata] ? cat
The Oracle base has been set to /u01/app/oracle

[oracle@rac2 ~]$ rman catalog rman/cat

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jul 20 12:38:59 2016

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

connected to recovery catalog database

RMAN> create catalog;

recovery catalog created

RMAN> exit


Recovery Manager complete.
[oracle@rac2 ~]$

Step 3: Register catalog database with target database

[oracle@rac2 ~]$ rman target sys/sys@w148p catalog rman/cat@cat

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jul 20 12:48:12 2016

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

connected to target database: W148P (DBID=3175693255)
connected to recovery catalog database

RMAN> REGISTER DATABASE;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN>

Step 4: Verify registration

[oracle@rac2 ~]$ rman target sys/sys@w148p catalog rman/cat@cat

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jul 20 12:48:12 2016

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

connected to target database: W148P (DBID=3175693255)
connected to recovery catalog database

RMAN> REPORT SCHEMA;

Report of database schema for database with db_unique_name W148P

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    700      SYSTEM               YES     +DATA/w148p/datafile/system.260.906753423
2    520      SYSAUX               NO      +DATA/w148p/datafile/sysaux.259.906753425
3    30       UNDOTBS1             YES     +DATA/w148p/datafile/undotbs1.258.906753425
4    5        USERS                NO      +DATA/w148p/datafile/users.256.906753425

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       +DATA/w148p/tempfile/temp.283.906753489

RMAN> exit


Recovery Manager complete.
[oracle@rac2 ~]$


II. How to do manual-backup-registration

Manual backups, made using Rman without a recovery catalog, can be cataloged and made available to the rman catalog.In this example a controlfile backup is made using rman in NOCATALOG mode, afterwards the backup is catalogued.

Step 1: Simulate the issue:

[oracle@rac2 ~]$ rman TARGET sys/sys@w148p NOCATALOG

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jul 20 14:22:30 2016

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

connected to target database: W148P (DBID=3175693255)
using target database control file instead of recovery catalog

RMAN> backup tablespace users;

Starting backup at 20-JUL-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=40 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=00004 name=+DATA/w148p/datafile/users.256.906753425
channel ORA_DISK_1: starting piece 1 at 20-JUL-16
channel ORA_DISK_1: finished piece 1 at 20-JUL-16
piece handle=/u01/app/oracle/product/11.2.0/db_1/dbs/04rb65de_1_1 tag=TAG20160720T142238 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 20-JUL-16

RMAN> exit


Recovery Manager complete.
[oracle@rac2 ~]$


Step 2: Verify

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
CAT       READ WRITE

SQL> select DB_NAME, BS_KEY, BACKUP_TYPE, STATUS from rman.rc_backup_datafile;

no rows selected

SQL>


Step 3: Manual registration

[oracle@rac2 ~]$ rman target sys/sys@w148p catalog rman/cat@cat

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jul 20 14:24:56 2016

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

connected to target database: W148P (DBID=3175693255)
connected to recovery catalog database

RMAN> CATALOG backuppiece '/u01/app/oracle/product/11.2.0/db_1/dbs/04rb65de_1_1';

cataloged backup piece
backup piece handle=/u01/app/oracle/product/11.2.0/db_1/dbs/04rb65de_1_1 RECID=7 STAMP=917706306

RMAN> list backup;


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


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
105     Full    1.30M      DISK        00:00:00     20-JUL-16
        BP Key: 113   Status: AVAILABLE  Compressed: NO  Tag: TAG20160720T142238
        Piece Name: /u01/app/oracle/product/11.2.0/db_1/dbs/04rb65de_1_1
  List of Datafiles in backup set 105
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  4       Full 1063017    20-JUL-16 +DATA/w148p/datafile/users.256.906753425

RMAN>


Step 4: Verify after manual registration

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
CAT       READ WRITE

SQL> select DB_NAME, BS_KEY, BACKUP_TYPE, STATUS from rman.rc_backup_datafile;

DB_NAME      BS_KEY B S
-------- ---------- - -
W148P           105 D A

SQL>


III. How to export the Catalog

The database holding the rman catalog should be protected against information loss, the minimum level of protection can be implemented by executing a full export of the catalog schema. In case of catalog loss an import of the schema on a new or existing database will make the catalog available again.

[oracle@rac2 ~]$ expdp \" / as sysdba\"  directory=DATA_PUMP_DIR schemas=rman dumpfile=rman_schemas.dmp logfile=rman_schemas.log

Export: Release 11.2.0.3.0 - Production on Wed Jul 20 14:49:58 2016

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  "/******** AS SYSDBA" directory=DATA_PUMP_DIR schemas=rman dumpfile=rman_schemas.dmp logfile=rman_schemas.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.562 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TYPE/TYPE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
. . exported "RMAN"."CFS"                                89.91 KB     157 rows
. . exported "RMAN"."AL"                                 15.19 KB       3 rows
. . exported "RMAN"."BDF"                                16.41 KB       1 rows
. . exported "RMAN"."BP"                                 14.84 KB       1 rows
. . exported "RMAN"."BS"                                 12.32 KB       1 rows
. . exported "RMAN"."CKP"                                8.710 KB       2 rows
. . exported "RMAN"."CONFIG"                             5.429 KB       1 rows
. . exported "RMAN"."DB"                                 5.820 KB       1 rows
. . exported "RMAN"."DBINC"                              7.484 KB       2 rows
. . exported "RMAN"."DF"                                 14.95 KB       4 rows
. . exported "RMAN"."NODE"                               20.60 KB       1 rows
. . exported "RMAN"."OFFR"                               8.789 KB       4 rows
. . exported "RMAN"."ORL"                                7.593 KB       3 rows
. . exported "RMAN"."RCVER"                              5.023 KB       1 rows
. . exported "RMAN"."RLH"                                8.726 KB       3 rows
. . exported "RMAN"."ROUT"                               25.75 KB     324 rows
. . exported "RMAN"."RSR"                                17.61 KB      32 rows
. . exported "RMAN"."RT"                                 7.828 KB       1 rows
. . exported "RMAN"."SITE_DFATT"                         6.015 KB       4 rows
. . exported "RMAN"."SITE_TFATT"                         8.265 KB       1 rows
. . exported "RMAN"."TF"                                 8.632 KB       1 rows
. . exported "RMAN"."TS"                                 9.640 KB       5 rows
. . exported "RMAN"."TSATT"                              7.515 KB       5 rows
. . exported "RMAN"."BCB"                                    0 KB       0 rows
. . exported "RMAN"."BCF"                                    0 KB       0 rows
. . exported "RMAN"."BCR"                                    0 KB       0 rows
. . exported "RMAN"."BRL"                                    0 KB       0 rows
. . exported "RMAN"."BSF"                                    0 KB       0 rows
. . exported "RMAN"."CCB"                                    0 KB       0 rows
. . exported "RMAN"."CCF"                                    0 KB       0 rows
. . exported "RMAN"."CDF"                                    0 KB       0 rows
. . exported "RMAN"."CONF"                                   0 KB       0 rows
. . exported "RMAN"."FB"                                     0 KB       0 rows
. . exported "RMAN"."GRSP"                                   0 KB       0 rows
. . exported "RMAN"."NRSP"                                   0 KB       0 rows
. . exported "RMAN"."RR"                                     0 KB       0 rows
. . exported "RMAN"."SCR"                                    0 KB       0 rows
. . exported "RMAN"."SCRL"                                   0 KB       0 rows
. . exported "RMAN"."TEMPRES"                                0 KB       0 rows
. . exported "RMAN"."VPC_DATABASES"                          0 KB       0 rows
. . exported "RMAN"."VPC_USERS"                              0 KB       0 rows
. . exported "RMAN"."XAL"                                    0 KB       0 rows
. . exported "RMAN"."XCF"                                    0 KB       0 rows
. . exported "RMAN"."XDF"                                    0 KB       0 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /u01/app/oracle/admin/cat/dpdump/rman_schemas.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:50:32

[oracle@rac2 ~]$


IV. Query RMAN CATALOG

Scrip 1: 

col OBJECT_TYPE for a20
col status for a15
set lines 180
select db_name, object_type, status, 
round((end_time - start_time) * 24 * 60, 2) duration_minutes,  
to_char(start_time, 'mm/dd/yyyy hh:mi:ss') start_time, 
to_char(end_time, 'mm/dd/yyyy hh:mi:ss') end_time,
round((input_bytes/(1024*1024*1024)),2) input_gb, 
round((output_bytes/(1024*1024*1024)),2) output_gb
from rc_rman_status
where operation = 'BACKUP';

Scrip 2:

Run below query on target database. <-- on Target database

sqlplus sys/sys@w148p as sysdba  
SELECT DBID FROM v$database;

Run below two commands on Catalog database.

SELECT DB_KEY FROM RC_DATABASE WHERE DBID='&DBID'; 

SELECT DBINC_KEY,BS_KEY, BACKUP_TYPE, COMPLETION_TIME
FROM RC_DATABASE_INCARNATION i, RC_BACKUP_SET b
WHERE i.DB_KEY='&DB_KEY'
AND i.DB_KEY = b.DB_KEY ;

Script 3: Run as catalog user

This script will query the RMAN catalog and report on the backup status of every database in the catalog.

set lines 80
set pages 250
ttitle "Daily Backup........"
select DB NAME,dbid,
NVL(TO_CHAR(max(backuptype_db),'DD/MM/YYYY HH24:MI'),'01/01/0001:00:00') DBBKP,
NVL(TO_CHAR(max(backuptype_arch),'DD/MM/YYYY HH24:MI'),'01/01/0001:00:00') ARCBKP
from (
select a.name DB,dbid,
decode(b.bck_type,'D',max(b.completion_time),'I',
max(b.completion_time)) BACKUPTYPE_db,
decode(b.bck_type,'L',
max(b.completion_time)) BACKUPTYPE_arch
from rc_database a,bs b
where a.db_key=b.db_key
and b.bck_type is not null
and b.bs_key not in(Select bs_key from rc_backup_controlfile
where AUTOBACKUP_DATE is not null or AUTOBACKUP_SEQUENCE is not null)
and b.bs_key not in(select bs_key from rc_backup_spfile)
group by a.name,dbid,b.bck_type
) group by db,dbid
ORDER BY least(to_date(DBBKP,'DD/MM/YYYY HH24:MI'),
to_date(ARCBKP,'DD/MM/YYYY HH24:MI'));

Script 4: No catalog

RMAN No catalog - get backup information from local controlfile

col OBJECT_TYPE for a20
col status for a15
select sid, object_type, status, 
round((end_time - start_time) * 24 * 60, 2) duration_minutes,  
to_char(start_time, 'mm/dd/yyyy hh:mi:ss') start_time, 
to_char(end_time, 'mm/dd/yyyy hh:mi:ss') end_time,
round((input_bytes/(1024*1024*1024)),2) input_gb, 
round((output_bytes/(1024*1024*1024)),2) output_gb
from v$rman_status
where operation = 'BACKUP';


V. Get CATALOG Version

[oracle@rac2 ~]$ sqlplus rman/cat@cat

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 20 15:58:25 2016

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


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

SQL> SELECT * FROM rcver;

VERSION
------------
11.02.00.03

SQL>

5.1
V(1)Resync catalog

Step 1: Take backup without catalog

[oracle@rac2 ~]$ rman TARGET sys/sys@w148p NOCATALOG

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jul 20 17:11:41 2016

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

connected to target database: W148P (DBID=3175693255)
using target database control file instead of recovery catalog

RMAN> backup spfile;

Starting backup at 20-JUL-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=47 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 20-JUL-16
channel ORA_DISK_1: finished piece 1 at 20-JUL-16
piece handle=/u01/app/oracle/product/11.2.0/db_1/dbs/07rb6fan_1_1 tag=TAG20160720T171151 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 20-JUL-16

RMAN> exit


Recovery Manager complete.
[oracle@rac2 ~]$

Step 2: Verify

[oracle@rac2 ~]$ sqlplus rman/cat@cat

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 20 17:11:01 2016

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


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

SQL> select count(*) from rc_backup_spfile;

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

SQL>

Step 3: Resync catalog

[oracle@rac2 ~]$ rman target sys/sys@w148p catalog rman/cat@cat

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jul 20 17:12:54 2016

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

connected to target database: W148P (DBID=3175693255)
connected to recovery catalog database

RMAN> resync catalog;

starting full resync of recovery catalog
full resync complete <----

RMAN> exit


Recovery Manager complete.
[oracle@rac2 ~]$

Step 4: Verify after resync

[oracle@rac2 ~]$ sqlplus rman/cat@cat

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 20 17:17:48 2016

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


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

SQL> select count(*) from rc_backup_spfile;

  COUNT(*)
----------
         1  <----

SQL>


VI.Unregister database from catalog db

If a database is not longer required on the rman catalog it can be unregistered.Note that rman metadata is always stored also on the local controlfile, if a database is unregistered from the catalog, it can be afterwards registered again and all backups contained in the controlfile will be cataloged

[oracle@rac2 ~]$ rman target sys/sys@w148p catalog rman/cat@cat

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jul 20 16:27:19 2016

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

connected to target database: W148P (DBID=3175693255)
connected to recovery catalog database

RMAN> UNREGISTER DATABASE;

database name is "W148P" and DBID is 3175693255

Do you really want to unregister the database (enter YES or NO)? YES
database unregistered from the recovery catalog

RMAN> exit


Recovery Manager complete.
[oracle@rac2 ~]$

[oracle@rac2 ~]$ sqlplus rman/cat@cat

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 20 16:30:51 2016

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


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

SQL> select NAME from rc_database;

no rows selected

SQL>


VII. DROP CATALOG

An rman catalog can be removed with the drop catalog command.

[oracle@rac2 ~]$ rman catalog rman/cat@cat

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jul 20 16:33:01 2016

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

connected to recovery catalog database

RMAN> DROP CATALOG;

recovery catalog owner is RMAN
enter DROP CATALOG command again to confirm catalog removal

RMAN> DROP CATALOG; <---

recovery catalog dropped

RMAN> exit


Recovery Manager complete.
[oracle@rac2 ~]$

[oracle@rac2 ~]$ sqlplus rman/cat@cat

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jul 20 16:39:19 2016

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


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

SQL> select * from tab;

no rows selected <--

SQL>


VIII. Useful views

rc_archived_log: Historical information about archived and non archived redo logs (Corresponds to the v$archived_log view).

rc_backup_controlfile: Contains information about control files in backup sets

rc_backup_corruption: Contains list of corrupted blocs in datafile backups. Corresponds to the v$backup_corruption view. 

rc_database_block_corruption: Lists information about data blocks that were corrupted after last backup.

rc_backup_datafile: Contains information about datafiles in backup sets.

rc_backup_piece: Lists information about backup pieces.

rc_backup_set: Contains information about backup sets of all incarnations of the database.

rc_backup_spfile: Lists information about server parameter file in backup sets.

rc_controlfile_copy: Lists information about controlfile copy in backup sets.

rc_database: Contains information about all databases that registered in the recovery catalog.

rc_database_incarnation: Lists information about all databases registered in the recovery catalog.

rc_datafile: Lists information about all datafiles registered in the recovery catalog.

rc_resync: Lists information about recovery catalog resynchronizations.

rc_rman_configuration: Contains information about RMAN configuration settings.

rc_rman_status: Contains information about all operations on all databases made by connecting to the recovery catalog.

rc_stored_script and rc_stored_script_line: Both contain information about the scripts that stored in the recovery catalog.

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Thank you,
Rajasekhar Amudala
Email: br8dba@gmail.com
WhatsApp : +
Linkedin: https://www.linkedin.com/in/rajasekhar-amudala/