熱備份還原應用練習

情境:
使用者不小心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;