Minimalistic Oracle

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?

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

Why is Oracle producing .aud files for internal sys-statements?

I have recently been in contact with Oracle support regarding an issue where my Oracle 18c database instance is sending audit information for internal statements, much similar to this:

Sun Jan 26 10:25:41 2020 +01:00
LENGTH : '401'
ACTION :[147] 'select /*+ opt_param('parallel_execution_enabled',
'false') EXEC_FROM_DBMS_XPLAN */ * from gv$sql_plan where 1=0'
DATABASE USER:[1] '/'
PRIVILEGE :[4] 'NONE'
CLIENT USER:[0] ''
CLIENT TERMINAL:[7] 'UNKNOWN'
STATUS:[1] '0'
DBID:[10] '1325844924'
SESSIONID:[1] '0'
USERHOST:[26] 'myhost.mydomain.com'
CLIENT ADDRESS:[0] ''
ACTION NUMBER:[1] '3'

Sun Jan 26 10:25:41 2020 +01:00
LENGTH : '375'
ACTION :[121] 'SELECT * FROM gv$sql_plan where sql_id = 'a0f1h9d5muwa6' and inst_id = 1 and child_address = hextoraw('00000004FFF16130')'
DATABASE USER:[1] '/'
PRIVILEGE :[4] 'NONE'
CLIENT USER:[0] ''
CLIENT TERMINAL:[7] 'UNKNOWN'
STATUS:[1] '0'
DBID:[10] '1325844924'
SESSIONID:[1] '0'
USERHOST:[26] 'myhost.mydomain.com'
CLIENT ADDRESS:[0] ''
ACTION NUMBER:[1] '3'

If you have migrated to Unified Auditing, Oracle states that "audit records are only expected to be generated in database tables and OS spillover files (*.bin) under audit destination path."

However, dynamic SQL statements parsed or executed using DBMS_SQL package are being audited in the conventional *.aud type OS files.

To get rid of these messages piling up in your audit_dump_dir:

alter system set audit_sys_operations=FALSE scope=spfile;
shutdown immediate
startup

If setting audit_sys_operations to FALSE is not desirable, Oracle states that you can request a patch through the following bug number:

Bug 21133343 *.aud file is generated though unified auditing=true and audit_trail=none

Note that you will see the same phenomenon under the mixed-mode or classic auditing.
Oracle does not explisitly say they will provide a patch in this case though.

Documentation from Oracle support: Doc ID 2020881.1: "OS Audit Files *.aud are Still Generated After Migrating to Unified Audit"