返回
软件
分类

最大性能模式(Maximum,备库不能开启实时查询)

日期: 2020-04-07 06:14 浏览次数 : 81

 

Oracle Dataguard一共支持三种模式:最大可用模式(Maximum Availability),最大性能模式(Maximum Performance),最大保护模式(Maximum Protection)。默认创建的是最大性能模式(Maximum Performance)。关于三者的区别,我们来看官方的定义。

情景:利用Dataguard滚动方式升级数据库后,备库应用redo报错:ORA-10485

1.主库在进行物理主备库角色转换的时候遇到ORA-01093错误

SQL> select switchover_status from v$database;  

Maximum Availability

 

SWITCHOVER_STATUS

SESSIONS ACTIVE             ----------有活动会话。

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY
*
ERROR at line 1:
ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected

此错误的由于主库中有活动的会话造成的。解决的办法为:
1.杀掉活动会话,又一次运行该命令
2.在该命令后面加上with session shutdown即
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

Transactions do not commit until all redo data needed to recover those transactions has been written to the online redo log and to the standby redo log on at least one synchronized standby database.If the primary database cannot write its redo stream to at least one synchronized standby         database,it operates as if it were in maximum performance mode to preserve primary database availability until it is again able to write its redo stream to a synchronized standby database.

MRP0: Background Media Recovery terminated with error 10485
Errors in file /home/u01/app/diag/rdbms/orcl11g/ORCL/trace/ORCL_pr00_1644.trc:
ORA-10485: Real-Time Query cannot be enabled while applying migration redo.
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
MRP0: Background Media Recovery process shutdown (ORCL)

2.备库在转换为主库时状态为:NOT ALLOWED--可能须要恢复或者退出恢复模式。

SQL> select switchover_status from v$database;

Maximum Performance

[oracle@app scripts]$ oerr ora 10485
10485, 00000, "Real-Time Query cannot be enabled while applying migration redo."
// *Cause: The Real-Time Query feature was enabled when an attempt was made
// to recover through migration redo generated during primary upgrades or
// downgrades.
// *Action: Close the standby database in order to recover through
// migration redo. Reenable the Real-Time Query feature afterwards.

SWITCHOVER_STATUS

NOT ALLOWED
SQL> alter database commit to switchover to primary;
alter database commit to switchover to primary
*
ERROR at line 1:
ORA-16139: media recovery required
或者例如以下:--RECOVERY NEEDED
SQL> select switchover_status from v$database;

This is accomplished by allowing transactions to commit as soon as all redo data generated by those transactions has been written to the online log. Redo data is also written to one of more standby databases,but this is done asynchronously with respect to transaction commitment,so primary       database performance is unaffected by delays in writing redo data to the standby databases.

 

SWITCHOVER_STATUS

RECOVERY NEEDED
###############
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> select switchover_status from v$database;

Maximum Protection

必赢备用网址 ,原因:在应用migration redo的时候不能开启实时查询(即,在主库进行升级时候,备库不能开启实时查询)

SWITCHOVER_STATUS

TO PRIMARY
SQL> alter database commit to switchover to primary;
Database altered.

This protection mode ensures that no data loss will occur if the primary database fails.To provide this level of protection,the redo data needed to      recover a transaction must be written to both the online redo log and to the standby redo on at least one synchronized standby database before the transaction commits.To ensure that data loss cannot occur,the primary database will shut down,rather than continue processing transactions,if it     cannot write its redo stream to at least one synchronized standby database.

解决方法:

3.主库切换为备库后,备库不做:alter database recover managed standby database cancel;也能够,此时已经自己主动断开了,日志中有提示:

Sun Apr 20 20:15:35 2014
Media Recovery Log /u01/app/oracle/prod/arch/1_62_844894247.dbf
Media Recovery Waiting for thread 1 sequence 63
Sun Apr 20 20:15:46 2014
Redo Shipping Client Connected as PUBLIC
-- Connected User is Valid
RFS[6]: Assigned to RFS process 19968
RFS[6]: Identified database type as 'physical standby'
RFS[6]: Archived Log: '/u01/app/oracle/prod/arch/1_63_844894247.dbf'
Sun Apr 20 20:15:50 2014
Media Recovery Log /u01/app/oracle/prod/arch/1_63_844894247.dbf
Identified End-Of-Redo for thread 1 sequence 63
Sun Apr 20 20:15:50 2014
Media Recovery End-Of-Redo indicator encountered
Sun Apr 20 20:15:50 2014
Media Recovery Applied until change 373573   ---最后应用到的SCN
Sun Apr 20 20:15:50 2014
MRP0: Media Recovery Complete: End-Of-REDO (PROD)
Resetting standby activation ID 257246883 (0xf5546a3)
Sun Apr 20 20:15:51 2014
MRP0: Background Media Recovery process shutdown (PROD)----MEDIA RECOVERY PROCESS已经关闭
Sun Apr 20 20:16:21 2014
alter database recover managed standby database cancel
Sun Apr 20 20:16:21 2014
ORA-16136 signalled during: alter database recover managed standby database cancel...

三种模式对于日志传输的要求如下:

shutdown immediate;

必赢备用网址 1

startup mount;

在前篇文章中-《Oracle Dataguard之物理standby的基本配置》,我们最后配置出来的是Physical Standby Database 最大性能模式下的异步传输,现在我们配置该模式下的Real-Time Apply。启用Real-Time Apply需要配置standby redo log,而这也是其它两种模式的基础。

alter database recover managed standby database disconnect from session;

一、 创建standby redo log

--待migration redo 相关的日志应用了后再开启实时apply

      1> 查询主库上online redo log的组数和大小   -->> node1 上操作

alter database recover managed standby database cancel;

         SQL> select group#,bytes/1024/1024 "size" from v$log;

alter database open;

         GROUP#  size
         ------     ----------
         1             50
         2             50
         3             50

alter database recover managed standby database using current logfile disconnect from session;

      2> 在备库上添加standby redo log   -->> group比主库多一组,大小和主库一样,node2上操作

 

         [oracle@node2 ~]$ mkdir /u01/standby

         SQL> alter database add standby logfile '/u01/standby/standby01.log' size 50M;

         SQL> alter database add standby logfile '/u01/standby/standby02.log' size 50M;

         SQL> alter database add standby logfile '/u01/standby/standby03.log' size 50M;

         SQL> alter database add standby logfile '/u01/standby/standby04.log' size 50M;

         注意:请确保已关闭Redo Apply,不然会报以下错误

                 SQL> alter database add standby logfile '/u01/standby/standby01.log' size 50M;

                 alter database add standby logfile '/u01/standby/standby01.log' size 50M
                 *
                 ERROR at line 1:
                 ORA-01156: recovery or flashback in progress may need access to files

         关闭Redo Apply,

                SQL> alter database recover managed standby database cancel;

二、 修改主库的配置参数  -->> node1 上操作

          SQL> alter system set log_archive_dest_2='service=to_victor lgwr affirm sync valid_for=(online_logfiles,primary_role) db_unique_name=victor';

          SQL> alter system switch logfile;

三、 在备库上启用Real-Time Apply  -->> node2 上操作

          SQL> alter database recover managed standby database using current logfile disconnect from session;

四、 测试

       除了用前篇文章中,查看归档日志的应用情况来验证dataguard的配置以外,本文将采用实际案例进行测试。 

      1> 在主库上新建一个测试表

           SQL> create table test(id number);

           SQL> insert into test values(1);

      2> 在备库中检测

          SQL> select * from test;

          no rows selected

      3> 在主库中提交事务

          SQL> commit;

      4> 在备库中检测

          SQL> select * from test;

          ID
          ----------
          1

总结:

      这个总结包括物理standby的基本配置和本文中的Real-Time Apply

     1> 如果只设置主/次两个本地归档目的地,可以使用初始化参数log_archive_dest指定主归档目的地,使用初始化参数log_archive_duplex_dest指定次归档目的地。

          在数据库中,初始换参数log_archive_dest和log_archive_duplex_dest与log_archive_dest_n只能使用一组来设置归档目的地,不能同时使用

     2> 备库log_archive_dest_1如果没有显性指定,默认的归档目录将是$ORACLE_HOME/dbs.倘若显性指定,但valid_for不是

          (standby_logfiles,standby_role)或者(all_logfiles,all_roles),则该设置无效,报警日志中将报以下错误:

            ORA-16032: parameter STANDBY_ARCHIVE_DEST destination string cannot be translated

            归档目录将继续为$ORACLE_HOME/dbs

      3> 在本文中,log_archive_dest_1='location=/u01/archivelog valid_for=(standby_logfiles,standby_role) db_unique_name=victor',如果再显性指定

            log_archive_dest_3='location=/u01/standbyarchive valid_for=(standby_logfiles,standby_role) db_unique_name=victor',将会有两份standby的

            归档日志产生,没有必要,只需要一个log_archive_dest_1即可

      4> 最大性能模式下,如果是async,即异步,则需要主库切一次日志,备库采用应用。而如果启用Real-Time Apply,则只需要主库事务commit, 备库就能应用

      5> 删除日志  

           SQL> alter database drop logfile group 7;

      6> 如果发出了alter database recover managed standby database cancel;这个命令,MRP(Media Recovery process)将停止工作,但

           RFS(Remote file server)仍继续工作

      7> 如果没有standby redo logs,是不能启动real time apply的

           SQL> alter database recover managed standby database using current logfile disconnect from session;
           alter database recover managed standby database using current logfile disconnect from session
           *
           ERROR at line 1:
           ORA-38500: USING CURRENT LOGFILE option not available without standby redo logs

      8> standby数据库startup后,没有发出alter database recover managed standby database disconnect from session这个命令,RFS仍然工作,只要监听

           都ok

      9> disconnect from session代表这个命令后台运行。

      10> Real-Time Apply的原理图

            必赢备用网址 2