DATAGUARD-physical standby安装配置

在前一章，我们介绍了DATAGUARD。这一章我们来亲自搭建一个DG环境来体验一下。

DATAGUARD介绍，请参看 我的博文 《DATAGUARD概述》

下面是physical standby安装配置过程

一、环境说明

primary环境信息 
IP : 192.168.152.10 
ORACLE_SID=PROD 
数据库版本 10.2.0.1 
操作系统 Red Hat Enterprise Linux Server release 5.9 (Tikanga) 32位

standby环境信息 
IP : 192.168.152.20 
ORACLE_SID=OCM3 
数据库版本 10.2.0.1 
操作系统 Red Hat Enterprise Linux Server release 5.9 (Tikanga) 32位

二、primary database配置

1、检查数据库是否启用forced logging模式

SYS@PROD>select name,LOG_MODE,OPEN_MODE,FORCE_LOGGING from v$database;NAME      LOG_MODE     OPEN_MODE  FOR --------- ------------ ---------- --- PROD      ARCHIVELOG   READ WRITE NO

检查发现force_logging列显示为NO，说明该数据库forced logging模式没有启动。下面将该库启用forced logging模式

SYS@PROD>startup mount ORACLE instance started.Total System Global Area  524288000 bytes Fixed Size                  1220384 bytes Variable Size             188743904 bytes Database Buffers          331350016 bytes Redo Buffers                2973696 bytes Database mounted. SYS@PROD>alter database force logging;Database altered.SYS@PROD>alter database open;Database altered.SYS@PROD>select name,LOG_MODE,OPEN_MODE,FORCE_LOGGING from v$database;NAME      LOG_MODE     OPEN_MODE  FOR --------- ------------ ---------- --- PROD      ARCHIVELOG   READ WRITE YES

2、创建密码文件

[oracle@ocm1 dbs]$ orapwd file=orapwPROD password=oracle entries=30 force=y

3、配置standby redolog

standby redolog是最大保护模式、最大可用模式和日志传输模式所要求使用的。 
standby redolog 文件大小要与online redo log文件大小一致 
standby redolog groups 要比 online redo log groups 多1个 
检查maxlogfiles和maxlogmembers参数值。确认添加的standby redo 不会超过这个值。

检查online redolog信息

 THREAD#     GROUP#  SEQUENCE# ARC STATUS           FILE_SIZEMB MEMBER ---------- ---------- ---------- --- ---------------- ----------- --------------------------------------------------          1          1          7 YES INACTIVE                 100 /u01/app/oracle/oradata/PROD/disk1/redo01.log          1          1          7 YES INACTIVE                 100 /u01/app/oracle/oradata/PROD/disk1/redo04.log          1          2          8 YES INACTIVE                 100 /u01/app/oracle/oradata/PROD/disk1/redo05.log          1          2          8 YES INACTIVE                 100 /u01/app/oracle/oradata/PROD/disk1/redo02.log          1          3          9 NO  CURRENT                  100 /u01/app/oracle/oradata/PROD/disk1/redo06.log          1          3          9 NO  CURRENT                  100 /u01/app/oracle/oradata/PROD/disk1/redo03.log

创建standby redolog

alter database add standby logfile group 4 ('/u01/app/oracle/oradata/PROD/disk1/standbylog4a.log','/u01/app/oracle/oradata/PROD/disk1/standbylog4b.log') size 100M;alter database add standby logfile group 5 ('/u01/app/oracle/oradata/PROD/disk1/standbylog5a.log','/u01/app/oracle/oradata/PROD/disk1/standbylog5b.log') size 100M;alter database add standby logfile group 6 ('/u01/app/oracle/oradata/PROD/disk1/standbylog6a.log','/u01/app/oracle/oradata/PROD/disk1/standbylog6b.log') size 100M;alter database add standby logfile group 7 ('/u01/app/oracle/oradata/PROD/disk1/standbylog7a.log','/u01/app/oracle/oradata/PROD/disk1/standbylog7b.log') size 100M;

确认standby redolog信息

SYS@PROD>select group#,thread#,sequence#,bytes/1024/1024 sizeMB,archived,status from v$standby_log;    GROUP#    THREAD#  SEQUENCE#     SIZEMB ARC STATUS ---------- ---------- ---------- ---------- --- ----------          4          0          0        100 YES UNASSIGNED          5          0          0        100 YES UNASSIGNED          6          0          0        100 YES UNASSIGNED          7          0          0        100 YES UNASSIGNED

4、配置tnsnames.ora文件

PROD=    (DESCRIPTION=        (ADDRESS=(PROTOCOL=tcp)(HOST=ocm1)(PORT=1521))        (CONNECT_DATA=            (SERVER=DEDICATED)            (SERVICE_NAME=PROD)        )    ) OCM3=    (DESCRIPTION=        (ADDRESS=(PROTOCOL=tcp)(HOST=ocm2)(PORT=1521))        (CONNECT_DATA=            (SERVER=DEDICATED)            (SERVICE_NAME=OCM3)        )    )

PROD是连接primary database的连接。OCM3是连接standby database的连接。

5、配置参数文件

主库角色参数，请参考下列参数值修改

DB_UNIQUE_NAME=PROD LOG_ARCHIVE_CONFIG='DG_CONFIG=(PROD,OCM3)' LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/archlog  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PROD' LOG_ARCHIVE_DEST_2='SERVICE=OCM3 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=OCM3' LOG_ARCHIVE_DEST_STATE_1=ENABLE LOG_ARCHIVE_DEST_STATE_2=ENABLE REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE LOG_ARCHIVE_FORMAT=%t_%s_%r.arc LOG_ARCHIVE_MAX_PROCESSES=30

备库角色参数，请参考下列参数值修改

FAL_SERVER=OCM3 FAL_CLIENT=PROD DB_FILE_NAME_CONVERT='OCM3','PROD' LOG_FILE_NAME_CONVERT='OCM3','PROD' STANDBY_FILE_MANAGEMENT=AUTO   

6、检查数据库是否启动在归档状态 

SYS@PROD>archive log list Database log mode              Archive Mode Automatic archival             Enabled Archive destination            /u01/app/oracle/archlog Oldest online log sequence     8 Next log sequence to archive   10 Current log sequence           10

如果数据库为非归档模式，则需要将数据库开启归档模式。

7、启动primary database 的监听

[oracle@ocm1 ~]$ lsnrctl start

三、创建physical standby database

1、对primary database进行备份，这里的备份可以采用冷备或者rman备份。

[oracle@ocm1 PROD]$ tar -zcvf primary_datafile.tar.gz disk1/

2、创建standby controlfile

SYS@PROD>startup mount SYS@PROD>alter database create standby controlfile as '/home/oracle/standby_controlfile.ctl'; SYS@PROD>alter database open;

3、参数文件调整

根据primary database的参数进行修改，需要调整的参数如下，请参考

*.control_files='/u01/app/oracle/oradata/OCM3/disk1/control01.ctl','/u01/app/oracle/oradata/OCM3/disk1/control02.ctl','/u01/app/oracle/oradata/OCM3/disk1/control03.ctl'#Restore Controlfile *.DB_FILE_NAME_CONVERT='PROD','OCM3' *.DB_UNIQUE_NAME='OCM3' *.FAL_CLIENT='OCM3' *.FAL_SERVER='PROD' *.LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/archlog  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=OCM3' *.LOG_ARCHIVE_DEST_2='SERVICE=PROD LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PROD' *.LOG_FILE_NAME_CONVERT='PROD','OCM3'

4、将相关数据拷贝到备库主机

[oracle@ocm1 ~]$ scp standby_controlfile.ctl oracle@ocm2:~ [oracle@ocm1 ~]$ scp standby_pfile.ora oracle@ocm2:~ [oracle@ocm1 PROD]$ scp primary_datafile.tar.gz oracle@ocm2:/u01/app/oracle/oradata [oracle@ocm1 dbs]$ scp orapwPROD oracle@ocm2:/u01/app/oracle/product/10.2.0/db_1/dbs

5、配置standby database环境

1）创建密码文件

我们这边的密码文件是从primary直接拷贝过来的，因此直接改名即可 

[oracle@ocm2 dbs]$ mv orapwPROD orapwOCM3

2）创建相关路径 

mkdir -p /u01/app/oracle/admin/OCM3/adump mkdir -p /u01/app/oracle/admin/OCM3/bdump mkdir -p /u01/app/oracle/admin/OCM3/cdump mkdir -p /u01/app/oracle/admin/OCM3/udump 

3）将备份的数据文件放到指定路径

[oracle@ocm2 ~]$ cd /u01/app/oracle/oradata [oracle@ocm2 ~]$ tar -zxvf primary_datafile.tar.gz -C /u01/app/oracle/oradata/OCM3 

4）使用standby controlfile替换原来的controlfile文件

[oracle@ocm2 disk1]$ cd /u01/app/oracle/oradata/OCM3/disk1 [oracle@ocm2 disk1]$ rm *.ctl [oracle@ocm2 ~]$ cp standby_controlfile.ctl /u01/app/oracle/oradata/OCM3/disk1/control01.ctl [oracle@ocm2 ~]$ cp standby_controlfile.ctl /u01/app/oracle/oradata/OCM3/disk1/control02.ctl [oracle@ocm2 ~]$ cp standby_controlfile.ctl /u01/app/oracle/oradata/OCM3/disk1/control03.ctl 

5）启动监听

[oracle@ocm2 ~]$ cd $ORACLE_HOME/network/admin [oracle@ocm2 admin]$ lsnrctl start 

6）配置tnsnames.ora文件，这个文件与primary database保持一致即可。

6、启动physical standby database

SYS@OCM3>startup mountSYS@OCM3>alter database recover managed standby database disconnect from session;

四、DG数据同步测试

在standby database检查归档日志应用情况，可以看到sequence 9,10已经被应用。

SYS@OCM3>select sequence#,first_time,next_time,applied from v$archived_log order by sequence#;SEQUENCE# FIRST_TIM NEXT_TIME APP ---------- --------- --------- ---          1 13-JAN-14 13-JAN-14 NO          2 13-JAN-14 20-JAN-14 NO          3 20-JAN-14 22-JAN-14 NO          4 22-JAN-14 24-JAN-14 NO          5 24-JAN-14 28-JAN-14 NO          6 28-JAN-14 03-MAR-14 NO          7 03-MAR-14 03-MAR-14 NO          8 03-MAR-14 03-MAR-14 NO          9 03-MAR-14 04-MAR-14 YES         10 04-MAR-14 04-MAR-14 YES10 rows selected.

在primary database做日志切换动作，检查新生成的归档是否被standby database应用

SYS@PROD>alter system switch logfile;System altered.

从视图可以看到新的sequence 11已经被应用。

SYS@OCM3>select sequence#,first_time,next_time,applied from v$archived_log order by sequence#; SEQUENCE# FIRST_TIM NEXT_TIME APP---------- --------- --------- ---         1 13-JAN-14 13-JAN-14 NO         2 13-JAN-14 20-JAN-14 NO         3 20-JAN-14 22-JAN-14 NO         4 22-JAN-14 24-JAN-14 NO         5 24-JAN-14 28-JAN-14 NO         6 28-JAN-14 03-MAR-14 NO         7 03-MAR-14 03-MAR-14 NO         8 03-MAR-14 03-MAR-14 NO         9 03-MAR-14 04-MAR-14 YES        10 04-MAR-14 04-MAR-14 YES        11 04-MAR-14 04-MAR-14 YES11 rows selected.

检查两边数据库角色

在primary database

SYS@PROD>select database_role,switchover_status from v$database;DATABASE_ROLE    SWITCHOVER_STATUS---------------- --------------------PRIMARY          TO STANDBY

在standby database

SYS@OCM3>select database_role,switchover_status from v$database;DATABASE_ROLE    SWITCHOVER_STATUS---------------- --------------------PHYSICAL STANDBY NOT ALLOWED

到这里一个physical standby安装就完成了。相对于RAC来说，DG的安装还是相对简单的。


****************************

说明：转载请注明出处，谢谢

****************************

--END--