Script to check the services in a multitenant container databaser

A simple script to check services running out of a specific PDB, or for all PDBs if the root container is selected:

column pdb_name format a20
column status format a20
prompt
prompt List of PDBs available on this server:
prompt
select p.pdb_name
from dba_pdbs p join v$pdbs v
on (p.pdb_id = v.con_id)
where open_mode='READ WRITE'
/

accept pdb_name DEFAULT 'CDB$ROOT' prompt 'Select a PDB (default=root container): '
alter session set container=&&pdb_name;
col con_name format a40
set lines 200
select 'You are connected to: ' || sys_context('USERENV','CON_NAME') "con_name"
from dual;

col name format a20
col global format a20
col pdb format a20
col con_name format a20
col network_name format a20
col creation_Date format a20
prompt V$SERVICES
select name,global,pdb,network_name from v$services
/

prompt V$ACTIVE_SERVICES
select name,global,con_name,network_name from v$active_services
/

prompt DBA_SERVICES
select service_id,name,network_name,creation_date,pdb
from dba_services
/

prompt CDB_SERVICES
select service_id,name,network_name,creation_date,pdb
from cdb_services
/
exit

What are the DST time zone files ?

What are the Oracle Time Zone files?

The Oracle Database time zone files contain the valid time zone names. The following information is also included for each time zone:

* Offset from Coordinated Universal Time (UTC)
* Transition times for Daylight Saving Time
* Abbreviations for standard time and Daylight Saving Time

Where do these timezone files exist?

The time zone files are stored in the $ORACLE_HOME/oracore/zoneinfo directory.  

Oracle Database supplies multiple versions of time zone files, and there are two types of file associated with each version:

* a large file, which contains all the time zones defined in the database
* a small file, which contains only the most commonly used time zones. 

The large version files are named as timezlrg_version_number.dat and the small version files are named as timezone_version_number.dat, where version_number is the version number of the time zone file. 

What is the default timezone file?

The default time zone file is a large time zone file having the highest version number [ that was delivered when the Oracle software was installed].

For Oracle 19c, the default is DST32:

In Oracle Database 19c, the default time zone file is $ORACLE_HOME/oracore/zoneinfo/timezlrg_32.dat.

How do I determine what timezone files that are in use in my database?

select * from V$TIMEZONE_FILE;

FILENAME                VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_32.dat              32          0

How do I find the database time zone?

SELECT dbtimezone FROM DUAL;

DBTIME
------
+00:00

When is the database time zone set?

Set the database time zone when the database is created by using the SET TIME_ZONE clause of the CREATE DATABASE statement. 
If you do not set the database time zone, then it defaults to the time zone of the server's operating system.

The time zone may be set to a named region or an absolute offset from UTC. 

To set the time zone to a named region, use a statement similar to the following example:

CREATE DATABASE db01
...
SET TIME_ZONE='Europe/London';

To set the time zone to an offset from UTC, use a statement similar to the following example:

CREATE DATABASE db01
...
SET TIME_ZONE='-05:00';

What operations are affected by the database time zone?

The database time zone is relevant only for TIMESTAMP WITH LOCAL TIME ZONE columns. Oracle recommends that you set the database time zone to UTC (0:00) to avoid data conversion and improve performance when data is transferred among databases. This is especially important for distributed databases, replication, and exporting and importing.

If you do NOT use NAMED Timezone information in your application(s) you are not using the Oracle DST information and Oracle DST patches have simply no impact on your system.
You may apply Oracle DST patches, but the Oracle DST information is simply not used.

Will the time zone files be updated during quarterly patching?

Starting with Oracle Database 19c RU 19.18.0, all available DST patches are installed with the RU, and deployed into the Oracle_home/oracore/zoneinfo directory. 
Installing DST patches does not affect database operation.

How are the time zone files delivered?

Each Oracle Database release includes a time zone file that is current at the time of the release and a number of older version files. 

The time zone files that are supplied with the Oracle Database are updated periodically to reflect changes in transition rules for various time zone regions.
Between Oracle Database releases, new time zone file versions may be provided in patch sets or individual patches to reflect the changes in transition rules for various time zone regions. 
Older time zone file versions allow you to run upgraded databases without a need to immediately upgrade the time zone file to the most current version.

Is the update of the DST time zone files absolutely necessary? In many cases, not at all.

The MOS note "Primary Note DST FAQ : Updated DST Transitions and New Time Zones in Oracle RDBMS and OJVM Time Zone File Patches (Doc ID 412160.1)" states:

Please DO note that even if you are located in a country that has changed the DST start or end day, in many cases there is no need to "update the Oracle RDBMS DST information".
If your OS has been patched or correctly configured the the "oracle time" (= sysdate) will be correct.
Only if the actual timezone is used in (PL)SQL an "Oracle RDBMS DST" update is needed.

What about multitenant?

Each container in a multitenant environment has its own time zone file
Oracle allows different containers to have different time zone file versions, so you have the option of upgrading only a subset of containers in a CDB
To perform a time zone data upgrade across an entire CDB, you must upgrade the CDB root and each PDB separately. 
A new PDB is always assigned the time zone version of PDB$SEED. 
PDB$SEED is always assigned the time zone version at the time of CDB creation. 
The time zone version of PDB$SEED cannot be changed.

Documentation:

Choosing a Time Zone File

Upgrading the Time Zone File and Timestamp with Time Zone Data

All Time Zone Files (DST) Included in Release Updates (RUs)

How to list the contents of a folder in a tree-like fashion in Linux

Use the tree command:

cd /sw/oracle/admin
tree -L 2
.
├── cdb
│   ├── adump
│   ├── dpdump
│   ├── log
│   ├── pfile
│   └── xdb_wallet
├── sales
│   ├── adump
│   ├── dpdump
│   ├── pfile
│   ├── sql
│   └── xdb_wallet
└── hr
    ├── adump
    ├── dpdump
    ├── log
    ├── pfile
    ├── scripts
    ├── sql
    └── xdb_wallet

20 directories, 0 files

The -L flag indicates the number of levels you want to display. In my case, if I change the value from 2 to 3, I get the output below instead (abbreviated):

.
├── cdb
│   ├── adump
│   │   ├── FE78BD1F8E6730CDE0536709D10AC9C0
│   │   └── FE7BD04D2DFBE569E0536709D10A3AF0
│   ├── dpdump
│   │   ├── dp.log
│   │   ├── FE78BD1F8E6730CDE0536709D10AC9C0
│   │   ├── FE7AF28B415262F7E0536709D10A8B2E
│   │   └── FE7BD04D2DFBE569E0536709D10A3AF0
│   ├── log
│   │   ├── 2023-07-11_cdb.2431565
│   │   ├── 2023-07-12_cdb.2489915
│   │   ├── 2023-07-12_cdb.2576176
├── sales
│   ├── adump
│   ├── dpdump
│   │   └── dp.log
│   ├── pfile
│   │   └── init.ora.5192023145034
│   ├── sql
│   │   ├── analyze_sales.sh
│   │   ├── cfgtoollogs
│   │   ├── config_sales.txt
│   │   ├── cre_db.sh
│   │   └── deploy_sales.sh

26 directories, 181 files

The command is not installed by default but is avaible both for RHEL / CentOS / Fedora Linux as well as Debian based Linux distributions like Ubuntu.

How to show current utilization of sessions, processes and other important settings for a database

Example:

set lines 300
col RESOURCE_NAME format a30
select RESOURCE_NAME,CURRENT_UTILIZATION,MAX_UTILIZATION,INITIAL_ALLOCATION,LIMIT_VALUE
from v$resource_limit
where resource_name in ('processes','sessions');

RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION     LIMIT_VALUE
------------------------------ ------------------- --------------- ---------------------- ---------------
processes                                     1498            1500       1500             1500
sessions                                      1511            1517       2272             2272

Documented for Oracle 19c here

Using the autoupgrade tool to migrate a non-multitenant database to a PDB on the same host

The autoupgrade tool can convert a non-cdb database to a PDB running in a container database.
If you need to switch to a multitenant architechture, this could be a way forward.

In my case, I had a non-cdb database of versjon 19c running on my server testserver1.oric.no, and I want to convert it to a PDB.

This is what I did to move away from the non-cdb architecture:

1. Download the latest version of autoupgrade from Oracle Support: AutoUpgrade Tool (Doc ID 2485457.1)

On your database server, make a backup of the existing $ORACLE_HOME/rdbms/admin/autoupgrade.jar file, then transfer the new version of the file you just downloaded from Oracle into the same folder.

2. create a container database.

You can execute the database configuration assistant silently to quickly set one up. Put this in a shell script and call it for example "cre_cdb.sh":

export ORACLE_SID=cdb
dbca -silent -createDatabase \
 -templateName General_Purpose.dbc \
 -gdbname cdb -sid cdb -responseFile NO_VALUE \
 -characterSet AL32UTF8 \
 -sysPassword secret \
 -systemPassword secret \
 -createAsContainerDatabase true \
 -numberOfPDBs 0 \
 -databaseType MULTIPURPOSE \
 -memoryMgmtType auto_sga \
 -totalMemory 2048 \
 -storageType FS \
 -datafileDestination "/data01/oradata/" \
 -redoLogFileSize 50 \
 -emConfiguration NONE \
 -ignorePreReqs

Execute it:

chmod 755 cre_db.sh
./cre_db.sh

3. Set some parameters in the cdb:

alter system set db_recovery_file_dest_size=10G;
alter system set db_recovery_file_dest='/fra';
alter system set db_create_file_dest = '/data01/oradata';
shutdown immediate
startup mount
alter database archivelog;
alter database open;
exit

4. When the container database is ready, create a configuration file.

Put this in a file called "cre_sample_file.sh":

$ORACLE_BASE/product/19c/jdk/bin/java \
  -jar $ORACLE_BASE/product/19c/rdbms/admin/autoupgrade.jar \
  -create_sample_file config /sw/oracle/admin/mydb/sql/config.txt noncdbtopdb

Execute it:

chmod 755 cre_sample_file.sh
./cre_sample_file.sh

The output should be a file that you now open in an editor and edit. This is how mine looked:

upg1.log_dir=/sw/oracle/cfgtoollogs/autoupgrade/mydb
upg1.sid=mydb
upg1.source_home=/sw/oracle/product/19c
upg1.target_cdb=cdb
upg1.target_home=/sw/oracle/product/19c
upg1.target_pdb_name=mydb
upg1.start_time=NOW                      # Optional. 10 Minutes from now
upg1.upgrade_node=testserver1.oric.no    # Optional. To find out the name of your node, run the hostname utility. Default is ''localhost''
upg1.run_utlrp=yes                       # Optional. Whether or not to run utlrp after upgrade
upg1.target_version=19                   # Oracle version of the target ORACLE_HOME.  Only required when the target Oracle database version is 12.2

There are many additional options that could potentially be relevant, but in my case the ones listed above was all that was needed.

5. Analyze the database before converting.

Put the following in a script called "analyze_db.sh":

$ORACLE_BASE/product/19c/jdk/bin/java \
  -jar $ORACLE_BASE/product/19c/rdbms/admin/autoupgrade.jar \
  -config /sw/oracle/admin/mydb/sql/config.txt -mode analyze

Execute it:

chmod 755 analyze_db.sh
./analyze_db.sh

In my case, this job returned very quickly, since there is no upgrade job do be done, we are simply converting from an non-cdb to a PDB!

6. Finally, convert the database to a pdb.

Put the following into a file called "deploy.sh":

$ORACLE_BASE/product/19c/jdk/bin/java \
  -jar $ORACLE_BASE/product/19c/rdbms/admin/autoupgrade.jar \
  -config /sw/oracle/admin/mydb/sql/config.txt -mode deploy

Execute it:

chmod 755 deploy.sh
./deploy.sh

The deploy phase goes through several steps and leaves you at the prompt. You can watch the progress by executing "lsj" at the prompt:

AutoUpgrade 23.1.230224 launched with default internal options
Processing config file ...
+--------------------------------+
| Starting AutoUpgrade execution |
+--------------------------------+
1 Non-CDB(s) will be processed
Type 'help' to list console commands
upg> lsj
+----+-------+-----------+---------+-------+----------+-------+-------------------+
|Job#|DB_NAME|      STAGE|OPERATION| STATUS|START_TIME|UPDATED|            MESSAGE|
+----+-------+-----------+---------+-------+----------+-------+-------------------+
| 101|   mydb|NONCDBTOPDB|EXECUTING|RUNNING|  15:19:20| 1s ago|Compatibility check|
+----+-------+-----------+---------+-------+----------+-------+-------------------+
Total jobs 1

upg> lsj
+----+-------+-----------+---------+-------+----------+-------+------------------+
|Job#|DB_NAME|      STAGE|OPERATION| STATUS|START_TIME|UPDATED|           MESSAGE|
+----+-------+-----------+---------+-------+----------+-------+------------------+
| 101|   mydb|NONCDBTOPDB|EXECUTING|RUNNING|  15:19:20| 3s ago|Executing describe|
+----+-------+-----------+---------+-------+----------+-------+------------------+
Total jobs 1
.
.
.
upg> lsj
+----+-------+---------+---------+-------+----------+-------+-------+
|Job#|DB_NAME|    STAGE|OPERATION| STATUS|START_TIME|UPDATED|MESSAGE|
+----+-------+---------+---------+-------+----------+-------+-------+
| 101|   mydb|DBUPGRADE|EXECUTING|RUNNING|  15:19:20| 8s ago|Running|
+----+-------+---------+---------+-------+----------+-------+-------+
Total jobs 1

upg> Job 101 completed
------------------- Final Summary --------------------
Number of databases            [ 1 ]

Jobs finished                  [1]
Jobs failed                    [0]
Jobs restored                  [0]
Jobs pending                   [0]



Please check the summary report at:
/sw/oracle/admin/mydb/sql/cfgtoollogs/upgrade/auto/status/status.html
/sw/oracle/admin/mydb/sql/cfgtoollogs/upgrade/auto/status/status.log

Looking at the log file /sw/oracle/admin/mydb/sql/cfgtoollogs/upgrade/auto/status/status.log I see that many of the stages were very quick to return:

==========================================
          Autoupgrade Summary Report
==========================================
[Date]           Mon Jun 19 15:28:58 CEST 2023
[Number of Jobs] 1
==========================================
[Job ID] 101
==========================================
[DB Name]                mydb
[Version Before Upgrade] 19.19.0.0.0
[Version After Upgrade]  19.19.0.0.0
------------------------------------------
[Stage Name]    PREUPGRADE
[Status]        SUCCESS
[Start Time]    2023-06-19 15:19:20
[Duration]      0:00:00
[Log Directory] /sw/oracle/cfgtoollogs/autoupgrade/mydb/mydb/101/preupgrade
------------------------------------------
[Stage Name]    DRAIN
[Status]        SUCCESS
[Start Time]    2023-06-19 15:19:21
[Duration]      0:00:00
[Log Directory] /sw/oracle/cfgtoollogs/autoupgrade/mydb/mydb/101/drain
------------------------------------------
[Stage Name]    NONCDBTOPDB
[Status]        SUCCESS
[Start Time]    2023-06-19 15:19:21
[Duration]      0:08:51
[Log Directory] /sw/oracle/cfgtoollogs/autoupgrade/mydb/mydb/101/noncdbtopdb
------------------------------------------
[Stage Name]    DBUPGRADE
[Status]        SUCCESS
[Start Time]    2023-06-19 15:28:12
[Duration]      0:00:42
[Log Directory] /sw/oracle/cfgtoollogs/autoupgrade/mydb/mydb/101/dbupgrade
------------------------------------------
[Stage Name]    POSTUPGRADE
[Status]        SUCCESS
[Start Time]    2023-06-19 15:28:57
[Duration]      0:00:00
[Log Directory] /sw/oracle/cfgtoollogs/autoupgrade/mydb/mydb/101/postupgrade
------------------------------------------
[Stage Name]    SYSUPDATES
[Status]        SUCCESS
[Start Time]    2023-06-19 15:28:58
[Duration]
[Log Directory] /sw/oracle/cfgtoollogs/autoupgrade/mydb/mydb/101/sysupdates
------------------------------------------
Summary: /sw/oracle/cfgtoollogs/autoupgrade/mydb/mydb/101/dbupgrade/datapatch_summary.log

If I move into the /sw/oracle/cfgtoollogs/autoupgrade/mydb/mydb/101/noncdbtopdb folder, I can see

ls -altrh
total 480K
-rw------- 1 oracle dba 6.9K Jun 19 15:20 mydb-MYDB.xml
-rw------- 1 oracle dba 1.7K Jun 19 15:20 createpdb_mydb_MYDB.log
-rw------- 1 oracle dba 323K Jun 19 15:28 noncdbtopdb_mydb_MYDB.log
-rw------- 1 oracle dba 127K Jun 19 15:28 noncdb_to_pdb_mydb.log
drwx------ 2 oracle dba 4.0K Jun 19 15:28 .
drwx------ 8 oracle dba 4.0K Jun 19 15:28 ..

This stage executes the command

create pluggable database "MYDB" using '/sw/oracle/cfgtoollogs/autoupgrade/mydb/mydb/101/noncdbtopdb/mydb-MYDB.xml' NOCOPY tempfile reuse

to perform the actual plug-in operation of the old non-cdb database, so that it becomes a PDB. This was the only stage that took a bit of time to complete, almost 9 minutes.

There are numerous ways to migrate from the non-container architecture to multitenant architecture, this is just one of them.

Things to notice:

* My non-cdb database was called "mydb" - notice the lower case of the name. During the process, this was ignored and the database was renamed to MYDB when it was recreated as a PDB. There seem to be no way to change this behaviour.

Credit to Tim Hall for the usual excellent work in his blog post about the same topic, but also involving upgrading to a higher version of the Oracle software.

The official Oracle 19c documenation about AutoUpgrade Configuration File for Non-CDB Upgrades on the Same System is essential reading

Lots of good stuff on the Mike Dietrich blog

How to list the content of a folder and get total size summary of the parent folder at the end

To list the size of all folders and get a summery at the end, use the --max-depth=1 switch with the du command.

In the listing below, I am following the examples of a recent blog post by Mike Dietrich, to measure the size of the subfolders in the .patch_storage folder, which is maintained by OPatch in case the DBA needs to rollback the patches later.

cd $ORACLE_HOME/.patch_storage

du -h --max-depth=1
100K    ./29585399_Apr_9_2019_19_12_47
284K    ./NApply
4.0K    ./oracle-home-1681903491076105
434M    ./34786990_Dec_6_2022_13_24_50
20K     ./NRollback
436M    ./35050341_Mar_17_2023_04_11_10
2.0G    ./35042068_Apr_6_2023_15_25_04
91M     ./backup_delete_inactive
2.9G    .

How to use sqlcl with / as sysdba

Creds to the findings of Rodrigo Jorge:
Make sure the environment variable LD_LIBRARY_PATH is set, and if so, includes $ORACLE_HOME/lib:

oracle@testserver01.oric.no:[testdb01]# export LD_LIBRARY_PATH=$ORACLE_HOME/lib

The following files are important for sqlcl to work:

cd $ORACLE_HOME/lib

ls -la *ocijdbc19*
-rw-r--r-- 1 oracle dba  166082 Apr  5  2019 libocijdbc19.a
-rw-r--r-- 1 oracle dba 1812344 Apr  6 17:24 libocijdbc19_g.so
-rw-r--r-- 1 oracle dba  153648 Apr  6 17:24 libocijdbc19.so

Connect with sqlcl as sysdba, just the way you used to with sqlplus:

 sql / as sysdba


SQLcl: Release 21.4 Production on Tue Jun 06 08:46:27 2023

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

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.19.0.0.0