DBVERIFY : Database Verify Utility
O DBVERIFY (Database Verify Utility) é um utilitário que fornece informações e possibilita ao DBA checar a estrutura física do banco de dados, fazendo parte do escopo de utilitários que a Oracle fornece para administrar bases de dados. No MOS (MY ORACLE SUPPORT), pode ser encontrado nas notas Doc ID 452443.1 , Doc ID 352907.1 e Doc ID 35512.1 , além de tantas outras que demonstram erros e exemplificações desse utilitário em várias plataformas e situações.
Segue exemplo de uma execução simples utilizando o dbv no arquivo de dados : /oracle/oradata/prod/users01.dbf utilizando a cláusula “dbv file=nomedoarquivo logfile=nomedoarquivodelog feedback=100 (onde feeback reporta que durante a execução será mostrado um progresso da mesma através de pontos e, n=100 o número de páginas verificadas durante a execução do DBVERIFY. )
[oracle@brunors product]$ dbv file=/oracle/oradata/prod/users01.dbf logfile=teste_dbv_users.log feedback=100
DBVERIFY: Release 11.2.0.3.0 - Production on Sun Sep 29 16:06:59 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
.......
##Verificando o log da execução
[oracle@brunors product]$ cat teste_dbv_users.log
DBVERIFY: Release 11.2.0.3.0 - Production on Sun Sep 29 16:06:59 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /oracle/oradata/prod/users01.dbf
DBVERIFY - Verification complete
Total Pages Examined : 640
Total Pages Processed (Data) : 16
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 2
Total Pages Failing (Index): 0
Total Pages Processed (Other): 145
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 477
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 1316532 (0.1316532)Onde cada step da verificação, segundo a doc [http://docs.oracle.com/cd/B14117_01/server.101/b10825/dbverify.htm] da Oracle tem o seguinte significado:
Notes:
- Pages = Blocks
- Total Pages Examined = number of blocks in the file
- Total Pages Processed = number of blocks that were verified (formatted blocks)
- Total Pages Failing (Data) = number of blocks that failed the data block checking routine
- Total Pages Failing (Index) = number of blocks that failed the index block checking routine
- Total Pages Marked Corrupt = number of blocks for which the cache header is invalid, thereby making it impossible for DBVERIFY to identify the block type
- Total Pages Influx = number of blocks that are being read and written to at the same time. If the database is open when DBVERIFY is run, DBVERIFY reads blocks multiple times to get a consistent image. But because the database is open, there may be blocks that are being read and written to at the same time (INFLUX). DBVERIFY cannot get a consistent image of pages that are in flux.
##Segue o exemplo de uma nova execução . No entanto, sem a cláusula feedbak e utilizando um outro arquivo de dados , o /oracle/oradata/prod/undotbs01.dbf . Nota-se que , nesta execução, não é mostrado um progresso de execução representado pelos “….” , conforme execução anterior utilizando a cláusula feedback.
[oracle@brunors product]$ dbv file=/oracle/oradata/prod/undotbs01.dbf logfile=este_dbv_undotbs.log
DBVERIFY: Release 11.2.0.3.0 - Production on Sun Sep 29 16:08:57 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
##Verificando o log da execução novamente
[oracle@brunors product]$ cat este_dbv_undotbs.log
DBVERIFY: Release 11.2.0.3.0 - Production on Sun Sep 29 16:08:57 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
DBVERIFY - Verification starting : FILE = /oracle/oradata/prod/undotbs01.dbf
DBVERIFY - Verification complete
Total Pages Examined : 101760
Total Pages Processed (Data) : 0
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 101247
Total Pages Processed (Seg) : 10
Total Pages Failing (Seg) : 0
Total Pages Empty : 513
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 2173247 (0.2173247)O DBVERIFY também pode ser utlizado para checar segmentos. Para isto, alguns valores como TSN, SEGFILE e SEGBLOCK, são importantes. Para isto, estaremos utilizando a DBA_SEGMENTS e a V$TABLESPACE através de uma consulta aninhada.
SELECT TABLESPACE.TS# AS TSN ,SEGMENTS.RELATIVE_FNO AS SEGFILE , SEGMENTS.HEADER_BLOCK AS SEGBLOCK
FROM DBA_SEGMENTS SEGMENTS, V$TABLESPACE TABLESPACE
WHERE SEGMENTS.OWNER='BRUNORS' AND SEGMENTS.SEGMENT_TYPE='INDEX'
AND SEGMENTS.SEGMENT_NAME='HDURBRU_J003821' AND TABLESPACE.NAME=SEGMENTS.TABLESPACE_NAME;##Aplicação do select acima
SQL> SELECT TABLESPACE.TS# AS TSN ,SEGMENTS.RELATIVE_FNO AS SEGFILE , SEGMENTS.HEADER_BLOCK AS SEGBLOCK FROM DBA_SEGMENTS SEGMENTS, V$TABLESPACE TABLESPACE WHERE SEGMENTS.OWNER='BRUNORS' AND SEGMENTS.SEGMENT_TYPE='INDEX'
AND SEGMENTS.SEGMENT_NAME='HDURBRU_J003821' AND TABLESPACE.NAME=SEGMENTS.TABLESPACE_NAME;
TSN SEGFILE SEGBLOCK
---------- ---------- ----------
1 2 16771Segue abaixo a execução da utilização do DBV para checar um segmento.
Cláusula:
dbv USERID=username/password SEGMENT_ID=tsn.segfile.segblock
[oracle@brunors product]$ dbv userid=brunors/123456 SEGMENT_ID=1.2.16771 logfile=segment.log
DBVERIFY: Release 11.1.0.7.0 - Production on Wed Oct 2 01:27:02 2013
Copyright (c) 1982, 2007, Oracle. All righ
##Verificando o log de execução:
[oracle@brunors product]$ cat segment.log
DBVERIFY - Verification starting : SEGMENT_ID = 1.2.16771
DBVERIFY - Verification complete
Total Pages Examined : 4
Total Pages Processed (Data) : 0
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 1
Total Pages Failing (Index): 0
Total Pages Processed (Other): 2
Total Pages Processed (Seg) : 1
Total Pages Failing (Seg) : 0
Total Pages Empty : 0
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 0 (0.0)Além das utilidades demonstradas acima, o DBV possui um help que pode ser consultado no prompt do Sistema Operacional passando a cláusula help como yes.
##Segue a demonstração:
[oracle@brunors product]$ dbv help=y
DBVERIFY: Release 11.1.0.7.0 - Production on Wed Oct 2 01:33:01 2013
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Keyword Description (Default)
----------------------------------------------------
FILE File to Verify (NONE)
START Start Block (First Block of File)
END End Block (Last Block of File)
BLOCKSIZE Logical Block Size (8192)
LOGFILE Output Log (NONE)
FEEDBACK Display Progress (0)
PARFILE Parameter File (NONE)
USERID Username/Password (NONE)
SEGMENT_ID Segment ID (tsn.relfile.block) (NONE)
HIGH_SCN Highest Block SCN To Verify (NONE)
(scn_wrap.scn_base OR scn)Espero ter ajudado,
Abraços!
Muito bom Bruno !
Valew Will! 🙂