Uwe Hesse

This user hasn't shared any biographical information

Homepage: http://uhesse.com

Online Move of Datafiles for Pluggable Databases

From my present Oracle Database 12c New Features course in Zürich: We have introduced the handy new functionality that you can move datafiles online in 12c. That is at first glance having an issue for pluggable databases:

 

SQL> @whoami
USER: SYS
SESSION ID: 253
CURRENT_SCHEMA: SYS
INSTANCE NAME: cdb1
CDB NAME: cdb1
CONTAINER NAME: CDB$ROOT
DATABASE ROLE: PRIMARY
OS USER: oracle
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb1/system01.dbf
/u01/app/oracle/oradata/cdb1/sysaux01.dbf
/u01/app/oracle/oradata/cdb1/undotbs01.dbf
/u01/app/oracle/oradata/cdb1/pdbseed/system01.dbf
/u01/app/oracle/oradata/cdb1/users01.dbf
/u01/app/oracle/oradata/cdb1/pdbseed/sysaux01.dbf
/u01/app/oracle/oradata/pdb1_1/system01.dbf
/u01/app/oracle/oradata/pdb1_1/sysaux01.dbf
/u01/app/oracle/oradata/pdb1_1/SAMPLE_SCHEMA_users01.dbf
/u01/app/oracle/oradata/pdb1_1/example01.dbf

10 rows selected.

SQL> alter database move datafile '/u01/app/oracle/oradata/pdb1_1/example01.dbf' to '/home/oracle/example01.dbf';
alter database move datafile '/u01/app/oracle/oradata/pdb1_1/example01.dbf' to '/home/oracle/example01.dbf'
*
ERROR at line 1:
ORA-01516: nonexistent log file, data file, or temporary file "11"

The error message is quite useless in this case. It works, but you need to be in the PDBs container where the datafile belongs to:

SQL> alter session set container=pdb1_1;

Session altered.

SQL> alter database move datafile '/u01/app/oracle/oradata/pdb1_1/example01.dbf' to '/home/oracle/example01.dbf';

Database altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb1/undotbs01.dbf
/u01/app/oracle/oradata/pdb1_1/system01.dbf
/u01/app/oracle/oradata/pdb1_1/sysaux01.dbf
/u01/app/oracle/oradata/pdb1_1/SAMPLE_SCHEMA_users01.dbf
/home/oracle/example01.dbf

Don’t believe it (even error messages may lie!), test it :-)

About these ads

Leave a comment

LVC Producers at #Oracle University

LVC stands for Live Virtual Class – this is how we call our courses done interactively over the internet. At Oracle University, we have a fine crew of people who take care that the attendees (as well as the instructor, sometimes) are not impacted by technical problems. This can be e.g. connectivity issues, browser incompatibilities, questions how to deal with the learning platform WebEx or which way to choose to access the remote lab environment. All that and more is handled by LVC producers, so that the instructor can focus on the educational matters. I really appreciate this separation of duties, because I find it already demanding enough to deliver high quality Oracle Technology classes!

Many of the LVC producers work from Bucharest, and they kindly invited me to visit them at their workplace today. I gladly accepted and we had the nicest chat up on the 6th floor – it was so cool to meet these guys in person that supported me so many times already! As you can see, this is a bright bunch :-)

LVC Producers from Bucharest

2 Comments

#Oracle University Expert Summit in London

Three days full of seminars are offered by Oracle University in London (19th to 21st May) at the Expert Summit

Oracle University Expert Summit

It is my pleasure to present there together with Arup Nanda, Dan Hotka, Jonathan Lewis and my dear colleagues Iloon Ellen-Wolff and Joel Goodman.

One funny detail here: There has been another event (an Exadata Workshop) in Vienna on my schedule during that week – yes, I’m very busy these days. Now in order to make it possible for me to present in London, the class in Vienna will be interrupted on Tuesday and continued on Wednesday :-)

A big “Thank You!”  goes out to the attendees in Vienna who agreed with the one day interruption to make that happen! Specifically, I’m going to talk about and demonstrate the 12c New Features of Data Guard in London.

Leave a comment

Follow

Get every new post delivered to your Inbox.

Join 2,635 other followers

%d bloggers like this: