Duplication Database 介紹
Duplicate database可以按照用途分為2種:
duplicate database(複制出一個資料庫)
duplicate standby database(複制出一個dataguard standby資料庫)
Duplicate Database又可以按照複制資料的來源不同分為2種方式:
Active Database Duplication(從正在運作的資料庫上複制資料)
Backup-based duplication(基于備份集的資料複制)
Active Database Duplication 介紹
Active database duplication功能是從11g開始引入的一個新功能,它是對比以前版本中的基于備份集(Backup-based duplication)的複制資料庫功能。 這種複制資料庫要求源資料庫是open狀态或者mount狀态,複制的過程一定要連接配接到源資料庫,RMAN直接從源庫複制資料庫到Duplication伺服器,這種方式不需要提前備份源庫。
Duplicate Database 特點
1. 複制的Database會自動配置設定一個新的DBID,與源資料庫的DBID不同,這樣Duplicate資料庫和源庫可以注冊到同一個catalog 資料庫。
如果使用作業系統的指令來做異機複制恢複的話,新建立的資料庫和源庫是相同的DBID。
2. 複制的資料庫可以是源庫的一個完全鏡像,也可以是源庫的一個子集。
3. 複制的資料庫和源庫必須是相同的作業系統平台,我們認為同平台下的32-bit 和 64-bit是同一個平台,例如Linux IA (32-bit) 和Linux IA (64-bit),認為是相同的平台,可以實施duplicate 功能,但是最後一定要運作下面的腳本來轉換PL/SQL:
ORACLE_HOME/rdbms/admin/utlirp.sql
Active Database Duplication 和 Backup-based duplication對比
Backup-based duplication :需要提前備份資料庫,磁盤空間大小能夠滿足備份的需要。
Active Database Duplication 不需要提前備份,節省了磁盤空間,減少了傳輸備份的時間,但在複制的過程中,對源庫有一定的壓力,需要一定的網絡帶寬
Active Database Duplication 原理
1. 手動建立一個臨時的pfile檔案,pfile檔案至少包括一個參數DB_NAME,然後啟動到nomount狀态。
2. RMAN從源庫拷貝spfile檔案到複制資料庫上,并且修改spfile的名字。
3. RMAN從源庫拷貝最新的control file到複制資料庫,并且mount 複制資料庫。
4. RMAN從源庫拷貝datafile和必要的歸檔日志到複制資料庫。
5. RMAN執行不完全的恢複。
6. RMAN建立新的control file,并且設定新的DBID。
7. 以RESETLOGS方式打開複制的database。
以上理論知識參考自下列網址:
11g 新特性—— Active Database Duplication
使用Active Database Duplication搭建步驟
一、要點
--主庫
主庫開啟歸檔
主庫開啟force logging
主庫修改dg需要的參數
主庫生成duplicate需要的控制檔案
主庫tns中加入備庫服務名
--備庫
備庫生成一個包含db_name的pfile
拷貝主庫的密碼檔案放置到備庫指定位置并改名
設定監聽為靜态監聽
拷貝主庫上的tns檔案到備庫指定位置
使用tnsping測試主庫備庫之間的連通性
使用pfile啟動備庫到nomount狀态
二、具體操作、
系統說明:
主庫:
IP:172.25.21.111
SID:siebuat
備庫:
IP:172.25.21.222
SID:uatldg
-----------------------------------------------主庫配置-----------------------------------------------
1、檢視主庫是否開啟歸檔
SYS@siebuat> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /oracle/archivelog
Oldest online log sequence 21
Next log sequence to archive 23
Current log sequence 23
2、檢視主庫是否開啟FORCE_LOGGING
SYS@siebuat> r
1* SELECT NAME,OPEN_MODE,DATABASE_ROLE,LOG_MODE,FORCE_LOGGING FROM V$DATABASE
NAME OPEN_MODE DATABASE_ROLE LOG_MODE FORCE_
------------------ ---------- -------------- ------------------------ ------
SIEBUAT READ WRITE PRIMARY ARCHIVELOG YES
如果未開啟FORCE_LOGGING,則執行:
SYS@siebuat> ALTER DATABASE
3、主庫修改dg需要的參數
--修改主庫spfile初始化參數
alter system set log_archive_config='dg_config=(siebuat,uatldg )';
--log_archive_dest_1已經設定在本地,不需要再設定,隻需要設定log_archive_dest_2
alter system set log_archive_dest_2='service=uatldg LGWR SYNC AFFIRM valid_for=(online_logfiles,primary_role) db_unique_name=uatldg';
--延遲開啟dest_2,等備庫配置好後再enable
alter system set log_archive_dest_state_2='defer';
--設定standby檔案自動管理
alter system set standby_file_management=auto;
alter system set fal_server='uatldg';
alter system set fal_client='siebuat';
4、主庫生成duplicate控制檔案需要的參數模闆
SYS@siebuat> create pfile='/home/oracle/to_standby/duplicate.cmd' from spfile;
File
5、主庫TNSNAMES.ORA中加入備庫服務名
# Generated by Oracle configuration tools.
SIEBUAT =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.21.111)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = siebuat)
)
)
UATLDG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.21.222)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = uatldg)
)
)
6、主庫修改duplicate參數檔案
[oracle@primary ~]$ cat duplicate.cmd
##在這裡指定主庫與備庫存在不同的參數,可以直接通過主庫生成的pfile來修改
##标紅的參數行都需要存在,其他的如果主庫和備庫一樣,可以不添加(推薦添加上),
##如audit_trail、compatiable、db_block_size等通常都要保持一樣,不需要更改
##但如果備庫的伺服器配置沒主庫高,如主庫記憶體100G,備庫隻有60G,
##則必須添加sga_target,pga_aggregate_target等參數限制備庫共享記憶體的大小,否則會報記憶體不足
duplicate target database for standby from active database dorecover
spfile
set audit_file_dest='/oracle/admin/uatldg/adump'
set audit_trail='db'
set compatible='11.2.0.4.0'
set control_files='/oracle/oradata/uatldg/control01.ctl','/oracle/oradata/uatldg/control02.ctl'
set db_block_size='8192'
set diagnostic_dest='/oracle'
set dispatchers='(PROTOCOL=TCP) (SERVICE=siebuatXDB)'
set fal_client='uatldg'
set fal_server='siebuat'
set log_archive_config='dg_config=(siebuat,uatldg )'
set log_archive_dest_1='LOCATION=/oracle/archivelog'
set log_archive_dest_2='service=siebuat LGWR valid_for=(online_logfiles,primary_role) db_unique_name=siebuat'
set log_archive_dest_state_2='enable'
set log_archive_format='%t_%s_%r.dbf'
set open_cursors='300'
set pga_aggregate_target='30M'
set processes='200'
set remote_login_passwordfile='EXCLUSIVE'
set sga_target='300M'
set standby_file_management='AUTO'
set undo_tablespace='UNDOTBS1'
##下列參數為新增的在主庫中沒有的,對資料檔案、日志檔案的路徑進行轉換,
##尤其在主庫和備庫在同一台機器上時,
##如果不指定,容易造成duplicate時把主庫的線上日志檔案沖掉,造成主庫crash。
set db_file_name_convert='/oracle/oradata/siebuat/','/oracle/oradata/uatldg/'
set log_file_name_convert='/oracle/oradata/siebuat/','/oracle/oradata/uatldg/'
set db_unique_name='uatldg';
-----------------------------------------------備庫配置-----------------------------------------------
1、拷貝主庫的密碼檔案、tnsnames.ora檔案
[oracle@primary to_standby]$ scp $ORACLE_HOME/dbs/orapwsiebuat [email protected]:$ORACLE_HOME/dbs/orapwuatldg
[email protected]'s password:
orapwsiebuat 100% 1536 1.5KB/s 00:00
scp $ORACLE_HOME/network/admin/tnsnames.ora [email protected]:$ORACLE_HOME/network/admin/
[email protected]'s password:
tnsnames.ora 100% 511 0.5KB/s 00:00
2、設定備庫監聽為靜态監聽
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = uatldg)
(ORACLE_HOME = /oracle/software/11.2.0.4/db_1)
(SID_NAME = uatldg)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.21.222)(PORT = 1521))
)
ADR_BASE_LISTENER = /oracle
[oracle@standby admin]$ lsnrctl status for Linux: Version 11.2.0.4.0 - Production on 10-MAR-2016 13:24:32
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.25.21.222)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 09-MAR-2016 21:25:24
Uptime 0 days 15 hr. 59 min. 8 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/software/11.2.0.4/db_1/network/admin/listener.ora
Listener Log File /oracle/diag/tnslsnr/standby/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.25.21.222)(PORT=1521)))
Services Summary...
Service "uatldg" has 1UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
3、使用tnsping測試主庫備庫之間的連通性
####備庫#########################################################################
[oracle@standby admin]$ hostname
standby
[oracle@standby admin]$ tnsping siebuatty for Linux: Version 11.2.0.4.0 - Production on 10-MAR-2016 13:29:07
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.21.111)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =OK (10 msec)
[oracle@standby admin]$ tnsping uatldgfor Linux: Version 11.2.0.4.0 - Production on 10-MAR-2016 13:29:16
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.21.222)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =OK (0 msec)
[oracle@standby admin]$
####主庫#######################################################################################
[oracle@primary ~]$ hostname
primary
[oracle@primary ~]$ tnsping siebuat
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 10-MAR-2016 13:30:48
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.21.111)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = siebuat)))
OK (0 msec)
[oracle@primary ~]$ tnsping uatldg
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 10-MAR-2016 13:30:57
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 172.25.21.222)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = uatldg)))
OK (0 msec)
4、備庫建立一個pfile,加入db_name即可,并啟動到nomount
db_name='uatldg'
5、啟動備庫到nomount模式
[oracle@standby dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 10 13:38:54 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SYS@uatldg> startup nomount521936896 bytes
Fixed Size 2254824 bytes
Variable Size 209717272 bytes
Database Buffers 306184192 bytes
Redo Buffers 3780608 bytes
SYS@uatldg>
duplicate操作(在主庫上執行)
[oracle@primary ~]$ rman target / auxiliary sys/passoword@uatldg cmdfile=duplicate.cmd log=standby_create.log
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Mar 10 13:41:38 2016 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights
reserved. connected to target database: SIEBUAT (DBID=443676106)
connected to auxiliary database: UATLDG (not mounted)
RMAN>
Starting Duplicate Db at 10-MAR-16
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:
{
backup as copy reuse
targetfile '/oracle/software/11.2.0.4/db_1/dbs/orapwsiebuat' auxiliary format
'/oracle/software/11.2.0.4/db_1/dbs/orapwuatldg' ;
}
executing Memory Script
Starting backup at 10-MAR-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=53 device type=DISK
Finished backup at 10-MAR-16
--複制主庫的控制檔案
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/oracle/oradata/uatldg/control01.ctl';
restore clone controlfile to '/oracle/oradata/uatldg/control02.ctl' from
'/oracle/oradata/uatldg/control01.ctl';
}
executing Memory Script
Starting backup at 10-MAR-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/oracle/software/11.2.0.4/db_1/dbs/snapcf_siebuat.f tag=TAG20160310T134417 RECID=3 STAMP=906126258
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 10-MAR-16
--還原控制檔案到備庫
Starting restore at 10-MAR-16
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 10-MAR-16
--使用還原的控制檔案啟動備庫到mount狀态
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
--轉換主庫的資料檔案、臨時檔案、undo檔案等到備庫路徑
contents of Memory Script:
{
set newname for tempfile 1 to
"/oracle/oradata/uatldg/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/oracle/oradata/uatldg/system01.dbf";
set newname for datafile 2 to
"/oracle/oradata/uatldg/sysaux01.dbf";
set newname for datafile 3 to
"/oracle/oradata/uatldg/undotbs01.dbf";
set newname for datafile 4 to
"/oracle/oradata/uatldg/users01.dbf";
set newname for datafile 5 to
"/oracle/oradata/uatldg/example01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/oracle/oradata/uatldg/system01.dbf" datafile
2 auxiliary format
"/oracle/oradata/uatldg/sysaux01.dbf" datafile
3 auxiliary format
"/oracle/oradata/uatldg/undotbs01.dbf" datafile
4 auxiliary format
"/oracle/oradata/uatldg/users01.dbf" datafile
5 auxiliary format
"/oracle/oradata/uatldg/example01.dbf" ;
sql 'alter system archive log current';--切換歸檔
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /oracle/oradata/uatldg/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
--進行資料檔案備份并拷貝到備庫
Starting backup at 10-MAR-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/oracle/oradata/siebuat/system01.dbf
output file name=/oracle/oradata/uatldg/system01.dbf tag=TAG20160310T134428
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/oracle/oradata/siebuat/sysaux01.dbf
output file name=/oracle/oradata/uatldg/sysaux01.dbf tag=TAG20160310T134428
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/oracle/oradata/siebuat/example01.dbf
output file name=/oracle/oradata/uatldg/example01.dbf tag=TAG20160310T134428
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=/oracle/oradata/siebuat/undotbs01.dbf
output file name=/oracle/oradata/uatldg/undotbs01.dbf tag=TAG20160310T134428
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/oracle/oradata/siebuat/users01.dbf
output file name=/oracle/oradata/uatldg/users01.dbf tag=TAG20160310T134428
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 10-MAR-16
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=3 STAMP=906126342 file name=/oracle/oradata/uatldg/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=4 STAMP=906126342 file name=/oracle/oradata/uatldg/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=906126342 file name=/oracle/oradata/uatldg/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=906126342 file name=/oracle/oradata/uatldg/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=7 STAMP=906126342=/oracle/oradata/uatldg/example01.dbf
Finished Duplicate Db at 10-MAR-16 --duplicate standby database完成
RMAN>
Dataguard操作
檢視主備庫資訊
--主庫
SYS@siebuat> l
1* select switchover_status,DATABASE_ROLE,open_mode,PROTECTION_MODE,PROTECTION_LEVEL from v$database
SYS@siebuat> /
SWITCHOVER_STATUS DATABASE_ROLE OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL
-------------------- --------------- ---------- ------------------------- -------------------------
TO STANDBY PRIMARY READ
--備庫
SYS@uatldg> l
1* select switchover_status,DATABASE_ROLE,open_mode,PROTECTION_MODE,PROTECTION_LEVEL from v$database
SYS@uatldg> /
SWITCHOVER_STATUS DATABASE_ROLE OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL
-------------------- -------------------- ---------- ------------------------- -------------------------
RECOVERY NEEDED PHYSICAL STANDBY MOUNTED MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
--開啟備庫日志應用
SYS@uatldg> alter database recover managed standby database disconnect from session;
Database altered.
SYS@uatldg> select switchover_status,DATABASE_ROLE,open_mode,PROTECTION_MODE,PROTECTION_LEVEL from v$database;
SWITCHOVER_STATUS DATABASE_ROLE OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL
-------------------- -------------------- ---------- ------------------------- -------------------------
NOT ALLOWED PHYSICAL STANDBY MOUNTED MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
--檢視是否存在gap
SYS@uatldg> select thread#,low_sequence#,high_sequence# from v$archive_gap;
no rows selected
--開啟備庫到ADG模式
SYS@uatldg> alter database recover managed standby database cancel;
Database altered.
SYS@uatldg> alter database open read only;
Database altered.
SYS@uatldg> alter database recover managed standby database disconnect from session;
Database altered.
SYS@uatldg> select switchover_status,DATABASE_ROLE,open_mode,PROTECTION_MODE,PROTECTION_LEVEL from v$database;
SWITCHOVER_STATUS DATABASE_ROLE OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL
-------------------- -------------------- ---------- ------------------------- -------------------------
NOT ALLOWED PHYSICAL STANDBY READ ONLY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
WITH APPLY
SYS@uatldg>