ITPub博客

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

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

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

最简单的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.

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

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

  29. PRI =
  30.   (DESCRIPTION =
  31.     (ADDRESS_LIST =
  32.       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.50)(PORT = 1521))
  33.     )
  34.     (CONNECT_DATA =
  35.       (SERVICE_NAME = woo)
  36.     )
  37.   )

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;

  2. File created.

  3. [oracle@db01 dbs]$ scp initwoo.ora orapwwoo db02:/DBSoft/oracle/product/11.2.4/dbhome_1/dbs
  4. 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.

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

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

  28. ADR_BASE_LISTENER = /DBSoft/oracle

  29. --启动监听
  30. [oracle@db02 dbs]$ lsnrctl start

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

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

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

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

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

  60. --查看监听状态
  61. [oracle@db02 dbs]$ lsnrctl status

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

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

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


  86. --修改参数文件
  87. [oracle@db02 ~]$ sqlplus / as sysdba

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

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

  90. Connected to an idle instance.

  91. SQL> startup nomount;
  92. ORACLE instance started.

  93. Total System Global Area 1188511744 bytes
  94. Fixed Size 1364228 bytes
  95. Variable Size 754978556 bytes
  96. Database Buffers 419430400 bytes
  97. Redo Buffers 12738560 bytes

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

  99. File created.

  100. SQL>
  101. SQL> shutdown abort;
  102. ORACLE instance shut down.
  103. SQL> startup nomount;
  104. ORACLE instance started.

  105. Total System Global Area 1188511744 bytes
  106. Fixed Size 1364228 bytes
  107. Variable Size 754978556 bytes
  108. Database Buffers 419430400 bytes
  109. Redo Buffers 12738560 bytes
  110. SQL>

  111. alter system set db_unique_name=sty scope=spfile;
  112. alter system set log_archive_config='DG_CONFIG=(pri,dg)' scope=spfile;
  113. alter system set log_archive_dest_1 ='LOCATION=/DBBackup/Archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=sty' scope=spfile;
  114. alter system set log_archive_dest_2 ='SERVICE=pri LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pri' scope=spfile;
  115. alter system set fal_server=pri scope=spfile;
  116. alter system set fal_client=sty scope=spfile;

  117. SQL> shutdown abort;
  118. ORACLE instance shut down.
  119. SQL> startup nomount
  120. ORACLE instance started.

  121. Total System Global Area 1188511744 bytes
  122. Fixed Size 1364228 bytes
  123. Variable Size 754978556 bytes
  124. Database Buffers 419430400 bytes
  125. Redo Buffers 12738560 bytes
  126. SQL>
  127. 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;

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

  7.     GROUP# THREAD# SEQUENCE# ARC STATUS
  8. ---------- ---------- ---------- --- ----------
  9.          4 0 0 YES UNASSIGNED
  10.          5 0 0 YES UNASSIGNED
  11.          6 0 0 YES UNASSIGNED
  12.          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执行日志切换

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

  10. System altered.

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

  18. #standby查看日志的sequence号也跟着变了
  19. SQL> archive log list;
  20. Database log mode Archive Mode
  21. Automatic archival Enabled
  22. Archive destination /DBBackup/Archive
  23. Oldest online log sequence 22
  24. Next log sequence to archive 0
  25. Current log sequence 23
  26. SQL> archive log list;
  27. Database log mode Archive Mode
  28. Automatic archival Enabled
  29. Archive destination /DBBackup/Archive
  30. Oldest online log sequence 22
  31. Next log sequence to archive 0
  32. Current log sequence 24
  33. SQL>


9、查看standby启动的DG进程

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

  2. PROCESS CLIENT_P SEQUENCE# STATUS
  3. --------- -------- ---------- ------------
  4. ARCH ARCH          23      CLOSING
  5. ARCH ARCH          0       CONNECTED               //归档进程
  6. ARCH ARCH          21      CLOSING
  7. ARCH ARCH          0       CONNECTED
  8. RFS ARCH           0       IDLE
  9. RFS UNKNOWN        0       IDLE
  10. RFS LGWR           24      IDLE               //归档传输进程
  11. RFS UNKNOWN        0       IDLE
  12. MRP0 N/A           24      APPLYING_LOG          //日志应用进程

  13. 9 rows selected.

10、查看数据库的保护模式:


  1. #primary 端查看,我们可以看到数据库的保护模式为最大性能
  2. SQL> select database_role,protection_mode,protection_level,open_mode from v$database;

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

  6. #standby 端查看,也是一样的。
  7. SQL> select database_role,protection_mode,protection_level,open_mode from v$database;

  8. DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL OPEN_MODE
  9. ---------------- -------------------- -------------------- --------------------
  10. PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE MOUNTED

11、查看DG的日志信息


  1. SQL> select * from v$dataguard_status;

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

  57. 53 rows selected.

12、Open Read Only standby数据库并且开启实时日志应用

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


  3. Database dismounted.
  4. ORACLE instance shut down.
  5. SQL> startup
  6. ORACLE instance started.

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

  15. DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL OPEN_MODE
  16. ---------------- -------------------- -------------------- --------------------
  17. PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE READ ONLY

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

  19. PROCESS CLIENT_P SEQUENCE# STATUS
  20. --------- -------- ---------- ------------
  21. ARCH ARCH 0 CONNECTED
  22. ARCH ARCH 0 CONNECTED
  23. ARCH ARCH 0 CONNECTED
  24. ARCH ARCH 26 CLOSING
  25. RFS ARCH 0 IDLE
  26. RFS UNKNOWN 0 IDLE
  27. RFS LGWR 27 IDLE

  28. 7 rows selected.

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

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

  32. PROCESS CLIENT_P SEQUENCE# STATUS
  33. --------- -------- ---------- ------------
  34. ARCH ARCH          0         CONNECTED
  35. ARCH ARCH          0         CONNECTED
  36. ARCH ARCH          0         CONNECTED
  37. ARCH ARCH          26        CLOSING
  38. RFS ARCH           0         IDLE
  39. RFS UNKNOWN        0         IDLE
  40. RFS LGWR           27        IDLE
  41. MRP0 N/A           27        APPLYING_LOG

  42. 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\';

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

  7. SQL> alter user scott account unlock;

  8. User altered.

  9. SQL> conn scott/tiger;
  10. ERROR:
  11. ORA-28001: the password has expired


  12. Changing password for scott
  13. New password:
  14. Retype new password:
  15. Password changed
  16. Connected.

  17. SQL> show user
  18. USER is "SCOTT"
  19. SQL> select * from tab;

  20. TNAME TABTYPE CLUSTERID
  21. ------------------------------ ------- ----------
  22. BONUS TABLE
  23. DEPT TABLE
  24. EMP TABLE
  25. SALGRADE TABLE

  26. SQL>

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

  28. Table created.

  29. SQL> begin
  30.   2 for i in 1..10000 loop
  31.   3 insert into test001 values (1,\'ww\');
  32.   4 end loop;
  33.   5 end;
  34.   6 /

  35. PL/SQL procedure successfully completed.

  36. SQL> commit;

  37. Commit complete.

  38. --standby端查询scott用户是否解锁,以及test001表是否创建并且插入了10000行数据:
  39. SQL> conn scott/tiger;
  40. Connected.

  41. SQL> select * from tab;

  42. TNAME TABTYPE CLUSTERID
  43. ------------------------------ ------- ----------
  44. BONUS TABLE
  45. DEPT TABLE
  46. EMP TABLE
  47. SALGRADE TABLE
  48. TEST001 TABLE

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

  50.   COUNT(*)
  51. ----------
  52.      10000

  53. 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; ----当前为最大性能

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

  6. SQL>
  7. SQL> alter database set standby database to maximize availability; ----切换为最大可用

  8. Database altered.

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

  10. DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
  11. ---------------- -------------------- --------------------
  12. PRIMARY MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

  13. SQL> alter database set standby database to maximize protection; ----切换为最大保护

  14. Database altered.

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

  16. DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
  17. ---------------- -------------------- --------------------
  18. PRIMARY MAXIMUM PROTECTION MAXIMUM PROTECTION

  19. SQL>


  20. --#standby端切换到最大保护是需要重启数据库到mount模式的:
  21. SQL> select database_role,protection_mode,protection_level from v$database; ----当前为最大性能

  22. DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
  23. ---------------- -------------------- --------------------
  24. PHYSICAL STANDBY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE

  25. SQL> alter database set standby database to maximize availability; ----切换为最大可用

  26. Database altered.

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

  28. DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
  29. ---------------- -------------------- --------------------
  30. PHYSICAL STANDBY MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

  31. SQL> alter database set standby database to maximize protection; ----切换为最大保护模式报错,需要将standby端启动到mount状态切换.
  32. alter database set standby database to maximize protection
  33. *
  34. ERROR at line 1:
  35. ORA-01126: database must be mounted in this instance and not open in any instance


  36. SQL> shutdown immediate ----将数据库启动到mount状态
  37. Database closed.
  38. Database dismounted.
  39. ORACLE instance shut down.

  40. SQL> startup mount;
  41. ORACLE instance started.

  42. Total System Global Area 1188511744 bytes
  43. Fixed Size 1364228 bytes
  44. Variable Size 754978556 bytes
  45. Database Buffers 419430400 bytes
  46. Redo Buffers 12738560 bytes
  47. Database mounted.

  48. SQL> alter database set standby database to maximize protection; ----再次切换为最大可用,成功。

  49. Database altered.

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

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

  53. DATABASE_ROLE PROTECTION_MODE PROTECTION_LEVEL
  54. ---------------- -------------------- --------------------
  55. PHYSICAL STANDBY MAXIMUM PROTECTION MAXIMUM PROTECTION

五、切换测试

15、ADG做(switchover)切换测试

  1. --primary 做如下操作

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

  3. Database altered.

  4. SQL> shutdown immediate
  5. ORA-01012: not logged on
  6. SQL> conn / as sysdba
  7. Connected to an idle instance.

  8. SQL> startup
  9. 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> alter database recover managed standby database disconnect from session;

  18. Database altered.

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

  20. DATABASE_ROLE SWITCHOVER_STATUS
  21. ---------------- --------------------
  22. PHYSICAL STANDBY TO PRIMARY

  23. SQL>

  24. --standby 端做如下操作

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

  26. Database altered.

  27. SQL> shutdown immediate
  28. ORA-01109: database not open


  29. Database dismounted.
  30. ORACLE instance shut down.

  31. SQL> startup
  32. ORACLE instance started.

  33. Total System Global Area 1188511744 bytes
  34. Fixed Size 1364228 bytes
  35. Variable Size 754978556 bytes
  36. Database Buffers 419430400 bytes
  37. Redo Buffers 12738560 bytes
  38. Database mounted.
  39. Database opened.

  40. SQL> alter system switch logfile;

  41. System altered.

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

  43. DATABASE_ROLE SWITCHOVER_STATUS
  44. ---------------- --------------------
  45. PRIMARY SESSIONS ACTIVE

  46. SQL>

16、ADG做(fail over)切换测试

  1. --standby 端检查状态
  2. SQL> select open_mode from v$database;

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

  6. --我们通过shutdown abort方式人工模拟primary奔溃,直接关闭:

  7. SQL> select open_mode from v$database;

  8. OPEN_MODE
  9. --------------------
  10. READ WRITE

  11. SQL> shutdown abort
  12. ORACLE instance shut down.
  13. SQL>

  14. --在standby端执行如下操作

  15. SQL> startup mount;
  16. ORACLE instance started.

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

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

  24. System altered.

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

  26. no rows selected

  27. --如果没有发现明显的gap现象,说明此次的failover不会有数据损失情况。在standby端,要进行关闭apply和结束应用动作。

  28. SQL> alter database recover managed standby database cancel;
  29. Database altered.
  30.  
  31.  
  32. SQL> alter database recover managed standby database finish;
  33. Database altered
  34.  
  35.  
  36. SQL> select open_mode, switchover_status from v$database;
  37. OPEN_MODE SWITCHOVER_STATUS
  38. -------------------- --------------------
  39. READ ONLY TO PRIMARY


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/20674423/viewspace-1704119/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2010-01-08

  • 博文量
    251
  • 访问量
    1983160