11G DATA Guard在新特性physical standby可以在apply log时，open read only,并且可以real-time apply,这样standby db 也就告别了用户老觉的机器闲置，丢掉了在发生故障时才见其价值的歧视。
standby db在高可用中不但可以用来做灾备，还可以平时用来测试、做报表统计，及logical standby的Rolling upgrade
下面配置一种DATA Guard,primary db是2nodes 11203 RAC,physical standby是11203 的single instance,Primary db有用到ASM存储文件，Single instance只用local filesystem
primary database server:
[grid@znode1 ~]$ olsnodes
znode1
znode2
sys@RAC>select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
sys@RAC>show parameter cluster
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cluster_database boolean TRUE
cluster_database_instances integer 2
cluster_interconnects string
sys@RAC>select group#,thread#,sequence#,bytes,blocksize,members from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS
---------- ---------- ---------- ---------- ---------- ----------
1 1 93 52428800 512 2
2 1 92 52428800 512 2
3 2 33 52428800 512 2
4 2 32 52428800 512 2
sys@RAC>select member,group# from v$logfile order by 2;
MEMBER GROUP#
------------------------------------------------------------ ----------
+FLRV/rac/onlinelog/group_1.260.790169423 1
+DBDG/rac/onlinelog/group_1.262.790169417 1
+FLRV/rac/onlinelog/group_2.259.790169425 2
+DBDG/rac/onlinelog/group_2.261.790169423 2
+DBDG/rac/onlinelog/group_3.258.790169683 3
+FLRV/rac/onlinelog/group_3.258.790169685 3
+DBDG/rac/onlinelog/group_4.257.790169685 4
+FLRV/rac/onlinelog/group_4.257.790169689 4
8 rows selected.
sys@RAC>select name from v$datafile;
NAME
----------------------------------------------------
+DBDG/rac/datafile/system.260.790169267
+DBDG/rac/datafile/sysaux.267.790169269
+DBDG/rac/datafile/undotbs1.263.790169271
+DBDG/rac/datafile/users.266.790169271
+DBDG/rac/datafile/undotbs2.259.790169575
sys@RAC>show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string
db_name string rac
db_unique_name string rac
global_names boolean FALSE
instance_name string rac1
service_names string rac.anbob.com
NOTE:
每个THREAD 两组日志，每组日志两个成员分别在+DBDG,+FLRV
standby database server:
sys@ORA11GR2>select * from v$version WHERE ROWNUM=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
sys@ORA11GR2>show parameter cluster
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cluster_database boolean FALSE
cluster_database_instances integer 1
cluster_interconnects string
NOTE:
Standby db版本与primary db相同，但是一个单实例
确保实例使用spfile启动，在primary db为了直观增加一个service name,不是必须的，修改service_names 参数，默认是db_unique_name + [.db_domain],这是一个动态参数不需要重启实例，可以通过
system set也可以通过srvctl add service增加，pmon process会自动注册到listener上。
sys@RAC>show parameter service
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string rac.anbob.com
sys@RAC>alter system set service_names='rac.anbob.com','prmrdb';
sys@RAC>show parameter service
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string rac.anbob.com, prmrdb
下面正式开始DATA GUARD配置
1.physical standby安装oracle software . 另外创建db所需要的目录机构如adump,oradata,这里我们用OMF结构也就是ORACLE管理方式
[oracle@dbserver1 ~]$ . 11g.env
[oracle@dbserver1 ~]$ env |grep ORACLE
ORACLE_SID=stdby
ORACLE_BASE=/u02/app/oracle
ORACLE_TERM=xterm
ORACLE_HOME=/u02/app/oracle/product/11.2.0/db_2
[oracle@dbserver1 admin]$ mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/{a,dp}dump
[oracle@dbserver1 admin]$ mkdir -p $ORACLE_BASE/fast_recovery_area/$ORACLE_SID/onlinelog
[oracle@dbserver1 admin]$ mkdir -p $ORACLE_BASE/oradata/$ORACLE_SID/datafile
[oracle@dbserver1 admin]$ mkdir -p $ORACLE_BASE/oradata/$ORACLE_SID/tempfile
[oracle@dbserver1 admin]$ mkdir -p $ORACLE_BASE/oradata/$ORACLE_SID/controlfile
2. 在standby db配置tnsnames.ora记录，这里我用的是SCAN 地址，也就是可以连接任何节点
primarytns =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac-scan.anbob.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = prmrdb.anbob.com)
)
)
3. 增加一个静态监听，在 physical standby db
cd $ORACLE_HOME/network/admin
vi listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = stdby)
(SID_NAME = stdby)
(ORACLE_HOME = /u02/app/oracle/product/11.2.0/db_2)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dbserver1)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
4. 在RAC的每个节点增加TNSNAMES.ORA记录,在primary db
[oracle@znode1 ~]$ cd $ORACLE_HOME/network/admin
[oracle@znode1 admin]$ vi tnsnames.ora
stdbytns =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.168.173)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = stdby)
)
)
5. 启动force logging 在 primary DB
sys@RAC>select force_logging from v$database;
FOR
---
NO
sys@RAC>alter database force logging;
sys@RAC>select force_logging from v$database;
FOR
---
YES
6,对于网络认证我这里没有用SSL，而是PASSWORD文件,复制password文件从primary db到standby host's ORACLE_HOME/dbs,并重命名为standby instance
[oracle@znode1 dbs]$ scp orapwrac1 192.168.168.173:/u02/app/oracle/product/11.2.0/db_2/dbs
oracle@192.168.168.173's password:
orapwrac1 100% 1536 1.5KB/s 00:00
login standby host
[oracle@dbserver1 dbs]$ mv orapwrac1 orapwstdby
7.修改 data guard 参数 to primary. 包括DG配置和 redo传输参数如SYNC,ASYNC, AFFIRM, NOAFFIRM, archive log ， datafile/logfile 文件名转换
sys@RAC>alter system set log_archive_config='dg_config=(rac,stdby)' scope=both ;
System altered.
tip:
DG_CONFIG list that includes the DB_UNIQUE_NAME of the primary database and each standby database
sys@RAC>alter system set log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=rac' scope=both;
System altered.
sys@RAC>alter system set log_archive_dest_2='service=STDBYTNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role)
db_unique_name=stdby' scope=both;
System altered.
sys@RAC>alter system set log_archive_dest_state_1='enable' scope=both;
sys@RAC>alter system set log_archive_dest_state_2='enable' scope=both;
sys@RAC>alter system set fal_server='STDBYTNS' scope=both;
sys@RAC>alter system set log_archive_max_processes=10 scope=both;
sys@RAC>alter system set db_file_name_convert='+DBDG/rac','/u02/app/oracle/oradata/stdby' scope=spfile;
sys@RAC>alter system set log_file_name_convert='+FLRV/rac','/u02/app/oracle/fast_recovery_area/stdby','+DBDG/rac','/u02/app/oracle/fast_recovery_area/stdby' scope=spfile;
sys@RAC>alter system set standby_file_management='AUTO' scope=both;
8,启用primary库rac 到archivelog mode
[oracle@znode1 oracle]$ srvctl stop database -d rac
[oracle@znode1 oracle]$ srvctl start database -d rac -o mount
idle>archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 72
Current log sequence 73
idle>alter database archivelog;
Database altered.
idle>archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 72
Next log sequence to archive 73
Current log sequence 73
9,在standby database 创建初始化参数，只写db_name
[oracle@dbserver1 dbs]$ vi initstdby.ora
*.db_name='stdby'
10，standby database 启动到nomount状态
[oracle@dbserver1 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed Aug 8 17:59:06 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
idle>startup nomount
ORACLE instance started.
Total System Global Area 167395328 bytes
Fixed Size 1343640 bytes
Variable Size 113250152 bytes
Database Buffers 50331648 bytes
Redo Buffers 2469888 bytes
idle>
11,用11g的新特性rman active duplicate 还原主库到备库，在primary host
[oracle@znode1 oracle]$ rman target / auxiliary sys/oracle@stdbytns
rman>
duplicate target database for standby from active database
spfile
parameter_value_convert 'rac','stdby','RAC','stdby'
set db_unique_name='stdby'
set db_file_name_convert='+DBDG/rac','/u02/app/oracle/oradata/stdby','+DBDG/rac/tempfile','/u02/app/oracle/oradata/stdby'
set log_file_name_convert='+FLRV/rac','/u02/app/oracle/fast_recovery_area/stdby','+DBDG/rac','/u02/app/oracle/oradata/stdby'
set control_files='/u02/app/oracle/oradata/stdby/controlfile/control01.ctl'
set log_archive_max_processes='5'
set LOG_ARCHIVE_FORMAT='log%t_%s_%r.arc'
set REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'
set fal_server='primarytns'
SET cluster_database='false'
set db_create_file_dest = '/u02/app/oracle/oradata/stdby'
set db_recovery_file_dest = '/u02/app/oracle/fast_recovery_area'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(rac,stdby)'
set log_archive_dest_2='service=PRIMARYTNS LGWR ASYNC NOAFFIRM max_failure=10 max_connections=5 reopen=180 valid_for=(online_logfiles,primary_role) db_unique_name=rac'
set log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=stdby'
set audit_file_dest = '/u02/app/oracle/admin/stdby/adump'
set diagnostic_dest= '/u02/app/oracle'
reset REMOTE_LISTENER
reset local_listener
TIP:
保证以下参数指定目录在备库存在audit_file_dest,control_files，如果没有audit_file_dest目录，备库会启动后无法登录遇ORA-09925 错误，解决方法是如下
[root@dbserver1 ~]# ps -ef|grep ora_|grep -v grep|awk '{print $2}'|xargs kill -9
[oracle@dbserver1 dbs]$ rm -rf /dev/shm/ora_stdby*
如果上面失败第二次duplicate会遇RMAN-05537，解决方法是shutdown abort standby db,rm spfile,startup nomount with pfile,re-run rman dumplicate on primary db
12,修改standby database spfile 记录，把primary instance记录删除，重建Standby spfile
sys@RAC>create pfile from spfile;
File created.
[oracle@dbserver1 dbs]$ vi initstdby.ora
rac1.__db_cache_size=209715200
rac2.__db_cache_size=209715200
stdby.__db_cache_size=339738624
stdby.__java_pool_size=4194304
rac1.__large_pool_size=4194304
rac2.__large_pool_size=4194304
stdby.__large_pool_size=4194304
rac1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
stdby.__oracle_base='/u02/app/oracle'#ORACLE_BASE set from environment
rac1.__pga_aggregate_target=293601280
rac2.__pga_aggregate_target=293601280
stdby.__pga_aggregate_target=339738624
...
note:
删除rac1和rac2开头的实例参数，可以按需要修改要修改的参数，因为从primary db传过来两个undo TBS(每个实例一个),需在standby db指定undo表空间*.undo_tablespace='UNDOTBS1'，重建spfile
sql>create spfile from pfile;
File created.
sql>startup force
13,在standby db增加standby 日志 做real-time apply,standby log 每个thread 日志组比online log 组数多一个,standby日志大小与online redo相同
primary db:
sys@RAC>select group#,thread#,bytes,members from v$log;
GROUP# THREAD# BYTES MEMBERS
---------- ---------- ---------- ----------
1 1 52428800 2
2 1 52428800 2
3 2 52428800 2
4 2 52428800 2
standby db:
alter database add standby logfile size 52428800;
增加6组(number of group per thread +1)* number of thread
14,修改standby database 到archive log mode
15，Starting Redo Apply on the physical standby database
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
USING CURRENT LOGFILE 表示接受到redo log立及应用，启用real-time apply
DISCONNECT 表示后台进程应用
有时因为primary db log_archive_dest_2 值 service=STDBYTNS LGWR ASYNC NOAFFIRM，不能保证适时传输时，需要alter system archive log current 或alter system switch logfile切日志查看
同步数据是否成功，当然也可以改log_archive_dest_2 的值。
验证日志传输与应用
SELECT RESETLOGS_ID,THREAD#,SEQUENCE#,STATUS,ARCHIVED FROM V$ARCHIVED_LOG
sys@RAC>SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=2;
RECOVERY_MODE
-----------------------
MANAGED REAL TIME APPLY
SQL>SELECT MESSAGE FROM V$DATAGUARD_STATUS;
...
LNS: Beginning to archive log 1 thread 1 sequence 117
ARCH: Completed archiving thread 1 sequence 116 (6010717-6011086)
LNS: Completed archiving log 1 thread 1 sequence 117
ARCH: Beginning to archive thread 1 sequence 117 (6011086-6011618)
LNS: Standby redo logfile selected for thread 1 sequence 118 for destination LOG_ARCHIVE_DEST_2
LNS: Beginning to archive log 2 thread 1 sequence 118
ARCH: Completed archiving thread 1 sequence 117 (6011086-6011618)
sys@RAC>SELECT name, value, datum_time, time_computed FROM V$DATAGUARD_STATS;
NAME VALUE DATUM_TIME TIME_COMPUTED
-------------------------------- ------------------------------ ------------------------------ ------------------------------
transport lag +00 00:00:00 08/13/2012 16:09:02 08/13/2012 16:09:03
apply lag +00 00:00:00 08/13/2012 16:09:02 08/13/2012 16:09:03
apply finish time +00 00:00:00.000 08/13/2012 16:09:03
estimated startup time 20 08/13/2012 16:09:03
The apply lag metric is computed using data that is periodically received from the primary database. The DATUM_TIME column contains a timestamp of when this data was last
received by the standby database. The TIME_COMPUTED column contains a timestamp taken when the apply lag metric was calculated
sql>SELECT * FROM V$STANDBY_EVENT_HISTOGRAM WHERE NAME = 'apply lag'
sys@RAC>SELECT * FROM V$STANDBY_EVENT_HISTOGRAM WHERE NAME = 'apply lag';
NAME TIME UNIT COUNT LAST_TIME_UPDATED
--------------- ---------- ---------------- ---------- --------------------
apply lag 0 seconds 2402 08/13/2012 16:12:45
apply lag 1 seconds 43 08/13/2012 16:12:40
apply lag 2 seconds 13 08/13/2012 16:02:40
apply lag 3 seconds 13 08/13/2012 16:01:08
apply lag 4 seconds 4 08/13/2012 13:16:47
...
当然可以建一张表来测试同步了,在两个节点上的事务也都可以同步到standby实例上
Stopping Redo Apply
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;