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 lkPROD spfilePROD.ora
initdw.ora initPROD.ora orapwPROD 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 initPROD.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:
initPROD.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 initPROD.ora /u01/app/oracle/PRODuct/10.2.0/db_1/dbs/initSHANGHAI.ora
Standby Database中不存在的目录要提前创建，包括oradata，admin，flash_recovery_area，arch
注意：不用拷贝redo log文件，
否则会报错
SQL> alter database recover to logical standby SHANGHAI;
alter database recover to logical standby SHANGHAI
*
ERROR at line 1:
ORA-00344: unable to re-create online log
'/u01/app/oracle/oradata/BEIJING/disk1/redo01.log'
ORA-27040: file create error, unable to create file
Linux Error: 2: No such file or directory
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)))
BEIJING_S=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=secdb1)(PORT=1526)))
(CONNECT_DATA=
(SERVER=shared)
(SERVICE_NAME=BEIJING)))
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
此操作会修改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.
SYS@SHANGHAI>
3.12 测试Standby Database的归档操作
在主库中切换日志组
SYS@BEIJING> alter system switch logfile;
System altered.
在备库中查看归档日志是否正常
SQL> select sequence#,first_time,next_time from v$archived_log order by sequence#;
SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
4 17-JAN-13 19-JAN-13
5 19-JAN-13 19-JAN-13
6 19-JAN-13 19-JAN-13
7 19-JAN-13 19-JAN-13
8 19-JAN-13 19-JAN-13
9 19-JAN-13 19-JAN-13
10 19-JAN-13 19-JAN-13
11 19-JAN-13 19-JAN-13
8 rows selected.
在备库中查看归档日志是否以应用
SQL> select sequence#,applied from v$archived_log order by sequence#;
SEQUENCE# APP
---------- ---
4 NO
5 YES
6 YES
7 YES
8 YES
9 YES
10 YES
11 YES
8 rows selected.
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.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/12710778/viewspace-753686/ ，如需转载，请注明出处，否则将追究法律责任。
