OracleDB12c New Feature: More to Online Datafile Move

As we already know, now with Oracle Database 12c, it is easier to move a data file. There is more to it… Let us see few operations in 11g, and how they all got simplified with Oracle 12c online file move feature…

1. Rename a file or move a file from one file system to another

  • Take tablespace offline, thus users/application cannot access the content.
  • Copy the file to new destination using OS commands
  • Use ALTER DATABASE RENAME FILE ‘x’ TO ‘y’;
  • Make the tablespace online

With Oracle Database 12c, all you need to do is one statement, and not worry about data file being inaccessible during the operation.

  • ALTER DATABASE MOVE DATAFILE ‘x’ TO ‘y’;

2. Move a file from one ASM diskgroup to another, Move a file from file system to ASM, Move a file from ASM to file system

  • Take the data file to be moved offline using ALTER DATABASE DATAFILE ‘+DG1/mydb/datafile/x1.dbf’ OFFLINE
  • Connect to RMAN and perform copy using COPY DATAFILE  ‘+DG1/mydb/datafile/x1.dbf’ TO ‘+DG2’  [Note, if you are copying a non-OMF file, make sure to include full path and file name for the destination].
  • Find the name of the new file if using OMF by querying DBA_DATA_FILES.
  • Rename the file using ALTER DATABASE RENAME FILE  ‘+DG1/mydb/datafile/x1.dbf’ TO  ‘+DG2/mydb/datafile/x1.dbf’
  • Connect to RMAN to switch to the new file using SWITCH DATAFILE  ‘+DG1/mydb/datafile/x1.dbf’ TO COPY
  • Recover the data file to make it consistent using RECOVER DATAFILE  ‘+DG2/mydb/datafile/x1.dbf’ TO
  • Finally, make the file online using ALTER DATABASE DATAFILE  ‘+DG2/mydb/datafile/x1.dbf’ ONLINE;

With Oracle Database 12c, all you need to do is one statement, and not worry about data file being inaccessible during the operation.

ASM to ASM

  • ALTER DATABASE MOVE DATAFILE  ‘+DG1/mydb/datafile/x1.dbf’ TO  ‘+DG2’;

File System to ASM

  • ALTER DATABASE MOVE DATAFILE  ‘/u01/db/x1.dbf’ TO  ‘+DG2’;

ASM to File System

  • ALTER DATABASE MOVE DATAFILE  ‘+DG1/mydb/datafile/x1.dbf’ TO  ‘/u01/db1/x2.dbf”;

The TO clause can be omitted for OMF, where unique data file name will be created on the location specified by DB_CREATE_FILE_DEST.

There are couple of keywords you may use with the MOVE operation.

  • KEEP does not delete the file from the source location.
  • REUSE will overwrite the file, if file with same name exists in the destination location.

 

 

OracleDB12c New Feature: RMAN Describe

Similar to SQL*Plus DESCRIBE command, RMAN now supports DESCRIBE.

RMAN> desc hr.jobs
using target database control file instead of recovery catalog
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
JOB_ID                                    NOT NULL VARCHAR2(10)
JOB_TITLE                                 NOT NULL VARCHAR2(35)
MIN_SALARY                                         NUMBER(6)
MAX_SALARY                                         NUMBER(6)
RMAN>

If you are connected using the SYSDBA privilege, you can view tables in other schemas, but with SYSBACKUP connection you will not be able to view user tables. SYSBACKUP is new privilege in 12c. [Administrative privileges began with SYSDBA and SYSOPER in 8i, then SYSASM in 11g, now SYSBACKUP, SYSDG and SYSKM in 12c]

RMAN also supports most SQL statements, without the SQL prefix (and messing with quotes around the SQL!!).

RMAN> SELECT user from dual;
USER
------------------------------
SYS
RMAN>