使用RMAN Active duplicate创建异地auxiliary Database

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

1g的RMAN duplicate 可以通过Active database duplicate和Backup-based duplicate两种方法实现。这里的测试使用的是Active database duplicate，因为Active database duplicate 功能强大，不需要先把目标数据库进行rman备份，只要目标数据库处于归档模式下即可直接通过网络对数据库进行copy，且copy完成后自动open数据库。这对于大数据特别是T级别的数据库来说优点非常明显，复制前不需要进行备份，减少了备份和传送备份的时间，同时节省备份空间。

1、环境说明

	目标数据库（被复制的数据库）	辅助数据库（复制后的数据库）
IP	172.17.22.16	172.17.22.17
SID	orcl	orcl
数据库版本	Oracle 11g 11.2.0.1.0	仅安装了数据库软件Oracle11g 11.2.0.1.0

2、确保目标数据库处于归档模式

SYS@orcl>archive log list
Database log mode           No Archive Mode
Automatic archival           Disabled
Archive destination           USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     5
Current log sequence           7
SYS@orcl>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl>startup mount
ORACLE instance started.

Total System Global Area 3290345472 bytes
Fixed Size            2217832 bytes
Variable Size         1795164312 bytes
Database Buffers     1476395008 bytes
Redo Buffers           16568320 bytes
Database mounted.
SYS@orcl>alter database archivelog;

Database altered.

SYS@orcl>alter database open;

Database altered.

SYS@orcl>archive log list
Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     5
Next log sequence to archive   7
Current log sequence           7
SYS@orcl>

3、在辅助数据库服务器上创建相应的目录

查询目标数据库的相关目录

SYS@orcl>col name for a30
SYS@orcl>col value for a200
SYS@orcl>select name,value from v$parameter where name in ('audit_file_dest','background_dump_dest','control_files','core_dump_dest','user_dump_dest') ORDER BY name ASC;

NAME                   VALUE
------------------------------ --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
audit_file_dest            /usr/oracle/app/admin/orcl/adump
background_dump_dest           /usr/oracle/app/diag/rdbms/orcl/orcl/trace
control_files               /usr/oracle/app/oradata/orcl/control01.ctl, /usr/oracle/app/flash_recovery_area/orcl/control02.ctl
core_dump_dest               /usr/oracle/app/diag/rdbms/orcl/orcl/cdump
user_dump_dest               /usr/oracle/app/diag/rdbms/orcl/orcl/trace

SYS@orcl>

在辅助数据库服务器创建相同的目录

[oracle@oracledb ~]$ mkdir -p /usr/oracle/app/admin/orcl/adump
[oracle@oracledb ~]$ mkdir -p /usr/oracle/app/diag/rdbms/orcl/orcl/trace
[oracle@oracledb ~]$ mkdir -p /usr/oracle/app/oradata/orcl
[oracle@oracledb ~]$ mkdir -p /usr/oracle/app/flash_recovery_area/orcl
[oracle@oracledb ~]$ mkdir -p /usr/oracle/app/diag/rdbms/orcl/orcl/
[oracle@oracledb ~]$ mkdir -p /usr/oracle/app/diag/rdbms/orcl/orcl/trace

4、创建口令文件

方法一：直接从目标数据库复制密钥文件到辅助数据库相同目录

[oracle@oracledb dbs]$ pwd
/usr/oracle/app/product/11.2.0/dbhome_1/dbs
[oracle@oracledb dbs]$ ls
hc_DBUA0.dat hc_orcl.dat init.ora lkORCL orapworcl snapcf_orcl.f spfileorcl.ora
[oracle@oracledb dbs]$ scp orapworcl oracle@172.17.22.17:/usr/oracle/app/product/11.2.0/dbhome_1/dbs
The authenticity of host '172.17.22.17 (172.17.22.17)' can't be established.
RSA key fingerprint is 72:28:f5:f9:9c:f8:49:23:48:6d:9d:d4:0e:0c:89:71.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '172.17.22.17' (RSA) to the list of known hosts.
oracle@172.17.22.17's password:
orapworcl 100% 1536 1.5KB/s 00:00
[oracle@oracledb dbs]$

方法二： orapwd生成

orapwd FILE=/usr/oracle/app/product/11.2.0/dbhome_1/dbs/orapworcl password=wangshengzhuang entries=30;

5、创建Pfile文件

在源数据库根据spfile生产pfile

SYS@orcl>create pfile from spfile;

File created.

6、复制口令文件、pfile文件到远端目标数据库

复制口令文件和pfile文件

[oracle@oracledb dbs]$ pwd
/usr/oracle/app/product/11.2.0/dbhome_1/dbs
[oracle@oracledb dbs]$ scp initorcl.ora 172.17.22.17:/usr/oracle/app/product/11.2.0/dbhome_1/dbs
oracle@172.17.22.17's password: ***************
initorcl.ora 100% 970 1.0KB/s 00:00
[oracle@oracledb dbs]$ scp orapworcl 172.17.22.17:/usr/oracle/app/product/11.2.0/dbhome_1/dbs
oracle@172.17.22.17's password: ***************
orapworcl 100% 1536 1.5KB/s 00:00
[oracle@oracledb dbs]$

7、启动辅助数据库到nomount状态

使用复制的pfile启动，或者由pfile生成spfile后，由spfile启动

create spfile from pfile

[oracle@oracledb ~]$ sqlplus sys as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Dec 22 16:07:27 2015

Enter password:
Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 3290345472 bytes
Fixed Size            2217832 bytes
Variable Size         1795164312 bytes
Database Buffers     1476395008 bytes
Redo Buffers           16568320 bytes
SQL>

8、辅助数据库配置监听并启动
listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME =/usr/oracle/app/product/11.2.0/dbhome_1)
(SID_NAME = orcl)
)
)

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

ADR_BASE_LISTENER = /usr/oracle/app
~

启动监听

[oracle@oracledb admin]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 22-DEC-2015 16:11:39

Starting /usr/oracle/app/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /usr/oracle/app/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /usr/oracle/app/diag/tnslsnr/oracledb/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.17.22.17)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date                22-DEC-2015 16:11:41
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /usr/oracle/app/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /usr/oracle/app/diag/tnslsnr/oracledb/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.17.22.17)(PORT=1521)))
Services Summary...
Service "orcl" has 1 instance(s).
Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@oracledb admin]$

9、目标数据库配置网络服务名

tnsnames.ora

oradu =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.22.17)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

测试

[oracle@oracledb admin]$ tnsping oradu

TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 22-DEC-2015 16:15:06

Used parameter files:
/usr/oracle/app/product/11.2.0/dbhome_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.22.17)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (0 msec)
[oracle@oracledb admin]$

10、使用RMAN duplicate命令复制

连接目标数据库和辅助数据库

[oracle@oracledb admin]$ rman target sys/wangshengzhuang@orcl auxiliary sys/wangshengzhuang@oradu

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Dec 23 10:50:34 2015

connected to target database: ORCL (DBID=1426832466)
connected to auxiliary database: ORCL (not mounted)

开始复制

RMAN> duplicate target database to orcl from active database nofilenamecheck;

Starting Duplicate Db at 23-DEC-15
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 "alter system set db_name =
''ORCL'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''ORCL'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
backup as copy current controlfile auxiliary format '/usr/oracle/app/oradata/orcl/control01.ctl';
restore clone controlfile to '/usr/oracle/app/flash_recovery_area/orcl/control02.ctl' from
'/usr/oracle/app/oradata/orcl/control01.ctl';
alter clone database mount;
}
executing Memory Script

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

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

Oracle instance shut down

Oracle instance started

Total System Global Area 3290345472 bytes

Fixed Size                     2217832 bytes
Variable Size               1795164312 bytes
Database Buffers            1476395008 bytes
Redo Buffers                  16568320 bytes

Starting backup at 23-DEC-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=38 device type=DISK
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=/usr/oracle/app/product/11.2.0/dbhome_1/dbs/snapcf_orcl.f tag=TAG20151223T105952 RECID=2 STAMP=899204392
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 23-DEC-15

Starting restore at 23-DEC-15
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 device type=DISK

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 23-DEC-15

database mounted

contents of Memory Script:
{
set newname for datafile 1 to
"/usr/oracle/app/oradata/orcl/system01.dbf";
set newname for datafile 2 to
"/usr/oracle/app/oradata/orcl/sysaux01.dbf";
set newname for datafile 3 to
"/usr/oracle/app/oradata/orcl/undotbs01.dbf";
set newname for datafile 4 to
"/usr/oracle/app/oradata/orcl/users01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/usr/oracle/app/oradata/orcl/system01.dbf"   datafile
2 auxiliary format
"/usr/oracle/app/oradata/orcl/sysaux01.dbf"   datafile
3 auxiliary format
"/usr/oracle/app/oradata/orcl/undotbs01.dbf"   datafile
4 auxiliary format
"/usr/oracle/app/oradata/orcl/users01.dbf"   ;
sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

Starting backup at 23-DEC-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/usr/oracle/app/oradata/orcl/system01.dbf
output file name=/usr/oracle/app/oradata/orcl/system01.dbf tag=TAG20151223T105958
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/usr/oracle/app/oradata/orcl/sysaux01.dbf
output file name=/usr/oracle/app/oradata/orcl/sysaux01.dbf tag=TAG20151223T105958
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/usr/oracle/app/oradata/orcl/undotbs01.dbf
output file name=/usr/oracle/app/oradata/orcl/undotbs01.dbf tag=TAG20151223T105958
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/usr/oracle/app/oradata/orcl/users01.dbf
output file name=/usr/oracle/app/oradata/orcl/users01.dbf tag=TAG20151223T105958
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 23-DEC-15

sql statement: alter system archive log current

contents of Memory Script:
{
backup as copy reuse
archivelog like "/usr/oracle/app/flash_recovery_area/ORCL/archivelog/2015_12_23/o1_mf_1_9_c7n3lkgq_.arc" auxiliary format
"/usr/oracle/app/flash_recovery_area/ORCL/archivelog/2015_12_23/o1_mf_1_9_%u_.arc" ;
catalog clone recovery area;
switch clone datafile all;
}
executing Memory Script

Starting backup at 23-DEC-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=9 RECID=3 STAMP=899204433
output file name=/usr/oracle/app/flash_recovery_area/ORCL/archivelog/2015_12_23/o1_mf_1_9_0bqphhah_.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 23-DEC-15

searching for all files in the recovery area

List of Files Unknown to the Database
=====================================
File Name: /usr/oracle/app/flash_recovery_area/ORCL/archivelog/2015_12_23/o1_mf_1_9_0bqphhah_.arc
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /usr/oracle/app/flash_recovery_area/ORCL/archivelog/2015_12_23/o1_mf_1_9_0bqphhah_.arc

datafile 1 switched to datafile copy
input datafile copy RECID=2 STAMP=899204434 file name=/usr/oracle/app/oradata/orcl/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=3 STAMP=899204434 file name=/usr/oracle/app/oradata/orcl/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=899204434 file name=/usr/oracle/app/oradata/orcl/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=899204434 file name=/usr/oracle/app/oradata/orcl/users01.dbf

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

executing command: SET until clause

Starting recover at 23-DEC-15
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 9 is already on disk as file /usr/oracle/app/flash_recovery_area/ORCL/archivelog/2015_12_23/o1_mf_1_9_0bqphhah_.arc
archived log file name=/usr/oracle/app/flash_recovery_area/ORCL/archivelog/2015_12_23/o1_mf_1_9_0bqphhah_.arc thread=1 sequence=9
media recovery complete, elapsed time: 00:00:00
Finished recover at 23-DEC-15

contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set db_name =
''ORCL'' 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 3290345472 bytes

Fixed Size                     2217832 bytes
Variable Size               1795164312 bytes
Database Buffers            1476395008 bytes
Redo Buffers                  16568320 bytes

sql statement: alter system set db_name = ''ORCL'' 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 3290345472 bytes

Fixed Size                     2217832 bytes
Variable Size               1795164312 bytes
Database Buffers            1476395008 bytes
Redo Buffers                  16568320 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "ORCL" RESETLOGS ARCHIVELOG
MAXLOGFILES     16
MAXLOGMEMBERS      3
MAXDATAFILES      100
MAXINSTANCES     8
MAXLOGHISTORY      292
LOGFILE
GROUP 1 SIZE 50 M ,
GROUP 2 SIZE 50 M ,
GROUP 3 SIZE 50 M
DATAFILE
'/usr/oracle/app/oradata/orcl/system01.dbf'
CHARACTER SET AL32UTF8

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

executing command: SET NEWNAME

renamed tempfile 1 to /usr/oracle/app/oradata/orcl/temp01.dbf in control file

cataloged datafile copy
datafile copy file name=/usr/oracle/app/oradata/orcl/sysaux01.dbf RECID=1 STAMP=899204444
cataloged datafile copy
datafile copy file name=/usr/oracle/app/oradata/orcl/undotbs01.dbf RECID=2 STAMP=899204444
cataloged datafile copy
datafile copy file name=/usr/oracle/app/oradata/orcl/users01.dbf RECID=3 STAMP=899204444

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=899204444 file name=/usr/oracle/app/oradata/orcl/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=899204444 file name=/usr/oracle/app/oradata/orcl/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=899204444 file name=/usr/oracle/app/oradata/orcl/users01.dbf

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

database opened
Finished Duplicate Db at 23-DEC-15

11、检查结果

SQL> select select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/usr/oracle/app/oradata/orcl/system01.dbf
/usr/oracle/app/oradata/orcl/sysaux01.dbf
/usr/oracle/app/oradata/orcl/undotbs01.dbf
/usr/oracle/app/oradata/orcl/users01.dbf

有个问题，logfile的文件名变化了，后期自己rename

select * from v$logfile order by group# asc;