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

Summary of Oracle downtime cases (1)

Case 1: bad UNDO blocks cause Oracle to fail to Open

Scene:

I will be off work on Friday, and the R & D feedback database can not be connected. Because it is the internal database of the company, all the data comes from other libraries imported through expdp/impdp, which is mainly used for problem reproduction processing product BUG. The database is characterized by large amount of data and many users. The database is operating impdp and drop user almost all the time, and the data is allowed to be partially lost. Well, with all that said, I just want to say that the database doesn't have any backups, and of course it doesn't start archive mode.

The database downtime caused by the bad block of UNDO can be located immediately through the alarm log.

ORA-01578: ORACLE data block corrupted (file # 3, block # 280)

ORA-01110: data file 3:'/ u02/app/oracle/oradata/orcl11/ undotbs01.dbf'

The safest way is to restore through rman backup and archiving to ensure data consistency.

But since there is no rman, there is a way to start the database.

It is easy to solve the UNDO corruption of the database in the open state. As long as a new UNDO is created, the current UNDO is switched to the newly created UNDO, and the old UNDO is deleted.

But the current situation is that the database cannot OPEN and can only be started to mount state.

SQL > startup

ORACLE instance started.

Total System Global Area 784998400 bytes

Fixed Size 2257352 bytes

Variable Size 515903032 bytes

Database Buffers 264241152 bytes

Redo Buffers 2596864 bytes

Database mounted.

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-01578: ORACLE data block corrupted (file # 3, block # 280)

ORA-01110: data file 3:'/ u02/app/oracle/oradata/orcl11/ undotbs01.dbf'

Process ID: 8265

Session ID: 1 Serial number: 5

Obviously, creation and deletion of undo tablespaces are not allowed in mount state.

SQL > create undo tablespace UNDOTBS2 datafile'/ u02 size autoextend on orcl11 size undotbs02.dbf' oradata

Create undo tablespace UNDOTBS2 datafile'/ u02 size autoextend on

*

ERROR at line 1:

ORA-01109: database not open

SQL > drop tablespace UNDOTBS1 including contents and datafiles

Drop tablespace UNDOTBS1 including contents and datafiles

*

ERROR at line 1:

ORA-01109: database not open

So when the database cannot open, how to skip the undo bad block and start the database?

At this point, you can use two implicit parameters, "_ CORRUPTED_ROLLBACK_SEGMENTS" and "_ offline_rollback_segments", which have a similar effect.

(1) _ CORRUPTED_ROLLBACK_SEGMENTS means that the damaged rollback segment is skipped during database startup. If you cannot confirm which rollback segment is damaged, you will choose to skip all rollback segments. The specific use is to add parameters to the parameter file.

_ CORRUPTED_ROLLBACK_SEGMENTS= (SYSSMU1 $, _ SYSSMU2 $, _ SYSSMU3 $,... etc)

The specific rollback segment name needs to be queried.

(2) _ offline_rollback_segments indicates that a partial rollback segment offline can be specified when the database is started.

Now that everything is in place, you only need to query the damaged rollback segment names, or query all the rollback segment names and add them to the _ CORRUPTED_ROLLBACK_SEGMENTS parameter, and you can open the database.

In some cases, when the undo is damaged, the number of the rollback segment will be shown in the alarm log, but the specific rollback segment name will not be shown this time.

So the question is, how to check the name of the UNDO rollback segment in mount?

Obviously, it is impossible to query directly in mount state.

SQL > select * from v$rollname

Select * from v$rollname

*

ERROR at line 1:

ORA-01219: database not open: queries allowed on fixed tables/views only

So how do you query the database UNDO segment name when the database is mount or closed?

In general, there are two ways:

Method 1: use the operating system command strings, this case database operating system is Redhat6.5

1.Get SMU information as following command:

[root@chenjch ~] # $strings system01.dbf | grep _ SYSSMU | cut-d $- f 1 | sort-u > listSMU

Show parameter corrupt

4. Recover and open the database:

SQL > RECOVER DATABASE UNTIL CANCEL

Cancel

SQL > ALTER DATABASE OPEN RESETLOGS

Method 2: bbed tool, you can directly query or modify part of the data block information without starting the database, of course, you can also query the UNDO segment name.

11g does not have this tool at the beginning and needs to be downloaded separately

-Oracle 11g version, undo$ information is generally located in the 225th data block of document 1.

BBED > set file 1 block 225

FILE# 1

BLOCK# 225

BBED > map

File: / u02/app/oracle/oradata/orcl11/system01.dbf (1)

Block: 225 Dba:0x004000e1

KTB Data Block (Table/Cluster)

.

Sb2 kdbr [25] @ 86-contains 25 UNDO segments

.

BBED > p kdbr

Sb2 kdbr [0] @ 86 8078

Sb2 kdbr [1] @ 88 8011

.

Sb2 kdbr [23] @ 132 6537

Sb2 kdbr [24] @ 134 6470

BBED > x / rnc * kdbr [0]-View number UNDO segment name

Col 1 [6] @ 8151: SYSTEM

BBED > x / rnc * kdbr [1]-View No. 1 UNDO segment name

Col 1 [20] @ 8085: _ SYSSMU1_4115952380 $

.

All UNDO segment names can be queried in turn.

Parameter file:

# * .undo _ tablespace='UNDOTBS1'

# * .undo _ management=AUTO

* .undo_tablespace='SYSTEM'

* .undo_management='MANUAL'

*. _ corrupted_rollback_segments='_SYSSMU1_4115952380 $','_ SYSSMU2_3882698531 $','_ SYSSMU3_1780844141 $'. '

SQL > shutdown immediate

SQL > startup

ORACLE instance started.

Total System Global Area 784998400 bytes

Fixed Size 2257352 bytes

Variable Size 515903032 bytes

Database Buffers 264241152 bytes

Redo Buffers 2596864 bytes

Database mounted.

Database opened.

Summary:

Testing by bbed tool, not any UNDO block damage will cause database downtime, only the segment header block damage will cause this problem. You can view the segment header block through the following SQL

Select header_file, header_block

From dba_segments

Where segment_name like'_ SYSSMU%'

Order by 2

UNDO damage can be divided into several situations:

One: if it is damaged in open, you can create, switch or delete it directly, or restore it through backup

Second: cannot OPEN, use implicit parameters to skip damaged segments, start the database, or restore through backup

Sometimes the UNDO damage will not be reported wrong immediately in the alarm log. There has been a power outage in the database server before. When the database is started after an incoming call, the open phase has been stuck and no result has been returned, and the background alarm log has not been output. When another sqlplus window is opened to check the database status, it is found that the database is already in open state, but all dml statements will be stuck and cannot be executed normally. This situation can take into account that when the data block starts the recovery instance with an abnormal power outage, it has to go through rollforward and rollback. After the rollforward ends, you can open the database, and then roll back. In this case, it seems that the rollforward has ended and the rollback is abnormal. It can be guessed that there is a problem with UNDO. In the case of no backup, try to use _ corrupted_rollback_segments to solve the problem.

The second method can be explained in more detail by my other blog.

Http://blog.itpub.net/29785807/viewspace-2128326/

Oracle_UNDO Bad Block testing and repair (BBED)

Welcome to follow my Wechat official account "IT Little Chen" and learn and grow together!

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.