Restore dari Offline 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, tidak bisa dilakukan restore ke direktori asalnya, maka bisa dilakukan restore ke tempat (direktori) lain. Step 1 dan 2 masih seperti yang di atas. Step 3 dan seterusnya adalah berikut ini:

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 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.ctlLog 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.logData 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.dbfTemp file

$ cp /backup/ts/temp01.dbf /newdir/ts/temp01.dbf

4. 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'

5. 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 ...
6. 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'

Offline Backup Oracle Database

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;