I came across some unexpected behaviour of file management in 12c Container Databases. I had to move file from +FRA to +DATA asm diskgroup, Oracle 12c gives you this wonderful feature of moving the file online while DMLs, DDLs are in progress. In 11.2 you still have to place the file offline, copy it to the other place (asm or filesystem), recover the file and switch online at the end. In 12c you can just issue the command like

ALTER DATABASE MOVE DATAFILE 22 TO '+DATA';
Database altered.

or if you have set DB_CREATE_FILE_DEST to +DATA all you need to do is just:

ALTER DATABASE MOVE DATAFILE 22;
Database altered.

But, I thought that when you have in example 30 PDBs in the CDB you can do all the operations as CDB’s GOD (sysdba). Let’s say you have to move datafiles online of all the PDBs to the different diskgroup (in ex. from +FRA to +DATA).

Login to CDB

$ sqlplus / as sysdba
show con_name
CON_NAME
------------------------------
CDB$ROOT

Try to move file number 22 which belongs to PDB

 
ALTER DATABASE MOVE DATAFILE 22 TO '+DATA';
ALTER DATABASE MOVE DATAFILE 22 TO '+DATA'
*
ERROR at line 1:
ORA-01516: nonexistent log file, data file, or temporary file "22"

What a surprise, because file is visible in the v$datafile when quering from CDB.

Let’s try to do the same in PDB

ALTER SESSION SET CONTAINER=PDB1;
ALTER DATABASE MOVE DATAFILE 22 TO '+DATA';
Database altered.

It seems like you can only manage the datafiles and tablespaces of the container just in that container. Being logged in to the CDB$ROOT you can manage just the files and tablespaces of the root container, being logged in to the PDB you can manage just its files.

Maybe it is logically consistent as the role separation is secured, but SYSDBA is SYSDBA, should be able to the the job from the CDB level. I have to think about it more, what was behind it.

Any ideas guys??

Follow up: I had asked Oracle support about it. They claimed this is normal behaviour. What is funny, you can offline and online datafile of PDB with ALTER PLUGGABLE DATABASE, but there is no possibility to MOVE it.

In example:

https://docs.oracle.com/database/121/SQLRF/statements_2008.htm#SQLRF55667

Waiting for info if this is going to change in future release.

About the author

 
maciej tokar
Maciej Tokar

An Oracle technology geek and crazy long distance runner, DBA24 Owner
Senior Oracle DBA / Consultant / [OCP10g, OCP12c, OCE RAC 10g] / [experience: 9y+]
Currently working for Bluegarden (Oslo Norway) by Miratech Group
Past: Mastercard / Trevica by Britenet, Citi International PLC, PZU

 
View Maciej Tokar's profile on LinkedIn         logoDB4
LinkedIn Auto Publish Powered By : XYZScripts.com