场景描述:数据库处于CLOSE状态,但有>=2个undo表空间 高可用方式
与场景8的区别:直接脱机开库,而没有在开库之前将表空间切换到一个可用的undo表空间,这样当前无默认的undo表空间。
-- 0.1 一致关库
sys@TESTDB11>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
--0.2 删除undo表空间数据文件
sys@TESTDB11>!rm /u01/app/oracle/oradata/TestDB11/undotbs01.dbf
--尝试启库
sys@TESTDB11>startup
ORACLE instance started.
Total System Global Area 855982080 bytes
Fixed Size 2230792 bytes
Variable Size 641730040 bytes
Database Buffers 209715200 bytes
Redo Buffers 2306048 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/u01/app/oracle/oradata/TestDB11/undotbs01.dbf'
--脱机
sys@TESTDB11>alter database datafile 3 offline;
Database altered.
--启库
sys@TESTDB11>alter database open;
Database altered.
--但此时会有问题(正常的查询操作没问题,但更新数据就会有问题)
scott@TESTDB11>conn scott/scott
Connected.
scott@TESTDB11>select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
scott@TESTDB11>update dept set dname = 'DNameD' where deptno = 40;
update dept set dname = 'DNameD' where deptno = 40
*
ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS'
--但在系统表空间下没问题
scott@TESTDB11>conn / as sysdba
Connected.
sys@TESTDB11>create table test_users as select * from dba_users;
Table created.
sys@TESTDB11>delete from test_users;
39 rows deleted.
--切换一下undo表空间
sys@TESTDB11>alter system set undo_tablespace=newundotbs;
System altered.
--此时就可以操作了
scott@TESTDB11>delete from dept where deptno = 40;
1 row deleted.
--还原,恢复
sys@TESTDB11>!cp /backup/inconsistent_backup/undotbs01.dbf /u01/app/oracle/oradata/TestDB11
sys@TESTDB11>recover datafile 3;
ORA-00279: change 2654893 generated at 08/09/2013 21:27:06 needed for thread 1
ORA-00289: suggestion : /archive2/1_98_813665348.dbf
ORA-00280: change 2654893 for thread 1 is in sequence #98
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 2660981 generated at 08/09/2013 22:19:48 needed for thread 1
ORA-00289: suggestion : /archive2/1_99_813665348.dbf
ORA-00280: change 2660981 for thread 1 is in sequence #99
ORA-00279: change 2667783 generated at 08/10/2013 00:00:55 needed for thread 1
ORA-00289: suggestion : /archive2/1_100_813665348.dbf
ORA-00280: change 2667783 for thread 1 is in sequence #100
ORA-00279: change 2679804 generated at 08/10/2013 03:00:28 needed for thread 1
ORA-00289: suggestion : /archive2/1_101_813665348.dbf
ORA-00280: change 2679804 for thread 1 is in sequence #101
ORA-00279: change 2699110 generated at 08/10/2013 08:29:58 needed for thread 1
ORA-00289: suggestion : /archive2/1_102_813665348.dbf
ORA-00280: change 2699110 for thread 1 is in sequence #102
ORA-00279: change 2725650 generated at 08/10/2013 10:27:18 needed for thread 1
ORA-00289: suggestion : /archive2/1_103_813665348.dbf
ORA-00280: change 2725650 for thread 1 is in sequence #103
ORA-00279: change 2726122 generated at 08/10/2013 10:29:03 needed for thread 1
ORA-00289: suggestion : /archive2/1_104_813665348.dbf
ORA-00280: change 2726122 for thread 1 is in sequence #104
ORA-00279: change 2726220 generated at 08/10/2013 10:32:28 needed for thread 1
ORA-00289: suggestion : /archive2/1_105_813665348.dbf
ORA-00280: change 2726220 for thread 1 is in sequence #105
ORA-00279: change 2726249 generated at 08/10/2013 10:33:07 needed for thread 1
ORA-00289: suggestion : /archive2/1_106_813665348.dbf
ORA-00280: change 2726249 for thread 1 is in sequence #106
Log applied.
Media recovery complete.
--联机
sys@TESTDB11>alter database datafile 3 online;
Database altered.
--切回来
sys@TESTDB11>alter system set undo_tablespace = undotbs1;
System altered.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17013648/viewspace-1153191/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/17013648/viewspace-1153191/