原文地址:http://blog.csdn.net/lk_db/article/details/51497943
测试环境说明:
主 库:Reahat 6.5 x64 + Oracle 11.2.0.3 x64 单机(IP:192.168.6.10 db) DB_UNIQUE_NAME=orcl
备库1:Reahat 6.5 x64 + Oracle 11.2.0.3 x64 单机(IP:192.168.6.101 db1) DB_UNIQUE_NAME=dglocal
备库2:Reahat 6.5 x64 + Oracle 11.2.0.3 x64 单机(IP:192.168.6.102 db2) DB_UNIQUE_NAME=dgsh
主备库归档日志传输使用路径说明:
orclßàdglocal:使用LOG_ARCHIVE_DEST_2
orclßàdgsh :使用LOG_ARCHIVE_DEST_3
1、修改主库为归档模式
SQL> shutdown immediate;
SQL> startup mount
SQL> alter database archivelog;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/archivelog/';
SQL> shutdown immediate;
SQL> STARTUP
2、设置主库force logging
SQL> alter database force logging;
Database altered.
3、修改主库数据库参数
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,dglocal,dgsh)' scope=both;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/archivelog/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl' scope=both;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=dglocal LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dglocal' scope=both;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='SERVICE=dgsh LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dgsh' scope=both;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE scope=both;
ALTER SYSTEM SET FAL_CLIENT = orcl scope=both;
ALTER SYSTEM SET FAL_SERVER = dglocal,dgsh scope=both;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT =AUTO scope=both;
4、重新启动主库
shutdown immediate
startup
5、修改备端数据库参数
备库1(dglocal)数据库执行:
ALTER SYSTEM SET DB_UNIQUE_NAME=dglocal scope=both;
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,dglocal)' scope=both;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/archivelog/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dglocal' scope=both;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=orcl LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl' scope=both;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;
ALTER SYSTEM SET FAL_CLIENT = dglocal scope=both;
ALTER SYSTEM SET FAL_SERVER = orcl scope=both;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT =AUTO scope=both;
备库2(dgsh)数据库执行:
ALTER SYSTEM SET DB_UNIQUE_NAME=dgsh scope=both;
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,dgsh)' scope=both;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/archivelog/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dgsh' scope=both;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='SERVICE=orcl LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl' scope=both;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;
ALTER SYSTEM SET FAL_CLIENT = dgsh scope=both;
ALTER SYSTEM SET FAL_SERVER = orcl scope=both;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT =AUTO scope=both;
--------------------------------------------------------------------------------------
附:
192.168.6.101(dglocal)或者192.168.6.102(dgsh)切换为主端时:
(1)在192.168.6.10(orcl)上添加for standby redo,无需修改其他参数。
(2)在192.168.6.101(dglocal)上执行如下语句,设置与192.168.6.102的日志传输通信:
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,dglocal,dgsh)' scope=both;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_4='SERVICE=dgsh LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dgsh';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_4=ENABLE;
ALTER SYSTEM SET FAL_SERVER = orcl,dgsh;
(3)在192.168.6.102(dgsh)上执行如下语句,设置与192.168.6.101的日志传输通信:
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,dgsh,dglocal)';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_4='SERVICE=dglocal LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dglocal';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_4=ENABLE;
ALTER SYSTEM SET FAL_SERVER = orcl,dglocal;
以上步骤可以在初次实施dg时设置,也可以在需要切换时设置!
注:切换后,原来的主端(orcl需要先open才能起到日志传输进程)
startup nomount
alter database mount standby database;
alter database open; --需要先open才能启动日志传输进程(实验中直接启动日志应用进程归档日志不传输--此问题待研究)
alter database recover managed standby database using current logfile disconnect from session;
------------------------------------------------------------------------------------
6、重新启动2个备库
SQL> shutdown immediate;
SQL>startup nomount;
7、分别创建2个备库的pfile
在备库中执行
SQL>create pfile='/home/oracle/pfiledglocal201605.ora' from spfile;
SQL>create pfile='/home/oracle/pfiledgsh201605.ora' from spfile;
8、备份主库
在主库中执行
[oracle@lkdb]$ rman target /
RMAN>run{
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
backup full database format='/backup/full_orcl_%U%T' include current controlfile for standby;
release channel d1;
release channel d2;
release channel d3;
}
备份主库控制文件
alter database create standby controlfile as '/backup/standby.ctl';
9、Netmanager配置主备服务器的tnsnames.ora
主库tnsnames.ora配置如下:
[oracle@lkdb admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
orcl =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.6.10)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
(SID = orcl)
)
)
dglocal =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.6.101)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dglocal)
(SID = orcl)
)
)
dgsh =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.6.102)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = DGSH) ----这里和show parameter service_name大小写一致
(SID = orcl)
)
)
备库tnsnames.ora配置和主库一致。
备库listener.ora添加配置如下:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = orcl)
)
)
10、使用DBCA删除dataguard备库
11、把主库的密码文件、控制文件、备份文件拷贝到2个备库
$scp /backup/* 192.168.6.101:/backup
$scp /backup/* 192.168.6.102:/backup
12、启动恢复备份数据库
12.1拷贝控制文件到2个备机参数文件中指定目录
cp /backup/standby.ctl /u01/app/oracle/oradata/orcl/control01.ctl
cp /backup/standby.ctl /u01/app/oracle/oradata/orcl/control02.ctl
cp /backup/standby.ctl /u01/app/oracle/oradata/orcl/control03.ctl
12.2拷贝密码文件到2个备机对应目录(务必确保主备库的sys密码一致)
$cp /backup/orapworcl1 $ORACLE_HOME/dbs/orapworcl
12.3将备机数据库启动到mount状态
$sqlplus / as sysdba
SQL>startup pfile='/home/oracle/pfiledglocal201605.ora' nomount;
SQL>create spfile from pfile='/home/oracle/pfiledglocal201605.ora';
SQL>shutdown immediate;
SQL>startup nomount;
SQL>alter database mount;
12.4在2个备机中分别执行恢复
$rman target /
RMAN>restore database;
13、备库添加for standby redo
备机1执行:
alter database add standby logfile thread 1 group 31 ('/u01/app/oracle/oradata/orcl/dgredo01.dbf') size 50M reuse;
alter database add standby logfile thread 1 group 32 ('/u01/app/oracle/oradata/orcl/dgredo02.dbf') size 50M reuse;
alter database add standby logfile thread 1 group 33 ('/u01/app/oracle/oradata/orcl/dgredo03.dbf') size 50M reuse;
alter database add standby logfile thread 1 group 34 ('/u01/app/oracle/oradata/orcl/dgredo04.dbf') size 50M reuse;
备机2执行:
alter database add standby logfile thread 1 group 35 ('/u01/app/oracle/oradata/orcl/dgredo01.dbf') size 50M reuse;
alter database add standby logfile thread 1 group 36 ('/u01/app/oracle/oradata/orcl/dgredo02.dbf') size 50M reuse;
alter database add standby logfile thread 1 group 37 ('/u01/app/oracle/oradata/orcl/dgredo03.dbf') size 50M reuse;
alter database add standby logfile thread 1 group 38 ('/u01/app/oracle/oradata/orcl/dgredo04.dbf') size 50M reuse;
14、分别启动2个dataguard备库
$sqlplus / as sysdba
SQL>shutdown immediate;
SQL>startup nomount;
SQL>alter database mount standby database;
SQL>alter database recover managed standby database using current logfile disconnect from session;
将备库启动到read only状态:
SQL>shutdown immediate;
SQL>startup nomount;
SQL>alter database mount standby database;
SQL>alter database open;
SQL>alter database recover managed standby database using current logfile disconnect from session;
15、主库(orcl)添加for standby redo(可在主备切换后添加,参考如下)
alter database add standby logfile thread 1 group 35 ('/u01/app/oracle/oradata/orcl/dgredo01.dbf') size 50M reuse;
alter database add standby logfile thread 1 group 36 ('/u01/app/oracle/oradata/orcl/dgredo02.dbf') size 50M reuse;
alter database add standby logfile thread 1 group 37 ('/u01/app/oracle/oradata/orcl/dgredo03.dbf') size 50M reuse;
alter database add standby logfile thread 1 group 38 ('/u01/app/oracle/oradata/orcl/dgredo04.dbf') size 50M reuse;
16、验证dataguard各进程状态:
主库(orcl):
SQL> !hostname
db
SQL> show parameter service
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string orcl
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
SQL> SELECT PROCESS, STATUS,THREAD#,SEQUENCE#,BLOCK#,BLOCKS, DELAY_MINS FROM V$MANAGED_STANDBY;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS DELAY_MINS
--------- ------------ ---------- ---------- ---------- ---------- ----------
ARCH CLOSING 1 121 1 399 0
ARCH CLOSING 1 119 1 63 0
ARCH OPENING 1 118 1 63 0
ARCH CLOSING 1 120 1 82 0
LNS WRITING 1 122 312 1 0
LNS WRITING 1 122 312 1 0
6 rows selected.
备库1(dglocal):
SQL> !hostnamedb1
SQL> show parameter service
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string DGLOCAL
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
SQL> SELECT PROCESS, STATUS,THREAD#,SEQUENCE#,BLOCK#,BLOCKS, DELAY_MINS FROM V$MANAGED_STANDBY;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS DELAY_MINS
--------- ------------ ---------- ---------- ---------- ---------- ----------
ARCH CLOSING 1 120 1 82 0
ARCH CONNECTED 0 0 0 0 0
ARCH CLOSING 1 118 1 1 0
ARCH CLOSING 1 121 1 399 0
MRP0 APPLYING_LOG 1 122 301 102400 0
RFS IDLE 0 0 0 0 0
RFS IDLE 1 122 301 1 0
RFS IDLE 0 0 0 0 0
8 rows selected.
备库2(dgsh):
SQL> !hostname
db2
SQL> show parameter service
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string DGSH
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
SQL> SELECT PROCESS, STATUS,THREAD#,SEQUENCE#,BLOCK#,BLOCKS, DELAY_MINS FROM V$MANAGED_STANDBY;
PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS DELAY_MINS
--------- ------------ ---------- ---------- ---------- ---------- ----------
ARCH CLOSING 1 121 1 399 0
ARCH CONNECTED 0 0 0 0 0
ARCH CONNECTED 0 0 0 0 0
ARCH CLOSING 1 120 1 82 0
MRP0 APPLYING_LOG 1 122 303 102400 0
RFS IDLE 0 0 0 0 0
RFS IDLE 1 122 303 1 0
RFS IDLE 0 0 0 0 0
RFS IDLE 0 0 0 0 0
9 rows selected.
附:主备库参数文件说明
主库(orcl):
SQL> create pfile='/home/oracle/pfilezhu.ora' from spfile;
File created.
SQL> !cat /home/oracle/pfilezhu.ora
orcl.__db_cache_size=54525952
orcl.__java_pool_size=12582912
orcl.__large_pool_size=4194304
orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=142606336
orcl.__sga_target=272629760
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=180355072
orcl.__streams_pool_size=12582912
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/fa
st_recovery_area/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4322230272
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.fal_client='ORCL'
*.fal_server='DGLOCAL','DGSH'
*.log_archive_config='DG_CONFIG=(orcl,dglocal,dgsh)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/archivelog VALID_FOR=(ALL_LOGFIL
ES,ALL_ROLES) DB_UNIQUE_NAME=orcl'
*.log_archive_dest_2='SERVICE=dglocal LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFIL
ES,PRIMARY_ROLE) DB_UNIQUE_NAME=dglocal'
*.log_archive_dest_3='SERVICE=dgsh LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,
PRIMARY_ROLE) DB_UNIQUE_NAME=dgsh'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_dest_state_3='ENABLE'
*.memory_target=414187520
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.utl_file_dir='*'
备库1(dglocal):
SQL> create pfile='/home/oracle/pfiledg1.ora' from spfile;
File created.
SQL> !cat /home/oracle/pfiledg1.ora
orcl.__db_cache_size=71303168
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=209715200
orcl.__sga_target=201326592
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=113246208
orcl.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/or
adata/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_unique_name='DGLOCAL'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.fal_client='DGLOCAL'
*.fal_server='ORCL','DGSH'
*.log_archive_config='DG_CONFIG=(orcl,dglocal,dgsh)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/archivelog/ VALID_FOR=(ALL_LOGFIL
ES,ALL_ROLES) DB_UNIQUE_NAME=dglocal'
*.log_archive_dest_2='SERVICE=orcl LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,
PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'
*.log_archive_dest_4='SERVICE=dgsh LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,
PRIMARY_ROLE) DB_UNIQUE_NAME=dgsh'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_dest_state_4='ENABLE'
*.memory_target=411041792
*.nls_language='AMERICAN'
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
备库2(dgsh):
SQL> create pfile='/home/oracle/pfiledg2.ora' from spfile;
File created.
SQL> !cat /home/oracle/pfiledg2.ora
orcl.__db_cache_size=104857600
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=184549376
orcl.__sga_target=226492416
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=104857600
orcl.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/fa
st_recovery_area/orcl/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4322230272
*.db_unique_name='DGSH'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.fal_client='DGSH'
*.fal_server='ORCL','DGLOCAL'
*.log_archive_config='DG_CONFIG=(orcl,dgsh,dglocal)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/archivelog/ VALID_FOR=(ALL_LOGFIL
ES,ALL_ROLES) DB_UNIQUE_NAME=dgsh'
*.log_archive_dest_3='SERVICE=orcl LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,
PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'
*.log_archive_dest_4='SERVICE=dglocal LGWR ASYNC AFFIRM VALID_FOR=(ONLINE_LOGFIL
ES,PRIMARY_ROLE) DB_UNIQUE_NAME=dglocal'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_dest_state_4='ENABLE'
*.memory_target=411041792
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'