切换UNDO(zt)

切换UNDO的一些注意事项：

[root@dbdev root]# su - oracle
[oracle@dbdev oracle]$ cd $ORACLE_HOME/dbs/

2. 查找数据库的UNDO表空间名

[oracle@dbdev dbs]$ strings spfileora9.ora | grep undo
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'

解决步骤：
1. 启动SQLPLUS，并用sys登陆到数据库。

[oracle@dbdev dbs]$ sqlplus "/ as sysdba"

3. 确认UNDO表空间；

SQL> col name format a40
SQL> select name from v$tablespace;

NAME
----------------------------------------
SYSTEM
UNDOTBS1
TEMP

HYC
HYCOMMONDATA
HYSADATA
HYFFDATA

33 rows selected.

4. 检查数据库UNDO表空间占用空间情况以及数据文件存放位置；

SQL> col FILE_NAME format a40
SQL> select file_name,bytes/1024/1024 from dba_data_files where tablespace_name like 'UNDOTBS1';

FILE_NAME BYTES/1024/1024
---------------------------------------- ---------------
/trsgfifs/oradata/UNDOTBS1.dbf 10000

5. 查看回滚段的使用情况，哪个用户正在使用回滚段的资源，如果有用户最好更换时间（特别是生产环境）。

SQL> select s.username, u.name from v$transaction t,v$rollstat r,
2 v$rollname u,v$session s where s.taddr=t.addr and
3 t.xidusn=r.usn and r.usn=u.usn order by s.username;

no rows selected

6. 检查UNDO Segment状态；

SQL> select usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks
2 from v$rollstat order by rssize;

USN XACTS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024 SHRINKS
---------- ---------- --------------------- ---------------------- ----------
0 0 .000358582 .000358582 0
1 0 .006950378 .026481628 78
3 0 .006950378 .051872253 48
5 0 .006950378 .078239441 58
6 0 .006950378 .030448914 52
9 0 .006950378 .070426941 59
10 0 .006950378 .022575378 63
8 0 .006950378 .059684753 51
2 0 .014762878 .021598816 57
4 0 .014762878 .022575378 67
7 0 .022575378 .022575378 59

11 rows selected.

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

SQL> desc v$datafile

SQL> select file#,ts#,name from v$datafile;

FILE# TS# NAME
---------- ---------- ----------------------------------------
1 0 /rdb/oradata/ora9/system01.dbf
2 1 /rdb/oradata/ora9/undotbs01.dbf
3 4 /trsg/oradata/tr_data01.dbf
4 5 /trsg/oradata/tr_index01.dbf
5 17 /trsg/oradata/hycdhindex_data01.dbf
6 7 /trsg/oradata/tr_roll.dbf
7 8 /trsg/oradata/hyc_data01.dbf
36 rows selected.

7. 创建新的UNDO表空间，并设置自动扩展参数；

SQL> create undo tablespace undotbs2 datafile '/trsgfifs/oradata/UNDOTBS2.dbf' size 1000m reuse autoextend on next 100m maxsize unlimited;

Tablespace created.

8. 动态更改spfile配置文件；

SQL> alter system set undo_tablespace=undotbs2 scope=both;

System altered.

9. 等待原UNDO表空间所有UNDO SEGMENT OFFLINE；

SQL> select usn,xacts,status,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks from v$rollstat order by rssize;

11 rows selected.

10. 再执行看UNDO表空间所有UNDO SEGMENT ONLINE；

SQL> /

11 rows selected.

SQL> shutdown immediate;

SQL> startup

11. 删除原有的UNDO表空间；

SQL> drop tablespace undotbs1 including contents;

Tablespace dropped.

12. 确认删除是否成功；

SQL> select name from v$tablespace;

NAME
----------------------------------------
JIRA_DATA
PERFSTAT
COGNOS_DATA
COGNOS_INDEX
TP_ITTEST_DATA
TP_ITTEST_INDEX
CUSTDEV_DATA
CUSTDEV_INDEX
UNDOTBS2

33 rows selected.

SQL> exit

13. 在做此步骤前，请到$ORACLE_HOME/dbs/init$ORACLE_SID.ora如下内容是否发生变更：

[oracle@dbdev dbs]$ strings spfileora9.ora | grep undo
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS2'

14. 册除原UNDO表空间的数据文件，其文件名为步骤中执行的结果。

[oracle@dbdev dbs]$ rm /rdb/oradata/ora9/undotbs01.dbf
[oracle@dbdev dbs]$

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/756652/viewspace-242494/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/756652/viewspace-242494/