Materialized view + DB link

BARRYDB

• user: barry
• table: t
• db-link: to_tpktst

TPKTST

• user: flash
• db-link: to_barry

BARRYDB side

barry

create materialized view log on mv_test with rowid including new values;

TPKTST side

sysdba

flash 

Job

example

1.
create table t(
  id   varchar2(30),
  name varchar2(30)
);

2.
create or replace procedure proce_t is
begin
  insert into t(id, name) values('1', to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'));
   commit;
end proce_t;
/

3.
declare
job number;
begin
  sys.dbms_job.submit(job => job,
                      what => 'proce_t;',
                      next_date => to_date('02-05-2017 13:15:00', 'dd-mm-yyyy hh24:mi:ss'),
                      interval => 'sysdate+1/24/60');
  commit;
end;
/

4.
select job, next_date, next_sec, failures, broken from user_jobs;

Database Link 結合 Data Pump

將遠端資料庫資料透過 Data Pump Import 直接匯入本地資料庫

將會匯入遠端資料庫barry下所有的物件

Data Pump

Export&Import

Merged Incremental Backup

熱備份還原應用練習

情境:
使用者不小心truncate table, 在不關閉資料庫的情況下, 將資料庫的備份帶到別的機器進行point-in-time還原, 找回table

模擬練習

• 第一部分 16:26 copy backup
• 第二部分 16:35 create table
• 第三部分 16:38 truncate table
• 第四部分 16:45 point-in-time recovery to 16:37

第一部分(source_db)
RMAN> backup as copy database;

第二部分(source_db)
SQL> create table AAA ( ID INT, NAME VARCHAR(10));
SQL> insert into AAA values (1, barry);

第三部分(source_db)
SQL> truncate table AAA;

第四部分
(source_db)
SQL> alter system switch logfile;
SQL> alter database backup controlfile to '指定路徑';
SCP copybackup, archived log, control file to target_db;

(target_db)
create pfile like this

SQL> startup mount pfile=xxxxx;
RMAN> catalog start with 'copybackup路徑';
RMAN> list datafilecopy all;
RMAN> switch database to copy;
RMAN> report schema;

RMAN> catalog start with 'archived log路徑';
RMAN> recover database until time "to_date('04 14 2017 16:37:00','MM DD YYYYHH24:MI:SS')" noredo;

因為control file紀錄的redo log file及temp file位置是source_db的因此必須利用rename重設
(source_db)
SQL> select member from v$logfile

(target_db)
建立存放資料夾 ex: ~/fra/redolog ~/fra/tmpfile
RENAME
SQL> alter database rename file '/u01/oracle/11gr2/oradata/BARRYDB/redo01.log' to '/home/oracle/fra/redolog/redo01.log';

SQL> alter database rename file '/u01/oracle/11gr2/oradata/BARRYDB/redo02.log' to '/home/oracle/fra/redolog/redo02.log';
SQL> alter database rename file '/u01/oracle/11gr2/oradata/BARRYDB/redo03.log' to '/home/oracle/fra/redolog/redo03.log';

SQL> alter database rename file '/u01/oracle/11gr2/oradata/BARRYDB/temp01.dbf' to '/home/oracle/fra/tmpfile/temp01.dbf';

開啟資料庫
SQL> alter database open resetlogs;

Hot backup and Recovery

狀況:
在master不Shutdown A資料庫的情況下，把A資料庫備份出來，並在slave將A資料庫還原。

1. 備份A
   RMAN> backup as copy database;
2. 在A建個table並新增幾筆資料，記得commit
3. 在A產生最新的archived log
   SQL> alter system switch logfile;
4. 複製A的control file
   SQL> alter database backup controlfile to '指定路徑';
5. 將備份檔、archived log 及 control file SCP至slave
6. 在slave建立pfile
   例:
   
7. slave DB mount
   SQL> startup mount pfile=___;
8. slave catalog
   RMAN> catalog start with 'copy backup路徑'; (記得輸入yes)
   RMAN> list datafilecopy all;
   RMAN> switch database to copy;
   RMAN> report schema;
   
   RMAN> catalog start with 'archived log路徑';
   RMAN> recover database;
   (可能失敗 則RMAN> recover database noredo;)
   
   SQL> alter database open resetlogs;
   

Cold Backup

1. TESTDB產生contral trace檔
   SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE
   找到檔案後複製出來進行更改
   
   製作成control file產生檔 xxx.sql
2. Shutdown database 使所有資料檔的SCN一致
3. 在$ORACLE_BASE/oradata目錄下創建CLONEDB資料夾，並將所有資料檔複製過去
   oradata]$ mkdir CLONEDB
   TESTDB]$ cp -r *.dbf $ORACLE_BASE/oradata/CLONEDB
4. pfile所有TESTDB改成CLONEDB (:%s'TESTDB'CLONEDB'g)
   db_name 改回TESTDB
   新增 db_unique_name=CLONEDB
   新增 instance_name=CLONEDB
   
5. 更改環境變數 export ORACLE_SID=CLONEDB
6. SQL> startup nomount pfile=路徑;
7. 製作control file: SQL> @xxx.sql檔路徑
8. 開啟database: SQL> alter database open resetlogs;
9. SQL>  ALTER SYSTEM ARCHIVE LOG ALL;
10. 建立TEMP: SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/oracle/11gr2/oradata/CLONEDB/temp01.dbf';

Point-In-Time Recovery

• 在做Point-In-Time Recovery時設定的還原時間點,必須在備份完成之後的時間。舉例來說，今天早上8:00進行備份，到早上10:00完成備份，則還原時間點必須設定在10:00之後。如果設定在8:00~10:00之間，那些部分還沒備份完成的資料，就會從上一次的備份進行還原(recover)，變得很麻煩。

練習 (DML操作記得commit，這樣紀錄才會寫進redo log)

備份

1. RMAN> backup as backupset database spfile;
2. 2017/4/12 早上09:00:00 完成備份

事件

1. Insert幾筆資料至Table - 2017/4/12 早上10:00:00
2. Drop Table - 2017/4/12 早上11:00:00

老闆: 哪個笨蛋把TABLE刪了?我10點輸入的資料都不見了! MIS! MIS!快給我處理一下

還原

1. SQL> shutdown immediate;
2. SQL> startup mount;
3. RMAN> restore database;
4. 恢復至TABLE刪除之前
   RMAN> recover database until time "to_date('04 12 2017 10:50:00','MM DD YYYYHH24:MI:SS' )";
5. SQL> alter database open resetlogs;

要注意的是Point-In-Time就像是回到過去的某個時間點，所以在那時間點之後的事都會丟失。就像The Flash回到過去一樣，會開出一條新的時間軸(Incranation)，因此必須評估是否值得這樣做，如果只是回復小事情可以用Flashback功能。

Create Control File

1.  SQL> alter database backup controlfile to trace;
2. 到產生Warn log的地方找檔案
   $ORACLE_BASE/diag/rdbms/testdb/TESTDB/trace
   找到內容
   
3. 複製一份取個名xxx.sql
4. 至spfile存放地址 $ORACLE_HOME/dbs
   進入SQL產生pfile
   SQL> create pfile from spfile;
   SQL> exit;
5. 避免產生的control file覆蓋掉原本的，修改pfile initTESTDB.ora內的control file destination
6. 在pfile目錄進入database
   SQL> startup nomount pfile=initTESTDB.ora;
   執行xxx.sql
   SQL> @$ORACLE_BASE/diag/rdbms/testdb/TESTDB/trace/xxx.sql;
7. SQL> shutdown immediate;
8. SQL> startup mount pfile=initTESTDB.ora;
9. SQL>  ALTER SYSTEM ARCHIVE LOG ALL;
10. SQL>  ALTER DATABASE OPEN;
11. SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/oracle/11gr2/oradata/TESTDB/temp01.dbf';

Restore and Recover

RESTORE(還原) command: Restores database files from backup.
RECOVER(恢復) command: Recovers restored files by applying changes recorded in the redo log files.

• 完全恢復(complete recovery)與不完全恢復(incomplete recovery)
• Complete recovery
  Bring the database to the state where it is fully up-to-date, including all committed data modifications to the present time.
• Incomplete recovery
  Bring the database to some point in the past. This means there are missing transactions; any data modifications done between the recovery destination time and the present are lost. In many cases, this is the desirable goal because there may have been some changes made to the database that need to be undone. Recovering to a point in the past is a way to remove the unwanted changes.

Obsolete、Expired

• Obsolete
  根據保留策略而被丟棄時，就會被標記為Obsolete。
  例如:
  設定保留策略
  RMAN> configure retention policy to recovery window of 4 days;
  因此5天前的備份都會被標記為Obsolete。
• Expired
  Crosscheck對備份進行校驗，當備份儲存在RMAN目錄中，但是找不倒備份實體的檔案。
  例如:
  Our controlfile thinks that there is a backup under a directory with a name but someone delete this file from operating system. We can run crosscheck command to check if these files are exist and if rman found a file is missing then mark that backup record as expired which means is no more exists.

REDO、UNDO

• REDO
  
  當使用DML指令時，會將我們對資料修改的操作及資料本身寫入Redo Log Buffer在適當的時機(註1)再透過LGWR寫入Redo Log File，然後把資料放進buffer cache再透過DBWR寫入datafile。

• UNDO
  
  當我們進行交易時，Oracle會利用Undo Segment來存放異動前後的資料，在交易未Commit前，其他使用者可以在這裡查詢舊資料，如果交易失敗或取消，會回復原先資料。

註1:

1. 交易確認時
2. Redo Log Buffer的資料異動量放超過整個Buffer的1/3
3. Redo Log Buffer的資料異動量超過1MB
4. 當DBWR將異動的Data Block從Data Buffer Cache寫入Data Files之前

使用Recovery Manager-2

Catalog


設置儲存器來儲存備份資訊

舉個例子，今天我想在甲地備份，乙地還原，即使乙地有甲地的備份檔案，但沒有備份的matadata，這樣子RMAN在乙地是看不懂這些檔案的。

當RMAN開啟目標資料庫(甲地)及CATALOG資料庫的連線時，會同步備份資訊，當要在乙地進行還原時就可以透過catalog看懂甲地的備份檔案。

• 在存放catalog的資料庫設定
• 設定表格空間
  SQL> create tablespace rman datafile '+DATA' size 10m autoextend on next 5m;
• 創帳號
  SQL> create user rman identified by rman default tablespace rman;
• 設定帳號權限
  SQL> grant resource, connect, recovery_catalog_owner to rman;
• 設定連線
• 設定目標資料庫的tnsnames.ora

TPKTST(連線用的alias) =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = [catalog資料庫ip])(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = TPKTST)

    )

  )

• 初始化
  $ rman catalog rman/rman@tpktst
RMAN> create catalog;
• 註冊資料庫
  $ rman target / catalog rman/rman@tpktst
RMAN> register database;

使用Recovery Manager-1

RMAN的功能及元件

• RMAN的元件
• 在沒有連線到復原目錄表的狀況下，使用作業系統認證來啟動RMAN
  $ rman target /
• 復原目錄表可以記錄一個或多個目標資料庫
• RMAN與傳統備份方式的比較
• 備份的類型
• 一致的備份:

1. 所有的資料檔都有相同的SCN 
2. 通常只有在資料庫正常關閉或MOUNT狀態中時，才會採用一致性的備份

• 不一致的備份:

1. 資料庫開啟，有使用者存取的狀態下來使用的方式
2. 在不一致備份發生時，資料檔與SCN通常是不一致的，使用不一致備份方式來執行的復原作業，必須仰賴archive log與online redo log，來將資料庫帶回到開啟前的一致狀態下

• 完整備份:

1. 所有表格空間或資料庫中的資料檔區塊，以位元為基礎來逐一複製一個或多個資料檔

• 遞增備份:

1. 遞增備份可以是層級0或層級1
2. 在復原決策中使用遞增備份最大的好處是，不需要使用archive log與online redo log就能重置資料庫或表格空間到一致性的狀態下

• 影像複本:

1. 由作業系統指令，或是RMAN backup as copy指令所建立的完整備份。
2. 影像複本是RMAN的預設備份檔案格式

• 備份集與備份片段:

1. 資料庫全部或部分的RMAN備份，由一個或多個備份片段所組成
2. 每個備份片段只能歸屬一個備份集，可以包含一個或多個資料檔

• 壓縮備份:

1. 降低磁碟或磁帶所用來儲存備份的空間需求
2. 只適用於RMAN， 不需要任何特殊處理就可以用來進行復原操作，RMAN會自動解壓縮備份
3. 建立壓縮備份只要使用RMAN backup指令中的as compressed backupset即可

實體資料庫配置與儲存管理-1

調整表格空間和資料檔的大小

• 使用ALTER DATABASE來調整smallfile表格空間大小
• 避免經常手動調整表格空間大小，在修改或建立資料檔時使用autoextend、next和maxsize。
• autoextend - 設為on時，資料檔是可擴充的。
• next<size> - 當資料需要擴充時，下一個分配磁碟空間的容量，單位是bit。<size>可以用K、M、G、T表示。
• maxsize<size> - 設為unlimited時，資料檔是無限制的，最大可到8EB。或者   用maxsize設定資料檔的最大bit。<size>可以用K、M、G、T表示。

• 使用ALTER TABLESPACE來調整bigfile表格空間
• 建立bigfile表格空間
  create bigfile tablespace dmarts
datafile '/u05/oradata/dmarts.dbf' size 250m
autoextend on  next 50m maxsize unlimited
extend management local
segment space management auto;
• 表格空間層及操作
  alter tablespace dmarts
  resize 1000m;
  好處是不用知道datafile儲存在哪裡

規劃和管理表格空間(Tablespace)-3/3

Oracle安裝的表格空間

• SYSTEM
• 不能儲存任何使用者segment
• 使用越多程序化物件如函數、procedure、trigger等等，SYSTEM tablespace會擴張越快，因為這些資料都必須放在data dictionary中。
• SYSAUX
• 不能儲存任何使用者segment
• 如果某一占據SYSAUX表格空間的程式耗費太多的空間，或是嚴重影響使用SYSAUX表格空間的應用程式效率，應該考慮移動這個程式到其他的表格空間
• TEMP
• 與其建立一個非常大的暫時性表格空間，不如使用幾個較小的暫時性表格空間，並用一個暫時性表格空間群組來裝載。
• UNDOTBS1
• 一個資料庫有多個UNDO表格空間，但一次只能有一個是有效的。
• 由於每個instance會管理本身所屬的undo表格空間，因此RAC環境中每個節點都要能取得一個UNDO表格空間。
• USERS
• 用於每個資料庫使用者建立各式各樣的segment
• 不適用於任何生產應用程式之上
• 不同的應用程式和segment類型需要建立不同的表格空間
• EXAMPLE
• 在production環境中應該要刪除這個表格空間

區段的分離

• 大型和小型區段應該分別置於不同的表格空間。
• table segment 及其對應的 index segment應置於不同的表格空間。
• 每個應用程式應置於不同的表格空間。
• 低用量segment與高用量segment應分開。
• 靜態的segment與大量DML的segment應分開。
• 唯讀資料表應放置在自己的表格空間。
• Data warehouse所用的過度資料表應該放置於自己所屬的表格空間。
• 在表格空間中，應該建立適當大小的block。以完整資料表掃描來存取segment，或是以每一個資料列來存取segment。
• materialized view應放置在與基礎資料表不同的表格空間
• 對於partition table和index，每個partition都應該有專屬的表格空間。

利用EM Database Control，不論是在檔案或是物件的層次都可以找出hotspot，進而辨識出表格空間的問題。

規劃和管理表格空間(Tablespace)-2/3

Optimal Flexible Architecture

OFA可以減輕軟體和資料庫檔案的維護工作，規劃資料庫檔案的方式來改善資料庫的執行效率，降低I/O的瓶頸。

• 非ASM環境
• 在UNIX伺服器上的非ASM環境,至少需要三個檔案系統和三個不同的實體裝置才能發揮OFA的強大功能。
• 掛載點的建議格式/<string const><numeric key>,ex: /u01,/u02...
• 可執行軟體
• 放置在/<string const>/<numeric key>/<directory type>/<product owner>,
  ex: /u01/app/oracle
• 資料檔
• 任何非ASM環境的Oracle資料檔都放在/<mount point>/oradata/<database name>
  ex: /u02/oradata/rac0
• 適用OFA的control file、redo log file、data file命名規則

檔案類型	檔案名稱	變數名稱
control file	control.ctl	無
redo log file	Redo.log	n是兩位數
data file	.dbf	t是tablespace name,n是兩位數

規劃和管理表格空間(Tablespace)-1/3

• 表格空間的類型
• 永久性表格空間
• Segment可持續時間超過一個session或是一個transaction
• undo表格空間
• 提供select陳述式(存取修改後的table)的讀取一致性,也提供回復資料給一些資料庫中的flashback功能
• undo segment中會儲存資料行update或delete之前的值
• 提供insert的資料列不存在的訊息
• 如果使用者session在下達commit或rollback指令之前失敗,則update、insert和delete指令都會被移除
• 使用者session不能直接存取undo segment,而undo tablespace只能有undo segment
• 暫時性表格空間
• 包含了只能持續一個session的暫時性資料
• 不儲存在記憶體中
• bigfile tablespace可用於上述三種tablespace,將維護的重點從data file換成tablesapce,因而簡化了tablespace的管理。bigfile tablespace只包含一個data file

永久性表格空間

• SYSTEM及SYSAUX不應該有任何的使用者segment
• 使用者需要保留,一個session和transaction範圍外的應用程式所需的segment,都應該放在永久性tablespace

SYSTEM tablespace 

•  local managed

          SYSAUX tablespace

• 可用EM Database Control應用程式來檢視

undo表格空間

• 一個資料庫可以有多個undo表格空間,但只能有一個是隨時有效的
• 大小需要精確規劃,避免snapshot too old錯誤
• 
  

暫時性表格空間

• 一個資料庫可以有多個線上且有效的暫時性表格空間
• 支援暫時性表格空間群組(temporary tablespace group),是一組暫時性表格空間的synonym
• temporary tablespace group 提供單一使用者多個session,而每個session可以使用不同的暫時性表格空間