Listing Databases on an Oracle DB node

 In this blog post I am sharing a script that I wrote that will give you the list of databases running on a DB node.  The information  provided by the script is

• DB_UNIQUE_NAME
• ORACLE_SID
• DB_HOME

WHY

I have been working on a script to automatically configure OKV for all of the Oracle Databases running on a DB host.  In order to install OKV in a RAC cluster, I want to ensure the unique OKV software files are in the same location on every host when I set the WALLET_ROOT variable for my database.  The optimal location is to put the software under $ORACLE_BASE/admin/${DB_NAME} which should exist on single instance nodes, and RAC nodes.

Easy right?

I thought it would be easy to determine the name of all of the databases on a host so that I could make sure the install goes into $ORACLE_BASE/admin/{DB_NAME}/okv directory on each DB node.

The first item I realized is that the directory structure under $ORACLE_BASE/admin is actually the DB_UNIQUE_NAME rather than DB_NAME. This allows for 2 different instances of the same DB_NAME (primary and standby) to be running on the same DB node without any conflicts. 

Along with determining the DB_UNIQUE_NAME, I wanted to take the following items into account

• A RAC environment with, or without srvctl properly configured
• A non-RAC environment 
• Exclude directories that are under $ORACLE_BASE/admin that are not a DB_UNQUE_NAME running on the host.
• Don't match on ORACLE_SID.  The ORACLE_SID name on a DB node can be completely different from the DB_UNIQUE_NAME.

Answer:

After searching around Google and not finding a good answer I checked with my colleagues.  Still no good answer.. There were just suggestions like "srvctl config", which would only work on a RAC node where all databases are properly registered.  

The way I decided to this was to 

• Identify the possible DB_UNIQUE_NAME entries by looking in $ORACLE_BASE/admin
• Match the possible DB_UNIQUE_NAME with ORACLE_SIDs by looking in $ORACLE_BASE/diag/rdbms/${DB_UNIQUE_NAME} to find the ORACLE_SID name.  I would only include DB_UNIQUE_NAMEs that exist in this directory structure and have a subdirectory.
• Find the possible ORACLE_HOME by matching the ORACLE_SID to the /etc/oratab.  If there is no entry in /etc/oratab still include it.

Script:

Below is the script I came up with, and it displays a report of the database on the host.  This can be changed to store the output in a temporary file and read it into a script that loops through the databases.

#################################################################### ## Make sure that $ORACLE_BASE is set and exists before continuing #################################################################### if [ -z ${ORACLE_BASE+x} ] then echo "\$ORACLE_BASE must be set" exit 0 fi #################################################################### ## Make sure that $ORACLE_BASE/admin exists before continuing #################################################################### if [ -d ${ORACLE_BASE}/admin ] then echo "\$ORACLE_BASE is set to : ${ORACLE_BASE}" else echo "\$ORACLE_BASE is set to ${ORACLE_BASE} but ${ORACLE_BASE}/admin does not exist" exit 0 fi ######################################################################## ## Loop through $ORACLE_BASE/admin to find the list of DB_UNIQUE_NAMEs ######################################################################### for dbdir in $ORACLE_BASE/admin/* do DB_UNIQUE_NAME=${dbdir##*/} ## echo "possible DB_UNIQUE_NAME : ${DB_UNIQUE_NAME}" ######################################################################## ## Loop through $ORACLE_BASE/diag/rdbms/${DB_UNIQUE_NAME} to validate ######################################################################### for siddir in ${ORACLE_BASE}/diag/rdbms/${DB_UNIQUE_NAME}/* do db_sid=${siddir##*/} if [ -d ${siddir} ] then echo " " echo "DB_UNIQUE_NAME : ${DB_UNIQUE_NAME}" echo "ORACLE_SID : ${db_sid}" db_home=`cat /etc/oratab | grep ${db_sid} | cut -d ':' -f 2` if [ -z "${db_home}" ] then echo "ORACLE_HOME : ****** NOT IN /etc/oratab **** Cannot determine ORACLE_HOME *****" else echo "ORACLE_HOME : ${db_home}" fi echo " " fi done done

Output:

Below is the sample output from the script.. You can see that it doesn't require the DB to exist in the /etc/oratab file.

DB_UNIQUE_NAME : cdb1db1
ORACLE_SID     : cdb1db11
ORACLE_HOME    :  ******  NOT IN /etc/oratab **** Cannot determine ORACLE_HOME *****


DB_UNIQUE_NAME : daver
ORACLE_SID     : daver1
ORACLE_HOME    : /u01/app/oracle/product/19.0.0.0/dbhome_1


DB_UNIQUE_NAME : dbsgadat
ORACLE_SID     : dbsgadat1
ORACLE_HOME    : /u01/app/oracle/product/19.0.0.0/dbhome_1


DB_UNIQUE_NAME : dbsgprd
ORACLE_SID     : dbsgprd1
ORACLE_HOME    : /u01/app/oracle/product/19.0.0.0/dbhome_1

Finally:

If you are also trying to get a list of databases that are running on a DB node I hope this helps you.

ZDLRA backups -- How do I know if they are Encrypted

 The ZDLRA introduced a new feature with release 23.1 that can both encrypt backups (if they are not already encrypted from TDE) and  compress the backups .  The combing of both encryption and compression with this feature is unique to the ZDLRA.

I talked about this new exciting feature in a blog post on Oracle.com you can find here.

What I am am going to cover in this blog post is how to audit the RMAN catalog on the ZDLRA to validate that your backups are completely RMAN encrypted.

There are two big advantages of ensuring your backups are fully encrypted

1) With the prevalence of data exfiltration, and the advent of new regulations in many industries,  full encryption of backups is mandatory

2) When sending a backup to the Oracle cloud (either in OCI or to object storage on ZFS) full encryption is required to protect the backup data.

The question I often get asked with this feature is..

 "How do you tell  if your backups are encrypted ?"

You can can determine that your backups are encrypted by looking at the RMAN catalog.

The RC_BACKUP_PIECE view contains a column identifying if the backup is encrypted.  This column is set to "YES" only when the backup piece is encrypted.

Keep in mind that there multiple types of backups pieces contained in the catalog

• Controlfile backups
• Spfile backups
• Archive log sweeps
• Archive log backups from real-time redo
• Datafile backups
• Virtual Full backups created from incremental backups.

All of these backups except for two are sent from RMAN with "encryption on" and the backup set will marked as encrypted based on the RMAN encryption setting.

The two that are not set by RMAN directly are

• Real-time redo backups. Real-time redo backups are identified in the RMAN catalog as encrypted when the destination setting on the protected database has ENCRYPTION=ENABLE set.
• Virtual Full backups.  Virtual full backups are identified, for each datafile backup set, as encrypted ONLY after a new L0 is taken with RMAN encryption on, and all subsequent L1 backups are encrypted.  I know that is a lot of stipulations on identifying the virtual full backup as encrypted.  Only when a new FULL encrypted backup is taken, and all future incremental backups are encrypted can the ZDLRA be sure the backup has remained completely encrypted.

Checking the catalog

  The script below takes 2 parameters (&db_name, and &days_to_compare) and it will check the RMAN catalog and display the status of the backups, by backup type making it easier to identify any backup pieces that may not be encrypted.

set linesize 170; set pagesize 100 ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD hh24:mi:ss'; define db_name =MYDB define days_to_compare = 7 col backup_count format 99999 heading 'Backup|pieces' col encrypted format a10 heading 'Encrypted | Yes or No' col compressed format a10 heading 'Compressed | Yes or No' col backup_type_known format a40 heading 'Backup piece type' col start_time format a25 heading 'Start Time | by Day' col backup_size format 999,999.99 heading 'Backup Size | by Day (GB)' set pagesize 150 set linesize 150 ttitle skip 2 CENTER 'Database backup summary for last &days_to_compare days database: &db_name' SKIP 2 set feedback off set echo off set underline = set verify off set termout off spool database_report_&db_name.out select Encrypted, compressed, count(1) backup_count, backup_type_known from ( select case when (backup_type = 'L' and handle like '$RSCN%') then 'Archive Log - real-time redo' when (backup_type = 'L' and handle not like '$RSCN%') then 'Archive Log - log sweep' when (backup_type = 'D' and handle like 'c-%') then 'Controlfile/SPFILE backup' when (backup_type = 'D' and incremental_level=0 and handle like 'VB$%') then 'Virtual Full backup' when (backup_type = 'I' and incremental_level=1 and handle like 'VB$%') then 'Incremental L1 backup' when (backup_type = 'I' and incremental_level=1 and handle not like 'VB$%') then 'Incr. L1 --> Not yet virtualized' else backup_type || ' ' || incremental_level || ' ' || media end as backup_type_known, to_char(START_TIME,'mm/dd/yy hh24:mi:ss') Start_time, Encrypted, compressed from rc_backup_piece_details where start_time > sysdate - &days_to_compare and db_name = '&db_name' and (media is null or media like 'Recovery Appliance%') order by backup_type_known,encrypted,compressed ) group by encrypted,backup_type_known,compressed;

This provides a nicely formatted output as you can see below.

                                             Database backup summary for last 15 days database: DBSG23AI

Encrypted  Compressed Backup
 Yes or No  Yes or No pieces Backup piece type
========== ========== ====== ========================================
YES        YES            69  Full backup
YES        NO             39 Archive Log - log sweep
NO         YES             1 Incremental L1  backup
YES        NO           3958 Archive Log - real-time redo
YES        YES            67 Incremental L1  backup
NO         YES             3  Full backup
NO         NO              1 Controlfile/SPFILE backup
YES        NO             26 Controlfile/SPFILE backup
YES        NO            221 Incremental L1  backup

In the report you can see that there a  few backups that not encrypted, along with some controlfile/spfile backups.

NOTE: In order to run this report, I created a REPORT user in the database on the ZDLRA as an "monitor" user.. A report has enough permissions to create this report.

OKV and ZDLRA 

Previously when sending backups to Cloud (which included OCI object storage on ZFSSA), OKV was required. When using Space Efficient Encrypted backups, you can ensure that EVERY backup piece is fully encrypted and RMAN recognizes them as encrypted.

If you follow the information in the blog, and what I have posted in the past, you will no longer need to configure OKV when sending backups to the cloud. 

If all backup pieces are encrypted, and the RMAN catalog reports that all backup are encrypted, you can create backups using DBMS_RA.CREATE_ARCHIVAL_BACKUP setting the "encryption_algorithm" to "CLIENT" or "ENC_CLIENT". This will tell the ZDLRA not utilize OKV to encrypt backups, but if any backup pieces are NOT encrypted, the archival backups will fail.

Oracle Backup Compression and Encryption layers explained

 When working with customers who are applying compression and/or encryption to their Oracle DB backups, I found that it isn't always clear if backups are compressed or encrypted, or both. In this blog post I will break compression and encryption of Oracle backups down into the levels where these operations could occur.  Below is a high level view of these 3 levels.

Database

Compression

Data in the database can be compressed in any one of the following formats or all of the formats

HCC - Available only on Exadata, or ZFS storage, this compression is a columnar compression format with different options that allow you to choose the appropriate access speed and compression ratio for your data

Advanced Compression - A licensable option that will automatically compress data in the background to optimize storage without compromising performance.

Basic Compression - Requires a lock on object during insert and is typically used within a data warehouse.

 External Compression - In some cases the data stored in the database may already be compressed externally. An example of this is image files which are already stored in a compressed format.

 

Encryption

Data in the database can be encrypted in any one of the following formats or all of the formats

TDE - All data in the tablespace is encrypted by database.

Column Encryption - Specific data within a column is encrypted, SSN for example.  This is less widely used and most customers use TDE instead.

 External Encryption - In some cases the data stored in the database may already be encrypted by the application.

 

 NOTE: 

1. If the data is compressed and/or encrypted in these manors it will continue in that format when backed up.  

• Any data that encrypted in the database will remain encrypted in the backups
• Any data that is compressed in the database will remain compressed in the backups
• Backups of data that is compressed and/encrypted will get little to no compression when backed up

2. RMAN does not know that the data  is either compressed or encrypted, and querying the RMAN views will not tell you that either has occurred.

3. Having data encrypted and/or compressed in the database may not stop you from further compressing and/or encrypted the backups.

ZDLRA

Compression

Datafile Compression - With Datafile compression you have 2 choices to compress the backups

• RA_FORMAT = TRUE - This  compresses all datafile backups in the new ZDLRA 23.1 format.  If the datafile is part of a TDE tablespace, the blocks will be decrypted prior to compression to ensure the best compression ratio.  
• RA_FORMAT not set or  FALSE - Backups of datafiles will be sent as uncompressed (unless you create a RMAN compressed backupset which the ZDLRA will uncompress before ingesting).  Once they are received on the ZDLRA they will be compressed in storage on ZDLRA.  When replicated to another ZDLRA, or restored, they are uncompressed.

Real-time Redo Compression - When sending real-time redo to the ZDLRA you can have the ZDLRA create an RMAN compressed backupset for the archive logs.  The level of compression can be set on the policy.  Once stored in an RMAN compressed backupset format, it is replicated and restored as a compressed backupset.  

          NOTE: If the Redo stream contains changes to a TDE tablespace, or you are                                            configuring encryption on the RA as destination, you may get little to no actual compression 

SPFILE, Controfile, archivelog backups - The ZDLRA will NOT attempt to compress these backupsets internally.  Only datafile backups are compressed on the ZDLRA.

 

Encryption

Datafile Encryption - Whether a datafile is encrypted by the ZDLRA in the new ZDLRA 23.1 format depends on these 2 conditions.

• RA_FORMAT = TRUE and "RMAN Encryption ON" - If the datafile is NOT part of a TDE tablespace, this will force BOTH compression and encryption of that datafile backup.
• RA_FORMAT = TRUE and "RMAN Encryption OFF" - If the datafile is part of a TDE tablespace, the backup of this datafile will remain encrypted.  If the datafile is NOT part of a TDE tablespace, the backup will NOT be encrypted.

Real-time Redo Encryption - If real-time redo is utilized and your database has implemented TDE, the change data in the archive log backups will be encrypted.  However, this backup is not considered RMAN encrypted, and ENCRYPTION=ENABLED must be set on the destination definition to ensure that the real-time redo backupsets are considered fully encrypted by RMAN.

SPFILE, Controlfile, archivelog backup Encryption - These are not encrypted by the ZDLRA.

 

 NOTE: 

1. The new Space Efficient Encrypted backup feature of the ZDLRA only affects datafile backups.

2. Real-time redo backups can be compressed and/or encrypted by the ZDLRA.

3. If you are using the new RA_FORMAT=TRUE for non-TDE datafile backup, you will only get a compressed a backupset.  You have set RMAN Encryption on with RA_FORMAT=TRUE in order to encrypt the backupset.

4. If you are backing up a non-TDE  datafile, and wish to encrypt it with the library, it will also be compressed.  You cannot encrypt without compression, but you can compress without encryption.

5. If datafile backups are sent to the ZDLRA  without RA_FORMAT=TRUE, they will appear as compressed in the RMAN catalog.  With RA_FORMAT=TRUE they will not appear as compressed.

6.If real-time redo is sent to the ZDLRA, and the profile for the database is set to compress the archivelogs, they will appear as compressed in the RMAN catalog. 

 

RMAN

Compression

Datafile Compression - With Datafile compression you have 2 choices to compress the backups

• RA_FORMAT = TRUE - RMAN compression is ignored when this option is set.  
• RA_FORMAT not set or  FALSE - RMAN can create a compressed backupset for datafiles.  If the datafile is part of a TDE tablespace, this datafile will not be able to create a virtual full.  If the Datafile is NOT part of a TDE tablespace, the backset will be decompressed on the ZDLRA and will not be stored as a Compressed backupset.

SPFILE, Controfile, archivelog backups - The ZDLRA will NOT attempt to compress these backupsets internally.  They remain compressed.

 

Encryption

Datafile Encryption - RMAN Encrypt ON  creates an Encrypted backupset which cannot be virtualized by the ZDLRA.  This should only be set when using RA_FORMAT=TRUE which bypasses RMAN encryption

SPFILE, Controlfile, archivelog backup Encryption - These can be encrypted by setting RMAN Encryption on.

 NOTE: 

1. The new Space Efficient Encrypted backup feature of the ZDLRA only affects datafile backups.

2. Real-time redo backups can be compressed and/or encrypted by the ZDLRA.

3. If you are using the new RA_FORMAT=TRUE for non-TDE datafile backup, you will only get a compressed a backupset.  You have set RMAN Encryption on with RA_FORMAT=TRUE in order to encrypt the backupset.

4. If you are backing up a non-TDE  datafile, and wish to encrypt it with the library, it will also be compressed.  You cannot separate encryption from compression, but you can compress only.