ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > 最简单的11g Active DataGuard(ADG)搭建配置过程（项目步骤）

最简单的11g Active DataGuard(ADG)搭建配置过程（项目步骤）

原创 Oracle 作者：wuweilong 时间：2015-06-18 16:19:24 3 删除 编辑

最简单的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,sty)' 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博客 ” ，链接：https://blog.itpub.net/20674423/viewspace-1704119/ ，如需转载，请注明出处，否则将追究法律责任。

1
0
上一篇： TNS-03505 名称无法解析
下一篇： 添加ASM磁盘报错ORA-02097和ORA-15014
请登录后发表评论 登录
全部评论
wuweilong
专家姓名：吴伟龙
Oracle ACE Associate； 12cOCM；11gOCM；MySQL OCP；PGCA；PGCE；KCA; KCP; 本人拥有十年以上运营商，广电传媒，能源，制造等行业DBA从业经验; 有着丰富的管理及运维经验，经历过国家级重点项目，省级标杆项目，新型试点项目

注册时间：2010-01-08

  • 博文量
    779
  • 访问量
    4931706

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593