Find all utl_ packages in source code

Using grep utility

grep -rohP "(^|\W)\Kutl_\w*\." . | sort -u

Database CI pipeline idea

1. Prod release
2. Remote online clone of Prod database using this technic. See the link above to the list of restrictions, but most influenceable are: source database must be in archivelog mode and have local undo (otherwise it should be in read-only mode), same endianness in source and target. The database link should exists from target to source.
3. Keep created clone as golden source. Use PDB snapshot copy to create databases for testing
   – parameter clonedb=true should be set
   – filesystem should supports sparse files (ext4, NTFS, … are ok)
   – as spare files are used, whole process is very fast and don’t consumes a lot of space

UTL_FILE_DIR AND ORA-29280

utl_file_dir parameter was deprecated at least in 12c and completely removed at least in 19c.

For UTL_FILE procedures like fopen there are 2 different ways to specify directory:

1. Using Oracle DIRECTORY object
2. Using string with full path. This approach work if and only if the specified directory is present in the parameter UTL_FILE_DIR, otherwise it raises ORA-29280 (see example below). This way is not working in 19c as UTL_FILE_DIR does not exists anymore
   The second approach is work regardless UTL_FILE_DIR and this is the only way in Oracle 19c

SELECT NAME, VALUE FROM v$parameter WHERE NAME = 'utl_file_dir';
NAME            VALUE
utl_file_dir    c:\temp\utl_file_dir

======================================================
Using folder name with full path.
UTL_FILE_DIR does NOT contain folder - NOT WORKING!!!
======================================================
DECLARE
   v_fp           sys.utl_file.file_type;
BEGIN
   v_fp := sys.utl_file.fopen('c:\temp\non_utl_file_dir', 'test_file.csv', 'w', 32000);
   utl_file.fclose(v_fp);
END;
/
DECLARE
   v_fp           sys.utl_file.file_type;
BEGIN
   v_fp := sys.utl_file.fopen('c:\temp\non_utl_file_dir', 'test_file.csv', 'w', 32000);
   utl_file.fclose(v_fp);
END;
ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE", line 41
ORA-06512: at "SYS.UTL_FILE", line 478
ORA-06512: at line 4

======================================================
USING DIRECTORY OBJECT - WORKING
======================================================
CREATE OR REPLACE directory REPORT_DIRECTORY AS 'c:\temp\non_utl_file_dir';
Directory created
DECLARE
   v_fp           sys.utl_file.file_type;
BEGIN
   v_fp := sys.utl_file.fopen('REPORT_DIRECTORY', 'test_file.csv', 'w',32000);
   utl_file.fclose(v_fp);
END;
/
PL/SQL procedure successfully completed

======================================================
Using folder name with full path.
UTL_FILE_DIR contains folder - WORKING
======================================================
DECLARE
   v_fp           sys.utl_file.file_type;
BEGIN
   v_fp := sys.utl_file.fopen('c:\temp\utl_file_dir', 'test_file.csv', 'w',32000);
   utl_file.fclose(v_fp);
END;
/
PL/SQL procedure successfully completed

Set ssh private key folder in GIT

Set variable HOME to the folder where .ssh folder with keys is created and windows GIT will pick up private key from this folder:

set HOME=x:\

Test connectivity on Linux

Using curl

curl -v telnet://host:port

Using netcat

nc -z -v host port

cURL file send to Oracle cloud

curl --write-out '%{time_total}' -X POST --data-binary "@5M.csv" -H "Content-Type:text/csv" --user <user>:<password> "https://....adb.us-ashburn-1.oraclecloudapps.com/ords/<user>/huge_csv/batchload?batchRows=5000&errorsMax=20"

Taken from here

USE_SHARED_SOCKET

For old versions as pointed in the note Troubleshooting Connectivity Issues From a Remote Client to a Database (Doc ID 271852.1)

For 9.2 and below, if the Operating System is Microsoft Windows NT,
and you are using the dedicated server connection, a spawned Oracle
thread communicates with the client through a randomly allocated port
(called a REDIRECT). To force the oracle process to communicate only
through the port in which Listener is listening, set the key,
USE_SHARED_SOCKET = TRUE, in the Windows Registry
NOTE:
This is no longer the case with 10g and up as a REDIRECT does not take place for DEDICATED connections.

Или, если верить ноте 124140.1, начиная с версии 10.2

This technique only involves the version 8.1 to 10.1 Oracle Database versions. From 10.2 onward the default changed to allow port sharing.

Причина этого

Microsoft WINSOCK V1.1 API did not allow one process to pass a TCP socket to another process and as a result did not allow port sharing like on UNIX systems

Знание уже абсолютно бесполезное, ибо:

1. С версии 10.2 USE_SHARED_SOCKET=TRUE в реестре или переменной окружения
2. Уже 100 лет не видел Oracle на Windows за исключением собственного ноута

Use BLOB instead of CLOB for JSON

Advice, I’ve met couple of times:

Oracle performance tip: JSON is always UTF8 -Use ‘al32utf8’ character set if possible
Use BLOB instead of CLOB CLOB stores data as UCS2/UTF16
Use lob data api to insert/fetch data - getBytes/setBytes instead of - getBlob/setBlob!

login.sql is not executed from SQLPATH

A lot has been written, that login.sql is no more executed from current folder, but if SQLPATH variable has been set in registry or in env variable and login.sql is not executed (but other scripts do) - patch from 2274608.1 have to be installed.
This is applicable to at least Oracle 12.2 clients

SQL json_object VS PL SQL json_object_t

Comparing performance for simple json generation in Oracle 12.2
SQL’s json_object performs 1.5 times faster.
Result

SQL json_object: 41
PL/SQL JSON_OBJECT_T: 64

Test case

DECLARE
   l_result CLOB;
  
   l_cnt NUMBER := 10000;
   l_start_time NUMBER;
   
   l_key VARCHAR2(100) := 'testkey';
   l_val VARCHAR2(32767) := lpad('x', 5000, 'x');
   
   FUNCTION simple_json(p_name IN VARCHAR2, p_value IN CLOB) RETURN CLOB IS
      l_result JSON_OBJECT_T := JSON_OBJECT_T;
   BEGIN
      l_result.put(p_name, p_value);
      RETURN l_result.to_clob;
   END;
BEGIN
   l_start_time := dbms_utility.get_time;
   FOR i IN 1 .. l_cnt LOOP
      l_val := lpad('x', 5000, 'x') || systimestamp;
      SELECT json_object(key l_key VALUE l_val RETURNING CLOB) json INTO l_result FROM dual;  
      --dbms_output.put_line(l_result);     
   END LOOP;
   dbms_output.put_line('SQL json_object: ' || (dbms_utility.get_time - l_start_time)); 

   l_start_time := dbms_utility.get_time;
   FOR i IN 1 .. l_cnt LOOP
      l_val := lpad('x', 5000, 'x') || systimestamp;
      l_result := simple_json(l_key, l_val);
      --dbms_output.put_line(l_result);     
   END LOOP;
   dbms_output.put_line('PL/SQL JSON_OBJECT_T: ' || (dbms_utility.get_time - l_start_time)); 
END;

Add domain user to ORA_DBA group

Right after Oracle 12c installation and database creation I was able to connect to the database with username and password

sqlplus sys/pass as sysdba
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

but unable to connect with / as sysdba

sqlplus / as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied

Connection from local administrator account worked perfectly without user name and password, so I’ve checked ORA_DBA group with lusrmgr.msc console and found that I have to add currently connected domain user to ORA_DBA group.
But Add user dialog doesn’t allow search and add domain users. Solution for this is command line under Local Administrator account

net localgroup ORA_DBA /add domain\USERNAME

Proxy users

1. If we don’t want to share application user password to all the users who need to connect to our schema.
2. For better audit
   proxy users can be created

CREATE USER app_user_unknown_passwd IDENTIFIED BY unknown_password;
GRANT CREATE SESSION TO app_user_unknown_passwd;

CREATE USER connect_user_known_password IDENTIFIED BY known_password;
GRANT CREATE SESSION TO connect_user_known_password;

ALTER USER app_user_unknown_passwd GRANT CONNECT THROUGH connect_user_known_password;

Check through proxy_users view

SELECT * FROM proxy_users;

PROXY	CLIENT	AUTHENTICATION	FLAGS
CONNECT_USER_KNOWN_PASSWORD	APP_USER_UNKNOWN_PASSWD	NO	PROXY MAY ACTIVATE ALL CLIENT ROLES

Try to connect from SQL*Plus and check some context parameters

sqlplus connect_user_known_password[app_user_unknown_passwd]/known_password@db122

SQL> select USER, 
  sys_context('USERENV','PROXY_USER'), 
  sys_context('USERENV','CURRENT_SCHEMA') 
from dual;

USER
--------------------------------------------------------------------------------
SYS_CONTEXT('USERENV','PROXY_USER')
--------------------------------------------------------------------------------
SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
--------------------------------------------------------------------------------
APP_USER_UNKNOWN_PASSWD
CONNECT_USER_KNOWN_PASSWORD
APP_USER_UNKNOWN_PASSWD

ORA-28040 using 32-bit PL SQL Developer

Given:

• 64-bit Oracle database 12.2.0.1 installed locally
• 32-bit PL/SQL Developer
• 32-bit Oracle client 12.2.0.1 (as 32-bit PL/SQL developer can’t use 64-bit client / JDBC)

When:
Trying to connect from 32-bit PL/SQL Developer get an error ORA-28040: No matching authentication protocol
Connection from 64-bit PL/SQL Developer is OK

Solution:
Add
SQLNET.ALLOWED_LOGON_VERSION_SERVER=12
to server sqlnet.ora file (not client or TNS_ADMIN file)

Batched update

If you UNDO is not big enough or you want to split transaction here is the simple script for batched update

-- Execute batched update 
-- UPDATE table_name SET set_parameter;
--
-- USAGE
-- 1. Set l_batch_size
-- 2. Set select WHERE clause
-- 3. Set update's SET clause
-- 4. invoke process_table(table_name) for which this update statement should be executed
DECLARE 
  TYPE tpt_rowid IS TABLE OF ROWID;
 
  --1.Batch size
  g_bacth_size NUMBER := 100000;
  
  --2. SELECT rowid FROM table_name t WHERE ...value of variable below...
  l_select_where_clause VARCHAR2(32000) := q'[column_a = 111]';
  
  --3. UPDATE table_name SET ...value of variable below... WHERE rowid=rid
  l_update_set_clause VARCHAR2(32000) := q'[column_a = 333]';
  
  PROCEDURE process_table(p_table_name VARCHAR2) IS

    l_rids tpt_rowid;
    cur SYS_REFCURSOR;
  BEGIN
    OPEN cur FOR 'SELECT /*+ PARALLEL(8)*/rowid FROM ' || p_table_name || ' t WHERE ' || l_select_where_clause;
    LOOP
      
      FETCH cur BULK COLLECT INTO l_rids LIMIT g_bacth_size;
      dbms_output.put_line('Fetched: ' || l_rids.count);
      EXIT WHEN l_rids.count = 0;
      
      FORALL i IN l_rids.FIRST .. l_rids.LAST
        EXECUTE IMMEDIATE 
          'UPDATE ' || p_table_name || ' t' ||
          ' SET ' || l_update_set_clause || 
          ' WHERE rowid = :1' 
          USING l_rids(i);
      dbms_output.put_line('Updated: ' || SQL%ROWCOUNT); 
      COMMIT;
    END LOOP;
  END;
BEGIN
  --4. Invoke for all tables
  process_table('BB');  
END;
/

Java: words count

Simple app to count number of words in the file

import java.io.IOException;  
import java.nio.file.Files;  
import java.nio.file.Paths;  
import java.util.*;  
import java.util.stream.Collectors;  
  
class Scratch {  
    public static void main(String[] args) throws IOException {  
        Map<String, Long> words =  
                Files.lines(Paths.get("American Beauty-English.srt"))  
                        .filter(str -> !str.trim().isBlank())  
                        .filter(str -> !str.matches("^\\d+.*"))  
                        .map(str -> str.split("[^\\w']"))  
                        .flatMap(x -> Arrays.stream(x))  
                        .filter(str -> !str.trim().isBlank())  
                        .collect(Collectors.groupingBy(  
                                s -> s.toLowerCase(),  
                                Collectors.counting()));  
  
        System.out.println("Words count: " + words.keySet().size());  
        System.out.println("20 most popular words: ");  
        PriorityQueue<Word> sortedWords = new PriorityQueue<>(Collections.reverseOrder());  
        words.forEach(  
                (k, v) -> sortedWords.add(new Word(v, k))  
        );  
  
        sortedWords.stream()  
                .limit(20)  
                .forEach(System.out::println);  
    }  
  
    static class Word implements Comparable<Word> {  
        public Word(long cnt, String word) {  
            this.cnt = cnt;  
            this.word = word;  
        }  
  
        public long getCnt() {  
            return cnt;  
        }  
  
        public String getWord() {  
            return word;  
        }  
  
        private long cnt;  
        private String word;  
  
        @Override  
  public int compareTo(Word word) {  
            return Comparator.comparing(Word::getCnt).thenComparing(Word::getWord).compare(this, word);  
        }  
  
        @Override  
  public String toString() {  
            return String.format("'%s' used %d times", word, cnt);  
        }  
    }  
}

PDB point in time recovery by Franck Pachot

Task
Source database has multiply PDBs. User wants to restore in specific point in time only one of them
Here is the Franck’s answer and script

There is no way other than point-in-time duplicate to an auxiliary CDB and unplug to put wherever you want. You can skip pluggable databases so that you restore only CDB$ROOT and your PDB. This process is automated by RMAN when restoring in-place (a temporary auxiliary database is created for CDB$ROOT) but not for your case.
Here is a script I used to demo it once on a lab:

---# create an init.ora  
cat > /tmp/CDB_TEMP.ora <<'CAT'  
db_name=CDB_TEMP  
# I set a temporary domain to avoid any conflict  
db_domain=[temp.dbi-services.com](http://temp.dbi-services.com/)  
enable_pluggable_database=true  
compatible=19.0.0.0  
db_block_size=8192  
db_files=200  
sga_target=1024M  
processes=150  
db_create_file_dest=/u02/oradata  
db_recovery_file_dest=/u90/fast_recovery_area  
db_recovery_file_dest_size=1G  
#_clone_one_pdb_recovery=true # this is used by automated in-place PDBPITR so it may be cool ;)  
#_system_trig_enabled=false  
CAT  
---# Start the instance  
ORACLE_SID=CDB_TEMP sqlplus / as sysdba <<<"startup force nomount pfile='/tmp/CDB_TEMP.ora';"  
---## RMAN duplicate  
---# RMAN connect to target and auxiliary  
ORACLE_SID=CDB_TEMP rman  
set echo on  
connect target sys/manager@//localhost:1521/[CDB1.it.dbi-services.com](http://cdb1.it.dbi-services.com/)  
alter system archive log current  
/  
connect auxiliary /  
---# list PDBs to exclude (remove the one you want to keep!)  
select listagg(pdb_name,',')within group(order by pdb_name) from dba_pdbs  
/  
---# duplicate skip PDBs(use no-open just to show funny name)  
duplicate database CDB1_SITE1 to CDB_TEMP skip pluggable database CDB1PDB02,CDB1PDB03 until restore point 'DEMO_OOP_PDB_PITR' noopen;  
quit;  
---# Open it (because we did a noopen duplicate)  
ORACLE_SID=CDB_TEMP sqlcl / as sysdba  
show pdbs  
alter database open resetlogs  
/  
show pdbs  

  

Cleanup:

  

---# remove the temporary CDB  
ORACLE_SID=CDB_TEMP rman target /  
startup dba mount force;  
drop database noprompt;  
quit;  
rm -rf /u??/?*/CDB_TEMP $ORACLE_BASE/diag/rdbms/cdb_temp $ORACLE_HOME/rdbms/log/cdb_temp* $ORACLE_HOME/rdbms/audit/CDB_TEMP* $ORACLE_HOME/dbs/?*CDB_TEMP* $ORACLE_BASE/admin/CDB_TEMP  

I doubt there will ever be another solution because you need a CDB$ROOT at same point-in-time in order to open the PDB and unplug it.

Note that, as an alternative, if you have a standby you may stop apply, flashback the pdb, convert to snapshot standby, clone the pdb, and get all back to physical standby.

DOP downgrade

Для версии 12 (по крайней мере 2) как написано тут DOP downgrade reason можно посмотреть в SQL Monitor в Other column for PX coordinator shows the downgrade reason, а сами причины расшифровать запросом

select qksxareasons, indx 
from x$qksxa_reason 
where qksxareasons like '%DOP downgrade%';

DOP downgrade due to adaptive DOP 351
DOP downgrade due to resource managermax DOP 352
DOP downgrade due to insufficient number of processes 353
DOP downgrade because slaves failed to join 354

В 11.2 такой таблички я не нашел, поэтому там придется пользоваться старым добрым

alter session set "_px_trace"=high,all;

который прекрасно описан тут

Execute as sysdba

One can use os_command package to create and execute script from database server as sysdba user. Oracle white paper about this package
Steps to implement:

1. Create directory to store file and grant permission to user

create or replace directory &tmpdir as '&tmppath';
grant read, write, execute on directory &tmpdir to &username;

2. Grant execute permissions

begin
  dbms_java.grant_permission
      (upper('&username'),
       'java.io.FilePermission',
       '<<ALL FILES>>',
       'read,write,execute');
  dbms_java.grant_permission
      (upper('&username'),
       'java.lang.RuntimePermission',
       '*',
       'writeFileDescriptor');
end;
/

3. Write file content with SQL or PL/SQL commands

--run sqlplus script
begin
  lob_writer_plsql.write_clob(
    '&tmpdir', '&scriptname',
q'[#!/bin/bash

ORACLE_SID=&dbname
ORACLE_HOME=/data/oracle/product/&dbver.&subver
PATH=/bin:/sbin:/usr/bin:${PATH}
export ORACLE_SID ORACLE_HOME

$ORACLE_HOME/bin/sqlplus -l -s / as sysdba <<!!!
--WRITE HERE WHAT TO DO

--example
SELECT * FROM sys.metaview\$;

--example (notice how to wrap handle "/" char)
BEGIN
  NULL;
END;
]'||q'[/

exit
!!!

exit 0
]'
  );
end;
/

&dbver and &subver can be extracted by query

column dbver new_value dbver
select regexp_substr(banner, 'Release (\d+.\d+.\d+.\d+).\d+', 1, 1, '', 1) dbver
from v$version
where banner like 'Oracle Database%';

4. Execute script

declare
  c clob;
begin
  c := os_command.exec_CLOB('chmod 755 &tmppath/&scriptname');
  c := os_command.exec_CLOB('&tmppath/&scriptname');
  dbms_output.put_line(c);
  c := os_command.exec_CLOB('rm &tmppath/&scriptname');
end;
/

log file sync by Tanel Poder

As “log file sync” is not directly an I/O wait event (IO happens indirectly under the hood), but “wait for LGWR to ACK/increment redo persisting position” then there may be multiple reasons for delays. I’m repeating some of what others have said here too:

• LGWR could be waiting for remote ACK (synchronous data guard) - that should show up as a LNS* wait or something like that though
• Process slowness due to CPU overload/scheduling latency or swapping (in both cases, LGWR may do its work fast, but it takes a while for the foreground process to wake up and end the wait event - you could check if there are any reports regarding switching LGWR mode in alert.log around that time)
• Bug/missed post due to switching between LGWR progress polling vs. waiting for LGWR post
• ASM instance communication issues (ASM communication can be on the critical path when creating/changing files on ASM and even for reading, when the ASM metadata cache in your local instance doesn’t have what it needs)
• So, the physical redo log file disk write is not necessarily the biggest contributor to the log file sync wait

You’ll need to measure what LGWR itself was doing during the problem time

• Since you’re on 19c (12c or later) the LGWR has slaves too and they may be doing most of the work
• You’ll need to look into both the LGWR process and any other LG* ones too
• Since you have (Oracle) Snapper running already and writing to trace you should have the data already
• The lgwr in the following @snapper ash,stats,trace 10 999999 lgwr syntax includes LGWR slaves too
• As LGWR slaves have their own sessions (and SIDs), you should run ashtop etc and report if there’s any BLOCKING_SESSION value listed for LGWR and slaves
• If LGWR and the slaves were listed idle during the problem time (while others waited for log file sync event at the same time) and if there was no huge OS level CPU/memory shortage at the time, this looks like a background <-> foreground communication problem.
• This (and any potential ASM issues) could probably be troubleshooted via KST tracing, KSR channels and X$TRACE that could help here (some of it is enabled by default ( Like I showed in a previous hacking session: https://www.youtube.com/watch?v=mkmvZv58W6w )

Snapper

Paste the full Snapper output (of one time snapshot from the problem time) here if you can - we’ll be able to see what the slaves were doing

• You’d need to include not only LGWR SID but also any LG* slave SIDs too
• Also we’ll see better LGWR delay metrics from V$SESSTAT that go beyond just wait events
• Thinking of metrics like these - snapper already handles them (if you look into the rightmost column)
• redo synch time overhead (usec) - “FG wakeup overhead per log file sync”
• redo write time - accounting end to end redo write time (not just the log file parallel write syscall) “per redo write”

ASH wait chains

Regarding the ASH wait chains error on 19c - I’m aware of this but forgot to look into it

• It has (probably) something to do with GV$ views (or PX slaves used for PDB V$ queries) and possibly the connect by I’m using
• Does it work in the CDB (if multitenant)?
• Does it work with lower optimizer features enabled?
• As we are already manually looking into what LGWR and its slaves are doing, there’s no need for the wait chains script here

Linux Process Snapper - pSnapper

• If you want to see the full “wait” states of Linux processes/threads, use the -a option.
• It’s somewhat like always showing “idle” threads too
• But for linux apps it’s harder to determine what’s idle and what’s some network RPC wait, so I currently just do what Linux “load” metric does
• I just shows threads in Runnable and Disk/Uninterruptible sleep states by default (-a shows all states, including Sleep and kernel-Idle threads etc)
• So the Linux pSnapper “idle” means a somewhat different thing than Oracle “idle” - say LGWR was waiting for a Data Guard ACK, that should not be shown as Idle in Oracle, but at the Linux process level it’s “just some network socket wait” and you’d need to use -a option to show all of them

Were LGWR/LG* processes mostly idle during the problem time?

• As I kept reading the thread, it looks like ASH reported LGWR being mostly idle during the problem time
• This is not exactly the same pattern, but I’ve seen cases in the past where everyone’s waiting for LGWR and LGWR doesn’t seem to be busy and doesn’t show any significant waits
• It turned out that LGWR was actually waiting for CKPT to make progress, but it wasn’t synchronously waiting, but just going to sleep and “checking again later” in a loop
• I wrote about this here: https://blog.tanelpoder.com/posts/log-file-switch-checkpoint-incomplete-and-lgwr-waiting-for-checkpoint-progress/
• So it may still make sense to check what LGWR/LG* processes are doing even if they don’t seem to be active much (like do you see an occasional controlfile read wait by LGWR etc)
• SQL*Trace would capture all activity of LGWR, but may slow things down further, so you could also sample & spool V$SESSION_WAIT_HISTORY (that keeps a log of last 10 waits of every session in memory)

Why my windows stops sleeping?

Why windows exit from sleep or hibernate mode? I’ve spent a lot of time trying to find answer on this question in internet. And finally found solution. Run from cmd with Administrator privileges

C:\Windows\system32>powercfg -sleepstudy

Sleep Study report saved to file path C:\Windows\system32\sleepstudy-report.html.

and see the report. This’ll show the reason why system enters/exits sleep/hibernate mode.