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;