近几日,公司一业务需求要将一些 dump 文件导入到测试库,但是却有一个头痛的事,发现数据库所在的根目录已经 100%,这样肯定是没办法导入 dump 数据文件的,而且数据库已面临巨大的宕机风险了,故申请了临时停机窗口,打算将原有的数据文件移动到其他新挂载的盘符中。
由于此测试库是单机版的且使用文件系统管理,故采用 alter database rename file 'XXX' to 'XXX'; 来实现这个。
说明:此环境为 Linux CentOS6.7 Oracle11.2.0.4
0、检查发现根目录 100%
TEST:/home/oracle$df -h
df: `/root/.gvfs': Permission denied
Filesystem Size Used Avail Use% Mounted on
/dev/xvda2 67G 66G 16M 100% /
udev 2.1G 68K 2.1G 1% /dev
tmpfs 2.1G 792K 2.1G 1% /dev/shm
/dev/xvdb1 197G 188M 187G 1% /testdata
1、查询数据文件号,文件名,路径,表空间(保存数据记录)
set linesize 200 pagesize 200
col file_name for a50
col tablespace_name for a20
select file_name,FILE_ID,TABLESPACE_NAME,BYTES/1024/1024/1024 from dba_Data_files order by 2;
FILE_NAME FILE_ID TABLESPACE_NAME BYTES/1024/1024/1024
-------------------------------------------------- ---------- -------------------- --------------------
/app/oracle/oradata/JiekeXutest/system01.dbf 1 SYSTEM 3.21289063
/app/oracle/oradata/JiekeXutest/sysaux01.dbf 2 SYSAUX 1.5234375
/app/oracle/oradata/JiekeXutest/undotbs01.dbf 3 UNDOTBS1 30
/app/oracle/oradata/JiekeXutest/users01.dbf 4 USERS 6.6784668
/app/oracle/oradata/JiekeXutest/rhzx01.dbf 5 RHZX 13.2151489
/app/product/11.2.0/db/dbs/D:test.ora 6 TEST .9765625
6 rows selected.
2、查询临时文件(保存数据)
set linesize 200 pagesize 200
col file_name for a50
col tablespace_name for a20
select file_name,FILE_ID,TABLESPACE_NAME,BYTES/1024/1024/1024 from dba_temp_files order by 2;
FILE_NAME FILE_ID TABLESPACE_NAME BYTES/1024/1024/1024
-------------------------------------------------- ---------- -------------------- --------------------
/app/oracle/oradata/JiekeXutest/temp01.dbf
3、查询 redo(保存数据)
set linesize 150;
set pagesize 50;
column MB format a10;
column STATUS format a12;
column MEMBER format a60;
select l.GROUP#,l.THREAD#,l.members,l.BYTES/1024/1024||'MB' MB,l.STATUS, lf.TYPE,lf.MEMBER from v$log l,v$logfile lf where l.GROUP#=lf.GROUP#;
GROUP# THREAD# MEMBERS MB STATUS TYPE MEMBER
---------- ---------- ---------- ---------- ------------ ------- ------------------------------------------------------------
3 1 1 50MB CURRENT ONLINE /app/oracle/oradata/JiekeXutest/redo03.log
2 1 1 50MB INACTIVE ONLINE /app/oracle/oradata/JiekeXutest/redo02.log
1 1 1 50MB INACTIVE ONLINE /app/oracle/oradata/JiekeXutest/redo01.log
4、关闭数据库
ps -ef |grep smon
echo $ORACLE_SID
alter system switch logfile; --切换日志
shutdown immediate
5、将数据文件拷贝到另外的目录(/testdata)使用 oracle 执行,注意权限不变
新建目录/testdata/app/oracle/oradata/JiekeXutest
TEST:/home/oracle$df -h
df: `/root/.gvfs': Permission denied
Filesystem Size Used Avail Use% Mounted on
/dev/xvda2 67G 66G 16M 100% /
udev 2.1G 68K 2.1G 1% /dev
tmpfs 2.1G 792K 2.1G 1% /dev/shm
/dev/xvdb1 197G 188M 187G 1% /testdata
TEST:/testdata$mkdir -p /testdata/app/oracle/oradata/JiekeXutest
TEST:/testdata$cd /app/oracle/oradata/JiekeXutest
TEST:/app/oracle/oradata/JiekeXutest$ll
total 58787740
-rw-r----- 1 oracle oinstall 9846784 Jul 19 21:00 control01.ctl
-rw-r----- 1 oracle oinstall 52429312 Jul 19 20:59 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Jul 19 21:00 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Jul 19 21:00 redo03.log
-rw-r----- 1 oracle oinstall 14189666304 Jul 19 21:00 rhzx01.dbf
-rw-r----- 1 oracle oinstall 1635786752 Jul 19 21:00 sysaux01.dbf
-rw-r----- 1 oracle oinstall 3449823232 Jul 19 21:00 system01.dbf
-rw-r----- 1 oracle oinstall 1326456832 Jul 19 17:00 temp01.dbf
-rw-r----- 1 oracle oinstall 32212262912 Jul 19 21:00 undotbs01.dbf
-rw-r----- 1 oracle oinstall 7170957312 Jul 19 21:00 users01.dbf
TEST:/app/oracle/oradata/JiekeXutest$pwd
/app/oracle/oradata/JiekeXutest
TEST:/app/oracle/oradata/JiekeXutest$mv *.dbf /testdata/app/oracle/oradata/JiekeXutest/
6、启动数据库到mount
startup mount
7、更改数据库普通文件,临时,redo名称
alter database rename file '/app/oracle/oradata/JiekeXutest/system01.dbf' to '/testdata/app/oracle/oradata/JiekeXutest/system01.dbf';
alter database rename file '/app/oracle/oradata/JiekeXutest/sysaux01.dbf' to '/testdata/app/oracle/oradata/JiekeXutest/sysaux01.dbf';
alter database rename file '/app/oracle/oradata/JiekeXutest/undotbs01.dbf' to '/testdata/app/oracle/oradata/JiekeXutest/undotbs01.dbf';
alter database rename file '/app/oracle/oradata/JiekeXutest/users01.dbf' to '/testdata/app/oracle/oradata/JiekeXutest/users01.dbf';
alter database rename file '/app/oracle/oradata/JiekeXutest/rhzx01.dbf' to '/testdata/app/oracle/oradata/JiekeXutest/rhzx01.dbf';
--alter database rename file '/app/product/11.2.0/db/dbs/D:test.ora' to '/testdata/app/oracle/oradata/JiekeXutest/test.dbf';
--这个D盘没法更改,路径不对,无法辨认,故暂时放弃
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01141: error renaming data file 6 - new file
'/testdata/app/oracle/oradata/JiekeXutest/test.dbf' not found
ORA-01110: data file 6: '/app/product/11.2.0/db/dbs/D:test.ora'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
alter database rename file '/app/oracle/oradata/JiekeXutest/temp01.dbf' to '/testdata/app/oracle/oradata/JiekeXutest/temp01.dbf';
alter database rename file '/app/oracle/oradata/JiekeXutest/redo01.log' to '/testdata/app/oracle/oradata/JiekeXutest/redo01.log';
alter database rename file '/app/oracle/oradata/JiekeXutest/redo02.log' to '/testdata/app/oracle/oradata/JiekeXutest/redo02.log';
alter database rename file '/app/oracle/oradata/JiekeXutest/redo03.log' to '/testdata/app/oracle/oradata/JiekeXutest/redo03.log';
8、打开数据库
SQL> alter database open;
Database altered.
SQL>
SQL>
9、检查新的数据文件路径
检查各个数据文件发现已经到新的路径下了。
SQL> set linesize 200 pagesize 200
SQL> col file_name for a50
SQL> col tablespace_name for a20
select file_name,FILE_ID,TABLESPACE_NAME,BYTES/1024/1024/1024 from dba_Data_files order by 2;SQL>
FILE_NAME FILE_ID TABLESPACE_NAME BYTES/1024/1024/1024
-------------------------------------------------- ---------- -------------------- --------------------
/testdata/app/oracle/oradata/JiekeXutest/system01.dbf 1 SYSTEM 3.21289063
/testdata/app/oracle/oradata/JiekeXutest/sysaux01.dbf 2 SYSAUX 1.5234375
/testdata/app/oracle/oradata/JiekeXutest/undotbs01.db 3 UNDOTBS1 30
f
/testdata/app/oracle/oradata/JiekeXutest/users01.dbf 4 USERS 6.6784668
/testdata/app/oracle/oradata/JiekeXutest/rhzx01.dbf 5 RHZX 13.2151489
/app/product/11.2.0/db/dbs/D:test.ora 6 TEST .9765625
6 rows selected.
SQL> set linesize 200 pagesize 200
SQL> col file_name for a50
SQL> col tablespace_name for a20
select file_name,FILE_ID,TABLESPACE_NAME,BYTES/1024/1024/1024 from dba_temp_files order by 2;SQL>
FILE_NAME FILE_ID TABLESPACE_NAME BYTES/1024/1024/1024
-------------------------------------------------- ---------- -------------------- --------------------
/testdata/app/oracle/oradata/JiekeXutest/temp01.dbf 1 TEMP 1.23535156
SQL> set linesize 150;
SQL> set pagesize 50;
SQL> column MB format a10;
column STATUS format a12;
column MEMBER format a60;
select l.GROUP#,l.THREAD#,l.members,l.BYTES/1024/1024||'MB' MB,l.STATUS, lf.TYPE,lf.MEMBER from v$log l,v$logfile lf where l.GROUP#=lf.GROUP#;
SQL> SQL> SQL>
GROUP# THREAD# MEMBERS MB STATUS TYPE MEMBER
---------- ---------- ---------- ---------- ------------ ------- ------------------------------------------------------------
3 1 1 50MB CURRENT ONLINE /testdata/app/oracle/oradata/JiekeXutest/redo03.log
2 1 1 50MB INACTIVE ONLINE /testdata/app/oracle/oradata/JiekeXutest/redo02.log
1 1 1 50MB INACTIVE ONLINE /testdata/app/oracle/oradata/JiekeXutest/redo01.log
SQL>
10、查看文件系统大小
查看文件系统大小发现根目录也已经到 15%,任务完成,完美收工,故此记录一下!
TEST:/testdata/app/oracle/oradata/JiekeXutest$df -h
df: `/root/.gvfs': Permission denied
Filesystem Size Used Avail Use% Mounted on
/dev/xvda2 67G 9.9G 57G 15% /
udev 2.1G 68K 2.1G 1% /dev
tmpfs 2.1G 792K 2.1G 1% /dev/shm
/dev/xvdb1 197G 57G 131G 31% /testdata
TEST:/testdata/app/oracle/oradata/JiekeXutest$