当前位置: 首页 > 工具软件 > ADG > 使用案例 >

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

薛弘壮
2023-12-01

最简单的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端配置静态监听

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

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /DBSoft/oracle/product/11.2.4/dbhome_1)
     (PROGRAM = extproc)
    )
      (SID_DESC =
     (SID_NAME = Woo )
     (ORACLE_HOME = /DBSoft/oracle/product/11.2.4/dbhome_1)
    )
   )
   
[oracle@sty admin]$cat tnsname.ora
# tnsnames.ora Network Configuration File: /DBSoft/oracle/product/11.2.4/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

STY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.51)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = woo)
    )
  )

PRI =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.50)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = woo)
    )
  )

 

2、修改primary端初始化参数文件

 

startup mount;
alter database archivelog;
alter database force logging;
alter database open;
alter system set log_archive_config = 'DG_CONFIG=(pri,sty)' scope=spfile;
alter system set log_archive_dest_1 = 'LOCATION=/DBBackup/Archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=pri' scope=spfile;
alter system set log_archive_dest_2 = 'SERVICE=sty LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sty' scope=spfile;
alter system set log_archive_dest_state_1 = ENABLE;
alter system set log_archive_dest_state_2 = ENABLE;
alter system set fal_server=sty scope=spfile;
alter system set fal_client=pri scope=spfile;
alter system set standby_file_management=AUTO scope=spfile;

 

3、在primary端pfile参数文件和密码文件,并且拷贝到standby段相应位置

 

SQL> create pfile from spfile;

File created.

[oracle@db01 dbs]$ scp initwoo.ora orapwwoo db02:/DBSoft/oracle/product/11.2.4/dbhome_1/dbs
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端的监听文件及初始化参数文件

 

--修改监听文件
[oracle@db02 ~]$ cd $ORACLE_HOME/network/admin
[oracle@db02 admin]$ vi listener.ora 
# listener.ora Network Configuration File: /DBSoft/oracle/product/11.2.4/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = db02)(PORT = 1521))
    )
  )

SID_LIST_LISTENER =  
  (SID_LIST =  
    (SID_DESC =  
      (SID_NAME = PLSExtProc)  
      (ORACLE_HOME = /DBSoft/oracle/product/11.2.4/dbhome_1)  
      (PROGRAM = extproc)  
    )  
  
    (SID_DESC =  
      (GLOBAL_DBNAME = woo)  
      (ORACLE_HOME = /DBSoft/oracle/product/11.2.4/dbhome_1)  
      (SID_NAME = woo)  
    )  
  
  )  

ADR_BASE_LISTENER = /DBSoft/oracle

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

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

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

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

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

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

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

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

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

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


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

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

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

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1188511744 bytes
Fixed Size                  1364228 bytes
Variable Size             754978556 bytes
Database Buffers          419430400 bytes
Redo Buffers               12738560 bytes

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

File created.

SQL> 
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1188511744 bytes
Fixed Size                  1364228 bytes
Variable Size             754978556 bytes
Database Buffers          419430400 bytes
Redo Buffers               12738560 bytes
SQL> 

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

SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area 1188511744 bytes
Fixed Size                  1364228 bytes
Variable Size             754978556 bytes
Database Buffers          419430400 bytes
Redo Buffers               12738560 bytes
SQL>
SQL>

 

5、在primary端通过Rman Duplicate创建备库,在db01上执行如下命令

rman target sys/oracle@pri auxiliary sys/oracle@sty nocatalog
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日志

 

 

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

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

    GROUP#    THREAD#  SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------
         4          0          0 YES UNASSIGNED
         5          0          0 YES UNASSIGNED
         6          0          0 YES UNASSIGNED
         7          0          0 YES UNASSIGNED

 

7、在standby端开启实时日志应用

 

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

 

三、开始测试ADG

 

8、执行日志切换测试(在pri端切换归档,在节点二上检查是否也发生了切换)

--primary执行日志切换

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /DBBackup/Archive
Oldest online log sequence     21
Next log sequence to archive   23
Current log sequence           23
SQL> alter system switch logfile;

System altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /DBBackup/Archive
Oldest online log sequence     22
Next log sequence to archive   23
Current log sequence           24

#standby查看日志的sequence号也跟着变了
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /DBBackup/Archive
Oldest online log sequence     22
Next log sequence to archive   0
Current log sequence           23
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /DBBackup/Archive
Oldest online log sequence     22
Next log sequence to archive   0
Current log sequence           24
SQL> 

 

9、查看standby启动的DG进程

 

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

PROCESS   CLIENT_P  SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH      ARCH             23 CLOSING
ARCH      ARCH              0 CONNECTED            //归档进程
ARCH      ARCH             21 CLOSING
ARCH      ARCH              0 CONNECTED
RFS       ARCH              0 IDLE
RFS       UNKNOWN           0 IDLE
RFS       LGWR             24 IDLE               //归档传输进程
RFS       UNKNOWN           0 IDLE
MRP0      N/A              24 APPLYING_LOG      //日志应用进程

9 rows selected.

 

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

 

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

DATABASE_ROLE    PROTECTION_MODE      PROTECTION_LEVEL     OPEN_MODE
---------------- -------------------- -------------------- --------------------
PRIMARY          MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  READ WRITE

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

DATABASE_ROLE    PROTECTION_MODE      PROTECTION_LEVEL     OPEN_MODE
---------------- -------------------- -------------------- --------------------
PHYSICAL STANDBY MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  MOUNTED

 

11、查看DG的日志信息

 

SQL> select * from v$dataguard_status;

FACILITY                 SEVERITY         DEST_ID MESSAGE_NUM ERROR_CODE CAL TIMESTAMP MESSAGE
------------------------ ------------- ---------- ----------- ---------- --- --------- --------------------------------------------------------------------------------
Log Transport Services   Informational          0           1          0 NO  17-JUN-15 ARC0: Archival started
Log Transport Services   Informational          0           2          0 NO  17-JUN-15 ARC1: Archival started
Log Transport Services   Informational          0           3          0 NO  17-JUN-15 ARC2: Archival started
Log Transport Services   Informational          0           4          0 NO  17-JUN-15 ARC2: Becoming the 'no FAL' ARCH
Log Transport Services   Informational          0           5          0 NO  17-JUN-15 ARC2: Becoming the 'no SRL' ARCH
Log Transport Services   Informational          0           6          0 NO  17-JUN-15 ARC1: Becoming the heartbeat ARCH
Log Transport Services   Control                0           7          0 YES 17-JUN-15 ARC2: Beginning to archive thread 1 sequence 15 (882580-883734)
Log Transport Services   Informational          0           8          0 NO  17-JUN-15 ARC3: Archival started
Log Transport Services   Control                0           9          0 YES 17-JUN-15 ARC2: Completed archiving thread 1 sequence 15 (882580-883734)
Log Transport Services   Control                0          10          0 YES 17-JUN-15 LGWR: Beginning to archive log 2 thread 1 sequence 17
Log Transport Services   Control                0          11          0 YES 17-JUN-15 ARC2: Beginning to archive thread 1 sequence 16 (883734-883788)
Log Transport Services   Control                0          12          0 YES 17-JUN-15 ARC2: Completed archiving thread 1 sequence 16 (883734-883788)
Log Transport Services   Warning                2          13       3113 NO  17-JUN-15 LGWR: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (3113)
Log Transport Services   Warning                2          14       3113 NO  17-JUN-15 LGWR: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
Log Transport Services   Error                  2          15       3113 YES 17-JUN-15 Error 3113 for archive log file 2 to 'sty'
Log Transport Services   Error                  2          16       1041 YES 17-JUN-15 LGWR: Error 1041 disconnecting from destination LOG_ARCHIVE_DEST_2 standby host 'sty'

Log Transport Services   Warning                2          17       3113 NO  17-JUN-15 ARC1: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (3113)
Log Transport Services   Error                  0          18       1034 YES 17-JUN-15 Error 1034 received logging on to the standby
Log Transport Services   Error                  2          19       1034 YES 17-JUN-15 ARC1: Error 1034 attaching to RFS for reconnect
Log Transport Services   Error                  2          20       3113 YES 17-JUN-15 PING[ARC1]: Error 3113 when pinging standby sty.
Log Transport Services   Error                  2          21      16058 YES 17-JUN-15 PING[ARC1]: Heartbeat failed to connect to standby 'sty'. Error is 16058.
Log Transport Services   Warning                0          22       3113 NO  17-JUN-15 LGWR: Failed to archive log 2 thread 1 sequence 17 (3113)
Log Transport Services   Control                0          23          0 YES 17-JUN-15 ARC2: Beginning to archive thread 1 sequence 17 (883788-884545)
Log Transport Services   Control                0          24          0 YES 17-JUN-15 ARC2: Completed archiving thread 1 sequence 17 (883788-884545)
Log Transport Services   Control                0          25          0 YES 17-JUN-15 ARCH: Beginning to archive thread 1 sequence 18 (884545-884856)
Log Transport Services   Control                0          26          0 YES 17-JUN-15 ARCH: Completed archiving thread 1 sequence 18 (884545-884856)
Log Transport Services   Control                0          27          0 YES 17-JUN-15 LGWR: Beginning to archive log 2 thread 1 sequence 20
Log Transport Services   Control                0          28          0 YES 17-JUN-15 ARC2: Beginning to archive thread 1 sequence 19 (884856-884904)
Log Transport Services   Control                0          29          0 YES 17-JUN-15 ARC2: Completed archiving thread 1 sequence 19 (884856-884904)
Log Transport Services   Informational          2          30          0 NO  17-JUN-15 ARC0: Archive log rejected (thread 1 sequence 19) at host 'sty'
Log Transport Services   Informational          0          31          0 NO  17-JUN-15 ARC4: Archival started
Log Transport Services   Informational          0          32          0 NO  17-JUN-15 ARCH shutting down
Log Transport Services   Informational          0          33          0 NO  17-JUN-15 ARC4: Archival stopped
Log Transport Services   Control                0          34          0 YES 17-JUN-15 LGWR: Completed archiving log 2 thread 1 sequence 20
Log Transport Services   Warning                1          35          0 NO  17-JUN-15 LGWR: Standby redo logfile selected for thread 1 sequence 21 for destination LOG _ARCHIVE_DEST_2
Log Transport Services   Control                0          36          0 YES 17-JUN-15 LGWR: Beginning to archive log 3 thread 1 sequence 21
Log Transport Services   Control                0          37          0 YES 17-JUN-15 ARC0: Beginning to archive thread 1 sequence 20 (884904-885855)
Log Transport Services   Control                0          38          0 YES 17-JUN-15 ARC0: Completed archiving thread 1 sequence 20 (884904-885855)
Log Transport Services   Control                0          39          0 YES 17-JUN-15 LGWR: Completed archiving log 3 thread 1 sequence 21
Log Transport Services   Warning                1          40          0 NO  17-JUN-15 LGWR: Standby redo logfile selected for thread 1 sequence 22 for destination LOG _ARCHIVE_DEST_2
Log Transport Services   Control                0          41          0 YES 17-JUN-15 LGWR: Beginning to archive log 1 thread 1 sequence 22
Log Transport Services   Control                0          42          0 YES 17-JUN-15 ARC2: Beginning to archive thread 1 sequence 21 (885855-886131)
Log Transport Services   Control                0          43          0 YES 17-JUN-15 ARC2: Completed archiving thread 1 sequence 21 (885855-886131)
Log Transport Services   Control                0          44          0 YES 18-JUN-15 LGWR: Completed archiving log 1 thread 1 sequence 22
Log Transport Services   Warning                1          45          0 NO  18-JUN-15 LGWR: Standby redo logfile selected for thread 1 sequence 23 for destination LOG _ARCHIVE_DEST_2
Log Transport Services   Control                0          46          0 YES 18-JUN-15 LGWR: Beginning to archive log 2 thread 1 sequence 23
Log Transport Services   Control                0          47          0 YES 18-JUN-15 ARC3: Beginning to archive thread 1 sequence 22 (886131-927736)
Log Transport Services   Control                0          48          0 YES 18-JUN-15 ARC3: Completed archiving thread 1 sequence 22 (886131-927736)
Log Transport Services   Control                0          49          0 YES 18-JUN-15 LGWR: Completed archiving log 2 thread 1 sequence 23
Log Transport Services   Warning                1          50          0 NO  18-JUN-15 LGWR: Standby redo logfile selected for thread 1 sequence 24 for destination LOG _ARCHIVE_DEST_2
Log Transport Services   Control                0          51          0 YES 18-JUN-15 LGWR: Beginning to archive log 3 thread 1 sequence 24
Log Transport Services   Control                0          52          0 YES 18-JUN-15 ARC0: Beginning to archive thread 1 sequence 23 (927736-961263)
Log Transport Services   Control                0          53          0 YES 18-JUN-15 ARC0: Completed archiving thread 1 sequence 23 (927736-961263)

53 rows selected.

 

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

 

SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

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

DATABASE_ROLE    PROTECTION_MODE      PROTECTION_LEVEL     OPEN_MODE
---------------- -------------------- -------------------- --------------------
PHYSICAL STANDBY MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  READ ONLY

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

PROCESS   CLIENT_P  SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH      ARCH              0 CONNECTED
ARCH      ARCH              0 CONNECTED
ARCH      ARCH              0 CONNECTED
ARCH      ARCH             26 CLOSING
RFS       ARCH              0 IDLE
RFS       UNKNOWN           0 IDLE
RFS       LGWR             27 IDLE

7 rows selected.

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

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

PROCESS   CLIENT_P  SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH      ARCH              0 CONNECTED
ARCH      ARCH              0 CONNECTED
ARCH      ARCH              0 CONNECTED
ARCH      ARCH             26 CLOSING
RFS       ARCH              0 IDLE
RFS       UNKNOWN           0 IDLE
RFS       LGWR             27 IDLE
MRP0      N/A              27 APPLYING_LOG

8 rows selected.

 

13、解锁scott用户,添加数据,验证数据是否能同步:

 

#在primary端创建解锁scott用户并创建测试表,插入10000行数据
#primary 端操作如下内容
SQL> set line 200
SQL> select username,default_tablespace,account_status from dba_users where username='SCOTT';

USERNAME                       DEFAULT_TABLESPACE             ACCOUNT_STATUS
------------------------------ ------------------------------ --------------------------------
SCOTT                          USERS                          EXPIRED & LOCKED

SQL> alter user scott account unlock;

User altered.

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


Changing password for scott
New password: 
Retype new password: 
Password changed
Connected.

SQL> show user
USER is "SCOTT"
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BONUS                          TABLE
DEPT                           TABLE
EMP                            TABLE
SALGRADE                       TABLE

SQL> 

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

Table created.

SQL>  begin
  2  for i in 1..10000 loop
  3  insert into test001 values (1,'ww');
  4  end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

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

SQL> select * from tab;   

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BONUS                          TABLE
DEPT                           TABLE
EMP                            TABLE
SALGRADE                       TABLE
TEST001                        TABLE

SQL> select count(*) from test001;

  COUNT(*)
----------
     10000

SQL> 

 


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

 

 

 

 

四、ADG三种模式切换及介绍

 

 

 

14、#ADG有三种(PROTECTION|AVAILABILITY|PERFORMANCE)模式,具体参考:探索Oracle11gR2 之  DataGuard_03   三种保护模式

--primary操作步骤也就是命令之差:
SQL> select database_role,protection_mode,protection_level from v$database;    ----当前为最大性能

DATABASE_ROLE    PROTECTION_MODE      PROTECTION_LEVEL
---------------- -------------------- --------------------
PRIMARY          MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

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

Database altered.

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

DATABASE_ROLE    PROTECTION_MODE      PROTECTION_LEVEL
---------------- -------------------- --------------------
PRIMARY          MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

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

Database altered.

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

DATABASE_ROLE    PROTECTION_MODE      PROTECTION_LEVEL
---------------- -------------------- --------------------
PRIMARY          MAXIMUM PROTECTION   MAXIMUM PROTECTION

SQL>


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

DATABASE_ROLE    PROTECTION_MODE      PROTECTION_LEVEL
---------------- -------------------- --------------------
PHYSICAL STANDBY MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

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

Database altered.

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

DATABASE_ROLE    PROTECTION_MODE      PROTECTION_LEVEL
---------------- -------------------- --------------------
PHYSICAL STANDBY MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY

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


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

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1188511744 bytes
Fixed Size                  1364228 bytes
Variable Size             754978556 bytes
Database Buffers          419430400 bytes
Redo Buffers               12738560 bytes
Database mounted.

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

Database altered.

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

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

DATABASE_ROLE    PROTECTION_MODE      PROTECTION_LEVEL
---------------- -------------------- --------------------
PHYSICAL STANDBY MAXIMUM PROTECTION   MAXIMUM PROTECTION

 

五、切换测试

 

15、ADG做(switchover)切换测试

--primary 做如下操作

SQL> alter database commit to switchover to physical standby;

Database altered.

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

SQL> startup  
ORACLE instance started.

Total System Global Area 1188511744 bytes
Fixed Size                  1364228 bytes
Variable Size             754978556 bytes
Database Buffers          419430400 bytes
Redo Buffers               12738560 bytes
Database mounted.
Database opened.

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

Database altered.

SQL>  select database_role,switchover_status from v$database;

DATABASE_ROLE    SWITCHOVER_STATUS
---------------- --------------------
PHYSICAL STANDBY TO PRIMARY

SQL> 

--standby 端做如下操作

SQL> alter database commit to switchover to primary;

Database altered.

SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 1188511744 bytes
Fixed Size                  1364228 bytes
Variable Size             754978556 bytes
Database Buffers          419430400 bytes
Redo Buffers               12738560 bytes
Database mounted.
Database opened.

SQL> alter system switch logfile;

System altered.

SQL>  select database_role,switchover_status from v$database;

DATABASE_ROLE    SWITCHOVER_STATUS
---------------- --------------------
PRIMARY          SESSIONS ACTIVE

SQL> 

 

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

 

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

OPEN_MODE
--------------------
READ ONLY WITH APPLY

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

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL> shutdown abort
ORACLE instance shut down.
SQL> 

--在standby端执行如下操作

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1188511744 bytes
Fixed Size                  1364228 bytes
Variable Size             754978556 bytes
Database Buffers          419430400 bytes
Redo Buffers               12738560 bytes
Database mounted.

SQL> alter system flush redo to 'pri';

System altered.

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

no rows selected

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

SQL> alter database recover managed standby database cancel;
Database altered.
 
 
SQL> alter database recover managed standby database finish;
Database altered
 
 
SQL> select open_mode, switchover_status from v$database;
OPEN_MODE            SWITCHOVER_STATUS
-------------------- --------------------
READ ONLY            TO PRIMARY

 

 

 

 

 

 类似资料: