Shulou(Shulou.com)06/01 Report--

Power failure caused by the ORA-1172 error resolution process, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

Due to UPS failure, the computer room lost power for many times in succession. After the problem was solved, it was found that a local test database was opened by Times error, ORA-1172, ORA-1151 error.

ORA-1172 error resolution process caused by power outage (1): http://yangtingkun.itpub.net/post/468/465223

Resolution process of ORA-1172 errors caused by power failure (2): http://yangtingkun.itpub.net/post/468/465868

Processing after opening the database:

Simply export the users in the database through EXP or EXPDP tools, rebuild the database, and then import the data.

However, there is much more that can be done for recovery to check the rollback segment status of the database:

SQL > SELECT SEGMENT_NAME, OWNER, TABLESPACE_NAME, STATUS

2 FROM DBA_ROLLBACK_SEGS

SEGMENT_NAME OWNER TABLESPACE_NAME STATUS

--

SYSTEM SYS SYSTEM ONLINE

_ SYSSMU1 $PUBLIC UNDOTBS1 OFFLINE

_ SYSSMU2 $PUBLIC UNDOTBS1 OFFLINE

_ SYSSMU3 $PUBLIC UNDOTBS1 OFFLINE

_ SYSSMU4 $PUBLIC UNDOTBS1 OFFLINE

_ SYSSMU5 $PUBLIC UNDOTBS1 OFFLINE

_ SYSSMU6 $PUBLIC UNDOTBS1 OFFLINE

_ SYSSMU7 $PUBLIC UNDOTBS1 OFFLINE

_ SYSSMU8 $PUBLIC UNDOTBS1 OFFLINE

_ SYSSMU9 $PUBLIC UNDOTBS1 OFFLINE

_ SYSSMU10 $PUBLIC UNDOTBS1 OFFLINE

_ SYSSMU11 $PUBLIC UNDOTBS1 OFFLINE

_ SYSSMU12 $PUBLIC UNDOTBS1 OFFLINE

_ SYSSMU13 $PUBLIC UNDOTBS1 OFFLINE

_ SYSSMU14 $PUBLIC UNDOTBS1 OFFLINE

_ SYSSMU15 $PUBLIC UNDOTBS1 OFFLINE

_ SYSSMU16 $PUBLIC UNDOTBS1 OFFLINE

_ SYSSMU17 $PUBLIC UNDOTBS1 OFFLINE

_ SYSSMU18 $PUBLIC UNDOTBS1 OFFLINE

_ SYSSMU19 $PUBLIC UNDOTBS1 OFFLINE

_ SYSSMU20 $PUBLIC UNDOTBS1 OFFLINE

_ SYSSMU21 $PUBLIC UNDOTBS1 OFFLINE

_ SYSSMU22 $PUBLIC UNDOTBS1 OFFLINE

_ SYSSMU23 $PUBLIC UNDOTBS1 OFFLINE

_ SYSSMU24 $PUBLIC UNDOTBS1 OFFLINE

_ SYSSMU25 $PUBLIC UNDOTBS1 OFFLINE

_ SYSSMU26 $PUBLIC UNDOTBS1 OFFLINE

_ SYSSMU27 $PUBLIC UNDOTBS1 OFFLINE

_ SYSSMU28 $PUBLIC UNDOTBS1 OFFLINE

_ SYSSMU29 $PUBLIC UNDOTBS1 OFFLINE

_ SYSSMU30 $PUBLIC UNDOTBS1 OFFLINE

_ SYSSMU31 $PUBLIC UNDOTBS1 OFFLINE

_ SYSSMU32 $PUBLIC UNDOTBS1 OFFLINE

_ SYSSMU33 $PUBLIC UNDOTBS1 OFFLINE

_ SYSSMU34 $PUBLIC UNDOTBS1 OFFLINE

_ SYSSMU35 $PUBLIC UNDOTBS1 OFFLINE

_ SYSSMU36 $PUBLIC UNDOTBS1 OFFLINE

_ SYSSMU37 $PUBLIC UNDOTBS1 OFFLINE

_ SYSSMU38 $PUBLIC UNDOTBS1 OFFLINE

_ SYSSMU39 $PUBLIC UNDOTBS1 OFFLINE

_ SYSSMU40 $PUBLIC UNDOTBS1 OFFLINE

_ SYSSMU41 $PUBLIC UNDOTBS1 OFFLINE

42 rows selected.

It can be found that except for the SYSTEM rollback segment, all the other rollback segments are in OFFLINE status, and all DML operations return errors:

SQL > DELETE TEST.T

DELETE TEST.T

*

ERROR at line 1:

ORA-01552: cannot use system rollback segment for non-system tablespace 'GPO'

Let's create a new UNDO table space so that ORACLE has available UNDO table spaces:

SQL > CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE'/ data/oradata/test08/undotbs21.dbf'

2 SIZE 4096M

Tablespace created.

The following is to modify the initialization parameter file, change the UNDO tablespace to UNDOTBS2, set UNDO management to AUTO mode, and comment out the implicit parameter _ corrupted_rollback_segments:

* .undo_management='AUTO'

* .undo_tablespace='UNDOTBS2'

An exception occurred while shutting down the database:

SQL > SHUTDOWN IMMEDIATE

After waiting for several hours, SHUTDOWN IMMEDIATE still cannot shut down the database. Check the alert file and find the following information:

Tue Jun 10 17:02:41 2008

Starting background process EMN0

EMN0 started with pid=16, OS id=15734

Tue Jun 10 17:02:41 2008

Shutting down instance: further logons disabled

Tue Jun 10 17:02:41 2008

Stopping background process CJQ0

Tue Jun 10 17:02:41 2008

Stopping background process MMNL

Tue Jun 10 17:02:42 2008

Stopping background process MMON

Tue Jun 10 17:02:43 2008

Shutting down instance (immediate)

License high water mark = 44

Tue Jun 10 17:02:43 2008

Stopping Job queue slave processes

Tue Jun 10 17:02:43 2008

Job queue slave processes stopped

All dispatchers and shared servers shutdown

Tue Jun 10 17:02:50 2008

Process OS id: 15693 alive after kill

Errors in file / opt/ora10g/admin/test08/udump/test08_ora_15629.trc

Log in as SYSDBA in another session, and use SHUTDOWN ABORT to close the database. The session information of SHUTDOWN IMMEDIATE is as follows:

ORA-03113: end-of-file on communication channel

SQL > STARTUP PFILE=/home/oracle/inittest08.ora

ORA-24324: service handle not initialized

ORA-01041: internal error. Hostdef extension doesn't exist

SQL > CONN / AS SYSDBA

Connected to an idle instance.

SQL > STARTUP PFILE=/home/oracle/inittest08.ora

ORACLE instance started.

Total System Global Area 2483027968 bytes

Fixed Size 2074760 bytes

Variable Size 1090520952 bytes

Database Buffers 1375731712 bytes

Redo Buffers 14700544 bytes

Database mounted.

Database opened.

The database can be started normally. Delete the UNDOTBS1 tablespace below:

SQL > DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES

Tablespace dropped.

SQL > DELETE TEST.T

4051072 rows deleted.

SQL > COMMIT

Commit complete.

However, because the database itself is in an abnormal state, a large number of bad blocks can often be found in the background:

Errors in file / opt/ora10g/admin/test08/bdump/test08_smon_19485.trc:

ORA-00604: error occurred at recursive SQL level 1

ORA-01578: ORACLE data block corrupted (file # 1, block # 32529)

ORA-01110: data file 1:'/ data/oradata/test08/system01.dbf'

Wed Jun 11 08:58:29 2008

WARNING: inbound connection timed out (ORA-3136)

Wed Jun 11 09:02:29 2008

Hex dump of (file 3, block 37871) in trace file / opt/ora10g/admin/test08/bdump/test08_m000_19556.trc

Corrupt block relative dba: 0x00c093ef (file 3, block 37871)

Fractured block found during buffer read

Data in bad block:

Type: 6 format: 2 rdba: 0x00c093ef

Last change scn: 0x0001.81e5b9f5 seq: 0x1 flg: 0x06

Spare1: 0x0 spare2: 0x0 spare3: 0x0

Consistency value in tail: 0xb56b0601

Check value in block header: 0x9fc

Computed block checksum: 0xdd4

Reread of rdba: 0x00c093ef (file 3, block 37871) found same corrupted data

Hex dump of (file 3, block 35683) in trace file / opt/ora10g/admin/test08/bdump/test08_m000_19556.trc

Corrupt block relative dba: 0x00c08b63 (file 3, block 35683)

Fractured block found during buffer read

Data in bad block:

Type: 6 format: 2 rdba: 0x00c08b63

Last change scn: 0x0001.856e48fc seq: 0x1 flg: 0x06

Spare1: 0x0 spare2: 0x0 spare3: 0x0

Consistency value in tail: 0x7ead0601

Check value in block header: 0x1214

Computed block checksum: 0x3404

Reread of rdba: 0x00c08b63 (file 3, block 35683) found same corrupted data

Hex dump of (file 3, block 40608) in trace file / opt/ora10g/admin/test08/bdump/test08_m000_19556.trc

Corrupt block relative dba: 0x00c09ea0 (file 3, block 40608)

Fractured block found during buffer read

Data in bad block:

Type: 6 format: 2 rdba: 0x00c09ea0

Last change scn: 0x0001.856e48fc seq: 0x1 flg: 0x06

Spare1: 0x0 spare2: 0x0 spare3: 0x0

Consistency value in tail: 0x65c20601

Check value in block header: 0x9ef1

Computed block checksum: 0x7a6b

Reread of rdba: 0x00c09ea0 (file 3, block 40608) found same corrupted data

Wed Jun 11 09:02:30 2008

Corrupt Block Found

TSN = 2, TSNAME = SYSAUX

RFN = 3, BLK = 37871, RDBA = 12620783

BJN = 8933, BJD = 8933, BJECT = WRH$_SQLTEXT, SUBOBJECT =

SEGMENT WNER = SYS, SEGMENT TYPE = Table Segment

Corrupt Block Found

TSN = 2, TSNAME = SYSAUX

RFN = 3, BLK = 35683, RDBA = 12618595

BJN = 8943, BJD = 8943, BJECT = WRH$_SQL_BIND_METADATA, SUBOBJECT =

SEGMENT WNER = SYS, SEGMENT TYPE = Table Segment

Wed Jun 11 09:07:30 2008

Errors in file / opt/ora10g/admin/test08/bdump/test08_smon_19485.trc:

ORA-00604: error occurred at recursive SQL level 1

ORA-01578: ORACLE data block corrupted (file # 1, block # 32529)

ORA-01110: data file 1:'/ data/oradata/test08/system01.dbf'

Wed Jun 11 09:07:32 2008

Corrupt Block Found

TSN = 2, TSNAME = SYSAUX

RFN = 3, BLK = 40608, RDBA = 12623520

BJN = 8939, BJD = 8939, BJECT = WRH$_SQL_PLAN, SUBOBJECT =

SEGMENT WNER = SYS, SEGMENT TYPE = Table Segment

Therefore, although the database can be used, it is safe to export, rebuild and import in order to prevent further damage to the database.

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.