ITPub博客

首页 > 数据库 > Oracle > dataguard之物理standby库failover 切换

dataguard之物理standby库failover 切换

原创 Oracle 作者:sxitsxit 时间:2014-03-20 21:58:17 0 删除 编辑

第一步、查询两个库的状态

在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: oraclepdg@rac1

*** 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: oracle@rac1 (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/24862808/viewspace-1126280/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-04-14

  • 博文量
    98
  • 访问量
    300755