OCP 11gR2: Administration II 練習筆記 (三)

Lession 03
=================================================
01. backup as backupset
- RMAN backup method are [backupset | copy] default is backupset.
- backup command run by serial

- Whole database backup command is "backup database plus archivelog"
  plus archivelog purpose is maintain log folder size.

- backup as copy [datafile|tablespace|database|archivelog];
  only on disk and not in FRA
  show records by "list copy"
  it support incremental backup



02. RMAN backup types
- backup database; <- Whole database backup.
- backup incremental level 0 database; <- Full database backup.

- backup incremental level 1 database; <- Incremental backup base on level 0.
  Level 1 with 2 types [differential  | cumulative] default is defferential

SUN	MON	TUE	WED	THU	FRI	SAT
LV0	LV1	LV1	LV1	LV1	LV1	LV1
D	<------	<------	<------	<------	<------	<------	24HR/DAY
C	<------						24HR/DAY
C	<--------------------						48HR/DAY
C	<----------------------------------						72HR/DAY
C	<------------------------------------------------						96HR/DAY
C	<--------------------------------------------------------------						120HR/DAY
C	<-----------------------------------------------------------------------------						148HR/DAY

03. Fast incremental backup
- not support save log in FRA
- block change tracking default is off
- enable command "alter database enable block change tracking using file 'location';
  after enable, it will not affect on coming incremental backup, it effective on next.

- OMF, parameter db_create_file_dest
  include [datafile, tempfile, redo, controlfile, block change tracking]



04. Proxy copy (OCP exam not test)
- backup to SAN via MML (media manage libarary)



05. create duplexed backup
- duplex support disk and sbt
- duplex not support image
- duplex cannot backup in FRA, must use "FORMAT"
  backup as backup set copies 2 database format '/tmp';
  or by configure



06. backup the backupset and FRA
- move all backupset to sbt by command "backup device type sbt backupset all;"
- backup FRA by command "backup recovery area;" or "backup recovery files;"


07. backup readonly tablespace (do not use this function)
- consider retention policy with backup optimization
- skip readonly no relationship with optimization
  the command is "backup database skip readonly;"



08. Multisection backup
- if datafile very large, use multisection to limited backuppiece size
- fast complete backup with parallel (common use)
- use multisection with parallel command as below:

RMAN> configure device type disk parallelism 4;
run {
allocate channel ch1 device type disk format '/tmp/bak1_%U';
allocate channel ch2 device type disk format '/tmp/bak2_%U';
allocate channel ch3 device type disk format '/tmp/bak3_%U';
allocate channel ch4 device type disk format '/tmp/bak4_%U';
backup section size 500M database; }

- if you have only 1 disk and config parallel to 4, the performance will be affect.



09. Archival backup
- backupset not affect by retention policy
- cannot use on FRA
- change backupset to archival backup as below:
  change backup tag 'name' keep forever;



10. RMAN report & dynamic view
report schema;
report need backup;
report obsolete
v$backup_set;
v$backup_piece;
v$datafile_copy;
v$backup_files;

11. Recovery Non-critical & critical recovery

Tempfile lost:

- Cannot sort table from "order by", also EM will have error

- automatic re-create of tempfile when restart <- if file corrupted, restart cannot fix

- manual re-create:

 "alter tablespace temp add tempfile '/location/temp02.dbf' size 500M autoextend on;"

 "alter tablespace temp drop tempfile '/location/temp01.dbf';"

Loss of a redo log group:

- current log corrupted and instance crashed, need cancel-based point in time recovery.

- current log corrupted but instance not crash. Perform alter log switch and checkpoint.

- inactive & active log corrupted, use clear log command.

        Below command for reference:

        "alter database clear logfile group [1|2|3];"  <- inactive status run, no data loss.

        "alter database clear unarchived logfile group [1|2|3];" <- active status run, when recovery will data loss.

        "alter database clear unarchived logfile group [1|2|3] unrecoverable datafile;" <- if the datafile offline and     need to clear unarchived logfile you need to add unrecoverable datafile

Index tablespace recovery:

- if datafile lose only contains indexes, no need perform recover task.

 Drop datafile, drop the tablespace(index only), re-create the index tablespace, re-create indexes that in tablespace.

- use parallel to reduce time for re-create index

 create index 'name' on 'schema.table'('column') parallel 4;

all controlfile loss

- open resetlogs

Readonly tablespace

- take tablespace offline

- restore tablespace

- bring tablespace online

No logging object

- drop it and re-create

 Use report unrecoverable

 Drop table 'table_name';

 Create table 'table_name' nologging;

 Insert /*+ append */ into 'table_name' select * from 'source_table';

Authentication for database

- SYS can use O/S auth but other role cannot, they are maintenance by password file.

 Recovery or reset

 orapwd filename='/location/name.ora' password='password' enries=10

 re-grant role to user

12. Complete & Incomplete recovery

- complete recovery - no data loss

                                - need datafile, archivelog, redo log and undo tablespace apply

- incomplete recovery - bring database to point in time recovery

                                - no need redo log and some archive missing

                                - undo tablespace apply and resetlogs to open db.

OCP 11gR2: Administration II 練習筆記 (四)
OCP 11gR2: Administration II 練習筆記 (二)

學習初級Oracle PL/SQL (五)

Single Row Processing

現在,將開始使用PL/SQL的insert,update,delete旅程.一次insert一行,然後每次update一行,接著一次delete一行,最後一次select一行.

Inserts

用PL/SQL將數據insert到數據庫中,您只需編寫一個SQL INSERT語句,其中PL/SQL的value是文字,PL/SQL變量或SQL column. 這裡將會舉例常見的做法,然後通過進展持續改進你的設計. 你會明白PL/SQL程序員最常做的, 以及如何改善代碼. 請花多點時間了解這些方案.

正常的時候你很少使用PL/SQL執行一個簡單的INSERT語句, 因為你可以使用SQLPLUS scripts而不使用PL/SQL.但更多的時候,當你要insert或update記錄時發現記錄已經存在於數據庫中,該記錄要insert還是update呢? Sqlplus scripts就不能有更好的做法.

以下是用PL/SQL insert的例子, 為empno建立一個sequence, 在scott.emp 增加一個記錄,並為ename,job,mgr,sal,comm 加入unique key防止重覆值:

create sequence emp_seq start with 8001 increment by 10; alter table emp add constraint UK1_emp unique(ename,job,mgr,sal,comm) using index;

set serveroutput on size 1000000; declare n_empno          emp.empno%type; n_mgr               emp.mgr%type; n_deptno  emp.deptno%type; -- 用n_count來保全insert結果. n_count    number; begin                 -- 假設,我們獲取BLAKE 的mgr number.         begin          select empno into n_mgr from emp where ename='BLAKE';         exception          when others then raise_application_error(-20002, SQLERRM||' get mgr number');         end;         -- 然後獲取sales deptno.         begin          select deptno into n_deptno from dept where dname='SALES';         exception          when others then raise_application_error(-20003, SQLERRM||' get deptno');         end;         -- 然後獲取 empno sequence.         begin          select emp_seq.nextval into n_empno from dual;         exception          when others then raise_application_error(-20004, SQLERRM||' get empno seq');         end;         -- 需要獲取的ID已齊全, 終於可以開始insert.         begin          insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(          n_empno,'SALLY','SALESMAN',n_mgr,SYSDATE,1500,300,n_deptno);                    n_count := sql%rowcount;         exception          when others then raise_application_error(-20005, SQLERRM||' insert record');         end;         sys.dbms_output.put_line(to_char(n_count)||' record(s) inserted.'); end; / commit;

執行效果:

1 record(s) inserted.

PL/SQL procedure successfully completed.

假若再次執行則出現下方錯誤:

declare

*

ERROR at line 1:

ORA-20005: ORA-00001: unique constraint (SCOTT.UK1_EMP) violated insert record

ORA-06512: at line 39

Unique constraint exception -20005 出現.

現在, 如果你要處理這種特殊的exception, 可以使用以下三種策略:

- 使用"DUP_VAL_ON_INDEX"捕捉exception (good).

- 使用額外的PL/SQL代碼來檢測預重複值的存在 (better).

- 使用額外的SQL代碼來檢測預重複值的存在 (best)

讓我們來看看這些解決方案,從good到best.

使用"DUP_VAL_ON_INDEX"捕捉exception

當涉及到處理的代碼, 估計可能會引發一個DUP_VAL_ON_INDEX exception, 捕捉exception是最懶的解決方案.

Oracle 會在任何現有的unique index或unique key檢查table有沒有重覆值. 如果發現重覆,PL/SQL會使用 DUP_VAL_ON_INDEX exception. 那麼,你如何處理這個問題?

-- 承接上一段代碼更改如下 .. …         begin          insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(          n_empno,'SALLY','SALESMAN',n_mgr,SYSDATE,1500,300,n_deptno);                    n_count := sql%rowcount;         exception          when DUP_VAL_ON_INDEX then                 n_count := 0;                 sys.dbms_output.put_line('Found duplicate record in table');          when others then raise_application_error(-20005, SQLERRM||' insert record');         end;         sys.dbms_output.put_line(to_char(n_count)||' record(s) inserted.'); end; /

執行效果:

Found duplicate record in table

0 record(s) inserted.

PL/SQL procedure successfully completed.

以上並沒有出現任何error,因為增加了WHEN DUP_VAL_ON_INDEX 的條件.可以更加好地處理重覆值的錯誤.

使用額外的PL/SQL代碼來檢測預重複值的存在

使用額外的PL/SQL代碼來檢測預重複值的存在是比使用"DUP_VAL_ON_INDEX"捕捉exception來的好,理由如下:

- 您維護您的PL/SQL程序執行的控制.

- 可以有條件地決定如何處理重複值.

- 您可以使用的檢測方式取得這個row的primary key,當發現是重複值,更改insert為update.

現在我們看看怎樣用PL/SQL來檢測重複值(留意綠字):

declare n_empno          emp.empno%type; n_mgr               emp.mgr%type; n_deptno  emp.deptno%type;         -- 設定變量         v_ename           emp.ename%type;         v_job                emp.job%type;         v_mgr               emp.ename%type;         n_sal                emp.sal%type;         n_comm           emp.comm%type; n_count    number;     begin          -- 自定檢查用的變量值          v_ename := 'SALLY';          v_job      := 'SALESMAN';          v_mgr     := 'BLAKE';          n_sal       := 1500;          n_comm  := 300;         --使用 uk1_emp 的條件檢查重複值"UK1_emp unique(ename,job,mgr,sal,comm)"         begin          select count(1) into n_count from emp c1 cross join emp c2 where          c1.ename=v_ename and c1.job=v_job and c2.ename=v_mgr and c1.sal=n_sal and c1.comm=n_comm;         exception          when others then raise_application_error(-20005, SQLERRM||' on detect duplicate');         end;     -- 如果沒有發現重複值     if n_count = 0 then                 -- 獲取BLAKE 的mgr number.         begin          select empno into n_mgr from emp where ename='BLAKE';         exception          when others then raise_application_error(-20002, SQLERRM||' get mgr number');         end;         -- 然後獲取sales deptno.         begin          select deptno into n_deptno from dept where dname='SALES';         exception          when others then raise_application_error(-20003, SQLERRM||' get deptno');         end;         -- 然後獲取 empno sequence.         begin          select emp_seq.nextval into n_empno from dual;         exception          when others then raise_application_error(-20004, SQLERRM||' get empno seq');         end;         -- 需要獲取的資已齊全, 可以開始insert.         begin          insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(          n_empno,v_ename,v_job,n_mgr,SYSDATE,n_sal,n_comm,n_deptno);          n_count := sql%rowcount;         exception          when others then raise_application_error(-20006, SQLERRM||' insert record');         end;     -- 否則     else      n_count := 0;      sys.dbms_output.put_line('Duplicate record found');     end if;     sys.dbms_output.put_line(to_char(n_count)||' record(s) inserted.');     end; / commit;

執行效果:

Duplicate record found

0 record(s) inserted.

PL/SQL procedure successfully completed.

執行結果雖然和DUP_VAL_ON_INDEX一樣, 但是已經能用if…else來控制重複後的處理, 以下例子處理發現重複值後更改為update (留意綠字).

declare v_ename           emp.ename%type; v_job                        emp.job%type; v_mgr               emp.ename%type; n_mgr               emp.mgr%type; n_sal                        emp.sal%type; n_comm           emp.comm%type; n_empno          emp.empno%type; n_deptno  emp.deptno%type; -- n_count number;  -- 由於有2個動作insert和update,所以更改為以下變量儲存結果 n_inserted         number := 0; n_updated                number := 0; begin   -- 自定檢查用的變量值   v_ename       := 'SALLY';   v_job             := 'SALESMAN';   v_mgr            := 'BLAKE';   n_sal             := 1500;   n_comm        := 300;                 --使用 uk1_emp 的條件檢查重複值"UK1_emp unique(ename,job,mgr,sal,comm)"         begin          select c1.empno into n_empno from emp c1 cross join emp c2 where          c1.ename=v_ename and c1.job=v_job and c2.ename=v_mgr and c1.sal=n_sal and c1.comm=n_comm;         exception          when NO_DATA_FOUND then n_empno := NULL;          when others then raise_application_error(-20005, SQLERRM||' on detect duplicate');         end;         if n_empno is NULL then          -- 獲取 empno sequence.          begin           select emp_seq.nextval into n_empno from dual;          exception           when others then raise_application_error(-20004, SQLERRM||' get empno seq');          end;          -- 獲取BLAKE 的mgr number.          begin           select empno into n_mgr from emp where ename='BLAKE';          exception           when others then raise_application_error(-20002, SQLERRM||' get mgr number');          end;          -- 然後獲取sales deptno.          begin           select deptno into n_deptno from dept where dname='SALES';          exception           when others then raise_application_error(-20003, SQLERRM||' get deptno');          end;          -- 需要獲取的資已齊全, 可以開始insert.          begin           insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(           n_empno,v_ename,v_job,n_mgr,SYSDATE,n_sal,n_comm,n_deptno);           n_inserted := sql%rowcount;          exception           when others then raise_application_error(-20006, SQLERRM||' insert record');       end;         else          begin           update emp set sal=n_sal where empno=n_empno;           n_updated := sql%rowcount;          exception           when others then raise_application_error(-20007, SQLERRM||' update record');          end;         end if;         sys.dbms_output.put_line(to_char(n_inserted)||' record(s) inserted.');         sys.dbms_output.put_line(to_char(n_updated)||' record(s) updated.'); end; /

執行效果:

0 record(s) inserted.

1 record(s) updated.

PL/SQL procedure successfully completed.

IF-THEN-ELSE語句的語法是:

[ ] 為非必要

單一條件 IF < condition> THEN -- do this if it's TRUE [ELSE -- do this if it's not TRUE] END IF;

多個條件 IF <condition> THEN -- do this if it's TRUE [ELSIF <condition> THEN -- do this if it's TRUE ELSIF ... ELSE -- do this if it's not TRUE] END IF;

使用額外的SQL代碼來檢測預重複值的存在

這幾乎是最好的,讓SQL執行EXISTS子句做檢測的工作!EXISTS子句被執行時,如果重複值不存在SQL會立刻insert.

以下例子:

-- 先把記錄刪除 delete emp where ename=’SALLY’;

declare v_ename           emp.ename%type; v_job                        emp.job%type; d_hiredate         emp.hiredate%type; n_sal                        emp.sal%type; n_comm           emp.comm%type; n_count            number; begin  v_ename         := 'SALLY';  v_job              := 'SALESMAN';  d_hiredate       := sysdate;  n_sal               := 1500;  n_comm          := 300;         -- 可以簡單使用SQL, 不用PL/SQL         begin          insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)          select emp_seq.nextval,v_ename,v_job,c1.mgr,sysdate,n_sal,n_comm,c2.deptno          from emp c1 cross join dept c2 where c1.ename='BLAKE' and c2.dname='SALES'          and not exists(select 1 from emp c3 where c3.ename=v_ename and c3.job=v_job          and c3.mgr=c1.mgr);          n_count := sql%rowcount;         exception          when others then raise_application_error(-20006, SQLERRM||' on insert');         end;  sys.dbms_output.put_line(to_char(n_count)||' record(s) inserted.'); end; / commit;

執行2次效果:

1 record(s) inserted.

PL/SQL procedure successfully completed.

0 record(s) inserted.

PL/SQL procedure successfully completed.

It’s Your Turn to Insert

你要編寫一個用PL/SQL insert 以下4個員工到 emp:

 - TOM,   ANALYST, JONES, sysdate-10, 2500, null, RESEARCH

 - BILLY, CLERK,   BLAKE, sysdate-5,  800,  null, SALES

 - KELLY, SALESMAN,BLAKE, sysdate-7,  1400, 200,  SALES

 - ALAN,  ANALYST, CLARK, sysdate-30, 3000, null, ACCOUNTING

提示: 你可以使用 Anonymous + Function 完成.

declare n_count number := 0; function add_emp( v_ename           in emp.ename%type, v_job                in emp.job%type, v_mgr               in emp.ename%type, d_hiredate in emp.hiredate%type, n_sal                in emp.sal%type, n_comm           in emp.comm%type, v_dname           in dept.dname%type) return number as begin  insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)  select emp_seq.nextval,v_ename,v_job,c1.mgr,d_hiredate,n_sal,n_comm,c2.deptno  from emp c1 cross join dept c2 where c1.ename=v_mgr and c2.dname=v_dname and  not exists(select 1 from emp c3 where c3.ename=v_ename and c3.job=v_job and  c3.mgr=c1.mgr);   return sql%rowcount; exception  when others then raise_application_error(-20006, SQLERRM||' on insert'); end add_emp; begin  n_count := n_count + add_emp('TOM','ANALYST','JONES',sysdate-10,2500,null,'RESEARCH');  n_count := n_count + add_emp('BILLY','CLERK','BLAKE',sysdate-5,800,null,'SALES');  n_count := n_count + add_emp('KELLY','SALESMAN','BLAKE',sysdate-7,1400,200,'SALES');  n_count := n_count + add_emp('ALAN','ANALYST','CLARK',sysdate-30,3000,null,'ACCOUNTING');  sys.dbms_output.put_line(to_char(n_count)||' add record(s).'); end; /

學習初級Oracle PL/SQL (六)
學習初級Oracle PL/SQL (四)