Online Move Datafile

Oracle Database 12c

--> Here i want move the datafiles from all PDBs to 
    the same location where the datafiles for the CDB are placed:

==> CDB:

  1* select file_name from dba_data_files
10:21:48 SQL> /

FILE_NAME
-----------------------------------------------
/data03/CDB/users01.dbf
/data03/CDB/undotbs01.dbf
/data03/CDB/system01.dbf
/data03/CDB/sysaux01.dbf

4 rows selected.

==> PDB:

10:22:17 SQL> select name, open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           READ WRITE
PDB3                           READ WRITE

==> Creating directories:

cd /data03/CDB

mkdir PDB1
mkdir PDB3

1.) Connecting on the PDB1;

alter session set container=PDB1;

10:27:25 SQL>  SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') pdb from dual;

PDB
----------
PDB1

10:28:01 SQL> select file_name from dba_data_files;

FILE_NAME
------------------------------------------------------------
/oracle/oradata/CDB/pdb1/system01.dbf
/oracle/oradata/CDB/pdb1/sysaux01.dbf
/oracle/oradata/CDB/pdb1/ts_pdb1.dbf


2.) Moving datafiles:

alter database move datafile '/oracle/oradata/CDB/pdb1/system01.dbf' to '/data03/CDB/PDB1/system01.dbf';
alter database move datafile '/oracle/oradata/CDB/pdb1/sysaux01.dbf' to '/data03/CDB/PDB1/sysaux01.dbf';
alter database move datafile '/oracle/oradata/CDB/pdb1/ts_pdb1.dbf' to '/data03/CDB/PDB1/ts_pdb1.dbf';

3.) Checking:

10:30:34 SQL> select file_name from dba_data_files;

FILE_NAME
------------------------------------------------------------
/data03/CDB/PDB1/sysaux01.dbf
/data03/CDB/PDB1/ts_pdb1.dbf
/data03/CDB/PDB1/system01.dbf

3 rows selected.

10:30:42 SQL> !ls -ltr /oracle/oradata/CDB/pdb1/
total 520
-rw-r----- 1 oracle oinstall 20979712 Mar  7 12:49 pdbseed_temp012016-01-07_08-28-45-AM.dbf

4.) Connecting on the PDB3;

alter session set container=PDB3;
 
10:32:13 SQL>  SELECT SYS_CONTEXT ('USERENV', 'CON_NAME') pdb from dual;

PDB
----------
PDB3

10:32:26 SQL> select file_name from dba_data_files;

FILE_NAME
------------------------------------------------------------
+DATA/CDB/PDB3/system01.dbf
+DATA/CDB/PDB3/sysaux01.dbf

5.) Moving datafiles, on this case from ASM:

alter database move datafile '+DATA/CDB/PDB3/system01.dbf' to '/data03/CDB/PDB3/system01.dbf';
alter database move datafile '+DATA/CDB/PDB3/sysaux01.dbf' to '/data03/CDB/PDB3/sysaux01.dbf';

6.) Checking:

10:35:22 SQL> select file_name from dba_data_files;

FILE_NAME
------------------------------------------------------------
/data03/CDB/PDB3/system01.dbf
/data03/CDB/PDB3/sysaux01.dbf
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s