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 -datafileDestination /u01/oracle/oradata/19c/assistants/dbca/templates

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.