實(shí)驗(yàn)遇到的問(wèn)題： 1、剛開(kāi)始做前面的實(shí)驗(yàn)的時(shí)候，resetlogs重置了聯(lián)機(jī)日志，導(dǎo)致閃回的時(shí)候總是報(bào)miss logfile的錯(cuò)誤。 這個(gè)時(shí)候需要用noretlogs的方式重建控制文件，然后重新啟動(dòng)數(shù)據(jù)庫(kù)到open狀態(tài)。 SQL SELECT distinct xid,commit_scn FROM flashback_tran
實(shí)驗(yàn)遇到的問(wèn)題：
1、剛開(kāi)始做前面的實(shí)驗(yàn)的時(shí)候，resetlogs重置了聯(lián)機(jī)日志，導(dǎo)致閃回的時(shí)候總是報(bào)miss logfile的錯(cuò)誤。
這個(gè)時(shí)候需要用noretlogs的方式重建控制文件，然后重新啟動(dòng)數(shù)據(jù)庫(kù)到open狀態(tài)。
SQL> SELECT distinct xid,commit_scn FROM flashback_transaction_query t
2 where table_owner='HR'
3 and lower(t.table_name) = 'employees'
4 and t.commit_timestamp > systimestamp - interval '90' minute
5 order by t.commit_scn ;
XID COMMIT_SCN
---------------- ----------
100004005E010000 2948380
0E0019005E010000 2948386
SQL> declare
2 xids sys.xid_array ;
3 begin
4 xids := sys.xid_array('100004005E010000');
5 dbms_flashback.transaction_backout(1,xids,options => dbms_flashback.cascade);
6 end ;
7 /
declare
*
ERROR at line 1:
ORA-55507: Encountered mining error during Flashback Transaction Backout.
function:krvxpsr
ORA-01291: missing logfile
ORA-06512: at "SYS.DBMS_FLASHBACK", line 37
ORA-06512: at "SYS.DBMS_FLASHBACK", line 70
ORA-06512: at line 5
此問(wèn)題是我之前啟動(dòng)數(shù)據(jù)庫(kù)時(shí)候重置了日志文件，導(dǎo)致數(shù)據(jù)庫(kù)不讀日志。需要重建控制文件。
shutdown immediate
startup nomount
alter database backup controlfile to trace as 'J:\app\wufan\diag\rdbms\orcl\orcl\trace\control.trac';
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 'J:\APP\WUFAN\ORADATA\ORCL\REDO01.LOG' SIZE 50M BLOCKSIZE 512,
GROUP 2 'J:\APP\WUFAN\ORADATA\ORCL\REDO02.LOG' SIZE 50M BLOCKSIZE 512,
GROUP 3 'J:\APP\WUFAN\ORADATA\ORCL\REDO03.LOG' SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'J:\APP\WUFAN\ORADATA\ORCL\SYSTEM01.DBF',
'J:\APP\WUFAN\ORADATA\ORCL\SYSAUX01.DBF',
'J:\APP\WUFAN\ORADATA\ORCL\UNDOTBS02.DBF',
'J:\APP\WUFAN\ORADATA\ORCL\USERS01.DBF',
'J:\APP\WUFAN\ORADATA\ORCL\EXAMPLE01.DBF',
'J:\APP\WUFAN\ORADATA\ORCL\UNDOTBS04.DBF',
'J:\APP\WUFAN\ORADATA\ORCL\DATAFILE\O1_MF_UNDOTBS4_CK602RTP_.DBF',
'J:\APP\WUFAN\ORADATA\ORCL\DATAFILE\O1_MF_TEST02_CK610HG8_.DBF',
'J:\APP\WUFAN\ORADATA\ORCL\DATAFILE\O1_MF_TEST02_CK611OKD_.DBF',
'J:\APP\WUFAN\ORADATA\ORCL\DATA_TEST01.BDF',
'J:\APP\WUFAN\ORADATA\ORCL\HEAT01.BDF'
CHARACTER SET ZHS16GBK
;
--這種情況其實(shí)不需要恢復(fù)，你執(zhí)行了這條命令它會(huì)告訴你沒(méi)有什么可恢復(fù)的。
RECOVER DATABASE;
--打開(kāi)所有的補(bǔ)充日志文件，可以不做
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
--打開(kāi)系統(tǒng)歸檔，當(dāng)前已經(jīng)是歸檔狀態(tài)，所以這條命令會(huì)報(bào)錯(cuò)，不用管
ALTER SYSTEM ARCHIVE LOG ALL;
--打開(kāi)數(shù)據(jù)庫(kù)
ALTER DATABASE OPEN;
至此，重建控制文件已經(jīng)完成
重新進(jìn)行試驗(yàn)
1、開(kāi)兩個(gè)事物
SQL> update hr.employees t
2 set t.salary = t.salary * 2 ;
107 rows updated.
SQL> commit ;
Commit complete.
SQL> update hr.employees t
2 set t.salary = t.salary * 1.1 ;
107 rows updated.
SQL> commit ;
Commit complete.
2、查詢兩個(gè)事物號(hào)
SQL> SELECT distinct xid,commit_scn FROM flashback_transaction_query t
2 where table_owner='HR'
3 and lower(t.table_name) = 'employees'
4 and t.commit_timestamp > systimestamp - interval '15' minute
5 order by t.commit_scn ;
XID COMMIT_SCN
---------------- ----------
13001A0061010000 2983670
0F0021005D010000 2983677
3、執(zhí)行事物閃回
SQL> declare
2 xids sys.xid_array ;
3 begin
4 xids := sys.xid_array('13001A0061010000');
5 dbms_flashback.transaction_backout(1,xids,options => dbms_flashback.nocascade);
6 end ;
7 /
declare
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [ktgRunStmt_5], [25153], [ORA-25153:
Temporary Tablespace is Empty
ORA-00600: internal error code, arguments: [ktgRunStmt_5], [25153], [ORA-25153:
Temporary Tablespace is Empty
], [], [], [], [], [], [], [], [], []
], [], [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [ktgRunStmt_5], [25153], [ORA-25153:
Temporary Tablespace is Empty
], [], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.DBMS_FLASHBACK", line 37
ORA-06512: at "SYS.DBMS_FLASHBACK", line 70
ORA-06512: at line 5
看著報(bào)錯(cuò)多嚇人！其實(shí)問(wèn)題就在于
[ORA-25153:
Temporary Tablespace is Empty
臨時(shí)表空間是空的。下面就來(lái)確認(rèn)這個(gè)問(wèn)題：
--當(dāng)前用戶默認(rèn)臨時(shí)表空間
SQL> select username,temporary_tablespace from dba_users where username='SYS';
USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
SYS TEMP_01
--默認(rèn)表空間邏輯上是聯(lián)機(jī)的，沒(méi)問(wèn)題
SQL> select tablespace_name,status from dba_tablespaces where tablespace_name='TEMP_01';
TABLESPACE_NAME STATUS
------------------------------ ---------
TEMP_01 ONLINE
--物理上沒(méi)有文件，問(wèn)題就在這兒
SQL> select file_name,tablespace_name from dba_temp_files;
no rows selected
--確定臨時(shí)文件是存在的，把該臨時(shí)文件添加到表空間
SQL> alter tablespace temp add tempfile 'J:\app\wufan\oradata\orcl\temp01.dbf';
Tablespace altered.
--上面那條語(yǔ)句把文件對(duì)應(yīng)到temp表空間了，其實(shí)sys的默認(rèn)臨時(shí)表空間是temp_01。
SQL> select tablespace_name,file_name from dba_temp_files;
TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
TEMP
J:\APP\WUFAN\ORADATA\ORCL\TEMP01.DBF
--將錯(cuò)就錯(cuò)吧，就將sys默認(rèn)表空間改成temp吧
SQL> alter user sys temporary tablespace temp;
User altered.
--查看是否改過(guò)來(lái)了
SQL> select username,temporary_tablespace from dba_users where username='SYS';
USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
SYS TEMP
重新進(jìn)行實(shí)驗(yàn)：
開(kāi)啟連個(gè)事務(wù)：
--這邊報(bào)了個(gè)錯(cuò)，因?yàn)樽隽撕芏啻螌?shí)驗(yàn)，現(xiàn)在這個(gè)薪水的值已經(jīng)很大了，超出了字段的長(zhǎng)度
SQL> update hr.employees t
2 set t.salary = t.salary * 2 ;
set t.salary = t.salary * 2
*
ERROR at line 2:
ORA-01438: value larger than specified precision allowed for this column
SQL> commit ;
Commit complete.
SQL> update hr.employees t
2 set t.salary = t.salary * 1.1 ;
107 rows updated.
SQL> commit ;
Commit complete.
--因?yàn)槭×艘粋€(gè)語(yǔ)句，所以查詢就只有3個(gè)事務(wù)。
SQL> SELECT distinct xid,commit_scn FROM flashback_transaction_query t
2 where table_owner='HR'
3 and lower(t.table_name) = 'employees'
4 and t.commit_timestamp > systimestamp - interval '15' minute
5 order by t.commit_scn ;
XID COMMIT_SCN
---------------- ----------
13001A0061010000 2983670
0F0021005D010000 2983677
0D00050064010000 2984032
--執(zhí)行回退到倒數(shù)第二個(gè)事務(wù)，用nocascade
SQL> declare
2 xids sys.xid_array ;
3 begin
4 xids := sys.xid_array('0F0021005D010000');
5 dbms_flashback.transaction_backout(1,xids,options => dbms_flashback.nocascade);
6 end ;
7 /
declare
*
ERROR at line 1:
ORA-55504: Transaction conflicts in NOCASCADE mode
ORA-06512: at "SYS.DBMS_FLASHBACK", line 37
ORA-06512: at "SYS.DBMS_FLASHBACK", line 70
ORA-06512: at line 5
--失敗了，因?yàn)榈箶?shù)第二個(gè)事務(wù)依賴于倒數(shù)第一個(gè)事務(wù)
--重新用cascade選項(xiàng)，把倒數(shù)第二個(gè)事務(wù)依賴的第一個(gè)事務(wù)一起回退掉
SQL> declare
2 xids sys.xid_array ;
3 begin
4 xids := sys.xid_array('0F0021005D010000');
5 dbms_flashback.transaction_backout(1,xids,options => dbms_flashback.cascade);
6 end ;
7 /
PL/SQL procedure successfully completed.
--過(guò)程執(zhí)行成功，但是別忘了commit，oracle在過(guò)程里面并沒(méi)有提交，需要你手動(dòng)提交才能生效
SQL> commit ;
Commit complete.
SQL>
聲明：本網(wǎng)頁(yè)內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問(wèn)題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com
