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)