Oracle Database support matrix

Release Schedule of Current Database Releases (Doc ID 742060.1)

Oracle 19c Features and Enhancements

This document highlights several key features and enhancements introduced or improved in Oracle Database 19c, focusing on their functionality and practical implications.

1. Real-time Statistics (Exadata Only)

Oracle Database 19c introduces real-time statistics, extending online statistics gathering to include conventional DML statements. This feature allows statistics to be collected "on-the-fly" during INSERT, UPDATE, and MERGE operations.

• Availability: This feature is only available on Exadata and Exadata Cloud Service.

• Functionality: Statistics are gathered dynamically during conventional DML, providing the optimizer with up-to-date information for plan generation.

• Parameters (Default TRUE):

  • _optimizer_gather_stats_on_conventional_dml: Controls whether real-time statistics are gathered.

  • _optimizer_use_stats_on_conventional_dml: Controls whether the optimizer uses real-time statistics.

  • _optimizer_stats_on_conventional_dml_sample_rate: Defaults to 100%, indicating the sampling rate for collection.

• Disabling: You can set these parameters to FALSE to disable real-time statistics.

• Impact on Dictionary Views:

  • USER_TABLES.NUM_ROWS: This column does not reflect real-time statistics changes.

  • USER_TAB_STATISTICS.NOTES: Will show STATS_ON_CONVENTIONAL_DML if real-time stats are active.

  • USER_TAB_COL_STATISTICS.NOTES: Will also show STATS_ON_CONVENTIONAL_DML for columns.

• Limitations:

  • Direct Path INSERT ... SELECT: Real-time statistics have no effect.

  • DELETE operations: Real-time statistics have no effect.

  • Gathering statistics for a table (e.g., using DBMS_STATS) will wipe out the real-time statistics for that table.

2. High-Frequency Automatic Optimizer Statistics

This feature allows for more frequent, granular collection of optimizer statistics, improving the accuracy of execution plans.

• Enabling/Configuring:

  EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_STATUS','ON'); -- Ensure auto tasks are on
  EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTO_TASK_INTERVAL','300'); -- Set interval to 300 seconds (5 minutes)
  
  

• Interval: The minimum allowed value for AUTO_TASK_INTERVAL is 60 seconds, and the maximum is 900 seconds.

• Monitoring: You can check the execution status of automatic statistics gathering tasks using:

  SELECT OPID, ORIGIN, STATUS, TO_CHAR(START_TIME, 'DD/MM HH24:MI:SS' ) AS BEGIN_TIME,
         TO_CHAR(END_TIME, 'DD/MM HH24:MI:SS') AS END_TIME, COMPLETED, FAILED,
         TIMED_OUT AS TIMEOUT, IN_PROGRESS AS INPROG
  FROM DBA_AUTO_STAT_EXECUTIONS
  ORDER BY OPID;
  
  

3. Validate SPFILE Parameters for Primary and Standby

Oracle 19c introduces a command to validate SPFILE parameters, which is particularly useful in Data Guard environments to ensure consistency between primary and standby databases.

• Command:

  VALIDATE DATABASE {database-name} SPFILE;
  
  

  Replace {database-name} with the actual database name. This command checks for discrepancies or issues in the SPFILE.

4. Schema Only Accounts

Introduced earlier (from 9i onwards), schema-only accounts are a security best practice to restrict direct login access to schema owners.

• Purpose:

  • Restrict Direct Access: Prevents users from directly logging in as the schema owner using shared credentials.

  • Proxy Connections: Users access the schema to perform DDL/DML changes via proxy connections, where a different user (with a password) connects and then proxies to the schema-only account.

  • Auditing: Allows for better auditing, as you can track which specific proxy user performed which tasks within the schema.

• NO AUTHENTICATION Clause: When creating a schema-only account, the NO AUTHENTICATION clause is used. This allows a user to be created without a password, meaning direct connections are impossible, but proxy connections are enabled. This ensures that even if the schema owner account conceptually becomes "locked" (e.g., due to no password), proxy connections can still function.

5. Automatic Replication of Restore Points from Primary to Standby

In Oracle Data Guard, restore points can now be automatically replicated from the primary database to the standby database.

• Mechanism: Restore points are replicated through the redo stream, which is then applied by the Managed Recovery Process (MRP) on the standby.

• Primary Database State: For this replication to occur, the primary database must be in OPEN mode.

6. DML Operations on Active Data Guard Standby Databases

Oracle 19c enhances DML redirection capabilities for Active Data Guard, allowing DML operations to be performed on a read-only standby database by transparently redirecting them to the primary.

• Enabling DML Redirect: This feature needs to be enabled on both the Primary and Standby databases.

  ALTER SYSTEM SET adg_redirect_dml=TRUE SCOPE=BOTH;
  
  

• Connection Requirement: When connecting to the standby to perform DML that will be redirected, you must connect using a username/password. Connecting with sqlplus / as sysdba (OS authentication) will not work for DML redirection.

7. Inline External Table - EXTERNAL Clause (Zero DDL)

Oracle 19c introduces the ability to define external tables directly within a SQL query using the EXTERNAL clause, eliminating the need for separate DDL statements to create the external table object. This is often referred to as "Zero DDL" for external tables.

• Concept: There is no need for an external table to be explicitly created as a database object. The definition is embedded directly in the SELECT statement.

• Example: This example reads data from MY.txt (located in the MY_DIR directory), assuming it's a CSV file with three fields: object_id, owner, and object_name.

  SELECT *
  FROM EXTERNAL (
  (
  object_id NUMBER,
  owner VARCHAR2(128),
  object_name VARCHAR2(128)
  )
  TYPE oracle_loader
  DEFAULT DIRECTORY MY_DIR
  ACCESS PARAMETERS (
  RECORDS DELIMITED BY NEWLINE
  BADFILE MY_DIR
  LOGFILE MY_DIR:'inline_ext_tab_as_%a_%p.log'
  DISCARDFILE MY_DIR
  FIELDS CSV WITH EMBEDDED TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  MISSING FIELD VALUES ARE NULL (
  object_id,
  owner,
  object_name)
  )
  LOCATION ('MY.txt')
  REJECT LIMIT UNLIMITED
  ) inline_ext_tab
  ORDER BY 1;
  
  

  • object_id NUMBER, owner VARCHAR2(128), object_name VARCHAR2(128): Defines the column structure of the external data.

  • TYPE oracle_loader: Specifies the access driver.

  • DEFAULT DIRECTORY MY_DIR: Specifies the database directory object where the external file is located.

  • ACCESS PARAMETERS (...): Defines how the data is parsed (e.g., RECORDS DELIMITED BY NEWLINE, FIELDS CSV).

  • LOCATION ('MY.txt'): Specifies the external data file.

  • REJECT LIMIT UNLIMITED: Allows all rows to be processed, even if some have errors.

  • inline_ext_tab: This is an alias for the inline external table definition.

Udev rules - SYMLINK - Device Persistence - Oracle ASM - Linux

Udev Rules for Oracle ASM Device Persistence on Linux

This document explains how to configure Udev rules in Linux to ensure persistent device naming for Oracle Automatic Storage Management (ASM) disks. This is crucial for maintaining stable disk paths across reboots, which is a requirement for ASM.

Understanding Device Persistence with Udev

In Linux, device names like /dev/sda, /dev/sdb, etc., are not guaranteed to be consistent across reboots. This can cause issues for applications like Oracle ASM, which rely on stable paths to storage. Udev is a device manager for the Linux kernel that allows you to define rules to create persistent symbolic links (symlinks) to your disks, ensuring they always have the same, predictable name regardless of their boot-time enumeration.

Steps to Configure Udev Rules for ASM

This example demonstrates the process for a single disk (/dev/sda), but the principles apply to multiple disks.

1. Present Raw Disks

Ensure that the raw disks intended for Oracle ASM are presented to the Linux operating system. These disks should be unpartitioned and not formatted with any file system.

2. Identify the SCSI ID of the Disk

The SCSI ID (or ID_SERIAL) provides a unique, persistent identifier for the disk. This is the key to creating a stable Udev rule.

• Command:

  /lib/udev/scsi_id -g -u -d /dev/sda
  
  

  (Replace /dev/sda with the actual device path of your raw disk.)

• Example Output:

  3600224800cbc991b76c2a957f833fc66
  
  

  This hexadecimal string is the unique SCSI ID for the disk.

3. Create/Update the Udev Rules File

Create a new Udev rules file (e.g., 99-asm.rules) in the /etc/udev/rules.d/ directory. The 99 prefix ensures that these rules are processed late in the Udev sequence, typically after other system-generated rules.

• File: /etc/udev/rules.d/99-asm.rules

• Content Example (for one disk):

  KERNEL=="sd*", SUBSYSTEM=="block", ENV{DEVTYPE}=="disk", ENV{ID_SERIAL}=="3600224800cbc991b76c2a957f833fc66", SYMLINK+="asmdatadisk1", OWNER="grid", GROUP="asmadmin", MODE="0660"
  
  

  • KERNEL=="sd*": Matches any block device with a name starting with sd (e.g., sda, sdb, sdc).

  • SUBSYSTEM=="block": Specifies that the rule applies to block devices.

  • ENV{DEVTYPE}=="disk": Ensures the rule applies only to whole disks, not partitions.

  • ENV{ID_SERIAL}=="3600224800cbc991b76c2a957f833fc66": This is the critical part. It matches the unique SCSI ID obtained in step 2.

  • SYMLINK+="asmdatadisk1": Creates a symbolic link named asmdatadisk1 in /dev/ (e.g., /dev/asmdatadisk1) that points to the actual device (e.g., /dev/sda). The += ensures that if other rules also create symlinks, this one is added.

  • OWNER="grid", GROUP="asmadmin", MODE="0660": Sets the ownership and permissions of the symlink.

    • OWNER="grid": Sets the owner to the grid OS user (typically the Oracle Grid Infrastructure owner).

    • GROUP="asmadmin": Sets the group to asmadmin (the ASM administrative group).

    • MODE="0660": Sets permissions to read/write for owner/group, and no access for others.

• Important Note for Cloud Databases: The ENV{ID_SERIAL} attribute might not be consistently available or reliable for device persistence in some cloud environments (e.g., certain cloud-specific block storage types). In such cases, other attributes like ID_PATH or ID_WWN might be more appropriate, or cloud provider-specific persistent naming mechanisms should be used. The commented-out line in the original input (#ACTION=="add|change", ENV{ID_SCSI_SERIAL}=="...", SYMLINK+="asmdatadisk1", OWNER="grid", GROUP="asmadmin", MODE="0660") suggests an alternative that might be considered, but ID_SERIAL is generally preferred for on-premises setups.

4. Reload Udev Rules

After modifying the Udev rules file, you must reload the Udev rules and trigger the Udev system to apply the changes without requiring a system reboot.

• Command:

  udevadm control --reload-rules && udevadm trigger
  
  

After these steps, you should see the new symlink in the /dev/ directory, pointing to your raw disk, and it will persist across reboots. You can then use this persistent symlink (e.g., /dev/asmdatadisk1) when configuring your ASM disk groups.

Flashback Data Archive (Oracle Total Recall)

Oracle Flashback Data Archive (Total Recall) Configuration

Oracle Flashback Data Archive (FDA), also known as Oracle Total Recall, provides the ability to track and store historical changes to table data. This feature allows users to query past states of data without relying on traditional backup and recovery mechanisms.

Flashback Data Archive in Oracle 11g

In Oracle Database 11g, FDA management is primarily performed at the individual table level.

STEP 1: Create a Tablespace for FDA

A dedicated tablespace is required to store the historical data.

CREATE TABLESPACE FDA DATAFILE '+DATA_DG' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 30G;

STEP 2: Create Flashback Archive

Define the flashback archive, specifying its tablespace and retention period.

CREATE FLASHBACK ARCHIVE DEFAULT TABLESPACE FDA QUOTA 10G RETENTION YEAR;

STEP 3: Enable FDA on Respective Table

Enable FDA for a specific table.

ALTER TABLE your_table_name FLASHBACK ARCHIVE; -- Replace 'your_table_name' with the actual table name

STEP 4: Check FDA is Enabled on Respective Table

Verify the FDA status for tables.

SELECT owner_name, table_name, flashback_archive_name, archive_table_name, status
FROM dba_flashback_archive_tables
ORDER BY owner_name, table_name;

Flashback Data Archive in Oracle 12c and Later

Starting with Oracle Database 12c, FDA introduces the concept of managing logical groups of tables at an application level. This simplifies enabling or disabling FDA for multiple related tables.

STEP 1: Create a Tablespace for FDA

Similar to 11g, create a tablespace for FDA.

CREATE TABLESPACE FDA DATAFILE '+DATA_DG' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 30G;

STEP 2: Create Flashback Archive and Grant Privileges

Create the flashback archive and grant necessary privileges to the user who will manage FDA.

CREATE FLASHBACK ARCHIVE FDA_NAME TABLESPACE FDA QUOTA 10G RETENTION YEAR; -- FDA_NAME is the name of your flashback archive
GRANT FLASHBACK ARCHIVE ON FDA_NAME TO XYZ; -- Replace 'XYZ' with the username
GRANT FLASHBACK ARCHIVE ADMINISTRATOR TO XYZ;

Step 3: Enable FDA on Respective Table

Enable FDA for a specific table, associating it with the named flashback archive.

ALTER TABLE TEST FLASHBACK ARCHIVE FDA_NAME; -- Replace 'TEST' with your table name and 'FDA_NAME' with your archive name

Step 4: Set Context Level for Transaction Data

To ensure that context information (e.g., SYS_CONTEXT attributes) is stored with the transaction data, use DBMS_FLASHBACK_ARCHIVE.SET_CONTEXT_LEVEL.

BEGIN
  DBMS_FLASHBACK_ARCHIVE.SET_CONTEXT_LEVEL(level=>'ALL');
END;
/

Create a Group and Add a Table (Application Level Management)

Oracle 12c allows you to group tables under an application name for easier management.

-- Register an application for FDA
EXEC DBMS_FLASHBACK_ARCHIVE.REGISTER_APPLICATION(application_name=>'FDA_APP', flashback_archive_name=>'FDA_NAME');

-- Add a table to the registered application
EXEC DBMS_FLASHBACK_ARCHIVE.ADD_TABLE_TO_APPLICATION(application_name=>'FDA_APP', table_name=>'TEST', schema_name=>'XYZ');

Enable/Disable Group

You can enable or disable FDA for an entire application group.

-- Disable FDA for the application group
EXEC DBMS_FLASHBACK_ARCHIVE.DISABLE_APPLICATION(application_name=>'FDA_APP');

-- Enable FDA for the application group
EXEC DBMS_FLASHBACK_ARCHIVE.ENABLE_APPLICATION(application_name=>'FDA_APP');

CDB/PDB - 12.2, 18c, 19c new features

Oracle CDB/PDB Features and Management (12.2, 18c, 19c)

This document outlines key features and management operations for Container Databases (CDBs) and Pluggable Databases (PDBs) in Oracle Database versions 12.2, 18c, and 19c.

1. Creating a Container Database (CDB)

A CDB is the root container that hosts multiple PDBs.

CREATE DATABASE ... ENABLE PLUGGABLE DATABASE;

This command will create CDB$ROOT (the root container) and PDB$SEED (a template for creating new PDBs).

To verify the CDB status:

SELECT NAME, CDB, CON_ID FROM V$DATABASE;

2. Creating an Application Root (12.2, 18c, 19c)

An Application Root acts as a parent container for application PDBs, allowing for centralized management of common application data and metadata.

Connect to the CDB root before creating:

CONNECT TO the_cdb_root_user_as_sysdba; -- Example: CONNECT SYS/password@cdb_name AS SYSDBA;
CREATE PLUGGABLE DATABASE your_app_root_name AS APPLICATION CONTAINER;

3. Creating an Application Seed (12.2, 18c, 19c)

An Application Seed is a template PDB within an Application Root, used for creating new application PDBs.

Ensure the current container is the application root:

ALTER SESSION SET CONTAINER = your_app_root_name;
CREATE PLUGGABLE DATABASE AS SEED;

4. Creating a PDB from Seeds (12.2, 18c, 19c)

You can create new PDBs from PDB$SEED (in CDB$ROOT) or from an Application Seed (in an Application Root).

-- Connect to the CDB root
ALTER SESSION SET CONTAINER=CDB$ROOT;

-- Create a new PDB
CREATE PLUGGABLE DATABASE mypdbnew
  ADMIN USER MY_DBA IDENTIFIED BY "####"
  STORAGE (MAXSIZE UNLIMITED) -- Example storage clause
  DEFAULT TABLESPACE mypdbnew_tbs
  DATAFILE '/path/to/datafiles/mypdbnew01.dbf' SIZE 100M
  FILE_NAME_CONVERT =('/path/to/seed_datafiles/','/path/to/new_pdb_datafiles/'); -- Adjust paths
  -- Example: FILE_NAME_CONVERT =('/u01/app/oracle/oradata/CDB1/pdbseed/','/u01/app/oracle/oradata/CDB1/mypdbnew/');

-- Open the new PDB
ALTER PLUGGABLE DATABASE mypdbnew OPEN;

-- Verify user information in the CDB
SELECT con_id, username, default_tablespace, common FROM cdb_users WHERE con_id = (SELECT con_id FROM v$pdbs WHERE name = 'MYPDBNEW');

5. Moving PDBs / Plugging In a PDB (12.2, 18c, 19c)

This process involves unplugging a PDB from one CDB and plugging it into another, or into the same CDB.

-- Close the PDB to be unplugged
ALTER PLUGGABLE DATABASE mypdb CLOSE IMMEDIATE;

-- Unplug the PDB, creating an XML manifest file
ALTER PLUGGABLE DATABASE mypdb UNPLUG INTO '/backup_location/mypdb.xml';

This command creates mypdb.xml (the manifest file) and leaves the PDB's datafiles in their current location.

• Move Files: Manually move the PDB's datafiles (e.g., system.dbf, sysaux.dbf, mypdb.dbf) and the mypdb.xml file to the new desired location.

• Check Compatibility (in the target CDB):

  -- Connect to the target CDB root
  ALTER SESSION SET CONTAINER=CDB$ROOT;
  -- Run the check (no output means compatible, errors indicate issues)
  SELECT DBMS_PDB.CHECK_PLUG_COMPATIBILITY('/path/to/new_location/mypdb.xml') FROM DUAL;
  
  

• Create PDB by Plugging In:

  CREATE PLUGGABLE DATABASE mypdb
    USING '/path/to/new_location/mypdb.xml'
    SOURCE_FILE_NAME_CONVERT =('/old/path/','/new/path/') -- Adjust paths as needed
    NOCOPY; -- Use NOCOPY if datafiles are already moved to the target location
  
  

• Open the Plugged-In PDB:

  ALTER PLUGGABLE DATABASE mypdb OPEN;
  
  

  If in a RAC environment, ensure it's opened on all relevant nodes.

6. Cold Clone from Another PDB (12.2, 18c, 19c)

A cold clone creates a new PDB from an existing PDB while the source PDB is closed or in read-only mode.

-- Open source PDB in read-only mode (required for cold clone)
ALTER PLUGGABLE DATABASE mypdb OPEN READ ONLY FORCE;

-- Create the clone PDB
CREATE PLUGGABLE DATABASE mypdb_test
  FROM mypdb
  FILE_NAME_CONVERT =('/path/to/source_datafiles/','/path/to/clone_datafiles/'); -- Adjust paths

-- Open the cloned PDB
ALTER PLUGGABLE DATABASE mypdb_test OPEN;

(Note: For a Hot Clone, the source PDB does not need to be in read-only mode.)

7. Cold Clone from Non-CDB (12.2, 18c, 19c)

You can convert a non-CDB into a PDB and plug it into a CDB.

1. Generate XML Manifest from Non-CDB:

   • Connect to the non-CDB as SYSDBA.

   EXEC DBMS_PDB.DESCRIBE(pdb_name => 'NONCDB_NAME', xml_file => '/path/to/noncdb.xml');
   
   

2. Create PDB in CDB using XML:

   • Move the non-CDB's datafiles and the generated noncdb.xml file to the target CDB's desired location.

   • Connect to the target CDB root.

   CREATE PLUGGABLE DATABASE noncdb_pdb
     USING '/path/to/noncdb.xml'
     SOURCE_FILE_NAME_CONVERT =('/old/noncdb/datafiles/','/new/pdb/datafiles/')
     NOCOPY;
   
   

3. Run noncdb_to_pdb.sql: After plugging in, this script must be run inside the new PDB to complete the conversion.

   ALTER PLUGGABLE DATABASE noncdb_pdb OPEN;
   ALTER SESSION SET CONTAINER = noncdb_pdb;
   @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
   
   

8. Dropping a Pluggable Database (12.2, 18c, 19c)

-- Close the PDB first
ALTER PLUGGABLE DATABASE mypdb CLOSE;

-- Drop the PDB and its associated datafiles
DROP PLUGGABLE DATABASE mypdb INCLUDING DATAFILES;

9. Hot Clone a Remote PDB or Non-CDB (12.2+)

This feature allows cloning a PDB or non-CDB from a remote database over a database link. The source must use local undo mode.

-- Create a database link to the remote source database
CREATE DATABASE LINK db_link CONNECT TO remote_user IDENTIFIED BY remote_user_password USING 'remotedb_service_name';

-- Create the new PDB as a clone from the remote source
CREATE PLUGGABLE DATABASE newpdb FROM remotedb@db_link;

-- Open the new PDB
ALTER PLUGGABLE DATABASE newpdb OPEN;

-- If cloning from a non-CDB, run the conversion script
-- ALTER SESSION SET CONTAINER = newpdb;
-- @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

10. Relocate PDB (12.2+)

Relocating a PDB moves it from one CDB to another while keeping the PDB online during most of the operation.

-- Create a database link to the source CDB
CREATE DATABASE LINK db_link CONNECT TO remote_user IDENTIFIED BY remote_user_password USING 'source_cdb_service_name';

-- Create the PDB in the target CDB and initiate relocation
CREATE PLUGGABLE DATABASE remote_pdb_name FROM remote_pdb_name@db_link RELOCATE;

-- Open the relocated PDB in the target CDB
ALTER PLUGGABLE DATABASE remote_pdb_name OPEN;

11. PDB Archive Files (12.2+)

PDB archive files (.pdb extension) are self-contained archives that include both the PDB's XML manifest and its datafiles. This simplifies PDB transport.

-- Unplug the PDB into an archive file
ALTER PLUGGABLE DATABASE mypdb UNPLUG INTO '/backup_location/mypdb.pdb';
```mypdb.pdb` is an archive containing both the `.xml` file and the datafiles.

```sql
-- Create a PDB from an archive file
CREATE PLUGGABLE DATABASE mypdb_test USING '/backup_location/mypdb.pdb';

12. PDB Refresh (12.2+)

Refreshable PDB clones allow a PDB to be periodically updated from a remote source PDB.

-- Create a database link to the source PDB
CREATE DATABASE LINK db_link CONNECT TO remote_user IDENTIFIED BY remote_user_password USING 'remote_pdb_service_name';

-- Create a refreshable PDB clone in manual refresh mode
CREATE PLUGGABLE DATABASE newpdb FROM remote_pdb_name@db_link REFRESH MODE MANUAL;

-- Open the refreshable PDB (initially read-only)
ALTER PLUGGABLE DATABASE newpdb READ ONLY;

-- To perform a manual refresh:
ALTER PLUGGABLE DATABASE newpdb CLOSE IMMEDIATE;
ALTER PLUGGABLE DATABASE newpdb REFRESH;
ALTER PLUGGABLE DATABASE newpdb READ ONLY; -- PDB will be read-only after refresh

• Auto-refresh:

  ALTER PLUGGABLE DATABASE newpdb REFRESH MODE EVERY 120 MINUTES;
  
  

  This auto-refresh only occurs if the PDB is closed.

13. Proxy PDB (12.2+)

A Proxy PDB acts as a pointer to a PDB in a remote CDB, allowing local access to a remote PDB without actually moving its datafiles.

• Benefits:

  • Existing client connections unchanged: Clients can connect to the proxy PDB as if it were local.

  • Single entry point for cloud DB: Simplifies access to remote databases, especially in cloud environments.

  • Share an application root container: Enables sharing of an application root's content across multiple containers.

• Example (CDB 1 instance):

  -- Create a database link to the remote CDB
  CREATE DATABASE LINK db_clone_link CONNECT TO c##remote_clone_user IDENTIFIED BY remote_clone_user_password USING 'CDB2_SERVICE_NAME';
  
  -- Create the proxy PDB
  CREATE PLUGGABLE DATABASE PDB2_PROXY AS PROXY FROM PDB2@db_clone_link;
  
  -- Verify the proxy PDB
  SELECT pdb_name, is_proxy_pdb, status FROM dba_pdbs WHERE pdb_name = 'PDB2_PROXY';
  
  

  No longer DB link & link user required for subsequent access after creation.

• Accessing the Proxy PDB:

  ALTER SESSION SET CONTAINER=PDB2_PROXY;
  -- Now you are effectively connected to the remote PDB2 via the proxy.
  -- DDL and DML operations performed here will execute on the remote CDB2 instance.
  
  

14. Snapshot Carousel (PDB Archives) (18c+)

Snapshot Carousel provides a repository for periodic point-in-time copies (snapshots) of a PDB, enabling easy recovery or cloning to a specific point in time.

-- Create a PDB with snapshot mode enabled
CREATE PLUGGABLE DATABASE pdb_snap
  ADMIN USER MY_DBA IDENTIFIED BY "####"
  SNAPSHOT MODE EVERY 24 HOURS; -- Automatically creates a snapshot every 24 hours

-- Open the PDB
ALTER PLUGGABLE DATABASE pdb_snap OPEN;

-- MAX_PDB_SNAPSHOT can be changed between 0 to 8 (0 will delete existing snapshots)
ALTER PLUGGABLE DATABASE pdb_snap SET MAX_PDB_SNAPSHOTS = 5;

-- Manual snapshot creation
ALTER PLUGGABLE DATABASE pdb_snap SNAPSHOT xyz_snap;

-- Create a new PDB from one of the archives (snapshots)
CREATE PLUGGABLE DATABASE pdb_from_snap
  FROM pdb_snap
  USING SNAPSHOT xyz_snap;

15. Transportable Backups (18c+)

This feature supports using backups performed on a PDB before it is unplugged and plugged into a new container. This significantly streamlines PDB relocation for purposes like load balancing or migration between on-premises and cloud, as it avoids the need for new backups immediately before and after each PDB move.

16. Switchover Refreshable Clone PDB between CDBs (Migration) (18c+)

This allows for a planned or unplanned switchover of a refreshable clone PDB between different CDBs, facilitating PDB migration with minimal downtime.

• Planned Switchover:

  ALTER PLUGGABLE DATABASE your_pdb_name REFRESH MODE EVERY 2 MINUTES FROM remote_pdb_name@dblink SWITCHOVER;
  
  

  This command prepares the PDB for a switchover, automatically refreshing it.

• Unplanned Switchover (after a planned switchover setup):

  ALTER PLUGGABLE DATABASE your_pdb_name REFRESH; -- Perform a final refresh
  ALTER PLUGGABLE DATABASE your_pdb_name REFRESH MODE NONE; -- Disable refresh mode
  ALTER PLUGGABLE DATABASE your_pdb_name OPEN READ WRITE; -- Open the PDB in read-write mode
  
  

17. Transient No-Standby PDBs (Clone) (18c+)

This feature allows creating a hot clone of a PDB without it being replicated to a standby database, useful for temporary testing or development environments.

• Hot clone to transient PDB:

  CREATE PLUGGABLE DATABASE transient_pdb FROM source_pdb CLONE STANDBY=NONE;
  
  

• Cold clone of this transient PDB with standby: You can then create a cold clone of this transient PDB that does include standby replication.

• Drop transient PDB:

  DROP PLUGGABLE DATABASE transient_pdb INCLUDING DATAFILES;
  
  

18. AWR for Pluggable Database (12.2 onwards)

Starting from Oracle 12.2, AWR (Automatic Workload Repository) data can be collected at the PDB level, providing granular performance insights for individual PDBs.

• Enable AWR Auto Flush for PDBs:

  ALTER SYSTEM SET awr_pdb_autoflush_enabled=TRUE;
  
  

• View PDB Snapshots:

  SELECT * FROM awr_pdb_snapshot;

Avamar rman backup generates large amount of trace files

Avamar RMAN Backup Trace Files Management

When performing RMAN backups using Avamar, it is common to observe the generation of a large number of trace files. These trace files can consume significant disk space and may not always be necessary for routine operations. This document outlines common methods to manage and reduce the generation of these trace files.

1. Avamar GUI Configuration

The primary method to control trace file generation from the Avamar side is through its graphical user interface (GUI).

• Action: The Avamar team (or administrator) needs to set the tracing level to 'TRACE 0' within the Avamar GUI for the relevant RMAN backup policies or configurations.

• Impact: Setting TRACE 0 typically disables detailed tracing, significantly reducing the volume of trace files generated by the Avamar client during RMAN operations.

2. Oracle Database Event Setting

Database administrators (DBAs) can configure an Oracle database event to disable specific kernel-related tracing, which often contributes to the generation of numerous trace files during backup operations.

Permanent Fix (Requires Database Restart)

To make the change persistent across database restarts, set the event in the SPFILE.

• Command:

  ALTER SYSTEM SET EVENT='trace[krb.*] disk disable, memory disable' SCOPE=SPFILE SID='*';
  
  
  

• Explanation:

  • trace[krb.*]: Targets tracing related to the krb (kernel resource broker) component, which is often involved in backup and recovery processes.

  • disk disable: Prevents trace information from being written to disk.

  • memory disable: Prevents trace information from being stored in memory.

  • SCOPE=SPFILE: Ensures the change is written to the server parameter file and will persist after a database restart.

  • SID='*': Applies the change to all instances in a Real Application Clusters (RAC) environment. For a single instance, you can omit SID='*' or specify the instance SID.

• Effectiveness: This change will take effect only after a full database restart.

Temporary Fix (No Database Restart Required)

For an immediate, temporary reduction in trace file generation without a database restart, the event can be set at the session or system level without SCOPE=SPFILE.

• Command:

  ALTER SYSTEM SET EVENT='trace[krb.*] disk disable, memory disable';
  
  
  

• Explanation:

  • This command applies the event setting to the current running instance(s) immediately.

  • Limitation: This change is not persistent across database restarts. If the database is restarted, this command would need to be re-executed.

By implementing both the Avamar GUI setting and the Oracle database event, you can effectively manage and significantly reduce the large amount of trace files generated during Avamar RMAN backup operations. Both steps are generally recommended for comprehensive trace file management.

TDE (Transparent Data Encryption)

Implementing Transparent Data Encryption (TDE) on Oracle 12c Standalone Database

This document provides a step-by-step guide to implementing Transparent Data Encryption (TDE) on an Oracle 12c standalone database. TDE helps protect sensitive data at rest by encrypting datafiles.

Prerequisites

• Oracle Database 12c installed and running.

• Appropriate OS user (e.g., oracle) with permissions to create directories and modify Oracle configuration files.

• Familiarity with SQL*Plus and basic OS commands.

Implementation Steps

1. Create Wallet Directory

Create a dedicated directory on the file system to store the TDE wallet (keystore). This directory should have restricted permissions.

mkdir -p /test/WALLET

Note: Ensure the oracle OS user has appropriate read and write permissions to this directory.

2. Modify sqlnet.ora

Update the sqlnet.ora file to specify the location of the encryption wallet. This file is typically located in $ORACLE_HOME/network/admin/.

Add the following lines to sqlnet.ora:

ENCRYPTION_WALLET_LOCATION =
  (SOURCE = (METHOD = FILE)(METHOD_DATA =
    (DIRECTORY = /test/WALLET)))

Important: Ensure this sqlnet.ora file is in the correct network/admin directory accessible by your database instance. If you have a separate Grid Infrastructure Home (Grid_Home), you might need to copy sqlnet.ora from the Grid Home's network/admin to the database home's network/admin if it's managed externally, or ensure the path is consistent.

3. Create Keystore (Wallet)

In Oracle 12c, the standard and recommended way to create a TDE keystore is using the ADMINISTER KEY MANAGEMENT command from SQL*Plus.

Connect to SQL*Plus as SYSDBA:

ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/test/WALLET/' IDENTIFIED BY "YOUR_WALLET_PASSWORD";

Replace "YOUR_WALLET_PASSWORD" with a strong password for your TDE wallet. Remember this password, as it's needed to open the wallet manually.

Verify File Creation: After executing the command, check the wallet directory. You should see an ewallet.p12 (or ewallet.p01 in older versions/configurations) file created.

ls -ltr /test/WALLET/

Example Output:

total 4
-rw------- 1 oracle asmadmin 2555 May 27 10:30 ewallet.p12

4. Open Keystore

Before you can activate keys or encrypt data, the keystore must be opened.

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "YOUR_WALLET_PASSWORD";

Note: If you copied sqlnet.ora from a Grid Home, ensure it's in the correct database network/admin path before attempting to open the keystore.

5. Activate the Master Encryption Key

Once the keystore is open, you can activate the master encryption key. This command generates a new master key and sets it as the active key for TDE operations.

First, check current key status (should show no keys initially):

SET LINESIZE 100
SELECT con_id, key_id FROM v$encryption_keys;

Example Output:

no rows selected

Now, activate the key:

ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY "YOUR_WALLET_PASSWORD" WITH BACKUP;

Example Output:

keystore altered.
```WITH BACKUP` option creates a backup of the current keystore before generating a new key, which is good practice.

Verify the new master key:
```sql
SELECT con_id, key_id FROM v$encryption_keys;

Example Output:

  CON_ID KEY_ID
---------- ------------------------------------------------------------------------------
         0 TTTTTTTTTTTTTTTTGfeeeeeeeeeeeeeeee

The KEY_ID will be a unique identifier for your master encryption key.

6. Enable Autologin for the Keystore (Recommended)

By default, the wallet is password-protected. This means you would need to manually open the wallet with the password every time the database restarts. To avoid this, enable autologin.

First, check the current wallet status:

column WRL_PARAMETER format a30
column WRL_TYPE format a10
column STATUS format a10

SELECT * FROM v$encryption_wallet;

Example Output (before autologin):

WRL_TYPE   WRL_PARAMETER               STATUS     WALLET_TYPE          WALLET_OR FULLY_BAC     CON_ID
---------- ------------------------------ ---------- -------------------- --------- --------- ----------
FILE       /test/WALLET/                  OPEN       PASSWORD             SINGLE    NO               0

Notice WALLET_TYPE is PASSWORD.

Now, enable autologin:

ADMINISTER KEY MANAGEMENT CREATE AUTO_LOGIN KEYSTORE FROM KEYSTORE '/test/WALLET/' IDENTIFIED BY "YOUR_WALLET_PASSWORD";

This command creates a cwallet.sso file in the wallet directory.

Restart the database to confirm the autologin functionality.

After restart, check the wallet status again:

ls -ltr /test/WALLET/

Example Output:

total 8
-rw------- 1 oracle asmadmin 2555 May 27 10:30 ewallet.p12
-rw------- 1 oracle asmadmin 2580 May 27 10:35 cwallet.sso

Now, query v$encryption_wallet:

SELECT * FROM v$encryption_wallet;

Example Output (after autologin):

WRL_TYPE   WRL_PARAMETER               STATUS     WALLET_TYPE          WALLET_OR FULLY_BAC     CON_ID
---------- ------------------------------ ---------- -------------------- --------- --------- ----------
FILE       /test/WALLET/                  OPEN       AUTOLOGIN            SINGLE    NO               0

The WALLET_TYPE should now be AUTOLOGIN.

7. Create Encrypted Tablespace

Now that TDE is configured and the master key is active, you can create encrypted tablespaces. Any data stored in these tablespaces will be automatically encrypted by TDE.

CREATE TABLESPACE TDE_TESTDATA
  DATAFILE '+DATA_DG' SIZE 200M AUTOEXTEND ON NEXT 100M MAXSIZE 8192M
  LOGGING
  ONLINE
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
  BLOCKSIZE 8K
  SEGMENT SPACE MANAGEMENT AUTO
  ENCRYPTION USING 'AES256' -- Specify the encryption algorithm
  DEFAULT STORAGE(ENCRYPT)  -- Data in this tablespace will be encrypted by default
  FLASHBACK ON;

Replace +DATA_DG with your actual ASM disk group or file system path.

Once the encrypted tablespace is created, you can create tables or move existing tables and indexes into it as usual. Oracle will handle the encryption and decryption transparently.

All you want to know about CDBs , PDBs and multitenant databases

Oracle Multitenant Architecture: CDBs, PDBs, and Key Features

Oracle's Multitenant Architecture, introduced in Oracle Database 12c, fundamentally changes how databases are managed by allowing multiple Pluggable Databases (PDBs) to reside within a single Container Database (CDB). This architecture provides significant benefits for consolidation, agility, and resource management.

What are CDBs and PDBs?

• Container Database (CDB): From a Database Administrator's (DBA) point of view, the CDB is the single, overarching database that contains all the PDBs. It includes common Oracle metadata, background processes, and shared memory (SGA).

• Pluggable Database (PDB): From an application's point of view, a PDB is an independent, fully functional database. Applications connect to a PDB as if it were a traditional non-CDB. Many PDBs can be plugged into a single Multitenant CDB.

Why Use Multitenant Databases?

The multitenant architecture offers numerous advantages:

• Rapid Provisioning (Via Clones): New PDBs can be quickly provisioned by cloning existing PDBs or the PDB$SEED, drastically reducing deployment time.

• Online PDB Relocate: PDBs can be relocated between CDBs (or within the same CDB) while remaining online, minimizing downtime.

• Sharing Background Processes: All PDBs within a CDB share the same set of background processes (e.g., PMON, SMON, DBWn), reducing the overall process footprint.

• Sharing SGA: PDBs share the same System Global Area (SGA) of the CDB, leading to more efficient memory utilization.

• Minimize CAPEX and OPEX: By consolidating multiple databases onto a single CDB, hardware resource requirements (CAPEX) and operational overhead (OPEX) are significantly reduced.

• Single Backup for CDB: A single backup operation for the CDB protects all contained PDBs, simplifying backup strategies.

• Automatically Standby: All PDBs within a CDB are automatically protected by a single Data Guard standby database configured for the CDB.

• Perform Rapid Upgrades: Upgrades are performed at the CDB level, meaning all PDBs are upgraded simultaneously, streamlining the upgrade process.

• Reference Data in a Different CDB: PDBs can access data in other PDBs or even other CDBs using database links, facilitating data sharing.

• Isolate Grants within PDBs: Security grants and user management can be isolated within individual PDBs, enhancing security and simplifying administration.

• Refreshable Clone: A read-only clone of a PDB that can periodically synchronize with its source PDB, useful for reporting or testing environments.

• Snapshot Copy PDB: A point-in-time copy of a PDB. Note that this PDB cannot be unplugged from the CDB root or application root.

Multitenant Database Structure

• The System Container: This includes the CDB$ROOT (the root container) and all PDBs directly plugged into the CDB$ROOT.

• CDB$ROOT: This is the mandatory root container of a CDB. It stores Oracle system metadata only and is exactly one per CDB.

• PDB$SEED: This is a system-supplied template PDB within CDB$ROOT. It cannot be dropped and serves as the source for creating new PDBs.

• Application Container: An application container consists of exactly one application root and the application PDBs plugged into this root. It allows for managing a set of related PDBs as a single application.

• Application Root: The parent container for application PDBs within an application container.

• Application PDB: If a PDB belongs to an application container, then it is an application PDB.

• Application Seed: An optional application PDB within an application root, serving as a template for creating new application PDBs.

Administration Roles

Different administrative roles exist to manage the various containers:

• CDB Administrator: Manages the entire CDB, including CDB$ROOT and all PDBs.

• Application Container Administrator: Manages a specific application container, including its application root and application PDBs.

• Application PDB Administrator: Manages PDBs within a specific application container.

• PDB Administrator: Manages PDBs that are not part of an application container (i.e., directly plugged into CDB$ROOT).

Key Concepts

• SYS User: SYS is a common user in the CDB. Every PDB is conceptually "owned" by SYS in that it's the superuser within each PDB.

• Cross-PDB Access: By default, a user connected to one PDB must use database links to access objects in a different PDB.

• PDB Lifecycle:

  • To open a PDB, you must start its respective service.

  • To stop a PDB: ALTER PLUGGABLE DATABASE PDB_NAME CLOSE IMMEDIATE;

• PDB$SEED Contents: Contains standard Oracle schemas and objects, including SYSTEM, SYSAUX, TEMP, and UNDO tablespaces, serving as a clean base for new PDBs.

Useful Commands

• Show current container name:

  show conn_name
  
  

• List all containers (CDBs and PDBs):

  SELECT NAME, CON_ID, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID;
  
  

• View common users in the CDB:

  SELECT con_id, username, default_tablespace, common FROM cdb_users;
  
  

• List all PDBs:

  SELECT PDB_NAME FROM DBA_PDBS;
  
  

How Hot Clones Work

When performing a hot clone of a PDB (where the source PDB remains open and available), Oracle ensures data consistency by:

• Applying Redo: The cloning process applies redo logs from the source PDB to catch up the cloned PDB to the point-in-time when the clone operation started.

• Applying Undo: It then applies undo information to roll back any uncommitted transactions that were active on the source PDB at the time of cloning, ensuring the cloned PDB is transactionally consistent.

PDB/CDB New Features (12.2, 18c, 19c)

Oracle continuously enhances the multitenant architecture with each release:

Oracle Database 12.2 Enhancements

• Hot Clone a Remote PDB or Non-CDB: Allows cloning a PDB or non-CDB from a remote database over a database link while the source is online. The source must use local undo mode.

• Relocate PDB: Enables moving a PDB from one CDB to another while keeping the PDB online during most of the operation.

• PDB Archive Files (.pdb): Introduces self-contained .pdb archive files that bundle both the PDB's XML manifest and its datafiles, simplifying PDB transport.

• PDB Refresh: Allows creating a refreshable PDB clone that can be periodically updated from a remote source PDB, with options for manual or auto-refresh.

• Proxy PDB: Creates a logical pointer to a PDB in a remote CDB, allowing local access to a remote PDB without actually moving its data. Benefits include unchanged client connections, a single entry point, and sharing an application root.

• AWR for Pluggable Database: Enables granular AWR data collection at the PDB level, providing performance insights for individual PDBs. (ALTER SYSTEM SET awr_pdb_autoflush_enabled=TRUE;)

Oracle Database 18c Enhancements

• Snapshot Carousel (PDB Archives): A repository for periodic point-in-time copies of a PDB, enabling easy recovery or cloning to a specific point in time. PDBs can be created with SNAPSHOT MODE EVERY X HOURS or manually with ALTER PLUGGABLE DATABASE SNAPSHOT.

• Transportable Backups: Supports using backups performed on a PDB prior to it being unplugged and plugged into a new container, facilitating agile PDB relocation without requiring immediate pre/post-move backups.

• Switchover Refreshable Clone PDB between CDBs: Allows for planned or unplanned switchover of a refreshable clone PDB between different CDBs, enabling PDB migration with minimal downtime.

• Transient No-Standby PDBs (Clone): Allows creating a hot clone of a PDB without it being replicated to a standby database, useful for temporary testing or development.

Oracle Database 19c Enhancements

• Real-time Statistics (Exadata Only): Extends online statistics gathering to include conventional DML statements, collecting statistics "on-the-fly" during INSERT, UPDATE, and MERGE operations. (Requires Exadata).

• High-Frequency Automatic Optimizer Statistics: Allows for more frequent, granular collection of optimizer statistics, improving the accuracy of execution plans.

• Validate SPFILE Parameters: New command to validate SPFILE parameters, useful for consistency checks, especially in Data Guard environments.

• Schema Only Accounts: While existing from 9i, their use is increasingly emphasized for security best practices in multitenant environments, restricting direct login to schema owners.

• Automatic Replication of Restore Points: Restore points created on the primary database are automatically replicated to the standby database via the redo stream.

• DML Operations on Active Data Guard Standby Databases: Enhances DML redirection, allowing DML to be performed on a read-only standby by transparently redirecting it to the primary (requires adg_redirect_dml=TRUE and username/password connection).

• Inline External Table (EXTERNAL clause): Allows defining external tables directly within a SQL query, eliminating the need for separate DDL statements ("Zero DDL").

This comprehensive overview should provide a solid understanding of Oracle's Multitenant Architecture and its evolution across recent database releases.

PDB/CDB new features (12.2, 18c, 19c) 

http://enteringtothedbaworld.blogspot.com/2019/05/cdb.html

Oracle architecture Diagram [ Multitenant and Single tenant]

Oracle 12C :

Single tenant Architecture Diagram :
https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/12c/r1/poster/OUTPUT_poster/pdf/Database%20Architecture.pdf



Multitenant Architecture Diagram :
https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/12c/r1/poster/OUTPUT_poster/pdf/Multitenant%20Architecture.pdf

Recreate Lob table or big table in PARALLEL

Recreating LOB/Big Tables in Parallel

This section provides steps to recreate a large table, potentially containing LOBs, in parallel for improved performance. This is useful for reorganizing data, applying new storage attributes, or simply moving data efficiently.

Source Table: APP_USER.APP_TABLE1 Target Table: APP_USER.APP_TABLE2

Step 1: Create a Sequence (for Logging Timing)

Create a sequence to generate unique IDs for logging the start and end times of the operation.

CREATE SEQUENCE Myuser.T_SQ
  START WITH 1
  INCREMENT BY 1
  NOCACHE;

-- Create a table to log the job times (if it doesn't exist)
CREATE TABLE Myuser.job_time (
    t_id    NUMBER,
    t_name  VARCHAR2(100),
    t_type  VARCHAR2(10),
    t_time  DATE
);

Step 2: Create a Procedure

Create a PL/SQL procedure that performs the parallel insert operation and logs its timing.

CREATE OR REPLACE PROCEDURE Myuser.my_proc AS
  v_seq NUMBER;
BEGIN
  -- Enable parallel processing for the current session
  EXECUTE IMMEDIATE 'ALTER SESSION SET PARALLEL_FORCE_LOCAL=TRUE';
  EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';

  -- Get the next sequence value for logging
  SELECT Myuser.T_SQ.NEXTVAL INTO v_seq FROM dual;

  -- Log the start time of the operation
  INSERT INTO Myuser.job_time (t_id, t_name, t_type, t_time) VALUES (v_seq, 'APP_TABLE1', 'START', SYSDATE);
  COMMIT;

  -- Perform the parallel insert from source to target table
  -- APPEND hint for direct path insert, PARALLEL hint for parallel execution
  INSERT /*+ APPEND PARALLEL(A,60) */ INTO APP_USER.APP_TABLE2 A
  SELECT /*+ PARALLEL(B,60) */ * FROM APP_USER.APP_TABLE1 B;
  COMMIT;

  -- Log the end time of the operation
  INSERT INTO Myuser.job_time (t_id, t_name, t_type, t_time) VALUES (v_seq, 'APP_TABLE1', 'END', SYSDATE);
  COMMIT;

EXCEPTION
  WHEN OTHERS THEN
    -- Log any errors
    INSERT INTO Myuser.job_time (t_id, t_name, t_type, t_time) VALUES (v_seq, 'APP_TABLE1', 'ERROR', SYSDATE);
    COMMIT;
    RAISE; -- Re-raise the exception after logging
END;
/

Note:

• PARALLEL(A,60) and PARALLEL(B,60) hints suggest using 60 parallel slaves. Adjust this number based on your system's CPU cores and I/O capabilities.

• APPEND hint performs a direct-path insert, which is faster for large data volumes as it bypasses the buffer cache.

• Ensure that APP_USER.APP_TABLE2 is already created with the desired structure (including LOB segments if APP_TABLE1 has them) and any necessary indexes or constraints are handled separately.

Step 3: Execute Procedure

Execute the procedure to start the parallel table recreation process.

EXEC Myuser.my_proc;

After execution, you can query Myuser.job_time to check the start and end times of the operation.

This comprehensive overview should provide a solid understanding of Oracle's Multitenant Architecture and its evolution across recent database releases.

Good motivational books to read 2025

"The Monk Who Sold His Ferrari" by Robin Sharma: A classic allegorical tale offering profound lessons on living a more fulfilling life.

"No Excuses: The Power of Self-Discipline" by Brian Tracy: A practical guide to developing self-discipline in various aspects of life to achieve greater success.

"Atomic Habits" by James Clear: Focuses on how tiny changes can lead to remarkable results. It's practical and actionable for building good habits and breaking bad ones.

"The 7 Habits of Highly Effective People" by Stephen Covey: A timeless classic that provides a holistic, integrated, principle-centered approach for solving personal and professional problems.

"Grit: The Power of Passion and Perseverance" by Angela Duckworth: Explores why talent isn't the only factor for success, highlighting the importance of passion and long-term perseverance.

"Mindset: The New Psychology of Success" by Carol S. Dweck: Introduces the concepts of fixed and growth mindsets and how they impact our ability to learn and grow.

"Can't Hurt Me: Master Your Mind and Defy the Odds" by David Goggins: An intense and inspiring memoir about overcoming incredible adversity through extreme mental toughness.

"The Power of Habit" by Charles Duhigg: Delves into the science behind habit formation in individuals, organizations, and societies.

"Drive: The Surprising Truth About What Motivates Us" by Daniel H. Pink: Challenges traditional ideas of motivation and explores the power of autonomy, mastery, and purpose.

query execution taking long time in oracle (parse bug)

Oracle Query Execution Long Time Due to Parsing Bug

Issue Description

In certain Oracle environments, a specific query has been observed to exhibit erratic execution times, ranging significantly from approximately 3 seconds to 50 seconds. Upon detailed analysis, it was determined that the variability in execution time was primarily attributable to the parsing phase of the query.

This prolonged parsing time was often exacerbated by sub-optimal application coding practices, which led to the query undergoing frequent hard parses rather than leveraging soft parsing. Hard parsing is resource-intensive and contributes significantly to query latency when repeated often.

Resolution

The root cause of this inconsistent parsing performance was identified as a parsing bug within the Oracle Database software. The following workaround was implemented to mitigate the issue:

ALTER SYSTEM SET "_fix_control"='13836796:OFF' SCOPE=BOTH SID='*';
ALTER SYSTEM SET "_optimizer_cost_based_transformation"=OFF SCOPE=BOTH SID='*';

Explanation of Parameters:

• _fix_control='13836796:OFF': This is a hidden parameter used to disable a specific internal Oracle fix (identified by bug ID 13836796). Setting it to OFF reverts a change that might be causing the parsing issue.

• _optimizer_cost_based_transformation=OFF: This hidden parameter controls whether the optimizer performs cost-based query transformations. Disabling it can prevent certain transformation paths that might interact negatively with the parsing bug, leading to more stable execution plans and faster parsing.

Important Note:

It is crucial to understand that setting hidden parameters (those prefixed with an underscore _) should generally be avoided without prior consultation and guidance from Oracle Support. Hidden parameters are internal to Oracle and their behavior or impact can change between database versions or even patches, potentially leading to unforeseen side effects or stability issues. Always open a Service Request (SR) with Oracle Support for confirmation and advice before applying such changes in a production environment.

Parallel DDL and DML (create table as select)

Oracle Parallel DDL and DML Operations

Oracle Database provides powerful capabilities for parallelizing both Data Definition Language (DDL) and Data Manipulation Language (DML) operations. This can significantly reduce the execution time for large operations such as creating tables from existing data, or inserting large volumes of data.

Parallel DDL: CREATE TABLE AS SELECT

Parallel DDL allows the creation of database objects (like tables, indexes) to be performed by multiple parallel execution servers. This is particularly beneficial when creating a new table based on a SELECT statement from a large existing table.

Example: Creating a new table APP_SCHEMA2.APP_TABLE2 from APP_SCHEMA1.APP_TABLE1 in parallel.

CREATE TABLE APP_SCHEMA2.APP_TABLE2 PARALLEL 15 AS
SELECT /*+ PARALLEL(A,15) */ *
FROM APP_SCHEMA1.APP_TABLE1 A;

Explanation:

• PARALLEL 15 (in CREATE TABLE clause): This specifies the default degree of parallelism (DOP) for the newly created table APP_SCHEMA2.APP_TABLE2. Any subsequent DML or DDL on this table might use this DOP if parallel execution is enabled.

• /*+ PARALLEL(A,15) */ (hint in SELECT clause): This hint explicitly tells the optimizer to use 15 parallel execution servers for the SELECT operation on APP_SCHEMA1.APP_TABLE1. The CREATE TABLE AS SELECT operation will then leverage this parallelism to read data and populate the new table concurrently.

Benefits: Faster table creation, especially for very large tables, by distributing the work among multiple processes.

Parallel DML: INSERT Operations

Parallel DML enables INSERT, UPDATE, DELETE, and MERGE statements to be executed by multiple parallel execution servers. For INSERT statements, this is most effective with direct-path inserts.

Prerequisite: Enable parallel DML for the session.

ALTER SESSION ENABLE PARALLEL DML;

Example: Inserting data from APP_USER1.APP_TABLE1 into APP_USER2.APP_TABLE2 using parallel DML.

INSERT /*+ APPEND PARALLEL(A,60) */ INTO APP_USER2.APP_TABLE2 A
SELECT /*+ PARALLEL(B,60) */ *
FROM APP_USER1.APP_TABLE1 B;

Explanation:

• ALTER SESSION ENABLE PARALLEL DML;: This statement is mandatory to enable parallel DML for the current session. Without it, the parallel hints in the DML statement will be ignored.

• /*+ APPEND */: This hint instructs Oracle to perform a direct-path insert. Direct-path inserts write data directly to the datafiles, bypassing the buffer cache, which is significantly faster for bulk data loading. It also acquires an exclusive table lock during the operation.

• /*+ PARALLEL(A,60) */ (hint in INSERT clause): This hint specifies that the INSERT operation on APP_USER2.APP_TABLE2 should use 60 parallel execution servers.

• /*+ PARALLEL(B,60) */ (hint in SELECT clause): This hint specifies that the SELECT operation on APP_USER1.APP_TABLE1 should also use 60 parallel execution servers.

Benefits: Dramatically faster data insertion for large datasets by leveraging multiple CPUs and I/O channels.

Important Considerations

• Resource Consumption: Parallel operations consume more CPU, memory, and I/O resources. Ensure your system has sufficient capacity.

• Table Locking: Direct-path inserts (APPEND hint) acquire an exclusive lock on the target table, preventing other DML operations on that table until the insert completes.

• Undo Generation: Direct-path inserts generate minimal undo, which is another reason for their speed.

• Degree of Parallelism (DOP): The optimal DOP depends on your hardware (number of CPU cores, I/O bandwidth) and the nature of the operation. Experimentation is often required to find the best value.

• Indexing: Indexes on the target table will be maintained during parallel DML, but this can impact performance. For very large inserts, it might be faster to drop indexes, perform the insert, and then rebuild the indexes.

• Transaction Management: Parallel DML operations are part of a transaction and require a COMMIT or ROLLBACK.

By strategically using parallel DDL and DML, you can significantly improve the performance of large-scale data operations in your Oracle database.

DB IOPS & Throughput from AWR report Oracle DB

Detailed I/O Metrics and Network Throughput

The AWR report provides more granular I/O and network metrics, often summarized in an "I/O Profile" or found within "Instance Activity Stats."

I/O Profile (Summary):

• IOPS: Total Requests: Read + Write Per Second

• Throughput (MB): Total (MB): Read + Write Per Second

Mapping from "Instance Activity Stats" to "IO Profile":

Instance Activity Stats Metric	IO Profile Metric (Per Second)
physical read total IO requests	Total Requests: Read per Second
physical write total IO requests	Total Requests: Write Per Second
physical read total bytes / 1024 / 1024	Total (MB): Read per Second
physical write total bytes / 1024 / 1024	Total (MB): Write Per Second

Mapping from "Load Profile" to "Instance Activity Stats":

Load Profile Metric	Instance Activity Stats Metric
Read IO requests	physical read IO requests
Write IO requests	physical write IO requests
Read IO(MB)	physical read bytes
Write IO(MB)	physical write bytes

Network Throughput:

These metrics provide insight into network activity related to SQL*Net communication.

• bytes received via SQL*Net from client

• bytes sent via SQL*Net to client

• MB received via SQL*Net from client

• MB sent via SQL*Net to client

Avamar Client Installation for Oracle 12cR2 RAC Database

This document outlines the step-by-step process for installing and configuring the Avamar Client and RMAN plugin on an Oracle 12cR2 Real Application Clusters (RAC) database. This setup enables Avamar to perform RMAN backups of your RAC database.

Prerequisites

• Access to the Avamar Client and RMAN plugin RPM files.

• root privileges on all RAC nodes.

• Knowledge of your Oracle Clusterware Home and Oracle Home paths.

• Avamar Administrator Server address and domain.

Installation and Configuration Steps

Step 1: Install Avamar Client RPM

Install the core Avamar client RPM package on all RAC nodes.

rpm -ivh AvamarClient-linux-sles11-x86_64-7.5XXXXXX.rpm

(Replace AvamarClient-linux-sles11-x86_64-7.5XXXXXX.rpm with the actual filename of your Avamar Client RPM.)

Step 2: Install Avamar RMAN RPM

Install the Avamar RMAN plugin RPM package on all RAC nodes. This RPM provides the necessary integration for RMAN backups.

rpm -ivh AvamarRMAN-linux-sles11-x86_64-7.5XXXXXXXX.rpm

(Replace AvamarRMAN-linux-sles11-x86_64-7.5XXXXXXXX.rpm with the actual filename of your Avamar RMAN RPM.)

Step 3: Client Configuration for RAC Node 1

Perform the RAC client configuration on the first node using the rac_config utility.

Navigate to the Avamar client binaries directory:

cd /usr/local/avamar/bin/

Execute the rac_config script:

[root@Hostname1 bin]# ./rac_config

Follow the prompts as shown in the example:

• Enter the path of Oracle Clusterware Home: /XXXX/XXXXX/product/12.2.0/grid

  • (This should be your actual Grid Infrastructure Home path.)

• Using ORACLE_HOME: /XXXX/XXXXX/product/12.2.0/grid

  • (Confirms the detected Oracle Home, which should be the Grid Home.)

• Setting PATH set for Oracle commands

• Oracle cluster version 12cR2

• Do you want to configure on a cluster shared filesystem? [y/n] [y]: n

  • (Choose 'n' if /usr/local/avamar/var is not on a shared filesystem. If you have a shared filesystem for Avamar configuration, you might choose 'y'.)

• Enter the full path of var directory location[]: /usr/local/avamar/bin/var

  • (This is where Avamar will store its variable data. Ensure it's local to each node if not using a shared filesystem.)

• Using /usr/local/avamar/bin/var as var directory location

• Enter the cluster scan name [scanXXXX]: scanXXXX

  • (Enter your Oracle SCAN name. If it's already detected correctly, you can just press Enter.)

• Using scanXXXX as hostname

• Now adding resource EMCagent.

• The resource EMCagent is added successfully.

The script will then provide instructions for the next steps:

1. Run rac_config with the same var directory on other nodes.

2. Run /usr/local/avamar/ora_rac/bin/avregister to register and activate this client with the Administrator server.

3. Copy cid.bin and avagent.cfg from Hostname1 to other nodes.

Step 4: Client Configuration for RAC Node 2 (and other nodes)

Repeat the rac_config process on each subsequent RAC node (e.g., Hostname2). Ensure you provide the same var directory location as used on Hostname1.

cd /usr/local/avamar/bin/
[root@Hostname2 bin]# ./rac_config

Follow the same prompts, providing consistent information.

Step 5: Register Client to Avamar Server

After rac_config has been run on all nodes, register the client with the Avamar Administrator server from one of the RAC nodes (e.g., Hostname1).

[root@Hostname1 bin]# /usr/local/avamar/ora_rac/bin/avregister

Follow the prompts:

• Enter the Administrator server address (DNS text name or numeric IP address, DNS name preferred): Backup server name/IP

  • (Provide the hostname or IP address of your Avamar Administrator server.)

• Enter the Avamar server domain [clients]: (Press Enter for default 'clients' or specify your domain)

The registration process will then proceed, showing output similar to this:

avagent.d Info: Client Agent not running.           [PASSED]
avagent Info : Logging to /usr/local/avamar/ora_rac/var/avagent.log
avagent Info : - Reading /usr/local/avamar/ora_rac/var/avagent.cmd
avagent.d Info: Client activated successfully.      [  OK  ]
avagent Info : Logging to /usr/local/avamar/ora_rac/var/avagent.log
avagent Info : - Reading /usr/local/avamar/ora_rac/var/avagent.cmd
avagent Info : daemonized as process id 25161
avagent.d Info: Client Agent started.               [  OK  ]
avagent.d Info: Stopping Avamar Client Agent (avagent)...
avagent.d Info: Client Agent stopped.               [  OK  ]
CRS-2672: Attempting to start 'EMCagent' on 'Hostname1'
CRS-2676: Start of 'EMCagent' on 'Hostname1' succeeded
Registration Complete.

Step 6: Transfer Configuration Files to Other Nodes

The avregister command creates or updates critical configuration files (cid.bin and avagent.cfg) on the node where it was executed. These files need to be copied to the corresponding var directory on all other RAC nodes to ensure consistent client registration across the cluster.

cp /usr/local/avamar/ora_rac/var/cid.bin /usr/local/avamar/ora_rac/var/avagent.cfg Hostname2:/usr/local/avamar/ora_rac/var/

(Replace Hostname2 with the actual hostname of your second RAC node, and repeat for any other nodes in the cluster.)

After these steps, your Avamar client and RMAN plugin should be successfully installed and configured across your Oracle 12cR2 RAC database, ready for backup operations.