Oralce 11g R2 Dataguard数据库配置2014100302

#修正之前档案中许多细节错误

oracle 软件环境
oracle version
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
Linux  Version
CentOS release 5.5 (Final)

1.主备库都要安装好oracle及database,这样可以免除一些不必要麻烦
2.配置主备库配置好hosts,DNS，监听配置，并且确认两边格式测试是否通过
3.主库配置相关参数
4.添加standby redolog
5.配置主库pfile dg相关参数
6.备份数据库
7.主库传送数据库备份及数据库密码文件到备库
8.备库配置pile相关参数
9.备库恢复主库备份
10.并将数据置dg模式
11.oracle验证dg配置是否成功

==========================================================
#配置主库oracle主机解析
[root@pd ~]# vi /etc/hosts                                            
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1                localhost.localdomain localhost
::1             localhost6.localdomain6 localhost6
192.168.14.112   pd
192.168.14.111   st
==========================================================
配置主库DNS
[root@pd ~]# vi /etc/resolv.conf
nameserver 192.168.14.112
==========================================================
重新启动主库
[root@pd ~]# reboot
=========================================================
#备库rman文件备份位置
root帐户创建
[root@pd u01]# mkdir -p /u01/backup/
[root@pd ~]# chown -R oracle:oinstall /u01/backup/
[root@pd ~]# chmod 775 /u01/backup/

==========================================================
#主库配置监听
[oracle@pd ~]$ vi  /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/10.0.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = pdb)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = pdb)
    )
  )


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


ADR_BASE_LISTENER = /u01/app/oracle
==========================================================================
#主库TNS配置
[oracle@pd ~]$ vi  /u01/app/oracle/product/10.0.0/db_1/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.0.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

PD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.14.112)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb)
    )
  )



ST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.14.111)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb)
    )
  )

=========================================================================
主库

[oracle@pd admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Dec 12 14:55:24 2014

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

Connected to an idle instance.

SQL> startup mount;

========================================================================
#指定主库归档文件所处位置
SQL> alter system set log_archive_dest_1='location=/u01/archive/' scope=spfile;
System altered.
=========================================================================
#重启oracle，装其置为mount状态
SQL> shutdown immediate;
=========================================================================
#配置oracle为归档
SQL> alter database archivelog;
Database altered.
==========================================================================
#强制oracle操作为归档
SQL> alter database force logging;
Database altered.
===========================================================================
#打开oracle
SQL> alter database open;

==========================================================================
#查看主库系统是否处于强制日志模式
SQL> select FORCE_LOGGING from v$database;      

FOR
---
YES

============================================================================
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
=============================================================================
SQL> startup
ORACLE instance started.

Total System Global Area  839282688 bytes
Fixed Size                  2233000 bytes
Variable Size             507514200 bytes
Database Buffers          322961408 bytes
Redo Buffers                6574080 bytes
Database mounted.
Database opened.

==============================================================================
SQL> archive log list;
Database log mode           Archive Mode
Automatic archival           Enabled
Archive destination           /u01/archive/
Oldest online log sequence     5
Next log sequence to archive   7
Current log sequence           7

================================================================================
#在主库添加standby redo log
SQL> alter database add standby logfile group 4 '/u01/app/oracle/oradata/pdb/standbyredo04.log' size 100m;
SQL> alter database add standby logfile group 5 '/u01/app/oracle/oradata/pdb/standbyredo05.log' size 100m;
SQL> alter database add standby logfile group 6 '/u01/app/oracle/oradata/pdb/standbyredo06.log' size 100m;
SQL> alter database add standby logfile group 7 '/u01/app/oracle/oradata/pdb/standbyredo07.log' size 100m;


SQL> col member for a50
SQL> select group#,type,member from v$logfile;

    GROUP# TYPE    MEMBER
---------- ------- --------------------------------------------------
         3 ONLINE  /u01/app/oracle/oradata/pdb/redo03.log
         2 ONLINE  /u01/app/oracle/oradata/pdb/redo02.log
         1 ONLINE  /u01/app/oracle/oradata/pdb/redo01.log
         4 STANDBY /u01/app/oracle/oradata/pdb/standbyredo04.log
         5 STANDBY /u01/app/oracle/oradata/pdb/standbyredo05.log
         6 STANDBY /u01/app/oracle/oradata/pdb/standbyredo06.log
         7 STANDBY /u01/app/oracle/oradata/pdb/standbyredo07.log

7 rows selected.

====================================================================================
主库
#配置主库相关参数
#先将主库spfile配置pfile

SQL> create pfile from spfile;
File created.

[oracle@pd dbs]$ vi /u01/app/oracle/product/11.2.0/db_1/dbs/initpdb.ora
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/pd/control01.ctl','/u01/app/oracle/flash_recovery_area/pd/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='pd'                                      
#指定数据库的名称，主备库的这2个参数值必须相同。             
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=pdXDB)'
#设置成归档
*.log_archive_dest_1='location=/u01/archive/'   
#设置归档文件格式。                 
*.log_archive_format='%t_%s_%r.dbf'                              
*.memory_target=620756992
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
#主库需要添加的参数
# add primary for dg
#主备库都指定为不同的名称， 这个名称指定以后就不会改变，即使主备库的角色发生转换
db_unique_name = 'pd'    
#在DG 环境中，必须指定这个参数，该参数的DG_CONFIG 属性必须包含所有节点的DB_UNIQUE_NAME 名称，每个名称使用逗号隔开。                             
log_archive_config='dg_config=(pd,st)'  
#主库的归档日志存放路径log_archive_dest_1='location=/u01/archive/'              
log_archive_dest_1='location=/u01/archive/ valid_for=(all_logfiles,all_roles) db_unique_name=pd'
#备库通过lgwr进程，用sync的方式进行日志传递给备库。
log_archive_dest_2='service=st reopen=120 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=st'
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
standby_file_management='auto'
fal_server='st'
log_file_name_convert='/u01/app/oracle/oradata/pdb','/u01/app/oracle/oradata/pdb'
db_file_name_convert='/u01/app/oracle/oradata/pdb','/u01/app/oracle/oradata/pdb'
x: #保存相关参数

#关闭oracle主库重新生成spfile文件
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

#mount方式加载pfile参数
SQL> startup mount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initpdb.ora';
ORACLE instance started.
Total System Global Area  839282688 bytes
Fixed Size                  2233000 bytes
Variable Size             511708504 bytes
Database Buffers          318767104 bytes
Redo Buffers                6574080 bytes
Database mounted.



#将pfile参数配置成spfile
SQL> create spfile from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initpdb.ora';
File created.


#检查参数配置是否正确
SQL> alter database open;
Database altered.

=================================================================================
#重置oracle密码,这里是测试库可以将密码文件重置OK，但生产库就不要这么玩了，直接复制它到备库就可以了。首提是你一定要清楚它的密码是多少
[oracle@pd dbs]$ cd /u01/app/oracle/product/11.2.0/db_1/dbs/
[oracle@pd dbs]$ ls
hc_pdb.dat  init.ora  initpdb.ora  lkPD  lkPDB  orapwpdb  snapcf_pdb.f  spfilepdb.ora

[oracle@pd dbs]$ mv orapwpdb orapwpdb1
[oracle@pdb dbs]$ orapwd file=orapwpdb password=oracle entries=10

=================================================================================
#切换归档
SQL> ALTER SYSTEM SWITCH LOGFILE;
主库

[oracle@pd admin]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Dec 11 12:19:37 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: PDB (DBID=3278846531)
RMAN>
RMAN> run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
sql 'alter system archive log current';
BACKUP FORMAT '/u01/backup/db_%U_%T' skip inaccessible filesperset 5 database;
sql 'alter system archive log current';
BACKUP FORMAT '/u01/backup/db_%U_%T' skip inaccessible filesperset 5 archivelog all delete input;
backup current controlfile for standby format='/u01/backup/control_%U';
release channel c2;                   
release channel c1;
}
--------------------------------------------------------------------------------------------------
Starting backup at 11-DEC-14
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including standby control file in backup set
channel c1: starting piece 1 at 11-DEC-14
channel c1: finished piece 1 at 11-DEC-14
piece handle=/u01/backup/control_0hppt6so_1_1 tag=TAG20141211T123536 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 11-DEC-14
released channel: c2
released channel: c1


==========================================================
[oracle@pdb dbs]$scp -r backup oracle@192.168.14.111:/u01/
//备份好以后将其复制到备库中，rman备份容易如果超过50G，建议使用移动存储将rman备份复制到备库中.

[oracle@pd u01]$ scp -r orapwpdb oracle@192.168.14.111:/u01/app/oracle/product/11.2.0/db_1/dbs/
 因为是测试库也没有多大，我就用scp复制过去的

==============================================================================
==============================================================================
==============================================================================
#配置备库
#配置主机解析
[root@st ~]# vi /etc/hosts

# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1       localhost
::1             localhost6.localdomain6 localhost6
192.168.14.111  st
192.168.14.112  pd
==============================================================================
#配置DNS解析
[root@st ~]# vi /etc/resolv.conf
nameserver 192.168.14.112
==============================================================================

#配置监控
[oracle@st ~]$ vi  /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = pdb)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = pdb)
    )
  )

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

ADR_BASE_LISTENER = /u01/app/oracle
================================================================================
[oracle@st admin]$ vi /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

PD =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.14.112)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb)
    )
  )

ST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.14.111)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb)
    )
  )

=========================================================================
#检查主备库监听配置情况

[oracle@st admin]$ tnsping st

TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 12-DEC-2014 16:04:34

Copyright (c) 1997, 2011, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.14.111)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdb)))
OK (0 msec)
===========================================================================
[oracle@st admin]$ tnsping pd

TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 12-DEC-2014 16:04:43

Copyright (c) 1997, 2011, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.14.112)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = pdb)))
OK (90 msec)
=========================================================================
完配监听配置后，查看备库情况
[oracle@st admin]$ sqlplus / as sysdba
SQL> startup
ORACLE instance started.

Total System Global Area  839282688 bytes
Fixed Size            2233000 bytes
Variable Size          591400280 bytes
Database Buffers      239075328 bytes
Redo Buffers            6574080 bytes
Database mounted.
Database opened.
#我只将备库置为打开状态，确认情况OK，然后请关闭

SQL> shutdown immediate;

=========================================================================
#开始配置oracle adg备库，将库置nomount状态
SQL> startup nomount;
ORACLE instance started.

Total System Global Area  839282688 bytes
Fixed Size                  2233000 bytes
Variable Size             511708504 bytes
Database Buffers          318767104 bytes
Redo Buffers                6574080 bytes
===========================================================================
#配置备库pfile相关参数
SQL> create pfile from spfile;
File created.

[oracle@st ~]$ cd /u01/app/oracle/product/11.2.0/db_1/dbs/
[oracle@st dbs]$ ls
hc_pdb.dat  init.ora  initpdb.ora  lkPDB  orapwpdb  spfilepdb.ora

#备库中配置initpdb.ora参数
#修改备库的pfile文件并生成spile文件，并且启动oracle

[oracle@st dbs]$ vi /u01/app/oracle/product/11.0.0/db_1/dbs/initpdb.ora
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/pd/control01.ctl','/u01/app/oracle/flash_recovery_area/pd/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='pdb'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=pdXDB)'
*.log_archive_dest_1='location=/u01/archive/'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=620756992
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'


# add standby for dg
db_unique_name = 'st'
log_archive_config='dg_config=(pd,st)'
log_archive_dest_1='location=/u01/archive/ valid_for=(all_logfiles,all_roles) db_unique_name=st'
log_archive_dest_2='service=pd reopen=120 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=pd'
log_archive_dest_state_1=enable
log_archive_dest_state_2=enable
standby_file_management='auto'
fal_server='pd'
log_file_name_convert='/u01/app/oracle/oradata/pdb','/u01/app/oracle/oradata/pdb'
db_file_name_convert='/u01/app/oracle/oradata/pdb','/u01/app/oracle/oradata/pdb'
===========================================================================
#先关闭oracle
SQL> shutdown immediate;

#利用pfile参数将备库启动至mount状态,检查pfile参数是否正确
SQL> startup mount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initpdb.ora';
ORACLE instance started.

Total System Global Area  839282688 bytes
Fixed Size                  2233000 bytes
Variable Size             511708504 bytes
Database Buffers          318767104 bytes
Redo Buffers                6574080 bytes
Database mounted.


#将备库中的pfile参数修改成spfile
SQL> create spfile from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initpdb.ora';
File created.

#关闭备库数据库
SQL> shutdown immediate;

#将备库置为nomount状态
SQL>startup nomount;
===========================================================================
#查看备库rman备份文件权限是否正确以防因为数据权限错误导致恢复失败
[oracle@st u01]$ ls -lh
total 16K
drwxrwxr-x 4 oracle oinstall 4.0K Aug 17 19:53 app
drwxrwxr-x 2 oracle oinstall 4.0K Aug 17 19:09 archive
drwxrwxr-x 2 oracle oinstall 4.0K Dec 11 12:44 backup
drwxr-xr-x 4 oracle oinstall 4.0K Aug 17 19:44 soft

[oracle@st /]$ cd /u01/backup
[oracle@st backup]$ ls
control_0jpk2ld4_1_1      db_0epk2lba_1_1_20141002  db_0hpk2ld0_1_1_20141002
db_0cpk2l8t_1_1_20141002  db_0fpk2lbf_1_1_20141002  db_0ipk2ld2_1_1_20141002
db_0dpk2l8t_1_1_20141002  db_0gpk2ld0_1_1_20141002

=============================================================================
#恢复备库的控制文件
[oracle@st backup]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Oct 2 18:50:19 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: PDB (not mounted)

RMAN> restore standby controlfile from '/u01/backup/control_0tpk4gsv_1_1';

Starting restore at 02-OCT-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/u01/app/oracle/oradata/pdb/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/pdb/control02.ctl
Finished restore at 02-OCT-14

RMAN>
=====================================================================
#现在将备库置为mount状态
SQL> alter database mount;
Database altered.

#现在用rman开始做全库恢复
RMAN> restore database;
======================================================================
#下面这一段是rman整库恢复时的记录，观察记录为成功搭建DG提供基础条件
Starting restore at 11-DEC-14
released channel: ORA_DISK_1
Starting implicit crosscheck backup at 11-DEC-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK
Crosschecked 6 objects
Finished implicit crosscheck backup at 11-DEC-14

Starting implicit crosscheck copy at 11-DEC-14
using channel ORA_DISK_1
Finished implicit crosscheck copy at 11-DEC-14

searching for all files in the recovery area
cataloging files...
no files cataloged

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/pdb/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/pdb/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/pdb/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/backup/db_0cppt6o7_1_1_20141211
channel ORA_DISK_1: piece handle=/u01/backup/db_0cppt6o7_1_1_20141211 tag=TAG20141211T123310
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:55
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/pdb/system01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/pdb/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/backup/db_0bppt6o6_1_1_20141211
channel ORA_DISK_1: piece handle=/u01/backup/db_0bppt6o6_1_1_20141211 tag=TAG20141211T123310
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:45
Finished restore at 11-DEC-14


===========================================================================
#备库执行
SQL>shutdown immediate;
SQL>startup nomount;
============================================================================
#将备库置于standby模式
SQL> alter database mount standby database;
Database altered.

#将备库与主库于同步模式
SQL> alter database recover managed standby database disconnect from session;
Database altered.
============================================================================
#测试dg是否搭成功
#主库建表空间

SQL> create tablespace testabc01 logging datafile '/u01/app/oracle/oradata/pdb/testabc01.dbf' size 5m  autoextend on next 10m maxsize 2048m  extent management local;
Tablespace created.

SQL> ALTER SYSTEM SWITCH LOGFILE;

System altered.

SQL> /

System altered.

SQL> /

SQL> select NAME from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/pdb/system01.dbf
/u01/app/oracle/oradata/pdb/sysaux01.dbf
/u01/app/oracle/oradata/pdb/undotbs01.dbf
/u01/app/oracle/oradata/pdb/users01.dbf
/u01/app/oracle/oradata/pdb/example01.dbf
/u01/app/oracle/oradata/pdb/testabc01.dbf

6 rows selected.

#备库查看到这个表空间说明搭建dg成功

#如何停用复制(MRP)进程
SQL >alter database recover managed standby database cancel;
SQL >shutdown immediate;

============================================================================
查看主备库发送接收、应用redo数据的过程相当于dg日志
SQL> select message from v$dataguard_status;
============================================================================
SQL> select open_mode from v$database;

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

SQL> shutdown immediate;

===========================================================
这时候关闭备库和打开备库可以这样做
关闭备用数据库
SQL >alter database recover managed standby database cancel;
SQL >shutdown immediate;
 
SQL> startup
ORACLE instance started.

Total System Global Area  839282688 bytes
Fixed Size            2233000 bytes
Variable Size          507514200 bytes
Database Buffers      322961408 bytes
Redo Buffers            6574080 bytes
Database mounted.
Database opened.

===============================================================
启用备用数据库
SQL > startup nomount;
SQL >alter database mount standby database;
SQL >alter database recover managed standby database disconnect from session;  #开启数据接收模式。

关闭备用数据库
SQL >alter database recover managed standby database cancel;                   #停止数据接收模式
SQL >shutdown immediate;

从关闭状态打开
SQL >startup nomount;
SQL >alter database mount standby database;
SQL >alter database open read only;
SQL >alter database recover managed standby database disconnect from session;  #开启数据接收模式。

主库归档：
SQL> ALTER SYSTEM SWITCH LOGFILE;    --对单实例数据库或RAC中的当前实例执行日志切换
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;    --对数据库中的所有实例执行日志切换
=========================================================================
监控SQL
 
查看备库是否和主库同步，查询V$archive_dest_status视图
SQL> select archived_thread#,archived_seq#,applied_thread#,applied_seq# from v$archive_dest_status;

ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ#
---------------- ------------- --------------- ------------
               1            20               0            0
               0             0               0            0
               0             0               0            0
               0             0               0            0
               0             0               0            0
               0             0               0            0
               0             0               0            0
               0             0               0            0
               0             0               0            0
               0             0               0            0
               0             0               0            0

ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ#
---------------- ------------- --------------- ------------
               0             0               0            0
               0             0               0            0
               0             0               0            0
               0             0               0            0
               0             0               0            0
               0             0               0            0
               0             0               0            0
               0             0               0            0
               0             0               0            0
               0             0               0            0
               0             0               0            0

ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ#
---------------- ------------- --------------- ------------
               0             0               0            0
               0             0               0            0
               0             0               0            0
               0             0               0            0
               0             0               0            0
               0             0               0            0
               0             0               0            0
               0             0               0            0
               0             0               0            0
               1            20               1           20

32 rows selected.
===============================================================================================
监控日志传送状态，V$archive_gap记录当前备库mrp进程恢复需要的但是还没有传到备库的日志更简单的是查看主备库的归档日志的序列号相差多少
SQL> select * from v$archive_gap;
no rows selected
===============================================================================================
查看当前主机的运行状态(这显示主库)
SQL> select switchover_status,database_role,protection_mode from v$database;

SWITCHOVER_STATUS    DATABASE_ROLE    PROTECTION_MODE
-------------------- ---------------- --------------------
TO STANDBY           PRIMARY          MAXIMUM PERFORMANCE


===============================================================================================
备库端查看RFS(Remote File Service)接收日志情况和MRP应用日志同步主数据库的情况(Physical Standby Database Only)   记录当前备库的一些进程情况和进程ID
主库
SQL> select process,status,thread#,sequence#,block#,blocks from v$managed_standby;

PROCESS   STATUS      THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH      CLOSING        1      72          1       1468
ARCH      CLOSING        1      62          1     34
ARCH      CLOSING        1      73          1      3
ARCH      CLOSING        1      74          1     11
LNS      WRITING        1      75        606
-----------------------------------------------------------------------------------
备库
SQL> select process,status,thread#,sequence#,block#,blocks from v$managed_standby;

PROCESS   STATUS      THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH      CLOSING        1      73          1      3
ARCH      CLOSING        1      74          1     11
ARCH      CONNECTED        0       0          0      0
ARCH      CLOSING        1      72          1       1468
MRP0      WAIT_FOR_LOG        1      75          0      0
RFS      IDLE            0       0          0      0
RFS      IDLE            0       0          0      0
RFS      IDLE            0       0          0      0
RFS      IDLE            1      75        692      1
===============================================================================================
V$STANDBY_LOG备用数据库的备用日志的数量与当前状态等信息
SQL> SELECT * FROM V$STANDBY_LOG;

    GROUP# DBID                                        THREAD#  SEQUENCE#
---------- ---------------------------------------- ---------- ----------
     BYTES  BLOCKSIZE       USED ARC STATUS     FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- --- ---------- ------------- ---------
NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME
------------ --------- ------------ ---------
         4 UNASSIGNED                                        1          0
  52428800        512          0 NO  UNASSIGNED


         5 3278846531                                        1         21
  52428800        512    1167872 YES ACTIVE           1114799 03-OCT-14
     1116436 03-OCT-14      1116436 03-OCT-14

    GROUP# DBID                                        THREAD#  SEQUENCE#
---------- ---------------------------------------- ---------- ----------
     BYTES  BLOCKSIZE       USED ARC STATUS     FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- --- ---------- ------------- ---------
NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME
------------ --------- ------------ ---------

         6 UNASSIGNED                                        0          0
  52428800        512          0 YES UNASSIGNED


         7 UNASSIGNED                                        0          0
  52428800        512          0 YES UNASSIGNED

    GROUP# DBID                                        THREAD#  SEQUENCE#
---------- ---------------------------------------- ---------- ----------
     BYTES  BLOCKSIZE       USED ARC STATUS     FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- --- ---------- ------------- ---------
NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME
------------ --------- ------------ ---------
===============================================================================================
启动Data Guard 后， 查看同步情况:
SQL> select error from v$archive_dest;

ERROR
--------------------------------------------------

ORA-16009: invalid redo transport destination

ERROR
--------------------------------------------------

ERROR
--------------------------------------------------
32 rows selected.
===============================================================================================
用SQL 查看了一下同步正常：
SQL> select sequence#,applied from v$archived_log;

 SEQUENCE# APPLIED
---------- ---------
        12 YES
        10 YES
        11 YES
        13 YES
        14 YES
         7 YES
         8 YES
         6 YES
         9 YES
        15 YES
        16 YES

 SEQUENCE# APPLIED
---------- ---------
        17 YES
        18 YES
        19 YES
        20 YES

15 rows selected.
==============================================================================================
在备库上，验证一下传过来的归档文件：
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED, completion_time FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

 SEQUENCE# FIRST_TIM NEXT_TIME APPLIED   COMPLETIO
---------- --------- --------- --------- ---------
         6 17-AUG-14 17-AUG-14 YES       03-OCT-14
         7 17-AUG-14 03-OCT-14 YES       03-OCT-14
         8 03-OCT-14 03-OCT-14 YES       03-OCT-14
         9 03-OCT-14 03-OCT-14 YES       03-OCT-14
        10 03-OCT-14 03-OCT-14 YES       03-OCT-14
        11 03-OCT-14 03-OCT-14 YES       03-OCT-14
        12 03-OCT-14 03-OCT-14 YES       03-OCT-14
        13 03-OCT-14 03-OCT-14 YES       03-OCT-14
        14 03-OCT-14 03-OCT-14 YES       03-OCT-14
        15 03-OCT-14 03-OCT-14 YES       03-OCT-14
        16 03-OCT-14 03-OCT-14 YES       03-OCT-14

 SEQUENCE# FIRST_TIM NEXT_TIME APPLIED   COMPLETIO
---------- --------- --------- --------- ---------
        17 03-OCT-14 03-OCT-14 YES       03-OCT-14
        18 03-OCT-14 03-OCT-14 YES       03-OCT-14
        19 03-OCT-14 03-OCT-14 YES       03-OCT-14
        20 03-OCT-14 03-OCT-14 YES       03-OCT-14

15 rows selected.

===============================================================================================
 在备库上，验证一下传过来的归档文件：
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME, APPLIED, completion_time FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;


在主库上，查询待转换standby库的归档文件是否连接：
SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
-----------------------------------------------------------------------------------------------
SQL> select sequence#,status from v$log;

 SEQUENCE# STATUS
---------- ----------------
    73 CLEARING
    74 CLEARING
    75 CURRENT