Oracle 11g Data Guard搭建物理Standby数据库（一）

Oracle Data Guard保证了企业数据的高可用性、数据保护和灾难恢复。它提供了一套全面的服务，可以创建、维护、管理和监视一个或多个备用数据库（Standby），以使生产数据库能够从灾难和数据损坏中恢复。Data Guard将维护备用数据库作为生产数据库的副本，然后，如果生产数据库由于计划内或计划外的停机而变得不可用，那么它可以将任何备用数据库切换到生产角色，最小化停机时间。Data Guard可以与传统的备份、恢复和集群技术一起使用，以提供高水平的数据保护和数据可用性。

Data Guard的本质是对事务数据的Redo传输，保证主库和备库之间的数据更新同步。Oracle支持一个主库最多有9个备库，Redo的传输既可以是LGWR触发，也可以是ARCn进程触发，本篇主要使用LGWR来演示Data Guard的搭建。

首先搭建Oracle 11G Data Guard环境，先对Data Guard有个初步的了解，然后后面再分别对其进行详细的讲解，欢迎一起学习、探讨Data Guard相关的知识。

一 软件环境

• CentOS-6.5-x86_64
• p13390677_112040_Linux-x86-64
• 主库：sz.pri.com，IP：192.168.1.106 ORACLE_SID：szpri
• 备库：sh.std.com，IP：192.168.1.109 ORACLE_SID：szpri

二 数据库设置

1、数据库软件安装与数据库安装

• 安装主库，具体安装步骤参照Oracle 11g软件安装、Oracle 11g利用Netmgr配置监听器和服务 和Oracle 11g利用DBCA创建数据库；
• 安装备库，具体安装步骤参照Oracle 11g软件安装、Oracle 11g利用Netmgr配置监听器和服务 和Oracle 11g利用DBCA创建数据库；

2、监听器配置

• 主库LISTENER配置

[oracle@sz ~]$ more /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora 
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SZ =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.106)(PORT = 1521))
  )

ADR_BASE_SZ = /u01/app/oracle

SH =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.109)(PORT = 1521))
  )

ADR_BASE_SH = /u01/app/oracle

SID_LIST_SZ =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = szpri)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = szpri)
    )
  )

SID_LIST_SH =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = szpri)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = szpri)
    )
  )

• 备库LISTENER配置

[oracle@sh ~]$ more /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SZ =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.106)(PORT = 1521))
  )

ADR_BASE_SZ = /u01/app/oracle

SH =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.109)(PORT = 1521))
  )

ADR_BASE_SH = /u01/app/oracle

SID_LIST_SZ =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = szpri)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = szpri)
    )
  )

SID_LIST_SH =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = szpri)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = szpri)
    )
  )

3、TNS配置

• 主库TNS配置

[oracle@sz ~]$ more /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora 
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

SZPRI =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.106)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = szpri)
    )
  )

SHSTD =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.109)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = szpri)
    )
  )

• 备库TNS配置（和主库TNS相同）

[oracle@sh ~]$ more /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora 
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

SZPRI =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.106)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = szpri)
    )
  )

SHSTD =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.109)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = szpri)
    )
  )

三 Data Guard设置

1、激活Force Logging

     在Mount或者Open模式，激活主库的Force Logging：

SQL> alter database force logging;

Database altered.

2、启用归档

     在Mount模式，启用主库的数据归档模式：

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

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

3、配置主库的参数文件

SQL> create pfile='/home/oracle/initszpri.ora' from spfile;

File created.
[oracle@sz ~]$ vim initszpri.ora

*.audit_file_dest='/u01/app/oracle/admin/szpri/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/szpri/control01.ctl','/u01/app/oracle/fast_recovery_area/szpri/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='szpri'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=szpriXDB)'
*.memory_target=833617920
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
#############################The following is adding for DG by Alen Liu 2017-09-11####################################
DB_UNIQUE_NAME=szpri
LOG_ARCHIVE_CONFIG='DG_CONFIG=(szpri,shstd)'
LOG_ARCHIVE_DEST_2=
 'SERVICE=shstd LGWR SYNC AFFIRM
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
  DB_UNIQUE_NAME=shstd'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=shstd
FAL_CLIENT=szpri
#############################The following is adding for DG by Alen Liu 2017-09-11####################################

4、配置备库的参数文件

[oracle@sh ~]$ vim initszpri.ora 

szpri.__db_cache_size=352321536
szpri.__java_pool_size=4194304
szpri.__large_pool_size=8388608
szpri.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
szpri.__pga_aggregate_target=335544320
szpri.__sga_target=499122176
szpri.__shared_io_pool_size=0
szpri.__shared_pool_size=125829120
szpri.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/szpri/adump'
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/szpri/control01.ctl','/u01/app/oracle/fast_recovery_area/szpri/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='szpri'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=szpriXDB)'
*.memory_target=833617920
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
#############################The following is adding for DG by Alen Liu 2017-09-11####################################
DB_UNIQUE_NAME=shstd
LOG_ARCHIVE_CONFIG='DG_CONFIG=(szpri,shstd)'
LOG_ARCHIVE_DEST_2=
 'SERVICE=szpri LGWR SYNC AFFIRM
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
  DB_UNIQUE_NAME=szpri'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=szpri
FAL_CLIENT=shstd
#############################The following is adding for DG by Alen Liu 2017-09-11####################################

5、创建备库密码文件

如果在同一主机进行Standby搭建，将主库的密码文件拷贝至备库目录即可；如果不在同一主 机，则此步骤可省略。

[oracle@sz oracle]$ scp product/11.2.0/dbhome_1/dbs/orapwszpri oracle@192.168.1.109:/home/oracle/

6、创建备库所需目录

如果在同一主机进行Standby搭建，则需要创建相应的目录，因为同一主机数据库实例名不能相同；如果不在同一主机，则此步骤可省略。

[oracle@sh ~]$ mkdir -p /u01/app/oracle/admin/szpri/adump
[oracle@sh ~]$ mkdir -p /u01/app/oracle/oradata/szpri/
[oracle@sh ~]$ mkdir -p /u01/app/oracle/fast_recovery_area/szpri/

7、打开主库

[oracle@sz ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Mon Sep 11 22:46:03 2017

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

Connected to an idle instance.

SQL> startup nomount pfile='/home/oracle/initszpri.ora';
ORACLE instance started.

Total System Global Area  830930944 bytes
Fixed Size		    2257800 bytes
Variable Size		  536874104 bytes
Database Buffers	  289406976 bytes
Redo Buffers		    2392064 bytes
SQL> alter database mount;

Database altered.

SQL> alter database open;

Database altered.

8、启动备库至Nomount状态

SQL> startup nomount pfile='/home/oracle/initshstd.ora';
ORACLE instance started.

Total System Global Area  830930944 bytes
Fixed Size		    2257800 bytes
Variable Size		  536874104 bytes
Database Buffers	  289406976 bytes
Redo Buffers		    2392064 bytes
SQL> 

9、RMAN在线复制数据库

[oracle@sh ~]$ rman target sys/system@szpri auxiliary sys/system@shstd

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Sep 11 23:44:42 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: SZPRI (DBID=4248758477)
connected to auxiliary database: SZPRI (not mounted)

RMAN> duplicate target database for standby from active database nofilenamecheck;

Starting Duplicate Db at 11-SEP-17
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK

contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwszpri' auxiliary format 
 '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwszpri'   ;
}
executing Memory Script

Starting backup at 11-SEP-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=50 device type=DISK
Finished backup at 11-SEP-17

contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '/u01/app/oracle/oradata/szpri/control01.ctl';
   restore clone controlfile to  '/u01/app/oracle/fast_recovery_area/szpri/control02.ctl' from 
 '/u01/app/oracle/oradata/szpri/control01.ctl';
}
executing Memory Script

Starting backup at 11-SEP-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_szpri.f tag=TAG20170911T234534 RECID=2 STAMP=954459937
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 11-SEP-17

Starting restore at 11-SEP-17
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 11-SEP-17

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
   set newname for tempfile  1 to 
 "/u01/app/oracle/oradata/szpri/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to 
 "/u01/app/oracle/oradata/szpri/system01.dbf";
   set newname for datafile  2 to 
 "/u01/app/oracle/oradata/szpri/sysaux01.dbf";
   set newname for datafile  3 to 
 "/u01/app/oracle/oradata/szpri/undotbs01.dbf";
   set newname for datafile  4 to 
 "/u01/app/oracle/oradata/szpri/users01.dbf";
   set newname for datafile  5 to 
 "/u01/app/oracle/oradata/szpri/example01.dbf";
   backup as copy reuse
   datafile  1 auxiliary format 
 "/u01/app/oracle/oradata/szpri/system01.dbf"   datafile 
 2 auxiliary format 
 "/u01/app/oracle/oradata/szpri/sysaux01.dbf"   datafile 
 3 auxiliary format 
 "/u01/app/oracle/oradata/szpri/undotbs01.dbf"   datafile 
 4 auxiliary format 
 "/u01/app/oracle/oradata/szpri/users01.dbf"   datafile 
 5 auxiliary format 
 "/u01/app/oracle/oradata/szpri/example01.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/szpri/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 11-SEP-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/szpri/system01.dbf
output file name=/u01/app/oracle/oradata/szpri/system01.dbf tag=TAG20170911T234556
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:03:58
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u01/app/oracle/oradata/szpri/sysaux01.dbf
output file name=/u01/app/oracle/oradata/szpri/sysaux01.dbf tag=TAG20170911T234556
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:05:49
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/app/oracle/oradata/szpri/example01.dbf
output file name=/u01/app/oracle/oradata/szpri/example01.dbf tag=TAG20170911T234556
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:04:19
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u01/app/oracle/oradata/szpri/undotbs01.dbf
output file name=/u01/app/oracle/oradata/szpri/undotbs01.dbf tag=TAG20170911T234556
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:04:11
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u01/app/oracle/oradata/szpri/users01.dbf
output file name=/u01/app/oracle/oradata/szpri/users01.dbf tag=TAG20170911T234556
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:04
Finished backup at 12-SEP-17

sql statement: alter system archive log current

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=2 STAMP=954461064 file name=/u01/app/oracle/oradata/szpri/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=3 STAMP=954461064 file name=/u01/app/oracle/oradata/szpri/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=954461064 file name=/u01/app/oracle/oradata/szpri/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=954461064 file name=/u01/app/oracle/oradata/szpri/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=6 STAMP=954461064 file name=/u01/app/oracle/oradata/szpri/example01.dbf
Finished Duplicate Db at 12-SEP-17

RMAN> 

此步骤完成后，备库处于Mounted状态，将其打开：

SQL> select instance_name,status from v$instance;

INSTANCE_NAME	 STATUS
---------------- ------------
szpri		 MOUNTED

SQL> alter database open;

Database altered.

10、检查主备库状态

• 主库

SQL> select name,open_mode,protection_mode,database_role from v$database;

NAME	  OPEN_MODE	       PROTECTION_MODE	    DATABASE_ROLE
--------- -------------------- -------------------- ----------------
SZPRI	  READ WRITE	       MAXIMUM PERFORMANCE  PRIMARY

• 备库

SQL> select name,open_mode,protection_mode,database_role from v$database;

NAME	  OPEN_MODE	       PROTECTION_MODE	    DATABASE_ROLE
--------- -------------------- -------------------- ----------------
SZPRI	  READ ONLY	       MAXIMUM PERFORMANCE  PHYSICAL STANDBY

11、启动MRP0进程应用日志

SQL> alter database recover managed standby database disconnect from session;

Database altered.
SQL> select process,group#,status from v$managed_standby;

PROCESS   GROUP#				   STATUS
--------- ---------------------------------------- ------------
ARCH	  N/A					   CONNECTED
ARCH	  N/A					   CONNECTED
ARCH	  N/A					   CONNECTED
ARCH	  N/A					   CONNECTED
RFS	  N/A					   IDLE
RFS	  N/A					   IDLE
RFS	  N/A					   IDLE
RFS	  1					   IDLE
MRP0	  N/A					   WAIT_FOR_LOG

9 rows selected.

12、验证主备数据同步

• 主库操作

SQL> create table test(id number , name varchar2(50));

Table created.

SQL> insert into test values(1,'Alen Liu');

1 row created.

SQL> commit；

Commit complete.
SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

• 备库查询

SQL> select *from test;
select *from test
             *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> /

	ID NAME
---------- --------------------------------------------------
	 1 Alen Liu

13、创建主备库SPFile

分别在主库、备库执行下面命令即可创建SPFile，这样下次启动数据库就可以不指定PFile了。

SQL> create spfile from pfile='/home/oracle/initszpri.ora';

File created.

至此，Data Guard搭建物理Standby数据库完成，下面我们进行Standby Redo Log的建立，继续完善该Data Guard环境。