Problems encountered in the experiment:
1, just beginning to do the previous experiment, resetlogs reset the online log, resulting in flash back when always reported miss logfile error.
This time you need to rebuild the control file in a noretlogs way, and then restart the database to the open state.
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 ' 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
The problem is that I reset the log file when I started the database, causing the database to not read the log. The control file needs to be rebuilt.
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
;
--This does not need to be restored, you execute this command and it will tell you that there is nothing to recover.
RECOVER DATABASE;
--Open all the supplemental log files, can not do
ALTER DATABASE ADD supplemental LOG DATA;
--Open the system archive, is currently an archive status, so this command will be error, do not control
ALTER SYSTEM ARCHIVE LOG all;
--Open Database
ALTER DATABASE OPEN;
The Rebuild control file is now complete
To re-test
1, open two things
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, query two things number
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 ' minute
5 order by T.COMMIT_SCN;
XID COMMIT_SCN
---------------- ----------
13001a0061010000 2983670
0f0021005d010000 2983677
3, the implementation of the thing Flash back
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
Look at the error more frightening! In fact, the problem is
[ORA-25153:
Temporary tablespace is Empty
The temporary table space is empty. Here's how to confirm the problem:
--the current User default temp table space
Sql> Select Username,temporary_tablespace from dba_users where username= ' SYS ';
USERNAME Temporary_tablespace
------------------------------ ------------------------------
SYS temp_01
--The default table space is logically online, no problem
Sql> Select Tablespace_name,status from dba_tablespaces where tablespace_name= ' temp_01 ';
Tablespace_name STATUS
------------------------------ ---------
Temp_01 ONLINE
--There's no physical file, and here's the problem.
Sql> select File_name,tablespace_name from Dba_temp_files;
No rows selected
--to determine that a temporary file exists, add the temporary file to the Tablespace
sql> Alter tablespace temp add tempfile ' J:\app\wufan\oradata\orcl\temp01.dbf ';
Tablespace altered.
The above statement corresponds the file to the temp table space, in fact the default temp tablespace for SYS is temp_01.
Sql> select Tablespace_name,file_name from Dba_temp_files;
Tablespace_name
------------------------------
file_name
--------------------------------------------------------------------------------
TEMP
J:\APP\WUFAN\ORADATA\ORCL\TEMP01. Dbf
--will wrong, change the SYS default tablespace to temp.
sql> alter user sys temporary TABLESPACE temp;
User altered.
--Check to see if it's changed.
Sql> Select Username,temporary_tablespace from dba_users where username= ' SYS ';
USERNAME Temporary_tablespace
------------------------------ ------------------------------
SYS TEMP
To re-experiment:
To open a single transaction:
--This is a mistake, because I have done many experiments, now the value of this salary is very large, beyond the length of the field
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.
--Because a statement has failed, the query has only 3 transactions.
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 ' minute
5 order by T.COMMIT_SCN;
XID COMMIT_SCN
---------------- ----------
13001a0061010000 2983670
0f0021005d010000 2983677
0d00050064010000 2984032
--performing a fallback to the penultimate transaction, with 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
--failed because the second-to-last transaction relies on the penultimate one
--Re-use the CASCADE option to return the first transaction dependent on the second-to-last transaction
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.
--The process executes successfully, but don't forget that commit,oracle is not committed in the process and requires you to submit it manually to take effect.
Sql> commit;
Commit complete.
Sql>
ora-01291:missing logfile transaction flash back missing log
