SQLServer備忘2

●このエラーは、Visual Studio デバッガーがユーザーをデバッグ用データベース サーバーに登録できないときに発生します。 最も可能性の高い原因は、SQL Server のインスタンスに関して必要なアクセス許可がないことです。 sysadmin 固定サーバー ロールを持つログインを使用して Transact-SQL エディターをサーバーに接続するだけでは不十分です。Visual Studio ユーザーの Windows ログインも、SQL Server のそのインスタンス上で sysadmin 固定サーバー ロールのメンバーである必要があります。
https://msdn.microsoft.com/ja-jp/library/vstudio/ms241738(v=vs.100).aspx

 

●Transact-SQL のデバッグのセットアップ エラーとトラブルシューティング

 

●シノニムの生成
create table Owner.b (a int)
create synonym b for Owner.b;
drop synonym b

select * from b;

select * from b
select * from Owner.b

insert into b (a) values (1);
PUBLIC SYNONYMは、権限付与で実現？

 

●DB内の全テーブルの件数一覧
select distinct a.name, b.row_count 
from sys.tables a
left join sys.dm_db_partition_stats b
on a.object_id = b.object_id
order by 2 desc

 

●ROWNUMのような値を取得するには。
SELECT column1 FROM
(SELECT ROW_NUMBER() OVER (ORDER BY id) AS rownum,*
FROM logsize) AS t

 

●ログの正味量を見る
SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;

 

●ログの圧縮 (10MBを目標にしたログ圧縮)
DBCC SHRINKFILE(hogehoge_DB_Log,10);

 

●権限指定
USE <DB名>
GO
GRANT SELECT ON OBJECT::[DB名].[スキーマ名].[テーブル名] TO <ユーザ名>;
GO"

 

●ストアドの再作成

 

名前解決時に同じ名前のオブジェクトが存在していれば再作成不要

 

●SSMSでプロシジャデバッグする設定

 

（サーバー側）
・TCP ポート 135 を許可
・プログラム sqlservr.exe を許可
※IPSec 経由でネットワーク通信を行う場合、UDP ポート 4500 と UDP ポート 500 も許可する。

 

（クライアント側）
・TCP ポート 135 を許可
・プログラム ssms.exe を許可に追加

 

・FWありローカルDBで試行時には、ポートが開いていないと管理者アカウントでないとエラー。
SSMSを管理者として実行で解消。

・SQL Server ユーザにsysadmin 権限が必要。
・更にクライアントのWindowsログインユーザーにもsysadmin権限必要。

 

●主キーのあるテーブル、ないテーブル、全テーブル数の表示
select count(*) from sys.tables
where object_id IN (select parent_object_id from sys.objects 
where type_desc like 'PRIMARY_KEY_CONSTRAINT')
UNION ALL
select count(*) from sys.tables
where object_id NOT IN (select parent_object_id from sys.objects 
where type_desc like 'PRIMARY_KEY_CONSTRAINT')
UNION ALL
select count(*) from sys.tables

 

●パブリケーションの設定情報
USE <DB名>
EXEC sp_helppublication @publication = 'パブリケーション名'
GO

 

●アーティクルの設定情報
USE [AdventureWorks2008R2]
EXEC sp_helparticle
@publication = @publication;
GO

 

●SQLCMDで処理結果件数表示を出力しない
以下を発行する。
set nocount on

 

●セッションKILL
select * from sys.dm_exec_sessions ⇒セッション情報
select * from sys.dm_exec_requests

sp_who ⇒ これだと利用しているDB名が表示される。

コマンドプロンプトで、
kill プロセスID(セッションID)

 

--SSMSだけでkillするには--------------------------------------
sp_who

Execute('Kill ' + '78')
--'78'はspidを表す。
--------------------------------------------------------------

 

●SQL Server Profilerを利用するには、以下を発行する。
use master
grant alter trace to ユーザ名;

 

●リンクサーバ
use master
exec master.dbo.sp_addlinkedserver
@server = N'SERVER\INSTANCE',
@srvproduct = N'SQL Server'
go
exec master.dbo.sp_addlinkedsrvlogin 'DB',false,NULL,'sa','Password'

exec master.dbo.sp_droplinkedsrvlogin 'DB','SERVER\Administrator'
exec master.dbo.sp_dropserver
@server=N'DB'

select * from [DB].master.sys.sysservers;
select * from [DB].master.sys.sysdatabases;
select * from master.sys.sysservers;
select * from master.sys.sysdatabases;

 

◎以下で設定
use master
exec master.dbo.sp_addlinkedserver
@server = N'SERVER\INSTANCE',
@srvproduct = N'SQL Server'
go
exec master.dbo.sp_addlinkedsrvlogin @rmtsrvname ='SERVER\INSTANCE',
@useself='FALSE',@locallogin =NULL,@rmtuser = 'OwnerUser',@rmtpassword = 'OwnerUser'

 

◎以下で確認
select * from [DB].master.sys.sysservers;
select * from [DB].master.sys.sysdatabases;
select * from master.sys.sysservers;
select * from master.sys.sysdatabases;

 

◎DROP時に確認する内容
sp_helpremotelogin
sp_helpserver 

 

●対象DBのみオフラインにし、データファイルを移動した後、オンラインにする。

 

-- 以下で事前確認
SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'OwnerMasterDB');

 

use master
alter database OwnerMasterDB set offline;

ALTER DATABASE OwnerMasterDB MODIFY FILE ( NAME = OwnerMasterDB_Data, FILENAME = 'D:\Database\UserDB\OwnerMasterDB.Mdf' );

ALTER DATABASE OwnerMasterDB MODIFY FILE ( NAME = OwnerMasterDB_Log, FILENAME = 'D:\Database\UserDB\OwnerMasterDB.Ldf' );

use master
alter database OwnerMasterDB set online;

 

●サーバ名の変更。インスタンス名の変更はできないと思った方がよい。
SELECT @@SERVERNAME AS 'Server Name';
select * from sys.servers;

sp_dropserver 'WIN-MI2D9GIA9E9';
GO
sp_addserver 'SQLServer', local;
GO

SELECT @@SERVERNAME AS 'Server Name';
select * from sys.servers;

 

●レプリケーションで生成した際に裏で自動生成されるリンクサーバの データアクセスのプロパティをTRUEにすると、リンクサーバとして分散クエリが利用できる。

 

●デフォルト値があるカラムの変更
ALTER TABLE テーブル名 DROP CONSTRAINT DF_xxx_xxx ; --制約削除
ALTER TABLE テーブル名 ALTER COLUMN [フィールド名]INT NOT NULL ; --カラム変更
ALTER TABLE テーブル名 ADD DEFAULT 0 FOR [フィールド名] ; --デフォルト値設定

 

●カラムの追加、変更、削除例

 

カラムの追加：
ALTER TABLE <テーブル名> ADD <カラム名> <データ型> <NULL/NOT NULL> <DEFAULT> …; 
例) ALTER TABLE Owner.MASTER ADD TEST_COLUMN NVARCHAR(10) NOT NULL DEFAULT 0;
 
カラムの変更：
ALTER TABLE <テーブル名> DROP CONSTRAINT <制約名：この場合はDEFAULT>;
ALTER TABLE <テーブル名> ALTER COLUMN <カラム名> <データ型> <NULL/NOT NULL> ;
ALTER TABLE <テーブル名> ADD DEFAULT <デフォルト値> FOR <カラム名>;
例) ALTER TABLE Owner.MASTER DROP CONSTRAINT DF__WORK___TEST___25A5F845;
ALTER TABLE Owner.MASTER ALTER COLUMN TEST_COLUMN CHAR(10) ;
ALTER TABLE Owner.MASTER ADD DEFAULT 0 FOR TEST_COLUMN;

 

カラムの削除： 動作に支障がなければ、利用しない方向で調整することを推奨。
ALTER TABLE <テーブル名> DROP CONSTRAINT <制約名：この場合はDEFAULT>;
ALTER TABLE <テーブル名> DROP COLUMN <カラム名> ;
例) ALTER TABLE Owner.MASTER DROP CONSTRAINT DF__WORK___TEST___2B5ED19B;
ALTER TABLE Owner.MASTER DROP COLUMN TEST_COLUMN;

 

●同じパスワード／SID のログインアカウントの作成： sp_help_revlogin
sp_change_users_loginによるログインアカウントの再マッピングは、データベース ユーザーの数が多い場合には、大変な作業になります。そこで、同じパスワード／SIDのログインアカウントを一括で作成できる方法があります。これは、sp_help_revlogin という名前のストアドプロシージャで、マイクロソフトのサポート技術情報（KB：Knowledge Base）の文書番号 918992 で提供されています。
KB918992：ログインとパスワードを SQL Server 2005 のインスタンスの間、転送する方法 この文書は、SQL Server 2005 用ですが、SQL Server 2008 にもそのまま適用可能です。この文書で提供されるスクリプトを丸ごとコピーして、Management Studioのクエリ エディタに貼り付けて実行すれば、sp_help_revlogin ストアドプロシージャを作成することができます。 作成後、次のように実行すれば、同一のパスワード／SIDのログインアカウントを作成するためのスクリプトが生成されます。 
USE master EXEC sp_help_revlogin

 

●主キー変更例
alter table dbo.ACCOUNTING drop constraint ACCOUNTING_PKC;
alter table dbo.ACCOUNTING alter column YEAR char(4) NOT NULL;
alter table dbo.ACCOUNTING add constraint ACCOUNTING_PKC 
PRIMARY KEY CLUSTERED (CD,SLIP_NO,SLIP_LINE_NO,YEAR);

 

● オブジェクトの統計情報確認。_WAは列ごとの統計
SELECT OBJECT_NAME(s.object_id) AS object_name,
COL_NAME(sc.object_id, sc.column_id) AS column_name,
s.name AS statistics_name
FROM sys.stats AS s Join sys.stats_columns AS sc
ON s.stats_id = sc.stats_id AND s.object_id = sc.object_id
WHERE s.name like '_WA%'
ORDER BY s.name;

 

● 統計の自動更新の設定確認
SELECT name AS "Name", 
is_auto_create_stats_on AS "Auto Create Stats",
is_auto_update_stats_on AS "Auto Update Stats",
is_read_only AS "Read Only" 
FROM sys.databases
WHERE database_ID > 4;

 

● 統計の非同期自動更新の設定確認
SELECT name AS "Name", 
is_auto_update_stats_async_on AS "Asynchronous Update" 
FROM sys.databases;

 

●オブジェクトごとの統計情報取得
DBCC SHOW_STATISTICS

 

●Current Worker と Acitive Worker

 

Current Worker の値から、各スケジューラーに割り当てられている、ワーカースレッド数を確認した。 
ワーカースレッドの状態はいくつかあるのだが、今回は Active Worker についてみていく。

 

Current Worker はスケジューラーに割り当てられているワーカースレッドの数になるのだが、これは割り当てられている状態を示しており、実際に使われているワーカースレッドということではない。
SELECT 
(SELECT SUM(current_workers_count) FROM sys.dm_os_schedulers) AS [CurrentWorker] 
, (SELECT SUM(active_workers_count) FROM sys.dm_os_schedulers) AS [Active Worker] 
, (SELECT COUNT(*) FROM sys.dm_os_threads) AS [OS Thread (Include Non SQL Server)] 
, (SELECT COUNT(*) FROM sys.dm_os_threads 
WHERE started_by_sqlservr = 1 AND scheduler_address IS NOT NULL) 
AS [OS Thread (Exclude Non SQL Server)]

 

●データコレクションはEE？でないと使えない。⇒ウソ。SEでも使える

 

●オブジェクト権限を確認する
USE データベース名
SELECT
OBJECT_NAME(major_id) AS オブジェクト名
,USER_NAME(grantee_principal_id) AS ユーザー名
,permission_name, state_desc
FROM sys.database_permissions
WHERE major_id = OBJECT_ID('オブジェクト名')

 

●2008ではALTER ROLE、ALTER SERVER ROLEでメンバーを追加できない。
sp_addrolemember、sp_addsrvrolemember を利用する。

●システム定義のメッセージを確認
select * from sys.messages

 

●データコレクタ系ログ
syscollector_collection_items 
syscollector_collection_sets 
syscollector_collector_types 
syscollector_config_store 
syscollector_execution_log 
syscollector_execution_log_full 
syscollector_execution_stats 

 

●データファイルとログファイルの容量
SELECT file_id, name, type_desc, physical_name, size, max_size
FROM sys.database_files ;
GO

 

●テーブル毎の容量
-- 領域情報の更新
EXEC sp_spaceused @updateusage = N'TRUE';
GO
--
DECLARE @table sysname
DECLARE @T1 Table (
name sysname
,rows bigint
,reserved varchar(10)
,data varchar(10)
,index_size varchar(10)
,unused varchar(10))

--カーソルの定義
DECLARE C CURSOR LOCAL FOR select s.name+'.'+t.name 
from sys.schemas s,sys.tables t
where s.schema_id = t.schema_id 
OPEN C
FETCH NEXT FROM C INTO @table --１件読む
WHILE (@@FETCH_STATUS=0)
BEGIN
--テーブル情報のストアド結果を一時テーブルに放り込む
INSERT INTO @T1 EXEC sp_spaceused @table
FETCH NEXT FROM C INTO @table --１件読む
END
CLOSE C
DEALLOCATE C

--結果を表示する
SELECT * FROM @T1
ORDER BY name

 

●断片化状況を確認する。
SELECT name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(N'DBname'), 
NULL, NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id; 
GO

 

●アローケーションユニットごとの情報
select * from sys.allocation_units

 

●Windows認証で別ユーザでログインする場合
コマンドプロンプトを別のユーザで実行した上で以下を発行
sqlcmd -S SERVER -E DOMAIN\user

 

●SQL Serverユーザでログインする場合
sqlcmd -S SERVER -U User -P Password

 

sys.dm_exec_sessions v$session?
sys.dm_exec_requests
sys.dm_exec_connections
sys.dm_exec_query_stats
sys.dm_exec_procedure_stats

sys.dm_db_file_space_usage ファイルに関する使用領域(tempDBのみ)
sys.dm_db_session_space_usage セッション毎のページ割り当て(tempDBのみ)
sys.dm_db_partition_stats データベースのパーティションごとに、ページ数と行数の情報
sys.dm_io_pending_io_requests SQL Server で保留中の I/O 要求ごとに 1 行のデータを返します。
sys.dm_io_virtual_file_stats(NULL,NULL) データとログ ファイルの I/O 統計を返します。

 

●平均 CPU 時間に基づく上位 5 つのクエリに関する情報を取得する
SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],
SUBSTRING(st.text, (qs.statement_start_offset/2)+1, 
*1 * 2) 
ELSE statement_end_offset END) - statement_start_offset) / 2+1)) AS sql_statement,
execution_count, 
plan_generation_num, 
last_execution_time, 
total_worker_time, 
last_worker_time, 
min_worker_time, 
max_worker_time,
total_physical_reads, 
last_physical_reads, 
min_physical_reads, 
max_physical_reads, 
total_logical_writes, 
last_logical_writes, 
min_logical_writes, 
max_logical_writes 
FROM sys.dm_exec_query_stats AS s1 
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 
WHERE s2.objectid is null 
ORDER BY s1.sql_handle, s1.statement_start_offset, s1.statement_end_offset;

 

●A. サーバーに接続しているユーザーを検索する
次の例では、サーバーに接続しているユーザーを検索し、各ユーザーのセッション数を返します。
SELECT login_name ,COUNT(session_id) AS session_count 
FROM sys.dm_exec_sessions 
GROUP BY login_name;

 

●B. 長時間実行されているカーソルを検索する
次の例では、特定の期間よりも長く開いているカーソル、カーソルの作成者、およびカーソルが配置されているセッションを検索。
USE master;
GO
SELECT creation_time ,cursor_id 
,name ,c.session_id ,login_name 
FROM sys.dm_exec_cursors(0) AS c 
JOIN sys.dm_exec_sessions AS s 
ON c.session_id = s.session_id 
WHERE DATEDIFF(mi, c.creation_time, GETDATE()) > 5;

 

●C. トランザクションが開いているアイドル状態のセッションを検索する
次の例では、トランザクションが開いているアイドル状態のセッションを検索します。アイドル状態のセッションとは、現在要求が実行されていないセッションです。
SELECT s.* 
FROM sys.dm_exec_sessions AS s
WHERE EXISTS 
(
SELECT * 
FROM sys.dm_tran_session_transactions AS t
WHERE t.session_id = s.session_id
)
AND NOT EXISTS 
(
SELECT * 
FROM sys.dm_exec_requests AS r
WHERE r.session_id = s.session_id
);

*1:CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY total_worker_time/execution_count DESC;

 

●バッチ実行の統計を取得する
次の例では、バッチで実行されている SQL クエリのテキストを返し、クエリに関する統計情報を提供。
SELECT s2.dbid, 
s1.sql_handle, 
(SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 , 
( (CASE WHEN statement_end_offset = -1 
THEN (LEN(CONVERT(nvarchar(max),s2.text

SQLServer備忘1

●テーブル情報参照： select * from sys.tables
●ログイン情報の取得： select * from syslogins
●カラム名の取得： select * from syscolumns
●テーブル名の取得： select * from sys.tables
●スキーマ一覧： select * from sys.schemas
●認証を無効にするには： ALTER LOGIN DISABLE
●DBファイル： sys.database_files
●テーブル名の変更: exec sp_rename 'Owner.MASTER', 'MASTER_BAK';
●主キーの変更: exec sp_rename 'Owner.MASTER_PKC','MASTER_PKC_BAK';

 

●対象テーブルのカラム名：
select name from sys.all_columns where object_id =
(select object_id from sys.all_objects where name = '<対象テーブル名>')
order by column_id
●スキーマの生成
CREATE SCHEMA スキーマ名 AUTHORIZATION <スキーマ所有者？>
●データベース ファイル名の変更方法
use <DB名>
ALTER DATABASE <DB名> MODIFY FILE 
( NAME = <論理名>, 
FILENAME = 'D:\Database\UserDB\xxxx_201509.mdf' );
※パス名は、変更後のパス名
●SqlConnection.ConnectionString の指定方法例(Windows統合認証の例)
Server=MSSQL1;Database=AdventureWorks;Integrated Security=true;
●dboがデフォルトスキーマになってしまう点。
ユーザにsysadmin権限を与えていると、自動的にdboで接続される。
それ以外は既定のスキーマが有効になる。
dboは消せない。

●権限チェック
SQL Server は最初にオブジェクトの所有者と、呼び出し元オブジェクト 
(つまり、チェーンにおける直前のリンク) の所有者とを比較します。
オブジェクトの所有者がどちらも同じであれば、参照されているオブジェクトに
対する権限はチェックされません。 あるオブジェクトが、所有者の
異なる別のオブジェクトにアクセスする場合、所有権の継承が途切れるため、
必ず呼び出し元のセキュリティ コンテキストが SQL Server によってチェックされる。

●DBのrename
SSMSで可能。ただし、データベース ファイル名はそのままなので、変更が必要。

◎DBリストア時にデータベースファイルを別ファイル名にするには、
[復元オプション]で設定可能。
https://msdn.microsoft.com/ja-jp/library/ms186390(v=sql.105).aspx

●管理者専用接続(この設定はローカルのみ。リモートを許可する時は1)
sp_configure 'remote admin connections', 0;
go
reconfigure
go
●設定値確認
exec sp_configure
●ロックエスカレーションの状態確認
DBCC tracestatus(1211,-1)
◎SQL Serverを使用しているサービスのパスワード変更時
https://msdn.microsoft.com/ja-jp/library/ms365941(v=sql.105).aspx

 

●データベース ファイル名の変更方法
use <DB名>
ALTER DATABASE <DB名> MODIFY FILE ( NAME = <論理名>, FILENAME = '<DBファイル名>' );
●リストア後、不明なユーザの対処方法
・不明なユーザー一覧を表示する方法
USE データベース名
EXEC sp_change_users_login 'Report'
・ユーザーマッピングの修復方法
USE データベース名
EXEC sp_change_users_login 'Update_One', 'test', 'test'
(EXEC sp_change_users_login 'Update_One', '現在のDBに存在するユーザー名', 'SQL Serverログイン名') 
●別のユーザでWindows認証でログインしたい場合、以下で行う。
Shiftキーを押しながら、SSMSを起動する。ログインダイアログボックスが
表示されるので、実行したいログインユーザで認証をする。

 

管理者で起動する場合も、Shiftキーを押しながらSSMSを起動することができる。

 

●NamedPipeで接続するには

 

np:サーバー名

 

SQL Server Management Studio と sqlcmd ユーティリティは、リモートの SQL Server に対して TCP/IP で接続試行し、接続できない場合は名前付きパイプの接続試行に切り替える。

 

Windows では、TCP/IP での応答の待ち時間 (タイムアウト) は初回 3 秒、再試行回数は 2 回。
再試行時のタイムアウトは前回の倍の秒数となるため、TCP/IP での接続試行により SQL Server に接続できずタイムアウトするまでには、次の合計値の 21 秒要する。

 

初回 3 秒 + 1 回目の再試行 6 秒 + 2 回目の再試行 12 秒

以上の動作より、論理的には、SQL Server の待ち受けプロトコルの TCP/IP が無効であり、名前付きパイプが有効である場合は、21 秒より長い接続タイムアウト値でないと SQL Server に接続できない。

 

●データベース プリンシパルは、データベースの スキーマ を所有しているので、削除できない。

 

[データベース]-[対象DB選択]-[セキュリティ]-[スキーマ]-[プロパティ] でスキーマの所有者を問題ないユーザに変更する。

 

●guest

 

データベース ユーザは、データベース レベルのプリンシパル。
データベース ユーザはすべて、public ロールのメンバ。
guest ユーザデータベースを作成すると、既定で guest ユーザが含まれる。guest ユーザに許可された権限は、このデータベースにユーザ アカウントのないユーザーに継承される。
guest ユーザは削除不可だが、CONNECT 権限を取り消すことで無効にすることは可。CONNECT 権限を取り消すには、master または tempdb 以外のデータベース内で REVOKE CONNECT FROM GUEST を実行。

 

●権限等の調査
use master
select * from sys.server_principals
select * from sys.database_principals
◎セッションKILL
select * from sys.dm_exec_sessions ⇒セッション情報
select * from sys.dm_exec_requests

sp_who ⇒ これだと利用しているDB名が表示される。

kill プロセスID(セッションID)
●◎データベースが (シングル ユーザー) または (制限付きユーザー) のアクセスに設定されている場合

 

[データベース]-[プロパティ]-[オプション]-[その他のオプション]
[状態] セクション [アクセスの制限] の値を [MULTI_USER] に変更

●ファイルの利用容量？
use <DB名>
SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;
●DBファイルのSHRINK
use <DB名>
SELECT file_id, name
FROM sys.database_files;
DBCC SHRINKFILE (1,TRUNCATEONLY);

1は、file_id
●ログの切り捨て
バックアップを取得すれば切り捨てられる。ログの圧縮で小さくはできる。

 

・ログを最後にバックアップしてからチェックポイントが発生したこと。
チェックポイントの後、少なくとも次のトランザクション ログのバックアップまで
ログはそのまま保たれる。
・ログ トランザクションを妨げる要因が他にないこと。
(長時間の処理や、レプリケーションが未完等)
・BACKUP LOG ステートメントで WITH COPY_ONLY を指定していないこと。

 

●ログの空き容量
use <DB名>
DBCC SQLPERF(LOGSPACE);
GO
●ログの切り捨てが遅れている理由
use <DB名>
select name,log_reuse_wait,log_reuse_wait_desc from sys.databases 
●メンテナンスプラン の結果ログ出力先

 

メンテナンスプランの[接続の管理…]コンボボックスの右隣に
「レポートとログ記録」というアイコンがあり、そこで出力先を指定する」

 

●getdate()の使い方例(適当なテーブルを指定しても出力できる)
select getdate(),* from sys.tables

select getdate()-10
●各プリンシパルへの権限付与状況
select p.name,e.class_desc,object_name(e.major_id),e.state_desc,e.permission_name
from sys.server_permissions as e
join sys.server_principals as p
on e.grantee_principal_id = p.principal_id
●各データベースユーザへの権限付与状況
select p.name,e.class_desc,object_name(e.major_id),e.state_desc,e.permission_name
from sys.database_permissions as e
join sys.database_principals as p
on e.grantee_principal_id = p.principal_id
●DROP INDEX文の作成
use DBNAME
select 'DROP INDEX ' + name + ' ON ' + OBJECT_SCHEMA_NAME(object_id) + '.' + OBJECT_NAME(object_id) + ';' from sys.indexes 
where *1 and is_unique = 1 and is_primary_key = 0 and index_id > 1
●DatabaseMailUserRole ロールにユーザーを追加するには、

 

事前に、対象ユーザをmsdbのDatabaseMailUserRole ロールにユーザマッピングし、当該ロールを付与しておく。
use msdb
exec sp_addrolemember DatabaseMailUserRole, <ユーザ名>
セキュリティを向上させるために、データベース メールでメール プロファイルへのアクセスが管理される。プロファイルはパブリックまたはプライベートにできる。msdb データベースの DatabaseMailUserRole データベース ロールのすべてのメンバが、パブリック プロファイルを利用できます。

 

DatabaseMailUserRole ロールのすべてのメンバは、このプロファイルを使用して電子メールを送信可。プライベート プロファイルは、msdb データベースのセキュリティ プリンシパル用に定義されます。指定したデータベース ユーザー、ロール、および sysadmin 固定サーバー ロールのメンバのみ、このプロファイルを使用して電子メールを送信できます。既定では、プロファイルはプライベートで、sysadmin 固定サーバー ロールのメンバのみがアクセスできます。プライベート プロファイルを使用するには、sysadmin が、プライベート プロファイルを使用するための権限をユーザーに与える必要があります。

 

また、sp_send_dbmail ストアド プロシージャの EXECUTE 権限は、DatabaseMailUserRole のメンバにのみ与えられます。ユーザーが電子メール メッセージを送信できるようにするには、システム管理者がユーザーを DatabaseMailUserRole データベース ロールに追加する必要があります。

 

●データベースメール設定を確認する設定
sp_configure 'show advanced', 1; 
GO
RECONFIGURE;
GO
sp_configure;
GO
●データベースメールを設定するには
sp_configure 'Database Mail XPs', 1; 
GO
RECONFIGURE;
GO
●DatabaseMailUserRoleロールメンバーを確認するには
EXEC msdb.sys.sp_helprolemember 'DatabaseMailUserRole';
●データベース メールを送信するユーザーは、少なくとも 1 つのデータベース メール プロファイルにアクセスできる必要があります。ユーザー (プリンシパル) とそのユーザーがアクセスできるプロファイルを一覧するには、次のステートメントを実行します。
EXEC msdb.dbo.sysmail_help_principalprofile_sp;
●プリンシパル
プリンシパルは、SQL Server リソースを要求できるエンティティです。SQL Server の承認モデルの他のコンポーネントと同様に、プリンシパルは階層内に配置できます。プリンシパルの効力のスコープは、プリンシパルの定義のスコープ (Windows、サーバー、データベース) と、プリンシパルが分割できないアイテムであるかコレクションであるかによって異なります。分割できないプリンシパルの例には Windows ログインがあり、コレクションであるプリンシパルの例には Windows グループがあります。各プリンシパルには、1 つのセキュリティ識別子 (SID) があります。
◆ Windows レベルのプリンシパル
- Windows ドメイン ログイン
- Windows ローカル ログイン
◆SQL Server レベルのプリンシパル
- SQL Server ログイン
◆データベースレベルのプリンシパル
- データベース ユーザー
- データベース ロール
- アプリケーション ロール

 

◆SQL Server sa ログイン
SQL Server sa ログインは、サーバー レベルのプリンシパルです。このログインは、インスタンスのインストール時に既定で作成されます。SQL Server 2005 および SQL Server 2008 では、sa の既定のデータベースは master です。これは、以前のバージョンの SQL Server の動作から変更されています。

 

◆public データベース ロール
データベース ユーザーはすべて、public データベース ロールに属しています。セキュリティ保護可能なリソースに対する特定の権限が与えられていないか権限が拒否されたユーザーは、public がそのリソースに対して許可されている権限を継承します。

 

◆INFORMATION_SCHEMA と sys
各データベースには、カタログ ビューにユーザーとして表示される 2 つのエンティティ INFORMATION_SCHEMA および sys が含まれています。SQL Server はこれらを必要とします。これらのエンティティはプリンシパルではなく、変更も削除もできません。

 

◆証明書ベースの SQL Server ログイン
名前が 2 つの番号記号 (##) で囲まれたサーバー プリンシパルは、内部システムでのみ使用されます。SQL Server のインストール時に証明書から作成される以下のプリンシパルは、削除しないでください。

 

 

 

●メールを飛ばす場合(SQL Server Agentからの場合)
EXECUTE msdb.dbo.sp_notify_operator @name=N'operator1',@subject=N'【タイトル】DBバックアップ処理',@body=N'表記処理正常終了'
●権限系メモ
use <DB名>
select * from sys.objects

SELECT * FROM fn_builtin_permissions(default);

SELECT * FROM sys.fn_builtin_permissions('SERVER') ORDER BY permission_name;
SELECT * FROM sys.fn_builtin_permissions('DATABASE') ORDER BY permission_name;
SELECT * FROM sys.fn_builtin_permissions('SCHEMA') ORDER BY permission_name;
SELECT * FROM sys.fn_builtin_permissions('LOGIN') ORDER BY permission_name;
SELECT * FROM sys.fn_builtin_permissions('USER') ORDER BY permission_name;
SELECT * FROM sys.fn_builtin_permissions('OBJECT') ORDER BY permission_name;
SELECT * FROM sys.fn_builtin_permissions('ENDPOINT') ORDER BY permission_name;
●ロールに含まれる権限を調べるには。
sp_dbfixedrolepermission で取得
●CONNECT BYの代替サンプル

 

https://msdn.microsoft.com/ja-jp/library/ms175972(v=sql.105).aspx

 

のE.参照。

 

●包含権限を確認する。(ORACLEのCONNECT BY、LEVEL擬似列の代替サンプル)
スコープは以下'SERVER'を変える。

WITH n(permission_name, covering_permission_name, level) AS 
(SELECT permission_name, covering_permission_name , 0 as [LEVEL]
FROM sys.fn_builtin_permissions('')
WHERE class_desc = 'SERVER'
and len(covering_permission_name) = 0
UNION ALL
SELECT nplus1.permission_name, nplus1.covering_permission_name , [LEVEL] + 1
FROM sys.fn_builtin_permissions('') as nplus1, n
WHERE n.permission_name = nplus1.covering_permission_name
and nplus1.class_desc = 'SERVER')
SELECT RIGHT(' '+permission_name,len(permission_name)+level),covering_permission_name,level FROM n

上記スコープ('SERVER'の部分)の候補：
XML SCHEMA COLLECTION
TYPE
ASYMMETRIC KEY
APPLICATION ROLE
LOGIN
REMOTE SERVICE BINDING
CONTRACT
MESSAGE TYPE
ROUTE
OBJECT
ASSEMBLY
USER
ENDPOINT
SERVER
DATABASE
SCHEMA
SYMMETRIC KEY
CERTIFICATE
FULLTEXT STOPLIST
ROLE
FULLTEXT CATALOG
SERVICE
●SQL Server カラム定義を変更すると「変更の保存が許可されていません」が表示された場合の対処法

 

基本はテーブル再作成が必要な場合、警告される模様。
http://nasunoblog.blogspot.jp/2013/10/sql-server-column-edit-error.html

 

●columnからSQL生成

 

以下は2つのINSERT-SELECTを作成する。
use <DB名>
select SQL FROM ( 
select 2 as A,0 as B,
case column_id 
when 1 THEN 'INSERT INTO ' + '<テーブル名1>' + ' ( ' + name 
when (select max(column_id) from sys.columns 
where object_id = (select object_id from sys.tables 
where name ='<テーブル名1>')) 
THEN ',' + name + ' ) ' 
else ',' + name 
end AS SQL
,column_id 
from sys.columns where object_id = 
(select object_id from sys.tables where name ='<テーブル名1>') 
UNION ALL 
select 2 as A,1 as B,
case column_id 
when 1 THEN 'SELECT ' + name 
when (select max(column_id) from sys.columns 
where object_id = (select object_id from sys.tables 
where name ='テーブル名1')) 
THEN ',' + name + ' FROM BK_' + 'テーブル名1' + ';' 
else ',' + name 
end AS SQL
,column_id 
from sys.columns where object_id = 
(select object_id from sys.tables where name ='テーブル名1') 
UNION ALL 
select 3 as A,0 as B,
case column_id 
when 1 THEN 'INSERT INTO ' + 'テーブル名2' + ' ( ' + name 
when (select max(column_id) from sys.columns 
where object_id = (select object_id from sys.tables 
where name ='テーブル名2')) 
THEN ',' + name + ' ) ' 
else ',' + name 
end AS SQL
,column_id 
from sys.columns where object_id = 
(select object_id from sys.tables where name ='テーブル名2') 
UNION ALL 
select 3 as A,1 as B,
case column_id 
when 1 THEN 'SELECT ' + name 
when (select max(column_id) from sys.columns 
where object_id = (select object_id from sys.tables 
where name ='テーブル名2')) 
THEN ',' + name + ' FROM BK_' + 'テーブル名2' + ';' 
else ',' + name 
end AS SQL
,column_id 
from sys.columns where object_id = 
(select object_id from sys.tables where name ='テーブル名2') 
) AS A order by A,B,column_id
●WITH句の利用例(入力パラメータを設定することはできない模様で、テーブル名2は正しく出力できない)
use <DB名>;
WITH TabName(oid) AS 
(select object_id as oid from sys.tables where name ='テーブル名1')
,MaxTab(m) AS 
(select max(column_id) as m from sys.columns 
where object_id = (select oid from TabName))
select SQL FROM ( 
select 2 as A,0 as B,
case column_id 
when 1 THEN 'INSERT INTO ' + 'テーブル名1' + ' ( ' + name 
when (select m from MaxTab) 
THEN ',' + name + ' ) ' 
else ',' + name 
end AS SQL
,column_id 
from sys.columns where object_id = (select oid from TabName) 
UNION ALL 
select 2 as A,1 as B,
case column_id 
when 1 THEN 'SELECT ' + name when (select m from MaxTab) 
THEN ',' + name + ' FROM BK_' + 'テーブル名1' + ';' 
else ',' + name 
end AS SQL
,column_id 
from sys.columns where object_id = (select oid from TabName) 
UNION ALL 
select 3 as A,0 as B,
case column_id 
when 1 THEN 'INSERT INTO ' + 'テーブル名2' + ' ( ' + name 
when (select max(column_id) from sys.columns 
where object_id = (select object_id from sys.tables 
where name ='テーブル名2')) 
THEN ',' + name + ' ) ' 
else ',' + name 
end AS SQL
,column_id 
from sys.columns where object_id = 
(select object_id from sys.tables where name ='テーブル名2') 
UNION ALL 
select 3 as A,1 as B,
case column_id 
when 1 THEN 'SELECT ' + name 
when (select max(column_id)from sys.columns where object_id = (select object_id from sys.tables 
where name ='テーブル名2')) 
THEN ',' + name + ' FROM BK_' + 'テーブル名2' + ';' 
else ',' + name 
end AS SQL
,column_id 
from sys.columns where object_id = 
(select object_id from sys.tables where name ='テーブル名2') 
) AS A order by A,B,column_id
●エクスポート・インポート
https://msdn.microsoft.com/ja-jp/library/ms140052(v=sql.105).aspx
https://technet.microsoft.com/ja-jp/library/ms175937%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396
http://mtgsqlserver.blogspot.jp/2013/03/blog-post_10.html#id26

 

エクスポート
bcp AdventureWorks2008R2.Sales.Currency out Currency.dat -T -c
bcp AdventureWorks2008R2.Sales.Currency out Currency.dat -c -U<login_id> -S<server_name\instance_name>
インポート
bcp AdventureWorks2008R2.Sales.Currency2 in Currency.dat -T -c
空テーブルの作成
USE AdventureWorks2008R2;
GO
SELECT * INTO AdventureWorks2008R2.Sales.Currency2 
FROM AdventureWorks2008R2.Sales.Currency WHERE 1=2
BULK INSERTの例
https://technet.microsoft.com/ja-jp/library/ms175915(v=sql.105).aspx

 

●IDENTITY列があるテーブルへのインポート例(INSERT文)
https://msdn.microsoft.com/ja-jp/library/ms188059.aspx?f=255&MSPPError=-2147217396
SET IDENTITY_INSERT SAMPLE_1 ON
INSERT INTO SAMPLE_1
(ID,
Name,
Status,
XTime)
SELECT ID,
Name,
Status,
XTime
FROM SAMPLE_2
SET IDENTITY_INSERT SAMPLE_1 OFF
●テーブル事前確認(どのテーブルがどのスキーマか)
use <DB>
select t.name,s.name,s.schema_id from sys.tables t,sys.schemas s
where t.schema_id = s.schema_id;
●リコンパイルするには。
sp_recompile
b) 次回実行時にリコンパイルする（テーブル or ストアドプロシージャ単位） 
sp_recompile

特定のクエリが遅い場合の試行方法

 

ステートメント)
USE <データベース名>; 
GO 
EXEC sp_recompile N'<テーブル名 or ストアドプロシージャ名>'; 
GO 

 

使用例) tbl_01 テーブルを対象とするストアド プロシージャおよびトリガーが次回実行時に再コンパイルされます。 
USE mydatabase; 
GO 
EXEC sp_recompile N'dbo.tbl_01'; 
GO 
●ストアドプロシジャ一覧
SELECT name, crdate FROM sysobjects WHERE type='P' ORDER BY name;
●ストアドリコンパイルのトラブルシューティング
https://support.microsoft.com/ja-jp/kb/243586

 

●カウントを取ってみる(テーブル名,件数 で表示したい場合)。
use <DB名>
select 'テーブル名1' AS TABLE_NAME,COUNT(*) AS COUNT FROM テーブル名1 UNION ALL 
select 'テーブル名2' AS TABLE_NAME,COUNT(*) AS COUNT FROM テーブル名2 UNION ALL 
select 'テーブル名3' AS TABLE_NAME,COUNT(*) AS COUNT FROM テーブル名3 ;
●スキーマ権限で、あるスキーマ全体を見えなくできるのでは

 

●信頼されていないドメイン間のレプリケーション
https://support.microsoft.com/ja-jp/kb/321822

 

結局、双方のレプリカサイトで同じユーザ/パスワードのアカウントが必要

 

●FTPサイトの構築
https://technet.microsoft.com/ja-jp/library/hh831655.aspx

 

●MINUSのかわりはEXCEPT
select * from [DB].[SCHEMA].テーブル名1
except
select * from [DB].[SCHEMA].テーブル名2
●useの説明

 

SQL Server ログインで SQL Server に接続すると、自動的に既定のデータベースに接続し、データベース ユーザーのセキュリティ コンテキストを取得できます。SQL Server ログイン用のデータベース ユーザーが 1 人も作成されていない場合、ログインは guest として接続されます。データベース ユーザーが、データベースに対する CONNECT 権限を持たない場合、USE ステートメントは失敗します。ログインに既定のデータベースが割り当てられていない場合、既定のデータベースとして master が設定されます。
USE は、コンパイル時と実行時の両方で実行でき、その効果は直ちに有効になります。したがって、バッチ内で USE ステートメントの後にあるステートメントは、指定したデータベースで実行されます。

 

●goの説明
GO は Transact-SQL ステートメントではなく、sqlcmd および osql ユーティリティと SQL Server Management Studio コード エディターで認識されるコマンドです。
SQL Server のユーティリティでは、GO は、現在の Transact-SQL ステートメントのバッチを SQL Server インスタンスに送信するためのシグナルとして解釈されます。現在のステートメントのバッチは、前回の GO の後に入力されたすべてのステートメントで構成されます。最初の GO の場合、現在のバッチは、アドホック セッションまたはスクリプトの開始後に入力されたすべてのステートメントで構成されます。
Transact-SQL ステートメントを GO コマンドと同じ行に入力することはできません。ただし、GO コマンドの行にコメントは入力できます。
ユーザーは、バッチの規則に従う必要があります。たとえば、バッチの最初のステートメント以降でストアド プロシージャを実行する場合は、バッチに EXECUTE キーワードを含める必要があります。ローカル (ユーザー定義) 変数のスコープはバッチ内に限られ、GO コマンドの後では参照できません。

 

SQL Server アプリケーションでは、複数の Transact-SQL ステートメントを SQL Server インスタンスに送信し、バッチとして実行できます。バッチ内のステートメントは、1 つの実行プランにコンパイルされます。SQL Server のユーティリティでアドホック ステートメントを実行する場合、または SQL Server ユーティリティを介して実行する Transact-SQL ステートメントのスクリプトを作成する場合、プログラマは GO を使用してバッチの終了を知らせる必要があります。
ODBC または OLE DB API に基づくアプリケーションで、GO コマンドを実行しようとすると、構文エラーになります。SQL Server のユーティリティからサーバーに GO コマンドが送信されることはありません。

 

GO は、権限を必要としないユーティリティ コマンドです。すべてのユーザーが実行できます。

 

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

*1:OBJECT_SCHEMA_NAME(object_id) = '<スキーマ名>')
OR (OBJECT_SCHEMA_NAME(object_id) = 'dbo' 
and name like '<テーブル接頭辞>%'

RACめも

■NIC : NAT 1つ、ホストオンリー 1つ で、VM作成。CentOS 6か7 開発ツール＋画面あり。

 

■DNS設定：DNSサーバ側に登録

 

dnsmasq使用(rpmあり)。/etc/hostsへの登録でDNS替わりに利用できる。

 

/etc/hosts変更：
192.168.20.51 r1.12c.jp r1
192.168.20.61 r1-vip.12c.jp r1-vip
192.168.20.52 r2.12c.jp r2
192.168.20.62 r2-vip.12c.jp r2-vip
192.168.20.53 r3.12c.jp r3
192.168.20.63 r3-vip.12c.jp r3-vip
192.168.20.200 rac.12c.jp rac
192.168.20.201 rac.12c.jp rac
192.168.20.202 rac.12c.jp rac

 

# systemctl start dnsmasq 
# systemctl enable dnsmas

 

■SELINUXを切

 

/etc/selinux/config

 

disabledにする。

 

■不要なNetwork Interfaceを削除

 

# virsh net-list --all
名前 状態 自動起動 永続
----------------------------------------------------------
default 動作中 はい (yes) はい (yes)

# virsh net-destroy default
ネットワーク default は強制停止されました

 

# virsh net-autostart default --disable
ネットワーク default の自動起動設定が解除されました

 

# virsh net-list --all

 

※virsh はゲストと hypervisor を管理するための コマンドラインインターフェイス

 

■ユーザ、グループ、ディレクトリの作成

 

groupadd oinstall
groupadd dba
groupadd asmadmin
groupadd asmdba
groupadd asmoper
useradd -u 500 -g oinstall -G dba,asmadmin,asmdba -d /home/oracle oracle
useradd -u 501 -g oinstall -G asmadmin,asmdba,asmoper,dba -d /home/grid grid
# mkdir -p /u01/app/grid
# mkdir -p /u01/app/12.1.0/grid
mkdir -p /u01/app/12.1.0
chown -R grid:oinstall /u01
mkdir -p /u01/app/oracle
# mkdir -p /u01/app/oracle/product/12.1.0/dbhome_1
mkdir -p /u01/app/oracle/product/12.1.0
chown -R oracle:oinstall /u01/app/oracle
chmod -R 775 /u01
passwd oracle
passwd grid

 

■Firewallの停止

 

systemctl stop firewalld
systemctl disable firewalld

 

■avahi-daemonサービスを停止する。

 

# CentOS 7
systemctl stop avahi-daemon.service
systemctl disable avahi-daemon.service

 

■ntpの設定があれば削除

 

/etc/ntp.conf というようなファイルがあればrename(ファイル名は曖昧)

 

■リソース制限変更

 

# vi /etc/security/limits.conf
＜以下を追記＞
grid soft nproc 2047
grid hard nproc 16384
grid soft nofile 1024
grid hard nofile 65536
grid soft stack 10240
grid hard stack 32768
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
oracle soft stack 10240
oracle hard stack 32768
oracle soft memlock unlimited
oracle hard memlock unlimited

 

/etc/sysctl.confに以下を追加
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 4294967295
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576

 

■grid、oracleのログインシェルに、以下を追加

 

ulimit -u 16384 -n 65536

 

■zeroconf の確認

 

echo "NOZEROCONF=yes" >> /etc/sysconfig/network

 

■ASM用のRAWパーティションを作成する。
------------------------------------------------------------
fdiskでパーティション作成
----------------------------------------------------------
vmxファイルに以下を追加
disk.locking = "false"
diskLib.dataCacheMaxSize = "0"
diskLib.dataCacheMaxReadAheadSize = "0"
diskLib.dataCacheMinReadAheadSize = "0"
diskLib.dataCachePageSize = "4096"
diskLib.maxUnsyncedWrites = "0"

 

★oracleasmではない時のディスクグループの作り方

 

root で fdisk

 

# vi /etc/udev/rules.d/99-oracle.rules
KERNEL=="sd[b-z]1",ACTION=="add|change",OWNER="grid",GROUP="asmadmin",MODE="0660"

 

# udevadm control --reload-rules
# start_udev

 

※CentOS では start_udevの代わりに以下を利用する。場合によっては reboot要(2行目で不要なはず)。
# udevadm control --reload-rules
# udevadm trigger

 

# ls -lrt /dev/sd*
brw-rw----. 1 grid asmadmin 8, 145 Jun 2 21:06 sdj1
brw-rw----. 1 grid asmadmin 8, 161 Jun 2 21:09 sdk1
brw-rw----. 1 grid asmadmin 8, 177 Jun 2 21:09 sdl1
brw-rw----. 1 grid asmadmin 8, 193 Jun 2 21:09 sdm1

 

上記の後に、create diskgroup <DG名> external redundancy disk '<device>' で上記デバイス名を指定する。

 

■tmpfsの追加

 

/etc/fstabに以下を追加
tmpfs /dev/shm tmpfs rw,exec,size=2500M 0 0

 

■rpm
# rpm -ivh compat-libcap1-1.10-7.el7.x86_64.rpm ksh-20120801-22.el7_1.2.x86_64.rpm libaio-devel-0.3.109-13.el7.x86_64.rpm 

 

■ホスト名：/etc/hostname(CentOS7)、/etc/sysconfig/network(CentOS6)、/etc/hosts の編集

 

■VMのクローンを作成する。「移動」ではなく「コピー」にして、MACアドレスを変更する。

 

ここでクローンを作ると楽。その場合は、以降、以下を実施する。

 

◎RAC側ホスト名：/etc/hostname(CentOS7)、/etc/sysconfig/network(CentOS6)、/etc/hosts の編集

 

◎IPアドレス変更：/etc/sysconfig/network-script/ifcfg-eno*を変更

 

■oracleインストーラ 日本語

 

./runInstaller -jreLoc /usr/lib/jvm/jre-1.8.0-openjdk-1.8.0.102-4.b14.el7.x86_64/

■cvuqdisk-1.0.9-1.rpm のインストール (GridInfrastructureのメディアより)

 

rpm -ivh cvuqdisk-1.0.9-1.rpm

 

■SSH (grid、root)

 

インストーラの「設定」or「setup」のボタンで設定可能。

 

ダメな場合は以下手順(全サーバで実施要)：

 

client% ssh-keygen -t dsa
Generating public/private dsa key pair.
Enter file in which to save the key (/home/foo/.ssh/id_dsa): <- Enter
Created directory '/home/foo/.ssh'.
Enter passphrase (empty for no passphrase): <- Enter(パスフレーズなし)
Enter same passphrase again: <- Enter(パスフレーズなし)

 

全サーバで以下を実施
client% ssh-copy-id -i ~/.ssh/id_dsa.pub user@server

 

■エラー

 

▼PRVG-11850： ファイアウォールの解除が必要。

 

CentOS 6 iptablesの無効化
CentOS 7 firewalldの無効化(上記参照)

 

▼PRVF-5507： ntpdが存在する。

 

CentOS 6
service ntpd stop

 

CentOS 7
systemctl stop ntpd.service
systemctl disable ntpd.service

 

/etc/ntp.conf、/var/run/ntpd.pid の削除

 

設定ファイル、pidファイルも削除

 

▼PRVG-1360

 

該当サービスを停止する。

 

CentOS 7
systemctl stop avahi-daemon.service
systemctl disable avahi-daemon.service

 

▼PRCF-2020
？

 

▼失敗したASMをもう一度利用するには。

 

dd if=/dev/zero of=<ASMのデバイス名> bs=8192 count=20

 

▼CRS-0184

 

インターコネクト通信ができない？
ノード全部再起動

 

▼Cluster Ready Service が起動しないとき

 

init.crsdは起動時に死ぬと、PIDファイルが残り、再起動時に失敗する。

 

起動オプション：
start 起動するフラグを立てる（/etc/init.d/init.crsから）
stop 停止する（/etc/init.d/init.crsから）
run 起動する（/etc/inittab から）
startを実施すると、再起動フラグが立つ。

 

・起動済みのinit.crsdをKILL
・PIDファイルを消す。

 

/etc/init.d/init.crsd stop
/etc/init.d/init.crsd start

 

▼INS-41210: 使用可能なネットワークのサブネットの1つが、Automatic Storage Management(ASM)用とマークされています。
原因: このストレージ・オプションは、ASMで使用するようにネットワーク・サブネットを構成する必要がありません。

 

インストーラのNetwork I/Fの選択肢で、ASMを選択から外す。

 

▼ASMで自動マウントさせるには

 

ASMインスタンスで、ASM_DISKGROUPSパラメータに追記する。

 

▼PRVE-0044

 

[root@primary01 ~]# vi /etc/security/limits.conf
...
oracle soft memlock unlimited
oracle hard memlock unlimited

 

■状態確認
crs_stat -t
crsctl status resource

 

srvctl status asm
srvctl status listener
srvctl status database -d <db>
srvctl status mgmtdb
srvctl status diskgroup -diskgroup <dg名>

 

# OCR、vote diskがASM内にある場合に、ASMを停止するには、rootで以下を実行(クラスタの一部として停止)
# この場合に、srvctl stop asmで止めようとするとORA-15027で停止できない(自分がつないでいるから
# 自分が停止できないという状態になる)。
crsctl stop crs

 

■patch
unzip
srvctl stop database -d <dbname>
opatch apply <patch#> # for DB
$ORACLE_HOME/crs/install/rootcrs.sh -prepatch
opatch apply <patch#> # for GI
$ORACLE_HOME/rdbms/install/rootadd_rdbms.sh
$ORACLE_HOME/crs/install/rootcrs.sh -postpatch
srvctl start mgmtdb
srvctl start database -d <dbname>

 

crsctl stop crs
crsctl start crs -wait

ブログめも

■Wiki表記のヘルプ

■）とか：）とか ”）とか‘とか＆＃x0029 というようなUnicode表記で逃げる。バックスラッシュは、前に2つバックスラッシュ(計3つ)つけるとエスケープできる模様

■表の書き方

Linuxめも

■まとめて処理するコマンド
pkill … 引数1にマッチしたプロセスIDにシグナルを送る。
pgrep … 引数1にマッチしたプロセスIDを列挙する。
pstree … プロセスの階層を表示する。

 

■標準エラー出力をパイプ渡す。
# 標準出力と標準エラー出力の両方をパイプで渡す
$ hoge.sh 2>&1 | sort | uniq

 

# 標準エラー出力のみをパイプで渡す
$ hoge 2>&1 > /dev/null | sort | uniq

 

■(Linux)コマンドを連結して行う｢;｣｢&｣｢&&｣｢||｣の違い
&&
前のコマンドがうまく終了した(終了ステータスが0)なら、次のコマンドを実行。そうでないなら次のコマンドは実行しない。
#configureが成功したらmake,makeが成功したらmake installする
configure && make && make install

 

#/home/a.txtがあれば cp コマンドでコピーする
[ -f /home/a.txt ] && cp /home/a.txt /var/bkup

 

||
&&とは逆で前のコマンドに失敗(終了コード0以外)したら、次のコマンドを実行する。
#command1が失敗したら指定したディレクトリ削除
command1 || rm -rf /home/hoge/tmp

 

■{[0-9]*}
{} , {0} , {1000} , {12345678} など、{} 内に０個以上の数字がある文字列にマッチします。

 

■パイプのステータスを取得する。$?は最後の結果のみ。
# true | true | false; echo ${PIPESTATUS[0]}
0
# true | true | false; echo ${PIPESTATUS[1]}
0
# true | true | false; echo ${PIPESTATUS[2]}
1

 

■一時ファイル不要のdiff(プロセス置換を使って一時ファイルを生成せずに置換)
diff <(sort file1.txt|uniq) <(sort file2.txt|uniq)
diff <(cat chklog.sh) <(cat chkres.sh )

 

■プロセス置換を使ってその後のログファイル出力を制御
以下のようにすると以降のコマンド実行結果は全てログにも出力される。

 

ログ出力
# 標準出力
exec 1> >(tee -a out.log)
# 標準エラー出力
exec 2> >(tee -a err.log >&2)

 

■バイナリデータをsarで読む
sar -f /var/log/sa/saDD 
※/var/log/sa/sarDD はテキスト

 

■sarでバイナリデータ出力後、テキスト書式に変換する方法
sadf -T -- -A sar.out >sadf.txt

 

■viで大文字⇒小文字変換
s/[A-Z]/?L&/g

 

■tail -f をパイプでgrepするとバッファに保存されてしまう場合、リアルタイムに出す方法。
tail -f /home/hoge/hoge.log | grep --line-buffered "error" 

 

■tail -fでリアルタイムに文字コード変換
$ tail -f logfile | while read LINE ; do echo $LINE | iconv -f SJIS -t UTF-8 ; done
もしくは
$ tail -f logfile | nkf -u -S -w

 

■shellで2次元配列を使う。
#!/bin/sh
for *1 {
for *2 {
eval ARRAY${x}${y}=\$RANDOM
}
}
 
for *3 {
for *4 {
RESULT=`eval echo \\$ARRAY${x}${y}`
echo $RESULT
}
}

 

■空の配列を生成する
array=()
--
declare -a array=() # 宣言
declare -a array=("a" "b" "c") # 初期化
--
some_func() {
local local_array=() # 関数内スコープは local で定義
}

 

■ 配列の要素数
echo ${#array[@]} # 3
echo ${#array[*]} # 3

 

■ 配列のデータを追加
# 先頭に追加
array=(3 "${array[@]}") # array は (3 “a” "b" "c")
# 末尾に追加
array=("${array[@]}" 4) # array は (3 "a" "b" "c" 4)
array+=( 5 ) # array は (3 "a" "b" "c" 4 5)

 

■ 配列からデータを取り出す
# データの先頭要素を取り出す（破壊的操作）
array=("${array[@]:1}") # array は ("a" "b" "c" 4 5)
# データの末尾要素取り出す（破壊的操作）
declare -i num=${#array[@]}-1
array=("${array[@]:0:$num}") # array は ("a" "b" "c" 4)
# 一行
array=(${array[@]:0:*5}) # array は ("a" "b" "c")

 

■ 配列のデータを参照する
i=0
for e in ${array[@]}; do
echo "array[$i] = ${e}"
let i++
done
array[0] = a
array[1] = b
array[2] = c

 

# C言語風に記述する
for *6 {
echo "array[$i] = ${array[i]}"
}
array[0] = a
array[1] = b
array[2] = c

 

# Bash 独自の記述 1
echo "${array[@]}"
a b c

 

# Bash 独自の記述 2
IFS=$'\n'
echo "${array[*]}"
a
b
c

 

■ 配列のデータから任意の要素を削除する
# ある要素を削除
unset array[1]
echo "${array[@]}" # arrayは("a" "c")
echo ${array[0]} # array[0]は"a"
echo ${array[1]} # array[1]は ""（空）
echo ${array[2]} # array[2]は"c"
配列の中身のデータは削除できても、配列自体は削除できない。
添字を詰めたければ、
array=("${array[@]}"
代入しなおす必要があり。

 

unset array[@] # 全削除する
echo "${array[@]}" # 配列 array は ""（空）
echo "${#array[@]}" # 要素数は0

 

■ 配列にデータを格納する
array[0]=1
array[1]="a"

 

■hangup時に強制的にkdumpを行って再起動する操作：(マジックキー)
Alt+SysRq+c (カーネルをクラッシュさせる)->crash時にkdumpが吐ける設定になっていないと意味がない。
Alt+SysRq+b 即座に再起動

 

■チルダ問題メモ
FULL WIDTH TILDE(15711646)とWAVE DASH(14909596)を切りかえて再調査。WAVE_DASHは済み。

 

■LinuxでNTPサーバと時刻同期していることを確認する方法。
ntpq -p

 

remote refid st t when poll reach delay offset jitter
==============================================================================
*10.XX.X.XXX LOCAL(0) 6 u 423 1024 377 1.758 -0.275 1.677
LOCAL(0) LOCAL(0) 5 l 64 64 377 0.000 0.000 0.008 
remoteのところにつき記号の意味は以下の通り。
*：現在参照同期中のサーバ
+：現在クロック誤り検査に合格したサーバ
#：現在参照同期中ですが，距離が遠いサーバ
：現在参照していないサーバ （空白）
x：現在クロック誤り検査で不合格になったサーバ
.：現在参照リストからはずされたサーバ

 

■NTPサーバと時刻合わせ
ntpdate NTPサーバ

 

■CentOSでhwclockをlocaltimeに設定する方法(9時間差を補正する)
# timedatectl
# hwclock -s --localtime
# timedatectl set-local-rtc true

 

systemd-timedated が /etc/adjtime を読み込む。このファイルの3行目がhwclockがUTCかLOCALかを設定。systemd-timedatedがそれを見て、システムクロックをUTCかLOCALに設定する。timedatectl set-local-rtc true で当該ファイル3行目をLOCALに変更する。3行目がないとUTCと判断してにする模様。
1行目は、時刻の補正やズレ、2行目は、時刻合わせ後の経過秒数。0は未実施か、異常であった場合の模様。

 

・/etc/adjtime
# more /etc/adjtime
0.0 0 0.0
0
LOCAL

 

■ハードウェアクロック確認
# hwclock --show
# hwclock --show --utc
# hwclock --show --localtime

 

■タイムゾーン変更
# mv /etc/localtime{,.org}
# ln -s /usr/share/zoneinfo/Asia/Tokyo /etc/localtime

 

■CentOS7で rootユーザのパスワードリセット
wheelグループのユーザでログインし以下を実行する。
$ sudo passwd root

 

※GRUB 2ではシングルユーザモード(エマージェンシーモードも同様)でもrootユーザのパスワードが要求される模様。

 

■Linuxにおける変数の設定

 

/etc/environment ファイル
ログイン時にOSが最初に使用するファイル。すべてのプロセス用の基本的な変数を記述。
シェルセッションを起動するさまざまなプログラムが /etc/environment ファイルを使う。組織で利用するHTTP_PROXY環境変数等に使う。

 

/etc/profile ファイル
ログイン時にOSが使用する 2 番目のファイル。
bash は対話型シェル用に /etc/bash.bashrc を使い、ログインシェル用に /etc/profile を使う。

 

.profile ファイル
$HOMEにあり、個々の作業環境をカスタマイズ。
bashの場合、~/.bashrc と ~/.bash_profile で、alias等を設定する。

 

.env ファイル
OSがログイン時に使用する 4 番目のファイル。.profile に export ENV=$HOME/.env の行が含まれている場合は、.env ファイル。

 

全シェルを同様設定にする
~/.bash_profile ファイルの中で ~/.bashrc の内容を実行 またはsourceする。
全ユーザで行う場合は、/etc/profile の中で /etc/bash.bashrc を呼ぶ。

 

■ログイン画面で、SHIFT+CTRL+F1をおすと、CUIの画面が開くことができる。

 

■/etc/environment例
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games
LANGUAGE=ja_JP:en
LANG=ja_JP.UTF-8
LC_MESSAGES=ja_JP.UTF-8
LC_CTYPE=ja_JP.UTF-8
LC_COLLATE=ja_JP.UTF-8

 

■/etc/default/locale例
LANG=ja_JP.UTF-8
LANGUAGE=ja_JP:en
LC_MESSAGES=ja_JP.UTF-8
LC_CTYPE=ja_JP.UTF-8
LC_COLLATE=ja_JP.UTF-8

 

■/etc/pam.d/gdm例
auth required pam_env.so readenv=1 envfile=/etc/default/locale

 

■名前変更、コピー
mv hoge{,.too}
cp hoge{,.too}

 

■vmware-toolsを手動でマウントする場合
vmhgfs-fuse .host:/ /mnt/hgfs

 

永続化したい場合、/etc/fstabを以下に変更

 

.host:/ /mnt/hgfs fuse.vmhgfs-fuse allow_other 0 0

*1: x = 0; x <= 2; x++

*2: y = 0; y <= 2; y++

*3: x = 0; x <= 2; x++

*4: y = 0; y <= 2; y++

*5:${#array[@]}-1

*6:i = 0; i < ${#array[@]}; i++

STATSPACKめも

●スケジュール確認
select * from dba_jobs;

 

●取得状況 PERFSTATユーザ
alter session set nls_date_format='yyyy/mm/dd HH24:MI:SS';
select snap_id,snap_time from stats$snapshot order by snap_id;

 

●レポート取得(インスタンス指定は、sprepins) PERFSTATユーザ
@?/rdbms/admin/spreport

 

●sqlレポート取得(インスタンス指定は、sprsqins) PERFSTATユーザで
@?/rdbms/admin/sprepsql

 

●STATSPACKの構築(sysで実施)
@?/rdbms/admin/spcreate

 

●STATSPACKの定義削除(sysで実施)
@?/rdbms/admin/spdrop

 

●STATSPACK自動スケジューリング
@?/rdbms/admin/spauto

 

●STATSPACK JOB間隔の変更
execute dbms_job.interval(1,'SYSDATE+(1/48)');

 

●過去のsnapshotを削除
-- スナップショットIDの範囲を指定して削除
execute statspack.purge(i_begin_snap=>91, i_end_snap=>115, i_extended_purge=>TRUE)
--特定日時以前に取得したスナップショットを削除
execute statspack.purge(i_purge_before_date=>to_date('20001231','YYYYMMDD'), i_extended_purge=>TRUE)
--指定日数分のスナップショットを残して削除
execute statspack.purge(i_num_days=>30, i_extended_purge=>TRUE)

 

●スナップショットレベルの確認
select snap_level from stats$statspack_parameter;

 

●スナップショットレベルの変更(スナップショットレベルを7に変更する場合)
exec statspack.modify_statspack_parameter (i_snap_level=>7);

 

●STATSPACKをexportする
exp PERFSTAT/<pass> parfile=$ORACLE_HOME/rdbms/admin/spuexp.par log=spuexp.log

 

●STATSPACKをimportする PERFSTATユーザ
spool dis.sql
select 'ALTER TABLE '||table_name||' DISABLE CONSTRAINT '||constraint_name||';' from user_constraints;
spool off
@dis
imp PERFSTAT/<pass> file=<expしたdmp> log=spuimp.log ignore=y

 

●初期化パラメータの確認
select * from stats$parameter;

 

●SQL概要確認
select * from STATS$SQL_SUMMARY;

 

●SQL実行計画取得
select * from stats$sql_plan;

 

●sqlと実行計画との関連把握
select * from stats$sql_plan_usage;

 

●SQLテキスト取得
select + from stats$sqltext;

SQL性能改善めも

●オプティマイザのパラメータ確認

 

select * from V$SYS_OPTIMIZER_ENV;
select * from V$SES_OPTIMIZER_ENV:

 

●オプティマイザヒント確認

 

select * from v$sql_hint;

 

●各オブジェクトのSELECTIVITY確認

 

セレクティビティ（選択率） ＝ 条件を適用した結果の行数 / 全体の行数
カーディナリティ ＝ 表の行数 × セレクティビティ

 

select count(distinct 列名1), count(distinct 列名2), … count(distinct 列名N)
from 対象表名

 

●plan_tableの作成

 

@$ORACLE_HOME/rdbms/admin/catplan.sql

 

●実行計画の生成

 

explain plan for SQL文

 

●実行計画確認

 

auto trace
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
select plan_table_output from table(dbms_xplan.display_plan(format=>'ALL'));
select * from v$sql_plan;
@?/rdbms/admin/UTLXPLS.SQL --シングル用
@?/rdbms/admin/UTLXPLP.SQL --パラレル用

 

●PLAN_TABLE別名

 

CREATE OR REPLACE PUBLIC SYNONYM my_plan_table for plan_table$

 

●SQL文確認

 

select * from v$sqltext;

 

●SQLによる確認

 

select * from v$sql;
select * from v$sqlarea;

 

●SQL実測確認

 

set pages 50000
set lines 30000
set trimspool on
set timing on
set time on
set autot on
spool <スプールファイル名>
<対象SQL実行>
spool off

 

●実行中のSQL確認

 

col sid for 99999
col serial# for 99999
col sql_text for a64
select s.sid,s.serial#,t.sql_text from v$session s,v$sqltext t where s.sql_id=t.sql_id and s.status = 'ACTIVE' order by 1,2,t.piece;

 

●実行中のSQL確認(RAC、マルチテナント版)

 

col sid-s# for a10
col i.c for a3
col sql_text for a64
select s.inst_id||'.'||s.con_id as "i.c",s.sid||','||s.serial# as "sid-s#",t.sql_text from gv$session s,gv$sqltext t where s.sql_id=t.sql_id and s.status = 'ACTIVE' order by 1,2,t.piece;