Oracle Database - SQL*Plus 실습과 Database STARTUP 단계 정리

About this offer

지난 시간까지 Oracle Database 19c Software와 ORCL Database를 생성하고 기본적인 Oracle Architecture를 살펴봤다. 이번 시간에는 Oracle Database에 접속하여 SQL*Plus의 주요 관리 명령어를 실습 하고, Oracle Architecture에서 Redo Log, Control File, Data File의 역할과 구조 를 조금 더 자세히 살펴봤다. 이후 Database가 시작될 때 어떤 File을 어떤 순서로 읽는지 확인하면서 STARTUP의 NOMOUNT → MOUNT → OPEN 단계와 각 단계에서 확인할 수 있는 상태 및 장애 범위 를 정리했다. 이번 시간의 주요 내용은 다음과 같다. SQL*Plus 접속 및 Database 상태 확인 ↓ 같은 User의 여러 Session 확인 ↓ SQL*Plus 주요 명령 / Script / SPOOL 실습 ↓ Online Redo Log / ARCHIVELOG / Control File / Data File ↓ 실습용 dcuser 생성 ↓ SHUTDOWN ↓ NOMOUNT → MOUNT → OPEN ↓ 각 단계의 Process / User 접속 / 장애 범위 확인 1. MobaXterm 접속이 안 될 때 IP 확인 실습 환경의 VMware Network는 DHCP를 사용하기 때문에 VM을 껐다 켜면 IP가 변경될 수 있다. VMware의 Linux Terminal에서: nmcli 를 실행하여 현재 IP를 확인한다. IP가 변경되었다면: MobaXterm → 기존 Session 우클릭 → Edit Session → Remote Host 수정 을 통해 새로운 IP로 변경하면 된다. MobaXterm 왼쪽에 표시되는 Session Name 은 실제 접속 IP와는 별개의 Bookmark 이름 이다. Session Name ≠ Remote Host IP 2. Oracle Database 접속 Tool 수업에서는 Oracle Database에 접속하기 위한 대표적인 Tool로 SQL*Plus 와 SQL Developer 를 소개했다. SQL*Plus Oracle Database Software가 설치되면 함께 설치되는 Command Line 기반 Tool이다. Oracle Engine 설치 ↓ SQL*Plus도 함께 설치 DBA나 Engineer가 Database를 관리할 때 많이 사용하는 Tool이다. SQL Developer Oracle에서 무료로 제공하는 GUI 기반 Tool이다. 별도로 Oracle 사이트에서 다운로드하여 사용하며, 개발자가 SQL 작성이나 Object 조회 등을 할 때 사용할 수 있다. 현업에서는 SQL Developer뿐 아니라: Orange TOAD DBeaver ... 와 같은 Tool도 많이 사용한다. 이번 수업에서는 주로 SQL*Plus 를 이용하여 Oracle을 관리했다. 3. Linux User와 Oracle Database User 구분 Oracle을 관리할 때 가장 먼저 구분해야 하는 것이 OS User와 Database User 이다. Linux OS User ├─ root ├─ oracle └─ ... Group ├─ oinstall ├─ dba └─ ... Oracle Database User ├─ SYS ├─ SYSTEM └─ 일반 Database User 즉: oracle → Linux OS User SYS / SYSTEM → Oracle Database User 이다. Oracle 설치 환경에서는 대표적으로 다음과 같은 Group을 사용한다. oinstall → Oracle Software 설치와 Inventory 관리에 사용하는 Group dba → Oracle Database의 관리자 권한과 연결되는 OSDBA Group 실습 환경에서는 oracle OS User가 dba Group에 포함되어 있고, Oracle 설치 시 해당 Group을 OSDBA Group으로 사용하도록 구성했다. 따라서 Local Server에서 oracle User는 OS Authentication을 이용하여: sqlplus / as sysdba 와 같이 SYSDBA 권한으로 접속할 수 있다. 단순화하면: Linux oracle User ↓ dba(OSDBA) Group ↓ OS Authentication ↓ SYSDBA 접속 구조이다. 왜 SYSDBA에 이러한 접속 방식이 필요한지는 뒤의 NOMOUNT / MOUNT / OPEN 단계 에서 다시 확인한다. 4. su - oracle 과 Oracle 환경변수 Oracle 관련 작업을 할 때는 oracle OS User로 전환한다. su - oracle → oracle User의 Login 환경까지 적용 su oracle → User만 변경 → Login 환경이 정상적으로 적용되지 않을 수 있음 oracle User의 Home Directory에는 다음 File이 존재한다. /home/oracle/.bash_profile 이 File에는 Oracle 관련 환경변수가 설정되어 있다. 대표적으로: ORACLE_BASE ORACLE_HOME ORACLE_SID PATH 가 있다. ORACLE_BASE Oracle 관련 관리 File과 Directory의 기준 경로이다. 이번 실습에서는: /u01/app/oracle 을 사용했다. ORACLE_HOME Oracle Software, 즉 Oracle Engine이 실제 설치된 경로 이다. /u01/app/oracle/product/19.0.0/dbhome_1 수업에서는: Oracle Engine Oracle Software Oracle Home 을 비슷한 의미로 사용했다. ORACLE_SID 현재 Server에서 작업할 Oracle Instance를 식별하는 값 이다. 이번 실습에서는: ORACLE_SID=ORCL 을 사용했다. ORCL 은 특별한 고정 이름이 아니라 실습에서 지정한 Sample 이름이다. 실제 업무 환경에서는 업무에 따라 다른 이름을 사용할 수 있다. PATH Linux에서 Command를 입력했을 때 실행 File을 검색할 Directory를 지정한다. Oracle 환경에서는 PATH에: $ORACLE_HOME/bin 이 포함되어 있기 때문에 어느 Directory에서든: sqlplus 를 실행할 수 있다. 5. SQL*Plus는 어떻게 실행되는가? su - oracle 을 하면 .bash_profile 을 통해 Oracle 환경변수를 읽게 된다. 이후: sqlplus 를 입력하면 Linux Shell은 PATH 를 검색한다. sqlplus 입력 ↓ PATH 검색 ↓ $ORACLE_HOME/bin ↓ sqlplus 실행 File 발견 ↓ SQL*Plus 실행 현재 환경을 직접 확인할 수도 있다. 예를 들어: $ command -v sqlplus /u01/app/oracle/product/19.0.0/dbhome_1/bin/sqlplus 와 같이 출력된다. sqlplus -v 는 Database에 접속하는 것이 아니라 SQL*Plus Program 자체가 정상적으로 실행되는지만 확인하는 명령 이다. SQL*Plus 실행 성공 ≠ Database 접속 성공 6. sqlplus: command not found 가 발생한다면 수업에서는 단순히 Error를 해결하는 것보다 어느 단계에서 문제가 발생했는지 추적하는 것 이 중요하다고 강조했다. 예를 들어: sqlplus 실행 시: sqlplus: command not found 가 발생한다면 다음 순서로 확인할 수 있다. su - oracle로 접속했는가? ↓ .bash_profile이 적용되었는가? ↓ ORACLE_HOME이 올바른가? ↓ PATH에 $ORACLE_HOME/bin이 존재하는가? ↓ sqlplus 실행 File이 실제로 존재하는가? ↓ 그래도 문제가 있다면 Oracle Engine 자체 확인 즉 Error가 발생했다고 바로 Oracle을 재설치하는 것이 아니라 실패한 단계부터 범위를 좁혀야 한다. 7. SYS와 SYSTEM의 인증 방식 Oracle Database에는 대표적인 관리자 User로: SYS SYSTEM 이 존재한다. 하지만 두 계정은 이번 수업에서 서로 다른 방식으로 접속했다. SYSDBA Local Server의 oracle OS User에서: sqlplus / as sysdba 를 사용했다. / 는 User Name과 Database Password를 직접 입력하지 않고 Local OS Authentication을 이용한다는 의미이다. SYSTEM SYSTEM은 Password Authentication 방식으로 접속했다. sqlplus system 또는 수업 실습에서는: sqlplus system/<password> 형태도 사용했다. SYSTEM은 관리자 계정이지만 SYSDBA와 같은 Local OS Authentication 접속을 사용한 것이 아니라 Database Password를 이용하여 인증했다. 이 차이에 대한 이유는 뒤의 Startup 실습에서 다시 확인한다. 8. Background Process로 Instance가 떠 있는지 확인 Oracle Database에 접속하기 전에 Linux에서 Oracle Background Process를 확인했다. 수업에서는 여러 Background Process 중 입력하기 편한 SMON 을 사용했다. ps -ef | grep smon Database Instance가 실행 중이라면 다음과 비슷한 Process를 확인할 수 있다. ora_smon_ORCL 하지만 여기서 중요한 점이 있다. SMON Process가 존재함 = Database가 정상적으로 OPEN됨 은 아니다. 뒤의 Startup 실습에서 확인하듯 NOMOUNT 상태에서도 SGA와 Background Process는 이미 생성된다. ( SHUTDOWN 상태에서 존재하지 않는다) 따라서 Process는 Instance가 어느 정도 올라와 있는지 빠르게 확인하는 수단 이고, Database의 정확한 상태는 별도로 확인해야 한다. 9. SQL*Plus 접속 후 Database 상태 확인 SYSDBA로 접속했다. sqlplus / as sysdba 그리고 실제 Database Instance 상태를 확인했다. SELECT instance_name, status FROM v$instance; 정상적으로 OPEN된 상태에서는: INSTANCE_NAME STATUS ---------------- ------------ ORCL OPEN 과 같이 나타난다. 즉: Linux Process 확인 → Instance Process 존재 여부 확인 V$INSTANCE.STATUS → 현재 Oracle Instance 상태 확인 으로 구분할 수 있다. 교수님은 특히 Background Process만 보고 “Database가 정상적으로 올라와 있다”고 판단하면 안 된다고 강조했다. 10. 같은 User라도 Session은 각각 생성된다 이번 시간에는 Terminal을 3개 열어 같은 Database User로 여러 Session을 연결한 뒤 V$SESSION 에서 확인하는 실습 을 진행했다. Terminal A → SYSTEM Terminal B → SYSTEM Terminal C → SYSDBA SYSTEM Password를 잊은 경우에는 SYSDBA에서 Password를 다시 지정할 수도 있다. 수업에서는 예시로: ALTER USER system IDENTIFIED BY oracle; 를 보여줬다. 이후 첫 번째와 두 번째 Terminal에서 각각 SYSTEM으로 접속했다. sqlplus system/<password> 세 번째 Terminal에서는 SYSDBA로 접속했다. sqlplus / as sysdba 이후 SYSDBA Session에서 V$SESSION 을 조회하여 SYSTEM으로 접속한 Session들을 확인했다. SELECT sid, serial#, username, status, program FROM v$session WHERE username = 'SYSTEM'; 같은 SYSTEM User로 접속했더라도 각각 다른 Session이므로 SID , SERIAL# 값이 서로 다르게 나타난다. 출력이 너무 길어 보기 어려웠기 때문에 Column Format도 변경했다. COL username FORMAT A15 COL program FORMAT A40 그리고: / 를 입력하여 SQL Buffer에 남아 있던 V$SESSION 조회 SQL을 다시 실행했다. 즉 이번 실습을 통해: Database User ≠ Session 하나의 Database User → 여러 Session 동시 생성 가능 이라는 점을 확인할 수 있었다. 11. 반복 SQL을 Script로 저장하고 @ 로 실행 DBA 업무에서는 같은 조회 SQL을 반복해서 사용하는 경우가 많다. 매번 긴 SQL을 다시 입력하는 대신 File로 저장해 두고 실행할 수 있다. 수업에서는 oracle User의 Home 아래에 Script를 관리할 Directory를 만들었다. mkdir -p /home/oracle/sql -p 옵션은 만약 상위 폴더가 없으면 같이 생성하게 하는 옵션이다. 이후 반복해서 사용할 Session 확인 SQL을: check_local_connection.sql 이라는 File로 저장했다. SQL*Plus에서는 @ 를 이용하여 SQL Script File을 실행할 수 있다. @파일경로 예를 들어: @/home/oracle/sql/check_local_connection.sql 과 같이 실행하면 Script File 안에 저장된 SQL이 순서대로 실행된다. 즉: 반복해서 사용할 SQL 작성 ↓ SQL Script File로 저장 ↓ @파일경로 ↓ Script 실행 형태로 사용할 수 있다. 긴 조회 SQL을 매번 직접 입력할 필요가 없기 때문에 반복적인 관리 작업에서 유용하다. 12. SP2-0310 원래 Script의 올바른 경로는 @/home/oracle/sql/check_local_connection.sql 이다. 만약 @/home/oracle/check_local_connection.sql 와 같이 다른 경로를 입력하면 SP2-0310: unable to open file ... Error가 발생했다. ORA- → Oracle Database / Oracle 관련 Error SP2- → SQL*Plus 자체에서 발생한 Error command not found → Linux Shell에서 명령을 찾지 못한 Error 단, 이번 Error의 원인은 OS File 경로에 있었지만 Error를 출력한 Layer는 SQL*Plus라고 구분하면 된다. 13. 주요 SQL*Plus 명령 이번 시간에는 SQL*Plus에서 자주 사용하는 명령들을 소개하고 실제로 여러 명령을 사용해봤다. 명령 약자 기능 CONNECT CONN Database 접속 변경 SHOW - SQL*Plus 환경 또는 접속 정보 확인 DESCRIBE DESC Object 구조 확인 LIST L SQL Buffer 확인 RUN R Buffer 내용을 표시한 뒤 실행 CHANGE C 문자열 치환 EDIT ED Editor에서 SQL 수정 SPOOL SPO SQL*Plus 출력 File 저장 COLUMN COL Column 출력 형식 설정 HOST ! SQL*Plus에서 OS Command 실행 CLEAR SCREEN CL SCR 화면 정리 @ @ SQL Script 실행 SET - SQL*Plus 환경 설정 EXIT - SQL*Plus 종료 14. CONN 으로 접속 User 변경 SQL*Plus를 종료하지 않고 접속 User를 변경할 수 있다. 먼저 현재 User: SHOW USER SYS였다면: USER is "SYS" SYSTEM으로 변경: CONN system/<password> 다시 확인: SHOW USER USER is "SYSTEM" 즉 매번: EXIT 한 뒤 다시 SQL Plus를 실행할 필요 없이 같은 SQL Plus 안에서 Connection을 변경할 수 있다. 15. SQL Buffer - L , / , R SQL*Plus는 최근 SQL 또는 PL/SQL Block 하나를 SQL Buffer에 보관한다. 예: SELECT instance_name, status FROM v$instance; L L Buffer에 저장되어 있는 SQL을 표시한다. 1 SELECT instance_name, status 2* FROM v$instance SQL을 실행하지는 않는다. / / 현재 SQL Buffer에 들어 있는 SQL을 바로 실행한다. R R Buffer 내용을 화면에 표시한 뒤 SQL을 실행한다. 정리하면: L → Buffer 확인 / → Buffer SQL 바로 실행 R → Buffer SQL 표시 후 실행 이다. 16. SPOOL 로 작업 내용 저장 DBA 작업에서는 어떤 명령을 수행했는지 기록을 남기는 것이 중요하다. 수업에서는 간단히: SPOOL /home/oracle/sql/a.txt 로 기록을 시작했다. 그 이후 SQL이나 Comment를 입력했다. 예: -- 세션 확인 명령 SELECT instance_name, status FROM v$instance; 기록을 종료할 때: SPOOL OFF 를 사용했다. 그리고 SQL*Plus에서 바로 Linux의 cat Command를 실행했다. ! cat /home/oracle/sql/a.txt File에 SQL과 실행 결과가 저장되어 있는 것을 확인했다. 즉: SPOOL File ↓ SQL*Plus 작업 ↓ SPOOL OFF ↓ 작업 기록 File 생성 이다. 17. SQL*Plus 출력 Format 설정 Database의 User 정보를 조회했다. SELECT username, account_status FROM dba_users; 처음에는 Column 폭이나 Page 크기 때문에 출력이 보기 불편했다. 현재 설정 확인: SHOW LINESIZE SHOW PAGESIZE 이후 출력 폭을 변경했다. SET LINESIZE 120 SET PAGESIZE 100 Column 폭도 지정했다. COL username FORMAT A25 COL account_status FORMAT A18 그리고: / 로 같은 SQL을 다시 실행하여 출력이 정리된 것을 확인했다. A25 는 문자 값을 화면에서 25자리 폭으로 표시하겠다는 의미이다. 18. ED 로 SQL Buffer 수정 앞에서 실행한: SELECT username, account_status FROM dba_users; SQL을 Editor에서 직접 수정했다. ED 를 입력하면 VI Editor가 열렸다. 기존 SELECT Column 뒤에: default_tablespace 를 추가했다. ED를 실행하면 SQL*Plus는 현재 SQL Buffer 내용을 기본 편집용 File인 afiedt.buf에 저장한 뒤 외부 Editor를 실행한다. Editor에서 수정 후 저장하고 종료하면 변경된 내용이 다시 SQL Buffer에 반영된다. 19. 행 번호를 이용한 SQL Buffer 수정 현재 Buffer 확인: L 기존 SQL이 2번 Line까지 있을 때 3번 Line을 추가했다. 3 WHERE username = 'SYSTEM' 이렇게 하면 세 번째 Line에 WHERE 절이 추가된다. 실행: / 이후 SYSTEM User만 조회되는 것을 확인했다. 20. C 로 문자열 치환 이번에는 WHERE 조건의: SYSTEM 을: SYS 로 변경했다. C 를 이용하여 특정 문자열만 바꿀 수도 있다. C/SYSTEM/SYS/ SYSTEM → SYS 로 문자열을 치환한다. 정리하면 SQL*Plus에서 SQL을 수정하는 방법은 3가지가 있다. ED → SQL 여러 줄을 제대로 편집할 때 Line 번호 입력 → 짧은 한 줄을 추가/통째로 수정할 때 C → 현재 줄에서 단어 몇 개만 고칠 때 21. DESC 로 Object 구조 확인 마지막으로 Object의 구조를 확인하는 방법을 실습했다. DESC dba_users DESC 는 실제 Data 행을 조회하는 명령이 아니라 해당 Object에: 어떤 Column이 존재하는가 Data Type은 무엇인가 NULL 제약 여부는 어떠한가 등의 구조를 확인하는 명령이다. DBA가 모든 Dictionary View의 Column을 외울 수는 없기 때문에 필요한 경우 DESC 로 구조를