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

最简单的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,dg)' 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
时间： 2024-07-31 10:10:04

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

11g Active DataGuard初探

原本dataguard中日志应用和数据库只读查询是一个互斥的关系,两者不能并存.如果需要应用日志,则数据库只能在Mount状态下 使用recover managed standby database disconnect from session来不断地从后台进行日志应用. 如果想查看备库中的数据情况,则只能使用recover managed standby database cancel来取消日志应用,然后把数据库启动到read only 状态下.这种情况从道理上也讲也是有理有据,但是终归还是

曲折的10g,11g中EM的安装配置过程

今天在本地搭了一套oracle环境,首先安装数据库的时候顺带了EM,结果安装好之后想修改监听器的端口,把原本15521的端口换成别的,结果在目录中修改了几个参数文件,EM竟然直接起不来了.最后自己分析了,其实有好几种思路来完成密码的修改. 一种是直接删除EM,然后重建,可以使用emca -deconfig dbcontrol db -repos drop来完成删除,然后通过emca -config dbcontrol db -repos create来重新创建EM, 还有一种方式可以通过图形界面

Oracle 11g Dataguard物理备库配置(二) Active Dataguard测试

在Oracle 11g之前,物理备库(physical Standby)在应用redo的时候,数据库需要处于mount状态.从11g开始,应用redo的时候,物理备库可以处于read-only模式,这就称为Active Data Guard,这种状态可以实现实时查询功能. 1. 备库上操作 1) 查看备库当前状态 mount SQL> select open_mode,database_role,db_unique_name from v$database; OPEN_MODE        

奕新集团RAC 11g 生产库环境搭建（无图）

                            奕新集团RAC 11g 生产库环境搭建   说明图片有50张,上传太麻烦.我放在百度云盘提供大家下载 项目规划: 操作系统 oracle-5.9.iso 数据库版本 oracle11g-11.2.0.3.iso 大     小 80G /         30G /opt      50G 存储 注意:  11g中决策盘和仲裁盘在中是一样的,10g决策和仲裁盘必须分开    决策盘.仲裁盘 2G *2   ocr1 ocr2 (grid)

ORACLE Active dataguard 一个latch: row cache objects BUG

在Active dataguard遇到latch: row cache objects 查询如下语句 select a.SAMPLE_TIME,a.SQL_ID,a.EVENT,a.P1TEXT,a.P1,a.P2TEXT,a.P2,a.P3TEXT,a.P3,  b.f2   from v$active_session_history a,     (select max(b.SQL_TEXT) f2,sql_id from  v$sql b group by sql_id ) b  wher

11g包dbms_parallel_execute在海量数据处理过程中的应用

11g包dbms_parallel_execute在海量数据处理过程中的应用 1.1  BLOG文档结构图       1.2  前言部分   1.2.1  导读 各位技术爱好者,看完本文后,你可以掌握如下的技能,也可以学到一些其它你所不知道的知识,~O(∩_∩)O~: ① 11g包dbms_parallel_execute在海量数据处理过程中的应用   注意:本篇BLOG中代码部分需要特别关注的地方我都用黄色背景和红色字体来表示,比如下边的例子中,thread 1的最大归档日志号为33,thr

LNAMP PHP多人开发环境搭建配置教程

 经常听到团队的小伙说:这个版本的扩展真难找,在本地运行正常的代码,上线之后却运行异常-诸如此类的问题大多是因为系统差异.扩展缺失.版本差异引起的.要解决此类问题搭建一个和线上一致的多人开发环境即可解决.     思路:搭建一台本地开发服务器,开发服务器和线上服务器环境保持一致,用samba服务映射到本地电脑磁盘,团队中所有人的开发代码都存储在开发机上,所有人的代码都是在开发机器上运行:团队成员无需再单独搭建开发环境,只需要挂载远程目录到本地磁盘,加上DNS解析,开发机上apache根据域名绑定

优酷数据架构师：搭建Hadoop平台三步走

文章讲的是优酷数据架构师:搭建Hadoop平台三步走,2013年11月22-23日,作为国内唯一专注于Hadoop技术与应用分享的大规模行业盛会,2013 Hadoop中国技术峰会(China Hadoop Summit 2013)将于北京福朋喜来登集团酒店隆重举行.届时,来自国内外各行业领域的近千名CIO.CTO.架构师.IT经理.咨询顾问.工程师.Hadoop技术爱好者,以及从事Hadoop研究与推广的IT厂商和技术专家将共襄盛举. ▲点击进入Haoop中国技术峰会 Haoop中国技术峰会由

LNAMP/LAMP/(多人)开发环境搭建配置教程

 经常听到团队的小伙伴说:这个版本的扩展真难找,在本地运行正常的代码,上线之后却运行异常-诸如此类的问题大多是因为系统差异.扩展缺失.版本差异引起的.要解决此类问题搭建一个和线上一致的多人开发环境即可解决.     思路:搭建一台本地开发服务器,开发服务器和线上服务器环境保持一致,用samba服务映射到本地电脑磁盘,团队中所有人的开发代码都存储在开发机上,所有人的代码都是在开发机器上运行:团队成员无需再单独搭建开发环境,只需要挂载远程目录到本地磁盘,加上DNS解析,开发机上apache根据域名绑