最簡單的11g Active DataGuard(ADG)搭建配置過程（專案步驟）

kingsql發表於2015-07-23

最簡單的11g Active DataGuard(ADG)搭建配置過程（專案步驟）



一、環境介紹:

    我在db01和db02兩臺Linux虛擬機器上首先分別安裝了一套資料庫軟體，在db01主機上建立了名為woo的資料庫；我們這次的實驗是要搭建了一套Oracle 11g Active DataGuard；目的是為了實現資料庫同步的功能，並且瞭解Oracle 11g DG的基本功能。

db01:192.168.1.50
db02:192.168.1.51

二、11g ADG部署：

1、pri端和sty端配置靜態監聽

  1. [oracle@sty admin]$ cat listener.ora
  2. # listener.ora Network Configuration File: /DBSoft/oracle/product/11.2.4/dbhome_1/network/admin/listener.ora
  3. # Generated by Oracle configuration tools.

  5. SID_LIST_LISTENER =
  6.   (SID_LIST =
  7.     (SID_DESC =
  8.       (SID_NAME = PLSExtProc)
  9.       (ORACLE_HOME = /DBSoft/oracle/product/11.2.4/dbhome_1)
  10.      (PROGRAM = extproc)
  11.     )
  12.       (SID_DESC =
  13.      (SID_NAME = Woo )
  14.      (ORACLE_HOME = /DBSoft/oracle/product/11.2.4/dbhome_1)
  15.     )
  16.    )
  17.    
  18. [oracle@sty admin]$cat tnsname.ora
  19. # tnsnames.ora Network Configuration File: /DBSoft/oracle/product/11.2.4/dbhome_1/network/admin/tnsnames.ora
  20. # Generated by Oracle configuration tools.

  22. STY =
  23.   (DESCRIPTION =
  24.     (ADDRESS_LIST =
  25.       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.51)(PORT = 1521))
  26.     )
  27.     (CONNECT_DATA =
  28.       (SERVICE_NAME = woo)
  29.     )
  30.   )

  32. PRI =
  33.   (DESCRIPTION =
  34.     (ADDRESS_LIST =
  35.       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.50)(PORT = 1521))
  36.     )
  37.     (CONNECT_DATA =
  38.       (SERVICE_NAME = woo)
  39.     )
  40.   )

2、修改primary端初始化引數檔案

  1. startup mount;
  2. alter database archivelog;
  3. alter database force logging;
  4. alter database open;
  5. alter system set log_archive_config = 'DG_CONFIG=(pri,sty)' scope=spfile;
  6. alter system set log_archive_dest_1 = 'LOCATION=/DBBackup/Archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=pri' scope=spfile;
  7. alter system set log_archive_dest_2 = 'SERVICE=sty LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sty' scope=spfile;
  8. alter system set log_archive_dest_state_1 = ENABLE;
  9. alter system set log_archive_dest_state_2 = ENABLE;
  10. alter system set fal_server=sty scope=spfile;
  11. alter system set fal_client=pri scope=spfile;
  12. alter system set standby_file_management=AUTO scope=spfile;

3、在primary端pfile引數檔案和密碼檔案，並且複製到standby段相應位置

  1. SQL> create pfile from spfile;

  3. File created.

  5. [oracle@db01 dbs]$ scp initwoo.ora orapwwoo db02:/DBSoft/oracle/product/11.2.4/dbhome_1/dbs
  6. oracle@192.168.1.51'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
    oracle@192.168.1.51's password: 
    init.ora.512201522543                                                                          100% 1778     1.7KB/s   00:01    
    dp.log                                                                                         100%  116     0.1KB/s   00:00    
    ........

    

4、修改standby端的監聽檔案及初始化引數檔案

  1. --修改監聽檔案
  2. [oracle@db02 ~]$ cd $ORACLE_HOME/network/admin
  3. [oracle@db02 admin]$ vi listener.ora
  4. # listener.ora Network Configuration File: /DBSoft/oracle/product/11.2.4/dbhome_1/network/admin/listener.ora
  5. # Generated by Oracle configuration tools.

  7. LISTENER =
  8.   (DESCRIPTION_LIST =
  9.     (DESCRIPTION =
  10.       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
  11.       (ADDRESS = (PROTOCOL = TCP)(HOST = db02)(PORT = 1521))
  12.     )
  13.   )

  15. SID_LIST_LISTENER =
  16.   (SID_LIST =
  17.     (SID_DESC =
  18.       (SID_NAME = PLSExtProc)
  19.       (ORACLE_HOME = /DBSoft/oracle/product/11.2.4/dbhome_1)
  20.       (PROGRAM = extproc)
  21.     )
  22.   
  23.     (SID_DESC =
  24.       (GLOBAL_DBNAME = woo)
  25.       (ORACLE_HOME = /DBSoft/oracle/product/11.2.4/dbhome_1)
  26.       (SID_NAME = woo)
  27.     )
  28.   
  29.   )

  31. ADR_BASE_LISTENER = /DBSoft/oracle

  33. --啟動監聽
  34. [oracle@db02 dbs]$ lsnrctl start

  36. LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 17-JUN-2015 21:29:57

  38. Copyright (c) 1991, 2013, Oracle. All rights reserved.

  40. Starting /DBSoft/oracle/product/11.2.4/dbhome_1/bin/tnslsnr: please wait...

  42. TNSLSNR for Linux: Version 11.2.0.4.0 - Production
  43. System parameter file is /DBSoft/oracle/product/11.2.4/dbhome_1/network/admin/listener.ora
  44. Log messages written to /DBSoft/oracle/diag/tnslsnr/db02/listener/alert/log.xml
  45. Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  46. Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db02)(PORT=1521)))

  48. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
  49. STATUS of the LISTENER
  50. ------------------------
  51. Alias LISTENER
  52. Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
  53. Start Date 17-JUN-2015 21:29:57
  54. Uptime 0 days 0 hr. 0 min. 1 sec
  55. Trace Level off
  56. Security ON: Local OS Authentication
  57. SNMP OFF
  58. Listener Parameter File /DBSoft/oracle/product/11.2.4/dbhome_1/network/admin/listener.ora
  59. Listener Log File /DBSoft/oracle/diag/tnslsnr/db02/listener/alert/log.xml
  60. Listening Endpoints Summary...
  61.   (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  62.   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db02)(PORT=1521)))
  63. Services Summary...
  64. Service "PLSExtProc" has 1 instance(s).
  65.   Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
  66. Service "woo" has 1 instance(s).
  67.   Instance "woo", status UNKNOWN, has 1 handler(s) for this service...
  68. The command completed successfully

  70. --檢視監聽狀態
  71. [oracle@db02 dbs]$ lsnrctl status

  73. LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 17-JUN-2015 21:30:02

  75. Copyright (c) 1991, 2013, Oracle. All rights reserved.

  77. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
  78. STATUS of the LISTENER
  79. ------------------------
  80. Alias LISTENER
  81. Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
  82. Start Date 17-JUN-2015 21:29:57
  83. Uptime 0 days 0 hr. 0 min. 4 sec
  84. Trace Level off
  85. Security ON: Local OS Authentication
  86. SNMP OFF
  87. Listener Parameter File /DBSoft/oracle/product/11.2.4/dbhome_1/network/admin/listener.ora
  88. Listener Log File /DBSoft/oracle/diag/tnslsnr/db02/listener/alert/log.xml
  89. Listening Endpoints Summary...
  90.   (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  91.   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db02)(PORT=1521)))
  92. Services Summary...
  93. Service "PLSExtProc" has 1 instance(s).
  94.   Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
  95. Service "woo" has 1 instance(s).
  96.   Instance "woo", status UNKNOWN, has 1 handler(s) for this service...
  97. The command completed successfully
  98. [oracle@db02 dbs]$


  101. --修改引數檔案
  102. [oracle@db02 ~]$ sqlplus / as sysdba

  104. SQL*Plus: Release 11.2.0.4.0 Production on Wed Jun 17 21:35:54 2015

  106. Copyright (c) 1982, 2013, Oracle. All rights reserved.

  108. Connected to an idle instance.

  110. SQL> startup nomount;
  111. ORACLE instance started.

  113. Total System Global Area 1188511744 bytes
  114. Fixed Size 1364228 bytes
  115. Variable Size 754978556 bytes
  116. Database Buffers 419430400 bytes
  117. Redo Buffers 12738560 bytes

  119. SQL> create spfile from pfile='/DBSoft/oracle/product/11.2.4/dbhome_1/dbs/initwoo.ora';

  121. File created.

  123. SQL>
  124. SQL> shutdown abort;
  125. ORACLE instance shut down.
  126. SQL> startup nomount;
  127. ORACLE instance started.

  129. Total System Global Area 1188511744 bytes
  130. Fixed Size 1364228 bytes
  131. Variable Size 754978556 bytes
  132. Database Buffers 419430400 bytes
  133. Redo Buffers 12738560 bytes
  134. SQL>

  136. alter system set db_unique_name=sty scope=spfile;
  137. alter system set log_archive_config='DG_CONFIG=(pri,dg)' scope=spfile;
  138. alter system set log_archive_dest_1 ='LOCATION=/DBBackup/Archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=sty' scope=spfile;
  139. alter system set log_archive_dest_2 ='SERVICE=pri LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pri' scope=spfile;
  140. alter system set fal_server=pri scope=spfile;
  141. alter system set fal_client=sty scope=spfile;

  143. SQL> shutdown abort;
  144. ORACLE instance shut down.
  145. SQL> startup nomount
  146. ORACLE instance started.

  148. Total System Global Area 1188511744 bytes
  149. Fixed Size 1364228 bytes
  150. Variable Size 754978556 bytes
  151. Database Buffers 419430400 bytes
  152. Redo Buffers 12738560 bytes
  153. SQL>
  154. SQL>


5、在primary端透過Rman Duplicate建立備庫,在db01上執行如下命令

  1. rman target sys/oracle@pri auxiliary sys/oracle@sty nocatalog
    duplicate target database for standby from active database nofilenamecheck;


    [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日誌


  1. SQL> alter database add standby logfile
  2. group 4 ('/DBSoft/oracle/oradata/woo/styredo04.log') size 50m,
  3. group 5 ('/DBSoft/oracle/oradata/woo/styredo05.log') size 50m,
  4. group 6 ('/DBSoft/oracle/oradata/woo/styredo06.log') size 50m,
  5. group 7 ('/DBSoft/oracle/oradata/woo/styredo07.log') size 50m;

  7. SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

  9.     GROUP# THREAD# SEQUENCE# ARC STATUS
  10. ---------- ---------- ---------- --- ----------
  11.          4 0 0 YES UNASSIGNED
  12.          5 0 0 YES UNASSIGNED
  13.          6 0 0 YES UNASSIGNED
  14.          7 0 0 YES UNASSIGNED

7、在standby端開啟實時日誌應用

  1. SQL> recover managed standby database using current logfile disconnect from session;
  2. Media recovery complete.
  3. SQL>

三、開始測試ADG

8、執行日誌切換測試（在pri端切換歸檔，在節點二上檢查是否也發生了切換）

  1. --primary執行日誌切換

  3. SQL> archive log list;
  4. Database log mode Archive Mode
  5. Automatic archival Enabled
  6. Archive destination /DBBackup/Archive
  7. Oldest online log sequence 21
  8. Next log sequence to archive 23
  9. Current log sequence 23
  10. SQL> alter system switch logfile;

  12. System altered.

  14. SQL> archive log list;
  15. Database log mode Archive Mode
  16. Automatic archival Enabled
  17. Archive destination /DBBackup/Archive
  18. Oldest online log sequence 22
  19. Next log sequence to archive 23
  20. Current log sequence 24

  22. #standby檢視日誌的sequence號也跟著變了
  23. SQL> archive log list;
  24. Database log mode Archive Mode
  25. Automatic archival Enabled
  26. Archive destination /DBBackup/Archive
  27. Oldest online log sequence 22
  28. Next log sequence to archive 0
  29. Current log sequence 23
  30. SQL> archive log list;
  31. Database log mode Archive Mode
  32. Automatic archival Enabled
  33. Archive destination /DBBackup/Archive
  34. Oldest online log sequence 22
  35. Next log sequence to archive 0
  36. Current log sequence 24
  37. SQL>


9、檢視standby啟動的DG程式

  1. SQL> select process,client_process,sequence#,status from v$managed_standby;

  3. PROCESS CLIENT_P SEQUENCE# STATUS
  4. --------- -------- ---------- ------------
  5. ARCH ARCH          23      CLOSING
  6. ARCH ARCH          0       CONNECTED               //歸檔程式
  7. ARCH ARCH          21      CLOSING
  8. ARCH ARCH          0       CONNECTED
  9. RFS ARCH           0       IDLE
  10. RFS UNKNOWN        0       IDLE
  11. RFS LGWR           24      IDLE               //歸檔傳輸程式
  12. RFS UNKNOWN        0       IDLE
  13. MRP0 N/A           24      APPLYING_LOG          //日誌應用程式

  15. 9 rows selected.

10、檢視資料庫的保護模式：


  1. #primary 端檢視，我們可以看到資料庫的保護模式為最大效能
  2. SQL> select database_role,protection_mode,protection_level,open_mode from v$database;

  4. DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL OPEN_MODE
  5. ---------------- -------------------- -------------------- --------------------
  6. PRIMARY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE READ WRITE

  8. #standby 端檢視，也是一樣的。
  9. SQL> select database_role,protection_mode,protection_level,open_mode from v$database;

  11. DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL OPEN_MODE
  12. ---------------- -------------------- -------------------- --------------------
  13. PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE MOUNTED

11、檢視DG的日誌資訊


  1. SQL> select * from v$dataguard_status;

  3. FACILITY SEVERITY DEST_ID MESSAGE_NUM ERROR_CODE CAL TIMESTAMP MESSAGE
  4. ------------------------ ------------- ---------- ----------- ---------- --- --------- --------------------------------------------------------------------------------
  5. Log Transport Services Informational 0 1 0 NO 17-JUN-15 ARC0: Archival started
  6. Log Transport Services Informational 0 2 0 NO 17-JUN-15 ARC1: Archival started
  7. Log Transport Services Informational 0 3 0 NO 17-JUN-15 ARC2: Archival started
  8. Log Transport Services Informational 0 4 0 NO 17-JUN-15 ARC2: Becoming the 'no FAL' ARCH
  9. Log Transport Services Informational 0 5 0 NO 17-JUN-15 ARC2: Becoming the 'no SRL' ARCH
  10. Log Transport Services Informational 0 6 0 NO 17-JUN-15 ARC1: Becoming the heartbeat ARCH
  11. Log Transport Services Control 0 7 0 YES 17-JUN-15 ARC2: Beginning to archive thread 1 sequence 15 (882580-883734)
  12. Log Transport Services Informational 0 8 0 NO 17-JUN-15 ARC3: Archival started
  13. Log Transport Services Control 0 9 0 YES 17-JUN-15 ARC2: Completed archiving thread 1 sequence 15 (882580-883734)
  14. Log Transport Services Control 0 10 0 YES 17-JUN-15 LGWR: Beginning to archive log 2 thread 1 sequence 17
  15. Log Transport Services Control 0 11 0 YES 17-JUN-15 ARC2: Beginning to archive thread 1 sequence 16 (883734-883788)
  16. Log Transport Services Control 0 12 0 YES 17-JUN-15 ARC2: Completed archiving thread 1 sequence 16 (883734-883788)
  17. Log Transport Services Warning 2 13 3113 NO 17-JUN-15 LGWR: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (3113)
  18. Log Transport Services Warning 2 14 3113 NO 17-JUN-15 LGWR: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
  19. Log Transport Services Error 2 15 3113 YES 17-JUN-15 Error 3113 for archive log file 2 to 'sty'
  20. Log Transport Services Error 2 16 1041 YES 17-JUN-15 LGWR: Error 1041 disconnecting from destination LOG_ARCHIVE_DEST_2 standby host 'sty'
  21. Log Transport Services Warning 2 17 3113 NO 17-JUN-15 ARC1: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (3113)
  22. Log Transport Services Error 0 18 1034 YES 17-JUN-15 Error 1034 received logging on to the standby
  23. Log Transport Services Error 2 19 1034 YES 17-JUN-15 ARC1: Error 1034 attaching to RFS for reconnect
  24. Log Transport Services Error 2 20 3113 YES 17-JUN-15 PING[ARC1]: Error 3113 when pinging standby sty.
  25. Log Transport Services Error 2 21 16058 YES 17-JUN-15 PING[ARC1]: Heartbeat failed to connect to standby 'sty'. Error is 16058.
  26. Log Transport Services Warning 0 22 3113 NO 17-JUN-15 LGWR: Failed to archive log 2 thread 1 sequence 17 (3113)
  27. Log Transport Services Control 0 23 0 YES 17-JUN-15 ARC2: Beginning to archive thread 1 sequence 17 (883788-884545)
  28. Log Transport Services Control 0 24 0 YES 17-JUN-15 ARC2: Completed archiving thread 1 sequence 17 (883788-884545)
  29. Log Transport Services Control 0 25 0 YES 17-JUN-15 ARCH: Beginning to archive thread 1 sequence 18 (884545-884856)
  30. Log Transport Services Control 0 26 0 YES 17-JUN-15 ARCH: Completed archiving thread 1 sequence 18 (884545-884856)
  31. Log Transport Services Control 0 27 0 YES 17-JUN-15 LGWR: Beginning to archive log 2 thread 1 sequence 20
  32. Log Transport Services Control 0 28 0 YES 17-JUN-15 ARC2: Beginning to archive thread 1 sequence 19 (884856-884904)
  33. Log Transport Services Control 0 29 0 YES 17-JUN-15 ARC2: Completed archiving thread 1 sequence 19 (884856-884904)
  34. Log Transport Services Informational 2 30 0 NO 17-JUN-15 ARC0: Archive log rejected (thread 1 sequence 19) at host 'sty'
  35. Log Transport Services Informational 0 31 0 NO 17-JUN-15 ARC4: Archival started
  36. Log Transport Services Informational 0 32 0 NO 17-JUN-15 ARCH shutting down
  37. Log Transport Services Informational 0 33 0 NO 17-JUN-15 ARC4: Archival stopped
  38. Log Transport Services Control 0 34 0 YES 17-JUN-15 LGWR: Completed archiving log 2 thread 1 sequence 20
  39. 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
  40. Log Transport Services Control 0 36 0 YES 17-JUN-15 LGWR: Beginning to archive log 3 thread 1 sequence 21
  41. Log Transport Services Control 0 37 0 YES 17-JUN-15 ARC0: Beginning to archive thread 1 sequence 20 (884904-885855)
  42. Log Transport Services Control 0 38 0 YES 17-JUN-15 ARC0: Completed archiving thread 1 sequence 20 (884904-885855)
  43. Log Transport Services Control 0 39 0 YES 17-JUN-15 LGWR: Completed archiving log 3 thread 1 sequence 21
  44. 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
  45. Log Transport Services Control 0 41 0 YES 17-JUN-15 LGWR: Beginning to archive log 1 thread 1 sequence 22
  46. Log Transport Services Control 0 42 0 YES 17-JUN-15 ARC2: Beginning to archive thread 1 sequence 21 (885855-886131)
  47. Log Transport Services Control 0 43 0 YES 17-JUN-15 ARC2: Completed archiving thread 1 sequence 21 (885855-886131)
  48. Log Transport Services Control 0 44 0 YES 18-JUN-15 LGWR: Completed archiving log 1 thread 1 sequence 22
  49. 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
  50. Log Transport Services Control 0 46 0 YES 18-JUN-15 LGWR: Beginning to archive log 2 thread 1 sequence 23
  51. Log Transport Services Control 0 47 0 YES 18-JUN-15 ARC3: Beginning to archive thread 1 sequence 22 (886131-927736)
  52. Log Transport Services Control 0 48 0 YES 18-JUN-15 ARC3: Completed archiving thread 1 sequence 22 (886131-927736)
  53. Log Transport Services Control 0 49 0 YES 18-JUN-15 LGWR: Completed archiving log 2 thread 1 sequence 23
  54. 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
  55. Log Transport Services Control 0 51 0 YES 18-JUN-15 LGWR: Beginning to archive log 3 thread 1 sequence 24
  56. Log Transport Services Control 0 52 0 YES 18-JUN-15 ARC0: Beginning to archive thread 1 sequence 23 (927736-961263)
  57. Log Transport Services Control 0 53 0 YES 18-JUN-15 ARC0: Completed archiving thread 1 sequence 23 (927736-961263)

  59. 53 rows selected.

12、Open Read Only standby資料庫並且開啟實時日誌應用

  1. SQL> shutdown immediate
  2. ORA-01109: database not open


  5. Database dismounted.
  6. ORACLE instance shut down.
  7. SQL> startup
  8. ORACLE instance started.

  10. Total System Global Area 1188511744 bytes
  11. Fixed Size 1364228 bytes
  12. Variable Size 754978556 bytes
  13. Database Buffers 419430400 bytes
  14. Redo Buffers 12738560 bytes
  15. Database mounted.
  16. Database opened.
  17. SQL> select database_role,protection_mode,protection_level,open_mode from v$database;

  19. DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL OPEN_MODE
  20. ---------------- -------------------- -------------------- --------------------
  21. PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE READ ONLY

  23. SQL> select process,client_process,sequence#,status from v$managed_standby;

  25. PROCESS CLIENT_P SEQUENCE# STATUS
  26. --------- -------- ---------- ------------
  27. ARCH ARCH 0 CONNECTED
  28. ARCH ARCH 0 CONNECTED
  29. ARCH ARCH 0 CONNECTED
  30. ARCH ARCH 26 CLOSING
  31. RFS ARCH 0 IDLE
  32. RFS UNKNOWN 0 IDLE
  33. RFS LGWR 27 IDLE

  35. 7 rows selected.

  37. SQL> recover managed standby database using current logfile disconnect from session;
  38. Media recovery complete.

  40. SQL> select process,client_process,sequence#,status from v$managed_standby;

  42. PROCESS CLIENT_P SEQUENCE# STATUS
  43. --------- -------- ---------- ------------
  44. ARCH ARCH          0         CONNECTED
  45. ARCH ARCH          0         CONNECTED
  46. ARCH ARCH          0         CONNECTED
  47. ARCH ARCH          26        CLOSING
  48. RFS ARCH           0         IDLE
  49. RFS UNKNOWN        0         IDLE
  50. RFS LGWR           27        IDLE
  51. MRP0 N/A           27        APPLYING_LOG

  53. 8 rows selected.

13、解鎖scott使用者，新增資料，驗證資料是否能同步：

  1. --primary 端操作如下內容
  2. SQL> set line 200
  3. SQL> select username,default_tablespace,account_status from dba_users where username=\'SCOTT\';

  5. USERNAME DEFAULT_TABLESPACE ACCOUNT_STATUS
  6. ------------------------------ ------------------------------ --------------------------------
  7. SCOTT USERS EXPIRED & LOCKED

  9. SQL> alter user scott account unlock;

  11. User altered.

  13. SQL> conn scott/tiger;
  14. ERROR:
  15. ORA-28001: the password has expired


  18. Changing password for scott
  19. New password:
  20. Retype new password:
  21. Password changed
  22. Connected.

  24. SQL> show user
  25. USER is "SCOTT"
  26. SQL> select * from tab;

  28. TNAME TABTYPE CLUSTERID
  29. ------------------------------ ------- ----------
  30. BONUS TABLE
  31. DEPT TABLE
  32. EMP TABLE
  33. SALGRADE TABLE

  35. SQL>

  37. SQL> create table test001 (id number(10),name varchar2(20));

  39. Table created.

  41. SQL> begin
  42.   2 for i in 1..10000 loop
  43.   3 insert into test001 values (1,\'ww\');
  44.   4 end loop;
  45.   5 end;
  46.   6 /

  48. PL/SQL procedure successfully completed.

  50. SQL> commit;

  52. Commit complete.

  54. --standby端查詢scott使用者是否解鎖，以及test001表是否建立並且插入了10000行資料：
  55. SQL> conn scott/tiger;
  56. Connected.

  58. SQL> select * from tab;

  60. TNAME TABTYPE CLUSTERID
  61. ------------------------------ ------- ----------
  62. BONUS TABLE
  63. DEPT TABLE
  64. EMP TABLE
  65. SALGRADE TABLE
  66. TEST001 TABLE

  68. SQL> select count(*) from test001;

  70.   COUNT(*)
  71. ----------
  72.      10000

  74. SQL>

#至此Oracle 11g ADG就已經配置完成了


四、ADG三種模式切換及介紹

14、#ADG有三種(PROTECTION|AVAILABILITY|PERFORMANCE)模式，具體參考: 探索Oracle11gR2 之 DataGuard_03 三種保護模式

  1. --primary操作步驟也就是命令之差：
  2. SQL> select database_role,protection_mode,protection_level from v$database; ----當前為最大效能

  4. DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
  5. ---------------- -------------------- --------------------
  6. PRIMARY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE

  8. SQL>
  9. SQL> alter database set standby database to maximize availability; ----切換為最大可用

  11. Database altered.

  13. SQL> select database_role,protection_mode,protection_level from v$database;

  15. DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
  16. ---------------- -------------------- --------------------
  17. PRIMARY MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

  19. SQL> alter database set standby database to maximize protection; ----切換為最大保護

  21. Database altered.

  23. SQL> select database_role,protection_mode,protection_level from v$database;

  25. DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
  26. ---------------- -------------------- --------------------
  27. PRIMARY MAXIMUM PROTECTION MAXIMUM PROTECTION

  29. SQL>


  32. --#standby端切換到最大保護是需要重啟資料庫到mount模式的:
  33. SQL> select database_role,protection_mode,protection_level from v$database; ----當前為最大效能

  35. DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
  36. ---------------- -------------------- --------------------
  37. PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE

  39. SQL> alter database set standby database to maximize availability; ----切換為最大可用

  41. Database altered.

  43. SQL> select database_role,protection_mode,protection_level from v$database;

  45. DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
  46. ---------------- -------------------- --------------------
  47. PHYSICAL STANDBY MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

  49. SQL> alter database set standby database to maximize protection; ----切換為最大保護模式報錯，需要將standby端啟動到mount狀態切換.
  50. alter database set standby database to maximize protection
  51. *
  52. ERROR at line 1:
  53. ORA-01126: database must be mounted in this instance and not open in any instance


  56. SQL> shutdown immediate ----將資料庫啟動到mount狀態
  57. Database closed.
  58. Database dismounted.
  59. ORACLE instance shut down.

  61. SQL> startup mount;
  62. ORACLE instance started.

  64. Total System Global Area 1188511744 bytes
  65. Fixed Size 1364228 bytes
  66. Variable Size 754978556 bytes
  67. Database Buffers 419430400 bytes
  68. Redo Buffers 12738560 bytes
  69. Database mounted.

  71. SQL> alter database set standby database to maximize protection; ----再次切換為最大可用，成功。

  73. Database altered.

  75. SQL> recover managed standby database using current logfile disconnect from session;
  76. Media recovery complete.

  78. SQL> select database_role,protection_mode,protection_level from v$database;

  80. DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
  81. ---------------- -------------------- --------------------
  82. PHYSICAL STANDBY MAXIMUM PROTECTION MAXIMUM PROTECTION

五、切換測試

15、ADG做(switchover)切換測試

  1. --primary 做如下操作

  3. SQL> alter database commit to switchover to physical standby;

  5. Database altered.

  7. SQL> shutdown immediate
  8. ORA-01012: not logged on
  9. SQL> conn / as sysdba
  10. Connected to an idle instance.

  12. SQL> startup
  13. ORACLE instance started.

  15. Total System Global Area 1188511744 bytes
  16. Fixed Size 1364228 bytes
  17. Variable Size 754978556 bytes
  18. Database Buffers 419430400 bytes
  19. Redo Buffers 12738560 bytes
  20. Database mounted.
  21. Database opened.

  23. SQL> alter database recover managed standby database disconnect from session;

  25. Database altered.

  27. SQL> select database_role,switchover_status from v$database;

  29. DATABASE_ROLE SWITCHOVER_STATUS
  30. ---------------- --------------------
  31. PHYSICAL STANDBY TO PRIMARY

  33. SQL>

  35. --standby 端做如下操作

  37. SQL> alter database commit to switchover to primary;

  39. Database altered.

  41. SQL> shutdown immediate
  42. ORA-01109: database not open


  45. Database dismounted.
  46. ORACLE instance shut down.

  48. SQL> startup
  49. ORACLE instance started.

  51. Total System Global Area 1188511744 bytes
  52. Fixed Size 1364228 bytes
  53. Variable Size 754978556 bytes
  54. Database Buffers 419430400 bytes
  55. Redo Buffers 12738560 bytes
  56. Database mounted.
  57. Database opened.

  59. SQL> alter system switch logfile;

  61. System altered.

  63. SQL> select database_role,switchover_status from v$database;

  65. DATABASE_ROLE SWITCHOVER_STATUS
  66. ---------------- --------------------
  67. PRIMARY SESSIONS ACTIVE

  69. SQL>

16、ADG做(fail over)切換測試

  1. --standby 端檢查狀態
  2. SQL> select open_mode from v$database;

  4. OPEN_MODE
  5. --------------------
  6. READ ONLY WITH APPLY

  8. --我們透過shutdown abort方式人工模擬primary奔潰，直接關閉:

  10. SQL> select open_mode from v$database;

  12. OPEN_MODE
  13. --------------------
  14. READ WRITE

  16. SQL> shutdown abort
  17. ORACLE instance shut down.
  18. SQL>

  20. --在standby端執行如下操作

  22. SQL> startup mount;
  23. ORACLE instance started.

  25. Total System Global Area 1188511744 bytes
  26. Fixed Size 1364228 bytes
  27. Variable Size 754978556 bytes
  28. Database Buffers 419430400 bytes
  29. Redo Buffers 12738560 bytes
  30. Database mounted.

  32. SQL> alter system flush redo to \'pri\';

  34. System altered.

  36. SQL> select thread#, low_sequence#, high_sequence# from v$archive_gap;

  38. no rows selected

  40. --如果沒有發現明顯的gap現象，說明此次的failover不會有資料損失情況。在standby端，要進行關閉apply和結束應用動作。

  42. SQL> alter database recover managed standby database cancel;
  43. Database altered.
  44.  
  45.  
  46. SQL> alter database recover managed standby database finish;
  47. Database altered
  48.  
  49.  
  50. SQL> select open_mode, switchover_status from v$database;
  51. OPEN_MODE SWITCHOVER_STATUS
  52. -------------------- --------------------
  53. READ ONLY TO PRIMARY


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28389881/viewspace-1745589/，如需轉載，請註明出處，否則將追究法律責任。

相關文章