数据库无事务情况下丢失undo表空间数据文件
1. 查看当前undo表空间,并删除物理undo文件
SYS@userdata>show parameter undo_tablespace; NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ undo_tablespace string UNDOTBS1 SYS@userdata>select file_name,tablespace_name,status from dba_data_files where tablespace_name='UNDOTBS1'; FILE_NAME TABLESPACE_NAME STATUS ------------------------------------------------------------ ------------------------------------------------------------------------------------------ --------------------------- /u01/app/oracle/oradata/userdata/undotbs01.dbf UNDOTBS1 AVAILABLE SYS@userdata>host rm -rf /u01/app/oracle/oradata/userdata/undotbs01.dbf SYS@userdata>shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down.
2.将数据库正常启动,可以看到缺少undo表空间数据文件的报错
SYS@userdata>startup; ORACLE instance started. Total System Global Area 314572800 bytes Fixed Size 2083592 bytes Variable Size 255853816 bytes Database Buffers 50331648 bytes Redo Buffers 6303744 bytes Database mounted. ORA-01157: cannot identify/lock data file 2 - see DBWR trace file ORA-01110: data file 2: '/u01/app/oracle/oradata/userdata/undotbs01.dbf' SYS@userdata>show parameter back; NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ background_core_dump string partial background_dump_dest string /u01/app/oracle/admin/userdata /bdump backup_tape_io_slaves boolean FALSE db_flashback_retention_target integer 1440 fast_start_parallel_rollback string LOW rollback_segments string transactions_per_rollback_segment integer 5 SYS@userdata>host tail -n 10 /u01/app/oracle/admin/userdata/bdump/alert_userdata.log Mon Oct 2 10:53:58 2017 ALTER DATABASE OPEN Mon Oct 2 10:53:58 2017 Errors in file /u01/app/oracle/admin/userdata/bdump/userdata_dbw0_8121.trc: ORA-01157: cannot identify/lock data file 2 - see DBWR trace file ORA-01110: data file 2: '/u01/app/oracle/oradata/userdata/undotbs01.dbf' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 3 ORA-1157 signalled during: ALTER DATABASE OPEN...
3.因为undo表空间此时不包含数据,drop undo表空间
SYS@userdata>alter database datafile 2 offline drop; Database altered.
4. 将undo管理改为manual
SYS@userdata>alter system set undo_management='MANUAL' scope=spfile; System altered.
5. 重启数据库
SYS@userdata>shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SYS@userdata>startup; ORACLE instance started. Total System Global Area 314572800 bytes Fixed Size 2083592 bytes Variable Size 255853816 bytes Database Buffers 50331648 bytes Redo Buffers 6303744 bytes Database mounted. Database opened. SYS@userdata>show parameter undo; NAME TYPE VALUE ------------------------------------ --------------------------------- ------------------------------ undo_management string MANUAL undo_retention integer 900 undo_tablespace string UNDOTBS1
6. 将丢失文件的undo表空间drop后重建
SYS@userdata>drop tablespace UNDOTBS1; Tablespace dropped. SYS@userdata>create undo tablespace undotbs1 datafile '/u01/app/oracle/oradata/userdata/undotbs01.dbf' size 100m autoextend on; Tablespace created.
7. 查看目前的数据库undo设置,需要将undo_management 改为auto
SYS@userdata>alter system set undo_management='AUTO' scope=spfile; System altered. SYS@userdata>shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SYS@userdata>startup; ORACLE instance started. Total System Global Area 314572800 bytes Fixed Size 2083592 bytes Variable Size 255853816 bytes Database Buffers 50331648 bytes Redo Buffers 6303744 bytes Database mounted. Database opened.
SYS@userdata>select name,status,enabled from v$datafile; NAME STATUS ENABLED -------------------------------------------------- --------------------- ------------------------------ /u01/app/oracle/oradata/userdata/system01.dbf SYSTEM READ WRITE /u01/app/oracle/oradata/userdata/undotbs01.dbf ONLINE READ WRITE /u01/app/oracle/oradata/userdata/sysaux01.dbf ONLINE READ WRITE /u01/app/oracle/oradata/userdata/users01.dbf ONLINE READ WRITE /u01/app/oracle/oradata/userdata/example01.dbf ONLINE READ WRITE
数据库有事务情况下丢失undo表空间数据文件
1. undo表空间数据文件丢失后启动数据库
SYS@userdata>startup ORACLE instance started. Total System Global Area 314572800 bytes Fixed Size 2083592 bytes Variable Size 255853816 bytes Database Buffers 50331648 bytes Redo Buffers 6303744 bytes Database mounted. ORA-01157: cannot identify/lock data file 2 - see DBWR trace file ORA-01110: data file 2: '/u01/app/oracle/oradata/userdata/undotbs01.dbf' SYS@userdata>alter database datafile 2 offline drop; Database altered. SYS@userdata>alter system set undo_management='MANUAL' scope=spfile; System altered. SYS@userdata>shutdown immediate; ORA-01109: database not open Database dismounted. ORACLE instance shut down. SYS@userdata>startup; ORACLE instance started. Total System Global Area 314572800 bytes Fixed Size 2083592 bytes Variable Size 255853816 bytes Database Buffers 50331648 bytes Redo Buffers 6303744 bytes Database mounted. ORA-01092: ORACLE instance terminated. Disconnection forced
2. 查看跟踪日志的详细报错
SYS@userdata>host tail -n 10 /u01/app/oracle/admin/userdata/bdump/alert_userdata.log Database Characterset is AL32UTF8 Mon Oct 2 11:20:10 2017 Errors in file /u01/app/oracle/admin/userdata/udump/userdata_ora_8444.trc: ORA-00604: error occurred at recursive SQL level 1 ORA-00376: file 2 cannot be read at this time ORA-01110: data file 2: '/u01/app/oracle/oradata/userdata/undotbs01.dbf' Error 604 happened during db open, shutting down database USER: terminating instance due to error 604 Instance terminated by USER, pid = 8444 ORA-1092 signalled during: ALTER DATABASE OPEN... SYS@userdata>host tail -n 10 /u01/app/oracle/admin/userdata/udump/userdata_ora_8444.trc Longest hash chain = 1 Average hash chain = 20/20 = 1.0 Max compares per lookup = 1 Avg compares per lookup = 95/195 = 0.5 ---------------------------------------------- tkcrrsarc: (WARN) Failed to find ARCH for message (message:0x1) tkcrrpa: (WARN) Failed initial attempt to send ARCH message (message:0x1) ORA-00604: error occurred at recursive SQL level 1 ORA-00376: file 2 cannot be read at this time ORA-01110: data file 2: '/u01/app/oracle/oradata/userdata/undotbs01.dbf'
3. 加undo隐藏参数重新启动数据库
SYS@userdata>select ksppinm from x$ksppi where ksppinm like '%roll%'; KSPPINM -------------------------------------------------- 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 _mv_rolling_inv 10 rows selected. SYS@userdata>alter system set "_offline_rollback_segments"=true scope=spfile; System altered. SYS@userdata>alter system set "_corrupted_rollback_segments"='_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$','_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$','_SYSSMU9$','_SYSSMU10$' scope=spfile; System altered. SYS@userdata>shutdown abort; ORACLE instance shut down. SYS@userdata>startup; ORACLE instance started. Total System Global Area 314572800 bytes Fixed Size 2083592 bytes Variable Size 255853816 bytes Database Buffers 50331648 bytes Redo Buffers 6303744 bytes Database mounted. Database opened.
4.将丢失文件的undo表空间drop后重建
SYS@userdata>drop tablespace UNDOTBS1; Tablespace dropped. SYS@userdata>create undo tablespace undotbs1 datafile '/u01/app/oracle/oradata/userdata/undotbs01.dbf' size 100m autoextend on; Tablespace created.
5.将undo管理方式修改为auto
SYS@userdata>alter system set undo_management='AUTO' scope=spfile; System altered.
6.将隐藏参数恢复为默认值
SYS@userdata>alter system reset "_offline_rollback_segments" scope=spfile sid='*'; System altered. SYS@userdata>alter system reset "_corrupted_rollback_segments" scope=spfile sid='*'; System altered.
7.重启数据库
SYS@userdata>shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SYS@userdata>startup; ORACLE instance started. Total System Global Area 314572800 bytes Fixed Size 2083592 bytes Variable Size 255853816 bytes Database Buffers 50331648 bytes Redo Buffers 6303744 bytes Database mounted. Database opened. SYS@userdata>select name,status,enabled from v$datafile; NAME STATUS ENABLED -------------------------------------------------- --------------------- ------------------------------ /u01/app/oracle/oradata/userdata/system01.dbf SYSTEM READ WRITE /u01/app/oracle/oradata/userdata/undotbs01.dbf ONLINE READ WRITE /u01/app/oracle/oradata/userdata/sysaux01.dbf ONLINE READ WRITE /u01/app/oracle/oradata/userdata/users01.dbf ONLINE READ WRITE /u01/app/oracle/oradata/userdata/example01.dbf ONLINE READ WRITE