この記事は約27分26秒で読むことができます。

19cr3のoracleで初期環境構築スクリプト作ろうかなって思った話

ディレクトリ構成

ffで作業

コード表示

[oracle@centos ff]$ [ -e init.sql -a -e output/* -a -e seq/* ] && rm init.sql output/* seq/*
[oracle@centos ff]$ tree
.
├── output
├── seq
├── sh
│   ├── def_order.sh
│   ├── exec.sh
│   └── replace.sh
├── src
│   └── user
└── tmpl
    ├── create_or_replace_directory
    ├── create_user
    ├── drop_user
    ├── grant_read_on_directory
    ├── grant_role_or_priviledge
    ├── grant_tablespace
    └── grant_write_on_directory

5 directories, 11 files

tmpl/*作成

テンプレ作成

コード表示

[oracle@centos ff]$ ls tmpl/* | xargs -t -I@ bash -c 'cat @'
bash -c cat tmpl/create_or_replace_directory 
CREATE OR REPLACE DIRECTORY @_in_dir AS '/mnt/@/in';
CREATE OR REPLACE DIRECTORY @_out_dir AS '/mnt/@/out';
bash -c cat tmpl/create_user 
CREATE USER @ IDENTIFIED BY "ORACLE_PWD" DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE temp;
bash -c cat tmpl/drop_user 
DROP USER @;
bash -c cat tmpl/grant_read_on_directory 
GRANT READ ON DIRECTORY @_in_dir TO @;
GRANT READ ON DIRECTORY @_out_dir TO @;
bash -c cat tmpl/grant_role_or_priviledge 
GRANT dba TO @;
bash -c cat tmpl/grant_tablespace 
GRANT UNLIMITED TABLESPACE TO @;
bash -c cat tmpl/grant_write_on_directory 
GRANT WRITE ON DIRECTORY @_in_dir TO @;
GRANT WRITE ON DIRECTORY @_out_dir TO @;

sh/*作成

忘れないように先に実行権限を付与しておこう

コード表示

[oracle@centos ff]$ find $(pwd) -name "*sh" | xargs -I@ bash -c 'chmod a+x @'
[oracle@centos ff]$ ll *sh
合計 12
-rwxr-xr-x. 1 oracle docker 164  6月  3 21:38 def_order.sh
-rwxr-xr-x. 1 oracle docker  83  6月  3 20:02 exec.sh
-rwxr-xr-x. 1 oracle docker 282  6月  3 21:37 replace.sh
[oracle@centos ff]$ find $(pwd) -name "*sh" | xargs -t -I@ bash -c 'cat @'
bash -c cat /home/oracle/ff/sh 
cat: /home/oracle/ff/sh: ディレクトリです
bash -c cat /home/oracle/ff/sh/exec.sh 
#!/bin/bash
while read line; do
  echo ${line} | bash;
done< <(cat $(pwd)/seq/exec_order)>$(pwd)/init.sql
bash -c cat /home/oracle/ff/sh/replace.sh 
#!/bin/bash
while read line1;do
  [ -e $(pwd)/output/$(basename ${line1}) ] && rm $(pwd)/output/$(basename ${line1});
  cat src/user | while read line2; do
    cat ${line1} | sed -e s/@/${line2}/g >> $(pwd)/output/$(basename ${line1});
  done
done < <(find $(pwd)/tmpl/* -name "*")
bash -c cat /home/oracle/ff/sh/def_order.sh 
#!/bin/bash
find $(pwd)/tmpl/* -name "*" | xargs -I@ bash -c 'echo [ -e $(pwd)/output/$(basename @) ] \&\& cat $(pwd)/output/$(basename @)' > $(pwd)/seq/exec_order

src/*作成

コード表示

[oracle@centos ff]$ cat src/*
aine
kuraine
nahato
mujiku

replace.sh→def_order.shの順に実行

コード表示

[oracle@centos ff]$ ./sh/replace.sh && ./sh/def_order.sh
[oracle@centos ff]$ tree
.
├── output
│   ├── create_or_replace_directory
│   ├── create_user
│   ├── drop_user
│   ├── grant_read_on_directory
│   ├── grant_role_or_priviledge
│   ├── grant_tablespace
│   └── grant_write_on_directory
├── seq
│   └── exec_order
├── sh
│   ├── def_order.sh
│   ├── exec.sh
│   └── replace.sh
├── src
│   └── user
└── tmpl
    ├── create_or_replace_directory
    ├── create_user
    ├── drop_user
    ├── grant_read_on_directory
    ├── grant_role_or_priviledge
    ├── grant_tablespace
    └── grant_write_on_directory

5 directories, 19 files
[oracle@centos ff]$ ls {output,seq}/* | xargs -t -I@ bash -c 'cat @'
bash -c cat output/create_or_replace_directory 
CREATE OR REPLACE DIRECTORY aine_in_dir AS '/mnt/aine/in';
CREATE OR REPLACE DIRECTORY aine_out_dir AS '/mnt/aine/out';
CREATE OR REPLACE DIRECTORY kuraine_in_dir AS '/mnt/kuraine/in';
CREATE OR REPLACE DIRECTORY kuraine_out_dir AS '/mnt/kuraine/out';
CREATE OR REPLACE DIRECTORY nahato_in_dir AS '/mnt/nahato/in';
CREATE OR REPLACE DIRECTORY nahato_out_dir AS '/mnt/nahato/out';
CREATE OR REPLACE DIRECTORY mujiku_in_dir AS '/mnt/mujiku/in';
CREATE OR REPLACE DIRECTORY mujiku_out_dir AS '/mnt/mujiku/out';
bash -c cat output/create_user 
CREATE USER aine IDENTIFIED BY "ORACLE_PWD" DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE temp;
CREATE USER kuraine IDENTIFIED BY "ORACLE_PWD" DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE temp;
CREATE USER nahato IDENTIFIED BY "ORACLE_PWD" DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE temp;
CREATE USER mujiku IDENTIFIED BY "ORACLE_PWD" DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE temp;
bash -c cat output/drop_user 
DROP USER aine;
DROP USER kuraine;
DROP USER nahato;
DROP USER mujiku;
bash -c cat output/grant_read_on_directory 
GRANT READ ON DIRECTORY aine_in_dir TO aine;
GRANT READ ON DIRECTORY aine_out_dir TO aine;
GRANT READ ON DIRECTORY kuraine_in_dir TO kuraine;
GRANT READ ON DIRECTORY kuraine_out_dir TO kuraine;
GRANT READ ON DIRECTORY nahato_in_dir TO nahato;
GRANT READ ON DIRECTORY nahato_out_dir TO nahato;
GRANT READ ON DIRECTORY mujiku_in_dir TO mujiku;
GRANT READ ON DIRECTORY mujiku_out_dir TO mujiku;
bash -c cat output/grant_role_or_priviledge 
GRANT dba TO aine;
GRANT dba TO kuraine;
GRANT dba TO nahato;
GRANT dba TO mujiku;
bash -c cat output/grant_tablespace 
GRANT UNLIMITED TABLESPACE TO aine;
GRANT UNLIMITED TABLESPACE TO kuraine;
GRANT UNLIMITED TABLESPACE TO nahato;
GRANT UNLIMITED TABLESPACE TO mujiku;
bash -c cat output/grant_write_on_directory 
GRANT WRITE ON DIRECTORY aine_in_dir TO aine;
GRANT WRITE ON DIRECTORY aine_out_dir TO aine;
GRANT WRITE ON DIRECTORY kuraine_in_dir TO kuraine;
GRANT WRITE ON DIRECTORY kuraine_out_dir TO kuraine;
GRANT WRITE ON DIRECTORY nahato_in_dir TO nahato;
GRANT WRITE ON DIRECTORY nahato_out_dir TO nahato;
GRANT WRITE ON DIRECTORY mujiku_in_dir TO mujiku;
GRANT WRITE ON DIRECTORY mujiku_out_dir TO mujiku;
bash -c cat seq/exec_order 
[ -e /home/oracle/ff/output/create_or_replace_directory ] && cat /home/oracle/ff/output/create_or_replace_directory
[ -e /home/oracle/ff/output/create_user ] && cat /home/oracle/ff/output/create_user
[ -e /home/oracle/ff/output/drop_user ] && cat /home/oracle/ff/output/drop_user
[ -e /home/oracle/ff/output/grant_read_on_directory ] && cat /home/oracle/ff/output/grant_read_on_directory
[ -e /home/oracle/ff/output/grant_role_or_priviledge ] && cat /home/oracle/ff/output/grant_role_or_priviledge
[ -e /home/oracle/ff/output/grant_tablespace ] && cat /home/oracle/ff/output/grant_tablespace
[ -e /home/oracle/ff/output/grant_write_on_directory ] && cat /home/oracle/ff/output/grant_write_on_directory

exec_orderよしなに直す

コード表示

[oracle@centos ff]$ vi seq/exec_order
[oracle@centos ff]$ cat seq/exec_order
[ -e /home/oracle/ff/output/drop_user ] && cat /home/oracle/ff/output/drop_user
[ -e /home/oracle/ff/output/create_user ] && cat /home/oracle/ff/output/create_user
[ -e /home/oracle/ff/output/grant_role_or_priviledge ] && cat /home/oracle/ff/output/grant_role_or_priviledge
[ -e /home/oracle/ff/output/grant_tablespace ] && cat /home/oracle/ff/output/grant_tablespace
[ -e /home/oracle/ff/output/create_or_replace_directory ] && cat /home/oracle/ff/output/create_or_replace_directory
[ -e /home/oracle/ff/output/grant_read_on_directory ] && cat /home/oracle/ff/output/grant_read_on_directory
[ -e /home/oracle/ff/output/grant_write_on_directory ] && cat /home/oracle/ff/output/grant_write_on_directory

exec.sh実行

コード表示

[oracle@centos ff]$ ./sh/exec.sh
[oracle@centos ff]$ tree
.
├── init.sql
├── output
│   ├── create_or_replace_directory
│   ├── create_user
│   ├── drop_user
│   ├── grant_read_on_directory
│   ├── grant_role_or_priviledge
│   ├── grant_tablespace
│   └── grant_write_on_directory
├── seq
│   └── exec_order
├── sh
│   ├── def_order.sh
│   ├── exec.sh
│   └── replace.sh
├── src
│   └── user
└── tmpl
    ├── create_or_replace_directory
    ├── create_user
    ├── drop_user
    ├── grant_read_on_directory
    ├── grant_role_or_priviledge
    ├── grant_tablespace
    └── grant_write_on_directory

5 directories, 20 files
[oracle@centos ff]$ cat init.sql
DROP USER aine;
DROP USER kuraine;
DROP USER nahato;
DROP USER mujiku;
CREATE USER aine IDENTIFIED BY "ORACLE_PWD" DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE temp;
CREATE USER kuraine IDENTIFIED BY "ORACLE_PWD" DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE temp;
CREATE USER nahato IDENTIFIED BY "ORACLE_PWD" DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE temp;
CREATE USER mujiku IDENTIFIED BY "ORACLE_PWD" DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE temp;
GRANT dba TO aine;
GRANT dba TO kuraine;
GRANT dba TO nahato;
GRANT dba TO mujiku;
GRANT UNLIMITED TABLESPACE TO aine;
GRANT UNLIMITED TABLESPACE TO kuraine;
GRANT UNLIMITED TABLESPACE TO nahato;
GRANT UNLIMITED TABLESPACE TO mujiku;
CREATE OR REPLACE DIRECTORY aine_in_dir AS '/mnt/aine/in';
CREATE OR REPLACE DIRECTORY aine_out_dir AS '/mnt/aine/out';
CREATE OR REPLACE DIRECTORY kuraine_in_dir AS '/mnt/kuraine/in';
CREATE OR REPLACE DIRECTORY kuraine_out_dir AS '/mnt/kuraine/out';
CREATE OR REPLACE DIRECTORY nahato_in_dir AS '/mnt/nahato/in';
CREATE OR REPLACE DIRECTORY nahato_out_dir AS '/mnt/nahato/out';
CREATE OR REPLACE DIRECTORY mujiku_in_dir AS '/mnt/mujiku/in';
CREATE OR REPLACE DIRECTORY mujiku_out_dir AS '/mnt/mujiku/out';
GRANT READ ON DIRECTORY aine_in_dir TO aine;
GRANT READ ON DIRECTORY aine_out_dir TO aine;
GRANT READ ON DIRECTORY kuraine_in_dir TO kuraine;
GRANT READ ON DIRECTORY kuraine_out_dir TO kuraine;
GRANT READ ON DIRECTORY nahato_in_dir TO nahato;
GRANT READ ON DIRECTORY nahato_out_dir TO nahato;
GRANT READ ON DIRECTORY mujiku_in_dir TO mujiku;
GRANT READ ON DIRECTORY mujiku_out_dir TO mujiku;
GRANT WRITE ON DIRECTORY aine_in_dir TO aine;
GRANT WRITE ON DIRECTORY aine_out_dir TO aine;
GRANT WRITE ON DIRECTORY kuraine_in_dir TO kuraine;
GRANT WRITE ON DIRECTORY kuraine_out_dir TO kuraine;
GRANT WRITE ON DIRECTORY nahato_in_dir TO nahato;
GRANT WRITE ON DIRECTORY nahato_out_dir TO nahato;
GRANT WRITE ON DIRECTORY mujiku_in_dir TO mujiku;
GRANT WRITE ON DIRECTORY mujiku_out_dir TO mujiku;

sqldeveloperから実行

ぉぉぉ。うまくいったぜ。

Leave a Reply

Your email address will not be published. Required fields are marked *