本文小編為大家詳細介紹“Oracle閃回數(shù)據(jù)庫怎么實現(xiàn)”,內(nèi)容詳細,步驟清晰,細節(jié)處理妥當,希望這篇“Oracle閃回數(shù)據(jù)庫怎么實現(xiàn)”文章能幫助大家解決疑惑,下面跟著小編的思路慢慢深入,一起來學(xué)習(xí)新知識吧。
成都創(chuàng)新互聯(lián)公司 - 西信服務(wù)器托管,四川服務(wù)器租用,成都服務(wù)器租用,四川網(wǎng)通托管,綿陽服務(wù)器托管,德陽服務(wù)器托管,遂寧服務(wù)器托管,綿陽服務(wù)器托管,四川云主機,成都云主機,西南云主機,西信服務(wù)器托管,西南服務(wù)器托管,四川/成都大帶寬,機柜大帶寬,四川老牌IDC服務(wù)商
在Oracle Database 12.1中,閃回數(shù)據(jù)庫操作僅限于根容器,因此會影響與根容器關(guān)聯(lián)的所有可插拔數(shù)據(jù)庫(PDB)。 Oracle Database 12.2現(xiàn)在支持可插拔數(shù)據(jù)庫的閃回,使閃回數(shù)據(jù)庫在多租戶架構(gòu)中再次相關(guān)。
將數(shù)據(jù)庫啟動到mount階段,開啟閃回,然后open,再啟用FLASHBACK
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> STARTUP MOUNT ORACLE instance started. Total System Global Area 1308622848 bytes Fixed Size 8792440 bytes Variable Size 905971336 bytes Database Buffers 385875968 bytes Redo Buffers 7983104 bytes Database mounted. SQL> alter database archivelog; Database altered. SQL> alter database open; Database altered.
指定閃回恢復(fù)區(qū)目錄和大小
SQL> alter system set db_recovery_file_dest_size=5G; System altered. SQL> alter system set db_recovery_file_dest='/home/oracle/archivelog'; System altered. SQL> alter database flashback on; Database altered.
關(guān)閉閃回
SQL> alter database flashback off; Database altered.
查看結(jié)果
SQL> SELECT flashback_on FROM v$database; FLASHBACK_ON ------------------------------------ YES
閃回日志保留的數(shù)量是受參數(shù)DB_FLASHBACK_RETENTION_TARGET控制的,該參數(shù)表示保留時間(分鐘),默認為7天。
SQL> show parameter DB_FLASHBACK_RETENTION_TARGE NAME TYPE VALUE ------------------------------------ ---------------------- ------------------------------ db_flashback_retention_target integer 1440
恢復(fù)點實際上是記錄當時的SCN。要想恢復(fù)到之前創(chuàng)建的還原點,要保證還原點之后的閃回日志是完整的。
在CDB級別創(chuàng)建恢復(fù)點與non-CDB相同。 以下示例在CDB級別分別創(chuàng)建和刪除正常,保證的還原點。
正常的還原點
CREATE RESTORE POINT cdb1_before_changes; DROP RESTORE POINT cdb1_before_changes;
保證的還原點(Guaranteed restore point)
CREATE RESTORE POINT cdb1_before_changes GUARANTEE FLASHBACK DATABASE; DROP RESTORE POINT cdb1_before_changes;
下面是在PDB級別創(chuàng)建還原點的幾個選項。
SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 ORCLPDB MOUNTED
–切換到PDB
SQL> alter session set container=orclpdb; Session altered. SQL> startup Pluggable Database opened.
–正常的還原點
SQL> create restore point pdb1_point1; Restore point created. SQL> drop restore point pdb1_point1; Restore point dropped.
–保證的還原點(Guaranteed restore point)
SQL> create restore point pdb1_point1 GUARANTEE FLASHBACK DATABASE; Restore point created. SQL> drop restore point pdb1_point1; Restore point dropped.
注:如果容器數(shù)據(jù)庫是以本地模式運行的就不用看這個章節(jié)。
本地模式運行的容器數(shù)據(jù)庫,閃回PDB不依賴它。但是如果CDB再共享模式下運行,那么閃回到干凈的還原點將更有效。這些是當可插拔數(shù)據(jù)庫關(guān)閉的時候沒有未完成的事務(wù)。
語法和上面相似,只是多加了個單詞。而且要關(guān)閉PDB
如:
切換到PDB下執(zhí)行:
SQL> alter session set container=orclpdb; SQL> shutdown immediate;
– 干凈正常的還原點
SQL> CREATE CLEAN RESTORE POINT cdb1_before_changes; SQL> DROP RESTORE POINT cdb1_before_changes;
– 干凈保證的還原點(Guaranteed restore point)
SQL> CREATE CLEAN RESTORE POINT cdb1_before_changes GUARANTEE FLASHBACK DATABASE; SQL> DROP RESTORE POINT cdb1_before_changes;
CDB ROOT下執(zhí)行:
SQL> ALTER PLUGGABLE DATABASE orclpdb CLOSE;
– 干凈正常的還原點
SQL> CREATE CLEAN RESTORE POINT cdb1_before_changes FOR PLUGGABLE DATABASE orclpdb ; SQL> DROP RESTORE POINT cdb1_before_changes ;FOR PLUGGABLE DATABASE orclpdb ;
– 干凈保證的還原點(Guaranteed restore point)
SQL> CREATE CLEAN RESTORE POINT cdb1_before_changes GUARANTEE FLASHBACK DATABASE FOR PLUGGABLE DATABASE orclpdb; SQL> DROP RESTORE POINT cdb1_before_changes ;FOR PLUGGABLE DATABASE orclpdb ; SQL> ALTER PLUGGABLE DATABASE orclpdb OPEN;
可以通過查看V$RESTORE_POINT視圖中的CLEAN_PDB_RESTORE_POINT列所示。
閃回CDB如下:
SQL>SHUTDOWN IMMEDIATE; SQL>STARTUP MOUNT; SQL>FLASHBACK DATABASE TO RESTORE POINT cdb1_before_changes; ALTER DATABASE OPEN RESETLOGS;
– 打開所有PDB
SQL>ALTER PLUGGABLE DATABASE ALL OPEN RESETLOGS;
閃回還支持多種形式的閃回,如按時間,SCN,或者某個時間點之前。
如:
FLASHBACK DATABASE TO TIMESTAMP my_date; FLASHBACK DATABASE TO BEFORE TIMESTAMP my_date; FLASHBACK DATABASE TO SCN my_scn; FLASHBACK DATABASE TO BEFORE SCN my_scn; FLASHBACK DATABASE TO RESTORE POINT my_restore_point;
根據(jù)數(shù)據(jù)庫運行的模式不同,操作也會有所不同。下面是本地UNDO模式的操作步驟:
SQL>ALTER PLUGGABLE DATABASE orclpdb CLOSE; SQL>FLASHBACK PLUGGABLE DATABASE orclpdb TO RESTORE POINT pdb1_before_changes; SQL>ALTER PLUGGABLE DATABASE orclpdb OPEN RESETLOGS;
如果使用的共享UNDO模式,語法有點不同,就是你需要制定輔助實例的位置。
SQL>FLASHBACK PLUGGABLE DATABASE orclpdb TO SCN my_scn AUXILIARY DESTINATION '/u01/auxiliary'; SQL>FLASHBACK PLUGGABLE DATABASE orclpdb TO RESTORE POINT my_restore_point AUXILIARY DESTINATION '/u01/auxiliary';
–創(chuàng)建還原點
SQL>CREATE RESTORE POINT pdb1_before_changes FOR PLUGGABLE DATABASE pdb1;
–創(chuàng)建表
SQL>CREATE TABLE t1 ( id NUMBER ); SQL>INSERT INTO t1 VALUES (1); SQL>COMMIT; SQL>SELECT * FROM t1; ID ---------- 1
閃回到指定還原點
SQL>Flashback the PDB to the restore point. SQL>ALTER PLUGGABLE DATABASE pdb1 CLOSE; SQL>FLASHBACK PLUGGABLE DATABASE pdb1 TO RESTORE POINT pdb1_before_changes; SQL>ALTER PLUGGABLE DATABASE pdb1 OPEN RESETLOGS;
–檢查數(shù)據(jù)是否存在
SQL>SELECT * FROM t1; SELECT * FROM t1 * ERROR at line 1: ORA-00942: table or view does not exist
可以看到已經(jīng)閃回到創(chuàng)建表之前的那個狀態(tài)了。
讀到這里,這篇“Oracle閃回數(shù)據(jù)庫怎么實現(xiàn)”文章已經(jīng)介紹完畢,想要掌握這篇文章的知識點還需要大家自己動手實踐使用過才能領(lǐng)會,如果想了解更多相關(guān)內(nèi)容的文章,歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道。
本文題目:Oracle閃回數(shù)據(jù)庫怎么實現(xiàn)
文章URL:http://muchs.cn/article48/piechp.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián),為您提供標簽優(yōu)化、靜態(tài)網(wǎng)站、ChatGPT、網(wǎng)站策劃、品牌網(wǎng)站制作、企業(yè)建站
聲明:本網(wǎng)站發(fā)布的內(nèi)容(圖片、視頻和文字)以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主,如果涉及侵權(quán)請盡快告知,我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場,如需處理請聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載,或轉(zhuǎn)載時需注明來源: 創(chuàng)新互聯(lián)