Oracle Databse 18c comes with some cool new features for DBAs. during this article I’ll list my preferred ones.
Just one note: i feel the name “Autonomous Database” is sort of misleading, because it means a number of the new features might be wont to configure your database as “autonomous” where previous versions required some maintenance, and this may be the direction of subsequent Oracle Database generations.
1. PRIVATE TEMPORARY TABLES
You can create temporary tables with these characteristics:
– NAME must be prefixed by “ORA$PTT_” (or the present value of PRIVATE_TEMP_TABLE_PREFIX initialization parameter)
– DATA is automatically deleted at the top of the transaction (just like GLOBAL TEMPORARY TABLES created with ON COMMIT DELETE ROWS clause)
– DEFINITION is automatically dropped at the top of the transaction (ON COMMIT DELETE DEFINITION) or at the top of the session (ON COMMIT PRESERVE DEFINITION)
This feature is beneficial for developers also as DBAs, because it could reduce the amount of temporary or “test” tables created within the database.
CREATE PRIVATE TEMPORARY TABLE ora$ptt_test1ptt (test1col NUMBER)
ON COMMIT DROP DEFINITION;
2. SQL CANCELLING
A new command is introduced for DBAs to cancel blocking or too expensive statements rather than kill the originating session.
Different syntaxes are available for RAC architectures (using INST#) or once you know the precise 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’;
3. PASSWORDFILE LOCATION
In Oracle Database 18c the passwordfile default location is that 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 each Oracle Home, so migrating a database from one Oracle Home to a different doesn’t require moving any passwordfile.
4. READ-ONLY ORACLE HOMES
The preceding feature makes possible to mount one shared read-only Oracle Home on different systems, making deployment for complex architectures more reliable and simpler.
5. SIMPLER INSTALLATIONS
Oracle Database SW installation via ZIP files is now possible, saving space previously needed for stage areas. This method was already adopted for Oracle Grid Infrastructure SW, and simplifies deployments on complex environments, also making them faster.
6. BASIC INSTALLATIONS
For simple, standard Oracle Database installations (single-instance, no Grid Infrastructure, …) and Linux environment Oracle now deploy an RPM SW package. This completely automates basic installations.
7. NEW ONLINE COMMANDS
New ONLINE commands are now available for partitioned tables:
ALTER TABLE <table_name> MERGE PARTITION… ONLINE;
ALTER TABLE <table_name> MERGE SUBPARTITION… ONLINE;
8. 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.
9. ORACLE GRID INFRASTRUCTURE AND ORACHK
Oracle Grid Infrastructure now comes with pre-configured default ORAchk daemon, ant ORAchk is automatically executed at pre-defined interval after Oracle GI installation.
10. ORACLE ASM AND RAC NEW FEATURES
Oracle RAC configurations and Automatic Storage Management (ASM) are enhanced with many interesting new features, for less complicated and more efficient deployments:
– It’s possible to clone a PDB in multi-tenant architecture by a near instantaneous replica through splitting off mirrored ASM Flex Disk Groups. DBAs don’t got to know the body of the PDB (datafile names) so as to finish the cloning activity
– so as to encourage the utilization of ASM Flex Disk Groups, it’s now possible to convert a standard DG during a Flex one completely online (previously this convertion coud be wiped out Restricted Mount mode only)
– ACFS disks can now be exported to other nodes within the same cluster (like NFS) using the new ACFS Remote Service feauturd
– For multi-cluster configuration it’s now possible to setup one SCAN configuration called “Shared SCAN”, simplifying management of multiple clusters within the same data center
– For non-critical nodes within the cluster (g.e. nodes that don’t host databases) there’s no need of a selected VIP address. once you configure such a node you’ll now simply not provides a VIP address. Less VIP addresses means less IP assignements in your network
– Oracle Database 18c introduces Cluster Domain Proxies, through which you’ll define member-cross resource dependencies in order that for instance an Application Member Cluster could start as long as its associated database is out there in it’s own Database Member Cluster.
11. MS ACTIVE DIRECTORY INTEGRATION
Last but not least, now Oracle Database 18c comes with native integration with MS Active Directory for database authentication and authorization. Use of Oracle Internet Directory (via LDAP) remains supported for an equivalent purpose.