关闭

physcial standby database (主-从-从)

标签: oracle数据库dg11g
507人阅读 评论(0) 收藏 举报
分类:
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


至此,完成测试。

0
0
查看评论

PostgreSQL HOT-Standby 的主备切换

http://stoneking.blog.51cto.com/65035/782132 PostgreSQL HOT-Standby 的主备切换 2012-02-17 15:21:06 标签:postgres sql 数据库 主备 --1 环境信息 PostgreSQL...
  • wangdeng1314
  • wangdeng1314
  • 2012-04-04 10:08
  • 1894

使用Duplicate target database命令恢复线上oracle datagard备库

线上oracle datagard备库由于断电以及误删除从库的归档日志文件,所以导致,备库主库数据不一致,备库需要紧急恢复,下面是大概恢复过程1,从主库上面备份控制文件[oracle@localhost rman_recover]$ rman target /RMAN> backup curr...
  • mchdba
  • mchdba
  • 2014-06-03 09:22
  • 2993

Oracle DG故障诊断一则:alter database recover to logical standby new_logical_dbname卡住

我们在基于物理standby的基础上搭建逻辑备库过程过程中,在执行:alter database recover to logical standby READDB;卡住不动,并且alert也没有报错信息,无比郁闷,咨询了别人,聊天记录如下:我们的业务是passport应用,无法停止或者停掉非常麻烦...
  • linwaterbin
  • linwaterbin
  • 2014-04-21 21:35
  • 6003

关于recover managed standby database finish force

主库是rac,备库是单实例,根据o的官档上说到在failover切换时,会使用alter database recover managed standby database finish force;然后alter database commit to switchover to primary将...
  • halou90
  • halou90
  • 2015-10-28 11:41
  • 1629

Oracle创建Physical Standby Database案例

Creating a Physical Standby Database This case is created, operated and followed the steps from oracle online help documentation.  The confi...
  • waterxcfg304
  • waterxcfg304
  • 2014-06-30 13:32
  • 6452

Alter database mount;和Alter database mount standby database;区别

MOUNT Clause Use the MOUNT clause to mount the database. Do not use this clause when the database is already mounted. MOUNT STANDBY ...
  • u011616400
  • u011616400
  • 2014-11-18 10:42
  • 1048

OCM 11g升级考试第二场搭建DataGuard遇到: prod - Physical standby database (disabled) 错误

OCM 11g升级考试第二场搭建DataGuard遇到: prod - Physical standby database (disabled) 错误
  • guoyJoe
  • guoyJoe
  • 2015-02-28 20:28
  • 3447

重新创建standby controlfile

--重新创建standby controlfile ############################################################### 查看alert\trace 文件信息 注:如果主库和备库的数据文件路径不完全一致, 建议通过设置db_fil...
  • zhaojian1988
  • zhaojian1988
  • 2013-07-30 09:32
  • 1236

MySql 主从配置(一主多从)

此次操作实现的是一主两从的方式。主服务器slave2(2.100),从服务器slave2-1(2.107),slave2-2(2.108); 第一:准备主数据库     1. 在不同的机器上分别建立slave2(主库) 和需要进行备份的从库 slave2-1 和slav...
  • www520507
  • www520507
  • 2016-10-10 17:12
  • 2329

Oracle DataGuard学习笔记(3)物理Standby主从角色切换

Oracle DataGuard学习笔记(3)物理Standby主从角色切换     关于Oracle DataGuard的主从角色切换,可以参考官方在线文档:     http://docs.oracle.com/cd/E11882_01/ser...
  • gyming
  • gyming
  • 2017-01-08 20:40
  • 568
    个人资料
    • 访问:123518次
    • 积分:2499
    • 等级:
    • 排名:第17380名
    • 原创:124篇
    • 转载:11篇
    • 译文:2篇
    • 评论:1条
    最新评论