Oracle admin1 -1

select  * from v$parameter

     where name like '%db%';

show parameter;

 

 

$ORACLE_HOME (/dbaA/dba/dba9ixx)

 |

 |-dbs-|

             |- initDBA9Ixx.ora

             |

             |  shared_pool_size = 32M

             |  db_cache_size = 12M

 

* parameter value 확인

 1) Query

     select * from v$parameter

     where name like '%db%';

 2) SQL Plus command

     show parameter db

 

도움말 : http://otn.oracle.com

 

* parameter class

 1) Dynamic : 운영중 수정 가능

 2) static :         "       수정 불가

 

* pfile : static parameter file (8i)

 

 initDBA9ixx.ora

           I_ $ORACLE_SID

database buffer cache 12M -> alter system set db_cache_size = 20M;

-> 20M -> shutdown -> startup -> 12M (static)

 

* spfile : persistent parameter file (9i)

 

 spfileDBA9ixx.ora

             I_ $ORACLE_SID

database buffer cache 12M -> alter system set db_cache_size = 20M;

*scop = [meoery, spfile, both,(default)] 옵션

-> 20M -> shutdown -> startup -> 20M (dynamic)

 

 

3-10. spfile 생성시

*syntax

 1) [] : 생략 가능.

 2)  |  : or 선택.

 3)  _  : default 값.

 

* parameter file type check

 

 show parameter spfile

 1) null : pfile 사용중

 2) 경로 / 이름 : spfile 사용중

          ?  -> $ORACLE_HOME

          @ -> $ORACLE_SID

 

 

3-14. spfileDBA9Ixx.ora

 

 1) ynamic

  *.db.cache_size=12M

   I_ 아스트리크 표시는 Single instance

 2) static

  *.sga_max_size =100M

 3) parameter 삭제 (default)

  *.open_cursor = 100

 

alter system

set db_cache_size = 20M [sope = both sid='*'];

 

alter system

set sga_max_size = 120M; -> 수정할 수 없음

 (pecified initialization parameter cannot be modified)

 

alter system

set sga_max_size = 120M scope=spfile -> 수정 가능

 

alter system

reset open_cursors scope=spfile sid='*';

                                                       I______ 삭제시 필수!

 

$ORACLE_HOME

  |                              ↓ (1)        startup!

  |-DBS- initDBA9Ixx.ora

  |                        I_spfile=/dbaA/dba/.../spfileDBA9Ixx.ora

  |-oradata-|                     

                   |                     ↓ (2)

                   |-spfileDBA9Ixx.ora

 

 

3-25.

--shutdown immediate

--startup nomount

 

alter database mount;

select * from v$instance;

 

3-26.

alter system

enable restricted session;

select * from v$instance;

 

logins 컬럼 = restricted 로 표시.

 

hr/hr 접속시 에러.

ERROR:

ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege

 

 

 

      hr

      ↓

① login -> session 생성

                         ↓

                            ↓ session 유지

                            ↓

 

 

      sys

       ↓

 ② alter system

     enable restricted session;

     select * from v$session;

 

 ③ alter system

     kill session '8, 15';

     select * from v$session;

 

 

3-29.

* 재시작시 read write

 

select * from v$database; (open_moed)

 

 

 

3-31. Database 종료

*normal

 

         hr                                  sys

 

    insert...                      

    update...

 

    select...                         shutdown;

    ...                                         ↓

                                               ↓   대기

 

     exit     →→→→→→→→  progress!

                                         (checkpoint)

                                         (file close)

 

 

* Transactional

 

          hr                                   sys

 

 

         insert...                           

         update...

                                                shutdown transactional; 

                                                    ↓

                                                         ↓   대기

      commit;/rollback;

                   →→→→→→→→   progress!

                                                 (checkpoint)

                                             (file close)

 

* Immediate

 

   hr                                  sys

 

    insert...                      

    update...

 

    select...                         shutdown immediate; →→→→ 강제 rollback

    ...                                         ↓

                                               ↓   대기

 

     exit     →→→→→→→→  progress!

                                         (checkpoint)

                                         (file close)

 

위 세가지 정상 종료.

 

 

* abort = 정전

 shutdown abort (비정상 종료)

 

> shoutdown transactional → 실행

                          .

                           .

                           .

          pending! (hang) → 걸렸을때

(ex: >shutdown immediate 하면 → error)

 

> shutdown abor 실행.

 

 

* startup force

     : shutdown abort → startup.

 

 

 

+:+: DB 삭제.

 

select * from v$datafile;

select * from v$tempfile;

select * from v$controlfile;

select * from v$logfile;

 

$ORACLE_HOME (/dbaA/dba/dba9ixx)

----------------

    |

    |-dbaㅡ|...   .dbf

                 |...   .ctl

                 |...   .rdl

 

1) shutdown abort

2) telnet

             ]rm *.dbf

             ]rm *.ctl

             ]rm *.rdo

             이후  → initDBA9I01.ora , create_dba9i01.sql 두개 제외 모두 삭제.

 

 

4-16. 수동으로 Database 생성

 

-NOMOUNT 단계에서 시작.

 

① startup nomount

 

② create database DBA9Ixx

logfile

      L group 1 ('/dbaA/dba/dba9ixx/dbs/log1a.rdo') size 10M,

      L group 2 ('/dbaA/dba/dba9ixx/dbs/log2a.rdo') size 10M

datafile '/dbaA/dba/dba9ixx/dbs/system01.dbf' size 200M

 

limit(제한)- max log files 10 → group 최대 갯수

               - max log members 5 → group당 최대 file 갯수

                - max datafiles → 100

                - max instance → 1

 

undo tablespace undotbs1

datafile '/dbaA/dba/dba9ixx/dbs/undotbs01.dbf' size 100M

default temporary tablespace temp

temp file '/dbaA/dba/dba9ixx/dbs/temp01.dbf' size 60M

 

character set  KO16KSC5601 (1~2 byte 가변길이)

                        AL32UTF8 (1~4 byte 가변길이)

 

national character set AL16UTF16(9i)       (2 byte 가변길이)

                                       UTF8 (8i)               (1~3 가변길이)

 

③

run ->@?/rdbms/admin/catalog.sql

          @?/rdbms/admin/catproc.sql

 

system user 로 접속.

 

          @?/rdbms/admin/pupbld.sql

 

필요하면 추가로 별도의 Tablespace 생성.

 

create tablespace users

datafile '?/dbs/user01.dbf' size 50M;

 

 

]sqlplus '/ as sysdba'

>@?/dbs/create_dba9ixx.sql

 

 

 

 

 

 

5-8. Data dictionary

 

select * from dictionary;

 

Data Dictionary view 목록

1) static data dictionary view (DBA_xxx, all_xxx, user_xxx)

    |- DDL 실행시에만 변경

    |   Database의 저장 정보 (구조 disk 쪽)

 

2) dynamic data dictionary view (v$xxxxx)

    |- 시시각각 자동변경

    |   instance/control file 정보

 

 

 

* sys 유저의 원격 연결

 1) parameter 설정

     remote_login_passwordfile=exclusive

 2) passwordfiel 생성.                                                        ↓한칸 내리기  

    os] orapwd file=$ORACLE_HOME/dbs/orapwDBA9Ixx \

                                           must ↑                  ↑$ORACLE_SID

 

  > password = oracle entries = 5(인증 항목 최저 갯수)

 

 

*spfile 계정생성

 

$ORACLE_HOME

     |-demo-|

                   |-schema-|

                                         |-human_resources-|

                                                                             |-hr_main.sql

 

SQL>@?/demo/schema/human_esourceshr_main.sql

 

1) 암호

2) defalut tablespace : users

3) temporory tablespace : temp

4) sys's 암호 : oracle

5) log file 의 위치 : .