分类目录归档：Data Guard

ORA-600 krse_arc_complete.4

发表于 2024 年 10 月 24 日 由 惜分飞

11.2.0.4版本数据库对于虚拟化环境中的备库进行克隆,然后尝试failover方式激活备库,结果遭遇ORA-600 krse_arc_complete.4错误

SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;
ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE
*
ERROR at line 1:
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/u01/app/oracle/oradata/hisdb/system.256.975233377'

alert日志显示

Wed Oct 23 21:45:46 2024
ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE
ALTER DATABASE ACTIVATE [PHYSICAL] STANDBY DATABASE (hisdb)
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
Wed Oct 23 21:45:46 2024
ARC0: Detected ARCH process failure
ARC0: STARTING ARCH PROCESSES
Wed Oct 23 21:45:46 2024
ARC3 started with pid=22, OS id=28848 
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
Beginning Standby Crash Recovery.
Serial Media Recovery started
Managed Standby Recovery starting Real Time Apply
Errors in file /u01/app/oracle/diag/rdbms/hisdbdg/hisdb/trace/hisdb_arc0_28695.trc  (incident=528155):
ORA-00600: internal error code, arguments: [krse_arc_complete.4], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/hisdbdg/hisdb/incident/incdir_528155/hisdb_arc0_28695_i528155.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Master archival failure: 600
Wed Oct 23 21:45:47 2024
Deleted Oracle managed file /fra/fast_recovery_area/HISDBDG/archivelog/2024_10_23/o1_mf_2_158418_mkkzjbqt_.arc
Warning: Datafile 1 (/u01/app/oracle/oradata/hisdb/system.256.975233377) is infinitely media recovery fuzzy
Standby database will not open with this datafile online!
Standby Crash Recovery aborted due to error 10554.
Errors in file /u01/app/oracle/diag/rdbms/hisdbdg/hisdb/trace/hisdb_ora_28842.trc:
ORA-10554: Media recovery failed to bring datafile 1 to a consistent point
ORA-01110: data file 1: '/u01/app/oracle/oradata/hisdb/system.256.975233377'
Completed Standby Crash Recovery.
Wed Oct 23 21:45:47 2024
Errors in file /u01/app/oracle/diag/rdbms/hisdbdg/hisdb/trace/hisdb_arc2_28840.trc  (incident=528172):
ORA-00600: internal error code, arguments: [krse_arc_complete.4], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/hisdbdg/hisdb/incident/incdir_528172/hisdb_arc2_28840_i528172.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Master archival failure: 600
ORA-1196 signalled during: ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE...
ARC3: Detected ARCH process failure

查询mos 发现是尝试应用standby log,并且arc进程尝试对其进行归档,发现归档失败从而报该错误,可以尝试对standby log进行clear,然后再激活备库

SQL> select group#,status from v$standby_log;

          GROUP# STATUS
---------------- ----------
              11 UNASSIGNED
              12 UNASSIGNED
              13 UNASSIGNED
              14 ACTIVE
              15 UNASSIGNED
              16 UNASSIGNED
              17 UNASSIGNED
              18 UNASSIGNED

8 rows selected.

SQL> alter database clear unarchived logfile group 14;

Database altered.

SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;

Database altered.


Wed Oct 23 21:55:12 2024
ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE
ALTER DATABASE ACTIVATE [PHYSICAL] STANDBY DATABASE (hisdb)
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
Standby terminal recovery start SCN: 35738445353
RESETLOGS after incomplete recovery UNTIL CHANGE 35738178180
Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST
Resetting resetlogs activation ID 1887849281 (0x70864b41)
Online log /u01/app/oracle/oradata/hisdb/group_5.272.976991793: Thread 1 Group 5 was previously cleared
Online log /u01/app/oracle/oradata/hisdb/group_5.2338.976991793: Thread 1 Group 5 was previously cleared
Online log /u01/app/oracle/oradata/hisdb/group_6.273.976991805: Thread 1 Group 6 was previously cleared
Online log /u01/app/oracle/oradata/hisdb/group_6.2339.976991805: Thread 1 Group 6 was previously cleared
Online log /u01/app/oracle/oradata/hisdb/group_7.274.976991825: Thread 2 Group 7 was previously cleared
Online log /u01/app/oracle/oradata/hisdb/group_7.2336.976991825: Thread 2 Group 7 was previously cleared
Online log /u01/app/oracle/oradata/hisdb/group_8.275.976991863: Thread 2 Group 8 was previously cleared
Online log /u01/app/oracle/oradata/hisdb/group_8.2337.976991863: Thread 2 Group 8 was previously cleared
Online log /u01/app/oracle/oradata/hisdb/group_9.276.976991877: Thread 1 Group 9 was previously cleared
Online log /u01/app/oracle/oradata/hisdb/group_9.2334.976991877: Thread 1 Group 9 was previously cleared
Online log /u01/app/oracle/oradata/hisdb/group_10.277.976991891: Thread 2 Group 10 was previously cleared
Online log /u01/app/oracle/oradata/hisdb/group_10.2333.976991893: Thread 2 Group 10 was previously cleared
Standby became primary SCN: 35738445352
Wed Oct 23 21:55:21 2024
Setting recovery target incarnation to 3
ACTIVATE STANDBY: Complete - Database mounted as primary
Completed: ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE

参考:Activate Standby Database failed with ORA-00600: [krse_arc_complete.4] (Doc ID 2409336.1)

发表在 Data Guard | 标签为 , | 评论关闭

利用flashback快速恢复failover 的备库

发表于 2024 年 8 月 8 日 由 惜分飞

客户数据库架构为单机+dataguard,一台生产库跑在物理机,备库跑在虚拟化环境中(当时由于成本原因使用了机械盘),今天物理机突然直接罢工,客户要求紧急切换备库

Thu Aug 08 09:52:13 2024
Media Recovery Waiting for thread 1 sequence 189448 (in transit)
Recovery of Online Redo Log: Thread 1 Group 12 Seq 189448 Reading mem 0
  Mem# 0: /oradata/xff/std_redo12.log
Thu Aug 08 09:52:13 2024
Archived Log entry 187514 added for thread 1 sequence 189447 ID 0x2e6bc37f dest 1:
Thu Aug 08 10:54:40 2024
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH force
Terminal Recovery: Stopping real time apply
Thu Aug 08 10:54:40 2024
MRP0: Background Media Recovery cancelled with status 16037
Errors in file /u01/app/oracle/diag/rdbms/xffdg/xff/trace/xff_pr00_17876.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 34188310512
Thu Aug 08 10:54:43 2024
MRP0: Background Media Recovery process shutdown (xff)
Terminal Recovery: Stopped real time apply
Thu Aug 08 10:55:14 2024
Stopping background process MMNL
Stopping background process MMON
Thu Aug 08 10:55:46 2024
Background process MMON not dead after 30 seconds
Killing background process MMON
All dispatchers and shared servers shutdown
CLOSE: killing server sessions.
Active process 17691 user 'oracle' program 'oracle@xffDG (MMON)'
Active process 15077 user 'oracle' program 'oracle@xffDG'
Active process 17691 user 'oracle' program 'oracle@xffDG (MMON)'
Active process 11536 user 'oracle' program 'oracle@xffDG (M000)'
Active process 17691 user 'oracle' program 'oracle@xffDG (MMON)'
Active process 15077 user 'oracle' program 'oracle@xffDG'
Active process 11536 user 'oracle' program 'oracle@xffDG (M000)'
Active process 11536 user 'oracle' program 'oracle@xffDG (M000)'
Active process 11536 user 'oracle' program 'oracle@xffDG (M000)'
CLOSE: all sessions shutdown successfully.
Thu Aug 08 10:56:11 2024
SMON: disabling cache recovery
Attempt to do a Terminal Recovery (xff)
Media Recovery Start: Managed Standby Recovery (xff)
 started logmerger process
Thu Aug 08 10:56:13 2024
Managed Standby Recovery not using Real Time Apply
Parallel Media Recovery started with 4 slaves
Media Recovery Waiting for thread 1 sequence 189448 (in transit)
Killing 4 processes with pids 17733,17729,17731,32533 (all RFS, wait for I/O) 
in order to disallow current and future RFS connections. Requested by OS process 15184
Thu Aug 08 10:56:16 2024
idle dispatcher 'D000' terminated, pid = (16, 1)
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
Terminal Recovery timestamp is '08/08/2024 10:56:17'
Terminal Recovery: applying standby redo logs.
Terminal Recovery: thread 1 seq# 189448 redo required
Terminal Recovery:
Recovery of Online Redo Log: Thread 1 Group 12 Seq 189448 Reading mem 0
  Mem# 0: /oradata/xff/std_redo12.log
Identified End-Of-Redo (failover) for thread 1 sequence 189448 at SCN 0xffff.ffffffff
Incomplete Recovery applied until change 34188310513 time 08/08/2024 11:32:41
Thu Aug 08 10:56:18 2024
Media Recovery Complete (xff)
Terminal Recovery: successful completion
Thu Aug 08 10:56:18 2024
ARCH: Archival stopped, error occurred. Will continue retrying
Forcing ARSCN to IRSCN for TR 7:4123539441
ORACLE Instance xff - Archival Error
Attempt to set limbo arscn 7:4123539441 irscn 7:4123539441
Resetting standby activation ID 778814335 (0x2e6bc37f)
ORA-16014: log 12 sequence# 189448 not archived, no available destinations
ORA-00312: online log 12 thread 1: '/oradata/xff/std_redo12.log'
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH force
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
ORA-16136 signalled during: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL...
Thu Aug 08 10:56:28 2024
ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE
ALTER DATABASE ACTIVATE [PHYSICAL] STANDBY DATABASE (xff)
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
Thu Aug 08 10:56:28 2024
Archiver process freed from errors. No longer stopped
Standby terminal recovery start SCN: 34188310512
RESETLOGS after incomplete recovery UNTIL CHANGE 34188310513
Online log /oradata/xff/redo01.log: Thread 1 Group 1 was previously cleared
Online log /oradata/xff/redo02.log: Thread 1 Group 2 was previously cleared
Online log /oradata/xff/redo03.log: Thread 1 Group 3 was previously cleared
Online log /oradata/xff/redo04.log: Thread 1 Group 4 was previously cleared
Standby became primary SCN: 34188310511
Thu Aug 08 10:56:29 2024
Setting recovery target incarnation to 3
ACTIVATE STANDBY: Complete - Database mounted as primary
Completed: ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE
ARC1: Becoming the 'no SRL' ARCH
alter database open
Thu Aug 08 10:56:34 2024
Assigning activation ID 832379854 (0x319d1bce)
Thread 1 advanced to log sequence 2 (thread open)
Thread 1 opened at log sequence 2
  Current log# 2 seq# 2 mem# 0: /oradata/xff/redo02.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Thu Aug 08 10:56:34 2024
SMON: enabling cache recovery
Thu Aug 08 10:56:34 2024
ARC0: LGWR is scheduled to archive destination LOG_ARCHIVE_DEST_2 after log switch
Thu Aug 08 10:56:34 2024
NSA2 started with pid=14, OS id=15198
[15133] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:1087824580 end:1087828220 diff:3640 (36 seconds)
Dictionary check beginning
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Thu Aug 08 10:56:38 2024
Database Characterset is ZHS16GBK
Starting background process SMCO
Thu Aug 08 10:56:39 2024
SMCO started with pid=15, OS id=15200
Thread 1 advanced to log sequence 3 (LGWR switch)
  Current log# 3 seq# 3 mem# 0: /oradata/xff/redo03.log
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
Thu Aug 08 10:56:40 2024
Archived Log entry 187515 added for thread 1 sequence 2 ID 0x319d1bce dest 1:
Starting background process QMNC
Thu Aug 08 10:56:43 2024
QMNC started with pid=17, OS id=15204
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Completed: alter database open

很不幸由于虚拟机资源io太差,无法接管业务,硬件工程师紧急修复好物理机,启动数据库正常,客户直接把业务又切换到物理机中,现在需要恢复dataguard环境(并且客户把虚拟机迁移到ssd环境中),把虚拟机数据库重启到mount状态

[oracle@xffDG ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Aug 8 20:06:30 2024

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

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 2.5655E+10 bytes
Fixed Size                  2265224 bytes
Variable Size            3892318072 bytes
Database Buffers         2.1743E+10 bytes
Redo Buffers               16896000 bytes
Database mounted.
SQL> select open_mode,database_role from v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
MOUNTED              PRIMARY

闪回数据库到备库failover之前scn

SQL> flashback database to scn 34188310500;       

Flashback complete.

Thu Aug 08 20:09:40 2024
flashback database to scn 34188310500
Flashback Restore Start
Thu Aug 08 20:10:34 2024
Flashback Restore Complete
Flashback Media Recovery Start
Thu Aug 08 20:10:34 2024
Setting recovery target incarnation to 2
 started logmerger process
Parallel Media Recovery started with 4 slaves
Flashback Media Recovery Log /oradata/fast_recovery_area/XFF/archivelog/2024_08_08/o1_mf_1_189448_mc8dzjxn_.arc
Thu Aug 08 20:10:35 2024
Identified End-Of-Redo (failover) for thread 1 sequence 189448 at SCN 0x7.f5c837f1
Incomplete Recovery applied until change 34188310501 time 08/08/2024 11:32:40
Flashback Media Recovery Complete
Setting recovery target incarnation to 3
Completed: flashback database to scn 34188310500

切换虚拟机库到standby 状态

SQL> alter database convert to physical standby;

Database altered.

SQL> select database_role from v$database;
select database_role from v$database
                          *
ERROR at line 1:
ORA-01507: database not mounted


SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00750: database has been previously mounted and dismounted

SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 2.5655E+10 bytes
Fixed Size                  2265224 bytes
Variable Size            3892318072 bytes
Database Buffers         2.1743E+10 bytes
Redo Buffers               16896000 bytes
Database mounted.
SQL>  select open_mode,database_role from v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
MOUNTED              PHYSICAL STANDBY

Thu Aug 08 20:10:46 2024
alter database convert to physical standby
ALTER DATABASE CONVERT TO PHYSICAL STANDBY (xff)
Flush standby redo logfile failed:1649
Clearing standby activation ID 832379854 (0x319d1bce)
The primary database controlfile was created using the
'MAXLOGFILES 16' clause.
There is space for up to 12 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 209715200;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 209715200;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 209715200;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 209715200;
ALTER DATABASE ADD STANDBY LOGFILE 'srl5.f' SIZE 209715200;
Shutting down archive processes
Archiving is disabled
Completed: alter database convert to physical standby

开启mrp进程

SQL> alter database open read only;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT  LOGFILE DISCONNECT FROM SESSION;

Database altered.
发表在 Data Guard | 标签为 | 评论关闭

WINDOWS 下用dg broker搭建ADG（单机to单机）

发表于 2024 年 5 月 8 日 由 惜分飞

环境

#主备库 C:\Windows\System32\drivers\etc\hosts 文件
192.168.11.10  dg1
192.168.11.11  dg2
#环境
主库主机名：dg1 现有实例orcl
备库主机名：dg2 只安装软件

一，主库配置
–主库设置强制日志，保证所有的操作都记录到日志文件
–查看当前force\_logging的设置

#主库如果已开启归档，不需要停机
sqlplus / as sysdba
select force_logging from v$database;
select flashback_on from v$database;
alter database force logging; -- 开启强制日志模式
#######################################################
#如果没开归档
sqlplus / as sysdba
shudown immediate;
startup mount;
alter database archivelog; -- 开启归档模式
alter database force logging; -- 开启强制日志模式
#alter database flashback on;  -- 开启闪回，不是必须，推荐开启
#######################################################
#主库添加standby日志组
#查看日志文件大小 select bytes/1024/1024 from v$log;这里是50M
alter database add standby logfile group 10 ('D:\app\Administrator\oradata\orcl\standby_redo01.log') size 50m;
alter database add standby logfile group 11 ('D:\app\Administrator\oradata\orcl\standby_redo02.log') size 50m;
alter database add standby logfile group 12 ('D:\app\Administrator\oradata\orcl\standby_redo03.log') size 50m;
alter database add standby logfile group 13 ('D:\app\Administrator\oradata\orcl\standby_redo04.log') size 50m;
########################################################
#设置文件管理自动
alter system set standby_file_management=auto;

二、主备库网络设置

#主库listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1)
      (SID_NAME = orcl)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = orcl_DGMGRL)  #用于dg broker的静态监听
      (ORACLE_HOME = D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1)
      (SID_NAME = orcl)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dg1)(PORT = 1521))
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
  )
#主库tnsnames.ora
ORCL_STBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dg2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dg1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
  
#备库listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1)
      (SID_NAME = orcl)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = orcl_stby_DGMGRL) #用于dg broker的静态监听
      (ORACLE_HOME = D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1)
      (SID_NAME = orcl)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dg2)(PORT = 1521))
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
  )
#备库tnsnames.ora
ORCL_STBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dg2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = dg1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
#主库监听reload
lsnrctl reload
#备库启动监听
lsnrctl start

三、备库配置

#创建一个临时参数文件如d:\pfile.txt内容如下
*.db_name='orcl'
#创建密码文件，或者从主库拷贝一个
orapwd file=D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\database\PWDorcl.ora password=oracle entries=10
#创建备库所需目录
mkdir D:\app\Administrator\oradata\orcl\
mkdir D:\app\Administrator\admin\orcl\adump\
mkdir D:\app\Administrator\fast_recovery_area\orcl\
#用ORADIM创建实例
oradim -new -sid orcl
#用临时参数文件启动
sqlplus / as sysdba
starup nomount pfile='d:\pfile.txt';

四、RMAN duplicate创建备库

#tnsping测试互通性
tnsping orcl
tnsping orcl_stby
#主库连接备库
sqlplus sys/oracle@stddb as sysdba
#备库连接主库
sqlplus sys/oracle@orcl as sysdba
#########################################################
#备库执行，连接主备库
rman target sys/oracle@orcl auxilary sys/oracle@orcl_stby
#创建dg备库，这里假设主备库路径相同
duplicate target database
  for standby
  from active database
  dorecover
  spfile
    set db_unique_name='orcl_stby'
  nofilenamecheck;
#########################################################
#如果主备库路径不同
duplicate target database
  for standby
  from active database
  dorecover
  spfile
    set db_unique_name='orcl_stby'
    set db_file_name_convert='orcl','orcl_stby'
    set log_file_name_convert='orcl','orcl_stby'
    set job_queue_processes='0'
  nofilenamecheck;
#开启ADG
sqlplus / as sysdba
alter database open read only;
alter database recover managed standby database disconnect from session;

五、配置DG BROKER

#主备库两边执行
alter system set dg_broker_start=true;
#主库连接dgmgrl
dgmgrl sys/oracle@orcl
#创建dg broker配置
create configuration dg_config as primary database is orcl connect identifier is orcl;
#添加备库到配置文件
add database orcl_stby as connect identifier is orcl_stby;
#启用配置
enable configuration;

############################################################################
#显示DG配置信息
show configuration
show configuration verbose
#显示主备库信息
show database orcl
show database orcl_stby
show database verbose orcl
show database verbose orcl_stby

六、一些测试

#测试Database Switchover
dgmgrl sys/oracle@orcl
switchover to orcl_stby;
show configuration
#切换回来
switchover to orcl;
show configuration
###########################################################################
#测试Database Failover，此时dg关系已经打破
dgmgrl sys/oracle@orcl
failover to orcl_stby;
#如果主库开启了flashback，执行以下语句自动重建主库
 reinstate database orcl;
#如果没有开启flashback，删除重建主库，重新建立dg关系
############################################################################
#测试快照备库
dgmgrl sys/oracle@orcl
convert database orcl_stby to snapshot standby;
show configuration;
#快照转成正常备库
convert database orcl_stby to physical standby;
show configuration;

七、总结
优点在于除监听设置外主备库都不需要做过多的设置，备库临时参数文件只需要一个dbname，其余dg有关的参数dg broker会自动设置。
八、参考资料
ORACLE-BASE – Data Guard Physical Standby Setup Using the Data Guard Broker in Oracle Database 11g Release 2

发表在 Data Guard | 评论关闭