Satya's DBA Blog: New features in Oracle Database 18c

June 2, 2018

New features in Oracle Database 18c

Oracle Database 18c (18.1.0, 18.2.0, 18.3.0) new features

Oracle 18c - Innovation Release includes 2 years of Premier Support, but there is no Extended Support.

General

Starting with Oracle 18c, we can configure ORACLE_HOME in read-only mode.
From Oracle 18c, we can CANCEL any database session SQL query (instead of terminating the entire session).
ALTER SYSTEM CANCEL SQL 'SID, Serial#';
ALTER SYSTEM CANCEL SQL 'SID, Serial#, @INST_ID, SQL_ID';
In DBCA silent mode, Hardware and software prerequisites for creating a database can be verified by executing -executePrereqs command on the system where the database needs to be created.
Inline external tables enable the runtime definition of an external table as part of a SQL statement, without creating the external table as the persistent object in the data dictionary.
Shadow lost write protection provides fast detection and immediate response to a lost write and can minimize data loss and the time required to repair a database. Oracle DBAs can enable shadow lost write protection for a database, a tablespace or a datafile without requiring an Oracle Data Guard standby database.
CREATE BIGFILE TABLESPACE ... LOST WRITE PROTECTION;
Oracle Database can connect directly to Microsoft Active Directory and authenticate and authorize users.
In 18c SQL*Plus utility, we can see the SQL_ID for each SQL statement using, SET FEEDBACK ON SQL_ID option.
RESTART clause can be used in an ALTER SEQUENCE statement to reset a sequence number to a specific value.
In stand-alone databases and Oracle RAC databases, the sequence can be made scalable by specifying the SCALE clause in the CREATE SEQUENCE or ALTER SEQUENCE statement.
Private temporary tables are stored in memory and each one is visible only to the session that created it.
CREATE PRIVATE TEMPORARY TABLE ORA$PTT_sales ....
The default location for Oracle Database password file is ORACLE_BASE directory (instead of ORACLE_HOME). This change supports read-only Oracle homes.
New utility oraversion, to find out Oracle Database releases/version information.
Memoptimized Rowstore (MEMOPTIMIZE_POOL_SIZE) enables fast lookup of data for the tables that are mainly queried based on primary key columns, useful for the applications such as Internet of Things (IoT).
In DBCA silent mode, a copy of a database template can be created using the -createTemplateFromTemplate command.

Pluggable Databases

Cloning a multitenant pluggable database (PDB) with Oracle Automatic Storage Management (Oracle ASM).
A copy of a PDB can be created using -createPluggableDatabase command option -createPDBFrom with the value of PDB.
In DBCA silent mode, a copy of a multitenant container database (CDB) can be created using the -createDuplicateDB command.

Oracle Data Guard

The database buffer cache state is maintained (in 18c) on an Oracle Active Data Guard standby during a role change.
Enhancements have been made to Data Guard broker support for upgrades performed using the DBMS_ROLLING PL/SQL package.
A new initialization parameter, ADG_ACCOUNT_INFO_TRACKING, extends control of user account security against login attacks across a production database and all Oracle Active Data Guard standby databases.
A new view V$DATAGUARD_PROCESS (which replaces V$MANAGED_STANDBY) provides information that can be queried to verify that redo is being transmitted from the primary database and applied to the standby database.
From 18c Oracle, Global temporary tables can be dynamically created on an Oracle ADG (Active Data Guard standby database).
Metadata for private/local temporary tables can be stored in memory, to be enabled on read-only databases, hence allowing reporting applications to run on Oracle Active Data Guard standby databases.
Database nologging has been extended with two new modes: Standby Nologging for Load Performance and Standby Nologging for Data Availability. These modes provide better support for use in an Oracle Active Data Guard environment without significantly increasing the amount of redo generated.
In DBCA silent mode, a standby database can be created from an existing database using -createDuplicateDB command option -createAsStandby.

Oracle RMAN

Oracle Database 18c onwards, a standby database can be refreshed over the network using one RMAN command, RECOVER STANDBY DATABASE.
In 18c, Block Change Tracking is supported with multi-instance redo apply.
From Oracle Database 18c, using RMAN backup, we can recover a table also, that is, point in time recovery for a single table.
RMAN> recover table satya.deportment until time "to_date('2019-09-03 18:33:00','yyyy-mm-dd:hh24:mi:ss')" auxiliary destination '/u02/fra';