【DATAGUARD】DG系列之RACtoONE物理備庫的搭建
說明：Oracle11.2.0.4 RAC/RedHat6.3_x64 à Oracle11.2.0.4/Oracle Linux 6.6
主資料庫：racdb 備庫：phydb
1、 網路配置：
主庫是透過SCAN IP進行網路訪問，顧可不做相關監聽配置，下面為備庫靜態監聽配置檔案：
|
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = phydb) (ORACLE_HOME = /oracle/app/oracle/product/11.2.0) (SID_NAME = phydb) ) ) |
注意：為了確保能夠在Oracle Data Guard配置中的任意節點上，發生角色轉換或者故障轉移後恢復原來的狀態，必須為每個資料庫例項在各自的節點上建立一個靜態監聽器項。詳情可參考《【DATAGUARD】DG系列之DG代理（broker)的搭建和應用》，後邊文章會說明相關配置。
2、 配置TNS，每個節點都需配置，本次測試主要針對RAC To ONE，暫不考慮RAC to RAC相關方面問題：
|
RACDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan)(PORT = 1521)) (SERVICE_NAME = racdb) ) ) PHYDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oeloracle)(PORT = 1521)) (SERVICE_NAME = phydb) ) ) |
3、 修改資料庫為歸檔模式，開啟強制日誌記錄
|
--歸檔不做相關說明，下面是開啟強制日誌記錄 SQL> alter database force logging; |
4、 備庫建立密碼檔案
#orapwd file=$ORACLE_HOME/dbs/orapwphydb password=oracle
5、 複製引數檔案，修改，並啟動
|
create pfile=’/tmp/initphydb.ora’ from spfile;
--啟動資料庫到nomount模式 |
6、 修改初始化引數，修改完成後重啟各例項
主庫引數：
|
alter system set db_unique_name='racdb' scope=spfile sid='*'; alter system set standby_file_management=AUTO scope=both; alter system set log_archive_config='DG_CONFIG=(racdb,phydb)' scope=both sid='*'; alter system set db_file_name_convert='/oracle/app/oracle/oradata/PHYDB','+DATA/racdb/datafile','/oracle/app/oracle/oradata/PHYDB','+DATA/racdb/tempfile' scope=spfile sid='*'; alter system set log_file_name_convert='/oracle/app/oracle/archivelog_phy','+DATA/racdb/archivelog' scope=spfile sid='*'; alter system set log_archive_dest_1='location=+DATA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=RACDB' scope=spfile sid='*'; alter system set log_archive_dest_2='SERVICE=phydb VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=phydb' scope=spfile sid='*'; alter system set log_archive_dest_state_2=enable scope=both sid='*'; alter system set fal_server='phydb' scope=spfile sid='*'; |
備庫引數：
|
alter system set db_unique_name='phydb' scope=spfile sid='*'; alter system set standby_file_management=AUTO scope=both; alter system set log_archive_config='DG_CONFIG=(racdb,phydb)' scope=both sid='*'; alter system set db_file_name_convert='+DATA/racdb/datafile','/oracle/app/oracle/oradata/PHYDB','+DATA/racdb/tempfile','/oracle/app/oracle/oradata/PHYDB' scope=spfile sid='*'; alter system set log_file_name_convert='+DATA/racdb/archivelog','/oracle/app/oracle/archivelog_phy' scope=spfile sid='*'; alter system set log_archive_dest_1='location=/oracle/app/oracle/archivelog_phy VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=phydb' scope=spfile sid='*'; alter system set log_archive_dest_2='SERVICE=racdb VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=racdb' scope=spfile sid='*'; alter system set log_archive_dest_state_2=enable scope=both; alter system set fal_server='racdb' scope=spfile sid='*'; |
7、 建立備用重做日誌，在建立備用重做日誌時，必須使它們的大小與線上重做日誌相同，2為每個執行緒建立一個額外的備用重做日誌組。
|
alter database add standby logfile thread 1 group 7 '+DATA' size 200m; alter database add standby logfile thread 2 group 8 '+DATA' size 200m; alter database add standby logfile thread 1 group 9 '+DATA' size 200m; alter database add standby logfile thread 2 group 10 '+DATA' size 200m; alter database add standby logfile thread 1 group 11 '+DATA' size 200m; alter database add standby logfile thread 2 group 12 '+DATA' size 200m; |
8、 使用duplicate屬性建立備用資料庫
|
[oracle@rac01 dbs]$ rman target sys/oracle@racdb auxiliary sys/oracle@phydb
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jul 2 09:19:55 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: RACDB (DBID=857055310) connected to auxiliary database: RACDB (not mounted)
RMAN> duplicate target database for standby from active database;
Starting Duplicate Db at 2015-07-02 09:19:59 using target database control file instead of recovery catalog allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=192 device type=DISK
contents of Memory Script: { backup as copy reuse targetfile '/oracle/app/oracle/product/11.2.0/dbs/orapwracdb1' auxiliary format '/oracle/app/oracle/product/11.2.0/dbs/orapwphydb' ; } executing Memory Script
Starting backup at 2015-07-02 09:20:00 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=51 instance=racdb1 device type=DISK Finished backup at 2015-07-02 09:20:01
contents of Memory Script: { backup as copy current controlfile for standby auxiliary format '/oracle/app/oracle/oradata/PHYDB/control01.dbf'; restore clone controlfile to '/oracle/app/oracle/oradata/PHYDB/control02.dbf' from '/oracle/app/oracle/oradata/PHYDB/control01.dbf'; } executing Memory Script
Starting backup at 2015-07-02 09:20:01 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy copying standby control file output file name=/oracle/app/oracle/product/11.2.0/dbs/snapcf_racdb1.f tag=TAG20150702T092001 RECID=7 STAMP=883992006 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 Finished backup at 2015-07-02 09:20:08
Starting restore at 2015-07-02 09:20:08 using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: copied control file copy Finished restore at 2015-07-02 09:20:09
contents of Memory Script: { sql clone 'alter database mount standby database'; } executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script: { set newname for tempfile 1 to "/oracle/app/oracle/oradata/PHYDB/temp.286.859438605"; switch clone tempfile all; set newname for datafile 1 to "/oracle/app/oracle/oradata/PHYDB/system.293.859438573"; set newname for datafile 2 to "/oracle/app/oracle/oradata/PHYDB/sysaux.288.859438589"; set newname for datafile 3 to "/oracle/app/oracle/oradata/PHYDB/undotbs1.287.859438601"; set newname for datafile 4 to "/oracle/app/oracle/oradata/PHYDB/undotbs2.285.859438615"; set newname for datafile 5 to "/oracle/app/oracle/oradata/PHYDB/users.278.859438619"; set newname for datafile 6 to "/oracle/app/oracle/oradata/PHYDB/test1.261.859451689"; set newname for datafile 7 to "/oracle/app/oracle/oradata/PHYDB/firsoul.259.882071697"; set newname for datafile 8 to "/oracle/app/oracle/oradata/PHYDB/firsoul.263.882093749"; set newname for datafile 11 to "/oracle/app/oracle/oradata/PHYDB/firsoul01.dbf"; backup as copy reuse datafile 1 auxiliary format "/oracle/app/oracle/oradata/PHYDB/system.293.859438573" datafile 2 auxiliary format "/oracle/app/oracle/oradata/PHYDB/sysaux.288.859438589" datafile 3 auxiliary format "/oracle/app/oracle/oradata/PHYDB/undotbs1.287.859438601" datafile 4 auxiliary format "/oracle/app/oracle/oradata/PHYDB/undotbs2.285.859438615" datafile 5 auxiliary format "/oracle/app/oracle/oradata/PHYDB/users.278.859438619" datafile 6 auxiliary format "/oracle/app/oracle/oradata/PHYDB/test1.261.859451689" datafile 7 auxiliary format "/oracle/app/oracle/oradata/PHYDB/firsoul.259.882071697" datafile 8 auxiliary format "/oracle/app/oracle/oradata/PHYDB/firsoul.263.882093749" datafile 11 auxiliary format "/oracle/app/oracle/oradata/PHYDB/firsoul01.dbf" ; sql 'alter system archive log current'; } executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /oracle/app/oracle/oradata/PHYDB/temp.286.859438605 in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 2015-07-02 09:20:16 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile copy input datafile file number=00003 name=+DATA/racdb/datafile/undotbs1.287.859438601 output file name=/oracle/app/oracle/oradata/PHYDB/undotbs1.287.859438601 tag=TAG20150702T092016 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55 channel ORA_DISK_1: starting datafile copy input datafile file number=00001 name=+DATA/racdb/datafile/system.293.859438573 output file name=/oracle/app/oracle/oradata/PHYDB/system.293.859438573 tag=TAG20150702T092016 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55 channel ORA_DISK_1: starting datafile copy input datafile file number=00002 name=+DATA/racdb/datafile/sysaux.288.859438589 output file name=/oracle/app/oracle/oradata/PHYDB/sysaux.288.859438589 tag=TAG20150702T092016 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:36 channel ORA_DISK_1: starting datafile copy input datafile file number=00004 name=+DATA/racdb/datafile/undotbs2.285.859438615 output file name=/oracle/app/oracle/oradata/PHYDB/undotbs2.285.859438615 tag=TAG20150702T092016 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15 channel ORA_DISK_1: starting datafile copy input datafile file number=00006 name=+DATA/racdb/datafile/test1.261.859451689 output file name=/oracle/app/oracle/oradata/PHYDB/test1.261.859451689 tag=TAG20150702T092016 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting datafile copy input datafile file number=00008 name=+DATA/racdb/datafile/firsoul.263.882093749 output file name=/oracle/app/oracle/oradata/PHYDB/firsoul.263.882093749 tag=TAG20150702T092016 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting datafile copy input datafile file number=00007 name=+DATA/racdb/datafile/firsoul.259.882071697 output file name=/oracle/app/oracle/oradata/PHYDB/firsoul.259.882071697 tag=TAG20150702T092016 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04 channel ORA_DISK_1: starting datafile copy input datafile file number=00011 name=+DATA/racdb/datafile/firsoul01.dbf output file name=/oracle/app/oracle/oradata/PHYDB/firsoul01.dbf tag=TAG20150702T092016 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03 channel ORA_DISK_1: starting datafile copy input datafile file number=00005 name=+DATA/racdb/datafile/users.278.859438619 output file name=/oracle/app/oracle/oradata/PHYDB/users.278.859438619 tag=TAG20150702T092016 channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 2015-07-02 09:23:14
sql statement: alter system archive log current
contents of Memory Script: { switch clone datafile all; } executing Memory Script
datafile 1 switched to datafile copy input datafile copy RECID=7 STAMP=883992258 file name=/oracle/app/oracle/oradata/PHYDB/system.293.859438573 datafile 2 switched to datafile copy input datafile copy RECID=8 STAMP=883992258 file name=/oracle/app/oracle/oradata/PHYDB/sysaux.288.859438589 datafile 3 switched to datafile copy input datafile copy RECID=9 STAMP=883992258 file name=/oracle/app/oracle/oradata/PHYDB/undotbs1.287.859438601 datafile 4 switched to datafile copy input datafile copy RECID=10 STAMP=883992258 file name=/oracle/app/oracle/oradata/PHYDB/undotbs2.285.859438615 datafile 5 switched to datafile copy input datafile copy RECID=11 STAMP=883992258 file name=/oracle/app/oracle/oradata/PHYDB/users.278.859438619 datafile 6 switched to datafile copy input datafile copy RECID=12 STAMP=883992258 file name=/oracle/app/oracle/oradata/PHYDB/test1.261.859451689 datafile 7 switched to datafile copy input datafile copy RECID=13 STAMP=883992258 file name=/oracle/app/oracle/oradata/PHYDB/firsoul.259.882071697 datafile 8 switched to datafile copy input datafile copy RECID=14 STAMP=883992258 file name=/oracle/app/oracle/oradata/PHYDB/firsoul.263.882093749 datafile 11 switched to datafile copy input datafile copy RECID=15 STAMP=883992258 file name=/oracle/app/oracle/oradata/PHYDB/firsoul01.dbf Finished Duplicate Db at 2015-07-02 09:24:27 |
9、 在執行復制過程中，容易出現以下錯誤，主要還是針對密碼檔案，為了避免出現錯誤，建議使用scp或者其他命令將主庫密碼檔案複製過去直接使用。
|
RMAN> duplicate target database for standby from active database;
Starting Duplicate Db at 2015-07-02 09:17:42 using target database control file instead of recovery catalog allocated channel: ORA_AUX_DISK_1 channel ORA_AUX_DISK_1: SID=192 device type=DISK
contents of Memory Script: { backup as copy reuse targetfile '/oracle/app/oracle/product/11.2.0/dbs/orapwracdb1' auxiliary format '/oracle/app/oracle/product/11.2.0/dbs/orapwphydb' ; } executing Memory Script
Starting backup at 2015-07-02 09:17:44 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=55 instance=racdb2 device type=DISK RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of Duplicate Db command at 07/02/2015 09:17:45 RMAN-05501: aborting duplication of target database RMAN-03015: error occurred in stored script Memory Script RMAN-03009: failure of backup command on ORA_DISK_1 channel at 07/02/2015 09:17:45 ORA-19505: failed to identify file "/oracle/app/oracle/product/11.2.0/dbs/orapwracdb1" ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 |
10、 只讀方式開啟資料庫，這裡忽略了read only，Oracle透過讀取控制檔案來確定它是否是備用資料庫，這裡它將自動置於只讀模式。
|
SQL> alter database open; |
11、 啟用實時查詢，只讀方式開啟後，重啟管理恢復，至此，Oracle ADG搭建完成：
|
SQL>alter database recover managed standby database using current logfile disconnect; |
12、 透過建立表空間、表等操作，檢視adg實時性，測試均正常。
13、 下面幾條adg管理命令
|
--把MRP作為前臺程式啟動，MRP程式也是啟動會話的一部分，如果退出會話，MRP程式也就退出了。
--把MRP作為後臺程式啟動
--讓日誌實時應用 --推遲10分鐘啟動MRP alter database recover managed standby database delay 120 disconnect; --立即讓備庫與主庫同步 alter database recover managed standby database nodelay; --停止MRP alter database recover managed standby database cancel; |
14、 主庫和備庫之間角色切換
|
1、主庫切換為備庫 alter database commit to switchover to physical standby; alter database commit to switchover to physical standby with session shutdown; shutdown immediate startup nomount; alter database mount standby database; alter database recover managed standby database disconnect from session;
2、 從庫切換為主庫 alter database commit to switchover to primary; shutdown immediate; startup |
15、 監視備用資料庫
|
--檢視dg整理情況 Select protection_mode,protection_level,database_role from v$database; --檢視是否使用實時應用
select dest_id,recovery_mode from v$archive_dest_status; select name,value,time_computed from v$datagurad_stats; |
OK
文盲筱燁
2015年7月3日星期五
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29487349/viewspace-1721743/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【DATAGUARD】DG系列之RACtoONE快照備用資料庫的搭建資料庫
- 【DATAGUARD】DG系列之11g物理備庫的搭建
- 【DATAGUARD】DG系列之11g邏輯備庫的搭建
- 【DataGuard】手工冷備搭建 Oracle 11g DataGuard 物理備庫Oracle
- RMAN DUPLICATE建立DataGuard物理備庫
- dataguard之物理備庫丟失資料檔案
- 【DATAGUARD】 基於同一個主機建立物理備庫和邏輯備庫 (四)--新增一個物理dg節點
- 【DATAGUARD】DG系列之邏輯備庫非實時更新小問題處理
- 【DATAGUARD】物理dg的switchover切換（五）
- oracle 之dataguard主庫系統崩潰之物理備庫切主庫Oracle
- dataguard回顧之安裝———使用rman建立物理備庫
- dataguard-建立物理備庫全程解析
- 搭建物理備庫
- 【DataGuard】使用GC建立的物理DataGuard主備庫pfile比較GC
- 【DATAGUARD】物理dg的failover切換(六)AI
- 物理備庫的搭建過程
- 【DG】怎麼使用Data Pump備份物理備庫
- dataguard系列之六------備用資料庫的維護資料庫
- DataGuard搭建物理StandBy
- 【DATAGUARD】Oracle Dataguard物理備庫切換最佳實踐(sqlplus)OracleSQL
- 【DataGuard】Oracle 11g DataGuard 角色轉換(一)物理備庫SwitchoverOracle
- ORACLE DATAGUARD 資料庫---建立物理備用資料庫Oracle資料庫
- Oracle10G Dataguard 多個備庫 - 主庫和物理備庫的切換Oracle
- ORACLE DG之備庫角色Oracle
- dataguard之物理standby庫failover 切換AI
- 物理DG、邏輯DG和快照DG的搭建（視訊講解）
- Oracle物理DG自動切換——Dataguard Broker配置Oracle
- 【DG】Oracle 19c使用dbca來搭建物理DG--主rac備racOracle
- 【DG】怎麼使用Data Pump備份物理備用資料庫資料庫
- 主庫RAC,備庫單節點ASM的dataguard搭建ASM
- 【DG】備庫RMAN還原方式搭建DG（不使用duplicate命令）
- 主庫不停做物理dg
- Data guard 配置之搭建物理備庫
- 10G物理備庫搭建文件
- 【DG】Oracle 19c使用dbca來搭建物理DGOracle
- 【DATAGUARD】DG系列之11g新特性簡單介紹
- 【DATAGUARD】 基於同一個主機建立物理備庫和邏輯備庫（二）
- 【DATAGUARD】 基於同一個主機建立物理備庫和邏輯備庫（一）