Problems encountered in the experiment: 1. At the beginning of the experiment, resetlogs reset the online log, resulting in misslogfile errors always reported during the flash. In this case, you need to use the noretlogs method to recreate the control file, and then restart the database to the open state. SQLSELECTdistinctxid, commit_scnFROMflashback_tran
Problems encountered in the experiment: 1. At the beginning of the experiment, resetlogs reset the online log, which always reports the error of missing logfile in the flash. In this case, you need to use the noretlogs method to recreate the control file, and then restart the database to the open state. SQL SELECT distinct xid, commit_scn FROM flashback_tran
Problems encountered in the experiment:
1. At the beginning of the experiment, resetlogs reset the online log, which always reports the error of missing logfile during the flash.
In this case, you need to use the noretlogs method to recreate the control file, 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) = 'ployees'
4 and t. commit_timestamp> paiimestamp-interval '90' minute
5 order by t. commit_scn;
XID COMMIT_SCN
--------------------------
20174005e010000 2948380
0E0019005E010000 2948386
SQL> declare
2 xids sys. xid_array;
3 begin
4 xids: = sys. xid_array ('00004005e010000 ');
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
This problem occurs when I reset the log file when I started the database, so that the database does not read logs. You need to recreate the control file.
Shutdown immediate
Startup nomount
Alter database backup controlfile to trace as 'J: \ app \ wufan \ diag \ rdbms \ 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 50 m blocksize 512,
GROUP 2 'J: \ APP \ WUFAN \ ORADATA \ ORCL \ REDO02.LOG 'SIZE 50 m blocksize 512,
GROUP 3 'J: \ APP \ WUFAN \ ORADATA \ ORCL \ redo03.log' SIZE 50 m 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
;
-- In this case, it does not need to be restored. If you execute this command, it will tell you that there is nothing to restore.
Recover database;
-- Open all the Supplemental log files.
Alter database add supplemental log data;
-- Open the system archive, which is already in the archive status. Therefore, this command reports an error.
Alter system archive log all;
-- Open the database
Alter database open;
Now, the reconstruction control file has been completed.
Retry the test
1. Start 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 transaction numbers
SQL> SELECT distinct xid, commit_scn FROM flashback_transaction_query t
2 where table_owner = 'hr'
3 and lower (t. table_name) = 'ployees'
4 and t. commit_timestamp> policimestamp-interval '15' minute
5 order by t. commit_scn;
XID COMMIT_SCN
--------------------------
13001A0061010000 2983670
0F0021005D010000 2983677
3. Execute things 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
It's scary to see an error! The problem is that
[ORA-25153:
Temporary Tablespace is Empty
The temporary tablespace is empty. Here we will confirm this problem:
-- The default temporary tablespace of the current user
SQL> select username, temporary_tablespace from dba_users where username = 'sys ';
USERNAME TEMPORARY_TABLESPACE
------------------------------------------------------------
SYS TEMP_01
-- The tablespace is logically online by default. No problem
SQL> select tablespace_name, status from dba_tablespaces where tablespace_name = 'temp _ 01 ';
TABLESPACE_NAME STATUS
---------------------------------------
TEMP_01 ONLINE
-- There are no files physically. The problem is here.
SQL> select file_name, tablespace_name from dba_temp_files;
No rows selected
-- Make sure that the temporary file exists and add the temporary file to the tablespace.
SQL> alter tablespace temp add tempfile 'J: \ app \ wufan \ oradata \ orcl \ temp01.dbf ';
Tablespace altered.
-- The preceding statement maps the file to the temp tablespace. In fact, the default temporary tablespace of 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
-- Change the default table space in sys to temp.
SQL> alter user sys temporary tablespace temp;
User altered.
-- Check whether it has been changed
SQL> select username, temporary_tablespace from dba_users where username = 'sys ';
USERNAME TEMPORARY_TABLESPACE
------------------------------------------------------------
SYS TEMP
Repeat the experiment:
Start a transaction:
-- An error is reported here, because I have done many experiments, and now the salary value is large, which exceeds 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 one statement fails, only three transactions are queried.
SQL> SELECT distinct xid, commit_scn FROM flashback_transaction_query t
2 where table_owner = 'hr'
3 and lower (t. table_name) = 'ployees'
4 and t. commit_timestamp> policimestamp-interval '15' minute
5 order by t. commit_scn;
XID COMMIT_SCN
--------------------------
13001A0061010000 2983670
0F0021005D010000 2983677
0D00050064010000 2984032
-- Execute the rollback to the second to last 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 penultimate transaction depends on the penultimate transaction.
-- Re-use the cascade option to return the first transaction on which the last and second transactions depend.
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 is successfully executed, but don't forget the commit. oracle has not submitted the process. You need to manually submit the process to take effect.
SQL> commit;
Commit complete.
SQL>
