SCN

System Change Number. A database ordering primitive. The value of an SCN is the logical point in time at which changes are made to a database.

SCN的值是數(shù)據(jù)庫進行更改時記錄邏輯時間點喊崖〖拖叮可以理解成SCN就相當(dāng)于ORACLE的計時方法

詳見https://docs.oracle.com/cd/E11882_01/server.112/e40540/glossary.htm#CHDBFAEG

查詢當(dāng)前SCN

SQL>select dbms_flashback.get_system_change_number() from dual;--不會影響原有的最新值

SQL>select current_scn from v$database;--SQL只要執(zhí)行一次SCN就會在原有的最新值的基礎(chǔ)上加1,和序列號的nextval相似

https://docs.oracle.com/database/121/REFRN/GUID-B33E13DF-0BF1-4E20-8471-CE168A8380FD.htm#REFRN-GUID-B33E13DF-0BF1-4E20-8471-CE168A8380FD

V$DATABASE displays information about the database from the control file

V$DATAFILE_HEADER displays datafile information from the datafile headers

V$DATAFILE displays datafile information from the control file

P18

undo參見官方文檔https://docs.oracle.com/cd/E11882_01/server.112/e25494/undo.htm#ADMIN11460

回滾段中的數(shù)據(jù)主要分為以下三種類型：

1.Uncommitted undo information; 未提交的回滾數(shù)據(jù)困介，該數(shù)據(jù)所關(guān)聯(lián)的事務(wù)并未提交假消，用于實現(xiàn)讀一致性，所以該數(shù)據(jù)不能被其它事務(wù)的數(shù)據(jù)所覆蓋(假如回滾段不夠的情況下晴股，開始執(zhí)行一個事務(wù)位他，執(zhí)行到一半就會提示ORA-30036:無法按8擴展段(在還原表空間‘XXXX’中)。就如undo只有10M的情況下枚钓，delete一張100M的表，delete執(zhí)行不到一半就會開始報錯了瑟押。)

2.Committed undo information; 已經(jīng)提交但未過期的回滾數(shù)據(jù)搀捷，該數(shù)據(jù)關(guān)聯(lián)的事務(wù)已經(jīng)提交，但是仍在undo retention參數(shù)保持的時間內(nèi)

3.Expired undo information; 事務(wù)已經(jīng)提交多望，而且數(shù)據(jù)保存時間已經(jīng)超過undo retention參數(shù)指定的時間嫩舟，屬于已經(jīng)過期的數(shù)據(jù)

當(dāng)回滾段不足時，會優(yōu)先覆蓋Expired undo information怀偷，當(dāng)過期數(shù)據(jù)空間覆蓋后家厌，會再覆蓋Committed undo information的區(qū)域，這時undo retention參數(shù)所規(guī)定的保持時間是不起作用的枢纠，Uncommitted undo information的數(shù)據(jù)是不允許覆蓋的像街，如果要求提交的數(shù)據(jù)在undo retention參數(shù)規(guī)定的時間內(nèi)不會被覆蓋，必須在undo表空間上指定RETENTION GUARANTEE

查詢UNDO表空間是否啟用retention guarantee

SQL> SELECT tablespace_name, retention FROM dba_tablespaces where tablespace_name='UNDOTBS1';

SQL> alter tablespace UNDOTBS1 retention guarantee;

SQL> SELECT tablespace_name, retention FROM dba_tablespaces where tablespace_name='UNDOTBS1';

了解Oracle在什么情況下會產(chǎn)生ORA-01555:快照過舊錯誤

假設(shè)有一張1000萬行數(shù)據(jù)的test表晋渺，預(yù)計test全表掃描1次需要1個小時镰绎，參考過程如下：

1、在1:00木西，用戶A發(fā)出了select * from test;此時不管將來test怎么變化畴栖，1小時執(zhí)行完后顯示的正確結(jié)果應(yīng)該是用戶A看到在1:00這個時刻的內(nèi)容。

2八千、在1:20吗讶，用戶B執(zhí)行了update命令，更新了test表中的第900萬行的這條記錄恋捆，這時照皆，用戶A的全表掃描還沒有到達第900萬條。毫無疑問沸停，這個時候膜毁，第900萬行的這條記錄是被寫入了回滾段，假設(shè)是回滾段UNDOTS1愤钾，如果用戶A的全表掃描到達了第900萬行瘟滨，是應(yīng)該會正確的從回滾段UNDOTS1中讀取出1:00這個時刻的內(nèi)容的。

3能颁、到了1:30這時杂瘸，用戶B將他剛才做的update操作提交了，但是這時伙菊，系統(tǒng)仍然可以給用戶A提供正確的數(shù)據(jù)败玉，因為那第900萬行記錄的內(nèi)容仍然還在回滾段UNDOTS1里敌土。但要注意到，就是第900萬行在回滾段UNDOTS1里的數(shù)據(jù)有可能隨時被覆蓋掉绒怨，因為這條記錄已經(jīng)被提交了纯赎！

4、到了1:40這時南蹂，由于用戶A的查詢時間漫長犬金，而業(yè)務(wù)在一直不斷的進行，UNDOTS1回滾段在被多個不同的事務(wù)使用著六剥，回滾段出現(xiàn)不足的情況晚顷，由于test表第900萬行的這條記錄已經(jīng)被標(biāo)記提交了，所以在1:40它在回滾段中的空間被其他事務(wù)覆蓋掉的疗疟！

5该默、到了1:50，用戶A的查詢終于到了第900萬行策彤，這時需要到回滾段UNDOTS1去找數(shù)據(jù)栓袖，而這時已經(jīng)出現(xiàn)了第4條說的情況，該回滾段已經(jīng)被覆蓋掉了店诗，這時就出現(xiàn)了ORA-01555錯誤裹刮。

ORACLE的多版本與讀一致性

網(wǎng)絡(luò)上比較好的一個例子https://blog.csdn.net/garefield/article/details/1818572

create table test1 (hid number,hname varchar2(10));

insert into test1 values (1,'1');

insert into test1 values (2,'2');

commit;

SQL> select * from test1;

? ? ? ? HID HNAME

---------- ----------

? ? ? ? 1 1

? ? ? ? 2 2

begin

? for s in (select * from test1) loop

? ? insert into test1 values(s.hid+10,s.hname);

? end loop;

? commit;

? end;

SQL> select * from test1;

? ? ? ? HID HNAME

---------- ----------

? ? ? ? 1 1

? ? ? ? 2 2

? ? ? ? 11 1

? ? ? ? 12 2

閃回查詢

alter session set nls_date_format='YYYY-MM-DD hh24:mi:ss';

select sysdate from dual;

SELECT dbms_flashback.get_system_change_number FROM dual;

conn hr/123456

select * from JOB_HISTORY;

delete from JOB_HISTORY where EMPLOYEE_ID>101;

commit;

select * from JOB_HISTORY;

按time：as of timestamp

select * from JOB_HISTORY as of timestamp to_timestamp('XX','YYYY-MM-DD hh24:mi:ss');

按scn：as of scn

select * from JOB_HISTORY as of scn XX;

SCN和time的關(guān)系

select time_dp,scn from sys.smon_scn_time order by 1 desc

閃回版本查詢

GRANT EXECUTE ON SYS.DBMS_LOCK TO HR;

grant select on sys.smon_scn_time to hr;

alter database add supplemental log data;

create table hr.test1(id number primary key,name varchar2(10));--10秒后執(zhí)行下面語句

begin

? insert into test1 values(1,'A');

? commit;

? insert into test1 values(2,'B');

? commit;

? insert into test1 values(3,'C');

? commit;

? dbms_lock.sleep(5);

? update test1 set name='C-1' where id=3;

? commit;

? insert into test1 values(4,'D');

? commit;

end;

select id,name,versions_startscn,versions_starttime,versions_xid,versions_operation from hr.test1 versions BETWEEN SCN MINVALUE AND MAXVALUE order by 3

--查詢結(jié)果發(fā)現(xiàn)1、2庞瘸、3的versions_starttime都一樣捧弃，但是versions_startscn各不一樣

--versions_startscn,versions_starttime等偽列的解釋見https://docs.oracle.com/cd/E11882_01/server.112/e41084/pseudocolumns003.htm#SQLRF00252

閃回事務(wù)查詢

select id,name,versions_startscn,versions_starttime,versions_xid,versions_operation from hr.test1 versions BETWEEN SCN MINVALUE AND MAXVALUE order by 3

select * from flashback_transaction_query where xid='0100000012040000'

閃回事務(wù)查詢需要ADD SUPPLEMENTAL LOG DATA的說明

https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#ADFNS610

閃回丟棄

SQL> create table test123? as select EMPLOYEE_ID from hr.employees;

SQL> drop table test123;

SQL> create table test123? as select FIRST_NAME from hr.employees;

SQL> drop table test123;

SQL> show recyclebin;

SQL> flashback table test123 to before drop;

SQL> desc test123

SQL> show recyclebin;

SQL>? flashback table test123 to before drop;

ERROR at line 1:

ORA-38312: original name is used by an existing object

SQL> flashback table test123 to before drop rename to test123_EMPLOYEE_ID;

SQL> desc test123_EMPLOYEE_ID

--類似堆棧，先進后出擦囊。

oracle回收站中的被刪除的表會存放多長時間

空間不夠用,需要擴展新的extent之前违霞，按時間順序嘗試逐個清空回收站中的對象（從最早刪除的對象開始清除）,全清空還是不夠則擴展新extent

oracle原則上不會自動刪除或者覆蓋你回收站，只有出現(xiàn)“space pressure”空間壓力的情況才會清理回收站

Dropped objects are kept in the recycle bin until such time as no new extents can be allocated in the tablespace to which the objects belong without growing the tablespace.

參考https://docs.oracle.com/cd/B19306_01/backup.102/b14192/flashptr004.htm

閃回表

conn hr/123456

select * from JOB_HISTORY;

ALTER TABLE JOB_HISTORY ENABLE ROW MOVEMENT;

flashback table JOB_HISTORY to scn XX

select * from test1

需要ROW MOVEMENT的原因

每一行數(shù)據(jù)都對應(yīng)一個row_id瞬场，記錄了數(shù)據(jù)存放的具體位置买鸽，哪個數(shù)據(jù)文件、哪個數(shù)據(jù)塊贯被、哪行

比如1行數(shù)據(jù)被刪除了眼五，這行對應(yīng)的數(shù)據(jù)塊就空了，一旦有其他數(shù)據(jù)插入刃榨，可能就會寫入到這些到數(shù)據(jù)塊上，也就是這行數(shù)據(jù)的位置被其他數(shù)據(jù)占用了双仍，以后如果要恢復(fù)這行枢希，因為原先的位置被其他數(shù)據(jù)占用，就要改變這行數(shù)據(jù)的位置朱沃，這就是row movement

注意事項

基于undo 的表恢復(fù)苞轿，需要注意DDL操作的影響茅诱，就是commit后，再對表做DDL操作會使undo表空間中的撤銷數(shù)據(jù)失效搬卒，對于執(zhí)行過這些操作的表應(yīng)用flashback query會觸發(fā)錯誤ORA-01466:無法讀取數(shù)據(jù) - 表定義已更改瑟俭。

select * from JOB_HISTORY as of scn XX;

truncate table JOB_HISTORY

select * from JOB_HISTORY as of scn XX;--報錯ORA-01466:無法讀取數(shù)據(jù) - 表定義已更改。

閃回數(shù)據(jù)庫

SQL> archive log list

Database log mode? ? ? ? ? ? ? No Archive Mode

Automatic archival? ? ? ? ? ? Disabled

Archive destination? ? ? ? ? ? /u01/app/oracle/archivelog

Oldest online log sequence? ? 65

Current log sequence? ? ? ? ? 67

SQL> shutdown immediate;

SQL> startup mount;

SQL> alter database archivelog;

SQL> alter database flashback on;

alter database flashback on

*ERROR at line 1:

ORA-38706: Cannot turn on FLASHBACK DATABASE logging.

ORA-38709: Recovery Area is not enabled.

SQL> show parameter recover

NAME? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? TYPE? ? ? ? VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest? ? ? ? ? ? ? ? string

db_recovery_file_dest_size? ? ? ? ? big integer 0

db_unrecoverable_scn_tracking? ? ? ? boolean? ? TRUE

recovery_parallelism? ? ? ? ? ? ? ? integer? ? 0

SQL> alter system set db_recovery_file_dest='/u01/app/oracle/flashlog';

alter system set db_recovery_file_dest='/u01/app/oracle/flashlog'

*ERROR at line 1:

ORA-02097: parameter cannot be modified because specified value is invalid

ORA-19802: cannot use DB_RECOVERY_FILE_DEST without DB_RECOVERY_FILE_DEST_SIZE

SQL> alter system set db_recovery_file_dest_size=1G;

SQL> alter system set db_recovery_file_dest='/u01/app/oracle/flashlog';

SQL> alter database flashback on;

SQL> alter database open;

SQL> create table hr.test1 as select employee_id from hr.employees;

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

XX

SQL> drop table hr.test1;

SQL> select count(*) from hr.test1;

select * from test1

? ? ? ? ? ? ? *

ERROR at line 1:

ORA-00942: table or view does not exist

SQL> shutdown immediate;

SQL> startup mount;

SQL> Flashback database to scn XX;

SQL> alter database open resetlogs;

SQL> select count(*) from hr.test1;

閃回技術(shù)的官方文檔https://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmintro.htm#BRADV89342

閃回技術(shù)分以下幾類

閃回查詢：Flashback Query (SELECT AS OF)

閃回版本查詢：Flashback Version Query

閃回事務(wù)查詢：Flashback Transaction Query

閃回表:Flashback Table

閃回刪除:Flashback Drop

閃回數(shù)據(jù)歸檔：Flashback Data Archive