RMAN Backup dan Recovery Oracle 10g (3)

9 Sep

Beberapa kasus RMAN
24. Kasus hilangnya datafile
[oracle@iscdd ~]$ rm /u02/oradata/iscdd/system01.dbf
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 1002438656 bytes
Fixed Size                  1223080 bytes
Variable Size             222299736 bytes
Database Buffers          771751936 bytes
Redo Buffers                7163904 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 – see DBWR trace file
ORA-01110: data file 1: ‘/u02/oradata/iscdd/system01.dbf’
SQL> exit;

[oracle@iscdd ~]$ cd $ORACLE_HOME/bin
[oracle@iscdd bin]$ ./rman target / NOCATALOG
RMAN> startup mount force;
Oracle instance started
database mounted
Total System Global Area     167772160 bytes
Fixed Size                     1218316 bytes
Variable Size                 62916852 bytes
Database Buffers             100663296 bytes
Redo Buffers                   2973696 bytes

[oracle@iscdd bin]$ ./rman target / NOCATALOG
Recovery Manager: Release 10.2.0.1.0 – Production on Tue Aug 19 10:13:18 2008
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
connected to target database (not started)

RMAN> startup mount force;
Oracle instance started
database mounted
Total System Global Area    1002438656 bytes
Fixed Size                     1223080 bytes
Variable Size                222299736 bytes
Database Buffers             771751936 bytes
Redo Buffers                   7163904 bytes

RMAN> restore datafile 1;
Starting restore at 19-AUG-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u02/oradata/iscdd/system01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/ISCDD-id-2055978995-12.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/backup/ISCDD-id-2055978995-12.bkp tag=TAG20080815T111906
channel ORA_DISK_1: restore complete, elapsed time: 00:05:47
Finished restore at 19-AUG-08

RMAN> recover database;
Starting recover at 19-AUG-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u02/oradata/iscdd/system01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/ISCDD-id-2055978995-15.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/backup/ISCDD-id-2055978995-15.bkp tag=LEVEL_1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u02/oradata/iscdd/system01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/ISCDD-id-2055978995-18.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/backup/ISCDD-id-2055978995-18.bkp tag=TAG20080819T093111
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 19-AUG-08

RMAN> alter database open;
database opened
RMAN> exit
Recovery Manager complete.
[oracle@iscdd bin]$

[oracle@iscdd ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 – Production on Tue Aug 19 10:26:21 2008
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options

SQL> shutdown;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup;
ORACLE instance started.
Total System Global Area 1002438656 bytes
Fixed Size                  1223080 bytes
Variable Size             222299736 bytes
Database Buffers          771751936 bytes
Redo Buffers                7163904 bytes
Database mounted.
Database opened.

25. Kasus hilangnya datafile pada saat transaksi
a. session I menambah transaksi
SQL> insert into DEPT values(’50’,’IT’,’SBY’);
SQL> insert into DEPT values(’51’,’IT’,’SBY’);
SQL> insert into DEPT values(’52’,’IT’,’SBY’);
SQL> COMMIT;
SQL> SELECT * FROM DEPT;

DEPTNO         DNAME          LOC
———- ————– ————-
50             IT             SBY
51             IT             SBY
52             IT             SBY
10             ACCOUNTING     NEW YORK
20             RESEARCH       DALLAS
30             SALES          CHICAGO
40             OPERATIONS     BOSTON
7 rows selected.

b. Session II data rusak / terhapus
[oracle@iscdd ~]$ rm /u02/oradata/iscdd/users01.dbf

c. Session III database restart
SQL> shutdown abort;
SQL> startup;
ORACLE instance started.
Total System Global Area 1002438656 bytes
Fixed Size                  1223080 bytes
Variable Size             222299736 bytes
Database Buffers          771751936 bytes
Redo Buffers                7163904 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 – see DBWR trace file
ORA-01110: data file 4: ‘/u02/oradata/iscdd/users01.dbf’

d. penyelamatan
[oracle@iscdd bin]$ ./rman target / NOCATALOG
Recovery Manager: Release 10.2.0.1.0 – Production on Tue Aug 19 10:36:30 2008
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
connected to target database: ISCDD (DBID=2055978995, not open)
using target database control file instead of recovery catalog

RMAN> startup mount force;
Oracle instance started
database mounted
Total System Global Area    1002438656 bytes
Fixed Size                     1223080 bytes
Variable Size                222299736 bytes
Database Buffers             771751936 bytes
Redo Buffers                   7163904 bytes

RMAN> restore datafile 4;
Starting restore at 19-AUG-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to /u02/oradata/iscdd/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/ISCDD-id-2055978995-12.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/backup/ISCDD-id-2055978995-12.bkp tag=TAG20080815T111906
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
Finished restore at 19-AUG-08

RMAN> recover database;
Starting recover at 19-AUG-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00004: /u02/oradata/iscdd/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/ISCDD-id-2055978995-15.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/backup/ISCDD-id-2055978995-15.bkp tag=LEVEL_1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00004: /u02/oradata/iscdd/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/ISCDD-id-2055978995-18.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/backup/ISCDD-id-2055978995-18.bkp tag=TAG20080819T093111
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
starting media recovery
media recovery complete, elapsed time: 00:00:01
Finished recover at 19-AUG-08

RMAN> alter database open;
database opened

RMAN> exit
Recovery Manager complete.
[oracle@iscdd bin]$
[oracle@iscdd ~]$ sqlplus scott/tiger@iscdd
SQL*Plus: Release 10.2.0.1.0 – Production on Tue Aug 19 10:38:13 2008
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options

SQL> select * from dept;
DEPTNO         DNAME          LOC
———- ————– ————-
50             IT             SBY
51             IT             SBY
52             IT             SBY
10             ACCOUNTING     NEW YORK
20             RESEARCH       DALLAS
30             SALES          CHICAGO
40             OPERATIONS     BOSTON
7 rows selected.

26. Kasus hilangnya controlfile
[oracle@iscdd ~]$ rm /u02/oradata/iscdd/*.ctl

[oracle@iscdd ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 – Production on Tue Aug 19 10:43:00 2008
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
With the Partitioning, OLAP and Data Mining options
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 1002438656 bytes
Fixed Size                  1223080 bytes
Variable Size             226494040 bytes
Database Buffers          767557632 bytes
Redo Buffers                7163904 bytes
ORA-00205: error in identifying control file, check alert log for more info

[oracle@iscdd ~]$ cd $ORACLE_HOME/bin
[oracle@iscdd bin]$ ./rman target / NOCATALOG
Recovery Manager: Release 10.2.0.1.0 – Production on Tue Aug 19 10:44:39 2008
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
connected to target database: iscdd (not mounted)
using target database control file instead of recovery catalog

RMAN> set dbid 2055978995;
executing command: SET DBID

RMAN> restore controlfile from autobackup;
Starting restore at 19-AUG-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
recovery area destination: /u02/oradata/flash_recovery_area/
database name (or database unique name) used for search: ISCDD
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: /u02/oradata/flash_recovery_area/ISCDD/autobackup/2008_08_19/o1_mf_s_663154943_4bndrlqh_.bkp
channel ORA_DISK_1: control file restore from autobackup complete
output filename=/u02/oradata/iscdd/control01.ctl
output filename=/u02/oradata/iscdd/control02.ctl
output filename=/u02/oradata/iscdd/control03.ctl
Finished restore at 19-AUG-08

RMAN> startup mount force;
Oracle instance started
database mounted
Total System Global Area    1002438656 bytes
Fixed Size                     1223080 bytes
Variable Size                226494040 bytes
Database Buffers             767557632 bytes
Redo Buffers                   7163904 bytes

RMAN> restore database;
Starting restore at 19-AUG-08
Starting implicit crosscheck backup at 19-AUG-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
Crosschecked 17 objects
Finished implicit crosscheck backup at 19-AUG-08

Starting implicit crosscheck copy at 19-AUG-08
using channel ORA_DISK_1
Finished implicit crosscheck copy at 19-AUG-08

searching for all files in the recovery area
cataloging files…
cataloging done

List of Cataloged Files
=======================
File Name: /u02/oradata/flash_recovery_area/ISCDD/autobackup/2008_08_19/o1_mf_s_663154943_4bndrlqh_.bkp
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u02/oradata/iscdd/system01.dbf
restoring datafile 00002 to /u02/oradata/iscdd/undotbs01.dbf
restoring datafile 00003 to /u02/oradata/iscdd/sysaux01.dbf
restoring datafile 00004 to /u02/oradata/iscdd/users01.dbf
restoring datafile 00005 to /u02/oradata/iscdd/example01.dbf
restoring datafile 00006 to /u02/oradata/iscdd/rman_ts01.dbf
restoring datafile 00007 to /u02/oradata/iscdd/rman_ts02.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/ISCDD-id-2055978995-12.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/backup/ISCDD-id-2055978995-12.bkp tag=TAG20080815T111906
channel ORA_DISK_1: restore complete, elapsed time: 00:00:36
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00008 to /u02/oradata/iscdd/senin_ts01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/ISCDD-id-2055978995-18.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/backup/ISCDD-id-2055978995-18.bkp tag=TAG20080819T093111
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
Finished restore at 19-AUG-08

RMAN> recover database;
Starting recover at 19-AUG-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u02/oradata/iscdd/system01.dbf
destination for restore of datafile 00002: /u02/oradata/iscdd/undotbs01.dbf
destination for restore of datafile 00003: /u02/oradata/iscdd/sysaux01.dbf
destination for restore of datafile 00004: /u02/oradata/iscdd/users01.dbf
destination for restore of datafile 00005: /u02/oradata/iscdd/example01.dbf
destination for restore of datafile 00006: /u02/oradata/iscdd/rman_ts01.dbf
destination for restore of datafile 00007: /u02/oradata/iscdd/rman_ts02.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/ISCDD-id-2055978995-15.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/backup/ISCDD-id-2055978995-15.bkp tag=LEVEL_1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:08
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u02/oradata/iscdd/system01.dbf
destination for restore of datafile 00002: /u02/oradata/iscdd/undotbs01.dbf
destination for restore of datafile 00003: /u02/oradata/iscdd/sysaux01.dbf
destination for restore of datafile 00004: /u02/oradata/iscdd/users01.dbf
destination for restore of datafile 00005: /u02/oradata/iscdd/example01.dbf
destination for restore of datafile 00006: /u02/oradata/iscdd/rman_ts01.dbf
destination for restore of datafile 00007: /u02/oradata/iscdd/rman_ts02.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/ISCDD-id-2055978995-18.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/backup/ISCDD-id-2055978995-18.bkp tag=TAG20080819T093111
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02

starting media recovery
archive log thread 1 sequence 9 is already on disk as file /u02/archive/archive011_9_662546934.dbf
archive log thread 1 sequence 10 is already on disk as file /u02/oradata/iscdd/redo03.log
archive log thread 1 sequence 11 is already on disk as file /u02/oradata/iscdd/redo01.log
archive log thread 1 sequence 12 is already on disk as file /u02/oradata/iscdd/redo02.log
archive log filename=/u02/archive/archive011_9_662546934.dbf thread=1 sequence=9
archive log filename=/u02/oradata/iscdd/redo03.log thread=1 sequence=10
archive log filename=/u02/oradata/iscdd/redo01.log thread=1 sequence=11
archive log filename=/u02/oradata/iscdd/redo02.log thread=1 sequence=12
media recovery complete, elapsed time: 00:00:03
Finished recover at 19-AUG-08

RMAN> alter database open resetlogs;
database opened

RMAN> exit
Recovery Manager complete.
[oracle@iscdd bin]$

27. script untuk melakukan backup
[oracle@iscdd ~]$ mkdir /home/oracle/script -p
[oracle@iscdd ~]$ vi /home/oracle/script/daily_incremental.sh
#! /bin/bash
# Daily incremental backup
#
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
cd $ORACLE_HOME/bin
./rman @/home/oracle/script/daily_incremental.rman LOG /home/oracle/script/daily_incremental.rman.log

[oracle@iscdd ~]$ chmod +x /home/oracle/script/daily_incremental.sh
[oracle@iscdd ~]$ vi /home/oracle/script/daily_incremental.rman
CONNECT TARGET sys/oracle0@orcl6
BACKUP INCREMENTAL LEVEL 1 DATABASE TAG=daily_incremental;
EXIT;

[oracle@iscdd ~]$ vi /home/oracle/script/weekly_full.sh
#! /bin/bash
# Weekly full backup
#
export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1
cd $ORACLE_HOME/bin
./rman @/home/oracle/script/weekly_full.rman LOG /home/oracle/script/weekly_full.rman.log

[oracle@iscdd ~]$ chmod +x /home/oracle/script/weekly_full.sh
[oracle@iscdd ~]$ vi /home/oracle/script/weekly_full.rman
CONNECT TARGET sys/ora@iscdd
BACKUP INCREMENTAL LEVEL 0 DATABASE TAG=full_backup;
delete noprompt obsolete;
EXIT;

28. crontab untuk melakukan backup
[root@iscdd ~]# crontab -e
0 10  * * 1-6 /home/oracle/script/daily_incremental.sh
0 10  * * 7   /home/oracle/script/weekly_full.sh

29. hasil crontab untuk backup
[root@iscdd ~]# date
Tue Aug 19 11:03:42 WIT 2008
[root@iscdd ~]# ls -la /home/oracle/script/
total 24
drwxr-xr-x   2 oracle oinstall 4096 Aug 19 11:01 .
drwx——  18 oracle oinstall 4096 Aug 19 11:01 ..
-rw-r–r–   1 oracle oinstall   95 Aug 19 10:59 daily_incremental.rman
-rwxr-xr-x   1 oracle oinstall  220 Aug 19 10:58 daily_incremental.sh
-rw-r–r–   1 oracle oinstall  115 Aug 19 11:01 weekly_full.rman
-rwxr-xr-x   1 oracle oinstall  203 Aug 19 11:00 weekly_full.sh

[root@iscdd ~]# date -s 23:55:00
Tue Aug 19 23:55:00 WIT 2008

2 Responses to “RMAN Backup dan Recovery Oracle 10g (3)”

  1. adi setyo April 10, 2011 at 10:51 am #

    artikelnya bagus, sangat bermanfaat, jika misal Bapak bikin buku, saya bersedia untuk beli,asal isinya langsung kearah kasus dunia nyata. Saya sangat apresiasi banget dengan artikelnya.
    Saya ada permasalahan restore database pake rman, saya coba restorenya berhasil, tapi data ga kembali.
    Misal, saya punya Schema namanya Cams, sekitar jam 10, ada 2 fungsi yang saya hapus, gimana saya restorenya ya supaya 2 fungsi itu kembali?
    saya sebelumnya udah melakukan backup full dan backup incremental.

    Terima Kasih sebelumnya,
    adi

  2. setijoagus April 11, 2011 at 2:51 am #

    saya akan lebih bersemangat kalau artikel ini bermanfaat.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: