Saturday, 17 October 2015

RMAN Duplicate in 12c

RMAN duplicate enchancement in 12c
-----------------------------------

Duplicate -  active duplicaiton using backup set / compresessd backup set / section size.
Duplicate -  noopen.

RMAN can transfer the files required for active database duplication as image copies or backup sets.

(Performing active database duplication using backup sets is available starting
with Oracle Database 12c Release 1 (12.1).)

When active database duplication is performed using image copies, after RMAN establishes a connection with the source database, the source database transfers the required database files to the auxiliary database.

Using image copies may require additional resources on the source database. This method is referred to
as the push-based method of active database duplication.


When RMAN performs active database duplication using backup sets, a connection is established with the source database and the auxiliary database. The auxiliary database then connects to the source database through Oracle Net Services and retrieves the required database files from the source database. This method of active database duplication is called as the pull-based method.

Using backup sets for active database duplication provides certain advantages. RMAN can employ unused block compression while creating backups, thus reducing the size of backups that are transported over the network.

Backup sets can be created in parallel on the source database by using multisection backups. You can also encrypt backup sets created on the source database.


RMAN only uses image copies to perform active database duplication when no auxiliary channels are allocated or when the number of auxiliary channels allocated is less than the number of target channels.

RMAN uses backup sets to perform active database duplication when the connection to the target database is established using a net service name and any one of the following conditions is satisfied:

The DUPLICATE ... FROM ACTIVE DATABASE command contains either the USING BACKUPSET, USING COMPRESSED BACKUPSET, or SECTION SIZE clause.

The number of auxiliary channels allocated is equal to or greater than the number of target channels allocated.

To duplicate the PDB pdb1 to the CDB cdb1, use the following command:
DUPLICATE DATABASE TO cdb1 PLUGGABLE DATABASE pdb1;

To duplicate the PDBs pdb1, pdb3, and pdb4 to the database cdb1, use the following command:
DUPLICATE DATABASE TO cdb1 PLUGGABLE DATABASE pdb1,pdb3,pdb4;

To duplicate all the databases in the CDB, except the PDB pdb3, use the following command:
DUPLICATE DATABASE TO cdb1 SKIP PLUGGABLE DATABASE pdb3;

To duplicate the users tablespace that is part of PDB pdb1, use the following command:
DUPLICATE DATABASE TO cdb1 TABLESPACE pdb1:users;

To duplicate the PDB pdb1 and the users tablespace in PDB pdb2, use the following command:
DUPLICATE DATABASE TO cdb1 PLUGGABLE DATABASE pdb1 TABLESPACE pdb2:users;



Duplicating non-cdb using pull method.
--------------------------------------

Source db - purchase
Target db - sales


On Target side


[oracle@standalone2 fast_recovery_area]$ mkdir -p /opt/oracle/product/admin/sales/adump
[oracle@standalone2 fast_recovery_area]$ mkdir -p /opt/oracle/oradata/sales



Prepare a pfile on target using using the contents of the source db. Below is the modified pfile on target side.

sales.__data_transfer_cache_size=0
sales.__db_cache_size=201326592
sales.__java_pool_size=159383552
sales.__large_pool_size=8388608
sales.__oracle_base='/u01/app/oraInventory'#ORACLE_BASE set from environment
sales.__pga_aggregate_target=373293056
sales.__sga_target=700448768
sales.__shared_io_pool_size=8388608
sales.__shared_pool_size=314572800
sales.__streams_pool_size=0
*.audit_file_dest='/opt/oracle/product/admin/sales/adump'
*.audit_trail='db'
*.compatible='12.1.0.0.0'
*.control_files='/opt/oracle/oradata/sales/control01.ctl','/opt/oracle/oradata/sales/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='sales'
*.db_recovery_file_dest='/opt/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4800m
*.diagnostic_dest='/opt/oracle/product'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=salesXDB)'
*.heat_map='ON'
*.java_pool_size=150M
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=616M
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.shared_pool_size=300M
*.undo_tablespace='UNDOTBS1'

[oracle@standalone2 dbs]$ cd $ORACLE_HOME/dbs
[oracle@standalone2 dbs]$ vi initsales.ora

-- Copy the contents of pfile and save.



Create the listener and register the db as static.

LISTENER_SALES =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = standalone2.manzoor.com)(PORT = 1521))
    )
  )

SID_LIST_LISTENER_SALES =
        (SID_LIST =
                (SID_DESC =
                (SID_NAME = PLSExtProc)
                (ORACLE_HOME = /opt/oracle/product/12.1.0.2/db_1)
                (PROGRAM = extproc)
                )
                (SID_DESC =
                (SID_NAME = sales )
                (ORACLE_HOME = /opt/oracle/product/12.1.0.2/db_1)
                )
        )


Create password file

$ orapwd file=orapwsales password=admin



[oracle@standalone2 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Sep 9 07:50:02 2015

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

Connected to an idle instance.

SQL> startup nomount;

Total System Global Area  645922816 bytes
Fixed Size                  2927720 bytes
Variable Size             603980696 bytes
Database Buffers           33554432 bytes
Redo Buffers                5459968 bytes
SQL> exit



Add the tns entries of source database in the target db.

PURCHASE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rhel11gr2rac1.manzoor.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = purchase)
    )
  )


SALES =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = standalone2.manzoor.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = sales)
    )
  )



[oracle@standalone2 admin]$ tnsping purchase

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 09-SEP-2015 08:03:50

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

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rhel11gr2rac1.manzoor.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = purchase)))
OK (20 msec)



[oracle@standalone2 admin]$ lsnrctl start LISTENER_SALES

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 09-SEP-2015 08:40:37

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

Starting /opt/oracle/product/12.1.0.2/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /opt/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Log messages written to /opt/oracle/diag/tnslsnr/standalone2/listener_sales/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=standalone2.manzoor.com)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_SALES
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                09-SEP-2015 08:40:37
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Listener Log File         /opt/oracle/diag/tnslsnr/standalone2/listener_sales/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=standalone2.manzoor.com)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "sales" has 1 instance(s).
  Instance "sales", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully



RMAN> run
 {
 allocate auxiliary channel a1 device type disk;
 allocate auxiliary channel a2 device type disk;
 allocate auxiliary channel a3 device type disk;
 duplicate target database to sales from active database using compressed backupset noopen
 db_file_name_convert '/u01/app/oracle/oradata/purchase','/opt/oracle/oradata/sales'
 logfile
      group 1 ( '/opt/oracle/oradata/sales/redo01.log') size 100M,
      group 2 ( '/opt/oracle/oradata/sales/redo02.log') size 100M,
      group 3 ( '/opt/oracle/oradata/sales/redo03.log') size 100M;
}

12>
using target database control file instead of recovery catalog
allocated channel: a1
channel a1: SID=22 device type=DISK

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

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

Starting Duplicate Db at 09-SEP-15
current log archived

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

Fixed Size                     2927720 bytes
Variable Size                603980696 bytes
Database Buffers              33554432 bytes
Redo Buffers                   5459968 bytes
allocated channel: a1
channel a1: SID=22 device type=DISK
allocated channel: a2
channel a2: SID=25 device type=DISK
allocated channel: a3
channel a3: SID=23 device type=DISK

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''PURCHASE'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''SALES'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone from service  'purchase' using compressed backupset
   primary controlfile;
   alter clone database mount;
}
executing Memory Script

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

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

Oracle instance shut down

Oracle instance started

Total System Global Area     645922816 bytes

Fixed Size                     2927720 bytes
Variable Size                603980696 bytes
Database Buffers              33554432 bytes
Redo Buffers                   5459968 bytes
allocated channel: a1
channel a1: SID=21 device type=DISK
allocated channel: a2
channel a2: SID=23 device type=DISK
allocated channel: a3
channel a3: SID=25 device type=DISK

Starting restore at 09-SEP-15

channel a1: starting datafile backup set restore
channel a1: using compressed network backup set from service purchase
channel a1: restoring control file
channel a1: restore complete, elapsed time: 00:00:04
output file name=/opt/oracle/oradata/sales/control01.ctl
output file name=/opt/oracle/oradata/sales/control02.ctl
Finished restore at 09-SEP-15

database mounted

contents of Memory Script:
{
   set newname for datafile  1 to
 "/opt/oracle/oradata/sales/system01.dbf";
   set newname for datafile  2 to
 "/opt/oracle/oradata/sales/active_data01.dbf";
   set newname for datafile  3 to
 "/opt/oracle/oradata/sales/sysaux01.dbf";
   set newname for datafile  4 to
 "/opt/oracle/oradata/sales/undotbs01.dbf";
   set newname for datafile  6 to
 "/opt/oracle/oradata/sales/users01.dbf";
   set newname for datafile  7 to
 "/opt/oracle/oradata/sales/read_only01.dbf";
   restore
   from service  'purchase'   using compressed backupset
   clone database
   ;
   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

executing command: SET NEWNAME

Starting restore at 09-SEP-15

channel a1: starting datafile backup set restore
channel a1: using compressed network backup set from service purchase
channel a1: specifying datafile(s) to restore from backup set
channel a1: restoring datafile 00001 to /opt/oracle/oradata/sales/system01.dbf
channel a2: starting datafile backup set restore
channel a2: using compressed network backup set from service purchase
channel a2: specifying datafile(s) to restore from backup set
channel a2: restoring datafile 00002 to /opt/oracle/oradata/sales/active_data01.dbf
channel a3: starting datafile backup set restore
channel a3: using compressed network backup set from service purchase
channel a3: specifying datafile(s) to restore from backup set
channel a3: restoring datafile 00003 to /opt/oracle/oradata/sales/sysaux01.dbf
channel a2: restore complete, elapsed time: 00:00:38
channel a2: starting datafile backup set restore
channel a2: using compressed network backup set from service purchase
channel a2: specifying datafile(s) to restore from backup set
channel a2: restoring datafile 00004 to /opt/oracle/oradata/sales/undotbs01.dbf
channel a1: restore complete, elapsed time: 00:07:54
channel a1: starting datafile backup set restore
channel a1: using compressed network backup set from service purchase
channel a1: specifying datafile(s) to restore from backup set
channel a1: restoring datafile 00006 to /opt/oracle/oradata/sales/users01.dbf
channel a1: restore complete, elapsed time: 00:00:05
channel a1: starting datafile backup set restore
channel a1: using compressed network backup set from service purchase
channel a1: specifying datafile(s) to restore from backup set
channel a1: restoring datafile 00007 to /opt/oracle/oradata/sales/read_only01.dbf
channel a1: restore complete, elapsed time: 00:00:17
channel a3: restore complete, elapsed time: 00:09:15
channel a2: restore complete, elapsed time: 00:09:58
Finished restore at 09-SEP-15

sql statement: alter system archive log current
current log archived

contents of Memory Script:
{
   restore clone force from service  'purchase' using compressed backupset
   archivelog from scn  7027426;
   switch clone datafile all;
}
executing Memory Script

Starting restore at 09-SEP-15

channel a1: starting archived log restore to default destination
channel a1: using compressed network backup set from service purchase
channel a1: restoring archived log
archived log thread=1 sequence=253
channel a2: starting archived log restore to default destination
channel a2: using compressed network backup set from service purchase
channel a2: restoring archived log
archived log thread=1 sequence=254
channel a1: restore complete, elapsed time: 00:00:02
channel a2: restore complete, elapsed time: 00:00:04
Finished restore at 09-SEP-15

datafile 1 switched to datafile copy
input datafile copy RECID=7 STAMP=889952838 file name=/opt/oracle/oradata/sales/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=889952838 file name=/opt/oracle/oradata/sales/active_data01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=9 STAMP=889952838 file name=/opt/oracle/oradata/sales/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=10 STAMP=889952838 file name=/opt/oracle/oradata/sales/undotbs01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=11 STAMP=889952838 file name=/opt/oracle/oradata/sales/users01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=12 STAMP=889952838 file name=/opt/oracle/oradata/sales/read_only01.dbf

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

executing command: SET until clause

Starting recover at 09-SEP-15

starting media recovery

archived log for thread 1 with sequence 253 is already on disk as file /opt/oracle/fast_recovery_area/SALES/archivelog/2015_09_09/o1_mf_1_253_byz1l2oc_.arc
archived log for thread 1 with sequence 254 is already on disk as file /opt/oracle/fast_recovery_area/SALES/archivelog/2015_09_09/o1_mf_1_254_byz1l4ll_.arc
archived log file name=/opt/oracle/fast_recovery_area/SALES/archivelog/2015_09_09/o1_mf_1_253_byz1l2oc_.arc thread=1 sequence=253
archived log file name=/opt/oracle/fast_recovery_area/SALES/archivelog/2015_09_09/o1_mf_1_254_byz1l4ll_.arc thread=1 sequence=254
media recovery complete, elapsed time: 00:00:17
Finished recover at 09-SEP-15
Oracle instance started

Total System Global Area     645922816 bytes

Fixed Size                     2927720 bytes
Variable Size                603980696 bytes
Database Buffers              33554432 bytes
Redo Buffers                   5459968 bytes

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

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

sql statement: alter system reset  db_unique_name scope=spfile
Oracle instance started

Total System Global Area     645922816 bytes

Fixed Size                     2927720 bytes
Variable Size                603980696 bytes
Database Buffers              33554432 bytes
Redo Buffers                   5459968 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "SALES" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP   1 ( '/opt/oracle/oradata/sales/redo01.log' ) SIZE 100 M ,
  GROUP   2 ( '/opt/oracle/oradata/sales/redo02.log' ) SIZE 100 M ,
  GROUP   3 ( '/opt/oracle/oradata/sales/redo03.log' ) SIZE 100 M
 DATAFILE
  '/opt/oracle/oradata/sales/system01.dbf'
 CHARACTER SET AL32UTF8


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

executing command: SET NEWNAME

renamed tempfile 1 to /opt/oracle/oradata/sales/temp01.dbf in control file

cataloged datafile copy
datafile copy file name=/opt/oracle/oradata/sales/active_data01.dbf RECID=1 STAMP=889952902
cataloged datafile copy
datafile copy file name=/opt/oracle/oradata/sales/sysaux01.dbf RECID=2 STAMP=889952902
cataloged datafile copy
datafile copy file name=/opt/oracle/oradata/sales/undotbs01.dbf RECID=3 STAMP=889952902
cataloged datafile copy
datafile copy file name=/opt/oracle/oradata/sales/users01.dbf RECID=4 STAMP=889952902
cataloged datafile copy
datafile copy file name=/opt/oracle/oradata/sales/read_only01.dbf RECID=5 STAMP=889952902

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=889952902 file name=/opt/oracle/oradata/sales/active_data01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=889952902 file name=/opt/oracle/oradata/sales/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=889952902 file name=/opt/oracle/oradata/sales/undotbs01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=4 STAMP=889952902 file name=/opt/oracle/oradata/sales/users01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=5 STAMP=889952902 file name=/opt/oracle/oradata/sales/read_only01.dbf
Leaving database unopened, as requested
Cannot remove created server parameter file

Finished Duplicate Db at 09-SEP-15

No comments:

Post a Comment