在oracle中修改数据文件常用的方法有两种:在mount状态修改 和 在open状态修改
方法一、在mount修改
此方法适用于归档模式和非归档模式,具体操作如下:
SQL> select file#,ts#,status,name from v$datafile;
FILE# TS# STATUS NAME
---------- ---------- ------- ---------------------------------------------
1 0 SYSTEM /u01/oracle10g/oradata/guoshun/system01.dbf
2 1 ONLINE /u01/oracle10g/oradata/guoshun/undotbs01.dbf
3 2 ONLINE /u01/oracle10g/oradata/guoshun/sysaux01.dbf
4 4 ONLINE /u01/oracle10g/oradata/guoshun/users01.dbf
5 6 ONLINE /u01/oracle10g/oradata/guoshun/ogg01.dbf
6 7 ONLINE /u01/oracle10g/oradata/guoshun/boke01.dbf
7 7 ONLINE /u01/oracle10g/oradata/guoshun/boke03.dbf ---要修改的数据文件
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount ---------启动到mount状态
ORACLE instance started.
Total System Global Area 184549376 bytes
Fixed Size 1218412 bytes
Variable Size 83888276 bytes
Database Buffers 96468992 bytes
Redo Buffers 2973696 bytes
Database mounted.
在操作系统层面修改数据文件的路径及名称:
[oracle@node guoshun]$ mv boke03.dbf ../boke02.dbf
在数据库层面修改数据文件的路径及名称,其实是修改controlfile的内容:
SQL> alter database rename file '/u01/oracle10g/oradata/guoshun/boke03.dbf' to '/u01/oracle10g/oradata/boke02.dbf';
SQL> alter database open;
Database altered.
SQL> select file#,ts#,status,name from v$datafile;
FILE# TS# STATUS NAME
---------- ---------- ------- ---------------------------------------------
1 0 SYSTEM /u01/oracle10g/oradata/guoshun/system01.dbf
2 1 ONLINE /u01/oracle10g/oradata/guoshun/undotbs01.dbf
3 2 ONLINE /u01/oracle10g/oradata/guoshun/sysaux01.dbf
4 4 ONLINE /u01/oracle10g/oradata/guoshun/users01.dbf
5 6 ONLINE /u01/oracle10g/oradata/guoshun/ogg01.dbf
6 7 ONLINE /u01/oracle10g/oradata/guoshun/boke01.dbf
7 7 ONLINE /u01/oracle10g/oradata/boke02.dbf ---已经修改成功了
7 rows selected.
方法二、在open状态修改此方法只适用于归档模式的数据库,具体操作如下(此次操作是将上面实验中更改的数据文件的路径及名称改回来):
查看数据库归档模式:
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 28
Next log sequence to archive 30
Current log sequence 30
SQL> select file#,ts#,status,name from v$datafile;
FILE# TS# STATUS NAME
---------- ---------- ------- ---------------------------------------------
1 0 SYSTEM /u01/oracle10g/oradata/guoshun/system01.dbf
2 1 ONLINE /u01/oracle10g/oradata/guoshun/undotbs01.dbf
3 2 ONLINE /u01/oracle10g/oradata/guoshun/sysaux01.dbf
4 4 ONLINE /u01/oracle10g/oradata/guoshun/users01.dbf
5 6 ONLINE /u01/oracle10g/oradata/guoshun/ogg01.dbf
6 7 ONLINE /u01/oracle10g/oradata/guoshun/boke01.dbf
7 7 ONLINE /u01/oracle10g/oradata/boke02.dbf -----要修改的数据文件
将要修改的数据文件offline:
SQL> alter database datafile 7 offline drop;
Database altered.
操作系统层面修改路径和名称:
SQL> !mv /u01/oracle10g/oradata/boke02.dbf /u01/oracle10g/oradata/guoshun/boke03.dbf
数据库层面修改路径和名称,其实是修改controlfile:
SQL> alter database rename file '/u01/oracle10g/oradata/boke02.dbf' to '/u01/oracle10g/oradata/guoshun/boke03.dbf';
Database altered.
由于此时数据库是open状态,offline状态数据文件的scn号与当前的scn号不一致,所以要进行恢复操作:
SQL> recover datafile 7;
Media recovery complete.
将数据文件online:
SQL> alter database datafile 7 online;
Database altered.
SQL> select file#,ts#,status,name from v$datafile;
FILE# TS# STATUS NAME
---------- ---------- ------- ---------------------------------------------
1 0 SYSTEM /u01/oracle10g/oradata/guoshun/system01.dbf
2 1 ONLINE /u01/oracle10g/oradata/guoshun/undotbs01.dbf
3 2 ONLINE /u01/oracle10g/oradata/guoshun/sysaux01.dbf
4 4 ONLINE /u01/oracle10g/oradata/guoshun/users01.dbf
5 6 ONLINE /u01/oracle10g/oradata/guoshun/ogg01.dbf
6 7 ONLINE /u01/oracle10g/oradata/guoshun/boke01.dbf
7 7 ONLINE /u01/oracle10g/oradata/guoshun/boke03.dbf ---又改回来了吧
7 rows selected.