포스팅 작성날짜: 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
