Oracle Database 18c: My preferred new features for DBAs
Oracle Databse 18c comes with some cool new features for DBAs. In this article I’ll list my preferred ones.
Just one note: I think the name “Autonomous Database” is quite misleading, as it means that some of the new features COULD be used to configure your database as “autonomous” where previous versions required some maintenance, and this will be the direction of the next Oracle Database generations.
1. PRIVATE TEMPORARY TABLES
You can create temporary tables with these characteristics:
– NAME must be prefixed by “ORA$PTT_” (or the current value of PRIVATE_TEMP_TABLE_PREFIX initialization parameter)
– DATA is automatically deleted at the end of the transaction (just like GLOBAL TEMPORARY TABLES created with ON COMMIT DELETE ROWS clause)
– DEFINITION is automatically dropped at the end of the transaction (ON COMMIT DELETE DEFINITION) or at the end of the session (ON COMMIT PRESERVE DEFINITION)
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.
CREATE PRIVATE TEMPORARY TABLE ora$ptt_testptt (testcol NUMBER)
ON COMMIT DROP DEFINITION;
2. SQL CANCELLING
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’;
3. PASSWORDFILE LOCATION
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.
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… MERGE PARTITION… ONLINE;
ALTER TABLE… 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) have been enhanced with many interesting new features, for simpler 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 need to know the physical structure of the PDB (datafile names) in order to complete the cloning activity
– In order to encourage the use of ASM Flex Disk Groups, it’s now possible to convert a traditional DG in a Flex one completely online (previously this convertion coud be done in Restricted Mount mode only)
– ACFS disks can now be exported to other nodes in the same cluster (like NFS) using the new ACFS Remote Service feauturd
– For multi-cluster configuration it’s now possible to setup a single SCAN configuration called “Shared SCAN”, simplifying management of multiple clusters in the same data center
– For non-critical nodes in the cluster (g.e. nodes that do not host databases) there is no need of a specific VIP address. When you configure such a node you can now simply not give a VIP address. Less VIP addresses means less IP assignements in your network
– Oracle Database 18c introduces Cluster Domain Proxies, through which you can define member-cross resource dependencies so that for example an Application Member Cluster could start only if its associated database is available 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) is still supported for the same purpose.