Oracle

    
※上記の広告は60日以上更新のないWIKIに表示されています。更新することで広告が下部へ移動します。

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; 
> 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;
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

SQL*Loader

  • コマンド
$ export ORACLE_SID=orcl
$ sqlldr userid=user01/******** control=testtbl.ctl data=testtbl.dat
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 をインストールする
  • ダウンロード
  • 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
|新しいページ|検索|ページ一覧|RSS|@ウィキご利用ガイド | 管理者にお問合せ
|ログイン|