管理还原数据-管理还原数据

1、如果UNDO当中有事务，UNDO文件被删除如何恢复

i.在scott用户下更改emp表，不提交

SQL> update empset sal = sal +1;

14 rows updated.

 

Ii.删除undo数据文件

SQL> select namefrom v$datafile;

 

NAME

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

/u01/oracle/oradata/wyzc10g/system01.dbf

/u01/oracle/oradata/wyzc10g/undotbs01.dbf

/u01/oracle/oradata/wyzc10g/sysaux01.dbf

/u01/oracle/oradata/wyzc10g/users01.dbf

/u01/oracle/oradata/wyzc10g/example01.dbf

 

SQL> ho rm/u01/oracle/oradata/wyzc10g/undotbs01.dbf

 

Iii.abort关闭并重新启动数据库

SQL> shutdownabort

ORACLE instance shutdown.

SQL> startup

ORACLE instancestarted.

 

Total System GlobalArea  524288000 bytes

Fixed Size                  2097592 bytes

Variable Size             293604936 bytes

DatabaseBuffers          222298112 bytes

Redo Buffers                6287360 bytes

Database mounted.

ORA-01157: cannotidentify/lock data file 2 - see DBWR trace file

ORA-01110: data file2: '/u01/oracle/oradata/wyzc10g/undotbs01.dbf'

 

Oracle在启动时需要为保证事务一致性需要做实例恢复，然后此时找不到UNDO没办法恢复,所以没办法打开数据库

 

Iv.解决方法:

(1)修改undo_management参数为手工，并重新启动数据库

SQL> alter systemset undo_management=manual scope=spfile;

 

System altered.

 

SQL> shutdown

ORA-01109: databasenot open

 

 

Database dismounted.

ORACLE instance shutdown.

SQL> startup

ORACLE instancestarted.

 

Total System GlobalArea  524288000 bytes

Fixed Size                  2097592 bytes

Variable Size             293604936 bytes

DatabaseBuffers          222298112 bytes

Redo Buffers                6287360 bytes

Database mounted.

ORA-01157: cannotidentify/lock data file 2 - see DBWR trace file

ORA-01110: data file2: '/u01/oracle/oradata/wyzc10g/undotbs01.dbf'

 

(2)尝试->将数据库脱机并打开

SQL> alterdatabase datafile 2 offline drop;

 

Database altered.

 

SQL> alterdatabase open;

alter database open

*

ERROR at line 1:

ORA-01092: ORACLEinstance terminated. Disconnection forced

如上，数据库依然没办法打开，因为事务不存在，再次启动数据库

SQL> conn / assysdba

Connected to an idleinstance.

SQL> startup

ORACLE instancestarted.

 

Total System GlobalArea  524288000 bytes

Fixed Size                  2097592 bytes

Variable Size             293604936 bytes

DatabaseBuffers          222298112 bytes

Redo Buffers                6287360 bytes

Database mounted.

ORA-01092: ORACLEinstance terminated. Disconnection forced

如上，此时怎么办呢?

 

(3)当数据库打开失败时，查看告警日志;

在/u01/oracle/admin/wyzc10g/bdump路径下的告警日志:alert_wyzc10g.log

查看到文件末尾最后的日志如下:

Errors in file/u01/oracle/admin/wyzc10g/udump/wyzc10g_ora_13564.trc:

ORA-00604: erroroccurred at recursive SQL level 1

ORA-00376: file 2cannot be read at this time

ORA-01110: data file2: '/u01/oracle/oradata/wyzc10g/undotbs01.dbf'

Error 604 happenedduring db open, shutting down database

USER: terminatinginstance due to error 604

Instance terminatedby USER, pid = 13564

ORA-1092 signalledduring: ALTER DATABASE OPEN...

查看trace文件，在trace文件当中同样可以看到UNDO有问题:

----- Recovery HashTable Statistics ---------

Hash table buckets =32768

Longest hash chain =1

Average hash chain =4/4 = 1.0

Max compares perlookup = 1

Avg compares perlookup = 80/80 = 1.0

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

ORA-00604: erroroccurred at recursive SQL level 1

ORA-00376: file 2cannot be read at this time

ORA-01110: data file2: '/u01/oracle/oradata/wyzc10g/undotbs01.dbf'

 

若要打开数据库,需要用到隐藏参数,如下在11g中查看到的隐藏参数;

SQL> selectksppinm from x$ksppi where ksppinm like '%roll%';

 

KSPPINM

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

_ksxp_disable_rolling_migration

transactions_per_rollback_segment

rollback_segments

_rollback_segment_initial

_rollback_segment_count

_offline_rollback_segments

_corrupted_rollback_segments

_cleanup_rollback_entries

_rollback_stopat

fast_start_parallel_rollback

_max_cr_rollbacks

 

KSPPINM

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

_mv_rolling_inv

_optimizer_nested_rollup_for_gset

 

13 rows selected.

 

(4)创建参数文件， 关闭数据库后修改上述参数

并重新启动

SQL> createpfile='/tmp/a.ora' from spfile;

 

File created.

Vi /tmp/a.ora,在文件末尾添加

_offline_rollback_segments=true，表示允许强制脱机

若还是无法启动，需要在参数文件中再添加如下:

_corrupted_rollback_segments='_SYSSMU1$','_SYSSMU2','_SYSSMU3','_SYSSMU4','_SYSSMU5','_SYSSMU6','_SYSSMU7','_SYSSMU8','_SYSSMU9','_SYSSMU10'

 

查询需要segment状态:

SQL> selectsegment_name,tablespace_name,status from dba_rollback_segs;

 

SEGMENT_NAME                   TABLESPACE_NAME                STATUS

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

SYSTEM                         SYSTEM                         ONLINE

_SYSSMU2$                      UNDOTBS1                       NEEDS RECOVERY

_SYSSMU3$                      UNDOTBS1                       NEEDS RECOVERY

_SYSSMU4$                      UNDOTBS1                       NEEDS RECOVERY

_SYSSMU5$                      UNDOTBS1                       NEEDS RECOVERY

_SYSSMU6$                      UNDOTBS1                       NEEDS RECOVERY

_SYSSMU7$                      UNDOTBS1                       NEEDS RECOVERY

_SYSSMU8$                      UNDOTBS1                       NEEDS RECOVERY

_SYSSMU9$                      UNDOTBS1                       NEEDS RECOVERY

_SYSSMU10$                     UNDOTBS1                       NEEDS RECOVERY

 

10 rows selected.

 

或者:select segment_name from dba_segmentswhere tablespace_name = 'UNDOTBS1';

 

(5)再次重新启动，

startuppfile='/tmp/a.ora';

此时可以重新启动成功，说明参数文件有效

 

(6)按之前的步骤，重新配置UNDO表空间

(6)(1)关闭数据库 shutdown;

(6)(2)启动到mount状态

(6)(3)脱机: alter database datafile 2 offlinedrop;

(6)(4)修改数据库为打开状态 alter database open;

(6)(5)重新建立新的undo表空间

SQL> droptablespace UNDOTBS1;

Tablespace dropped.

 

SQL> create undotablespace undotbs1 datafile '/u01/oracle/oradata/wyzc10g/undotbs01.dbf' size10m autoextend on;

Tablespace created.

 

2、设置UNDO的相关参数

i.查看UNDO保留时间

SQL> showparameter undo_re

 

NAME                                 TYPE        VALUE

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

undo_retention                       integer     900

如上，设的是900S，保留15min,值设置的越大占用的UNDO表空间就越大(若设置的是自动增长)

 

Ii.查看UNDO数据文件是否自动增长

SQL> selectfile_name,autoextensible from dba_data_files;

FILE_NAME                                          AUT

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

/u01/oracle/oradata/wyzc10g/users01.dbf            YES

/u01/oracle/oradata/wyzc10g/sysaux01.dbf           YES

/u01/oracle/oradata/wyzc10g/undotbs01.dbf          YES

/u01/oracle/oradata/wyzc10g/system01.dbf           YES

/u01/oracle/oradata/wyzc10g/example01.dbf          YES

 

UNDO的管理

假定UNDO表空间的大小500M,RETENTION(保留历史数据)设置的是900S

秒                M

1                 100

2                 100

3                 100

4                 200

如上4S之后完成第一个100M做了提交，那么第5S,50M会覆盖第1S的历史数据

所以，如果数据文件设置的是固定大小，不管RETENSION设置的多大，都会被覆盖

若 100 100 30050,均没有提交，则第4秒钟就会报错，说UNDO表空间的大小不足，空间不足。若要避免这个错误，需设置为自动增长

 

Iii.设置UNDO表空间"确保",即不允许覆盖历史数据

 

SQL> selecttablespace_name,retention from dba_tablespaces;

 

TABLESPACE_NAME                RETENTION

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

SYSTEM                         NOT APPLY

UNDOTBS1                       NOGUARANTEE

SYSAUX                         NOT APPLY

TEMP                           NOT APPLY

USERS                          NOT APPLY

EXAMPLE                        NOT APPLY

 

6 rows selected.

 

SQL> altertablespace undotbs1 retention guarantee;

 

Tablespace altered.

修改为确保UNDO表空间,这样UNDO表空间的历史数据不允许覆盖