11gR2 ASM RAC + ASM RAC dataguard配置

alter database archivelog;

RACDB =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 11grac1-vip.oracle.com)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = 11grac2-vip.oracle.com)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = racdb_DGMGRL)

)

RACDB1 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 11grac1-vip.oracle.com)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = racdb_DGMGRL)

(INSTANCE_NAME = racdb1)

)

RACDB2 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 11grac2-vip.oracle.com)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = racdb_DGMGRL)

(INSTANCE_NAME = racdb2)

)

RACDBDG =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 11gracdg1-vip.oracle.com)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = 11gracdg2-vip.oracle.com)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = racdbdg_DGMGRL)

)

RACDBDG1 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 11gracdg1-vip.oracle.com)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = racdbdg_DGMGRL)

(INSTANCE_NAME = racdbdg1)

)

RACDBDG2 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 11gracdg2-vip.oracle.com)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = racdbdg_DGMGRL)

(INSTANCE_NAME = racdbdg2)

)

4、primary端修改数据库参数,将数据库置于归档模式和force logging

alter system set log_archive_config='dg_config=(racdb,racdbdg)' scope=spfile sid='*';

alter system set log_archive_dest_1='LOCATION=+data VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=racdb' scope=spfile sid='*';

alter system set log_archive_dest_2='SERVICE=racdbdg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=racdbdg' scope=spfile sid='*';

alter system set log_archive_format = '%t_%s_%r.arc' scope=spfile sid='*';

alter system set remote_login_passwordfile=EXCLUSIVE scope=spfile sid='*';

alter system set standby_file_management=auto scope=spfile sid='*';

alter system set db_file_name_convert='+datadg','+data' scope=spfile sid='*';

alter system set log_file_name_convert='+datadg','+data' scope=spfile sid='*';

$ srvctl stop database -d racdb -o immediate

startup mount;

alter database open;

alter database force logging;

shutdown immediate

$ srvctl start database -d racdb

5、配置standby端的静态注册和tnsnames.ora

--节点1 grid用户的listener.ora:

--节点2 grid用户的listener.ora:

SID_LIST_LISTENER =

(SID_DESC =

(GLOBAL_DBNAME = racdbdg_DGMGRL)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/dbhome_1)

(SID_NAME = racdbdg1)

)

--节点1和节点2 的oracle用户下的的tnsnames.ora:

SID_LIST_LISTENER =

(SID_DESC =

(GLOBAL_DBNAME = racdbdg_DGMGRL)

(ORACLE_HOME = /u01/app/oracle/product/11.2.0.4/dbhome_1)

(SID_NAME = racdbdg2)

)

SQL> create pfile='/tmp/pfile.ora' from spfile;

RACDB =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 11grac1-vip.oracle.com)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = 11grac2-vip.oracle.com)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = racdb_DGMGRL)

)

RACDB1 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 11grac1-vip.oracle.com)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = racdb_DGMGRL)

(INSTANCE_NAME = racdb1)

)

RACDB2 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 11grac2-vip.oracle.com)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = racdb_DGMGRL)

(INSTANCE_NAME = racdb2)

)

RACDBDG =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 11gracdg1-vip.oracle.com)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = 11gracdg2-vip.oracle.com)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = racdbdg_DGMGRL)

)

RACDBDG1 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 11gracdg1-vip.oracle.com)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = racdbdg_DGMGRL)

(INSTANCE_NAME = racdbdg1)

)

RACDBDG2 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 11gracdg2-vip.oracle.com)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = racdbdg_DGMGRL)

(INSTANCE_NAME = racdbdg2)

)

6、配置primary端及standby端的hosts解析

### 11g rac ###

192.168.56.120 11grac1.oracle.com 11grac1

10.0.0.5 11grac1-priv1.oracle.com 11grac1-priv1

10.0.0.7 11grac1-priv2.oracle.com 11grac1-priv2

192.168.56.121 11grac1-vip.oracle.com 11grac1-vip

192.168.56.122 11grac2.oracle.com 11grac2

10.0.0.6 11grac2-priv1.oracle.com 11grac2-priv1

10.0.0.8 11grac2-priv2.oracle.com 11grac2-priv2

192.168.56.123 11grac2-vip.oracle.com 11grac2-vip

192.168.56.124 rac-scan.oracle.com rac-scan

### 11rac dg###

192.168.56.125 11gracdg1.oracle.com 11gracdg1

10.0.0.10 11gracdg1-priv1.oracle.com 11gracdg1-priv1

10.0.0.11 11gracdg1-priv2.oracle.com 11gracdg1-priv2

192.168.56.126 11gracdg1-vip.oracle.com 11gracdg1-vip

192.168.56.127 11gracdg2.oracle.com 11gracdg2

10.0.0.12 11gracdg2-priv1.oracle.com 11gracdg2-priv1

10.0.0.13 11gracdg2-priv2.oracle.com 11gracdg2-priv2

192.168.56.128 11gracdg2-vip.oracle.com 11gracdg2-vip

192.168.56.129 racdg-scan.oracle.com racdg-scan

7、primary端生成pfile,将生成的pfile和密码复制至standby端

$ scp /tmp/pfile.ora 11gracdg1:$ORACLE_HOME/dbs

cd $ORACLE_HOME/dbs

$ scp orapwracdb1 11gracdg1:$ORACLE_HOME/dbs

8、在primary库做RMAN全备及备份控制文件

rman target /

backup full database format='/u01/software/for_standby_%u%p%s.RMN' include current controlfile for standby;

SQL> alter system archive log current;

9、将primary库生成的RMAN备份集上传至standby库的相同目录

$ scp for_standby_*.RMN 11gracdg1:/u01/software

10、primary端创建standby logfile

---- 在primary端创建standby logfile(每组thread都需要):

alter system set standby_file_management=manual sid='*';

alter database add standby logfile thread 1 group 7 '+data/racdb/onlinelog/standby_07.log' size 50m;

alter database add standby logfile thread 1 group 8 '+data/racdb/onlinelog/standby_08.log' size 50m;

alter database add standby logfile thread 1 group 9 '+data/racdb/onlinelog/standby_09.log' size 50m;

alter database add standby logfile thread 2 group 10 '+data/racdb/onlinelog/standby_10.log' size 50m;

alter database add standby logfile thread 2 group 11 '+data/racdb/onlinelog/standby_11.log' size 50m;

alter database add standby logfile thread 2 group 12 '+data/racdb/onlinelog/standby_12.log' size 50m;

alter system set standby_file_management=auto sid='*';

11、修改standby端的pfile文件和密码文件,并启动至nomount状态

修改之后的参数：

[oracle@11gracdg1 dbs]$ more initracdbdg1.ora

*.audit_file_dest='/u01/app/oracle/admin/racdb/adump'

*.audit_trail='db'

*.cluster_database=true

*.compatible='11.2.0.4.0'

*.control_files='+DATAdg/racdb/controlfile/control01.ctl'

*.db_block_size=8192

*.db_create_file_dest='+DATADG'

*.db_domain=''

*.db_file_name_convert='+data','+datadg'

*.db_name='racdb'

*.db_unique_name='racdbdg'

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

racdbdg1.instance_number=1

racdbdg2.instance_number=2

*.log_archive_config='dg_config=(racdb,racdbdg)'

*.log_archive_dest_1='LOCATION=+datadg VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=racdbdg'

*.log_archive_dest_2='SERVICE=racdb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=racdb'

*.log_archive_format='%t_%s_%r.arc'

*.log_file_name_convert='+data','+datadg'

*.open_cursors=300

*.pga_aggregate_target=209715200

*.processes=300

*.remote_listener='racdg-scan.oracle.com:1521'

*.remote_login_passwordfile='EXCLUSIVE'

*.sessions=335

*.sga_target=1073741824

*.standby_file_management='AUTO'

racdbdg1.thread=1

racdbdg2.thread=2

racdbdg1.undo_tablespace='UNDOTBS1'

racdbdg2.undo_tablespace='UNDOTBS2'

[oracle@11gracdg1 dbs]$

SQL> startup nomount;

ORACLE instance started.

Total System Global Area 419430400 bytes

Fixed Size 1262140 bytes

Variable Size 322964932 bytes

Database Buffers 92274688 bytes

Redo Buffers 2928640 bytes

standby端的所有节点都启动至nomount状态：

Standby端的第一个节点复制 pfile和密码文件至其它节点，并启动至nomount:

[oracle@11gracdg1 dbs]$ scp initracdbdg1.ora 192.168.56.132:/u01/app/oracle/product/11.2.0.4/db_home/dbs/

initracdbdg1.ora 100% 1185 1.2KB/s 00:00

[oracle@11gracdg1 dbs]$ scp orapwracdbdg1 192.168.56.132:/u01/app/oracle/product/11.2.0.4/db_home/dbs/

orapwracdbdg1 100% 1536 1.5KB/s 00:00

[oracle@11gracdg1 dbs]$

12、配置及测试主备库的监听及TNS

sqlplus sys/welcome1@racdb1 as sysdba

sqlplus sys/welcome1@racdb2 as sysdba

sqlplus sys/welcome1@racdb as sysdba

sqlplus sys/welcome1@racdbdg1 as sysdba

sqlplus sys/welcome1@racdbdg2 as sysdba

sqlplus sys/welcome1@racdbdg as sysdba

13、将主库的文件复制至备库

在源端操作:

rman target / auxiliary sys/welcome1@racdbdg1

duplicate target database for standby nofilenamecheck;

注意:

如果standby端的RAC，只是安装了软件，则$ORACLE_HOME/bin/oracle执行文件的属组为oracle:oinstall，但这个属组是存在问题的，正确的属性为oracle: asmadmin

如果不修改这个属组问题，则在使用rman 进行 duplicate 或restore, recover时会报错：

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece /u01/software/for_standby_03r7gctp13.RMN

channel ORA_AUX_DISK_1: ORA-19870: error while restoring backup piece /u01/software/for_standby_03r7gctp13.RMN

ORA-19504: failed to create file "+DATADG"

ORA-17502: ksfdcre:4 Failed to create file +DATADG

ORA-15001: diskgroup "DATADG" does not exist or is not mounted

ORA-15040: diskgroup is incomplete

ORA-15040: diskgroup is incomplet

failover to previous backup

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of Duplicate Db command at 06/06/2016 23:25:08

RMAN-05501: aborting duplication of target database

RMAN-03015: error occurred in stored script Memory Script

RMAN-06026: some targets not found - aborting restore

RMAN-06024: no backup or copy of the control file found to restore

RMAN>

修改完属组后，问题消失。

2、使用DBCA创建第一个数据库时，系统会在DBCA的时候调用命令进行属组的修改。

具体命令如下：

# /u01/app/11.2.0.4/grid/bin/setasmgidwrap o=/u01/app/oracle/product/11.2.0.4/dbhome_1/bin/oracle

14、启动standby库(先检测是否能应用归档日志)

shutdown immediate;

startup nomount;

alter database mount standby database;

alter database recover managed standby database disconnect from session;

如果能应用归档日志,说明配置成功.

--源端:

SQL> alter system archive log current;

Tue Apr 08 13:17:43 CST 2014

Redo Shipping Client Connected as PUBLIC

-- Connected User is Valid

RFS[1]: Assigned to RFS process 11466

RFS[1]: Identified database type as 'physical standby'

Tue Apr 08 13:17:43 CST 2014

RFS LogMiner: Client disabled from further notification

SUCCESS: diskgroup FAR1 was mounted

RFS[1]: Archived Log: '+FAR1/standby/archivelog/2014_04_08/thread_2_seq_10.262.844348665'

RFS[1]: Archived Log: '+FAR1/standby/archivelog/2014_04_08/thread_2_seq_11.263.844348665'

RFS[1]: Archived Log: '+FAR1/standby/archivelog/2014_04_08/thread_2_seq_12.264.844348667'

Tue Apr 08 13:17:57 CST 2014

SUCCESS: diskgroup FAR1 was dismounted

Tue Apr 08 13:18:15 CST 2014

Redo Shipping Client Connected as PUBLIC

-- Connected User is Valid

RFS[2]: Assigned to RFS process 11675

RFS[2]: Identified database type as 'physical standby'

SUCCESS: diskgroup FAR1 was mounted

RFS[2]: Archived Log: '+FAR1/standby/archivelog/2014_04_08/thread_1_seq_14.265.844348697'

RFS[2]: Archived Log: '+FAR1/standby/archivelog/2014_04_08/thread_1_seq_15.266.844348697'

RFS[2]: Archived Log: '+FAR1/standby/archivelog/2014_04_08/thread_1_seq_16.267.844348697'

Tue Apr 08 13:18:23 CST 2014

SUCCESS: diskgroup FAR1 was dismounted

Tue Apr 08 13:18:24 CST 2014

Media Recovery Log +FAR1/standby/archivelog/2014_04_08/thread_1_seq_14.265.844348697

SUCCESS: diskgroup FAR1 was mounted

Media Recovery Log +FAR1/standby/archivelog/2014_04_08/thread_2_seq_10.262.844348665

Media Recovery Log +FAR1/standby/archivelog/2014_04_08/thread_2_seq_11.263.844348665

Media Recovery Log +FAR1/standby/archivelog/2014_04_08/thread_1_seq_15.266.844348697

Media Recovery Log +FAR1/standby/archivelog/2014_04_08/thread_1_seq_16.267.844348697

Media Recovery Log +FAR1/standby/archivelog/2014_04_08/thread_2_seq_12.264.844348667

Media Recovery Waiting for thread 1 sequence 17

Tue Apr 08 13:18:36 CST 2014

SUCCESS: diskgroup FAR1 was dismounted

15、standby端创建standby logfile

---- 在standby端创建standby logfile(每组thread都需要):

alter system set standby_file_management=manual sid='*';

alter database recover managed standby database cancel;

alter database add standby logfile thread 1 group 7 '+datadg/racdbdg/onlinelog/standby_07.log' size 50m;

alter database add standby logfile thread 1 group 8 '+datadg/racdbdg/onlinelog/standby_08.log' size 50m;

alter database add standby logfile thread 1 group 9 '+datadg/racdbdg/onlinelog/standby_09.log' size 50m;

alter database add standby logfile thread 1 group 10 '+datadg/racdbdg/onlinelog/standby_10.log' size 50m;

alter database add standby logfile thread 2 group 11 '+datadg/racdbdg/onlinelog/standby_11.log' size 50m;

alter database add standby logfile thread 2 group 12 '+datadg/racdbdg/onlinelog/standby_12.log' size 50m;

alter database add standby logfile thread 2 group 13 '+datadg/racdbdg/onlinelog/standby_13.log' size 50m;

alter database add standby logfile thread 2 group 14 '+datadg/racdbdg/onlinelog/standby_14.log' size 50m;

alter system set standby_file_management=auto sid='*';

16、注册standby 端的database 资源

--生成新的spfile文件:

SQL> create spfile='+datadg/racdbdg/spfileracdbdg.ora' from pfile='/u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/initracdbdg1.ora';

File created.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

--修改pfile文件:

[oracle@10gracdg1 dbs]$ mv initracdbdg1.ora initracdbdg1.ora_bak

[oracle@10gracdg1 dbs]$ vi initracdbdg1.ora

添加如下内容:

spfile='+datadg/racdbdg/spfileracdbdg.ora'

--添加资源:

srvctl add database -d racdbdg -o /u01/app/oracle/product/11.2.0.4/dbhome_1 -p +datadg/racdbdg/spfileracdbdg.ora

srvctl add instance -d racdbdg -i racdbdg1 -n 11gracdg1

srvctl add instance -d racdbdg -i racdbdg2 -n 11gracdg2

注意:

在注册资源时，数据库名必须设置为db_unique_name, 而不能设置成db_name, 否则在dgmgrl命令行中用switchover to standby时，报CRS 不允许的错误，导致无法正常切换。

[root@11gracdg2 ~]# crsctl status resource -t

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

NAME TARGET STATE SERVER STATE_DETAILS

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

Local Resources

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

ora.CRSDG.dg

ONLINE ONLINE 11gracdg1

ONLINE ONLINE 11gracdg2

ora.DATADG.dg

ONLINE ONLINE 11gracdg1

ONLINE ONLINE 11gracdg2

ora.LISTENER.lsnr

ONLINE ONLINE 11gracdg1

ONLINE ONLINE 11gracdg2

ora.asm

ONLINE ONLINE 11gracdg1 Started

ONLINE ONLINE 11gracdg2 Started

ora.gsd

OFFLINE OFFLINE 11gracdg1

OFFLINE OFFLINE 11gracdg2

ora.net1.network

ONLINE ONLINE 11gracdg1

ONLINE ONLINE 11gracdg2

ora.ons

ONLINE ONLINE 11gracdg1

ONLINE ONLINE 11gracdg2

ora.registry.acfs

ONLINE ONLINE 11gracdg1

ONLINE ONLINE 11gracdg2

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

Cluster Resources

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

ora.11gracdg1.vip

1 ONLINE ONLINE 11gracdg1

ora.11gracdg2.vip

1 ONLINE ONLINE 11gracdg2

ora.LISTENER_SCAN1.lsnr

1 ONLINE ONLINE 11gracdg2

ora.cvu

1 ONLINE ONLINE 11gracdg1

ora.oc4j

1 ONLINE ONLINE 11gracdg1

ora.racdbdg.db

1 ONLINE ONLINE 11gracdg1 Open

2 ONLINE ONLINE 11gracdg2 Open

ora.scan1.vip

1 ONLINE ONLINE 11gracdg2

[root@11gracdg2 ~]#

[oracle@11gracdg1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Jan 22 15:06:36 2017

Connected to:

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

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

Data Mining and Real Application Testing options

SQL> select open_mode from v$database;

READ ONLY

SQL> /

[oracle@11gracdg2 dbs]$ sqlplus / as sysdba

READ ONLY WITH APPLY

SQL>

---节点2上执行日志恢复：

SQL*Plus: Release 11.2.0.4.0 Production on Sun Jan 22 15:06:51 2017

Connected to:

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

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

Data Mining and Real Application Testing options

SQL> select open_mode from v$database;

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

READ ONLY

Database altered.

SQL> select open_mode from v$database;