记一次完整的Max(um) Availabity Data Guard config and switchover on SUSE Linux 11 sp3

一.环境

SourceDB: 11.2.0.4 Rac  2 Nodes on SUSE Linux + ASM using ASM Lib

cpxxx1/cpxxx2

Target DB: 11.2.0.4 Single Node on SUSE Linux  + Localdisk

cpxxxdg

二.Data Guard 配置过程

1.准备主库参数文件

create pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initcpxxx.ora' from spfile;

2.修改主库参数文件

cpxxx2.__db_cache_size=119453777920
cpxxx1.__db_cache_size=119453777920
cpxxx2.__java_pool_size=1073741824
cpxxx1.__java_pool_size=1073741824
cpxxx2.__large_pool_size=1342177280
cpxxx1.__large_pool_size=1342177280
cpxxx1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
cpxxx2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
cpxxx2.__pga_aggregate_target=44828721152
cpxxx1.__pga_aggregate_target=44828721152
cpxxx2.__sga_target=133949292544
cpxxx1.__sga_target=133949292544
cpxxx2.__shared_io_pool_size=0
cpxxx1.__shared_io_pool_size=0
cpxxx2.__shared_pool_size=11274289152
cpxxx1.__shared_pool_size=11274289152
cpxxx2.__streams_pool_size=0
cpxxx1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/cpxxx/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.4.0'
*.control_files='+fra/cpxxx_CONTROLFILE/control01.ctl','+data/cpxxx/controlfile/current.276.926124019'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='/u01/app/oracle/product/11.2.0/db_1/dbs'
*.db_domain=''
*.db_name='cpxxx'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=419430400000
*.dg_broker_start=FALSE
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=cpxxxXDB)'
cpxxx1.instance_number=1
cpxxx2.instance_number=2
*.open_cursors=4000
*.pga_aggregate_target=44608520192
*.processes=3000
*.remote_listener='cpxxx-scan:1521'
*.remote_login_passwordfile='exclusive'
*.sessions=1820
*.sga_target=133827657728
cpxxx2.thread=2
cpxxx1.thread=1
cpxxx2.undo_tablespace='UNDOTBS2'
cpxxx1.undo_tablespace='UNDOTBS1'
*.log_archive_config='DG_CONFIG=(cpxxx,cpxxxdg)'
*.log_archive_dest_1='LOCATION=+FRA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=cpxxx'
*.log_archive_dest_2='SERVICE=cpxxxdg SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=cpxxxdg COMPRESSION=ENABLE'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=4
*.db_file_name_convert='+DATA/cpxxx/datafile','/u01/app/oracle/oradata' 
*.log_file_name_convert='+DATA/onlinelog','/u01/app/oracle/oradata','+FRA/onlinelog/','/u01/app/oracle/fast_recovery_area'

注:由于主库是RAC，可通过如下操作更改对应参数

alter system set log_archive_config='DG_CONFIG=(cpxxx,cpxxxdg)' scope=bothsid=*;
alter system set log_archive_dest_1='LOCATION=+FRA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=cpxxx' scope=both sid=*;
alter system set log_archive_dest_2='SERVICE=cpxxxdg SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=cpxxxdg COMPRESSION=ENABLE' scope=both sid=*;
alter system set db_file_name_convert='+DATA/cpxxx/datafile','/u01/app/oracle/oradata'  scope=spfile sid=*;
alter system set log_file_name_convert='+DATA/onlinelog','/u01/app/oracle/oradata','+FRA/onlinelog/','/u01/app/oracle/fast_recovery_area' scope=spfile sid=*;--务必主库位置和备库位置成对出现

3.启用主库 forcelogging以及设置最大高可用模式,并且应用db_file_name_convert,log_file_name_convert参数

alter database force logging;

--mount状态下更改DG 保护模式

srvctl stop database -d cpxxx
srvctl start database -d cpxxx -o mount

--mount模式下更改dg 保护模式
alter database set standby database to maximize availability;
srvctl stop database -d cpxxx
srvctl start database -d cpxxx

4.修改主库的监听和tnsname.ora 文件
-- 主库监听listener.ora文件 添加 （如果有就不修改）

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = cpxxx)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = cpxxx1)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER))
  )

  主库tnsname.ora 添加备库的相关信息：

cpxxx_DG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = shadcplscv02)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = cpxxxdg)
    )
  )

5.在主库用scp把listener.ora,tnsname.ora,initcpxxx.ora,orapwcpxxx1传给备库,然后进行相应的修改

scp -r oracle@10.64.3x.xx:/u01/app/oracle/product/11.2.0/db_1/dbs/initcpxxx.ora /u01/app/oracle/product/11.2.0.4/db_1/dbs/initcpxxxdg.ora
scp -r oracle@10.64.3x.xx:/u01/app/oracle/product/11.2.0/db_1/dbs/orapwcpxxx1 /u01/app/oracle/product/11.2.0.4/db_1/dbs/orapwcpxxxdg
scp -r oracle@10.64.3x.xx:/u01/app/oracle/product/11.2.0/db_1/dbs/orapwcpxxx1  /u01/app/oracle/product/11.2.0.4/db_1/network/admin/tnsnames.ora
scp -rp oracle@10.64.3x.xx:/u01/app/11.2.0/grid/network/admin/listener.ora /u01/app/11.2.0.4/grid/network/admin/listener.ora

注：密码文件必须保证主库2个节点和备库均为同一份（rac：orapwcpxxx1，orapwcpxxx2；备库，orapwcpxxxdg）
  
6.修改备库listener.ora,initcpxxx.ora

*.audit_file_dest='/u01/app/oracle/admin/cpxxx/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/fast_recovery_area/control01.ctl','/u01/app/oracle/oradata/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata'
*.db_create_online_log_dest_1='/u01/app/oracle/oradata'
*.db_domain=''
*.db_name='cpxxx'
*.db_unique_name='cpxxxdg'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=419430400000
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=cpxxxXDB)'
*.open_cursors=4000
*.pga_aggregate_target=44608520192
*.processes=3000
*.remote_login_passwordfile='exclusive'
*.sessions=1820
*.sga_target=133827657728
cpxxx2.thread=2
cpxxx1.thread=1
cpxxx2.undo_tablespace='UNDOTBS2'
cpxxx1.undo_tablespace='UNDOTBS1'
*.log_archive_config='DG_CONFIG=(cpxxxdg,cpxxx)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/fast_recovery_area VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=cpxxxdg'
*.log_archive_dest_2='SERVICE=cpxxx SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=cpxxx COMPRESSION=ENABLE'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.db_file_name_convert='+DATA/cpxxx/datafile','/u01/app/oracle/oradata' 
*.log_file_name_convert='+DATA/onlinelog','/u01/app/oracle/oradata','+FRA/onlinelog/','/u01/app/oracle/fast_recovery_area'

  
7.配置主库的STANDBY重做日志 
#Thread 1

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 101 ('+DATA/onlinelog/sbredo05_01.log','+FRA/onlinelog/sbredo05_02.log') SIZE 1000M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 102 ('+DATA/onlinelog/sbredo06_01.log','+FRA/onlinelog/sbredo06_02.log') SIZE 1000M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 103 ('+DATA/onlinelog/sbredo07_01.log','+FRA/onlinelog/sbredo07_02.log') SIZE 1000M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 104 ('+DATA/onlinelog/sbredo09_01.log','+FRA/onlinelog/sbredo09_02.log') SIZE 1000M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 105 ('+DATA/onlinelog/sbredo10_01.log','+FRA/onlinelog/sbredo10_02.log') SIZE 1000M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 106 ('+DATA/onlinelog/sbredo11_01.log','+FRA/onlinelog/sbredo11_02.log') SIZE 1000M;

#Thread 2

ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 107 ('+DATA/onlinelog/sbredo12_01.log','+FRA/onlinelog/sbredo12_02.log') SIZE 1000M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 108 ('+DATA/onlinelog/sbredo13_01.log','+FRA/onlinelog/sbredo13_02.log') SIZE 1000M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 109 ('+DATA/onlinelog/sbredo14_01.log','+FRA/onlinelog/sbredo14_02.log') SIZE 1000M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 110 ('+DATA/onlinelog/sbredo15_01.log','+FRA/onlinelog/sbredo15_02.log') SIZE 1000M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 111 ('+DATA/onlinelog/sbredo16_01.log','+FRA/onlinelog/sbredo16_02.log') SIZE 1000M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 112 ('+DATA/onlinelog/sbredo17_01.log','+FRA/onlinelog/sbredo17_02.log') SIZE 1000M;

   
  
8.备份及拷贝主库备份集到备库
--准备备控制文件 on primary databae

alter database create standby controlfile as '/ora_backup/controlfile_std.ctl';

备份主库数据库

rman target / nocatalog log /home/oracle/backup_pri_database.log<<EOF
run
{allocate channel c1 type disk maxpiecesize 10G;
allocate channel c2 type disk maxpiecesize 10G;
allocate channel c3 type disk maxpiecesize 10G;
backup as compressed backupset maxsetsize 20G database format
 '/ora_backup/Bakup_%T_%n_%U';
sql 'alter system archive log current';
sql 'alter system archive log current';
sql 'alter system archive log current';
sql 'alter system archive log current';
sql 'alter system archive log current';
sql 'alter system archive log current';
sql 'alter system archive log current';
sql 'alter system archive log current';
sql 'alter system archive log current';
crosscheck archivelog all;
--注意:可以不备份归档日志
backup as compressed backupset maxsetsize 20G archivelog all format
 '/ora_backup/arch_%d_%T_%s_%p';
}
exit;
EOF

传输备控制文件及数据库备份集到standby database
scp -rp /ora_backup/*10.64.3x.xx@oracle 10.64.3x.xx:/u01/backup/

9..备库还原和恢复备份

startup nomount pfile='/u01/app/oracle/product/11.2.0.4/db_1/dbs/initcpxxxdg.ora';
rman target /
restore standby controlfile from '/u01/backup/controlfile_std.ctl';
alter database mount;
catalog start with '/u01/backup';
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
allocate channel c5 type disk;
allocate channel c6 type disk;
restore database;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
release channel c6;
}

如果第一次备份后，依然有大量的业务数据变化，建议再做一次增量备份
10.主库做增量备份(基于SCN)并且在备库上恢复

查询备库当前scn  

SQL> SELECT CURRENT_SCN FROM V$DATABASE;
CURRENT_SCN
-----------
  982165757

 
Note：从备库检查已还原后的datafile是否有 scn不一致的情况，找出最小scn的datafile

SELECT file#,checkpoint_change# FROM v$datafile_header where rownum<100 ORDER BY 2 ;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         6          965864819
        98          965864819
        13          965864819

在主库以备库最大scn号对主库进行rman增量备份  

run{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
allocate channel c5 type disk;
allocate channel c6 type disk;
allocate channel c7 type disk;
allocate channel c8 type disk;
allocate channel c9 type disk;
allocate channel c10 type disk;
backup as compressed backupset section size 20G incremental from scn 965864819 database format '/ora_backup/db_incre%U';
release channel c1;
release channel c2;
release channel c3;
release channel c4;
release channel c5;
release channel c6;
release channel c7;
release channel c8;
release channel c9;
release channel c10;
}

--恢复备库

recover database noredo;

11.配置备库的STANDBY重做日志 
#Thread 1

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 101 ('/u01/app/oracle/oradata/sbredo05_01.log','/u01/app/oracle/fast_recovery_area/sbredo05_02.log') SIZE 1000M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 102 ('/u01/app/oracle/oradata/sbredo06_01.log','/u01/app/oracle/fast_recovery_area/sbredo06_02.log') SIZE 1000M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 103 ('/u01/app/oracle/oradata/sbredo07_01.log','/u01/app/oracle/fast_recovery_area/sbredo07_02.log') SIZE 1000M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 104 ('/u01/app/oracle/oradata/sbredo09_01.log','/u01/app/oracle/fast_recovery_area/sbredo09_02.log') SIZE 1000M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 105 ('/u01/app/oracle/oradata/sbredo10_01.log','/u01/app/oracle/fast_recovery_area/sbredo10_02.log') SIZE 1000M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 106 ('/u01/app/oracle/oradata/sbredo11_01.log','/u01/app/oracle/fast_recovery_area/sbredo11_02.log') SIZE 1000M;

                                                                                                 
#Thread 2                                                                                 

ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 107 ('/u01/app/oracle/oradata/sbredo12_01.log','/u01/app/oracle/fast_recovery_area/sbredo12_02.log') SIZE 1000M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 108 ('/u01/app/oracle/oradata/sbredo13_01.log','/u01/app/oracle/fast_recovery_area/sbredo13_02.log') SIZE 1000M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 109 ('/u01/app/oracle/oradata/sbredo14_01.log','/u01/app/oracle/fast_recovery_area/sbredo14_02.log') SIZE 1000M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 110 ('/u01/app/oracle/oradata/sbredo15_01.log','/u01/app/oracle/fast_recovery_area/sbredo15_02.log') SIZE 1000M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 111 ('/u01/app/oracle/oradata/sbredo16_01.log','/u01/app/oracle/fast_recovery_area/sbredo16_02.log') SIZE 1000M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 112 ('/u01/app/oracle/oradata/sbredo17_01.log','/u01/app/oracle/fast_recovery_area/sbredo17_02.log') SIZE 1000M;

--手动清理备库在线日志。注，如果采取Real Time Apply模式，在开启应用命令后，同样会自动执行如下操作：

alter database clear logfile group 5;
alter database clear logfile group 6;
alter database clear logfile group 7;
alter database clear logfile group 9;
alter database clear logfile group 10;
alter database clear logfile group 11;
alter database clear logfile group 12;
alter database clear logfile group 13;
alter database clear logfile group 14;
alter database clear logfile group 15;
alter database clear logfile group 16;
alter database clear logfile group 17;

12.启动DG日志应用
DG端运行:

alter database recover managed standby database disconnect from session;

三.准备主备库切换
--备库准备工作，取消DG日志应用

alter database recover managed standby database cancel;主库:

3.1 主库切换检查及步骤

[oracle@db_home1]lsnrctl start

SQL>sitchover_status,open_mode from v$database;

注：如返回结果为session_active 则使用如下命令进行中断活动并提交切换：

如alter dtabase commit to switchover to physical standby with session shutdown;

SQL>shutdown immediate;
SQL>startup nomount;
SQL>alter database mount standby database;
SQL>alter database recover managed standby database disconnect from session;

3.2 备库:

SQL>select switchover_status,open_mode from v$database;

注：如返回结果为not allow也并非100%表示无法切换，只要确保主备端dg参数设置正确，可正常操作：
SQL>alter database commit to switchover to primary with session shutdown;

SQL>shutdown immediate;
SQL>startup;

[oracle@xx]lsnrctl start

3.3 配置主备库配置以应用归档删除策略（2端都需要执行）:

CONRCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

3.4 查询备库归档日志是否被正常应用

select SEQUENCE#,APPLIED from v$archived_log where applied='YES' order by SEQUENCE#