# Fung's DBA World

## Part of 12c New Features

March 31, 2016

With the multitenant architecture, Oracle 12c provide some great new features. Such as RMAN table-level recovery, moving datafiles online and so on. This post will introduce some of the new features in 12c.

### 1. RMAN enhancement

Most of previous RMAN commands are still supported in new 12c. In CDB environments, backup the whole CDB it means including backup ALL PDBs. You also can backup individual PDB just like previous version.

#### 1.1 Table-level recovery

As of 12c, RMAN provide a fast way to recover a dropped/truncated table than before, and this recovery procedure is without any affecting the remaining database objects. Before you can perform the table-level recovery, you must backup the SYSTEM,SYSAUX,UNDO and the relative tablespaces. If the table you want to recover is reside in PDB, you also need to backup the ROOT container’s SYSTEM,SYSAUX, UNDO tablespaces along with PDB’s SYSTEM,SYSAUX tablespaces.
The grammar of table-level recovery is like below:

• Example of table level-recovery

Create a table reside in PDB pdb12c.

Perform a CDB full backup first.

Now, truncate the table

If in production environments, this table maybe still got some DML operations, in this situation, it’s recommended that use remap approache to recover this table.

Check the result:

From the output, we can find what happened during the recovery:

• Create an auxiliary instance automatically

• Restore the dadafiles into the auxiliary instance

• Perform a point in time recovery

• Export the table

• Import the table to a specified table

• Do house keeping, delete the instance

Some restrictions in table-level recovery:

• Additional disk space needed for storing the auxiliary instance

• SYS user tables cannot be recovered

• REMAP with NOT NULL constraints is not supported

This feature only support non-CDB or single-database. It provide possible resolutions regarding data loss anylyze.

Show the advise of the resolutions:

The repair script generated by RMAN as below:

Follow the script to repair the failure:

If the failure still remain in DRA, you can remove it by manually:

#### 1.3 Execute SQLs in RMAN

As of 12c, not like previous version, RMAN can execute SQLs directly without any pre-sql commands, but you cannot change the containers in RMAN session.

### 2. Invisible column

This feature is like hidden column in DB2 database. Below example is the hidden column in DB2.

In Oracle 12c, the invisible column just like the DB2’s hidden column, the difference is in Oracle 12c, you cannot use describe table to show the invisible columns.

External table, temporary table and cluster table are not supported invisible column.

### 3. Online rename/move datafiles

Please refer to previous post Moving Files in Database .

### 4. DDL logging

Enable this feature by turning parameter enable_ddl_logging to true. Then the DDL statement can be logged into a XML file. The default DDL log location is \$ORACLE_BASE/diag/rdbms/DBNAME/log/ddl

You can find the log under the diag directory:

This post only covers some of the 12c new features, more freatures will be discovered in the future’s post.

EOF