DB스토리

논리적 => index rebuild

물리적 => BMR (Block Media Recovery)

 

 

 

 

 

 

-- tablespace 옮기기.

 

S SCOTT> select table_name,tablespace_name from user_tables where table_name='EMP';

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
EMP                            USERS

S SCOTT> alter table emp move tablespace example;

Table altered.

S SCOTT> select table_name,tablespace_name from user_tables where table_name='EMP';

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
EMP                            EXAMPLE

 

-- 문제점

rowid가 다 바뀌었음

 

S SCOTT> select rowid from dept;

ROWID
------------------
AAANBpAAEAAAAG8AAA
AAANBpAAEAAAAG8AAB
AAANBpAAEAAAAG8AAC
AAANBpAAEAAAAG8AAD

S SCOTT> alter table dept move tablespace example;

Table altered.

S SCOTT>  select rowid from dept;

ROWID
------------------
AAANOoAAFAAAAQEAAA
AAANOoAAFAAAAQEAAB
AAANOoAAFAAAAQEAAC
AAANOoAAFAAAAQEAAD

 

 

데이터 파일 바뀜. -> rowid 수정->index 작동 안함.   => 논리적 에러.

 

 

--현상의 문제. select ......where empno=10 ==> 느려짐.

--검출

 S SCOTT> analyze table emp validate structure cascade;
analyze table emp validate structure cascade
*
ERROR at line 1:
ORA-01502: index 'SCOTT.PK_EMP' or partition of such index is in unusable state

S SCOTT> alter index pk_emp rebuild;

Index altered.

 

S SCOTT> analyze table dept validate structure cascade;
analyze table dept validate structure cascade
*
ERROR at line 1:
ORA-01502: index 'SCOTT.PK_DEPT' or partition of such index is in unusable state

S SCOTT> alter index pk_dept rebuild;

Index altered.

 

 

S HR> select table_name,tablespace_name from user_tables where table_name='EMPLOYEES';

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
EMPLOYEES                      EXAMPLE

S HR> alter table EMPLOYEES move tablespace users;

Table altered.

 

S HR> select 'alter index '||index_name||' rebuild online;' from user_indexes
              where table_name='EMPLOYEES'

'ALTERINDEX'||INDEX_NAME||'REBUILDONLINE;'
----------------------------------------------------------
alter index EMP_NAME_IX rebuild online;
alter index EMP_MANAGER_IX rebuild online;
alter index EMP_JOB_IX rebuild online;
alter index EMP_DEPARTMENT_IX rebuild online;
alter index EMP_EMP_ID_PK rebuild online;
alter index EMP_EMAIL_UK rebuild online;

6 rows selected.

 

복사해서 붙이고 실행.

S HR>analyze table dept validate structure cascade;
Table analyzed.

물리적 오류시

 

--준비

 S SYS> create tablespace ts datafile '/home/oracle/ts.dbf' size 10M;

Tablespace created.

 

RMAN> backup tablespace ts;

 

 S HR> create table t1(id number(10), data1 char(100), data2 varchar2(100)) tablespace ts;

Table created.

 

S HR> insert into t1 values(1,'zyx','wvu');

1 row created.

S HR> commit;

Commit complete.

현재 버퍼에만 데이터가 들어가있고 데이터 파일에 넣기 위해서 checkpoint 실행

 

S SYS> alter system checkpoint;

System altered.

 

 --고장내기 -for BMR

 

 vi -b ts.dbf

:%!xxd

/zyx

        값 바꾸고

:%!xxd -r

 

 

 --고장난 현상 보기.

 S SYS> alter system flush buffer_cache;                  버퍼 캐쉬 지우기.

System altered.

 S SYS> select * from hr.t1;                     

        유저 에러 발생후 신고.

 

 [oracle@orcl ~]$ cd /u01/app/oracle/admin/orcl/bdump
[oracle@orcl bdump]$ vi alert_orcl.log

sys입장에서 확인.

 

 

dbv : 여부 + 갯수

exp: 위치

 

 

--hr 에러 발생 신고

--hr 사용하고 있는 ts 목록

select distinct tablespace_name from ( select distinct tablespace_name from dba_tables where owner='HR'
union all
select distinct tablespace_name from dba_indexes where owner='HR')
/

 

TABLESPACE_NAME
------------------------------

USERS
EXAMPLE
TS

--hr이 사용하고 있는 datafile 목록?

select name from v$datafile where ts# in(
select ts# from v$tablespace where name in(
select distinct tablespace_name from (select distinct tablespace_name from dba_tables where owner='HR'
 union all
select distinct tablespace_name from dba_indexes where owner='HR')))
/

       NAME
--------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_53mk33rr_.dbf
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_53ydv7gf_.dbf
/home/oracle/ts.dbf

 

 

--hr이 소유한 객체에서 물리적인 일기 에러시 에러 유무 및 깨진 블록의 갯수를 확인. 

 

      -dbv   사용법이 나옴.

 [oracle@orcl ~]$ dbv

DBVERIFY: Release 10.2.0.1.0 - Production on Tue Jun 23 16:08:06 2009

Copyright (c) 1982, 2005, 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)

[oracle@orcl ~]$ dbv file="/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_53mk33rr_.dbf" blocksize=8192

DBVERIFY: Release 10.2.0.1.0 - Production on Tue Jun 23 16:09:21 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

DBVERIFY - Verification starting : FILE = /u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_53mk33rr_.dbf

 

DBVERIFY - Verification complete

Total Pages Examined         : 34720
Total Pages Processed (Data) : 18594
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 10709
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 1008
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 4409
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Highest block SCN            : 11670242 (0.11670242)

 

 

[oracle@orcl ~]$ dbv file="/home/oracle/ts.dbf"

DBVERIFY: Release 10.2.0.1.0 - Production on Tue Jun 23 16:10:22 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

DBVERIFY - Verification starting : FILE = /home/oracle/ts.dbf
Page 16 is marked corrupt
Corrupt block relative dba: 0x02800010 (file 10, block 16)
Bad check value found during dbv:
Data in bad block:
 type: 6 format: 2 rdba: 0x02800010
 last change scn: 0x0000.00b2181c seq: 0x1 flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x181c0601
 check value in block header: 0x2f2e
 computed block checksum: 0x5b0e

 

DBVERIFY - Verification complete

Total Pages Examined         : 1280
Total Pages Processed (Data) : 4
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 11
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 1264
Total Pages Marked Corrupt   : 1                                                  =>   에러난 파일 갯수.
Total Pages Influx           : 0
Highest block SCN            : 11671572 (0.11671572)

 

 

 -exp

 [oracle@orcl ~]$ exp hr/hr

긍정적으로 엔터 칠 것.
에러 위치 보여줌.

 

 

rman

 

RMAN> blockrecover datafile (exp 해서 나온  파일위치) block(exp 해서 나온 블록 위치);

 

 

 

 

 

DBV 팁

 

S SYS> create sequence tmp;

Sequence created.
S SYS> select 'dbv file='||name||' logfile=dbv'||tmp.nextval||'.log' from v$datafile;

'DBVFILE='||NAME||'LOGFILE=DBV'||TMP.NEXTVAL||'.LOG'
--------------------------------------------------------------------------------------------------------------
dbv file=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_50sxqqv0_.dbf logfile=dbv12.log
dbv file=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_52d4tltd_.dbf logfile=dbv13.log
dbv file=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_50sxqqvn_.dbf logfile=dbv14.log
dbv file=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_53mk33rr_.dbf logfile=dbv15.log
dbv file=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_53ydv7gf_.dbf logfile=dbv16.log
dbv file=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_ts1_519t7p0h_.dbf logfile=dbv17.log
dbv file=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_ts1_519tbfc4_.dbf logfile=dbv18.log
dbv file=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_rcat_ts_53k2b11h_.dbf logfile=dbv19.log
dbv file=/u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_5316d4z3_.dbf logfile=dbv20.log
dbv file=/home/oracle/ts.dbf logfile=dbv21.log
dbv file=/home/oracle/disk2/ORCL/datafile/myts_reorg0.dbf logfile=dbv22.log

11 rows selected.