Oracle 11G Physical Standby Database Active Data Guard - 安装配置

参考官方：Creating a Physical Standby Database
http://docs.oracle.com/cd/E11882_01/server.112/e41134/create_ps.htm#i1225703

#######################
--data guard physical standby database 即Active data guard
--主备库在同一台机器上，单机环境。
#######################

主库配置
Linux5.6x86-64 + Oracle11.2.3
归档模式
DB_NAME=DGDB
DB_UNIQUE_NAME=DGDB
ORACLE_SID=DGDB
INSTANCE_NAME=DGDB
controlfile,redolog,datafile与tempfile目录：/prod/oracle/oradata/DGDB/
归档日志目录：USE_DB_RECOVERY_FILE_DEST

关于主库data guard会涉及到2类配置参数
+ data guard正常使用参数
+ switchover需要用到的参数，主备库角色切换时才需要。

备库配置
Linux5.6x86-64 + Oracle11.2.3
DB_NAME=DGDB
DB_UNIQUE_NAME=DGDBST
ORACLE_SID=DGDBST
INSTANCE_NAME=DGDBST
controlfile,redolog,datafile与tempfile目录：/prod/oracle/oradata/DGDBST/
归档日志目录：USE_DB_RECOVERY_FILE_DEST

步骤

a,主库创建pfile,备用，或用来修改成备库需要的参数文件。
DGDB> create pfile='/home/oratest/DGDBST/initDGDBST.ora' from spfile;
File created.

b,主库强制归档
select name,log_mode,FORCE_LOGGING from v$database;
alter database force logging;

c,主库添加standby redolog,用于switch over是需要,主库时刻需要进行角色切换。
DGDB> alter database add standby logfile ('/prod/oracle/oradata/DGDB/standby_redo01.log') size 50M;
Database altered.
DGDB> alter database add standby logfile ('/prod/oracle/oradata/DGDB/standby_redo02.log') size 50M;
Database altered.
DGDB> alter database add standby logfile ('/prod/oracle/oradata/DGDB/standby_redo03.log') size 50M;
Database altered.
DGDB> alter database add standby logfile ('/prod/oracle/oradata/DGDB/standby_redo04.log') size 50M;
Database altered.

d,配置主库和备库的tnsname.ora与监听listener.ora，主库与备库的这两个文件保持相同。
如果主备库不在同一机器，则拷贝至备库。
+ listener.ora文件：在主库静态注册备库DGDBST的监听程序listener.ora，因为需要在主库连接备库进行RMAN duplicate,拷贝这个listener.ora到备库。
+ tnsnames.ora文件：修改完毕，拷贝到备库。

修改后的主库监听文件listener.ora
##############################
# Add static register standby database DGDBST listener.
SID_LIST_LISTENER01 =  
  (SID_LIST =  
    (SID_DESC =  
      (GLOBAL_DBNAME = DGDBST)  
      (ORACLE_HOME = /prod/oracle/product/11.2.0/db_1)  
      (SID_NAME = DGDBST)  
    )  
  )  
##############################

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_PROD = ON

LISTENER01 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = q1ebsdb.imc.com)(PORT = 1531))
    )
  )

ADR_BASE_LISTENER01 = /prod/oracle

修改后的主库tnsname.ora文件如下
LISTENER_DGDB =
  (ADDRESS = (PROTOCOL = TCP)(HOST = q1ebsdb.imc.com)(PORT = 1531))


DGDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = q1ebsdb.imc.com)(PORT = 1531))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DGDB)
    )
  )

#############################
# Database DB_UNIQUE_NAME = DGDBST
DGDBST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = q1ebsdb.imc.com)(PORT = 1531))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DGDBST)
    )
  )
############################

e,修改参数文件。
如下修改主库参数
alter system set db_unique_name='DGDB' scope=spfile;
alter system set log_archive_config='DG_CONFIG=(DGDB,DGDBST)';
alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DGDB';
alter system set log_archive_dest_2='SERVICE=DGDBST ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DGDBST';
--alter system set log_archive_dest_state_1=enable;  
--alter system set log_archive_dest_state_2=enable;  
--alter system set log_archive_max_processes=4;  --alter system set remote_login_passwordfile='EXCLUSIVE' scope=spfile;

#这些参数是主库切换角色时需要的。
alter system set db_file_name_convert='/prod/oracle/oradata/DGDBST','/prod/oracle/oradata/DGDB' scope=spfile;  --注意：对主库DGDB来说，DGDBST路径在前，DGDB路径在后。
alter system set log_file_name_convert='/prod/oracle/oradata/DGDBST','/prod/oracle/oradata/DGDB' scope=spfile;  
alter system set standby_file_management='AUTO';  
alter system set fal_server='DGDBST';  
alter system set fal_client='DGDB';  

f,从组库最新的spifle生成文件,并修改成为备库可用的参数文件,参照主库修改如下。
DGDB> create pfile='/home/oratest/oraDGDB.ora' from spfile;
cp /home/oratest/oraDGDB.ora /home/oratest/oraDGDBST.ora
vi /home/oratest/oraDGDBST.ora

*.audit_file_dest='/prod/oracle/admin/DGDBST/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/prod/oracle/oradata/DGDBST/control01.ctl','/prod/oracle/fast_recovery_area/DGDBST/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='/prod/oracle/oradata/DGDB','/prod/oracle/oradata/DGDBST'
*.db_name='DGDB'
*.db_recovery_file_dest='/prod/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4322230272
*.db_unique_name='DGDBST'
*.diagnostic_dest='/prod/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DGDBSTXDB)'
*.fal_client='DGDBST'
*.fal_server='DGDB'
#*.local_listener=''
*.log_archive_config='DG_CONFIG=(DGDB,DGDBST)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=DGDBST'
*.log_archive_dest_2='SERVICE=DGDB ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DGDB'
*.log_archive_format='%t_%s_%r.arch'
*.log_file_name_convert='/prod/oracle/oradata/DGDB','/prod/oracle/oradata/DGDBST'
*.open_cursors=300
*.pga_aggregate_target=134217728
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=335
*.sga_target=536870912
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

g,创建备库相关的目录
su - oratest
mkdir -p /prod/oracle/admin/DGDBST/adump
mkdir -p /prod/oracle/oradata/DGDBST
mkdir -p /prod/oracle/fast_recovery_area/DGDBST

h,拷贝密码文件到备库。
cp $ORACLE_HOME/dbs/orapwDGDB $ORACLE_HOME/dbs/orapwDGDBST --同一台机器直接复制。

i,从主库上连接RMAN duplicate，此时备库的静态监听起作用了。
q1ebsdb.imc.com | XXX | /home/oratest > . .profile_DGDB
q1ebsdb.imc.com | DGDB | /home/oratest > rman target sys/amaxgs auxiliary sys/amaxgs@DGDBST
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Sep 5 14:46:16 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DGDB (DBID=2171249938)
connected to auxiliary database: DGDB (not mounted)

RMAN> duplicate target database for standby from active database nofilenamecheck;

Starting Duplicate Db at 05-SEP-13
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=10 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/prod/oracle/product/11.2.0/db_1/dbs/orapwDGDB' auxiliary format
 '/prod/oracle/product/11.2.0/db_1/dbs/orapwDGDBST'   ;
}
executing Memory Script

Starting backup at 05-SEP-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=32 device type=DISK
Finished backup at 05-SEP-13

contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '/prod/oracle/oradata/DGDBST/control01.ctl';
   restore clone controlfile to  '/prod/oracle/fast_recovery_area/DGDBST/control02.ctl' from
 '/prod/oracle/oradata/DGDBST/control01.ctl';
}
executing Memory Script

Starting backup at 05-SEP-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/prod/oracle/product/11.2.0/db_1/dbs/snapcf_DGDB.f tag=TAG20130905T145100 RECID=2 STAMP=825346261
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 05-SEP-13

Starting restore at 05-SEP-13
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 05-SEP-13

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
   set newname for tempfile  1 to
 "/prod/oracle/oradata/DGDBST/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to
 "/prod/oracle/oradata/DGDBST/system01.dbf";
   set newname for datafile  2 to
 "/prod/oracle/oradata/DGDBST/sysaux01.dbf";
   set newname for datafile  3 to
 "/prod/oracle/oradata/DGDBST/undotbs01.dbf";
   set newname for datafile  4 to
 "/prod/oracle/oradata/DGDBST/users01.dbf";
   set newname for datafile  5 to
 "/prod/oracle/oradata/DGDBST/example01.dbf";
   backup as copy reuse
   datafile  1 auxiliary format
 "/prod/oracle/oradata/DGDBST/system01.dbf"   datafile
 2 auxiliary format
 "/prod/oracle/oradata/DGDBST/sysaux01.dbf"   datafile
 3 auxiliary format
 "/prod/oracle/oradata/DGDBST/undotbs01.dbf"   datafile
 4 auxiliary format
 "/prod/oracle/oradata/DGDBST/users01.dbf"   datafile
 5 auxiliary format
 "/prod/oracle/oradata/DGDBST/example01.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /prod/oracle/oradata/DGDBST/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 05-SEP-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/prod/oracle/oradata/DGDB/system01.dbf
output file name=/prod/oracle/oradata/DGDBST/system01.dbf tag=TAG20130905T145108
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/prod/oracle/oradata/DGDB/sysaux01.dbf
output file name=/prod/oracle/oradata/DGDBST/sysaux01.dbf tag=TAG20130905T145108
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/prod/oracle/oradata/DGDB/example01.dbf
output file name=/prod/oracle/oradata/DGDBST/example01.dbf tag=TAG20130905T145108
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/prod/oracle/oradata/DGDB/undotbs01.dbf
output file name=/prod/oracle/oradata/DGDBST/undotbs01.dbf tag=TAG20130905T145108
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/prod/oracle/oradata/DGDB/users01.dbf
output file name=/prod/oracle/oradata/DGDBST/users01.dbf tag=TAG20130905T145108
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 05-SEP-13

sql statement: alter system archive log current

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=2 STAMP=825346320 file name=/prod/oracle/oradata/DGDBST/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=3 STAMP=825346320 file name=/prod/oracle/oradata/DGDBST/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=825346320 file name=/prod/oracle/oradata/DGDBST/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=825346320 file name=/prod/oracle/oradata/DGDBST/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=6 STAMP=825346320 file name=/prod/oracle/oradata/DGDBST/example01.dbf
Finished Duplicate Db at 05-SEP-13

j,最后启动备库的redo apply进程。
select status from v$instance;
alter database open read only;
alter database recover managed standby database using current logfile disconnect from session;

k,修改备库的监听配置，这一步实际上是在备库上操作，也可以不用修改，而保持开头设置的静态注册。
在tnsnames.ora中添加下面的字串，并设置local_listener='LISTENER_DGDBST'。
LISTENER_DGDBST =
  (ADDRESS = (PROTOCOL = TCP)(HOST = q1ebsdb.imc.com)(PORT = 1531))

最后通过下面的脚本验证，并可以通过DML，DDL等操作查看主备库的同步情况。
##########
Data guard 维护脚本
##########

--data guard状态
col NAME for a20  
select NAME,DB_UNIQUE_NAME,OPEN_MODE,DATABASE_ROLE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS from v$database;  

--归档日志应用状态
set lines 200 pages 100  
col name for a100  
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';  
select sequence#,name,first_time, next_time,applied from v$archived_log order by sequence#;    
    
--v$recovery_progress查看恢复的细节,注意这个视图在mount恢复状态下是无法查询的,只有在read only状态下可以.
select to_char(start_time,'yyyy-mm-dd hh24:mi:ss') Start_Time,  
       TYPE,
       ITEM,
       UNITS,
       Total,
       to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') timestamp   
from   v$recovery_progress; 

--v$dataguard_stats查看redo传输和redo应用的延迟情况.
select name,value,unit,time_computed from v$dataguard_stats  

--v$dataguard_status查看主库，备库的进程日志
col lines 200 pages 100
col message for a120
select facility,to_char(timestamp,'dd-mon-yyyy hh24:mi') time,message from v$dataguard_status;

--v$managed_standby查看归档,MRP,RFS的进程状态
select process,pid,status,thread#,group#,sequence#,delay_mins from v$managed_standby;

完结!