How to display a run-time parameter in a postgreSQL instance

Like with Oracle, PostgreSQL has an easy way to display run-time parameters:

[postgres@oric-pg01~]$ psql
psql (11.7)
Type "help" for help.

postgres=# show log_min_duration_statement;
 log_min_duration_statement
----------------------------
 -1
(1 row)

the log_min_duration_statement parameter is documented here

How to specifiy default compression for a tablespace

CREATE BIGFILE TABLESPACE star_data DATAFILE
'/oradata/proddb01/star_data.dbf' SIZE 1024M AUTOEXTEND ON NEXT 128M MAXSIZE 12T
LOGGING
DEFAULT
TABLE COMPRESS FOR OLTP
INDEX COMPRESS ADVANCED LOW
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

Note that the syntax used for tablespaces

TABLE COMPRESS FOR OLTP

is equivalent to the ROW STORE COMPRESS ADVANCED clause of CREATE TABLE.

Read more about tablespaces with default compression attributes in the 12.2 documentation

How to compress an existing table online

The ONLINE keyword makes it simple to compress an existing table online:

alter TABLE scott.emp
move new_tablespace
row store compress advanced
online;

If you omit the new_tablespace clause, Oracle will move it to the same tablespace as it is already residing in and the table will be reorganized and compressed.

How to modify a unified auditing policy to make exceptions based on login information

The audit policy had been created as follows:

create audit policy all_select_policy actions select;

Here is how you can alter an existing policy so that the policy will make an exception for session created by user "DBAADMIN"

alter audit policy ALL_SELECT_POLICY 
condition 'SYS_CONTEXT (''USERENV'',''CURRENT_USER'') NOT IN (''DBAADMIN'')' 
evaluate per Session;

Documentation can be found here
The oracle-supplied policy ORA_LOGIN_FAILURES automatically audits all failed login attempts. You can alter it to exclude certain uninteresting connections, such as for example DBSNMP, like this:

alter audit policy ORA_LOGON_FAILURES
condition 'SYS_CONTEXT (''USERENV'',''CURRENT_USER'') NOT IN (''DBSNMP'')'
evaluate per session;

For more information about the SYS_CONTEXT function, check the official 12.2 documentation.

Remember to delete archivelogs from obsolete incarnations after a flashback database operation

I have recently been supporting a customer by setting guaranteed restore points and using these to flash the database back on their signal, typically when they need to redeploy some code for their application.

It's easy to forget that every time you open the database with the RESETLOG options, you create another incarnation of your database.
Here is what my flash recovery area looked like:

cd /fra/SALESDB/archivelogs
du -sh *
300M    2020_01_30
36G     2020_02_03
248M    2020_02_04
1.5G    2020_02_05
273M    2020_02_06
284M    2020_02_07
332M    2020_02_08
3.7G    2020_02_09
510G    2020_02_11
15G     2020_02_12
1.1G    2020_02_13
386M    2020_02_14
237M    2020_02_15
3.7G    2020_02_16
14G     2020_02_17
523G    2020_02_18
1.5G    2020_02_19
208M    2020_02_20
213M    2020_02_21
239M    2020_02_22
3.6G    2020_02_23
217M    2020_02_24
293M    2020_02_25
1.5G    2020_02_26
258M    2020_02_27
261M    2020_02_28
296M    2020_02_29
3.6G    2020_03_01
507G    2020_03_02
216M    2020_03_03
13G     2020_03_04
214M    2020_03_05
211M    2020_03_06
237M    2020_03_07
3.6G    2020_03_08
288M    2020_03_09
244M    2020_03_10
1.6G    2020_03_11
16G     2020_03_12
504G    2020_03_13

The latest incarnation was created on the 12.03.2020:

RMAN> list incarnation of database;

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       SALESDB 1626528189       PARENT  270012427233 30.01.2020 11:39:03
2       2       SALESDB 1626528189       PARENT  270012433324 30.01.2020 11:51:46
3       3       SALESDB 1626528189       PARENT  270020068677 11.02.2020 11:00:20
4       4       SALESDB 1626528189       PARENT  270020175869 13.02.2020 12:02:52
5       5       SALESDB 1626528189       PARENT  270020238995 18.02.2020 10:50:25
6       6       SALESDB 1626528189       PARENT  270020500540 02.03.2020 13:51:53
7       7       SALESDB 1626528189       CURRENT 270020646622 12.03.2020 14:18:33

This situation caused the flash recovery area to fill, since Oracle wasn't able to delete archivelogs from the previous incarnation.

I solved this by deleting archivelogs from before the last incarnation was created.

First, list the logs you want to delete:

rman target / nocatalog log=list_archlogs.txt'
RMAN> list archivelog all completed before "to_date('12.03.2020 14:18:33')";

When I check the file, these were all older logs, residing in folders dated before the last incarnation was created.

I then deleted them as follows:

delete archivelog all completed before "to_date('12.03.2020 14:18:33')";

The size of the flash recovery area has now dropped to 17% and the file listing of /fra/SALESDB/archivelogs now shows empty folders:

4.0K    2020_01_30
8.0K    2020_02_03
4.0K    2020_02_04
4.0K    2020_02_05
4.0K    2020_02_06
4.0K    2020_02_07
4.0K    2020_02_08
16K     2020_02_09
48K     2020_02_11
4.0K    2020_02_12
4.0K    2020_02_13
4.0K    2020_02_14
4.0K    2020_02_15
4.0K    2020_02_16
4.0K    2020_02_17
52K     2020_02_18
4.0K    2020_02_19
4.0K    2020_02_20
4.0K    2020_02_21
4.0K    2020_02_22
4.0K    2020_02_23
4.0K    2020_02_24
4.0K    2020_02_25
4.0K    2020_02_26
4.0K    2020_02_27
4.0K    2020_02_28
4.0K    2020_02_29
4.0K    2020_03_01
52K     2020_03_02
4.0K    2020_03_03
4.0K    2020_03_04
4.0K    2020_03_05
4.0K    2020_03_06
4.0K    2020_03_07
4.0K    2020_03_08
4.0K    2020_03_09
4.0K    2020_03_10
4.0K    2020_03_11
15G     2020_03_12
504G    2020_03_13

Workaround for error Warning: ORA-16714: the value of property LogFileNameConvert is inconsistent with the member setting

Error from data guard:

oracle@oric-dbserver01:[vegdb01]# dgmgrl /
DGMGRL for Linux: Release 18.0.0.0.0 - Production on Thu Mar 12 08:02:54 2020
Version 18.9.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected to "vegdb01"
Connected as SYSDG.
DGMGRL> show configuration;

Configuration - myConfig

  Protection Mode: MaxPerformance
  Members:
  vegdb01      - Primary database
    vegdb01_stby - Physical standby database
      Warning: ORA-16792: configurable property value is inconsistent with member setting

Fast-Start Failover: DISABLED

Configuration Status:
WARNING   (status updated 10 seconds ago)

DGMGRL> show database vegdb01_stby

Database - vegdb01_stby

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 1 second ago)
  Apply Lag:          0 seconds (computed 1 second ago)
  Average Apply Rate: 143.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    vegdb01
      Warning: ORA-16714: the value of property LogFileNameConvert is inconsistent with the member setting

Database Status:
WARNING

Solved by setting the log_file_name_convert on the standby side.
Using dgmgrl:

edit DATABASE "vegdb01_stby" SET PROPERTY LogFileNameConvert =  " "," "; 

Or set it by using sqlplus on the standby site, see this post for how to do that

How to grant access to all tables in a schema in a PostgreSQL database

PostgreSQL offers a feature that Oracle currently does not: the ability to grant access rights to all objects in a specific schema.
Here is how.

Connect to the database "klmdb" and grant user JIM DML rights + query rights to all tables in the schema "klm":

postgres-# \connect klmdb
You are now connected to database "klmdb" as user "postgres".
klmdb=# GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA klm to JIM;

or

klmdb=# GRANT ALL ON ALL TABLES IN SCHEMA KLM TO JIM;