PSI Labs RSS feed

Oracle:expdpもexpコマンドも使えない場合の日々のデータバックアップ方法(BLOB,CLOB含む場合も)

こんにちは。shintaniです。

Oracleで日々の運用でテーブル単位のバックアップを取る場合、expdpまたは expを使うことが多いと思います。
しかし expdpではDBサーバ上にしかファイルを出力できず、かつ expは暗号化表領域の場合に使えなかったりします。

これらの問題に引っかかり、expdpも expコマンドも使えないという場合のデータバックアップ方法について紹介します。

その方法とは

データをカンマ区切りで出力し、SQL*Loaderによって復元できるようにする。

です。

バックアップ処理では、SQL*Plus の spool コマンドで下記のSQLの結果を出力します。


SET ECHO OFF
SET TERMOUT OFF
SET VERIFY OFF
SET HEAD OFF
SET FEEDBACK OFF
SET PAGES 0
SET LINESIZE 32767
SET TRIMSPOOL ON

select
      '"' ||  DATA_NO
 || '","' ||  DATA_YYYYMMDD
 || '","' ||  DATA_COL2
 || '","' ||  DATA_COL3
 || '","' ||  DATA_COL4
 || '","' ||  TO_CHAR(INSERT_TIME ,  'YYYY/MM/DD HH24:MI:SS.FF')
 || '"'  as "ALL_COLS"
  from
    TEST_TABLE
  where
    DATA_YYYYMMDD = '&BACKUP_DATE'
order by
  DATA_NO
;

---------------------------------------

この結果は下記のようになります。

"00000000000001","20170901","0000000","000000000201","ABCDE001","2017/09/01 18:00:01.000001"
"00000000000002","20170901","0000000","000000000202","ABCDE002","2017/09/01 18:00:02.000002"
"00000000000003","20170901","0000000","000000000203","ABCDE003","2017/09/01 18:00:03.000003"
"00000000000004","20170901","0000000","000000000204","ABCDE004","2017/09/01 18:00:04.000004"

復元が必要になった場合、このファイルをSQL*Loaderで取り込みます。
(コントロールファイルの書式などは別途調べて下さい)


さて、この書き方ではBLOBやCLOBなどサイズが大きい列は出力できません。SQL*Plusでは表示が切れます。
この場合の手順を紹介します。
少し長くなるのでポイントとなるBLOB項目の部分だけをピックアップすると、下記の図のようなことをします。



WS000000.JPG

以下はBLOBの場合の手順です。CLOBの場合は文字型なので色々簡単になると思います。
※ Linux上でシェル実行する手順が含まれています。


【SQL その1】

SET ECHO OFF
SET TERMOUT OFF
SET VERIFY OFF
SET HEAD OFF
SET FEEDBACK OFF
SET PAGES 0
SET LINESIZE 32767
SET TRIMSPOOL ON



SELECT
      '"' ||  DATA_NO
 || '","' ||  DATA_YYYYMMDD
 || '","' ||  DATA_COL2
 || '","' ||  'DATA_COL3/' || DATA_NO || '.blob'
 || '"'  as "ALL_COLS"
  FROM
    TEST_TABLE_2
  where
    DATA_YYYYMMDD = '&BACKUP_DATE'
order by
  DATA_NO
;

【SQL その2】

SET ECHO OFF
SET TERMOUT OFF
SET VERIFY OFF
SET HEAD OFF
SET FEEDBACK OFF
SET PAGES 0
SET LINESIZE 32767
SET TRIMSPOOL ON

COLUMN BLOB0 FORMAT A2010
COLUMN BLOB1 FORMAT A2010
COLUMN BLOB2 FORMAT A2010
COLUMN BLOB3 FORMAT A2010
COLUMN BLOB4 FORMAT A2010
COLUMN BLOB5 FORMAT A2010
COLUMN BLOB6 FORMAT A2010
COLUMN BLOB7 FORMAT A2010
COLUMN BLOB8 FORMAT A2010
COLUMN BLOB9 FORMAT A2010

SELECT
     '"' ||  DATA_NO  || '",'     as "PK_COLS"
          ,rawtohex(dbms_lob.substr(DATA_COL3 , 1000,1)   )  as BLOB0
          ,rawtohex(dbms_lob.substr(DATA_COL3 , 1000,1001))  as BLOB1
          ,rawtohex(dbms_lob.substr(DATA_COL3 , 1000,2001))  as BLOB2
          ,rawtohex(dbms_lob.substr(DATA_COL3 , 1000,3001))  as BLOB3
          ,rawtohex(dbms_lob.substr(DATA_COL3 , 1000,4001))  as BLOB4
          ,rawtohex(dbms_lob.substr(DATA_COL3 , 1000,5001))  as BLOB5
          ,rawtohex(dbms_lob.substr(DATA_COL3 , 1000,6001))  as BLOB6
          ,rawtohex(dbms_lob.substr(DATA_COL3 , 1000,7001))  as BLOB7
          ,rawtohex(dbms_lob.substr(DATA_COL3 , 1000,8001))  as BLOB8
          ,rawtohex(dbms_lob.substr(DATA_COL3 , 1000,9001))  as BLOB9
  FROM
    TEST_TABLE_2
  where
    DATA_YYYYMMDD = '&BACKUP_DATE'
order by
  DATA_NO
;

それぞれの出力ファイルは下記のようになります。


【SQL1結果ファイル】

"00000000000001","20170901","0000000","DATA_COL3/00000000000001.blob
"00000000000002","20170901","0000000","DATA_COL3/00000000000002.blob
"00000000000003","20170901","0000000","DATA_COL3/00000000000003.blob
"00000000000004","20170901","0000000","DATA_COL3/00000000000004.blob


【SQL2結果ファイル】
"00000000000001", 330313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313030313031303130313031303130313031303130313031303130313031303130313031303031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130 30313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303031303130313031303130313031303130313031303130313031303130313031303031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313030313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130 3031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313030313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031
"00000000000002", 33031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031
"00000000000003", 33031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031



SQL1の結果ファイルでは「主キーの値と、列ディレクトリの下にある.blobのファイル名」という形式で出力されます
SQL2の結果ファイルでは「主キーの値と、BLOB項目のHEX表示(1000バイト区切り)」という形式で出力されます。


SQL1結果ファイルは特に編集の必要はありません。
SQL2結果ファイルについては、方法は何でもいいのでこのような形式のファイルに変更します。
(表示上は改行されていますが、実際は echo ごとに1行です)

echo 330313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313030313031303130313031303130313031303130313031303130313031303130313031303031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130 >> 00000000000001_HEX.blob
echo 330313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313030313031303130313031303130313031303130313031303130313031303130313031303031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130313031303130 >> 00000000000002_HEX.blob
(以下略)

※ 先頭にecho 付加
※ 主キーの値を末尾に持ってきて _HEX.blob を追記
※ BLOB項目の間の半角スペースを除去
※ ファイル名と BLOB項目の間に >> を追記


上記のファイルをLinux上に配置し、実行します
※大量にファイルが作成されるので、DATA_COL3 ディレクトリを作成し、その下に配置して実行して下さい。


作成されたファイルの一覧を出力し、それを元に、下記のコマンドを作成します。

xxd -r -p 00000000000001_HEX.txt > 00000000000001.blob
xxd -r -p 00000000000002_HEX.txt > 00000000000002.blob
xxd -r -p 00000000000003_HEX.txt > 00000000000003.blob



xxd コマンドは HEXファイルの内容をバイナリに変換してファイル出力するものです。
これにより blob項目のデータ内容を持ったファイルが作成されます。

この作成されたファイルと、メインとなるデータファイルを配置し、SQL*Loaderを実行します。

コントロールファイルは以下のようになります。


OPTIONS(ERRORS=1)
LOAD DATA
INFILE 'TEST_TABLE_2_20170901.csv'
REPLACE
PRESERVE BLANKS
INTO TABLE TEST_TABLE_2
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
  DATA_NO
 ,DATA_YYYYMMDD
 ,DATA_COL2
 ,FILENAME_DATA_COL3  FILLER  CHAR, DATA_COL3  LOBFILE( FILENAME_DATA_COL3 ) TERMINATED BY EOF
)

FILENAME_REQ の行で、ファイルパスとファイル名からロードするBLOBファイルを読み込みます。
これによりデータが復元されます。

途中、BLOB列のHEX形式ファイルの編集方法などは大きく端折りました。
皆さまの実行する環境での効率の良い方法を各自ご検討下さい。

ではでは。


※ なおこの方法は SQL*PlusのSPOOLで出力するという方法上、下記の条件を満たさないと使えません。

① LOB項目の最大長が決まっていること。
② LOB項目の出力行の長さが SQL*Plusの1行の最大長32767バイトを超えないこと。