如何移動Oracle的所有數據檔－Johnson峰的部落格

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
============