OPW-00010: Could not create the password file. This resource has a Password File.

Password file creation filed with below error

$ orapwd file='+DATAC1/DEVDB_OCI/PASSWORD/orapwdevdb' dbuniquename='DEVDB_oci'

Enter password for SYS:

OPW-00010: Could not create the password file. This resource has a Password File.

Looks password file details added to srvctl , check srvctl config

$  srvctl config database -d DEVDB_oci

Database unique name: DEVDB_oci

Database name: DEVDB

Oracle home: /u02/app/oracle/product/19.0.0.0/dbhome_2

Oracle user: oracle

Spfile: +DATAC1/DEVDB_OCI/PARAMETERFILE/spfile.339.1079736451

Password file: /u02/app/oracle/product/19.0.0.0/dbhome_2/dbs/orapwdevdb11

Domain: db.devdb.com

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools:

Disk Groups: DATAC1,RECOC1

Mount point paths: /acfs01

Services: DEVDB_obi,DEVDB_xa

Type: RAC

Start concurrency:

Stop concurrency:

OSDBA group: dba

OSOPER group: racoper

Database instances: DEVDB11,DEVDB12

Configured nodes: racnode01,racnode02

CSS critical: no

CPU count: 0

Memory target: 0

Maximum memory: 0

Default network number for database services:

Database is administrator managed

Remove pwfile details from srvctl config:

$ srvctl modify database -d DEVDB_oci -pwfile

Check srvctl config:

$  srvctl config database -d DEVDB_oci

Database unique name: DEVDB_oci

Database name: DEVDB

Oracle home: /u02/app/oracle/product/19.0.0.0/dbhome_2

Oracle user: oracle

Spfile: +DATAC1/DEVDB_OCI/PARAMETERFILE/spfile.339.1079736451

Password file:

Domain: db.devdb.com

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools:

Disk Groups: DATAC1,RECOC1

Mount point paths: /acfs01

Services: DEVDB_obi,DEVDB_xa

Type: RAC

Start concurrency:

Stop concurrency:

OSDBA group: dba

OSOPER group: racoper

Database instances: DEVDB11,DEVDB12

Configured nodes: racnode01,racnode02

CSS critical: no

CPU count: 0

Memory target: 0

Maximum memory: 0

Default network number for database services:

Database is administrator managed

Lets try adding password file:

$ orapwd file='+DATAC1/DEVDB_OCI/PASSWORD/orapwdevdb' dbuniquename='DEVDB_oci'

Enter password for SYS:

Add pwfile to srvctl config again:

$ srvctl modify database -d DEVDB_oci -pwfile  '+DATAC1/DEVDB_OCI/PASSWORD/orapwdevdb'

$ srvctl config database -d DEVDB_oci

Database unique name: DEVDB_oci

Database name: DEVDB

Oracle home: /u02/app/oracle/product/19.0.0.0/dbhome_2

Oracle user: oracle

Spfile: +DATAC1/DEVDB_OCI/PARAMETERFILE/spfile.339.1079736451

Password file: +DATAC1/DEVDB_OCI/PASSWORD/orapwdevdb

Domain: db.devdb.com

Start options: open

Stop options: immediate

Database role: PRIMARY

Management policy: AUTOMATIC

Server pools:

Disk Groups: DATAC1,RECOC1

Mount point paths: /acfs01

Services: DEVDB_obi,DEVDB_xa

Type: RAC

Start concurrency:

Stop concurrency:

OSDBA group: dba

OSOPER group: racoper

Database instances: DEVDB11,DEVDB12

Configured nodes: racnode01,racnode02

CSS critical: no

CPU count: 0

Memory target: 0

Maximum memory: 0

Default network number for database services:

Database is administrator managed

Oracle RAC GC (Global Cache) waits

Oracle RAC GC (Global Cache) waits occur when multiple instances in a Real Application Clusters (RAC) environment contend for access to a shared cache. These waits can impact performance and scalability. Let's discuss common findings related to RAC GC waits and some potential solutions, including relevant commands.
 
Identify top wait events related to GC Buffer Busy waits:
SELECT event, total_waits, time_waited
FROM gv$system_event
WHERE event LIKE 'gc buffer busy%';

Monitor the most accessed objects:
SELECT owner, object_name, object_type, buffers, gets
FROM dba_objects
ORDER BY gets DESC; 

• Implement partitioning, caching mechanisms, or workload distribution strategies to reduce contention.

Unbalanced Global Cache Fusion:
Check the workload distribution across instances:
SELECT inst_id, count(*)
FROM gv$session
GROUP BY inst_id;

SELECT inst_id, name, network_type, bandwidth, latency FROM gv$cluster_interconnects;

• Ensure the interconnect network is functioning properly and has sufficient bandwidth.
• Redistribute workload across instances using connection load balancing or Oracle services.

Inadequate Buffer Cache Size:
Check buffer cache hit ratios:
SELECT name, value
FROM gv$sysstat
WHERE name IN ('db block gets', 'consistent gets', 'physical reads');

• Adjust the buffer cache size using the DB_CACHE_SIZE initialization parameter or Automatic Shared Memory Management (ASMM) / Automatic Memory Management (AMM) features.

High Library Cache Lock:
Identify top SQL statements causing contention:
SELECT sql_text, executions, child_number
FROM gv$sql
WHERE address IN (
  SELECT address
  FROM gv$session
  WHERE event LIKE 'library cache%');

• Optimize SQL statements using SQL plan management, indexing, query rewriting, or SQL profile hints.

Optimizing Physical Standby Recovery with Parallel Media Recovery

What is Parallel Media Recovery?
Parallel Media Recovery  is a mechanism in Oracle that allows multiple parallel processes (or recovery slaves) to recover redo logs concurrently in a physical standby database. This process improves recovery time by leveraging multiple CPU cores on the standby system to read and apply redo logs simultaneously. With the evolution of Oracle versions, the implementation and control of parallel recovery have become more sophisticated, offering better scalability and flexibility.
Basics of Media Recovery?
Media recovery involves applying archived redo logs from primary database to physical standby database to keep it in sync with the primary. In a physical standby, recovery is typically managed by the Managed Recovery Process (MRP). MRP applies redo logs continuously from the archive to maintain data consistency between the primary and the standby.
Without parallel recovery, MRP applies redo logs sequentially, which can be slow, especially in high-transaction environments. This is where Parallel Media Recovery becomes vital.
Parallel media recovery was introduced in  Oracle 10g. In this version, Oracle automatically determines the number of parallel processes based on the number of CPUs available on the system. Oracle 11g brought further improvements, particularly in decoupling parallel recovery from parallel query processes, which were used in previous versions.

Start Parallel Media Recovery:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE PARALLEL 12 DISCONNECT FROM SESSION;
This command instructs Oracle to use 12 parallel recovery slaves for media recovery, and recovery process will disconnect from the session after starting.
Monitor the parallel recovery process through the Oracle alert log. Here's an example output:
Attempt to start background Managed Standby Recovery process (SID)
Mon Feb 23 02:03:24 2021
MRP0: Background Managed Standby Recovery process started (SID)
started logmerger process
Mon Feb 23 02:03:29 2021
Managed Standby Recovery not using Real Time Apply
Mon Feb 23 02:03:50 2021
Parallel Media Recovery started with 12 slaves
Canceling Parallel Recovery:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Switching Between Parallel and Serial Recovery:
If you want to disable parallel recovery and revert to serial recovery, use the command below:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT noparallel;
This command disables parallelism (noparallel) and switches the recovery to a serial mode, where only one process applies redo logs.
Best Practices for Parallel Media Recovery:
While Parallel Media Recovery can significantly speed up the recovery process, it’s essential to use it judiciously. Here are some best practices:
 Allow Oracle to Determine Parallelism: Rather than manually setting the parallel degree, it is generally recommended to let Oracle automatically manage the degree of parallelism based on system resources. This helps avoid overloading the system and ensures optimal recovery performance.
 Leverage Data Guard Broker for Configuration: If you’re using Data Guard Broker, you can configure the ApplyParallel property to control parallel recovery. In Oracle 11.1 and later, we can set the ApplyParallel property to AUTO (enabled) or NO (disabled). In Oracle 11.2 and higher, you can specify the exact number of parallel recovery slaves via the PARALLEL attribute.
    Example:
    ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=STANDBY' PARALLEL 8;
Monitor and Adjust Based on System Performance: Always monitor system performance and adjust parallelism if necessary. For systems with limited CPU resources, setting too many parallel processes could negatively impact overall system performance.
Use Real-Time Apply with Caution: While Real-Time Apply can apply redo logs as they are generated, it might not always be compatible with parallel recovery. Ensure your system requirements are met before enabling this feature.
Optimize Redo Log Archiving: Efficient redo log archiving and transmission are essential to maximize the effectiveness of Parallel Media Recovery. Ensure that the redo logs from the primary are transmitted quickly to the standby database for recovery.
Troubleshooting Parallel Media Recovery:
If Parallel Media Recovery is not functioning as expected, here are a few steps to diagnose the issue:
Check Alert Logs: Always review the alert logs for messages related to recovery, such as issues with log shipping, file access, or insufficient resources.
Monitor the Processes: Use Oracle views like V$RECOVERY_PROGRESS and V$MANAGED_STANDBY to check the status of recovery processes and ensure the parallel slaves are running as expected.
Review Configuration Parameters: Ensure that the parallel_max_servers and related parameters are correctly configured and not restricting parallel recovery.
Test Performance: Run performance tests by changing the parallel degree to see how the system responds. You can adjust parallelism based on the results to achieve optimal recovery speed.

Opatch apply failed with Missing command :fuser

Opatch apply failed with Prerequisite check "CheckSystemCommandAvailable" failed error

/usr/local/312813555> opatch apply

Oracle Interim Patch Installer version 12.2.0.1.21

Copyright (c) 2021, Oracle Corporation.  All rights reserved.

Oracle Home       : /u01/app/oracle/product/19.8.0_64

Central Inventory : /app/oraInventory

   from           : /u01/app/oracle/product/19.8.0_64/oraInst.loc

OPatch version    : 12.2.0.1.21

OUI version       : 12.2.0.7.0

Log file location : /u01/app/oracle/product/19.8.0_64/cfgtoollogs/opatch/opatch2021-01-01_07-48-00AM_1.log

Verifying environment and performing prerequisite checks...

Prerequisite check "CheckSystemCommandAvailable" failed.

The details are:

Missing command :fuser

UtilSession failed: 

Prerequisite check "CheckSystemCommandAvailable" failed.

Log file location: /u01/app/oracle/product/19.8.0_64/cfgtoollogs/opatch/opatch2021-01-01_07-48-00AM_1.log

OPatch failed with error code 73

Opatch log:                                 

[Feb 1, 2021 7:48:10 AM] [INFO]     Execution of 'sh /usr/local/31281355/custom/scripts/init -apply 31281355 ':

[Feb 1, 2021 7:48:10 AM] [INFO]     Return Code = 0

[Feb 1, 2021 7:48:10 AM] [INFO]     Execution of ' Command and arguments of init script is restricted to be viewed in log file ':    Return Code = 0

[Feb 1, 2021 7:48:10 AM] [INFO]     Running prerequisite checks...

[Feb 1, 2021 7:48:13 AM] [INFO]     Space Needed : 5612.323MB

[Feb 1, 2021 7:48:13 AM] [INFO]     Missing command :fuser

[Feb 1, 2021 7:48:13 AM] [INFO]     Prerequisite check "CheckSystemCommandAvailable" failed.

                                    The details are:

                                    Missing command :fuser

[Feb 1, 2021 7:48:13 AM] [SEVERE]   OUI-67073:UtilSession failed: 

                                    Prerequisite check "CheckSystemCommandAvailable" failed.

[Feb 1, 2021 7:48:13 AM] [INFO]     Finishing UtilSession at Mon Mar 01 07:48:13 GMT 2021

[Feb 1, 2021 7:48:13 AM] [INFO]     Log file location: /u01/app/oracle/product/19.8.0_64/cfgtoollogs/opatch/opatch2021-01-01_07-48-00AM_1.log

[Feb 1, 2021 7:48:13 AM] [INFO]     Stack Description: java.lang.RuntimeException: 

                                    Prerequisite check "CheckSystemCommandAvailable" failed.

Solution:                                 

/home/oracle> cd $ORACLE_HOME/bin

[oracle@dbrestore-test] -->poc19c1<--

/u01/app/oracle/product/19.8.0_64/bin> touch fuser

[oracle@dbrestore-test] -->poc19c1<--

/u01/app/oracle/product/19.8.0_64/bin> chmod 755 fuser

[oracle@dbrestore-test] -->poc19c1<--

/u01/app/oracle/product/19.8.0_64/bin> ls -ltr fuser

-rwxr-xr-x. 1 oracle oinstall 0 Mar  1 07:50 fuser