Since brbackup simply calls RMAN to execute the check it's also possible to run the check directly via RMAN. To do so execute the following commands:
rman nocatalog
RMAN> connect target /
RMAN> backup check logical validate ( database );
To check single files or tablespaces you can alter the command e.g. like this:
RMAN> backup check logical validate ( datafile 1,23,34 );
Or
RMAN> backup check logical validate ( tablespace 'SYSAUX' );
RMAN stores information about found block corruptions in the control file of the database. You can access these information via the database view V$DATABASE_BLOCK_CORRUPTION.
RMAN> connect target /
RMAN> backup check logical validate ( database );
To check single files or tablespaces you can alter the command e.g. like this:
RMAN> backup check logical validate ( datafile 1,23,34 );
Or
RMAN> backup check logical validate ( tablespace 'SYSAUX' );
RMAN stores information about found block corruptions in the control file of the database. You can access these information via the database view V$DATABASE_BLOCK_CORRUPTION.
Tips to check dbverify using command line RMAN or dbv or brconnect as below
Note 23345 - Consistency check of ORACLE database
As for other related information you may used these notes also
Note 365481 - Block corruptions
Note 540463 - FAQ Consistency Checks + Block Corruptions
http://docs.oracle.com/cd/A97630_01/server.920/a96652/ch13.htm#1006642
Command-Line Interface
The following example shows a sample use of the command-line interface to this mode of DBVERIFY.
% dbv FILE=t_db1.dbf FEEDBACK=100
Sample DBVERIFY Output
The following example is sample output of verification for the file
t_db1.dbf. The feedback parameter has been given the value 100 to display one period (.) for every 100 pages processed:% dbv FILE=t_db1.dbf FEEDBACK=100 DBVERIFY: Release 9.2.0.1.0 - Production on Wed Feb 27 13:55:26 2002 (c) Copyright 2002 Oracle Corporation. All rights reserved. DBVERIFY - Verification starting : FILE = t_db1.dbf ................................................................................ DBVERIFY - Verification complete Total Pages Examined : 9216 Total Pages Processed (Data) : 2044 Total Pages Failing (Data) : 0 Total Pages Processed (Index): 733 Total Pages Failing (Index): 0 Total Pages Empty : 5686 Total Pages Marked Corrupt : 0 Total Pages Influx : 0
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.
DBverify using brtools commands
http://www.saptechies.com/consistency-check-of-oracle-database/
------------------------- Complete check
-------------------------
Our recommendation for a regular complete check:
generally
'dbverify' using brbackup (parallelized) using the complete database
If the block check sum calculation (DB_BLOCK_CHECKSUM = TRUE) is set, DBVERIFY checks the consistency of the data in more detail. For details on the block check sum calculation, refer to Note 923919.
in addition:
if an earlier Oracle version than 9 and table locks and long runtimes are acceptable OR
if Oracle 9 and higher and table locks and long runtime are acceptable:
=> 'analyze table ... validate structure cascade ...' using
brconnect (parallelized) OR
otherwise
'analyze table ... validate structure cascade ...' on all objects of the User SYS
You should also regularly check the alert log for messages about corruptions. To do this, you can use the brconnect -f check for example.
Since some corruption messages in the Alert Log may sometimes be flagged with the text 'Corrupt Block' rather than an Oracle error code, the amount of critical search strings must therefore be increased according to SAP Note 435290.
Note that running brconnect -f check or regularly checking the Alert Log is no subsitute for a full consistency check as described above because it is only possible to identify corruptions on blocks that have been accessed. These corruptions are simply identified earlier.
1. Complete check: Analyze table ... validate structure cascade;
CAUTION:
If you use dbverify or the export procedure for a complete check of your database, (for example, if you cannot accept long table locks) using the Analyze procedure, check the consistency of the objects of user 'SYS' (Oracle Dictionary). To do this, save the attached SQL script analyze_oracle_dictionary.txt as analyze_oracle_dictionary.sql and execute it as follows:
sqlplus "/ as sysdba" @analyze_oracle_dictionary.sql
If file analyze_cascade.log contains errors that indicate corruption, please correct them as described in Note 365481.
Alternatively, you can check the Oracle Dictionary with brconnect as described in Note 914174.
brconnect -u / -c -f stats -t oradict_tab -v
To analyze your database completely using this procedure, you need to check all objects of the database users. Here you have two options: Analyze using brconnect or Analyze using Oracle Script.
a) Analyze using brconnect
To check your database completely using brconnect, the following
prerequisites have to be met:
Version brconnect 6.10 PL 46/6.20 PL 15 or a higher patch level or version is used (brconnect can also be used for releases lower than 6.20. See Note 12741 for information).
only Oracle 9 (not Oracle 8 and not Oracle 10): Init.ora Parameter O7_DICTIONARY_ACCESSIBILITY = TRUE is set
If these prerequisites are met, call brconnect from the
command line as follows:
brconnect [ -u / ] -c -f stats -v cascade -t all -e null -p
indicates the number of Oracle tables that are being checked simultaneously. Do not choose a value for which is higher than your CPU number.
The option '-e null' ensures that tables excluded from statistics calculation are checked nevertheless.
If file /oracle//sapcheck/*.vst contains errors that indicate corruption, correct them as described in Note 365481.
b) Analyze using Oracle Script
Save the attached SQL script analyze_complete_db_.txt as analyze_complete_db_.sql that corresponds to the database, and execute it as follows:
Oracle <=8.1 :sqlplus "/ as sysdba" @analyze_complete_db_8.sql
Oracle >=9.2 :sqlplus "/ as sysdba" @analyze_complete_db_9.sql
If file analyze_cascade.log contains errors that indicate corruption, please correct them as described in Note 365481.
2. Complete check: Dbverify
To check your database completely using this procedure, you need to check all data files of the database.
Command on OS level to check for a closed database:
brbackup -w only_dbv -c -t offline
Command on OS level to check for an open database (on Windows operating systems, an online check may not be possible in certain cases):
brbackup -w only_dbv -c -t online
The result of dbverify is contained in the following file:
UNIX: $ORACLE_HOME/sapbackup/b*.dbv
NT: :oraclesapbackupb*.dbv
If the log file contains errors that indicate corruption, correct them as described in Note 365481.
For detailed information about dbverify and how to combine data backup and consistency check, refer to the online documentation in the brbackup command options.
3. Complete check: Export
To check your database completely using this procedure you need to export all objects. To do this the Null Device is mainly used because only the log that states whether the objects could be exported without errors is required and not the export dump itself.
Depending on the operating system, choose one of the following commands on OS level:
UNIX
exp system/manager full=y file=/dev/null volsize=0 buffer=3000000 log=
Windows
exp system/manager full=y file=NUL: buffer=3000000 log=
The result of the export is contained in file .
If file analyze_sys.log contains errors that indicate corruption, correct them as described in Note 365481.
-------------------------- Single object check --------------------------
1. Single object check: Analyze table ... validate structure cascade
In this case, a single object is a table.
Log on to your database with '/ as sysdba' in sqlplus, and execute thefollowing command:
Oracle <= 8.1:
analyze table ""." " validate structure cascade;
Oracle >=9.2:
analyze table ""." " validate structure cascade online;
If messages indicating corruption are displayed on the screen, make the corrections as described in Note 365481.
2. Single object check: Dbverify
In this case, a single object is a data file. If you want to use this method to carry out a consistency check for a single data file, execute the following command as ora on operating system level:
dbv file= blocksize=8192
For example:
dbv file=/oracle/AIO/sapdata1/btabd_1/btabd.data1 blocksize=8192
If messages indicating corruption are displayed on the screen, make the corrections as described in Note 365481.
3. Single object check: Export
In this case, a single object is a table.
If you want to use this method to carry out a consistency check on a single table, execute the following command on operating system level:
UNIX
exp / tables= file=/dev/null volsize=0 buffer=3000000
Windows
exp / tables=
Our recommendation for a regular complete check:
generally
'dbverify' using brbackup (parallelized) using the complete database
If the block check sum calculation (DB_BLOCK_CHECKSUM = TRUE) is set, DBVERIFY checks the consistency of the data in more detail. For details on the block check sum calculation, refer to Note 923919.
in addition:
if an earlier Oracle version than 9 and table locks and long runtimes are acceptable OR
if Oracle 9 and higher and table locks and long runtime are acceptable:
=> 'analyze table ... validate structure cascade ...' using
brconnect (parallelized) OR
otherwise
'analyze table ... validate structure cascade ...' on all objects of the User SYS
You should also regularly check the alert log for messages about corruptions. To do this, you can use the brconnect -f check for example.
Since some corruption messages in the Alert Log may sometimes be flagged with the text 'Corrupt Block' rather than an Oracle error code, the amount of critical search strings must therefore be increased according to SAP Note 435290.
Note that running brconnect -f check or regularly checking the Alert Log is no subsitute for a full consistency check as described above because it is only possible to identify corruptions on blocks that have been accessed. These corruptions are simply identified earlier.
1. Complete check: Analyze table ... validate structure cascade;
CAUTION:
If you use dbverify or the export procedure for a complete check of your database, (for example, if you cannot accept long table locks) using the Analyze procedure, check the consistency of the objects of user 'SYS' (Oracle Dictionary). To do this, save the attached SQL script analyze_oracle_dictionary.txt as analyze_oracle_dictionary.sql and execute it as follows:
sqlplus "/ as sysdba" @analyze_oracle_dictionary.sql
If file analyze_cascade.log contains errors that indicate corruption, please correct them as described in Note 365481.
Alternatively, you can check the Oracle Dictionary with brconnect as described in Note 914174.
brconnect -u / -c -f stats -t oradict_tab -v
To analyze your database completely using this procedure, you need to check all objects of the database users. Here you have two options: Analyze using brconnect or Analyze using Oracle Script.
a) Analyze using brconnect
To check your database completely using brconnect, the following
prerequisites have to be met:
Version brconnect 6.10 PL 46/6.20 PL 15 or a higher patch level or version is used (brconnect can also be used for releases lower than 6.20. See Note 12741 for information).
only Oracle 9 (not Oracle 8 and not Oracle 10): Init.ora Parameter O7_DICTIONARY_ACCESSIBILITY = TRUE is set
If these prerequisites are met, call brconnect from the
command line as follows:
brconnect [ -u / ] -c -f stats -v cascade -t all -e null -p
indicates the number of Oracle tables that are being checked simultaneously. Do not choose a value for which is higher than your CPU number.
The option '-e null' ensures that tables excluded from statistics calculation are checked nevertheless.
If file /oracle//sapcheck/*.vst contains errors that indicate corruption, correct them as described in Note 365481.
b) Analyze using Oracle Script
Save the attached SQL script analyze_complete_db_.txt as analyze_complete_db_.sql that corresponds to the database, and execute it as follows:
Oracle <=8.1 :sqlplus "/ as sysdba" @analyze_complete_db_8.sql
Oracle >=9.2 :sqlplus "/ as sysdba" @analyze_complete_db_9.sql
If file analyze_cascade.log contains errors that indicate corruption, please correct them as described in Note 365481.
2. Complete check: Dbverify
To check your database completely using this procedure, you need to check all data files of the database.
Command on OS level to check for a closed database:
brbackup -w only_dbv -c -t offline
Command on OS level to check for an open database (on Windows operating systems, an online check may not be possible in certain cases):
brbackup -w only_dbv -c -t online
The result of dbverify is contained in the following file:
UNIX: $ORACLE_HOME/sapbackup/b*.dbv
NT: :oraclesapbackupb*.dbv
If the log file contains errors that indicate corruption, correct them as described in Note 365481.
For detailed information about dbverify and how to combine data backup and consistency check, refer to the online documentation in the brbackup command options.
3. Complete check: Export
To check your database completely using this procedure you need to export all objects. To do this the Null Device is mainly used because only the log that states whether the objects could be exported without errors is required and not the export dump itself.
Depending on the operating system, choose one of the following commands on OS level:
UNIX
exp system/manager full=y file=/dev/null volsize=0 buffer=3000000 log=
Windows
exp system/manager full=y file=NUL: buffer=3000000 log=
The result of the export is contained in file .
If file analyze_sys.log contains errors that indicate corruption, correct them as described in Note 365481.
-------------------------- Single object check --------------------------
1. Single object check: Analyze table ... validate structure cascade
In this case, a single object is a table.
Log on to your database with '/ as sysdba' in sqlplus, and execute thefollowing command:
Oracle <= 8.1:
analyze table ""." " validate structure cascade;
Oracle >=9.2:
analyze table ""." " validate structure cascade online;
If messages indicating corruption are displayed on the screen, make the corrections as described in Note 365481.
2. Single object check: Dbverify
In this case, a single object is a data file. If you want to use this method to carry out a consistency check for a single data file, execute the following command as ora on operating system level:
dbv file= blocksize=8192
For example:
dbv file=/oracle/AIO/sapdata1/btabd_1/btabd.data1 blocksize=8192
If messages indicating corruption are displayed on the screen, make the corrections as described in Note 365481.
3. Single object check: Export
In this case, a single object is a table.
If you want to use this method to carry out a consistency check on a single table, execute the following command on operating system level:
UNIX
exp / tables= file=/dev/null volsize=0 buffer=3000000
Windows
exp / tables=
If messages indicating corruption are displayed on the screen, make the corrections as described in Note 365481.
1 comment:
Luckily found this spot... otherwise grinding for answer to heaven.
Post a Comment