大量数据文件恢复时,set new…
来源:互联网 发布:bilibili直播点歌软件 编辑:程序博客网 时间:2024/06/15 07:55
需要把大量数据文件恢复到新的目录(如RMAN异机恢复),可以设置DB_CREATE_FILE_DEST参数,使用OMF自动生成文数据文件名,比拼SQL还要简单快速
set newname for datafile * to NEW;
参考文档:
Goal
--------------------------------------------------------------------------------
Applies to:
Oracle Database - Standard Edition - Version 10.1.0.2 andlater
Oracle Database - Enterprise Edition - Version 9.0.1.2 andlater
Information in this document applies to any platform.
***Checked for relevance on 29-Apr-2013***
Goal
When you must restore the database the same directory structure isnot always there. If you have a database containing 1000s ofdatafiles it can be very tedious to setup the set newname commandsfor all the datafiles.
Using sqlplus we can extract the information we need into a filewhich can then be easily modifed and executed as an RMAN script tocomplete the task.
Solution
You are restoring or duplicating the target database to a new hostusing RMAN. The datafiles are not OMF files and you want to makethem OMF. Using 'set newname for datafile to NEW' will generate anew OMF filename for the restored datafile.
This will avoid the manual entry or vi/notepad editing of similaroutput. Using this output the datafiles will be restored to theDB_CREATE_FILE_DEST. If this parameter is not set you must add thecorrect path as in '/path/NEW' will direct the files to the newlocation and give an OMF filename.
sqlplus /nolog
connect system/manager
set echo off pages 0 feed off sqlp #
spool /path/setnewnamedf.lst
select 'set newname for datafile '||file#||' to NEW;' fromv$datafile;
-- select 'set newname for datafile '||file#||' to /newpath/NEW;'from v$datafile;
spool offThere are 2 select statements above with slightlydifferent output.
Select #1 Output:
set newname for datafile 1 to NEW;
set newname for datafile 2 to NEW;
set newname for datafile 3 to NEW;
set newname for datafile 4 to NEW;
set newname for datafile 5 to NEW;
Select #2 Output:
set newname for datafile 1 to /newpath/NEW;
set newname for datafile 2 to /newpath/NEW;
set newname for datafile 3 to /newpath/NEW;
set newname for datafile 4 to /newpath/NEW;
set newname for datafile 5 to /newpath/NEW;
In this scenario you do not use OMF naming for your files and youwant to continue to control the datafile names. To generate setnewname commands to point to an ASM volume execute the sql below.It will create a file that you just add your restore command tocomplete the script and execute in RMAN inside a runblock.
sqlplus /nolog
connect system/manager
set echo off pages 0 feed off sqlp #
spool /path/setnewnamedf.lst
select 'set newname for datafile '||file#||' to ''+DG'';' fromv$datafile;
spool offSelect #3 Output:
set newname for datafile 1 to '+DG';
set newname for datafile 2 to '+DG';
set newname for datafile 3 to '+DG';
set newname for datafile 4 to '+DG';
set newname for datafile 5 to '+DG';
With the following query you keep the same path and name as on theoriginal target. Using vi global search and replace you changechange the path to the new directory using %s. This becomes veryuseful when there are 1000s of files to update. If using multipledirectories you can split the output to change the path all in afile then merge the files or find a quicker method. Here is theexample output of before and after the search andreplace.
set echo off pages 0 feed off sqlp #
spool setnewnamedf.lst
select 'set newname for datafile '||file#||' to '''||name||''';'from v$datafile;
spool offSelect #4 Output: Before change:
set newname for datafile 1 to'/u01/64bit/app/oracle/oradata/V102REP1/system01.dbf';
set newname for datafile 2 to'/u01/64bit/app/oracle/oradata/V102REP1/undotbs01.dbf';
set newname for datafile 3 to'/u01/64bit/app/oracle/oradata/V102REP1/sysaux01.dbf';
set newname for datafile 4 to'/u01/64bit/app/oracle/oradata/V102REP1/users01.dbf';
set newname for datafile 5 to'/u01/64bit/app/oracle/oradata/V102REP1/fujitsu_1.dbf';
Command to change the above path:
set newname for datafile 1 to '/newpath/system01.dbf';
set newname for datafile 2 to '/newpath/undotbs01.dbf';
set newname for datafile 3 to '/newpath/sysaux01.dbf';
set newname for datafile 4 to '/newpath/users01.dbf';
set newname for datafile 5 to '/newpath/fujitsu_1.dbf';
Using these methods, or something similar with other editors,you can quickly setup a restore or duplicate script to be used on anew host and directory structure no matter the number of files.
--------------------------------------------------------------------------------
- 大量数据文件恢复时,set new…
- RMAN 恢复实际操作(模拟数据文件…
- oracle11g 数据文件误删恢复(…
- MAN 恢复实际操作(模拟数据文件丢…
- 恢复Set unused的字段数据
- oracle 只有数据文件时的恢复
- mysql 数据恢复 案例: “恢复/导入…
- set unused column column_name 和…
- GVF - a new snake algorithm for …
- 关于 adding a new disk or removi…
- Facebook's New Realtime Analytic…
- Lesson 93 Our new…
- label = new JLabe…
- Call a New Standa…
- RMAN恢复数据文件
- 重建数据文件恢复
- oracle数据文件恢复
- 数据文件损坏恢复
- 盘点史上最严重的的十大黑客袭击事件
- ORA-03137: TTC …
- ORA-29702: error occur…
- Solaris下用date获得前一天日期
- 策略模式
- 大量数据文件恢复时,set new…
- Java进阶--深入理解Java垃圾回收机制
- RMAN-06054 report duri…
- RMAN-6556 datafile %s&…
- sqlserver 连接oracle 配置详解 .
- [Guava源码日报](3)Joiner分析
- 监控索引有没有用及碎片整理
- 利用sys.sysprocesses检查SqlServe…
- AndroidStudio中查找和设置函数注释的快捷键