DBVERIFY Utility:-
[oracle@sujeet ~]$ dbv file=/10g/oracle/product/10.2.0/oradata/idea/
users01.dbf
DBVERIFY: Release 10.2.0.1.0 - Production on Tue Dec 13 17:23:38 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = /10g/oracle/product/10.2.0/oradata/
idea/users01.dbf
DBVERIFY - Verification complete
Total Pages Examined : 1600
Total Pages Processed (Data) : 58
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 158
Total Pages Failing (Index): 0
Total Pages Processed (Other): 532
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 852
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Highest block SCN : 950123 (0.950123)
[oracle@sujeet ~]$ dbv file=/10g/oracle/product/10.2.0/oradata/idea/
example01.dbf blocksize=8192
DBVERIFY: Release 10.2.0.1.0 - Production on Tue Dec 13 17:29:59 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = /10g/oracle/product/10.2.0/oradata/
idea/example01.dbf
DBVERIFY - Verification complete
Total Pages Examined : 12800
Total Pages Processed (Data) : 4409
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 1264
Total Pages Failing (Index): 0
Total Pages Processed (Other): 1539
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 5588
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Highest block SCN : 470508 (0.470508)
[oracle@sujeet ~]$ vi /10g/oracle/product/10.2.0/oradata/idea/users01.dbf
NOTE:-write any word after that save it..
[oracle@sujeet ~]$ dbv file=/10g/oracle/product/10.2.0/oradata/idea/
users01.dbf
DBVERIFY - Verification complete
Total Pages Examined : 640
Total Pages Processed (Data) : 0
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 0
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 0
Total Pages Marked Corrupt : 640
Total Pages Influx : 635
Highest block SCN : 0 (0.0)
SQL> alter database datafile'/u01/app/oracle/oracle/product/10.2.0/db_1/
oradata/orcl2/users01.dbf' offline;
Database altered.
SQL> alter database open;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
----------
READ WRITE
RMAN> restore datafile 4;
RMAN> recover datafile 4;
SQL> shut immediate
SQL> startup
*****ANALYZE Command******
SQL> conn hr/hr
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
REGIONS TABLE
COUNTRIES TABLE
LOCATIONS TABLE
DEPARTMENTS TABLE
JOBS TABLE
EMPLOYEES TABLE
JOB_HISTORY TABLE
EMP_DETAILS_VIEW VIEW
T TABLE
9 rows selected.
SQL> analyze table EMPLOYEES validate structure cascade;
Table analyzed.
SQL> select * from user_indexes;
SQL> ANALYZE INDEX index_name VALIDATE STRUCTURE;
Index analyzed.
SQL> select index_name from user_indexes;
SQL> analyze index SYS_IL0000051628C00144$$ validate structure;
SQL> SET SERVEROUTPUT ON
DECLARE num_corrupt INT;
BEGIN
num_corrupt := 0;
DBMS_REPAIR.CHECK_OBJECT (
SCHEMA_NAME => 'SCOTT',
OBJECT_NAME => 'DEPT',
REPAIR_TABLE_NAME => 'REPAIR_TABLE',
CORRUPT_COUNT => num_corrupt);
DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt));
END;
number corrupt: 0
PL/SQL procedure successfully completed.
*************************************************
SQL> BEGIN
DBMS_REPAIR.ADMIN_TABLES (
TABLE_NAME => 'REPAIR_TABLE',
TABLE_TYPE => dbms_repair.repair_table,
ACTION => dbms_repair.create_action,
TABLESPACE => 'USERS');
END;
PL/SQL procedure successfully completed.
************************************************************
SQL> desc repair_table;
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJECT_ID NOT NULL NUMBER
TABLESPACE_ID NOT NULL NUMBER
RELATIVE_FILE_ID NOT NULL NUMBER
BLOCK_ID NOT NULL NUMBER
CORRUPT_TYPE NOT NULL NUMBER
SCHEMA_NAME NOT NULL VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(30)
BASEOBJECT_NAME VARCHAR2(30)
PARTITION_NAME VARCHAR2(30)
CORRUPT_DESCRIPTION VARCHAR2(2000)
REPAIR_DESCRIPTION VARCHAR2(200)
MARKED_CORRUPT NOT NULL VARCHAR2(10)
CHECK_TIMESTAMP NOT NULL DATE
FIX_TIMESTAMP DATE
REFORMAT_TIMESTAMP DATE
*******************************************************************
SQL> BEGIN
DBMS_REPAIR.ADMIN_TABLES (
TABLE_NAME => 'ORPHAN_KEY_TABLE',
TABLE_TYPE => dbms_repair.orphan_table,
ACTION => dbms_repair.create_action,
TABLESPACE => 'USERS');
END;
PL/SQL procedure successfully completed.
SQL> desc ORPHAN_KEY_TABLE;
Name Null? Type
----------------------------------------- -------- ----------------------------
SCHEMA_NAME NOT NULL VARCHAR2(30)
INDEX_NAME NOT NULL VARCHAR2(30)
IPART_NAME VARCHAR2(30)
INDEX_ID NOT NULL NUMBER
TABLE_NAME NOT NULL VARCHAR2(30)
PART_NAME VARCHAR2(30)
TABLE_ID NOT NULL NUMBER
KEYROWID NOT NULL ROWID
KEY NOT NULL ROWID
DUMP_TIMESTAMP NOT NULL DATE
*********************************************************
SQL> SET SERVEROUTPUT ON
DECLARE num_corrupt INT;
BEGIN
num_corrupt := 0;
DBMS_REPAIR.CHECK_OBJECT (
SCHEMA_NAME => 'SCOTT',
OBJECT_NAME => 'DEPT',
REPAIR_TABLE_NAME => 'REPAIR_TABLE',
CORRUPT_COUNT => num_corrupt);
DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt));
END;
number corrupt: 0
PL/SQL procedure successfully completed.
BLOCKRECOVER Command:-
RMAN> BLOCKRECOVER DATAFILE 6 BLOCK 3;
RMAN> BLOCKRECOVER CORRUPTION LIST RESTORE UNTIL TIME 'sysdate – 10';
No comments:
Post a Comment