Oracle DG 之逻辑备库--Switchover
Oracle DG 之逻辑备库--Switchover系统环境：
操作系统：RedHat EL5
Oracle： Oracle 11gR2
逻辑备库switchover 与物理备库不同，以下案例记录了如何在逻辑备库下switchover。
1、Switchover 前的准备
主库：
15:20:48 SYS@ prod>select name,database_role,switchover_status from v$database;
NAME DATABASE_ROLE SWITCHOVER_STATUS
--------- ---------------- --------------------
PROD PRIMARY TO STANDBY
Elapsed: 00:00:00.00
添加归档日志位置：
15:21:18 SYS@ prod>alter system set log_archive_dest_3='location=/u01/arch1 valid_for=(standby_logfiles,standby_roles) db_unique_name=bj';
System> Elapsed: 00:00:00.13
切换前准备：
15:29:48 SYS@ prod>alter database prepare to switchover to logical standby;
Database> Elapsed: 00:00:00.04
15:30:58 SYS@ prod>select name,database_role,switchover_status from v$database;
NAME DATABASE_ROLE SWITCHOVER_STATUS
--------- ---------------- --------------------
PROD PRIMARY PREPARING SWITCHOVER
告警日志：
alter database prepare to switchover to logical standby
ALTER DATABASE PREPARE TO SWITCHOVER TO LOGICAL STANDBY (prod)
Completed:> Fri Apr 11 15:31:43 2014
RFS: Assigned to RFS process 3776
RFS:> RFS: Opened log for thread 1 sequence 8 dbid 1674782321 branch 843241928
Fri Apr 11 15:32:15 2014
RFS: Assigned to RFS process 3781
RFS:>
RFS LogMiner: RFS> Fri Apr 11 15:32:21 2014
RFS: Assigned to RFS process 3783
RFS:> RFS: Opened log for thread 1 sequence 9 dbid 1674782321 branch 843241928
Fri Apr 11 15:32:34 2014
RFS LogMiner: Registered logfile to LogMiner session> Fri Apr 11 15:32:39 2014
RFS: Assigned to RFS process 3790
RFS:> RFS: Opened log for thread 1 sequence 10 dbid 1674782321 branch 843241928
RFS LogMiner: Registered logfile to LogMiner session> 备库：
添加归档日志位置：
15:21:18 SYS@ sh>alter system set log_archive_dest_3='location=/u01/arch1 valid_for=(standby_logfiles,standby_roles) db_unique_name=sh';
15:20:48 SYS@ sh>select name,database_role,switchover_status from v$database;
NAME DATABASE_ROLE SWITCHOVER_STATUS
--------- ---------------- --------------------
PROD PRIMARY NOT ALLOW
切换前准备： 15:30:39 SYS@ sh>alter database prepare to switchover to primary;
Database> Elapsed: 00:01:11.00
15:33:45 SYS@ sh>select name,database_role,switchover_status from v$database;
NAME DATABASE_ROLE SWITCHOVER_STATUS
--------- ---------------- --------------------
SHDB LOGICAL STANDBYPREPARING SWITCHOVER
Elapsed: 00:00:00.10
15:35:08 SYS@ sh>
告警日志：
alter database prepare to switchover to primary
ALTER DATABASE SWITCHOVER TO PRIMARY (sh)
ALTER DATABASE PREPARE TO SWITCHOVER TO PRIMARY (sh)
Fri Apr 11 15:32:34 2014
Thread 1 advanced to log sequence 8 (LGWR switch)
Current log# 2 seq# 8 mem# 0: /disk1/oradata/sh/redo02a.log
Current log# 2 seq# 8 mem# 1: /disk2/oradata/sh/redo02b.log
Fri Apr 11 15:32:48 2014
Archived Log entry 7 added for thread 1 sequence 7> Fri Apr 11 15:32:49 2014
Fri Apr 11 15:32:49 2014
Logminer Bld: Build started
ALTER SYSTEM SWITCH ALL LOGFILE start (sh)
ALTER SYSTEM SWITCH ALL LOGFILE complete (sh)
Fri Apr 11 15:32:49 2014
Thread 1 advanced to log sequence 9 (LGWR switch)
Current log# 3 seq# 9 mem# 0: /disk1/oradata/sh/redo03a.log
Current log# 3 seq# 9 mem# 1: /disk2/oradata/sh/redo03b.log
ARC2: LGWR is scheduled to archive destination LOG_ARCHIVE_DEST_2 after log switch
Fri Apr 11 15:32:49 2014
Logminer Bld: Lockdown Complete.DB_TXN_SCN is 0 968908 LockdownSCN is 968908
Archived Log entry 8 added for thread 1 sequence 8> Fri Apr 11 15:33:26 2014
ALTER SYSTEM ARCHIVE LOG
Fri Apr 11 15:33:26 2014
Thread 1 cannot allocate new log, sequence 10
Checkpoint not complete
Current log# 3 seq# 9 mem# 0: /disk1/oradata/sh/redo03a.log
Current log# 3 seq# 9 mem# 1: /disk2/oradata/sh/redo03b.log
ARCH: LGWR is scheduled to archive destination LOG_ARCHIVE_DEST_2 after log switch
Thread 1 advanced to log sequence 10 (LGWR switch)
Current log# 1 seq# 10 mem# 0: /disk1/oradata/sh/redo01a.log
Current log# 1 seq# 10 mem# 1: /disk2/oradata/sh/redo01b.log
Fri Apr 11 15:33:42 2014
Archived Log entry 10 added for thread 1 sequence 9> Fri Apr 11 15:33:42 2014
Logminer Bld: Done
Fri Apr 11 15:33:42 2014
Thread 1 cannot allocate new log, sequence 11
Checkpoint not complete
Current log# 1 seq# 10 mem# 0: /disk1/oradata/sh/redo01a.log
Current log# 1 seq# 10 mem# 1: /disk2/oradata/sh/redo01b.log
ARCH: LGWR is scheduled to archive destination LOG_ARCHIVE_DEST_2 after log switch
Thread 1 advanced to log sequence 11 (LGWR switch)
Current log# 2 seq# 11 mem# 0: /disk1/oradata/sh/redo02a.log
Current log# 2 seq# 11 mem# 1: /disk2/oradata/sh/redo02b.log
Archived Log entry 12 added for thread 1 sequence 10>
Completed:> 2、主备库 switchover
再次查看主库状态：
15:31:12 SYS@ prod>select name,database_role,switchover_status from v$database;
NAME DATABASE_ROLE SWITCHOVER_STATUS
--------- ---------------- --------------------
PROD PRIMARY TO LOGICAL STANDBY
Elapsed: 00:00:00.01
主库切换到备库：
15:33:54 SYS@ prod>alter database commit to switchover to logical standby;
Database> Elapsed: 00:00:25.46
15:36:02 SYS@ prod>
告警日志：
alter database commit to switchover to logical standby
ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY (prod)
LOGSTDBY: Ensuring no active hot backups.
LOGSTDBY: Enabling database guard to prevent new transactions.
LOGSTDBY: Waiting for transactions in flight at scn 0x0000.000e76d5 to complete
Waiting for transactions in flight at scn 0x0000.000e76d5 to complete
Fri Apr 11 15:35:37 2014
Thread 1 advanced to log sequence 91 (LGWR switch)
Current log# 2 seq# 91 mem# 0: /disk1/oradata/prod/redo02a.log
Current log# 2 seq# 91 mem# 1: /disk2/oradata/prod/redo02b.log
Archived Log entry 148 added for thread 1 sequence 90> LOGSTDBY: Waiting for pending archivals to dest .
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Waiting for all FAL entries to be archived...
All FAL entries have been archived.
Waiting for potential switchover target to become synchronized...
Fri Apr 11 15:35:57 2014
Active, synchronized Logical Standbyswitchover target has been> LOGSTDBY: Preparing the COMMIT TO SWITCHOVER TO LOGICAL STANDBY DDL at scn .
ARCH: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2
Fri Apr 11 15:35:57 2014
Thread 1 advanced to log sequence 92 (LGWR switch)
Current log# 3 seq# 92 mem# 0: /disk1/oradata/prod/redo03a.log
Current log# 3 seq# 92 mem# 1: /disk2/oradata/prod/redo03b.log
ARCH: Standby redo logfile selected for thread 1 sequence 91 for destination LOG_ARCHIVE_DEST_2
Archived Log entry 149 added for thread 1 sequence 91> LOG_ARCHIVE_DEST_2 is a potential Logical Standbyswitchover target
Thread 1 cannot allocate new log, sequence 93
Checkpoint not complete
Current log# 3 seq# 92 mem# 0: /disk1/oradata/prod/redo03a.log
Current log# 3 seq# 92 mem# 1: /disk2/oradata/prod/redo03b.log
Fri Apr 11 15:35:59 2014
NSA2 started with pid=23, OS> Thread 1 advanced to log sequence 93 (LGWR switch)
Current log# 1 seq# 93 mem# 0: /disk1/oradata/prod/redo01a.log
Current log# 1 seq# 93 mem# 1: /disk2/oradata/prod/redo01b.log
Archived Log entry 151 added for thread 1 sequence 92> LOGSTDBY: Switchover complete (prod)
LOGSTDBY: enabling scheduler job queue processes.
JOBQ: re-enabling CJQ0
Completed:> 备库日志：
LOGMINER: End mining logfile for session 1 thread 1 sequence 90, /disk1/oradata/sh/std_redo02a.log
Fri Apr 11 15:36:50 2014
RFS LogMiner: Registered logfile to LogMiner session> Fri Apr 11 15:36:53 2014
RFS: Assigned to RFS process 4142
RFS:>
RFS LogMiner: RFS>
RFS LogMiner: RFS> Fri Apr 11 15:37:03 2014
RFS: Assigned to RFS process 4146
RFS:> RFS: Selected log 4 for thread 1 sequence 91 dbid 242030492 branch 830192161
Fri Apr 11 15:37:03 2014
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 91, /disk1/oradata/sh/std_redo01a.log
LOGMINER: End mining logfile for session 1 thread 1 sequence 91, /disk1/oradata/sh/std_redo01a.log
Fri Apr 11 15:37:03 2014
RFS LogMiner: Registered logfile to LogMiner session> Fri Apr 11 15:37:03 2014
LOGSTDBY Analyzer process AS00 server> Fri Apr 11 15:37:03 2014
LOGSTDBY Apply process AS01 server> Fri Apr 11 15:37:03 2014
LOGSTDBY Apply process AS02 server> Fri Apr 11 15:37:03 2014
LOGSTDBY Apply process AS03 server> Fri Apr 11 15:37:03 2014
LOGSTDBY Apply process AS04 server> Fri Apr 11 15:37:03 2014
LOGSTDBY Apply process AS05 server>
LOGMINER: session#=1, reader MS00 pid=37 OS> Fri Apr 11 15:37:03 2014
LOGMINER: session#=1, builder MS01 pid=38 OS> Fri Apr 11 15:37:03 2014
LOGMINER: session#=1, preparer MS02 pid=39 OS> Fri Apr 11 15:37:03 2014
LOGSTDBY status: ORA-16257: Switchover initiated stop apply successfully completed
Fri Apr 11 15:37:03 2014
RFS: Assigned to RFS process 4148
RFS:>
RFS LogMiner: RFS>
RFS LogMiner: RFS> 主库切换完成！
切换备库到主库：
15:33:45 SYS@ sh>select name,database_role,switchover_status from v$database;
NAME DATABASE_ROLE SWITCHOVER_STATUS
--------- ---------------- --------------------
SHDB LOGICAL STANDBYTO PRIMARY
15:38:16 SYS@ sh>alter database commit to switchover to primary;
Database> Elapsed: 00:00:15.95
切换完成：
15:39:54 SYS@ sh>select name,database_role,switchover_status from v$database;
NAME DATABASE_ROLE SWITCHOVER_STATUS
--------- ---------------- --------------------
SHDB PRIMARY LOG SWITCH GAP
Elapsed: 00:00:00.00
15:40:02 SYS@ sh>
备库日志：
alter database commit to switchover to primary
ALTER DATABASE SWITCHOVER TO PRIMARY (sh)
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY (sh)
LOGSTDBY: Starting SCN of new stream from seeded lockdown
LOGSTDBY: Successful close of the current log stream:
LOGSTDBY: primary:
LOGSTDBY: first scn:
LOGSTDBY: end scn:
LOGSTDBY: processed scn:
LOGSTDBY: Preparing the COMMIT TO SWITCHOVER TO PRIMARY DDL at scn .
ARCH: LGWR is scheduled to archive destination LOG_ARCHIVE_DEST_2 after log switch
Fri Apr 11 15:39:38 2014
ARC2: STARTING ARCH PROCESSES
Fri Apr 11 15:39:38 2014
ARC3 started with pid=25, OS> ARC3: Archival started
ARC2: STARTING ARCH PROCESSES COMPLETE
Fri Apr 11 15:39:38 2014
Thread 1 advanced to log sequence 12 (LGWR switch)
Current log# 3 seq# 12 mem# 0: /disk1/oradata/sh/redo03a.log
Current log# 3 seq# 12 mem# 1: /disk2/oradata/sh/redo03b.log
ARCH: Standby redo logfile selected for thread 1 sequence 11 for destination LOG_ARCHIVE_DEST_2
Fri Apr 11 15:39:54 2014
Archived Log entry 14 added for thread 1 sequence 11> LOGSTDBY: enabling scheduler job queue processes.
JOBQ: re-enabling CJQ0
Completed:> 主库日志：
RFS: Assigned to RFS process 3819
RFS:> RFS: Selected log 4 for thread 1 sequence 11 dbid 1674782321 branch 843241928
Fri Apr 11 15:38:38 2014
RFS LogMiner: Registered logfile to LogMiner session> 15:40:01 SYS@ prod>alter database start logical standby apply immediate;
Database> Elapsed: 00:00:00.30
alter database start logical standby apply immediate
ALTER DATABASE START LOGICAL STANDBY APPLY (prod)
with optional part
IMMEDIATE
Attempt to start background Logical Standby process
Fri Apr 11 15:41:21 2014
LSP0 started with pid=29, OS>
Completed:> LOGMINER: Parameters summary for session# = 1
LOGMINER: Number of processes = 3, Transaction Chunk>
LOGMINER: Memory> LOGMINER: SpillScn 0, ResetLogScn 939573
LOGMINER: summary for session# = 1
LOGMINER: StartScn: 968908 (0x0000.000ec8cc)
LOGMINER: EndScn: 0 (0x0000.00000000)
LOGMINER: HighConsumedScn: 0 (0x0000.00000000)
LOGMINER: session_flag 0x1
Fri Apr 11 15:41:23 2014
LOGMINER: session#=1, reader MS00 pid=32 OS> Fri Apr 11 15:41:23 2014
LOGMINER: session#=1, builder MS01 pid=33 OS> Fri Apr 11 15:41:23 2014
LOGMINER: session#=1, preparer MS02 pid=35 OS> LOGMINER: Turning ON Log Auto Delete
LOGMINER: Begin mining logfile during dictionary load for session 1 thread 1 sequence 9, /disk2/arch_prod/arch_1_9_843241928.log
原备库：
15:43:24 SYS@ sh>alter system switch logfile;
System> Thread 1 advanced to log sequence 12 (LGWR switch)
Current log# 3 seq# 12 mem# 0: /disk1/oradata/sh/redo03a.log
Current log# 3 seq# 12 mem# 1: /disk2/oradata/sh/redo03b.log
告警日志：
ARCH: Standby redo logfile selected for thread 1 sequence 11 for destination LOG_ARCHIVE_DEST_2
Fri Apr 11 15:39:54 2014
Archived Log entry 14 added for thread 1 sequence 11> LOGSTDBY: enabling scheduler job queue processes.
JOBQ: re-enabling CJQ0
Completed:> Fri Apr 11 15:40:23 2014
Shutting down archive processes
Fri Apr 11 15:40:23 2014
ARCH shutting down
ARC3: Archival stopped
Fri Apr 11 15:43:44 2014
Thread 1 advanced to log sequence 13 (LGWR switch)
Current log# 1 seq# 13 mem# 0: /disk1/oradata/sh/redo01a.log
Current log# 1 seq# 13 mem# 1: /disk2/oradata/sh/redo01b.log
Fri Apr 11 15:43:44 2014
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
Fri Apr 11 15:43:45 2014
Archived Log entry 16 added for thread 1 sequence 12> Thread 1 cannot allocate new log, sequence 14
Checkpoint not complete
Current log# 1 seq# 13 mem# 0: /disk1/oradata/sh/redo01a.log
Current log# 1 seq# 13 mem# 1: /disk2/oradata/sh/redo01b.log
Thread 1 advanced to log sequence 14 (LGWR switch)
Current log# 2 seq# 14 mem# 0: /disk1/oradata/sh/redo02a.log
Current log# 2 seq# 14 mem# 1: /disk2/oradata/sh/redo02b.log
Archived Log entry 17 added for thread 1 sequence 13> Fri Apr 11 15:43:59 2014
LNS: Standby redo logfile selected for thread 1 sequence 13 for destination LOG_ARCHIVE_DEST_2
LNS: Standby redo logfile selected for thread 1 sequence 14 for destination LOG_ARCHIVE_DEST_2
Fri Apr 11 15:44:06 2014
ARC2: Standby redo logfile selected for thread 1 sequence 12 for destination LOG_ARCHIVE_DEST_2
原主库日志：
Fri Apr 11 15:42:17 2014
Thread 1 advanced to log sequence 94 (LGWR switch)
Current log# 2 seq# 94 mem# 0: /disk1/oradata/prod/redo02a.log
Current log# 2 seq# 94 mem# 1: /disk2/oradata/prod/redo02b.log
Fri Apr 11 15:42:37 2014
Archived Log entry 152 added for thread 1 sequence 93> Fri Apr 11 15:42:50 2014
AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail
Fri Apr 11 15:42:50 2014
AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail
RFS: Assigned to RFS process 3840
RFS:> Primary database is in MAXIMUM PERFORMANCE mode
RFS: Selected log 4 for thread 1 sequence 13 dbid 1674782321 branch 843241928
RFS: Selected log 5 for thread 1 sequence 14 dbid 1674782321 branch 843241928
Fri Apr 11 15:42:58 2014
AUD: Audit Commit Delay exceeded, written a copy to OS Audit Trail
RFS: Assigned to RFS process 3842
RFS:> RFS: Selected log 6 for thread 1 sequence 12 dbid 1674782321 branch 843241928
Fri Apr 11 15:43:04 2014
RFS LogMiner: Registered logfile to LogMiner session> Fri Apr 11 15:43:21 2014
RFS LogMiner: Registered logfile to LogMiner session> Fri Apr 11 15:43:30 2014
RFS: Assigned to RFS process 3845
RFS:>
RFS LogMiner: RFS>
RFS LogMiner: RFS> Fri Apr 11 15:44:01 2014
Thread 1 advanced to log sequence 95 (LGWR switch)
Current log# 3 seq# 95 mem# 0: /disk1/oradata/prod/redo03a.log
Current log# 3 seq# 95 mem# 1: /disk2/oradata/prod/redo03b.log
3、验证主备库同步
在原备库做DML操作：
15:43:46 SYS@ sh>select count(*) from scott.emp1;
COUNT(*)
----------
56
Elapsed: 00:00:00.10
15:46:03 SYS@ sh>insert into scott.emp1 select * from scott.emp where rownum commit;
Commit complete.
Elapsed: 00:00:00.00
15:46:23 SYS@ sh>select count(*) from scott.emp1;
COUNT(*)
----------
60
Elapsed: 00:00:00.00
15:46:28 SYS@ sh>alter system switch logfile;
System> Elapsed: 00:00:02.39
15:46:53 SYS@ sh>
Fri Apr 11 15:46:50 2014
告警日志：
Thread 1 cannot allocate new log, sequence 15
Checkpoint not complete
Current log# 2 seq# 14 mem# 0: /disk1/oradata/sh/redo02a.log
Current log# 2 seq# 14 mem# 1: /disk2/oradata/sh/redo02b.log
Thread 1 advanced to log sequence 15 (LGWR switch)
Current log# 3 seq# 15 mem# 0: /disk1/oradata/sh/redo03a.log
Current log# 3 seq# 15 mem# 1: /disk2/oradata/sh/redo03b.log
Fri Apr 11 15:46:53 2014
Archived Log entry 20 added for thread 1 sequence 14> Fri Apr 11 15:46:53 2014
LNS: Standby redo logfile selected for thread 1 sequence 15 for destination LOG_ARCHIVE_DEST_2
原主库查看：
15:41:21 SYS@ prod>select count(*) from scott.emp1;
COUNT(*)
----------
60
告警日志：
Fri Apr 11 15:45:47 2014
RFS: Selected log 4 for thread 1 sequence 15 dbid 1674782321 branch 843241928
Fri Apr 11 15:45:49 2014
RFS LogMiner: Registered logfile to LogMiner session> Fri Apr 11 15:45:49 2014
LOGMINER:> LOGMINER: End mining logfile for session 1 thread 1 sequence 14, /disk2/arch_prod/arch_1_14_843241928.log
LOGMINER: Begin mining logfile for session 1 thread 1 sequence 15, /disk1/oradata/prod/std_redo01a.log
@至此，主备库Switchover 成功！
页:
[1]