Duplicate (Clone) Oracle Database to the Same Machine (同一机器数据库还原)(一)

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