配置RAC环境的standby

来源：互联网发布：sql 求和编辑：程序博客网时间：2024/05/21 08:02

一、初始化环境:

准备已经安装完毕可以正常运行的RAC数据库，以及一台安装完ORACLE软件未创建数据库的。

我的配置如下：

Hostname:IP

RAC1:172.17.61.131

RAC2:172.17.61.132

rhel133:172.17.61.133

主库rac的情况：

[oracle@rac1~]$ cat /etc/hosts

# Do not removethe following line, or various programs

# that requirenetwork functionality will fail.

127.0.0.1 localhost

172.17.61.131 rac1.mycorpdomain.com rac1

172.17.61.231 rac1-vip.mycorpdomain.com rac1-vip

10.10.10.31 rac1-priv.mycorpdomain.com rac1-priv

172.17.61.132 rac2.mycorpdomain.com rac2

172.17.61.232 rac2-vip.mycorpdomain.com rac2-vip

10.10.10.32 rac2-priv.mycorpdomain.com rac2-priv

172.17.61.133 racdg

[oracle@rac1~]$ srvctl config database -d devdb -a

rac1 devdb1/u01/app/oracle/product/10.2.0/db_1

rac2 devdb2/u01/app/oracle/product/10.2.0/db_1

DB_NAME:devdb

ORACLE_HOME:/u01/app/oracle/product/10.2.0/db_1

SPFILE:+DG1/devdb/spfiledevdb.ora

DOMAIN: null

DB_ROLE: null

START_OPTIONS:null

POLICY: AUTOMATIC

ENABLE FLAG: DBENABLED

[oracle@rac1~]$ crs_stat -t

Name Type Target State Host

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

ora.devdb.db application ONLINE ONLINE rac2

ora....b1.instapplication ONLINE ONLINE rac1

ora....b2.instapplication ONLINE ONLINE rac2

ora....SM1.asmapplication ONLINE ONLINE rac1

ora....C1.lsnrapplication ONLINE ONLINE rac1

ora.rac1.gsd application ONLINE ONLINE rac1

ora.rac1.ons application ONLINE ONLINE rac1

ora.rac1.vip application ONLINE ONLINE rac1

ora....SM2.asmapplication ONLINE ONLINE rac2

ora....C2.lsnrapplication ONLINE ONLINE rac2

ora.rac2.gsd application ONLINE ONLINE rac2

ora.rac2.ons application ONLINE ONLINE rac2

ora.rac2.vip application ONLINE ONLINE rac2

备库的情况：

[oracle@rhel133~]$ cat /etc/hosts

# Do not removethe following line, or various programs

# that requirenetwork functionality will fail.

127.0.0.1 localhost.localdomain localhost

172.17.61.131 rac1

172.17.61.132 rac2

172.17.61.133 rhel133

[oracle@rhel133~]$ more .bash_profile

# .bash_profile

# Get thealiases and functions

if [ -f~/.bashrc ]; then

. ~/.bashrc

export PATH

unset USERNAME

ORACLE_BASE=/u01/app/oracle

ORACLE_HOME=$ORACLE_BASE/product/10201

ORACLE_SID=racdg

PATH=$HOME/bin:$ORACLE_HOME/bin:$PATH:$ORACLE_HOME/OPatch

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib

export ORACLE_BASEORACLE_HOME ORACLE_SID PATH LD_LIBRARY_PATH

unset USERNAME

umask 022

查看RAC中各文件的位置：

SQL> archivelog list;

Database logmode Archive Mode

Automaticarchival Enabled

Archivedestination +DG1/devdb/

Oldest online logsequence 49

Next logsequence to archive 50

Current log sequence 50

SQL> selectdb_unique_name,name from v$database;

DB_UNIQUE_NAME NAME

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

devdb DEVDB

SQL> selectname from v$datafile;

NAME

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

+DG1/devdb/datafile/system.256.788541117

+DG1/devdb/datafile/undotbs1.258.788541119

+DG1/devdb/datafile/sysaux.257.788541119

+DG1/devdb/datafile/users.259.788541119

+DG1/devdb/datafile/example.264.788541209

+DG1/devdb/datafile/undotbs2.265.788541339

+DG1/devdb/datafile/test_d.270.788794255

7 rowsselected.

SQL> selectname,block_size*file_size_blks/1024/1024 as bytes_m from v$controlfile;

NAME BYTES_M

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

+DG1/devdb/controlfile/current.260.788541179 14.5625

+RECOVERYDEST/devdb/controlfile/current.256.788541179 14.5625

SQL> selectgroup#,member from v$logfile;

GROUP# MEMBER

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

2+DG1/devdb/onlinelog/group_2.262.788541187

2 +RECOVERYDEST/devdb/onlinelog/group_2.258.788541189

1+DG1/devdb/onlinelog/group_1.261.788541183

1+RECOVERYDEST/devdb/onlinelog/group_1.257.788541185

3+DG1/devdb/onlinelog/group_3.266.788541489

3+RECOVERYDEST/devdb/onlinelog/group_3.259.788541493

4+DG1/devdb/onlinelog/group_4.267.788541495

4+RECOVERYDEST/devdb/onlinelog/group_4.260.788541497

8 rows selected.

SQL> selectname from v$tempfile;

NAME

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

+DG1/devdb/tempfile/temp.263.788541201

SQL> showparameter spfile;

NAME TYPE VALUE

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

spfile string +DG1/devdb/spfiledevdb.ora

二、修改rac中的初始化参数：

SQL> alter system set standby_file_management=autoscope=spfile;

System altered.

SQL> altersystem set log_archive_config="DG_CONFIG=(devdb,racdg)" scope=spfile;

System altered.

SQL> altersystem set log_archive_dest_2="SERVICE=racdg LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)DB_UNIQUE_NAME=racdg" scope=spfile;

System altered.

SQL> altersystem set fal_server=racdg scope=spfile;

System altered.

SQL>alter system set fal_client=devdb scope=spfile;

Systemaltered.

SQL> altersystem set db_file_name_convert="+DG1/devdb/datafile/","/u01/oradata/racdg/"scope=spfile;

System altered.

SQL> altersystem setlog_file_name_convert="+DG1/devdb/onlinelog/","/u01/oradata/racdg/"scope=spfile;

System altered.

SQL> create pfile='/home/oracle/initracdg.ora'from spfile;

File created.

三、配置双方的listener及tnsnames:

RAC端的listener不用修改，用之前的就行，需要在rac1和rac2中同时修改：

[oracle@rac1~]$ more /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora

#listener.ora.rac1 Network Configuration File:/u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora.rac1

# Generatedby Oracle configuration tools.

LISTENER_RAC1=

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY =EXTPROC1))

(ADDRESS = (PROTOCOL = TCP)(HOST =rac1-vip)(PORT = 1521)(IP = FIRST))

(ADDRESS = (PROTOCOL = TCP)(HOST =172.17.61.131)(PORT = 1521)(IP = FIRST))

)

SID_LIST_LISTENER_RAC1=

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)

(PROGRAM = extproc)

)

在tnsnames中增加racdg的部分：

[oracle@rac1~]$ more /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora

# tnsnames.oraNetwork Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora

# Generated byOracle configuration tools.

racdg=

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = racdg)

)

DEVDB1 =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = devdb)

(INSTANCE_NAME = devdb1)

)

备库中的listener及tnsnames:

[oracle@rhel133admin]$ more listener.ora

# listener.oraNetwork Configuration File: /u01/app/oracle/product/10201/network/admin/list

ener.ora

# Generated byOracle configuration tools.

SID_LIST_LISTENER=

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME =/u01/app/oracle/product/10201)

(PROGRAM = extproc)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = IPC)(KEY =EXTPROC1))

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

)

[oracle@rhel133admin]$ more tnsnames.ora

# tnsnames.oraNetwork Configuration File: /u01/app/oracle/product/10201/network/admin/tnsn

ames.ora

# Generated byOracle configuration tools.

DEVDB =

(DESCRIPTION =

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

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

(LOAD_BALANCE = yes)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = devdb)

(FAILOVER_MODE =

(TYPE = SELECT)

(METHOD = BASIC)

(TETRIES = 180)

(DELAY = 5)

)

racdg =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = racdg)

)

最后需要测试一样三方是否互通。

四、准备备库的密码文件及参数文件

将密码文件和参数文件copy到备库:

[oracle@rac1dbs]$ scp orapwdevdb1racdg:/u01/app/oracle/product/10201/dbs

oracle@racdg'spassword:

orapwdevdb1 100% 1536 1.5KB/s 00:00

[oracle@rac1 dbs]$ scp /home/oracle/initracdg.oraracdg:/u01/app/oracle/product/10201/dbs

oracle@racdg'spassword:

initracdg.ora 100% 1893 1.9K

在备库上修改密码文件名，以及修改参数文件的相关设置：

[oracle@rhel133dbs]$ ls

initracdg.ora orapwdevdb1

[oracle@rhel133dbs]$ mv orapwdevdb1 orapwracdg

[oracle@rhel133dbs]$ ls

initracdg.ora orapwracdg

建立所需要的目录：

[oracle@rhel133admin]$ pwd

/u01/app/oracle/admin

[oracle@rhel133admin]$ mkdir -p racdg/adump

[oracle@rhel133admin]$ mkdir -p racdg/bdump

[oracle@rhel133admin]$ mkdir -p racdg/cdump

[oracle@rhel133admin]$ mkdir -p racdg/dpdump

[oracle@rhel133admin]$ mkdir -p racdg/udump

[oracle@rhel133dbs]$ mkdir -p /u01/oradata/racdg/flash_recovery_area

[oracle@rhel133 dbs]$mkdir -p /u01/oradata/racdg/archivelog

修改后的参数文件如下：

[oracle@rhel133dbs]$ more initracdg.ora

db_cache_size=176160768

java_pool_size=4194304

large_pool_size=4194304

shared_pool_size=92274688

streams_pool_size=0

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

*.background_dump_dest='/u01/app/oracle/admin/racdg/bdump'

*.compatible='10.2.0.1.0'

*.control_files='/u01/oradata/racdg/control01.ctl','/u01/oradata/racdg/control02.ctl','/u01/oradata/racdg/control03.ctl'

*.core_dump_dest='/u01/app/oracle/admin/racdg/cdum'

*.db_block_size=8192

*.db_create_file_dest='/u01/oradata/racdg'

*.db_file_multiblock_read_count=16

*.db_file_name_convert='+DG1/devdb/datafile/','/u01/oradata/racdg/','+DG1/devdb/tempfile/','/u01/oradata/oracdg/'

*.db_name='devdb' --这里dbname必须和主库一致

*.db_recovery_file_dest='/u01/oradata/racdg/flash_recovery_area/'

*.db_recovery_file_dest_size=1572864000

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

*.fal_client='DEVDB'--为了以后的switchover设置的，也可暂不设置

*.fal_server='RACDG'

*.job_queue_processes=10

*.log_archive_config='DG_CONFIG=(devdb,racdg)'

*.log_archive_dest_1='LOCATION=/u01/oradata/racdg/archivelog/'

*.log_archive_dest_2='SERVICE=devdbLGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=devdb' --为了以后的switchover设置的，也可暂不设置

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

*.log_file_name_convert='+DG1/devdb/onlinelog/','/u01/oradata/racdg/','+RECOVERYDEST/devdb/onlinelog/','/u01/oradata/racdg/'

*.open_cursors=300

*.pga_aggregate_target=92274688

*.processes=150

#*.remote_listener='LISTENERS_DEVDB'

*.remote_login_passwordfile='exclusive'

*.sga_target=307872640

*.standby_file_management='AUTO'

*.undo_management='AUTO'

*.user_dump_dest='/u01/app/oracle/admin/racdg/udump'

*.db_unique_name='racdg'

五、备份控制文件到备库：

SQL> altersystem archive log current;

Systemaltered.

SQL>alter database create standby controlfile as '/home/oracle/control01.ctl';

Databasealtered.

[oracle@rac1 dbs]$ scp /home/oracle/control01.ctl racdg:/home/oracle

(这里的路径racdg:/home/oracle不要更改，避免rman恢复时找不到控制文件)

六、用RMAN备份主库

RMAN> run {

2>crosscheck archivelog all;

3> deleteexpired archivelog all;

4>crosscheck backup;

5> deleteexpired backup;

6> backupdatabase format '/u01/oradata/racfull_%u_%s_%p.bak';

7> backupfilesperset 1 format '/u01/oradata/arch_%u_%s_%p.bak' archivelog all deleteinput;

8> }

备份结束后将产生的.bak文件都复制到备库：

[oracle@rac1oradata]$ scp *.bak racdg:/u01/oradata

(这里的路径racdg:/u01/oradata也不要更改，避免rman恢复时找不到备份的文件)

七、恢复备库：

先用之前修改后的pfile开启到nomount状态：

SQL> startup nomount

[oracle@rhel133dbs]$ rman target sys/system@devdb auxiliary sys/system

RecoveryManager: Release 10.2.0.1.0 - Production on Tue Aug 13 15:36:39 2013

connected totarget database: DEVDB (DBID=693271163)

connected toauxiliary database: DEVDB (not mounted)

RMAN>

在备库中用duplicate的方式来恢复备库：

RMAN>duplicate target database for standby;

StartingDuplicate Db at 13-AUG-13

using targetdatabase control file instead of recovery catalog

allocatedchannel: ORA_AUX_DISK_1

channelORA_AUX_DISK_1: sid=156 devtype=DISK

contents ofMemory Script:

{

restore clone standby controlfile;

sql clone 'alter database mount standbydatabase';

}

executingMemory Script

Startingrestore at 13-AUG-13

using channelORA_AUX_DISK_1

channelORA_AUX_DISK_1: restoring control file

channelORA_AUX_DISK_1: copied control file copy

inputfilename=/home/oracle/control01.ctl

outputfilename=/u01/oradata/racdg/control01.ctl

outputfilename=/u01/oradata/racdg/control02.ctl

outputfilename=/u01/oradata/racdg/control03.ctl

Finishedrestore at 13-AUG-13

sql statement:alter database mount standby database

releasedchannel: ORA_AUX_DISK_1

contents ofMemory Script:

{

set newname for tempfile 1 to

"/u01/oradata/oracdg/temp.263.788541201";

switch clone tempfile all;

set newname for datafile 1 to

"/u01/oradata/racdg/system.256.788541117";

set newname for datafile 2 to

"/u01/oradata/racdg/undotbs1.258.788541119";

set newname for datafile 3 to

"/u01/oradata/racdg/sysaux.257.788541119";

set newname for datafile 4 to

"/u01/oradata/racdg/users.259.788541119";

set newname for datafile 5 to

"/u01/oradata/racdg/example.264.788541209";

set newname for datafile 6 to

"/u01/oradata/racdg/undotbs2.265.788541339";

set newname for datafile 7 to

"/u01/oradata/racdg/test_d.270.788794255";

restore

check readonly

clone database

;

}

executingMemory Script

executingcommand: SET NEWNAME

renamedtemporary file 1 to /u01/oradata/oracdg/temp.263.788541201 in control file

executingcommand: SET NEWNAME

Startingrestore at 13-AUG-13

allocatedchannel: ORA_AUX_DISK_1

channelORA_AUX_DISK_1: sid=156 devtype=DISK

channelORA_AUX_DISK_1: starting datafile backupset restore

channelORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

restoringdatafile 00001 to /u01/oradata/racdg/system.256.788541117

restoringdatafile 00002 to /u01/oradata/racdg/undotbs1.258.788541119

restoringdatafile 00003 to /u01/oradata/racdg/sysaux.257.788541119

restoringdatafile 00004 to /u01/oradata/racdg/users.259.788541119

restoringdatafile 00005 to /u01/oradata/racdg/example.264.788541209

restoringdatafile 00006 to /u01/oradata/racdg/undotbs2.265.788541339

restoringdatafile 00007 to /u01/oradata/racdg/test_d.270.788794255

channelORA_AUX_DISK_1: reading from backup piece /u01/oradata/racfull_01oh6u76_1_1.bak

channelORA_AUX_DISK_1: restored backup piece 1

piecehandle=/u01/oradata/racfull_01oh6u76_1_1.bak tag=TAG20130813T150158

channelORA_AUX_DISK_1: restore complete, elapsed time: 00:02:45

Finishedrestore at 13-AUG-13

contents ofMemory Script:

{

switch clone datafile all;

}

executingMemory Script

datafile 1switched to datafile copy

input datafilecopy recid=9 stamp=823361998 filename=/u01/oradata/racdg/system.256.788541117

datafile 2switched to datafile copy

input datafilecopy recid=10 stamp=823361998filename=/u01/oradata/racdg/undotbs1.258.788541119

datafile 3switched to datafile copy

input datafilecopy recid=11 stamp=823361998 filename=/u01/oradata/racdg/sysaux.257.788541119

datafile 4switched to datafile copy

input datafilecopy recid=12 stamp=823361998 filename=/u01/oradata/racdg/users.259.788541119

datafile 5switched to datafile copy

input datafilecopy recid=13 stamp=823361998 filename=/u01/oradata/racdg/example.264.788541209

datafile 6switched to datafile copy

input datafilecopy recid=14 stamp=823361998filename=/u01/oradata/racdg/undotbs2.265.788541339

datafile 7switched to datafile copy

input datafilecopy recid=15 stamp=823361999 filename=/u01/oradata/racdg/test_d.270.788794255

FinishedDuplicate Db at 13-AUG-13

RMAN>

恢复完成了，现在可以将备库打开成应用模式：

[oracle@rhel133dbs]$ sqlplus / as sysdba

SQL*Plus:Release 10.2.0.1.0 - Production on Tue Aug 13 15:42:32 2013

Connected to:

Oracle Database10g Enterprise Edition Release 10.2.0.1.0 - Production

With thePartitioning, OLAP and Data Mining options

SQL> selectNAME,OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,DB_UNIQUE_NAME from v$database;

NAME OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL DB_UNIQUE_NAME

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

DEVDB MOUNTED MAXIMUM PERFORMANCE MAXIMUMPERFORMANCE racdg

SQL> alterdatabase recover managed standby database disconnect from session;

Databasealtered.

最后需要将rac的两台机器都重新启动一下，以使log_archive_dest_2等参数生效。