使用 SET NEWNAME 切换文件（相当于copy）

1.创建测试表空间，数据文件放在/home/oracle/oradata/目录下
[oracle@host01 ~]$ mkdir oradata
[oracle@host01 ~]$ cd oradata/
[oracle@host01 oradata]$ pwd
/home/oracle/oradata

创建表空间
SYS@PROD1>create tablespace ts_setnewname datafile '/home/oracle/oradata/ts_setnew01.dbf' size 10m;

Tablespace created.

SYS@PROD1>select file_name from dba_data_files where tablespace_name='TS_SETNEWNAME';

FILE_NAME
--------------------------------------------------------------------------------
/home/oracle/oradata/ts_setnew01.dbf

SYS@PROD1>ed 001
run{
ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
SQL "ALTER TABLESPACE TS_SETNEWNAME OFFLINE IMMEDIATE";
SET NEWNAME FOR DATAFILE '/home/oracle/oradata/ts_setnew01.dbf' TO '/u01/app/oracle/oradata/PROD1/ts_setnew01.dbf';
RESTORE TABLESPACE TS_SETNEWNAME;
SWITCH DATAFILE ALL;
RECOVER TABLESPACE TS_SETNEWNAME;
SQL "ALTER TABLESPACE TS_SETNEWNAME ONLINE";
}

[oracle@host01 ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Wed Dec 21 11:25:50 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database: PROD1 (DBID=2148633869)

RMAN> run{
2> ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
3> SQL "ALTER TABLESPACE TS_SETNEWNAME OFFLINE IMMEDIATE";
4> SET NEWNAME FOR DATAFILE '/home/oracle/oradata/ts_setnew01.dbf' TO '/u01/app/oracle/oradata/PROD1/ts_setnew01.dbf';
5> RESTORE TABLESPACE TS_SETNEWNAME;
6> SWITCH DATAFILE ALL;
7> RECOVER TABLESPACE TS_SETNEWNAME;
8> SQL "ALTER TABLESPACE TS_SETNEWNAME ONLINE";
9> }

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=17 device type=DISK

sql statement: ALTER TABLESPACE TS_SETNEWNAME OFFLINE IMMEDIATE

executing command: SET NEWNAME

Starting restore at 21-DEC-16

creating datafile file number=6 name=/u01/app/oracle/oradata/PROD1/ts_setnew01.dbf
restore not done; all files read only, offline, or already restored
Finished restore at 21-DEC-16

datafile 6 switched to datafile copy
input datafile copy RECID=8 STAMP=931173953 file name=/u01/app/oracle/oradata/PROD1/ts_setnew01.dbf

Starting recover at 21-DEC-16

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 21-DEC-16

sql statement: ALTER TABLESPACE TS_SETNEWNAME ONLINE
released channel: c1

3.验证
SYS@PROD1>select file_name from dba_data_files where tablespace_name='TS_SETNEWNAME';

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD1/ts_setnew01.dbf

SYS@PROD1>! ls /u01/app/oracle/oradata/PROD1/ts_setnew01.dbf
/u01/app/oracle/oradata/PROD1/ts_setnew01.dbf

SYS@PROD1>! ls /home/oracle/oradata/ts_setnew01.dbf
/home/oracle/oradata/ts_setnew01.dbf

SYS@PROD1>! rm /home/oracle/oradata/ts_setnew01.dbf

总结：在rman下SET NEWNAME，就是将其他目录下的同名数据文件复制到原目录下去，利用的是RMAN工具！
切换完毕后，原目录下还有数据文件，删除即可。

ITPub博客

【备份恢复】set newname切换日志

最新文章