Some of the important 12c New Features for DBA's

http://db12c.blogspot.co.uk/2013/06/using-new-oracle-database-12c-feature.html

 

Some of the important 12c New Features for DBA's

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 '/u00/data/users01.dbf' TO '/u00/data/users_01.dbf';

Migrate a data file from non-ASM to ASM:

SQL> ALTER DATABASE MOVE DATAFILE '/u00/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 R1, a column can’t be in more than one index in any form. Perhaps one might wonder why usually a column needs to be in multiple indexes. There are many reasons where you need a column or set of columns in more than one index. In 12c R1, a column can be included in a B-tree index as well as Bitmap index as long the type of index is a different form. However, only one type of index is usable at a given time.

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

6. 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:

• Compatibility parameter must be set to 12.0.0 or higher
• Enable TEMP_UNDO_ENABLED initialization parameter
• Since the temporary undo records now stored in a temp tablespace, you need to create the temporary tablespace with sufficient space
• For session level, you can use: ALTER SYSTEM 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:

• V$TEMPUNDOSTAT
• DBA_HIST_UNDOSTAT
• V$UNDOSTAT

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

SQL> ALTER SYSTEM|SESSION SET TEMP_UNDO_ENABLED=FALSE;

7. 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 privilege to perform any backup and recovery related tasks in RMAN without being granting the SYSDBA privilege.

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

8. 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 users ADD DATAFILE SIZE 121m;

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:

• Required backup sets are identified to recover the table/partition
• An auxiliary database will be configured to a point-in-time temporarily in the process of recovering the table/partition
• Required table/partitions will be then exported to a dumpfile using the data pumps
• Optionally, you can import the table/partitions in the source database
• 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:

• Ensure sufficient free space available under /u01 filesystem for auxiliary database and also to keep the data pump file
• 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:

• SYS user table/partition can’t be recovered
• Tables/partitions stored under SYSAUX and SYSTEM tablespaces can’t be recovered
• Recovery of a table is not possible when REMAP option used to recovery a table that contains NOT NULL constraints

10. Restricting PGA size

Pre Oracle 12c R1, there was no option to limit and control the PGA size. Although, you set a certain size to PGA_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=2G;

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.

11. 32k VARCHAR2 Support

32k VARCHAR2 Support - Yes, 32k varchar2 in the database. Stored like a CLOB

12. DISABLE_ARCHIVE_LOGGING

We will no longer require to take the database out of archivelog mode during those large imports with this new feature in DataPump call DISABLE_ARCHIVE_LOGGING.

The new TRANSFORM option, DISABLE_ARCHIVE_LOGGING, to the impdp command line causes Oracle Data Pump to disable redo logging when loading data into tables and when creating indexes. It also adds the same option as part of the PL/SQL DBMS_DATAPUMP package. With redo logging disabled, the disk space required for redo logs during an Oracle Data Pump import will be smaller. However, to ensure recovery from media failure, the DBA should do an RMAN backup after the import completes.
Even with this parameter specified, there is still redo logging for other operations of Oracle Data Pump. This includes all CREATE and ALTER statements, except CREATE INDEX, and all operations against the master table used by Oracle Data Pump during the import.
This feature reduces the required maintenance of redo logs by DBAs

Cardinality Feedback (Available from 11g):
a feature introduced in Version 11g -- monitors the execution of SQL statements and reoptimizes if the actual cardinality, such as the number of rows returned from the query, varies greatly from the cardinality estimates. A new feature in 12c called Adaptive Plan takes the next step in SQL auto-tuning. Instead of choosing the final execution plan at parse time, Optimizer defers the final choice among multiple sub-plans until execution time.
In Version 12c, if the quality of available statistics is not good enough, then the Optimizer can dynamically sample the tables to recollect statistics. This dynamic statistics collection uses the same methods as dynamic sampling available in earlier releases, except that, in Database 12c, these statistics are also stored for future use.

Traditionally, queries with union or union all branches execute one after another, meaning that one branch of the union or union all is executed, followed by the next branch, and so on. Version 12c introduces concurrent execution of union branches, meaning that one set of parallel servers will be executing one branch, a second set of parallel servers will be executing a different union all branch, and so on, all at the same time.
This concurrent execution feature will be very useful if the majority of the query execution time is spent outside of the database, such as when waiting for a SQL*Net message from a remote database link or for an SOA call response. The effective use of this new feature could reduce wait time dramatically, improving SQL elapsed time. (Incidentally, with Version 12c, SQL*Net packets can be compressed for database traffic, helping to reduce latency in a WAN environment.)


Global Data Source uses Global Data Listener
After a service failover to another instance, applications usually do not know the status of in-flight transactions. While the changes made by a committed transaction are permanent, as dictated by the ACID properties of Oracle Database, commit status messages to the application are transient. The result is that an instance failure creates a classic dilemma: If the application reissues an already committed transaction it can lead to logical corruption, but if the application does not reissue a failed transaction then changes can be permanently lost.
Database 12c resolves this dilemma with Transaction Guard, a new feature that maintains transaction status permanently. Transaction Guard assigns a unique global transaction ID to each transaction, and maintains the status of that global transaction for a predefined period of time. After a failover, the application can requery the status of a transaction and take corrective action deterministically.
Oracle did not stop at merely providing a mechanism to identify transaction status. Version 12c also introduces Application Continuity. With this feature, a new client-side replay driver remembers submitted SQL statements, and after the failure detection, statements are replayed to insert failed transactions into the database. Note that code changes may be required to safely integrate the replay driver with the application, though.

RMAN>
restore table
Prior to Database 12c, RMAN did not support native compression during active duplication, so generally, DBAs resorted to another method of restoring from backup, such as copying files over the network through a compressed pipe, or even shipping a tape. In Database 12c, RMAN supports datafile copies over the network with compression. This feature will ease database cloning efforts tremendously. Also, the Active Duplicate command supports network compression during the data transfer, enabling faster clones directly from the production database.
Executing SQL statements from the RMAN command line is not only unwieldy, but the syntax is not exactly user-friendly. Version 12c enhances the RMAN command line so that you can execute SQL statements natively in RMAN without needing the additional SQL clause.

Materialized View Refresh. Non-atomic refreshes of a materialized view can affect the performance of user queries due to the need to maintain read consistency. Delete statements are used for non-atomic refreshes, so if another SQL query accesses the materialized view concurrently, then the query will suffer from performance issues because the query must apply enormous amount of undo records to reconstruct read consistent blocks. Version 12c introduces new optimization, so instead of deleting from original table, a new identical table is populated with refreshed data. At the completion of refresh, tables are swapped, thus completing the out-of-place refresh. Users can query the original table without incurring any additional overhead. This strategy provides operational convenience to refresh materialized view with minimal impact.
IPv6 support. Many organizations are gearing up to certify IPv6 support as IPv4 address space becomes exhausted. Database 12c supports IPv6 for public network addresses. It does not support IPv6 in private network addresses, but this is probably a non-issue.
Parallel upgrade. This is a feature I have been looking for over many years. In highly available environments, keeping the database down for a database upgrade, even for a few hours, is cost-prohibitive. Rolling upgrades are not always possible for major database software upgrades, and it is always a risky adventure to tune Database Upgrade itself. Version 12c uses parallelism to improve the database upgrade to reduce upgrade-related downtime.
Password files in ASM. Another important Database 12c feature is the ability to store password files in ASM (Automatic Storage Management). In RAC, changing passwords for privileged users is a cumbersome task. Even with the use of NFS or another shared file system for the password file, grants must be executed in all instances. In Database 12c, password files are stored in ASM -- and grants need to be executed in just one instance.
The wealth of new features in Oracle Database 12c gives Oracle shops many reasons to consider upgrading. If you manage a cloud database, or wish to improve resource utilization by consolidating multiple databases on shared hardware, you should consider upgrading sooner rather than later. The new pluggable databases feature is extremely useful for co-locating multiple applications in a single database instance.
If you frequently clone databases over a WAN, then you should consider upgrading to to take advantage of RMAN's native compression when transferring the files. If your employer mandates IPv6 protocol support, then you should look at upgrading. Further, sites that make use of numerous materialized views can benefit from the new refresh methods available in the new version.