Konfigurasi RMAN dengan Catalog DB

July 25, 2013

Reading time ~4 minutes

Jika anda memiliki infrastruktur yang kompleks biasanya anda akan menggunakan teknologi backup yang tidak sederhana. Demikian juga jika anda menggunakan server IBM,HP-UX, dan Oracle sebagai pondasi sistem komputasi di perusahaan anda. Maka anda memerlukan teknologi backup yang mumpuni untuk menjamin keselamatan data anda dari segala resiko apapun. Maka dari itu Oracle menyediakan teknologi backup yang sudah cukup baik untuk memenuhi kebutuhan ini yaitu dengan menggunakan RMAN. RMAN (Oracle Recovery Manager) adalah teknologi backup yang dimiliki oleh Oracle, dan dapat bekerja sama dengan berbagai jenis backup hardware.

Tampa basa-basi mengungkapkan kelebihan dan kekurangan RMAN, maka mari kita mulai.

Skenario yang kita miliki kali ini adalah:

  • OS Linux
  • Menggunakan TSM backup device
  • Konsultan TSM akan memberikan backup parameter yang berlokasi di /opt/tivoli/tsm/client/oracle/bin64/tdpo.opt
  • Mereka juga akan mendefinisikan OS user yang akan mengeksekusi backup ini (sebaiknya user dengan privilage ke oracle database)
  • Backup dilakukan secara Harian, Mingguan, Bulanan
  • Retensi backup 14 hari untuk harian dan Mingguan 6 bulan untuk Bulanan.
  • Memiliki banyak oracle database yang berjalan dalam 1 physical server.

Langkah-langkah

Login sebagai oracle user, dan masuk kedalam Oracle SQL.

[sourcecode language=”shell”]HSCDEV2:orasmq 67> sqlplus "/as sysdba"[/sourcecode]

Cek tablespace Oracle

[sourcecode language=”sql”]col name format a30
col value format a30
select file_name
from dba_data_files;[/sourcecode]

Output:

[sourcecode language=”plain”]
FILE_NAME
——————————————————————————–
/oracle/SMQ/sapdata4/system_1/system.data1
/oracle/SMQ/sapdata4/sysaux_1/sysaux.data1
/oracle/SMQ/sapdata4/undo_1/undo.data1
/oracle/SMQ/sapdata1/sr3_1/sr3.data1
/oracle/SMQ/sapdata1/sr3_2/sr3.data2
/oracle/SMQ/sapdata1/sr3_3/sr3.data3
/oracle/SMQ/sapdata1/sr3_4/sr3.data4
/oracle/SMQ/sapdata1/sr3_5/sr3.data5
/oracle/SMQ/sapdata1/sr3_6/sr3.data6
/oracle/SMQ/sapdata1/sr3_7/sr3.data7
/oracle/SMQ/sapdata1/sr3_8/sr3.data8
[/sourcecode]

Cek blocksize:

[sourcecode language=”sql”]
select name, value
from gv$parameter
where name like ‘%block%’;
[/sourcecode]

Output:

[sourcecode language=”plain”]
NAME VALUE
—————————— ——————————
db_block_buffers 0
db_block_checksum TYPICAL
db_block_size 8192
db_file_multiblock_read_count 128
db_block_checking FALSE
[/sourcecode]

Buat tablespace khusus untuk catalog DB:

[sourcecode language=”sql”]
create tablespace cat_rman
datafile ‘/oracle/SMQ/sapdata1/cat_rman/cat-rman.dbf’ size 50M
autoextend on
blocksize 8192
force logging
default nocompress
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K
SEGMENT SPACE MANAGEMENT AUTO
online;
[/sourcecode]

Output:

[sourcecode language=”plain”]Tablespace created.[/sourcecode]

Cek tablespace yang sudah dibuat:

[sourcecode language=”sql”]SQL> select tablespace_name from dba_tablespaces;[/sourcecode]

Output:

[sourcecode language=”plain”]
TABLESPACE_NAME
——————————
SYSTEM
SYSAUX
PSAPUNDO
PSAPTEMP
PSAPSR3
PSAPSR3702
PSAPSR3USR
PSAPSR3DB
CAT_RMAN

9 rows selected.
[/sourcecode]

Buat user rman dengan password rm4n4l4y

[sourcecode language=”sql”]
create user rman
identified by rm4n4l4y
default tablespace cat_rman
temporary tablespace psaptemp
quota unlimited on cat_rman;
[/sourcecode]

Output:

[sourcecode language=”plain”]
User created.
[/sourcecode]

Beri privilege untuk user rman

[sourcecode language=”sql”]grant create session to rman;[/sourcecode]

Output:

[sourcecode language=”plain”]Grant succeeded.[/sourcecode]

Beri privilege recovery catalog untuk user rman

[sourcecode language=”sql”]grant recovery_catalog_owner to rman;[/sourcecode]

Output:

[sourcecode language=”plain”]Grant succeeded.[/sourcecode]

Beri privilege DBMS_STAT untuk rman

[sourcecode language=”sql”]grant execute on dbms_stats to rman;[/sourcecode]

Output:

[sourcecode language=”plain”]Grant succeeded.[/sourcecode]

Konfigurasi RMAN

Setelah selesai, exit dari sqlplus dan masuk kedalam RMAN environment.

[sourcecode language=”shell”]HSCDEV2:orasmq 73> rman target / catalog rman/if2w4RMAN[/sourcecode]

Buat catalog RMAN

[sourcecode language=”sql”]RMAN> create catalog;[/sourcecode]

Output:

[sourcecode language=”plain”]recovery catalog created[/sourcecode]

Register database RMAN kedalam catalog

[sourcecode language=”sql”]RMAN> register database;[/sourcecode]

Output:

[sourcecode language=”plain”]
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
[/sourcecode]

Check perlu di backup apa tidak:

[sourcecode language=”sql”]RMAN> report need backup;[/sourcecode]

Output:

[sourcecode language=”plain”]
RMAN retention policy will be applied to the command
RMAN retention policy is set to recovery window of 7 days
Report of files that must be backed up to satisfy 7 days recovery window
File Days Name
—- —– —————————————————–
1 12 /oracle/SMQ/sapdata4/system_1/system.data1
2 12 /oracle/SMQ/sapdata4/sysaux_1/sysaux.data1
3 12 /oracle/SMQ/sapdata4/undo_1/undo.data1
4 12 /oracle/SMQ/sapdata1/sr3_1/sr3.data1
5 12 /oracle/SMQ/sapdata1/sr3_2/sr3.data2
6 12 /oracle/SMQ/sapdata1/sr3_3/sr3.data3
7 12 /oracle/SMQ/sapdata1/sr3_4/sr3.data4
8 12 /oracle/SMQ/sapdata1/sr3_5/sr3.data5
9 12 /oracle/SMQ/sapdata1/sr3_6/sr3.data6
10 12 /oracle/SMQ/sapdata1/sr3_7/sr3.data7
11 12 /oracle/SMQ/sapdata1/sr3_8/sr3.data8
12 12 /oracle/SMQ/sapdata1/sr3_9/sr3.data9
13 12 /oracle/SMQ/sapdata2/sr3702_1/sr3702.data1
14 12 /oracle/SMQ/sapdata2/sr3702_2/sr3702.data2
15 12 /oracle/SMQ/sapdata2/sr3702_3/sr3702.data3
16 12 /oracle/SMQ/sapdata2/sr3702_4/sr3702.data4
17 12 /oracle/SMQ/sapdata2/sr3702_5/sr3702.data5
18 12 /oracle/SMQ/sapdata2/sr3702_6/sr3702.data6
19 12 /oracle/SMQ/sapdata2/sr3702_7/sr3702.data7
20 12 /oracle/SMQ/sapdata2/sr3702_8/sr3702.data8
21 12 /oracle/SMQ/sapdata4/sr3usr_1/sr3usr.data1
22 12 /oracle/SMQ/sapdata3/sr3db_1/sr3db.data1
23 12 /oracle/SMQ/sapdata3/sr3db_2/sr3db.data2
24 12 /oracle/SMQ/sapdata3/sr3db_3/sr3db.data3
[/sourcecode]

Konfigurasi RMAN
Beberapa langkah yang perlu dilakukan untuk mengkonfigurasi RMAN.

[sourcecode language=”sql”]
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 14 DAYS;
RMAN> show all;
[/sourcecode]

comments powered by Disqus