目录
1. Data Guard概述
1.1 DG 日志传递模式-图文并茂
了解知道原理即可
LGWR(异步)传输日志模式：先把redo数据写入本地在线日志后在传递给备库在线日志并应用
LGWR(同步)传输日志模式：主库触发LGWR->传给->备库重做日志->备库接收到重做日志后马上应用
ARCH传输日志模式：主库触发ARCH->传给->备库归档日志->备库接受到归档日志后马上应用->当归档日志目录快满时需要手工删除或备份到别处
2. DG 搭建过程
2.1 主数据检查
确认主库处于归档模式并开启force logging（不管什么操作都生成redo日志）功能 force logging功能：不管什么操作都生成redo日志，因为DG要求primary与standby数据必须一致，因此为防止主库的一些操作不产生redo导致无法完整同步备库，强制设置无论什么操作都要产生redo，这就是force logging的目的。
col FORCE_LOGGING format a20
select LOG_MODE,DATABASE_ROLE,FORCE_LOGGING from v$database;
LOG_MODE DATABASE_ROLE FORCE_LOGGING
------------ ---------------- --------------------
ARCHIVELOG PRIMARY YES
如果未启动强制日志功能执行
alter database force logging;
2.2 主数据库添加standby redo log
standby logfile
（1）standby logfile是备库组件，当成为备库时用来接收来自主库的redo日志内容
（2）现在也可以不用创建，当切换为备库时会自动创建与主库数量相同的日志个数和大小
（3）添加standby logfile大小需要与logfile大小保持一致，数量最好n+1用于日志缓冲
日志查询
set linesize 200
set pagesize 200
col member format a50
col ARCHIVED format a10
select a. GROUP#,a.type,a.STATUS,a.MEMBER,b.THREAD#,b.SEQUENCE#,b.BYTES/1024/1024 "size(M)",b.MEMBERS,b.ARCHIVED,b.STATUS
from v$Logfile a,v$log b
where a.group#=b.group#
order by b.THREAD#;
GROUP# TYPE STATUS MEMBER THREAD# SEQUENCE# size(M) MEMBERS ARCHIVED STATUS
---------- ------- ------- -------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ----------------
3 ONLINE /oradata/ora1/redo03.log 1 57 100 1 YES INACTIVE
1 ONLINE /oradata/ora1/redo01.log 1 58 100 1 NO CURRENT
2 ONLINE /oradata/ora1/redo02.log 1 56 100 1 YES INACTIVE
Col member format a50
select group#,type,member from v$logfile order by 1;
GROUP# TYPE MEMBER
---------- ------- --------------------------------------------------
1 ONLINE /oradata/ora1/redo01.log
2 ONLINE /oradata/ora1/redo02.log
3 ONLINE /oradata/ora1/redo03.log
4 STANDBY /oradata/ora1/standby_redo04.log
5 STANDBY /oradata/ora1/standby_redo05.log
6 STANDBY /oradata/ora1/standby_redo06.log
7 STANDBY /oradata/ora1/standby_redo07.log
7 rows selected.
添加standby redo log 语句
alter database add standby logfile group 4 '/oradata/standby_redo04.log' size 100M;
alter database add standby logfile group 5 '/oradata/standby_redo05.log' size 100M;
alter database add standby logfile group 6 '/oradata/standby_redo06.log' size 100M;
alter database add standby logfile group 7 '/oradata/standby_redo07.log' size 100M;
2.3 主数据库创建参数文件
SQL> create pfile='/home/oracle/pfile.ora' from spfile;
File created.
2.4 备数据库启动到nomount状态
备数据库环境变量
[oracle@ora2-12cR1 ~]$ env |grep ORA
ORACLE_SID=ora1_dg
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/db_1
备库准备的参数文件
备注：需要添加 db_unique_name 参数
[oracle@ora2-12cR1 ~]$ cat pfile.ora
*.audit_file_dest='/u01/app/oracle/admin/ora1_dg/adump'
*.audit_trail='NONE'
*.compatible='12.1.0.2.0'
*.control_files='/oradata/ora1_dg/control01.ctl','/oradata/ora1_dg/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='ora1'
*.db_unique_name='ora1_dg'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ora1XDB)'
*.enable_pluggable_database=true
*.log_archive_dest_1='location=/oradata/arch'
*.memory_target=814m
*.open_cursors=300
*.processes=500
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
创建必要的文件目录
[oracle@ora2-12cR1 ~]$
[oracle@ora2-12cR1 ~]$ mkdir -p /u01/app/oracle/admin/ora1_dg/adump
[oracle@ora2-12cR1 ~]$ mkdir -p /oradata/ora1_dg
[oracle@ora2-12cR1 ~]$ mkdir -p /oradata/arch
启动数据库到nomount 状态
SQL> startup nomount pfile='/home/oracle/pfile.ora';
ORACLE instance started.
Total System Global Area 855638016 bytes
Fixed Size 2930128 bytes
Variable Size 713034288 bytes
Database Buffers 134217728 bytes
Redo Buffers 5455872 bytes
SQL> create spfile from pfile='/home/oracle/pfile.ora';
File created.
SQL> set linesize 150
SQL> show parameter dump
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
background_core_dump string partial
background_dump_dest string /u01/app/oracle/product/12.1.0
.2/db_1/rdbms/log
core_dump_dest string /u01/app/oracle/diag/rdbms/ora
1_dg/ora1_dg/cdump
max_dump_file_size string unlimited
shadow_core_dump string partial
user_dump_dest string /u01/app/oracle/product/12.1.0
.2/db_1/rdbms/log
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 855638016 bytes
Fixed Size 2930128 bytes
Variable Size 713034288 bytes
Database Buffers 134217728 bytes
Redo Buffers 5455872 bytes
2.5 主数据库创建备库控制文件
SQL> alter database create standby controlfile as '/home/oracle/control.ctl';
Database altered.
[oracle@ora1-12cR1 ~]$ cat /etc/hosts
127.0.0.1 localhost.localdomain localhost
192.168.150.114 ora1-12cR1.localdomain ora1-12cR1
192.168.150.115 ora2-12cR1.localdomain ora2-12cR1
传递到备库端
[oracle@ora1-12cR1 ~]$ scp control.ctl oracle@ora2-12cR1:/oradata/ora1_dg
oracle@ora2-12cr1's password:
control.ctl 100% 17MB 17.4MB/s 00:01
备库操作，创建两个控制文件
[oracle@ora2-12cR1 ora1_dg]$ ls -l
total 17808
-rw-r----- 1 oracle oinstall 18235392 May 28 02:46 control.ctl
[oracle@ora2-12cR1 ora1_dg]$
[oracle@ora2-12cR1 ora1_dg]$ cp control.ctl control01.ctl
[oracle@ora2-12cR1 ora1_dg]$ cp control.ctl control02.ctl
有了控制文件备库启动mount状态
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 855638016 bytes
Fixed Size 2930128 bytes
Variable Size 713034288 bytes
Database Buffers 134217728 bytes
Redo Buffers 5455872 bytes
Database mounted.
查看控制文件类型判断主库或者备库，主库和备库只有控制文件不一样，其他文件都一样
主库操作控制文件
SQL> select NAME,OPEN_MODE,CONTROLFILE_TYPE from v$database;
NAME OPEN_MODE CONTROL
--------- -------------------- -------
ORA1 READ WRITE CURRENT
备库操作控制文件
SQL> select NAME,OPEN_MODE,CONTROLFILE_TYPE from v$database;
NAME OPEN_MODE CONTROL
--------- -------------------- -------
ORA1 MOUNTED STANDBY
2.6 主数据库配置网络参数
[oracle@ora1-12cR1 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora1-12cR1.localdomain)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=ora1_DGMGRL)
(ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/db_1)
(SID_NAME=ora1))
(SID_DESC=
(SID_NAME=plsextproc)
(ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/db_1)
(PROGRAM=extproc)))
解释：添加静态注册语句才能顺利使用DG broker管理工具来实现switchover和fast start failover，并且GLOBAL_DBNAME必须以db_unique_name_DGMGRL.db_domain格式存在，可以使用show parameter db_unique_name和show parameter db_domain方式查询。
SQL> show parameter unique
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string ora1
SQL> show parameter domain
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_domain string
[oracle@ora1-12cR1 admin]$ lsnrctl status
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 28-MAY-2016 02:58:21
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora1-12cR1.localdomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 27-MAY-2016 19:29:57
Uptime 0 days 7 hr. 28 min. 24 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/ora1-12cR1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora1-12cR1.localdomain)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "ora1" has 1 instance(s).
Instance "ora1", status READY, has 1 handler(s) for this service...
Service "ora1XDB" has 1 instance(s).
Instance "ora1", status READY, has 1 handler(s) for this service...
Service "ora1_DGMGRL" has 1 instance(s).
Instance "ora1", status UNKNOWN, has 1 handler(s) for this service...
Service "pdb11" has 1 instance(s).
Instance "ora1", status READY, has 1 handler(s) for this service...
Service "pdb12" has 1 instance(s).
Instance "ora1", status READY, has 1 handler(s) for this service...
Service "plsextproc" has 1 instance(s).
Instance "plsextproc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
名词解释 UNKNOWN：监听不知道实例的状态，连接请求时才进行实例检查，静态注册 READY：监听会不间断探测实例，当实例启动后会自动动态注册到监听，动态注册。
主库tnames 配置信息
[oracle@ora1-12cR1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_ORA1 =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora1-12cR1.localdomain)(PORT = 1521))
ORA1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora1-12cR1.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ora1)
)
)
ORA1_DG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora2-12cR1.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ora1_dg_DGMGRL)
)
)
pdb11 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora1-12cR1.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdb11)
)
)
pdb12 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora1-12cR1.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdb12)
)
)
2.7 备数据库网络参数配置
[oracle@ora2-12cR1 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora2-12cR1.localdomain)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=ora1_dg_DGMGRL)
(ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/db_1)
(SID_NAME=ora1_dg))
(SID_DESC=
(SID_NAME=plsextproc)
(ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/db_1)
(PROGRAM=extproc)))
SQL> show parameter unique
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string ora1_dg
SQL> show parameter domain
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_domain string
备库tnames 配置信息
[oracle@ora2-12cR1 admin]$ cat tnsnames.ora
LISTENER_ORA1_DG =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora2-12cR1.localdomain)(PORT = 1521))
ORA1_DG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora2-12cR1.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ora1_dg_DGMGRL)
)
)
ORA1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora1-12cR1.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ora1)
)
)
pdb11 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora2-12cR1.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdb11)
)
)
pdb12 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora2-12cR1.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = pdb12)
)
)
2.8 备库密码文件
注意备库密码文件与主库密码文件内容保持一致
手动创建密码文件
[oracle@ora2-12cR1 dbs]$ cd $ORACLE_HOME/dbs
[oracle@ora2-12cR1 dbs]$ orapwd file=orapwora1_dg password=oracle entries=10
或者从主库拷贝
[oracle@ora1-12cR1 dbs]$ scp orapwora1 oracle@ora2-12cR1:/u01/app/oracle/product/12.1.0.2/db_1/dbs
oracle@ora2-12cr1's password:
[oracle@ora2-12cR1 dbs]$ mv orapwora1 orapwora1_dg
[oracle@ora2-12cR1 dbs]$ ls
hc_ora1_dg.dat init.ora lkORA1 lkORA1_DG orapwora1_dg spfileora1_dg.ora
2.9 主备数据库网络测试
主库操作
[oracle@ora1-12cR1 ~]$ tnsping ORA1
[oracle@ora1-12cR1 ~]$ tnsping ORA1_DG
[oracle@ora1-12cR1 ~]$ sqlplus sys/oracle@ORA1 as sysdba
[oracle@ora1-12cR1 ~]$ sqlplus sys/oracle@ORA1_DG as sysdba
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
备库操作
[oracle@ora2-12cR1 admin]$ tnsping ORA1_DG
[oracle@ora2-12cR1 admin]$ tnsping ORA1
[oracle@ora2-12cR1 admin]$ sqlplus sys/oracle@ORA1 as sysdba
[oracle@ora2-12cR1 admin]$ sqlplus system/oracle@ORA1
[oracle@ora2-12cR1 admin]$ sqlplus sys/oracle@ORA1_DG as sysdba
2.10 主数据库全库备份
SQL> select DBID,con_id,dbid,NAME,OPEN_MODE from v$pdbs;
DBID CON_ID DBID NAME OPEN_MODE
---------- ---------- ---------- ------------------------------ ----------
1952071276 2 1952071276 PDB$SEED READ ONLY
2246627906 3 2246627906 PDB11 READ WRITE
491426823 4 491426823 PDB12 READ WRITE
pdb 数据库没有打开需要打开数据库
打开pdb 数据库
SQL> alter pluggable database PDB11 open;
Pluggable database altered.
SQL> alter pluggable database PDB12 open;
Pluggable database altered.
[oracle@ora1-12cR1 ~]$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Sat May 28 03:38:48 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA1 (DBID=1389781259)
RMAN> backup as compressed backupset database format '/home/oracle/full_%U.bak';
Starting backup at 28-MAY-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=68 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/oradata/ora1/system01.dbf
input datafile file number=00003 name=/oradata/ora1/sysaux01.dbf
input datafile file number=00004 name=/oradata/ora1/undotbs01.dbf
input datafile file number=00006 name=/oradata/ora1/users01.dbf
备份完成传递到备库端
[oracle@ora1-12cR1 ~]$ ls -l *.bak
-rw-r----- 1 oracle oinstall 332308480 May 28 03:39 full_0ar6mjec_1_1.bak
-rw-r----- 1 oracle oinstall 165306368 May 28 03:40 full_0br6mjgd_1_1.bak
-rw-r----- 1 oracle oinstall 165371904 May 28 03:41 full_0cr6mjhh_1_1.bak
-rw-r----- 1 oracle oinstall 165281792 May 28 03:41 full_0dr6mjik_1_1.bak
[oracle@ora1-12cR1 ~]$ scp *.bak oracle@ora2-12cR1:/home/oracle
2.11 备库恢复数据库
SQL> select status from v$instance;
STATUS
------------
MOUNTED
SQL> select open_Mode from v$database;
OPEN_MODE
--------------------
MOUNTED
修改主备库映射文件
SQL> show parameter convert
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string
log_file_name_convert string
pdb_file_name_convert string
未恢复之前文件位置信息
set linesize 200
set pagesize 4000
col name format a100
select name from v$dbfile
union
select member from v$logfile
union
select name from v$controlfile
union
select name from v$tempfile
order by 1;
NAME
----------------------------------------------------------------------------------------------------
/oradata/ora1/pdb11/pdb11_test01.dbf
/oradata/ora1/pdb11/pdb11_users01.dbf
/oradata/ora1/pdb11/sysaux01.dbf
/oradata/ora1/pdb11/system01.dbf
/oradata/ora1/pdb11/temp012016-05-27_02-08-06-AM.dbf
/oradata/ora1/pdb12/pdb12_users01.dbf
/oradata/ora1/pdb12/sysaux01.dbf
/oradata/ora1/pdb12/system01.dbf
/oradata/ora1/pdb12/temp012016-05-27_02-08-06-AM.dbf
/oradata/ora1/pdbseed/pdbseed_temp012016-05-27_02-08-06-AM.dbf
/oradata/ora1/pdbseed/sysaux01.dbf
/oradata/ora1/pdbseed/system01.dbf
/oradata/ora1/redo01.log
/oradata/ora1/redo02.log
/oradata/ora1/redo03.log
/oradata/ora1/standby_redo04.log
/oradata/ora1/standby_redo05.log
/oradata/ora1/standby_redo06.log
/oradata/ora1/standby_redo07.log
/oradata/ora1/sysaux01.dbf
/oradata/ora1/system01.dbf
/oradata/ora1/temp01.dbf
/oradata/ora1/undotbs01.dbf
/oradata/ora1/users01.dbf
/oradata/ora1_dg/control01.ctl
/oradata/ora1_dg/control02.ctl
26 rows selected.
创建文件目录和修改主库和备份数据文件和日志文件映射关系，pdb 数据库单独映射
[oracle@ora2-12cR1 ora1_dg]$ mkdir pdb11 pdb12 pdbseed
alter system set db_file_name_convert ='/oradata/ora1/','/oradata/ora1_dg/' scope=spfile;
alter system set log_file_name_convert='/oradata/ora1/','/oradata/ora1_dg/' scope=spfile;
alter system set pdb_file_name_convert='/oradata/ora1/pdbseed/','/oradata/ora1_dg/pdbseed/','/oradata/ora1/pdb11/','/oradata/ora1_dg/pdb11/','/oradata/ora1/pdb12/','/oradata/ora1_dg/pdb12/' scope=spfile;
修改完成重启数据库
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 855638016 bytes
Fixed Size 2930128 bytes
Variable Size 713034288 bytes
Database Buffers 134217728 bytes
Redo Buffers 5455872 bytes
Database mounted.
SQL> set linesize 150
SQL> show parameter convert
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string /oradata/ora1/, /oradata/ora1_
dg/
log_file_name_convert string /oradata/ora1/, /oradata/ora1_
dg/
pdb_file_name_convert string /oradata/ora1/pdbseed/, /orada
ta/ora1_dg/pdbseed/, /oradata/
ora1/pdb11/, /oradata/ora1_dg/
pdb11/, /oradata/ora1/pdb12/,
/oradata/ora1_dg/pdb12/
文件全部修改完成
NAME
----------------------------------------------------------------------------------------------------
/oradata/ora1_dg/control01.ctl
/oradata/ora1_dg/control02.ctl
/oradata/ora1_dg/pdb11/pdb11_test01.dbf
/oradata/ora1_dg/pdb11/pdb11_users01.dbf
/oradata/ora1_dg/pdb11/sysaux01.dbf
/oradata/ora1_dg/pdb11/system01.dbf
/oradata/ora1_dg/pdb11/temp012016-05-27_02-08-06-AM.dbf
/oradata/ora1_dg/pdb12/pdb12_users01.dbf
/oradata/ora1_dg/pdb12/sysaux01.dbf
/oradata/ora1_dg/pdb12/system01.dbf
/oradata/ora1_dg/pdb12/temp012016-05-27_02-08-06-AM.dbf
/oradata/ora1_dg/pdbseed/pdbseed_temp012016-05-27_02-08-06-AM.dbf
/oradata/ora1_dg/pdbseed/sysaux01.dbf
/oradata/ora1_dg/pdbseed/system01.dbf
/oradata/ora1_dg/redo01.log
/oradata/ora1_dg/redo02.log
/oradata/ora1_dg/redo03.log
/oradata/ora1_dg/standby_redo04.log
/oradata/ora1_dg/standby_redo05.log
/oradata/ora1_dg/standby_redo06.log
/oradata/ora1_dg/standby_redo07.log
/oradata/ora1_dg/sysaux01.dbf
/oradata/ora1_dg/system01.dbf
/oradata/ora1_dg/temp01.dbf
/oradata/ora1_dg/undotbs01.dbf
/oradata/ora1_dg/users01.dbf
26 rows selected.
备注：为了实现主备数据库切换
主数据库也需要修改convert 参数，convert角色是备库的生效，主库的时候不生效。
alter system set db_file_name_convert ='/oradata/ora1_dg/','/oradata/ora1/' scope=spfile;
alter system set log_file_name_convert='/oradata/ora1_dg/','/oradata/ora1/' scope=spfile;
alter system set pdb_file_name_convert='/oradata/ora1_dg/pdbseed/','/oradata/ora1/pdbseed/','/oradata/ora1_dg/pdb11/','/oradata/ora1/pdb11/','/oradata/ora1_dg/pdb12/','/oradata/ora1/pdb12/' scope=spfile;
rman 注册备份集恢复数据库
[oracle@ora2-12cR1 ~]$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Sat May 28 04:01:53 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA1 (DBID=1389781259, not open)
RMAN> catalog start with '/home/oracle/';
RMAN> restore database;
Starting restore at 28-MAY-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=29 device type=DISK
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 28-MAY-16
打开数据库报错，需要恢复数据库
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-10458: standby database requires recovery
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/oradata/ora1_dg/system01.dbf'
SQL> recover standby database;
ORA-00279: change 1832764 generated at 05/28/2016 00:27:48 needed for thread 1
ORA-00289: suggestion : /oradata/arch/1_58_912910029.dbf
ORA-00280: change 1832764 for thread 1 is in sequence #58
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
主数据库切换归档
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
传递到备库恢复
[oracle@ora1-12cR1 arch]$ scp 1_58_912910029.dbf 1_59_912910029.dbf 1_60_912910029.dbf 1_61_912910029.dbf oracle@ora2-12cR1:/oradata/arch
备库查看
[oracle@ora2-12cR1 arch]$ ls -lhtr
total 34M
-rw-r----- 1 oracle oinstall 34M May 28 04:18 1_58_912910029.dbf
-rw-r----- 1 oracle oinstall 1.5K May 28 04:18 1_59_912910029.dbf
-rw-r----- 1 oracle oinstall 2.0K May 28 04:18 1_61_912910029.dbf
-rw-r----- 1 oracle oinstall 2.5K May 28 04:18 1_60_912910029.dbf
备库继续恢复操作
SQL> recover standby database;
ORA-00279: change 1832764 generated at 05/28/2016 00:27:48 needed for thread 1
ORA-00289: suggestion : /oradata/arch/1_58_912910029.dbf
ORA-00280: change 1832764 for thread 1 is in sequence #58
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 1846005 generated at 05/28/2016 04:15:52 needed for thread 1
ORA-00289: suggestion : /oradata/arch/1_59_912910029.dbf
ORA-00280: change 1846005 for thread 1 is in sequence #59
ORA-00278: log file '/oradata/arch/1_58_912910029.dbf' no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 1846010 generated at 05/28/2016 04:15:54 needed for thread 1
ORA-00289: suggestion : /oradata/arch/1_60_912910029.dbf
ORA-00280: change 1846010 for thread 1 is in sequence #60
ORA-00278: log file '/oradata/arch/1_59_912910029.dbf' no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 1846015 generated at 05/28/2016 04:15:55 needed for thread 1
ORA-00289: suggestion : /oradata/arch/1_61_912910029.dbf
ORA-00280: change 1846015 for thread 1 is in sequence #61
ORA-00278: log file '/oradata/arch/1_60_912910029.dbf' no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00279: change 1846022 generated at 05/28/2016 04:15:58 needed for thread 1
ORA-00289: suggestion : /oradata/arch/1_62_912910029.dbf
ORA-00280: change 1846022 for thread 1 is in sequence #62
ORA-00278: log file '/oradata/arch/1_61_912910029.dbf' no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log '/oradata/arch/1_62_912910029.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
打开数据库
SQL> alter database open;
Database altered.
查看数据库状态
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> select open_Mode from v$database;
OPEN_MODE
--------------------
READ ONLY
2.12 主备数据库配置
主数据库：
alter system set log_archive_config='dg_config=(ora1,ora1_dg)';
alter system set log_archive_dest_2='service=ORA1_DG lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=ora1_dg';
alter system set standby_file_management='AUTO';
启动或关闭日志传送
SQL> alter system set log_archive_dest_state_2=enable;
SQL> alter system set log_archive_dest_state_2=defer;
备注：log_archive_config 参数备库必须设置，否则报错 ora-14607
主数据库日志
Sat May 28 04:37:55 2016
Archived Log entry 107 added for thread 1 sequence 70 ID 0x52d5c60b dest 1:
Sat May 28 04:37:59 2016
TT01: Standby redo logfile selected for thread 1 sequence 70 for destination LOG_ARCHIVE_DEST_2
TT01: Standby redo logfile selected for thread 1 sequence 71 for destination LOG_ARCHIVE_DEST_2
备库数据库：
alter system set log_archive_config='dg_config=(ora1,ora1_dg)';
alter system set log_archive_dest_2='service=ORA1 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=ora1';
alter system set standby_file_management='AUTO';
添加备库standby redo log 数据库可以在read only 状态下操作：
alter database add standby logfile group 8 ('/oradata/ora1_dg/standby_redo08.log') size 100M;
alter database add standby logfile group 9 ('/oradata/ora1_dg/standby_redo09.log') size 100M;
alter database add standby logfile group 10 ('/oradata/ora1_dg/standby_redo10.log') size 100M;
alter database add standby logfile group 11 ('/oradata/ora1_dg/standby_redo11.log') size 100M;
备库启动自动恢复
alter database recover managed standby database disconnect from session;
备库启动并行恢复
alter database recover managed standby database disconnect from session parallel 2;
取消自动恢复
alter database recover managed standby database cancel;
备库恢复日志:
Archived Log entry 13 added for thread 1 sequence 74 ID 0x52d5c60b dest 1:
RFS[1]: Selected log 5 for thread 1 sequence 75 dbid 1389781259 branch 912910029
Sat May 28 04:53:46 2016
Media Recovery Waiting for thread 1 sequence 75 (in transit)
Sat May 28 04:53:47 2016
Recovery of Online Redo Log: Thread 1 Group 5 Seq 75 Reading mem 0
Mem# 0: /oradata/ora1_dg/standby_redo05.log
已经接受75号规定文件
3. 测试ADG
3.1 备库操作
SQL> select DBID,con_id,dbid,NAME,OPEN_MODE from v$pdbs;
DBID CON_ID DBID NAME OPEN_MODE
---------- ---------- ---------- ------------------------------ ----------
1952071276 2 1952071276 PDB$SEED READ ONLY
2246627906 3 2246627906 PDB11 MOUNTED
491426823 4 491426823 PDB12 MOUNTED
打开pdb 数据库
SQL> alter pluggable database PDB11 open;
Pluggable database altered.
SQL> alter pluggable database PDB12 open;
Pluggable database altered.
3.2 主数据库操作，创建local测试用户
SQL> alter session set container=PDB11;
Session altered.
SQL>
SQL> show con_name
CON_NAME
------------------------------
PDB11
SQL> create tablespace test datafile '/oradata/ora1/pdb11/pdb11_test01.dbf' size 100M;
Tablespace created
create user test identified by test default tablespace test;
SQL> grant connect,resource to test;
Grant succeeded.
create table t1(id int,name varchar2(20));
insert into t1 values (1,'oracle12c');
insert into t1 values (2,'oracle数据库');
commit;
备库查询
[oracle@ora2-12cR1 ora1_dg]$ sqlplus test/test@pdb11
SQL*Plus: Release 12.1.0.2.0 Production on Sat May 28 05:05:10 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Sat May 28 2016 00:28:25 +08:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> select * from t1;
ID NAME
---------- --------------------
1 oracle12c
3.3 主数据库操作，创建common测试用户
查询common 用户
set linesize 150
set pagesize 200
col username format a30
col common format a10
select username,common,ACCOUNT_STATUS,DEFAULT_TABLESPACE,con_id from cdb_users
order by 3;
创建用户并授权
SQL> create user c##test identified by test;
User created.
SQL> grant connect,resource to c##test;
Grant succeeded.
SQL> grant unlimited tablespace to c##test;
Grant succeeded.
create table t1(id int,name varchar2(20));
insert into t1 values (1,'oracle12c');
insert into t1 values (2,'oracle数据库');
备库查询
[oracle@ora2-12cR1 ~]$ sqlplus c##test/test
SQL*Plus: Release 12.1.0.2.0 Production on Tue May 31 04:17:59 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Tue May 31 2016 04:12:06 +08:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing optionsSQL> select * from t1;
ID NAME
---------- --------------------
1 oracle12c
2 oracle数据库
4. 常用查询命令
4.1 主库状态查询
set linesize 150
col FLASHBACK_ON for a10
col SWITCHOVER_STATUS for a20
select db_unique_name,NAME, OPEN_MODE, DATABASE_ROLE, FLASHBACK_ON, SWITCHOVER_STATUS,dataguard_broker from v$database;
DB_UNIQUE_NAME NAME OPEN_MODE DATABASE_ROLE FLASHBACK_ SWITCHOVER_STATUS DATAGUAR
------------------------------ --------- -------------------- ---------------- ---------- -------------------- --------
ora1 ORA1 READ WRITE PRIMARY NO TO STANDBY DISABLED
主库：可读写 没有启动broker 主库角色
SQL> select group#,bytes/1024/1024 "size(M)",status from v$standby_log;
GROUP# size(M) STATUS
---------- ---------- ----------
4 100 UNASSIGNED
5 100 UNASSIGNED
6 100 UNASSIGNED
7 100 UNASSIGNED
主库的standby_log没有使用
set linesize 150
set pagesize 400
col standby_dest for a12
col archived for a10
col applied for a10
col status for a10
select sequence#,standby_dest,archived,applied,status from v$archived_log
order by 1;
SEQUENCE# STANDBY_DEST ARCHIVED APPLIED STATUS
---------- ------------ ---------- ---------- ----------
123 NO YES NO A
123 YES YES YES A
124 NO YES NO A
124 YES YES YES A
125 YES YES NO A
125 NO YES NO A
说明：一共125个归档日志，全部传递到了备库，全部归档，125号未应用，状态A
set linesize 150
set pagesize 400
col dest_name format a50
select dest_name,database_mode,recovery_mode,archived_seq#,applied_seq# from v$archive_dest_status;
DEST_NAME DATABASE_MODE RECOVERY_MODE ARCHIVED_SEQ# APPLIED_SEQ#
-------------------------------------------------- --------------- ----------------------- ------------- ------------
LOG_ARCHIVE_DEST_1 OPEN IDLE 125 0
LOG_ARCHIVE_DEST_2 OPEN_READ-ONLY MANAGED REAL TIME APPLY 125 124
LOG_ARCHIVE_DEST_3 UNKNOWN IDLE 0 0
说明：主库归档目录用途，已经生成125号日志，应用124号，LOG_ARCHIVE_DEST_1 本地目录 LOG_ARCHIVE_DEST_2 远程备库目录
主库进程状态查询
SQL> select process,status,sequence#,delay_mins from v$managed_standby;
PROCESS STATUS SEQUENCE# DELAY_MINS
--------- ---------- ---------- ----------
ARCH CLOSING 128 0
ARCH CONNECTED 0 0
ARCH CLOSING 126 0
ARCH CLOSING 127 0
LNS WRITING 129 0
说明：主库进程的工作状态，arch进程已经完成126号127号 128号日志的归档已关闭，LGWR 等待写129号日志
4.2 备库状态查询
set linesize 150
col FLASHBACK_ON for a10
col SWITCHOVER_STATUS for a20
select db_unique_name,NAME, OPEN_MODE, DATABASE_ROLE, FLASHBACK_ON, SWITCHOVER_STATUS,dataguard_broker from v$database;
DB_UNIQUE_NAME NAME OPEN_MODE DATABASE_ROLE FLASHBACK_ SWITCHOVER_STATUS DATAGUAR
------------------------------ --------- -------------------- ---------------- ---------- -------------------- --------
ora1_dg ORA1 READ ONLY WITH APPLY PHYSICAL STANDBY NO NOT ALLOWED DISABLED
备库：read only状态 物理备库角色 没有启动broker
SQL> select group#,bytes,status from v$standby_log;
GROUP# BYTES STATUS
---------- ---------- ----------
4 104857600 ACTIVE
5 104857600 UNASSIGNED
6 104857600 UNASSIGNED
7 104857600 UNASSIGNED
8 104857600 UNASSIGNED
9 104857600 UNASSIGNED
10 104857600 UNASSIGNED
11 104857600 UNASSIGNED
8 rows selected.
备库的standby_log使用了一个
SQL> select * from v$archive_gap;
no rows selected
说明：备库也没有遗漏
set linesize 150
set pagesize 400
col standby_dest for a12
col archived for a10
col applied for a10
col status for a10
select sequence#,standby_dest,archived,applied,status from v$archived_log
order by 1;
SEQUENCE# STANDBY_DEST ARCHIVED APPLIED STATUS
---------- ------------ ---------- ---------- ----------
123 NO YES YES A
124 NO YES YES A
125 NO YES YES A
126 NO YES YES A
127 NO YES YES A
128 NO YES IN-MEMORY A
说明：目前只有128个归档日志，已经归档，还一个没有应用，状态A
set linesize 150
set pagesize 400
col dest_name format a50
select dest_name,database_mode,recovery_mode,archived_seq#,applied_seq# from v$archive_dest_status;
DEST_NAME DATABASE_MODE RECOVERY_MODE ARCHIVED_SEQ# APPLIED_SEQ#
-------------------------------------------------- --------------- ----------------------- ------------- ------------
LOG_ARCHIVE_DEST_1 OPEN_READ-ONLY MANAGED REAL TIME APPLY 128 0
STANDBY_ARCHIVE_DEST UNKNOWN IDLE 128 128
说明：备库归档目录用途，已经接收128号日志，应用128号，LOG_ARCHIVE_DEST_1 本地备库目录
set linesize 150
col status format a15
select process,status,sequence#,delay_mins from v$managed_standby;
PROCESS STATUS SEQUENCE# DELAY_MINS
--------- --------------- ---------- ----------
ARCH CLOSING 127 0
ARCH CONNECTED 0 0
ARCH CLOSING 126 0
ARCH CLOSING 128 0
RFS IDLE 0 0
RFS IDLE 129 0
RFS IDLE 0 0
RFS IDLE 0 0
MRP0 APPLYING_LOG 129 0
说明：备库进程的工作状态，arch进程已经完成126,127,128号日志归档，RFS进程等待接收来自主库的日志为空闲状态。
4.3 查询standby redo log
SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
SQL>SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
GROUP# THREAD# SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------
4 1 88 YES ACTIVE
5 1 0 NO UNASSIGNED
6 1 0 NO UNASSIGNED
7 1 0 NO UNASSIGNED
8 0 0 YES UNASSIGNED
9 0 0 YES UNASSIGNED
10 0 0 YES UNASSIGNED
11 0 0 YES UNASSIGNED
8 rows selected.
4.4 查询PDB数据库状态
SQL> select DBID,con_id,dbid,NAME,OPEN_MODE from v$pdbs;
DBID CON_ID DBID NAME OPEN_MODE
---------- ---------- ---------- ------------------------------ ----------
1952071276 2 1952071276 PDB$SEED READ ONLY
2246627906 3 2246627906 PDB11 MOUNTED
491426823 4 491426823 PDB12 MOUNTED
4.5 打开PDB数据库
SQL> alter pluggable database PDB11 open;
Pluggable database altered.
SQL> alter pluggable database PDB12 open;
Pluggable database altered.
打开所有PDB数据库
SQL> alter pluggable database all open;
Pluggable database altered.
4.6 主数据库查询归档应用情况
set linesize 150
set pagesize 400
col standby_dest for a12
col archived for a10
col applied for a10
col status for a10
select sequence#,standby_dest,archived,applied,status from v$archived_log
order by 1;
SEQUENCE# STANDBY_DEST ARCHIVED APPLIED STATUS
---------- ------------ ---------- ---------- ----------
82 YES YES YES A
83 YES YES YES A
84 NO YES NO A
85 NO YES NO A
86 NO YES NO A
86 YES YES NO A
85 YES YES NO A
84 YES YES NO A
87 NO YES NO A
87 YES YES NO A
set linesize 150
set pagesize 400
col dest_name format a50
select dest_name,database_mode,recovery_mode,archived_seq#,applied_seq# from v$archive_dest_status;
DEST_NAME DATABASE_MODE RECOVERY_MODE ARCHIVED_SEQ# APPLIED_SEQ#
-------------------------------------------------- --------------- ----------------------- ------------- ------------
LOG_ARCHIVE_DEST_1 OPEN IDLE 125 0
LOG_ARCHIVE_DEST_2 OPEN_READ-ONLY MANAGED REAL TIME APPLY 125 124
LOG_ARCHIVE_DEST_3 UNKNOWN IDLE 0 0
LOG_ARCHIVE_DEST_4 UNKNOWN IDLE 0 0
4.7 备库归档应用情况
SQL> select sequence#,standby_dest,archived,applied,status from v$archived_log;
SEQUENCE# STA ARC APPLIED S
---------- --- --- --------- -
64 NO YES YES A
65 NO YES YES A
66 NO YES YES A
67 NO YES YES A
68 NO YES YES A
69 NO YES YES A
63 NO YES YES A
62 NO YES YES A
70 NO YES YES A
71 NO YES YES A
72 NO YES YES A
4.8 备库启动MRP恢复
打开日后应用
alter database recover managed standby database disconnect from session parallel 2;
alter database recover managed standby database disconnect from session;
取消日志应用
alter database recover managed standby database cancel;
备注：
disconnect from session：启动日志应用同时截断所有会话，等同于nohup提交后台运行，如果不加鼠标会停留在下一行开头不动，你只能另开启一个windows了。
parallel 2：启动2个MRP slaves进程进行日志应用操作加快速度。
4.9 主备数据库切换命令
(1)主切备
SELECT SWITCHOVER_STATUS FROM V$DATABASE;
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
alter database commit to switchover to physical standby with session shutdown;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
(2)备切主
SELECT SWITCHOVER_STATUS FROM V$DATABASE;
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
ALTER DATABASE OPEN;
SHUTDOWN IMMEDIATE;
STARTUP;
(3) FAIL OVER 切换
SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';
Repeat Step 1 until all gaps are resolved.
Copy any other missing archived redo log files.
SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#)
OVER (PARTITION BY thread#) AS LAST from V$ARCHIVED_LOG;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH FORCE;
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
ALTER DATABASE OPEN;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;
5. 单实例主备数据库切换
5.1 实现SWITCH OVER切换
Data Guard中分为两种冗余切换，一种是switchover无损切换，一种是failover丢弃切换。 所谓switchover是针对failover而言的，它是一种无损切换，切换的过程中不会丢失数据。可以平滑的使主备互换并且2个库都可以正常使用。
切换过程:
(1)主库->切换->备库
(2)检查状态
(3)原备库->切换->主库
(4)再检查状态
应用场合：主库需要调整升级，主库性能不佳，这时可以切换后用新主库对外提供服务是否丢失数据：不会丢失数据
Switchover Role Transition之前准备工作（标准流程，一般自己建的库最了解就可以skip）
(1)检查主备库参数文件是否配置正确
(2)主备库必须为归档模式，要保证两端网络畅通
(3)RAC模式下备库只能有一个实例为open，其他实例全部为shutdown，经过角色转换后，重新启动这些实例open 评估一下备库切换成主库后日志同步时间是否及时，只有备库有信息，主库没有信息
估算switchover切换需要的时间(switchover time=apply finish time+estimated startup time)
备库查询估计切换时间
set linesize 300
set pagesize 999
col value for a20
col SOURCE_DB_UNIQUE_NAME for a10
select * from v$dataguard_stats;
SOURCE_DBID SOURCE_DB_ NAME VALUE UNIT TIME_COMPUTED DATUM_TIME CON_ID
----------- ---------- -------------------------------- -------------------- ------------------------------ ------------------------------ ------------------------------ ----------
1389781259 ora1 transport lag +00 00:00:00 day(2) to second(0) interval 05/28/2016 18:51:48 05/28/2016 18:51:47 0
1389781259 ora1 apply lag +00 00:00:00 day(2) to second(0) interval 05/28/2016 18:51:48 05/28/2016 18:51:47 0
1389781259 ora1 apply finish time +00 00:00:00.000 day(2) to second(3) interval 05/28/2016 18:51:48 0
0 estimated startup time 11 second 05/28/2016 18:51:48 0
字段解释：
（1）apply lag：该值表示在通过在备库上应用主库传递过来的重做日志与出库同步所延迟的时间。APPLY LAG: Amount of time that the application of redo data on the standby database lags behind the primary database。
（2）transport lag：该值表示在单位时间内主库上产生的重做日志还没有传输到备库上，或者主库上产生的重做日志还没有被备库所应用
（3）apply finish time：该值表示在备库上完成应用重做日志所需要的时间
（4）estimated startup time：该值表示启动和打开物理备库所需要的时间，该字段不是适用于逻辑备库。 An estimate of the time needed to start and open the database
（5）VALUE：给出各个参数的值。如第1个查询中的，apply finish time值为+00 00:00:00.1，说明该物理备库需要0.1秒的时间来完成应用剩余的重做日志数据。
（6）UNIT：各个参数的时间单元。
（7）TIME_COMPUTED：物理备库上估算各个参数的本地时间。
（8）DATUM_TIME：在物理备库上获取元数据来估算 APPLY LAG 和 TRANSPORT LAG 这两个参数值的本地时间。如果从多次查询中看到该时间值对应的APPLY LAG 和 TRANSPORT LAG 这两个参数值保持不变的话，那么就说明该物理备库已经停止从主库接收到重做数据！该字段是11g中新出现的
官方文档上，关于V$DATAGUARD_STATS是这样描述的：该动态性能视图显示出在主库上产生了多少重做日志数据，但是还没有被备库所应用。所以，通过查询该视图可以基本确定如果万一主库出现崩溃的话，备库上将丢失多少重做日志数据。我们可以在一套Dataguard环境下的任一备库的实例上从该视图里获取相关信息，然而，在主库的实例上查询该视图返回的信息都将是空。也就是说，只可以从备库的实例上查询V$DATAGUARD_STATS，从主库实例上是看不到任何有用信息的。
主库查询为空
SQL> set linesize 400
SQL> set pagesize 999
SQL> col value for a15
SQL> select * from v$dataguard_stats;
no rows selected
备注：以下是具体操作步骤。
5.2 主数据库状态检查
SQL> select database_role,switchover_status,open_mode from v$database;
DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE
---------------- -------------------- --------------------
PRIMARY TO STANDBY READ WRITE
作用：检查主库切换的可能性
如果“TO STANDBY”值表示主库可以顺利切换到备库
如果“SESSIONS ACTIVE”表示应然存在活动的会话，在切换时可以添加“with session shutdown”关键字在切换时终止所有存活会话。
如果“RESOLVABLE GAP”表示备库的standby_log组状态全部为UNASSIGNED，可做一次alter system archive log current;日志切换来应用一次即可恢复。
切换时候注意primary 的alert.log是否有报错信息以便定位问题
主库执行切换命令
SQL> alter database commit to switchover to physical standby with session shutdown;
Database altered.
5.3 切换时主备数据库日志查看
主库日志：
Sat May 28 19:04:49 2016
alter database commit to switchover to physical standby with session shutdown
Sat May 28 19:04:49 2016
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 3442] (ora1)
Sat May 28 19:04:49 2016
Waiting for target standby to receive all redo
Sat May 28 19:04:49 2016
Waiting for all non-current ORLs to be archived...
Sat May 28 19:04:49 2016
All non-current ORLs have been archived.
Sat May 28 19:04:49 2016
Waiting for all FAL entries to be archived...
Sat May 28 19:04:49 2016
All FAL entries have been archived.
Sat May 28 19:04:49 2016
Waiting for potential Physical Standby switchover target to become synchronized...
Sat May 28 19:04:50 2016
Active, synchronized Physical Standby switchover target has been identified
Preventing updates and queries at the Primary
Generating and shipping final logs to target standby
Switchover End-Of-Redo Log thread 1 sequence 96 has been fixed
Switchover: Primary highest seen SCN set to 0x0.0x1e151a
ARCH: Noswitch archival of thread 1, sequence 96
ARCH: End-Of-Redo Branch archival of thread 1 sequence 96
ARCH: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2
ARCH: Standby redo logfile selected for thread 1 sequence 96 for destination LOG_ARCHIVE_DEST_2
ARCH: Archiving is disabled due to current logfile archival
Primary will check for some target standby to have received all redo
Waiting for target standby to apply all redo
Final check for a synchronized target standby. Check will be made once.
LOG_ARCHIVE_DEST_2 is a potential Physical Standby switchover target
Active, synchronized target has been identified
Target has also received all redo
Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/ora1/ora1/trace/ora1_ora_3442.trc
Converting the primary database to a new standby database
Clearing standby activation ID 1389741579 (0x52d5c60b)
The primary database controlfile was created using the
'MAXLOGFILES 16' clause.
There is space for up to 13 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 104857600;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 104857600;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 104857600;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 104857600;
Archivelog for thread 1 sequence 96 required for standby recovery
Offline data file 5 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 7 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 8 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 9 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 10 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 11 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 12 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 13 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Offline data file 14 marked as online during convert to standby or switchover to standby.
Restore of backup may be required if the file is not physically accessible.
Switchover: Primary controlfile converted to standby controlfile succesfully.
Switchover: Complete - Database shutdown required
USER (ospid: 3442): terminating the instance
Sat May 28 19:04:52 2016
Instance terminated by USER, pid = 3442
Completed: alter database commit to switchover to physical standby with session shutdown
Shutting down instance (abort)
License high water mark = 9
Sat May 28 19:04:52 2016
Instance shutdown complete
备库日志：
Sat May 28 19:04:53 2016
RFS[6]: Assigned to RFS process (PID:2764)
RFS[6]: Selected log 4 for thread 1 sequence 96 dbid 1389781259 branch 912910029
Sat May 28 19:04:53 2016
Archived Log entry 35 added for thread 1 sequence 96 ID 0x52d5c60b dest 1:
Sat May 28 19:04:53 2016
Resetting standby activation ID 1389741579 (0x52d5c60b)
Sat May 28 19:04:53 2016
Media Recovery End-Of-Redo indicator encountered
Sat May 28 19:04:53 2016
Media Recovery Continuing
Media Recovery Waiting for thread 1 sequence 97
Sat May 28 19:04:53 2016
RFS[7]: Assigned to RFS process (PID:2762)
RFS[7]: Possible network disconnect with primary database
Sat May 28 19:04:53 2016
RFS[5]: Possible network disconnect with primary database
5.4 重启主库到mount 状态
SQL> shutdown immediate;
ORA-01012: not logged on
SQL> startup mount;
ORACLE instance started.
Total System Global Area 855638016 bytes
Fixed Size 2930128 bytes
Variable Size 675285552 bytes
Database Buffers 171966464 bytes
Redo Buffers 5455872 bytes
Database mounted.
5.5 主数据库检查
col controlfile_type format a20
select database_role,switchover_status,open_mode,controlfile_type from v$database;
DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE CONTROLFILE_TYPE
---------------- -------------------- -------------------- --------------------
PHYSICAL STANDBY RECOVERY NEEDED MOUNTED STANDBY
现在primary库角色已经成为“PHYSICAL STANDBY”，控制文件类型standby
5.6 切换备库为主库，备库状态检查
SQL> col controlfile_type format a20
SQL> select database_role,switchover_status,open_mode,controlfile_type from v$database;
DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE CONTROLFILE_TYPE
---------------- -------------------- -------------------- --------------------
PHYSICAL STANDBY TO PRIMARY READ ONLY WITH APPLY STANDBY
作用：检查切换的可能性
如果“TO PRIMARY” 备库可以顺利切换到主库
如果“SESSIONS ACTIVE”表示应然存在活动的会话，在切换时可以添加“with session shutdown”关键字在切换时终止所有存活会话。如果不加你需要等待900秒，这只是一个估计值，实际时间会更长。
备库执行切换命令
SQL> alter database commit to switchover to primary with session shutdown;
Database altered.
备库切换日志：
Sat May 28 19:30:17 2016
alter database commit to switchover to primary with session shutdown
Sat May 28 19:30:17 2016
ALTER DATABASE SWITCHOVER TO PRIMARY (ora1_dg)
Sat May 28 19:30:17 2016
Maximum wait for role transition is 15 minutes.
Switchover: Media recovery is still active
Role Change: Canceling MRP - no more redo to apply
Sat May 28 19:30:17 2016
MRP0: Background Media Recovery cancelled with status 16037
Sat May 28 19:30:17 2016
Errors in file /u01/app/oracle/diag/rdbms/ora1_dg/ora1_dg/trace/ora1_dg_pr00_2555.trc:
ORA-16037: user requested cancel of managed recovery operation
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Sat May 28 19:30:17 2016
Errors in file /u01/app/oracle/diag/rdbms/ora1_dg/ora1_dg/trace/ora1_dg_pr00_2555.trc:
ORA-16037: user requested cancel of managed recovery operation
Sat May 28 19:30:17 2016
MRP0: Background Media Recovery process shutdown (ora1_dg)
Sat May 28 19:30:18 2016
Role Change: Canceled MRP
Stopping Emon pool
All dispatchers and shared servers shutdown
CLOSE: killing server sessions.
CLOSE: all sessions shutdown successfully.
Stopping Emon pool
Sat May 28 19:30:18 2016
SMON: disabling cache recovery
Sat May 28 19:30:18 2016
Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/ora1_dg/ora1_dg/trace/ora1_dg_ora_2348.trc
SwitchOver after complete recovery through change 1971482
##切换成主库后需要清空在线redolog
Online logfile pre-clearing operation disabled by switchover
Online log /oradata/ora1_dg/redo01.log: Thread 1 Group 1 was previously cleared
Online log /oradata/ora1_dg/redo02.log: Thread 1 Group 2 was previously cleared
Online log /oradata/ora1_dg/redo03.log: Thread 1 Group 3 was previously cleared
##备库已经成为主库，状态为mounted
Standby became primary SCN: 1971480
Switchover: Complete - Database mounted as primary
Completed: alter database commit to switchover to primary with session shutdown
Sat May 28 19:30:50 2016
ARC2: Becoming the 'no SRL' ARCH
备库切换完成，查看状态
col controlfile_type format a20
select database_role,switchover_status,open_mode,controlfile_type from v$database;
DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE CONTROLFILE_TYPE
---------------- -------------------- -------------------- --------------------
PRIMARY NOT ALLOWED MOUNTED CURRENT
角色：primary 不可切换 状态mounted
5.7 无损切换完成，打开数据库
SQL> alter database open;
Database altered.
SQL> col controlfile_type format a20
SQL> select database_role,switchover_status,open_mode,controlfile_type from v$database;
DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE CONTROLFILE_TYPE
---------------- -------------------- -------------------- --------------------
PRIMARY TO STANDBY READ WRITE CURRENT
打开之后数据库状态为R/W可读写，现在可以使用新主库对外提供服务了
查询PDB数据库状态并打开PDB数据库
SQL> select DBID,con_id,dbid,NAME,OPEN_MODE from v$pdbs;
DBID CON_ID DBID NAME OPEN_MODE
---------- ---------- ---------- ------------------------------ ----------
1952071276 2 1952071276 PDB$SEED READ ONLY
2246627906 3 2246627906 PDB11 MOUNTED
491426823 4 491426823 PDB12 MOUNTED
SQL> alter pluggable database pdb11 open;
Pluggable database altered.
SQL> alter pluggable database pdb12 open;
Pluggable database altered.
备注：建议关闭数据库，重启启动下。
5.8 检查新主备数据库日志是否同步
新主库
col standby_dest for a12
col archived for a10
col applied for a10
col status for a10
select sequence#,standby_dest,archived,applied,status from v$archived_log
order by 1;
SEQUENCE# STANDBY_DEST ARCHIVED APPLIED STATUS
---------- ------------ ---------- ---------- ----------
97 YES YES NO A
98 NO YES NO A
98 YES YES NO A
99 NO YES NO A
99 YES YES NO A
新备库日志是否应用
SQL> col standby_dest for a12
SQL> col archived for a10
SQL> col applied for a10
SQL> col status for a10
SQL> select sequence#,standby_dest,archived,applied,status from v$archived_log
2 order by 1;
SEQUENCE# STANDBY_DEST ARCHIVED APPLIED STATUS
---------- ------------ ---------- ---------- ----------
95 NO YES YES A
95 YES YES YES A
96 YES YES NO A
96 NO YES NO A
97 NO YES NO A
98 NO YES NO A
99 NO YES NO A
经过对比发现96,97,98,99 号归档日志没有在备库应用
5.9 open新备库并引用日志
查看新备库并打开数据库
col FLASHBACK_ON for a10
col SWITCHOVER_STATUS for a20
SQL> set line 150
SQL> select NAME, OPEN_MODE, DATABASE_ROLE, FLASHBACK_ON, SWITCHOVER_STATUS from v$database;
NAME OPEN_MODE DATABASE_ROLE FLASHBACK_ SWITCHOVER_STATUS
--------- -------------------- ---------------- ---------- --------------------
ORA1 MOUNTED PHYSICAL STANDBY NO RECOVERY NEEDED
SQL> alter database open;
Database altered.
启动2个MRP恢复进程开始日志应用
SQL> alter database recover managed standby database disconnect from session parallel 2;
Database altered.
新备库恢复日志
alter database recover managed standby database disconnect from session parallel 2
Sat May 28 19:59:23 2016
Attempt to start background Managed Standby Recovery process (ora1)
Starting background process MRP0
Sat May 28 19:59:23 2016
MRP0 started with pid=30, OS id=4152
Sat May 28 19:59:23 2016
MRP0: Background Managed Standby Recovery process started (ora1)
Sat May 28 19:59:28 2016
Started logmerger process
Sat May 28 19:59:28 2016
Managed Standby Recovery starting Real Time Apply
Sat May 28 19:59:28 2016
Parallel Media Recovery started with 2 slaves
Sat May 28 19:59:28 2016
Waiting for all non-current ORLs to be archived...
Sat May 28 19:59:28 2016
All non-current ORLs have been archived.
Clearing online redo logfile 1 /oradata/ora1/redo01.log
Clearing online log 1 of thread 1 sequence number 94
Completed: alter database recover managed standby database disconnect from session parallel 2
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 /oradata/ora1/redo02.log
Clearing online log 2 of thread 1 sequence number 95
Clearing online redo logfile 2 complete
Clearing online redo logfile 3 /oradata/ora1/redo03.log
Clearing online log 3 of thread 1 sequence number 96
Clearing online redo logfile 3 complete
Sat May 28 19:59:35 2016
Media Recovery Log /oradata/arch/1_97_912910029.dbf
Sat May 28 19:59:35 2016
Media Recovery Log /oradata/arch/1_98_912910029.dbf
Sat May 28 19:59:38 2016
Media Recovery Log /oradata/arch/1_99_912910029.dbf
Media Recovery Waiting for thread 1 sequence 100 (in transit)
Sat May 28 19:59:38 2016
Recovery of Online Redo Log: Thread 1 Group 4 Seq 100 Reading mem 0
Mem# 0: /oradata/ora1/standby_redo04.log
小结：完成了一次SWITCH OVER，原主库切换成备库，原备库切换成主库
6. RAC数据库切换
6.1 主数据库状态检查
SQL> select database_role,switchover_status,open_mode from v$database;
DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE
---------------- -------------------- --------------------
PRIMARY TO STANDBY READ WRITE
备注：切换前关闭主数据库的其他实例，只留下一个实例操作，进行数据库切换。
主库执行切换命令
SQL> alter database commit to switchover to physical standby with session shutdown;
Database altered.
SQL> shutdown immediate;
ORA-01012: not logged on
SQL> startup mount;
ORACLE instance started.
Total System Global Area 855638016 bytes
Fixed Size 2930128 bytes
Variable Size 675285552 bytes
Database Buffers 171966464 bytes
Redo Buffers 5455872 bytes
Database mounted.
备注：其他实例不需要启动到Mount，还是关闭状态，不需要操作。
查询下数据库状态
col controlfile_type format a20
select database_role,switchover_status,open_mode,controlfile_type from v$database;
DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE CONTROLFILE_TYPE
---------------- -------------------- -------------------- --------------------
PHYSICAL STANDBY RECOVERY NEEDED MOUNTED STANDBY
现在primary库角色已经成为“PHYSICAL STANDBY”，控制文件类型standby
6.2 切换备库为主库
备库状态检查
SQL> col controlfile_type format a20
SQL> select database_role,switchover_status,open_mode,controlfile_type from v$database;
DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE CONTROLFILE_TYPE
---------------- -------------------- -------------------- --------------------
PHYSICAL STANDBY TO PRIMARY READ ONLY WITH APPLY STANDBY
作用：检查切换的可能性
如果“TO PRIMARY” 备库可以顺利切换到主库
如果“SESSIONS ACTIVE”表示应然存在活动的会话，在切换时可以添加“with session shutdown”关键字在切换时终止所有存活会话。如果不加你需要等待900秒，这只是一个估计值，实际时间会更长。
备注：切换前关闭备数据库的其他实例，只留下一个实例操作，进行数据库切换。
备库执行切换命令
SQL> alter database commit to switchover to primary with session shutdown;
Database altered.
备库切换完成，查看状态
col controlfile_type format a20
select database_role,switchover_status,open_mode,controlfile_type from v$database;
DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE CONTROLFILE_TYPE
---------------- -------------------- -------------------- --------------------
PRIMARY NOT ALLOWED MOUNTED CURRENT
角色：primary 不可切换 状态mounted
6.3 Open新主RAC数据库
SQL> alter database open;
Database altered.
SQL> col controlfile_type format a20
SQL> select database_role,switchover_status,open_mode,controlfile_type from v$database;
DATABASE_ROLE SWITCHOVER_STATUS OPEN_MODE CONTROLFILE_TYPE
---------------- -------------------- -------------------- --------------------
PRIMARY TO STANDBY READ WRITE CURRENT
打开之后数据库状态为R/W可读写，现在可以使用新主库对外提供服务了
查询PDB数据库状态并打开PDB数据库
SQL> select DBID,con_id,dbid,NAME,OPEN_MODE from v$pdbs;
DBID CON_ID DBID NAME OPEN_MODE
---------- ---------- ---------- ------------------------------ ----------
1952071276 2 1952071276 PDB$SEED READ ONLY
2246627906 3 2246627906 PDB11 MOUNTED
491426823 4 491426823 PDB12 MOUNTED
SQL> alter pluggable database pdb11 open;
Pluggable database altered.
SQL> alter pluggable database pdb12 open;
Pluggable database altered.
备注：
(1).打开RAC集群中的所有数据库。
(2).建议关闭数据库，重启启动下。
6.4 Open新备RAC数据库
SQL> alter database open;
Database altered.
SQL> select DBID,con_id,dbid,NAME,OPEN_MODE from v$pdbs;
DBID CON_ID DBID NAME OPEN_MODE
---------- ---------- ---------- ------------------------------ ----------
1952071276 2 1952071276 PDB$SEED READ ONLY
2246627906 3 2246627906 PDB11 MOUNTED
491426823 4 491426823 PDB12 MOUNTED
SQL> alter pluggable database pdb11 open;
Pluggable database altered.
SQL> alter pluggable database pdb12 open;
Pluggable database altered.
6.5 启动新备库实时恢复
启动2个MRP恢复进程开始日志应用
SQL> alter database recover managed standby database disconnect from session parallel 2;
Database altered.
6.6 新主备数据库日志检查
set linesize 150
set pagesize 400
col standby_dest for a12
col archived for a10
col applied for a10
col status for a10
select sequence#,standby_dest,archived,applied,status from v$archived_log
order by 1;
小结：至此RAC数据库主备数据库切换完成。