采用offline的方式更改数据文件位置

本文参照http://www.eygle.com/archives/2005/11/oracle_howt_move_datafile2.html学习

1.将表空间offline

[oracle@lglg root]$ sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.6.0 - Production on Tue Nov 24 08:08:04 2009

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Disabled
Archive destination            /opt/oracle/product/dbs/arch
Oldest online log sequence     18
Next log sequence to archive   20
Current log sequence           20
SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/hcs2000/system01.dbf
/opt/oracle/oradata/hcs2000/cwmlite01.dbf
/opt/oracle/oradata/hcs2000/drsys01.dbf
/opt/oracle/oradata/hcs2000/example01.dbf
/opt/oracle/oradata/hcs2000/indx01.dbf
/opt/oracle/oradata/hcs2000/odm01.dbf
/opt/oracle/oradata/hcs2000/tools01.dbf
/opt/oracle/oradata/hcs2000/users01.dbf
/opt/oracle/oradata/hcs2000/xdb01.dbf
/opt/oracle/oradata/hcs2000/oem_repository.dbf

NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/hcs2000/UNDOTBS3.dbf

14 rows selected.

**修改数据库的归档模式begin

SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  236000476 bytes
Fixed Size                   451804 bytes
Variable Size             201326592 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Disabled
Archive destination            /opt/oracle/product/dbs/arch
Oldest online log sequence     18
Next log sequence to archive   20
Current log sequence           20

SQL> alter database noarchivelog;       

Database altered.

SQL>  archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /opt/oracle/product/dbs/arch
Oldest online log sequence     18
Current log sequence           20

SQL> alter system set log_archive_start= true scope=spfile;

System altered.

重启数据库后

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Enabled
Archive destination            /opt/oracle/product/dbs/arch
Oldest online log sequence     20
Current log sequence           22

***修改end

SQL> alter tablespace users offline;

Tablespace altered.

2.拷贝相应的数据文件

SQL> !cp /opt/oracle/oradata/hcs2000/users01.dbf /opt/oradata/users01.dbf
源目录  新目录

SQL> alter tablespace users rename datafile '/opt/oracle/oradata/hcs2000/users01.dbf' to '/opt/oradata/users01.dbf';

Tablespace altered.

3.将表空间online

SQL> alter tablespace users online;
Tablespace altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/hcs2000/system01.dbf
/opt/oracle/oradata/hcs2000/cwmlite01.dbf
/opt/oracle/oradata/hcs2000/drsys01.dbf
/opt/oracle/oradata/hcs2000/example01.dbf
/opt/oracle/oradata/hcs2000/indx01.dbf
/opt/oracle/oradata/hcs2000/odm01.dbf
/opt/oracle/oradata/hcs2000/tools01.dbf
/opt/oradata/users01.dbf
/opt/oracle/oradata/hcs2000/xdb01.dbf
/opt/oracle/oradata/hcs2000/oem_repository.dbf

NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/hcs2000/UNDOTBS3.dbf

14 rows selected.

 

****将数据库恢复非归档模式begin
SQL> alter system set log_archive_start= true scope=spfile;

System altered.

SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  236000476 bytes
Fixed Size                   451804 bytes
Variable Size             201326592 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.

SQL> alter system set log_archive_start= false scope=spfile;

System altered.

SQL> alter database archivelog;       

Database altered.

SQL>  archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            /opt/oracle/product/dbs/arch
Oldest online log sequence     18
Current log sequence           20

**修改完毕