BASIC REPLICATION MASTER – VIEW (Materialized View) September 9, 2008
Posted by setijoagus in REPLICATION.Tags: ORACLE ADVANCE REPLIKASI, materialized view, Mate
2 comments
########################################
# Materialized View
# Setup di sisi MASTER
########################################
1. Materialized View Log at Master Site
[oracle@iscdd ~]$ sqlplus scott/tiger@iscdd
SQL> CREATE TABLE dept_ku AS SELECT * FROM dept;
SQL> CREATE MATERIALIZED VIEW LOG ON dept_ku;
CREATE MATERIALIZED VIEW LOG ON dept_ku
*
ERROR at line 1:
ORA-12014: table ‘DEPT_KU’ does not contain a primary key constraint
— Materialized view log tidak dapat dibuat pada tables tanpa primary key.
SQL> desc dept_ku;
Name Null? Type
—————————————– ——– —————————-
DEPTNO NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
SQL> ALTER TABLE dept_ku ADD CONSTRAINT pk_dept_ku PRIMARY KEY (deptno);
SQL> CREATE MATERIALIZED VIEW LOG ON dept_ku;
########################################
# Setup di sisi VIEW SITE
########################################
2. Set up Materialized View Site
[oracle@iscdd2 ~]$ sqlplus system@iscdd2
SQL> CREATE USER remote_user IDENTIFIED BY abc DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;
SQL> GRANT connect, resource, create materialized view, create database link TO remote_user;
3. Database Link
SQL> connect remote_user/abc@iscdd2
SQL> CREATE DATABASE LINK iscdd_ku CONNECT TO scott IDENTIFIED BY tiger USING ‘iscdd’;
4. Materialized View
SQL> CREATE MATERIALIZED VIEW mv1_ku REFRESH FAST AS SELECT * FROM scott.dept_ku@iscdd_ku;
5. Refresh Group
SQL> BEGIN
dbms_refresh.make(
name => ‘mv1_refgroup’,
list => ‘mv1_ku’,
next_date => sysdate,
—- 5 menit refresh
interval => ’sysdate + 1 / ((24*60)/5)’,
implicit_destroy => true,
lax => true);
END;
/
6. Refresh Manual
SQL> exec dbms_refresh.refresh(name => ‘mv1_refgroup’);
########################################
# TEST — TEST
########################################
########################################
# Master Site
########################################
7. test update di master site, hasil lihat di view site
[oracle@iscdd dbs]$ sqlplus scott/tiger@iscdd
SQL> select * from dept_ku;
DEPTNO DNAME LOC
———- ————– ————-
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> insert into dept_ku values(50,’IT’,'Lamongan’);
SQL> commit;
SQL> select to_char(sysdate , ‘Dy DD-Mon-YYYY HH24:MI:SS’) as “Time” from dual;
Time
————————
Mon 08-Sep-2008 21:42:09
SQL> select to_char(sysdate + 1/((24*60)/5), ‘Dy DD-Mon-YYYY HH24:MI:SS’) as “Time” from dual;
Time
————————
Mon 08-Sep-2008 21:47:09
########################################
# View Site
########################################
[oracle@iscdd2 ~]$ sqlplus remote_user/abc@iscdd2
SQL> select * from mv1_ku;
DEPTNO DNAME LOC
———- ————– ————-
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> select to_char(sysdate , ‘Dy DD-Mon-YYYY HH24:MI:SS’) as “Time” from dual;
Time
————————
Mon 08-Sep-2008 21:42:59
SQL> select to_char(sysdate , ‘Dy DD-Mon-YYYY HH24:MI:SS’) as “Time” from dual;
Time
————————
Mon 08-Sep-2008 21:50:32
SQL> select * from mv1_ku;
DEPTNO DNAME LOC
———- ————– ————-
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 IT Lamongan
RMAN Backup dan Recovery Oracle 10g (3) September 9, 2008
Posted by setijoagus in Oracle Backup, oracle.Tags: Backup, oracle, Recovery, RMAN
add a comment
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
RMAN Backup dan Recovery Oracle 10g (2) September 9, 2008
Posted by setijoagus in oracle.Tags: Backup, oracle, Recovery, RMAN
add a comment
20. Apa saja yang harus dibackup ?
a. semisal ada penambahan tablespace baru, kita dapat melakukan backup hanya pada tablespace baru itu saja.
[oracle@iscdd ~]$ sqlplus / as sysdba
SQL> create tablespace senin_ts
datafile ‘/u02/oradata/iscdd/senin_ts01.dbf’
size 2M
segment space management auto;
SQL> exit;
[oracle@iscdd ~]$ cd $ORACLE_HOME/bin
[oracle@iscdd bin]$ ./rman CATALOG rman_user/rman123@iscdd TARGET sys/ora@iscdd
RMAN> report need backup;
starting full resync of recovery catalog
full resync complete
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of files with less than 1 redundant backups
File #bkps Name
—- —– —————————————————–
8 0 /u02/oradata/iscdd/senin_ts01.dbf
RMAN> backup incremental level 1 database;
Starting backup at 19-AUG-08
using channel ORA_DISK_1
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
input datafile fno=00008 name=/u02/oradata/iscdd/senin_ts01.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-18.bkp tag=TAG20080819T093111 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
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_663154276_4bnd3pwb_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 19-AUG-08
b. Back Up Control Files
RMAN> backup current controlfile;
Starting backup at 19-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 19-AUG-08
channel ORA_DISK_1: finished piece 1 at 19-AUG-08
piece handle=/home/oracle/backup/ISCDD-id-2055978995-20.bkp tag=TAG20080819T093916 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:06
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_663154764_4bndlyw8_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 19-AUG-08
c. Back Up Archived Redo Log File dengan BACKUP ARCHIVELOG
RMAN> backup archivelog all;
Starting backup at 19-AUG-08
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=6 recid=3 stamp=662902743
input archive log thread=1 sequence=7 recid=4 stamp=662987905
input archive log thread=1 sequence=8 recid=5 stamp=663112810
input archive log thread=1 sequence=9 recid=6 stamp=663154809
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-22.bkp tag=TAG20080819T094010 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:58
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_663154869_4bndp8mq_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 19-AUG-08
d. Back Up Server Parameter Files
RMAN> BACKUP SPFILE;
Starting backup at 19-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 19-AUG-08
channel ORA_DISK_1: finished piece 1 at 19-AUG-08
piece handle=/home/oracle/backup/ISCDD-id-2055978995-24.bkp tag=TAG20080819T094220 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
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_663154943_4bndrlqh_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 19-AUG-08
e. Daftar backup.
RMAN> list backup summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
——- — – – ———– ————— ——- ——- ———- —
33 B F A DISK 15-AUG-08 1 1 NO TAG20080815T103941
63 B 0 A DISK 15-AUG-08 1 1 NO FULL_BACKUP
78 B F A DISK 15-AUG-08 1 1 NO TAG20080815T110529
109 B A A DISK 15-AUG-08 1 1 NO FULL_BACKUP2
110 B 0 A DISK 15-AUG-08 1 1 NO TAG20080815T111906
131 B A A DISK 15-AUG-08 1 1 NO FULL_BACKUP2
149 B F A DISK 15-AUG-08 1 1 NO TAG20080815T112329
209 B 1 A DISK 19-AUG-08 1 1 NO LEVEL_1
225 B F A DISK 19-AUG-08 1 1 NO TAG20080819T091455
248 B F A DISK 19-AUG-08 1 1 NO TAG20080819T092323
263 B 1 A DISK 19-AUG-08 1 1 NO TAG20080819T093111
284 B F A DISK 19-AUG-08 1 1 NO TAG20080819T093116
317 B F A DISK 19-AUG-08 1 1 NO TAG20080819T093916
334 B F A DISK 19-AUG-08 1 1 NO TAG20080819T093924
380 B A A DISK 19-AUG-08 1 1 NO TAG20080819T094010
402 B F A DISK 19-AUG-08 1 1 NO TAG20080819T094109
440 B F A DISK 19-AUG-08 1 1 NO TAG20080819T094220
461 B F A DISK 19-AUG-08 1 1 NO TAG20080819T094223
RMAN> list backup;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
33 Full 6.80M DISK 00:00:03 15-AUG-08
BP Key: 39 Status: AVAILABLE Compressed: NO Tag: TAG20080815T103941
Piece Name: /u02/oradata/flash_recovery_area/ISCDD/autobackup/2008_08_15/o1_mf_s_662812781_4b9ymzz2_.bkp
Control File Included: Ckp SCN: 545731 Ckp time: 15-AUG-08
SPFILE Included: Modification time: 15-AUG-08
BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
63 Incr 0 584.06M DISK 00:04:12 15-AUG-08
BP Key: 66 Status: AVAILABLE Compressed: NO Tag: FULL_BACKUP
Piece Name: /home/oracle/backup/ISCDD-id-2055978995-9.bkp
List of Datafiles in backup set 63
File LV Type Ckp SCN Ckp Time Name
—- — —- ———- ——— —-
1 0 Incr 547576 15-AUG-08 /u02/oradata/iscdd/system01.dbf
2 0 Incr 547576 15-AUG-08 /u02/oradata/iscdd/undotbs01.dbf
3 0 Incr 547576 15-AUG-08 /u02/oradata/iscdd/sysaux01.dbf
4 0 Incr 547576 15-AUG-08 /u02/oradata/iscdd/users01.dbf
5 0 Incr 547576 15-AUG-08 /u02/oradata/iscdd/example01.dbf
6 0 Incr 547576 15-AUG-08 /u02/oradata/iscdd/rman_ts01.dbf
7 0 Incr 547576 15-AUG-08 /u02/oradata/iscdd/rman_ts02.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
78 Full 6.80M DISK 00:00:03 15-AUG-08
BP Key: 86 Status: AVAILABLE Compressed: NO Tag: TAG20080815T110529
Piece Name: /u02/oradata/flash_recovery_area/ISCDD/autobackup/2008_08_15/o1_mf_s_662814329_4bb04dpp_.bkp
Control File Included: Ckp SCN: 547755 Ckp time: 15-AUG-08
SPFILE Included: Modification time: 15-AUG-08
RMAN> list backupset summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
——- — – – ———– ————— ——- ——- ———- —
33 B F A DISK 15-AUG-08 1 1 NO TAG20080815T103941
63 B 0 A DISK 15-AUG-08 1 1 NO FULL_BACKUP
78 B F A DISK 15-AUG-08 1 1 NO TAG20080815T110529
109 B A A DISK 15-AUG-08 1 1 NO FULL_BACKUP2
110 B 0 A DISK 15-AUG-08 1 1 NO TAG20080815T111906
131 B A A DISK 15-AUG-08 1 1 NO FULL_BACKUP2
149 B F A DISK 15-AUG-08 1 1 NO TAG20080815T112329
209 B 1 A DISK 19-AUG-08 1 1 NO LEVEL_1
225 B F A DISK 19-AUG-08 1 1 NO TAG20080819T091455
248 B F A DISK 19-AUG-08 1 1 NO TAG20080819T092323
263 B 1 A DISK 19-AUG-08 1 1 NO TAG20080819T093111
284 B F A DISK 19-AUG-08 1 1 NO TAG20080819T093116
317 B F A DISK 19-AUG-08 1 1 NO TAG20080819T093916
334 B F A DISK 19-AUG-08 1 1 NO TAG20080819T093924
380 B A A DISK 19-AUG-08 1 1 NO TAG20080819T094010
402 B F A DISK 19-AUG-08 1 1 NO TAG20080819T094109
440 B F A DISK 19-AUG-08 1 1 NO TAG20080819T094220
461 B F A DISK 19-AUG-08 1 1 NO TAG20080819T094223
RMAN> list backupset 33;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
33 Full 6.80M DISK 00:00:03 15-AUG-08
BP Key: 39 Status: AVAILABLE Compressed: NO Tag: TAG20080815T103941
Piece Name: /u02/oradata/flash_recovery_area/ISCDD/autobackup/2008_08_15/o1_mf_s_662812781_4b9ymzz2_.bkp
Control File Included: Ckp SCN: 545731 Ckp time: 15-AUG-08
SPFILE Included: Modification time: 15-AUG-08
21. Apakah ada yang sudah kuno ?
RMAN> report obsolete;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of obsolete backups and copies
Type Key Completion Time Filename/Handle
——————– —— —————— ——————–
Backup Set 33 15-AUG-08
Backup Piece 39 15-AUG-08 /u02/oradata/flash_recovery_area/ISCDD/autobackup/2008_08_15/o1_mf_s_662812781_4b9ymzz2_.bkp
Backup Set 63 15-AUG-08
Backup Piece 66 15-AUG-08 /home/oracle/backup/ISCDD-id-2055978995-9.bkp
Backup Set 78 15-AUG-08
Backup Piece 86 15-AUG-08 /u02/oradata/flash_recovery_area/ISCDD/autobackup/2008_08_15/o1_mf_s_662814329_4bb04dpp_.bkp
Backup Set 109 15-AUG-08
Backup Piece 113 15-AUG-08 /home/oracle/backup/ISCDD-id-2055978995-11.bkp
Backup Set 149 15-AUG-08
Backup Piece 151 15-AUG-08 /u02/oradata/flash_recovery_area/ISCDD/autobackup/2008_08_15/o1_mf_s_662815409_4bb1642n_.bkp
Backup Set 225 19-AUG-08
Backup Piece 233 19-AUG-08 /u02/oradata/flash_recovery_area/ISCDD/autobackup/2008_08_19/o1_mf_s_663153295_4bnc521b_.bkp
Backup Set 248 19-AUG-08
Backup Piece 251 19-AUG-08 /u02/oradata/flash_recovery_area/ISCDD/autobackup/2008_08_19/o1_mf_s_663153803_4bncny0s_.bkp
Backup Set 284 19-AUG-08
Backup Piece 293 19-AUG-08 /u02/oradata/flash_recovery_area/ISCDD/autobackup/2008_08_19/o1_mf_s_663154276_4bnd3pwb_.bkp
Backup Set 317 19-AUG-08
Backup Piece 320 19-AUG-08 /home/oracle/backup/ISCDD-id-2055978995-20.bkp
Backup Set 334 19-AUG-08
Backup Piece 336 19-AUG-08 /u02/oradata/flash_recovery_area/ISCDD/autobackup/2008_08_19/o1_mf_s_663154764_4bndlyw8_.bkp
Backup Set 402 19-AUG-08
Backup Piece 407 19-AUG-08 /u02/oradata/flash_recovery_area/ISCDD/autobackup/2008_08_19/o1_mf_s_663154869_4bndp8mq_.bkp
Backup Set 440 19-AUG-08
Backup Piece 443 19-AUG-08 /home/oracle/backup/ISCDD-id-2055978995-24.bkp
22. Hapus backup, khususnya yang obsolete
RMAN> delete noprompt obsolete;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
using channel ORA_DISK_1
Deleting the following obsolete backups and copies:
Type Key Completion Time Filename/Handle
——————– —— —————— ——————–
Backup Set 33 15-AUG-08
Backup Piece 39 15-AUG-08 /u02/oradata/flash_recovery_area/ISCDD/autobackup/2008_08_15/o1_mf_s_662812781_4b9ymzz2_.bkp
Backup Set 63 15-AUG-08
Backup Piece 66 15-AUG-08 /home/oracle/backup/ISCDD-id-2055978995-9.bkp
Backup Set 78 15-AUG-08
Backup Piece 86 15-AUG-08 /u02/oradata/flash_recovery_area/ISCDD/autobackup/2008_08_15/o1_mf_s_662814329_4bb04dpp_.bkp
Backup Set 109 15-AUG-08
Backup Piece 113 15-AUG-08 /home/oracle/backup/ISCDD-id-2055978995-11.bkp
Backup Set 149 15-AUG-08
Backup Piece 151 15-AUG-08 /u02/oradata/flash_recovery_area/ISCDD/autobackup/2008_08_15/o1_mf_s_662815409_4bb1642n_.bkp
Backup Set 225 19-AUG-08
Backup Piece 233 19-AUG-08 /u02/oradata/flash_recovery_area/ISCDD/autobackup/2008_08_19/o1_mf_s_663153295_4bnc521b_.bkp
Backup Set 248 19-AUG-08
Backup Piece 251 19-AUG-08 /u02/oradata/flash_recovery_area/ISCDD/autobackup/2008_08_19/o1_mf_s_663153803_4bncny0s_.bkp
Backup Set 284 19-AUG-08
Backup Piece 293 19-AUG-08 /u02/oradata/flash_recovery_area/ISCDD/autobackup/2008_08_19/o1_mf_s_663154276_4bnd3pwb_.bkp
Backup Set 317 19-AUG-08
Backup Piece 320 19-AUG-08 /home/oracle/backup/ISCDD-id-2055978995-20.bkp
Backup Set 334 19-AUG-08
Backup Piece 336 19-AUG-08 /u02/oradata/flash_recovery_area/ISCDD/autobackup/2008_08_19/o1_mf_s_663154764_4bndlyw8_.bkp
Backup Set 402 19-AUG-08
Backup Piece 407 19-AUG-08 /u02/oradata/flash_recovery_area/ISCDD/autobackup/2008_08_19/o1_mf_s_663154869_4bndp8mq_.bkp
Backup Set 440 19-AUG-08
Backup Piece 443 19-AUG-08 /home/oracle/backup/ISCDD-id-2055978995-24.bkp
deleted backup piece
backup piece handle=/u02/oradata/flash_recovery_area/ISCDD/autobackup/2008_08_15/o1_mf_s_662812781_4b9ymzz2_.bkp recid=6 stamp=662812783
deleted backup piece
backup piece handle=/home/oracle/backup/ISCDD-id-2055978995-9.bkp recid=7 stamp=662814065
deleted backup piece
backup piece handle=/u02/oradata/flash_recovery_area/ISCDD/autobackup/2008_08_15/o1_mf_s_662814329_4bb04dpp_.bkp recid=8 stamp=662814332
deleted backup piece
backup piece handle=/home/oracle/backup/ISCDD-id-2055978995-11.bkp recid=9 stamp=662815120
deleted backup piece
backup piece handle=/u02/oradata/flash_recovery_area/ISCDD/autobackup/2008_08_15/o1_mf_s_662815409_4bb1642n_.bkp recid=12 stamp=662815412
deleted backup piece
backup piece handle=/u02/oradata/flash_recovery_area/ISCDD/autobackup/2008_08_19/o1_mf_s_663153295_4bnc521b_.bkp recid=14 stamp=663153298
deleted backup piece
backup piece handle=/u02/oradata/flash_recovery_area/ISCDD/autobackup/2008_08_19/o1_mf_s_663153803_4bncny0s_.bkp recid=15 stamp=663153806
deleted backup piece
backup piece handle=/u02/oradata/flash_recovery_area/ISCDD/autobackup/2008_08_19/o1_mf_s_663154276_4bnd3pwb_.bkp recid=17 stamp=663154278
deleted backup piece
backup piece handle=/home/oracle/backup/ISCDD-id-2055978995-20.bkp recid=18 stamp=663154759
deleted backup piece
backup piece handle=/u02/oradata/flash_recovery_area/ISCDD/autobackup/2008_08_19/o1_mf_s_663154764_4bndlyw8_.bkp recid=19 stamp=663154766
deleted backup piece
backup piece handle=/u02/oradata/flash_recovery_area/ISCDD/autobackup/2008_08_19/o1_mf_s_663154869_4bndp8mq_.bkp recid=21 stamp=663154872
deleted backup piece
backup piece handle=/home/oracle/backup/ISCDD-id-2055978995-24.bkp recid=22 stamp=663154941
Deleted 12 objects
RMAN> report obsolete;
RMAN retention policy will be applied to the command
RMAN retention policy is set to recovery window of 2 days
no obsolete backups found
23. Mencatat DBID yang akan digunakan untuk recovery
SQL> select dbid from v$database;
DBID
———-
2055978995
RMAN Backup dan Recovery Oracle 10g (1) September 9, 2008
Posted by setijoagus in Oracle Backup, oracle.Tags: Backup, oracle, Recovery, RMAN
7 comments
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;
