【DG】Oracle之級聯DG--(cascade dg) --(一主一備一級聯)

dataguard 一主一備一級聯，意思是主庫將日誌傳輸給備庫，然後備庫在將日誌傳輸給級聯庫，主庫和級聯庫其實沒有任何關係。另外，在Oracle 11g中，關於資料同步問題，主庫上的操作一般情況下是可以實時同步到備庫的，但是級聯庫必須等備庫歸檔時，才能同步。如果主庫切換日誌，那麼這時級聯庫也能及時同步。

Oracle 11g的級聯備庫是不支援實時應用的，要等源庫日誌切換後才會應用。Oracle 12c的級聯備庫支援實時應用。

在11.2及以上版本支援級聯備庫，就是第二備庫從第一個備庫接受redo日誌，而不是直接從主庫接受redo日誌。

這樣會減少主庫的壓力。實際上和正常搭建DG沒什麼區別，只是改一下引數即可。

最多支援30個級聯備庫，因為LOG_ARCHIVE_DEST_n，只有31個。

更多詳細資訊，參考官方文件： http://docs.oracle.com/database/121/SBYDB/log_transport.htm#SBYDB5126

Oracle 級聯DG部署以及切換測試：

https://blog.csdn.net/weixin_36239782/article/details/91316703

1 說明

A standby database that cascades redo to other standby databases can transmit redo directly from its standby redo log file as soon as it is received from the primary database. Cascaded standby databases receive redo in real-time. They no longer have to wait for standby redo log files to be archived before redo is transmitted.

啟用real-time redo，不需要等歸檔standby redo日誌檔案，然後再傳輸到級聯備庫上。

As of Oracle Database 12c Release 1 (12.1), a cascading standby database can either cascade redo in real-time (as it is being written to the standby redo log file) or non-real-time (as complete standby redo log files are being archived on the cascading standby).

從12c開始，支援real-time級聯redo(等寫入備庫redo log)。

限制：

Only physical standby databases can cascade redo.

Real-time cascading requires a license for the Oracle Active Data Guard option.

Non-real-time cascading is supported on destinations 1 through 10 only. (Real-time cascading is supported on all destinations.)

If you specify ASYNC transport mode on destinations 1 through 10, then redo is shipped in real-time. If you do not specify a transport mode or you specify SYNC on destinations 1 through 10, then redo is shipped in non-real-time. Destinations 11 through 31 operate only in ASYNC (real-time) transport mode.

在用於級聯的備庫中的LOG_ARCHIVE_DEST_n(1…10)指定ASYNC，則是real-time。如果不指定，或者指定SYNC，則是non-real-time。

LOG_ARCHIVE_DEST_n(11…31)只支援ASYNC，即real-time傳輸模式。

2.1 準備工作

和正常搭建DG一樣，安裝資料庫軟體，建立相應的目錄，拷貝引數檔案，密碼檔案等。我這裡演示的是，新增第三個級聯備庫過程。

2.2 主庫修改引數

SQL> Alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(cndba_p,cndba_s,cndba_ss)' scope=both;

2.3 第一備庫修改引數

SQL> Alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(cndba_p,cndba_s,cndba_ss)' scope=both;

SQL> alter system set LOG_ARCHIVE_DEST_2= 'SERVICE=cndba_ss VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=cndba_ss' scope=spfile;

2.4 第二備庫修改引數

*.DB_UNIQUE_NAME=cndba_ss

*.FAL_SERVER=cndba_s

*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(cndba_p,cndba_s,cndba_ss)'

*.LOG_ARCHIVE_DEST_1='LOCATION= USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=cndba_ss'

2.5 主備庫建立TNSNAME

CNDBA_SS =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.173)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = cndba)

)

[oracle@12cdg-p ~]$ tnsping cndba_ss

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 16-AUG-2017 17:36:54

Used parameter files:

/u01/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.173)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = cndba)))

OK (0 msec)

2.6 將第二備庫啟動到nomount

SQL> startup nomount pfile='/u01/app/oracle/product/12.1.0.2/db_1/dbs/initcndba.ora';

ORACLE instance started.

Total System Global Area 2348810240 bytes

Fixed Size 2927048 bytes

Variable Size 1409287736 bytes

Database Buffers 922746880 bytes

Redo Buffers 13848576 bytes

2.7 開始DUPLICATE

注意：還是主庫和第二備庫的DUPLICATE

[oracle@12cdg-p ~]$ rman target [email protected]_p auxiliary [email protected]_ss

Recovery Manager: Release 12.1.0.2.0 - Production on Wed Aug 16 17:38:28 2017

connected to target database: CNDBA (DBID=216462194)

connected to auxiliary database: CNDBA (not mounted)

RMAN> duplicate target database for standby from active database nofilenamecheck;

Starting Duplicate Db at 16-AUG-17

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=23 device type=DISK

contents of Memory Script:

{

backup as copy reuse

targetfile '/u01/app/oracle/product/12.1.0.2/db_1/dbs/orapwcndba' auxiliary format

'/u01/app/oracle/product/12.1.0.2/db_1/dbs/orapwcndba' ;

}

executing Memory Script

Starting backup at 16-AUG-17

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=1 device type=DISK

Finished backup at 16-AUG-17

contents of Memory Script:

{

restore clone from service 'cndba_p' standby controlfile;

}

executing Memory Script

Starting restore at 16-AUG-17

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 cndba_p

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:04

output file name=/u01/app/oracle/oradata/cndba/control01.ctl

output file name=/u01/app/oracle/fast_recovery_area/cndba/control02.ctl

Finished restore at 16-AUG-17

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

"/u01/app/oracle/oradata/cndba/temp01.dbf";

set newname for tempfile 2 to

"/u01/app/oracle/oradata/cndba/pdbseed/pdbseed_temp012017-08-14_12-17-51-PM.dbf";

set newname for tempfile 3 to

"/u01/app/oracle/oradata/cndba/sihong/temp012017-08-14_12-17-51-PM.dbf";

switch clone tempfile all;

set newname for datafile 1 to

"/u01/app/oracle/oradata/cndba/system01.dbf";

set newname for datafile 3 to

"/u01/app/oracle/oradata/cndba/sysaux01.dbf";

set newname for datafile 4 to

"/u01/app/oracle/oradata/cndba/undotbs01.dbf";

set newname for datafile 5 to

"/u01/app/oracle/oradata/cndba/pdbseed/system01.dbf";

set newname for datafile 6 to

"/u01/app/oracle/oradata/cndba/users01.dbf";

set newname for datafile 7 to

"/u01/app/oracle/oradata/cndba/pdbseed/sysaux01.dbf";

set newname for datafile 8 to

"/u01/app/oracle/oradata/cndba/sihong/system01.dbf";

set newname for datafile 9 to

"/u01/app/oracle/oradata/cndba/sihong/sysaux01.dbf";

set newname for datafile 10 to

"/u01/app/oracle/oradata/cndba/sihong/sihong_users01.dbf";

restore

from service 'cndba_p' clone database

;

sql 'alter system archive log current';

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/cndba/temp01.dbf in control file

renamed tempfile 2 to /u01/app/oracle/oradata/cndba/pdbseed/pdbseed_temp012017-08-14_12-17-51-PM.dbf in control file

renamed tempfile 3 to /u01/app/oracle/oradata/cndba/sihong/temp012017-08-14_12-17-51-PM.dbf in control file

executing command: SET NEWNAME

Starting restore at 16-AUG-17

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 cndba_p

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/cndba/system01.dbf

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:35

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: using network backup set from service cndba_p

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/cndba/sysaux01.dbf

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:26

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: using network backup set from service cndba_p

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/cndba/undotbs01.dbf

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: using network backup set from service cndba_p

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/cndba/pdbseed/system01.dbf

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: using network backup set from service cndba_p

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/cndba/users01.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 cndba_p

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/cndba/pdbseed/sysaux01.dbf

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: using network backup set from service cndba_p

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/cndba/sihong/system01.dbf

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:16

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: using network backup set from service cndba_p

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/cndba/sihong/sysaux01.dbf

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: using network backup set from service cndba_p

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00010 to /u01/app/oracle/oradata/cndba/sihong/sihong_users01.dbf

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01

Finished restore at 16-AUG-17

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=3 STAMP=952191686 file name=/u01/app/oracle/oradata/cndba/system01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=4 STAMP=952191686 file name=/u01/app/oracle/oradata/cndba/sysaux01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=5 STAMP=952191686 file name=/u01/app/oracle/oradata/cndba/undotbs01.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=6 STAMP=952191686 file name=/u01/app/oracle/oradata/cndba/pdbseed/system01.dbf

datafile 6 switched to datafile copy

input datafile copy RECID=7 STAMP=952191686 file name=/u01/app/oracle/oradata/cndba/users01.dbf

datafile 7 switched to datafile copy

input datafile copy RECID=8 STAMP=952191686 file name=/u01/app/oracle/oradata/cndba/pdbseed/sysaux01.dbf

datafile 8 switched to datafile copy

input datafile copy RECID=9 STAMP=952191686 file name=/u01/app/oracle/oradata/cndba/sihong/system01.dbf

datafile 9 switched to datafile copy

input datafile copy RECID=10 STAMP=952191686 file name=/u01/app/oracle/oradata/cndba/sihong/sysaux01.dbf

datafile 10 switched to datafile copy

input datafile copy RECID=11 STAMP=952191686 file name=/u01/app/oracle/oradata/cndba/sihong/sihong_users01.dbf

Finished Duplicate Db at 16-AUG-17

2.8 開啟第二備庫並啟用MRP

SQL> alter database open;

Database altered.

SQL> alter database recover managed standby database disconnect;

Database altered.

–檢視MRP程式

SQL> select process,status from v$managed_standby;

PROCESS STATUS

--------- ------------

ARCH CLOSING

ARCH CONNECTED

ARCH CLOSING

RFS IDLE

MRP0 WAIT_FOR_LOG

8 rows selected.

–資料庫狀態

SQL> select database_role,open_mode from v$database;

DATABASE_ROLE OPEN_MODE

---------------- --------------------

PHYSICAL STANDBY READ ONLY WITH APPLY

2.9 檢視日誌序列號

主庫：

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

--------------

46

第一備庫：

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

--------------

46

第二備庫：

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

--------------

46

2.9.1 主庫切換日誌

SQL> alter system switch logfile;

System altered.

–再檢視日誌序列號，全部都為47

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

--------------

47

2 實驗

搭建級聯備庫參考：https://blog.csdn.net/qianglei6077/article/details/90736799

2.1 檢視當前DG配置

SQL> select * from V$DATAGUARD_CONFIG;

DB_UNIQUE_NAME PARENT_DBUN DEST_ROLE CURRENT_SCN CON_ID

------------------------------ ------------------------------ ----------------- ----------- ----------

cndba_p NONE PRIMARY DATABASE 2122746 0

cndba_s cndba_p PHYSICAL STANDBY 2122754 0

cndba_ss cndba_s PHYSICAL STANDBY 2112269 0

2.2 檢視用於級聯的備庫引數–啟用real-time redo

SQL> show parameter LOG_ARCHIVE_DEST_2

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_2 string SERVICE=cndba_ss ASYNC NOAFFIRM VALID_FOR=(ST

ANDBY_LOGFILES,STANDBY_ROLE) D

B_UNIQUE_NAME=cndba_ss

可以看到啟用real-time redo cascade。

2.2.1 主庫建立表，檢視日誌序列號

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

-------------

51

–建立表，並插入資料

SQL> create table cndba(id number);

Table created.

SQL> insert into cndba select object_id from dba_objects;

90947 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from cndba;

COUNT(*)

----------

90947

–可以看到日誌沒有發生切換

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

--------------

51

2.2.2 檢視用於級聯(Cascading )備庫表

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

--------------

51 --日誌序列號沒有變化，表示沒有發生日誌切換

SQL> select count(*) from cndba;

COUNT(*)

----------

90947 --由於DG預設啟用實時redo應用，所以Cascading備庫資料實時傳輸過來，下面注意是驗證cascaded資料是否傳輸過來。

2.2.3 檢視級聯(cascaded)的備庫表

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

--------------

51 --同樣日誌序列號沒有變化。

SQL> select count(*) from cndba;

COUNT(*)

----------

90947 --資料已經傳輸過來了，符合預期。

從日誌中也可以檢視出來：

Recovery of Online Redo Log: Thread 1 Group 4 Seq 51 Reading mem 0

Mem# 0: /u01/app/oracle/fast_recovery_area/CNDBA_SS/onlinelog/o1_mf_4_ds84tg8t_.log

2.3 修改用於級聯備庫(Cascading )的引數-啟用non-real-time

SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=cndba_ss SYNC VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=cndba_ss' scope=both;

System altered.

SQL> show parameter LOG_ARCHIVE_DEST_2

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

log_archive_dest_2 string SERVICE=cndba_ss SYNC VALID_FO

R=(STANDBY_LOGFILES,STANDBY_RO

LE) DB_UNIQUE_NAME=cndba_ss

2.3.1 主庫插入資料

SQL> insert into cndba select object_id from dba_objects;

90947 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from cndba;

COUNT(*)

----------

181894

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

--------------

51

2.3.2 檢視用於級聯(Cascading )備庫表\

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

--------------

51

SQL> select count(*) from cndba;

COUNT(*)

----------

181894

2.3.3 檢視級聯(cascaded)的備庫表

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)

--------------

51

SQL> select count(*) from cndba;

COUNT(*)

----------

181894 --可以看到資料沒有同步過來。

從日誌也可以看出來：當前日誌時51，等52日誌來程式恢復。

Media Recovery Waiting for thread 1 sequence 52

至此對於Real-time redo的介紹已經結束了。該特性還是非常有用的，對於資料容災更加可靠。

About Me

........................................................................................................................

● 本文作者：小麥苗，部分內容整理自網路，若有侵權請聯絡小麥苗刪除

● 本文在itpub、部落格園、CSDN和個人微 信公眾號（
xiaomaimiaolhr）上有同步更新

● 本文itpub地址：
http://blog.itpub.net/26736162 

● 本文部落格園地址：
http://www.cnblogs.com/lhrbest 

● 本文CSDN地址：
https://blog.csdn.net/lihuarongaini 

● 本文pdf版、個人簡介及小麥苗雲盤地址：
http://blog.itpub.net/26736162/viewspace-1624453/ 

● 資料庫筆試面試題庫及解答：
http://blog.itpub.net/26736162/viewspace-2134706/ 

● DBA寶典今日頭條號地址：
http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826 

........................................................................................................................

● QQ群號：
230161599
、618766405

● 微 信群：可加我微 信，我拉大家進群，非誠勿擾

● 聯絡我請加QQ好友
（
646634621
），註明新增緣由

● 於 2019-09-01 06:00 ~ 2019-09-31 24:00 在西安完成

● 最新修改時間：2019-09-01 06:00 ~ 2019-09-31 24:00

● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解

● 版權所有，歡迎分享本文，轉載請保留出處

........................................................................................................................

● 
小麥苗的微店：
https://weidian.com/s/793741433?wfr=c&ifr=shopdetail

● 
小麥苗出版的資料庫類叢書：
http://blog.itpub.net/26736162/viewspace-2142121/

● 
小麥苗OCP、OCM、高可用網路班：
http://blog.itpub.net/26736162/viewspace-2148098/

● 
小麥苗騰訊課堂主頁：
https://lhr.ke.qq.com/

........................................................................................................................
使用

微 信客戶端掃描下面的二維碼來關注小麥苗的微 信公眾號（
xiaomaimiaolhr）及QQ群（DBA寶典）、新增小麥苗微 信，
學習最實用的資料庫技術。 


........................................................................................................................

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2656076/，如需轉載，請註明出處，否則將追究法律責任。

【DG】Oracle之級聯DG--(cascade dg) --(一主一備一級聯)

【DG】Oracle之級聯DG--(cascade dg) --(一主一備一級聯)

Oracle 級聯DG部署以及切換測試：

相關文章