Table of Contents
오라클을 설치하고 나면 정보를 확인해야 한다. 오라클은 많은 테이블, 뷰, 동적쿼리등을 지원하는데 워낙 많다보니까 다 알수는 없다. 필요한 정보를 위한 간단한 쿼리들을 소개 한다.
데이터베이스 컴포넌트들의 상태 확인
오라클 데이터베이스는 다양한 컴포넌트들로 구성되는데, 이에 대한 상태를 확인할 수 있다.
SQL> col comp_name for a40 SQL> col version for a15 SQL> col status for a10 SQL> SELECT name, comp_name, version, status FROM v$database, dba_registry; NAME COMP_NAME VERSION STATUS O19C Oracle Database Catalog Views 19.0.0.0.0 VALID O19C Oracle Database Packages and Types 19.0.0.0.0 VALID O19C Oracle Real Application Clusters 19.0.0.0.0 OPTION OFF O19C JServer JAVA Virtual Machine 19.0.0.0.0 VALID O19C Oracle XDK 19.0.0.0.0 VALID O19C Oracle Database Java Packages 19.0.0.0.0 VALID O19C OLAP Analytic Workspace 19.0.0.0.0 VALID O19C Oracle XML Database 19.0.0.0.0 VALID O19C Oracle Workspace Manager 19.0.0.0.0 VALID O19C Oracle Text 19.0.0.0.0 VALID O19C Oracle Multimedia 19.0.0.0.0 VALID O19C Spatial 19.0.0.0.0 VALID O19C Oracle OLAP API 19.0.0.0.0 VALID O19C Oracle Label Security 19.0.0.0.0 VALID NAME COMP_NAME VERSION STATUS O19C Oracle Database Vault 19.0.0.0.0 VALID 15 rows selected.
만일 INVALID 컴포넌트가 있다면 다음과 같이 해준다.
SQL> @?/rdbms/admin/utlrp.sql
데이터베이스 이름 확인
데이터베이스 이름이 뭔지를 확인할 수 있다.
SQL> SELECT dbid, name, db_unique_name, created, log_mode, open_mode, cdb, con_id FROM v$database;
DBID NAME DB_UNIQUE_NAME CREATED LOG_MODE OPEN_MODE CDB CON_ID
2857479539 O19C o19c 30-APR-21 NOARCHIVELOG READ WRITE YES 0
CDB 가 YES 면 데이터베이스가 CDB 로 생성되었다는 것을 말한다. NO 라면 non-CDB 데이터베이스다.
DB_UNIQUE_NAME 은 쉘 환경변수에도 사용되고 있어 확인해 둘 필요가 있다. 데이터베이스 상태는 READ WRITE 상태여야 정상상태로 본다.
컨테이너 확인
오라클 12c 부터 도입된 개념이 멀티테넌트 컨테이너 데이터베이스다. 어떤 컨테이너들이 있는지 확인해 볼 필요가 있다.
SQL> select con_id, name, open_mode, dbid, con_uid, guid from v$containers;
CON_ID NAME OPEN_MODE DBID CON_UID GUID
1 CDB$ROOT READ WRITE 2857479539 1 86B637B62FDF7A65E053F706E80A27CA
2 PDB$SEED READ ONLY 3121618961 3121618961 C1202C7545A136AAE0531D60A8C0E59E
3 PDB1 READ WRITE 2824454508 2824454508 C120605562113BC8E0531D60A8C0D56F
v$containers 뷰는 CDB 안에 root 와 모든 PDB 를 포함한 모든 컨테이너에 대한 정보를 제공한다. 이것으로 PDB 이름을 확인해 볼 수 있다.
모든 CDB 는 다음의 컨테이너를 포함 한다.
- 정확하기 한개의 root – root 는 오라클이 제공하는 메타데이터와 일반 유저들을 저장한다. 메타데이터는 오라클이 제공하는 PL/SQL 패키지드르이 소스코드 같은 것이다. 일반 유저는 모든 컨테이너가 알아야 하는 데이터베이스 사용자다. root 컨테이너의 이름은 CDB$ROOT 다.
- 정확히 하나의 시드(seed) PDB – 시드 PDB 는 CDB 가 새로운 PDB를 생성하는데 사용할 수 있도록 하는 시스템이 제공하는 템플릿이다. 시드 PDB 이름은 PDB$SEED 다. PDB$SEED 를 추가하거나 수정할 수 없다.
- 없거나 하나 이상의 사용자 생성 PDB – PDB는 사용자가 생성한 엔터티로 코드와 데이터를 포함한다. 예를들어, PDB는 인적자원(Human Resources) 나 판매 애플리케이션(Sales application) 과 같은 특정한 애플리케이션을 지원할 수있다. CDB 생성 시점시 PDB는 존재하지 않는다. PDB는 비지니스 요청에 따라 추가 된다.
위 그림은 멀티테넌트 컨테이너 데이터베이스 구조 이다. CDB 안에 PDB 가 존재하는 구조다. PDB 는 Pluggable Database 다.
root 컨테이너와 함께 오라클 데이터베이스는 자동으로 seed PDB(PDB$SEED) 를 생성 한다. 다음 그래프는 새로운 CDB 생성을 보여준다.
연결 상태 보기
CDB, PDB 구조에서 현재 내가 어느 곳에 연결되어 있는지 아는 건 중요 하다. 다음의 명령어로 확인해 볼 수 있다.
SQL> show con_id; CON_ID ------------------------------ 1 SQL> show con_name; CON_NAME ------------------------------ CDB$ROOT
혹은 다음과 같은 쿼리문으로 확인 가능하다.
SQL> select sys_context('USERENV','CON_NAME') CON_NAME,
sys_context('USERENV','CON_ID') CON_ID,
sys_context('USERENV','DB_NAME') DB_NAME from DUAL;
CON_NAME CON_ID DB_NAME
CDB$ROOT 1 o19c
SQL> alter session set container=pdb1;
Session altered.
SQL> select sys_context('USERENV','CON_NAME') CON_NAME,
sys_context('USERENV','CON_ID') CON_ID,
sys_context('USERENV','DB_NAME') DB_NAME from DUAL;
CON_NAME CON_ID DB_NAME
PDB1 3 PDB1
컨테이너 타입 확인하기
내가 접속한 컨테이너가 CDB 인지, PDB 인지 타입을 확인할 수 있다.
SQL> select decode(sys_context('USERENV', 'CON_NAME'),'CDB$ROOT',sys_context('USERENV', 'DB_NAME'),sys_context('USERENV', 'CON_NAME')) DB_NAME,
decode(sys_context('USERENV','CON_ID'),1,'CDB','PDB') TYPE
from DUAL;
DB_NAME TYPE
o19c CDB
SQL> alter session set container=pdb1;
Session altered.
SQL> select decode(sys_context('USERENV', 'CON_NAME'),'CDB$ROOT',sys_context('USERENV', 'DB_NAME'),sys_context('USERENV', 'CON_NAME')) DB_NAME,
decode(sys_context('USERENV','CON_ID'),1,'CDB','PDB') TYPE
from DUAL;
DB_NAME TYPE
PDB1 PDB
컨테이너 연결 세션 변경하기
일반 사용자(common user) 는 CDB, PDB 모두에 걸친 사용자임으로 연결 세션 변경을 통해서 PDB, CDB 를 교체할 수 있다.
SQL> alter session set container=pdb1; Session altered. SQL> show con_id; CON_ID ------------------------------ 3 SQL> show con_name; CON_NAME ------------------------------ PDB1 SQL> alter session set container=cdb$root; Session altered. SQL> show con_id; CON_ID ------------------------------ 1 SQL> show con_name; CON_NAME ------------------------------ CDB$ROOT
CDB 와 연결된 PDB 상태 보기
CDB 와 연결된 PDB 상태는 다음과 같이 조회할 수 있다.
SQL> COLUMN PDB_NAME FORMAT A15
SQL> SELECT PDB_ID, PDB_NAME, STATUS FROM CDB_PDBS ORDER BY PDB_ID;
PDB_ID PDB_NAME STATUS
2 PDB$SEED NORMAL
3 PDB1 NORMAL
STATUS 값을 확인해 상태를 점검할 수 있다.
PDB 의 오픈 모드(Open Mode) 보기
이 쿼리를 통해서 마지막 오픈시간을 확인해 볼 수 있다. root 컨테이너에서는 모든 PDB 에 대한 정보를 보여주며, PDB 에 있다면 오직 하나의 PDB에 대한 정보만 보여준다.
SQL> COLUMN NAME FORMAT A15 SQL> COLUMN RESTRICTED FORMAT A10 SQL> COLUMN OPEN_TIME FORMAT A30 SQL> SQL> SELECT NAME, OPEN_MODE, RESTRICTED, OPEN_TIME FROM V$PDBS; NAME OPEN_MODE RESTRICTED OPEN_TIME PDB$SEED READ ONLY NO 01-MAY-21 04.52.38.929000000 PM +09:00 PDB1 READ WRITE NO 01-MAY-21 04.52.39.609000000 PM +09:00 SQL> alter session set container = pdb1; Session altered. SQL> SELECT NAME, OPEN_MODE, RESTRICTED, OPEN_TIME FROM V$PDBS; NAME OPEN_MODE RESTRICTED OPEN_TIME PDB1 READ WRITE NO 01-MAY-21 04.52.39.609000000 PM +09:00
컨테이터 데이터 오브젝트 확인
root 에서(root CDB), 컨테이터 데이터 오브젝트는 root 나 PDB에 포함된 데이터베이스 오브젝트에(테이블 이나 사용자) 대한 정보를 볼 수 있다.
CDB_ 뷰는 컨테이너 데이터 오브젝트들인데, 여기에는 CON_ID 컬럼이 있다. 이 컬럼은 각 PDB 의 컨테이터 ID 를 나타내는 것이며 DBA_PDBS 뷰에 쿼리하면 각 컨테이너 ID 에 대한 PDB 이름을 알 수 있다. 이 두개의 뷰를 조인해서 다음과 같이 데이터 오브젝트 확인이 가능하다.
SQL> COLUMN PDB_NAME FORMAT A15
SQL> COLUMN OWNER FORMAT A15
SQL> COLUMN TABLE_NAME FORMAT A30
SQL> SELECT p.PDB_ID, p.PDB_NAME, t.OWNER, t.TABLE_NAME
FROM DBA_PDBS p, CDB_TABLES t
WHERE p.PDB_ID > 2 AND
t.OWNER IN('HR','OE') AND
p.PDB_ID = t.CON_ID
ORDER BY p.PDB_ID;
PDB_ID PDB_NAME OWNER TABLE_NAME
3 PDB1 HR REGIONS
3 PDB1 HR LOCATIONS
3 PDB1 HR DEPARTMENTS
3 PDB1 HR JOBS
3 PDB1 HR EMPLOYEES
3 PDB1 HR JOB_HISTORY
3 PDB1 HR COUNTRIES
7 rows selected.
p.PDB_ID > 2 조건을 준 이유는 root 와 seed 컨테이너를 제외하기 위함이다.
PDBS 에 있는 사용자들 보기
DBA_PDBS 와 CDB_USERS 를 조합하면 각 PDB에 사용자들을 확인해 볼 수 있다.
SQL> COLUMN PDB_NAME FORMAT A15
SQL> COLUMN USERNAME FORMAT A30
SQL> SELECT p.PDB_ID, p.PDB_NAME, u.USERNAME
FROM DBA_PDBS p, CDB_USERS u
WHERE p.PDB_ID > 2 AND
p.PDB_ID = u.CON_ID
ORDER BY p.PDB_ID;
PDB_ID PDB_NAME USERNAME
3 PDB1 SYS
3 PDB1 SYSTEM
3 PDB1 XS$NULL
3 PDB1 LBACSYS
3 PDB1 OUTLN
3 PDB1 DBSNMP
3 PDB1 APPQOSSYS
3 PDB1 DBSFWUSER
3 PDB1 GGSYS
3 PDB1 ANONYMOUS
3 PDB1 CTXSYS
3 PDB1 DVSYS
3 PDB1 DVF
3 PDB1 GSMADMIN_INTERNAL
PDB_ID PDB_NAME USERNAME
3 PDB1 MDSYS
3 PDB1 OLAPSYS
3 PDB1 XDB
3 PDB1 WMSYS
3 PDB1 GSMCATUSER
3 PDB1 MDDATA
3 PDB1 SYSBACKUP
3 PDB1 REMOTE_SCHEDULER_AGENT
3 PDB1 PDBADMIN
3 PDB1 GSMUSER
3 PDB1 SYSRAC
3 PDB1 HR
3 PDB1 OJVMSYS
3 PDB1 SI_INFORMTN_SCHEMA
PDB_ID PDB_NAME USERNAME
3 PDB1 AUDSYS
3 PDB1 DIP
3 PDB1 ORDPLUGINS
3 PDB1 SYSKM
3 PDB1 ORDDATA
3 PDB1 ORACLE_OCM
3 PDB1 SYS$UMF
3 PDB1 SYSDG
3 PDB1 ORDSYS
37 rows selected.
p.PDB_ID > 2 조건을 준 이유는 root 와 seed 컨테이너를 제외하기 위함이다.
각 PDB 의 데이터 파일 보기
DBA_PDBS 와 CDB_DATA_FILES 뷰를 조합하면 데이터 파일들을 확인할 수 있다.
SQL> COLUMN PDB_ID FORMAT 999
SQL> COLUMN PDB_NAME FORMAT A8
SQL> COLUMN FILE_ID FORMAT 9999
SQL> COLUMN TABLESPACE_NAME FORMAT A10
SQL> COLUMN FILE_NAME FORMAT A45
SQL>
SQL> SELECT p.PDB_ID, p.PDB_NAME, d.FILE_ID, d.TABLESPACE_NAME, d.FILE_NAME
FROM DBA_PDBS p, CDB_DATA_FILES d
WHERE p.PDB_ID = d.CON_ID
ORDER BY p.PDB_ID;
PDB_ID PDB_NAME FILE_ID TABLESPACE_NAME FILE_NAME
3 PDB1 9 SYSTEM /u02/oradata/O19C/pdb1/system01.dbf
3 PDB1 10 SYSAUX /u02/oradata/O19C/pdb1/sysaux01.dbf
3 PDB1 11 UNDOTBS1 /u02/oradata/O19C/pdb1/undotbs01.dbf
3 PDB1 12 USERS /u02/oradata/O19C/pdb1/users01.dbf
혹은 다음과 같이 확인해 볼 수 있다.
SQL> SELECT c.name DB_NAME, a.name TABLESPACE_NAME, b.name DATA_FILE_NAME, b.bytes/1024/1024 SIZE_MB, b.status
-- c.name DB_NAME, a.con_id, a.ts#, a.name TABLESPACE_NAME, b.name DATA_FILE_NAME, b.file#, b.blocks, b.bytes/1024/1024 SIZE_MB, b.status, b.enabled, b.creation_time
FROM v$tablespace a, v$datafile b, v$containers c
WHERE a.con_id = b.con_id
AND a.con_id = c.con_id
AND a.ts# = b.ts#
ORDER BY a.con_id, a.TS#;
DB_NAME TABLESPACE_NAME DATA_FILE_NAME SIZE_MB STATUS
CDB$ROOT SYSTEM /u02/oradata/O19C/system01.dbf 910 SYSTEM
CDB$ROOT SYSAUX /u02/oradata/O19C/sysaux01.dbf 580 ONLINE
CDB$ROOT UNDOTBS1 /u02/oradata/O19C/undotbs01.dbf 330 ONLINE
CDB$ROOT USERS /u02/oradata/O19C/users01.dbf 5 ONLINE
PDB$SEED SYSTEM /u02/oradata/O19C/pdbseed/system01.dbf 270 SYSTEM
PDB$SEED SYSAUX /u02/oradata/O19C/pdbseed/sysaux01.dbf 330 ONLINE
PDB$SEED UNDOTBS1 /u02/oradata/O19C/pdbseed/undotbs01.dbf 100 ONLINE
PDB1 SYSTEM /u02/oradata/O19C/pdb1/system01.dbf 280 SYSTEM
PDB1 SYSAUX /u02/oradata/O19C/pdb1/sysaux01.dbf 350 ONLINE
PDB1 UNDOTBS1 /u02/oradata/O19C/pdb1/undotbs01.dbf 100 ONLINE
PDB1 USERS /u02/oradata/O19C/pdb1/users01.dbf 5 ONLINE
11 rows selected.
CDB 에 임시 파일들 보기
CDB_TEMP_FILES 뷰를 활용하면 CDB 에 각 임시 파일 이름과 위치를 확인할 수 있다.
SQL> COLUMN CON_ID FORMAT 999
SQL> COLUMN FILE_ID FORMAT 9999
SQL> COLUMN TABLESPACE_NAME FORMAT A15
SQL> COLUMN FILE_NAME FORMAT A45
SQL>
SQL> SELECT CON_ID, FILE_ID, TABLESPACE_NAME, FILE_NAME
FROM CDB_TEMP_FILES
ORDER BY CON_ID;
CON_ID FILE_ID TABLESPACE_NAME FILE_NAME
3 3 TEMP /u02/oradata/O19C/pdb1/temp01.dbf
컨트롤 파일 확인
다음과 같이 컨트롤 파일들을 확인할 수 있다.
SQL> show parameter control_files; NAME TYPE VALUE ------------- ------ ---------------------------------------------------------------- control_files string /u02/oradata/O19C/control01.ctl, /u02/oradata/O19C/control02.ctl
Redo Log 파일 확인
다음과 같이 RedoLog 파일을 확인할 수 있다.
SQL> col member for a50;
SQL> SELECT a.group#, a.members, a.bytes/1024/1024 SIZE_MB, a.status, b.member
2 FROM v$log a, v$logfile b
3 WHERE a.group# = b.group#;
GROUP# MEMBERS SIZE_MB STATUS MEMBER
3 1 50 CURRENT /u02/oradata/O19C/redo03.log
2 1 50 INACTIVE /u02/oradata/O19C/redo02.log
1 1 50 INACTIVE /u02/oradata/O19C/redo01.log
PDB에 연결된 서비스 보기
CDB_SERVICES 뷰를 통해서 PDB 이름, 네트워크 이름, 컨테이너 ID 등을 확인해 볼 수 있다.
SQL> COLUMN NETWORK_NAME FORMAT A30
SQL> COLUMN PDB FORMAT A15
SQL> COLUMN CON_ID FORMAT 999
SQL>
SQL> SELECT PDB, NETWORK_NAME, CON_ID FROM CDB_SERVICES
WHERE PDB IS NOT NULL AND
CON_ID > 2
ORDER BY PDB;
PDB NETWORK_NAME CON_ID
PDB1 PDB1 3
PDB 의 히스토리 보기
CDB_PDB_HISTORY 뷰는 PDB의 생성된 시간과 여러 히스토리 정보들을 보여준다.
SQL> COLUMN DB_NAME FORMAT A10
SQL> COLUMN CON_ID FORMAT 999
SQL> COLUMN PDB_NAME FORMAT A15
SQL> COLUMN OPERATION FORMAT A16
SQL> COLUMN OP_TIMESTAMP FORMAT A10
SQL> COLUMN CLONED_FROM_PDB_NAME FORMAT A15
SQL>
SQL> SELECT DB_NAME, CON_ID, PDB_NAME, OPERATION, OP_TIMESTAMP, CLONED_FROM_PDB_NAME
FROM CDB_PDB_HISTORY
WHERE CON_ID > 2
ORDER BY CON_ID;
DB_NAME CON_ID PDB_NAME OPERATION OP_TIMESTAMP CLONED_FROM_PDB_NAME
SEEDDATA 3 PDB$SEED UNPLUG 17-APR-19
O19C 3 PDB$SEED PLUG 30-APR-21 PDB$SEED
O19C 3 PDB1 CREATE 30-APR-21 PDB$SEED