Oracle：数据文件无备份的情况下的RECOVER
今天遇到了一个数据文件的问题，刚刚找资料的时候看到了MaClean的一篇文档，尝试了下，是可行的，希望对解决我的问题有所启示。
具体的操作如下所示：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 |
SQL> select file_id,file_name,tablespace_name,online_status,status,bytes/1024/1024 "MB" from dba_data_files; FILE_ID FILE_NAME TABLESPACE_NAME ONLINE_ STATUS MB ---------- --------------------------------------------- ------------------------------ ------- --------- ---------- 4 /oradata/orcl/users01.dbf USERS ONLINE AVAILABLE 5 3 /oradata/orcl/sysaux01.dbf SYSAUX ONLINE AVAILABLE 250 2 /oradata/orcl/undotbs01.dbf UNDOTBS1 ONLINE AVAILABLE 35 1 /oradata/orcl/system01.dbf SYSTEM SYSTEM AVAILABLE 480 5 /oradata/orcl/example01.dbf EXAMPLE ONLINE AVAILABLE 100 6 /oradata/orcl/adamhuan01.dbf ADAMHUAN ONLINE AVAILABLE 200 7 /oradata/orcl/adamhuan02.dbf ADAMHUAN ONLINE AVAILABLE 200 8 /oradata/orcl/adamhuan03.dbf ADAMHUAN ONLINE AVAILABLE 200 9 /oradata/orcl/adamhuan04.dbf ADAMHUAN ONLINE AVAILABLE 200 9 rows selected. SQL> SQL> alter database datafile '/oradata/orcl/adamhuan04.dbf' offline; Database altered. SQL> SQL> select file_id,file_name,tablespace_name,online_status,status,bytes/1024/1024 "MB" from dba_data_files; FILE_ID FILE_NAME TABLESPACE_NAME ONLINE_ STATUS MB ---------- --------------------------------------------- ------------------------------ ------- --------- ---------- 4 /oradata/orcl/users01.dbf USERS ONLINE AVAILABLE 5 3 /oradata/orcl/sysaux01.dbf SYSAUX ONLINE AVAILABLE 250 2 /oradata/orcl/undotbs01.dbf UNDOTBS1 ONLINE AVAILABLE 35 1 /oradata/orcl/system01.dbf SYSTEM SYSTEM AVAILABLE 480 5 /oradata/orcl/example01.dbf EXAMPLE ONLINE AVAILABLE 100 6 /oradata/orcl/adamhuan01.dbf ADAMHUAN ONLINE AVAILABLE 200 7 /oradata/orcl/adamhuan02.dbf ADAMHUAN ONLINE AVAILABLE 200 8 /oradata/orcl/adamhuan03.dbf ADAMHUAN ONLINE AVAILABLE 200 9 /oradata/orcl/adamhuan04.dbf ADAMHUAN RECOVER AVAILABLE 9 rows selected. SQL> SQL> !ls -l /oradata/orcl/adamhuan04.dbf -rw-r----- 1 oracle oinstall 209723392 Jul 7 05:25 /oradata/orcl/adamhuan04.dbf SQL> !mv /oradata/orcl/adamhuan04.dbf /oradata/orcl/delete-adamhuan04.dbf-delete SQL> !ls -l /oradata/orcl/ total 1886996 -rw-r----- 1 oracle oinstall 209723392 Jul 7 05:25 adamhuan01.dbf -rw-r----- 1 oracle oinstall 209723392 Jul 7 05:25 adamhuan02.dbf -rw-r----- 1 oracle oinstall 209723392 Jul 7 05:25 adamhuan03.dbf -rw-r----- 1 oracle oinstall 7061504 Jul 7 05:28 control01.ctl -rw-r----- 1 oracle oinstall 7061504 Jul 7 05:28 control02.ctl -rw-r----- 1 oracle oinstall 7061504 Jul 7 05:28 control03.ctl -rw-r----- 1 oracle oinstall 209723392 Jul 7 05:25 delete-adamhuan04.dbf-delete -rw-r----- 1 oracle oinstall 104865792 Jul 7 05:25 example01.dbf -rw-r----- 1 oracle oinstall 52429312 Jul 7 05:25 redo01.log -rw-r----- 1 oracle oinstall 52429312 Jul 7 05:25 redo02.log -rw-r----- 1 oracle oinstall 52429312 Jul 7 05:28 redo03.log -rw-r----- 1 oracle oinstall 262152192 Jul 7 05:25 sysaux01.dbf -rw-r----- 1 oracle oinstall 503324672 Jul 7 05:25 system01.dbf -rw-r----- 1 oracle oinstall 20979712 Jun 17 22:00 temp01.dbf -rw-r----- 1 oracle oinstall 36708352 Jul 7 05:25 undotbs01.dbf -rw-r----- 1 oracle oinstall 5251072 Jul 7 05:25 users01.dbf SQL> SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 11 Next log sequence to archive 13 Current log sequence 13 SQL> SQL> show parameter db_recovery_file_dest NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string /oradata/flash_recovery_area db_recovery_file_dest_size big integer 2G SQL> SQL> !ls -l /oradata/flash_recovery_area total 4 drwxr-x--- 4 oracle oinstall 4096 Jun 17 21:42 ORCL SQL> !ls -l /oradata/flash_recovery_area/ORCL total 12 drwxr-x--- 5 oracle oinstall 4096 Jul 7 05:18 archivelog drwxr-x--- 3 oracle oinstall 4096 Jun 17 21:27 backupset -rw-r--r-- 1 oracle oinstall 2259 Jun 17 21:59 sqlnet.log SQL> !ls -l /oradata/flash_recovery_area/ORCL/archivelog total 12 drwxr-x--- 2 oracle oinstall 4096 Jun 17 23:18 2015_06_17 drwxr-x--- 2 oracle oinstall 4096 Jun 23 06:00 2015_06_23 drwxr-x--- 2 oracle oinstall 4096 Jul 7 05:18 2015_07_07 SQL> !tree /oradata/flash_recovery_area/ORCL/archivelog /oradata/flash_recovery_area/ORCL/archivelog |-- 2015_06_17 | |-- o1_mf_1_10_br4roh6t_.arc | |-- o1_mf_1_3_br4fxlvo_.arc | |-- o1_mf_1_4_br4fy10k_.arc | |-- o1_mf_1_5_br4fydsg_.arc | |-- o1_mf_1_6_br4lb3cp_.arc | |-- o1_mf_1_7_br4nmslr_.arc | |-- o1_mf_1_8_br4nnqdn_.arc | `-- o1_mf_1_9_br4rng1x_.arc |-- 2015_06_23 | `-- o1_mf_1_11_brlp30qd_.arc `-- 2015_07_07 `-- o1_mf_1_12_bsqjvt1g_.arc 3 directories, 10 files SQL> SQL> !mv /oradata/flash_recovery_area/ORCL/archivelog/2015_07_07/o1_mf_1_12_bsqjvt1g_.arc /oradata/flash_recovery_area/ORCL/archivelog/2015_07_07/lost-o1_mf_1_12_bsqjvt1g_.arc-lost SQL> !tree /oradata/flash_recovery_area/ORCL/archivelog /oradata/flash_recovery_area/ORCL/archivelog |-- 2015_06_17 | |-- o1_mf_1_10_br4roh6t_.arc | |-- o1_mf_1_3_br4fxlvo_.arc | |-- o1_mf_1_4_br4fy10k_.arc | |-- o1_mf_1_5_br4fydsg_.arc | |-- o1_mf_1_6_br4lb3cp_.arc | |-- o1_mf_1_7_br4nmslr_.arc | |-- o1_mf_1_8_br4nnqdn_.arc | `-- o1_mf_1_9_br4rng1x_.arc |-- 2015_06_23 | `-- o1_mf_1_11_brlp30qd_.arc `-- 2015_07_07 `-- lost-o1_mf_1_12_bsqjvt1g_.arc-lost 3 directories, 10 files SQL> SQL> select file_id,file_name,tablespace_name,online_status,status,bytes/1024/1024 "MB" from dba_data_files; FILE_ID FILE_NAME TABLESPACE_NAME ONLINE_ STATUS MB ---------- --------------------------------------------- ------------------------------ ------- --------- ---------- 4 /oradata/orcl/users01.dbf USERS ONLINE AVAILABLE 5 3 /oradata/orcl/sysaux01.dbf SYSAUX ONLINE AVAILABLE 250 2 /oradata/orcl/undotbs01.dbf UNDOTBS1 ONLINE AVAILABLE 35 1 /oradata/orcl/system01.dbf SYSTEM SYSTEM AVAILABLE 480 5 /oradata/orcl/example01.dbf EXAMPLE ONLINE AVAILABLE 100 6 /oradata/orcl/adamhuan01.dbf ADAMHUAN ONLINE AVAILABLE 200 7 /oradata/orcl/adamhuan02.dbf ADAMHUAN ONLINE AVAILABLE 200 8 /oradata/orcl/adamhuan03.dbf ADAMHUAN ONLINE AVAILABLE 200 9 /oradata/orcl/adamhuan04.dbf ADAMHUAN RECOVER AVAILABLE 9 rows selected. SQL> SQL> alter database create datafile '/oradata/orcl/adamhuan04.dbf' as '/oradata/orcl/adamhuan04-restore.dbf'; Database altered. SQL> select file_id,file_name,tablespace_name,online_status,status,bytes/1024/1024 "MB" from dba_data_files; FILE_ID FILE_NAME TABLESPACE_NAME ONLINE_ STATUS MB ---------- --------------------------------------------- ------------------------------ ------- --------- ---------- 4 /oradata/orcl/users01.dbf USERS ONLINE AVAILABLE 5 3 /oradata/orcl/sysaux01.dbf SYSAUX ONLINE AVAILABLE 250 2 /oradata/orcl/undotbs01.dbf UNDOTBS1 ONLINE AVAILABLE 35 1 /oradata/orcl/system01.dbf SYSTEM SYSTEM AVAILABLE 480 5 /oradata/orcl/example01.dbf EXAMPLE ONLINE AVAILABLE 100 6 /oradata/orcl/adamhuan01.dbf ADAMHUAN ONLINE AVAILABLE 200 7 /oradata/orcl/adamhuan02.dbf ADAMHUAN ONLINE AVAILABLE 200 8 /oradata/orcl/adamhuan03.dbf ADAMHUAN ONLINE AVAILABLE 200 9 /oradata/orcl/adamhuan04-restore.dbf ADAMHUAN RECOVER AVAILABLE 9 rows selected. SQL> recover datafile '/oradata/orcl/adamhuan04-restore.dbf'; Media recovery complete. SQL> select file_id,file_name,tablespace_name,online_status,status,bytes/1024/1024 "MB" from dba_data_files; FILE_ID FILE_NAME TABLESPACE_NAME ONLINE_ STATUS MB ---------- --------------------------------------------- ------------------------------ ------- --------- ---------- 4 /oradata/orcl/users01.dbf USERS ONLINE AVAILABLE 5 3 /oradata/orcl/sysaux01.dbf SYSAUX ONLINE AVAILABLE 250 2 /oradata/orcl/undotbs01.dbf UNDOTBS1 ONLINE AVAILABLE 35 1 /oradata/orcl/system01.dbf SYSTEM SYSTEM AVAILABLE 480 5 /oradata/orcl/example01.dbf EXAMPLE ONLINE AVAILABLE 100 6 /oradata/orcl/adamhuan01.dbf ADAMHUAN ONLINE AVAILABLE 200 7 /oradata/orcl/adamhuan02.dbf ADAMHUAN ONLINE AVAILABLE 200 8 /oradata/orcl/adamhuan03.dbf ADAMHUAN ONLINE AVAILABLE 200 9 /oradata/orcl/adamhuan04-restore.dbf ADAMHUAN OFFLINE AVAILABLE 9 rows selected. SQL> SQL> alter database datafile 'select file_id,file_name,tablespace_name,online_status,status,bytes/1024/1024 "MB" from dba_data_files; SQL> SQL> alter database datafile '/oradata/orcl/adamhuan04-restore.dbf' online; Database altered. SQL> select file_id,file_name,tablespace_name,online_status,status,bytes/1024/1024 "MB" from dba_data_files; FILE_ID FILE_NAME TABLESPACE_NAME ONLINE_ STATUS MB ---------- --------------------------------------------- ------------------------------ ------- --------- ---------- 4 /oradata/orcl/users01.dbf USERS ONLINE AVAILABLE 5 3 /oradata/orcl/sysaux01.dbf SYSAUX ONLINE AVAILABLE 250 2 /oradata/orcl/undotbs01.dbf UNDOTBS1 ONLINE AVAILABLE 35 1 /oradata/orcl/system01.dbf SYSTEM SYSTEM AVAILABLE 480 5 /oradata/orcl/example01.dbf EXAMPLE ONLINE AVAILABLE 100 6 /oradata/orcl/adamhuan01.dbf ADAMHUAN ONLINE AVAILABLE 200 7 /oradata/orcl/adamhuan02.dbf ADAMHUAN ONLINE AVAILABLE 200 8 /oradata/orcl/adamhuan03.dbf ADAMHUAN ONLINE AVAILABLE 200 9 /oradata/orcl/adamhuan04-restore.dbf ADAMHUAN ONLINE AVAILABLE 200 9 rows selected. SQL> SQL> SQL> |
——————————————————————————————
Done。
