Undo tablespace fault special recovery (2) -- ORA-01092: Oracle instance termination. Force disconnect

Author: skate

Time: 2010-09-10

 

Undo tablespace fault special recovery (2)

This test shows that the recovery of instance recover (crash recovery in a single instance) requires the data in the failed undo,
In general, instance recover uses online log files. When a multi-version update failure occurs, you can also roll back the segment data.

Test Table

SQL> select count (1) From tabtest;

Count (1)
----------
17732

 

SQL> insert into tabtest select * From tabtest where rownum <2001;

You have created 2000 rows.

 

SQL> insert into tabtest select * From tabtest where rownum <2001;

You have created 2000 rows.

 

Simulate power failure, so that data in the rollback segment is not rolled back, so that the rollback segment retains transactions that are not committed when the database is closed.

 

SQL> shutdown abort
The Oracle routine has been disabled.

SQL> quit
From Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-Production
With the partitioning, OLAP, data mining and real application testing options disconnected
Open

 

Only when you exit the sqlplus environment can you change the rollback segment data file, delete the rollback data file, and simulate the rollback segment loss.

 

C:/Documents and Settings/Administrator> sqlplus "/As sysdba"

SQL * Plus: Release 10.2.0.4.0-production on Thursday September 9 22:23:50 2010

Copyright (c) 1982,200 7, Oracle. All rights reserved.

Already connected to the idle routine.

SQL> startup
The Oracle routine has been started.

Total system global area 574619648 bytes
Fixed size 1297944 bytes
Variable Size 234881512 bytes
Database buffers 331350016 bytes
Redo buffers 7090176 bytes
The database has been loaded.
ORA-01157: unable to identify/lock data file 2-see dbwr trace file
ORA-01110: data file 2: 'e:/Oracle/product/10.2.0/oradata/test/undotbs13.dbf'

The error occurs because I have deleted the file "E:/Oracle/product/10.2.0/oradata/test/undotbs13.dbf"

 

SQL> shutdown abort;
The Oracle routine has been disabled.

 

Here, startup force is used to simulate the data in the rollback segment during instance recover,I don't know why it can be simulated. I found it through multiple tests. Which of the following experts knows why ????

 

 

SQL> startup force;
The Oracle routine has been started.

Total system global area 574619648 bytes
Fixed size 1297944 bytes
Variable Size 234881512 bytes
Database buffers 331350016 bytes
Redo buffers 7090176 bytes
The database has been loaded.
ORA-01157: unable to identify/lock data file 2-see dbwr trace file
ORA-01110: data file 2: 'e:/Oracle/product/10.2.0/oradata/test/undotbs13.dbf'

 

SQL> alter database datafile 2 offline drop;

The database has been changed.

 

SQL> alter database open;
Alter database open
*
Row 3 has an error:
ORA-01092: Oracle instance terminated. Force disconnect

 

Here, instance recover requires data in the rollback segment, but the rollback segment is lost, so the instance is shut down directly if an exception occurs.

SQL> startup
ORA-24324: Service handle not initialized
ORA-01041: Internal error, hostdef extension does not exist

 

SQL> quit
From Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-Production
With the partitioning, OLAP, data mining and real application testing options disconnected
Open

 

C:/Documents and Settings/Administrator> sqlplus "/As sysdba"

SQL * Plus: Release 10.2.0.4.0-production on Thursday September 9 22:27:05 2010

Copyright (c) 1982,200 7, Oracle. All rights reserved.

Already connected to the idle routine.

 

SQL> startup
The Oracle routine has been started.

Total system global area 574619648 bytes
Fixed size 1297944 bytes
Variable Size 234881512 bytes
Database buffers 331350016 bytes
Redo buffers 7090176 bytes
The database has been loaded.
ORA-01092: Oracle instance terminated. Force disconnect

SQL>

 

Check the alertlog file. The error message is as follows:

 

....

Errors in file E:/Oracle/product/10.2.0/admin/test/bdump/test_smon_1828.trc:
ORA-00604: Error occurred at recursive SQL Level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: 'e:/Oracle/product/10.2.0/oradata/test/undotbs13.dbf'

Thu Sep 09 22:27:17 2010
Errors in file E:/Oracle/product/10.2.0/admin/test/udump/test_ora_3844.trc:
ORA-00604: recursive SQL Level 1 error
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: 'e:/Oracle/product/10.2.0/oradata/test/undotbs13.dbf'

Error 604 happened during dB open, shutting down Database
User: Terminating instance due to error 604.
Thu Sep 09 22:27:17 2010
Errors in file E:/Oracle/product/10.2.0/admin/test/bdump/test_pmon_5952.trc:
ORA-00604: Error occurred at recursive SQL level

Thu Sep 09 22:27:18 2010
Errors in file E:/Oracle/product/10.2.0/admin/test/bdump/test_reco_3988.trc:
ORA-00604: Error occurred at recursive SQL level

Thu Sep 09 22:27:18 2010
Errors in file E:/Oracle/product/10.2.0/admin/test/bdump/test_ckpt_5320.trc:
ORA-00604: Error occurred at recursive SQL level

Thu Sep 09 22:27:18 2010
Errors in file E:/Oracle/product/10.2.0/admin/test/bdump/test_lgwr_1312.trc:
ORA-00604: Error occurred at recursive SQL level

Thu Sep 09 22:27:18 2010
Errors in file E:/Oracle/product/10.2.0/admin/test/bdump/test_mman_4972.trc:
ORA-00604: Error occurred at recursive SQL level

Thu Sep 09 22:27:18 2010
Errors in file E:/Oracle/product/10.2.0/admin/test/bdump/test_dbw0_4060.trc:
ORA-00604: Error occurred at recursive SQL level

Thu Sep 09 22:27:18 2010
Errors in file E:/Oracle/product/10.2.0/admin/test/bdump/test_psp0_1_6.trc:
ORA-00604: Error occurred at recursive SQL level

Instance terminated by user, pid = 3844
ORA-1092 signalled during: Alter database open...

.....

 

According to the error message, data in the rollback segment will be lost due to faults. If there is a backup, you can directly recover datafile to recover it easily. If there is no backup,
What should we do? The database cannot be opened because of a data file failure in the rollback segment, so we can try to bypass the database check or fix the number of rollback segments.
When the database is open, all data files must be online. If they cannot be online, they must be restored or directly drop the data files. We do not have
If the backup cannot be recovered, drop the faulty data file, enable the data to be opened, and create a new tablespace In the rollback segment so that the database can use the new rollback segment.
Finally, delete the tablespace In the faulty rollback segment. This is the basic idea for solving the problem. The procedure is as follows:

Modify the parameter file and add implicit Parameters

I only used "_ uploupted_rollback_segments" here"

The last append of the text parameter file inittest. ora is shown as the next record.

*. _ Syssmu12 $, _ syssmu13 $, _ syssmu14 $, _ syssmu15 $, _ syssmu16 $, _ syssmu17 $, _ syssmu18 $, _ syssmu19 $, _ syssmu20 $, _ syssmu11 $)

SQL> quit
From Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-Production
With the partitioning, OLAP, data mining and real application testing options disconnected
Open

C:/Documents and Settings/Administrator> sqlplus "/As sysdba"

SQL * Plus: Release 10.2.0.4.0-production on Thursday September 9 22:32:43 2010

Copyright (c) 1982,200 7, Oracle. All rights reserved.

Already connected to the idle routine.

 

SQL> startup pfile = 'e:/Oracle/product/10.2.0/db_1/database/inittest. ora'
The Oracle routine has been started.

Total system global area 574619648 bytes
Fixed size 1297944 bytes
Variable Size 234881512 bytes
Database buffers 331350016 bytes
Redo buffers 7090176 bytes
The database has been loaded.
The database has been opened.

 

 

Now that the database has been opened, it is much simpler to create a new tablespace for the rollback segment and delete the tablespace for the faulty rollback segment.

SQL>

SQL> show parameter undo

Name type value
-----------------------------------------------------------------------------
Undo_management string auto
Undo_retention integer 900
Undo_tablespace string undotbs1

 

SQL> Create undo tablespace undotbs4 datafile 'e:/Oracle/product/10.2.0/oradata/
Test/undotbs41.dbf 'size 50 m;

The tablespace has been created.

 

 

SQL> show parameter undo

Name type value
-----------------------------------------------------------------------------
Undo_management string auto
Undo_retention integer 900
Undo_tablespace string undotbs1
SQL> alter system set undo_tablespace = 'undotbs4 'scope = spfile;
Alter system set undo_tablespace = 'undotbs4 'scope = spfile
*
Row 3 has an error:
ORA-32001: spfile requested to be written, but spfile not specified at startup

 

SQL> alter system set undo_tablespace = 'undotbs4 ';

The system has been changed.

SQL>

 

SQL> shutdown immediate
The database has been closed.
The database has been detached.
The Oracle routine has been disabled.

SQL> startup pfile = 'e:/Oracle/product/10.2.0/db_1/database/inittest. ora'
The Oracle routine has been started.

Total system global area 574619648 bytes
Fixed size 1297944 bytes
Variable Size 234881512 bytes
Database buffers 331350016 bytes
Redo buffers 7090176 bytes
The database has been loaded.
The database has been opened.

 

Check the current rollback segments

 

 

SQL> select segment_name, status, tablespace_name from dba_rollback_segs;

Segment_name status tablespace_name
----------------------------------------------------------------------------
System Online System
_ Syssmu1 $ needs recovery undotbs1
_ Syssmu2 $ needs recovery undotbs1
_ Syssmu3 $ needs recovery undotbs1
_ Syssmu4 $ needs recovery undotbs1
_ Syssmu5 $ needs recovery undotbs1
_ Syssmu6 $ needs recovery undotbs1
_ Syssmu7 $ needs recovery undotbs1
_ Syssmu8 $ needs recovery undotbs1
_ Syssmu9 $ needs recovery undotbs1
_ Syssmu10 $ needs recovery undotbs1

Segment_name status tablespace_name
----------------------------------------------------------------------------
_ Syssmu11 $ online undotbs4
_ Syssmu12 $ online undotbs4
_ Syssmu13 $ online undotbs4
_ Syssmu14 $ online undotbs4
_ Syssmu15 $ online undotbs4
_ Syssmu16 $ online undotbs4
_ Syssmu17 $ online undotbs4
_ Syssmu18 $ online undotbs4
_ Syssmu19 $ online undotbs4
_ Syssmu20 $ online undotbs4

21 rows have been selected.

 

 

SQL> select * from V $ recover_file;

File # online _
------------------------
Error change #
---------------------------------------------------------------------------
Time
--------------
2 offline
File not found 0

8 offline
16898701
-10

File # online _
------------------------
Error change #
---------------------------------------------------------------------------
Time
--------------

SQL> show parameter undo

Name type value
-----------------------------------------------------------------------------
Undo_management string auto
Undo_retention integer 900
Undo_tablespace string undotbs4
SQL> drop tablespace undotbs4 including contents and datafiles;
Drop tablespace undotbs4 including contents and datafiles
*
Row 3 has an error:
ORA-30013: Restoring tablespace 'undotbs4 'is currently in use

SQL>

SQL> shutdown immediate;
The database has been closed.
The database has been detached.
The Oracle routine has been disabled.

 

 

Modify the text parameter file to add implicit parameters.

The last append of the text parameter file inittest. ora is shown as the next record.

 

*. _ Syssmu2 $, _ syssmu3 $, _ syssmu4 $, _ syssmu5 $, _ syssmu6 $, _ syssmu7 $, _ syssmu8 $, _ syssmu9 $, _ syssmu10 $, _ syssmu1 $)

 

Start Database

SQL> startup pfile = 'e:/Oracle/product/10.2.0/db_1/database/inittest. ora'
The Oracle routine has been started.

Total system global area 574619648 bytes
Fixed size 1297944 bytes
Variable Size 234881512 bytes
Database buffers 331350016 bytes
Redo buffers 7090176 bytes
The database has been loaded.
The database has been opened.

 

SQL> drop tablespace undotbs1 including contents and datafiles;

The tablespace has been deleted.

 

 

SQL> select segment_name, status, tablespace_name from dba_rollback_segs;

Segment_name status tablespace_name
----------------------------------------------------------------------------
System Online System
_ Syssmu11 $ online undotbs4
_ Syssmu12 $ online undotbs4
_ Syssmu13 $ online undotbs4
_ Syssmu14 $ online undotbs4
_ Syssmu15 $ online undotbs4
_ Syssmu16 $ online undotbs4
_ Syssmu17 $ online undotbs4
_ Syssmu18 $ online undotbs4
_ Syssmu19 $ online undotbs4
_ Syssmu20 $ online undotbs4

11 rows have been selected.

 

 

SQL> shutdown immediate;
The database has been closed.
The database has been detached.
The Oracle routine has been disabled.

 

SQL> startup pfile = 'e:/Oracle/product/10.2.0/db_1/database/inittest. ora'
The Oracle routine has been started.

Total system global area 574619648 bytes
Fixed size 1297944 bytes
Variable Size 234881512 bytes
Database buffers 331350016 bytes
Redo buffers 7090176 bytes
The database has been loaded.
The database has been opened.

 

 

 

Check whether the following operations are normal.

SQL> select segment_name, status, tablespace_name from dba_rollback_segs;

Segment_name status tablespace_name
----------------------------------------------------------------------------
System Online System
_ Syssmu11 $ online undotbs4
_ Syssmu12 $ online undotbs4
_ Syssmu13 $ online undotbs4
_ Syssmu14 $ online undotbs4
_ Syssmu15 $ online undotbs4
_ Syssmu16 $ online undotbs4
_ Syssmu17 $ online undotbs4
_ Syssmu18 $ online undotbs4
_ Syssmu19 $ online undotbs4
_ Syssmu20 $ online undotbs4

11 rows have been selected.

 

 

SQL> select count (1) From tabtest;

Count (1)
----------
21732

SQL> Create spfile from pfile;

The file has been created.

SQL> Create spfile from pfile;

The file has been created.

SQL> shutdown immediate;
The database has been closed.
The database has been detached.
The Oracle routine has been disabled.

 

SQL> startup
The Oracle routine has been started.

Total system global area 574619648 bytes
Fixed size 1297944 bytes
Variable Size 234881512 bytes
Database buffers 331350016 bytes
Redo buffers 7090176 bytes
The database has been loaded.
The database has been opened.
SQL>

 

 

Recovered OK

 

 

 

 

 

 

 

Note:

In fact, it is very important to recover the database. Take things seriously and be perfect. Do not leave any regrets. Details are important

 

Aftercare:

0. Check the status of the database and instance (V $ instance, V $ database), and check whether other files are restored (V $ recover_file)
1. Check for invalid object definitions
2. Check for any invalid Index
3. Check whether the application script is normal.
4. Check whether the system has a deadlock
5. Check whether the system resources are normal.
6. View System Log, alertlog, tracelog, and other files

 

 

 

 

---- End -----