Move Oracle Datafiles ONLINE in 12c

-
Do you remember the old days when we had to move datafiles, and this process had to be completed offline, it could not be done while users were accessing the datafile? Gone are those days now!
In 12c things changed, and since, datafiles can be moved online, while the users are accessing the content of the datafile.

Why would you want to move a datafile?

1) you need to place the datafile to a different location. By example: /u01/oradata/TSTDB/user_data01.dbf to /u02/oradata/TSTDB/user_data01.dbf
2) you need to rename the file, as the initial name is not correct, and you want to make the correction. By example /u01/oradata/TSTDB/user_data)01.dbf to u01/oradata/TSTDB/user_data01.dbf
3) you are moving the file to ASM
4) another reason you might have


Anyhow, the good news is that you can perform this operation online.
In order to accomplish this task, we need to use the ALTER DATABASE MOVE DATAFILE statement. You can refer to the datafile either by name, or by datafile number. To get the file name, you can query either  DBA_DATA_FILES or V$DATAFILE views.
You can use the keyword: KEEP, which will move the file to the new location and keep the original file as well.
When you move a file, if a file with the same name already exists, you will get an error, unless you use the REUSE keyword. From my point of view, this keyword could be dangerous to use. What if the existing file belongs to a different tablespace, and you are overwriting it? So if I were you, I would not use the REUSE keyword, just out of caution.

So let’s get our hands dirty with some examples

–get the file names:

SQL> select file_name, file_id from dba_data_files;
C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\SYSTEM01.DBF 9
C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\SYSAUX01.DBF 10
C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\UNDOTBS01.DBF 11
C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\USERS01.DBF 12
C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\LOST_PROTECTION_TBS.DBF 14


SQL> select name, file# from v$datafile;
C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\SYSTEM01.DBF 9
C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\SYSAUX01.DBF 10
C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\UNDOTBS01.DBF 11
C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\USERS01.DBF 12
C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\LOST_PROTECTION_TBS.DBF 14

Let’s move the datafile from current location to new location

SQL> alter database move datafile 'C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\UNDOTBS01.DBF' to 'C:\oracle\oradata\TESTDB\testdbpdb\newlocation\users01.dbf';

SQL> select name, file# from v$datafile;
C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\SYSTEM01.DBF 9
C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\SYSAUX01.DBF 10
C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\NEWLOCATION\USERS01.DBF 11
C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\USERS01.DBF 12
C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\LOST_PROTECTION_TBS.DBF 14

Oops! Just noticed I moved and renamed the UNDO datafile to users datafile by mistake. Let’s correct that and change it back to undo instead:

SQL> alter database move datafile 'C:\oracle\oradata\TESTDB\testdbpdb\newlocation\users01.dbf' to 'C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\NEWLOCATION\UNDOTBS01.DBF';

SQL> select name, file# from v$datafile;
C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\SYSTEM01.DBF 9
C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\SYSAUX01.DBF 10
C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\NEWLOCATION\UNDOTBS01.DBF 11
C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\USERS01.DBF 12
C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\LOST_PROTECTION_TBS.DBF 14

These examples are great with regular tablespaces, but I’m interested to see if I can move system, sysaux, undo or temp datafiles online.
 
Let’s try that:

Q1) Can I move system and sysaux tablespace? Yes!

SQL> alter database move datafile 'C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\SYSTEM01.DBF' to 'C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\SYSTEM.DBF';

Database altered.

SQL> alter database move datafile 'C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\SYSAUX01.DBF' to 'C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\SYSAUX.DBF';

Database altered.

Q2) Can I move undo tablespace? Yes, just demonstrated that above!

Q3) Can I move temp tablespace? Looks like not

SQL> alter database move tempfile 'C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\TEMP01.DBF' to 'C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\TEMP.DBF';
alter database move tempfile 'C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\TEMP01.DBF' to 'C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\TEMP.DBF'
*
ERROR at line 1:
ORA-00905: missing keyword

SQL> alter database move datafile 'C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\TEMP01.DBF' to 'C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\TEMP.DBF';
alter database move datafile 'C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\TEMP01.DBF' to 'C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\TEMP.DBF'
*
ERROR at line 1:
ORA-01516: nonexistent log file, data file,

or temporary file "C:\ORACLE\ORADATA\TESTDB\TESTDBPDB\TEMP01.DBF" in the current container


source: D Robete blog

Comments

Post a Comment

Popular posts from this blog

Oracle Cloud Infrastructure(OCI) - Part2

-
Image
In my earlier post (OCI -Part1 ), we have gone through OCI basic details, let's dive deep more into it. Before that, we need to u nderstand the following concepts and terminology to help you get started with Oracle Cloud Infrastructure.  Note: OCI is re-branding of Bare Metal Cloud Service (BMCS) BARE METAL HOST Oracle Cloud Infrastructure provides you control of the physical host (“bare metal”) machine. Bare metal compute instances run directly on bare metal servers without a hypervisor. When you provision a bare metal compute instance, you maintain sole control of the physical CPU, memory, and network interface card (NIC). You can configure and utilize the full capabilities of each physical machine as if it were hardware running in your own data center. You do not share the physical machine with any other tenants. REGIONS AND AVAILABILITY DOMAINS  Oracle Cloud Infrastructure is physically hosted in regions and availability domains. A region is a localized geographic area, an...
Read more

Script to display the SQL text for a specific SQL_ID

-
--> Purpose:    Script to display the SQL text  for a specific SQL_ID. -->             It returns the SQL_TEXT. --> Parameters: sql_id --> To Run:     @sqltext.sql sql_id --> Example:    @sqltext.sql 27b3qfm5x89xn --> --> Copyright 2020@The Ultimate SQL Tuning Formula set echo off set define '&' set verify off define _sql_id=&1 set verify off set feedback off set linesize 200 set heading on set termout on col sqltext format a100 word_wrapped select  sql_text as sqltext from gv$sqltext where sql_id = '&_sql_id' order by piece; **************************************************************************************** --> Purpose:    Script to display the SQL text  for a specific SQL_ID. -->             It returns the SQL_TEXT. --> Caution:  ...
Read more

How to Change Your EnterpriseOne Environment Color

-
Image
Sometimes users start working and then forget which EnterpriseOne environment they are logged into. Can you tell which environment you are logged into? Luckily, there is a new UDC that allows you to change your EnterpriseOne environment color. You can now set up environments to be color-coded for when you need a clear visual distinction as to which environment you are logged into. This enables you to quickly identify the non-production environments by color. The new UDC is 98/CO and is available in EnterpriseOne Tools Release 9.2.4 with Applications Release 9.1 and 9.2. The image below shows the default EnterpriseOne environment color. However, you can easily change the color to reflect different environments. For example, DV could be red, PY could be green, and PD could be yellow, as shown below. To do so, just add a new row in the User Defined Codes section, enter the EnterpriseOne environment name and the HEX value of the color you want the environment to be, and ...
Read more