в продолжение темы:
validate SMSPEC
база магазина
oracle 10.2.0.5 , win2003 32 bit
разбираюсь на копии базы (поднял на резервном серваке)
не могу дождаться окончания
Код:
ANALYZE TABLE "SUPERMAG"."SMCASHCHECKITEMS" VALIDATE STRUCTURE CASCADE;
Ждал > 20 часов . Не могу дождаться.
Это отрабатывает за пару минут:
Код:
ANALYZE TABLE "SUPERMAG"."SMCASHCHECKITEMS" VALIDATE STRUCTURE ;
Делал
Код:
alter table supermag.SMCASHCHECKITEMS move;
alter INDEX SUPERMAG.SMCCASHCHECKITEMS_PK rebuild;
alter INDEX SUPERMAG.SMCASHCHECKITEMS_ART rebuild;
Все проходит быстро и без ошибок, но VALIDATE STRUCTURE CASCADE всё равно зависает.
Без CASCADE всё быстро отрабатывает.
Включил Архивлог.
Код:
RMAN> BACKUP VALIDATE DATABASE ARCHIVELOG ALL;
Starting backup at 27-JUL-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00005 name=I:\ORACLE\ORADATA\KRAS01\USERS01.DBF
input datafile fno=00004 name=I:\ORACLE\ORADATA\KRAS01\INDX01.DBF
input datafile fno=00007 name=I:\ORACLE\ORADATA\KRAS01\TOOLS+1.DBF
input datafile fno=00001 name=I:\ORACLE\ORADATA\KRAS01\SYSTEM01.DBF
input datafile fno=00003 name=I:\ORACLE\ORADATA\KRAS01\SYSAUX01.DBF
input datafile fno=00002 name=I:\ORACLE\ORADATA\KRAS01\UNDOTBS01.DBF
input datafile fno=00006 name=I:\ORACLE\ORADATA\KRAS01\TOOLS01.DBF
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:55
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=221 recid=1 stamp=821901979
input archive log thread=1 sequence=222 recid=2 stamp=821902078
input archive log thread=1 sequence=223 recid=3 stamp=821904589
input archive log thread=1 sequence=224 recid=4 stamp=821904789
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 27-JUL-13
RMAN> BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;
Starting backup at 27-JUL-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00005 name=I:\ORACLE\ORADATA\KRAS01\USERS01.DBF
input datafile fno=00004 name=I:\ORACLE\ORADATA\KRAS01\INDX01.DBF
input datafile fno=00007 name=I:\ORACLE\ORADATA\KRAS01\TOOLS+1.DBF
input datafile fno=00001 name=I:\ORACLE\ORADATA\KRAS01\SYSTEM01.DBF
input datafile fno=00003 name=I:\ORACLE\ORADATA\KRAS01\SYSAUX01.DBF
input datafile fno=00002 name=I:\ORACLE\ORADATA\KRAS01\UNDOTBS01.DBF
input datafile fno=00006 name=I:\ORACLE\ORADATA\KRAS01\TOOLS01.DBF
channel ORA_DISK_1: backup set complete, elapsed time: 00:04:05
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=221 recid=1 stamp=821901979
input archive log thread=1 sequence=222 recid=2 stamp=821902078
input archive log thread=1 sequence=223 recid=3 stamp=821904589
input archive log thread=1 sequence=224 recid=4 stamp=821904789
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 27-JUL-13
RMAN> restore DATABASE VALIDATE;
Starting restore at 27-JUL-13
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile backupset
channel ORA_DISK_1: reading from backup piece H:\BACKUP_RMAN\BACKUP_DB_KRAS01_82
1904594
channel ORA_DISK_1: restored backup piece 1
piece handle=H:\BACKUP_RMAN\BACKUP_DB_KRAS01_821904594 tag=TAG20130727T184954
channel ORA_DISK_1: validation complete, elapsed time: 00:01:46
Finished restore at 27-JUL-13
RMAN> blockrecover corruption list;
Starting blockrecover at 27-JUL-13
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished blockrecover at 27-JUL-13
Т.е. валидация всей базы проходит.
Я просто почему упёрся : хочется использовать optimizer Олегона.
А он, похоже, запускает VALIDATE STRUCTURE CASCADE.
И зависает, дальше не идёт.