本文小編為大家詳細(xì)介紹“Oracle閃回?cái)?shù)據(jù)庫怎么實(shí)現(xiàn)”，內(nèi)容詳細(xì)，步驟清晰，細(xì)節(jié)處理妥當(dāng)，希望這篇“Oracle閃回?cái)?shù)據(jù)庫怎么實(shí)現(xiàn)”文章能幫助大家解決疑惑，下面跟著小編的思路慢慢深入，一起來學(xué)習(xí)新知識(shí)吧。
成都創(chuàng)新互聯(lián)公司 - 西信服務(wù)器托管，四川服務(wù)器租用，成都服務(wù)器租用，四川網(wǎng)通托管,綿陽服務(wù)器托管，德陽服務(wù)器托管，遂寧服務(wù)器托管，綿陽服務(wù)器托管，四川云主機(jī)，成都云主機(jī)，西南云主機(jī)，西信服務(wù)器托管，西南服務(wù)器托管，四川/成都大帶寬，機(jī)柜大帶寬，四川老牌ＩＤＣ服務(wù)商
在Oracle Database 12.1中，閃回?cái)?shù)據(jù)庫操作僅限于根容器，因此會(huì)影響與根容器關(guān)聯(lián)的所有可插拔數(shù)據(jù)庫（PDB）。 Oracle Database 12.2現(xiàn)在支持可插拔數(shù)據(jù)庫的閃回，使閃回?cái)?shù)據(jù)庫在多租戶架構(gòu)中再次相關(guān)。
將數(shù)據(jù)庫啟動(dòng)到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ù)表示保留時(shí)間(分鐘)，默認(rèn)為7天。
SQL> show parameter DB_FLASHBACK_RETENTION_TARGE NAME TYPE VALUE ------------------------------------ ---------------------- ------------------------------ db_flashback_retention_target integer 1440
恢復(fù)點(diǎn)實(shí)際上是記錄當(dāng)時(shí)的SCN。要想恢復(fù)到之前創(chuàng)建的還原點(diǎn)，要保證還原點(diǎn)之后的閃回日志是完整的。
在CDB級(jí)別創(chuàng)建恢復(fù)點(diǎn)與non-CDB相同。 以下示例在CDB級(jí)別分別創(chuàng)建和刪除正常，保證的還原點(diǎn)。
正常的還原點(diǎn)
CREATE RESTORE POINT cdb1_before_changes; DROP RESTORE POINT cdb1_before_changes;
保證的還原點(diǎn)(Guaranteed restore point)
CREATE RESTORE POINT cdb1_before_changes GUARANTEE FLASHBACK DATABASE; DROP RESTORE POINT cdb1_before_changes;
下面是在PDB級(jí)別創(chuàng)建還原點(diǎn)的幾個(gè)選項(xià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.
–正常的還原點(diǎn)
SQL> create restore point pdb1_point1; Restore point created. SQL> drop restore point pdb1_point1; Restore point dropped.
–保證的還原點(diǎn)(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ù)庫是以本地模式運(yùn)行的就不用看這個(gè)章節(jié)。
本地模式運(yùn)行的容器數(shù)據(jù)庫，閃回PDB不依賴它。但是如果CDB再共享模式下運(yùn)行，那么閃回到干凈的還原點(diǎn)將更有效。這些是當(dāng)可插拔數(shù)據(jù)庫關(guān)閉的時(shí)候沒有未完成的事務(wù)。
語法和上面相似，只是多加了個(gè)單詞。而且要關(guān)閉PDB
如:
切換到PDB下執(zhí)行：
SQL> alter session set container=orclpdb; SQL> shutdown immediate;
– 干凈正常的還原點(diǎn)
SQL> CREATE CLEAN RESTORE POINT cdb1_before_changes; SQL> DROP RESTORE POINT cdb1_before_changes;
– 干凈保證的還原點(diǎn)(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;
– 干凈正常的還原點(diǎn)
SQL> CREATE CLEAN RESTORE POINT cdb1_before_changes FOR PLUGGABLE DATABASE orclpdb ; SQL> DROP RESTORE POINT cdb1_before_changes ;FOR PLUGGABLE DATABASE orclpdb ;
– 干凈保證的還原點(diǎn)(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;
閃回還支持多種形式的閃回，如按時(shí)間，SCN,或者某個(gè)時(shí)間點(diǎn)之前。
如：
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ù)庫運(yùn)行的模式不同，操作也會(huì)有所不同。下面是本地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模式，語法有點(diǎn)不同，就是你需要制定輔助實(shí)例的位置。
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)建還原點(diǎn)
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
閃回到指定還原點(diǎn)
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)建表之前的那個(gè)狀態(tài)了。
讀到這里，這篇“Oracle閃回?cái)?shù)據(jù)庫怎么實(shí)現(xiàn)”文章已經(jīng)介紹完畢，想要掌握這篇文章的知識(shí)點(diǎn)還需要大家自己動(dòng)手實(shí)踐使用過才能領(lǐng)會(huì)，如果想了解更多相關(guān)內(nèi)容的文章，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道。
重慶分公司
重慶分公司
