切换UNDO的一些注意事项:
解决步骤:
1. 启动SQLPLUS,并用sys登陆到数据库。
3. 确认UNDO表空间;
5. 查看回滚段的使用情况,哪个用户正在使用回滚段的资源,如果有用户最好更换时间(特别是生产环境)。
SQL> desc v$datafile
SQL> select file#,ts#,name from v$datafile;
7. 创建新的UNDO表空间,并设置自动扩展参数;
9. 等待原UNDO表空间所有UNDO SEGMENT OFFLINE;
11. 删除原有的UNDO表空间;
12. 确认删除是否成功;
[root@dbdev root]# su - oracle
[oracle@dbdev oracle]$ cd $ORACLE_HOME/dbs/
[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'
*.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;
SQL> select name from v$tablespace;
NAME
----------------------------------------
SYSTEM
UNDOTBS1
TEMP
----------------------------------------
SYSTEM
UNDOTBS1
TEMP
HYC
HYCOMMONDATA
HYSADATA
HYFFDATA
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';
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
---------------------------------------- ---------------
/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;
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;
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
---------- ---------- --------------------- ---------------------- ----------
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)
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.
---------- ---------- ----------------------------------------
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
----------------------------------------
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'
*.undo_management='AUTO'
*.undo_retention=10800
*.undo_tablespace='UNDOTBS2'
14. 册除原UNDO表空间的数据文件,其文件名为步骤中执行的结果。
[oracle@dbdev dbs]$ rm /rdb/oradata/ora9/undotbs01.dbf
[oracle@dbdev dbs]$
[oracle@dbdev dbs]$
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/756652/viewspace-242494/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/756652/viewspace-242494/