三、开始测试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 端操作如下内容
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就已经配置完成了