DG学习笔记(7)_保护模式

目录

1 Set the Protection Mode

2 MAXimum Protection

3 Maximum Availability

4 Maximum Performance

 

1 Set the Protection Mode

 

Each level of protection requires certain attributes of the LOG_ARCHIVE_DEST_n be set properly. For each level of protection you must have at least one standby database with the following:

• Maximum Protection: LGWR, SYNC, AFFIRM, and standby redo logs files

 

• Maximum Availability: LGWR, SYNC, AFFIRM, and standby redo logs files for physical standby databases

 

• Maximum Performance, any combination of: LGWR or ARCH

 

alter system set log_archive_dest_2='service=standby lgwr SYNC NOAFFIRM';

 

Using the following SQL SET STANDBY DATABASE statement on the primary database, you can configure the Data Guard environment to maximize data protection, availability, or performance:

 

ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE {PROTECTION | AVAILABILITY | PERFORMANCE};

 

2 MAXimum Protection

 

优势:

Guarantees zero data loss

Highest level of data protection

Must use SYNC transport method

Must use standby redo logs

If no physical standby available, primary database is aborted

 

A transaction does not commit until all data needed to recover it has been written to at least one physical standby database that is configured to use the SYNC log transport mode.(提交之前必须将数据写到了备端,这是不是有问题, 不提交,数据不能保证被写到了Online redo log,不会有问题, 意思即是在Primary提交完成之前,Standby redo log 也必须已记录日志变化.)

 

 

最大保护: 按理解,应是写online日志的过程中也同步变化到备端,不用通过切换日志来触发.

即在Primary DML数据,Commit. Standby也会更改数据. 不用通过切换日志来触发. 是这样吗? 不是的, 如不通过切换来触发不会应用日志,

那这样的话有什么意义呢, 还是说,对同步的理解有误,只是说将变化传到了备端,但不应用. 这才是正确的,只是保证你数据传过去了,不会丢失数据而已.

注意只是保证数据不会丢失，而不是保证数据已应用到Standby. 实际的情况是会保证数据传输到Standby，但不一定应用．

 

You must define at least one standby site destination with these attributes of the LOG_ARCHIVE_DEST_n parameter: MANDATORY, LGWR, SYNC, and AFFIRM. If the primary database is unable to write the redo records to at least one such physical standby database, the primary database will be shut down.

(这四个属性中LGWR, SYNC是必须的)

 

 

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

 

Total System Global Area  320308744 bytes

Fixed Size                   742920 bytes

Variable Size             285212672 bytes

Database Buffers           33554432 bytes

Redo Buffers                 798720 bytes

Database mounted.

 

尝试用NOAFFIRM

SQL> alter system set log_archive_dest_2='service=standby lgwr SYNC NOAFFIRM';

 

System altered.

 

SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;

 

Database altered.

 

SQL> alter database open;

 

Database altered.

成功

 

 

尝试用ASYNC

SQL> alter system set log_archive_dest_2='service=standby lgwr ASYNC NOAFFIRM';

 

 

System altered.

 

SQL> SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION;

 

Database altered.

 

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-03113: end-of-file on communication channel

 

失败

 

在实际测试中:

如果Shutdown abort Standby,则Primary也随之被Shutdown.

 

在尝试正常关闭Shutdown immediate Standby Database时, 发现关闭不了，在Standby alert_xxx.log中,发现

alter database recover managed standby database cancel

Mon Mar  8 17:16:30 2010

MRP0: Background Media Recovery user canceled with status 16037

Recovery interrupted.

MRP0: Background Media Recovery process shutdown

Mon Mar  8 17:16:31 2010

Managed Standby Recovery Cancelled

Completed:      alter database recover managed standby database c

Attempt to shut down Standby Database

Standby Database operating in NO DATA LOSS mode

Detected primary database alive, shutdown primary first, shutdown aborted

 

表明在关闭Primary之前,不能正常关闭Standby.

 

在关闭Primary,Standby后,尝试启动Primary,查看Primary Alert_xxx.log

 

Alert_xxx.log

LGWR: Primary database is in CLUSTER CONSISTENT mode

LGWR: Primary database is in MAXIMUM PROTECTION mode

LGWR: Destination LOG_ARCHIVE_DEST_1 is not serviced by LGWR

LNS0 started with pid=15

Mon Mar  8 22:10:39 2010

LGWR: Error 12505 verifying archivelog destination LOG_ARCHIVE_DEST_2

LGWR: Continuing...

Mon Mar  8 22:10:39 2010

Errors in file /opt/oracle/admin/mydb/bdump/mydb_lgwr_13090.trc:

ORA-12505: TNS:listener could not resolve SID given in connect descriptor

LGWR: Minimum of 1 applicable standby database required

Mon Mar  8 22:10:39 2010

Errors in file /opt/oracle/admin/mydb/bdump/mydb_lgwr_13090.trc:

ORA-16072: a minimum of one standby database destination is required

LGWR: terminating instance due to error 16072

Instance terminated by LGWR, pid = 13090

 

表明在最大保护模式,至少必须有一个Standby是启动的,才能启动Primary.

 

Note: Oracle Corporation recommends that you use multiple physical standby databases when your business requires maximum data protection. With multiple physical standby databases, if one becomes unavailable, the primary database can continue operations as long as at least one physical standby database is participating in the configuration.

 

所以Oracle建议如果是配置MAXIMUN Protection Mode, 则Standby database 最好有两个以上.

 

 

3 Maximum Availability

 

优势:

Zero data loss

Next highest level of data protection

Must use SYNC transport method

Must use standby redo logs for physical standby database

If no standby available, primary database is not shut down

 

Unlike Maximum Protection mode, the primary database will not be shut down if it is unable to write the redo records to at least one such standby database. Instead, the primary database will run in Maximum Performance mode until the fault has been corrected and the standby database has caught up with the primary database. Then it will return to Maximum Availability.

(不像最大保护模式,在最大可用性模式中，备库的崩溃不会导致主库也随之崩溃,只会自动切换到最大性能模式)

 

切换到Maximum Availability

Primay:

 

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

 

Total System Global Area  320308744 bytes

Fixed Size                   742920 bytes

Variable Size             285212672 bytes

Database Buffers           33554432 bytes

Redo Buffers                 798720 bytes

Database mounted.

SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE availability;

 

Database altered.

 

2.1 Shutdown Abort Standby database 观察Primary的情况

 

Standby: SQL> shutdown abort;

ORACLE instance shut down.

 

主库要多久才能检测到呢?

 

Primary:  SQL> select protection_mode from v$database;

 

PROTECTION_MODE

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

MAXIMUM AVAILABILITY

 

仍是最大可用.

因为你设置的就是最大可用性模式，虽然正常情况下其表现出来的与最大保护模式一致，只有在备库崩溃时才会切换到最大性能模式的工作方式．

 

过一段时间在alert_xxx.log中发现,检测到了对端的错误

 

Network asynch I/O wait error 3114 log 2 service 'standby'

Mon Mar  8 22:35:55 2010

Errors in file /opt/oracle/admin/mydb/bdump/mydb_lgwr_13820.trc:

ORA-03114: not connected to ORACLE

 

Primary:  SQL> select protection_mode from v$database;

 

PROTECTION_MODE

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

MAXIMUM AVAILABILITY

 

仍是最大可用,

 

2.2 恢复Standby database 到正常状态

 

 

4 Maximum Performance

 

优势:

Default level of data protection

Can use SYNC, ASYNC, or ARCH transport method

Can use standby redo logs

No write required to any physical or logical standby database before commit

If no standby available, primary database is not shutdown

Data loss is possible

 

A transaction commits before the data needed to recover it has been written to a (physical or logical) standby database. (事务提交后再传输到Standby)