2007年7月 – 第 3 页

未分类

undo表空间丢失之恢复

2007-07-10 一条评论

sys@ORALOCAL(10.1.19.11)> insert into hejianmin.kk values(222333);

已创建 1 行。

已用时间:  00: 00: 00.05
sys@ORALOCAL(10.1.19.11)> shutdown abort;
ORACLE 例程已经关闭。
sys@ORALOCAL(10.1.19.11)> exit;
从Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production中断开

1

2

3

4

5

6

7

8

9

10

11

sys@ORALOCAL(10.1.19.11)> insert into hejianmin.kk values(222333);

已创建 1 行。

已用时间: 00: 00: 00.05

sys@ORALOCAL(10.1.19.11)> shutdown abort;

ORACLE 例程已经关闭。

sys@ORALOCAL(10.1.19.11)> exit;

从Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.1.0 - Production中断开

C:\Documents and Settings\Administrator>d:

D:\>cd D:\oracle\oradata\oralocal

D:\oracle\oradata\oralocal>ls
CONTROL01.CTL       DRSYS01.DBF         REDO0201.LOG        REDO101.LOG         REDO301.LOG         TS_DATA_USER01.DBF  TS_UNDO_02.DBF
CONTROL02.CTL       EXAMPLE01.DBF       REDO0202.LOG        REDO102.LOG         REDO302.LOG         TS_INDX_01.DBF      XDB01.DBF
CONTROL03.CTL       ODM01.DBF           REDO0301.LOG        REDO201.LOG         SYSTEM01.DBF        TS_RMAN_01.DBF
CWMLITE01.DBF       PERFSTAT.DBF        REDO0302.LOG        REDO202.LOG         TEMP.DBF            TS_UNDO_01.DBF

D:\oracle\oradata\oralocal>rm TS_UNDO_01.DBF

D:\oracle\oradata\oralocal>set nls_lang=american

D:\oracle\oradata\oralocal>sqlplus "/ as sysdba"

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

C:\Documents and Settings\Administrator>d:

D:\>cd D:\oracle\oradata\oralocal

D:\oracle\oradata\oralocal>ls

CONTROL01.CTL DRSYS01.DBF REDO0201.LOG REDO101.LOG REDO301.LOG TS_DATA_USER01.DBF TS_UNDO_02.DBF

CONTROL02.CTL EXAMPLE01.DBF REDO0202.LOG REDO102.LOG REDO302.LOG TS_INDX_01.DBF XDB01.DBF

CONTROL03.CTL ODM01.DBF REDO0301.LOG REDO201.LOG SYSTEM01.DBF TS_RMAN_01.DBF

CWMLITE01.DBF PERFSTAT.DBF REDO0302.LOG REDO202.LOG TEMP.DBF TS_UNDO_01.DBF

D:\oracle\oradata\oralocal>rm TS_UNDO_01.DBF

D:\oracle\oradata\oralocal>set nls_lang=american

D:\oracle\oradata\oralocal>sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Jul 5 13:48:49 2007

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to an idle instance.


> startup
ORACLE instance started.

Total System Global Area  133765984 bytes
Fixed Size                   453472 bytes
Variable Size              79691776 bytes
Database Buffers           50331648 bytes
Redo Buffers                3289088 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: 'D:\ORACLE\ORADATA\ORALOCAL\TS_UNDO_01.DBF'


> alter database datafile 'D:\ORACLE\ORADATA\ORALOCAL\TS_UNDO_01.DBF' offline drop;

Database altered.

Elapsed: 00:00:00.02
> alter database open;

Database altered.

Elapsed: 00:00:18.00
> create undo tablespace UNDOTBS03 datafile 'D:\ORACLE\ORADATA\ORALOCAL\TS_UNDO_03.dbf' size 100M;

Tablespace created.

Elapsed: 00:00:09.08
> alter system set undo_tablespace=UNDOTBS03 scope=spfile;

System altered.

Elapsed: 00:00:00.01
> shutdown immediate;
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: 'D:\ORACLE\ORADATA\ORALOCAL\TS_UNDO_01.DBF'


> select status from v$instance;
select status from v$instance
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [1236], [], [], [], [], [], [], []


Elapsed: 00:00:02.00
> shutdown immediate;
ORA-00600: internal error code, arguments: [1236], [], [], [], [], [], [], []
> startup;
ORA-01081: cannot start already-running ORACLE - shut it down first
> shutdown abort;
ORACLE instance shut down.
> startup
ORACLE instance started.

Total System Global Area  133765984 bytes
Fixed Size                   453472 bytes
Variable Size              79691776 bytes
Database Buffers           50331648 bytes
Redo Buffers                3289088 bytes
Database mounted.
Database opened.
> select SEGMENT_NAME,STATUS from dba_rollback_segs;

SEGMENT_NAME                   STATUS
------------------------------ ----------------
SYSTEM                         ONLINE
_SYSSMU1$                      OFFLINE
_SYSSMU2$                      NEEDS RECOVERY
_SYSSMU3$                      NEEDS RECOVERY
_SYSSMU4$                      NEEDS RECOVERY
_SYSSMU5$                      NEEDS RECOVERY
_SYSSMU6$                      NEEDS RECOVERY
_SYSSMU7$                      NEEDS RECOVERY
_SYSSMU8$                      NEEDS RECOVERY
_SYSSMU9$                      NEEDS RECOVERY
_SYSSMU10$                     NEEDS RECOVERY
_SYSSMU11$                     OFFLINE
_SYSSMU12$                     OFFLINE
_SYSSMU13$                     OFFLINE
_SYSSMU14$                     OFFLINE
_SYSSMU15$                     OFFLINE
_SYSSMU16$                     OFFLINE
_SYSSMU17$                     OFFLINE
_SYSSMU18$                     OFFLINE
_SYSSMU19$                     OFFLINE
_SYSSMU20$                     NEEDS RECOVERY
_SYSSMU21$                     ONLINE
_SYSSMU22$                     ONLINE
_SYSSMU23$                     ONLINE
_SYSSMU24$                     ONLINE
_SYSSMU25$                     ONLINE
_SYSSMU26$                     ONLINE
_SYSSMU27$                     ONLINE
_SYSSMU28$                     ONLINE
_SYSSMU29$                     ONLINE
_SYSSMU30$                     ONLINE

31 rows selected.

Elapsed: 00:00:02.00
> drop tablespace TS_UNDO_01 including contents and datafiles;
drop tablespace TS_UNDO_01 including contents and datafiles
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU2$' found, terminate dropping tablespace


Elapsed: 00:00:02.01
> shutdown immediate;
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: 'D:\ORACLE\ORADATA\ORALOCAL\TS_UNDO_01.DBF'


> shutdown abort;
ORA-00600: internal error code, arguments: [1236], [], [], [], [], [], [], []
>exit

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Jul 5 13:48:49 2007

Connected to an idle instance.

> startup

ORACLE instance started.

Total System Global Area 133765984 bytes

Fixed Size 453472 bytes

Variable Size 79691776 bytes

Database Buffers 50331648 bytes

Redo Buffers 3289088 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 2 - see DBWR trace file

ORA-01110: data file 2: 'D:\ORACLE\ORADATA\ORALOCAL\TS_UNDO_01.DBF'

> alter database datafile 'D:\ORACLE\ORADATA\ORALOCAL\TS_UNDO_01.DBF' offline drop;

Database altered.

Elapsed: 00:00:00.02

> alter database open;

Database altered.

Elapsed: 00:00:18.00

> create undo tablespace UNDOTBS03 datafile 'D:\ORACLE\ORADATA\ORALOCAL\TS_UNDO_03.dbf' size 100M;

Tablespace created.

Elapsed: 00:00:09.08

> alter system set undo_tablespace=UNDOTBS03 scope=spfile;

System altered.

Elapsed: 00:00:00.01

> shutdown immediate;

ORA-00376: file 2 cannot be read at this time

ORA-01110: data file 2: 'D:\ORACLE\ORADATA\ORALOCAL\TS_UNDO_01.DBF'

> select status from v$instance;

select status from v$instance

*

ERROR at line 1:

ORA-00600: internal error code, arguments: [1236], [], [], [], [], [], [], []

Elapsed: 00:00:02.00

> shutdown immediate;

ORA-00600: internal error code, arguments: [1236], [], [], [], [], [], [], []

> startup;

ORA-01081: cannot start already-running ORACLE - shut it down first

> shutdown abort;

ORACLE instance shut down.

> startup

ORACLE instance started.

Total System Global Area 133765984 bytes

Fixed Size 453472 bytes

Variable Size 79691776 bytes

Database Buffers 50331648 bytes

Redo Buffers 3289088 bytes

Database mounted.

Database opened.

> select SEGMENT_NAME,STATUS from dba_rollback_segs;

SEGMENT_NAME STATUS

------------------------------ ----------------

SYSTEM ONLINE

_SYSSMU1$ OFFLINE

_SYSSMU2$ NEEDS RECOVERY

_SYSSMU3$ NEEDS RECOVERY

_SYSSMU4$ NEEDS RECOVERY

_SYSSMU5$ NEEDS RECOVERY

_SYSSMU6$ NEEDS RECOVERY

_SYSSMU7$ NEEDS RECOVERY

_SYSSMU8$ NEEDS RECOVERY

_SYSSMU9$ NEEDS RECOVERY

_SYSSMU10$ NEEDS RECOVERY

_SYSSMU11$ OFFLINE

_SYSSMU12$ OFFLINE

_SYSSMU13$ OFFLINE

_SYSSMU14$ OFFLINE

_SYSSMU15$ OFFLINE

_SYSSMU16$ OFFLINE

_SYSSMU17$ OFFLINE

_SYSSMU18$ OFFLINE

_SYSSMU19$ OFFLINE

_SYSSMU20$ NEEDS RECOVERY

_SYSSMU21$ ONLINE

_SYSSMU22$ ONLINE

_SYSSMU23$ ONLINE

_SYSSMU24$ ONLINE

_SYSSMU25$ ONLINE

_SYSSMU26$ ONLINE

_SYSSMU27$ ONLINE

_SYSSMU28$ ONLINE

_SYSSMU29$ ONLINE

_SYSSMU30$ ONLINE

31 rows selected.

Elapsed: 00:00:02.00

> drop tablespace TS_UNDO_01 including contents and datafiles;

drop tablespace TS_UNDO_01 including contents and datafiles

*

ERROR at line 1:

ORA-01548: active rollback segment '_SYSSMU2$' found, terminate dropping tablespace

Elapsed: 00:00:02.01

> shutdown immediate;

ORA-00376: file 2 cannot be read at this time

ORA-01110: data file 2: 'D:\ORACLE\ORADATA\ORALOCAL\TS_UNDO_01.DBF'

> shutdown abort;

ORA-00600: internal error code, arguments: [1236], [], [], [], [], [], [], []

>exit

vi INIToralocal.ORA *.aq_tm_processes=1 *.backgroun […]

Study note

DBMS_STATS包的一些研究

2007-07-10 没有评论

–检查对表的上次分析时间，可以看到返回空值，即一直都未分析过。 hejianmin@ORALOCAL(10.203.1.23)> select to_char(LAST_ANALYZED,’yyyymmdd hh24:mi:ss’) from user_table […]

..troubleshooting, Working case

job 不能正常执行

2007-07-10 没有评论

今天在准备做一个job，用于分析undo增长过快的原因，检查v$transaction中有什么session运行，session对应的sql_text是什么。写完存储过程后，放到job中，发现job没有运行。于是进行一下检查分析： 1、检查alertlog中没有发现异常 2、[oracle@sit […]

Study note

031 study note-关于控制文件、redolog、archivelog的几个主要视图

2007-07-10 没有评论

control_file： v$controlfile V$CONTROLFILE_RECORD_SECTION V$ARCHIVED_LOG V$BACKUP Backup V$BACKUP_DATAFILE V$BACKUP_PIECE V$BACKUP_REDOLOG V$BACKUP_SET […]

It's my life

爱情的终点不是分手，而是绝望

2007-07-10 5条评论

有人说，小荷，不要光是写技术的文章了，咱们插不上嘴。那好吧，就来写写生活，谈谈感悟…… 很久没有那种淡淡恬美的爱情的感觉了，或许，只有在梦中才有了。离开爱情久了，也就不相信爱情了，不相信浪漫，不相信巧克力，不相信烟花，不相信拥抱，不相信承诺。剩下的，只有痞子、戏谑和虚伪的微笑。听着jk、db […]

..experience, Working case

在rac 模式下维护的一些注意点

2007-07-10 没有评论

1、通过gv$session查询到的sid，serial#，然后进行alter system kill sesson ‘sid,serial#’，如果不是登陆在那个实例的机器上，会报session 不存在。必须登陆到那个session所在的实例上，在那个实例上进行kill […]

Study note

031 study note-startup instance

2007-07-10 没有评论

startup nomount后可以用的几个常用视图： v$parameter v$session v$process v$option v$instance v$sga v$version startup mount后可以用的几个常用视图：（在nomount状态可以desc，但是不能select） […]

..db tuning

oracle 优化－解读statspack

2007-07-10 一条评论

今天看了《statspack_tuning_otn_new.pdf》，主要学习到了一些top event的产生原因和解决办法。 The Load Profile：一般用户数据库主机的负载量 Instance Efficiency 不同的应用可以接受不同的实例状态，如在DSS环境中，In-memor […]

Study note

rman 学习笔记－rman qucik review

2007-07-10 没有评论

今天粗看了一遍《Recovery Manager Quick Reference》，又了解一些以前不知道的rman（Recovery Manager）的特性和一些还原的步骤。 1、rman也可有日志：rman nocatalog target /log=’rman.log’ […]

..oracle service, Working case

oracle三机rac模式，rac02宕机故障报告

2007-07-10 没有评论

故障报告 1背景：某省的某一数据库主机目前的工作模式为3机rac模式，在该模式下，当一个节点发生故障时，相应的数据库连接能自动切换到其他节点，从而不影响应用的正常工作。 2故障过程： 2007年1月8日下午18：00左右，主机工程师利用siteview进行系统检查时发现rac02主机告警。通过te […]

..troubleshooting, Working case

用errorstack解决exp导出时使用direct=y参数报错问题

2007-07-10 没有评论

一、问题提出：在一台数据库主机上做exp，一样的语句，用exp导出，加上direct=y参数就报错： oracle@gd_rdb01:/rpt_bak$exp report/pwd@gdreport tables=hjm_jfbb_close direct=y log=111.log Export […]

Study note

031 study note-oracle的后台进程

2007-07-10 没有评论

oracle的后台进程：（1） db writer（DBWn）：将dirty buffer写入数据文件中。启动的时候只有一个DBWn，即DBW0，最多可以有10个（DBW0～DBW9）。参数DB_WRITER_PROCESS可以设置启动的时候DBWn的数量。发生条件：(a)clean buffer […]