作者:dbtan |【转载时请以超链接形式标明文章出处作者信息】


AUM下如何重建UNDO表空间:

在迁移(同平台)的时候由于UNDO表空间过大,不打算要现在的UNDO文件,想要重建一个,该如何做,是否需要通过一些隐含参数来做特殊处理?前提是拥有一个有效的冷备份(或者Clean Shutdown的数据库)。拥有冷备份,那么这个操作是很简单的,并不需要使用隐含参数。

以下是一个简单的测试过程,重建UNDO表空间的步骤和此类似。
⑴ 假定拥有一个Clean shutdown的数据库(以shutdown immediate方式关闭并执行备份):

sys@TQGZS11G> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

⑵ 同平台迁移时可以放弃UNDO表空间,这时候启动会报错:ORA-01157

[oracle@rhel52: ~/oradata/tqgzs11g]$mv undotbs01.dbf undotbs01.dbf.bk
idle> startup
ORACLE instance started.
Total System Global Area  318046208 bytes
Fixed Size                  1313120 bytes
Variable Size             260048544 bytes
Database Buffers           50331648 bytes
Redo Buffers                6352896 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/oracle/oradata/tqgzs11g/undotbs01.dbf'

⑶ 删除UNDO文件启动数据库:

idle> alter database datafile '/oracle/oradata/tqgzs11g/undotbs01.dbf' offline drop;
Database altered.
idle> alter database open;
Database altered.
idle> conn / as sysdba
Connected.
sys@TQGZS11G> select name from v$datafile;
NAME
--------------------------------------------------
/oracle/oradata/tqgzs11g/system01.dbf
/oracle/oradata/tqgzs11g/sysaux01.dbf
/oracle/oradata/tqgzs11g/undotbs01.dbf
/oracle/oradata/tqgzs11g/users01.dbf
/oracle/oradata/tqgzs11g/example01.dbf
/oracle/oradata/tqgzs11g/TQ.dbf
6 rows selected.
sys@TQGZS11G> create undo tablespace undotbs2 datafile '/oracle/oradata/tqgzs11g/undotbs02.dbf' size 200m autoextend on next 2m;
Tablespace created.
sys@TQGZS11G> alter system set undo_tablespace = undotbs2 ;
System altered.

然后数据库即可恢复正常使用:

sys@TQGZS11G> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@TQGZS11G> conn / as sysdba
Connected to an idle instance.
idle> startup
ORACLE instance started.
Total System Global Area  318046208 bytes
Fixed Size                  1313120 bytes
Variable Size             260048544 bytes
Database Buffers           50331648 bytes
Redo Buffers                6352896 bytes
Database mounted.
Database opened.
idle> conn / as sysdba
Connected.
sys@TQGZS11G> select name,ceil(bytes/1024/1024) MB,status from v$datafile;
NAME                                                       MB STATUS
-------------------------------------------------- ---------- ----------
/oracle/oradata/tqgzs11g/system01.dbf                     740 SYSTEM
/oracle/oradata/tqgzs11g/sysaux01.dbf                     670 ONLINE
/oracle/oradata/tqgzs11g/undotbs01.dbf                      0 OFFLINE
/oracle/oradata/tqgzs11g/users01.dbf                       13 ONLINE
/oracle/oradata/tqgzs11g/example01.dbf                    100 ONLINE
/oracle/oradata/tqgzs11g/TQ.dbf                           100 ONLINE
/oracle/oradata/tqgzs11g/undotbs02.dbf                    200 ONLINE
7 rows selected.
sys@TQGZS11G> drop tablespace undotbs1 including contents and datafiles;
Tablespace dropped.
sys@TQGZS11G> select name,ceil(bytes/1024/1024) MB,status from v$datafile;
NAME                                                       MB STATUS
-------------------------------------------------- ---------- ----------
/oracle/oradata/tqgzs11g/system01.dbf                     740 SYSTEM
/oracle/oradata/tqgzs11g/sysaux01.dbf                     670 ONLINE
/oracle/oradata/tqgzs11g/users01.dbf                       13 ONLINE
/oracle/oradata/tqgzs11g/example01.dbf                    100 ONLINE
/oracle/oradata/tqgzs11g/TQ.dbf                           100 ONLINE
/oracle/oradata/tqgzs11g/undotbs02.dbf                    200 ONLINE
6 rows selected.

- The End -