Contents

DataGuard New Features
1. New 11.2 Features Specific to Redo Apply
前提条件
1. 主服务器信息
2. 备服务器信息
主服务器配制
备数据库服务器操作

DataGuard New Features

Support for XMLType data stored as binary XML
Support for XMLType data stored in object-relational format
A Data Guard configuration can now consist of a primary database and up to 30 standby databases.
As of Oracle Database 11g Release 2 (11.2.0.2), Oracle Data Guard is fully integrated with Oracle Real Application Clusters One Node (Oracle RAC One Node).
The FAL_CLIENT database initialization parameter is no longer required.
The default archive destination used by the Oracle Automatic Storage Management (Oracle ASM) feature and the fast recovery area feature has changed from LOG_ARCHIVE_DEST_10 to LOG_ARCHIVE_DEST_1
Redo transport compression is no longer limited to compressing redo data only when a redo gap is being resolved. When compression is enabled for a destination, all redo data sent to that destination is compressed.
The new ALTER SYSTEM FLUSH REDO SQL statement can be used at failover time to flush unsent redo from a mounted primary database to a standby database, thereby allowing a zero data loss failover to be performed even if the primary database is not running in a zero data loss data protection mode.
Support for both these storage formats requires that the primary database be running Oracle Database 11g Release 2 (11.2.0.3) or higher with a redo compatibility setting of 11.2.0.3 or highe

New 11.2 Features Specific to Redo Apply

You can configure apply lag tolerance in a real-time query environment by using the new STANDBY_MAX_DATA_DELAY parameter.

You can use the new ALTER SESSION SYNC WITH PRIMARY SQL statement to ensure that a suitably configured physical standby database is synchronized with the primary database as of the time the statement is issued.

The V$DATAGUARD_STATS view has been enhanced to a greater degree of accuracy in many of its columns, including apply lag and transport lag.

You can view a histogram of apply lag values on the physical standby. To do so, query the new V$STANDBY_EVENT_HISTOGRAM view.

A corrupted data block in a primary database can be automatically replaced with an uncorrupted copy of that block from a physical standby database that is operating in real-time query mode. A corrupted block in a physical standby database can also be automatically replaced with an uncorrupted copy of the block from the primary database.

前提条件

db_unique_name 在主备服务器不能相同。
hosts文件中定义各自的主机名

主服务器信息

OEL/RHEL 6.6
Server name: hdwms
IP: 192.168.81.1
Oracle 11.2.0.4 software with oracle instance
Oracle SID/Global_name: hdwms / card
Oracle db_unique_name: hdwms

备服务器信息

OEL/RHEL 6.6
Server name: hdwms-stb
IP: 192.168.81.2
Oracle 11.2.0.4 software only
Oracle SID/Global_name: hdwms / ec
Oracle db_unique_name: hdwmsstb

主服务器配制

启用归档

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;

确认归档配制
SQL> archive log list;

启用logging

SQL> ALTER DATABASE FORCE LOGGING;

检查初始化参数

SQL> show parameter db_name
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      hdwms

SQL> show parameter db_unique_name
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      hdwms

配制LOG_ARCHIVE_CONFIG参数

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(hdwms,hdwmsstb)';

确认LOG_ARCHIVE_CONFIG配制
SQL> show parameter LOG_ARCHIVE_CONFIG

设置对端LOG ARCHIVE

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=hdwmsstb NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=hdwmsstb';

确认以上配制
SQL> show parameter LOG_ARCHIVE_DEST_2

启用LOG_ARCHIVE_DEST_2
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

确认状态
SQL> show parameter LOG_ARCHIVE_DEST_STATE_2;

设置LOG ARCHIVE

ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30;
ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

show parameter log_archive_format;
show parameter log_archive_max_processes;
show parameter remote_login_passwordfile;

设置fal_server

ALTER SYSTEM SET FAL_SERVER=hdwmsstb;
ALTER SYSTEM SET DB_FILE_NAME_CONVERT='hdwmsstb','hdwms' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='hdwmsstb','hdwms'  SCOPE=SPFILE;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

show parameter fal_server;
show parameter standby_file_management;

配制listener.ora & tnsnames.ora

tnsnames.ora

HDWMS =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = HDWMS)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = HDWMS.WORLD)
    )
  )

HDWMSSTB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = HDWMSSTB)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = HDWMS.WORLD)
    )
  )

listener.ora

SID_LIST_LISTENER =
 (SID_LIST =
  (SID_DESC =
    (GLOBAL_DBNAME = HDWMS)
  (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
  (SID_NAME = HDWMS)
) )
  LISTENER =
   (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = HDWMS)(PORT = 1521))
   )
ADR_BASE_LISTENER = /u01/app/oracle

确认主备都能ping通

tnsping HDWMS
tnsping HDWMSSTB

备份主数据库

$ rman target /

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/backup/rman/full_%u_%s_%p';
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

# 将备份到Flash Recovery Area.

为备数据库创建控制文件和PFILE

ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/hdwmsstb.ctl';
CREATE PFILE='/tmp/inithdwmsstb.ora' FROM SPFILE;

修改PFILE /tmp/inithdwmsstb.ora

*.db_unique_name='hdwmsstb'
*.fal_server='hdwms'
*.log_archive_dest_2='SERVICE=hdwms ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=hdwms'

*.db_create_online_log_dest_1='+DATA'
*.db_create_online_log_dest_2='/localdist'

首先登录备数据库创建以下目录

mkdir -p /u01/app/oracle/oradata/hdwms
mkdir -p /u01/app/oracle/fast_recovery_area/hdwms
mkdir -p /u01/app/oracle/admin/hdwms/adump

复制两个文件至备数据库

scp /tmp/inithdwmsstb.ora oracle@192.168.81.2:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/
scp /tmp/hdwmsstb.ctl oracle@192.168.81.2:/u01/app/oracle/oradata/hdwms/control01.ctl
scp /tmp/hdwmsstb.ctl oracle@192.168.81.2:/u01/app/oracle/flash_recovery_area/hdwms/control02.ctl

复制password file和备份文件

cd /u01/app/oracle/product/11.2.0/dbhome_1/dbs/
scp orapwhdwms oracle@192.168.81.2:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/

cd /u01/app/oracle/flash_recovery_area/
scp -r hdwms oracle@192.168.81.2:/u01/app/oracle/flash_recovery_area/

备数据库服务器操作

注：可选？！首先更改db_unique_name为对应的名称。

alter system set db_unique_name='orclstb' SCOPE=SPFILE;
select db_unique_name from v$database;
# OR:
show parameter unique;

确认listener启动正常

lsnrctl status

从备份中恢复数据库

恢复控制文件

export ORACLE_SID=hdwms
sqlplus / as sysdba

SQL> CREATE SPFILE FROM PFILE='/tmp/inithdwmsstb.ora';

恢复数据库

export ORACLE_SID=hdwms
rman target /

RMAN> STARTUP MOUNT;
RMAN> list backup of database summary;
RMAN> RESTORE DATABASE;

Note: Recovery of database would be failed with RMAN-06054 error, We can ignore it because RMAN will ask for unknown archive log ( i.e. next archive log sequence, i.e. 10 ) who is not also available on Primary database.

Error log: RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 10 and starting SCN of 1009554

创建日志文件

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;
ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/oradata/hdwms/online_redo01.log') SIZE 200M;
ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/oradata/hdwms/online_redo02.log') SIZE 200M;
ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/oradata/hdwms/online_redo03.log') SIZE 200M;
ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/oradata/hdwms/online_redo04.log') SIZE 200M;
ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/oradata/hdwms/online_redo05.log') SIZE 200M;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

在主和备数据库中创建STANDBY LOGFILE

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='MANUAL';
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/hdwms/standby_redo01.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/hdwms/standby_redo02.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/hdwms/standby_redo03.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/hdwms/standby_redo04.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/hdwms/standby_redo05.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/hdwms/standby_redo06.log') SIZE 200M;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO';

应用传输进程

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY DISCONNECT FROM SESSION;

# Foreground redo apply. Session never returns until cancel. 
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;

# Background redo apply. Control is returned to the session once the apply process is started.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

停止传输

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

确认Dataguard保护模式

默认为 maximum performance 模式

SELECT protection_mode FROM v$database;

select name,open_mode,database_role from v$database;
select message from v$dataguard_status;
select dest_name,status,error from v$archive_dest where dest_name='LOG_ARCHIVE_DEST_2';

测试主备服务器ARCHIVELOG

主:

ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

SELECT sequence#, first_time, next_time
FROM   v$archived_log
ORDER BY sequence#;

ALTER SYSTEM SWITCH LOGFILE;

备:

ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

SELECT sequence#, first_time, next_time, applied
FROM   v$archived_log
ORDER BY sequence#;

主备数据库切换操作

手动从主hdwms切换至备hdwmsstb

CONNECT / AS SYSDBA
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;

关闭主hdwms数据库，挂载主数据库为“备”数据库工作

SHUTDOWN IMMEDIATE;
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

切换回去

备数据库(hdwmsstb)服务器切换至主服务器。

CONNECT / AS SYSDBA
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
SHUTDOWN IMMEDIATE;
STARTUP;

主数据库故障手动切换操作

在备hdwmsstb数据库执行

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
ALTER DATABASE ACTIVATE STANDBY DATABASE;

ALTER SYSTEM SET log_archive_dest_state_2 = DEFER;

Read-Only Standby and Active Data Guard

To switch the standby database into read-only mode, do the following.

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE OPEN READ ONLY;

select name,open_mode,database_role from v$database;

To resume managed recovery, do the following.

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

select name,open_mode,database_role from v$database;

désert/Oracle/DataGuard