ANBOB™

A powerful feature of RMAN is the ability to duplicate (clone), a database from a backup. It is possible to create a duplicate database on:

• 1,A remote server with the same file structure
• 2,A remote server with a different file structure
• 3,The local server with a different file structure

Starting with Oracle 11g there are now two ways to clone a database:

• 1. Active Database Duplication
• 2. Backup-based Duplication

I will demo the local server with a different file structure 2 ways . db version 11.2.0.4 , on linux

Details about this example:
Primary db – pora58
Auxiliary db – new58

TNS alias for Primary – db1
TNS alias for Auxiliary – new1

Create the Oracle Password file for the Auxiliary Instance

$ orapwd file=orapw password=password ignorecase=y entries=5

Update listener.ora and tnsnames.ora for Auxiliary INSTANCE,Set up the Auxiliary Instance’s listener with a static reference to the Auxiliary database instance

$ cd $ORACLE_HOME/network/admin/

$ cat listener.ora

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbserver58)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /oracle/product/11.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = new58)
(ORACLE_HOME = /oracle/product/11.2.0/db_1)
(SID_NAME = new58)
)
)

ADR_BASE_LISTENER = /oracle

$ cat tnsnames.ora

db1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.212.58)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pora58)
)
)

new1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.212.58)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = new58)
)
)

Create audit file path for Auxiliary Instance

To check audit_file_dest parameter value in Primary instance

SQL> show parameter audit_file

PARAMETER_NAME TYPE VALUE
———————- ———– —————-
audit_file_dest string /oracle/admin/pora58/adump

$ mkdir -p /oracle/admin/new58/adump

make sure Auxiliary Instance server pfile not exists in ORACLE_HOME at this time.

A.Active Database Duplication

[oracle@dbserver58 admin]$ rman target /

RMAN> startup nomount force
RMAN> exit

$ rman target sys/oracle@db1 auxiliary sys/oracle@new1

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Nov 11 09:59:26 2014

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

connected to target database: PORA58 (DBID=745023158)
connected to auxiliary database: DUMMY (not mounted)

RMAN> run{
allocate channel prmy1 type disk;
allocate auxiliary channel stby type disk;
duplicate target database TO new58 FROM ACTIVE DATABASE
spfile
parameter_value_convert 'pora58','new58'
set db_unique_name='new58'
set db_file_name_convert='/oradata/pora58/','/oradata/new58/'
set log_file_name_convert='/oradata/pora58/','/oradata/new58/'
set control_files='/oradata/new58/control01.ctl','/oradata/new58/control02.ctl'
set log_archive_max_processes='5'
;
}

using target database control file instead of recovery catalog
allocated channel: prmy1
channel prmy1: SID=4 device type=DISK

allocated channel: stby
channel stby: SID=396 device type=DISK

Starting Duplicate Db at 11-NOV-14

contents of Memory Script:
{
backup as copy reuse
targetfile '/oracle/product/11.2.0/db_1/dbs/spfilepora58.ora' auxiliary format
'/oracle/product/11.2.0/db_1/dbs/spfilenew58.ora' ;
sql clone "alter system set spfile= ''/oracle/product/11.2.0/db_1/dbs/spfilenew58.ora''";
}
executing Memory Script

Starting backup at 11-NOV-14
Finished backup at 11-NOV-14

sql statement: alter system set spfile= ''/oracle/product/11.2.0/db_1/dbs/spfilenew58.ora''

contents of Memory Script:
{
sql clone "alter system set db_name =
''NEW58'' comment=
''duplicate'' scope=spfile";
sql clone "alter system set audit_file_dest =
''/oracle/admin/new58/adump'' comment=
'''' scope=spfile";
sql clone "alter system set dispatchers =
''(PROTOCOL=TCP) (SERVICE=new58XDB)'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_format =
''new58_%t_%s_%r.arc'' comment=
'''' scope=spfile";
sql clone "alter system set db_unique_name =
''new58'' comment=
'''' scope=spfile";
sql clone "alter system set db_file_name_convert =
''/oradata/pora58/'', ''/oradata/new58/'' comment=
'''' scope=spfile";
sql clone "alter system set log_file_name_convert =
''/oradata/pora58/'', ''/oradata/new58/'' comment=
'''' scope=spfile";
sql clone "alter system set control_files =
''/oradata/new58/control01.ctl'', ''/oradata/new58/control02.ctl'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_max_processes =
5 comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script

sql statement: alter system set db_name = ''NEW58'' comment= ''duplicate'' scope=spfile

sql statement: alter system set audit_file_dest = ''/oracle/admin/new58/adump'' comment= '''' scope=spfile

sql statement: alter system set dispatchers = ''(PROTOCOL=TCP) (SERVICE=new58XDB)'' comment= '''' scope=spfile

sql statement: alter system set log_archive_format = ''new58_%t_%s_%r.arc'' comment= '''' scope=spfile

sql statement: alter system set db_unique_name = ''new58'' comment= '''' scope=spfile

sql statement: alter system set db_file_name_convert = ''/oradata/pora58/'', ''/oradata/new58/'' comment= '''' scope=spfile

sql statement: alter system set log_file_name_convert = ''/oradata/pora58/'', ''/oradata/new58/'' comment= '''' scope=spfile

sql statement: alter system set control_files = ''/oradata/new58/control01.ctl'', ''/oradata/new58/control02.ctl'' comment= '''' scope=spfile

sql statement: alter system set log_archive_max_processes = 5 comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 6012973056 bytes

Fixed Size 2264176 bytes
Variable Size 1711276944 bytes
Database Buffers 4294967296 bytes
Redo Buffers 4464640 bytes
allocated channel: stby
channel stby: SID=1423 device type=DISK

contents of Memory Script:
{
sql clone "alter system set db_name =
''PORA58'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''NEW58'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
backup as copy current controlfile auxiliary format '/oradata/new58/control01.ctl';
restore clone controlfile to '/oradata/new58/control02.ctl' from
'/oradata/new58/control01.ctl';
alter clone database mount;
}
executing Memory Script

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

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

Oracle instance shut down

Oracle instance started

Total System Global Area 6012973056 bytes

Fixed Size 2264176 bytes
Variable Size 1711276944 bytes
Database Buffers 4294967296 bytes
Redo Buffers 4464640 bytes
allocated channel: stby
channel stby: SID=1423 device type=DISK

Starting backup at 11-NOV-14
channel prmy1: starting datafile copy
copying current control file
output file name=/oracle/product/11.2.0/db_1/dbs/snapcf_pora58.f tag=TAG20141111T095954 RECID=3 STAMP=863344794
channel prmy1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 11-NOV-14

Starting restore at 11-NOV-14

channel stby: copied control file copy
Finished restore at 11-NOV-14

database mounted

contents of Memory Script:
{
set newname for datafile 1 to
"/oradata/new58/system01.dbf";
set newname for datafile 2 to
"/oradata/new58/sysaux01.dbf";
set newname for datafile 3 to
"/oradata/new58/undotbs01.dbf";
set newname for datafile 4 to
"/oradata/new58/users01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/oradata/new58/system01.dbf" datafile
2 auxiliary format
"/oradata/new58/sysaux01.dbf" datafile
3 auxiliary format
"/oradata/new58/undotbs01.dbf" datafile
4 auxiliary format
"/oradata/new58/users01.dbf" ;
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

Starting backup at 11-NOV-14
channel prmy1: starting datafile copy
input datafile file number=00002 name=/oradata/pora58/sysaux01.dbf
output file name=/oradata/new58/sysaux01.dbf tag=TAG20141111T100000
channel prmy1: datafile copy complete, elapsed time: 00:00:15
channel prmy1: starting datafile copy
input datafile file number=00004 name=/oradata/pora58/users01.dbf
output file name=/oradata/new58/users01.dbf tag=TAG20141111T100000
channel prmy1: datafile copy complete, elapsed time: 00:00:07
channel prmy1: starting datafile copy
input datafile file number=00001 name=/oradata/pora58/system01.dbf
output file name=/oradata/new58/system01.dbf tag=TAG20141111T100000
channel prmy1: datafile copy complete, elapsed time: 00:00:07
channel prmy1: starting datafile copy
input datafile file number=00003 name=/oradata/pora58/undotbs01.dbf
output file name=/oradata/new58/undotbs01.dbf tag=TAG20141111T100000
channel prmy1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 11-NOV-14

sql statement: alter system archive log current

contents of Memory Script:
{
backup as copy reuse
archivelog like "/oradata/arch/pora58_1_52_860491320.arc" auxiliary format
"/oradata/arch/new58_1_52_860491320.arc" ;
catalog clone archivelog "/oradata/arch/new58_1_52_860491320.arc";
switch clone datafile all;
}
executing Memory Script

Starting backup at 11-NOV-14
channel prmy1: starting archived log copy
input archived log thread=1 sequence=52 RECID=44 STAMP=863344831
output file name=/oradata/arch/new58_1_52_860491320.arc RECID=0 STAMP=0
channel prmy1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 11-NOV-14

cataloged archived log
archived log file name=/oradata/arch/new58_1_52_860491320.arc RECID=44 STAMP=863344833

datafile 1 switched to datafile copy
input datafile copy RECID=3 STAMP=863344833 file name=/oradata/new58/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=4 STAMP=863344833 file name=/oradata/new58/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=863344833 file name=/oradata/new58/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=863344833 file name=/oradata/new58/users01.dbf

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

executing command: SET until clause

Starting recover at 11-NOV-14

starting media recovery

archived log for thread 1 with sequence 52 is already on disk as file /oradata/arch/new58_1_52_860491320.arc
archived log file name=/oradata/arch/new58_1_52_860491320.arc thread=1 sequence=52
media recovery complete, elapsed time: 00:00:00
Finished recover at 11-NOV-14
Oracle instance started

Total System Global Area 6012973056 bytes

Fixed Size 2264176 bytes
Variable Size 1711276944 bytes
Database Buffers 4294967296 bytes
Redo Buffers 4464640 bytes

contents of Memory Script:
{
sql clone "alter system set db_name =
''NEW58'' 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 = ''NEW58'' 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 6012973056 bytes

Fixed Size 2264176 bytes
Variable Size 1711276944 bytes
Database Buffers 4294967296 bytes
Redo Buffers 4464640 bytes
allocated channel: stby
channel stby: SID=1423 device type=DISK
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "NEW58" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '/oradata/new58/redo01.rlog' ) SIZE 200 M REUSE,
GROUP 2 ( '/oradata/new58/redo02.rlog' ) SIZE 200 M REUSE,
GROUP 3 ( '/oradata/new58/redo03.rlog' ) SIZE 200 M REUSE,
GROUP 4 ( '/oradata/new58/redo04.rlog' ) SIZE 200 M REUSE
DATAFILE
'/oradata/new58/system01.dbf'
CHARACTER SET ZHS16GBK

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

executing command: SET NEWNAME

renamed tempfile 1 to /oradata/new58/temp01.dbf in control file

cataloged datafile copy
datafile copy file name=/oradata/new58/sysaux01.dbf RECID=1 STAMP=863344848
cataloged datafile copy
datafile copy file name=/oradata/new58/undotbs01.dbf RECID=2 STAMP=863344848
cataloged datafile copy
datafile copy file name=/oradata/new58/users01.dbf RECID=3 STAMP=863344848

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=863344848 file name=/oradata/new58/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=863344848 file name=/oradata/new58/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=863344848 file name=/oradata/new58/users01.dbf
Reenabling controlfile options for auxiliary database
Executing: alter database add supplemental log data
Executing: alter database force logging

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

database opened
Finished Duplicate Db at 11-NOV-14
released channel: prmy1
released channel: stby

RMAN> exit

Know issue:

Starting Duplicate Db at 11-NOV-14
released channel: prmy1
released channel: stby
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 11/11/2014 09:49:02
RMAN-05501: aborting duplication of target database
RMAN-05537: DUPLICATE without TARGET connection when auxiliary instance is started with spfile cannot use SPFILE clause

CAUSE: control file is wrong, as path not exists. etc..

I will to demo 2. Backup-based Duplication in next post.

Reference http://www.pafumi.net/Clone_11g_with_RMAN_Active_Command.html

打赏

微信扫一扫，打赏作者吧～