dmpとかするシェルスクリプト

ディレクトリマウント

OSとORACLEをつなぐ。

コード表示

cd /mnt/18cr3/share/dump/ORCL/aine/in
cd /mnt/18cr3/share/dump/ORCL/aine/in/liz
cd /mnt/18cr3/share/dump/ORCL/aine/out
cd /mnt/18cr3/share/dump/ORCL/aine/out/liz
cd /mnt/18cr3/share/query
cd /mnt/18cr3/share/sh
--sys session
CREATE OR REPLACE DIRECTORY dp_out_aine_dir AS '/mnt/18cr3/share/dump/ORCL/aine/out';
GRANT READ ON DIRECTORY dp_out_aine_dir TO aine;
GRANT WRITE ON DIRECTORY dp_out_aine_dir TO aine;
CREATE OR REPLACE DIRECTORY dp_in_aine_dir AS '/mnt/18cr3/share/dump/ORCL/aine/in';
GRANT READ ON DIRECTORY dp_in_aine_dir TO aine;
GRANT WRITE ON DIRECTORY dp_in_aine_dir TO aine;

フォルダ構成

RACできたら、かえると思う

コード表示

[oracle@f285aba0589a mnt]$ pwd
/mnt
[oracle@f285aba0589a mnt]$ tree
.
|-- 11gr2
|   |-- init
|   |   |-- create_user.sql
|   |   `-- startup.sh
|   |-- oradata
|   `-- share
|       |-- dump
|       |   `-- xe
|       |       |-- aine
|       |       |   |-- in
|       |       |   `-- out
|       |       |-- kuraine
|       |       |   |-- in
|       |       |   `-- out
|       |       |-- mujiku
|       |       |   |-- in
|       |       |   `-- out
|       |       `-- nahato
|       |           |-- in
|       |           `-- out
|       |-- login
|       |-- query
|       |-- sh
|       `-- spool
|-- 12cr2
|   |-- init
|   |   |-- create_user.sql
|   |   `-- startup.sh
|   |-- oradata
|   `-- share
|       |-- dump
|       |   `-- ORCL
|       |       |-- aine
|       |       |   |-- in
|       |       |   `-- out
|       |       |-- kuraine
|       |       |   |-- in
|       |       |   `-- out
|       |       |-- mujiku
|       |       |   |-- in
|       |       |   `-- out
|       |       `-- nahato
|       |           |-- in
|       |           `-- out
|       |-- login
|       |-- query
|       |-- sh
|       `-- spool
`-- 18cr3
    |-- init
    |   |-- create_user.sql
    |   `-- startup.sh
    |-- oradata
    `-- share
        |-- dump
        |   `-- ORCL
        |       |-- aine
        |       |   |-- in
        |       |   |   |-- dup___test_01___.png
        |       |   |   |-- dup___test_02___.png
        |       |   |   |-- dup___test_03___.png
        |       |   |   |-- tbl___loader___.dat
        |       |   |   |-- tbl___loader___.log
        |       |   |   |-- tbl___loader____18130.log_xt
        |       |   |   `-- test.png
        |       |   `-- out
        |       |       |-- aine_user_scheduler_job_log_20190406111520.dmp
        |       |       |-- aine_user_scheduler_job_log_20190406111520.log
        |       |       |-- aine_user_scheduler_job_log_20190406111533.dmp
        |       |       |-- aine_user_scheduler_job_log_20190406111533.log
        |       |       |-- aine_user_scheduler_job_log_20190406111929.dmp
        |       |       |-- aine_user_scheduler_job_log_20190406111929.log
        |       |       |-- aine_user_scheduler_job_log_20190406113707.dmp
        |       |       |-- aine_user_scheduler_job_log_20190406113707.log
        |       |       |-- aine_user_scheduler_job_log_20190406114236.dmp
        |       |       |-- aine_user_scheduler_job_log_20190406114236.log
        |       |       |-- aine_user_scheduler_job_log_20190406115931.dmp
        |       |       |-- aine_user_scheduler_job_log_20190406115931.log
        |       |       `-- liz
        |       |           `-- tar_tbl
        |       |-- kuraine
        |       |   |-- in
        |       |   `-- out
        |       |-- mujiku
        |       |   |-- in
        |       |   `-- out
        |       `-- nahato
        |           |-- in
        |           `-- out
        |-- login
        |-- query
        |   `-- drop_create.sql
        |-- sh
        |   `-- exe.sh
        |-- spool
        |   `-- test.html
        `-- zip
            `-- v18c.zip

68 directories, 30 files

シェルスクリプト

雰囲気でつくった。imp、query、spoolの場合はまだ。bash再入門してるから、もっとスマートかきたいものだ。ひっさしぶりにかいた。

コード表示

#!/bin/bash

echo "enter username number."
echo "-->1:aine 2:kuraine 3:nahato 4:mujiku"
read USR_PAT
echo "enter password number."
echo "-->1:ORACLE_PWD"
read PWD_PAT
echo "enter database connect identifer number."
echo "-->1:@pdb1 2:@pdb2 3:@pdb3 4:@pdb4"
read DB_PAT
echo "enter target table liz file name number."
echo "-->1:tar_tbl"
read FNM_PAT
echo "enter version number."
echo "-->3:18cr3 2:12cr2 1:11gr2"
read VRN_PAT
echo "enter ORACLE_SID number."
echo "-->1:ORCL"
read SID_PAT
echo 'enter usage number'
echo '1:dump_out 2:dump_in 3:spool 4:query'
read USE_PAT

case ${USR_PAT} in
    1)
        USR=aine
        ;;
    2)
        USR=kuraine
        ;;
    3)
        USR=nahato
        ;;
    4)
        USR=mujiku
        ;;
    *)
        ;;
esac

case ${PWD_PAT} in
    1)
        PWD=ORACLE_PWD
        ;;
    *)
        ;;
esac

case ${DB_PAT} in
    1)
        DB=@pdb1
        ;;
    2)
        DB=@pdb2
        ;;
    3)
        DB=@pdb3
        ;;
    4)
        DB=@pdb4
        ;;
    *)
        ;;
esac

case ${FNM_PAT} in
    1)
        FNM=tar_tbl
        ;;
    *)
        ;;
esac

case ${VRN_PAT} in
    1)
        VRN=11gr2
        ;;
    2)
        VRN=12cr2
        ;;
    3)
        VRN=18cr3
        ;;
    *)
        ;;
esac

case ${SID_PAT} in
    1)
        SID=ORCL
        ;;
    *)
        ;;
esac

USC=_
SLH=/
PFX=tmp_

MNT_DIR=/mnt/${VRN}/share

LGN_INFO=${USR}${SLH}${PWD}${DB}
SYS_DATE=`date '+%Y%m%d%H%M%S'`
DP_EXT=.dmp
LOG_EXT=.log
QRY_DIR=${MNT_DIR}${SLH}query${SLH}

case ${USE_PAT} in
    1)
        DP_DIR=${MNT_DIR}${SLH}dump${SLH}${SID}${SLH}${USR}${SLH}out${SLH}
        LZ_DIR=${DP_DIR}liz${SLH}
        while read LN
        do
            sqlplus ${LGN_INFO} @${QRY_DIR}drop_create.sql ${PFX} ${LN} <<EOF
EOF
            LOG_NM=${USR}${USC}${LN}${USC}
            expdp ${LGN_INFO} DIRECTORY=dp_out_${USR}_dir DUMPFILE=${LOG_NM}${SYS_DATE}${DP_EXT} LOG=${LOG_NM}${SYS_DATE}${LOG_EXT} TABLES=${PFX}${LN}
        done < ${LZ_DIR}${FNM}
        ;;
    2)
        DP_DIR=${MNT_DIR}${SLH}dump${SLH}${SID}${SLH}${USR}${SLH}in${SLH}
        LZ_DIR=${DP_DIR}liz${SLH}
        ;;
    *)
        ;;
esac

sqlスクリプト

シェルスクリプトのなかで@${QRY_DIR}drop_create.sqlとかしてるやつ

コード表示

drop table &1&2;
create table &1&2 nologging parallel as select * from &2;

tar_tblリスト

エキスポート対象のテーブルリスト。

コード表示

user_scheduler_job_log

実行結果

tar_tblリストはすこし増やして4つにした。できてそう。


コード表示

[oracle@f285aba0589a out]$ pwd
/mnt/18cr3/share/dump/ORCL/aine/out
[oracle@f285aba0589a out]$ ll -lt | head -9
total 4224
-rw-r-----. 1 oracle oinstall 176128 Apr  7 17:51 aine_TEST___RCP____20190407175116.dmp
-rw-r--r--. 1 oracle oinstall   1440 Apr  7 17:51 aine_TEST___RCP____20190407175116.log
-rw-r-----. 1 oracle oinstall 176128 Apr  7 17:51 aine_TEST___MST____20190407175116.dmp
-rw-r--r--. 1 oracle oinstall   1440 Apr  7 17:51 aine_TEST___MST____20190407175116.log
-rw-r-----. 1 oracle oinstall 176128 Apr  7 17:51 aine_TEST___AVL____20190407175116.dmp
-rw-r--r--. 1 oracle oinstall   1440 Apr  7 17:51 aine_TEST___AVL____20190407175116.log
-rw-r--r--. 1 oracle oinstall   1440 Apr  7 17:51 aine_TEST___STF____20190407175116.log
-rw-r-----. 1 oracle oinstall 176128 Apr  7 17:51 aine_TEST___STF____20190407175116.dmp