Shulou(Shulou.com)05/31 Report--

This article mainly explains the "Oracle data file migration methods", the article explains the content is simple and clear, easy to learn and understand, the following please follow the editor's ideas slowly in depth, together to study and learn "what are the methods of Oracle data file migration"!

(1) method 1: offline tablespace

* * *

* main steps: *

* 1. Offline tablespace: alter tablespace tablespace_name offline;*

* 2. Copy the data file to a new directory; *

* 3. Rename modifies the table space and modifies the control file; *

* 4. Online tablespace; *

* * *

Take changing the location of the datafile / opt/oracle/oradata/ZERONE01.DBF of the tablespace zerone as an example

1. Offline tablespace zerone to make tablespace zerone offline

-

SQL > alter tablespace zerone offline

The tablespace has changed.

2. Copy the data file to the new directory

--

Note:

* 1. If you check whether the new directory / home/oracle/oradata/zerone exists, create this path if it does not exist, and change the directory attributes:

* # mkdir-p / home/oracle/oradata/zerone

* # chown-R oracle:oinstall / home/oracle/oradata/zerone

Copy data file

# cp / opt/oracle/oradata/ZERONE01.DBF / home/oracle/oradata/zerone/ZERONE01.DBF

# chown oracle:oinstall / home/oracle/oradata/zerone/ZERONE01.DBF

3. Rename modifies the tablespace data file to the new location and modifies the control file

SQL > alter tablespace zerone rename datafile'/ opt/oracle/oradata/ZERONE01.DBF' to'/ home/oracle/oradata/zerone/ZERONE01.DBF'

The tablespace has changed.

4. Online tablespace

-

SQL > alter tablespace zerone online

The tablespace has changed.

Check:

SQL > select name from v$datafile

NAME

/ home/oracle/oradata/zerone/ZERONE01.DBF

SQL > select file_name,tablespace_name from dba_data_files where tablespace_name='ZERONE'

FILE_NAME TABLESPACE_NAME

-

ZERONE / home/oracle/oradata/zerone/ZERONE01.DBF

(2) method 2: SQL modifies the location of the data file

* *

* main steps:

* 1. Close the database

* 2. Copy the data file to a new location

* 3. Start the database to mount status

* 4. Modify the location of data files through SQL

* 5. Open the database

* *

1. Close the database

-

SQL > shutdown immediate

The database has been closed.

The database has been uninstalled.

The ORACLE routine has been closed.

2. Copy the data file to the new location

-

Same method one

3. Start the database to mount status

-

SQL > startup mount

The ORACLE routine has been started.

Total System Global Area 167772160 bytes

Fixed Size 1247900 bytes

Variable Size 96470372 bytes

Database Buffers 67108864 bytes

Redo Buffers 2945024 bytes

The database is loaded.

4. Modify the location of data files through SQL

-

SQL > alter database rename file'/ opt/oracle/oradata/ZERONE01.DBF' to'/ home/oracle/oradata/zerone/ZERONE01.DBF'

The database has changed.

5. Open the database

-

SQL > alter database open

The database has changed.

Check:

SQL > select name from v$datafile

NAME

/ home/oracle/oradata/zerone/ZERONE01.DBF

SQL > select file_name,tablespace_name from dba_data_files where tablespace_name='ZERONE'

FILE_NAME TABLESPACE_NAME

-

ZERONE / home/oracle/oradata/zerone/ZERONE01.DBF

How to see which tablespace the data file belongs to

(1) View the tablespace corresponding to the data file of the permanent tablespace

SQL > select TABLESPACE_NAME from dba_data_files where FILE_NAME=' data file full path'

(2) View the temporary tablespace corresponding to the data file of the temporary tablespace

SQL > select TABLESPACE_NAME from dba_temp_files where FILE_NAME=' data file full path'

Thank you for your reading, these are the contents of "what are the methods of Oracle data file migration". After the study of this article, I believe you have a deeper understanding of what the method of Oracle data file migration has, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.