physcial standby database （主-从-从）

1， DG 主-从-从   主要配置参数： 以下摘自官当：

Primary Database

DB_UNIQUE_NAME=boston

FAL_SERVER=boston2

LOG_ARCHIVE_CONFIG='DG_CONFIG=(boston,boston2,denver)'

LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST

VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=boston'

LOG_ARCHIVE_DEST_2='SERVICE=boston2 SYNC

VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=boston2'

midle database:

Cascading Physical Standby(备用品) Database

DB_UNIQUE_NAME=boston2

FAL_SERVER=boston

LOG_ARCHIVE_CONFIG= 'DG_CONFIG=(boston,boston2,denver)'

LOG_ARCHIVE_DEST_1='LOCATION= USE_DB_RECOVERY_FILE_DEST

VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=boston2'

LOG_ARCHIVE_DEST_2 = 'SERVICE=denver

VALID_FOR=( STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=denver'

standby database

Cascaded Physical Standby Database

DB_UNIQUE_NAME=denver

FAL_SERVER=boston2

LOG_ARCHIVE_CONFIG='DG_CONFIG=(boston,boston2,denver)'

LOG_ARCHIVE_DEST_1='LOCATION= USE_DB_RECOVERY_FILE_DEST

VALID_FOR=(ALL_LOGFILES,ALL_ROLES)  DB_UNIQUE_NAME=denver'

2， 测试：

主库：  SID=sfpay2

             DB_UNIQUE_NAME=sfpay2

             IP: 10.118.242.216    

             hostname= sfpay.datatest.mysql01

  

中间备库：  sid=sfpay2

                    DB_UNIQUE_NAME=sfpaydg1

                    IP: 10.118.242.215 

                   hostname= sfpay.datatest.mysql02

级联备库：  sid=sfpay2

                   DB_UNIQUE_NAME=sfpaydg2

                   IP:10.118.230.27 

                   hostname= sfpay.datatest.mysql03

版本：    linux 6.4 redhat x86 64bit

               Oracle 11.2.0,4

因为SID 相同：   密码文件 直接拷贝。

因方便测试，全部是本地文件系统。

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

监听文件： 

主库：  listener.ora  

[ oracle@sfpay admin]$ cat listener.ora 

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

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

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

ADR_BASE_LISTENER = /u01/app/oracle

中间备库： listener.ora 

[ oracle@sfpay admin]$ cat listener.ora 

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = sfpaydg1)   

      (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/dbhome_1)

      (SID_NAME = sfpay2)

    )

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = sfpay.datatest.mysql02)(PORT = 1521))

    )

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

ADR_BASE_LISTENER = /u01/app/oracle

级联备库： listener.ora  

[ oracle@sfpay admin]$ cat listener.ora 

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0.4/dbhome_1/network/admin/listener.ora

# Generated by Oracle configuration tools.

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = sfpaydg2)   

      (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/dbhome_1)

      (SID_NAME = sfpay2)

    )

  )

LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = sfpay.datatest.mysql03)(PORT = 1521))

    )

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

  )

ADR_BASE_LISTENER = /u01/app/oracle

监听文件：  tnsnames.ora  相同；

##  --主库-- ##

SFPAY2 =

  (DESCRIPTION =

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

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = sfpay2)

      (SID = sfpay2)

    )

  )

## -- 中间备库 -- ##

TO_DG1 =

  (DESCRIPTION =

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

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = sfpaydg1)

       (SID = sfpay2)

    )

  )

## -- 级联备库 -- ##

TO_DG2 =

(DESCRIPTION =

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

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = sfpaydg2)

       (SID = sfpay2)

    )

  )

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

参数文件： spfile

主库： pfile

sfpay2.__db_cache_size=1207959552

sfpay2.__java_pool_size=16777216

sfpay2.__large_pool_size=33554432

sfpay2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

sfpay2.__pga_aggregate_target=1056964608

sfpay2.__sga_target=1577058304

sfpay2.__shared_io_pool_size=0

sfpay2.__shared_pool_size=285212672

sfpay2.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/sfpay2/adump'

*.audit_trail='db'

*.compatible='11.2.0.4.0'

*.control_files='/u01/app/oracle/oradata/sfpay2/control01.ctl','/u01/app/oracle/fast_recovery_area/sfpay2/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*. db_file_name_convert='/u01/app/oracle/oradata/sfpaydg1/','/u01/app/oracle/oradata/sfpay2/','/u01/app/oracle/oradata/sfpaydg2','/u01/app/oracle/oradata/sfpay2/','/data/oradata1/','/data/oradata/','/data/oradata2/','/data/oradata/'

*.db_name='sfpay2'

*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

*.db_recovery_file_dest_size=4385144832

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=sfpay2XDB)'

*.fal_server='sfpaydg1'

*.log_archive_config='dg_config=( sfpay2,sfpaydg1,sfpaydg2 )'

*.log_archive_dest_1='location=/data/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=sfpay2'

*.log_archive_dest_2='service= to_dg1  sync affirm lgwr valid_for=( online_logfiles,primary_role ) db_unique_name= sfpaydg1 '

*.log_archive_dest_state_1='enable'

*.log_archive_dest_state_2='ENABLE'

*.log_archive_format='%t_%s_%r.dbf'

*.memory_target=2621440000

*.open_cursors=300

*.processes=1000

*.remote_login_passwordfile='EXCLUSIVE'

*.sessions=1105

*.standby_file_management='auto'

*.star_transformation_enabled='TRUE'

*.undo_tablespace='UNDOTBS1'

--这里  log_file_name_convert 因 路径都一致，就没写。

中间备库  pfile:

sfpay2.__db_cache_size=1191182336

sfpay2.__java_pool_size=16777216

sfpay2.__large_pool_size=33554432

sfpay2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

sfpay2.__pga_aggregate_target=1056964608

sfpay2.__sga_target=1577058304

sfpay2.__shared_io_pool_size=0

sfpay2.__shared_pool_size=301989888

sfpay2.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/sfpaydg1/adump'

*.audit_trail='db'

*.compatible='11.2.0.4.0'

*.control_files='/u01/app/oracle/oradata/sfpaydg1/control01.ctl','/u01/app/oracle/fast_recovery_area/sfpaydg1/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_file_name_convert='/u01/app/oracle/oradata/sfpay2/','/u01/app/oracle/oradata/sfpaydg1/','/u01/app/oracle/oradata/sfpaydg2','/u01/app/oracle/oradata/sfpaydg1/','/data/oradata/','/data/oradata1/','/data/oradata2/','/data/oradata1/'

*.db_name='sfpay2'

*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

*.db_recovery_file_dest_size=4385144832

*.db_unique_name='sfpaydg1'

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=sfpay2XDB)'

*.fal_server='sfpay2'

*.log_archive_config='dg_config=(sfpay2,sfpaydg1,sfpaydg2)'

*.log_archive_dest_1='location=/data/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=sfpaydg1'

* .log_archive_dest_2='service=to_dg2 sync affirm lgwr valid_for=( all_logfiles,all_roles) db_unique_name=sfpaydg2'

*.log_archive_dest_3='service=sfpay2 sync affirm lgwr valid_for=(all_logfiles,primary_role) db_unique_name=sfpay2'

*.log_archive_dest_state_1='enable'

*.log_archive_dest_state_2='ENABLE'

*.log_archive_dest_state_3='DEFER'

*.log_archive_format='%t_%s_%r.dbf'

*.memory_target=2621440000

*.open_cursors=300

*.processes=1000

*.remote_login_passwordfile='EXCLUSIVE'

*.sessions=1105

*.standby_file_management='auto'

*.star_transformation_enabled='TRUE'

*.undo_tablespace='UNDOTBS1'

级联数据 ： pfile 

sfpay2.__db_cache_size=469762048

sfpay2.__java_pool_size=16777216

sfpay2.__large_pool_size=419430400

sfpay2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

sfpay2.__pga_aggregate_target=822083584

sfpay2.__sga_target=1207959552

sfpay2.__shared_io_pool_size=0

sfpay2.__shared_pool_size=285212672

sfpay2.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/sfpaydg2/adump'

*.audit_trail='db'

*.compatible='11.2.0.4.0'

*.control_files='/u01/app/oracle/oradata/sfpaydg2/control01.ctl','/u01/app/oracle/fast_recovery_area/sfpaydg2/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_file_name_convert='/u01/app/oracle/oradata/sfpay2/','/u01/app/oracle/oradata/sfpaydg2/','/u01/app/oracle/oradata/sfpaydg1','/u01/app/oracle/oradata/sfpaydg2/','/data/oradata/','/data/oradata2/','/data/oradata1/','/data/oradata2/'

*.db_name='sfpay2'

*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

*.db_recovery_file_dest_size=4385144832

*.db_unique_name='sfpaydg2'

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=sfpay2XDB)'

*.fal_client='SFPAYDG2'   --可忽略

*.fal_server='sfpaydg1'

*.log_archive_config='dg_config=(sfpay2,sfpaydg1,sfpaydg2)'

*.log_archive_dest_1='location=/data/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=sfpaydg2'

*.log_archive_dest_state_1='enable'

*.log_archive_dest_state_2='defer'

*.log_archive_format='%t_%s_%r.dbf'

*.memory_target=2021440000

*.open_cursors=300

*.processes=1000

*.remote_login_passwordfile='EXCLUSIVE'

*.sessions=1105

*.standby_file_management='auto'

*.star_transformation_enabled='TRUE'

*.undo_tablespace='UNDOTBS1'

这里级联数据库的备份duplicate  直接使用的第一份rman备份

VALID_FOR属性指定传输及接收对象

LOG_ARCHIVE_DEST_n参数中的VALID_FOR属性，用来指定传输的内容。从字面理解VALID_FOR就是基于那谁有效，该属性有两个参数值需要指定：REDO_LOG_TYPE和DATABASE_ROLE，我们基本上可以将其理解为：发送指定角色生成的指定类型的日志文件，该参数的主要目的是为了确保，一旦发生角色切换操作后数据库的正常运转。

其中，REDO_LOG_TYPE和DATABASE_ROLE两个参数可供选择的参数值如下：

REDO_LOG_TYPE：可设置为ONLINE_LOGFILE、STANDBY_LOGFILE、ALL_LOGFILES。  

DATABASE_ROLE：可设置为PRIMARY_ROLE、STANDBY_ROLE、ALL_ROLES。 

 

注意：VALID_FOR参数默认值是：VALID_FOR=（ALL_LOGFILES,ALL_ROLES）。 

 

推荐手动设置该参数而不要使用默认值，在某些情况下默认的参数值不一定合适，如逻辑Standby在默认情况下就处于OPEN READ WRITE模式，不仅有REDO数据而且还包含多种日志文件（Online Redologs、Archived Redologs及Standby Redologs）。

默认情况下，逻辑Standby数据库生成的归档文件和接收到的归档文件在相同的路径下，这既不便于管理，也极有可能带来一些隐患。建议对每个LOG_ARCHIVE_DEST_n参数设置合适的VALID_FOR属性。本地生成的归档文件和接收到的归档文件最好分别保存于不同路径下。

日志接受：

主： 强制切换：

Archived Log entry 71 added for thread 1 sequence 57 ID 0x9646c158 dest 1:

Destination LOG_ARCHIVE_DEST_2 is SYNCHRONIZED

Destination LOG_ARCHIVE_DEST_2 no longer supports SYNCHRONIZATION

Thread 1 advanced to log sequence 59 (LGWR switch)

  Current log# 2 seq# 59 mem# 0: /home/oracle/oradata/redo2.dbf

Wed Nov 18 14:56:44 2015

Archived Log entry 73 added for thread 1 sequence 58 ID 0x9646c158 dest 1:

中间备库：

RFS[1]: Opened log for thread 1 sequence 58 dbid -1773766056 branch 890541914

Archived Log entry 37 added for thread 1 sequence 58 rlc 890541914 ID 0x9646c158 dest 2:

RFS[1]: No standby redo logfiles created

RFS[1]: Opened log for thread 1 sequence 59 dbid -1773766056 branch 890541914

Wed Nov 18 14:56:44 2015

Media Recovery Log /data/archivelog/1_56_890541914.dbf

Media Recovery Log /data/archivelog/1_57_890541914.dbf

Media Recovery Log /data/archivelog/1_58_890541914.dbf

Media Recovery Waiting for thread 1 sequence 59 (in transit)

Wed Nov 18 14:56:59 2015

ARC3: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (3113)

ARC3: RFS network connection re-established at host 'to_dg2'

ARC3: RFS destination opened for reconnect at host 'to_dg2'

级联备库：

Wed Nov 18 14:54:08 2015

RFS[2]: Opened log for thread 1 sequence 57 dbid -1773766056 branch 890541914

Archived Log entry 18 added for thread 1 sequence 58 rlc 890541914 ID 0x9646c158 dest 2:

Archived Log entry 19 added for thread 1 sequence 57 rlc 890541914 ID 0x9646c158 dest 2:

Wed Nov 18 14:54:08 2015

RFS[3]: Assigned to RFS process 4748

RFS[3]: Opened log for thread 1 sequence 56 dbid -1773766056 branch 890541914

Archived Log entry 20 added for thread 1 sequence 56 rlc 890541914 ID 0x9646c158 dest 2:

Wed Nov 18 14:54:11 2015

Media Recovery Log /data/archivelog/1_56_890541914.dbf

Media Recovery Log /data/archivelog/1_57_890541914.dbf

Media Recovery Log /data/archivelog/1_58_890541914.dbf

Media Recovery Waiting for thread 1 sequence 59

v$managed_standby

主：

SQL> select process,status,sequence# from v$managed_standby; 

PROCESS   STATUS    SEQUENCE#

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

ARCH      CLOSING           57

ARCH      CLOSING           58

ARCH      CLOSING           45

ARCH      CLOSING           56

LGWR      WRITING           59

SQL> host

[ oracle@sfpay ~]$ hostname

sfpay.datatest.mysql01

SQL> /

OPEN_MODE         DATABASE_ROLE    DB_UNIQUE_NAME             FLASHBACK_ON

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

READ WRITE         PRIMARY          sfpay2                 NO

中：

SQL> select process,status,sequence# from v$managed_standby; 

PROCESS   STATUS    SEQUENCE#

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

ARCH      CLOSING           57

ARCH      CONNECTED        0

ARCH      CLOSING           58

ARCH      CLOSING           56

RFS      IDLE            0

MRP0      WAIT_FOR_LOG           59

RFS      IDLE               59

RFS      IDLE            0

8 rows selected.

[ oracle@sfpay ~]$ hostname

sfpay.datatest.mysql02

SQL> /

OPEN_MODE         DATABASE_ROLE    DB_UNIQUE_NAME             FLASHBACK_ON

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

READ ONLY WITH APPLY PHYSICAL STANDBY sfpaydg1                 NO

级：

SQL> select process,status,sequence# from v$managed_standby; 

PROCESS   STATUS    SEQUENCE#

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

ARCH      CONNECTED        0

ARCH      CONNECTED        0

ARCH      CONNECTED        0

ARCH      CONNECTED        0

MRP0      WAIT_FOR_LOG           59

RFS      IDLE            0

RFS      IDLE            0

RFS      IDLE            0

8 rows selected.

SQL> host

[ oracle@sfpay ~]$ hostname

sfpay.datatest.mysql03

SQL> /

OPEN_MODE         DATABASE_ROLE    DB_UNIQUE_NAME             FLASHBACK_ON

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

MOUNTED          PHYSICAL STANDBY sfpaydg2                 NO

至此，完成测试。