Oracle11gR2 RAC primary+Single standby DG配置实践

很久之前做的实验，今天在CSDN存档一下：

说明：

RAC primary和Single standby配置

2节点RAC和1个single instance组成的data guard环境。

1.环境介绍

Primary database是一个两节的RAC，存储采用raw和ASM混合的方式，具体如下

RAC Primary	Inode1	Inode2
Public IP	172.28.22.246	172.28.22.247
Private IP	172.28.7.70	172.28.7.244
Virtual IP	172.28.22.248	172.28.22.249
Instance	Orcl1	Orcl2
DB_NAME	orcl
Data,Controle file,Redo file	Raw,ASM

Standby database的数据文件放在本地，不用raw和ams方式，具体如下

Single instance standby	说明(inode2)
IP	172.28.7.244
Oracle	安装的非RAC版本
Instance	orcl
Data,Controle file,Redo file	/home/orastd/oradata/orcl

注：因为条件限制，这个实例里的standby database也装在inode2机器上，只是在不同的系统用户下安装的单实例引擎。

2.配置要点

本例中包括了switchover过程，下面按照switchover前后进行介绍。

switchover之前，这时RAC是primary database.

(1) RAC 每个实例都要配置日志发送，日的地都指向standby

(2) 确认日志发送方法，本例使用了默认同步方式，ARCH进程

(3) standby配置日志接收方法，本例使用standby redo log

(4) 启动MRP

switchover之后，这时RAC是standby database.

如果standby是RAC,则日志的接收和恢复可不是同一个instance,术语上把这个两个实例分别叫做receive instance和recover instance.

本例为简化，把二者都统一为一个instance。

(1) single instance的日志只发送到RAC的一个实例

(2) 确认RAC的日志接收方法，本例使用standby redo log

(3) 在RAC的一个实例上启动MRP

3.配置步骤

(1) 配置两个数据库的tnsnames.ora和listener.ora

RAC(rac1,rac2)和standby(orcl)上的tnsnames.ora相同，如下：

ORCL_SINGLE =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 172.28.7.244)(PORT = 1522))

)

(CONNECT_DATA =

(SID = orcl)

)

)

ORCL2 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = inode2-vip)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

(INSTANCE_NAME = orcl2)

)

)

ORCL1 =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = inode1-vip)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = orcl)

(INSTANCE_NAME = orcl1)

)

)

standby上的listener.ora

inode2:orastd:orcl:/home/orastd/product/10.2.0/db_1/network/admin> more listener.ora

LISTENER =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(Host = 172.28.7.244)(Port = 1522))

)

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = orcl)

(ORACLE_HOME = /home/orastd/product/10.2.0/db_1)

(SID_NAME = orcl)

)

)

(2) 准备参数文件

原始的RAC参数文件如下

orcl2.__db_cache_size=142606336

orcl2.__java_pool_size=4194304

orcl1.__java_pool_size=4194304

orcl2.__large_pool_size=4194304

orcl1.__large_pool_size=4194304

orcl1.__shared_pool_size=117440512

orcl2.__shared_pool_size=138412032

orcl2.__streams_pool_size=0

orcl1.__streams_pool_size=0

*.audit_file_dest='/db/oracle/admin/orcl/adump'

*.background_dump_dest='/db/oracle/admin/orcl/bdump'

*.cluster_database_instances=2

*.cluster_database=TRUE

*.compatible='10.2.0.1.0'

*.control_files='/dev/rcontrol1_raw','/dev/rcontrol2_raw','/dev/rcontrol3_raw'

*.core_dump_dest='/db/oracle/admin/orcl/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='orcl'

*.db_recovery_file_dest_size=2147483648

*.db_recovery_file_dest='+DG1'

*.dispatchers='(PROTOCOL=TCP)(SERVICE=orclXDB)'

orcl2.instance_number=2

orcl1.instance_number=1

*.job_queue_processes=10

*.log_archive_config=''

*.log_archive_dest_1='location=/db/oracle'

orcl1.log_archive_dest_1='location=/db/arch1'

orcl2.log_archive_dest_1='location=/db/arch2'

orcl2.log_archive_dest_2='service=orcl1'

orcl1.log_archive_dest_2='service=orcl2'

*.open_cursors=300

*.pga_aggregate_target=96468992

*.processes=150

*.remote_listener='LISTENERS_ORCL'

*.remote_login_passwordfile='exclusive'

*.sga_target=290455552

orcl2.standby_archive_dest='/db/arch1'

orcl1.standby_archive_dest='/db/arch2'

*.standby_file_management='AUTO'

orcl2.thread=2

orcl1.thread=1

*.undo_management='AUTO'

orcl2.undo_tablespace='UNDOTBS2'

orcl1.undo_tablespace='UNDOTBS1'

RAC原参数不变，添加如下参数：

*.log_archive_config='DG_CONFIG=(orcl,orcl_single)'

*.log_archive_dest_3='SERVICE=orcl_single VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl_single'

*.db_file_name_convert='/home/orastd/oradata/orcl/','+DG3/orcl/datafile/','/home/orastd/oradata/orcl/','/dev/'

*.log_file_name_convert='/home/orastd/oradata/orcl/','+DG3/orcl/onlinelog/'

*.standby_file_management=AUTO

*.FAL_SERVER='orcl_single'

orcl1.FAL_CLIENT='orcl1'

orcl2.FAL_CLIENT='orcl2'

注意：

db_file_name_convert、log_file_name_convert是做主备切换时用到，如果不做主备切换这两参数可以不配，而且其它参数可以动态修改，不用重启生效。这两参数要重启后才能生效。

为了文件存储格式的，这两参数的值是成对出现的。

在ASM的RAC中不要更改db_unique_name的值，因为ASM的文件存储方式是按些值存放的。

single standby上的参数initorcl.ora配置:

*.__db_cache_size=150994944

*.__java_pool_size=4194304

*.__large_pool_size=4194304

*.__shared_pool_size=130023424

*.__streams_pool_size=0

*.compatible='10.2.0.1.0'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='orcl'

*.db_recovery_file_dest_size=2147483648

*.dispatchers='(PROTOCOL=TCP)(SERVICE=orclXDB)'

*.job_queue_processes=10

*.open_cursors=300

*.pga_aggregate_target=96468992

*.processes=150

*.remote_login_passwordfile='exclusive'

*.sga_target=290455552

*.undo_management='AUTO'

#要修改的参数

*.control_files='/home/orastd/oradata/orcl/stdcrl.ctl'

*.log_archive_config='DG_CONFIG=(orcl,orcl_single)'

*.standby_archive_dest='/home/orastd/arch'

*.log_archive_dest_1='location=/home/orastd/arch'

*.log_archive_dest_2='service=orcl1 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'

*.db_file_name_convert='/dev/','/home/orastd/oradata/orcl/','+DG3/orcl/datafile/','/home/orastd/oradata/orcl/'

*.log_file_name_convert='/dev/','/home/orastd/oradata/orcl/'

*.standby_file_management='AUTO'

fal_server='orcl1','orcl2'

fal_client='orcl_single'

thread=1

undo_tablespace='UNDOTBS1'

*.core_dump_dest='/home/orastd/admin/orcl/cdump'

*.audit_file_dest='/home/orastd/admin/orcl/adump'

*.background_dump_dest='/home/orastd/admin/orcl/bdump'

*.user_dump_dest='/home/orastd/admin/orcl/udump'

##要添加的参数

db_unique_name='orcl_single'

service_name='orcl_single'

##要删除的参数，下面这些参数是RAC上特有的，可以删除。

*.cluster_database_instances=2

*.cluster_database=TRUE

orcl2.instance_number=2

orcl1.instance_number=1

*.remote_listener='LISTENERS_ORCL'

*.db_recovery_file_dest='+DG1'

(3) 在RAC上进行备份

inode2:oracle:orcl2:/db/oracle> rman target /

inode2:oracle:orcl2:/db/oracle/product/10.2.0/db_1/dbs> rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Feb 23 15:26:01 2011

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

connected to target database: ORCL (DBID=1268210488)

RMAN> backup database format '/db/dbback/%U';

....

(4) 创建standby的控制文件

在RAC两实例上进行几次归档

SQL>alter system switch logfile;

SQL>alter database create standby controlfile as '/db/dbback/stdcrl.ctl';

(5) 把所以备份拷贝到standby服务器的相同目录下

因为standby库和rac2在相同的服务器inode2上，所以这步可以省略。

只需要把stdcrl.ctl拷贝到指定的目录，并赋权限：

inode2:root::/db/dbback> ls

4bm5ajul_1_1 4cm5ajul_1_1 stdcrl.ctl

inode2:root::/db/dbback> chown orastd:dba /db/dbback/*

inode2:root::/db/dbback> ls -l

total 2057968

-rw-r----- 1 orastd dba 487129088 Feb 22 15:55 4bm5ajul_1_1

-rw-r----- 1 orastd dba 554999808 Feb 22 15:55 4cm5ajul_1_1

-rw-r----- 1 orastd dba 11550720 Feb 22 16:02 stdcrl.ctl

inode2:root::/db/dbback>cp stdcrl.ctl /home/orastd/oradata/orcl/

(6) 启动standby到nomount状态

创建密码文件：

inode2:orastd:orcl:/home/orastd/>orapwd password=oracle file=orapworcl entries=30

启动数据库，创建spfile文件：

分别用下面两种方式把实例启动到nmount状态:

inode2:orastd:orcl:/home/orastd/product/10.2.0/db_1/network/admin> sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Feb 23 10:03:40 2011

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

Connected to an idle instance.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 293601280 bytes

Fixed Size 2020392 bytes

Variable Size 138415064 bytes

Database Buffers 150994944 bytes

Redo Buffers 2170880 bytes

SQL> exit

inode2:orastd:orcl:/home/orastd/product/10.2.0/db_1/network/admin> sqlplus sys/oracle@ORCL_SINGLE as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Feb 23 10:04:12 2011

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

Connected to an idle instance.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 293601280 bytes

Fixed Size 2020392 bytes

Variable Size 138415064 bytes

Database Buffers 150994944 bytes

Redo Buffers 2170880 bytes

SQL> create spfile from pfile;

file created.

(7) 用rman创建standby数据库

在RAC orcl2实例上做还原恢复操作：

inode2:oracle:orcl2:/db/oracle/product/10.2.0/db_1/network/admin> rman target / auxiliary sys/oracle@ORCL_SINGLE

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Feb 23 10:19:52 2011

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

connected to target database: ORCL (DBID=1268210488)

connected to auxiliary database: ORCL (not mounted)

RMAN> duplicate target database for standby;

Starting Duplicate Db at 23-FEB-11

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: sid=156 devtype=DISK

allocated channel: ORA_AUX_DISK_2

channel ORA_AUX_DISK_2: sid=155 devtype=DISK

contents of Memory Script:

{

restore clone standby controlfile;

sql clone 'alter database mount standby database';

}

executing Memory Script

Starting restore at 23-FEB-1

.............

datafile 5 switched to datafile copy

input datafile copy recid=29 stamp=743855043 filename=/home/orastd/oradata/orcl/rundotbs2_raw

datafile 6 switched to datafile copy

input datafile copy recid=30 stamp=743855044 filename=/home/orastd/oradata/orcl/lcz.256.743266487

datafile 7 switched to datafile copy

input datafile copy recid=31 stamp=743855044 filename=/home/orastd/oradata/orcl/lcz.257.743186313

datafile 8 switched to datafile copy

input datafile copy recid=32 stamp=743855044 filename=/home/orastd/oradata/orcl/ts.258.743273077

Finished Duplicate Db at 23-FEB-11

RMAN> exit

Recovery Manager complete

(8)检查standby数据库状态

--用sqlplus "/as sysdba"方式不能mount实例

inode2:orastd:orcl:/home/orastd/oradata/orcl> sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Feb 23 10:26:53 2011

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

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

SQL> select status from v$instance;

STATUS

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

STARTED

SQL> select open_mode from v$database;

select open_mode from v$database

*

ERROR at line 1:

ORA-01507: database not mounted

SQL> alter database mount;

alter database mount

*

ERROR at line 1:

ORA-01102: cannot mount database in EXCLUSIVE mode

SQL> exit

--用sqlplus sys/oracle@ORCL_SINGLE方式查看实例已经启动到了mount状态

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

inode2:orastd:orcl:/home/orastd/oradata/orcl> sqlplus sys/oracle@ORCL_SINGLE as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Feb 23 10:30:24 2011

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

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

SQL> select open_mode from v$database;

OPEN_MODE

----------

MOUNTED

SQL> select member from v$logfile;

MEMBER

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

/home/orastd/oradata/orcl/rredo2_1_raw

/home/orastd/oradata/orcl/rredo2_2_raw

/home/orastd/oradata/orcl/rredo1_1_raw

/home/orastd/oradata/orcl/rredo1_2_raw

/home/orastd/oradata/orcl/rredo3_1_raw

/home/orastd/oradata/orcl/rredo3_2_raw

/home/orastd/oradata/orcl/rredo4_1_raw

/home/orastd/oradata/orcl/rredo4_2_raw

8 rows selected.

SQL> select name from v$datafile;

NAME

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

/home/orastd/oradata/orcl/rsystem_raw

/home/orastd/oradata/orcl/rundotbs_raw

/home/orastd/oradata/orcl/rsysaux_raw

/home/orastd/oradata/orcl/ruserlv_raw

/home/orastd/oradata/orcl/rundotbs2_raw

/home/orastd/oradata/orcl/lcz.256.743266487

/home/orastd/oradata/orcl/lcz.257.743186313

/home/orastd/oradata/orcl/ts.258.743273077

8 rows selected.

SQL> select name from v$tempfile;

NAME

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

/home/orastd/oradata/orcl/rtemp_raw

(9) 创建standby redo log日志

RAC第个redo thread都需要创建对应的standby redo log.创建原则和单实例一样：

包括日志文件大小相等，日志组数量要多1组。

查看RAC中联机日志：

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

THREAD# GROUP# BYTES/1024/1024

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

1 1 50

1 2 50

2 3 50

2 4 50

RAC有两个redo thread,每个thread有两个日志组，第个日志组文件大小为50M，所以要针对每个需要3组standby redo,大小为50M

SQL> alter database add standby logfile thread 1 group 5 ('//home/orastd/oradata/orcl/st_1_5.rdo') size 50m;

Database altered.

SQL> alter database add standby logfile thread 1 group 6 ('//home/orastd/oradata/orcl/st_1_6.rdo') size 50m;

Database altered.

SQL> alter database add standby logfile thread 1 group 7 ('//home/orastd/oradata/orcl/st_1_7.rdo') size 50m;

Database altered.

SQL> alter database add standby logfile thread 2 group 8 ('//home/orastd/oradata/orcl/st_2_8.rdo') size 50m;

Database altered.

SQL> alter database add standby logfile thread 2 group 9 ('//home/orastd/oradata/orcl/st_2_9.rdo') size 50m;

Database altered.

SQL> alter database add standby logfile thread 2 group 10 ('/home/orastd/oradata/orcl/st_2_10.rdo') size 50m;

Database altered.

(10) 开始同步

启动MRP：

SQL> recover managed standby database disconnect from session;

Media recovery complete.

在RAC各实例上查看日志同步情况：

SQL> col dest_name for a30

SQL> col error for a39

SQL> Select dest_name,status,error From v$archive_dest;

DEST_NAME STATUS ERROR

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

LOG_ARCHIVE_DEST_1 VALID

LOG_ARCHIVE_DEST_2 BAD PARAM ORA-16052: DB_UNIQUE_NAME attribute is

required

LOG_ARCHIVE_DEST_3 VALID

LOG_ARCHIVE_DEST_4 INACTIVE

LOG_ARCHIVE_DEST_5 INACTIVE

LOG_ARCHIVE_DEST_6 INACTIVE

LOG_ARCHIVE_DEST_7 INACTIVE

LOG_ARCHIVE_DEST_8 INACTIVE

LOG_ARCHIVE_DEST_9 INACTIVE

LOG_ARCHIVE_DEST_10 INACTIVE

10 rows selected

注意：这里LOG_ARCHIVE_DEST_2报的错是由于当初配置时，在RAC上面的tnsnames.ora配的orcl_single连接不正确，检查修改后正常。正常状态是VALID或INACTIVE

(11) 确认同步：

在RAC某实例上执行：

SQL> create tablespace test datafile '+DG3' size 10m;

Tablespace created

在每个实例上执行日志切换：

SQL> alter system switch logfile;

System altered.

在standby上查看日志同步情况：

SQL> Select SEQUENCE#,APPLIED,Thread# From v$archived_log Order By Thread#,SEQUENCE#;

SEQUENCE# APP THREAD#

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

125 YES 1

126 YES 1

127 YES 1

128 YES 1

129 YES 1

130 YES 1

131 YES 1

132 YES 1

133 YES 1

127 YES 2

128 YES 2

SEQUENCE# APP THREAD#

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

129 YES 2

130 YES 2

131 YES 2

132 YES 2

133 NO 2

16 rows selected.

SQL> select name from v$datafile;

NAME

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

/home/orastd/oradata/orcl/rsystem_raw

/home/orastd/oradata/orcl/rundotbs_raw

/home/orastd/oradata/orcl/rsysaux_raw

/home/orastd/oradata/orcl/ruserlv_raw

/home/orastd/oradata/orcl/rundotbs2_raw

/home/orastd/oradata/orcl/lcz.256.743266487

/home/orastd/oradata/orcl/lcz.257.743186313

/home/orastd/oradata/orcl/ts.258.743273077

/home/orastd/oradata/orcl/test.260.743858749

9 rows selected.

可以看到表空间已经同步到standby

--下面的步骤没有做测试

(12) 切换 (switchover)

在orcl1上执行：

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

在orcl2上执行：

SQL> alter database commit to switchover to physical standby with session shutdown;

在standby上执行：

SQL> alter database commit to switchover to primary with session shutdown;

SQL> shutdown immediate

在orcl2上创建standby redolog file:

SQL>startup mount

SQL>alter database add standby logfile thread 1 group 5 size 50m;

SQL>alter database add standby logfile thread 1 group 6 size 50m;

SQL>alter database add standby logfile thread 1 group 7 size 50m;

SQL>alter database add standby logfile thread 2 group 8 size 50m;

SQL>alter database add standby logfile thread 2 group 9 size 50m;

SQL>alter database add standby logfile thread 2 group 10 size 50m;

开始同步，在orcl2上执行：

SQL>recover managed standby database disconnect from session;

确认同步：

SQL>create tablespace test2 datafile '/home/orastd/oradata/orcl/test2.dbf' size 10m;

SQL>alter system switch logfile;

在orcl2确认同步：

SQ>select name from v$datafile;

(13)下面是failorer切换语句

如果primary无法启动或是想直接把standby库切成主库

物理standby的failover注意几点：

1) failover 之后，原primary 数据库默认不再是data guard配置的一部分。

2) 多数情况下，其它逻辑/物理standby数据库不直接参与failover 的过程，因此这些数据库不需要做任何

操作。

3) 某些情况下，新的primary数据库配置之后，需要重新创建其它所有的standby 数据库。

另外，如果待转换角色的standby 处于maximum protection或maximum availability 模式的话，归档日志应

该是连续存在的，这种情况下你可以直接从第3 步执行，否则建议你按照操作步骤从第1步开始执行。

一般情况下failover 都是表示primary数据库瘫痪，最起码也是起不来了，因此这种类型的切换基本上不需

要primary 数据库做什么操作。所以下列步骤中如果有提到primary和standby 执行的，只是建议你如果primary

还可以用，那就执行一下，即使它能用你却不执行，也没关系，不影响standby数据库的切换:)

1、检查归档文件是否连续

查询待转换standby 数据库的V$ARCHIVE_GAP视图，确认归档文件是否连接：

SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

未选定行

如果返回的有记录，按照列出的记录号复制对应的归档文件到待转换的standby服务器。这一步非常重

要，必须确保所有已生成的归档文件均已存在于standby 服务器，不然可能会数据不一致造成转换时报错。

文件复制之后，通过下列命令将其加入数据字典：

SQL> ALTER DATABASE REGISTER PHYSICAL LOGFILE 'filespec1';

2、检查归档文件是否完整

分别在primary/standby 执行下列语句：

SQL> select distinct thread#,max(sequence#) over(partition by thread#) a from v$archived_log;

该语句取得当前数据库各线程已归档文件最大序号，如果primary 与standby 最大序号不相同，必须将

多出的序号对应的归档文件复制到待转换的standby 服务器。不过既然是failover，有可能primary数据库此

时已经无法打开，甚至无法访问.

3、启动failover

执行下列语句：

SQL> alter database recover managed standby database finish force;

数据库已更改。

FORCE 关键字将会停止当前活动的RFS进程，以便立刻执行failover。

剩下的步骤就与前面switchover 很相似了

4、切换物理standby角色为primary

SQL> alter database commit to switchover to primary;

数据库已更改。

5、启动新的primary数据库。

如果当前数据库已mount,直接open即可，如果处于read-only 模式，需要首先shutdown immediate，然

后再直接startup。

SQL> alter database open;

数据库已更改。