Contents

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

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.

calc shm

# Output lines suitable for sysctl configuration based
# on total amount of RAM on the system.  The output
# will allow up to 50% of physical memory to be allocated
# into shared memory.

# On Linux, you can use it as follows (as root):
#
# ./shmsetup >> /etc/sysctl.conf
# sysctl -p

# Early FreeBSD versions do not support the sysconf interface
# used here.  The exact version where this works hasn't
# been confirmed yet.

page_size=`getconf PAGE_SIZE`
phys_pages=`getconf _PHYS_PAGES`

if [ -z "$page_size" ]; then
  echo Error:  cannot determine page size
  exit 1
fi

if [ -z "$phys_pages" ]; then
  echo Error:  cannot determine number of memory pages
  exit 2
fi

shmall=`expr $phys_pages / 2`
shmmax=`expr $shmall \* $page_size`

echo \# Maximum shared segment size in bytes
echo kernel.shmmax = $shmmax
echo \# Maximum number of shared memory segments in pages
echo kernel.shmall = $shmall

前提条件

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;

# Real time
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

停止传输

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

确认Dataguard保护模式

默认为 maximum performance 模式

SELECT protection_mode FROM v$database;

select name, db_unique_name, database_role, open_mode, switchover_status 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#;

SELECT sequence#, first_time, next_time, applied FROM v$archived_log where applied="no";


select count(*) from v$archived_log where applied = 'NO' and standby_dest = 'YES'

SELECT sequence#, first_time, next_time, applied FROM   v$archived_log ORDER BY sequence# where applied="no";

SELECT group#, dbid, thread#, sequence#, status FROM v$standby_log;

主备数据库切换操作

手动从主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;

Flashback primary database in dataguard environment

https://dbaclass.com/article/flashback-primary-database-in-dataguard-environment/

Make sure both primary and standby database have flashback enabled

-- PRIMARY DB
 
SQL> select name,database_role,flashback_on from v$database;
 
NAME      DATABASE_ROLE    FLASHBACK_ON
--------- ---------------- ------------------
PRIMDB    PRIMARY           YES
 
-- STANDBY DB
 
SQL> select name,database_role,flashback_on from v$database;
 
NAME      DATABASE_ROLE    FLASHBACK_ON
--------- ---------------- ------------------
STDBY      PHYSICAL STANDBY      YES

Create a restore point in the primary:[PRIMARY]

SQL> create restore  point BEFORE_TEST GUARANTEE FLASHBACK DATABASE;
 
Restore point created.
 
SQL>  select scn,NAME from v$restore_point;
 
       SCN NAME
---------- -----------------------
  14084750 BEFORE_TEST

Now we can confirm the dev team to do the changes in the primary database. And once dev team confirmed that their testing is over, Follow below steps to flashback primary.

Shutdown Primary database [ PRIMARY]

SQL> SHUTDOWN IMMEDIATE

Mount primary database [ PRIMARY]

SQL> startup mount

Flashback primary database [ PRIMARY]

SQL> flashback database to restore point BEFORE_TEST;

Open primary database in resetlogs: [ PRIMARY]

SQL> alter database open resetlogs;

Cancel the recovery on standby:[ STANDBY ]

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Flashback standby database [ STANDBY]

Get the SCN value from point 2 (the respective scn of the guarantee restore point)

SQL>FLASHBACK STANDBY DATABASE TO 14084750;

Start recovery [ STANDBY ]

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

NOTE:

If you are getting below error in standby, then we need to recreate the control file in standby

How to recreate physical standby controlfile

Get the controlfile location [STANDBY]

SQL> show parameter control_files

Cancel recovery and Shutdown [STANDBY]

alter database recover managed standby database cancel;
SHUTDOWN IMMEDIATE

Create standby controlfile in primary then copy to standby server [ PRIMARY]

SQL> alter database create standby controlfile as '/tmp/standby_ctrl.ctl';
SQL> !scp /tmp/standby_ctrl.ctl oracle@host2:/tmp/

Replace the existing controlfile with the new controlfile [ STANDBY]

cp /tmp/standby_ctrl.ctl /PATH-TO-CONTROLFILE/

Mount standby database then Start MRP recovery process [ STANDBY]

SQL> STARTUP MOUNT
SQL> alter database recover managed standby database disconnect from session;

Troubleshooting

Failed to request gap sequence

SQL> SELECT * FROM V$ARCHIVE_GAP;

increasing db_recovery_file_dest_size value on the primary would not increase the value on the standby server too.

For the log gap issue, if you have the backups of the deleted archives, you can restore them back and the standby would catch it up. If you do not have the backup of the deleted archives, then you can consider going with the "Roll Forward Incremental SCN backup" method.

Roll Forward Physical Standby Database using RMAN incremental backup

Please see blew article:

/RollForwardPhysicalStandby

References

https://dbaclass.com/dataguard/

désert/Oracle/DataGuard