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