Translate

2019년 11월 13일 수요일

[Docker] 개발DB Oracle 환경을 로컬에 그대로 구축하는 방법




포스팅 작성날짜: 2019.11.13


서문

이 글은 회사에서 진행하는 프로젝트의 개발DB서버가 외부에서 접속이 되지 않는 상황에 사용하기 위해 작성이 되었다.

외부에서 개발DB로 접속은 되지 않지만 프로젝트 내부에서 인터넷이 되거나 외부에서 접속 가능한 SVN/GIT 같은 환경이 있다면 사용해볼만 하다.

사내 개발DB와 같은 환경(스키마,데이터)를 어디서나 이용할 수 있다는데 장점이 있다.


PS.
단순히 깨끗한 오라클 개발DB가 필요한 경우라는 다음 링크를 참고하길 바란다.
링크 - [Docker] Oracle 12c 구성 및 사용하기 (Oracle 공식 배포버전)

이 글은 위 링크의 글에서 확장된 개념으로 생각하면 좋을 듯 싶다.
따라서 위 링크의 내용을 따라하지 않더라도 한번 훓어보고 어떤식으로 진행되는지 알고 있어야 한다.




작업 순서

1. 하려는 것
2. 준비해야 할 것
2. 작업 개념 설명
3. 작업 방법
4. 작업 실행
5. 주의사항




하려는 것

- 기본적으로 오라클 개발DB와 동일한 환경을 로컬에 구성
1. 접속정보 동일 (SID, id, pw, port)
2. 테이블 및 데이터 동일
 (아래 준비사항에서 설명하듯 개발DB의 백업 sql 파일이 필요한다.)





준비해야 할 것

- 오라클 개발DB의 백업 sql 파일 필요

1. 스키마 파일 (create 구문)
ex)
create table API_ACCESS
(
    ACCESS_DT   DATE               not null,
    ACCESS_CODE VARCHAR2(200 char) not null,
    EXPIRE_IN   NUMBER(10)         not null
)
/

2. 데이터 파일 (insert 구문)
ex)
INSERT INTO API_ACCESS (ACCESS_DT, ACCESS_CODE, EXPIRE_IN) VALUES (TO_DATE('2019-07-26 14:35:33', 'YYYY-MM-DD HH24:MI:SS'), '123456', 86400);
INSERT INTO API_ACCESS (ACCESS_DT, ACCESS_CODE, EXPIRE_IN) VALUES (TO_DATE('2019-07-25 14:21:47', 'YYYY-MM-DD HH24:MI:SS'), '123456', 86400);
INSERT INTO API_ACCESS (ACCESS_DT, ACCESS_CODE, EXPIRE_IN) VALUES (TO_DATE('2019-07-09 11:47:58', 'YYYY-MM-DD HH24:MI:SS'), '123456', 86400);
INSERT INTO API_ACCESS (ACCESS_DT, ACCESS_CODE, EXPIRE_IN) VALUES (TO_DATE('2019-07-10 11:48:22', 'YYYY-MM-DD HH24:MI:SS'), '123456', 86400);
INSERT INTO API_ACCESS (ACCESS_DT, ACCESS_CODE, EXPIRE_IN) VALUES (TO_DATE('2019-07-11 12:06:58', 'YYYY-MM-DD HH24:MI:SS'), '123456', 86400);
INSERT INTO API_ACCESS (ACCESS_DT, ACCESS_CODE, EXPIRE_IN) VALUES (TO_DATE('2019-07-12 15:53:32', 'YYYY-MM-DD HH24:MI:SS'), '123456', 86400);





작업 개념

- Oracle에서 배포한 Oracle 12C 이미지를 Docker 컨테이너 구동 시
초기에 내부적으로 Oracle 셋팅을 하게되는데
컨테이너 구동 전 Oracle 셋팅파일 커스텀한 파일로 교체하여
원하는대로 Oracle 셋팅을 할 수 있게 작업한다.
(도커 컨테이너 구동 후 setup 디렉토리의 파일들을 모두 내려받아 스크립트 분석 하게되면 본인의 입맞에 맞게 다른 커스텀이 가능하다.)


D:\source\1004lucifer\src\main\docker>dockerSsh.bat

D:\source\1004lucifer\src\main\docker>docker exec -it local_db bash
[oracle@c1c7c5acbca2 ~]$
[oracle@c1c7c5acbca2 ~]$ ll
total 8
drwxr-xr-x 1 oracle oinstall 4096 Nov 13 00:35 initSql
drwxr-xr-x 1 oracle oinstall 4096 Nov 13 00:40 setup
[oracle@c1c7c5acbca2 ~]$
# 컨테이너 구동 전 테이블, 데이터 파일을 미리 복사 (/home/oracle/initSql/*.sql)
[oracle@c1c7c5acbca2 ~]$ ll initSql/
total 8
-rwxr-xr-x 1 oracle oinstall 851 Nov 12 23:55 API_ACCESS.sql
-rwxr-xr-x 1 oracle oinstall 171 Nov 12 23:31 SCHEME.sql
[oracle@c1c7c5acbca2 ~]$
# configDB.sh, configDBora.sh 기본 셋팅파일을 커스텀 파일로 교체
[oracle@c1c7c5acbca2 ~]$ ll setup/
total 60
-rw-r--r-- 1 oracle oinstall  157 Nov 13 00:40 DB_ENV
-rwxr-xr-x 1 oracle oinstall 1591 Nov 13 00:22 configDB.sh
-rwxr-xr-x 1 oracle oinstall 7434 Nov 13 00:01 configDBora.sh
-rwxr-xr-- 1 oracle oinstall 2165 Aug 18  2017 dockerInit.sh
-rwxr-xr-- 1 oracle oinstall  763 Aug 18  2017 healthcheck.sh
drwxr-xr-- 1 oracle oinstall 4096 Nov 13 00:38 log
-rwxr-xr-- 1 oracle oinstall 1782 Aug 18  2017 paramChk.sh
-rwxr-xr-- 1 oracle oinstall   18 Aug 18  2017 patchDB.dat
-rwxr-xr-- 1 oracle oinstall 1229 Aug 18  2017 patchDB.sh
-rwxr-xr-- 1 oracle oinstall 3019 Aug 18  2017 setupDB.sh
-rwxr-xr-- 1 oracle oinstall 1298 Aug 18  2017 shutDB.sh
-rwxr-xr-- 1 oracle oinstall 1361 Aug 18  2017 startupDB.sh
-rwxr-xr-- 1 oracle oinstall 1708 Aug 18  2017 tnsentry.sh
-rwxr-xr-- 1 oracle oinstall 1340 Aug 18  2017 untarDB.sh
[oracle@c1c7c5acbca2 ~]$






작업 방법

- 디렉토리 구조는 Java 프로젝트 기준으로 다음과 같다.
(서로에 대해 상대경로로 의존을 하기 때문에 본 내용의 내용과 똑같이 하려면 구조를 맞춰줘야 한다.)


1. SCHEME.sql, API_ACCESS.sql 파일은 위에 준비해야 할것의 create, insert 파일



2. ORACLE 셋팅 설정 값

- ora_sample.conf 파일 (샘플파일)

DB_SID=             # default: ORCLCDB
DB_PDB=             # default: ORCLPDB1
DB_MEMORY=          # default: 2GB
DB_DOMAIN=          # default: localdomain


- ora.conf (실제 사용하는 파일)

DB_SID=LUCIFER




3. Dockerfile (이미지 구성 파일)


FROM store/oracle/database-enterprise:12.2.0.1-slim

WORKDIR /home/oracle

RUN mkdir -p initSql
COPY --chown=oracle:oinstall sql/SCHEME.sql initSql/
COPY --chown=oracle:oinstall sql/API_ACCESS.sql initSql/

COPY --chown=oracle:oinstall dockerInit/configDB.sh setup/configDB.sh
COPY --chown=oracle:oinstall dockerInit/configDBora.sh setup/configDBora.sh




4. Oracle 커스텀 셋팅 파일

- configDB.sh

#!/bin/sh
#
# $Header: dbaas/docker/build/dbsetup/setup/configDB.sh rduraisa_docker_122_image/2 2017/03/02 13:26:06 rduraisa Exp $
#
# configDB.sh
#
# Copyright (c) 2016, 2017, Oracle and/or its affiliates. All rights reserved.
#
#    NAME
#      configDB.sh - configure database as root
#
#    DESCRIPTION
#      rename the DB to customized name
#
#    NOTES
#      run as root and call renameDBora.sh inside
#
#    MODIFIED   (MM/DD/YY)
#    rduraisa    03/02/17 - Modify scripts to build for 12102 and 12201
#    xihzhang    10/25/16 - Remove EE bundles
#    xihzhang    08/08/16 - Remove privilege mode
#    xihzhang    05/23/16 - Creation
#

echo `date`
echo "Start Docker DB configuration"

# basic parameters
ENV_FILE=/home/oracle/setup/DB_ENV
BASH_RC=/home/oracle/.bashrc
ORA_TAB=/etc/oratab

# set env
source $ENV_FILE

ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1

# set env for oracle user
echo "export ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1" >> $BASH_RC
echo "export OH=$ORACLE_HOME" >> $BASH_RC
echo "export PATH=$PATH:$ORACLE_HOME/bin" >> $BASH_RC
echo "export TNS_ADMIN=$ORACLE_HOME/admin/${DB_SID}" >> $BASH_RC
echo "export ORACLE_SID=$DB_SID;" >> $BASH_RC

# set env - 1004lucifer
# 시간대역을 설정해 준다.
echo "export TZ='Asia/Seoul'" >> /home/oracle/.bashrc

source $BASH_RC

# configure database
echo "Call configDBora.sh to configure database"
/bin/bash /home/oracle/setup/configDBora.sh

# remove passwd info
echo "Remove password info"
sed -i '/DB_PASSWD/d' $ENV_FILE
unset DB_PASSWD

echo "Docker DB configuration is complete !"

# end


- configDBora.sh

#!/bin/sh
#
# $Header: dbaas/docker/build/dbsetup/setup/configDBora.sh rduraisa_docker_122_image/6 2017/04/02 06:29:55 rduraisa Exp $
#
# configDBora.sh
#
# Copyright (c) 2016, 2017, Oracle and/or its affiliates. All rights reserved.
#
#    NAME
#      configDBora.sh - configure database as oracle user
#
#    DESCRIPTION
#      rename the DB to customized name
#
#    NOTES
#      run as oracle
#
#    MODIFIED   (MM/DD/YY)
#    rduraisa    03/02/17 - Modify scripts to build for 12102 and 12201
#    xihzhang    10/25/16 - Remove EE bundles
#    xihzhang    08/08/16 - Remove privilege mode
#    xihzhang    05/23/16 - Creation
#

echo `date`
echo "Configure DB as oracle user"

# basic parameters
SETUP_DIR=/home/oracle/setup

IFS='.' read -r -a dbrelarr <<< 12.2.0

if [[ ${dbrelarr[0]} == 12 && ${dbrelarr[1]} == 1 ]];
then
    PATCH_LOG=${SETUP_DIR}/log/patchDB.log
    # unpatch opc features
    echo "Patching Database ...."
    /bin/bash ${SETUP_DIR}/patchDB.sh 2>&1 >> ${PATCH_LOG}

    $ORACLE_HOME/bin/relink as_installed
fi

#setup directories and soft links
echo "Setup Database directories ..."
mkdir -p /ORCL/u01/app/oracle/diag /u01/app/oracle /u02/app/oracle /u03/app/oracle /u04/app/oracle
mkdir -p $TNS_ADMIN
ln -s /ORCL/$ORACLE_HOME/dbs $ORACLE_HOME/dbs
ln -s /ORCL/u01/app/oracle/diag /u01/app/oracle/diag
ln -s /ORCL/u02/app/oracle/audit /u02/app/oracle/audit
ln -s /ORCL/u02/app/oracle/oradata /u02/app/oracle/oradata
ln -s /ORCL/u03/app/oracle/fast_recovery_area /u03/app/oracle/fast_recovery_area
ln -s /ORCL/u04/app/oracle/redo /u04/app/oracle/redo

if [[ $EXISTING_DB = false ]];
then
  cd /u01/app/oracle/product/12.2.0/dbhome_1/dbs/
  # set domain
  echo "*.db_domain='$DB_DOMAIN'" >> initORCLCDB.ora
  # set sga & pga
  MEMORY=${DB_MEMORY//[!0-9]/}
  SGA_MEM=$(($MEMORY * 640))M
  PGA_MEM=$(($MEMORY * 384))M
  echo "*.sga_target=$SGA_MEM" >> initORCLCDB.ora
  echo "*.pga_aggregate_target=$PGA_MEM" >> initORCLCDB.ora

  # create the diag directory to avoid errors with the below mv command
  mkdir -p /u01/app/oracle/diag/rdbms/orclcdb/ORCLCDB

  if [ "$DB_SID" != "ORCLCDB" ]
  then
  # mount db
      sqlplus / as sysdba 2>&1 <<EOF
      startup mount pfile=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/initORCLCDB.ora;
      exit;
EOF

  # nid change name
      echo "NID change db name"
      echo "Y" | nid target=/ dbname=$DB_SID

  # update init.ora
      rm -f init$DB_SID.ora
      cp initORCLCDB.ora init$DB_SID.ora

  # change sid
      sed -i -- "s#ORCLCDB#$DB_SID#g" init$DB_SID.ora

  # rename all the dirs/files
      mv /u01/app/oracle/diag/rdbms/orclcdb/ORCLCDB /u01/app/oracle/diag/rdbms/orclcdb/$DB_SID
      mv /u01/app/oracle/diag/rdbms/orclcdb /u01/app/oracle/diag/rdbms/${DB_SID,,}
      mv /u02/app/oracle/audit/ORCLCDB /u02/app/oracle/audit/$DB_SID
      mv /u02/app/oracle/oradata/ORCLCDB /u02/app/oracle/oradata/$DB_SID   # cp -R
      mv /u03/app/oracle/fast_recovery_area/ORCLCDB /u03/app/oracle/fast_recovery_area/$DB_SID
      mv /u02/app/oracle/oradata/$DB_SID/cntrlORCLCDB.dbf /u02/app/oracle/oradata/$DB_SID/cntrl${DB_SID}.dbf
      mv /u03/app/oracle/fast_recovery_area/$DB_SID/cntrlORCLCDB2.dbf /u03/app/oracle/fast_recovery_area/$DB_SID/cntrl${DB_SID}2.dbf

  # make links
      cd /u02/app/oracle/oradata/
      ln -s $DB_SID ORCLCDB

  # change SID
      export ORACLE_SID=$DB_SID

  # db setup
  # enable archivelog + change global name + create spfile
      NEW_ORA=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/init$DB_SID.ora
      sqlplus / as sysdba 2>&1 <<EOF
      create spfile from pfile='$NEW_ORA';
      startup mount;
      alter database open resetlogs;
      alter database rename global_name to $DB_SID.$DB_DOMAIN;
      show parameter spfile;
      show parameter encrypt_new_tablespaces;
      alter user sys identified by "$DB_PASSWD";
      alter user system identified by "$DB_PASSWD";
      exit;
EOF

  else
  # db setup
  # enable archivelog + change global name + create spfile
      NEW_ORA=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/init$DB_SID.ora
      sqlplus / as sysdba 2>&1 <<EOF
      create spfile from pfile='$NEW_ORA';
      startup;
      alter database rename global_name to $DB_SID.$DB_DOMAIN;
      show parameter spfile;
      show parameter encrypt_new_tablespaces;
      alter user sys identified by "$DB_PASSWD";
      alter user system identified by "$DB_PASSWD";
      exit;
EOF
  fi

  # create orapw
  echo "update password"
  echo "$DB_PASSWD" | orapwd file=/u01/app/oracle/product/12.2.0/dbhome_1/dbs/orapw$DB_SID

  # create pdb
  echo "create pdb : $DB_PDB"
  sqlplus / as sysdba 2>&1 <<EOF
    create pluggable database $DB_PDB ADMIN USER sys1 identified by "$DB_PASSWD"
    default tablespace users
      datafile '/u02/app/oracle/oradata/ORCLCDB/orclpdb1/users01.dbf'
      size 10M reuse autoextend on maxsize unlimited
      file_name_convert=('/u02/app/oracle/oradata/ORCL/pdbseed','/u02/app/oracle/oradata/ORCLCDB/orclpdb1');
    alter pluggable database $DB_PDB open;
    alter pluggable database all save state;
    exit;
EOF

  # config charset - 1004lucifer
  # 한글 사용을 위해 셋팅 (개발DB 설정에 따라 바뀔 수 있음)
  echo "update language, charset"
  sqlplus / as sysdba 2>&1 <<EOF
    update sys.props$ set value$='KOREAN_KOREA.UTF8' where name='NLS_LANGUAGE';
    update sys.props$ set value$='UTF8' where name='NLS_CHARACTERSET';
    update sys.props$ set value$='UTF8' where name='NLS_NCHAR_CHARACTERSET';
    commit;
    shutdown;
    startup;
    exit;
EOF

  # create lucifer user - 1004lucifer
  # 개발 DB에서 사용하던 계정 생성
  echo "create user : lucifer"
  sqlplus / as sysdba 2>&1 <<EOF
    alter session set "_ORACLE_SCRIPT"=true;
    create user lucifer identified by 1234;
    grant connect, resource, dba to lucifer;
    exit;
EOF

  # init DB - 1004lucifer
  # 개발DB의 테이블,데이터를 구성한다.
  echo "init DB"
  export NLS_LANG=KOREAN_KOREA.UTF8
  sqlplus lucifer/1234 2>&1 <<EOF
    @/home/oracle/initSql/SCHEME.sql;
    @/home/oracle/initSql/API_ACCESS.sql;
    exit;
EOF

  if [[ ${dbrelarr[0]} == 12 && ${dbrelarr[1]} > 1 ]] || [[ ${dbrelarr[0]} > 12 ]];
  then
    echo "Reset Database parameters"
    sqlplus / as sysdba 2>&1 <<EOF
      alter system set encrypt_new_tablespaces=ddl scope=both;
      exit;
EOF
  fi
else
  echo "startup database instance"
  sqlplus / as sysdba 2>&1 <<EOF
    startup;
    exit;
EOF
fi

## db network set
# sqlnet.ora
SQLNET_ORA=$TNS_ADMIN/sqlnet.ora
echo "NAME.DIRECTORY_PATH= {TNSNAMES, EZCONNECT, HOSTNAME}" >> $SQLNET_ORA
echo "SQLNET.EXPIRE_TIME = 10" >> $SQLNET_ORA
echo "SSL_VERSION = 1.0" >> $SQLNET_ORA
# listener.ora
LSNR_ORA=$TNS_ADMIN/listener.ora
echo "LISTENER = \
  (DESCRIPTION_LIST = \
    (DESCRIPTION = \
      (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521)) \
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) \
    ) \
  ) \
\
" >> $LSNR_ORA
echo "DIAG_ADR_ENABLED = off"  >> $LSNR_ORA
echo "SSL_VERSION = 1.0"  >> $LSNR_ORA
# tnsnames.ora
TNS_ORA=$TNS_ADMIN/tnsnames.ora
echo "$DB_SID = \
  (DESCRIPTION = \
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521)) \
    (CONNECT_DATA = \
      (SERVER = DEDICATED) \
      (SERVICE_NAME = $DB_SID.$DB_DOMAIN) \
    ) \
  ) \
" >> $TNS_ORA
echo "$DB_PDB = \
  (DESCRIPTION = \
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1521)) \
    (CONNECT_DATA = \
      (SERVER = DEDICATED) \
      (SERVICE_NAME = $DB_PDB.$DB_DOMAIN) \
    ) \
  ) \
" >> $TNS_ORA

# start listener
lsnrctl start

# clean
unset DB_PASSWD
history -w
history -c

echo ""
echo "DONE!"

# end




5. 도커 명령어 (배치)실행 파일

- dockerBuild.bat

docker build --tag local_db:1.0 .


- dockerRun.bat  (접속포트 수정 => 외부접속포트:1521)

docker run -dit --name local_db -p 1521:1521 --env-file dockerInit/ora.conf local_db:1.0


- dockerSsh.bat

docker exec -it local_db bash






작업 실행

- 도커 (배치)실행파일 수행


Microsoft Windows [Version 10.0.18362.418]
(c) 2019 Microsoft Corporation. All rights reserved.

D:\source\1004lucifer\src\main\docker>dockerBuild.bat

D:\source\1004lucifer\src\main\docker>docker build --tag local_db:1.0 .
Sending build context to Docker daemon  23.55kB
Step 1/7 : FROM store/oracle/database-enterprise:12.2.0.1-slim
12.2.0.1-slim: Pulling from store/oracle/database-enterprise
4ce27fe12c04: Pull complete
9d3556e8e792: Pull complete
fc60a1a28025: Pull complete
0c32e4ed872e: Pull complete
be0a1f1e8dfd: Pull complete
Digest: sha256:dbd87ae4cc3425dea7ba3d3f34e062cbd0afa89aed2c3f3d47ceb5213cc0359a
Status: Downloaded newer image for store/oracle/database-enterprise:12.2.0.1-slim
 ---> 27c9559d36ec
Step 2/7 : WORKDIR /home/oracle
 ---> Running in d28905860446
Removing intermediate container d28905860446
 ---> 85483b9a765c
Step 3/7 : RUN mkdir -p initSql
 ---> Running in a0bce3911848
Removing intermediate container a0bce3911848
 ---> 32291109be05
Step 4/7 : COPY --chown=oracle:oinstall sql/SCHEME.sql initSql/
 ---> 0f42f6808349
Step 5/7 : COPY --chown=oracle:oinstall sql/API_ACCESS.sql initSql/
 ---> 6d7cc0d3d8eb
Step 6/7 : COPY --chown=oracle:oinstall dockerInit/configDB.sh setup/configDB.sh
 ---> f508c8847763
Step 7/7 : COPY --chown=oracle:oinstall dockerInit/configDBora.sh setup/configDBora.sh
 ---> b369d71bdf7b
Successfully built b369d71bdf7b
Successfully tagged local_db:1.0
SECURITY WARNING: You are building a Docker image from Windows against a non-Windows Docker host. All files and directories added to build context will have '-rwxr-xr-x' permissions. It is recommended to double check and reset permissions for sensitive files and directories.

D:\source\1004lucifer\src\main\docker>
D:\source\1004lucifer\src\main\docker>
D:\source\1004lucifer\src\main\docker>
D:\source\1004lucifer\src\main\docker>dockerRun.bat

D:\source\1004lucifer\src\main\docker>docker run -dit --name local_db -p 1521:1521 --env-file dockerInit/ora.conf local_db:1.0
c1c7c5acbca239124ca3946c3cb3d2fe8983e45352f749a93cd44979921288d1

D:\source\1004lucifer\src\main\docker>




위와같이 도커를 띄운 후 잠시 후 Oracle 접속하면 정상적으로 테이블과 데이터가 들어가 있는것을 확인 할 수 있다.






주의사항

다음의 상황에서 문제가 발생할 수 있다.

1. insert 쿼리가 들어있는 sql 파일이 바로 실행할 수 없는 내용이 있다.

 예를들어 한줄의 insert 쿼리가 아니라 아래와 같은 경우에는 모든 데이터가 올바르게 들어가지 않을 수도있다. (게시판 컨텐츠의 경우 이와 같이 나올 수 있다.)

insert 구문이 모두 한줄로 나올 수 있도록 수정을 해야 한다.
(나의 경우에는 에디터에서 정규표현식을 이용해 일괄적으로 수정했다.)


insert into TABLE_NAME values (1, '게시판 내용 입니다.', sysdate);
insert into TABLE_NAME values (2, '일반 게시판
내용 입니다', sysdate);
insert into TABLE_NAME values (3, '게시판 내용', sysdate);




2. 로컬 오라클에 접속 되지 않는다.

- 도커 컨테이너 내부에서 오라클 셋팅이 끝나고 리스너까지 올라오는데 시간이 걸린다. 잘못된 설정이 없더라도 컨테이너를 띄우고 어느정도 시간이 지나기 까지 접속이 되지 않는다.



* 아래와 같이 docker 로그 확인이 가능하며 로그 확인 시 어느부분이 문제가 되는지 대부분 찾을 수 있다.


D:\source\1004lucifer\src\main\docker>docker logs local_db
Setup Oracle Database
Oracle Database 12.2.0.1 Setup
Tue Nov 12 15:36:32 UTC 2019

Check parameters ......
log file is : /home/oracle/setup/log/paramChk.log
paramChk.sh is done at 0 sec

untar DB bits ......
log file is : /home/oracle/setup/log/untarDB.log
untarDB.sh is done at 94 sec

config DB ......
log file is : /home/oracle/setup/log/configDB.log

DBNEWID: Release 12.2.0.1.0 - Production on Wed Nov 13 00:38:21 2019

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Connected to database ORCLCDB (DBID=2722599211)

Connected to server version 12.2.0

Control Files in database:
    /u02/app/oracle/oradata/ORCLCDB/cntrlORCLCDB.dbf
    /u03/app/oracle/fast_recovery_area/ORCLCDB/cntrlORCLCDB2.dbf

Change database ID and database name ORCLCDB to LUCIFER? (Y/[N]) =>
Proceeding with operation
Changing database ID from 2722599211 to 1882728639
Changing database name from ORCLCDB to LUCIFER
    Control File /u02/app/oracle/oradata/ORCLCDB/cntrlORCLCDB.dbf - modified
    Control File /u03/app/oracle/fast_recovery_area/ORCLCDB/cntrlORCLCDB2.dbf - modified
    Datafile /u02/app/oracle/oradata/ORCL/system01.db - dbid changed, wrote new name
    Datafile /u02/app/oracle/oradata/ORCL/undotbs01.db - dbid changed, wrote new name
    Datafile /u02/app/oracle/oradata/ORCL/sysaux01.db - dbid changed, wrote new name
    Datafile /u02/app/oracle/oradata/ORCL/pdbseed/system01.db - dbid changed, wrote new name
    Datafile /u02/app/oracle/oradata/ORCL/pdbseed/sysaux01.db - dbid changed, wrote new name
    Datafile /u02/app/oracle/oradata/ORCL/xdb01.db - dbid changed, wrote new name
    Datafile /u02/app/oracle/oradata/ORCL/pdbseed/xdb01.db - dbid changed, wrote new name
    Datafile /u02/app/oracle/oradata/ORCL/pdbseed/temp01.db - dbid changed, wrote new name
    Datafile /u02/app/oracle/oradata/ORCL/temp01.db - dbid changed, wrote new name
    Control File /u02/app/oracle/oradata/ORCLCDB/cntrlORCLCDB.dbf - dbid changed, wrote new name
    Control File /u03/app/oracle/fast_recovery_area/ORCLCDB/cntrlORCLCDB2.dbf - dbid changed, wrote new name
    Instance shut down

Database name changed to LUCIFER.
Modify parameter file and generate a new password file before restarting.
Database ID for database LUCIFER changed to 1882728639.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.

Tue Nov 12 15:38:06 UTC 2019
Start Docker DB configuration
Call configDBora.sh to configure database
Wed Nov 13 00:38:06 KST 2019
Configure DB as oracle user
Setup Database directories ...

SQL*Plus: Release 12.2.0.1.0 Production on Wed Nov 13 00:38:07 2019

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

Connected to an idle instance.

SQL> ORACLE instance started.

Total System Global Area 1342177280 bytes
Fixed Size                  8792536 bytes
Variable Size             352323112 bytes
Database Buffers          973078528 bytes
Redo Buffers                7983104 bytes
Database mounted.
SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
NID change db name

SQL*Plus: Release 12.2.0.1.0 Production on Wed Nov 13 00:38:39 2019

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

Connected to an idle instance.

SQL>
File created.

SQL> ORACLE instance started.

Total System Global Area 1342177280 bytes
Fixed Size                  8792536 bytes
Variable Size             352323112 bytes
Database Buffers          973078528 bytes
Redo Buffers                7983104 bytes
Database mounted.
SQL>
Database altered.

SQL>
Database altered.

SQL>
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/12.2.0
                                                 /dbhome_1/dbs/spfileLUCIFER.ora
SQL>
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
encrypt_new_tablespaces              string      CLOUD_ONLY
SQL>
User altered.

SQL>
User altered.

SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
update password

Enter password for SYS:
create pdb : ORCLPDB1

SQL*Plus: Release 12.2.0.1.0 Production on Wed Nov 13 00:38:53 2019

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>   2    3    4    5
Pluggable database created.

SQL>
Pluggable database altered.

SQL>
Pluggable database altered.

SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
update language, charset

SQL*Plus: Release 12.2.0.1.0 Production on Wed Nov 13 00:39:15 2019

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>
1 row updated.

SQL>
1 row updated.

SQL>
1 row updated.

SQL>
Commit complete.

SQL> Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> ORACLE instance started.

Total System Global Area 1342177280 bytes
Fixed Size                  8792536 bytes
Variable Size             352323112 bytes
Database Buffers          973078528 bytes
Redo Buffers                7983104 bytes
Database mounted.
Database opened.
SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
create user : lucifer

SQL*Plus: Release 12.2.0.1.0 Production on Wed Nov 13 00:40:33 2019

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>
Session altered.

SQL>
User created.

SQL>
Grant succeeded.

SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
init DB

SQL*Plus: Release 12.2.0.1.0 Production on 수 11월 13 00:40:34 2019

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


다음에 접속됨:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>
테이블이 생성되었습니다.

SQL>
1 개의 행이 만들어졌습니다.


1 개의 행이 만들어졌습니다.


1 개의 행이 만들어졌습니다.


1 개의 행이 만들어졌습니다.


1 개의 행이 만들어졌습니다.


1 개의 행이 만들어졌습니다.

SQL> Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production에서 분리되었습니다.
Reset Database parameters

SQL*Plus: Release 12.2.0.1.0 Production on 수 11월 13 00:40:35 2019

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


다음에 접속됨:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>
시스템이 변경되었습니다.

SQL> Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production에서 분리되었습니다.

LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 13-11월-2019 00:40:35

Copyright (c) 1991, 2016, Oracle.  All rights reserved.

시작 /u01/app/oracle/product/12.2.0/dbhome_1/bin/tnslsnr: 잠시만 기다리세요...

TNSLSNR for Linux: Version 12.2.0.1.0 - Production
시스템 매개변수 파일은 /u01/app/oracle/product/12.2.0/dbhome_1/admin/LUCIFER/listener.ora 입니다
/u01/app/oracle/diag/tnslsnr/c1c7c5acbca2/listener/alert/log.xml (으)로 로그 메시지를 기록했습니다
리스닝이: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
리스닝이: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=1521)))에 연결되었습니다
리스너의 상태
------------------------
별칭                     LISTENER
버전                     TNSLSNR for Linux: Version 12.2.0.1.0 - Production
시작 날짜                 13-11월-2019 00:40:36
업타임                   0 일 0 시간. 0 분. 0 초
트레이스 수준            off
보안                     ON: Local OS Authentication
SNMP                     OFF리스너 매개변수 파일   /u01/app/oracle/product/12.2.0/dbhome_1/admin/LUCIFER/listener.ora
리스너 로그 파일         /u01/app/oracle/diag/tnslsnr/c1c7c5acbca2/listener/alert/log.xml
끝점 요약 청취 중...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
리스너는 서비스를 지원하지 않습니다
명령이 성공적으로 수행되었습니다

DONE!
Remove password info
Docker DB configuration is complete !
configDB.sh is done at 244 sec

Done ! The database is ready for use .
# ===========================================================================
# == Add below entries to your tnsnames.ora to access this database server ==
# ====================== from external host =================================
LUCIFER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<ip-address>)(PORT=<port>))
    (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=LUCIFER.localdomain)))
ORCLPDB1=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<ip-address>)(PORT=<port>))
    (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCLPDB1.localdomain)))
#
#ip-address : IP address of the host where the container is running.
#port       : Host Port that is mapped to the port 1521 of the container.
#
# The mapped port can be obtained from running "docker port <container-id>"
# ===========================================================================
===========================================================
2019-11-13T00:40:34.972296+09:00
Resize operation completed for file# 1, old size 833688K, new size 833704K
2019-11-13T00:40:35.293824+09:00
ALTER SYSTEM SET encrypt_new_tablespaces='DDL' SCOPE=BOTH;
2019-11-13T00:40:35.539180+09:00
db_recovery_file_dest_size of 17814 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
2019-11-13T00:40:37.099015+09:00
Errors in file /u01/app/oracle/diag/rdbms/LUCIFER/LUCIFER/trace/LUCIFER_m001_569.trc:

D:\source\1004lucifer\src\main\docker>






참고
https://www.it-note.kr/180
http://databaseindex.blogspot.com/2018/04/oracle-12c-docker-windows-ocr.html


댓글 없음 :

댓글 쓰기