SQLServer2005を使ってみる・4 DBの移行

今回はこんな感じでやってみます。

移行元
Enterprise上AdventureWorks
移行先
Express上

方法1・BACKUP→RESTORE

バックアップコマンドはこんな感じ

osql -U sa -P password -S Enterprise
BACKUP DATABASE AdventureWorks
TO DISK='C:\backup\AdventureWorks.bak'
GO

んで、作成されたAdventureWorks.bakを移行先のサーバへ移動して

osql -U sa -P password -S Express
RESTORE DATABASE AdventureWorks
FROM DISK='C:\backup\AdventureWorks.bak'
GO

これで!と思ったら、エラー。メッセージを見ると、どうもディレクトリ構造が異なるせいらしい。
というのも、Express側には何度もインスタンス作成していたので^^;
(MSSQL.5まであった笑)
移行元と移行先のディレクトリ構造が同じなら上のコマンドでOKなはずです。

んで、ディレクトリ構造を変更するのにMOVE句を使用するのだけど、論理ファイル名と物理ファイル名の両方が必要になります。そこで、

RESTORE FILELISTONLY
FROM DISK='C:\backup\AdventureWorks.bak'
GO

と叩きました。これでLogicalNameとPhysicalName両方が表示されます。
論理ファイル名がAdventureWorks_DataとAdventureWorks_Log、物理ファイル名がAdventureWorks.mdfとAdventureWorks.ldfと分かったので、MOVE句を追加して再度RESTOREコマンドを叩いてみます。

RESTORE DATABASE AdventureWorks
FROM DISK='C:\backup\AdventureWorks.bak'
WITH MOVE 'AdventureWorks_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.5\MSSQL\Data\AdventureWorks.mdf'
, MOVE 'AdventureWorks_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL.5\MSSQL\Data\AdventureWorks.ldf'
GO

これでExpress側にAdventureWorksが移行されました。
ただし、データベースユーザーに関してはログインとの関連付けがSIDで行われる為、同じ名前のログイン名を用意しても関連付けが出来ません。

これ(Microsoft SQL Server 2005 Tips and Tips)

を見てもらうのが手っ取り早いのですが、掻い摘んで書いてみると

USE AdventureWorks
EXEC sp_change_users_login 'Report'
GO

で、ログインにマッピングされていないユーザーを洗い出し
(今回は自分で追加したtestuserがいました)

EXEC sp_change_users_login 'Update_One'
'tesruser','test_login'
GO

でユーザーを再マッピングできます。もちろん、この場合のログイン(test_login)はプロシージャ実行前に用意して下さい。

もしくは、ここ(上のTipsからもリンク有)にあるスクリプトを実行し

EXEC master..sp_help_revlogin
GO

と叩くと、インスタンスに存在するログインをそっくりそのまま作成するCREATE文が出力されるので、移行先でそのCREATE文を実行するとユーザーがマッピングされます。
全部のログインではなく、必要なログインのみ作成した方が良いと思います。というかDROPしないと全部は作成できないような。。。

CREATE文がいくつも出力されるので、ManagementStudioで実行するか、コマンドプロンプト使用の場合はファイルに書き出すようにしないと大変かもしれません。

SQL Server 7.0/2000ではsp_help_revloginはこれだったようです。
2005用のが7.0/2000でも使用出来るかは確認してないので、ご注意を。

sp_help_revloginでやっていることを手打ちで実行したいという人はここが非常に参考になると思います。


とまあ、こんな感じで移行出来ました。
一番最後に載せたリンクを見ると、

>通常は、DTSなどを使って、ログイン情報も転送します。

・・・DTSなんてシーラネ(お
スポンサーサイト

VirtualServer2005を使ってみる・1 インストール

データベースとかって一回入れるとアンインストールが面倒ですよね。特にOracleとかOracleとか。
なんで、検証用にVMwareを使っていたのですが、ことWindows上でならVirtualServerが使いやすそうだったので、使ってみました。
んで、インストール編です。

この辺(Microsoftダウンロードセンター)からVirtual Server 2005 R2 をダウンロードします。

インストールするマシンにIISが追加されていることを確認します。
確認方法はサービスで確認するか、「プログラムの追加と削除」の「Windowsコンポーネントの追加と削除」あたりで出来ます。
追加されていなければ、「Windowsコンポーネントの追加と削除」から追加しましょう。最低限WWWサービスがあれば良いようです。

IISが追加できたら、ダウンロードしたファイルからインストールしましょう。うん、それだけなんだけどね。
特に悩むところは無かった気がするので、インストールはこんなもんで。

そうそう、こんなページよりもこことか見ると良いと思う。

SQLServer2005:ログインとデータベースユーザー、スキーマ

これ、自分は理解に時間がかかってしまいました。
というのも、Oracleではユーザー=スキーマで、ログインもユーザー単位だったので、ログインと別にユーザーを用意して、さらにそこにスキーマを割り当てるという考え方に慣れなかったのです。
ネットを調べまくった後、本屋で立ち読みまでしましたよ(笑)

で、自分の理解した内容をメモ程度に。

・ログインは名前の通りログインのみを担当
・データへのアクセス権限等はユーザーが担当
・1ユーザーに対応付けられるデータベースは1つのみ
・データベースが異なれば同じユーザー名の作成は可能
・ログインに対してユーザーを対応付ける
・ログインに対応付け可能なユーザーは1データベースにつき1ユーザー

分かりにくいねorz
つまり

LOGIN_A
 →USER_A(←DB_A)
 →USER_A(←DB_B)
 →USER_B(←DB_C)
 ×USER_C(←DB_C)

という対応付けです。で、権限・ロールはユーザーに付与すると。
しかも、ユーザー名と異なるスキーマを対応付けることも出来ると。
便利そうではあるけど、管理がめんどくさそうな気もするなあ…

SQLServer2005を使ってみる・3 ユーザー作成・SQL発行

接続先SQLServerの環境
サーバー名:Express
インスタンス名:SQLEXPRESS


まずはユーザー作成…の前にログインの作成が必要になるようです。
なので、ログインを作成(もちろん、saユーザーかOS認証で)

osql -U sa -P password -S ExpressSQLEXPRESS
CREATE LOGIN oreno_login WITH PASSWORD='password'
GO

次に作成したログインに紐付けたデータベースユーザーを作成

CREATE USER ore FOR LOGIN oreno_login WITH DEFAULT_SCHEMA=Person
GO

これで、oreno_loginでDBにアクセスが出来る様になります。

osql -U oreno_login -P password -S ExpressSQLEXPRESS -d AdventureWorks

ただし、権限が全くないので表の参照も出来ません。
というわけで、次は権限を与えます。(saで入りなおして下さい)

osql -U sa -P password -S ExpressSQLEXPRESS -d AdventureWorks
GRANT SELECT ON Person.Address TO ore
GO

これで再度確認

osql -U oreno_login -P password -S ExpressSQLEXPRESS -d AdventureWork
SELECT * FROM Person.Address
GO

これで大量のデータが表示されるはずです。


特定のDBのみ、特定のスキーマのみにアクセスする場合は

osql -U sa -P password -S ExpressSQLEXPRESS
CREATE LOGIN oreno_login WITH PASSWORD='password',DEFAULT_DATABASE=AdventureWorks
CREATE USER ore FOR LOGIN oreno_login WITH DEFAULT_SCHEMA=Person
USE AdventureWorks
GRANT SELECT ON Person.Address TO ore
GO

と指定して作成すると良いかと思います。そうすることで

osql -U oreno_login -P password -S ExpressSQLEXPRESS
SELECT * FROM Address
GO

のみで検索結果を表示出来るようになります。ALTER LOGINとか叩いて指定しなおしても同じことが出来ますが。


今回作成したログインはSQL Server認証を使用していますが、

CREATE LOGIN hoge FROM WINDOWS

でWindows認証のログインが作成できるようです。試してないけど。
一応Oracle使いの端くれな自分としてはSQL Server認証の方がどうしても使いやすいのですよ。分かりやすいし。
ActiveDirectryとか勉強するとWindows認証も使うようになるかしら?

SQLServer2005を使ってみる・2 接続してみる・SQL発行

インスタンス名と接続文字列の部分でちょっと悩んでしまいました。
EnterpriseとExpress両方使っていたのですが、Enterprise(というかExpress以外)だとデフォルトのインスタンス名が「MSSQLSERVER」なのでDBアクセス時の接続先がサーバー名のみでの接続

osql -U sa -P password -S SERVER_NAME

だったのですが、Expressだと「SQLEXPRESS」がデフォルトの為接続先にサーバー名インスタンス名を指定

osql -U sa -P password -S SERVER_NAMESQLEXPRESS

とする必要があったのです。
で、なんでこの差があるんだろうと悩んであれこれ試した結果、「MSSQLSERVER」かそれ以外か、というところに気づいたのです。マニュアル読まないのが悪いのですが。


VirtualServerを利用してEnterprise用とExpress用にサーバーを組んだ(会社が与えてくれる検証用サーバーなのでハイパワーなのです!)ので、じゃあネットワーク越しにもアクセスしてみるか!と思い、お互いからお互いに接続実験。

[SQL Native Client]SQL ネットワークインターフェイス:サーバーは要求されたプロトコルをサポートしていません
[xFFFFFFFF].
[SQL Native Client]ログイン タイムアウトが時間切れになりました
[SQL Native Client]サーバーへの接続確立時にエラーが発生しました。接続先が SQL Server 2005 である場合は、既定の設定では
SQL Server がリモート接続を許可していないことが原因である可能性があります

つまり、デフォルトだとリモート接続が許可されていないと。
調べてみると、TCP/IPを有効にしてやれば良いとの事。

SQLサーバー構成マネージャ(SQL Server Configration Manager)でTCP/IPを有効にしてサービスを再起動したところ、無事つながるようになりました!


今回、ちょっと強引ですがEnterpriseについてくるサンプルDB(AdventureWorks)をバックアップ→Express側で復元としてどちらにもAdventureWorksが存在する状態にしましたので、SQLを発行してみる。
(Express用のAdventureWorksってダウンロード出来たのね、後で知った)

use AdventureWorks
select * from Person.Address
go

ををを!なんかいっぱい出てきた!!
とりあえずは無事成功のようです。


今回のポイント

・インスタンス名をMSSQLSERVERにするとサーバー名のみでアクセス可、ローカルだと接続文字列なしで繋がる
それ以外のインスタンス名だとサーバー名インスタンス名を指定して接続

・外部から接続する場合は、SQLサーバー構成マネージャでTCP/IPを有効にしてから

SQLServer2005を使ってみる・1 インストール

今回自分の学習用に使用するのは、SQL Server 2005 Enterprise Edition(評価版)とSQL Server 2005 Express Edition with Advanced Servicesの二つです。

だって、業務でどっち使うか分かんなかったんだもん。

ちなみにOSはWindows 2003 Server Enterprise Edition(評価版)


で、まずはインストールです。

Enterpriseは評価版のダウンロードに時間がかかるくらいでインストールは非常に簡単でした。
手順1:OSにIISを入れてなかったのでIIS追加
手順2:ディスクの自動実行→インストール
実質これだけ。
評価版ということ以外はまるまる製品版と同じなので、インストールに必要な
.NET Framework 2.0
.NET Framework 2.0 - 言語パッケージ
Microsoft SQL Native Client
Microsoft SQL Server セットアップ サポート ファイル
のインストールも自動で行ってくれます。


Expressは本体のみなので、.NET Framework 2.0は別途インストールが必要になります。
いちいち落としてくるのが面倒だったので、今回はEnterpriseのインストールディスクを起動して、.NET Framework 2.0のインストールまで進めてキャンセルしました。(一旦画面が切り替わるので、その後キャンセル可なのです)
このやり方っていいのかどうかちょっと微妙な気もしますが…^^;

まあ、これだけでインストールは出来るので導入は簡単だと思います。
DBの最低限の機能はデフォルトのままでインストールされますし。

あ、デフォルトのままだとManagementStudioがインストールされないので、それだけは追加しておいたほうが良いと思います。
GUIで管理出来るというのはなんだかんだで便利ですからね

そういえば、IISのなかで必要なコンポーネントってどれだったんだろう?
IISのデフォルトで追加したら問題なかったので気にすること無いのかもしれませんが、最低限しか入れたくない!という方は調べてみると良いかもしれません

【トラブル】DBが起動しない!

【トラブル】OS起動時にDBが起動しない
に状況はそっくりなのですが、
sqlplus /nolog
connect sys/password as sysdba
でアイドルインスタンスに接続しstartupを叩いてもエラーが!

現場にいたわけではないのでエラー番号等はメモ出来ませんでしたが、ロールバックセグメントがどうこうといったことらしい・・・

とりあえずの処置として、initORCL.oraファイルのロールバックセグメントの指定部分を一旦削除することで起動させました。


んで、手元に残してあった導入時のinitORCL.oraを見ると

rollback_segment =(rbs1,rbs2,rbs3)

とあります。
分かる人が見れば一発ですが、これが原因ですね。。。

正しくは

rollback_segments =(rbs1,rbs2,rbs3)

です。「s」一つにやられました。。。
皆さんもスペルミスにはくれぐれもご注意を。。。

【エラー】トリガーやシノニムのインポート時にエラー

自分がこのエラーを見た状況
Oracleバージョン:8.1.7.0.0
OS:Windows2000Server

症状:
>IMP XXX/YYY@ZZZ FILE=oraora.dmp FROMUSER=ORE TOSER=OMAE
としてインポートした際、シノニムが正しく作成されなかったり、トリガーインポート時にコンパイルエラーが発生する。

原因:
オブジェクトのインポート時にスキーマの移動は出来ても、オブジェクト内のスキーマ名までは変更されないのが原因です。
シノニム・トリガーに限らずオブジェクト全てで起こりうるそうです。

対応:
シノニム・トリガーとも頑張って作り直してください(ぉ

一応、シノニムに関しては誤ったシノニムの削除と再作成を行うスクリプトを発行するSELECT文置いときます。

SPOOL C:\DROP_CREATE_SYNONYM.sql
SELECT
 'DROP SYNONYM '||SYNONYM_NAME||';'
 FROM USER_SYNONYMS
;
SELECT
 'CREATE SYNONYM '||SYNONYM_NAME||' FOR OMAE.'||TABLE_NAME||';'
 FROM USER_SYNONYMS
;
SPOOL OFF


トリガーに関しては、本体がLONG型の為SELECT文での出力が出来ません。
パッケージを用いた方法もあるようですが、自分はトリガー名と本体部を別々にファイルに書き出して、必死こいてコンパイル出来る形に整形してました。

※本体部を全行表示する為に!
 LONG型はデフォルトだと20文字(だったかな?)分しか出力されません。
 そこで、
 SET LINESIZE 10000
 SET LONG 20000
 をSPOOLの前に実行しました。
 
 但し、これだと改行コードが入ってしまう場合があるので、整形が大変でした。
 SET LONGCHUNKSIZE 1000
 とかもやると大丈夫なようです。検証してないけど^^;
 
 トリガー関係では、もっと楽なやり方が有りそうなので、要研究ですね。

【エラー】ORA-04031:共有メモリのnバイトを割り当てできません

自分がこのエラーを見た状況
・Oracleバージョン:8.1.7.0.0
・OS:Windows2000Server
・エクスポート時に発生

このエラーは、共有メモリが足りなかったり断片化してしまうことにより、連続したメモリ空間が確保出来ない場合に発生する模様。

対応1:
以下のSQLコマンドにて共有メモリをクリアしてから再実行

ALTER SYSTEM FLUSH SHARED_POOL;

これでエラーが回避されればベスト…なのかな?
細かく様子を見るのであれば、共有メモリの空き容量をチェックする必要もあるかと思います。ので、SQL置いときます。

SELECT POOL,NAME,BYTES FROM V$SGASTAT WHERE NAME='free memory' AND POOL='shared pool';

これを共有メモリのクリア前とクリア後に実行すると様子が分かりやすいかと。


対応2:
SHARED_POOL_SIZE初期化パラメータを大きくする

この対応しようにも物理メモリのサイズには限界があるので、出来る場合と出来ない場合があります。


対応番外編:今回のエクスポート時に取った手段
対応1では解決出来ず、メモリをいっぱいに使っていたため対応2は実施すら出来ずだったので、今回はユーザーごとにエクスポートを行いました。
というか、データ自体を持ってるのは1ユーザーだけだったので、1人分だけエクスポート。

作業自体を細かく分けられるのであれば、分けて実行してみるのも手かも知れません。

【トラブル】OS起動時にDBが起動しない

自分がこのトラブルにあった状況
Oracleバージョン:8.1.7.0.0
OS:Windows2000Server

症状:
・定期メンテナンスでサーバーを再起動したところ、DBが起動しなくなった
・今まで何度も再起動していたが、その時は問題なかった
・Windows上でのサービスは起動している
・アラートファイル(BACKGROUND_DUMP_DESTで指定)には記述なし
・OraDim.LOG(デフォルトでは%ORACLE_HOME%/database/)には
 ORA-12640: 認証アダプタの初期化に失敗しました。
 ORA-12638: 資格証明の取出しに失敗しました。
 の二種類の記述有り
 ※ORA-12640は大量に、ORA-12638はサーバー再起動の時間に発生した模様
・サーバーの再起動していたらなんか動き始めた!

このトラブルは、認証まわりが原因のようです。
sqlnet.oraファイル(%ORACLE_HOME%/network/admin/)にて

SQLNET.AUTHENTICATION_SERVICES=(NTS)

となっていて、OSへのログインをドメインユーザーで行った場合に起こりうる問題だそうです。
SQLNET.AUTHENTICATION_SERVICES=(NTS)はWindowsNT固有のOS認証を使用するという設定です。
このとき、通常であれば

 OS起動→Windowsログイン→Oracleサービス起動→認証→DB起動

となるのですが、ドメインユーザーを使用していると、稀に

 OS起動→Windowsログインに時間がかかる→→→→→→→→↓
   ↓→Oracleサービス起動→認証したいが間に合わない→→DB起動してない!

となる場合があるようです。
この場合、OS認証が出来ずに止まっているだけなので、SQL*Plus等からSTARTUPコマンドを実行してあげれば起動します。

対応としては、

・発生する度に手動でSTARTUPコマンド
・SQLNET.AUTHENTICATION_SERVICES=(NONE)として認証なしに設定
・Oracleサービスの開始を手動にし、Windows起動後に
 net start OracleServiceORCL
 srvctl start ORCL
 と記述したバッチファイルを実行するようにタスクを組む

の三パターンになるんじゃないかと。
毎回STARTUPは面倒なので、まずは認証無しに設定し、それでも引っかかることがあるようならバッチファイルの実行タスクを設定すればいいのかな。

【エラー】IMP-00020:LONG列は列バッファ・サイズ(n)に対して大きすぎます。

自分がこのエラーを見た状況
・Oracleバージョン:8.1.7.0.0
・OS:Windows2000Server
・データのインポート時に発生
・該当テーブルにLONG型の列は存在しない

このエラー、どうやらインポートよりもエクスポートの問題の様です。

EXP XXX/YYY@ZZZ DIRECT=Y FILE=oraora.dmp OWNER=ORE

といった感じでエクスポートしていたのですが、該当テーブルの1レコード当たりのサイズが大きすぎて、インポート時のSQLがデフォルトのバッファサイズ(4KB)に収まらなかったようです。
ここで自分の取った手段

EXP XXX/YYY@ZZZ DIRECT=Y BUFFER=200000000 FILE=oraora.dmp OWNER=ORE

これでもエラー。当然です。
BUFFERオプションは従来型パス(指定なし、もしくはDIRECT=Nを指定)を使用した場合のバッファサイズの変更オプションなのですから。
ダイレクトパスロードを使用した場合(DIRECT=Yを指定)のバッファサイズの変更はRECORDLENGTHオプションになります。

この為、今回の正解としては

EXP XXX/YYY@ZZZ DIRECT=Y RECORDLENGTH=200000000 FILE=oraora.dmp OWNER=ORE
EXP XXX/YYY@ZZZ DIRECT=N BUFFER=200000000 FILE=oraora.dmp OWNER=ORE
EXP XXX/YYY@ZZZ BUFFER=200000000 FILE=oraora.dmp OWNER=ORE

この三種類になります。三種類のうち上二種類は実際にExp/Imp確認しました。
動かしてみた限りでは、インポートコマンドには最小限のオプションだけでいいようです。

IMP XXX/YYY@ZZZ FILE=oraora.dmp FROMUSER=ORE TOUSER=OMAE

これでDIRECTオプションの有無に関わらずインポート出来ました。


※ORACLEによると、バッファサイズが大きすぎても同じような問題が発生する為、入力バッファサイズを段階的に上げて下さい、となっております。一応、ご注意を。

再開しちゃってみる

えらい久しぶりの更新になります。ラキアです。
ずっとmixiに書いてたもんで、こっちは放置だったのですよ。

mixiにも書いたのですが、こっちをSEとして出会ったトラブルとその対応等を書き留めていく自分の知識データベースにしようかなと思ったわけです。

暫定的にこんな安易なタイトルですが、いいタイトルを思いついた方は連絡下さい。

あ、もちろんゲームのことも相変わらず書きますよ。RO再開したし。

HOME

プロフィール

ラキア

  • Author:ラキア
  • ビリヤードB級、JPA SL5
    ホームは新宿BAGUSと荻窪サンビリ

    プレイ :McDarmott+OBPro+
    ブレイク:Adam+OBSBS+
    ジャンプ:TENGU

最近の記事

カテゴリー

月別アーカイブ

最近のコメント

最近のトラックバック

ブログ内検索

RSSフィード

リンク