Physical Standby Swithover_status as UNRESOLVABLE GAP

On Data Guard site, I can see that Archive logs are copying but not applying to Physical Standby Database. When I query I see that SWITCHOVER_STATUS showing as “UNRESOLVABLE GAP”

SQL> select OPEN_MODE,LOG_MODE,DATABASE_ROLE, switchover_status from v$database;

OPEN_MODE LOG_MODE DATABASE_ROLE SWITCHOVER_STATUS
-------------------- -------------------- ------------------------------ ---------------------------------------------
MOUNTED ARCHIVELOG PHYSICAL STANDBY UNRESOLVABLE GAP

I didn’t see any errors when we query v$archive_Dest_status;
SQL> select DEST_NAME, ERROR from v$archive_Dest_status;

But when we query V$ARCHIVE_GAP we can see archive log Gap

SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;
THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
1 47402 47409

I don’t see missing archive logs in the archive log destination, but when I query v$managed_standby I can see that MRP0 processes waiting for archive log sequence 47402

SQL> select PROCESS,STATUS,THREAD#,SEQUENCE# from v$managed_standby where PROCESS='MRP0';
PROCESS STATUS THREAD# SEQUENCE#
------------------------------------ ------------------------------------------------ ---------- ----------
MRP0 WAIT_FOR_GAP 1 47402

If the archive logs on the Physical Standby site removed by mistake then you can restore using RMAN
RMAN> restore archivelog from logseq 47402 until logseq 47409;

If you want to restore archive logs into different destination other than default use below command.
RMAN> set archivelog destination to '/tmp/archive_restore';

In case, if you need to restore specific archive log then use below command
RMAN> restore archivelog from logseq=47402;

Once the archive logs are restored, it will apply to standby site. In case if you have restored to non-default destination then you need to copy the archive logs into default destination.

If the archive logs are not applying on Physical Standby site then shut down and open the Physical Standby in recovery mode again. In case if the archive logs are missing and cannot able to restore from backup then you might get below error message. 

SQL> STARTUP NOMOUNT;
ORACLE instance started.
Total System Global Area 2.0737E+10 bytes
Fixed Size 9923356 bytes
Variable Size 8680473632 bytes
Database Buffers 2040487392 bytes
Redo Buffers 6859032 bytes
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active

Note that with missing archive logs you cannot able to recover the database.

Thanks & Regards

http://oracleracexpert.com, Oracle ACE

Webinar: Oracle Data Guard New Features in Oracle 18c & 19c

This Webinar helps you to understand the benefits of Oracle Data Guard, Standby Database types, protection modes and new features in Oracle 18c and 19c.

Date and time: May 5th 2022 8:00am-9:00am
Pacific Daylight Time (San Francisco, GMT-07:00)

This Webinar covers following Topics.

• Introduction to Oracle Data Guard
• Oracle Data Guard Benefits
• Standby Database types
• Data Guard Protection Modes
• What's new in Oracle 18c
• What's new in Oracle 19c
• References
• Contact Info
• Q&A

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

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

For presentation link Click here

Thanks & Regards,
http://www.oracleracexpert.com

DML operations on Active Data Guard standby in Oracle 19c

The DML operations is an Active Data Guard only feature enables DML operations on the standby database to be redirected to the primary to accommodate infrequent writes by reporting applications running against Active Data Guard standby database.

This DML feature introduced in Oracle 19c, this includes DML statements that are part of PL/SQL blocks.

The DML on the standby database will have below steps

• The user executes DML against open standby database.
• DML will be automatically redirects to the primary database.
• The DML will be applied on the primary Datable
• The redo info related change is streamed back to the standby database.
• The application change based redo info completes the DML and data is available for the client.

There are two methods to configure DML Redirection with the help of ADG_REDIRECT_DML parameter.

1. The DML Redirect can be configured at system level, this will apply to all sessions connection to the standby database

Run below command for SYSTEM LEVEL DML Redirection
SQL> ALTER SYSTEM SET ADG_REDIRECT_DML=TRUE SCOPE=BOTH;

2. You can overwrite the system level parameter with alert session command to enable the DML redirect for the current session only.

Run below command to enable SESSION LEVEL DML Redirection 

SQL>ALTER SESSION ENABLE ADG_REDIRECT_DML;

Thanks & Regards

http://oracleracexpert.com

 

Oracle Data Guard New Features in Oracle 18c & 19c

This Webinar helps you to understand the benefits of Oracle Data Guard, Standby Database types, protection modes and new features in Oracle 18c and 19c.

Date and time: Nov 17th 2020 8:00am-9:00am
Pacific Daylight Time (San Francisco, GMT-07:00)

This Webinar covers following Topics.

• Introduction to Oracle Data Guard
• Oracle Data Guard Benefits
• Standby Database types
• Data Guard Protection Modes
• What's new in Oracle 18c
• What's new in Oracle 19c
• References
• Contact Info
• Q&A

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

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

For presentation link Click here

Thanks & Regards,
http://www.oracleracexpert.com

When using Data Guard broker Error: ORA-16627: operation disallowed since no member would remain to support protection mode

You will encounter this error when PROTECTION mode is not consistent betweeen PRIMARY and STANDBY. Make sure you are using DGMGRL to update the configurationn.

DGMGRL> show configuration;
Configuration - oradb_dg_fsfconf
Protection Mode: MaxPerformance
Members:
oradb - Primary database
oradb_stdby - Physical standby database

Fast-Start Failover: Disabled

Configuration Status:
SUCCESS (status updated 13 seconds ago)

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;

Error: ORA-16627: operation disallowed since no member would remain to support protection mode Failed.

=============

Make sure you verify the entire configuration using verbose.

DGMGRL> show configuration verbose;
Configuration - oradb_dg_fsfconf
Protection Mode: MaxPerformance
Members:
oradb - Primary database
oradb_stdby - Physical standby database

Properties:
FastStartFailoverThreshold = '30'
OperationTimeout = '30'
TraceLevel = 'USER'
FastStartFailoverLagLimit = '30'
CommunicationTimeout = '180'
ObserverReconnect = '0'
FastStartFailoverAutoReinstate = 'TRUE'
FastStartFailoverPmyShutdown = 'TRUE'
BystandersFollowRoleChange = 'ALL'
bserverOverride = 'FALSE'
ExternalDestination1 = ''
ExternalDestination2 = ''
PrimaryLostWriteAction = 'CONTINUE'
ConfigurationWideServiceName = 'ORADB_CFG'

Fast-Start Failover: Disabled

Configuration Status:
SUCCESS

DGMGRL> show database verbose ORADB;
Database - oradb
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
ORADB

Properties:
DGConnectIdentifier = 'oradb'
ObserverConnectIdentifier = ''
FastStartFailoverTarget = ''
PreferredObserverHosts = ''
LogShipping = 'ON'
RedoRoutes = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyLagThreshold = '30'
TransportLagThreshold = '30'
TransportDisconnectedThreshold = '30'
ApplyParallel = 'AUTO'
ApplyInstances = '0'
StandbyFileManagement = ''
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '0'
LogArchiveMinSucceedDest = '0'
DataGuardSyncLatency = '0'
LogArchiveTrace = '0'
LogArchiveFormat = ''
DbFileNameConvert = ''
LogFileNameConvert = ''
ArchiveLocation = ''
AlternateLocation = ''
StandbyArchiveLocation = ''
StandbyAlternateLocation = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
LogXptStatus = '(monitor)'
SendQEntries = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'scllqa0078'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=seroradb-db)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORADB_DGMGRL)(INSTANCE_NAME=ORADB)(SERVER=DEDICATED)))'
TopWaitEvents = '(monitor)'
SidName = '(monitor)'

Log file locations:
Alert log : /home/oracle/diag/rdbms/oradb/ORADB/trace/alert_ORADB.log
Data Guard Broker log : /home/oracle/diag/rdbms/oradb/ORADB/trace/drcORADB.log

Database Status:
SUCCESS

DGMGRL> show database verbose ORADB_STDBY;

Database - oradb_stdby
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Average Apply Rate: 26.00 KByte/s
Active Apply Rate: 938.00 KByte/s
Maximum Apply Rate: 940.00 KByte/s
Real Time Query: OFF
Instance(s):
ORADB

Properties:
DGConnectIdentifier = 'oradb_stdby'
ObserverConnectIdentifier = ''
FastStartFailoverTarget = ''
PreferredObserverHosts = ''
LogShipping = 'ON'
RedoRoutes = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyLagThreshold = '30'
TransportLagThreshold = '30'
TransportDisconnectedThreshold = '30'
ApplyParallel = 'AUTO'
ApplyInstances = '0'
StandbyFileManagement = ''
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '0'
LogArchiveMinSucceedDest = '0'
DataGuardSyncLatency = '0'
LogArchiveTrace = '0'
LogArchiveFormat = ''
DbFileNameConvert = ''
LogFileNameConvert = ''
ArchiveLocation = ''
AlternateLocation = ''
StandbyArchiveLocation = ''
StandbyAlternateLocation = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
LogXptStatus = '(monitor)'
SendQEntries = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'scllqa0120'
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oraoradb-dg)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORADB_STDBY_DGMGRL)(INSTANCE_NAME=ORADB)(SERVER=DEDICATED)))'
TopWaitEvents = '(monitor)'
SidName = '(monitor)'

Log file locations:
Alert log : /home/oracle/diag/rdbms/oradb_stdby/ORADB/trace/alert_ORADB.log
Data Guard Broker log : /home/oracle/diag/rdbms/oradb_stdby/ORADB/trace/drcORADB.log

Database Status:
SUCCESS
Make sure you update the LogXptMode and set the protection mode.

DGMGRL> EDIT DATABASE ORADB SET PROPERTY 'LogXptMode'='SYNC';
Property "LogXptMode" updated
DGMGRL> EDIT DATABASE ORADB_STDBY SET PROPERTY 'LogXptMode'='SYNC';
Property "LogXptMode" updated

DGMGRL> show configuration;
Configuration - oradb_dg_fsfconf

Protection Mode: MaxPerformance
Members:
oradb - Primary database
oradb_stdby - Physical standby database

Fast-Start Failover: Disabled

Configuration Status:
SUCCESS (status updated 32 seconds ago)

DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;
Succeeded.

DGMGRL> show configuration;

Configuration - oradb_dg_fsfconf
Protection Mode: MaxAvailability
Members:
oradb - Primary database
oradb_stdby - Physical standby database

Fast-Start Failover: Disabled

Configuration Status:
SUCCESS (status updated 57 seconds ago) Regards,

Satishbabu Gunukula, Oracle ACE

http://www.oracleracexpert.com

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

RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process

 The warning is clear that archive log not deleted and the below are the possible reasons. This post will help you how to manage the archive logs on standby.

RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process

1. The archive log destination on Standby is full
2. Standby destination not accepting any logs due to issues
3. RMAN archive log configuration is set to none/default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

First run below command to identify the archive log gap

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM (SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH, (SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD#

Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
1 185685 185669 16

If there is an archive gap then the issue is either standby archive destination is full or standby destination has some issues and not accepting any more logs, based up on the error take appropriate action. As long as you archive logs backed upon on Primary you can proceed deleting the archive logs on standby

In order to manage the archive logs you have 2 options

1. Create a shell script to check applied logs on standby and delete rest on periodic basis
2. Configure FRA for archive logs and set the RMAN archive log deletion policy as per below

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=800GBG;
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/u01/oracle/FRA';

In case if you are using ASM specify disk group
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='+FRA_DISK';

You need to set LOG_ARCHIVE_DEST_1 parameter DB_RECOVERY_FILE_DEST so that archived logfiles will be created at Flash recovery area.
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST';

Set the RMAN archived log deletion policy as follows.
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;

With above setting the applied archived logs will be deleted automatically when there is a space constraint in flash recovery area.

It’s different for the database where you do the backup, because you want to be sure that the backup is done before an archive log is deleted:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY BACKED UP 1 TIMES TO DISK;

Regards

Satishababu Gunukula, Oracle ACE

http://www.oracleracexpert.com

Why Oracle DataGuard? New Features in Oracle 18c,19c

This Webinar helps you to understand the benefits of Oracle Data Guard, available methods, protection modes and new features in Oracle 18c and 19c.

Date and time: Jan 3th 2020 8:00am-9:00am
Pacific Daylight Time (San Francisco, GMT-07:00)

This Webinar covers following Topics.

• Introduction to Oracle Data Guard
• Oracle Data Guard Benefits
• Standby Database types
• Data Guard Protection Modes
• What's new in Oracle 18c
• What's new in Oracle 19c
• References
• Contact Info
• Q&A

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

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

For presentation link Click here

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

Data Guard Broker Support for DBMS_ROLLING Upgrades

Data guard broker can remain on during a DBMS_ROLLING upgrade and no need to disable it. In oracle 19c there are many enhancements for Data Guard Broker Support for DBMS_ROLLING Upgrades

• Data Guard broker support is enabled by using the DBMS_ROLLING.BUILD_PLAN procedure
• Before starting a DBMS_ROLLING upgrade the fast-start failover feature must be disabled.
• Role changes are permissible during when rolling upgrade is in progress.
• During rolling upgrade any attempt to enable fast-start failover is rejected.
• Broker support is enabled by default during execution of the DBMS_ROLLING.BUILD_PLAN procedure
• The broker prevents a role change to a standby which is not protecting the current primary. The role changes to the Trailing Group Standby are allowed before the switchover phase. After the switchover phase, role changes are only allowed to the Leading Group Standbys.
• The broker will notifies Global Data Services and Oracle Clusterware as appropriate during the rolling upgrade.
• If the upgrade target is an Oracle RAC database, then the broker automatically reduces the target standby to one instance during the start of the upgrade process and allows the upgrade to proceed. Without the broker, the start of the upgrade is rejected if target has multiple instances running.
• The switchover step during a rolling upgrade should be performed using the DBMS_ROLLING.SWITCHOVER procedure.
• The status of a rolling upgrade being done using the PL/SQL package DBMS_ROLLING and the information is displayed in the broker commands SHOW CONFIGURATION and SHOW DATABASE output.

Please see the output of SHOW CONFIGURATION, where you can see configuration status as ROLLING DATABASE MAINTENANCE IN PROGRESS

Configuration - DRSystem
Protection Mode: MaxPerformance
Members:
North_Sales - Primary database
South_Sales - Transient logical standby database
Fast-Start Failover: DISABLED
Configuration Status:
ROLLING DATABASE MAINTENANCE IN PROGRESS

Please see the output of SHOW Database , where you can see Database status as WARNING.

Database – DRNode1
Role: Physical standby database
Intended State: APPLY-ON
Transport Lag: ***
Apply Lag: ***
Average Apply Rate: ***
Real Time Query: OFF
Instance(s):
South
Database Warning(s):
ORA-16866: database converted to transient logical standby database for rolling
database maintenance
Database Status:
WARNING

Three are specific Requirements DBMS_ROLLING Upgrades on a CDB

• To use DBMS_ROLLING the database compatibility should be set to 12.2 or higher

• Different character sets pluggable databases in a CDB supported for upgrade using DBMS_ROLLING

• The TNS services referenced in the LOG_ARCHIVE_DEST_n parameters must be services that resolve to the root container of the destination database.

• The process assisting DBMS_ROLLING can only execute from the root container.

• While a DBMS_ROLLING upgrade is in progress, if any DDL is executed to start the install, upgrade, or patching of an container then you will get an error.

• When user attempt to start a DBMS_ROLLING upgrade when upgrade to an application container is in progress user will get an error. Before calling DBMS_ROLLING.SWITCHOVER all container databases on the transient logical standby must be plugged in and opened, this helps eliminating logical standby apply engine halt because it cannot apply to a given PDB.

Regards

Satishbabu Gunukula, Oracle ACE

http://www.oracleracexpert.com

Webinar: Data Guard Physical Standby Setup in Oracle?

This Webinar helps you to understand the benefits of Oracle Data Guard, available methods and how to setup and common issues.

Date and time: Feb 28th 2019 8:00am-9:00am
Pacific Daylight Time (San Francisco, GMT-07:00)

This Webinar covers following Topics.
· Oracle Data Guard and its Benefits
· Overview of different Data Guard Options
· Difference between RAC vs DG
· Prerequisites
· DataGuard Demonstration
· Common Issues
· Post verification Steps
· References
· Contact Info
· Q&A

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

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

For presentation link Click here

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

Physical standby redo log must be renamed

When creating standby database using database duplication it completed but received below standby redo error. This error will not stop the replication but user don’t want to see these ORA- errors.

Finished recover at 2019-02-12:15:02:32
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 1 thread 1: '/oracle/redo1/ORCL/ ORCL _1A.rdo'

RMAN-05535: warning: All redo log files were not defined properly.
ORACLE error from auxiliary database: ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 2 thread 1: '/oracle/redo1/ORCL/ORCL _2A.rdo'

Though this didn't stop the data guard replication, the customer wanted to get-rid the message. Starting with v10g, this was an expected behavior to improve the switchover and failover. With v10g, when MRP is started, it will attempt to clear the online log files.

If PRIMARY and STANDBY has the same directory structure then you will see these errors. This is an expected behavior when you don’t set log_file_name_convert parameter. You can get rid-off the error message just setup dummy value

SQL> ALTER SYSTEM SET log_file_name_convert='dummy','dummy';

You should not see ORA messages any more in the alert.log file.

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

ORA-16664 with ORA-01031 password file issue

Data Guard Broker switchover fails with an ORA-16664 error

DGMGRL> show configuration;
Configuration – ORCL_DG
Protection Mode: MaxPerformance
Databases:
orcl - Primary database
orcl_stdby - Physical standby database
Error: ORA-16664: unable to receive the result from a database

Fast-Start Failover: DISABLED
Configuration Status:
ERROR

Errors from Primary DRC log 2012-01-02 13:46:23.675 NSV2: database actually reached ORCL_STDBY
2012-01-02 13:46:23.677 NSV2: Failed to send message to site ORCL_STDBY. Error code is ORA-16642.
2012-01-02 13:46:23.678 03000000 973032160 DMON: Database ORCL_STDBYreturned ORA-16642

Errors from Standby DRC log 2012-01-03 13:51:24.902 Connection to database ORCL returns ORA-01031.
2012-01-03 13:51:24.903 Please check database ORCL is using a remote password file,
2012-01-03 13:51:24.904 its remote_login_passwordfile is set to SHARED or EXCLUSIVE,
2012-01-03 13:51:24.905 and the SYS password is the same as this database.
2012-01-03 13:51:24.905 NSV0: Failed to connect to remote database ORCL. Error is ORA-01031
2012-01-03 13:51:24.906 NSV0: Failed to send message to site ORCL. Error code is ORA-01031.

Solution : The password file mismatch at primary and standby cuased the issue. Make sure the same password file copied to all nodes

Also verify the connect identifier for for standby database is correct and you are able to tnsping and connect using sqlplus as sysdba.

Refer : Troubleshooting ORA-16191 and ORA-1017/ORA-1031 in Data Guard Log Transport Services or Data Guard Broker (Doc ID 1368170.1)

Regards,

Satishbabu Gunukula, Oracle ACE

http://www.oracleracepxert.com

ORA-16664 error when using ASM filesystem

We have received ORA-16664: unable to receive the result from a database error when enabling the DataGuard broker config

DGMGRL> show configuration verbose;

Configuration - ORCL_DG

Protection Mode: MaxAvailability

Databases:

orcl - Primary database

orlc_stdby - Physical standby database

Error: ORA-16664: unable to receive the result from a database

1. In one of the senario the problem was in the Primary's REDO_TRANSPORT_USER init parameter

The value for this parameter had weird characters and removing helped.

SQL> show parameter redo_transport

NAME TYPE                               VALUE

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

redo_transport_user string          ???DG_USER???

2. The database is running on ASM and running on ASM and Data Guard Broker configuration files were created locally on filesystem.

In this case drop the existing broker configuration from primary and standby local filesystem and creating on broker configuration on ASM helped.

Regards

Satishbabu Gunukula

http://www.oracleracexpert.com

ORA-16664: unable to receive the result from a database with ORA-12514

We have received below error when trying to enabling Data guard Broker configuration

Error: ORA-16664: unable to receive the result from a database
Dataguard broker log has below errors

Failed to connect to remote database sat. Error is ORA-12514
Failed to send message to member sat. Error code is ORA-12514.

To identify the issue review the confirmation

DGMGRL> SHOW CONFIGURATION;
Configuration - orcl_dgbroker_fsfconf
Protection Mode: MaxAvailability
Members:
orcl - Primary database
orcl_stdby - Physical standby database
Error: ORA-16664: unable to receive the result from a member

Fast-Start Failover: DISABLED

DGMGRL> show database verbose 'ORCL';
Database - ORCL
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
ORCL
Properties:
DGConnectIdentifier = 'ORCL'
ObserverConnectIdentifier = ''
LogXptMode = 'SYNC'
RedoRoutes = ''
DelayMins = '0'
Binding = 'optional'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyLagThreshold = '30'
TransportLagThreshold = '30'
TransportDisconnectedThreshold = '30'
ApplyParallel = 'AUTO'
ApplyInstances = '0'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DataGuardSyncLatency = '0'
DbFileNameConvert = ''
LogFileNameConvert = 'dummy, dummy'
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
PreferredObserverHosts = ''
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=node1.oracleracexpert.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL_DGMGRL)(INSTANCE_NAME=ORCL)(SERVER=DEDICATED)))'
StandbyArchiveLocation = '/oraarch/ORCL'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'arch_ORCL_%r_%s_%t.arc'
TopWaitEvents = '(monitor)'

Log file locations:
Alert log : /oracle/diag/rdbms/ORCL/ORCL/trace/alert_ORCL.log
Data Guard Broker log : /oracle/diag/rdbms/ORCL/ORCL/trace/drcORCL.log

Database Status:
SUCCESS

DGMGRL> show database verbose 'ORCL_stdby';
Database - ORCL_stdby
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: (unknown)
Apply Lag: (unknown)
Average Apply Rate: (unknown)
Active Apply Rate: (unknown)
Maximum Apply Rate: (unknown)
Real Time Query: OFF
Instance(s):
ORCL
Properties:
DGConnectIdentifier = 'ORCL_stdby'
ObserverConnectIdentifier = ''
LogXptMode = 'SYNC'
RedoRoutes = ''
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyLagThreshold = '30'
TransportLagThreshold = '30'
TransportDisconnectedThreshold = '30'
ApplyParallel = 'AUTO'
ApplyInstances = '0'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DataGuardSyncLatency = '0'
DbFileNameConvert = ''
LogFileNameConvert = 'dummy, dummy'
FastStartFailoverTarget = ''
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
PreferredObserverHosts = ''
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)HOST=node2.oracleracexpert.com)(PORT=1521))(CONNECT_DATA=SERVICE_NAME=ORCL_STDBY_DGMGRL)(INSTANCE_NAME=ORCL)SERVER=DEDICATED)))'
StandbyArchiveLocation = '/oraarch/ORCL'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'arch_ORCL_%r_%s_%t.arc'
TopWaitEvents = '(monitor)'

Log file locations:
(Failed to retrieve log file locations.)

Database Status:
DGM-17016: failed to retrieve status for database "ORCL_stdby"
ORA-16664: unable to receive the result from a member

Solution:

1. Makes sure you have Static listener entry has SID_DGMGRL

cat $ORACLE_HOME/network/admin/listener.ora

(SID_DESC =
(GLOBAL_DBNAME = ORCL_DGMGRL) <----------------add _DGMGRL
(ORACLE_HOME = /<oracle_home>)
(SID_NAME = ORCL)
)

2. Same way change the static entry of current primary also for feature role transition. For non-default port set the local listener on the respective database where ORA-12514 was thrown.

SQL>alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<hostIP>)(PORT=port)))';

Refer Below oracle notes for more scenarios
1. Causes and Solutions for DGMGRL ORA-16664 (Data Guard Broker) (Doc ID 2494260.1) 

Regards

Satishbabu Gunukula

http://www.oracleracexpert.com

RMAN-05001: auxiliary file name conflicts with a file used by the target database and RMAN-05501

The Duplicate db for standby failed with below error

RMAN-05501: aborting duplication of target database
RMAN-05001: auxiliary file name /oradata1/ORCL/system01.dbf conflicts with a file used by the target database
The duplicate script will look like below…

RMAN> run {
set until time "to_date('20018/12/08 14:00:00','yyyy/mm/dd HH24:MI:SS')";
allocate auxiliary channel aux1 type 'sbttape';
allocate auxiliary channel aux2 type 'sbttape';
duplicate target database for standby dorecover;
}

If primary and secondary has different directory structure then you will not see this issue. This is happening because the primary and secondary datafile directory structures are same.

To skip this check, use 'nofilenamecheck' clause. This will instructs rman not to check whether target database file names share the same name as auxiliary.

Now the script will look like below and you should not see this error.

RMAN> run {
set until time "to_date('20018/12/08 14:00:00','yyyy/mm/dd HH24:MI:SS')";
allocate auxiliary channel aux1 type 'sbttape';
allocate auxiliary channel aux2 type 'sbttape';
duplicate target database for standby dorecover nofilenamecheck;
}

Thanks

Satishbabu Gunukula, Oracle ACE

http://www.oracleracexpert.com

ORA-16053: DB_UNIQUE_NAME ORCL_STBY is not in the Data Guard Configuration with ORA-02097 error

The ORA-02097 is very generic error you need to check following error message to understand the issue.

ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16053: DB_UNIQUE_NAME ORCL _STBY is not in the Data Guard Configuration
In my case I am trying to update DB_UNIQUE_NAME but it is failed, because the specified DB_UNIQUE_NAME is not in the Data Guard Configuration.

Before you update DB_UNIQUE_NAME parameter make sure LOG_ARCHIVE_CONFIG is enabled and it has valid DB_UNIQUE_NAME.

ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCL,ORCL_STDBY)'

You can find list of valid DB_UNIQUE_NAMEs in V$DATAGUARD_CONFIG view

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

ORA-01111: name for data file is unknown in Standby

Many users encountered the issue UNNAMEDnnn file in Standby Database

MRP0: Background Media Recovery terminated with error 1111
ORA-01111: name for data file 536 is unknown - rename to correct file
ORA-01110: data file 10:'/home/oracle/11.2.0/dbs/UNNAMED00010'
ORA-01157: cannot identify/lock data file 10 - see DBWR trace file

Uses will encounter this error when standby_file_management is set to auto and file path which is added to the primary database does not exist on standby site. In this situation standby database creates a dummy data file such as UNNAMEDnnnn

If you encounter this error follow the steps
Find the files which are needs to be recovered

SQL> select * from v$recover_file where error=' FILE MISSING';
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME
---------- ------- ------- -------------------- ---------- ------------------
10 ONLINE ONLINE FILE MISSING 0

Run below command to Identify the file name on Primary as well as on Standby
SQL>select file#,name from v$datafile where file#=10;

Set the standby_file_management to Manual
SQL> ALTER SYSTEM set standby_file_management=MANUAL;

Create the actual data file from dummy filename
SQL> ALTER DATABASE CREATE DATAFILE 'datafile_path/UNNAMEDnnnn' as '{standby_file_path}/datafile_name.dbf';

Users will also encounter the same error when they add a data file on Primary database and STANDBY_FILE_MANAGEMENT on standby set to MANUAL. When MRP (Recovery process) trying to apply the archive logs, it will create an UNNAMEDnnnn file under $ORACLE_HOME/dbs

Run below command to create the data file manually in Standby

SQL> ALTER DATABASE CREATE DATAFILE ‘/home/oracle/11.2.0/dbs/UNNAMED00010'as ‘/dbs01/ORAC/oradata/prd_data10.dbf’;

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