UNDO tablespace Backup Recovery

UNDOThe tablespace is restored when there is hot standby and no transaction occurs.

First, let's take a look at ensuringUndoNo transactions in the tablespace

SQL> select XID USN, XIDSLOT, XIDSQN fromv $ transaction;

No rows selected

Second, confirm the currently used tablespace for rollback

SQL> show parameter undo

 

NAME TYPE VALUE

-----------------------------------------------------------------------------

Undo_management string AUTO

Undo_retention integer 900

Undo_tablespace string UNDOTBS1

Third, view the data files corresponding to the tablespace.

SQL> select tablespace_name, file_name from dba_data_Files

TABLESPACE_NAME FILE_NAME

----------------------------------------------------------------------

USERS/u01/tiger/oradata/orcl2/users01.dbf

SYSAUX/u01/tiger/oradata/orcl2/sysaux01.dbf

SYSTEM/u01/tiger/oradata/orcl2/system01.dbf

UNDOTBS2/U01/tiger/oradata/orcl2/undotbs02.dbf

TIGER/u01/tiger/oradata/orcl2/tiger. dbf

Fourth, view the status of the data file

SQL> selectfile #, name, status, recover, error from v $ datafile_header;

 

FILE # NAME STATUS REC ERROR

--------------------------------------------------------------------------------

1/u01/tiger/oradata/orcl2/system01.dbf ONLINE NO

3/u01/tiger/oradata/orcl2/sysaux01.dbf ONLINE NO

4/u01/tiger/oradata/orcl2/users01.dbf ONLINE NO

5/u01/tiger/oradata/orcl2/undotbs02.dbf ONLINE NO

7/u01/tiger/oradata/orcl2/tiger. dbf ONLINE NO

Fifth, Hot BackupUndoTablespace

SQL> alter tablespace undotbs2 beginbackup;

 

Tablespace altered.

 

SQL>! Cp/u01/tiger/oradata/orcl2/undotbs02.dbf/bk

 

SQL> alter tablespace undotbs2 endbackup;

 

Tablespace altered.

If you are executing this command

SQL> alter tablespace undotbs2 beginbackup;

Error:

ERROR at line 1:

ORA-01123: cannot start online backup; media recovery notenabled

This indicates that your database is not in the archive mode, and hot backup cannot be performed when it is not in the archive mode. You only need to change the database to the archive mode.

Sixth, destroy the data block UNDO

QL>! Cp/etc/passwd/u01/tiger/oradata/orcl2/undotbs02.dbf

 

SQL> selectfile #, name, status, recover, error from v $ datafile_header;

 

FILE # NAME STATUS REC ERROR

--------------------------------------------------------------------------------

1/u01/tiger/oradata/orcl2/system01.dbf ONLINE NO

3/u01/tiger/oradata/orcl2/sysaux01.dbf ONLINE NO

4/u01/tiger/oradata/orcl2/users01.dbf ONLINE NO

5/U01/tiger/oradata/orcl2/undotbs02.dbf ONLINE CANNOTREAD HEADER

7/u01/tiger/oradata/orcl2/tiger. dbf ONLINE NO

7. EnableUNDOIf the file is offline, the file write operation is triggered, and an error is reported.UNDOoffline

SQL> alter database datafile 5 offline;

Alter database datafile 5 offline

*

ERROR at line 1:

ORA-00603: Oracle server session terminatedby fatal error

SQL>/

 

FILE # NAME STATUS REC ERROR

--------------------------------------------------------------------------------

1/u01/tiger/oradata/orcl2/system01.dbf ONLINE NO

3/u01/tiger/oradata/orcl2/sysaux01.dbf ONLINE NO

4/u01/tiger/oradata/orcl2/users01.dbf ONLINE NO

5/U01/tiger/oradata/orcl2/undotbs02.dbf OFFLINE WRONG FILE TYPE

7/u01/tiger/oradata/orcl2/tiger. dbf ONLINE NO

Eighth, recovery

SQL>! Cp/bk/undotbs02.dbf/u01/tiger/oradata/orcl2/

 

SQL> selectfile #, name, status, recover, error from v $ datafile_header;

 

FILE # NAME STATUS REC ERROR

--------------------------------------------------------------------------------

1/u01/tiger/oradata/orcl2/system01.dbf ONLINE NO

3/u01/tiger/oradata/orcl2/sysaux01.dbf ONLINE NO

4/u01/tiger/oradata/orcl2/users01.dbf ONLINE NO

5/U01/tiger/oradata/orcl2/undotbs02.dbf OFFLINEYES

7/u01/tiger/oradata/orcl2/tiger. dbf ONLINE NO

The file header is identified correctly but must be restored.

SQL> recover datafile 5;

ORA-00603: ORACLE server session terminatedby fatal error

 

 

SQL> conn/as sysdba

Connected.

SQL> col error for a20

SQL> selectfile #, name, status, recover, error from v $ datafile_header;

 

FILE # NAME STATUS REC ERROR

--------------------------------------------------------------------------------

1/u01/tiger/oradata/orcl2/system01.dbf ONLINE NO

3/u01/tiger/oradata/orcl2/sysaux01.dbf ONLINE NO

4/u01/tiger/oradata/orcl2/users01.dbf ONLINE NO

5/u01/tiger/oradata/orcl2/undotbs02.dbfOFFLINENO

7/u01/tiger/oradata/orcl2/tiger. dbf ONLINE NO

Make5No. Data File launched

SQL> alter database datafile 5 online;

 

Database altered.

 

SQL> selectfile #, name, status, recover, error from v $ datafile_header;

 

FILE # NAME STATUS REC ERROR

--------------------------------------------------------------------------------

1/u01/tiger/oradata/orcl2/system01.dbf ONLINE NO

3/u01/tiger/oradata/orcl2/sysaux01.dbf ONLINE NO

4/u01/tiger/oradata/orcl2/users01.dbf ONLINE NO

5/u01/tiger/oradata/orcl2/undotbs02.dbf ONLINE NO

7/u01/tiger/oradata/orcl2/tiger. dbf ONLINE NO

1 2 3 4 Next Page