Oracle11g dataguard配置

前言

DG的配置过程，参考了如下两篇文章：

http://www.cnblogs.com/tippoint/archive/2013/04/18/3029019.html

http://blog.itpub.net/29324876/viewspace-1246133/

主库IP:10.211.55.6

备库IP:10.211.55.7

安装之前使用如下命令同步主备库的时间

1	ntpdate -u 0.centos.pool.ntp.org

配置

判断DG是否已安装

SQL> select * from v$option where parameter = 'Oracle Data Guard';
PARAMETER               VALUE
-------------------     ---------
Oracle Data Guard       TRUE
SQL>

如果是true表示已经安装可以配置，否则需要安装相应组件。

设置主库为强制记录日志

默认情况下数据库操作会记录redo log，但是在一些特定的情况下可以使用nologging来不生成redo信息

表的批量INSERT（通过/+APPEND /提示使用“直接路径插入“。或采用SQL*Loader直接路径加载）。表数据不生成redo，但是

所有索引修改会生成redo，但是所有索引修改会生成redo（尽管表不生成日志，但这个表上的索引却会生成redo！）。

LOB操作（对大对象的更新不必生成日志）。
通过CREATE TABLE AS SELECT创建表
各种ALTER TABLE操作，如MOVE和SPLIT
在一些表迁移和表空间迁移中，可以使用alter table a nologging;或者alter tablespace snk nologging;在操作完成后再修改回logging状态。

这里需要多说一句，如果你使用nologging导入大批量数据，以后对这些数据的修改会在redo或者archive log中，但是基准的数据是没有的，所以一旦介质损坏是无法完全恢复的，必须在使用nologging完成切换回logging后，做一次全备或者0级备份。

强制记录日志

1
2
3

SQL> alter database force logging;
Database altered.
SQL>

检查状态(YES为强制)

SQL> select name,force_logging from v$database;
NAME                        FORCE_LOG
--------------------------- ---------
SRMCLOUD                    YES
SQL>

如果需要在主库添加或者删除数据文件时，这些文件也会在备份添加或删除，使用如下

1
2
3

SQL> alter system set standby_file_management = 'AUTO';
System altered.
SQL>

创建standby log files

从库使用standby log files来保存从主库接收到的重做日志。既然主要是从库在使用，那为什么需要在主库上也建立standby log files ? 原因主要由两个：一是主库可能转换为备库，而备库是需要有standby log files的二是如果主库建立了standby log files那备库会自动建立。

创建standby log files需要注意两点：

standby log files的大小和redo log files一样

一般而言， standbyredo 日志文件组数要比 primary 数据库的 online redo 日志文件组数至少多一个。推荐 standbyredo 日志组数量基于 primary 数据库的线程数(这里的线程数可以理解为 rac 结构中的 rac节点数)。有一个推荐的公式可以做参考：(每线程的日志组数+1)最大线程数
假设现在节点是1个，则=(3+1)1=4
如果是双节点则=(3+1)*2=8
这里我们创建4个standby logfile:

查询redo日志大小

SQL> select group#,bytes/1024/1024 as M from v$log;
    GROUP#          M
---------- ----------
         1        100
         2        100
         3        100
SQL>

这里是100M，三个

创建

不建议组号group#紧挨着redo，因为后续redo有可能调整，这里我们从建立从11到14的standby logfile

SQL> alter database add standby logfile group 11 '/home/oracle/opt/oracle/oradata/srmcloud/standby11.log' size 100M;
Database altered.
SQL> alter database add standby logfile group 12 '/home/oracle/opt/oracle/oradata/srmcloud/standby12.log' size 100M;
Database altered.
SQL> alter database add standby logfile group 13 '/home/oracle/opt/oracle/oradata/srmcloud/standby13.log' size 100M;
Database altered.
SQL> alter database add standby logfile group 14 '/home/oracle/opt/oracle/oradata/srmcloud/standby14.log' size 100M;
Database altered.
SQL>

创建密码文件并传输给备库

一般数据库默认就有密码文件，存放在$ORACLE_HOME/dbs/orapwSID 这里为orapwsrmcloud

如果没有

1	sql> orapwd file=$ORACLE_HOME/dbs/orapwsrmcloud password=oracle;

检查REMOTE_LOGIN_PASSWORDFILE值是否为 EXCLUSIVE：

1	sql> show parameter REMOTE_LOGIN_PASSWORDFILE;

如果值不是EXCLUSIVE，则：

1	sql> alter system set remote_login_passwordfile=exclusive scope=spfile;

如果存在或者创建完成，将密码文件传输到standby 库的对应目录，并授权

处理控制文件

查看控制文件位置

SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/home/oracle/opt/oracle/oradata/srmcloud/control01.ctl
/home/oracle/opt/oracle/oradata/srmcloud/control02.ctl
SQL>

生成standby控制文件

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1553305600 bytes
Fixed Size                  2253544 bytes
Variable Size            1426066712 bytes
Database Buffers          117440512 bytes
Redo Buffers                7544832 bytes
Database mounted.
SQL> alter database create standby controlfile as '/tmp/standby_control01.ctl';
Database altered.
SQL> alter database open;
Database altered.
SQL>

然后在备库建立对应的目录,并授权

1 2	[oracle@srmclouddg software]$ mkdir -p /home/oracle/opt/oracle/oradata/srmcloud/ [oracle@srmclouddg software]$ chown oracle:oinstall /home/oracle/opt/oracle/oradata/srmcloud

拷贝主库的控制文件到备库

db_name和db_unique_name

默认db_name和db_unique_name和实例名是一致的，这里是srmcloud。需要注意在DG中主库和从库的db_unique_name是不能一致的，需要区分开的。这里我们设置主库的db_unique_name为srmcloud，从库为srmclouddg。

查看db_unique_name

SQL> show parameter db_unique_name
NAME             TYPE              VALUE
--------------   ---------------   ------------------  
db_unique_name   string            srmcloud
SQL>

设置db_unique_name

1
2
3

SQL> alter system set db_unique_name=srmcloud scope=spfile;
System altered.
SQL>

注意虽然默认db_unique_name和db_name是一致的，但是需要显式设置，否则在spfile中没有此参数

闪回

略

SQL*NET设置

配置主库监听(listener.ora)

虽然可以通过netca来进行配置，但是除了这个默认的外，我们还需要一个静态注册SID_LIST_LISTENER，如果没有此从参数，而且dataguard启动顺序不正确，主库会报PING[ARC1]:Heartbeat failed to connect to standby ‘*‘.Error is 12514导致归档无法完成
配置如下:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = srmcloud)(PORT = 1521))
    )
  )
SID_LIST_LISTENER=
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = srmcloud)
      (ORACLE_HOME = /home/oracle/opt/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = srmcloud)
    )
  )

配置tns

配置如下：

SRMCLOUD =
   (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = 10.211.55.6)(PORT = 1521))
     )
     (CONNECT_DATA =
       (SERVICE_NAME = srmcloud)
     )
   )
SRMCLOUDDG =
   (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = 10.211.55.7)(PORT = 1521))
     )
     (CONNECT_DATA =
       (SERVICE_NAME = srmclouddg)
     )
   )

传输到备库并修改listener.ora

如下:

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = srmclouddg)(PORT = 1521))
    )
  )
SID_LIST_LISTENER=
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = srmclouddg)
      (ORACLE_HOME = /home/oracle/opt/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = srmcloud)
    )
  )

日志传输配置

查看是否启用归档

SQL> Archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     35
Current log sequence           37
SQL>

启用归档并设置归档日志路径

SQL> alter system set log_archive_dest_1='LOCATION=/home/oracle/opt/oracle/archive1 valid_for=(all_logfiles,primary_role) db_unique_name=srmcloud' scope=spfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1553305600 bytes
Fixed Size                  2253544 bytes
Variable Size            1426066712 bytes
Database Buffers          117440512 bytes
Redo Buffers                7544832 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL>

配置归档日志到备份库

1
2
3

SQL> alter system set log_archive_dest_2='SERVICE=srmclouddg lgwr sync valid_for=(online_logfile,primary_role) db_unique_name=srmclouddg';
System altered.
SQL>

要注意STANDBY_ARCHIVE_DEST 参数不需要，已经被官方弃用。设置此参数后启动数据库，只会报 ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance 错。

配置FAL_SERVER

这个参数指定当日志传输出现问题时，备库到哪里去找缺少的归档日志。它用在备库接收的到的重做日志间有缺口的时候。这种情况会发生在日志传输出现中断时，比如你需要对备库进行维护操作。在备库维护期间，没有日志传输过来，这时缺口就出现了。设置了这个参数，备库就会主动去寻找那些缺少的日志，并要求主库进行传输。
你是主库,就填写:fal_server=从库
从库上就反过来:fal_server=主库
注意:FAL_CLIENT在11g中已经废弃，虽然可以配置但是已经不起作用了

1 2	SQL> alter system set FAL_SERVER=srmclouddg; System altered.

Data Guard 配置里的另外一个库的名字

1 2	SQL> alter system set log_archive_config = 'dg_config=(srmcloud,srmclouddg)'; System altered.

1	SQL> create pfile from spfile;

手工修改pfile，如下:

srmcloud.__db_cache_size=687865856
srmcloud.__java_pool_size=16777216
srmcloud.__large_pool_size=33554432
srmcloud.__oracle_base='/home/oracle/opt/oracle'#ORACLE_BASE set from environment
srmcloud.__pga_aggregate_target=469762048
srmcloud.__sga_target=1090519040
srmcloud.__shared_io_pool_size=0
srmcloud.__shared_pool_size=335544320
srmcloud.__streams_pool_size=0
*.audit_file_dest='/home/oracle/opt/oracle/admin/srmcloud/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/home/oracle/opt/oracle/oradata/srmcloud/control01.ctl','/home/oracle/opt/oracle/oradata/srmcloud/control02.ctl'
*.db_block_size=32768
*.db_domain=''
*.db_name='srmcloud'
*.db_recovery_file_dest='/home/oracle/opt/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=5218762752
*.db_unique_name='SRMCLOUD'
*.diagnostic_dest='/home/oracle/opt/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=srmcloudXDB)'
*.fal_server='SRMCLOUDDG'
*.job_queue_processes=1000
*.log_archive_config='dg_config=(srmcloud,srmclouddg)'
*.log_archive_dest_1='LOCATION=/home/oracle/opt/oracle/archive1 valid_for=(all_logfiles,primary_role) db_unique_name=srmcloud'
*.log_archive_dest_2='SERVICE=srmclouddg lgwr sync valid_for=(online_logfile,primary_role) db_unique_name=srmclouddg'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.log_archive_format='srmcloud_%t_%s_%r.dbf'
*.memory_target=1546649600
*.open_cursors=300
*.processes=1500
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=1655
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> create spfile from pfile;
File created.
SQL> startup;
ORACLE instance started.
Total System Global Area 1553305600 bytes
Fixed Size                  2253544 bytes
Variable Size            1426066712 bytes
Database Buffers          117440512 bytes
Redo Buffers                7544832 bytes
Database mounted.
Database opened.
SQL>

传输pfile到备库并修改

只需修改如下项即可:

*.db_name='srmcloud'
*.db_unique_name='SRMCLOUDDG'
*.fal_server='SRMCLOUD'
*.log_archive_config='dg_config=(srmclouddg,srmcloud)'
*.log_archive_dest_1='LOCATION=/home/oracle/opt/oracle/archive1 valid_for=(all_logfiles,primary_role) db_unique_name=srmclouddg'
*.log_archive_dest_2='SERVICE=srmcloud lgwr sync valid_for=(online_logfile,primary_role) db_unique_name=srmcloud'

建立相关目录

[root@srmclouddg srmcloud]# mkdir -p  /home/oracle/opt/oracle/admin/srmcloud/
[root@srmclouddg srmcloud]# cd  /home/oracle/opt/oracle/admin/srmcloud/
[root@srmclouddg srmcloud]# pwd
/home/oracle/opt/oracle/admin/srmcloud
[root@srmclouddg srmcloud]# mkdir adump dpdump pfile
[root@srmclouddg srmcloud]# ll
total 12
drwxr-xr-x 2 root root 4096 Dec 28 21:38 adump
drwxr-xr-x 2 root root 4096 Dec 28 21:38 dpdump
drwxr-xr-x 2 root root 4096 Dec 28 21:38 pfile
[root@srmclouddg srmcloud]# chown -R  oracle:oinstall /home/oracle/opt/oracle/admin

1	[oracle@srmclouddg dbs]$ mkdir -p /home/oracle/opt/oracle/fast_recovery_area

使用pfile启动备库到nomount

SQL> startup nomount pfile='/home/oracle/opt/oracle/product/11.2.0/dbhome_1/dbs/initsrmcloud.ora';
ORACLE instance started.
Total System Global Area 1553305600 bytes
Fixed Size                  2253544 bytes
Variable Size            1426066712 bytes
Database Buffers          117440512 bytes
Redo Buffers                7544832 bytes
SQL>

注意：要启动备库监听

Duplicate复制主库到备库

[oracle@srmcloud ~]$ rman target sys/handhand@SRMCLOUD auxiliary sys/handhand@SRMCLOUDDG
Recovery Manager: Release 11.2.0.4.0 - Production on Thu Dec 28 23:11:34 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: SRMCLOUD (DBID=3572656870)
connected to auxiliary database: SRMCLOUD (not mounted)
RMAN> duplicate target database for standby from active database spfile set db_unique_name 'SRMCLOUD' nofilenamecheck;
Starting Duplicate Db at 28-DEC-17
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=10 device type=DISK
contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/home/oracle/opt/oracle/product/11.2.0/dbhome_1/dbs/orapwsrmcloud' auxiliary format
 '/home/oracle/opt/oracle/product/11.2.0/dbhome_1/dbs/orapwsrmcloud'   targetfile
 '/home/oracle/opt/oracle/product/11.2.0/dbhome_1/dbs/spfilesrmcloud.ora' auxiliary format
 '/home/oracle/opt/oracle/product/11.2.0/dbhome_1/dbs/spfilesrmcloud.ora'   ;
   sql clone "alter system set spfile= ''/home/oracle/opt/oracle/product/11.2.0/dbhome_1/dbs/spfilesrmcloud.ora''";
}
executing Memory Script
Starting backup at 28-DEC-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1157 device type=DISK
Finished backup at 28-DEC-17
sql statement: alter system set spfile= ''/home/oracle/opt/oracle/product/11.2.0/dbhome_1/dbs/spfilesrmcloud.ora''
contents of Memory Script:
{
   sql clone "alter system set  db_unique_name =
 ''SRMCLOUD'' comment=
 '''' scope=spfile";
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script
sql statement: alter system set  db_unique_name =  ''SRMCLOUD'' comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area    1553305600 bytes
Fixed Size                     2253544 bytes
Variable Size               1426066712 bytes
Database Buffers             117440512 bytes
Redo Buffers                   7544832 bytes
contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '/home/oracle/opt/oracle/oradata/srmcloud/control01.ctl';
   restore clone controlfile to  '/home/oracle/opt/oracle/oradata/srmcloud/control02.ctl' from
 '/home/oracle/opt/oracle/oradata/srmcloud/control01.ctl';
}
executing Memory Script
Starting backup at 28-DEC-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/home/oracle/opt/oracle/product/11.2.0/dbhome_1/dbs/snapcf_srmcloud.f tag=TAG20171228T231151 RECID=3 STAMP=963961911
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 28-DEC-17
Starting restore at 28-DEC-17
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=9 device type=DISK
channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 28-DEC-17
contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
RMAN-05538: WARNING: implicitly using DB_FILE_NAME_CONVERT
contents of Memory Script:
{   set newname for tempfile  1 to
 "/home/oracle/opt/oracle/oradata/srmcloud/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to
 "/home/oracle/opt/oracle/oradata/srmcloud/system01.dbf";
   set newname for datafile  2 to
 "/home/oracle/opt/oracle/oradata/srmcloud/sysaux01.dbf";
   set newname for datafile  3 to
 "/home/oracle/opt/oracle/oradata/srmcloud/undotbs01.dbf";
   set newname for datafile  4 to
 "/home/oracle/opt/oracle/oradata/srmcloud/users01.dbf";
   backup as copy reuse
   datafile  1 auxiliary format
 "/home/oracle/opt/oracle/oradata/srmcloud/system01.dbf"   datafile
 2 auxiliary format
 "/home/oracle/opt/oracle/oradata/srmcloud/sysaux01.dbf"   datafile
 3 auxiliary format
 "/home/oracle/opt/oracle/oradata/srmcloud/undotbs01.dbf"   datafile
 4 auxiliary format
 "/home/oracle/opt/oracle/oradata/srmcloud/users01.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /home/oracle/opt/oracle/oradata/srmcloud/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 28-DEC-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/home/oracle/opt/oracle/oradata/srmcloud/sysaux01.dbf
output file name=/home/oracle/opt/oracle/oradata/srmcloud/sysaux01.dbf tag=TAG20171228T231202
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/home/oracle/opt/oracle/oradata/srmcloud/undotbs01.dbf
output file name=/home/oracle/opt/oracle/oradata/srmcloud/undotbs01.dbf tag=TAG20171228T231202
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/home/oracle/opt/oracle/oradata/srmcloud/system01.dbf
output file name=/home/oracle/opt/oracle/oradata/srmcloud/system01.dbf tag=TAG20171228T231202
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/home/oracle/opt/oracle/oradata/srmcloud/users01.dbf
output file name=/home/oracle/opt/oracle/oradata/srmcloud/users01.dbf tag=TAG20171228T231202
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 28-DEC-17
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=3 STAMP=963961972 file name=/home/oracle/opt/oracle/oradata/srmcloud/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=4 STAMP=963961972 file name=/home/oracle/opt/oracle/oradata/srmcloud/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=963961972 file name=/home/oracle/opt/oracle/oradata/srmcloud/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=963961972 file name=/home/oracle/opt/oracle/oradata/srmcloud/users01.dbf
Finished Duplicate Db at 28-DEC-17
RMAN> quit

关闭，并使用pfile重启数据库，并创建spfile

SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount pfile='/home/oracle/opt/oracle/product/11.2.0/dbhome_1/dbs/initsrmcloud.ora';
ORACLE instance started.
Total System Global Area 1553305600 bytes
Fixed Size                  2253544 bytes
Variable Size            1426066712 bytes
Database Buffers          117440512 bytes
Redo Buffers                7544832 bytes
SQL> alter database mount standby database;
Database altered.
SQL> show parameter db_unique_name
NAME                                 TYPE
------------------------------------ ---------------------------------
VALUE
------------------------------
db_unique_name                       string
SRMCLOUDDG
SQL> create spfile from pfile;
File created.
SQL>

关闭数据库，使用spfile重启数据库

SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 1553305600 bytes
Fixed Size                  2253544 bytes
Variable Size            1426066712 bytes
Database Buffers          117440512 bytes
Redo Buffers                7544832 bytes
SQL> alter database mount standby database;
Database altered.
SQL> alter database open read only;
Database altered.
SQL>

查看日志

SQL> select SEQUENCE#,FIRST_TIME,NEXT_TIME ,APPLIED from v$archived_log order by 1;
 SEQUENCE# FIRST_TIME      NEXT_TIME       APPLIED
---------- --------------- --------------- ---------------------------
        40 28-DEC-17       28-DEC-17       NO
        41 28-DEC-17       28-DEC-17       NO
        42 28-DEC-17       28-DEC-17       NO
        43 28-DEC-17       28-DEC-17       NO
        44 28-DEC-17       28-DEC-17       NO
SQL>

已接收，但未应用

开启备库日志应用

SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL> select SEQUENCE#,FIRST_TIME,NEXT_TIME ,APPLIED from v$archived_log order by 1;
 SEQUENCE# FIRST_TIME      NEXT_TIME       APPLIED
---------- --------------- --------------- ---------------------------
        40 28-DEC-17       28-DEC-17       YES
        41 28-DEC-17       28-DEC-17       YES
        42 28-DEC-17       28-DEC-17       YES
        43 28-DEC-17       28-DEC-17       YES
        44 28-DEC-17       28-DEC-17       YES
SQL>

验证

数据传输验证

在主库创建用户

SQL>  create user test identified by test;
User created.
SQL> select username from dba_users where username='TEST';
USERNAME
--------------------------------------------------------------------------------
TEST

在备库查询TEST用户是否存在

SQL> select username from dba_users where username='TEST';
USERNAME
--------------------------------------------------------------------------------
TEST
SQL>

dg数据传输正常

主备切换验证

#####主库切换为备库

SQL> alter database commit to switchover to physical standby with session shutdown;
Database altered.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1553305600 bytes
Fixed Size                  2253544 bytes
Variable Size            1426066712 bytes
Database Buffers          117440512 bytes
Redo Buffers                7544832 bytes
Database mounted.
SQL> select database_role from v$database;
DATABASE_ROLE
------------------------------------------------
PHYSICAL STANDBY
SQL>

备库切换为主库

SQL> select database_role from v$database;
DATABASE_ROLE
------------------------------------------------
PHYSICAL STANDBY
SQL> select open_mode from v$database;
OPEN_MODE
------------------------------------------------------------
READ ONLY WITH APPLY
SQL> alter database commit to switchover to primary with session shutdown;
Database altered.
SQL> select database_role from v$database;
DATABASE_ROLE
------------------------------------------------
PRIMARY
SQL> select open_mode from v$database;
OPEN_MODE
------------------------------------------------------------
MOUNTED
SQL> alter database open;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
------------------------------------------------------------
READ WRITE
SQL>

启动原主库(现在为备库)为read only模式，并启用日志自动应用

SQL> alter database open read only;
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL>

至此，DG配置完成

管理

dataguard启动关闭顺序

监听

先启从库再起主库

1	lsnrctl start

启动

先起从库

sql>startup nomount
sql>alter database mount standby database;
sql>alter database open read only;
sql>alter database recover managed standby database using current logfile disconnect from session;

再启主库

1	sql>startup

关闭(和开启正好相反)

先关主库：

1	sql>shutdown immediate

再关从库：

1 2	sql>alter database recover managed standby database cancel; sql>shutdown immediate;

后续

介绍一篇dg文章，写的比我好

https://community.oracle.com/docs/DOC-1007058