Oracleデータベース

(16)

https://www.nakivo.com/blog/how-to-install-oracle-on-ubuntu-linux-waltkhrough/

DB : 11gR2


-- 1. OS設定


vim /etc/sysctl.conf

kernel.sem = 250 32000 100 128
kernel.shmmni = 4096
fs.file-max = 6815744
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
fs.aio-max-nr = 1048576 

sysctl -p

groupadd -g 54321 oinstall
groupadd -g 54322 dba
groupadd -g 54323 oper

adduser --home /home/oracle --uid 54321 --gid 54321 oracle
adduser oracle dba
adduser oracle oper

mkdir -p /u01/app/oracle

mkdir /u01/app/oracle/oradata
mkdir /u01/app/oracle/fra
mkdir -p /u01/app/oracle/product/11.2.0/dbhome_1

chown -R oracle:oinstall /u01
chmod -R 775 /u01

vim /etc/security/limits.conf

oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536 
oracle  soft  stack   10240
oracle  hard  stack   32768


apt install unzip

-- GUI追加
apt update
apt -y install ubuntu-desktop
reboot


-- パッケージインストール

apt -y install alien \
autoconf \
automake \
autotools-dev \
binutils \
bzip2 \
doxygen \
elfutils \
expat \
gawk \
gcc \
gcc-multilib \
g++-multilib \
libelf-dev \
libltdl-dev \
libodbcinstq4-1 libodbcinstq4-1:i386 \
libpth-dev \
libpthread-stubs0-dev \
libstdc++5 \
make \
openssh-server \
rlwrap \
rpm \
sysstat \
unixodbc \
unixodbc-dev \
unzip \
x11-utils \
zlibc \
libaio1 \
libaio-dev


apt install lib32z1

wget http://launchpadlibrarian.net/207968936/libmotif4_2.3.4-8ubuntu1_amd64.deb
dpkg -i libmotif4_2.3.4-8ubuntu1_amd64.deb
apt -f install

wget http://launchpadlibrarian.net/154418307/libpthread-stubs0-dev_0.3-4_amd64.deb
dpkg -i libpthread-stubs0-dev_0.3-4_amd64.deb
apt -f install


wget http://packages.linuxmint.com//pool/upstream/l/lsb/lsb-cxx_4.1+Debian11ubuntu6mint1_amd64.deb
dpkg -i lsb-cxx_4.1+Debian11ubuntu6mint1_amd64.deb
apt -f install

wget http://launchpadlibrarian.net/200019501/pdksh_50e-2ubuntu1_all.deb
dpkg -i pdksh_50e-2ubuntu1_all.deb
apt -f install


wget http://mirrors.edge.kernel.org/ubuntu/pool/universe/g/gcc-3.3/libstdc++5_3.3.6-30_i386.deb
wget http://mirrors.edge.kernel.org/ubuntu/pool/universe/g/gcc-3.3/libstdc++5_3.3.6-30_amd64.deb
dpkg --force-architecture -i libstdc++5_3.3.6-30_i386.deb
apt -f install

reboot


-- シンボリックリンク作成

mkdir /usr/lib64
ln -s /etc /etc/rc.d
ln -s /usr/bin/awk /bin/awk
ln -s /usr/bin/basename /bin/basename
ln -s /usr/bin/rpm /bin/rpm
ln -s /lib/x86_64-linux-gnu/libgcc_s.so.1 /usr/lib64/
ln -s /usr/lib/x86_64-linux-gnu/libc_nonshared.a /usr/lib64/
ln -s /usr/lib/x86_64-linux-gnu/libpthread_nonshared.a /usr/lib64/
ln -s /usr/lib/x86_64-linux-gnu/libstdc++.so.6 /usr/lib64/

ln -s /usr/lib/x86_64-linux-gnu /usr/lib64

ln -sf /bin/bash /bin/sh
ln -s /etc/rc0.d /etc/rc.d/rc0.d
ln -s /etc/rc2.d /etc/rc.d/rc2.d
ln -s /etc/rc3.d /etc/rc.d/rc3.d
ln -s /etc/rc4.d /etc/rc.d/rc4.d
ln -s /etc/rc5.d /etc/rc.d/rc5.d
ln -s /etc/rc6.d /etc/rc.d/rc6.d
ln -s /etc/init.d /etc/rc.d/init.d

cd /lib64
ln -s /lib/x86_64-linux-gnu/libgcc_s.so.1 .


echo 'Red Hat Linux release 5' > /etc/redhat-release


-- 2. Oracleソフトウェアインストール

linux.x64_11gR2_database_1of2.zip
linux.x64_11gR2_database_2of2.zip


cd /home/oracle
chmod 777 linux.x64_11gR2_database_?of2.zip
chown oracle:oinstall linux.x64_11gR2_database_?of2.zip

 

oracleユーザでログインして実行

vim ~/.profile

export TMP=/tmp
export TMPDIR=$TMP
export ORACLE_HOSTNAME=mmm146
export ORACLE_UNQNAME=orcl
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export ORACLE_SID=orcl
export PATH=$ORACLE_HOME/bin:/usr/sbin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/lib64
export CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
umask 022

. ~/.profile


cd /home/oracle


unzip linux.x64_11gR2_database_1of2.zip
unzip linux.x64_11gR2_database_2of2.zip


cd database
LANG=C ./runInstaller


★ins_ctx.mkのエラー発生。参考サイトに従って対処してretry実行
rootユーザで実行

vim /u01/app/oracle/product/11.2.0/dbhome_1/ctx/lib/ins_ctx.mk

    $(LINK_CTXHX) $(CTXHXOBJ) $(INSO_LINK)

    -static $(LINK_CTXHX) $(CTXHXOBJ) $(INSO_LINK) /usr/lib64/stdc.a


★ins_emagent.mkのエラー発生。参考サイトに従って対処してretry実行
oracleユーザで実行

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

sed -i 's/^\(\s*\$(MK_EMAGENT_NMECTL)\)\s*$/\1 -lnnz11/g' $ORACLE_HOME/sysman/lib/ins_emagent.mk

 

★ins_rdbms.mkのエラー発生。参考サイトに従って対処してretry実行
oracleユーザで実行

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

cd $ORACLE_HOME/lib

ln -s libclient11.a libagtsh.a

$ORACLE_HOME/bin/genagtsh $ORACLE_HOME/lib/libagtsh.so 1.0


sed -i 's/^\(\$LD \$LD_RUNTIME\) \(\$LD_OPT\)/\1 -Wl,--no-as-needed \2/g' $ORACLE_HOME/bin/genorasdksh

sed -i 's/^\(\s*\)\(\$(OCRLIBS_DEFAULT)\)/\1 -Wl,--no-as-needed \2/g' $ORACLE_HOME/srvm/lib/ins_srvm.mk

sed -i 's/^\(TNSLSNR_LINKLINE.*\$(TNSLSNR_OFILES)\) \(\$(LINKTTLIBS)\)/\1 -Wl,--no-as-needed \2/g' $ORACLE_HOME/network/lib/env_network.mk

sed -i 's/^\(ORACLE_LINKLINE.*\$(ORACLE_LINKER)\) \(\$(PL_FLAGS)\)/\1 -Wl,--no-as-needed \2/g' $ORACLE_HOME/rdbms/lib/env_rdbms.mk


-- 3. DB作成

LANG=C dbca


-- 4. リスナー作成

LANG=C netca

lsnrctl status

-- 5. tnsnames.ora作成

oracleユーザでviが「symbol lookup error」により使用できなくなっているため、rootユーザで実行

vim /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora

orcl =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = mmm146)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

chown oracle:oinstall /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora


-- 6. DB作成後設定作業

-- 6.1 ユーザー作成
create user test identified by test;
grant dba to test;

-- 6.2 DBプロファイル修正
set lines 1000
set pages 5000
select * from dba_profiles where profile='DEFAULT';
alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited;
alter profile default limit PASSWORD_LIFE_TIME unlimited;
alter profile default limit PASSWORD_LOCK_TIME unlimited;
alter profile default limit PASSWORD_GRACE_TIME unlimited;
select * from dba_profiles where profile='DEFAULT';


-- 6.3 glogin.sql修正
vim /u01/app/oracle/product/11.2.0/dbhome_1/sqlplus/admin/glogin.sql

set lines 1000
set pages 5000
set trims on
set sqlprompt "&_connect_identifier(&_user)> "


-- 6.4 alias設定

vim ~oracle/.bashrc
alias cdh='cd $ORACLE_HOME'
alias cdb='cd $ORACLE_BASE'
alias sql='sqlplus / as sysdba'

. ~/.bashrc

 

 

(8)
https://qiita.com/rltm/items/4a49dfc6703f4fcc812a


DB : 19c

Debianのversionが9や10の場合、glibc関連のエラーでOracleソフトウェアがインストールできなかった

 

-- 1. OS設定

apt install \
build-essential \
binutils \
libcap-dev \
gcc \
g++ \
libc6-dev \
ksh \
libaio-dev \
make \
libxi-dev \
libxtst-dev \
libxau-dev \
libxcb1-dev \
sysstat \
rpm \
xauth

vim /etc/sysctl.conf

kernel.sem = 250 32000 100 128
kernel.shmmni = 4096
fs.file-max = 6815744
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
fs.aio-max-nr = 1048576 

sysctl -p

groupadd -g 54321 oinstall
groupadd -g 54322 dba
groupadd -g 54323 oper
groupadd -g 54324 backupdba
groupadd -g 54325 dgdba
groupadd -g 54326 kmdba
groupadd -g 54327 racdba

adduser --home /home/oracle --uid 54321 --gid 54321 oracle
adduser oracle dba
adduser oracle oper
adduser oracle backupdba
adduser oracle dgdba
adduser oracle kmdba
adduser oracle racdba

mkdir -p /u01/app/oracle

mkdir /u01/app/oracle/oradata
mkdir /u01/app/oracle/fra
mkdir -p /u01/app/oracle/product/19.0.0/dbhome_1

chown -R oracle:oinstall /u01
chmod -R 775 /u01

vim /etc/security/limits.conf

oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536 
oracle  soft  stack   10240
oracle  hard  stack   32768

vim /home/oracle/.profile

export ORACLE_BASE=/u01/app/oracle
umask 022


ln -s /usr/bin/awk /bin/awk
ln -s /usr/bin/basename /bin/basename
ln -s /usr/bin/rpm /bin/rpm
ln -s /usr/lib/x86_64-linux-gnu /usr/lib64


-- 2. Oracleソフトウェアインストール

LINUX.X64_193000_db_home.zip をSCP転送

cd /tmp
chmod 777 LINUX.X64_193000_db_home.zip
chown oracle:oinstall LINUX.X64_193000_db_home.zip


oracleユーザでログインして実行
cd /tmp
unzip LINUX.X64_193000_db_home.zip -d /u01/app/oracle/product/19.0.0/dbhome_1
cd /u01/app/oracle/product/19.0.0/dbhome_1


export CV_ASSUME_DISTID=RHEL7

LANG=C ./runInstaller

 

-- 3. DB作成
-- 3.1  CDB作成


su - oracle

vim /home/oracle/.profile

export ORACLE_SID=orcl
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export NLS_LANG=Japanese_Japan.AL32UTF8
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin/:$PATH

. /home/oracle/.profile

mkdir -p /u01/app/oracle/oradata/orcl
mkdir -p /u01/app/oracle/oradata/orcl/pdbseed

vim $ORACLE_HOME/dbs/initorcl.ora
db_name='orcl'
memory_target=1G
db_domain='example.com'
control_files = ('/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl')
ENABLE_PLUGGABLE_DATABASE=true

sqlplus / as sysdba


startup nomount;

CREATE DATABASE orcl
  USER SYS IDENTIFIED BY oracle
  USER SYSTEM IDENTIFIED BY oracle
  LOGFILE GROUP 1 ('/u01/app/oracle/oradata/orcl/redo01a.log','/u01/app/oracle/oradata/orcl/redo01b.log') 
             SIZE 100M BLOCKSIZE 512,
          GROUP 2 ('/u01/app/oracle/oradata/orcl/redo02a.log','/u01/app/oracle/oradata/orcl/redo02b.log') 
             SIZE 100M BLOCKSIZE 512,
          GROUP 3 ('/u01/app/oracle/oradata/orcl/redo03a.log','/u01/app/oracle/oradata/orcl/redo03b.log') 
             SIZE 100M BLOCKSIZE 512
  MAXLOGHISTORY 1
  MAXLOGFILES 16
  MAXLOGMEMBERS 3
  MAXDATAFILES 1024
  CHARACTER SET AL32UTF8
  NATIONAL CHARACTER SET AL16UTF16
  EXTENT MANAGEMENT LOCAL
  DATAFILE '/u01/app/oracle/oradata/orcl/system01.dbf'
    SIZE 100M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
  SYSAUX DATAFILE '/u01/app/oracle/oradata/orcl/sysaux01.dbf'
    SIZE 100M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
  DEFAULT TABLESPACE users
     DATAFILE '/u01/app/oracle/oradata/orcl/users01.dbf'
     SIZE 100M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
  DEFAULT TEMPORARY TABLESPACE temp
     TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf'
     SIZE 100M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
  UNDO TABLESPACE undo
     DATAFILE '/u01/app/oracle/oradata/orcl/undo01.dbf'
     SIZE 100M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
  ENABLE PLUGGABLE DATABASE
    SEED
    FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/orcl/', 
                         '/u01/app/oracle/oradata/orcl/pdbseed/')
    SYSTEM DATAFILES SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
    SYSAUX DATAFILES SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
  USER_DATA TABLESPACE usertbs
    DATAFILE '/u01/app/oracle/oradata/orcl/pdbseed/usertbs01.dbf'
    SIZE 100M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

 

@?/rdbms/admin/catcdb.sql

 

@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/rdbms/admin/utlrp.sql
conn system/oracle
@?/sqlplus/admin/pupbld.sql

conn / as sysdba
create spfile from pfile;

shutdown immediate;
startup;


-- 3.2 PDB作成
set lines 1000
set pages 5000

show con_name
select * from V$PDBS;
select * from cdb_pdbs;


CREATE PLUGGABLE DATABASE pdb1 ADMIN USER pdbadm IDENTIFIED BY oracle
FILE_NAME_CONVERT= ('/u01/app/oracle/oradata/orcl/pdbseed/', '/u01/app/oracle/oradata/orcl/pdb1/');

alter pluggable database pdb1 open;
alter pluggable database pdb1 save state;

-- 4. リスナー作成

LANG=C netca

lsnrctl status

-- 5. tnsnames.ora作成

vim $ORACLE_HOME/network/admin/tnsnames.ora

pdb1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = mmm153)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb1.example.com)
    )
  )

-- 6. DB作成後設定作業

-- 6.1 ユーザー作成
alter session set container = pdb1;
create user test identified by test;
grant dba to test;

-- 6.2 DBプロファイル修正
select * from dba_profiles where profile='DEFAULT';
alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited;
alter profile default limit PASSWORD_LIFE_TIME unlimited;
alter profile default limit PASSWORD_LOCK_TIME unlimited;
alter profile default limit PASSWORD_GRACE_TIME unlimited;
select * from dba_profiles where profile='DEFAULT';


-- 6.3 glogin.sql修正
vim $ORACLE_HOME/sqlplus/admin/glogin.sql

set lines 1000
set pages 5000
set trims on
set sqlprompt "&_connect_identifier(&_user)> "


-- 6.4 alias設定

vim ~/.bashrc
alias cdh='cd $ORACLE_HOME'
alias cdb='cd $ORACLE_BASE'
alias sql='sqlplus / as sysdba'

. ~/.bashrc

 

(7)


DB version : 19c

-- 1. OS設定
yum -y install \
binutils \
compat-libcap1 \
compat-libstdc++-33.i686 \
compat-libstdc++-33 \
glibc.i686 \
glibc \
glibc-devel.i686 \
glibc-devel \
ksh \
libaio.i686 \
libaio \
libaio-devel.i686 \
libaio-devel \
libgcc.i686 \
libgcc \
libstdc++.i686 \
libstdc++ \
libstdc++-devel.i686 \
libstdc++-devel \
libxcb.i686 \
libxcb \
libX11.i686 \
libX11 \
libXau.i686 \
libXau \
libXi.i686 \
libXi \
libXtst.i686 \
libXtst \
make \
net-tools \
nfs-utils \
smartmontools \
sysstat

yum install -y gcc-c++

vim /etc/sysctl.conf

kernel.sem = 250 32000 100 128
kernel.shmmni = 4096
fs.file-max = 6815744
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
fs.aio-max-nr = 1048576 

sysctl -p

groupadd -g 54321 oinstall
groupadd -g 54322 dba
groupadd -g 54323 oper
groupadd -g 54324 backupdba
groupadd -g 54325 dgdba
groupadd -g 54326 kmdba
groupadd -g 54327 racdba

useradd -u 54321 -g oinstall -G dba,oper,backupdba,dgdba,kmdba,racdba oracle


echo oracle | passwd --stdin oracle

mkdir -p /u01/app/oracle

mkdir /u01/app/oracle/oradata
mkdir /u01/app/oracle/fra
mkdir -p /u01/app/oracle/product/19.0.0/dbhome_1

chown -R oracle:oinstall /u01
chmod -R 775 /u01

vim /etc/pam.d/login
session required pam_limits.so

vim /etc/security/limits.conf

oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536 
oracle  soft  stack   10240
oracle  hard  stack   32768


vim /home/oracle/.bash_profile

export ORACLE_BASE=/u01/app/oracle
umask 022

vim /etc/default/grub

GRUB_CMDLINE_LINUXの項目に設定追記(transparent_hugepage=never)します

grub2-mkconfig -o /boot/grub2/grub.cfg
reboot

cat /sys/kernel/mm/transparent_hugepage/enabled

-- 2. Oracleソフトウェアインストール

LINUX.X64_193000_db_home.zip をSCP転送

cd /tmp
chmod 777 LINUX.X64_193000_db_home.zip
chown oracle:oinstall LINUX.X64_193000_db_home.zip

su - oracle
cd /tmp
unzip LINUX.X64_193000_db_home.zip -d /u01/app/oracle/product/19.0.0/dbhome_1
cd /u01/app/oracle/product/19.0.0/dbhome_1

vim /home/oracle/db.rsp

oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v19.0.0
oracle.install.option=INSTALL_DB_SWONLY
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/u01/app/oraInventory
ORACLE_BASE=/u01/app/oracle
oracle.install.db.InstallEdition=EE
oracle.install.db.OSDBA_GROUP=dba
oracle.install.db.OSOPER_GROUP=oper
oracle.install.db.OSBACKUPDBA_GROUP=backupdba
oracle.install.db.OSDGDBA_GROUP=dgdba
oracle.install.db.OSKMDBA_GROUP=kmdba
oracle.install.db.OSRACDBA_GROUP=racdba
oracle.install.db.rootconfig.executeRootScript=true
oracle.install.db.rootconfig.configMethod=ROOT
oracle.install.db.rootconfig.sudoPath=
oracle.install.db.rootconfig.sudoUserName=

LANG=C ./runInstaller -silent -noconfig -responseFile /home/oracle/db.rsp

exit


-- 3. DB作成
-- 3.1  CDB作成

vim /etc/fstab
tmpfs   /dev/shm        tmpfs   defaults,size=1200M     0 0

df -h
umount /dev/shm
mount /dev/shm
df -h

su - oracle

vim /home/oracle/.bash_profile

export ORACLE_SID=orcl
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export NLS_LANG=Japanese_Japan.AL32UTF8
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin/:$PATH

. /home/oracle/.bash_profile

mkdir -p /u01/app/oracle/oradata/orcl
mkdir -p /u01/app/oracle/oradata/orcl/pdbseed

vim $ORACLE_HOME/dbs/initorcl.ora
db_name='orcl'
memory_target=1G
db_domain='example.com'
control_files = ('/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl')
ENABLE_PLUGGABLE_DATABASE=true

sqlplus / as sysdba


startup nomount;

CREATE DATABASE orcl
  USER SYS IDENTIFIED BY oracle
  USER SYSTEM IDENTIFIED BY oracle
  LOGFILE GROUP 1 ('/u01/app/oracle/oradata/orcl/redo01a.log','/u01/app/oracle/oradata/orcl/redo01b.log') 
             SIZE 100M BLOCKSIZE 512,
          GROUP 2 ('/u01/app/oracle/oradata/orcl/redo02a.log','/u01/app/oracle/oradata/orcl/redo02b.log') 
             SIZE 100M BLOCKSIZE 512,
          GROUP 3 ('/u01/app/oracle/oradata/orcl/redo03a.log','/u01/app/oracle/oradata/orcl/redo03b.log') 
             SIZE 100M BLOCKSIZE 512
  MAXLOGHISTORY 1
  MAXLOGFILES 16
  MAXLOGMEMBERS 3
  MAXDATAFILES 1024
  CHARACTER SET AL32UTF8
  NATIONAL CHARACTER SET AL16UTF16
  EXTENT MANAGEMENT LOCAL
  DATAFILE '/u01/app/oracle/oradata/orcl/system01.dbf'
    SIZE 100M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
  SYSAUX DATAFILE '/u01/app/oracle/oradata/orcl/sysaux01.dbf'
    SIZE 100M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
  DEFAULT TABLESPACE users
     DATAFILE '/u01/app/oracle/oradata/orcl/users01.dbf'
     SIZE 100M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
  DEFAULT TEMPORARY TABLESPACE temp
     TEMPFILE '/u01/app/oracle/oradata/orcl/temp01.dbf'
     SIZE 100M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
  UNDO TABLESPACE undo
     DATAFILE '/u01/app/oracle/oradata/orcl/undo01.dbf'
     SIZE 100M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
  ENABLE PLUGGABLE DATABASE
    SEED
    FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/orcl/', 
                         '/u01/app/oracle/oradata/orcl/pdbseed/')
    SYSTEM DATAFILES SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
    SYSAUX DATAFILES SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
  USER_DATA TABLESPACE usertbs
    DATAFILE '/u01/app/oracle/oradata/orcl/pdbseed/usertbs01.dbf'
    SIZE 100M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

 

@?/rdbms/admin/catcdb.sql

 

@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/rdbms/admin/utlrp.sql
conn system/oracle
@?/sqlplus/admin/pupbld.sql

conn / as sysdba
create spfile from pfile;

shutdown immediate;
startup;


-- 3.2 PDB作成
set lines 1000
set pages 5000

show con_name
select * from V$PDBS;
select * from cdb_pdbs;


CREATE PLUGGABLE DATABASE pdb1 ADMIN USER pdbadm IDENTIFIED BY oracle
FILE_NAME_CONVERT= ('/u01/app/oracle/oradata/orcl/pdbseed/', '/u01/app/oracle/oradata/orcl/pdb1/');

alter pluggable database pdb1 open;
alter pluggable database pdb1 save state;

-- 4. リスナー作成

cp $ORACLE_HOME/assistants/netca/netca.rsp /home/oracle

vim /home/oracle/netca.rsp

[GENERAL]
RESPONSEFILE_VERSION="19.0"
CREATE_TYPE="CUSTOM"

SHOW_GUI=false

[oracle.net.ca]
INSTALLED_COMPONENTS={"server","net8","javavm"}
INSTALL_TYPE="typical"

LISTENER_NUMBER=1
LISTENER_NAMES={"LISTENER"}
LISTENER_PROTOCOLS={"TCP;1521"}
LISTENER_START="LISTENER"
NAMING_METHODS={"TNSNAMES"}


LANG=C /u01/app/oracle/product/19.0.0/dbhome_1/bin/netca -silent -responsefile /home/oracle/netca.rsp

lsnrctl status

-- 5. tnsnames.ora作成

vim $ORACLE_HOME/network/admin/tnsnames.ora

pdb1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = mmm121)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb1.example.com)
    )
  )

 


-- 6. DB作成後設定作業

-- 6.1 ユーザー作成
alter session set container = pdb1;
create user test identified by test;
grant dba to test;

-- 6.2 DBプロファイル修正
select * from dba_profiles where profile='DEFAULT';
alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited;
alter profile default limit PASSWORD_LIFE_TIME unlimited;
alter profile default limit PASSWORD_LOCK_TIME unlimited;
alter profile default limit PASSWORD_GRACE_TIME unlimited;
select * from dba_profiles where profile='DEFAULT';


-- 6.3 glogin.sql修正
vim $ORACLE_HOME/sqlplus/admin/glogin.sql

set lines 1000
set pages 5000
set trims on
set sqlprompt "&_connect_identifier(&_user)> "


-- 6.4 alias設定

vim ~/.bashrc
alias cdh='cd $ORACLE_HOME'
alias cdb='cd $ORACLE_BASE'
alias sql='sqlplus / as sysdba'

. ~/.bashrc

 

 

(2019)
https://ai-can-fly.hateblo.jp/entry/oracle-database-19c-install
https://docs.oracle.com/cd/F19136_01/ntdbi/index.html
https://docs.oracle.com/cd/F19136_01/admin/creating-and-configuring-an-oracle-database.html#GUID-807DE711-C82C-4BB2-8C31-5EE89CA71349
https://hosopro.blogspot.com/2017/01/powershell-set-environment-variable.html


DB : 19c


-- 1. OS設定

mkdir C:\app\oracle\product\19.0.0\dbhome_1


-- 2. Oracleソフトウェアインストール

WINDOWS.X64_193000_db_home.zip をサーバへ転送
C:\app\oracle\product\19.0.0\dbhome_1 に展開

cd C:\app\oracle\product\19.0.0\dbhome_1


notepad C:\Users\Administrator\db.rsp

oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v19.0.0
oracle.install.option=INSTALL_DB_SWONLY
ORACLE_BASE=C:\app\oracle
oracle.install.db.InstallEdition=EE
oracle.install.db.config.starterdb.type=GENERAL_PURPOSE
oracle.install.db.ConfigureAsContainerDB=false
oracle.install.db.config.starterdb.memoryOption=false
oracle.install.db.config.starterdb.installExampleSchemas=false
oracle.install.db.config.starterdb.managementOption=DEFAULT
oracle.install.db.config.starterdb.omsPort=0
oracle.install.db.config.starterdb.enableRecovery=false
oracle.install.IsBuiltInAccount=false
oracle.install.IsVirtualAccount=true

 

.\setup.exe -silent -noconfig -responseFile C:\Users\Administrator\db.rsp


-- 3. DB作成
-- 3.1 dbcaでDB作成する場合

notepad C:\Users\Administrator\dbca.rsp

responseFileVersion=/oracle/assistants/rspfmt_dbca_response_schema_v12.2.0
gdbName=orcl
sid=orcl
databaseConfigType=SI
policyManaged=false
createServerPool=false
force=false
createAsContainerDatabase=true
numberOfPDBs=1
pdbName=pdb1
useLocalUndoForPDBs=true
templateName=C:\app\oracle\product\19.0.0\dbhome_1\assistants\dbca\templates\New_Database.dbt
sysPassword=oracle
systemPassword= oracle
pdbAdminPassword=oracle
emExpressPort=5500
runCVUChecks=FALSE
omsPort=0
dvConfiguration=false
olsConfiguration=false
datafileDestination={ORACLE_BASE}\oradata\{DB_UNIQUE_NAME}\
storageType=FS
characterSet=AL32UTF8
nationalCharacterSet=AL16UTF16
registerWithDirService=false
variables=ORACLE_BASE_HOME=C:\app\oracle\product\19.0.0\dbhome_1,DB_UNIQUE_NAME=orcl,ORACLE_BASE=C:\app\oracle,PDB_NAME=,DB_NAME=orcl,ORACLE_HOME=C:\app\oracle\product\19.0.0\dbhome_1,SID=orcl
initParams=undo_tablespace=UNDOTBS1,enable_pluggable_database=true,db_block_size=8192BYTES,nls_language=JAPANESE,dispatchers=(PROTOCOL=TCP) (SERVICE=orclXDB),diagnostic_dest={ORACLE_BASE},control_files=("{ORACLE_BASE}\oradata\{DB_UNIQUE_NAME}\control01.ctl", "{ORACLE_BASE}\oradata\{DB_UNIQUE_NAME}\control02.ctl"),remote_login_passwordfile=EXCLUSIVE,audit_file_dest={ORACLE_BASE}\admin\{DB_UNIQUE_NAME}\adump,processes=300,nls_territory=JAPAN,memory_target=1000MB,open_cursors=300,compatible=19.0.0,db_name=orcl,audit_trail=db
sampleSchema=false
memoryPercentage=40
databaseType=MULTIPURPOSE
automaticMemoryManagement=true
totalMemory=0


$env:NLS_LANG = "American_America.AL32UTF8"
$env:NLS_LANG

dbca -silent -createDatabase -responseFile C:\Users\Administrator\dbca.rsp

-- 3.2 create database文でDB作成する場合

$env:ORACLE_SID = "orcl"
[System.Environment]::SetEnvironmentVariable("ORACLE_SID", "orcl", "User")
$env:ORACLE_SID

$env:ORACLE_HOME = "C:\app\oracle\product\19.0.0\dbhome_1"
[System.Environment]::SetEnvironmentVariable("ORACLE_HOME", "C:\app\oracle\product\19.0.0\dbhome_1", "User")
$env:ORACLE_HOME

mkdir C:\app\oracle\oradata\orcl\pdbseed

cd C:\app\oracle\product\19.0.0\dbhome_1
notepad database\initorcl.ora

db_name='orcl'
memory_target=1G
control_files = ('C:\app\oracle\oradata\orcl\control01.ctl','C:\app\oracle\oradata\orcl\control02.ctl')
ENABLE_PLUGGABLE_DATABASE=true


oradim -NEW -SID orcl -STARTMODE MANUAL -PFILE C:\app\oracle\product\19.0.0\dbhome_1\database\initorcl.ora

$env:NLS_LANG = "American_America.AL32UTF8"
$env:NLS_LANG

$env:PATH = $env:PATH + ";C:\app\oracle\product\19.0.0\dbhome_1\perl\bin"
$env:PATH


sqlplus / as sysdba


startup nomount;

CREATE DATABASE orcl
  USER SYS IDENTIFIED BY oracle
  USER SYSTEM IDENTIFIED BY oracle
  LOGFILE GROUP 1 ('C:/app/oracle/oradata/orcl/redo01a.log','C:/app/oracle/oradata/orcl/redo01b.log') 
             SIZE 100M BLOCKSIZE 512,
          GROUP 2 ('C:/app/oracle/oradata/orcl/redo02a.log','C:/app/oracle/oradata/orcl/redo02b.log') 
             SIZE 100M BLOCKSIZE 512,
          GROUP 3 ('C:/app/oracle/oradata/orcl/redo03a.log','C:/app/oracle/oradata/orcl/redo03b.log') 
             SIZE 100M BLOCKSIZE 512
  MAXLOGHISTORY 1
  MAXLOGFILES 32
  MAXLOGMEMBERS 3
  MAXDATAFILES 1024
  CHARACTER SET AL32UTF8
  NATIONAL CHARACTER SET AL16UTF16
  EXTENT MANAGEMENT LOCAL
  DATAFILE 'C:/app/oracle/oradata/orcl/system01.dbf'
    SIZE 100M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
  SYSAUX DATAFILE 'C:/app/oracle/oradata/orcl/sysaux01.dbf'
    SIZE 100M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
  DEFAULT TABLESPACE users
     DATAFILE 'C:/app/oracle/oradata/orcl/users01.dbf'
     SIZE 100M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
  DEFAULT TEMPORARY TABLESPACE temp
     TEMPFILE 'C:/app/oracle/oradata/orcl/temp01.dbf'
     SIZE 100M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
  UNDO TABLESPACE undo
     DATAFILE 'C:/app/oracle/oradata/orcl/undo01.dbf'
     SIZE 100M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
  ENABLE PLUGGABLE DATABASE
    SEED
    FILE_NAME_CONVERT = ('C:/app/oracle/oradata/orcl/', 
                         'C:/app/oracle/oradata/orcl/pdbseed/')
    SYSTEM DATAFILES SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
    SYSAUX DATAFILES SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
  USER_DATA TABLESPACE usertbs
    DATAFILE 'C:/app/oracle/oradata/orcl/pdbseed/usertbs01.dbf'
    SIZE 100M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

 

@?/rdbms/admin/catcdb.sql

Enter value for 1: C:\Users\Administrator
Enter value for 2: a.log

@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/rdbms/admin/utlrp.sql
conn system/oracle
@?/sqlplus/admin/pupbld.sql

conn / as sysdba
create spfile from pfile;

shutdown immediate;
startup;

set lines 1000
set pages 5000

show con_name
select * from V$PDBS;
select * from cdb_pdbs;


CREATE PLUGGABLE DATABASE pdb1 ADMIN USER pdbadm IDENTIFIED BY oracle
FILE_NAME_CONVERT= ('C:\APP\ORACLE\ORADATA\ORCL\PDBSEED\', 'C:\APP\ORACLE\ORADATA\ORCL\PDB1\');


alter pluggable database pdb1 open;
alter pluggable database pdb1 save state;

ORADIM -EDIT -SID orcl -STARTMODE AUTO -SRVCSTART SYSTEM -SPFILE

 

-- 4. リスナー作成

notepad C:\Users\Administrator\netca.rsp

[GENERAL]
RESPONSEFILE_VERSION="19.0"
CREATE_TYPE="CUSTOM"

SHOW_GUI=false

[oracle.net.ca]
INSTALLED_COMPONENTS={"server","net8","javavm"}
INSTALL_TYPE="typical"

LISTENER_NUMBER=1
LISTENER_NAMES={"LISTENER"}
LISTENER_PROTOCOLS={"TCP;1521"}
LISTENER_START="LISTENER"
NAMING_METHODS={"TNSNAMES"}

$env:NLS_LANG = "American_America.AL32UTF8"
$env:NLS_LANG

netca -silent -responsefile C:\Users\Administrator\netca.rsp


lsnrctl status


-- 5. tnsnames.ora作成


notepad C:\app\oracle\product\19.0.0\dbhome_1\network\admin\tnsnames.ora


pdb1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = mmm061)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb1)
    )
  )


-- 6. DB作成後設定作業

-- 6.1 ユーザー作成

alter session set container = pdb1;
create user test identified by test;
grant dba to test;

-- 6.2 DBプロファイル修正
set lines 1000
set pages 5000
select * from dba_profiles where profile='DEFAULT';
alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited;
alter profile default limit PASSWORD_LIFE_TIME unlimited;
alter profile default limit PASSWORD_LOCK_TIME unlimited;
alter profile default limit PASSWORD_GRACE_TIME unlimited;
select * from dba_profiles where profile='DEFAULT';


-- 6.3 glogin.sql修正

notepad C:\app\oracle\product\19.0.0\dbhome_1\sqlplus\admin\glogin.sql

set lines 1000
set pages 5000
set trims on
set sqlprompt "&_connect_identifier(&_user)> "