Oracle 11 - RMAN Creating scripts inside catalog

A few examples creating, deleting and listing scripts stored inside an RMAN catalog.

Creating RMAN scripts inside rman catalog. 

GLOBAL scripts are available for all database instances connected to rman catalog.

RMAN> create global script
2> global_backup_db {backup database plus archivelog;}

created global script blobal_backup_db

Running previous script.

RMAN> run {execute global script global_backup_db;}

executing global script: global_backup_db

Starting backup at 30-JUL-2013 23:03:28
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 instance=RACDB1 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=2 sequence=11 RECID=1 STAMP=821038151
input archived log thread=1 sequence=83 RECID=2 STAMP=821038215
input archived log thread=1 sequence=84 RECID=3 STAMP=821043970
.
.
.
channel ORA_DISK_1: finished piece 1 at 30-JUL-2013 23:30:44
piece handle=+FRA/racdb/backupset/2013_07_30/annnf0_tag20130730t233041_0.300.822180641 tag=TAG20130730T233041 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 30-JUL-2013 23:30:44

Creating global script including tablespace variable

RMAN> create global script backup_ts
2> { backup tablespace &1; }

Enter value for 1: users

created global script backup_ts

Running global script with variable backup_ts

RMAN> run {execute global script backup_ts;}

executing global script: backup_ts

Enter value for 1: system

Starting backup at 30-JUL-2013 23:43:39
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/racdb/datafile/system.259.820185587
channel ORA_DISK_1: starting piece 1 at 30-JUL-2013 23:43:43

Listing scripts stored inside catalog. 

RMAN> list script names;

List of Stored Scripts in Recovery Catalog

    Global Scripts

       Script Name
       Description
       -----------------------------------------------------------------------
       backup_ts

       global_backup_db

Listing description

RMAN> print global script global_backup_db;

printing stored global script: global_backup_db
{backup database plus archivelog;}

Deleting script from catalog.

RMAN> delete global script backup_ts;

deleted global script: backup_ts

Oracle 11 - Creating RMAN Catalog

On this new post, information regarding how to create an RMAN catalog. It's a very easy process, with this step by step guide your RMAN catalog will be ready in 5 minutes.

Accomplishing this goal requires a dedicated database, in this example called RCAT, to store the rman catalog.

1.- Export ORACLE_SID to database instance name of RMAN catalog.

[oracle@racnode1 admin]$ export ORACLE_SID=RCAT1
[oracle@racnode1 admin]$ sqlplus / as sysdba

2.- Create tablespace where the catalog data will remain on RCAT instance.

SQL> create tablespace rman datafile '+DATA2' size 200m autoextend on;

Tablespace created.

3.- Create user to connect to catalog on RCAT instance.

SQL> create user rman identified by rman default tablespace rman quota unlimited on rman;

User created.

4.- Grant owner catalog privileges to rman user.

SQL> grant recovery_catalog_owner to rman;

Grant succeeded.

5.- Connect to catalog database with rman user and create the catalog.

[oracle@racnode1 admin]$ rman catalog rman/rman@RCAT

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Jul 30 21:53:18 2013

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

connected to recovery catalog database

RMAN> create catalog;

recovery catalog created

6.- You can ensure login on RMAN user that metadata has been added.

[oracle@racnode1 ~]$ export ORACLE_SID=RCAT1
[oracle@racnode1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Jul 30 22:09:29 2013

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

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

SQL> select object_name,object_type,status from dba_objects where owner='RMAN';

OBJECT_NAME OBJECT_TYPE     STATUS
---------------------------------------- ------------------- -------
RC_PROXY_DATAFILE VIEW     VALID
RC_PROXY_CONTROLFILE VIEW     VALID
RC_RMAN_CONFIGURATION VIEW     VALID
BCR TABLE     VALID
BCR_P INDEX     VALID
RC_DATABASE_BLOCK_CORRUPTION VIEW     VALID
RC_PROXY_ARCHIVEDLOG VIEW     VALID
RC_RESTORE_POINT VIEW     VALID
RC_RMAN_STATUS VIEW     VALID
RC_SITE  VIEW     VALID
BP_I_2 INDEX     VALID
BS_I_1 INDEX     VALID
.
.
.

7.- Registering database on catalog.

[oracle@racnode1 ~]$ rman target / catalog rman@RCAT

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Jul 30 22:14:05 2013

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

connected to target database: RACDB (DBID=817802211)
recovery catalog database Password: 
connected to recovery catalog database

RMAN> register database;

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

8.- Cataloging an existing flash recovery area.

RMAN> catalog recovery area noprompt;

searching for all files in the recovery area

List of Files Unknown to the Database
=====================================
File Name: +fra/RACDB/CONTROLFILE/Current.256.820185477
File Name: +fra/RACDB/CONTROLFILE/Current.nuevo
cataloging files...
no files cataloged

List of Files Which Where Not Cataloged
=======================================
File Name: +fra/RACDB/CONTROLFILE/Current.256.820185477
  RMAN-07519: Reason: Error while cataloging. See alert.log.
File Name: +fra/RACDB/CONTROLFILE/Current.nuevo
  RMAN-07519: Reason: Error while cataloging. See alert.log.

List of files in Recovery Area not managed by the database
==========================================================
File Name: +FRA/racdb/onlinelog/group_5.261.820970867
  RMAN-07527: Reason: File was not created using DB_RECOVERY_FILE_DEST initialization parameter
File Name: +FRA/racdb/onlinelog/group_6.262.820971003
  RMAN-07527: Reason: File was not created using DB_RECOVERY_FILE_DEST initialization parameter

number of files not managed by recovery area is 2, totaling 100.00MB

9.- If the catalog database stops temporally, or network problems blocks connectivity between target database and catalog database, could be necessary, re-syncing modifications made to controlfile against rman catalog once the problem is solved.

RMAN> resync catalog;

starting full resync of recovery catalog
full resync complete

Oracle RMAN DUPLICATE database

This practical case, shows how to easily duplicate a Oracle database using RMAN.

Starting with our database, instance named TEST, in open mode, we'll create all we need to start in nomount mode a second instance named TEST2, this one will be the cloned database, finally launching copy from RMAN utility to clone the database.

1.- The first step, creating password file for the new instance (TEST2).

[oracle@oracletest ~]$ cd $ORACLE_HOME/dbs

[oracle@oracletest dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwTEST2

Enter password for SYS: 

2.- New listener for TEST2 database.

Configure the listener.ora and tnsnames.ora files, to permit database TEST2 accepting connections.

[oracle@oracletest admin]$ cd $ORACLE_HOME/network/admin

[oracle@oracletest admin]$ vi listener.ora 

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = TEST)

      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db)

      (SID_NAME = TEST)

    )

    (SID_DESC =

      (GLOBAL_DBNAME = TEST2)

      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db)

      (SID_NAME = TEST2)

    )

  )

LISTENER =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = oracletest)(PORT = 1521))

  )

ADR_BASE_LISTENER = /u01/app/oracle

TRACE_LEVEL_LISTENER = USER

[oracle@oracletest admin]$ vi tnsnames.ora 

TEST =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = oracletest)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = TEST)

    )

  )

TEST2 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = oracletest)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = TEST2)

3.-  Create pfile.

[oracle@oracletest admin]$ cd $ORACLE_HOME/dbs

[oracle@oracletest dbs]$ vi initTEST2.ora

DB_NAME=TEST2

DB_BLOCK_SIZE=8192

CONTROL_FILES=(+DATA2/test2/controlfile/control01.ctl)

DB_FILE_NAME_CONVERT=(+DATA,+DATA2)

LOG_FILE_NAME_CONVERT=(+DATA,+DATA2)

Only these above parameters are in the new database pfile, DB_NAME specifies the new name for cloned instance, db_block_size is the block size for database, the next parameter indicates the control files location, and the last two parameters are for the naming conversion for datafiles and redo log files, the new ASM disk location for this files will be +DATA2 instead the original +DATA1.

Export ORACLE_SID, and run into nomount mode the new instance TEST2.

[oracle@oracletest dbs]$ export ORACLE_SID=TEST2

[oracle@oracletest dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Sep 29 22:41:19 2012

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

Connected to an idle instance.

SQL> startup nomount pfile='?/dbs/initTEST2.ora';

ORACLE instance started.

Total System Global Area  217157632 bytes

Fixed Size    2211928 bytes

Variable Size  159387560 bytes

Database Buffers   50331648 bytes

Redo Buffers    5226496 bytes

SQL> create spfile from pfile;

File created.

4.- Start origin database (TEST) in mount or open mode.

[oracle@oracletest ~]$ echo $ORACLE_SID

TEST

[oracle@oracletest ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Sep 29 22:45:17 2012

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

Connected to an idle instance.

SQL> startup mount;

ORACLE instance started.

Total System Global Area  313159680 bytes

Fixed Size    2212936 bytes

Variable Size  163580856 bytes

Database Buffers  142606336 bytes

Redo Buffers    4759552 bytes

Base de datos montada.

5.- Connect to RMAN and executing DUPLICATE command to start with the clone process.

Connect to RMAN on the origin database (TEST) , from here connecting to the new database using the following command: CONNECT AUXILIARY.

Once DUPLICATE command is running, RMAN is responsible for doing all the job.

[oracle@oracletest ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Sat Sep 29 22:46:32 2012

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

RMAN> connect target sys@TEST  

target database Password: 

connected to target database: TEST (DBID=2093094325, not open)

RMAN> connect auxiliary sys@TEST2

auxiliary database Password: 

connected to auxiliary database: TEST2 (not mounted)

RMAN> duplicate target database to TEST2 from active database;

Starting Duplicate Db at 29-SEP-2012 22:57:20

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=1 device type=DISK

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

Fixed Size                     2211928 bytes

Variable Size                159387560 bytes

Database Buffers              50331648 bytes

Redo Buffers                   5226496 bytes

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 = 

 ''TEST2'' comment=

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

   shutdown clone immediate;

   startup clone force nomount

   backup as copy current controlfile auxiliary format  '+DATA2/test2/controlfile/control01.ctl';

   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 =  ''TEST2'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area     217157632 bytes

Fixed Size                     2211928 bytes

Variable Size                159387560 bytes

Database Buffers              50331648 bytes

Redo Buffers                   5226496 bytes

Starting backup at 29-SEP-2012 22:59:41

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=25 device type=DISK

channel ORA_DISK_1: starting datafile copy

copying current control file

output file name=/u01/app/oracle/product/11.2.0/db/dbs/snapcf_TEST.f tag=TAG20120929T225952 RECID=15 STAMP=795308405

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35

Finished backup at 29-SEP-2012 23:00:28

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 

 "+data2";

   set newname for datafile  2 to 

 "+data2";

   set newname for datafile  3 to 

 "+data2";

   set newname for datafile  4 to 

 "+data2";

   backup as copy reuse

   datafile  1 auxiliary format 

 "+data2"   datafile 

 2 auxiliary format 

 "+data2"   datafile 

 3 auxiliary format 

 "+data2"   datafile 

 4 auxiliary format 

 "+data2"   ;

}

executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 29-SEP-2012 23:01:06

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

input datafile file number=00001 name=+DATA/test/datafile/system.276.794947707

output file name=+DATA2/test2/datafile/system.257.795308479 tag=TAG20120929T230107

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:25

channel ORA_DISK_1: starting datafile copy

input datafile file number=00002 name=+DATA/test/datafile/sysaux.277.794947709

output file name=+DATA2/test2/datafile/sysaux.256.795308565 tag=TAG20120929T230107

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05

channel ORA_DISK_1: starting datafile copy

input datafile file number=00003 name=+DATA/test/datafile/undotbs1.278.794947711

output file name=+DATA2/test2/datafile/undotbs1.259.795308631 tag=TAG20120929T230107

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25

channel ORA_DISK_1: starting datafile copy

input datafile file number=00004 name=+DATA/test/datafile/users.259.795088363

output file name=+DATA2/test2/datafile/users.260.795308657 tag=TAG20120929T230107

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15

Finished backup at 29-SEP-2012 23:04:21

contents of Memory Script:

{

   switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy RECID=15 STAMP=795308662 file name=+DATA2/test2/datafile/system.257.795308479

datafile 2 switched to datafile copy

input datafile copy RECID=16 STAMP=795308662 file name=+DATA2/test2/datafile/sysaux.256.795308565

datafile 3 switched to datafile copy

input datafile copy RECID=17 STAMP=795308662 file name=+DATA2/test2/datafile/undotbs1.259.795308631

datafile 4 switched to datafile copy

input datafile copy RECID=18 STAMP=795308663 file name=+DATA2/test2/datafile/users.260.795308657

contents of Memory Script:

{

   set until scn  1092216;

   recover

   clone database

   noredo

    delete archivelog

   ;

}

executing Memory Script

executing command: SET until clause

Starting recover at 29-SEP-2012 23:04:24

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=23 device type=DISK

Finished recover at 29-SEP-2012 23:04:39

contents of Memory Script:

{

   shutdown clone immediate;

   startup clone nomount;

   sql clone "alter system set  db_name = 

 ''TEST2'' 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

database dismounted

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area     217157632 bytes

Fixed Size                     2211928 bytes

Variable Size                159387560 bytes

Database Buffers              50331648 bytes

Redo Buffers                   5226496 bytes

sql statement: alter system set  db_name =  ''TEST2'' 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     217157632 bytes

Fixed Size                     2211928 bytes

Variable Size                159387560 bytes

Database Buffers              50331648 bytes

Redo Buffers                   5226496 bytes

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

  MAXLOGFILES     16

  MAXLOGMEMBERS      3

  MAXDATAFILES      100

  MAXINSTANCES     8

  MAXLOGHISTORY      292

 LOGFILE

  GROUP  1 ( '+data2' ) SIZE 50 M  REUSE,

  GROUP  2 ( '+data2' ) SIZE 50 M  REUSE,

  GROUP  3 ( '+data2' ) SIZE 50 M  REUSE

 DATAFILE

  '+DATA2/test2/datafile/system.257.795308479'

 CHARACTER SET AL32UTF8

contents of Memory Script:

{

   set newname for tempfile  1 to 

 "+data2";

   switch clone tempfile all;

   catalog clone datafilecopy  "+DATA2/test2/datafile/sysaux.256.795308565", 

 "+DATA2/test2/datafile/undotbs1.259.795308631", 

 "+DATA2/test2/datafile/users.260.795308657";

   switch clone datafile all;

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +data2 in control file

cataloged datafile copy

datafile copy file name=+DATA2/test2/datafile/sysaux.256.795308565 RECID=1 STAMP=795308819

cataloged datafile copy

datafile copy file name=+DATA2/test2/datafile/undotbs1.259.795308631 RECID=2 STAMP=795308819

cataloged datafile copy

datafile copy file name=+DATA2/test2/datafile/users.260.795308657 RECID=3 STAMP=795308820

datafile 2 switched to datafile copy

input datafile copy RECID=1 STAMP=795308819 file name=+DATA2/test2/datafile/sysaux.256.795308565

datafile 3 switched to datafile copy

input datafile copy RECID=2 STAMP=795308819 file name=+DATA2/test2/datafile/undotbs1.259.795308631

datafile 4 switched to datafile copy

input datafile copy RECID=3 STAMP=795308820 file name=+DATA2/test2/datafile/users.260.795308657

contents of Memory Script:

{

   Alter clone database open resetlogs;

}

executing Memory Script

database opened

Finished Duplicate Db at 29-SEP-2012 23:07:24