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

PRCR-1709 / ORA-01031 Insufficient privileges en Oracle Standalone ASM

Al realizar una instalación de Oracle Database 11g con ASM, previamente instalando Grid Infraestructure, con roles separados, por un lado Grid infraestructure con el usuario grid, y Oracle Database con usuario Oracle, nos podemos encontrar con un error como este al crear la base de datos.

PRCR-1079 : Failed to start resource ora.test.db
ORA-01031: insufficient privileges
ORA-01031: insufficient privileges
CRS-2674: Start of 'ora.test.db' on 'oracletest' failed

Sucede al intentar levantar automáticamente el recurso "ora.test.db" por parte del usuario grid, por falta de permisos, para solucionarlo debemos agregar al usuario grid al grupo "dba", o hacer toda la instalación (Grid Infraestructure y Oracle Database) con el mismo usuario.