relocate and rename datafile 方法、示例

        对于rename datafile方法有两种，可以分为
        alter database  rename file ... to ...
        alter tablespace ... rename datafile ... to ...
        关于这两种方法，官网链接如下
http://docs.oracle.com/cd/E11882_01/server.112/e25494/tspaces.htm#ADMIN12490

---

 一 . alter database rename file  ... to ...方法、示例

这种方法适用于所有的数据文件，包含system数据文件
需要注意的是，
        rename到目标位置的数据文件，必须手动拷贝到目标位置， alter database  rename file ... to ...命令不会去创建文件。
步骤如下

     To rename datafiles of several tablespaces in one operation or to rename 

datafiles of the SYSTEM tablespace, you must have the ALTER DATABASE system

privilege. 

1 .Ensure that the database is mounted but closed. 

2.Copy the datafiles to be renamed to their new locations and new names, using  operating system commands. 

3.Make sure the new copies of the datafiles have different fully specified  filenames from the datafiles currently in use. 

4.Use the SQL statement ALTER DATABASE to rename the file pointers in the  database's control file. 

5 .Open the database.

6.Back up the database. After making any structural changes to a database, always perform an immediate and complete backup
       
示例如下
变更前检查数据文件所在路径

1. > select file_name,tablespace_name from dba_data_files;
   
2. 
   
3. FILE_NAME TABLESPACE_NAME
   
4. -------------------------------------------------------------------------------- ------------------------------
   
5. /s01/oracle/app/oracle/oradata/test/system01.dbf SYSTEM
   
6. /s01/oracle/app/oracle/oradata/test/sysaux01.dbf SYSAUX
   
7. /s01/oracle/app/oracle/oradata/test/undotbs01.dbf UNDOTBS1
   
8. /s01/oracle/app/oracle/oradata/test/users01.dbf USERS
   
9. /s01/oracle/app/oracle/oradata/test/users02.dbf USERS

1. 确认当前数据库处于closed但mount状态

1. > shutdown immediate
   
2. Database closed.
   
3. Database dismounted.
   
4. ORACLE instance shut down.
   
5. > startup mount;
   
6. ORACLE instance started.
   
7. 
   
8. Total System Global Area 839282688 bytes
   
9. Fixed Size 2257880 bytes
   
10. Variable Size 784337960 bytes
    
11. Database Buffers 50331648 bytes
    
12. Redo Buffers 2355200 bytes
    
13. Database mounted.

2. 手动将数据文件拷贝到新的位置，示例中将     
    / s01 / oracle / app / oracle / oradata / test / users02 . dbf 和 / s01 / oracle / app / oracle / oradata / test / temp01.dbf  
    rename 到  / home / oracle / user . dbf和/home/oracle/temp.dbf
3. 确认已经将数据文件拷贝到目标位置

1. [oracle@uumile ~]$ cd /s01/oracle/app/oracle/oradata/test/
   
2. [oracle@uumile test]$ cp users02.dbf /home/oracle/user.dbf
3. [oracle@uumile test]$ cp temp01.dbf /home/oracle/temp.dbf
4. [oracle@uumile ~]$ cd /home/oracle
5. [oracle@uumile ~]$ ls -l *.dbf
   
6. -rw-r----- 1 oracle oinstall 20979712 Feb 26 13:40 temp.dbf
   
7. -rw-r----- 1 oracle oinstall 10493952 Feb 26 13:39 user.dbf

4. 使用alter database rename file ... to ...命令修改控制文件中的数据文件路径指向。

1. > alter database rename file \'/s01/oracle/app/oracle/oradata/test/temp01.dbf\',
   
2.   2          '/s01/oracle/app/oracle/oradata/test/users02.dbf\'
   
3.   3      to    '/home/oracle/temp.dbf\',
   
4.   4             '/home/oracle/user.dbf\';
   
5. 
   
6. Database altered.

5. open database 

1. > alter database open;
   
2. 
   
3. Database altered.
   
4. 
   
5. > select file_name,tablespace_name from dba_data_files;
   
6. 
   
7. FILE_NAME TABLESPACE_NAME
   
8. -------------------------------------------------------------------------------- ------------------------------
   
9. /s01/oracle/app/oracle/oradata/test/system01.dbf SYSTEM
   
10. /s01/oracle/app/oracle/oradata/test/sysaux01.dbf SYSAUX
    
11. /s01/oracle/app/oracle/oradata/test/undotbs01.dbf UNDOTBS1
    
12. /s01/oracle/app/oracle/oradata/test/users01.dbf USERS
    
13. /home/oracle/user.dbf USERS
    
14. 
    
15. > select file_name,tablespace_name from dba_temp_files;
    
16. 
    
17. FILE_NAME TABLESPACE_NAME
    
18. -------------------------------------------------------------------------------- ------------------------------
    
19. /home/oracle/temp.dbf TEMP

6. 备份全库。

二 . alter tablespace XXX rename datafile ... to ...方法、示例

这种方法适用于非system数据文件的rename，
这种方法同样需要手动拷贝数据文件到目标位置，alter tablespace XXX rename datafile ... to ... 命令不会去自动创建文件。
步骤如下

1.Take the non-SYSTEM tablespace that contains the datafiles offline. The database must be open
   
2.Copy the datafiles to the new location or new names using operating system 
commands. 
   
3.Make sure that the new, fully specified filenames are different from the old 
filenames. 
   
4.Use the SQL statement ALTER TABLESPACE with the RENAME DATAFILE option to 
change the filenames within the database. 

5.Bring the tablespace online

6. Back up the database. After making any structural changes to a database, always perform an immediate and complete backup

示例
rename前数据文件路径检查

1. > select file_name,tablespace_name from dba_data_files;
   
2. 
   
3. FILE_NAME TABLESPACE_NAME
   
4. -------------------------------------------------------------------------------- ------------------------------
   
5. /s01/oracle/app/oracle/oradata/test/system01.dbf SYSTEM
   
6. /s01/oracle/app/oracle/oradata/test/sysaux01.dbf SYSAUX
   
7. /s01/oracle/app/oracle/oradata/test/undotbs01.dbf UNDOTBS1
   
8. /s01/oracle/app/oracle/oradata/test/users01.dbf USERS
   
9. /home/oracle/user.dbf USERS

1. 需要rename的数据文件所在的tablespace offline

1. > alter tablespace users offline normal;
   
2. 
   
3. Tablespace altered.

2. 手动拷贝数据文件到目标位置

1. [oracle@uumile ~]$ pwd
   
2. /home/oracle
   
3. [oracle@uumile ~]$ cp user.dbf /s01/oracle/app/oracle/oradata/test/users02.dbf

3. 确认数据文件已经拷贝成功

1. [oracle@uumile ~]$ cd /s01/oracle/app/oracle/oradata/test/
   
2. [oracle@uumile test]$ ls users02.dbf
   
3. users02.dbf

4. 使用alter tablespace 命令rename datafile 到目标位置

1. > alter tablespace users rename datafile  '/home/oracle/user.dbf' to
   
2.   2 '/s01/oracle/app/oracle/oradata/test/users02.dbf';
   
3. 
   
4. Tablespace altered.

5. 将目标表空间online，并检查数据文件路径

1. > alter tablespace users online;
   
2. 
   
3. Tablespace altered.
   
4. 
   
5. > select file_name,tablespace_name from dba_data_files;
   
6. 
   
7. FILE_NAME TABLESPACE_NAME
   
8. -------------------------------------------------------------------------------- ------------------------------
   
9. /s01/oracle/app/oracle/oradata/test/system01.dbf SYSTEM
   
10. /s01/oracle/app/oracle/oradata/test/sysaux01.dbf SYSAUX
    
11. /s01/oracle/app/oracle/oradata/test/undotbs01.dbf UNDOTBS1
    
12. /s01/oracle/app/oracle/oradata/test/users01.dbf USERS
    
13. /s01/oracle/app/oracle/oradata/test/users02.dbf USERS

6. 备份全库。



这里需要注意的是，temporary tablespace 是不能offline的，但是temporary tablespace的tempfile可以offline。
官网链接如下
http://docs.oracle.com/cd/E11882_01/server.112/e25494/tspaces.htm#ADMIN11369

        

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28697282/viewspace-1441916/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28697282/viewspace-1441916/