In this article, we will see Oracle 18c New Features for DBA’s .
Oracle 18c New Features for DBA’s
Manual Termination of run-away queries
A new command is introduced for DBAs to cancel blocking or too expensive statements instead of kill the originating session.
Different syntaxes are available for RAC architectures (using INST#) or when you know the specific SQL_ID:
ALTER SYSTEM CANCEL SQL ‘sid,serial#’;
ALTER SYSTEM CANCEL SQL ‘sid,serial#,sqlid’;
ALTER SYSTEM CANCEL SQL ‘sid,serial#,@inst#’;
ALTER SYSTEM CANCEL SQL ‘sid,serial#,@inst#,sqlid’;
Private Temporary Tables
Data is automatically deleted at the end of transaction (just like GLOBAL TEMPORARY TABLES created with ON COMMIT DELETE ROWS clause)
– NAME must be prefixed by “ORA$PTT_”.
This feature is useful for developers as well as DBAs, because it could reduce the number of temporary or “test” tables created in the database.
In Oracle Database 18c the passwordfile default location is the Oracle Base, while in previous versions was $ORACLE_HOME/dbs (for Unix/Linux) or %ORACLE_HOME%\database (for Windows). The new location simplifies database migrations because Oracle Base is common to every Oracle Home, so migrating a database from one Oracle Home to another does not require moving any passwordfile
PARTITION STRATEGY MODIFICATION
Now it’s possible to completely modify partition strategy (for example form RANGE to composite RANGE-HASH) or partition a non-partitioned table using the ALTER TABLE… MODIFY PARTITION command, also in ONLINE mode.
New ONLINE commands are now available for partitioned tables to merge partitions
ALTER TABLE… MERGE PARTITION… ONLINE;
ALTER TABLE… MERGE SUBPARTITION… ONLINE;
DML on Active DataGuard
DML can be executed on Active DataGuard, however it will be automatically Re-directed to the Primary without compromising ACID
Enabled by setting parameter _enable_proxy_adg_redirect=TRUE and appropriate connectivity parameters
Respects ACID properties of a database for ADG session
Keep Buffer Cache at Role Change
In case of Switchover , the database buffer cache state will be maintained on an ADG standby during a role change. Automatic , nothing to setup
Standby Database can be rolled forward Command RECOVER STANDBY
RMAN> RMAN RECOVER STANDBY DATABASE FROM SERVICE primary_db;
New commands for Validate through DataGuard Broker
This release provides several new commands:
VALIDATE DATABASE SPFILE performs a comparison of server parameter file (SPFILE) entries between the primary database and a specified standby database.
VALIDATE NETWORK CONFIGURATION performs network connectivity checks between members of a broker configuration.
VALIDATE STATIC CONNECT IDENTIFIER validates the static connect identifier of a database.
SET ECHO controls whether or not to echo commands that are issued either at the command-line prompt or from a DGMGRL script.
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
RMAN can recover table using PITR
Using RMAN backup, we can recover a table also, that is, point in time recovery for 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’;
ASM and RAC Features:
Shared SCAN for an Oracle Member Cluster
A shared single client access name (SCAN) enables the sharing of one set of SCAN virtual IPs (VIPs) and Listeners on one dedicated cluster in the data center with other clusters to avoid the deployment of one SCAN setup per cluster, which not only reduces the number of SCAN-related DNS entries, but also the number of VIPs that need to be deployed for a cluster configuration.
A shared SCAN simplifies the deployment and management of groups of clusters in the data center by providing a shared SCAN setup that can be used by multiple systems at the same time
ASM Database Cloning
ASM database cloning provides cloning of Multitenant databases (PDBs). This feature works by leveraging ASM redundancy. Flex Disk Groups now can provide up to five redundant copies, in which one or more of the copies can be split off to provide a near instantaneous replica.
The advantage of ASM database cloning, when compared with storage array-based replication, is that ASM database clones replicate complete databases (PDBs) rather than files or blocks of physical storage.
Simplified Image-Based Oracle Database Installation
Popular 12.2 Feature:
Database In-Memory (Columnar Format)
Originally introduced in 18.104.22.168 however enhanced in 18c.
Originally, Data is stored in row format. Data is stored in Columnar format but only in Cache not in DISK.
So obviously, this is good for an OLAP env, as it allows faster data retrieval when a large portion of data is selected but only for a few columns.
So a row format is ideal for processing DMLs (Insert, Update, Delete) and a column format for a SELECT (Large portion but for a few columns), so up until now if you choose only one of the option and thus have to experience the cons of the format.
Tables are stored in columnar format.
Follow Me On
Linkedin :Ankush Chawla