RMAN Backup dan Recovery Oracle 10g (1)

9 Sep

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;

em02

JUAL ebook 10g Real Application Clusters on vmware DETAIL

8 Responses to “RMAN Backup dan Recovery Oracle 10g (1)”

  1. Anton February 21, 2009 at 5:15 pm #

    Halo Pak Agus,

    Artikel bapak sangat berguna bagi saya. karena saya beginer di oracle. Saat ini saya sedang mencari cara untuk membuat backup di 1 instance pecah ke 2 policy retention. Misalnya daily backup retain 7 hari dan weekly backup retain 60 hari.. apakah ini dimungkinkan dalam rman?.
    dan saya memakai nocatalog mode.

    Saya sangat memerlukan info ini..Sebelumnya banyak terimakasih

    • setijoagus February 23, 2009 at 12:52 am #

      terima kasih kalau Artikel yang ada berguna, saya juga beginer di oracle kok,
      spt kata tag line saya “diatas langit ada langit lagi, dibawah neraka ada neraka lagi”,
      artinya : bisa jadi ada yang nggak aku ngerti, ternyata ada orang lain yang lebih ngerti atau sebaliknya

      menurut saya : daily backup retain 7 hari dan weekly backup retain 60 hari, kok nggak dimungkinkan sebab
      rman configuration cuma mengenal 1 command, tp bisa diakali dengan memisahkan / memindahkan file – file
      backup weekly tsb ke disk lain spy nggak terhapus, any idea ?…………………

  2. Sayed Azri July 20, 2009 at 6:56 am #

    Salam,

    saya dari malaysia. juga merupakan oracle DBA. boleh bapak komen tentang script berikut utk Backup Linux (guna export DumpFile):

    ***********
    export ORACLE_HOME=/u01/app/oracle/product/10.2.0/rdbms
    export PATH=$ORACLE_HOME$\bin:$PATH
    export ORACLE_SID=
    exp ‘/as sysdba’ FULL=y
    FILE=/mnt/backup/dump/fullexp.dmp CONSISTENT=y STATISTICS=none
    LOG=/mnt/backup/dump/log/fullexp.log
    ************

    kerana ianya kurang berhasil. Apa perlu ditambah lagi?

    Terima kasih.

    • setijoagus July 22, 2009 at 1:13 am #

      salam juga, apa khabar Bapak, semoga baik selalu.
      Karena Bapak tidak menyertakan pesan errornya, sehingga saya nggak bisa komentar lebih banyak. Berikut script yang biasa saya pakai untuk backup dmp

      ================================================
      ORACLE_HOSTNAME=orcl1.localdomain; export ORACLE_HOSTNAME
      ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
      ORACLE_HOME=$ORACLE_BASE/product/11.1.0/db_1; export ORACLE_HOME
      ORACLE_SID=orcl1; export ORACLE_SID
      ORACLE_TERM=xterm; export ORACLE_TERM
      PATH=/usr/sbin:$PATH; export PATH
      PATH=$ORACLE_HOME/bin:$PATH; export PATH
      LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
      CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
      exp userid=scott/tiger file=/backup/01snrbjm_scott.dmp log=/backup/01snrbjm_scott.txt
      ================================================

      semoga bermanfaat

  3. Sayed Azri July 22, 2009 at 8:26 am #

    Salam,

    terima kasih diatas pertolongan dari bapak. saya lupa utk memasukan error tersebut. Ianya merupakan error dari Oracle Grid Control yang saya gunakan utk lancarkan RMAN script ke RHEL 4 update 6 (system HP RX6600 itanium).

    Script yang saya gunakan:
    backup incremental level 1 cumulative
    format ‘/mnt/backup/BICBJES/BICBJES_INC_%s:%T:%p.dbf’
    database tag ‘DAILY_DATAFILES’;
    backup
    format ‘/mnt/backup/BICBJES/BICBJES_INC_%s:%T:%p.arc’
    archivelog all not backed up tag ‘DAILY_ARCHIVE’
    delete input;
    backup
    format ‘/mnt/backup/BICBJES/BICBJES_INC_%s:%T:%p.ctl’
    current controlfile tag ‘DAILY_CONTROLFILE’;
    delete noprompt obsolete device type disk;

    Error tersebut:
    Code: 255
    Status: Failed
    Step ID: 1080067
    “Unable to perform the backup because the database is closed.”

    p/s: bagaimana pun, saya akan cuba dengan script yang bapak bagi.

    Terima kasih.

  4. Ericson Ginting September 24, 2009 at 7:35 am #

    Pak,

    bagaimana dengan resources CPU? Apakah selama ini terkuras habis? Atau berjalan dengan normal?

    Terima Kasih

    • setijoagus September 29, 2009 at 7:21 am #

      betul resources CPU, suka nggak suka pasti kepakai lebih, biasanya saya pilih jam yang tidak sibuk, dan dengan demikian DB oke – oke saja

  5. drehen April 29, 2013 at 6:25 am #

    Definitely take joy in what you’ve got here, thrilled you’re putting it around and what you
    have to say. Your article was excellently balanced between amusing and educational.

    I am inspired to trade theory and critical information with you.
    Your website is unquestionably one of the better blogs available
    right now.

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: