카디널리티라는 용어는 두 가지 다른 의미로 사용됩니다. 데이터 모델링에서 사용할 때와 SQL 문에서 사용할 때입니다. 데이터 모델링에서 사용할 때 카디널리티는 한 테이블이 다른 테이블과 가질 수 있는 관계를 나타냅니다. 다대다, 다대일/일대다 또는 일대일의 테이블 간의 관계를 카디널리티라고 합니다. SQL에서 사용할 때는 해당 열에 대한 테이블에 나타나는 고유한 값의 수를 나타냅니다.

data modeling 에서의 cardinality 

Docker registry에 login하여 Oracle image를 검색한 후 원하는 컨테이너 이미지를 docker pull로 다운받아 사용한다.

참고로, 이 이미지는 multitenant DB(CDB, PDB)이다.

[ 수행환경 ]

- Microsoft Windows 10 Pro(10.0.19041 N/A 빌드 19041)

- WSL 2(Ubuntu 20.04.3 LTS)

- Docker Desktop version :  4.3.2

- SQL Developer 21.4.1

WSL 에 접속

윈도우 command 창이나 Window PowerShell 창에서 wsl 명령어를 수행하여 wsl에 접속한다. wsl에 접속한다는 것은 wsl 이 기동하는 Ubuntu Linux에 접속하는 것이다. 문서에서 "$" 표시는 wsl 접속된 상태를 의미한다.

PS D:\> wsl
username@computername:/mnt/d$

Oracle container 이미지 검색

dockerhub(https://hub.docker.com/)에 접속하여 검색창에 oracle19를 입력하여 oracle image를 검색한다. Oracle19.3c 버전의 image가 검색된다. 본 문서에서는 Docker image for Oracle Database 19c (19.3) Enterprise Edition 이미지인 banglamon/oracle193db 이미지를 docker pull로 다운받는다. 

docker login

Docker registry(dockerhub)에 login하는 명령어로 docker registry에 등록된 이미지를 pull하거나 push를 위해 login한다. docker login은 패스워드를 직접 입력하는 방식과 STDIN 을 사용해서 로그인하는 방식이 있는데 보안을 위해 STDIN 방식으로 로긴하는 것을 권장한다.

1) STDIN을 사용하면 shll history나 로그파일에 남는 것을 방지

아래 예제는 pass.txt 파일에서 패스워드를 읽어서 login하는 방식이다.

$ cat pass.txt | docker login -u <username> --password-stdin

2) 패스워드 직업 입력 login

$ docker login -u <username> -p <password>

docker pull

docker pull 명령어로 oracle datbase 19c 이미지를 다운로드한다.

$ docker pull banglamon/oracle193db:19.3.0-ee

docker images

다운로드한 이미지 목록을 조회한다.

docker run

다운로드한 도커 이미지파일을 이용해서 컨테이너를 실행한다.

$ docker run -d --name oracle19db \
-p 1521:1521 \
-e ORACLE_SID=MONGO \
-e ORACLE_PDB=MONGOPDB \
-e ORACLE_PWD=Oracle123 \
-v /u01/app/oracle/oradata:/opt/oracle/oradata \
banglamon/oracle193db:19.3.0-ee

-d : deatach mode를 나타내며, -d=true 는 detached mode(background mode), -d=false는 foreground mode 로 수행

       -d 옵션만 쓸 경우는 -d=true와 동일

--name : 컨테이너 이름을 지정

-p, --publish : host port를 컨테이너 port로 게시, 앞쪽 port가 host port이고 뒤쪽 port가 컨테이너 port이다.

     ( 예, -p 1621:1521 로 설정하면 호스트에 1621 TCP port로 유입되는 모든 traffic은 컨테이너의 1521 port로 전달)

-e :  컨테이너의 환경변수를 설정. -e 옵션을 사용하면 Dockerfile의 ENV 설정도 덮어쓰게 된다.

    (예, -e ORACLE_SID=MONGO 는 오라클 컨테이너 환경변수 ORACLE_SID를 MONGO로 설정)

-v : 호스트와 컨테이너 간의 볼륨(volumn) 설정을 위해서 사용.

      호스트(host) 컴퓨터의 파일 시스템의 특정 경로를 컨테이너의 파일 시스템의 특정 경로로 마운트(mount)를 해준다.

      (Window : -v d:\temp )

      (Linux : /u01/app/oracle/oradata )

      윈도우에서 위의 명령어로 수행했는데도 오류가 발생하지는 않는다.

위 예제는 oracle193db 이미지로 detached mode로 컨테이너를 실행하고

host에 1521 port로 유입되는 traffic을 컨테이너의 1521 port로 전달한다는 명령어이다. (1521은 오라클 리스너 port ) 

컨테이너 조회 - docker ps 

docker ps는 현재 실행중인 도커 컨테이너를 조회한다.

docker ps -a는 실행중인 컨테이너와 중지된 컨테이너 모두를 조회한다. 컨테이너가 생성된 것을 확인 할 수 있다.

컨테이너 로그 조회 - docker logs 

컨테이너 실행 명령어 수행 후 바로 컨테이너를 사용할 수 있는 것은 아니다. 컨테이너에 따라 컨테이너를 생성하는데 필요한 설정을 하고 컨테이너를 생성하는데 시간이 소요된다. 오라클 데이터베이스는 오라클 인스턴스와 데이터베이스에 필요한 파일들을 생성하는데 일정 시간이 소요된다. 컨테이너 생성 과정을 모니터링 하기 위해 

docker logs <container name or cotainer id> 명령어를 수행한다. 아래 화면은 DB 설치가 진행 중인 것을 보여주고 있다.

log의 마지막 줄에 "XDB initialized" 가 보이면 정상 설치된 것이다.

컨테이너 접속 - docker exec 

컨테이너 생성이 "XDB initialized" 메시지와 함께 정상적으로 완료되면,

docker exec -i -t <ontainer name or cotainer id> /bin/bash 명령어로 컨Docker 테이너에 접속할 수 있다.

$ docker exec -it oracle19db bash

• -i, --interactive=false : 표준 입력(stdin)을 활성화하며 컨테이너와 연결(attach)되어 있지 않더라도 표준 입력을 유지한다.
• -t, --tty=false : TTY 모드(pseudo-TTY)를 사용한다. Bash를 사용하려면 이 옵션을 설정해야 한다. 이 옵션을 설정하지 않으면 명령을 입력할 수는 있지만 셸이 표시되지 않는다.
• ontainer name 을 oracle19db 로 입력한다.
• bash : 컨테이너 안의 /bin/bash를 실행하여 Bash 셸에 연결한다

참고로 아래 명령어는 bash 쉘로 접속하고 /home/oracle/.bashrc을 수행한 후 oracle DB에 sqlplus /nolog로 접속한다.

이 명령어는 참고만 하고 개인적으로 수행해 본다.

<참고> docker exec -it oracle19db bash -c "source /home/oracle/.bashrc; sqlplus /nolog"

Oracle DB가 설치된 컨테이너에서 SQL 명령어 수행

$ sqlplus '/as sysdba'

SQL> select name from v$database;
NAME
---------
MONGO

SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT

SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                          READ ONLY  NO
         3 MONGOPDB                       READ WRITE NO

## PDB에 접속
SQL> alter session set container=MONGOPDB;
Session altered.

## user 생성
SQL> create user docker identified by "docker123";

## 접속, resource 권한 부여
SQL> grant connect, resource to docker;

## users 테이블스페이스 사용 권한 설정
SQL> alter user docker quota unlimited on users;

Oracle client Tool로 DB 접속하기

Oracle Client Tool(SQL Developer, DBeaver, TOAD, Orange, Golden 등)로 컨테이너 DB 접속하기

SQL Developer로 Docker DB 접속하기

SQL Developer실행 후 좌측 상단의 [+] 를 클릭한 후 새 데이터베이스 접속 메뉴를 클릭한다.

데이터베이스에 접속하기 위해 아래의 화면에 각 항목을 입력한다.

- Name : 본인이 사용하자 하는 임의의 이름을 정한다.

- 사용자이름 : PDB에 생성한 사용자계정명을 입력한다. 예시에서는 docker를 입력한다.

- 비밀번호 : PDB에 생성한 사용자계정명을 입력한다. 예시에서는 docker123을 입력한다.

- 호스트이름 : Localhost 또는 127.0.0.1 또는 본인의 PC/서버 IP를 입력한다.

- 포트 : docker run 수행 시 지정한 host port를 입력한다.

- 서비스 이름 : 반드시 SID가 아닌 서비스명을 입력한다.

[테스트] 버튼을 클릭하여 정상 접속 여부를 확인한다. 올바르게 설정했으면 좌측 하단에 "상태 : 성공" 이라는 메시지가 출력된다.

접속 테스트를 성공했으면 [접속] 버튼을 클릭하여 PDB에 접속한다.

SQL DML 수행하기

SQL Developer의 SQL 작성창에 테이블생성, 데이터 입력 등을 할 수 있다.

sqlplus 로 Docker DB 접속하기

접속하고자는 PC나 서버에 오라클 Client가 설치된 경우에는 아래와 같이 접속할 수 있다.

sqlplus 사용자/패스워드@//host:port/service_name

host 앞의 '//' 문자는 IP를 입력할 경우 생략해도 된다. url을 입력할 경우에는 사용해야 하니, 평소 사용하는 습관을 들이는 것이 좋을 듯하다. port도 오라클 DB 기본 port인 1521인 경우에는 생략해도 되나, 습관적으로 사용하는 것이 좋겠다.

주의할 것은 service_name에는 반드시 SID가 아닌 service_name을 사용해야 한다.

SID를 사용할 경우 ORA-01017 : invalid username/password:login denied 오류가 발생한다. 

$ sqlplus docker/docker123@//127.0.0.1:1521/MONGOPDB

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Dec 25 02:26:49 2021
Version 19.3.0.0.0

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

Last Successful login time: Sat Dec 25 2021 02:10:43 +00:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

관련글 : Docker 명령어

Oracle ADG Affirm VS SYNC

SYNC and ASYNC

Specifies that network I/O is to be done synchronously (SYNC) or asynchronously (ASYNC) when archival is performed using the log writer process (LGWR).

Note:

Usage Notes

DB 정상/비정상 종료에 따른 Sequence cache 작용

시퀀스 최초 생성 시에 Oracle dictionary view에서 관리하는 last_number 값은 start 값이고,

시퀀스 nextval 로 최초 채번하면 last_number 값은 start 값 + cache 값이 된다.

DB의 정상/비정상 종료 시에 따라 last_number 값이 상이하게 관리된다.

- DB 정상 종료 시

cache와 상관없이 last_number는 sequence current 값 + 1 로 관리

- DB 비정상 종료 시

sequence의 last_number값 유지

시퀀스 cache 테스트

- 시퀀스 생성

SQL> CREATE SEQUENCE SEQ_CACHE
           START WITH 1

       INCREMENT BY 1
          CACHE 100;

- 시퀀스 조회

SQL> select sequence_name, last_number from user_sequences where sequence_name='SEQ_CACHE';

SEQUENCE_NAME                  LAST_NUMBER
------------------------------ -----------
SEQ_CACHE                                1

- 시퀀스 nextval

4번 채번하면 시퀀스 current value가 4가 된다.

SQL> select seq_cache.nextval from dual;

----------
         1

SQL> select seq_cache.nextval from dual;

----------
         2

SQL> select seq_cache.nextval from dual;

----------
         3

SQL> select seq_cache.nextval from dual;

----------
         4

- 시퀀스 last_number 조회

SQL> select sequence_name, last_number from user_sequences where sequence_name='SEQ_CACHE';

SEQUENCE_NAME                  LAST_NUMBER
------------------------------ -----------
SEQ_CACHE                              101

- DB 정상 종료 후 시퀀스 조회

SQL> shutdown immediate;

SQL> startup

SQL> select sequence_name, last_number from user_sequences where sequence_name='SEQ_CACHE';

SEQUENCE_NAME                  LAST_NUMBER
------------------------------ -----------
SEQ_CACHE                                5

- DB 비정상 종료 후 시퀀스 조회

SQL> shutdown abort;

SQL> startup

SQL> select sequence_name, last_number from user_sequences where sequence_name='SEQ_CACHE';

SEQUENCE_NAME                  LAST_NUMBER
------------------------------ -----------
SEQ_CACHE                              101

* CACHE값에 때문에 RAC에서긔 각각의 node에서 채번할 경우 채번값이 상이하다.

  CDC로 동기화할 경우 동기화 대상 node는 시퀀스값을 계속 증가하여 유지한다.

Recovery Manager Architecture

About Recovery Manager (RMAN)

RMAN(Recovery Manager)은 오라클 데이터베이스 백업 및 복구를 수행할 수 있는 도구이다. 별도의 설치가 필요없는 오라클 데이터베이스 기능이다.

RMAN은 수행한 메타정보를 target database의 control file이나 recovery catalog 에 저장한다. command-line이나 EM(Enterprise Manage) GUI로 RMAN을 수행할 수 있다.

About the RMAN Environment

RMAN은 백업/복구에 필요한 여러 application으로 구성되어 있다.

Table 3-1 Components of the RMAN Environment

ORACLE ATP DB 접속

오라클 클라우드에 sign-in 한 후,

좌측 메뉴의 Autonomous Transaction Processing 를 클릭한다.

아래와 같은 화면에서 [Create Autonomous Database]를 클릭한다 

다음 화면에서 각 항목을 입력한 후 자율 데이터베이스를 생성한다.

Provide basic information for the Autonomous Database

• Choose a compartment : autonomous DB를 생성하고자 하는 compartment를 선택

• Display name : 원하는 Display name 입력(예시는 ATPDB 입력하였음)

• Database name : 원하는 Database name 입력(예시는 ATPDB 입력하였음)

• Choose a workload type : Transaction Processing 선택

• Choose a deployment type : Shared Infrastructure 선택

Create administrator credentials

• Password : 패스워드 입력(암호는 12자 이상 30자 이하, 대문자, 소문자 및 숫자가 각각 하나 이상. 암호에는 큰 따옴표 (") 또는 사용자 이름 "admin"을 사용할 수 없음)

• Confirm password : password와 동일하게 입력

• Choose a license type : License Included 선택

[Create Autonomous Database] 를 클릭한다.

프로비저닝 한 후 다음과 같이 Autonomous Database가 생성된다.

sqlselect * from DBA_HIST_SQLTEXT  where sql_id ='4b6z0rh5jrktx';
select * from  DBA_HIST_SQL_PLAN  where sql_id ='4b6z0rh5jrktx' order by plan_hash_value, id;
select * from  DBA_HIST_SQLSTAT where sql_id ='4b6z0rh5jrktx';
select * from  v$sql  where sql_id ='4b6z0rh5jrktx';

select sql_id, plan_hash_value, id, count(*) from dba_hist_sql_plan group by sql_id, plan_hash_value, id
having count(*) > 1;
WRH$_SQL_PLAN
select * from(
select a.aid, b.bid from 
(select distinct sql_id as aid from DBA_HIST_SQLTEXT) A full outer join
(select sql_id as bid from DBA_HIST_SQLSTAT ) B
on a.aid = b.bid) c
where c.bid is null;

select * from(
select a.aid, b.bid from 
(select distinct sql_id as aid from sys.WRH$_SQLTEXT) A full outer join
(select sql_id as bid from sys.WRH$_SQLSTAT ) B
on a.aid = b.bid) c
where c.bid is null;

select * from sys.WRH$_SQLTEXT  where sql_id ='6rjdxthsp2yd4';
select * from sys.WRH$_SQLSTAT  where sql_id ='6rjdxthsp2yd4';
select * from sys.WRH$_SQL_PLAN where sql_id ='6rjdxthsp2yd4';

select * 
     FROM WRM$_SNAPSHOT sn, WRH$_SQLSTAT sql
    WHERE     sn.snap_id = sql.snap_id
          AND sn.dbid = sql.dbid
          AND sn.instance_number = sql.instance_number
          AND sn.status = 0;
          
select * From dba_hist_snapshot where snap_id = 96410 order by 1 desc;
select * From sys.WRH$_SQLTEXT b where not exists(select 'x' from sys.WRH$_SQLstat a where a.sql_id = b.sql_id);

select * From dba_objects where object_name='AWR_ROOT_SQLTEXT';
select * From dict where table_name='AWR_ROOT_SQLTEXT';


select sql_id, plan_hash_value, count(*) 
from  DBA_HIST_SQLSTAT s  
where    s.parsing_schema_name = 'KALMHS'
group by sql_id, plan_hash_value order by 1
;
----------------------
-- sql_id 추출
----------------------
select sql_id, sql_text, replace(dbms_lob.substr(sql_text, 20, 1), chr(10), ' '), 
       (select command_name from DBA_HIST_SQLCOMMAND_NAME where command_type=t.command_type) type
from   DBA_HIST_SQLTEXT T
where  exists (select 'EXIST' 
               from   DBA_HIST_SQLSTAT S
               where  t.sql_id = s.sql_id
               and    s.parsing_schema_name = 'KALMHS');
;

select * from (
select t.sql_id,
       t.sql_text,
       replace(dbms_lob.substr(t.sql_text, 20, 1), chr(10), ' '),
       (select command_name from DBA_HIST_SQLCOMMAND_NAME where command_type=t.command_type) type,
       p.plan_hash_value 
from DBA_HIST_SQLTEXT T,
     (select sql_id, plan_hash_value from
         (select sql_id, plan_hash_value,
                row_number() over (partition by sql_id order by sql_id, timestamp desc) row_num
          from  DBA_HIST_SQL_PLAN
          where 1 = 1) -- 전체 SQL
      where row_num = 1) P
where t.sql_id = p.sql_id(+))
where plan_hash_value is null and type='SELECT';

select a.sql_id, b.sql_id, b.command_type from 
(select distinct sql_id from v$sql) a, DBA_HIST_SQLTEXT b
where a.sql_id = b.sql_id(+)
;-- where sql_id='3hc96wfvxp4tk';
-- 56196, 9610
select distinct sql_id from DBA_HIST_SQLTEXT;
--> 5540 

select sql_id, sql_text, sql_t, type, plan_hash_value, parsing_schema_name
from (
    select t.sql_id,
           t.sql_text,
           replace(dbms_lob.substr(t.sql_text, 20, 1), chr(10), ' ') sql_t,
           (select command_name from DBA_HIST_SQLCOMMAND_NAME where command_type=t.command_type) type,
           p.plan_hash_value,
           (select parsing_schema_name from DBA_HIST_SQLSTAT S where 1=1 and   s.sql_id = t.sql_id
                   and   s.plan_hash_value = p.plan_hash_value and rownum = 1) parsing_schema_name 
    from DBA_HIST_SQLTEXT T,
         (select sql_id, plan_hash_value from
             (select sql_id, plan_hash_value,
                    row_number() over (partition by sql_id order by sql_id, timestamp desc) row_num
              from  DBA_HIST_SQL_PLAN
              where 1 = 1) -- 전체 SQL
          where row_num = 1) P
    where t.sql_id = p.sql_id
    and   exists ( select 'X' 
                   from DBA_HIST_SQLSTAT S 
                   where s.parsing_schema_name in ('KALMHS','KALPPO','NETBACKUP','KALPPO_SSO','KALSTD')
                   and   module like '%%'
                   and   s.sql_id = t.sql_id
                   and   s.plan_hash_value = p.plan_hash_value)
)
order by 1;

select t.*,
       sql_id, replace(dbms_lob.substr(t.sql_text, 20, 1), chr(10), ' ') sql_t,
       (select command_name from DBA_HIST_SQLCOMMAND_NAME where command_type=t.command_type) type,
       parsing_schema_name
 From v$sql t
 where t.parsing_schema_name in ('KALMHS','KALPPO','NETBACKUP','KALPPO_SSO','KALSTD');

where plan_hash_value is null and type='SELECT';

select sql_id, count(*) From v$sql group by sql_id having count(*) > 1;


select * From dba_tab_columns where column_name = 'MODULE' order by table_name;

select * from v$sql where sql_id='grvaq6wc3s189';

select * From dict where table_name like '%SHARED%';
select * from V$SQL_SHARED_CURSOR where sql_id='grvaq6wc3s189';

SELECT  
       A.SQL_ID,
       A.SQL_TEXT, 
       A.COMMAND_TYPE, --> IF NEEDED
       B.PLAN_HASH_VALUE,
       B.ID,
       B.OPERATION,
       B.OPTIONS,
       B.OBJECT_OWNER,
       B.OBJECT_NAME,
       B.OBJECT_TYPE,
       B.OPTIMIZER,
       B.PARTITION_START,
       B.PARTITION_STOP,
       B.PARTITION_ID
FROM   DBA_HIST_SQLTEXT A, DBA_HIST_SQL_PLAN B
WHERE  A.DBID = B.DBID
AND    A.SQL_ID = B.SQL_ID
AND    A.SQL_ID IN('03y5xw17vncxd')
AND    EXISTS
       (SELECT 'E' 
        FROM  DBA_HIST_SQLSTAT C 
        WHERE A.DBID = C.DBID 
        AND   A.SQL_ID = C.SQL_ID 
        AND   B.SQL_ID = C.SQL_ID
        AND   B.PLAN_HASH_VALUE <> C.PLAN_HASH_VALUE
        AND   PARSING_SCHEMA_NAME  IN('KALMHS'))
ORDER BY A.DBID, A.SQL_ID, B.PLAN_HASH_VALUE, B.ID;