Oracle Data Guard白皮书

Oracle Data Guard白皮书

 

下载地址:Oracle Data Guard白皮书 https://zcdn.askmaclean.com/【诗檀软件-技术报告】Oracle%20Data%20Guard白皮书.pdf

 

为12.1 DataGuard配置DGMGRL遇到ORA-16698

为12.1.0.2 DataGuard配置DGMGRL时遇到了ORA-16698错误:

 

 



BANNER                                                                                         CON_ID
------------------------------------------------------------------------------------------ ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production                        0
PL/SQL Release 12.1.0.2.0 - Production                                                              0
CORE    12.1.0.2.0      Production                                                                          0
TNS for Linux: Version 12.1.0.2.0 - Production                                                      0
NLSRTL Version 12.1.0.2.0 - Production                                                              0


DGMGRL> DGMGRL> [oracle@PD009 ~]$ oerr ora 16698
16698, 0000, "LOG_ARCHIVE_DEST_n parameter set for object to be added"
// *Cause:  One or more LOG_ARCHIVE_DEST_n initialization parameters that
//          contain a SERVICE attribute but not the NOREGISTER attribute were
//          set when attempting to create a configuration or add a standby or
//          far sync instance to the configuration.  askmaclean.com
// *Action: If creating a configuration, clear all LOG_ARCHIVE_DEST_n
//          initialization parameters that contain a SERVICE attribute but not
//          the NOREGISTER attribute. If adding a standby database or far sync
//          instance, clear the LOG_ARCHIVE_DEST_n initialization parameter
//          that specifies the database or far sync instance to be added.


这个错误可以通过在Primary 和 Standby上取消log_archive_dest_n参数来解决,实际这一块的参数应当是交给DG broker 来管理了,不再需要人为介入设置。

 



primary:


SQL>  alter system reset log_archive_dest_2 scope=spfile sid='*';

System altered.



SQL> alter system reset log_archive_dest_1 scope=spfile sid='*';

System altered.


SQL> startup force;
ORACLE instance started.

standby:


SQL>  alter system reset log_archive_dest_2 scope=spfile sid='*';

System altered.



SQL> alter system reset log_archive_dest_1 scope=spfile sid='*';

System altered.


重启primary和standby实例

[oracle@PD009 ~]$  dgmgrl sys/oracle@PDPROD
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

Copyright (c) 2000, 2013, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected as SYSDBA.
DGMGRL> create CONFIGURATION PDPROD as primary database is PDPROD CONNECT IDENTIFIER IS  PDPROD;
Configuration "pdprod" created with primary database "pdprod"

add database PDSTBY as CONNECT IDENTIFIER IS PDSTBY MAINTAINED AS PHYSICAL;

Database "pdstby" added

Connected as SYSDBA.
DGMGRL> enable configuration;

Enabled.
DGMGRL> DGMGRL> show configuration;

Configuration - pdprod

  Protection Mode: MaxPerformance
  Members:
  pdprod - Primary database
    pdstby - Physical standby database 

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 5 seconds ago)

 

V$RECOVERY_PROGRESS For Oracle Recovery

V$RECOVERY_PROGRESS For Oracle Recovery

 

V$RECOVERY_PROGRESS view
Major overhaul for Media Recovery and Physical Standby in 10gR2
Tracks redo apply rate: overall and recent
Breakdown of apply vs. checkpoint time per log

 

Recovery performance can be monitored using V$RECOVERY_PROGRESS and several system events
and stats. However, more work is needed in this space to help discover which parts of the recovery
algorithm are taking a long time. The discussion here mainly applies to RDBMS Release 11.2, but can
also be applicable for future releases. Below, new stats are described in bold-faced.

 

more info: http://emrebaransel.blogspot.jp/2010/06/mrp-speed-log-apply-rate-of-standby.html

【Maclean Liu视频技术分享】11g ocm考纲复习Active Data Guard + Fast Failover + DG Broker

【Maclean Liu视频技术分享】11g ocm考纲复习Active Data Guard + Fast Failover + DG Broker

 

Data Guard讨论帖 :http://t.askmaclean.com/thread-2530-1-1.html

 

教学视频观看地址:

 

 

 

【Maclean Liu技术分享】11g ocm考纲复习Data Guard.pdf (176.18 KB, 下载次数: 69)

9i中DG remote archive可能导致Primary Database挂起

9i中若dg的remove archive stuck卡住可能即便在Max Performance最大性能模式下也会导致Primary database 主库的local archive 本地归档无法正常进行, 进而导致Primary db hang挂起。

Solaris sparce 64  平台上的9.2.0.5 库 的相关日志:

 

Verifying the issue in the alert log
Fri Oct 28 08:10:12 2011
ALTER SYSTEM SET log_archive_dest_1='location=/u24/app/oradata/temp_arch' SCOPE=BOTH;
Fri Oct 28 08:10:51 2011
ORACLE Instance maclean - Can not allocate log, archival required
Fri Oct 28 08:10:51 2011
ARCH: Connecting to console port...
Thread 1 cannot allocate new log, sequence 18963
All online logs needed archiving
 Current log# 3 seq# 18962 mem# 0: /u23/app/oradata/maclean/redo3a.log
 Current log# 3 seq# 18962 mem# 1: /u24/app/oradata/maclean/redo3b.log
Fri Oct 28 08:12:22 2011
ALTER SYSTEM SET log_archive_dest_1='location=/u04/app/oracle/admin/maclean mandatory' SCOPE=BOTH; 
<== changing archive destination multiple times
Fri Oct 28 08:27:56 2011
Shutting down instance: further logons disabled
Shutting down instance (immediate) <===== issued shut immediate
License high water mark = 546
Fri Oct 28 08:30:55 2011
PMON failed to delete process, see PMON trace file
Fri Oct 28 08:33:11 2011
Active call for process 19878 user 'oracle' program 'oracle@e02k72 (TNS V1-V3)'
SHUTDOWN: waiting for active calls to complete. <=== looks there are active trans and waiting to complete
Fri Oct 28 08:36:40 2011
Starting ORACLE instance (force) <=== issued startup force
Fri Oct 28 08:37:03 2011
ALTER DATABASE   MOUNT
Fri Oct 28 08:37:08 2011
Successful mount of redo thread 1, with mount id 2399989231
Fri Oct 28 08:37:08 2011
Database mounted in Exclusive Mode.
Completed: ALTER DATABASE   MOUNT <=== mount completed
Fri Oct 28 08:37:08 2011
ALTER DATABASE OPEN
Fri Oct 28 08:37:08 2011
Beginning crash recovery of 1 threads <=== started crash recover as expected
Fri Oct 28 08:37:08 2011
Started redo scan
Fri Oct 28 08:37:08 2011
Completed redo scan
0 redo blocks read, 0 data blocks need recovery
Fri Oct 28 08:37:08 2011
Started recovery at
Thread 1: logseq 18962, block 409599, scn 2775.3390299666
Recovery of Online Redo Log: Thread 1 Group 3 Seq 18962 Reading mem 0
 Mem# 0 errs 0: /u23/app/oradata/maclean/redo3a.log
 Mem# 1 errs 0: /u24/app/oradata/maclean/redo3b.log
Fri Oct 28 08:37:08 2011
Completed redo application
Fri Oct 28 08:37:08 2011
Ended recovery at
Thread 1: logseq 18962, block 409599, scn 2775.3390319667
0 data blocks read, 0 data blocks written, 0 redo blocks read
Crash recovery completed successfully
Fri Oct 28 08:37:08 2011
LGWR: Primary database is in CLUSTER CONSISTENT mode
Fri Oct 28 08:37:08 2011
ARCH: Evaluating archive   log 1 thread 1 sequence 18959
ARCH: Beginning to archive log 1 thread 1 sequence 18959
Creating archive destination LOG_ARCHIVE_DEST_2: 'maclean_dr'
Creating archive destination LOG_ARCHIVE_DEST_1: '/u04/app/oracle/admin/maclean/maclean_1_18959.arch'
Fri Oct 28 08:41:12 2011
alter database open
Fri Oct 28 08:41:12 2011
ORA-1154 signalled during: alter database open...
Fri Oct 28 08:59:10 2011
ARCH: Completed archiving  log 1 thread 1 sequence 18959
Fri Oct 28 08:59:10 2011
LGWR: Primary database is in CLUSTER CONSISTENT mode
Thread 1 advanced to log sequence 18963
Thread 1 opened at log sequence 18963
 Current log# 1 seq# 18963 mem# 0: /u23/app/oradata/maclean/redo1a.log
 Current log# 1 seq# 18963 mem# 1: /u24/app/oradata/maclean/redo1b.log
Successful open of redo thread 1
Fri Oct 28 08:59:11 2011
LOG_CHECKPOINT_INTERVAL was set when MTTR advisory was switched on.
Fri Oct 28 08:59:11 2011
ARC0: Evaluating archive   log 2 thread 1 sequence 18960
ARC0: Beginning to archive log 2 thread 1 sequence 18960
Creating archive destination LOG_ARCHIVE_DEST_2: 'maclean_dr'
Fri Oct 28 08:59:11 2011
SMON: enabling cache recovery
Fri Oct 28 08:59:11 2011
Creating archive destination LOG_ARCHIVE_DEST_1: '/u04/app/oracle/admin/maclean/maclean_1_18960.arch'
Fri Oct 28 08:59:12 2011
Undo Segment 1 Onlined
Undo Segment 2 Onlined
Undo Segment 3 Onlined
Undo Segment 4 Onlined
Undo Segment 5 Onlined
Undo Segment 6 Onlined
Undo Segment 7 Onlined
Undo Segment 8 Onlined
Undo Segment 9 Onlined
Undo Segment 10 Onlined
Successfully onlined Undo Tablespace 1.
Fri Oct 28 08:59:12 2011
SMON: enabling tx recovery
Fri Oct 28 08:59:12 2011
Database Characterset is AL32UTF8
Fri Oct 28 08:59:12 2011
SMON: about to recover undo segment 17
Fri Oct 28 08:59:12 2011
replication_dependency_tracking turned off (no async multimaster replication found)
Fri Oct 28 08:59:12 2011
SMON: about to recover undo segment 17
SMON: mark undo segment 17 as available
SMON: about to recover undo segment 18
SMON: mark undo segment 18 as available
SMON: about to recover undo segment 17
SMON: mark undo segment 17 as available
SMON: about to recover undo segment 18
SMON: mark undo segment 18 as available
SMON: about to recover undo segment 17
SMON: mark undo segment 17 as available
SMON: about to recover undo segment 18
SMON: mark undo segment 18 as available
SMON: about to recover undo segment 17
SMON: mark undo segment 17 as available
SMON: about to recover undo segment 17
SMON: mark undo segment 17 as available
SMON: about to recover undo segment 17
SMON: mark undo segment 17 as available
SMON: about to recover undo segment 17
SMON: mark undo segment 17 as available
SMON: about to recover undo segment 17
SMON: mark undo segment 17 as available
SMON: about to recover undo segment 17
SMON: mark undo segment 17 as available
SMON: about to recover undo segment 17
SMON: mark undo segment 17 as available
SMON: about to recover undo segment 17
SMON: mark undo segment 17 as available
SMON: about to recover undo segment 17
SMON: mark undo segment 17 as available
SMON: about to recover undo segment 17
SMON: mark undo segment 17 as available
SMON: about to recover undo segment 17
SMON: mark undo segment 17 as available
SMON: about to recover undo segment 17
SMON: mark undo segment 17 as available
SMON: about to recover undo segment 17
SMON: mark undo segment 17 as available
SMON: about to recover undo segment 17
SMON: mark undo segment 17 as available
SMON: about to recover undo segment 17
SMON: mark undo segment 17 as available
SMON: about to recover undo segment 17
SMON: mark undo segment 17 as available
SMON: about to recover undo segment 17
SMON: mark undo segment 17 as available
SMON: about to recover undo segment 17
SMON: mark undo segment 17 as available
SMON: Parallel transaction recovery tried <=== transaction recovery
Fri Oct 28 08:59:14 2011
Completed: ALTER DATABASE OPEN <=== database is now open
Fri Oct 28 09:02:34 2011
This looks to be an expected behavior, as the database waited for the crash/transaction recovery to
complete and it opened the database just after the transaction recovery.
Note 414242.1 Database Hangs Because SMON is taking 100% CPU doing transaction recovery Startup Hang
Also Please note that 9i  is Sustaining Support as of  now, so we cannot raise any bug/backport requests for
this version of Oracle.
So, with limited resources and information We will do our best to solve your issue.
However, if there is no available workaround or fix for this version of Oracle, you may need to migrate to
a supported version like 11.2.0.3 to solve this problem.
Also please note that 9.2.0.5 is no more listed in the certification for Oracle Solaris on SPARC (64-bit) platform.
So i request you to upgrade to at least 9.2.0.8 immediately, if upgrade to 11.2.0.3 is not feasible immediately.
If it is up are you able to issue an "alter system switch logfile" command and it finish?
yes now alter system switch logfile is working. but we had to restart database couple of times by shutdown abort..
we did it around 4-5 times.. why it concerns us a lot bcoz produciton instance was down for 2-3 hours.
3. Upload the output of the command SQL> show parameter archive.
SQL> show parameter archive
NAME TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
archive_lag_target integer
0
log_archive_dest string
log_archive_dest_1 string
location=/u04/app/oracle/admin
/maclean
log_archive_dest_10 string
NAME TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
log_archive_dest_2 string
service=maclean_dr reopen=60
log_archive_dest_3 string
log_archive_dest_4 string
log_archive_dest_5 string
log_archive_dest_6 string
NAME TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
log_archive_dest_7 string
log_archive_dest_8 string
log_archive_dest_9 string
log_archive_dest_state_1 string
enable
NAME TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
log_archive_dest_state_10 string
enable
log_archive_dest_state_2 string
enable
log_archive_dest_state_3 string
enable
log_archive_dest_state_4 string
enable
log_archive_dest_state_5 string
NAME TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
enable
log_archive_dest_state_6 string
enable
log_archive_dest_state_7 string
enable
log_archive_dest_state_8 string
enable
log_archive_dest_state_9 string
enable
NAME TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
log_archive_duplex_dest string
log_archive_format string
maclean_%t_%s.arch
log_archive_max_processes integer
2
log_archive_min_succeed_dest integer
1
log_archive_start boolean
NAME TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
TRUE
log_archive_trace integer
0
remote_archive_enable string
true
standby_archive_dest string
?/dbs/arch
SQL>
Destination LOG_ARCHIVE_DEST_2 is in CLUSTER CONSISTENT mode
Destination LOG_ARCHIVE_DEST_2 is in MAXIMUM PERFORMANCE mode
Destination LOG_ARCHIVE_DEST_2 is in CLUSTER CONSISTENT mode
Destination LOG_ARCHIVE_DEST_2 is in MAXIMUM PERFORMANCE mode
This indicates that your Primary Init.ora parameter log_archive_dest_2 is not in sync with your standby destination.
I presume your standby database maclean_dr is working properly?
The standby dataguard database may not be in a correct state. Archive logs not being successfully transported.
Possible password errors. Possible destination file errors, possible set up errors.
standby was in sync. we even defer log_archive_dest_2 during issue to check if log switch works after that.
but even after deferring the log_archive_dest_2 log switch didn't work .& database was in bad shape for 2-3 hours..
Fri Oct 28 08:35:32 2011
RFS: Possible network disconnect with primary database <<<<<DISCONNECT FROM PRIMARY - SHUTDOWN IMMEDIATE ON PRIMARY
Closing latent archivelog for thread 1 sequence 18960
EOF located at block 47105 low SCN 2775:-904684681 next SCN 16:0
Latent archivelog '/u04/app/oracle/admin/maclean/maclean_1_18960.arch'
If you wish to failover to this standby database, you should use the
following command to manually register the archivelog for recovery:
ALTER DATABASE REGISTER LOGFILE '/u04/app/oracle/admin/maclean/maclean_1_18960.arch';
Fri Oct 28 08:35:32 2011
Errors in file /u01/app/oracle/admin/maclean/udump/maclean_rfs_6173.trc:
ORA-00367: checksum error in log file header <<<<<<<<<<<<<<<<<<<<<<CHECKSUM ERROR IN FILES
ORA-00332: archived log is too small - may be incompletely archived
ORA-00334: archived log: '/u04/app/oracle/admin/maclean/maclean_1_18960.arch'
by 0954 the standby had caught up.
Fri Oct 28 09:54:41 2011
Media Recovery Log /u04/app/oracle/admin/maclean/maclean_1_18962.arch
Media Recovery Log /u04/app/oracle/admin/maclean/maclean_1_18963.arch
Media Recovery Log /u04/app/oracle/admin/maclean/maclean_1_18964.arch
Media Recovery Log /u04/app/oracle/admin/maclean/maclean_1_18965.arch
Media Recovery Log /u04/app/oracle/admin/maclean/maclean_1_18966.arch
Media Recovery Log /u04/app/oracle/admin/maclean/maclean_1_18967.arch
Media Recovery Log /u04/app/oracle/admin/maclean/maclean_1_18968.arch
Media Recovery Log /u04/app/oracle/admin/maclean/maclean_1_18969.arch
Media Recovery Log /u04/app/oracle/admin/maclean/maclean_1_18970.arch
Media Recovery Log /u04/app/oracle/admin/maclean/maclean_1_18971.arch
Media Recovery Log /u04/app/oracle/admin/maclean/maclean_1_18972.arch
Media Recovery Log /u04/app/oracle/admin/maclean/maclean_1_18973.arch
The following notes should help you to reduce some of the errors on the standby side.
"Data Guard 9i ORA-332 Error When Registering Partial Archive Log After Primary Server Goes Down (Doc ID 233253.1)"
"Note:14579.1 SOLARIS Configuring Keepalive on Solaris (SPARC): "
This shows the value of the keep alive timer
ndd /dev/tcp tcp_keepalive_interval
You can change the parameter with:
ndd -set /dev/tcp tcp_keepalive_interval <new-smaller-value>
Current value for tcp_keepalive_interval is 7200000,
Can you please suggest us the value for this parameter also let us know if this parameter requires
server reboot or it can be done online.
$ ndd /dev/tcp tcp_keepalive_interval
7200000
Fri Oct 28 04:59:21 2011
Creating archive destination LOG_ARCHIVE_DEST_1: '/u04/app/oracle/admin/maclean/maclean_1_18958.arch'
Fri Oct 28 04:59:22 2011
Creating archive destination LOG_ARCHIVE_DEST_1: '/u04/app/oracle/admin/maclean/maclean_1_18957.arch'
Fri Oct 28 05:01:48 2011
ORACLE Instance maclean - Can not allocate log, archival required
Fri Oct 28 05:01:48 2011
ARCH: Connecting to console port...
Thread 1 cannot allocate new log, sequence 18960
All online logs needed archiving
Current log# 1 seq# 18959 mem# 0: /u23/app/oradata/maclean/redo1a.log
Current log# 1 seq# 18959 mem# 1: /u24/app/oradata/maclean/redo1b.log
Fri Oct 28 05:12:10 2011
ARCH: Completed archiving log 2 thread 1 sequence 18957
ARCH: Evaluating archive log 4 thread 1 sequence 18956
ARCH: Unable to archive log 4 thread 1 sequence 18956
Log actively being archived by another process
Fri Oct 28 05:12:10 2011
Thread 1 advanced to log sequence 18960
Current log# 2 seq# 18960 mem# 0: /u23/app/oradata/maclean/redo2a.log
Current log# 2 seq# 18960 mem# 1: /u24/app/oradata/maclean/redo2b.log
Fri Oct 28 05:40:25 2011
ARC1: Completed archiving log 4 thread 1 sequence 18956
ARC1: Evaluating archive log 3 thread 1 sequence 18958
ARC1: Unable to archive log 3 thread 1 sequence 18958
Log actively being archived by another process
ARC1: Evaluating archive log 1 thread 1 sequence 18959
ARC1: Beginning to archive log 1 thread 1 sequence 18959
Creating archive destination LOG_ARCHIVE_DEST_2: 'maclean_dr'
Creating archive destination LOG_ARCHIVE_DEST_1: '/u04/app/oracle/admin/maclean/maclean_1_18959.arch'
Fri Oct 28 05:40:27 2011
ARCH: Evaluating archive log 3 thread 1 sequence 18958
ARCH: Unable to archive log 3 thread 1 sequence 18958
Log actively being archived by another process
ARCH: Evaluating archive log 1 thread 1 sequence 18959
ARCH: Unable to archive log 1 thread 1 sequence 18959
Log actively being archived by another process
Fri Oct 28 05:40:27 2011
Thread 1 advanced to log sequence 18961
Current log# 4 seq# 18961 mem# 0: /u23/app/oradata/maclean/redo4a.log
Current log# 4 seq# 18961 mem# 1: /u24/app/oradata/maclean/redo4b.log
Fri Oct 28 06:11:45 2011
ORACLE Instance maclean - Can not allocate log, archival required
Fri Oct 28 06:11:45 2011
ARCH: Connecting to console port...
Thread 1 cannot allocate new log, sequence 18962
All online logs needed archiving
Current log# 4 seq# 18961 mem# 0: /u23/app/oradata/maclean/redo4a.log
Current log# 4 seq# 18961 mem# 1: /u24/app/oradata/maclean/redo4b.log
Fri Oct 28 07:40:42 2011
Archive Log Stop
Archiving is disabled
Shutting down archive processes
Archiving is disabled
Archive Log Start
Archiving is enabled
ARCH: STARTING ARCH PROCESSES
ARCH: STARTING ARCH PROCESSES COMPLETE
Fri Oct 28 07:54:01 2011
ALTER SYSTEM SET log_archive_dest_state_2='defer' SCOPE=SPFILE;
Fri Oct 28 07:54:09 2011
ALTER SYSTEM SET log_archive_dest_state_2='defer' SCOPE=BOTH;
Fri Oct 28 08:03:52 2011
ALTER SYSTEM SET log_archive_dest_state_2='enable' SCOPE=BOTH;
Fri Oct 28 08:06:20 2011
ARC0: Completed archiving log 3 thread 1 sequence 18958
Fri Oct 28 08:06:20 2011
ARCH: Evaluating archive log 1 thread 1 sequence 18959
ARCH: Unable to archive log 1 thread 1 sequence 18959
Log actively being archived by another process
ARCH: Evaluating archive log 2 thread 1 sequence 18960
ARCH: Beginning to archive log 2 thread 1 sequence 18960
Creating archive destination LOG_ARCHIVE_DEST_2: 'maclean_dr'
Fri Oct 28 08:06:20 2011
Thread 1 advanced to log sequence 18962
Current log# 3 seq# 18962 mem# 0: /u23/app/oradata/maclean/redo3a.log
Current log# 3 seq# 18962 mem# 1: /u24/app/oradata/maclean/redo3b.log
Fri Oct 28 08:06:20 2011
ARCH shutting down
ARC0: Archival stopped
ARC0: Shutdown aborted (current state is 3)
ARC0: Evaluating archive log 1 thread 1 sequence 18959
ARC0: Unable to archive log 1 thread 1 sequence 18959
Log actively being archived by another process
ARC0: Evaluating archive log 2 thread 1 sequence 18960
ARC0: Unable to archive log 2 thread 1 sequence 18960
Log actively being archived by another process
Fri Oct 28 08:06:21 2011
ARC0: Evaluating archive log 4 thread 1 sequence 18961
Fri Oct 28 08:06:21 2011
Undo Segment 21 Onlined
ARC0: Beginning to archive log 4 thread 1 sequence 18961
Creating archive destination LOG_ARCHIVE_DEST_2: 'maclean_dr'
Fri Oct 28 08:06:21 2011
Undo Segment 22 Onlined
Fri Oct 28 08:06:21 2011
Undo Segment 23 Onlined
Fri Oct 28 08:06:21 2011
Undo Segment 24 Onlined
Fri Oct 28 08:06:21 2011
Undo Segment 25 Onlined
Fri Oct 28 08:06:21 2011
Undo Segment 26 Onlined
Fri Oct 28 08:06:21 2011
Creating archive destination LOG_ARCHIVE_DEST_1: '/u04/app/oracle/admin/maclean/maclean_1_18960.arch'
Fri Oct 28 08:06:21 2011
Creating archive destination LOG_ARCHIVE_DEST_1: '/u04/app/oracle/admin/maclean/maclean_1_18961.arch'
Fri Oct 28 08:10:12 2011
ALTER SYSTEM SET log_archive_dest_1='location=/u24/app/oradata/temp_arch' SCOPE=BOTH;
Fri Oct 28 08:10:51 2011
ORACLE Instance maclean - Can not allocate log, archival required
Fri Oct 28 08:10:51 2011
ARCH: Connecting to console port...
Thread 1 cannot allocate new log, sequence 18963
All online logs needed archiving
Current log# 3 seq# 18962 mem# 0: /u23/app/oradata/maclean/redo3a.log
Current log# 3 seq# 18962 mem# 1: /u24/app/oradata/maclean/redo3b.log
Fri Oct 28 08:12:22 2011
ALTER SYSTEM SET log_archive_dest_1='location=/u04/app/oracle/admin/maclean mandatory' SCOPE=BOTH;
Fri Oct 28 08:27:56 2011
Shutting down instance: further logons disabled
Shutting down instance (immediate)
License high water mark = 546
Fri Oct 28 08:30:55 2011
PMON failed to delete process, see PMON trace file
Fri Oct 28 08:33:11 2011
Active call for process 19878 user 'oracle' program 'oracle@e02k72 (TNS V1-V3)'
SHUTDOWN: waiting for active calls to complete.
Fri Oct 28 08:36:40 2011
Starting ORACLE instance (force)
License high water mark = 546
Instance terminated by USER, pid = 28373
FileName
----------------
alert_maclean.log

 

这个SR提交之后 , 一开始认可能是Solaris上tcp tcp_keepalive_interval 参数的问题, 后来esclated到serverity 1 后Oracle high availability support team接手, 发现是 因为 9i中若 remote archive stuck住 会导致本地归档无法正常,  造成数据库无响应。

 

 

Please refer the below Article which seems to match with your issue :
Doc ID 260040.1 -- Refining Remote Archival Over a Slow Network with the ARCH Process .
Please set the parameter "_LOG_ARCHIVE_CALLOUT" to 'LOCAL_FIRST=TRUE' and also
increase the number of ARCH processes from the default value of 2 to 6.
SQL> alter system set log_archive_max_processes=6 scope=both;
As in Doc ID 260040.1
If the remote archiving goes into unknown state, it can cause primary hang as it doesn't attempts
local archiving first in 9..2.0.5
It can still happen in max performance mode. The only workaround to prevent such problem is to
set _LOG_ARCHIVE_CALLOUT" to 'LOCAL_FIRST=TRUE'
With that parameter, it will do local archiving first before trying remote archiving preventing
any impact of standby setup to primary database.
You mentioned that you did try deferring remote archiving but it didn't help.
If the destination is stuck (in network) already, deferring the destination may not help.
But you rather restart instances.
Action plan suggested).
Please set _LOG_ARCHIVE_CALLOUT" to 'LOCAL_FIRST=TRUE'
If problem happens again, get multiple system state dump with 10046 trace
on alter system switch log file session. 
When the parameter "_LOG_ARCHIVE_CALLOUT" is set , 1 ARCH process is reserved for local archiving.
So even if the network is slow or the Standby is down, the Primary will not be impacted.
Setting the parameter "_LOG_ARCHIVE_CALLOUT" should not cause log shipping issues.

 

 

“_LOG_ARCHIVE_CALLOUT”(Secondly, you can change the above outlined archiver behavior, to make sure the local destination is archived first, causing the OPTIONAL destination to behave really optionally. If the following parameter is set then the ARCH process will begin archiving to the local destination first. Once the redo log has been completely and successfully archived to at least one local destination. )

For 9iR2 (9.2.0.5 and above):

_log_archive_callout=’LOCAL_FIRST=TRUE’

–OR–

alter system set “_LOG_ARCHIVE_CALLOUT”=’LOCAL_FIRST=TRUE’ scope=both;

In Oracle 10g, this parameter is replaced by ‘log_archive_local_first’, which defaults to TRUE.

In Oracle 11g, ‘log_archive_local_first’ has been deprecated.

 

这个隐藏参数可以控制ARCH归档进程优先完成本地归档local archive工作,这样就避免了本地重做日志长期无法归档造成数据库无响应。

 

文档《Refining Remote Archival Over a Slow Network with the ARCH Process》介绍了更多信息:

 

Purpose

When archiving locally and remotely using the ARCH process where the remote destination is across a saturated or slow network you can receive the following errors in the alert log:

ARC0: Evaluating archive   log 2 thread 1 sequence 100

ARC0: Unable to archive log 2 thread 1 sequence 100

      Log actively being archived by another process

If the ARCH process is unable to archive at the rate at which online logs are switched then it is possible for the primary database to suspend while waiting for archiving to complete.  The following discussion describes how this can occur.

Default Behavior for 9iR2 and Below

The ARCH process sits in a very tight loop waiting for an update to the controlfile that states an online log needs to be archived.  Once the update occurs the ARCH process builds a list of archive destinations that need to be serviced.  Once this list is complete, the ARCH process will read a one megabyte chunk of data from the online log that is to be archived.  This one megabyte chunk is then sent to the first destination in the list.  When the write has completed, the same one megabyte chunk is written to the second destination.  This continues until all of the data from the online log being archived has been written to all destinations.  So it can be said that archiving is only as fast as the slowest destination.

A common misconception is that if the LOG_ARCHIVE_DEST_n parameter for a particular destination has the OPTIONAL attribute set, then that destination will not impede local archiving. This is true during error situations while archiving to that destination – e.g. a network disconnect error, but not during an archival over a slow network, which is not an error situation. In error situations, whether the destination is marked OPTIONAL or MANDATORY, Data Guard will close that destination and continue transmitting to all other valid destinations. Transmitting to the closed destination will be attempted again only after the time specified in the REOPEN attribute has expired and a log switch has occurred.  This process will continue for the number of times specified by the MAX_FAILURE attribute. During this time, it is possible that the log writer process recycles through the available online redo log groups and tries to use the online redo log file which has not yet been transmitted successfully to the remote destination. If the destination is marked OPTIONAL, log writer will reuse the online redo log file for the next set of redo. If the destination is marked MANDATORY,  log writer will not be able to reuse that online redo log file, and the primary database will delay processing until that online redo log file has been successfully transmitted to the remote destination.

However, the situation is very different if the transmission is being done over a slow network. In this case, no error is encountered and the destination is not closed. Transmission continues, but is very slow. Ultimately, with the unavailability of any more online redo log groups, Log writer may suspend because the archive process is taking a long time to complete its archival, including local archival.

Refining the Default Behavior

The following underscore parameter was introduced as of 9.2.0.5 to allow the DBA to change this default behavior:

_LOG_ARCHIVE_CALLOUT=’LOCAL_FIRST=TRUE’

This is a dynamic Parameter, so you can set it this Way:

SQL> alter system set “_LOG_ARCHIVE_CALLOUT”=’LOCAL_FIRST=TRUE’ scope=both;

If the above parameter is set then the ARCH process will begin archiving to the local destination first.  Once the redo log has been completely and successfully archived to at least one local destination, it will then be transmitted to the remote destination. This is the default behavior beginning with Oracle Database 10g Release 1.

Starting in 9.2.0.7 patchsets, one ARCH process will begin acting as a ‘dedicated’ archiver, handling only local archival duties. It will not perform remote log shipping or service FAL requests. This is a backport of behavior from 10gR1 to 9iR2.

 

 

设置_LOG_ARCHIVE_CALLOUT=’LOCAL_FIRST=TRUE’  + ’log_archive_max_processes=6′  一般可以解决该因为 归档状态异常而引起的实例无响应。

ORA-26786造成逻辑备库无法应用SQL一例

一套Linux x86-64 上的11.2.0.2逻辑备库因为出现ORA-26786: A row with key exists but has conflicting column(s) “导致APPLIER  进程无法继续工作,详细信息如下:

 

1) ### Primary OS Details ###
Hostname - vrh6
OS details -
[oracle@vrh6 ~]$ uname -a
Linux vrh6 2.6.18-194.8.1.el5 #1 SMP Wed Jun 23 10:52:51 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux
2) ### Standby OS Details ###
Hostname - vrh7
[oracle@vrh7 sqlscripts]$ uname -a
Linux vrh7 2.6.18-194.8.1.el5 #1 SMP Wed Jun 23 10:52:51 EDT 2010 x86_64 x86_64 x86_64 GNU/Linux
DB Version - 11.2.0.2
3) ### Primary DB Details ###
11.2.0.2
4) ### Standby DB Details ###
11.2.0.2
5) ### Standby Configuration Type ###
Logical Standby(s) Only.
6) ### RAC Usage ###
Primary only uses RAC.
7) ### Documentation Reference ###
We configured logical standby successfully, but the log apply process on
standby is very slow and lagging by many archive log files
8) ### Data Guard Management Method ###
Data Guard GUI (Console/Grid Control).
9) ### Issue or Question Details ###
The logical standby was applying logs very slow, so raised the LCR cache size to 3058M.
After this the performance has improved, but we still see lag (about 3-4 hours)

 

现象是logical standby逻辑备库应用日志十分缓慢,加大LCR cache也无济于事。

该问题提交SR后,MOS要求通过以下脚本收集primary 与 logical standby的信息:

 

Script to Collect Data Guard Logical Standby Diagnostic Information (Doc ID 241512.1)
Script to Collect Data Guard Primary Site Diagnostic Information (Doc ID 241374.1)
I reviewed the trace file and could not see any errors or stuck SQL apply
, please refer to note:
Oracle10g Data Guard SQL Apply Troubleshooting (Doc ID 312434.1)
MAA - SQL Apply Best Practices 10gR2 (Doc ID 387450.1)
to verify the SQL Apply progress and review some tuning recommendation in note 387450.1

 

分析脚本运行结果以后发现逻辑备库的日志应用其实停止了:

 

The logical standby database in now almost stopped.In last two days it is not applying any changes,
but in log files it says " loading previously applied transaction information".
In the alert_log file either it is mining the same set of log files many times.
It has been mining the same files foew about 6 hours now. The logical standby is lagging production by 4 days. 
This morning i have enabled few parameters as per the document 10G bestpractices. Here are the parameters
Exec DBMS_LOGSTDBY.APPLY_SET('MAX_SERVERS',24);
ALTER SYSTEM SET PARALLEL_MAX_SERVERS=24; scope=BOTH;
Exec DBMS_LOGSTDBY.APPLY_SET('LOG_AUTO_DELETE', 'TRUE');
We are getting below error in logical standby. I am uploading the alert log file as well.
Tue Aug 30 02:22:04 2011
LOGSTDBY: SQL Apply about to stop with ORA-26786
Tue Aug 30 02:22:04 2011
LOGSTDBY: SQL Apply about to stop with ORA-26786
Tue Aug 30 02:22:04 2011
LOGSTDBY: SQL Apply about to stop with ORA-26786
Tue Aug 30 02:22:04 2011
LOGSTDBY: SQL Apply about to stop with ORA-26786
Tue Aug 30 02:22:04 2011
LOGSTDBY: SQL Apply about to stop with ORA-26786
LOGSTDBY: SQL Apply finished logging error information
LOGSTDBY Apply process AS02 server id=2 pid=37 OS id=23940 stopped
Errors in file /d02/app/prrp081/diag/rdbms/prrp081/prrp081/trace/prrp081_as02_23940.trc:
ORA-26786: A row with key ("COGS_OM_LINE_ID", "PAC_COST_TYPE_ID") = (84243, ) exists but has
conflicting column(s) "LAST_UPDATE_LOGIN", "ORIGINAL_SHIPPED_QTY", "REQUEST_ID" in table
BOM.CST_REVENUE_COGS_MATCH_LINES
LOGSTDBY: SQL Apply finished logging error information
LOGSTDBY Apply process AS05 server id=5 pid=40 OS id=23946 stopped
Tue Aug 30 02:22:05 2011
Errors in file /d02/app/prrp081/diag/rdbms/prrp081/prrp081/trace/prrp081_lsp0_30249.trc:
ORA-26808: Apply process AS02 died unexpectedly.
ORA-26786: A row with key ("COGS_OM_LINE_ID", "PAC_COST_TYPE_ID") = (84243, ) exists but has
conflicting column(s) "LAST_UPDATE_LOGIN", "ORIGINAL_SHIPPED_QTY", "REQUEST_ID" in table
BOM.CST_REVENUE_COGS_MATCH_LINES
Errors in file /d02/app/prrp081/diag/rdbms/prrp081/prrp081/trace/prrp081_as05_23946.trc:
ORA-26786: A row with key ("COGS_OM_LINE_ID", "PAC_COST_TYPE_ID") = (84243, ) exists but has
conflicting column(s) "LAST_UPDATE_LOGIN", "ORIGINAL_SHIPPED_QTY", "REQUEST_ID" in table
BOM.CST_REVENUE_COGS_MATCH_LINES
We have re-started the apply process yesterday and it was still doing
"ORA-16106: loading previously applied transaction information" .
When i see the alert.log file, it has been mining the same archive log files since yesterday
(more than 30 hours for now). My archive log files are piling up and the gap is widening.
10G best practices have been implemented yesterday.
sys@vrh7> ;
1* SELECT sid,SERIAL#,TYPE, LOGSTDBY_ID,STATUS_CODE, STATUS FROM V$LOGSTDBY_PROCESS
sys@vrh7> /
SID SERIAL# TYPE LOGSTDBY_ID STATUS_CODE STATUS
----- ---------- ------------------------------ ----------- -----------
754 9 COORDINATOR -1 16116 ORA-16116: no work available
1010 63 ANALYZER 0 16116 ORA-16116: no work available
1257 165 APPLIER 1 16116 ORA-16116: no work available
1509 51 APPLIER 2 16116 ORA-16116: no work available
1760 59 APPLIER 3 16116 ORA-16116: no work available
10 51 APPLIER 4 16116 ORA-16116: no work available
254 59 APPLIER 5 16116 ORA-16116: no work available
508 267 APPLIER 6 16116 ORA-16116: no work available
757 81 APPLIER 7 16116 ORA-16116: no work available
1009 53 APPLIER 8 16116 ORA-16116: no work available
1256 73 APPLIER 9 16116 ORA-16116: no work available
1510 55 APPLIER 10 16116 ORA-16116: no work available
1762 69 APPLIER 11 16116 ORA-16116: no work available
11 51 APPLIER 12 16116 ORA-16116: no work available
256 59 APPLIER 13 16116 ORA-16116: no work available
504 85 APPLIER 14 16116 ORA-16116: no work available
760 105 APPLIER 15 16116 ORA-16116: no work available
1006 63 APPLIER 16 16116 ORA-16116: no work available
1255 161 APPLIER 17 16116 ORA-16116: no work available
1511 51 APPLIER 18 16116 ORA-16116: no work available
1761 59 APPLIER 19 16116 ORA-16116: no work available
252 61 READER 1024 16242 ORA-16242: Processing log file (thread# 1, sequence# 11585)
510 67 BUILDER 1025 16119 ORA-16119: building transaction at SCN 0x0abb.d76aec86
1004 55 PREPARER 1026 16116 ORA-16116: no work available
5 61 PREPARER 1027 16116 ORA-16116: no work available
25 rows selected.
The logical standby is a week behind the primary database and the logmining is happening on the
same files when we re-started the standby apply. With this speed it never catch up and clear
backlog arch files. I see some data descriphancy errors in the alert log file on table
BOM.CST_REVENUE_COGS_MATCH_LINES. The apply is not updating many of the records in this
table compalining "ORA-26786: A row with key ("COGS_OM_LINE_ID", "PAC
_COST_TYPE_ID") = (84243, ) exists but has conflic
ting column(s) "ORIGINAL_SHIPPED_QTY" in table BOM
.CST_REVENUE_COGS_MATCH_LINES"
I ran logical standby diag script and uploading the output. Please do the needful ASAP as
this is directly impacting the revenue/business loss.

 

MOS 最后给出的建议:

 

Looks like you identify the issue, you may want to skip the stuck table or transactions
and re-instantiate the table when both databases are synced, please refer to the below notes:
Oracle10g Data Guard SQL Apply Troubleshooting (Doc ID 312434.1)
Handling ORA-1403 ora-12801 on logical standby apply (Doc ID 1178284.1)
Applies to:
Oracle Server - Enterprise Edition - Version: 9.2.0.8 to 11.2.0.1.0 - Release: 9.2 to 11.2
Information in this document applies to any platform.
Purpose
Logical standby apply may report ora-1403: No data found during apply.
This note explains how to check the transaction that is failing and ways to fix the same problematic transaction.
Sample Alert log contents
==================
LOGMINER: Begin mining logfile: D:\ORADATA\LOGI\SREDO05.LOG
LOGSTDBY Apply process P012 started with pid=50 OS id=5300
LOGMINER: session# = 41, builder process P003 started with pid=41 OS id=4740
Tue Aug 17 09:53:08 2010
LOGSTDBY status: ORA-01403: no data found
LOGSTDBY Apply process P006 pid=44 OS id=2892 stopped
Tue Aug 17 09:53:08 2010
Errors in file c:\oracle\product\10.2.0\admin\logi\bdump\logi_lsp0_2144.trc:
ORA-12801: error signaled in parallel query server P006
ORA-01403: no data found
LOGSTDBY Apply process P006 pid=44 OS id=2892 stopped
Tue Aug 17 09:53:08 2010
Errors in file c:\oracle\product\10.2.0\admin\logi\bdump\logi_lsp0_2144.trc:
ORA-12801: error signaled in parallel query server P006
ORA-01403: no data found
Last Review Date
August 18, 2010
Instructions for the Reader
A Troubleshooting Guide is provided to assist in debugging a specific issue. When possible, 
diagnostic tools are included in the document to assist in troubleshooting.
Troubleshooting Details
ORA-1403 is mostly caused due some of below reasons
1) Skip rule defined on DML operations earlier in Logical standby database that cause the 
data mismatch between PRIMARY and STANDBY database.
2) User modified the data directly in Logical standby database by setting database guard status OFF.
Solution:1
**************
1) Identify the failing sql statement using DBA_LOGSTDBY_EVENTS from logical standby database
SQL> select XIDUSN, XIDSLT, XIDSQN , status , event from dba_logstdby_events order by event_time;
XIDUSN XIDSLT XIDSQN STATUS EVENT
---------- ---------- ---------- ------------------------------ -------------------------------
5 22 1826 ORA-01403: no data found delete from "SHANKAR"."TEST1" where "ID" = 1 and
"ENAME" = 'shankar' and ROWID = 'AAAAAAAABAAAJHaAAA'
You should not compare the ROWID of PRIMARY & STANDBY database as it will be complete 
different and sql apply will not check the row based on ROWID that is shown in the sql statement above.
2) Skip the failing transaction from logical standby database.
Execute the procedure dbms_lostdby.skip_transaction(XIDUSN , XIDSLT ,XIDSQN) of the failing transaction.
SQL> exec dbms_logstdby.skip_transaction (5,22,1826);
3) Restart the sql apply.
SQL> alter database start logical standby apply immediate;
If using broker
DGMGRL>edit database logi set state='online';
Solution:2
*************
If the data mismatch is huge that may cause repeated ora-1403 on same table then its 
advisable to instantiate the complete table from PRIMARY to STANDBY.
TableName : TEST1
SchemaName : SHANKAR
1. At primary, as sys user grant below roles to the user whose table(s) is being reinstantiated
SQL> grant SELECT_CATALOG_ROLE to SHANKAR;
SQL> grant EXP_FULL_DATABASE to SHANKAR;
SQL> grant IMP_FULL_DATABASE to SHANKAR;
2. As sys user, create a connected user database link at logical standby.
SQL> create public database link synctable connect to shankar identified by shankar using 'prim';
database link created.
prim - is the service name in TNSNAMES.ORA that points to primary database.
3. verify the database link from logical standby to ensure that it gets info from primary
SQL> select db_unique_name,database_role from v$database@synctable;
DB_UNIQUE_NAME DATABASE_ROLE
------------------------------ ----------------
PRIM PRIMARY
4. Stop the sql apply if it is already on. You can skip this step otherwise
SQL> select count(*) from v$logstdby; ## will result zero rows if sql apply is not running
SQL> alter database stop logical standby apply;
If using BROKER
DGMGRL> edit database logi set state='log-apply-off';
5. Remove the skip rules in LOGICAL standby database, if there is any, from the table that is being instantiated
SQL> select STATEMENT_OPT,NAME from DBA_LOGSTDBY_SKIP where OWNER='SHANKAR';
SQL> exec dbms_logstdby.unskip('SCHEMA_DDL', 'SHANKAR','TEST1');
SQL> exec dbms_logstdby.unskip('DML', 'SHANKAR','TEST1');
6. From logical standby ,start instantiating the the table as sys user
SQL> exec dbms_logstdby.instantiate_table('SHANKAR','TEST1','SYNCTABLE')
Note:- Ensure that the Schema Name, Table Name and dblink name are in Uppercase .
7. Once you are done with instantiation, resume sql apply
SQL> alter database start logical standby apply immediate;
If using BROKER
DGMGRL> edit database logi set state='online';
8.At primary, Revoke the roles granted to the application user - SHANKAR
SQL> revoke SELECT_CATALOG_ROLE from SHANKAR;
SQL> revoke EXP_FULL_DATABASE from SHANKAR;
SQL> revoke IMP_FULL_DATABASE from SHANKAR;
Solution:3
*************
If the sql apply fails even though the data is in sync, you may need to log a service 
request with oracle support with the below events set in Logical standby and the issue being reproduced.
1) In logical standby, stop the sql apply
sql> alter database stop logical standby apply;
if using broker
DGMGRL> edit database logi set state='log-apply-off';
2) Set the below events in logical standby (you may backup and remove all the files in 
/bdump so that you can zip and upload all the files generated in /bdump to Oracle Support)
SQL> alter system set max_dump_file_size=unlimited;
SQL>alter system set events '1349 trace name context forever, level 4095';
SQL> alter system set events '16300 trace name context forever, level 15';
SQL> alter system set events '26700 trace name context forever, level 1544';
SQL>alter system set events '10308 trace name context forever, level 8';
SQL>alter system set events '1403 trace name errorstack level 3';
3) Start the sql apply
sql> alter database start logical standby apply immediate;
if using broker
DGMGRL> edit database logi set state='online';
4) Monitor the alert log to ensure issue is reproduced.
Errors in file c:\oracle\product\10.2.0\admin\logi\bdump\logi_lsp0_4532.trc:
ORA-12801: error signaled in parallel query server P006
ORA-01403: no data found
Log a service request and upload the alert log and trace files generated in /bdump folder
5) Turn off the trace events set earlier.
SQL>alter system set events '1349 trace name context off';
SQL>alter system set events '16300 trace name context off';
SQL>alter system set events '26700 trace name context off';
SQL>alter system set events '10308 trace name context off';
SQL>alter system set events '1403 trace name context off';
6. End

Does Duplicate Target Database need Pre-existing DB backup?

之前有网友问我在10g中通过RMAN 的duplicate target database命令复制数据库时是否需要先完成全库的备份。

实际我在10g中并不常用duplicate target database 来帮助创建DataGuard Standby Database,所以虽然记忆中仍有些印象,却不能十分确定地回答了。

今天查了一下资料,发现原来Active database duplication 和 Backup-based duplication 是11g才引入的特性,换句话说10g中duplication是要求预先完成数据库的RMAN backup备份的。

具体关于以上2个特性见文档<RMAN ‘Duplicate Database’ Feature in 11G>,引文如下:

RMAN 'Duplicate Database' Feature in 11G
You can create a duplicate database using the RMAN duplicate command.
The duplicate database has a different DBID from the source database and functions
entirely independently.Starting from 11g you can do duplicate database in 2 ways.
1. Active database duplication
2. Backup-based duplication
Active database duplication copies the live target database over the network to the
auxiliary destination and then creates the duplicate database.Only difference is that you
don't need to have the pre-existing RMAN backups and copies.
The duplication work is performed by an auxiliary channel.
This channel corresponds to a server session on the auxiliary instance on the auxiliary host.
As part of the duplicating operation, RMAN automates the following steps:
1. Creates a control file for the duplicate database
2. Restarts the auxiliary instance and mounts the duplicate control file
3. Creates the duplicate datafiles and recovers them with incremental backups and archived redo logs.
4. Opens the duplicate database with the RESETLOGS option
For the active database duplication, RMAN does one extra step .i.e. copy the
target database datafiles over the network to the auxiliary instance
A RAC TARGET database can be duplicated as well. The procedure is the same as below.
If the auxiliary instance needs to be a RAC-database as well,
than start the duplicate procedure for to a single instance and convert
the auxiliary to RAC after the duplicate has succeeded.

 

而在10g 中不仅需要对目标数据库进行备份,还需要手动将备份集(backupset)拷贝至目标主机上,这确实过于繁琐了:

 

Oracle10G RMAN Database Duplication
 If you are using a disk backup solution and duplicate to a
remote node you must first copy the backupsets from the original hosts backup
location to the same mount and path on the remote server. Because duplication
uses auxiliary channels the files must be where the IO pipe is allocated. So the
IO will take place on the remote node and disk backups must be locally available.

11gR2新特性:STANDBY_MAX_DATA_DELAY

Active Data Guard 是 Oracle 11g 的亮点特性之一,而在11G release 2中对Active Data Guard引入了更多诱人的新特性,这些特性将Active Data Guard打造成Oracle 读写分离或报表查询的理想方案之一。

 

STANDBY_MAX_DATA_DELAY是11gr2中对Active Data Guard的最大增强(buffer)之一,这是一个可以在会话级别指定的参数(session parameter),该参数指定了在Primary Database已commit提交的变化与standby Database数据库上涉及相关变化的查询之间所允许的时间延迟,单位为second 秒(Specifies a limit for the amount of time (in seconds) allowed to elapse between when changes are committed on the primary and when those same changes can be queries  on the standby database)。

 

使用该STANDBY_MAX_DATA_DELAY参数的语法如下:

ALTER SESSION SET STANDBY_MAX_DATA_DELAY ={ NONE | INTEGER }

 

注意事项

  • 该参数无法为SYS用户所用,在SYS用户的SESSION下设置该参数将被忽略
  • 若没有指定STANDBY_MAX_DATA_DELAY,即使用其默认值NONE,那么无论主备库之间有多大的延迟,在Physical Standby上的查询都会被执行
  • 若查询延迟超过STANDBY_MAX_DATA_DELAY所指定的值那么,将报ORA-03172错误:

 

03172, 00000, "STANDBY_MAX_DATA_DELAY of %s seconds exceeded"
// *Cause:  Standby recovery fell behind the STANDBY_MAX_DATA_DELAY
//          requirement.
// *Action: Tune recovery and retry the query later, or switch to another
//          standby database within the data delay requirement.

在实际运用中STANDBY_MAX_DATA_DELAY保证了在Standby数据库上所作的报表查询不会得到过于陈旧的结果(stale result),通过该参数我们可以指定一个报表应用所容许的数据时间延迟。

当然也可以指定不容许任何数据延迟,即设置STANDBY_MAX_DATA_DELAY为零,以便做到实时数据查询。

配置Primary 与 Standby 数据库之间的实时查询或者说零延迟查询有以下注意事项:

  • 只有特定的应用程序才会对数据延迟有零容忍的需求,注意你的应用程序是否有如此苛刻的要求
  • 在Standby数据库上执行的查询语句必须返回和主库上查询的完全一致的结果
  • 必须设置STANDBY_MAX_DATA_DELAY 为0
  • 在查询开始的那一刻,Standby数据库必须同步到与Primary数据库一致的Current Scn
  • 若结果没有在200ms内返回,则查询会因ORA-03172而终止
  • Primary数据库必须采用最大可用(max availability)或最大保护(maximum protection)模式
  • redo 传输必须使用SYNC 选项
  • 必须启用 Real-Time Query 特性

 

实际使用

 

以下我们通过演示来了解该STANDBY_MAX_DATA_DELAY的效果:

SQL> select * from v$version;  
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production
SQL> select * from global_name;
GLOBAL_NAME
--------------------------------------------------------------------------------
www.askmaclean.com & www.askmaclean.com
Primary Database  SQL> conn maclean/maclean
Connected.
Primary Database SQL> select database_role,protection_mode from v$database;
DATABASE_ROLE    PROTECTION_MODE
---------------- --------------------
PRIMARY          MAXIMUM AVAILABILITY
Primary Database SQL>  create table TSMDD tablespace users as select * From dba_objects;
Table created.
Standby Database SQL> conn maclean/maclean
Connected.
Standby Database SQL> select database_role,protection_mode from v$database;
DATABASE_ROLE    PROTECTION_MODE
---------------- --------------------
PHYSICAL STANDBY MAXIMUM AVAILABILITY
注意STANDBY_MAX_DATA_DELAY是一个会话参数session parameter,而非实例参数instance parameter
Standby Database SQL> select name from v$system_parameter where name='standby_max_data_delay';
no rows selected
Standby Database SQL> alter session set STANDBY_MAX_DATA_DELAY=0;
Session altered.
Standby Database SQL> select count(*) from TSMDD; 
COUNT(*)
----------
13378

 

实际测试可以发现当STANDBY_MAX_DATA_DELAY=0时,并不是查询语句执行时间超过200ms就返回ORA-03172错误,而是指从查询开始的200ms内,若备库没有追上主库的Current SCN时出现ORA-03172。

 

Standby Database SQL> alter session set STANDBY_MAX_DATA_DELAY=0;
Session altered.
Standby Database SQL> set timing on;
Standby Database SQL> select count(1) from TSMDD a, TSMDD b;
COUNT(1)
----------
178970884
Elapsed: 00:00:05.34
Standby Database SQL> alter session set events '10046 trace name context forever,level 12';
Session altered.
在主库上执行大数据量的insert操作,但是不提交commit;
Primary Database SQL> insert into /*+ append */  tsmdd select * from tsmdd;
此时在Standby 数据库 上执行查询语句将触发ORA-3172错误
Standby Database SQL> select count(*) from tsmdd
*
ERROR at line 1:
ORA-03172: STANDBY_MAX_DATA_DELAY of 0 seconds exceeded
Standby Database SQL>  /
select count(*) from tsmdd
*
ERROR at line 1:
ORA-03172: STANDBY_MAX_DATA_DELAY of 0 seconds exceeded

 

以上查询语句执行过程中的10046 trace如下:

 

PARSING IN CURSOR #47828795969456 len=26 dep=0 uid=34 oct=3 lid=34 tim=1316692536000853
hv=2314050071 ad='7115e798' sqlid='3smn48y4yv6hr'
select count(*) from tsmdd
END OF STMT
PARSE #47828795969456:c=0,e=61,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1739041831,tim=1316692536000852
WAIT #47828795969456: nam='standby query scn advance'
ela= 201440 p1=770798 p2=0 p3=20 obj#=13873 tim=1316692536202337
WAIT #47828795969456: nam='SQL*Net break/reset to client' ela= 25 driver id=1650815232
break?=1 p3=0 obj#=13873 tim=1316692536202528
WAIT #47828795969456: nam='SQL*Net break/reset to client' ela= 144 driver id=1650815232
break?=0 p3=0 obj#=13873 tim=1316692536202694
WAIT #47828795969456: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1
p3=0 obj#=13873 tim=1316692536202715
*** 2011-09-22 19:55:37.983
WAIT #47828795969456: nam='SQL*Net message from client' ela= 1781108 driver
id=1650815232 #bytes=1 p3=0 obj#=13873 tim=1316692537983884
CLOSE #47828795969456:c=0,e=24,dep=0,type=0,tim=1316692537984068
===============================================================================================
PARSING IN CURSOR #47828795969456 len=26 dep=0 uid=34 oct=3 lid=34 tim=1316692537984172
hv=2314050071 ad='7115e798' sqlid='3smn48y4yv6hr'
select count(*) from tsmdd
END OF STMT
PARSE #47828795969456:c=0,e=53,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1739041831,tim=1316692537984171
WAIT #47828795969456: nam='standby query scn advance' ela= 200546 p1=770914
p2=0 p3=20 obj#=13873 tim=1316692538184822
WAIT #47828795969456: nam='SQL*Net break/reset to client' ela= 10 driver
id=1650815232 break?=1 p3=0 obj#=13873 tim=1316692538184998
WAIT #47828795969456: nam='SQL*Net break/reset to client' ela= 103 driver
id=1650815232 break?=0 p3=0 obj#=13873 tim=1316692538185154
WAIT #47828795969456: nam='SQL*Net message to client' ela= 1 driver
id=1650815232 #bytes=1 p3=0 obj#=13873 tim=1316692538185182

 

注意这里出现的standby query scn advance等待事件,显然该等待事件是为了确认Primary与Standby之间的Scn差距,但这又是一个Internal的undocumented 等待事件。我猜测是P1是Standby数据库的Current Scn,而p3可能是Primary 与 Standby之间的Scn 差距。OBJ#是查询对象的object_id:

 

SQL> col owner for a20
SQL> col object_name for a20
SQL> select owner,object_name from dba_objects where object_id=13873;
OWNER                OBJECT_NAME
-------------------- --------------------
MACLEAN              TSMDD

 

使用技巧

 

在实际的使用过程中我们没有必要每次登录会话查询都去指定STANDBY_MAX_DATA_DELAY参数,可以通过创建AFTER LOGON触发器来简化工作。

在11 g Release 2中引入了USERENV Context的一种新属性DATABASE_ROLE,使用该属性可以便捷地定位用户所登录数据库的角色是Primary 还是 Standby,11g的SQL 和 PL/SQL客户端程序均可以通过 SYS_CONTEXT 函数获取该数据库角色信息。

通过创建以下登陆后触发器可以做到当应用程序登录到启用实时查询的Standby数据库上后即自动设置合适的STANDBY_MAX_DATA_DELAY参数。这样即避免了修改应用程序的代码,有做到了配置合理的最大数据延迟。

CREATE OR REPLACE TRIGGER AUTO_SMDD
AFTER LOGON ON USER.SCHEMA
BEGIN
IF (SYS_CONTEXT('USERENV', 'DATABASE_ROLE') IN ('PHYSICAL STANDBY')) THEN
execute immediate 'alter session set standby_max_data_delay=5';
END IF;
END;

 

注意以上trigger 只需要在Primary Database上以应用相关用户身份建立即可,会同步到Standby上:

 

Primary Database SQL>  conn maclean/maclean
Connected.
Primary Database SQL> CREATE OR REPLACE TRIGGER AUTO_SMDD
  2    AFTER LOGON ON MACLEAN.SCHEMA
  3  BEGIN
  4    IF (SYS_CONTEXT('USERENV', 'DATABASE_ROLE') IN ('PHYSICAL STANDBY')) THEN
  5      execute immediate 'alter session set standby_max_data_delay=0';
  6    END IF;
  7  END;
  8  /
Trigger created.

解决DGMGRL Unable to connect to database ORA-12541: TNS:no listener问题

晚上配了一套11.2.0.2 的DataGuard物理备库,准备用DGMGRL做切换测试,结果发现在切换过程中会遇到”Unable to connect to database”的问题,具体日志如下:

 

DGMGRL> switchover to dgogg
Performing switchover NOW, please wait...
New primary database "dgogg" is opening...
Operation requires shutdown of instance "SBDB" on database "sbdb"
Shutting down instance "SBDB"...
ORACLE instance shut down.
Operation requires startup of instance "SBDB" on database "sbdb"
Starting instance "SBDB"...
Unable to connect to database
ORA-12541: TNS:no listener
Failed.
Warning: You are no longer connected to ORACLE.
Please complete the following steps to finish switchover:
start up instance "SBDB" of database "sbdb"

 

ORA-12541: TNS:no listener显然是因为DGMGRL使用的连接串ConnectIdentifier存在问题,但是在创建DGMGRL的Configuration之前我已经将SERIVCENAME_DGMGLR形式的静态信息添加到listener.ora文件中了,如:

 

DGLSN =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vrh3.oracle.com)(PORT = 1588))
)
SID_LIST_DGLSN =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = DGOGG)
(ORACLE_HOME = /s01/orabase/product/11.2.0/dbhome_1)
(SID_NAME = DGOGG)
)
(SID_DESC =
(GLOBAL_DBNAME = DGOGG_DGB)
(ORACLE_HOME = /s01/orabase/product/11.2.0/dbhome_1)
(SID_NAME = DGOGG)
)
(SID_DESC =
(GLOBAL_DBNAME = DGOGG_DGMGRL)
(ORACLE_HOME = /s01/orabase/product/11.2.0/dbhome_1)
(SID_NAME = DGOGG)
)
)

 

查了半天最后终于发现时因为监听端口的问题,我创建的LISTENER DGLSN使用1588端口,而dgmgrl中的静态连接串默认使用1521端口,如:

 

DGMGRL> show database dgogg
Database - dgogg
Role:            PRIMARY
Intended State:  TRANSPORT-ON
Instance(s):
DGOGG
Database Status:
SUCCESS
DGMGRL> show database verbose dgogg
Database - dgogg
Role:            PRIMARY
Intended State:  TRANSPORT-ON
Instance(s):
DGOGG
Properties:
DGConnectIdentifier             = 'dgogg_dgb'
ObserverConnectIdentifier       = ''
LogXptMode                      = 'sync'
DelayMins                       = '0'
Binding                         = 'optional'
MaxFailure                      = '0'
MaxConnections                  = '1'
ReopenSecs                      = '300'
NetTimeout                      = '30'
RedoCompression                 = 'DISABLE'
LogShipping                     = 'ON'
PreferredApplyInstance          = ''
ApplyInstanceTimeout            = '0'
ApplyParallel                   = 'AUTO'
StandbyFileManagement           = 'MANUAL'
ArchiveLagTarget                = '0'
LogArchiveMaxProcesses          = '4'
LogArchiveMinSucceedDest        = '1'
DbFileNameConvert               = ''
LogFileNameConvert              = ''
FastStartFailoverTarget         = ''
InconsistentProperties          = '(monitor)'
InconsistentLogXptProps         = '(monitor)'
SendQEntries                    = '(monitor)'
LogXptStatus                    = '(monitor)'
RecvQEntries                    = '(monitor)'
SidName                         = 'DGOGG'
StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=vrh3.oracle.com)
 (PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DGOGG_DGMGRL)(INSTANCE_NAME=DGOGG)(SERVER=DEDICATED)))'
StandbyArchiveLocation          = '/s01/orabase/arch'
AlternateLocation               = ''
LogArchiveTrace                 = '0'
LogArchiveFormat                = '%t_%s_%r.dbf'
TopWaitEvents                   = '(monitor)'

 

了解到问题所在后就容易解决了,只需要修改Broker中的StaticConnectIdentifier就可以了:

 

edit database dgogg set property StaticConnectIdentifier='(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vrh3.oracle.com)(PORT = 1588)) (CONNECT_DATA =
(SERVER = DEDICATED) (SERVICE_NAME = DGOGG_DGMGRL)))';
edit database sbdb set property StaticConnectIdentifier='(DESCRIPTION =
(ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = vrh4.oracle.com)(PORT = 1588))) (CONNECT_DATA =
(SERVICE_NAME = SBDB_DGMGRL)))';

 

再次尝试切换Switchover DataGuard,不再需要手动启动standby 实例了:

 

DGMGRL> switchover to sbdb;
Performing switchover NOW, please wait...
New primary database "sbdb" is opening...
Operation requires shutdown of instance "DGOGG" on database "dgogg"
Shutting down instance "DGOGG"...
ORACLE instance shut down.
Operation requires startup of instance "DGOGG" on database "dgogg"
Starting instance "DGOGG"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "sbdb"
DGMGRL> show configuration
Configuration - dgogg
Protection Mode: MaxAvailability
Databases:
sbdb  - Primary database
dgogg - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

DataGuard与异构平台

DataGuard对主备库异构平台的支持一直是让很多人纠结的问题,我们在学习Oracle数据卫士时必要优先阅读的官方文档是<Oracle Data Guard Concepts and Administration 10g Release 2>,在这个文档中给出了极为苛刻的硬件环境限制条件:

All members of a Data Guard configuration must run an Oracle image that is built for the same platform.
For example, this means a Data Guard configuration with a primary database on a 32-bit Linux on Intel system
can have a standby database that is configured on a 32-bit Linux on Intel system. However, a primary database
on a 64-bit HP-UX system can also be configured with a standby database on a 32-bit HP-UX system,
as long as both servers are running 32-bit images.

以上主要提出了2点要求:即平台(platform)和字长(word size)都必须一致,注意这里不管是physical standby还是logical standby都要求遵守。

官方文档这样撰写的原因是Oracle并不推荐用户在异构平台上搭建DataGuard,如果有用户大量地部署异构平台上的DataGuard可能给后续的服务和支持带来麻烦;所以除非是找不到其他可用的硬件了,否则不推荐采用异构平台搭建的DataGuard环境来提供高可用性。

实际情况是在10g中已经有少数几个异构平台组合可以兼容physical standby或logical standby,而在11g中更增加了对physical standby支持的几种异构平台组合(As of Oracle Database 11g Data Guard provides increased flexibility for Data Guard configurations in which the primary and standby systems may have different CPU architectures, operating systems (for example, Windows & Linux), operating system binaries (32-bit/64-bit), or Oracle database binaries (32-bit/64-bit). For specific information about mixed-platform support, see the My Oracle Support note 413484.1)。

具体10g/11g DataGuard可以利用的异构平台组合,见下列图表:

10g
Physical Standby Logical Standby
Heterogeneous
Support
No Win<->Linux Only
Different
Word-size
(32 / 64 bit)
Win32<->Win64
Linux32<->Linux64
Win32->Win64  

Linux32->Linux64 (1 way only)

Heterogeneous AND Word-size No No

 

11g
Physical Standby Logical Standby
Heterogeneous
Support
Win<–>Linux
Solaris <–>AIX
Solaris<–>Linux
Win<->Linux Only
Different
Word-size
(32 / 64 bit)
Win32<->Win64
Linux32<->Linux64
Win32->Win64 Linux32->Linux64 (1 way only)
Heterogeneous AND Word-size Win32<->Linux64 Win32->Linux64
(1 way only)

 

而在metalink文档<Data Guard Support for Heterogeneous Primary and Physical Standbys in Same Data Guard Configuration [ID 413484.1]><Data Guard Support for Heterogeneous Primary and Logical Standbys in Same Data Guard Configuration [ID 1085687.1]>中列出了更为详尽的平台间的兼容信息,在这里一并引用:

Physical Standbys

In addition to general support when using the same Oracle platform, Data Guard Redo Apply (physical standby) can support specific mixed Oracle Platform combinations.  Oracle Platform IDs, platform names, and which combinations of platform ID(s) that can be combined to form a supported Data Guard configuration using Redo Apply are listed in the table below.  Platform combinations not listed in the table below are not supported using Data Guard Redo Apply.

Table Notes

  1. Prior to Data Guard 11g, the Data Guard Broker did not support different word-size in the same Data Guard configuration, thus requiring management from the SQL*Plus command line for mixed word-size Data Guard configurations.  This restriction is lifted from Data Guard 11g onward.
  2. Both primary and standby databases must be set at the same compatibility mode as the minimum release (if specified) in the table below.
  3. A standby database cannot be open read-only in any environment that has binary-level PL/SQL-related incompatibilities between primary and standby databases.  Support Note 414043.1 is referenced in the table below for any platform combinations where this is the case (the note provides instructions for eliminating incompatibilities post role transition).  It is possible to access a standby database in such environments in Oracle Database 11g by temporarily converting it to a Snapshot Standby database, or in Oracle Database 10g by opening the standby read/write as described in the Data Guard 10g Concepts and Administration guide: Using a Physical Standby Database for Read/Write Testing and Reporting. Both procedures require following the steps in note 414043.1 before making the database available to users.
  4. Please be sure to read Support Notes when referenced in the table below.
  5. RMAN generally supports instantiation of a physical standby database for the supported platform combinations. Please see Support Note 1079563.1 for details.
  6. Platforms in a supported combination may operate in either the primary or standby role.
  7. Enterprise Manager can not be used for standby database creation or other administrative functions in any configuration where PLATFORM_IDs are not identical. Oracle recommends using the Data Guard Broker command line interface (DGMGRL) to administer mixed platform combinations from Oracle Database 11g onward and SQL*Plus command line for configurations that pre-date Oracle Database 11g.
PLATFORM_ID PLATFORM_NAME
Release name
PLATFORM_IDs supported within the same Data Guard configuration when using Data Guard Redo Apply (Physical Standby)
2 Solaris[tm] OE (64-bit)
Solaris Operating System (SPARC) (64-bit)
2
6 – Oracle 11.2.0.2 onward, primary database must be non-RAC and non-TDE
3 HP-UX (64-bit)
HP-UX PA-RISC
3
4 – Oracle 10g onward, see Support Notes 395982.1 and 414043.1
4 HP-UX IA (64-bit)
HP-UX Itanium
4
3 – Oracle 10g onward, see Support Notes 395982.1 and 414043.1
5 HP Tru64 UNIX
HP Tru64 UNIX
5
6 IBM AIX on POWER Systems (64-bit) 2 – Oracle 11.2.0.2 onward, primary database must be non-RAC and non-TDE
6
7 Microsoft Windows (32-bit)
Microsoft Windows (x86)
7
8, 12  – Oracle 10g onward, see Support Note 414043.1
10 – Oracle 11g onward
11, 13 – Oracle 11g onward, see Support Note 414043.1
8 Microsoft Windows IA (64-bit)
Microsoft Windows (64-bit Itanium)
7 – Oracle 10g onward, see Support Note 414043.1
8
12 – Oracle 10g onward
11, 13 – Oracle 11g onward
9 IBM zSeries Based Linux
z/Linux
9
18 (64-bit zSeries only)
10 Linux (32-bit)
Linux x86
7 – Oracle 11g onward
10
11, 13 – Oracle 10g onward, see Support Note 414043.1
11 Linux IA (64-bit)
Linux Itanium
10 – Oracle 10g onward, see Support Note 414043.1
11
13 – Oracle 10g onward
7 – Oracle 11g onward, see Support Note 414043.1
8, 12 – Oracle 11g onward
12 Microsoft Windows 64-bit for AMD
Microsoft Windows (x86-64)
7 – Oracle 10g onward, see Support Note 414043.1
8 – Oracle 10g onward
12
11, 13 – Oracle 11g onward
13 Linux 64-bit for AMD
Linux x86-64
7 – Oracle 11g onward, see Support Note 414043.1
10 – Oracle 10g onward, see Support Note 414043.1
11 – Oracle 10g onward
8, 12 – Oracle 11g onward
13
20 – Oracle 11g onward
15 HP Open VMS
HP OpenVMS Alpha
HP IA OpenVMS
OpenVMS Itanium
15
16 Apple Mac OS
Mac OS X Server
16
17 Solaris Operating System (x86)
Solaris Operating System (x86)
17
20 – Oracle 10g onward, see Support Note 414043.1
18 IBM Power Based Linux
Linux on Power
9 (64-bit zSeries only)
18
20 Solaris Operating System (AMD64)
Solaris Operating System (x86-64)
13 – Oracle 11g onward
17 – Oracle 10g onward, see Support Note 414043.1
20

Logical Standby

In addition to general support when using the same Oracle platform, Data Guard SQL Apply (logical standby) can support specific mixed Oracle Platform combinations as of Oracle Database 11g.  Oracle Platform IDs, platform names, and which combinations of platform ID(s) that can be combined to form a supported Data Guard configuration using SQL Apply are listed in the table below.  Platform combinations not listed in the table below are not supported using Data Guard SQL Apply.

Table Notes

  1. All mixed platform combinations for SQL Apply in the table below are supported from Oracle Database 11g onward.
  2. Prior to Data Guard Broker 11g, the Data Guard Broker did not support different word-size in the same Data Guard configuration, thus requiring all management from the SQL*Plus command line.  This restriction is lifted from Data Guard 11g onward.
  3. Both primary and standby databases must be set at the same compatibility mode as the minimum release (if specified) in the table below.
  4. Please be sure to read Support Notes when referenced in the table below.
  5. RMAN generally supports instantiation of a physical standby database for the supported platform combinations. Please see Support Note 1079563.1 for details.
  6. Platforms in a supported combination may operate in either the primary or standby role unless otherwise specified.
  7. Enterprise Manager can not be used for standby database creation or other administrative functions in any configuration where PLATFORM_IDs are not identical. Oracle recommends using the Data Guard Broker command line interface (DGMGRL) to administer mixed platform combinations from Oracle Database 11g onward and SQL*Plus command line for configurations that pre-date Oracle Database 11g.
PLATFORM_ID PLATFORM_NAME
Release name
PLATFORM_IDs supported within the same Data Guard configuration when using Data Guard SQL Apply (Logical Standby)
2 Solaris[tm] OE (64-bit)
Solaris Operating System (SPARC) (64-bit)
2
3 HP-UX (64-bit)
HP-UX PA-RISC
3, 4
4 HP-UX IA (64-bit)
HP-UX Itanium
3, 4
5 HP Tru64 UNIX
HP Tru64 UNIX
5
6 AIX-Based Systems (64-bit)
AIX5L
6
7 Microsoft Windows (32-bit)
Microsoft Windows (x86)
7, 10
8, 12 – Replication can only occur from a 32-bit primary to a 64-bit standby, once a role transition has promoted the 64-bit system to the primary role, the original 32-bit primary is not supported as a standby database.
8 Microsoft Windows IA (64-bit)
Microsoft Windows (64-bit Itanium)
7 – Replication can only occur from a 32-bit primary to a 64-bit standby, once a role transition has promoted the 64-bit system to the primary role, the original 32-bit primary is not supported as a standby database.
8, 11, 12, 13
9 IBM zSeries Based Linux
z/Linux
9
10 Linux (32-bit)
Linux x86
7, 10
11, 13 – Replication can only occur from a 32-bit primary to a 64-bit standby, once a role transition has promoted the 64-bit system to the primary role, the original 32-bit primary is not supported as a standby database.
11 Linux IA (64-bit)
Linux Itanium
10 – Replication can only occur from a 32-bit primary to a 64-bit standby, once a role transition has promoted the 64-bit system to the primary role, the original 32-bit primary is not supported as a standby database.
8, 11, 13
12 Microsoft Windows 64-bit for AMD
Microsoft Windows (x86-64)
7 – from Oracle 11g onward.  Replication can only occur from a 32-bit primary to a 64-bit standby, once a role transition has promoted the 64-bit system to the primary role, the original 32-bit primary is not supported as a standby database.
8, 12
13 Linux 64-bit for AMD
Linux x86-64
10 – Replication can only occur from a 32-bit primary to a 64-bit standby, once a role transition has promoted the 64-bit system to the primary role, the original 32-bit primary is not supported as a standby database.
8, 11, 13
15 HP Open VMS
HP OpenVMS Alpha
HP IA OpenVMS
OpenVMS Itanium
15
16 Apple Mac OS
Mac OS X Server
16
17 Solaris Operating System (x86)
Solaris Operating System (x86)
17
18 IBM Power Based Linux
Linux on Power
18
20 Solaris Operating System (AMD64)
Solaris Operating System (x86-64)
20

Reference:

<Cross Platform Database Migrations>-Owen Ireland

<Data Guard Support for Heterogeneous Primary and Physical Standbys in Same Data Guard Configuration [ID 413484.1]>

<Data Guard Support for Heterogeneous Primary and Logical Standbys in Same Data Guard Configuration [ID 1085687.1]>



沪ICP备14014813号

沪公网安备 31010802001379号