Recovery

RECOVERING FROM A LOST DATAFILE IN A UNDO TABLESPACE

Problem :

Berikut adalah skenario recovery dimana datafile ditablespace rollback segment mengalami kerusakan atau hilang, kondisi dimana Oracle tidak dapat mengenalinya lagi. Saat kita coba startup database maka yang keluar adalah error in ORA-1157, ORA-1110, dan error dilevel OS seperti ORA-7360. Dan apabila kita coba shutdown database dengan mode NORMAL atau IMMEDIATE akan keluar error ORA-1116, ORA-1110,dan juga kemungkinan error di level OS seperti ORA-7368.

Solution :

Pendekatan yang harus dilakukan tergantung dari skenario yang spesifik bagaimana status dari datafile rollback yang hilang:

I. DATABASE IS DOWN

Saat dilakukan startup database akan keluar error ORA-1157 dan ORA-1110. Disini solusi yang dijalankan tergantung apakah database bisa melakukan shutdown secara sempurna atau tidak.

I.A. DATABASE CLEANLY SHUT DOWN

Jika kita bener2 YAKIN kalo database bener2 “cleanly shutdown”, dan proses ini dengan menggunakan shutdown NORMAL ataupun IMMEDIATE, lalu solusi yang paling mudah adalah dengan melakukan OFFLINE DROP terhadap datafile yang hilang. Open database dalam mode RESTRICTED, kemudian DROP dan recreate UNDO tablespace dan masukkan ke tablespace yang terhubung dengannya. JANGAN lakukan prosedur ini jika database kita shutdown ABORT atau crash.

Step2 nya sebagai berikut:

1. Pastikan database shutdown “cleanly”!!!

Cek file alert.log untuk database instance. Lihat dibaris paling bawah dari file ini dan pastikan bahwa terakhir kali kita shutdown kita mendapat pesan:

“Shutting down instance (immediate)”

ATAU

“alter database close normal Completed: alter database close normal”

Hal ini juga termasuk dalam kasus clean shutdown diikuti dengan failed saat proses startup database. Dalam kasus seperti itu Oracle akan mengeluarkan pesan error dan akan melakukan shutdown abort. Khusus untuk solusi ini, kita anggap kondisi adalah clean shutdown. Jika kasusnya tidak seperti itu adalah bila terakhir kali dilakukan shutdown database adalah dalam mode ABORT, atau database mengalami crash.. dan ini TIDAK sesuai apabila dilakukan prosedur ini. Kita harus melakukan sesuai kasus I.B dibawah

2. Jika kita menggunakan automatic UNDO_MANAGEMENT, hapus parameter ini dari parameter file atau set ke MANUAL.

Apabila menggunakan rollback segment, hapus semua segment di tablespace dimana datafile yang hilang termasuk dari parameter ROLLBACK_SEGMENTS di init.ora untuk instance ini. Bila tidak yakin rollback yang mana yang ada di tablespace tsb, gampang saja, hapus semua masukan ROLLBACK_SEGMENTS.

3. Mount database dalam restricted mode.

SQL> STARTUP RESTRICT MOUNT

4. Offline drop datafile yang rusak.

SQL> ALTER DATABASE DATAFILE ‘<full_path_file_name>’ OFFLINE DROP;

5. Open the database.

SQL> ALTER DATABASE OPEN

Kita akan mendapati pesan “Statement processed,”

Apabila kita malah mendapat pesan error ORA-604, ORA-376, and ORA-1110 nampaknya shutdown tidak normal/immediate. Review lagi opsi yang ada atau kalo punya link ke metalink bisa contact langsung ke Oracle Support Services.

6. Drop undo tablespace atau tablespace yang memiliki rollback segments dimana datafile termasuk dalam bagiannya jg.

SQL> DROP TABLESPACE <tablespace_name> INCLUDING CONTENTS;

7. Create ulang undo tablespace. Jika menggunakan rollback segments, recreate rollback segment tablespace dan semua rollback segment jg.Ingatlah untuk alter rollback ONLINE setelah kita create ulang.

8. Edit parameter file setting:

UNDO_MANAGEMENT=AUTO

UNDO_TABLESPACE=<new undo tablespace name>

Jika menggunakan rollback segments, reinclude rollbacks yang baru saja kita buat diROLLBACK_SEGMENTS parameter kedalam init.ora. Setelah rollback segment kita online dilangkah #7, tidak perlu untuk memproses dengan melakukan shutdown/startup seperti yang dilakukan pada undo tablespace. Yang perlu kita jalankan adalah :

SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;

9. Apabila kita menggunakan UNDO tablespace, shutdown dan startup database oracle kita.

I.B. DATABASE NOT CLEANLY SHUT DOWN

Ini adalah situasi dimana database saat dishutdown terakhir yaitu dengan shutdown abort atau database mengalami crash.

Dalam kasus ini, rollback segments yang mempunyai extent di datafile yang rusak masih terdapat transaksi aktif. Oleh karena itu, maka file tidak dapat kita OFFLINE atau DROP. Kita harus melakukan restore terhadap datafile yang rusak dari backup dan mengunakan media recovery. Jika database dalam mode NOARCHIVELOG, kita hanya akan bisa melakukan recovery datafile jika redo yang diaplikasikan adalah masih dalam range dari online log kita. Kalau tidak full backup yang terakhir harus direstore. Dan bila backup datafile tidak ada, please contact Oracle Support Services..

Berikut step2 nya:

1. Restore file yang rusak dari backup kita.

2. Mount database:

SQL> STARTUP MOUNT;

3. Jalankan query berikut:

SQL> SELECT FILE#, NAME, STATUS FROM V$DATAFILE;

Jika status dari file yang baru saja kita restore “OFFLINE”, kita harus membuatnya online sebelum memprosesnya.

SQL> ALTER DATABASE DATAFILE ‘<full_path_file_name>’ ONLINE;

4. Jalankan query berikut :

SQL> SELECT V1.GROUP#, MEMBER, SEQUENCE#, FIRST_CHANGE#

FROM V$LOG V1, V$LOGFILE V2

WHERE V1.GROUP# = V2.GROUP# ;

Ini akan memperlihatkan semua online redolog file dan masing2 sequence dan nomer first change.

5. Jika database dalam mode NOARCHIVELOG, jalankan query berikut:

SQL> SELECT FILE#, CHANGE# FROM V$RECOVER_FILE;

6. Recover the datafile:

SQL> RECOVER DATAFILE ‘<full_path_file_name>’

8. Open the database:

SQL> ALTER DATABASE OPEN;

II. THE DATABASE IS UP

Jika kita mendapati rollback datafile corupt dan database masih dalam posisi run. JANGAN SHUTDOWN DATABASE. Dikebanyakan kasus ini sangat simple untuk diselesaikan dengan database up daripada database dalam kondisi down.

Dua Pendekatan yang memungkinkan dalam skenario ini adalah:

A) Pendekatan pertama adalah dengan create UNDO baru (atau rollback segment) tablespace, alter system agar menggunakan yang baru dan drop yang lama.

B) Pendekatan yang lain meliputi membuat offline datafile yang hilang, melakukan restore datafile dari file backup dan kemudian menggunakan media recovery agar konsisten dengan keseluruhan database.Metode ini hanya dapat digunakan jika database dalam mode ARCHIVELOG.

Secara umum, pendekatan IIA harus coba dilakukan terlebih dahulu, meskipun begitu, tergantung dari aktivitas dan pemakaian undo, Menghapus tablespace yang lama mungkin berakibat error dalam transaksi yang aktif. Ada beberapa cara untuk dicoba , drop tablespace yang lama, tapi jika ternyata cara2 tsb gagal, pendekatan IIB bisa digunakan.

PENDEKATAN II.A: RECREATE ROLLBACK TABLESPACE

Pendekatan ini dapat digunakan apabila database menggunakan mode Archive.

Step2nya adalah:

Untuk undo tablespaces:

1. Create undo tablespace baru:

SQL> CREATE UNDO TABLESPACE <nama baru undo tablepace>

DATAFILE ‘<lokasi baru dan nama datafile>’ SIZE ….;

2. Alter system agar menggunakan undo tablespace yang baru:

SQL> ALTER SYSTEM SET UNDO_TABLESPACE=’<nama undo tablepace baru >’;

Di point ini, semua undo yang baru akan dialokasikan di undo segment yang dimanage dalam UNDO_TBS2 tablespace.

3. Coba Drop undo tablespace lama:

SQL> DROP TABLESPACE <nama undo tablespace lama> INCLUDING CONTENTS;

JIka hal ini sukses, berarti selesai. Tapi jika keluar error, cek dokumentasi “Handling errors dropping undo/rollback segments” dibawah ini.

Untuk rollback segment tablespaces:

1. Create tablespace baru dengan rollback segments. Untuk menentukan ukuran space dan extent, lihat informasi di tabel dba_tablespaces untuk info rollback segment tablespace yang lama.

SQL> CREATE TABLESPACE …..

2. Create rollback segments ditablespace ini

SQL> CREATE ROLLBACK SEGMENT…..

3. buat online rollback segments ditablespace baru:

SQL> ALTER ROLLBACK SEGMENT <new_rollback_segment> ONLINE;

Ulangi untuk semua rollback segment.

4. Coba buat offline semua rollback segment di tablespace dimana datafile yang corupt berada.

SQL> ALTER ROLLBACK SEGMENT <rollback_segment> OFFLINE;

Ulangi command ini untuk semua rollback ditablespace.

5. Check status dari rollbacks.

Semua itu harus kita buat offline terlebih dahulu sebelum bisa kita drop. Jalankan query berikut:

SQL> SELECT SEGMENT_NAME, STATUS FROM DBA_ROLLBACK_SEGS

WHERE TABLESPACE_NAME = ‘<TABLESPACE_NAME>’;

6. Drop semua rollback segment yang offline.

Untuk setiap rollback yang memiliki status “OFFLINE” dilihat dengan query dilangkah ke 2, jalankan command berikut:

SQL> DROP ROLLBACK SEGMENT <rollback_segment>;

7. Drop rollback segment tablespace:

SQL> DROP TABLESPACE <tablespace_name> INCLUDING CONTENTS;

Jika hal ini sukses, kita selesai. Jika ternyata keluar error, cek dokumentasi “Handling errors dropping undo/rollback segments” dibawah ini.

HANDLING ERRORS DROPPING UNDO/ROLLBACK SEGMENTS:

NOTE, meskipun automatic undo dan undo tablespace digunakan database, prosedurnya adalah sama. Yang membedakan adalah nama segment ditentukan oleh Oracle.

1. Check untuk rollback segments yang online.

SQL> SELECT SEGMENT_NAME, STATUS FROM DBA_ROLLBACK_SEGS

WHERE TABLESPACE_NAME = ‘<OLD TABLESPACE_NAME>’;

Jika ada beberapa rollback yang kita coba offline masih punya status “ONLINE”, hal ini biasanya mengindikasikan bahwa segment masih terdapat transaksi yang aktif.

2. Check untuk active transactions dengan query berikut:

SQL> SELECT SEGMENT_NAME, XACTS ACTIVE_TX, V.STATUS

FROM V$ROLLSTAT V, DBA_ROLLBACK_SEGS

WHERE TABLESPACE_NAME = ‘<TABLESPACE_NAME>’ AND SEGMENT_ID = USN;

Jika query diatas tidak menghasilkan apa2, ini berarti semua rollbacks di tablespace yang bersangkutan telah offline. Ulangi query dilangkah 1 untuk mencari nama rollback yang offline dan coba untuk drop undo tablespace atau individual rollback segments seperti yang disebutkan diatas.

Jika query diatas menghasilkan satu atau lebih dari satu row, maka seharusnya statusnya adalah “PENDING OFFLINE”. selanjutnya, check kolom ACTIVE_TX untuk tiap rollback. Jika ini memiliki nilai 0, ini berarti tidak ada transaksi yang pending yang tertinggal di rollback dan ini seharusnya dibuat offline. Ulangi query dilangkah 1 dan ulangi lagi sampai terlihat rollback segment telah offline dan coba untuk DROP lagi. Sekali DROP telah sukses, kita selesai. Jika ada “pending offline” rollback memiliki nilai 1 atau lebih besar di kolom ACTIVE_TX, jalankan lagi step

3. Force rollbacks dengan active transactions agar menjadi offline.

Pada point ini ,jalan satu2nya adalah dengan menghilangkan “pending offline” rollback. Active transaction di rollback harus di commit atau kita rollback. Query berikut akan menunjukkan user mana yang memiliki transaksi yang akan dirollback:

SQL> SELECT S.SID, S.SERIAL#, S.USERNAME, R.NAME “ROLLBACK”

FROM V$SESSION S, V$TRANSACTION T, V$ROLLNAME R

WHERE R.NAME IN (‘<PENDING_ROLLBACK_1>’, … , ‘<PENDING_ROLLBACK_N>’)

AND S.TADDR = T.ADDR AND T.XIDUSN = R.USN;

Kita bisa meminta user yang ada ditransaksi di”pending offline” rollback dan meminta mereka untuk melakukan commit atau rollback secepatnya. Apabila tidak memungkinkan, kita bisa force dengan kill session mereka.Untuk masukkan perintah berikut bisa dilihat dari query diatas:

SQL> ALTER SYSTEM KILL SESSION ‘<SID>, <SERIAL#>’;

dimana <SID> dan <SERIAL#> dihasilkan dari query sebelumnya. Setelah session kita kill, oracle akan membutuhkan waktu beberapa menit sebelum oracle menyelesaikan rolling back dan melakukan cleanup work. Kembali ke step 1 dan ulangi query secara periodik sampai semua rollbacks ditablespace yang berhubungan telah offline dan siap kita DROP.Jika kita masih tidak bisa melakukan drop terhadap undo tablespace yanglama atau rollback segments, coba untuk restore file dari backup dan recover secara full (pendekatan II.B). Sekali file telah direcovery coba untuk drop undo tablespace lama. If this fail just contact Oracle Support Services.

PENDEKATAN II.B: RESTORE DATAFILE DARI FILE BACKUP

Seperti disebutkan sebelumnya, pendekatan ini hanya bisa dilakukan apabila database dalam mode ARCHIVELOG. Berikut ini adalah

step2 nya:

1. Offline datafile yang corupt.

SQL> ALTER DATABASE DATAFILE ‘<full_path_file_name>’ OFFLINE;

NOTE: Tergantung dari aktivitas database yang ada sekarang, kita mungkin harus create rollback segment tambahan di tablespace yang berbedauntuk menjaga databse agar tetap berjalan saat kita memperbaiki masalah yang ada.

2. Restore datafile dari file backup.

3. Jalankan query berikut:

SQL> SELECT V1.GROUP#, MEMBER, SEQUENCE#

FROM V$LOG V1, V$LOGFILE V2

WHERE V1.GROUP# = V2.GROUP# ;

Ini akan menunjukkan list semua online redolog files dan sequence numbernya.

4. Recover datafile:

SQL> RECOVER DATAFILE ‘<full_path_file_name>’

5. Pastikan tiap command diatas yang kita jalankan kita mendapat pesan “Media recovery complete”. Jika kita diminta untuk non-existing archived log, Oracle mungkin membutuhkan satu atau lebih dari online logs untuk memproses dengan recovery. Bandingkan sequence number yang ada dipesan ORA-280 dengan sequence numbers dari online log kita. lalu masukkan full path name dari satu members dari redo group dimana sequence number sesuai dengan yang diminta oacle. Tetap masukkan online log yang diminta Oracle sampai kita mendapat

pesan “Media recovery complete”.

6. Alter datafile kembali online.

SQL> ALTER DATABASE DATAFILE ‘<full_path_file_name>’ ONLINE;

its DONE