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

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

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-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

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