57.oracle 11g移动数据文件
下面介绍移动oracle数据文件的两种方法
1.alter database方法
该方法,可以移动任何表空间的数据文件。
***关闭数据库*** SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. ***移动数据文件,用oracle用户操作*** [oracle@test ~]$ mv /u01/app/oracle/oradata/test/system01.dbf /oracledb/test/system01.dbf [oracle@test ~]$ mv /u01/app/oracle/oradata/test/sysaux01.dbf /oracledb/test/sysaux01.dbf [oracle@test ~]$ mv /u01/app/oracle/oradata/test/undotbs01.dbf /oracledb/test/undotbs01.dbf [oracle@test ~]$ mv /u01/app/oracle/oradata/test/users01.dbf /oracledb/test/users01.dbf [oracle@test ~]$ mv /u01/app/oracle/oradata/test/temp01.dbf /oracledb/test/temp01.dbf [oracle@test ~]$ mv /u01/app/oracle/oradata/test/redo03.log /oracledb/test/redo03.log [oracle@test ~]$ mv /u01/app/oracle/oradata/test/redo02.log /oracledb/test/redo02.log [oracle@test ~]$ mv /u01/app/oracle/oradata/test/redo01.log /oracledb/test/redo01.log ***启动到mount状态*** SQL> startup mount ORACLE instance started. Total System Global Area 1.0122E+10 bytes Fixed Size 2237088 bytes Variable Size 1610616160 bytes Database Buffers 8489271296 bytes Redo Buffers 19468288 bytes Database mounted. SQL> alter database rename file '/u01/app/oracle/oradata/test/system01.dbf' to '/oracledb/test/system01.dbf'; Database altered. SQL> alter database rename file '/u01/app/oracle/oradata/test/sysaux01.dbf' to '/oracledb/test/sysaux01.dbf'; Database altered. SQL> alter database rename file '/u01/app/oracle/oradata/test/undotbs01.dbf' to '/oracledb/test/undotbs01.dbf'; Database altered. SQL> alter database rename file '/u01/app/oracle/oradata/test/users01.dbf' to '/oracledb/test/users01.dbf'; Database altered. SQL> alter database rename file '/u01/app/oracle/oradata/test/temp01.dbf' to '/oracledb/test/temp01.dbf'; Database altered. SQL> alter database rename file '/u01/app/oracle/oradata/test/redo01.log' to '/oracledb/test/redo01.log'; Database altered. SQL> alter database rename file '/u01/app/oracle/oradata/test/redo02.log' to '/oracledb/test/redo02.log'; Database altered. SQL> alter database rename file '/u01/app/oracle/oradata/test/redo03.log' to '/oracledb/test/redo03.log'; Database altered. SQL> alter database open; Database altered. ***重启验证*** SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. Total System Global Area 1.0122E+10 bytes Fixed Size 2237088 bytes Variable Size 1610616160 bytes Database Buffers 8489271296 bytes Redo Buffers 19468288 bytes Database mounted. Database opened.
2.alter tablespace方法
该方法,不能移动system表空间,回滚段表空间和临时段表空间的数据文件
***offline system表空间时报错*** SQL> alter tablespace system offline; alter tablespace system offline * ERROR at line 1: ORA-01541: system tablespace cannot be brought offline; shut down if necessary 报错:说明system表空间不能offline ***由此说明一下system表空间的特性--不能脱机offline --不能置为只读read only --不能重命名 --不能删除 SQL> alter tablespace sysaux offline; Tablespace altered. [oracle@test ~]$ cp /oracledb/test/sysaux01.dbf /u01/app/oracle/oradata/test/sysaux01.dbf SQL> alter tablespace sysaux rename datafile '/oracledb/test/sysaux01.dbf' to '/u01/app/oracle/oradata/test/sysaux01.dbf'; Tablespace altered. SQL> alter tablespace sysaux online; Tablespace altered. ***offline UNDO表空间时报错*** SQL> alter tablespace UNDOTBS1 offline; alter tablespace UNDOTBS1 offline * ERROR at line 1: ORA-30042: Cannot offline the undo tablespace ***offline TEMP表空间时报错*** SQL> alter tablespace TEMP offline; alter tablespace TEMP offline * ERROR at line 1: ORA-03217: invalid option for alter of TEMPORARY TABLESPACE #把需要移动的数据文件对应的表空间offline SQL> alter tablespace USERS offline; Tablespace altered. #移动数据文件至目标位置 [oracle@test ~]$ cp /oracledb/test/users01.dbf /u01/app/oracle/oradata/test/users01.dbf #修改表空间中数据文件的位置 SQL> alter tablespace USERS rename datafile '/oracledb/test/users01.dbf' to '/u01/app/oracle/oradata/test/users01.dbf'; Tablespace altered. #把表空间online SQL> alter tablespace users online; Tablespace altered.
3.总结
- alter database方法可以移动任何表空间的数据文件,但其要求数据库必须处于mount状态,故此种方法更适合做整体数据库的迁移。
- alter tablespace方法需要数据库处于open状态,表空间在offline的状态下才可更改。但其不能移动system表空间,undo表空间和temp表空间的数据文件,故此种方法更适合于做用户数据文件的迁移。
转载于:https://blog.csdn.net/snowYing97/article/details/52576028