如何将ORACLE数据库数据文件迁移到其它目录

如何将ORACLE数据库数据文件迁移到其它目录 

 

一、实验环境

平台：VMware Server 1.0.6 Linux 2.6.18-164 el5

DB Version: Oracle 10g 10.2.0.1

Oracle SID: orcl

数据库运行在归档模式

sys@ORCL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

或者：

sys@ORCL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/arch_orcl
Oldest online log sequence     28
Next log sequence to archive   30
Current log sequence           30

 

二、操作步骤：

源数据库数据文件位置：/u01/oradata/orcl
实验中想把数据文件迁移到的位置：/u01/oradata/orcl_test

 

源数据库数据文件位置：

sys@ORCL> select file_name from dba_data_files;

FILE_NAME
----------------------------------------
/u01/oradata/orcl/system01.dbf
/u01/oradata/orcl/undotbs01.dbf
/u01/oradata/orcl/sysaux01.dbf
/u01/oradata/orcl/users01.dbf

sys@ORCL> select file_name from dba_temp_files;

FILE_NAME
----------------------------------------
/u01/oradata/orcl/temp01.dbf

sys@ORCL> col member for a30
sys@ORCL> select member from v$logfile ;

MEMBER
------------------------------
/u01/oradata/orcl/redo01.log
/u01/oradata/orcl/redo02.log
/u01/oradata/orcl/redo03.log

需要移动所有的数据文件，采用alter database 方法

1、关闭数据库

 sys@ORCL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

2、移动数据文件到/u01/oradata/orcl_test目录

[oracle@ora10gserv orcl]$ pwd
/u01/oradata/orcl
[oracle@ora10gserv orcl]$ mv * /u01/oradata/orcl_test/
[oracle@ora10gserv orcl]$ ls
[oracle@ora10gserv orcl]$ cd /u01/oradata/orcl_test/
[oracle@ora10gserv orcl_test]$ ls
control01.ctl  control03.ctl  redo02.log  sysaux01.dbf  temp01.dbf     users01.dbf
control02.ctl  redo01.log     redo03.log  system01.dbf  undotbs01.dbf

3、修改控制文件位置

[oracle@ora10gserv /]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Aug 2 00:05:27 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.

Total System Global Area  465567744 bytes
Fixed Size                  1220024 bytes
Variable Size             146801224 bytes
Database Buffers          314572800 bytes
Redo Buffers                2973696 bytes
SQL> create pfile='/u01/initorcl.ora' from spfile;

File created.

SQL> host vi /u01/initorcl.ora

orcl.__db_cache_size=314572800
orcl.__java_pool_size=4194304
orcl.__large_pool_size=4194304
orcl.__shared_pool_size=138412032
orcl.__streams_pool_size=0
*.audit_file_dest='/dba/admin/orcl/adump'
*.background_dump_dest='/dba/admin/orcl/bdump'
*.compatible='10.2.0.1.0'
*.control_file_record_keep_time=14
*.control_files='/u01/oradata/orcl_test/control01.ctl','/u01/oradata/orcl_test/control02.ctl','/u01/oradata/orcl_test/control03.ctl'
*.core_dump_dest='/dba/admin/orcl/cdump'
*.db_block_size=8192
*.db_domain='lsf.com.cn'
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.db_recovery_file_dest='/dba/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='location=/u01/arch_orcl'
*.open_cursors=300
*.pga_aggregate_target=154140672
*.processes=1000
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=2000
*.sga_target=463470592
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/dba/admin/orcl/udump'

SQL> shutdown immediate
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> create spfile from pfile='/u01/initorcl.ora';

File created.

4、启动数据库到mount状态，更改数据文件位置

idle> startup mount
ORACLE instance started.

Total System Global Area  465567744 bytes
Fixed Size                  1220024 bytes
Variable Size             146801224 bytes
Database Buffers          314572800 bytes
Redo Buffers                2973696 bytes
Database mounted.

idle> alter database rename file '/u01/oradata/orcl/system01.dbf' to '/u01/oradata/orcl_test/system01.dbf';

Database altered.

idle> alter database rename file '/u01/oradata/orcl/undotbs01.dbf' to '/u01/oradata/orcl_test/undotbs01.dbf';

Database altered.

idle> alter database rename file '/u01/oradata/orcl/sysaux01.dbf' to '/u01/oradata/orcl_test/sysaux01.dbf';

Database altered.

idle> alter database rename file '/u01/oradata/orcl/users01.dbf' to '/u01/oradata/orcl_test/users01.dbf';

Database altered.

5、继续接着第4步，更改联机日志文件位置

idle> alter database rename file '/u01/oradata/orcl/redo01.log' to '/u01/oradata/orcl_test/redo01.log';

Database altered.

idle> alter database rename file '/u01/oradata/orcl/redo02.log' to '/u01/oradata/orcl_test/redo02.log';

Database altered.

idle> alter database rename file '/u01/oradata/orcl/redo03.log' to '/u01/oradata/orcl_test/redo03.log';

Database altered.

6、打开数据库

idle> alter database open;

Database altered.

idle> conn /as sysdba
Connected.
sys@ORCL> select file_name from dba_data_files;

FILE_NAME
----------------------------------------
/u01/oradata/orcl_test/system01.dbf
/u01/oradata/orcl_test/undotbs01.dbf
/u01/oradata/orcl_test/sysaux01.dbf
/u01/oradata/orcl_test/users01.dbf

sys@ORCL> select file_name from dba_temp_files;

FILE_NAME
----------------------------------------
/u01/oradata/orcl/temp01.dbf
sys@ORCL> col member for a40
sys@ORCL> select member from v$logfile;

MEMBER
----------------------------------------
/u01/oradata/orcl_test/redo01.log
/u01/oradata/orcl_test/redo02.log
/u01/oradata/orcl_test/redo03.log

sys@ORCL> col name for a50
sys@ORCL> select name from v$controlfile;

NAME
--------------------------------------------------
/u01/oradata/orcl_test/control01.ctl
/u01/oradata/orcl_test/control02.ctl
/u01/oradata/orcl_test/control03.ctl

7、临时文件的处理

由于控制文件不记录临时文件的信息，所以不能使用alter database rename file 命令完成，只能删除掉原来的再创建一个

sys@ORCL> create temporary tablespace temp02 tempfile '/u01/oradata/orcl_test/temp02.dbf' size 200m autoextend on extent management local uniform size 1m;

Tablespace created.

sys@ORCL> alter database default temporary tablespace temp02;

Database altered.

sys@ORCL> drop tablespace temp including contents and datafiles;

Tablespace dropped.

sys@ORCL> select file_name from dba_temp_files;

FILE_NAME
----------------------------------------
/u01/oradata/orcl_test/temp02.dbf

[oracle@ora10gserv orcl_test]$ ls -l
total 1427572
-rw-r----- 1 oracle oinstall   7061504 Aug  2 00:20 control01.ctl
-rw-r----- 1 oracle oinstall   7061504 Aug  2 00:20 control02.ctl
-rw-r----- 1 oracle oinstall   7061504 Aug  2 00:20 control03.ctl
-rw-r----- 1 oracle oinstall  52429312 Aug  2 00:20 redo01.log
-rw-r----- 1 oracle oinstall  52429312 Aug  2 00:15 redo02.log
-rw-r----- 1 oracle oinstall  52429312 Aug  2 00:15 redo03.log
-rw-r----- 1 oracle oinstall 251666432 Aug  2 00:20 sysaux01.dbf
-rw-r----- 1 oracle oinstall 503324672 Aug  2 00:20 system01.dbf
-rw-r----- 1 oracle oinstall 209723392 Jul 21 22:00 temp01.dbf
-rw-r----- 1 oracle oinstall 209723392 Aug  2 00:20 temp02.dbf
-rw-r----- 1 oracle oinstall 419438592 Aug  2 00:20 undotbs01.dbf
-rw-r----- 1 oracle oinstall 104865792 Aug  2 00:15 users01.dbf
[oracle@ora10gserv orcl_test]$ rm -rf temp01.dbf

sys@ORCL> select username,temporary_tablespace from dba_users;

USERNAME                       TEMPORARY_TABLESPACE
------------------------------ ------------------------------
MGMT_VIEW                      TEMP02
SYS                            TEMP02
SYSTEM                         TEMP02
DBSNMP                         TEMP02
SYSMAN                         TEMP02
OUTLN                          TEMP02
MDSYS                          TEMP02
ORDSYS                         TEMP02
CTXSYS                         TEMP02
ANONYMOUS                      TEMP02
EXFSYS                         TEMP02
DMSYS                          TEMP02
WMSYS                          TEMP02
XDB                            TEMP02
ORDPLUGINS                     TEMP02
SI_INFORMTN_SCHEMA             TEMP02
OLAPSYS                        TEMP02
MDDATA                         TEMP02
DIP                            TEMP02
TSMSYS                         TEMP02

20 rows selected.

 

--End--