dataguard之物理standby庫failover 切換
第一步、查詢兩個庫的狀態
在primary庫上執行查詢
SQL> set linesize 200;
SQL> select name,open_mode,database_role,switchover_status from v$database;
NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
------------------ -------------------- -----------------------
PDG READ WRITE PRIMARY TO STANDBY
SQL> select instance_name from v$instance;
INSTANCE_NAME
--------------------------------
pdg
在standby 庫上查詢
SQL> set linesize 200;
SQL> select name,open_mode,database_role,switchover_status from v$database;
NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
------------------ -------------------- --------------------------
PDG MOUNTED PHYSICAL STANDBY NOT ALLOWED
SQL> select instance_name from v$instance;
INSTANCE_NAME
--------------------------------
sdg
第二步、在primary庫上執行切換
primay庫上執行切換
SQL> alter database commit to switchover to physical standby;
Database altered.
在執行切換的過程正式開始之前，當前的控制檔案被備份到當前sql session trace file中
在udump中找到相關的trc檔案
然後在standby庫上檢視狀態
SQL> select name,open_mode,database_role,switchover_status from v$database;
NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
------------------ -------------------- -----------------------
PDG MOUNTED PHYSICAL STANDBY TO PRIMARY
SWITCHOVER_STATUS 的狀態已經變為了 TO PRIMARY
第三步、關閉原primary庫，並啟動到mount狀態
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL>
SQL> startup mount;
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1218316 bytes
Variable Size 67111156 bytes
Database Buffers 96468992 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL>
第四步、再次檢查原standby庫上的狀態
SQL> select name,open_mode,database_role,switchover_status from v$database;
NAME OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS
------------------ -------------------- ---------------------
PDG MOUNTED PHYSICAL STANDBY TO PRIMARY
第五步、將原來的standby庫切進行切換
原standby庫上執行
SQL> alter database commit to switchover to primary;
Database altered.
第六步、啟動原standby庫
SQL> alter database open;
Database altered.
第七步、驗證log 是否切換正常
在新primary庫上執行
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
14
在新standby庫上執行
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
14
然後在新primary庫上執行
SQL> alter system switch logfile;
System altered.
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
15
SQL> select sequence#,first_time,next_time from v$archived_log order by sequence#;
SEQUENCE# FIRST_TIME NEXT_TIME
---------- ------------ ------------
2 16-MAR-14 16-MAR-14
3 16-MAR-14 16-MAR-14
4 16-MAR-14 16-MAR-14
5 16-MAR-14 17-MAR-14
6 17-MAR-14 17-MAR-14
7 17-MAR-14 17-MAR-14
8 17-MAR-14 17-MAR-14
9 17-MAR-14 20-MAR-14
10 20-MAR-14 20-MAR-14
11 20-MAR-14 20-MAR-14
12 20-MAR-14 20-MAR-14
SEQUENCE# FIRST_TIME NEXT_TIME
---------- ------------ ------------
13 20-MAR-14 20-MAR-14
14 20-MAR-14 20-MAR-14
14 20-MAR-14 20-MAR-14
15 20-MAR-14 20-MAR-14
15 20-MAR-14 20-MAR-14
16 rows selected.
再在新standby庫上執行
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
15
SQL> select sequence#,first_time,next_time from v$archived_log order by sequence#;
SEQUENCE# FIRST_TIME NEXT_TIME
---------- ------------ ------------
2 16-MAR-14 16-MAR-14
2 16-MAR-14 16-MAR-14
3 16-MAR-14 16-MAR-14
3 16-MAR-14 16-MAR-14
4 16-MAR-14 16-MAR-14
4 16-MAR-14 16-MAR-14
5 16-MAR-14 17-MAR-14
5 16-MAR-14 17-MAR-14
6 17-MAR-14 17-MAR-14
6 17-MAR-14 17-MAR-14
7 17-MAR-14 17-MAR-14
SEQUENCE# FIRST_TIME NEXT_TIME
---------- ------------ ------------
7 17-MAR-14 17-MAR-14
8 17-MAR-14 17-MAR-14
8 17-MAR-14 17-MAR-14
9 17-MAR-14 20-MAR-14
9 17-MAR-14 20-MAR-14
10 20-MAR-14 20-MAR-14
10 20-MAR-14 20-MAR-14
11 20-MAR-14 20-MAR-14
11 20-MAR-14 20-MAR-14
12 20-MAR-14 20-MAR-14
12 20-MAR-14 20-MAR-14
SEQUENCE# FIRST_TIME NEXT_TIME
---------- ------------ ------------
13 20-MAR-14 20-MAR-14
13 20-MAR-14 20-MAR-14
14 20-MAR-14 20-MAR-14
15 20-MAR-14 20-MAR-14
26 rows selected.
由此可以看到 switchover 切換正常
+++++++++++++++++++++++++++++++++++++++++++
在primary庫切換之前，控制檔案會被寫入到使用者的trace檔案中，如下：
[oracle@rac1 udump]$ more pdg_fal_7015.trc
/home/oracle/admin/pdg/udump/pdg_fal_7015.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /home/oracle/10.2.0
System name: Linux
Node name: rac1
Release: 2.6.9-78.ELsmp
Version: #1 SMP Wed Jul 9 15:39:47 EDT 2008
Machine: i686
Instance name: pdg
Redo thread mounted by this instance: 1
Oracle process number: 22
Unix process pid: 7015, image:
*** SERVICE NAME:(pdg) 2014-03-20 20:35:38.135
*** SESSION ID:(151.21) 2014-03-20 20:35:38.135
FAL Redo Shipping Client Did Not Establish Network Login
[oracle@rac1 udump]$
[oracle@rac1 udump]$ more pdg_ora_7003.trc
/home/oracle/admin/pdg/udump/pdg_ora_7003.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /home/oracle/10.2.0
System name: Linux
Node name: rac1
Release: 2.6.9-78.ELsmp
Version: #1 SMP Wed Jul 9 15:39:47 EDT 2008
Machine: i686
Instance name: pdg
Redo thread mounted by this instance: 1
Oracle process number: 15
Unix process pid: 7003, image: (TNS V1-V3)
*** 2014-03-20 20:45:10.487
*** SERVICE NAME:(SYS$USERS) 2014-03-20 20:45:10.482
*** SESSION ID:(159.5) 2014-03-20 20:45:10.482
ARCH: Connecting to console port...
*** 2014-03-20 20:45:10.503 12878 dbsdrv.c
Waiting for all non-current ORLs to be archived...
*** 2014-03-20 20:45:10.504 12927 dbsdrv.c
All non-current ORLs have been archived.
*** 2014-03-20 20:45:10.504 12938 dbsdrv.c
Waiting for FAL entries to be archived...
*** 2014-03-20 20:45:10.505 12969 dbsdrv.c
All FAL entries have been archived.
*** 2014-03-20 20:45:10.505 12980 dbsdrv.c
Waiting for dest_id 2 to become synchronized...
*** 2014-03-20 20:45:10.505 12995 dbsdrv.c
Destination 2 is synchronized
*** 2014-03-20 20:45:10.507 52565 kcrr.c
Checking if any ASYNC LNS processes need to be terminated..
Shutting immediately previously started LNS1 [pid 7013] mode ASYNC
... EOR ARCH archival in progress
NOSWITCH archival processing for any existing Async LNS processes
Redo shipping client performing standby login
*** 2014-03-20 20:45:14.671 64561 kcrr.c
Logged on to standby successfully
Client logon and security negotiation successful!
Destination LOG_ARCHIVE_DEST_2 is in MAXIMUM PERFORMANCE mode
Checking if any ASYNC LNS processes need to be started..
Forcing a log switch to start ASYNC LNS processes [kcrr.c:64309]
*** 2014-03-20 20:45:30.043
... NOSWITCH ARCH archival in progress
... EOR ARCH archival in progress
... FINAL ARCH archival in progress
NOSWITCH archival processing for any existing Async LNS processes
Redo shipping client performing standby login
*** 2014-03-20 20:45:30.110 64561 kcrr.c
Logged on to standby successfully
Client logon and security negotiation successful!
Destination LOG_ARCHIVE_DEST_2 is in MAXIMUM PERFORMANCE mode
*** 2014-03-20 20:45:30.157 9155 kcrr.c
*** 2014-03-20 20:45:30.158 9213 kcrr.c
ARCH: Setting End-Of-Redo flag
*** 2014-03-20 20:45:30.246 2520 kcrf.c
tkcrf_clear_srl: Started clearing Standby Redo Logs
*** 2014-03-20 20:45:30.257 2828 kcrf.c
tkcrf_clear_srl: Completed clearing Standby Redo Logs
*** 2014-03-20 20:45:30.257
-- The following are current System-scope REDO Log Archival related
-- parameters and can be included in the database initialization file.
--
-- LOG_ARCHIVE_DEST=''
-- LOG_ARCHIVE_DUPLEX_DEST=''
--
-- LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
--
-- DB_UNIQUE_NAME="PDG"
--
-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE'
-- LOG_ARCHIVE_CONFIG='DG_CONFIG=("sdg")'
-- LOG_ARCHIVE_MAX_PROCESSES=2
-- STANDBY_FILE_MANAGEMENT=AUTO
-- STANDBY_ARCHIVE_DEST=?/dbs/arch
-- FAL_CLIENT=pdg
-- FAL_SERVER=sdg
--
-- LOG_ARCHIVE_DEST_2='SERVICE=sdg'
-- LOG_ARCHIVE_DEST_2='OPTIONAL REOPEN=300 NODELAY'
-- LOG_ARCHIVE_DEST_2='ARCH NOAFFIRM NOEXPEDITE NOVERIFY ASYNC=61440'
-- LOG_ARCHIVE_DEST_2='REGISTER NOALTERNATE NODEPENDENCY'
-- LOG_ARCHIVE_DEST_2='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED'
-- LOG_ARCHIVE_DEST_2='DB_UNIQUE_NAME=sdg'
-- LOG_ARCHIVE_DEST_2='VALID_FOR=(STANDBY_LOGFILE,STANDBY_ROLE)'
-- LOG_ARCHIVE_DEST_STATE_2=ENABLE
--
-- LOG_ARCHIVE_DEST_1='LOCATION=/home/oracle/archivelog'
-- LOG_ARCHIVE_DEST_1='OPTIONAL REOPEN=300 NODELAY'
-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_1='REGISTER NOALTERNATE NODEPENDENCY'
-- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED'
-- LOG_ARCHIVE_DEST_1='DB_UNIQUE_NAME=pdg'
-- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
-- LOG_ARCHIVE_DEST_STATE_1=ENABLE
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "PDG" NORESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/home/oracle/oradata/pdg/redo01.log' SIZE 50M,
GROUP 2 '/home/oracle/oradata/pdg/redo02.log' SIZE 50M,
GROUP 3 '/home/oracle/oradata/pdg/redo03.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/home/oracle/oradata/pdg/system01.dbf',
'/home/oracle/oradata/pdg/undotbs01.dbf',
'/home/oracle/oradata/pdg/sysaux01.dbf',
'/home/oracle/oradata/pdg/users01.dbf',
'/home/oracle/oradata/pdg/example01.dbf'
CHARACTER SET WE8ISO8859P1
;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/home/oracle/archivelog/log1_1_562360180.arc';
-- ALTER DATABASE REGISTER LOGFILE '/home/oracle/archivelog/log1_1_842395447.arc';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE
-- All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
-- Database can now be opened normally.
ALTER DATABASE OPEN;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/home/oracle/oradata/pdg/temp01.dbf' REUSE;
-- End of tempfile additions.
--
*** 2014-03-20 20:45:30.266
*** 2014-03-20 20:45:30.288 1011 krsm.c
Managed Recovery: NODELAY posted.
*** 2014-03-20 20:45:30.289 1708 krsm.c
Managed Recovery: TIMEOUT 3 minutes posted.
*** 2014-03-20 20:45:30.289 1011 krsm.c
Managed Recovery: DISCONNECT posted.
*** 2014-03-20 20:45:30.290 1011 krsm.c
Managed Recovery: Startup posted.
ARCH: Connecting to console port...
+++++++++++++++++++++++++++++++++++++
參考文件：
Oracle? Data Guard Concepts and Administration 10g Release 2 (10.2) Part Number B14239-05
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28373936/viewspace-1722273/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- dataguard之物理standby 日誌切換
- DataGuard---->物理StandBy的角色切換之switchover
- 物理Standby角色切換作業failoverAI
- DATA GUARD物理STANDBY的FAILOVER切換AI
- 【DATAGUARD】物理dg的failover切換(六)AI
- oracle 之dataguard standby 切換Oracle
- 【DataGuard】物理Data Guard之Failover轉換AI
- Oracle 10g DataGuard物理主備切換-switchover與failoverOracle 10gAI
- Dataguard failover切換實驗AI
- Dataguard物理Standby Switchover 角色轉換
- 【DataGuard】10g物理standby主備switchover方式切換詳述
- 盛哥學習 Data Guard 第四篇《物理standby之failover 丟棄切換》AI
- DataGuard:Physical Standby FailoverAI
- 【DATAGUARD】Oracle Dataguard物理備庫切換最佳實踐(sqlplus)OracleSQL
- RAC環境STANDBY的FAILOVER切換AI
- DATA GUARD物理STANDBY的 SWITCHOVER切換
- 【DATAGUARD】物理dg的switchover切換（五）
- DataGuard:Logical Standby FailoverAI
- 【DATAGUARD】 將11g物理備庫轉換為Snapshot Standby
- DataGuard搭建物理StandBy
- DATA GUARD物理STANDBY的 SWITCHOVER切換[zt]
- Oracle DataGuard環境failover後通過舊備份建立物理StandbyOracleAI
- RAC環境LOGICAL STANDBY的FAILOVER切換AI
- oracle 之dataguard主庫系統崩潰之物理備庫切主庫Oracle
- DG物理standby，failover步驟AI
- DG物理standby，Failover之後原primary重回DGAI
- 【DATAGUARD】物理dg配置客戶端無縫切換 (八.2)--Fast-Start Failover 的配置客戶端ASTAI
- RAC環境的物理STANDBY的 SWITCHOVER切換
- Oracle物理DG自動切換——Dataguard Broker配置Oracle
- 【DATAGUARD 學習】使用duplicate 建立物理standby 資料庫資料庫
- Oracle10G Dataguard 多個備庫 - 主庫和物理備庫的切換Oracle
- ORACLE 11G DataGuard Failover後如何修復standby庫OracleAI
- 【DataGuard】Oracle 11g物理Data Guard之Snapshot Standby資料庫功能Oracle資料庫
- 邏輯 rac standby和物理 rac standby的switchover 和 failoverAI
- DataGuard主備庫切換步驟
- 【轉】【DataGuard】Oracle 11g物理Data Guard之Snapshot Standby資料庫功能Oracle資料庫
- 物理dataguard 正常切換 角色轉換，switchover_status 狀態改變
- 物理DG角色轉換: failoverAI