您好,登錄后才能下訂單哦!
很多時(shí)候人為誤操作會(huì)對(duì)Oracle的數(shù)據(jù)造成影響,比如誤刪數(shù)據(jù)。當(dāng)誤操作發(fā)生時(shí),通常有四個(gè)解決方法:
1.閃回?cái)?shù)據(jù)庫(kù)或閃回表
2.備份恢復(fù)
3.logminer挖掘redo
4.使用延時(shí)庫(kù)
前三種方法都不是很完美:
閃回?cái)?shù)據(jù)庫(kù)影響較大,且事先需要開(kāi)啟庫(kù)上的閃回。
閃回表或閃回版本查詢使用方便,但在繁忙的數(shù)據(jù)庫(kù)中很容易快照過(guò)舊。
備份恢復(fù)耗時(shí)長(zhǎng)、影響大
logminer挖掘日志耗時(shí)耗力
此時(shí)如果事先有搭建延時(shí)備庫(kù)的話,則可以很容易的從備庫(kù)得到誤刪的數(shù)據(jù)。以下進(jìn)行演示。
主庫(kù):
SQL> show parameter instance_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string BDDEV2
備庫(kù):
SQL> show parameter instance_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_name string BDTEST
現(xiàn)有表及數(shù)據(jù)如下:
SQL> select * from scott.tb_test;
ID AGE TM
---------- ---------- -----------------
1 2 20181203 14:22:14
2 3 20181203 14:22:14
3 4 20181203 14:22:14
4 5 20181203 14:22:14
5 6 20181203 14:22:14
6 7 20181203 14:22:14
7 8 20181203 14:22:14
8 9 20181203 14:22:14
9 10 20181203 14:22:14
10 11 20181203 14:22:14
10 rows selected.
此時(shí)備庫(kù)是實(shí)時(shí)應(yīng)用的:
SQL> SELECT * FROM V$DATAGUARD_STATS;
NAME VALUE UNIT TIME_COMPUTED DATUM_TIME
------------------------------ ------------------------------ ---------------------------------------- ------------------------------ ------------------------------
transport lag +00 00:00:00 day(2) to second(0) interval 12/03/2018 14:23:33 12/03/2018 14:23:33
apply lag +00 00:00:00 day(2) to second(0) interval 12/03/2018 14:23:33 12/03/2018 14:23:33
apply finish time +00 00:00:00.000 day(2) to second(3) interval 12/03/2018 14:23:33
estimated startup time 7 second 12/03/2018 14:23:33
備庫(kù)轉(zhuǎn)為延時(shí)庫(kù),延時(shí)60分鐘:
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database recover managed standby database disconnect from session delay 60;
Database altered.
主庫(kù)修改記錄:
SQL> update scott.tb_test set age = 100,tm = sysdate where id = 10;
1 row updated.
SQL> commit;
Commit complete.
SQL> select * from scott.tb_test;
ID AGE TM
---------- ---------- -----------------
1 2 20181203 14:22:14
2 3 20181203 14:22:14
3 4 20181203 14:22:14
4 5 20181203 14:22:14
5 6 20181203 14:22:14
6 7 20181203 14:22:14
7 8 20181203 14:22:14
8 9 20181203 14:22:14
9 10 20181203 14:22:14
10 100 20181203 14:26:50
10 rows selected.
可看到,修改數(shù)據(jù)的時(shí)間為' 20181203 14:26:50 '。
此時(shí)刪除表數(shù)據(jù):
SQL> select sysdate from dual;
SYSDATE
-----------------
20181203 14:28:18
SQL> truncate table scott.tb_test;
Table truncated.
SQL> alter system switch logfile;
System altered.
此時(shí)主庫(kù)中數(shù)據(jù)已經(jīng)刪除,備庫(kù)中數(shù)據(jù)還停留在主庫(kù)修改數(shù)據(jù)之前:
SQL> select * from scott.tb_test;
ID AGE TM
---------- ---------- -----------------
1 2 20181203 14:22:14
2 3 20181203 14:22:14
3 4 20181203 14:22:14
4 5 20181203 14:22:14
5 6 20181203 14:22:14
6 7 20181203 14:22:14
7 8 20181203 14:22:14
8 9 20181203 14:22:14
9 10 20181203 14:22:14
10 11 20181203 14:22:14
如果我們要恢復(fù)數(shù)據(jù)到update之后、truncate之前,則要手動(dòng)前滾備庫(kù)到指定時(shí)間點(diǎn):
--備庫(kù)操作
重啟備庫(kù)到mount:
SQL> startup force mount;
ORACLE instance started.
Total System Global Area 2.1379E+10 bytes
Fixed Size 2262656 bytes
Variable Size 2.0401E+10 bytes
Database Buffers 939524096 bytes
Redo Buffers 36073472 bytes
Database mounted.
手動(dòng)前滾備庫(kù)到指定時(shí)間點(diǎn):
SQL> recover standby database until time '20181203 14:27:00';
ORA-00279: change 108456269 generated at 12/03/2018 14:26:50 needed for thread 1
ORA-00289: suggestion : /opt/app/oracle/archivelog_bdtest/1_248_987097214.dbf
ORA-00280: change 108456269 for thread 1 is in sequence #248
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
Log applied.
Media recovery complete.
前滾完成,打開(kāi)備庫(kù):
SQL> alter database open;
Database altered.
SQL> alter session set nls_date_format='yyyymmdd hh34:mi:ss';
Session altered.
SQL> select * from scott.tb_test;
ID AGE TM
---------- ---------- -----------------
1 2 20181203 14:22:14
2 3 20181203 14:22:14
3 4 20181203 14:22:14
4 5 20181203 14:22:14
5 6 20181203 14:22:14
6 7 20181203 14:22:14
7 8 20181203 14:22:14
8 9 20181203 14:22:14
9 10 20181203 14:22:14
10 100 20181203 14:26:50
10 rows selected.
此時(shí)就得到了所需數(shù)據(jù),只需把數(shù)據(jù)重新導(dǎo)入到主庫(kù)中即可。
待處理完成后,重新打開(kāi)備庫(kù)的redo應(yīng)用:
SQL> alter database recover managed standby database disconnect from session delay 60;
Database altered.
此時(shí)備庫(kù)又變?yōu)榱苏5难訒r(shí)庫(kù)。
注意:
1.備庫(kù)DELAY時(shí)間指的是當(dāng)主庫(kù)日志歸檔后,再延時(shí)DELAY指定的時(shí)間應(yīng)用日志,比如:設(shè)置DELAY參數(shù)為20分鐘,10:00主庫(kù)日志歸檔,則10:20備庫(kù)才會(huì)應(yīng)用此歸檔日志
2.也可以在LOG_ARCHIVE_DEST_2指定DELAY參數(shù)達(dá)到同樣的延時(shí)目的:
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=DEVSTBY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=DEVSTBY DELAY=60'
3.備庫(kù)不能進(jìn)行實(shí)時(shí)應(yīng)用,因?yàn)閷?shí)時(shí)應(yīng)用會(huì)使DELAY參數(shù)失效,備庫(kù)變?yōu)锳DG
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。