oracle standby rac,同一环境下新建Standby RAC库

需求：在同一个环境下新建Standby RAC库，即和Primary RAC在相同的磁盘组。

说明：生产环境一般不建议这样配置DG，因为存储层面是相同磁盘组，灾备的实际意义不大。我这里是用作读写分离。

基本信息：

db_name: jyzhao

Primary RAC db_unique_name：jyzhao

Standby RAC db_unique_name：jyzhaodg

Standby RAC instance_name: jyzhaodg1, jyzhaodg2

版本：GI 11.2.0.4 + DB 11.2.0.4

第一章 准备工作

1.1 ASM存储

1.2 配置tnsnames.ora

1.3 密码文件

第二章 源数据库备份

第三章 参数文件

3.1 修改主库参数文件

3.2 修改Standby RAC 参数

3.3 在ASM中创建standby的spfile

3.4 Standby RAC启动到nomount

第五章 rman恢复数据库

第六章 备库开启日志应用

第七章 创建standby log

第八章 检查资源状态

第一章 准备工作

1.1 ASM存储

确认RAC Standby存储是在和RAC Primary相同的ASM磁盘组内(PS：灾备效果不明显)，创建RAC Standby存储目录(根据db_unique_name)

mkdir +DATA/JYZHAODG

mkdir +FRA/JYZHAODG

1.2 配置tnsnames.ora

cd $Oracle_HOME/network/admin/

cat tnsnames.ora

添加主库备库的连接信息(所有节点)：

JYZHAO =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = oradb-scan)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = jyzhao)

)

)

jyzhaodg =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = oradb-scan)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = jyzhaodg)

)

)

1.3 密码文件

节点1:

export ORACLE_SID=jyzhaodg1

密码文件；

cd $ORACLE_HOME/dbs

orapwd file=orapwjyzhaodg1 password=oracle entries=5

或者直接copy之前的密码文件，然后mv重命名:

cp orapwjyzhao1 orapwjyzhaodg1

节点2:

export ORACLE_SID=jyzhaodg2

密码文件；

cd $ORACLE_HOME/dbs

orapwd file=orapwjyzhaodg2 password=oracle entries=5

或者直接copy之前的密码文件，然后mv重命名:

cp orapwjyzhao2 orapwjyzhaodg2

最后测试相互连接可用

sqlplus sys/oracle@jyzhao as sysdba

sqlplus sys/oracle@jyzhaodg as sysdba

第二章 源数据库备份

vi backup.sh

备份脚本如下：

rman target / <

run {

allocate channel c1 device type disk;

allocate channel c2 device type disk;

backup as compressed backupset database filesperset 1 format '/u01/orabak/salehrdb_%d_%T_%s.bak';

backup current controlfile format '/u01/orabak/control.bak';

release channel c1;

release channel c2;

}

EOF

后台执行备份任务：

nohup sh backup.sh &

注意：如果使用backup as copy database format方案,就不用再备份到磁盘后再恢复了,可以节省时间。

backup as copy 方案备份脚本 backupcp.sh内容如下：

rman target / <

run {

allocate channel c1 device type disk;

allocate channel c2 device type disk;

backup as copy database format '+DATA/JYZHAODG/DATAFILE/%u.dbf';

release channel c1;

release channel c2;

}

EOF

注意：这种方式，路径包含的目录需手动创建。

mkdir +DATA/JYZHAODG/DATAFILE

第三章 参数文件

3.1 修改主库参数文件

确认当前环境ORACLE_SID：

echo $ORACLE_SID

export ORACLE_SID=jyzhao1

根据spfile文件创建pfile：

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

Primary RAC 添加参数

--为不停止primary RAC，所以尽可能动态修改参数：

show parameter log_archive_config

show parameter db_file_name_convert

show parameter log_file_name_convert

show parameter fal_client

show parameter fal_server

show parameter log_archive_dest_3

alter system set log_archive_config='dg_config=(jyzhao,jyzhaodg)';

alter system set db_file_name_convert='+DATA/jyzhaodg','+DATA/jyzhao' scope=spfile;

alter system set log_file_name_convert='+DATA/jyzhaodg','+DATA/jyzhao' scope=spfile;

alter system set fal_client='jyzhao';

alter system set fal_server='jyzhaodg';

alter system set log_archive_dest_3='service=jyzhaodg valid_for=(online_logfiles,primary_role) db_unique_name=jyzhaodg';

--暂时defer传输链路，防止此时主库告警生成相关错误

SQL> alter system set log_archive_dest_state_3=defer;

3.2 修改Standby RAC 参数

根据主库之前导出的参数文件修改备库的参数文件：

cp /tmp/pfile.ora /tmp/pfile_std.ora

vi /tmp/pfile_std.ora

*._high_priority_processes='LMS*'

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

*.audit_trail='db'

*.cluster_database=true

*.compatible='11.2.0.4.0'

*.control_files='+DATA/jyzhaodg/controlfile/current.260.931878631','+FRA/jyzhaodg/controlfile/current.256.931878631'

*.db_block_size=8192

*.db_create_file_dest='+DATA'

*.db_domain=''

*.db_name='jyzhao'

*.db_recovery_file_dest='+FRA'

*.db_recovery_file_dest_size=4621074432

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

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

jyzhaodg2.instance_number=2

jyzhaodg1.instance_number=1

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

*.memory_target=313286272

*.open_cursors=300

*.processes=150

*.remote_listener='oradb-scan:1521'

*.remote_login_passwordfile='exclusive'

jyzhaodg2.thread=2

jyzhaodg1.thread=1

jyzhaodg2.undo_tablespace='UNDOTBS2'

jyzhaodg1.undo_tablespace='UNDOTBS1'

#add

db_unique_name='jyzhaodg'

log_archive_config='dg_config=(jyzhao,jyzhaodg)'#db_unique_name

db_file_name_convert='+DATA/jyzhao','+DATA/jyzhaodg'

log_file_name_convert='+DATA/jyzhao','+DATA/jyzhaodg'

standby_file_management=auto

fal_client='jyzhaodg'#tnsnames.ora

fal_server='jyzhao'#salehrdb

log_archive_dest_3='service=jyzhao valid_for=(online_logfiles,primary_role) db_unique_name=jyzhao'

主要是注意后面#add之后的内容。

3.3 在ASM中创建standby的spfile

在ASM中创建standby的spfile,并确定各节点的参数文件内容指向磁盘中的spfile。

节点1:

echo $ORACLE_SID

export ORACLE_SID=jyzhaodg1

create spfile='+DATA/jyzhaodg/spfilejyzhaodg.ora' from pfile='/tmp/pfile_std.ora';

--

cat initjyzhaodg1.ora

SPFILE='+DATA/jyzhaodg/spfilejyzhaodg.ora'

节点2：

echo $ORACLE_SID

export ORACLE_SID=jyzhaodg2

cat initjyzhaodg2.ora

SPFILE='+DATA/jyzhaodg/spfilejyzhaodg.ora'

创建adump目录(所有节点)

mkdir -p /u01/app/oracle/admin/jyzhaodg/adump

3.4 Standby RAC启动到nomount

节点1:

echo $ORACLE_SID

export ORACLE_SID=jyzhaodg1

startup nomount

节点2:

echo $ORACLE_SID

export ORACLE_SID=jyzhaodg2

startup nomount

第四章 rman恢复控制文件

在Primary RAC上创建备库使用的控制文件：

echo $ORACLE_SID

export ORACLE_SID=jyzhao1

SQL> alter database create standby controlfile as '/tmp/control01.ctlbak';

在Standby RAC的节点1上恢复控制文件并启动到mount：

echo $ORACLE_SID

export ORACLE_SID=jyzhaodg1

restore controlfile from '/tmp/control01.ctlbak';

alter database mount;

crosscheck backupset;

--如果是之前copy到磁盘组的备份没加载到控制文件中，可以手动catalog

catalog start with '+data/jyzhaodg/DATAFILE';

--同样，如果是之前的备份集没加载到控制文件中，一样手动catalog

catalog start with '/u01/orabak/';

查看此时standby记录的各文件路径是否符合预期：

SQL> select name from v$datafile;

NAME

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

+DATA/jyzhaodg/datafile/system.256.931878537

+DATA/jyzhaodg/datafile/sysaux.257.931878537

+DATA/jyzhaodg/datafile/undotbs1.258.931878537

+DATA/jyzhaodg/datafile/users.259.931878537

+DATA/jyzhaodg/datafile/undotbs2.264.931878827

+DATA/jyzhaodg/datafile/dbs_d_jingyu.268.937515173

6 rows selected.

SQL> select member from v$logfile;

MEMBER

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

+DATA/jyzhaodg/onlinelog/group_2.262.931878637

+FRA/jyzhao/onlinelog/group_2.258.931878639

+DATA/jyzhaodg/onlinelog/group_1.261.931878635

+FRA/jyzhao/onlinelog/group_1.257.931878637

+DATA/jyzhaodg/onlinelog/group_3.265.931879021

+FRA/jyzhao/onlinelog/group_3.259.931879023

+DATA/jyzhaodg/onlinelog/group_4.266.931879027

+FRA/jyzhao/onlinelog/group_4.260.931879029

8 rows selected.

SQL> select name from v$tempfile;

NAME

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

+DATA/jyzhaodg/tempfile/temp.263.931878661

SQL> select name from v$controlfile;

NAME

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

+DATA/jyzhaodg/controlfile/current.288.937645851

+FRA/jyzhaodg/controlfile/current.275.937645851

发现日志文件有不符合预期的路径，进行修正：

SQL> show parameter convert

NAME TYPE VALUE

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

db_file_name_convert string +DATA/jyzhao, +DATA/jyzhaodg

log_file_name_convert string +DATA/jyzhao, +DATA/jyzhaodg

SQL> alter system set log_file_name_convert='+DATA/jyzhao','+DATA/jyzhaodg','+FRA/jyzhao','+FRA/jyzhaodg' scope=spfile;

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 313159680 bytes

Fixed Size 2252824 bytes

Variable Size 222302184 bytes

Database Buffers 83886080 bytes

Redo Buffers 4718592 bytes

Database mounted.

SQL> select member from v$logfile;

MEMBER

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

+DATA/jyzhaodg/onlinelog/group_2.262.931878637

+FRA/jyzhaodg/onlinelog/group_2.258.931878639

+DATA/jyzhaodg/onlinelog/group_1.261.931878635

+FRA/jyzhaodg/onlinelog/group_1.257.931878637

+DATA/jyzhaodg/onlinelog/group_3.265.931879021

+FRA/jyzhaodg/onlinelog/group_3.259.931879023

+DATA/jyzhaodg/onlinelog/group_4.266.931879027

+FRA/jyzhaodg/onlinelog/group_4.260.931879029

8 rows selected.

第五章 rman恢复数据库

Standby RAC节点1:

确定ORACLE_SID变量：

echo $ORACLE_SID

export ORACLE_SID=jyzhaodg1

a. 如果是使用从备份集恢复的方式

vi restore.sh

rman target / < db_restore.log

run {

allocate channel d1 type disk;

allocate channel d2 type disk;

restore database;

release channel d1;

release channel d2;

}

exit;

EOF!

nohup sh restore.sh &

b. 如果是直接使用copy到磁盘组的

直接switch database to copy即可。

RMAN> switch database to copy;

using target database control file instead of recovery catalog

datafile 1 switched to datafile copy "+DATA/jyzhaodg/datafile/0eru6m4s.dbf"

datafile 2 switched to datafile copy "+DATA/jyzhaodg/datafile/0fru6m4s.dbf"

datafile 3 switched to datafile copy "+DATA/jyzhaodg/datafile/0hru6meo.dbf"

datafile 4 switched to datafile copy "+DATA/jyzhaodg/datafile/0iru6mg5.dbf"

datafile 5 switched to datafile copy "+DATA/jyzhaodg/datafile/0jru6mg6.dbf"

datafile 6 switched to datafile copy "+DATA/jyzhaodg/datafile/0gru6men.dbf"

第六章 备库开启日志应用

确认Primary RAC的日志传输链路打开：

echo $ORACLE_SID

export ORACLE_SID=jyzhao1

SQL> alter system set log_archive_dest_state_3=enable;

Standby RAC节点1在mount状态下开启日志应用：

echo $ORACLE_SID

export ORACLE_SID=jyzhaodg1

SQL> alter database recover managed standby database disconnect from session;

第七章 创建standby log

停止备库应用：

SQL> alter database recover managed standby database cancel;

查看日志信息：

SQL> select * from v$Log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME

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

1 1 69 52428800 512 2 YES CURRENT 2450934 03-MAR-17 2.8147E+14

2 1 0 52428800 512 2 YES UNUSED 2440706 03-MAR-17 2450934 03-MAR-17

3 2 0 52428800 512 2 YES UNUSED 2440817 03-MAR-17 2450939 03-MAR-17

4 2 36 52428800 512 2 YES CURRENT 2450939 03-MAR-17 2.8147E+14

SQL> col member for a70

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

GROUP# TYPE MEMBER

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

2 ONLINE +DATA/jyzhaodg/onlinelog/group_2.298.937648563

2 ONLINE +FRA/jyzhaodg/onlinelog/group_2.278.937648565

1 ONLINE +DATA/jyzhaodg/onlinelog/group_1.297.937648559

1 ONLINE +FRA/jyzhaodg/onlinelog/group_1.279.937648561

3 ONLINE +DATA/jyzhaodg/onlinelog/group_3.299.937648567

3 ONLINE +FRA/jyzhaodg/onlinelog/group_3.389.937648569

4 ONLINE +DATA/jyzhaodg/onlinelog/group_4.300.937648573

4 ONLINE +FRA/jyzhaodg/onlinelog/group_4.390.937648573

8 rows selected.

根据检查结果，合理为数据库添加standby logfile：

alter database add standby logfile thread 1 group 11 ('+DATA','+FRA') size 52428800;

alter database add standby logfile thread 1 group 12 ('+DATA','+FRA') size 52428800;

alter database add standby logfile thread 1 group 13 ('+DATA','+FRA') size 52428800;

alter database add standby logfile thread 2 group 21 ('+DATA','+FRA') size 52428800;

alter database add standby logfile thread 2 group 22 ('+DATA','+FRA') size 52428800;

alter database add standby logfile thread 2 group 23 ('+DATA','+FRA') size 52428800;

添加完再次查看：

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

GROUP# TYPE MEMBER

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

2 ONLINE +DATA/jyzhaodg/onlinelog/group_2.298.937648563

2 ONLINE +FRA/jyzhaodg/onlinelog/group_2.278.937648565

1 ONLINE +DATA/jyzhaodg/onlinelog/group_1.297.937648559

1 ONLINE +FRA/jyzhaodg/onlinelog/group_1.279.937648561

3 ONLINE +DATA/jyzhaodg/onlinelog/group_3.299.937648567

3 ONLINE +FRA/jyzhaodg/onlinelog/group_3.389.937648569

4 ONLINE +DATA/jyzhaodg/onlinelog/group_4.300.937648573

4 ONLINE +FRA/jyzhaodg/onlinelog/group_4.390.937648573

11 STANDBY +DATA/jyzhaodg/onlinelog/group_11.301.937648773

11 STANDBY +FRA/jyzhaodg/onlinelog/group_11.391.937648775

12 STANDBY +DATA/jyzhaodg/onlinelog/group_12.302.937648777

GROUP# TYPE MEMBER

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

12 STANDBY +FRA/jyzhaodg/onlinelog/group_12.392.937648779

13 STANDBY +DATA/jyzhaodg/onlinelog/group_13.303.937648779

13 STANDBY +FRA/jyzhaodg/onlinelog/group_13.393.937648781

21 STANDBY +DATA/jyzhaodg/onlinelog/group_21.304.937648783

21 STANDBY +FRA/jyzhaodg/onlinelog/group_21.394.937648783

22 STANDBY +DATA/jyzhaodg/onlinelog/group_22.305.937648785

22 STANDBY +FRA/jyzhaodg/onlinelog/group_22.395.937648787

23 STANDBY +DATA/jyzhaodg/onlinelog/group_23.306.937648787

23 STANDBY +FRA/jyzhaodg/onlinelog/group_23.396.937648789

20 rows selected.

继续开启备库应用，确定恢复完成日志没报错信息后取消日志应用，打开数据库，开启ADG：

alter database recover managed standby database disconnect from session;

alter database recover managed standby database cancel;

alter database open;

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

查看DG同步状态：

SQL> SELECT OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER, GUARD_STATUS FROM V$DATABASE;

OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS FOR DATAGUAR GUARD_S

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

READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED NO DISABLED NONE

SQL> set lines 1000

SQL> select * from v$dataguard_stats;

NAME VALUE UNIT TIME_COMPUTED DATUM_TIME

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

transport lag +00 00:09:37 day(2) to second(0) interval 03/03/2017 10:03:20 03/03/2017 10:03:13

apply lag +00 00:09:38 day(2) to second(0) interval 03/03/2017 10:03:20 03/03/2017 10:03:13

apply finish time day(2) to second(3) interval 03/03/2017 10:03:20

estimated startup time 40 second 03/03/2017 10:03:20

--可以在Primary RAC上归档当前日志模拟业务切换归档：

SQL> SELECT OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER, GUARD_STATUS FROM V$DATABASE;

OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS FOR DATAGUAR GUARD_S

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

READ WRITE PRIMARY TO STANDBY NO DISABLED NONE

SQL> alter system archive log current;

System altered.

--再次在Standby RAC上查看DG同步状态：

SQL> r

1* select * from v$dataguard_stats

NAME VALUE UNIT TIME_COMPUTED DATUM_TIME

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

transport lag +00 00:00:00 day(2) to second(0) interval 03/03/2017 10:04:45 03/03/2017 10:04:44

apply lag +00 00:00:00 day(2) to second(0) interval 03/03/2017 10:04:45 03/03/2017 10:04:44

apply finish time day(2) to second(3) interval 03/03/2017 10:04:45

estimated startup time 40 second 03/03/2017 10:04:45

至此，已完成RAC Standby库在同环境下的创建。

第八章 检查资源状态

我们可以将RAC Standby也加入到crs资源中：

[oracle@oradb23 ~]$ srvctl add database -d jyzhaodg -o $ORACLE_HOME -i jyzhaodg1 -n jyzhao

[oracle@oradb23 ~]$ srvctl add instance -d jyzhaodg -i jyzhaodg1 -n oradb23

[oracle@oradb23 ~]$ srvctl add instance -d jyzhaodg -i jyzhaodg2 -n oradb24

--启动数据库

[oracle@oradb23 ~]$ srvctl start database -d salehrdg

--查看资源状态：

[grid@oradb23 ~]$ crsctl stat res -t

总结：同环境下搭建Standby RAC，最重要的注意事项就是一定要细心，操作前确保自己操作的是正确的ORACLE_SID，备库的路径也要反复确认无误再操作。