Oracle Database 18c comes with some cool new features for DBA’s. In 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 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 one doesn’t require moving any passwordfile.
4. READ-ONLY ORACLE HOMES:
The preceding feature makes it 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:
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 get to know the body of the PDB (datafile names) to finish the cloning activity.
- – 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 conversion could 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 feature.
- – For multi-cluster configuration, it’s now possible to set up one SCAN configuration called “Shared SCAN”, simplifying the 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 for a selected VIP address. Once you configure such a node, you’ll now simply not provide a VIP address. Fewer VIP addresses mean fewer IP assignments 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 its 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. The use of Oracle Internet Directory (via LDAP) remains supported for an equivalent purpose.
When you want to make a strong Oracle DBA career then you should be aware of database services and other database technology. Without having knowledge of Oracle internals, Oracle performance tuning, and skill of Oracle database troubleshooting you can’t be an Oracle DBA expert.
This expert DBA Team club blog always provides you latest technology news and database news to keep yourself up to date. You should need to be aware of Cloud database technology like DBaaS. All Oracle DBA tips are available in a single unique resource at our orageek. Meanwhile, we are also providing some sql tutorials for Oracle DBA. This is the part of Dbametrix Group and you would enjoy more advanced topics from our partner resource.