手把手教你搭建2节点dataguard物理备库

lzyever

已于 2024-02-26 22:38:13 修改

阅读量406

点赞数 11

分类专栏： Oracle 文章标签： oracle 数据库

于 2024-02-25 00:10:28 首次发布

本文链接：https://blog.csdn.net/lzyever/article/details/136277589

版权

Oracle 专栏收录该内容

139 篇文章 5 订阅

订阅专栏

本文一步一步详细搭建Oracle 11gR2 2节点Dataguard备库。

环境说明：
主库和备库是基于Roseha双机热备模式。默认情况下，pri-db01是active状态，pri-db02是slave状态。
现在搭建的是主库双机热备环境下，配置2节点dataguard standby 数据库。

主库1：pri-db01
主库2：pri-db02
备库1：std-db01
备库2：std-db02

1、主库：启用强制日志模式:

alter database force logging;
select name,log_mode,force_logging from v$database;

2、主库：将主库的密码文件拷贝到备库：

cd /orasys/database/11g/dbs
scp orapworcl oracle@172.50.1.102:/orasys/database/11g/dbs/orapworcldg
scp orapworcl oracle@172.50.1.103:/orasys/database/11g/dbs/orapworcldg2

3、主库：创建 standby redolog 日志组

alter database add standby logfile group 101 ('/oradata/orcl/redo101a.log','/oradata/orcl/redo101b.log') size 200m;
alter database add standby logfile group 102 ('/oradata/orcl/redo102a.log','/oradata/orcl/redo102b.log') size 200m;
alter database add standby logfile group 103 ('/oradata/orcl/redo103a.log','/oradata/orcl/redo103b.log') size 200m;
alter database add standby logfile group 104 ('/oradata/orcl/redo104a.log','/oradata/orcl/redo104b.log') size 200m;
alter database add standby logfile group 105 ('/oradata/orcl/redo105a.log','/oradata/orcl/redo105b.log') size 200m;
alter database add standby logfile group 106 ('/oradata/orcl/redo106a.log','/oradata/orcl/redo106b.log') size 200m;


select group#,sequence#,status, bytes/1024/1024 from v$standby_log;
select group#,member from v$logfile order by group#;

4、主库：修改spfile参数，主库db01修改后，拷贝到主库db02

create pfile from spfile;

4.1、编辑pfile：

orcl.__db_cache_size=226492416
orcl.__java_pool_size=4194304
orcl.__large_pool_size=71303168
orcl.__oracle_base='/orasys'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=104857600
orcl.__sga_target=419430400
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=109051904
orcl.__streams_pool_size=0
*.archive_lag_target=0
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_file_record_keep_time=30
*.db_block_size=8192
*.db_domain=''
*.db_recovery_file_dest='/oradata/flashback'
*.db_recovery_file_dest_size=524288000
*.db_ultra_safe='DATA_ONLY'
*.dg_broker_start=TRUE
*.diagnostic_dest='/orasys'
orcl.log_archive_format='%t_%s_%r.arc'
*.log_archive_min_succeed_dest=1
orcl.log_archive_trace=0
*.open_cursors=300
*.pga_aggregate_target=104857600
*.processes=150
*.sga_max_size=419430400
*.sga_target=419430400
*.undo_tablespace='UNDOTBS1'

*.audit_file_dest='/orasys/admin/orcl/adump'
*.control_files='/oradata/orcl/control01.ctl','/oradata/orcl/control02.ctl'，'/oradata/orcl/control03.ctl'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

*.service_names='ORCL'
*.db_name='orcl'
*.DB_UNIQUE_NAME='orcl'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orcldg1,orcldg2)'
*.log_archive_dest_1='LOCATION=/oradata/archivelog      VALID_FOR=(ALL_LOGFILES,ALL_ROLES)     DB_UNIQUE_NAME=orcl'
*.log_archive_dest_2='SERVICE=orcldg1  LGWR SYNC AFFIRM  VALID_FOR=(online_logfiles,PRIMARY_ROLE)  DB_UNIQUE_NAME=orcldg1'
*.log_archive_dest_3='SERVICE=orcldg2 LGWR SYNC AFFIRM  VALID_FOR=(online_logfiles,PRIMARY_ROLE)  DB_UNIQUE_NAME=orcldg2'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_dest_state_3='ENABLE'
*.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'
*.LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'
*.log_archive_max_processes=30

*.FAL_CLIENT='orcl'
*.fal_server='orcldg1'
*.fal_server='orcldg2'
*.db_file_name_convert='/oradata/orcldg1','/oradata/orcl'
*.db_file_name_convert='/oradata/orcldg2','/oradata/orcl'
*.log_file_name_convert='/oradata/orcldg1','/oradata/orcl'
*.log_file_name_convert='/oradata/orcldg2','/oradata/orcl'
*.standby_file_management='AUTO'

4.2、将主库的pfile拷贝到pri-db02,std-db01,std-db02

cd /orasys/database/11g/dbs
scp initorcl.ora oracle@pri-db02:/orasys/database/11g/dbs/initorcl.ora
scp initorcl.ora oracle@std-db01:/orasys/database/11g/dbs/initorcldg1.ora
scp initorcl.ora oracle@std-db02:/orasys/database/11g/dbs/initorcldg2.ora

用修改过的pfile，再创建一个spfile，启动数据库
shutdown immediate
create spfile from pfile;
startup;
show parameter spfile;
确认启用日志强制：
select name,log_mode,force_logging from v$database;

5、主备库：修改监听文件，添加静态监听

5.1、主库1：

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = pri-db01)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
#manual insert static lisenter
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
     (GLOBAL_DBNAME= orcl)
     (ORACLE_HOME = /orasys/database/11g)
     (SID_NAME = orcl)
    )
    (SID_DESC =
     (GLOBAL_DBNAME = orcl_DGMGRL)
     (ORACLE_HOME = /orasys/database/11g)
     (SID_NAME = orcl)
    )
  )
ADR_BASE_LISTENER = /orasys

5.2、主库2：

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = pri-db02)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
#manual insert static lisenter
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
     (GLOBAL_DBNAME= orcl)
     (ORACLE_HOME = /orasys/database/11g)
     (SID_NAME = orcl)
    )
    (SID_DESC =
     (GLOBAL_DBNAME = orcl_DGMGRL)
     (ORACLE_HOME = /orasys/database/11g)
     (SID_NAME = orcl)
    )
  )
ADR_BASE_LISTENER = /orasys

5.3、备库1：

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = std-db01)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
#manual insert static lisenter
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
     (GLOBAL_DBNAME= orcldg1)
     (ORACLE_HOME = /orasys/database/11g)
     (SID_NAME = orcldg1)
    )
    (SID_DESC =
     (GLOBAL_DBNAME = orcldg1_DGMGRL)
     (ORACLE_HOME = /orasys/database/11g)
     (SID_NAME = orcldg1)
    )
  )
ADR_BASE_LISTENER = /orasys

5.4、备库2：

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = std-db02)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
#manual insert static lisenter
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
     (GLOBAL_DBNAME= orcldg2)
     (ORACLE_HOME = /orasys/database/11g)
     (SID_NAME = orcldg2)
    )
    (SID_DESC =
     (GLOBAL_DBNAME = orcldg2_DGMGRL)
     (ORACLE_HOME = /orasys/database/11g)
     (SID_NAME = orcldg2)
    )
  )
ADR_BASE_LISTENER = /orasys

6、主备库：配置tnsname

6.1、主库1：

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.50.1.1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

ORCLDG1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = std-db01)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcldg)
    )
  )
ORCLDG2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = std-db02)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcldg2)
    )
   )

6.2、主库2：

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.50.1.1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

ORCLDG1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = std-db01)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcldg1)
    )
  )
ORCLDG2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = std-db02)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcldg2)
    )
   )

6.3、备库1：

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.50.1.1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

ORCLDG1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = std-db01)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcldg1)
    )
  )
ORCLDG2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = std-db02)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcldg2)
    )
   )

6.4、备库2：

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.50.1.1)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

ORCLDG1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = std-db01)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcldg1)
    )
  )
ORCLDG2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = std-db02)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcldg2)
    )
   )

7、备库修改pfile文件：

7.1、备库1：

orcldg备节点1：

orcl.__db_cache_size=482344960
orcldg.__db_cache_size=226492416
orcl.__java_pool_size=4194304
orcldg.__java_pool_size=4194304
orcl.__large_pool_size=8388608
orcldg.__large_pool_size=71303168
orcl.__oracle_base='/orasys'#ORACLE_BASE set from environment
orcldg.__oracle_base='/orasys'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=159383552
orcldg.__pga_aggregate_target=104857600
orcl.__sga_target=683671552
orcldg.__sga_target=419430400
orcl.__shared_io_pool_size=0
orcldg.__shared_io_pool_size=0
orcl.__shared_pool_size=180355072
orcldg.__shared_pool_size=109051904
orcl.__streams_pool_size=0
orcldg.__streams_pool_size=0
*.archive_lag_target=0
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='/oradata/flashback'
*.db_recovery_file_dest_size=524288000
*.db_ultra_safe='DATA_ONLY'
*.dg_broker_start=TRUE
*.diagnostic_dest='/orasys'
orcldg.log_archive_format='%t_%s_%r.arc'
*.log_archive_min_succeed_dest=1
orcldg.log_archive_trace=0
*.open_cursors=300
*.pga_aggregate_target=104857600
*.processes=150
*.sga_max_size=419430400
*.sga_target=419430400
*.undo_tablespace='UNDOTBS1'

*.audit_file_dest='/orasys/admin/orcldg1/adump'
*.control_files='/oradata/orcldg1/control01.ctl','/oradata/orcldg1/control02.ctl','/oradata/orcldg1/control03.ctl'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orcldg1XDB)'

*.service_names='ORCL'
*.db_name='orcl'
*.DB_UNIQUE_NAME='orcldg1'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orcldg1,orcldg2)'
*.log_archive_dest_1='LOCATION=/oradata/archivelog      VALID_FOR=(ALL_LOGFILES,ALL_ROLES)     DB_UNIQUE_NAME=orcldg1'
*.log_archive_dest_2='service=orcl    LGWR SYNC AFFIRM  VALID_FOR=(online_logfiles,PRIMARY_ROLE)  DB_UNIQUE_NAME=orcl'
*.log_archive_dest_3='service=orcldg2 LGWR SYNC AFFIRM  VALID_FOR=(online_logfiles,PRIMARY_ROLE)  DB_UNIQUE_NAME=orcldg2'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_dest_state_3='ENABLE'
*.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'
*.LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'
*.log_archive_max_processes=30

*.FAL_CLIENT='orcldg1'
*.fal_server='orcl'
*.fal_server='orcldg2'
*.db_file_name_convert='/oradata/orcl','/oradata/orcldg1'
*.db_file_name_convert='/oradata/orcldg2','/oradata/orcldg1'
*.log_file_name_convert='/oradata/orcl','/oradata/orcldg1'
*.log_file_name_convert='/oradata/orcldg2','/oradata/orcldg1'
*.standby_file_management='AUTO'

7.2、备库2：

orcldg2节点:

orcl.__db_cache_size=226492416
orcl.__java_pool_size=4194304
orcl.__large_pool_size=71303168
orcl.__oracle_base='/orasys'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=104857600
orcl.__sga_target=419430400
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=109051904
orcl.__streams_pool_size=0
*.archive_lag_target=0
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_file_record_keep_time=30
*.db_block_size=8192
*.db_domain=''
*.db_recovery_file_dest='/oradata/flashback'
*.db_recovery_file_dest_size=524288000
*.db_ultra_safe='DATA_ONLY'
*.dg_broker_start=TRUE
*.diagnostic_dest='/orasys'
orcl.log_archive_format='%t_%s_%r.arc'
*.log_archive_min_succeed_dest=1
orcl.log_archive_trace=0
*.open_cursors=300
*.pga_aggregate_target=104857600
*.processes=150
*.service_names='ORCL'
*.sga_max_size=419430400
*.sga_target=419430400
*.undo_tablespace='UNDOTBS1'

*.audit_file_dest='/orasys/admin/orcldg2/adump'
*.control_files='/oradata/orcldg2/control01.ctl','/oradata/orcldg2/control02.ctl','/oradata/orcldg2/control03.ctl'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orcldg2XDB)'

*.service_names='ORCL'
*.db_name='orcl'
*.DB_UNIQUE_NAME='orcldg2'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orcldg1,orcldg2)'
*.log_archive_dest_1='LOCATION=/oradata/archivelog      VALID_FOR=(ALL_LOGFILES,ALL_ROLES)     DB_UNIQUE_NAME=orcldg2'
*.log_archive_dest_2='service=orcl   LGWR SYNC AFFIRM   valid_for=(online_logfiles,PRIMARY_ROLE)  db_unique_name=orcl'
*.log_archive_dest_3='service=orcldg1 LGWR SYNC AFFIRM   valid_for=(online_logfiles,PRIMARY_ROLE)  db_unique_name=orcldg1'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_dest_state_3='ENABLE'
*.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'
*.LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'
*.log_archive_max_processes=30

*.FAL_CLIENT='orcldg2'
*.fal_server='orcl'
*.fal_server='orcldg'
*.db_file_name_convert='/oradata/orcl','/oradata/orcldg2'
*.db_file_name_convert='/oradata/orcldg','/oradata/orcldg2'
*.log_file_name_convert='/oradata/orcl','/oradata/orcldg2'
*.log_file_name_convert='/oradata/orcldg','/oradata/orcldg2'
*.standby_file_management='AUTO'

8、在备库创建目录：

orcldg1:

mkdir  -p /orasys/admin/orcldg1/adump
mkdir  -p /orasys/diag/rdbms/orcldg1/orcldg1/trace
mkdir  -p /oradata/orcldg1
mkdir  -p /oradata/archivelog
chmod -R 775 /orasys/
chmod -R 775 /oradata/
chown -R oracle:oinstall /orasys/
chown -R oracle:oinstall /oradata/

orcldg2:

mkdir  -p /orasys/admin/orcldg2/adump
mkdir  -p /orasys/diag/rdbms/orcldg2/orcldg2/trace
mkdir  -p /oradata/orcldg2
mkdir  -p /oradata/archivelog
chmod -R 775 /orasys/
chmod -R 775 /oradata/
chown -R oracle:oinstall /orasys/
chown -R oracle:oinstall /oradata/

9、备库：使用pfile创建spfile

备库1：
sqlplus / as sysdba
create spfile from pfile;
startup nomount;

备库2：
sqlplus / as sysdba
create spfile from pfile;
startup nomount;

10、使用rman恢复备库：

备库1：
使用duplicate复制orcldg备库：
rman target sys/oraSYS.9189@orcl auxiliary sys/oraSYS.9189@orcldg1
RMAN>duplicate target database for standby from active database nofilenamecheck;

备库2：
使用duplicate复制orcldg2备库：
rman target sys/oraSYS.9189@orcl auxiliary sys/oraSYS.9189@orcldg2
RMAN>duplicate target database for standby from active database nofilenamecheck;


尝试开启备库：
备库恢复完成后，是mount状态。
select status from v$instance;
STATUS
------------
MOUNTED

11、备库启动日志应用

alter database recover managed standby database disconnect from session;
停止日志应用的命令是： alter database recover managed standby database cancel;
查看归档是否一致：
archive log list;

12、主库：开启最大可用模式

alter database set standby database to maximize availability;
查看主库状态：
SQL> select name,log_mode,open_mode,protection_mode from V$DATABASE;
NAME	  LOG_MODE     OPEN_MODE	    PROTECTION_MODE
--------- ------------ -------------------- --------------------
ORCL	  ARCHIVELOG   READ WRITE	    MAXIMUM AVAILABILITY
SQL> 
查看备库状态：
SQL> select name,log_mode,open_mode,protection_mode from V$DATABASE;
NAME	  LOG_MODE     OPEN_MODE	    PROTECTION_MODE
--------- ------------ -------------------- --------------------
ORCL	  ARCHIVELOG   MOUNTED		    MAXIMUM AVAILABILITY

13、配置Dataguard broker：

主库broker配置：
主库查询switchover状态
SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE    SWITCHOVER_STATUS
----------------            --------------------
PRIMARY              TO STANDBY

主库启用dg_broker_start
SQL> alter system set dg_broker_start = true;
SQL> show parameter dg_broker_start;
NAME            TYPE        VALUE
------------------------ -------------------- ---------------
dg_broker_start     boolean     TRUE

listener文件中加入静态监听
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = pri-db01)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
     (GLOBAL_DBNAME= orcl)
     (ORACLE_HOME = /orasys/database/11g)
     (SID_NAME = orcl)
    ) 
    (SID_DESC =
     (GLOBAL_DBNAME = orcl_DGMGRL)
     (ORACLE_HOME = /orasys/database/11g)
     (SID_NAME = orcl)
    )
  )
ADR_BASE_LISTENER = /orasys

lsnrctl reload
lsnrctl stop
lsnrctl start


备库broker配置：
备库启用dg_broker_start
备库1：
SQL> alter system set dg_broker_start = true;
SQL> show parameter dg_broker_start;
NAME            TYPE        VALUE
------------------------ -------------------- ---------------
dg_broker_start     boolean     TRUE

listener文件中加入静态监听
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = std-db01)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
     (GLOBAL_DBNAME= orcldg)
     (ORACLE_HOME = /orasys/database/11g)
     (SID_NAME = orcldg)
    ) 
    (SID_DESC =
     (GLOBAL_DBNAME = orcldg_DGMGRL)
     (ORACLE_HOME = /orasys/database/11g)
     (SID_NAME = orcldg)
    )
  )
ADR_BASE_LISTENER = /orasys

lsnrctl reload
lsnrctl stop
lsnrctl start



备库2：
SQL> alter system set dg_broker_start = true;
SQL> show parameter dg_broker_start;
NAME            TYPE        VALUE
------------------------ -------------------- ---------------
dg_broker_start     boolean     TRUE

listener文件中加入静态监听
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = std-db02)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
     (GLOBAL_DBNAME= orcldg2)
     (ORACLE_HOME = /orasys/database/11g)
     (SID_NAME = orcldg2)
    ) 
    (SID_DESC =
     (GLOBAL_DBNAME = orcldg2_DGMGRL)
     (ORACLE_HOME = /orasys/database/11g)
     (SID_NAME = orcldg2)
    )
  )
ADR_BASE_LISTENER = /orasys

lsnrctl reload
lsnrctl stop
lsnrctl start




在主库：配置broker，实现主备切换
dgmgrl sys/oracle@orcl
dgmgrl sys/oraSYS.9189@orcl
创建DGMGRL 数据库，数据库名自定义：
DGMGRL> create configuration DGDB as primary database is orcl connect identifier is orcl;
添加备库到broker数据库中：
DGMGRL> add database orcldg1 as connect identifier is orcldg1 maintained as physical;
DGMGRL> add database orcldg2 as connect identifier is orcldg2 maintained as physical;
        CREATE CONFIGURATION dgdb AS PRIMARY DATABASE IS orcl CONNECT IDENTIFIER IS orcl;

add database orcldg1 as connect identifier is orcldg1;
add database orcldg2 as connect identifier is orcldg2;

alter system set LOG_ARCHIVE_DEST_2='service=orcldg1 async valid_for=(online_logfiles,primary_role) db_unique_name=auxdb' scope=both;

alter system set log_archive_dest_2='service=manualdb async valid_for=(online_logfiles,primary_role) db_unique_name=manualdb' scope=both;

启用broker
DGMGRL> enable configuration;

启用实时应用日志：
DGMGRL> EDIT DATABASE orcl SET PROPERTY DelayMins = 0;

DGMGRL> EDIT DATABASE orcldg SET PROPERTY DelayMins = 0;

DGMGRL> EDIT DATABASE orcldg2 SET PROPERTY DelayMins = 0;

查看配置信息
DGMGRL> show configuration

DGMGRL> show database orcldg

DGMGRL> show database verbose orcldg


查询dg状态：
select  name,log_mode,open_mode,protection_mode,database_role,switchover_status from v$database;




主备库不同步日志:报错如下：
PING[ARCo]: Heartbeat failed to connect to standby 'orcldg2'. Error is 1033.
解决方法是：停止主备库，重新将主库的密码文件传输给备库，然后开启主备库即可。




使用dgmgrl添加orcldg2备库到broker数据库中：
dgmgrl sys/oracle@orcl
DGMGRL>add database orcldg2 as connect identifier is orcldg2 maintained as physical;

解决orcldg2 出现disabled状态：orcldg2 - Physical standby database (disabled)
DMGMRL>enable database orcldg2;


解决orcl    - Primary database
      Warning: ORA-16792: configurable property value is inconsistent with database setting
查看主备库状态：
发现如下不同：
DbFileNameConvert和LogFileNameConvert参数不同：
DGMGRL>show database verbose orcl
Database - orcl
  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    orcl
      Warning: ORA-16714: the value of property DbFileNameConvert is inconsistent with the database setting
      Warning: ORA-16714: the value of property LogFileNameConvert is inconsistent with the database setting
    Properties:
			DbFileNameConvert               = '/oradata/orcldg, /oradata/orcl'
   	 LogFileNameConvert              = '/oradata/orcldg, /oradata/orcl'
DGMGRL>show database verbose orcldg
 Properties:
    DbFileNameConvert               = '/oradata/orcl, /oradata/orcldg'
    LogFileNameConvert              = '/oradata/orcl, /oradata/orcldg'

DGMGRL>show database verbose orcldg2
Properties:
		DbFileNameConvert               = '/oradata/orcl, /oradata/orcldg2, /oradata/orcldg, /oradata/orcldg2'
    LogFileNameConvert              = '/oradata/orcl, /oradata/orcldg2, /oradata/orcldg, /oradata/orcldg2'


解决方法是：
使用edit命令修改为：
DGMGRL>
 EDIT DATABASE ORCL SET PROPERTY DbFileNameConvert = '/oradata/orcldg, /oradata/orcl, /oradata/orcldg2, /oradata/orcl';
 EDIT DATABASE ORCL SET PROPERTY LogFileNameConvert = '/oradata/orcldg, /oradata/orcl, /oradata/orcldg2, /oradata/orcl';
 
 EDIT DATABASE orcldg SET PROPERTY DbFileNameConvert = '/oradata/orcl, /oradata/orcldg, /oradata/orcldg2, /oradata/orcldg';
 EDIT DATABASE orcldg SET PROPERTY LogFileNameConvert = '/oradata/orcl, /oradata/orcldg, /oradata/orcldg2, /oradata/orcldg';
 
修改完成后，重启数据库：
关闭：
关闭主库：
shutdown immediate；
关闭备库1：
shutdown immediate;
关闭备库2：
shutdown immediate；

开启：
开启备库1到mount：
startup mount；
开启备库2到mount：
startup mount；
开启主库：
startup

启动完毕后，使用dgmgrl查看状态：
[oracle@pri-db01 admin]$ dgmgrl sys/oracle@orcl
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> show configuration
Configuration - dgorcldb
  Protection Mode: MaxAvailability
  Databases:
    orcl    - Primary database
    orcldg  - Physical standby database
    orcldg2 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL> 
***主库、备库1、备库2均正常。

14、测试failover

failover后，备节点无法启动问题解决。
主库没有变成standby状态，而一直是primary状态。
要恢复，必须是开启闪回才行，一般情况下，闪回不会关闭。
在dgmgrl中执行reinstate database orcldg2 。如果失败，则需要重新使用duplicate建库，首先从pfile创建一个spfile启动到nomount状态，执行duplicate建库。
再在dgmgrl中启用备库，enable database orcldg2
此时备库就成功同步了。



 
 
failover测试：
将主库强制关机，使用备库登录dgmgrl
dgmgrl sys/oracle@orcldg
将备库升级为主库：
DGMGRL>failover to orcldg

升级成功后，查看角色:
DGMGRL>show configuration

备库成功修复后，再次开机，开启监听，将数据库启动到mount
su - oracle
lsnrctl start
sqlplus / as sysdba
startup mount

备库启动到mount后，在dgmgrl中修复备库：
DGMGRL>reinstate database orcl

成功后，再次查看
DGMGRL>show configuration
主备库角色已经正常

lzyever

关注

11
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
手把手教你搭建2节点dataguard物理备库

本文一步一步详细搭建Oracle 11gR2 2节点Dataguard备库。环境说明：主库和备库是基于Roseha双机热备模式。默认情况下，pri-db01是active状态，pri-db02是slave状态。现在搭建的是主库双机热备环境下，配置2节点dataguard standby 数据库。主库1：pri-db01主库2：pri-db02备库1：std-db01备库2：std-db02。
复制链接

扫一扫

专栏目录