27 February, 2024

Installing and Running Oracle AHF ORACHK on a 12.2 DB Server

 The Oracle Autonomous Health Framework is described in Support Document "Autonomous Health Framework (AHF) - Including TFA and ORAchk/EXAchk (Doc ID 2550798.1)"

In a recent video I have demonstrated running 24.1 orachk (with "-b" for "Best Practices Check) against a 21.3 RAC Cluster.

Here I demonstrate the installation and execution against a 12.2 non-RAC database.

When you download the 24.1 release of AHF (AHF-LINUX_v24.1.0.zip, approximately 410MB), you have to unzip it and then run ahf_setup.  It is preferable to use the default location /opt/oracle.ahf  (and precreate a "data" subfolder if it doesn't exist).

If your first attempt at installation returns an error :

[ERROR] : AHF-00074: Required Perl Modules not found :  Data::Dumper

you can check the perl version and download and install this module (Note : In the listings below "AHF_Installer is the location where I have extracted the installation zip file).


[root@vbgeneric AHF_Installer]# /bin/perl -v

This is perl 5, version 16, subversion 3 (v5.16.3) built for x86_64-linux-thread-multi
(with 34 registered patches, see perl -V for more detail)

Copyright 1987-2012, Larry Wall

Perl may be copied only under the terms of either the Artistic License or the
GNU General Public License, which may be found in the Perl 5 source kit.

Complete documentation for Perl, including FAQ lists, should be found on
this system using "man perl" or "perldoc perl".  If you have access to the
Internet, point your browser at http://www.perl.org/, the Perl Home Page.

[root@vbgeneric AHF_Installer]# yum install perl-Data-Dumper
Loaded plugins: langpacks, ulninfo
ol7_UEKR4                                                           | 3.0 kB  00:00:00     
ol7_latest                                                          | 3.6 kB  00:00:00     
(1/5): ol7_latest/x86_64/group_gz                                   | 136 kB  00:00:00     
(2/5): ol7_UEKR4/x86_64/updateinfo                                  | 130 kB  00:00:00     
(3/5): ol7_latest/x86_64/updateinfo                                 | 3.6 MB  00:00:00     
(4/5): ol7_latest/x86_64/primary_db                                 |  50 MB  00:00:02     
(5/5): ol7_UEKR4/x86_64/primary_db                                  |  37 MB  00:00:04     
Resolving Dependencies
--> Running transaction check
---> Package perl-Data-Dumper.x86_64 0:2.145-3.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

===========================================================================================
 Package                   Arch            Version               Repository           Size
===========================================================================================
Installing:
 perl-Data-Dumper          x86_64          2.145-3.el7           ol7_latest           47 k

Transaction Summary
===========================================================================================
Install  1 Package

Total download size: 47 k
Installed size: 97 k
Is this ok [y/d/N]: y
Downloading packages:
perl-Data-Dumper-2.145-3.el7.x86_64.rpm                             |  47 kB  00:00:00     
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Warning: RPMDB altered outside of yum.
  Installing : perl-Data-Dumper-2.145-3.el7.x86_64                                     1/1 
  Verifying  : perl-Data-Dumper-2.145-3.el7.x86_64                                     1/1 

Installed:
  perl-Data-Dumper.x86_64 0:2.145-3.el7                                                    

Complete!
[root@vbgeneric AHF_Installer]#


Then resume the installation (precreate the "data" folder if it doesn't exist)

[root@vbgeneric AHF_Installer]# mkdir /opt/oracle.ahf/data
[root@vbgeneric AHF_Installer]# ./ahf_setup

AHF Installer for Platform Linux Architecture x86_64

AHF Installation Log : /tmp/ahf_install_241000_6588_2024_02_27-13_48_51.log

Starting Autonomous Health Framework (AHF) Installation

AHF Version: 24.1.0 Build Date: 202402051317

Default AHF Location : /opt/oracle.ahf

Do you want to install AHF at [/opt/oracle.ahf] ? [Y]|N : Y

AHF Location : /opt/oracle.ahf

AHF Data Directory stores diagnostic collections and metadata.
AHF Data Directory requires at least 5GB (Recommended 10GB) of free space.

Please Enter AHF Data Directory : /opt/oracle.ahf/data

AHF Data Directory : /opt/oracle.ahf/data

Do you want to add AHF Notification Email IDs ? [Y]|N : N

Extracting AHF to /opt/oracle.ahf

Setting up AHF CLI and SDK

Configuring TFA Services

Discovering Nodes and Oracle Resources

Successfully generated certificates.

Starting TFA Services
Created symlink from /etc/systemd/system/multi-user.target.wants/oracle-tfa.service to /etc/systemd/system/oracle-tfa.service.
Created symlink from /etc/systemd/system/graphical.target.wants/oracle-tfa.service to /etc/systemd/system/oracle-tfa.service.

.-------------------------------------------------------------------------------.
| Host      | Status of TFA | PID  | Port  | Version    | Build ID              |
+-----------+---------------+------+-------+------------+-----------------------+
| vbgeneric | RUNNING       | 8540 | 39049 | 24.1.0.0.0 | 240100020240205131724 |
'-----------+---------------+------+-------+------------+-----------------------'

Running TFA Inventory...

Adding default users to TFA Access list...

.-------------------------------------------------------.
|              Summary of AHF Configuration             |
+-----------------+-------------------------------------+
| Parameter       | Value                               |
+-----------------+-------------------------------------+
| AHF Location    | /opt/oracle.ahf                     |
| TFA Location    | /opt/oracle.ahf/tfa                 |
| Orachk Location | /opt/oracle.ahf/orachk              |
| Data Directory  | /opt/oracle.ahf/data                |
| Repository      | /opt/oracle.ahf/data/repository     |
| Diag Directory  | /opt/oracle.ahf/data/vbgeneric/diag |
'-----------------+-------------------------------------'

Starting ORAchk Scheduler from AHF

AHF binaries are available in /opt/oracle.ahf/bin

AHF is successfully Installed

Do you want AHF to store your My Oracle Support Credentials for Automatic Upload ? Y|[N] : N

Moving /tmp/ahf_install_241000_6588_2024_02_27-13_48_51.log to /opt/oracle.ahf/data/vbgeneric/diag/ahf/

[root@vbgeneric AHF_Installer]# 


orachk can then be executed.  This execution is to check against "Best Practices"  :



[root@vbgeneric AHF_Installer]# orachk -b

List of running databases

1. orcl12c
2. None of above

Select databases from list for checking best practices. For multiple databases, select 1 for All or comma separated number like 1,2 etc [1-2][1]. 1
.  .
.  .  

Checking Status of Oracle Software Stack - Clusterware, ASM, RDBMS

.  .  . . . .  
.  .  .  .  .  .  .  .  .  
-------------------------------------------------------------------------------------------------------
                                                 Oracle Stack Status                          
-------------------------------------------------------------------------------------------------------
  Host Name       CRS Installed       ASM HOME  RDBMS Installed    CRS UP    ASM UP  RDBMS UP    DB Instance Name
-------------------------------------------------------------------------------------------------------
  vbgeneric                  No           No          Yes           No       No      Yes             orcl12c
-------------------------------------------------------------------------------------------------------


Copying plug-ins

. .
.  .  .  .  .  .  

*** Checking Best Practice Recommendations ( Pass / Warning / Fail ) ***

.  

============================================================
              Node name - vbgeneric
============================================================
. . . . . . 
 Collecting - Database Parameters for orcl12c database
 Collecting - Database Undocumented Parameters for orcl12c database
 Collecting - List of active logon and logoff triggers for orcl12c database
 Collecting - CPU Information
 Collecting - Disk I/O Scheduler on Linux
 Collecting - DiskMount Information
 Collecting - Kernel parameters
 Collecting - Maximum number of semaphore sets on system
 Collecting - Maximum number of semaphores on system
 Collecting - Maximum number of semaphores per semaphore set
 Collecting - Memory Information
 Collecting - OS Packages
 Collecting - Operating system release information and kernel version
 Collecting - Patches for RDBMS Home
 Collecting - Patches xml for RDBMS Home
 Collecting - RDBMS patch inventory
 Collecting - Table of file system defaults
 Collecting - number of semaphore operations per semop system call
 Collecting - Database Server Infrastructure Software and Configuration
 Collecting - Disk Information
 Collecting - Root user limits
 Collecting - Verify ORAchk scheduler configuration
 Collecting - Verify TCP Selective Acknowledgement is enabled
 Collecting - Verify no database server kernel out of memory errors
 Collecting - Verify the vm.min_free_kbytes configuration

Data collections completed. Checking best practices on vbgeneric.
------------------------------------------------------------

 INFO =>     Traditional auditing is enabled in database for orcl12c
 WARNING =>  Linux swap configuration does not meet recommendation
 WARNING =>  Hidden database initialization parameters should not be set per best practice recommendations for orcl12c
 FAIL =>     loopback interface MTU value needs to be set to 16436
 INFO =>     Most recent ADR incidents for /u01/app/oracle/product/12.2/db_1
 FAIL =>     Verify Database Memory Allocation
 INFO =>     Oracle GoldenGate failure prevention best practices
 FAIL =>     The vm.min_free_kbytes configuration is not set as recommended
 INFO =>     user_dump_dest has trace files older than 30 days for orcl12c
 INFO =>     At some times checkpoints are not being completed for orcl12c
 WARNING =>  One or more redo log groups are not multiplexed for orcl12c
 WARNING =>  Primary database is not protected with Data Guard (standby database) for real-time data protection and availability for orcl12c
 INFO =>     Important Storage Minimum Requirements for Grid & Database Homes
 CRITICAL => Operating system hugepages count does not satisfy total SGA requirements
 FAIL =>     Table AUD$[FGA_LOG$] should use Automatic Segment Space Management for orcl12c
 FAIL =>     Database parameter DB_LOST_WRITE_PROTECT is not set to recommended value on orcl12c instance
 INFO =>     umask for RDBMS owner is not set to 0022
 FAIL =>     Database parameter DB_BLOCK_CHECKING on primary is not set to the recommended value. for orcl12c
 INFO =>     Operational Best Practices
 INFO =>     Database Consolidation Best Practices
 INFO =>     Computer failure prevention best practices
 INFO =>     Data corruption prevention best practices
 INFO =>     Logical corruption prevention best practices
 INFO =>     Database/Cluster/Site failure prevention best practices
 INFO =>     Client failover operational best practices
 WARNING =>  Oracle patch 30712670 is not applied on RDBMS_HOME /u01/app/oracle/product/12.2/db_1
 WARNING =>  Oracle patch 29867728 is not applied on RDBMS_HOME /u01/app/oracle/product/12.2/db_1
 WARNING =>  Oracle patch 31142749 is not applied on RDBMS_HOME /u01/app/oracle/product/12.2/db_1
 WARNING =>  Oracle patch 26749785 is not applied on RDBMS_HOME /u01/app/oracle/product/12.2/db_1
 WARNING =>  Oracle patch 29302565 is not applied on RDBMS_HOME /u01/app/oracle/product/12.2/db_1
 WARNING =>  Oracle patch 29259068 is not applied on RDBMS_HOME /u01/app/oracle/product/12.2/db_1
 WARNING =>  Oracle clusterware is not being used
 WARNING =>  RAC Application Cluster is not being used for database high availability on orcl12c instance
 WARNING =>  DISK_ASYNCH_IO is NOT set to recommended value for orcl12c
 WARNING =>  Flashback on PRIMARY is not configured for orcl12c
 INFO =>     Database failure prevention best practices
 WARNING =>  fast_start_mttr_target has NOT been changed from default on orcl12c instance
 FAIL =>     Active Data Guard is not configured for orcl12c
 WARNING =>  Perl Patch 31858212 is not found in 12.2.0.1 RDBMS_HOME. /u01/app/oracle/product/12.2/db_1
 WARNING =>  Oracle patch 31602782 is not applied on RDBMS_HOME /u01/app/oracle/product/12.2/db_1
 WARNING =>  Oracle patch 33121934 is not applied on RDBMS_HOME /u01/app/oracle/product/12.2/db_1
 WARNING =>  Oracle patch 31211220 is not applied on RDBMS_HOME /u01/app/oracle/product/12.2/db_1
 INFO =>     Software maintenance best practices
 INFO =>     Oracle recovery manager(rman) best practices
 INFO =>     Database feature usage statistics for orcl12c
 WARNING =>  Consider investigating changes to the schema objects such as DDLs or new object creation for orcl12c
 WARNING =>  Consider investigating the frequency of SGA resize operations and take corrective action for orcl12c
------------------------------------------------------------

UPLOAD [if required] - /opt/oracle.ahf/data/vbgeneric/orachk/user_root/output/orachk_vbgeneric_orcl12c_022724_140315.zip

[root@vbgeneric AHF_Installer]# 


Thus, you can actually run the 24.1 orachk against even a 12.2 non-RAC (single instance) database.

The complete report is in HTML format in the final ZIP file.  

Here's the header :



23 February, 2024

A PDB as a Cluster Resource in RAC

 In my previous post, I have demonstrated how a new service can be created for a Pluggable Database (PDB) in Oracle RAC.  

Custom Services are configured as Resources.  Thus, in that post, I have demonstrated the custom service "newservice" for the PDB "hemantpdb" being configured in the Cluster.

However, PDBs can also be configured and managed as resources.   (see the reference in the Oracle Database Features documentation).

This is the current listing of resources in my 2-node 21c RAC Cluster :



[grid@node1 ~]$ crsctl status resource -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
               ONLINE  ONLINE       node1                    STABLE
               ONLINE  ONLINE       node2                    STABLE
ora.chad
               ONLINE  ONLINE       node1                    STABLE
               ONLINE  ONLINE       node2                    STABLE
ora.net1.network
               ONLINE  ONLINE       node1                    STABLE
               ONLINE  ONLINE       node2                    STABLE
ora.ons
               ONLINE  ONLINE       node1                    STABLE
               ONLINE  ONLINE       node2                    STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)
      1        ONLINE  ONLINE       node1                    STABLE
      2        ONLINE  ONLINE       node2                    STABLE
ora.DATA.dg(ora.asmgroup)
      1        ONLINE  ONLINE       node1                    STABLE
      2        ONLINE  ONLINE       node2                    STABLE
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       node1                    STABLE
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       node1                    STABLE
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       node2                    STABLE
ora.RECO.dg(ora.asmgroup)
      1        ONLINE  ONLINE       node1                    STABLE
      2        ONLINE  ONLINE       node2                    STABLE
ora.asm(ora.asmgroup)
      1        ONLINE  ONLINE       node1                    Started,STABLE
      2        ONLINE  ONLINE       node2                    Started,STABLE
ora.asmnet1.asmnetwork(ora.asmgroup)
      1        ONLINE  ONLINE       node1                    STABLE
      2        ONLINE  ONLINE       node2                    STABLE
ora.cdp1.cdp
      1        ONLINE  ONLINE       node1                    STABLE
ora.cdp2.cdp
      1        ONLINE  ONLINE       node1                    STABLE
ora.cdp3.cdp
      1        ONLINE  ONLINE       node2                    STABLE
ora.cvu
      1        ONLINE  ONLINE       node1                    STABLE
ora.db21crac.db
      1        ONLINE  ONLINE       node2                    Open,HOME=/u01/app/o
                                                             racle/product/21.3.0
                                                             .0/dbhome_1,STABLE
      2        ONLINE  ONLINE       node1                    Open,HOME=/u01/app/o
                                                             racle/product/21.3.0
                                                             .0/dbhome_1,STABLE
ora.db21crac.hemantpdb.pdb
      1        ONLINE  ONLINE       node1                    STABLE
      2        ONLINE  ONLINE       node2                    STABLE
ora.db21crac.newservice.svc
      1        ONLINE  ONLINE       node1                    STABLE
ora.node1.vip
      1        ONLINE  ONLINE       node1                    STABLE
ora.node2.vip
      1        ONLINE  ONLINE       node2                    STABLE
ora.qosmserver
      1        ONLINE  ONLINE       node1                    STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       node1                    STABLE
ora.scan2.vip
      1        ONLINE  ONLINE       node1                    STABLE
ora.scan3.vip
      1        ONLINE  ONLINE       node2                    STABLE
--------------------------------------------------------------------------------
[grid@node1 ~]$


Above, we can see 
the database resource "ora.db21crac.db"
the pdb resource "ora.db21crac.hemantpdb.pdb"
the service resource "ora.db21crac.newservice.svc"  (which I created in my previous post)

Now, I will add a new PDB "pdb2"


[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on Fri Feb 23 17:04:31 2024
Version 21.3.0.0.0

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


Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL> create pluggable database pdb2 admin user pdb2_admin identified by pdb2_admin;

Pluggable database created.

SQL>
SQL> col name format a30
SQL> select name, open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
HEMANTPDB                      READ WRITE
PDB2                           MOUNTED

SQL>

[grid@node1 ~]$ crsctl status resource -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
               ONLINE  ONLINE       node1                    STABLE
               ONLINE  ONLINE       node2                    STABLE
ora.chad
               ONLINE  ONLINE       node1                    STABLE
               ONLINE  ONLINE       node2                    STABLE
ora.net1.network
               ONLINE  ONLINE       node1                    STABLE
               ONLINE  ONLINE       node2                    STABLE
ora.ons
               ONLINE  ONLINE       node1                    STABLE
               ONLINE  ONLINE       node2                    STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)
      1        ONLINE  ONLINE       node1                    STABLE
      2        ONLINE  ONLINE       node2                    STABLE
ora.DATA.dg(ora.asmgroup)
      1        ONLINE  ONLINE       node1                    STABLE
      2        ONLINE  ONLINE       node2                    STABLE
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       node1                    STABLE
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       node1                    STABLE
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       node2                    STABLE
ora.RECO.dg(ora.asmgroup)
      1        ONLINE  ONLINE       node1                    STABLE
      2        ONLINE  ONLINE       node2                    STABLE
ora.asm(ora.asmgroup)
      1        ONLINE  ONLINE       node1                    Started,STABLE
      2        ONLINE  ONLINE       node2                    Started,STABLE
ora.asmnet1.asmnetwork(ora.asmgroup)
      1        ONLINE  ONLINE       node1                    STABLE
      2        ONLINE  ONLINE       node2                    STABLE
ora.cdp1.cdp
      1        ONLINE  ONLINE       node1                    STABLE
ora.cdp2.cdp
      1        ONLINE  ONLINE       node1                    STABLE
ora.cdp3.cdp
      1        ONLINE  ONLINE       node2                    STABLE
ora.cvu
      1        ONLINE  ONLINE       node1                    STABLE
ora.db21crac.db
      1        ONLINE  ONLINE       node2                    Open,HOME=/u01/app/o
                                                             racle/product/21.3.0
                                                             .0/dbhome_1,STABLE
      2        ONLINE  ONLINE       node1                    Open,HOME=/u01/app/o
                                                             racle/product/21.3.0
                                                             .0/dbhome_1,STABLE
ora.db21crac.hemantpdb.pdb
      1        ONLINE  ONLINE       node1                    STABLE
      2        ONLINE  ONLINE       node2                    STABLE
ora.db21crac.newservice.svc
      1        ONLINE  ONLINE       node1                    STABLE
ora.node1.vip
      1        ONLINE  ONLINE       node1                    STABLE
ora.node2.vip
      1        ONLINE  ONLINE       node2                    STABLE
ora.qosmserver
      1        ONLINE  ONLINE       node1                    STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       node1                    STABLE
ora.scan2.vip
      1        ONLINE  ONLINE       node1                    STABLE
ora.scan3.vip
      1        ONLINE  ONLINE       node2                    STABLE
--------------------------------------------------------------------------------
[grid@node1 ~]$


The new PDB, "pdb2" has been created but not opened yet (it is still only in the MOUNT state). However, if I try to manually add it, I get an error message that CARDINALITY wasn't specified for the first PDB that had been manually created from the SQL command line but not registered via srvctl. However, I *can* add the new PDB without having to specify CARDINALITY and then verify the status in the Cluster :

[oracle@node1 ~]$ srvctl add pdb -db DB21CRAC -pdb PDB2 -cardinality ALL -startoption open
PRCZ-4036 : failed to create pluggable database PDB2 with '-cardinality' option because this option was not specified for the previously created pluggable databases for database DB21CRAC

[oracle@node1 ~]$ srvctl config pdb -db DB21CRAC
Pluggable database name: HEMANTPDB
Application Root PDB:
Cardinality: %CRS_SERVER_POOL_SIZE%
Maximum CPU count (whole CPUs): 0
Minimum CPU count unit (1/100 CPU count): 0
Management policy: MANUAL
Rank value: 0
Start Option: open
Stop Option: immediate

[oracle@node1 ~]$ srvctl add pdb -db DB21CRAC -pdb PDB2  -startoption open

[oracle@node1 ~]$ srvctl config pdb -db DB21CRAC
Pluggable database name: HEMANTPDB
Application Root PDB:
Cardinality: %CRS_SERVER_POOL_SIZE%
Maximum CPU count (whole CPUs): 0
Minimum CPU count unit (1/100 CPU count): 0
Management policy: MANUAL
Rank value: 0
Start Option: open
Stop Option: immediate
Pluggable database name: PDB2
Application Root PDB:
Cardinality: %CRS_SERVER_POOL_SIZE%
Maximum CPU count (whole CPUs): 0
Minimum CPU count unit (1/100 CPU count): 0
Management policy: AUTOMATIC
Rank value: 0
Start Option: open
Stop Option: immediate

[grid@node1 ~]$ crsctl status resource -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
               ONLINE  ONLINE       node1                    STABLE
               ONLINE  ONLINE       node2                    STABLE
ora.chad
               ONLINE  ONLINE       node1                    STABLE
               ONLINE  ONLINE       node2                    STABLE
ora.net1.network
               ONLINE  ONLINE       node1                    STABLE
               ONLINE  ONLINE       node2                    STABLE
ora.ons
               ONLINE  ONLINE       node1                    STABLE
               ONLINE  ONLINE       node2                    STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)
      1        ONLINE  ONLINE       node1                    STABLE
      2        ONLINE  ONLINE       node2                    STABLE
ora.DATA.dg(ora.asmgroup)
      1        ONLINE  ONLINE       node1                    STABLE
      2        ONLINE  ONLINE       node2                    STABLE
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       node1                    STABLE
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       node1                    STABLE
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       node2                    STABLE
ora.RECO.dg(ora.asmgroup)
      1        ONLINE  ONLINE       node1                    STABLE
      2        ONLINE  ONLINE       node2                    STABLE
ora.asm(ora.asmgroup)
      1        ONLINE  ONLINE       node1                    Started,STABLE
      2        ONLINE  ONLINE       node2                    Started,STABLE
ora.asmnet1.asmnetwork(ora.asmgroup)
      1        ONLINE  ONLINE       node1                    STABLE
      2        ONLINE  ONLINE       node2                    STABLE
ora.cdp1.cdp
      1        ONLINE  ONLINE       node1                    STABLE
ora.cdp2.cdp
      1        ONLINE  ONLINE       node1                    STABLE
ora.cdp3.cdp
      1        ONLINE  ONLINE       node2                    STABLE
ora.cvu
      1        ONLINE  ONLINE       node1                    STABLE
ora.db21crac.db
      1        ONLINE  ONLINE       node2                    Open,HOME=/u01/app/o
                                                             racle/product/21.3.0
                                                             .0/dbhome_1,STABLE
      2        ONLINE  ONLINE       node1                    Open,HOME=/u01/app/o
                                                             racle/product/21.3.0
                                                             .0/dbhome_1,STABLE
ora.db21crac.hemantpdb.pdb
      1        ONLINE  ONLINE       node1                    STABLE
      2        ONLINE  ONLINE       node2                    STABLE
ora.db21crac.newservice.svc
      1        ONLINE  ONLINE       node1                    STABLE
ora.db21crac.pdb2.pdb
      1        OFFLINE OFFLINE                               STABLE
      2        OFFLINE OFFLINE                               STABLE
ora.node1.vip
      1        ONLINE  ONLINE       node1                    STABLE
ora.node2.vip
      1        ONLINE  ONLINE       node2                    STABLE
ora.qosmserver
      1        ONLINE  ONLINE       node1                    STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       node1                    STABLE
ora.scan2.vip
      1        ONLINE  ONLINE       node1                    STABLE
ora.scan3.vip
      1        ONLINE  ONLINE       node2                    STABLE
--------------------------------------------------------------------------------
[grid@node1 ~]$


If I then OPEN the new PDB on both instances, I can query the status again :

[oracle@node1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on Fri Feb 23 17:19:20 2024
Version 21.3.0.0.0

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


Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL> alter pluggable database pdb2 open;

Pluggable database altered.

SQL>

[oracle@node2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 21.0.0.0.0 - Production on Fri Feb 23 17:19:36 2024
Version 21.3.0.0.0

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


Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0

SQL> alter pluggable database pdb2 open;

Pluggable database altered.

SQL>

[grid@node1 ~]$ crsctl status resource ora.db21crac.pdb2.pdb
NAME=ora.db21crac.pdb2.pdb
TYPE=ora.pdb.type
TARGET=ONLINE         , ONLINE
STATE=ONLINE on node1, ONLINE on node2

[grid@node1 ~]$ crsctl status resource -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
               ONLINE  ONLINE       node1                    STABLE
               ONLINE  ONLINE       node2                    STABLE
ora.chad
               ONLINE  ONLINE       node1                    STABLE
               ONLINE  ONLINE       node2                    STABLE
ora.net1.network
               ONLINE  ONLINE       node1                    STABLE
               ONLINE  ONLINE       node2                    STABLE
ora.ons
               ONLINE  ONLINE       node1                    STABLE
               ONLINE  ONLINE       node2                    STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)
      1        ONLINE  ONLINE       node1                    STABLE
      2        ONLINE  ONLINE       node2                    STABLE
ora.DATA.dg(ora.asmgroup)
      1        ONLINE  ONLINE       node1                    STABLE
      2        ONLINE  ONLINE       node2                    STABLE
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       node1                    STABLE
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       node1                    STABLE
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       node2                    STABLE
ora.RECO.dg(ora.asmgroup)
      1        ONLINE  ONLINE       node1                    STABLE
      2        ONLINE  ONLINE       node2                    STABLE
ora.asm(ora.asmgroup)
      1        ONLINE  ONLINE       node1                    Started,STABLE
      2        ONLINE  ONLINE       node2                    Started,STABLE
ora.asmnet1.asmnetwork(ora.asmgroup)
      1        ONLINE  ONLINE       node1                    STABLE
      2        ONLINE  ONLINE       node2                    STABLE
ora.cdp1.cdp
      1        ONLINE  ONLINE       node1                    STABLE
ora.cdp2.cdp
      1        ONLINE  ONLINE       node1                    STABLE
ora.cdp3.cdp
      1        ONLINE  ONLINE       node2                    STABLE
ora.cvu
      1        ONLINE  ONLINE       node1                    STABLE
ora.db21crac.db
      1        ONLINE  ONLINE       node2                    Open,HOME=/u01/app/o
                                                             racle/product/21.3.0
                                                             .0/dbhome_1,STABLE
      2        ONLINE  ONLINE       node1                    Open,HOME=/u01/app/o
                                                             racle/product/21.3.0
                                                             .0/dbhome_1,STABLE
ora.db21crac.hemantpdb.pdb
      1        ONLINE  ONLINE       node1                    STABLE
      2        ONLINE  ONLINE       node2                    STABLE
ora.db21crac.newservice.svc
      1        ONLINE  ONLINE       node1                    STABLE
ora.db21crac.pdb2.pdb
      1        ONLINE  ONLINE       node1                    STABLE
      2        ONLINE  ONLINE       node2                    STABLE
ora.node1.vip
      1        ONLINE  ONLINE       node1                    STABLE
ora.node2.vip
      1        ONLINE  ONLINE       node2                    STABLE
ora.qosmserver
      1        ONLINE  ONLINE       node1                    STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       node1                    STABLE
ora.scan2.vip
      1        ONLINE  ONLINE       node1                    STABLE
ora.scan3.vip
      1        ONLINE  ONLINE       node2                    STABLE
--------------------------------------------------------------------------------
[grid@node1 ~]$


I can now add a custom service "pdb2service" for pdb2 to use DB21CRAC2 (instance 2, instead of instance 1) as preferred and with SESSION failover (and compare it with "newservice" for hemantpdb that I had added in the previous blogpost) :

[oracle@node1 ~]$ srvctl add service -db DB21CRAC  -service pdb2service -preferred DB21CRAC2 -available DB21CRAC1 \
> -tafpolicy BASIC -failovertype SESSION -pdb PDB2

[oracle@node1 ~]$ srvctl status service -db DB21CRAC -service pdb2service
Service pdb2service is not running.

[oracle@node1 ~]$ srvctl config service -db DB21CRAC -service pdb2service
Service name: pdb2service
Server pool:
Cardinality: 1
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Global: false
Commit Outcome: false
Reset State: NONE
Failover type: SESSION
Failover method:
Failover retries:
Failover delay:
Failover restore: NONE
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: BASIC
Edition:
Pluggable database name: PDB2
Hub service:
Maximum lag time: ANY
SQL Translation Profile:
Retention: 86400 seconds
Failback :  no
Replay Initiation Time: 300 seconds
Drain timeout:
Stop option:
Session State Consistency: DYNAMIC
GSM Flags: 0
Service is enabled
Preferred instances: DB21CRAC2
Available instances: DB21CRAC1
CSS critical: no
[oracle@node1 ~]$


[oracle@node1 ~]$ srvctl config service -db DB21CRAC -service newservice
Service name: newservice
Server pool:
Cardinality: 1
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Global: false
Commit Outcome: false
Reset State: NONE
Failover type: SELECT
Failover method:
Failover retries:
Failover delay:
Failover restore: NONE
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: BASIC
Edition:
Pluggable database name: HEMANTPDB
Hub service:
Maximum lag time: ANY
SQL Translation Profile:
Retention: 86400 seconds
Failback :  no
Replay Initiation Time: 300 seconds
Drain timeout:
Stop option:
Session State Consistency: DYNAMIC
GSM Flags: 0
Service is enabled
Preferred instances: DB21CRAC1
Available instances: DB21CRAC2
CSS critical: no
Service uses Java: false
[oracle@node1 ~]$

[oracle@node1 ~]$ srvctl start service -db DB21CRAC -service pdb2service

[oracle@node1 ~]$ srvctl config service -db DB21CRAC -service pdb2service
Service name: pdb2service
Server pool:
Cardinality: 1
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Global: false
Commit Outcome: false
Reset State: NONE
Failover type: SESSION
Failover method:
Failover retries:
Failover delay:
Failover restore: NONE
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: BASIC
Edition:
Pluggable database name: PDB2
Hub service:
Maximum lag time: ANY
SQL Translation Profile:
Retention: 86400 seconds
Failback :  no
Replay Initiation Time: 300 seconds
Drain timeout:
Stop option:
Session State Consistency: DYNAMIC
GSM Flags: 0
Service is enabled
Preferred instances: DB21CRAC2
Available instances: DB21CRAC1
CSS critical: no
Service uses Java: false
[oracle@node1 ~]$

[oracle@node1 ~]$ srvctl status service -db DB21CRAC -service pdb2service
Service pdb2service is running on instance(s) DB21CRAC2

[oracle@node1 ~]$ srvctl status service -db DB21CRAC -service newservice
Service newservice is running on instance(s) DB21CRAC1
[oracle@node1 ~]$


[grid@node1 ~]$ crsctl status resource -t
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER.lsnr
               ONLINE  ONLINE       node1                    STABLE
               ONLINE  ONLINE       node2                    STABLE
ora.chad
               ONLINE  ONLINE       node1                    STABLE
               ONLINE  ONLINE       node2                    STABLE
ora.net1.network
               ONLINE  ONLINE       node1                    STABLE
               ONLINE  ONLINE       node2                    STABLE
ora.ons
               ONLINE  ONLINE       node1                    STABLE
               ONLINE  ONLINE       node2                    STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.ASMNET1LSNR_ASM.lsnr(ora.asmgroup)
      1        ONLINE  ONLINE       node1                    STABLE
      2        ONLINE  ONLINE       node2                    STABLE
ora.DATA.dg(ora.asmgroup)
      1        ONLINE  ONLINE       node1                    STABLE
      2        ONLINE  ONLINE       node2                    STABLE
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       node1                    STABLE
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       node1                    STABLE
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       node2                    STABLE
ora.RECO.dg(ora.asmgroup)
      1        ONLINE  ONLINE       node1                    STABLE
      2        ONLINE  ONLINE       node2                    STABLE
ora.asm(ora.asmgroup)
      1        ONLINE  ONLINE       node1                    Started,STABLE
      2        ONLINE  ONLINE       node2                    Started,STABLE
ora.asmnet1.asmnetwork(ora.asmgroup)
      1        ONLINE  ONLINE       node1                    STABLE
      2        ONLINE  ONLINE       node2                    STABLE
ora.cdp1.cdp
      1        ONLINE  ONLINE       node1                    STABLE
ora.cdp2.cdp
      1        ONLINE  ONLINE       node1                    STABLE
ora.cdp3.cdp
      1        ONLINE  ONLINE       node2                    STABLE
ora.cvu
      1        ONLINE  ONLINE       node1                    STABLE
ora.db21crac.db
      1        ONLINE  ONLINE       node2                    Open,HOME=/u01/app/o
                                                             racle/product/21.3.0
                                                             .0/dbhome_1,STABLE
      2        ONLINE  ONLINE       node1                    Open,HOME=/u01/app/o
                                                             racle/product/21.3.0
                                                             .0/dbhome_1,STABLE
ora.db21crac.hemantpdb.pdb
      1        ONLINE  ONLINE       node1                    STABLE
      2        ONLINE  ONLINE       node2                    STABLE
ora.db21crac.newservice.svc
      1        ONLINE  ONLINE       node1                    STABLE
ora.db21crac.pdb2.pdb
      1        ONLINE  ONLINE       node1                    STABLE
      2        ONLINE  ONLINE       node2                    STABLE
ora.db21crac.pdb2service.svc
      1        ONLINE  ONLINE       node2                    STABLE
ora.node1.vip
      1        ONLINE  ONLINE       node1                    STABLE
ora.node2.vip
      1        ONLINE  ONLINE       node2                    STABLE
ora.qosmserver
      1        ONLINE  ONLINE       node1                    STABLE
ora.scan1.vip
      1        ONLINE  ONLINE       node1                    STABLE
ora.scan2.vip
      1        ONLINE  ONLINE       node1                    STABLE
ora.scan3.vip
      1        ONLINE  ONLINE       node2                    STABLE
--------------------------------------------------------------------------------
[grid@node1 ~]$




So, I have two PDBs "hemantpdb" and "pdb2" and their corresponding services ("newservice" and "pdb2service") using alternate database instances as Preferred Instances. "hemantpdb"'s service "newservices" runs on node1 (database instance DB21CRAC1) and "pdb2"'s service "pdb2service" runs on node2 (database instance DB21CRAC2) while both PDBs are actually OPEN on both nodes.

13 February, 2024

Adding a PDB (and Service) to a RAC database -- service running on only 1 Instance

On my existing, RAC database I have :

[oracle@node1 ~]$ srvctl status database -db DB21CRAC
Instance DB21CRAC1 is running on node node1
Instance DB21CRAC2 is running on node node2
[oracle@node1 ~]$
[oracle@node1 ~]$ srvctl status service -d DB21CRAC -s hemantpdb
PRKO-2017 : Service hemantpdb does not exist for database DB21CRAC.
[oracle@node1 ~]$ srvctl status service -db DB21CRAC -service hemantpdb -pdb HEMANTPDB
PRKO-2017 : Service hemantpdb does not exist for database DB21CRAC.
[oracle@node1 ~]$
[grid@node1 ~]$ lsnrctl status LISTENER_SCAN1

LSNRCTL for Linux: Version 21.0.0.0.0 - Production on 13-FEB-2024 15:06:11

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_SCAN1
Version                   TNSLSNR for Linux: Version 21.0.0.0.0 - Production
Start Date                13-FEB-2024 14:51:13
Uptime                    0 days 0 hr. 14 min. 57 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/21.3.0.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/node1/listener_scan1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER_SCAN1)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.115)(PORT=1521)))
Services Summary...
Service "0f488ad896262f80e0636f38a8c0fc18" has 2 instance(s).
  Instance "DB21CRAC1", status READY, has 1 handler(s) for this service...
  Instance "DB21CRAC2", status READY, has 1 handler(s) for this service...
Service "DB21CRAC" has 2 instance(s).
  Instance "DB21CRAC1", status READY, has 1 handler(s) for this service...
  Instance "DB21CRAC2", status READY, has 1 handler(s) for this service...
Service "DB21CRACXDB" has 2 instance(s).
  Instance "DB21CRAC1", status READY, has 1 handler(s) for this service...
  Instance "DB21CRAC2", status READY, has 1 handler(s) for this service...
Service "hemantpdb" has 2 instance(s).
  Instance "DB21CRAC1", status READY, has 1 handler(s) for this service...
  Instance "DB21CRAC2", status READY, has 1 handler(s) for this service...
The command completed successfully
[grid@node1 ~]$
[oracle@node1 ~]$SQL> select inst_id, name, network_name from gv$services order by 1;

   INST_ID NAME             NETWORK_NAME
---------- ---------------- ----------------
         1 hemantpdb        hemantpdb
         1 DB21CRAC         DB21CRAC
         1 SYS$BACKGROUND
         1 DB21CRACXDB      DB21CRACXDB
         1 SYS$USERS
         2 hemantpdb        hemantpdb
         2 DB21CRAC         DB21CRAC
         2 SYS$BACKGROUND
         2 DB21CRACXDB      DB21CRACXDB
         2 SYS$USERS

10 rows selected.

SQL>
SQL> select inst_id, con_id, name, open_mode from gv$pdbs order by 1,2;

   INST_ID     CON_ID NAME             OPEN_MODE
---------- ---------- ---------------- ----------
         1          2 PDB$SEED         READ ONLY
         1          3 HEMANTPDB        READ WRITE
         2          2 PDB$SEED         READ ONLY
         2          3 HEMANTPDB        READ WRITE

SQL>


Which means that I have created a custom PDB called "HEMANTPDB" and there is a default service called "hemantpdb" on each instance.  However, this services is NOT listed when I check via srvctl. 

This is because srvctl queries the cluster for information about services.  Service"hemantpdb" is created by default when I run CREATE PLUGGABLE DATABASE HEMANTPDB.


So, I can add a new service and configure Transparent Application Failover for SELECT failover but with only the first instance(DB21CRAC1) as the only one to start the service on initially{and the second instance (DB21CRAC2) as the alternate} :

[oracle@node1 ~]$ srvctl add service -db DB21CRAC  -service newservice -preferred DB21CRAC1 -available DB21CRAC2 -tafpolicy BASIC -failovertype SELECT -pdb HEMANTPDB
[oracle@node1 ~]$
[oracle@node1 ~]$ srvctl config service -db DB21CRAC  -service newservice
Service name: newservice
Server pool:
Cardinality: 1
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: false
Global: false
Commit Outcome: false
Reset State: NONE
Failover type: SELECT
Failover method:
Failover retries:
Failover delay:
Failover restore: NONE
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: BASIC
Edition:
Pluggable database name: HEMANTPDB
Hub service:
Maximum lag time: ANY
SQL Translation Profile:
Retention: 86400 seconds
Failback :  no
Replay Initiation Time: 300 seconds
Drain timeout:
Stop option:
Session State Consistency: DYNAMIC
GSM Flags: 0
Service is enabled
Preferred instances: DB21CRAC1
Available instances: DB21CRAC2
CSS critical: no
[oracle@node1 ~]$
[oracle@node1 ~]$ srvctl start service -db DB21CRAC -service newservice
[oracle@node1 ~]$ srvctl status service -db DB21CRAC -service newservice
Service newservice is running on instance(s) DB21CRAC1
[oracle@node1 ~]$




If the database instance DB21CRAC1 fails (crashes), the ClusterWare starts the service on DB21CRAC2.  Here I kill the DB21CRAC process and then verify that the service has restarted on DB1CRAC2  :

[oracle@node1 ~]$ srvctl status service -db DB21CRAC -service newservice
Service newservice is running on instance(s) DB21CRAC1
[oracle@node1 ~]$ ps -ef |grep smon
oracle    2951     1  0 16:05 ?        00:00:00 ora_smon_DB21CRAC1
root      3521     1  1 14:50 ?        00:00:48 /u01/app/21.3.0.0/grid/bin/osysmond.bin
grid      4068     1  0 14:50 ?        00:00:00 asm_smon_+ASM1
oracle    4146 25526  0 16:05 pts/0    00:00:00 grep --color=auto smon
[oracle@node1 ~]$ kill -9 2951
[oracle@node1 ~]$ srvctl status service -db DB21CRAC -service newservice
Service newservice is running on instance(s) DB21CRAC2
[oracle@node1 ~]$


In this case, connections using this service name ("newservice") will connect (failover) to the 2nd database instance running on node2 of the Cluster.