Oracle RMAN ile Tape Ünitesindeki Eski bir Backupa dönme

Merhaba Arkadaşlar,

Bu yazımda sizlere Oracle da yıllar önce Tape backup ünitesine alınmış bir backup ı adım adım dönmeyi anlatacağım.

 

Özellikle bankacılık ve sigortacılık sektöründeki Kurumlarda yıllık backuplar saklanır 10 yıl kadar. Gerçektende geçmişe dönük backuplar gerekebiliyor. Açılan bir dava sonucu yıllar öncesinin verisinin ne olduğunu bilmek için o zamana yakın bir backup dönmek gerekebiliyor. İşte bende bu yazımda daha önce bir sigortacılık müşterimde EMC Data domain e alınmış 2014 yılına ait bir backup ı indirmek gerekiyordu onu indirip dökümante etmiştim şimdi sizlerle paylaşıyorum.

Oracle RMAN toolunu bilmeyenlerin önceden yazdığım bu makalemi okumalarını tavsiye ederim.

Bu senaryoda full backup ı alınan bir veritabanının farklı bir sunucu üzerine full backup ını restore edilmesi ve ardından veritabanının belirlenenen bir zamana kadar recover edilmesi adımlarını içermektedir. Restore için hazırlanan sunucuda Oracle instance sını başlatıp Production veritabanına RMAN aracılığıyla bağlanıp son alınan backup restore edilmiştir. Restore işlemini tamamladıktan sonra veritabanını açmak için belirlenen bir zamana kadar alınan archivelog lar recover edilip Veritabanı open resetlogs seçeneği ile açılmıştır.

 

1)Backup ın indirileceği Yeni Sunucuda Oracle Software Only kurulur ve Gerekli env değişkenleri oracle kullanıcısı için profile dan aşağıdaki gibi ayarlanır.

 

export ORACLE_BASE=/oracle/app

export ORACLE_HOME=/oracle/app/product/11.2.0.3/db

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P9

export ORACLE_SID=ORCL

export PATH=$ORACLE_HOME/bin:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin:$PATH

 

2) Instancesın başlatılabilmesi için $ORACLE_HOME/dbs altında pfile oluşturulur.

oracle:deveci01:/oracle/app/product/11.2.0.3/db/dbs:>cat initORCL.ora 

db_name='ORCL'

db_unique_name='ORCLYENI'

memory_target=12G

processes = 1500

audit_file_dest='/oracle/app/admin/ORCLYENI'

audit_trail ='db'

db_block_size=8192

db_domain=''

diagnostic_dest='/oracle/app'

dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'

open_cursors=500

remote_login_passwordfile='EXCLUSIVE'

undo_tablespace='UNDOTBS1'

control_files='+DATA/ORCLYENI/CONTROLFILE/current.256.849957761'

compatible='11.2.0.0.0'


3) Instance nomount modda oluşturulan pfile kullanılarak açılır.

SQL> startup nomount pfile=’ initORCL.ora’;

ORACLE instance started.

 

Total System Global Area 1.7364E+10 bytes

Fixed Size                  1359424 bytes

Variable Size            1254834624 bytes

Database Buffers         1.6106E+10 bytes

Redo Buffers                2097152 bytes


 

4) RMAN e bağlanıp , Catalog veritabanına bağlanıp Production veritabanından alınan o tarihe ait Controlfile backup I sorgulanır.

SELECT session_recid,TO_CHAR (start_time, 'DD-MM-YYYY  HH24:MI:SS') start_time,         input_type,         status,         ROUND (elapsed_seconds / 3600, 1) time_hr,INPUT_BYTES/1024/1024/1024 IN_GB,OUTPUT_BYTES/1024/1024/1024  OUT_GB ,OUTPUT_DEVICE_TYPE FROM

v$rman_backup_job_details   WHERE START_TIME > SYSDATE - 5 ORDER BY start_time desc;

 

select * from v$rman_output where session_recid=729;   

 

 

oracle:deveci01:/home/users/oracle:>rman

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jun 11 16:42:06 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect catalog rman/rman123@CAT

set DBID=2402765690

connect target /connected to recovery catalog database

RMAN> 

executing command: SET DBID

database name is "ORCL" and DBID is 2402765690

RMAN> 

connected to target database: ORCL (DBID=2402765690, not open)

 

RMAN> list backup of controlfile completed between "to_date('11.06.2014','dd.mm.yyyy hh24')" and "to_date('12.06.2014','dd.mm.yyyy hh24')";

 

List of Backup Sets

===================

          .

          .

         .

BS Key  Type LV Size       Device Type Elapsed Time Completion Time

------- ---- -- ---------- ----------- ------------ ---------------

1576770235 Full    24.25M     SBT_TAPE    00:00:04     11-JUN-14      

        BP Key: 1576770276   Status: AVAILABLE  Compressed: NO  Tag: TAG20140611T110055

        Handle: c-2402765690-20140611-04   Media: V10DBBackup.002

  Control File Included: Ckp SCN: 68825716813   Ckp time: 11-JUN-14

 

5. Dördüncü adımda bulunan o tarihe ait Controlfile backup ı aşağıdaki gibi restore edilir. Bu adımda restore edilirken EMC Data domain ünitesine ait NSR Server bilgisi ve Client bilgisi aşağıdaki gibi girilir. Böylece RMAN gidip Tape ünitesinde handle bilgisi belirtilen 2014 tarihine ait Controlfile ı indirecek.

run

{

ALLOCATE CHANNEL CH1 DEVICE TYPE 'SBT_TAPE';

send 'NSR_ENV=(NSR_CLIENT=deveci01.localdomain,NSR_SERVER=TAPE_SERVER.localdomain) ';

restore controlfile from 'c-2402765690-20140611-04';

RELEASE CHANNEL CH1;

}


 

6. O tarihe ait controlfile restore edildikten sonra Database restore işlemi başlatılır. Filesystem den alınan full backup ASM e restore edilebilmesi için Datafile ların yeni isme set edilmesi gerekmektedir. Bunun için aşağıdaki sorgudan gelen Datafilelara ASM formatında isim verilir. Aşağıdaki scripte yeni Restore edilecek path bilgisi kırmızı belirtilen yerdeki gibi verilirse script otomatik üretecektir.

select file_name,file_id from dba_data_files;
set head off pages 0 feed off echo off verify off
set lines 200
select 'SET NEWNAME FOR DATAFILE ' || FILE# || ' TO ''' || '+DATA/ORCLYENI/DATAFILE/' || substr(name,instr(name,'/',-1)+1) || ''';' from v$datafile;

 

Örnek bir Datafile convert işlemi aşağıdaki gibidir.

 

set newname for datafile 102 to '+DATA/ORCLYENI/DATAFILE/harita04.dba';

set newname for datafile 99 to '+DATA/ORCLYENI/DATAFILE/users14.dbf';

set newname for datafile 98 to '+DATA/ORCLYENI/DATAFILE/users13.dbf';

set newname for datafile 97 to '+DATA/ORCLYENI/DATAFILE/users12.dbf';

 

7. Datafile ların tamamı ASM göre convert edilmiş şeklini içeren restore database scripti hazırladıktan sonra Bu Restore işleminin kesintisiz çalışabilmesi için Restore scriptinin nohup ile çalıştırılması gerekmektedir. Scripti içeren RestoreORCL.rman dosyasının içeriği aşağıdaki gibidir. Bu adımda da yine datafile lar restore edilirken EMC Data domain ünitesine ait NSR Server bilgisi ve Client bilgisi aşağıdaki gibi girilir.

oracle:deveci01:/home/users/oracle:>cat RestoreORCL.rman

connect catalog rman/rman123@CAT

set DBID=2402765690

connect target /

run

{

ALLOCATE CHANNEL CH1 TYPE 'SBT_TAPE';

ALLOCATE CHANNEL CH2 TYPE 'SBT_TAPE';

ALLOCATE CHANNEL CH3 TYPE 'SBT_TAPE';

ALLOCATE CHANNEL CH4 TYPE 'SBT_TAPE';

ALLOCATE CHANNEL CH5 TYPE 'SBT_TAPE';

ALLOCATE CHANNEL CH6 TYPE 'SBT_TAPE';

ALLOCATE CHANNEL CH7 TYPE 'SBT_TAPE';

ALLOCATE CHANNEL CH8 TYPE 'SBT_TAPE';

ALLOCATE CHANNEL CH9 TYPE 'SBT_TAPE';

ALLOCATE CHANNEL CH10 TYPE 'SBT_TAPE';

ALLOCATE CHANNEL CH11 TYPE 'SBT_TAPE';

ALLOCATE CHANNEL CH12 TYPE 'SBT_TAPE';

ALLOCATE CHANNEL CH13 TYPE 'SBT_TAPE';

ALLOCATE CHANNEL CH14 TYPE 'SBT_TAPE';

ALLOCATE CHANNEL CH15 TYPE 'SBT_TAPE';

ALLOCATE CHANNEL CH16 TYPE 'SBT_TAPE';

send 'NSR_ENV=(NSR_CLIENT=deveci01.localdomain,NSR_SERVER=TAPE_SERVER.localdomain) ';

set newname for datafile 102 to '+DATA/ORCLYENI/DATAFILE/harita04.dba';

set newname for datafile 99 to '+DATA/ORCLYENI/DATAFILE/users14.dbf';

set newname for datafile 98 to '+DATA/ORCLYENI/DATAFILE/users13.dbf';

set newname for datafile 97 to '+DATA/ORCLYENI/DATAFILE/users12.dbf';

set newname for datafile 96 to '+DATA/ORCLYENI/DATAFILE/tsevam01.dba';

set newname for datafile 93 to '+DATA/ORCLYENI/DATAFILE/users11.dbf';

set newname for datafile 92 to '+DATA/ORCLYENI/DATAFILE/users10.dbf';

set newname for datafile 91 to '+DATA/ORCLYENI/DATAFILE/users09.dbf';

set newname for datafile 90 to '+DATA/ORCLYENI/DATAFILE/users08.dbf';

set newname for datafile 87 to '+DATA/ORCLYENI/DATAFILE/users07.dbf';

set newname for datafile 86 to '+DATA/ORCLYENI/DATAFILE/users06.dbf';

set newname for datafile 85 to '+DATA/ORCLYENI/DATAFILE/users05.dbf';

set newname for datafile 84 to '+DATA/ORCLYENI/DATAFILE/users04.dbf';

set newname for datafile 83 to '+DATA/ORCLYENI/DATAFILE/harita03.dba';

set newname for datafile 82 to '+DATA/ORCLYENI/DATAFILE/iapro_harita18.dba';

set newname for datafile 81 to '+DATA/ORCLYENI/DATAFILE/iapro_harita19.dba';

set newname for datafile 80 to '+DATA/ORCLYENI/DATAFILE/iapro_harita20.dba';

set newname for datafile 79 to '+DATA/ORCLYENI/DATAFILE/system02.dbf';

set newname for datafile 78 to '+DATA/ORCLYENI/DATAFILE/deveci_41.dba';

set newname for datafile 77 to '+DATA/ORCLYENI/DATAFILE/deveci_40.dba';

set newname for datafile 76 to '+DATA/ORCLYENI/DATAFILE/deveci_39.dba';

set newname for datafile 75 to '+DATA/ORCLYENI/DATAFILE/undotbs03.dbf';

set newname for datafile 74 to '+DATA/ORCLYENI/DATAFILE/undotbs02.dbf';

set newname for datafile 73 to '+DATA/ORCLYENI/DATAFILE/users03.dbf';

set newname for datafile 72 to '+DATA/ORCLYENI/DATAFILE/users02.dbf';

set newname for datafile 71 to '+DATA/ORCLYENI/DATAFILE/iapro_harita17.dba';

set newname for datafile 70 to '+DATA/ORCLYENI/DATAFILE/deveci_37.dba';

set newname for datafile 69 to '+DATA/ORCLYENI/DATAFILE/deveci_38.dba';

set newname for datafile 68 to '+DATA/ORCLYENI/DATAFILE/iapro_harita15.dba';

set newname for datafile 67 to '+DATA/ORCLYENI/DATAFILE/iapro_harita16.dba';

set newname for datafile 66 to '+DATA/ORCLYENI/DATAFILE/iapro_deveci03.dba';

set newname for datafile 65 to '+DATA/ORCLYENI/DATAFILE/iapro_deveci04.dba';

set newname for datafile 64 to '+DATA/ORCLYENI/DATAFILE/iapro_harita13.dba';

set newname for datafile 63 to '+DATA/ORCLYENI/DATAFILE/iapro_harita14.dba';

set newname for datafile 62 to '+DATA/ORCLYENI/DATAFILE/iapro_harita11.dba';

set newname for datafile 61 to '+DATA/ORCLYENI/DATAFILE/iapro_harita12.dba';

set newname for datafile 60 to '+DATA/ORCLYENI/DATAFILE/iapro_harita10.dba';

set newname for datafile 59 to '+DATA/ORCLYENI/DATAFILE/iapro_harita08.dba';

set newname for datafile 58 to '+DATA/ORCLYENI/DATAFILE/iapro_harita09.dba';

set newname for datafile 57 to '+DATA/ORCLYENI/DATAFILE/iapro_harita07.dba';

set newname for datafile 56 to '+DATA/ORCLYENI/DATAFILE/iapro_harita06.dba';

set newname for datafile 55 to '+DATA/ORCLYENI/DATAFILE/iapro_harita05.dba';

set newname for datafile 54 to '+DATA/ORCLYENI/DATAFILE/iapro_harita04.dba';

set newname for datafile 53 to '+DATA/ORCLYENI/DATAFILE/iapro_harita03.dba';

set newname for datafile 52 to '+DATA/ORCLYENI/DATAFILE/INFOMAP_05.dbf';

set newname for datafile 51 to '+DATA/ORCLYENI/DATAFILE/INFOMAP_04.dbf';

set newname for datafile 50 to '+DATA/ORCLYENI/DATAFILE/INFOMAP_03.dbf';

set newname for datafile 49 to '+DATA/ORCLYENI/DATAFILE/INFOMAP_02.dbf';

set newname for datafile 48 to '+DATA/ORCLYENI/DATAFILE/deveci_36.dba';

set newname for datafile 47 to '+DATA/ORCLYENI/DATAFILE/deveci_35.dba';

set newname for datafile 46 to '+DATA/ORCLYENI/DATAFILE/deveci_34.dba';

set newname for datafile 45 to '+DATA/ORCLYENI/DATAFILE/deveci_33.dba';

set newname for datafile 44 to '+DATA/ORCLYENI/DATAFILE/deveci_32.dba';

set newname for datafile 43 to '+DATA/ORCLYENI/DATAFILE/deveci_31.dba';

set newname for datafile 42 to '+DATA/ORCLYENI/DATAFILE/deveci_30.dba';

set newname for datafile 41 to '+DATA/ORCLYENI/DATAFILE/INFOMAP_01.dbf';

set newname for datafile 40 to '+DATA/ORCLYENI/DATAFILE/iapro_harita02.dba';

set newname for datafile 39 to '+DATA/ORCLYENI/DATAFILE/iapro_harita01.dba';

set newname for datafile 38 to '+DATA/ORCLYENI/DATAFILE/iapro_deveci02.dba';

set newname for datafile 37 to '+DATA/ORCLYENI/DATAFILE/iapro_deveci01.dba';

set newname for datafile 36 to '+DATA/ORCLYENI/DATAFILE/harita02.dba';

set newname for datafile 35 to '+DATA/ORCLYENI/DATAFILE/harita01.dba';

set newname for datafile 34 to '+DATA/ORCLYENI/DATAFILE/pmdata01.dba';

set newname for datafile 33 to '+DATA/ORCLYENI/DATAFILE/deveci_29.dba';

set newname for datafile 32 to '+DATA/ORCLYENI/DATAFILE/deveci_28.dba';

set newname for datafile 31 to '+DATA/ORCLYENI/DATAFILE/deveci_27.dba';

set newname for datafile 30 to '+DATA/ORCLYENI/DATAFILE/deveci_26.dba';

set newname for datafile 29 to '+DATA/ORCLYENI/DATAFILE/deveci_25.dba';

set newname for datafile 28 to '+DATA/ORCLYENI/DATAFILE/deveci_24.dba';

set newname for datafile 27 to '+DATA/ORCLYENI/DATAFILE/deveci_23.dba';

set newname for datafile 26 to '+DATA/ORCLYENI/DATAFILE/deveci_22.dba';

set newname for datafile 25 to '+DATA/ORCLYENI/DATAFILE/deveci_21.dba';

set newname for datafile 24 to '+DATA/ORCLYENI/DATAFILE/deveci_20.dba';

set newname for datafile 23 to '+DATA/ORCLYENI/DATAFILE/deveci_19.dba';

set newname for datafile 22 to '+DATA/ORCLYENI/DATAFILE/deveci_18.dba';

set newname for datafile 21 to '+DATA/ORCLYENI/DATAFILE/deveci_17.dba';

set newname for datafile 20 to '+DATA/ORCLYENI/DATAFILE/deveci_16.dba';

set newname for datafile 19 to '+DATA/ORCLYENI/DATAFILE/deveci_15.dba';

set newname for datafile 18 to '+DATA/ORCLYENI/DATAFILE/deveci_14.dba';

set newname for datafile 17 to '+DATA/ORCLYENI/DATAFILE/deveci_13.dba';

set newname for datafile 16 to '+DATA/ORCLYENI/DATAFILE/deveci_12.dba';

set newname for datafile 15 to '+DATA/ORCLYENI/DATAFILE/deveci_11.dba';

set newname for datafile 14 to '+DATA/ORCLYENI/DATAFILE/deveci_10.dba';

set newname for datafile 13 to '+DATA/ORCLYENI/DATAFILE/deveci_09.dba';

set newname for datafile 12 to '+DATA/ORCLYENI/DATAFILE/deveci_08.dba';

set newname for datafile 11 to '+DATA/ORCLYENI/DATAFILE/deveci_07.dba';

set newname for datafile 10 to '+DATA/ORCLYENI/DATAFILE/deveci_06.dba';

set newname for datafile 9 to '+DATA/ORCLYENI/DATAFILE/deveci_05.dba';

set newname for datafile 8 to '+DATA/ORCLYENI/DATAFILE/deveci_04.dba';

set newname for datafile 7 to '+DATA/ORCLYENI/DATAFILE/deveci_03.dba';

set newname for datafile 6 to '+DATA/ORCLYENI/DATAFILE/deveci_02.dba';

set newname for datafile 5 to '+DATA/ORCLYENI/DATAFILE/deveci.dba';

set newname for datafile 4 to '+DATA/ORCLYENI/DATAFILE/users01.dbf';

set newname for datafile 3 to '+DATA/ORCLYENI/DATAFILE/undotbs01.dbf';

set newname for datafile 2 to '+DATA/ORCLYENI/DATAFILE/sysaux01.dbf';

set newname for datafile 1 to '+DATA/ORCLYENI/DATAFILE/system01.dbf';

restore database;

switch datafile all;

RELEASE CHANNEL CH1;

RELEASE CHANNEL CH2;

RELEASE CHANNEL CH3;

RELEASE CHANNEL CH4;

RELEASE CHANNEL CH5;

RELEASE CHANNEL CH6;

RELEASE CHANNEL CH7;

RELEASE CHANNEL CH8;

RELEASE CHANNEL CH9;

RELEASE CHANNEL CH10;

RELEASE CHANNEL CH11;

RELEASE CHANNEL CH12;

RELEASE CHANNEL CH13;

RELEASE CHANNEL CH14;

RELEASE CHANNEL CH15;

RELEASE CHANNEL CH16;

}

oracle:deveci01:/home/users/oracle:>


 

 

8.Restore scripti aşağıdaki gibi Nohup ile çalıştırılır.

nohup rman cmdfile RestoreORCL.rman log RestoreORCL.rman.log &

 

9.Restore işleminin gerçekleştiğini görmek için 8.adımdaki log dosyası görüntülenerek durum izlenebilir.

oracle:deveci01:/home/users/oracle:>vi RestoreORCL.rman.log 

"RestoreORCL.rman.log" 979 lines, 49631 characters 

Recovery Manager: Release 11.2.0.3.0 - Production on Wed Jun 11 14:22:43 2014

allocated channel: CH16

channel CH16: SID=1565 device type=SBT_TAPE

channel CH16: NMDA Oracle v1.6.0

sent command to channel: CH16

executing command: SET NEWNAME

Starting restore at 11-JUN-14

 

channel CH1: starting datafile backup set restore

channel CH1: specifying datafile(s) to restore from backup set

channel CH1: restoring datafile 00004 to +DATA/ORCLYENI/DATAFILE/users01.dbf

channel CH1: reading from backup piece rman_ORCL_inc0_1795_1_849916841

channel CH2: starting datafile backup set restore

channel CH2: specifying datafile(s) to restore from backup set

channel CH2: restoring datafile 00072 to +DATA/ORCLYENI/DATAFILE/users02.dbf

channel CH2: reading from backup piece rman_ORCL_inc0_1796_1_849916841

channel CH3: starting datafile backup set restore

channel CH3: specifying datafile(s) to restore from backup set

channel CH3: restoring datafile 00003 to +DATA/ORCLYENI/DATAFILE/undotbs01.dbf

channel CH3: reading from backup piece rman_ORCL_inc0_1794_1_849916841

channel CH4: starting datafile backup set restore

channel CH4: specifying datafile(s) to restore from backup set

channel CH4: restoring datafile 00086 to +DATA/ORCLYENI/DATAFILE/users06.dbf

channel CH4: reading from backup piece rman_ORCL_inc0_1792_1_849916841

channel CH5: starting datafile backup set restore

channel CH5: specifying datafile(s) to restore from backup set

channel CH5: restoring datafile 00075 to +DATA/ORCLYENI/DATAFILE/undotbs03.dbf

channel CH5: reading from backup piece rman_ORCL_inc0_1791_1_849916841

channel CH6: starting datafile backup set restore

channel CH6: specifying datafile(s) to restore from backup set

channel CH6: restoring datafile 00085 to +DATA/ORCLYENI/DATAFILE/users05.dbf

channel CH6: reading from backup piece rman_ORCL_inc0_1793_1_849916841

channel CH7: starting datafile backup set restore

channel CH7: specifying datafile(s) to restore from backup set

channel CH7: restoring datafile 00074 to +DATA/ORCLYENI/DATAFILE/undotbs02.dbf

channel CH7: reading from backup piece rman_ORCL_inc0_1798_1_849920088

channel CH8: starting datafile backup set restore

channel CH8: specifying datafile(s) to restore from backup set

channel CH8: restoring datafile 00073 to +DATA/ORCLYENI/DATAFILE/users03.dbf

channel CH8: reading from backup piece rman_ORCL_inc0_1797_1_849920088

channel CH9: starting datafile backup set restore

channel CH9: specifying datafile(s) to restore from backup set

channel CH9: restoring datafile 00063 to +DATA/ORCLYENI/DATAFILE/iapro_harita14.dba

channel CH9: reading from backup piece rman_ORCL_inc0_1801_1_849920345

channel CH10: starting datafile backup set restore

channel CH10: specifying datafile(s) to restore from backup set

channel CH10: restoring datafile 00084 to +DATA/ORCLYENI/DATAFILE/users04.dbf

channel CH10: reading from backup piece rman_ORCL_inc0_1799_1_849920124

channel CH11: starting datafile backup set restore

channel CH11: specifying datafile(s) to restore from backup set

channel CH11: restoring datafile 00064 to +DATA/ORCLYENI/DATAFILE/iapro_harita13.dba

channel CH11: reading from backup piece rman_ORCL_inc0_1802_1_849920352

channel CH12: starting datafile backup set restore

channel CH12: specifying datafile(s) to restore from backup set

channel CH12: restoring datafile 00061 to +DATA/ORCLYENI/DATAFILE/iapro_harita12.dba

channel CH12: reading from backup piece rman_ORCL_inc0_1800_1_849920330

channel CH13: starting datafile backup set restore

channel CH13: specifying datafile(s) to restore from backup set

channel CH13: restoring datafile 00036 to +DATA/ORCLYENI/DATAFILE/harita02.dba

channel CH13: reading from backup piece rman_ORCL_inc0_1807_1_849923187

channel CH14: starting datafile backup set restore

channel CH14: specifying datafile(s) to restore from backup set

channel CH14: restoring datafile 00035 to +DATA/ORCLYENI/DATAFILE/harita01.dba

channel CH14: reading from backup piece rman_ORCL_inc0_1806_1_849923180

channel CH15: starting datafile backup set restore

channel CH15: specifying datafile(s) to restore from backup set

channel CH15: restoring datafile 00039 to +DATA/ORCLYENI/DATAFILE/iapro_harita01.dba

channel CH15: reading from backup piece rman_ORCL_inc0_1808_1_849923195

channel CH16: starting datafile backup set restore

channel CH16: specifying datafile(s) to restore from backup set

channel CH16: restoring datafile 00067 to +DATA/ORCLYENI/DATAFILE/iapro_harita16.dba

channel CH16: reading from backup piece rman_ORCL_inc0_1803_1_849923083

channel CH15: piece handle=rman_ORCL_inc0_1808_1_849923195 tag=ORCL_INC0_BACKUP

channel CH15: restored backup piece 1

channel CH15: restore complete, elapsed time: 00:21:37

input datafile copy RECID=416 STAMP=849978593 file name=+DATA/ORCLYENI/datafile/users14.dbf

datafile 102 switched to datafile copy

input datafile copy RECID=417 STAMP=849978593 file name=+DATA/ORCLYENI/datafile/harita04.dba

released channel: CH1

released channel: CH16

 

Recovery Manager complete.

 

10. Restore işlemi bittikten sonra Veritabanı Recover edilir. Bu adımda Recover edilecek Archivelog sequence I belirlenir.

RMAN> 

database name is "ORCL" and DBID is 2402765690

 

RMAN> connect target /

 

connected to target database: ORCL (DBID=2402765690, not open)

 

RMAN> list backup of archivelog from time 'sysdate-776';

 

 

List of Backup Sets

===================

        .

        .

BS Key  Size       Device Type Elapsed Time Completion Time

------- ---------- ----------- ------------ ---------------

1580180174 505.25M    SBT_TAPE    00:02:48     11-JUN-14

        BP Key: 1580180177   Status: AVAILABLE  Compressed: NO  Tag: ORCL_ARC_BACKUP

        Handle: rman_ORCL_arc_1907_1_849985279   Media: V10DBBackup.003

 

  List of Archived Logs in backup set 1580180174

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

  ---- ------- ---------- --------- ---------- ---------

  1    123     68829021957 11-JUN-14 68829259069 11-JUN-14

 

BS Key  Size       Device Type Elapsed Time Completion Time

------- ---------- ----------- ------------ ---------------

1581845193 436.00M    SBT_TAPE    00:00:23     11-JUN-14

        BP Key: 1581845306   Status: AVAILABLE  Compressed: NO  Tag: ORCL_ARC_BACKUP

        Handle: rman_ORCL_arc_1909_1_849999650   Media: V10DBBackup.003

 

  List of Archived Logs in backup set 1581845193

  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time

  ---- ------- ---------- --------- ---------- ---------

  1    124     68829259069 11-JUN-14 68829533952 11-JUN-14

 

11. Veritabanını Recover etmek için 124 Sequence li Archivelog a kadar Veritabanı Recover edilir. Recover işleminin aksamaması için Nohup ile çalıştırmamız gerekmektedir. Bunun için Recover scriptini RecoverORCL.rman dosyasına kaydettim. RecoverORCL.rman dosyasının içeriği aşağıdaki gibidir. Bu adımdada  recover edilirken EMC Data domain ünitesine ait NSR Server bilgisi ve Client bilgisi aşağıdaki gibi girilir.

oracle:deveci01:/home/users/oracle:>more RecoverORCL.rman

connect catalog rman/rman123@CAT

set DBID=2402765690

connect target /

run

{

ALLOCATE CHANNEL CH1 TYPE 'SBT_TAPE';

ALLOCATE CHANNEL CH2 TYPE 'SBT_TAPE';

ALLOCATE CHANNEL CH3 TYPE 'SBT_TAPE';

ALLOCATE CHANNEL CH4 TYPE 'SBT_TAPE';

ALLOCATE CHANNEL CH5 TYPE 'SBT_TAPE';

ALLOCATE CHANNEL CH6 TYPE 'SBT_TAPE';

ALLOCATE CHANNEL CH7 TYPE 'SBT_TAPE';

ALLOCATE CHANNEL CH8 TYPE 'SBT_TAPE';

send 'NSR_ENV=(NSR_CLIENT=deveci01.localdomain,NSR_SERVER=TAPE_SERVER.localdomain) ';

set until sequence 124 thread 1;

recover database;

RELEASE CHANNEL CH1;

RELEASE CHANNEL CH2;

RELEASE CHANNEL CH3;

RELEASE CHANNEL CH4;

RELEASE CHANNEL CH5;

RELEASE CHANNEL CH6;

RELEASE CHANNEL CH7;

RELEASE CHANNEL CH8;

}

 

12. RecoverORCL.rman dosyasını nohup ile aşağıdaki gibi çalıştırabiliriz.

nohup rman cmdfile RecoverORCL.rman log RecoverORCL.rman.log &

 

13. Nohup ile çalıştırdığımızda çıktısını aşağıdaki gibi görebiliriz.

oracle:deveci01:/home/users/oracle:>cat RecoverORCL.rman.log 

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Jun 12 09:37:37 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to recovery catalog database

executing command: SET DBID

database name is "ORCL" and DBID is 2402765690

connected to target database: ORCL (DBID=2402765690, not open)

allocated channel: CH8

channel CH8: SID=3 device type=SBT_TAPE

channel CH8: NMDA Oracle v1.6.0

 

sent command to channel: CH1

executing command: SET until clause

Starting recover at 12-JUN-14

starting media recovery

channel CH2: restored backup piece 1

channel CH2: restore complete, elapsed time: 00:00:15

channel CH1: piece handle=rman_ORCL_arc_1821_1_849927663 tag=ORCL_ARC_BACKUP

channel CH1: restored backup piece 1

channel CH1: restore complete, elapsed time: 00:00:25

archived log file name=/oracle/app/product/11.2.0.3/db/dbs/arch1_119_848780439.dbf thread=1 sequence=119

archived log file name=/oracle/app/product/11.2.0.3/db/dbs/arch1_120_848780439.dbf thread=1 sequence=120

channel CH3: piece handle=rman_ORCL_arc_1903_1_849956438 tag=ORCL_ARC_BACKUP

channel CH3: restored backup piece 1

channel CH3: restore complete, elapsed time: 00:00:30

archived log file name=/oracle/app/product/11.2.0.3/db/dbs/arch1_121_848780439.dbf thread=1 sequence=121

channel CH4: piece handle=rman_ORCL_arc_1905_1_849970843 tag=ORCL_ARC_BACKUP

channel CH4: restored backup piece 1

channel CH4: restore complete, elapsed time: 00:00:33

archived log file name=/oracle/app/product/11.2.0.3/db/dbs/arch1_122_848780439.dbf thread=1 sequence=122

channel CH5: piece handle=rman_ORCL_arc_1907_1_849985279 tag=ORCL_ARC_BACKUP

channel CH5: restored backup piece 1

channel CH5: restore complete, elapsed time: 00:00:36

archived log file name=/oracle/app/product/11.2.0.3/db/dbs/arch1_123_848780439.dbf thread=1 sequence=123

media recovery complete, elapsed time: 00:00:03

Finished recover at 12-JUN-14

released channel: CH1

Recovery Manager complete.

 

13. Veritabanını Recover ettikten sonra open resetlogs ile açmadan önce Controlfile da görünen aşağıdaki olmayan Logfile ları temizlememiz lazım.

SQL> select member from v$logfile;

 

MEMBER

--------------------------------------------------------------------------------

+DATA/ORCLYENI/onlinelog/redo0101.log

+DATA/ORCLYENI/onlinelog/redo0102.log

+DATA/ORCLYENI/onlinelog/redo0201.log

+DATA/ORCLYENI/onlinelog/redo0202.log

+DATA/ORCLYENI/onlinelog/redo0301.log

/gecici/data/oradata/ORCL/redo0302.log

/gecici/data/oradata/ORCL/redo0303.log

/gecici/data/oradata/ORCL/redo0304.log

/gecici/data/oradata/ORCL/redo0305.log

/gecici/data/oradata/ORCL/redo0306.log

/gecici/data/oradata/ORCL/redo0307.log

 

MEMBER

--------------------------------------------------------------------------------

+DATA/ORCLYENI/onlinelog/redo0302.log

/gecici/data/oradata/ORCL/redo0308.log

 

13 rows selected.

 

SQL>

 

 

SQL> ALTER DATABASE RENAME FILE  '/gecici/data/oradata/ORCL/redo0302.log' to '+DATA/ORCLYENI/onlinelog/redo0303.log';

 

Database altered.

SQL> ALTER DATABASE RENAME FILE  '/gecici/data/oradata/ORCL/redo0303.log' to '+DATA/ORCLYENI/onlinelog/redo0304.log';

Database altered.

SQL> ALTER DATABASE RENAME FILE '/gecici/data/oradata/ORCL/redo0304.log' to '+DATA/ORCLYENI/ONLINELOG/redo0305.log';

Database altered.

SQL> ALTER DATABASE RENAME FILE '/gecici/data/oradata/ORCL/redo0305.log' to '+DATA/ORCLYENI/ONLINELOG/redo0306.log';

Database altered.

 

SQL> ALTER DATABASE RENAME FILE '/gecici/data/oradata/ORCL/redo0306.log' to '+DATA/ORCLYENI/ONLINELOG/redo0307.log';

Database altered.

 

SQL> ALTER DATABASE RENAME FILE '/gecici/data/oradata/ORCL/redo0307.log' to '+DATA/ORCLYENI/ONLINELOG/redo0308.log';

Database altered.

 

SQL> ALTER DATABASE RENAME FILE '/gecici/data/oradata/ORCL/redo0308.log' to '+DATA/ORCLYENI/ONLINELOG/redo0309.log';

Database altered.

 

  1. Tüm Logfile ların rename edildiğini aşağıdaki gibi teyit edilir.
 

SQL> select member from v$logfile;

MEMBER

--------------------------------------------------------------------------------

+DATA/ORCLYENI/onlinelog/redo0101.log

+DATA/ORCLYENI/onlinelog/redo0102.log

+DATA/ORCLYENI/onlinelog/redo0201.log

+DATA/ORCLYENI/onlinelog/redo0202.log

+DATA/ORCLYENI/onlinelog/redo0301.log

+DATA/ORCLYENI/onlinelog/redo0303.log

+DATA/ORCLYENI/onlinelog/redo0304.log

+DATA/ORCLYENI/onlinelog/redo0305.log

+DATA/ORCLYENI/onlinelog/redo0306.log

+DATA/ORCLYENI/onlinelog/redo0307.log

+DATA/ORCLYENI/onlinelog/redo0308.log

 

MEMBER

--------------------------------------------------------------------------------

+DATA/ORCLYENI/onlinelog/redo0302.log

+DATA/ORCLYENI/onlinelog/redo0309.log

13 rows selected.

 

14. Son olarak açmaya çalıştığımız zaman logfile group ın birini clear etmemizi istiyor.

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-00392: log 5 of thread 1 is being cleared, operation not allowed

ORA-00312: online log 5 thread 1: '+DATA/ORCLYENI/onlinelog/redo0304.log'

Bu log file grubunu aşağıdaki gibi Clear ettim.

SQL> alter database clear unarchived logfile group 5;

Database altered.


 

15. Tüm adımları yaptıktan sonra veritabanını aşağıdaki gibi open resetlogs ile açıyoruz.

SQL> alter database open resetlogs;

Database altered.

 

16. Veritabanı açıldıktan sonra aşağıdaki gibi tempfile eklenir.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA/ORCLYENI/DATAFILE/temp01.dbf' SIZE 33554416K AUTOEXTEND ON NEXT 20M MAXSIZE UNLIMITED;

Tablespace altered.

 

Böylece bu yazınında sonuna gelmiş bulunmaktayım bir sonraki yazıda görüşmek dileğiyle esen kalın…

 

Oracle Exadata SQL Server Goldengate Weblogic EBS ve Linux konusunda aşağıdaki konularda 7×24 Uzman Danışmanlara yada Eğitimlere mi İhtiyacınız var mehmet.deveci@gridgroup.com.tr adresine mail atarak Bizimle iletişime geçebilirsiniz.

– Oracle Veritabanı Danışmanlığı
– Oracle Veritabanı Bakım ve Destek
– Exadata Danışmanlığı
– Exadata Bakım ve Destek
– SQL Server Veritabanı Danışmanlığı
– SQL Server Veritabanı Bakım ve Destek
– Goldengate Danışmanlığı
– Goldengate Bakım ve Destek
– Linux Danışmanlığı
– Linux Bakım ve Destek
– Oracle EBS Danışmanlığı
– Oracle EBS Bakım ve Destek
– Weblogic Danışmanlığı
– Weblogic Bakım ve Destek
– Oracle Veritabanı Eğitimleri
– Oracle VM Server Danışmanlığı
– Oracle VM Server Bakım ve Destek
– Oracle EPPM Danışmanlığı
– Oracle EPPM Bakım ve Destek
– Oracle Primavera Danışmanlığı
– Oracle Primavera Bakım ve Destek
– Oracle Eğitimleri
– SQL Server Eğitimleri
– Goldengate Eğitimleri
– Exadata Eğitimleri
– Linux Eğitimleri
– Oracle EBS Eğitimleri
– Oracle VM Server Eğitimleri
– Weblogic Eğitimleri
– Oracle EPPM Eğitimleri
– Oracle Primavera Eğitimleri

Mehmet Salih Deveci

I am Founder of IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience. I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource. I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks. I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients. If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress mehmetsalih.deveci@outlook.com. -                                                                                                                                                                                                                                                 - Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.