251126 TIL

DB 접속 시 오류

리스너가 중지되었을 때

요청한 작업을 수행하는 중 오류 발생:

IO 오류: The Network Adapter could not establish the connection

업체 코드 17002

리스너 실행

[oracle@ora19c ~]$ lsnrctl start

DB 인스턴스가 유휴 상태일 때

요청한 작업을 수행하는 중 오류 발생:

Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor


업체 코드 12505

alert log 모니터링

[oracle@ora19c ~]$ cd $ORACLE_BASE/diag/rdbms/ora19c/ora19c/trace
[oracle@ora19c trace]$ tail -F alert_ora19c.log

DB 인스턴스 실행

SYS@ora19c> startup
ORACLE instance started.

Total System Global Area  830469472 bytes
Fixed Size                  8901984 bytes
Variable Size             562036736 bytes
Database Buffers          251658240 bytes
Redo Buffers                7872512 bytes
Database mounted.
Database opened.

STARTUP 단계

1. NOMOUNT

INSTANCE 시작

• SGA(System Global Area), Background Process 생성
• SGA 공간을 할당하기 위해서는 크기를 알아야 함

(1) 초기 파라미터 파일 open

• 초기 파라미터 파일 : SGA 크기, control file, 아카이브 로그 위치 등 인스턴스를 시작할 때 필요한 파라미터 정보를 가짐
• spfile : spfile<SID>.ora
• pfile : init<SID>.ora
• SID(System Identifier) = Instance_name

# 인스턴스명 조회
SYS@ora19c> show parameter instance_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
instance_name                        string      ora19c

• 기본 위치 : $ORACLE_HOME/dbs
  • 기본 위치에서 spfile.ora를 찾아보고 없으면 init.ora를 찾음
  • 만약 초기 파라미터 파일이 둘다 없으면 오류 발생

# DB 실행 시 사용한 초기 파라미터 파일 조회
SYS@ora19c> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/19.3.0
                                                 /dbhome_1/dbs/spfileora19c.ora

# 인스턴스에 현재 적용된 초기 파라미터 조회
select * from v$parameter;

(2) 초기 파라미터 설정에 따라 SGA 할당

(3) Oracle 백그라운드 프로세스 시작

(4) alert_.log, trace file open

• 기본 위치 : $ORACLE_BASE/diag/rdbms/{db_name}/{instance_name}/trace

nomount 단계에서 오라클 작업을 수행해야 할 때

• DB 생성
• control file 재생성

2. MOUNT

control file open

• control file : 데이터베이스 정보를 가지는 파일
• 초기 파라미터 파일에 저장된 컨트롤 파일 정보를 이용해서 open 함

SYS@ora19c> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /u01/app/oracle/oradata/ORA19C
                                                 /control01.ctl, /u01/app/oracl
                                                 e/fast_recovery_area/ORA19C/co
                                                 ntrol02.ctl

mount 단계에서 오라클 작업을 수행할 때

• 데이터 파일 이관 작업
• noarchivelog mode를 archivelog mode로 변경
• rman을 이용해서 close backup 할 때

3. OPEN

data file, redo log file open

• 데이터베이스의 일관성 검사
• 필요한 경우 SMON 백그라운드 프로세스가 instance recovery 시작

-- 데이터 파일 정보
select * from v$datafile;
select * from dba_data_files;

-- 임시 파일 정보
select * from v$tempfile;
select * from dba_temp_files;

-- 로그 정보
select * from v$log;
select * from v$logfile;

STARTUP 방법

nomount 단계로 시작

# nomount 단계까지만 시작
SYS@ora19c> startup nomount
ORACLE instance started.

Total System Global Area  830469472 bytes
Fixed Size                  8901984 bytes
Variable Size             562036736 bytes
Database Buffers          251658240 bytes
Redo Buffers                7872512 bytes

# 인스턴스 상태 조회
SYS@ora19c> select status from v$instance;

STATUS
------------
STARTED # => nomount까지 시작한 상태

# mount 단계 실행
SYS@ora19c> alter database mount;

Database altered.

# 인스턴스 상태 조회
SYS@ora19c> select status from v$instance;

STATUS
------------
MOUNTED

# open 단계 실행
SYS@ora19c> alter database open;

Database altered.

# 인스턴스 이름 및 상태 조회
SYS@ora19c> select instance_name, status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
ora19c           OPEN

mount 단계로 시작

# mount 단계까지만 시작
SYS@ora19c> startup mount
ORACLE instance started.

Total System Global Area  830469472 bytes
Fixed Size                  8901984 bytes
Variable Size             562036736 bytes
Database Buffers          251658240 bytes
Redo Buffers                7872512 bytes
Database mounted.

# 인스턴스 이름 및 상태 조회
SYS@ora19c> select instance_name, status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
ora19c           MOUNTED

# open 단계 실행
SYS@ora19c> alter database open;

Database altered.

# 인스턴스 이름 및 상태 조회
SYS@ora19c> select instance_name, status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
ora19c           OPEN

open 단계로 시작

# open 단계까지 시작
SYS@ora19c> startup open
ORACLE instance started.

Total System Global Area  830469472 bytes
Fixed Size                  8901984 bytes
Variable Size             562036736 bytes
Database Buffers          251658240 bytes
Redo Buffers                7872512 bytes
Database mounted.
Database opened.

# 기본이 open까지 시작
SYS@ora19c> startup
ORACLE instance started.

Total System Global Area  830469472 bytes
Fixed Size                  8901984 bytes
Variable Size             562036736 bytes
Database Buffers          251658240 bytes
Redo Buffers                7872512 bytes
Database mounted.
Database opened.

OPEN MODE

# 데이터베이스 모드 조회
SYS@ora19c> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
ORA19C    READ WRITE

# hr 계정 비밀번호 설정 및 잠금 해제
SYS@ora19c> alter user hr identified by hr account unlock;

User altered.

# 데이터베이스가 READ WRITE 모드이기 때문에 데이터베이스 읽기, 쓰기 모두 가능
SYS@ora19c> select count(*) from hr.employees;

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

SYS@ora19c> select salary from hr.employees where employee_id = 200;

    SALARY
----------
      4400

SYS@ora19c> update hr.employees set salary = salary * 1.1 where employee_id = 200;

1 row updated.

SYS@ora19c> select salary from hr.employees where employee_id = 200;

    SALARY
----------
      4840

SYS@ora19c> rollback;

Rollback complete.

데이터베이스 읽기 모드로 변경

# 데이터베이스 종료
SYS@ora19c> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

# 읽기 모드로 데이터베이스 시작
SYS@ora19c> startup open read only
ORACLE instance started.

Total System Global Area  830469472 bytes
Fixed Size                  8901984 bytes
Variable Size             562036736 bytes
Database Buffers          251658240 bytes
Redo Buffers                7872512 bytes
Database mounted.
Database opened.

# 데이터베이스 모드 조회
SYS@ora19c> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
ORA19C    READ ONLY

# 데이터 조회 가능
SYS@ora19c> select salary from hr.employees where employee_id = 200;

    SALARY
----------
      4400

# 데이터 수정 불가 => 데이터베이스가 read only 모드이기 때문에 트랜잭션 작업 수행 불가
SYS@ora19c> update hr.employees set salary = salary * 1.1 where employee_id = 200;
update hr.employees set salary = salary * 1.1 where employee_id = 200
          *
ERROR at line 1:
ORA-16000: database or pluggable database open for read-only access

읽기 쓰기 모드로 변경

# 데이터베이스 종료
SYS@ora19c> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ora19c> startup
ORACLE instance started.

# 읽기 쓰기 모드로 데이터베이스 시작
SYS@ora19c> startup open read write
ORACLE instance started.

Total System Global Area  830469472 bytes
Fixed Size                  8901984 bytes
Variable Size             562036736 bytes
Database Buffers          251658240 bytes
Redo Buffers                7872512 bytes
Database mounted.
Database opened.

# 기본이 read write 모드
SYS@ora19c> startup
ORACLE instance started.

Total System Global Area  830469472 bytes
Fixed Size                  8901984 bytes
Variable Size             562036736 bytes
Database Buffers          251658240 bytes
Redo Buffers                7872512 bytes
Database mounted.
Database opened.

SYS@ora19c> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SHUTDOWN

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

SHUTDOWN MODE

https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/oracle-database-instance.html#GUID-CD115A39-2FAE-4B94-BB3E-59818AD42803

Database Concepts

NORMAL

• 기본 종료 모드
• 새 연결 생성 불가 ⇒ 새 세션 생성 불가
• 오라클 서버는 모든 유저가 연결을 끊을 때까지 종료하지 않고 대기
• 데이터 버퍼 캐시, 리두 로그 버퍼의 내용을 디스크에 기록
• 백그라운드 프로세스, SGA 메모리 해제
• 인스턴스 종료 전에 데이터 파일, 리두 로그 파일, 컨트롤 파일 해제
• 데이터베이스를 다시 시작할 때 instance recovery 필요 X

SYS@ora19c> shutdown normal
Database closed.
Database dismounted.
ORACLE instance shut down.

# 기본이 normal
SYS@ora19c> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.

# shutdown 중에 새로운 접속을 시도할 경우 오류 발생
ERROR:
ORA-01090: shutdown in progress - connection is not permitted
Process ID: 0
Session ID: 0 Serial number: 0

TRANSACTIONAL

• 새 연결 생성 불가 ⇒ 새 세션 생성 불가
• 접속되어 있는 유저가 새로운 트랜잭션 시작 불가
• 진행 중인 트랜잭션이 종료(commit, rollback)되면 세션 해제
• 모든 트랜잭션이 종료되면 데이터베이스 종료
• 데이터 버퍼 캐시, 리두 로그 버퍼의 내용을 디스크에 기록
• 백그라운드 프로세스, SGA 메모리 해제
• 인스턴스 종료 전에 데이터 파일, 리두 로그 파일, 컨트롤 파일 해제
• 데이터베이스를 다시 시작할 때 instance recovery 필요 X

IMMEDIATE

• 새 연결 생성 불가 ⇒ 새 세션 생성 불가
• 오라클 서버는 현재 연결 중인 유저를 자동으로 세션 종료(kill)
• 현재 DML 작업을 수행하는 세션(유저)를 kill 시키는 순간 트랜잭션은 자동 rolback
  • PMON 백그라운드 프로세스가 이 작업 수행
• 데이터 버퍼 캐시, 리두 로그 버퍼의 내용을 디스크에 기록
• 백그라운드 프로세스, SGA 메모리 해제
• 인스턴스 종료 전에 데이터 파일, 리두 로그 파일, 컨트롤 파일 해제
• 데이터베이스를 다시 시작할 때 instance recovery 필요 X

ABORT

• 비정상적인 종료
• 새 연결 생성 불가 ⇒ 새 세션 생성 불가
• normal, transactional, immediate 모드가 작동되지 않을 때 사용
• 데이터 버퍼 캐시, 리두 로그 버퍼가 디스크에 기록되지 않음
• 트랜잭션에 대해 commit 또는 commit 되지 않은 정보가 데이터 파일에 기록되지 않고 불안전하게 종료된 상태
• 데이터베이스 시작 시 instance recovery가 무조건 필요
  • SMON 백그라운드 프로세스가 instance recovery를 자동으로 수행

STARTUP FORCE

• 데이터베이스를 재시작
• shutdown abort + startup
• 함부로 사용 금지!

초기 파라미터 파일(initialization parameter file)

SYS@ora19c> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/19.3.0
                                                 /dbhome_1/dbs/spfileora19c.ora

서버 파라미터 파일(server parameter file), persistent server parmeter file

• spfile
• 일반적으로 사용되는 초기 파라미터 파일 유형
• 오라클 서버가 읽고 쓸 수 있는 이진 파일(bianry file)
• vi 편집기를 이용해서 수동으로 편집 불가
• spfile은 오라클이 실행되는 서버에 있으며, 종료 및 시작과 관계없이 계속 유지 됨
• 기본 이름은 spfile<SID>.ora
• 기본 위치 : $ORACLE_HOME/dbs

텍스트 파라미터 파일(text parameter file), static parameter file

• pfile
• vi 편집기를 사용하여 수동으로 편집
• 변경 사항은 다음 시작 시 적용됨
• 데이터베이스 시작 시점에 한 번만 access
• 기본 이름은 init<SID>.ora
• 기본 위치 : $ORACLE_HOME/dbs

파라미터 값 변경

-- 시스템 레벨에서 변경
alter system set 파라미터 = 값 scope = ;

-- 세션 레벨에서 변경
alter session set 파라미터 = 값;

• 세션 레벨에서 파라미터 값을 변경하면 해당 세션에서만 변경사항이 적용됨

변경 가능한 파라미터 조회

-- 세션 레벨에서 파라미터 값 변경 가능한 파라미터
select * from v$parameter where isses_modifiable = 'TRUE';

-- 시스템 레벨에서만 파라미터 값 변경 가능한 파라미터
select * from v$parameter where isses_modifiable = 'FALSE';

시스템 레벨에서 파라미터 값 변경

alter system set 파라미터 = 값 scope = [spfile|memory|both];

1. static parameter

• 초기 파라미터 파일에서만 변경
• 인스턴스 재시작해야만 적용됨

# static parameter 조회
select * from v$parameter where issys_modifiable = 'FALSE';

# static parameter 값 변경
alter system set 파라미터 = 값 scope = spfile;

2. dynamic parameter

• 데이터베이스 운영 중에 파라미터 값 변경 및 적용 가능

# dynamic parmeter 조회
select * from v$parameter where issys_modifiable in ('IMMEDIATE', 'DEFERRED');

# # dynamic parmeter 값 변경
alter system set 파라미터 = 값 scope = [spfile|memory|both];

scope = spfile

• 파라미터 변경 사항이 서버 파라미터 파일(spfile<SID>.ora)에 적용
• 변경한 값을 인식하려면 오라클 재시작 필요

# prcesses 파라미터 값 조회 : 300
SYS@ora19c> show parameter processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     1
db_writer_processes                  integer     1
gcs_server_processes                 integer     0
global_txn_processes                 integer     1
job_queue_processes                  integer     40
log_archive_max_processes            integer     4
processes                            integer     300

SYS@ora19c> col name format a30
SYS@ora19c> col value format a30
SYS@ora19c> select name, value, issys_modifiable from v$parameter where name = 'processes';

NAME                           VALUE                          ISSYS_MOD
------------------------------ ------------------------------ ---------
processes                      300                            FALSE

# processes 파라미터 값 변경 -> 오류 발생 
# why? scope는 기본이 both, static parameter 값을 변경할 때는 scope를 spfile로 지정해야 함
SYS@ora19c> alter system set processes = 150;
alter system set processes = 150
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

# processes 파라미터 값 변경 (scope = spfile로 지정)
SYS@ora19c> alter system set processes = 150 scope = spfile;

System altered.

# 변경한 파라미터 값이 바로 적용되지 않음 why? static parameter이기 때문
SYS@ora19c> select name, value, issys_modifiable from v$parameter where name = 'processes';

NAME                           VALUE                          ISSYS_MOD
------------------------------ ------------------------------ ---------
processes                      300                            FALSE

# 데이터베이스 종료 후 재시작
SYS@ora19c> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@ora19c> startup
ORACLE instance started.

Total System Global Area  830469472 bytes
Fixed Size                  8901984 bytes
Variable Size             562036736 bytes
Database Buffers          251658240 bytes
Redo Buffers                7872512 bytes
Database mounted.
Database opened.

# 변경한 파라미터 값 적용됨
SYS@ora19c> select name, value, issys_modifiable from v$parameter where name = 'processes';

NAME                           VALUE                          ISSYS_MOD
------------------------------ ------------------------------ ---------
processes                      150                            FALSE

scope = memory

• 변경 사항이 메모리(인스턴스)에만 적용
• 현재 인스턴스가 변경되고 변경사항이 즉시 적용됨

# open_curors 파라미터 값 조회 : 300
SYS@ora19c> col default_value format a30
SYS@ora19c> set linesize 200
SYS@ora19c> select name, value, default_value, issys_modifiable from v$parameter where name = 'open_cursors';

NAME                           VALUE                          DEFAULT_VALUE                  ISSYS_MOD
------------------------------ ------------------------------ ------------------------------ ---------
open_cursors                   300                            50                             IMMEDIATE

SYS@ora19c> show parameter open_cursors

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     300

# open_curors 파라미터 값 변경(scope = memory)
SYS@ora19c> alter system set open_cursors = 100 scope = memory;                                                      
System altered.

# 변경 사항이 바로 적용됨
SYS@ora19c> select name, value, default_value, issys_modifiable from v$parameter where name = 'open_cursors';

NAME                           VALUE                          DEFAULT_VALUE                  ISSYS_MOD
------------------------------ ------------------------------ ------------------------------ ---------
open_cursors                   100                            50                             IMMEDIATE

SYS@ora19c> show parameter open_cursors

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     100

# 데이터베이스 종료 후 재시작
SYS@ora19c> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SYS@ora19c> startup
ORACLE instance started.

Total System Global Area  830469472 bytes
Fixed Size                  8901984 bytes
Variable Size             562036736 bytes
Database Buffers          251658240 bytes
Redo Buffers                7872512 bytes
Database mounted.
Database opened.

# 원래 파라미터 값으로 돌아옴
SYS@ora19c> show parameter open_cursors

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     300

scope = both

• 파라미터 변경 값을 현재 메모리(인스턴스)에도 적용하고, 서버 파라미터 파일에도 적용
• dynamic parameter 만 사용 가능

# open_curors 파라미터 값 조회 : 300
SYS@ora19c> show parameter open_cursors

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     300

# open_curors 파라미터 값 변경(scope = both)
SYS@ora19c> alter system set open_cursors = 100 scope = both;

System altered.

# 변경 사항이 바로 적용됨
SYS@ora19c> show parameter open_cursors

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     100

# 데이터베이스 종료 후 재시작
SYS@ora19c> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SYS@ora19c> startup
ORACLE instance started.

Total System Global Area  830469472 bytes
Fixed Size                  8901984 bytes
Variable Size             562036736 bytes
Database Buffers          251658240 bytes
Redo Buffers                7872512 bytes
Database mounted.
Database opened.

# spfile에도 변경사항을 적용하기 때문에 변경한 값이 유지됨
SYS@ora19c> show parameter open_cursors

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     100

deferred 옵션

# deferred 파라미터 조회
SYS@ora19c> select * from v$parameter where issys_modifiable = 'DEFERRED';
...

SYS@ora19c> show parameter sort_area_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sort_area_size                       integer     65536

# 세션 레벨에서 변경
SYS@ora19c> alter session set sort_area_size = 1048576;

Session altered.

SYS@ora19c> show parameter sort_area_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sort_area_size                       integer     1048576

# 시스템 레벨에서 변경 -> 오류 발생
# deferred 옵션 필요
SYS@ora19c> alter system set sort_area_size = 1048576;
alter system set sort_area_size = 1048576
                                        *
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this option

# deferred 옵션 사용
SYS@ora19c> alter system set sort_area_size = 1048576 deferred;

System altered.

# 변경 사항 적용 안됨
SYS@ora19c> select name, value, default_value, issys_modifiable from v$parameter where name = 'sort_area_size';

NAME                           VALUE                          DEFAULT_VALUE                  ISSYS_MOD
------------------------------ ------------------------------ ------------------------------ ---------
sort_area_size                 65536                          65536                          DEFERRED

# 새로운 세션으로 접속 시 변경 사항 적용됨
SYS@ora19c> conn / as sysdba
Connected.
SYS@ora19c> select name, value, default_value, issys_modifiable from v$parameter where name = 'sort_area_size';

NAME                           VALUE                          DEFAULT_VALUE                  ISSYS_MOD
------------------------------ ------------------------------ ------------------------------ ---------
sort_area_size                 1048576                        65536                          DEFERRED

SQL*Plus 출력 포맷 지정

# 컬럼 사이즈 지정
SYS@ora19c> col name format a30
SYS@ora19c> col value format a30
SYS@ora19c> col default_value format a30

# 라인 사이즈 지정
SYS@ora19c> set linesize 200