Wednesday, April 30, 2025

Cleanup of the ADR

Sometimes you may want to clean up your adr. I had this problem after I had just recently created a new database on my server called "db01".

In my case, I had the following adr homes set:

ADR Homes:
diag/rdbms/db01/db01
diag/rdbms/db02/db02
diag/rdbms/db03/db03
diag/rdbms/db04/db04
diag/rdbms/db05/db05
diag/clients/user_oracle/RMAN_610688766_110
diag/clients/user_oracle/host_610688766_110
diag/clients/user_oracle/host_610688766_82
diag/tnslsnr/dbhost1/listener
diag/tnslsnr/dbhost1/mylsnr
diag/tnslsnr/dbhost1/testlistener
This caused operations against ADR to fail with
ADR HOME selected:  diag/rdbms/db01/db01 diag/rdbms/db02/db02 diag/rdbms/db03/db03 diag/rdbms/db04/db04 
DIA-48415: Syntax error found in string [diag/rdbms/db01/db01] at column [26]

DIA-48415: Syntax error found in string [diag/rdbms/db02/db02] at column [26]

DIA-48415: Syntax error found in string [diag/rdbms/db03/db03] at column [26]

DIA-48415: Syntax error found in string [diag/rdbms/db04/db04] at column [26]

Syntax
error
Solution: clean out the old folder physically:
cd /u01/oracle/diag/rdbms
rm -rf db02 db03 db04 db05

cd /u01/oracle/diag/tnslsnr/dbhost1
rm -rf mylsnr testlistener

cd /u01/oracle/diag/clients/user_oracle
rm -rf *
Afterwards, the adr should you can check your adrci settings. They should look better now:
ADR base = "/u01/oracle"
adrci> show homes
ADR Homes:
diag/rdbms/db01/db01
diag/clients/user_oracle/RMAN_610688766_110
diag/tnslsnr/dbhost1/listener

Friday, April 25, 2025

List folders only - no files

For future quick reference:

I prefer:
ls -d */

dir1/      dir2/       di3/   dir4/ 
Also good:
ls -l | grep "^d"

drwxr-xr-x 4 root   root           4096 Oct 13  2022 dir1
drwxr-xr-x 2 root   root           4096 Mar 14 11:17 dir2
drwxr-xr-x 2 root   root           4096 Mar 26 15:51 dir2
drwxr-xr-x 2 root   root           4096 Apr 27  2021 dir4
A third option:
ls -F | grep "/$"
 
dir1/
dir2/
dir3/
dir4/

Arguments that can be passed to "dbca -createCloneTemplate" usage output

dbca -createCloneTemplate
   -sourceSID source_database_sid | -sourceDB source_database_name
   -templateName new_database_template_name
   [-promptForWalletPassword]
   [-rmanParallelism parallelism_integer_value]
   [-maxBackupSetSizeInMB maximum_backup_set_size_in_MB]
   [-dataFileBackup {true | false}]
   [-datafileJarLocation data_files_backup_directory]
   [-sysDBAUserName SYSDBA_user_name]
   [-sysDBAPassword SYSDBA_user_password]
   [-useWalletForDBCredentials {true | false}
       -dbCredentialsWalletPassword wallet_account_password
       -dbCredentialsWalletLocation wallet_files_directory]
   [-uploadToCloud 
       -opcLibPath OPC_library_path
       -opcConfigFile OPC_configuration_file_name
       [-rmanEncryptionPassword rman_encryption_password ]
   [-compressBackup { true | false } ]
   [-walletPassword database_wallet_password]
Documentation here

What is the reason behind the message: "xauth: file /root/.Xauthority does not exist "?

What does the message that you sometimes see when attempting to launch X applications on a Linux server
"xauth:  file /root/.Xauthority does not exist"
really mean?

It means that the .Xauthority file, which stores X11 authentication cookies, does not yet exist in your home directory (in this case, /root/).

This is common when:

  • X11 forwarding has not been set up or used, yet.
  • You are logging in as root and have not initialized X11 in that session.
  • The file was deleted or never created.

    To work around the issue, follow these steps:

    1. Make sure xauth is installed
    # RHEL/Centos
    su - 
    yum install xauth
    
    #Debian
    apt install xauth
    
    2. Make sure you use the -X flag (X forwarding) when connecting to the server
    ssh -X root@yourserver
    
    3. On the server, edit the file /etc/ssh/sshd_config, and make sure these lines exist and are set to yes
    X11Forwarding yes
    X11UseLocalhost yes
    
    4. Restart ssh daemon
    systemctl restart sshd
    
    5. Try launching a graphical app, like xterm

    If these work, your X11 forwarding is properly set up, and .Xauthority will be created automatically.

    6. If you need to manually create the .Xauthority file

    This is rarely necessary, but you can run:
    touch ~/.Xauthority
    xauth generate :0 . trusted
    
  • Wednesday, April 23, 2025

    How to create a database with non-default blocksize using 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

    1. create a response file called $ORACLE_SID.rsp, in this case, tstdwh1.rsp:
    responseFileVersion=/oracle/assistants/rspfmt_dbca_response_schema_v12.2.0
    gdbName=tstdwh1.oric.no
    sid=tstdwh1
    databaseConfigType=SI
    policyManaged=false
    createServerPool=false
    force=false
    createAsContainerDatabase=false
    templateName=/u01/oracle/product/19c/assistants/dbca/templates/New_Database.dbt
    sysPassword=mysecreatpassword
    systemPassword=mysecreatpassword
    datafileJarLocation={ORACLE_HOME}/assistants/dbca/templates/
    datafileDestination=/data02/oradata/{DB_UNIQUE_NAME}/
    recoveryAreaDestination=/data04/fra/{DB_UNIQUE_NAME}
    storageType=FS
    characterSet=AL32UTF8
    nationalCharacterSet=AL16UTF16
    registerWithDirService=false
    listeners=LISTENER
    skipListenerRegistration=false
    variables=ORACLE_BASE_HOME=/u01/oracle/product/19c,DB_UNIQUE_NAME=tstdwh1,ORACLE_BASE=/u01/oracle,PDB_NAME=,DB_NAME=tstdwh1,ORACLE_HOME=/u01/oracle/product/19c,SID=tstdwh1
    initParams=undo_tablespace=UNDOTBS1,sga_target=4710MB,db_block_size=32768BYTES,nls_language=NORWEGIAN,dispatchers=(PROTOCOL=TCP) (SERVICE=tstdwh1XDB),diagnostic_dest={ORACLE_BASE},control_files=("{ORACLE_BASE}/oradata/{DB_UNIQUE_NAME}/control01.ctl", "/u04/fra/{DB_UNIQUE_NAME}/control02.ctl"),remote_login_passwordfile=EXCLUSIVE,audit_file_dest={ORACLE_BASE}/admin/{DB_UNIQUE_NAME}/adump,processes=400,pga_aggregate_target=1570MB,nls_territory=NORWAY,local_listener=LISTENER_TSTDWH1,db_recovery_file_dest_size=7851MB,open_cursors=300,log_archive_format=%t_%s_%r.dbf,db_domain=oric.no,compatible=19.0.0,db_name=tstdwh1,db_recovery_file_dest=/u04/fra/{DB_UNIQUE_NAME},audit_trail=db
    sampleSchema=false
    memoryPercentage=40
    automaticMemoryManagement=false
    
    Notice the parameter db_block_size=32768BYTES in the "variables" directive, and the fact that I am using the New_Database.dbt template. This template is more flexible than the others delivered by Oracle, but makes the whole database creation process take more time, as they do not include any databaes files.

    Also notice the absence of the directive "databaseType" - it is only meaningful if you let DBCA choose a built-in template based on that type (like OLTP or Data Warehouse).

    2. execute the dbca directly in the shell:
    dbca -createDatabase -responsefile ./${ORACLE_SID}.rsp -silent 
    
    Or, create a wrapper around the command, make it executable and execute it in the background:
    #!/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
    
    chmod 755 run_dbca.sh
    nohup ./run_dbca.sh &
    
  • Friday, April 11, 2025

    Solution to "ORA-12546: TNS:permission denied" when starting sqlplus

    Problem: when trying to start sqlplus, the error below is thrown:
    ORA-12546: TNS:permission denied
    
    Cause: Incorrect permissions on the oracle binary.

    It needs the setuid permissions. Without those permissions, it cannot open sockets or shared memory properly — and the ORA-12546 is thrown when trying to connect via sqlplus.

    Solution: Relink the oracle binary
    oracle@myserver:[cdb]# ls -l $ORACLE_HOME/bin/oracle
    -rw-r--r-- 1 oracle dba 0 Jan 24 09:57 /u01/oracle/product/19c/bin/oracle
    oracle@myserver:[cdb]# cd $ORACLE_HOME/rdbms/lib
    oracle@myserver:[cdb]# make -f ins_rdbms.mk ioracle
    
    Afterwards, permissions are correct:
    oracle@myserver:[cdb]# ls -l $ORACLE_HOME/bin/oracle
    -rwsr-s--x 1 oracle dba 462603312 Apr 11 10:54 /u01/oracle/product/19c/bin/oracle
    

    Wednesday, April 9, 2025

    How to mimic sqlplus variable behaviour in PostgreSQL

    Here is how you can mimic the usage of variables at the prompt using psql and PostgreSQL like you would with oracle and sqlplus/sqlc:
    \connect mydb
    \prompt 'Enter value for querid: ' v_quid
    \echo You entered: :v_quid
    SELECT query_sql_text FROM query_store.query_texts_view WHERE query_text_id = :'v_quid';
    
    Run it:
    sql -h pgserver1.oric.no -U dbadmin -d postgres -f find_rest_of_statement.sql
    
    Enter value for querid: -7644745457188850826
    You entered: -7644745457188850826
                                                        query_sql_text                                                    
    ----------------------------------------------------------------------------------------------------------------------
          THE QUERY TEXT WILL SHOW UP HERE :-)                                                                                                    
    
    

    Wednesday, April 2, 2025

    How to check the number of rows inserted into the different partitions in a PostgreSQL partitioned table

    Create the table:
    CREATE TABLE mytable
    (
        id bigserial,
        key text COLLATE pg_catalog."default" NOT NULL,
        outdata jsonb,
        partno integer,
        koffset bigint,
        handled_at timestamp with time zone,
        inserted_at timestamp with time zone NOT NULL DEFAULT now(),
        kyear integer NOT NULL, -- Ensure it's NOT NULL to work properly with partitions
        nk boolean NOT NULL,
        CONSTRAINT mytable_pkey PRIMARY KEY (id,kyear)
    )
    PARTITION BY RANGE (kyear);
    
    Create the partitions. Make it suitable for ranges that adhere to the standard YYYYMM:
    CREATE TABLE mytable_202501
    PARTITION OF mytable
    FOR VALUES FROM (202501) TO (202502);
    
    CREATE TABLE mytable_202502
    PARTITION OF mytable
    FOR VALUES FROM (202502) TO (202503);
    
    Insert some test values:
    INSERT INTO mytable (key, outdata, partno, koffset, handled_at, kyear, nk)
    VALUES
        ('A123', '{"data": "test1"}', 101, 500, NOW(), 202501, TRUE),
        ('B456', '{"data": "test2"}', 102, 600, NOW(), 202501, FALSE),
        ('C789', '{"data": "test3"}', 103, 700, NOW(), 202501, TRUE);
    
    INSERT INTO mytable (key, outdata, partno, koffset, handled_at, kyear, nk)
    VALUES
        ('D111', '{"data": "test4"}', 104, 800, NOW(), 202502, FALSE),
        ('E222', '{"data": "test5"}', 105, 900, NOW(), 202502, TRUE),
        ('F333', '{"data": "test6"}', 106, 1000, NOW(), 202502, FALSE);
    
    Confirm that the data is indeed there:
    select * from mytable;
    
    Finally, verify that the rows have been commited to two different partitions:
    musicdb=> SELECT relname, n_tup_ins
    musicdb-> FROM pg_stat_all_tables
    musicdb-> WHERE relname IN ('mytable_202501', 'mytable_202502');
        relname     | n_tup_ins
    ----------------+-----------
     mytable_202501 |         3
     mytable_202502 |         3
    (2 rows)
    

    Wednesday, March 5, 2025

    How to manually purge your standard audit trail


    In this post I will show you how you can manage your audit trail using the package dbms_audit_mgmt.
    It can be used for all kinds of audit trails, both OS, XML, unified and standard auditing.
    You refer to them using the constants for audit_trail_types, found here

    In this post, I am using the DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD

    Note: If you simply want to purge the entire audit trail, use the procedure below:
    BEGIN
      DBMS_AUDIT_MGMT.clean_audit_trail(
       audit_trail_type        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
       use_last_arch_timestamp => FALSE);
    END;
    /
    
    Be careful:

    Setting the use_last_arch_timestamp to FALSE will effectlivly do a TRUNCATE TABLE SYS.AUD$ behind the scenes and takes only a couple of minutes to execute, even with millions of rows to be purged. You may lose your audit data, which can have legal consequences.


    If desirable, audit trails can be cleaned based on the "Last Archive Timestamp" value.
    The Last Archive Timestamp represents the timestamp of the most recent audit record that was securely archived:
    SQL> SELECT * FROM DBA_AUDIT_MGMT_LAST_ARCH_TS;
    
    no rows selected
    
    
    No rows returned from this query means that there has been no previous cleanup performed.

    In cases where you want to keep some of your audit data, you can manually set the "Last Archive Timestamp" to a value of your choice.
    Here I set it to a month ago from today:
    BEGIN
      DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP (
        AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
        LAST_ARCHIVE_TIME => SYSTIMESTAMP-30);
    END;
    /
    

    If you try to use the SET_LAST_ARCHIVE_TIMESTAMP procedure before cleanup has been initialized, you will receive error

    ERROR at line 1:
     ORA-46258: Cleanup not initialized for the audit trail
     ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 61
     ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 2283
     ORA-06512: at line 17
    

    So make sure to initialize the cleanup first. Note that "initialization" will automatically relocate the AUD$ table to the SYSAUX tablespace.
    Use the IS_CLEANUP_INITIALIZED procedure to verify the state of the cleanup before and after the the INIT_CLEANUP has been executed.
    Put the following in a file called check_init_status.sql:
    SET SERVEROUTPUT ON
     BEGIN
      IF DBMS_AUDIT_MGMT.IS_CLEANUP_INITIALIZED(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN
       DBMS_OUTPUT.PUT_LINE('Cleanup is initialized.');
      ELSE
       DBMS_OUTPUT.PUT_LINE('Cleanup is not initialized.');
      END IF;
     END;
     /
    
    Check status:
    sqlplus / as sysdba@check_init_status.sql
    
    Cleanup is not initialized.
    
    PL/SQL procedure executed.
    
    To initialize cleanup of the standard audit trail, put the following in a file called init_cleanup.sql:
    BEGIN
       DBMS_AUDIT_MGMT.INIT_CLEANUP (
       audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
       default_cleanup_interval => 48);
     END;
     /
    
    Initialize cleanup:
    sqlplus / as sysdba@init_cleanup.sql
    
    Check status again:
    sqlplus / as sysdba@check_init_status.sql
    
    Cleanup is initialized.
    
    PL/SQL procedure executed.
    

    Note that you need sufficient space in the SYSAUX tablespace to proceed. Autoextend on the datafiles will not help, there need to be sufficiently allocated space in advance:
    ERROR at line 1:
     ORA-46267: Insufficient space in 'SYSAUX' tablespace, cannot complete operation
     ORA-06512: at "SYS.DBMS_AUDIT_MGMT", line 1087
     ORA-06512: at line 3
    

    How many rows do we have in our standard audit trail?
    select count(*) from sys.aud$;
    
    "COUNT(*)"
    141 362 956


    How old is that data?
    select min(ntimestamp#) "mindate", max(ntimestamp#) "maxdate"
    from sys.aud$;
    
    "mindate" "maxdate"
    21.11.2016 08:29:16,325777 04.02.2020 14:08:49,660074

    So our data dates back to 2016. Time to clean up.
    The "use_last_arch_timestamp" make sure I will preserve audit statements more recent than one month:
    BEGIN
      DBMS_AUDIT_MGMT.clean_audit_trail(
       audit_trail_type        => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
       use_last_arch_timestamp => TRUE);
    END;
    /
    
    Let's check the status after the execution of the clean_audit_trail-procedure: How many rows do we have left in our standard audit trail?
    select count(*) from sys.aud$;
    
    "COUNT(*)"
    2 356 049


    How old is that data?
    select min(ntimestamp#) "mindate", max(ntimestamp#) "maxdate"
    from sys.aud$;
    
    "mindate" "maxdate"
    04.03.2020 08:09:01,325371 04.02.2020 14:08:49,660074

    Thursday, February 6, 2025

    Potential solution to INS-32715 when executing runInstaller

    Error when running runInstaller:
    [FATAL] [INS-32715] The source home (/u01/oracle/product/19c) is not registered in the central inventory.
       ACTION: Ensure that the source home is registered in the central inventory.
    
    Cause:
    There is something wrong with the inventory, although it seems correct.

    Find the inventory file:
    opatch lsinventory
    
    Oracle Home       : /u01/oracle/product/19c
    Central Inventory : /u01/oraInventory
       from           : /u01/oracle/product/19c/oraInst.loc
    
    
    Check the file for obvious errors:
    cd /u01/oraInventory/ContentsXML
    cat inventory.xml (excerpt)
    
    <HOME_LIST>
    <HOME NAME="19c" LOC="/u01/oracle/product/19c" TYPE="O" IDX="8"/>
    </HOME_LIST>
    
    Solution:
    Attach the ORACLE_HOME to the inventory once more, even though it looks correct to begin with:
    
     $ORACLE_HOME/oui/bin/runInstaller -silent -attachHome ORACLE_HOME="$ORACLE_HOME" ORACLE_HOME_NAME="19c"
    Starting Oracle Universal Installer...
    
    Checking swap space: must be greater than 500 MB.   Actual 25599 MB    Passed
    The inventory pointer is located at /etc/oraInst.loc
    You can find the log of this install session at:
     /home/oracle/oraInventory/logs/AttachHome2025-02-06_01-07-51PM.log
    'AttachHome' was successful.
    
    Try the runInstaller command again, and it should work.

    Monday, January 27, 2025

    How to install and configure PG Bouncer on Linux x86_64

    Install the PGBouncer software


    List packages allready installed on the system:
    yum list installed |grep bounce
    
    Search repository:
     yum search bounce
    =========================================================== Name & Summary Matched: bounce ============================================================
    pgbouncer.x86_64 : Lightweight connection pooler for PostgreSQL
    =============================================================== Summary Matched: bounce ===============================================================
    
    Install:
     yum install pgbouncer.x86_64
    
    Verify installation:
     yum list installed |grep bounce
    pgbouncer.x86_64                                              1.24.0-42PGDG.rhel8                     @YOURREPOSITORY_PostgreSQL_PostgreSQL_common_RHEL8_x86_64
    
    Done with the installation!


    Configure the pgbouncer for connections made by the user "music" to my database called "musicdb"

    [root] chown postgres:postgres /etc/pgbouncer/ -R
    
    Create the file /etc/pgbouncer/pgbouncer.ini:
    ;
    ; pgbouncer config file
    ;
    [databases]
    postgres    = port=5432 dbname=musicdb
    [pgbouncer]
    listen_addr = 127.0.0.1
    listen_port = 6432
    admin_users = music
    auth_type   = scram-sha-256
    auth_file   = /postgres_data/config/users.txt
    logfile     = /postgres_data/config/pgbouncer.log
    pidfile     = /postgres_data/config/pgbouncer.pid
    server_reset_query = DISCARD ALL;
    pool_mode = session
    default_pool_size = 5
    log_pooler_errors = 0
    
    Find the SCRAM authenticatio key:
    psql
    select '"'||rolname||'" "'||rolpassword||'"' from pg_authid where rolname='music';
    
      
    Create the file /postgres_data/config/users.txt

    vi /postgres_data/config/users.txt

    Add the following, and paste the value of the key you found in the query above, in between the second pair of quotation marks:

    "music" "SCRAM-SHA-256_your_SCRAM_key_here"


    Make sure port 6432 isn't occupied
     ss -ltpn | grep 6432 --> no output? Go go ahead! 
    
    As the postgres user, start the pgbouncer process
    pgbouncer -d /etc/pgbouncer/pgbouncer.ini
    
    The port will now be occupied:
    ss -ltpn | grep 6432
    LISTEN 0      128        127.0.0.1:6432      0.0.0.0:*    users:(("pgbouncer",pid=392231,fd=12))
    
    You can now connect to your pgbouncer instance. There are a number of different show-commands:
    psql -p 6432 -h 127.0.0.1 -U music pgbouncer -c "show config"
    Password for user music:
                 key             |                         value                          |                        default                         | changeable
    -----------------------------+--------------------------------------------------------+--------------------------------------------------------+------------
     admin_users                 | music                                                  |                                                        | yes
     application_name_add_host   | 0                                                      | 0                                                      | yes
     auth_dbname                 |                                                        |                                                        | yes
     auth_file                   | /var/lib/pgsql/users.txt                               |                                                        | yes
    
    And some control commands:
    psql -p 6432 -h 127.0.0.1 -U music pgbouncer -c "reload"
    Password for user music:
    RELOAD
    

    To connect through the pgbouncer rather than a direct connection, use the syntax below, where you exchange the database name ("musicdb") with "pgbouncer":
      psql -p 6432 -h 127.0.0.1 -U music -d pgbouncer
    Password for user music:
    psql (15.10, server 1.24.0/bouncer)
    WARNING: psql major version 15, server major version 1.24.
             Some psql features might not work.
    Type "help" for help.
    
    pgbouncer=# \conninfo
    You are connected to database "pgbouncer" as user "music" on host "127.0.0.1" at port "6432".