Moving Online Data files in Oracle 12c

Dear Readers,

In this article, we will see the following Moving Online Data files in Oracle 12c.

In order to rename, move and relocate a datafile up to 11g, we had to put a tablespace or database completely offline. As we can see here drawback is Database is not available this whole maintenance activity.

In a nutshell prior to 12c, we need downtime windows to relocate, rename or copy a datafile. From 12c, one single step performs the required action while the database remains entirely available in read and write for users, without any data loss.

The oracle database 12c online move datafile feature provides the capability to move an online datafile from one kind of storage system to another (Like an example from non-asm to asm) while the database is open and accessing the file. While datafile moving online you can perform operation like Data Manipulation Language and Data Definition Language. You can create indexes, tables, rebuild indexes online. You can select tables and partition data. If objects are compressed while the data file is moved, the compression remain the same.

Let’s look at 12c new feature, which is being able to move online data file and partition as part of 12c.

Set your database environment and check your datafile

Check datafile location

Creating one directory here, to perform our task

When an oracle managed file used, we need to set first db_create_file_dest but as you can see above db_create_file_dest is not set

Let’s check that our datafile with the help of below command

As you can see above directory that it is empty so let’s move datafile. To move datafile look below command

Now check again at location that you are able to find ktexpts01.dbf datafile or not

Now you can see above that our datafile has been moved successfully.

Here, we have two more option that you would like to know

1.KEEP => You can move the datafile to new location and keep that datafile at original location also. So basically you will have backup copy at original place.

Move datafile using KEEP

Check datafile location

Check datafile at physical level

As you can see above command at both place we can find datafile ktexpts01.dbf.

2.REUSE => It just reuses the existing data file that is at that location (overwrite datafile with the same name)

Lets check datafile from DB level.

In case you are  moving datafiles which belongs to Pluggable database(Container environment ).Below command showing you container and pluggable database file

If we will try to move file that belong to PDB from container database then it will show an error like below

If we want to move pluggable database file then we need to enter/log-in into pluggable database after that we can move database normally as above we did in container database.

Check datafile location

 

As you can see above we have successfully moved pluggable database file also.

 

The ALTER DATABASE MOVE DATAFILE syntax does not work for redo log files, control files and temporary files means we cannot move this files with the help of this 12c new feature.

V$SESSION_LONGOPS view to display ongoing online move operation like number of blocks moved so far. Each ongoing operation has one row.

There is two points that we need to keep in mind while performing above operation.

1. After moving datafile, Oracle automatically deletes old data file and also prevent the user from overwriting an existing file.
2. This whole operation requires twice the size of the files to be copied as when you started to move datafile, oracle first make the copy of the datafile after successfully completed, pointers to the datafile are updated and the old file is removed from the file system.

This feature is not compatible with OFFLINE datafile but it is compatible with block media recovery, read only tablespace, read write tablespace.

If you execute flashback database when file is moving, flashback database will not change file name to the original file name but it will restore old contents of the datafile.

 

Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates

KTEXPERTS is always active on below social media platforms.

Facebook : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts
Instagram : https://www.instagram.com/knowledgesharingplatform