How to modify the location of Oracle Database Data Files

How to modify the location of Oracle Database Data Files

A data file is a file ending with dbf, that is, a file used to store our data. This file is usually large, so we generally store it in a directory with a large space, let's take a look at how to modify the location of Oracle database data files.

 

How to change the location of Oracle data files

A: In archive Mode

1. Connect to the Oracle database of the file to be moved in sqlplus, and then execute the following SQL statement to view the location of the Oracle database file:

SQL> select file_name from sys. dba_data_files;

FILE_NAME
--------------------------------------------------------------
C: ORA8ORADATAORACLEUSERS01. DBF
C: ORA8ORADATAORACLEDR01. DBF
C: ORA8ORADATAORACLETOOLS01. DBF
C: ORA8ORADATAORACLEINDX01. DBF
C: ora8oradata1_lerbs01. DBF
C: ORA8ORADATAORACLETEMP01. DBF
C: ORA8ORADATAORACLESYSTEM01. DBF

7 records are found. The location and name of the file to be moved are recorded.

2. next we will take the file "C: ORA8ORADATAORACLEUSERS01. the following example shows how to move DBF to "D: ORADATAUSERS01.DBF". To move other files, perform the same operation (modify the file path and name according to the actual situation.

<1> first, stop all users from connecting to the database and shut down the database. In the command line window, enter:

C: svrmgrl (Press ENTER)
SVRMGR> connect internal (Press ENTER)
SVRMGR> shutdown immediate (Press ENTER)
# Wait for the database prompt to close
SVRMGR> exit

<2> find the file "USERS01.DBF" in the "C: ORA8ORADATAORACLE" directory and copy it to the directory "D: ORADATA. If multiple files are moved, repeat this step.

<3> enter the command window and enter the following content:
C: svrmgrl (Press ENTER)
SVRMGR> connect internal (Press ENTER)
SVRMGR> startup mount (Press ENTER)
# Wait until the database prompts that the load is successful
SVRMGR> alter database rename file 'C: ORA8ORADATAORACLEUSERS01. dbf' to 'd: oradatausers01.dbf ';
# After the prompt statement is processed, if you want to move multiple files, modify the file path and name and then run the preceding statement again. Open the database.
SVRMGR> alter database open;
SVRMGR> exit

The database file is successfully moved.
B: If the database is in non-archive mode, follow these steps to change the file path:

1. Shut down the database

2. System-level File Replication

3. Start the database to the mount status.

3. Modify the data file location using SQL

4. Open the database

The procedure is as follows:

1. The database is in non-archive mode.

SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Enabled
Archive destination/opt/oracle/oradata/conner/archive
Oldest online log sequence 150
Current log sequence 153

2. You need to move the test. dbf File

SQL> select name from v $ datafile;

NAME
------------------------------------------------------------
/Opt/oracle/oradata/conner/system01.dbf
/Opt/oracle/oradata/conner/undotbs01.dbf
/Opt/oracle/oradata/conner/users01.dbf
/Opt/oracle/test. dbf

3. Shut down the database

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

4. copy the file to a new location.

SQL>! Cp/opt/oracle/test. dbf/opt/oracle/oradata/conner/test. dbf

5. Start the database to the mount status.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 101782828 bytes
Fixed Size 451884 bytes
Variable Size 37748736 bytes
Database Buffers 62914560 bytes
Redo Buffers 667648 bytes
Database mounted.
SQL> select name from v $ datafile;

NAME
-----------------------------------------------------------
/Opt/oracle/oradata/conner/system01.dbf
/Opt/oracle/oradata/conner/undotbs01.dbf
/Opt/oracle/oradata/conner/users01.dbf
/Opt/oracle/test. dbf

6. Modify the file location

SQL> alter database rename file '/opt/oracle/test. dbf' to'/opt/oracle/oradata/conner/test. dbf ';

Database altered.

SQL> alter database open;

Database altered.

SQL> select name from v $ datafile;

NAME
----------------------------------------------------------------
/Opt/oracle/oradata/conner/system01.dbf
/Opt/oracle/oradata/conner/undotbs01.dbf
/Opt/oracle/oradata/conner/users01.dbf
/Opt/oracle/oradata/conner/test. dbf