How to turn off output generated by psql

I guess this could be viewed as the equivalent to oracles "set" statements in sqlplus, for example "set heading off verify off feedback off echo off":

proddb01=# \dn
           List of schemas
           Name           |  Owner
--------------------------+----------
 sales                    | postgres
 hr                       | postgres
 manufacturing            | postgres
 public                   | postgres
 (4 rows)

Turn off unneccessary output like this:

proddb01=# \t
Tuples only is on.

Try again:

proddb01=# \dn
 sales                    | postgres
 hr                       | postgres
 manufacturing            | postgres
 public                   | postgres

How to extract estimated number of rows from a PostgreSQL table

prod=# SELECT reltuples::BIGINT AS estimate FROM pg_class WHERE relname='yourtable';
 estimate
----------
 42223028
(1 row)

To include the name of the tables in case you want to check several tables in one go:

prod=# select relname,reltuples::BIGINT AS estimate FROM pg_class WHERE relname in ('mytable','yourtable');

         relname         | relowner | estimate
-------------------------+----------+----------
            mytable      |    16724 |        0
          yourtable      |    16724 |        0

Unlike Oracle, PostgreSQL is converting all strings to lowercase, so you should not use WHERE relname='YOURTABLE', but stick to lower case.

Find duplicate file names

You can find duplicate file names, on different file systems, by using this query:

set lines 200
col "file_name" format a30
col "tablespace" format a30

set trimspool on
spool duplicates.lst
alter session set nls_language='american';

select t.name "tablespace",
trim(
            substr(f.name,
                (instr(f.name,'/', -1, 1) +1)
                )
               ) "file_name", count(*)
from v$datafile f join v$tablespace t
on (f.ts# = t.ts#)
group by t.name,
         trim(
            substr(f.name,
                (instr(f.name,'/', -1, 1) +1)
                )
               )
having count(*) > 1;



exit

How to manually register missing logsequences on a standby database

I have previously documented how to identify gaps in your standby database's log sequence. See these posts:

How to check if your physical standby database is applying logs or not
How to find out if your standby database lags behind the primary database
How to find the last archivelog received and applied in a standby database

After a successful rescue operation of my standby database, I had a 3-day lag behind the primary. It's easy to identified these using the data guard broker command below:

show database "prod_stby" RecvQEntries

Output from this command was (abbreviated):

STANDBY_RECEIVE_QUEUE
              STATUS     RESETLOGS_ID  THREAD   LOG_SEQ       TIME_GENERATED       TIME_COMPLETED        FIRST_CHANGE#         NEXT_CHANGE#       SIZE (KBs)
         NOT_APPLIED        894886266  1    372460  01/28/2021 16:46:01  01/28/2021 16:47:35        6196037493227        6196037506094            20840
         NOT_APPLIED        894886266  1    372462  01/28/2021 17:02:35  01/28/2021 18:31:30        6196037647447        6196038652945          1350187
         NOT_APPLIED        894886266  1    372463  01/28/2021 18:31:30  01/28/2021 19:09:06        6196038652945        6196039875468          1051704
         .
         .
         .

The logfiles were phyically present in the Flash Recovery Area:

cd /fra/PROD_STBY/archivelog
find . -name "*372108*"
./2021_01_25/o1_mf_1_372108_j0x8j1fc_.arc

There are two ways to inform the standby database about the presence of the logfile:

1. RMAN.
On the standby database:

rman target /

Verify that the standby database does not recognize the archivelog:

list archivelog sequence between 372106 and 372107;

using target database control file instead of recovery catalog
List of Archived Log Copies for database with db_unique_name PROD_STBY
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
371883  1    372106  A 25-JAN-21
        Name: /fra/PROD_STBY/archivelog/2021_01_25/o1_mf_1_372106_j0x6x0cj_.arc

The output above confirms that sequence 372106 exists, and that sequence 372107 does not. 

To catalog the missing file:

RMAN> catalog start with '/fra/PROD_STBY/archivelog/2021_01_25/o1_mf_1_372107_j0x7k8lh_.arc';

using target database control file instead of recovery catalog
searching for all files that match the pattern /fra/PROD_STB//archivelog/2021_01_25/o1_mf_1_372107_j0x7k8lh_.arc

List of Files Unknown to the Database
=====================================
File Name: /fra/PROD_STB/archivelog/2021_01_25/o1_mf_1_372107_j0x7k8lh_.arc

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /fra/PROD_STB/archivelog/2021_01_25/o1_mf_1_372107_j0x7k8lh_.arc

Confirm again, and you'll see that the new files is registered:

RMAN> list archivelog sequence between 372106 and 372107;

List of Archived Log Copies for database with db_unique_name PROD_STBY
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
371883  1    372106  A 25-JAN-21
        Name: /fra/PROD_STBY/archivelog/2021_01_25/o1_mf_1_372106_j0x6x0cj_.arc

371956  1    372107  A 25-JAN-21
        Name: /fra/PROD_STBY/archivelog/2021_01_25/o1_mf_1_372107_j0x7k8lh_.arc

If the number of logfiles missing is large, use a shortcut to register them all:

RMAN> catalog start with '/fra/PROD_STBY/archivelog/2021_01_25';

The above command will register all logfiles in the directory /fra/PROD_STBY/archivelog/2021_01_25

 
2. sqlplus:

SQL> alter database register logfile '/fra/PROD_STBY/archivelog/2021_01_25/o1_mf_1_372107_j0x7k8lh_.arc';

If you tail the alert log of the database, you'll see that the standby database quickly picks up the missing logfiles.

What privileges are needed for flashback queries?

If the table is in your own schema, no extra privileges are needed. If the table is in a schema different from your own, you need object privileges to flashback the table:

grant flashback on scott.emp to jim;

or you can grant user jim a system privilege to flashback any table:

grant flashback any table to jim;

How to disable a scheduler job in a another schema

Use the syntax

connect system
set serveroutput on
EXECUTE DBMS_SCHEDULER.DISABLE(name=>'SCOTT.UPDATE_COMMISIONS');

You cannot login as SYS to perform this operation, you need to be logged in as another user with the required preivileges (such as SYSTEM, in this case). Documented here

Comparison between default traditional auditing and default unified auditing

Applicable to Oracle Database versions 12.1 and onwards. By default, the databases are created with "Mixed-Mode" auditing enabled. This means that the unified auditing polices ORA_SECURECONFIG og ORA_LOGON_FAILURE are enabled. These policies actually covers a lot of what any DBA would typically want to audit, and overlaps the default (tradtitional) auditing implemented by running $ORACLE_HOME/rdbms/admin/secconf.sql.

Here is a table comparing what the two default settings under each auditing strategy:

My conclusion is that unless your customer specifically wants to continue with traditional auditing, take the opportunity to migrate to Unified Auditing policies implmemented in "Mixed-Mode" auditing. In such cases you should also disable the default traditional auditing by setting the parameter audit_trail to 'NONE'.