1. 环境说明
两台数据库，实例名分别为BEIJING和SHANGHAI，配置为Logical Standby Database。并进行主备切换测试。两台数据库结构相同也可以不同。
说明：主备数据库是靠控制文件来识别，因此用主库创建备库的控制文件是必须要做的工作。同时也说明无论是物理还是逻辑备库，都是基于主库而生成的。并且在创建逻辑备库之前，备库首先是工作在物理备库状态下的，可以从alert中体现出来。
2. 设置Primary Database
2.1 启用Forced Logging
检查是否启动Forced Logging
SYS@BEIJING> col FORCE_LOGGING for a15 SYS@BEIJING> select FORCE_LOGGING from v$database; FORCE_LOGGING --------------- NO
启用Forced Logging
SYS@BEIJING> alter database force logging; Database altered.
2.2 检查密码文件
[oracle@secdb1 dbs]$ ls /u01/app/oracle/product/10.2.0/db_1/dbs/ hc_BEIJING.dat init.ora lkBEIJING spfileBEIJING.ora initdw.ora initBEIJING.ora orapwBEIJING sqlnet.log
保证所有节点的sys密码必须一致
2.3 配置Standby Redo Log
查看现有日日志组
SYS@BEIJING> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_ ---------- ------- ------- -------------------------------- --- 1 ONLINE /u01/app/oracle/oradata/BEIJING/disk1/redo01.log NO 2 ONLINE /u01/app/oracle/oradata/BEIJING/disk1/redo02.log NO 3 ONLINE /u01/app/oracle/oradata/BEIJING/disk1/redo03.log NO 1 ONLINE /u01/app/oracle/oradata/BEIJING/disk2/redo01_02.log NO 2 ONLINE /u01/app/oracle/oradata/BEIJING/disk2/redo02_02.log NO 3 ONLINE /u01/app/oracle/oradata/BEIJING/disk2/redo03_02.log NO 4 ONLINE /u01/app/oracle/oradata/BEIJING/disk1/redo04_01.log NO 4 ONLINE /u01/app/oracle/oradata/BEIJING/disk2/redo04_02.log NO 5 ONLINE /u01/app/oracle/oradata/BEIJING/disk1/redo05_01.log NO 5 ONLINE /u01/app/oracle/oradata/BEIJING/disk2/redo05_02.log NO 10 rows selected.
增加standby redo log日志组，组数量等于online redo log group +1
SYS@BEIJING> alter database add standby logfile group 6 ('/u01/app/oracle/oradata/BEIJING/disk1/stredo06.log') size 100m;
Database altered.
SYS@BEIJING> alter database add standby logfile group 7 ('/u01/app/oracle/oradata/BEIJING/disk1/stredo07.log') size 100m;
Database altered.
SYS@BEIJING> alter database add standby logfile group 8 ('/u01/app/oracle/oradata/BEIJING/disk1/stredo08.log') size 100m;
Database altered.
SYS@BEIJING> alter database add standby logfile group 9 ('/u01/app/oracle/oradata/BEIJING/disk1/stredo09.log') size 100m;
Database altered.
SYS@BEIJING> alter database add standby logfile group 10 ('/u01/app/oracle/oradata/BEIJING/disk1/stredo10.log') size 100m;
Database altered.
SYS@BEIJING> alter database add standby logfile group 11 ('/u01/app/oracle/oradata/BEIJING/disk1/stredo11.log') size 100m;
Database altered.
检查配置结果
SYS@BEIJING> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_ ---------- ------- ------- --------------------------------- --- 1 ONLINE /u01/app/oracle/oradata/BEIJING/disk1/redo01.log NO 2 ONLINE /u01/app/oracle/oradata/BEIJING/disk1/redo02.log NO 3 ONLINE /u01/app/oracle/oradata/BEIJING/disk1/redo03.log NO 1 ONLINE /u01/app/oracle/oradata/BEIJING/disk2/redo01_02.log NO 2 ONLINE /u01/app/oracle/oradata/BEIJING/disk2/redo02_02.log NO 3 ONLINE /u01/app/oracle/oradata/BEIJING/disk2/redo03_02.log NO 4 ONLINE /u01/app/oracle/oradata/BEIJING/disk1/redo04_01.log NO 4 ONLINE /u01/app/oracle/oradata/BEIJING/disk2/redo04_02.log NO 5 ONLINE /u01/app/oracle/oradata/BEIJING/disk1/redo05_01.log NO 5 ONLINE /u01/app/oracle/oradata/BEIJING/disk2/redo05_02.log NO 6 STANDBY /u01/app/oracle/oradata/BEIJING/disk1/stredo06.log NO 7 STANDBY /u01/app/oracle/oradata/BEIJING/disk1/stredo07.log NO 8 STANDBY /u01/app/oracle/oradata/BEIJING/disk1/stredo08.log NO 9 STANDBY /u01/app/oracle/oradata/BEIJING/disk1/stredo09.log NO 10 STANDBY /u01/app/oracle/oradata/BEIJING/disk1/stredo10.log NO 11 STANDBY /u01/app/oracle/oradata/BEIJING/disk1/stredo11.log NO 16 rows selected.
2.4 配置参数文件
生成pfile
SYS@BEIJING> create pfile from spfile; File created.
在参数文件中增加如下部分
DB_NAME=BEIJING DB_UNIQUE_NAME=BEIJING LOG_ARCHIVE_CONFIG='DG_CONFIG=(BEIJING,SHANGHAI)' LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/arch1/BEIJING/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=BEIJING' LOG_ARCHIVE_DEST_2='SERVICE=SHANGHAI LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=SHANGHAI' LOG_ARCHIVE_DEST_3='LOCATION=/u01/app/oracle/arch2/BEIJING/ VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=BEIJING' LOG_ARCHIVE_DEST_STATE_1=ENABLE LOG_ARCHIVE_DEST_STATE_2=ENABLE LOG_ARCHIVE_DEST_STATE_3=ENABLE REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE LOG_ARCHIVE_FORMAT=%t_%s_%r.arc FAL_SERVER=SHANGHAI FAL_CLIENT=BEIJING DB_FILE_NAME_CONVERT='SHANGHAI','BEIJING' LOG_FILE_NAME_CONVERT='SHANGHAI','BEIJING' STANDBY_FILE_MANAGEMENT=AUTO
当结构不同时
DB_UNIQUE_NAME=BEIJING LOG_ARCHIVE_CONFIG='DG_CONFIG=(BEIJING,SHANGHAI)' LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/arch1/BEIJING/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=BEIJING' LOG_ARCHIVE_DEST_2='SERVICE=SHANGHAI LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=SHANGHAI' LOG_ARCHIVE_DEST_3='LOCATION=/u01/app/oracle/arch2/BEIJING/ VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=BEIJING' LOG_ARCHIVE_DEST_STATE_1=ENABLE LOG_ARCHIVE_DEST_STATE_2=ENABLE LOG_ARCHIVE_DEST_STATE_3=ENABLE REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE LOG_ARCHIVE_FORMAT=%t_%s_%r.arc FAL_SERVER=SHANGHAI FAL_CLIENT=BEIJING DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/SHANGHAI/','/u01/app/oracle/oradata/BEIJING/disk1/' LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/SHANGHAI/','/u01/app/oracle/oradata/BEIJING/disk1/','/u01/app/oracle/oradata/SHANGHAI/','/u01/app/oracle/oradata/BEIJING/disk2/' STANDBY_FILE_MANAGEMENT=AUTO
注：
"LOCATION="不要写成"LOCATION ="，否则会报如下错误：
ORA-16024: parameter LOG_ARCHIVE_DEST_3 cannot be parsed
LOG_FILE_NAME_CONVERT如果写错，将导致standby log file在备库无法自动生成，需要手工新建。
关闭数据库以创建spfile
SYS@BEIJING> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SYS@BEIJING> create spfile from pfile; File created.
2.5 启用归档
SYS@BEIJING> startup mount ORACLE instance started. Total System Global Area 314572800 bytes Fixed Size 1219184 bytes Variable Size 75498896 bytes Database Buffers 234881024 bytes Redo Buffers 2973696 bytes Database mounted. SYS@BEIJING> alter database archivelog; Database altered. SYS@BEIJING> alter database open; Database altered. SYS@BEIJING> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination /u01/app/oracle/arch2/BEIJING Oldest online log sequence 2 Next log sequence to archive 6 Current log sequence 6
2.6 检查主库中没有唯一标示的表
SYS@BEIJING> SELECT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_NOT_UNIQUE WHERE (OWNER, TABLE_NAME) NOT IN (SELECT DISTINCT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED) AND BAD_COLUMN = 'Y'; OWNER TABLE_NAME ------------------------------ ------------------------------ TSMSYS SRS$
3 设置Standby Database
3.1 创建Primary Database的数据文件备份
备份中不要包括日志文件
SYS@BEIJING> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down.
备份数据文件：
[oracle@secdb1 oradata]$ tar -zcvf BEIJING.tar *
3.2 创建Standby Database的控制文件
SYS@BEIJING> startup mount ORACLE instance started. Total System Global Area 314572800 bytes Fixed Size 1219184 bytes Variable Size 75498896 bytes Database Buffers 234881024 bytes Redo Buffers 2973696 bytes Database mounted. SYS@BEIJING> alter database create standby controlfile as '/home/oracle/controlstb.ctl'; Database altered. SYS@BEIJING> alter database open; Database altered.
3.3 创建Standby Database的参数文件
SYS@BEIJING> create pfile from spfile; File created.
编辑参数文件
DB_NAME=BEIJING DB_UNIQUE_NAME=SHANGHAI LOG_ARCHIVE_CONFIG='DG_CONFIG=(BEIJING,SHANGHAI)' LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/arch1/SHANGHAI/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=SHANGHAI' LOG_ARCHIVE_DEST_2='SERVICE=BEIJING LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=BEIJING' LOG_ARCHIVE_DEST_3='LOCATION=/u01/app/oracle/arch2/SHANGHAI/ VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=SHANGHAI' LOG_ARCHIVE_DEST_STATE_1=ENABLE LOG_ARCHIVE_DEST_STATE_2=ENABLE LOG_ARCHIVE_DEST_STATE_3=ENABLE REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE LOG_ARCHIVE_FORMAT=%t_%s_%r.arc FAL_SERVER=BEIJING FAL_CLIENT=SHANGHAI DB_FILE_NAME_CONVERT='BEIJING','SHANGHAI' LOG_FILE_NAME_CONVERT='BEIJING','SHANGHAI' STANDBY_FILE_MANAGEMENT=AUTO
或者当结构不同时
DB_NAME=BEIJING DB_UNIQUE_NAME=SHANGHAI LOG_ARCHIVE_CONFIG='DG_CONFIG=(BEIJING,SHANGHAI)' LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/oradata/arch1/SHANGHAI/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=SHANGHAI' LOG_ARCHIVE_DEST_2='SERVICE=BEIJING LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=BEIJING' LOG_ARCHIVE_DEST_3='LOCATION=/u01/app/oracle/oradata/arch2/SHANGHAI/ VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=SHANGHAI' LOG_ARCHIVE_DEST_STATE_1=ENABLE LOG_ARCHIVE_DEST_STATE_2=ENABLE LOG_ARCHIVE_DEST_STATE_3=ENABLE REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE LOG_ARCHIVE_FORMAT=%t_%s_%r.arc FAL_SERVER=BEIJING FAL_CLIENT=SHANGHAI DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/BEIJING/disk1/','/u01/app/oracle/oradata/SHANGHAI/' LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/BEIJING/disk1/','/u01/app/oracle/oradata/SHANGHAI/','/u01/app/oracle/oradata/BEIJING/disk2/','/u01/app/oracle/oradata/SHANGHAI/' STANDBY_FILE_MANAGEMENT=AUTO
3.4 拷贝数据库文件，控制文件，参数文件到Standby Database
[oracle@secdb1 ~]$ scp initBEIJING.ora BEIJING.tar tnsnames.ora listener.ora control01.ctl oracle@secdb2:/home/oracle The authenticity of host 'secdb2 (192.168.80.102)' can't be established. RSA key fingerprint is 90:77:13:80:91:dd:8c:42:c0:24:ee:f7:06:36:1c:7f. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added 'secdb2,192.168.80.102' (RSA) to the list of known hosts. oracle@secdb2's password: initBEIJING.ora 100% 1765 1.7KB/s 00:00 BEIJING.tar 100% 156MB 4.2MB/s 00:37 tnsnames.ora 100% 593 0.6KB/s 00:00 listener.ora 100% 569 0.6KB/s 00:00 control01.ctl 100% 6896KB 3.4MB/s 00:02 [oracle@secdb2 oradata]$ tar -zxvf BEIJING.tar [oracle@secdb2 disk2]$ mv ../disk1/control01.ctl . [oracle@secdb2 ~]$ mv initBEIJING.ora /u01/app/oracle/BEIJINGuct/10.2.0/db_1/dbs/initSHANGHAI.ora
Standby Database中不存在的目录要提前创建，包括oradata，admin，flash_recovery_area，arch
3.5 创建密码文件
[oracle@secdb2 dbs]$ orapwd file=orapwSHANGHAI password=oracle entries=30
保证密码和主库一致
3.6 编辑监听文件
[oracle@secdb2 ~]$ mv listener.ora /u01/app/oracle/product/10.2.0/db_1/network/admin
[oracle@secdb2 ~]$ mv tnsnames.ora /u01/app/oracle/product/10.2.0/db_1/network/admin
[oracle@secdb2 ~]$ cd /u01/app/oracle/product/10.2.0/db_1/network/admin/
[oracle@secdb2 admin]$ vi listener.ora
LISTENER=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=secdb2)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))
SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=SHANGHAI)
(ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1)
(SID_NAME=SHANGHAI))
(SID_DESC=
(SID_NAME=plsextproc)
(ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1)
(PROGRAM=extproc)))
[oracle@secdb2 admin]$ vi tnsnames.ora
BEIJING=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=secdb1)(PORT=1521)))
(CONNECT_DATA=
(SERVER=dedicated)
(SERVICE_NAME=BEIJING)))
SHANGHAI=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=secdb2)(PORT=1521)))
(CONNECT_DATA=
(SERVER=dedicated)
(SERVICE_NAME=SHANGHAI)))
3.7 启动监听器
[oracle@secdb2 admin]$ lsnrctl start LSNRCTL for Linux: Version 10.2.0.1.0 - production on 19-JAN-2013 21:40:16 Copyright (c) 1991, 2005, Oracle. All rights reserved. Starting /u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait... TNSLSNR for Linux: Version 10.2.0.1.0 - production System parameter file is /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora Log messages written to /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=secdb2.localdomain)(PORT=1521))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc))) Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=secdb2)(PORT=1521))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 10.2.0.1.0 - production Start Date 19-JAN-2013 21:40:17 Uptime 0 days 0 hr. 0 min. 1 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora Listener Log File /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=secdb2.localdomain)(PORT=1521))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc))) Services Summary... Service "SHANGHAI" has 1 instance(s). Instance "SHANGHAI", status UNKNOWN, has 1 handler(s) for this service... Service "plsextproc" has 1 instance(s). Instance "plsextproc", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully
3.8 启动Standby Database到mount阶段
[oracle@secdb2 admin]$ sqlplus sys/oracle@SHANGHAI as sysdba SQL*Plus: Release 10.2.0.1.0 - production on Sat Jan 19 21:58:27 2013 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to an idle instance. SQL> create spfile from pfile; File created. SQL> startup mount ORACLE instance started. Total System Global Area 314572800 bytes Fixed Size 1219184 bytes Variable Size 96470416 bytes Database Buffers 213909504 bytes Redo Buffers 2973696 bytes Database mounted.
3.9 在主库中创建Redo Data 数据字典
SYS@BEIJING> exec dbms_logstdby.build; PL/SQL procedure successfully completed.
3.10 转换为Logical Standby Database
SYS@SHANGHAI> alter database recover to logical standby SHANGHAI; Database altered. SYS@SHANGHAI> shutdown immediate ORACLE instance shut down.
此操作会修改db_name参数的名字。
注:请在转换之前，检查standby log file文件的路径是否已经转换成备库的路径，否则转换后将导致standby log file丢失。
SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_ ---------- ------- ------- -------------------------------------------------- --- 1 ONLINE /u01/app/oracle/oradata/SHANGHAI/redo01.log NO 2 ONLINE /u01/app/oracle/oradata/SHANGHAI/redo02.log NO 3 ONLINE /u01/app/oracle/oradata/SHANGHAI/redo03.log NO 1 INVALID ONLINE /u01/app/oracle/oradata/SHANGHAI/redo01_02.log NO 2 INVALID ONLINE /u01/app/oracle/oradata/SHANGHAI/redo02_02.log NO 3 INVALID ONLINE /u01/app/oracle/oradata/SHANGHAI/redo03_02.log NO 4 ONLINE /u01/app/oracle/oradata/SHANGHAI/redo04_01.log NO 4 ONLINE /u01/app/oracle/oradata/SHANGHAI/redo04_02.log NO 5 ONLINE /u01/app/oracle/oradata/SHANGHAI/redo05_01.log NO 5 ONLINE /u01/app/oracle/oradata/SHANGHAI/redo05_02.log NO 6 STANDBY /u01/app/oracle/oradata/SHANGHAI/stdbyredo06.log NO 7 STANDBY /u01/app/oracle/oradata/SHANGHAI/stdbyredo07.log NO 8 STANDBY /u01/app/oracle/oradata/SHANGHAI/stdbyredo08.log NO 9 STANDBY /u01/app/oracle/oradata/SHANGHAI/stdbyredo09.log NO 10 STANDBY /u01/app/oracle/oradata/SHANGHAI/stdbyredo10.log NO 11 STANDBY /u01/app/oracle/oradata/SHANGHAI/stdbyredo11.log NO
3.11 打开Logical Standby Database
ORACLE instance shut down.SYS@SHANGHAI> startup mount ORACLE instance started. Total System Global Area 314572800 bytes Fixed Size 1219184 bytes Variable Size 96470416 bytes Database Buffers 213909504 bytes Redo Buffers 2973696 bytes Database mounted. SYS@SHANGHAI> alter database open resetlogs; Database altered. SYS@SHANGHAI> alter database start logical standby apply immediate; Database altered.
3.12 测试Standby Database的归档操作
在主库中切换日志组
SYS@BEIJING> alter system switch logfile; System altered.
4 主备机切换
4.1 检查主库切换的可行性
SYS@BEIJING> select switchover_status from v$database; SWITCHOVER_STATUS -------------------- TO STANDBY
4.2 准备当前主库的switchover操作
SYS@BEIJING> ALTER DATABASE PREPARE TO SWITCHOVER TO LOGICAL STANDBY; Database altered.
4.3 准备目标备库
SQL> ALTER DATABASE PREPARE TO SWITCHOVER TO PRIMARY; Database altered.
4.4 确认主库已经可以进行switchover操作
SYS@BEIJING> SELECT SWITCHOVER_STATUS FROM V$DATABASE; SWITCHOVER_STATUS -------------------- TO LOGICAL STANDBY
4.5 切换原主库为备库
SYS@BEIJING> ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY; Database altered.
4.6 检查原备库是否可以进行switchover
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; SWITCHOVER_STATUS -------------------- TO PRIMARY
4.7 切换原备库为主库
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY; Database altered. SQL> alter database open; Database altered.
4.8 启动实时SQL Apply
SYS@BEIJING> ALTER DATABASE START LOGICAL STANDBY APPLY immediate; Database altered.
Speak Your Mind