当前位置: 首页 > DUL, INSTALL > 正文

11G RAC搭建RAC ACTIVE DATAGUARD(ADG)

     QQ群里面一个朋友在问11 RAC环境中怎么搭建RAC ADG,其实搭建DG,在RAC与单机环境是一样的,没有任何的区别,都只需要更改那几个参数,下面就在LINUX环境搭建一个RAC TO RACADG

1 环境介绍

1.1 版本与OS介绍

www.htz.pw > select * from v$version;

 

BANNER

——————————————————————————–

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

PL/SQL Release 11.2.0.3.0 – Production

CORE    11.2.0.3.0      Production

TNS for Linux: Version 11.2.0.3.0 – Production

NLSRTL Version 11.2.0.3.0 – Production

 

5 rows selected.

 

www.htz.pw > !lsb_release -a

LSB Version:    :core-4.0-amd64:core-4.0-ia32:core-4.0-noarch:graphics-4.0-amd64:graphics-4.0-ia32:graphics-4.0-noarch:printing-4.0-amd64:printing-4.0-ia32:printing-4.0-noarch

Distributor ID: EnterpriseEnterpriseServer

Description:    Enterprise Linux Enterprise Linux Server release 5.8 (Carthage)

Release:        5.8

Codename:       Carthage

1.2 数据库配置介绍

主库hosts与软件安装目录介绍

192.168.111.13 11rac1

192.168.111.14 11rac2

 

192.168.111.15 11rac1-vip

192.168.111.16 11rac2-vip

 

[root@11rac2 ~]# echo $GRID_HOME

/u01/app/11.2.0/grid

[root@11rac2 ~]# echo $ORACLE_HOME

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

备库hosts与软件安装目录介绍

192.168.111.23 dg1

192.168.111.24 dg2

 

192.168.111.25 dg1-vip

192.168.111.26 dg2-vip

[root@dg2 ~]# echo $GRID_HOME

/u01/app/11.2.0/grid

[root@dg2 ~]# echo $ORACLE_HOME

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

1.3 集群资源状态

先看看目录主备库的资源状态

[root@11rac1 ~]# crsctl stat resource -t

——————————————————————————–

NAME           TARGET  STATE        SERVER                   STATE_DETAILS      

——————————————————————————–

Local Resources

——————————————————————————–

ora.CRS.dg

               ONLINE  ONLINE       11rac1                                      

               ONLINE  ONLINE       11rac2                                      

ora.DATA.dg

               ONLINE  ONLINE       11rac1                                      

               ONLINE  ONLINE       11rac2                                      

ora.LISTENER.lsnr

               ONLINE  ONLINE       11rac1                                      

               ONLINE  ONLINE       11rac2                                      

ora.asm

               ONLINE  ONLINE       11rac1                   Started            

               ONLINE  ONLINE       11rac2                   Started            

ora.gsd

               OFFLINE OFFLINE      11rac1                                      

               OFFLINE OFFLINE      11rac2                                      

ora.net1.network

               ONLINE  ONLINE       11rac1                                      

               ONLINE  ONLINE       11rac2                                      

ora.ons

               ONLINE  ONLINE       11rac1                                      

               ONLINE  ONLINE       11rac2                                      

——————————————————————————–

Cluster Resources

——————————————————————————–

ora.11rac1.vip

      1        ONLINE  ONLINE       11rac1                                      

ora.11rac2.vip

      1        ONLINE  ONLINE       11rac2                                      

ora.LISTENER_SCAN1.lsnr

      1        ONLINE  ONLINE       11rac1                                      

ora.cvu

      1        ONLINE  ONLINE       11rac1                                      

ora.oc4j

      1        ONLINE  ONLINE       11rac1                                      

ora.power.db

      1        ONLINE  ONLINE       11rac2                   Open               

      2        ONLINE  ONLINE       11rac1                   Open               

ora.power.power1.svc

      1        ONLINE  ONLINE       11rac1                                      

ora.power.power2.svc

      1        ONLINE  ONLINE       11rac1                                      

ora.scan1.vip

      1        ONLINE  ONLINE       11rac1  

     

备库资源

[root@dg1 ~]# crsctl stat resource -t

——————————————————————————–

NAME           TARGET  STATE        SERVER                   STATE_DETAILS      

——————————————————————————–

Local Resources

——————————————————————————–

ora.LISTENER.lsnr

               ONLINE  ONLINE       dg1                                         

               ONLINE  ONLINE       dg2                                         

ora.OCR.dg

               ONLINE  ONLINE       dg1                                         

               ONLINE  ONLINE       dg2                                         

ora.asm

               ONLINE  ONLINE       dg1                      Started            

               ONLINE  ONLINE       dg2                      Started            

ora.gsd

               OFFLINE OFFLINE      dg1                                         

               OFFLINE OFFLINE      dg2                                         

ora.net1.network

               ONLINE  ONLINE       dg1                                         

               ONLINE  ONLINE       dg2                                         

ora.ons

               ONLINE  ONLINE       dg1                                         

               ONLINE  ONLINE       dg2                                         

ora.registry.acfs

               ONLINE  ONLINE       dg1                                         

               ONLINE  ONLINE       dg2                                         

——————————————————————————–

Cluster Resources

——————————————————————————–

ora.LISTENER_SCAN1.lsnr

      1        ONLINE  ONLINE       dg2                                         

ora.cvu

      1        ONLINE  ONLINE       dg1                                         

ora.dg1.vip

      1        ONLINE  ONLINE       dg1                                         

ora.dg2.vip

      1        ONLINE  ONLINE       dg2                                         

ora.oc4j

      1        ONLINE  ONLINE       dg1                                         

ora.scan1.vip

      1        ONLINE  ONLINE       dg2

这里可以看到备库是没有创建数据库的,但是有监听资源

2 主库配置

下面介绍主库允许配置一些东西

2.1 启用强制日志

数据库需要运行在归档模式,如果是非归档,需要先更改为归档模式,需要停数据库,配置归档模式见其它的文档,下面只是启用强制日志功能

www.htz.pw > alter database force logging;

 

Database altered.

2.2 配置tnsnames.ora文件

下面需要注意的,我这里直接使用的SCANIP地址,也可以更换成VIP地址,不过需要把所有的节点的VIP地址都写上。

POWER =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = power)

    )

  )

 

DG =

  (DESCRIPTION =

    (ADDRESS_LIST =

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

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = dg)

    )

  )

2.3 主库修改参数

www.htz.pw > alter system set LOG_ARCHIVE_CONFIG=’DG_CONFIG=(power,dg)’ sid=’*’;

System altered.

www.htz.pw > alter system set LOG_ARCHIVE_DEST_1=’LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=power’ sid=’*’;

System altered.

www.htz.pw > alter system set LOG_ARCHIVE_DEST_2=’SERVICE=dg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dg’ sid=’*’;

System altered.

www.htz.pw > alter system set LOG_ARCHIVE_DEST_STATE_2=defer sid=’*’

System altered.

www.htz.pw > alter system set standby_file_management=auto sid=’*’;

System altered.

www.htz.pw > alter system set log_archive_max_processes=10 sid=’*’;

System altered.

www.htz.pw > alter system set db_file_name_convert=’+DATA’, ‘+DATA’ scope=spfile sid=’*’;

System altered.

www.htz.pw > alter system set log_file_name_convert=’+DATA’, ‘+DATA’,’+ARCH’,’+ARCH’ scope=spfile sid=’*’;

System altered.

www.htz.pw > alter system set FAL_SERVER=dg sid=’*’;

System altered.

www.htz.pw > alter system set FAL_CLIENT=power sid=’*’;

System altered.

www.htz.pw > alter system set db_unique_name=power scope=spfile sid=’*’;

System altered.

这里建议见local_listener参数也配置上,特别是需要切换的时候。

2.4 配置备库日志

这一些也是为了要切换才增加的,如果不需要切换,可以在备库mount状态的时候,增加备库日志文件。

这里增加备库日志有2个原则,备库每节点的日志组个数要大于源库每个节点的日志组个数+1,备库日志文件大小的最小值要大于或者等于源库日志文件中的最大值。

www.htz.pw > select * from v$logfile;

 

    GROUP# STATUS  TYPE    MEMBER                                             IS_

———- ——- ——- ————————————————– —

         2         ONLINE  +DATA/power/onlinelog/group_2.264.806651211        NO

         2         ONLINE  +DATA/power/onlinelog/group_2.265.806651213        YES

         1         ONLINE  +DATA/power/onlinelog/group_1.262.806651209        NO

         1         ONLINE  +DATA/power/onlinelog/group_1.263.806651209        YES

         3         ONLINE  +DATA/power/onlinelog/group_3.268.806651413        NO

         3         ONLINE  +DATA/power/onlinelog/group_3.269.806651413        YES

         4         ONLINE  +DATA/power/onlinelog/group_4.270.806651415        NO

         4         ONLINE  +DATA/power/onlinelog/group_4.271.806651415        YES

 

www.htz.pw > alter database add standby logfile thread 1 size 50m;

 

Database altered.

 

www.htz.pw > alter database add standby logfile thread 1 size 50m;

 

Database altered.

 

www.htz.pw > alter database add standby logfile thread 1 size 50m;

 

Database altered.

 

www.htz.pw > alter database add standby logfile thread 2 size 50m;

 

Database altered.

 

www.htz.pw > alter database add standby logfile thread 2 size 50m;

 

Database altered.

 

www.htz.pw > alter database add standby logfile thread 2 size 50m;

 

Database altered.

                                         bytes

GROUP#    THREAD# STATUS     TYPE              M STATUS     FIRST_CHANGE# NEXT_CHANGE# MEMBER                                        First Time

—— ———- ———- ———- ——– ———- ————- ———— ——————————————— ——————-

     1          1 INACTIVE   ONLINE           50                 29652243     29655089 +DATA/power/onlinelog/group_1.263.806651209   20141027 04:22:51

                1 INACTIVE   ONLINE           50                 29652243     29655089 +DATA/power/onlinelog/group_1.262.806651209   20141027 04:22:51

     2          1 CURRENT    ONLINE           50                 29655089   2.8147E+14 +DATA/power/onlinelog/group_2.265.806651213   20141027 04:34:30

                1 CURRENT    ONLINE           50                 29655089   2.8147E+14 +DATA/power/onlinelog/group_2.264.806651211   20141027 04:34:30

     3          2 CURRENT    ONLINE           50                 29655093   2.8147E+14 +DATA/power/onlinelog/group_3.269.806651413   20141027 04:34:34

                2 CURRENT    ONLINE           50                 29655093   2.8147E+14 +DATA/power/onlinelog/group_3.268.806651413   20141027 04:34:34

     4          2 INACTIVE   ONLINE           50                 29652535     29655093 +DATA/power/onlinelog/group_4.271.806651415   20141027 04:25:17

                2 INACTIVE   ONLINE           50                 29652535     29655093 +DATA/power/onlinelog/group_4.270.806651415   20141027 04:25:17

     5          1 UNASSIGNED STANDBY          50                                       +DATA/power/onlinelog/group_5.277.861975521

                1 UNASSIGNED STANDBY          50                                       +DATA/power/onlinelog/group_5.278.861975523

     6          1 UNASSIGNED STANDBY          50                                       +DATA/power/onlinelog/group_6.279.861975539

                1 UNASSIGNED STANDBY          50                                       +DATA/power/onlinelog/group_6.280.861975541

     7          1 UNASSIGNED STANDBY          50                                       +DATA/power/onlinelog/group_7.282.861975545

                1 UNASSIGNED STANDBY          50                                       +DATA/power/onlinelog/group_7.281.861975543

     8          2 UNASSIGNED STANDBY          50                                       +DATA/power/onlinelog/group_8.283.861975553

                2 UNASSIGNED STANDBY          50                                       +DATA/power/onlinelog/group_8.284.861975555

     9          2 UNASSIGNED STANDBY          50                                       +DATA/power/onlinelog/group_9.285.861975559

                2 UNASSIGNED STANDBY          50                                       +DATA/power/onlinelog/group_9.286.861975561

    10          2 UNASSIGNED STANDBY          50                                       +DATA/power/onlinelog/group_10.288.861975565

                2 UNASSIGNED STANDBY          50                                       +DATA/power/onlinelog/group_10.287.861975565

2.5 更改SYS用户密码

要求在2个节点同时更改sys用户的密码,但是不知道为什么,今天我在2个节点都更改了sys用户的密码,最后节点2还是报了密码文件的问题,最后面报错部分

www.htz.pw > alter user sys identified by oracle;

                                        

User altered. 

2.6 配置数据库

在备份数据库的时候,我们需要启用归档路径,不然在还原后,我们需要手动注册归档日志文件到备库中

www.htz.pw >  alter system set log_archive_dest_state_2=enable;

 

System altered.

建议将rman脚本命令写到文件中,以后台的方式运行脚本

[oracle@11rac1 tmp]$ vi backup.sh

rman target / <<EOF

RUN {

ALLOCATE CHANNEL ch00 TYPE DISK;

BACKUP

    SKIP INACCESSIBLE

    TAG hot_db_bk_level0

    FORMAT ‘/soft/backup/bk_%s_%p_%t’

    DATABASE;

    sql ‘alter system archive log current’;

BACKUP

                FORMAT ‘/soft/backup/ar_%s_%p_%t’

                ARCHIVELOG ALL DELETE INPUT;

BACKUP

    FORMAT ‘/soft/backup/sp_%s_%p_%t’

    SPFILE;

BACKUP

                FORMAT ‘/soft/backup/con_%s_%p_%t’

                CURRENT CONTROLFILE FOR STANDBY;

RELEASE CHANNEL ch00;

}

exit

EOF

2.7 备份参数文件

这里将参数文件创建成pfile文件,方式备库使用

www.htz.pw > create spfile from pfile=’/tmp/123.ora’;

 

File created.

 

www.htz.pw > !cat /tmp/123.ora

*.audit_file_dest=’/u01/app/oracle/admin/dg/adump’

*.audit_trail=’none’

*.cluster_database=true

*.compatible=’11.2.0.0.0′

*.control_files=’+DATA/dg/controlfile/current.261.806651205′,’+DATA/dg/controlfile/current.260.806651205′

*.db_block_size=8192

*.db_create_file_dest=’+DATA’

*.db_domain=”

*.db_file_name_convert=’+DATA’,’+DATA’

*.db_name=’power’

*.db_recovery_file_dest=’+DATA’

*.db_recovery_file_dest_size=4558159872

*.diagnostic_dest=’/u01/app/oracle’

*.dispatchers='(PROTOCOL=TCP) (SERVICE=powerXDB)’

*.fal_client=’DG’

*.fal_server=’POWER’

*.db_unique_name=’DG’

dg2.instance_number=2

dg1.instance_number=1

*.log_archive_config=’DG_CONFIG=(power,dg)’

*.log_archive_dest_1=’LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dg’

*.log_archive_dest_2=’SERVICE=power LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=power’

*.log_archive_dest_state_2=’ENABLE’

*.log_archive_max_processes=10

*.log_file_name_convert=’+DATA’,’+DATA’,’+ARCH’,’+ARCH’

*.memory_target=1048576000

*.open_cursors=300

*.processes=150

*.recyclebin=’OFF’

*.remote_listener=’scan-dg:1521′

*.remote_login_passwordfile=’exclusive’

*.service_names=’dg’

*.standby_file_management=’AUTO’

dg2.thread=2

dg1.thread=1

dg2.undo_tablespace=’UNDOTBS2′

dg1.undo_tablespace=’UNDOTBS1′

2.8 传密码文件

这里将主库节点1的密码文件传到两个备库上面

[oracle@11rac1 dbs]$ scp orapwpower1 192.168.111.23:/u01/app/oracle/product/11.2.0/db_1/dbs/

The authenticity of host ‘192.168.111.23 (192.168.111.23)’ can’t be established.

RSA key fingerprint is d4:57:67:e6:64:14:9a:ba:fb:df:6c:8a:3e:ad:2f:c6.

Are you sure you want to continue connecting (yes/no)? yes

Warning: Permanently added ‘192.168.111.23’ (RSA) to the list of known hosts.

 

 

orapwpower1  100% 1536     1.5KB/s   00:00   

[oracle@11rac1 dbs]$

[oracle@11rac1 dbs]$

[oracle@11rac1 dbs]$ scp orapwpower1 192.168.111.24:/u01/app/oracle/product/11.2.0/db_1/dbs/

The authenticity of host ‘192.168.111.24 (192.168.111.24)’ can’t be established.

RSA key fingerprint is d4:57:67:e6:64:14:9a:ba:fb:df:6c:8a:3e:ad:2f:c6.

Are you sure you want to continue connecting (yes/no)? yes

Warning: Permanently added ‘192.168.111.24’ (RSA) to the list of known hosts.

orapwpower1   100% 1536     1.5KB/s   00:00

 

 

[oracle@dg1 dbs]$ mv orapwpower1 orapwdg1

[oracle@dg2 dbs]$ mv orapwpower1 orapwdg2

3 备库操作

3.1 备库参数文件

这里我们将主库的参数文件拿过来做简单的修改就可以了,绿色部分为主要修改部分

*.audit_file_dest=’/u01/app/oracle/admin/dg/adump’

*.audit_trail=’none’

*.cluster_database=true

*.compatible=’11.2.0.0.0′

*.control_files=’+DATA/dg/controlfile/current.257.861978557′,’+DATA/dg/controlfile/current.258.861978559’#Restore Controlfile

*.db_block_size=8192

*.db_create_file_dest=’+DATA’

*.db_domain=”

*.db_file_name_convert=’+DATA’,’+DATA’

*.db_name=’power’

*.db_recovery_file_dest=’+DATA’

*.db_recovery_file_dest_size=4558159872

*.db_unique_name=’DG’

*.diagnostic_dest=’/u01/app/oracle’

*.dispatchers='(PROTOCOL=TCP) (SERVICE=powerXDB)’

*.fal_client=’DG’

*.fal_server=’POWER’

dg2.instance_number=2

dg1.instance_number=1

dg1.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.111.25)(PORT=1521))))’

dg2.local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.111.26)(PORT=1521))))’

*.log_archive_config=’DG_CONFIG=(power,dg)’

*.log_archive_dest_1=’LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dg’

*.log_archive_dest_2=’SERVICE=power LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=power’

*.log_archive_dest_state_2=’ENABLE’

*.log_archive_max_processes=10

*.log_file_name_convert=’+DATA’,’+DATA’,’+ARCH’,’+ARCH’

*.memory_target=1048576000

*.open_cursors=300

*.processes=150

*.recyclebin=’OFF’

*.remote_listener=’scan-dg:1521′

*.remote_login_passwordfile=’exclusive’

*.service_names=’dg’

*.standby_file_management=’AUTO’

dg2.thread=2

dg1.thread=1

dg2.undo_tablespace=’UNDOTBS2′

dg1.undo_tablespace=’UNDOTBS1′

3.2 还原控制文件

在还原控制文件的时候,我们需要增加上standby参数

RMAN> restore standby controlfile from  ‘/soft/backup/con_7_1_862031446’;

 

Starting restore at 27-OCT-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=36 instance=dg1 device type=DISK

 

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:07

output file name=+DATA/dg/controlfile/current.257.861978557

output file name=+DATA/dg/controlfile/current.258.861978559

Finished restore at 27-OCT-14

3.3 还原数据库

RMAN> alter database mount;

 

database mounted

released channel: ORA_DISK_1

 

RMAN>  restore database;

 

Starting restore at 27-OCT-14

Starting implicit crosscheck backup at 27-OCT-14

allocated channel: ORA_DISK_1

Crosschecked 6 objects

Finished implicit crosscheck backup at 27-OCT-14

 

Starting implicit crosscheck copy at 27-OCT-14

using channel ORA_DISK_1

Crosschecked 2 objects

Finished implicit crosscheck copy at 27-OCT-14

 

searching for all files in the recovery area

cataloging files…

no files cataloged

 

using channel ORA_DISK_1

 

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00001 to +DATA/power/datafile/system.256.806651129

channel ORA_DISK_1: restoring datafile 00002 to +DATA/power/datafile/sysaux.257.806651129

channel ORA_DISK_1: restoring datafile 00003 to +DATA/power/datafile/undotbs1.258.806651129

channel ORA_DISK_1: restoring datafile 00004 to +DATA/power/datafile/users.259.806651129

channel ORA_DISK_1: restoring datafile 00005 to +DATA/power/datafile/undotbs2.267.806651333

channel ORA_DISK_1: reading from backup piece /soft/backup/bk_3_1_862031201

channel ORA_DISK_1: piece handle=/soft/backup/bk_3_1_862031201 tag=HOT_DB_BK_LEVEL0

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:05:25

Finished restore at 27-OCT-14

RMAN> recover database noredo;

 

Starting recover at 27-OCT-14

using channel ORA_DISK_1

 

Finished recover at 27-OCT-14

3.4 运行归档

oracle@dg1 ~]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Mon Oct 27 05:55:27 2014

 

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

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

 

www.htz.pw > recover managed standby database disconnect;

Media recovery complete.

www.htz.pw >

3.5 数据库read only状态

www.htz.pw > alter database recover managed standby database cancel;

 

Database altered.

 

www.htz.pw > alter database open;

 

Database altered.

 

www.htz.pw > recover managed standby database using current logfile disconnect;

Media recovery complete.

www.htz.pw > select open_mode from v$database;

 

OPEN_MODE

——————–

READ ONLY WITH APPLY

3.6 测试是否时时同步

www.htz.pw > create table scott.htz as select * from dba_objects;

 

Table created.

 

 

这里没有同步

报下面的错误

 

27-OCT-2014 06:04:11        2 FAL[server, ARC4]: Error 16191 creating remote archivelog file ‘dg’

27-OCT-2014 06:09:50        2 PING[ARC1]: Heartbeat failed to connect to standby ‘dg’. Error is 16191.

27-OCT-2014 06:09:50        2 Error 1017 received logging on to the standby

27-OCT-2014 06:10:51        2 Error 1017 received logging on to the standby

27-OCT-2014 06:10:51        2 PING[ARC1]: Heartbeat failed to connect to standby ‘dg’. Error is 16191.

27-OCT-2014 06:11:52        2 Error 1017 received logging on to the standby

27-OCT-2014 06:11:52        2 PING[ARC1]: Heartbeat failed to connect to standby ‘dg’. Error is 16191.

27-OCT-2014 06:12:52        2 Error 1017 received logging on to the standby

27-OCT-2014 06:12:52        2 PING[ARC1]: Heartbeat failed to connect to standby ‘dg’. Error is 16191.

 

www.htz.pw > show parameter remote

 

NAME                                 TYPE        VALUE

———————————— ———– ——————————

remote_dependencies_mode             string      TIMESTAMP

remote_listener                      string      scan:1521

remote_login_passwordfile            string      EXCLUSIVE

remote_os_authent                    boolean     FALSE

remote_os_roles                      boolean     FALSE

result_cache_remote_expiration       integer     0

www.htz.pw > show parameter redo_transport_user

 

NAME                                 TYPE        VALUE

———————————— ———– ——————————

redo_transport_user                  string

www.htz.pw >  select * from v$pwfile_users;

 

USERNAME                       SYSDB SYSOP SYSAS

—————————— —– —– —–

SYS                            TRUE  TRUE  FALSE

 

www.htz.pw > alter user sys identified by oracle;

 

User altered.

不知道为什么,还是报错,两端的SYS用户密码已经同步,不知道为什么密码文件还是没有同步。只能COPY节点1的密码来节点2

[oracle@11rac1 dbs]$ scp orapwpower1 192.168.111.14:/u01/app/oracle/product/11.2.0/db_1/dbs

orapwpower1   100% 1536     1.5KB/s   00:00   

[oracle@11rac2 dbs]$ rm -rf  orapwpower2

[oracle@11rac2 dbs]$ mv  orapwpower1 orapwpower2

 

报错不在出现

Mon Oct 27 06:41:04 2014

Archived Log entry 85 added for thread 2 sequence 99 ID 0x66ec4741 dest 1:

Mon Oct 27 06:41:18 2014

ARC8: Standby redo logfile selected for thread 2 sequence 99 for destination LOG_ARCHIVE_DEST_2

Mon Oct 27 06:41:21 2014

LNS: Standby redo logfile selected for thread 2 sequence 100 for destination LOG_ARCHIVE_DEST_2

通过创建与删除表测试,两边能时时同步数据

3.7 将备库增加到GRID

 

2节点操作

 

www.htz.pw > startup mount pfile=’/tmp/123.ora’;

ORACLE instance started.

 

Total System Global Area 1043886080 bytes

Fixed Size                  2234960 bytes

Variable Size             666895792 bytes

Database Buffers          369098752 bytes

Redo Buffers                5656576 bytes

Database mounted.

www.htz.pw > create spfile=’+data’ from pfile=’/tmp/123.ora’;

 

File created.

 

www.htz.pw > !vi $ORACLE_HOME/dbs/initdg2.ora

spfile=’+data/dg/parameterfile/spfile.256.861983521′

 

[oracle@dg2 dbs]$ cat initdg2.ora

spfile=’+data/dg/parameterfile/spfile.256.861983521′

[oracle@dg2 dbs]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.3.0 Production on Mon Oct 27 06:53:34 2014

 

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

 

Connected to an idle instance.

 

www.htz.pw > startup

ORACLE instance started.

 

Total System Global Area 1043886080 bytes

Fixed Size                  2234960 bytes

Variable Size             666895792 bytes

Database Buffers          369098752 bytes

Redo Buffers                5656576 bytes

Database mounted.

Database opened.

www.htz.pw > select open_mode from gv$database;

 

OPEN_MODE

——————–

READ ONLY WITH APPLY

READ ONLY WITH APPLY

 

 

[oracle@dg1 trace]$ srvctl add database -d dg -o $ORACLE_HOME -c RAC  -p +data/dg/parameterfile/spfile.256.861983521  -r physical_standby -s ‘READ ONLY’ -a DATA

[oracle@dg1 trace]$ srvctl add instance -d dg -i dg1 -n dg1

[oracle@dg1 trace]$ srvctl add instance -d dg -i dg2 -n dg2

 

[oracle@dg1 ~]$ srvctl start instance -d dg -i dg1

[oracle@dg1 ~]$ srvctl start instance -d dg -i dg2

[oracle@dg1 ~]$ srvctl status database -d dg

Instance dg1 is running on node dg1

Instance dg2 is running on node dg2

 

 

[grid@dg1 ~]$ crsctl stat resource -t

——————————————————————————–

NAME           TARGET  STATE        SERVER                   STATE_DETAILS      

——————————————————————————–

Local Resources

——————————————————————————–

ora.DATA.dg

               ONLINE  ONLINE       dg1                                         

               ONLINE  ONLINE       dg2                                         

ora.LISTENER.lsnr

               ONLINE  ONLINE       dg1                                         

               ONLINE  ONLINE       dg2                                         

ora.OCR.dg

               ONLINE  ONLINE       dg1                                         

               ONLINE  ONLINE       dg2                                         

ora.asm

               ONLINE  ONLINE       dg1                      Started            

               ONLINE  ONLINE       dg2                      Started            

ora.gsd

               OFFLINE OFFLINE      dg1                                         

               OFFLINE OFFLINE      dg2                                         

ora.net1.network

               ONLINE  ONLINE       dg1                                         

               ONLINE  ONLINE       dg2                                         

ora.ons

               ONLINE  ONLINE       dg1                                         

               ONLINE  ONLINE       dg2                                         

ora.registry.acfs

               ONLINE  ONLINE       dg1                                         

               ONLINE  ONLINE       dg2                                         

——————————————————————————–

Cluster Resources

——————————————————————————–

ora.LISTENER_SCAN1.lsnr

      1        ONLINE  ONLINE       dg2                                         

ora.cvu

      1        ONLINE  ONLINE       dg2                                         

ora.dg.db

      1        ONLINE  ONLINE       dg1                      Open,Readonly      

      2        ONLINE  ONLINE       dg2                      Open,Readonly      

ora.dg1.vip

      1        ONLINE  ONLINE       dg1                                         

ora.dg2.vip

      1        ONLINE  ONLINE       dg2                                         

ora.oc4j

      1        ONLINE  ONLINE       dg2                                         

ora.scan1.vip

      1        ONLINE  ONLINE       dg2

节点1操作

[oracle@dg1 sql]$ cd $ORACLE_HOME/dbs

[oracle@dg1 dbs]$ echo “spfile=’+data/dg/parameterfile/spfile.256.861983521′” >initdg1.ora

[oracle@dg1 dbs]$ cat !$

cat initdg1.ora

spfile=’+data/dg/parameterfile/spfile.256.861983521′

[oracle@dg1 dbs]$ rm spfiledg1.ora

3.8 测试ADG关闭一个节点

这里需要指明的ADG数据一个节点crash,整个ADG RAC都会启动到mount状态的,所以这里把这一小节的测试拿出来

关闭备库节点1,看是否能正常运行日志

 

这里使用直接关闭主机的方法

 

这里可以看到节点2自动到mount状态,因为在12.1之前的ADG RAC环境中,当一个节点异常关闭时,所以其它的节点都将会自动切换到mount状态,在11.20.4开启,可以通过参数来控制。如果是在BROKER环境,BROKER将会选择一个节点来自动启动mrp进程。可以看MOS文档:Behavior of Active Dataguard(ADG) When Apply Node Aborts/Crash (文档 ID 1613719.1)有详细的说明。

ora.dg.db

      1        ONLINE  OFFLINE                                                  

      2        ONLINE  INTERMEDIATE dg2                      Mounted (Closed)   

ora.dg1.vip

      1        ONLINE  INTERMEDIATE dg2                      FAILED OVER 

     

 

www.htz.pw > select open_mode from v$database;

 

OPEN_MODE

——————–

MOUNTED

 

www.htz.pw > alter database open;

alter database open

*

ERROR at line 1:

ORA-10458: standby database requires recovery

ORA-01196: file 1 is inconsistent due to a failed media recovery session

ORA-01110: data file 1: ‘+DATA/dg/datafile/system.261.861978615’

 

 

www.htz.pw > recover managed standby database disconnect;

Media recovery complete.

www.htz.pw > recover managed standby database cancel;

Media recovery complete.

www.htz.pw > alter database open;

 

Database altered.

4 测试结束

整个测试结束,其它RAC与单机环境中,配置ADG的命令这些都是一样的,并没有什么特别的地方。这里就没有测试切换了,切换可以个人自己测试了。

本文固定链接: http://www.htz.pw/2014/10/27/11g-rac%e6%90%ad%e5%bb%barac-active-standby-databaseadg.html | 认真就输

该日志由 huangtingzhong 于2014年10月27日发表在 DUL, INSTALL 分类下, 通告目前不可用,你可以至底部留下评论。
原创文章转载请注明: 11G RAC搭建RAC ACTIVE DATAGUARD(ADG) | 认真就输
关键字: , ,