最簡單的11gActiveDataGuard(ADG)搭建配置過程（項目步
最簡單的11g Active DataGuard(ADG)搭建配置過程（項目步驟）
一、環境介紹: 我在db01和db02兩台Linux虛擬機上首先分別安裝了一套數據庫軟件，在db01主機上創建了名為woo的數據庫；我們這次的實驗是要搭建了一套Oracle 11g Active DataGuard；目的是為了實現數據庫同步的功能，並且了解Oracle 11g DG的基本功能。
db01:192.168.1.50
二、11g ADG部署：
1、pri端和sty端配置靜態監聽
[oracle@sty admin]$ cat listener.ora
# listener.ora Network Configuration File: /DBSoft/oracle/product/11.2.4/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /DBSoft/oracle/product/11.2.4/dbhome_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = Woo )
(ORACLE_HOME = /DBSoft/oracle/product/11.2.4/dbhome_1)
)
)
[oracle@sty admin]$cat tnsname.ora
# tnsnames.ora Network Configuration File: /DBSoft/oracle/product/11.2.4/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
STY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.51)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = woo)
)
)
PRI =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.50)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = woo)
)
)2、修改primary端初始化參數文件
startup mount;
alter database archivelog;
alter database force logging;
alter database open;
alter system set log_archive_config = 'DG_CONFIG=(pri,sty)' scope=spfile;
alter system set log_archive_dest_1 = 'LOCATION=/DBBackup/Archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=pri' scope=spfile;
alter system set log_archive_dest_2 = 'SERVICE=sty LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sty' scope=spfile;
alter system set log_archive_dest_state_1 = ENABLE;
alter system set log_archive_dest_state_2 = ENABLE;
alter system set fal_server=sty scope=spfile;
alter system set fal_client=pri scope=spfile;
alter system set standby_file_management=AUTO scope=spfile;
3、在primary端pfile參數文件和密碼文件，並且拷貝到standby段相應位置
SQL> create pfile from spfile;
File created.
[oracle@db01 dbs]$ scp initwoo.ora orapwwoo db02:/DBSoft/oracle/product/11.2.4/dbhome_1/dbs
[email protected]'s password:
initwoo.ora 100% 1260 1.2KB/s 00:00
orapwwoo 100% 1536 1.5KB/s 00:00
[oracle@db01 oracle]$ scp -r admin/ diag/ fast_recovery_area/ oradata/ 192.168.1.51:$ORACLE_BASE
[email protected]'s password:
init.ora.512201522543 100% 1778 1.7KB/s 00:01
dp.log 100% 116 0.1KB/s 00:00
........
4、修改standby端的監聽文件及初始化參數文件
--修改監聽文件
[oracle@db02 ~]$ cd $ORACLE_HOME/network/admin
[oracle@db02 admin]$ vi listener.ora
# listener.ora Network Configuration File: /DBSoft/oracle/product/11.2.4/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = db02)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /DBSoft/oracle/product/11.2.4/dbhome_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = woo)
(ORACLE_HOME = /DBSoft/oracle/product/11.2.4/dbhome_1)
(SID_NAME = woo)
)
)
ADR_BASE_LISTENER = /DBSoft/oracle
--啟動監聽
[oracle@db02 dbs]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 17-JUN-2015 21:29:57
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Starting /DBSoft/oracle/product/11.2.4/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.4.0 - Production
System parameter file is /DBSoft/oracle/product/11.2.4/dbhome_1/network/admin/listener.ora
Log messages written to /DBSoft/oracle/diag/tnslsnr/db02/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db02)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 17-JUN-2015 21:29:57
Uptime 0 days 0 hr. 0 min. 1 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /DBSoft/oracle/product/11.2.4/dbhome_1/network/admin/listener.ora
Listener Log File /DBSoft/oracle/diag/tnslsnr/db02/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db02)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "woo" has 1 instance(s).
Instance "woo", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
--查看監聽狀態
[oracle@db02 dbs]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 17-JUN-2015 21:30:02
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 17-JUN-2015 21:29:57
Uptime 0 days 0 hr. 0 min. 4 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /DBSoft/oracle/product/11.2.4/dbhome_1/network/admin/listener.ora
Listener Log File /DBSoft/oracle/diag/tnslsnr/db02/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db02)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "woo" has 1 instance(s).
Instance "woo", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@db02 dbs]$
--修改參數文件
[oracle@db02 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Jun 17 21:35:54 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 1188511744 bytes
Fixed Size 1364228 bytes
Variable Size 754978556 bytes
Database Buffers 419430400 bytes
Redo Buffers 12738560 bytes
SQL> create spfile from pfile='/DBSoft/oracle/product/11.2.4/dbhome_1/dbs/initwoo.ora';
File created.
SQL>
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 1188511744 bytes
Fixed Size 1364228 bytes
Variable Size 754978556 bytes
Database Buffers 419430400 bytes
Redo Buffers 12738560 bytes
SQL>
alter system set db_unique_name=sty scope=spfile;
alter system set log_archive_config='DG_CONFIG=(pri,dg)' scope=spfile;
alter system set log_archive_dest_1 = 'LOCATION=/DBBackup/Archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=sty' scope=spfile;
alter system set log_archive_dest_2 = 'SERVICE=pri LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pri' scope=spfile;
alter system set fal_server=pri scope=spfile;
alter system set fal_client=sty scope=spfile;
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1188511744 bytes
Fixed Size 1364228 bytes
Variable Size 754978556 bytes
Database Buffers 419430400 bytes
Redo Buffers 12738560 bytes
SQL>
SQL>5、在primary端通過Rman Duplicate創建備庫,在db01上執行如下命令
rman target sys/oracle@pri auxiliary sys/oracle@sty nocatalog
[oracle@db01 ~]$ rman target sys/oracle@pri auxiliary sys/oracle@sty nocatalog
Recovery Manager: Release 11.2.0.4.0 - Production on Wed Jun 17 22:33:54 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: WOO (DBID=4221729487)
using target database control file instead of recovery catalog
connected to auxiliary database: WOO (not mounted)
RMAN>
RMAN> duplicate target database for standby from active database nofilenamecheck;
Starting Duplicate Db at 17-JUN-15
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK
contents of Memory Script:
{
backup as copy reuse
targetfile '/DBSoft/oracle/product/11.2.4/dbhome_1/dbs/orapwwoo' auxiliary format
'/DBSoft/oracle/product/11.2.4/dbhome_1/dbs/orapwwoo' ;
}
executing Memory Script
Starting backup at 17-JUN-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=44 device type=DISK
Finished backup at 17-JUN-15
contents of Memory Script:
{
backup as copy current controlfile for standby auxiliary format '/DBSoft/oracle/oradata/woo/control01.ctl';
restore clone controlfile to '/DBSoft/oracle/fast_recovery_area/woo/control02.ctl' from
'/DBSoft/oracle/oradata/woo/control01.ctl';
}
executing Memory Script
Starting backup at 17-JUN-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/DBSoft/oracle/product/11.2.4/dbhome_1/dbs/snapcf_woo.f tag=TAG20150617T223502 RECID=1 STAMP=882657308
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
Finished backup at 17-JUN-15
Starting restore at 17-JUN-15
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 17-JUN-15
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
"/DBSoft/oracle/oradata/woo/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/DBSoft/oracle/oradata/woo/system01.dbf";
set newname for datafile 2 to
"/DBSoft/oracle/oradata/woo/sysaux01.dbf";
set newname for datafile 3 to
"/DBSoft/oracle/oradata/woo/undotbs01.dbf";
set newname for datafile 4 to
"/DBSoft/oracle/oradata/woo/users01.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/DBSoft/oracle/oradata/woo/system01.dbf" datafile
2 auxiliary format
"/DBSoft/oracle/oradata/woo/sysaux01.dbf" datafile
3 auxiliary format
"/DBSoft/oracle/oradata/woo/undotbs01.dbf" datafile
4 auxiliary format
"/DBSoft/oracle/oradata/woo/users01.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /DBSoft/oracle/oradata/woo/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 17-JUN-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/DBSoft/oracle/oradata/woo/system01.dbf
output file name=/DBSoft/oracle/oradata/woo/system01.dbf tag=TAG20150617T223532
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:05:58
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/DBSoft/oracle/oradata/woo/sysaux01.dbf
output file name=/DBSoft/oracle/oradata/woo/sysaux01.dbf tag=TAG20150617T223532
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:05:30
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/DBSoft/oracle/oradata/woo/undotbs01.dbf
output file name=/DBSoft/oracle/oradata/woo/undotbs01.dbf tag=TAG20150617T223532
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/DBSoft/oracle/oradata/woo/users01.dbf
output file name=/DBSoft/oracle/oradata/woo/users01.dbf tag=TAG20150617T223532
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 17-JUN-15
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=1 STAMP=882658052 file name=/DBSoft/oracle/oradata/woo/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=882658052 file name=/DBSoft/oracle/oradata/woo/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=882658052 file name=/DBSoft/oracle/oradata/woo/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=882658052 file name=/DBSoft/oracle/oradata/woo/users01.dbf
Finished Duplicate Db at 17-JUN-15
#至此已經恢復完成6、在primary 和standby端添加standby日志
SQL> alter database add standby logfile
group 4 ('/DBSoft/oracle/oradata/woo/styredo04.log') size 50m,
group 5 ('/DBSoft/oracle/oradata/woo/styredo05.log') size 50m,
group 6 ('/DBSoft/oracle/oradata/woo/styredo06.log') size 50m,
group 7 ('/DBSoft/oracle/oradata/woo/styredo07.log') size 50m;
SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
GROUP# THREAD# SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------
4 0 0 YES UNASSIGNED
5 0 0 YES UNASSIGNED
6 0 0 YES UNASSIGNED
7 0 0 YES UNASSIGNED7、在standby端開啟實時日志應用
SQL> recover managed standby database using current logfile disconnect from session;
Media recovery complete.
SQL>
三、開始測試ADG
8、執行日志切換測試（在pri端切換歸檔，在節點二上檢查是否也發生了切換）
--primary執行日志切換
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /DBBackup/Archive
Oldest online log sequence 21
Next log sequence to archive 23
Current log sequence 23
SQL> alter system switch logfile;
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /DBBackup/Archive
Oldest online log sequence 22
Next log sequence to archive 23
Current log sequence 24
#standby查看日志的sequence號也跟著變了
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /DBBackup/Archive
Oldest online log sequence 22
Next log sequence to archive 0
Current log sequence 23
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /DBBackup/Archive
Oldest online log sequence 22
Next log sequence to archive 0
Current log sequence 24
SQL>
9、查看standby啟動的DG進程
SQL> select process,client_process,sequence#,status from v$managed_standby;
PROCESS CLIENT_P SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH ARCH 23 CLOSING
ARCH ARCH 0 CONNECTED //歸檔進程
ARCH ARCH 21 CLOSING
ARCH ARCH 0 CONNECTED
RFS ARCH 0 IDLE
RFS UNKNOWN 0 IDLE
RFS LGWR 24 IDLE //歸檔傳輸進程
RFS UNKNOWN 0 IDLE
MRP0 N/A 24 APPLYING_LOG //日志應用進程
9 rows selected.
10、查看數據庫的保護模式：
#primary 端查看，我們可以看到數據庫的保護模式為最大性能
SQL> select database_role,protection_mode,protection_level,open_mode from v$database;
DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL OPEN_MODE
---------------- -------------------- -------------------- --------------------
PRIMARY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE READ WRITE
#standby 端查看，也是一樣的。
SQL> select database_role,protection_mode,protection_level,open_mode from v$database;
DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL OPEN_MODE
---------------- -------------------- -------------------- --------------------
PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE MOUNTED
11、查看DG的日志信息
SQL> select * from v$dataguard_status;
FACILITY SEVERITY DEST_ID MESSAGE_NUM ERROR_CODE CAL TIMESTAMP MESSAGE
------------------------ ------------- ---------- ----------- ---------- --- --------- --------------------------------------------------------------------------------
Log Transport Services Informational 0 1 0 NO 17-JUN-15 ARC0: Archival started
Log Transport Services Informational 0 2 0 NO 17-JUN-15 ARC1: Archival started
Log Transport Services Informational 0 3 0 NO 17-JUN-15 ARC2: Archival started
Log Transport Services Informational 0 4 0 NO 17-JUN-15 ARC2: Becoming the 'no FAL' ARCH
Log Transport Services Informational 0 5 0 NO 17-JUN-15 ARC2: Becoming the 'no SRL' ARCH
Log Transport Services Informational 0 6 0 NO 17-JUN-15 ARC1: Becoming the heartbeat ARCH
Log Transport Services Control 0 7 0 YES 17-JUN-15 ARC2: Beginning to archive thread 1 sequence 15 (882580-883734)
Log Transport Services Informational 0 8 0 NO 17-JUN-15 ARC3: Archival started
Log Transport Services Control 0 9 0 YES 17-JUN-15 ARC2: Completed archiving thread 1 sequence 15 (882580-883734)
Log Transport Services Control 0 10 0 YES 17-JUN-15 LGWR: Beginning to archive log 2 thread 1 sequence 17
Log Transport Services Control 0 11 0 YES 17-JUN-15 ARC2: Beginning to archive thread 1 sequence 16 (883734-883788)
Log Transport Services Control 0 12 0 YES 17-JUN-15 ARC2: Completed archiving thread 1 sequence 16 (883734-883788)
Log Transport Services Warning 2 13 3113 NO 17-JUN-15 LGWR: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (3113)
Log Transport Services Warning 2 14 3113 NO 17-JUN-15 LGWR: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
Log Transport Services Error 2 15 3113 YES 17-JUN-15 Error 3113 for archive log file 2 to 'sty'
Log Transport Services Error 2 16 1041 YES 17-JUN-15 LGWR: Error 1041 disconnecting from destination LOG_ARCHIVE_DEST_2 standby host 'sty'
Log Transport Services Warning 2 17 3113 NO 17-JUN-15 ARC1: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (3113)
Log Transport Services Error 0 18 1034 YES 17-JUN-15 Error 1034 received logging on to the standby
Log Transport Services Error 2 19 1034 YES 17-JUN-15 ARC1: Error 1034 attaching to RFS for reconnect
Log Transport Services Error 2 20 3113 YES 17-JUN-15 PING[ARC1]: Error 3113 when pinging standby sty.
Log Transport Services Error 2 21 16058 YES 17-JUN-15 PING[ARC1]: Heartbeat failed to connect to standby 'sty'. Error is 16058.
Log Transport Services Warning 0 22 3113 NO 17-JUN-15 LGWR: Failed to archive log 2 thread 1 sequence 17 (3113)
Log Transport Services Control 0 23 0 YES 17-JUN-15 ARC2: Beginning to archive thread 1 sequence 17 (883788-884545)
Log Transport Services Control 0 24 0 YES 17-JUN-15 ARC2: Completed archiving thread 1 sequence 17 (883788-884545)
Log Transport Services Control 0 25 0 YES 17-JUN-15 ARCH: Beginning to archive thread 1 sequence 18 (884545-884856)
Log Transport Services Control 0 26 0 YES 17-JUN-15 ARCH: Completed archiving thread 1 sequence 18 (884545-884856)
Log Transport Services Control 0 27 0 YES 17-JUN-15 LGWR: Beginning to archive log 2 thread 1 sequence 20
Log Transport Services Control 0 28 0 YES 17-JUN-15 ARC2: Beginning to archive thread 1 sequence 19 (884856-884904)
Log Transport Services Control 0 29 0 YES 17-JUN-15 ARC2: Completed archiving thread 1 sequence 19 (884856-884904)
Log Transport Services Informational 2 30 0 NO 17-JUN-15 ARC0: Archive log rejected (thread 1 sequence 19) at host 'sty'
Log Transport Services Informational 0 31 0 NO 17-JUN-15 ARC4: Archival started
Log Transport Services Informational 0 32 0 NO 17-JUN-15 ARCH shutting down
Log Transport Services Informational 0 33 0 NO 17-JUN-15 ARC4: Archival stopped
Log Transport Services Control 0 34 0 YES 17-JUN-15 LGWR: Completed archiving log 2 thread 1 sequence 20
Log Transport Services Warning 1 35 0 NO 17-JUN-15 LGWR: Standby redo logfile selected for thread 1 sequence 21 for destination LOG _ARCHIVE_DEST_2
Log Transport Services Control 0 36 0 YES 17-JUN-15 LGWR: Beginning to archive log 3 thread 1 sequence 21
Log Transport Services Control 0 37 0 YES 17-JUN-15 ARC0: Beginning to archive thread 1 sequence 20 (884904-885855)
Log Transport Services Control 0 38 0 YES 17-JUN-15 ARC0: Completed archiving thread 1 sequence 20 (884904-885855)
Log Transport Services Control 0 39 0 YES 17-JUN-15 LGWR: Completed archiving log 3 thread 1 sequence 21
Log Transport Services Warning 1 40 0 NO 17-JUN-15 LGWR: Standby redo logfile selected for thread 1 sequence 22 for destination LOG _ARCHIVE_DEST_2
Log Transport Services Control 0 41 0 YES 17-JUN-15 LGWR: Beginning to archive log 1 thread 1 sequence 22
Log Transport Services Control 0 42 0 YES 17-JUN-15 ARC2: Beginning to archive thread 1 sequence 21 (885855-886131)
Log Transport Services Control 0 43 0 YES 17-JUN-15 ARC2: Completed archiving thread 1 sequence 21 (885855-886131)
Log Transport Services Control 0 44 0 YES 18-JUN-15 LGWR: Completed archiving log 1 thread 1 sequence 22
Log Transport Services Warning 1 45 0 NO 18-JUN-15 LGWR: Standby redo logfile selected for thread 1 sequence 23 for destination LOG _ARCHIVE_DEST_2
Log Transport Services Control 0 46 0 YES 18-JUN-15 LGWR: Beginning to archive log 2 thread 1 sequence 23
Log Transport Services Control 0 47 0 YES 18-JUN-15 ARC3: Beginning to archive thread 1 sequence 22 (886131-927736)
Log Transport Services Control 0 48 0 YES 18-JUN-15 ARC3: Completed archiving thread 1 sequence 22 (886131-927736)
Log Transport Services Control 0 49 0 YES 18-JUN-15 LGWR: Completed archiving log 2 thread 1 sequence 23
Log Transport Services Warning 1 50 0 NO 18-JUN-15 LGWR: Standby redo logfile selected for thread 1 sequence 24 for destination LOG _ARCHIVE_DEST_2
Log Transport Services Control 0 51 0 YES 18-JUN-15 LGWR: Beginning to archive log 3 thread 1 sequence 24
Log Transport Services Control 0 52 0 YES 18-JUN-15 ARC0: Beginning to archive thread 1 sequence 23 (927736-961263)
Log Transport Services Control 0 53 0 YES 18-JUN-15 ARC0: Completed archiving thread 1 sequence 23 (927736-961263)
53 rows selected.
12、Open Read Only standby數據庫並且開啟實時日志應用
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1188511744 bytes
Fixed Size 1364228 bytes
Variable Size 754978556 bytes
Database Buffers 419430400 bytes
Redo Buffers 12738560 bytes
Database mounted.
Database opened.
SQL> select database_role,protection_mode,protection_level,open_mode from v$database;
DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL OPEN_MODE
---------------- -------------------- -------------------- --------------------
PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE READ ONLY
SQL> select process,client_process,sequence#,status from v$managed_standby;
PROCESS CLIENT_P SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 26 CLOSING
RFS ARCH 0 IDLE
RFS UNKNOWN 0 IDLE
RFS LGWR 27 IDLE
7 rows selected.
SQL> recover managed standby database using current logfile disconnect from session;
Media recovery complete.
SQL> select process,client_process,sequence#,status from v$managed_standby;
PROCESS CLIENT_P SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 0 CONNECTED
ARCH ARCH 26 CLOSING
RFS ARCH 0 IDLE
RFS UNKNOWN 0 IDLE
RFS LGWR 27 IDLE
MRP0 N/A 27 APPLYING_LOG
8 rows selected.
13、解鎖scott用戶，添加數據，驗證數據是否能同步：
#在primary端創建解鎖scott用戶並創建測試表，插入10000行數據
#primary 端操作如下內容
SQL> set line 200
SQL> select username,default_tablespace,account_status from dba_users where username='SCOTT';
USERNAME DEFAULT_TABLESPACE ACCOUNT_STATUS
------------------------------ ------------------------------ --------------------------------
SCOTT USERS EXPIRED & LOCKED
SQL> alter user scott account unlock;
User altered.
SQL> conn scott/tiger;
ERROR:
ORA-28001: the password has expired
Changing password for scott
New password:
Retype new password:
Password changed
Connected.
SQL> show user
USER is "SCOTT"
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
SQL>
SQL> create table test001 (id number(10),name varchar2(20));
Table created.
SQL> begin
2 for i in 1..10000 loop
3 insert into test001 values (1,'ww');
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
#standby端查詢scott用戶是否解鎖，以及test001表是否創建並且插入了10000行數據：
SQL> conn scott/tiger;
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
TEST001 TABLE
SQL> select count(*) from test001;
COUNT(*)
----------
10000
SQL> #至此Oracle 11g ADG就已經配置完成了
四、ADG三種模式切換及介紹
14、#ADG有三種(PROTECTION|AVAILABILITY|PERFORMANCE)模式，具體參考:探索Oracle11gR2 之 DataGuard_03 三種保護模式
--primary操作步驟也就是命令之差：
SQL> select database_role,protection_mode,protection_level from v$database; ----當前為最大性能
DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
---------------- -------------------- --------------------
PRIMARY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
SQL>
SQL> alter database set standby database to maximize availability; ----切換為最大可用
Database altered.
SQL> select database_role,protection_mode,protection_level from v$database;
DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
---------------- -------------------- --------------------
PRIMARY MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY
SQL> alter database set standby database to maximize protection; ----切換為最大保護
Database altered.
SQL> select database_role,protection_mode,protection_level from v$database;
DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
---------------- -------------------- --------------------
PRIMARY MAXIMUM PROTECTION MAXIMUM PROTECTION
SQL>
--#standby端切換到最大保護是需要重啟數據庫到mount模式的:
SQL> select database_role,protection_mode,protection_level from v$database; ----當前為最大性能
DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
---------------- -------------------- --------------------
PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE
SQL> alter database set standby database to maximize availability; ----切換為最大可用
Database altered.
SQL> select database_role,protection_mode,protection_level from v$database;
DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
---------------- -------------------- --------------------
PHYSICAL STANDBY MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY
SQL> alter database set standby database to maximize protection; ----切換為最大保護模式報錯，需要將standby端啟動到mount狀態切換.
alter database set standby database to maximize protection
*
ERROR at line 1:
ORA-01126: database must be mounted in this instance and not open in any instance
SQL> shutdown immediate ----將數據庫啟動到mount狀態
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1188511744 bytes
Fixed Size 1364228 bytes
Variable Size 754978556 bytes
Database Buffers 419430400 bytes
Redo Buffers 12738560 bytes
Database mounted.
SQL> alter database set standby database to maximize protection; ----再次切換為最大可用，成功。
Database altered.
SQL> recover managed standby database using current logfile disconnect from session;
Media recovery complete.
SQL> select database_role,protection_mode,protection_level from v$database;
DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
---------------- -------------------- --------------------
PHYSICAL STANDBY MAXIMUM PROTECTION MAXIMUM PROTECTION
五、切換測試
15、ADG做(switchover)切換測試
--primary 做如下操作
SQL> alter database commit to switchover to physical standby;
Database altered.
SQL> shutdown immediate
ORA-01012: not logged on
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 1188511744 bytes
Fixed Size 1364228 bytes
Variable Size 754978556 bytes
Database Buffers 419430400 bytes
Redo Buffers 12738560 bytes
Database mounted.
Database opened.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY TO PRIMARY
SQL>
--standby 端做如下操作
SQL> alter database commit to switchover to primary;
Database altered.
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1188511744 bytes
Fixed Size 1364228 bytes
Variable Size 754978556 bytes
Database Buffers 419430400 bytes
Redo Buffers 12738560 bytes
Database mounted.
Database opened.
SQL> alter system switch logfile;
System altered.
SQL> select database_role,switchover_status from v$database;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PRIMARY SESSIONS ACTIVE
SQL>
16、ADG做(fail over)切換測試
--standby 端檢查狀態
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ ONLY WITH APPLY
--我們通過shutdown abort方式人工模擬primary奔潰，直接關閉:
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
SQL> shutdown abort
ORACLE instance shut down.
SQL>
--在standby端執行如下操作
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1188511744 bytes
Fixed Size 1364228 bytes
Variable Size 754978556 bytes
Database Buffers 419430400 bytes
Redo Buffers 12738560 bytes
Database mounted.
SQL> alter system flush redo to 'pri';
System altered.
SQL> select thread#, low_sequence#, high_sequence# from v$archive_gap;
no rows selected
--如果沒有發現明顯的gap現象，說明此次的failover不會有數據損失情況。在standby端，要進行關閉apply和結束應用動作。
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database recover managed standby database finish;
Database altered
SQL> select open_mode, switchover_status from v$database;
OPEN_MODE SWITCHOVER_STATUS
-------------------- --------------------
READ ONLY TO PRIMARY
