physcial standby database (主-从-从)

原创 2015年11月17日 23:05:45
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_TYPEDATABASE_ROLE,我们基本上可以将其理解为:发送指定角色生成的指定类型的日志文件,该参数的主要目的是为了确保,一旦发生角色切换操作后数据库的正常运转

其中,REDO_LOG_TYPEDATABASE_ROLE两个参数可供选择的参数值如下:

REDO_LOG_TYPE可设置为ONLINE_LOGFILESTANDBY_LOGFILEALL_LOGFILES。  

DATABASE_ROLE可设置为PRIMARY_ROLESTANDBY_ROLEALL_ROLES。 

 

注意VALID_FOR参数默认值VALID_FOR=ALL_LOGFILES,ALL_ROLES)。 

 

推荐手动设置该参数而不要使用默认值,在某些情况下默认的参数值不一定合适,如逻辑Standby在默认情况下就处于OPEN READ WRITE模式,不仅有REDO数据而且还包含多种日志文件(Online RedologsArchived RedologsStandby 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


至此,完成测试。

版权声明:本文为博主原创文章,未经博主允许不得转载。

相关文章推荐

9i下创建standby database 步骤.doc

  • 2009年07月25日 15:45
  • 28KB
  • 下载

oracle standby database

  • 2011年05月18日 08:39
  • 15KB
  • 下载

Oracle 11g Active Data Guard step by step: How to create a Physical Standby Database using RMAN

Database IP DB_UNIQUE_NAME Oracle Net Service Name  Primary 192.168.8.1...

How to grant access to SQL logins on a standby database when the guest user is disabled in SQL Serve

Steps to reproduce the behavior In the following example, Pubs is the database, server1 is the se...
  • pbsh
  • pbsh
  • 2012年01月29日 11:45
  • 363

Step By Step Guide To Create Physical Standby Database Using RMAN Backup and Restore (Doc ID 469493.

In this Document   Goal   Solution   References Applies to: Oracle Database - ...

Primary Database Changes That Require Manual Intervention at a Physical Standby

Primary Database Changes That Require Manual Intervention at a Physical Standby Most structural cha...

【翻译自mos文章】重新创建物理standby database 的 控制文件的方法

【翻译自mos文章】重新创建物理standby database 的 控制文件的方法

Creating a 10gr2 Data Guard Physical Standby database with Real-Time apply [ID 343424.1]

Creating a 10gr2 Data Guard Physical Standby database with Real-Time apply [ID 343424.1] Modified 14...

fast incremental backup failed on standby database

一套Linux上的11.1.0.7的physical standby物理备库在使用fast incremental backup进行高于0级的增量备份时会出现ORA-19648错误,其出错记录如下: ...
  • halou90
  • halou90
  • 2015年10月23日 09:46
  • 295
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:physcial standby database (主-从-从)
举报原因:
原因补充:

(最多只允许输入30个字)