az @Wiki

Oracle

最終更新：2007年06月13日 22:58

- view

管理者のみ編集可

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

SQL*Loader

コマンド

$ export ORACLE_SID=orcl
$ sqlldr userid=user01/******** control=testtbl.ctl data=testtbl.dat

シーケンスを使用する(参考URL)

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 をダウンロードする
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

現象

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

「Oracle」をウィキ内検索

az @Wiki

記事メニュー

メニュー

人気記事ランキング

Linux

最近更新されたページ

人気Wikiランキング

atwikiでよく見られているWikiのランキングです。新しい情報を発見してみよう！

全体ページランキング

最近アクセスの多かったページランキングです。話題のページを見に行こう！