Oracle vs PostgreSQL replication terminology

Functionality	Oracle	PostgreSQL
A standby server that can accept connections and serves read-only queries	Active Data Guard	Hot Standby
A standby server that cannot be connected to until it is promoted to a master server	Physical Standby Server	Warm Standby
A data-modifying transaction is not considered committed until all servers have committed the transaction.	Maximum Availability	Synchronous
Allow some delay between the time of a commit and its propagation to the other servers. Some transactions might be lost in the switch to a backup server load balanced servers might return slightly stale results.	Maximum Performance	Asynchronous
Do not allow any transactions to be unprotected at any time	Maximum Protection

PostgreSQL 11 dokumentation here

Oracle 12cR2 documentation here

Wrappers for pg_dump and pg_restore

Export:

#pg_dump dumps a database as a text file or to other formats.
#!/bin/bash
export SCRIPT_NAME=`basename $0`
export HOST=`uname -n`
export TS=`date +\%m.\%d.\%y\_%H_%M_%S`
export RUN_DATE=`date +\%m.\%d.\%y`
export RUN_DIR=.
export LOG_DIR=/tmp
export DUMPDIR=/pgdata/export
export JOB_NAME="ExportPGDB"
export VERSION=1.0.0
export LOGFILE=${LOG_DIR}/${SCRIPT_NAME}_${TS}.log

exec &> ${LOGFILE}
echo "Starting export job at " `date`
pg_dump -Fd musicdb -n music -v -f ${DUMPDIR}/mussikkdbexport -j 2
echo "Ending job at " `date`
exit

Import:

# pg_restore - restore a PostgreSQL database from an archive file created by pg_dump
#!/bin/bash
export SCRIPT_NAME=`basename $0`
export HOST=`uname -n`
export TS=`date +\%m.\%d.\%y\_%H_%M_%S`
export RUN_DATE=`date +\%m.\%d.\%y`
export RUN_DIR=.
export LOG_DIR=/tmp
export DUMPDIR=/pgdata/export
export JOB_NAME="ImportPGDB"
export VERSION=1.0.0
export LOGFILE=${LOG_DIR}/${SCRIPT_NAME}_${TS}.log
exec &> ${LOGFILE}
echo "Starting job at " `date`
pg_restore ${DUMPDIR}/mussikkdbexport -C -c -d postgres -j 4 -v
pg_restore ${DUMPDIR}/mussikkdbexport -c -d musicdb -j 4 -v
echo "Ending job at " `date`
exit

More articles on pg_dump and pgrestore:

How to export and import a database in PostgreSQL

How to export and import a schema in PostgreSQL

How to list the contents of a custom format PostgreSQL export file

How to export and import a schema using the directory format

How to export a single table using different format in PostgreSQL

Workaround for Automatic Diagnostic Repository Errors when executing "validate database" through Data Guard Broker

If you have a Data Guard setup and using the broker, you may see the following error when validating your setup before a switchover:

DGMGRL> validate database stb01

  Database Role:     Physical standby database
  Primary Database:  proddb01

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

  Flashback Database Status:
    proddb01:  Off
    stb01:     Off

  Managed by Clusterware:
    proddb01:  NO
    stb01:     NO
    Warning: Ensure primary database's StaticConnectIdentifier property
    is configured properly so that the primary database can be restarted
    by DGMGRL after switchover

  Automatic Diagnostic Repository Errors:
    Error                       proddb01 stb01
    System data file missing    NO       YES
    User data file missing      NO       YES

This problem and its solution is outlined in Doc Id 2300040.1 "Known issues when using "Validate database" DGMGRL command" at Oracle Support.

Cause: The issue occurs because the old health check messages were not purged properly and the command VALIDATE DATABASE signals it found a failed check.

To get rid of this warning, rename the file HM_FINDING.ams from the ADR rdbms metadata folder or move it to another folder. This is what I did on my standby server:

 cd /u01/oracle/diag/
find . -name "HM_FINDING.ams"
./rdbms/stb01/stb01/metadata/HM_FINDING.ams
./rdbms/stb01/proddb01/metadata/HM_FINDING.ams
./plsql/user_oracle/host_1804485962_107/metadata/HM_FINDING.ams

Rename or remove the files listed above and execute the "validate database" command in dgmgrl again. The message should now be gone for good.

Note that the file named HM_FINDING.ams will most likely reappear immediately after deletion. But this new copy will not cause the Data Guard Broker to throw warnings.

How to connect via sqlplus with the connect string

To connect as sys using the entire connect string, use the following syntax:

sqlplus /nolog
SQL>connect sys@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=proddb01.mydomain.com)(PORT=1534))(CONNECT_DATA=(SERVICE_NAME=proddb01.mydomain.com)(INSTANCE_NAME=proddb01)(SERVER=DEDICATED)))' as sysdba

Result:

Enter password:
Connected.
SQL> select name,database_role from v$database;

NAME      DATABASE_ROLE
--------- ----------------
proddb01  PRIMARY

How to solve message from broker Warning: standby redo logs not configured for thread 1 on primary

DGMGRL> validate database 'proddb01';

  Database Role:    Primary database

  Ready for Switchover:  Yes

  Flashback Database Status:
    tdridbt1:  Off

  Managed by Clusterware:
    tdridbt1:  NO
    Warning: Ensure primary database's StaticConnectIdentifier property
    is configured properly so that the primary database can be restarted
    by DGMGRL after switchover

DGMGRL> validate database 'stby01';

  Database Role:     Physical standby database
  Primary Database:  proddb01

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

  Flashback Database Status:
    proddb01:  Off
    stby01:    Off

  Managed by Clusterware:
    proddb01:  NO
    stby01:    NO
    Warning: Ensure primary database's StaticConnectIdentifier property
    is configured properly so that the primary database can be restarted
    by DGMGRL after switchover

  Current Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (proddb01)              (stby01)
    1         3                       2                       Insufficient SRLs

  Future Log File Groups Configuration:
    Thread #  Online Redo Log Groups  Standby Redo Log Groups Status
              (stby01)              (proddb01)
    1         3                       0                       Insufficient SRLs
    Warning: standby redo logs not configured for thread 1 on proddb01

The solution was found in Doc ID 1956103.1 "Warning: standby redo logs not configured for thread on "

This is how I corrected the situation:

On the primary, check the status of the standby redo log files:

set lines 200
col member format a50
select s.group#,s.thread#,s.sequence#,s.archived,s.status,f.member, f.type
from v$standby_log s, v$logfile f
where f.type = 'STANDBY'
and s.group# = f.group#;


    GROUP#    THREAD#  SEQUENCE# ARC STATUS     MEMBER                                             TYPE
---------- ---------- ---------- --- ---------- -------------------------------------------------- -------
         4          0          0 YES UNASSIGNED /u03/oradata/proddb01/stb_redo01.log               STANDBY
         5          0          0 YES UNASSIGNED /u03/oradata/proddb01/stb_redo02.log               STANDBY
         6          0          0 YES UNASSIGNED /u03/oradata/proddb01/stb_redo03.log               STANDBY
         7          0          0 YES UNASSIGNED /u03/oradata/proddb01/stb_redo04.log               STANDBY

Indeed, there is no standby redo logs assigned to thread 1.

First, set standby_file_management to MANUAL on both primary and standby, if not already done.

alter system set standby_file_management=MANUAL;

Still on the primary, drop the standby redo logs:

alter database drop logfile group 4;

Database altered.

alter database drop logfile group 5;

Database altered.

alter database drop logfile group 6;

Database altered.

alter database drop logfile group 7;

Database altered.

Still on the primary, add new standby redo logs:

alter database add standby logfile thread 1 group 4('/u03/oradata/proddb01/stb_redo01.log') size 2048M REUSE;
alter database add standby logfile thread 1 group 5('/u03/oradata/proddb01/stb_redo02.log') size 2048M REUSE;
alter database add standby logfile thread 1 group 6('/u03/oradata/proddb01/stb_redo03.log') size 2048M REUSE;
alter database add standby logfile thread 1 group 7('/u03/oradata/proddb01/stb_redo04.log') size 2048M REUSE;

Run the query above again, and you should see a thread number assigned to each group:

    GROUP#    THREAD#  SEQUENCE# ARC STATUS     MEMBER                                             TYPE
---------- ---------- ---------- --- ---------- -------------------------------------------------- -------
         4          1          0 YES UNASSIGNED /u03/oradata/proddb01/stb_redo01.log               STANDBY
         5          1          0 YES UNASSIGNED /u03/oradata/proddb01/stb_redo02.log               STANDBY
         6          1          0 YES UNASSIGNED /u03/oradata/proddb01/stb_redo03.log               STANDBY
         7          1          0 YES UNASSIGNED /u03/oradata/proddb01/stb_redo04.log               STANDBY

On the standby database, stop managed recovery:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Check the status:

     GROUP#    THREAD#  SEQUENCE# ARC STATUS     MEMBER                                             TYPE
---------- ---------- ---------- --- ---------- -------------------------------------------------- -------
         4          1         46 YES ACTIVE     /u03/oradata/stby01/stb_redo01.log                 STANDBY
         5          1          0 YES UNASSIGNED /u03/oradata/stby01/stb_redo02.log                 STANDBY
         6          0          0 YES UNASSIGNED /u03/oradata/stby01/stb_redo03.log                 STANDBY
         7          0          0 YES UNASSIGNED /u03/oradata/stby01/stb_redo04.log                 STANDBY        

Drop the two standby redo log groups not having been assigned a thread number:

alter database drop logfile group 6;

Database altered.

alter database drop logfile group 7;

Database altered.

Add two new groups:

alter database add standby logfile thread 1 group 6('/u03/oradata/stby01/stb_redo03.log') size 2048M REUSE;

Database altered.

alter database add standby logfile thread 1 group 7('/u03/oradata/stby01/stb_redo04.log')  size 2048M REUSE;

Database altered.

Start managed recovery again:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
Database altered.

Verify by running the same query once more:

    GROUP#    THREAD#  SEQUENCE# ARC STATUS     MEMBER                                             TYPE
---------- ---------- ---------- --- ---------- -------------------------------------------------- -------
         4          1         46 YES ACTIVE     /u03/oradata/stby01/stb_redo01.log               STANDBY
         5          1          0 NO  UNASSIGNED /u03/oradata/stby01/stb_redo02.log               STANDBY
         6          1          0 YES UNASSIGNED /u03/oradata/stby01/stb_redo03.log               STANDBY
         7          1          0 YES UNASSIGNED /u03/oradata/stby01/stb_redo04.log               STANDBY

The warning is now gone from the output of data guard broker:

DGMGRL> validate database 'stby01';

  Database Role:     Physical standby database
  Primary Database:  proddb01

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

  Flashback Database Status:
    proddb01:  Off
    stby01:  Off

  Managed by Clusterware:
    proddb01:  NO
    stby01:  NO
    Warning: Ensure primary database's StaticConnectIdentifier property
    is configured properly so that the primary database can be restarted
    by DGMGRL after switchover

Finally, remember to adjust standby_file_management to AUTO on both primary and standby databases:

alter system set standby_file_management=AUTO;

How to use active database duplication for creating a physical standby

When you use active database duplication for creating a physical standby database, make sure of the following:

1. keep your pfile used to start an auxiliary instance to a minimum. Only the following lines are needed:

db_block_size=8192
db_name='proddb01'

Of course, the db_name must be identical to the db_name of the primary database.

2. In your duplication script, make sure you the spfile contains a correct value for db_unique name:

run{
        allocate channel c1 type disk;
        allocate channel c2 type disk;
        allocate channel c3 type disk;
        allocate channel c4 type disk;
        allocate auxiliary channel stby type disk;
duplicate target database for standby from active database
spfile
SET db_unique_name='stby01'
SET db_domain='mydomain.no'
SET FAL_CLIENT='stby01'
SET FAL_SERVER='proddb01'
SET log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stby01'
SET log_archive_dest_2=''
SET control_files='/data1/oradata/stby01/control01.ctl','/data2/fra/stby01/control02.ctl'
SET STANDBY_FILE_MANAGEMENT='MANUAL'
SET LOG_ARCHIVE_MAX_PROCESSES='2'
SET local_listener='stby01.skead.no'
SET dg_broker_config_file1='/sw/oracle/product/12201/dbs/dr1stby01.dat'
SET dg_broker_config_file2='/sw/oracle/product/12201/dbs/dr2stby01.dat'
SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(stby01,proddb01)'
SET diagnostic_dest='/sw/oracle'
SET db_recovery_file_dest='/data2/fra'
SET db_file_name_convert='/data1/oradata/proddb01','/data1/oradata/stby01'
SET log_file_name_convert='/data3/oradata/proddb01','/data3/oradata/stby01'
NOFILENAMECHECK;
}

Note that the directive "spfile" used right before the individual SET commands does not mean "create spfile". It means "fetch spfile from primary". The SET commands, however, will be written to your local spfile which will then be used to start the auxiliary instance once more, before the actual cloning starts.

When the duplicaton has finished, the value of "db_name" parameter will be identical to the primary database, since we are fetching the spfile from your primary database server over the network.

What constitute "default auditing" under Unified Auditing?

Under Mixed-mode auditing and Pure Unified Auditing, the policies ORA_SECURECONFIG and ORA_LOGON_FAILURES are audited by default.

From the documentation:

ORA_SECURECONFIG audits the same default audit settings from Oracle Database Release 11g.
It tracks the use of a number of privileges such as ALTER ANY TABLE, GRANT ANY PRIVILEGE, and CREATE USER. The actions that it tracks include ALTER USER, CREATE ROLE, LOGON, and other commonly performed activities.

There are a couple of other policies that are not enabled by default but certainly worth auditing. These are:

ORA_DATABASE_PARAMETER audits commonly used Oracle Database parameter settings: ALTER DATABASE, ALTER SYSTEM, and CREATE SPFILE.

ORA_ACCOUNT_MGMT audits the commonly used user account and privilege settings: CREATE USER, ALTER USER, DROP USER, CREATE ROLE, DROP ROLE,ALTER ROLE, SET ROLE, GRANT, and REVOKE.

Enabling these is as simple as executing the following SQL against the database as a privileged user:

AUDIT POLICY ORA_DATABASE_PARAMETER;
AUDIT POLICY ORA_ACCOUNT_MGMT;