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'.

How to fix error SYS.DBMS_BACKUP_RESTORE version 18.11.00.00 in TARGET database is not current

Even if you're not using a recovery catalog, you may encounter this issue if you have recently patched your database. Simply by connecting to the target database as sysdba, you can verify that you need to update your internal packages:

oracle@myserver.mydomain.com:[proddb01]# rman target / nocatalog

Recovery Manager: Release 18.0.0.0.0 - Production on Wed Dec 16 13:51:43 2020
Version 18.12.0.0.0

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

PL/SQL package SYS.DBMS_BACKUP_RESTORE version 18.11.00.00 in TARGET database is not current
PL/SQL package SYS.DBMS_RCVMAN version 18.11.00.00 in TARGET database is not current
connected to target database: PRODDB01 (DBID=1234567890)
using target database control file instead of recovery catalog

Solution: Follow Doc ID 888818.1: "Rman 06190 Connecting to target database after upgrade" In short, connect to the database as sysdba, and run the following scripts:

SQL> @$ORACLE_HOME/rdbms/admin/dbmsrman.sql
SQL> @$ORACLE_HOME/rdbms/admin/prvtrmns.plb   
SQL> @$ORACLE_HOME/rdbms/admin/dbmsbkrs.sql 
SQL> @$ORACLE_HOME/rdbms/admin/prvtbkrs.plb

How to view the contens of a zip file without extracting it

Method 1:

zip -sf myzipfile.zip

Method 2:

zipinfo myzipfile.zip

How to mark an existing SQL Plan baseline as fixed

Thanks a lot to Tim Hall for providing this simple example:

SET SERVEROUTPUT ON
DECLARE
  l_plans_altered  PLS_INTEGER;
BEGIN
  l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
    sql_handle      => 'SQL_7ff180a4583f257d',
    plan_name       => 'SQL_PLAN_7zwc0njc3y9bx2c993bf4',
    attribute_name  => 'fixed',
    attribute_value => 'YES');

  DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
END;
/

The details for the plan baselines can be found like this:

SELECT sql_handle,plan_name,origin,enabled,accepted,fixed
FROM dba_sql_plan_baselines
WHERE plan_name ='SQL_PLAN_7zwc0njc3y9bx2c993bf4';

Result:

SQL_HANDLE	PLAN_NAME	ORIGIN	ENABLED	ACCEPTED	FIXED
SQL_7ff180a4583f257d	SQL_PLAN_7zwc0njc3y9bx2c993bf4	AUTO-CAPTURE	YES	YES	YES

After fixing the plan, all the plan alternatives for the SQL ID can be seen below:

SELECT TO_CHAR(LAST_ACTIVE_TIME, 'dd.mm.yyyy hh24:mi') "last active",
       SQL_ID,
       PLAN_HASH_VALUE,
       SQL_PLAN_BASELINE "BASELINE", 
       SQL_PROFILE,
       IS_RESOLVED_ADAPTIVE_PLAN,
       CHILD_NUMBER AS "Child Num",
       ELAPSED_TIME,
       EXECUTIONS,
       ROUND(ELAPSED_TIME/1000000) "duration (sec)"
       ,CASE WHEN EXECUTIONS > 0 THEN
        ROUND( (ELAPSED_TIME/EXECUTIONS)/1000000, 1)
        ELSE
            NULL
       END "sec per exe"
FROM V$SQL 
WHERE SQL_ID in('2mympbsn3r4rk')
ORDER BY sql_id,LAST_ACTIVE_TIME DESC;

Result:

last active	SQL_ID	PLAN_HASH_VALUE	BASELINE	SQL_PROFILE	IS_RESOLVED_ADAPTIVE_PLAN	Child Num	ELAPSED_TIME	EXECUTIONS	duration (sec)	sec per exe
04.12.2020 09:49	2mympbsn3r4rk	480132689	SQL_PLAN_7zwc0njc3y9bx3bf43977			1	612838711	116	613	5,3
04.12.2020 09:49	2mympbsn3r4rk	3102497174	SQL_PLAN_7zwc0njc3y9bx2c993bf4		Y	2	203961	191	0	0
04.12.2020 08:49	2mympbsn3r4rk	480132689	SQL_PLAN_7zwc0njc3y9bx3bf43977			0	2247452482	30	2247	74,9