Thursday, February 27, 2020

How to install and configure a simple replication using Golden Gate 19.1 Classic Architecture



In this article I will show the steps to set up a very simple schema replication using the latest Oracle Golden Gate software, which is 19.1 at the time of writing. Although the Microservices Architecture has been released, I will be implementing an Oracle-to-Oracle one-way replication, using the "Classic Architecture".

Servers involved:

Source server: db-server1.oric.no
Source database: td1
Oracle RDBMS version 12.2.0.1
Oracle RDBMS installation path: /u01/oracle/product/12201
Software owner: oracle

Destination server: db-server2.oric.no
Destination database: td2
Oracle RDBMS version 19.0
Oracle RDBMS installation path: /u01/oracle/product/19x00
Software owner: oracle

The steps in this article are based on source from the Oracle documentation available online, as well as Document ID 1276058.1 available support.oracle.com

Step 1: Download the software from otn.oracle.com

Step 2: Create a temporary directory on your servers:

su - oracle
mkdir -p /u01/oracle/tmp/software
Step 3: Upload the software to both of your servers

Step 4: Change directory to your temporary directory and unpack the GG software on each server

cd /u01/oracle/tmp/software
unzip 191004_fbo_ggs_Linux_x64_shiphome.zip
Step 5: Start an X-server on your client, like Xming

Step 6: Configure your servers to forward X-windows.
If xauth is not installed, you may need to install it as root with yum install xauth and then follow the instructions in this post

Step 7: Start the installer:

cd /u01/oracle/tmp/software/fbo_ggs_Linux_x64_shiphome/Disk1
./runInstaller

Follow the installation wizard. It is a very simple procedure consisting of 4 screens. The installation will be identical on both serveres except for the first screen, which needs to be adapted to the database version running on the server you're installing on:


When the installation is finished, you will see lots of subdirectories in the Golden Gate installation directory, such as dirchk,dirdat,dirprm etc

Step 8 (optional): Create an environment file for the golden gate software, for convenience:
cd
vi .gg191
# add the following lines to the file .gg191:
export ORACLE_BASE=/u01/oracle export ORACLE_SID=td1 export ORACLE_UNQNAME=td1 export TNS_ADMIN=$ORACLE_HOME/network/admin export ORACLE_HOME_LISTNER=$ORACLE_HOME PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$JAVA_HOME/bin export PATH export CDPATH=.:$HOME:$ORACLE_HOME:$ORACLE_BASE:$ORACLE_BASE/admin/$ORACLE_SID:$ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID DISPLAY=localhost:10.0 export DISPLAY export GGHOME=$ORACLE_BASE/product/gg191 export ORACLE_GG=$GGHOME export LIBPATH=$ORACLE_HOME/lib:$GGHOME:/lib:/usr/lib export LD_LIBRARY_PATH=$LIBPATH export PATH=$GGHOME:$GGHOME/OPatch:$ORACLE_HOME/bin:/usr/bin:/bin:/etc:/usr/sbin:/usr/ucb:$HOME/bin:/usr/bin/X11:/sbin:/usr/java14/bin:. set -o vi clear echo "OH is : $ORACLE_HOME" echo "GG is : $GGHOME" cd $GGHOME;pwd

Step 9: Prepare the database for integrated capture mode

a) Implement forced logging and minimal supplemental logging for your source database:
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
SQL> ALTER DATABASE FORCE LOGGING;

SQL> SELECT supplemental_log_data_min, force_logging FROM v$database;

SUPPLEME FORCE_LOGGING
-------- ---------------
YES      YES

b) Enable golden gate replication in both your source and target database instance:
SQL> alter system set ENABLE_GOLDENGATE_REPLICATION=true scope=both;

System altered.

b) Create the Golden Gate user:
create bigfile tablespace ogg_data 
datafile  '/oradata/ogg_data.dbf' SIZE 128M AUTOEXTEND ON NEXT 32M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;

CREATE USER OGG
  IDENTIFIED BY mypassword
  DEFAULT TABLESPACE OGG_DATA
  TEMPORARY TABLESPACE TEMP
  PROFILE NOEXP
  ACCOUNT UNLOCK;

-- 4 Roles for OGG
GRANT CONNECT TO OGG;
GRANT RESOURCE TO OGG;
GRANT SELECT_CATALOG_ROLE TO OGG;
ALTER USER OGG DEFAULT ROLE ALL;

GRANT ALTER ANY TABLE TO OGG;
GRANT ALTER SYSTEM TO OGG;
GRANT ALTER SESSION TO OGG;
GRANT BECOME USER TO OGG;
GRANT SELECT ANY DICTIONARY TO OGG;
GRANT SELECT ANY TABLE TO OGG;
GRANT SELECT ANY TRANSACTION TO OGG;
GRANT UNLIMITED TABLESPACE TO OGG;

-- base privileges for Oracle 11.2.0.4 and later
exec dbms_goldengate_auth.grant_admin_privilege('OGG','CAPTURE');
c) Edit both of the servers' GLOBALS file. This is done in the ggsci cli interface. The parameter added here is the GGSCHEMA, which specifies the schema containing the database objects that are owned by Oracle GoldenGate. Here I am showing how I did it:
oracle@db-server1.oric.no # ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Oct 18 2019 01:38:51
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.

GGSCI (db-server1.oric.no) 1> edit params ./GLOBALS
Add the following line, then save and close it:
GGSCHEMA ogg
d) Configure connection types for integrated processes. I will use a USERALIAS, which is based on the Oracle Credential Store Framework (CSF).
oracle@db-server1.oric.no # ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Oct 18 2019 01:38:51
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.


GGSCI (db-server1.oric.no) 1> add credentialstore

Credential store created.

GGSCI (db-server1.oric.no) 2> alter credentialstore add user OGG, password mypassword alias ggadmin

Credential store altered.

#test it
GGSCI (db-server1.oric.no) 4> dblogin useridalias ggadmin
Successfully logged into database.

Repeat the same procedure on the target host.

e) Since I want DDL to be replicated, we need to enable schema-level supplemental logging:
GGSCI (db-server1.oric.no as OGG@td1) 6> add schematrandata SCOTT
2020-02-26 10:45:12  INFO    OGG-01788  SCHEMATRANDATA has been added on schema "SCOTT".
2020-02-26 10:45:12  INFO    OGG-01976  SCHEMATRANDATA for scheduling columns has been added on schema "SCOTT".
2020-02-26 10:45:12  INFO    OGG-10154  Schema level PREPARECSN set to mode NOWAIT on schema "SCOTT".

The source server should now be ready for extract.

10. Prepare the destination database for integrated apply

a) create the Golden Gate database user.
Note: The DBA role seems to be important to make DDL replication work

create bigfile tablespace ogg_data datafile '/oradata/td2/ogg_data.dbf' SIZE 128M AUTOEXTEND ON NEXT 32M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;

CREATE USER OGG
  IDENTIFIED BY mypassword
  DEFAULT TABLESPACE OGG_DATA
  TEMPORARY TABLESPACE TEMP
  PROFILE NOEXP
  ACCOUNT UNLOCK;

-- 5 Roles for OGG
GRANT CONNECT TO OGG;
GRANT RESOURCE TO OGG;
GRANT SELECT_CATALOG_ROLE TO OGG;
GRANT DBA TO OGG;
ALTER USER OGG DEFAULT ROLE ALL;

GRANT ALTER ANY TABLE TO OGG;
GRANT ALTER SYSTEM TO OGG;
GRANT ALTER SESSION TO OGG;
GRANT BECOME USER TO OGG;
GRANT SELECT ANY DICTIONARY TO OGG;
GRANT SELECT ANY TABLE TO OGG;
GRANT SELECT ANY TRANSACTION TO OGG;
GRANT UNLIMITED TABLESPACE TO OGG;
grant insert any table to ogg;
grant update any table to ogg;
grant delete any table to ogg;

-- base privileges for Oracle 11.2.0.4 and later
exec dbms_goldengate_auth.grant_admin_privilege('OGG','apply');
exit


11. Instantiate the replication environment by running an initial load

There are different methods available for loading an empty target database with data from the source. They are
  1. Load using Data Pump
  2. Loading Data from File to Replicat
  3. Loading Data with an Oracle GoldenGate Direct Load
  4. Loading Data with a Direct Bulk Load to SQL*Loader

I will use method number 1 in this article. With this method, you do not have to set up any initial-load processes on the target.
You can use either a full export or an schema export.

Check that the source server is ready for instantiation:
GGSCI (db-server1.oric.no as OGG@td1) 10> INFO SCHEMATRANDATA SCOTT
2020-02-27 09:15:52  INFO    OGG-06480  Schema level supplemental logging, excluding non-validated keys, is enabled on schema "SCOTT".
2020-02-27 09:15:52  INFO    OGG-01980  Schema level supplemental logging is enabled on schema "SCOTT" for all scheduling columns.
2020-02-27 09:15:52  INFO    OGG-10462  Schema "SCOTT" have 23 prepared tables for instantiation.
The last line shown above confirms the database has been prepared for instantiation.

12. Create a Manager parameter file on the source server

GGSCI (db-server1.oric.no as OGG@td1) 16> edit params mgr

PORT 7809
DYNAMICPORTLIST 7802-7803
PURGEOLDEXTRACTS ./dirdat/et* , USECHECKPOINTS, MINKEEPHOURS 24, FREQUENCYMINUTES 60
AUTORESTART EXTRACT *, RETRIES 3, WAITMINUTES 10, RESETMINUTES 60
AUTOSTART EXTRACT *
DOWNREPORTMINUTES 15
LAGCRITICALSECONDS 10
LAGINFOMINUTES 0
LAGREPORTMINUTES 15

13. Create an extract parameter file on the source server
GGSCI (db-server1.oric.no  as OGG@td1) 20> edit params vkext
EXTRACT vkext
--- User login
USERIDALIAS ggadmin
DISCARDFILE ./dirrpt/vkapps.dsc, APPEND
DISCARDROLLOVER AT 01:00 ON SUNDAY
EXTTRAIL ./dirdat/et
REPORT AT 01:00
STATOPTIONS REPORTFETCH
REPORTCOUNT every 10 minutes, RATE
REPORTROLLOVER AT 01:15 ON SUNDAY
--- DDL Parameters
DDL INCLUDE MAPPED
DDLOPTIONS REPORT
TABLE SCOTT.* ;

14. Create an Extract Pump parameter file on the source server
GGSCI (db-server1.oric.no  as OGG@td1) 23> edit params vkpump
EXTRACT VKPUMP
USERIDALIAS ggadmin
RMTHOST db-server2.oric.no, MGRPORT 7809
RMTTRAIL ./dirdat/rt
ddl include mapped
ddloptions report
TABLE SCOTT.* ;

15. Register the extracts:
GGSCI (db-server1.oric.no as OGG@td1) 27> dblogin useridalias ggadmin
Successfully logged into database.

GGSCI (db-server1.oric.no as OGG@td1) 28> register extract vkext, database
2020-02-27 11:59:21  INFO    OGG-02003  Extract VKEXT successfully registered with database at SCN 55440705.

GGSCI (db-server1.oric.no as OGG@td1) 29> add extract vkext, integrated tranlog, begin now
EXTRACT (Integrated) added.


GGSCI (db-server1.oric.no as OGG@td1) 31> add exttrail ./dirdat/et, extract vkext, megabytes 128
EXTTRAIL added.

GGSCI (db-server1.oric.no as OGG@td1) 32> add extract vkpump, exttrailsource ./dirdat/et
EXTRACT added.

GGSCI (db-server1.oric.no as OGG@td1) 34> add rmttrail ./dirdat/rt, extract vkpump, megabytes 128
RMTTRAIL added.

16. Create the parameter file for the manager on the target server:

GGSCI (db-server2.oric.no) 4> edit params mgr
PORT 7809
DYNAMICPORTLIST 7802-7803
PURGEOLDEXTRACTS ./dirdat/rt* , USECHECKPOINTS, MINKEEPHOURS 72
AUTORESTART REPLICAT *, RETRIES 3, WAITMINUTES 10, RESETMINUTES 60
AUTOSTART REPLICAT *
DOWNREPORTMINUTES 15
LAGCRITICALSECONDS 10
LAGINFOMINUTES 0

17. Create the parameter file for the replicat process on the target server:

GGSCI (db-server2.oric.no) 1> dblogin useridalias ggadmin
Successfully logged into database.

GGSCI (db-server2.oric.no as OGG@td2) 3> edit params vkrepl
REPLICAT vkrepl
DISCARDFILE ./dirrpt/vkrepl.dsc, APPEND
USERIDALIAS ggadmin
REPORTCOUNT EVERY 30 MINUTES, RATE
REPORTROLLOVER AT 01:00 ON SUNDAY
DISCARDROLLOVER AT 01:00 ON SUNDAY
--- DDL Parameters
DDL INCLUDE ALL
DDLOPTIONS REPORT
MAP SCOTT.*, TARGET SCOTT.*;

18. On the target, create a checkpoint table:

GGSCI (db-server2.oric.no as OGG@td2) 7> add checkpointtable ogg.chktbl

Successfully created checkpoint table ogg.chktbl.

19. On the target, start the manager

GGSCI (db-server2.oric.no as OGG@td2) 7> start mgr

20. On the target, add the replicate process and trail files:

GGSCI (db-server2.oric.no as OGG@td2) 13> add replicat vkrepl, integrated, exttrail ./dirdat/rt, checkpointtable ogg.chktbl
REPLICAT (Integrated) added.

21. Start all of the Golden Gate processes on both servers before target instantiation:

# source
GGSCI (db-server1.oric.no as OGG@td1) 15> start mgr

# target
GGSCI (db-server2.oric.no as OGG@td2) 15> start mgr
If this doesn't bring up all processes, check the file $GGHOME/ggserr.log for errors. Correct the errors as indicated. You could see a number of errors at this point, from a misspelled keyword, an incorrectly specified group name, to an incorrectly specified port number.

When ready to try again, you can issue start of individual golden gate processes, like this:
GGSCI (db-server1.oric.no as OGG@td1) 15> start extract *
or
GGSCI (db-server1.oric.no as OGG@td1) 15> start extract vkext | vkpump

20. On the source server db-server1.oric.no, export the schema I'd like replicated:


a) create a directory called DATA_PUMP_DIR
create or replace directory DATA_PUMP_DIR as '/data/dpdir';
grant read,write on directory DATA_PUMP_DIR to system;

b) create a parameter file for data pump export. Add the following lines to the file expdp_instantinate.par
userid=system
directory=DATA_PUMP_DIR
schemas=SCOTT
dumpfile=instantiate_scott.dmp
logfile=instantiate_scott.log

c) export the data
expdp parfile=instantinate.par


21. On the target server create a directory similar to he one in step 20a.

22. Transfer the dumpfile to the target server db-server2.oric.no

23. Create a parameter file for data pump import. Add the following lines to the file impdp_instantinate.par:

userid=system
directory=DATA_PUMP_DIR
dumpfile=instantiate_scott.dmp
logfile=imp_instantiate_scott4.log
job_name=instantiate_scott
remap_tablespace=DOCS_2014:USERS
remap_tablespace=DOCS_2015:USERS
remap_tablespace=DOCS_2016:USERS
remap_tablespace=SCOTT_DATA:USERS
remap_tablespace=DOCS_DATA:USERS
remap_tablespace=LOB_DATA:USERS
table_exists_action=SKIP
exclude=statistics

As you can see from the above directives, I am taking the opportunity to consolidate all my replicated data into one single tablespace, USERS.

24. Test your replication.
Insert a row into your source database td1, and check if's being transferred over the network to the target database t2.


Insert into COUNTRY_TABLE
   (ID, DYEAR, COUNTRY, CREATED, CREATED_BY, 
    LAST_CHANGED, LAST_CHANGED_BY, VERSION)
 Values
   ('443hy70-s421-11x5-8031-0054567837649', 2018, 'Japan', TO_TIMESTAMP('02/01/2018 00:00:00,000000','DD/MM/YYYY HH24:MI:SS,FF'), 'BOB',    TO_TIMESTAMP('07/03/2019 10:56:18,563307','DD/MM/YYYY HH24:MI:SS,FF'), 'JIM', 4);
COMMIT;

Tail of the $GGHOME/ggserr.log file on the target server db-server2.oric.no shows:
2020-02-27T15:14:27.981+0100  INFO    OGG-02243  Oracle GoldenGate Delivery for Oracle, vkrepl.prm:  Opened trail file /u01/oracle/product/gg191/dirdat/rt000000000 at 2020-02-27 15:14:27.981153.
2020-02-27T15:14:27.981+0100  INFO    OGG-03506  Oracle GoldenGate Delivery for Oracle, vkrepl.prm:  The source database character set, as determined from the trail file, is UTF-8.
2020-02-27T15:14:27.981+0100  INFO    OGG-06506  Oracle GoldenGate Delivery for Oracle, vkrepl.prm:  Wildcard MAP resolved (entry SCOTT.*): MAP "SCOTT"."COUNTRY_TABLE", TARGET SCOTT."COUNTRY_TABLE".
2020-02-27T15:14:27.992+0100  INFO    OGG-02756  Oracle GoldenGate Delivery for Oracle, vkrepl.prm:  The definition for table SCOTT.COUNTRY_TABLE is obtained from the trail file.
2020-02-27T15:14:27.992+0100  INFO    OGG-06511  Oracle GoldenGate Delivery for Oracle, vkrepl.prm:  Using following columns in default map by name: ID, DYEAR, COUNTRY, CREATED, CREATED_BY, LAST_CHANGED, LAST_CHANGED_BY, VERSION, DOCUMENT, PERIOD.
2020-02-27T15:14:27.992+0100  INFO    OGG-10155  Oracle GoldenGate Delivery for Oracle, vkrepl.prm:  Instantiation CSN filtering is enabled on table SCOTT.COUNTRY_TABLE at CSN 55,489,598.
2020-02-27T15:14:27.992+0100  INFO    OGG-06510  Oracle GoldenGate Delivery for Oracle, vkrepl.prm:  Using the following key columns for target table SCOTT.COUNTRY_TABLE: ID.

When checking the target database, the inserted row has indeed been transferred.

Tuesday, February 25, 2020

How to debug an ssh connection


ssh -X -v myserver.mydomain.com

You can also use -vv and -vvv for more detailed debug information.

Thursday, February 20, 2020

What does the SELECT ... FOR UPDATE statement do?



From the documentation:

The FOR UPDATE clause lets you lock the selected rows so that other users cannot lock or update the rows until you end your transaction. You can specify this clause only in a top-level SELECT statement, not in subqueries.

Some examples here

Used in PL/SQL:

The SELECT statement with the FOR UPDATE clause (SELECT FOR UPDATE statement) selects the rows of the result set and locks them. SELECT FOR UPDATE lets you base an update on the existing values in the rows, because it ensures that no other user can change those values before you update them.

Monday, February 17, 2020

How to export and import a schema in PostgreSQL


Schema exports and imports



There are many ways to export a schema using pg_dump:

Export the schema scott from db01, data only, no metadata (object definitions):
pg_dump db01 -n 'scott' -a | gzip > db01_scott.gz


Which is really just a shorthand for the syntax
pg_dump db01 -n 'scott' -a -f db01_scott.txt | gzip > db01_scott.gz
The -f parameter can be omitted for file based output formats, in which case the standard output is used (as shown in the first example).
The -a option dumps only the data, not the schema (data definitions). Table data, large objects, and sequence values are dumped.

Export the schema music from the database musicdb, metadata only, no actual data:
pg_dump musicdb -n 'music' -s | gzip > musicdb.music_definitions.gz
For smaller schemas you could potentially skip the compression altogether.

Export schema scott using the custom postgres format
pg_dump -v --schema=scott db01 -Fc > db01_scott.dmp

Alternatively, use the -f switch to send the output to a specific file:
pg_dump -v --schema=scott db01 -Fc  -f db01_scott.dmp
The -Fc option indicates postgres' "custom format" and is compressed by default.


Export schema scott using the "plain" format like this:
pg_dump -v --schema=scott db01  -Fp > scott.db01.sql

Alternatively, you can use the -f switch to send output to a specific file:
pg_dump -v --schema=music musicdb -Fp -f musicdb_music.txt
The -Fp means "plain" format. The output of the above operation is an text file which can be parsed by psql as an alternative way of importing. See examples below for usage.

Next, I am using the -f option and piping the resulting output directly to gzip for compression:
pg_dump -v --schema=music musicdb -f mymusic.txt | gzip > musicdb_music.txt.gz

Whichever method you used, transfer the exported data to the destination server using ssh

Import the schema "music" into the existing database "musicdb", metadata only, no actual data:
gunzip -c musicdb.music_definitions.gz | psql musicdb

Import the data for schema "music" into the existing database "musicdb", no metadata, only the actual data.
Note that this will throw errors upon import if the tables do not exist beforehand:
gunzip -c musicdb.music_actualdata.gz | psql musicdb

For import using custom format, use pg_restore instead of psql.
Import the schema "music" and the existing database "musicdb":
pg_restore -d musicdb musicdb_Fc.dmp -v

Create the database "musicdb", then import the schema "music" into it:
pg_restore -d postgres musicdb_Fc.dmp -v -C


See also my posts about how to export and import using the directory format, and how to list the contents of a custom formated export file

You can find a wrapper for pg_dump and pg_restore in the article Wrappers for pg_dump and pg_restore

The official documentation for the pg_dump utility can be found here

Friday, February 14, 2020

How to list schemas in a PostgreSQL database



Use the "\dn" or "\dn+" for more details. They will list the schemas in the database you are connected to.

When you simply type "psql" at your prompt, you will by default be connected to the "postgres" database.

Your output will be something like this:
[postgres@oric-pgdb01.oric.no ~]$ psql
psql (11.6)
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".
postgres=# \dn
       List of schemas
       Name        |  Owner
-------------------+----------
 postgres_exporter | postgres
 public            | postgres
(2 rows)

If you have multiple databases on your postgres server, you must connect to the proper one to see the schemas you're interested in.
For example:

[postgres@oric-pgdb01.oric.no ~]$ psql
psql (11.6)
Type "help" for help.

postgres=# \connect klmdb superuser 
or, suppling detailed information to psql directly on the command-line:

-h is the FQDN server
-U is the username
"klmdb" is the database name:

[postgres@oric-pgdb01.oric.no ~]$ psql -h oric-pgdb01.oric.no -U superuser klmdb
Then:
klmdb=# \dn
 klm         | klm_migrator
 public      | postgres
 klm_inv     | klm_inventory

Thursday, February 13, 2020

How to list PostgreSQL database sizes



Use the psql shortcut \l+ , like this
postgres=# \l+ gld
                                                             List of databases
 Name |      Owner      | Encoding |  Collate   |   Ctype    |           Access privileges            |  Size   | Tablespace | Description
------+-----------------+----------+------------+------------+----------------------------------------+---------+------------+-------------
 kml  | klm_master      | UTF8     | en_US.utf8 | en_US.utf8 | klm_master=CTc/klm_master             +| 1448 MB | pg_default |
      |                 |          |            |            | klm_consumer=c/klm_master+             |         |            |
      |                 |          |            |            | klm_producer=c/klm_master+             |         |            |
      |                 |          |            |            | klm_migrator=c/klm_master+             |         |            |
      |                 |          |            |            | postgres_exporter=c/klm_master         |         |            |
(1 row)


By using SQL:
postgres=# select pg_database_size('klm');
 pg_database_size
------------------
       1518559747
(1 row)

# convert to GB:
postgres=# select pg_database_size('klm')/1024/1024/1024 "gb";
 gb
----
  1
(1 row)

Tuesday, February 4, 2020

How to change configuration for your audit trail


This post is applicable from Oracle version 11.2 until present (Oracle 19 as per writing).

Check the current settings:
SELECT * 
FROM DBA_AUDIT_MGMT_CONFIG_PARAMS
ORDER BY AUDIT_TRAIL;

"PARAMETER_NAME" "PARAMETER_VALUE" "AUDIT_TRAIL"
DB AUDIT TABLESPACE AUDDATA FGA AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE 10000 FGA AUDIT TRAIL
AUDIT FILE MAX SIZE 10000 OS AUDIT TRAIL
OS FILE CLEAN BATCH SIZE 1000 OS AUDIT TRAIL
AUDIT FILE MAX AGE 5 OS AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL 1 STANDARD AUDIT TRAIL
DB AUDIT TABLESPACE AUDDATA STANDARD AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE 10000 STANDARD AUDIT TRAIL
AUDIT FILE MAX SIZE 10000 UNIFIED AUDIT TRAIL
AUDIT FILE MAX AGE 5 UNIFIED AUDIT TRAIL
DB AUDIT TABLESPACE AUDDATA UNIFIED AUDIT TRAIL
AUDIT WRITE MODE IMMEDIATE WRITE MODE UNIFIED AUDIT TRAIL
AUDIT FILE MAX SIZE 10000 XML AUDIT TRAIL
AUDIT FILE MAX AGE 5 XML AUDIT TRAIL
OS FILE CLEAN BATCH SIZE 1000 XML AUDIT TRAIL


I will now change the DBMS_AUDIT_MGMT.OS_FILE_MAX_AGE for the XML AUDIT TRAIL from the default 5 days to 2 days.
This is the maximum age of an audit trail file before a new audit trail file gets created:
BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY(
   audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_XML,
   audit_trail_property => DBMS_AUDIT_MGMT.OS_FILE_MAX_AGE,
   audit_trail_property_value => 2
) ;
END;
/

Verify that it was set successfully:
SELECT * 
 FROM DBA_AUDIT_MGMT_CONFIG_PARAMS 
 WHERE AUDIT_TRAIL='XML AUDIT TRAIL'
 AND PARAMETER_NAME='AUDIT FILE MAX AGE';

"PARAMETER_NAME" "PARAMETER_VALUE" "AUDIT_TRAIL"
AUDIT FILE MAX AGE 2 XML AUDIT TRAIL

For more examples, check the Oracle Documentation