ORA-01665: control file is not a standby control file

Users may see this error when trying to mount standby database after restore or during the switch over

SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE MOUNT STANDBY DATABASE
*
ERROR at line 1:
ORA-01665: control file is not a standby control file

SQL> SELECT database_role FROM v$database;
DATABASE_ROLE
----------------------------------------------------------------
PRIMARY

In current scenario, we see the database role as primary and trying to convert as Physical Standby. You can convert when database not mounted, otherwise you will see below error.

SQL> Alter database convert to physical standby;
Alter database convert to physical standby
*
ERROR at line 1:
ORA-01507: database not mounted

SQL> shutdown immediate
SQL> startup nomount;

SQL> Alter database convert to physical standby;
Database altered.
SQL> SELECT database_role FROM v$database;
DATABASE_ROLE
----------------------------------------------------------------
PHYSICAL STANDBY
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE disconnect from session;
Database altered.

Make sure you start the recovery using below command

sql> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE disconnect from session;

To avoid any user mistakes I would advise to use Data Guard broker

Refer below presentation related to Data Guard.

1. “Why Oracle DataGuard? New Features in Oracle 18c,19c”
https://www.oracleracexpert.com/2019/12/why-oracle-dataguard-new-features-in.html

2. Data Guard Physical Standby Setup in Oracle?
https://www.oracleracexpert.com/2019/02/webinar-data-guard-physical-standby.html

Regards
Satishbabu Gunukula, Oracle ACE
http://oracleracexpert.com

All redo log files were not defined properly and Operation RENAME is not allowed

All redo log files were not defined properly and Operation RENAME is not allowed

We have created the Standby database using RMAN in Oracle 19c and received following error after standby DB restore.

RMAN-05535: warning: All redo log files were not defined properly.
Oracle error from auxiliary database: ORA-01275: Operation RENAME is not allowed if standby file management is automatic.

I have seen this error in the newer Oracle versions only and this warning can be ignored as parameter STANDBY_FILE_MANAGEMENT is set to AUTO

During recovery I also come across below error, this is because I have not allocated any auxiliary channel.

run{
allocate channel PR1 type disk;
allocate channel PR2 type disk;
duplicate target database for standby from active database dorecover nofilenamecheck
spfile
set db_unique_name='ORCLT_STDBY'
set standby_file_management='AUTO'
}

During the upgrade
RMAN-05501: aborting duplication of target database
RMAN-05503: at least one auxiliary channel must be allocated to execute this command

I have added below command in the script and restore worked fine.

allocate auxiliary channel STDBY type disk;

Check presentation on Data Guard Physical Standby Setup in Oracle?

Regards
Satishbabu Gunukula, Oracle ACE
http://oracleracexpert.com

ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set

I have come across issue when adding Standby Database during Data Guard broker config

DGMGRL> create configuration 'ORCLT' as primary database is 'ORCLT' connect identifier is ORCLT;
Configuration "ORCLT" created with primary database "ORCLT"
DGMGRL> add database 'ORCLT_STDBY' as connect identifier is ORCLT_STDBY maintained as physical;
Error: ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set

I have not seen this issue on older versions, after research found that there are few changes from Oracle 12c. We have to unset LOG_ARCHIVE_DEST_2 parameter as DGMGRL will take care of updating this parameter.

I have followed below steps

1. Disable and Remove Data Guard configuration

DGMGRL> disable configuration;
DGMGRL> remove configuration;

2. Disable/Enable Data Guard broker on both Primary/standby

SQL> alter system set dg_broker_start=false scope=both;
SQL> alter system set dg_broker_start=true scope=both;

3. Disable log_archive_dest_2 on both Primary/standby
SQL> alter system set log_archive_dest_2'' scope=both;

4. Configure Data Guard Broker

DGMGRL> create configuration ‘orclt_dg’ as primary database is 'ORCLT' connect identifier is ORCLT;
Configuration "ORCLT" created with primary database "ORCLT"
DGMGRL> add database 'ORCLT_STDBY' as connect identifier is ORCLT_STDBY maintained as physical;
DGMGRL> Database 'ORCLT_STDBY' added
DGMGRL> enable configuration;
Enabled.

DGMGRL> show configuration;
Configuration – orclt_dg
Protection Mode: MaxPerformance
Members:
orclt - Primary database
orclt_stdby - Physical standby database

Fast-Start Failover: Disabled

Configuration Status:

SUCCESS (status updated 46 seconds ago)

Regards,

Satishbabu Gunukula, Oracle ACE

http://oracleracexpert.com

Webinar: What’s New in Oracle Database 19c?

Oracle Database 19c has many exciting new features and in order to take advantage of these features you need to upgrade the databases from older versions to Oracle 19c

Join the Webinar to learn New Features of Oracle Database 19c

Date and time: Mar 31st 2018 8:00am-9:00am
Pacific Daylight Time (San Francisco, GMT-07:00)

To register for this Webinar, please send an email to SatishbabuGunukula@gmail.com.

Note that registrations are limited, first come and first serve basis only. You will receive an email confirmation with meeting session link.

For Presentation, link "Click here"

Thanks,
Satishbabu Gunukula, Oracle ACE
http://oracleracexpert.com

ORA-16416: No viable Physical Standby switchover targets available

You will see this error when you are trying to switchover from Primary to Standby, you will see the error when log_archive_dest_n not ready at standby or there is some issue with REDO log.

Also note that you will see this error if f there is an archive gap, that prevents switchover because of which the operation fails

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY
*
ERROR at line 1:
ORA-16416: No viable Physical Standby switchover targets available

Error from standby alert.log file

.... (PID:24072): No viable Physical Standby switchover targets available
ORA-16416 signalled during: ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY... 2020-02-27T19:36:42.425842-08:00
rfs (PID:36824): Database mount ID mismatch [0x5a5dee6f:0x5a5d7a7b] (1516105327:1516075643)
rfs (PID:36824): Not using real application clusters
2020-02-27T19:38:17.144104-08:00
.... (PID:24072): Using STANDBY_ARCHIVE_DEST parameter default value as /oraarch/ORCLP krsd.c:18141]
2020-02-27T19:38:17.154204-08:00
ALTER SYSTEM SET log_archive_dest_2='SERVICE=ORCLP AFFIRM SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLP' SCOPE=BOTH;
2020-02-27T19:38:21.552799-08:00
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY
2020-02-27T19:38:21.552940-08:00
TMI: adbdrv kcv_primary_switchover BEGIN 2020-02-27 19:38:21.552864
2020-02-27T19:38:21.553038-08:00
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY [Process Id: 24072] (ORCLP)
.... (PID:24072): Waiting for target standby to receive all redo
.... (PID:24072): Waiting for target standby to receive all redo
2020-02-27T19:38:21.553572-08:00
.... (PID:24072): Waiting for all non-current ORLs to be archived
2020-02-27T19:38:21.553661-08:00
.... (PID:24072): All non-current ORLs have been archived
2020-02-27T19:38:21.553741-08:00
.... (PID:24072): Waiting for all FAL entries to be archived
2020-02-27T19:38:21.553820-08:00
.... (PID:24072): All FAL entries have been archived
2020-02-27T19:38:21.553924-08:00
.... (PID:24072): Waiting for potential Physical Standby switchover target to become synchronized
2020-02-27T19:38:21.598598-08:00
Errors in file /home/oracle/diag/rdbms/ORCLP_stdby/ORCLP/trace/ORCLP_tt00_16934.trc:
ORA-16009: invalid redo transport destination
2020-02-27T19:38:21.598892-08:00
TT00 (PID:16934): krsg_check_connection: Error 16009 connecting to standby 'ORCLP'
2020-02-27T19:38:22.554301-08:00

Run below command to find out the Archive Destination status errors..

SQL> select DEST_ID, STATUS, DESTINATION from V$ARCHIVE_DEST_STATUS;
SQL> select DEST_ID,DEST_NAME,STATUS,TARGET,DESTINATION,ERROR,ALTERNATE from v$archive_dest

SQL> select GROUP#,THREAD#,BYTES/1024/1024/1024,ARCHIVED,STATUS from v$standby_log;

Also run below command to identify the gap

SQL> SELECT THREAD#, SEQUENCE# FROM V$THREAD;
SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

After running the above commands analyzing the logs I see that the error due to invalid REDO LOG destination. I have created the directories as same as Primary on Standby site.

I also come across issue with LOG_ARCHIVE_DEST_2, after fixing the parameter it worked fine. Also, make sure LOG_ARCHIVE_DEST_STATE_2 enabled.

ORA-16474: target_db_name not found in the LOG_ARCHIVE_DEST_n parameter

I would strongly advise to use Data Guard Broker, it minimizes the errors, and switchover is very easy.

Refer below Data Guard presentation
Webinar: Data Guard Physical Standby Setup in Oracle?

Regards

Satishbabu Gunukula, Oracle ACE
http://oracleracexpert.com