Hi there,
I have a fairly simple problem : I want to check if I have a valid backup.
In the documentation, I found REPORT NEED BACKUP and RESTORE VALIDATE DATABASE. But none of them seems to work :-(
Ok, here is my scenario
at the beginning, I have no backup
connected to target database: LSC75 (DBID=1093677405)
connected to recovery catalog database
RMAN> list backup;
RMAN> list copy;
specification does not match any archive log in the recovery catalog
Now I do a backup:
RMAN> backup database plus archivelog delete input;
Starting backup at 20.03.2006 12:19:16
current log archived
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=1164 devtype=SBT_TAPE
channel ORA_SBT_TAPE_1: Tivoli Data Protection for Oracle: version 5.2.0.0
channel ORA_SBT_TAPE_1: starting archive log backupset
channel ORA_SBT_TAPE_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=5 recid=2 stamp=585577044
input archive log thread=1 sequence=6 recid=3 stamp=585577161
channel ORA_SBT_TAPE_1: starting piece 1 at 20.03.2006 12:19:29
channel ORA_SBT_TAPE_1: finished piece 1 at 20.03.2006 12:19:32
piece handle=02heecmg_1_1 tag=TAG20060320T121927 comment=API Version 2.0,MMS Version 5.2.0.0
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:04
channel ORA_SBT_TAPE_1: deleting archive log(s)
archive log filename=/dbms/oracle/LSC75/arch/arch_584379933_1_5.dbf recid=2 stamp=585577044
archive log filename=/dbms/oracle/LSC75/arch/arch_584379933_1_6.dbf recid=3 stamp=585577161
Finished backup at 20.03.2006 12:19:32
Starting backup at 20.03.2006 12:19:32
using channel ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: starting full datafile backupset
channel ORA_SBT_TAPE_1: specifying datafile(s) in backupset
input datafile fno=00003 name=/dbms/oracle/LSC75/data/sysaux01LSC75.dbf
input datafile fno=00001 name=/dbms/oracle/LSC75/data/system01LSC75.dbf
input datafile fno=00002 name=/dbms/oracle/LSC75/data/undo01LSC75.dbf
input datafile fno=00004 name=/dbms/oracle/LSC75/data/users01LSC75.dbf
channel ORA_SBT_TAPE_1: starting piece 1 at 20.03.2006 12:19:33
channel ORA_SBT_TAPE_1: finished piece 1 at 20.03.2006 12:20:38
piece handle=03heecml_1_1 tag=TAG20060320T121932 comment=API Version 2.0,MMS Version 5.2.0.0
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:01:05
Finished backup at 20.03.2006 12:20:38
Starting backup at 20.03.2006 12:20:38
current log archived
using channel ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: starting archive log backupset
channel ORA_SBT_TAPE_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=7 recid=4 stamp=585577238
channel ORA_SBT_TAPE_1: starting piece 1 at 20.03.2006 12:20:42
channel ORA_SBT_TAPE_1: finished piece 1 at 20.03.2006 12:20:45
piece handle=04heecop_1_1 tag=TAG20060320T122041 comment=API Version 2.0,MMS Version 5.2.0.0
channel ORA_SBT_TAPE_1: backup set complete, elapsed time: 00:00:04
channel ORA_SBT_TAPE_1: deleting archive log(s)
archive log filename=/dbms/oracle/LSC75/arch/arch_584379933_1_7.dbf recid=4 stamp=585577238
Finished backup at 20.03.2006 12:20:46
Starting Control File and SPFILE Autobackup at 20.03.2006 12:20:46
piece handle=autobackup_c-1093677405-20060320-00 comment=API Version 2.0,MMS Version 5.2.0.0
Finished Control File and SPFILE Autobackup at 20.03.2006 12:20:55
Than I archive one log
SQL> select THREAD#,SEQUENCE#,CREATOR,ARCHIVED,DELETED,NAME from v$archived_log;
THREAD# SEQUENCE# CREATOR ARC DEL NAME
---------- ---------- ------- --- --- ------------------------------------------------------------
1 4 ARCH YES YES
1 5 ARCH YES YES
1 6 FGRD YES YES
1 7 FGRD YES YES
SQL> alter system archive log current;
System altered.
SQL> select THREAD#,SEQUENCE#,CREATOR,ARCHIVED,DELETED,NAME from v$archived_log;
THREAD# SEQUENCE# CREATOR ARC DEL NAME
---------- ---------- ------- --- --- ------------------------------------------------------------
1 4 ARCH YES YES
1 5 ARCH YES YES
1 6 FGRD YES YES
1 7 FGRD YES YES
1 8 ARCH YES NO /dbms/oracle/LSC75/arch/arch_584379933_1_8.dbf
Yet I delete manually one archive log, for this test, and I ask rman to take notice the file disappears
$ rm /dbms/oracle/LSC75/arch/arch_584379933_1_8.dbf
RMAN> crosscheck archivelog sequence 8 thread 1;
validation failed for archived log
archive log filename=/dbms/oracle/LSC75/arch/arch_584379933_1_8.dbf recid=5 stamp=585577588
Crosschecked 1 objects
at this point, I do not have a copy of log 8, so I am no longer able to restore my database without data loss
but I cannot find out this info using rman.
I tried REPORT NEED BACKUP
RMAN> report need backup recovery window of 1 days;
Report of files that must be backed up to satisfy 1 days recovery window
File Days Name
---- ----- -----------------------------------------------------
RMAN> report need backup redundancy 1;
Report of files with less than 1 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------
Nothing!
And RESTORE VALIDATE
RMAN> restore validate database;
Starting restore at 20.03.2006 12:29:52
piece handle=03heecml_1_1 tag=TAG20060320T121932
channel ORA_SBT_TAPE_1: validation complete, elapsed time: 00:00:37
Finished restore at 20.03.2006 12:30:39
again, no info that sequence 8 is missing.
What are your opinions?
Thanks for feedback
Laurent