[Oracle DBA]오라클 데이터베이스 Instance 관리

DATABASE(oracleDB 11g)/DBA

[Oracle DBA]오라클 데이터베이스 Instance 관리

SEUNGSAMI 2019. 3. 25. 13:22

오라클 데이터베이스 Instance 관리

SYS@orcl2> show parameter dump

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_core_dump                 string      partial
background_dump_dest                 string      /u01/app/oracle/diag/rdbms/orc

-- 우리가 보려하는  alert 파일의 경로를 확인 할 수 있다.

[orcl2:~]$ tail -f /u01/app/oracle/diag/rdbms/orcl2/orcl2/trace/alert_orcl2.log
-- 삭제해도 재생성 된다.

1.NOMOUNT

Instance만 기동된 상태이다.
DB를 생성할 수 있다.

control file 체크, 리두로그그룹 생성 .....

Control file을 재생성 할 수 있다.

Control file이란 DB의 Physical(물리적인) 구조 정보가 있다.
데이터베이스의 동기화 정보가 있다.
물리적인 구조의 사이즈를 변경하기 위해서는 Control file을 재생성 해야한다.

v$instance, v$sga, v$sgastat, v$process

SYS@orcl2> select * from v$instance;
-- 현재 인스턴스의 여러가지 상태 정보를 보여준다.
-- 인스턴스가 active함을 보여준다.


SYS@orcl2> select * from v$database;
select * from v$database
              *
ERROR at line 1:
ORA-01507: database not mounted
-- Database가 마운트되지 않았다.


SYS@orcl2> select * from v$process;

SYS@orcl2> select count(*) from v$process;
  COUNT(*)
----------
        19

2.MOUNT

Control file 파라미터의 내용대로 컨트롤 파일을 찾아서 읽을 수 있는 상태

DB의 Physical(물리적인) 구조 정보가 있다.(v$database)

Datafile 이름과 위치, 사이즈(v$datafile)
Redo log file의 이름과 위치, 크기(v$logfile, v$log)

DB의 동기화 정보가 있다.

checkpoint_change#(v$database)

DB의 일반정보 (ID, 로그인 시간.....)
등...

SYS@orcl2> alter database mount
Database altered.
-- NOMOUNT 상태의 INSTANCE를 MOUNT해준다.

SYS@orcl2> select * from v$database;

SYS@orcl2> select file#, ts#, name, bytes/1024/1024 mb
             from v$datafile;

SYS@orcl2> save datafile
Created file datafile.sql
-- 인스턴스 실습간 자주 쓸거니까 저장해두자.

SYS@orcl2> select group#, member, status
             from v$logfile;

SYS@orcl2> save logfile
Created file logfile.sql
-- 인스턴스 실습간 자주 쓸거니까 저장해두자.

SYS@orcl2> select group#, sequence#, status, archived, members, bytes/1024/1024 mb
             from v$log;

    GROUP#  SEQUENCE# STATUS           ARC    MEMBERS         MB
---------- ---------- ---------------- --- ---------- ----------
         1         10 CURRENT          NO           1         50
         3          9 INACTIVE         NO           1         50
         2          8 INACTIVE         NO           1         50

SYS@orcl2> save log
-- 인스턴스 실습간 자주 쓸거니까 저장해두자.

SYS@orcl2> select * from dba_users;
select * from dba_users
              *
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only
-- 데이터파일의 이름만 알뿐, 열지 않은 상태이기 때문에 오류가 발생한다.

MOUNT모드에서 할 수 있는 3가지

데이터베이스의 log_mode(Control file에 있다)의 변경(NOARCHIVELOG <-> ARCHIVELOG)

SYS@orcl2> select log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOG

Datafile의 이름을 변경(= Rename file)

control file내의 정보 중 경로를 포함한 이름을 변경하겠다는 뜻이다.
ex) /d1/a.dbf => /d5/a.dbf

바꾸는 이유는 디스크 헤더가 깨져 복구 작업을 해야하는 경우 store 공간을 설정해주는데 control file에 경로를 변경해야 하기 때문이다.

Recovery(=Offline recovery)

DB가 오픈이 되지 않은 상태에서의 recovery

3.OPEN

모든 파일(Instance, Control file)이 열린 상태(Database opend)
유저가 데이터베이스에 연결된 상태
데이터베이스 open

데이터파일 열기
온라인 리두로그 파일 열기(오프라인은 아카이브 파일)

SMON이라는 프로세스가 데이터베이스 open 전에 리두로그파일에 쓰여진 정보중 마지막 checkpoint가 마지막에 쓰여진 정보가 데이터 파일에 반영되지 않음을 알게되고, (commit 했는데 데이터가 없거나; roll forward, commit를 안했는데 데이터가 있거나; rollback) 그 과정의 undo를 알고 작업하여 instance recovery를 수행

SYS@orcl2> select * from dba$users;
select * from dba$users
              *
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only

SYS@orcl2> alter database open;
Database altered.

SYS@orcl2> select * from dba_users;

SYS@orcl2> select count(*) from hr.employees;
-- database가 open되어있기 때문에 위의 두명령어가 정상적으로 실행된다.

STARTUP

OPEN

Restrict Mode(제한모드)

RESTRICTED SESSION 권한이 있는 사용자만 DB access

SYS@orcl2> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SYS@orcl2> startup restrict
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area  431038464 bytes
Fixed Size                  1337016 bytes
Variable Size             146803016 bytes
Database Buffers          276824064 bytes
Redo Buffers                6074368 bytes

SYS@orcl2> select logins from v$instance;

LOGINS
----------
RESTRICTED
-- 제한모드가 아닐 경우에는 ALLOWED로 출력된다.

SYS@orcl2> select count(*) from dba_users;

  COUNT(*)
----------
        36

SYS@orcl2> select count(*) from hr.employees;

  COUNT(*)
----------
       107
-- 하지만 현재 SYS로 접속되어있기 때문에 작업을 수행 할 수 있다.

SYS@orcl2> select privilege from session_privs
            where privilege = 'RESTRICTED SESSION';
PRIVILEGE
----------------------------------------
RESTRICTED SESSION

-- RESTRICTED SESSION권한이 있는 USER는 작업을 수행 할 수 있다.


SYS@orcl2> conn hr/hr
ERROR:
ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege
-- RESTRICTED SESSION권한이 없다.

@> conn /as sysdba

SYS@orcl2> grant restricted session to hr;
Grant succeeded.
-- RESTRICTED SESSION 권한을 hr에게 준다

SYS@orcl2> conn hr/hr
Connected.
-- 정상적으로 접속이 된다.

HR@orcl2> select count(*)
            from employees;

  COUNT(*)
----------
       107

HR@orcl2> conn / as sysdba
Connected.

SYS@orcl2> alter system disable restricted session;
System altered.
--DBA가 제한 모드에서 관리작업을 마친 후 제한모드를 disable

SYS@orcl2> select username, account_status
             from dba_users
            where username='SCOTT';

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
SCOTT                          EXPIRED & LOCKED

SYS@orcl2> alter user scott identified by tiger
         account unlock;
User altered.
-- 패스워드 변경, 언락

SYS@orcl2> conn scott/tiger
Connected.


SCOTT@orcl2> conn / as sysdba
Connected.

SYS@orcl2> select privilege from dba_sys_privs
            where grantee='SCOTT';

PRIVILEGE
----------------------------------------
UNLIMITED TABLESPACE
-- 어떤 권한을 받았는지 확인.

SYS@orcl2> select privilege from dba_sys_privs
            where grantee='HR';

PRIVILEGE
----------------------------------------
CREATE VIEW
UNLIMITED TABLESPACE
CREATE DATABASE LINK
CREATE SEQUENCE
RESTRICTED SESSION
CREATE SESSION
ALTER SESSION
CREATE SYNONYM

8 rows selected.


SYS@orcl2> conn scott/tiger
Connected.

SCOTT@orcl2> select count(*) from emp;

  COUNT(*)
----------
        14


SCOTT@orcl2> conn / as sysdba
Connected.
SYS@orcl2> select logins from v$instance;

LOGINS
----------
ALLOWED
-- 제한모드가 아니므로 ALLOWED가 출력

Read Only 로 DB를 open

select * from hr.employees; => 성공
update => 실패
create => 실패
대량의 sort로 Disk sort가 일어나는 것은 OK(temp file에 쓰는 것은 OK)

SYS@orcl2> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.


SYS@orcl2> startup open read only
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area  431038464 bytes
Fixed Size                  1337016 bytes
Variable Size             146803016 bytes
Database Buffers          276824064 bytes
Redo Buffers                6074368 bytes
Database mounted.
Database opened.


SYS@orcl2> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY

SYS@orcl2> select salary from hr.employees
            where employee_id=100;

    SALARY
----------
     24000
-- Read Only에서 읽기는 가능하다.

SYS@orcl2> update hr.employees
  2  set salary = 1000
  3  where employee_id = 100;
update hr.employees
          *
ERROR at line 1:
ORA-16000: database open for read-only access


SYS@orcl2> create table hr.copy_emp( a number );
create table hr.copy_emp( a number )
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access
-- DDL불가


<다시 돌아가기>
SYS@orcl2> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl2> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.


Total System Global Area  431038464 bytes
Fixed Size                  1337016 bytes
Variable Size             146803016 bytes
Database Buffers          276824064 bytes
Redo Buffers                6074368 bytes
Database mounted.
Database opened.

SYS@orcl2> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE
-- 정상적으로 확인 가능

오라클 데이터베이스 Instance 종료

ABORT

정전과 같은 종료
체크포인트 없이 비정상 종료

Instance failure 상황

IMMEDIATE

현재 접속한 유저의 세션을 끊고 체크포인트를 일으키면서 DB종료
새로운 접속은 불가
가장 일반적, 커밋되지 않은 트랜잭션 롤백

TRANSACTIONAL

현재 접속한 유저의 현재 TX(Transaction)이 끝날 때까지 기다렸다가 체크포인트를 일으키면서 DB종료

NORMAL

현재 접속한 모든 유저가 접속을 끊고 나갈 때까지 기다렸다가, 체크포인트를 일으키면서 DB종료

Server Control 명령어

$ srvctl start database -d orcl -o open
$ srvctl stop database -d orcl -o immediate

Alert file, trace file, V$ views

오라클에서 성능, 문제 상황을 진단하기 위한 정보(도구)들

alert log file : alert_<SID>.log

Append 된다 => file 관리가 필요( 주기적으로 지우거나, 백업)

DB 생성, startup, shutdown 언제 했는디, 어느 옵션을 걸었는지

DB 구조를 변경한 명령어들

테이블스페이스 추가 , 삭제
redo log file 추가 삭제

기본값이 아닌 parameter
중요한 에러

백그라운드 프로세스 에러
dead lock
oracle internal error : ORA-600

trace file

백그라운드 프로세스의 에러가 발생하면 생성

<SID>_<process_id{ex:SMON, DBWR}>_<pid>.trc

서버프로세스가 생성하는 trace file

<SID>_ora_<pid>.trc
sql trace 를 썼을때
backup control file
dead lock(유일한 에러)

어떤 상황에서 발생했는지
어떤 테이블에 어떤 행이 dead lock을 유발하는지

ADR(Automatic Diagnostic Repository)

trace, alert 등 DB서버와 관련된 모든 에러 정보를 한 군데서 관리하도록 하는 저장소
ADR의 위치 : diagnostic_dest

/u01/app/oracle/diag/rdbms/DB명/Instance명/trace 텍스트 파일
/u01/app/oracle/diag/rdbms/DB명/Instance명/alert XML파일

SYS@orcl2> show parameter diagnostic_dest
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest                      string      /u01/app/oracle

[orcl2:~]$ /u01/app/oracle/diag/rdbms/orcl2/orcl2/trace
-- alert와 trace file들이 존재하는 위치

[orcl2:trace]$ ls *.log
alert_orcl2.log

[orcl2:trace]$ rm
[orcl2:trace]$ ls
<삭제됨>

SYS@orcl2> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

[orcl2:trace]$ ls
alert_orcl2.log       orcl2_q000_12777.trm  orcl2_vktm_12707.trm
orcl2_q000_12777.trc  orcl2_vktm_12707.trc
-- 다시 생성된 것을 확인 할 수 있다.

adrci

command line interface

[orcl2:~]$ adrci
ADRCI: Release 11.2.0.1.0 - Production on Mon Mar 25 15:54:52 2019
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
ADR base = "/u01/app/oracle"
adrci> help
HELP [topic]
   Available Topics:
        CREATE REPORT
        ECHO
        EXIT
        HELP
        HOST
        IPS
        PURGE
        RUN
        SET BASE
        SET BROWSER
        SET CONTROL
        SET ECHO
        SET EDITOR
        SET HOMES | HOME | HOMEPATH
        SET TERMOUT
        SHOW ALERT
        SHOW BASE
        SHOW CONTROL
        SHOW HM_RUN
        SHOW HOMES | HOME | HOMEPATH
        SHOW INCDIR
        SHOW INCIDENT
        SHOW PROBLEM
        SHOW REPORT
        SHOW TRACEFILE
        SPOOL
There are other commands intended to be used directly by Oracle, type
"HELP EXTENDED" to see the list

adrci> show tracefile
     diag/rdbms/orcl2/orcl2/trace/orcl2_q000_12777.trc
     diag/rdbms/orcl2/orcl2/trace/orcl2_vktm_12707.trc
     diag/rdbms/orcl2/orcl2/trace/alert_orcl2.log

adrci> show alert
-- alert 파일을 열어준다.

adrci> exit

v$diag_info(11g)

ADR 관련 정보, 특히 default trace file의 이름과 위치정보를 알 수 있다.

SYS@orcl2> select name, value from v$diag_info;
-- 위치를 알려준다.
NAME                    VALUE
----------------------- ---------------------------------------------------------------
Diag Enabled            TRUE
ADR Base                /u01/app/oracle
ADR Home                /u01/app/oracle/diag/rdbms/orcl2/orcl2
Diag Trace              /u01/app/oracle/diag/rdbms/orcl2/orcl2/trace
Diag Alert              /u01/app/oracle/diag/rdbms/orcl2/orcl2/alert
Diag Incident           /u01/app/oracle/diag/rdbms/orcl2/orcl2/incident
Diag Cdump              /u01/app/oracle/diag/rdbms/orcl2/orcl2/cdump
Health Monitor          /u01/app/oracle/diag/rdbms/orcl2/orcl2/hm
Default Trace File      /u01/app/oracle/diag/rdbms/orcl2/orcl2/trace/orcl2_ora_13632.trc
Active Problem Count    0
Active Incident Count   0

SYS@orcl2> alter session set sql_trace = true;
Session altered.
-- 여기서부터 기록을 시작

SYS@orcl2> alter session set sql_trace = false;
Session altered.
-- 끝

SYS@orcl2> select name, value from v$diag_info;
NAME                    VALUE
----------------------- ---------------------------------------------------------------
Diag Enabled            TRUE
ADR Base                /u01/app/oracle
ADR Home                /u01/app/oracle/diag/rdbms/orcl2/orcl2
Diag Trace              /u01/app/oracle/diag/rdbms/orcl2/orcl2/trace
Diag Alert              /u01/app/oracle/diag/rdbms/orcl2/orcl2/alert
Diag Incident           /u01/app/oracle/diag/rdbms/orcl2/orcl2/incident
Diag Cdump              /u01/app/oracle/diag/rdbms/orcl2/orcl2/cdump
Health Monitor          /u01/app/oracle/diag/rdbms/orcl2/orcl2/hm
Default Trace File      /u01/app/oracle/diag/rdbms/orcl2/orcl2/trace/orcl2_ora_13632.trc
Active Problem Count    0
Active Incident Count   0

SYS@orcl2> !ls /u01/app/oracle/diag/rdbms/orcl2/orcl2/trace/orcl2_ora_13632.trc
/u01/app/oracle/diag/rdbms/orcl2/orcl2/trace/orcl2_ora_13632.trc

V$ views ( Dynamic Performance Views , Fixed table )

V$ : virtual 이라는 의미(가상), view

Base Table이 존재(V_$table)

V$FIXED_TABLE 을 query 하여 모든 view를 확인
Instance와 관련된 V$views( 주로 서버 성능 튜닝 할때 주로 쓰인다 )

v$instance
v$sga, v$sgastat
v$sql : shared_pool에 저장되어있는 sql문을 확인
v$session
v$lock
v$process
v$transaction
v$sysstat

Control file과 관된된 V$views( 주로 백업 & 복구 할때 쓰인다. )

v$database
v$datafile
v$logfile, v$log

v$controlfile

Password file

v$pwfile_user

Data Dictionary

데이터베이스 안의 모든 관리해야할 정보가 기록된 테이블

Meta Data = Data Dictionary

<<<<복습>>>>

인스턴스 startup 단계중 nomount 단계에서 수행해야 하는 일은?

DB를 생성

control file 체크, 리두로그그룹 생성 .....

Control file을 재생성

Control file이란 DB의 Physical(물리적인) 구조 정보가 있다.
데이터베이스의 동기화 정보가 있다.
물리적인 구조의 사이즈를 변경하기 위해서는 Control file을 재생성 해야한다.

v$instance, v$sga, v$sgastat, v$process

mount 단계에서 수행해야 하는 일은?

데이터베이스의 log_mode(Control file에 있다)의 변경(NOARCHIVELOG <-> ARCHIVELOG)

SYS@orcl2> select log_mode from v$database;
LOG_MODE
------------
NOARCHIVELOG

Datafile의 이름을 변경(= Rename file)

control file내의 정보 중 경로를 포함한 이름을 변경하겠다는 뜻이다.
ex) /d1/a.dbf => /d5/a.dbf

바꾸는 이유는 디스크 헤더가 깨져 복구 작업을 해야하는 경우 store 공간을 설정해주는데 control file에 경로를 변경해야 하기 때문이다.

Recovery(=Offline recovery)

DB가 오픈이 되지 않은 상태에서의 recovery

DB open시에 제한 모드로 오픈하면 누가 작업을 할 수 있나?

RESTRICTED SESSION 권한이 있는 사용자만 DB access

read only 로 DB를 오픈하면 DB에 모든 write 작업이 불가하다.(O,X)

Read Only 로 DB를 open

select * from hr.employees; => 성공
update => 실패
create => 실패
대량의 sort로 Disk sort가 일어나는 것은 OK(temp file에 쓰는 것은 OK)

그러므로X

shutdown transactional의 특징은?

shutdown transactional의 실습 시나리오를 생각해서 실습해 보세요

현재 접속한 유저의 현재 TX(Transaction)이 끝날 때까지 기다렸다가 체크포인트를 일으키면서 DB종료

[orcl2:~]$ cat /u01/app/oracle/diag/rdbms/orcl2/orcl2/trace/alert_orcl2.log
Mon Mar 25 15:50:35 2019
Shutting down instance (immediate)
Stopping background process SMCO
Shutting down instance: further logons disabled
Stopping background process QMNC
Mon Mar 25 15:50:36 2019
Stopping background process CJQ0
Stopping background process MMNL
......

alert file에 기록되는 내용은?

(alert file을 cat으로 확인해 보세요)

alert log file : alert_<SID>.log

Append 된다 => file 관리가 필요( 주기적으로 지우거나, 백업)

DB 생성, startup, shutdown 언제 했는디, 어느 옵션을 걸었는지
DB 구조를 변경한 명령어들

테이블스페이스 추가 , 삭제
redo log file 추가 삭제

기본값이 아닌 parameter
중요한 에러

백그라운드 프로세스 에러
dead lock
oracle internal error : ORA-600

trace file은 언제 생성되며, 이름과 위치를 알려면 어떤 정보를 봐야하나?

백그라운드 프로세스의 에러가 발생하면 생성

<SID>_<process_id{ex:SMON, DBWR}>_<pid>.trc

서버프로세스가 생성하는 trace file

<SID>_ora_<pid>.trc
sql trace 를 썼을때
backup control file
dead lock(유일한 에러)

어떤 상황에서 발생했는지
어떤 테이블에 어떤 행이 dead lock을 유발하는지

유저프로세스가 생성하는 trace file

dead lock

SYS@orcl2> select name, value from v$diag_info;
-- trace file의 위치를 보여준다.
NAME                    VALUE
----------------------- ---------------------------------------------------------------
Diag Enabled            TRUE
ADR Base                /u01/app/oracle
ADR Home                /u01/app/oracle/diag/rdbms/orcl2/orcl2
Diag Trace              /u01/app/oracle/diag/rdbms/orcl2/orcl2/trace
Diag Alert              /u01/app/oracle/diag/rdbms/orcl2/orcl2/alert
Diag Incident           /u01/app/oracle/diag/rdbms/orcl2/orcl2/incident
Diag Cdump              /u01/app/oracle/diag/rdbms/orcl2/orcl2/cdump
Health Monitor          /u01/app/oracle/diag/rdbms/orcl2/orcl2/hm
Default Trace File      /u01/app/oracle/diag/rdbms/orcl2/orcl2/trace/orcl2_ora_6054.trc
Active Problem Count    0
Active Incident Count   0

'DATABASE(oracleDB 11g) > DBA' 카테고리의 다른 글

[Oracle DBA]데이터 동시성 관리 (0)	2019.04.03
[Oracle DBA]유저 보안 관리 (0)	2019.04.01
[Oracle DBA]데이터베이스 저장 영역 구조 관리 (0)	2019.03.28
[Oracle DBA]Oracle 네트워크 관리 (0)	2019.03.27
[Oracle DBA]ASM Instance 관리 (0)	2019.03.26

현재글[Oracle DBA]오라클 데이터베이스 Instance 관리

잡다한 모든 것들이 있는 작업공간

linux, Oracle, DBA, admin, pl/sql, db, instance, database, WS, sql, 리눅스, Raid,

Today :
Yesterday :

승삼이의 작업공간