New Features 12c Database

Oracle Database 12c new features

Trying to put some Very new Features of 12c Database, which cover Administration, HA, Performance Tuning and Few RMAN things.

1. Online rename and relocation of an active data file

In 12c R1, a data file can be renamed or moved online simply using the ALTER DATABASE MOVE DATAFILE SQL statement. 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 table space in READ ONLY mode, followed by data file offline action. While the data file is being transferred, the end user can perform queries, DML and DDL tasks. On Top of it, data files can be migrated between storages e.g. from non-ASM to ASM and vice versa.

Data File Rename Command as under:

SQL> ALTER DATABASE MOVE DATAFILE '/u01/testdata/users01.dbf' TO '/u01/testdata/users_01.dbf';

Non-ASM to ASM Migrate

SQL> ALTER DATABASE MOVE DATAFILE '/u01/testdata/users_01.dbf' TO '+DG_DATA';

ASM disk group Migration 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 '/u01/testdata/users_01.dbf' TO '/u01/testdata_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 '/u01/testdata/users_01.dbf' TO '/u01/testdata_new/users_01.dbf' KEEP;

you can refer the alert.log of the database where Oracle writes the details about action being taken place Or You can monitor the progress while a data file being moved by querying the v$session_longops dynamic view.

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:

o    The UPDATE INDEXES clause will avoid any local/global indexes going unusable on the table.

o    Table online migration restriction applies here too.

o    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. 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;

4. Temporary Undo

Each Oracle database contains a set of system related tablespaces, such as, SYSTEM, SYSAUX, UNDO & TEMP, and each are used for different purposes within the Oracle database. Pre Oracle 12c R1, undo records generated by the temporary tables used to be stored in undo tablespace, much similar to a general/persistent table undo records. However, with the temporary undo feature in 12c R1, the temporary undo records can now be stored in a temporary table instead of stored in undo tablespace. The prime benefits of temporary undo includes: reduction in undo tablespace and less redo data generation as the information won’t be logged in redo logs. You have the flexibility to enable the temporary undo option either at session level or database level.

Enabling temporary undo

To be able to use the new feature, the following needs to be set:

o    Compatibility parameter must be set to 12.0.0 or higher

o    Enable TEMP_UNDO_ENABLED initialization parameter

o    Since the temporary undo records now stored in a temp tablespace, you need to create the temporary tablespace with sufficient space

o    For session level, you can use: ALTER SESSION SET TEMP_UNDO_ENABLE=TRUE;

Query temporary undo information

The dictionary views listed below are used to view/query the information/statistics about the temporary undo data:

NOTE:- you can also see this on my other Post with full detail Example as this is very Help Full .

o    V$TEMPUNDOSTAT

o    DBA_HIST_UNDOSTAT

o    V$UNDOSTAT

To disable the feature, you simply need to set the following:

SQL> ALTER SYSTEM|SESSION SET TEMP_UNDO_ENABLED=FALSE;

5. 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.

6. Restricting PGA size

Pre Oracle 12c R1, there was no option to limit and control the PGA size. Although, you set a certain size toPGA_AGGREGATE_TARGET initialization parameter, Oracle could increase/reduce the size of the PGA dynamically based on the workload and requirements. In 12c, you can set a hard limit on PGA by enabling the automatic PGA management, which requires PGA_AGGREGATE_LIMIT parameter settings. Therefore, you can now set the hard limit on PGA by setting the new parameter to avoid excessive PGA usage.

SQL> ALTER SYSTEM SET PGA_AGGREGATE_LIMIT=4G;

SQL> ALTER SYSTEM SET PGA_AGGREGATE_LIMIT=0; --disables the hard limit

Important notes:

When the current PGA limits exceeds, Oracle will automatically terminates/abort the session/process that holds the most untenable PGA memory.

7. How to execute SQL statement in RMAN

In 12c, you can now execute any SQL and PL/SQL commands in RMAN without the need of a SQL prefix: you can execute any SQL and PLS/SQL commands directly from RMAN. How you can execute SQL statements in RMAN:

RMAN> SELECT username,machine FROM v$session;

        RMAN> ALTER TABLESPACE tools ADD DATAFILE SIZE 300m;

8. Backup specific user privilege

In 11g R2, SYSASM privilege was introduced to perform ASM specific operations. Similarly, backup and recovery tasks specific privilege SYSBACKUP has been introduced in 12c to execute backup and recovery commands in Recovery Manager (RMAN). Therefore, you can create a local user in the database and grant the SYSBACKUP Role privilege to perform any backup and recovery related tasks in RMAN without being granting the SYSDBA privilege.

$ ./rman target "username/password as SYSBACKUP"

9. Table or partition recovery in RMAN

Oracle database backups are mainly categorized into two types: logical and physical. Each backup type has its own pros and cons. In previous editions, it was not feasible to restore a table or partition using existing physical backups. In order to restore a particular object, you must have logical backup. With 12c R1, you can recover a particular table or partition to a point-in-time or SCN from RMAN backups in the event of a table drop or truncate.

When a table or partition recovery is initiated via RMAN, the following action is performed:

o    Required backup sets are identified to recover the table/partition

o    An auxiliary database will be configured to a point-in-time temporarily in the process of recovering the table/partition

o    Required table/partitions will be then exported to a dumpfile using the data pumps

o    Optionally, you can import the table/partitions in the source database

o    Rename option while recovery

An example of a table point-in-time recovery via RMAN (ensure you already have a full database backup from earlier):

        RMAN> connect target "username/password as SYSBACKUP";

        RMAN> RECOVER TABLE username.tablename UNTIL TIME 'TIMESTAMP…'

               AUXILIARY DESTINATION '/u01/tablerecovery'

               DATAPUMP DESTINATION '/u01/dpump'

               DUMP FILE 'tablename.dmp'

               NOTABLEIMPORT    -- this option avoids importing the table automatically.

REMAP TABLE 'username.tablename': 'username.new_table_name';    -- can rename table with this option.

Important notes:

o    Ensure sufficient free space available under /u01 filesystem for auxiliary database and also to keep the data pump file

o    A full database backup must be exists, or at least the SYSTEM related tablespaces

The following limitations/restrictions are applied on table/partition recovery in RMAN:

o    SYS user table/partition can’t be recovered

o    Tables/partitions stored under SYSAUX and SYSTEM tablespaces can’t be recovered

o    Recovery of a table is not possible when REMAP option used to recovery a table that contains NOT NULL constraints

10. 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
NOTE: This is Not exactly New.

SQL> ALTER SYSTEM|SESSION SET ENABLE_DDL_LOGGING=TRUE;

The following DDL statements are likely to be recorded in the xml/log file:

o    CREATE|ALTER|DROP|TRUNCATE TABLE

o    DROP USER

o    CREATE|ALTER|DROP PACKAGE|FUNCTION|VIEW|SYNONYM|SEQUENCE

11. Table partition maintenance enhancements

a) Adding multiple new partitions

Before Oracle 12c R1, it was only possible to add one new partition at a time to an existing partitioned table. To add more than one new partition, you had to execute an individual ALTER TABLE ADD PARTITION statement to every new partition. Oracle 12c provides the flexibility to add multiple new partitions using a single ALTER TABLE ADD PARTITION command. The following example explains how to add multiple new partitions to an existing partitioned table:

SQL> CREATE TABLE emp_part
    (eno number(8), ename varchar2(40), sal number (6))
  PARTITION BY RANGE (sal)
  (PARTITION p1 VALUES LESS THAN (10000),
   PARTITION p2 VALUES LESS THAN (20000),
   PARTITION p3 VALUES LESS THAN (30000)
  );

Lets add few couple of new partitions:

SQL> ALTER TABLE emp_part ADD PARTITION
  PARTITION p4 VALUES LESS THAN (35000),
  PARTITION p5 VALUES LESS THAN (40000);

You can add multiple new partitions to a list and system partitioned table, provided that the MAXVALUE partition doesn’t exist.

b) How to drop and truncate multiple partitions/sub-partitions

Part of data maintenance, you typically either use drop or truncate partition maintenance task on a partitioned table.  before 12c R1, it was only possible to drop or truncate one partition at a time on an existing partitioned table.  Now Oracle 12c, multiple partitions or sub-partitions can be dropped or merged using a single command
ALTER TABLE table_name {DROP|TRUNCATE} PARTITIONS

Following example explains how to drop or truncate multiple partitions on an existing partitioned table:

SQL> ALTER TABLE emp_part DROP PARTITIONS p4,p5;
SQL> ALTER TABLE emp_part TRUNCATE PARTITONS p4,p5;

To keep indexes up-to-date, use the UPDATE INDEXES or UPDATE GLOBAL INDEXES clause, shown below:

SQL> ALTER TABLE emp_part DROP PARTITIONS p4,p5 UPDATE GLOBAL INDEXES;
SQL> ALTER TABLE emp_part TRUNCATE PARTITIONS p4,p5 UPDATE GLOBAL INDEXES;

Truncate or drop a partition without the UPDATE GLOBAL INDEXES clause, you can query the column ORPHANED_ENTRIES in the USER_INDEXES or USER_IND_PARTITIONS dictionary views to find out whether the index contains any stale entries.

c) Splitting a single partition into multiple new partitions

The new enhanced SPLIT PARTITION clause in 12c will let you split a particular partition or sub-partition into multiple new partitions using a single command. The following example explains how to split a partition into multiple new partitions:

SQL> CREATE TABLE emp_part
    (eno number(8), ename varchar2(40), sal number (6))
  PARTITION BY RANGE (sal)
  (PARTITION p1 VALUES LESS THAN (10000),
   PARTITION p2 VALUES LESS THAN (20000),
   PARTITION p_max VALUES LESS THAN (MAXVALUE)
  );

SQL> ALTER TABLE emp_part SPLIT PARTITION p_max INTO
  (PARTITION p3 VALUES LESS THAN (25000),
   PARTITION p4 VALUES LESS THAN (30000), PARTITION p_max);

d) Merge multiple partitions into one partition

Merge multiple partitions to a single partition using a single ALTER TBALE MERGE PARTITIONS statement:

SQL> CREATE TABLE emp_part
  (eno number(8), ename varchar2(40), sal number (6))
  PARTITION BY RANGE (sal)
  (PARTITION p1 VALUES LESS THAN (10000),
   PARTITION p2 VALUES LESS THAN (20000),
   PARTITION p3 VALUES LESS THAN (30000),
   PARTITION p4 VALUES LESS THAN (40000),
   PARTITION p5 VALUES LESS THAN (50000),
   PARTITION p_max (MAXVALUE)
  );

SQL> ALTER TABLE emp_part MERGE PARTITIONS p3,p4,p5 INTO PARTITION p_merge;

If the range falls in the sequence, you can use the following example:

SQL> ALTER TABLE emp_part MERGE PARTITIONS p3 TO p5 INTO PARTITION p_merge;

12. Gathering  statistics concurrently on multiple tables

In previous Oracle database editions, whenever you execute a DBMS_STATS procedure to gather table, index, schema or database level statistics, Oracle used to collect stats one table at a time. If the table is big enough, then increasing the parallelism was recommended. With 12c R1, you can now collect stats on multiple tables, partitions and sub partitions concurrently.  Before you start using it, you must set the following at the database level to enable the feature:

SQL> ALTER SYSTEM SET RESOURCE_MANAGER_PLAN='DEFAULT_MAIN';
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=4;

SQL> EXEC DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT', 'ALL');

SQL> EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT');

13. Restore/Recover data files over the network

This is particularly useful to synchronize the primary and standby databases.You can now restore or recover a data file, control file, spfile, tablespace or entire database between primary and standby databases using a SERVICE name.
When there is a pretty long gap found between the primary and standby database, you no longer require the complex roll-forward procedure to fill the gap between the primary and standby. RMAN is able to perform standby recovery getting the incremental backups through the network and applying them to the physical standby database. Having said that, you can directly copy the required data files from the standby location to the primary side using the SERVICE name e.g. in the case of a data file, tablespace lost on the primary database, or without actually restoring the data files from a backup set.

Perform a roll forward using the new features to synchronize the standby database with its primary database:

On the physical standby database:

./rman target "username/password@standby_db_tns as SYSBACKUP"
RMAN> RECOVER DATABASE FROM SERVICE primary_db_tns USING COMPRESSED BACKUPSET;

This above example uses the primary_db_tns connect string defined on the standby database, connects to the primary database, performs an incremental backup, transfers these incremental backups over standby destination, and then applies these files to the standby database to synchronize the standby. However, you need to Make sure you have configured primary_db_tns to point to the primary database on the standby database side.

In this Example, A scenario to restore a lost data file on the primary database by fetching the data file from the standby database in ASM File System

On the primary database:

./rman target "username/password@primary_db_tns as SYSBACKUP"

RMAN> RESTORE DATAFILE ‘+DG_DISKGROUP/DBANME/DATAFILE/filename’ FROM SERVICE standby_db_tns;

14. Data Pump enhancements

Turning off logging while import and quite a few useful additions, such as converting view into a table while exporting

a) Turn off redo log generation

The new TRANSFORM option introduced in data pumps import provides the flexibility to turn off the redo generation for the objects during the course of import. When DISABLE_ARCHIVE_LOGGING values is specified with the TRANSFORM option, redo generation for the objects in the context will be turned off during the entire import duration. This feature provides a great relief when importing large tables, and reduces the excessive redo generation, which results in quicker imports. This attribute applies to tables and indexes.

Here is the Example

$ ./impdp directory=dpump dumpfile=abcd.dmp logfile=abcd.log TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y

b) Transport view as table

This is another improvement in the data pumps. With the new VIEWS_AS_TABLES option, you can unload the view data into a table. The following example describes how to unload views data into a table during export:

$ ./expdp directory=dpump dumpfile=abcd.dmp logfile=abcd.log views_as_tables=my_view:my_table

15. Database upgrade improvements

Every DBA confronts is the upgrade process. In this section, Two new improvements introduced for upgrading to 12c.

a) Pre-upgrade script

A new and much improved pre-upgrade information script, preupgrd.sql, replaces the legacy utlu[121]s.sql script in 12c R1. Apart from the preupgrade checks verification, the script is capable of addressing the various issues – in the form of fixup scripts – that are raised during the pre-post upgrade process.

The fixup scripts that are generated can be executed to resolve the problems at different levels, for example, pre-upgrade and post upgrade. When upgrading the database manually, the script must be executed manually before initiating the actual upgrade procedure. However, when the Database Upgrade Assistant (DBUA) tool is used to perform a database upgrade, it automatically executes the pre-upgrade scripts as part of the upgrade procedure and will prompt you to execute the fixup scripts in case of any errors that are reported.

Example how to Execute as under

SQL> @$ORACLE_12GHOME/rdbms/admin/preupgrd.sql

The above script generates a log file and a [pre/post]upgrade_fixup.sql script.  All these files are located under the $ORACLE_BASE/cfgtoollogs directory. Before you continue with the real upgrade procedure, you should run through the recommendations mentioned in the log file and execute the scripts to fix any issues.

Note: Ensure you copy the preupgrd.sql and utluppkg.sql scripts from the 12c Oracle home/rdbms/admin directory to the current (Assume 11gR2) Oracle database/rdbms/admin location or you can put it under temp location

b) Parallel-upgrade utility

The database upgrade duration is directly proportional to the number of components that are configured on the database, rather than the database size. In previous releases, there was no direct option or workaround available to run the upgrade process in parallel to quickly complete the overall upgrade procedure.

The catctl.pl (parallel-upgrade utility) that replaces the legacy catupgrd.sql script in 12c R1 comes with an option to run the upgrade procedure in parallel mode to improve the overall duration required to complete the procedure.

The following procedure explains how to initiate the parallel (with 3 processes) upgrade utility; you need to run this after you STARTUP the database in UPGRADE mode:

cd $ORACLE_12_HOME/perl/bin
$ ./perl catctl.pl –n 3 -catupgrd.sql

These two Above steps can be done only when you are doing the Manual Upgrade otherwise DBUA inherits the both new changes.