DGMGRL : ORA-16789: standby redo logs not configured/ORA-16826: apply service state is inconsistent with the DelayMins property

Posted By Sagar Patil

My DGMGRL configuration is returning warnings as below.

DGMGRL> show database primary;
Database – primary
Role:            PRIMARY
Intended State:  TRANSPORT-ON
Instance(s):
primary
Database Warning(s):
ORA-16789: standby redo logs not configured

Database Status:
WARNING

sys@ PRIMARY> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
no rows selected

Let’s go ahead & add Standby Redo Log files at primary

sys@ PRIMARY> select bytes from v$log;
BYTES
———-
52428800
52428800
52428800
3 rows selected.

sys@ PRIMARY> select group#, member from v$logfile;
GROUP#                    MEMBER
——————————————————————————–
1     C:\APP\PATILSA\ORADATA\PRIMARY\REDO01.LOG
2    C:\APP\PATILSA\ORADATA\PRIMARY\REDO02.LOG
3    C:\APP\PATILSA\ORADATA\PRIMARY\REDO03.LOG
3 rows selected.

sys@ PRIMARY> ALTER DATABASE ADD STANDBY LOGFILE GROUP 1 SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 1 SIZE 50M
*
ERROR at line 1:
ORA-01184: logfile group 1 already exists

My primary database had 3 log file groups originally and I created 3 standby redo log groups using the following commands:

sys@ PRIMARY> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 50M;
Database altered.
sys@ PRIMARY> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 SIZE 50M;
Database altered.
sys@ PRIMARY> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 SIZE 50M;
Database altered.

sys@ PRIMARY> select * from v$standby_log;
GROUP# DBID                                        THREAD#  SEQUENCE#      B
YTES  BLOCKSIZE       USED ARC STATUS     FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME LAST_CHANGE# LAST_TIME
———- —————————————- ———- ———- ——
4 UNASSIGNED                                        0          0   52428800        512        512 YES UNASSIGNED             0                      0
0
5 UNASSIGNED                                        0          0   52428800        512        512 YES UNASSIGNED             0                      0
0
6 UNASSIGNED                                        0          0   52428800        512        512 YES UNASSIGNED             0                      0
0
3 rows selected.

DGMGRL> show database primary;
Database – primary
Role:            PRIMARY
Intended State:  TRANSPORT-ON
Instance(s):
primary

Database Status:
SUCCESS

If you try and switchover at this point after adding the Standby Redo logs, you will receive a ORA-16826 from the Data Guard Broker.

DGMGRL> switchover to secondary;
Performing switchover NOW, please wait…
Error: ORA-16775: target standby database in broker operation has potential dataloss
Failed.
Unable to switchover, primary database is still “primary”

DGMGRL> show configuration verbose;
Configuration – sample
Protection Mode: MaxPerformance
Databases:
primary Primary database
secondary Physical standby database
Warning: ORA-16826: apply service state is inconsistent with the DelayMins
property
Fast-Start Failover: DISABLED
Configuration Status:
WARNING

DGMGRL> remove database secondary;
Removed database “secondary” from the configuration

DGMGRL> ADD DATABASE ‘secondary’ AS CONNECT IDENTIFIER IS secondary MAINTAINED AS PHYSICAL;
Database “secondary” added

DGMGRL> show database secondary;
Database – secondary
Role:            PHYSICAL STANDBY
Intended State:  OFFLINE
Transport Lag:   (unknown)
Apply Lag:       (unknown)
Real Time Query: OFF
Instance(s):
secondary
Database Status:
DISABLED

DGMGRL> enable database secondary;
Enabled.

DGMGRL> show configuration;
Configuration – sample
Protection Mode: MaxPerformance
Databases:
primary   – Primary database
secondary – Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

DGMGRL> switchover to secondary;
Performing switchover NOW, please wait…
New primary database “secondary” is opening…
Operation requires shutdown of instance “primary” on database “primary”
Shutting down instance “primary”…
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance “primary” on database “primary”
Starting instance “primary”…
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connectdescriptor Failed.
Warning: You are no longer connected to ORACLE.
Please complete the following steps to finish switchover:
start up and mount instance “primary” of database “primary”

Don’t worry on TNS error ORA-12514: I had to start my PRIMARY database Manually  to fix it.

sys@ SECONDARY> select instance_name, status from v$instance;
INSTANCE_NAME    STATUS
—————- ————
secondary        OPEN
sys@ SECONDARY> select count(*) from tab;
COUNT(*)
———-
4731

sys@ SECONDARY> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     129
Next log sequence to archive   131
Current log sequence           131

sys@ PRIMARY> select instance_name, status from v$instance;

INSTANCE_NAME    STATUS
—————- ————
primary          MOUNTED

sys@ PRIMARY> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            C:\app\patilsa\product\11.2.0\dbhome_1\oradata\pr
imary
Oldest online log sequence     131
Next log sequence to archive   0
Current log sequence           133

DGMGRL> connect sys/manager@secondary
Connected.
DGMGRL> show configuration verbose;

Configuration – sample

Protection Mode: MaxPerformance
Databases:
secondary Primary database
primaryPhysical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS

DGMGRL> show database ‘primary’ StatusReport;
STATUS REPORT
INSTANCE_NAME   SEVERITY ERROR_TEXT

DGMGRL> show database ‘secondary’ StatusReport;
STATUS REPORT
INSTANCE_NAME   SEVERITY ERROR_TEXT

There are no errors so everything is fine now.

Leave a Reply

You must be logged in to post a comment.

Top of Page

Top menu