Renaming and moving ____oracle for various Oracle files

Recently in the case of a bank database migration and escalation, as the system for the statistical run batch, the original system configuration has been unable to meet the performance requirements of running batches, it is required to buy new mainframe with SSD and HD two kinds of hybrid storage, intermediate process table corresponding table space into the SSD corresponding directory, The history results table is placed in the HD counterpart directory. The data files need to be moved and renamed during the migration process. The following is a search from the Internet article, a more detailed test. Record the following

At the same time, migrating from Oracle 11.2.0.4 to 12.2.0.01 focused on the Oracle 12c In_memory features. It is very helpful to the performance improvement of running batch. I hereby record

Here are two ways to move Oracle data files. 1.alter Database Method

This method allows you to move data files from any table space.

Close Database * * * sql> shutdown immediate db closed.
Database dismounted.

ORACLE instance shut down. Move data files, operate with Oracle user * * [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 * * * Boot to mount State * * * 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 mou

nted.

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.
Reboot Verification * * * * 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 by
TEs database buffers 8489271296 bytes Redo buffers 19468288 Database bytes.
 Database opened.

2.alter Tablespace Method

This method cannot move the system table space, rolling back the data files of the segment Tablespace and the temporary segment table space.

Offline system table Space times wrong * * * * sql> alter TABLESPACE system offline; Alter TABLESPACE system offline * ERROR at line 1:ora-01541:system Tablespace cannot is brought offline; Shut down if necessary error: Description system tablespace can not offline * * * This explains the characteristics of the system tablespace--not offline offline--cannot be set to read-only re Ad only--cannot rename--cannot delete sql> alter tablespace Sysaux Offlin

E

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 table Space Times Wrong * * * * sql> alter tablespace UNDOTBS1 offline; Alter tablespace UNDOTBS1 offline * error at line 1:ora-30042:cannot offline the undo tablespace ***offline Temp Table space times wrong *
* * sql> alter tablespace TEMP offline; Alter Tablespace TEMPOffline * ERROR at line 1:ora-03217:invalid option for alter of temporary tablespace #把需要移动的数据文件对应的表空间offline sql> A

Lter 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. SummaryThe ALTER DATABASE method can move data files in any tablespace, but it requires the database to be mount, so the method is more suitable for migrating the whole database. The alter Tablespace method requires the database to be open and the tablespace can be changed in a offline state. However, it cannot move the system table space, undo table space and temp table space data files, so the method is more suitable for user data file migration. Original address: http://blog.csdn.net/snowying97/article/details/52576028

The original author: Wind Summer Snow Yi