Database resetlogs opsiyonu ile her açıldığında, sistem tarafından otomatik olarak yeni bir incarnation numarası verilmektedir. Resetlogs sonrasında database’ in yeni bir versiyonu oluşmakta buda yeni bir incarnation ile ifade edilmektedir diyebiliriz. Resetlog sonrasında database’ deki archive ve tüm redologlar clear edildikden sonra log sequence değerleride resetlenerek 1’ den başlayacak şekilde set edilmiş olacaktır. Eğer database’ de incomplete recovery yapıyor iseniz bu opsiyonu kullanmak durumunda kalacaksınız demektir.
Burdaki bir önemli nokta, database resetlogs opsiyonu ile açıldıkdan sonra, resetlogs öncesine ait backupları kullanamayacağınız dır. Eğer bu tarz bir durumla kalırsanız ne yapılması gerektiğinden bu yazımda bahsediyor olacağım.
Birde aşağıdaki grafik üzerinden anlatmaya çalışayım. Bakalım ne kadar başarılı olacağım. t anında bir tablo create ettiğimizi düşünelim. Sonra t1 anında tablomuzu truncate ettiğimizi sonrasında yetmeyip t2 anında tabloyu drop ettiğimizi sonrasında da t4 anında olayı farkettiğimizi düşünelim. Sonrasında önce t1 anına dönelim sonrada t anına tabloyu create ettiğimiz ana gitmeye çalışalım.
Teste başlamadan önce database de şu anda var olan incarnation durumuna bir bakalım ;
===> select incarnation#, to_char(resetlogs_time,’dd/mm/yyyy hh24:mi:ss’)resetlogs_time, resetlogs_change# from v$database_incarnation
INCARNATION# RESETLOGS_TIME RESETLOGS_CHANGE#
———— ——————- —————–
1 07/07/2014 05:38:47 1
2 28/03/2016 13:56:47 1594143
3 12/04/2016 22:07:26 9560869
4 12/04/2016 22:29:15 9576103
4 rows selected.
Grafikde belirttiğimiz anlardaki senaryolarımızı gerçekleştirelim;
• t anı = tablomuzu truncate edelim ;
===> create table kamil.incarnation_test as select * from dba_tables
Table created.
===> select count(*) from kamil.incarnation_test
COUNT(*)
———-
2409
1 row selected.
===> select current_scn from v$database
CURRENT_SCN
———–
9959014
1 row selected.
===> select to_char(sysdate,’dd/mm/yyyy hh24:mi:ss’) date_time from v$database
DATE_TIME
——————-
18/04/2016 16:26:43
1 row selected.
• t1 anı = tablomuzu truncate edelim;
===> truncate table kamil.incarnation_test
Table truncated.
===> select to_char(sysdate,’dd/mm/yyyy hh24:mi:ss’) date_time from dual
DATE_TIME
——————-
18/04/2016 16:50:22
1 row selected.
===> select current_scn from v$database
CURRENT_SCN
———–
9960313
1 row selected.
• t2 anı = tablomuzu drop edelim;
===> select current_scn from v$database
CURRENT_SCN
———–
9972348
1 row selected.
===> drop table kamil.incarnation_test
Table dropped.
===> select to_char(sysdate,’dd/mm/yyyy hh24:mi:ss’) date_time from dual
DATE_TIME
——————-
18/04/2016 21:40:19
1 row selected.
===> select current_scn from v$database
CURRENT_SCN
———–
9972365
1 row selected.
t4 anı ve birde farkettik ki tablomuz drop olmuş. Buna da bir zaman atayalım isterseniz;
===> select to_char(sysdate,’dd/mm/yyyy hh24:mi:ss’) date_time from dual
DATE_TIME
——————-
18/04/2016 22:19:05
1 row selected.
Şimdi yaptıklarımızı toparlayalım;
İlk restore’ umuza başlayalım. Truncate anına dönelim bakalım ;
[SID=t1:oracle@Redhat70 ~]$ rman target /
Recovery Manager: Release 12.1.0.2.0 – Production on Mon Apr 18 16:55:34 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: T1 (DBID=2718473307, not open)
RMAN> RUN {
2> ALLOCATE CHANNEL ch01 TYPE ‘SBT_TAPE’ ;
3> ALLOCATE CHANNEL ch02 TYPE ‘SBT_TAPE’ ;
4> ALLOCATE CHANNEL ch03 TYPE ‘SBT_TAPE’ ;
5> ALLOCATE CHANNEL ch04 TYPE ‘SBT_TAPE’ ;
6> ALLOCATE CHANNEL ch05 TYPE ‘SBT_TAPE’ ;
7> ALLOCATE CHANNEL ch06 TYPE ‘SBT_TAPE’ ;
8> ALLOCATE CHANNEL ch07 TYPE ‘SBT_TAPE’ ;
9> ALLOCATE CHANNEL ch08 TYPE ‘SBT_TAPE’ ;
10> ALLOCATE CHANNEL ch09 TYPE ‘SBT_TAPE’ ;
11> ALLOCATE CHANNEL ch10 TYPE ‘SBT_TAPE’ ;
12> ALLOCATE CHANNEL ch11 TYPE ‘SBT_TAPE’ ;
13> ALLOCATE CHANNEL ch12 TYPE ‘SBT_TAPE’ ;
14> ALLOCATE CHANNEL ch13 TYPE ‘SBT_TAPE’ ;
15> set until time “to_date(’18/04/2016 16:51:00′,’dd/mm/yyyy hh24:mi:ss’)”;
16>restore database;
17> recover database;
18> alter database open resetlogs;
19> }
using target database control file instead of recovery catalog
allocated channel: ch01
channel ch01: SID=6 device type=SBT_TAPE
channel ch01: Veritas NetBackup for Oracle – Release 7.5 (2013061020)
allocated channel: ch02
channel ch02: SID=200 device type=SBT_TAPE
channel ch02: Veritas NetBackup for Oracle – Release 7.5 (2013061020)
…
…
allocated channel: ch13
channel ch13: SID=9 device type=SBT_TAPE
channel ch13: Veritas NetBackup for Oracle – Release 7.5 (2013061020)
executing command: SET until clause
Starting restore at 18-APR-16
flashing back control file to SCN 9959787
channel ch01: starting datafile backup set restore
channel ch01: specifying datafile(s) to restore from backup set
channel ch01: restoring datafile 00011 to /u02/oradata/t1/t1_pdb1/example01.dbf
channel ch01: reading from backup piece bk_920_1_909505699
channel ch02: starting datafile backup set restore
…
channel ch04: restoring datafile 00012 to /u02/oradata/t1/t1_pdb1/dbadata01.dbf
channel ch04: reading from backup piece bk_922_1_909505699
…
…
channel ch10: restored backup piece 1
channel ch10: restore complete, elapsed time: 00:00:35
Finished restore at 18-APR-16
Starting recover at 18-APR-16
starting media recovery
archived log for thread 1 with sequence 604 is already on disk as file /u04/oradata/t1/arch/t1_arch_1_604_909008955.arc
…
archived log for thread 1 with sequence 623 is already on disk as file /u04/oradata/t1/arch/t1_arch_1_623_909008955.arc
archived log for thread 1 with sequence 624 is already on disk as file /u02/oradata/t1/redo03.log
archived log for thread 1 with sequence 625 is already on disk as file /u02/oradata/t1/redo01.log
archived log file name=/u04/oradata/t1/arch/t1_arch_1_604_909008955.arc thread=1 sequence=604
…
archived log file name=/u04/oradata/t1/arch/t1_arch_1_623_909008955.arc thread=1 sequence=623
media recovery complete, elapsed time: 00:00:02
Finished recover at 18-APR-16
Statement processed
released channel: ch01
released channel: ch02
released channel: ch03
released channel: ch04
released channel: ch05
released channel: ch06
released channel: ch07
released channel: ch08
released channel: ch09
released channel: ch10
released channel: ch11
released channel: ch12
released channel: ch13
RMAN>
Incomplete recovery yaptık ve database’ imizi resetlogs opsiyonu ile açtık. Şimdi database’ in incarnation durumunu tekrar sorgulayalım.
===> select incarnation#, to_char(resetlogs_time,’dd/mm/yyyy hh24:mi:ss’)resetlogs_time, resetlogs_change# from v$database_incarnation
INCARNATION# RESETLOGS_TIME RESETLOGS_CHANGE#
———— ——————- —————–
1 07/07/2014 05:38:47 1
2 28/03/2016 13:56:47 1594143
3 12/04/2016 22:07:26 9560869
4 12/04/2016 22:29:15 9576103
5 18/04/2016 16:56:31 9960186
5 rows selected.
5 nolu incarnation numarasını bizim restore’ umuz ile almış oldu. Tablomuzu kontrol ediyoruz ;
===> select count(*) from kamil.incarnation_test
COUNT(*)
———-
0
1 row selected.
Datamız yok. Şimdi ilk restore yaptığımız andan öncesine (örneğin 16:30’ a) tekrar bir restore deneyelim ;
[SID=t1:oracle@Redhat70 ~]$ rman target /
Recovery Manager: Release 12.1.0.2.0 – Production on Mon Apr 18 22:36:51 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: T1 (DBID=2718473307, not open)
RMAN> RUN {
2> ALLOCATE CHANNEL ch01 TYPE ‘SBT_TAPE’ ;
3> ALLOCATE CHANNEL ch02 TYPE ‘SBT_TAPE’ ;
4> ALLOCATE CHANNEL ch03 TYPE ‘SBT_TAPE’ ;
5> ALLOCATE CHANNEL ch04 TYPE ‘SBT_TAPE’ ;
6> ALLOCATE CHANNEL ch05 TYPE ‘SBT_TAPE’ ;
7> ALLOCATE CHANNEL ch06 TYPE ‘SBT_TAPE’ ;
8> ALLOCATE CHANNEL ch07 TYPE ‘SBT_TAPE’ ;
9> ALLOCATE CHANNEL ch08 TYPE ‘SBT_TAPE’ ;
10> ALLOCATE CHANNEL ch09 TYPE ‘SBT_TAPE’ ;
11> ALLOCATE CHANNEL ch10 TYPE ‘SBT_TAPE’ ;
12> ALLOCATE CHANNEL ch11 TYPE ‘SBT_TAPE’ ;
13> ALLOCATE CHANNEL ch12 TYPE ‘SBT_TAPE’ ;
14> ALLOCATE CHANNEL ch13 TYPE ‘SBT_TAPE’ ;
15> set until time “to_date(’18/04/2016 16:30:00′,’dd/mm/yyyy hh24:mi:ss’)”;
restore database;
16> 17> recover database;
18> alter database open resetlogs;
19> }
using target database control file instead of recovery catalog
allocated channel: ch01
channel ch01: SID=6 device type=SBT_TAPE
channel ch01: Veritas NetBackup for Oracle – Release 7.5 (2013061020)
…
allocated channel: ch13
channel ch13: SID=9 device type=SBT_TAPE
channel ch13: Veritas NetBackup for Oracle – Release 7.5 (2013061020)
executing command: SET until clause
Starting restore at 18-APR-16
released channel: ch01
…
released channel: ch13
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 04/18/2016 22:37:27
RMAN-20207: UNTIL TIME or RECOVERY WINDOW is before RESETLOGS time
RMAN>
Ve işte beklenen hata, oluşturmaya çalıştığımız durumu nihayet yakaladık. Şimdi bu durumdan nasıl kurtulacağımıza bakalım ;
Incarnation numaralarını rman üzerinden de kontrol edebiliriz;
RMAN> list incarnation;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
——- ——- ——– —————- — ———- ———-
1 1 T1 2718473307 PARENT 1 07-JUL-14
2 2 T1 2718473307 PARENT 1594143 28-MAR-16
3 3 T1 2718473307 PARENT 9560869 12-APR-16
4 4 T1 2718473307 PARENT 9576103 12-APR-16
5 5 T1 2718473307 CURRENT 9960186 18-APR-16
Şimdi son resetlogs’ un hemen öncesine restore etmeye çalıştığımız için son resetlogs öncesine de database’ i resetlesek sorunumuz çözülmüş olacaktır, deneyelim görelim ;
RMAN> RESET DATABASE TO INCARNATION 4;
database reset to incarnation 4
Restore’ u tekrar deneyelim ;
RMAN> RUN {
2> ALLOCATE CHANNEL ch01 TYPE ‘SBT_TAPE’ ;
3> ALLOCATE CHANNEL ch02 TYPE ‘SBT_TAPE’ ;
4> ALLOCATE CHANNEL ch03 TYPE ‘SBT_TAPE’ ;
5> ALLOCATE CHANNEL ch04 TYPE ‘SBT_TAPE’ ;
6> ALLOCATE CHANNEL ch05 TYPE ‘SBT_TAPE’ ;
7> ALLOCATE CHANNEL ch06 TYPE ‘SBT_TAPE’ ;
8> ALLOCATE CHANNEL ch07 TYPE ‘SBT_TAPE’ ;
9> ALLOCATE CHANNEL ch08 TYPE ‘SBT_TAPE’ ;
10> ALLOCATE CHANNEL ch09 TYPE ‘SBT_TAPE’ ;
11> ALLOCATE CHANNEL ch10 TYPE ‘SBT_TAPE’ ;
12> ALLOCATE CHANNEL ch11 TYPE ‘SBT_TAPE’ ;
13> ALLOCATE CHANNEL ch12 TYPE ‘SBT_TAPE’ ;
14> ALLOCATE CHANNEL ch13 TYPE ‘SBT_TAPE’ ;
15> set until time “to_date(’18/04/2016 16:30:00′,’dd/mm/yyyy hh24:mi:ss’)”;
16> restore database;
17> recover database;
18> alter database open resetlogs;
19> }
allocated channel: ch01
channel ch01: SID=6 device type=SBT_TAPE
channel ch01: Veritas NetBackup for Oracle – Release 7.5 (2013061020)
….
allocated channel: ch13
channel ch13: SID=9 device type=SBT_TAPE
channel ch13: Veritas NetBackup for Oracle – Release 7.5 (2013061020)
executing command: SET until clause
Starting restore at 18-APR-16
flashing back control file to SCN 9959429
channel ch01: starting datafile backup set restore
channel ch01: specifying datafile(s) to restore from backup set
….
channel ch10: starting datafile backup set restore
channel ch10: specifying datafile(s) to restore from backup set
channel ch10: restoring datafile 00001 to /u02/oradata/t1/system01.dbf
channel ch10: reading from backup piece bk_923_1_909505699
channel ch02: piece handle=bk_930_1_909505700 tag=T1_INCARNATION1_180420161627
channel ch02: restored backup piece 1
…..
channel ch06: restore complete, elapsed time: 00:01:05
channel ch09: piece handle=bk_924_1_909505699 tag=T1_INCARNATION1_180420161627
channel ch09: restored backup piece 1
channel ch09: restore complete, elapsed time: 00:01:15
channel ch10: piece handle=bk_923_1_909505699 tag=T1_INCARNATION1_180420161627
channel ch10: restored backup piece 1
channel ch10: restore complete, elapsed time: 00:01:25
Finished restore at 18-APR-16
Starting recover at 18-APR-16
starting media recovery
archived log for thread 1 with sequence 604 is already on disk as file /u04/oradata/t1/arch/t1_arch_1_604_909008955.arc
…..
archived log for thread 1 with sequence 611 is already on disk as file /u04/oradata/t1/arch/t1_arch_1_611_909008955.arc
archived log file name=/u04/oradata/t1/arch/t1_arch_1_604_909008955.arc thread=1 sequence=604
….
archived log file name=/u04/oradata/t1/arch/t1_arch_1_611_909008955.arc thread=1 sequence=611
media recovery complete, elapsed time: 00:00:01
Finished recover at 18-APR-16
Statement processed
released channel: ch01
released channel: ch02
released channel: ch03
released channel: ch04
released channel: ch05
released channel: ch06
released channel: ch07
released channel: ch08
released channel: ch09
released channel: ch10
released channel: ch11
released channel: ch12
released channel: ch13
RMAN>
Restore’ u başarıyla yapmış olduk. Şimdi son durumda database’ deki incarnation durumunu tekrar sorgulayalım;
===> select incarnation#, to_char(resetlogs_time,’dd/mm/yyyy hh24:mi:ss’)resetlogs_time, resetlogs_change# from v$database_incarnation
INCARNATION# RESETLOGS_TIME RESETLOGS_CHANGE#
———— ——————- —————–
1 07/07/2014 05:38:47 1
2 28/03/2016 13:56:47 1594143
3 12/04/2016 22:07:26 9560869
4 12/04/2016 22:29:15 9576103
5 18/04/2016 16:56:31 9960186
6 18/04/2016 22:58:22 9959455
6 rows selected.
Tablomuzu kontrol edelim ;
===> select count(*) from kamil.incarnation_test
COUNT(*)
———-
2409
1 row selected.