How to change the Data files Location in 12c

In this Section we will cover Data Files location change in Root Container as well in the Pluggable Container.

We have this configuration with us: 1 Root Container (cdb1) with 1 Pluggable Database (pdb1)

Important Note: You cannot change the pluggable datafile location from the root container.

So first start with the Pluggable database pdb1 datafile location change:

SQL> alter session set container=pdb1;

Session altered.

SQL>

Find out the location of Data Files within the PDB:

SQL> select file_name from dba_data_files;
select file_name from dba_data_files
*
ERROR at line 1:
ORA-01219: database or pluggable database not open: queries allowed on fixed tables or views only

SQL>

So PDB is not open, it happened because I just started the Root Container and whenever it happens all PDB start in mount state. Check out the status of the PDB and open it:

SQL> select status from v$Instance;

STATUS
————
MOUNTED

SQL>
SQL> alter database open;

Database altered.

SQL> select status from v$Instance;

STATUS
————
OPEN

SQL>

Now we can find out the location of data files:

SQL> select file_name from dba_data_files;

FILE_NAME
———————————————————————–
/u01/app/oracle/oradata/CDB2/2E01889CD630125FE0530F00A8C039AD/datafile/o1_mf_system_cgf7wwc3_.dbf
/u01/app/oracle/oradata/CDB2/2E01889CD630125FE0530F00A8C039AD/datafile/o1_mf_sysaux_cgf7wwc8_.dbf
/u01/app/oracle/oradata/CDB2/2E01889CD630125FE0530F00A8C039AD/datafile/o1_mf_users_cgf7xmkc_.dbf

SQL>

Create the desired directory to keep the new files:

[oracle@oracle12c ~]$ mkdir -p /u01/app/oracle/oradata/cdb3/pdb1/datafile
[oracle@oracle12c ~]$

Find out the permission of the old directory where the current files are present:

[oracle@oracle12c ~]$ ls -ld /u01/app/oracle/oradata/CDB2/2E01889CD630125FE0530F00A8C039AD
drwxr-x— 3 oracle oinstall 4096 Mar 14 16:55 /u01/app/oracle/oradata/CDB2/2E01889CD630125FE0530F00A8C039AD
[oracle@oracle12c ~]$
[oracle@oracle12c ~]$ ls -ld /u01/app/oracle/oradata/CDB2/2E01889CD630125FE0530F00A8C039AD/datafile/
drwxr-x— 2 oracle oinstall 4096 Mar 14 16:55 /u01/app/oracle/oradata/CDB2/2E01889CD630125FE0530F00A8C039AD/datafile/
[oracle@oracle12c ~]$

Check out the permission of the directory we created to have the new files:

[oracle@oracle12c ~]$ ls -ld /u01/app/oracle/oradata/cdb3/pdb1/datafile
drwxr-xr-x 2 oracle oinstall 4096 Apr 4 13:42 /u01/app/oracle/oradata/cdb3/pdb1/datafile
[oracle@oracle12c ~]$

Change the permission to match with the old one:

[oracle@oracle12c ~]$ chmod 750 /u01/app/oracle/oradata/cdb3/pdb1
[oracle@oracle12c ~]$ chmod 750 /u01/app/oracle/oradata/cdb3/pdb1/datafile
[oracle@oracle12c ~]$
[oracle@oracle12c ~]$ ls -ld /u01/app/oracle/oradata/cdb3/pdb1/datafile
drwxr-x— 2 oracle oinstall 4096 Apr 4 13:42 /u01/app/oracle/oradata/cdb3/pdb1/datafile
[oracle@oracle12c ~]$
[oracle@oracle12c ~]$ ls -ld /u01/app/oracle/oradata/cdb3/pdb1
drwxr-x— 3 oracle oinstall 4096 Apr 4 13:42 /u01/app/oracle/oradata/cdb3/pdb1
[oracle@oracle12c ~]$

Now change the location online, verify you are in PDB:

SQL> show con_name

CON_NAME
——————————
PDB1
SQL>

For SYSTEM Data file:

SQL> alter database move datafile ‘/u01/app/oracle/oradata/CDB2/2E01889CD630125FE0530F00A8C039AD/datafile/o1_mf_system_cgf7wwc3_.dbf’ to
2 ‘/u01/app/oracle/oradata/cdb3/pdb1/datafile/pdb1_system01.dbf’;

Database altered.

SQL>

For SYSAUX Data file:

SQL> alter database move datafile ‘/u01/app/oracle/oradata/CDB2/2E01889CD630125FE0530F00A8C039AD/datafile/o1_mf_sysaux_cgf7wwc8_.dbf’ to
2 ‘/u01/app/oracle/oradata/cdb3/pdb1/datafile/pdb1_sysaux01.dbf’;

Database altered.

SQL>

Try to change location from the root container for a PDB:

SQL> alter session set container=cdb$root;

Session altered.

SQL>
SQL> alter database move datafile ‘/u01/app/oracle/oradata/CDB2/2E01889CD630125FE0530F00A8C039AD/datafile/o1_mf_users_cgf7xmkc_.dbf’ to
2 ‘/u01/app/oracle/oradata/cdb3/pdb1/datafile/pdb1_users01.dbf’;
alter database move datafile ‘/u01/app/oracle/oradata/CDB2/2E01889CD630125FE0530F00A8C039AD/datafile/o1_mf_users_cgf7xmkc_.dbf’ to
*
ERROR at line 1:
ORA-01516: nonexistent log file, data file, or temporary file “10”
SQL>

See it is not allowing to do that, I need to check if we need to have some sort of permission to do it from root or not. I will keep this posted if I get something concrete on it. By default it is not allowing us to do that.

Go back to pdb container and changed the location of last data file:

SQL> alter session set container=pdb1;

Session altered.

SQL> alter database move datafile ‘/u01/app/oracle/oradata/CDB2/2E01889CD630125FE0530F00A8C039AD/datafile/o1_mf_users_cgf7xmkc_.dbf’ to
2 ‘/u01/app/oracle/oradata/cdb3/pdb1/datafile/pdb1_users01.dbf’;

Database altered.

SQL>

Find out the files on physical location:

SQL> !ls -lrt /u01/app/oracle/oradata/cdb3/pdb1/datafile
total 876428
-rw-r—– 1 oracle oinstall 5251072 Apr 4 13:47 pdb1_users01.dbf
-rw-r—– 1 oracle oinstall 618668032 Apr 4 14:35 pdb1_sysaux01.dbf
-rw-r—– 1 oracle oinstall 272637952 Apr 4 14:35 pdb1_system01.dbf

SQL>

So we are good here for PDB, all files of PDB has been moved-

Old Location: /u01/app/oracle/oradata/CDB2/2E01889CD630125FE0530F00A8C039AD/datafile

New Location: /u01/app/oracle/oradata/cdb3/pdb1/datafile/

At this point we are done with PDB datafile location change.

Now we will change the location for the root container. Connect with root container and find out the location:

SQL> alter session set container=cdb$root;

Session altered.

SQL> select file_name from dba_data_files;

FILE_NAME
———————————————————————-
/u01/app/oracle/oradata/CDB2/datafile/o1_mf_users_cgf7glr4_.dbf
/u01/app/oracle/oradata/CDB2/datafile/o1_mf_undotbs1_cgf7gmtn_.dbf
/u01/app/oracle/oradata/CDB2/datafile/o1_mf_system_cgf7f5n4_.dbf
/u01/app/oracle/oradata/CDB2/datafile/o1_mf_sysaux_cgf7cr9q_.dbf

SQL>
SQL>

Find out the directories under cdb3 folder and create a directory beneath cdb3 with name datafile:

SQL> !ls -lrt /u01/app/oracle/oradata/cdb3
total 24
drwxr-xr-x 2 oracle oinstall 4096 Apr 1 14:51 controlfile
drwxr-xr-x 2 oracle oinstall 4096 Apr 3 15:01 onlinelog
drwxr-x— 3 oracle oinstall 4096 Apr 4 13:42 pdb1

SQL> !mkdir /u01/app/oracle/oradata/cdb3/datafile

SQL>

Change the files location now finally:

SQL> alter database move datafile ‘/u01/app/oracle/oradata/CDB2/datafile/o1_mf_users_cgf7glr4_.dbf’ to
2 ‘/u01/app/oracle/oradata/cdb3/datafile/cdb3_users01.dbf’;

Database altered.

SQL> alter database move datafile ‘/u01/app/oracle/oradata/CDB2/datafile/o1_mf_undotbs1_cgf7gmtn_.dbf’ to
2 ‘/u01/app/oracle/oradata/cdb3/datafile/cdb3_undotbs01.dbf’
3 /

Database altered.

SQL>
SQL> alter database move datafile ‘/u01/app/oracle/oradata/CDB2/datafile/o1_mf_system_cgf7f5n4_.dbf’ to
2 ‘/u01/app/oracle/oradata/cdb3/datafile/cdb3_system01.dbf’;

Database altered.

SQL> alter database move datafile ‘/u01/app/oracle/oradata/CDB2/datafile/o1_mf_sysaux_cgf7cr9q_.dbf’ to
2 ‘/u01/app/oracle/oradata/cdb3/datafile/cdb3_sysaux01.dbf’;

Database altered.

SQL>

Confirm the location from the physical side:

SQL> !ls -lrt /u01/app/oracle/oradata/cdb3/datafile/
total 2075696
-rw-r—– 1 oracle oinstall 5251072 Apr 4 15:04 cdb3_users01.dbf
-rw-r—– 1 oracle oinstall 849354752 Apr 4 15:09 cdb3_system01.dbf
-rw-r—– 1 oracle oinstall 230694912 Apr 4 15:10 cdb3_undotbs01.dbf
-rw-r—– 1 oracle oinstall 1038098432 Apr 4 15:11 cdb3_sysaux01.dbf

SQL>

So finally we are done with the location changing of root container as well.

Let me know if anyone find out any problem with my blog.

Thanks!

 

 

 

 

 

 

 

 

 

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