oracle11g（oracle12c通用）使用active duplicate活动复制创建备库（数据库迁移）
1、库环境说明
target库：172.16.24.144 db_name=cc,db_unique_name=cc
库是完整的，启动到open阶段
SQL> select instance_name,status from v$instance ;
INSTANCE_NAME STATUS
---------------- ------------
cc OPEN
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string cc
SQL> show parameter db_unique_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string cc
auxiliary库:172.16.24.145 db_name=cc,db_unique_name=ccaux 注意：db_unique_name跟target库不同
通过先手工配置一个初始化参数，把auxiliary库启动到nomount阶段
vi $ORACLE_HOME/dbs/initcc.ora
db_name='cc'
db_unique_name='ccaux'
这两个参数即可，保存退出。
创建auxiliary库的密码文件，sys密码跟target库设置一致。
cd $ORACLE_HOME/dbs
orapwd password=oracle file=orapwcc entries=20
axiliary库起到nomount阶段
export ORACLE_SID=cc
sqlplus / as sysdba
startup nomount
2、监听和tns配置
target库：
listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = cc)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.24.144)(PORT = 1521))
)
tnsnames.ora
cc =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.24.144)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cc)
)
)
ccaux =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.24.145)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cc)
)
)
aux库：
listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = cc)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.24.145)(PORT = 1521))
)
tnsnames.ora
cc =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.24.144)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cc)
)
)
ccaux =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.24.145)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cc)
)
)
3、在主库开归档模式
export ORACLE_SID=cc
sqlplus / as sysdba
shutdown immediate
startup mount
SQL> alter system set log_archive_dest_1='location=/oradata/arch' ;
System altered.
SQL> alter database archivelog ;
Database altered.
SQL> archive log list ;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oradata/arch
Oldest online log sequence 23
Next log sequence to archive 25
Current log sequence 25
alter database open ;
4、在target库上创建一个测试用户和一个测试表
SQL> create user wufan identified by wufan ;
User created.
SQL> grant resource,connect to wufan ;
Grant succeeded.
SQL> grant unlimited tablespace to wufan ;
Grant succeeded.
SQL> grant alter session to wufan ;
Grant succeeded.
SQL> conn wufan/wufan;
Connected.
SQL> create table tmp_list_001(id int,name varchar2(10));
Table created.
SQL> insert into tmp_list_001 values(1,'AAAAAA');
1 row created.
SQL> commit ;
Commit complete.
SQL> select * from tmp_list_001 ;
ID NAME
---------- ----------
1 AAAAAA
5、在target（auxiliary库也行）库上rman登录两个库
rman target sys/[email protected] auxiliary sys/[email protected]
[email protected][/oracle]$rman target sys/[email protected] auxiliary sys/[email protected]
Recovery Manager: Release 12.1.0.2.0 - Production on Sat Oct 20 16:27:45 2018
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: CC (DBID=2201514723)
connected to auxiliary database: CC (not mounted)
RMAN>
6、执行脚本
在auxiliary库上建相关目录
target库：
SQL> show parameter audit
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest string /oracle/admin/cc/adump
audit_sys_operations boolean TRUE
audit_syslog_level string
audit_trail string DB
unified_audit_sga_queue_size integer 1048576
SQL> show parameter db_recovery_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /oradata/fast_recovery_area
db_recovery_file_dest_size big integer 4815M
在auxiliary库上创建必要的目录：
mkdir -p /oracle/admin/cc/adump
mkdir -p /oradata/fast_recovery_area
run{
duplicate target database
to cc
from active database
db_file_name_convert '/oradata/cc','/oradata/cc/standby/dbf'
spfile
parameter_value_convert '/oracle/product/12c','/oradata/cc/standby/spfile'
set db_unique_name='ccaux'
set log_file_name_convert '/oradata/cc','/oradata/cc/standby/redo'
set control_files='/oradata/cc/standby/control/ccaux01.ctl','/oradata/cc/standby/control/ccaux02.ctl'
set log_archive_dest_1='location=/oradata/cc/standby/archive' ;
}
注：关于db_file_name_convert 参数的特别说明：
https://docs.oracle.com/cd/B28359_01/backup.111/b28273/rcmsynta020.htm#RCMRF126
在所有匹配的初始化参数值中用第一个字符串替换第一个字符串。注意，DB_FILE_NAME_CONVERT和LOG_FILE_NAME_CONVERT是对该规则的例外，不受影响。
可以使用PARAMETER_VALUE_CONVERT设置初始化参数值的集合，并避免显式设置它们。例如，如果源数据库使用磁盘组+ALPHA，而备用数据库将使用+BETA，那么可以通过指定SPFILE PARAMETER_VALUE_CONVERT('+ALHPA','+BETA')来修改引用这些磁盘组的所有参数。
注意：参数值在PARAMETER_VALUE_CONVERT中区分大小写，即使相同的值在直接在初始化参数文件或spfile文件中设置时可能不区分大小写。
======================================================================================================================================
RMAN> run{
2> duplicate target database
3> to cc
4> from active database
5> db_file_name_convert '/oradata/cc','/oradata/cc/standby/dbf'
6> spfile
7> parameter_value_convert '/oracle/product/12c','/oradata/cc/standby/spfile'
8> set db_unique_name='ccaux'
9> set log_file_name_convert '/oradata/cc','/oradata/cc/standby/redo'
10> set control_files='/oradata/cc/standby/control/ccaux01.ctl','/oradata/cc/standby/control/ccaux02.ctl'
11> set log_archive_dest_1='location=/oradata/cc/standby/archive' ;
12> }
Starting Duplicate Db at 20-OCT-18
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=173 device type=DISK
current log archived
contents of Memory Script:
{
restore clone from service 'cc' spfile to
'/oracle/product/12c/dbs/spfilecc.ora';
sql clone "alter system set spfile= ''/oracle/product/12c/dbs/spfilecc.ora''";
}
executing Memory Script
Starting restore at 20-OCT-18
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cc
channel ORA_AUX_DISK_1: restoring SPFILE
output file name=/oracle/product/12c/dbs/spfilecc.ora
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 20-OCT-18
sql statement: alter system set spfile= ''/oracle/product/12c/dbs/spfilecc.ora''
contents of Memory Script:
{
sql clone "alter system set db_name =
''CC'' comment=
''duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''ccaux'' comment=
'''' scope=spfile";
sql clone "alter system set log_file_name_convert =
''/oradata/cc'', ''/oradata/cc/standby/redo'' comment=
'''' scope=spfile";
sql clone "alter system set control_files =
''/oradata/cc/standby/control/ccaux01.ctl'', ''/oradata/cc/standby/control/ccaux02.ctl'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_1 =
''location=/oradata/cc/standby/archive'' comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set db_name = ''CC'' comment= ''duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''ccaux'' comment= '''' scope=spfile
sql statement: alter system set log_file_name_convert = ''/oradata/cc'', ''/oradata/cc/standby/redo'' comment= '''' scope=spfile
sql statement: alter system set control_files = ''/oradata/cc/standby/control/ccaux01.ctl'', ''/oradata/cc/standby/control/ccaux02.ctl'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_1 = ''location=/oradata/cc/standby/archive'' comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 2499805184 bytes
Fixed Size 2927432 bytes
Variable Size 822084792 bytes
Database Buffers 1660944384 bytes
Redo Buffers 13848576 bytes
contents of Memory Script:
{
sql clone "alter system set db_name =
''CC'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone from service 'cc' primary controlfile;
alter clone database mount;
}
executing Memory Script
sql statement: alter system set db_name = ''CC'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 2499805184 bytes
Fixed Size 2927432 bytes
Variable Size 822084792 bytes
Database Buffers 1660944384 bytes
Redo Buffers 13848576 bytes
Starting restore at 20-OCT-18
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=12 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cc
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/oradata/cc/standby/control/ccaux01.ctl
output file name=/oradata/cc/standby/control/ccaux02.ctl
Finished restore at 20-OCT-18
database mounted
contents of Memory Script:
{
set newname for datafile 1 to
"/oradata/cc/standby/dbf/system01.dbf";
set newname for datafile 2 to
"/oradata/cc/standby/dbf/sysaux01.dbf";
set newname for datafile 3 to
"/oradata/cc/standby/dbf/undotbs01.dbf";
set newname for datafile 4 to
"/oradata/cc/standby/dbf/users01.dbf";
restore
from service 'cc' 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
Starting restore at 20-OCT-18
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cc
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /oradata/cc/standby/dbf/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cc
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00002 to /oradata/cc/standby/dbf/sysaux01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cc
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to /oradata/cc/standby/dbf/undotbs01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service cc
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to /oradata/cc/standby/dbf/users01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 20-OCT-18
sql statement: alter system archive log current
current log archived
contents of Memory Script:
{
restore clone force from service 'cc'
archivelog from scn 344329;
switch clone datafile all;
}
executing Memory Script
Starting restore at 20-OCT-18
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service cc
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=27
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service cc
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=28
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 20-OCT-18
datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=990059204 file name=/oradata/cc/standby/dbf/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=990059204 file name=/oradata/cc/standby/dbf/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=990059204 file name=/oradata/cc/standby/dbf/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=990059204 file name=/oradata/cc/standby/dbf/users01.dbf
contents of Memory Script:
{
set until scn 344643;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 20-OCT-18
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 27 is already on disk as file /oradata/cc/standby/archive/1_27_990017315.dbf
archived log for thread 1 with sequence 28 is already on disk as file /oradata/cc/standby/archive/1_28_990017315.dbf
archived log file name=/oradata/cc/standby/archive/1_27_990017315.dbf thread=1 sequence=27
archived log file name=/oradata/cc/standby/archive/1_28_990017315.dbf thread=1 sequence=28
media recovery complete, elapsed time: 00:00:00
Finished recover at 20-OCT-18
Oracle instance started
Total System Global Area 2499805184 bytes
Fixed Size 2927432 bytes
Variable Size 822084792 bytes
Database Buffers 1660944384 bytes
Redo Buffers 13848576 bytes
contents of Memory Script:
{
sql clone "alter system set db_name =
''CC'' comment=
''Reset to original value by RMAN'' scope=spfile";
}
executing Memory Script
sql statement: alter system set db_name = ''CC'' comment= ''Reset to original value by RMAN'' scope=spfile
Oracle instance started
Total System Global Area 2499805184 bytes
Fixed Size 2927432 bytes
Variable Size 822084792 bytes
Database Buffers 1660944384 bytes
Redo Buffers 13848576 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "CC" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '/oradata/cc/standby/redo/redo01.log' ) SIZE 50 M REUSE,
GROUP 2 ( '/oradata/cc/standby/redo/redo02.log' ) SIZE 50 M REUSE,
GROUP 3 ( '/oradata/cc/standby/redo/redo03.log' ) SIZE 50 M REUSE
DATAFILE
'/oradata/cc/standby/dbf/system01.dbf'
CHARACTER SET AL32UTF8
contents of Memory Script:
{
set newname for tempfile 1 to
"/oradata/cc/standby/dbf/temp01.dbf";
switch clone tempfile all;
catalog clone datafilecopy "/oradata/cc/standby/dbf/sysaux01.dbf",
"/oradata/cc/standby/dbf/undotbs01.dbf",
"/oradata/cc/standby/dbf/users01.dbf";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /oradata/cc/standby/dbf/temp01.dbf in control file
cataloged datafile copy
datafile copy file name=/oradata/cc/standby/dbf/sysaux01.dbf RECID=1 STAMP=990059225
cataloged datafile copy
datafile copy file name=/oradata/cc/standby/dbf/undotbs01.dbf RECID=2 STAMP=990059225
cataloged datafile copy
datafile copy file name=/oradata/cc/standby/dbf/users01.dbf RECID=3 STAMP=990059225
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=990059225 file name=/oradata/cc/standby/dbf/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=990059225 file name=/oradata/cc/standby/dbf/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=990059225 file name=/oradata/cc/standby/dbf/users01.dbf
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 20-OCT-18
RMAN>
======================================================================================================================================
7、去auxiliary库上检查是否复制成功
[email protected][/oradata/cc/standby]$ll *
archive:
total 164
-rw-r----- 1 oracle dba 162816 Oct 21 00:26 1_27_990017315.dbf
-rw-r----- 1 oracle dba 1024 Oct 21 00:26 1_28_990017315.dbf
control:
total 20256
-rw-r----- 1 oracle dba 10371072 Oct 21 00:37 ccaux01.ctl
-rw-r----- 1 oracle dba 10371072 Oct 21 00:37 ccaux02.ctl
dbf:
total 1589040
-rw-r----- 1 oracle dba 576724992 Oct 21 00:32 sysaux01.dbf
-rw-r----- 1 oracle dba 734011392 Oct 21 00:32 system01.dbf
-rw-r----- 1 oracle dba 20979712 Oct 21 00:27 temp01.dbf
-rw-r----- 1 oracle dba 309338112 Oct 21 00:32 undotbs01.dbf
-rw-r----- 1 oracle dba 5251072 Oct 21 00:27 users01.dbf
redo:
total 153612
-rw-r----- 1 oracle dba 52429312 Oct 21 00:36 redo01.log
-rw-r----- 1 oracle dba 52429312 Oct 21 00:27 redo02.log
-rw-r----- 1 oracle dba 52429312 Oct 21 00:27 redo03.log
spfile:
total 0
8、auxiliary库检查测试用户和测试表
SQL> conn wufan/wufan
Connected.
SQL> select * from tmp_list_001 ;
ID NAME
---------- ----------
1 AAAAAA
SQL>