合 物理DG的switchover和failover切换过程

 

0    320    1

Tags：

👉 本文共约53995个字，系统预计阅读时间或需203分钟。

物理dg的switchover切换

前言部分

导读

各位技术爱好者，看完本文后，你可以掌握如下的技能，也可以学到一些其它你所不知道的知识，~O(∩_∩)O~：

① 物理dg的switchover切换演练过程

② 物理dg管理和维护的一些sql

实验环境介绍

项目主库dg库
db 类型单实例单实例
db version11.2.0.311.2.0.3
db 存储FS typeFS type
ORACLE_SIDoradg11goradgphy
db_nameoradg11goradg11g
主机IP地址：192.168.59.130192.168.59.130
OS版本及kernel版本RHEL6.5 64位，2.6.32-504.16.2.el6.x86_64RHEL6.5 64位，2.6.32-504.16.2.el6.x86_64
OS hostnamerhel6_lhrrhel6_lhr

相关参考文章链接

【DATAGUARD】 基于同一个主机建立物理备库和逻辑备库（一）： http://blog.itpub.net/26736162/viewspace-1448197/
【DATAGUARD】 基于同一个主机建立物理备库和逻辑备库（二 ): http://blog.itpub.net/26736162/viewspace-1448207/
【DATAGUARD】 基于同一个主机建立物理备库和逻辑备库（三 ): http://blog.itpub.net/26736162/viewspace-1481972/
【DATAGUARD】 基于同一个主机建立物理备库和逻辑备库 (四)--添加一个物理dg节点 ：http://blog.itpub.net/26736162/viewspace-1484878/

本文简介

这段时间比较忙，一直没有更新blog了，看了大神们一直在坚持写blog，真心佩服。这几天一个客户要搭建物理dg，顺便演示switchover的切换，这里我整理了一下共享出来，希望对大家有用，本次实验的环境基于http://blog.itpub.net/26736162/viewspace-1448197/ 文章中搭建的环境，下一章中介绍failover的切换。

相关知识点扫盲

1．角色

Oracle 数据库中含有两种角色。

用户角色：定义了一组权限的集合，该角色可以分配给用户，也可以分配给其他角色。

数据库角色：在备库中数据库扮演什么样的角色，primary还是standby。

v$database.DATABASE_ROLE标识了数据库的运行角色。

处于备库环境中，数据库有两种类型：phsical standby、logical standby。

2．角色管理服务

一个数据库运行在如下互相排斥的角色中。

Primary role：一个数据库运行在primary role，那么log transport services传递重做日志到备库。

Standby role：一个数据库运行在standby role，那么log apply services应用归档日志到备库。

角色管理服务允许用户动态地在主、备库中进行角色切换。

用户可以使用角色管理服务，进行主、备库的计划中的角色切换，这个叫switchover，或者是非计划中的角色切换，叫failover。

3．Switchover&Failover

切换是在主数据库与其备数据库之间进行角色反转，切换确保不丢失数据。这是对于主系统计划维护的典型操作。在切换期间，主数据库转换到备角色，备数据库转换到主角色。转换发生不需要重建任何数据库。

（1）Switchover

用到的场景：计划中的角色转换或用户操作系统和硬件的维护等。

（2）Failover

故障转移是当主数据库不可用时执行的。故障转移只有在主数据库灾难故障的情况下执行，并且故障转移导致备数据库转换到主角色。用到的场景：非计划中的角色切换，一般在紧急情况下使用。根据保护模式的不同，可能会没有或者很少的数据损失。

（3）角色转换决策树

角色转换（switchover&failover）的最终目的是尽快地使主库在线，而同时尽量减少数据损失或者是实现无数据损失。尽量选择宕机时间最短，同时数据损失最小的策略。总之在失败切换前，应该先考虑修复主数据库或者进行无数据损失的角色转换。

即使使用无数据损失的备库方案，修复主库可能会比切换到备库更快点。如果修复了主库，那么就不需要修改客户端的连接。但是如果修复工作导致了任何的数据损失，那么可能需要重新创建所有的备用数据库。

通常情况下，最合适切换的备库为已经应用了最多的归档日志的备用数据库。

实验部分

实验目标

  1. 主库和物理dg的switchover切换
  2. 还原为最初始的状态

实验过程

主库oradg11g的参数设置检查

这里主要有2个内容需要检测：

  1. fal_client、fal_server、standby_file_management、db_file_name_convert 、log_filename_convert是否合理
  2. standbylog file 是否已经创建

[oracle@rhel6_lhr ~]$ echo $ORACLE_SID

oradg11g

[oracle@rhel6_lhr ~]$

[oracle@rhel6_lhr ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on 星期日 7月 26 17:33:53 2015

Copyright (c) 1982, 2011, Oracle. All rights reserved.

连接到:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

17:33:53 SQL> col name format a25

17:33:55 SQL> col VALUE format a20

17:33:55 SQL> SELECT a.NAME,a.VALUE

17:33:55 2 FROM v$parameter a

17:33:55 3 WHERE a.NAME LIKE '%file_name_convert'

17:33:55 4 OR a.NAME LIKE 'fal%'

17:33:55 5 OR a.NAME LIKE 'standby_file%';

NAME VALUE

------------------------- --------------------

db_file_name_convert oradgphy, oradg11g

log_file_name_convert oradgphy, oradg11g

fal_client oradg11g

fal_server oradgphy

standby_file_management AUTO

已用时间: 00: 00: 00.01

17:33:56 SQL>

20:32:09 SQL> set line 9999 pagesize 9999

20:36:47 SQL> col db_id format a15

20:36:47 SQL> select GROUP#, DBID db_id, THREAD#, SEQUENCE#, BYTES, USED, ARCHIVED, STATUS, FIRST_CHANGE#, NEXT_CHANGE#,LAST_CHANGE# from v$standby_log ;

GROUP# DB_ID THREAD# SEQUENCE# BYTES USED ARC STATUS FIRST_CHANGE# NEXT_CHANGE# LAST_CHANGE#

---------- --------------- ---------- ---------- ---------- ---------- --- ---------- ------------- ------------ ------------

4 UNASSIGNED 1 0 52428800 0 YES UNASSIGNED

5 UNASSIGNED 1 0 52428800 0 YES UNASSIGNED

6 UNASSIGNED 0 0 52428800 0 YES UNASSIGNED

7 UNASSIGNED 0 0 52428800 0 YES UNASSIGNED

已用时间: 00: 00: 00.02

20:36:47 SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_

---------- ------- ------- --------------------------------------------------- ---

3 ONLINE /u01/app/oracle/oradata/oradg11g/redo03.log NO

2 ONLINE /u01/app/oracle/oradata/oradg11g/redo02.log NO

1 ONLINE /u01/app/oracle/oradata/oradg11g/redo01.log NO

4 STANDBY /u01/app/oracle/oradata/oradg11g/standby_redo04.log NO

5 STANDBY /u01/app/oracle/oradata/oradg11g/standby_redo05.log NO

6 STANDBY /u01/app/oracle/oradata/oradg11g/standby_redo06.log NO

7 STANDBY /u01/app/oracle/oradata/oradg11g/standby_redo07.log NO

已选择7行。

已用时间: 00: 00: 00.00

20:37:00 SQL> ! ls /u01/app/oracle/oradata/oradg11g/standby*

/u01/app/oracle/oradata/oradg11g/standby_redo04.log /u01/app/oracle/oradata/oradg11g/standby_redo06.log

/u01/app/oracle/oradata/oradg11g/standby_redo05.log /u01/app/oracle/oradata/oradg11g/standby_redo07.log

20:37:11 SQL>

主库oradg11g执行切换

17:35:45 SQL> select name, LOG_MODE, OPEN_MODE, database_role, SWITCHOVER_STATUS, db_unique_name from v$database;

NAME LOG_MODE OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DB_UNIQUE_NAME

--------- ------------ -------------------- ---------------- -------------------- ------------------------------

ORADG11G ARCHIVELOG READ WRITE PRIMARY TO STANDBY oradg11g

已用时间: 00: 00: 00.01

17:35:46 SQL> alter database commit to switchover to physical standby with session shutdown;

数据库已更改。

已用时间: 00: 00: 06.22

17:36:14 SQL> shutdown immediate

ORA-01092: ORACLE instance terminated. Disconnection forced

17:36:35 SQL> ORA-24324: 未初始化服务句柄

ORA-01041: 内部错误, hostdef 扩展名不存在

17:36:35 SQL> exit

从 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options 断开

[oracle@rhel6_lhr ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on 星期日 7月 26 17:36:39 2015

Copyright (c) 1982, 2011, Oracle. All rights reserved.

已连接到空闲例程。

17:36:39 SQL> startup mount;

ORACLE 例程已经启动。

Total System Global Area 325685248 bytes

Fixed Size 2228064 bytes

Variable Size 192938144 bytes

Database Buffers 125829120 bytes

Redo Buffers 4689920 bytes

数据库装载完毕。

17:36:52 SQL> select name, LOG_MODE, OPEN_MODE, database_role, SWITCHOVER_STATUS, db_unique_name from v$database;

NAME LOG_MODE OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DB_UNIQUE_NAME

--------- ------------ -------------------- ---------------- -------------------- ------------------------------

ORADG11G ARCHIVELOG MOUNTED PHYSICAL STANDBY RECOVERY NEEDED oradg11g

已用时间: 00: 00: 00.01

17:37:04 SQL>

告警日志：

Sun Jul 26 17:36:07 2015

alter database commit to switchover to physical standby with session shutdown

ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 37669] (oradg11g)

Sun Jul 26 17:36:07 2015

Thread 1 advanced to log sequence 127 (LGWR switch)

Current log# 1 seq# 127 mem# 0: /u01/app/oracle/oradata/oradg11g/redo01.log

Sun Jul 26 17:36:07 2015

Stopping background process CJQ0

Stopping background process QMNC

All dispatchers and shared servers shutdown

CLOSE: killing server sessions.

Active process 37415 user 'grid' program 'oracle@rhel6_lhr'

Active process 37413 user 'grid' program 'oracle@rhel6_lhr'

Active process 37483 user 'oracle' program 'oracle@rhel6_lhr (W000)'

Active process 37483 user 'oracle' program 'oracle@rhel6_lhr (W000)'

Active process 37415 user 'grid' program 'oracle@rhel6_lhr'

Active process 37413 user 'grid' program 'oracle@rhel6_lhr'

Active process 37483 user 'oracle' program 'oracle@rhel6_lhr (W000)'

Active process 37483 user 'oracle' program 'oracle@rhel6_lhr (W000)'

Active process 37415 user 'grid' program 'oracle@rhel6_lhr'

Active process 37413 user 'grid' program 'oracle@rhel6_lhr'

Active process 37483 user 'oracle' program 'oracle@rhel6_lhr (W000)'

Active process 37483 user 'oracle' program 'oracle@rhel6_lhr (W000)'

Active process 37415 user 'grid' program 'oracle@rhel6_lhr'

Active process 37413 user 'grid' program 'oracle@rhel6_lhr'

Active process 37483 user 'oracle' program 'oracle@rhel6_lhr (W000)'

Active process 37483 user 'oracle' program 'oracle@rhel6_lhr (W000)'

Active process 37415 user 'grid' program 'oracle@rhel6_lhr'

Active process 37413 user 'grid' program 'oracle@rhel6_lhr'

Active process 37483 user 'oracle' program 'oracle@rhel6_lhr (W000)'

Active process 37483 user 'oracle' program 'oracle@rhel6_lhr (W000)'

Active process 37415 user 'grid' program 'oracle@rhel6_lhr'

Active process 37413 user 'grid' program 'oracle@rhel6_lhr'

Active process 37483 user 'oracle' program 'oracle@rhel6_lhr (W000)'

Active process 37483 user 'oracle' program 'oracle@rhel6_lhr (W000)'

Active process 37415 user 'grid' program 'oracle@rhel6_lhr'

Active process 37413 user 'grid' program 'oracle@rhel6_lhr'

Active process 37483 user 'oracle' program 'oracle@rhel6_lhr (W000)'

Active process 37483 user 'oracle' program 'oracle@rhel6_lhr (W000)'

Active process 37415 user 'grid' program 'oracle@rhel6_lhr'

Active process 37413 user 'grid' program 'oracle@rhel6_lhr'

Active process 37483 user 'oracle' program 'oracle@rhel6_lhr (W000)'

Active process 37483 user 'oracle' program 'oracle@rhel6_lhr (W000)'

Active process 37415 user 'grid' program 'oracle@rhel6_lhr'

Active process 37413 user 'grid' program 'oracle@rhel6_lhr'

Active process 37483 user 'oracle' program 'oracle@rhel6_lhr (W000)'

Active process 37483 user 'oracle' program 'oracle@rhel6_lhr (W000)'

Active process 37415 user 'grid' program 'oracle@rhel6_lhr'

Active process 37413 user 'grid' program 'oracle@rhel6_lhr'

Active process 37483 user 'oracle' program 'oracle@rhel6_lhr (W000)'

Active process 37483 user 'oracle' program 'oracle@rhel6_lhr (W000)'

Active process 37415 user 'grid' program 'oracle@rhel6_lhr'

Active process 37413 user 'grid' program 'oracle@rhel6_lhr'

Active process 37483 user 'oracle' program 'oracle@rhel6_lhr (W000)'

Active process 37483 user 'oracle' program 'oracle@rhel6_lhr (W000)'

Active process 37415 user 'grid' program 'oracle@rhel6_lhr'

Active process 37413 user 'grid' program 'oracle@rhel6_lhr'

Active process 37483 user 'oracle' program 'oracle@rhel6_lhr (W000)'

Active process 37483 user 'oracle' program 'oracle@rhel6_lhr (W000)'

Active process 37415 user 'grid' program 'oracle@rhel6_lhr'

Active process 37413 user 'grid' program 'oracle@rhel6_lhr'

Active process 37483 user 'oracle' program 'oracle@rhel6_lhr (W000)'

Active process 37483 user 'oracle' program 'oracle@rhel6_lhr (W000)'

Active process 37415 user 'grid' program 'oracle@rhel6_lhr'

Active process 37413 user 'grid' program 'oracle@rhel6_lhr'

Active process 37483 user 'oracle' program 'oracle@rhel6_lhr (W000)'

Active process 37483 user 'oracle' program 'oracle@rhel6_lhr (W000)'

CLOSE: all sessions shutdown successfully.

Waiting for all non-current ORLs to be archived...

Waiting for the ORL for thread 1 sequence 126 to be archived...

Sun Jul 26 17:36:11 2015

Archived Log entry 349 added for thread 1 sequence 126 ID 0x5441e6d9 dest 1:

ORL for thread 1 sequence 126 has been 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 Physical Standby switchover target to become synchronized...

Active, synchronized Physical Standby switchover target has been identified

Switchover End-Of-Redo Log thread 1 sequence 127 has been fixed

Switchover: Primary highest seen SCN set to 0x0.0x1a1f6f

ARCH: Noswitch archival of thread 1, sequence 127

ARCH: End-Of-Redo Branch archival of thread 1 sequence 127

ARCH: LGWR is actively archiving destination LOG_ARCHIVE_DEST_2

ARCH: Standby redo logfile selected for thread 1 sequence 127 for destination LOG_ARCHIVE_DEST_2

Archived Log entry 350 added for thread 1 sequence 127 ID 0x5441e6d9 dest 1:

ARCH: Archiving is disabled due to current logfile archival

Primary will check for some target standby to have received alls redo

Final check for a synchronized target standby. Check will be made once.

LOG_ARCHIVE_DEST_2 is a potential Physical Standby switchover target

Active, synchronized target has been identified

Target has also received all redo

Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/oradg11g/oradg11g/trace/oradg11g_ora_37669.trc

Clearing standby activation ID 1413605081 (0x5441e6d9)

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;

Archivelog for thread 1 sequence 127 required for standby recovery

Switchover: Primary controlfile converted to standby controlfile succesfully.

Switchover: Complete - Database shutdown required

Completed: alter database commit to switchover to physical standby with session shutdown

Sun Jul 26 17:36:15 2015

Process (ospid 26918) is suspended due to switchover to physical standby operation.

Sun Jul 26 17:36:33 2015

Performing implicit shutdown abort due to switchover to physical standby

Shutting down instance (abort)

License high water mark = 7

USER (ospid: 37669): terminating the instance

Instance terminated by USER, pid = 37669

Sun Jul 26 17:36:35 2015

Instance shutdown complete

ORA-1092 : opitsk aborting process

Sun Jul 26 17:36:44 2015

Starting ORACLE instance (normal)

****************** Large Pages Information *****************

Total Shared Global Region in Large Pages = 0 KB (0%)

Large Pages used by this instance: 0 (0 KB)

Large Pages unused system wide = 0 (0 KB) (alloc incr 4096 KB)

Large Pages configured system wide = 0 (0 KB)

Large Page size = 2048 KB

RECOMMENDATION:

Total Shared Global Region size is 314 MB. For optimal performance,

prior to the next instance restart increase the number

of unused Large Pages by atleast 157 2048 KB Large Pages (314 MB)

system wide to get 100% of the Shared

Global Region allocated with Large pages

***********************************************************

LICENSE_MAX_SESSION = 0

LICENSE_SESSIONS_WARNING = 0

Picked latch-free SCN scheme 3

Autotune of undo retention is turned on.

IMODE=BR

ILAT =27

LICENSE_MAX_USERS = 0

SYS auditing is disabled

Starting up:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options.

ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1

System name: Linux

Node name: rhel6_lhr

Release: 2.6.32-504.16.2.el6.x86_64

Version: #1 SMP Tue Apr 21 08:37:59 PDT 2015

Machine: x86_64

VM name: VMWare Version: 6

Using parameter settings in server-side spfile /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfileoradg11g.ora

System parameters with non-default values:

processes = 150

sga_target = 312M

control_files = "/u01/app/oracle/oradata/oradg11g/control01.ctl"

control_files = "/u01/app/oracle/flash_recovery_area/oradg11g/control02.ctl"

db_file_name_convert = "oradgphy"

db_file_name_convert = "oradg11g"

log_file_name_convert = "oradgphy"

log_file_name_convert = "oradg11g"

db_block_size = 8192

compatible = "11.2.0.0.0"

log_archive_dest_1 = "LOCATION=USE_DB_RECOVERY_FILE_DEST db_unique_name=oradg11g valid_for=(ALL_LOGFILES,ALL_ROLES)"

log_archive_dest_2 = "SERVICE=tns_oradgphy LGWR ASYNC db_unique_name=oradgphy valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)"

log_archive_dest_3 = "SERVICE=tns_oradglg LGWR ASYNC db_unique_name=oradglg valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)"

log_archive_dest_4 = "SERVICE=tns_oradgss LGWR ASYNC db_unique_name=oradgss valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE)"

log_archive_dest_state_1 = "ENABLE"

log_archive_dest_state_2 = "ENABLE"

log_archive_dest_state_3 = "defer"

log_archive_dest_state_4 = "defer"

fal_client = "oradg11g"

fal_server = "oradgphy"

log_archive_config = "DG_CONFIG=(oradg11g,oradgphy,oradglg,oradgss)"

log_archive_max_processes= 6

db_recovery_file_dest = "/u01/app/oracle/flash_recovery_area"

db_recovery_file_dest_size= 4122M

standby_file_management = "AUTO"

undo_tablespace = "UNDOTBS1"

remote_login_passwordfile= "EXCLUSIVE"

db_domain = ""

dispatchers = "(PROTOCOL=TCP) (SERVICE=oradg11gXDB)"

audit_file_dest = "/u01/app/oracle/admin/oradg11g/adump"

audit_trail = "DB"

db_name = "oradg11g"

db_unique_name = "oradg11g"

open_cursors = 300

pga_aggregate_target = 78M

diagnostic_dest = "/u01/app/oracle"

Sun Jul 26 17:36:45 2015

PMON started with pid=2, OS id=37709

Sun Jul 26 17:36:45 2015

PSP0 started with pid=3, OS id=37711

Sun Jul 26 17:36:46 2015

VKTM started with pid=4, OS id=37713 at elevated priority

VKTM running at (1)millisec precision with DBRM quantum (100)ms

Sun Jul 26 17:36:46 2015

GEN0 started with pid=5, OS id=37717

Sun Jul 26 17:36:46 2015

这一步容易出现问题，注意下边的问题：

alter database commit to switchover to physical standby with session shutdown;

稍等。。。。等待数据库挂掉后再启动到mount，千万别shutdown immediate,应该用shutdown abort，等待告警日志中出现如下提示再进行下一步操作，否则可能导致switchover切换失败，主库将不可用

--Switchover: Primary controlfile converted to standby controlfile succesfully.

--Switchover: Complete - Database shutdown required

--Completed: alter database commit to switchover to physical standby with session shutdown

--Sun Jul 26 17:36:15 2015

--Process (ospid 26918) is suspended due to switchover to physical standby operation.

备库oradgphy执行切换

[oracle@rhel6_lhr admin]$ echo $ORACLE_SID

oradgphy

[oracle@rhel6_lhr admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on 星期日 7月 26 17:40:24 2015

Copyright (c) 1982, 2011, Oracle. All rights reserved.

连接到:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

17:40:24 SQL> select name, LOG_MODE, OPEN_MODE, database_role, SWITCHOVER_STATUS, db_unique_name from v$database;

NAME LOG_MODE OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DB_UNIQUE_NAME

--------- ------------ -------------------- ---------------- -------------------- ------------------------------

ORADG11G ARCHIVELOG READ ONLY WITH APPLY PHYSICAL STANDBY TO PRIMARY oradgphy

已用时间: 00: 00: 00.01

17:40:28 SQL> alter database commit to switchover to primary with session shutdown;

数据库已更改。

已用时间: 00: 00: 02.07

17:40:44 SQL> select name, LOG_MODE, OPEN_MODE, database_role, SWITCHOVER_STATUS, db_unique_name from v$database;

NAME LOG_MODE OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DB_UNIQUE_NAME

--------- ------------ -------------------- ---------------- -------------------- ------------------------------

ORADG11G ARCHIVELOG MOUNTED PRIMARY NOT ALLOWED oradgphy

已用时间: 00: 00: 00.01

17:40:56 SQL>

17:42:47 SQL> alter database open;

数据库已更改。

已用时间: 00: 00: 01.88

17:43:04 SQL>

告警日志：

Sun Jul 26 17:40:42 2015

alter database commit to switchover to primary with session shutdown

ALTER DATABASE SWITCHOVER TO PRIMARY (oradgphy)

Maximum wait for role transition is 15 minutes.

Switchover: Media recovery is still active

Role Change: Canceling MRP - no more redo to apply

Sun Jul 26 17:40:44 2015

MRP0: Background Media Recovery cancelled with status 16037

Errors in file /u01/app/oracle/diag/rdbms/oradgphy/oradgphy/trace/oradgphy_pr00_28229.trc:

ORA-16037: user requested cancel of managed recovery operation

Managed Standby Recovery not using Real Time Apply

Recovery interrupted!

Sun Jul 26 17:40:44 2015

MRP0: Background Media Recovery process shutdown (oradgphy)

Role Change: Canceled MRP

All dispatchers and shared servers shutdown

CLOSE: killing server sessions.

CLOSE: all sessions shutdown successfully.

Sun Jul 26 17:40:44 2015

SMON: disabling cache recovery

Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/oradgphy/oradgphy/trace/oradgphy_ora_37868.trc

SwitchOver after complete recovery through change 1711983

Online log /u01/app/oracle/oradata/oradgphy/redo01.log: Thread 1 Group 1 was previously cleared

Online log /u01/app/oracle/oradata/oradgphy/redo02.log: Thread 1 Group 2 was previously cleared

Online log /u01/app/oracle/oradata/oradgphy/redo03.log: Thread 1 Group 3 was previously cleared

Standby became primary SCN: 1711981

AUDIT_TRAIL initialization parameter is changed back to its original value as specified in the parameter file.

Switchover: Complete - Database mounted as primary

Completed: alter database commit to switchover to primary with session shutdown

Sun Jul 26 17:41:14 2015

idle dispatcher 'D000' terminated, pid = (17, 1)

新备库oradg11g开启实时应用

[oracle@rhel6_lhr ~]$ echo $ORACLE_SID

oradg11g

[oracle@rhel6_lhr ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on 星期日 7月 26 17:45:47 2015

Copyright (c) 1982, 2011, Oracle. All rights reserved.

连接到:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

17:45:47 SQL> select name, LOG_MODE, OPEN_MODE, database_role, SWITCHOVER_STATUS, db_unique_name from v$database;

NAME LOG_MODE OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DB_UNIQUE_NAME

--------- ------------ -------------------- ---------------- -------------------- ------------------------------

ORADG11G ARCHIVELOG MOUNTED PHYSICAL STANDBY RECOVERY NEEDED oradg11g

已用时间: 00: 00: 00.01

17:45:53 SQL> alter database recover managed standby database using current logfile disconnect;

数据库已更改。

已用时间: 00: 00: 06.02

17:46:13 SQL> alter database recover managed standby database cancel;

数据库已更改。

已用时间: 00: 00: 01.48

17:46:16 SQL> alter database open;

数据库已更改。

已用时间: 00: 00: 00.56

17:46:21 SQL> alter database recover managed standby database using current logfile disconnect;

数据库已更改。

已用时间: 00: 00: 06.02

17:46:35 SQL>

测试切换后的情况

17:47:42 SQL> select name, LOG_MODE, OPEN_MODE, database_role, SWITCHOVER_STATUS, db_unique_name from v$database;

NAME LOG_MODE OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DBUNIQUE\

--------- ------------ -------------------- ---------------- -------------------- ----------

ORADG11G ARCHIVELOG READ WRITE PRIMARY TO STANDBY oradgphy

已用时间: 00: 00: 00.00

17:47:54 SQL> create table test as select * from scott.emp;

表已创建。

已用时间: 00: 00: 00.86

17:48:21 SQL> select count(1) from test;

COUNT(1)

----------

14

已用时间: 00: 00: 00.00

17:50:27 SQL>

17:50:27 SQL> conn sys/lhr@tns_oradg11g as sysdba

已连接。

17:50:42 SQL> select count(1) from test;

COUNT(1)

----------

14

已用时间: 00: 00: 00.01

17:50:46 SQL> conn sys/lhr@tns_oradgphy as sysdba

已连接。

17:51:08 SQL> drop table test;

表已删除。

已用时间: 00: 00: 01.14

17:51:13 SQL> conn sys/lhr@tns_oradgphy as sysdba

已连接。

17:51:17 SQL> conn sys/lhr@tns_oradg11g as sysdba

已连接。

17:51:21 SQL> select count(1) from test;

select count(1) from test

*

第 1 行出现错误:

ORA-00942: 表或视图不存在

已用时间: 00: 00: 00.00

17:51:24 SQL>

切换回原来最初的状态

新主库ordgphy操作：

20:28:43 SQL> alter database commit to switchover to physical standby with session shutdown;

数据库已更改。

已用时间: 00: 00: 04.65

20:30:01 SQL> shutdown immediate

ORA-01092: ORACLE instance terminated. Disconnection forced

20:30:33 SQL> startup mount;

ORA-24324: 未初始化服务句柄

ORA-01041: 内部错误, hostdef 扩展名不存在

20:30:41 SQL> exit

从 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options 断开

[oracle@rhel6_lhr admin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on 星期日 7月 26 20:30:45 2015

Copyright (c) 1982, 2011, Oracle. All rights reserved.

已连接到空闲例程。

20:30:45 SQL> startup mount;

ORACLE 例程已经启动。

Total System Global Area 242171904 bytes

Fixed Size 2227256 bytes

Variable Size 188744648 bytes

Database Buffers 46137344 bytes

Redo Buffers 5062656 bytes

数据库装载完毕。

新备库oradg11g操作：

20:28:46 SQL> select name, LOG_MODE, OPEN_MODE, database_role, SWITCHOVER_STATUS, db_unique_name from v$database;

NAME LOG_MODE OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DB_UNIQUE_NAME

--------- ------------ -------------------- ---------------- -------------------- ------------------------------

ORADG11G ARCHIVELOG READ ONLY WITH APPLY PHYSICAL STANDBY TO PRIMARY oradg11g

已用时间: 00: 00: 00.01

20:31:20 SQL> alter database commit to switchover to primary with session shutdown;

数据库已更改。

已用时间: 00: 00: 02.07

20:31:32 SQL> alter database open;

数据库已更改。

已用时间: 00: 00: 00.34

20:31:38 SQL> select name, LOG_MODE, OPEN_MODE, database_role, SWITCHOVER_STATUS, db_unique_name from v$database;

NAME LOG_MODE OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DB_UNIQUE_NAME

--------- ------------ -------------------- ---------------- -------------------- ------------------------------

ORADG11G ARCHIVELOG READ WRITE PRIMARY RESOLVABLE GAP oradg11g

已用时间: 00: 00: 00.00

oradgphy 操作：

20:30:54 SQL> alter database recover managed standby database using current logfile disconnect;

数据库已更改。

已用时间: 00: 00: 06.04

20:32:23 SQL> alter database recover managed standby database cancel;

数据库已更改。

已用时间: 00: 00: 01.01

20:32:36 SQL> alter database open;

数据库已更改。

已用时间: 00: 00: 00.21

20:32:42 SQL> alter database recover managed standby database using current logfile disconnect;

数据库已更改。

已用时间: 00: 00: 06.02

20:32:53 SQL> 20:32:53 SQL> select name, LOG_MODE, OPEN_MODE, database_role, SWITCHOVER_STATUS, db_unique_name from v$database;

NAME LOG_MODE OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DB_UNIQUE_NAME

--------- ------------ -------------------- ---------------- -------------------- ------------------------------

ORADG11G ARCHIVELOG READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED oradgphy

已用时间: 00: 00: 00.00

20:33:21 SQL>

OK，切换完成。

实验总结

切换过程中尤其注意主库执行alter database commit to switchover to physical standby with session shutdown;后需要等待数据库abort掉，或者我们手动的shutdown abort，否则可能导致实验失败，主库脱离dg环境不可用从而变为了failover切换了，所以实验之前还是对主库做rman备份比较稳妥一些。

总结

进行Dataguard switchover和failover是非常常见的运维需求，在实际场景下，我们尽可能选择稳妥完全的策略进行操作，SQL命令还是我们比较好的选择，多操作几次就熟悉了。

物理dg的failover切换

前言部分

导读

各位技术爱好者，看完本文后，你可以掌握如下的技能，也可以学到一些其它你所不知道的知识，~O(∩_∩)O~：

① 物理dg的failover切换演练过程

② 物理dg管理和维护的一些sql

③ 利用duplicate搭建物理dg

实验环境介绍

项目主库dg库
db 类型单实例单实例
db version11.2.0.311.2.0.3
db 存储FS typeFS type
ORACLE_SIDoradg11goradgphy
db_nameoradg11goradg11g
主机IP地址：192.168.59.130192.168.59.130
OS版本及kernel版本RHEL6.5 64位，2.6.32-504.16.2.el6.x86_64RHEL6.5 64位，2.6.32-504.16.2.el6.x86_64
OS hostnamerhel6_lhrrhel6_lhr

相关参考文章链接

【DATAGUARD】 基于同一个主机建立物理备库和逻辑备库（一）： http://blog.itpub.net/26736162/viewspace-1448197/
【DATAGUARD】 基于同一个主机建立物理备库和逻辑备库（二 ): http://blog.itpub.net/26736162/viewspace-1448207/
【DATAGUARD】 基于同一个主机建立物理备库和逻辑备库（三 ): http://blog.itpub.net/26736162/viewspace-1481972/
【DATAGUARD】 基于同一个主机建立物理备库和逻辑备库 (四)--添加一个物理dg节点 ：http://blog.itpub.net/26736162/viewspace-1484878/

【DATAGUARD】物理dg的switchover切换（五） ：http://blog.itpub.net/26736162/viewspace-1753111/

本文简介

接上一篇文章【DATAGUARD】物理dg的switchover切换（五） ：http://blog.itpub.net/26736162/viewspace-1753111/，本章中介绍failover的切换。

相关知识点扫盲

1．角色

Oracle 数据库中含有两种角色。

用户角色：定义了一组权限的集合，该角色可以分配给用户，也可以分配给其他角色。

数据库角色：在备库中数据库扮演什么样的角色，primary还是standby。

v$database.DATABASE_ROLE标识了数据库的运行角色。

处于备库环境中，数据库有两种类型：phsical standby、logical standby。

2．角色管理服务

一个数据库运行在如下互相排斥的角色中。

Primary role：一个数据库运行在primary role，那么log transport services传递重做日志到备库。

Standby role：一个数据库运行在standby role，那么log apply services应用归档日志到备库。

角色管理服务允许用户动态地在主、备库中进行角色切换。

用户可以使用角色管理服务，进行主、备库的计划中的角色切换，这个叫switchover，或者是非计划中的角色切换，叫failover。

3．Switchover&Failover

切换是在主数据库与其备数据库之间进行角色反转，切换确保不丢失数据。这是对于主系统计划维护的典型操作。在切换期间，主数据库转换到备角色，备数据库转换到主角色。转换发生不需要重建任何数据库。

（1）Switchover

用到的场景：计划中的角色转换或用户操作系统和硬件的维护等。

（2）Failover

故障转移是当主数据库不可用时执行的。故障转移只有在主数据库灾难故障的情况下执行，并且故障转移导致备数据库转换到主角色。用到的场景：非计划中的角色切换，一般在紧急情况下使用。根据保护模式的不同，可能会没有或者很少的数据损失。

（3）角色转换决策树

角色转换（switchover&failover）的最终目的是尽快地使主库在线，而同时尽量减少数据损失或者是实现无数据损失。尽量选择宕机时间最短，同时数据损失最小的策略。总之在失败切换前，应该先考虑修复主数据库或者进行无数据损失的角色转换。

即使使用无数据损失的备库方案，修复主库可能会比切换到备库更快点。如果修复了主库，那么就不需要修改客户端的连接。但是如果修复工作导致了任何的数据损失，那么可能需要重新创建所有的备用数据库。

通常情况下，最合适切换的备库为已经应用了最多的归档日志的备用数据库。

实验部分

实验目标

  1. 主库和物理dg的failover切换
  2. 还原为最初始的状态

实验过程

注：failover切换后原来的主库将不可用，必须重新搭建，所以该实验请慎重选择。

在主库端模拟数据库意外宕机

[oracle@rhel6_lhr oradg11g]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on 星期日 7月 26 21:26:20 2015

Copyright (c) 1982, 2011, Oracle. All rights reserved.

连接到:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

21:26:20 SQL>

21:26:29 SQL> select name, LOG_MODE, OPEN_MODE, database_role, SWITCHOVER_STATUS, db_unique_name from v$database;

NAME LOG_MODE OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DB_UNIQUE_NAME

--------- ------------ -------------------- ---------------- -------------------- ------------------------------

ORADG11G ARCHIVELOG READ WRITE PRIMARY TO STANDBY oradg11g

已用时间: 00: 00: 00.01

21:26:37 SQL> alter system switch logfile;

系统已更改。

已用时间: 00: 00: 00.01

21:27:31 SQL> create table test as select * from dba_objects;

表已创建。

已用时间: 00: 00: 00.50

21:27:38 SQL> insert into test select * from test;

已创建 75250 行。

已用时间: 00: 00: 00.51

21:27:56 SQL> insert into test select * from test;

已创建 150500 行。

已用时间: 00: 00: 02.62

21:28:00 SQL> commit;

提交完成。

已用时间: 00: 00: 00.00

21:28:06 SQL> insert into test select * from test;

已创建 301000 行。

已用时间: 00: 00: 12.41

21:28:21 SQL> commit;

提交完成。

已用时间: 00: 00: 00.00

21:28:27 SQL> insert into test select * from test;

已创建 602000 行。

已用时间: 00: 00: 22.63

21:29:08 SQL> commit;

提交完成。

已用时间: 00: 00: 01.29

21:29:12 SQL> shutdown abort;

ORACLE 例程已经关闭。

21:29:14 SQL>

备库查询：

20:45:30 SQL> select count(1) from test;

COUNT(1)

----------

1204000

已用时间: 00: 00: 00.05

21:29:42 SQL> select thread#, low_sequence#, high_sequence# from v$archive_gap;

未选定行

已用时间: 00: 00: 00.02

21:31:23 SQL>

如果没有发现明显的gap现象，说明此次的failover不会有数据损失情况。在standby端，要进行关闭apply和结束应用动作。

备库直接切换

21:31:23 SQL> alter database recover managed standby database cancel;

数据库已更改。

已用时间: 00: 00: 01.01

21:33:46 SQL> alter database recover managed standby database finish;

数据库已更改。

已用时间: 00: 00: 00.31

21:33:52 SQL> select name, LOG_MODE, OPEN_MODE, database_role, SWITCHOVER_STATUS, db_unique_name from v$database;

NAME LOG_MODE OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DB_UNIQUE_NAME

--------- ------------ -------------------- ---------------- -------------------- ------------------------------

ORADG11G ARCHIVELOG READ ONLY PHYSICAL STANDBY TO PRIMARY oradgphy

已用时间: 00: 00: 00.01

21:34:16 SQL> alter database commit to switchover to primary with session shutdown;

数据库已更改。

已用时间: 00: 00: 00.07

21:36:16 SQL>

21:36:16 SQL> select name, LOG_MODE, OPEN_MODE, database_role, SWITCHOVER_STATUS, db_unique_name from v$database;

NAME LOG_MODE OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DB_UNIQUE_NAME

--------- ------------ -------------------- ---------------- -------------------- ------------------------------

ORADG11G ARCHIVELOG MOUNTED PRIMARY NOT ALLOWED oradgphy

已用时间: 00: 00: 00.01

21:38:18 SQL> alter database open;

数据库已更改。

已用时间: 00: 00: 00.30

21:38:42 SQL> select name, LOG_MODE, OPEN_MODE, database_role, SWITCHOVER_STATUS, db_unique_name from v$database;

NAME LOG_MODE OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DB_UNIQUE_NAME

--------- ------------ -------------------- ---------------- -------------------- ------------------------------

ORADG11G ARCHIVELOG READ WRITE PRIMARY RESOLVABLE GAP oradgphy

已用时间: 00: 00: 00.00

21:38:49 SQL>

切换成功。切换之后，我们观察这个过程的日志情况。

alert日志：

alter database recover managed standby database cancel

Sun Jul 26 21:33:45 2015

MRP0: Background Media Recovery cancelled with status 16037

Errors in file /u01/app/oracle/diag/rdbms/oradgphy/oradgphy/trace/oradgphy_pr00_45791.trc:

ORA-16037: user requested cancel of managed recovery operation

Managed Standby Recovery not using Real Time Apply

Recovery interrupted!

Recovered data files to a consistent state at change 1874535

Sun Jul 26 21:33:46 2015

MRP0: Background Media Recovery process shutdown (oradgphy)

Managed Standby Recovery Canceled (oradgphy)

Completed: alter database recover managed standby database cancel

alter database recover managed standby database finish

Attempt to do a Terminal Recovery (oradgphy)

Media Recovery Start: Managed Standby Recovery (oradgphy)

started logmerger process

Sun Jul 26 21:33:52 2015

Managed Standby Recovery not using Real Time Apply

Parallel Media Recovery started with 2 slaves

Begin: Standby Redo Logfile archival

End: Standby Redo Logfile archival

Terminal Recovery timestamp is '07/26/2015 21:33:52'

Terminal Recovery: applying standby redo logs.

Terminal Recovery: thread 1 seq# 183 redo required

Terminal Recovery:

Recovery of Online Redo Log: Thread 1 Group 4 Seq 183 Reading mem 0

Mem# 0: /u01/app/oracle/oradata/oradgphy/standby_redo04.log

Identified End-Of-Redo (failover) for thread 1 sequence 183 at SCN 0xffff.ffffffff

Incomplete Recovery applied until change 1874536 time 07/26/2015 21:29:12

Media Recovery Complete (oradgphy)

Terminal Recovery: successful completion

Sun Jul 26 21:33:52 2015

ARCH: Archival stopped, error occurred. Will continue retrying

ORACLE Instance oradgphy - Archival Error

ORA-16014: log 4 sequence# 183 not archived, no available destinations

ORA-00312: online log 4 thread 1: '/u01/app/oracle/oradata/oradgphy/standby_redo04.log'

Forcing ARSCN to IRSCN for TR 0:1874536

Attempt to set limbo arscn 0:1874536 irscn 0:1874536

Resetting standby activation ID 1413582769 (0x54418fb1)

Completed: alter database recover managed standby database finish

Sun Jul 26 21:36:16 2015

alter database commit to switchover to primary with session shutdown

ALTER DATABASE SWITCHOVER TO PRIMARY (oradgphy)

Maximum wait for role transition is 15 minutes.

All dispatchers and shared servers shutdown

CLOSE: killing server sessions.

CLOSE: all sessions shutdown successfully.

Sun Jul 26 21:36:16 2015

SMON: disabling cache recovery

Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/oradgphy/oradgphy/trace/oradgphy_ora_45673.trc

Standby terminal recovery start SCN: 1874535

RESETLOGS after incomplete recovery UNTIL CHANGE 1874536

Online log /u01/app/oracle/oradata/oradgphy/redo01.log: Thread 1 Group 1 was previously cleared

Online log /u01/app/oracle/oradata/oradgphy/redo02.log: Thread 1 Group 2 was previously cleared

Online log /u01/app/oracle/oradata/oradgphy/redo03.log: Thread 1 Group 3 was previously cleared

Standby became primary SCN: 1874534

Sun Jul 26 21:36:16 2015

Setting recovery target incarnation to 3

AUDIT_TRAIL initialization parameter is changed back to its original value as specified in the parameter file.

Switchover: Complete - Database mounted as primary

Completed: alter database commit to switchover to primary with session shutdown

Sun Jul 26 21:36:45 2015

idle dispatcher 'D000' terminated, pid = (17, 1)

Sun Jul 26 21:36:54 2015

ARC0: Becoming the 'no SRL' ARCH

Sun Jul 26 21:36:55 2015

ARC1: Becoming the 'no SRL' ARCH

切换后的测试

21:38:49 SQL> alter system switch logfile;

系统已更改。

已用时间: 00: 00: 00.01

21:40:59 SQL> delete from test where rownum\<=10000;

已删除10000行。

已用时间: 00: 00: 00.13

21:41:31 SQL> commit;

提交完成。

已用时间: 00: 00: 00.00

21:42:27 SQL> set line 9999

21:42:49 SQL> col DEST_NAME format a20

21:42:49 SQL> col DESTINATION format a20

21:42:49 SQL> col GAP_STATUS format a10

21:42:49 SQL> col DB_UNIQUE_NAME format a10

21:42:49 SQL> col error format a10

21:42:49 SQL> SELECT al.thread#,

21:42:50 2 ads.dest_id,

21:42:50 3 ads.DEST_NAME,

21:42:50 4 (SELECT ad.TARGET

21:42:50 5 FROM v$archive_dest AD

21:42:50 6 WHERE AD.DEST_ID = ADS.DEST_ID) TARGET,

21:42:50 7 ADS.DATABASE_MODE,

21:42:50 8 ads.STATUS,

21:42:50 9 ads.error,

21:42:50 10 ads.TYPE,

21:42:50 11 ads.RECOVERY_MODE,

21:42:50 12 ads.DB_UNIQUE_NAME,

21:42:50 13 ads.DESTINATION,

21:42:50 14 ads.GAP_STATUS,

21:42:50 15 (SELECT MAX(sequence#) FROM v$log na WHERE na.thread# = al.thread#) "Current Sequence",

21:42:50 16 MAX(sequence#) "Last Archived",

21:42:50 17 MAX(decode(al.APPLIED, 'YES', sequence#)) APPLIED_SEQ#

21:42:50 18 FROM (SELECT *

21:42:50 19 FROM v$archived_log V

21:42:50 20 WHERE V.resetlogs_change# =

21:42:50 21 (SELECT d.RESETLOGS_CHANGE# FROM v$database d)) al,

21:42:50 22 v$archive_dest_status ads

21:42:50 23 WHERE al.dest_id(+) = ads.dest_id

21:42:50 24 AND ads.STATUS != 'INACTIVE'

21:42:50 25 GROUP BY al.thread#,

21:42:50 26 ads.dest_id,

21:42:50 27 ads.DEST_NAME,

21:42:50 28 ads.STATUS,

21:42:50 29 ads.error,

21:42:50 30 ads.TYPE,

21:42:50 31 ADS.DATABASE_MODE,

21:42:50 32 ads.RECOVERY_MODE,

21:42:50 33 ads.DB_UNIQUE_NAME,

21:42:50 34 ads.DESTINATION,

21:42:50 35 ads.GAP_STATUS

21:42:50 36 ORDER BY al.thread#,

21:42:50 37 ads.dest_id;

THREAD# DEST_ID DEST_NAME TARGET DATABASE_MODE STATUS ERROR TYPE RECOVERY_MODE DBUNIQUE\ DESTINATION GAP_STATUS Current Sequence Last Archived APPLIED_SEQ#

---------- ---------- -------------------- ------- --------------- --------- ---------- -------------- ----------------------- ---------- -------------------- ---------- ---------------- ------------- ------------

1 1 LOG_ARCHIVE_DEST_1 PRIMARY OPEN VALID LOCAL IDLE oradgphy 4 3

2 LOG_ARCHIVE_DEST_2 STANDBY UNKNOWN ERROR ORA-01034: UNKNOWN IDLE oradg11g tns_oradg11g RESOLVABLE

ORACLE GAP

不可用

已用时间: 00: 00: 00.00

21:42:50 SQL>

此时，failover过程成功。

原主库修复后，开机

21:52:00 SQL> startup

ORACLE 例程已经启动。

Total System Global Area 208769024 bytes

Fixed Size 2226936 bytes

Variable Size 146801928 bytes

Database Buffers 54525952 bytes

Redo Buffers 5214208 bytes

数据库装载完毕。

数据库已经打开。

21:52:15 SQL> alter database commit to switchover to physical standby with session shutdown;

alter database commit to switchover to physical standby with session shutdown

*

第 1 行出现错误:

ORA-16416: 没有可行的 Physical Standby 切换目标

已用时间: 00: 00: 02.06

21:52:24 SQL> select name, LOG_MODE, OPEN_MODE, database_role, SWITCHOVER_STATUS, db_unique_name from v$database;

NAME LOG_MODE OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DB_UNIQUE_NAME

--------- ------------ -------------------- ---------------- -------------------- ------------------------------

ORADG11G ARCHIVELOG READ WRITE PRIMARY FAILED DESTINATION oradg11g

已用时间: 00: 00: 00.00

21:52:31 SQL>

现在原来的主库被修复后，整个DataGuara架构已经被破坏了，所以必须把原来的主库删除后构建成新的备库，重新恢复DataGuard的环境，最后可以根据需要是否进行switchover切换来调整环境。

在源主库上重新搭建物理dg

[oracle@rhel6_lhr oradg11g]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on 星期日 7月 26 21:59:40 2015

Copyright (c) 1982, 2011, Oracle. All rights reserved.

连接到:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

21:59:40 SQL> alter database close;

数据库已更改。

已用时间: 00: 00: 02.17

22:00:01 SQL> alter system enable restricted session;

系统已更改。

已用时间: 00: 00: 02.06

22:00:05 SQL> drop database;

数据库已删除。

从 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options 断开

已用时间: 00: 00: 08.50

22:00:31 SQL> exit

[oracle@rhel6_lhr oradg11g]$ cd $ORACLE_HOME/dbs

[oracle@rhel6_lhr dbs]$ rm -rf spfileoradg11g.ora

[oracle@rhel6_lhr dbs]$ ll spfileoradg11g.ora

ls: cannot access spfileoradg11g.ora: No such file or directory

[oracle@rhel6_lhr dbs]$ more initoradg

initoradg11g.ora initoradglg.ora initoradgphy.ora initoradgss.ora

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
AiDBA后续精彩内容已被站长无情隐藏，请输入验证码解锁本文！
验证码：
获取验证码： 请先关注本站微信公众号，然后回复“验证码”，获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。

标签：

Avatar photo

小麦苗

学习或考证，均可联系麦老师，请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复