Tuesday, 6 July 2021

Oracle Database - 19c New Features

Inspecting the Oracle 19c documentation, following are some of the fascinating new database features for your reference.

Table of Contents

Automatic Indexing

The automatic indexing feature automates the index management tasks in an Oracle database. Automatic indexing automatically creates, rebuilds, and drops indexes in a database based on the changes in application workload, thus improving database performance. The automatically managed indexes are known as auto indexes.

Functionality

Runs the automatic indexing process in the background periodically at a predefined time interval.
Analyzes application workload, and accordingly creates new indexes and drops the existing under performing indexes to improve database performance.
Rebuilds the indexes that are marked unusable due to table partitioning maintenance operations, such as ALTER TABLE MOVE.
Provides PL/SQL APIs for configuring automatic indexing in a database and generating reports related to automatic indexing operations.

How to Configure?

Enable automatic indexing in a database and creates any new auto indexes as visible indexes, so that they can be used in SQL statements:

  
 EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');

Enable automatic indexing in a database, but creates any new auto indexes as invisible indexes, so that they cannot be used in SQL statements:

  
 EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','REPORT ONLY');

Disable automatic indexing in a database, so that no new auto indexes are created, and the existing auto indexes are disabled:

  
 EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','OFF');

Set retention period for unused auto indexes to 90 days:

  
 EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_AUTO', '90');

Set retention period for unused non-auto indexes to 60 days:

   
 EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_MANUAL', '60');

Define tablespace of TBSAUTO to store auto indexes:

   
 EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE', 'TBSAUTO');

Allocates 5% of the tablespace for auto indexes:

  
 EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SPACE_BUDGET', '5');

Generating Reports

Generate a report about the automatic indexing operations for last 24 hours in plain txt(default) format:

 
 set long 9999999 pages 50000 lines 500
 col xxxx for a300
 SELECT dbms_auto_index.Report_activity() as xxxx 
 FROM dual;

Make a report about the automatic indexing operations for specific timestamp in HTML format:

 
 set long 9999999 pages 50000 lines 500
 col xxxx for a300
 SELECT dbms_auto_index.Report_activity(activity_start => systimestamp-1, 
					activity_end => systimestamp, 
					type => 'HTML',
					section => 'ALL') as xxxx
 FROM dual;

Create a report about the last automatic indexing operation:

  
 set long 9999999 pages 50000 lines 500
 col xxxx for a300
 SELECT dbms_auto_index.Report_last_activity() as xxxx 
 FROM dual;

Behind the Scenes!

 
 col DESCRIPTION for a30
 col ADVISOR_NAME for a25
 col task_name for a30
 select TASK_NAME,DESCRIPTION,ADVISOR_NAME,CREATED,LAST_MODIFIED,LAST_EXECUTION,EXECUTION_START,EXECUTION_END,STATUS,PROGRESS_METRIC
 from dba_advisor_tasks;

 TASK_NAME                      DESCRIPTION                    ADVISOR_NAME              CREATED              LAST_MODIFIED
 ------------------------------ ------------------------------ ------------------------- -------------------- ---------------------  
 SYS_AUTO_SPM_EVOLVE_TASK       Automatic SPM Evolve Task      SPM Evolve Advisor        17-apr-2019 01:32:48 17-apr-2019 01:32:48
 SYS_AI_SPM_EVOLVE_TASK         Automatic SPM Evolve Task      SPM Evolve Advisor        17-apr-2019 01:32:48 17-apr-2019 01:32:48
 SYS_AI_VERIFY_TASK                                            SQL Performance Analyzer  17-apr-2019 01:32:48 05-aug-2019 19:41:36
 SYS_AUTO_INDEX_TASK                                           SQL Access Advisor        17-apr-2019 01:32:48 05-aug-2019 19:41:37
 AUTO_STATS_ADVISOR_TASK                                       Statistics Advisor        17-apr-2019 01:32:53 17-apr-2019 01:56:11
 INDIVIDUAL_STATS_ADVISOR_TASK                                 Statistics Advisor        17-apr-2019 01:32:53 17-apr-2019 01:32:53

 Task Run for Every 15 minutes

 col TASK_NAME for a30
 col EXECUTION_NAME for a30
 set lines 200 pages 5000
 select TASK_NAME,EXECUTION_NAME,EXECUTION_START,EXECUTION_END,STATUS,REQUESTED_DOP,ACTUAL_DOP
 from dba_advisor_executions 
 where TASK_NAME in ('SYS_AUTO_INDEX_TASK','SYS_AI_SPM_EVOLVE_TASK','SYS_AI_VERIFY_TASK')
 order by EXECUTION_START;

 TASK_NAME                      EXECUTION_NAME                 EXECUTION EXECUTION STATUS      REQUESTED_DOP ACTUAL_DOP
 ------------------------------ ------------------------------ --------- --------- ----------- ------------- ----------
 SYS_AUTO_INDEX_TASK            SYS_AI_2019-08-05/19:26:07     05-AUG-19 05-AUG-19 COMPLETED               0          1
 SYS_AUTO_INDEX_TASK            SYS_AI_2019-08-05/19:41:17     05-AUG-19 05-AUG-19 COMPLETED               0          1
 SYS_AI_VERIFY_TASK             SYS_AI_2019-08-05/19:41:17_C   05-AUG-19 05-AUG-19 COMPLETED               0          1
 SYS_AI_VERIFY_TASK             SYS_AI_2019-08-05/19:41:17_E1  05-AUG-19 05-AUG-19 COMPLETED               0          1
 SYS_AUTO_INDEX_TASK            SYS_AI_2019-08-05/19:56:27     05-AUG-19 05-AUG-19 COMPLETED               0          1
 SYS_AUTO_INDEX_TASK            SYS_AI_2019-08-05/20:11:36     05-AUG-19 05-AUG-19 COMPLETED               0          1

 Internal Parameters
 
 select * 
 From SMB$CONFIG 
 where PARAMETER_NAME like '%INDEX%';

 PARAMETER_NAME                           PARAMETER_VALUE LAST_UPDATED                   UPDATED_BY   PARAMETER_DATA
 ---------------------------------------- --------------- ------------------------------ ------------ --------------------
 AUTO_INDEX_SCHEMA                                      0                                             
 AUTO_INDEX_DEFAULT_TABLESPACE                          0
 AUTO_INDEX_SPACE_BUDGET                               50
 AUTO_INDEX_REPORT_RETENTION                           31
 AUTO_INDEX_RETENTION_FOR_AUTO                          0                                             373
 AUTO_INDEX_RETENTION_FOR_MANUAL                        0
 AUTO_INDEX_MODE                                        0 05-AUG-19 07.10.13.000000 PM   SYS          IMPLEMENT
 _AUTO_INDEX_TRACE                                      0
 _AUTO_INDEX_TASK_INTERVAL                            900
 _AUTO_INDEX_TASK_MAX_RUNTIME                        3600
 _AUTO_INDEX_IMPROVEMENT_THRESHOLD                     20
 _AUTO_INDEX_REGRESSION_THRESHOLD                      10
 _AUTO_INDEX_ABSDIFF_THRESHOLD                        100
 _AUTO_INDEX_STS_CAPTURE_TASK                           0 05-AUG-19 07.10.13.000000 PM   SYS          ON
 _AUTO_INDEX_CONTROL                                    0
 _AUTO_INDEX_DERIVE_STATISTICS                          0                                             ON
 _AUTO_INDEX_CONCURRENCY                                1
 _AUTO_INDEX_SPA_CONCURRENCY                            1
 _AUTO_INDEX_REBUILD_TIME_LIMIT                        30
 _AUTO_INDEX_REBUILD_COUNT_LIMIT                        5
 _AUTO_INDEX_REVERIFY_TIME                             30
 AUTO_INDEX_COMPRESSION                                 0                                             OFF

 Change Task Interval from 15 minutes to 30 minutes

 exec dbms_auto_index_internal.configure('_AUTO_INDEX_TASK_INTERVAL', '1800', allow_internal => true);

 PARAMETER_NAME             PARAMETER_VALUE LAST_UPDATED                   UPDATED_BY PARAMETER_DATA
 -------------------------- --------------- ------------------------------ ---------- --------------------
 _AUTO_INDEX_TASK_INTERVAL             1800 06-AUG-19 07.02.01.000000 PM   SYS

Demo

 
 EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');

 col PARAMETER_NAME for a40
 col PARAMETER_VALUE for a15
 col LAST_MODIFIED for a20
 col MODIFIED_BY for a15
 select * From DBA_AUTO_INDEX_CONFIG;

 PARAMETER_NAME                           PARAMETER_VALUE LAST_MODIFIED                  MODIFIED_BY
 ---------------------------------------- --------------- ------------------------------ ------------ 
 AUTO_INDEX_COMPRESSION                   OFF
 AUTO_INDEX_DEFAULT_TABLESPACE
 AUTO_INDEX_MODE                          IMPLEMENT       05-AUG-19 07.10.13.000000 PM   SYS
 AUTO_INDEX_REPORT_RETENTION              31
 AUTO_INDEX_RETENTION_FOR_AUTO            373
 AUTO_INDEX_RETENTION_FOR_MANUAL
 AUTO_INDEX_SCHEMA
 AUTO_INDEX_SPACE_BUDGET                  50

 CREATE TABLE TEST(id NUMBER, name VARCHAR2(20));

 begin
 for a in 1..500000 loop
 insert into TEST values (a,'test'||to_char(a));
 end loop;
 commit;
 end;
 /
 
 set serveroutput on
 declare
 vid number;
 vname varchar2(20);
 begin
 for a in 1..500 loop
 execute immediate 'select /*+ DINESH_005 */ id,name from test where id=:a' into vid,vname using a;
 dbms_output.put_line('Data : '||vid||','||vname);
 end loop;
 end;
 /

Sample Report – Auto_Index_Report

Real-Time Statistics

Oracle Database automatically gathers online statistics during conventional DML operations.
Statistics can go stale between execution of DBMS_STATS statistics gathering jobs. By gathering some statistics automatically during DML operations, the database augments the statistics gathered by DBMS_STATS. Fresh statistics enable the optimizer to produce more optimal plans.

Parameters to control this feature

 
 NAME                                               VALUE           DESCRIPTION
 -------------------------------------------------- --------------- -----------------------------------------------------------------
 _optimizer_gather_stats_on_conventional_config     0               settings for optimizer online stats gathering on conventional DML 
 _optimizer_gather_stats_on_conventional_dml        TRUE            optimizer online stats gathering for conventional DML
 _optimizer_stats_on_conventional_dml_sample_rate   100             sampling rate for online stats gathering on conventional DML
 _optimizer_use_stats_on_conventional_config        0               settings for optimizer usage of online stats on conventional DML
 _optimizer_use_stats_on_conventional_dml           TRUE            use optimizer statistics gathered for conventional DML

Demo

Note: In My environment, this feature functioned after setting “_exadata_feature_on”=TRUE which defaults to FALSE.

 
 CREATE TABLE TEST(id NUMBER, name VARCHAR2(20));

 begin
 for a in 1..500000 loop
 insert into TEST values (a,'test'||to_char(a));
 end loop;
 commit;
 end;
 /

 begin
  DBMS_STATS.GATHER_TABLE_STATS('DINESH', 'TEST', METHOD_OPT=>'FOR ALL COLUMNS SIZE 1');
 end;
 /

 SET PAGES 5000 LINES 200
 COL TABLE_NAME for a15
 COL COLUMN_NAME FORMAT a13 
 COL NOTES FORMAT a35
 select TABLE_NAME,COLUMN_NAME,NUM_DISTINCT,SAMPLE_SIZE,LAST_ANALYZED,NOTES 
 from USER_TAB_COL_STATISTICS 
 where table_name='TEST';

 TABLE_NAME      COLUMN_NAME   NUM_DISTINCT SAMPLE_SIZE LAST_ANALYZED        NOTES
 --------------- ------------- ------------ ----------- -------------------- -------------------
 TEST            ID                  500000      500000 05-aug-2019 17:19:49
 TEST            NAME                500000      500000 05-aug-2019 17:19:49

 select TABLE_NAME,NUM_ROWS,BLOCKS,AVG_ROW_LEN,LAST_ANALYZED,NOTES 
 from USER_TAB_STATISTICS 
 where table_name='TEST';

 TABLE_NAME        NUM_ROWS     BLOCKS AVG_ROW_LEN LAST_ANALYZED        NOTES
 --------------- ---------- ---------- ----------- -------------------- -------------------------
 TEST                500000       1504          16 05-aug-2019 17:19:49

 begin
 for a in 500001..600000 loop
 insert into TEST values (a,'test'||to_char(a));
 end loop;
 commit;
 end;
 /

 SET PAGES 5000 LINES 200
 COL TABLE_NAME for a25
 COL COLUMN_NAME FORMAT a13 
 COL NOTES FORMAT a35
 select TABLE_NAME,COLUMN_NAME,NUM_DISTINCT,SAMPLE_SIZE,LAST_ANALYZED,NOTES 
 from USER_TAB_COL_STATISTICS 
 where table_name='TEST';

 TABLE_NAME      COLUMN_NAME   NUM_DISTINCT SAMPLE_SIZE LAST_ANALYZED        NOTES
 --------------- ------------- ------------ ----------- -------------------- ------------------------------ 
 TEST            ID                  500000      500000 05-aug-2019 17:19:49
 TEST            NAME                500000      500000 05-aug-2019 17:19:49
 TEST            ID                                 957 05-aug-2019 17:21:23 STATS_ON_CONVENTIONAL_DML
 TEST            NAME                               935 05-aug-2019 17:21:23 STATS_ON_CONVENTIONAL_DML

 EXEC DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;

 select TABLE_NAME,NUM_ROWS,BLOCKS,AVG_ROW_LEN,LAST_ANALYZED,NOTES,SCOPE 
 from USER_TAB_STATISTICS 
 where table_name='TEST';

 TABLE_NAME        NUM_ROWS     BLOCKS AVG_ROW_LEN LAST_ANALYZED        NOTES
 --------------- ---------- ---------- ----------- -------------------- -----------------------------------
 TEST                500000       1504          16 05-aug-2019 17:19:49
 TEST                600000       1756             05-aug-2019 17:21:23 STATS_ON_CONVENTIONAL_DML

 Insert Single Row - Plan

 explain plan for insert into test values(1000001,'test1000001');
 Explained.

 SQL> select * From table(dbms_xplan.display);
 
 PLAN_TABLE_OUTPUT
 ------------------------------------------------------------------------------------------------
 ---------------------------------------------------------------------------------
 | Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 ---------------------------------------------------------------------------------
 |   0 | INSERT STATEMENT         |      |     1 |    16 |     1   (0)| 00:00:01 |
 |   1 |  LOAD TABLE CONVENTIONAL | TEST |       |       |            |          |
 ---------------------------------------------------------------------------------

 Note
 -----
   - dynamic statistics used: statistics for conventional DML enabled

 11 rows selected.

 Insert Multiple Rows - Plan

 SQL> explain plan for insert into test select * From test where rownum <100000;
 Explained.

 SQL> select * From table(dbms_xplan.display);
 
 PLAN_TABLE_OUTPUT
 -------------------------------------------------------------------------------------------------
 Plan hash value: 3931117773
 -----------------------------------------------------------------------------------------
 | Id  | Operation                        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 -----------------------------------------------------------------------------------------
 |   0 | INSERT STATEMENT                 |      | 99999 |  1562K|   412   (1)| 00:00:01 |
 |   1 |  LOAD TABLE CONVENTIONAL         | TEST |       |       |            |          |
 |   2 |   OPTIMIZER STATISTICS GATHERING |      |   500K|  7812K|   412   (1)| 00:00:01 |
 |*  3 |    COUNT STOPKEY                 |      |       |       |            |          |
 |   4 |     TABLE ACCESS FULL            | TEST |   500K|  7812K|   412   (1)| 00:00:01 |
 -----------------------------------------------------------------------------------------

 Predicate Information (identified by operation id):
 ---------------------------------------------------

   3 - filter(ROWNUM<100000)

 16 rows selected.

 Query Plan

 SELECT /*+ DINESH_001 */ COUNT(*) 
 FROM TEST WHERE ID > 590000;

 SQL_ID  7nn4gu7s0p5yu, child number 0
 -------------------------------------
 SELECT /*+ DINESH_001 */ COUNT(*) FROM TEST WHERE ID > 590000

 Plan hash value: 1950795681
 ---------------------------------------------------------------------------
 | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 ---------------------------------------------------------------------------
 |   0 | SELECT STATEMENT   |      |       |       |   481 (100)|          |
 |   1 |  SORT AGGREGATE    |      |     1 |    13 |            |          |
 |*  2 |   TABLE ACCESS FULL| TEST |  9897 |   125K|   481   (1)| 00:00:01 |
 ---------------------------------------------------------------------------

 Predicate Information (identified by operation id):
 ---------------------------------------------------
   2 - filter("ID">590000)

 Hint Report (identified by operation id / Query Block Name / Object Alias):
 Total hints for statement: 1 (E - Syntax error (1))
 ---------------------------------------------------------------------------
   1 -  SEL$1
         E -  DINESH_001
 Note
 -----
   - dynamic statistics used: statistics for conventional DML

Active Data Guard DML Redirection

You can run DML operations on Active Data Guard standby databases. This enables you to run read-mostly applications, which occasionally execute DML’s, on the standby database.

DML operations on a standby can be transparently redirected to and run on the primary database. This includes DML statements that are part of PL/SQL blocks. The Active Data Guard session waits until the corresponding changes are shipped to and applied to the Active Data Guard standby. Read consistency is maintained during the DML operation and the standby database on which the DML is run can view its uncommitted changes. However, all the other standby database instances can view these changes only after the transaction is committed.

How to configure?

To configure automatic redirection of DML operations for all standby sessions in an Active Data Guard environment:

 
 ADG_REDIRECT_DML=TRUE

To configure automatic redirection of DML operations for the current session, use the following command:

 
 ALTER SESSION ENABLE ADG_REDIRECT_DML;

Demo

 
 Production

 select name, open_mode, controlfile_type from v$database;

 NAME      OPEN_MODE            CONTROL
 --------- -------------------- -------
 DBADB     READ WRITE           CURRENT

 begin
 for a in 1..500 loop
 insert into TEST values (a,'test'||to_char(a));
 end loop;
 commit;
 end;
 /

 PL/SQL procedure successfully completed.
 Elapsed: 00:00:00.05

 select count(*) from test;

   COUNT(*)
 ----------
     601000
 
 Here as highlighted above, inserting 500 records completes within 0.05 seconds.

 
 Standby
 
 select name, open_mode, controlfile_type from v$database;

 NAME      OPEN_MODE            CONTROL
 --------- -------------------- -------
 DBADB     READ ONLY WITH APPLY STANDBY

 show parameter redirect

 NAME                                 TYPE        VALUE
 ------------------------------------ ----------- -------  
 adg_redirect_dml                     boolean     TRUE

 select count(*) from test;

   COUNT(*)
 ----------
     601000

 begin
 for a in 1..500 loop
 insert into TEST values (a,'test'||to_char(a));
 end loop;
 commit;
 end;
 /

 PL/SQL procedure successfully completed.
 Elapsed: 00:08:18.72

 select count(*) from test;

   COUNT(*)
 ----------
     601500
 
 On Standby when same DML block is executed, it took around 8 minutes 18 seconds.  
 This is the impact of running DML over the network.

What’s happening backstairs?

While performing DML’s on Standby database, session tracing has been enabled.
Following are details from trace file.

 
 begin
 for a in 1..500 loop
 insert into TEST values (a,'test'||to_char(a));
 end loop;
 commit;
 end;
 
 call     count       cpu    elapsed       disk      query    current        rows
 ------- ------  -------- ---------- ---------- ---------- ----------  ----------
 Parse        1      0.00       0.00          0          0          0           0
 Execute      1      0.16     464.14          0          0          0           1
 Fetch        0      0.00       0.00          0          0          0           0
 ------- ------  -------- ---------- ---------- ---------- ----------  ----------  
 total        2      0.16     464.15          0          0          0           1
 
 Misses in library cache during parse: 1
 Optimizer mode: ALL_ROWS
 Parsing user id: 107
 
 Elapsed times include waiting on following events:
   Event waited on                             Times   Max. Wait  Total Waited
   ----------------------------------------   Waited  ----------  ------------
   SQL*Net message to client                       1        0.00          0.00
   SQL*Net message from client                     1       12.57         12.57
 ********************************************************************************
 
 
 SQL ID: cz467hg4s37vr Plan Hash: 0
 
 INSERT INTO TEST
 VALUES
  (:B1 ,'test'||TO_CHAR(:B1 ))
 
 
 call     count       cpu    elapsed       disk      query    current        rows
 ------- ------  -------- ---------- ---------- ---------- ----------  ----------
 Parse        1      0.01       5.97          0          0          3           0
 Execute    500      0.28       0.41          0          0          0         500
 Fetch        0      0.00       0.00          0          0          0           0
 ------- ------  -------- ---------- ---------- ---------- ----------  ----------
 total      501      0.29       6.39          0          0          3         500
 
 Misses in library cache during parse: 0
 Optimizer mode: ALL_ROWS
 Parsing user id: 107     (recursive depth: 1)
 
 Elapsed times include waiting on following events:
   Event waited on                             Times   Max. Wait  Total Waited
   ----------------------------------------   Waited  ----------  ------------
   single-task message                             1        0.11          0.11
   SQL*Net message from dblink                  1010        0.07          0.66
   SQL*Net message to dblink                    1008        0.00          0.00
   Disk file operations I/O                        1        0.00          0.00
   SQL*Net vector data to dblink                 501        0.01          0.03
   standby query scn advance                     500        1.49        463.66
 ********************************************************************************

As tinted in above section, total wait time has been consumed across “standby query scn advance“.
For event details refer below Doc ID

Wait event ‘standby query scn advance’ (Doc ID 2533359.1)

Automatic Resolution of SQL Plan Regressions

SQL plan management searches for SQL statements in the Automatic Workload Repository (AWR). Prioritizing by highest load, it looks for alternative plans in all available sources, adding better-performing plans to the SQL plan baseline. Oracle Database also provides a plan comparison facility and improved hint reporting.

Automatic SQL plan management resolves plan regressions without user intervention. For example, if high-load statements are performing sub optimally, then SQL plan management evolve advisor can locate the statements automatically, and then test and accept the best plans.

Automatic SPM Evolve Advisor

Finer granularity Supplemental Logging

Supplemental logging was designed and implemented for Logical Standby or full database replication requirements. This adds unnecessary overhead in environments where only a subset of tables is being replicated. Fine-grained supplemental logging provides a way for partial database replication users to disable supplemental logging for uninteresting tables so that even when supplemental logging is enabled in database or schema level, there is no supplemental logging overhead for uninteresting tables.

Use of this feature can significantly reduce the overhead in terms of resource usage and redo generation in case when only some of the tables in the database require supplemental logging, such as in a Golden Gate partial replication configuration.

If both source and mining database are at redo compatibility 19 or higher and the value of enable_goldengate_replication is TRUE, then Oracle Database 19c and higher provides an advantage of reduced supplemental logging overhead for Oracle GoldenGate.

How to enable

 
 SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA SUBSET DATABASE REPLICATION;
 ALTER DATABASE ADD SUPPLEMENTAL LOG DATA SUBSET DATABASE REPLICATION
 *
 ERROR at line 1:
 ORA-26947: Oracle GoldenGate replication is not enabled.

 SQL> alter system set enable_goldengate_replication=true;
 System altered.

 SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA SUBSET DATABASE REPLICATION;
 Database altered.

 SQL> SELECT supplemental_log_data_min, supplemental_log_data_sr from v$database; 

 SUPPLEMENTAL_LOG_DATA_MIN      SUPPLEMENTAL_LOG_DATA_SR
 ------------------------------ ------------------------------
 IMPLICIT                       YES

Way to disable

 
 SQL> ALTER DATABASE DROP SUPPLEMENTAL LOG DATA SUBSET DATABASE REPLICATION;
 ALTER DATABASE DROP SUPPLEMENTAL LOG DATA SUBSET DATABASE REPLICATION
 *
 ERROR at line 1:
 ORA-31545: cannot drop subset database replication when there is no other
 database (container) level supplemental log setting


 Note:
 If no other database-level supplemental log is explicitly enabled, this DDL would fail.  
 This is to prevent the loss of supplemental log data during replication. 
 In this case, you need to add minimal supplemental logging explicitly using the command 
 ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; 
 and then execute 
 ALTER DATABASE DROP SUPPLEMENTAL LOG DATA SUBSET DATABASE REPLICATION;


 SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; 
 Database altered. 

 SQL> ALTER DATABASE DROP SUPPLEMENTAL LOG DATA SUBSET DATABASE REPLICATION; 
 Database altered. 

 SQL> SELECT supplemental_log_data_min, supplemental_log_data_sr from v$database;

 SUPPLEMENTAL_LOG_DATA_MIN      SUPPLEMENTAL_LOG_DATA_SR
 ------------------------------ ------------------------------
 YES                            NO

Dry-Run Validation of Clusterware Upgrade

Oracle Grid Infrastructure installation wizard (gridSetup.sh) enables you to perform a dry-run mode upgrade using dryRunForUpgrade flag to check your system’s upgrade readiness.

In dry-run upgrade mode, the installation wizard performs all of the system readiness checks that it would perform in an actual upgrade and enables you to verify whether your system is ready for upgrade before you start the upgrade. This mode does not perform an actual upgrade. It helps anticipate potential problems with the system setup and avoid upgrade failures.

Steps to perform Dry-Run

AutoUpgrade for Oracle Database

AutoUpgrade enables to upgrade one or many Oracle databases at the command-line with a single command and a single configuration file.

AutoUpgrade will run the preupgrade tasks, perform automated fixups where needed, execute the database upgrade, and finish by taking care of post-upgrade tasks. It includes automatic retry and fallback, the possibility to schedule upgrades for future points in time, and the ability to set, change or remove initialization parameters as desired.

It saves time and money by upgrading hundreds of databases with one command and replacing bespoke high maintenance upgrade solutions.

Reference Doc ID: AutoUpgrade Tool (Doc ID 2485457.1)

Workflow and Stages

Demo

 
 Config File

 global.autoupg_log_dir=/home/oracle/DBADB122_UPG/DBADB122
 DBADB122.dbname=DBADB122
 DBADB122.start_time=NOW
 DBADB122.source_home=/u02/app/oracle/product/12c/dbhome_1
 DBADB122.target_home=/opt/oracle/product/19c/dbhome_1
 DBADB122.sid=DBADB122
 DBADB122.log_dir=/home/oracle/DBADB122_UPG/DBADB122
 DBADB122.upgrade_node=dinesh19c
 DBADB122.target_version=19.3
 DBADB122.run_utlrp=yes
 DBADB122.timezone_upg=yes

 
 Run PRECHECK

 $ORACLE_HOME/jdk/bin/java -jar autoupgrade.jar -config DBADB122_config.cfg -mode analyze 

 upg> status -job 100
 Progress
 -----------------------------------
 Start time:      19/08/09 16:55
 Elapsed (min):   3
 End time:        N/A
 Last update:     2019-08-09T16:58:08.991
 Stage:           PRECHECKS
 Operation:       PREPARING
 Status:          RUNNING
 Pending stages:  1
 Stage summary:
    SETUP             <1 min
    PRECHECKS         2 min (IN PROGRESS)

 Job Logs Locations
 -----------------------------------
 Logs Base:    /home/oracle/DBADB122_UPG/DBADB122/DBADB122
 Job logs:     /home/oracle/DBADB122_UPG/DBADB122/DBADB122/100
 Stage logs:   /home/oracle/DBADB122_UPG/DBADB122/DBADB122/100/prechecks
 TimeZone:     /home/oracle/DBADB122_UPG/DBADB122/DBADB122/temp

 Error Details:
 None

 upg> Job 100 completed
 ------------------- Final Summary --------------------
 Number of databases            [ 1 ]

 Jobs finished successfully     [1]
 Jobs failed                    [0]
 Jobs pending                   [0]
 ------------- JOBS FINISHED SUCCESSFULLY -------------

 
 PRECHECK completion status

 cat /home/oracle/DBADB122_UPG/DBADB122/cfgtoollogs/upgrade/auto/status/status.json
 {
   "totalJobs" : 1,
   "lastUpdateTime" : "2019-08-09 16:58:12",
   "jobs" : [ {
     "dbName" : "DBADB122",
     "jobNo" : 100,
     "logDirectory" : "/home/oracle/DBADB122_UPG/DBADB122/DBADB122/100",
     "conNumber" : 1,
     "lastUpdateTime" : "2019-08-09 16:58:12",
     "modules" : [ {
       "moduleName" : "PRECHECKS",
       "status" : 0,
       "errors" : [ ],
       "lastUpdateTime" : "2019-08-09 16:58:12"
     } ]
   } ]

 
 Upgrade Database using DEPLOY

 $ORACLE_HOME/jdk/bin/java -jar autoupgrade.jar -config DBADB122_config.cfg -mode deploy

 upg> status -job 101
 Progress
 -----------------------------------
 Start time:      19/08/09 17:42
 Elapsed (min):   94
 End time:        N/A
 Last update:     2019-08-09T19:15:36.494
 Stage:           POSTUPGRADE
 Operation:       EXECUTING
 Status:          RUNNING
 Pending stages:  1
 Stage summary:
    SETUP             <1 min
    PREUPGRADE        <1 min
    PRECHECKS         3 min
    GRP               <1 min
    PREFIXUPS         5 min
    DRAIN             <1 min
    DBUPGRADE         70 min
    POSTCHECKS        4 min
    POSTFIXUPS        8 min
    POSTUPGRADE       <1 min (IN PROGRESS) 

 Job Logs Locations
 -----------------------------------
 Logs Base:    /home/oracle/DBADB122_UPG/DBADB122/DBADB122
 Job logs:     /home/oracle/DBADB122_UPG/DBADB122/DBADB122/101
 Stage logs:   /home/oracle/DBADB122_UPG/DBADB122/DBADB122/101/postupgrade
 TimeZone:     /home/oracle/DBADB122_UPG/DBADB122/DBADB122/temp

 Additional information
 -----------------------------------
 Details:
 Update of oratab [DBADB122]
        [/etc/oratab] [SUCCESS] [None]

 Network Files [DBADB122]
        [/opt/oracle/product/19c/dbhome_1/network/admin/tnsnames.ora] [SUCCESS] [None]
        [/opt/oracle/product/19c/dbhome_1/network/admin/listener.ora] [SUCCESS] [None]
        [/opt/oracle/product/19c/dbhome_1/network/admin/sqlnet.ora] [SUCCESS] [None]

 Copy of password file [DBADB122]
        [/opt/oracle/product/19c/dbhome_1/dbs/orapwDBADB122] [SUCCESS] [None]

 Database State
        Resetting the database's state: [SUCCESS] [None]

 Drop GRP [DBADB122]
        [SUCCESS] [None]

 Restart of database [DBADB122]
        [PENDING] [None]

 Error Details:
 None
 
 upg> Job 101 completed
 ------------------- Final Summary --------------------
 Number of databases            [ 1 ]

 Jobs finished successfully     [1]
 Jobs failed                    [0]
 Jobs pending                   [0]
 ------------- JOBS FINISHED SUCCESSFULLY -------------
 Job 101 FOR DBADB122

 ---- Drop GRP at your convenience once you consider it is no longer needed ----

 Drop GRP from DBADB122: drop restore point AUTOUPGRADE_221145114461854_DBADB122

Oracle Network Log File Segmentation

The maximum size and number of text log files can be configured for Oracle Network components such as Oracle Net Listener, Oracle Connection Manager, and Global Service Manager. This feature allows better management of log files, particularly in Cloud environments.

Use below parameters to define number of files with defined size

LOG_FILE_NUM_listener_name
To specify the number of log file segments. At any point of time there can be only “n” log file segments where “n” is LOG_FILE_NUM_listener_name.
If the log grows beyond this number, then the older segments are deleted.

LOG_FILE_SIZE_listener_name
To specify the size of each log file segment. The size is in MB.

Sample listener.ora

 
 DBADB_LISTENER =
   (DESCRIPTION_LIST =
     (DESCRIPTION =
       (ADDRESS = (PROTOCOL = TCP)(HOST = dinesh19c)(PORT = 1521)) 
       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
     )
   )
 
 SID_LIST_DBADB_LISTENER =
 (SID_LIST =
   (SID_DESC =
    (GLOBAL_DBNAME = DBADB)
    (ORACLE_HOME = /opt/oracle/product/19c/dbhome_1)
    (SID_NAME = DBADB)
   )
 )
 
 LOG_FILE_NUM_DBADB_LISTENER=5
 LOG_FILE_SIZE_DBADB_LISTENER=1

Flashback Standby when Primary is flashed back

Flashback Database moves the entire database to an older point in time and opens the database with RESETLOGS. In a Data Guard setup, if the primary database is flashed back, the standby site is no longer in sync with the primary.

In previous releases, getting the secondary to the same point in time as the primary requires a manual procedure to flash back standby databases. A new parameter(_standby_auto_flashback) is introduced which enables the standby database to be flashed back automatically when Flashback Database is performed on the primary database.

Demo

Note :
(On PRIMARY database, if you create restore point without explicitly enabling FLASHBACK ON, standby database failed to automatically flashback.
In my case I need to recreate standby database from scratch
You must enable “FLASHBACK ON” explicitly for standby database as well)

Lets start with demo.

On PRIMARY database, guaranteed restore point created with “FLASHBACK ON” for entire database.
Standby must configured with “FLASHBACK ON” setting.

 
 create restore point DINESH_REST guarantee flashback database;
 Restore point created.
 
 select name, open_mode, controlfile_type ,database_role,flashback_on
 from v$database;
 
 NAME       OPEN_MODE            CONTROL DATABASE_ROLE    FLASHBACK_ON
 ---------- -------------------- ------- ---------------- -------------
 DBADB      READ WRITE           CURRENT PRIMARY          YES

 col NAME for a25
 col TIME for a40
 col REPLICATED for a15
 col GUARANTEE_FLASHBACK_DATABASE for a30
 select SCN, GUARANTEE_FLASHBACK_DATABASE, TIME, NAME, REPLICATED
 from v$restore_point;
 
        SCN GUARANTEE_FLASHBACK_DATABASE   TIME                                     NAME                      REPLICATED
 ---------- ------------------------------ ---------------------------------------- ------------------------- -----------  
    3189676 YES                            29-AUG-19 05.40.18.000000000 PM          DINESH_REST               NO

PRIMARY database restarted and flashback to restore point

 
 shutdown immediate
 Database closed.
 Database dismounted.
 ORACLE instance shut down.
 
 startup mount
 ORACLE instance started.
 Total System Global Area 1795159792 bytes
 Fixed Size                  9135856 bytes
 Variable Size             452984832 bytes
 Database Buffers         1191182336 bytes
 Redo Buffers                7639040 bytes
 In-Memory Area            134217728 bytes
 Database mounted.
 
 SQL> flashback database to restore point DINESH_REST; 
 Flashback complete.

 SQL> alter database open resetlogs;
 Database altered.

During this process, standby database was running in read only mode and failed with highlighted error. Media recovery interrupted.

 
 2019-08-29T18:01:43.445468+05:30
  rfs (PID:24762): New archival redo branch: 1017597674 current: 1017589052
 2019-08-29T18:01:43.508354+05:30
  rfs (PID:24762): Selected LNO:6 for T-1.S-1 dbid 3467819188 branch 1017597674
  rfs (PID:24762): Standby in the future of new recovery destination branch(resetlogs_id) 1017597674
  rfs (PID:24762): Incomplete Recovery SCN:0x000000000030ba2f
  rfs (PID:24762): Resetlogs SCN:0x000000000030abae
  rfs (PID:24762): Flashback database to SCN:0x000000000030abad (3189677) to follow new branch
  rfs (PID:24762): New Archival REDO Branch(resetlogs_id): 1017597674  Prior: 1017589052
  rfs (PID:24762): Archival Activation ID: 0xced3adfb Current: 0xced35e65
  rfs (PID:24762): Effect of primary database OPEN RESETLOGS
  rfs (PID:24762): Managed Standby Recovery process is active
 2019-08-29T18:01:43.628450+05:30
 Incarnation entry added for Branch(resetlogs_id): 1017597674 (DBADBFALL)
 2019-08-29T18:01:43.665243+05:30
 Setting recovery target incarnation to 4
 2019-08-29T18:01:43.667334+05:30
 PR00 (PID:22886): MRP0: Incarnation has changed! Retry recovery...
 2019-08-29T18:01:43.667635+05:30
 Errors in file /opt/oracle/diag/rdbms/dbadbfall/DBADBFALL/trace/DBADBFALL_pr00_22886.trc:
 ORA-19906: recovery target incarnation changed during recovery
 PR00 (PID:22886): Managed Standby Recovery not using Real Time Apply
 2019-08-29T18:01:43.715752+05:30
 ARC3 (PID:21354): Archived Log entry 10 added for T-1.S-1 ID 0xced3adfb LAD:1
 2019-08-29T18:01:43.793205+05:30
  rfs (PID:24825): Opened log for T-1.S-11 dbid 3467819188 branch 1017589052
 2019-08-29T18:01:43.881460+05:30
 Recovery interrupted!
 2019-08-29T18:01:44.126230+05:30
  rfs (PID:24825): Archived Log entry 11 added for B-1017589052.T-1.S-11 ID 0xced35e65 LAD:2
 Recovered data files to a consistent state at change 3193867
 2019-08-29T18:01:44.129553+05:30
 stopping change tracking
 2019-08-29T18:01:44.131161+05:30
 Errors in file /opt/oracle/diag/rdbms/dbadbfall/DBADBFALL/trace/DBADBFALL_pr00_22886.trc:
 ORA-19906: recovery target incarnation changed during recovery
 2019-08-29T18:01:44.307981+05:30
  Started logmerger process
 2019-08-29T18:01:44.324883+05:30
 PR00 (PID:24828): Managed Standby Recovery starting Real Time Apply
 max_pdb is 3
 Warning: Recovery target destination is in a sibling branch
 of the controlfile checkpoint. Recovery will only recover
 changes to datafiles.
 Datafile 1 (ckpscn 3193867) is orphaned on incarnation#=3
 PR00 (PID:24828): MRP0: Detected orphaned datafiles!
 2019-08-29T18:01:44.381034+05:30
 Errors in file /opt/oracle/diag/rdbms/dbadbfall/DBADBFALL/trace/DBADBFALL_pr00_24828.trc:
 ORA-19909: datafile 1 belongs to an orphan incarnation
 ORA-01110: data file 1: '/u01/oradata/data/DBADBFALL/system01.dbf'
 PR00 (PID:24828): Managed Standby Recovery not using Real Time Apply
 stopping change tracking
 2019-08-29T18:01:44.545290+05:30
 Recovery Slave PR00 previously exited with exception 19909
 2019-08-29T18:01:44.545932+05:30
 Errors in file /opt/oracle/diag/rdbms/dbadbfall/DBADBFALL/trace/DBADBFALL_mrp0_22880.trc:
 ORA-19909: datafile 1 belongs to an orphan incarnation
 ORA-01110: data file 1: '/u01/oradata/data/DBADBFALL/system01.dbf'
 2019-08-29T18:01:53.132342+05:30
  rfs (PID:24856): Primary database is in MAXIMUM PERFORMANCE mode
 2019-08-29T18:01:53.243039+05:30
  rfs (PID:24856): Selected LNO:6 for T-1.S-2 dbid 3467819188 branch 1017597674
 2019-08-29T18:02:04.551559+05:30
 MRP0 (PID:22880): Recovery coordinator encountered one or more errors during automatic flashback on standby 
 2019-08-29T18:02:04.552127+05:30
 Background Media Recovery process shutdown (DBADBFALL)

To proceed further, standby database restarted in mounted mode and media recovery started.
Now standby database able to identify flashback performed on primary and automatically flashback to restore point.

 
 Background Managed Standby Recovery process started (DBADBFALL)
 2019-08-29T18:05:40.530166+05:30
  Started logmerger process
 2019-08-29T18:05:40.559253+05:30
 PR00 (PID:25235): Managed Standby Recovery starting Real Time Apply
 max_pdb is 3
 Warning: Recovery target destination is in a sibling branch
 of the controlfile checkpoint. Recovery will only recover
 changes to datafiles.
 Datafile 1 (ckpscn 3193867) is orphaned on incarnation#=3
 PR00 (PID:25235): MRP0: Detected orphaned datafiles!
 2019-08-29T18:05:40.587358+05:30
 Errors in file /opt/oracle/diag/rdbms/dbadbfall/DBADBFALL/trace/DBADBFALL_pr00_25235.trc:
 ORA-19909: datafile 1 belongs to an orphan incarnation
 ORA-01110: data file 1: '/u01/oradata/data/DBADBFALL/system01.dbf'
 PR00 (PID:25235): Managed Standby Recovery not using Real Time Apply
 stopping change tracking
 2019-08-29T18:05:40.753195+05:30
 Recovery Slave PR00 previously exited with exception 19909
 2019-08-29T18:05:40.875141+05:30
 Errors in file /opt/oracle/diag/rdbms/dbadbfall/DBADBFALL/trace/DBADBFALL_mrp0_25221.trc:
 ORA-19909: datafile 1 belongs to an orphan incarnation
 ORA-01110: data file 1: '/u01/oradata/data/DBADBFALL/system01.dbf'
 2019-08-29T18:06:00.882678+05:30
 MRP0 (PID:25221): Recovery coordinator performing automatic flashback of database to SCN:0x000000000030abac (3189676) 
 Flashback Restore Start
 Flashback Restore Complete
 Flashback Media Recovery Start
 2019-08-29T18:06:01.438806+05:30
 Setting recovery target incarnation to 3
 2019-08-29T18:06:01.451938+05:30
  Started logmerger process
 2019-08-29T18:06:01.486379+05:30
 max_pdb is 3
 2019-08-29T18:06:01.561969+05:30
 Parallel Media Recovery started with 3 slaves
 2019-08-29T18:06:01.683807+05:30
 stopping change tracking
 2019-08-29T18:06:01.859008+05:30
 Media Recovery Log /u01/oradata/data/DBADBFALL/arch/1_9_1017589052.dbf
 2019-08-29T18:06:01.961192+05:30
 Media Recovery Log /u01/oradata/data/DBADBFALL/arch/1_10_1017589052.dbf
 2019-08-29T18:06:02.090738+05:30
 Media Recovery Log /u01/oradata/data/DBADBFALL/arch/1_11_1017589052.dbf
 2019-08-29T18:06:02.356221+05:30
 Incomplete Recovery applied until change 3189676 time 08/29/2019 17:40:18
 2019-08-29T18:06:02.373747+05:30
 Flashback Media Recovery Complete
 2019-08-29T18:06:02.473453+05:30
 stopping change tracking
 2019-08-29T18:06:02.506925+05:30
 Setting recovery target incarnation to 4
 2019-08-29T18:06:02.542883+05:30
  Started logmerger process
 2019-08-29T18:06:02.568830+05:30
 PR00 (PID:25267): Managed Standby Recovery starting Real Time Apply
 max_pdb is 3
 2019-08-29T18:06:02.682426+05:30
 Parallel Media Recovery started with 3 slaves
 2019-08-29T18:06:02.686792+05:30
 Media Recovery start incarnation depth : 1, target inc# : 4, irscn : 3189677
 stopping change tracking
 2019-08-29T18:06:02.880310+05:30
 PR00 (PID:25267): Media Recovery Log /u01/oradata/data/DBADBFALL/arch/1_11_1017589052.dbf
 2019-08-29T18:06:03.101114+05:30
 PR00 (PID:25267): Media Recovery Log /u01/oradata/data/DBADBFALL/arch/1_1_1017597674.dbf
 PR00 (PID:25267): Media Recovery Waiting for T-1.S-2 (in transit)
 2019-08-29T18:06:03.213438+05:30
 Recovery of Online Redo Log: Thread 1 Group 6 Seq 2 Reading mem 0
   Mem# 0: /u01/oradata/data/DBADBFALL/redostby03.log
 2019-08-29T18:06:03.545178+05:30
 Completed: ALTER DATABASE RECOVER  managed standby database disconnect

Clear Flash logs periodically for FRA size certainty

Customers have many databases that all use the Fast Recovery Area (FRA). They usually subscribe to FRA by using the db_recovery_file_dest_size initialization parameter. Difficulties arise when flashback logs are not cleared until space pressure requires it. In many cases, the only remedy is to turn off flashback logging and turn it back on.

This feature makes flashback space usage become predictable from a storage management perspective, since flashback uses no more space than is required by retention. This feature also allows users to control cumulative space pressure by adjusting the flashback retention.

Propagate Restore Points from Primary to Standby

Normal restore points or guaranteed restore points can be defined at the primary site to enable fast point-in-time recovery in the event of any logical corruption issues. However, this restore point is stored in the control file and is not propagated to the standby database. In the event of a failover, the standby becomes the primary and the restore point information is lost.

This feature ensures that the restore point is propagated from the primary to standby sites, so that the restore point is available even after a failover event.

Demo

 
 Production
 
 select name, open_mode, controlfile_type ,database_role,flashback_on 
 from v$database;
 
 NAME      OPEN_MODE            CONTROL DATABASE_ROLE    FLASHBACK_ON
 --------- -------------------- ------- ---------------- ------------------
 DBADB     READ WRITE           CURRENT PRIMARY          YES
 
 create restore point DINESH_REST guarantee flashback database;
 Restore point created.
 
 col NAME for a25
 col TIME for a40
 col REPLICATED for a15
 col GUARANTEE_FLASHBACK_DATABASE for a30
 select SCN, GUARANTEE_FLASHBACK_DATABASE, TIME, NAME, REPLICATED
 from v$restore_point;
 
        SCN GUARANTEE_FLASHBACK_DATABASE   TIME                                     NAME          REPLICATED
 ---------- ------------------------------ ---------------------------------------- ------------- ---------------
    3189676 YES                            29-AUG-19 05.40.18.000000000 PM          DINESH_REST   NO
 
 
 Standby
 
 select name, open_mode, controlfile_type ,database_role,flashback_on
 from v$database;
 
 NAME      OPEN_MODE            CONTROL DATABASE_ROLE    FLASHBACK_ON
 --------- -------------------- ------- ---------------- ------------------
 DBADB     READ ONLY WITH APPLY STANDBY PHYSICAL STANDBY YES
 
 col NAME for a25
 col TIME for a40
 col REPLICATED for a15
 col GUARANTEE_FLASHBACK_DATABASE for a30
 select SCN, GUARANTEE_FLASHBACK_DATABASE, TIME, NAME, REPLICATED
 from v$restore_point;
 
        SCN GUARANTEE_FLASHBACK_DATABASE   TIME                                     NAME                      REPLICATED
 ---------- ------------------------------ ---------------------------------------- ------------------------- -----------  
    3189676 NO                             29-AUG-19 05.40.18.000000000 PM          DINESH_REST_PRIMARY       YES

As highlighted in “Standby” section, once you create restore point on primary database, it is created with “_PRIMARY” suffix in standby database. Important column to note here is “REPLICATED” which shows “YES” on standby.

Easy Parameter Management in a Broker Config

Users can now manage all Data Guard related parameter settings using the SQL*Plus ALTER
SYSTEM commands or in DGMGRL with the new EDIT DATABASE … SET PARAMETER command.
Parameter changes made in the DGMGRL interface are immediately executed on the target database.

In addition, this new capability allows the user to modify a parameter on all databases in a Data Guard
configuration using the ALL qualifier, eliminating the requirement to attach to each database and
execute an ALTER SYSTEM command.

Parameters to tune auto outage resolution with ADG

In Oracle Database 19c, the DBA can tune the amount of wait time for this detection period by using two new parameters, DATA_GUARD_MAX_IO_TIME and DATA_GUARD_MAX_LONGIO_TIME.

These parameters allow the waits times to be tuned for a specific Data Guard configuration based on the user network and Disk I/O behavior. Users can now tune Oracle Data Guard automatic outage resolution to fit their specific needs.

max_datapump_parallel_per_job

MAX_DATAPUMP_PARALLEL_PER_JOB is introduced to control the number of parallel workers that can be used for an individual Data Pump job. This is an improvement to implement resource usage policies in DataPump.

In a database with MAX_DATAPUMP_PARALLEL_PER_JOB set to N, even if parallel>N is specified for the DataPump job, only N number of Worker processes will be started.

Test Mode for Transportable Tablespaces

TTS_CLOSURE_CHECK=TEST_MODE indicates that the tablespaces are not required to be in Read-Only mode. This option is for testing purposes only to provide timing requirements of a transportable tablespace export operation. The resulting export dump file is not available for use by Data Pump Import.

Now you can more easily determine how long an export takes, and discover unforeseen issues not reported by the closure check.

Demo

 
 select status,tablespace_name 
 from dba_tablespaces 
 where tablespace_name='USERS';

 STATUS    TABLESPACE_NAME
 --------- ----------------
 ONLINE    USERS

 expdp 
 directory=DATA_PUMP_DIR 
 dumpfile=users_tts_test.dmp 
 logfile=users_tts_test.log 
 transport_tablespaces=users 
 TTS_CLOSURE_CHECK=test_mode

 Export: Release 19.0.0.0.0 - Production on Fri Sep 13 17:13:38 2019
 Version 19.3.0.0.0
 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
 Username: / as sysdba
 Password:

 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

 Warning: Oracle Data Pump operations are not typically needed when connected to the root or seed of a container database. 

 Starting "SYS"."SYS_EXPORT_TRANSPORTABLE_01": /******** AS SYSDBA directory=DATA_PUMP_DIR
 dumpfile=users_tts_test.dmp logfile=users_tts_test.log transport_tablespaces=users TTS_CLOSURE_CHECK=test_mode 
 Processing object type TRANSPORTABLE_EXPORT/STATISTICS/TABLE_STATISTICS
 Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
 Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
 Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
 Processing object type TRANSPORTABLE_EXPORT/TABLE
 Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
 Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
 Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
 Master table "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
 ******************************************************************************
 Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:
 /opt/oracle/admin/DBADB/dpdump/users_tts_test.dmp
 Dump file set is unusable. TEST_MODE requested.
 ******************************************************************************
 Datafiles required for transportable tablespace USERS:
 /u01/oradata/data/DBADB/users01.dbf
 Job "SYS"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Fri Sep 13 17:15:13 2019 elapsed 0 00:01:15

 ls -rlt /opt/oracle/admin/DBADB/dpdump/users_tts_test.dmp
 -rw-r-----. 1 oracle oinstall 2367488 Sep 13 17:15 /opt/oracle/admin/DBADB/dpdump/users_tts_test.dmp

Orachk & Exachk Support for Encrypting Collection Files

Oracle ORAchk and Oracle EXAchk diagnostic collection files may contain sensitive data. Starting in this release, you can encrypt and decrypt diagnostic collection ZIP files and protect them with a password.

Oracle ORAchk and Oracle EXAchk collections and their reports can include sensitive data. When you email or transfer these reports to repositories, it is critical that only the intended recipients can view the sensitive data. To prevent leaks, you can restrict access to sensitive data by encrypting the diagnostic collections and protecting them with a password.

This feature is available only on Linux and Solaris platforms.

crsctl status query - RAC

crsctl status query - RAC

[grid@rac3 ~]$ crsctl status res |grep -v "^$"|awk -F "=" 'BEGIN {print " "} {printf("%s",NR%4 ? $2"|" : $2"\n")}'|sed -e 's/ *, /,/g' -e 's/, /,/g'|\
> awk -F "|" 'BEGIN { printf "%-40s%-35s%-20s%-50s\n","Resource Name","Resource Type","Target ","State" }{ split ($3,trg,",") split ($4,st,",")}{for (i in trg) {printf "%-40s%-35s%-20s%-50s\n",$1,$2,trg[i],st[i]}}'

Resource Name Resource Type Target State
ora.ARCH.dg ora.diskgroup.type ONLINE ONLINE on rac3
ora.ARCH.dg ora.diskgroup.type ONLINE ONLINE on rac4
ora.ASMNET1LSNR_ASM.lsnr ora.asm_listener.type ONLINE ONLINE on rac3
ora.ASMNET1LSNR_ASM.lsnr ora.asm_listener.type ONLINE ONLINE on rac4
ora.CRS.dg ora.diskgroup.type ONLINE ONLINE on rac3
ora.CRS.dg ora.diskgroup.type ONLINE ONLINE on rac4
ora.DATA.dg ora.diskgroup.type ONLINE ONLINE on rac3
ora.DATA.dg ora.diskgroup.type ONLINE ONLINE on rac4
ora.LISTENER.lsnr ora.listener.type ONLINE ONLINE on rac3
ora.LISTENER.lsnr ora.listener.type ONLINE ONLINE on rac4
ora.LISTENER_SCAN1.lsnr ora.scan_listener.type ONLINE ONLINE on rac4
ora.LISTENER_SCAN2.lsnr ora.scan_listener.type ONLINE ONLINE on rac3
ora.LISTENER_SCAN3.lsnr ora.scan_listener.type ONLINE ONLINE on rac3
ora.MGMTLSNR ora.mgmtlsnr.type OFFLINE OFFLINE
ora.asm ora.asm.type ONLINE ONLINE on rac3
ora.asm ora.asm.type ONLINE ONLINE on rac4
ora.asm ora.asm.type OFFLINE OFFLINE
ora.cvu ora.cvu.type ONLINE ONLINE on rac3
ora.net1.network ora.network.type ONLINE ONLINE on rac3
ora.net1.network ora.network.type ONLINE ONLINE on rac4
ora.ons ora.ons.type ONLINE ONLINE on rac3
ora.ons ora.ons.type ONLINE ONLINE on rac4
ora.orcl.db ora.database.type ONLINE ONLINE on rac3
ora.orcl.db ora.database.type ONLINE ONLINE on rac4
ora.proxy_advm ora.proxy_advm.type OFFLINE OFFLINE
ora.proxy_advm ora.proxy_advm.type OFFLINE OFFLINE
ora.qosmserver ora.qosmserver.type ONLINE ONLINE on rac3
ora.rac3.vip ora.cluster_vip_net1.type ONLINE ONLINE on rac3
ora.rac4.vip ora.cluster_vip_net1.type ONLINE ONLINE on rac4
ora.scan1.vip ora.scan_vip.type ONLINE ONLINE on rac4
ora.scan2.vip ora.scan_vip.type ONLINE ONLINE on rac3
ora.scan3.vip ora.scan_vip.type ONLINE ONLINE on rac3

Voting Disk Internals - RAC

CSSD processes (Cluster Services Synchronization Daemon) monitors the health of RAC nodes by two distinct heart beats, Network heart beat and Disk heart beat.

Network heartbeat is across the interconnect, every one second, a thread (sending) of CSSD sends a network tcp heartbeat to itself and all other nodes, another thread (receiving) of CSSD receives the heartbeat. If the network packets are dropped or has error, the error correction mechanism on tcp would re-transmit the package, Oracle does not re-transmit in this case. In the CSSD log, you will see a WARNING message about missing of heartbeat if a node does not receive a heartbeat from another node for 15 seconds (50% of misscount). Another warning is reported in CSSD log if the same node is missing for 22 seconds (75% of misscount) and similarly at 90% of misscount and when the heartbeat is missing for a period of 100% of the misscount (i.e. 30 seconds by default), the node is evicted.

Disk heartbeat is between the cluster nodes and the voting disk. CSSD process in each RAC node maintains a heart beat in a block of size 1 OS block in a specific offset by read/write system calls (pread/pwrite), in the voting disk. In addition to maintaining its own disk block, CSSD processes also monitors the disk blocks maintained by the CSSD processes running in other cluster nodes. The written block has a header area with the node name and a counter which is incremented with every next beat (pwrite) from the other nodes. Disk heart beat is maintained in the voting disk by the CSSD processes and If a node has not written a disk heartbeat within the I/O timeout, the node is declared dead. Nodes that are of an unknown state, i.e. cannot be definitively said to be dead, and are not in the group of nodes designated to survive, are evicted, i.e. the node’s kill block is updated to indicate that it has been evicted.

Thus summarizing the heartbeats, N/W Heartbeat is pinged every second, nodes must respond in css_miscount time, failure would lead to node eviction. Similarly Disk Heartbeat, node pings (r/w) voting disk every second, nodes must recieve a response in (long/short) disk timeout time.

Below are the different possibilities of individual heartbeat failures.

Network Ping	Disk Ping	Reboot
Completes within misscount seconds	Completes within Misscount seconds	N
Completes within Misscount seconds	Takes more than misscount seconds but less than Disktimeout seconds	N
Completes within Misscount seconds	Takes more than Disktimeout seconds	Y
Takes more than Misscount Seconds	Completes within Misscount seconds	Y

Voting Disk contains information regarding nodes in the cluster and the disk heartbeat information, CSSD of the individual nodes registers the information regarding their nodes in the voting disk and with that pwrite() system call at a specific offset and then a pread() system call to read the status of other CSSD processes. But as information regarding the nodes is in OCR/OLR too and system calls have nothing to do with previous calls, there isn’t any useful data kept in the voting disk. So, if you lose voting disks, you can simply add them back without losing any data. But, of course, losing voting disks can lead to node reboots. If you lose all voting disks, then you will have to keep the CRS daemons down, then only you can add the voting disks. Now that we have understood both the heartbeats which was the most important part, we will dig deeper into Voting Disk, as in what is stored inside voting disk, why Clusterware needs of Voting Disk, how many voting disks are required etc.

Now finally to understand the whole concept of voting disk we need to know what is split brain syndrome, I/O Fencing and simple majority rule.

Split Brain Syndrome, In a Oracle RAC environment all the instances/servers communicate with each other using high-speed interconnects on the private network. This private network interface or interconnect are redundant and are only used for inter-instance oracle data block transfers. Now talking about split-brain concept with respect to oracle RAC systems, it occurs when the instance members in a RAC fail to ping/connect to each other via this private interconnect, but the servers are all physically up and running and the database instance on each of these servers is also running. These individual nodes are running fine and can conceptually accept user connections and work independently. So basically due to lack of communication the instance thinks that the other instance that it is not able to connect is down and it needs to do something about the situation. The problem is if we leave these instances running, the same block might get read, updated in these individual instances and there would be data integrity issue, as the blocks changed in one instance, will not be locked and could be over-written by another instance. This situation is termed as Split Brain Syndrome.

I/O Fencing, there will be some situation where the leftover write operations from failed database instances (The cluster function failed on the nodes, but the nodes are still running at OS level) reach the storage system after the recovery process starts. Since these write operations are no longer in the proper serial order, they can damage the consistency of the data stored data. Therefore when a cluster node fails, the failed node needs to be fenced off from all the shared disk devices or disk groups. This methodology is called I/O fencing or failure fencing

Simple Majority Rule, According to Oracle – “An absolute majority of voting disks configured (more than half) must be available and responsive at all times for Oracle Clusterware to operate.” Which means to survive from loss of ‘N’ voting disks, you must configure atleast ‘2N+1′ voting disks.

Now we are in a state to understand the use of voting disk in case of heartbeat failure.

Example 1:- Suppose in a 3 node cluster with 3 voting disks, a network heartbeat fails between Node 1 and Node 3 & Node 2 and Node 3 whereas Node 1 and Node 2 are able to communicate via interconnect, and from the Voting Disk CSSD notices that all the nodes are able to write to Voting Disks thus spli-brain, so the healthy nodes Node 1 & Node 2 would would update the kill block in the voting disk for Node 3. Then when during pread() system call of CSSD of Node 3, it sees a self kill flag set and thus the CSSD of Node 3 evicts itself. And then the I/O fencing and finally the OHASD will finally attempt to restart the stack after graceful shutdown.

Example 2:- Suppose in a 2 node cluster with 3 voting disk, a disk heartbeat fails such that Node 1 can see 2 Voting Disks and Node 2 can see 1 Voting Disk, ( If here the Voting Disk wouldn’t have been odd then both the Nodes would have thought the other node should be killed hence would have been difficult to avoid split-brain), thus based on Simple Majority Rule, CSSD process of Node 1 (2 Voting Disks) sends a kill request to the CSSD process of Node 2 (1 Voting Disk) and thus the Node 2 evicts itself and then the I/O fencing and finally the OHASD will finally attempt to restart the stack after graceful shutdown.

Thus Voting disk is plays a role in both the heartbeat failures, and hence a very important file for node eviction & I/O fencing in case of a split brain situation.

Monday, 5 July 2021

Oracle RAC Documents

In this Document

Click on below any points, you will be redirected to Oracle Support.

Goal

Fix

Prepare the disks

1. Size

2. For raw or block device (pre 11.2)

3. For ASM disks (11.2+)

4. For cluster file system

5. Permissions

ADD/REMOVE/REPLACE/MOVE OCR Device

1. To add an OCRMIRROR device when only OCR device is defined:

2. To remove an OCR device

3. To replace or move the location of an OCR device

ADD/DELETE/MOVE Voting Disk

Voting Disks in Oracle 11gR2 RAC

Voting disks are important component of Oracle Clusterware. Clusterware uses voting disk to determine which nodes are members of the cluster. After ASM is introduced to store these files, these are called as VOTING FILES.

Primary function of voting disks is to manage node membership and prevent SPLITBRAIN Syndrome in which 2 or more instances attempt to control the RAC database.

Ø These files can be stored either in ASM or on shared storage.

Ø If it is stored in ASM, no need to configure manually as the files will be created depending on the redundancy in ASM.

Ø In shared storage system, we need to manually configure these files with redundancy setup for high availability.

Ø We must have odd number of disks.

Ø Oracle recommends minimum of 3 and maximum of 5. In 10g, Clusterware can supports 32 voting disks but in 11gR2 supports 15 voting disks.

Ø A node must be able to access more than half of the voting disks at any time. For eg, if you have 5 voting disks, a node must be able access atleast 3 of the voting disks. If it cannot access the minimum of voting disks, then it is evicted/removed from the cluster.

Ø All nodes in the RAC cluster register their heartbeat information in the voting disks/files. RAC heartbeat is the polling mechanism that is sent over the cluster interconnect to ensure all RAC

nodes are available.

How Voting Happens

The CKPT process updates the control file every 3 seconds in an operation known as heartbeat.

CKPT writes to a single block that is local to the node/each instance and intra instance coordination is not required. This block is called checkpoint progress record.

All members of the cluster attempt to lock on the controlfile record for updating.

The instance which obtains the locks tallies the votes from all members. Then, the group membership must conform to the decided(voted) membership before allowing GCS/GES to proceed for reconfiguration. The control file record is then stored in the same block as the heartbeat in the controlfile checkpoint progress record.

What is NETWORK and DISK HEARTBEAT and how it registers in VOTING DISKS/FILES

1. All nodes in the RAC cluster register their heartbeat information in the voting disks/files. RAC heartbeat is the polling mechanism that is sent over the cluster interconnect to ensure all RAC

a. nodes are available.

b. Voting disks/files are just like attendance register where you have nodes mark their attendance (heartbeats).

2. CSSD process on every node makes entries in the voting disk to ascertain the membership of the node. While marking their own presence, all the nodes also register the information about their communicability with other nodes in the voting disk. This is called NETWORK HEARTBEAT.

3. CSSD process in each RAC maintains the heart beat in a block of size 1 OS block in the hot block of voting disk at a specific offset. The written block has a header area with the node name. The heartbeat counter increments every second on every write call. Thus heartbeat of various nodes is recorded at different offsets in the voting disk. This process is called DISK HEARTBEAT.

4. In addition of maintaining its own disk block, CSSD processes also monitors the disk block maintained by the CSSD processes of other nodes in cluster. Healthy nodes will have continuous network & disk heartbeats exchanged between the nodes. Break in heartbeats indicates a possible error scenario.

5. If the disk is not updated in a short timeout period, the node is considered unhealthy and may be rebooted to protect the database. In this case, a message to this effect is written in the KILL BLOCK of node. Each nodes reads its KILL BLOCK once per second, if the kill block is not overwritten, node commits suicide.

6. During reconfig (leaving or joining), CSSD monitors all nodes heartbeat information and determines whether the nodes has a disk heartbeat including those with no network heartbeat. If no disk heartbeat is detected, then node is considered as dead.

What Information is stored in VOTING DISK/FILE

It contains 2 types of data .

Static data : Info about the nodes in cluster

Dynamic data: Disk heartbeat logging

It contains the important details of the cluster nodes membership like

a. Which node is part of the cluster.

b. Which node is leaving the cluster and

c. Which node is joining the cluster.

Purpose of Voting disk or Why is Voting disk needed

Voting disks are used by clusterware for health check.

Ø Used by CSS to determine which nodes are currently members of the cluster.

Ø In concert with other cluster components like CRS to shutdown, fence or reboot either single or multiple nodes whenever network communication is lost between any node within the cluster, to prevent to split-brain condition in which 2 or more instances attempt to control the RAC database and thus protecting the database.

Ø Will be used by CSS to arbitrate (to take an authorized decision) with peers that it is not able to see over the private interconnect in the event of an outage, allowing it to salvage (rescue from loss) the largest fully connected sub-cluster for further operation. During this operation , node membership (NM) will make an entry in the voting disk to inform its vote on availability. Other instances in the cluster too do similar actions. The 3 voting disks configured also provide a method to determine who in the cluster should survive.

Example : if eviction of one of the node is necessiated due to unresponsive action, then the node that has 2 voting disks with start evicting the other node. NM alternates it action between the heartbeat and the voting disk to determine the availability of the other nodes in cluster.

Possible scenarios in Voting disks

As we know now that voting disks is used by CSSD. It contains both network & disk heartbeat from all nodes and if any break in heartbeat will result in eviction of the node from cluster. There are possible scenarios with missing heartbeats.

1. Network heart beat is successful, but disk heart beat is missed.

2. Disk heart beat is successful, but network heart beat is missed.

3. Both heart beats failing.

When a cluster is involved with many nodes, then few more scenarios are possible.

1. Nodes have a split into N sets of nodes., communicating within the sets, but not with the members in other set.

2. Just one node going unhealthy. Nodes with quorum (minimum number of nodes to make cluster valid) will maintain active membership of the cluster and other node(s) will be fenced/rebooted.

Why should we have ODD number of voting diks ??

A node must be able to access more than half of the voting disks at any time.

Example.

a. Let us consider 2 node cluster with even number of voting disks say 2.

b. Let node 1 is able to access Voting disk 1.

c. Node 2 is able to access voting disk 2.

d. From the above steps, we see that we don’t any common file where clusterware can check the heartbeat of both the nodes.

e. If we have 3 voting disks and both the nodes are able to access more than half ie., 2 voting disks, there will be atleast one disk which will be accessed by both the nodes. The clusterware can use this disk to check the heartbeat of the nodes.

f. A node not able to do so will be evicted from the cluster by another node that has more than half the voting disks to maintain the integrity of the cluster.

Where voting disks are stored

It can be stored in

a. Raw devices

b. Cluster file system supported by Oracle RAC such as OCFS,Sun cluster or Veritas Cluster Filesystem

c. ASM disks (in 11gR2).

When voting disk is stored in ASM, a question is arised how the voting file on ASM can be accessed when we want to add a new node to a cluster.

The answer is.

Oracle ASM reserves several blocks at the fixed location for every Oracle ASM disk used for storing the voting files. As a result, Oracle clusterware can access the voting disks present in ASM even if the ASM instance is down and CSS can continue to maintain the Oracle cluster even if the ASM has failed. The physical location of the voting files in ASM disks are fixed i.e., the cluster stack does not rely on a running ASM instance to access the files.

d. If the ASM is stored in ASM, the multiplexing of voting disk is decided by the redundancy of the diskgroup.

Redundancy of the diskgroup	#of copies of voting disk	( Minimum # of disks in the diskgroup)
External	1	1
Normal	3	3
High	5	5

Commands to check the Votingdisk

Crsctl query css votedisk - for checking the file location

When to take voting disk backup

1. Fresh installation

2. Adding /deleting node

Voting disk backup (In 10g)

dd if=<voting-disk-path> of=<backup/path>

Voting disk restore (In 10g)

dd if=<backup/path> of=<voting disk path>

In 11gR2, the voting files are backed up automatically as part of OCR. Oracle recommends NOT used dd command to backup or restore as this can lead to loss of the voting disk.

Add/delete vote disk

crsctl add css votedisk <path> -adds a new voting disk

crsctl delete css votedisk <path> -- deletes the voting disk

Disclaimer

Tuesday, 6 July 2021

Automatic Indexing

Functionality

How to Configure?

Generating Reports

Behind the Scenes!

Demo

Sample Report – Auto_Index_Report

Real-Time Statistics

Parameters to control this feature

Demo

Active Data Guard DML Redirection

How to configure?

Demo

What’s happening backstairs?

Automatic Resolution of SQL Plan Regressions

Automatic SPM Evolve Advisor

Finer granularity Supplemental Logging

How to enable

Way to disable

Dry-Run Validation of Clusterware Upgrade

AutoUpgrade for Oracle Database

Workflow and Stages

Demo

Oracle Network Log File Segmentation

Sample listener.ora

Flashback Standby when Primary is flashed back

Demo

Clear Flash logs periodically for FRA size certainty

Propagate Restore Points from Primary to Standby

Demo

Easy Parameter Management in a Broker Config

Parameters to tune auto outage resolution with ADG

max_datapump_parallel_per_job

Test Mode for Transportable Tablespaces

Demo

Orachk & Exachk Support for Encrypting Collection Files

Monday, 5 July 2021

Voting Disks in Oracle 11gR2 RAC