ORA-00230: operation disallowed: snapshot control file enqueue unavailable

User might encounter below error, in case if RMAN backup is already running and user might have triggered another backup. The second backup must wait control file enqueuer to create a snapshot of the controlfile.

RMAN-03002: failure of backup command at 04/10/2020 16:40:33
ORA-00230: operation disallowed: snapshot control file enqueue unavailable
You can run below command to find out the backup that is running and causing the wait

SELECT S.SID, USERNAME , PROGRAM, MODULE,
ACTION, LOGON_TIME FROM V$SESSION s, V$ENQUEUE_LOCK l
WHERE l.SID = s.SID AND l.TYPE = 'CF' AND l.ID1 = 0 AND l.ID2 = 2;

To avoid this error make sure that backups are not overlapping each other.

Regards,
Satishbabu Gunukula, Oracle ACE
http://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

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

How to manage Oracle RAC?

Oracle Real Application cluster is a group of independent servers that collaborate as singe system and provides single system image for management and configuration. All tools and utilities provided by oracle to manage system can be used from one system. The clusterware and Database can be installed, configured and managed from single location.

Rolling PatchOracle supports rolling patches, but this requires that each node has a separate Oracle Home. The patches are applied one node at a time while other nodes are operational. Oracle cluster patches are applied in rolling fashion, so no downtime required.

Refer https://docs.oracle.com/cd/B16240_01/doc/em.102/e15294/rac.htm’

Rolling UpgradeOracle supports rolling upgrades and individual patches will be rolling upgradable. If any patch modifies the common structures shared between the instances or database will not be quailed for rolling upgrade. Oracle will certify the patches eligible for rolling upgrade and not all the patches are eligible, rolling upgrades are not approved for patch sets. From oracle 10g release1 supports database software upgrade in rolling fashion without any down time by using Data Guard SQL Apply.

Refer: https://docs.oracle.com/cd/B28359_01/install.111/b28263/procstop.htm#CWLIN341

Enterprise Grid ControlOracle Enterprise Grid Control is a GUI management tool provided by oracle to eliminate tedious work and manage your cluster environment more efficiently. This tool provides the centralized management of cluster databases, using this tool you can view system status, view alerts and set thresholds for alert generation, backup and recovery, monitor cluster wait events, monitor performance metrics across all database instances, and you can able to perform all maintenance activities.

Refer : https://docs.oracle.com/cd/E11857_01/em.111/e11982/overview.htm

Scalability

Oracle Real Application Cluster provides scalability for all your enterprise business applications. Oracle provides a wide array of tools and techniques for scaling, and you can use these tools to ensure seamless growth while minimizing the investment in hardware resources. You can allow RAC database to grow seamlessly from a small system to a big multinational enterprise applications.

Refer : https://docs.oracle.com/cd/B10501_01/rac.920/a96597/psscadtl.htm

Oracle RAC architecture automatically accommodates rapidly changing business requirements, adding a server to the cluster does not require an outage and as soon as the new Instance added the application can take advantage of extra resources. You need to make sure that all servers in the cluster must run the same OS and same Oracle version, but they do not have to be the exactly same capacity. Oracle automatically balances the user load among the multiple nodes in the cluster.

High Availability

Oracle Real application cluster provides the following important features for a High available data management.

Reliability – In Oracle real application cluster if an instance fails, the remaining instances in the server remain active and open for users. Oracle cluster monitors all oracle processes and immediately restarts any failed component.

Recoverability - If an instance fails in Oracle RAC database, it is recognized by other instances in the server pool and recovery will start automatically. Fast connection Failover (FCF) and Fast application notification (FAN) makes it easy for applications to mask component failures from the user.

Continuous Operations – Oracle Real application cluster provides continuous service for both unplanned and planned outages. If a server or instances fails, the database remains open and the application/users are able to access data from other surviving instances. Most of the database maintenance operations can be performed without downtime and many other maintenance tasks can be done in a rolling fashion so application downtime is minimized.

Error Detection - Oracle Cluster automatically monitors oracle databases and other oracle process (ASM instances, Listener, ONS…etc) and provides fast detection of problems in the RAC environment. It also automatically recovers from failures often before users noticed that a failure has occurred.

Refer: https://docs.oracle.com/cd/B28359_01/server.111/b28281/architectures.htm#g1012375

Benefits of using Oracle RAC:-

Scalability - Oracle Real Application Cluster provides scalability for all your enterprise business applications. Oracle provides a wide array of tools and techniques for scaling, and you can use these tools to ensure seamless growth while minimizing the investment in hardware resources. You can allow RAC database to grow seamlessly from a small system to a big multinational enterprise applications.

High Availability- Oracle Real application cluster provides Reliability, Recoverability, Continuous Operations, Error Detection features for a High available data management. If an instance fails in Oracle RAC database, the Cluster detects the problems immediately and recovery will start automatically. The remaining instances in the server remain active and open for uses.

Maintenance - In Oracle RAC, most of the database maintenance operations can be performed without downtime and many other maintenance tasks can be done in a rolling fashion (Rolling Patch, Rolling Upgrade) so application downtime is minimized

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