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
Tidak ada komentar:
Posting Komentar