HOWTO--如何移動Oracle的所有數據文件

HOWTO--如何移動Oracle的所有數據文件

HOWTO--如何移動Oracle的所有數據文件

发表人:vongates | 发表时间: 2005年一月29日, 11:29

昨天在vmware中折騰solaris 9 x86時 也就順手裝上了oracle 因為安裝solaris的時候沒有規划好partition，因為我裝的oracle是817還好占用的空間不多(全部安裝才900多M) 又因為安裝oracle的時候就一路default也就建了個DB，沒有太在意空間問題，問題就來了，在根目錄下只有50M的可用空間了。df -k看了一下 /export/home 這個目錄是空的有700多M 哈哈，空著也是空，就把datafile全部移過來吧。。下面我們來看全過程。實在簡單不做太多說明。。

還是寫上几句小結吧：
1. 在所有操作系統中的移動數據文件的方式是一樣的
2. 此方法是在shutdown DB的情況下做的。如果我們的數據量很大，又不允許停機太久，此方法就不太實用，但我們可以先在線移動除了system和rollback以外的表間中的數據文件，最后停機移動system and rollback表間的數據文件。
3. 在數據庫處于mount下狀態下用 alter database rename file .... to ....;來實現，而alter tablespace tbs_name rename datafile ......to...;是用在DB open狀態下的
全過程如下：

$ sqlplus /nolog

SQL*Plus: Release 8.1.7.0.0 - Production on Sat Jan 29 09:39:44 2005

(c) Copyright 2000 Oracle Corporation.  All rights reserved.

SQL> conn / as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  106139808 bytes
Fixed Size                    73888 bytes
Variable Size              56356864 bytes
Database Buffers           49537024 bytes
Redo Buffers                 172032 bytes
Database mounted.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/oradata/ORCL/system01.dbf
/opt/oracle/oradata/oradata/ORCL/tools01.dbf
/opt/oracle/oradata/oradata/ORCL/rbs01.dbf
/opt/oracle/oradata/oradata/ORCL/temp01.dbf
/opt/oracle/oradata/oradata/ORCL/users01.dbf
/opt/oracle/oradata/oradata/ORCL/indx01.dbf
/opt/oracle/oradata/oradata/ORCL/drsys01.dbf

7 rows selected.

SQL> shutdown immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> host;
$ cd /opt/oracle/
$ cd oradata/oradata/ORCL
$ ls
control01.ctl  drsys01.dbf    redo01.log     system01.dbf   users01.dbf
control02.ctl  indx01.dbf     redo02.log     temp01.dbf
control03.ctl  rbs01.dbf      redo03.log     tools01.dbf
$ cd /export/home
$ ls
lost+found
$ mkdir -p oradata/ORCL/
mkdir: "oradata/ORCL": Permission denied
$ su - root
Password:
Sun Microsystems Inc.   SunOS 5.9       Generic January 2003
# chown -R oracle:dba /export/home
# pwd
/
# ls -al /export
total 6
drwxr-xr-x   3 root     sys          512 Jan 22 16:36 .
drwxr-xr-x  35 root     root        1024 Jan 28 16:03 ..
drwxr-xr-x   3 oracle   dba          512 Jan 22 16:46 home
# exit
$ id
uid=103(oracle) gid=100(dba)
$ cd /export/home
$ pwd
/export/home
$ ls -al
total 20
drwxr-xr-x   3 oracle   dba          512 Jan 22 16:46 .
drwxr-xr-x   3 root     sys          512 Jan 22 16:36 ..
drwx------   2 oracle   dba         8192 Jan 22 16:36 lost+found
$ mkdir -p oradata/ORCL
$ cd /opt/oracle/oradata/oradata/ORCL
$ mv *.dbf /export/home/oradata/ORCL
$ exit

SQL> startup mount;
ORACLE instance started.

Total System Global Area  106139808 bytes
Fixed Size                    73888 bytes
Variable Size              56356864 bytes
Database Buffers           49537024 bytes
Redo Buffers                 172032 bytes
Database mounted.

SQL> desc v$tablespace;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
TS#                                                NUMBER
NAME                                               VARCHAR2(30)

SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
TEMP
RBS
INDX
USERS
DRSYS
TOOLS

7 rows selected.

SQL> alter database rename file '/opt/oracle/oradata/oradata/ORCL/system01.dbf' to '/export/home/oradata/ORCL/system01.dbf';

Database altered.

SQL> alter database rename file '/opt/oracle/oradata/oradata/ORCL/tools01.dbf' to '/export/home/oradata/ORCL/tools01.dbf';
Database altered.

SQL> alter database rename file '/opt/oracle/oradata/oradata/ORCL/rbs01.dbf' to '/export/home/oradata/ORCL/rbs01.dbf';

Database altered.

SQL> alter database rename file '/opt/oracle/oradata/oradata/ORCL/users01.dbf' to '/export/home/oradata/ORCL/users01.dbf';

Database altered.

SQL>
SQL> alter database rename file '/opt/oracle/oradata/oradata/ORCL/indx01.dbf' to '/export/home/oradata/ORCL/indx01.dbf';

Database altered.

SQL> alter database rename file '/opt/oracle/oradata/oradata/ORCL/drsys01.dbf' to '/export/home/oradata/ORCL/drsys01.dbf';

Database altered.

SQL>  select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/export/home/oradata/ORCL/system01.dbf
/export/home/oradata/ORCL/tools01.dbf
/export/home/oradata/ORCL/rbs01.dbf
/opt/oracle/oradata/oradata/ORCL/temp01.dbf
/export/home/oradata/ORCL/users01.dbf
/export/home/oradata/ORCL/indx01.dbf
/export/home/oradata/ORCL/drsys01.dbf

7 rows selected.

SQL> alter database rename file '/opt/oracle/oradata/oradata/ORCL/temp01.dbf' to '/export/home/oradata/ORCL/temp01.dbf';

Database altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/export/home/oradata/ORCL/system01.dbf
/export/home/oradata/ORCL/tools01.dbf
/export/home/oradata/ORCL/rbs01.dbf
/export/home/oradata/ORCL/temp01.dbf
/export/home/oradata/ORCL/users01.dbf
/export/home/oradata/ORCL/indx01.dbf
/export/home/oradata/ORCL/drsys01.dbf

7 rows selected.

SQL> alter database open;

Database altered.

SQL
============

原文地址： http://vongates.itpub.net/post/2553/16516