ORA-01291:missinglogfile事务闪回缺失日志

实验遇到的问题： 1、刚开始做前面的实验的时候，resetlogs重置了联机日志，导致闪回的时候总是报miss logfile的错误。 这个时候需要用noretlogs的方式重建控制文件，然后重新启动数据库到open状态。 SQL SELECT distinct xid,commit_scn FROM flashback_tran

实验遇到的问题：
1、刚开始做前面的实验的时候，resetlogs重置了联机日志，导致闪回的时候总是报miss logfile的错误。
这个时候需要用noretlogs的方式重建控制文件，然后重新启动数据库到open状态。
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
此问题是我之前启动数据库时候重置了日志文件，导致数据库不读日志。需要重建控制文件。

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
;
--这种情况其实不需要恢复，你执行了这条命令它会告诉你没有什么可恢复的。
RECOVER DATABASE;
--打开所有的补充日志文件，可以不做
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
--打开系统归档，当前已经是归档状态，所以这条命令会报错，不用管
ALTER SYSTEM ARCHIVE LOG ALL;
--打开数据库
ALTER DATABASE OPEN;
至此，重建控制文件已经完成

重新进行试验
1、开两个事物
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、查询两个事物号
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、执行事物闪回
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
看着报错多吓人！其实问题就在于
[ORA-25153:
Temporary Tablespace is Empty
临时表空间是空的。下面就来确认这个问题：
--当前用户默认临时表空间
SQL> select username,temporary_tablespace from dba_users where username='SYS';
USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
SYS TEMP_01
--默认表空间逻辑上是联机的，没问题
SQL> select tablespace_name,status from dba_tablespaces where tablespace_name='TEMP_01';
TABLESPACE_NAME STATUS
------------------------------ ---------
TEMP_01 ONLINE
--物理上没有文件，问题就在这儿
SQL> select file_name,tablespace_name from dba_temp_files;
no rows selected
--确定临时文件是存在的，把该临时文件添加到表空间
SQL> alter tablespace temp add tempfile 'J:\app\wufan\oradata\orcl\temp01.dbf';
Tablespace altered.
--上面那条语句把文件对应到temp表空间了，其实sys的默认临时表空间是temp_01。
SQL> select tablespace_name,file_name from dba_temp_files;
TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
TEMP
J:\APP\WUFAN\ORADATA\ORCL\TEMP01.DBF
--将错就错吧，就将sys默认表空间改成temp吧
SQL> alter user sys temporary tablespace temp;
User altered.
--查看是否改过来了
SQL> select username,temporary_tablespace from dba_users where username='SYS';
USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
SYS TEMP
重新进行实验：
开启连个事务：
--这边报了个错，因为做了很多次实验，现在这个薪水的&＃20540;已经很大了，超出了字段的长度
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.
--因为失败了一个语句，所以查询就只有3个事务。
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
--执行回退到倒数第二个事务，用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
--失败了，因为倒数第二个事务依赖于倒数第一个事务
--重新用cascade选项，把倒数第二个事务依赖的第一个事务一起回退掉
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.
--过程执行成功，但是别忘了commit，oracle在过程里面并没有提交，需要你手动提交才能生效
SQL> commit ;
Commit complete.
SQL>