UNDO tablespace loss caused by rman backup and recovery

The UNDO tablespace is used to store UNDO data. When you perform DML operations (INSERT, UPDATE, DELETE), ORACLE writes the old data of these operations to the UNDO segment. UNDO count

The UNDO tablespace is used to store UNDO data. When you perform DML operations (INSERT, UPDATE, DELETE), ORACLE writes the old data of these operations to the UNDO segment. UNDO count

UNDO tablespace description

In the previous article (RMAN backup and recovery for the loss of offline data files), we talked about how to deal with the loss of offline data files. This article describes how to solve the loss of UNDO tablespace.

The UNDO tablespace is used to store UNDO data. When you perform DML operations (INSERT, UPDATE, DELETE), Oracle writes the old data of these operations to the UNDO segment. UNDO data, also known as rollback data, is used to ensure data consistency. Functions include: rollback, read consistency, transaction recovery, and flashback query. Start from 9i. You can use UNDO tablespace or rollback segments to manage UNDO data. At the beginning of 10 Gb, ORACLE has abandoned the use of rollback segments. When we mention the UNDO tablespace, we have to mention the UNDO segment. UNDO Segment is divided into two parts: one is the UNDO Segment Head and the other is the UNDO Segment Block (also known as the transaction slot ). The UNDO Segment Head contains the transaction information of This rollback Segment and has a pointer pointing to the Undo Segment Block. UNDO tablespace is very important. If it is lost, data cannot be updated. In normal database management, pay attention to whether the space of the UNDO tablespace is sufficient, whether to use automatic expansion or limit the size, and how to set the undo_retention value.

2. UNDO tablespace backup and recovery

To back up and restore UNDO tablespaces, you must first have a backup. After the RMAN backup is complete, we simulate UNDO tablespace loss. The update operation is still successful because the shared pool and buffer cache store the updated information. If we refresh the shared pool and buffer cache, and then connect to the user or update the operation, the system will prompt that the data file cannot be found. The UNDO tablespace is lost and the UNDO tablespace cannot be taken offline. Therefore, the UNDO tablespace cannot be restored when the database is running. This requires us to disable the database for restoration. If you perform operations in the real environment, you must perform operations in off-peak hours or test libraries. We failed to use consistency to close the database, but only forced to close the database. At this time, the parameter file and control file are normal, but the data file is abnormal, so we can start the database to the MOUNT state. After the MOUNT state is started, we need to take the UNDO tablespace data file offline. Pay attention to the data file number at this time. Then, log on to RMAN and restore the UNDO tablespace data file. In fact, a copy operation is performed to copy the UNDO tablespace data file from the backup file to the data directory. After the copy is complete, we need to restore the UNDO tablespace data file. After the restoration is complete, the UNDO tablespace data file is online. The database is in the MOUNT state and we need to open the database. If all operations are successful, you can update the data.

Three Simulation

Step 1. Backup the entire database in RMAN

RMAN> backup database;

Starting backup at 12-DEC-13
Using channel ORA_DISK_1
Channel ORA_DISK_1: starting full datafile backup set
Channel ORA_DISK_1: specifying datafile (s) in backup set
Input datafile file number = 00001 name =/u01/oracle/oradata/justdb/system01.dbf
Input datafile file number = 00002 name =/u01/oracle/oradata/justdb/sysaux01.dbf
Input datafile file number = 00003 name =/u01/oracle/oradata/justdb/undotbs01.dbf
Input datafile file number = 00004 name =/u01/oracle/oradata/justdb/users01.dbf
Channel ORA_DISK_1: starting piece 1 at 12-DEC-13
Channel ORA_DISK_1: finished piece 1 at 12-DEC-13
Piece handle =/u01/oracle/fast_recovery_area/JUSTDB/backupset/2013_12_12/partition _. bkp tag = TAG20131212T095816 comment = NONE
Channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Channel ORA_DISK_1: starting full datafile backup set
Channel ORA_DISK_1: specifying datafile (s) in backup set
Including current control file in backup set
Including current SPFILE in backup set
Channel ORA_DISK_1: starting piece 1 at 12-DEC-13
Channel ORA_DISK_1: finished piece 1 at 12-DEC-13
Piece handle =/u01/oracle/fast_recovery_area/JUSTDB/backupset/2013_12_12/partition _. bkp tag = TAG20131212T095816 comment = NONE
Channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 12-DEC-13

Step 2: Simulate UNDO tablespace loss

SQL> CONN/AS SYSDBA
Connected.
SQL> HO mv/u01/oracle/oradata/justdb/undotbs01.dbf/opt/learn/

Step 3: connect to the sys user in SQL Plus and refresh the shared pool and buffer cache.

SQL> CONN/AS SYSDBA
Connected.

SQL> CONN/AS SYSDBA
Connected.
SQL> ALTER SYSTEM FLUSH shared_pool;

System altered.

SQL> ALTER SYSTEM FLUSH buffer_cache;

System altered.

Step 4, SQL Plus connected to scoot user, found ORA-01110 error, data file not found

SQL> CONN SCOTT/tiger;
ERROR:
ORA-00604: error occurred at recursive SQL level 1
The ORA-01116: error in opening database file 3
ORA-01110: data file 3: '/u01/oracle/oradata/justdb/undotbs01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

Warning: You are no longer connected to ORACLE.

Step 5: SQL Plus closes the database consistently. If it fails, only the database is forcibly disabled.

SQL> CONN/AS SYSDBA
CONN/AS SYSDBA
Connected.
SQL> SHUTDOWN IMMEDIATE;
The ORA-01116: error in opening database file 3
ORA-01110: data file 3: '/u01/oracle/oradata/justdb/undotbs01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> SHUTDOWN ABORT;
ORACLE instance shut down.

Step 6: log on to SQL Plus again and start the database to the MOUNT status.

[Oracle @ orcl ~] $ Sqlplus
[Uniread] Loaded history (157 lines)

SQL * Plus: Release 11.2.0.3.0 Production on Thu Dec 12 10:37:52 2013

Copyright (c) 1982,201 1, Oracle. All rights reserved.

Connected to an idle instance.

SQL> STARTUP MOUNT;
ORACLE instance started.