Oracle/기타2010. 12. 16. 22:00

[펌] plan의 hash value는 버전이 올라가도 같습니까?






Q :

PLAN_HASH_VALUE 값을 비교하면 실행 계획이 동일한지 아닌지 알 수 있다고 합니다. 이 값을 버전 업그레이드시에 실행 계획을 비교하는데 사용해도 무방할만큼 믿을만한가요?

믿을 만하지 않다면 어떤 점을 조심해야하는지요?


A :

1. PLAN_HASH_VALUE 값을 계산하는 방식은 Oracle 9i, 10g, 11g에서 동일합니다. 따라서 버전과 무관하게 사용할 수 있습니다.

2. 단, PLAN_HASH_VALUE 값을 계산할 때 실행 계획에서 사용되는 모든 값을 참조하는 것이 아니라 다음과 같은 일부값을 사용합니다.

1) 실행 계획 상의 오퍼레이션 유형(TABLE ACCESS FULL, INDEX RANGE SCAN 등)

2) 실행 계획 상의 오브젝트 이름(T1, T2 등)

3) 병렬 실행에서의 Distribution 방식과 순서

문제는 위의 정보가 일치한다고 하더라도 실제 실행 계획은 다를 수 있다는 것입니다. 가령 특정 조건(Predicate)이 ACCESS로 사용되느냐, FILTER라 사용되는냐에 따라 실제로는 다른 실행 계획으로 볼 수 있습니다. 하지만 이 정보는 PLAN_HASH_VALUE를 계산할 때는 참조되지 않습니다.

위의 정보를 조합해보면 PLAN_HASH_VALUE 값으로 실행 계획의 동일성 여부를 판단하는 것은 99%의 경우에는 신뢰할 수 있지만, 특수한 일부 경우에서는 신뢰할 수 없는 경우도 있다고 결론내릴 수 있습니다.

만일 필요하다면 위에서 열거한 정보 외에 다른 정보를 포함한 독자적인 Hash Value를 계산해서 실행 계획의 동일성 여부를 확인할 수도 있습니다.



출처 : http://121.254.172.39:8080/pls/apex/f?p=101:11:0::::P11_QUESTION_ID:12242200346708503

'Oracle > 기타' 카테고리의 다른 글

조동욱 님의 새로운 책 "오라클 성능 트러블 슈팅의 기초"  (0) 2011.05.31
LoadRunner software  (0) 2011.02.02
단말기폭 초과 발생시  (0) 2010.11.13
SQL*PLUS에서 spool 시에 파일명에 날짜 추가하기  (0) 2010.10.13
SQL*PLUS에서 명령 편집  (0) 2010.07.08
Posted by 자수성가한 부자
Oracle/Admin2010. 12. 15. 16:53

블록 크기가 다른 경우 migration이 잘 될까? (16kbytes -> 8kbytes)





Q : 현재 db block size가 16kbytes로 설정된 db가 있다. 이 db의 데이터를 export/import를 이용하여
     8kbyte의 db block size를 갖는 db로 마이그레이션을 하려고 한다. 
     정상적으로 migration이 될까?
      반대로 8 kbytes -> 16 kbytes 도 될까?


1. 16 kbytes -> 8 kbytes migration


서버 1 ( db block size = 16kbytes) export

1. 버전 확인


SQL> select * from v$version where rownum <= 1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod



2. db block size 확인

SQL> show parameter db_block_size
NAME         TYPE      VALUE
------------------------------------ ---------------------- ------------------------------
db_block_size        integer      16384



3. 유저 생성 / 권한 부여

SQL> create user test identified by test;
User created.
SQL> grant resource, connect to test;
Grant succeeded.



4. 테이블 생성 / 데이터 입력

SQL> create table t1 (col1 number, col2 varchar2(20));
Table created.
SQL> insert into t1
  2  select level, '16k to 8k test'
  3  from dual
  4  connect by level <= 100;
100 rows created.
SQL> commit;
Commit complete.

SQL> select count(*) from t1;
  COUNT(*)
----------
       100
SQL> select * from t1 where rownum <= 10;
      COL1 COL2
---------- ----------------------------------------
  1 16k to 8k test
  2 16k to 8k test
  3 16k to 8k test
  4 16k to 8k test
  5 16k to 8k test
  6 16k to 8k test
  7 16k to 8k test
  8 16k to 8k test
  9 16k to 8k test
 10 16k to 8k test
10 rows selected.




5. 데이터 export(유저)

$ exp test/test file=16k_to_8k.dmp log=16k_to_8k_exp.log

$ ls -lrt 16*
-rw-r--r--  1 oracle oinstall  1241 12월 16 14:57 16k_to_8k_exp.log
-rw-r--r--  1 oracle oinstall 16384 12월 16 14:57 16k_to_8k.dmp




서버 2 ( db block size = 8kybytes) import

1. 버전 확인


SQL> select * from v$version where rownum <= 1;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod



2. db block size 확인


SQL> show parameter db_block_size
NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
db_block_size        integer  8192



3. 유저 생성


SQL> create user test identified by test;
User created.
SQL> grant resource, connect to test;
Grant succeeded.



4. 데이터 import

$ imp test/test file=16k_to_8k.dmp log=16k_to_8k_imp.log



5. 데이터 확인

SQL> show user
USER is "TEST"
SQL> select count(*) from t1;
  COUNT(*)
----------
       100
SQL> select * from t1 where rownum <= 10;
      COL1 COL2
---------- --------------------
  1 16k to 8k test
  2 16k to 8k test
  3 16k to 8k test
  4 16k to 8k test
  5 16k to 8k test
  6 16k to 8k test
  7 16k to 8k test
  8 16k to 8k test
  9 16k to 8k test
 10 16k to 8k test
10 rows selected.




2. 8 kbytes -> 16 kbytes migration


서버 1 ( db block size = 8kbytes) export

1 ~ 3은 생략
1. 버전 확인
2. db block size 확인
3. 유저 생성 / 권한 부여

4. 테이블 생성 / 데이터 입력

SQL> drop table t1 purge;
Table dropped.
SQL> create table t1 (col1 number, col2 varchar2(20));
Table created.
SQL> insert into t1
  2  select level, '8k to 16k test'
  3  from dual
  4  connect by level <= 150;
150 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from t1;
  COUNT(*)
----------
       150
SQL> select * from t1 where rownum <= 10;
      COL1 COL2
---------- --------------------
  1 8k to 16k test
  2 8k to 16k test
  3 8k to 16k test
  4 8k to 16k test
  5 8k to 16k test
  6 8k to 16k test
  7 8k to 16k test
  8 8k to 16k test
  9 8k to 16k test
 10 8k to 16k test
10 rows selected.



5. 데이터 export

$ exp test/test file=8k_to_16k.dmp log=8k_to_16k_exp.log



서버 2 ( db block size = 16kbytes) import

1 ~ 3은 생략
1. 버전 확인
2. db block size 확인
3. 유저 생성 / 권한 부여



4. 데이터 import

-- 이미 생성되어 있는 테이블을 삭제
SQL> drop table t1 purge;
Table dropped.

$ imp test/test file=8k_to_16k.dmp log=8k_to_16k_imp.log





5. 데이터 확인

SQL> show user
USER is "TEST"
SQL> select count(*) from t1;
  COUNT(*)
----------
       150
SQL> select * from t1 where rownum <= 10;
      COL1 COL2
---------- ----------------------------------------
  1 8k to 16k test
  2 8k to 16k test
  3 8k to 16k test
  4 8k to 16k test
  5 8k to 16k test
  6 8k to 16k test
  7 8k to 16k test
  8 8k to 16k test
  9 8k to 16k test
 10 8k to 16k test
10 rows selected.



A : 
테스트의 범위가 조금 협소하긴 하지만 16kbytes -> 8kbytes, 8kbytes -> 16kbytes의 마이그레이션은 잘 되는 것으로 판단됨.

'Oracle > Admin' 카테고리의 다른 글

[펌] 아카이브 로그 파일 자동 삭제 스크립트  (0) 2011.01.05
유저의 디폴트 테이블 스페이스  (0) 2010.12.21
index의 테이블스페이스 변경  (0) 2010.12.15
index tree dump 뜨기  (0) 2010.11.29
오라클 자동 기동  (0) 2010.11.08
Posted by 자수성가한 부자
Oracle/Admin2010. 12. 15. 16:10

index의 테이블스페이스 변경






Q : 인덱스가 저장되는 테이블 스페이스가 잘못 지정되었을 경우 테이블스페이스를 어떻게 옮길까?


데이터베이스의 버전은 아래와 같다.

SQL> select * from v$version where rownum <=1;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod




우선, 테스트를 위한 테이블 스페이스들과 유저를 생성한다.

SQL> create tablespace test_data
  2  datafile '/u01/app/oracle/oradata/ORCL/test_data01.dbf' size 10m;

Tablespace created.

SQL> create tablespace test_idx
  2  datafile '/u01/app/oracle/oradata/ORCL/test_idx01.dbf' size 10m;

Tablespace created.

SQL> create user test identified by test
  2  default tablespace test_data;
User created.
SQL> grant resource, connect to test;
Grant succeeded.



test 유저로 접속한다.

SQL> conn test/test
Connected.

테이블을 생성하고 데이터를 입력해준다.


SQL> create table t1(col1 number);
Table created.
SQL> insert into t1
  2  select level
  3  from dual
  4  connect by level<=100;
100 rows created.
SQL> commit;
Commit complete.

인덱스를 생성해준다.


SQL> create index t1_col1 on t1(col1);
Index created


생성한 인덱스가 어느 테이블 스페이스에 저장되어 있는지 확인한다.


SQL> select index_name, tablespace_name 
  2  from all_indexes
  3  where index_name = 'T1_COL1';
INDEX_NAME         TABLESPACE_NAME
------------------------------ ------------------------------
T1_COL1          TEST_DATA


TEST_DATA 테이블 스페이스에 저장되어 있을 것이다. 이것을 TEST_IDX 테이블 스페이스로 옮기는 작업을 할 것이다.



SQL> alter index t1_col1 rebuild tablespace test_idx;
Index altered.


다시 한번 인덱스가 어느 테이블 스페이스에 생성되었는지 확인해본다.


SQL> select index_name, tablespace_name 
from all_indexes
where index_name = 'T1_COL1';
  2    3 
INDEX_NAME         TABLESPACE_NAME
------------------------------ ------------------------------
T1_COL1          TEST_IDX


TEST_IDX 테이블 스페이스로 옮겨진 것을 확인할 수 있다.



A : alter index <인덱스명> rebuild tablespace <옮길 테이블 스페이스명>; 구문을 실행한다.

'Oracle > Admin' 카테고리의 다른 글

유저의 디폴트 테이블 스페이스  (0) 2010.12.21
블록 크기가 다른 경우 migration이 잘 될까? (16kbytes -> 8kbytes)  (0) 2010.12.15
index tree dump 뜨기  (0) 2010.11.29
오라클 자동 기동  (0) 2010.11.08
데이터 파일 자동 증가  (0) 2010.10.15
Posted by 자수성가한 부자
Oracle/Admin2010. 11. 29. 08:16

index tree dump 뜨기






테스트용 테이블과 인덱스를 생성한다.

SQL> create table t1
         as
         select level as c1, rpad('x',10) as c2
         from dual
         connect by level <= 10000;

SQL> create index t1_idx1 on t1(c1, c2);




현재 index의 구조를 알아보기 위해 treedump를 뜬다.
tree dump를 뜨기 위하여 object_id를 구해야 한다. object id를 구하는 SQL은 다음과 같다.

SQL> select object_id, object_name
     from dba_objects
     where object_name = 'T1_IDX1';

     
 OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------
     52954 T1_IDX1


SQL> alter session set events 'immediate trace name treedump level 52954';

 


user_dump_dest에 설정되어 있는 디렉토리로 이동하여 trace파일을 확인한다.

trace 파일을 확인해보면...

*** 2010-11-18 16:20:33.409
----- begin tree dump
branch: 0x40f44a 4256842 (0: nrow: 37, level: 1)
   leaf: 0x40f44b 4256843 (-1: nrow: 280 rrow: 280)
   leaf: 0x40f44c 4256844 (0: nrow: 276 rrow: 276)
   leaf: 0x40f44d 4256845 (1: nrow: 276 rrow: 276)
   leaf: 0x40f44e 4256846 (2: nrow: 276 rrow: 276)
   leaf: 0x40f44f 4256847 (3: nrow: 276 rrow: 276)
   leaf: 0x40f450 4256848 (4: nrow: 276 rrow: 276)
   leaf: 0x40f451 4256849 (5: nrow: 276 rrow: 276)
   leaf: 0x40f452 4256850 (6: nrow: 276 rrow: 276)
   leaf: 0x40f453 4256851 (7: nrow: 276 rrow: 276)
   leaf: 0x40f454 4256852 (8: nrow: 276 rrow: 276)
   leaf: 0x40f455 4256853 (9: nrow: 276 rrow: 276)
   leaf: 0x40f456 4256854 (10: nrow: 276 rrow: 276)
   leaf: 0x40f457 4256855 (11: nrow: 276 rrow: 276)
   leaf: 0x40f458 4256856 (12: nrow: 276 rrow: 276)
   leaf: 0x40f459 4256857 (13: nrow: 276 rrow: 276)
   leaf: 0x40f45a 4256858 (14: nrow: 276 rrow: 276)
   leaf: 0x40f45b 4256859 (15: nrow: 276 rrow: 276)
   leaf: 0x40f45c 4256860 (16: nrow: 276 rrow: 276)
   leaf: 0x40f45d 4256861 (17: nrow: 276 rrow: 276)
   leaf: 0x40f45e 4256862 (18: nrow: 276 rrow: 276)
   leaf: 0x40f45f 4256863 (19: nrow: 276 rrow: 276)
   leaf: 0x40f460 4256864 (20: nrow: 276 rrow: 276)
   leaf: 0x40f461 4256865 (21: nrow: 276 rrow: 276)
   leaf: 0x40f462 4256866 (22: nrow: 276 rrow: 276)
   leaf: 0x40f463 4256867 (23: nrow: 276 rrow: 276)
   leaf: 0x40f464 4256868 (24: nrow: 276 rrow: 276)
   leaf: 0x40f465 4256869 (25: nrow: 276 rrow: 276)
   leaf: 0x40f466 4256870 (26: nrow: 276 rrow: 276)
   leaf: 0x40f467 4256871 (27: nrow: 276 rrow: 276)
   leaf: 0x40f468 4256872 (28: nrow: 276 rrow: 276)
   leaf: 0x40f469 4256873 (29: nrow: 276 rrow: 276)
   leaf: 0x40f46a 4256874 (30: nrow: 276 rrow: 276)
   leaf: 0x40f46b 4256875 (31: nrow: 276 rrow: 276)
   leaf: 0x40f46c 4256876 (32: nrow: 276 rrow: 276)
   leaf: 0x40f46d 4256877 (33: nrow: 276 rrow: 276)
   leaf: 0x40f46e 4256878 (34: nrow: 276 rrow: 276)
   leaf: 0x40f46f 4256879 (35: nrow: 60 rrow: 60)
----- end tree dump


UPDATE가 발생한 후에 index가 어떻게 바뀌었는지 알아보기 위해 UPDATE문을 실행한다.

SQL> update t1
         set c2 = 'yyyyyy'
         where c1 < 5000;

SQL> commit;



update문이 발생한 후 실제로 index가 어떻게 바뀌었는지 확인해보자.
object_id가 동일하므로 이전에 dba_objects 뷰에서 구한 object id를 사용하여
tree dump 수행

SQL> alter session set events 'immediate trace name treedump level 52954';

*** 2010-11-18 16:22:57.538
----- begin tree dump
branch: 0x40f44a 4256842 (0: nrow: 55, level: 1)
   leaf: 0x40f44b 4256843 (-1: nrow: 262 rrow: 131)
   leaf: 0x40f470 4256880 (0: nrow: 298 rrow: 149)
   leaf: 0x40f44c 4256844 (1: nrow: 238 rrow: 119)
   leaf: 0x40f471 4256881 (2: nrow: 314 rrow: 157)
   leaf: 0x40f44d 4256845 (3: nrow: 254 rrow: 127)
   leaf: 0x40f472 4256882 (4: nrow: 298 rrow: 149)
   leaf: 0x40f44e 4256846 (5: nrow: 254 rrow: 127)
   leaf: 0x40f473 4256883 (6: nrow: 298 rrow: 149)
   leaf: 0x40f44f 4256847 (7: nrow: 254 rrow: 127)
   leaf: 0x40f474 4256884 (8: nrow: 298 rrow: 149)
   leaf: 0x40f450 4256848 (9: nrow: 254 rrow: 127)
   leaf: 0x40f475 4256885 (10: nrow: 298 rrow: 149)
   leaf: 0x40f451 4256849 (11: nrow: 254 rrow: 127)
   leaf: 0x40f476 4256886 (12: nrow: 298 rrow: 149)
   leaf: 0x40f452 4256850 (13: nrow: 254 rrow: 127)
   leaf: 0x40f477 4256887 (14: nrow: 298 rrow: 149)
   leaf: 0x40f453 4256851 (15: nrow: 254 rrow: 127)
   leaf: 0x40f478 4256888 (16: nrow: 298 rrow: 149)
   leaf: 0x40f454 4256852 (17: nrow: 254 rrow: 127)
   leaf: 0x40f479 4256889 (18: nrow: 298 rrow: 149)
   leaf: 0x40f455 4256853 (19: nrow: 254 rrow: 127)
   leaf: 0x40f47a 4256890 (20: nrow: 298 rrow: 149)
   leaf: 0x40f456 4256854 (21: nrow: 254 rrow: 127)
   leaf: 0x40f47b 4256891 (22: nrow: 298 rrow: 149)
   leaf: 0x40f457 4256855 (23: nrow: 254 rrow: 127)
   leaf: 0x40f47c 4256892 (24: nrow: 298 rrow: 149)
   leaf: 0x40f458 4256856 (25: nrow: 254 rrow: 127)
   leaf: 0x40f47d 4256893 (26: nrow: 298 rrow: 149)
   leaf: 0x40f459 4256857 (27: nrow: 254 rrow: 127)
   leaf: 0x40f47e 4256894 (28: nrow: 298 rrow: 149)
   leaf: 0x40f45a 4256858 (29: nrow: 254 rrow: 127)
   leaf: 0x40f47f 4256895 (30: nrow: 298 rrow: 149)
   leaf: 0x40f45b 4256859 (31: nrow: 254 rrow: 127)
   leaf: 0x40f480 4256896 (32: nrow: 298 rrow: 149)
   leaf: 0x40f45c 4256860 (33: nrow: 254 rrow: 127)
   leaf: 0x40f481 4256897 (34: nrow: 298 rrow: 149)  
   leaf: 0x40f45d 4256861 (35: nrow: 303 rrow: 276)
   leaf: 0x40f45e 4256862 (36: nrow: 276 rrow: 276)
   leaf: 0x40f45f 4256863 (37: nrow: 276 rrow: 276)
   leaf: 0x40f460 4256864 (38: nrow: 276 rrow: 276)
   leaf: 0x40f461 4256865 (39: nrow: 276 rrow: 276)
   leaf: 0x40f462 4256866 (40: nrow: 276 rrow: 276)
   leaf: 0x40f463 4256867 (41: nrow: 276 rrow: 276)
   leaf: 0x40f464 4256868 (42: nrow: 276 rrow: 276)
   leaf: 0x40f465 4256869 (43: nrow: 276 rrow: 276)
   leaf: 0x40f466 4256870 (44: nrow: 276 rrow: 276)
   leaf: 0x40f467 4256871 (45: nrow: 276 rrow: 276)
   leaf: 0x40f468 4256872 (46: nrow: 276 rrow: 276)
   leaf: 0x40f469 4256873 (47: nrow: 276 rrow: 276)
   leaf: 0x40f46a 4256874 (48: nrow: 276 rrow: 276)
   leaf: 0x40f46b 4256875 (49: nrow: 276 rrow: 276)
   leaf: 0x40f46c 4256876 (50: nrow: 276 rrow: 276)
   leaf: 0x40f46d 4256877 (51: nrow: 276 rrow: 276)
   leaf: 0x40f46e 4256878 (52: nrow: 276 rrow: 276)
   leaf: 0x40f46f 4256879 (53: nrow: 60 rrow: 60)
----- end tree dump

인덱스 블록에 split이 발생한 것을 확인할 수가 있다.


'Oracle > Admin' 카테고리의 다른 글

블록 크기가 다른 경우 migration이 잘 될까? (16kbytes -> 8kbytes)  (0) 2010.12.15
index의 테이블스페이스 변경  (0) 2010.12.15
오라클 자동 기동  (0) 2010.11.08
데이터 파일 자동 증가  (0) 2010.10.15
아카이브 로그 파일 포맷변경  (0) 2010.10.14
Posted by 자수성가한 부자
Oracle/Backup & Recovery2010. 11. 24. 23:53

current redo log group 삭제시 복구

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIV STATUS          FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- ------ -------------------------------- ------------- ------------
  1     1        2   52428800      1 YES    INACTIVE          469524 24-NOV-10
  2     1        3   52428800      1 YES    INACTIVE          469526 24-NOV-10
  3     1        4   52428800      1 NO     CURRENT          469528 24-NOV-10


SQL> select * from v$logfile;

    GROUP# STATUS   TYPE   MEMBER             IS_REC
---------- -------------- -------------- ------------------------------------------------------------ ------
  3    ONLINE  /u01/app/oracle/oradata/orcl/redo02.log        NO
  2    ONLINE  /u01/app/oracle/oradata/orcl/redo03.log        NO
  1    ONLINE  /u01/app/oracle/oradata/orcl/redo01.log        NO


SQL> !rm /u01/app/oracle/oradata/orcl/redo02.log


SQL> !ls /u01/app/oracle/oradata/orcl/redo02.log
ls: /u01/app/oracle/oradata/orcl/redo02.log: No such file or directory



SQL> shutdown abort;
ORACLE instance shut down.


SQL> startup;
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size      1218992 bytes
Variable Size     92276304 bytes
Database Buffers   188743680 bytes
Redo Buffers      2973696 bytes
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/orcl/redo02.log'

 

SQL> alter database backup controlfile to trace as '/u01/app/oracle/con_bak.sql';
Database altered.


SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.


SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options


[oracle@test app]$ cd /u01/app/oracle/


[oracle@test oracle]$ ls
admin  bak_con.sql  con_bak.sql  flash_recovery_area  oraInventory  oradata  product  test_ts02.dbf  test_ts03.dbf


[oracle@test oracle]$ vi con_bak.sql
"con_bak.sql" 147L, 5792C-- The following are current System-scope REDO Log Archival related


CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16   
    MAXLOGMEMBERS 3   
    MAXDATAFILES 100   
    MAXINSTANCES 8   
    MAXLOGHISTORY 292
    LOGFILE 
      GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log'  SIZE 50M, 
      GROUP 2 '/u01/app/oracle/oradata/orcl/redo03.log'  SIZE 50M,
      GROUP 3 '/u01/app/oracle/oradata/orcl/redo02.log'  SIZE 50M
   DATAFILE'/u01/app/oracle/oradata/orcl/system01.dbf',
           '/u01/app/oracle/oradata/orcl/undotbs01.dbf',
           '/u01/app/oracle/oradata/orcl/sysaux01.dbf',
           '/u01/app/oracle/oradata/orcl/users01.dbf',
           '/u01/app/test_ts01.dbf'
CHARACTER SET KO16KSC5601;                                                                                                                                           

[oracle@test oracle]$ sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Nov 24 23:40:31 2010

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

Enter user-name: /as sysdba
Connected to an idle instance.


SQL> startup nomount;
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size      1218992 bytes
Variable Size     92276304 bytes
Database Buffers   188743680 bytes
Redo Buffers      2973696 bytes


SQL> @con_bak
CREATE CONTROLFILE SET DATABASE "ORCL" RESETLOGS  ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-00200: control file could not be created
ORA-00202: control file: '/u01/app/oracle/oradata/orcl/control01.ctl'
ORA-27038: created file already exists
Additional information: 1


SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options



[oracle@test oracle]$ cd /u01/app/oracle/oradata/orcl


[oracle@test orcl]$ ls -lrt con*
-rw-r-----  1 oracle dba 7389184 Nov 24 23:38 control03.ctl
-rw-r-----  1 oracle dba 7389184 Nov 24 23:38 control02.ctl
-rw-r-----  1 oracle dba 7389184 Nov 24 23:38 control01.ctl


[oracle@test orcl]$ rm con*


[oracle@test orcl]$ cd /u01/app/oracle


[oracle@test oracle]$ ls
admin  bak_con.sql  con_bak.sql  flash_recovery_area  oraInventory  oradata  product  test_ts02.dbf  test_ts03.dbf


[oracle@test oracle]$ sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Nov 24 23:41:13 2010

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

Enter user-name: /as sysdba

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options


SQL> @con_bak

Control file created.


SQL> alter database open resetlogs;

Database altered.


SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.


SQL> startup;
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size      1218992 bytes
Variable Size     92276304 bytes
Database Buffers   188743680 bytes
Redo Buffers      2973696 bytes
Database mounted.
Database opened.


SQL> show lines
linesize 80


SQL> set lines 200

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIV STATUS          FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- ------ -------------------------------- ------------- ------------
  1     1        0   52428800      1 YES    UNUSED        0
  2     1        1   52428800      1 NO     CURRENT          489578 24-NOV-10
  3     1        0   52428800      1 YES    UNUSED        0


SQL> col member for a60

SQL> /

    GROUP# STATUS   TYPE   MEMBER             IS_REC
---------- -------------- -------------- ------------------------------------------------------------ ------
  3    ONLINE  /u01/app/oracle/oradata/orcl/redo02.log        NO
  2    ONLINE  /u01/app/oracle/oradata/orcl/redo03.log        NO
  1    ONLINE  /u01/app/oracle/oradata/orcl/redo01.log        NO

 

'Oracle > Backup & Recovery' 카테고리의 다른 글

Import 성능 향상 시키는 방법  (0) 2015.06.16
[펌] [Export/Import] EXPORT FILE의 SIZE 예측  (0) 2013.04.03
RMAN  (0) 2010.07.09
start backup 후 end backup을 안했을 경우.  (0) 2010.04.17
Block Corruption  (0) 2009.12.23
Posted by 자수성가한 부자
Oracle/기타2010. 11. 13. 15:35

단말기폭 초과 발생시






Q :

vi 에디터로 파일을 열려고 하면 단말기폭 초과라는 메시지가 나올때가 있다.

이럴 때는 어떻게 해결할까요?


A :

stty columns 160

'Oracle > 기타' 카테고리의 다른 글

LoadRunner software  (0) 2011.02.02
[펌] plan의 hash value는 버전이 올라가도 같습니까?  (0) 2010.12.16
SQL*PLUS에서 spool 시에 파일명에 날짜 추가하기  (0) 2010.10.13
SQL*PLUS에서 명령 편집  (0) 2010.07.08
database의 feature의 사용유무 체크뷰  (0) 2010.05.04
Posted by 자수성가한 부자
Oracle/Admin2010. 11. 8. 10:37

오라클 자동 기동





자동 기동의 구조 이해

자동 기동 관련 파일은 다음의 4개의 파일이다. 1번부터 3번까지는 오라클 패키지에 들어있고, 4번은 유저가 직접 작성한다.
벤더에 따라서 이 파일들이 미리 작성되어 있는 경우도 있다.

1. $ORACLE_HOME/bin/dbstart  DB 기동 스크립트
2. $ORACLE_HOME/bin/dbstop  DB 정지 스크립트
3. /etc/oratab 인스턴스 정의파일
4. /etc/rc.d/init.d/dbora 서비스 스크립트


oratab
oratab은 인스턴스의 SID를 등록하는 파일. oratab의 서식은 아래와 같다.
문장 끝에 Y 또는 N을 넣음으로써, 자동 기동 여부를 설정한다.
인스턴스가 여러 개 있을 경우 인스턴스 수만큼 갯수가 늘어난다.

$ORACLE_HOME:$ORACLE_SID:[Y|N]

dbora
dbora는 OS가 실행하는 오라클 기동/정지 스크립트이다. OS는 직접 dbstart와 dbshut을 실행하지 않고
dbora를 경유로 실행한다. 즉 OS가 기동될 경우 다음의 순서대로 파일이 실행된다.

1. init 프로세스는 /etc/initab를 참조해서 dbora를 기동시킨다.
2. dbora는 dbstart를 실행시킨다.
3. dbstart는 /etc/oratab를 참조해서 자동기동대상의 인스턴스를 기동시킨다.

참고 : http://elflord.egloos.com/2543425

'Oracle > Admin' 카테고리의 다른 글

index의 테이블스페이스 변경  (0) 2010.12.15
index tree dump 뜨기  (0) 2010.11.29
데이터 파일 자동 증가  (0) 2010.10.15
아카이브 로그 파일 포맷변경  (0) 2010.10.14
스케쥴링 하기(일주일마다 통계 정보)  (0) 2010.10.13
Posted by 자수성가한 부자
Oracle/Admin2010. 10. 15. 14:29

데이터 파일 자동 증가






테이블 스페이스에 데이터 파일이 두개 존재하고 데이터 파일의 속성 중 자동 증가 설정이 되어 있다고 가정하자.
그 상태에서 데이터가 계속해서 들어가서 데이터 파일의 기본 설정 크기보다 커졌을 때
어느 데이터 파일이 증가할까요?

테스트를 통해 알아봅시다.

1. 테스트용 테이블 스페이스를 생성하고, 데이터 파일을 추가합니다.

SQL> create tablespace test_ts 
         datafile '/u01/app/oracle/oradata/ORCL/test_ts01.dbf' size 1m autoextend on;
SQL> alter tablespace test_ts 
         add datafile '/u01/app/oracle/oradata/ORCL/test_ts02.dbf' size 1m autoextend on;


2. 1에서 생성한 테이블 스페이스가 제대로 생성이 되었는지 확인해볼까요?

SQL> select tablespace_name, file_name, bytes/1024/1024 "Size(MB)", autoextensible
         from dba_data_files
         where tablespace_name = 'TEST_TS';

TABLESPACE_NAME         FILE_NAME         Size(MB) AUT
------------------------------ -------------------------------------------------- ---------- ---
TEST_TS          /u01/app/oracle/oradata/ORCL/test_ts01.dbf     1 YES
TEST_TS          /u01/app/oracle/oradata/ORCL/test_ts02.dbf     1 YES


3. 테스트용 유저를 생성하자. 옵션 중 디폴트 테이블 스페이스에 1.에서 생성한 테이블 스페이스를 줍니다.
   그리고 테이블을 생성할 수 있도록 resource, connect role을 부여합니다.


SQL> create user testuser identified by testuser
         default tablespace test_ts;
SQL> grant resource, connect to testuser;


4. 3에서 생성한 유저(testuser)로 접속하여, 데이터를 충분히 생성합니다.

SQL> conn testuser/testuser

SQL> insert into t1
        select level, 'testtesttesttesttesttesttest'
        from dual
        connect by level <= 100000;


5. 데이터 파일이 증가되는 것을 확인해봅니다. 일정 주기로 확인해보니 데이터 파일이 고르게 증가되는 것을 확인할 수 있습니다.


SQL> select tablespace_name, file_name, bytes/1024/1024 "Size(MB)", autoextensible
from dba_data_files
where tablespace_name = 'TEST_TS';  2    3 
TABLESPACE_NAME         FILE_NAME            Size(MB) AUT
------------------------------ ------------------------------------------------------------ ---------- ---
TEST_TS          /u01/app/oracle/oradata/ORCL/test_ts01.dbf        1 YES
TEST_TS          /u01/app/oracle/oradata/ORCL/test_ts02.dbf        1 YES
SQL> /
TABLESPACE_NAME         FILE_NAME            Size(MB) AUT
------------------------------ ------------------------------------------------------------ ---------- ---
TEST_TS          /u01/app/oracle/oradata/ORCL/test_ts01.dbf        4 YES
TEST_TS          /u01/app/oracle/oradata/ORCL/test_ts02.dbf        5 YES
SQL> /
TABLESPACE_NAME         FILE_NAME            Size(MB) AUT
------------------------------ ------------------------------------------------------------ ---------- ---
TEST_TS          /u01/app/oracle/oradata/ORCL/test_ts01.dbf        5 YES
TEST_TS          /u01/app/oracle/oradata/ORCL/test_ts02.dbf        5 YES
SQL> /
TABLESPACE_NAME         FILE_NAME            Size(MB) AUT
------------------------------ ------------------------------------------------------------ ---------- ---
TEST_TS          /u01/app/oracle/oradata/ORCL/test_ts01.dbf        5 YES
TEST_TS          /u01/app/oracle/oradata/ORCL/test_ts02.dbf        6 YES
SQL> /
TABLESPACE_NAME         FILE_NAME            Size(MB) AUT
------------------------------ ------------------------------------------------------------ ---------- ---
TEST_TS          /u01/app/oracle/oradata/ORCL/test_ts01.dbf       10 YES
TEST_TS          /u01/app/oracle/oradata/ORCL/test_ts02.dbf       10 YES
SQL> /
TABLESPACE_NAME         FILE_NAME            Size(MB) AUT
------------------------------ ------------------------------------------------------------ ---------- ---
TEST_TS          /u01/app/oracle/oradata/ORCL/test_ts01.dbf       10 YES
TEST_TS          /u01/app/oracle/oradata/ORCL/test_ts02.dbf       11 YES
SQL> /
TABLESPACE_NAME         FILE_NAME            Size(MB) AUT
------------------------------ ------------------------------------------------------------ ---------- ---
TEST_TS          /u01/app/oracle/oradata/ORCL/test_ts01.dbf       16 YES
TEST_TS          /u01/app/oracle/oradata/ORCL/test_ts02.dbf       16 YES
SQL> /
TABLESPACE_NAME         FILE_NAME            Size(MB) AUT
------------------------------ ------------------------------------------------------------ ---------- ---
TEST_TS          /u01/app/oracle/oradata/ORCL/test_ts01.dbf       17 YES
TEST_TS          /u01/app/oracle/oradata/ORCL/test_ts02.dbf       18 YES
SQL> /
TABLESPACE_NAME         FILE_NAME            Size(MB) AUT
------------------------------ ------------------------------------------------------------ ---------- ---
TEST_TS          /u01/app/oracle/oradata/ORCL/test_ts01.dbf       18 YES
TEST_TS          /u01/app/oracle/oradata/ORCL/test_ts02.dbf       19 YES

....


SQL> /
TABLESPACE_NAME         FILE_NAME            Size(MB) AUT
------------------------------ ------------------------------------------------------------ ---------- ---
TEST_TS          /u01/app/oracle/oradata/ORCL/test_ts01.dbf      225 YES
TEST_TS          /u01/app/oracle/oradata/ORCL/test_ts02.dbf      218 YES



번외로, 그리고 데이터 파일이 증가하면 데이터를 rollback을 해주어도 다시 원래의 데이터 파일 크기로 돌아오지 않습니다.
이때는 데이터 파일 resize를 해주면 됩니다.

SQL> alter database datafile '/u01/app/oracle/oradata/ORCL/test_ts01.dbf' resize 1m;
Database altered.
SQL> alter database datafile '/u01/app/oracle/oradata/ORCL/test_ts02.dbf' resize 1m;
Database altered.

'Oracle > Admin' 카테고리의 다른 글

index tree dump 뜨기  (0) 2010.11.29
오라클 자동 기동  (0) 2010.11.08
아카이브 로그 파일 포맷변경  (0) 2010.10.14
스케쥴링 하기(일주일마다 통계 정보)  (0) 2010.10.13
[펌]dbms_metadata 패키지 (테이블 스크립트 가져오기)  (0) 2010.09.16
Posted by 자수성가한 부자
Oracle/Admin2010. 10. 14. 17:11

아카이브 로그 파일 포맷변경



아카이브 로그 파일 포맷 변경은 온라인 중에 가능할까? 라는 의문이 갑자가 생겼다.

그래서 테스트를 진행해 보았다.

SQL> archive log list
Database log mode        Archive Mode
Automatic archival        Enabled
Archive destination        /u01/app/oracle/oradata/ORCL/arc
Oldest online log sequence     21
Next log sequence to archive   23
Current log sequence        23
SQL> show parameter log_archive_format
NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
log_archive_format       string  orcl_%s_%t_%r.arc

SQL> alter system set log_archive_format='orcl_%s_%t_%r.dbf' scope=memory;
alter system set log_archive_format='orcl_%s_%t_%r.dbf' scope=memory
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

=> log_archive_format 파라미터는 온라인 중에 변경되지 않네요..


# 참고 : 파라미터에 관련된 뷰를 조회해보면
SQL> select name, ISSES_MODIFIABLE, ISSYS_MODIFIABLE, ISINSTANCE_MODIFIABLE
from v$parameter
where name like '%archive_format'  2    3  ;
NAME           ISSES_MODI ISSYS_MODI ISINSTANCE
-------------------------------------------------------------------------------- ---------- ---------- ----------
log_archive_format         FALSE     FALSE      FALSE


'Oracle > Admin' 카테고리의 다른 글

오라클 자동 기동  (0) 2010.11.08
데이터 파일 자동 증가  (0) 2010.10.15
스케쥴링 하기(일주일마다 통계 정보)  (0) 2010.10.13
[펌]dbms_metadata 패키지 (테이블 스크립트 가져오기)  (0) 2010.09.16
특정 패키지가 invalid 되었을 때  (0) 2010.09.07
Posted by 자수성가한 부자
Oracle/Admin2010. 10. 13. 22:49

스케쥴링 하기(일주일마다 통계 정보)

2010. 10. 15일 dbms_scheduler를 이용한 스케쥴링 추가


스케쥴링 하려는 시나리오는 다음과 같다.
매주 월요일 10:00에 전주 월요일부터 일요일까지의 데이터를 통계를 낸다.
실행되는 스크립트는 get_weekly_stats.sh이며, 이 스크립트가 통계를 내는 것인데
sqlplus에 접속하여 html의 형식으로 result_stats_20101013.html과 같은 이름의 파일이 생성되면 된다.


# 통계의 소스가 되는 테이블은 아래와 같습니다.

create table t1 (col1 number, col2 date);

insert into t1
select level, sysdate - 7
from dual
connect by level  <= 10
union
select level*2, sysdate -6
from dual
connect by level <= 10
union
select level*3, sysdate -5
from dual
connect by level <= 10
union
select level*4, sysdate -4
from dual
connect by level <= 10
union
select level*5, sysdate -3
from dual
connect by level <=10
union
select level*6, sysdate -2
from dual
connect by level <= 10
union
select level*7, sysdate -1
from dual
connect by level <= 10;

commit;



# 통계정보를 취득하는 쿼리는 아래와 같습니다.

select sum(col1), col2
from t1
where col2 between next_day(sysdate,'mon')-14
         and next_day(sysdate,'sun')-7
group by col2
order by col2;



통계정보 생성용 스크립트의 내용은 아래와 같고, 주의할 점은 이 스크립트 파일에 대해 oracle 유저가 실행권한을 갖고 있어야 합니다.
get_weekly_stats.sh

sqlplus / as sysdba << EOF

set markup html on
set feedback off
set heading off
set echo off
set pages 1000

column date_column new_value today_var
select to_char(sysdate,'yyyymmdd') date_column
from dual
/

set heading on
spool result_stats_&today_var..html

select sum(col1), col2
from t1
where col2 between next_day(sysdate,'mon')-14
      and next_day(sysdate,'sun')-7
group by col2
order by col2
/

spool off

EOF



os 명령어 중 crontab을 이용하면 손쉽게 스케쥴링을 할 수 있다.
아래의 내용을 참고해서 매주 월요일 10:00에 get_weekly_stats.sh을 실행시키도록 crontab에 설정해보면 ...


os] crontab -e    => crontab 편집
00 10 * * 1 /home/oracle/get_weekly_stats.sh
os] crontab -l     => 설정된 내용 확인


위와 같이 설정을 하면 매주 월요일 10:00에 get_weekly_stats.sh 스크립트가 잘 실행됩니다.


########## crontab 참고 내용 ############

crontab에 등록된 작업 보기
os] crontab -l

crontab에 작업 등록 & 수정 하기
os] crontab -e

※작업 등록시 주의할 점
  - 한 줄당 하나의 명령어를 등록
  - 각 필드의 의미는 다음과 같다.
------　　--------　---------------------------------------------------
필　드　　의　미　　범　위
------　　--------　---------------------------------------------------
첫번째　　분　　　　0-59
두번째　　시　　　　0-23
세번째　　일　　　　0-31
네번째　　월　　　　1-12
다섯번째　요일　　　0-7 (0 또는 7=일요일, 1=월, 2=화,...)
여섯번째　명령어　　실행할 명령을 한줄로 쓴다.
------　　--------　---------------------------------------------------

  • 0,15,30,45 * * * * command
    -> 매 15분 마다 실행
  • 10 3 * * * command
    -> 03시 10분에 실행
  • 10 * 1 * * command
    -> 매월 1일 0시 10분에 실행
  • 10 14 * * 1 command
    -> 매주 월요일 14시 10분에 실행


    ################################################


    참고 : http://radiocom.kunsan.ac.kr/lecture/oracle/package/dbms_scheduler.html
             http://blog.daum.net/won-bo/16839169
             http://kwoncharlie.blog.me/10091007798


    dbms_scheduler를 이용한 스케쥴링

    begin
     dbms_scheduler.create_job(
      job_name => 'get_stats',
      job_type => 'executable',
      job_action => '/home/oracle/get_weekly_stats.sh',
      repeat_interval => 'FREQ=DAILY;BYHOUR=17;BYMINUTE=45',
      enabled => true,
      comments => 'LEVEL 0');
    end;
    /
  • begin
     dbms_scheduler.set_attribute(
      name => 'get_stats',
      attribute => 'raise_events',
      value => dbms_scheduler.job_started
      +dbms_scheduler.job_succeeded
      +dbms_scheduler.job_failed
      +dbms_scheduler.job_broken
      +dbms_scheduler.job_stopped
     );
    end;
    /
  • exec dbms_scheduler.run_job('get_stats');
  • exec dbms_scheduler.drop_job('get_stats');





'Oracle > Admin' 카테고리의 다른 글

데이터 파일 자동 증가  (0) 2010.10.15
아카이브 로그 파일 포맷변경  (0) 2010.10.14
[펌]dbms_metadata 패키지 (테이블 스크립트 가져오기)  (0) 2010.09.16
특정 패키지가 invalid 되었을 때  (0) 2010.09.07
show sga의 variable size의 크기는?  (0) 2010.08.27
Posted by 자수성가한 부자

티스토리툴바