Oracle DG 之逻辑备库--Switchover

死siua11

　　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[1]: Assigned to RFS process 3776

　　RFS[1]:>　　RFS[1]: Opened log for thread 1 sequence 8 dbid 1674782321 branch 843241928
　　Fri Apr 11 15:32:15 2014
　　RFS[2]: Assigned to RFS process 3781

　　RFS[2]:>
　　RFS LogMiner: RFS>　　Fri Apr 11 15:32:21 2014
　　RFS[3]: Assigned to RFS process 3783

　　RFS[3]:>　　RFS[3]: Opened log for thread 1 sequence 9 dbid 1674782321 branch 843241928
　　Fri Apr 11 15:32:34 2014

　　RFS LogMiner: Registered logfile [/disk2/arch_prod/arch_1_9_843241928.log] to LogMiner session>　　Fri Apr 11 15:32:39 2014
　　RFS[4]: Assigned to RFS process 3790

　　RFS[4]:>　　RFS[4]: Opened log for thread 1 sequence 10 dbid 1674782321 branch 843241928

　　RFS LogMiner: Registered logfile [/disk2/arch_prod/arch_1_10_843241928.log] 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 STANDBY  PREPARING 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 [all].
　　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 Standby  switchover target has been>　　LOGSTDBY: Preparing the COMMIT TO SWITCHOVER TO LOGICAL STANDBY DDL at scn [947944].
　　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 Standby  switchover 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 [/disk2/arch_sh/arch_1_90_830192161.log] to LogMiner session>　　Fri Apr 11 15:36:53 2014
　　RFS[6]: Assigned to RFS process 4142

　　RFS[6]:>
　　RFS LogMiner: RFS>
　　RFS LogMiner: RFS>　　Fri Apr 11 15:37:03 2014
　　RFS[7]: Assigned to RFS process 4146

　　RFS[7]:>　　RFS[7]: 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 [/disk2/arch_sh/arch_1_91_830192161.log] 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[8]: Assigned to RFS process 4148

　　RFS[8]:>
　　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 STANDBY  TO 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 [0x0000.000ec8cc]
　　LOGSTDBY: Successful close of the current log stream:
　　LOGSTDBY:   primary:       [242030492]
　　LOGSTDBY:   first scn:     [0x0000.00000000]
　　LOGSTDBY:   end scn:       [0x0000.000e76ec]
　　LOGSTDBY:   processed scn: [0x0000.000e76ed]
　　LOGSTDBY: Preparing the COMMIT TO SWITCHOVER TO PRIMARY DDL at scn [970802].
　　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[5]: Assigned to RFS process 3819

　　RFS[5]:>　　RFS[5]: Selected log 4 for thread 1 sequence 11 dbid 1674782321 branch 843241928
　　Fri Apr 11 15:38:38 2014

　　RFS LogMiner: Registered logfile [/disk2/arch_prod/arch_1_11_843241928.log] 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[6]: Assigned to RFS process 3840

　　RFS[6]:>　　Primary database is in MAXIMUM PERFORMANCE mode
　　RFS[6]: Selected log 4 for thread 1 sequence 13 dbid 1674782321 branch 843241928
　　RFS[6]: 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[7]: Assigned to RFS process 3842

　　RFS[7]:>　　RFS[7]: Selected log 6 for thread 1 sequence 12 dbid 1674782321 branch 843241928
　　Fri Apr 11 15:43:04 2014

　　RFS LogMiner: Registered logfile [/disk2/arch_prod/arch_1_13_843241928.log] to LogMiner session>　　Fri Apr 11 15:43:21 2014

　　RFS LogMiner: Registered logfile [/disk2/arch_prod/arch_1_12_843241928.log] to LogMiner session>　　Fri Apr 11 15:43:30 2014
　　RFS[8]: Assigned to RFS process 3845

　　RFS[8]:>
　　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[6]: Selected log 4 for thread 1 sequence 15 dbid 1674782321 branch 843241928
　　Fri Apr 11 15:45:49 2014

　　RFS LogMiner: Registered logfile [/disk2/arch_prod/arch_1_14_843241928.log] 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 成功！