How to tell/find if the instance is Primary/Standby?

By querying v$database one can tell if the host is primary or standby

For primary
testdb> select database_role from v$database;

DATABASE_ROLE
—————-
PRIMARY

For Standby – Note you may need to connect to as sys as sysdba if the instance is in mount state
testd> select database_role from v$database;

DATABASE_ROLE
—————-
PHYSICAL STANDBY

OR
On Primary database the value of controlfile_type in V$database is “CURRENT” and standby is “STANDBY”

Value on Primary:
SQL> SELECT controlfile_type FROM V$database;

CONTROL
——–
CURRENT

Value on Standby:
SQL> SELECT controlfile_type FROM V$database;

CONTROL
——–
STANDBY

Advertisements

Dataguard ORA-16817

If you have an issue where the server where standby resides had to be restarted and when you try to enable Fast Start Failover, it wouldn’t enable due to ORA-16608 and when checking the primary database in dataguard it would return ORA-16817 “unsynchronized fast-start failover configuration”

Example:
$ dgmgrl /
DGMGRL> enable configuration
Enabled.
DGMGRL> show configuration verbose
Configuration
Name: FSF
Enabled: YES
Protection Mode: MaxAvailability
Databases:
testdb_a – Primary database
testdb_b – Physical standby database

Fast-Start Failover: DISABLED
Current status for “FSF”:
Warning: ORA-16608: one or more databases have warnings

So upon checking both the databases, the primary would report the following warning ORA-16817 and Standby was fine.
DGMGRL> SHOW DATABASE VERBOSE testdb_a
..
Current status for “testdb_a”:
Warning: ORA-16817: unsynchronized fast-start failover configuration.

Also then checking the primary it returned the following:
On Primary:
SQL> select PROTECTION_LEVEL, FS_FAILOVER_STATUS from v$database;
PROTECTION_LEVEL FS_FAILOVER_STATUS
——————– ———————-
RESYNCHRONIZATION UNSYNCHRONIZED

And checking V$archived_log

select * from (
select sequence#,dest_id,first_time,archived,applied,completion_time, name
from v$archived_log
where applied = ‘YES’
order by first_time desc
);

SEQUENCE# DEST_ID FIRST_TIM ARC APPLIED COMPLETIO NAME
———- ———- ——— — ——— ——— ———————-

15321 2 03-MAR-12 YES NO 03-MAR-12 testdb_b.amazon
15321 1 03-MAR-12 YES NO 03-MAR-12 /arch/testdb/redolog-15321-1.arc
15320 1 03-MAR-12 YES NO 03-MAR-12 /arch/testdb/redolog-15320-1.arc
15319 2 03-MAR-12 YES NO 03-MAR-12 testdb_b.amazon
15319 1 03-MAR-12 YES NO 03-MAR-12 /arch/testdb/redolog-15319-1.arc
15318 2 03-MAR-12 YES YES 03-MAR-12 avm1na_b.amazon
15318 1 03-MAR-12 YES NO 03-MAR-12 /arch/testdb/redolog-15318-1.arc
..

If you see above there is a gap for redo log sequence# 15320 and due to which primary is in the Resynchronization state and when one does a logfile switch on primary, it transmits the redo log to standby and standby applies the archive log (by monitoring the alert log of standby).

So fix this issue, resetting the value of log_archive_max_processes would fix the synchronize issue i.e. update FS_FAILOVER_STATUS to SYNCHRONIZED and update the protection_level in v$database.

SQL> show parameters log_archive_max_processes;
log_archive_max_processes integer 4

SQL> alter system set log_archive_max_processes=1;
System altered.

SQL> alter system set log_archive_max_processes=4;
System altered.