모두의 지식 저장고

조회 쿼리

 set linesize 400;
 select substr(a.tablespace_name,  1, 30) as tablespace_name
	    , round(sum(a.total1) /1024/1024, 1) "Total(MB)"
	    , round(sum(a.total1) /1024/1024/1024, 1) "Total(GB)"
	    , round(sum(a.total1) /1024/1024,1) -round (sum(a.sum1) /1024/1024, 1) "Used(MB)"
	    , round(sum(a.sum1) /1024/1024, 1) "Free(MB)"
	    , round( (round(sum(a.total1) /1024/1024,1) -round (sum(a.sum1) /1024/1024,1) ) /round (sum(a.total1) /1024/1024, 1) *100, 2) "Used(%)"
   from ( select tablespace_name
               , 0 total1
               , sum(bytes) sum1
               , max(bytes) MAXB
               , count(bytes) cnt
	          from dba_free_space
	         group by tablespace_name
	         union
	         select tablespace_name
	              , sum(bytes) total1
	              , 0
	              , 0
	              , 0  
	           from dba_data_files
	           group by tablespace_name) a
  group by a.tablespace_name
  order by tablespace_name;

결과

데이터 파일 사이즈 비교

오라클 파라미터에 대해 알아 보겠습니다. 

오라클 초기화 파라미터 파일이란? 

오라클은 기동시에 파라미터 파일을 읽어서 구동되어지도록 설계되었습니다. 

아래는 오라클 기동시 파라미터 파일을 읽는 순서에 대한 설명 그림입니다. 

NOMOUNT 로 부팅 하기 위해서는 SPFILE 일 찾는 다음 없음면 PFILE 을 찾게 됩니다. 

SPFILE 과 PFILE 이 모두 없다면 기동에 실패하는 동작을 나타내는 그림 입니다. 

오라클 파라미터 파일 종류?

파라미터 파일에 대해 알아보겠습니다. 

오라클은 앞서 설명 했듯이 SPFILE 과 PFILE 두종류의 파라미터 파일이 있습니다. 

PFILE ?

특징은 아래와 같습니다. 

• 정적 파라미터라고 부르며 오라클 8i 부터 사용 가능 합니다. 
• 파일 변경이 가능 하며 $ORACLE_HOME/dbs/initSID.ora 파일 입니다. 
• V$PARAMETER 뷰에서 파라미터 조회가 가능 합니다. 

SPFILE ?

• 동적 파라미터라고 부르며 오라클 9i 부터 사용 가능 합니다. 
• 파일 변경이 가능 하며 $ORACLE_HOME/dbs/spfileSID.ora 파일 입니다. 
• V$SPARAMETER 뷰에서 파라미터 조회가 가능 합니다. 

오라클 파라미터 변경 방법

아래와 같이 파라미터는 변경 가능 합니다. 

• SPFILE : ALTER SYSTEM SET [parameter name]=[values] [SCOPE=(MEMORY/SPFILE/BOTH)]
• PFILE   : ALTER SYSTEM SET [parameter name]=[values] [SCOPE=(MEMORY)]

출처: https://2factor.tistory.com/63 [IT 일기]

오라클 파라미터 파일 서칭 순서

위 그림에서 설명을 했지만 좀더 자세히 설명합니다. 

정확히는 아래와 같은 순서로 부팅시 파라미터 파일을 찾게 됩니다. 

1. spfile[SID].ora
2. spfile.ora
3. init[SID].ora
4. init.ora

오라클 파라미터 파일 생성 방법

아래처럼 create 명령을 통해 파라미터 파일을 만들수 있습니다. 

• create pfile='파일명' from spfile='파일명'
• create spfile='파일명' from pfile='파일명'

왜 사용할까요? 

위에서 언급을 했지만 spfile은 기동시 최우선 순위에 있고 파일 수정이 불가 합니다. 

추후에 파라미터 파일을 백업 용도로 사용하기 위해 PFILE로 백업을 해놓아 spfile 파일이 유실되었을 경우 pfile로 기동이 가능(startup pfile=[파라미터 파일명]) 합니다. 

그리고 spfile 은 pfile 보다 기동시 우선순위에 있기 때문에 pfile 이 있더라도 spfile 이 있을 경우 pfile 에 등록된 파라미터 값은 반영이 되지 않습니다. 

운영 중에 필수적으로 반영되어야 하는 파라미터 값이 있다면 spfile 을 pfile 로 만든 다음 pfile  을 spfile 로 만들어서 파라미터 값을 운영에 반영 할수 있습니다. 

오라클 파라미터 파일 생성 및 값 변경 실습

-- pfile 생성
create pfile='/opt/oracle/product/19c/dbhome_1/dbs/test_pfile.ora' from spfile ='/opt/oracle/product/19c/dbhome_1/dbs/spfileORCLCDB.ora'
 
-- spfile 생성
create spfile from pfile = '/opt/oracle/oradata/dbconfig/ORCLCDB/test_pfile.ora';

Oracle NLS_LANGUAGE 변경 Scripts

• 데이터 베이스 NLS_LANGUAGE 변경 방법에 대해 공유 합니다. 
• 독일어로 변경하는 방법에 대해 알아 보겠습니다. 

-- dba 접속
sqlplus sys/oracle as sysdba
 
-- 언어셋 변경 
update sys.props$ set value$='AL32UTF8' where name='NLS_CHARACTERSET';  
update sys.props$ set value$='AL16UTF16' where  name='NLS_NCHAR_CHARACTERSET';
update sys.props$ set value$='GERMAN_GERMANY.WE8ISO8859P1  ' where name='NLS_LANGUAGE';
commit;
 
- db 재기동
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
 
ALTER SYSTEM ENABLE RESTRICTED SESSION;
 
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
 
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
 
ALTER DATABASE OPEN;
 
ALTER DATABASE CHARACTER SET INTERNAL_USE WE8ISO8859P1;
 
SHUTDOWN IMMEDIATE;
 
STARTUP;

NLS_LANGUAGE 변경 진행 과정

변경된 NLS 정보 확인 방법

set linesize 400
set pagesize 100
col PARAMETER for a30
col VALUE for a100
 
-- 현재 nls 구성정보
SELECT * FROM V$NLS_PARAMETERS;
 
--설치시 nls 정보
SELECT * FROM NLS_DATABASE_PARAMETERS;
 
--인스턴스 nls 정보
SELECT * FROM NLS_INSTANCE_PARAMETERS;​

오라클 샘플 스키마 생성 방법에 대해 알아보겠습니다. 

오라클 설치 시  오라클 스키마는 생성되지 않습니다. 

테스트하기 위한 Sample Schema 생성 방법에 대해 알아보겠습니다. 

Oracle Database Sample Schemas 다운로드

먼저 오라클에서 제공하는 샘플 스키마를 다운로드 받습니다. 

https://github.com/oracle/db-sample-schemas/tree/v19c

git clone https://github.com/oracle/db-sample-schemas.git

다운로드 파일 :db-sample-schemas

sample-schemas scripts 수행 설명 (mksample.sql)

sqlplus sys/oracle@orclcdb as sysdba
 
@/opt/oracle/oradata/db-sample-schemas/mksample.sql oracle oracle hr oe pm ix sh bi users temp /opt/oracle/oradata/db-sample-schemas/logs/ ora19c

mksample.sql 에 서 입력 받는 인자 값들입니다. 

입력 변수는 볼드체로 표기했습니다. 

mksample.sql 파일 수정 

스크립트를 수행하면 샘플 유저를 생성하는 과정에서 ORA-65096: invalid common user or role name 에러가 발생합니다 .

에러 발생을 막기 위해 ALTER SESSION SET "_ORACLE_SCRIPT"=true; 적용하겠습니다. 

mksample.sql 스크립트중 CONNECT system/&&password_system@&&connect_string 구문 밑에 ALTER SESSION SET "_ORACLE_SCRIPT"=true; 를 추가합니다. 

mksample.sql 실행 

정상적으로 수행이 완료되면 log 디렉토리에 수행 스크립트 별로 로그가 남게 되고 

마지막으로 검증 결과가 mkverify_v3.log 와 같은 형태로 남게 됩니다. 

The Customer Orders(CO) schema 스크립트 수행 

mksample 에서는 co 생성 스크립트가 포함되어 있지 않습니다. 

별도 스크립트로 수행해야 테스트 샘플 스키마가 생성 되게 됩니다. 

수행 파일은  db-sample-schemas/customer_orders/co_main.sql 입니다. 

Scott 계정 생성 

오라클 12c? 이후 부터는 scott 계정이 기본 적으로 생성되지 않습니다. 

livesql 사이트에서 테이블 정보와 데이터 확인후 샘플데이터를 생성하면 됩니다.

https://livesql.oracle.com/apex/livesql/file/content_O5AEB2HE08PYEPTGCFLZU9YCV.html

Scott 계정 스크립트

create user scott identified by tiger;
grant dba to scott;
 
conn scott/tiger
 
DROP TABLE DEPT;
CREATE TABLE DEPT
       (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
	DNAME VARCHAR2(14) ,
	LOC VARCHAR2(13) ) ;
	
DROP TABLE EMP;
CREATE TABLE EMP
       (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
	ENAME VARCHAR2(10),
	JOB VARCHAR2(9),
	MGR NUMBER(4),
	HIREDATE DATE,
	SAL NUMBER(7,2),
	COMM NUMBER(7,2),
	DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);
INSERT INTO DEPT VALUES
	(10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES
	(30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES
	(40,'OPERATIONS','BOSTON');
INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES
(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES
(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES
(7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87')-85,3000,NULL,20);
INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES
(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES
(7876,'ADAMS','CLERK',7788,to_date('13-JUL-87')-51,1100,NULL,20);
INSERT INTO EMP VALUES
(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES
(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES
(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
 
DROP TABLE BONUS;
CREATE TABLE BONUS
	(
	ENAME VARCHAR2(10)	,
	JOB VARCHAR2(9)  ,
	SAL NUMBER,
	COMM NUMBER
	) ;
	
DROP TABLE SALGRADE;
CREATE TABLE SALGRADE
      ( GRADE NUMBER,
	LOSAL NUMBER,
	HISAL NUMBER );
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);
COMMIT;

샘플 스키마 ERD Diagrams 

자세한 자료는 오라클 자료를 참고하시기 바랍니다. 

https://docs.oracle.com/en/database/oracle/oracle-database/19/comsc/schema-diagrams.html#GUID-D268A4DE-BA8D-428E-B47F-80519DC6EE6E

Oralce 환경설정 방법

앞서 docker-compose 를 이용한 Oracle 19c 설치 글에서는 단순히  설치 방법에 대해서 알아보았습니다. 

이번에는 Docker 설치후 테스트를 좀더 효율적으로 진행하기 위한 방법에 대해 소개하겠습니다. 

_ORACLE_SCRIPT 파라미터 적용 

19c 에서는 테이블이나 유저를 생성하게 되면 ORA-65096 에러가 발생 합니다. 

_ORACLE_SCRIPT  파라미터를 변경함으로서 에러발생을 해결 할 수 있습니다. 

외부 장비 접속 설정 (네트워크 설정)

tnsnames.ora 설정

ORCLCDB=localhost:1521/ORCLCDB
 
ora19c= 
(DESCRIPTION = 
  (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = ORCLCDB)
  )
)
ORCLPDB1= 
(DESCRIPTION = 
  (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = ORCLPDB1)
  )
)

listener.ora 설정

LISTENER =
(DESCRIPTION_LIST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521))
  )
)
 
SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
        (GLOBAL_DBNAME = ORCLCDB)
        (SID_NAME = ORCLCDB)
    )     
    (SID_DESC =
        (GLOBAL_DBNAME = ORCLPDB1)
        (SID_NAME =ORCLPDB1)                
    )
)

리스너 재시작 

리스너 상태 확인 

sqlplus 접속 확인

MyOra  툴은 무료로 오라클 sql 작업이나 DB 자원 모니터링을 할수 있는 툴입니다. 

해당 툴을 다운로드 해서 사용하는 방법에 대해 소개하겠습니다. 

MyOra 다운로드

아래 경로에서 해당 툴 다운로드 받습니다. 

https://www.myorasql.com/download.html

현재는 9.4 버전 까지 Release 되어있습니다. (2022.03.21) 

MyOra 실행

다운로드 받은 앞출 파일을 해제 후 프로그램을 실행합니다. 

설치된 java 버전에 따라 실행 파일이 다릅니다. 

제환경은 1.8 jdk 가 설치 되어 있어 MyOra-9.4\Java1_8andBelow 디렉토리에있는 MyOra 파일을 실행 하겠습니다. 

* 주의 : 접속하로자 하는 오라클 jdbc 라이브러리가 실행 파일 경로에 위치해야 합니다. 

https://growupcoding.tistory.com/27 글 에서 설치한 오라클 jdbc 파일을 MyOra-9.4\Java1_8andBelow 로 복사하겠습니다. 

오라클 자원 사용률 모니터링 

Top Query 조회 

Instance 정보

SQL Editor

기본 SQL Editor 도 지원 합니다. 쿼리 실행 단축키는 F9 번입니다. 

1.Docker Image 다운로드 

우선 Docker 환경에서 오라클을 설치 하려면 오라클 이미지가 준비 되어있어야 겠죠? 

아래 GitHub 에서 Oracle19c 이미지를 다운로드 받습니다. 

https://github.com/oracle/

git clone https://github.com/oracle/docker-images.git

2.Oracle19c 바이너리 다운로드 

Docker 이미지를 다운로드를 받았다면 이제 실제로 설치할 오라클 바이너리를 준비해야 겠습니다. 

아래 경로에서 오라클 19c 바이너리(LINUX.ZSERIES64_193000_db_home.zip)를 다운로드 받습니다. 

https://www.oracle.com/kr/database/technologies/oracle19c-linux-downloads.html

3. 설치 

설치를 위한 바이너리가 모두 준비되었습니다. 

-rw-r--r--  1 oracle-docker oracle-docker 3059705302  2월 22 19:36 LINUX.X64_193000_db_home.zip
drwxr-xr-x 32 oracle-docker oracle-docker       4096  2월 22 15:40 docker-images

이제 본격적인 설치 작업을 진행하도록 하겠습니다. 

Oracle 19c 바이너리 Docker 디렉토리 복사 

 cp LINUX.X64_193000_db_home.zip docker-images/OracleDatabase/SingleInstance/dockerfiles/19.3.0

Docker Image build

cd docker-images/OracleDatabase/SingleInstance/dockerfiles
./buildContainerImage.sh -e -v 19.3.0

Docker 실행 

$ docker run --name oracle -p 1521:1521 -p 5500:5500 -e ORACLE_PWD=oracle -v oracle19c:/opt/oracle/
oradata oracle/database:19.3.0-ee

Oracle 접속 및 테스트 

$ docker exec -it oracle sqlplus sys/oracle@//localhost:1521/ORCLCDB as sysdba