Oracle physical dataguard with broker部署

一、环境说明

主库：10.110.96.88

备库：10.110.96.87

数据库实例：gisc

二、主库操作

1.开启force logging

ALTER DATABASE FORCE LOGGING;

2.开启日志归档

a.查看日志归档是否开启

ARCHIVE LOG LIST;

b.如果没有开始归档则开启

1 SHUTDOWN IMMEDIATE;
2 STARTUP MOUNT;
3 ALTER DATABASE ARCHIVELOG;
4 ALTER DATABASE OPEN;

3.创建standby redo logs

a.查看重做日志文件组信息的两个视图v$log,v$logfile

b.修改重做日志文件大小

默认重做日志文件组有3个，因为重做日志文件组至少为2个，所以采用删除一个再创建一个的方式进行依次修改。

1 ALTER DATABASE DROP LOGFILE GROUP 1;
2 ALTER DATABASE ADD LOGFILE GROUP 1('/home/oracle/app/oradata/gisc/redo01.log') SIZE 1G;
3 ALTER DATABASE DROP LOGFILE GROUP 2;
4 ALTER DATABASE ADD LOGFILE GROUP 2('/home/oracle/app/oradata/gisc/redo02.log') SIZE 1G;
5 ALTER DATABASE DROP LOGFILE GROUP 3;
6 ALTER DATABASE ADD LOGFILE GROUP 3('/home/oracle/app/oradata/gisc/redo03.log') SIZE 1G;

因为日志文件组3在使用，所以修改时报了异常：

SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-01624: log 3 needed for crash recovery of instance gisc (thread 1)
ORA-00312: online log 3 thread 1: '/home/oracle/app/oradata/gisc/redo03.log'

解决方法是执行：ALTER SYSTEM CHECKPOINT，如下代码示：

1 ALTER SYSTEM CHECKPOINT;2 ALTER DATABASE DROP LOGFILE GROUP 3; 3 ALTER DATABASE ADD LOGFILE GROUP 3('/home/oracle/app/oradata/gisc/redo03.log') SIZE 1G;

c.The standby redo logs should be at least as big as the largest online redo log and there should be one extra group per thread compared the online redo logs.

d.创建standby redo logs操作语句：

1 ALTER DATABASE ADD STANDBY LOGFILE ('/home/oracle/app/oradata/gisc/standby_redo01.log') SIZE 1g;
2 ALTER DATABASE ADD STANDBY LOGFILE ('/home/oracle/app/oradata/gisc/standby_redo02.log') SIZE 1g;
3 ALTER DATABASE ADD STANDBY LOGFILE ('/home/oracle/app/oradata/gisc/standby_redo03.log') SIZE 1g;
4 ALTER DATABASE ADD STANDBY LOGFILE ('/home/oracle/app/oradata/gisc/standby_redo04.log') SIZE 1g;

4.开启闪回数据库

该操作需在日志归档模式开启后操作。

ALTER DATABASE FLASHBACK ON;

5.设置初始化参数

a.参数设置命令格式

alter system set parameter_name=value  [scope=both/spfile/memory]

memory:仅对内存中参数有效，数据库重启后失效；

spfile:仅对spfile参数文件有效，对当前运行的实例无效，重启数据库后生效；

both:对内存和参数文件都有效。

If a server parameter file was used to start up the database, then BOTH is the default. If a parameter file was used to start up the database, then MEMORY is the default, as well as the only scope you can specify.

b.设置参数内容

 1 DB_NAME=gisc
 2 DB_UNIQUE_NAME=gisc
 3 LOG_ARCHIVE_CONFIG='DG_CONFIG=(gisc,gisc_stdby)'
 4 CONTROL_FILES='/home/oracle/app/oradata/gisc/control01.ctl', '/home/oracle/app/fast_recovery_area/gisc/control02.ctl'#日志本地归档
 5 LOG_ARCHIVE_DEST_1=
 6  'LOCATION=/home/oracle/app/oradata/archive_logs
 7   VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
 8   DB_UNIQUE_NAME=gisc'#日志发往备库，配置broker时LOG_ARCHIVE_DEST_2需设置为空,配置broker成功后会自动配置
 9 #LOG_ARCHIVE_DEST_2=
10 #'SERVICE=GISC87 ASYNC
11 #  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
12 #  DB_UNIQUE_NAME=gisc_stdby'
13 REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
14 LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' scope=spfile
15 
16 #以下参数主库切换为备库时发挥作用
18 FAL_SERVER=gisc_stdby#数据文件和日志文件目录转换路径，主备库可不一样，前面是备库路径，后面是主库路径。
19 DB_FILE_NAME_CONVERT='/home/oracle/app/oradata/gisc','/home/oracle/app/oradata/gisc' scope=spfile
20 LOG_FILE_NAME_CONVERT='/home/oracle/app/oradata/gisc','/home/oracle/app/oradata/gisc'  scope=spfile
21 STANDBY_FILE_MANAGEMENT='AUTO'

6.为备库创建初始化参数文件

执行如下命令从spfile创建pfile，以便以文本方式为备库修改参数

create pfile='/home/oracle/dg/initgisc.ora' from spfile;

7.修改监听参数$ORACLE_HOME/network/admin/listener.ora

 1 LISTENER =
 2   (DESCRIPTION_LIST =
 3     (DESCRIPTION =
 4       (ADDRESS = (PROTOCOL = TCP)(HOST = gisc-db2)(PORT = 1521))
 5       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
 6     )
 7   )
 8 SID_LIST_LISTENER =
 9   (SID_LIST =
10     (SID_DESC =#注意加_DGMGRL后缀，配置broker时用到
11       (GLOBAL_DBNAME = gisc_DGMGRL)
12       (ORACLE_HOME = /home/oracle/app/oracle/product/12.1.0/dbhome)
13       (SID_NAME = gisc)
14     )
15   )
16 
17 ADR_BASE_LISTENER = /home/oracle/app

8.修改客户端连接参数$ORACLE_HOME/network/admin/tnsnames.ora，备库需执行同样的操作

 1 gisc88 =
 2   (DESCRIPTION =
 3     (ADDRESS = (PROTOCOL = TCP)(HOST = 10.110.96.88)(PORT = 1521))
 4     (CONNECT_DATA =
 5       (SERVER = DEDICATED)
 6       (SERVICE_NAME = gisc)
 7     )
 8   )
 9 gisc87 =
10   (DESCRIPTION =
11     (ADDRESS = (PROTOCOL = TCP)(HOST = 10.110.96.87)(PORT = 1521))
12     (CONNECT_DATA =
13       (SERVER = DEDICATED)
14      # (SERVICE_NAME = gisc_stdby)
15 
16 　　(SID = gisc)
17 
18 　　(UR=A)
19     )
20   )

三、备库操作

1.拷贝主库密码文件到备库相应位置

scp oracle@10.0.96.88:/home/oracle/app/oracle/product/12.1.0/dbhome/dbs/orapwgisc $ORACLE_HOME/dbs

2.拷贝一.6中生成的参数文件到备库，并修改为备库中的相应参数值。

a.拷贝到备库

scp oracle@10.0.96.88:/home/oracle/dg/initgisc.ora  $ORACLE_HOME/dg

b. 修改备库中的相应参数，其中涉及备库中的路径，应改为相应备库中的路径，如基路径、数据文件路径、日志文件路径、恢复区路径、控制文件路径等。

这里主备库路径一致，所以相应路径就不用改了。

 1 gisc.__oracle_base='/home/oracle/app'  2  3 *.audit_file_dest='/home/oracle/app/admin/gisc/adump'  4  5 *.control_files='/home/oracle/app/oradata/gisc/control01.ctl','/home/oracle/app/fast_recovery_area/gisc/control02.ctl'  6  7 *.db_file_name_convert='/home/oracle/app/oradata/gisc','/home/oracle/app/oradata/gisc'  8  9 *.log_file_name_convert='/home/oracle/app/oradata/gisc','/home/oracle/app/oradata/gisc' 10 11 *.db_recovery_file_dest='/home/oracle/app/fast_recovery_area' 12 13 *.diagnostic_dest='/home/oracle/app' 14 15 DB_UNIQUE_NAME=gisc_stdby 16 LOG_ARCHIVE_DEST_1= 17 'LOCATION=/home/oracle/app/oradata/archive_logs 18  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) 19  DB_UNIQUE_NAME=gisc_stdby'#日志发往主库，配置broker时LOG_ARCHIVE_DEST_2需设置为空,转换为主库时会自动配置 20 #LOG_ARCHIVE_DEST_2= 21 #'SERVICE=gisc88 ASYNC 22 # VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) 23 # DB_UNIQUE_NAME=gisc' 24 25 FAL_SERVER=gisc

3.创建相应目录

mkdir -p /home/oracle/app/oradata/gisc/pdbseed
mkdir -p /home/oracle/app/oradata/gisc/pdb1
mkdir -p /home/oracle/app/fast_recovery_area/gisc
mkdir -p /home/oracle/app/admin/gisc/adump

4.确保设置了环境变量：

export ORACLE_SID=gisc

5.确保配置了监听，如果没有可用netca图形界面进行配置。

6.修改监听参数$ORACLE_HOME/network/admin/listener.ora

 1 LISTENER =
 2   (DESCRIPTION_LIST =
 3     (DESCRIPTION =
 4       (ADDRESS = (PROTOCOL = TCP)(HOST = gisc-db2)(PORT = 1521))  5 (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))  6  )  7  )  8 SID_LIST_LISTENER =  9 (SID_LIST = 10 (SID_DESC =#注意加_DGMGRL后缀，配置broker时用到 11 (GLOBAL_DBNAME = gisc_stdby_DGMGRL) 12 (ORACLE_HOME = /home/oracle/app/oracle/product/12.1.0/dbhome) 13 (SID_NAME = gisc) 14  ) 15  ) 16 17 ADR_BASE_LISTENER = /home/oracle/app

7.修改客户端连接参数$ORACLE_HOME/network/admin/tnsnames.ora

 1 gisc88 =
 2   (DESCRIPTION =
 3     (ADDRESS = (PROTOCOL = TCP)(HOST = 10.110.96.88)(PORT = 1521))  4 (CONNECT_DATA =  5 (SERVER = DEDICATED)  6 (SERVICE_NAME = gisc)  7  )  8  )  9 gisc87 = 10 (DESCRIPTION = 11 (ADDRESS = (PROTOCOL = TCP)(HOST = 10.110.96.87)(PORT = 1521)) 12 (CONNECT_DATA = 13 (SERVER = DEDICATED) 14 # (SERVICE_NAME = gisc_stdby) 16 　　　　(SID = gisc) 18 　　　　(UR=A) 19  ) 20 )

8.为备库创建服务器参数文件spfile

create spfile from pfile='/home/oracle/dg/initgisc.ora' ;

9.启动备库到nomount状态

startup nomount;

10.rman连接主备库,并执行同步

a0:确保主备库防火墙开通了oralce 1521端口，否则执行主备同步时会出现以下错误：

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 01/24/2019 22:58:26
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 01/24/2019 22:58:26
ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-12543: TNS:destination host unreachable
ORA-17629: Cannot connect to the remote database server

a1:主备都要重启监听和数据库？

a.连接主备库

rman target sys/*****@GISC88 auxiliary sys/****@GISC87

b.执行主备同步

DUPLICATE TARGET DATABASE
  FOR STANDBY
  FROM ACTIVE DATABASE
  DORECOVER
  NOFILENAMECHECK;

11. 启动归档日志应用进程

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

四.配置broker

1.确认主备库log_archive_dest_2设置为空，如果不为空设置为空

show parameter log_archive_dest_2;alter system set log_archive_dest_2='';

2.在主备库开启dg_broker_start

ALTER SYSTEM SET dg_broker_start=true;

3.连接主库配置broker

a.dgmgrl连接主库

dgmgrl sys/****@GISC88

b.创建配置，指定主库

CREATE CONFIGURATION dg_config AS PRIMARY DATABASE IS gisc CONNECT IDENTIFIER IS gisc88;

c.添加备库

ADD DATABASE gisc_stdby AS CONNECT IDENTIFIER IS gisc87 MAINTAINED AS PHYSICAL;

d.使配置生效

ENABLE CONFIGURATION;

e.查看主备库状态

DGMGRL> SHOW CONFIGURATION;

Configuration - dg_config

  Protection Mode: MaxPerformance
  Members:
  gisc       - Primary database
    gisc_stdby - Physical standby database 

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 32 seconds ago)

DGMGRL> SHOW DATABASE GISC;

Database - gisc

  Role:               PRIMARY
  Intended State:     TRANSPORT-ON
  Instance(s):
    gisc

Database Status:
SUCCESS

DGMGRL> SHOW DATABASE gisc_stdby;

Database - gisc_stdby

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 1 second ago)
  Apply Lag:          0 seconds (computed 1 second ago)
  Average Apply Rate: 39.00 KByte/s
  Real Time Query:    ON
  Instance(s):
    gisc

Database Status:
SUCCESS

五.主库中确认主备库重做日志传输和应用情况

SQL> SELECT CLIENT_PROCESS, PROCESS, THREAD#, SEQUENCE#, STATUS FROM
  2  V$MANAGED_STANDBY WHERE CLIENT_PROCESS='LGWR' OR PROCESS='MRP0'; CLIENT_P PROCESS THREAD# SEQUENCE# STATUS -------- --------- ---------- ---------- ------------ N/A MRP0 1 326 APPLYING_LOG LGWR RFS 1 326 IDLE

六.DGMGRL中正常运行时的主备切换SWITCHOVER

a.主库切到87

DGMGRL>SWITCHOVER TO gisc_stdby;

b.主库切回到88

DGMGRL>SWITCHOVER TO gisc;

七.DGMGRL中主库故障时的主备切换FAILOVER

假设主库故障，需切换备库为主库。DGMGRL连接到备库进行如下操作：

a.切换备库为主库

DGMGRL>FAILOVER TO gisc_stdby;

b.原主库故障恢复后，重做其状态，使其为备库，该操作需在闪回数据库开启的状态下进行。

DGMGRL>REINSTATE DATABASE gisc;

c.如果闪回数据库没有开启，原主数据库故障恢复后，要恢复其为备库，需按以下步骤重做

c1. Cleanup the old instance,可把以下代码写成bash脚本。

 1 rm -Rf $ORACLE_BASE/oradata/$ORACLE_SID/*
 2 rm -Rf $ORACLE_BASE/fast_recovery_area/$ORACLE_SID
 3 rm -Rf $ORACLE_BASE/fast_recovery_area/$ORACLE_SID_stdby
 4 rm -Rf $ORACLE_BASE/admin/$ORACLE_SID
 5 rm $ORACLE_HOME/dbs/spfile*.ora
 6 rm $ORACLE_HOME/dbs/init*.ora
 7 
 8 mkdir -p $ORACLE_BASE/fast_recovery_area/$ORACLE_SID
 9 mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/adump
10 mkdir -p $ORACLE_BASE/oradata/$ORACLE_SID/pdbseed
11 mkdir -p $ORACLE_BASE/oradata/$ORACLE_SID/pdb1

c2. Connect to RMAN.

$ rman TARGET sys/***@gisc_stby AUXILIARY sys/***@gisc

c3.Duplicate the database.

DUPLICATE TARGET DATABASE
  FOR STANDBY
  FROM ACTIVE DATABASE
  DORECOVER
  NOFILENAMECHECK;

c4. Connect to DGMDRL on the current primary.

$ dgmgrl sys/***@gisc_stby

c5. Enable the new standby.

DGMGRL> ENABLE DATABASE gisc;

其他：

1.oralce服务器查看错误号命令：oerr ora 错误号，如下所示：

[oracle@gisc-db1 data_guard]$ oerr ora 01624
01624, 00000, "log %s needed for crash recovery of instance %s (thread %s)"
// *Cause:  A log cannot be dropped or cleared until the thread's checkpoint
//          has advanced out of the log.
// *Action: If the database is not open, then open it. Crash recovery will
//          advance the checkpoint. If the database is open force a global
//          checkpoint. If the log is corrupted so that the database cannot
//          be opened, it may be necessary to do incomplete recovery until
//          cancel at this log.

主要参考：

1.Data Guard Physical Standby Setup Using the Data Guard Broker in Oracle Database 12c Release 1

2.修改oracle日志文件大小