「Oracle」(2007/06/13 (水) 22:58:54) の最新版変更点
追加された行は緑色になります。
削除された行は赤色になります。
*rman でバックアップを取る
-dbca で rmanリポジトリ(データベース)を作成する。
--グローバル・データベース名: rmanrep
--SID: rmanrep
--パスワード: oracle
--キャラクタセット: Unicode(AL32UTF8)
--各国語キャラクタセット: UTF8
-oranavi で自動起動設定を ON にする。
-rman ユーザを作成する。
$ export ORACLE_SID=rmanrep
$ sqlplus "/ as sysdba"
> create user rman identified by rman
quota unlimited on users;
> grant connect, resource, recovery_catalog_owner to rman;
$ rman catalog rman/rman
> create catalog;
> connect target sys/oracle@orcl
> register database;
> backup database;
*rman tips
-Miracle Linux V4.0 で rman を使用できない
$ which rman
/usr/bin/X11/rman ← Oracle の rman とは別のプログラムを呼び出している。
-アーカイブログ手動削除後に再同期するには
> crosscheck copy;
> delete expired copy;
> delete obsolete;
-リカバリ・カタログを更新する
> resync catalog;
-rman リポジトリの設定を確認する
$ export ORACLE_SID=rmanrep
> rman
> connect target sys/manager@orcl.db01.oracle.co.jp
> connect catalog rman/rman@rmanrep
> show all
-RMAN 20003 エラーが発生する場合
> reset database;
*DBA SQL
-表ロック調査
COL USERNAME FOR A8
COL OBJECT_NAME FOR A8
COL LMODE FOR 9999
COL PROGRAM FOR A8
COL CTIME FOR 9999
SELECT S.USERNAME, S.SID, S.SERIAL#, O.OBJECT_NAME, LMODE, V.CTIME, S.PROGRAM
FROM DBA_OBJECTS O, V$LOCKED_OBJECT L, V$SESSION S, V$LOCK V
WHERE O.OBJECT_ID = L.OBJECT_ID
AND L.SESSION_ID = S.SID
AND L.SESSION_ID = V.SID
AND V.TYPE = 'TM'
ORDER BY CTIME DESC;
-行ロック調査
SELECT S.USERNAME, S.SID, S.SERIAL#, O.OBJECT_NAME, LMODE, V.CTIME, S.PROGRAM
FROM DBA_OBJECTS O, V$LOCKED_OBJECT L, V$SESSION S, V$LOCK V
WHERE O.OBJECT_ID = L.OBJECT_ID
AND L.SESSION_ID = S.SID
AND L.SESSION_ID = V.SID
AND V.TYPE = 'TX'
ORDER BY CTIME DESC;
-SQL調査
SELECT A.SQL_TEXT, A.ADDRESS FROM V$SQLAREA A, V$SESSION B WHERE
A.ADDRESS = B.SQL_ADDRESS AND B.SID = ANY(SELECT SID FROM V$LOCK WHERE TYPE IN ('TX', 'TM'));
-統計情報収集（スキーマ単位）
BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS (
ownname => 'スキーマ名',
options => 'GATHER AUTO');
END;
/
-統計情報収集（テーブル単位）
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
OWNNAME => 'スキーマ名'
,TABNAME => 'テーブル名'
);
END;
/
-統計情報収集日付の確認
SELECT TABLE_NAME NAME, LAST_ANALYZED FROM USER_TABLES ORDER BY TABLE_NAME;
-統計情報の内容確認
SELECT TABLE_NAME, NUM_ROWS, AVG_ROW_LEN, BLOCKS FROM USER_TABLES ORDER BY TABLE_NAME;
-セッションを強制終了する
> SELECT SID||','||SERIAL#||','||STATUS||','||USERNAME FROM V$SESSION WHERE USERNAME = 'C_LIVE';
SID||','||SERIAL#||','||STATUS||','||USERNAME
---------------------------------------------
199,7860,INACTIVE,C_LIVE
213,6979,INACTIVE,C_LIVE
> ALTER SYSTEM KILL SESSION '199,7860'
> ALTER SYSTEM KILL SESSION '213,6979'
-DBID を確認する
$ sqlplus "/ as sysdba"
>select dbid from v$database;
> select username, default_tablespace from dba_users;
> select FILE_NAME, TABLESPACE_NAME from dba_data_files;
-http://www.dbforums.com/archive/index.php/t-1066637.html
> select dbms_metadata.get_ddl('TRIGGER','<name>','<owner>') from dual;
-ユーザ作成
$ sqlplus /nolog
> connect / as sysdba
> create user scott profile default
identified by tiger
default tablespace user01
temporary tablespace temp
account unlock;
grant resource, connect to amashin;
quit;
-UNDO保存を保証する。
> select tablespace_name, retention from dba_tablespaces;
> alter tablespace UNDOTBS1 retention guarantee;
-データファイルの自動拡張を有効化する。
> select file_name, tablespace_name from dba_data_files;
> select autoextensible, tablespace_name from dba_data_files;
> alter database
datafile '/opt/app/oracle/oradata/orcl/undotbs01.dbf'
autoextend on;
-ユーザのアカウントロックを解除する。
-- ロック解除
> alter user SCOTT account unlock;
-- パスワード変更
$ sqlplus scott/tiger
-SCN からタイムスタンプを求める。
> select current_scn scn, scn_to_timestamp(current_scn) timestamp from v$database;
-タイムスタンプから SCN を求める。
> select timestamp_to_scn(systimestamp) scn, systimestamp timestamp from dual;
*SQL*Plus
-CSV書き出し
> set linesize 1000
> set pagesize 0
> set trimspool on
> spool user_data.txt
> select '"'||id||'","'||name||'"' from users;
*シノニム
-PUBLICシノニムを作成する
$ sqlplus "/ as sysdba"
> CREATE PUBLIC SYNONYM emp FOR scott.emp;
-シノニムを削除する
> drop public synonym samplelink;
*データベースリンク
-データベースリンクの定義を確認する
$ export ORACLE_SID=orcl
$ sqlplus "/ as sysdba"
> select owner,db_link,username,host,created from dba_db_links;
-データベースリンクを作成する
$ export ORACLE_SID=orcl
$ sqlplus "/ as sysdba"
> create public database link samplelink
connect to scott identified by tiger using 'orcl.db01.oracle.co.jp';
-データベースリンクを使用する
$ export ORACLE_SID=orcl
$ sqlplus scott/tiger
> select * from emp@samplelink
-データベースリンクを削除する
> drop public database link samplelink;
*スタンドバイ・データベースを rman でバックアップすると、ORA-19573 エラーが発生する
-症状:
ORA-19573: cannot obtain sub-shared enqueue for datafile 6
-原因:
RMAN in version 9.2.0.1 のバグです。(bug 2688591 (TAR 2708247.999))
-処置方法:
SQL> shutdown immediate;
SQL> startup nomount;
SQL> alter database mount standby database;
SQL> alter database recover managed standby database disconnect from session;
-参考情報: http://www.trivadis.ch/Images/standbybackup_en_tcm17-7294.pdf
If you create a new tablespace on the primary database,
perform a logfile switch and want to back up this
tablespace on the standby database,RMAN in version
9.2.0.1 aborts with an error:
RMAN> backup tablespace test;
ORA-19573: cannot obtain sub-shared enqueue for datafile 6
This behavior has now been accepted as bug 2688591 (TAR 2708247.999).
But there is a workaround: stopping and starting the standby database,
after which the backup works correctly.
*export/import
-全データベースモードでエクスポートする
$ export ORACLE_SID=orcl
$ exp system/パスワード file=fulldump.dmp log=fulldump.log full=y
-ユーザーモードでエクスポートする
$ export ORACLE_SID=orcl
$ exp scott/tiger file=scott.dmp owner=scott
-ユーザーモードでインポートする
$ imp scott/tiger file=sccot.dmp fromuser=sccot touser=sccot destroy=y ignore=y
*ORA-19809: リカバリ・ファイルの制限を超えています
-pfile の db_recovery_file_dest_size を増やす。
$ export ORACLE_SID=devel
$ sqlplus "/ as sysdba"
SQL> create pfile='$ORACLE_BASE/admin/$ORACLE_SID/pfile/init$ORACLE_SID.ora' from spfile;
SQL> exit
$ vi $ORACLE_BASE/admin/$ORACLE_SID/pfile/init$ORACLE_SID.ora
-編集した pfile から spfile を作成する。
$ cd $ORACLE_BASE/admin/$ORACLE_SID/pfile
$ mv init$ORACLE_SID.ora init$ORACLE_SID.ora.org
$ sqlplus "/ as sysdba"
SQL> create spfile from pfile='$ORACLE_BASE/admin/$ORACLE_SID/pfile/init$ORACLE_SID.ora';
SQL> startup
-参考:
--[[14 ORA-19400～ORA-24276>http://otndnld.oracle.co.jp/document/products/oracle10g/101/doc_v5/server.101/B12448-02/e19400.htm]]
*SQL*Loader
-コマンド
$ export ORACLE_SID=orcl
$ sqlldr userid=user01/******** control=testtbl.ctl data=testtbl.dat
-シーケンスを使用する([[参考URL>http://fukkey.dyndns.org/pins/ora/021230/51159.html]])
LOAD DATA CHARACTERSET JA16SJIS
INTO TABLE T_COMPANY_RAW
FIELDS TERMINATED BY ','
trailing nullcols
(ID "SEQ_COMPANY_RAW_ID.NEXTVAL",
CORP_TYPE,
NAME,
・
・
FUTURE)
-改行があるデータの流し込み
load data CHARACTERSET JA16SJIS
append
continueif last !='"'
into table test_table
fields terminated by ',' enclosed by '"'
( ID,
NAME,
VALUE,
TESTDATE DATE "YYYYMMDD")
-日付データの流し込み
load data CHARACTERSET JA16SJIS
append into table t_answers
fields terminated by ','
trailing nullcols
( id,
campaign_id,
user_id,
registered "to_date(:registered, 'YYYY/MM/DD HH24:MI:SS')" )
-「データ・ファイルのフィールドが最大長を超えています。」
load data CHARACTERSET JA16SJIS
append into table t_encourage
fields terminated by ','
( ID,
CAT2_ID,
MAILBODY char(4000))
*Oracle 10g R10.2.0.1 インストール (MiracleLinuxV3.0)
-memo
--RPM を更新してから Oracle をインストールする
--DHCP を使用している場合、一時的にTCP/IPの設定で固定IPアドレスを割り当ててから、Oracle をインストールする
-ダウンロード
--[[Oracle 10g R10.2.0.1>http://www.oracle.com/technology/software/products/database/oracle10g/index.html]] をダウンロードする
-rootユーザでログインする
-ランレベルを 5 に変更する
# init 5
-oranavi を起動する
# oranavi
-Oracle DBMS のインストール
--全てデフォルト設定でインストール
*Oracle 9i R9.2.0.4.0 インストール後に必要な作業
-Run the following commands and scripts in order from $ORACLE_HOME in migrate mode on all updated databases. If you are using the OLAP option, then ensure the database listener is up.
$ sqlplus "/ as sysdba"
> startup migrate
> spool patch.log
> @?/rdbms/admin/catpatch.sql
> shutdon immediate
> startup
-This step is optional, and will recompile all invalid PL/SQL packages now instead of when the packages are accessed the first time. The utlrcmp.sql script can be used to parallelize this in multiprocessor machines.
> @?/rdbms/admin/utlrp.sql
-Enter the following commands if using Oracle OLAP option:
> alter user olapsys identified by password account unlock;
> connect olapsys/password
> @?/cwmlite/admin/postamd.sql
*ORA-06512 → [[参考URL>http://lists.suse.com/archive/suse-oracle/2005-Jul/0123.html]]
-現象
ORA-00604: error occurred at recursive SQL level 1
ORA-06521: PL/SQL: Error mapping function
ORA-06512: at "SYS.OLAPIHISTORYRETENTION", line 1
ORA-06512: at line 6
-原因: BUG 3386542
-処置: (metalink Note:266728.1): Disable OLAPISTARTUPTRIGGER and OLAPISHUTDOWNTRIGGER to avoid error from being generated.
ALTER TRIGGER OLAPISTARTUPTRIGGER DISABLE;
ALTER TRIGGER OLAPISHUTDOWNTRIGGER DISABLE;
*rmanでデータベースを複製する
-前提条件
--Miracle Linux V3.0
--Oracle 10g Standard Edition
*ターゲット・データベースへ接続できるようにする
$ vi /opt/app/oracle/product/10.1.0/db_1/network/admin/tnsnames.ora
ACCOUNT1.DB01.ORACLE.CO.JP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.101)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = account1.db01.oracle.co.jp)
(SERVER = DEDICATED)
)
)
ACCOUNT1.RESEARCH01.ORACLE.CO.JP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.102)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = account1.oracle.co.jp)
(SERVER = DEDICATED)
)
)
$ vi /opt/app/oracle/product/10.1.0/db_1/network/admin/listener.ora
(SID_DESC =
(GLOBAL_DBNAME = account1.db01.oracle.co.jp)
(ORACLE_HOME = /opt/app/oracle/product/10.1.0/db_1)
(SID_NAME = account1)
)
$ sqlplus scott/tiger@account1.db02.oracle.co.jp
-ターゲット・データベースのバックアップ
(複製側)
# su - oracle
$ mkdir -p /opt/app/oracle/oradata/account1/backups ← ターゲットデータベース側・複製側の両方で作成する
$ export ORACLE_SID=orcl
$ sqlplus /nolog
> conn / as sysdba
> create user rman identified by rman temporary
tablespace temp default tablespace users quota unlimited on users;
> grant recovery_catalog_owner, connect, resource to rman;
$ rman catalog rman/rman@orcl
> connect target sys/manager@account1.db01.oracle.co.jp;
> create catalog;
> register database;
> configure retention policy to redundancy = 1;
> configure controlfile autobackup on;
> configure controlfile autobackup format for device type disk to '/opt/app/oracle/oradata/account1/backups/%F';
> backup database
> format '/opt/app/oracle/oradata/account1/backups/full_%d_%T_s%s_p%p';
> backup archivelog all delete all input
> format '/opt/app/oracle/oradata/account1/backups/arc_%d_%T_s%s_p%p';
-補助データベースを作成する
$ orapwd file=/opt/app/oracle/product/10.1.0/db_1/dbs/orapwaccount1 password=manager entries=30 force=y
> alter system set remote_login_passwordfile='EXCLUSIVE' scope=spfile;
> shutdown immediate
> startup force nomount
> show parameters remote_login_passwordfile;
$ cd /opt/app/oracle/admin/account1/
$ mkdir pfile bdump cdump create udump
$ cd /opt/app/oracle/admin/account1/pfile
$ vi initaccount1.ora
DB_NAME=account1
CONTROL_FILES=(/opt/app/oracle/oradata/account1/control01.ctl,
/opt/app/oracle/oradata/account1/control02.ctl,
/opt/app/oracle/oradata/account1/control03.ctl)
$ export ORACLE_SID=account1
$ sqlplus "/ as sysdba"
> create spfile from pfile='/opt/app/oracle/admin/account1/pfile/initaccount1.ora';
> startup force nomount
$ lsnrctl start
$ rman target sys/manager@account1.db01.oracle.co.jp auxiliary sys/manager@account1.db02.oracle.co.jp
> duplicate target database to account1 nofilenamecheck;
> duplicate target database for standby nofilenamecheck;
*フラッシュバックドロップ
-ごみ箱の中を見る。
> SELECT original_name, type, ts_name, droptime, dropscn, object_name FROM user_recyclebin;
-テーブルをリストアする。
> flashback table test to before drop;
-インデックスをリストアする
--テーブルをリストアすると、インデックスや制約も復活するが、名前はごみ箱の中の時のままなので、手動でリネームする。
> select index_name from user_indexes where table_name = 'TEST';
> alter index "BIN$ExUXY+Nfh9zgQAB/AQAI6g==$0" rename to PK_TEST;
-ごみ箱を空にする。
> purge dba_recyclebin;
> purge user_recyclebin;
> purge tablespace user01 user test;
> purge table test;
> purge index pk_test;
-ごみ箱を介さずに削除する。
> drop table test purge;
> drop user scott cascade;
> drop tablespace user01 including contents;
*フラッシュバックデータベースを有効にする
-DB_RECOVERY_FILE_DEST、DB_RECOVERY_FILE_DEST_SIZE が設定されているか確認する。
> select name,value from v$parameter where name like 'db_recovery_file_dest%';
-設定されていなければ、設定する。
> alter system set db_recovery_file_dest='/opt/app/oracle/flash_recovery_area' scope =both;
> alter system set db_recovery_file_dest_size = 2147483648 scope = both;
-アーカイブログモードにする。
> shutdown immediate
> startup mount
> alter database archivelog;
> alter database open;
> archive log list;
-フラッシュバックログの保存期間を確認する。
> select name, value from v$parameter where name = 'db_flashback_retention_target';
-フラッシュバックデータベース機能を有効化する。
> shutdown immediate
> startup mount
> alter database flashback on;
> alter database open
> select log_mode, flashback_on from v$database;
-どの時点までフラッシュバックできるか確認する。
> select flashback_size/1024/1024 "SIZE(M)",
estimated_flashback_size/1024/1024 "EST_SIZE(M)",
oldest_flashback_scn, to_char(oldest_flashback_time, 'YYYY-MM-DD HH24:MI:SS')
from v$flashback_database_log;
-フラッシュバックデータの書き込みサイズを確認する。
> select to_char(begin_time, 'HH24') hour,
flashback_data/1024 "FLASHBACK_DATA(K)",
redo_data/1024 "REDO_DATA(K)"
from v$flashback_database_stat;
-フラッシュバックデータベースを実行する
SQL> shutdwon immediate
SQL> startup mount
SQL> flashback database to scn 608964;
SQL> flashback database to time "to_timestamp('06-05-06 22:41:00.059098')";
RMAN> flashback database to scn 608964;
RMAN> flashback database to time "to_timestamp('06-05-06 22:41:00.059098')";
RMAN> flashback database to sequence 1 thread 1;
-データベースをオープンする。
> shutdown immediate
> startup mount
> alter databse open read only; -- 読み取り専用でオープンする場合
> alter database open resetlogs; -- 読み書き可能でオープンする場合
-フラッシュバックログから表領域を除外する
> alter tablespace example flashback off;
> select name, flashback_on from v$tablespace;
*ブロック破損
-ブロック破損の診断
$ dbv file=/opt/app/oracle/oradata/orcl/system01.dbf
SQL> analyze table test validate structure cascade;
SQL> analyze index pk_test validate structure;
-rman で物理破損ブロックを検出する。
RMAN> backup validate datafile 1;
SQL> select * from v$database_block_corruption;
-rman で論理破損ブロックを検出する。
RMAN> backup check logical datafile 1;
-rman で物理破損チェックを行わずにバックアップを行う。
RMAN> backup nochecksum datafile 1;
-破損ブロックを含んだままバックアップをとる。
RMAN> run {
set maxcorrupt for datafile '/opt/app/oracle/oradata/orcl/system01.dbf' to 2;
backup datafile '/opt/app/oracle/oradata/orcl/system01.dbf';
}
-rman によるブロックメディアリカバリ
RMAN> blockrecover datafile 1 block 1;
RMAN> blockrecover corruption list;
*簡単なジョブの作成
-ジョブを作成する
$ export ORACLE_SID=rmanrep
$ sqlplus "/ as sysdba"
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'SYS.BACKUP',
job_type => 'EXECUTABLE',
job_action => '/home/oracle/devel/shell/backup.sh',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=DAILY;BYHOUR=5;BYMINUTE=0',
enabled => TRUE,
auto_drop => FALSE,
comments => 'ORCLデータベースのバックアップ');
END;
/
-ジョブを削除する
BEGIN
DBMS_SCHEDULER.DROP_JOB(job_name => 'sys.backup');
END;
/
*プログラムとスケジュールを使用したジョブの作成
-SCOTTユーザに CREATE JOB 権限を与える
$ export ORACLE_SID=orcl
$ sqlplus "/ as sysdba"
> GRANT CREATE JOB TO SCOTT;
> CONNECT SCOTT/TIGER;
-ストアドプロシージャの作成
CREATE OR REPLACE PROCEDURE test_del(
p_id test.id%TYPE)
AS
BEGIN
DELETE FROM test WHERE ID = p_id;
END test_del;
/
-プログラムの作成
> BEGIN
-- プログラムの作成
DBMS_SCHEDULER.CREATE_PROGRAM(
program_name => 'SCOTT.TEST_PROGRAM',
program_type => 'STORED_PROCEDURE',
program_action => 'SCOTT.TEST_DEL',
number_of_arguments => 1,
enabled => FALSE,
comments => 'TEST表のDELETE');
-- プログラム引数の定義
DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT(
program_name => 'SCOTT.TEST_PROGRAM',
argument_name => 'P_ID',
argument_position => 1,
argument_type => 'NUMBER');
-- プログラムの有効化
DBMS_SCHEDULER.ENABLE('SCOTT.TEST_PROGRAM');
END;
/
-スケジュールの作成
BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE(
schedule_name => 'SCOTT.MONTHLY_SCHEDULE',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=MONTHLY;BYMONTHDAY=15',
comments => '毎月15日実行'
);
END;
/
-プログラムとスケジュールを利用したジョブの作成
> BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'SCOTT.TEST_JOB',
program_name => 'SCOTT.TEST_PROGRAM',
schedule_name => 'SCOTT.MONTHLY_SCHEDULE',
enabled => FALSE,
auto_drop => FALSE,
comments => 'TEST表のDELETE'
);
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
job_name => 'SCOTT.TEST_JOB',
argument_name => 'P_ID',
argument_value => 10
);
DBMS_SCHEDULER.ENABLE('SCOTT.TEST_JOB');
END;
/
*PL/SQL
-PL/SQL実行後、エラーを表示する。
> SHOW ERROR
-DBMS_OUTPUT.PUT_LINE などでの画面出力を有効にする。
> SET SERVEROUTPUT ON
*rman でバックアップを取る
-dbca で rmanリポジトリ(データベース)を作成する。
--グローバル・データベース名: rmanrep
--SID: rmanrep
--パスワード: oracle
--キャラクタセット: Unicode(AL32UTF8)
--各国語キャラクタセット: UTF8
-oranavi で自動起動設定を ON にする。
-rman ユーザを作成する。
$ export ORACLE_SID=rmanrep
$ sqlplus "/ as sysdba"
> create user rman identified by rman
quota unlimited on users;
> grant connect, resource, recovery_catalog_owner to rman;
$ rman catalog rman/rman
> create catalog;
> connect target sys/oracle@orcl
> register database;
> backup database;
*rman tips
-Miracle Linux V4.0 で rman を使用できない
$ which rman
/usr/bin/X11/rman ← Oracle の rman とは別のプログラムを呼び出している。
-アーカイブログ手動削除後に再同期するには
> crosscheck copy;
> delete expired copy;
> delete obsolete;
-リカバリ・カタログを更新する
> resync catalog;
-rman リポジトリの設定を確認する
$ export ORACLE_SID=rmanrep
> rman
> connect target sys/manager@orcl.db01.oracle.co.jp
> connect catalog rman/rman@rmanrep
> show all
-RMAN 20003 エラーが発生する場合
> reset database;
*DBA SQL
-DDL取得
set pagesize 0
SELECT
DBMS_METADATA.GET_DDL('TABLE', USER_TABLES.TABLE_NAME),
DBMS_METADATA.GET_DEPENDENT_DDL('INDEX', USER_TABLES.TABLE_NAME)
FROM USER_TABLES;
-表ロック調査
COL USERNAME FOR A8
COL OBJECT_NAME FOR A8
COL LMODE FOR 9999
COL PROGRAM FOR A8
COL CTIME FOR 9999
SELECT S.USERNAME, S.SID, S.SERIAL#, O.OBJECT_NAME, LMODE, V.CTIME, S.PROGRAM
FROM DBA_OBJECTS O, V$LOCKED_OBJECT L, V$SESSION S, V$LOCK V
WHERE O.OBJECT_ID = L.OBJECT_ID
AND L.SESSION_ID = S.SID
AND L.SESSION_ID = V.SID
AND V.TYPE = 'TM'
ORDER BY CTIME DESC;
-行ロック調査
SELECT S.USERNAME, S.SID, S.SERIAL#, O.OBJECT_NAME, LMODE, V.CTIME, S.PROGRAM
FROM DBA_OBJECTS O, V$LOCKED_OBJECT L, V$SESSION S, V$LOCK V
WHERE O.OBJECT_ID = L.OBJECT_ID
AND L.SESSION_ID = S.SID
AND L.SESSION_ID = V.SID
AND V.TYPE = 'TX'
ORDER BY CTIME DESC;
-SQL調査
SELECT A.SQL_TEXT, A.ADDRESS FROM V$SQLAREA A, V$SESSION B WHERE
A.ADDRESS = B.SQL_ADDRESS AND B.SID = ANY(SELECT SID FROM V$LOCK WHERE TYPE IN ('TX', 'TM'));
-統計情報収集（スキーマ単位）
BEGIN
DBMS_STATS.GATHER_SCHEMA_STATS (
ownname => 'スキーマ名',
options => 'GATHER AUTO');
END;
/
-統計情報収集（テーブル単位）
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(
OWNNAME => 'スキーマ名'
,TABNAME => 'テーブル名'
);
END;
/
-統計情報収集日付の確認
SELECT TABLE_NAME NAME, LAST_ANALYZED FROM USER_TABLES ORDER BY TABLE_NAME;
-統計情報の内容確認
SELECT TABLE_NAME, NUM_ROWS, AVG_ROW_LEN, BLOCKS FROM USER_TABLES ORDER BY TABLE_NAME;
-セッションを強制終了する
> SELECT SID||','||SERIAL#||','||STATUS||','||USERNAME FROM V$SESSION WHERE USERNAME = 'C_LIVE';
SID||','||SERIAL#||','||STATUS||','||USERNAME
---------------------------------------------
199,7860,INACTIVE,C_LIVE
213,6979,INACTIVE,C_LIVE
> ALTER SYSTEM KILL SESSION '199,7860'
> ALTER SYSTEM KILL SESSION '213,6979'
-DBID を確認する
$ sqlplus "/ as sysdba"
>select dbid from v$database;
> select username, default_tablespace from dba_users;
> select FILE_NAME, TABLESPACE_NAME from dba_data_files;
-http://www.dbforums.com/archive/index.php/t-1066637.html
> select dbms_metadata.get_ddl('TRIGGER','<name>','<owner>') from dual;
-ユーザ作成
$ sqlplus /nolog
> connect / as sysdba
> create user scott profile default
identified by tiger
default tablespace user01
temporary tablespace temp
account unlock;
grant resource, connect to amashin;
quit;
-UNDO保存を保証する。
> select tablespace_name, retention from dba_tablespaces;
> alter tablespace UNDOTBS1 retention guarantee;
-データファイルの自動拡張を有効化する。
> select file_name, tablespace_name from dba_data_files;
> select autoextensible, tablespace_name from dba_data_files;
> alter database
datafile '/opt/app/oracle/oradata/orcl/undotbs01.dbf'
autoextend on;
-ユーザのアカウントロックを解除する。
-- ロック解除
> alter user SCOTT account unlock;
-- パスワード変更
$ sqlplus scott/tiger
-SCN からタイムスタンプを求める。
> select current_scn scn, scn_to_timestamp(current_scn) timestamp from v$database;
-タイムスタンプから SCN を求める。
> select timestamp_to_scn(systimestamp) scn, systimestamp timestamp from dual;
*SQL*Plus
-CSV書き出し
> set linesize 1000
> set pagesize 0
> set trimspool on
> spool user_data.txt
> select '"'||id||'","'||name||'"' from users;
*シノニム
-PUBLICシノニムを作成する
$ sqlplus "/ as sysdba"
> CREATE PUBLIC SYNONYM emp FOR scott.emp;
-シノニムを削除する
> drop public synonym samplelink;
*データベースリンク
-データベースリンクの定義を確認する
$ export ORACLE_SID=orcl
$ sqlplus "/ as sysdba"
> select owner,db_link,username,host,created from dba_db_links;
-データベースリンクを作成する
$ export ORACLE_SID=orcl
$ sqlplus "/ as sysdba"
> create public database link samplelink
connect to scott identified by tiger using 'orcl.db01.oracle.co.jp';
-データベースリンクを使用する
$ export ORACLE_SID=orcl
$ sqlplus scott/tiger
> select * from emp@samplelink
-データベースリンクを削除する
> drop public database link samplelink;
*スタンドバイ・データベースを rman でバックアップすると、ORA-19573 エラーが発生する
-症状:
ORA-19573: cannot obtain sub-shared enqueue for datafile 6
-原因:
RMAN in version 9.2.0.1 のバグです。(bug 2688591 (TAR 2708247.999))
-処置方法:
SQL> shutdown immediate;
SQL> startup nomount;
SQL> alter database mount standby database;
SQL> alter database recover managed standby database disconnect from session;
-参考情報: http://www.trivadis.ch/Images/standbybackup_en_tcm17-7294.pdf
If you create a new tablespace on the primary database,
perform a logfile switch and want to back up this
tablespace on the standby database,RMAN in version
9.2.0.1 aborts with an error:
RMAN> backup tablespace test;
ORA-19573: cannot obtain sub-shared enqueue for datafile 6
This behavior has now been accepted as bug 2688591 (TAR 2708247.999).
But there is a workaround: stopping and starting the standby database,
after which the backup works correctly.
*export/import
-全データベースモードでエクスポートする
$ export ORACLE_SID=orcl
$ exp system/パスワード file=fulldump.dmp log=fulldump.log full=y
-ユーザーモードでエクスポートする
$ export ORACLE_SID=orcl
$ exp scott/tiger file=scott.dmp owner=scott
-ユーザーモードでインポートする
$ imp scott/tiger file=sccot.dmp fromuser=sccot touser=sccot destroy=y ignore=y
*ORA-19809: リカバリ・ファイルの制限を超えています
-pfile の db_recovery_file_dest_size を増やす。
$ export ORACLE_SID=devel
$ sqlplus "/ as sysdba"
SQL> create pfile='$ORACLE_BASE/admin/$ORACLE_SID/pfile/init$ORACLE_SID.ora' from spfile;
SQL> exit
$ vi $ORACLE_BASE/admin/$ORACLE_SID/pfile/init$ORACLE_SID.ora
-編集した pfile から spfile を作成する。
$ cd $ORACLE_BASE/admin/$ORACLE_SID/pfile
$ mv init$ORACLE_SID.ora init$ORACLE_SID.ora.org
$ sqlplus "/ as sysdba"
SQL> create spfile from pfile='$ORACLE_BASE/admin/$ORACLE_SID/pfile/init$ORACLE_SID.ora';
SQL> startup
-参考:
--[[14 ORA-19400～ORA-24276>http://otndnld.oracle.co.jp/document/products/oracle10g/101/doc_v5/server.101/B12448-02/e19400.htm]]
*SQL*Loader
-コマンド
$ export ORACLE_SID=orcl
$ sqlldr userid=user01/******** control=testtbl.ctl data=testtbl.dat
-シーケンスを使用する([[参考URL>http://fukkey.dyndns.org/pins/ora/021230/51159.html]])
LOAD DATA CHARACTERSET JA16SJIS
INTO TABLE T_COMPANY_RAW
FIELDS TERMINATED BY ','
trailing nullcols
(ID "SEQ_COMPANY_RAW_ID.NEXTVAL",
CORP_TYPE,
NAME,
・
・
FUTURE)
-改行があるデータの流し込み
load data CHARACTERSET JA16SJIS
append
continueif last !='"'
into table test_table
fields terminated by ',' enclosed by '"'
( ID,
NAME,
VALUE,
TESTDATE DATE "YYYYMMDD")
-日付データの流し込み
load data CHARACTERSET JA16SJIS
append into table t_answers
fields terminated by ','
trailing nullcols
( id,
campaign_id,
user_id,
registered "to_date(:registered, 'YYYY/MM/DD HH24:MI:SS')" )
-「データ・ファイルのフィールドが最大長を超えています。」
load data CHARACTERSET JA16SJIS
append into table t_encourage
fields terminated by ','
( ID,
CAT2_ID,
MAILBODY char(4000))
*Oracle 10g R10.2.0.1 インストール (MiracleLinuxV3.0)
-memo
--RPM を更新してから Oracle をインストールする
--DHCP を使用している場合、一時的にTCP/IPの設定で固定IPアドレスを割り当ててから、Oracle をインストールする
-ダウンロード
--[[Oracle 10g R10.2.0.1>http://www.oracle.com/technology/software/products/database/oracle10g/index.html]] をダウンロードする
-rootユーザでログインする
-ランレベルを 5 に変更する
# init 5
-oranavi を起動する
# oranavi
-Oracle DBMS のインストール
--全てデフォルト設定でインストール
*Oracle 9i R9.2.0.4.0 インストール後に必要な作業
-Run the following commands and scripts in order from $ORACLE_HOME in migrate mode on all updated databases. If you are using the OLAP option, then ensure the database listener is up.
$ sqlplus "/ as sysdba"
> startup migrate
> spool patch.log
> @?/rdbms/admin/catpatch.sql
> shutdon immediate
> startup
-This step is optional, and will recompile all invalid PL/SQL packages now instead of when the packages are accessed the first time. The utlrcmp.sql script can be used to parallelize this in multiprocessor machines.
> @?/rdbms/admin/utlrp.sql
-Enter the following commands if using Oracle OLAP option:
> alter user olapsys identified by password account unlock;
> connect olapsys/password
> @?/cwmlite/admin/postamd.sql
*ORA-06512 → [[参考URL>http://lists.suse.com/archive/suse-oracle/2005-Jul/0123.html]]
-現象
ORA-00604: error occurred at recursive SQL level 1
ORA-06521: PL/SQL: Error mapping function
ORA-06512: at "SYS.OLAPIHISTORYRETENTION", line 1
ORA-06512: at line 6
-原因: BUG 3386542
-処置: (metalink Note:266728.1): Disable OLAPISTARTUPTRIGGER and OLAPISHUTDOWNTRIGGER to avoid error from being generated.
ALTER TRIGGER OLAPISTARTUPTRIGGER DISABLE;
ALTER TRIGGER OLAPISHUTDOWNTRIGGER DISABLE;
*rmanでデータベースを複製する
-前提条件
--Miracle Linux V3.0
--Oracle 10g Standard Edition
*ターゲット・データベースへ接続できるようにする
$ vi /opt/app/oracle/product/10.1.0/db_1/network/admin/tnsnames.ora
ACCOUNT1.DB01.ORACLE.CO.JP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.101)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = account1.db01.oracle.co.jp)
(SERVER = DEDICATED)
)
)
ACCOUNT1.RESEARCH01.ORACLE.CO.JP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.102)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = account1.oracle.co.jp)
(SERVER = DEDICATED)
)
)
$ vi /opt/app/oracle/product/10.1.0/db_1/network/admin/listener.ora
(SID_DESC =
(GLOBAL_DBNAME = account1.db01.oracle.co.jp)
(ORACLE_HOME = /opt/app/oracle/product/10.1.0/db_1)
(SID_NAME = account1)
)
$ sqlplus scott/tiger@account1.db02.oracle.co.jp
-ターゲット・データベースのバックアップ
(複製側)
# su - oracle
$ mkdir -p /opt/app/oracle/oradata/account1/backups ← ターゲットデータベース側・複製側の両方で作成する
$ export ORACLE_SID=orcl
$ sqlplus /nolog
> conn / as sysdba
> create user rman identified by rman temporary
tablespace temp default tablespace users quota unlimited on users;
> grant recovery_catalog_owner, connect, resource to rman;
$ rman catalog rman/rman@orcl
> connect target sys/manager@account1.db01.oracle.co.jp;
> create catalog;
> register database;
> configure retention policy to redundancy = 1;
> configure controlfile autobackup on;
> configure controlfile autobackup format for device type disk to '/opt/app/oracle/oradata/account1/backups/%F';
> backup database
> format '/opt/app/oracle/oradata/account1/backups/full_%d_%T_s%s_p%p';
> backup archivelog all delete all input
> format '/opt/app/oracle/oradata/account1/backups/arc_%d_%T_s%s_p%p';
-補助データベースを作成する
$ orapwd file=/opt/app/oracle/product/10.1.0/db_1/dbs/orapwaccount1 password=manager entries=30 force=y
> alter system set remote_login_passwordfile='EXCLUSIVE' scope=spfile;
> shutdown immediate
> startup force nomount
> show parameters remote_login_passwordfile;
$ cd /opt/app/oracle/admin/account1/
$ mkdir pfile bdump cdump create udump
$ cd /opt/app/oracle/admin/account1/pfile
$ vi initaccount1.ora
DB_NAME=account1
CONTROL_FILES=(/opt/app/oracle/oradata/account1/control01.ctl,
/opt/app/oracle/oradata/account1/control02.ctl,
/opt/app/oracle/oradata/account1/control03.ctl)
$ export ORACLE_SID=account1
$ sqlplus "/ as sysdba"
> create spfile from pfile='/opt/app/oracle/admin/account1/pfile/initaccount1.ora';
> startup force nomount
$ lsnrctl start
$ rman target sys/manager@account1.db01.oracle.co.jp auxiliary sys/manager@account1.db02.oracle.co.jp
> duplicate target database to account1 nofilenamecheck;
> duplicate target database for standby nofilenamecheck;
*フラッシュバックドロップ
-ごみ箱の中を見る。
> SELECT original_name, type, ts_name, droptime, dropscn, object_name FROM user_recyclebin;
-テーブルをリストアする。
> flashback table test to before drop;
-インデックスをリストアする
--テーブルをリストアすると、インデックスや制約も復活するが、名前はごみ箱の中の時のままなので、手動でリネームする。
> select index_name from user_indexes where table_name = 'TEST';
> alter index "BIN$ExUXY+Nfh9zgQAB/AQAI6g==$0" rename to PK_TEST;
-ごみ箱を空にする。
> purge dba_recyclebin;
> purge user_recyclebin;
> purge tablespace user01 user test;
> purge table test;
> purge index pk_test;
-ごみ箱を介さずに削除する。
> drop table test purge;
> drop user scott cascade;
> drop tablespace user01 including contents;
*フラッシュバックデータベースを有効にする
-DB_RECOVERY_FILE_DEST、DB_RECOVERY_FILE_DEST_SIZE が設定されているか確認する。
> select name,value from v$parameter where name like 'db_recovery_file_dest%';
-設定されていなければ、設定する。
> alter system set db_recovery_file_dest='/opt/app/oracle/flash_recovery_area' scope =both;
> alter system set db_recovery_file_dest_size = 2147483648 scope = both;
-アーカイブログモードにする。
> shutdown immediate
> startup mount
> alter database archivelog;
> alter database open;
> archive log list;
-フラッシュバックログの保存期間を確認する。
> select name, value from v$parameter where name = 'db_flashback_retention_target';
-フラッシュバックデータベース機能を有効化する。
> shutdown immediate
> startup mount
> alter database flashback on;
> alter database open
> select log_mode, flashback_on from v$database;
-どの時点までフラッシュバックできるか確認する。
> select flashback_size/1024/1024 "SIZE(M)",
estimated_flashback_size/1024/1024 "EST_SIZE(M)",
oldest_flashback_scn, to_char(oldest_flashback_time, 'YYYY-MM-DD HH24:MI:SS')
from v$flashback_database_log;
-フラッシュバックデータの書き込みサイズを確認する。
> select to_char(begin_time, 'HH24') hour,
flashback_data/1024 "FLASHBACK_DATA(K)",
redo_data/1024 "REDO_DATA(K)"
from v$flashback_database_stat;
-フラッシュバックデータベースを実行する
SQL> shutdwon immediate
SQL> startup mount
SQL> flashback database to scn 608964;
SQL> flashback database to time "to_timestamp('06-05-06 22:41:00.059098')";
RMAN> flashback database to scn 608964;
RMAN> flashback database to time "to_timestamp('06-05-06 22:41:00.059098')";
RMAN> flashback database to sequence 1 thread 1;
-データベースをオープンする。
> shutdown immediate
> startup mount
> alter databse open read only; -- 読み取り専用でオープンする場合
> alter database open resetlogs; -- 読み書き可能でオープンする場合
-フラッシュバックログから表領域を除外する
> alter tablespace example flashback off;
> select name, flashback_on from v$tablespace;
*ブロック破損
-ブロック破損の診断
$ dbv file=/opt/app/oracle/oradata/orcl/system01.dbf
SQL> analyze table test validate structure cascade;
SQL> analyze index pk_test validate structure;
-rman で物理破損ブロックを検出する。
RMAN> backup validate datafile 1;
SQL> select * from v$database_block_corruption;
-rman で論理破損ブロックを検出する。
RMAN> backup check logical datafile 1;
-rman で物理破損チェックを行わずにバックアップを行う。
RMAN> backup nochecksum datafile 1;
-破損ブロックを含んだままバックアップをとる。
RMAN> run {
set maxcorrupt for datafile '/opt/app/oracle/oradata/orcl/system01.dbf' to 2;
backup datafile '/opt/app/oracle/oradata/orcl/system01.dbf';
}
-rman によるブロックメディアリカバリ
RMAN> blockrecover datafile 1 block 1;
RMAN> blockrecover corruption list;
*簡単なジョブの作成
-ジョブを作成する
$ export ORACLE_SID=rmanrep
$ sqlplus "/ as sysdba"
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'SYS.BACKUP',
job_type => 'EXECUTABLE',
job_action => '/home/oracle/devel/shell/backup.sh',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=DAILY;BYHOUR=5;BYMINUTE=0',
enabled => TRUE,
auto_drop => FALSE,
comments => 'ORCLデータベースのバックアップ');
END;
/
-ジョブを削除する
BEGIN
DBMS_SCHEDULER.DROP_JOB(job_name => 'sys.backup');
END;
/
*プログラムとスケジュールを使用したジョブの作成
-SCOTTユーザに CREATE JOB 権限を与える
$ export ORACLE_SID=orcl
$ sqlplus "/ as sysdba"
> GRANT CREATE JOB TO SCOTT;
> CONNECT SCOTT/TIGER;
-ストアドプロシージャの作成
CREATE OR REPLACE PROCEDURE test_del(
p_id test.id%TYPE)
AS
BEGIN
DELETE FROM test WHERE ID = p_id;
END test_del;
/
-プログラムの作成
> BEGIN
-- プログラムの作成
DBMS_SCHEDULER.CREATE_PROGRAM(
program_name => 'SCOTT.TEST_PROGRAM',
program_type => 'STORED_PROCEDURE',
program_action => 'SCOTT.TEST_DEL',
number_of_arguments => 1,
enabled => FALSE,
comments => 'TEST表のDELETE');
-- プログラム引数の定義
DBMS_SCHEDULER.DEFINE_PROGRAM_ARGUMENT(
program_name => 'SCOTT.TEST_PROGRAM',
argument_name => 'P_ID',
argument_position => 1,
argument_type => 'NUMBER');
-- プログラムの有効化
DBMS_SCHEDULER.ENABLE('SCOTT.TEST_PROGRAM');
END;
/
-スケジュールの作成
BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE(
schedule_name => 'SCOTT.MONTHLY_SCHEDULE',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=MONTHLY;BYMONTHDAY=15',
comments => '毎月15日実行'
);
END;
/
-プログラムとスケジュールを利用したジョブの作成
> BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'SCOTT.TEST_JOB',
program_name => 'SCOTT.TEST_PROGRAM',
schedule_name => 'SCOTT.MONTHLY_SCHEDULE',
enabled => FALSE,
auto_drop => FALSE,
comments => 'TEST表のDELETE'
);
DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
job_name => 'SCOTT.TEST_JOB',
argument_name => 'P_ID',
argument_value => 10
);
DBMS_SCHEDULER.ENABLE('SCOTT.TEST_JOB');
END;
/
*PL/SQL
-PL/SQL実行後、エラーを表示する。
> SHOW ERROR
-DBMS_OUTPUT.PUT_LINE などでの画面出力を有効にする。
> SET SERVEROUTPUT ON
表示オプション
横に並べて表示：
変化行の前後のみ表示：
