Oracle 12c and RMAN automatic datafile creation

By William Sescu

A lot of new features popped up with RMAN 12c, like  Expansion of Multi-section support, or Simplified cross-platform migration and many many more. However, in this post I would like to highlight a small, but very helpful new feature which I demonstrated during my session at the SOUG day.

The automatic datafile creation

In earlier versions of Oracle, you might run into situations where you create a new tablespace, and some objects into it, and then, all of a sudden, the DB crashes or the datafile is lost, without ever being backed up.

This is where the following command comes into play:

alter database create datafile <NAME>;

However, in Oracle 12c, this is done automatically for you, in case you use RMAN.

Lets create a tablespace and afterwards resize the datafile.

SQL> create tablespace dbi datafile '/home/oracle/rman/dbi01.dbf' size 16M;

Tablespace created.

SQL> alter database datafile '/home/oracle/rman/dbi01.dbf' resize 32M;

Database altered.

Now lets create a table in the new tablespace.

SQL> create table t_dbi tablespace dbi as select * from dba_objects;

Table created.

SQL> select count(*) from t_dbi;

  COUNT(*)
----------
    120130

Afterwards, we simulate an error and then check what Oracle says.

$ echo destroy > /home/oracle/rman/dbi01.dbf

SQL> alter system flush buffer_cache; 

System altered. 

SQL> select count(*) from t_dbi; 
select count(*) from t_dbi 
* 
ERROR at line 1: 
ORA-03135: connection lost contact Process ID: 25538 
Session ID: 387 Serial number: 55345

From the alert.log

Errors in file /u00/app/oracle/diag/rdbms/ocm121/OCM121/trace/OCM121_ora_25904.trc:
ORA-01157: cannot identify/lock data file 11 - see DBWR trace file
ORA-01110: data file 11: '/home/oracle/rman/dbi01.dbf'

Ok. Datafile is gone, and we have no backup. Lets do a preview to see, how Oracle would restore the datafile.

RMAN> restore ( datafile 11 ) preview;

Starting restore at 30-SEP-2016 15:46:27
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=355 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=126 device type=DISK

datafile 11 will be created automatically during restore operation
using channel ORA_DISK_1
using channel ORA_DISK_2

List of Archived Log Copies for database with db_unique_name OCM121
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - --------------------
519     1    529     A 30-SEP-2016 12:23:00
        Name: +FRA/OCM121/ARCHIVELOG/2016_09_30/thread_1_seq_529.454.923931563

recovery will be done up to SCN 7346834
Media recovery start SCN is 7346834
Recovery must be done beyond SCN 7346834 to clear datafile fuzziness
Finished restore at 30-SEP-2016 15:46:28

As you can see in the preview output, Oracle will create the datafile automatically for us. Ok. Lets try it.

RMAN> restore ( datafile 11 );

Starting restore at 30-SEP-2016 15:48:29
using channel ORA_DISK_1
using channel ORA_DISK_2

creating datafile file number=11 name=/home/oracle/rman/dbi01.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 30-SEP-2016 15:48:31

RMAN> recover datafile 11;

Starting recover at 30-SEP-2016 15:49:17
using channel ORA_DISK_1
using channel ORA_DISK_2

starting media recovery
media recovery complete, elapsed time: 00:00:02

Finished recover at 30-SEP-2016 15:49:20

RMAN> alter database datafile 11 online;

Statement processed

Ready. We can now access our table again without running the “alter database create datafile” command during the restore. And the resize to 32M was also done for us. However, the resize came during the recovery part.

$ ls -l dbi01.dbf
-rw-r----- 1 oracle asmadmin 33562624 Sep 30 15:52 dbi01.dbf

From my point of view, a small, but quite helpful feature.

One last remark. I have done my demo with Oracle 12cR1 with July 2016 PSU. In that version, the RMAN PREVIEW command has a bug, which says that you cannot recover into a consistent state, even if you can.  After applying the following OnOff patch on top of July PSU 2016, the RMAN PREVIEW command works as expected.

Patch 20315311: RMAN-5119: RECOVERY CAN NOT BE DONE TO A CONSISTENT STATE

Cheers,

William