在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来进行逻辑导出

网络设备重启造成rac监听故障连接数据库报ora-12170:TNS:连接超时的错误

由于网络管理员把所有网络设备做了重启造成rac的虚拟IP发生漂移造成监听程序失效应用连接数据库
时报ora-12170:TNS:连接超时的错误
从下面的信息可以看到两个节点的vip漂移到另一个节点上使监听offline了

[root@hnzdb2 bin]# ./crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora....SM1.asm application    ONLINE    ONLINE    hnzdb1
ora....ER.lsnr application    ONLINE    OFFLINE
ora....B1.lsnr application    ONLINE    OFFLINE
ora.hnzdb1.gsd application    ONLINE    ONLINE    hnzdb1
ora.hnzdb1.ons application    ONLINE    ONLINE    hnzdb1
ora.hnzdb1.vip application    ONLINE    ONLINE    hnzdb2
ora....SM2.asm application    ONLINE    ONLINE    hnzdb2
ora....ER.lsnr application    ONLINE    OFFLINE
ora....B2.lsnr application    ONLINE    OFFLINE
ora.hnzdb2.gsd application    ONLINE    ONLINE    hnzdb2
ora.hnzdb2.ons application    ONLINE    ONLINE    hnzdb2
ora.hnzdb2.vip application    ONLINE    ONLINE    hnzdb1
ora.orcl.db    application    ONLINE    ONLINE    hnzdb1
ora....l1.inst application    ONLINE    ONLINE    hnzdb1
ora....l2.inst application    ONLINE    ONLINE    hnzdb2

尝试重启所有服务报虚拟ip漂移的错误信息

[root@hnzdb2 bin]# ./crs_start -all
hnzdb1 : CRS-1019: Resource ora.hnzdb2.LISTENER.lsnr (application) cannot run on
 hnzdb1
hnzdb2 : CRS-1018: Resource ora.hnzdb2.vip (application) is already running on h
nzdb1

hnzdb1 : CRS-1018: Resource ora.hnzdb1.vip (application) is already running on h
nzdb2
hnzdb2 : CRS-1019: Resource ora.hnzdb1.LISTENER.lsnr (application) cannot run on
 hnzdb2

hnzdb1 : CRS-1019: Resource ora.hnzdb2.LISTENER_HNZDB2.lsnr (application) cannot
 run on hnzdb1
hnzdb2 : CRS-1018: Resource ora.hnzdb2.vip (application) is already running on h
nzdb1

hnzdb1 : CRS-1018: Resource ora.hnzdb1.vip (application) is already running on h
nzdb2
hnzdb2 : CRS-1019: Resource ora.hnzdb1.LISTENER_HNZDB1.lsnr (application) cannot
 run on hnzdb2

CRS-0223: Resource 'ora.hnzdb1.LISTENER.lsnr' has placement error.

CRS-0223: Resource 'ora.hnzdb1.LISTENER_HNZDB1.lsnr' has placement error.

CRS-0223: Resource 'ora.hnzdb2.LISTENER.lsnr' has placement error.

CRS-0223: Resource 'ora.hnzdb2.LISTENER_HNZDB2.lsnr' has placement error.

先停止所有服务再来重启

[root@hnzdb2 bin]# ./crs_stop -all
Attempting to stop `ora.hnzdb2.gsd` on member `hnzdb2`
Attempting to stop `ora.hnzdb2.ons` on member `hnzdb2`
Stop of `ora.hnzdb2.gsd` on member `hnzdb2` succeeded.
Attempting to stop `ora.hnzdb1.gsd` on member `hnzdb1`
Attempting to stop `ora.orcl.db` on member `hnzdb1`
Attempting to stop `ora.hnzdb1.ons` on member `hnzdb1`
Stop of `ora.hnzdb2.ons` on member `hnzdb2` succeeded.
Stop of `ora.hnzdb1.gsd` on member `hnzdb1` succeeded.
Stop of `ora.hnzdb1.ons` on member `hnzdb1` succeeded.
^[Stop of `ora.orcl.db` on member `hnzdb1` succeeded.
`ora.orcl.orcl1.inst` is already OFFLINE.
`ora.orcl.orcl2.inst` is already OFFLINE.
Attempting to stop `ora.hnzdb2.ASM2.asm` on member `hnzdb2`
Target set to OFFLINE for `ora.hnzdb2.LISTENER_HNZDB2.lsnr`
Target set to OFFLINE for `ora.hnzdb1.LISTENER_HNZDB1.lsnr`
Attempting to stop `ora.hnzdb1.ASM1.asm` on member `hnzdb1`
Target set to OFFLINE for `ora.hnzdb2.LISTENER.lsnr`
Attempting to stop `ora.hnzdb2.vip` on member `hnzdb1`
Target set to OFFLINE for `ora.hnzdb1.LISTENER.lsnr`
Attempting to stop `ora.hnzdb1.vip` on member `hnzdb2`
Stop of `ora.hnzdb2.vip` on member `hnzdb1` succeeded.
Stop of `ora.hnzdb1.vip` on member `hnzdb2` succeeded.

[root@hnzdb2 bin]# ./crs_start -all

oracle result cache 结果集缓存的使用

结果集缓存
缓存是一种最常见的在计算机系统用来提高性能的技术.硬件和软件被广泛的使用.oracle数据库也不会例外.例如在缓冲区缓存中缓存数据文件块,在字典缓存中缓存字典信息,在库缓存中缓存游标.在oracle11G中结果集缓存也是可用的.

结果集缓存是如何工作的
oracle数据库引擎提供了三种结果集缓存:
服务器结果集缓存也叫查询结果缓存,是一种服务器端缓存,它用来存储查询的结果集.

pl/sql函数结果集缓存是一种服务器端缓存,它用来存储pl/sql函数返回的结果集.

客户端结果集缓存是一种客户端缓存,用来存储查询结果集.

服务器结果集缓存
服务器结果集缓存是用来避免重复执行查询.简单来说查询第一次执行,它的结果集被存储在共享池中.然后后续执行相同的查询时从结果集缓存中直接提取结果集而不用重新计算.注意这两个查询被认为是相等的.因此能使用相同的结果集.另外如果出现绑定变量,那么它们的值必须相同.这是必须的,因为很明显,绑定变量作为参数输入并传给查询.因此不同的绑定变量值会有不同的结果集.注意结果集缓存是存储在共享池中,对于一个指定的实例所有连接的会话都能共享相同的缓存条目.

下面来举例说明.注意在查询时指定了result_cache提示来启用结果集缓存.第一次执行花了1.04秒.可以看到在执行计划中操作result cache确定了对查询启用了结果集缓存.在执行计划中starts列清楚的说明了所有的操作都至少要被执行一次.执行计划中所有的操作都是必须的因为是第一次执行这个查询.因此结果集缓存还不包含结果集.

SQL> alter session set statistics_level=all;

SQL> SELECT /*+ result_cache */
  2  p.prod_category, c.country_id,
  3  sum(s.quantity_sold) AS quantity_sold,
  4  sum(s.amount_sold) AS amount_sold
  5  FROM sh.sales s, sh.customers c, sh.products p
  6  WHERE s.cust_id = c.cust_id
  7  AND s.prod_id = p.prod_id
  8  GROUP BY p.prod_category, c.country_id
  9  ORDER BY p.prod_category, c.country_id;

已选择81行。

已用时间:  00: 00: 01.04

SQL> select * from table(dbms_xplan.display_cursor(null,null,'all allstats'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

SQL_ID  g9sxqz9qgutu7, child number 0
-------------------------------------
SELECT /*+ result_cache */ p.prod_category, c.country_id,
sum(s.quantity_sold) AS quantity_sold, sum(s.amount_sold) AS
amount_sold FROM sh.sales s, sh.customers c, sh.products p WHERE
s.cust_id = c.cust_id AND s.prod_id = p.prod_id GROUP BY
p.prod_category, c.country_id ORDER BY p.prod_category, c.country_id

Plan hash value: 1866882273

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


PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

| Id  | Operation                 | Name                       | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   |  OMem |  1Mem |  O/1/M   |

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                            |      1 |        |       |   985 (100)|          |       |       |     81 |00:00:00.01 |       |       |          |

|   1 |  RESULT CACHE             | cu8pf4s7jn9d05yda1swvwpd7y |      1 |        |       |            |          |       |       |     81 |00:00:00.01 |       |       |          |

|   2 |   SORT GROUP BY           |                            |      1 |     68 |  3808 |   985   (7)| 00:00:12 |       |       |      0 |00:00:00.01 | 73728 | 73728 |          |

|*  3 |    HASH JOIN              |                            |      1 |    968 | 54208 |   984   (7)| 00:00:12 |       |       |      0 |00:00:00.01 |   940K|   940K|          |

|   4 |     VIEW                  | index$_join$_003           |      1 |     72 |  1512 |     3  (34)| 00:00:01 |       |       |      0 |00:00:00.01 |       |       |          |

|*  5 |      HASH JOIN            |                            |      1 |        |       |            |          |       |       |      0 |00:00:00.01 |  1269K|  1269K|          |

|   6 |       INDEX FAST FULL SCAN| PRODUCTS_PK                |      1 |     72 |  1512 |     1   (0)| 00:00:01 |       |       |      0 |00:00:00.01 |       |       |          |

|   7 |       INDEX FAST FULL SCAN| PRODUCTS_PROD_CAT_IX       |      1 |     72 |  1512 |     1   (0)| 00:00:01 |       |       |      0 |00:00:00.01 |       |       |          |

|   8 |     VIEW                  | VW_GBC_9                   |      1 |    968 | 33880 |   981   (7)| 00:00:12 |       |       |      0 |00:00:00.01 |       |       |          |

|   9 |      HASH GROUP BY        |                            |      0 |    968 | 26136 |   981   (7)| 00:00:12 |       |       |      0 |00:00:00.01 |    56M|  4744K|          |

|* 10 |       HASH JOIN           |                            |      1 |    918K|    23M|   941   (3)| 00:00:12 |       |       |      0 |00:00:00.01 |  3056K|  1398K|          |

|  11 |        TABLE ACCESS FULL  | CUSTOMERS                  |      1 |  55500 |   541K|   406   (1)| 00:00:05 |       |       |      0 |00:00:00.01 |       |       |          |

|  12 |        PARTITION RANGE ALL|                            |      1 |    918K|    14M|   530   (3)| 00:00:07 |     1 |    28 |      0 |00:00:00.01 |       |       |          |

|  13 |         TABLE ACCESS FULL | SALES                      |     28 |    918K|    14M|   530   (3)| 00:00:07 |     1 |    28 |      0 |00:00:00.01 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

第二次执行只花了0.03秒.在执行计划中starts列显示了所有操作的执行次数.RESULT CACHE被执一次.其它的操作没有被执行.换句话说直接使用了存储在结果集缓存中的结果集.

SQL> SELECT /*+ result_cache */
  2  p.prod_category, c.country_id,
  3  sum(s.quantity_sold) AS quantity_sold,
  4  sum(s.amount_sold) AS amount_sold
  5  FROM sh.sales s, sh.customers c, sh.products p
  6  WHERE s.cust_id = c.cust_id
  7  AND s.prod_id = p.prod_id
  8  GROUP BY p.prod_category, c.country_id
  9  ORDER BY p.prod_category, c.country_id;

已选择81行。

已用时间:  00: 00: 00.03

SQL> select *  from table(dbms_xplan.display_cursor(null,null,'all allstats'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

SQL_ID  g9sxqz9qgutu7, child number 0
-------------------------------------
SELECT /*+ result_cache */ p.prod_category, c.country_id,
sum(s.quantity_sold) AS quantity_sold, sum(s.amount_sold) AS
amount_sold FROM sh.sales s, sh.customers c, sh.products p WHERE
s.cust_id = c.cust_id AND s.prod_id = p.prod_id GROUP BY
p.prod_category, c.country_id ORDER BY p.prod_category, c.country_id

Plan hash value: 1866882273

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


PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

| Id  | Operation                 | Name                       | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | Pstart| Pstop | A-Rows |   A-Time   |  OMem |  1Mem |  O/1/M   |

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                            |      1 |        |       |   985 (100)|          |       |       |    162 |00:00:00.01 |       |       |          |

|   1 |  RESULT CACHE             | cu8pf4s7jn9d05yda1swvwpd7y |      1 |        |       |            |          |       |       |    162 |00:00:00.01 |       |       |          |

|   2 |   SORT GROUP BY           |                            |      0 |     68 |  3808 |   985   (7)| 00:00:12 |       |       |      0 |00:00:00.01 | 73728 | 73728 |          |

|*  3 |    HASH JOIN              |                            |      0 |    968 | 54208 |   984   (7)| 00:00:12 |       |       |      0 |00:00:00.01 |   940K|   940K|          |

|   4 |     VIEW                  | index$_join$_003           |      0 |     72 |  1512 |     3  (34)| 00:00:01 |       |       |      0 |00:00:00.01 |       |       |          |

|*  5 |      HASH JOIN            |                            |      0 |        |       |            |          |       |       |      0 |00:00:00.01 |  1269K|  1269K|          |

|   6 |       INDEX FAST FULL SCAN| PRODUCTS_PK                |      0 |     72 |  1512 |     1   (0)| 00:00:01 |       |       |      0 |00:00:00.01 |       |       |          |

|   7 |       INDEX FAST FULL SCAN| PRODUCTS_PROD_CAT_IX       |      0 |     72 |  1512 |     1   (0)| 00:00:01 |       |       |      0 |00:00:00.01 |       |       |          |

|   8 |     VIEW                  | VW_GBC_9                   |      0 |    968 | 33880 |   981   (7)| 00:00:12 |      |        |      0 |00:00:00.01 |       |       |          |

|   9 |      HASH GROUP BY        |                            |      0 |    968 | 26136 |   981   (7)| 00:00:12 |      |        |      0 |00:00:00.01 |    56M|  4744K|          |

|* 10 |       HASH JOIN           |                            |      0 |    918K|    23M|   941   (3)| 00:00:12 |      |        |      0 |00:00:00.01 |  3056K|  1398K|          |

|  11 |        TABLE ACCESS FULL  | CUSTOMERS                  |      0 |  55500 |   541K|   406   (1)| 00:00:05 |      |        |      0 |00:00:00.01 |       |       |          |

|  12 |        PARTITION RANGE ALL|                            |      0 |    918K|    14M|   530   (3)| 00:00:07 |    1 |     28 |      0 |00:00:00.01 |       |       |          |

|  13 |         TABLE ACCESS FULL | SALES                      |      0 |    918K|    14M|   530   (3)| 00:00:07 |    1 |     28 |      0 |00:00:00.01 |       |       |          |

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

在执行计划中要注意一个名字缓存ID它与操作result cache相关.如果知道这个缓存ID,可以查询v$resul_cache_objects视图来显示关于缓存数据的信息.下面的查询显示缓存结果集已经被发布(换句话说可以使用).视图信息会显示缓存结果集是何进创建的,创建它花了多长时间,有多少行记录被存储和它被引用了多少次.其它提供关于缓存结果集信息的视图还有v$result_cache_dependency,v$result_cache_memory和v$result_chace_statistics.

SQL> SELECT status, creation_timestamp, build_time, row_count, scan_count
  2  FROM v$result_cache_objects
  3  WHERE cache_id = 'cu8pf4s7jn9d05yda1swvwpd7y';
 
STATUS    CREATION_TIMESTAMP BUILD_TIME  ROW_COUNT SCAN_COUNT
--------- ------------------ ---------- ---------- ----------
Published 2013-7-5 9:21:26           94         81          3

为了保证结果的一致性(也就是说结果集是相同的就是看它是来自缓存还是来自数据库计算).每当查询中所引用的对象发生改变,那么依赖于这些表的缓存条目将会无效.事实情况就是这样,即使真正的改变没有发生.例如.甚至一个select * for update语句后面紧跟着一个commit提交也会导致依赖于select表的缓存条目变为无效.

有一些动态初始化参数可以控制服务器结果集缓存:
result_cache_max_size:以byte为单位来指定在共享池中用于结果集缓存的内存总量.如果它被设置为0,这个功能将会被禁用.缺省值是一个比0大的值,它是从共享池中派生出来的.内存分配是动态的,因此初始化参数只能指定它的上限.可以使用下面的查询来显示当前分配的内存大小:

SQL> SELECT name, sum(bytes) FROM v$sgastat WHERE name LIKE 'Result Cache%' GROUP BY rollup(name);
 
NAME                       SUM(BYTES)
-------------------------- ----------
Result Cache                   161680
Result Cache: Bloom Fltr         2048
Result Cache: Cache Mgr           208
Result Cache: Memory Mgr          200
Result Cache: State Objs         2896
                               167032
下面的语句显示了resulr_cache_max_size的值为15424K
SQL> show parameter result
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_lag              big integer 3000
client_result_cache_size             big integer 0
result_cache_max_result              integer     5
result_cache_max_size                big integer 15424K
result_cache_mode                    string      MANUAL
result_cache_remote_expiration       integer     0

result_cache_mode:指定在什么情况下使用结果集缓存.可以将它设置为manual手动这是缺省值或者设置为force.当使用manual时结果集缓存只有当指定result_cache提示时才使用.当使用force时那么会对所有没有使用no_result_cache提示的所有查询使用结果集缓存.因为在大多数情况下我们只会对少量的查询使用结果集缓存,所以让该参数设置为它的缺省值manual当只需要使用时在查询中指定result_cache来启用它.

result_cache_max_result:指定单一个结果集缓存的占用result_cache_max_size服务器结果集缓存大小的百分比.它的缺省值是5.它的值可以是0到100的任何一个数字.超过这个限制的结果集缓存将是无效的.

result_cache_remote_expiration:指定基于远程对象的结果集的有效时间(以分钟为单位).这是必须的,因当远程对象已经发生改变了基于这些远程对象的失效的结果集缓存不能被执行.当经过初始化参数所指定的有效时间后这些结果集才变为无效.这个参数的缺省值是0,意味着基于远程对象的查询缓存功能被禁用.

初始化参数result_cache_max_size和result_cache_max_result只能在系统级别进行修改.其它的result_cache_mode
和result_cache_remote_expiration能在会话级别进行修改.

注意:将result_cache_remote_expiration参数设置为一个比0大的数字会导致过时的结果集缓存存在.因此只有你真正地理解这样做的影响后才能将它设置为比0大.

使用结果集缓存有几个很明显的限制:
查询使用非确定性的函数,序列和临时表是结果集不会被缓存

查询违反了读一致性时结果集将不会被缓存.例如,当通过会话创建结果集时所引用的表正经历大量的事务这样的结果
集不会被缓存.

引用数据字典视图的查询的结果集不会被缓存.

dbms_result_cache包
可以使用dbms_result_cache包来管理结果集缓存,它提供了以下程序来进行管理:
bypass:在会话或系统级别临时禁用或启用结果集缓存.

flush:从结果集缓存中清空所有对象

invalidate:让依赖于指定的数据库对象的所有结果集缓存变为无效

invalidate_object:让单个结果集缓存条目变为无效

memory_report:生成一个内存使用情况报告

status:显示结果集缓存的状态.

例如:

SQL> select dbms_result_cache.status from dual;
 
STATUS
--------------------------------------------------------------------------------
ENABLED

pl/sql函数结果集缓存
pl/sql函数结果集缓存类似于服务器结果集缓存,但是它支持pl/sql函数,还和服务器结果集缓存共享相同的内存结构.它的目的是在结果集缓存中存储函数返回的值(仅仅只有函数返回的值,过程返回的值不能被缓存).明显地使用不同输入值的函数是以不同的缓存条目被缓存在结果集缓存中的.在下面的例子中显示了一个启用结果集缓存的函数.为了启用pl/sql函数结果集缓存,要指定result_cache子句.还可以指定relies_on子句来指定函数依赖于哪个表来返回结果.

SQL> CREATE OR REPLACE FUNCTION f
  2    RETURN NUMBER
  3    RESULT_CACHE RELIES_ON(t) IS
  4    l_ret NUMBER;
  5  BEGIN
  6    SELECT count(t.indi_id) INTO l_ret FROM impl_chenzhou.bs_insured t;
  7    RETURN l_ret;
  8  END;
  9  /
 
Function created

在下面的例子中将会调用函数2480625次f不使用结果集缓存(通过使用bypass过程来临时禁用结果集缓存)共用了4.69秒

SQL> execute dbms_result_cache.bypass(bypass_mode => TRUE, session => TRUE);

SQL> select count(f) from impl_chenzhou.bs_insured t;

  COUNT(F)
----------
   2480625

Elapsed: 00:00:04.69

在下面的例子中将会调用函数2480625次f使用结果集缓存共用了0.32秒
SQL> execute dbms_result_cache.bypass(bypass_mode => FALSE, session => TRUE)

SQL> select count(f) from impl_chenzhou.bs_insured t;

  COUNT(F)
----------
   2480625

Elapsed: 00:00:00.32

注意:如果relies_on子句没有指定或者包含错误信息,当函数依赖的对象发生修改结果集缓存不会变为无效.因此可能会出现过时的结果集.

使用pl/sql函数结果集有一些限制,下面的函数不能使用结果集缓存:
使用out和(或者)in out参数的函数

定义了使用调用者权限的函数

管道化表函数

从匿名块调用函数

使用in参数或者返回值有以下类型的函数:LOB,REF CURSOR,对象和记录

客户端结果缓存集
客户端结果集缓存是用客户端缓存来存查询的结果集.它的目的和工作类似于服务器端结果集缓存.与服务器端的实现进行比较有两个重要的不同.第一它避免了需要在客户端/服务器之间来回地执行sql语句.这是一大优点.第二结果集的失效是基于一种轮询机制,因此一致性不能保证这是一大缺点.

为了实现这种轮询客户端必须定期地执行数据库调用来检查数据库引擎看看它的结果集缓存是否已经变为无效了.为了轮询的开销最小化,每一次客户端由于其它原因执行一个数据库调用时它将检查结果集缓存的有效性.这样,就可以避免掉那些用于对缓存的结果集进行失效操作的数据库调用.使客户端能持续地执行”正常的”数据库调用.

尽管它是客户端缓存但还是要在服务器端来启用它.下面有些参数来控制客户端缓存:
client_result_cache_size:指定每一个客户端进程能使用的结果集缓存的最大内存大小以byte为单位.如果它设置为0,这也是缺省值那么这个功能将禁用.这个初始化参数是静态的只能在实例级别进行修改.修改完后必须对实例进行重启才会生效.

client_result_cache_lag:指定两次数据库调用之间的最大时间间隔以毫秒为单位.也就是说它指定无效的结果集能在客户端缓存中保留多长的时间.缺省值是3000.这个初始化参数是静态的只能在实例级别进行修改.修改完后必须对实例进行重启才会生效.

oci_result_cache_max_size:会覆盖服务器端设置的初始化参数client_result_cache-size.然而要注意是如果在服务器禁用了结果集缓存这个参数将不能激活它.

oci_result_cache_max_rset_size:指定单个结果集缓存最多能使用的内存大小以byte为单位.

oci_result_cache_max_rset_rows:指定单个结果集缓存最多能存储的返回行数.

何时使用
当你遇到由程序反复执行相同的操作所导致的性能问题时,你要么减少执行的频率要么减少操作的响应时间.理想的情况时两者都做.然而有时(例如由于应用程序代码不能修改)你只能实现后者.为了减少响应时间可以使用各种优化技术,如果还不能满足要求那就只能使用高级优化技术了象结果集缓存.基本上要有效使用结果集缓存要满足两个条件.第一相对于修改数据来说同样的数据查询的更频繁.第二要有足够的内存来存储结果集.

在大多数情况下不能对所有的查询都启用结果集缓存.事实上在大多数时候只有特定的查询才能从结果集缓存中获益而对于其它的情况来说,结果集缓存只不过是一种纯粹的额外的开销说不定还会使用缓存过载.还要记住的是服务器端缓存是对所有会话共享的,因此它们的访问是要同步的(它们也会象所有的共享资源一样变成一个串处理点).因此只有在真正查询请求它们的时候才会结果集缓存.也就是说只当真正需要使用它们来提高性能时才在查询中指定result_cache提示.

服务器端结果集缓存无法完全避免执行一个查询的额外开销.这意味着如果一个查询在不使用结果集缓存的情况下对于每一行执行了最少的逻辑读(不是物理读)了,那么使用结果集缓存性能不会提高很多.请记住高速缓存和结果集缓存都是存储在相同的共享内存中的.

pl/sql函数结果集缓存对于经常在sql语句中使用的函数来说特别有用.事实上常遇到这样的情况被处理或被返回的每一行都会调用一次这个函数,同时输入的参数也只有几个不同的值,然而这个函数经常从pl/sql中被频繁地调用它能使用结果集缓存.

因为一致性的问题客户端缓存只应该用在只读表或主要是读的表上.

最后要注意的是可以同进使用服务器和客户端结果集缓存.然而对于客户端执行的查询不能选择只使用服务器结果集缓存而不使用客户端结果集缓存.也就是说两种结果集缓存都会被使用.

通过dbms_mviewdbms_mview.explain_rewrite检测为什么不使用查询重写的问题

先创建一个物化视图使用最简单的语法来创建

create materialized view  sales_customers_products as
SELECT p.prod_category, c.country_id,
sum(s.quantity_sold) AS quantity_sold,
sum(s.amount_sold) AS amount_sold
FROM sh.sales s, sh.customers c, sh.products p
WHERE s.cust_id = c.cust_id
AND s.prod_id = p.prod_id
and c.country_id='Ruddy'
GROUP BY p.prod_category, c.country_id
ORDER BY p.prod_category, c.country_id;
/

创建rewrite_table表
SQL> @d:/oracle/product/10.2.0/db_1/rdbms/admin/utlxrw.sql

Table created

检查查询重写的参数设置
SQL> show parameter query

NAME TYPE VALUE
———————————— ———– —————–
query_rewrite_enabled string TRUE
query_rewrite_integrity string enforced

SQL> DECLARE
  2   l_query CLOB := 'SELECT p.prod_category, c.country_id,
  3  sum(s.quantity_sold) AS quantity_sold,
  4  sum(s.amount_sold) AS amount_sold
  5  FROM sh.sales s, sh.customers c, sh.products p
  6  WHERE s.cust_id = c.cust_id
  7  AND s.prod_id = p.prod_id
  8  GROUP BY p.prod_category, c.country_id
  9  ORDER BY p.prod_category, c.country_id';
 10   BEGIN
 11   dbms_mview.explain_rewrite(
 12   query => l_query,
 13   mv => 'sales_customers_products',
 14   statement_id => '42'
 15   );
 16  END;
 17  /

PL/SQL procedure successfully completed;

SQL> select message from rewrite_table;

MESSAGE
-------------------------------------------------------------------
QSM-01150: 未重写查询
QSM-01052: 表的引用完整性约束条件 PRODUCTS 在 ENFORCED 完整性模式中无效
QSM-01026: 对 SALES_CUSTOMERS_PRODUCTS 禁用查询重写


/
SQL>drop materialized view  sales_customers_products ;

下面在创建物化视图时启用查询重写
create materialized view  sales_customers_products
ENABLE QUERY REWRITE
as
SELECT p.prod_category, c.country_id,
sum(s.quantity_sold) AS quantity_sold,
sum(s.amount_sold) AS amount_sold
FROM sh.sales s, sh.customers c, sh.products p
WHERE s.cust_id = c.cust_id
AND s.prod_id = p.prod_id
GROUP BY p.prod_category, c.country_id
ORDER BY p.prod_category, c.country_id;


SQL> DECLARE
  2   l_query CLOB := 'SELECT p.prod_category, c.country_id,
  3  sum(s.quantity_sold) AS quantity_sold,
  4  sum(s.amount_sold) AS amount_sold
  5  FROM sh.sales s, sh.customers c, sh.products p
  6  WHERE s.cust_id = c.cust_id
  7  AND s.prod_id = p.prod_id
  8  GROUP BY p.prod_category, c.country_id
  9  ORDER BY p.prod_category, c.country_id';
 10   BEGIN
 11   dbms_mview.explain_rewrite(
 12   query => l_query,
 13   mv => 'sales_customers_products',
 14   statement_id => '43'
 15   );
 16  END;
 17  /

PL/SQL procedure successfully completed

SQL>select message from rewrite_table where statement_id='43';

MESSAGE
-------------------------------------------------------------------
QSM-01151: 已重写查询
QSM-01209: 已通过实体化视图 SALES_CUSTOMERS_PRODUCTS, 采用文本匹配算法进行了查询重写
/

		

如何能在执行计划中看到Starts

SQL> explain plan for
  2
  2  SELECT *
  3  FROM scott.emp
  4  WHERE NOT EXISTS (SELECT 0
  5  FROM scott.dept
  6  WHERE dept.dname = 'SALES' AND dept.deptno = emp.deptno)
  7  AND NOT EXISTS (SELECT 0
  8  FROM scott.bonus
  9  WHERE bonus.ename = emp.ename);

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 734347697
-----------------------------------------------------------------------------
| Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       |     9 |   522 |     9  (12)| 00:00:01 |
|*  1 |  HASH JOIN ANTI     |       |     9 |   522 |     9  (12)| 00:00:01 |
|*  2 |   HASH JOIN ANTI    |       |     9 |   459 |     7  (15)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMP   |    14 |   532 |     3   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| DEPT  |     1 |    13 |     3   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL | BONUS |     1 |     7 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("BONUS"."ENAME"="EMP"."ENAME")
   2 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
   4 - filter("DEPT"."DNAME"='SALES')

使用advanced也不显示
SQL> explain plan for select * from scott.emp;

Explained

SQL> SELECT * FROM table(dbms_xplan.display(NULL,NULL,'advanced'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |   532 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |   532 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / EMP@SEL$1
Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      FULL(@"SEL$1" "EMP"@"SEL$1")

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('11.2.0.1')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - "EMP"."EMPNO"[NUMBER,22], "EMP"."ENAME"[VARCHAR2,10],
       "EMP"."JOB"[VARCHAR2,9], "EMP"."MGR"[NUMBER,22],
       "EMP"."HIREDATE"[DATE,7], "EMP"."SAL"[NUMBER,22],
       "EMP"."COMM"[NUMBER,22], "EMP"."DEPTNO"[NUMBER,22]

要得到starts要么在SQLPLUS中将statistics_level设置all,并真实执行sql语句
因为display_cursor是从动态性能视图v$session,v$sql,v$sql_plan和v$sql_plan_statistics_all中获取直接计划的使用explain plan for不行
因为它只生成执行计划并没真正地执行而且执行计划是存储在plan_table表中
并使用display来显示

要么在要执行的sql语句中statistics_level 仍然保留’typical’ ,
然后用这个HINT/*+gather_plan_statistics */
SQL> alter session set statistics_level=all;

会话已更改。

SQL> select count(*) from scott.emp;

COUNT(*)
———-
14

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

SQL_ID  abj9tmfcs15bm, child number 0
-------------------------------------
select count(*) from scott.emp

Plan hash value: 2937609675

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

| Id  | Operation        | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

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

|   0 | SELECT STATEMENT |        |      1 |        |      1 |00:00:00.01 |  1      |

|   1 |  SORT AGGREGATE  |        |      1 |      1 |      1 |00:00:00.01 |  1      |

|   2 |   INDEX FULL SCAN| PK_EMP |      1 |     14 |     14 |00:00:00.01 |  1      |

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

已选择14行。

SQL>

SQL> alter session set statistics_level='typical';

会话已更改。

SQL> select /*+gather_plan_statistics */ count(*) from scott.emp;

  COUNT(*)
----------
        14

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

SQL_ID  2vku9s3sb55tz, child number 0
-------------------------------------
select /*+gather_plan_statistics */ count(*) from scott.emp

Plan hash value: 2937609675

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

| Id  | Operation        | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

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

|   0 | SELECT STATEMENT |        |      1 |        |      1 |00:00:00.01 |  1 |

|   1 |  SORT AGGREGATE  |        |      1 |      1 |      1 |00:00:00.01 |  1 |

|   2 |   INDEX FULL SCAN| PK_EMP |      1 |     14 |     14 |00:00:00.01 |  1 |

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

已选择14行。

使用dbms_xplan包来获得sql语句的执行计划

dbms_xplan包
dbms_xplan包可以用来显示存储在三个不同地方的执行计划:plan_table表,库缓存和awr.下面将会介绍dbms_xplan包中可用的函数.

输出
这里主要解释通过dbms_xplan包中函数返回的信息.下面是dbms_xplan.display_cursor输出信息的第一部分
SQL_ID 9nrttza3c2x2u, child number 0
————————————-
select * from scott.emp where empno=7788
Plan hash value: 2949544139

在这一部信息中指出与sql语句相关的以下信息:
sql_id识别父游标.这个信息只有当使用display_cursor和display_awr时才有

child number与sql_id一起用来识别子游标.这种信息只有当使用display_cursor时才有

sql语句的文本只有当使用display_cursor和display_awr函数时才有

第二部分显示的是一个表中的执行计划哈希值和执行计划本身:

Plan hash value: 2949544139
--------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |       |       |     1 (100)|
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    38 |     1   (0)| 00:0
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)|
--------------------------------------------------------------------------------

在这个表中,评估和每一操作的执行统计都被提供.这个表中的列数直接取决于可用信息的数量.例如,关于分区的信息,并行处理或执行统计.由于这个原因相同的函数和完全相同的参数可能产生两组不同的输出结果.在这种情况下,你将看一下通常可用的列:
包含执行计划的表中的列如下:
列 描述
id 在执行计划中标识每一个操作.如果在数字的前面有一 个星号.它意味着这一行是谓词信息
operation 被执行操作.也叫做行资源操作
name 被执行操作的对象

查询优化器评估
rows(e-rows) 评估操作所返回的行数
bytes(e-bytes) 评估操作所返回的数据量
TempSpc 评估操作使用的临时表空间大小
cost(%cpu) 评估操作的成本.在括号中指出了cpu成本的百分比.这个值是通过执行计划来计算的.
换句话说,父操作的成本包含了子操作的成本
Time 评估执行这个操作需要的时间(HH:MM:SS)

分区信息
pstart 第一个分区被访问的次数.如果在解析时未知,可以设置为key,key(I),key(mc),key(or)或
kye(sq)

pstop 最后一个分区被访问的次数.如果在解析时未知,可以设置为key,key(I),key(mc),key(or)
或kye(sq)

并行和分布式处理
inst 对于分布式处理,操作使用的数据库链路名称
tq 对于并行处理,在两个并行从属进程之间通信的表队列
in-out 并行或分布式操作之间的关系
pqdistrib 对于并行处理,由生产者使用分布处理将数据返回给消费者

运行时统计
starts 一个特定操作被执行的次数
a-rows 操作返回的实际行数
a-time 操作实际花费的时间(HH:MM:SS)

I/O统计
buffers 在执行时执行的逻辑读取的次数
reads 在执行时执行的物理读取的次数
writes 在执行时执行的物理写的次数

内存利用统计
0Mem 评估一个最优的执行所需要的内存总量
1Mem 评估一次通过执行所需要的内存总量
0/1/m 在最优,一次通过和多次通过模型下被执行的次数

used_mem 在最后一次执行操作时使用的内存量
used_tmp 在最后一次执行操作时使用的临时表空间量.它是以字节为单位

max_tmp 操作中使用临时表空间的最大量
下面的部分显示的是查询块的名字和对象别名:
Query Block Name / Object Alias (identified by operation id):
————————————————————-
1 – SEL$1 / EMP@SEL$1
2 – SEL$1 / EMP@SEL$1
对于执行计划中的每一个操作都会有一个查询与之相关

第四部分只在oracle10gr2中可用,下面显示是在oracle11G中的输出信息.它显示了为了强制特定的执行计划可以设置提示
这种设置提示叫做计划概要

Outline Data
-------------
/*+

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
BEGIN_OUTLINE_DATA
INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMP"."EMPNO"))
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('11.2.0.1')
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/

下面的部分显示谓词信息
Predicate Information (identified by operation id):
—————————————————
2 – access(“EMPNO”=7788)
Column Projection Information (identified by operation id):
———————————————————–
1 – “EMPNO”[NUMBER,22], “EMP”.”ENAME”[VARCHAR2,10],
“EMP”.”JOB”[VARCHAR2,9], “EMP”.”MGR”[NUMBER,22], “EMP”.”HIREDATE”[DATE,7]
“EMP”.”SAL”[NUMBER,22], “EMP”.”COMM”[NUMBER,22], “EMP”.”DEPTNO”[NUMBER,22

PLAN_TABLE_OUTPUT
——————————————————————————–
2 – “EMP”.ROWID[ROWID,10], “EMPNO”[NUMBER,22]

display函数
display函数返回存储在plan_table表中的执行计划.它返回的是一个实例集合dbms_xplan_type_table.
集合中的元素是实例对象类型dbms_xplan_type.唯一属性的对象类型名叫plan_table_output是varchar2类型
这个函数有以下输出参数:
table_name:指定plan_table表名.缺省值是plan_table如果指定为null将使用缺省值

statement_id:指定sql语句名字是一个可选参数.当执行explain plan语句时.缺省值是null,如果使用缺省值.
那么最近插入到plan_table表中的执行计划会被显示(提供了filter_reds参数但没有指定)

format:指定什么信息被提供在输出信息中.这里有一些原始值(basic,typical,serial,all和advanced).为了
更好的控制有一些额外的修饰符(alias,bytes,cost,note,outline,parallel,partition,peeked_binds,
predicate,projection,remote和rows)可以被加到参数中.如果信息需要被添加可以通过字符+做为可选的修饰符
例如(basic+predicate).如果信息要被删除可以通过字符-做为可选的修饰符(例如,typical-bytes).多个修饰符
可以同时被指定例如(typical+alias-bytes-cost).缺省值是typical,原始值advanced和可用的修饰符只在
oracle10gr2中有.

filter_preds:当查询plan_table表时应用一个限制.这个限制是基于plan table表中某一列的一个常规sql谓词(
例如statement_id=’test’).缺省值是null.如果使用缺省值,那么最近插入plan_table表中的执行计划将会被显示.
这个参数只能在oracle10gr2中使用.

为了使用display函数,调用都只需要有dbms_xplan包的execute权限和plan_table表的select权限

格式参数的原始值表
值 描述
basic 只会显示最小量的信息,基本只有操作和被执行的对象的信息

typical 显示最相关的信息,除以别名,计划概要和列投影信息以外的信息

serial 象typical只有并行处理的信息不显示

all 显示了除了计划概要以外的所有信息

advanced 显示所有可用的信息

236

格式参数可以使用的修饰符
值 描述
alias 控制显示的查询块名字和对象别名
bytes 控制在执行计划表中列bytes的显示
cost 控制在执行计划表中列cost的显示
note 控制注释的显示
outline 控制概要的显示
parallel 控制并行处理信息的显示特别是执行计划表中的TQ,IN-OUT和PQ Distrib列的显示
partition 控制分区信息的显示特别是执行计划表中的Pstart和Pstop列的显示
peeked_binds 控制窥视绑定变量的显示
predicate 控制过滤和访问谓词的显示
projection 控制列投影信息的显示
remote 控制远程执行sql语句的显示
rows 控制执行计划表中的rows列的显示

下面的查询将显示使用不同的格式参数值basic,typical和advanced来显示执行计划.

SQL> explain plan for select * from scott.emp where empno=7788;

Explained

SQL> SELECT * FROM table(dbms_xplan.display(NULL,NULL,'basic'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2949544139
----------------------------------------------
| Id  | Operation                   | Name   |
----------------------------------------------
|   0 | SELECT STATEMENT            |        |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |
|   2 |   INDEX UNIQUE SCAN         | PK_EMP |
----------------------------------------------

9 rows selected

SQL> SELECT * FROM table(dbms_xplan.display(NULL,NULL,'typical'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    38 |     1   (0)| 00:0
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    38 |     1   (0)| 00:0
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("EMPNO"=7788)

14 rows selected

SQL> SELECT * FROM table(dbms_xplan.display(NULL,NULL,'advanced'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2949544139
--------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    38 |     1   (0)| 00:0
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    38 |     1   (0)| 00:0
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:0
--------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / EMP@SEL$1
   2 - SEL$1 / EMP@SEL$1
Outline Data
-------------
  /*+

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
      BEGIN_OUTLINE_DATA
      INDEX_RS_ASC(@"SEL$1" "EMP"@"SEL$1" ("EMP"."EMPNO"))
      OUTLINE_LEAF(@"SEL$1")
      ALL_ROWS
      DB_VERSION('11.2.0.1')
      OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("EMPNO"=7788)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - "EMPNO"[NUMBER,22], "EMP"."ENAME"[VARCHAR2,10],
       "EMP"."JOB"[VARCHAR2,9], "EMP"."MGR"[NUMBER,22], "EMP"."HIREDATE"[DATE,7]
       "EMP"."SAL"[NUMBER,22], "EMP"."COMM"[NUMBER,22], "EMP"."DEPTNO"[NUMBER,22

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   2 - "EMP".ROWID[ROWID,10], "EMPNO"[NUMBER,22]

42 rows selected

下面的查询将显示使用格式化参数basic和typical并使用修饰符来增加或删除所要输出的信息.

SQL> SELECT * FROM table(dbms_xplan.display(NULL,NULL,'basic +predicate',NULL));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2949544139
----------------------------------------------
| Id  | Operation                   | Name   |
----------------------------------------------
|   0 | SELECT STATEMENT            |        |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |
----------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("EMPNO"=7788)

14 rows selected

SQL>
SQL> SELECT * FROM table(dbms_xplan.display(NULL,NULL,'typical -bytes -note',NULL));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2949544139
------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("EMPNO"=7788)

14 rows selected

display_cursor函数
display_cursor函数用来显示存储在库缓存中的执行计划.在oracle10g中可以使用,和display一样它返回也是实例集合
dbms_xplan_type_table.这个函数有以下输入参数:
sql_id:指定要被返回的执行计划的父游标.缺省值是null,如果使用缺省值会显示当前会话最后一次执行的sql语句的
执行计划

cursor_child_no:指定子游标号与sql_id一起用来识别被返回的执行计划的子游标.缺省值是0,如果指定为null,那么通过
sql_id找到的父游标的所有子游标

format:指定哪些信息被显示.这个参数与display的格式化参数一样.如果执行统计可用(换句话说,如查初始化参数
statistics_level设置为all或在sql语句中指定gather_plan_statistics提示),那么也支持修饰符.它的缺省值为
typical

为了使用display_cursor函数,调用都要对以下动态性能视图v$session,v$sql,v$sql_plan和v$sql_plan_statistics_all
有select权限.而select_catalog_role角色和select any dictionary系统权限提供了这些权限

格式化可以使用的修饰符
值 描述
allstats* 这是一个对于iostats,memstats的一个快捷方式
iostats* 控制I/O统计的显示
last* 默认是所有执行的累积统计被显示如果这个值被指定只有最后的执行统计被显示
memstats* 控制PGA相关统计的显示
runstats_last 和iostats last一样,它只能在oracle10gr1中使用
runstats_tot 和iostats一样,只能在oracle10gr1中使用

下面的例子显示在查询语句中使用gather_plan_statistics来生成执行计划.display_cursor函数针显示最后执行的
I/O统计.注意这里只会显示逻辑读取操作(buffers)因为这里没有物理读或写:

SQL> select /*+ gather_plan_statistics */ * from scott.emp where empno=7788;

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7788 SCOTT      ANALYST    7566 1987-4-19     3000.00               20

SQL> select a.SQL_TEXT,a.SQL_ID from v$sqlarea a where a.SQL_TEXT
  2  like '% select /*+ gather_plan_statistics */ * from scott.emp where empno=7788 %' and a.sql_text not like  '%v$sqlarea%'
  3  ;

SQL_TEXT                                                                         SQL_ID
-------------------------------------------------------------------------------- -------------
 select /*+ gather_plan_statistics */ * from scott.emp where empno=7788          dzbmswjhdhk8t




SQL> SELECT * FROM table(dbms_xplan.display_cursor('dzbmswjhdhk8t',0, 'iostats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  dzbmswjhdhk8t, child number 0
-------------------------------------
 select /*+ gather_plan_statistics */ * from scott.emp where empno=7788
Plan hash value: 2949544139
--------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Starts | E-Rows | A-Rows |   A-Ti
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |      1 |        |      1 |00:00:0
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |      1 |      1 |      1 |00:00:0
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |      1 |      1 |      1 |00:00:0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("EMPNO"=7788)

19 rows selected

display_awr函数
这个函数返回存储在awr中的执行计划.在oracle10g中可以使用.和display函数一样,它返回的也是一个实例集合
dbms_xplan_type_table.这个函数有以下输入参数:
sql_id:指定要被返回执行计划的父游标.这个参数没有缺省值

plan_hash_value:指定要被返回执行计划的哈希值.缺省值是null.如果使用缺省值,与通过sql_id标识的父游标相关的
所有执行计划都会被返回

db_id:指定要返回哪个数据库的执行计划,这个参数的缺省值是null,如果使用缺省值就代表是当前数据库

format:指定哪些信息会被显示.与display的格式化参数相同,缺省值是typical

为了能使用display_awr函数,调用者至少要对以下视图dba_hist_sql_plan和dbs_hist_sqltext有select权限.
如果db_id参数没有指定,那么对v$database视图要有select权限.select_catalog_role角色提供了这些权限.

当对于一个特定的游标有多个执行计划存在时使用plan_hash_value参数进行查询是有帮助的

SQL> SELECT * FROM table(dbms_xplan.display_awr('4pqx4cy7p7tnp',2657262937,NULL,'basic'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 4pqx4cy7p7tnp
--------------------
select * from v$sql_plan
Plan hash value: 2657262937
--------------------------------------
| Id  | Operation        | Name      |
--------------------------------------
|   0 | SELECT STATEMENT |           |
|   1 |  FIXED TABLE FULL| X$KQLFXPL |
--------------------------------------

13 rows selected

有很多情况导致一个游标有多个执行计划.比如象增加了一个索引或都数据发生变化(对象统计改变).基本上查询优化器的工作环境随时发生变化所以可能会生成不同的执行计划.因此当一个sql语句执行比较长的时间又没有报错你对这个语句的性能产生怀疑的时候输出的执行计划的信息对于诊断性能问题是有帮助的.如果在这种情况下,你可以基于输出的信息推断出导致问题的原因.

linux中误删除oracle数据文件的恢复操作

下面来模拟误删除users表空中的数据文件users01.dbf

[root@jingyong jingyong]# ls
control01.ctl example01_bak.dbf jy01.dbf redo02.log sysaux01.dbf system01.dbf undotbs01.dbf users01.dbf.bak
control02.ctl example01.dbf redo01.log redo03.log system_01.dbf temp01.dbf users01.dbf

删除users01.dbf

[root@jingyong jingyong]# rm -rf users01.dbf
SQL> create table testjy(id number(20));
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4:
'/u01/app/oracle/product/11.2.0/oradata/jingyong/users01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

检查dbwr的进程PID

[root@jingyong ~]# ps -ef|grep dbw0|grep -v grep
oracle 2236 1 0 06:40 ? 00:00:01 ora_dbw0_jingyong

dbwr会打开所有数据文件的句柄。在proc目录中可以查到,目录名是进程PID,fd表示文件描述符
[root@jingyong ~]# cd /proc/2236/fd

[root@jingyong fd]# ls -l
total 0
lr-x------ 1 oracle oinstall 64 May 31 08:15 0 -> /dev/null
l-wx------ 1 oracle oinstall 64 May 31 08:15 1 -> /dev/null
l-wx------ 1 oracle oinstall 64 May 31 08:15 10 -> /u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_2213.trc
l-wx------ 1 oracle oinstall 64 May 31 08:15 11 -> /u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_2213.trm
lr-x------ 1 oracle oinstall 64 May 31 08:15 12 -> /u01/app/oracle/product/11.2.0/db/rdbms/mesg/oraus.msb
lr-x------ 1 oracle oinstall 64 May 31 08:15 13 -> /dev/zero
lr-x------ 1 oracle oinstall 64 May 31 08:15 14 -> /proc/2236/fd
lr-x------ 1 oracle oinstall 64 May 31 08:15 15 -> /dev/zero
lrwx------ 1 oracle oinstall 64 May 31 08:15 16 -> /u01/app/oracle/product/11.2.0/db/dbs/hc_jingyong.dat
lrwx------ 1 oracle oinstall 64 May 31 08:15 17 -> /u01/app/oracle/product/11.2.0/db/dbs/lkJINGYONG
lrwx------ 1 oracle oinstall 64 May 31 08:15 18 -> /u01/app/oracle/product/11.2.0/oradata/jingyong/control01.ctl
lrwx------ 1 oracle oinstall 64 May 31 08:15 19 -> /u01/app/oracle/product/11.2.0/oradata/jingyong/control02.ctl
l-wx------ 1 oracle oinstall 64 May 31 08:15 2 -> /dev/null
lrwx------ 1 oracle oinstall 64 May 31 08:15 20 -> /u01/app/oracle/product/11.2.0/oradata/jingyong/system01.dbf
lrwx------ 1 oracle oinstall 64 May 31 08:15 21 -> /u01/app/oracle/product/11.2.0/oradata/jingyong/sysaux01.dbf
lrwx------ 1 oracle oinstall 64 May 31 08:15 22 -> /u01/app/oracle/product/11.2.0/oradata/jingyong/undotbs01.dbf
lrwx------ 1 oracle oinstall 64 May 31 08:15 23 -> /u01/app/oracle/product/11.2.0/oradata/jingyong/users01.dbf (deleted)
lrwx------ 1 oracle oinstall 64 May 31 08:15 24 -> /u01/app/oracle/product/11.2.0/oradata/jingyong/example01.dbf
lrwx------ 1 oracle oinstall 64 May 31 08:15 25 -> /u01/app/oracle/product/11.2.0/oradata/jingyong/jy01.dbf
lrwx------ 1 oracle oinstall 64 May 31 08:15 26 -> /u01/app/oracle/product/11.2.0/oradata/jingyong/temp01.dbf
lr-x------ 1 oracle oinstall 64 May 31 08:15 27 -> /u01/app/oracle/product/11.2.0/db/rdbms/mesg/oraus.msb
l-wx------ 1 oracle oinstall 64 May 31 08:15 3 -> /u01/app/oracle/product/11.2.0/db/rdbms/log/jingyong_ora_2213.trc
lr-x------ 1 oracle oinstall 64 May 31 08:15 4 -> /dev/null
lr-x------ 1 oracle oinstall 64 May 31 08:15 5 -> /dev/null
lr-x------ 1 oracle oinstall 64 May 31 08:15 6 -> /dev/null
lrwx------ 1 oracle oinstall 64 May 31 08:15 7 -> /u01/app/oracle/product/11.2.0/db/dbs/hc_jingyong.dat

注意其中”/u01/app/oracle/product/11.2.0/oradata/jingyong/users01.dbf (deleted)”字样,表示该文件已经被删除,
直接cp该句柄文件名回原位置

[root@jingyong fd]# cp 23 /u01/app/oracle/product/11.2.0/oradata/jingyong/users01.dbf

数据文件users01.dbf恢复回来了,因为了用的是root用户操作的要修改一下权限

[root@jingyong jingyong]# ls -lrt
total 2564428
-rw-r----- 1 root root 723525632 May 16 13:33 system_01.dbf
-rw-r----- 1 root root 104865792 May 22 15:46 example01_bak.dbf
-rw-r----- 1 oracle oinstall 52429312 May 31 06:40 redo02.log
-rw-r----- 1 oracle oinstall 52429312 May 31 06:40 redo01.log
-rw-r----- 1 oracle oinstall 1056768 May 31 06:40 jy01.dbf
-rw-r----- 1 oracle oinstall 104865792 May 31 06:40 example01.dbf
-rw-r----- 1 root root 24911872 May 31 08:16 users01.dbf.bak
-rw-r----- 1 oracle oinstall 31465472 May 31 08:20 temp01.dbf
-rw-r----- 1 oracle oinstall 608182272 May 31 08:21 sysaux01.dbf
-rw-r----- 1 oracle oinstall 104865792 May 31 08:22 undotbs01.dbf
-rw-r----- 1 oracle oinstall 723525632 May 31 08:22 system01.dbf
-rw-r----- 1 oracle oinstall 52429312 May 31 08:22 redo03.log
-rw-r----- 1 root root 24911872 May 31 08:23 users01.dbf
-rw-r----- 1 oracle oinstall 10076160 May 31 08:23 control02.ctl
-rw-r----- 1 oracle oinstall 10076160 May 31 08:23 control01.ctl

[root@jingyong jingyong]# chown oracle:oinstall users01.dbf
[root@jingyong jingyong]# chmod 777 users01.dbf
[root@jingyong jingyong]# ls -lrt
total 2564428
-rw-r----- 1 root root 723525632 May 16 13:33 system_01.dbf
-rw-r----- 1 root root 104865792 May 22 15:46 example01_bak.dbf
-rw-r----- 1 oracle oinstall 52429312 May 31 06:40 redo02.log
-rw-r----- 1 oracle oinstall 52429312 May 31 06:40 redo01.log
-rw-r----- 1 oracle oinstall 1056768 May 31 06:40 jy01.dbf
-rw-r----- 1 oracle oinstall 104865792 May 31 06:40 example01.dbf
-rw-r----- 1 root root 24911872 May 31 08:16 users01.dbf.bak
-rw-r----- 1 oracle oinstall 31465472 May 31 08:20 temp01.dbf
-rw-r----- 1 oracle oinstall 723525632 May 31 08:22 system01.dbf
-rwxrwxrwx 1 oracle oinstall 24911872 May 31 08:23 users01.dbf
-rw-r----- 1 oracle oinstall 104865792 May 31 08:23 undotbs01.dbf
-rw-r----- 1 oracle oinstall 608182272 May 31 08:23 sysaux01.dbf
-rw-r----- 1 oracle oinstall 52429312 May 31 08:23 redo03.log
-rw-r----- 1 oracle oinstall 10076160 May 31 08:23 control02.ctl
-rw-r----- 1 oracle oinstall 10076160 May 31 08:23 control01.ctl

进行数据文件恢复

[oracle@jingyong ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri May 31 08:24:35 2013

Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database datafile 4 offline;

Database altered.

SQL> recover datafile 4;
Media recovery complete.
SQL> alter database datafile 4 online;

Database altered.

恢复的原理是,在Linux操作系统中,如果文件从操作系统级别被rm掉,之前打开该文件的进程仍然持有相应的文件句柄,所指向的文件仍然可以读写,并且该文件的文件描述符可以从/proc目录中获得。但是要注意的是,此时如果关闭数据库,则此句柄会消失.

oracle中的sql trace

sql trace
为了处理sql语句,数据库引擎(特别是sql引擎)会执行数据库调用(解析,执行和获取).对于每一种数据库调用都会给出总的概括,sql引擎:通过使用CPU它本身也做一些处理
利用其它资源(如,磁盘)通过一个同步点来保证多用户使用数据库引擎的能力(如latch闩锁)

sql trace的目的是双重的:第一,它将响应时间分成了服务时间和等待时,第二它提供了关于使用资源和同步点(latch)的详细信息.所有关于sql引擎和其它组件之间的交互信息都被写入到跟踪文件中了.

通过sql trace得到的各种信息可以通过tkprof工具抽取出来.它们包括sql语句的文本,一些执行统计,在处理阶段发生的等待和解析步骤生成的执行计划.注意,提供了通过应用程序执行的每一个sql语句的信息和数据库引擎本身的递归调用的信息.

SELECT CUST_ID, EXTRACT(YEAR FROM TIME_ID), SUM(AMOUNT_SOLD)
FROM SH.SALES
WHERE CHANNEL_ID = :B1
GROUP BY CUST_ID, EXTRACT(YEAR FROM TIME_ID)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 164 1.12 1.90 2588 1720 0 16348
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 166 1.13 1.90 2588 1720 0 16348
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 28 (SH) (recursive depth: 1)
Rows Row Source Operation
------ ---------------------------------------------------
16348 HASH GROUP BY
540328 PARTITION RANGE ALL PARTITION: 1 28
540328 TABLE ACCESS FULL SALES PARTITION: 1 28
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 30 0.01 0.07
db file scattered read 225 0.02 0.64
direct path write temp 941 0.00 0.00
direct path read temp 941 0.01 0.05

上面的信息是通过tkprof从跟踪文件抽取出来的,它不是直接将sql trace文件中的信息给输出.事实上,sql trace生成的跟踪文件中的信息是没有加工的信息.例如下面就是上面输出的源始跟踪文件信息,一般来说,对于每一个调用或等待,在跟踪文件中至少有一行信息

...
...
PARSING IN CURSOR #1 len=142 dep=1 uid=28 oct=3 lid=28 tim=1156387084566620
hv=1624534809 ad='6f8a7620'
SELECT CUST_ID, EXTRACT(YEAR FROM TIME_ID), SUM(AMOUNT_SOLD) FROM SH.SALES
WHERE CHANNEL_ID = :B1 GROUP BY CUST_ID, EXTRACT(YEAR FROM TIME_ID)
END OF STMT
PARSE #1:c=0,e=93,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1156387084566617
BINDS #1:
kkscoacd
Bind#0
oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1206001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=2a9721f070 bln=22 avl=02 flg=05
value=3
EXEC #1:c=1000,e=217,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=1156387084566889
WAIT #1: nam='db file sequential read' ela= 19333 file#=4 block#=211 blocks=1
obj#=10293 tim=1156387084610301
WAIT #1: nam='db file sequential read' ela= 2962 file#=4 block#=219 blocks=1
obj#=10294 tim=1156387084613517
...
...
WAIT #2: nam='SQL*Net message from client' ela= 978 driver id=1413697536 #bytes=1
p3=0 obj#=10320 tim=1156387086475763
STAT #1 id=1 cnt=16348 pid=0 pos=1 obj=0 op='HASH GROUP BY (cr=1720 pr=2588 pw=941
time=1830257 us)'
STAT #1 id=2 cnt=540328 pid=1 pos=1 obj=0 op='PARTITION RANGE ALL PARTITION: 1 28
(cr=1720 pr=1647 pw=0 time=1129471 us)'
STAT #1 id=3 cnt=540328 pid=2 pos=1 obj=10292 op='TABLE ACCESS FULL SALES PARTITION:
1 28 (cr=1720 pr=1647 pw=0 time=635959 us)'
WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0
obj#=10320 tim=1156387086475975

上面的输出中:
PARSING IN CURSOR and END OF STMT之间包含的是sql语句文本
PARSE, EXEC, and FETCH 分别对应 parse, execution, and fetch 调用

BINDS:定义绑定变量和变量值
WAIT 在处理阶段发生的等待事件
STAT:是执行计划和相关的统计信息

事实上,sql trace是基于调试事件10046的.10046事件支持多种级别,这些级别定义了写入跟踪文件中的信息量.当sql trace使用大于1的级别它也叫扩展sql trace.

10046诊断事件的跟踪级别
0级:诊断事件被禁用

1级:诊断事件被启用,对于每一个数据库调用进程都会记录以下信息:sql语句,响应时间,服务时间,处理的行数,逻辑读,物理读和写,执行计划和一些其它信息

4级:在1级的基础上,增加了关于绑定变量的信息,主要是数据类型,精度和每次执行时的值

8级:在1级的基础上增加了关于等待时间的详细信息,对于执行时的每一个等待都记录了以下信息:等待事件名称,持续时间和另外一些识别等待资源的参数信息

12级:同时包含级别4和级别8的信息

调试(诊断)事件
一个诊断事件是通过一个数字来进行标识的,它意味着在运行的数据库引擎中通过设置一种类型的标记来启用诊断事件.诊断事件的目的是改变其行为,例如可以通过启用或禁用一个事件,来测试或模拟错误或崩溃,或收集跟踪或调试信息,有一些诊断事件不是简单的标记它其实也能在服务器级别启用.每一种级都有属于它的行为.在有些情况下,这级别变成了一个数据块的地址或内存结构

你在使用诊断事件时要小心,只有当你在oracle客户支持或你已经了解这个诊断事件将会改变什么的情况下你才能设置诊断事件.诊断事件启用特定的代码路径.因此,如果当设置诊断事件后出现了问题,那么有必要在禁用该诊断事件的情况来检查该问题是否还是会出现.

一些诊断事件记录在oracle的文档中,如果文档存它通常是通过MetaLink来提供的,也就是说这些诊断事件是不会发布在oracle的官方文档中的.你能通$ORACLE_HOEM/rdbms/mesg/oraus.msg文件找到可用的完整的诊断事件列表.注意这个文件不会对所有平台都进行发布,10000到10999是保留给诊断事件使用的所有诊断事件

启用sql跟踪:遗留的启用方式
到oracle9i时,Database Performance Tuning Guide and Reference manual文档描述了三种启用sql跟踪的方式:
设置初始化参数sql_trace,使用dbms_session包中的set_sql_trace过程和使用dbms_system包中的
set_sql_trace_in_session过程.需要注意的是这三种方法它们都只是使用1级的sql跟踪功能.不幸的是,这些方式在实际
情况下是不足的.事实上,大多数情况下你需要完全分解响应时间并理解瓶颈出在哪里.由于这个原因我们将不会详细描述
这三种方法.这里我们将介绍一些非官方的在任何级别启用sql跟踪的方法.在oracle10g中的文档中已经介绍了启用或禁用
sql跟踪的方法了.因此,如果是在oracle10g中你将不必使用上面描述的方法来进行sql跟踪.然而如果是在oracle10g以前的
版本,你能够使用上面的方法因为它们都已经使用多年了.

为了能够启用或禁用任何级别的sql跟踪,这里有两种方法.你可以通过alter session语句来设置事件参数来启用特定的
跟踪事件或者你也能调用dbms_system包中的set_ev过程.前者只能对当前启用sql跟踪的会话进行跟踪而后都可以通过
设置会话ID(sid)和序列号(serial#)来对任何会话启用sql跟踪.下面是使用这些方法的例子

下面的例子对要启用跟踪的会话启用级别为12的sql跟踪,注意事件号和级别是怎样被指定的:

ALTER SESSION SET events '10046 trace name context forever, level 12'

下面的例子对要禁用跟踪的会话禁用sql跟踪,注意禁用sql跟踪不是通过将sql跟踪级别指定为0来实现的.

ALTER SESSION SET events '10046 trace name context off'

下面的pl/sql调用对sid=127,serial#=29的会话启用级别为12的sql跟踪.没有参数是默认值,尽管在这种情况下最后一个参数也必须指定

dbms_system.set_ev(si => 127, -- session id
se => 29, -- serial number
ev => 10046, -- event number
le => 12, -- level
nm => NULL)

下面的pl/sql调用对sid=127,serial#=29的会话禁用sql跟踪,注意这里只要将跟踪级别修改为0就可以了

dbms_system.set_ev(si => 127, -- session id
se => 29, -- serial number
ev => 10046, -- event number
le => 0, -- level
nm => NULL)

你能通过执行下面的查询来列出每一个连接到实例的用户的会话ID和会话序号

SQL>SELECT sid, serial#, username, machine FROM v$session WHERE type != 'BACKGROUND';

SID SERIAL# USERNAME MACHINE
---------- ---------- ------------------------------ ---------------------------
31 57 SYS WORKGROUP\JINGYONG
34 61 jingyong
40 65 SYS WORKGROUP\JINGYONG
51 68 jingyong

你能通过执行alter system语句来设置初始化参数事件.它的语法与alter session的语法相同.
在任何情况下通常都不需要在实例级启用sql跟踪,另外需要注意的是只有在会话创建启用跟踪后才能生效.

通常情况下,dbms_system包只能由sys用户来执行.如果执行权限被授予给其它用户,那么要小心因为这个包
包含了其它的过程和set_ev过程本身它能够用来设置其它事件.如果你真的需要让其它的用户有权来对任何
其它的会话启用或禁用sql跟踪,那么建议你使用另一个只包含启用或禁用sql跟踪的包比如dbms_support.
但是dbms_support缺省的情况下是没有安装的.dbms_support只在MetaLink中有记录,dbms_support包是不受
oracle官方支持的
为了安装dbms_supoort包并给它创建一个共用同义词,授予dba角色来执行它:

CONNECT / as sysdba
@?/rdbms/admin/dbmssupp.sql
CREATE PUBLIC SYNONYM dbms_support FOR dbms_support;
GRANT EXECUTE ON dbms_support TO dba;

这个包真地不应该用于不是oracle10g的数据库.如果你在安装dbms_support包时出现了警告(例如设置初始化
参数plsql_warning to enbale:all),当创建dbms_support包时出现了如下警告可以忽略它:

PLW-05005: function CURRENT_SERIAL returns without value at line 29

下面的pl/sql调用将对sid=127,serial#=29的会话启用级别为8的sql跟踪.注意设置sql跟踪级别的操作在启用
sql跟踪时被第三,第四个参数取代了.你能指定你是否想启用对等待和绑定变量信息的跟踪.因此第一,二两个
参数没有缺省值,waits参数缺省值为true,binds参数缺省值为false

dbms_support.start_trace_in_session(sid => 127,
serial => 29,
waits => TRUE,
binds => FALSE)

下面的pl/sql调用将对sid=127,serial#-29的会话禁用sql跟踪.这两个参数是没有缺省值

dbms_support.stop_trace_in_session(sid => 127,
serial => 29)

启用sql跟踪:当前使用的方式
对于oracle10g来说启用或禁用sql跟踪使用dbms_monitor包.使用这个dbms_monitor包你不仅有一个官方的方式来
让你完全使用sql跟踪的功能,更重要的是你能根据会话属性来启用或禁用sql跟踪.会话属性包括:客户端标识符,
服务名,模块名和操作名.这意味着如果程序正在被做正确的性能测量,你能独立于执行数据库调用的会话来启用或
禁用sql跟踪.对于今天来说这是特别有用的因为在许多情况下是使用连接池因此用户是不能系住一个特定的会话的.
下面是一些使用dbms_monitor包来在会话,客户端,组件和数据库级别来启用或禁用sql跟踪的例子.注意,缺省情况下,
只能有dba角色的用户能使用dbms_monitor

会话级
为了对一个会话启用或禁用sql跟踪.dbms_monitor提供了session_trace_enable和session_trace_disable过程
下面的pl/sql代码就是对sid=31,serial#=57的会话启用级别为8的sql跟踪.所有的参数都有缺省值.如果两个标识
会话的参数没有指定,那么会对执行这个plsql代码的会话启用sql跟踪.waits参数缺省值是true,binds参数的缺省
值是false

SQL>exec dbms_monitor.session_trace_enable(session_id => 31,
serial_num =>57,
waits => true,
binds => true);

如果是在oracle10gR2的版本中当使用dbms_monitor.session_trace_enable过程启用sql跟踪后那么在v$session
视图中的sql_trace,sql_trace_waits和sql_trace_binds字段会进行设置.警告:当使用session_trace_enable过程
启用sql跟踪后且至少有一个sql语句在这个启用跟踪的会话中被执行才会这样. 但是在oracle11G中只要启用这三个
字段就会被设置

SQL> select sql_trace,sql_trace_waits,sql_trace_binds from v$session where sid=31;

SQL_TRACE SQL_TRACE_WAITS SQL_TRACE_BINDS
--------- --------------- ---------------
ENABLED TRUE TRUE

下面的pl/sql代码是对sid=31,serial#=57的会话禁用sql跟踪.注意这两个参数都有缺省值,如果你没有指定标识会话的
两个参数那么就会对执行pl/sql代码的会话禁用sql跟踪.

SQL>exec dbms_monitor.session_trace_disable(session_id => 31,serial_num => 57);

注意,如果是在RAC中使用,那么session_trace_enable和session_trace_disable必须在会话所在的实例上执行.

客户端级别
为了对一个客户端启用或禁用sql跟踪可以分别执行dbms_monitor包中的client_id_trace_enable和
client_id_trace_disable过程.通常这些过程只有对哪些会话已经设置client标识符的会话使用.

下面的pl/sql代码对有客户端标识符作为参数的所有会话启用级别为8的sql跟踪.client_id参数没有缺省值,
waits参数的缺省值为true,binds参数的缺省值为false,要注意client_id参数是区分大小写的.

SQL>exec dbms_monitor.client_id_trace_enable(client_id => 'jingyong',waits => true,binds => false);

PL/SQL procedure successfully completed

dba_enabled_traces视图显示了哪些客户端标识所对应的会话通过client_id_trace_enable过程启用了sql跟踪.

SQL> select primary_id as client_id,waits,binds
2 from dba_enabled_traces
3 where trace_type='CLIENT_ID';

CLIENT_ID WAITS BINDS
---------------------------------------------------------------- ----- -----
jingyong TRUE FALSE

下面的pl/sql代码将对有客户端标识符作为参数的所有会话禁用sql跟踪.client_Id参数是没有缺省值的

SQL>exec dbms_monitor.client_id_trace_disable(client_id => 'jingyong');

PL/SQL procedure successfully completed

组件级别
为了对一个组件通过指定服务名,模块名和操作名来启用或禁用sql跟踪.dbms_monitor包分别提供了
serv_mod_act_trace_enable和serv_mod_act_trace_disable过程.为了使用这些过程必须要设置会话属性,
模块名和操作名.

下面的pl/sql代码对哪些指定会话属性参数的所有会话启用或禁用级别为8的sql跟踪.只有一个参数没有缺省值就是
第一个参数:service_name.module_name和action_name参数的缺省值是any_module和any_action.同时null是一个有效值.
如果action_name参数被指定那么module_name也必须要指定.如果不这样做会触发ora-13859错误.waits参数的缺省值为
true,binds参数的缺省值为false.如果在RAC中使用instance_name参数可以控制它只跟踪单个实例.缺省的情况下是对
所有实例启用.要注意参数service_name,module_name,action_name和instance_name是区分大小写的

dbms_monitor.serv_mod_act_trace_enable(service_name => 'DBM10203.antognini.ch',
module_name => 'mymodule',
action_name => 'myaction',
waits => TRUE,
binds => FALSE,
instance_name => NULL)

和在客户端启用sql跟踪一样,dba_enabled_traces视图会显示哪个组件启用了sql跟踪,哪些参数通过
serv_mod_act_trace_enable过程设置为启用了.在启用sql跟踪后你能得到以下信息.注意如果没有指定这三个参数(
service name,module name和action name)而启用了sql跟踪,那么dba_enabled_traces视图中的trace_type列的值
将会概括使用的参数被设置成SERVICE或SERVICE_MODULE

SQL>SELECT primary_id AS service_name, qualifier_id1 AS module_name,
2 qualifier_id2 AS action_name, waits, binds
3 FROM dba_enabled_traces
4 WHERE trace_type = 'SERVICE_MODULE_ACTION';
SERVICE_NAME MODULE_NAME ACTION_NAME WAITS BINDS
---------------------- ------------ ------------ ----- -----
DBM10203.antognini.ch mymodule myaction TRUE FALSE

下面的pl/sql代码将对指定会话参数的所有会话禁用sql跟踪.

dbms_monitor.serv_mod_act_trace_disable(service_name => 'DBM10203.antognini.ch',
module_name => 'mymodule',
action_name => 'myaction',
instance_name => NULL)

数据库级别
在oracle10gr2中,可以使用dbms_monitor包提供了database_trace_enable和database_trace_disable过程来
对所有连接到数据库的所有会话启用或禁用sql跟踪.

下面的pl/sql代码将对数据库启用级别为12的sql跟踪.所有的参数都有缺省值.waits参数的缺省值为true,
binds参数的缺省值为false.在rac中你可以通过使用instance_name参数来限制只对单个实例进行跟踪.
如果instance_name设置为null,null也是缺省值,那么将对所有实例启用sql跟踪.注意instance_name参数是区分大小写的.

dbms_monitor.database_trace_enable(waits => TRUE,
binds => TRUE,
instance_name => NULL)

和在客户端与在组件级别启用sql跟踪一样,dba_enabled_traces视图将会显示哪个实例已经启用了sql跟踪,通过
database_trace_enable过程设置哪些数为启用了.

SQL>SELECT instance_name, waits, binds
2 FROM dba_enabled_traces
3 WHERE trace_type = 'DATABASE';
INSTANCE_NAME WAITS BINDS
---------------- ----- -----
TRUE TRUE

下面的pl/sql代码将禁用对数据库的sql跟踪.如果instance_name被设置为null,null也是缺省值,将对会所有实例
禁用sql跟踪

dbms_monitor.database_trace_disable(instance_name => NULL);

触发sql跟踪
在前面已经介绍了各种不同的启用或禁用sql跟踪的方法.对于简单的情况,可以在sqlplus中手功执行显示sql语句或
pl/sql调用.有些时候然而需要自动触发sql跟踪,自动触发sql跟踪意味着需要增加代码.

最简单的方法是在数据库级别创建一个登录触发器.为了避免对所有用户启用sql跟踪,我通常建议创建一个角色(
例如叫sql_trace角色)和只是暂时授予给用户让其测试.通常也可能对单个方案定义触发器或执行其它的基本的检查.
例如,在userenv上下文中.注意除了启用sql跟外,也还可以设置其它与sql跟踪相关的参数.

CREATE ROLE sql_trace;
CREATE OR REPLACE TRIGGER enable_sql_trace AFTER LOGON ON DATABASE
BEGIN
IF (dbms_session.is_role_enabled('SQL_TRACE'))
THEN
EXECUTE IMMEDIATE 'ALTER SESSION SET timed_statistics = TRUE';
EXECUTE IMMEDIATE 'ALTER SESSION SET max_dump_file_size = unlimited';
dbms_monitor.session_trace_enable;
END IF;
END;
/

另一种方法是直接在应用程序控制中添加一些代码来启用sql跟踪.一些类型的参数将促使代码必须要被添加.
对于一个胖客户端程序的命令行参数或对于web程序来说的http参数就是这样的例子

跟踪文件中的时间统计信息
动态初始化参数timed_statistics可以动态地被设置为true或false.它将控制跟踪文件中的运行时间和cpu时间的
时间统计信息的可用性.如果timed_statistics参数设置为true,那么时间统计信息会被写入跟踪文件,如果设置为false
那么在跟踪文件中不会有时间统计信息,然而根据你工作的端口,它们可能是部分可用.timed_statistics参数的缺省值
还依赖于另一个初始化参数statistics_level.如果statistics_level被设置为basic,timed_statsitics缺省值为false
否则timed_statistics缺省值为true.

一般来说如果说时间统计信息不可用那么跟踪文件没有什么用.所以在启用sql跟踪之胶,timed_statistics参数要设置
为true,你也可以修改这个参数通过以下语句

ALTER SESSION SET timed_statistics = TRUE

动态初始化参数
有些初始化参数是静态地而有些是动态的.如果参数是动态参数它意味着它们不用重启实例就能修改.在这些动态
初始化参数中有一些只能在会话级进行修改,有一些只能在系统级进行修改还有一些可以在会话级和系统级进行修改.
为了在会话级和系统级修改初始化参数,你可以使用alter session和alter system语句.在实例级修改初始化参数会
立即生效或在会话级修改后会立即生效.在v$parameter视图中有许多列如isses_modifiable和issys_modifiable列.
从它们的值可以看出参数可以在什么级别被修改.

限制跟踪文件大小
通常,你可能不会关心跟踪文件的大小.如果需要设置跟踪文件大小,然而你可以在会话级或系统级设置动态初始化
参数max_dump_file_size.这个参数的值是以K或M以单位的,如果想设置跟踪文件大小不受限制,可以使用下面的语句
将该参数值设置为unlimited:

alter session set max_dump_file_size=unlimited

查找跟踪文件
跟踪文件是由运行在数据库服务器上的数据库引擎服务器进程所生成的.这意味着跟踪文件是写在数据库服务器上
可以访问的磁盘上的.根据进程生成的跟踪文件类型,它们会被存储在两个不同的目录:
专用的服务器进程创建跟踪文件的目录是由user_dump_dest初始化参数来设置的
后台进程创建的跟踪文件的目录是由background_dump_dest初始化参数来设置的

在oracle11g中引入了自动诊断资料库,user_dump_dest和background_dump_dest被弃用而使用diagnostic_dest目录
来代替.因此你只能使用新的初始化参数来设置基本目录,你能使用v$diag_info视图来查询跟踪文件的真实目录.
下面的查询显示了初始化参数值与跟踪文件目录的差别

SQL>conn sys/zzh_2046@jy_201 as sysdba
已连接。
SQL> SELECT value FROM v$parameter WHERE name = 'diagnostic_dest';

VALUE
--------------------------------------------------------------------------------

/u01/app/oracle

SQL> SELECT value FROM v$diag_info WHERE name = 'Diag Trace';

VALUE
--------------------------------------------------------------------------------

/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace

跟踪文件名称以前依赖于版本和平台,它们有如下结构:
{instance name}_{process name}_{process id}.trc

instance name:它是初始化参数instance_name值的小写,注意在RAC环境中,它是来自不同的初始化参数db_name.
在v$instance视图中的instance_name列是可用的

process name:这是生成跟踪文件的进程名的小写值.对于专用服务器进程,名字是ora,对于共享服务器进程,它的值
可以在v$dispatcher或v$shared_server视图中找到这个列名.对于并行服务器,可以从v$px_process视图中找到
server_name列,对于其它后台进程可以从v$bgprocess视图中找到这个列名.

process id:它是在操作系统级别来唯一标识进程的.它的值可以从v$process视图中的spid列得到.

基于这些信息,可以写一个快速查询每一个会话所生成的跟踪文件

SQL>SELECT s.sid,
2 s.server,
3 lower(
4 CASE
5 WHEN s.server IN ('DEDICATED','SHARED') THEN
6 i.instance_name || '_' ||
7 nvl(pp.server_name, nvl(ss.name, 'ora')) || '_' ||
8 p.spid || '.trc'
9 ELSE NULL
10 END
11 ) AS trace_file_name
12 FROM v$instance i,
13 v$session s,
14 v$process p,
15 v$px_process pp,
16 v$shared_server ss
17 WHERE s.paddr = p.addr
18 AND s.sid = pp.sid (+)
19 AND s.paddr = ss.paddr(+)
20 AND s.type = 'USER'
21 ORDER BY s.sid;
SID SERVER TRACE_FILE_NAME
---------- --------- --------------------------------------
145 DEDICATED dbm10203_ora_24387.trc
146 DEDICATED dbm10203_ora_24380.trc
147 NONE
149 DEDICATED dbm10203_ora_24260.trc
150 SHARED dbm10203_s000_24374.trc

而对于11g来说,对于当前会话你可以使用v$diag_info视图来查询:

SQL>SELECT value FROM v$diag_info WHERE name = 'Default Trace File';

VALUE
--------------------------------------------------------------------------------

/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_2594.trc

为了简单快速找到正确的跟踪文件,其实可以使用初始化参数tracefile_identifier.事实上使用这个参数,
你能给跟踪文件的名字加一个最多长度可达255个字符的标识符.如果使用这个参数那么跟踪文件的名称结构
就变成如下格式了:
{instance name}_{process name}_{process id}_{tracefile identifier}.trc
注意的是这个方法只适用于专用服务器进程,当一个新的跟踪文件创建时,这个参数的值会根据每个会话动态改变.
这个参数值是可从v$process视图中的traceid找到tracefile_identifier参数的值.

跟踪文件包含哪些机密信息
通常跟踪文件不是每个人都能访问的.这是因为跟踪文件可能包含机密信息.事实上,sql语句可能包含许多数据(文本值)
和绑定变量的值.这就意味着存储在数据库中的每一个数据片段都会被写入跟踪文件.

例如在unix/linux系统中,跟踪文件属于运行在数据库引擎上用户和组并且有-rw-r—权限.换句话说,也就是只有这个用户
和运行数据库的用户在同一个组才能读取这个跟踪文件.

然而真的没有必要阻上哪些可以访问数据库数据的人来访问这些跟踪文件,如果要求执行这样的操作,事实上从安全的角度
跟踪文件对于哪些没有访问数据库权限的人来说是一个有用的信息来源.对于这种情况,数据库引擎提供了一个没有记录在
文档中的参数_trace_files_public.缺省值被设置为false,如果设置为true,跟踪文件可以被每一个能访问系统的人读取.

例如在unix/linux中,将_trace_files_public设置为true,那么缺省的权限将变为-rw_r–r–,这样所有的用户都能读取\
这个跟文件.
跟踪文件的结构
一个跟踪文件包含了通过一个特定进程执行的数据库调用的信息.事实上,当进程ID在操作系统级别重用时,一个跟踪文件的
信息可能来自多个进程.因此一个进程可以被用于不同的会话(例如,共享服务或并行服务从属进程),每个会话可以有不同的
会话属性(例如,模块名和操作名),一个跟踪文件可以被分成几个逻辑部分

BEGIN
dbms_session.set_identifier(client_id=>'helicon.antognini.ch');
dbms_application_info.set_module(module_name=>'Module 1',
action_name=>'Action 11');
-- code module 1, action 11
dbms_application_info.set_module(module_name=>'Module 1',
action_name=>'Action 12');
-- code module 1, action 12
dbms_application_info.set_module(module_name=>'Module 1',
action_name=>'Action 13');
-- code module 1, action 13
dbms_application_info.set_module(module_name=>'Module 2',
action_name=>'Action 21');
-- code module 2, action 21
dbms_application_info.set_module(module_name=>'Module 2',
action_name=>'Action 22');
-- code module 2, action 22
END;

使用trcsess
你能使用命令行工具trcsess,它可用于oracle11g来抽取一个或多个跟踪文件中的部分信息.基于逻辑标记符
为了了解trcsess参数列表可以运行不带参数的trcsess

C:\Documents and Settings\Administrator>;trcsess
oracle.ss.tools.trcsess.SessTrcException: SessTrc-00002: 会话跟踪用法错误: 传递
了错误的参数。
trcsess [output=<output file name >] [session=<session ID>] [clientid=<clientid
>] [service=<service name>] [action=<action name>] [module=<module name>] <trace
file names>

output=<output file name> output destination default being standard output.
session=<session Id> session to be traced.
Session id is a combination of session Index & session serial number e.g. 8.13.

clientid=<clientid> clientid to be traced.
service=<service name> service to be traced.
action=<action name> action to be traced.
module=<module name> module to be traced.
<trace_file_names> Space separated list of trace files with wild card '*' suppor
ted.

就象你看到的一样,它可以指定一个会话,客户端id,服务名,模块名和操作名作为参数.例如你想从跟踪文件
dbm10203_ora_24433.trc中抽取操作为12的信息并将抽取到的信息写一个新的名为action12.trc的跟踪文件中
trcsess output=action12.trc action=”Action 12″ dbm10203_ora_24433.trc

记住,clientid,service,action和module参数是区分大小写的.这个trcsess工具也支持之前的版本生成的跟踪
文件.

剖析工具
当你已经识别正确的跟踪文件或使用trcsess工具截取它们的一部分.下面来分析它的内容,为了这个目的你可以
使用一种剖析工具.它的目的是根据原始的跟踪文件生成格式化的输出.oracle发布了数据库和客户端二进制文件
分析工具.它叫tkprof(它是标准的跟踪内核剖析器).虽然在有些情况下输出的信息是有用的,但有时它不能快速
识别性能问题.奇怪的是oracle没有重视这个工具的重要性,因此自从oracle7引入以来只做了稍微的改进.有很多
商业和免费的分析器可以使用.我还开发了一个自己的分析器叫TVD$XTAT,其它的分析器你可能会考虑Hotsos Profiler
itfprofSQL Analyzer和OraSRP.甚至是oracle的另一种分析器叫Trace Analyzer

下面来执行一段plsql代码并生成跟踪文件

SQL>declare
2 l_channel_id sh.sales.channel_id%type :=3;
3 begin
4 for c in (select cust_id,extract(year from time_id),sum(amount_sold)
5 from sh.sales
6 where channel_id=l_channel_id
7 group by cust_id,extract(year from time_id))
8 loop
9 null;
10 end loop;
11 end;
12 /

PL/SQL 过程已成功完成。
对于oracle11g获得当前会话的跟踪文件可以使用下面的查询

SQL>SELECT value FROM v$diag_info WHERE name = 'Default Trace File';

VALUE
--------------------------------------------------------------------------------

/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_2717.trc

也可以使用下面的方法来获得

SQL>oradebug setmypid
已处理的语句
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_2717.trc

使用tkprof
tkprof是一个命令行工具,它的主要目的是使用一个原始的跟踪文件作为输入并生成一个格式化的文本文件作为输出.
另外,它也能生成一个sql脚本在数据库中加载数据尽管这个功能从来没有使用过.

使用这个工具最简单的例子就是只指定一个输入文件和一个输出文件.在下面的例子中,输入文件为jingyong_ora_2717.trc
输出文件为jingyong_ora_2717.txt.尽管tkprof工具输出文件的缺省文件扩展名为prf.但是我总是使用txt.

[oracle@jingyong trace]$ tkprof jingyong_ora_2717.trc jingyong_ora_2717.txt

TKPROF: Release 11.2.0.1.0 - Development on Mon May 20 01:10:23 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

没有指定参数的分析只有当对小跟踪文件进行分析才有,在大多数情况下为了得到更好输出内容你必须指定一些参数

tkprof参数
如果你运行不带任何参数的tkprof,你将得到一个完整的参数列表并有它们的一个简短描述

[oracle@jingyong trace]$ tkprof
Usage: tkprof tracefile outputfile [explain= ] [table= ]
[print= ] [insert= ] [sys= ] [sort= ]
table=schema.tablename Use 'schema.tablename' with 'explain=' option.
explain=user/password Connect to ORACLE and issue EXPLAIN PLAN.
print=integer List only the first 'integer' SQL statements.
aggregate=yes|no
insert=filename List SQL statements and data inside INSERT statements.
sys=no TKPROF does not list SQL statements run as user SYS.
record=filename Record non-recursive statements found in the trace file.
waits=yes|no Record summary for any wait events found in the trace file.
sort=option Set of zero or more of the following sort options:
prscnt number of times parse was called
prscpu cpu time parsing
prsela elapsed time parsing
prsdsk number of disk reads during parse
prsqry number of buffers for consistent read during parse
prscu number of buffers for current read during parse
prsmis number of misses in library cache during parse
execnt number of execute was called
execpu cpu time spent executing
exeela elapsed time executing
exedsk number of disk reads during execute
exeqry number of buffers for consistent read during execute
execu number of buffers for current read during execute
exerow number of rows processed during execute
exemis number of library cache misses during execute
fchcnt number of times fetch was called
fchcpu cpu time spent fetching
fchela elapsed time fetching
fchdsk number of disk reads during fetch
fchqry number of buffers for consistent read during fetch
fchcu number of buffers for current read during fetch
fchrow number of rows fetched
userid userid of user that parsed the cursor

每一个参数的功能描述如下:
explain:它是指示tkprof对于跟踪文件中的每一个sql语句提供一个执行计划.这是通过执行explain plan sql语句来
完成的.很明显的是为了执行一个sql语句需要连接到数据库.因此必须指定用户和密码参数,如果需要还要指定连接串.
公认的格式为:explain=user/password@connect_string and explain=user/password.注意为了最大化你得到正确的
执行计划的机会,你应该指定一个能(和生成跟踪文件相同的用户)访问相同对象的用户并确保所有的查询优化器初始化
参数的设置和生成跟踪文件时是一样的.你应该小心初始化参数通过应用程序或登录触发器被修改.不用多说你使用
相同的用户是最好的.在任何情况下,尽管之前所说的所有条件都满足,通过explain plan生成的执行计划也不一定就是
和真实的相匹配.指定explain参数是不明智的.如果一个错误的用户名和密码或连接串被指定,那么处理这个跟踪文件时
不会有任何的错误信息,而在输出文件中可以找到如下错误信息:

error connecting to database using: scott/lion
ORA-01017: invalid username/password; logon denied
EXPLAIN PLAN option disabled

table:table参数只能与explain参数一起使用.它的目的是指定explain plan语句使用哪一个表来生成执行计划.
通常情况下可以不用指定它因为tkprof会自动在用户方案中创建一个名为prof$plan_table的表用于分析并最终
将其删除.在任何情况下如果用户不能创建表(例如因为create table权限丢失)那么就必须指定table参数.例如
为了指定system用户使用的plan_table表,那么这个参数必须指定为table=system.plan_table.执行分析的用户
必须对指定的表有select,insert和delete权限.同样地,在这种情况下错误也中出现在输出文件中.

注意:在oracle10gr1中因为bug3451410,tkprof不能自动创建计划表.因此会在输出文件记录ora-00922错误信息.

print:用来限制写入输出文件中的sql语句的数量.缺省值是没有限制的.它与sort参数一起使用才有意义.例如,
只获取10个sql语句,可以设置为print=10

aggregate:指示tkprof是否要对相同的语句分别处理.缺省情况不是这样的,换句话说,属于一个特定sql语句的
所有信息会被聚合在一起.注意这样做是不依赖于跟踪文件中出现的sql语句的数量的.就是在使用任何聚合的
情况下也有信息丢失.在这种情况下,一个游标有多个同执行计划的子游标的语句将会作为单个sql语句来处理.
尽管在大多数情况下缺省值已经够用了,但有些时候最好指定aggregate=no这样就可以单独的看到每一个语句.

insert:指示tkprof生成一个sql脚本在数据库中来存储所有的信息.这个脚本的名字可以通过参数来指定,如
insert=load.sql

sys:指定被写到输出文件中的sql语句是否由用户sys来执行(例如,在解析操作时对数据字典的解析调用).这个
缺省值是yes.但大多数时候我更喜欢设置它为no来避免在输出文件中写入不必要的信息.这是没有必要的,因为
你通常是不能通过sys用户来控制递归调用的sql语句的执行的.

record:指示tkprof生成一个sql脚本包含在跟踪文件中出现的所有不是递归的sql语句.这个脚本的名字是通过
record参数来指定的(例如record=replay.sql).根据文档,这个功能可以被用来手动重演sql语句.因为绑定变量
不能处理所以这通常是不可能的.

waits:决定是否等待事件信息将被写入到输出文件中.缺省值是写入,个人认为没有理由指定waits=no.如果指定
为no将不会有重要的等待事件信息写入到输出文件中.

sort:指定sql语句写入到输出文件中的顺序.缺省的顺序是在跟踪文件中找到的顺序.基本上通过指定一个推荐的
选项,你能根据资源利用情况来排序(例如,调用的次数,cpu时间和物理读取次数)或响应时间(指运行时间).你大
多数看到的选项(例如运行时间),每一种可用的数据库调用类型的值:例如,prsela解析游标所花的时间,exeela
执行游标所花的时间和fchela从游标中获取行记录所花的时间.尽管你有许多选项和选项组合,这里只有一种排序
顺序对于调查性能问题是有用的:响应时间.因此,你应该指定sort=prsela,exeela,fchela.当你指定一个以逗号
分隔的值时,tkprof会将这些值作为参数来传递.即使这些参数可能互不相容也是这样.注意当一个跟踪文件包含
多个会话且参数aggregate=no时,对于每个会话的sql语句将会单独排序.

基于前面的信息个人通常执行tkprof使用的参数如下:
tkprof {input trace file} {output file} sys=no sort=prsela,exeela,fchela

现在已经知道怎么样使用tkprof工具来分析跟踪文件了.下面来让我们来看看输出文件的内容.

解释tkprof的输出内容
分析是通过指定以下参数来完成的.注意
tkprof DBM11106_ora_9813.trc DBM11106_ora_9813.txt
sort=prsela,exeela,fchela print=3 explain=sh/sh aggregate=no

这个输出文件是从一个文件头开始的.大部分的信息是静态的.然则这在些信息中有非常有用的信息:跟踪文件名,
用于生成输出文件而使用的排序参数(sort)和用于标识跟踪会话的行.最后有用的信息仅当参数aggregate=no被
指定,这个头在两个属于不同会话的语句之间会重复出现和使用

TKPROF: Release 11.1.0.6.0 - Production on Tue Feb 29 10:37:08 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Trace file: DBM11106_ora_6334.trc
Sort options: prsela exeela fchela
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
--------------------------------------------------------------------------------
*** SESSION ID:(90.6) 2008-02-29 07:43:11.461

在这个头信息之后当连接到数据库或生成执行计划时可能会出现错误.

在头信息之后,接下来是每一个sql语句的信息:sql语句的文本信息,执行统计,解析信息,执行计划和等待事件.
执行计划和等待事件只有这些信息被存储在跟踪文件中才是可选的和可报告的.记住,只当游标被关闭执行计划
才会被存储而且只有当跟踪等待事件被启用等待事件信息才会被存储在跟踪文件中.

在有些情况下sql语句被格式化.不幸地是,格式化操作不能对所有情况提供正确的格式.对于实例来说,在使用
带有关键字from的extract函数与sql语句中的from子句混淆的情况.

SQL ID : g4h8jndhd8vst
SELECT CUST_ID, EXTRACT(YEAR
FROM
TIME_ID), SUM(AMOUNT_SOLD) FROM SH.SALES WHERE CHANNEL_ID = :B1 GROUP BY
CUST_ID, EXTRACT(YEAR FROM TIME_ID)

执行统计信息提供数据,聚合数据库调用.对于这些信息有以下的性能指标
count:数据库调用被执行的次数
cpu:花在处理数据库调用上的cpu时间以秒为单位
elapsed:是总的运行时间以秒为单位.花费在处理数据库调用上的总时间.如果这个值比cpu时间高,那么会在执行
统计信息下面找到关于资源或同步相关的等待事件信息

disk:物理读取的块数,注意,这不是物理I/O操作的总数.物理I/O操作的总数在等待事件部分有详细信息.如果这个
值比逻读取的块数高(disk>query+current),这意味着有些块被溢出到临时表空间中了

query:是指在一致性模式下从数据库缓冲区缓存中进行逻辑读取数据块的次数.通常这种类型的逻辑读是指查询.

current:是指在一致性模式下从数据库缓冲区缓存中进行逻辑读取数据块的次数,通常这种类型的逻辑读是指
insert,delete,merge和update语句.

rows:是指被处理的行数.对于查询来说,它是指获取的行数,对于insert,delete,merge和update语句来说.它是指
受影响的行数.从下面的输出中获取了16348行记录.这意味着平均每一次调用获取100行.

call    count  cpu       elapsed   disk       query      current    rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        1     0.00       0.00          0          0          0          0
Execute      1     0.04       0.19          0          0          0          0
Fetch      164     1.09       4.50       2590       1720          0      16348
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total      166     1.13       4.70       2590       1720          0      16348

下面的行是对解析操作基本信息的一个汇总.前两个值(Misses in library cache)提供了在解析和执行调用期间
硬解析的次数.如果在执行调用期间没有发生硬解析,那么这年特定的行信息会丢失.优化器的模式和谁执行的解析
用户会被显示出来.注意这个用户的名字ops$cha,这只有在指不定期explain参数时才会被提供否则只会有用户id
最后片段信息是指递归调用的深度.它只对递归sql语句提供.通过应用程序直接执行sql语句的这个递归深度为0.
一个深度为n(现在这个例子为1)简单地意味着会执行n-1次其它的语句来完成这个语句的调用.

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 33 (OPS$CHA) (recursive depth: 1)

在解析信息之后,你可能还会看到执行计划信息.实际上如果参数explain被指定,那么可能会看到两次执行计划的信息.
第一次是被不准确地叫作行资源操作.当跟踪被激活游标被关闭时执行计划才会被写入到跟踪文件中.这意味着如果
一个应用程序重复使用游标而没有关闭,那么对于这个重复使用的游标的执行计划将不会写入到跟踪文件中.第二次是
叫作执行计划且只有当参数explain被指定时由tkprof生成的.因此它是后生成的所以也是不是必须的.

执行计划通过执行计划中每一个操作所提供的行数(不是处理的行数),在上面的例子中,表sales有918843行记录,而跟踪
文件只有540328行在通过where子句过滤后被返回.在通过group by子句之后减少到只有16348行.
对于每一个行资源操作,可能会提供以下运行时统计信息

cr:在一致性模式上逻辑读取的数据块的数量
pr:从磁盘中进行物理读取的数据块的数量
pw:使用物理写到磁盘中的数据块的数量
time:是处理操作总的运行时间以微秒为单位.注意这个值是静态被提供的所以不是很准确.事实上为了减小开销可以对它
进行抽样.
cost:评估操作的成本.这个值只能在11g中使用
size:评估操作返回的数据量大小.这个值只能在11g中使用
card:评估操作所返回的行数这个值只能在11g中使用
注意这些值除了card其它都是累计值.它们包括子行源操作.

Rows Row Source Operation
------ ---------------------------------------------------
16348 HASH GROUP BY (cr=1720 pr=2590 pw=2590 time=79 us cost=9990 size=11217129
card=534149)
540328 PARTITION RANGE ALL PARTITION: 1 28 (cr=1720 pr=1649 pw=1649 time=7744 us
cost=496 size=11217129 card=534149)
540328 TABLE ACCESS FULL SALES PARTITION: 1 28 (cr=1720 pr=1649 pw=1649
time=4756 us cost=496
size=11217129 card=534149)
Rows Execution Plan
------ ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
16348 HASH (GROUP BY)
540328 PARTITION RANGE (ALL) PARTITION: START=1 STOP=28
540328 TABLE ACCESS MODE: ANALYZED (FULL) OF 'SALES' (TABLE)
PARTITION: START=1 STOP=28

下面的部分是对sql语句所等待的等待事件信息的汇总:
times:一个等待事件已经等待的时间
Max.Wait:单个等待事件最等待时间最长的以秒为单位
Total Waited:一个等待事件总的等待时间.理论上所有等待事件的等待时间总和应该等待通执行统计所统计的运行时间
与cpu时间之差,其实也可以叫做未被计算的时间.

未被计算的时间
sql跟踪提供了数据库处理每一个操作所花的时间信息.理论上计算应该非常精确.不幸地是找到每个跟踪文件中的每个几分
这一秒这是很罕见的.每当真实的运行时间和在跟踪文件中所计算的时间有差异说明就存有没有被计算的时间存在.
unaccounted-for time = real elapsed time – accounted for time

存在没有被计算的时间有以下原因:
最明显的原因是因为缺少时间信息或等待事件.前者是因为timed_statistics参数被设置为false.后都可能使用的级别为1
或4的sql跟踪.在这两种情况下总是存在没有被计算的时间信息.当你正确使用扩展sql跟踪将会帮你避免这些问题.

一般来说,一个进程可能处在三种状态中:在一个cpu上运行,等待满足要求的设备或在运行队列中等待CPU.工具代码能够
计算前面两个状态的所花费的时间,但对在运行队列中等待所花的时间却一直没有办法计算.因此在cpu资源匮乏的情况下,
总是存在有些时间没有被计算这些时间可能很长.基本上你有两种方法来避免这个问题:要么增加可用cpu时间的数量要么
降低cpu利用率

通过测量工具来执行时间检查是精确的,然而在每一个检查中因为在计算机系统中实现计时器会存一些小的误差.特别是当
检测事件非常短的情况,这些量化的误差可能导致显著的没有被计算的时间存在.这些是自然而然存在的,识差可能会导致
没有被计算时间的值有可能是正数或负数.不幸地是你对它们无能为力.

如果你能消除上面的三个原因那么问题可能是因为检测代码没有对整个个代码进行计算.例如,写跟踪文件本身是没有被计算的
这通常不是问题.如果跟踪文件被写到一个低性能的设备上或都生成的跟踪信息很多它可能导致大量的开销.在这种情况下没有
被计算在内的时间值肯定是正数.为了避免这个问题,你应该简单将跟踪文件写到一个能维持必要吞吐量的设备上,在大多数情况
下你可以强制将跟踪文件写到裸设备上.

由于这些值是高度聚合的,它们将帮助你了解你正在等待什么类型的资源.例如,根据以下信息,几乎整个等待时间都花在物理读
上面了.事实上,db file sequential read等待事件与单块读有关,db file scattered read等待事件与多块读有关.另外
direct path write temp和direct path read temp等待事件与临时表空间有关.注意这里等待次数是941,完全与之前的
hash group by操作的物理写次数相同.

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 32 0.02 0.13
db file scattered read 225 0.04 1.76
direct path write temp 941 0.04 0.40
direct path read temp 941 0.03 1.00

在分析等待事件关键是要知道它们与什么操作相关.幸运地是尽管有上百种等待类型,但最常用的只有少数一些.你能在
oracle database reference手册的索引中找到它们.

接下来继续分析sql语句.

SQL>alter session set events '10046 trace name context forever,level 12';

Session altered.

SQL> DECLARE
2 l_channel_id sh.sales.channel_id%TYPE := 3;
3 BEGIN
4 FOR c IN (SELECT cust_id, extract(YEAR FROM time_id), sum(amount_sold)
5 FROM sh.sales
6 WHERE channel_id = l_channel_id
7 GROUP BY cust_id, extract(YEAR FROM time_id))
8 LOOP
9 NULL;
10 END LOOP;
11 END;
12 /

PL/SQL procedure successfully completed.

SQL>alter session set events '10046 trace name context off';

Session altered.

SQL>select name from v$diag_info where name='Default Trace File';

NAME
----------------------------------------------------------------
Default Trace File

SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_2803.trc
SQL> SELECT value FROM v$diag_info WHERE name = 'Default Trace File';

VALUE
--------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_2803.trc

[oracle@jingyong trace]$ tkprof jingyong_ora_2803.trc jingyong_ora_2803.txt explain=sys/zzh_2046 sort=prsela,exeela,fchela

pl/sql调用的执行统计是受限的.没有关于物理和逻辑读取可用的信息.这是因为资源是被递归调用的sql语句消耗的.
它们与父语句不相关.这意味着对于每一个sql语句来说你将只能看到sql语句本身的资源使用.

call    count  cpu      elapsed    disk       query      current    rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        2     0.04       0.10          0          0          0          0
Execute      2     0.00       0.01          0          0          0          1
Fetch        0     0.00       0.00          0          0          0          0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total        4     0.05       0.11          0          0          0          1

因为pl/sql块不通过数据库递归执行,递归深度没有显示(因为递归深度为0).没有执行计划可用

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 54 (OPS$CHA)

数据库等待SQL*Net message to client指示网络层发送数据到客户端(注意,发送数据跨越网络不被包括在这里面)
等待事件SQL*Net message from client指示从客户端等待数据

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 8.89 13.58