A-A+
11gr2 RAC primary+single standby配置及切换测试
说明：
主库是位于32位的redhat linux5.8的11gr2的RAC，物理备库是在和主库相同OS下的单实例库。
注意：此处省略rac和单实例数据库的安装
简单架构如下：
主节点 11gr2 RAC 集群状态
[root@11grac1 ~]# su - grid [grid@11grac1 ~]$ crs_stat -t -v Name Type R/RA F/FT Target State Host ---------------------------------------------------------------------- ora....SM1.asm application 0/5 0/0 ONLINE ONLINE 11grac1 ora....C1.lsnr application 0/5 0/0 ONLINE ONLINE 11grac1 ora....ac1.gsd application 0/5 0/0 ONLINE ONLINE 11grac1 ora....ac1.ons application 0/3 0/0 ONLINE ONLINE 11grac1 ora....ac1.vip ora....t1.type 0/0 0/0 ONLINE ONLINE 11grac1 ora....SM2.asm application 0/5 0/0 ONLINE ONLINE 11grac2 ora....C2.lsnr application 0/5 0/0 ONLINE ONLINE 11grac2 ora....ac2.gsd application 0/5 0/0 ONLINE ONLINE 11grac2 ora....ac2.ons application 0/3 0/0 ONLINE ONLINE 11grac2 ora....ac2.vip ora....t1.type 0/0 0/0 ONLINE ONLINE 11grac2 ora.DATA.dg ora....up.type 0/5 0/ ONLINE ONLINE 11grac1 ora....ER.lsnr ora....er.type 0/5 0/ ONLINE ONLINE 11grac1 ora....N1.lsnr ora....er.type 0/5 0/0 ONLINE ONLINE 11grac1 ora.asm ora.asm.type 0/5 0/ ONLINE ONLINE 11grac1 ora.eons ora.eons.type 0/3 0/ ONLINE ONLINE 11grac1 ora.gsd ora.gsd.type 0/5 0/ ONLINE ONLINE 11grac1 ora....network ora....rk.type 0/5 0/ ONLINE ONLINE 11grac1 ora.oc4j ora.oc4j.type 0/5 0/0 ONLINE ONLINE 11grac1 ora.ons ora.ons.type 0/3 0/ ONLINE ONLINE 11grac1 ora.racdb.db ora....se.type 0/2 0/1 ONLINE ONLINE 11grac1 ora.scan1.vip ora....ip.type 0/0 0/0 ONLINE ONLINE 11grac1
主备节点查看数据库版本一致，如下：
SQL> set line 300
SQL> select * from gv$version;
INST_ID BANNER
---------- --------------------------------------------------------------------------------
1 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
1 PL/SQL Release 11.2.0.1.0 - Production
1 CORE 11.2.0.1.0 Production
1 TNS for Linux: Version 11.2.0.1.0 - Production
1 NLSRTL Version 11.2.0.1.0 - Production
2 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
2 PL/SQL Release 11.2.0.1.0 - Production
2 CORE 11.2.0.1.0 Production
2 TNS for Linux: Version 11.2.0.1.0 - Production
2 NLSRTL Version 11.2.0.1.0 - Production
10 rows selected.
主节点文件结构
SQL> select file_name from dba_data_files 2 union 3 select file_name from dba_temp_files; FILE_NAME -------------------------------------------------------------------------------- +DATA/racdb/sysaux01.dbf +DATA/racdb/system01.dbf +DATA/racdb/temp01.dbf +DATA/racdb/undotbs01.dbf +DATA/racdb/undotbs02.dbf +DATA/racdb/users01.dbf 6 rows selected. SQL> select MEMBER from gv$logfile; MEMBER -------------------------------------------------------------------------------- +DATA/racdb/redo02.log +DATA/racdb/redo01.log +DATA/racdb/redo03.log +DATA/racdb/redo04.log +DATA/racdb/redo02.log +DATA/racdb/redo01.log +DATA/racdb/redo03.log +DATA/racdb/redo04.log 8 rows selected.
主节点查看主机配置
[oracle@11grac1 ~]$ cat /etc/redhat-release Red Hat Enterprise Linux Server release 5.8 (Tikanga) [oracle@11grac1 ~]$ uname -a Linux 11grac1 2.6.32-300.10.1.el5uek #1 SMP Wed Feb 22 17:22:40 EST 2012 i686 i686 i386 GNU/Linux [oracle@11grac1 ~]$ getconf LONG_BIT 32
主节点IP配置
[grid@11grac1 ~]$ cat /etc/hosts # Do not remove the following line, or various programs # that require network functionality will fail. 127.0.0.1 localhost.localdomain localhost ::1 localhost6.localdomain6 localhost6 #public ip 192.168.137.161 11grac1 192.168.137.162 11grac2 #private ip 192.168.136.161 11grac1-priv 192.168.136.162 11grac2-priv #vip 192.168.137.163 11grac1-vip 192.168.137.164 11grac2-vip #openfiler ip 192.168.137.141 openfiler 192.168.136.141 openfiler-priv #scan ip 192.168.137.200 racscan
备节点IP设置
[root@racdb_st ~]# cat /etc/hosts # Do not remove the following line, or various programs # that require network functionality will fail. 127.0.0.1 localhost.localdomain localhost ::1 localhost6.localdomain6 localhost6 192.168.137.171 racdb_st
主节点归档设置
SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 5 Next log sequence to archive 6 Current log sequence 6
主节点参数文件
SQL> show parameter pfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string +DATA/racdb/spfileracdb.ora SQL> create pfile='/tmp/pfile_20130404.txt' from spfile; File created. SQL> !cat /tmp/pfile_20130404.txt racdb1.__db_cache_size=360710144 racdb2.__db_cache_size=377487360 racdb1.__java_pool_size=4194304 racdb2.__java_pool_size=4194304 racdb1.__large_pool_size=4194304 racdb2.__large_pool_size=4194304 racdb1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment racdb2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment racdb1.__pga_aggregate_target=213909504 racdb2.__pga_aggregate_target=213909504 racdb1.__sga_target=633339904 racdb2.__sga_target=633339904 racdb1.__shared_io_pool_size=0 racdb2.__shared_io_pool_size=0 racdb1.__shared_pool_size=255852544 racdb2.__shared_pool_size=239075328 racdb1.__streams_pool_size=0 racdb2.__streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/racdb/adump' *.audit_trail='db' *.cluster_database=TRUE *.compatible='11.2.0.0.0' *.control_files='+DATA/racdb/control01.ctl','+DATA/racdb/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='racdb' *.db_recovery_file_dest='+DATA' *.db_recovery_file_dest_size=4039114752 *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=racdbXDB)' racdb2.instance_number=2 racdb1.instance_number=1 *.open_cursors=300 *.pga_aggregate_target=210763776 *.processes=150 *.remote_listener='racscan:1521' *.remote_login_passwordfile='exclusive' *.sga_target=632291328 racdb2.thread=2 racdb1.thread=1 racdb1.undo_tablespace='UNDOTBS1' racdb2.undo_tablespace='UNDOTBS2'
主节点网络配置（2个节点一致）
[oracle@11grac1 ~]$ cd /u01/app/oracle/product/11.2.0/db_1/network/admin/
[oracle@11grac1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
RACDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = racscan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdb)
)
)
[oracle@11grac1 admin]$ ls -l
total 12
drwxr-xr-x 2 oracle oinstall 4096 Aug 28 2012 samples
-rw-r--r-- 1 oracle oinstall 187 May 9 2007 shrept.lst
-rw-r----- 1 oracle oinstall 324 Aug 28 2012 tnsnames.ora
############################################################################
以下进行RAC+DG配置
############################################################################
修改主备节点的tnsnames.ora文件，均修添加以下内容
# for rac+dg sets
RACDB_1 =
(DESCRIPTION=
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 11grac1-vip)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdb)
(INSTANCE_NAME = racdb1)
)
)
RACDB_2 =
(DESCRIPTION=
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 11grac2-vip)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdb)
(INSTANCE_NAME = racdb2)
)
)
RACDB_ST =
(DESCRIPTION=
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.137.171)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = racdb_st)
)
)
修改备节点监听文件，并启动监听
[oracle@racdb_st ~]$ cat /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.137.171)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = racdb_st)
)
)
修改备节点的hosts文件，添加以下内容
#for rac dg sets #vip 192.168.137.163 11grac1-vip 192.168.137.164 11grac2-vip
修改主节点参数文件
SQL> alter system set log_archive_config='dg_config=(racdb,racdb_st)'; System altered. SQL> alter system set log_archive_dest_2='SERVICE=racdb_st ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=racdb_st'; System altered. SQL> alter system set db_file_name_convert='/u01/app/oracle/oradata','+DATA/racdb' scope=spfile; System altered. SQL> alter system set log_file_name_convert='/u01/app/oracle/oradata','+DATA/racdb' scope=spfile; System altered. SQL> alter system set standby_file_management=auto; System altered. SQL> alter system set fal_server='racdb_st'; System altered. SQL> alter system set fal_client='racdb_1' sid='racdb1'; System altered. SQL> alter system set fal_client='racdb_2' sid='racdb2'; System altered. SQL> alter system set db_unique_name='racdb' scope=spfile; System altered.
重启主节点所有实例后，做全库备份
[oracle@11grac1 ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Thu Apr 4 16:23:39 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: RACDB (DBID=790139163)
RMAN> run{
2> allocate channel a1 type disk;
3> allocate channel a2 type disk;
4> allocate channel a3 type disk;
5> backup database filesperset 3 format '/u01/app/oracle/dbfullbak_%u_%s_%p_%T.bak';
6> release channel a1;
7> release channel a2;
8> release channel a3;
9> }
using target database control file instead of recovery catalog
allocated channel: a1
channel a1: SID=70 instance=racdb1 device type=DISK
allocated channel: a2
channel a2: SID=45 instance=racdb1 device type=DISK
allocated channel: a3
channel a3: SID=58 instance=racdb1 device type=DISK
Starting backup at 04-APR-13
channel a1: starting full datafile backup set
channel a1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/racdb/system01.dbf
channel a1: starting piece 1 at 04-APR-13
channel a2: starting full datafile backup set
channel a2: specifying datafile(s) in backup set
input datafile file number=00002 name=+DATA/racdb/sysaux01.dbf
input datafile file number=00004 name=+DATA/racdb/users01.dbf
channel a2: starting piece 1 at 04-APR-13
channel a3: starting full datafile backup set
channel a3: specifying datafile(s) in backup set
input datafile file number=00003 name=+DATA/racdb/undotbs01.dbf
input datafile file number=00005 name=+DATA/racdb/undotbs02.dbf
channel a3: starting piece 1 at 04-APR-13
channel a3: finished piece 1 at 04-APR-13
piece handle=/u01/app/oracle/dbfullbak_05o68d53_5_1_20130404.bak tag=TAG20130404T162359 comment=NONE
channel a3: backup set complete, elapsed time: 00:03:02
channel a3: starting full datafile backup set
channel a3: specifying datafile(s) in backup set
including current control file in backup set
channel a3: starting piece 1 at 04-APR-13
channel a2: finished piece 1 at 04-APR-13
piece handle=/u01/app/oracle/dbfullbak_04o68d52_4_1_20130404.bak tag=TAG20130404T162359 comment=NONE
channel a2: backup set complete, elapsed time: 00:12:34
channel a2: starting full datafile backup set
channel a2: specifying datafile(s) in backup set
including current SPFILE in backup set
channel a2: starting piece 1 at 04-APR-13
channel a1: finished piece 1 at 04-APR-13
piece handle=/u01/app/oracle/dbfullbak_03o68d51_3_1_20130404.bak tag=TAG20130404T162359 comment=NONE
channel a1: backup set complete, elapsed time: 00:15:34
channel a2: finished piece 1 at 04-APR-13
piece handle=/u01/app/oracle/dbfullbak_07o68dt0_7_1_20130404.bak tag=TAG20130404T162359 comment=NONE
channel a2: backup set complete, elapsed time: 00:02:46
channel a3: finished piece 1 at 04-APR-13
piece handle=/u01/app/oracle/dbfullbak_06o68dau_6_1_20130404.bak tag=TAG20130404T162359 comment=NONE
channel a3: backup set complete, elapsed time: 00:05:37
Finished backup at 04-APR-13
released channel: a1
released channel: a2
released channel: a3
准备创建备库控制文件
主库所有节点切换日志
SQL> alter system switch logfile; System altered.
在主节点创建备库控制文件
SQL> alter database create standby controlfile as '/tmp/racdb_st.ctl'; Database altered.
从主节点拷贝控制文件，密码文件和所有的备份文件到备库
[oracle@11grac1 ~]$ scp /tmp/racdb_st.ctl 192.168.137.171:/tmp/racdb_st.ctl The authenticity of host '192.168.137.171 (192.168.137.171)' can't be established. RSA key fingerprint is ed:67:0a:b9:a6:0e:6a:38:d1:b1:92:b0:e4:22:fb:10. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '192.168.137.171' (RSA) to the list of known hosts. racdb_st.ctl 100% 18MB 734.1KB/s 00:25 [oracle@11grac1 ~]$ scp /u01/app/oracle/dbfullbak* 192.168.137.171:/u01/app/oracle/ dbfullbak_03o68d51_3_1_20130404.bak 100% 582MB 2.0MB/s 04:59 dbfullbak_04o68d52_4_1_20130404.bak 100% 368MB 2.2MB/s 02:51 dbfullbak_05o68d53_5_1_20130404.bak 100% 4832KB 4.7MB/s 00:01 dbfullbak_06o68dau_6_1_20130404.bak 100% 18MB 3.5MB/s 00:05 dbfullbak_07o68dt0_7_1_20130404.bak 100% 96KB 96.0KB/s 00:00
创建备库密码文件（11g中已经不行）
[oracle@racdb_st dbs]$ orapwd password=oracle file=orapwracdb_st entries=5
此处采用直接拷贝
[oracle@11grac1 ~]$ scp /u01/app/oracle/product/11.2.0/db_1/dbs/orapwracdb1 192.168.137.171:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/orapwracdb_st orapwracdb1 100% 2048 2.0KB/s 00:00
构建备库参数文件并将库打开到nomount状态
注意环境变量中的sid设置
[oracle@racdb_st ~]$ cat /tmp/pfile.txt *.db_cache_size=360710144 *.java_pool_size=4194304 *.large_pool_size=4194304 *.pga_aggregate_target=213909504 *.sga_target=633339904 *.shared_pool_size=255852544 *.streams_pool_size=0 *.audit_file_dest='/u01/app/oracle/admin/racdb_st/adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.db_block_size=8192 *.db_domain='' *.db_name='racdb' *.db_unique_name='racdb_st' *.db_recovery_file_dest_size=4039114752 *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=racdbXDB)' *.open_cursors=300 *.pga_aggregate_target=210763776 *.processes=150 *.remote_login_passwordfile='exclusive' *.sga_target=632291328 *.log_archive_config='dg_config=(racdb,racdb_st)' *.log_archive_dest_2='SERVICE=racdb_1 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=racdb' *.db_file_name_convert='+DATA/racdb','/u01/app/oracle/oradata' *.log_file_name_convert='+DATA/racdb','/u01/app/oracle/oradata' *.standby_file_management=auto *.fal_server='racdb_1','racdb_1' *.fal_client='racdb_st' *.thread=1 *.control_files='/u01/app/oracle/oradata/racdb_st.ctl' *.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area/racdb_st' *.undo_tablespace='UNDOTBS1' *.service_names=racdb_st
注意：在备库需要创建相关目录
[oracle@racdb_st ~]$ sqlplus "/as sysdba" SQL*Plus: Release 11.2.0.1.0 Production on Sat Apr 6 00:13:50 2013 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount pfile='/tmp/pfile.txt' ORACLE instance started. Total System Global Area 631914496 bytes Fixed Size 1338364 bytes Variable Size 264242180 bytes Database Buffers 360710144 bytes Redo Buffers 5623808 bytes SQL> create spfile from pfile='/tmp/pfile.txt'; File created.
在主库恢复数据到备库
[oracle@11grac1 ~]$ rman target / auxiliary sys/oracle@racdb_st
Recovery Manager: Release 11.2.0.1.0 - Production on Thu Apr 4 19:33:30 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: RACDB (DBID=790139163)
connected to auxiliary database: RACDB (not mounted)
RMAN> duplicate target database for standby;
Starting Duplicate Db at 04-APR-13
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 device type=DISK
contents of Memory Script:
{
restore clone standby controlfile;
}
executing Memory Script
Starting restore at 04-APR-13
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: copied control file copy
input file name=/tmp/racdb_st.ctl
output file name=/u01/app/oracle/oradata/racdb_st.ctl
Finished restore at 04-APR-13
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
Using previous duplicated file /u01/app/oracle/oradata/sysaux01.dbf for datafile 2 with checkpoint SCN of 974435
Using previous duplicated file /u01/app/oracle/oradata/undotbs01.dbf for datafile 3 with checkpoint SCN of 974445
Using previous duplicated file /u01/app/oracle/oradata/users01.dbf for datafile 4 with checkpoint SCN of 974435
Using previous duplicated file /u01/app/oracle/oradata/undotbs02.dbf for datafile 5 with checkpoint SCN of 974445
contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/app/oracle/oradata/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/system01.dbf";
restore
clone datafile
1 ;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/temp01.dbf in control file
executing command: SET NEWNAME
Starting restore at 04-APR-13
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/system01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/dbfullbak_03o68d51_3_1_20130404.bak
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/dbfullbak_03o68d51_3_1_20130404.bak tag=TAG20130404T162359
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:12:30
Finished restore at 04-APR-13
contents of Memory Script:
{
catalog clone datafilecopy "/u01/app/oracle/oradata/sysaux01.dbf",
"/u01/app/oracle/oradata/undotbs01.dbf",
"/u01/app/oracle/oradata/users01.dbf",
"/u01/app/oracle/oradata/undotbs02.dbf";
switch clone datafile 2 to datafilecopy
"/u01/app/oracle/oradata/sysaux01.dbf";
switch clone datafile 3 to datafilecopy
"/u01/app/oracle/oradata/undotbs01.dbf";
switch clone datafile 4 to datafilecopy
"/u01/app/oracle/oradata/users01.dbf";
switch clone datafile 5 to datafilecopy
"/u01/app/oracle/oradata/undotbs02.dbf";
switch clone datafile all;
}
executing Memory Script
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/sysaux01.dbf RECID=3 STAMP=811885699
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/undotbs01.dbf RECID=4 STAMP=811885699
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/users01.dbf RECID=5 STAMP=811885699
cataloged datafile copy
datafile copy file name=/u01/app/oracle/oradata/undotbs02.dbf RECID=6 STAMP=811885701
datafile 2 switched to datafile copy
input datafile copy RECID=3 STAMP=811885699 file name=/u01/app/oracle/oradata/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=4 STAMP=811885699 file name=/u01/app/oracle/oradata/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=5 STAMP=811885699 file name=/u01/app/oracle/oradata/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=6 STAMP=811885701 file name=/u01/app/oracle/oradata/undotbs02.dbf
datafile 1 switched to datafile copy
input datafile copy RECID=7 STAMP=811885726 file name=/u01/app/oracle/oradata/system01.dbf
Finished Duplicate Db at 04-APR-13
在备库检查文件情况
SQL> select status from v$instance; STATUS ------------ MOUNTED SQL> select member from v$logfile; MEMBER -------------------------------------------------------------------------------- /u01/app/oracle/oradata/redo02.log /u01/app/oracle/oradata/redo01.log /u01/app/oracle/oradata/redo03.log /u01/app/oracle/oradata/redo04.log SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/system01.dbf /u01/app/oracle/oradata/sysaux01.dbf /u01/app/oracle/oradata/undotbs01.dbf /u01/app/oracle/oradata/users01.dbf /u01/app/oracle/oradata/undotbs02.dbf SQL> select name from v$tempfile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/temp01.dbf
在主节点为所有的实例创建备用日志
查看当前主节点的各个实例当前大小
SQL> select thread#,group#,bytes/1024/1024 mb from v$log;
THREAD# GROUP# MB
---------- ---------- ----------
1 1 50
1 2 50
2 3 50
2 4 50
SQL> alter database add standby logfile thread 1 group 5 ('+data') size 50m;
Database altered.
SQL> alter database add standby logfile thread 1 group 6 ('+data') size 50m;
Database altered.
SQL> alter database add standby logfile thread 1 group 7 ('+data') size 50m;
Database altered.
SQL> alter database add standby logfile thread 2 group 8 ('+data') size 50m;
Database altered.
SQL> alter database add standby logfile thread 2 group 9 ('+data') size 50m;
Database altered.
SQL> alter database add standby logfile thread 2 group 10 ('+data') size 50m;
Database altered.
SQL> select INST_ID,GROUP#,TYPE,MEMBER from gv$logfile order by 1,2,3;
INST_ID GROUP# TYPE MEMBER
---------- ---------- ------- --------------------------------------------------
1 1 ONLINE +DATA/racdb/redo01.log
1 2 ONLINE +DATA/racdb/redo02.log
1 3 ONLINE +DATA/racdb/redo03.log
1 4 ONLINE +DATA/racdb/redo04.log
1 5 STANDBY +DATA/racdb/onlinelog/group_5.305.811887421
1 6 STANDBY +DATA/racdb/onlinelog/group_6.306.811887513
1 7 STANDBY +DATA/racdb/onlinelog/group_7.307.811887529
1 8 STANDBY +DATA/racdb/onlinelog/group_8.308.811887553
1 9 STANDBY +DATA/racdb/onlinelog/group_9.309.811887569
1 10 STANDBY +DATA/racdb/onlinelog/group_10.310.811887585
2 1 ONLINE +DATA/racdb/redo01.log
INST_ID GROUP# TYPE MEMBER
---------- ---------- ------- --------------------------------------------------
2 2 ONLINE +DATA/racdb/redo02.log
2 3 ONLINE +DATA/racdb/redo03.log
2 4 ONLINE +DATA/racdb/redo04.log
2 5 STANDBY +DATA/racdb/onlinelog/group_5.305.811887421
2 6 STANDBY +DATA/racdb/onlinelog/group_6.306.811887513
2 7 STANDBY +DATA/racdb/onlinelog/group_7.307.811887529
2 8 STANDBY +DATA/racdb/onlinelog/group_8.308.811887553
2 9 STANDBY +DATA/racdb/onlinelog/group_9.309.811887569
2 10 STANDBY +DATA/racdb/onlinelog/group_10.310.811887585
20 rows selected.
测试同步
主库测试数据
备库开启日志应用
SQL> alter database recover managed standby database disconnect from session; Database altered.
主库添加测试数据
SQL> create tablespace yallonking datafile '+data' size 1m; Tablespace created. SQL> select file_name from dba_data_files; FILE_NAME ------------------------------------------------------------------------------------------------------------------------------------ +DATA/racdb/users01.dbf +DATA/racdb/undotbs01.dbf +DATA/racdb/sysaux01.dbf +DATA/racdb/system01.dbf +DATA/racdb/undotbs02.dbf +DATA/racdb/datafile/yallonking.342.811901535 6 rows selected.
主节点各个实例切换日志
SQL> alter system switch logfile; System altered.
在备节点查看记录
SQL> select name from v$datafile; NAME -------------------------------------------------------------- /u01/app/oracle/oradata/system01.dbf /u01/app/oracle/oradata/sysaux01.dbf /u01/app/oracle/oradata/undotbs01.dbf /u01/app/oracle/oradata/users01.dbf /u01/app/oracle/oradata/undotbs02.dbf /u01/app/oracle/oradata/datafile/yallonking.342.811901535
进行切换测试
首先关掉主节点的实例2
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down.
在主库另一个节点上执行切换到物理备库
SQL> alter database commit to switchover to physical standby with session shutdown; Database altered.
在备库执行切换到主库
SQL> alter database commit to switchover to primary with session shutdown; Database altered.
将主库启动到mount，并启动日志接收应用
SQL> shutdown immediate ORA-01507: database not mounted ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 631914496 bytes Fixed Size 1338364 bytes Variable Size 264242180 bytes Database Buffers 360710144 bytes Redo Buffers 5623808 bytes Database mounted. SQL> alter database recover managed standby database disconnect from session; Database altered.
将新的主库（原备库）打开
SQL> shutdown immediate ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 630501376 bytes Fixed Size 2215984 bytes Variable Size 264245200 bytes Database Buffers 360710144 bytes Redo Buffers 3330048 bytes Database mounted. Database opened.
测试同步
在新主库（原备库）建立测试数据
SQL> col FILE_NAME for a70 SQL> select tablespace_name,file_name from dba_data_files; TABLESPACE_NAME FILE_NAME ------------------------------ ---------------------------------------------------------------------- USERS /u01/app/oracle/oradata/users01.dbf UNDOTBS1 /u01/app/oracle/oradata/undotbs01.dbf SYSAUX /u01/app/oracle/oradata/sysaux01.dbf SYSTEM /u01/app/oracle/oradata/system01.dbf UNDOTBS2 /u01/app/oracle/oradata/undotbs02.dbf YALLONKING /u01/app/oracle/oradata/datafile/yallonking.432.812066619 6 rows selected. SQL> create tablespace yallonking_2 datafile '/u01/app/oracle/oradata/yallonking_2.dbf' size 1m; Tablespace created.
在新主库（原备库）切换日志
SQL> alter system switch logfile; System altered. SQL> / System altered.
在备库查看同步结果
SQL> select name from v$datafile; NAME ------------------------------------------------------------------------------------------------------------------------------------ +DATA/racdb/system01.dbf +DATA/racdb/sysaux01.dbf +DATA/racdb/undotbs01.dbf +DATA/racdb/users01.dbf +DATA/racdb/undotbs02.dbf +DATA/racdb/datafile/yallonking.432.812066619 +DATA/racdb/yallonking_2.dbf 7 rows selected.
至此，配置及切换已经ok！
