1.配置一个基于归档日志的RAC到单节点standby的data guard,贴出主要的配置操作。
rac上启动归档和force logging,以下操作在instance1上完成
alter system set cluster_database=false scope=spfile;
alter system set log_archive_dest_1='location=/opt/ora10g/arch/' scope=spfile;
srvctl stop database -d racdb -o immediate;
sqlplus > startup mount;
alter database archive log;
alter database force logging;
alter system set cluster_database=true scope=spfile;
shutdown abort;srvctl start database -d racdb;
rac上配置dg所需参数,仅在instance1上完成:
ALTER SYSTEM SET log_archive_dest_1 ='LOCATION=location=/opt/ora10g/arch/' SCOPE=SPFILE;
ALTER SYSTEM SET log_archive_dest_2 = 'SERVICE=dg4rac VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=racdb_st' SCOPE=both;
ALTER SYSTEM SET fal_client = racdb SCOPE = both;
ALTER SYSTEM SET fal_server = dg4rac SCOPE = both;
ALTER SYSTEM SET standby_archive_dest='/opt/ora10g/arch/' SCOPE = both;
ALTER SYSTEM SET standby_file_management = AUTO SCOPE = both;
srvctl stop database -d racdb -o immediate;
srvctl start database -d racdb;
standby配置环境变量并安装数据库软件:
[oracle@dg4rac dbs]$ tail -n15 ~/.bash_profile
export TMP=/tmp
export TMPDIR=$TMP
export ORACLE_BASE=/opt/ora10g
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export ORACLE_SID=racdb
export ORACLE_TERM=xterm
export PATH=/usr/sbin:$PATH
#export PATH=/opt/ora10g/product/10.2.0/crs_1/bin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/bin:/bin:/usr/bin:/usr/local/bin:/usr/X11R6/bin/
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
export TNS_ADMIN=$ORACLE_BASE/product/10.2.0/db_1/network/admin
umask 022
[oracle@dg4rac dbs]$
standby上配置dg和rman duplicate db参数:
[oracle@dg4rac ~]$ cd /opt/ora10g/product/10.2.0/db_1/dbs/
[oracle@dg4rac dbs]$ ls
18ohidnt_1_1 alert_racdb.log cntrlracdb.dbf hc_racdb.dat initdw.ora init.ora initracdb.ora lkRACDB_ST lkRACDG orapwracdb
[oracle@dg4rac dbs]$ cat initracdb.ora
db_name=racdb
db_unique_name=racdb_st
compatible=10.2.0.1.0
db_file_name_convert='+DATE/racdb/datafile/','/opt/ora10g/oradata/racdb/','+DATE/racdb/tempfile/','/opt/ora10g/oradata/racdb/'
log_file_name_convert='+DATE/racdb/onlinelog/','/opt/ora10g/oradata/racdb/'
log_archive_dest_1='LOCATION=/opt/ora10g/arch/'
log_archive_dest_2='SERVICE=racdb VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=racdb_pr'
fal_client=dg4rac
fal_server=racdb
standby_archive_dest='/opt/ora10g/arch/'
standby_file_management=AUTO
[oracle@dg4rac dbs]$
standby配置监听:
[oracle@dg4rac admin]$ cat listener.ora
# listener.ora Network Configuration File: /opt/ora10g/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/ora10g/product/10.2.0/db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = racdb)
(ORACLE_HOME = /opt/ora10g/product/10.2.0/db_1)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg4rac)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
[oracle@dg4rac admin]$
standby配置TNS:
[oracle@dg4rac admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /opt/ora10g/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
RACDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac3-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdb)
(INSTANCE_NAME = racdb1)
)
)
RACDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac3-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = rac4-vip)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdb)
)
)
LISTENERS_RACDB =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac3-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = rac4-vip)(PORT = 1521))
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
RACDB2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac4-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdb)
(INSTANCE_NAME = racdb2)
)
)
dg4rac =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg4rac)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdb)
)
)
[oracle@dg4rac admin]$
[oracle@rac3 admin]$ tail -n 10 $TNS_ADMIN/tnsnames.ora
dg4rac =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg4rac)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdb)
)
)
[oracle@rac3 admin]$
dg4rac =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg4rac)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdb)
)
)
[oracle@rac4 admin]$
在节点1(其他主机也可以)上生成orapwd密码文件,并复制到rac其他节点和standby机器上,需要做相应的改名:
[oracle@rac3 dbs]$ cd $ORACLE_HOME/dbs[oracle@rac3 dbs]$ orapwd file=orapwracdb1 password=oracle entries=5
[oracle@rac3 dbs]$ scp orapwracdb1rac4:/opt/ora10g/product/10.2.0/db_1/dbs/orapwracdb2
[oracle@rac3 dbs]$ scp orapwracdb1dg4rac:/opt/ora10g/product/10.2.0/db_1/dbs/orapwracdb
启动standby到nomount状态,确保rac各节点到standby及standby到rac的都能连通:
[oracle@rac3 admin]$ tnsping dg4rac
[oracle@rac3 admin]$ sqlplus sys/oracle@dg4rac as sysdba
[oracle@rac4 admin]$ tnsping dg4rac
[oracle@rac4 admin]$ sqlplus sys/oracle@dg4rac as sysdba
[oracle@dg4rac admin]$ tnsping racdb
[oracle@dg4rac ~]$ sqlplus sys/oracle@racdb as sysdba
在rac节点1上用rman做一个全备并对控制文件做一个standby备份:
configure channel 1 device type disk connect sys/oracle@racdb1;
configure channel 2 device type disk connect sys/oracle@racdb2;
configure device type disk parallelism 2;
backup database format '/opt/ora10g/backup/full_%d_%T_%s' plus archivelog format '/opt/ora10g/backup/arch_%d_%T_%s' delete all input;
BACKUP CURRENT CONTROLFILE FOR STANDBY;
将rac各节点的备份与standby控制文件copy到standby机器上同结构的目录:
[oracle@rac4 ~]$ scp /opt/ora10g/backup/* dg4rac:/opt/ora10g/backup/
arch_RACDB_20130817_39 100% 4608 4.5KB/s 00:00
full_RACDB_20130817_35 100% 417MB 10.2MB/s 00:41
full_RACDB_20130817_36 100% 15MB 14.8MB/s 00:01
full_RACDB_20130817_37 100% 96KB 96.0KB/s 00:01
[oracle@rac4 ~]$
[oracle@rac3 ~]$ scp /opt/ora10g/backup/* dg4rac:/opt/ora10g/backup/
arch_RACDB_20130817_32 100% 2691KB 2.6MB/s 00:00
arch_RACDB_20130817_33 100% 645KB 645.0KB/s 00:00
arch_RACDB_20130817_38 100% 34KB 33.5KB/s 00:00
full_RACDB_20130817_34 100% 968MB 8.0MB/s 02:01
[oracle@rac3 ~]$
[oracle@rac3 backup]$ scp /opt/ora10g/product/10.2.0/db_1/dbs/18ohidnt_1_1 dg4rac:/opt/ora10g/product/10.2.0/db_1/dbs/18ohidnt_1_1
18ohidnt_1_1 100% 15MB 14.8MB/s 00:01
[oracle@rac3 backup]$
在rac节点1(rac3)上执行duplicate:
[oracle@rac3 ~]$ rman NOCATALOG TARGET / AUXILIARY sys/oracle@dg4rac;
Recovery Manager: Release 10.2.0.1.0 - Production on Sun Aug 18 12:20:38 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: RACDB (DBID=797407379)
using target database control file instead of recovery catalog
connected to auxiliary database: RACDB (not mounted)
RMAN> DUPLICATE TARGET DATABASE FOR STANDBY;
Starting Duplicate Db at 18-AUG-13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=36 devtype=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: sid=35 devtype=DISK
contents of Memory Script:
{
restore clone standby controlfile;
sql clone 'alter database mount standby database';
}
executing Memory Script
Starting restore at 18-AUG-13
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /opt/ora10g/product/10.2.0/db_1/dbs/18ohidnt_1_1
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/opt/ora10g/product/10.2.0/db_1/dbs/18ohidnt_1_1 tag=TAG20130817T233421
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output filename=/opt/ora10g/product/10.2.0/db_1/dbs/cntrlracdb.dbf
Finished restore at 18-AUG-13
sql statement: alter database mount standby database
released channel: ORA_AUX_DISK_1
released channel: ORA_AUX_DISK_2
contents of Memory Script:
{
set newname for tempfile 1 to
"/opt/ora10g/oradata/racdb/temp.274.799790627";
switch clone tempfile all;
set newname for datafile 1 to
"/opt/ora10g/oradata/racdb/system.271.799790617";
set newname for datafile 2 to
....
如果duplicate的时候没有dorecover选项且有归档在备份后删掉了,可能要在standby上手动恢复出arch log,否则会有gap:
RMAN> run {
2> allocate channel c1 type disk;
3> set archivelog destination to '/opt/ora10g/arch/';
4> restore archivelog from logseq 40 until logseq 40;
5> release channel c1;
6> }
RMAN> run {
2> allocate channel c1 type disk;
3> set archivelog destination to '/opt/ora10g/arch/';
4> restore archivelog sequence 40 thread 2;
5> release channel c1;
6> }
检查作为primary的rac上的主要view:
节点1:
SQL> select name,db_unique_name,open_mode,database_role,switchover_status from v$database;
NAME DB_UNIQUE_ OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
---------- ---------- ---------- ---------------- --------------------
RACDB RACDB_PR READ WRITE PRIMARY SESSIONS ACTIVE
SQL> select process,status,thread#,sequence# from v$managed_standby;
PROCESS STATUS THREAD# SEQUENCE#
--------- ------------ ---------- ----------
ARCH CLOSING 1 66
ARCH CLOSING 1 66
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
racdb1 OPEN
SQL>
节点2:
SQL> select name,db_unique_name,open_mode,database_role,switchover_status from v$database;
NAME DB_UNIQUE_ OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
---------- ---------- ---------- ---------------- --------------------
RACDB RACDB_PR READ WRITE PRIMARY SESSIONS ACTIVE
SQL> select process,status,thread#,sequence# from v$managed_standby;
PROCESS STATUS THREAD# SEQUENCE#
--------- ------------ ---------- ----------
ARCH CLOSING 2 46
ARCH CLOSING 2 46
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
racdb2 OPEN
SQL>
standby:
SQL> select name,db_unique_name,open_mode,database_role,switchover_status from v$database;
NAME DB_UNIQUE_ OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
---------- ---------- ---------- ---------------- --------------------
RACDB racdb_st MOUNTED PHYSICAL STANDBY SESSIONS ACTIVE
SQL> select process,status,thread#,sequence# from v$managed_standby;
PROCESS STATUS THREAD# SEQUENCE#
--------- ------------ ---------- ----------
ARCH CONNECTED 0 0
ARCH CONNECTED 0 0
RFS IDLE 0 0
MRP0 WAIT_FOR_LOG 1 67
RFS IDLE 0 0
SQL>
standby上启动MRP进程应用redo/arch log:
SQL> alter database recover managed standby database disconnect from session;
如果需使用最大保护或最高可用模式,还需在主备双方添加(maximum number of logfiles for each thread + 1) * maximum number of threads 个 standby redo log。
ALTER DATABASE ADD STANDBY LOGFILE THREAD 5 ('/oracle/dbs/log1c.rdo','/oracle/dbs/log2c.rdo') SIZE 500M;http://docs.oracle.com/cd/B19306_01/server.102/b14239/create_ps.htm#SBYDB00426
--EOF--
2.分别从RAC的两个节点切换日志,让归档传到standby,在standby上观察它是如何应用来自两个节点的归档日志的,贴出观察到的结果。
节点1连续归档两次,然后节点2连续归档两次:SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 67 52428800 1 NO CURRENT 1142010 18-AUG-13
2 1 66 52428800 1 YES INACTIVE 1141719 18-AUG-13
3 2 47 52428800 1 NO CURRENT 1144645 18-AUG-13
4 2 46 52428800 1 YES INACTIVE 1142022 18-AUG-13
节点1:SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
节点2:SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 69 52428800 1 NO CURRENT 1147969 18-AUG-13
2 1 68 52428800 1 YES INACTIVE 1147959 18-AUG-13
3 2 49 52428800 1 NO CURRENT 1147978 18-AUG-13
4 2 48 52428800 1 YES INACTIVE 1147973 18-AUG-13
SQL>
观察standby的alert log,可以看出standby对接收到的两个节点的日志是按照scn的先后顺序应用的:
...
Sun Aug 18 14:57:35 2013
RFS[1]: No standby redo logfiles created
RFS[1]: Archived Log: '/opt/ora10g/arch/1_67_799790611.dbf'
Sun Aug 18 14:57:35 2013
Media Recovery Log /opt/ora10g/arch/1_67_799790611.dbf
Media Recovery Log /opt/ora10g/arch/2_46_799790611.dbf
Media Recovery Waiting for thread 2 sequence 47
Sun Aug 18 14:57:40 2013
RFS[1]: No standby redo logfiles created
RFS[1]: Archived Log: '/opt/ora10g/arch/1_68_799790611.dbf'
Sun Aug 18 14:57:45 2013
RFS[2]: No standby redo logfiles created
RFS[2]: Archived Log: '/opt/ora10g/arch/2_47_799790611.dbf'
Sun Aug 18 14:57:46 2013
Media Recovery Log /opt/ora10g/arch/2_47_799790611.dbf
Media Recovery Log /opt/ora10g/arch/1_68_799790611.dbf
Media Recovery Waiting for thread 1 sequence 69
Sun Aug 18 14:57:51 2013
RFS[2]: No standby redo logfiles created
RFS[2]: Archived Log: '/opt/ora10g/arch/2_48_799790611.dbf'
--EOF--