devbox
知新_RL
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

手把手教你搭建2节点dataguard物理备库_oracle双机热备dataguard

作者：知新_RL | 2024-08-13 20:31:43

赞

踩

oracle双机热备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

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

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

  • 1
  • 2

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

  • 1
  • 2
  • 3

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#;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10

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

create pfile from spfile;

  • 1

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'

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56

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;

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12

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

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22

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

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22

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

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22

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

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22

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)
    )
   )

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25

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)
    )
   )

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25

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)
    )
   )
   

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26

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)
    )
   )

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25

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'

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67

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'

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57

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/

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8

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/

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8

9、备库：使用pfile创建spfile

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

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


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10

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


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18

11、备库启动日志应用

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


  • 1
  • 2
  • 3
  • 4
  • 5

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


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13

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均正常。
 

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68
  • 69
  • 70
  • 71
  • 72
  • 73
  • 74
  • 75
  • 76
  • 77
  • 78
  • 79
  • 80
  • 81
  • 82
  • 83
  • 84
  • 85
  • 86
  • 87
  • 88
  • 89
  • 90
  • 91
  • 92
  • 93
  • 94
  • 95
  • 96
  • 97
  • 98
  • 99
  • 100
  • 101
  • 102
  • 103
  • 104
  • 105
  • 106
  • 107
  • 108
  • 109
  • 110
  • 111
  • 112
  • 113
  • 114
  • 115
  • 116
  • 117
  • 118
  • 119
  • 120
  • 121
  • 122
  • 123
  • 124
  • 125
  • 126
  • 127
  • 128
  • 129
  • 130
  • 131
  • 132
  • 133
  • 134
  • 135
  • 136
  • 137
  • 138
  • 139
  • 140
  • 141
  • 142
  • 143
  • 144
  • 145
  • 146
  • 147
  • 148
  • 149
  • 150
  • 151
  • 152
  • 153
  • 154
  • 155
  • 156
  • 157
  • 158
  • 159
  • 160
  • 161
  • 162
  • 163
  • 164
  • 165
  • 166
  • 167
  • 168
  • 169
  • 170
  • 171
  • 172
  • 173
  • 174
  • 175
  • 176
  • 177
  • 178
  • 179
  • 180
  • 181
  • 182
  • 183
  • 184
  • 185
  • 186
  • 187
  • 188
  • 189
  • 190
  • 191
  • 192
  • 193
  • 194
  • 195
  • 196
  • 197
  • 198
  • 199
  • 200
  • 201
  • 202
  • 203
  • 204
  • 205
  • 206
  • 207
  • 208
  • 209
  • 210
  • 211
  • 212
  • 213
  • 214
  • 215
  • 216
  • 217
  • 218
  • 219
  • 220
  • 221
  • 222
  • 223
  • 224
  • 225
  • 226
  • 227
  • 228
  • 229
  • 230
  • 231
  • 232
  • 233
  • 234
  • 235
  • 236
  • 237
  • 238
  • 239
  • 240
  • 241
  • 242
  • 243
  • 244
  • 245

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
主备库角色已经正常

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/知新_RL/article/detail/976439
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号