undo备份/恢复

来源：互联网发布：太阁立志传 mac 编辑：程序博客网时间：2024/05/22 12:59

联机备份
先启动数据库
alter database open;
将表空间设置为备份模式
alter tablespace users begin backup;
当表空间设置为备份模式后就可以对其中的数据文件进行复制了，使用命令如下
host cp /u01/app/oracle/oradata/dbt/undotbs01.dbf /u01/app/oracle/undotbs01.dbf;
复制之后就不要设置为备份模式了，因此可以返回正常模式
alter tablespace users end backup;

SQL> desc v$tablespace;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
TS#                                                NUMBER
NAME                                               VARCHAR2(30)
INCLUDED_IN_DATABASE_BACKUP                        VARCHAR2(3)
BIGFILE                                            VARCHAR2(3)
FLASHBACK_ON                                       VARCHAR2(3)
ENCRYPT_IN_BACKUP                                  VARCHAR2(3)

SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
TEMP
MYF
MYFTMP

7 rows selected.

SQL> alter tablespace UNDOTBS1 begin backup;

Tablespace altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/dbt/system01.dbf
/u01/app/oracle/oradata/dbt/sysaux01.dbf
/u01/app/oracle/oradata/dbt/undotbs01.dbf
/u01/app/oracle/oradata/dbt/users01.dbf
/u01/app/oracle/oradata/dbt/myf01.dbf
/u01/app/oracle/oradata/dbt/myf02.dbf
/u01/app/oracle/oradata/dbt/myf03.dbf
/u01/app/oracle/oradata/dbt/myf04.dbf

8 rows selected.

SQL> host cp /u01/app/oracle/oradata/dbt/undotbs01.dbf /u01/app/oracle/undotbs01.dbf;

SQL> alter tablespace UNDOTBS1 end backup;

Tablespace altered.

SQL>

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

恢复过程

[oracle@myf dbt]$ rm -rf undotbs01.dbf ----------删掉undo文件，

[oracle@myf ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 21 05:42:56 2013

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 422670336 bytes
Fixed Size                  1336960 bytes
Variable Size             327158144 bytes
Database Buffers           88080384 bytes
Redo Buffers                6094848 bytes
Database mounted.
ORA-01113: file 3 needs media recovery
ORA-01110: data file 3: '/u01/app/oracle/oradata/dbt/undotbs01.dbf'

利用备份文件还原被删掉的undo文件

[oracle@myf oracle]$ cp undotbs01.dbf /u01/app/oracle/oradata/dbt/

SQL> RECOVER TABLESPACE UNDOTBS1; ---------执行这命令，
Media recovery complete.
SQL> alter database open; ----------打开数据，，，