Minimalistic Oracle

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"

Tuesday, December 10, 2019

What is the Asynchronous Global Index Maintenance introduced in Oracle 12.1?

Asynchronous Global Index Maintenance, also called Deferred Global Index Maintenance, means that Oracle will maintain global indexes during the nightly maintenance window, when you perform operations on a partitioned tables that use a global index. Oracle has introduced this in an effort to make online operations less disruptive.

Examples of such operations are

* DROP PARTITION
* TRUNCATE PARTITION
* MOVE PARTITION (although this operation is not listed in the Oracle 12.1 documentation)

The execution of the index maintenance is done through the PL/SQL program PMO_DEFERRED_GIDX_MAINT, which purpose is to clear orphan data from global indexes generated during partition maintenance operations.

The program can be verified through DBA_SCHEDULER_PROGRAMS:

SELECT OWNER,PROGRAM_NAME,PROGRAM_TYPE,PROGRAM_ACTION,ENABLED
FROM DBA_SCHEDULER_PROGRAMS
WHERE PROGRAM_NAME='PMO_DEFERRED_GIDX_MAINT';

OWNER	PROGRAM_NAME	PROGRAM_TYPE	PROGRAM_ACTION	ENABLED
SYS	PMO_DEFERRED_GIDX_MAINT	PLSQL_BLOCK	dbms_part.cleanup_gidx_internal( noop_okay_in => 1);	TRUE

It is executed trough the scheduler every night:

select schedule_name,repeat_interval
from DBA_SCHEDULER_SCHEDULES
where schedule_name='PMO_DEFERRED_GIDX_MAINT_SCHED';

SCHEDULE_NAME	REPEAT_INTERVAL
PMO_DEFERRED_GIDX_MAINT_SCHED	FREQ=DAILY; BYHOUR=02; BYMINUTE=0; BYSECOND=0

It is executed through the job PMO_DEFERRED_GIDX_MAINT_JOB:

SELECT owner,job_name,program_name,schedule_name,enabled,state,run_count,to_char(last_start_date,'dd.mm.yyyy hh24:mi') "start",stop_on_window_close
FROM DBA_SCHEDULER_JOBS
WHERE PROGRAM_NAME='PMO_DEFERRED_GIDX_MAINT';

OWNER	JOB_NAME	PROGRAM_NAME	SCHEDULE_NAME	ENABLED	STATE	RUN_COUNT	start	STOP_ON_WINDOW_CLOSE
SYS	PMO_DEFERRED_GIDX_MAINT_JOB	PMO_DEFERRED_GIDX_MAINT	PMO_DEFERRED_GIDX_MAINT_SCHED	TRUE	RUNNING	1328	10.12.2019 02:00	FALSE

If the job is not finished within a day, is it possible to run PMO_DEFERRED_GIDX_MAINT with parallelism, as specified in the Oracle Support Note "Is It Possible To Execute The Job PMO_DEFERRED_GIDX_MAINT_JOB With Parallel Degree? (Doc ID 2126370.1)".

An example from my own experience is that the following command

ALTER TABLE SALES
MOVE PARTITION P_2019_02 ONLINE
TABLESPACE DATA2
ROW STORE COMPRESS ADVANCED
PARALLEL 4;

Triggered the statement:

ALTER INDEX "SH"."SALES_IDX1" COALESCE CLEANUP;

I have found that I sometime need to stop an job that's running through the maintenance window, and into office hours.
In that case, I've used the procedure dbms_scheduler.stop_job, like this:

BEGIN
  DBMS_SCHEDULER.STOP_JOB('SYS.PMO_DEFERRED_GIDX_MAINT_JOB', 'TRUE');
END;
/

If you need to find out which indexes that are due for coalescing, use this query:

SELECT owner,index_name,orphaned_entries
FROM   dba_indexes
where owner ='SCOTT'
and orphaned_entries='YES'
ORDER BY 1;

Tuesday, December 3, 2019

How an incorrect password file format can stop the redo apply process (MRP0) on standby database

Error in dgmgrl shows:

DGMGRL> show configuration;

Configuration - DGConfig1

  Protection Mode: MaxPerformance
  Members:
  proddb01   - Primary database
    stby02- Physical standby database
    stby01 - Physical standby database
      Error: ORA-16810: multiple errors or warnings detected for the member

Fast-Start Failover: DISABLED

Configuration Status:
ERROR   (status updated 36 seconds ago)

Checking the database throwing error:

DGMGRL> show database stby01

Database - stby01

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          21 hours 43 minutes 37 seconds (computed 0 seconds ago)
  Average Apply Rate: 5.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    pipat

  Database Error(s):
    ORA-16766: Redo Apply is stopped

  Database Warning(s):
    ORA-16853: apply lag has exceeded specified threshold

Try restarting redo apply:

DGMGRL>  edit database 'stby01'  set state='APPLY-OFF';
Succeeded.
DGMGRL>  edit database 'stby01'  set state='APPLY-ON';
Succeeded.

The alert log reports:

2019-12-03T11:33:22.214114+01:00
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT  NODELAY
2019-12-03T11:33:22.270642+01:00
Attempt to start background Managed Standby Recovery process (proddb01)
Starting background process MRP0
2019-12-03T11:33:22.312794+01:00
MRP0 started with pid=105, OS id=20354
2019-12-03T11:33:22.333315+01:00
MRP0: Background Managed Standby Recovery process started (proddb01)
2019-12-03T11:33:27.472196+01:00
 Started logmerger process
2019-12-03T11:33:27.599549+01:00
Managed Standby Recovery starting Real Time Apply
2019-12-03T11:33:27.801888+01:00
Parallel Media Recovery started with 4 slaves
2019-12-03T11:33:28.279378+01:00
Media Recovery Log /u04/fra/STBY01/archivelog/2019_12_02/o1_mf_1_121201__y2thfwyz_.arc
2019-12-03T11:33:28.318586+01:00
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT  NODELAY
MRP0: Background Media Recovery terminated with error 46952
2019-12-03T11:33:28.372536+01:00
Errors in file /orasoft/diag/rdbms/stby01/proddb01/trace/proddb01_pr00_20395.trc:
 ORA-46952: standby database format mismatch for password file '/orasoft/product/122/dbs/orapwproddb01'
Managed Standby Recovery not using Real Time Apply
Recovery interrupted!
Recovered data files to a consistent state at change 826857150
2019-12-03T11:33:28.447080+01:00
2019-12-03T11:33:28.554534+01:00
MRP0: Background Media Recovery process shutdown (proddb01)

The password file is of an older version and should be recreated in order to ressume log apply. How to do this is outlined in one of my previous posts, available here.

After you have done this, restart redo apply again with

DGMGRL>  edit database 'stby01'  set state='APPLY-ON';
Succeeded.

Put a tail on the standby database's alert log and you'll see that the outstanding logs are cherned through quicly.