Five New Features in Oracle Database 12c for DBAs : Part1


Oracle Database 12c

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:

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

Database interview questions

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

Click here to know 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 

 



Series Navigation<< Oracle Database 12c : Multitenant Architecture : Container or Pluggable Database CDB/PDBNext Five New Features in Oracle Database 12c for DBAs : Part II >>
    

    

    
            


    

        

            About the Author            SeemaYadav
        


        
Oracle Consultant at K21
Technologies - Oracle Gold Partner specialising in Design, Implement, and Support.


            

    

        

    


    
    
        


    
    

                    
Leave a Comment:

            

                

                                            

                            
                            
                        

                        

                            
                            
                        

                        

                        
                        
                                        
                    

                    

                        
			
                    

                    


                    

                    
                    

                

            

        
                    

                
1 comments

            

                
                    
            

                                                                    

                    

                        

                                                    


                        

                                
                                                                            Prasad says                                                                    
                                
                                                                                                                     April 18, 2016                                                                                                          

                                                            
Thanks for Good Explanation about 12c concepts



                            
                                                            
                                    Reply                                
                                                        


                        

                        

                    

                    
                

                

                
                            
                            


            
                        

            
                Add Your Reply            
            

                

                    

                                                    

                                
                                
                            

                            

                                
                                
                            

                            

                            
                            
                        
                        
                        

                        

                            
                        

                        

                        
                        

                    

                

            

                

    

            

		    

                
            

        

        

    





				
			


Not found