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)
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
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
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
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
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string auto
SQL> alter system set standby_file_management=MANUAL;
System altered.
System altered.
SQL> show parameter standby_file_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_file_management string MANUAL
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 databaseGROUP# 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
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:
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> 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
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:
Configuration details cannot be determined by DGMGRL After starting the db broker db_broker_start=true then you can see the 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
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.
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
No comments:
Post a Comment