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".

Friday, December 27, 2024

How to check why a PostgreSQL server won't start

journalctl -u postgresql-15.service
Example output:
Dec 27 07:15:50 psl0part-prod-pgsql02.skead.no systemd[1]: Unit postgresql-15.service entered failed state.
Dec 27 07:15:50 psl0part-prod-pgsql02.skead.no systemd[1]: postgresql-15.service failed.
Dec 27 07:22:01 psl0part-prod-pgsql02.skead.no systemd[1]: Starting PostgreSQL 15 database server...
Dec 27 07:22:01 psl0part-prod-pgsql02.skead.no postmaster[65528]: 
2024-12-27 06:22:01.703 GMT [65528] 
LOG:  syntax error in file "/path/to/file/postgresql.auto.conf" line
Dec 27 07:22:01 psl0part-prod-pgsql02.skead.no systemd[1]: postgresql-15.service: main process exited, code=exited, status=1/FAILURE
Dec 27 07:22:01 psl0part-prod-pgsql02.skead.no systemd[1]: Failed to start PostgreSQL 15 database server.
Dec 27 07:22:01 psl0part-prod-pgsql02.skead.no systemd[1]: Unit postgresql-15.service entered failed state.
Dec 27 07:22:01 psl0part-prod-pgsql02.skead.no systemd[1]: postgresql-15.service failed.
In this case, the error seems to be in an error in the file postgresql.auto.conf.

Monday, December 2, 2024

How to use rsync between two servers

For example, to syncronize two servers so they get identical file structures: 

  • Source server: server1.oric.no 
  • Destination server: server2.oric.no 
  • Execute as: root
target=server2.oric.no && rsync -vaz --delete /u01/app/myfiles ebsuser@$target:/u01/app
This will sync the entire "/u01/app/myfiles" folder + all subfolders.

The --delete flag in rsync ensures that files in the destination directory (ebsuser@$target:/u01/app) that are not present in the source directory (/u01/app/inst) are deleted. This helps keep the destination directory synchronized with the source directory.

Thursday, October 31, 2024

Error: ORA-16831: operation not allowed on this member

When trying to add a standby database to your configuration, I received
DGMGRL>  add database 'cdb_stby1' as connect identifier is 'cdb_stby1';
Error: ORA-16831: operation not allowed on this member
This message means that the database you are trying to add, in this case cdb_stby1, is not ready to become a standby database in your configuration.

It is easy to verify by querying the "database_role" column in the v$database view.

It needs to be opened with a role that is NOT primary, for example, the PHYSICAL STANDBY role
select database_role, open_mode,name from v$database;

DATABASE_ROLE    OPEN_MODE            NAME
---------------- -------------------- ------------------------------
PHYSICAL STANDBY MOUNTED              CDB
If your database is opened with the role PRIMARY, you need to clone it from your target, for example by using RMANs DUPLICATE ... FOR STANDBY FROM ACTIVE DATABASE