database's docs

5.1.0 oracle dataguard 11g r2 一主多备搭建


0. 环境准备

1主多从dataguard架构说明
1主多从dataguard最佳实践

同时也参考了1主1从的文档 step by step doc
官方知识介绍

hostname ip ORACLE_UNIQUE_NAME ORACLE_SID
node1.dataguard.com 192.168.33.91 EXAMPLE EXAMPLE
node2.dataguard.com 192.168.33.92 EXAMPLE_STBY1 EXAMPLE
node3.dataguard.com 192.168.33.93 EXAMPLE_STBY2 EXAMPLE
  • oracle 版本:11.2.0.3
  • OS 版本:CENTOS 6.8
  • node1 为主数据库,安装了软件和数据库实例
  • node2和node3为备数据库,仅安装了软件

1. 主服务器配置(node1)

1) 更改日志模式为强制记录日志

-- 查看日志模式
SELECT log_mode FROM v$database;

-- 开启日志归档
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

-- 开启强制日志记录模式
ALTER DATABASE FORCE LOGGING;

2) 生成standby redo log

-- 查看redo log和standby redo log
SELECT GROUP#, BYTES FROM V$LOG;
SELECT GROUP#, BYTES FROM V$STANDBY_LOG;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/u01/app/oracle/oradata/EXAMPLE/standby_redo01.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/u01/app/oracle/oradata/EXAMPLE/standby_redo02.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/u01/app/oracle/oradata/EXAMPLE/standby_redo03.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/u01/app/oracle/oradata/EXAMPLE/standby_redo04.log') SIZE 50M;

3) 初始化参数

step 1. 检查DB_UNIQUE_NAME和DB_NAME

show parameter db_name
show parameter db_unique_name

主库和备库上的DB_NAME需要一致,而DB_UNIQUE_NAME需要不同。
DB_UNIQUE_NAME会用在DG_CONFIG中的LOG_ARCHIVE_CONFIG参数中。
此例中,我们给node2的db_unique_name分别为EXAMPLE_STBY

step 2. 配置LOG_ARCHIVE_CONFIG参数

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(EXAMPLE,EXAMPLE_STBY1,EXAMPLE_STBY2)';

step 3. 配置归档日志储存位置

ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=EXAMPLE';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=example_stby1 NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=EXAMPLE_STBY1';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='SERVICE=example_stby2 NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=EXAMPLE_STBY2';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE;

step 4. 配置LOG_ARCHIVE_FORMAT和LOG_ARCHIVE_MAX_PROCESSES
将REMOTE_LOGIN_PASSWORDFILE设置为exclusive.

ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30;
ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

step 5. 除了以上配置之外,推荐也确保主库可以切换为备库

ALTER SYSTEM SET FAL_SERVER=EXAMPLE_STBY1,EXAMPLE_STBY2;
ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/EXAMPLE','/u01/app/oracle/oradata/EXAMPLE' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/EXAMPLE','/u01/app/oracle/oradata/EXAMPLE' SCOPE=SPFILE;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

4) 监听服务安装

需要在各服务器的"$ORACLE_HOME/network/admin/tnsnames.ora"中配置主库和备库的入口。
可以使用netca或者手动创建以下内容

step 1. 配置listener.ora

vim /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
**********************************************
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = EXAMPLE)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = EXAMPLE)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = node1.dataguard.com)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle
**********************************************

step 2. 配置tnsnames.ora

vim /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
**********************************************
EXAMPLE =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = node1.dataguard.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID=EXAMPLE)
    )
  )

EXAMPLE_STBY1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = node2.dataguard.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID=EXAMPLE)
    )
  )

EXAMPLE_STBY2 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = node3.dataguard.com)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID=EXAMPLE)
    )
  )
**********************************************

step 3. 重启监听服务

lsnrctl stop
lsnrctl start

5) 备份主库

rman target=/
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

6) 创建备库控制文件和PFILE

ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/example_stby.ctl';
CREATE PFILE='/tmp/initEXAMPLE_stby.ora' FROM SPFILE;

2. 从库安装配置(手动)

1) 拷贝文件即适配本地化

step 1. 拷贝文件

# 创建数据库相关目录
mkdir -p /u01/app/oracle/oradata/EXAMPLE
mkdir -p /u01/app/oracle/fast_recovery_area/EXAMPLE
mkdir -p /u01/app/oracle/admin/EXAMPLE/adump

# 拷贝控制文件到所有目录
scp oracle@192.168.33.91:/tmp/example_stby.ctl /u01/app/oracle/oradata/EXAMPLE/control01.ctl
cp /u01/app/oracle/oradata/EXAMPLE/control01.ctl /u01/app/oracle/fast_recovery_area/EXAMPLE/control02.ctl

# 拷贝备份的数据库文件和日志文件
scp -r oracle@192.168.33.91:/u01/app/oracle/fast_recovery_area/EXAMPLE/archivelog /u01/app/oracle/fast_recovery_area/EXAMPLE/
scp -r oracle@192.168.33.91:/u01/app/oracle/fast_recovery_area/EXAMPLE/backupset /u01/app/oracle/fast_recovery_area/EXAMPLE/

# 拷贝参数文件
scp -r oracle@192.168.33.91:/tmp/initEXAMPLE_stby.ora /tmp/

# 拷贝远程登陆密码文件
scp -r oracle@192.168.33.91:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwEXAMPLE /u01/app/oracle/product/11.2.0/dbhome_1/dbs

step 2. 修改pfile文件使其适配备库

vim /tmp/initEXAMPLE_stby.ora
***************************************
# node2只需要修改以下字段
*.db_unique_name='EXAMPLE_STBY1'
*.fal_client='EXAMPLE_STBY1'
*.fal_server='EXAMPLE'
*.log_archive_config='DG_CONFIG=(EXAMPLE,EXAMPLE_STBY1)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=EXAMPLE_STBY1';
*.log_archive_dest_2='SERVICE=example NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=EXAMPLE'
# 删除*.log_archive_dest_3和*.log_archive_dest_state_3
*.log_archive_dest_3='...'

# node3只需要修改以下字段
*.db_unique_name='EXAMPLE_STBY2'
*.fal_client='EXAMPLE_STBY2'
*.fal_server='EXAMPLE'
*.log_archive_config='DG_CONFIG=(EXAMPLE,EXAMPLE_STBY2)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=EXAMPLE_STBY2';
*.log_archive_dest_2='SERVICE=example NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=EXAMPLE'
# 删除*.log_archive_dest_3和*.log_archive_dest_state_3
*.log_archive_dest_3='...'
***************************************

2) 开启监听程序

scp -r oracle@192.168.33.91:/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
scp -r oracle@192.168.33.91:/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
vim /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
***************************************
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = EXAMPLE)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = EXAMPLE)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = node2.dataguard.com)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle
***************************************

lsnrctl stop
lsnrctl start

3) 恢复备份

从pfile中创建SPFILE

export ORACLE_SID=EXAMPLE
sqlplus / as sysdba

CREATE SPFILE FROM PFILE='/tmp/initEXAMPLE_stby.ora';

恢复数据库

rman target=/
RMAN> STARTUP MOUNT;
RMAN> RESTORE DATABASE;

4) 创建redo日志

-- 查看redo log和standby redo log
SELECT GROUP#, BYTES FROM V$LOG;
SELECT GROUP#, BYTES FROM V$STANDBY_LOG;

-- ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;
-- ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/oradata/EXAMPLE/online_redo01.log') SIZE 50M;
-- ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/oradata/EXAMPLE/online_redo02.log') SIZE 50M;
-- ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/oradata/EXAMPLE/online_redo03.log') SIZE 50M;
-- ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
ALTER DATABASE DROP LOGFILE GROUP 4;
ALTER DATABASE DROP LOGFILE GROUP 5;
ALTER DATABASE DROP LOGFILE GROUP 6;
ALTER DATABASE DROP LOGFILE GROUP 7;
-- 除了以上创建online redo日志以外,我们还需要在各服务器上创建备库的redo日志,这是为了应对切换角色的情况。  
-- 创建备库的redo日志时,日志的size要比online的日志要大,而且要多一条。
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/u01/app/oracle/oradata/EXAMPLE/standby_redo01.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/u01/app/oracle/oradata/EXAMPLE/standby_redo02.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/u01/app/oracle/oradata/EXAMPLE/standby_redo03.log') SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/u01/app/oracle/oradata/EXAMPLE/standby_redo04.log') SIZE 50M;

5) 开启应用进程

两种应用方式

-- 前台redo apply. 直到取消才会返回Session
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;

-- 后台 redo apply. 一旦apply进程开启,则返回到session
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

取消应用的方式

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

设置delay或者nodelay

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DELAY 30 DISCONNECT FROM SESSION;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY DISCONNECT FROM SESSION;

一旦开启了备库上的redo log,则可以开启下面的实时应用进程

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE;

3. 测试log传输

主库上执行

ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';
SELECT sequence#, first_time, next_time FROM v$archived_log ORDER BY sequence#;
ALTER SYSTEM SWITCH LOGFILE;

备库上执行

ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';
SELECT sequence#, first_time, next_time, applied FROM v$archived_log ORDER BY sequence#;

其他

  1. 数据库redo日志信息:/u01/app/oracle/diag/rdbms/example/EXAMPLE/trace/alert_EXAMPLE.log