Move database files in ORACLE

The ORACLE tutorial you are looking at is: move database files in ORACLE. The ORACLE database consists of three files: data files, control files and online log files. Due to changes in disk space, or based on database disks I/O
For performance tuning, database administrators may consider moving database files. Let's take the UNIX platform as an example to discuss three ways to move database files.

-- i. move data file:

-- you can use ALTER DATABASE and ALTER TABLESPACE to move data files.

-- 1. ALTER DATABASE method;

With this method, you can move data files from any table space.

-- STEP 1. Database:

$ svrmgrl
SVRMGR > CONNECT INTERNAL;
SVRMGR > SHUTDOWN;
SVRMGR > EXIT;

-- STEP 2. Move data files with operating system commands:
-- move the data file app1_data.ora from /ora/oracle7/data1 to /ora/oracle7/data2:

---- $ mv /ora/oracle7/data1/app1_data.ora /ora/oracle7/data2

-- STEP 3. Mount database, rename the data file

with the ALTER DATABASE command

---- $ svrmgrl

SVRMGR > CONNECT INTERNAL;
SVRMGR > STARTUP MOUNT;
SVRMGR > ALTER DATABASE RENAME FILE
2 > /ora/oracle7/data1/app1_data.oraTO
3 > /ora/oracle7/data2/app1_data.ora;

STEP 4. Open database:.
SVRMGR > ALTER DATABASE OPEN;
SVRMGR >SELECT NAME,STATUS FROM V$DATAFILE;

-- 2. ALTER TABLESPACE method:
In this way, the data file is required to be neither part of the SYSTEM table space nor part of the table space with the ACTIVE rollback or temporary segment.

-- STEP1. OFFLINE:

$ svrmgrl
SVRMGR > CONNECT INTERNAL;
SVRMGR > ALTER TABLESPACE app1_data OFFLINE;
SVRMGR > EXIT;

STEP2. Move the data file with the operating system command:
The data file app1_data. ora from/ora/oracle7 /
data1 directory to/ora/oracle7 / data2 directory:
$ mv /ora/oracle7/data1/app1_data.ora /ora/oracle7/data2

STEP3. Use ALTER TABLESPACE to change the data file name:
$ svrmgrl
SVRMGR > CONNECT INTERNAL;
SVRMGR > ALTER TABLESPACE app1_data RENAME DATAFILE
2 > /ora/oracle7/data 1/app1_data.ora TO
3 > /ora/oracle7/data2/app1_data.ora;

STEP4. Put this data file in the table space ONLINE:
SVRMGR > ALTER TABLESPACE app1_data ONLINE;
SVRMGR > SELECT NAME,STATUS FROM V$DATAFILE;

-- ii. Movement control file:

-- the control file is specified in the INIT.ORA file. Move control file is relatively simple, down the database, edit INIT.ORA, move control file, restart
The database.

STEP 1. Database:
$ svrmgrl
SVRMGR > CONNECT INTERNAL;
SVRMGR > SHUTDOWN;
SVRMGR > EXIT;

STEP 2. Move control files with operating system commands:
Will control the file ctl3orcl.ora from /ora/oracle7
/ data1 moved to/ora/oracle7 / data2 directory:
$ mv /ora/oracle7/data 1/ctrl3orcl.ora
/ora/oracle7/data2

STEP 3. Edit INIT.ORA file:
The INIT.ORA file is in the $ORACLE_HOME/dbs directory,
Modify the parameter "control_files", where the control file after the move is specified:
control_files = (/ora/oracle7/data 1/ctrl1orcl.ora,
/ora/oracle7/data1/ctrl2orcl.ora,
/ora/oracle7/data2/ctrl3orcl.ora)

4. Restart database:
$ svrmgrl
SVRMGR > CONNECT INTERNAL;
SVRMGR > STARTUP;
SVRMGR >SELECT name FROM V$CONTROLFILE;
SVRMGR > EXIT;

-- 3. Mobile online log file:
1. Stop database:
$ svrmgrl
SVRMGR > CONNECT INTERNAL;
SVRMGR > SHUTDOWN;
SVRMGR > EXIT;

STEP 2. Move online log files with operating system commands:
Will online log file redolog1.ora from /ora/oracle7
/ data1 moved to/ora/oracle7 / data2 directory:
$ mv /ora/oracle7/data 1/redolog1.ora
/ora/oracle7/data2

STEP 3. Mount database, ALTER DATABASE
Command to change the online log file name:.
$ svrmgrl
SVRMGR > CONNECT INTERNAL;
SVRMGR > STARTUP MOUNT CC1;
SVRMGR > ALTER DATABASE RENAME FILE
2 > /ora/oracle7/data 1/redolog1.ora TO
3 > /ora/oracle7/data 2/redolog1.ora;

Restart database:.STEP SVRMGR > ALTER DATABASE OPEN;
SVRMGR >SELECT MEMBERFROM V$LOGFILE;