OracleBlog – 第 34 页 – 笑看数据库江湖的那些事儿……

rman备份发现坏块之后的处理

2009-04-20 8条评论

今天收到某省的备份发生失败的告警：

……
四月 16 16:30:19 ur_bak01: NetWorker savegroup: (alert) urmdborafull completed, total 2 client(s), 0 Hostname(s) Unresolved, 1 Failed, 1 Succeeded. (ur_mdb01 Failed)
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on t2 channel at 03/20/2009 13:49:09
ORA-19566: exceeded limit of 0 corrupt blocks for file /dev/vg_mdb02/rdata_2g_050
ORA-000060: Deadlock detected. More info in file /oracle/app/oracle/admin/uradt/udump/uradt_ora_3035.trc.
***
Corrupt block relative dba: 0x1a43d4e3 (file 105, block 251107)
Fractured block found during backing up datafile
Data in bad block -
 type: 0 format: 0 rdba: 0x00000000
 last change scn: 0x0000.00000000 seq: 0x0 flg: 0x00
 consistency value in tail: 0x00000000
 check value in block header: 0x0, block checksum disabled
 spare1: 0x0, spare2: 0x0, spare3: 0x0
***
Reread of blocknum=251107, file=/dev/vg_mdb02/rdata_2g_050. found same corrupt data
Thu Apr 16 16:31:04 2009
……

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

……

四月 16 16:30:19 ur_bak01: NetWorker savegroup: (alert) urmdborafull completed, total 2 client(s), 0 Hostname(s) Unresolved, 1 Failed, 1 Succeeded. (ur_mdb01 Failed)

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03009: failure of backup command on t2 channel at 03/20/2009 13:49:09

ORA-19566: exceeded limit of 0 corrupt blocks for file /dev/vg_mdb02/rdata_2g_050

ORA-000060: Deadlock detected. More info in file /oracle/app/oracle/admin/uradt/udump/uradt_ora_3035.trc.

***

Corrupt block relative dba: 0x1a43d4e3 (file 105, block 251107)

Fractured block found during backing up datafile

Data in bad block -

type: 0 format: 0 rdba: 0x00000000

last change scn: 0x0000.00000000 seq: 0x0 flg: 0x00

consistency value in tail: 0x00000000

check value in block header: 0x0, block checksum disabled

spare1: 0x0, spare2: 0x0, spare3: 0x0

***

Reread of blocknum=251107, file=/dev/vg_mdb02/rdata_2g_050. found same corrupt data

Thu Apr 16 16:31:04 2009

……

用dbv检查发现有至少有45个坏块：

[oracle@ur_mdb01 /oracle$]dbv file=/dev/vg_mdb02/rdata_2g_050 BLOCKSIZE=8192

DBVERIFY: Release 9.2.0.6.0 - Production on Mon Apr 20 09:55:07 2009

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

DBVERIFY - Verification starting : FILE = /dev/vg_mdb02/rdata_2g_050
Page 251107 is influx - most likely media corrupt
***
Corrupt block relative dba: 0x1a43d4e3 (file 105, block 251107)
Fractured block found during dbv: 
Data in bad block -
 type: 0 format: 0 rdba: 0x00000000
 last change scn: 0x0000.00000000 seq: 0x0 flg: 0x00
 consistency value in tail: 0x00000000
 check value in block header: 0x0, block checksum disabled
 spare1: 0x0, spare2: 0x0, spare3: 0x0
***

Page 251108 is marked corrupt
***
Corrupt block relative dba: 0x1a43d4e4 (file 105, block 251108)
Bad header found during dbv: 
Data in bad block -
 type: 181 format: 6 rdba: 0x00000000
 last change scn: 0x0000.00000000 seq: 0x0 flg: 0x00
 consistency value in tail: 0x00000000
 check value in block header: 0x0, block checksum disabled
 spare1: 0x7, spare2: 0xc, spare3: 0x0
***

……

Corrupt block relative dba: 0x1a43d56f (file 105, block 251247)
Bad header found during dbv: 
Data in bad block -
 type: 65 format: 5 rdba: 0x527002c2
 last change scn: 0x3131.02063033 seq: 0x30 flg: 0x31
 consistency value in tail: 0x3635032d
 check value in block header: 0x180, block checksum disabled
 spare1: 0x50, spare2: 0x72, spare3: 0x430
***



DBVERIFY - Verification complete

Total Pages Examined         : 262016
Total Pages Processed (Data) : 60240
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 568
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 201163
Total Pages Marked Corrupt   : 45
Total Pages Influx           : 11
Highest block SCN            : 10816042273 (2.2226107681)

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

[oracle@ur_mdb01 /oracle$]dbv file=/dev/vg_mdb02/rdata_2g_050 BLOCKSIZE=8192

DBVERIFY: Release 9.2.0.6.0 - Production on Mon Apr 20 09:55:07 2009

DBVERIFY - Verification starting : FILE = /dev/vg_mdb02/rdata_2g_050

Page 251107 is influx - most likely media corrupt

***

Corrupt block relative dba: 0x1a43d4e3 (file 105, block 251107)

Fractured block found during dbv:

Data in bad block -

type: 0 format: 0 rdba: 0x00000000

last change scn: 0x0000.00000000 seq: 0x0 flg: 0x00

consistency value in tail: 0x00000000

check value in block header: 0x0, block checksum disabled

spare1: 0x0, spare2: 0x0, spare3: 0x0

***

Page 251108 is marked corrupt

***

Corrupt block relative dba: 0x1a43d4e4 (file 105, block 251108)

Bad header found during dbv:

Data in bad block -

type: 181 format: 6 rdba: 0x00000000

last change scn: 0x0000.00000000 seq: 0x0 flg: 0x00

consistency value in tail: 0x00000000

check value in block header: 0x0, block checksum disabled

spare1: 0x7, spare2: 0xc, spare3: 0x0

***

……

Corrupt block relative dba: 0x1a43d56f (file 105, block 251247)

Bad header found during dbv:

Data in bad block -

type: 65 format: 5 rdba: 0x527002c2

last change scn: 0x3131.02063033 seq: 0x30 flg: 0x31

consistency value in tail: 0x3635032d

check value in block header: 0x180, block checksum disabled

spare1: 0x50, spare2: 0x72, spare3: 0x430

***

DBVERIFY - Verification complete

Total Pages Examined : 262016

Total Pages Processed (Data) : 60240

Total Pages Failing (Data) : 0

Total Pages Processed (Index): 0

Total Pages Failing (Index): 0

Total Pages Processed (Other): 568

Total Pages Processed (Seg) : 0

Total Pages Failing (Seg) : 0

Total Pages Empty : 201163

Total Pages Marked Corrupt : 45

Total Pages Influx : 11

Highest block SCN : 10816042273 (2.2226107681)

经检查，发现这些坏块上没有任何数据对象： [crayon-678320e197e8784772 […]

..experience, Working case

opatch被异常中断后的处理

2009-04-13 一条评论

今天在打一个patch的时候，已经是用opatch执行到了最后一个patch，一时手欠，没看清楚telnet的窗口，不小心按下了ctrl+C，于是，opatch被中断了。汗了，晚节不保啊！再次执行opatch apply，报错：

$ opatch apply 6163771
Invoking OPatch 10.2.0.4.6

Oracle Interim Patch Installer version 10.2.0.4.6
Copyright (c) 2009, Oracle Corporation.  All rights reserved.


Oracle Home       : /oracle/app/oracle/product/10.2.0/db_1
Central Inventory : /oracle/app/oracle/oraInventory
   from           : /var/opt/oracle/oraInst.loc
OPatch version    : 10.2.0.4.6
OUI version       : 10.2.0.4.0
OUI location      : /oracle/app/oracle/product/10.2.0/db_1/oui
Log file location : /oracle/app/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch2009-04-08_16-29-59PM.log

Patch history file: /oracle/app/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch_history.txt

ApplySession applying interim patch '6163771' to OH '/oracle/app/oracle/product/10.2.0/db_1'

Running prerequisite checks...

OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.


Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/oracle/app/oracle/product/10.2.0/db_1')


Is the local system ready for patching? [y|n]
Y
User Responded with: Y
Backing up files and inventory (not for auto-rollback) for the Oracle Home
Backing up files affected by the patch '6163771' for restore. This might take a while...
Backing up files affected by the patch '6163771' for rollback. This might take a while...

Patching component oracle.rdbms, 10.2.0.4.0...
Updating archive file "/oracle/app/oracle/product/10.2.0/db_1/lib/libserver10.a"  with "lib/libserver10.a/kfc.o"
Updating archive file "/oracle/app/oracle/product/10.2.0/db_1/lib/libserver10.a"  with "lib/libserver10.a/kfcb.o"
Updating archive file "/oracle/app/oracle/product/10.2.0/db_1/lib/libserver10.a"  with "lib/libserver10.a/kfcl.o"
Updating archive file "/oracle/app/oracle/product/10.2.0/db_1/lib/libserver10.a"  with "lib/libserver10.a/kfr.o"
Updating archive file "/oracle/app/oracle/product/10.2.0/db_1/lib/libserver10.a"  with "lib/libserver10.a/kfrb.o"

The following actions have failed:
Archive not applied /arch/ora_patch/patch_ia/7409356/6163771/files/lib/libserver10.a/kfc.o to /oracle/app/oracle/product/10.2.0/db_1/lib/libserver10.a... ''
Archive not applied /arch/ora_patch/patch_ia/7409356/6163771/files/lib/libserver10.a/kfcb.o to /oracle/app/oracle/product/10.2.0/db_1/lib/libserver10.a... ''
Archive not applied /arch/ora_patch/patch_ia/7409356/6163771/files/lib/libserver10.a/kfcl.o to /oracle/app/oracle/product/10.2.0/db_1/lib/libserver10.a... ''
Archive not applied /arch/ora_patch/patch_ia/7409356/6163771/files/lib/libserver10.a/kfr.o to /oracle/app/oracle/product/10.2.0/db_1/lib/libserver10.a... ''
Archive not applied /arch/ora_patch/patch_ia/7409356/6163771/files/lib/libserver10.a/kfrb.o to /oracle/app/oracle/product/10.2.0/db_1/lib/libserver10.a... ''


Do you want to proceed? [y|n]
Y   
User Responded with: Y
Running make for target ioracle
Make failed to invoke "/usr/ccs/bin/make -f ins_rdbms.mk ioracle ORACLE_HOME=/oracle/app/oracle/product/10.2.0/db_1"....'ld: I/O error, file "/oracle/app/oracle/product/10.2.0/db_1/lib//libserver10.a": 
Fatal error.

Stop.
'

The following make actions have failed :

Re-link fails on target "ioracle".


Do you want to proceed? [y|n]
Y
User Responded with: Y
ApplySession adding interim patch '6163771' to inventory

Verifying the update...
Inventory check OK: Patch ID 6163771 is registered in Oracle Home inventory with proper meta-data.
ApplySession failed: ApplySession failed in system modification phase... 'Verification of patch failed: Error verification failed: ar: kfc.o not found
'
OPatch will attempt to restore the system...
Restoring the Oracle Home...
Checking if OPatch needs to invoke 'make' to restore some binaries...
Make failed to invoke "/usr/ccs/bin/make -f ins_rdbms.mk ioracle ORACLE_HOME=/oracle/app/oracle/product/10.2.0/db_1"....'ld: I/O error, file "/oracle/app/oracle/product/10.2.0/db_1/lib//libserver10.a": 
Fatal error.

Stop.
'

--------------------------------------------------------------------------------
Failed to run make commands. They are stored in file '/oracle/app/oracle/product/10.2.0/db_1/.patch_storage/6163771_Jun_19_2008_17_41_45/make.txt'
Invoke these commands manually to restore the binaries in the Oracle Home.
OPatch failed to restore OH '/oracle/app/oracle/product/10.2.0/db_1'. Consult OPatch document to restore the home manually before proceeding.
--------------------------------------------------------------------------------
The following warnings have occurred during OPatch execution:
1) OUI-67124:Archive not applied /arch/ora_patch/patch_ia/7409356/6163771/files/lib/libserver10.a/kfc.o to /oracle/app/oracle/product/10.2.0/db_1/lib/libserver10.a... ''
Archive not applied /arch/ora_patch/patch_ia/7409356/6163771/files/lib/libserver10.a/kfcb.o to /oracle/app/oracle/product/10.2.0/db_1/lib/libserver10.a... ''
Archive not applied /arch/ora_patch/patch_ia/7409356/6163771/files/lib/libserver10.a/kfcl.o to /oracle/app/oracle/product/10.2.0/db_1/lib/libserver10.a... ''
Archive not applied /arch/ora_patch/patch_ia/7409356/6163771/files/lib/libserver10.a/kfr.o to /oracle/app/oracle/product/10.2.0/db_1/lib/libserver10.a... ''
Archive not applied /arch/ora_patch/patch_ia/7409356/6163771/files/lib/libserver10.a/kfrb.o to /oracle/app/oracle/product/10.2.0/db_1/lib/libserver10.a... ''

2) OUI-67200:Make failed to invoke "/usr/ccs/bin/make -f ins_rdbms.mk ioracle ORACLE_HOME=/oracle/app/oracle/product/10.2.0/db_1"....'ld: I/O error, file "/oracle/app/oracle/product/10.2.0/db_1/lib//libserver10.a": 
Fatal error.

Stop.
'
3) OUI-67124:Re-link fails on target "ioracle".

4) OUI-67200:Make failed to invoke "/usr/ccs/bin/make -f ins_rdbms.mk ioracle ORACLE_HOME=/oracle/app/oracle/product/10.2.0/db_1"....'ld: I/O error, file "/oracle/app/oracle/product/10.2.0/db_1/lib//libserver10.a": 
Fatal error.

Stop.
'
--------------------------------------------------------------------------------

OPatch failed with error code 115
$

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

$ opatch apply 6163771

Invoking OPatch 10.2.0.4.6

Oracle Interim Patch Installer version 10.2.0.4.6

Oracle Home : /oracle/app/oracle/product/10.2.0/db_1

Central Inventory : /oracle/app/oracle/oraInventory

from : /var/opt/oracle/oraInst.loc

OPatch version : 10.2.0.4.6

OUI version : 10.2.0.4.0

OUI location : /oracle/app/oracle/product/10.2.0/db_1/oui

Log file location : /oracle/app/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch2009-04-08_16-29-59PM.log

Patch history file: /oracle/app/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch_history.txt

ApplySession applying interim patch '6163771' to OH '/oracle/app/oracle/product/10.2.0/db_1'

Running prerequisite checks...

OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.

(Oracle Home = '/oracle/app/oracle/product/10.2.0/db_1')

Is the local system ready for patching? [y|n]

Y

User Responded with: Y

Backing up files and inventory (not for auto-rollback) for the Oracle Home

Backing up files affected by the patch '6163771' for restore. This might take a while...

Backing up files affected by the patch '6163771' for rollback. This might take a while...

Patching component oracle.rdbms, 10.2.0.4.0...

Updating archive file "/oracle/app/oracle/product/10.2.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kfc.o"

Updating archive file "/oracle/app/oracle/product/10.2.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kfcb.o"

Updating archive file "/oracle/app/oracle/product/10.2.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kfcl.o"

Updating archive file "/oracle/app/oracle/product/10.2.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kfr.o"

Updating archive file "/oracle/app/oracle/product/10.2.0/db_1/lib/libserver10.a" with "lib/libserver10.a/kfrb.o"

The following actions have failed:

Archive not applied /arch/ora_patch/patch_ia/7409356/6163771/files/lib/libserver10.a/kfc.o to /oracle/app/oracle/product/10.2.0/db_1/lib/libserver10.a... ''

Archive not applied /arch/ora_patch/patch_ia/7409356/6163771/files/lib/libserver10.a/kfcb.o to /oracle/app/oracle/product/10.2.0/db_1/lib/libserver10.a... ''

Archive not applied /arch/ora_patch/patch_ia/7409356/6163771/files/lib/libserver10.a/kfcl.o to /oracle/app/oracle/product/10.2.0/db_1/lib/libserver10.a... ''

Archive not applied /arch/ora_patch/patch_ia/7409356/6163771/files/lib/libserver10.a/kfr.o to /oracle/app/oracle/product/10.2.0/db_1/lib/libserver10.a... ''

Archive not applied /arch/ora_patch/patch_ia/7409356/6163771/files/lib/libserver10.a/kfrb.o to /oracle/app/oracle/product/10.2.0/db_1/lib/libserver10.a... ''

Do you want to proceed? [y|n]

Y

User Responded with: Y

Running make for target ioracle

Make failed to invoke "/usr/ccs/bin/make -f ins_rdbms.mk ioracle ORACLE_HOME=/oracle/app/oracle/product/10.2.0/db_1"....'ld: I/O error, file "/oracle/app/oracle/product/10.2.0/db_1/lib//libserver10.a":

Fatal error.

Stop.

'

The following make actions have failed :

Re-link fails on target "ioracle".

Do you want to proceed? [y|n]

Y

User Responded with: Y

ApplySession adding interim patch '6163771' to inventory

Verifying the update...

Inventory check OK: Patch ID 6163771 is registered in Oracle Home inventory with proper meta-data.

ApplySession failed: ApplySession failed in system modification phase... 'Verification of patch failed: Error verification failed: ar: kfc.o not found

'

OPatch will attempt to restore the system...

Restoring the Oracle Home...

Checking if OPatch needs to invoke 'make' to restore some binaries...

Make failed to invoke "/usr/ccs/bin/make -f ins_rdbms.mk ioracle ORACLE_HOME=/oracle/app/oracle/product/10.2.0/db_1"....'ld: I/O error, file "/oracle/app/oracle/product/10.2.0/db_1/lib//libserver10.a":

Fatal error.

Stop.

'

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

Failed to run make commands. They are stored in file '/oracle/app/oracle/product/10.2.0/db_1/.patch_storage/6163771_Jun_19_2008_17_41_45/make.txt'

Invoke these commands manually to restore the binaries in the Oracle Home.

OPatch failed to restore OH '/oracle/app/oracle/product/10.2.0/db_1'. Consult OPatch document to restore the home manually before proceeding.

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

The following warnings have occurred during OPatch execution:

1) OUI-67124:Archive not applied /arch/ora_patch/patch_ia/7409356/6163771/files/lib/libserver10.a/kfc.o to /oracle/app/oracle/product/10.2.0/db_1/lib/libserver10.a... ''

Archive not applied /arch/ora_patch/patch_ia/7409356/6163771/files/lib/libserver10.a/kfcb.o to /oracle/app/oracle/product/10.2.0/db_1/lib/libserver10.a... ''

Archive not applied /arch/ora_patch/patch_ia/7409356/6163771/files/lib/libserver10.a/kfcl.o to /oracle/app/oracle/product/10.2.0/db_1/lib/libserver10.a... ''

Archive not applied /arch/ora_patch/patch_ia/7409356/6163771/files/lib/libserver10.a/kfr.o to /oracle/app/oracle/product/10.2.0/db_1/lib/libserver10.a... ''

Archive not applied /arch/ora_patch/patch_ia/7409356/6163771/files/lib/libserver10.a/kfrb.o to /oracle/app/oracle/product/10.2.0/db_1/lib/libserver10.a... ''

2) OUI-67200:Make failed to invoke "/usr/ccs/bin/make -f ins_rdbms.mk ioracle ORACLE_HOME=/oracle/app/oracle/product/10.2.0/db_1"....'ld: I/O error, file "/oracle/app/oracle/product/10.2.0/db_1/lib//libserver10.a":

Fatal error.

Stop.

'

3) OUI-67124:Re-link fails on target "ioracle".

4) OUI-67200:Make failed to invoke "/usr/ccs/bin/make -f ins_rdbms.mk ioracle ORACLE_HOME=/oracle/app/oracle/product/10.2.0/db_1"....'ld: I/O error, file "/oracle/app/oracle/product/10.2.0/db_1/lib//libserver10.a":

Fatal error.

Stop.

'

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

OPatch failed with error code 115

$

用 […]

..experience, Working case

listener的内存泄漏

2009-04-07 6条评论

今天收到告警邮件，某省的数据库无法登录，在alertlog中有如下的报错：

Tue Mar 31 16:38:11 2009
Errors in file /oracle/app/oracle/admin/zjfs/bdump/zjfs_ora_22423.trc:
ORA-27102: out of memory
HPUX-ia64 Error: 12: Not enough space
Additional information: 103
Additional information: 524288

1

2

3

4

5

6

Tue Mar 31 16:38:11 2009

Errors in file /oracle/app/oracle/admin/zjfs/bdump/zjfs_ora_22423.trc:

ORA-27102: out of memory

HPUX-ia64 Error: 12: Not enough space

Additional information: 103

Additional information: 524288

登录数据库主机检查内存剩余量不到10%，用top检查：

System: zj-db01                                       Wed Apr  1 14:10:47 2009
Load averages: 0.12, 0.18, 0.28
387 processes: 362 sleeping, 25 running
Cpu states:
CPU   LOAD   USER   NICE    SYS   IDLE  BLOCK  SWAIT   INTR   SSYS
 0    0.21   8.4%   0.0%   7.0%  84.7%   0.0%   0.0%   0.0%   0.0%
 1    0.09   3.4%   0.0%   2.4%  94.2%   0.0%   0.0%   0.0%   0.0%
 2    0.11   3.4%   0.0%   0.0%  96.6%   0.0%   0.0%   0.0%   0.0%
 3    0.10   3.2%   0.0%   1.0%  95.8%   0.0%   0.0%   0.0%   0.0%
---   ----  -----  -----  -----  -----  -----  -----  -----  -----
avg   0.12   4.6%   0.0%   2.6%  92.8%   0.0%   0.0%   0.0%   0.0%

Memory: 4456068K (2521320K) real, 6115756K (3135960K) virtual, 391416K free  Page# 1/15

CPU TTY     PID USERNAME PRI NI   SIZE    RES STATE    TIME %WCPU  %CPU COMMAND
 0   ?     2307 oracle   178 20  3015M 24612K sleep 4383:34 15.11 15.08 oraclezjfs
 2   ?     2228 oracle   178 20  2986M  9624K sleep 3580:19  2.94  2.93 oraclezjfs
 1   ?     2309 oracle   178 20  2986M  5536K sleep  541:29  2.92  2.91 oraclezjfs
 1   ?     4400 oracle   154 20  1375M  1340M sleep 11224:04  2.04  2.04 tnslsnr  
 1   ?     2528 oracle   178 20  2986M  5612K sleep   15:05  1.32  1.32 oraclezjfs   
 1   ?     4380 oracle   178 20  3009M 35136K sleep  268:00  1.16  1.16 ora_lgwr_zjfs
 3   ?     4378 oracle   178 20  3009M 35152K sleep  244:54  0.74  0.74 ora_dbw0_zjfs
 1   ?       54 root     152 20  3312K  2944K run    136:01  0.45  0.45 vxfsd
 3   ?     1439 root     152 20   207M 84724K run    103:21  0.40  0.40 cimprovagt
 3   ?     1442 root     152 20 38312K  2820K run   2725:46  0.36  0.36 cimprovagt   
 3   ?    12064 oracle   178 20  2986M  5264K sleep    0:20  0.32  0.32 oraclezjfs   
 2   ?     1436 root     152 20 56792K 13976K run    180:41  0.23  0.23 cimserver 
 3 pts/ta 21448 oracle   168 20 10836K  1284K sleep    0:00  0.22  0.22 top
 2   ?     2381 oracle   178 20  2987M  5944K sleep  419:20  0.18  0.18 oraclezjfs
 1   ?       38 root     152 20   432K   384K run     60:21  0.16  0.16 schedcpu  
 2 pts/tc 21573 oracle   178 20 10964K  1412K run      0:00  0.27  0.15 top
 2   ?    21551 oracle   178 20  2986M  5260K sleep    0:00  0.16  0.14 oraclezjfs
 3   ?     1793 root     152 20   113M 17208K run      6:09  0.14  0.14 vxsvc
 0   ?       20 root     191 20   360K   320K run     33:43  0.13  0.13 ksyncer_daemon
 2   ?     1429 root     152 20 25516K  5536K run      4:42  0.12  0.12 rpcd
 1   ?     4297 root     -27 20 46772K 38548K run     31:58  0.12  0.12 cmcld
 2   ?    21518 oracle   178 20  2986M  5260K sleep    0:00  0.13  0.12 oraclezjfs
 2   ?     1228 root     154 20  7812K   848K sleep  126:00  0.10  0.10 sendmail:
 2   ?       39 root     191 20   288K   256K run    305:44  0.08  0.08 pagezerod
 2   ?     1589 root     152 20 25072K  3992K run      1:20  0.08  0.08 swagentd 
$ 
$

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

System: zj-db01 Wed Apr 1 14:10:47 2009

Load averages: 0.12, 0.18, 0.28

387 processes: 362 sleeping, 25 running

Cpu states:

CPU LOAD USER NICE SYS IDLE BLOCK SWAIT INTR SSYS

0 0.21 8.4% 0.0% 7.0% 84.7% 0.0% 0.0% 0.0% 0.0%

1 0.09 3.4% 0.0% 2.4% 94.2% 0.0% 0.0% 0.0% 0.0%

2 0.11 3.4% 0.0% 0.0% 96.6% 0.0% 0.0% 0.0% 0.0%

3 0.10 3.2% 0.0% 1.0% 95.8% 0.0% 0.0% 0.0% 0.0%

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

avg 0.12 4.6% 0.0% 2.6% 92.8% 0.0% 0.0% 0.0% 0.0%

Memory: 4456068K (2521320K) real, 6115756K (3135960K) virtual, 391416K free Page# 1/15

CPU TTY PID USERNAME PRI NI SIZE RES STATE TIME %WCPU %CPU COMMAND

0 ? 2307 oracle 178 20 3015M 24612K sleep 4383:34 15.11 15.08 oraclezjfs

2 ? 2228 oracle 178 20 2986M 9624K sleep 3580:19 2.94 2.93 oraclezjfs

1 ? 2309 oracle 178 20 2986M 5536K sleep 541:29 2.92 2.91 oraclezjfs

1 ? 4400 oracle 154 20 1375M 1340M sleep 11224:04 2.04 2.04 tnslsnr

1 ? 2528 oracle 178 20 2986M 5612K sleep 15:05 1.32 1.32 oraclezjfs

1 ? 4380 oracle 178 20 3009M 35136K sleep 268:00 1.16 1.16 ora_lgwr_zjfs

3 ? 4378 oracle 178 20 3009M 35152K sleep 244:54 0.74 0.74 ora_dbw0_zjfs

1 ? 54 root 152 20 3312K 2944K run 136:01 0.45 0.45 vxfsd

3 ? 1439 root 152 20 207M 84724K run 103:21 0.40 0.40 cimprovagt

3 ? 1442 root 152 20 38312K 2820K run 2725:46 0.36 0.36 cimprovagt

3 ? 12064 oracle 178 20 2986M 5264K sleep 0:20 0.32 0.32 oraclezjfs

2 ? 1436 root 152 20 56792K 13976K run 180:41 0.23 0.23 cimserver

3 pts/ta 21448 oracle 168 20 10836K 1284K sleep 0:00 0.22 0.22 top

2 ? 2381 oracle 178 20 2987M 5944K sleep 419:20 0.18 0.18 oraclezjfs

1 ? 38 root 152 20 432K 384K run 60:21 0.16 0.16 schedcpu

2 pts/tc 21573 oracle 178 20 10964K 1412K run 0:00 0.27 0.15 top

2 ? 21551 oracle 178 20 2986M 5260K sleep 0:00 0.16 0.14 oraclezjfs

3 ? 1793 root 152 20 113M 17208K run 6:09 0.14 0.14 vxsvc

0 ? 20 root 191 20 360K 320K run 33:43 0.13 0.13 ksyncer_daemon

2 ? 1429 root 152 20 25516K 5536K run 4:42 0.12 0.12 rpcd

1 ? 4297 root -27 20 46772K 38548K run 31:58 0.12 0.12 cmcld

2 ? 21518 oracle 178 20 2986M 5260K sleep 0:00 0.13 0.12 oraclezjfs

2 ? 1228 root 154 20 7812K 848K sleep 126:00 0.10 0.10 sendmail:

2 ? 39 root 191 20 288K 256K run 305:44 0.08 0.08 pagezerod

2 ? 1589 root 152 20 25072K 3992K run 1:20 0.08 0.08 swagentd

$

发现listener占用的内存非常大 […]

..experience, Working case

安装数据库时报错无法写入

2009-03-23 没有评论

今天在安装数据库的时候，报错文件无法写入：一开始想，是在copy的时候报错，是不是安装介质的缘故，难道是ftp传输的时候有问题？由于之前是通过写ftp脚本挂后台跑，log中虽然没什么报错，但是以防万一，还是再传了一次。但是安装到27%，还是报错了，虽然不是报同样的一个文件write error， […]

It's my life

用无线连接电信E8的机顶盒收看IPTV

2009-03-16 12条评论

前段时间，申请的电信的E8套餐，装上了4M的宽带和2M带宽的IPTV（电信称之为iTV），团购价为1860元1年。和1800元3M宽带相比，这个确实很实惠，除了总计6M的带宽外，还有300分钟的无线热点上网，其他还包含固话月租（20元）、来显（6元）、彩铃（5元）、铃音盒（3首/月），50元语音话费 […]

..experience, Working case

rac的dp备份时候报错RMAN-20242

2009-03-09 一条评论

今天收到一个rac省的dp报错记录：

[Normal] From: BSM@fj-bak01 ""  Time: 2009-3-9 9:10:11
	The session "2009/03/09-1" will be restarted.

[Normal] From: BSM@fj-bak01 ""  Time: 2009-3-9 9:10:11
	Restart backup specification found: "Oracle8 fjmisc2_arch".

[Normal] From: BSM@fj-bak01 "fjmisc2_arch"  Time: 2009-3-9 9:10:13
	OB2BAR application on "fj_db02" successfully started.

ob2rman.exe started with arguments:
	-backup -full

Recovery Manager: Release 9.2.0.6.0 - 64bit Production

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

RMAN> 
connected to target database: FJMISC (DBID=169290886)

RMAN> 
connected to recovery catalog database

RMAN> 
RMAN> run {
2> allocate channel 'dev_0' type 'sbt_tape'
3> parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=fjmisc2,OB2BARLIST=fjmisc2_arch,OB2BARHOSTNAME=fj_db02)';
4> allocate channel 'dev_1' type 'sbt_tape'
5> parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=fjmisc2,OB2BARLIST=fjmisc2_arch,OB2BARHOSTNAME=fj_db02)';
6> allocate channel 'dev_2' type 'sbt_tape'
7> parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=fjmisc2,OB2BARLIST=fjmisc2_arch,OB2BARHOSTNAME=fj_db02)';
8> allocate channel 'dev_3' type 'sbt_tape'
9> parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=fjmisc2,OB2BARLIST=fjmisc2_arch,OB2BARHOSTNAME=fj_db02)';
10> crosscheck archivelog from time 'sysdate-1' until time 'sysdate';
11> backup
12> format 'fjmisc2_arch<fjmisc2_%s:%t:%p>.dbf'
13> archivelog like '/archlog2/fjmisc2%'
14> delete input;
15> }
allocated channel: dev_0
channel dev_0: sid=251 devtype=SBT_TAPE
channel dev_0: Data Protector A.05.50/330

allocated channel: dev_1
channel dev_1: sid=276 devtype=SBT_TAPE
channel dev_1: Data Protector A.05.50/330

allocated channel: dev_2
channel dev_2: sid=204 devtype=SBT_TAPE
channel dev_2: Data Protector A.05.50/330

allocated channel: dev_3
channel dev_3: sid=301 devtype=SBT_TAPE
channel dev_3: Data Protector A.05.50/330

validation succeeded for archived log
archive log filename=/archlog1/fjmisc1_1_21584.arc recid=38312 stamp=681012033
validation succeeded for archived log
archive log filename=/archlog1/fjmisc1_1_21585.arc recid=38315 stamp=681032056
Crosschecked 2 objects


Starting backup at 03/09/2009 [09:16:19]
released channel: dev_0
released channel: dev_1
released channel: dev_2
released channel: dev_3
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 03/09/2009 09:16:19
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20242: specification does not match any archive log in the recovery catalog

RMAN> **end-of-file**

RMAN> 

Recovery Manager complete.
[Major] From: ob2rman.exe@FJ_DB02 "fjmisc2"  Time: 03/09/09 09:16:19

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

[Normal] From: BSM@fj-bak01 "" Time: 2009-3-9 9:10:11

The session "2009/03/09-1" will be restarted.

[Normal] From: BSM@fj-bak01 "" Time: 2009-3-9 9:10:11

Restart backup specification found: "Oracle8 fjmisc2_arch".

[Normal] From: BSM@fj-bak01 "fjmisc2_arch" Time: 2009-3-9 9:10:13

OB2BAR application on "fj_db02" successfully started.

ob2rman.exe started with arguments:

-backup -full

Recovery Manager: Release 9.2.0.6.0 - 64bit Production

RMAN>

connected to target database: FJMISC (DBID=169290886)

RMAN>

connected to recovery catalog database

RMAN>

RMAN> run {

2> allocate channel 'dev_0' type 'sbt_tape'

3> parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=fjmisc2,OB2BARLIST=fjmisc2_arch,OB2BARHOSTNAME=fj_db02)';

4> allocate channel 'dev_1' type 'sbt_tape'

5> parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=fjmisc2,OB2BARLIST=fjmisc2_arch,OB2BARHOSTNAME=fj_db02)';

6> allocate channel 'dev_2' type 'sbt_tape'

7> parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=fjmisc2,OB2BARLIST=fjmisc2_arch,OB2BARHOSTNAME=fj_db02)';

8> allocate channel 'dev_3' type 'sbt_tape'

9> parms 'ENV=(OB2BARTYPE=Oracle8,OB2APPNAME=fjmisc2,OB2BARLIST=fjmisc2_arch,OB2BARHOSTNAME=fj_db02)';

10> crosscheck archivelog from time 'sysdate-1' until time 'sysdate';

11> backup

12> format 'fjmisc2_arch<fjmisc2_%s:%t:%p>.dbf'

13> archivelog like '/archlog2/fjmisc2%'

14> delete input;

15> }

allocated channel: dev_0

channel dev_0: sid=251 devtype=SBT_TAPE

channel dev_0: Data Protector A.05.50/330

allocated channel: dev_1

channel dev_1: sid=276 devtype=SBT_TAPE

channel dev_1: Data Protector A.05.50/330

allocated channel: dev_2

channel dev_2: sid=204 devtype=SBT_TAPE

channel dev_2: Data Protector A.05.50/330

allocated channel: dev_3

channel dev_3: sid=301 devtype=SBT_TAPE

channel dev_3: Data Protector A.05.50/330

validation succeeded for archived log

archive log filename=/archlog1/fjmisc1_1_21584.arc recid=38312 stamp=681012033

validation succeeded for archived log

archive log filename=/archlog1/fjmisc1_1_21585.arc recid=38315 stamp=681032056

Crosschecked 2 objects

Starting backup at 03/09/2009 [09:16:19]

released channel: dev_0

released channel: dev_1

released channel: dev_2

released channel: dev_3

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of backup command at 03/09/2009 09:16:19

RMAN-06004: ORACLE error from recovery catalog database: RMAN-20242: specification does not match any archive log in the recovery catalog

RMAN> **end-of-file**

RMAN>

Recovery Manager complete.

[Major] From: ob2rman.exe@FJ_DB02 "fjmisc2" Time: 03/09/09 09:16:19

看到这个log，一开始的感觉是手工删除arch过了，上数据库主机做了crosscheck，发现在db01上关于db02的arch都是expired的状态。通过bdf检查发现在db01上，只能看到a […]

Study note

HPUX中常用的getconf命令参数

2009-03-05 没有评论

常用的getconf命令参数，在此记录，补充一下自己的os知识：

getconf MACHINE_SERIAL
获取主机设备序列号，这个序列号可用于HP的800报障。

getconf PAGE_SIZE
每个内存页的大小。用kmeminfo或者ps -lfp 看内存显示的结果是多少个内存页，要乘以这个每个内存页的大小才得到每个进程的占用内存。

getconf KERNEL_BITS
查看操作系统是32位还是64位，在安装oracle前必须弄清楚os版本，安装对应版本的oracle。

getconf MACHINE_MODEL
查看主机的具体型号。如rp8440，N4000-55

getconf HW_CPU_SUPP_BITS
硬件cpu支持多少位。如64位的cpu支持64位的os和32位的os。

1

2

3

4

5

6

7

8

9

10

11

12

13

14

getconf MACHINE_SERIAL

获取主机设备序列号，这个序列号可用于HP的800报障。

getconf PAGE_SIZE

每个内存页的大小。用kmeminfo或者ps -lfp 看内存显示的结果是多少个内存页，要乘以这个每个内存页的大小才得到每个进程的占用内存。

getconf KERNEL_BITS

查看操作系统是32位还是64位，在安装oracle前必须弄清楚os版本，安装对应版本的oracle。

getconf MACHINE_MODEL

查看主机的具体型号。如rp8440，N4000-55

getconf HW_CPU_SUPP_BITS

硬件cpu支持多少位。如64位的cpu支持64位的os和32位的os。

..experience, Working case

3小时的sql调优到3分钟。

2009-03-02 9条评论

今天接到这样一个问题，某省的报表系统的一个某个处理进程在前几天处理的速度突然变慢，而且从应用的log上还看到1555的报错：

[2009-02-25 00:02:27] [200010000000006] [5]: 任务1执行失败!!!
[2009-02-25 01:36:55] [200010000000007] [5]: servid(015027016400) not found in map_servid_servattr!!!
[2009-02-25 03:24:55] [200010000600008] [0]: 从光标中fetch数据出错 ORA-01555: snapshot too old: rollback segment number 6 with name "_SYSSMU6$" too small

1

2

3

[2009-02-25 00:02:27] [200010000000006] [5]: 任务1执行失败!!!

[2009-02-25 01:36:55] [200010000000007] [5]: servid(015027016400) not found in map_servid_servattr!!!

[2009-02-25 03:24:55] [200010000600008] [0]: 从光标中fetch数据出错 ORA-01555: snapshot too old: rollback segment number 6 with name "_SYSSMU6$" too small

当时第一个反应就是加大undo表空间大小和undo retention参数。但是，之前的程序跑的还比较正常， […]

..experience, Working case

legato备份磨难记

2009-02-26 6条评论

华西某省进行数据库主机升级，将一台老机器升级到新机器上，在新机器上安装完数据库软件之后，把存储从老机器往新机器一挂，数据库算是迁移完成了。但是自从升级完成后，legato一直备份不成功。为了这个问题，和华西的dba王术成研究了好几天。第一天：发现备份执行完了，但任务状态不恢复正常，检查数据库主机 […]

Study note

小议9i和10g的差别点

2009-02-19 4条评论

10g的一些新特性就不在这边讨论了，flashback、ash、awr等等都在newfeature中可以看到。在这里讨论下在平常的开发和维护的过程中，容易忽视的差别点。如果您在日常工作中也遇到了别的差别，值得需要注意的地方，也欢迎您告诉我。一、9i的group by会排序，10g的不会。 [cra […]

It's my life

细数幸福来临的日子……

2009-02-12 48条评论

渐渐的，到结婚的日子越来越近了，人生中又一个重大的转折要来临了。看着像燕子衔泥一样，把家一点一点的装填，心中感到无比的幸福。本来有好多话要说，到这个时候，又想不出来要说什么；今年年初的时候，本来计划了好多事情，结婚，搞DBA团队建设，考OCM，考驾照……此时才发现，好多事情都要给结婚让路了，结婚才 […]

Study note

9i和10g的plan table

2009-02-12 没有评论

在oracle 10g中,plan table的基表已经改变，在9i的时候，还是一个table类型，在10g的时候，已经是temporary table类型了。在10g中，如果通过dblink访问9i的库，那么explain table将无法显示。会报错： [crayon-678320e1a340 […]