failover之前一直没有做,现在终于完整地测试了一遍。这个主要是记得打开闪回功能,确保最后的reinstate顺利完成。
就这样就能在failover之后顺利地把原主库恢复为备库。
----failover测试:
----2.2SBDB作为主库:
SQL> select open_mode,protection_mode,switchover_status,database_role from v$database;
OPEN_MODE PROTECTION_MODE SWITCHOVER_STATUS DATABASE_ROLE
-------------------- -------------------- -------------------- ----------------
READ WRITE MAXIMUM PERFORMANCE SESSIONS ACTIVE PRIMARY
----2.4ORA11GR2作为备库:
SQL> select open_mode,protection_mode,switchover_status,database_role from v$database;
OPEN_MODE PROTECTION_MODE SWITCHOVER_STATUS DATABASE_ROLE
-------------------- -------------------- -------------------- ----------------
READ ONLY WITH APPLY MAXIMUM PERFORMANCE NOT ALLOWED PHYSICAL STANDBY
----开启主库的flashback功能:
SQL> alter database flashback on;
Database altered.
#已经开启。
----主库模拟故障不正常关库:
--在不正常关库前可以查看主备库的最大日志号:
SQL> select max(sequence#), to_char(max(first_time),'yyyy-mm-dd hh24:mi:ss') from v$log_history where thread#=1
2 and RESETLOGS_TIME = (select max(a.RESETLOGS_TIME) from v$log_history a);
MAX(SEQUENCE#) TO_CHAR(MAX(FIRST_T
-------------- -------------------
210 2017-03-19 22:32:10
#经核对,主备库的日志号是相同的,则同步没有延迟。
----主库不正常关库模拟:
SQL> shutdown abort;
ORACLE instance shut down.
SQL> select status from v$instance;
select status from v$instance
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 26669
Session ID: 88 Serial number: 313
SQL> select open_mode,protection_mode,switchover_status,database_role from v$database;
select open_mode,protection_mode,switchover_status,database_role from v$database
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 26669
Session ID: 88 Serial number: 313
----查看备库此时的状态:
SQL> select open_mode,protection_mode,switchover_status,database_role from v$database;
OPEN_MODE PROTECTION_MODE SWITCHOVER_STATUS DATABASE_ROLE
-------------------- -------------------- -------------------- ----------------
READ ONLY WITH APPLY MAXIMUM PERFORMANCE NOT ALLOWED PHYSICAL STANDBY
#现在不是正常的switchover切换,所以备库依然显示切换状态为NOT ALLOWED 。
----备库登录DGbroker工具进行failover切换:
[oracle@oracle ~]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/skysky
Connected.
DGMGRL> show configuration;
Configuration - SBDB
Protection Mode: MaxPerformance
Databases:
SBDB - Primary database
ORA11GR2 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
ORA-01034: ORACLE not available
ORA-16625: cannot reach database "SBDB"
DGM-17017: unable to determine configuration status
----进行failover切换:
DGMGRL> failover to 'ORA11GR2';
Performing failover NOW, please wait...
Failover succeeded, new primary is "ORA11GR2"
DGMGRL>
#failover切换成功。
----切换成功后查看DGbroker配置文件:
DGMGRL> show configuration;
Configuration - SBDB
Protection Mode: MaxPerformance
Databases:
ORA11GR2 - Primary database
SBDB - Physical standby database (disabled)
ORA-16661: the standby database needs to be reinstated
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
---查看现在主库:
DGMGRL> show database 'ORA11GR2';
Database - ORA11GR2
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
ORA11GR2
Database Status:
SUCCESS
---查看原来主库:
DGMGRL> show database 'SBDB';
Database - SBDB
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: (unknown)
Apply Lag: (unknown)
Apply Rate: (unknown)
Real Time Query: OFF
Instance(s):
SBDB
Database Status:
ORA-16661: the standby database needs to be reinstated
----尝试查看failover后主库的状态:
SQL> select open_mode,protection_mode,switchover_status,database_role from v$database;
select open_mode,protection_mode,switchover_status,database_role from v$database
*
ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 1497
Session ID: 18 Serial number: 7
SQL> select status from v$instance;
ERROR:
ORA-03114: not connected to ORACLE
----重启新主库的实例:
SQL> select open_mode,protection_mode,switchover_status,database_role from v$database;
OPEN_MODE PROTECTION_MODE SWITCHOVER_STATUS DATABASE_ROLE
-------------------- -------------------- -------------------- ----------------
READ WRITE MAXIMUM PERFORMANCE NOT ALLOWED PRIMARY
#现在变成了可读写状态,则作为主库使用。
----一段时间后,尝试重启原来的主库:
[oracle@host01 ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Mon Mar 20 00:55:20 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter password:
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 830930944 bytes
Fixed Size 2257800 bytes
Variable Size 704646264 bytes
Database Buffers 121634816 bytes
Redo Buffers 2392064 bytes
Database mounted.
ORA-16649: possible failover to another database prevents this database from
being opened
----查看此时的状态:
SQL> select open_mode,protection_mode,switchover_status,database_role from v$database;
OPEN_MODE PROTECTION_MODE SWITCHOVER_STATUS DATABASE_ROLE
-------------------- -------------------- -------------------- ----------------
MOUNTED MAXIMUM PERFORMANCE NOT ALLOWED PRIMARY
----alert日志的记录:
Successful mount of redo thread 1, with mount id 255649302
Allocated 3981120 bytes in shared pool for flashback generation buffer
Starting background process RVWR
Mon Mar 20 00:55:55 2017
RVWR started with pid=21, OS id=26995
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE MOUNT
Mon Mar 20 00:55:56 2017
ALTER DATABASE OPEN
Data Guard Broker initializing...
Data Guard Broker initialization complete
Data Guard: verifying database primary role...
Starting Data Guard Broker (DMON)
Mon Mar 20 00:55:58 2017
INSV started with pid=22, OS id=27002
Mon Mar 20 00:56:01 2017
NSV1 started with pid=23, OS id=27006
Mon Mar 20 00:56:07 2017
Data Guard: version check completed
Data Guard determines a failover has occurred - this is no longer a primary database
ORA-16649 signalled during: ALTER DATABASE OPEN...
----恢复原来的主库为新备库:
----把原来的主库开启到mount状态:
[oracle@host01 ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Mon Mar 20 01:04:39 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 830930944 bytes
Fixed Size 2257800 bytes
Variable Size 704646264 bytes
Database Buffers 121634816 bytes
Redo Buffers 2392064 bytes
Database mounted.
SQL>
----使用DGbroker登录Reinstate 原来的主库(Reinstate the old primary database):
--新主库侧:
DGMGRL> REINSTATE DATABASE 'SBDB';
Reinstating database "SBDB", please wait...
Operation requires shutdown of instance "SBDB" on database "SBDB"
Shutting down instance "SBDB"...
Unable to connect to database
ORA-12545: Connect failed because target host or object does not exist
Failed.
Warning: You are no longer connected to ORACLE.
Please complete the following steps and reissue the REINSTATE command:
shut down instance "SBDB" of database "SBDB"
start up and mount instance "SBDB" of database "SBDB"
#这个虽然没有一步到位,但是只要跟着提示走,去重启SBDB实例就可以了。重启完之后,重新执行 REINSTATE DATABASE 'SBDB'
语句,就可以顺利地REINSTATE DATABASE,把原来的主库恢复起来了。重启过程和REINSTATE过程可以参考本文提示到的两段alert日志记录。
DGMGRL>
DGMGRL> REINSTATE DATABASE 'SBDB';
Reinstating database "SBDB", please wait...
Reinstatement of database "SBDB" succeeded
DGMGRL>
#REINSTATE DATABASE完成。
----查看参数文件:
DGMGRL> show configuration;
Configuration - SBDB
Protection Mode: MaxPerformance
Databases:
ORA11GR2 - Primary database
SBDB - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
DGMGRL>
----查看新备库:
DGMGRL> show database 'SBDB';
Database - SBDB
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Apply Rate: 196.00 KByte/s
Real Time Query: ON
Instance(s):
SBDB
Database Status:
SUCCESS
#已经恢复成功。
----查看备库的状态:
SQL> select open_mode,protection_mode,switchover_status,database_role from v$database;
OPEN_MODE PROTECTION_MODE SWITCHOVER_STATUS DATABASE_ROLE
-------------------- -------------------- -------------------- ----------------
READ ONLY WITH APPLY MAXIMUM PERFORMANCE SWITCHOVER PENDING PHYSICAL STANDBY
#已经成功切换成DG的新备库。
SQL> select max(sequence#), to_char(max(first_time),'yyyy-mm-dd hh24:mi:ss') from v$log_history where thread#=1
2 and RESETLOGS_TIME = (select max(a.RESETLOGS_TIME) from v$log_history a);
MAX(SEQUENCE#) TO_CHAR(MAX(FIRST_T
-------------- -------------------
6 2017-03-20 01:20:19
#新主备库的最大日志号相同。
--附加reinstate过程的日志:
Reinstate过程中的alert日志选段:
Using STANDBY_ARCHIVE_DEST parameter default value as /u01/app/arch/SBDB/
RFS[1]: Assigned to RFS process 27186
RFS[1]: Database mount ID mismatch [0xf3d3a60:0xf3ceb30] (255670880:255650608)
RFS[1]: Not using real application clusters
Mon Mar 20 01:14:26 2017
RSM0 started with pid=26, OS id=27188
FLASHBACK DATABASE TO SCN 3526040
Flashback Restore Start
Flashback Restore Complete
Flashback Media Recovery Start
Serial Media Recovery started
Recovery of Online Redo Log: Thread 1 Group 3 Seq 211 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/SBDB/redo03.log
Mem# 1: /u01/app/oracle/oradata/SBDB/redo06.log
Incomplete Recovery applied until change 3526041 time 03/20/2017 00:38:59
Flashback Media Recovery Complete
Completed: FLASHBACK DATABASE TO SCN 3526040
alter database convert to physical standby
ALTER DATABASE CONVERT TO PHYSICAL STANDBY (SBDB)
Mon Mar 20 01:14:36 2017
Flush standby redo logfile failed:1649
Clearing standby activation ID 255679734 (0xf3d5cf6)
The primary database controlfile was created using the
'MAXLOGFILES 16' clause.
There is space for up to 13 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800;
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Clearing online redo logfile 1 /u01/app/oracle/oradata/SBDB/redo01.log
Clearing online log 1 of thread 1 sequence number 209
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 /u01/app/oracle/oradata/SBDB/redo02.log
Clearing online log 2 of thread 1 sequence number 210
Mon Mar 20 01:14:49 2017
Clearing online redo logfile 2 complete
Clearing online redo logfile 3 /u01/app/oracle/oradata/SBDB/redo03.log
Clearing online log 3 of thread 1 sequence number 211
Clearing online redo logfile 3 complete
Completed: alter database convert to physical standby
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31392094/viewspace-2135704/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/31392094/viewspace-2135704/