Five New Features in Oracle Database 12c for DBAs : Part1
This post is series of Oracle Database 12c new features, If you are new to PDB & CDB and challenges encountered in database consolidation then look at post here
In this article, I shall be extensively exploring some of the very important new additions/enhancements introduced in Oracle Database 12c in the area of Database Administration, RMAN, High Availability and Performance Tuning.
New Features in Oracle DBA 12C:
1. Online rename and relocation of an active data file:
Unlike in the previous releases, a data file migration or renaming in Oracle database 12c R1 no longer requires a number of steps i.e. putting the tablespace in READ ONLY mode, followed by data file offline action.
In 12c R1, a data file can be renamed or moved online simply using the ALTER DATABASE MOVE DATAFILE SQL statement. While the data file is being transferred, the end user can perform queries, DML and DDL tasks. Additionally, data files can be migrated between storages e.g. from non-ASM to ASM and vice versa.
Rename a data file:
SQL> ALTER DATABASE MOVE DATAFILE '/u01/data/users01.dbf' TO '/u00/data/users_01.dbf';
Migrate a data file from non-ASM to ASM:
SQL> ALTER DATABASE MOVE DATAFILE '/u01/data/users_01.dbf' TO '+DG_DATA';
Migrate a data file from one ASM disk group to another:
SQL> ALTER DATABASE MOVE DATAFILE '+DG_DATA/DBNAME/DATAFILE/users_01.dbf ' TO '+DG_DATA_02';
Overwrite the data file with the same name, if it exists at the new location:
SQL> ALTER DATABASE MOVE DATAFILE '/u00/data/users_01.dbf' TO '/u00/data_new/users_01.dbf' REUSE;
Copy the file to a new location whilst retaining the old copy in the old location:
SQL> ALTER DATABASE MOVE DATAFILE '/u00/data/users_01.dbf' TO '/u00/data_new/users_01.dbf' KEEP;
You can monitor the progress while a data file being moved by querying the v$session_longops dynamic view. Additionally, you can also refer the alert.log of the database where Oracle writes the details about action being taken place.
2. Online migration of table partition or sub-partition
Migration of a table partition or sub-partition to a different tablespace no longer requires a complex procedure in Oracle 12c R1.
In a similar way to how a heap (non-partition) table online migration was achieved in the previous releases, a table partition or sub-partition can be moved to a different tablespace online or offline.
When an ONLINE clause is specified, all DML operations can be performed without any interruption on the partition|sub-partition which is involved in the procedure. In contrast, no DML operations are allowed if the partition|sub-partition is moved offline.
Here are some working examples:
SQL> ALTER TABLE table_name MOVE PARTITION|SUBPARTITION partition_name TO tablespace tablespace_name;
SQL> ALTER TABLE table_name MOVE PARTITION|SUBPARTITION partition_name TO tablespace tablespace_name UPDATE INDEXES ONLINE;
The first example is used to move a table partition|sub-partition to a new tablespace offline. The second example moves a table partition/sub-partitioning online maintaining any local/global indexes on the table. Additionally, no DML operation will get interrupted when ONLINE clause is mentioned.
Important notes:
- The UPDATE INDEXES clause will avoid any local/global indexes going unusable on the table.
- Table online migration restriction applies here too.
- There will be locking mechanism involved to complete the procedure, also it might leads to performance degradation and can generate huge redo, depending upon the size of the partition, sub-partition.
3. Invisible columns
In Oracle 11g R1, Oracle introduced a couple of good enhancements in the form of invisible indexes and virtual columns. Taking the legacy forward, invisible column concepts has been introduced in Oracle 12c R1. I still remember, in the previous releases, to hide important data –columns from being displayed in the generic queries– we used to create a view hiding the required information or apply some sort of security conditions.
In 12c R1, you can now have an invisible column in a table. When a column is defined as invisible, the column won’t appear in generic queries, unless the column is explicitly referred to in the SQL statement or condition, or DESCRIBED in the table definition. It is pretty easy to add or modify a column to be invisible and vice versa:
SQL> CREATE TABLE emp (eno number(6), ename name varchar2(40), sal number(9) INVISIBLE);
SQL> ALTER TABLE emp MODIFY (sal visible);
You must explicitly refer to the invisible column name with the INSERT statement to insert the database into invisible columns. A virtual column or partition column can be defined as invisible too. However, temporary tables, external tables and cluster tables won’t support invisible columns.
4. Multiple indexes on the same column
Pre Oracle 12c, you can’t create multiple indexes either on the same column or set of columns in any form. For example, if you have an index on column {a} or columns {a,b}, you can’t create another index on the same column or set of columns in the same order. In 12c, you can have multiple indexes on the same column or set of columns as long as the index type is different. However, only one type of index is usable/visible at a given time. In order to test the invisible indexes, you need to set the optimizer_use_use_invisible_indexes=true.
Here’s an the example:
SQL> CREATE INDEX emp_ind1 ON EMP(ENO,ENAME); SQL> CREATE BITMAP INDEX emp_ind2 ON EMP(ENO,ENAME) INVISIBLE;
5. DDL logging
There was no direction option available to log the DDL action in the previous releases.
In 12cR1, you can now log the DDL action into xml and log files. This will be very useful to know when the drop or create command was executed and by who. The ENABLE_DDL_LOGGING initiation parameter must be configured in order to turn on this feature.
The parameter can be set at the database or session levels. When this parameter is enabled, all DDL commands are logged in an xml and a log file under the $ORACLE_BASE/diag/rdbms/DBNAME/log|ddl location. An xml file contains information, such as DDL command, IP address, timestamp etc. This helps to identify when a user or table dropped or when a DDL statement is triggered.
To enable DDL logging
SQL> ALTER SYSTEM|SESSION SET ENABLE_DDL_LOGGING=TRUE;
The following DDL statements are likely to be recorded in the xml/log file:
- CREATE|ALTER|DROP|TRUNCATE TABLE
- DROP USER
- CREATE|ALTER|DROP PACKAGE|FUNCTION|VIEW|SYNONYM|SEQUENCE
In part 2, you will learn more on new changes on Database Administration, RMAN, High Availability and Performance Tuning areas.
References:
- https://docs.oracle.com/database/121/NEWFT/chapter12102.htm#NEWFT003
- http://education.oracle.com/pls/web_prod-plq-dad/view_pdf?c_org_id=37&c_lang=N&c_id=D79094GC10
https://www.youtube.com/watch?v=2bnYpzMU_u8
https://www.youtube.com/watch?v=KSvpYW901Dc
Did you get a chance to download Free Interview Questions related to Database? If not, download it here http://k21academy.com/oracle-dba-12c-interview-question
Learn Oracle Database 12c Administration Training
If you want to learn Oracle Database 12c Administration with tons of additional features like Live Interactive Sessions, Life time access to membership portal, Free re-taking sessions for next one year, Dedicated Machine to practice, On Job Support and much more
This post is from our Oracle Database 12c Training where We cover Architecture, Installation, File System, Backup and Recovery and difference in 12c from previous version with Hands-On, If interested in learning Oracle Database 12c you can Register here for Oracle Database 12c Training
About the Author Brijesh
I'm 10gR2 RAC Oracle certified professional, working from last 6 year in the field of database.