Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you about the recovery of Oracle database after UNDO damage. The article is rich in content and analyzes and describes for you from a professional point of view. I hope you can get something after reading this article.

The UNDO tablespace stores the pre-mirrored data of the DML operation blocks, which may be used in the UNDO tablespace during data rollback, consistent read, flashback operations, and instance recovery. If the UNDO tablespace is lost or destroyed during production, some transactions may not be rolled back, the database cannot be restored to a consistent state, the Oracle instance may be down, and then the instance cannot be started normally. If there are multiple UNDO tablespace data files, the loss of one of the data files may not lead to instance downtime, the database cannot be cleanly shut down (only SHUTDOWN ABORT), and the database instance can be restarted normally, but all unrolled data blocks cannot be processed. If you try to create a new UNDO tablespace, exp, expdp and other operations, you will receive an error report from ORA-604, ORA-376, and ORA-1110. Let's discuss how to deal with recovery after UNDO damage through an actual case.

One of the customer's system database runs on the HP-UX server, the database version 10.2.0.5, the stand-alone database, the database runs in non-archive mode, and the database files are stored on the bare equipment. Due to the operating system of a file system space is not enough, the maintenance engineer is ready to expand the size of the file system, due to a misoperation in the production period (mistakenly thought that will not affect the operation of the system of an operation) caused the file system can not be mounted after unmounting, unfortunately, do not know who previously for the database UNDO table space and TEMP table space added a data file to the file system. This operation causes some transactions to be suspended and cannot be rolled back, the database cannot be shut down normally, but the transaction can be restarted successfully, the transaction still exists after restart, the rollback segment still shows ONLINE status, the attempt to create a new UNDO table space receives ORA-604, ORA-376, and ORA-1110, and cannot perform exp and expdp operations. The alarm log keeps reporting errors of ORA-604, ORA-376, and and ORA-1110.

When this happens, we first need to determine whether any transactions are affected, determine the scope of the impact, and then try to recover the lost data files (if backup and archiving, online logs are used in archive mode to recover lost files). However, the customer's database is running in non-archive mode and the data files cannot be recovered. Backup is more important than anything else, and all work should be carried out around data security!

The following is the process:

Our basic idea should be to create a new UNDO table space to replace the existing UNDO table space, the loss of UNDO table space data files leads to some transactions can not be rolled back, some data blocks of the database are inconsistent (can be considered to be damaged by logic), but we can accept the damage of some blocks, and the recovery process needs to restart the database instance many times.

If your database can still be cleanly closed, but under normal circumstances you cannot create a new UNDO tablespace, perform the following steps:

I.A. THE DATABASE WAS CLEANLY SHUT DOWN

-

If you are ABSOLUTELY POSITIVE that the database was cleanly shutdown

I.e., it was closed with either shutdown NORMAL or IMMEDIATE, then

The simplest solution is to offline drop the missing datafile, open the

Database in restricted mode, and then drop and recreate the undo

Tablespace to which the file belonged. DO NOT follow this procedure

If the database was shut down ABORT or if it crashed.

The steps are:

1. Make sure the database was last cleanly shut down.

Check the alert.log file for this instance. Go to the bottom of

The file and make sure the last time you shut the database down

You got the messages:

"Shutting down instance (immediate)"

OR

"alter database close normal

Completed: alter database close normal "

This also includes the case of a clean shutdown followed by a

Failed attempt to startup the database. In that case, Oracle will

Issue error messages and shut itself down abort. For the purposes

Of this solution, though, this counts as a clean shutdown.

If that is not the case, i.e., if the last time YOU shut the database

Down it was in abort mode, or the database crashed itself, it is

NOT safe to proceed. You should follow the instructions for

Case I.B below.

2. If using automatic UNDO_MANAGEMENT, comment out this entry from the parameter

File, or set it to MANUAL.

Change UNDO_MANAGEMENT to MANUAL because the UNDO tablespace is in automatic management mode, and failure to successfully create a new rollback segment (which will later DROP the existing tablespace) will lead to database instance downtime.

If using rollback segments, remove all the rollback segments in the

Tablespace to which the lost datafile belongs from the ROLLBACK_SEGMENTS

Parameter in the init.ora file for this instance. If you are not sure about which rollbacks are

In that tablespace, simply comment out the whole ROLLBACK_SEGMENTS entry.

3. Mount the database in restricted mode.

SQL > STARTUP RESTRICT MOUNT

Starting the instance in RESTRICT mode avoids other client connections during processing.

4. Offline drop the lost datafile.

SQL > ALTER DATABASE DATAFILE''OFFLINE DROP

5. Open the database.

SQL > ALTER DATABASE OPEN

You should receive the message "Statement processed,".

If instead you get ORA-604, ORA-376, and ORA-1110, it is likely the shutdown

Was not normal/immediate. Review the rest of the options available and/or

Contact Oracle Support Services.

6. Drop the undo tablespace or tablespace which contains rollback segments

To which the datafile belonged.

SQL > DROP TABLESPACE INCLUDING CONTENTS

7. Recreate the undo tablespace. If using rollback segments, recreate the

Rollback segment tablespace and all it's rollback segments. Remember to

Bring the rollbacks online after you create them.

SQL > CREATE TABLESPACE UNDOTBS2 DATAFILE SIZE 1G AUTOEXTEND ON NEXT 100m

8. Edit the parameter file setting:

UNDO_MANAGEMENT=AUTO

UNDO_TABLESPACE=

If using rollback segments, reinclude the rollbacks you just recreated in

The ROLLBACK_SEGMENTS parameter in the init.ora file for this instance.

As rollback segments were brought online in step # 7, no need to proceed

With shutdown/startup as needed for undo tablespace. All that is required

Is:

SQL > ALTER SYSTEM DISABLE RESTRICTED SESSION

If your database cannot be shut down properly, just add the following parameters to the parameter file before restarting the database instance:

_ allow_resetlogs_corruption=TRUE

_ offline_rollback_segments= "_ SYSSMU1 $"

_ offline_rollback_segments= "_ SYSSMU2 $"

_ offline_rollback_segments= "_ SYSSMU3 $"

_ offline_rollback_segments= "_ SYSSMU4 $"

_ offline_rollback_segments= "_ SYSSMU5 $"

_ offline_rollback_segments= "_ SYSSMU6 $"

_ offline_rollback_segments= "_ SYSSMU7 $"

_ offline_rollback_segments= "_ SYSSMU8 $"

_ offline_rollback_segments= "_ SYSSMU9 $"

_ offline_rollback_segments= "_ SYSSMU10 $"

_ corrupted_rollback_segments= "_ SYSSMU1 $"

_ corrupted_rollback_segments= "_ SYSSMU2 $"

_ corrupted_rollback_segments= "_ SYSSMU3 $"

_ corrupted_rollback_segments= "_ SYSSMU4 $"

_ corrupted_rollback_segments= "_ SYSSMU5 $"

_ corrupted_rollback_segments= "_ SYSSMU6 $"

_ corrupted_rollback_segments= "_ SYSSMU7 $"

_ corrupted_rollback_segments= "_ SYSSMU8 $"

_ corrupted_rollback_segments= "_ SYSSMU9 $"

_ corrupted_rollback_segments= "_ SYSSMU10 $"

The specific value of rollback_segments can be obtained from v$rollname.

After the completion of processing, stop the database instance, remove the above parameters, modify the relevant parameters of UNDO, you can start the database instance normally, and then manually deal with the TEMP data files lost in TEMP tablespaces.

Although the database instance can be started normally and the use of UNDO tablespaces has been restored, it does not mean that inconsistent blocks have been restored and errors may be received when some queries are executed. A logical backup + physical backup should be performed immediately after the database is fully restored to normal to ensure the security of the database.

The above is what the Oracle database UNDO damage recovery is like. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.