RMAN 中 “backup validate database” 语法可以用来扫描数据库中的物理错误，实际验证期间并不产生备份集。
如果需要更多的错误检查，可以使用backup 命令的check logical 选项来配置备份执行逻辑讹误检查，示例如下：
backup validate check logical database;
示例中RMAN 仅执行逻辑数据库验证操作，而不产生实际备份集。
需要注意如果要在给定的错误数内仍然继续执行备份，需设置maxcorrupt 参数值。如下：
run {
set maxcorrupt for datafile 1,2,3,4 to 10;
backup validate check logical database;
}
How to Check/Validate That RMAN Backups Are Good
Applies to:
Oracle Server – Enterprise Edition – Version: 10.1.0.2 to 10.2.0.4 – Release: 10.1 to 10.2
Information in this document applies to any platform.
Goal
How to check/validate backup of datafile and archivelogs are good to restore and recover ?
Solution
To check the backups are good or not there are two options :
Option 1: Restore the database on a server and recover it using the backups. This you accomplish using RMAN duplicate command.
View the below note for more information on RMAN duplicate :
Note.228257.1 RMAN ‘Duplicate Database’ Feature in Oracle9i / Oracle 10G
Option 2 : Run RMAN validate command. The validate command will not restore any file it just reads and gives confirmation that the backups are good.
Example:
Want to restore and recover the database till time ’10:23, 05-November-2007 then you have to use the below command to accomplish this :
Step 1: The below command just gives the report of backups that are used to do the restore and recover :
run
{
set until time “to_date(‘2007-05-10:23:00:00′,’yyyy-dd-mm:hh24:mi:ss’)”;
restore database preview;
}
The Restore … Preview identifies the backups (backup sets or image copies, on disk or sequential media like tapes) required to carry out a given restore operation, based on the information in the RMAN repository. Use RESTORE… PREVIEW when planning your restore and recovery operation, to ensure that all required backups are available or to identify situations.
The restore database preview gives a report of all the backup pieces and archivelogs that are required to restore/recover the database. Make a note of the start sequence and end sequence of archivelogs and the same will be used in the next step
Step 2: Then run the below command to check the backup pieces are good :
run
{
allocate channel c1 type disk;
set until time “to_date(‘2007-05-10:23:00:00′,’yyyy-dd-mm:hh24:mi:ss’)”;
restore database validate;
}
The above command will read the backup pieces/Copies which has datafiles and if finds any error it will report at the RMAN prompt.
RMAN> run
{
allocate channel c1 type disk;
restore archivelog from sequence xxx until sequence yyy validate;
}
Replace the xxx, yyy with the start and end archivelog sequence reported by restore database preview command ran in the step 1.
The RESTORE … VALIDATE command test whether you can restore from your backups. You can test the availability of usable backups for any desired RESTORE operation, or test the contents of a specific backup for use in RESTORE operations. The contents of the backups are actually read and validated for corruption to ensure that the objects to be restored can be restored from them.
BACKUP VALIDATE CHECK LOGICAL DATABASE EXPIRES BACKUPS
Applies to:
Oracle Server – Enterprise Edition – Version: 10.2.0.4
This problem can occur on any platform.
Symptoms
When running the following sequence of rman commands the presence of the command
‘BACKUP VALIDATE CEHCK LOGICAL DATABASE;’
there is a backup that is expired yet without the command no backups are expired:
set echo on;
BACKUP INCREMENTAL LEVEL 0 DATABASE;
CROSSCHECK ARCHIVELOG ALL;
BACKUP ARCHIVELOG ALL DELETE ALL INPUT;
DELETE NOPROMPT OBSOLETE;
CROSSCHECK BACKUP;
BACKUP VALIDATE CHECK LOGICAL DATABASE;
CROSSCHECK BACKUP;
Cause
Do not use the rman command: BACKUP VALIDATE CHECK LOGICAL DATABASE;
Issue reporduces in 102040, with MAXPIECESIZE set to a value less than 320 M.
BUG 8891438 was logged for this issue and it was closed as a duplicate of BUG 6740371.
Solution
Do not specifed MAXPIECESIZE during channel allocation used for BACKUP VALIDATE.
You please do validation of backup after clearing channel allocation with maxpiecesize:
CONFIGURE CHANNEL 1 DEVICE TYPE DISK CLEAR;
Or you can get a backport for BUG 6740371.
How to check for physical and logical database corruption using “backup validate check logical database” command for database on a non-archivelog mode
Applies to:
Oracle Server – Enterprise Edition – Version: 9.2.0.1 to 10.2.0.3 – Release: 9.2 to 10.2
Oracle Server – Enterprise Edition – Version: 9.2.0.1 to 11.1.0.7 [Release: 9.2 to 11.1]
Information in this document applies to any platform.
Purpose
How can we use this command for a non-archivelog database so we can use this as opposed to the dbv command ?
RMAN> backup validate check logical database;
Questions and Answers
“backup validate check logical database” RMAN command could NOT be used for a non-archivelog database.
RMAN> backup validate check logical database;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_2 channel at 11/06/2007 14:56:31
ORA-19602: cannot backup or copy active file in NOARCHIVELOG mode
The only way to do that for database on no-archive log mode is to perform above command while the target database in MOUNT status not OPEN
Additional Information:
————————————
– RMAN restore/backup validate with check logical will ensure that there is no logical and also physical corruption.However , DBV would report only physical corruption.
– If the backup validate discovers corrupt blocks, then RMAN updates the V$DATABASE_BLOCK_CORRUPTION view with rows describing the corruptions.
– After corrupt block is repaired the row identifies this block is deleted from the view.
How To Use RMAN To Check For Logical & Physical Database Corruption
This article is being delivered in Draft form and may contain
errors. Please use the MetaLink “Feedback” button to advise
Oracle of any issues related to this article.
***
PURPOSE
——-
To explain how RMAN can be used to check for both logical and physical corruption.
SCOPE & APPLICATION
——————-
This document is intended for database adminstrators of all levels.
Note: No need that RMAN has been used or setup previously. There is no need for
a recovery catalog. No backup is created. Since a single channel is
being allocated there is little overhead. The only requirement is that
Oracle8i or later has to be used and the database is mounted (required
for a noarchivelog mode database) or open.
During a RMAN backup or RMAN ‘backup validate’ every block currently used or
previously used is read into memory then written to another portion of memory.
During this memory to memory write the block is checked for corruption.
Therefore RMAN’s BACKUP command with the VALIDATE and CHECK LOGICAL clauses
allow a Database Adminstrator to quickly check for both physical and logical
corruption. If the initialization parameter DB_BLOCK_CHECKSUM=TRUE, specifying
CHECK LOGICAL detects all types of corruption that are possible to detect.
How To Use RMAN To Check For Logical And Physical Block Corruption
——————————————————————-
*** For databases in noarchivelog mode, ***
*** the commands that follow only work ***
*** while the database is in mount mode ***
1) For version 9.2 or higher, query v$database_block_corruption to see if
there are any rows already listed there.
2) Set your $ORACLE_SID and $ORACLE_HOME appropriately if not already set.
3) Start RMAN in nocatalog mode and connect to your database:
From the operating system prompt issue:
$ rman target / nocatalog
or
$ rman target sys/ nocatalog
4) From the RMAN> prompt issue the validate command with the “check logical”
clause:
The following example shows how to validate all datafiles:
run {
allocate channel d1 type disk;
backup blocks all check logical validate database;
release channel d1;
}
You’ll see output like this:
Starting backup at 15-SEP-04
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=17 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=C:\ORACLE\ORADATA\ORA92\SYSTEM01.DBF
input datafile fno=00002 name=C:\ORACLE\ORADATA\ORA92\UNDOTBS01.DBF
input datafile fno=00005 name=C:\ORACLE\ORADATA\ORA92\EXAMPLE01.DBF
input datafile fno=00010 name=C:\ORACLE\ORADATA\ORA92\XDB01.DBF
input datafile fno=00006 name=C:\ORACLE\ORADATA\ORA92\INDX01.DBF
input datafile fno=00009 name=C:\ORACLE\ORADATA\ORA92\USERS01.DBF
input datafile fno=00003 name=C:\ORACLE\ORADATA\ORA92\CWMLITE01.DBF
input datafile fno=00004 name=C:\ORACLE\ORADATA\ORA92\DRSYS01.DBF
input datafile fno=00007 name=C:\ORACLE\ORADATA\ORA92\ODM01.DBF
input datafile fno=00008 name=C:\ORACLE\ORADATA\ORA92\TOOLS01.DBF
<<<<< SCREEN OUTPUT MAY PAUSE HERE FOR A WHILE >>>>>
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
Finished backup at 15-SEP-04
The following example shows how to validate a single datafile:
run {
allocate channel d1 type disk;
backup blocks all check logical validate datafile 1;
release channel d1;
}
The following example shows how to validate two datafiles:
run {
allocate channel d1 type disk;
backup blocks all check logical validate datafile 1, 2;
release channel d1;
}
The following example shows how to run a validate with multiple channels. This
is helpful for very large databases, datafiles, etc:
run {
allocate channel d1 type disk;
allocate channel d2 type disk;
backup blocks all check logical validate database;
release channel d1;
release channel d2;
}
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Note:
——-
For all the databases lesser than version 10g, the “backup blocks all”
does not work. You have skip the “blocks all” clause.Check below example:
backup check logical validate datafile 1;
backup check logical validate datafile 1,2;
backup check logical validate database;
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
If you wish to monitor the progress of RMAN backup validate, you may issue this
query:
SQL> select sid, serial#, context, sofar, totalwork,
round(sofar/totalwork*100,2) “%_complete”
from v$session_longops
where opname like ‘RMAN%’
and opname not like ‘%aggregate%’
and totalwork != 0
and sofar <> totalwork
/
5) Once the validate process is complete, you either check the alert log or a
view depending on the version of Oracle being used.
In Oracle8i corruptions found with the RMAN validate command are only reported
in the alert log. Oracle8i users must search the alert.log for corruption
errors in the time range during which the validate command was started and when
it finished. Corruptions found are NOT reported back to the RMAN interface.
In Oracle9i and beyond you can query the view name V$DATABASE_BLOCK_CORRUPTION
to determine what corruption, if any, was found by RMAN. As in Oracle8i,
corruptions found are NOT reported back to the RMAN interface.
Note that corruption reported in V$DATABASE_BLOCK_CORRUPTION is cleared with each
RMAN backup validate run. If new corruption is found this view is updated with
the new corruption details. Rows in this view are not removed until another
RMAN backup validate or RMAN backup is run AND during which corruption for a block
is longer detected. To understand what is reported in this view, see the description
of the view as shown in the manual titled Database Reference.
If you exclude “blocks all”, review note 561010.1 mentioned below.
Identify the corruption extension using RMAN/DBV/ANALYZE etc
Applies to:
Oracle Server – Enterprise Edition – Version: 10.2.0.1 to 11.1.0.7
Information in this document applies to any platform.
Purpose
The purpose of this article is to describe in a single note how to identify the most common corruptions/inconsistencies in Oracle 10g or above.
Scope and Application
Corruption Types
There are several types of corruptions like:
* Datafile Block Corruption – Physical/Logical
* Table/Index Inconsistency
* Extents Inconsistencies
* Data Dictionary Inconsistencies
The next section is intended to describe the tools used to detect corruption for each corruption type.
Identify the corruption extension using RMAN/DBV/ANALYZE etc
Datafile Block Corruption – Intra block corruption
It refers to intra block corruptions that may cause different errors like ORA-1578, ORA-8103, ORA-1410, ORA-600 etc.
Oracle classifies the corruptions as Physical and Logical. See Note 840978.1
RMAN – Identify Datafile Block Corruptions
* To identify both Physical and Logical Block Corruptions use the “CHECK LOGICAL” option. The next command checks the complete database for both corruptions without actually doing a backup:
$ rman target /
RMAN> backup check logical validate database;
The next command checks the complete database for both corruptions in a backup:
$ rman target /
RMAN> backup check logical database
* Chek the view V$DATABASE_BLOCK_CORRUPTION to identify the block corruptions detected by RMAN.
* Use Note 472231.1 (section “Step 2: Identify the corrupt segments”) to identify all the Corrupted Objects in the Database reported by RMAN.
* The above command can use PARALLELISM using multiple channels to make the validation faster. See Note 472231.1 for examples of PARALLELISM.
* By Default RMAN backups (without the CHECK LOGICAL option) only detect Physical Block Corruptions.
* In 10g and when using Locally Managed Tablespaces (LMT) RMAN may not check for blocks that belong to free extents (Blocks in extents shown by dba_free_space).
DBVerify – Identify Datafile Block Corruptions
* DBVERIFY identify Physical and Logical Intra Block Corruptions by default. Dbverify cannot be run for the whole database in a single command. It does not need a database connection either:
dbv file= blocksize=
* Use Note 819533.1 to identify all the corrupt objects reported by DBVerify
* Use Note 369076.1 to simulate running dbv in PARALLEL for a single datafile.
RMAN Vs DBVerify – Datafile Intra Block Corruption
It has been a dilemma of what tool to use when identifying intra block corruptions. Here are some comparisons between RMAN and DBV:
* When the logical option is used by RMAN, it does exactly the same checks as DBV does for intra block corruption.
* RMAN can be run with PARALLELISM using multiple channels making it faster than DBV which cannot be run in parallel in a single command. See Note 472231.1 for examples.
* DBV checks for empty blocks. In 10g RMAN may not check blocks in free extents when Locally Managed Tablespaces are used. In 11g RMAN checks for both free and used extents.
* Both DBV and RMAN (11g) can check for a range of blocks. RMAN: VALIDATE DATAFILE 1 BLOCK 10 to 100;. DBV: start=10 end=100
* RMAN keeps corruption information in the control file (v$database_block_corruption, v$backup_corruption). DBV does not.
* RMAN may not report the corruption details like what is exactly corrupted in a block reported as a LOGICAL corrupted block. DBV reports the corruption details in the screen or in a log file.
* DBV can scan blocks with a higher SCN than a given SCN.
* DBV does not need a connection to the database.
Identify TABLE / INDEX Inconsistency
Table / Index inconsistencies is when an entry in the Table does not exist in the Index or vice versa. The common errors are ORA-8102, ORA-600 [kdsgrp1], ORA-1499 by “analyze validate structure cascade”.
* The tool to identify TABLE / INDEX inconsistencies is the ANALYZE command:
analyze table
When an inconsistency is identified, the above analyze command will produce error ORA-1499 and a trace file.
* Use Note 100419.1 to run ANALYZE with the CASCADE option for a specific tablespace.
* Use <<563070.1>> when the trace file produced by ANALYZE command has the message “Table/Index row count mismatch”
Identify Extents Inconsistency in Locally Managed Tablespace
It refers to inconsistencies in the section used by Locally Managed Tablespaces (LMT) to keep information about used and free extents.
Examples of these inconsistencies are:
* Overlapped Extents (2 segments could incorrectly use the same block). The common errors are ORA-8103, ORA-1410, ORA-600 [kdddgb2]
* A free extent reported as used
LMT is identified by EXTENT_MANAGEMENT=’LOCAL’ in view dba_tablespaces.
* To identify inconsistencies in LMT for MSSM (Manual Segment Space Mangement – dba_tablespaces.SEGMENT_SPACE_MANAGEMENT=’MANUAL’):
execute dbms_space_admin.tablespace_verify(‘&tablespace_name’)
oradebug setmypid
oradebug tracefile_name
* To identify inconsistencies in LMT for ASSM (Automatic Segment Space Mangement – dba_tablespaces.SEGMENT_SPACE_MANAGEMENT=’AUTO’):
execute dbms_space_admin.assm_tablespace_verify(‘&tablespace_name’,dbms_space_admin.TS_VERIFY_BITMAPS)
oradebug setmypid
oradebug tracefile_name
If there are inconsistencies, dbms_space_admin generates a trace file. Note that the session running dbms_space_admin may not report in the screen if inconsistencies are detected.
Reference Note.887263.1 to identify the segments reported in the trace file.
Identify Extents Inconsistency in Dictionary Managed Tablespaces
It refers to inconsistencies in data dictoinary tables FET$/UET$ used by Dictionary Managed Tablespaces to keep information of free and used extents respectively.
Examples of these inconsistencies are:
* Overlapped Extents (2 segments could incorrectly use the same block). The common errors are ORA-8103, ORA-1410
* Free extent reported as used
Dictionary Managed Tablespace are identified by EXTENT_MANAGEMENT=’DICTIONARY’ in view dba_tablespaces.
* To identify inconsistencies in Dictionary Managed Tablespaces run script hcheck:
Note 136697.1 “hcheck.sql” script to check for known problems in Oracle8i, Oracle9i, Oracle10g and Oracle 11g
Identify Data Dictionary Inconsistency
It refers to inconsistencies between Data Dictionary tables. These tables are owned by the user SYS and are stored in the SYSTEM tablespace to keep track of the users, tables, indexes, etc that are created in the database.
Example of this inconsistency is:
* ‘Problem: OBJ$.OWNER# not in USER$” which refers to a user in table OBJ$ that does not exist in USER$.
* To identify known Data Dictionary inconsistencies run script hcheck:
Note 136697.1 “hcheck.sql” script to check for known problems in Oracle8i, Oracle9i, Oracle10g and Oracle 11g
How to Check Archivelogs for Corruption using RMAN
Applies to:
Oracle Server – Enterprise Edition – Version: 8.1.7.4 to 10.2.0.3 – Release: 8.1.7 to 10.2
Oracle Server – Enterprise Edition – Version: 8.1.7.4 to 10.2.0.4 [Release: 8.1.7 to 10.2]
Information in this document applies to any platform.
Goal
How to check archivelogs for corruption using RMAN.
Solution
Use RMAN to back them up. RMAN will backup archivelogs in their entirety (no blocks are skipped) and perfom corruption
checks on the blocks – no corruptions will be tolerated on any log so the backup will fail on the
FIRST corrupt block found.
Example: RMAN>backup archivelog all;
If you simply want to check for corruption but not actually create a backuppiece then use the ‘validate’ option:
RMAN>backup validate archivelog all;
If you want to do the backup and delete the archivelogs from disk afterwards:
RMAN>backup archivelog all delete all input;
These commands rely on ALL archivelogs listed in the controlfile and/or rman catalog being present on disk – if archived logs have been removed from disk then the backup command will fail:
RMAN-06059: expected archived log not found, lost of archived log compromises recoverability
If you get this error, replace the log if you can (perhaps it was moved to a temporary directory to avoid the archivelog directory filling up) and if you cant then remove the reference from the controlfile before running the backup again:
RMAN>crosscheck archivelog all;
RMAN>delete expired archivelog all;