Oracle 表空间数据文件物理位置转移 | 您所在的位置:网站首页 › oracle数据文件移动位置 › Oracle 表空间数据文件物理位置转移 |
数据表空间
数据表空间的文件转移示例展示如下: 1.登录sqlplus SQL>sqlplus / as sysdba2.确认需要迁移的数据文件 SQL> select * from dba_data_files where tablespace_name like 'USERS'; FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_ ---------- ------------------------------ ---------- ---------- --------- ------------ ------------ ------------ -------- -------- -------- -------- /oradata/oracle/app/oracle/oradata/test/users01.dbf 6 USERS 1028915200 125600 AVAILABLE 6 YES 3.4360E+10 4194302 160 1027866624 125472 ONLINE3.修改USERS表空间为Offline状态 SQL>alter tablespace USERS offline; Tablespace altered.4.查看表空间状态 SQL> select * from dba_data_files where tablespace_name like 'USERS'; FILE_NAME FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_ ---------- ------------------------------ ---------- ---------- --------- ------------ ------------ ------------ -------- -------- -------- -------- /oradata/oracle/app/oracle/oradata/test/users01.dbf 6 USERS 1028915200 125600 AVAILABLE 6 YES 3.4360E+10 4194302 160 1027866624 125472 OFFLINE5.拷贝USERS数据表空间文件 $ cp /oradata/oracle/app/oracle/oradata/test/users01.dbf /oradata/oracle/datafile6.修改oracle表空间指向地址 SQL>alter database rename file '/oradata/oracle/app/oracle/oradata/test/users01.dbf' to '/oradata/oracle/datafile/users01.dbf'; Database altered.7.修改表空间为Online状态 SQL> alter tablespace USERS online; Tablespace altered.8.查看修改后的表空间文件存储位置. SQL> select file_name from sys.dba_data_files; FILE_NAME -------------------------------------------------------------------------------- /oradata/oracle/app/oracle/oradata/test/system01.dbf /oradata/oracle/app/oracle/oradata/test/sysaux01.dbf /oradata/oracle/datafile/users01.dbf /oradata/oracle/app/oracle/oradata/test/undotbs01.dbf9.查看空闲表空间 SQL> select tablespace_name ,sum(bytes)/1024/1024||'M' as freespaces from dba_data_files group by tablespace_name; TABLESPACE_NAME FREESPACES ------------------------------ ----------------------------------------- SYSAUX 1070M UNDOTBS1 355M USERS 981.25M SYSTEM 810M10删除原数据文件 $ rm -rf /oradata/oracle/app/oracle/oradata/test/users01.dbf 系统表空间数据表空间的文件转移示例展示如下: 1.登录sqlplus SQL>sqlplus / as sysdba2.确认需要迁移的数据文件 SQL> select name from v$controlfile; NAME -------------------------------------------------------------------------------- /oradata/oracle/app/oracle/oradata/test/control01.ctl /oradata/oracle/app/oracle/fast_recovery_area/test/control02.ctl SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /oradata/oracle/app/oracle/oradata/test/system01.dbf /oradata/oracle/app/oracle/oradata/test/sysaux01.dbf /oradata/oracle/app/oracle/oradata/test/undotbs01.dbf /oradata/oracle/datafile/users01.dbf SQL> select member from v$logfile; MEMBER -------------------------------------------------------------------------------- /oradata/oracle/app/oracle/oradata/test/redo03.log /oradata/oracle/app/oracle/oradata/test/redo02.log /oradata/oracle/app/oracle/oradata/test/redo01.log /oradata/oracle/app/oracle/oradata/test/standby01.log /oradata/oracle/app/oracle/oradata/test/standby02.log /oradata/oracle/app/oracle/oradata/test/standby03.log /oradata/oracle/app/oracle/oradata/test/standby04.log 7 rows selected. SQL> select name from v$tempfile; NAME -------------------------------------------------------------------------------- /oradata/oracle/app/oracle/oradata/test/temp01.dbf3.创建pfile SQL> create pfile from spfile; File created.4.关闭oracle实例 SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down.5.拷贝待转移文件 $ mkdir fast_recovery_area $cp /oradata/oracle/app/oracle/oradata/test/control01.ctl /oradata/oracle/datafile $cp /oradata/oracle/app/oracle/fast_recovery_area/test/control02.ctl /oradata/oracle/datafile/fast_recovery_area $cp /oradata/oracle/app/oracle/oradata/test/system01.dbf /oradata/oracle/datafile $cp /oradata/oracle/app/oracle/oradata/test/sysaux01.dbf /oradata/oracle/datafile $cp /oradata/oracle/app/oracle/oradata/test/undotbs01.dbf /oradata/oracle/datafile $cp /oradata/oracle/app/oracle/oradata/test/redo03.log /oradata/oracle/datafile $cp /oradata/oracle/app/oracle/oradata/test/redo02.log /oradata/oracle/datafile $cp /oradata/oracle/app/oracle/oradata/test/redo01.log /oradata/oracle/datafile $cp /oradata/oracle/app/oracle/oradata/test/standby01.log /oradata/oracle/datafile $cp /oradata/oracle/app/oracle/oradata/test/standby02.log /oradata/oracle/datafile $cp /oradata/oracle/app/oracle/oradata/test/standby03.log /oradata/oracle/datafile $cp /oradata/oracle/app/oracle/oradata/test/standby04.log /oradata/oracle/datafile $cp /oradata/oracle/app/oracle/oradata/test/temp01.dbf /oradata/oracle/datafile可简化为如下命令: $ mkdir fast_recovery_area $cp /oradata/oracle/app/oracle/oradata/test/control01.ctl /oradata/oracle/datafile $cp /oradata/oracle/app/oracle/fast_recovery_area/test/control02.ctl /oradata/oracle/datafile/fast_recovery_area $cp /oradata/oracle/app/oracle/oradata/test/*.dbf /oradata/oracle/datafile $cp /oradata/oracle/app/oracle/oradata/test/*.log /oradata/oracle/datafile6.修改pfile中控制文件路径 $cd $ORACLE_HOME/dbs $ vi initwind.ora *.control_files='/oradata/oracle/datafile/control01.ctl','/oradata/oracle/datafile/fast_recovery_area/control02.ctl'7.启动实例并不打开数据库 SQL> startup mount; ORACLE instance started. Total System Global Area 2516582400 bytes Fixed Size 2927528 bytes Variable Size 671089752 bytes Database Buffers 1828716544 bytes Redo Buffers 13848576 bytes Database mounted.8.修改oracle表空间指向地址 SQL>alter database rename file '/oradata/oracle/app/oracle/oradata/test/system01.dbf' to '/oradata/oracle/datafile/system01.dbf'; Database altered. SQL>alter database rename file '/oradata/oracle/app/oracle/oradata/test/sysaux01.dbf' to '/oradata/oracle/datafile/sysaux01.dbf'; Database altered. SQL>alter database rename file '/oradata/oracle/app/oracle/oradata/test/undotbs01.dbf' to '/oradata/oracle/datafile/undotbs01.dbf'; Database altered. SQL>alter database rename file '/oradata/oracle/app/oracle/oradata/test/redo01.log' to '/oradata/oracle/datafile/redo01.log'; Database altered. SQL>alter database rename file '/oradata/oracle/app/oracle/oradata/wind/redo02.log' to '/oradata/oracle/datafile/redo02.log'; Database altered. SQL>alter database rename file '/oradata/oracle/app/oracle/oradata/test/redo03.log' to '/oradata/oracle/datafile/redo03.log'; Database altered. SQL>alter database rename file '/oradata/oracle/app/oracle/oradata/test/standby01.log' to '/oradata/oracle/datafile/standby01.log'; Database altered. SQL>alter database rename file '/oradata/oracle/app/oracle/oradata/test/standby02.log' to '/oradata/oracle/datafile/standby02.log'; Database altered. SQL>alter database rename file '/oradata/oracle/app/oracle/oradata/test/standby03.log' to '/oradata/oracle/datafile/standby03.log'; Database altered. SQL>alter database rename file '/oradata/oracle/app/oracle/oradata/test/standby04.log' to '/oradata/oracle/datafile/standby04.log'; Database altered. SQL>alter database rename file '/oradata/oracle/app/oracle/oradata/test/temp01.dbf' to '/oradata/oracle/datafile/temp01.dbf'; Database altered.9.查询文件是否转换成功 SQL> select name from v$datafile; SQL> select member from v$logfile; SQL> select name from v$tempfile;10.打开数据库 SQL> alter database open; Database altered. |
CopyRight 2018-2019 实验室设备网 版权所有 |