OCI , OIC, IDCS, IDM ,DBCS, Databases, EBS ERP ,Linux, GoldenGate, Fusion Applications Practices : DBbroker

Showing posts with label DBbroker. Show all posts

Saturday, April 25, 2015

Thread 1 cannot allocate new log, sequence Checkpoint not complete

We will get the above error when we more update's in the database and less log file groups are available

As for this practice we have two node RAC database as Primary and two Node RAC database standby when the archive needed to be shipped it gives the error message

With out standby when you have more log switches and less log file then this error occurs

To diagnose how many log files we need we need to check the log switch per hour

in the alert log of the Primary you will find the below messages
Beginning log switch checkpoint up to RBA [0x21a1.2.10], SCN: 32758402584
Thread 1 advanced to log sequence 8609 (LGWR switch)
Current log# 1 seq# 8609 mem# 0: +DATA/omprod/onlinelog/group_1.347.834070855
Wed Jul 30 23:32:17 2014
Archived Log entry 14823 added for thread 1 sequence 8608 ID 0xe6e2761a dest 1:
Wed Jul 30 23:32:18 2014
ARC4: Standby redo logfile selected for thread 1 sequence 8608 for destination LOG_ARCHIVE_DEST_2
Wed Jul 30 23:32:37 2014
FAL[server, ARC5]: FAL archive failed, see trace file.
ARCH: FAL archive failed. Archiver continuing
ORACLE Instance OMPROD1 - Archival Error. Archiver continuing.
Wed Jul 30 23:35:57 2014
ALTER SYSTEM ARCHIVE LOG
Wed Jul 30 23:35:57 2014
Thread 1 cannot allocate new log, sequence 8610
Checkpoint not complete
Current log# 1 seq# 8609 mem# 0: +DATA/omprod/onlinelog/group_1.347.834070855
Wed Jul 30 23:36:02 2014
Completed checkpoint up to RBA [0x21a1.2.10], SCN: 32758402584
Beginning log switch checkpoint up to RBA [0x21a2.2.10], SCN: 32758482611
Thread 1 advanced to log sequence 8610 (LGWR switch)
Current log# 2 seq# 8610 mem# 0: +DATA/omprod/onlinelog/group_2.348.834070859
Archived Log entry 14826 added for thread 1 sequence 8609 ID 0xe6e2761a dest 1:
Wed Jul 30 23:36:08 2014
ARC3: Standby redo logfile selected for thread 1 sequence 8609 for destination LOG_ARCHIVE_DEST_2
Wed Jul 30 23:41:10 2014
Completed checkpoint up to RBA [0x21a2.2.10], SCN: 32758482611
Wed Jul 30 23:43:23 2014
Incremental checkpoint up to RBA [0x21a2.250d.0], current log tail at RBA [0x21a2.af3a.0]
Thu Jul 31 00:03:38 2014
Incremental checkpoint up to RBA [0x21a2.48062.0], current log tail at RBA [0x21a2.60776.0]
Thu Jul 31 00:12:11 2014
Beginning log switch checkpoint up to RBA [0x21a3.2.10], SCN: 32759374394
Thread 1 advanced to log sequence 8611 (LGWR switch)

Step 1 Check the Status of the log switch for last one month by using the Below querywhich gives the output for every hour for each day

Query to find the no of log switches generated for each day

SQL> set lines 300;
set pages 999;
SQL> SELECT
2 to_char(first_time,'YYYY-MON-DD') day,
3 to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'99') "00",
4 to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'99') "01",
5 to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'99') "02",
6 to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'99') "03",
7 to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'99') "04",
8 to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'99') "05",
9 to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'99') "06",
10 to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'99') "07",
11 to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'99') "0",
12 to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'99') "09",
13 to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'99') "10",
14 to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'99') "11",
15 to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'99') "12",
16 to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'99') "13",
17 to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'99') "14",
18 to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'99') "15",
19 to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'99') "16",
20 to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'99') "17",
21 to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'99') "18",
22 to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'99') "19",
23 to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'99') "20",
24 to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'99') "21",
25 to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'99') "22",
26 to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'99') "23"
27 from
28 v$log_history
29 GROUP by
30 to_char(first_time,'YYYY-MON-DD')
31 having to_char(first_time,'YYYY-MON-DD') between '2014-JUL-01'and '2014-JUL-31'
32 order by 1;

DAY               00 01 02 03 04 05 06 07 0   09 10 11 12 13 14 15 16 17 18 19 20 21 22 23
----------------- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
2014-JUL-01         3   1   2   2   1   3   3   1   4   5   4   7   4   2   4   2   3   2   2   1   3   4   3   4
2014-JUL-02         3   1   2   3   1   1   3   2   2   5   4   5   3   3   2   4   0   2   1   0   2   1   5   0
2014-JUL-03         2   0   3   3   2   0   2   2   2   3   4   2   5   2   1   3   2   1   2   0   2   1   2   0
2014-JUL-04         3   0   0   2   0   0   3   0   1   0   2   2   0   1   0   1   0   1   2   0   2   2   1   0
2014-JUL-05         2   1   2   3   0   0   3   0   1   2   1   1   2   1   2   2   2 28 25 12   3   5   5   8
2014-JUL-06         5   2   1   4   2   2   3   1   4   4   4   4   6   3   4   2   4   3   3   1   3   2   2   1
2014-JUL-07         3   2   2   2   2   1   3   1   3   5   4   5   6   6   3   4   2   3   3   1   3   3 11   3
2014-JUL-08         3   1   2   2   1   1   4   1   3   5   3   7   7   3   3   3   4   2   2   1   4   3   2   2
2014-JUL-09         2   1   2   2   1   1   3   2   3   3   2   6   4   2   4   4   2   2   3   1   3   4   2   2
2014-JUL-10         3   1   2   2   2   1   2   3   3   3   3   6   5   2   4   3   2   2   3   1   3   3   2   2
2014-JUL-11         3   2   1   3   2   2   3   1   2   2   2   4   2   2   1   2   1   2   2   1   3   3   2   2
2014-JUL-12         2   1   2   3   1   1   3   1   3   2   3   3   3   2   3   3   0   7   2   2   5   4   6   7
2014-JUL-13         4   2   1   4   3   1   4   1   4   3   3   4   4   3   3   4   4   3   3   1   5   2   3   3
2014-JUL-14         2   2   1   3   1   1   4   1   4   3   6   3   4   3   3   4   3   3   2   1   3   4   1   3
2014-JUL-15         2   2   1   2   2   1   4   2   8   4   4   4 10   2   2   4   2   2   3   1   3   3   2   2
2014-JUL-16         2   2   1   3   1   1   4   2   2   5   2   3   4   3   3   4   3   2   2   1   4   3   5   3
2014-JUL-17         2   2   0   3   2   1   3   2   3   5   4   5   4   2   2   3   1   2   1   0   2   4   1   2
2014-JUL-18         1   0   0   2   1   0   2   0   0   2   1   3   0   1   0   1   1   0   2   0   2   2   0   1
2014-JUL-19         2   0   0   1   1   0   2   2   0   7   1   4   1   1   2   1   2   0   2   1   3   3   3   6
2014-JUL-20         7   1   0   3   1   1   2   2   2   5   3   8   3   3   4   3   3   2   4   2   2   4   3   2
2014-JUL-21         3   1   3   2   2   2   4   2   4   7   2   3   4   3   4   2   4   3   2   1   3   4   3   2
2014-JUL-22         2   1   1   3   1   1   3   3   2   4   3   4   4   3   3   2   4   2   2   2   4   2   2   4
2014-JUL-23         3   1   1   3   1   1   4   2   3   5   3   4   3   3   3   3   2   2   1   3   3   2   2   2
2014-JUL-24         1   0   0   2   1   0   3   0   2   4   3   3   4   4   3   3   2   2   0   1   2   4   2   2
2014-JUL-25         1   0   2   0   1   0   2   1   0   2   0   0   1   0   1   2   0   0   1   0   2   2   3   0
2014-JUL-26         2   0   0   2   0   1 33   0   1   2   1   2   3   2   1   3   1   2   2   2   4   3   4   4
2014-JUL-27         4   7   5   4   1   2   5   1   4   4   5   4   4   2   4   4   6   5   3   1   4   3   1   4
2014-JUL-28         2   2   1   3   1   2   4   1   1   3   1   2   1   2   1   3   1   2   1   0   4   2   0   0
2014-JUL-29         2   0   0   0   0   0   0   9   5   1   1   1   3   3   0   1   0   6   1   3   6   2   1   7
2014-JUL-30         8   3   1   2   2   1   3   2   4   4   3   6   4   3   3   4   4   3   3   2   3   3   1   4
2014-JUL-31         2   2   1   2   1   2   4   1   4   5   3   6   3   3   4   3   5   3   4   6   0   0   0   0

31 rows selected.

As this out put show the number of days and hours [24] and how many log switches had been occur per hour

as the average if the log switches are more then 3 per hour we need alt-least 3 -4 log file groups per thread

if it is more then 6 then we need to have double the amount of log files per thread

we can increase the size of the redo log or we can increase the number of redo log according to the environment we can take the steps

Steps to add the ONLINE LOG and STANDBY LOG in the data guard environment

Adding the online log and stand by log in the primary database

Step 1 Check the primary and stand by environment

We need to stop the db broker if we are using the db broker in the environment before adding the files

to stop the db broker we need to disable the parameter

SQL> show parameter dg_broker_start
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start boolean TRUE

In Both Stand by and Primary we need to disable the db broker by setting the parameter as false

SQL> alter system set dg_broker_start=false scope=both sid='*';

System altered.

SQL> show parameter dg_broker_start
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start boolean FALSE

We need to set MANUAL

SQL> show parameter standby_file_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string auto

SQL> alter system set standby_file_management=MANUAL;
System altered.

SQL> show parameter standby_file_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string MANUAL

Step 2 check the log files

SQL> select * from v$logfile;

    GROUP# STATUS TYPE    MEMBER                                                       IS_
---------- ------- ------- ------------------------------------------------------------ ---
         1         ONLINE +DATA/omprod/onlinelog/group_1.347.834070855                 NO
         2         ONLINE +DATA/omprod/onlinelog/group_2.348.834070859                 NO
         3         ONLINE +DATA/omprod/onlinelog/group_3.345.834070869                 NO
         4         ONLINE +DATA/omprod/onlinelog/group_4.346.834070883                 NO
         5         STANDBY +DATA/omprod/onlinelog/group_5.370.854197269                 NO
         6         STANDBY +DATA/omprod/onlinelog/group_6.371.854197307                 NO
         7         STANDBY +DATA/omprod/onlinelog/group_7.372.854197323                 NO
         8         STANDBY +DATA/omprod/onlinelog/group_8.373.854197339                 NO
         9         ONLINE +DATA/omprod/onlinelog/group_9.376.854381087                 NO
        10         ONLINE +DATA/omprod/onlinelog/group_10.377.854381105                NO
        11         STANDBY +DATA/omprod/onlinelog/group_11.378.854381301                NO
        12         STANDBY +DATA/omprod/onlinelog/group_12.379.854381321                NO

12 rows selected.

SQL> select * from v$log;

    GROUP#    THREAD# SEQUENCE#      BYTES BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
         1          1       8648 524288000       1024          1 YES INACTIVE            3.2798E+10 31-JUL-14   3.2798E+10 31-JUL-14
         2          1       8650 524288000       1024          1 NO CURRENT             3.2799E+10 31-JUL-14   2.8147E+14
         3          2       6147 524288000       1024          1 NO CURRENT             3.2799E+10 31-JUL-14   2.8147E+14
         4          2       6146 524288000       1024          1 YES ACTIVE              3.2799E+10 31-JUL-14   3.2799E+10 31-JUL-14
         9          1       8649 524288000       1024          1 YES ACTIVE              3.2798E+10 31-JUL-14   3.2799E+10 31-JUL-14
        10          2       6145 524288000       1024          1 YES INACTIVE            3.2798E+10 31-JUL-14   3.2799E+10 31-JUL-14

Step 3 Add the online log file's in primary database

SQL> ALTER DATABASE ADD LOGFILE THREAD 1 group 13 size 500M;

Database altered.

SQL> ALTER DATABASE ADD LOGFILE THREAD 1 group 14 size 500M;

Database altered.

SQL> ALTER DATABASE ADD LOGFILE THREAD 2 group 15 size 500M;

Database altered.

SQL> ALTER DATABASE ADD LOGFILE THREAD 2 group 16 size 500M;

Database altered.

Step 4 Add Standby log file in the Primary database

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group 17 size 500M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group 18 size 500M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 group 19 size 500M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 group 20 size 500M;

Database altered.

SQL> select * from v$log;

    GROUP#    THREAD# SEQUENCE#      BYTES BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
         1          1       8648 524288000       1024          1 YES INACTIVE            3.2798E+10 31-JUL-14   3.2798E+10 31-JUL-14
         2          1       8650 524288000       1024          1 NO CURRENT             3.2799E+10 31-JUL-14   2.8147E+14
         3          2       6147 524288000       1024          1 NO CURRENT             3.2799E+10 31-JUL-14   2.8147E+14
         4          2       6146 524288000       1024          1 YES INACTIVE            3.2799E+10 31-JUL-14   3.2799E+10 31-JUL-14
         9          1       8649 524288000       1024          1 YES INACTIVE            3.2798E+10 31-JUL-14   3.2799E+10 31-JUL-14
        10          2       6145 524288000       1024          1 YES INACTIVE            3.2798E+10 31-JUL-14   3.2799E+10 31-JUL-14
        13          1          0 524288000       1024          1 YES UNUSED                       0                      0
        14          1          0 524288000       1024          1 YES UNUSED                       0                      0
        15          2          0 524288000       1024          1 YES UNUSED                       0                      0
        16          2          0 524288000       1024          1 YES UNUSED                       0                      0

10 rows selected.

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

    GROUP#    THREAD# SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------
         5          1          0 YES UNASSIGNED
         6          1          0 YES UNASSIGNED
         7          2          0 YES UNASSIGNED
         8          2          0 YES UNASSIGNED
        11          1          0 YES UNASSIGNED
        12          2          0 YES UNASSIGNED
        17          1          0 YES UNASSIGNED
        18          1          0 YES UNASSIGNED
        19          2          0 YES UNASSIGNED
        20          2          0 YES UNASSIGNED

Adding the online log and stand by log in the standby database

Step 1check the status of the log in the stand by environment

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME

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

9 1 8649 524288000 1024 1 YES CLEARING 3.2798E+10 31-JUL-14 3.2799E+10 31-JUL-14

2 1 8650 524288000 1024 1 YES CURRENT 3.2799E+10 31-JUL-14 2.8147E+14

1 1 8648 524288000 1024 1 YES CLEARING 3.2798E+10 31-JUL-14 3.2798E+10 31-JUL-14

4 2 6146 524288000 1024 1 YES CLEARING 3.2799E+10 31-JUL-14 3.2799E+10 31-JUL-14

10 2 6145 524288000 1024 1 YES CLEARING 3.2798E+10 31-JUL-14 3.2799E+10 31-JUL-14

3 2 6147 524288000 1024 1 YES CURRENT 3.2799E+10 31-JUL-14 2.8147E+14

Step 2 add the online log and standby log

SQL> ALTER DATABASE ADD LOGFILE THREAD 1 group 13 size 500M;
ALTER DATABASE ADD LOGFILE THREAD 1 group 13 size 500M
*
ERROR at line 1:

ORA-01156: recovery or flashback in progress may need access to files

When you are getting the above error cancel the recovery the [stop the redo apply mode and start add the log file]

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> ALTER DATABASE ADD LOGFILE THREAD 1 group 13 size 500M;

Database altered.

SQL> ALTER DATABASE ADD LOGFILE THREAD 1 group 14 size 500M;

Database altered.

SQL> ALTER DATABASE ADD LOGFILE THREAD 2 group 15 size 500M;

Database altered.

SQL> ALTER DATABASE ADD LOGFILE THREAD 2 group 16 size 500M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group 17 size 500M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group 18 size 500M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 group 19 size 500M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 group 20 size 500M;

Database altered.

Step 3

SQL> select * from v$log;

    GROUP#    THREAD# SEQUENCE#      BYTES BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
         1          1       8648 524288000       1024          1 YES CLEARING            3.2798E+10 31-JUL-14   3.2798E+10 31-JUL-14
         2          1       8650 524288000       1024          1 YES CURRENT             3.2799E+10 31-JUL-14   2.8147E+14
         9          1       8649 524288000       1024          1 YES CLEARING            3.2798E+10 31-JUL-14   3.2799E+10 31-JUL-14
        14          1          0 524288000       1024          1 YES UNUSED                       0                      0
        13          1          0 524288000       1024          1 YES UNUSED                       0                      0
        10          2       6145 524288000       1024          1 YES CLEARING            3.2798E+10 31-JUL-14   3.2799E+10 31-JUL-14
        15          2          0 524288000       1024          1 YES UNUSED                       0                      0
        16          2          0 524288000       1024          1 YES UNUSED                       0                      0
         4          2       6146 524288000       1024          1 YES CLEARING            3.2799E+10 31-JUL-14   3.2799E+10 31-JUL-14
         3          2       6147 524288000       1024          1 YES CURRENT             3.2799E+10 31-JUL-14   2.8147E+14

Enable the db_broker and standby file management in both the primary and stand by

SQL> alter system set standby_file_management=AUTO scope=both sid='*';

System altered.

SQL> alter system set dg_broker_start=true scope=both sid='*';

System altered.

check the db broker configuration

when we stop the db_broker it will give this error message

DGMGRL> show configuration;
Error:

ORA-16525: the Data Guard broker is not yet available

Configuration details cannot be determined by DGMGRL After starting the db broker db_broker_start=true then you can see the configuration

DGMGRL> show configuration;

Configuration - OMOICDR

Protection Mode: MaxPerformance
Databases:
    OMPROD - Primary database
    OMDRDB - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database 'OMDRDB';

Database - OMDRDB

Role:            PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag:   28 minutes 54 seconds
Apply Lag:       28 minutes 55 seconds
Real Time Query: OFF
Instance(s):
    OMDRDB1 (apply instance)
    OMDRDB2

Database Status:
SUCCESS

From Primary database
SQL> select * from v$log;

    GROUP#    THREAD# SEQUENCE#      BYTES BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
         1          1       8648 524288000       1024          1 YES INACTIVE            3.2798E+10 31-JUL-14   3.2798E+10 31-JUL-14
         2          1       8650 524288000       1024          1 YES INACTIVE            3.2799E+10 31-JUL-14   3.2800E+10 31-JUL-14
         3          2       6147 524288000       1024          1 YES INACTIVE            3.2799E+10 31-JUL-14   3.2800E+10 31-JUL-14
         4          2       6146 524288000       1024          1 YES INACTIVE            3.2799E+10 31-JUL-14   3.2799E+10 31-JUL-14
         9          1       8649 524288000       1024          1 YES INACTIVE            3.2798E+10 31-JUL-14   3.2799E+10 31-JUL-14
        10          2       6145 524288000       1024          1 YES INACTIVE            3.2798E+10 31-JUL-14   3.2799E+10 31-JUL-14
        13          1       8651 524288000       1024          1 YES INACTIVE            3.2800E+10 31-JUL-14   3.2800E+10 31-JUL-14
        14          1       8652 524288000       1024          1 NO CURRENT             3.2800E+10 31-JUL-14   2.8147E+14
        15          2       6148 524288000       1024          1 YES INACTIVE            3.2800E+10 31-JUL-14   3.2800E+10 31-JUL-14
        16          2       6149 524288000       1024          1 NO CURRENT             3.2800E+10 31-JUL-14   2.8147E+14

10 rows selected.

From Stand by database

SQL> select * from v$log;

    GROUP#    THREAD# SEQUENCE#      BYTES BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
         1          1       8648 524288000       1024          1 YES CLEARING            3.2798E+10 31-JUL-14   3.2798E+10 31-JUL-14
         2          1       8650 524288000       1024          1 YES CLEARING            3.2799E+10 31-JUL-14   3.2800E+10 31-JUL-14
         9          1       8649 524288000       1024          1 YES CLEARING            3.2798E+10 31-JUL-14   3.2799E+10 31-JUL-14
        14          1       8652 524288000       1024          1 YES CURRENT             3.2800E+10 31-JUL-14   2.8147E+14
        13          1       8651 524288000       1024          1 YES CLEARING            3.2800E+10 31-JUL-14   3.2800E+10 31-JUL-14
        10          2       6145 524288000       1024          1 YES CLEARING            3.2798E+10 31-JUL-14   3.2799E+10 31-JUL-14
        15          2       6148 524288000       1024          1 YES CLEARING            3.2800E+10 31-JUL-14   3.2800E+10 31-JUL-14
        16          2       6149 524288000       1024          1 YES CURRENT             3.2800E+10 31-JUL-14   2.8147E+14
         4          2       6146 524288000       1024          1 YES CLEARING            3.2799E+10 31-JUL-14   3.2799E+10 31-JUL-14
         3          2       6147 524288000       1024          1 YES CLEARING            3.2799E+10 31-JUL-14   3.2800E+10 31-JUL-14

10 rows selected.

DGMGRL> show database 'OMDRDB';

Database - OMDRDB

Role: PHYSICAL STANDBY

Intended State: APPLY-ON

Transport Lag: 1 minute 59 seconds

Apply Lag: 2 minutes 2 seconds

Real Time Query: OFF

Instance(s):

OMDRDB1 (apply instance)

OMDRDB2

Database Status:

SUCCESS

Thursday, July 31, 2014

ORA-16826: apply service state is inconsistent with the DelayMins property

We will get this Error mostly when we start the standby database redo apply Manually when the db broker is configured .. when the db broker is configured we should use the db broker to stop and start the redo apply
When the error is noted we have to disable and enable the configuration of the db broker to clear the error
In this same practice you can find the details how to stop and start the redo shipping using the db broker
Step 1 check the database status in the dbbroker
Login to the db broker and check the status of the configuration and check the status of the database

Step 2 Disable the configuration and enable it
Legends
show configuration := the command which is used to show the configuration of the db broker
Disable configuration := Disable the Configuration the db broker will stop processing
enable configuration := enables the configuration of the db broker

In the Primary database dbbroker log file we will get the below message
07/31/2014 18:33:04
Data Guard Broker Status Summary:
Type                        Name                             Severity Status
Configuration               OMOICDR                           Warning ORA-16608
Primary Database            OMPROD                            Success ORA-00000
Physical Standby Database   OMDRDB                            Warning ORA-16826
07/31/2014 18:33:20
DISABLE CONFIGURATION
Command DISABLE CONFIGURATION completed
07/31/2014 18:33:34
DISABLE CONFIGURATION
Command DISABLE CONFIGURATION completed
07/31/2014 18:33:50
ENABLE CONFIGURATION
07/31/2014 18:34:29
Command ENABLE CONFIGURATION completed

In the Standby database logfile we will get the below details
Redo Apply is running without USING CURRENT LOGFILE option while DelayMins=0
07/31/2014 18:30:17
DMON Deregistering service OMDRDB_DGB with listener(s)
07/31/2014 18:30:50
DMON Registering service OMDRDB_DGB with listener(s)
07/31/2014 18:30:55
Redo Apply is running without USING CURRENT LOGFILE option while DelayMins=0
07/31/2014 18:31:24
Command ENABLE CONFIGURATION completed

Check the configuration of the 'OMDRDB'

STOP REDO SHIPPING USING DB BROKER

To stop the redo shipping by using the db broker we can Use
Edit database <database_name> set state='TRANSPORT-OFF'; then check the configuration of the primary database
if we use this command it will globally stop the redo shipping to all the stand by database we have
If we have three standby database and we need to stop apply for one database then we need to execute the command as
EDIT DATABASE < name pf stadnby database for which we to stop log shipping> SET PROPERTY LogShipping=OFF;

when you stop the redo shipping using the db broker the log_archive_dest_state_2 will be changed to the reset mode.. as we have configured only one destination for the stand by database

SQL> show parameter log_archive_dest_state_2

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2             string      RESET

Check the log file sequence from the Primary
SQL> select RESETLOGS_CHANGE#, THREAD#, min(SEQUENCE#) mins, max(SEQUENCE#) maxs from v$log_history group by RESETLOGS_CHANGE#, THREAD#;

RESETLOGS_CHANGE#    THREAD#       MINS       MAXS
----------------- ---------- ---------- ----------
       2.3900E+10          1       4283       8646
       2.3900E+10          2       3203       6141

Check the log file sequence from the Stand by
SQL> select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
2 from (select thread# thrd, max(sequence#) almax
3 from v$archived_log
4 where resetlogs_change#=(select resetlogs_change# from v$database)
5 group by thread#) al,
6 (select thread# thrd, max(sequence#) lhmax
7 from v$log_history
8 where resetlogs_change#=(select resetlogs_change# from v$database)
9 group by thread#) lh
10 where al.thrd = lh.thrd;

    Thread Last Seq Received Last Seq Applied
---------- ----------------- ----------------
         1              8644             8644
         2              6140             6139

SQL>
Now you can clearly find the difference between the two log sequecne as the redo shipping had been stooped if you want to enable the redo shipping then again change the transport on property

START REDO SHIPPING USING THE DB BROKER

To start the redo shipping again we need to edit the parameter of the database in the db broker and change to EDIT DATABASE '<primary database name>' SET STATE='TRANSPORT-ON';

SQL> show parameter log_archive_dest_state_2

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2 string ENABLE

Once you enable the transport-on in the db broker then the parameter state is turned to enable

Check the log sequence from the Primary

SQL> select RESETLOGS_CHANGE#, THREAD#, min(SEQUENCE#) mins, max(SEQUENCE#) maxs from v$log_history group by RESETLOGS_CHANGE#, THREAD#;

RESETLOGS_CHANGE#    THREAD#       MINS       MAXS
----------------- ---------- ---------- ----------
       2.3900E+10          1       4287       8648
       2.3900E+10          2       3204       6144

SQL>

Check the log sequence from the stand by

SQL> select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
2 from (select thread# thrd, max(sequence#) almax
3 from v$archived_log
4 where resetlogs_change#=(select resetlogs_change# from v$database)
5 group by thread#) al,
6 (select thread# thrd, max(sequence#) lhmax
7 from v$log_history
8 where resetlogs_change#=(select resetlogs_change# from v$database)
9 group by thread#) lh
10 where al.thrd = lh.thrd;

    Thread Last Seq Received Last Seq Applied
---------- ----------------- ----------------
         1              8648             8648
         2              6144             6143

Now both Primary and standby are sync again

Warning: ORA-16714: the value of property is inconsistent with the database setting

Warning: ORA-16714: the value of property LogArchiveMaxProcesses is inconsistent with the database setting

When we are editing the init parameter's which is effecting the DB broker then we need to change the property value in the db broker also ..

when we changed the property in of the SQL> alter system set log_archive_max_processes=10 scope=both sid='*'; then we need to change the same property in the db broker configuration file also by following method

you can find what are the int parameter's used by the db broker by using the show database verbose <database_name>

Step 1 Check the status in the dgmgrl When we get the error in Db broker config then we need to check the particular database

Step 2 check the two database's configuration

For checking the configuration of the each database we need to type the command as show configuration <database_name>

As when we are checking the Stand by database We are getting the inconsistent property error for the LogArchiveMaxProcesses .. because we had changed the process in the standby database by using the alter system command

Step 3 check the inconsistent properties

For checking the Inconsistent Properties we need to give the command show database<database name> InconsistentProperties

it will show what are the process are inconsistent in the below example we have two process because we are using the two node rac as the stadnby database

Legends

Instance_name:=Name of the instance where the inconsistent properties are located

Property_Name := Name of the inconsistent Property

Memory_value:= 10[the value which is located in the memory of the database]

Spfile_value:= 10[the value which is specified in the spfile]

db broker_value:=4[the value which is specified in the db broker ]

INCONSISTENT PROPERTIES
   INSTANCE_NAME      PROPERTY_NAME            MEMORY_VALUE SPFILE_VALUE    BROKER_VALUE
   OMDRDB1            LogArchiveMaxProcesses                               10               10              4
   OMDRDB2            LogArchiveMaxProcesses                               10               10              4

Step 4 Set the Properties which had been change and set the correct value

Once we had identified the wrong propery value we need to edit the property of the database

for editing the property of the database

edit database <db_name> SET PROPERTY property_name=value

Here in the below example we are editing the database to have max of 10 processes

Once you changed the property they you will not able to find the values when you query for inconsistent property then no values are displayed

Finally check the configuration again and check now both the configuration are successful