1 테스트 환경
n 서버 : Sun Enterprise E3500 (2048M)
n 운영체제 : Solaris 9
n 오라클 : Oracle 10.2.0.4 Enterprise 64bit
2 DBV Utility
– Database verify 의 약자로, 7.3.2 부터 지원되는 유틸리티.
– Data 나 Index block 이 어느 정도 신뢰성이 있는지, 손상(corruption)의 유무에 대한 정도를 점검.
– dbv 는 Block level 까지만 점검하기 때문에, ‘ANALYZE TABLE … VALIDATE STRUCTURE CASCADE’ 와는 달리 Index 와 Data block 간의 일치성 점검은 수행하지는 않는다.
– dbv 는 Analyze 시 Table lock(TM) 이 걸리는 문제 때문에, Analyze 할 수 없는 상황에서 유용하게 사용될 수 있다.
|
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 (scn_wrap.scn_base OR scn) |
(NONE) |
3 DBV Utility 사용법
– dbv 유틸리티는 data file에 대해서만 사용될 수 있고 Redo log 나 control file 에 대해서는 사용할 수 없다.
– raw device 에 대해서는 직접 dbv 를 수행할 수 없고 symbolic link 를 사용해야 한다. 그리고 link name은 반드시 확장자(extension)를 줘야한다.
(ln -s /dev/rvol/users /oracle/users01.dbf)
n 일반적인 File의 사용법
– 만약 ASM를 사용하는 경우에는 반드시 userid를 넣어줘야만 한다.
(Ex : dbv file=+DG1/ORCL/datafile/system01.dbf userid=system/sys)
|
$ dbv file=파일명 blocksize=8192
|
![clip_image003[4]](https://i0.wp.com/oracledba.zapto.org/wordpress/wp-content/uploads/2019/01/clip_image0034-1.jpg "clip_image003[4]")
– 만약 dbv를 실행한 결과에서 Marked Corrupt 나 Failing 이 있다면, 다시 dbv 를 수행하여 일시적인 현상인지 확인한다.
n Raw Device의 사용법
– 파일명은 ln -s /dev/rvol/users /oracle/users01.dbf 와 같이 symbolic link를 걸어서 넣어준다.
– Start=1로 설정을 하고 End는 datafile의 bytes를 db_block_size로 나눈 값을 넣는다.
|
End 값 구하는 방법 SQL> show parameter
● 위에서 구한 db_block_size를 구하려는 datafile의 bytes 값으로 나누면 얻을 수 있다.
SQL> select bytes/8192 from
|
![clip_image006[4]](https://i0.wp.com/oracledba.zapto.org/wordpress/wp-content/uploads/2019/01/clip_image0064-1.jpg "clip_image006[4]")
![clip_image009[4]](https://i0.wp.com/oracledba.zapto.org/wordpress/wp-content/uploads/2019/01/clip_image0094.jpg "clip_image009[4]")
– 위에서 구한 값을 end 값으로 넣어서 실행을 하면 된다.
|
$ dbv file=파일명 blocksize=8192 start=1 end= (bytes/db_block_size)
|
![clip_image012[4]](https://i0.wp.com/oracledba.zapto.org/wordpress/wp-content/uploads/2019/01/clip_image0124-1.jpg "clip_image012[4]")
– SEGMENT_ID를 이용해서 특정 Segment만 지정하여 사용할 수도 있다.
|
SQL> select t.ts#, s.header_file, s.header_block from v$tablespace t, dba_segments s where s.segment_name=’EMP_TEST’ and t.name = s.tablespace_name
|
![clip_image015[4]](https://i0.wp.com/oracledba.zapto.org/wordpress/wp-content/uploads/2019/01/clip_image0154.jpg "clip_image015[4]")
– 위 예제처럼 원하는 Segment의 TS#, HEADER_FILE, HEADER_BLOCK를 구하여서 dbv를 이용하여 Check를 할 수 있다. Segment만 사용할 경우에는 userid를 넣어줘야 한다.
|
$ dbv userid=system/oracle segment_id=4.10.851
|
![clip_image018[4]](https://i0.wp.com/oracledba.zapto.org/wordpress/wp-content/uploads/2019/01/clip_image0184-1.jpg "clip_image018[4]")
n Corrupt 된 Object 확인 방법
|
● Corrupt가 발생된 출력 예제
|
– 위와 같이 Corrupt된 예제를 살펴보면 관련 file과 block을 확인 할 수 있다. File과 block를 이용하여 corrupt된 Object를 확인 할 수 있다.
|
SQL > select segment_name, segment_type, owner from dba_extents where file_id = (Absolute file number) and (corrupted block number) between block_id and block_id + blocks -1;
|
![clip_image022[5]](https://i0.wp.com/oracledba.zapto.org/wordpress/wp-content/uploads/2019/01/clip_image0225.jpg "clip_image022[5]")
excellent points altogether, you just gained a new reader. What would you recommend about your post that you made some days ago? Any positive?
Some genuinely superb blog posts on this website , thankyou for contribution.
I want to express thanks to the writer for bailing me out of this scenario. Just after looking out throughout the world wide web and seeing thoughts which are not powerful, I believed my entire life was gone. Living devoid of the solutions to the difficulties you’ve sorted out all through your entire short post is a serious case, and the kind that might have negatively affected my entire career if I had not come across your website. The ability and kindness in handling the whole lot was important. I am not sure what I would have done if I hadn’t encountered such a thing like this. I can at this time look ahead to my future. Thanks for your time so much for your professional and results-oriented guide. I won’t hesitate to refer your web site to anyone who needs to have guidance about this subject matter.
This is really interesting, You are a very skilled blogger. I have joined your rss feed and look forward to seeking more of your great post. Also, I have shared your web site in my social networks!
Fantastic beat ! I would like to apprentice while you amend your site, how can i subscribe for a blog site? The account aided me a acceptable deal. I had been a little bit acquainted of this your broadcast provided bright clear idea
Very interesting subject, thank you for putting up.
hello!,I love your writing very much! share we keep in touch extra about your post on AOL? I need a specialist on this area to resolve my problem. May be that’s you! Having a look forward to look you.
A person necessarily help to make critically posts I’d state. This is the first time I frequented your website page and thus far? I surprised with the research you made to make this actual publish incredible. Great task!
I really appreciate this post. I have been looking everywhere for this! Thank goodness I found it on Bing. You’ve made my day! Thank you again!
I simply couldn’t depart your site prior to suggesting that I extremely enjoyed the usual information a person provide to your visitors? Is gonna be back steadily in order to investigate cross-check new posts.
I gotta bookmark this internet site it seems very useful very beneficial
I believe you have observed some very interesting points , thanks for the post.
Some genuinely select posts on this website , saved to bookmarks .
Informative article, totally what I needed.
Your means of describing the whole thing in this post is in fact pleasant, all can simply be aware of it, Thanks a lot.|
Thanks , I’ve just been searching for info about this topic for a long time and yours is the best I’ve came upon so far. But, what concerning the conclusion? Are you positive about the supply?|