David's Logger

2017年8月24日星期四

ORACLE HEALTH MONITOR

HEALTH MONITOR 可檢查資料庫健康狀態。在ENTERPRISE MANAGER 或 DATABASE CONTROL 管理網頁，其路徑為：

軟體和支援 ==> 建議程式中心

由建議程式中心==> 選擇檢查程式

2017年8月21日星期一

RMAN 備份的實作

BACKUP的基本觀念

OPEN Backup: (備份時資料庫是開啟的): 資料庫必須是 ARCHIVE MODE
CLOSE Backup: (備份時資料庫是關閉的): 資料庫是 NONARCHIVE MODE
Offline Backup: (備份時資料庫是關閉的): 資料庫是 NONARCHIVE MODE
Partial Backup: (備份部分檔案): 資料庫必須是 ARCHIVE MODE

RMAN BACKUP可以備份下列檔案

Datafiles
Controlfile
Archive redo log files
SPFILE
Backup set pieces

但是RMAN不可以備份下列檔案

Tempfiles
Online redo log files
Password file
Static PFILE
Oracle Net Configuration files

RMAN有三種備份型態

Backup Set: 可採用漸進式備份
Compressed Backup Set: 同上，但經過壓縮
Image Copy: 較占空間，不可採用漸進式備份，也不可備份SPFILE檔案。但是在還原時速度較快

漸進式備份有三方面的優勢：備份所費時間、備份所需空間、對使用者的影響。

BACKUP SETS 是最小的備份單位，比原始檔或是IMAGE COPIES(另一種備份格式) 都來得小。

開始漸進式備份，首先須建立備份基線

backup as backupset incremental level 0 database;

接下來有兩種漸進式備份方式

Differential 備份

backup as backupset incremental level 1 database;

RMAN> backup as backupset incremental level 1 database;

Starting backup at 2017-08-21 13:19:13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=42 device type=DISK
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/oracle/app/oracle/oradata/sedb/system01.dbf
input datafile file number=00003 name=/oracle/app/oracle/oradata/sedb/undotbs01.dbf
input datafile file number=00002 name=/oracle/app/oracle/oradata/sedb/sysaux01.dbf
input datafile file number=00006 name=/oracle/app/oracle/oradata/sedb/TBS_PRETORIA_01.dbf
input datafile file number=00007 name=/oracle/app/oracle/oradata/sedb/TBS_NOVATEKTEST_01.dbf
input datafile file number=00008 name=/oracle/app/oracle/oradata/sedb/TBS_REPAIR_01.dbf
input datafile file number=00010 name=/oracle/app/oracle/oradata/sedb/STORETABS_01.dbf
input datafile file number=00011 name=/oracle/app/oracle/oradata/sedb/STORETABS_02.dbf
input datafile file number=00005 name=/oracle/app/oracle/oradata/sedb/TBS_PROCHUB_01.dbf
input datafile file number=00009 name=/oracle/app/oracle/oradata/sedb/TBS_PRE_IND_01.dbf
input datafile file number=00013 name=/oracle/app/oracle/oradata/sedb/vpd_admin.dbf
input datafile file number=00012 name=/oracle/app/oracle/oradata/sedb/TBS_NEWTBS_01.dbf
input datafile file number=00004 name=/oracle/app/oracle/oradata/sedb/users01.dbf
channel ORA_DISK_1: starting piece 1 at 2017-08-21 13:19:14
channel ORA_DISK_1: finished piece 1 at 2017-08-21 13:21:00
piece handle=/oracle/app/oracle/flash_recovery_area/PRISEDB/backupset/2017_08_21/o1_mf_nnnd1_TAG20170821T131914_dspj23nb_.bkp tag=TAG20170821T131914 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:46
Finished backup at 2017-08-21 13:21:00

Starting Control File and SPFILE Autobackup at 2017-08-21 13:21:00
piece handle=/oracle/app/oracle/flash_recovery_area/PRISEDB/autobackup/2017_08_21/o1_mf_s_952608060_dspj5gc3_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2017-08-21 13:21:03

Cumulative 備份

backup as backupset incremental level 1 cumulative database;

RMAN> backup as backupset incremental level 1 cumulative database;

Starting backup at 2017-08-21 13:21:51
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental level 1 datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/oracle/app/oracle/oradata/sedb/system01.dbf
input datafile file number=00003 name=/oracle/app/oracle/oradata/sedb/undotbs01.dbf
input datafile file number=00002 name=/oracle/app/oracle/oradata/sedb/sysaux01.dbf
input datafile file number=00006 name=/oracle/app/oracle/oradata/sedb/TBS_PRETORIA_01.dbf
input datafile file number=00007 name=/oracle/app/oracle/oradata/sedb/TBS_NOVATEKTEST_01.dbf
input datafile file number=00008 name=/oracle/app/oracle/oradata/sedb/TBS_REPAIR_01.dbf
input datafile file number=00010 name=/oracle/app/oracle/oradata/sedb/STORETABS_01.dbf
input datafile file number=00011 name=/oracle/app/oracle/oradata/sedb/STORETABS_02.dbf
input datafile file number=00005 name=/oracle/app/oracle/oradata/sedb/TBS_PROCHUB_01.dbf
input datafile file number=00009 name=/oracle/app/oracle/oradata/sedb/TBS_PRE_IND_01.dbf
input datafile file number=00013 name=/oracle/app/oracle/oradata/sedb/vpd_admin.dbf
input datafile file number=00012 name=/oracle/app/oracle/oradata/sedb/TBS_NEWTBS_01.dbf
input datafile file number=00004 name=/oracle/app/oracle/oradata/sedb/users01.dbf
channel ORA_DISK_1: starting piece 1 at 2017-08-21 13:21:52
channel ORA_DISK_1: finished piece 1 at 2017-08-21 13:23:47
piece handle=/oracle/app/oracle/flash_recovery_area/PRISEDB/backupset/2017_08_21/o1_mf_nnnd1_TAG20170821T132151_dspj70kr_.bkp tag=TAG20170821T132151 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:55
Finished backup at 2017-08-21 13:23:47

Starting Control File and SPFILE Autobackup at 2017-08-21 13:23:47
piece handle=/oracle/app/oracle/flash_recovery_area/PRISEDB/autobackup/2017_08_21/o1_mf_s_952608228_dspjbntn_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2017-08-21 13:23:51

不管哪種方式都會產生兩個BACKUPSET，第一個是所有的DATAFILES，第二個是CONTROL FILE 及 SPFILE

當進行第二次的漸進式備份時，出現下列錯誤

channel ORA_DISK_1: starting piece 1 at 2017-08-21 16:27:41
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 08/21/2017 16:32:07
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 52428800 bytes disk space from 5218762752 limit

透過TOAD發現 FLASH_RECOVERY_FILE_DEST_SIZE 的大小太小了

其大小原為

修正後為

alter system set db_recovery_file_dest_size=5G;

修正完畢後可以存放兩組漸進式的備份

Backupset 備份

      backup as compressed backupset filesperset 4 database;
-- 將BACKUPSET 分為四個檔案儲存
      backup as compressed backupset archivelog all delete all input;
-- 將ARCHIVELOG 壓縮儲存
      backup as backupset format '/backup/orcl/df_%d_%s_%p' tablespace g1_tabs;
-- 備份表格空間 gl_tabs 
      backup as compressed backupset datafile 4;
-- 以編號命名 Datafile ，並壓縮後儲存
      backup as backupset archivelog like '/u01/archivel/arch_1%';
-- 搜尋目錄內符合PATTERN的ARCHIVE LOG，並加以備份

漸進式備份雖然可產生較小的備份檔案，但是備份較花時間，原因是它必須花時間掃描全部的DATAFILE。

BLOCK CHANGE TRACKING 技術，在背景執行一支程序，稱為 Change Tracking Writing (CTW)，它將每個BLOCK更動的紀錄，紀載在一個稱之為 change tracking file 的 DBF 檔案上

以下命令開啟 CTW 程序，並將BLOCK變動紀錄紀載到所指定到的DBF檔案

以下參考文章或是 OCA OCP Oracle Database 11g All-in-One Exam Guide Chapter 15

alter database enable block change tracking using file '/oracle/app/oracle/oradata/sedb/change_tracking.dbf';

CTW 狀態和紀錄檔可由 V$BLOCK_CHANGE_TRACKING 來檢視

select * from v$block_change_tracking;

下列SQL查詢是否 CTW 已經啟動

select program from v$process where program like '%CTWR%';

啟動 CTW 程序以後，漸進式備份所需的時間降低了。 BLOCKS_READ 與 DATAFILE_BLOCKS 的比例由 1 降到 1 以下

以下SQL可以驗證，開啟CTW以後，資料庫SCAN讀取DATAFILE的比例降低到 1 以下

select file#, datafile_blocks, (blocks_read / datafile_blocks) * 100
    as pct_read_for_backup from v$backup_datafile
    where used_change_tracking='YES' and incremental_level > 0

關閉 CTW 則透過以下指令

SQL> alter database disable block change tracking;

建立保留長期的備份 ARCHIVAL BACKUPS

長期備份，例如，建立一個保留90天的備份檔，該備份不受 DELETE OBSOLETE，也不受 RETENTION POLICY 的影響

語法如下:

backup database as compressed backup set keep until time 'sysdate + 90' restore point quarterly_backup;

RMAN 備份的管理

可以運用LIST及REPORT，配合DELETE指令處理

list backup;
list copy;
list backup of database;
list backup of datafile 1;
list backup of tablespace users;
list backup of archivelog all;
list copy of archivelog from time='sysdate - 7';
list backup of archivelog from sequence 1000 until sequence 1050;
list backup of spfile;
list backup of controlfile;

report schema
report need backup;
report need backup days 3;
report need backup redundancy 3;
report obsolete;
delete obsolete;
report obsolete redundancy 2;
delete obsolete redundancy 2;
delete backupset 4;
delete copy of datafile 6 file6_extra;

RMAN 相關的 DYNAMIC VIEW

下列的 DYNAMIC VIEW 提供比LIST/REPORT命令更多的資訊，來管理備份

select * from  v$backup_files  ;
select * from  v$backup_set  ;
select * from  v$backup_piece   ;
select * from  v$backup_redolog  ;
select * from  v$backup_spfile  ;
select * from  v$backup_datafile  ;
select * from  v$backup_device  ;
select * from  v$rman_configuration  ;

備份的空間使用狀況可由 V$FLASH_RECOVERY_AREA_USAGE 來顯示，例如

select * from v$flash_recovery_area_usage;

2017年8月18日星期五

MTTR平均修復時間參數的調整

FAST_START_MTTR_TARGET 預設值為 0，因此系統預設以最高效能來運行。

上述參數有系統建議的可行數值

使用 EM ，在管理網頁上可以看到它的建議程式 MTTR ADVISOR，由它來建議可行數字

2017年8月17日星期四

FLASHBACK 處理誤刪資料之應用

處理DELETE 誤刪:

select count(*) from empttt; ==> 108筆

delete from empttt; --刪除

commit; --COMMIT

以下回復五分鐘前的刪除資料

insert into empttt (select * from empttt as of timestamp(sysdate - 5/1440));

commit;

select count(*) from empttt; ==> 108筆

處理DROP 誤刪:

從10g版本開始，當使用者下達DROP命令時，並不會直接刪除TABLE，僅僅只是將原有的TABLE改名，讓使用者無法存取。

drop table empttt;

flashback table empttt to before drop;

2017年8月9日星期三

EXPLAIN PLAN 的運用

EXPLAIN PLAN 用於分析SQL執行之效能

在執行前，必須由SYS進行授權

以下以SYS身分執行

GRANT SELECT ON v$session TO hr;
grant select on v$sql_plan_statistics_all to hr;
grant select on v$sql_plan to hr;
grant select on v$sql to hr;

OR

GRANT SELECT ON v_$session TO hr;
grant select on v_$sql_plan_statistics_all to hr;
grant select on v_$sql_plan to hr;
grant select on v_$sql to hr;

以下使用HR身分執行

explain plan for
select d.department_name, avg(e.salary)
from departments d, emp e
where d.department_id=e.department_id
group by d.department_name;

select plan_table_output
from table(dbms_xplan.display(NULL,NULL,'basic'));
OR
select plan_table_output
from table(dbms_xplan.display_cursor(NULL,NULL,'basic'));

產生結果如下圖

但是上述PLAN TABLE 通常難以閱讀，可以先將要分析的SQL加以編號，再依據編號，直接搜尋該編號，以SQL查詢。

explain plan 
set statement_id='foo01'
for
select d.department_name, avg(e.salary)
from departments d, emp e
where d.department_id=e.department_id
group by d.department_name;

另外一種方式，是在SELECTION裡面加上HINT

explain plan 
set statement_id='foo02'
for
select /*+GATHER_PLAN_STATISTICS */ d.department_name, avg(e.salary)
from departments d, emp e
where d.department_id=e.department_id
group by d.department_name;

然後使用SQL查詢PLAN_TABLE_OUTPUT (參考此文章 )

select plan_id,
       operation,
       options,
       cost,
       cpu_cost,
       io_cost,
       temp_space,
       access_predicates,
       bytes,
       object_name,
       object_alias,
       optimizer,
       object_type
from plan_table
start with parent_id is null and statement_id = 'foo01'
connect by prior id = parent_id;

產生的結果如下圖所示

額外的資訊表示方式如下

select plan_table_output from 
       TABLE(DBMS_XPLAN.DISPLAY('plan_table',null,'basic +predicate +cost'));  
OR
select plan_table_output from 
       TABLE(DBMS_XPLAN.DISPLAY('plan_table',null,'typical -cost -bytes')); 
OR
select plan_table_output from 
       TABLE(DBMS_XPLAN.DISPLAY('plan_table',null,'basic +note'));

2017年8月8日星期二

使用 DBMS_HPROF 對PLSQL程式進行側寫分析

DBMS_HPROF 是 ORACLE 的側寫分析工具，要順利執行須具備下列條件:

執行 dbmshptab.sql (Console)
以SYS身分登入，建立目錄物件 (TOAD)
以SYS身分執行側寫分析，在目錄物件所指定的目錄產生 TRC 檔案 (TOAD)
使用CONSOLE命令，分析 TRC 檔案，產生 HTML

1. 執行 dbmshptab.sql ，將會產生下列物件

TABLE: dbmshp_runs, dbmshp_function_info, dbmshp_parent_child_info

SEQUENCE: dbmshp_runnumber

上述SQL位置在 /oracle/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/

必須在DB主機，使用 SQLPLUS 以 SYSDBA 身分執行

執行完畢後會產生表格如下

2. 以SYS身分登入，建立目錄物件 (TOAD)

   CREATE OR REPLACE DIRECTORY HPROF_DIR AS 
'/home/oracle/logs/';

3. 以SYS身分執行側寫分析，在目錄物件所指定的目錄產生 TRC 檔案 (TOAD)

假設在HR中，欲對某PROCEDURE側寫分析，該PROCEDURE如下(以HR身分)

   
CREATE OR REPLACE PROCEDURE HR.hprof_test 
IS 
BEGIN 
    FOR v_Lp IN 1..4 LOOP 
       simple_procedure;   --呼叫另一PROCEDURE
    END LOOP; 
END hprof_test;

側寫分析的區塊必須以下列指令包圍之

DBMS_HPROF.START_PROFILING('目錄物件', 'TRC檔案'); 及

DBMS_HPROF.STOP_PROFILING;

以下以SYS身分

   
BEGIN 
    DBMS_HPROF.START_PROFILING('HPROF_DIR', 'hprof_test.trc'); 
    hprof_test; 
    DBMS_HPROF.STOP_PROFILING; 
END;

側寫完畢，以SYS身分在TOAD執行DBMS_HPROF.ANALYZE，它會回傳一個NUMBER，可用該回傳值，從dbmshp_runs, dbmshp_function_info, dbmshp_parent_child_info 這三個TABLE中，撈取所要的資料

   
DECLARE 
   v_hprun     NUMBER; 
BEGIN 
   v_hprun := DBMS_HPROF.analyze(
      LOCATION  => 'HPROF_DIR',          -- 目錄物件
      FILENAME  => 'hprof_test.trc');    -- TRC檔案
   DBMS_OUTPUT.PUT_LINE('v_hprun: ' || v_hprun); 
END;

執行完DBMS_HPROF.ANALYZE以後，回傳值及訊息如下:

三個分析表如下

DBMSHP_RUNS

   

CREATE TABLE SYS.DBMSHP_RUNS
(
  RUNID               NUMBER,
  RUN_TIMESTAMP       TIMESTAMP(6),
  TOTAL_ELAPSED_TIME  INTEGER,
  RUN_COMMENT         VARCHAR2(2047 BYTE)
)
TABLESPACE SYSTEM

DBMSHP_FUNCTION_INFO

   
CREATE TABLE SYS.DBMSHP_FUNCTION_INFO
(
  RUNID                  NUMBER,
  SYMBOLID               NUMBER,
  OWNER                  VARCHAR2(32 BYTE),
  MODULE                 VARCHAR2(32 BYTE),
  TYPE                   VARCHAR2(32 BYTE),
  FUNCTION               VARCHAR2(4000 BYTE),
  LINE#                  NUMBER,
  HASH                   RAW(32)                DEFAULT NULL,
  NAMESPACE              VARCHAR2(32 BYTE)      DEFAULT NULL,
  SUBTREE_ELAPSED_TIME   INTEGER                DEFAULT NULL,
  FUNCTION_ELAPSED_TIME  INTEGER                DEFAULT NULL,
  CALLS                  INTEGER                DEFAULT NULL
)
TABLESPACE SYSTEM

DBMSHP_PARENT_CHILD_INFO

   
CREATE TABLE SYS.DBMSHP_PARENT_CHILD_INFO
(
  RUNID                  NUMBER,
  PARENTSYMID            NUMBER,
  CHILDSYMID             NUMBER,
  SUBTREE_ELAPSED_TIME   INTEGER                DEFAULT NULL,
  FUNCTION_ELAPSED_TIME  INTEGER                DEFAULT NULL,
  CALLS                  INTEGER                DEFAULT NULL
)
TABLESPACE SYSTEM

執行完DBMS_HPROF.ANALYZE以後，回傳值的RUNID為1 ，因此透過該 RUNID 來搜尋合乎該 RUNID 的資料

   
SELECT run_timestamp, total_elapsed_time
FROM   dbmshp_runs where runid = 1;

結果如下

   
SELECT owner, type, function, line#,  
   subtree_elapsed_time AS ST_TIME,  
   function_elapsed_time AS FN_TIME, 
   calls FROM   dbmshp_function_info 
   where runid = 1;

結果如下

   
SELECT parentsymid, childsymid,  
   subtree_elapsed_time AS ST_TIME, 
   function_elapsed_time as FN_TIME, 
   calls FROM   dbmshp_parent_child_info 
   where runid = 1;

結果如下

4. 使用CONSOLE命令，分析 TRC 檔案，產生 HTML

CONSOLE 命令列的指令是：PLSHPROF，

產生出的HTML檔案如下所示

在WIN8 64bits上安裝使用PLSQL DEVELOPER

PLSQL DEVELOPER 是32位元應用程式，如果要使用它來連接 64 位元的ORACLE 資料庫

需設定以下參數： TNS_ADMIN、NLS_LANGUAGE，並且須將OCI.DLL路徑設定於PATH參數中

TNS_ADMIN:

NLS_LANGUAGE:

PATH:

OCI.DLL 在上述路徑中，該檔案必須被PLSQL DEVELOPER 找到，才能正確執行

訂閱：文章 (Atom)

David's Logger

ORACLE DB 的學習者們

2017年8月24日星期四

ORACLE HEALTH MONITOR

2017年8月21日星期一

RMAN 備份的實作

BACKUP的基本觀念

RMAN有三種備份型態

接下來有兩種漸進式備份方式

Differential 備份

Cumulative 備份

Backupset 備份

建立保留長期的備份 ARCHIVAL BACKUPS

RMAN 備份的管理

RMAN 相關的 DYNAMIC VIEW

2017年8月18日星期五

MTTR平均修復時間參數的調整

2017年8月17日星期四

FLASHBACK 處理誤刪資料之應用

2017年8月9日星期三

EXPLAIN PLAN 的運用

2017年8月8日星期二

使用 DBMS_HPROF 對PLSQL程式進行側寫分析

在WIN8 64bits上安裝使用PLSQL DEVELOPER

CODEBOOK

ORACLE DB 的學習者們

2017年8月24日 星期四

2017年8月21日 星期一

BACKUP的基本觀念

RMAN有三種備份型態

接下來有兩種漸進式備份方式

Differential 備份

Cumulative 備份

Backupset 備份

建立保留長期的備份 ARCHIVAL BACKUPS

RMAN 備份的管理

RMAN 相關的 DYNAMIC VIEW

2017年8月18日 星期五

2017年8月17日 星期四

2017年8月9日 星期三

2017年8月8日 星期二

CODEBOOK

2017年8月24日星期四

2017年8月21日星期一

2017年8月18日星期五

2017年8月17日星期四

2017年8月9日星期三

2017年8月8日星期二