点击上方蓝色字关注我们~ 先从Online Move Datafile说起，Online Move Datafile是12.1的新特性，在12c之前如果想要move datafile通常需要下列步骤： 1.offline datafile 2.backup as copy数据文件到指定路径 3.switch datafile to copy切换控制文件中数据文件路径 4.由于offline datafile 不会做检查点，所以在online之前需要recover datafile 5.online datafile Oracle 12.1推出了Online Move Datafile的新特性，那么Oracle 10046跟踪Online Move Datafile的会话，strace跟踪dbwr进程。 该步骤是Online Move Datafile的结束动作。
需要修改的数据文件块特别多时，可以用我写的以下脚本为bbed提供批量修改数据 通过名字过滤出仅含datafile的文件 (此处仅以system文件举例) ll /data/|grep system|awk '{print $9}' >datafile_name.txt 准备数据块偏移量 cat bbed_repair_data.sh #! rm -rf /home/oracle/rm_old_asm_file cp /home/oracle/datafile_name.txt /home/oracle/order_datafile_name.txt cp /home/oracle/datafile_name.txt /home/oracle/rm_old_asm_file i=1 cat /home/oracle/datafile_name.txt ，之后将bbed修复后的新datafile文件传过来既可 AFsI3eekARAEGAAg1cy88wUo0pvBtwEwhAc49AM!
2核2G云服务器 每月9.33元起，个人开发者专属3年机 低至2.3折
黄廷忠（网名：认真就输） 云和恩墨技术专家 个人博客：http://www.htz.pw/ resize数据文件的时候，常常会报ORA-03297错误，也许很多...
在备库查看： select file#,df.name,df.ts#,ts.name,df.RFILE# from v$datafile df,v$tablespace ts where df.ts (文档 ID 5623467.8) 这个bug，oracle也没有给出其它可行的意见，除了升级打补丁外，建议就是不要使用drop datafile的命令，但是我已经运行了，你说怎么办，只能重建备库了 既然数据字典中不同步，对于drop的操作不支持，我就直接使用alter database datafile ‘xxxxxx' offline drop来搞定这个问题，上次的MRP的问题在11g中就可以这么解决 SQL> alter database datafile '/U01/app/oracle/oradata/test/test_new_index04.dbf' offline drop; Database 命令运行成功了，但是查看datafile还是没有发生变化。
9 /datafile/dbs_data9.dbf 10 /datafile/dbs_data10.dbf 11 /datafile/dbs_data11.dbf 11 9 format '/oradata/jy/datafile/dbs_data9.dbf'; backup as copy datafile 10 format '/oradata/jy/datafile > switch datafile 9,10,11 to copy; datafile 9 switched to datafile copy "/oradata/jy/datafile/dbs_data9 .dbf" datafile 10 switched to datafile copy "/oradata/jy/datafile/dbs_data10.dbf" datafile 11 switched datafile copy datafile copy filename=/datafile/data10.dbf recid=14 stamp=975320371 deleted datafile
/lhrdb/datafile/users.445.916601927 ONLINE 5 +DATA/lhrdb/datafile/example SYS@lhrdb> ALTER DATABASE DATAFILE 6 OFFLINE; ALTER DATABASE DATAFILE 6 OFFLINE * ERROR at line 1: ORA /lhrdb/datafile/users.445.916601927 ONLINE 5 +DATA/lhrdb/datafile/example SYS@lhrdb> ALTER DATABASE DATAFILE 6 ONLINE; alter database datafile 6 online * ERROR at line 1: ORA- /lhrdb/datafile/users.445.916601927 ONLINE 5 +DATA/lhrdb/datafile/example
datafile 00002: +DATA/mynas/datafile/sysaux.257.951608183 destination for restore of datafile 00003: +DATA/mynas/datafile/undotbs1.259.951608185 destination for restore of datafile 00004: +DATA/mynas/datafile of datafile 00008: +DATA/mynas/datafile/test.264.951608185 destination for restore of datafile 00009 : +DATA/mynas/datafile/test2.260.951608185 destination for restore of datafile 00010: +DATA/mynas/datafile datafile 00002: +DATA/mynas/datafile/sysaux.257.951608183 destination for restore of datafile 00003:
## asm转换成平面文件 方法一 (rman)： RMAN> convert datafile '+DATA/oracleasm/datafile/test.366.88888' format'/home ' offline; rman> convert datafile '/home/oracle/file/system' format '+DATA/DATAFILE/system.777.93459 datafile '+DATA/DATAFILE/system.777.93459'; --- sql> alter database datafile '+DATA/DATAFILE datafile 67; --alter database open; --3. online datafile重命名需先offline datafile -- offline datafile ); -- offline datafile 方法二： select 'alter database datafile ' ||file# ||' offline drop;' from v$datafile_header
" datafile 2 switched to datafile copy "+DATA/mynas/datafile/sysaux.257.951608183" datafile 3 switched to datafile copy "+DATA/mynas/datafile/undotbs1.259.951608185" datafile 4 switched to datafile copy "+DATA/mynas/datafile/users.265.951608205" datafile 5 switched to datafile copy "+DATA/mynas/datafile to datafile copy "+DATA/mynas/datafile/test.264.951608185" datafile 9 switched to datafile copy "+DATA /mynas/datafile/test2.260.951608185" datafile 10 switched to datafile copy "+DATA/mynas/datafile/dbs_d_hank
name=+DATA/jingyudb/datafile/pt_data.1 input datafile file number=00003 name=+DATA/jingyudb/datafile name=+DATA/jingyudb/datafile/pt_indx.1 input datafile file number=00005 name=+DATA/jingyudb/datafile name=+DATA/jingyudb/datafile/pt_data.2 input datafile file number=00002 name=+DATA/jingyudb/datafile name=+DATA/jingyudb/datafile/pt_data.1 input datafile file number=00003 name=+DATA/jingyudb/datafile name=+DATA/jingyudb/datafile/pt_indx.1 input datafile file number=00005 name=+DATA/jingyudb/datafile/
数据库是归档模式，那么我们实际可以利用rman的backup as copy datafile xxx format ''以及 switch datafile xxx to copy来操作，这样来解决是最简单且最不容易误操作的 /system.257.954241433 3 +DATA/NEWDB1/DATAFILE/sysaux.258.954241497 4 +DATA/NEWDB1/DATAFILE copy input datafile file number=00015 name=/tmp/user02.dbf output file name=+DATA/NEWDB1/DATAFILE/users to datafile copy "+DATA/NEWDB1/DATAFILE/users.279.961935881" 5.恢复15号数据文件 RMAN> recover datafile 15; DATAFILE/users.279.961935881 9 +DATA/NEWDB1/DATAFILE/undotbs2.269.954241841 6 rows selected
SQL> create tablespace pandy datafile '+DG_DAT/zxnora/datafile/pandy_dat_01.dbf' size 200m autoextend SQL> alter tablespace pandy add datafile '+DG_DAT/zxnora/datafile/pandy_dat_02.db' size 200m autoextend DATAFILE MIRROR COARSE JAN 20 12:00:00 Y SYSAUX.257.933178281 DATAFILE MIRROR COARSE JAN DATAFILE MIRROR COARSE JAN 17 16:00:00 Y UNDOTBS2.271.933178505 DATAFILE MIRROR COARSE SQL> alter database rename file '+DG_DAT/zxnora/datafile/pandy_dat_02.db' to '+DG_DAT/zxnora/datafile
1、系统正常关闭： system scn=datafile scn=start scn=stop scn 1)system scn=datafile scn=start scn，不需要介质恢复 2)stopscn not null,不需要实例恢复 2、系统异常关闭： system scn=datafile scn=start scn，stop scn null 1)system scn=datafile scn 1)system scn=datafile scn>start scn，需要介质恢复成system scn=datafile scn=start scn 2)stopscn null,需要实例恢复，not scn=datafile scn<=start scn，需要使用using backup controlfile介质恢复成system scn=datafile scn=start scn=current 1)current log scn=system scn=datafile scn>=start scn，需要介质恢复成system scn=datafile scn=start scn=redolog
/system01.dbf datafile 2 switched to datafile copy input datafile copy RECID=7 STAMP=984353142 file name =/u01/app/oradata/ora235/sysaux01.dbf datafile 3 switched to datafile copy input datafile copy RECID= datafile copy datafile copy file name=/data/oracle/data/lottu01.dbf RECID=4 STAMP=984353151 datafile /sysaux01.dbf datafile 3 switched to datafile copy input datafile copy RECID=2 STAMP=984353151 file name =/u01/app/oradata/ora235/undotbs01.dbf datafile 4 switched to datafile copy input datafile copy RECID
dbs_d_test add datafile; --这里是12 alter tablespace dbs_d_test add datafile; --这里是13 alter database datafile datafile copy input datafile fno=00012 name=+ZHAOJINGYU/jy/datafile/dbs_d_test.613.985387391 channel c3: starting datafile copy input datafile fno=00013 name=+ZHAOJINGYU/jy/datafile/dbs_d_test.611.985387395 full datafile backupset channel c2: specifying datafile(s) in backupset input datafile fno=00012 name : starting compressed full datafile backupset channel c3: specifying datafile(s) in backupset input datafile
/system01.dbf datafile 3 switched to datafile copy input datafile copy RECID=5 STAMP=1070263317 file name=/oradata/ORCL_STBY/sysaux01.dbf datafile 4 switched to datafile copy input datafile copy RECID=6 /sysaux01.dbf datafile 7 switched to datafile copy input datafile copy RECID=9 STAMP=1070263318 file name=/oradata/ORCL_STBY/users01.dbf datafile 8 switched to datafile copy input datafile copy RECID=10 _.dbf datafile 10 switched to datafile copy input datafile copy RECID=12 STAMP=1070263318 file name=/
恢复过程中的一些关键性的命令如下所示： CONVERT DATAFILE '+DATA1/oralhrs/datafile/tbs101.262.923139373' FORMAT '/tmp/tbs101 SYS@oraLHRDG2> ALTER DATABASE DATAFILE 64 ONLINE; alter database datafile 64 online * ERROR at line 1 file name=+DATA1/oralhrs/datafile/tbs101.262.923139373 converted datafile=/tmp/tbs101.dbf_bk channel 1.5761E+10 OFFLINE SYS@oraLHRDG2> ALTER DATABASE DATAFILE 64 ONLINE; ALTER DATABASE DATAFILE 64 ONLINE '+DATA1/oralhrsg/datafile/tbs101.483.923151901' TO '+DATA1/oralhrsg/datafile/tbs101.382.923151215' *
/datafile/undotbs03.dbf destination for restore of datafile 00019: /data/jieket4dg/datafile/prod_scfop_tbs of datafile 00003: /data/jieket4dg/datafile/undotbs1.270.1016468699 destination for restore of datafile /datafile/jieke_dat.256.1016468521 destination for restore of datafile 00008: /data/jieket4dg/datafile for restore of datafile 00016: /data/jieket4dg/datafile/undotbs02.dbf destination for restore of datafile /datafile/jieke_data.309.1067980347 destination for restore of datafile 00025: /data/jieket4dg/datafile
/CDB2/system01.dbf datafile 3 switched to datafile copy input datafile copy RECID=16 STAMP=1048971255 file name=/opt/oracle/oradata/CDB2/sysaux01.dbf datafile 4 switched to datafile copy input datafile /system01.dbf datafile 6 switched to datafile copy input datafile copy RECID=19 STAMP=1048971255 file /undotbs01.dbf datafile 9 switched to datafile copy input datafile copy RECID=22 STAMP=1048971255 file /CDB2/ORCLPDB1/undotbs01.dbf datafile 12 switched to datafile copy input datafile copy RECID=25 STAMP
扫码关注腾讯云开发者
领取腾讯云代金券
云服务器
网站备案
对象存储
云直播
实时音视频
