따봉 구걸남's 블로그

1. 파라미터 파일이란 : 

   shutdown 상태에서 no mount 가 되면 SGA 등이 포함된 instance 를 메모리에 올리게 되는데 이때

 파라미터 파일을 참고해서 instance 를 올린다. 

 오라클 서버 프로세스가 사용할 인스턴스를 어떤 스펙으로 올려라 같은 레시피 같은 것.

2. 어떤것을 규정하는가 

- 인스턴스에 대한 내용(SGA, PGA, 버퍼캐쉬)

- 컨트롤 파일의 위치

- 기타 성능 및 버그 관련 work around

3. 최적화 

파라미터는 사이트마다 최적이 다르다. 

- 최적을 알고 싶다면 oracle acs 파라미터 진단 서비스  => 유료 

- 널리 쓰이는 공통적인 것은 적용

- 좋은 기능이지만 리스크가 있을만한것은 놔두고  

- 그때그때 필요한것을 추가 

- 버그 문제는 히든파라미터를 써가며 work around

- 현실 : 오라클 버전 업, 패치 적용이후 파라미터들을 최신, 최적화하여 관리해야 하지만, 한번 설정한 파라미터는 시간이 갈수록 side effect 에 대해서 책임지기 싫어서 놔둠.

- 결론 : 한방에 최적을 알고 싶지만 경험을 통해 넣고 빼고 해야함 

사이베이스 datetime -> 오라클로 이관 하기
날짜 타입의 경우 형식이 달라 바로 이관이 되지 않는다.
sql loader 로 들어갈수 있게 형식을 맞추고 집어넣어야 한다.

1. sybase 에서 view 만들기

-- 오라클 date 타입 으로 변경 

create view aaa
as
select aa, bb,
convert(varchar, datetime_column, 112) || str_replace( convert(varchar, datetime_column, 8),':',null) dt
from souce_tab
go
=> yyyymmddhh24miss 형식으로 datetime 을 바꿔준다.

-- 오라클 timestamp 타입(milsec 까지) 으로 변경 

convert(varchar, datetime_column, 112) || str_replace( convert(varchar, datetime_column, 20),':',null) dt

=> yyyymmddhh24missff 형식으로 datetime 을 바꿔준다.

2. bcp out 으로 덤프 내리기

bcp owner..table_name out ./dat/TAB_NM.dat -c -t'|~#' -r'!@\r\n' -U계정 -P비번
-t : field terminator => '|' 한글자는 데이터에 포함될 가능성이 있고, '||' 두글자는 널 컬럼이 많을 경우 제대로 인식 못할 가능성. 안전하게 특수문자 2,3 개
-r : row terminator => 필드 하나의 값에 여러줄 데이터가 있을 경우 loader 가 row 를 잘 인식못한다.
'특수문자 + 개행' 조합 으로 한줄의 끝을 정하자.

SELECT 'BCP '||OWNER||'..'||TABLE_NAME||' OUT ./dat/'||table_name||'.dat -c -t'||''''||'|~#'||''''||' -r'||''''||'!@\r\n'||''''||' -Uxx -Pp'
FROM DBA_TABLES
WHERE OWNER='XXX'
ORDER BY TABLE_NAME
;

3. control 파일 만들기
이관 대상 테이블이 여러개일 경우, 한방 쿼리로 ctl 파일을 만들자.

========= mk_ctl.sql ===========
SET PAGESIZE 0
SET ECHO OFF
SET VERIFY OFF
SET FEEDBACK OFF

spool ./ctl/&&..ctl

SELECT 'LOAD DATA'||CHAR(10)
||'INFILE '||''''||'./dat/'||t.table_name||'.dat'||''''||' "str '||''''||'!@\r\n'||''''||'"'||CHR(10)
||'TRUNCATE '||CHR(10)
||'INTO TABLE '||OWNER||'.'||TABLE_NAME||CHR(10)
||'FIELDS TERMINATED BY '||''''||','||''''||CHR(10)
||'TRAILING NULLCOLS'||CHR(10)
||'('
FROM DBA_TABLES T
WHERE T.OWNER='AA'
AND T.TABLE_NAME LIKE '&&1'
UNION ALL
SELECT DECODE(COLUMN_ID,1,'',',')||COLUMN_NAME||
DECODE(DATA_TYPE,'VARCHAR2',' CHAR('||DATA_LENGTH||')',
'CHAR',' CHAR('||DATA_LENGTH||')',
'CLOB',' CHAR('||DATA_LENGTH||')',
'DATE',' DATE '||''''||'YYYYMMDDHH24MISS'||''''
)
FROM (
SELECT T.OWNER, T.TABLE_NAME, C.COLUMN_NAME, C.DATA_TYPE,
C.DATA_LENGTH, C.COLUMN_ID
FROM DBA_TABLES T, DBA_TAB_COLS C
WHERE T.OWNER='AA'
AND T.TABLE_NAME = C.TABLE_NAME
AND T.TABLE_NAME LIKE '&&1'
ORDER BY COLUMN_ID
)
UNION ALL
SELECT ')' FROM DUAL
;

spool off
exit;

** data_type = timestamp(6) => .ctl  timestamp missff

입력값 (테이블 이름) 을 받고 ctl 파일 만들기
========= mk_ctl.sh ===========

sqlplus -s lee/pwd @mk_ctl $1

========= run_mkctl.sh ===========

./mk_ctl.sh TAB_NM
./mk_ctl.sh TAB_NM

4. sql loader 실행
loader 스크립트 만들기
sqlldr userid=xxx/aaa control='./ctl/aaa.ctl' log='./log/aaa.log' bad='./bad/aaa.bad'

select 'sqlldr ....'
from dba_tables t
where t.owner='XXX'
order by table_name

 oracle linux 에 oracle 19c 를 설치 해보자.

가상화머신은 oracle virtual box 를 사용하였다.  

아래 글에서 가상화머신을 다운 받을수 있다. 

https://riorio.tistory.com/403

1. sw 다운로드

 - oracle linux

https://yum.oracle.com/oracle-linux-isos.html

위의 링크에서 full 로 다운을 받자.  

- oracle 19.3 

지금 21까지 나와있는데 19c 를 많이 사용하고 있으니 19c 를 다운받자. 

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

2. virtual box 리눅스 설치 

virtual box 실행 -> 머신 -> 새로만들기 

 메모리는 최소 1G 를 잡아줘야 에러없이 DB 설치를 무난하게 할수 있다. 

3. 오라클 설치 준비

yum -y install oracle-database-preinstall-19c


# vi /etc/sysctl.conf
fs.file-max = 6815744
kernel.sem = 250 32000 100 128
kernel.shmmni = 4096
kernel.shmall = 1073741824
kernel.shmmax = 4398046511104
kernel.panic_on_oops = 1
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
net.ipv4.conf.all.rp_filter = 2
net.ipv4.conf.default.rp_filter = 2
fs.aio-max-nr = 1048576
net.ipv4.ip_local_port_range = 9000 65500


# vi /etc/security/limits.d/oracle-database-preinstall-19c.conf
oracle   soft   nofile    1024
oracle   hard   nofile    65536
oracle   soft   nproc    16384
oracle   hard   nproc    16384
oracle   soft   stack    10240
oracle   hard   stack    32768
oracle   hard   memlock    134217728
oracle   soft   memlock    134217728


- 유저 및 그룹 수정

 # usermod -g dba -G dba oracle 

- selinux permissive 설정

# vi /etc/selinux/config
SELINUX=permissive


- 방화벽 해제
 
# systemctl stop firewalld
# systemctl disable firewalld
 

- 설치 경로 생성
 
mkdir -p /app/oracle/product/19.0.0/db_1/
mkdir -p /app/oradata
chown -R oracle:oinstall /app 
chmod -R 775 /app

## -p 옵션은 없는 경로를 만들어가면서 생성한다.  


- 서버에 Oracle Database 설치 미디어 업로드 후 권한 부여

 
# chown oracle:oinstall LINUX.X64_193000_db_home.zip
# ls -al /app/oracle/product/19.0.0/db_1/

- 오라클 계정 접속 후 .bash_profile 에 아래 내용 추가
  
# su - oracle
$ vi .bash_profile

---------------- .bash_profile -------------------------
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

export TMP=/tmp
export TMPDIR=$TMP

export LANG=C
export LC_ALL=C
export CV_ASSUME_DISTID=RHEL7.6

export ORACLE_HOSTNAME=oel.ora19
export ORACLE_UNQNAME=ora19
export ORACLE_BASE=/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/19.0.0/db_1
export ORA_INVENTORY=/oraInventory
export ORACLE_SID=ora19
export DATA_DIR=/app/oradata
export PATH=/usr/sbin:/usr/local/bin:$PATH
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

---------------- .bash_profile -------------------------

4. 오라클 설치 

runInstaller 실행 

DB 생성중에 실패했다면 나중에 디비만 다시 생성하자

=> dbca 

오라클 이외의 DB 에서 오라클로 데이터를 이관할 때는 SQL Loader 가 유용하다. 

1. 사용법
ctl dat 분리

2. 한글 nls
  load시 한글깨져서 들어가는현상
NLS_LANG=AMERICAN_AMERICA.AL32UTF8

3. field row terminator
    INFILE 'A.dat'   "str '@'"
    fields terminated by ''

# 한글 깨질경우 구분자 먹기도 한다

# 구분자는 한글자보단 두글자, 똑같은거 말고 서로다른 두글자 이상 

4. exceed length
    ctl 에 char 명시 (255 이상)

5. trailing null
  데이터파일 컬럼수 적을때

6. LOB 타입 

https://docs.oracle.com/cd/A97630_01/server.920/a96652/ch05.htm

오라클은 한번 라이센스를 구매하면 그걸로 끝이 아니다.
날짜가 지나감에 따라 제한된 support 만 제공된다.
오라클 공홈에 라이센스 정책에 대해서 잘 나와있다.
여기서는 간단하게만 정리하고, 자세한 내용은 글 가장 하단의 technical support policies
링크를 통해서 원문을 확인해보자.

- 오라클 lifetime support 종류
오라클의 새 버전이 출시되면 premier support 가 공급일로부터 5년간 가능.
이후, extended support 가 3년.
이후에도 새 버전으로 넘어가지 않으면 sustaining support 만이 유일한 선택지이다.
이 단계에서는 제한된 Software Update License & Support를 제공 받는다.

2021 년 현재는 많은 사이트에서 12.1 버전을 사용하고 있을거 같은데 extended support 가 얼마남지 않았다.

- 일부만 라이선스를 구매하는것
오라클을 한세트만이 아닌 여러세트를 사용하는 기업들이 많을 것이다.
비용을 줄이기 위해서 이 중 일부만 최신으로 유지하고 싶은 생각을 해볼수 있는데
문서에는 아래정도가 예외로 두는것 같다.

물론 이것은 공식적 입장이고 정확한 것은 오라클 영업담당에게 확인해보자

- oracle technical support policies
아래 링크에서 local language 를 선택하면 한글로 된 문서를 확인할수 있다.
https://www.oracle.com/support/policies.html

뷰를 생성하기위해서는 create view 권한이 필요하다. 

자신의 스키마가 아닌 다른 스키마에 뷰를 생성하기 위해서는 create any view 권한이 필요하다. 

1. grant create view

2. view vs any view

 ===  1. grant create view ====

create user USER_A identified by "q1w2e3r4";

grant resource to USER_A;

grant connect to USER_A;

exit

sqlplus USER_A/q1w2e3r4

create table USER_A.test_tab(a int);

Table created.

create or replace view USER_A.vw_a

as

select '1' a

from USER_A.test_tab

;

ORA-01031: insufficient privileges

exit

sqlplus / as sysdba

grant create view to USER_A;

exit

sqlplus USER_A/q1w2e3r4

create or replace view USER_A.vw_a

as

select '1' a

from USER_A.test_tab

;

View created.

exit

===  2. grant create any view ====

sqlplus / as sysdba

create user USER_B identified by "q1w2e3r4";

grant resource to USER_B;

grant connect to USER_B;

grant create view to USER_B;

grant select on USER_A.test_tab to USER_B;

exit

sqlplus USER_B/q1w2e3r4

create or replace view USER_B.vw_a

as

select '1' a

from USER_A.test_tab

;

View created.

exit

sqlplus USER_A/q1w2e3r4

create or replace view USER_B.vw_a2

as

select '1' a

from USER_A.test_tab

;

ORA-01031: insufficient privileges

exit

sqlplus / as sysdba

grant create any view to USER_A;

create or replace view USER_B.vw_a2

as

select '1' a

from USER_A.test_tab

;

View created.

drop user USER_A cascade;

drop user USER_B cascade;

===  3. 뷰 권한 확인  ====

select * from dba_sys_privs where grantee like 'USER%';

테이블에 대한 권한을 role 에 주는 것과 바로 유저에게 주는 것에 차이가 있을까?

ex)  grant select on tabA to roleA  VS  grant select on tabA to userA

두 경우 모두 테이블을 직접 사용하는 것에는 차이가 없다. 

하지만 만약 role 로 부여받은 권한으로 pl/sql 을 작성하려고 하면 에러가 날 것이다. 

==============  테스트 스크립트 =================

-- 1. 테이블 오너 tab_user,  프로시져를 생성할  ddl_user  생성

create user tab_user identified by "q1w2e3r4";

conn tab_user/"q1w2e3r4" 

create user ddl_user identified by "q1w2e3r4"; 

conn ddl_user/"q1w2e3r4"

grant resource to tab_user;

grant connect to tab_user;

grant resource to ddl_user;

grant connect to ddl_user;

alter user tab_user quota 30M on users;

-- 2. 프로시저에 쓰일 테스트 테이블, 데이터 생성

create table tab_user.ddltest(

a int

) ;

insert into tab_user.ddltest values(1);

commit;

-- 3. 롤 생성

create role test_role;

grant select on tab_user.ddltest to test_role;

grant test_role to ddl_user;

sqlplus ddl_user/"q1w2e3r4"

select * from tab_user.ddltest;

=> 조회 가능 

-- 4. 테스트 프로시져 생성

create or replace procedure ddl_user.test_pcdr

is

 v_msg varchar2(100);

 begin

           select a into v_msg

           from tab_user.ddltest;

end;

/

=> PL/SQL: ORA-00942 : table or view does not exist

-- 5. 직접 권한 부여 

grant select on tab_user.ddltest to ddl_user;

이후에 프로시져를 생성하면 잘 된다. 

## 결론 : 권한관리를 위해 role 오브젝트를 사용하여 개발자에게 권한을 부여하지만

   pl/sql 을 사용하기 위해서는 직접 사용자에게 권한이 주어져야 한다. 

지금 조회되는 테이블이 role 을 부여받아서 조회가 되는건지 다이렉트로 권한을 받아서 되는건지

확인 하고자 할 때는 아래와 같이 하면 된다. 

select * from tab_user.ddltest;

 => 조회 가능 

set role none;

select * from tab_user.ddltest;

 => ORA-00942

이렇다면 role 로만 권한을 받은 것이다. 

순차적으로 증가하는 번호를 만드는 방벙에 대해서 알아보자

1. 채번테이블

   채번가진 테이블 

   create table chaebun(

    seq number

   constraint primary key (seq) )

장점 : 중복 x (pk 제약조건을 걸어놓았다면)

단점 : 성능저하 (채번값수정 ->채번값입력 -> commit) lock, 경합 발생

       관리포인트 증가 

2. 최대값 +1

 insert into  

 select max(seq) +1 from 주문 

 장점 : 빠르다. 채번만을 위한 오브젝트를 따로 만들 필요없어 관리가 용이 

 단점 : 중복가능 (lock 을 사용하지 않는다면)  

3. 시퀀스

오라클에서 제공하는 시퀀스 오브젝트.

 장 : 빠름. lock X, 중복 없음

 단 : 관리항목 증가. 오라클 시퀀스의 특성을 이해하지 못하면 예상치 못한 결과 나올수도 

- 시퀀스 오브젝트 생성시 고려 요소 

  : 유일하기만 하면 되는가? => no order, cache

   순서가 반드시 중요한것인가? => order

   중간중간 누락이 되어도 괜찮은가 ? => cache

   재활용 될수 있는가 ?  => cycle

   순서도 보장, 누락도 허용안되 => order, no cache 

오라클 로그인 시 특수문자를 입력하는 방법에 대해서 알아보자

아래 같은 DB 계정을 생성한다고 가정.

ID / password :   test / qwer!@#$

 => create user test identified by "qwer!@#$";

1. golden, toad 같은 client tool 에서 로긴

  - 특수문자를 넣는다고 해서 달라질게 없다. 변동 무

2. sqlplus 로긴

  - 다른 유저로 로긴 후  conn 으로 변경시

    => 비밀번호 를 쌍따옴표로 감싸기

     sqlplus / as sysdba

      conn  test/"qwer!@#$"

  - sqlplus 로 바로 접속시 

   => 패스워드부분을 쌍따옴표로 감싸고 @,쌍따옴표 앞에 역슬래쉬(\) 를 붙인다. 

  sqlplus test/\"qwer!\@#$\"

 특수문자 @ 는 오라클에서 디비를 식별할때 쓰기 때문에 sqlplus 명령에서 쓰려면

  위와같은 조치를 해줘야 한다. 

토드를 사용하면 object 의 DDL 정보를 쉽게 확인 할 수 있다. 

그래서 계정의 경우 부여받은 role, 권한 까지  모두 확인할수 있다.

그러나 orange 에서는 그게 안되서 각 권한에 해당하는 sql 을 작성해서 확인해야한다. 

뭐.. 항상 토드를 쓰는 환경으로 갈수는 없는 일이니까  이번 기회에 정리를 하자.

## Role 이 포함한 권한 확인

- select * from dba_tab_privs where grantee='ROLE 이름';

## 유저에게 부여된 권한 확인

- select * from dba_role_privs where grantee='USER이름';

- select * from dba_sys_privs where grantee='USER이름';  ( create view 등) 

## DB object 에 대한 권한을 누가 가지고 있나  ( role 은 제외되고 grantee 만 되나보다)

  select * from dba_tab_privs where table_name= 'PLSQL/TABLE명';

## A 의 object권한을 B 에 동일하게 주기

select 'GRANT '||PRIVILEGE||' ON '||OWNER||'.'||TABLE_NAME||' TO 받는사람(B);'

from dba_tab_privs where grantee=주는사람(A);

## userB 에게 없는 userA 의 object 권한을 주기 (A 의 권한이랑 똑같이 B 에게 주기)

권한은 중복으로 줘도 상관이 없지만 중복없이 주는 쿼리는 outer join 이나 차집합으로 구할수 있다.

SELECT 'GRANT '||A.PRIVILEGE||' ON '||A.OWNER||'.'||A.TABLE_NAME||' TO userB;' STMT
  FROM (
        select '유저A 이름' AS NM, GRANTEE,OWNER,TABLE_NAME,GRANTOR,PRIVILEGE,TYPE
          from dba_tab_privs
         where GRANTEE ='userA'
       )A,
       (
        select '유저B 이름' AS NM, GRANTEE,OWNER,TABLE_NAME,GRANTOR,PRIVILEGE,TYPE
          from dba_tab_privs
         where GRANTEE ='userB'
       )B
 WHERE A.OWNER = B.OWNER(+) 
   AND A.TABLE_NAME = B.TABLE_NAME(+)
   AND A.GRANTOR = B.GRANTOR(+)
   AND A.PRIVILEGE = B.PRIVILEGE(+)
   AND B.NM IS NULL
ORDER BY A.OWNER, A.TABLE_NAME, A.PRIVILEGE, A.TYPE