How to transfer ORACLE database files to another machine

How to transfer the ORACLE database tutorial file to another machine

Due to insufficient space caused by video data restoration, some data files need to be transferred to other hard disks. In reference

Network Information, combined with their own practices, summarizes the Oracle Data File Transfer Method.

1) manually copy the data file to be transferred 'd: oraclew.wtable42.dbf 'to the new bit

Set 'e: oraclew.wtable42.dbf '.

 

2) run the following command in SQLPLUS to Offline the tablespace to which the data file belongs:

Sqlplus> alter tablespace GWTABLE offline;

 

3) modify the tablespace file path alter database rename file 'old file path' to 'new file path ';

Sqlplus> alter database rename file 'd: oraclew.wtable42.dbf'

'E: oraclew.wtable42.dbf ';

 

4) after the RENAME Command is executed, ORACLE considers the database file to be corrupted and prompts "media needs to be restored"

Sqlplus> shutdown immediate; -- close the database

Sqlplus> startup mount; -- starts the database in archive mode, and does not open the database

Sqlplus> recover datafile 'e: oraclew.wtable42.dbf '; -- media recovery

Sqlplus> alter database open;

 

5) Online the tablespace.

Sqlplus> alter tablespace GWTABLE online;

 

6) view data files, tablespaces, and statuses

Sqlplus> select file_name, tablespace_name, status from dba_data_files;

Refer to another method

When using Oracle databases, database files gradually increase with the increase of data.

The disk space may be insufficient. In this case, we can move the database file to another large hard disk partition.

Next I will introduce the Oracle database by moving the database file on drive C to drive D in Oracle for Windows.

File movement methods and steps.
1. Connect to the Oracle database of the file to be moved in sqlplus, and then execute the following SQL statement to view the Oracle

Database File Location:

SQL> select file_name from sys. dba_data_files;

FILE_NAME
--------------------------------------------------------------
E: ORACLEORADATAORADBSYSTEM01.DBF
E: ORACLEORADATAORADBUNDOTBS01.DBF
E: ORACLEORADATAORADBCWMLITE01.DBF
E: ORACLEORADATAORADBDRSYS01.DBF
E: ORACLEORADATAORADBEXAMPLE01.DBF
E: ORACLEORADATAORADBINDX01.DBF
E: ORACLEORADATAORADBODM01.DBF
E: ORACLEORADATAORADBTOOLS01.DBF
E: ORACLEORADATAORADBUSERS01.DBF
E: ORACLEORADATAORADBXDB01.DBF

10 records are queried.

Record the location and name of the file to be moved.

2. Next we will move the file "E: ORACLEORADATAORADBUSERS01.DBF"

"D: ORADATAUSERS01.DBF" is used as an example to describe how to move other files.

Modify the file path and name.

<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 "E: ORACLEORADATAORADB" directory, and then copy

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 'e: oracleoradataoradbusers01.dbf'

'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. End

Open the database.
SVRMGR> alter database open;
SVRMGR> exit

The database file is successfully moved.