Database Software

Hi, I'm having a problem with the Oracle Physru script provided from MOS note # 949322 I was hoping I could get some help with.

My system contains of two Oracle 11.1.0.7.0, one primary and one physical standby.
The hosts are two Oracle Solaris 10 Sparc x64 machines.

My goal is to update from 11.1.0.7.0 to 11.1.0.7.8 using the script that is provided, however I'm bumping into problems at the step where the script is checking the Apply lag on the logical standby (function "get_apply_lag"). The lag seems to increase, and to be that indicates a problem with the redo apply process. However, when I query the DBA_LOGSTDBY_EVENTS View i get the following:

SQL> SELECT EVENT_TIME, STATUS, EVENT FROM DBA_LOGSTDBY_EVENTS ORDER BY EVENT_TIMESTAMP, COMMIT_SCN;

EVENT_TIME STATUS EVENT
--------------------------------------------------------------------------------
20-SEP-11 16:02:12 ORA-16111: log mining and apply setting up
20-SEP-11 16:02:12 Apply LWM 848622, HWM 848622, SCN 848622

Showing the output from Primary archive dest 2:

SQL> show parameter log_archive_dest_2;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string service="test08db2", LGWR ASYNC NOAFFIRM delay=0 OPTIONAL compression=DISABLE max_failure=0 max_connections=1 reopen=300 db_unique_name="db_test08db2" net_timeout=30 valid_for=(online_logfile,primary_role)


SQL> select from v$LOGSTDBY_STATS;*

NAME VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
logminer session id 1
number of preparers 1
number of appliers 11
server processes in use 15
maximum SGA for LCR cache (MB) 50
maximum events recorded 2000000000
preserve commit order TRUE
transaction consistency FULL
record skipped errors Y
record skipped DDLs Y
record applied DDLs N

NAME VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
record unsupported operations Y
realtime apply Y
apply delay (minutes) 0
coordinator state IDLE
coordinator startup time 20-SEP-11 16:02:11
coordinator uptime (seconds) 727
txns received from logminer 62
txns assigned to apply 29
txns applied 29
txns discarded during restart 33
large txns waiting to be assigned 0

NAME VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
rolled back txns mined 4
DDL txns mined 2
CTAS txns mined 0
bytes of redo mined 8195884
bytes paged out 0
pageout time (seconds) 0
bytes checkpointed 709802
checkpoint time (seconds) 0
system idle time (seconds) 479
standby redo logs mined 0
archived logs mined 4

NAME VALUE
---------------------------------------------------------------- ----------------------------------------------------------------
gap fetched logs mined 2
standby redo log reuse detected 0
logfile open failures 0
current logfile wait (seconds) 0
total logfile wait (seconds) 0
thread enable mined 0
thread disable mined 0
distinct txns in queue 0

41 rows selected.

SQL> select type, high_scn, status, pid from v$logstdby order by type;

TYPE HIGH_SCN STATUS PID
------------------------------ -------- ------------------------------------------------------------ ------------
ANALYZER 850320 ORA-16116: no work available 20702
APPLIER 848808 ORA-16116: no work available 20719
APPLIER 850320 ORA-16116: no work available 20731
APPLIER 850204 ORA-16116: no work available 20727
APPLIER 848895 ORA-16116: no work available 20725
APPLIER 848665 ORA-16116: no work available 20705
APPLIER 848677 ORA-16116: no work available 20709
APPLIER 848728 ORA-16116: no work available 20713
APPLIER 848740 ORA-16116: no work available 20715
APPLIER 848796 ORA-16116: no work available 20717
APPLIER 848842 ORA-16116: no work available 20721

TYPE HIGH_SCN STATUS PID
------------------------------ -------- ------------------------------------------------------------ ------------
APPLIER 848854 ORA-16116: no work available 20723
BUILDER 850320 ORA-16116: no work available 20221
COORDINATOR 850326 ORA-16116: no work available 20119
PREPARER 850318 ORA-16116: no work available 20223
READER 850326 ORA-16116: no work available 20217

16 rows selected.

Physru Script Output:_

### Initialize script to either start over or resume execution
Sep 20 14:35:41 2011 [0-1] Identifying rdbms software version
Sep 20 14:35:41 2011 [0-1] database nobilldb is at version 11.1.0.7.0
Sep 20 14:35:42 2011 [0-1] database nobilldb is at version 11.1.0.7.0
Sep 20 14:35:44 2011 [0-1] verifying flashback database is enabled at db_test08db1 and db_test08db2
Sep 20 14:35:44 2011 [0-1] verifying available flashback restore points
Sep 20 14:35:45 2011 [0-1] verifying DG Broker is disabled
Sep 20 14:35:46 2011 [0-1] looking up prior execution history
Sep 20 14:35:46 2011 [0-1] purging script execution state from database db_test08db1
Sep 20 14:35:46 2011 [0-1] purging script execution state from database db_test08db2
Sep 20 14:35:47 2011 [0-1] starting new execution of script

### Stage 1: Backup user environment in case rolling upgrade is aborted
Sep 20 14:35:47 2011 [1-1] stopping media recovery on db_test08db2
Sep 20 14:35:48 2011 [1-1] creating restore point PRUP_0000_0001 on database db_test08db2
Sep 20 14:35:49 2011 [1-1] backing up current control file on db_test08db2
Sep 20 14:35:50 2011 [1-1] created backup control file /opt/oracle/product/11.1.0.7/dbs/PRUP_0001_db_test08db2_f.f
Sep 20 14:35:50 2011 [1-1] creating restore point PRUP_0000_0001 on database db_test08db1
Sep 20 14:35:51 2011 [1-1] backing up current control file on db_test08db1
Sep 20 14:35:52 2011 [1-1] created backup control file /opt/oracle/product/11.1.0.7/dbs/PRUP_0001_db_test08db1_f.f

NOTE: Restore point PRUP_0000_0001 and backup control file PRUP_0001_db_test08db2_f.f
can be used to restore db_test08db2 back to its original state as a
physical standby, in case the rolling upgrade operation needs to be aborted
prior to the first switchover done in Stage 4.

### Stage 2: Create transient logical standby from existing physical standby
Sep 20 14:35:53 2011 [2-1] verifying RAC is disabled at db_test08db2
Sep 20 14:35:53 2011 [2-1] verifying database roles
Sep 20 14:35:54 2011 [2-1] verifying physical standby is mounted
Sep 20 14:35:54 2011 [2-1] verifying database protection mode
Sep 20 14:35:55 2011 [2-1] verifying transient logical standby datatype support
Sep 20 14:36:00 2011 [2-2] starting media recovery on db_test08db2
Sep 20 14:36:11 2011 [2-2] confirming media recovery is running
Sep 20 14:36:12 2011 [2-2] waiting for v$dataguard_stats view to initialize
Sep 20 14:36:13 2011 [2-2] waiting for apply lag on db_test08db2 to fall below 30 seconds
Sep 20 14:36:13 2011 [2-2] apply lag is now less than 30 seconds
Sep 20 14:36:14 2011 [2-2] stopping media recovery on db_test08db2
Sep 20 14:36:15 2011 [2-2] executing dbms_logstdby.build on database db_test08db1
Sep 20 14:36:27 2011 [2-2] converting physical standby into transient logical standby
Sep 20 14:36:52 2011 [2-3] opening database db_test08db2
Sep 20 14:37:28 2011 [2-4] configuring transient logical standby parameters for rolling upgrade
Sep 20 14:37:29 2011 [2-4] starting logical standby on database db_test08db2
Sep 20 14:37:37 2011 [2-4] waiting until logminer dictionary has fully loaded
Sep 20 14:37:58 2011 [2-4] dictionary load 17% complete
Sep 20 14:38:09 2011 [2-4] dictionary load 32% complete
Sep 20 14:38:19 2011 [2-4] dictionary load 43% complete
Sep 20 14:38:30 2011 [2-4] dictionary load 59% complete
Sep 20 14:38:40 2011 [2-4] dictionary load 62% complete
Sep 20 14:38:50 2011 [2-4] dictionary load 70% complete
Sep 20 14:39:01 2011 [2-4] dictionary load 72% complete
Sep 20 14:39:11 2011 [2-4] dictionary load 75% complete
Sep 20 14:40:54 2011 [2-4] dictionary load is complete
Sep 20 14:41:00 2011 [2-4] waiting for v$dataguard_stats view to initialize
Sep 20 14:41:01 2011 [2-4] waiting for apply lag on db_test08db2 to fall below 30 seconds
Sep 20 14:42:02 2011 [2-4] current apply lag: 316
Sep 20 14:42:32 2011 [2-4] current apply lag: 316
Sep 20 14:43:03 2011 [2-4] current apply lag: 376
Sep 20 14:43:33 2011 [2-4] current apply lag: 376
Sep 20 14:44:03 2011 [2-4] current apply lag: 437
Sep 20 14:44:34 2011 [2-4] current apply lag: 437
Sep 20 14:45:04 2011 [2-4] current apply lag: 497
Sep 20 14:45:35 2011 [2-4] current apply lag: 497
Sep 20 14:46:05 2011 [2-4] current apply lag: 558
Sep 20 14:46:36 2011 [2-4] current apply lag: 558
Sep 20 14:47:06 2011 [2-4] current apply lag: 618


I would appreciate any help I could get, I'm stuck =/

Regards,