Step by Step – 在RHEL上搭建物理Data Guard(DG)

摘要:本文档详细记录搭建Data Guard过程,并记录搭建过程中可能遇到的问题。

环境配置:

主库 备库
IP 192.168.0.150 192.168.0.151
ORACLE_SID enmodb enmostd
DB_UNIQUE_NAME enmodb enmostd

一、环境准备

以下说明搭建DG前的环境准备情况,单实例数据库的搭建可参考Step by Step – Install Oracle 11gR2 on RHEL5

(一)环境准备

主库安装数据库软件,配置监听,建库。

备库安装数据库软件,配置监听。

(二)问题小结

使用netca配置监听时,提示Use another port number,Use another port number. The information provided is used by another software。更换其他端口亦无法创建监听。

这个问题是由于使用图形化界面修改主机名,却未在/etc/hosts文件里添加新的主机名导致的。配置监听前需先改动/etc/hosts文件,把主机名加在里面,否则无法配置监听。

原hosts文件为:

127.0.0.1 localhost.localdomain localhost

::1 localhost6.localdomain6 localhost6

应修改为:

127.0.0.1 Enmostd localhost.localdomain localhost

::1 localhost6.localdomain6 localhost6

【参考】

http://forums.oracle.com/forums/thread.jspa?threadID=1130289

二、配置DG

1.确认主库为归档模式

CONFIRM PRIMARY DB ARCHIVE LOG MODE)

SQL> alter database archivelog;

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 56

Next log sequence to archive 58

Current log sequence 58
2.更改主库为Force logging模式

ALTER PRAIMARY DATABASE FORCE LOGGING

SQL> select force_logging from v$database;

FOR

---

NO

SQL> alter database force logging;

Database altered.
3.在主库上创建密钥文件

CREATE PWD FILE FROM PRIMARY DB

密钥文件命名方式与平台有关,如果名称配置不当,会导致ORA-01031错误。

Linux/UNIX平台:orapw$ORACLE_SID

Windows平台:PWD$ORACLE_SID.ora

[[email protected] dbs]$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=oracle entries=30;

[[email protected] dbs]$ ll

total 9568

-rw-rw---- 1 oracle oinstall 1544 May 17 13:50 hc_DBUA0.dat

-rw-rw---- 1 oracle oinstall 1544 May 17 13:55 hc_enmodb.dat

-rw-r--r-- 1 oracle oinstall 913 May 17 15:40 initenmodb.ora

-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora

-rw-r----- 1 oracle oinstall 24 May 17 13:55 lkENMODB

-rw-r----- 1 oracle oinstall 5120 May 17 18:03 orapwenmodb

-rw-r----- 1 oracle oinstall 9748480 May 17 15:51 snapcf_enmodb.f

-rw-r----- 1 oracle oinstall 3584 May 17 17:52 spfileenmodb.ora
4.将密钥文件传输至备库

TRANSFER PWD FILE FROM PRIMARY DB TO STANDBY DB

[[email protected] dbs]$ scp orapwenmodb 192.168.0.151:/u01/app/oracle/product/11.2.0/db_1/dbs

Address 192.168.0.151 maps to localhost, but this does not map back to the address - POSSIBLE BREAK-IN ATTEMPT!

[email protected]'s password:

orapwenmodb                                100% 5120 5.0KB/s 00:00
5.修改密钥文件文件名

(此步骤可与上一步一起操作)

RENAME PWD FILE ON STANDBY DATABASE

[[email protected] dbs]$ mv orapwenmodb orapwenmostd
6.修改主备库tnsnames文件

在主库上修改,传输到备库

CONFIG tnsnames.ora FILE SYNC PRIMARY & STANDBY DB

ENMO_PRI=

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = enmodb)

)

)

ENMO_STD =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = enmostd)

)

)
[[email protected] admin]$ scp tnsnames.ora 192.168.0.151:/u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

Address 192.168.0.151 maps to localhost, but this does not map back to the address - POSSIBLE BREAK-IN ATTEMPT!

[email protected]'s password:

tnsnames.ora 100% 514 0.5KB/s 00:00

配置完主备库tnsnames文件后可通过以下命令验证是否配置成功。验证前需开启监听。

START LISTENER @PRIMARY DB & STANDBY DB

验证:

在备库:

tnsping enmo_pri

在主库:

tnsping enmo_std

这一步可能遇到的问题:如果防火墙没关,会报TNS-12543错误

【ERROR】

IF FIREWALL IS ON @PRIMARY,THE tnsping FROM STANDBY WILL GET AN ERROR(TNS-12543)

[[email protected] admin]$ tnsping marshall_pri

TNS Ping Utility for Linux: Version 11.2.0.2.0 - Production on 06-MAY-2011 16:04:40

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

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.119)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = marshall)))

TNS-12543: TNS:destination host unreachable

【SOLUTION】

JUST TURN OFF THE FIREWALL ON PRIMARY DB

[[email protected] tmp]$ /etc/rc.d/init.d/iptables status

Firewall is not configured.

[[email protected] ~]# /etc/rc.d/init.d/iptables stop

Flushing firewall rules: [ OK ]

Setting chains to policy ACCEPT: filter [ OK ]

Unloading iptables modules: [ OK ]

[[email protected] ~]# chkconfig iptables off
7.在备库创建与主库相同的目录结构
(1)查看主库以下几个目录

FIND THE FOLLOWING INFO ON PRIMARY DB

user_dump_dest —> show parameter

core_dump_dest —> show parameter

datafile所在目录(oradata)—> select file_name from dba_data_files;

controlfile所在目录 —> show parameter control

onlinelog所在位置 —> select member from v$logfile;

archivelog所在位置 —> show parameter db_recovery_file_dest

adump位置 —> 通常为$ORACLE_BASE/admin/<ORACLE_SID>/adump

(2)在备库创建相同目录结构

再根据上述位置,在备库创建相关目录

mkdir -p /u01/app/oracle/diag/rdbms/enmodb/enmodb/trace

mkdir -p /u01/app/oracle/diag/rdbms/enmodb/enmodb/cdump

mkdir -p /u01/app/oracle/oradata/enmodb/

mkdir -p /u02/flash_recovery_area/enmodb/

mkdir -p /u01/app/oracle/admin/enmodb/adump
8.主库创建PFILE
(1)在主库上创建PFILE

CREATE PFILE FROM SPFILE @PRIMARY DB

SQL> show parameter spfile;

NAME TYPE VALUE

-------- ----------- ------------------------------

spfile string /u01/app/oracle/product/11.2.0

/dbhome_1/dbs/spfileenmodb.ora

SQL> create pfile from spfile;

File created.
(2)将PFILE拷贝至备库

COPY PFILE FROM PRIMARY DB TO STANDBY DB.

[[email protected] dbs]$ ll init*

-rw-r--r-- 1 oracle oinstall 913 May 17 15:40 initenmodb.ora

-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora

[[email protected] dbs]$ scp initenmodb.ora 192.168.0.151:/u01/app/oracle/product/11.2.0/db_1/dbs/initenmostd.ora

Address 192.168.0.151 maps to localhost, but this does not map back to the address - POSSIBLE BREAK-IN ATTEMPT!

[email protected]'s password:

initenmodb.ora 100% 913 0.9KB/s 00:00
9.修改备库PFILE参数

EDIT THE FOLLOWING PARAMETERS INTO STANDBY DB PFILE

在备库上,禁用闪回区(db_recovery_file_dest*参数删除或注释掉),db_create*参数无需配置,但需要配置下

db_file_name_convert和log_file_name_convert。否则重做日志文件将在闪回区自动生成,与主库不符。

#*.db_create_file_dest='/u01/app/oracle/oradata/enmodb'

#*.db_create_online_log_dest_1='/u01/app/oracle/oradata/enmodb'

#*.db_recovery_file_dest='/u02/flash_recovery_area'

#*.db_recovery_file_dest_size=5218762752

#fal_* parts parameter can be found from tnsnames.ora file

*.fal_server='ENMO_PRI'

*.fal_client='ENMO_STD'

*.db_unique_name='enmostd'

*.job_queue_processes=10

#set the db_unique_name as you want

*.log_archive_config='dg_config=(enmodb,enmostd)'

*.log_archive_dest_1='LOCATION=use_db_recovery_file_dest LGWR VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=enmostd'

*.log_archive_dest_2='SERVICE=ENMO_PRI VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=enmodb'

*.standby_file_management='AUTO'

#convert parameter

*.db_file_name_convert='/u01/app/oracle/oradata/enmodb/','/u01/app/oracle/oradata/enmodb/'

*.log_file_name_convert='/u01/app/oracle/oradata/enmodb/','/u01/app/oracle/oradata/enmodb/'

【测试结果:闪回区为on时,备库日志由Oracle自动通过OMF管理,命名】

备库:

SQL> select member from v$logfile;

MEMBER

--------------------------------------------------------------------------------

/u02/flash_recovery_area/MARSHALL/onlinelog/o1_mf_1_6w7hqx8b_.log

/u02/flash_recovery_area/MARSHALL/onlinelog/o1_mf_2_6w7hqygj_.log

/u02/flash_recovery_area/MARSHALL/onlinelog/o1_mf_3_6w7hqzoo_.log

主库:

SQL> select member from v$logfile;

MEMBER

---------------------------------------------

/u01/app/oracle/oradata/MARSHALL/redo01.log

/u01/app/oracle/oradata/MARSHALL/redo02.log

/u01/app/oracle/oradata/MARSHALL/redo03.log
10.启动备库至NOMOUNT状态

STARTUP STANDBY DB WITH INTO NOMOUNT MODE

[[email protected] dbs]$ pwd

/u01/app/oracle/product/11.2.0/db_1/dbs

[[email protected] dbs]$ ll

total 16

-rw-r--r-- 1 oracle oinstall 1421 May 17 15:41 initenmostd.ora

-rw-r--r-- 1 oracle oinstall 2851 May 15 2009 init.ora

-rw-r----- 1 oracle oinstall 5120 May 17 15:05 pwdenmostd.ora

[[email protected] ~]$ sql

SQL*Plus: Release 11.2.0.2.0 Production on Tue May 17 15:43:34 2011

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

Connected to an idle instance.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 1653518336 bytes

Fixed Size 2227032 bytes

Variable Size 956302504 bytes

Database Buffers 687865856 bytes

Redo Buffers 7122944 bytes
11.根据PFILE创建SPFILE并用SPFILE启动数据库(备库)

CREATE SPFILE AND STARTUP STANDBY DB WITH SPFILE

SQL> create spfile from pfile;

File created.

SQL> shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 1653518336 bytes

Fixed Size 2227032 bytes

Variable Size 956302504 bytes

Database Buffers 687865856 bytes

Redo Buffers 7122944 bytes

SQL> show parameter spfile

NAME TYPE VALUE

------ ----------- ------------------------------

spfile string /u01/app/oracle/product/11.2.0

/db_1/dbs/spfileenmostd.ora
12.获取主库数据文件及重做日志文件

GET DATA FILES FROM PRIMARY DB TO STANDBY

前提条件:主备库互相可以通过tnsping PING通

(1)在主库上全备
RMAN> backup database;
(2)在备库上创建同名目录
[[email protected] 2011_05_17]$ pwd

/u02/flash_recovery_area/ENMODB/backupset/2011_05_17

[[email protected] 2011_05_17]$ ll

total 1511920

-rw-r----- 1 oracle oinstall 9830400 May 17 15:51 o1_mf_ncsnf_TAG20110517T155008_6x4b7m6m_.bkp

-rw-r----- 1 oracle oinstall 1536851968 May 17 15:51 o1_mf_nnndf_TAG20110517T155008_6x4b5jqh_.bkp

[[email protected] ~]$ mkdir -p /u02/flash_recovery_area/ENMODB/backupset/2011_05_17
(3)拷贝备份片至备库相同目录
[[email protected] 2011_05_17]$ scp o1_mf_ncsnf_TAG20110517T155008_6x4b7m6m_.bkp 192.168.0.151:/u02/flash_recovery_area/ENMODB/backupset/2011_05_17/

[[email protected] 2011_05_17]$ scp o1_mf_nnndf_TAG20110517T155008_6x4b5jqh_.bkp 192.168.0.151:/u02/flash_recovery_area/ENMODB/backupset/2011_05_17/
(4)根据备份创建副本数据库
[[email protected] 2011_05_17]$ rman target sys/[email protected]

Recovery Manager: Release 11.2.0.2.0 - Production on Tue May 17 16:20:51 2011

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

connected to target database: ENMODB (DBID=3649408623)

RMAN> connect auxiliary /

connected to auxiliary database: ENMODB (not mounted)

RMAN> duplicate target database for standby nofilenamecheck;
(5)检查文件是否创建
[[email protected] enmodb]$ pwd

/u01/app/oracle/oradata/enmodb

[[email protected] enmodb]$ ll

total 2126244

-rw-r----- 1 oracle oinstall 9748480 May 17 17:30 control01.ctl

-rw-r----- 1 oracle oinstall 52429312 May 17 17:27 redo01.log

-rw-r----- 1 oracle oinstall 52429312 May 17 17:27 redo02.log

-rw-r----- 1 oracle oinstall 52429312 May 17 17:27 redo03.log

-rw-r----- 1 oracle oinstall 629153792 May 17 17:26 sysaux01.dbf

-rw-r----- 1 oracle oinstall 734011392 May 17 17:26 system01.dbf

-rw-r----- 1 oracle oinstall 639639552 May 17 17:26 undotbs01.dbf

-rw-r----- 1 oracle oinstall 5251072 May 17 17:26 users01.dbf
(6)检查备库状态

现在为MOUNTED状态

SQL> select status from v$instance;

STATUS

------------

MOUNTED
(7)问题小结

[email protected]

RMAN> connect auxiliary /

connected to auxiliary database: MARSHALL (DBID=2393086917, not open)

主库为not open状态,最快的方式便是shutdown备库,然后重新startup nomount

13.主备库添加STANDBY日志
(1)检查主库日志

CHECK/ADD STANDBY LOGFILE ON PRIMARY & STANDBY DB

查询主库日志

SQL> select group#,lf.member,l.bytes/1024/1024 MB from v$log l join v$logfile lf using(group#);

GROUP# MEMBER MB

---------- -------------------------------------------------- ----------

1 /u01/app/oracle/oradata/enmodb/redo01.log 50

2 /u01/app/oracle/oradata/enmodb/redo02.log 50

3 /u01/app/oracle/oradata/enmodb/redo03.log 50
(2)在主备库上添加STANDBY日志

根据主库日志组号,在主备库上分别创建STANDBY LOGFILE,组数比主库多一个

备库:

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 4 '/u01/app/oracle/oradata/enmodb/redo04.log' size 50M;

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 5 '/u01/app/oracle/oradata/enmodb/redo05.log' size 50M;

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 6 '/u01/app/oracle/oradata/enmodb/redo06.log' size 50M;

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 7 '/u01/app/oracle/oradata/enmodb/redo07.log' size 50M;

主库:

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 4 '/u01/app/oracle/oradata/enmodb/redo04.log' size 50M;

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 5 '/u01/app/oracle/oradata/enmodb/redo05.log' size 50M;

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 6 '/u01/app/oracle/oradata/enmodb/redo06.log' size 50M;

ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 7 '/u01/app/oracle/oradata/enmodb/redo07.log' size 50M;
14.调整主库参数

—–CONFIG PRIMARY DB SPFILE PARAMETER (RUN ON PRIMARY)

alter system set log_archive_config='dg_config=(enmodb,enmostd)';

alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST LGWR valid_for=(ALL_LOGFILES, ALL_ROLES) db_unique_name=enmodb';

alter system set log_archive_dest_2='service=ENMO_STD valid_for=(online_logfiles,primary_role) db_unique_name=enmostd';

alter system set standby_file_management=auto;

alter system set fal_server='ENMO_STD';

alter system set fal_client='ENMO_PRI';

alter system set job_queue_processes=10;
15.设置备库为MAXIMIZE AVAILABILITY模式

SET STANDBY AS maximize AVAILABILITY MODE(On Primary DB)

alter database set standby database to maximize AVAILABILITY;
16.恢复备库

Recover DG(On standby)

ALTER DATABASE recover managed standby database using current logfile disconnect;
17.校验主备库
(1)校验状态

至此,DG搭建完毕。可用过如下命令查看主备库status。

select switchover_status from v$database;

正常情况下主库为SESSIONS ACTIVE或是TO STANDBY状态:

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

--------------------

TO STANDBY

备库应为TO PRIMARY或是NOT ALLOWED状态:

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

--------------------

NOT ALLOWED
(2)通过日志校验
①主备库现在归档情况:

主库:

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 63

Next log sequence to archive 65

Current log sequence 65

备库:

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 63

Next log sequence to archive 0

Current log sequence 65
②手动归档

使用tailf或是tail –f命令分别监视主备库alert日志,然后在主库上执行手动归档日志的操作:

alter system archive log current;

这是,主库日志提示:

Tue May 17 23:34:01 2011

ALTER SYSTEM ARCHIVE LOG

Tue May 17 23:34:01 2011

Thread 1 advanced to log sequence 66 (LGWR switch)

Current log# 3 seq# 66 mem# 0: /u01/app/oracle/oradata/enmodb/redo03.log

备库日志提示:

Tue May 17 23:31:00 2011

Archived Log entry 4 added for thread 1 sequence 65 rlc 751384559 ID 0xd986396f dest 2:

RFS[4]: No standby redo logfiles available for thread 1

RFS[4]: Opened log for thread 1 sequence 66 dbid -645558673 branch 751384559

Tue May 17 23:31:01 2011

Media Recovery Log /u01/app/oracle/product/11.2.0/db_1/dbs/arch1_65_751384559.dbf

Media Recovery Waiting for thread 1 sequence 66 (in transit)
③再查看归档情况

主库:

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 64

Next log sequence to archive 66

Current log sequence 66

备库:

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 64

Next log sequence to archive 0

Current log sequence 66

三、总结

上述步骤看似复杂(实际也挺复杂)。稍微总结下,不过分如下几个步骤:

1. 环境准备。在此Step by Step中,我们搭建的是物理备库。在备库上,无需建库,但是要起监听。

2. 配置部分分为以下几步:

a) 1.2为更改主库归档及logging模式

b) 3-5为关于密码文件的配置,配置不成功会导致主库的switchover_status显示为FAILED DESTINATION

c) 7-11步为修改备库的PFILE文件并最终使用SPFILE起库至NOMOUNT状态。其中第九步最重要。

d) 12步为创建副本数据库

e) 13步为添加STANDBY日志

f) 14为调整主库参数,这一步与第九步类似,但是我们在保证主库不停机的情况下搭建备库。

g) 15步为调整备库模式

h) 16步为恢复备库

i) 17步为校验

普人特福的博客cnzz&51la for wordpress,cnzz for wordpress,51la for wordpress