Automatic Indexing in Oracle 19c using DBMS_AUTO_INDEX

Oracle 19c introduced new features automatic indexing, this feature creates and drops without dba intervention and takes care of index management

The DBMS_AUTO_INDEX package used to manage the automatic indexing feature. This feature evaluates the need for new indexes, creates when needed and drops then when they are no longer needed. It supports both single and multi-column index

Enable/Disable AUTO Index

To enable automatic indexing run below command
SQL> dbms_auto_index.configure ('AUTO_INDEX_MODE', 'IMPLEMENT');

To turn off or disable automatic indexing run below command
SQL> exec dbms_auto_index.configure('AUTO_INDEX_MODE','OFF');

To turn on automatic indexing, but new indexes remain invisible run below command
SQL> exec dbms_auto_index.configure('AUTO_INDEX_MODE','REPORT ONLY');

Note: The invisible Index feature introduced in Oracle 11g, these indexes are maintained like other indexes but ignored by the optimizer unless the he OPTIMIZER_USE_INVISIBLE_INDEXES parameter is set to TRUE

For ex:- SQL> create index INDX_01 on EMP (emp_name) invisible;

Auto Index Tablespace Management

The automatic indexes by default created in the default permanent tablespace, but you can you can specify a tablespace using the AUTO_INDEX_DEFAULT_TABLESPACE property

SQL> exec dbms_auto_index.configure('AUTO_INDEX_DEFAULT_TABLESPACE','TBS_AUTO_INDX');

Run below command with NULL to return using the default permanent tablespace.
SQL> exec dbms_auto_index.configure('AUTO_INDEX_DEFAULT_TABLESPACE',NULL);

User can specify threshold in percentage using below command. In this case only 60% of the tablespace is used for Auto Index
SQL> exec dbms_auto_index.configure ('AUTO_INDEX_SPACE_BUDGET', '60');

The autocreated indexes will be named with prefix SYS_AI. To find out these indexes there is a called “AUTO” added in DBA_INDEXES where you will see the value as “YES”

Automatic Index Management at Schema Level

You can control the SCHEMAS that’s should participate in Automatic Indexing feature by using the AUTO_INDEX_SCHEMA property.

SQL> exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA', ‘EMP’, allow => TRUE);

If ALLOW parameter is set to TRUE, then specified schema will be added to inclusion list and you can clear the inclusion list using below command. 

SQL>  exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA', NULL, allow => TRUE);

User can manually add the SCHEMAS to the exclusion list by setting ALLOW parameter to FALSE. SQL> exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA', 'TEST', allow => FALSE);

User can clear the exclusion list by running below command 

SQL> exec dbms_auto_index.configure('AUTO_INDEX_SCHEMA', NULL, allow => FALSE);

Users can also use AUTO_INDEX_EXCLUDE_SCHEMA property to exclude specific schema.
SQL> exec dbms_auto_index.configure ('AUTO_INDEX_EXCLUDE_SCHEMA', ‘EMP’);

The manually created unused indexes never deleted by the automatic indexing process and they can be deleted using AUTO_INDEX_RETENTION_FOR_MANUAL. But user needs to mention after how many days the unused manual indexes can be dropped.

SQL> exec dbms_auto_index.configure ('AUTO_INDEX_RETENTION_FOR_AUTO', '50')

Views: Oracle offers several Views associated with automatic Indexing features

 
DBA_AUTO_INDEX_CONFIG - configuration settings related to automatic indexes
DBA_AUTO_INDEX_EXECUTIONS - The history of Automatic Indexing task executions
DBA_AUTO_INDEX_STATISTICS - Statistics related to automatic indexes
DBA_AUTO_INDEX_VERIFICATIONS- stats about PLAN_HASH_VALUE, AUTO_INDEX_BUFFER_GETS
DBA_AUTO_INDEX_IND_ACTIONS - Actions performed on automatic indexes
DBA_AUTO_INDEX_SQL_ACTIONS - Actions performed on SQL statements to verify automatic indexes

Thanks & Regards

http://oracleracexpert.com, Oracle ACE

Create Recovery Catalog for RMAN

Recovery catalog schema used by RMAN to store one or more Oracle databases metadata. This info is already stored in control file of each Target Database and using Recovery catalog provides redundancy. In case, if target control file and all backups are lost then you can still get the info from Recovery catalog.

RMAN Recovery catalog also helps as centralized metadata location for all your databases and it helps easily report from once location. In addition, you can store metadata history longer than Control file.

You must register database in order to store metadata in Recovery catalog. it is highly recommended to use enable ARCHIVELOG mode for Recovery Catalog database

· Recovery Catalog metadata has following database file info
· Data file and archived redo log backup sets and pieces
· Data file and Archive log copies
· Database structure such as tablespaces and data files
· Stored scripts
· RMAN configuration settings

Creating Recovery Catalog

1. Connect to Recovery catalog DB as admin and create the Schema.

SQL> CREATE USER RCAT IDENTIFIED BY password
DEFAULT TABLESPACE RCATTBS
TEMPORARY TABLESPACE temp;

2. The RECVEROY_CATALOG_OWNER role is required to maintain and query, so grant the role to the Schema

SQL> GRANT RECOVERY_CATALOG_OWNER TO RCAT;

3. Connect to the database as the recovery catalog owner and run CREATE CATALOG command to create the catalog.

RMAN> CREATE CATALOG

You can also specify tableaspace name for the catalog.
RMAN> CREATE CATALOG TABLESPACE RCATTBS;

4. Connect to recovery Catalog and Register Target Database using below nelow command

$ rman TARGET / CATALOG RCAT@RCATDB;
RMAN> REGISTER DATABASE;

Examples for Data file copy, Archive log backup and backup pieces
RMAN>CATALOG DATAFILECOPY '/bkp/dbfile/testuser01.dbf';
RMAN>CATALOG ARCHIVELOG '/arc/arc_DB_1_559.dbf', '/arc/arc_DB_1_560.dbf',
RMAN>CATALOG BACKUPPIECE '/disk1/backups/backup_820.bkp';

Catalog multiple backup files in a directory by using one single command
RMAN> CATALOG START WITH '/bkp/dbfile/';

Refer below link for Virtual Private Catalogs
https://docs.oracle.com/en/database/oracle/oracle-database/19/bradv/managing-recovery-catalog.html#GUID-DA362C55-5B94-4AB8-A8A1-163BF08FE594

Thanks & Regards,

http://oracleracpert.com, Oracle ACE

Immutable tables in Oracle 19c or 21c

Oracle 19c (19.11) introduced Immutable tables, these tables provides protection against unauthorized data modification that means these are read-only tables. User can able add or insert the rows to the table but cannot able to modify. In order to manage the data the user must specify retention period for both immutable table and for the rows with in the table.

The immutable tables will have hidden columns and these are same as Block chain tables introduced in Oracle 21c. Based upon the retention period rows become obsolete in the tableau then only user can be able to delete the rows.

In order to use this feature user must set set the COMPATIBLE parameter set to 19.11.0
SQL> alter system set compatible='19.11.0' scope=spfile;

There are several data types are not supported with immutable tables ROWID, UROWID, LONG, object type, REF, varray, nested table, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, BFILE, and XMLType and XMLType tables are not supported.

Immutable tables contain system-generated hidden columns and they are the same as blockchain tables. When a row is inserted, a non-NULL value is set for the ORABCTAB_CREATION_TIME$ and ORABCTAB_USER_NUMBER$ columns,  value of remaining system-generated hidden columns is set to NULL.

When creating IMMUTABLE TABLE statement user should use below NO DROP, NO DELETE to specify retention period. The LOCKED keyword specifies table or rows retention setting cannot be modified.

1. NO DROP clause in CREATE IMMUTABLE TABLE statement for retention period

• NO DROP : If you use this clause without any retention then then you cannot able to drop and user needs to be careful when using this clause
• NO DROP UNTIL number DAYS IDLE : By using retention user can able to drop the but User cannot able to drop the table if newest row is less than n days old

To set the table retention period to 0 days, the initialization parameter BLOCKCHAIN_TABLE_MAX_NO_DROP must be set to 0. Pls note that the minimum value is 16

2. NO DELETE clause to specify the retention period for the rows

• NO DELETE [LOCKED] – If you use without any retention then Rows cannot be deleted from the immutable table
• 
  
• NO DELETE UNTIL n DAYS AFTER INSERT [LOCKED] – if you use this clause the row cannot be deleted until n days after it was added.

User can use ALTER TABLE statement with the NO DELETE UNTIL clause to modify this setting and increase the retention period but cannot reduce the retention period. The minimum value for n is 16 days. If you use LOCKED then you cannot subsequently modify the row retention.

SQL> CREATE IMMUTABLE TABLE IMMU_TAB1 (
COLA NUMBER,
COLB VARCHAR2(15),
COLC DATE
) NO DROP UNTIL 3 DAYS IDLE
NO DELETE UNTIL 30 DAYS AFTER INSERT;

The user can able to drop table only after 3 days of inactivity and cannot deleted rows until 30 days after it has been inserted.

Use the DBMS_IMMUTABLE_TABLE.DELETE_EXPIRED_ROWS procedure to delete rows beyond the specified retention period or obsolete . Either SYS user or the owner of the schema can delete immutable table rows.

In order to DROP immutable table, it must be the owner of the schema or must have the DROP ANY TABLE system privilege.

SELECT row_retention "Row Retention Period", row_retention_locked "Row Retention Lock", table_inactivity_retention "Table Retention Period" FROM dba_immutable_tables
WHERE table_name = 'IMMU_TAB1';

Row Retention Period Row Retention Locked Table Retention Period
-------------------- -------------------- ----------------------
30 NO 3

User can query {CDB|DBA|ALL|USER}_IMMUTABLE_TABLES views information about immutable tables.

Regards,

http://oracleracexpert.com, Oracle ACE

Renaming or moving Oracle Control files and redo log files

When renaming Oracle control files and redo log files user needs to be very careful and must take latest database backup to restore in case of any user mistakes.

Control File: The current location of the control files can be queried from the V$CONTROLFILE view

SQL> select NAME from v$controlfile;
NAME
--------------------------------------------------
/oracle/TDB/controlfile/TDB01.ctl
/oracle/TDB/controlfile/TDB02.ctl
/oracle/TDB/controlfile/TDB03.ctl

To rename the control file location we must alter control_files parameter in the spfile/pfile. Follow below steps

•  Alter the control_files parameter

ALTER SYSTEM SET control_files='/dbf1/TDB/controlfile/TDB01.ctl', '/dbf2/TDB/controlfile/TDB02.ctl','/dbf3/TDB/controlfile/TDB03.ctl' SCOPE=SPFILE;

• Shutdown the database and copy or move the files to new location

SQL> SHUTDOWN IMMEDIATE
SQL> ! mv /oracle/TDB/controlfile/TDB01.ctl /dbf1/TDB/controlfile/TDB01.ctl
SQL> ! mv /oracle/TDB/controlfile/TDB02.ctl /dbf2/TDB/controlfile/TDB02.ctl
SQL> ! mv /oracle/TDB/controlfile/TDB03.ctl /dbf3/TDB/controlfile/TDB03.ctl

• Startup the database and verify new location

SQL> Startup
SQL> select NAME from v$controlfile;
NAME
--------------------------------------------------
/dbf1/TDB/controlfile/TDB01.ctl
/dbf2/TDB/controlfile/TDB02.ctl'
/dbf3/TDB/controlfile/TDB03.ctl'

Redo log: The current redo log files location can be queried from the V$logfile view

SQL> SELECT member FROM v$logfile;

MEMBER
---------------------------------------------------------------------
/oracle/TDB/TDB11.rdo
/oracle/TDB/TDB12.rdo
/oracle/TDB/TDB21.rdo
/oracle/TDB/TDB22.rdo

Follow the below steps to move or rename a Redo log file

• Shutdown the Db and rename the file at operating system

SQL> Shutdown Immediate
SQL> ! /oracle/TDB/TDB11.rdo /dbf1/TDB/redo/TDB11.rdo
SQL> ! /oracle/TDB/TDB12.rdo /dbf1/TDB/redo/TDB12.rdo
SQL> ! /oracle/TDB/TDB21.rdo /dbf1/TDB/redo/TDB21.rdo
SQL> ! /oracle/TDB/TDB22.rdo /dbf1/TDB/redo/TDB22.rdo

• Start the database in mount mode and ALTER DATABASE RENAME FILE

SQL> Startup mount
SQL> ALTER DATABASE RENAME FILE '/oracle/TDB/TDB11.rdo’ to '/dbf1/TDB/redo/TDB11.rdo’;
SQL> ALTER DATABASE RENAME FILE '/oracle/TDB/TDB12.rdo’ to ‘/dbf1/TDB/redo/TDB12.rdo’;
SQL> ALTER DATABASE RENAME FILE '/oracle/TDB/TDB21.rdo’ to ‘/dbf1/TDB/redo/TDB21.rdo’;
SQL> ALTER DATABASE RENAME FILE '/oracle/TDB/TDB22.rdo’ to ‘/dbf1/TDB/redo/TDB22.rdo’;

• Open the database and verify

SQL> Alter database open;
SQL> SELECT member FROM v$logfile;

MEMBER
---------------------------------------------------------------------
/oracle/TDB/TDB11.rdo
/oracle/TDB/TDB12.rdo
/oracle/TDB/TDB21.rdo
/oracle/TDB/TDB22.rdo

We can also DROP and RECREATE the redo in different location. But make sure the group STATUS should be “INACTIVE” in order to drop.

• You can check the redo status from the V$log view

SQL> select group#, status from v$log;
GROUP# STATUS
---------- ----------------
1 CURRENT
2 INACTIVE

• Drop and recreate the redo log group with new location

SQL> ALTER DATABASE DROP LOGFILE GROUP 2;
SQL> ALTER DATABASE ADD LOGFILE GROUP 2 (‘/dbf1/TDB/redo/TDB21.rdo’, ‘/dbf1/TDB/redo/TDB22.rdo’) SIZE 100M;

• Switch the log file to change the current redo and recreate the other redo groups

SQL> Alter system switch log file;
SQL> ALTER DATABASE DROP LOGFILE GROUP 1;
SQL> ALTER DATABASE ADD LOGFILE GROUP 1 (‘/dbf1/TDB/redo/TDB11.rdo’, ‘/dbf1/TDB/redo/TDB12.rdo’) SIZE 100M;

Thanks & Regards.

http://oracleracexpert.com, Oracle ACE

ORA-20011: Approximate NDV failed: ORA-08103: object no longer exists

The ORA-20011, can be found in alert.log and this error occurs when DBMS_STATS:GATHER_STATS_JOB gathering stats

ORA-20011: Approximate NDV failed: ORA-08103: object no longer exists

You identify the issue set the following trace events
SQL> alter session set events '10384 trace name context forever , level 16384';

After research found that object truncate and starts are gathering at same time. In case of partition set granularity to ‘PARTITION’

DB level
SQL> exec dbms_stats.set_global_prefs(pname=>'GRANULARITY',pvalue=>'PARTITION')

Table level
SQL> exec dbms_stats.set_table_prefs(ownname=>'Owner_Name',tabname=>'<Table_Name>',pname=>'GRANULARITY',pvalue=>'PARTITION')

Run the gather stats after DDL or DML have been completed.

Also note that “ORA-08103 object no longer exists” error occurs when another user has deleted object since the operation began. In case of incomplete recovery of the object, the work around would be to remove references to the object or delete the object.

When using Global temporary table with ON COMMIT DELETE ROWS options, may encounter ORA-08103: object no longer exists when commit statement that followed right after the delete statement.

In this case recreation of global temporary table with ON COMMIT PRESERVE ROWS clause will helps to safely fetch data

There are several bugs related to ORA-8103 error, check oracle support for more details.

Bug 13618170

Bug 5523799
Bug 5637976

Thanks & Regards,

http://oracleracexpert.com, Oracle ACE