dataguard 最大保护模式下的 failover-优快云博客

本文详细记录了在Oracle数据库中从最大性能模式切换至最大保护模式的过程，包括启动数据库、添加备用日志文件、设置归档日志目标、激活备用数据库等关键步骤，并验证了模式切换的有效性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

测试如下

测试版本： 9。2。0。8

[@more@]

建立最大保护模式

最大性能模式修改成最大保护模式：

SQL> alter database add standby logfile GROUP 4 ('/u02/oracle/uassdb/stdy_redo04.log') SIZE 300m;
alter database add standby logfile GROUP 4 ('/u02/oracle/uassdb/stdy_redo04.log') SIZE 300m
*
ERROR at line 1:
ORA-01156: recovery in progress may need access to files

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> startup nomount pfile='/export/home/oracle/init_switch/initstandby.ora'
ORACLE instance started.

Total System Global Area 6291456000 bytes
Fixed Size 1987968 bytes
Variable Size 1090521728 bytes
Database Buffers 5184159744 bytes
Redo Buffers 14786560 bytes
SQL> alter database mount standby database;

Database altered.

SQL> alter database add standby logfile GROUP 4 ('/trunkbow/oracle/oradata/mmcdb/stdy_redo04.log') SIZE 512m;

Database altered.

SQL> alter database add standby logfile GROUP 5 ('/trunkbow/oracle/oradata/mmcdb/stdy_redo05.log') SIZE 512m;

Database altered.

SQL> alter database add standby logfile GROUP 7 ('/trunkbow/oracle/oradata/mmcdb/stdy_redo07.log') SIZE 512m;

Database altered.

SQL> alter database add standby logfile GROUP 7 ('/trunkbow/oracle/oradata/mmcdb/stdy_redo07.log') SIZE 512m;

Database altered.

SQL> recover managed standby database disconnect from session;
Media recovery complete.
SQL>

启动到managed recovery模式
SQL> recover managed standby database disconnect from session;
保证备用库的监听正常，保证主数据库到备用数据库的连接正常。

8.3 转换模式由最大性能到最大保护
在主库上，检查当前模式
SQL> select protection_mode from v$database;

PROTECTION_MODE
--------------------
MAXIMUM PERFORMANCE

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 6291456000 bytes
Fixed Size 1987968 bytes
Variable Size 1090521728 bytes
Database Buffers 5184159744 bytes
Redo Buffers 14786560 bytes
Database mounted.
SQL> alter system set log_archive_dest_2='SERVICE=mmcdbbak LGWR SYNC AFFIRM' scope=both;

System altered.

SQL> alter database set standby database to maximize protection;

Database altered.

SQL> alter database open;

Database altered.

SQL> select protection_mode from v$database;

PROTECTION_MODE
--------------------
MAXIMUM PROTECTION

建立测试数据：

SQL> connect wrj/wrj
Connected.
SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
WRJ

SQL> create table wrj2 as select * from wrj;

Table created.

SQL> create table wrj3 as select * from wrj;

Table created.

SQL> create table wrj4 as select * from wrj;

Table created.

SQL> delete from wrj4;

28444 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from wrj;

COUNT(*)
----------
28444

SQL> select count(*) from wrj1;
select count(*) from wrj1
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> select count(*) from wrj2;

COUNT(*)
----------
28444

SQL> select count(*) from wrj4;

COUNT(*)
----------
0

备用库：

$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.8.0 - Production on Fri Jan 11 13:58:10 2008

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL>
SQL> select OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS from v$database;

OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS
---------- -------------------- -------------------- ------------------
MOUNTED MAXIMUM PROTECTION MAXIMUM PROTECTION NOT ALLOWED

SQL>
SQL>
SQL> select * from v$archive_gap;

no rows selected

SQL> select unique thread#,max(sequence#) over(partition by thread#) as last from v$archived_log;

THREAD# LAST
---------- ----------
1 26

SQL> recover managed standby database cancel;
Media recovery complete.
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;

Database altered.

SQL> alter database recover managed standby database finish;
alter database recover managed standby database finish
*
ERROR at line 1:
ORA-16043: managed recovery session canceled
ORA-16137: No terminal recovery is required

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> SQL> startup nomount;
ORACLE instance started.

Total System Global Area 3242987696 bytes
Fixed Size 733360 bytes
Variable Size 1174405120 bytes
Database Buffers 2063597568 bytes
Redo Buffers 4251648 bytes
SQL> alter database mount standby database;

Database altered.

SQL>
SQL> alter database activate standby database;

Database altered.

SQL> alter database mount;

Database altered.

SQL> alter database open;

Database altered.

SQL>
SQL> connect wrj/wrj
Connected.
SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
WRJ
WRJ2
WRJ3
WRJ4

SQL> select count(*) from wrj;

COUNT(*)
----------
28444

SQL> select count(*) from wrj3;