✅ log file sync란?

데이터의 변경사항은 redo log buffer에 저장됩니다. 트랜잭션을 커밋할 때 Oracle은 LGWR(Log Writer) 프로세스를 통해 Redo Buffer의 변경 이력을 디스크(Redo Log File)에 기록합니다.

사용자가 COMMIT 또는 ROLLBACK을 수행하면, Server Process는 LGWR에게 flush 요청을 보냅니다. LGWR는 Redo Buffer를 Redo Log File로 기록하고, 기록이 완료되기 전까지 Server Process는 대기하게 되는데, 이 대기 이벤트가 log file sync입니다.

✅ log file parallel write란?

log file parallel write 이벤트는 오직 LGWR(Log Writer) 프로세스에서 발생하는 대기 이벤트입니다. LGWR는 Redo Buffer에 저장된 데이터를 Redo Log 파일에 기록하는 작업을 수행하는데, 이때 디스크 I/O 요청을 실행한 후 해당 작업이 완료될 때까지 기다리는 동안 log file parallel write 대기를 하게 됩니다.

즉, LGWR 내부에서 발생하는 대기 이벤트입니다. 비동기 I/O 환경에서는 멀티 로그 멤버를 병렬로, 동기식 I/O 환경에서는 순차적으로 기록합니다.

대기 이벤트

[사용자가 COMMIT 명령 실행]
↓
[Server Process가 LGWR에게 Redo 로그 기록 요청]
↓
[LGWR는 Redo Buffer → Redo Log File로 기록 시도 (I/O 작업 수행)]
↓
[기록이 완료될 때까지 LGWR는 log file parallel write 이벤트 상태에서 대기]
↓
[기록 완료 후 Server Process에 응답 → COMMIT 처리 완료]
↓
[Server Process는 log file sync 대기 상태에서 해제됨]

log file sync는 Server Process의 대기, log file parallel write는 LGWR의 대기입니다.

서버 프로세스가 커밋할 때 LGWR의 작업이 지연되면 → 결국 전체 시스템의 응답 속도가 저하됩니다.

참고 https://12bme.tistory.com/320

종종 기존 테이블에 컬럼을 추가하는 일이 발생한다. 이때 우리는 default 값 혹은 null 값 제약 조건을 주는데, 칼럼을 생성하고 제약조건을 주는 경우와 컬럼을 생성하면서 제약조건을 주는 건 완전히 다르게 진행된다.

1. 컬럼 추가시 default 값을 지정하는 경우

ALTER TABLE TEST.EMP ADD AGE NUMBER DEFAULT 0;

2. 컬럼 추가 후, default 값을 지정하는 경우

ALTER TABLE TEST.EMP ADD AGE NUMBER;
ALTER TABLE TEST.EMP MODIFY AGE DEFAULT 0;

① 과 같은 방식으로 하면 전에 입력하였던 칼럼들까지 모두 default가 0 인지 조회해야함으로 시간이 많이 걸려 lock이 발생하거나 타임아웃으로 실패할 수도 있다. (Alter ddl 명령어)

②의 경우는 add 칼럼 후 modify 하는 방식으로 default, not null 제약조건 넣으면 기존 레코드에 대한 값은 변경되지 않고, 앞으로의 부분만 제약됨으로 부하가 덜하다.

이럴때는 개발자에게 의도를 물어봐야 한다. (전에 데이터도 default 0 으로 바꾸길 바라냐, 아니면 현재 데이터부터 default 0이면 되는것인가).

[결론] 테이블 컬럼 추가를 할 때 default값이나 null 값 부여할때 주의하자. 트랜잭션이 많이 발생하는 테이블에 컬럼을 추가할때 default 제약조건을 함께 줄 경우, 새로 추가된 컬럼에 default 값 설정하느라 테이블이 상당 시간동안 접근 불가능한 상태가 될 수도 있다.

내가 만든 프로시저 안에 테이블이나 함수 같은거 검색하고 싶은데 그게 될까?

%like% 를 이용하면 될 것 같은데 해.. 해보겠습니다!

프로시저를 사용할 때 다른 프로시저를 호출하는 등 연관이 많고 사용할수록 목록도 늘어나기 때문에 어느 프로시저에 어떤 테이블이 사용되는지, 어떤 함수를 고쳐야하는데 이 함수를 사용하고 있는 프로시저들을 나열해야 할 때가 많을 것이다.

이럴때 DBA_SOURCE를 사용하면 된다.

일단 이런 내용을 검색할 때 자주 사용하는 data dictrionary를 살펴보자

DBA_SOURCE : 프로시저,함수의 내용과 type 정보가 있음 DBA_OBJECTS : DB에 존재하는 모든 오브젝트의 목록 정보가 있음 (table, view, procedure, function...) DBA_PROCEDURES : DB에 존재하는 프로시저 목록 정보 있음

프로시저 조회

프로시저에 있는 내용을 검색하고 싶다면 해당 쿼리를 돌려보면 된다.

Select * 
From dba_source
Where 1=1
     And type='procedure'
     And text like '%검색하고싶은내용%'
Order by name, line;

함수 조회

함수에 있는 내용을 검색하고 싶다면 해당 쿼리를 돌려보면 된다.

Select * 
From dba_source
Where 1=1
     And type='function'
     And text like '%검색하고싶은내용%'
Order by name, line;

SQL 구문 실행 순서 상 alias는 gorup by 절 이후부터 인식한다.

실행순서 FROM - WHERE - GROUP BY - HAVING - SELECT - ORDER BY

1. FROM : 조회 테이블 확인

FROM 절에서는 테이블의 모든 데이터를 가져온다.

2. WHERE : 데이터 추출 조건 확인

FROM 절에서 읽어온 데이터 중에서 조건에 일치하는 데이터만 가져온다.

3. GROUP BY : 컬럼 그룹화

WHERE 조건에서 읽어온 데이터를 선택한 컬럼으로 그룹화하여 단일 값으로 축소한다.

4. HAVING : 그룹화 조건 확인

항상 GROUP BY 뒤에 위치하고 WHERE 조건절과 마친가지로 조건ㅇ르 줄 수 있다. 차이점은 WHERE 절은 기본적인 조건절로서 우선적으로 모든 필드를 조건에 둘 수 있지만, HAVING 절은 GROUP BY 된 이후 특정한 필드로 그룹화된 새로운 테이블에 조건을 준다.

5. SELECT : 데이터 추출

여러 조건들을 처리한 후 남은 데이터에서 어떤 열을 출력할지 선택한다.

6. ORDER BY : 데이터 순서 정렬

마지막으로 행의 순서 정렬한다.

nohup 이란

nohup은 no hang up의 약자로 “끊지마!” 를 뜻한다. nohup 명령어는 리눅스에서 프로세스를 실행한 터미널의 세션 연결이 끊기더라도 프로세스가 백그라운드에서 계속 동작되도록 해주는 명령어이다.

기본적으로 터미널에서 세션 로그아웃이 발생하면, 리눅스는 해당 터미널에서 실행한 프로세스들에게 HUP signal이 전달하여 종료시키게 되는데, 이 HUP signal 을 프로세스가 무시하도록 하는 명령어가 nohup이란 이름이다.

nohup 기본 명령어 사용법

add_datafile.sh 쉘파일을 nohup을 이용하여 돌린다고 전제할 것 이다.

nohup 파일 권한 설정

우선, nohup명령어를 사용하려면 파일의 권한이 755(rxwrx-rx-) 이상이여야 한다.

chmod 755 add_datafile.sh

nohup 파일 실행

nohup [실행하고자 하는 파일 이름] &

nohup .out 파일 생성하지 않는 명령어 nohup [실행하고자 하는 파일 이름] 1>dev/null 2>&1 &

nohup ./add_datafile.sh &

nohup 실행 확인

nohup 실행 파일을 확인하고 싶다면

ps -ef | grep add_datafile.sh

nohup 종료

nohup 명령어로 실행을 시키면, 프로세스는 대기 상태가 되고 Ctrl+C 누르면 종료된다. 그리고 해당 스크립트 프로그램의 표준 출력이 nohup을 실행시킨 경로에 nohup.out 파일이 출력된다. 터미널이 종료되어도 표준 출력은 nohup.out 파일에 계속해서 기록되기 때문에 프로세스의 상태 확인 가능하다. 하지만 필요 이상의 로그가 nohup.out 파일에 찍히면 디스크 공간 낭비하기 때문에 꼭 필요한 로그만 출력하거나 nohup.out 파일 생성하지 않는 것이 좋다.

nohup 실행 종료하기

kill -9 [pid번호]

DB 모니터링 하는 와중에 해당 스크립트를 돌리다가 control file 백업이 실패했다는 것을 알게 되었다. 참고로 아래 스크립트는 PDB가 아닌 CDB 에서 돌려야 한다.

select RECID,
           STAMP,
           PARENT_STAMP,
           SESSION_RECID,
           SESSION_STAMP,
           OPERATION,
           OBJECT_TYPE,
           OUTPUT_DEVICE_TYPE,
           STATUS,
           START_TIME,
           END_TIME
from v$rman_status
Where START_TIME >= trunc(sysdate) - 15
Order by START_TIME desc;

다음과 같은 에러가 뜬다.

ORA-00245: control file backup failed; in Oracle RAC, target might not be on shared storage

Oracle에 문의를 해보니까

I can see your snapshot controlfile is configured on local FS . Please confirgure it on shared ASm .

이라는 oracle support 답변이 왔다.

이 오류가 발생 한 경우 oracle RAC를 사용 중이므로 snapshot controlfile name 을 로컬 Disk 또는 스토리지가 아닌 Shared Storage 또는 Shared Location으로 설정해야 합니다.

## RMAN 접속
$ rman target /

## 현재까지 모든 설정 내역 확인
RMAN> show all;

show all 이라는 명령어를 치면 지금까지의 설정값들이 다 출력될 것 이다. 여기서 봐야할 것은 CONFIGURE SNAPSHOT CONTROLFILE NAME TO라고 써져있는 (아마 맨 아래) 문장이다.

현재 그 부분의 저장 경로가

RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/19.0.0.0/dbhome_1/dbs/snapdf_HNM1.f'; 

으로 되어 있을 것이다. 이것은 file system에 저장된 것으로 RAC를 사용하고 있다면 ASM 또는 shared 되는 다른 곳으로 저장을 해야한다. 나는 +RECO 쪽으로 저장 장소를 옮겼다.

## 설정값 default 로 되돌리기 
RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME CLEAR;
RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+RECO/<DB unique name>/CONTROLFILE/SNAPCF_SPRFPROD.F';

RMAN 백업 설정을 하고 나서 DB 재기동 할 필요는 없다. 이러고 하루 지나서 증분 백업이 성공하는 것을 볼 수 있다.

대량의 DELETE

월 단위 혹은 특정 기간 단위의 데이터를 주기적으로 삭제하는 업무는 어디든 항상 존재한다. 대량의 delete 작업은 table full scan이나 index scan 등과 같은 해당 테이블을 access 하는 방법에 의한 비효율보다는 필연적으로 발생할 수 밖에 없는 redo, undo 데이터 생성과 더불어 해당 테이블에 인덱스가 존재한다면 인덱스의 개수에 따라 추가적인 overhead가 발생하는 것이 성능 저하의 주된 이유이다.

인덱스에서 발생하는 추가적인 overhead란, 삭제 대상이 되는 데이터를 인덱스에서도 삭제해야하는 것과 동시에, 이 과정 역시 redo, undo 데이터가 생성됨을 의미한다.

개선 방법

1. partition table 구성

주기적으로 삭제해야 하는 기간이 정해져 있다면, 해당 칼럼을 key로 하는 range partition 을 구성하고, 삭제 대상이 되는 기간에 해당하는 partition table을

ALTER TABLE partition_test DROP PARTITION partition_test1;  

명령어를 통해 DML이 아닌 DDL로 작업을 대체할 수 있다.

DDL로의 delete 작업의 대체는, redo 데이터가 생성되지 않고 단순히 해당 partition을 drop 하는 작업만 수행하므로 속도의 차이는 비교할 수 없을 만큼 크고, 많은 양의 redo / undo 데이터가 발생되지 않음으로 down time 최소화에도 큰 기여를 할 수 있다.

대량의 INSERT

Insert는 delete 작업에 비해 성능 개선 여지가 많은 편이다. 데이터 입력 대상 테이블의 속성변경(nologging)과 힌트 (/+append/) 만으로 direct path insert를 유도하며 redo 발생을 억제하는 방법이 있기 때문이다.

1. Insert 쿼리에 /*+append*/ 힌트를 추가
2. 테이블에 nologging 추가(alter table xxx nologging; => 리두 로그에 쌓이지 않음

위의 두 경우에는 /*+append*/ 가 의미가 없는 듯 하다.

1. NOARCHIVELOG 모드 데이터베이스가 사용되고 있는 경우
2. NOLOGGING으로 표시된 테이블을 대상으로 작업하고 있는 경우

대량의 INSERT의 제일 큰 문제점이 있다면 그건 인덱스의 존재다.

대량의 insert 작업 시 데이터가 테이블에 엽력되는 작업보다도 인덱스의 개수와 인덱스의 성격에 따라 성능이 좌우된다고 해도 과언이 아니다.

대량의 데이터를 입력할 때, 해당 테이블의 인덱스가 많으면 성능 저하 및 속도가 느려지는 것을 알 수 있다.

array processing

대량의 DML 작업은 데이터를 load 하거나 update 할 때 일반적으로 LOOP 문을 사용하여 건건이 처리하는 경우가 많다. 이 방법은 매 LOOP 마다 1회씩 DML 작업이 수행되어 그만큼 DBMS CALL이 발생하기 때문에 성능상 불리하다. 하지만 array processing을 이용한 BULK SQL을 사용하면 LOOP 없이 단 한번의 SQL 수행만으로 처리가 가능하다. 즉, 대량의 DML을 단 한번에 처리할 수 있으므로 DBMS CALL을 감소시켜 loop 로 처리되던 방법에 비해 큰 성능개선 효과를 볼 수 있다.

**** ATTENTION: **** The controlfile header block returned by the OS has a sequence number that is too old. The controlfile might be corrupted. PLEASE DO NOT ATTEMPT TO START UP THE INSTANCE without following the steps below. RE-STARTING THE INSTANCE CAN CAUSE SERIOUS DAMAGE TO THE DATABASE, if the controlfile is truly corrupted. In order to re-start the instance safely, Please do the following: (1) Save all copies of the controlfile for later analysis and contact your OS vendor and Oracle support. (2) Mount the instance and issue: ALTER DATABASE BACKUP CONTROLFILE TO TRACE' (3) Unmount the instance. (4) Use the script in the trace file to RE-CREATE THE CONTROLFILE and open the database. **** -- ****

2. pfile로 DB를 기동하기 위해 pfile 생성

SQL> create pfile='/oracle/app/oracle/product/19.12.0/dbs/initRECO.ora' from spfile;

3. Controlfile 을 trace 파일로 백업하기 위해 pfile로 DB mount 상태로 재기동

SQL> shut immediate;
SQL> startup mount pfile='/oracle/app/oracle/product/19.12.0/dbs/initRECO.ora' 

4. Controlfile 재성성을 위해 trace 파일로 백업

SQL> alter database backup controlfile to trace;

5. Controlfile 백업한 trace 파일에서 controlfile 재생성 부분 추출

SQL> 
create controlfile reuse database "DBDB" noresetlogs archivelog
    maxlogfiles 192
    maxlogmembers 3
    maxdatafiles 1024
    maxInstances 32
    maxLoghistory 292
LOGFILE
GROUP 1 (
    '+DATA/DBDB/ONLINELOG/group_1.290.102934',
    '+DATA/DBDB/ONLINELOG/group_1.257.'656753'
) SIZE 2000M BLOCKSIZE 512,
GROUP 2 (
    '+DATA/DBDB/ONLINELOG/group_2.291.1029453',
    '+DATA/DBDB/ONLINELOG/group_2.258.'656723'
) SIZE 2000M BLOCKSIZE 512
-- standby logfile
DATAFILE
    '+DATA/DBDB/DATAFILE/system.298.24891235',
    '+DATA/DBDB/DATAFILE/sysaux.298.24891235',
    '+DATA/DBDB/DATAFILE/undotbs1.298.24891235',
    '+DATA/DBDB/DATAFILE/undotbs2.298.24891235',
    '+DATA/DBDB/DATAFILE/users.298.24891235',
CHARACTER SET AL32UTF8
;

6. Trace 파일로 백업한 controlfile 을 적용하기 위해 nomount 상태로 재기동

SQL> shut immediate
SQL> startup nomount pfile='/oracle/app/oracle/product/19.12.0/dbs/initRECO.ora';

7. Controlfile 추출한 내용 적용 및 DB open

-- create database 문 실행
SQL> 
create controlfile reuse database "DBDB" noresetlogs archivelog
    maxlogfiles 192
    maxlogmembers 3
    maxdatafiles 1024
    maxInstances 32
    maxLoghistory 292
LOGFILE
GROUP 1 (
    '+DATA/DBDB/ONLINELOG/group_1.290.102934',
    '+DATA/DBDB/ONLINELOG/group_1.257.'656753'
) SIZE 2000M BLOCKSIZE 512,
GROUP 2 (
    '+DATA/DBDB/ONLINELOG/group_2.291.1029453',
    '+DATA/DBDB/ONLINELOG/group_2.258.'656723'
) SIZE 2000M BLOCKSIZE 512
-- standby logfile
DATAFILE
    '+DATA/DBDB/DATAFILE/system.298.24891235',
    '+DATA/DBDB/DATAFILE/sysaux.298.24891235',
    '+DATA/DBDB/DATAFILE/undotbs1.298.24891235',
    '+DATA/DBDB/DATAFILE/undotbs2.298.24891235',
    '+DATA/DBDB/DATAFILE/users.298.24891235',
CHARACTER SET AL32UTF8
;

SQL> recover database;
SQL> alter database open;

8. Temp tablespace add

SQL> alter tablespace temp add tempfile '+DATA/DBDB/TEMPFILE/temp.291.12930124' REUSE;

끝.

요즘은 ORA-00600 같은 에러들은 애초에 SR에 등록할때 TFA로 수집된 로그 정보도 함께 등록하라고 되어있다.

TFA(trace file analyzer)란 oracle support에서 데이터를 수집하기 TFA 수집기는 이벤트 시간을 기준으로 관련 정보만 수집하므로 수집된 데이터의 크기가 훨씬 작다. TFA collector는 모든 CRS 로그 파일, ASM trace 파일, 데이터베이스 trace 파일, OSWatcher 출력값 및 CHM(cluster health monitor) 출력값을 수집한다.

TFA를 뽑아내는 명령어는 다음과 같다.

$TFA_HOME/bin/tfactl diagcollect -srdc dbracperf

오라클 support 에서 서비스 지연이 있다고 SR을 올렸을 때 요청한 내용을 공유한다.

Please follow below steps and upload the logs. 

Auto Collection Using TFA 
------------------------------- 
1) As the RDBMS/DB homeowner, run the TFA collection using the following single command (This requires TFA version 21.1.0 and above): 

$TFA_HOME/bin/tfactl diagcollect -srdc dbracperf 

The above command will prompt below details: 

Enter the Database Name [Required for this SRDC] : 
Do you have a performance issue now [Y|y|N|n] [Y]: 
How many hours ago did the issue appear? [<RETURN>=1h] : 
Enter start time when the performance was good [YYYY-MM-DD HH24:MI:SS] : 
Enter stop time when the performance was good [YYYY-MM-DD HH24:MI:SS] : 

2) Incase, if the TFA is not installed or the installed TFA version is lower than 21.1.0: 

Download and install the latest version of TFA using the AHF installer: Download AHF from below document 

Autonomous Health Framework (AHF) - Including TFA and ORAchk/EXAchk ( Doc ID 2550798.1 ) 

(a) Note: To check whether it is installed or not, run Run grep TFA_HOME = /etc/init.d/init.tfa 
(b) Note: To check the current version, run tfactl version -all 
(c) Note: To check the status of TFA, run tfactl print status 

3) In case, If the above SRDC "dbracperf" command fails, then ONLY run the below two (a) , (b) & (c) SRDC commands and upload them along with global AWR reports. 

(a) As the RDBMS/DB homeowner, run the TFA collection using the following single command 

$TFA_HOME/bin/tfactl diagcollect -srdc dbperf 

(b) As the Grid Infrastructure (ASM) homeowner, run the TFA collection using the following single command: 

$TFA_HOME/bin/tfactl diagcollect -srdc dbasm 

(c) Generate and upload global AWR report - Run on any one of the node for each databases that you have performance issues 

SQL> @?/rdbms/admin/awrgrpt.sql 

4) If you have the TFA Version below 22.3 then please download racdiag.sql script from below document, this script is to be run via sqlplus, as sys user, during a session or system level hang in a RAC environment and upload the output file. 

Script to Collect RAC Diagnostic Information (racdiag.sql) ( Doc ID 135714.1 ) 


Note: 
1.The output from the above srdc command will provide the location of the TFA collection for each and every node. 
2.Do not rename or repackage the TFA collection. 
3.Before uploading the tfa logs please verify it is covering the incident date and time. 
4.Also verify whether OSwatcher logs (covering the incident timeframe) have been captured in the tfa logs, if not collect it separately and upload it. 
5.Please upload the tfa collected using the above suggested tfa command or srdc as this is for specific issues. 



(If this information has already been provided please ignore this action request). 
This SR will go through some automatic checks to: 
- verify that the required data has been attached to the SR 


Thanks, 
Oracle Support

테이블 생성 스크립트

Table_owner : SYS Table_name : PART_SUM

/* 테이블 생성 */
CREATE TABLE SYS.PART_SUM
(
NAME varchar2(10) not null,
AGE number not null,
PHONE_NUM number,
ADDR varchar2(50),
CREATED date
)
TABLESPACE TS_MEM_ETC_01;

/* PK 인덱스 생성 */
CREATE UNIQUE INDEX SYS.PART_SUM_PK ON SYS.PART_SUM
(
NAME,
AGE
)
TABLESPACE TS_MEM_ETC_IDX_01;

/* PK 제약조건 생성*/
ALTER TABLE SYS.PART_SUM ADD CONSTRAINT PART_SUM_PK PRIMARY KEY
(
NAME,
AGE
)
USING INDEX SYS.PART_SUM_PK;

/* 코멘트 생성 */
COMMENT ON TABLE SYS.PART_SUM is '파트타임_합계';
COMMENT ON COLUMN SYS.PART_SUM.NAME is '이름';
COMMENT ON COLUMN SYS.PART_SUM.AGE is '나이';
COMMENT ON COLUMN SYS.PART_SUM.PHONE_NUM is '전화번호';
COMMENT ON COLUMN SYS.PART_SUM.ADDR is '주소‘;
COMMENT ON COLUMN SYS.PART_SUM.CREATED is '생성_일시';

/* 권한 부여 */
GRANT SELECT, INSERT, DELETE, UPDATE ON SYS.PART_SUM TO CONNECT;
GRANT SELECT, INSERT, DELETE, UPDATE ON SYS.PART_SUM TO RESOURCE;

/* PUBLIC  시노님 생성*/
CREATE PUBLIC SYNONYM PART_SUM FOR SYS.PART_SUM;
CREATE OR REPLACE PUBLIC SYNONYM PART_SUM FOR SYS.PART_SUM;

위와 같은 형식으로 테이블 생성을 하면 뀼!

테이블스페이스

대학교때 SQL을 배울때는 테이블스페이스에 대한 중요성을 잘 알지 못하였다. 왜 또 다른 공간을 만들어서 데이터를 집어 넣는거지 싶었지만, 회사는 거대한 데이터를 다루기 때문에 한 공간에 모든 데이터를 집어 넣는다면 당연히 문제가 생길 것이다. 각각의 테이블을 테이블스페이스 별로 나누어서 관리함으로써 성능 향상을 가져온다. 테이블스페이스를 생성하면 정의된 용량만큼 미리 확보한 테이블스페이스가 생성되어지고, 생성된 테이블스페이스에 테이블의 데이터가 저장된다.

기본으로 있는 tablespace는 총 4개이고 나머지는 DBA가 추가로 생성하고 지울 수 있다. (영구적으로 보존되는 객체들을 저장하기 위한 용도)

• undo : ROLLBACK 대비하여 수정 이전 값을 UNDO segment에 저장한다.
• temporary : 사용자 쿼리의 요청으로 정렬작업이 필요한 경우 메모리 부담 덜어주기 위해 사용

(데이터베이스가 운영되기 위해 꼭 필요)

• system : data dictionary table 이 저장되는 공간, 일반 오브젝트 저장하지 말자
• sysaux : system tablespace 보조로 기존에 system tablespace에 있는 다양한 유틸리티 및 기능 분리하여 저장, AWR 기능 저장되어있음.

월별 파티션 스크립트

해당 스크립트는 월별 파티션 생성 스크립트이다. 내가 하는건 월별도 있고 일별도 있어서 매우 귀찮은 반복 작업이다! 아래 처럼 명령어를 하나씩 입력해야한다...

ALTER TABLE SYS.EMP ADD PARTITION PR_202401 VALUES LESS THAN ('20240201') TABLESPACE TS_EMP_01; 

이걸 2024년 파티션 생성 스크립트를 만들어봤다!

select /*+ FIRST_ROWS */
  'alter table '||TABLE_OWNER||'.'||TABLE_NAME||' add partition '||'PR_'||DT||' values less than ('''||to_char(add_months(to_date(DT,'yyyymm'),1),'yyyymm') || '01'') tablespace '||TABLESPACE_NAME||';' as query
from (
          Select DT
          From (
                     Select to_char(add_months(to_date(to_char(sysdate,'yyyy')+1||'01','yyyymm'), LEVEL -1),'yyyymm') as DT from DUAL connect by LEVEL <= 24
               )
         ), DBA_TAB_PARTITIONS P
where TABLE_OWNER = '<계정이름>'
         and PARTITION_NAME like '%'||(to_char(sysdate,'yyyymm'))||''
         and TABLE_NAME='<테이블이름>‘
order by P.TABLE_OWNER, P.TABLE_NAME, DT, PARTITION_NAME; 

이대로 하시면 2024년, 2025년 파티션 테이블 추가할 수 있는 스크립트가 생성됩니당! 만약 2024년만 하고 싶다면 level <= 12로 하시면 됩니다. 저 쿼리에서 어느정도 변경하면 입맛에 맞게 쿼리를 만드실 수 있습니다.

일별 파티션 스크립트

일별 파티션도 위와 마친가지로 하는 건데, 이건 일별이니까 만약 쿼리로 안짠다면 (절레절레) 나는 못짠다..

ALTER TABLE SYS.EMP ADD PARTITION PR_20240101 VALUES LESS THAN ('20240102') TABLESPACE TS_EMP_01; 

위와 같은 쿼리를 2024년 366개(2월 29일), 2025년 365개 만들어주면 된다!

select /*+ FIRST_ROWS */
  'alter table '||TABLE_OWNER||'.'||TABLE_NAME||' add partition '||'PR_'||DT||' values less than ('''||to_char(add_months(to_date(DT,'yyyymmdd'),1),'yyyymmdd') || ''') tablespace '||TABLESPACE_NAME||';' as query
from (
          Select TO_CHAR(DT,'yyyymmdd') DT
          From (
                     Select to_date(to_char(sysdate,'yyyy')+1||'01','yyyymm')+ LEVEL -1 as DT 
                     from DUAL connect by LEVEL <= LAST_DAY(to_date(to_char(sysdate,'yyyy')+2||'12','yyyymm')) - to_date(to_char(sysdate,'yyyy')+1||'01','yyyymm')+1
               )
          Order by DT
  ), DBA_TAB_PARTITIONS P
where TABLE_OWNER = '<계정이름>'
         and PARTITION_NAME like '%'||(to_char(sysdate,'yyyymmdd'))||''
         and TABLE_NAME='<테이블이름>‘
order by P.TABLE_OWNER, P.TABLE_NAME, DT, PARTITION_NAME; 

쿼리를 다 만들어놓고 sqlplus에서 돌리는 방법은

파일이 sql 파일이라면 
SQL> @2024.sql

파일이 sh 파일이라면 
파일이 있는 디렉토리 위치에서 
[oracle@hawkfa01 DBA]$ ./2024.sh

이런식으로 돌리면 된다.

혹시 질문이 있거나 잘못된 내용이 있다면 댓글로 달아주세요 :)

DBA가 된다면 사람들이 하는 말이 있다.

엔터를 누르기 전에 다시 한번 생각해보고 눌러라

그러므로 DBA_USERS , ALL_USERS를 통해서 이미 user가 생성되어 있는지, 권한은 어떻게 주어야하는지를 미리 확인을 해보고 생성하면 좋다.

 select * from DBA_USERS;

User 계정 생성 및 권한 부여

create user U1234 identified by "U1234" profile 프로파일_이름;
grant connect to U1234;

User 삭제 : cascade를 하면 유저 뿐 아니라 관련된 프로파일 및 role 도 삭제가 된다!

drop user U1234 cascade;

ROLL 조회

# User 권한 조회 
select * from dba_role_privs where grantee = '유저명';

계정을 생성할 때 끝에 profile 프로파일_이름 을 추가하는 것을 확인할 수 있다.

Profile

Profile 이란, oracle DB를 사용함에 따르는 암호, pw 와 리소스에 대한 제한을 주기 위해 생성하는 하나의 오브젝트로서 일단 생성해 놓은 후 오라클 사용자에게 DB 서버자원, 암호에 대한 설정을 그룹으로 만들어 놓고 사용자에게 할당한다.

# 프로파일 종류 조회
select distince profile from DBA_PROFILES;

# 각 프로파일에 정의된 설정값 확인
select * from dba_profiles order by resource_type;

# 각 user에게 할당된 profile 조회
Select username,profile from dba_users;

# 어떠한 프로파일의 자원, 암호, 설정값 확인
select * from dba_profiles where profile = '프로파일_이름'; 

가끔 유저 계정 점검을 해야할 때도 있다. 우리 회사는 3개월마다 비밀번호를 초기화하여 바꾸고 5번 이상 입력 실패하면 초기화 된다.

SELECT 'ORACLE' as "DB_TYPE",
       (SELECT HOST_NAME FROM V$INSTANCE) as "HOST",
       (SELECT INSTANCE_NAME FROM V$INSTANCE) as "SID",
       AB.USERNAME as "USERNAME",
       AB.PROFILE as "PROFILE",
       MAX(LIFE) as "패스워드변경주기",
       MAX(ATTEMPTS) as "패스워드샐패횟수"
       AB.EXPIRY_DATE as "expiry_date"
       TO_CHAR(AB.CREATED, 'YYYY-MM-DD HH24:MI:SS') as "created"
FROM (
   A.USERNAME AS "USERNAME",
   A.ACCOUNT_STATUS as "account status",
   A.EXPIRY_DATE as "expiry date",
   A.CREATED as "created",
   CASE WHEN B.RESOURCE_NAME='PASSWORD_VERIFY_FUNCTION' THEN B.LIMIT END AS "PWD",
   CASE WHEN B.RESOURCE_NAME='PASSWORD_LIFE_TIME' THEN B.LIMIT END AS "LIFE",
   CASE WHEN B.RESOURCE_NAME='FAILED_LOGIN_ATTEMPTS' THEN B.LIMIT END AS "LIFE"
FROM DBA_USERS A, DBA_PROFILES B
WHERE A.PROFILE = B.PROFILE
 AND B.RESOURCE_NAME IN ('PASSWORD_VERIFY_FUNCTION','PASSWORD_LIFE_TIME','FAILED_LOGIN_ATTEMPTS')
)AB
GROUP BY AB.USERNAME, AB.ACCOUNT_STATUS,AB.PROFILE, AB.EXPIRY_DATE, AB.CREATED
ORDER BY PROFILE, USERNAME;

user lock

USER 의 lock 상태를 검사하는 쿼리이다.

SELECT USERNAME, USER_ID, ACCOUNT_STATUS, LOCK_DATE
FROM DBA_USERS
ORDER BY USER_ID;

사용하지 않는 user는 lock을 걸을 수 있다.

# LOCK 걸기
ALTER USER [user name] ACCOUNT LOCK;

# Lock 해제
ALTER USER [user name] ACCOUNT UNLOCK;

실무에서는 안쓰는 유저를 바로 삭제하지 않고, 일단 lock을 걸어놓고 일주일 정도 지켜본 후 아무도 접속하지 않으면 그때 지우기도 한다.