Oracle Database “18c New Features”

Perusing the Oracle 18c documentation , following are some of the riveting new database features for your reference.

Private Temporary Tables

Private temporary tables are temporary database objects that are automatically dropped at the end of a transaction or a session. A private temporary table is stored in memory and is visible only to the session that created it.

A private temporary table confines the scope of a temporary table to a session or a transaction, thus providing bemore flexibility in application coding, leading to easier code maintenance and a better ready-to-use functionality.

Benefits of Private temporary tables

• When an application stores temporary data in transient tables that are populated once, read few times, and then dropped at the end of a transaction or session
• When a session is maintained indefinitely and must create different temporary tables for different transactions
• When the creation of a temporary table must not start a new transaction or commit an existing transaction
• When different sessions of the same user must use the same name for a temporary table
• When a temporary table is required for a read-only database

Examples: Creating a Private Temporary Table

 
 Transaction specific
 CREATE PRIVATE TEMPORARY TABLE ORA$PTT_sales_ptt_transaction 
 (time_id DATE,
 amount_sold NUMBER(10,2))
 ON COMMIT DROP DEFINITION;

 Session specific
 CREATE PRIVATE TEMPORARY TABLE ORA$PTT_sales_ptt_session
 (time_id DATE,
 amount_sold NUMBER(10,2))
 ON COMMIT PRESERVE DEFINITION;

Automatic Correction of Non-logged Blocks at a Data Guard Standby Database

Database nologging functionality has been extended to provide better support for use in an Oracle Active Data Guard environment without significantly increasing the amount of redo generated. There are two new nologging modes:

Standby Nologging for Load Performance

With this mode loading process can stop sending the data to the standbys if the network cannot keep up with the speed at which data is being loaded to the primary. In this mode it is possible that the standbys may have missing data, but each standby automatically fetches the data from the primary as a normal part of running managed recovery in an Active Data Guard environment

Standby Nologging for Data Availability

This mode causes the load operation to send the loaded data to each standby through its own respective connection. The commit is delayed until all the standbys have applied the data as part of running managed recovery in an Active Data Guard environment

You can use database nologging when loading data into your production databases without compromising the integrity of your Oracle Data Guard standby databases, choosing the level of synchronization between the primary and standby databases.

Commands to Enable an Appropriate Logging Mode

 
 FORCE LOGGING
 ALTER DATABASE FORCE LOGGING;

 STANDBY NOLOGGING FOR DATA AVAILABILITY
 ALTER DATABASE SET STANDBY NOLOGGING FOR DATA AVAILABILITY; 

 STANDBY NOLOGGING FOR LOAD PERFORMANCE
 ALTER DATABASE SET STANDBY NOLOGGING FOR LOAD PERFORMANCE;

Recovery Steps for Physical Standby Databases with conventional nonlogged operations

 
 SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; 
 RMAN> RECOVER DATABASE NONLOGGED BLOCK;

If the presence of unrecoverable blocks is only found after a switchover, then you can use these same two steps, but the primary database must be just mounted ( not open) and, RMAN must be connected to the primary.

Shadow Lost Write Protection (ORA-03020)

A data block lost write occurs when an I/O subsystem acknowledges the completion of the block write even though the write did not occur or when a former image of the block overwrites the current image. Shadow lost write protection can protect against lost writes for tablespaces or for individual data files.

Shadow lost write protection provides fast detection and immediate response to a lost write. Using shadow lost write protection can minimize data loss and the time required to repair a database.

To use shadow lost write protection, you must enable it for the database and create one or more shadow tablespaces. A shadow tablespace is a special-purpose bigfile tablespace that contains only system change numbers (SCNs) for tracked data files. You create a shadow tablespace by including the LOST WRITE PROTECTION clause in the CREATE TABLESPACE statement.

 
 CREATE BIGFILE TABLESPACE SHADOWTS DATAFILE 'DBNAME_SHADOWTS_01.dbf'  
 SIZE 10M LOST WRITE PROTECTION;

You can enable shadow lost write protection for specific tablespaces and data files. Therefore, you can choose to enable it only for your most important data. You do not need to use it to track all of your data.

Enable Shadow Lost Write Protection for a Database

 
 ALTER DATABASE ENABLE LOST WRITE PROTECTION;
 ALTER PLUGGABLE DATABASE ENABLE LOST WRITE PROTECTION; 

Once database is enabled with LOST WRITE PROTECTION with at least one shadow tablespace, You can enable shadow lost write protection for specific tablespaces and data files.
Therefore, you can choose to enable it only for your most important data. You do not need to use it to track all of your data.

Enable Shadow Lost Write Protection for Tablespaces and Data Files

 
 ALTER TABLESPACE TBS1 ENABLE LOST WRITE PROTECTION;
 ALTER DATABASE DATAFILE 'DBNAME_TBS2_01.dbf' ENABLE LOST WRITE PROTECTION;
 ALTER PLUGGABLE DATABASE DATAFILE 'PDBNAME_TBS1_01.dbf' ENABLE LOST WRITE PROTECTION; 

Disable Shadow Lost Write Protection for a Database

 
 ALTER DATABASE DISABLE LOST WRITE PROTECTION;
 ALTER PLUGGABLE DATABASE DISABLE LOST WRITE PROTECTION; 

Removing or Suspending Shadow Lost Write Protection

 
 ALTER TABLESPACE TBS1 REMOVE LOST WRITE PROTECTION;
 ALTER DATABASE DATAFILE 'DBNAME_TBS2_01.dbf' SUSPEND LOST WRITE PROTECTION;
 ALTER PLUGGABLE DATABASE DATAFILE 'PDBNAME_TBS1_01.dbf' SUSPEND LOST WRITE PROTECTION; 

Finally DROP shadow tablespace

If you use the DROP TABLESPACE statement with the INCLUDING CONTENTS clause, then the shadow tablespace is dropped along with its contents. If you use the DROP TABLESPACE statement without the INCLUDING CONTENTS clause, then before dropping the shadow tablespace, its contents are moved to another shadow tablespace, if it exists and has a sufficient free space.

Cancelling a SQL Statement in a Session

A SQL query consuming excessive resources, including parallel servers, can be terminated using the ALTER SYSTEM CANCEL SQL statement.
Instead of terminating a session, you can cancel a high-load SQL statement in a session. When you cancel a DML statement, the statement is rolled back.

Syntax

 
 ALTER SYSTEM CANCEL SQL 'SID, SERIAL, @INST_ID, SQL_ID'; 

Zero-Downtime Database Upgrade

Zero-Downtime Database Upgrade automates all of the steps required for a Database upgrade. It can minimize or even eliminate application downtime during the upgrade process. It can also minimize resource requirements. It also provides a fallback path to roll back upgrades if necessary.

Using Rapid Home Provisioning, which automates and orchestrates database upgrades, you can upgrade an Oracle RAC or Oracle RAC One Node database with no disruption in service.

The zero-downtime upgrade process is resumable, restartable, and recoverable should any errors interrupt the process. You can fix the issue then re-run the command, and Rapid Home Provisioning continues from the error point.

Multiple methods of running a Zero-Downtime Upgrade

• Running a Zero-Downtime Upgrade Using Oracle GoldenGate for Replication
• Running a Zero-Downtime Upgrade Using Oracle Data Guard for Replication.

Reference Documentation link

Oracle Documentation

New Default Location of Oracle Database Password File

While creating password file, If you specify a location on an Oracle ASM disk group, then the database password file is shared automatically among the nodes in the cluster. When you use an Oracle ASM disk group to store the password file, and you are not using Oracle Managed Files, you must specify the name of the password file, including its full path. The full path is not required if you are using Oracle Managed Files.

If you do not specify a location on an Oracle ASM disk group, then the file name required for the password file is operating system specific. Some operating systems require the password file to adhere to a specific format and be located in a specific directory. Other operating systems allow the use of environment variables to specify the name and location of the password file.

Location for the password file on the UNIX, Linux, and Windows platforms

 
 UNIX and Linux	ORACLE_BASE/dbs
 Windows	ORACLE_BASE\database 

Example

 
 Creating a Database Password File Located in an Oracle ASM Disk Group
 orapwd FILE='+DATA/orcl/orapworcl' DBUNIQUENAME='orcl' FORMAT=12.2
 
 Creating a Database Password File Located in a File System
 orapwd FILE='/u01/oracle/dbs/orapworcl' FORMAT=12.2
 
 Migrating a Legacy Database Password File to Oracle Database 12c Format
 orapwd FILE='/u01/oracle/dbs/orapworcl' FORMAT=12.2 INPUT_FILE='/u01/oracle/dbs/orapworcl' FORCE=y 
 
 Describing a Password File
 orapwd DESCRIBE FILE='orapworcl'

Read-Only Oracle Homes

Starting with Oracle Database 18c, you can configure an Oracle home in read-only mode.

A read-only Oracle home separates the software from the database configuration information and log files. This separation enables you to easily share the software across different deployments. A read-only Oracle home also simplifies version control and standardization.

In a read-only Oracle home, all the configuration data and log files reside outside of the read-only Oracle home. This feature allows you to use the read-only Oracle home as a software image that can be distributed across multiple servers.

Benefits of a Read-Only Oracle Home

• Enables seamless patching and updating of Oracle databases without extended downtime.
• Simplifies patching and mass rollout as only one image needs to be updated to distribute a patch to many servers.
• Simplifies provisioning by implementing separation of installation and configuration.

How to Enable Read Only Oracle Home

• Perform a software-only Oracle Database installation.
• Run the roohctl -enable script.
• Run Oracle Database Configuration Assistant (Oracle DBCA) to create a database.

Reference Documentation link

Oracle Documentation

Automatic In-Memory

Automatic In-Memory uses access tracking, column statistics, and other relevant statistics to manage objects in the IM column store.

If the IM column store is full, and if other more frequently accessed segments would benefit from population in the IM column store, then the IM column store evicts inactive segments. If the IM column store is configured to hold all INMEMORY segments, however, then Automatic In-Memory takes no action.

In Oracle Database 18c, Automatic In-Memory transparently automates the management of the IM column store by evicting cold (infrequently accessed) segments to ensure that the working data set is always populated.

Benefits

Improved performance
By mitigating memory pressure through eviction of cold segments, Automatic In-Memory improves the performance of workloads because the working data set resides in the IM column store.

Ease of management
Management of the IM column store for mitigating memory pressure by eviction of cold segments involves significant user intervention. Automatic In-Memory addresses these issues with minimal user intervention.

Enable and disable Automatic In-Memory using the initialization parameter INMEMORY_AUTOMATIC_LEVEL.

Parameter values

 
 OFF (default)
 This option disables Automatic In-Memory, returning the IM column store to its Oracle Database 12c Release 2 
 (12.2.0.1) behavior.
 LOW
 When under memory pressure, the database evicts cold segments from the IM column store.
 MEDIUM
 This level includes an additional optimization that ensures that any hot segment that was not populated because of 
 memory pressure is populated first. 

Scalable Sequences

A scalable sequence is particularly efficient when used to generate unordered primary or unique keys for data ingestion workloads having high level of concurrency. Single Oracle database instances as well as Oracle RAC databases benefit from this feature.

Scalable sequences significantly reduce the sequence and index block contention and provide better data load scalability compared to the solution of configuring a very large sequence cache using the CACHE clause of CREATE SEQUENCE or ALTER SEQUENCE statement.

Syntax

 
 CREATE | ALTER SEQUENCE sequence_name...SCALE [EXTEND | NOEXTEND] | NOSCALE; 

Shared Single Client Access Names

A shared single client access name (SCAN) enables you to share one set of SCAN virtual IPs (VIPs) and listeners on a dedicated cluster with other clusters.

The use of a shared SCAN enables multiple clusters to use a single common set of SCAN virtual IP (VIP) addresses to manage user connections, instead of deploying a set of SCAN VIPs per cluster.

For example, instead of 10 clusters deploying 3 SCAN VIPs per cluster using a total of 30 IP addresses, with shared SCAN deployments, you only deploy 3 SCAN VIPs for those same 10 clusters, requiring only 3 IP addresses.

How to configure?

Oracle Documentation

Practical Examples coming your way soon !!!