RMAN Backup dan Recovery Oracle 10g


Sebagai acuan untuk prosedur backup dan recovery dengan RMAN

1. RMAN dapat melakukan back up pada semua file database yang dibutuhkan untuk recovery pada saat terjadi kerusakan. RMAN support back up file-file
a. Datafile, dan image copy dari datafiles
b. Control file, dan image copy dari control file
c. Archived redo logs
d. file parameter server
e. Backup pieces, yang berisi backup lain yang dibuat oleh RMAN

2. Menggunakan RMAN O/S authentication

[oracle@iscdd bin]$ cd $ORACLE_HOME/bin
[oracle@iscdd bin]$ ./rman target / NOCATALOG
RMAN> exit
Recovery Manager complete.

3. Menggunakan RMAN Password authentication

[oracle@iscdd bin]$ cd $ORACLE_HOME/bin
[oracle@iscdd ~]$ ./rman target system/ora NOCATALOG
RMAN> exit
Recovery Manager complete.

4. Konfigurasi RMAN agar dapat diakses oleh user tertentu.

SQL> create tablespace rman_ts
datafile ‘/u02/oradata/iscdd/rman_ts01.dbf’
size 5M
segment space management auto;
SQL> alter tablespace rman_ts
Add datafile ‘/u02/oradata/iscdd/rman_ts02.dbf’
size 5M;
SQL> create user rman_user identified by rman123
default tablespace rman_ts
quota unlimited on rman_ts;
SQL> grant connect, resource, recovery_catalog_owner to rman_user;
SQL> exit

5. Konfigurasi RMAN, setting catalog dengan user tertentu.

[oracle@iscdd ~]$ cd $ORACLE_HOME/bin
[oracle@iscdd bin]$ ./rman CATALOG rman_user/rman123@iscdd TARGET sys/ora@iscdd
Recovery Manager: Release 10.2.0.1.0 – Production on Fri Aug 15 10:25:00 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ISCDD (DBID=2055978995)
connected to recovery catalog database
RMAN> create catalog tablespace rman_ts;
recovery catalog created
RMAN> REGISTER DATABASE;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
RMAN> exit
Recovery Manager complete.

6. Untuk mengetahui apakah sudah ter-rigister

[oracle@iscdd bin]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 – Production on Fri Aug 15 13:56:28 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 name from rman_user.rc_database;
NAME
——–
ISCDD
SQL> exit

7. Melihat konfigurasi RMAN

[oracle@iscdd ~]$ cd $ORACLE_HOME/bin
[oracle@iscdd bin]$ ./rman CATALOG rman_user/rman123@iscdd TARGET sys/ora@iscdd
RMAN> SHOW ALL;
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘%F’; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM ‘AES128′; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/u01/app/oracle/product/10.1.0/db_1/dbs/snapcf_edxx.f’; # default

8. Konfigurasi RMAN untuk menyimpan control file secara otomatis

RMAN> configure controlfile autobackup on;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

9. Konfigurasi RMAN untuk set obsolote backup, default 1 hari misal diganti menjadi 2 hari.

RMAN> show retention policy;
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
RMAN> CONFIGURE RETENTION POLICY to recovery window of 2 days;
new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;
new RMAN configuration parameters are successfully stored
RMAN> show retention policy;
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;

10. Konfigurasi RMAN agar archive log disimpan pada folder tertentu sesuai dengan DB_RECOVERY_FILE_DEST.

[oracle@iscdd bin]$ sqlplus / as sysdba
SQL> host
[oracle@iscdd ~]$ mkdir /u02/archive
[oracle@iscdd ~]$ exit

SQL> alter system set log_archive_dest_1=’location=/u02/archive/archive01′;
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Enabled
Archive destination /u02/archive/archive01
Oldest online log sequence 4
Next log sequence to archive 6
Current log sequence 6

11. Konfigurasi RMAN pada database harus dalam mode archive log.

SQL> alter database archivelog;
alter database archivelog
*
ERROR at line 1:
ORA-01126: database must be mounted in this instance and not open in any
instance

SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u02/archive/archive01
Oldest online log sequence 2
Next log sequence to archive 4
Current log sequence 4
SQL> exit

12. Lewat RMAN untuk mengetahui tablespace dan datafilenya.

[oracle@iscdd ~]$ cd $ORACLE_HOME/bin
[oracle@iscdd bin]$ ./rman CATALOG rman_user/rman123@iscdd TARGET sys/ora@iscdd
Recovery Manager: Release 10.2.0.1.0 – Production on Fri Aug 15 14:08:58 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ISCDD (DBID=2055978995)
connected to recovery catalog database
RMAN> report schema;
Report of database schema
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
—- ——– ——————– ——- ————————
1 480 SYSTEM YES /u02/oradata/iscdd/system01.dbf
2 25 UNDOTBS1 YES /u02/oradata/iscdd/undotbs01.dbf
3 230 SYSAUX NO /u02/oradata/iscdd/sysaux01.dbf
4 5 USERS NO /u02/oradata/iscdd/users01.dbf
5 100 EXAMPLE NO /u02/oradata/iscdd/example01.dbf
6 5 RMAN_TS NO /u02/oradata/iscdd/rman_ts01.dbf
7 5 RMAN_TS NO /u02/oradata/iscdd/rman_ts02.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
—- ——– ——————– ———– ——————–
1 20 TEMP 32767 /u02/oradata/iscdd/temp01.dbf

13. Setup backup optimization, agar file yang tidak mengalami perubahan tidak perlu dibackup lagi

RMAN> configure backup optimization on;
new RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

14. Tempat penyimpanan backup, sesuai dengan parameter CHANNEL, jika tidak diisi secara default disimpan di DEFAULT DEVICE TYPE, Jika menggunakan flash recovery area maka secara default disimpan di DB_RECOVERY_FILE_DEST.

RMAN> configure channel device type disk format ‘/home/oracle/backup/%d-id-%I-%s.bkp’;
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘/home/oracle/backup/%d-id-%I-%s.bkp’;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete
%d : database name
%I : nomer DBID
%s : nomer sequence

15. RMAN backup controlfile

[oracle@iscdd bin]$ cd $ORACLE_HOME/bin
[oracle@iscdd bin]$ ./rman
Recovery Manager: Release 10.2.0.1.0 – Production on Mon Aug 13 23:26:16 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
RMAN> CONNECT TARGET sys/ora@iscdd
connected to target database: ISCDD (DBID=2055978995)
RMAN> backup current controlfile;
Starting backup at 15-AUG-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=147 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_1: starting piece 1 at 15-AUG-08
channel ORA_DISK_1: finished piece 1 at 15-AUG-08
piece handle=/home/oracle/backup/ISCDD-id-2055978995-1.bkp tag=TAG20080815T103342 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:06
Finished backup at 15-AUG-08
Starting Control File and SPFILE Autobackup at 15-AUG-08 piece handle=/u02/oradata/flash_recovery_area/ISCDD/autobackup/2008_08_15/o1_mf_s_662812428_4b9y8zdr_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 15-AUG-08

16. RMAN backup controlfile

RMAN> backup current controlfile format
‘/home/oracle/backup/control.bkp’;

Starting backup at 15-AUG-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
channel ORA_DISK_1: starting piece 1 at 15-AUG-08
channel ORA_DISK_1: finished piece 1 at 15-AUG-08
piece handle=/home/oracle/backup/control.bkp tag=TAG20080815T103649 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 15-AUG-08
Starting Control File and SPFILE Autobackup at 15-AUG-08 piece handle=/u02/oradata/flash_recovery_area/ISCDD/autobackup/2008_08_15/o1_mf_s_662812616_4b9ygv40_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 15-AUG-08

17. RMAN backup spfile

RMAN> backup spfile format ‘/home/oracle/backup/spfile.bkp’;
Starting backup at 15-AUG-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 15-AUG-08
channel ORA_DISK_1: finished piece 1 at 15-AUG-08
piece handle=/home/oracle/backup/spfile.bkp tag=TAG20080815T103938 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 15-AUG-08
Starting Control File and SPFILE Autobackup at 15-AUG-08
piece handle=/u02/oradata/flash_recovery_area/ISCDD/autobackup/2008_08_15/o1_mf_s_662812781_4b9ymzz2_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 15-AUG-08
RMAN> exit
Recovery Manager complete.

18. Copy spfile
SPFILE, Server Parameter File, adalah sebuah file binary yang digunakan oracle sebagai parameter initialization. Parameter dapat diubah menggunakan ALTER SYSTEM SET, dimana parameter hanya berubah pada SPFILE.
PFILE adalah traditional text based dari parameter file init.ora. Umumnya disimpan pada directory $ORACLE_BASE/admin/SID/pfile, dan symbolic link berada di directory $ORACLE_HOME/dbs.

[oracle@iscdd bin]$ sqlplus sys/ora@iscdd as sysdba;
SQL> CREATE PFILE = ‘/home/oracle/backup/initorcl.ora’ FROM SPFILE;
SQL> exit
[oracle@iscdd bin]$ cat /home/oracle/backup/initorcl.ora | more
iscdd.__db_cache_size=713031680
iscdd.__java_pool_size=4194304
iscdd.__large_pool_size=4194304
iscdd.__shared_pool_size=264241152
iscdd.__streams_pool_size=8388608
*.audit_file_dest=’/u01/app/oracle/admin/iscdd/adump’
*.background_dump_dest=’/u01/app/oracle/admin/iscdd/bdump’
*.compatible=’10.2.0.1.0′
*.control_files=’/u02/oradata/iscdd/control01.ctl’,'/u02/oradata/iscdd/control02
.ctl’,'/u02/oradata/iscdd/control03.ctl’
*.core_dump_dest=’/u01/app/oracle/admin/iscdd/cdump’
*.db_block_size=8192
*.db_domain=”
*.db_file_multiblock_read_count=16
*.db_name=’iscdd’
*.db_recovery_file_dest=’/u02/oradata/flash_recovery_area/’
*.db_recovery_file_dest_size=2147483648
*.dispatchers=’(PROTOCOL=TCP) (SERVICE=iscddXDB)’
*.job_queue_processes=10
*.log_archive_dest_1=’location=/u02/archive/archive01′
*.open_cursors=300
*.pga_aggregate_target=333447168
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.sga_target=1002438656
*.undo_management=’AUTO’
*.undo_tablespace=’UNDOTBS1′
*.user_dump_dest=’/u01/app/oracle/admin/iscdd/udump’

19. Melakukan proses backup
a. full backup

RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE TAG=full_backup;
Starting backup at 15-AUG-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=141 devtype=DISK
channel ORA_DISK_1: starting incremental level 0 datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u02/oradata/iscdd/system01.dbf
input datafile fno=00003 name=/u02/oradata/iscdd/sysaux01.dbf
input datafile fno=00005 name=/u02/oradata/iscdd/example01.dbf
input datafile fno=00002 name=/u02/oradata/iscdd/undotbs01.dbf
input datafile fno=00004 name=/u02/oradata/iscdd/users01.dbf
input datafile fno=00006 name=/u02/oradata/iscdd/rman_ts01.dbf
input datafile fno=00007 name=/u02/oradata/iscdd/rman_ts02.dbf
channel ORA_DISK_1: starting piece 1 at 15-AUG-08
channel ORA_DISK_1: finished piece 1 at 15-AUG-08
piece handle=/home/oracle/backup/ISCDD-id-2055978995-9.bkp tag=FULL_BACKUP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:04:22
Finished backup at 15-AUG-08
Starting Control File and SPFILE Autobackup at 15-AUG-08
piece handle=/u02/oradata/flash_recovery_area/ISCDD/autobackup/2008_08_15/o1_mf_s_662814329_4bb04dpp_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 15-AUG-08

b. backup incremental

RMAN> backup incremental level 1 tag ‘level_1′ database;
Starting backup at 19-AUG-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=144 devtype=DISK
channel ORA_DISK_1: starting incremental level 1 datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u02/oradata/iscdd/system01.dbf
input datafile fno=00003 name=/u02/oradata/iscdd/sysaux01.dbf
input datafile fno=00005 name=/u02/oradata/iscdd/example01.dbf
input datafile fno=00002 name=/u02/oradata/iscdd/undotbs01.dbf
input datafile fno=00004 name=/u02/oradata/iscdd/users01.dbf
input datafile fno=00006 name=/u02/oradata/iscdd/rman_ts01.dbf
input datafile fno=00007 name=/u02/oradata/iscdd/rman_ts02.dbf
channel ORA_DISK_1: starting piece 1 at 19-AUG-08
channel ORA_DISK_1: finished piece 1 at 19-AUG-08
piece handle=/home/oracle/backup/ISCDD-id-2055978995-15.bkp tag=LEVEL_1 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:46
Finished backup at 19-AUG-08
Starting Control File and SPFILE Autobackup at 19-AUG-08
piece handle=/u02/oradata/flash_recovery_area/ISCDD/autobackup/2008_08_19/o1_mf_s_663153295_4bnc521b_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 19-AUG-08

c. strategi backup
Minggu : full backup ? backup incremental level 0
Senin – Sabtu : block – block yang diperbarui ? backup
incremental level 1
Hari minggu :

RMAN> backup incremental level 0 tag ‘level_0′ database;
Hari senin – sabtu :
RMAN> backup incremental level 1 tag ‘level_1′ database;

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

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