oracle 11g 生产环境dg配置全过程

环境介绍：
数据库版本： 11.2.0.4
服务器操作系统：主库 centos6
备库 centos7
操作方案
生产环境，因为涉及到重启数据库问题，时间安排在凌晨，为了减少凌晨工作量，决定操作方案：
备库服务器装好数据库软件，利用主库rman备份先进行恢复，然后白天追归档日志，凌晨之前追齐归档日志，完成参数修改，凌晨之后，重启主库，打开备库，打开日志应用，完成dg配置。http://www.cndba.cn/asker/article/2699

主库rman脚本：

export ORACLE_SID=orcl
export ORACLE_HOME=/appl/oracle/product/11.2.0.4/db_1
DATE=`date +%Y%m%d`
mkdir -p /backup_database/rmanbk/$DATE
RMAN=$ORACLE_HOME/bin/rman
RMAN_LOG_FILE=/backup_database/rmanbk/$DATE/backup_db_level0.`date +%Y%m%d%H%M%S`.log
$RMAN target /  msglog $RMAN_LOG_FILE append << EOF
run{
allocate channel cha1 type disk;
allocate channel cha2 type disk;
crosscheck archivelog all;
delete noprompt expired archivelog all;
crosscheck backup;
delete noprompt expired backup;
sql 'alter system archive log current';
backup
incremental level 0
database format '/backup_database/rmanbk/$DATE/db_level0_%d_%s_%p_%T' plus archivelog format '/backup_database/rmanbk/$DATE/arch_%d_%s_%p_%T' delete all input;
backup current controlfile format '/backup_database/rmanbk/$DATE/cntrl_%d_%I_%s_%p_%T';
report obsolete;
delete noprompt obsolete;
release channel cha1;
release channel cha2;
}
exit
EOF
echo `date` >> $RMAN_LOG_FILE

主库操作：
1 数据库强制归档开启，添加standby log

SQL> ALTER DATABASE FORCE LOGGING;
SQL>alter database set standby database to maximize availability;
SQL>alter database add standby logfile group x ('/xxxx/xxxx/xxxx/xxxx.log') size 500M;

standby log需要比主库日志组多一组http://www.cndba.cn/asker/article/2699

2 生成pfile文件

SQL> create pfile from spfile;

3 修改pfile文件

vim $ORACLE_HOME/dbs/initorcl.ora

修改log_archive_dest_1http://www.cndba.cn/asker/article/2699http://www.cndba.cn/asker/article/2699

*.log_archive_dest_1='LOCATION=/oracle/archlog/ valid_for=(all_logfiles,all_roles)'

添加以下内容http://www.cndba.cn/asker/article/2699

*.db_unique_name=orcl
*.fal_server=orcl
*.fal_client=orcldg
*.standby_file_management=auto
*.db_file_name_convert='/oracle/datafile/orcl/','/oracle/oracle/oradata/orcl/'
*.log_file_name_convert='/oracle/datafile/orcl/onlinelog/','/oracle/oracle/oradata/orcl/'
*.log_archive_config='dg_config=(orcl,orcldg)'
*.log_archive_dest_2='service=orcldg LGWR SYNC AFFIRM valid_for=(online_logfiles,primary_role) db_unique_name=orcldg'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_dest_state_1='ENABLE'

4 修改$ORACLE_HOME/network/admin/tnsname.ora

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

ORCLDG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ip2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcldg)
    )
  )

ip1为主库服务器ip,ip2为备库服务器ip

5 将主库$ORACLE_HOME/dbs和$ORACLE_HOME/network/admin下所有文件同步到备库环境相应目录。

备库：
1.根据备库自身目录环境修改参数文件

vim $ORACLE_HOME/dbs/initorcl.ora

其中和dg相关的修改完后的内容

*.db_unique_name=orcldg
*.fal_server='orcl'
*.fal_client='orcldg'
*.standby_file_management=auto
*.db_file_name_convert='/oracle/datafile/orcl/','/oracle/oracle/oradata/orcl/'
*.log_file_name_convert='/oracle/datafile/orcl/onlinelog/','/oracle/oracle/oradata/orcl/'
*.log_archive_config='dg_config=(orcl,orcldg)'
*.log_archive_dest_2='service=orcl LGWR SYNC AFFIRM valid_for=(online_logfiles,primary_role) db_unique_name=orcl'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_dest_state_1='ENABLE

主要是参数dg_unique_name和log_archive_dest_2

2 修改listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME = /oracle/oracle/product/11.2.0/db_1)
      (SID_NAME = orcl)
    )
  )

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

ADR_BASE_LISTENER = /oracle/oracle

oracle_home，host和ADR_BASE_LISTENER 根据实际情况修改

3 利用参数文件将数据库启动至nomount

SQL> startup pfile='/appl/oracle/product/11.2.0.4/db_1/dbs/initorcl.ora' nomount;

4 利用主库备份恢复控制文件

 rman target /;
 restore standby controlfile from '/xxx/xxx/xxx/cntrl_ORCL_1397393707_12984_1_20180321';
 mount database;

5 恢复备库数据库
参照http://www.cndba.cn/asker/article/317 四~七恢复过程

6 追归档日志
将主库在备份之后新生成的归档日志同步到备库的归档目录中
备库，追齐归档

RMAN> recover database until sequence 40326;

凌晨操作：
主库，利用新的参数文件重启数据库
备库，添加standby log

SQL> alter database add standby logfile;
SQL> alter database recover managed standby database cancel;
SQL> alter database open;
SQL> alter database recover managed standby database using current logfile disconnect from session;

查看主备库alert日志，检查有没有报错，主库创建测试表,插入删除数据，验证备库是不是正常同步。
配置备库归档删除脚本

export ORACLE_SID=orcl
export ORACLE_HOME=/oracle/oracle/product/11.2.0/db_1
RMAN=$ORACLE_HOME/bin/rman
RMAN_LOG_FILE=/oracle/scripts/delete_archivelog/delete_archivelog.`date +%Y%m%d%H%M%S`.log
$RMAN  target /  msglog $RMAN_LOG_FILE append << EOF
run{
crosscheck archivelog all;
delete noprompt archivelog all completed before 'sysdate - 1/24';
}
exit
EOF
echo `date` >> $RMAN_LOG_FILE

oracle dg

签到成功

CNDBA社区

oracle 11g 生产环境dg配置全过程

shmily

AI QQ群