Oracle 单实例DG搭建详解

约定不等于承诺〃 2022-06-14 01:23 210阅读 0赞

OracleData Guard for Linux

实

施

部

署

方

案

[**1****、文档介绍**... 1][1_... 1]

[**2** **、OracleData Guard** **介绍**... 1][2_ _OracleData Guard_ _... 1]

[**2.1**     **Data Guard****环境拓展**... 1][2.1_ _Data Guard_... 1]

[**2.2**     **Data Guard****特点**... 1][2.2_ _Data Guard_... 1]

[**3****、OracleDataGurad****单实例部署**... 2][3_OracleDataGurad_... 2]

[**3.1****安装环境**... 2][3.1_... 2]

[**3.2****主数据库配置**... 2][3.2_... 2]

[3.2.1 设置数据库归档... 2][3.2.1 _... 2]

[3.2.2 设置数据库闪回... 3][3.2.2 _... 3]

[3.2.3 设置数据库强制归档... 4][3.2.3 _... 4]

[3.2.4 添加STANDBY 日志文件... 5][3.2.4 _STANDBY _... 5]

[3.2.5 修改参数文件... 7][3.2.5 _... 7]

[3.2.6 修改监听配置文件... 8][3.2.6 _... 8]

[3.2.7 修改TNS配置文件... 8][3.2.7 _TNS_... 8]

[3.2.8 重启监听服务... 9][3.2.8 _... 9]

[3.2.9 拷贝参数文件... 9][3.2.9 _... 9]

[3.2.10 拷贝密码文件... 9][3.2.10 _... 9]

[**3.3****备数据库配置**... 10][3.3_... 10]

[3.3.1      修改参数文件... 10][3.3.1_ _... 10]

[3.3.2 修改密码文件... 11][3.3.2 _... 11]

[3.3.3 创建相应的文件目录... 11][3.3.3 _... 11]

[3.3.4 修改监听配置文件... 11][3.3.4 _... 11]

[3.3.5 修改TNS配置文件... 12][3.3.5 _TNS_... 12]

[3.3.6 重启监听服务... 12][3.3.6 _... 12]

[3.3.7 启动数据库到nomount状态... 12][3.3.7 _nomount_... 12]

[3.3.8 验证监听和TNS配置... 13][3.3.8 _TNS_... 13]

[3.3.9 恢复数据库... 14][3.3.9 _... 14]

[3.3.10 开启实时同步... 18][3.3.10 _... 18]

[3.3.11 开启flashback. 19][3.3.11 _flashback. 19]

[**3.4****验证DG****同步**... 19][3.4_DG_... 19]

[**3.5DG****切换与恢复**... 22][3.5DG_... 22]

[3.5.1 switchover. 22][]

[3.5.2 failover. 24][]

[3.5.2 failover恢复... 25][3.5.2 failover_... 25]

# 1、文档介绍 #

本实施方案主要对Oracle DataGuard实施部署作相应的说明。以便实施人员能根据当前业务特点，规划、建设符合高可用、高可靠的数据库集群系统。具体由Oracle DG环境拓扑、Oracle单机数据库规划部分构成！

# 2  、Oracle Data Guard 介绍 #

## 2.1  Data Guard环境拓展 ##

## 2.2  Data Guard特点                            ##

数据库服务器采用DATAGUARD灾备模式，可以满足对可用性有特殊需求的应

用，具备以下特点：

1、  需要冗余的服务器设备。该模式需要有冗余的服务器硬件。硬件成本较高。

2、  需要冗余的存储设备。主机和备机都需要同样的存储空间，成本较高。

3、  安装配置比较复杂。该模式比单节点、单实例的模式配置复杂一些，需要

更多的配置步骤。

4、  管理维护成本高。该模式对维护人员的要求较高，维护成本高。

5、  具备一定的容灾特性。当主机整个数据库系统不可用并短期内无法恢复

时，可以把数据库系统切换到备机上，具备容灾的功能。

6、  备机可以用作只读查询。备机可以切换到只读状态供报表之类的查询操

作，减轻主机的压力。

# 3、Oracle DataGurad单实例部署 #

## 3.1安装环境 ##

在主机1上安装数据库软件，并建监听和实例，在主机2上安装数据库软件，并建监听，但不建实例。

<table> 
 <tbody> 
 <tr> 
 <td> &nbsp; </td> 
 <td> 主机1（主库） </td> 
 <td> 主机2（备库） </td> 
 </tr> 
 <tr> 
 <td> 操作系统 </td> 
 <td> Centos6.5 64位 </td> 
 <td> Centos6.5 64位 </td> 
 </tr> 
 <tr> 
 <td> 主机名 </td> 
 <td> cebpm </td> 
 <td> cebpms </td> 
 </tr> 
 <tr> 
 <td> IP </td> 
 <td> 192.168.1.141 </td> 
 <td> 192.168.1.143 </td> 
 </tr> 
 <tr> 
 <td> 数据库软件版本 </td> 
 <td> oracle 11.2.0.4 </td> 
 <td> oracle 11.2.0.4 </td> 
 </tr> 
 <tr> 
 <td> ORACLE_BASE </td> 
 <td> /u01/app/oracle </td> 
 <td> /u01/app/oracle </td> 
 </tr> 
 <tr> 
 <td> ORACLE_HOME </td> 
 <td> /u01/app/oracle/product/11.2.0/db_1 </td> 
 <td> /u01/app/oracle/product/11.2.0/db_1 </td> 
 </tr> 
 <tr> 
 <td> ORACLE_SID </td> 
 <td> cebpm </td> 
 <td> &nbsp; </td> 
 </tr> 
 <tr> 
 <td> 闪回区 </td> 
 <td> 开启 </td> 
 <td> &nbsp; </td> 
 </tr> 
 <tr> 
 <td> 归档 </td> 
 <td> 开启 </td> 
 <td> &nbsp; </td> 
 </tr> 
 </tbody> 
</table>

## 3.2主数据库配置 ##

### 3.2.1  设置数据库归档 ###

查看数据库是否运行在归档模式：

SQL> archive log list;

Database log mode         No Archive Mode

Automatic archival       Disabled     \#未开启归档

Archive destination      USE\_DB\_RECOVERY\_FILE\_DEST

Oldest online log sequence     117

Current log sequence          119

SQL>

备注：如果数据库已经开启归档，下面的操纵可以忽略。

如上所示未开启归档，可按下面方法开启数据库归档

SQL> shutdownimmediate    \#关闭数据库

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startupmount;    \#启动到mount状态

ORACLE instance started.

Total System Global Area  688959488 bytes

Fixed Size          2256432 bytes

Variable Size        566231504 bytes

Database Buffers     117440512 bytes

Redo Buffers           3031040 bytes

Database mounted.

SQL> alterdatabase archivelog;    \#开启归档

Database altered.

SQL> alterdatabase open;   \#open数据库

Database altered.

SQL> altersystem set  log\_archive\_dest\_1='location=/data/CEBPM/archivelog';  \#设置归档路径

System altered.

SQL> archive log list;

Database log mode         Archive Mode

Automatic archival       Enabled

Archive destination       /data/CEBPM/archivelog

Oldest online log sequence     119

Next log sequence to archive   121

Current log sequence          121

SQL>

### 3.2.2  设置数据库闪回 ###

验证是否开启闪回

SQL> select flashback\_on fromv$database;

FLASHBACK\_ON

\------------------

SQL>

备注：如果数据库已经开启flashback，那么下面步骤可忽略。

如上显示，该数据库未开启flashback，可按下面方法开启。

SQL> altersystem set db\_recovery\_file\_dest='/data/CEBPM';   \#设置闪回去路径

System altered.

SQL> altersystem set db\_recovery\_file\_dest\_size='5G';  \#设置闪回区大小

System altered.

SQL> shutdownimmediate;  \#关闭数据库

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area  688959488 bytes

Fixed Size          2256432 bytes

Variable Size        566231504 bytes

Database Buffers     117440512 bytes

Redo Buffers           3031040 bytes

Database mounted.

SQL> alterdatabase flashback on;    \#开启闪回

Database altered.

SQL> alterdatabase open;   \#open数据库

Database altered.

SQL> selectflashback\_on from v$database;   \#验证是否开启

FLASHBACK\_ON

\------------------

YES

SQL>

### 3.2.3  设置数据库强制归档 ###

验证是否开启focelogging

SQL>select force\_logging from v$database;

FOR

\---

如果数据库已经开启force logging，那么下面步骤可忽略。

如上可以看出数据库未开启，则按下面步骤执行：

SQL>alter database force logging;  \#开启force logging

Databasealtered.

SQL>select force\_logging fromv$database;  \#验证

FOR

\---

YES

SQL>

### 3.2.4  添加STANDBY  日志文件 ###

在备库，当RFS进程接受到日志后，就将其写入Standby日志文件里，备库的Standby日志文件可以看做是主库在线日志文件的一个镜像，当主库做日志切换时，备库的Standby日志也做相应的切换，切换后的Standby日志由备库的ARCH进程归档。

Oracle规定备库的Standby日志文件大小不能小于主库在线日志文件最大的一个，一般情况下，为了管理方便，最好把所有的在线日志和Standby日志大小设为一样。

通过下面语句可以查询主库在线日志的大小和组数：

SQL> select group\#,bytes/1024/1024from v$log;

GROUP\# BYTES/1024/1024

\---------- ---------------

1      50

2      50

3      50

SQL>

通过下面的语句可以查询备库Standby日志的大小和组数：

SQL> selectgroup\#,bytes/1024/1024 from v$standby\_log;

no rows selected

创建standby logfile

SQL> alter databaseadd standby logfile group 11 '/data/CEBPM/onlinelog/redo11\_stb01.log' size 50M;

Database altered.

SQL> alterdatabase add standby logfile group 12 '/data/CEBPM/onlinelog/redo12\_stb01.log'size 50M;

Database altered.

SQL> alterdatabase add standby logfile group 13 '/data/CEBPM/onlinelog/redo13\_stb01.log'size 50M;

Database altered.

SQL> alterdatabase add standby logfile group 14 '/data/CEBPM/onlinelog/redo14\_stb01.log'size 50M;

Database altered.

SQL> selectgroup\#,bytes/1024/1024 from v$standby\_log;

GROUP\# BYTES/1024/1024

\---------- ---------------

11      50

12      50

13      50

14      50

SQL>

### 3.2.5  修改参数文件 ###

SQL> altersystem set log\_archive\_config='DG\_CONFIG=(cebpm,cebpms)';

System altered.

SQL> altersystem set log\_archive\_dest\_2='SERVICE=cebpmsVALID\_FOR=(ONLINE\_LOGFILES,PRIMARY\_ROLE) DB\_UNIQUE\_NAME=cebpms' scope=spfile;

System altered.

SQL> altersystem set log\_archive\_dest\_state\_1='enable';

System altered.

SQL> altersystem set log\_archive\_dest\_state\_2='enable';

System altered.

SQL> altersystem set db\_file\_name\_convert='/data/CEBPMS/datafile','/data/CEBPM/datafile'scope=spfile;

System altered.

SQL> altersystem setlog\_file\_name\_convert='/data/CEBPMS/onlinelog','/data/CEBPM/onlinelog'scope=spfile;

System altered.

SQL> altersystem set fal\_server='cebpms';

System altered.

SQL> altersystem set fal\_client='cebpm';

System altered.

SQL> altersystem set standby\_file\_management='AUTO';

System altered.

注意：上面修改的参数有的需要重启数据库才能生效，下面为重启数据库步骤

SQL> shutdown immediate

Databaseclosed.

Databasedismounted.

ORACLEinstance shut down.

SQL> startup

ORACLEinstance started.

TotalSystem Global Area  688959488 bytes

Fixed Size         2256432 bytes

VariableSize       566231504 bytes

DatabaseBuffers    117440512 bytes

RedoBuffers           3031040 bytes

Databasemounted.

Databaseopened.

SQL>

### 3.2.6  修改监听配置文件 ###

cebpm:/home/oracle@cebpm>vi/u01/app/oracle/product/11.2.0/db\_1/network/admin/listener.ora

LISTENER =

(DESCRIPTION\_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.141)(PORT = 1521))

)

ADR\_BASE\_LISTENER = /u01/app/oracle

注意：如果主库上的监听之前已经配置过，这里可以忽略该步骤。

### 3.2.7  修改TNS配置文件 ###

cebpm:/home/oracle@cebpm>vi/u01/app/oracle/product/11.2.0/db\_1/network/admin/tnsnames.ora

cebpm =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.141)(PORT = 1521))

(CONNECT\_DATA =

(SERVER = DEDICATED)

(SERVICE\_NAME = cebpm)

)

cebpms =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.143)(PORT = 1521))

(CONNECT\_DATA =

(SERVER = DEDICATED)

(SERVICE\_NAME = cebpms)

)

### 3.2.8  重启监听服务 ###

cebpm:/home/oracle@cebpm>lsnrctl stop

cebpm:/home/oracle@cebpm>lsnrctl start

### 3.2.9  拷贝参数文件 ###

利用spfile创建pfile

SQL> create pfile='/tmp/initcebpms' fromspfile;

File created.

将pfile拷贝到备库

cebpm:/home/oracle@cebpm>scp /tmp/initcebpms.ora192.168.1.143:/u01/app/oracle/product/11.2.0/db\_1/dbs

oracle@192.168.1.143's password:

initcebpms.ora                                                                              100% 1397     1.4KB/s   00:00

cebpm:/home/oracle@cebpm>

### 3.2.10  拷贝密码文件 ###

cebpm:/u01/app/oracle/product/11.2.0/db\_1/dbs@cebpm>pwd

/u01/app/oracle/product/11.2.0/db\_1/dbs

cebpm:/u01/app/oracle/product/11.2.0/db\_1/dbs@cebpm>scp orapwcebpm 192.168.1.143:/u01/app/oracle/product/11.2.0/db\_1/dbs

oracle@192.168.1.143's password:

orapwcebpm                                                                                  100% 1536     1.5KB/s   00:00

cebpm:/u01/app/oracle/product/11.2.0/db\_1/dbs@cebpm>

## 3.3备数据库配置 ##

### 3.3.1  修改参数文件 ###

修改之前从主库拷贝过来的那个参数文件，具体如下：

cebpms:/u01/app/oracle/product/11.2.0/db\_1/dbs@cebpms>vi initcebpms.ora

如下所示：

\*.audit\_file\_dest='/u01/app/oracle/admin/cebpms/adump'

\*.audit\_trail='db'

\*.compatible='11.2.0.4.0'

\*.control\_files='/data/CEBPMS/controlfile/o1\_mf\_dm1flhj3\_.ctl'

\*.db\_block\_size=8192

\*.db\_create\_file\_dest='/data'

\*.db\_domain=''

\*.db\_file\_name\_convert='/data/CEBPM/datafile','/data/CEBPMS/datafile'

\*.db\_name='cebpm'

\*.db\_recovery\_file\_dest='/data/CEBPMS'

\*.db\_recovery\_file\_dest\_size=5368709120

\*.db\_unique\_name='cebpms'

\*.diagnostic\_dest='/u01/app/oracle'

\*.dispatchers='(PROTOCOL=TCP) (SERVICE=cebpmsXDB)'

\*.fal\_client='cebpms'

\*.fal\_server='cebpm'

\*.log\_archive\_config='DG\_CONFIG=(cebpms,cebpm)'

\*.log\_archive\_dest\_1='location=/data/CEBPMS/archivelog'

\*.log\_archive\_dest\_2='SERVICE=cebpm VALID\_FOR=(ONLINE\_LOGFILES,PRIMARY\_ROLE)DB\_UNIQUE\_NAME=cebpm'

\*.log\_archive\_dest\_state\_1='enable'

\*.log\_archive\_dest\_state\_2='enable'

\*.log\_archive\_format='%t\_%s\_%r.arc'

\*.log\_file\_name\_convert='/data/CEBPM/onlinelog','/data/CEBPMS/onlinelog'

\*.memory\_target=691011584

\*.open\_cursors=300

\*.processes=200

\*.remote\_login\_passwordfile='EXCLUSIVE'

\*.sessions=1000

\*.standby\_file\_management='AUTO'

\*.undo\_tablespace='UNDOTBS1'

注意:尤其注意红色标注的地方

### 3.3.2  修改密码文件 ###

修改之前从主库拷贝过来的密码文件，具体如下：

cebpms:/u01/app/oracle/product/11.2.0/db\_1/dbs@cebpms>mv orapwcebpm orapwcebpms

### 3.3.3  创建相应的文件目录 ###

根据上面修改的参数文件，为备库创建相应的文件目录

cebpms:/home/oracle@cebpms>mkdir -p /u01/app/oracle/admin/cebpms/adump

cebpms:/home/oracle@cebpms>mkdir -p /data/CEBPMS/controlfile

cebpms:/home/oracle@cebpms>mkdir -p /data/CEBPMS/datafile

cebpms:/home/oracle@cebpms>mkdir -p /data/CEBPMS

cebpms:/home/oracle@cebpms>mkdir -p /data/CEBPMS/archivelog

cebpms:/home/oracle@cebpms>mkdir -p /data/CEBPMS/onlinelog

### 3.3.4  修改监听配置文件 ###

cebpms:/u01/app/oracle/product/11.2.0/db\_1/network/admin@cebpms>vi listener.ora

具体如下：

LISTENER =

(DESCRIPTION\_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.143)(PORT = 1521))

)

SID\_LIST\_LISTENER =

(SID\_LIST =

(SID\_DESC =

(GLOBAL\_DBNAME = cebpms)

(ORACLE\_HOME = /u01/app/oracle/product/11.2.0/db\_1)

(SID\_NAME = cebpms)

)

ADR\_BASE\_LISTENER = /u01/app/oracle

### 3.3.5  修改TNS配置文件 ###

cebpms:/u01/app/oracle/product/11.2.0/db\_1/network/admin@cebpms>vi tnsnames.ora

具体如下：

cebpm =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.1.141)(PORT = 1521))

(CONNECT\_DATA =

(SERVER = DEDICATED)

(SERVICE\_NAME = cebpm)

)

cebpms =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST =192.168.1.143)(PORT = 1521))

(CONNECT\_DATA =

(SERVER = DEDICATED)

(SERVICE\_NAME = cebpms)

)

### 3.3.6  重启监听服务 ###

cebpms:/data/CEBPMS@cebpms>lsnrctl stop

cebpms:/data/CEBPMS@cebpms>lsnrctl start

### 3.3.7  启动数据库到nomount状态 ###

SQL> startupnomount pfile='/u01/app/oracle/product/11.2.0/db\_1/dbs/initcebpms.ora';

ORACLE instance started.

Total System Global Area  688959488 bytes

Fixed Size          2256432 bytes

Variable Size        566231504 bytes

Database Buffers     117440512 bytes

Redo Buffers           3031040 bytes

SQL> createspfile from pfile;   \#创建spfile

File created.

SQL>

### 3.3.8  验证监听和TNS配置 ###

主库上验证:

cebpm:/home/oracle@cebpm>sqlplus sys/oracle@cebpm as sysdba

SQL\*Plus: Release 11.2.0.4.0 Production onTue Jun 6 10:34:42 2017

Connected to:

Oracle Database 11g Enterprise EditionRelease 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Miningand Real Application Testing options

SQL> exit

Disconnected from Oracle Database 11gEnterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Miningand Real Application Testing options

cebpm:/home/oracle@cebpm>sqlplus sys/oracle@cebpms as sysdba

SQL\*Plus: Release 11.2.0.4.0 Production onTue Jun 6 10:34:50 2017

Connected to:

Oracle Database 11g Enterprise EditionRelease 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Miningand Real Application Testing options

SQL>

备库上验证：

cebpms:/home/oracle@cebpms>sqlplus sys/oracle@cebpm as sysdba

SQL\*Plus: Release 11.2.0.4.0 Production onTue Jun 6 11:05:01 2017

Connected to:

Oracle Database 11g Enterprise EditionRelease 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Miningand Real Application Testing options

SQL> exit

Disconnected from Oracle Database 11gEnterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Miningand Real Application Testing options

cebpms:/home/oracle@cebpms>sqlplus sys/oracle@cebpms as sysdba

SQL\*Plus: Release 11.2.0.4.0 Production onTue Jun 6 11:05:05 2017

Connected to:

Oracle Database 11g Enterprise EditionRelease 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Miningand Real Application Testing options

SQL>

注意：该步骤一定要在主备库上都能通过才能执行下面步骤

### 3.3.9  恢复数据库 ###

cebpms:/home/oracle@cebpms>rman target sys/oracle@cebpm auxiliary sys/oracle@cebpms

Recovery Manager: Release 11.2.0.4.0 -Production on Tue Jun 6 11:13:43 2017

connected to target database: CEBPM(DBID=3677012495)

connected to auxiliary database: CEBPM (notmounted)

RMAN> duplicatetarget database for standby from active database;

具体执行过程显示如下：

Starting Duplicate Db at 2017/06/06 11:14:26

using target database control file insteadof recovery catalog

allocated channel: ORA\_AUX\_DISK\_1

channel ORA\_AUX\_DISK\_1: SID=19 devicetype=DISK

contents of Memory Script:

backup as copy reuse

targetfile '/u01/app/oracle/product/11.2.0/db\_1/dbs/orapwcebpm' auxiliary format

'/u01/app/oracle/product/11.2.0/db\_1/dbs/orapwcebpms'   ;

executing Memory Script

Starting backup at 2017/06/06 11:14:29

allocated channel: ORA\_DISK\_1

channel ORA\_DISK\_1: SID=43 device type=DISK

Finished backup at 2017/06/06 11:14:31

contents of Memory Script:

backup as copy current controlfile for standby auxiliary format '/data/CEBPMS/controlfile/o1\_mf\_dm1flhj3\_.ctl';

sql clone "create spfile from memory";

shutdown clone immediate;

startup clone nomount;

sql clone "alter system set control\_files =

''/data/CEBPMS/controlfile/o1\_mf\_dm1flhj3\_.ctl'' comment=

''Set by RMAN'' scope=spfile";

shutdown clone immediate;

startup clone nomount;

executing Memory Script

Starting backup at 2017/06/06 11:14:32

using channel ORA\_DISK\_1

channel ORA\_DISK\_1: starting datafile copy

copying standby control file

output filename=/u01/app/oracle/product/11.2.0/db\_1/dbs/snapcf\_cebpm.ftag=TAG20170606T105032 RECID=5 STAMP=945946234

channel ORA\_DISK\_1: datafile copy complete,elapsed time: 00:00:03

Finished backup at 2017/06/06 11:14:36

sql statement: create spfile from memory

Oracle instance shut down

connected to auxiliary database (notstarted)

Oracle instance started

Total System Global Area     688959488 bytes

Fixed Size                     2256432 bytes

Variable Size                566231504 bytes

Database Buffers             117440512 bytes

Redo Buffers                   3031040 bytes

sql statement: alter system set  control\_files =   ''/data/CEBPMS/controlfile/o1\_mf\_dm1flhj3\_.ctl''comment= ''Set by RMAN'' scope=spfile

Oracle instance shut down

connected to auxiliary database (notstarted)

Oracle instance started

Total System Global Area     688959488 bytes

Fixed Size                     2256432 bytes

Variable Size                566231504 bytes

Database Buffers             117440512 bytes

Redo Buffers                   3031040 bytes

contents of Memory Script:

sql clone 'alter database mount standby database';

executing Memory Script

sql statement: alter database mount standbydatabase

contents of Memory Script:

set newname for tempfile  1 to

"/data/CEBPMS/datafile/o1\_mf\_temp\_dm1fp4bs\_.tmp";

switch clone tempfile all;

set newname for datafile  1 to

"/data/CEBPMS/datafile/o1\_mf\_system\_dm1flxkw\_.dbf";

set newname for datafile  2 to

"/data/CEBPMS/datafile/o1\_mf\_sysaux\_dm1fnw5v\_.dbf";

set newname for datafile  3 to

"/data/CEBPMS/datafile/o1\_mf\_undotbs1\_dm1foow9\_.dbf";

set newname for datafile  4 to

"/data/CEBPMS/datafile/o1\_mf\_users\_dm1fqcrp\_.dbf";

backup as copy reuse

datafile  1 auxiliary format

"/data/CEBPMS/datafile/o1\_mf\_system\_dm1flxkw\_.dbf"   datafile

2auxiliary format

"/data/CEBPMS/datafile/o1\_mf\_sysaux\_dm1fnw5v\_.dbf"  datafile

3auxiliary format

"/data/CEBPMS/datafile/o1\_mf\_undotbs1\_dm1foow9\_.dbf"   datafile

4auxiliary format

"/data/CEBPMS/datafile/o1\_mf\_users\_dm1fqcrp\_.dbf"   ;

sql 'alter system archive log current';

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to/data/CEBPMS/datafile/o1\_mf\_temp\_dm1fp4bs\_.tmp in control file

executing command: SET NEWNAME

Starting backup at 2017/06/06 11:15:06

using channel ORA\_DISK\_1

channel ORA\_DISK\_1: starting datafile copy

input datafile file number=00001name=/data/CEBPM/datafile/o1\_mf\_system\_dm1flxkw\_.dbf

output filename=/data/CEBPMS/datafile/o1\_mf\_system\_dm1flxkw\_.dbf tag=TAG20170606T105107

channel ORA\_DISK\_1: datafile copy complete,elapsed time: 00:01:15

channel ORA\_DISK\_1: starting datafile copy

input datafile file number=00002name=/data/CEBPM/datafile/o1\_mf\_sysaux\_dm1fnw5v\_.dbf

output filename=/data/CEBPMS/datafile/o1\_mf\_sysaux\_dm1fnw5v\_.dbf tag=TAG20170606T105107

channel ORA\_DISK\_1: datafile copy complete,elapsed time: 00:01:05

channel ORA\_DISK\_1: starting datafile copy

input datafile file number=00003name=/data/CEBPM/datafile/o1\_mf\_undotbs1\_dm1foow9\_.dbf

output file name=/data/CEBPMS/datafile/o1\_mf\_undotbs1\_dm1foow9\_.dbftag=TAG20170606T105107

channel ORA\_DISK\_1: datafile copy complete,elapsed time: 00:00:35

channel ORA\_DISK\_1: starting datafile copy

input datafile file number=00004name=/data/CEBPM/datafile/o1\_mf\_users\_dm1fqcrp\_.dbf

output filename=/data/CEBPMS/datafile/o1\_mf\_users\_dm1fqcrp\_.dbf tag=TAG20170606T105107

channel ORA\_DISK\_1: datafile copy complete,elapsed time: 00:00:03

Finished backup at 2017/06/06 11:18:07

sql statement: alter system archive logcurrent

contents of Memory Script:

switch clone datafile all;

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy RECID=5 STAMP=945947890file name=/data/CEBPMS/datafile/o1\_mf\_system\_dm1flxkw\_.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=6 STAMP=945947890file name=/data/CEBPMS/datafile/o1\_mf\_sysaux\_dm1fnw5v\_.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=7 STAMP=945947890file name=/data/CEBPMS/datafile/o1\_mf\_undotbs1\_dm1foow9\_.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=8 STAMP=945947890file name=/data/CEBPMS/datafile/o1\_mf\_users\_dm1fqcrp\_.dbf

Finished Duplicate Db at2017/06/06 11:18:25

RMAN>

### 3.3.10  开启实时同步 ###

SQL> alterdatabase open;

Database altered.

SQL> alter databaserecover managed standby database using current logfile disconnect from session;

Database altered.

SQL>

### 3.3.11  开启flashback ###

上面DataGuard搭建好之后，千万不要忘了把备库的flashback打开，具体如下：

SQL> alterdatabase recover managed standby database cancel;  \#取消实时同步

Database altered.

SQL> shutdownimmediate  \#关闭数据库

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startupmount   \#打开到mount状态

ORA-32004: obsolete or deprecatedparameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area  688959488 bytes

Fixed Size          2256432 bytes

Variable Size        566231504 bytes

Database Buffers     117440512 bytes

Redo Buffers           3031040 bytes

Database mounted.

SQL> alterdatabase flashback on;  \#开始flashback

Database altered.

SQL> alterdatabase open;   \#open数据库

Database altered.

SQL> alterdatabase recover managed standby database using current logfile disconnect fromsession;    \#开启实时同步

Database altered.

## 3.4验证DG同步 ##

上面已经把DataGurad搭建完，下面介绍如何验证DataGuard是否能实时同步

1.  通过查看archive\_log\_dest\_2列是否有error报错，如果有报错，则需要先根据报错内容解决问题

SQL>col dest\_name format a30

SQL>col error format a20

SQL>select dest\_name,error from v$archive\_dest;

DEST\_NAME             ERROR

\--------------------------------------------------

LOG\_ARCHIVE\_DEST\_1

LOG\_ARCHIVE\_DEST\_2

LOG\_ARCHIVE\_DEST\_3

LOG\_ARCHIVE\_DEST\_4

LOG\_ARCHIVE\_DEST\_5

LOG\_ARCHIVE\_DEST\_6

LOG\_ARCHIVE\_DEST\_7

LOG\_ARCHIVE\_DEST\_8

LOG\_ARCHIVE\_DEST\_9

LOG\_ARCHIVE\_DEST\_10

LOG\_ARCHIVE\_DEST\_11

DEST\_NAME             ERROR

\--------------------------------------------------

LOG\_ARCHIVE\_DEST\_12

LOG\_ARCHIVE\_DEST\_13

LOG\_ARCHIVE\_DEST\_14

LOG\_ARCHIVE\_DEST\_15

LOG\_ARCHIVE\_DEST\_16

LOG\_ARCHIVE\_DEST\_17

LOG\_ARCHIVE\_DEST\_18

LOG\_ARCHIVE\_DEST\_19

LOG\_ARCHIVE\_DEST\_20

LOG\_ARCHIVE\_DEST\_21

LOG\_ARCHIVE\_DEST\_22

DEST\_NAME             ERROR

\--------------------------------------------------

LOG\_ARCHIVE\_DEST\_23

LOG\_ARCHIVE\_DEST\_24

LOG\_ARCHIVE\_DEST\_25

LOG\_ARCHIVE\_DEST\_26

LOG\_ARCHIVE\_DEST\_27

LOG\_ARCHIVE\_DEST\_28

LOG\_ARCHIVE\_DEST\_29

LOG\_ARCHIVE\_DEST\_30

LOG\_ARCHIVE\_DEST\_31

31rows selected.

SQL>

注意：上面显示没有报错

2.  查询主库最大归档序号，一致即归档同步成功。

主库上执行：

SQL>select max(sequence\#) from v$archived\_log;

MAX(SEQUENCE\#)

\--------------

132

备库上操作：

SQL>select max(sequence\#) from v$archived\_log;

MAX(SEQUENCE\#)

\--------------

132

主库上执行日志切换

SQL>alter system archive log current;

Systemaltered.

SQL>select max(sequence\#) from v$archived\_log;

MAX(SEQUENCE\#)

\--------------

133

SQL>

备库上再次验证：

SQL>select max(sequence\#) from v$archived\_log;

MAX(SEQUENCE\#)

\--------------

133

SQL>

3.  查看主备库状态

主库上执行：

SQL>select switchover\_status,database\_role from v$database;

SWITCHOVER\_STATUS    DATABASE\_ROLE

\------------------------------------

TOSTANDBY        PRIMARY

备库上执行：

SQL>select switchover\_status,database\_role from v$database;

SWITCHOVER\_STATUS    DATABASE\_ROLE

\------------------------------------

NOTALLOWED     PHYSICAL STANDBY

4.  可以创建一个table进行测试

主库上操作：

SQL>create table dg(id number);

Tablecreated.

SQL>insert into dg values(1);

1row created.

SQL>commit;

Commitcomplete.

SQL>select \* from dg;

\----------

备库上操作：

SQL>select \* from dg;

\----------

SQL>

上面说明DG是同步的。

## 3.5DG切换与恢复 ##

我们配置DG的目的就是为了在主库出现故障时，备库能够提供服务，保证业务的正常运行。DG的故障切换分为switchover和failover两种:

### 3.5.1 switchover ###

switchover是用户有计划的进行停机切换，能够保证不丢失数据，下面我们来看下switchover是怎样操作的：

主库上操作：

SQL> selectswitchover\_status,database\_role from v$database;

SWITCHOVER\_STATUS    DATABASE\_ROLE

\-------------------- ----------------

TO STANDBY      PRIMARY

SQL>

注意：上面查询结果为TO STANDBY 或 SESSIONS ACTIVE表明可以进行切换

SQL> alter database commit to switchover to physical standby;

Databasealtered.

SQL> startupmount

ORACLE instance started.

Total System Global Area  688959488 bytes

Fixed Size          2256432 bytes

Variable Size        566231504 bytes

Database Buffers     117440512 bytes

Redo Buffers           3031040 bytes

Database mounted.

SQL> selectdatabase\_role from v$database;

DATABASE\_ROLE

\----------------

PHYSICAL STANDBY

备库上操作：

SQL> select switchover\_status,database\_role from v$database;

SWITCHOVER\_STATUS    DATABASE\_ROLE

\------------------------------------

TO PRIMARY       PHYSICAL STANDBY

SQL>

注意：上面查询结果显示为TO PRIMARY 或 SESSIONS ACTIVE表明可以切换成主库；

现在可以把备库切换成主库：

SQL> alterdatabase commit to switchover to primary with session shutdown;

Database altered.

SQL> alter database open;

Database altered.

SQL> selectswitchover\_status,database\_role,open\_mode from v$database;

SWITCHOVER\_STATUS    DATABASE\_ROLE    OPEN\_MODE

\-------------------- ------------------------------------

SESSIONS ACTIVE      PRIMARY         READ WRITE

记住：这时候需要在现在的备库（原先的主库）开启实时同步

SQL> alterdatabase open;

Database altered.

SQL> alterdatabase recover managed standby database using current logfile disconnect fromsession;

Database altered.

到此DG switover切换完成，验证方法同上。

### 3.5.2 failover ###

failover是当主库真正出现严重系统故障，如数据库宕机，软硬件故障导致主库不能支持服务，从而进行的切换动作。

注意：为了能够在failover后能够恢复DG,需要在主库上开启flashback，如果不开启flashback的话，DG就可能需要重新搭建

由于主库已经不可访问，下面所有的操作都在备库完成：

SQL> alterdatabase recover managed standby database cancel;   \#停止实时同步

Database altered.

SQL> alter databaserecover managed standby database finish force;

Database altered.

SQL> selectdatabase\_role from v$database;

DATABASE\_ROLE

\----------------

PHYSICAL STANDBY

SQL> alterdatabase commit to switchover to primary;

Database altered.

SQL> alter database open;

Database altered.

SQL> selectswitchover\_status,database\_role,open\_mode from v$database;

SWITCHOVER\_STATUS    DATABASE\_ROLE    OPEN\_MODE

\-------------------- ------------------------------------

RESOLVABLE GAP       PRIMARY         READ WRITE

至此failover操作完成，原来的备库已经切换为主库，可以给业务提供服务了。

### 3.5.2 failover恢复 ###

上面提到了failover，这种情形是当主库真正出现异常之后，才会执行的操作，那么我们执行过failover 之后，如何在重新构建DG，这里我们利用flashback database来重构，具体方法如下：

在新的主库上执行：

SQL> selectto\_char(standby\_became\_primary\_scn) from v$database;

TO\_CHAR(STANDBY\_BECAME\_PRIMARY\_SCN)

\----------------------------------------

977458

SQL>

在之前的主库上，也就是现在的备库上执行下面的操作:

SQL> startupmount

ORA-32004: obsolete or deprecatedparameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area  688959488 bytes

Fixed Size          2256432 bytes

Variable Size        566231504 bytes

Database Buffers     117440512 bytes

Redo Buffers           3031040 bytes

Database mounted.

SQL> flashbackdatabase to scn 977458; \#这个值为在新主库上查询到的SCN值

Flashback complete.

SQL> alterdatabase convert to physical standby;

Database altered.

SQL> shutdownimmediate

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup

ORA-32004: obsolete or deprecatedparameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area  688959488 bytes

Fixed Size          2256432 bytes

Variable Size        566231504 bytes

Database Buffers     117440512 bytes

Redo Buffers           3031040 bytes

Database mounted.

Database opened.

SQL> alterdatabase recover managed standby database using current logfile disconnect fromsession;

Database altered.

SQL>

到此failover  恢复已经完成，关于如何验证DG方法如上，这里不在介绍。

[1_... 1]: http://write.blog.csdn.net/postedit?ticket=ST-487456-z2vr3OeQKU9DdEODidSm-passport.csdn.net#_Toc484523626
[2_ _OracleData Guard_ _... 1]: http://write.blog.csdn.net/postedit?ticket=ST-487456-z2vr3OeQKU9DdEODidSm-passport.csdn.net#_Toc484523627
[2.1_ _Data Guard_... 1]: http://write.blog.csdn.net/postedit?ticket=ST-487456-z2vr3OeQKU9DdEODidSm-passport.csdn.net#_Toc484523628
[2.2_ _Data Guard_... 1]: http://write.blog.csdn.net/postedit?ticket=ST-487456-z2vr3OeQKU9DdEODidSm-passport.csdn.net#_Toc484523629
[3_OracleDataGurad_... 2]: http://write.blog.csdn.net/postedit?ticket=ST-487456-z2vr3OeQKU9DdEODidSm-passport.csdn.net#_Toc484523630
[3.1_... 2]: http://write.blog.csdn.net/postedit?ticket=ST-487456-z2vr3OeQKU9DdEODidSm-passport.csdn.net#_Toc484523631
[3.2_... 2]: http://write.blog.csdn.net/postedit?ticket=ST-487456-z2vr3OeQKU9DdEODidSm-passport.csdn.net#_Toc484523632
[3.2.1 _... 2]: http://write.blog.csdn.net/postedit?ticket=ST-487456-z2vr3OeQKU9DdEODidSm-passport.csdn.net#_Toc484523633
[3.2.2 _... 3]: http://write.blog.csdn.net/postedit?ticket=ST-487456-z2vr3OeQKU9DdEODidSm-passport.csdn.net#_Toc484523634
[3.2.3 _... 4]: http://write.blog.csdn.net/postedit?ticket=ST-487456-z2vr3OeQKU9DdEODidSm-passport.csdn.net#_Toc484523635
[3.2.4 _STANDBY _... 5]: http://write.blog.csdn.net/postedit?ticket=ST-487456-z2vr3OeQKU9DdEODidSm-passport.csdn.net#_Toc484523636
[3.2.5 _... 7]: http://write.blog.csdn.net/postedit?ticket=ST-487456-z2vr3OeQKU9DdEODidSm-passport.csdn.net#_Toc484523637
[3.2.6 _... 8]: http://write.blog.csdn.net/postedit?ticket=ST-487456-z2vr3OeQKU9DdEODidSm-passport.csdn.net#_Toc484523638
[3.2.7 _TNS_... 8]: http://write.blog.csdn.net/postedit?ticket=ST-487456-z2vr3OeQKU9DdEODidSm-passport.csdn.net#_Toc484523639
[3.2.8 _... 9]: http://write.blog.csdn.net/postedit?ticket=ST-487456-z2vr3OeQKU9DdEODidSm-passport.csdn.net#_Toc484523640
[3.2.9 _... 9]: http://write.blog.csdn.net/postedit?ticket=ST-487456-z2vr3OeQKU9DdEODidSm-passport.csdn.net#_Toc484523641
[3.2.10 _... 9]: http://write.blog.csdn.net/postedit?ticket=ST-487456-z2vr3OeQKU9DdEODidSm-passport.csdn.net#_Toc484523642
[3.3_... 10]: http://write.blog.csdn.net/postedit?ticket=ST-487456-z2vr3OeQKU9DdEODidSm-passport.csdn.net#_Toc484523643
[3.3.1_ _... 10]: http://write.blog.csdn.net/postedit?ticket=ST-487456-z2vr3OeQKU9DdEODidSm-passport.csdn.net#_Toc484523644
[3.3.2 _... 11]: http://write.blog.csdn.net/postedit?ticket=ST-487456-z2vr3OeQKU9DdEODidSm-passport.csdn.net#_Toc484523645
[3.3.3 _... 11]: http://write.blog.csdn.net/postedit?ticket=ST-487456-z2vr3OeQKU9DdEODidSm-passport.csdn.net#_Toc484523646
[3.3.4 _... 11]: http://write.blog.csdn.net/postedit?ticket=ST-487456-z2vr3OeQKU9DdEODidSm-passport.csdn.net#_Toc484523647
[3.3.5 _TNS_... 12]: http://write.blog.csdn.net/postedit?ticket=ST-487456-z2vr3OeQKU9DdEODidSm-passport.csdn.net#_Toc484523648
[3.3.6 _... 12]: http://write.blog.csdn.net/postedit?ticket=ST-487456-z2vr3OeQKU9DdEODidSm-passport.csdn.net#_Toc484523649
[3.3.7 _nomount_... 12]: http://write.blog.csdn.net/postedit?ticket=ST-487456-z2vr3OeQKU9DdEODidSm-passport.csdn.net#_Toc484523650
[3.3.8 _TNS_... 13]: http://write.blog.csdn.net/postedit?ticket=ST-487456-z2vr3OeQKU9DdEODidSm-passport.csdn.net#_Toc484523651
[3.3.9 _... 14]: http://write.blog.csdn.net/postedit?ticket=ST-487456-z2vr3OeQKU9DdEODidSm-passport.csdn.net#_Toc484523652
[3.3.10 _... 18]: http://write.blog.csdn.net/postedit?ticket=ST-487456-z2vr3OeQKU9DdEODidSm-passport.csdn.net#_Toc484523653
[3.3.11 _flashback. 19]: http://write.blog.csdn.net/postedit?ticket=ST-487456-z2vr3OeQKU9DdEODidSm-passport.csdn.net#_Toc484523654
[3.4_DG_... 19]: http://write.blog.csdn.net/postedit?ticket=ST-487456-z2vr3OeQKU9DdEODidSm-passport.csdn.net#_Toc484523655
[3.5DG_... 22]: http://write.blog.csdn.net/postedit?ticket=ST-487456-z2vr3OeQKU9DdEODidSm-passport.csdn.net#_Toc484523656
[3.5.1 switchover. 22]: http://write.blog.csdn.net/postedit?ticket=ST-487456-z2vr3OeQKU9DdEODidSm-passport.csdn.net#_Toc484523657
[3.5.2 failover. 24]: http://write.blog.csdn.net/postedit?ticket=ST-487456-z2vr3OeQKU9DdEODidSm-passport.csdn.net#_Toc484523658
[3.5.2 failover_... 25]: http://write.blog.csdn.net/postedit?ticket=ST-487456-z2vr3OeQKU9DdEODidSm-passport.csdn.net#_Toc484523659