1.AMAÇ VE KAPSAM
DATA1 VE DATA2 olmak üzere 2 tane disk groupumuz var. Datafileların coğunlugu DATA1 üstünde bulunmakta, arcive log ,backup ve 1 tane datafileda DATA2 diskgroupun ustunde bulunmakta.
Senaryomuza göre DATA2 diskgroupunu mount edemiyoruz , backup ve archiveloglar o dizinde oldugundan dolayı recover yapamıyoruz ve tek datafile orda bulundugundan ötürü veritabanını açamıyoruz.AMDU yu kullanıp datafilelı dışarı alıp veritabanını açmayı deniyecez.
2.UYGULAMA
2.1. Amdu tooluyla asm mount olmasada içindeki dosyaları kurtarma şansımız mevcut fakat bu dosyalar neyin nesidir bilemiyoruz ancak bazı yöntemlerle TAHMIN edebiliyoruz…
Mount modda kadar açılan veritabanına bağlanıp datafileların nerelerde olduguna bakalım.
|
1 2 3 4 5 6 7 8 9 |
SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- +DATA1/orcl/datafile/system.260.755095657 +DATA1/orcl/datafile/sysaux.266.755095657 +DATA1/orcl/datafile/undotbs1.264.755095657 +DATA1/orcl/datafile/users.263.755095657 +DATA1/orcl/datafile/example.257.755096019 +DATA2/orcl/datafile/users02.755.755095657 |
PS: Kaybolan diskgroupunun üstünde control veya redo dosyalarınız varsa onlarında ismini,lokasyonunu almanızda yarar var.
2.2. Rmane baglanıp backupları listeliyoruz bu listeyide bir önceki sorgumuz gibi ilerde kullanıcaz, arcihiveloglarınızda kaybolan disk ustundeyse ‘list archivelog all’ diyip archivelogların listesinide almamız gerekiyor.
PS: Crosscheck backup yapmayın!
|
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 |
[oracle@oracle bin]$ rman Recovery Manager: Release 11.1.0.6.0 - Production on Fri Aug 12 05:05:13 2011 Copyright (c) 1982, 2007, Oracle. All rights reserved. RMAN> connect target connected to target database: ORCL (DBID=1282876694, not open) RMAN> list backup; using target database control file instead of recovery catalog List of Backup Sets =================== BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 1 Full 1.09G DISK 00:01:25 12-AUG-11 BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20110812T044259 Piece Name: +DATA2/orcl/backupset/2011_08_12/nnndf0_tag20110812t044259_0.256.758954585 List of Datafiles in backup set 1 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 1 Full 787011 12-AUG-11 +DATA1/orcl/datafile/system.260.755095657 2 Full 787011 12-AUG-11 +DATA1/orcl/datafile/sysaux.266.755095657 3 Full 787011 12-AUG-11 +DATA1/orcl/datafile/undotbs1.264.755095657 4 Full 787011 12-AUG-11 +DATA1/orcl/datafile/users.263.755095657 5 Full 787011 12-AUG-11 +DATA1/orcl/datafile/example.257.755096019 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ --------------- 2 Full 9.36M DISK 00:00:06 12-AUG-11 BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20110812T044259 Piece Name: +DATA2/orcl/backupset/2011_08_12/ncsnf0_tag20110812t044259_0.259.758954677 SPFILE Included: Modification time: 12-AUG-11 SPFILE db_unique_name: ORCL Control File Included: Ckp SCN: 787011 Ckp time: 12-AUG-11 |
2.3. ASM disklerimizi kontrol edelim.
|
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 |
SQL> SELECT name, header_status, path FROM V$ASM_DISK; NAME HEADER_STATU ------------------------------ ------------ PATH -------------------------------------------------------------------------------- MEMBER ORCL:DISK2 DISK1 MEMBER ORCL:DISK1 DISK3 MEMBER ORCL:DISK3 NAME HEADER_STATU ------------------------------ ------------ PATH -------------------------------------------------------------------------------- DISK4 MEMBER ORCL:DISK4 |
2.4. Artık AMDU yu kullanmaya başlayabiliriz. AMDU tooluna veritabanınız 11g se $ORACLE_HOME/bin klasöründen eğer 10g’se metalinkten ulaşabilirsiniz. Metalink id: 553639.1
PS: Metalinkte veya herhangi bir sitede amdunun tam olarak kullanımıyla ilgili herhangi bir doküman bulamadım.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
[oracle@oracle bin]$ amdu -diskstring '/dev/oracleasm/disks/DISK*' -dump 'DATA2' amdu_2011_08_12_21_28_34/ [oracle@oracle bin]$ cd amdu_2011_08_12_21_28_34/ [oracle@oracle amdu_2011_08_12_21_28_34]$ ls -ltrh total 50M -rw-r--r-- 1 oracle oinstall 5.4K Aug 12 21:28 report.txt -rw-r--r-- 1 oracle oinstall 4.2K Aug 12 21:28 DATA2.map -rw-r--r-- 1 oracle oinstall 50M Aug 12 21:28 DATA2_0001.img |
2.5. Report.txt dosyasını inceleyelim.
|
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 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 |
[oracle@oracle amdu_2011_08_12_21_28_34]$ more report.txt -*-amdu-*- ******************************* AMDU Settings ******************************** ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1 System name: Linux Node name: oracle Release: 2.6.18-164.el5 Version: #1 SMP Tue Aug 18 15:51:54 EDT 2009 Machine: i686 amdu run: 12-AUG-11 21:28:34 Endianess: 1 --------------------------------- Operations --------------------------------- -dump DATA2 ------------------------------- Disk Selection ------------------------------- -diskstring '/dev/oracleasm/disks/DISK*' ------------------------------ Reading Control ------------------------------- ------------------------------- Output Control ------------------------------- ********************************* DISCOVERY ********************************** ----------------------------- DISK REPORT N0001 ------------------------------ Disk Path: /dev/oracleasm/disks/DISK4 Unique Disk ID: Disk Label: Physical Sector Size: 512 bytes Disk Size: 5137 megabytes Group Name: DATA1 Disk Name: DISK4 Failure Group Name: DISK4 Disk Number: 2 Header Status: 3 Disk Creation Time: 2011/06/24 01:02:30.354000 Last Mount Time: 2011/08/12 21:21:20.570000 Compatibility Version: 0x0a100000 Disk Sector Size: 512 bytes Disk size in AUs: 5137 AUs Group Redundancy: 1 Metadata Block Size: 4096 bytes AU Size: 1048576 bytes Stride: 113792 AUs Group Creation Time: 2011/06/23 23:48:12.995000 File 1 Block 1 location: AU 0 ----------------------------- DISK REPORT N0002 ------------------------------ Disk Path: /dev/oracleasm/disks/DISK3 Unique Disk ID: Disk Label: Physical Sector Size: 512 bytes Disk Size: 5098 megabytes Group Name: DATA1 Disk Name: DISK3 Failure Group Name: DISK3 Disk Number: 1 Header Status: 3 Disk Creation Time: 2011/06/23 23:48:13.102000 Last Mount Time: 2011/08/12 21:21:20.570000 Compatibility Version: 0x0a100000 Disk Sector Size: 512 bytes Disk size in AUs: 5098 AUs Group Redundancy: 1 Metadata Block Size: 4096 bytes AU Size: 1048576 bytes Stride: 113792 AUs Group Creation Time: 2011/06/23 23:48:12.995000 File 1 Block 1 location: AU 0 ----------------------------- DISK REPORT N0003 ------------------------------ Disk Path: /dev/oracleasm/disks/DISK2 Unique Disk ID: Disk Label: Physical Sector Size: 512 bytes Disk Size: 5137 megabytes Group Name: DATA2 Disk Name: DISK2 Failure Group Name: DISK2 Disk Number: 0 Header Status: 3 Disk Creation Time: 2011/06/23 23:48:46.237000 Last Mount Time: 2011/08/12 20:49:00.609000 Compatibility Version: 0x0a100000 Disk Sector Size: 512 bytes Disk size in AUs: 5137 AUs Group Redundancy: 1 Metadata Block Size: 4096 bytes AU Size: 1048576 bytes Stride: 113792 AUs Group Creation Time: 2011/06/23 23:48:46.214000 File 1 Block 1 location: AU 2 ----------------------------- DISK REPORT N0004 ------------------------------ Disk Path: /dev/oracleasm/disks/DISK1 Unique Disk ID: Disk Label: Physical Sector Size: 512 bytes Disk Size: 5098 megabytes Group Name: DATA1 Disk Name: DISK1 Failure Group Name: DISK1 Disk Number: 0 Header Status: 3 Disk Creation Time: 2011/06/23 23:48:13.102000 Last Mount Time: 2011/08/12 21:21:20.570000 Compatibility Version: 0x0a100000 Disk Sector Size: 512 bytes Disk size in AUs: 5098 AUs Group Redundancy: 1 Metadata Block Size: 4096 bytes AU Size: 1048576 bytes Stride: 113792 AUs Group Creation Time: 2011/06/23 23:48:12.995000 File 1 Block 1 location: AU 2 ***************** Slept for 6 seconds waiting for heartbeats ***************** ************************** SCANNING DISKGROUP DATA2 ************************** Creation Time: 2011/06/23 23:48:46.214000 Disks Discovered: 1 Redundancy: 1 AU Size: 1048576 bytes Metadata Block Size: 4096 bytes Physical Sector Size: 512 bytes Metadata Stride: 113792 AU Duplicate Disk Numbers: 0 ---------------------------- SCANNING DISK N0003 ----------------------------- Disk N0003: '/dev/oracleasm/disks/DISK2' Allocated AU's: 3224 Free AU's: 1913 AU's read for dump: 53 Block images saved: 12555 Map lines written: 53 Heartbeats seen: 0 Corrupt metadata blocks: 0 Corrupt AT blocks: 0 ------------------------ SUMMARY FOR DISKGROUP DATA2 ------------------------- Allocated AU's: 3224 Free AU's: 1913 AU's read for dump: 53 Block images saved: 12555 Map lines written: 53 Heartbeats seen: 0 Corrupt metadata blocks: 0 Corrupt AT blocks: 0 ******************************* END OF REPORT ******************************** |
2.6. Map dosyası buzamana kadar notunu almış olduğumuz bilgileri karşılıklı check edeceğimiz dosya oluyor, burada 5. colona inceliyoruz 256 küçük olan sayılar ASM metadatalaı oluyor yani 256dan küçük olanlar datafile,archivelog,redo veya backup pieace olamazlar. Bu sebepten ötürü sadece 256 ye esit veya büyük olan sayıları göz önüne alıcaz.Bu sayıları not alıyoruz.
|
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 |
[oracle@oracle amdu_2011_08_12_21_28_34]$ more DATA2.map N0003 D0000 R00 A00000000 F00000000 I0 E00000000 U00 C00256 S0001 B0000000000 N0003 D0000 R00 A00000001 F00000000 I0 E00000000 U00 C00256 S0001 B0001048576 N0003 D0000 R00 A00000002 F00000001 I0 E00000000 U00 C00256 S0001 B0002097152 N0003 D0000 R00 A00000003 F00000002 I0 E00000000 U00 C00256 S0001 B0003145728 N0003 D0000 R00 A00000004 F00000003 I0 E00000000 U00 C00256 S0001 B0004194304 N0003 D0000 R00 A00000005 F00000003 I0 E00000001 U00 C00256 S0001 B0005242880 N0003 D0000 R00 A00000006 F00000003 I0 E00000002 U00 C00256 S0001 B0006291456 N0003 D0000 R00 A00000007 F00000003 I0 E00000003 U00 C00256 S0001 B0007340032 N0003 D0000 R00 A00000008 F00000003 I0 E00000004 U00 C00256 S0001 B0008388608 N0003 D0000 R00 A00000009 F00000003 I0 E00000005 U00 C00256 S0001 B0009437184 N0003 D0000 R00 A00000010 F00000003 I0 E00000006 U00 C00256 S0001 B0010485760 N0003 D0000 R00 A00000011 F00000003 I0 E00000007 U00 C00256 S0001 B0011534336 N0003 D0000 R00 A00000012 F00000003 I0 E00000008 U00 C00256 S0001 B0012582912 N0003 D0000 R00 A00000013 F00000003 I0 E00000009 U00 C00256 S0001 B0013631488 N0003 D0000 R00 A00000014 F00000003 I0 E00000010 U00 C00256 S0001 B0014680064 N0003 D0000 R00 A00000015 F00000003 I0 E00000011 U00 C00256 S0001 B0015728640 N0003 D0000 R00 A00000016 F00000003 I0 E00000012 U00 C00256 S0001 B0016777216 N0003 D0000 R00 A00000017 F00000003 I0 E00000013 U00 C00256 S0001 B0017825792 N0003 D0000 R00 A00000018 F00000003 I0 E00000014 U00 C00256 S0001 B0018874368 N0003 D0000 R00 A00000019 F00000003 I0 E00000015 U00 C00256 S0001 B0019922944 N0003 D0000 R00 A00000020 F00000003 I0 E00000016 U00 C00256 S0001 B0020971520 N0003 D0000 R00 A00000021 F00000003 I0 E00000017 U00 C00256 S0001 B0022020096 N0003 D0000 R00 A00000022 F00000003 I0 E00000018 U00 C00256 S0001 B0023068672 N0003 D0000 R00 A00000023 F00000003 I0 E00000019 U00 C00256 S0001 B0024117248 N0003 D0000 R00 A00000024 F00000003 I0 E00000020 U00 C00256 S0001 B0025165824 N0003 D0000 R00 A00000025 F00000003 I0 E00000021 U00 C00256 S0001 B0026214400 N0003 D0000 R00 A00000026 F00000003 I0 E00000022 U00 C00256 S0001 B0027262976 N0003 D0000 R00 A00000027 F00000003 I0 E00000023 U00 C00256 S0001 B0028311552 N0003 D0000 R00 A00000028 F00000003 I0 E00000024 U00 C00256 S0001 B0029360128 N0003 D0000 R00 A00000029 F00000003 I0 E00000025 U00 C00256 S0001 B0030408704 N0003 D0000 R00 A00000030 F00000003 I0 E00000026 U00 C00256 S0001 B0031457280 N0003 D0000 R00 A00000031 F00000003 I0 E00000027 U00 C00256 S0001 B0032505856 N0003 D0000 R00 A00000032 F00000003 I0 E00000028 U00 C00256 S0001 B0033554432 N0003 D0000 R00 A00000033 F00000003 I0 E00000029 U00 C00256 S0001 B0034603008 N0003 D0000 R00 A00000034 F00000003 I0 E00000030 U00 C00256 S0001 B0035651584 N0003 D0000 R00 A00000035 F00000003 I0 E00000031 U00 C00256 S0001 B0036700160 N0003 D0000 R00 A00000036 F00000003 I0 E00000032 U00 C00256 S0001 B0037748736 N0003 D0000 R00 A00000037 F00000003 I0 E00000033 U00 C00256 S0001 B0038797312 N0003 D0000 R00 A00000038 F00000003 I0 E00000034 U00 C00256 S0001 B0039845888 N0003 D0000 R00 A00000039 F00000003 I0 E00000035 U00 C00256 S0001 B0040894464 N0003 D0000 R00 A00000040 F00000003 I0 E00000036 U00 C00256 S0001 B0041943040 N0003 D0000 R00 A00000041 F00000003 I0 E00000037 U00 C00256 S0001 B0042991616 N0003 D0000 R00 A00000042 F00000003 I0 E00000038 U00 C00256 S0001 B0044040192 N0003 D0000 R00 A00000043 F00000003 I0 E00000039 U00 C00256 S0001 B0045088768 N0003 D0000 R00 A00000044 F00000003 I0 E00000040 U00 C00256 S0001 B0046137344 N0003 D0000 R00 A00000045 F00000003 I0 E00000041 U00 C00256 S0001 B0047185920 N0003 D0000 R00 A00000046 F00000004 I0 E00000000 U00 C00003 S0001 B0048234496 N0003 D0000 R00 A00000047 F00000004 I0 E00000001 U00 C00000 S0001 B0048246784 N0003 D0000 R00 A00000048 F00000005 I0 E00000000 U00 C00256 S0001 B0048246784 N0003 D0000 R00 A00000049 F00000006 I0 E00000000 U00 C00256 S0001 B0049295360 N0003 D0000 R00 A00000050 F00000001 I0 E00000001 U00 C00256 S0001 B0050343936 N0003 D0000 R00 A00000223 F00000256 I1 E00000000 U00 C00003 S0001 B0051392512 N0003 D0000 R00 A00000579 F00000259 I1 E00000035 U00 C00256 S0001 B0040894464 N0003 D0000 R00 A00001234 F00000755 I1 E00000000 U00 C00005 S0001 B0051404800 |
256 ya eşit ve büyük sayılar: 256, 259, 755
2.7. Şimdi daha önceden notunu aldıgımız backup ve datafilelarla ilgili bilgilere bakıyoruz.
Backup Piece: +DATA2/orcl/backupset/2011_08_12/nnndf0_tag20110812t044259_0.256.758954585
+DATA2/orcl/backupset/2011_08_12/ncsnf0_tag20110812t044259_0.259.758954677
Datafile :
+DATA2/orcl/datafile/users02.755.755095657
2.8. Numaralar birbiriyle eşleşiyor, test çalışması yaptıgımdan dolayı map dosyası az dosya buldu amaç ve kapsamdaki senaryomuzu bire bir uygulasaydık çok sayıda archivelog olacagından DATA2.map baya büyüyecekti ve analizi zor olacaktı.Artık datafileları dışarı extracti deniyelim.
|
1 2 3 4 5 6 7 8 9 10 11 |
[oracle@oracle bin]$ amdu -diskstring '/dev/oracleasm/disks/DISK*' -extract 'DATA2.256' amdu_2011_08_12_21_50_21/ [oracle@oracle bin]$ amdu -diskstring '/dev/oracleasm/disks/DISK*' -extract 'DATA2.259' amdu_2011_08_12_21_54_01/ [oracle@oracle bin]$ amdu -diskstring '/dev/oracleasm/disks/DISK*' -extract 'DATA2.755' amdu_2011_08_12_22_03_03/ |
2.9. Dışarı aktarmış olduğumuz DATA2.755 sını kaybetmiş oldugumuz datafile oldugunu düşünüyorum.Bu yuzden veritabanı mount modayken datafile rename yapıyorum.
|
1 2 3 |
SQL> ALTER DATABASE RENAME FILE '+DATA2/users02.dbf' to '/u01/app/oracle/product/11.1.0/db_1/bin/amdu_2011_08_12_22_03_03/DATA2_755.f'; Database altered. |
2.10. Eski datafilelı dışarı aktarmış olduğumuz datafilelımızla rename yapabildiğimize göre artık veritabanını açmayı deniyebiliriz.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
SQL> alter database open; Database altered. SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- +DATA1/orcl/datafile/system.260.755095657 +DATA1/orcl/datafile/sysaux.266.755095657 +DATA1/orcl/datafile/undotbs1.264.755095657 +DATA1/orcl/datafile/users.263.755095657 +DATA1/orcl/datafile/example.257.755096019 /u01/app/oracle/product/11.1.0/db_1/bin/amdu_2011_08_12_22_03_03/DATA2_755.f |
Merhaba
256 dan kucuk olanlar tam olarak hangi dosyalar oluyor? Birde bu dosyalari tahmin etmek disinda yapabilecegimiz baska birsey varmi?
Tahmin ve olasılık yürütmekten başka bir seçeneğimiz yok diye biliyorum. 256dan düşük olanlar spfile etc ve asmnin kendine haz dosyaları oluyor.
Merhaba Buğra, Ellerine sağlık. Güzel bir yazı.
Merhaba Hakan, begendigine sevindim.
PS: Sql syntax lerin gorunumu esk wp templatime gore ayarliydi , onlari duzenledim simdi daha okunakli oldu.