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))

--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;

最后

以上就是娇气乌龟为你收集整理的Oracle 11G Physical Standby Database Active Data Guard - 安装配置的全部内容，希望文章能够帮你解决Oracle 11G Physical Standby Database Active Data Guard - 安装配置所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。