Langsung ke konten utama

TUTORIAL CARA MELAKUKAN OFFLINE BACKUP DATABASE ORACLE

Off line backup database Oracle


Ada dua metode untuk membackup database Oracle, yaitu off line dan online. Off line backup dilakukan dengan mematikan database terlebih dahulu, baru kemudian membackup datafile. Online backup dilakukan tanpa mematikan database, jadi database masih bisa diakses selama proses backup.
Online backup mensyaratkan database berada dalam mode archive log. Nanti akan saya bahas di artikel lain. Kali ini saya akan membahas tentang off line backup.
Berikut ini step-step untuk off line backup:
1. lihat daftar file oracle (datafile, logfile, dan control file)
Gunakan query berikut ini untuk melihat semua file
SQL> select name as file_name from
(select name from v$tempfile union
select name from v$datafile union
select name from v$controlfile union
select member as name from v$logfile)
order by file_name;

2. Matikan database
SQL> shutdown immediate;
3. Backup file-file database (datafile, logfile, dan control file)

$ cp /oradata/ts/control01.ctl /backup/ts/control01.ctl
$ cp /oradata/ts/control02.ctl /backup/ts/control02.ctl
$ cp /oradata/ts/control03.ctl /backup/ts/control03.ctl
$ cp /oradata/ts/redo04.log /backup/ts/redo04.log
$ cp /oradata/ts/redo05.log /backup/ts/redo05.log
$ cp /oradata/ts/redo06.log /backup/ts/redo06.log
$ cp /oradata/ts/sysaux01.dbf /backup/ts/sysaux01.dbf
$ cp /oradata/ts/system01.dbf /backup/ts/system01.dbf
$ cp /oradata/ts/temp01.dbf /backup/ts/temp01.dbf
$ cp /oradata/ts/undotbs01.dbf /backup/ts/undotbs01.dbf
$ cp /oradata/ts/users01.dbf /backup/ts/users01.dbf

4. Nyalakan database
SQL> startup;
KEMUDIAN LAKUKAN RESTORE DARI OFFLINE BACKUP

Restore dari off line backup


Berikut ini step-step restore dengan memakai instance yang sama.
  1. Siapkan file-file yang akan direstore
    File-file tersebut adalah control, log, data, dan temp file
  2. Pastikan instance sudah mati
    SQL> shutdown immediate;
  3. Restore file-file backup ke directory asalnya
    $ cp /backup/ts/control01.ctl /oradata/ts/control01.ctl
    $ cp /backup/ts/control02.ctl /oradata/ts/control02.ctl
    $ cp /backup/ts/control03.ctl /oradata/ts/control03.ctl
    $ cp /backup/ts/redo04.log /oradata/ts/redo04.log
    $ cp /backup/ts/redo05.log /oradata/ts/redo05.log
    $ cp /backup/ts/redo06.log /oradata/ts/redo06.log
    $ cp /backup/ts/sysaux01.dbf /oradata/ts/sysaux01.dbf
    $ cp /backup/ts/system01.dbf /oradata/ts/system01.dbf
    $ cp /backup/ts/temp01.dbf /oradata/ts/temp01.dbf
    $ cp /backup/ts/undotbs01.dbf /oradata/ts/undotbs01.dbf
    $ cp /backup/ts/users01.dbf /oradata/ts/users01.dbf
  4. Nyalakan databaseSQL> startup;
Jika karena suatu hal, kita tidak bisa merestore ke direktori asalnya, maka kita bisa merestore ke tempat (direktori) lain. Step 1 dan 2 masih seperti yang di atas. Step 3 dan seterusnya adalah berikut ini:
  1. Restore ke directory baru
    Control file
    $ cp /backup/ts/control01.ctl /newdir/ts/control01.ctl
    $ cp /backup/ts/control02.ctl /newdir/ts/control02.ctl
    $ cp /backup/ts/control03.ctl /newdir/ts/control03.ctl    Log file
    $ cp /backup/ts/redo04.log /newdir/ts/redo04.log
    $ cp /backup/ts/redo05.log /newdir/ts/redo05.log
    $ cp /backup/ts/redo06.log /newdir/ts/redo06.log    Data file
    $ cp /backup/ts/sysaux01.dbf /newdir/ts/sysaux01.dbf
    $ cp /backup/ts/system01.dbf /newdir/ts/system01.dbf
    $ cp /backup/ts/undotbs01.dbf /newdir/ts/undotbs01.dbf
    $ cp /backup/ts/users01.dbf /newdir/ts/users01.dbf    Temp file
    $ cp /backup/ts/temp01.dbf /newdir/ts/temp01.dbf
  2. Ubah konfigurasi control file. Edit init (instance parameter) file.
    Filenya di $ORACLE_HOME/dbs/init[NAMAINSTANCE].ora
    Ganti lokasi control file dari yang lama ke yang baru.
    Value yang lama:
    control_files='/oradata/ts/control01.ctl', '/oradata/ts/control02.ctl','/oradata/ts/control03.ctl'Value yang baru:
    control_files='/newdir/ts/control01.ctl', '/newdir/ts/control02.ctl','/newdir/ts/control03.ctl'
  3. Ubah konfigurasi file yang lainnya (log, data, dan temp file)
    SQL> startup mountSQL> alter database rename file '/oradata/ts/redo04.log' to '/newdir/ts/redo04.log';
    SQL> alter database rename file '/oradata/ts/sysaux01.dbf' to '/newdir/ts/sysaux01.dbf';
    SQL> alter database rename file '/oradata/ts/temp01.dbf' to '/newdir/ts/temp01.dbf';
    dan seterusnya ...
  4. Open database
    SQL> alter database open;
Error yang terkait
  1. Jika control file tidak ada, atau ada tapi direktorinya berubah dan init file belum diedit
    SQL> startup
    ORACLE instance started.    Total System Global Area 1610612736 bytes
    Fixed Size 2177912 bytes
    Variable Size 396149896 bytes
    Database Buffers 1207959552 bytes
    Redo Buffers 4325376 bytes
    ORA-00205: error in identifying control file, check alert log for more info
  2. Jika ada data file yang kelewatan, atau ada tapi direktorinya berubah dan belum di-alter/rename
    SQL> startup
    ORACLE instance started.    Total System Global Area 1610612736 bytes
    Fixed Size 2177912 bytes
    Variable Size 396149896 bytes
    Database Buffers 1207959552 bytes
    Redo Buffers 4325376 bytes
    Database mounted.
    ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
    ORA-01110: data file 4: '/oradata/ts/users01.dbf'

Komentar

Posting Komentar