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

Can the recyclebin be turned off for a container database but remain on in a pluggable database?

Yes, the multitenant architecture allows this.

SQL>select name, value , ISPDB_MODIFIABLE from V$parameter where name = 'recyclebin';

NAME                           VALUE                ISPDB
------------------------------ -------------------- -----
recyclebin                     OFF                  TRUE

Switch container to a PDB:

SQL>alter session set container=veg7;

Session altered.

SQL>show con_name

CON_NAME
------------------------------
VEG7

SQL>select name, value , ISPDB_MODIFIABLE from V$parameter where name = 'recyclebin';

NAME                           VALUE                ISPDB
------------------------------ -------------------- -----
recyclebin                     ON                   TRUE

Remember that the pluggable database must be closed and reopened if you toggle the recyclebin on/off:

SQL>alter session set container=veg7;

Session altered.

SQL>alter system set recyclebin=on container=current scope=spfile;

System altered.

SQL>alter pluggable database close;

Pluggable database altered.

cdb>SQL>alter pluggable database open;

Pluggable database altered.

If you want to toggle the recyclebin on/off for the container database, the procedure is the same except that the entire instance will have to be bounced.

How to use RMAN DUPLICATE FROM ACTIVE DATABASE to clone an entire CDB and all PDBs to a remote host

In this post I will show how to clone an entire cdb with all PDBs using the DUPLICATE FROM ACTIVE DATABASE command, which has been around for many years. 

You can still reuse your existing scripts for these operations, with some minor tweaks, even after you have moved to the multitenant architechture.

My target server is called prodserver

My auxiliary server is called testserver

On prodserver, the container database is called "cdb" and for the time being, there is only one pluggable database running there, called "pdbprod", as shown below:

orasoft@prodserver:[pdbprod] sqlplus / as sysdba
SYS@CDB$ROOT SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 pdbprod                        READ WRITE NO

On my auxiliary server testserver, I also have a container database called "cdb", and a PDB called "pdbtest":

orasoft@testserver:[pdbtest] sqlplus / as sysdba
SYS@CDB$ROOT SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 pdbtest                        READ WRITE NO

First, I recommend to configure a wallet, to avoid exposing your passwords in scripts or in on the Linux shell prompt.

Add a global user that exists in both container database and make sure it has SYSDBA privileges.
In this example, I add a user called c##cloneadmin and use the alias "cdbprod" and "cdbtest" for both:

mkstore -wrl $TNS_ADMIN/wallet -createCredential cdbprod c##cloneadmin
mkstore -wrl $TNS_ADMIN/wallet -createCredential cdbaux c##cloneadmin

Add the connect descriptions to your tnsnames.ora file, on both source and target.
Make sure the passwordless connections work before you attempt cloning. Test like this:

rman 
connect target /@cdbprod
connect auxiliary /@cdbaux
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate auxiliary channel aux1 type disk;
allocate auxiliary channel aux2 type disk;
}
exit

Do not proceed until you connect successfully to both.

Create a minimal initcdb.ora file, containing only these two parameters:

*.db_name='cdb'
*.enable_pluggable_database=TRUE

Startup your auxiliary container database in nomount mode using the initcdb.ora file above:

sqlplus / as sysdba
startup nomount pfile=initcdb.ora

Create an RMAN script for your duplication, in my example I put it in a file called run_duplication.cmd:

connect target /@cdbprod
connect auxiliary /@cdbaux
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate auxiliary channel aux1 type disk;
allocate auxiliary channel aux2 type disk;
configure device type disk parallelism 2;
debug io;
DUPLICATE DATABASE TO cdb
FROM ACTIVE DATABASE
USING BACKUPSET
SPFILE
 set db_file_name_convert='pdbprod','pdbtest'
 set audit_file_dest='/oraadmin/oracle/admin/cdb/adump'
 set core_dump_dest='/oraadmin/oracle/diag/rdbms/cdb/cdb/cdump'
 set control_files='/dbfiles01/oradata/CDB/control01.ctl','/dbfiles02/fra/CDB/control02.ctl'
 set sga_target='33621540864'
 set sga_max_size='33621540864'
 set pga_aggregate_target='12773752832'
 set shared_pool_size='2751463424'
 set streams_pool_size='268435456'
 set service_names='cdb,pdbtest'
 set db_recovery_file_dest_size='3221225472000'
NOFILENAMECHECK 
;
debug off;
}
exit

Remember that the NOFILENAMECHECK is required when using the same data file names but on different hosts. Without it, RMAN throws the error below and aborts your script:

RMAN-05001: auxiliary file name /dbfiles02/oradata/CDB/undotbs01.dbf conflicts with a file used by the target database

Create a shell script that calls the RMAN script, for example run_duplication.sh:

rman cmdfile='run_duplication.cmd' debug trace='duplicate.trc' log='duplicate.log'

Make the script executable, and execute it. If the source database is very large, and you expect the duplication to take more than 2-3 hours, you may want execute it in the background:

chmod 755 run_duplication.sh
nohup ./run_duplication.sh &

When the duplication was finished, the cdb + pdbprod was opened on the remote server. The last lines of the logfile states:

RMAN-06400: database opened
RMAN-06162: sql statement: alter pluggable database all open
RMAN-03091: Finished Duplicate Db at 29-APR-23

Sources:
Duplicating a CDB from the Oracle 19c documentation.

How to get verbose output in a call to mailx

Use the -vvv flag to debug your call to mailx:

root # echo "Test message" | mailx -S smtp="smtp.oric.no" -vvv -s "$(hostname) is up" monitoring@oric.no