Oracle 12c ile birlikte dba’ ler olarak aslında bizim en çok beklediğimiz bikaç özellikden biride tablo bazında recover işlemini yapabilmekti. Bu aslında çokda karşılaştığımız “Nasıl yani sadece bir tabloyu dönemiyormusunuz” gibi sorularada cevap olmuş oldu. Ama burda şunu da söylemeden geçemiycem umarım developerların bu özellikden çok da haberleri olmaz. Tabiki çalıştığım veya tanıdığım çok iyi yazılımcılarda var ama olmayanlarda var. Dolayısıyla her yapılan hata sonrasında bu tarz restore taleplerininde kaçınılmaz gibi gözüküyor.
Örneğimize geçmeden önce tablo bazından restore derken temelde neleri kastediyoruz ve ksıtlarımız neler kısaca bunlardan bahsedelim;
• Bir tabloyu restore edebileceğimiz gibi sadece bir partitionı da restore edebiliriz,
• Recover edilen tabloyu yeni bir isimle başka bir tabloya veya bir partitiona insert edebiliriz,
• Recover etmeye çalıştığımız tablonun exportunu alıp database’ de import etmeyebiliriz de,
• Bütün bu işlemleri yapabilmemiz için database archive modde ve read/write olarak açılmış olmalıdır,
• Recover edeceğimiz tabloya ait kullanılabilir bir RMAN backupımız olmalıdır.
Bu işlemi aynı zamanda bir point in time recover işlemi olarak da düşünebilirsiniz dolayısıyla biz point in time recover yaparken nasıl SCN, spesifik bir zaman dilimi veya sequence numarası verebiliyor isek aynı durum burda da geçerlidir. Bu 3 opsiyonu kullanarak da tablomuzu recover edebiliriz.
Tablo bazında recover işlemini yaparken yapılabilecek olası hatalardan da bahsetmek istiyorum. Böylelikle sizinde bu tarz işlemler de karşılaşma ihtimali yüksek olan bazı hataları farketme şansınız olacaktır.
İlk olarak tablo bazında recover ederken set until clause’ unu kullanmadan recover başlatırsanız ;
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
rman target / Recovery Manager: Release 12.1.0.2.0 - Production on Tue Jun 16 14:29:48 2015 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database: T1 (DBID=2690208568) RMAN> run { ALLOCATE CHANNEL ch00 DEVICE TYPE sbt_tape PARMS='ENV=(SBT_LIBRARY=/u01/app/oracle/db/12cR1/lib/libobk.a,NB_ORA_CLIENT=Redhat70, NB_ORA_SERV=veritas))'; ALLOCATE CHANNEL ch01 DEVICE TYPE sbt_tape PARMS='ENV=(SBT_LIBRARY=/u01/app/oracle/db/12cR1/lib/libobk.a,NB_ORA_CLIENT=Redhat70, NB_ORA_SERV=veritas))'; RECOVER TABLE kamil.restore_pdbt11 AUXILIARY DESTINATION '/u03/oradata/t1/restore' DATAPUMP DESTINATION '/u03/oradata/t1/restore' DUMP FILE 'restore_pdbt11.dat'; release channel ch00; release channel ch01; } |
using target database control file instead of recovery catalog
allocated channel: ch00
channel ch00: SID=367 device type=SBT_TAPE
channel ch00: Veritas NetBackup for Oracle – Release 7.5 (2013061020)
allocated channel: ch01
channel ch01: SID=14 device type=SBT_TAPE
channel ch01: Veritas NetBackup for Oracle – Release 7.5 (2013061020)
Starting recover at 16-JUN-15
released channel: ch00
released channel: ch01
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/16/2015 14:29:52
RMAN-05078: Table Point-in-Time Recovery requires an UNTIL clause
Hatasını alırsınız. Tablo bazında recover için set until komutu bir zorunluluktur. Zaten diğer türlü tabloyu ana dönecekseniz tabloyu reover etmenin ne anlamı olacakki !
Set until komutunuda scriptimize ekleyerek devam edelim ;
|
1 2 3 4 5 6 7 8 9 10 |
RMAN> run { ALLOCATE CHANNEL ch00 DEVICE TYPE sbt_tape PARMS='ENV=(SBT_LIBRARY=/u01/app/oracle/db/12cR1/lib/libobk.a,NB_ORA_CLIENT=Redhat70, NB_ORA_SERV=veritas))'; ALLOCATE CHANNEL ch01 DEVICE TYPE sbt_tape PARMS='ENV=(SBT_LIBRARY=/u01/app/oracle/db/12cR1/lib/libobk.a,NB_ORA_CLIENT=Redhat70, NB_ORA_SERV=veritas))'; RECOVER TABLE kamil.restore_pdbt11 until time "to_date('16-06-2015 14:30:00','DD-MM-YYYY HH24:MI:SS')" AUXILIARY DESTINATION '/u03/oradata/t1/restore' DATAPUMP DESTINATION '/u03/oradata/t1/restore' DUMP FILE 'restore_pdbt11.dat'; release channel ch00; release channel ch01; } |
using target database control file instead of recovery catalog
allocated channel: ch00
channel ch00: SID=15 device type=SBT_TAPE
channel ch00: Veritas NetBackup for Oracle – Release 7.5 (2013061020)
allocated channel: ch01
channel ch01: SID=256 device type=SBT_TAPE
channel ch01: Veritas NetBackup for Oracle – Release 7.5 (2013061020)
Starting recover at 16-JUN-15
current log archived
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified Point-in-Time
List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1
Creating automatic instance, with SID=’nBgA’
initialization parameters used for automatic instance:
db_name=T1
db_unique_name=nBgA_pitr_T1
compatible=12.1.0.2.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/sq/ora_3/oracle
_system_trig_enabled=FALSE
sga_target=2560M
processes=200
db_create_file_dest=/u03/oradata/t1/restore
log_archive_dest_1=’location=/u03/oradata/t1/restore’
enable_pluggable_database=true
_clone_one_pdb_recovery=true
#No auxiliary parameter file used
starting up automatic instance T1
Oracle instance started
Total System Global Area 2684354560 bytes
Fixed Size 2928008 bytes
Variable Size 603980408 bytes
Database Buffers 2063597568 bytes
Redo Buffers 13848576 bytes
Automatic instance created
contents of Memory Script:
{
# set requested point in time
set until time “to_date(’16-06-2015 14:30:00′,’DD-MM-YYYY HH24:MI:SS’)”;
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone ‘alter database mount clone database’;
# archive current online log
sql ‘alter system archive log current’;
}
executing Memory Script
executing command: SET until clause
Starting restore at 16-JUN-15
Removing automatic instance
shutting down automatic instance
Oracle instance shut down
Automatic instance removed
released channel: ch00
released channel: ch01
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/16/2015 14:32:31
RMAN-03015: error occurred in stored script Memory Script
RMAN-06034: at least 1 channel must be allocated to execute this command
Burda sizede anlamsız geleceğini düşündüğüm bir hata var. Biz restore’ umuzu 2 tane channel ile başlatmıştık ama process’ in bir noktasında bize en az bir tane channel allocate etmelisin gibi br hata dönüyor. Aslına bu hata dan daha doğrusu BUG dan bir önceki yazımda da bahsetmiştim. Oracle 12.2.0.2 de henüz fix edilmemiş olan bir bug bu hata, çözüm önerisi olarak da ALLOCATE CHANNEL komutunun kullanılmaması önerilmektedir.
Bu bilgilerden sonra scriptimizi tekrar düzenleyelim ve tekrar başlatalım. Ancak öncesinde benim backup’ ım tape de olduğundan dolayı ve allocate channel komutunu da kullanamadığım için default device type’ını tape olacak şekilde değiştirmemiz gerekiyor.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
RMAN> show all ; RMAN configuration parameters for database with db_unique_name T1 are: CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default CONFIGURE BACKUP OPTIMIZATION OFF; # default <strong>CONFIGURE DEFAULT DEVICE TYPE TO DISK;</strong> CONFIGURE CONTROLFILE AUTOBACKUP ON; 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 COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/db/12cR1/dbs/snapcf_t1.f'; # default |
Değiştirelim ;
|
1 2 3 4 5 6 7 |
RMAN> CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE'; old RMAN configuration parameters: CONFIGURE DEFAULT DEVICE TYPE TO DISK; new RMAN configuration parameters: CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE'; new RMAN configuration parameters are successfully stored |
değişiklik sonrası check edelim ;
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
RMAN> show all ; RMAN configuration parameters for database with db_unique_name T1 are: CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default CONFIGURE BACKUP OPTIMIZATION OFF; # default <strong>CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';</strong> CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%F'; # default CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default CONFIGURE DEVICE TYPE SBT_TAPE PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE 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 COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/db/12cR1/dbs/snapcf_t1.f'; |
Artık hazırız, komutumuzun son hali ;
|
1 2 3 4 5 6 7 |
run { RECOVER TABLE kamil.rest_pdbt11 OF PLUGGABLE DATABASE PDBT11 until time "to_date('17-06-2015 10:20:00','DD-MM-YYYY HH24:MI:SS')" AUXILIARY DESTINATION '/u03/oradata/t1/restore' DATAPUMP DESTINATION '/u03/oradata/t1/restore' DUMP FILE 'restore_pdbt11.dat'; } |
Starting recover at 17-JUN-15
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=371 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Veritas NetBackup for Oracle – Release 7.5 (2013061020)
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18 device type=DISK
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified Point-in-Time
List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1
Creating automatic instance, with SID=’Arxj’
initialization parameters used for automatic instance:
db_name=T1
db_unique_name=Arxj_pitr_PDBT11_T1
compatible=12.1.0.2.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/sq/ora_3/oracle
_system_trig_enabled=FALSE
sga_target=2560M
processes=200
db_create_file_dest=/u03/oradata/t1/restore
log_archive_dest_1=’location=/u03/oradata/t1/restore’
enable_pluggable_database=true
_clone_one_pdb_recovery=true
#No auxiliary parameter file used
starting up automatic instance T1
Oracle instance started
Total System Global Area 2684354560 bytes
Fixed Size 2928008 bytes
Variable Size 603980408 bytes
Database Buffers 2063597568 bytes
Redo Buffers 13848576 bytes
Automatic instance created
contents of Memory Script:
{
# set requested point in time
set until time “to_date(’17-06-2015 10:20:00′,’DD-MM-YYYY HH24:MI:SS’)”;
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone ‘alter database mount clone database’;
# archive current online log
sql ‘alter system archive log current’;
}
executing Memory Script
executing command: SET until clause
Starting restore at 17-JUN-15
allocated channel: ORA_AUX_SBT_TAPE_1
channel ORA_AUX_SBT_TAPE_1: SID=246 device type=SBT_TAPE
channel ORA_AUX_SBT_TAPE_1: Veritas NetBackup for Oracle – Release 7.5 (2013061020)
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=87 device type=DISK
channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: restoring control file
channel ORA_AUX_SBT_TAPE_1: reading from backup piece c-2690208568-20150617-01
channel ORA_AUX_SBT_TAPE_1: piece handle=c-2690208568-20150617-01 tag=TAG20150617T101647
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:15
output file name=/u03/oradata/t1/restore/T1/controlfile/o1_mf_br2d2lsh_.ctl
Finished restore at 17-JUN-15
sql statement: alter database mount clone database
sql statement: alter system archive log current
contents of Memory Script:
{
# set requested point in time
set until time “to_date(’17-06-2015 10:20:00′,’DD-MM-YYYY HH24:MI:SS’)”;
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 8 to new;
set newname for clone datafile 9 to new;
set newname for clone tempfile 1 to new;
set newname for clone tempfile 3 to new;
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 4, 3, 8, 9;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to /u03/oradata/t1/restore/T1/datafile/o1_mf_temp_%u_.tmp in control file
renamed tempfile 3 to /u03/oradata/t1/restore/T1/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 17-JUN-15
using channel ORA_AUX_SBT_TAPE_1
using channel ORA_AUX_DISK_1
channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_SBT_TAPE_1: restoring datafile 00001 to /u03/oradata/t1/restore/T1/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_SBT_TAPE_1: reading from backup piece bk_1091_1_882612283
channel ORA_AUX_SBT_TAPE_1: piece handle=bk_1091_1_882612283 tag=T1_TEST_20150617
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:35
channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_SBT_TAPE_1: restoring datafile 00004 to /u03/oradata/t1/restore/T1/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_SBT_TAPE_1: reading from backup piece bk_1093_1_882612343
channel ORA_AUX_SBT_TAPE_1: piece handle=bk_1093_1_882612343 tag=T1_TEST_20150617
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:25
channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_SBT_TAPE_1: restoring datafile 00009 to /u03/oradata/t1/restore/T1/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_SBT_TAPE_1: reading from backup piece bk_1130_1_882613157
channel ORA_AUX_SBT_TAPE_1: piece handle=bk_1130_1_882613157 tag=T1_TEST_20150617
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:35
channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_SBT_TAPE_1: restoring datafile 00003 to /u03/oradata/t1/restore/T1/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_SBT_TAPE_1: reading from backup piece bk_1131_1_882613157
channel ORA_AUX_SBT_TAPE_1: piece handle=bk_1131_1_882613157 tag=T1_TEST_20150617
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:25
channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_SBT_TAPE_1: restoring datafile 00008 to /u03/oradata/t1/restore/T1/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_SBT_TAPE_1: reading from backup piece bk_1129_1_882613157
channel ORA_AUX_SBT_TAPE_1: piece handle=bk_1129_1_882613157 tag=T1_TEST_20150617
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:35
Finished restore at 17-JUN-15
datafile 1 switched to datafile copy
input datafile copy RECID=8 STAMP=882617645 file name=/u03/oradata/t1/restore/T1/datafile/o1_mf_system_br2d3dbq_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=9 STAMP=882617645 file name=/u03/oradata/t1/restore/T1/datafile/o1_mf_undotbs1_br2d4d2p_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=10 STAMP=882617645 file name=/u03/oradata/t1/restore/T1/datafile/o1_mf_sysaux_br2d6bn3_.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=11 STAMP=882617646 file name=/u03/oradata/t1/restore/T1/datafile/o1_mf_system_br2d70y6_.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=12 STAMP=882617646 file name=/u03/oradata/t1/restore/T1/datafile/o1_mf_sysaux_br2d58vx_.dbf
contents of Memory Script:
{
# set requested point in time
set until time “to_date(’17-06-2015 10:20:00′,’DD-MM-YYYY HH24:MI:SS’)”;
# online the datafiles restored or switched
sql clone “alter database datafile 1 online”;
sql clone “alter database datafile 4 online”;
sql clone “alter database datafile 3 online”;
sql clone ‘PDBT11’ “alter database datafile
8 online”;
sql clone ‘PDBT11’ “alter database datafile
9 online”;
# recover and open database read only
recover clone database tablespace “SYSTEM”, “UNDOTBS1”, “SYSAUX”, “PDBT11″:”SYSTEM”, “PDBT11″:”SYSAUX”;
sql clone ‘alter database open read only’;
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 1 online
sql statement: alter database datafile 4 online
sql statement: alter database datafile 3 online
sql statement: alter database datafile 8 online
sql statement: alter database datafile 9 online
Starting recover at 17-JUN-15
using channel ORA_AUX_SBT_TAPE_1
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 4195 is already on disk as file /u04/oradata/t1/archive/t1_arch_1_4195_879417466.arc
archived log for thread 1 with sequence 4196 is already on disk as file /u04/oradata/t1/archive/t1_arch_1_4196_879417466.arc
archived log for thread 1 with sequence 4197 is already on disk as file /u04/oradata/t1/archive/t1_arch_1_4197_879417466.arc
archived log for thread 1 with sequence 4198 is already on disk as file /u04/oradata/t1/archive/t1_arch_1_4198_879417466.arc
archived log for thread 1 with sequence 4199 is already on disk as file /u04/oradata/t1/archive/t1_arch_1_4199_879417466.arc
archived log for thread 1 with sequence 4200 is already on disk as file /u04/oradata/t1/archive/t1_arch_1_4200_879417466.arc
archived log for thread 1 with sequence 4201 is already on disk as file /u04/oradata/t1/archive/t1_arch_1_4201_879417466.arc
archived log for thread 1 with sequence 4202 is already on disk as file /u04/oradata/t1/archive/t1_arch_1_4202_879417466.arc
archived log for thread 1 with sequence 4203 is already on disk as file /u04/oradata/t1/archive/t1_arch_1_4203_879417466.arc
archived log for thread 1 with sequence 4204 is already on disk as file /u04/oradata/t1/archive/t1_arch_1_4204_879417466.arc
archived log for thread 1 with sequence 4205 is already on disk as file /u04/oradata/t1/archive/t1_arch_1_4205_879417466.arc
archived log for thread 1 with sequence 4206 is already on disk as file /u04/oradata/t1/archive/t1_arch_1_4206_879417466.arc
archived log for thread 1 with sequence 4207 is already on disk as file /u04/oradata/t1/archive/t1_arch_1_4207_879417466.arc
archived log for thread 1 with sequence 4208 is already on disk as file /u04/oradata/t1/archive/t1_arch_1_4208_879417466.arc
archived log for thread 1 with sequence 4209 is already on disk as file /u04/oradata/t1/archive/t1_arch_1_4209_879417466.arc
archived log for thread 1 with sequence 4210 is already on disk as file /u04/oradata/t1/archive/t1_arch_1_4210_879417466.arc
archived log for thread 1 with sequence 4211 is already on disk as file /u04/oradata/t1/archive/t1_arch_1_4211_879417466.arc
archived log for thread 1 with sequence 4212 is already on disk as file /u04/oradata/t1/archive/t1_arch_1_4212_879417466.arc
archived log for thread 1 with sequence 4213 is already on disk as file /u04/oradata/t1/archive/t1_arch_1_4213_879417466.arc
archived log file name=/u04/oradata/t1/archive/t1_arch_1_4195_879417466.arc thread=1 sequence=4195
archived log file name=/u04/oradata/t1/archive/t1_arch_1_4196_879417466.arc thread=1 sequence=4196
archived log file name=/u04/oradata/t1/archive/t1_arch_1_4197_879417466.arc thread=1 sequence=4197
archived log file name=/u04/oradata/t1/archive/t1_arch_1_4198_879417466.arc thread=1 sequence=4198
archived log file name=/u04/oradata/t1/archive/t1_arch_1_4199_879417466.arc thread=1 sequence=4199
archived log file name=/u04/oradata/t1/archive/t1_arch_1_4200_879417466.arc thread=1 sequence=4200
archived log file name=/u04/oradata/t1/archive/t1_arch_1_4201_879417466.arc thread=1 sequence=4201
archived log file name=/u04/oradata/t1/archive/t1_arch_1_4202_879417466.arc thread=1 sequence=4202
archived log file name=/u04/oradata/t1/archive/t1_arch_1_4203_879417466.arc thread=1 sequence=4203
archived log file name=/u04/oradata/t1/archive/t1_arch_1_4204_879417466.arc thread=1 sequence=4204
archived log file name=/u04/oradata/t1/archive/t1_arch_1_4205_879417466.arc thread=1 sequence=4205
archived log file name=/u04/oradata/t1/archive/t1_arch_1_4206_879417466.arc thread=1 sequence=4206
archived log file name=/u04/oradata/t1/archive/t1_arch_1_4207_879417466.arc thread=1 sequence=4207
archived log file name=/u04/oradata/t1/archive/t1_arch_1_4208_879417466.arc thread=1 sequence=4208
archived log file name=/u04/oradata/t1/archive/t1_arch_1_4209_879417466.arc thread=1 sequence=4209
archived log file name=/u04/oradata/t1/archive/t1_arch_1_4210_879417466.arc thread=1 sequence=4210
archived log file name=/u04/oradata/t1/archive/t1_arch_1_4211_879417466.arc thread=1 sequence=4211
archived log file name=/u04/oradata/t1/archive/t1_arch_1_4212_879417466.arc thread=1 sequence=4212
archived log file name=/u04/oradata/t1/archive/t1_arch_1_4213_879417466.arc thread=1 sequence=4213
media recovery complete, elapsed time: 00:00:03
Finished recover at 17-JUN-15
sql statement: alter database open read only
contents of Memory Script:
{
sql clone ‘alter pluggable database PDBT11 open read only’;
}
executing Memory Script
sql statement: alter pluggable database PDBT11 open read only
contents of Memory Script:
{
sql clone “create spfile from memory”;
shutdown clone immediate;
startup clone nomount;
sql clone “alter system set control_files =
”/u03/oradata/t1/restore/T1/controlfile/o1_mf_br2d2lsh_.ctl” comment=
”RMAN set” scope=spfile”;
shutdown clone immediate;
startup clone nomount;
# mount database
sql clone ‘alter database mount clone database’;
}
executing Memory Script
sql statement: create spfile from memory
database closed
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 2684354560 bytes
Fixed Size 2928008 bytes
Variable Size 603980408 bytes
Database Buffers 2063597568 bytes
Redo Buffers 13848576 bytes
sql statement: alter system set control_files = ”/u03/oradata/t1/restore/T1/controlfile/o1_mf_br2d2lsh_.ctl” comment= ”RMAN set” scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 2684354560 bytes
Fixed Size 2928008 bytes
Variable Size 603980408 bytes
Database Buffers 2063597568 bytes
Redo Buffers 13848576 bytes
sql statement: alter database mount clone database
contents of Memory Script:
{
# set requested point in time
set until time “to_date(’17-06-2015 10:20:00′,’DD-MM-YYYY HH24:MI:SS’)”;
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile 10 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 10;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
Starting restore at 17-JUN-15
allocated channel: ORA_AUX_SBT_TAPE_1
channel ORA_AUX_SBT_TAPE_1: SID=6 device type=SBT_TAPE
channel ORA_AUX_SBT_TAPE_1: Veritas NetBackup for Oracle – Release 7.5 (2013061020)
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=88 device type=DISK
channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore
channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_SBT_TAPE_1: restoring datafile 00010 to /u03/oradata/t1/restore/ARXJ_PITR_PDBT11_T1/datafile/o1_mf_users_%u_.dbf
channel ORA_AUX_SBT_TAPE_1: reading from backup piece bk_1132_1_882613157
channel ORA_AUX_SBT_TAPE_1: piece handle=bk_1132_1_882613157 tag=T1_TEST_20150617
channel ORA_AUX_SBT_TAPE_1: restored backup piece 1
channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:00:15
Finished restore at 17-JUN-15
datafile 10 switched to datafile copy
input datafile copy RECID=14 STAMP=882617725 file name=/u03/oradata/t1/restore/ARXJ_PITR_PDBT11_T1/datafile/o1_mf_users_br2db59p_.dbf
contents of Memory Script:
{
# set requested point in time
set until time “to_date(’17-06-2015 10:20:00′,’DD-MM-YYYY HH24:MI:SS’)”;
# online the datafiles restored or switched
sql clone ‘PDBT11’ “alter database datafile
10 online”;
# recover and open resetlogs
recover clone database tablespace “PDBT11″:”USERS”, “SYSTEM”, “UNDOTBS1”, “SYSAUX”, “PDBT11″:”SYSTEM”, “PDBT11″:”SYSAUX” delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 10 online
Starting recover at 17-JUN-15
using channel ORA_AUX_SBT_TAPE_1
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 4213 is already on disk as file /u04/oradata/t1/archive/t1_arch_1_4213_879417466.arc
archived log file name=/u04/oradata/t1/archive/t1_arch_1_4213_879417466.arc thread=1 sequence=4213
media recovery complete, elapsed time: 00:00:00
Finished recover at 17-JUN-15
database opened
contents of Memory Script:
{
sql clone ‘alter pluggable database PDBT11 open’;
}
executing Memory Script
sql statement: alter pluggable database PDBT11 open
contents of Memory Script:
{
# create directory for datapump import
sql ‘PDBT11’ “create or replace directory
TSPITR_DIROBJ_DPDIR as ”
/u03/oradata/t1/restore””;
# create directory for datapump export
sql clone ‘PDBT11’ “create or replace directory
TSPITR_DIROBJ_DPDIR as ”
/u03/oradata/t1/restore””;
}
executing Memory Script
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ”/u03/oradata/t1/restore”
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ”/u03/oradata/t1/restore”
Performing export of tables…
EXPDP> Starting “SYS”.”TSPITR_EXP_Arxj_Emho”:
EXPDP> Estimate in progress using BLOCKS method…
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
EXPDP> Total estimation using BLOCKS method: 768 KB
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
EXPDP> . . exported “KAMIL”.”REST_PDBT11″ 630.7 KB 2354 rows
EXPDP> Master table “SYS”.”TSPITR_EXP_Arxj_Emho” successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_Arxj_Emho is:
EXPDP> /u03/oradata/t1/restore/restore_pdbt11.dat
EXPDP> Job “SYS”.”TSPITR_EXP_Arxj_Emho” successfully completed at Wed Jun 17 11:36:12 2015 elapsed 0 00:00:18
Export completed
contents of Memory Script:
{
# shutdown clone before import
shutdown clone abort
}
executing Memory Script
Oracle instance shut down
Performing import of tables…
IMPDP> Master table “SYS”.”TSPITR_IMP_Arxj_tAmp” successfully loaded/unloaded
IMPDP> Starting “SYS”.”TSPITR_IMP_Arxj_tAmp”:
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
IMPDP> . . imported “KAMIL”.”REST_PDBT11″ 630.7 KB 2354 rows
IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
IMPDP> Job “SYS”.”TSPITR_IMP_Arxj_tAmp” successfully completed at Wed Jun 17 11:36:50 2015 elapsed 0 00:00:20
Import completed
Removing automatic instance
Automatic instance removed
auxiliary instance file /u03/oradata/t1/restore/T1/datafile/o1_mf_temp_br2d8fmg_.tmp deleted
auxiliary instance file /u03/oradata/t1/restore/T1/datafile/o1_mf_temp_br2d84bb_.tmp deleted
auxiliary instance file /u03/oradata/t1/restore/ARXJ_PITR_PDBT11_T1/onlinelog/o1_mf_3_br2dbhfy_.log deleted
auxiliary instance file /u03/oradata/t1/restore/ARXJ_PITR_PDBT11_T1/onlinelog/o1_mf_2_br2dbh7r_.log deleted
auxiliary instance file /u03/oradata/t1/restore/ARXJ_PITR_PDBT11_T1/onlinelog/o1_mf_1_br2dbh0j_.log deleted
auxiliary instance file /u03/oradata/t1/restore/ARXJ_PITR_PDBT11_T1/datafile/o1_mf_users_br2db59p_.dbf deleted
auxiliary instance file /u03/oradata/t1/restore/T1/datafile/o1_mf_sysaux_br2d58vx_.dbf deleted
auxiliary instance file /u03/oradata/t1/restore/T1/datafile/o1_mf_system_br2d70y6_.dbf deleted
auxiliary instance file /u03/oradata/t1/restore/T1/datafile/o1_mf_sysaux_br2d6bn3_.dbf deleted
auxiliary instance file /u03/oradata/t1/restore/T1/datafile/o1_mf_undotbs1_br2d4d2p_.dbf deleted
auxiliary instance file /u03/oradata/t1/restore/T1/datafile/o1_mf_system_br2d3dbq_.dbf deleted
auxiliary instance file /u03/oradata/t1/restore/T1/controlfile/o1_mf_br2d2lsh_.ctl deleted
auxiliary instance file restore_pdbt11.dat deleted
Finished recover at 17-JUN-15
RMAN>
Aslında scriptin logundan da görüleceği üzere tablo bazında restore işlemi operasyonel açıdan ok da kolay bir işlem değil, çok kaba hatlarıyla bu işlem boyunca ;
• Öncelikle nomount modda bir db create ediyor (sadece spfile kullanarak),
• Sonra bu db için controlfile restore ediyor,
• Db’ yi mount moda alıyor,
• Until clause kısmında set edilen zamana kadar olacak şekilde db’ yi restore sonrasında recover ediyor,
• Bu db içerisinden bizim tablomuzun exportunu alıyor,
• Sonrasında oluşturduğu bu db’ yi shutdown ediyor,
• Almış olduğu exportu import ediyor,
• Ve son olarak en başta oluşturmuş olduğu db’ ide remove ediyor.
Yazının başında loglarını saklamadığım için belirtmemiştim ama son durumda tablomuzun artık geri geldiğini söyleyebiliriz.
|
1 2 3 4 5 6 |
select count(*) from kamil.rest_pdbt11 COUNT(*) ---------- 2354 1 row selected. |
Yazının başında da belirttğim gibi sadece tablo bazında değil partition bazında da recover işlemi yapıalbilir. Hatta REMAP opsiyonu ile farklı bir tablo altına recover işlemi yapılabilir. Yine REMAP TABLESPACE opsiyonu ile de yine farklı bir tablespace altına recover edilebilir. Örneğin ;
|
1 2 3 4 5 6 7 8 |
run { RECOVER TABLE kamil.rest_pdbt11:ocakq1 OF PLUGGABLE DATABASE PDBT11 until time "to_date('17-06-2015 10:20:00','DD-MM-YYYY HH24:MI:SS')" AUXILIARY DESTINATION '/u03/oradata/t1/restore' DATAPUMP DESTINATION '/u03/oradata/t1/restore' REMAP TABLE 'KAMIL'.'REST_PDB11':'OCAKQ1':'OCAK2015' REMAP TABLESPACE 'PDB11_DATA':'DATA2015'; } |
Yine ek olarak import yapmak zorunda değiliz demiştik, recover işlemi sırasına datayı yeni bir yere import etmek istemezsek de komutun sonuna ;
|
1 |
NOTABLEIMPORT; |
Parametresini eklersek belirtmiş olduğumuz yere dmp alacak ve sonrasında import etmeyecektir.
Referance;
Bug 17080042 : RMAN-6034 POINT IN TIME PDB OR TABLE RECOVERY ERRORS WITH RMAN MANUAL CHANNELS
