oracle 10g rac当监听程序监听对方vip时启动监听报错TNS-12545

rac中各节点监听程序本来只监听当前节点的vip 和public ip,不知为何客户的rac各个节点配置成监听自己节点和对方节点的vip
连接rac数据库报TNS-12545错误
登录数据库服务器执行crs_stat -t

[oracle@keqsi1 admin]$ crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora.keqjm.db   application    ONLINE    ONLINE    keqsi1
ora....m1.inst application    ONLINE    ONLINE    keqsi1
ora....m2.inst application    ONLINE    ONLINE    keqsi2
ora....orcl.cs application    ONLINE    ONLINE    keqsi2
ora....jm1.srv application    ONLINE    ONLINE    keqsi1
ora....jm2.srv application    ONLINE    ONLINE    keqsi2
ora....SM1.asm application    ONLINE    ONLINE    keqsi1
ora....I1.lsnr application    ONLINE    OFFLINE
ora.keqsi1.gsd application    ONLINE    ONLINE    keqsi1
ora.keqsi1.ons application    ONLINE    ONLINE    keqsi1
ora.keqsi1.vip application    ONLINE    ONLINE    keqsi1
ora....SM2.asm application    ONLINE    ONLINE    keqsi2
ora....I2.lsnr application    ONLINE    OFFLINE
ora.keqsi2.gsd application    ONLINE    ONLINE    keqsi2
ora.keqsi2.ons application    ONLINE    ONLINE    keqsi2
ora.keqsi2.vip application    ONLINE    ONLINE    keqsi2

检查listener.ora文件:10.53.1.237和10.53.1.238是两个节点
的虚拟IP地址

[oracle@keqsi2 admin]$ more listener.ora
# listener.ora.keqsi2 Network Configuration File: /u01/app/oracle/10gR2/db/network/admin/listener.ora.keqsi2
# Generated by Oracle configuration tools.

LISTENER_KEQSI2 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.53.1.237)(PORT = 1521)(IP = FIRST))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.53.1.238)(PORT = 1521)(IP = FIRST))
    )
  )

SID_LIST_LISTENER_KEQSI2 =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/10gR2/db)
      (PROGRAM = extproc)
    )
  )

检查网络是否能拼通

[oracle@keqsi2 admin]$ hostname
keqsi2
[oracle@keqsi2 admin]$ more /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
#127.0.0.1       keqsi2  localhost.localdomain   localhost
127.0.0.1       localhost.localdomain    localhost
172.18.20.1     keqsi1-priv
172.18.20.2     keqsi2-priv
10.53.1.230     keqsi1
10.53.1.237     keqsi1-vip
10.53.1.231     keqsi2
10.53.1.238     keqsi2-vip
[oracle@keqsi2 admin]$ ping keqsi1
PING keqsi1 (10.53.1.230) 56(84) bytes of data.
64 bytes from keqsi1 (10.53.1.230): icmp_seq=0 ttl=64 time=0.131 ms
64 bytes from keqsi1 (10.53.1.230): icmp_seq=1 ttl=64 time=0.125 ms
64 bytes from keqsi1 (10.53.1.230): icmp_seq=2 ttl=64 time=0.128 ms
64 bytes from keqsi1 (10.53.1.230): icmp_seq=3 ttl=64 time=0.125 ms
64 bytes from keqsi1 (10.53.1.230): icmp_seq=4 ttl=64 time=0.126 ms
64 bytes from keqsi1 (10.53.1.230): icmp_seq=5 ttl=64 time=0.125 ms
64 bytes from keqsi1 (10.53.1.230): icmp_seq=6 ttl=64 time=0.126 ms

--- keqsi1 ping statistics ---
7 packets transmitted, 7 received, 0% packet loss, time 5999ms
rtt min/avg/max/mdev = 0.125/0.126/0.131/0.012 ms, pipe 2
[oracle@keqsi2 admin]$ ping keqsi2
PING keqsi2 (10.53.1.231) 56(84) bytes of data.
64 bytes from keqsi2 (10.53.1.231): icmp_seq=0 ttl=64 time=0.024 ms
64 bytes from keqsi2 (10.53.1.231): icmp_seq=1 ttl=64 time=0.010 ms
64 bytes from keqsi2 (10.53.1.231): icmp_seq=2 ttl=64 time=0.015 ms

--- keqsi2 ping statistics ---
3 packets transmitted, 3 received, 0% packet loss, time 1999ms
rtt min/avg/max/mdev = 0.010/0.016/0.024/0.006 ms, pipe 2
[oracle@keqsi2 admin]$ ping keqsi2-vip
PING keqsi2-vip (10.53.1.238) 56(84) bytes of data.
64 bytes from keqsi2-vip (10.53.1.238): icmp_seq=0 ttl=64 time=0.018 ms
64 bytes from keqsi2-vip (10.53.1.238): icmp_seq=1 ttl=64 time=0.011 ms
64 bytes from keqsi2-vip (10.53.1.238): icmp_seq=2 ttl=64 time=0.010 ms
64 bytes from keqsi2-vip (10.53.1.238): icmp_seq=3 ttl=64 time=0.009 ms

--- keqsi2-vip ping statistics ---
4 packets transmitted, 4 received, 0% packet loss, time 2999ms
rtt min/avg/max/mdev = 0.009/0.012/0.018/0.003 ms, pipe 2
[oracle@keqsi2 admin]$ ping keqsi1-vip
PING keqsi1-vip (10.53.1.237) 56(84) bytes of data.
64 bytes from keqsi1-vip (10.53.1.237): icmp_seq=0 ttl=64 time=0.135 ms
64 bytes from keqsi1-vip (10.53.1.237): icmp_seq=1 ttl=64 time=0.129 ms
64 bytes from keqsi1-vip (10.53.1.237): icmp_seq=2 ttl=64 time=0.121 ms

--- keqsi1-vip ping statistics ---
3 packets transmitted, 3 received, 0% packet loss, time 2000ms
rtt min/avg/max/mdev = 0.121/0.128/0.135/0.010 ms, pipe 2

经上面的命令检查网络没有问题

下面手功启动listener还是报TNS-12545

[oracle@keqsi2 admin]$ srvctl start listener -n keqsi2
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 28-AUG-2013 08:26:14
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:Copyright (c) 1991, 2007, Oracle.  All rights reserved.
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:Starting /u01/app/oracle/10gR2/db/bin/tnslsnr: please wait...
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:TNSLSNR for Linux: Version 10.2.0.4.0 - Production
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:System parameter file is /u01/app/oracle/10gR2/db/network/admin/listener.ora
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:Log messages written to /u01/app/oracle/10gR2/db/network/log/listener_keqsi2.log
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.53.1.237)(PORT=1521)(IP=FIRST)))
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:TNS-12545: Connect failed because target host or object does not exist
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr: TNS-12560: TNS:protocol adapter error
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:  TNS-00515: Connect failed because target host or object does not exist
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:   Linux Error: 99: Cannot assign requested address
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:Listener failed to start. See the error message(s) above...
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 28-AUG-2013 08:26:14
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:Copyright (c) 1991, 2007, Oracle.  All rights reserved.
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.53.1.237)(PORT=1521)(IP=FIRST)))
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:TNS-12541: TNS:no listener
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr: TNS-12560: TNS:protocol adapter error
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:  TNS-00511: No listener
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:   Linux Error: 111: Connection refused
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.53.1.238)(PORT=1521)(IP=FIRST)))
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:TNS-12541: TNS:no listener
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr: TNS-12560: TNS:protocol adapter error
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:  TNS-00511: No listener
keqsi2:ora.keqsi2.LISTENER_KEQSI2.lsnr:   Linux Error: 111: Connection refused
CRS-0215: Could not start resource 'ora.keqsi2.LISTENER_KEQSI2.lsnr'.

下面让各个节点的监听程序只监听自己节点的vip

[oracle@keqsi2 admin]$ vi listener.ora
   
# listener.ora.keqsi2 Network Configuration File: /u01/app/oracle/10gR2/db/network/admin/listener.ora.keqsi2
# Generated by Oracle configuration tools.

LISTENER_KEQSI2 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.53.1.238)(PORT = 1521)(IP = FIRST))
    )
  )

SID_LIST_LISTENER_KEQSI2 =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/10gR2/db)
      (PROGRAM = extproc)
    )
  )

~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
"listener.ora" 19L, 474C written

[oracle@keqsi1 admin]$ vi listener.ora

# listener.ora.keqsi1 Network Configuration File: /u01/app/oracle/10gR2/db/network/admin/listener.ora.keqsi1
# Generated by Oracle configuration tools.

LISTENER_KEQSI1 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST =10.53.1.237)(PORT = 1521)(IP = FIRST))
    )
  )

SID_LIST_LISTENER_KEQSI1 =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/10gR2/db)
      (PROGRAM = extproc)
    )
  )

~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
~
"listener.ora" 19L, 473C written

启动监听程序正常

[oracle@keqsi2 admin]$ lsnrctl start LISTENER_KEQSI2

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 28-AUG-2013 08:42:17

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Starting /u01/app/oracle/10gR2/db/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.4.0 - Production
System parameter file is /u01/app/oracle/10gR2/db/network/admin/listener.ora
Log messages written to /u01/app/oracle/10gR2/db/network/log/listener_keqsi2.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.53.1.238)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.53.1.238)(PORT=1521)(IP=FIRST)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_KEQSI2
Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date                28-AUG-2013 08:42:17
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/10gR2/db/network/admin/listener.ora
Listener Log File         /u01/app/oracle/10gR2/db/network/log/listener_keqsi2.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.53.1.238)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully


[oracle@keqsi1 admin]$ lsnrctl start LISTENER_KEQSI1

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 28-AUG-2013 08:37:08

Copyright (c) 1991, 2007, Oracle.  All rights reserved.

Starting /u01/app/oracle/10gR2/db/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.4.0 - Production
System parameter file is /u01/app/oracle/10gR2/db/network/admin/listener.ora
Log messages written to /u01/app/oracle/10gR2/db/network/log/listener_keqsi1.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.53.1.237)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.53.1.237)(PORT=1521)(IP=FIRST)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER_KEQSI1
Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production
Start Date                28-AUG-2013 08:37:08
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/10gR2/db/network/admin/listener.ora
Listener Log File         /u01/app/oracle/10gR2/db/network/log/listener_keqsi1.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.53.1.237)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully


[oracle@keqsi1 admin]$ crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora.keqjm.db   application    ONLINE    ONLINE    keqsi1
ora....m1.inst application    ONLINE    ONLINE    keqsi1
ora....m2.inst application    ONLINE    ONLINE    keqsi2
ora....orcl.cs application    ONLINE    ONLINE    keqsi2
ora....jm1.srv application    ONLINE    ONLINE    keqsi1
ora....jm2.srv application    ONLINE    ONLINE    keqsi2
ora....SM1.asm application    ONLINE    ONLINE    keqsi1
ora....I1.lsnr application    ONLINE    ONLINE    keqsi1
ora.keqsi1.gsd application    ONLINE    ONLINE    keqsi1
ora.keqsi1.ons application    ONLINE    ONLINE    keqsi1
ora.keqsi1.vip application    ONLINE    ONLINE    keqsi1
ora....SM2.asm application    ONLINE    ONLINE    keqsi2
ora....I2.lsnr application    ONLINE    ONLINE    keqsi2
ora.keqsi2.gsd application    ONLINE    ONLINE    keqsi2
ora.keqsi2.ons application    ONLINE    ONLINE    keqsi2
ora.keqsi2.vip application    ONLINE    ONLINE    keqsi2

然后再重新启动rac数据库能正常启动

[oracle@keqsi1 admin]$ crs_stop -all
Attempting to stop `ora.keqjm.orcl.keqjm1.srv` on member `keqsi1`
Attempting to stop `ora.keqsi1.gsd` on member `keqsi1`
Stop of `ora.keqjm.orcl.keqjm1.srv` on member `keqsi1` succeeded.
Attempting to stop `ora.keqjm.orcl.keqjm2.srv` on member `keqsi2`
Attempting to stop `ora.keqjm.db` on member `keqsi1`
Attempting to stop `ora.keqsi1.ons` on member `keqsi1`
Attempting to stop `ora.keqjm.orcl.cs` on member `keqsi2`
Attempting to stop `ora.keqsi2.gsd` on member `keqsi2`
Attempting to stop `ora.keqsi2.ons` on member `keqsi2`
Stop of `ora.keqjm.orcl.keqjm2.srv` on member `keqsi2` succeeded.
Stop of `ora.keqsi1.gsd` on member `keqsi1` succeeded.
Stop of `ora.keqsi1.ons` on member `keqsi1` succeeded.
Stop of `ora.keqsi2.gsd` on member `keqsi2` succeeded.
Stop of `ora.keqsi2.ons` on member `keqsi2` succeeded.
Stop of `ora.keqjm.orcl.cs` on member `keqsi2` succeeded.
Stop of `ora.keqjm.db` on member `keqsi1` succeeded.
`ora.keqjm.keqjm1.inst` is already OFFLINE.
`ora.keqjm.keqjm2.inst` is already OFFLINE.
Attempting to stop `ora.keqsi1.ASM1.asm` on member `keqsi1`
Attempting to stop `ora.keqsi2.ASM2.asm` on member `keqsi2`
Attempting to stop `ora.keqsi1.LISTENER_KEQSI1.lsnr` on member `keqsi1`
Attempting to stop `ora.keqsi2.LISTENER_KEQSI2.lsnr` on member `keqsi2`
Stop of `ora.keqsi2.LISTENER_KEQSI2.lsnr` on member `keqsi2` succeeded.
Attempting to stop `ora.keqsi2.vip` on member `keqsi2`
Stop of `ora.keqsi1.LISTENER_KEQSI1.lsnr` on member `keqsi1` succeeded.
Attempting to stop `ora.keqsi1.vip` on member `keqsi1`
Stop of `ora.keqsi2.vip` on member `keqsi2` succeeded.
Stop of `ora.keqsi1.ASM1.asm` on member `keqsi1` succeeded.
Stop of `ora.keqsi1.vip` on member `keqsi1` succeeded.
Stop of `ora.keqsi2.ASM2.asm` on member `keqsi2` succeeded.
CRS-0216: Could not stop resource 'ora.keqjm.keqjm1.inst'.

CRS-0216: Could not stop resource 'ora.keqjm.keqjm2.inst'.

[oracle@keqsi1 admin]$ crs_start -all
Attempting to start `ora.keqsi1.ASM1.asm` on member `keqsi1`
Attempting to start `ora.keqsi1.vip` on member `keqsi1`
Attempting to start `ora.keqsi2.ASM2.asm` on member `keqsi2`
Attempting to start `ora.keqsi2.vip` on member `keqsi2`
Start of `ora.keqsi1.vip` on member `keqsi1` succeeded.
Attempting to start `ora.keqsi1.LISTENER_KEQSI1.lsnr` on member `keqsi1`
Start of `ora.keqsi2.vip` on member `keqsi2` succeeded.
Attempting to start `ora.keqsi2.LISTENER_KEQSI2.lsnr` on member `keqsi2`
Start of `ora.keqsi1.LISTENER_KEQSI1.lsnr` on member `keqsi1` succeeded.
Start of `ora.keqsi2.LISTENER_KEQSI2.lsnr` on member `keqsi2` succeeded.
Start of `ora.keqsi1.ASM1.asm` on member `keqsi1` succeeded.
Attempting to start `ora.keqjm.keqjm1.inst` on member `keqsi1`
Start of `ora.keqsi2.ASM2.asm` on member `keqsi2` succeeded.
Attempting to start `ora.keqjm.keqjm2.inst` on member `keqsi2`
Start of `ora.keqjm.keqjm1.inst` on member `keqsi1` succeeded.
Start of `ora.keqjm.keqjm2.inst` on member `keqsi2` succeeded.
CRS-1002: Resource 'ora.keqsi1.ons' is already running on member 'keqsi1'

CRS-1002: Resource 'ora.keqsi2.ons' is already running on member 'keqsi2'

CRS-1002: Resource 'ora.keqjm.db' is already running on member 'keqsi2'

Attempting to start `ora.keqjm.orcl.cs` on member `keqsi2`
Attempting to start `ora.keqsi1.gsd` on member `keqsi1`
Attempting to start `ora.keqjm.orcl.keqjm1.srv` on member `keqsi1`
Attempting to start `ora.keqjm.orcl.keqjm2.srv` on member `keqsi2`
Attempting to start `ora.keqsi2.gsd` on member `keqsi2`
Start of `ora.keqjm.orcl.cs` on member `keqsi2` succeeded.
Start of `ora.keqjm.orcl.keqjm1.srv` on member `keqsi1` succeeded.
Start of `ora.keqsi1.gsd` on member `keqsi1` succeeded.
Start of `ora.keqjm.orcl.keqjm2.srv` on member `keqsi2` succeeded.
Start of `ora.keqsi2.gsd` on member `keqsi2` succeeded.
CRS-0223: Resource 'ora.keqjm.db' has placement error.

CRS-0223: Resource 'ora.keqsi1.ons' has placement error.

CRS-0223: Resource 'ora.keqsi2.ons' has placement error.

[oracle@keqsi1 admin]$ crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora.keqjm.db   application    ONLINE    ONLINE    keqsi2
ora....m1.inst application    ONLINE    ONLINE    keqsi1
ora....m2.inst application    ONLINE    ONLINE    keqsi2
ora....orcl.cs application    ONLINE    ONLINE    keqsi2
ora....jm1.srv application    ONLINE    ONLINE    keqsi1
ora....jm2.srv application    ONLINE    ONLINE    keqsi2
ora....SM1.asm application    ONLINE    ONLINE    keqsi1
ora....I1.lsnr application    ONLINE    ONLINE    keqsi1
ora.keqsi1.gsd application    ONLINE    ONLINE    keqsi1
ora.keqsi1.ons application    ONLINE    ONLINE    keqsi1
ora.keqsi1.vip application    ONLINE    ONLINE    keqsi1
ora....SM2.asm application    ONLINE    ONLINE    keqsi2
ora....I2.lsnr application    ONLINE    ONLINE    keqsi2
ora.keqsi2.gsd application    ONLINE    ONLINE    keqsi2
ora.keqsi2.ons application    ONLINE    ONLINE    keqsi2
ora.keqsi2.vip application    ONLINE    ONLINE    keqsi2
[oracle@keqsi1 admin]$

修改数据库中的初始化参数LOCAL_LISTENER和REMOTE_LISTENER

oracle@keqsi1 admin ]$ sqlplus  / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Aug 28 00:26:39 2013

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options


SQL> ALTER SYSTEM
2 SET LOCAL_LISTENER = '(ADDRESS = (PROTOCOL = TCP)(HOST = 10.53.1.237)(PORT = 1521))'
3 SID = 'keqsi1';

系统已更改。


SQL> ALTER SYSTEM
2 SET LOCAL_LISTENER = '(ADDRESS = (PROTOCOL = TCP)(HOST = 10.53.1.238)(PORT = 1521))'
3 SID = 'keqsi2';

系统已更改。
SQL> ALTER SYSTEM
2 SET REMOTE_LISTENER = 'LISTENERS_KEQJM'
3 SID = '*';
系统已更改。

其中’LISTENERS_KEQJM’对应于tnsnames.ora中的LISTENERS_KEQJM
连接串,该参数用于rac的负载均衡

[oracle@keqsi1 admin]$ more tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/10gR2/db/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

KEQJM =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = keqsi1-vip)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = keqsi2-vip)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = keqjm)
    )
  )

KEQJM2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = keqsi2-vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = keqjm)
      (INSTANCE_NAME = keqjm2)
    )
  )

KEQJM1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = keqsi1-vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = keqjm)
      (INSTANCE_NAME = keqjm1)
    )
  )

LISTENERS_KEQJM =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = keqsi1-vip)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = keqsi2-vip)(PORT = 1521))
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = keqsi1-vip)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = keqsi2-vip)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
      (FAILOVER_MODE =
        (TYPE = SELECT)
        (METHOD = BASIC)
        (RETRIES = 180)
        (DELAY = 5)
      )
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
)

下面是测看各个节点只监听自己的vip地址时通过remote_listener能不能实现
rac的负载均衡
在本机测试

[oracle@test admin]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Aug 28 00:31:45 2013

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> conn test/test@keqsi
Connected.

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
keqjm1

SQL>exit

[oracle@test admin]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Aug 28 00:31:45 2013

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> conn test/test@keqsi
Connected.

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
keqjm2

经测试rac是可以负载均衡的

自定义show_space过程来显示数据段的利用信息

SHOW_SPACE过程
SHOW_SPACE例程用于打印数据库段空间利用率信息:

sys@DEVELOP> create or replace procedure show_space
  2  ( p_segname in varchar2,
  3    p_owner in varchar2 default user,
  4    p_type in varchar2 default 'TABLE',
  5    p_partition in varchar2 default NULL )
  6  -- this procedure uses authid current user so it can query DBA_*
  7  -- views using privileges from a ROLE, and so it can be installed
  8  -- once per database, instead of once per user who wanted to use it
  9    authid current_user
 10  as
 11    l_free_blks number;
 12    l_total_blocks number;
 13    l_total_bytes number;
 14    l_unused_blocks number;
 15    l_unused_bytes number;
 16    l_LastUsedExtFileId number;
 17    l_LastUsedExtBlockId number;
 18    l_LAST_USED_BLOCK number;
 19    l_segment_space_mgmt varchar2(255);
 20    l_unformatted_blocks number;
 21    l_unformatted_bytes number;
 22    l_fs1_blocks number; l_fs1_bytes number;
 23    l_fs2_blocks number; l_fs2_bytes number;
 24    l_fs3_blocks number; l_fs3_bytes number;
 25    l_fs4_blocks number; l_fs4_bytes number;
 26    l_full_blocks number; l_full_bytes number;
 27  -- inline procedure to print out numbers nicely formatted
 28  -- with a simple label
 29  procedure p( p_label in varchar2, p_num in number )
 30  is
 31  begin
 32    dbms_output.put_line( rpad(p_label,40,'.') ||
 33    to_char(p_num,'999,999,999,999') );
 34  end;
 35  begin
 36  -- this query is executed dynamically in order to allow this procedure
 37  -- to be created by a user who has access to DBA_SEGMENTS/TABLESPACES
 38  -- via a role as is customary.
 39  -- NOTE: at runtime, the invoker MUST have access to these two
-- views!
 40   41  -- this query determines if the object is an ASSM object or not
 42    begin
 43      execute immediate
 44      'select ts.segment_space_management
 45      from dba_segments seg, dba_tablespaces ts
 46      where seg.segment_name = :p_segname
    and (:p_partition is null or
 47   48      seg.partition_name = :p_partition)
 49      and seg.owner = :p_owner
 50      and seg.tablespace_name = ts.tablespace_name'
 51      into l_segment_space_mgmt
 52      using p_segname, p_partition, p_partition, p_owner;
 53    exception
 54    when too_many_rows then
 55      dbms_output.put_line
 56        ( 'This must be a partitioned table, use p_partition => ');
 57      return;
 58    end;
 59  -- if the object is in an ASSM tablespace, we must use this API
 60  -- call to get space information, otherwise we use the FREE_BLOCKS
 61  -- API for the user-managed segments
 62    if l_segment_space_mgmt = 'AUTO'   then
 63       dbms_space.space_usage
 64       ( p_owner, p_segname, p_type, l_unformatted_blocks,
 65       l_unformatted_bytes, l_fs1_blocks, l_fs1_bytes,
 66       l_fs2_blocks, l_fs2_bytes, l_fs3_blocks, l_fs3_bytes,
 67       l_fs4_blocks, l_fs4_bytes, l_full_blocks, l_full_bytes, p_partition);
 68       p( 'Unformatted Blocks ', l_unformatted_blocks );
 69       p( 'FS1 Blocks (0-25) ', l_fs1_blocks );
 70       p( 'FS2 Blocks (25-50) ', l_fs2_blocks );
 71       p( 'FS3 Blocks (50-75) ', l_fs3_blocks );
 72       p( 'FS4 Blocks (75-100)', l_fs4_blocks );
 73       p( 'Full Blocks ', l_full_blocks );
 74    else
 75       dbms_space.free_blocks(
 76       segment_owner => p_owner,
 77       segment_name => p_segname,
 78       segment_type => p_type,
 79       freelist_group_id => 0,
 80       free_blks => l_free_blks);
 81       p( 'Free Blocks', l_free_blks );
 82    end if;
 83  -- and then the unused space API call to get the rest of the
 84  -- information
 85    dbms_space.unused_space
 86     ( segment_owner => p_owner,
 87       segment_name => p_segname,
 88       segment_type => p_type,
 89       partition_name => p_partition,
 90       total_blocks => l_total_blocks,
 91       total_bytes => l_total_bytes,
 92       unused_blocks => l_unused_blocks,
 93       unused_bytes => l_unused_bytes,
 94       LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
 95       LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
 96       LAST_USED_BLOCK => l_LAST_USED_BLOCK );
 97       p( 'Total Blocks', l_total_blocks );
 98       p( 'Total Bytes', l_total_bytes );
 99       p( 'Total MBytes', trunc(l_total_bytes/1024/1024) );
100       p( 'Unused Blocks', l_unused_blocks );
101       p( 'Unused Bytes', l_unused_bytes );
102       p( 'Last Used Ext FileId', l_LastUsedExtFileId );
103       p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
104       p( 'Last Used Block', l_LAST_USED_BLOCK );
105  end;
106  /

Procedure created.

show_space过程包含以下参数:

sys@DEVELOP> desc show_space
PROCEDURE show_space
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_SEGNAME                      VARCHAR2                IN
 P_OWNER                        VARCHAR2                IN     DEFAULT
 P_TYPE                         VARCHAR2                IN     DEFAULT
 P_PARTITION                    VARCHAR2                IN     DEFAULT

参数如下:
• P_SEGNAME:段名(例如,表或索引名)。
• P_OWNER:默认为当前用户,不过也可以使用这个例程查看另外某个用户。
• P_TYPE:默认为TABLE,这个参数表示查看哪种类型的对象(段)。例如,SELECT DISTINCT SEGMENT_TYPE FROM DBA_SEGMENTS
会列出合法的段类型。
• P_PARTITION:显示分区对象的空间时所用的分区名。SHOW_SPACE一次只显示一个分区的空间利用率。

这个过程的输出如下,这里段位于一个自动段空间管理(Automatic Segment Space Management, ASSM)表空间中:

sys@DEVELOP> exec show_space('AC01','HNSIC');
Unformatted Blocks .....................               0
FS1 Blocks (0-25) ......................               1
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................              12
Full Blocks ............................          16,305
Total Blocks............................         146,304
Total Bytes.............................   1,198,522,368
Total MBytes............................           1,143
Unused Blocks...........................         129,920
Unused Bytes............................   1,064,304,640
Last Used Ext FileId....................              10
Last Used Ext BlockId...................         348,800
Last Used Block.........................           8,192

PL/SQL procedure successfully completed.

报告的各项结果说明如下:
• Unformatted Blocks:为表分配的位于高水位线(high-water mark, HWM)之下但未用的块数。把未格式化和未用的块加在一起,就是已为表分配但从未用于保存ASSM对象数据的总块数。
• FS1 Blocks-FS4 Blocks:包含数据的格式化块。项名后的数字区间表示各块的“空闲度”。例如,(0-25)是指空闲度为
0~25%的块数。
• Full Blocks:已满的块数,不能再对这些执行插入。
• Total Blocks、Total bytes、Total Mbytes:为所查看的段分配的总空间量,单位分别是数据库块、字节和兆字节。
• Unused Blocks、Unused Bytes:表示未用空间所占的比例(未用空间量)。这些块已经分配给所查看的段,但目前在段的HWM之上。
• Last Used Ext FileId:最后使用的文件的文件ID,该文件包含最后一个含数据的区段(extent)。
• Last Used Ext BlockId:最后一个区段开始处的块ID;这是最后使用的文件中的块ID。
• Last Used Block:最后一个区段中最后一个块的偏移量。

如果对象在用户空间管理的表空间中,使用SHOW_SPACE查看时,输出如下:

sys@DEVELOP> exec show_space('T1');
Free Blocks.............................               3
Total Blocks............................          17,408
Total Bytes.............................     142,606,336
Total MBytes............................             136
Unused Blocks...........................             869
Unused Bytes............................       7,118,848
Last Used Ext FileId....................               1
Last Used Ext BlockId...................       1,696,896
Last Used Block.........................             155

PL/SQL procedure successfully completed.

这里惟一的区别是报告中最前面的Free Blocks项。这是段的第一个freelist(自由列表)组中的块数。
脚本只测试了第一个freelist组。如果想测试多个freelist组,还需要修改这个脚本。

自定义统计结果脚本mystat

mystat.sql和相应的mystat2.sql用于展示完成某操作之前和之后的某些Oracle“统计结果”的变化情况。mystat.sql只是获得统计结果的开始值:
mystat.sql脚本内容如下:

set echo off
set verify off
column value new_val V
define S="&1"
set autotrace off
select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like '%' || lower('&S')||'%'
/
set echo on

mystat2.sql用于报告统计结果的变化情况(差值):

set echo off
set verify off
select a.name, b.value V, to_char(b.value-&V,'999,999,999,999') diff
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like '%' || lower('&S')||'%'
/
set echo on

例如,要查看某个UPDATE生成的redo数,可以使用以下命令:

sys@DEVELOP> @/oracle/product/11.2/sqlplus/mystat.sql "redo size"

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                         756293676
redo size for lost write detection                                        0
redo size for direct writes                                               0

sys@DEVELOP> update t1 set aac009='1';

795680 rows updated.

sys@DEVELOP> @/oracle/product/11.2/sqlplus/mystat2.sql

NAME                                                                      V DIFF
---------------------------------------------------------------- ---------- ----------------
redo size                                                        1139306496    1,139,306,496
redo size for lost write detection                                        0                0
redo size for direct writes                                               0                0

由此可见,795,680行记录的UPDATE会生成1,139,306,496字节的redo.

自定义性能统计存储过程包runstats

runstats是一个工具,能对做同一件事的两个不同方法进行比较,得出孰优孰劣的结果。只需提供两个不同的方法, 余下的事情都由runstats负责。runstats只是测量3个要素:
? 耗用时间(elapsed time):知道耗用时间很有用,不过这不是最重要的信息。
? 系统统计结果:会并排显示每个方法做某件事(如执行一个解析调用)的次数,并展示二者之差。
? 闩锁(latching):这是这个报告的关键输出。
闩锁(latch)是一种轻量级的锁。锁(lock)是一种串行化设备,而串行化设备不支持并发。如果应用不支 持并发,可扩缩性就比较差,只能支持较少的用户,而且需要更多的资源。构建应用时,我们往往希望应用能很好地扩缩,也就是说,为1位用户服务与为1,000或10,000位用户服务应该是一样的。应用中使用的闩锁越少,性能就越好。如果一种方法从耗用时间来看运行时间较长,但是只使用了另一种方法10%的闩锁,我可能会选择前者。因为与使用更多闩锁的方法相比,使用较少闩锁的方法能更好地扩缩。
runstats最后独立使用,也就是说,最好在一个单用户数据库上运行。它会测量各个方法的统计结果和闩锁(锁定)活动。 runstats在运行过程中,不希望其他任务对系统的负载或闩锁产生影响。只需一个小的测试数据库就能很好地完成这些测试。要使用runstats,需要能访问几个V$视图,并创建一个表来存储统计结果,还要创建runstats包。为此,需要访问3个V$表:V$STATNAME、V$MYSTAT和V$LATCH。以下是我使用的视图:

create or replace view stats
as select 'STAT...' || a.name name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
union all
select 'LATCH.' || name, gets
from v$latch;

如果你能得到V$STATNAME、V$MYSTAT、V$LATCH和V$TIMER的直接授权,就能直接对这些表执行SELECT操作(相应地可以自行创建 视图);否则,可以由其他人对这些表执行SELECT操作为你创建视图,并授予你在这个视图上执行SELECT的权限。
一旦建立视图,接下来只需要一个小表来收集统计结果:

sys@DEVELOP> create global temporary table run_stats
  2  ( runid varchar2(15),
  3  name varchar2(80),
  4  value int )
  5  on commit preserve rows;

Table created.

最后,需要创建runstats包。其中包含3个简单的API调用:
runstats测试开始时调用RS_STAT(runstats开始)。
正如你想象的,RS_MIDDLE会在测试之间调用。
完成时调用RS_STOP,打印报告。
创建runstats包的规范如下:

sys@DEVELOP> create or replace package runstats_pkg
  2  as
  3  procedure rs_start;
  4  procedure rs_middle;
  5  procedure rs_stop(p_difference_threshold in number default 0);
  6  end;
  7  /

Package created.

参数P_DIFFERENCE_THRESHOLD用于控制最后打印的数据量。runstats会收集并得到每次运行的统计结果和闩信息,然后打印一个 报告,说明每次测试(每个方法)使用了多少资源,以及不同测试(不同方法)的结果之差。可以使用这个输入参数来控制只查 看差值大于这个数的统计结果和闩信息。由于这个参数默认为0,所以默认情况下可以看到所有输出。
下面我们逐一分析包体中的过程。包前面是一些全局变量,这些全局变量用于记录每次运行的耗用时间:
RS_START过程。这个过程只是清空保存统计结果的表,并填入“上一次”(before)得到的统计结果和闩信息。然后获得 当前定时器值,这是一种时钟,可用于计算耗用时间(单位百分之一秒):

接下来是RS_MIDDLE过程。这个过程只是把第一次测试运行的耗用时间记录在G_RUN1中。然后插入当前的一组统计结果和闩信息。 如果把这些值与先前在RS_START中保存的值相减,就会发现第一个方法使用了多少闩,以及使用了多少游标(一种统计结果), 等等。
最后,记录下一次运行的开始时间:

完整的包体创建语句如下:

sys@DEVELOP> create or replace package body runstats_pkg
  2   as
  3
  4   g_start number;
  5   g_run1 number;
  6   g_run2 number;
  7
  8   procedure rs_start
  9   is
 10   begin
 11     delete from run_stats;
 12
 13     insert into run_stats
 14     select 'before', stats.* from stats;
 15
 16     g_start := dbms_utility.get_time;
 17   end;
 18
 19   procedure rs_middle
 20   is
 21   begin
 22     g_run1 := (dbms_utility.get_time-g_start);
 23
 24     insert into run_stats
 25     select 'after 1', stats.* from stats;
 26     g_start := dbms_utility.get_time;
 27
 28   end;
 29
 30   procedure rs_stop(p_difference_threshold in number default 0)
 31   is
 32   begin
 33     g_run2 := (dbms_utility.get_time-g_start);
 34
 35     dbms_output.put_line
 36       ( 'Run1 ran in ' || g_run1 || ' hsecs' );
 37     dbms_output.put_line
 38       ( 'Run2 ran in ' || g_run2 || ' hsecs' );
 39     dbms_output.put_line
 40       ( 'run 1 ran in ' || round(g_run1/g_run2*100,2) ||
 41       '% of the time' );
 42     dbms_output.put_line( chr(9) );
 43
 44     insert into run_stats
 45     select 'after 2', stats.* from stats;
 46
 47     dbms_output.put_line
 48       ( rpad( 'Name', 30 ) || lpad( 'Run1', 10 ) ||
 49       lpad( 'Run2', 10 ) || lpad( 'Diff', 10 ) );
 50
 51     for x in
 52       ( select rpad( a.name, 30 ) ||
 53         to_char( b.value-a.value, '9,999,999' ) ||
 54         to_char( c.value-b.value, '9,999,999' ) ||
 55         to_char( ( (c.value-b.value)-(b.value-a.value)), '9,999,999' ) data
 56         from run_stats a, run_stats b, run_stats c
 57         where a.name = b.name
 58         and b.name = c.name
 59         and a.runid = 'before'
 60         and b.runid = 'after 1'
 61         and c.runid = 'after 2'
 62         and (c.value-a.value) > 0
 63         and abs( (c.value-b.value) - (b.value-a.value) )
 64         > p_difference_threshold
 65         order by abs( (c.value-b.value)-(b.value-a.value))
 66       ) loop
 67       dbms_output.put_line( x.data );
 68     end loop;
 69
 70     dbms_output.put_line( chr(9) );
 71     dbms_output.put_line
 72       ( 'Run1 latches total versus runs -- difference and pct' );
 73     dbms_output.put_line
 74       ( lpad( 'Run1', 10 ) || lpad( 'Run2', 10 ) ||
 75         lpad( 'Diff', 10 ) || lpad( 'Pct', 8 ) );
 76
 77     for x in
 78       ( select to_char( run1, '9,999,999' ) ||
 79       to_char( run2, '9,999,999' ) ||
 80       to_char( diff, '9,999,999' ) ||
 81       to_char( round( run1/run2*100,2 ), '999.99' ) || '%' data
 82       from ( select sum(b.value-a.value) run1, sum(c.value-b.value) run2,
 83       sum( (c.value-b.value)-(b.value-a.value)) diff
 84       from run_stats a, run_stats b, run_stats c
 85       where a.name = b.name
 86       and b.name = c.name
 87       and a.runid = 'before'
 88       and b.runid = 'after 1'
 89       and c.runid = 'after 2'
 90       and a.name like 'LATCH%'
 91          )
 92       ) loop
 93       dbms_output.put_line( x.data );
 94     end loop;
 95   end;
 96
 97  end;
 98   /

Package body created.

下面可以使用runstats了。我们将通过例子来说明如何使用runstats对批量插入(INSERT)和逐行处理进行比较,
看看哪种方法效率更高。首先建立两个表,要在其中插入795,680行记录:

sys@DEVELOP> create table t1 as select * from hnsic.ac01 where 1=0;

Table created.

sys@DEVELOP> create table t2 as select * from hnsic.ac01 where 1=0;

Table created.

接下来使用第一种方法插入记录,也就是使用单独一条SQL语句完成批量插入。首先调用RUNSTATS_PKG.RS_START:

sys@DEVELOP> exec runstats_pkg.rs_start;

PL/SQL procedure successfully completed.

sys@DEVELOP> insert into t1 select * from hnsic.ac01;

795680 rows created.

sys@DEVELOP> commit;

Commit complete.

下面准备执行第二种方法,即逐行地插入数据:

sys@DEVELOP> exec runstats_pkg.rs_middle;

PL/SQL procedure successfully completed.

sys@DEVELOP> begin
  2   for x in (select * from hnsic.ac01)
  3   loop
  4     insert into t2 values x;
  5   end loop;
  6   commit;
  7  end;
  8  /

PL/SQL procedure successfully completed.

最后生成报告:

sys@DEVELOP> exec runstats_pkg.rs_stop;
Run1 ran in 4835 hsecs
Run2 ran in 14497 hsecs
run 1 ran in 33.35% of the time

Name                                                                                            Run1              

  Run2
Diff
LATCH.space background state object latch                                                1         0        -1
LATCH.file cache latch                                                                  93        94         1
LATCH.ktm global data                                                                    5         4        -1
LATCH.KTF sga latch                                                                      1         0        -1
LATCH.dml lock allocation                                                                1         2         1
LATCH.tablespace key chain                                                               1         2         1
LATCH.deferred cleanup latch                                                             1         2         1
LATCH.kcbtsemkid latch                                                                   2         1        -1
LATCH.threshold alerts latch                                                             1         2         1
LATCH.cp sga latch                                                                       1         2         1
LATCH.hash table modification latch                                                      1         2         1
STAT...parse count (hard)                                                                1         2         1
STAT...parse count (total)                                                               5         4        -1
STAT...sql area evicted                                                                  1         0        -1
LATCH.ASM network state latch                                                            1         2         1
STAT...write clones created in foreground                                                3         2        -1
STAT...shared hash latch upgrades - no wait                                             93        94         1
LATCH.ncodef allocation latch                                                            1         2         1
STAT...deferred (CURRENT) block cleanout applications                                    1         2         1
STAT...commit txn count during cleanout                                                  0         1         1
STAT...IMU Flushes                                                                       0         1         1
STAT...table fetch by rowid                                                             94        95         1
STAT...index scans kdiixs1                                                              94        95         1
STAT...Requests to/from client                                                           6         4        -2
STAT...session cursor cache count                                                        5         3        -2
STAT...redo log space wait time                                                          2         0        -2
STAT...redo log space requests                                                           4         2        -2
LATCH.ksv class latch                                                                    3         5         2
STAT...non-idle wait time                                                                2         0        -2
STAT...calls to get snapshot scn: kcmgss                                               184       186         2
STAT...SQL*Net roundtrips to/from client                                                 6         4        -2
LATCH.MinActiveScn Latch                                                                 0         2         2
STAT...db block gets from cache (fastpath)                                             103       105         2
LATCH.ksv allocation latch                                                               4         6         2
STAT...redo entries                                                                849,543   849,545         2
LATCH.message pool operations parent latch                                               4         1        -3
STAT...parse time cpu                                                                    1         4         3
STAT...user calls                                                                        9         6        -3
LATCH.kwqbsn:qsga                                                                        2         5         3
LATCH.Shared B-Tree                                                                      3         6         3
LATCH.interrupt manipulation                                                             1         5         4
LATCH.SQL memory manager latch                                                           1         5         4
LATCH.kokc descriptor allocation latch                                                   2         6         4
STAT...redo buffer allocation retries                                                    5         1        -4
LATCH.multiblock read objects                                                            6         2        -4
LATCH.object stats modification                                                          8        13         5
STAT...enqueue releases                                                              3,711     3,716         5
STAT...enqueue requests                                                              3,710     3,716         6
STAT...redo subscn max counts                                                       23,411    23,405        -6
LATCH.corrupted undo seg latch                                                          18        12        -6
STAT...heap block compress                                                               7         0        -7
STAT...free buffer requested                                                        23,406    23,414         8
LATCH.ksuosstats global area                                                             5        14         9
STAT...calls to kcmgas                                                              10,299    10,289       -10
STAT...redo ordering marks                                                          10,201    10,191       -10
STAT...calls to kcmgcs                                                              33,174    33,187        13
LATCH.loader state object freelist                                                      14        28        14
LATCH.qmn task queue latch                                                              15        30        15
LATCH.job_queue_processes parameter latch                                               11        31        20
LATCH.parallel query alloc buffer                                                        7        31        24
STAT...consistent changes                                                               31        56        25
STAT...db block gets from cache                                                    870,280   870,307        27
STAT...db block gets                                                               870,280   870,307        27
STAT...db block changes                                                          1,672,435 1,672,465        30
STAT...active txn count during cleanout                                             16,027    15,995       -32
STAT...cleanout - number of ktugct calls                                            16,027    15,995       -32
STAT...consistent gets - examination                                                16,032    16,000       -32
LATCH.session timer                                                                     16        48        32
LATCH.Change Notification Hash table latch                                              16        48        32
LATCH.KMG MMAN ready and startup request latch                                          16        49        33
STAT...workarea memory allocated                                                        40        -5       -45
STAT...messages sent                                                                   370       322       -48
LATCH.sort extent pool                                                                   2        61        59
STAT...undo change vector size                                                  ####################        60
LATCH.ASM db client latch                                                               36        99        63
LATCH.simulator lru latch                                                            6,903     6,977        74
LATCH.parameter list                                                                    36       125        89
LATCH.FOB s.o list latch                                                                 6        97        91
LATCH.active checkpoint queue latch                                                    143       237        94
LATCH.global tx hash mapping                                                            19       129       110
LATCH.transaction branch allocation                                                     32       162       130
LATCH.space background task latch                                                       89       270       181
LATCH.post/wait queue                                                                   68       251       183
LATCH.SGA IO buffer pool latch                                                         193       442       249
STAT...change write time                                                             1,393     1,648       255
LATCH.Real-time plan statistics latch                                                  383       763       380
LATCH.shared pool simulator                                                            106       508       402
STAT...bytes sent via SQL*Net to client                                              1,438       948      -490
LATCH.JS queue state obj latch                                                         360     1,044       684
STAT...bytes received via SQL*Net from client                                        2,313     1,623      -690
LATCH.lgwr LWN SCN                                                                     401     1,402     1,001
LATCH.Consistent RBA                                                                   399     1,401     1,002
LATCH.session switching                                                                  7     1,009     1,002
LATCH.resmgr:actses change group                                                         9     1,018     1,009
LATCH.resmgr group change latch                                                          7     1,016     1,009
LATCH.compile environment latch                                                          8     1,017     1,009
LATCH.global KZLD latch for mem in SGA                                                   7     1,016     1,009
LATCH.mostly latch-free SCN                                                            408     1,427     1,019
LATCH.PL/SQL warning settings                                                           53     1,086     1,033
LATCH.In memory undo latch                                                             298     1,793     1,495
LATCH.channel handle pool latch                                                         15     2,024     2,009
LATCH.dummy allocation                                                                  14     2,024     2,010
LATCH.ksz_so allocation latch                                                           13     2,023     2,010
LATCH.OS process: request allocation                                                    13     2,023     2,010
LATCH.resmgr:active threads                                                             13     2,023     2,010
LATCH.resmgr:free threads list                                                          13     2,023     2,010
LATCH.process allocation                                                                14     2,024     2,010
LATCH.process group creation                                                            13     2,023     2,010
LATCH.list of block allocation                                                       1,665     3,678     2,013
LATCH.Event Group Locks                                                                 17     2,035     2,018
LATCH.session state list latch                                                          13     2,042     2,029
LATCH.transaction allocation                                                            24     2,079     2,055
STAT...IMU undo allocation size                                                      3,256     1,080    -2,176
LATCH.DML lock allocation                                                               73     2,251     2,178
LATCH.parameter table management                                                        69     2,249     2,180
LATCH.OS process allocation                                                            113     2,315     2,202
LATCH.redo allocation                                                                2,664     5,249     2,585
LATCH.sequence cache                                                                    21     3,048     3,027
LATCH.undo global data                                                              23,400    26,617     3,217
LATCH.active service list                                                              146     3,396     3,250
LATCH.cache buffer handles                                                              42     3,356     3,314
LATCH.redo writing                                                                   1,389     4,733     3,344
LATCH.channel operations parent latch                                                  273     3,729     3,456
STAT...CPU used when call started                                                    2,405     6,330     3,925
STAT...CPU used by this session                                                      2,403     6,330     3,927
STAT...DB time                                                                       2,416     6,351     3,935
LATCH.OS process                                                                        29     4,041     4,012
LATCH.call allocation                                                                   26     4,252     4,226
LATCH.session allocation                                                               200     4,567     4,367
STAT...free buffer inspected                                                         9,080    13,902     4,822
LATCH.messages                                                                       1,938     7,030     5,092
STAT...hot buffers moved to head of LRU                                              3,719     9,033     5,314
STAT...recursive cpu usage                                                               6     5,494     5,488
LATCH.client/application info                                                           52     7,115     7,063
STAT...consistent gets from cache                                                   49,254    57,049     7,795
STAT...consistent gets                                                              49,254    57,049     7,795
STAT...table scan blocks gotten                                                     16,318    24,129     7,811
STAT...no work - consistent read gets                                               16,507    24,320     7,813
STAT...buffer is not pinned count                                                   16,695    24,510     7,815
STAT...session logical reads                                                       919,534   927,356     7,822
STAT...consistent gets from cache (fastpath)                                        33,128    40,954     7,826
LATCH.enqueue hash chains                                                            8,891    17,620     8,729
LATCH.object queue header heap                                                      12,561    21,765     9,204
STAT...session cursor cache hits                                                        97     9,347     9,250
STAT...IMU Redo allocation size                                                        476    11,228    10,752
STAT...redo size                                                                ####################    17,552
LATCH.session idle bit                                                               5,575    23,796    18,221
LATCH.cache buffers lru chain                                                       18,487    42,303    23,816
LATCH.checkpoint queue latch                                                        17,631    49,092    31,461
LATCH.object queue header operation                                                122,486   188,006    65,520
STAT...session uga memory max                                                      123,488    31,848   -91,640
LATCH.simulator hash latch                                                         237,377   443,767   206,390
LATCH.SQL memory manager workarea list latch                                         1,219   237,704   236,485
STAT...session pga memory max                                                            0   262,144   262,144
STAT...execute count                                                                   101   795,782   795,681
STAT...opened cursors cumulative                                                       102   795,789   795,687
STAT...recursive calls                                                                 848   804,498   803,650
LATCH.shared pool                                                                    7,037   884,303   877,266
LATCH.row cache objects                                                             25,994 1,205,408 1,179,414
LATCH.cache buffers chains                                                      #################### 7,690,411
STAT...logical read bytes from cache                                            ##############################

Run1 latches total versus runs -- difference and pct
Run1      Run2      Diff     Pct
##############################  53.64%

PL/SQL procedure successfully completed.

本文参考Expert.Oracle.Database.Architecture.9i.and.10g.Programming.Techniques.and.Solutions

linux系统中的调度周期任务:cron

linux系统中的调度周期任务:cron
主要概念
cron工具用来调度经常重复的任务
crontab命令是编辑crontab文件的一个前端程序
crontab文件使用5个字段来规定计时信息
cron作业中的标准输出会作为邮件寄给用户

执行周期任务
人们经常发现自己会定期执行一些任务.在系统管理中,这些任务包括从/tmp目录下删除旧的,不使用的文件,或者经常
检查记录登录信息的文件以确保其不会变得过大.其他用户可能会有自己的任务,如检查不再使用的大型文件,或者查看
网站上是否公布了新的信息.

cron工具允许用户配置要定期运行的命令,如每隔十分钟,每周四一次,或每月两次.用户用crontab命令配置自己的任务
计划(cron table),指定何种命令在何时运行.这些任务由传统的linux(和unix)守护进程,即crond守护进程管理.

cron服务
crond守护进程是代表系统或个人用户执行周期任务的守护进程.通常这个守护进程随着系统的启动而启动,因此大多数
用户都不会注意到.通过列出所有进程且搜索crond,你可以确定crond守护进程有没有在运行.

[root@sidatabase /]# ps aux | grep crond
root      3204  0.0  0.0 117204  1368 ?        Ss   Aug09   0:11 crond
root      4687  0.0  0.0 103244   872 pts/0    S+   14:52   0:00 grep crond

如果crond守护进程没有在运行,系统管理员需要以根用户身份来启动crond守护进程.

crontab语法
用户通过配置一个称为”cron table”(经常缩写成”crontab”)的文件指定要运行哪些作业以及何时运行.下面列出了一个
crontab文件的例子.

30 23 * * 6  su - sybx -c "/sydata/app/db/bin/rman target / msglog=/sybak/bak0.log  cmdfile=/sybak/sybx_rman_script/bak0"
30 23 * * 0,1,2,3,4,5  su - sybx -c "/sydata/app/db/bin/rman target / msglog=/sybak/bak1.log  cmdfile=/sybak/sybx_rman_script/bak1"
30 2 * * 0,1,2,3,4,5,6  su - sybx -c "/sydata/app/db/bin/rman target / msglog=/sybak/bakarch.log  cmdfile=/sybak/sybx_rman_script/bakarch"
30 3 * * 0,1,2,3,4,5,6  su - sybx -c "/sydata/app/db/bin/rman target / msglog=/sybak/delbackup.log  cmdfile=/sybak/sybx_rman_script/delbackup"

crontab文件是一个以行为运行单位的配置文件,每行执行三种功能中的一种:
注释
首字符(非空格)是一个#的行被认为是注释,可忽略.

环境变量
具有name=value格式的所有行被用来定义环境变量

cron命令
其他的任何(非空)行被认为是cron命令,由下面描述的六个字段组成.
cron命令行包括六个用空白分隔的字段.前五个字段用来指定何时运行命令,剩余的第六个字段(包括所有在第五个字
段后的部分)指定要运行的命令.前五个字段指定下列时间信息:

minute      hour   day of month    month(1=January,....)   day of week (0=Sunday,....)    command to run

25          04     1               *                       *                              echo "HI"

前五种字段的每一种都必须含有一个使用下列语法的标记
crontab时间表示语法标记
标记 含义 例子 解释(如果用在第一个字段中)
* 每次 * 每分钟
n 在指定时间 10 在每小时过10分时
n,n,… 在任何指定时间 22,52 在每小时过22分和每小时过52分时
*/n 每隔n次 */15 每隔15分钟(在每个整点,一刻钟,半点,或差一刻整点时)

使用crontab命令
用户很少直接管理自己的crontab文件(甚至不知道crontab文件被保存在哪里),而是使用crontab命令来编辑,列出或者
删除它.
crontab {[-e] | [-l] | [-r]}
crontab file
编辑,列出或删除当胶crontab文件,或者用file取代当前crontab文件.crontab命令行选项释义如下
crontab命令行选项
选项 作用
-e 编辑当前文件
-l 列出当前文件
-r 删除当前文件

直接编辑crontab文件
用户经常用crontab -e 直接编辑自己的crontab文件.crontab命令将把当前crontab配置打开到用户默认的编辑器中.
当用户编辑完文件并退出编辑器时,修改过的文件内容作为新的crontab配置被添加.

默认的编辑器是/bin/vi,然而crontab像其他许多命令一样,检查editor环境变量.如果变理已经被设置,它将会被用来
替代默认编辑器.

环境变量与cron
配置cron作业时,用户应该知道一个微妙的细节.当crond守护进程启动用户命令时,它没有从shell中运行命令,而是
直接对这个命令派生和执行(fork和exec).这有一个重要的含义:启动时被shell配置的任何环境变量或别名(alias),
例如在/etc/profile或.bash_profile中被定义的任何环境变量,不会在cron执行命令时出现.

如果用户想定义一个环境变量,需要在自己的crontab配置中定义该变量.

linux系统中的调度延迟任务:at 命令

linux系统中的调度延迟任务:at 命令
主要概念
at命令可以使命令稍后运行
batch命令可以让命令在机器负载较低的情况下运行
可以直接进入命令,或者以脚本形式提交命令
作业中的标准输出用邮件发送给用户
atq命令和atrm命令用来查看和删除当前的计划任务

linux的守护进程是那些在后台运行的进程,脱离控制终端,执行通常成键盘输入无关的任务.守护进程经常与
网络服务相关联,例如网页服务器(httpd)或ftp服务器(vsftpd).其他守护进程处理系统任务,例如日志守护进程(
syslogd)和电源管理守护进程(apmd).这个主要解释说明两个守护进程:一个允许用户延迟任务(atd);另一个允许
用户在固定间隔时间运行命令(crond).

守护进程像其他任何进程一样,通常作为系统启动序列的一部分被启动,或者由根用户启动.因此,除非你特意寻找
它们,否则可能一直不知道它们的存在.

[root@sidatabase /]# ps aux | grep crond
root      3204  0.0  0.0 117204  1368 ?        Ss   Aug09   0:11 crond
root     21399  0.0  0.0 103244   868 pts/0    S+   14:07   0:00 grep crond
[root@sidatabase /]# ps aux | grep atd
rpcuser   2800  0.0  0.0  23340  1204 ?        Ss   Aug09   0:00 rpc.statd
root      3215  0.0  0.0  21448   464 ?        Ss   Aug09   0:00 /usr/sbin/atd
root     21405  0.0  0.0 103244   872 pts/0    S+   14:07   0:00 grep atd

有些守护进程作为根用户运行,而有些守护进程为了安全起见,则以一个系统用户的身份运行.在上面,crond守护进程
作为根用户运行,而ntpd守护进程则作为系统用户运行,如下所示.

[root@sidatabase /]# ps aux | grep ntpd
root     26538  0.0  0.0 103240   868 ?        14:22   0:00 ntpd -u ntp:ntp -p

atd守护进程
atd守护进程允许用户提交稍后运行的作业,如”at 14:13 “.atd守护进程必须在运行时才能使用,用户可以通过查看
运行的进程列表来确定atd是否在运行.

root@sidatabase /]# ps aux | grep atd
rpcuser   2800  0.0  0.0  23340  1204 ?        Ss   Aug09   0:00 rpc.statd
root      3215  0.0  0.0  21448   548 ?        Ss   Aug09   0:00 /usr/sbin/atd
root     28604  0.0  0.0 103244   872 pts/0    S+   14:24   0:00 grep atd

在上面的输出中第七列指出了与进程相关联的终端.对用户root的grep命令而言,终端是pts/2,这可甬指的网络shell
或X会话中的图形终端.注意,atd守护进程没有相关联的终端.守护进程的一个定义特征是,它结束与启动它的终端之
间的联系.

用at命令提交作业
at命令用来向atd守护进程提交需要在特定时间运行的作业.要运行的命令可以作为脚本提交(用-f命令行选项),也可以
通过标准输入直接输入.命令的标准输出将用电子邮件的形式寄给用户
at [[-f filename] | [-m]] time

规定一天中的时间可以用HH:MM格式,后面附加”am”或”pm”,也可以用”midnight”,”noon”和”teatime”待词语.日期也可以
用好几种格式规定,其中mm/dd/yy

例如要在14:13这个时间生成一个名叫at.txt的文件并在文件中写入”hello I am JingYong”信息

[root@sidatabase /]# echo "hello I am JingYong " > at.txt | at 14:13
job 1 at 2013-08-23 14:13

查看作业

[root@sidatabase /]# atq
1       2013-08-23 14:13 a root

删除作业

[root@sidatabase /]# atrm 1

用batch延迟任务
batch命令与at命令一样,用来延迟任务.与at命令不同的是,batch命令不在特定时间运行,而是等到系统不忙于别的
任务时运行.如果提交作业时机器不繁忙,可以立即运行作业.batch守护进程会监控系统的平均负载(load average)
等待它降到0.8以下,然后开始运行作业任务.

batch命令的语法与at命令的语法一模一样,可以用标准输入规定作业,也可以用-f命令行选项把作业作为batch文件
来提交.如果规定了时间,batch会延迟到指定的时间开始观察机器,那时,atd将开始监控系统的平均负载,并且在系统
不繁忙时运行作业.

在linux系统中在后台以作业形式运行命令

在linux系统中在后台以作业形式运行命令
通过给命令行附加一个”&”字符,任何指定的命令也可以在后台运行.通常,只有那些不需要键盘输入而且不
会生成大量输出的长时间运行的命令才适合在后台运行.当bash shell在后台运行命令时,该命令被称为作
业(job),被分配一个作业号码.

[root@sidatabase oradata]# cp system20130708.dmp / > cp.txt 2> /dev/null &
[1] 20629

在后台启动作业后,bash shell向用户报告了两条信息:第一条是作业号码,出现在方括号中;
第二条是后台作业的进程id.上面的信息说明该作业的作业号码为1,cp命令的进程id为20629

用jobs命令列出当前作业

[root@sidatabase /]# jobs
[1]+  Running                 cp -i system20130708.dmp / > cp.txt 2> /dev/null &  (wd: /oracle/oradata)

他的每个后台作业都和作业号码一起列出.最新操作的作业被作为当前作业,在jobs命令输出中用一个”+”修饰.

用fg命令把作业置于前台
可以用fg内置命令把后台作业置于前台运行.fg命令用作业号作为参数,如果没有提供任何作业号码,将在前台运行当前
作业.

[root@sidatabase oradata]# fg 1
cp -i system20130708.dmp / > cp.txt 2> /dev/null

cp -i system20130708.dmp / > cp.txt 2> /dev/null正在前台运行,因此,当进程仍在运行时,shell不会发送打印提示
符.

用ctrl+z挂起前台作业
ctrl+z控制组合键是挂起进程的一种方法.当用户挂起前台命令时,仔细观察bash shell的输出,会发现bash shell把任何
挂起的前台进程都看成作业.

[root@sidatabase oradata]# fg 1
cp -i system20130708.dmp / > cp.txt 2> /dev/null
^Z
[1]+  Stopped                 cp -i system20130708.dmp / > cp.txt 2> /dev/null
[root@sidatabase oradata]# jobs
[1]+  Stopped                 cp -i system20130708.dmp / > cp.txt 2> /dev/null

[root@sidatabase oradata]# ps u
USER       PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
root      3297  0.0  0.0   4056   544 tty2     Ss+  Aug09   0:00 /sbin/mingetty
root      3299  0.0  0.0   4056   540 tty3     Ss+  Aug09   0:00 /sbin/mingetty
root      3301  0.0  0.0   4056   540 tty4     Ss+  Aug09   0:00 /sbin/mingetty
root      3303  0.0  0.0   4056   540 tty5     Ss+  Aug09   0:00 /sbin/mingetty
root      3305  0.0  0.0   4056   544 tty6     Ss+  Aug09   0:00 /sbin/mingetty
root      3345  0.0  0.0 129680 25964 tty1     Ss+  Aug09   5:39 /usr/bin/Xorg :
root      6828  0.0  0.0 108452  1932 pts/0    Ss   08:46   0:00 -bash
root     25925 37.0  0.0 113636   896 pts/0    T    11:12   1:36 cp -i system201
root     27324 12.0  0.0 110232  1168 pts/0    R+   11:16   0:00 ps u

当进程被挂起(即被停止)时,被分配给一个作业号码(如果没有的话),并被置于后台.jobs命令把该作业报告成”停止的”
作业.ps命令确定进程处于停止(挂起)状态.

重新启动挂起在后台的作业
挂起在后台的作业可以用bg内置命令重新启动.像fg命令一样,bg命令把作业号码作为参数,或者,如果没有提供任何
作业号码,就使用当前作业

[root@sidatabase oradata]# bg 1
[1]+ cp -i system20130708.dmp / > cp.txt 2> /dev/null &
[root@sidatabase oradata]# jobs
[1]+  Running                 cp -i system20130708.dmp / > cp.txt 2> /dev/null &
[root@sidatabase oradata]#

作业号码1现在再次处于运行状态

在删除用户时报ORA-00600: 内部错误代码, 参数: [13011], [420], [4293646], [57], [4293653], [0], [], [], [], [], [], []

在删除用户时报ORA-00600: 内部错误代码, 参数: [13011], [420], [4293646], [57], [4293653], [0], [], [], [], [], [], []

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as system

SQL> drop user gzyb cascade;

ORA-00600: 内部错误代码, 参数: [13011], [420], [4293646], [57], [4293653], [0], [], [], [], [], [], []

检查跟踪文件/orac/diag/rdbms/dbservice/dbservice/incident/incdir_47163/dbservice_ora_13533_i47163.trc

Dump file /orac/diag/rdbms/dbservice/dbservice/incident/incdir_47163/dbservice_ora_13533_i47163.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /orac/orahome/11.2.0/db_1
System name:    Linux
Node name:      gzybtest
Release:        2.6.32-279.el6.x86_64
Version:        #1 SMP Wed Jun 13 18:24:36 EDT 2012
Machine:        x86_64
Instance name: dbservice
Redo thread mounted by this instance: 1
Oracle process number: 26
Unix process pid: 13533, image: oracle@gzybtest


*** 2013-08-10 13:15:13.697
*** SESSION ID:(68.2534) 2013-08-10 13:15:13.697
*** CLIENT ID:() 2013-08-10 13:15:13.697
*** SERVICE NAME:(dbservice) 2013-08-10 13:15:13.697
*** MODULE NAME:(PL/SQL Developer) 2013-08-10 13:15:13.697
*** ACTION NAME:(Main session) 2013-08-10 13:15:13.697

Dump continued from file: /orac/diag/rdbms/dbservice/dbservice/trace/dbservice_ora_13533.trc
ORA-00600: 内部错误代码, 参数: [13011], [420], [4293646], [57], [4293653], [0], [], [], [], [], [], []

========= Dump for incident 47163 (ORA 600 [13011]) ========

*** 2013-08-10 13:15:13.697
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=2mp99nzd9u1qp) -----
delete from histgrm$ where obj# = :1

----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
skdstdst()+36        call     kgdsdst()            000000000 ? 000000000 ?
                                                   7FFFEC2A8D08 ? 000000001 ?
                                                   7FFFEC2AD208 ? 000000000 ?

发现是在执行delete from histgrm$ where obj# = :1语句时报错

对于13013错误而言,随后的6个参数含义如下:
Arg [a] Passcount
Arg [b] Data Object number
Arg [c] Tablespace Relative DBA of block containing the row to be updated
Arg [d] Row Slot number
Arg [e] Relative DBA of block being updated (should be same as [c])

查询object_id=420是什么对象发现是C_OBJ#_INTCOL#

SQL> Select object_name,object_type,owner from dba_objects where data_object_id=420;

OBJECT_NAME                                                                      OBJECT_TYPE         OWNER
-------------------------------------------------------------------------------- ------------------- ----------- -------------------
HISTGRM$                                                                         TABLE               SYS
C_OBJ#_INTCOL#                                                                   CLUSTER             SYS

看样子只有HISTGRM$存储在这个CLUSTER中。按理说这个对象是可以TRUNCATE的

SQL> truncate cluster c_obj#_intcol#;
truncate cluster c_obj#_intcol#
                 *
第 1 行出现错误:
ORA-00701: 无法变更热启动数据库所需的对象

由于是BOOTSTRAP$对象,所以无法TRUNCATE.由于这个对象是420>59,因此不是核心BOOTSTRAP$对象,所以我们用得上EVENT 38003 了。

SQL> alter system set EVENT="38003 trace name context forever, level 10"
  2  SCOPE=SPFILE;

系统已更改。
SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。
Total System Global Area  448790528 bytes
Fixed Size                  1297220 bytes
Variable Size             163579068 bytes
Database Buffers          276824064 bytes
Redo Buffers                7090176 bytes
数据库装载完毕。
数据库已经打开。
SQL> truncate cluster c_obj#_intcol#;
簇已截断

SQL> drop user gzyb cascade;

User dropped

隐含参数_DISABLE_LOGGING导致数据库的损坏在重启数据库时报ORA-19821

由于修改了隐含参数_DISABLE_LOGGING为TRUE:_disable_logging = TRUE 那么启动数据库后进行任何的操作,在关闭数据库后,就会发现数据库出现了损坏:

SQL> startup
ORACLE instance started.

Total System Global Area 1.3495E+10 bytes
Fixed Size                  2218032 bytes
Variable Size            5838473168 bytes
Database Buffers         7583301632 bytes
Redo Buffers               71471104 bytes
Database mounted.
ORA-19821: an intentionally corrupt log file was found

由于是将_disable_logging”=true造成的这个问题那么现在将_disable_logging”设置为false

SQL> alter system set "_disable_logging"=false scope=spfile;

System altered.

SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1.3495E+10 bytes
Fixed Size                  2218032 bytes
Variable Size            5838473168 bytes
Database Buffers         7583301632 bytes
Redo Buffers               71471104 bytes
Database mounted.
ORA-19821: an intentionally corrupt log file was found

还是报ORA-19821

由于错误提示是说日志文件有问题那么设置_allow_resetlogs_corruption来尝试恢复执行下面的sql语句来创建pfile参数文件
SQL>create pfile from spfile;

编辑/orac/orahome/11.2.0/db_1/dbs/spfiledbservice.ora

[root@gzybtest dbservice]vi /orac/orahome/11.2.0/db_1/dbs/spfiledbservice.ora
System parameters with non-default values:
  processes                = 150
  memory_target            = 12928M
  control_files            = "/oradata/dbservice/control01.ctl"
  control_files            = "/orac/flash_recovery_area/dbservice/control02.ctl"
  db_block_size            = 8192
  compatible               = "11.2.0.0.0"
  _disable_logging         = FALSE
  db_files                 = 500
  db_recovery_file_dest    = "/orac/flash_recovery_area"
  db_recovery_file_dest_size= 3882M
  _allow_resetlogs_corruption= TRUE
  undo_tablespace          = "UNDOTBS1"
  remote_login_passwordfile= "EXCLUSIVE"
  db_domain                = ""
  dispatchers              = "(PROTOCOL=TCP) (SERVICE=dbserviceXDB)"
  audit_file_dest          = "/orac/admin/dbservice/adump"
  audit_trail              = "NONE"
  db_name                  = "dbservic"
  db_unique_name           = "dbservice"
  open_cursors             = 300
  deferred_segment_creation= FALSE
  diagnostic_dest          = "/orac"

然后使用pfile文件来启动实例

SQL>  startup pfile=/orac/orahome/11.2.0/db_1/dbs/initdbservice.ora mount
ORACLE instance started.

Total System Global Area 1.3495E+10 bytes
Fixed Size                  2218032 bytes
Variable Size            5838473168 bytes
Database Buffers         7583301632 bytes
Redo Buffers               71471104 bytes
Database mounted.

SQL> recover database until cancel;

ORA-00279: change 13480665044793 generated at 08/08/2013 16:36:47 needed for
thread 1
ORA-00289: suggestion :
/orac/flash_recovery_area/DBSERVICE/archivelog/2013_08_08/o1_mf_1_1258_%u_.arc
ORA-00280: change 13480665044793 for thread 1 is in sequence #1258

Specify log: {=suggested | filename | AUTO | CANCEL}
ORA-00308: cannot open archived log
'/orac/flash_recovery_area/DBSERVICE/archivelog/2013_08_08/o1_mf_1_1258_%u_.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

ORA-10879: error signaled in parallel recovery slave
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/oradata/dbservice/system01.dbf'

SQL> recover datafile 1;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

SQL> alter database backup controlfile to trace;

SQL> oradebug setmypid;

SQL> oradebug TRACEFILE_NAME
/orac/diag/rdbms/dbservice/dbservice/trace/dbservice_ora_6571.trc

找到跟踪文件中的

CREATE CONTROLFILE REUSE DATABASE "DBSERVIC" NORESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 800
    MAXINSTANCES 8
    MAXLOGHISTORY 2336
LOGFILE
  GROUP 4 '/oradata/dbservice/redo04.log'  SIZE 1000M BLOCKSIZE 512,
  GROUP 5 '/oradata/dbservice/redo05.log'  SIZE 1000M BLOCKSIZE 512,
  GROUP 6 '/oradata/dbservice/redo07.log'  SIZE 1000M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/oradata/dbservice/system01.dbf',
  '/oradata/dbservice/sysaux01.dbf',
  '/oradata/dbservice/undotbs01.dbf',
  '/oradata/dbservice/users01.dbf',
  '/oradata/dbservice/example01.dbf',
  '/oradata/dbservice/sicp3_bbk/sicp3_bbk01.dbf',
  '/oradata/dbservice/sicp3_bbk/sicp3_bbk02.dbf',
  '/oradata/dbservice/sicp3_bbk/sicp3_bbk03.dbf',
  '/oradata/dbservice/sicp3_bbk/sicp3_bbk04.dbf',
  '/oradata/dbservice/sicp3_bbk/sicp3_bbk05.dbf',
  '/oradata/dbservice/sicp3_bbk/sicp3_bbk06.dbf',
  '/oradata/dbservice/sicp3_bbk/sicp3_bbk07.dbf',
  '/oradata/dbservice/sicp3_bbk/sicp3_bbk08.dbf',
  '/oradata/dbservice/sicp3_bbk/sicp3_bbk09.dbf',
  '/oradata/dbservice/sicp3_bbk/sicp3_bbk10.dbf',
  '/oradata/dbservice/sicp3_bbk/sicp3_bbk11.dbf',
  '/oradata/dbservice/sicp3_bbk/sicp3_bbk12.dbf',
  '/oradata/dbservice/sicp3_bbk/sicp3_bbk13.dbf',
  '/oradata/dbservice/sicp3_bbk/sicp3_bbk14.dbf',
  '/oradata/dbservice/sicp3_bbk/sicp3_bbk15.dbf',
  '/oradata/dbservice/sicp3_bbk/sicp3_bbk16.dbf',
  '/oradata/dbservice/sicp3_bbk/sicp3_bbk17.dbf',
  '/oradata/dbservice/sicp3_bbk/sicp3_bbk18.dbf',
  '/oradata/dbservice/sicp3_bbk/sicp3_bbk19.dbf',
  '/oradata/dbservice/sicp3_bbk/sicp3_bbk20.dbf',
  '/oradata/dbservice/sicp3_bbk/sicp3_bbk21.dbf',
  '/oradata/dbservice/sicp3_bbk/sicp3_bbk22.dbf',
  '/oradata/dbservice/sicp3_bbk/sicp3_bbk23.dbf',
  '/oradata/dbservice/sicp3_bbk/sicp3_bbk24.dbf',
  '/oradata/dbservice/sicp3_bbk/sicp3_bbk25.dbf',
  '/oradata/dbservice/sicp3_bbk/sicp3_bbk26.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb01.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk01.dbf',
  '/oradata/dbservice/sicp3_ybjh/sicp3_ybjh01.dbf',
  '/oradata/dbservice/sicp3_ybjh/sicp3_ybjh02.dbf',
  '/oradata/dbservice/sicp3_ybjh/sicp3_ybjh03.dbf',
  '/oradata/dbservice/sicp3_ybjh/sicp3_ybjh04.dbf',
  '/oradata/dbservice/sicp3_ybjh/sicp3_ybjh05.dbf',
  '/oradata/dbservice/sicp3_ybjh/sicp3_ybjh06.dbf',
  '/oradata/dbservice/sicp3_ybjh/sicp3_ybjh07.dbf',
  '/oradata/dbservice/sicp3_ybjh/sicp3_ybjh08.dbf',
  '/oradata/dbservice/sicp3_ybjh/sicp3_ybjh09.dbf',
  '/oradata/dbservice/sicp3_ybjh/sicp3_ybjh10.dbf',
  '/oradata/dbservice/sicp3_ybjh/sicp3_ybjh11.dbf',
  '/oradata/dbservice/sicp3_ybjh/sicp3_ybjh12.dbf',
  '/oradata/dbservice/sicp3_ydjg/sicp3_ydjg01.dbf',
  '/oradata/dbservice/sicp3_ydjg/sicp3_ydjg02.dbf',
  '/oradata/dbservice/sicp3_ydjg/sicp3_ydjg03.dbf',
  '/oradata/dbservice/sicp3_ydjg/sicp3_ydjg04.dbf',
  '/oradata/dbservice/sicp3_ydjg/sicp3_ydjg05.dbf',
  '/oradata/dbservice/sicp3_ydjg/sicp3_ydjg06.dbf',
  '/oradata/dbservice/sicp3_ydjg/sicp3_ydjg07.dbf',
  '/oradata/dbservice/sicp3_ydjg/sicp3_ydjg08.dbf',
  '/oradata/dbservice/sicp3_ydjg/sicp3_ydjg09.dbf',
  '/oradata/dbservice/sicp3_ydjg/sicp3_ydjg10.dbf',
  '/oradata/dbservice/sicp3_ydjg/sicp3_ydjg11.dbf',
  '/oradata/dbservice/sicp3_ydjg/sicp3_ydjg12.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb02.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb03.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb04.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb05.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb06.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb07.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb08.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb09.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb10.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb11.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb12.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb13.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb14.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb15.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb16.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb17.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb18.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb19.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb20.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb21.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb22.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb23.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb24.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb25.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb26.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb27.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb28.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb29.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb30.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb31.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb32.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb33.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb34.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb35.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb36.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb37.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb38.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb39.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb40.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb41.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb42.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb43.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb44.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb45.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb46.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb47.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb48.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb49.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb50.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb51.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb52.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb53.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb54.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb55.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb56.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb57.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb58.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb59.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb60.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb61.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb62.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb63.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb64.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb65.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb66.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb67.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb68.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb69.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb70.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb71.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb72.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb73.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb74.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb75.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb76.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb77.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb78.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb79.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb80.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb81.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb82.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb83.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb84.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb85.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb86.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb87.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb88.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb89.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb90.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb91.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb92.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb93.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb94.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb95.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb96.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb97.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb98.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb99.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb100.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb101.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb102.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb103.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk02.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk03.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk04.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk05.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk06.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk07.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk08.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk09.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk10.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk11.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk12.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk13.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk14.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk15.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk16.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk17.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk18.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk19.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk20.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk21.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk22.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk23.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk24.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk25.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk26.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk27.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk28.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk29.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk30.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk31.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk32.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk33.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk34.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk35.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk36.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk37.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk38.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk39.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk40.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk41.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk42.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk43.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk44.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk45.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk46.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk47.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk48.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk49.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk50.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk51.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk52.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk53.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk54.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk55.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk56.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk57.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk58.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk59.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk60.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk61.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk62.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk63.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk64.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk65.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk66.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk67.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk68.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk69.dbf',
  '/oradata/dbservice/gzyb/gzyb01.dbf',
  '/oradata/dbservice/gzyb/gzyb02.dbf',
  '/oradata/dbservice/gzyb/gzyb03.dbf',
  '/oradata/dbservice/gzyb/gzyb04.dbf',
  '/oradata/dbservice/gzyb/gzyb05.dbf',
  '/oradata/dbservice/gzyb/gzyb06.dbf',
  '/oradata/dbservice/gzyb/gzyb07.dbf',
  '/oradata/dbservice/gzyb/gzyb08.dbf',
  '/oradata/dbservice/gzyb/gzyb09.dbf',
  '/oradata/dbservice/gzyb/gzyb10.dbf',
  '/oradata/dbservice/gzyb/gzyb11.dbf',
  '/oradata/dbservice/gzyb/gzyb12.dbf',
  '/oradata/dbservice/gzyb/gzyb13.dbf',
  '/oradata/dbservice/gzyb/gzyb14.dbf',
  '/oradata/dbservice/gzyb/gzyb15.dbf',
  '/oradata/dbservice/gzyb/gzyb16.dbf',
  '/oradata/dbservice/gzyb/gzyb17.dbf',
  '/oradata/dbservice/gzyb/gzyb18.dbf',
  '/oradata/dbservice/gzyb/gzyb19.dbf',
  '/oradata/dbservice/gzyb/gzyb20.dbf',
  '/oradata/dbservice/gzyb/gzyb21.dbf',
  '/oradata/dbservice/gzyb/gzyb22.dbf',
  '/oradata/dbservice/gzyb/gzyb23.dbf',
  '/oradata/dbservice/gzyb/gzyb24.dbf',
  '/oradata/dbservice/gzyb/gzyb25.dbf',
  '/oradata/dbservice/gzyb/gzyb26.dbf',
  '/oradata/dbservice/gzyb/gzyb27.dbf',
  '/oradata/dbservice/gzyb/gzyb28.dbf',
  '/oradata/dbservice/gzyb/gzyb29.dbf',
  '/oradata/dbservice/gzyb/gzyb30.dbf',
  '/oradata/dbservice/gzyb/gzyb31.dbf',
  '/oradata/dbservice/gzyb/gzyb32.dbf',
  '/oradata/dbservice/gzyb/gzyb33.dbf',
  '/oradata/dbservice/gzyb/gzyb34.dbf',
  '/oradata/dbservice/gzyb/gzyb35.dbf',
  '/oradata/dbservice/gzyb/gzyb36.dbf',
  '/oradata/dbservice/gzyb/gzyb37.dbf',
  '/oradata/dbservice/gzyb/gzyb38.dbf',
  '/oradata/dbservice/gzyb/gzyb39.dbf',
  '/oradata/dbservice/gzyb/gzyb40.dbf',
  '/oradata/dbservice/gzyb/gzyb41.dbf',
  '/oradata/dbservice/gzyb/gzyb42.dbf',
  '/oradata/dbservice/gzyb/gzyb43.dbf',
  '/oradata/dbservice/gzyb/gzyb44.dbf',
  '/oradata/dbservice/gzyb/gzyb45.dbf',
  '/oradata/dbservice/gzyb/gzyb46.dbf',
  '/oradata/dbservice/gzyb/gzyb47.dbf',
  '/oradata/dbservice/gzyb/gzyb48.dbf',
  '/oradata/dbservice/gzyb/gzyb49.dbf',
  '/oradata/dbservice/gzyb/gzyb50.dbf',
  '/oradata/dbservice/gzyb/gzyb51.dbf',
  '/oradata/dbservice/gzyb/gzyb52.dbf',
  '/oradata/dbservice/gzyb/gzyb53.dbf',
  '/oradata/dbservice/gzyb/gzyb54.dbf',
  '/oradata/dbservice/gzyb/gzyb55.dbf',
  '/oradata/dbservice/gzyb/gzyb56.dbf',
  '/oradata/dbservice/gzyb/gzyb57.dbf',
  '/oradata/dbservice/gzyb/gzyb58.dbf',
  '/oradata/dbservice/gzyb/gzyb59.dbf',
  '/oradata/dbservice/gzyb/gzyb60.dbf',
  '/oradata/dbservice/gzyb/gzyb61.dbf',
  '/oradata/dbservice/gzyb/gzyb62.dbf',
  '/oradata/dbservice/gzyb/gzyb63.dbf',
  '/oradata/dbservice/gzyb/gzyb64.dbf',
  '/oradata/dbservice/gzyb/gzyb65.dbf',
  '/oradata/dbservice/gzyb/gzyb66.dbf',
  '/oradata/dbservice/gzyb/gzyb67.dbf',
  '/oradata/dbservice/gzyb/gzyb68.dbf',
  '/oradata/dbservice/gzyb/gzyb69.dbf',
  '/oradata/dbservice/gzyb/gzyb70.dbf',
  '/oradata/dbservice/gzyb/gzyb71.dbf',
  '/oradata/dbservice/gzyb/gzyb72.dbf',
  '/oradata/dbservice/gzyb/gzyb73.dbf',
  '/oradata/dbservice/gzyb/gzyb74.dbf',
  '/oradata/dbservice/gzyb/gzyb75.dbf',
  '/oradata/dbservice/gzyb/gzyb76.dbf',
  '/oradata/dbservice/gzyb/gzyb77.dbf',
  '/oradata/dbservice/gzyb/gzyb78.dbf',
  '/oradata/dbservice/gzyb/gzyb79.dbf',
  '/oradata/dbservice/gzyb/gzyb80.dbf',
  '/oradata/dbservice/gzyb/gzyb81.dbf',
  '/oradata/dbservice/gzyb/gzyb82.dbf',
  '/oradata/dbservice/gzyb/gzyb83.dbf',
  '/oradata/dbservice/gzyb/gzyb84.dbf',
  '/oradata/dbservice/gzyb/gzyb85.dbf',
  '/oradata/dbservice/gzyb/gzyb86.dbf',
  '/oradata/dbservice/gzyb/gzyb87.dbf',
  '/oradata/dbservice/gzyb/gzyb88.dbf',
  '/oradata/dbservice/gzyb/gzyb89.dbf',
  '/oradata/dbservice/gzyb/gzyb90.dbf',
  '/oradata/dbservice/gzyb/gzyb91.dbf',
  '/oradata/dbservice/gzyb/gzyb92.dbf',
  '/oradata/dbservice/gzyb/gzyb93.dbf',
  '/oradata/dbservice/gzyb/gzyb94.dbf',
  '/oradata/dbservice/gzyb/gzyb95.dbf',
  '/oradata/dbservice/gzyb/gzyb96.dbf',
  '/oradata/dbservice/gzyb/gzyb97.dbf',
  '/oradata/dbservice/gzyb/gzyb98.dbf',
  '/oradata/dbservice/gzyb/gzyb99.dbf',
  '/oradata/dbservice/gzyb/gzyb100.dbf',
  '/oradata/dbservice/gzyb/gzyb101.dbf',
  '/oradata/dbservice/gzyb/gzyb102.dbf',
  '/oradata/dbservice/gzyb/gzyb103.dbf',
  '/oradata/dbservice/gzyb/gzyb104.dbf',
  '/oradata/dbservice/gzyb/gzyb105.dbf',
  '/oradata/dbservice/gzyb/gzyb106.dbf',
  '/oradata/dbservice/gzyb/gzyb107.dbf',
  '/oradata/dbservice/gzyb/gzyb108.dbf',
  '/oradata/dbservice/gzyb/gzyb109.dbf',
  '/oradata/dbservice/gzyb/gzyb110.dbf',
  '/oradata/dbservice/gzyb/gzyb111.dbf',
  '/oradata/dbservice/gzyb/gzyb112.dbf',
  '/oradata/dbservice/gzyb/gzyb113.dbf',
  '/oradata/dbservice/gzyb/gzyb114.dbf',
  '/oradata/dbservice/gzyb/gzyb115.dbf',
  '/oradata/dbservice/gzyb/gzyb116.dbf',
  '/oradata/dbservice/gzyb/gzyb117.dbf',
  '/oradata/dbservice/gzyb/gzyb118.dbf',
  '/oradata/dbservice/gzyb/gzyb119.dbf',
  '/oradata/dbservice/gzyb/gzyb120.dbf',
  '/oradata/dbservice/gzyb/gzyb121.dbf',
  '/oradata/dbservice/gzyb/gzyb122.dbf',
  '/oradata/dbservice/gzyb/gzyb123.dbf',
  '/oradata/dbservice/gzyb/gzyb124.dbf',
  '/oradata/dbservice/gzyb/gzyb125.dbf',
  '/oradata/dbservice/gzyb/gzyb126.dbf',
  '/oradata/dbservice/gzyb/gzyb127.dbf',
  '/oradata/dbservice/gzyb/gzyb128.dbf',
  '/oradata/dbservice/gzyb/gzyb129.dbf',
  '/oradata/dbservice/gzyb/gzyb130.dbf',
  '/oradata/dbservice/gzyb/gzyb131.dbf',
  '/oradata/dbservice/gzyb/gzyb132.dbf',
  '/oradata/dbservice/gzyb/gzyb133.dbf',
  '/oradata/dbservice/gzyb/gzyb134.dbf',
  '/oradata/dbservice/gzyb/gzyb135.dbf',
  '/oradata/dbservice/gzyb/gzyb136.dbf',
  '/oradata/dbservice/gzyb/gzyb137.dbf',
  '/oradata/dbservice/gzyb/gzyb138.dbf',
  '/oradata/dbservice/gzyb/gzyb139.dbf',
  '/oradata/dbservice/gzyb/gzyb140.dbf',
  '/oradata/dbservice/gzyb/gzyb141.dbf',
  '/oradata/dbservice/gzyb/gzyb142.dbf',
  '/oradata/dbservice/gzyb/gzyb143.dbf',
  '/oradata/dbservice/gzyb/gzyb144.dbf',
  '/oradata/dbservice/gzyb/gzyb145.dbf',
  '/oradata/dbservice/gzyb/gzyb146.dbf',
  '/oradata/dbservice/gzyb/gzyb147.dbf',
  '/oradata/dbservice/gzyb/gzyb148.dbf',
  '/oradata/dbservice/gzyb/gzyb149.dbf',
  '/oradata/dbservice/gzyb/gzyb150.dbf',
  '/oradata/dbservice/gzyb/gzyb151.dbf',
  '/oradata/dbservice/gzyb/gzyb152.dbf',
  '/oradata/dbservice/gzyb/gzyb153.dbf',
  '/oradata/dbservice/gzyb/gzyb154.dbf',
  '/oradata/dbservice/gzyb/gzyb155.dbf',
  '/oradata/dbservice/gzyb/gzyb156.dbf',
  '/oradata/dbservice/gzyb/gzyb157.dbf',
  '/oradata/dbservice/gzyb/gzyb158.dbf',
  '/oradata/dbservice/gzyb/gzyb159.dbf',
  '/oradata/dbservice/gzyb/gzyb160.dbf',
  '/oradata/dbservice/gzyb/gzyb161.dbf',
  '/oradata/dbservice/gzyb/gzyb162.dbf',
  '/oradata/dbservice/gzyb/gzyb163.dbf',
  '/oradata/dbservice/gzyb/gzyb164.dbf',
  '/oradata/dbservice/gzyb/gzyb165.dbf',
  '/oradata/dbservice/gzyb/gzyb166.dbf',
  '/oradata/dbservice/gzyb/gzyb167.dbf',
  '/oradata/dbservice/gzyb/gzyb168.dbf',
  '/oradata/dbservice/gzyb/gzyb169.dbf',
  '/oradata/dbservice/gzyb/gzyb170.dbf',
  '/oradata/dbservice/gzyb/gzyb171.dbf',
  '/oradata/dbservice/gzyb/gzyb172.dbf',
  '/oradata/dbservice/gzyb/gzyb173.dbf',
  '/oradata/dbservice/gzyb/gzyb174.dbf',
  '/oradata/dbservice/gzyb/gzyb175.dbf',
  '/oradata/dbservice/gzyb/gzyb176.dbf',
  '/oradata/dbservice/gzyb/gzyb177.dbf',
  '/oradata/dbservice/gzyb/gzyb178.dbf',
  '/oradata/dbservice/gzyb/gzyb179.dbf',
  '/oradata/dbservice/gzyb/gzyb180.dbf',
  '/oradata/dbservice/gzyb/gzyb181.dbf',
  '/oradata/dbservice/gzyb/gzyb182.dbf',
  '/oradata/dbservice/gzyb/gzyb183.dbf',
  '/oradata/dbservice/gzyb/gzyb184.dbf',
  '/oradata/dbservice/gzyb/gzyb185.dbf',
  '/oradata/dbservice/gzyb/gzyb186.dbf',
  '/oradata/dbservice/gzyb/gzyb187.dbf',
  '/oradata/dbservice/gzyb/gzyb188.dbf',
  '/oradata/dbservice/gzyb/gzyb189.dbf',
  '/oradata/dbservice/gzyb/gzyb190.dbf',
  '/oradata/dbservice/gzyb/gzyb191.dbf',
  '/oradata/dbservice/gzyb/gzyb192.dbf',
  '/oradata/dbservice/gzyb/gzyb193.dbf',
  '/oradata/dbservice/gzyb/gzyb194.dbf',
  '/oradata/dbservice/gzyb/gzyb195.dbf',
  '/oradata/dbservice/gzyb/gzyb196.dbf',
  '/oradata/dbservice/gzyb/gzyb197.dbf',
  '/oradata/dbservice/gzyb/gzyb198.dbf',
  '/oradata/dbservice/gzyb/gzyb199.dbf',
  '/oradata/dbservice/gzyb/gzyb200.dbf',
  '/oradata/dbservice/gzyb_sy.dbf'
CHARACTER SET ZHS16GBK
;

来创建控制文件,在创建新的控制文件前记得在操作系统级别来执行rm来删除原来的控制文件

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1.3495E+10 bytes
Fixed Size                  2218032 bytes
Variable Size            5838473168 bytes
Database Buffers         7583301632 bytes
Redo Buffers               71471104 bytes

SQL>CREATE CONTROLFILE REUSE DATABASE "DBSERVIC" NORESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 800
    MAXINSTANCES 8
    MAXLOGHISTORY 2336
LOGFILE
  GROUP 4 '/oradata/dbservice/redo04.log'  SIZE 1000M BLOCKSIZE 512,
  GROUP 5 '/oradata/dbservice/redo05.log'  SIZE 1000M BLOCKSIZE 512,
  GROUP 6 '/oradata/dbservice/redo07.log'  SIZE 1000M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/oradata/dbservice/system01.dbf',
  '/oradata/dbservice/sysaux01.dbf',
  '/oradata/dbservice/undotbs01.dbf',
  '/oradata/dbservice/users01.dbf',
  '/oradata/dbservice/example01.dbf',
  '/oradata/dbservice/sicp3_bbk/sicp3_bbk01.dbf',
  '/oradata/dbservice/sicp3_bbk/sicp3_bbk02.dbf',
  '/oradata/dbservice/sicp3_bbk/sicp3_bbk03.dbf',
  '/oradata/dbservice/sicp3_bbk/sicp3_bbk04.dbf',
  '/oradata/dbservice/sicp3_bbk/sicp3_bbk05.dbf',
  '/oradata/dbservice/sicp3_bbk/sicp3_bbk06.dbf',
  '/oradata/dbservice/sicp3_bbk/sicp3_bbk07.dbf',
  '/oradata/dbservice/sicp3_bbk/sicp3_bbk08.dbf',
  '/oradata/dbservice/sicp3_bbk/sicp3_bbk09.dbf',
  '/oradata/dbservice/sicp3_bbk/sicp3_bbk10.dbf',
  '/oradata/dbservice/sicp3_bbk/sicp3_bbk11.dbf',
  '/oradata/dbservice/sicp3_bbk/sicp3_bbk12.dbf',
  '/oradata/dbservice/sicp3_bbk/sicp3_bbk13.dbf',
  '/oradata/dbservice/sicp3_bbk/sicp3_bbk14.dbf',
  '/oradata/dbservice/sicp3_bbk/sicp3_bbk15.dbf',
  '/oradata/dbservice/sicp3_bbk/sicp3_bbk16.dbf',
  '/oradata/dbservice/sicp3_bbk/sicp3_bbk17.dbf',
  '/oradata/dbservice/sicp3_bbk/sicp3_bbk18.dbf',
  '/oradata/dbservice/sicp3_bbk/sicp3_bbk19.dbf',
  '/oradata/dbservice/sicp3_bbk/sicp3_bbk20.dbf',
  '/oradata/dbservice/sicp3_bbk/sicp3_bbk21.dbf',
  '/oradata/dbservice/sicp3_bbk/sicp3_bbk22.dbf',
  '/oradata/dbservice/sicp3_bbk/sicp3_bbk23.dbf',
  '/oradata/dbservice/sicp3_bbk/sicp3_bbk24.dbf',
  '/oradata/dbservice/sicp3_bbk/sicp3_bbk25.dbf',
  '/oradata/dbservice/sicp3_bbk/sicp3_bbk26.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb01.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk01.dbf',
  '/oradata/dbservice/sicp3_ybjh/sicp3_ybjh01.dbf',
  '/oradata/dbservice/sicp3_ybjh/sicp3_ybjh02.dbf',
  '/oradata/dbservice/sicp3_ybjh/sicp3_ybjh03.dbf',
  '/oradata/dbservice/sicp3_ybjh/sicp3_ybjh04.dbf',
  '/oradata/dbservice/sicp3_ybjh/sicp3_ybjh05.dbf',
  '/oradata/dbservice/sicp3_ybjh/sicp3_ybjh06.dbf',
  '/oradata/dbservice/sicp3_ybjh/sicp3_ybjh07.dbf',
  '/oradata/dbservice/sicp3_ybjh/sicp3_ybjh08.dbf',
  '/oradata/dbservice/sicp3_ybjh/sicp3_ybjh09.dbf',
  '/oradata/dbservice/sicp3_ybjh/sicp3_ybjh10.dbf',
  '/oradata/dbservice/sicp3_ybjh/sicp3_ybjh11.dbf',
  '/oradata/dbservice/sicp3_ybjh/sicp3_ybjh12.dbf',
  '/oradata/dbservice/sicp3_ydjg/sicp3_ydjg01.dbf',
  '/oradata/dbservice/sicp3_ydjg/sicp3_ydjg02.dbf',
  '/oradata/dbservice/sicp3_ydjg/sicp3_ydjg03.dbf',
  '/oradata/dbservice/sicp3_ydjg/sicp3_ydjg04.dbf',
  '/oradata/dbservice/sicp3_ydjg/sicp3_ydjg05.dbf',
  '/oradata/dbservice/sicp3_ydjg/sicp3_ydjg06.dbf',
  '/oradata/dbservice/sicp3_ydjg/sicp3_ydjg07.dbf',
  '/oradata/dbservice/sicp3_ydjg/sicp3_ydjg08.dbf',
  '/oradata/dbservice/sicp3_ydjg/sicp3_ydjg09.dbf',
  '/oradata/dbservice/sicp3_ydjg/sicp3_ydjg10.dbf',
  '/oradata/dbservice/sicp3_ydjg/sicp3_ydjg11.dbf',
  '/oradata/dbservice/sicp3_ydjg/sicp3_ydjg12.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb02.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb03.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb04.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb05.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb06.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb07.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb08.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb09.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb10.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb11.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb12.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb13.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb14.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb15.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb16.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb17.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb18.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb19.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb20.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb21.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb22.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb23.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb24.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb25.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb26.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb27.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb28.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb29.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb30.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb31.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb32.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb33.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb34.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb35.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb36.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb37.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb38.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb39.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb40.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb41.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb42.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb43.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb44.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb45.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb46.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb47.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb48.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb49.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb50.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb51.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb52.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb53.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb54.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb55.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb56.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb57.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb58.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb59.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb60.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb61.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb62.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb63.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb64.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb65.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb66.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb67.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb68.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb69.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb70.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb71.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb72.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb73.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb74.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb75.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb76.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb77.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb78.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb79.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb80.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb81.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb82.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb83.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb84.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb85.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb86.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb87.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb88.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb89.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb90.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb91.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb92.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb93.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb94.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb95.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb96.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb97.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb98.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb99.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb100.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb101.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb102.dbf',
  '/oradata/dbservice/sicp3_gzyb/sicp3_gzyb103.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk02.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk03.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk04.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk05.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk06.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk07.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk08.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk09.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk10.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk11.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk12.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk13.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk14.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk15.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk16.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk17.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk18.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk19.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk20.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk21.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk22.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk23.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk24.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk25.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk26.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk27.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk28.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk29.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk30.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk31.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk32.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk33.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk34.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk35.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk36.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk37.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk38.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk39.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk40.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk41.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk42.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk43.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk44.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk45.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk46.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk47.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk48.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk49.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk50.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk51.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk52.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk53.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk54.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk55.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk56.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk57.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk58.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk59.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk60.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk61.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk62.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk63.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk64.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk65.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk66.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk67.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk68.dbf',
  '/oradata/dbservice/sicp3_ybnk/sicp3_ybnk69.dbf',
  '/oradata/dbservice/gzyb/gzyb01.dbf',
  '/oradata/dbservice/gzyb/gzyb02.dbf',
  '/oradata/dbservice/gzyb/gzyb03.dbf',
  '/oradata/dbservice/gzyb/gzyb04.dbf',
  '/oradata/dbservice/gzyb/gzyb05.dbf',
  '/oradata/dbservice/gzyb/gzyb06.dbf',
  '/oradata/dbservice/gzyb/gzyb07.dbf',
  '/oradata/dbservice/gzyb/gzyb08.dbf',
  '/oradata/dbservice/gzyb/gzyb09.dbf',
  '/oradata/dbservice/gzyb/gzyb10.dbf',
  '/oradata/dbservice/gzyb/gzyb11.dbf',
  '/oradata/dbservice/gzyb/gzyb12.dbf',
  '/oradata/dbservice/gzyb/gzyb13.dbf',
  '/oradata/dbservice/gzyb/gzyb14.dbf',
  '/oradata/dbservice/gzyb/gzyb15.dbf',
  '/oradata/dbservice/gzyb/gzyb16.dbf',
  '/oradata/dbservice/gzyb/gzyb17.dbf',
  '/oradata/dbservice/gzyb/gzyb18.dbf',
  '/oradata/dbservice/gzyb/gzyb19.dbf',
  '/oradata/dbservice/gzyb/gzyb20.dbf',
  '/oradata/dbservice/gzyb/gzyb21.dbf',
  '/oradata/dbservice/gzyb/gzyb22.dbf',
  '/oradata/dbservice/gzyb/gzyb23.dbf',
  '/oradata/dbservice/gzyb/gzyb24.dbf',
  '/oradata/dbservice/gzyb/gzyb25.dbf',
  '/oradata/dbservice/gzyb/gzyb26.dbf',
  '/oradata/dbservice/gzyb/gzyb27.dbf',
  '/oradata/dbservice/gzyb/gzyb28.dbf',
  '/oradata/dbservice/gzyb/gzyb29.dbf',
  '/oradata/dbservice/gzyb/gzyb30.dbf',
  '/oradata/dbservice/gzyb/gzyb31.dbf',
  '/oradata/dbservice/gzyb/gzyb32.dbf',
  '/oradata/dbservice/gzyb/gzyb33.dbf',
  '/oradata/dbservice/gzyb/gzyb34.dbf',
  '/oradata/dbservice/gzyb/gzyb35.dbf',
  '/oradata/dbservice/gzyb/gzyb36.dbf',
  '/oradata/dbservice/gzyb/gzyb37.dbf',
  '/oradata/dbservice/gzyb/gzyb38.dbf',
  '/oradata/dbservice/gzyb/gzyb39.dbf',
  '/oradata/dbservice/gzyb/gzyb40.dbf',
  '/oradata/dbservice/gzyb/gzyb41.dbf',
  '/oradata/dbservice/gzyb/gzyb42.dbf',
  '/oradata/dbservice/gzyb/gzyb43.dbf',
  '/oradata/dbservice/gzyb/gzyb44.dbf',
  '/oradata/dbservice/gzyb/gzyb45.dbf',
  '/oradata/dbservice/gzyb/gzyb46.dbf',
  '/oradata/dbservice/gzyb/gzyb47.dbf',
  '/oradata/dbservice/gzyb/gzyb48.dbf',
  '/oradata/dbservice/gzyb/gzyb49.dbf',
  '/oradata/dbservice/gzyb/gzyb50.dbf',
  '/oradata/dbservice/gzyb/gzyb51.dbf',
  '/oradata/dbservice/gzyb/gzyb52.dbf',
  '/oradata/dbservice/gzyb/gzyb53.dbf',
  '/oradata/dbservice/gzyb/gzyb54.dbf',
  '/oradata/dbservice/gzyb/gzyb55.dbf',
  '/oradata/dbservice/gzyb/gzyb56.dbf',
  '/oradata/dbservice/gzyb/gzyb57.dbf',
  '/oradata/dbservice/gzyb/gzyb58.dbf',
  '/oradata/dbservice/gzyb/gzyb59.dbf',
  '/oradata/dbservice/gzyb/gzyb60.dbf',
  '/oradata/dbservice/gzyb/gzyb61.dbf',
  '/oradata/dbservice/gzyb/gzyb62.dbf',
  '/oradata/dbservice/gzyb/gzyb63.dbf',
  '/oradata/dbservice/gzyb/gzyb64.dbf',
  '/oradata/dbservice/gzyb/gzyb65.dbf',
  '/oradata/dbservice/gzyb/gzyb66.dbf',
  '/oradata/dbservice/gzyb/gzyb67.dbf',
  '/oradata/dbservice/gzyb/gzyb68.dbf',
  '/oradata/dbservice/gzyb/gzyb69.dbf',
  '/oradata/dbservice/gzyb/gzyb70.dbf',
  '/oradata/dbservice/gzyb/gzyb71.dbf',
  '/oradata/dbservice/gzyb/gzyb72.dbf',
  '/oradata/dbservice/gzyb/gzyb73.dbf',
  '/oradata/dbservice/gzyb/gzyb74.dbf',
  '/oradata/dbservice/gzyb/gzyb75.dbf',
  '/oradata/dbservice/gzyb/gzyb76.dbf',
  '/oradata/dbservice/gzyb/gzyb77.dbf',
  '/oradata/dbservice/gzyb/gzyb78.dbf',
  '/oradata/dbservice/gzyb/gzyb79.dbf',
  '/oradata/dbservice/gzyb/gzyb80.dbf',
  '/oradata/dbservice/gzyb/gzyb81.dbf',
  '/oradata/dbservice/gzyb/gzyb82.dbf',
  '/oradata/dbservice/gzyb/gzyb83.dbf',
  '/oradata/dbservice/gzyb/gzyb84.dbf',
  '/oradata/dbservice/gzyb/gzyb85.dbf',
  '/oradata/dbservice/gzyb/gzyb86.dbf',
  '/oradata/dbservice/gzyb/gzyb87.dbf',
  '/oradata/dbservice/gzyb/gzyb88.dbf',
  '/oradata/dbservice/gzyb/gzyb89.dbf',
  '/oradata/dbservice/gzyb/gzyb90.dbf',
  '/oradata/dbservice/gzyb/gzyb91.dbf',
  '/oradata/dbservice/gzyb/gzyb92.dbf',
  '/oradata/dbservice/gzyb/gzyb93.dbf',
  '/oradata/dbservice/gzyb/gzyb94.dbf',
  '/oradata/dbservice/gzyb/gzyb95.dbf',
  '/oradata/dbservice/gzyb/gzyb96.dbf',
  '/oradata/dbservice/gzyb/gzyb97.dbf',
  '/oradata/dbservice/gzyb/gzyb98.dbf',
  '/oradata/dbservice/gzyb/gzyb99.dbf',
  '/oradata/dbservice/gzyb/gzyb100.dbf',
  '/oradata/dbservice/gzyb/gzyb101.dbf',
  '/oradata/dbservice/gzyb/gzyb102.dbf',
  '/oradata/dbservice/gzyb/gzyb103.dbf',
  '/oradata/dbservice/gzyb/gzyb104.dbf',
  '/oradata/dbservice/gzyb/gzyb105.dbf',
  '/oradata/dbservice/gzyb/gzyb106.dbf',
  '/oradata/dbservice/gzyb/gzyb107.dbf',
  '/oradata/dbservice/gzyb/gzyb108.dbf',
  '/oradata/dbservice/gzyb/gzyb109.dbf',
  '/oradata/dbservice/gzyb/gzyb110.dbf',
  '/oradata/dbservice/gzyb/gzyb111.dbf',
  '/oradata/dbservice/gzyb/gzyb112.dbf',
  '/oradata/dbservice/gzyb/gzyb113.dbf',
  '/oradata/dbservice/gzyb/gzyb114.dbf',
  '/oradata/dbservice/gzyb/gzyb115.dbf',
  '/oradata/dbservice/gzyb/gzyb116.dbf',
  '/oradata/dbservice/gzyb/gzyb117.dbf',
  '/oradata/dbservice/gzyb/gzyb118.dbf',
  '/oradata/dbservice/gzyb/gzyb119.dbf',
  '/oradata/dbservice/gzyb/gzyb120.dbf',
  '/oradata/dbservice/gzyb/gzyb121.dbf',
  '/oradata/dbservice/gzyb/gzyb122.dbf',
  '/oradata/dbservice/gzyb/gzyb123.dbf',
  '/oradata/dbservice/gzyb/gzyb124.dbf',
  '/oradata/dbservice/gzyb/gzyb125.dbf',
  '/oradata/dbservice/gzyb/gzyb126.dbf',
  '/oradata/dbservice/gzyb/gzyb127.dbf',
  '/oradata/dbservice/gzyb/gzyb128.dbf',
  '/oradata/dbservice/gzyb/gzyb129.dbf',
  '/oradata/dbservice/gzyb/gzyb130.dbf',
  '/oradata/dbservice/gzyb/gzyb131.dbf',
  '/oradata/dbservice/gzyb/gzyb132.dbf',
  '/oradata/dbservice/gzyb/gzyb133.dbf',
  '/oradata/dbservice/gzyb/gzyb134.dbf',
  '/oradata/dbservice/gzyb/gzyb135.dbf',
  '/oradata/dbservice/gzyb/gzyb136.dbf',
  '/oradata/dbservice/gzyb/gzyb137.dbf',
  '/oradata/dbservice/gzyb/gzyb138.dbf',
  '/oradata/dbservice/gzyb/gzyb139.dbf',
  '/oradata/dbservice/gzyb/gzyb140.dbf',
  '/oradata/dbservice/gzyb/gzyb141.dbf',
  '/oradata/dbservice/gzyb/gzyb142.dbf',
  '/oradata/dbservice/gzyb/gzyb143.dbf',
  '/oradata/dbservice/gzyb/gzyb144.dbf',
  '/oradata/dbservice/gzyb/gzyb145.dbf',
  '/oradata/dbservice/gzyb/gzyb146.dbf',
  '/oradata/dbservice/gzyb/gzyb147.dbf',
  '/oradata/dbservice/gzyb/gzyb148.dbf',
  '/oradata/dbservice/gzyb/gzyb149.dbf',
  '/oradata/dbservice/gzyb/gzyb150.dbf',
  '/oradata/dbservice/gzyb/gzyb151.dbf',
  '/oradata/dbservice/gzyb/gzyb152.dbf',
  '/oradata/dbservice/gzyb/gzyb153.dbf',
  '/oradata/dbservice/gzyb/gzyb154.dbf',
  '/oradata/dbservice/gzyb/gzyb155.dbf',
  '/oradata/dbservice/gzyb/gzyb156.dbf',
  '/oradata/dbservice/gzyb/gzyb157.dbf',
  '/oradata/dbservice/gzyb/gzyb158.dbf',
  '/oradata/dbservice/gzyb/gzyb159.dbf',
  '/oradata/dbservice/gzyb/gzyb160.dbf',
  '/oradata/dbservice/gzyb/gzyb161.dbf',
  '/oradata/dbservice/gzyb/gzyb162.dbf',
  '/oradata/dbservice/gzyb/gzyb163.dbf',
  '/oradata/dbservice/gzyb/gzyb164.dbf',
  '/oradata/dbservice/gzyb/gzyb165.dbf',
  '/oradata/dbservice/gzyb/gzyb166.dbf',
  '/oradata/dbservice/gzyb/gzyb167.dbf',
  '/oradata/dbservice/gzyb/gzyb168.dbf',
  '/oradata/dbservice/gzyb/gzyb169.dbf',
  '/oradata/dbservice/gzyb/gzyb170.dbf',
  '/oradata/dbservice/gzyb/gzyb171.dbf',
  '/oradata/dbservice/gzyb/gzyb172.dbf',
  '/oradata/dbservice/gzyb/gzyb173.dbf',
  '/oradata/dbservice/gzyb/gzyb174.dbf',
  '/oradata/dbservice/gzyb/gzyb175.dbf',
  '/oradata/dbservice/gzyb/gzyb176.dbf',
  '/oradata/dbservice/gzyb/gzyb177.dbf',
  '/oradata/dbservice/gzyb/gzyb178.dbf',
  '/oradata/dbservice/gzyb/gzyb179.dbf',
  '/oradata/dbservice/gzyb/gzyb180.dbf',
  '/oradata/dbservice/gzyb/gzyb181.dbf',
  '/oradata/dbservice/gzyb/gzyb182.dbf',
  '/oradata/dbservice/gzyb/gzyb183.dbf',
  '/oradata/dbservice/gzyb/gzyb184.dbf',
  '/oradata/dbservice/gzyb/gzyb185.dbf',
  '/oradata/dbservice/gzyb/gzyb186.dbf',
  '/oradata/dbservice/gzyb/gzyb187.dbf',
  '/oradata/dbservice/gzyb/gzyb188.dbf',
  '/oradata/dbservice/gzyb/gzyb189.dbf',
  '/oradata/dbservice/gzyb/gzyb190.dbf',
  '/oradata/dbservice/gzyb/gzyb191.dbf',
  '/oradata/dbservice/gzyb/gzyb192.dbf',
  '/oradata/dbservice/gzyb/gzyb193.dbf',
  '/oradata/dbservice/gzyb/gzyb194.dbf',
  '/oradata/dbservice/gzyb/gzyb195.dbf',
  '/oradata/dbservice/gzyb/gzyb196.dbf',
  '/oradata/dbservice/gzyb/gzyb197.dbf',
  '/oradata/dbservice/gzyb/gzyb198.dbf',
  '/oradata/dbservice/gzyb/gzyb199.dbf',
  '/oradata/dbservice/gzyb/gzyb200.dbf',
  '/oradata/dbservice/gzyb_sy.dbf'
CHARACTER SET ZHS16GBK
;

SQL>recover database;
Media Recovery Complete 

SQL> alter database open;
alter database open

SQL>  alter database open 
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/oradata/dbservice/system01.dbf'

打开数据库还是报错检查alert_dbservice.log文件发现

Errors in file /orac/diag/rdbms/dbservice/dbservice/trace/dbservice_ora_5247.trc  (incident=24161):
ORA-00600: internal error code, arguments: [2662], [3138], [3057689962], [3138], [3057697443], [12583040], [], 

[], [], [], [], []
Incident details in: /orac/diag/rdbms/dbservice/dbservice/incident/incdir_24161/dbservice_ora_5247_i24161.trc
Errors in file /orac/diag/rdbms/dbservice/dbservice/trace/dbservice_ora_5247.trc:
ORA-00600: internal error code, arguments: [2662], [3138], [3057689962], [3138], [3057697443], [12583040], [], 

[], [], [], [], []
Errors in file /orac/diag/rdbms/dbservice/dbservice/trace/dbservice_ora_5247.trc:
ORA-00600: internal error code, arguments: [2662], [3138], [3057689962], [3138], [3057697443], [12583040], [], 

[], [], [], [], []
Error 600 happened during db open, shutting down database

得到的信息有限,只能看到是严重内部错误,剩下的都是内存堆栈的一堆信息,于是查找了一下这个错误的具体相关信息。ORA-600 [2662] “Block SCN is ahead of Current SCN”,说明当前数据库的数据块的SCN早于当前的SCN,主要是和存储在UGA变量中的dependent SCN进行比较,如果当前的SCN小于它,数据库就会产生这个ORA-600 [2662]的错误了。这个错误一共有五个参
数,分别代表不同的含义,
ORA-600 [2662] [a] [b] [c] [d] [e]
Arg [a] Current SCN WRAP
Arg [b] Current SCN BASE
Arg [c] dependent SCN WRAP
Arg [d] dependent SCN BASE
Arg [e] Where present this is the DBA where the dependent SCN came from.
我们分析错误中的提示,它的参数b=3057689962,d=3057697443,表明当前的SCN确实是小于dependent SCN,所以产生了这个600的错误。
通过查阅文档,发现这个错误的产生原因主要有以下几条:
1.使用隐含参数_ALLOW_RESETLOGS_CORRUPTION后resetlogs打开数据库
2.硬件错误引起数据库没法写控制文件和重做日志文件
3.错误的部分恢复数据库
4.恢复了控制文件但是没有使用recover database using backup controlfile进行恢复
5.数据库crash后设置了_DISABLE_LOGGING隐含参数
6.在并行服务器环境中DLM存在问题
仔细对比了一下,发现问题可能是由于第一条产生的,由于设置了_ALLOW_RESETLOGS_CORRUPTION这个隐含参数后,虽然强制性的打开数据库,但是数据库本身存在了corruption,仍然存在严重的问题。于是想到使用ADJUST_SCN事件来调整当前的SCN,使其大于dependent SCN,然后保证数据库可以全库的导出,然后重建数据库导入数据。执行:

alter session set events ‘IMMEDIATE trace name ADJUST_SCN level 1’;

SQL>  startup pfile=/orac/orahome/11.2.0/db_1/dbs/initdbservice.ora mount
ORACLE instance started.

Total System Global Area 1.3495E+10 bytes
Fixed Size                  2218032 bytes
Variable Size            5838473168 bytes
Database Buffers         7583301632 bytes
Redo Buffers               71471104 bytes
Database mounted.

SQL>recover database;
Media Recovery Complete 

SQL> alter session set events '10015 trace name adjust_scn level 1';

Session altered.

SQL>  alter database open;

ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/oradata/dbservice/system01.dbf'

检查alert_dbservice.log文件发现

No Resource Manager plan active
Errors in file /orac/diag/rdbms/dbservice/dbservice/trace/dbservice_ora_5571.trc  (incident=25369):
ORA-00600: internal error code, arguments: [4193], [], [], [], [], [], [], [], [], [], [], []

说明:600-[4000]到[5000]的错误都是和rollback 相关的

可以先使用手功管理的回滚表空间的方式将数据库打开
设置如下参数
undo_tablespace=’SYSTEM’
undo_management=’MANUAL’

[root@gzybtest dbservice]# vi /orac/orahome/11.2.0/db_1/dbs/initdbservice.ora
dbservice.__db_cache_size=7583301632
dbservice.__java_pool_size=67108864
dbservice.__large_pool_size=67108864
dbservice.__oracle_base='/orac'#ORACLE_BASE set from environment
dbservice.__pga_aggregate_target=4764729344
dbservice.__sga_target=8791261184
dbservice.__shared_io_pool_size=0
dbservice.__shared_pool_size=872415232
dbservice.__streams_pool_size=67108864
*._allow_resetlogs_corruption=TRUE
*._disable_logging=FALSE
*.audit_file_dest='/orac/admin/dbservice/adump'
*.audit_trail='none'
*.compatible='11.2.0.0.0'
*.control_files='/oradata/dbservice/control01.ctl','/orac/flash_recovery_area/dbservice/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_files=500
*.db_name='dbservic'
*.db_recovery_file_dest='/orac/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.db_unique_name='dbservice'
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/orac'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dbserviceXDB)'
*.memory_target=13501464576
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='SYSTEM'
undo_management='MANUAL'

SQL>  startup pfile=/orac/orahome/11.2.0/db_1/dbs/initdbservice.ora
ORACLE instance started.

Total System Global Area 1.3495E+10 bytes
Fixed Size                  2218032 bytes
Variable Size            5838473168 bytes
Database Buffers         7583301632 bytes
Redo Buffers               71471104 bytes
Database mounted.
Database opened.

接下来创建新的undo表空间并修改initdbservice.ora文件的undo表空间相关的参数

SQL> create undo tablespace undotbs02 datafile '/oradata/dbservice/undotbs02.dbf' size 500M;

Tablespace created.

[root@gzybtest dbservice]# vi /orac/orahome/11.2.0/db_1/dbs/initdbservice.ora
dbservice.__db_cache_size=7583301632
dbservice.__java_pool_size=67108864
dbservice.__large_pool_size=67108864
dbservice.__oracle_base='/orac'#ORACLE_BASE set from environment
dbservice.__pga_aggregate_target=4764729344
dbservice.__sga_target=8791261184
dbservice.__shared_io_pool_size=0
dbservice.__shared_pool_size=872415232
dbservice.__streams_pool_size=67108864
*._allow_resetlogs_corruption=TRUE
*._disable_logging=FALSE
*.audit_file_dest='/orac/admin/dbservice/adump'
*.audit_trail='none'
*.compatible='11.2.0.0.0'
*.control_files='/oradata/dbservice/control01.ctl','/orac/flash_recovery_area/dbservice/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_files=500
*.db_name='dbservic'
*.db_recovery_file_dest='/orac/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.db_unique_name='dbservice'
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/orac'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dbserviceXDB)'
*.memory_target=13501464576
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='undotbs02'
undo_management='AUTO'

SQL> startup pfile=/orac/orahome/11.2.0/db_1/dbs/initdbservice.ora
ORACLE instance started.

Total System Global Area 1.3495E+10 bytes
Fixed Size                  2218032 bytes
Variable Size            5838473168 bytes
Database Buffers         7583301632 bytes
Redo Buffers               71471104 bytes
Database mounted.
Database opened.
SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      undotbs02
SQL> create spfile from pfile;

File created.

使用spfile参数文件来启动数据库

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 1.3495E+10 bytes
Fixed Size                  2218032 bytes
Variable Size            5838473168 bytes
Database Buffers         7583301632 bytes
Redo Buffers               71471104 bytes
Database mounted.
Database opened.

数据库打开了,本以为搞定了数据库的恢复但是过了一分钟连接数据库进行操作时报错

SQL> ALTER DATABASE  DATAFILE '/oradata/dbservice/undotbs01.dbf' OFFLINE DROP;
ALTER DATABASE  DATAFILE '/oradata/dbservice/undotbs01.dbf' OFFLINE DROP
*
ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 8532
Session ID: 96 Serial number: 3

检查alert_dbservice.log文件发现

Errors in file /orac/diag/rdbms/dbservice/dbservice/trace/dbservice_smon_7186.trc  (incident=32527):
ORA-00600: internal error code, arguments: [4137], [2.18.4491], [0], [0], [], [], [], [], [], [], [], []
Thu Aug 08 23:14:00 2013

错误原因:
_smon_internal_errlimit(limit of SMON internal errors) SMON遇到了内部错误,最大允许100次,不断计数增长,达到100的时候,数据库smon进程自动down掉,从而导致数据库down

解决办法:
1.临时解决办法:设置_smon_internal_errlimit一个较大值
2.根本解决办法:使用undo隐含参数,删除有问题undo 回滚段和undo表空间或者使用10513 事件

我这里选择删除有问题的undo表空间

SQL> drop tablespace UNDOTBS1 including contents and datafiles;
drop tablespace UNDOTBS1 including contents and datafiles
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU3_2097677531$' found, terminate
dropping tablespace

执行下面的查询来看undotbs1表空间的回滚段

SQL> SELECT SEGMENT_NAME, OWNER, TABLESPACE_NAME, STATUS FROM DBA_ROLLBACK_SEGS;

SEGMENT_NAME                   OWNER  TABLESPACE_NAME
------------------------------ ------ ------------------------------
STATUS
----------------
SYSTEM                         SYS    SYSTEM
ONLINE

_SYSSMU13_2246115931$          PUBLIC UNDOTBS1
OFFLINE

_SYSSMU12_1541645018$          PUBLIC UNDOTBS1
NEEDS RECOVERY

SEGMENT_NAME                   OWNER  TABLESPACE_NAME
------------------------------ ------ ------------------------------
STATUS
----------------
_SYSSMU11_1259892418$          PUBLIC UNDOTBS1
OFFLINE

_SYSSMU10_3550978943$          PUBLIC UNDOTBS1
OFFLINE

_SYSSMU9_1424341975$           PUBLIC UNDOTBS1
OFFLINE

SEGMENT_NAME                   OWNER  TABLESPACE_NAME
------------------------------ ------ ------------------------------
STATUS
----------------
_SYSSMU8_2012382730$           PUBLIC UNDOTBS1
OFFLINE

_SYSSMU7_3286610060$           PUBLIC UNDOTBS1
OFFLINE

_SYSSMU6_2443381498$           PUBLIC UNDOTBS1
OFFLINE

SEGMENT_NAME                   OWNER  TABLESPACE_NAME
------------------------------ ------ ------------------------------
STATUS
----------------
_SYSSMU5_1527469038$           PUBLIC UNDOTBS1
OFFLINE

_SYSSMU4_1152005954$           PUBLIC UNDOTBS1
OFFLINE

_SYSSMU3_2097677531$           PUBLIC UNDOTBS1
NEEDS RECOVERY

SEGMENT_NAME                   OWNER  TABLESPACE_NAME
------------------------------ ------ ------------------------------
STATUS
----------------
_SYSSMU37_1815119680$          PUBLIC UNDOTBS02
OFFLINE

_SYSSMU36_3725248445$          PUBLIC UNDOTBS02
OFFLINE

_SYSSMU35_2892765787$          PUBLIC UNDOTBS02
OFFLINE

SEGMENT_NAME                   OWNER  TABLESPACE_NAME
------------------------------ ------ ------------------------------
STATUS
----------------
_SYSSMU34_2695217521$          PUBLIC UNDOTBS02
OFFLINE

_SYSSMU33_1797432692$          PUBLIC UNDOTBS02
OFFLINE

_SYSSMU32_1653795157$          PUBLIC UNDOTBS02
OFFLINE

SEGMENT_NAME                   OWNER  TABLESPACE_NAME
------------------------------ ------ ------------------------------
STATUS
----------------
_SYSSMU31_1105695367$          PUBLIC UNDOTBS02
OFFLINE

_SYSSMU30_424248798$           PUBLIC UNDOTBS02
OFFLINE

_SYSSMU29_778801568$           PUBLIC UNDOTBS02
OFFLINE

SEGMENT_NAME                   OWNER  TABLESPACE_NAME
------------------------------ ------ ------------------------------
STATUS
----------------
_SYSSMU28_2939988898$          PUBLIC UNDOTBS02
OFFLINE

_SYSSMU27_220897705$           PUBLIC UNDOTBS02
OFFLINE

_SYSSMU26_153095121$           PUBLIC UNDOTBS02
OFFLINE

SEGMENT_NAME                   OWNER  TABLESPACE_NAME
------------------------------ ------ ------------------------------
STATUS
----------------
_SYSSMU25_193585708$           PUBLIC UNDOTBS02
OFFLINE

_SYSSMU24_3014414866$          PUBLIC UNDOTBS02
OFFLINE

_SYSSMU23_2910579661$          PUBLIC UNDOTBS02
OFFLINE

SEGMENT_NAME                   OWNER  TABLESPACE_NAME
------------------------------ ------ ------------------------------
STATUS
----------------
_SYSSMU22_2488176863$          PUBLIC UNDOTBS02
OFFLINE

_SYSSMU21_1905929509$          PUBLIC UNDOTBS02
OFFLINE

_SYSSMU20_3032273491$          PUBLIC UNDOTBS02
OFFLINE

SEGMENT_NAME                   OWNER  TABLESPACE_NAME
------------------------------ ------ ------------------------------
STATUS
----------------
_SYSSMU19_2000855474$          PUBLIC UNDOTBS02
OFFLINE

_SYSSMU18_729116992$           PUBLIC UNDOTBS02
OFFLINE

_SYSSMU17_388387478$           PUBLIC UNDOTBS02
OFFLINE

SEGMENT_NAME                   OWNER  TABLESPACE_NAME
------------------------------ ------ ------------------------------
STATUS
----------------
_SYSSMU16_241755401$           PUBLIC UNDOTBS02
OFFLINE

_SYSSMU15_1810545622$          PUBLIC UNDOTBS02
OFFLINE

_SYSSMU14_714084753$           PUBLIC UNDOTBS02
OFFLINE

[root@gzybtest admin]# vi /orac/orahome/11.2.0/db_1/dbs/initdbservice.ora
dbservice.__db_cache_size=7583301632
dbservice.__java_pool_size=67108864
dbservice.__large_pool_size=67108864
dbservice.__oracle_base='/orac'#ORACLE_BASE set from environment
dbservice.__pga_aggregate_target=4764729344
dbservice.__sga_target=8791261184
dbservice.__shared_io_pool_size=0
dbservice.__shared_pool_size=872415232
dbservice.__streams_pool_size=67108864
*._allow_resetlogs_corruption=TRUE
*._disable_logging=FALSE
*.audit_file_dest='/orac/admin/dbservice/adump'
*.audit_trail='none'
*.compatible='11.2.0.0.0'
*.control_files='/oradata/dbservice/control01.ctl','/orac/flash_recovery_area/dbservice/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_files=500
*.db_name='dbservic'
*.db_recovery_file_dest='/orac/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.db_unique_name='dbservice'
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/orac'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=dbserviceXDB)'
*.memory_target=13501464576
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_management='MANUAL'
*.undo_tablespace='undotbs02'
_CORRUPTED_ROLLBACK_SEGMENTS = (_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7

$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$,_SYSSMU11$,_SYSSMU12$,_SYSSMU13$,_SYSSMU14$,_SYSSMU15$,_SYSSMU16$,_SYSSMU17

$,_SYSSMU18$,_SYSSMU19$,_SYSSMU20$,_SYSSMU2_2232571081$,
_SYSSMU3_2097677531$,_SYSSMU4_1152005954$,_SYSSMU5_1527469038$,_SYSSMU6_2443381498$,_SYSSMU7_3286610060

$,_SYSSMU8_2012382730$,
_SYSSMU9_1424341975$,_SYSSMU10_3550978943$,_SYSSMU11_1259892418$,_SYSSMU12_1541645018$,_SYSSMU13_2246115931$)
~
~
~
~
"/orac/orahome/11.2.0/db_1/dbs/initdbservice.ora" 34L, 1602C written
[root@gzybtest admin]# 

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup pfile=/orac/orahome/11.2.0/db_1/dbs/initdbservice.ora
ORACLE instance started.

Total System Global Area 1.3495E+10 bytes
Fixed Size                  2218032 bytes
Variable Size            5838473168 bytes
Database Buffers         7583301632 bytes
Redo Buffers               71471104 bytes
Database mounted.
Database opened.
SQL> drop tablespace UNDOTBS1 including contents and datafiles;

Tablespace dropped.

到此数据恢复终于搞定了,接下来就是备份数据使用exp/expdp来进行逻辑导出