PowerShell で RDS for Oracle へファイル転送（PUT_RAW）

データベースサーバへファイル転送なんて、普通にコピーすれば良いじゃんって思うのですが、相手が RDS for Oracle だとそうはいきません。今回は UTL_FILE.PUT_RAW を使ってデータベースサーバへファイル転送してみます。

はじめに

UTL_FILE.PUT_RAWを使ったファイル転送は、AWSホワイトペーパーである「Strategies for Migrating Oracle Databases to AWS」の P.31 あたりから方法が記載されていますが、これ Perl なんですよね。

Perl がダメってわけではなく、Windows サーバを運用管理している側からすれば、管理ツールが色々な言語で書かれているのは好ましくありません。（と個人的には思います）

実行には ODP.NET managed driver を使いますので「PowerShell から ODP.NET managed driver を使って Oracle 接続する」を参考にしてください。

UTL_FILE.PUT_RAW でファイル転送

Oracle に対してファイル転送しますので、Oracle 側に接続するためのユーザ、ディレクトリオブジェクトに対する適切なアクセス権限等が必要です。今回はデフォルトで存在する DATA_PUMP_DIR を利用します。

最初に接続用のユーザを作成しますが、権限など本筋ではないので、DBA 権限与えてます、、、、

create user scott identified by tiger;
grant dba to scott;

前述のホワイトペーパーの Perl のコードを PowerShell で書き直します。エラー処理等、省略しているところもありますので、そのあたりはご容赦ください。転送するファイルを 8192 バイトずつ読み込みを転送を繰り返すという仕様です。このサイズは UTL_FILE.PUT_RAW に与えるサイズの上限に依存していて、最大サイズは 32767 バイトです。

$ErrorActionPreference = "STOP"

# Loading ODP.NET
[void][Reflection.Assembly]::LoadFile("D:\ODP.NET_Managed_ODAC12cR4\odp.net\managed\common\Oracle.ManagedDataAccess.dll")

# RDS インスタンス情報
$rds_port = 1521
$rds_host = "orcl.csbouk99kbra.ap-northeast-1.rds.amazonaws.com"

$rds_user = "scott"
$rds_pass = "tiger"
$rds_sid  = "ORCL"

$dirname  = "DATA_PUMP_DIR"

# 転送ファイル情報
$indir    = "D:\TEMP"
$fname    = "SCOTT12.DMP"
$chunk    = 8192 # UTL_FILE.PUT_RAW の最大サイズ=32767

# 接続文字列
$constring=("User Id={0};Password={1};Data Source={2}:{3}/{4}" -f $rds_user,$rds_pass,$rds_host,$rds_port,$rds_sid)

# SQL定義
$sql_global = "CREATE OR REPLACE PACKAGE PS_GLOBAL AS FH UTL_FILE.FILE_TYPE; END;";
$sql_open   = "BEGIN PS_GLOBAL.FH := UTL_FILE.FOPEN(:DIRNAME, :FNAME, 'WB', :CHUNK); END;";
$sql_write  = "BEGIN UTL_FILE.PUT_RAW(PS_GLOBAL.FH,:DATA, TRUE); END;";
$sql_close  = "BEGIN UTL_FILE.FCLOSE(PS_GLOBAL.FH);END;";


# 接続
$con = New-Object Oracle.ManagedDataAccess.Client.OracleConnection($constring)
$con.Open()

# グローバル変数定義
$cmd = New-Object Oracle.ManagedDataAccess.Client.OracleCommand($sql_global,$con)
[void]$cmd.ExecuteNonQuery()

# 転送先ファイルオープン
$cmd.CommandText = $sql_open
$cmd.Parameters.Clear()
[void]$cmd.Parameters.Add((New-Object Oracle.ManagedDataAccess.Client.OracleParameter "DIRNAME", $dirname))
[void]$cmd.Parameters.Add((New-Object Oracle.ManagedDataAccess.Client.OracleParameter "FNAME"  , $fname))
[void]$cmd.Parameters.Add((New-Object Oracle.ManagedDataAccess.Client.OracleParameter "CHUNK"  , $chunk))
[void]$cmd.ExecuteNonQuery()

# ファイル転送
$cmd.CommandText = $sql_write
$reader = [System.IO.File]::OpenRead((Join-Path $indir $fname))
$wbuf = New-Object byte[]( $chunk )
while( $true )
{
    # 対象ファイルを指定サイズ読み込み
    $size = $reader.Read($wbuf,0, $chunk)
    if ( $size -eq 0 )
    {
        break;
    }

    # 転送用データ設定
    $data = New-Object byte[]( $size )
    [array]::Copy($wbuf,0,$data,0,$size)

    # 転送
    $cmd.Parameters.Clear()
    [void]$cmd.Parameters.Add((New-Object Oracle.ManagedDataAccess.Client.OracleParameter "DATA", $data))
    [void]$cmd.ExecuteNonQuery()
}
$reader.Close()

# 転送先ファイルクローズ
$cmd.CommandText=$sql_close
$cmd.Parameters.Clear()
[void]$cmd.ExecuteNonQuery()

# 切断
$con.Close()

尚、このスクリプトを流すと、接続したスキーマに PS_GLOBAL というパッケージが作成されます。ローカルでファイルを一定サイズずつ読み込みながら転送を繰り返す関係で、ひとつの PL/SQL で閉じて実行することはできません。そのため、転送先のファイルハンドルを保持するためのグローバル変数が必要なのです。もし定常的に利用するのであれば、他利用者と競合しないように工夫する必要があります。パッケージ名にタイムスタンプを含めるとか、SYS_GUID パッケージでユニークな名称を作るとか。

転送先の DATA_PUMP_DIR 配下については以下の SQL で確認できます。

SELECT * FROM TABLE(RDSADMIN.RDS_FILE_UTIL.LISTDIR('DATA_PUMP_DIR'));

結果は以下のような感じです。

転送ファイル確認

おっと、こんな真夜中に実行した覚えはないのですが、、、（笑）どうやらタイムスタンプが UTC になっているようです。気づいてなかった、、、「Amazon RDS DB インスタンスのタイムゾーンを変更する方法を教えてください」にあるようにデフォルトは UTC だそうです。変更しなければいけませんね。これは別記事にします。

AWS,Windows,データベースOracle,PowerShell,RDS

Posted by takmaru