首页 / ORACLE / Oracle12cR1 Data Guard 实施文档

Oracle12cR1 Data Guard 实施文档

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了Oracle12cR1 Data Guard 实施文档，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含49438字，纯文字阅读大概需要71分钟。

内容图文

1. Data Guard概述. 5

1.1 DG 日志传递模式-图文并茂. 5

2. DG 搭建过程. 6

2.1 主数据检查. 6

2.2 主数据库添加standby redo log 7

2.3 主数据库创建参数文件. 8

2.4 备数据库启动到nomount状态. 8

2.5 主数据库创建备库控制文件. 10

2.6 主数据库配置网络参数. 11

2.7 备数据库网络参数配置. 13

2.8 备库密码文件. 15

2.9 主备数据库网络测试. 15

2.10 主数据库全库备份. 16

2.11 备库恢复数据库. 17

2.12 主备数据库配置. 22

3. 测试ADG 23

3.1 备库操作. 23

3.2 主数据库操作，创建local测试用户. 23

3.3 主数据库操作，创建common测试用户. 24

4. 常用查询命令. 25

4.1 主库状态查询. 25

4.2 备库状态查询. 26

4.3 查询standby redo log 28

4.4 查询PDB数据库状态. 28

4.5 打开PDB数据库. 29

4.6 主数据库查询归档应用情况. 29

4.7 备库归档应用情况. 30

4.8 备库启动MRP恢复. 30

4.9 主备数据库切换命令. 30

5. 单实例主备数据库切换. 31

5.1 实现SWITCH OVER切换. 31

5.2 主数据库状态检查. 33

5.3 切换时主备数据库日志查看. 33

5.4 重启主库到mount 状态. 36

5.5 主数据库检查. 36

5.6 切换备库为主库，备库状态检查. 36

5.7 无损切换完成，打开数据库. 38

5.8 检查新主备数据库日志是否同步. 38

5.9 open新备库并引用日志. 39

6. RAC数据库切换. 41

6.1 主数据库状态检查. 41

6.2 切换备库为主库. 41

6.3 Open新主RAC数据库. 42

6.4 Open新备RAC数据库. 43

6.5 启动新备库实时恢复. 43

6.6 新主备数据库日志检查. 43

1. Data Guard概述

1.1 DG 日志传递模式-图文并茂

了解知道原理即可

LGWR(异步)传输日志模式：先把redo数据写入本地在线日志后在传递给备库在线日志并应用

Oracle12cR1 Data Guard 实施文档 - 文章图片

LGWR(同步)传输日志模式：主库触发LGWR->传给->备库重做日志->备库接收到重做日志后马上应用

Oracle12cR1 Data Guard 实施文档 - 文章图片

ARCH传输日志模式：主库触发ARCH->传给->备库归档日志->备库接受到归档日志后马上应用->当归档日志目录快满时需要手工删除或备份到别处

Oracle12cR1 Data Guard 实施文档 - 文章图片

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

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

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

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

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

2 oracle数据库

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

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

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.