oracle11g高可用,oracle11g 搭建DG环境总结

本帖最后由 zhofly 于 2016-6-24 23:19 编辑

测试环境为：两台服务器配置一样：Oracle Linux Server release 6.5  X86_64, DB  11.2.0.4.0

前提环境：一台已经安装好数据库软件并创建数据库实例的服务器

另一台也就是备库，只安装了数据库软件，空实例。

安装环境与创建数据库这里就不用说了，很简单的。

整个环境为成4个步骤：

1，主备(gydg)修改

2，备库(yzdg)修改

3，主备duplicate数据库

4，测试DG是否成功

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

1.1 查看数据库是否在归档与是否强制LOGGING模式。

SQL> select log_mode,force_logging from v$database;

LOG_MODE     FOR

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

ARCHIVELOG   NO

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

1.2 查看数据库的日志组个数与大小，因为我们创建standby日志组的个数是原日志组个数+1再与thread的积，size不能小于原日志文件的大小。

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

GROUP# BYTES/1024/1024

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

1              50

2              50

3              50

SQL> select member from v$logfile;

MEMBER

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

/u01/app/oracle/oradata/gydg/redo03.log

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

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

1.3 创建standby日志组，位置与原日志组相同的路径。创建完成后查询是否成功

SQL> alter database add standby logfile '/u01/app/oracle/oradata/gydg/standby01.log' size 50m;

Database altered.

SQL> alter database add standby logfile '/u01/app/oracle/oradata/gydg/standby02.log' size 50m;

Database altered.

SQL> alter database add standby logfile '/u01/app/oracle/oradata/gydg/standby03.log' size 50m;

Database altered.

SQL> alter database add standby logfile '/u01/app/oracle/oradata/gydg/standby04.log' size 50m;

Database altered.

SQL> select group#,status,type,member from v$logfile;

GROUP# STATUS  TYPE    MEMBER

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

3         ONLINE  /u01/app/oracle/oradata/gydg/redo03.log

2         ONLINE  /u01/app/oracle/oradata/gydg/redo02.log

1         ONLINE  /u01/app/oracle/oradata/gydg/redo01.log

4         STANDBY /u01/app/oracle/oradata/gydg/standby01.log

5         STANDBY /u01/app/oracle/oradata/gydg/standby02.log

6         STANDBY /u01/app/oracle/oradata/gydg/standby03.log

7         STANDBY /u01/app/oracle/oradata/gydg/standby04.log

1.4 修改相关的参数，与DG的参数就只与几个参数相关，大概就是日志，文件的位置的转换，GAP的处理，其实GAP已经会自动的处理，不过这里我们还是介绍配置FAL_SERVER,FAL_CLIENT参数。

SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(gydg,yzdg)';

SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/arch/gydg/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=gydg';

SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=gydg LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=gydg';

SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;

SQL> alter system set FAL_SERVER=yzdg;

SQL> alter system set FAL_CLIENT=gydg;

SQL>alter system set db_file_name_convert='/u01/app/oracle/oradata/gydg/','/u01/app/oracle/oradata/yzdg/' scope=spfile;

SQL>alter system set log_file_name_convert='/u01/app/oracle/oradata/logfile/','/u01/app/oracle/oradata/logfile/' scope=spfile;

SQL> create pfile from spfile;

1.5 把dbs下的内容同步到standby主机上面，主要是密码文件与init文件。(如果SID不一样，创建密码文件要保证entries值一致;)

orapwd file=orapwupsh password=123456 entries=5;( 在主库创建密码文件)

$scp initSID.ora orapwSID oracle@192.168.137.99:$ORACLE_HOME/dbs

1.6 配置静态监听

主库：

1、修改/u01/app/oracle/product/11.2.0.4.0/network/admin/listener.ora

[oracle@gydg admin]$ vi listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = gydg)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0.4.0)

(SID_NAME = gydg)

)

)

LISTENER =

(DESCRIPTION =

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

)

ADR_BASE_LISTENER = /u01/app/oracle

2、修改 /u01/app/oracle/product/11.2.0.4.0/network/admin/tnsnames.ora

[oracle@gydg admin]$ vi tnsnames.ora

yzdg =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(service_name = yzdg)(UR=A)

)

)

gydg =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(service_name = gydg)(UR=A)

)

)

备库

1、修改/u01/app/oracle/product/11.2.0.4.0/network/admin/listener.ora

[oracle@yzdg admin]$ vi listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = yzdg)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0.4.0)

(SID_NAME = yzdg)

)

)

LISTENER =

(DESCRIPTION =

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

)

ADR_BASE_LISTENER = /u01/app/oracle

2、修改 /u01/app/oracle/product/11.2.0.4.0/network/admin/tnsnames.ora

[oracle@yzdg admin]$ vi  tnsnames.ora

yzdg =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(service_name = yzdg)(UR=A)

)

)

gydg =

(DESCRIPTION =

(ADDRESS_LIST =

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

)

(CONNECT_DATA =

(service_name = gydg)(UR=A)

)

)

启动主备库的监听

[oracle@yzdg admin]$lsnrctl

LSNRCTL> start

测试监听是否正常

sqlplus sys/oracle@gydg as sysdba

sqlplus sys/oracle@yzdg as sysdba

备库上创建相关文件与修改参数文件

[oracle@yzdg dbs]$ more inityzdg.ora

*.audit_trail='db'

*.compatible='11.2.0.4.0'

*.control_files='/u01/app/oracle/oradata/yzdg/control01.ctl','/u01/app/oracle/fast_recovery_area/yzdg/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='gydg'

*.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=gydgXDB)'

*.fal_client='YZDG'

*.fal_server='GYDG'

*.log_archive_config='DG_CONFIG=(yzdg,gydg)'

*.log_archive_dest_1='LOCATION=/u01/app/oracle/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=yzdg'

*.log_archive_dest_2='SERVICE=gydg LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=gydg'

*.log_archive_dest_state_1='ENABLE'

*.log_archive_dest_state_2='ENABLE'

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

*.memory_target=1270874112

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

*.DB_UNIQUE_NAME='yzdg'

*.db_file_name_convert='/u01/app/oracle/oradata/gydg/','/u01/app/oracle/oradata/yzdg/'

[oracle@gydg admin]$ mkdir -p   /u01/app/oracle/oradata/logfile/              (主备都创建)

[oracle@gydg admin]$ mkdir -p  /u01/app/oracle/fast_recovery_area/yzdg

[oracle@gydg admin]$ mkdir -p /u01/app/oracle/oradata/yzdg/

[oracle@gydg admin]$ mkdir -p /u01/app/oracle/oradata/gydg/

在备库创建spfile,并启动数据库到NOMOUNT

[oracle@yzdg yzdg]$ echo $ORACLE_SID

yzdg

SQL> startup nomount;

SQL> create spfile from pfile;

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

重启主库

SQL> shutdown immediate;

SQL> startup

duplicate开始

在主库上通过rman进行复制备库(注意在这一步之前必须退出备库的所有连接,否则会报错)

rman target sys/123456 auxiliary sys/123456@yzdg

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

把备库启动到open only下面。并recover

SQL> alter database open read only;

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

查看两边状态：主库

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE    SWITCHOVER_STATUS

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

PRIMARY          TO STANDBY

备库

SQL> select database_role,switchover_status from v$database;

DATABASE_ROLE    SWITCHOVER_STATUS

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

PHYSICAL STANDBY NOT ALLOWED

注： 备库的SWITCHOVER_STATUS状态为 NOT ALLOWED  是正常的，只有当主库切换为备库时，原备库的状态才会变。

查看两边的sequence# 是否一致

SQL> select max(sequence#) from v$archived_log;

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

下面就是开始测试了。这里我们是用的SCOTT这个用户来做测试，在SCOTT用户下面创建一个表，在备库去查询看是否创建成功，与在主库上面SWIRCH一次日志，看alert中的日志输出内容。

在主库上面做测试

[oracle@11g admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Fri Sep 7 05:57:41 2012

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database set standby to maximize  availability;

Database altered.

SQL> alter user scott identified by oracle;

User altered.

SQL> alter user scott account unlock;

User altered.

SQL> create table scott.test1 as select * from dba_objects;

Table created.

备库上面

SQL> select count(*) from scott.test1;

COUNT(*)

----------

72391

说明已经同步

主库上面刷新日志。

SQL> conn / as sysdba

Connected.

SQL> alter system archive log current;

System altered.

日志

ALTER SYSTEM ARCHIVE LOG

Fri Sep 07 06:04:45 2012

LGWR: Standby redo logfile selected to archive thread 1 sequence 11

LGWR: Standby redo logfile selected for thread 1 sequence 11 for destination LOG_ARCHIVE_DEST_2

Thread 1 advanced to log sequence 11 (LGWR switch)

Current log# 2 seq# 11 mem# 0: /u01/app/oracle/oradata/htz/redo02.log

Fri Sep 07 06:04:48 2012

Archived Log entry 12 added for thread 1 sequence 10 ID 0x6e285e98 dest 1:

备库日志

Standby controlfile consistent with primary

RFS[5]: Selected log 5 for thread 1 sequence 11 dbid 1848107928 branch 793343515

Thu Sep 06 18:40:13 2012

Media Recovery Waiting for thread 1 sequence 11 (in transit)

Recovery of Online Redo Log: Thread 1 Group 5 Seq 11 Reading mem 0

Mem# 0: /u01/app/oracle/oradata/htz/standby02.log

Thu Sep 06 18:40:16 2012

Archived Log entry 5 added for thread 1 sequence 10 ID 0x6e285e98 dest 1: