问题:测试那边的dba突然说一个库文件系统被undo撑爆了,想改下undo的属性,但是现在库启不来,让帮忙给启下。后面他自己改undo。hp-u的机器。
思路:mount,删或者挪一个控制文件来腾出点小空间,open,再重建undo。
$ bdf
Filesystem kbytes used avail %used Mounted on
/dev/vg00/lvol3 2097152 225752 1856880 11% /
/dev/vg00/lvol1 1835008 175280 1646856 10% /stand
/dev/vg00/lvol8 7077888 1160056 5872280 16% /var
/dev/vg00/lvol7 6127616 3027552 3075912 50% /usr
/dev/vg00/lvol4 2097152 381752 1702296 18% /tmp
/dev/vg00/datalv 15728640 15728640 0 100% /oradata
/dev/vg00/oralv 15728640 15255367 443734 97% /oracle
/dev/vg00/lvol6 9207808 5183960 3992520 56% /opt
/dev/vg00/lvol5 114688 5432 108408 5% /home
/dev/vg00/entlv 15728640 10132682 5246327 66% /Entegor
/oradata下应该没有可删的,要不人自己dba也会删。
$ du -sk *
3072016 ENTEGOR_TSPACE.dbf
7376 control01.ctl
7225 control02.ctl
7120 control03.ctl
51201 redo01.log
51201 redo02.log
51201 redo03.log
450576 sysaux01.dbf
501776 system01.dbf
8056 temp01.dbf
11494416 undotbs01.dbf
5128 users01.dbf
启库报错
alter database open
Thu Jan 9 23:25:01 2014
Rolling back half complete log switch of thread 1
Thu Jan 9 23:25:01 2014
Errors in file /oracle/oracle/product/10.2.0/db_1/admin/entegor/bdump/entegor_lgwr_5841.trc:
ORA-19502: write error on file "/oradata/entegor/control02.ctl", blockno 448 (blocksize=16384)
ORA-27072: File I/O error
HPUX-ia64 Error: 11: Resource temporarily unavailable
Additional information: 4
Additional information: 448
Additional information: 56320
LGWR: terminating instance due to error 19502
Instance terminated by LGWR, pid = 5841
1.文件系统free为0字节,首先得把库启起来对不对,挪出一点小空间。
ALTER SYSTEM SET CONTROL_FILES ='/oradata/entegor/control01.ctl','/oradata/entegor/control03.ctl','/oracle/oracle/product/10.2.0/db_1/dbs/control04.ctl' SCOPE = SPFILE;
cp control03.ctl /oracle/oracle/product/10.2.0/db_1/dbs/control04.ctl
show parameter control_file;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /oradata/entegor/control01.ctl
, /oradata/entegor/control03.c
tl, /oracle/oracle/product/10.
2.0/db_1/dbs/control04.ctl
2.把02控制文件备份到oracle下。/oradata下空出了一个控制文件大小。
mv control02.ctl /oracle/oracle/product/10.2.0/db_1/dbs/control02.ctl
SQL> alter database open;
Database altered.
终于拉起来了。
3.检查UNDO Segment状态
SQL> select usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks from v$rollstat order by rssize;
USN XACTS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024 SHRINKS
---------- ---------- --------------------- ---------------------- ----------
0 0 .000419617 .000419617 0
14 0 .002067566 .002067566 0
3 0 .003044128 .003044128 0
18 0 .003044128 .003044128 0
1 0 .003044128 .003044128 0
17 0 .003044128 .003044128 0
16 0 .003044128 .003044128 0
13 0 .003044128 .003044128 0
12 0 .003044128 .003044128 0
11 0 .003044128 .003044128 0
6 0 .003044128 .003044128 0
10 0 .003105164 .003105164 0
9 0 .003105164 .003105164 0
15 0 .008903503 .008903503 0
7 0 .008903503 .008903503 0
4 0 .008903503 .008903503 0
2 0 .009880066 .009880066 0
8 0 .010856628 .010856628 0
5 0 .010856628 .010856628 0
19 rows selected.
4.创建一个临时中转用的UNDO表空间
create undo tablespace undotbs11 datafile '/oracle/oracle/product/10.2.0/db_1/dbs/undotbs11.dbf' size 100M reuse autoextend on next 80m maxsize unlimited;
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
alter system set undo_tablespace=UNDOTBS11 scope=both;
5.删除原有的UNDO表空间加and datafiles删除os的数据文件:
drop tablespace UNDOTBS1 including contents and datafiles;
!string spfileentegor.ora
*.undo_tablespace='UNDOTBS11'
os上空间腾出来
6.创建原来的undo表空间
create undo tablespace undotbs1 datafile '/oradata/entegor/undotbs1.dbf' size 8000M reuse autoextend on next 80m maxsize unlimited;
alter system set undo_tablespace=UNDOTBS1 scope=both;
show parameter undo;
drop tablespace UNDOTBS11 including contents and datafiles;
!string spfileentegor.ora
7.将控制文件改回来
ALTER SYSTEM SET CONTROL_FILES ='/oradata/entegor/control01.ctl','/oradata/entegor/control02.ctl','/oradata/entegor/control03.ctl' SCOPE = SPFILE;
shutdown immediate;
cp control03.ctl control02.ctl
show parameter control_file;
今天主要是在搭一个dataguard,主库是rac,备库是单节点asm。有意思吧,还是55公里同城。1T+,好慢啊,明天发步骤。
明天还有一个坏块要处理 。 发现很麻烦的处理过程往往忘记记录。只能记下简单的问题处理过程。总结能力还是不好 。
思路:mount,删或者挪一个控制文件来腾出点小空间,open,再重建undo。
$ bdf
Filesystem kbytes used avail %used Mounted on
/dev/vg00/lvol3 2097152 225752 1856880 11% /
/dev/vg00/lvol1 1835008 175280 1646856 10% /stand
/dev/vg00/lvol8 7077888 1160056 5872280 16% /var
/dev/vg00/lvol7 6127616 3027552 3075912 50% /usr
/dev/vg00/lvol4 2097152 381752 1702296 18% /tmp
/dev/vg00/datalv 15728640 15728640 0 100% /oradata
/dev/vg00/oralv 15728640 15255367 443734 97% /oracle
/dev/vg00/lvol6 9207808 5183960 3992520 56% /opt
/dev/vg00/lvol5 114688 5432 108408 5% /home
/dev/vg00/entlv 15728640 10132682 5246327 66% /Entegor
/oradata下应该没有可删的,要不人自己dba也会删。
$ du -sk *
3072016 ENTEGOR_TSPACE.dbf
7376 control01.ctl
7225 control02.ctl
7120 control03.ctl
51201 redo01.log
51201 redo02.log
51201 redo03.log
450576 sysaux01.dbf
501776 system01.dbf
8056 temp01.dbf
11494416 undotbs01.dbf
5128 users01.dbf
启库报错
alter database open
Thu Jan 9 23:25:01 2014
Rolling back half complete log switch of thread 1
Thu Jan 9 23:25:01 2014
Errors in file /oracle/oracle/product/10.2.0/db_1/admin/entegor/bdump/entegor_lgwr_5841.trc:
ORA-19502: write error on file "/oradata/entegor/control02.ctl", blockno 448 (blocksize=16384)
ORA-27072: File I/O error
HPUX-ia64 Error: 11: Resource temporarily unavailable
Additional information: 4
Additional information: 448
Additional information: 56320
LGWR: terminating instance due to error 19502
Instance terminated by LGWR, pid = 5841
1.文件系统free为0字节,首先得把库启起来对不对,挪出一点小空间。
ALTER SYSTEM SET CONTROL_FILES ='/oradata/entegor/control01.ctl','/oradata/entegor/control03.ctl','/oracle/oracle/product/10.2.0/db_1/dbs/control04.ctl' SCOPE = SPFILE;
cp control03.ctl /oracle/oracle/product/10.2.0/db_1/dbs/control04.ctl
show parameter control_file;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string /oradata/entegor/control01.ctl
, /oradata/entegor/control03.c
tl, /oracle/oracle/product/10.
2.0/db_1/dbs/control04.ctl
2.把02控制文件备份到oracle下。/oradata下空出了一个控制文件大小。
mv control02.ctl /oracle/oracle/product/10.2.0/db_1/dbs/control02.ctl
SQL> alter database open;
Database altered.
终于拉起来了。
3.检查UNDO Segment状态
SQL> select usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks from v$rollstat order by rssize;
USN XACTS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024 SHRINKS
---------- ---------- --------------------- ---------------------- ----------
0 0 .000419617 .000419617 0
14 0 .002067566 .002067566 0
3 0 .003044128 .003044128 0
18 0 .003044128 .003044128 0
1 0 .003044128 .003044128 0
17 0 .003044128 .003044128 0
16 0 .003044128 .003044128 0
13 0 .003044128 .003044128 0
12 0 .003044128 .003044128 0
11 0 .003044128 .003044128 0
6 0 .003044128 .003044128 0
10 0 .003105164 .003105164 0
9 0 .003105164 .003105164 0
15 0 .008903503 .008903503 0
7 0 .008903503 .008903503 0
4 0 .008903503 .008903503 0
2 0 .009880066 .009880066 0
8 0 .010856628 .010856628 0
5 0 .010856628 .010856628 0
19 rows selected.
4.创建一个临时中转用的UNDO表空间
create undo tablespace undotbs11 datafile '/oracle/oracle/product/10.2.0/db_1/dbs/undotbs11.dbf' size 100M reuse autoextend on next 80m maxsize unlimited;
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
alter system set undo_tablespace=UNDOTBS11 scope=both;
5.删除原有的UNDO表空间加and datafiles删除os的数据文件:
drop tablespace UNDOTBS1 including contents and datafiles;
!string spfileentegor.ora
*.undo_tablespace='UNDOTBS11'
os上空间腾出来
6.创建原来的undo表空间
create undo tablespace undotbs1 datafile '/oradata/entegor/undotbs1.dbf' size 8000M reuse autoextend on next 80m maxsize unlimited;
alter system set undo_tablespace=UNDOTBS1 scope=both;
show parameter undo;
drop tablespace UNDOTBS11 including contents and datafiles;
!string spfileentegor.ora
7.将控制文件改回来
ALTER SYSTEM SET CONTROL_FILES ='/oradata/entegor/control01.ctl','/oradata/entegor/control02.ctl','/oradata/entegor/control03.ctl' SCOPE = SPFILE;
shutdown immediate;
cp control03.ctl control02.ctl
show parameter control_file;
今天主要是在搭一个dataguard,主库是rac,备库是单节点asm。有意思吧,还是55公里同城。1T+,好慢啊,明天发步骤。
明天还有一个坏块要处理 。 发现很麻烦的处理过程往往忘记记录。只能记下简单的问题处理过程。总结能力还是不好 。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7590112/viewspace-1064772/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7590112/viewspace-1064772/