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

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

Database damage is inevitable in the daily work of DBA. The editor will mainly introduce the recovery methods that should be adopted when Oracle database encounters different levels of damage, so that readers can find suitable recovery methods and improve work efficiency when they encounter such situations.

Recovery of data block damage

When only a small number of data blocks in the data file are damaged, we can use RMAN to recover them at the block level. Local recovery at the block level can greatly shorten the recovery time, even to a few thousandths of that of other recovery methods. In addition, in the recovery caused by the damage of the data block, the system can be in the running state, and the data file can also be in the online application state, without setting it offline to keep the application running.

1) Connect to the RMAN environment and connect to the database as SYSDBA (SYSOPER):

$rman target /

2) execute the following data block repair command to repair the data block:

Rman > blockrecover datafile 3 block 2150152

Now that the data blocks are restored, the system continues to run.

Recovery of damaged data files

Step 1: set corrupted data files offline

SQL > alter database datafile 4 offline

Step 2: load the data file by RMAN

RMAN > restore datafile 4

Step 3: recover corrupted data files

RMAN > recover datafile 4

Step 4: set the restored data files online

RMAN > sql "alter database datafile 4 online"

Step 5: check the availability of the data file

SQL > select name,enabled,status from v$datafile

At this point, the data file recovery process for a non-system tablespace is complete.

Recovery of tablespace damage

Of course, there is not only one method of database recovery, but also the administrator can recover according to the method of tablespace recovery. Again, in the above case, if a failure occurs, it is now restored according to the recovery method in the case of table space corruption.

The following uses a backup to restore all the data files in the tablespace users:

Step 1: take the tablespace offline

SQL > alter tablespace users offline

Step 2: load the tablespace

RMAN > restore tablespace users

Step 3: restore the tablespace

RMAN > recover tablespace users

Step 4: bring the tablespace online

RMAN > sql "alter tablespace users online"

Step 5: check the availability of the data file

SQL > select name,enabled,status from v$datafile

Recovery of system tablespace

If the restore tablespace or the system tablespace is damaged, the database system cannot run and the database must not be open. Connect to Oracle in the RMAN environment. Note that the connection is not to the database that is already open, but to the database in the mounted state:

Step 1: connect RMAN

$rman target /

Step 2: load the tablespace

RMAN > restore tablespace system

Step 3: restore the tablespace

RMAN > recover tablespace system

Step 4: open the database

SQL > alter database open

Database crash recovery

The invention relates to a full database recovery method, which is used in the case where the database crashes completely and the database local recovery operation cannot be carried out. This method is simple-loaded back to a full backup set of the database for recovery. Although the method is simple, the cost is huge-it may take a long time! First, you need to start the database instance. When the control file is intact, there is no problem to boot to the mount state, but if the control file is corrupted, it can only be booted to the nomount state. In this example, the database can be started to the mount state:

Step 1: start the instance

SQL > startup mount

Step 2: enter the RMAN environment:

$rman target /

Step 3: load the database backup

RMAN > restore database

Step 4: execute the following command for a full recovery of the database:

RMAN > recover database

Step 5: open the database

RMAN > alter database open

Control the recovery of file corruption

For some reason, such as when all control files are unavailable due to media corruption, the administrator can use RMAN's control files to automatically back up and restore the control files.

For the convenience of description, we assume that in a set of archived databases, all control files are unavailable due to media corruption. Other database files, including data files, parameter files (SPFILE), archive log files, online redo log files, etc., are intact. We can follow the steps below to restore. Because the database control file is corrupted, the database can only be offline at this time.

Step 1: start the database to nomount status:

SQL > startup nomount

Step 2: in the RMAN environment, register the database ID (DBID)

$rman target / rman > set DBID=1195474494

Step 3: load control file backups from database backups centrally

RMAN > restore controlfile from autobackup

Step 4: open the database to mount state:

RMAN > alter database mount

Step 5: restore the database with RMAN backup

RMAN > recover database

Step 6: open the database instance, but you need to resetlogs the log:

RMAN > alter database open resetlogs

Because the database is opened in resetlogs mode, although the data of this system has not been lost, because all log files have been reset, a full database backup is needed. If the control file is damaged, and accompanied by the corruption of other data files, the recovery of the control file described in this section, coupled with the crash recovery of the database, can achieve full recovery (or incomplete recovery) of the database.

Recovery of corrupted log files

Because the database log can use the multi-member mechanism, this method ensures the continuous operation of the system under the damage of a single log file. Even if all members of a log group are corrupted, if it is the current log group, data loss and incomplete recovery of the database are the inevitable choice. If it is not the current log group, it may not need to be repaired at all, just delete the log group and create a new log group.

Step 1: incomplete recovery of the database

SQL > recover database until cancel

Step 2: open the database as you reset the log

SQL > alter database open resetlogs

The database was not fully restored. Of course, the above process can also be done in RMAN.

Restore from a specified backup set

RMAN >

Run {

Set until scn=1645604;-perform an incomplete recovery

Restore database from tag='TAG20110702T231137';-- specifies the tag value of the backup set, which can be found in list backup of database

Recover database

Sql 'alter database open resetlogs'

}

The above is the recovery of different damage levels of the Oracle database shared by the editor. 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.