How to write custom messages to the alert log file

exec sys.dbms_system.ksdwrt(2,'Your message here')

The first argument can be either

1 -> Write to the trace file

2 -> Write to the database alert log file

3 -> Write to both of the above files

Syntax for applying and removing a patch on the WLS server Home in an EBS 12.2 installation

The bsu (or "Smart update") utility:

cd $MW_HOME/utils/bsu
bsu.sh -version
Oracle Smart Update. Version: 3.3.0.0

Here's an example on how to install a patch:

./bsu.sh -remove -patch_download_dir=$MW_HOME/utils/bsu/cache_dir -patchlist=1LRI -prod_dir=$MW_HOME/wlserver_10.3 -verbose -log=install_1LRI.log

Here's an example on how to remove a patch:

./bsu.sh -install -patch_download_dir=$MW_HOME/utils/bsu/cache_dir -patchlist=E7HI -prod_dir=$MW_HOME/wlserver_10.3 -verbose -log=install_E7HI.log

Script sniplet to differentiate between cdb and pdb environment settings in a shell script

This little sniplet could be used if you find yourself in a situation where you need to differentiate between the cdb environment and the pdb environment on a server using multitenant setup.

I am setting a commonly used alias for the usage of sqlplus called "sql" depending on the ORACLE_SID being set to the cdb or the pdb.

It differs slightly since I you can only do a bequath session when connecting to the former, and not the latter:

When connecting to the pdb as sysdba, you need to go through the listener, and therefore supply the password.

When connecting to the cdb you can do a so called bequeath session and connect directly to the oracle instance through IPC.

read -p 'Will you be administrating the cdb or the PDB? ' container
case $container in
        CDB|cdb)
        . $ORACLE_HOME/cdb_settings.env;
        alias sql='sqlplus / as sysdba';;
        PDB1|pdb1)
        . $ORACLE_HOME/pdb1.env;
        alias sql='sqlplus sys@pdb1 as sysdba';;
        *) echo "You must select cdb or pdb1";
         exit 1;;
esac

How to generate a cold backup script for a database

Probably many ways to to this, but here is how I generated a simple file that after being made executable will copy all files to a specific folder. The database must be shut down before running the script, so in other words, this will be a good, old-fashioned cold backup!

set lines 200
set pages 0
set trimspool on
set heading off
set echo off
set feedback off
set verify off

spool cp_files.sh


SELECT    'cp '
       || a.file_name
       || ' /u01/oracle/cold_backup/'
       || (SELECT TRIM (SUBSTR (b.file_name,
                                (  INSTR (b.file_name,
                                          '/',
                                          -1,
                                          1)
                                 + 1)))
             FROM cdb_data_files b
            WHERE a.file_name = b.file_name)
  FROM cdb_data_files a
UNION
SELECT    'cp '
       || a.MEMBER
       || ' /u01/oracle/cold_backup/'
       || (SELECT TRIM (SUBSTR (b.MEMBER,
                                (  INSTR (b.MEMBER,
                                          '/',
                                          -1,
                                          1)
                                 + 1)))
             FROM v$logfile b
            WHERE a.MEMBER = b.MEMBER)
FROM v$logfile A
UNION
SELECT    'cp '
       || a.name
       || ' /u01/oracle/cold_backup/'
       || (SELECT TRIM (SUBSTR (b.name,
                                (  INSTR (b.name,
                                          '/',
                                          -1,
                                          1)
                                 + 1)))
             FROM v$tempfile b
            WHERE a.name = b.name)
  FROM v$tempfile a
UNION
SELECT    'cp '
       || a.name
       || ' /u01/oracle/cold_backup/'
       || (SELECT TRIM (SUBSTR (b.name,
                                (  INSTR (b.name,
                                          '/',
                                          -1,
                                          1)
                                 + 1)))
             FROM v$controlfile b
            WHERE a.name = b.name)
  FROM v$controlfile a;

How to use strace to figure out what files are being accessed by a shell script

I had a situation where an ebs-supplied script adstrtal.sh would not start - it kept throwing error

ORA-12541: TNS:no listener

Although $TNS_ADMIN was correctly set, and sqlplus and tnsping would confirm that the database was open and the listener was up, accepting connections on the desired port.

The sqlnet.log file created in the same directory from which I executed adstrtal.sh displayed the connection being attempted:

Fatal NI connect error 12541, connecting to:
 (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=EBS32.oric.no)(CID=(PROGRAM=sqlplus)(HOST=oric-ebsapp-utv.oric.no)(USER=ebs)))
 (ADDRESS=(PROTOCOL=TCP)(HOST=162.20.5.225)(PORT=1521)))

We are not using default port 1521, but a different port.

I then used strace to find the source of the error, like this:

strace -f -o /tmp/strace.out ./adstrtal.sh apps/****

When going through the /tmp/strace.out file, I was pointed in the right direction:

openat(AT_FDCWD, "$INST_TOP/ora/10.1.3/network/admin/EBS32_oric-ebsapp-utv_ifile.ora", O_RDONLY|O_LARGEFILE) = -1 ENOENT (No such file or directory)

Turns out that adstrtal.sh was looking for a tnsnames.ora entry not in the tnsnames.ora in $TNS_ADMIN ($INST_TOP/ora/10.1.2/network/admin) but rather in $INST_TOP/ora/10.1.3/network/admin.

As soon as I had added the correct tnsnames.ora entry, the adstrtall.sh script worked.

Solution to ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

I had a situation where the error

ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

was thrown when connecting to a PDB.

The PDB was up and the listener runnning and serving the service_name which I wanted to connect to:

Solution was to reverse the order of directory_path in sqlnet.ora from

NAMES.DIRECTORY_PATH= (EZCONNECT,TNSNAMES)

to

NAMES.DIRECTORY_PATH= (TNSNAMES,EZCONNECT )

What are EBS snapshots?

I found this info in the document Oracle® Applications Maintenance Utilities Release 12.1 Part No. E13676-02 There are two types of snapshots: APPL_TOP snapshotsand global snapshots.

An APPL_TOP snapshot lists patches and versions of files in the APPL_TOP.

A global snapshot lists patches and latest versions of files in the entire Applications system (that is, across all APPL_TOPs).

Both APPL_TOP snapshots and global snapshots may be either current view snapshots or named view snapshots.

A current view snapshot is created once and updated when appropriate to maintain a consistent view.

A partial view snapshot allows you to synchronize only selected files from a current view.

A named view snapshot is a copy of the current view snapshot at a particular time (not necessarily the latest current view snapshot), and is not updated.

Generation of "alter database rename file" scripts

I have written several blog posts where I generate "alter database rename file" statements using the familiar syntax

select 'alter database move datafile ''' || file_name || ''' TO ''' || replace(file_name,'old_sid','new_sid') || ''';'

Recently, an experienced co-worker showed me another version of the script which is, in my opinion, much simpler:

select 'alter database move datafile ' ||chr(39) || file_name || chr(39) || ' TO ' || chr(39) || replace(file_name,'old_sid','new_sid') || chr(39)|| ';'
from dba_data_files;

By referring to chr(39) instead of masking the char ' with the same character, your script becomes simpler to read and less error-prone. This will be particulary important as your scripts get more complex.

The same method can of course be applied when generating scripts for moving table partitions, indexes etc.

How to see the number of huge pages configured on a Linux server

On a RHEL server, to see if hugespages have been configured, you can use

cat /proc/meminfo|grep -iE 'tables|huge'

PageTables:        33368 kB
AnonHugePages:         0 kB
ShmemHugePages:        0 kB
FileHugePages:         0 kB
HugePages_Total:    5121
HugePages_Free:        9
HugePages_Rsvd:        9
HugePages_Surp:        0
Hugepagesize:       2048 kB
Hugetlb:        10487808 kB

From the above, we can see that the total amount of huge pages is 5121.
Each page is 2M in size, as can be seen from Hugepagesize.
The total size of hugepages amounts to 10242M, or 10G.

Frank Pachot has written a really neat pice of code to format the output from sysctl to display huge pages usage on a Linux server.

awk '/Hugepagesize:/{p=$2} / 0 /{next} / kB$/{v[sprintf("%9d GB %-s",int($2/1024/1024),$0)]=$2;next} {h[$0]=$2} /HugePages_Total/{hpt=$2} /HugePages_Free/{hpf=$2} {h["HugePages Used (Total-Free)"]=hpt-hpf} END{for(k in v) print sprintf("%-60s %10d",k,v[k]/p); for (k in h) print sprintf("%9d GB %-s",p*h[k]/1024/1024,k)}' /proc/meminfo|sort -nr|grep --color=auto -iE "^|( HugePage)[^:]*"

The output is much easier to draw conclusions from, and it gives quite a lof of other useful information about your system, too:

32767 GB VmallocTotal:   34359738367 kB                    16777215
24 GB CommitLimit:    26025928 kB                          12707
19 GB SwapTotal:      20971516 kB                          10239
19 GB SwapFree:       20969176 kB                          10238
19 GB MemTotal:       20596632 kB                          10056
12 GB DirectMap1G:    12582912 kB                           6144
10 GB Hugetlb:        10487808 kB                           5121
10 GB HugePages_Total:    5121
9 GB HugePages Used (Total-Free)
9 GB DirectMap2M:    10082304 kB                           4923
7 GB MemAvailable:    8173656 kB                           3991
6 GB Cached:          6329396 kB                           3090
5 GB Inactive:        5862704 kB                           2862
4 GB Inactive(file):  4679556 kB                           2284
2 GB Committed_AS:    2912096 kB                           1421
1 GB MemFree:         1835428 kB                            896
1 GB Inactive(anon):  1183148 kB                            577
1 GB AnonPages:       1056944 kB                            516
1 GB Active(file):    1502496 kB                            733
1 GB Active:          1514284 kB                            739
0 GB VmallocUsed:       30708 kB                             14
0 GB Unevictable:       12376 kB                              6
0 GB SwapCached:          432 kB                              0
0 GB SUnreclaim:       101008 kB                             49
0 GB SReclaimable:     375752 kB                            183
0 GB Slab:             476760 kB                            232
0 GB Shmem:            139612 kB                             68
0 GB Percpu:             4960 kB                              2
0 GB PageTables:        33448 kB                             16
0 GB Mlocked:           12376 kB                              6
0 GB Mapped:           322284 kB                            157
0 GB KReclaimable:     375752 kB                            183
0 GB KernelStack:        6080 kB                              2
0 GB HugePages_Surp:        0
0 GB HugePages_Rsvd:        9
0 GB Hugepagesize:       2048 kB                              1
0 GB HugePages_Free:        9
0 GB Dirty:              1272 kB                              0
0 GB DirectMap4k:      403328 kB                            196
0 GB Buffers:            2852 kB                              1
0 GB Active(anon):      11788 kB                              5

How to create a template that includes a seed database using dbca

This is how you can create a new template which will include the database "mydb01" as a seed database for future installations:

dbca -silent -createCloneTemplate -sourceDB mydb01 -templateName minimal_nonmt_seed -maintainFileLocations true -sysDBAPassword mysecretpassword -sysDBAUserName sys -rmanParallelism 2 -dataFileBackup true

What to do if your flash recovery area is filled with "foreign archive logs"?

One of my databases came to a halt when the flash recovery area filled completely.

It wasn't a problem with space preassure, but merely a condition that made the flash recovery area *seem* full, when it really wasn't.

To clear the archiver stuck condition is easy enough, simply increase the value of db_recovery_file_dest_size, but how to fix the source of the problem?

My flash recovery area was, at the time, filled up with foreign archive logs:

SYS@CDB$ROOT SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE               PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE                             0                         0               0          0
REDO LOG                                 0                         0               0          0
ARCHIVED LOG                         14.04                         0             235          0
BACKUP PIECE                           .06                         0              44          0
IMAGE COPY                               0                         0               0          0
FLASHBACK LOG                            0                         0               0          0
FOREIGN ARCHIVED LOG                  85.8                         0            1558          0
AUXILIARY DATAFILE COPY                  0                         0               0          0

8 rows selected.

To fix this, log on to the database using rman:

rman target / nocatalog

Here you can list the foreign archivelogs:

list foreign archivelogs all;

They were all from may 2023, and they have ended up here since the database was cloned using storage snapshots.

To clear them out of the flash recovery area, you need to first crosscheck them:

RMAN> crosscheck foreign archivelog all;

Then, delete them:

RMAN> delete noprompt foreign archivelog all ;

The crosscheck is important. Without it, Oracle cannot delete the entries from the controlfile, which means that tha v$flash_recovery_area will still be reported as full.
The flash recovery area is not full anymore:

SYS@CDB$ROOT SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE               PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES     CON_ID
----------------------- ------------------ ------------------------- --------------- ----------
CONTROL FILE                             0                         0               0          0
REDO LOG                                 0                         0               0          0
ARCHIVED LOG                         18.21                         0             280          0
BACKUP PIECE                           .06                         0              44          0
IMAGE COPY                               0                         0               0          0
FLASHBACK LOG                            0                         0               0          0
FOREIGN ARCHIVED LOG                     0                         0               0          0
AUXILIARY DATAFILE COPY                  0                         0               0          0

8 rows selected.

Documented in Doc ID 1617965.1 "What commands may be used to remove foreign archivelog files?" at Oracle Support.

Using the dbca General Purpose template and the resulting database

If you execute the dbca GUI and choose the simple configuration, you will be using the "General Purpose" template, which gives you an out-of-the-box database based on a seed database. There is not much you can do to adjust the resulting database to your needs when you select this option.

But, you do have the option to save the response file before the dbca GUI starts the actual creation process. Using the response file instead of the GUI, you have a couple of options to override the use of OMF and the naming of the subdirectories used for data files:

The reponse file, after peeling off the comments, contains the following:

responseFileVersion=/oracle/assistants/rspfmt_dbca_response_schema_v12.2.0
gdbName=mydb.skead.no
sid=mydb
databaseConfigType=SI
policyManaged=false
createServerPool=false
force=false
createAsContainerDatabase=false
numberOfPDBs=0
useLocalUndoForPDBs=true
templateName=/u01/oracle/product/19c/assistants/dbca/templates/General_Purpose.dbc
sysPassword=
systemPassword=
runCVUChecks=FALSE
dvConfiguration=false
olsConfiguration=false
datafileJarLocation={ORACLE_HOME}/assistants/dbca/templates/
datafileDestination=/u01/oradata/{DB_UNIQUE_NAME}/
recoveryAreaDestination=/u04/flash_recovery_area/{DB_UNIQUE_NAME}
storageType=FS
characterSet=AL32UTF8
registerWithDirService=false
listeners=LISTENER
skipListenerRegistration=false
variables=ORACLE_BASE_HOME=/u01/oracle/product/19c,DB_UNIQUE_NAME=mydb,ORACLE_BASE=/u01/oracle,PDB_NAME=,DB_NAME=mydb,ORACLE_HOME=/u01/oracle/product/19c,SID=mydb
initParams=undo_tablespace=UNDOTBS1,sga_target=4718MB,db_block_size=8KB,dispatchers=(PROTOCOL=TCP) (SERVICE={SID}XDB),diagnostic_dest={ORACLE_BASE},remote_login_passwordfile=EXCLUSIVE,db_create_file_dest=/data2/oradata/{DB_UNIQUE_NAME}/,audit_file_dest={ORACLE_BASE}/admin/{DB_UNIQUE_NAME}/adump,processes=320,pga_aggregate_target=1573MB,local_listener=LISTENER_MYDB,db_recovery_file_dest_size=8256MB,open_cursors=300,db_domain=skead.no,compatible=19.0.0,db_name=mydb,db_recovery_file_dest=/data4/flash_recovery_area/{DB_UNIQUE_NAME},audit_trail=db
sampleSchema=false
memoryPercentage=40
databaseType=MULTIPURPOSE
automaticMemoryManagement=false
totalMemory=0

Notice how the dbca withholds the value you supply for SYS and SYSTEM passwords, while it prints out the value for datafileDestination and recoveryAreaDestination in the response file for you.

This response file can be used to execute dbca silently, like this:

dbca -silent -createDatabase -responsefile youresponsefilename.rsp

This will create the database MYDB with OMF naming:

select file_name from dba_data_files:

/data2/oradata/MYDB/datafile/o1_mf_users__vbctspxs_.dbf
/data2/oradata/MYDB/datafile/o1_mf_undotbs1__vbcsmzql_.dbf
/data2/oradata/MYDB/datafile/o1_mf_system__vbb9h6kc_.dbf
/data2/oradata/MYDB/datafile/o1_mf_sysaux__vbcc2o9j_.dbf

select member from v$logfile

MEMBER
--------------------------------------------------
/data2/oradata/MYDB/onlinelog/o1_mf_3__vbdwcyb1_.log
/data4/fra/MYDB/onlinelog/o1_mf_3__vbdwxrdx_.log
/data2/oradata/MYDB/onlinelog/o1_mf_2__vbdwbqv8_.log
/data4/fra/MYDB/onlinelog/o1_mf_2__vbdwxb5f_.log
/data2/oradata/MYDB/onlinelog/o1_mf_1__vbdw9lqy_.log
/data4/fra/MYDB/onlinelog/o1_mf_1__vbdwtx1r_.log

This is because the response file contains the value

initParams=db_create_file_dest=/data2/oradata/{DB_UNIQUE_NAME}/

If dbca is run again without this value, the files will not be of type OMF anymore:

SYS@mydb>select member from v$logfile;

MEMBER
----------------------------------------
/u01/oradata/MYDB/redo03.log
/u01/oradata/MYDB/redo02.log
/u01/oradata/MYDB/redo01.log

SYS@mydb>select member from v$logfile;

MEMBER
----------------------------------------
/u01/oradata/MYDB/redo03.log
/u01/oradata/MYDB/redo02.log
/u01/oradata/MYDB/redo01.log

If you alter the directions

datafileDestination=/u01/oradata/{DB_UNIQUE_NAME}/
recoveryAreaDestination=/data4/flash_recovery_area/{DB_UNIQUE_NAME}

to

datafileDestination=/u01/oradata/mydb
recoveryAreaDestination=/data4/flash_recovery_area/mydb

you will also be able to override the fact that dbca creates the subdirectories in capital letters by default.

What are dbca templates and how do they work?

What are dbca templates? 

Database Configuration Assistant (DBCA) templates are XML files that contain information required to create a database.

What are templates used for? 

Templates are used in DBCA to create new databases and duplicate existing databases. 

What information does a template contain?

The information in templates includes database options, initialization parameters, and storage attributes (for data files, tablespaces, control files, and online redo log files). 

Are there different types of templates?

Yes:

Type	File extension	Includes Data Files	Database Structure	Configurable?
Seed	.dbc	Yes	contains both the structure and the physical data files of an existing database, referred to as the seed database. The data files for the seed database are stored in compressed RMAN backup format in a file with a .dfb extension. The seed database control file is stored in a file with .ctl extension The .dbc file contains the location of the seed database data files and contains the source database name used to mount the control file.	No
nonseed	.dbt	No	This type of template is used to create a new database. It contains the characteristics of the database to be created. Nonseed templates are more flexible than their seed counterparts because all data files and online redo log files are created to your specification, and names, sizes, and other attributes can be changed as required.	Yes

Why use templates? 

Templates can be used just like scripts, but they are more powerful than scripts because you have the option of duplicating a database. Duplication saves time because you copy the files of an existing database, referred to as a seed database, to the correct locations.

When using templates, beware of the following:

1. Database options (for example, Data Vault or Spatial) can be configured or modified only for a custom database or nonseed template (.dbt file).
2. You cannot modify database options for a seed template (includes data file backups).
3. Using a seed template is a much quicker operation, than using a non-seed template, but less flexible.

What templates are delivered with a standard Oracle 19c software installation?

Upon completion of an Oracle 19c software installation, you will find the following files in the directory $ORACLE_HOME/assistants/dbca/templates:

New_Database.dbt

Seed_Database.dfb

Seed_Database.ctl

Data_Warehouse.dbc

General_Purpose.dbc

pdbseed.xml

They are used by the different templates:

Template	Workload	Files used
Data warehouse	Users perform numerous, complex queries that process large volumes of data.	Seed_Database.dbf
General Purpose or Transaction processing	Many concurrent users perform numerous transactions that require rapid access to data.	Seed_Database.dbf
Custom database	Allows for maximum flexibility in defining a database; you can change any of the settings for the database being created.	New_Database.dbt

Both the Data_Warehouse.dbc and the General_Purpose.dbc are pointing to the Seed_Database.dbf when they are used, which means they will be create a database for you quickly but you will have to accept the options and standard given by the template


Documentation:

2.3.5 Managing Templates with DBCA

How to remove the Oracle Workspace Manager component from an Oracle database

I have installed a database through dbca, unchecking all the options boxes when going through the setup wizard:

Upon completion, there is still a component left that I do not want to have installed: the Oracle Workspace Manager.

It is registred in the database's registry:

SYS@orcl>SQL>SELECT COMP_NAME,COMP_ID,VERSION,STATUS FROM DBA_REGISTRY;

COMP_NAME                                COMP_ID                        VERSION                        STATUS
---------------------------------------- ------------------------------ ------------------------------ ----------------
Oracle Database Catalog Views            CATALOG                        19.0.0.0.0                     VALID
Oracle Database Packages and Types       CATPROC                        19.0.0.0.0                     VALID
Oracle Real Application Clusters         RAC                            19.0.0.0.0                     OPTION OFF
Oracle XML Database                      XDB                            19.0.0.0.0                     VALID
Oracle Workspace Manager                 OWM                            19.0.0.0.0                     VALID

To remove the OWM component:

SYS@orcl>SQL> @?/rdbms/admin/owmuinst.plb

Session altered.

PL/SQL procedure executed.

PL/SQL procedure executed.

Session altered.

Option is now deinstalled:

SYS@orcl>SQL>SELECT COMP_NAME,COMP_ID,VERSION,STATUS FROM DBA_REGISTRY;

COMP_NAME                                COMP_ID                        VERSION                        STATUS
---------------------------------------- ------------------------------ ------------------------------ ------------------
Oracle Database Catalog Views            CATALOG                        19.0.0.0.0                     VALID
Oracle Database Packages and Types       CATPROC                        19.0.0.0.0                     VALID
Oracle Real Application Clusters         RAC                            19.0.0.0.0                     OPTION OFF
Oracle XML Database                      XDB                            19.0.0.0.0                     VALID

What exactly is the OWM ?

In a nutshell, it's a version tool for data within a schema. It has been around since Oracle 9i

See documentation here

Particulary the whitepaper

Check out Tim Hall's article on oracle-base.com about the subject.

How to install an extension in a postgreSQL database

Are the desired extensions available in our installation?

[postgres@myserver.no ~]$ echo "SELECT * FROM pg_available_extensions;" | psql | egrep 'cube|earthdistance';
 cube               | 1.5             |                   | data type for multidimensional cubes
 earthdistance      | 1.1             |                   | calculate great-circle distances on the surface of the Earth

Yes they are.

Source : https://www.postgresql.org/docs/current/view-pg-available-extensions.html

Connect to the relevant database and create the extensions in the schema you want them:

postgres=# \connect mydb1
You are now connected to database "mydb1" as user "postgres".
mydb1=# create extension if not exists cube with schema myschema1 cascade;
CREATE EXTENSION
mydb1=# create extension if not exists earthdistance  with schema myschema1 cascade;
CREATE EXTENSION
mydb1=# exit

Check if they have indeed been installed:

[postgres@myserver.no ~]$ psql mydb01
psql (15.4)
Type "help" for help.

sit1=# \dx
                                                List of installed extensions
        Name        | Version |       Schema        |                              Description
--------------------+---------+---------------------+------------------------------------------------------------------------
 cube               | 1.5     | myschema1           | data type for multidimensional cubes
 earthdistance      | 1.1     | myschema1           | calculate great-circle distances on the surface of the Earth
 pg_qualstats       | 2.0.4   | public              | An extension collecting statistics about quals
 pg_stat_statements | 1.10    | public              | track planning and execution statistics of all SQL statements executed
 plpgsql            | 1.0     | pg_catalog          | PL/pgSQL procedural language
(5 rows)

Create extension is documented here

Solution for [WARNING] ORA-01081: cannot start already-running ORACLE - shut it down first when running dbca

I received the following error multiple times, when executing dbca like below:

dbca -createDatabase -responsefile ./cdb.rsp -silent
 2023-09-14 15:05:45.435 CEST ] Prepare for db operation
DBCA_PROGRESS : 10%
[ 2023-09-14 15:05:45.505 CEST ] Copying database files
DBCA_PROGRESS : 12%
[ 2023-09-14 15:05:47.250 CEST ] [WARNING] ORA-01081: cannot start already-running ORACLE - shut it down first

DBCA_PROGRESS : 40%
DBCA_PROGRESS : 100%
[ 2023-09-14 15:05:47.616 CEST ] [FATAL] Recovery Manager failed to restore datafiles. Refer logs for details.
DBCA_PROGRESS : 10%
DBCA_PROGRESS : 0%

At first I thought it was because I had forgotten to terminate some processes owned by oracle. And I also made sure that /etc/oratab was empty before I started.

Reason:

Turns out, there were old memory segments left behind that dbca considers a part of a running instance. Even if

ps -fu oracle

gave no processes in the list.

Solution:

su - oracle
sqlplus / as sysdba
shutdown abort

Given that you've set ORACLE_SID to the same value you used in your response file, this will properly shut down your instance, and clean up the memory segments left behind from the previous run.

How to delete a pluggable database with dbca using the command line

dbca -silent -deletePluggableDatabase \
-sourceDB cdb \
-pdbName pdb3 \

How to create a pluggable database with dbca using the command line

In the following code snippet, I am creating the pluggable database "pdb3" as a copy of the already existing pluggable database "pdb1":

dbca -silent -createPluggableDatabase \
-sourceDB cdb.skead.no \
-pdbName pdb3 \
-sourcePDB pdb1 \
-createPDBFrom PDB \
-pdbDatafileDestination "/dbfiles/oradata/cdb/pdb3/" \
-pdbAdminPassword mysecretpassword1 \
-sysPassword mysecretpassword2 \
-systemPassword mysecretpassword3 \

If you want to use the SEED database as source instead of an already existing database, remove the directive

-sourcePDB pdb1 \

and use this directive instead:

-createPDBFrom DEFAULT \

Source: Oracle 19c documentation

Script for creating a multitenant database with dbca

Prerequisites:

You have a valid Oracle 19c installation in a server

You have set your Oracle environment already, so that your PATH, ORACLE_SID etc already exists when you execute your script

The script called run_dbca.sh:

#!/bin/bash
start=$(date +%s.%N)
export GLOGFILE=run_dbca.log
touch ${GLOGFILE}
chmod 666 ${GLOGFILE}
exec 1> ${GLOGFILE} 2>&1
echo "Now running run_dbca.sh"
echo "Resetting oratab..."
echo '' > /etc/oratab
if [ $? -ne 0 ]; then
 echo "Could not erase oratab. Exit."
 exit 1
else
 echo "oratab erased. Continuing..."
fi
export instanceAlive=`ps -ef| grep pmon | grep -v grep |awk '{ print $8}' | cut -f 3 -d"_"`

if [ ! -z ${instanceAlive} ]; then
 echo "Instance for database $ORACLE_SID is already running. Shut it down first"
 exit 1
fi
dbca -createDatabase -responsefile ./${ORACLE_SID}.rsp -silent
echo "Finished running run_dbca.sh"
dur=$(echo "$(date +%s.%N) - $start" | bc)
printf "Total execution time for run_dbca.sh: %.6f seconds\n" $dur
exit 0

The response file can be seen below. I saved it in a file called cdb.rsp and put it in the same directory.

I commented out most instructions, as they are not needed.

The original template db_install.rsp is delivered by default with your oracle installation and resides in your $ORACLE_HOME/install/response.

#-------------------------------------------------------------------------------
# Do not change the responseFileVersion diretive!
#-------------------------------------------------------------------------------
responseFileVersion=/oracle/assistants/rspfmt_dbca_response_schema_v19.0.0
gdbName=cdb.oric.no
sid=cdb
createAsContainerDatabase=true
numberOfPDBs=1
pdbName=pdb1
useLocalUndoForPDBs=true
pdbAdminPassword=mysecretpassword
templateName=New_Database.dbt
sysPassword=mysyspassword
systemPassword=mysystempassword
datafileDestination=/data01/oradata/cdb
recoveryAreaDestination=/data02/fra
characterSet=AL32UTF8
initParams=max_pdbs=3,db_create_file_dest_size=1024GB
memoryPercentage=75
automaticMemoryManagement=false
enableArchive=true
redoLogFileSize=2048
dbOptions=JSERVER:false,DV:false,ORACLE_TEXT:false,IMEDIA:false,CWMLITE:false,SPATIAL:false,OMS:false,APEX:false

Execute the script like this:

./run_dbca.sh

Follow the progression of the creation in the log run_dbca.log:

tail -f run_dbca.log

Sources:

Oracle 19c documentation: About creating a database with dbca

Oracle 19c documentation: Managing templates with dbca

Oracle-Base.com: Multitenant : Create and Configure a Pluggable Database (PDB) in Oracle Database 12c Release 1 (12.1) - still a relevant and good source.

Oracle-base.com: a dbca template

Oracle-base.com: Database Configuration Assistant (DBCA) : Creating Databases in Silent Mode

Solution for [DBT-10317] Specified SID Name (SID) already exists when running dbca

When running dbca, for example like this:

dbca -createDatabase -responsefile ./cdb.rsp -silent
[FATAL] [DBT-10317] Specified SID Name (cdb) already exists.
   ACTION: Specify a different SID Name that does not already exist.

There are two potential reasons for the error:

1. you have a /etc/oratab which has an entry, for example:

cdb:/installedsw1/oracle/product/Ora19c:Y

2. your instance is running.

Solution to case 1: remove entry

Solution to case 2: shutdown the instance

Rerun dbca and it will now proceed as expected.

Syntax for switching over a container databases using data guard broker

Here I am switching over a container database called cdb, to the standby server which is running a container database with unique name cdb_stby1.

First, view the configuration:

DGMGRL> show configuration

Configuration - DGConfig1

  Protection Mode: MaxPerformance
  Members:
  cdb       - Primary database
    cdb_stby1 - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 84 seconds ago)

Perform the switchover:

DGMGRL> switchover to 'cdb_stby1';

Typical output from a successful switchover:

Performing switchover NOW, please wait...
Operation requires a connection to database "cdb_stby1"
Connecting ...
Connected to "cdb_stby1"
Connected as SYSDBA.
New primary database "cdb_stby1" is opening...
Operation requires start up of instance "cdb" on database "cdb"
Starting instance "cdb"...
Connected to an idle instance.
ORACLE instance started.
Connected to "cdb"
Database mounted.
Switchover succeeded, new primary is "cdb_stby1"

If I wish to switch back, make sure you log on to whichever server is running your primary database at the time.
(DO NOT use os authentication with dgmgrl / as sysdba, it will throw an error)

dgmgrl sys@cdb_stby1 as sysdba
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Thu Sep 7 15:27:27 2023
Version 19.20.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.
Password:
Connected to "cdb_stby1"
Connected as SYSDBA.
DGMGRL> show configuration;

Configuration - DGConfig1

  Protection Mode: MaxPerformance
  Members:
  cdb_stby1 - Primary database
    cdb       - Physical standby database

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 46 seconds ago)

DGMGRL> switchover to cdb
Performing switchover NOW, please wait...
Operation requires a connection to database "cdb"
Connecting ...
Connected to "cdb"
Connected as SYSDBA.
New primary database "cdb" is opening...
Operation requires start up of instance "cdb" on database "cdb_stby1"
Starting instance "cdb"...
Connected to an idle instance.
ORACLE instance started.
Connected to "cdb_stby1"
Database mounted.
Switchover succeeded, new primary is "cdb"

How to extract all datafile names without path using substr and instr functions

select trim(
            substr(file_name,
                (instr(file_name,'/', -1, 1) +1)
                )
               )  "file name"
from  dba_data_files;

Result:

file name
system01.dbf
sysaux01.dbf
undotbs01.dbf
users01.dbf
appl_data.dbf

More on how to display the current container in a multitenant database

My listener is listening for connections to a service called "myservice1", which runs out of the pluggable database "pdb1":

lsnrctl status
Service "pdb1" has 1 instance(s). <-- the default service for the pluggable database
  Instance "cdb", status READY, has 1 handler(s) for this service...

Service "myservice1" has 1 instance(s). <-- service defined by the DBA
  Instance "cdb", status READY, has 1 handler(s) for this service...

In the code snippet below, I am executing a script called disp_session.sql which will

connecting directly to a service served by the listener

switching schema within my session

The following will display the username, current schema, and the service my connction is connecting to:

alter session set current_schema=scott;
set lines 200
col service format a20
col container format a20
col username format a20
col "container ID" format a20
col schemaname format a20
select sys_context ('userenv','SERVICE_NAME')   service,
       sys_context ('userenv','CON_NAME')       container,
       sys_context ('userenv','CON_ID')         "container ID",
       sys_context ('userenv','CURRENT_USER')   username,
       sys_context ('userenv','CURRENT_SCHEMA') schemaname
from dual;

oracle@server1.oric.no:[cdb]# sqlplus system@myservice1 @disp_session.sql

Session altered.

SERVICE              CONTAINER            container ID         USERNAME             SCHEMANAME
-------------------- -------------------- -------------------- -------------------- --------------------
myservice1           pdb1                 4                    SYSTEM               SCOTT

Useful information when debugging client connections ;-)

How to exchange a line in a text file with a different one, when using double quotation marks in the text itself?

The following sed command will exchange the string

initParams=

with

initParams=undo_tablespace=UNDOTBS1,sga_target=9GB,db_block_size=32768BYTES,control_files=\"\/u02\/oradata\/{DB_UNIQUE_NAME}\/control01.ctl\"\,\"\/recovery\/fra\/{DB_UNIQUE_NAME}\/control01.ctl\"),db_recovery_file_dest=\/recovery\/fra\/{DB_UNIQUE_NAME}

in the file "myresponsefile.rsp":

sed -i "s/initParams=/initParams=undo_tablespace=UNDOTBS1,sga_target=9GB,db_block_size=32768BYTES,control_files=\"\/u02\/oradata\/{DB_UNIQUE_NAME}\/control01.ctl\"\,\"\/recovery\/fra\/{DB_UNIQUE_NAME}\/control01.ctl\"),db_recovery_file_dest=\/u04\/recovery\/{DB_UNIQUE_NAME} /" myreponse_file.rsp

Note that all double quotes (") and forward (/) slashes must be masked by the backslash charcater \ to be interpreted correctly.

Cause and solution to ORA-00058: DB_BLOCK_SIZE must be 8192 to mount this database (not 16384)

You attempt to execute dbca silently, using a response file, as shown below:

dbca -createDatabase -responsefile mydb01.rsp -silent

But it errors out with the error:

[WARNING] ORA-00058: DB_BLOCK_SIZE must be 8192 to mount this database (not 16384)

and the instance is left running, with an unmounted database.

Cause:

This means you are using a pre-defined template for dbca that includes datafiles.

When you do this, you cannot change the db_block_size. It is set to 8K, and carved in stone.

Solution:

Create your own, customized template and refer to that instead.

Remember that templates ending with the extension .dbc contains database files, so they will be significantly faster to use, than custom made templates ending with the extension .dbt - the latter will have to go through the "CREATE DATABASE" statements from scratch.

Templates that do not contain datafiles have the extension .dbt

What you can do in order to have your cake and eat it, too, is to first create a database with a non-confirming db_block_size, then create a dbca template based on this new database, including its datafiles.

I will see if I have time to write a blog post about how to do extactly this.

Simple PL/SQL script to alter parameter in database

At my current workplace, we use emcli for mass-updates of database parameters in groups of databases.

Below is a simple PL/SQL script that can be used to change parameter if needed. If the parameter is already set, nothing happens.

In my case, it was the parameter "recyclebin" that was needed to be switched from OFF to ON for some databases, but it could be any parameter.

Surely there are many ways to solve such a problem, this was how I solved it with very little effort :-)

set serveroutput on
set feedback off
set echo off
set verify off

DECLARE

 v_db_name v$database.name%%TYPE;
 v_rb v$system_parameter.name%%TYPE;


BEGIN
 execute immediate 'alter session set nls_language=''american''';
 select name
 into v_db_name
 from v$database;

 select value
 into v_rb
 from v$system_parameter
 where name = 'recyclebin';
 
--dbms_output.put_line('v_db_name: ' || v_db_name);
--dbms_output.put_line('v_rb:      ' || v_rb);

CASE
   WHEN v_rb = 'ON' THEN
    dbms_output.put_line('Database ' || v_db_name || ': recyclebin already enabled.');
   ELSE
    dbms_output.put_line('Database ' || v_db_name || ': recyclebin is currently disabled. Turning it on now...');
    execute immediate 'alter system set recyclebin=ON scope=spfile';
     dbms_output.put_line('Database ' || v_db_name || ' now has recyclebin=ON in spfile. Database must be bounced in in order to enable the setting');
   END CASE;
END;
/
exit

When we execute the script through emcli, the syntax comes to:

emcli execute_sql -sql="FILE" -iemcli execute_sql -sql="FILE" -input_file="FILE:/scripts/chk_and_alter_param.sql" -targets="TEST_DBS:composite"

How to trace the dbca tool

These notes are taken directly from MOS, and jotted down here so I have it readily available for the future.

To trace the DBCA, we need to perform the following.

1. Do a copy of the original dbca file in $ORACLE_HOME/bin. For example:

cp $ORACLE_HOME/bin/dbca $ORACLE_HOME/bin/dbca.ori

2. vi the dbca file in the $ORACLE_HOME/bin directory.

3. At the end of the file, look for the following line:

# Run DBCA
$JRE_DIR/bin/jre -DORACLE_HOME=$OH -DJDBC_PROTOCOL=thin -mx64m -classpath $CLASSPATH oracle.sysman.assistants.dbca.Dbca $ARGUMENTS

4. Add the following just before the -classpath in the '$JRE_DIR' line:

-DTRACING.ENABLED=true -DTRACING.LEVEL=2

5. At the end of the dbca file, the string should now look like this:

# Run DBCA
$JRE_DIR/bin/jre -DORACLE_HOME=$OH -DJDBC_PROTOCOL=thin -mx64m -DTRACING.ENABLED=true -DTRACING.LEVEL=2 -classpath $CLASSPATH oracle.sysman.assistants.dbca.Dbca $ARGUMENTS

6. To trace, run:

[host]/u01/home/usupport> dbca > dbca.out >

The output will be written to the dbca.out file.

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