但行好事莫问前程

网络故障造成备库standby logfile的损坏

Posted on 2016年7月6日2016年7月6日 by YongJing(敬勇)

某生产库exadata,oracle 11.2.0.4,客户说物理备库与主库不能进行同步，如是要客户把生产库与备库的alert_sid.log文件打包传给我。查看了一个主库的alert.log日志文件发现从15号开始网络就断断续续的，信息如下:

Wed Jun 15 14:25:48 2016


***********************************************************************

Fatal NI connect error 12543, connecting to:
 (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.137.8.101)(PORT=1521)))

(CONNECT_DATA=(SERVICE_NAME=syyjm)(CID=(PROGRAM=oracle)(HOST=yb01dbadm01.dnsserver)

(USER=oracle))))

  VERSION INFORMATION:
	TNS for Linux: Version 11.2.0.4.0 - Production
	TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production
  Time: 15-JUN-2016 14:25:48
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12543
    
TNS-12543: TNS:destination host unreachable
    ns secondary err code: 12560
    nt main err code: 513
    
TNS-00513: Destination host unreachable
    nt secondary err code: 113
    nt OS err code: 0


***********************************************************************

Fatal NI connect error 12543, connecting to:
 (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.137.8.101)(PORT=1521)))

(CONNECT_DATA=(SERVICE_NAME=syyjm)(CID=(PROGRAM=oracle)(HOST=yb01dbadm01.dnsserver)

(USER=oracle))))

  VERSION INFORMATION:
	TNS for Linux: Version 11.2.0.4.0 - Production
	TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production
  Time: 15-JUN-2016 14:25:51
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12543
    
TNS-12543: TNS:destination host unreachable
    ns secondary err code: 12560
    nt main err code: 513
    
TNS-00513: Destination host unreachable
    nt secondary err code: 113
    nt OS err code: 0
Wed Jun 15 14:26:02 2016
...省略...

对应的备库这个时间点的alert.log日志文件信息如下:

Wed Jun 15 14:24:47 2016


***********************************************************************

Fatal NI connect error 12170.
Wed Jun 15 14:24:47 2016


***********************************************************************

  VERSION INFORMATION:
	TNS for Linux: Version 11.2.0.4.0 - Production
	Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production
	TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production

Fatal NI connect error 12170.
  Time: 15-JUN-2016 14:24:47

  VERSION INFORMATION:
	TNS for Linux: Version 11.2.0.4.0 - Production
	Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production
	TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production
  Tracing not turned on.
  Time: 15-JUN-2016 14:24:47
  Tns error struct:
  Tracing not turned on.
    ns main err code: 12535
  Tns error struct:
    
    ns main err code: 12535
    
TNS-12535: TNS:operation timed out
TNS-12535: TNS:operation timed out
    ns secondary err code: 12560
    ns secondary err code: 12560
    nt main err code: 505
    nt main err code: 505
    
    
TNS-00505: Operation timed out
TNS-00505: Operation timed out
    nt secondary err code: 110
    nt secondary err code: 110
    nt OS err code: 0
    nt OS err code: 0
  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.137.8.73)(PORT=12282))
  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.137.8.73)(PORT=12091))
RFS[4]: Possible network disconnect with primary database
RFS[5]: Possible network disconnect with primary database
Wed Jun 15 14:30:55 2016
Killing 1 processes with pids 25908 (idle RFS by thread/sequence) in order to allow the 

in-transit gap to be queued. Requested by OS process 58349
Wed Jun 15 14:30:57 2016
RFS[10]: Assigned to RFS process 58352
RFS[10]: Selected log 8 for thread 1 sequence 40085 dbid 643576469 branch 867103448
Wed Jun 15 14:30:58 2016
Archived Log entry 3997 added for thread 1 sequence 40085 ID 0x265be592 dest 1:
Wed Jun 15 14:30:59 2016
Primary database is in MAXIMUM PERFORMANCE mode
RFS[11]: Assigned to RFS process 58363
RFS[11]: Selected log 7 for thread 1 sequence 40087 dbid 643576469 branch 867103448
RFS[10]: Selected log 8 for thread 1 sequence 40086 dbid 643576469 branch 867103448
Wed Jun 15 14:31:01 2016
Killing 1 processes with pids 25910 (idle RFS by thread/sequence) in order to retry 

receiving a log after reattaching. Requested by OS process 58367
Wed Jun 15 14:31:01 2016
Archived Log entry 3998 added for thread 1 sequence 40086 ID 0x265be592 dest 1:
RFS[12]: Assigned to RFS process 58367
RFS[12]: Selected log 10 for thread 2 sequence 

35718 dbid 643576469 branch 867103448

从信息RFS[4]: Possible network disconnect with primary database可以看到备库不能与主库进行连接。从信息Killing 1 processes with pids 25908 (idle RFS by thread/sequence) in order to allow the in-transit gap to be queued. Requested by OS process 58349，可知因为不能从主库接受归档日志，且超时而被进程58349给kill掉了，而58349进程是另一个RFS进程用于接受主库所传输过来的归档日志。也就是说，因为网络连接出现了问题，RFS进程在不断的kill，restart重复这样的操作

在2016-06-15 14:34:25这个时间点由于网络恢复日志传输恢复正常,但在2016-05-15 16:59:00这个时间点网络又断了，不能进行日志传输

Wed Jun 15 14:34:25 2016
ARC3: Standby redo logfile selected for thread 1 sequence 40085 for destination 

LOG_ARCHIVE_DEST_2
Wed Jun 15 14:34:27 2016
Thread 1 advanced to log sequence 40087 (LGWR switch)
  Current log# 1 seq# 40087 mem# 0: +DATAC1/yyjm/onlinelog/group_1.261.867103449
Wed Jun 15 14:34:27 2016
Archived Log entry 139166 added for thread 1 sequence 40086 ID 0x265be592 dest 1:
Wed Jun 15 14:34:27 2016
LNS: Standby redo logfile selected for thread 1 sequence 40087 for destination 

LOG_ARCHIVE_DEST_2
ARC3: Standby redo logfile selected for thread 1 sequence 40086 for destination 

LOG_ARCHIVE_DEST_2
Wed Jun 15 15:58:19 2016
Thread 1 advanced to log sequence 40088 (LGWR switch)
  Current log# 2 seq# 40088 mem# 0: +DATAC1/yyjm/onlinelog/group_2.262.867103449
Wed Jun 15 15:58:19 2016
LNS: Standby redo logfile selected for thread 1 sequence 40088 for destination 

LOG_ARCHIVE_DEST_2
Wed Jun 15 15:58:20 2016
Archived Log entry 139173 added for thread 1 sequence 40087 ID 0x265be592 dest 1:
Wed Jun 15 16:59:00 2016

***********************************************************************

Fatal NI connect error 12170.

  VERSION INFORMATION:
	TNS for Linux: Version 11.2.0.4.0 - Production
	Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production
	TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.4.0 - Production
  Time: 15-JUN-2016 16:59:00
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12535
    
TNS-12535: TNS:operation timed out
    ns secondary err code: 12560
    nt main err code: 505
    
TNS-00505: Operation timed out
    nt secondary err code: 110
    nt OS err code: 0
  Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.137.32.4)(PORT=1222))
Wed Jun 15 17:05:01 2016

从备库日志信息来看，备库其实在2016-06-15 16:08:43这个时间点网络就已经断开了

Wed Jun 15 16:08:43 2016
RFS[9]: Possible network disconnect with primary database
Wed Jun 15 16:08:43 2016
RFS[6]: Possible network disconnect with primary database
Wed Jun 15 17:56:36 2016

这个DG环境的网络时断时连的情况从2016-06-15一直到2016-06-19号，从备库的日志信息可以看到如下信息，不能在备库创建归档日志文件，从信息RFS[23]: No standby redo logfiles available for thread 1 与RFS[24]: No standby redo logfiles available for thread 2，以及ORA-00312: online log 9 thread 1: ‘/data/syyjm/datafile/standy03’可知因为备库的standby log的standy03出现了损坏。

Sun Jun 19 12:15:21 2016
RFS[23]: Assigned to RFS process 18969
RFS[23]: No standby redo logfiles available for thread 1 
Creating archive destination file : /arch/syyjm/1_40277_867103448.dbf (12905 blocks)
Sun Jun 19 12:15:56 2016
Archiver process freed from errors. No longer stopped
Sun Jun 19 12:15:56 2016
Primary database is in MAXIMUM PERFORMANCE mode
Re-archiving standby log 11 thread 2 sequence 36153
RFS[24]: Assigned to RFS process 18976
RFS[24]: No standby redo logfiles available for thread 2 
...省略...
Sun Jun 19 12:21:03 2016
Unable to create archive log file '/arch/syyjm/1_40254_867103448.dbf'
ARC3: Error 19504 Creating archive log file to '/arch/syyjm/1_40254_867103448.dbf'
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance syyjm - Archival Error
ORA-16038: log 9 sequence# 40254 cannot be archived
ORA-19504: failed to create file ""
ORA-00312: online log 9 thread 1: '/data/syyjm/datafile/standy03'
Sun Jun 19 12:21:03 2016
...省略...
Sun Jun 19 12:31:03 2016
Suppressing further error logging of LOG_ARCHIVE_DEST_1.
Suppressing further error logging of LOG_ARCHIVE_DEST_1.
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance syyjm - Archival Error
ORA-16038: log 9 sequence# 40254 cannot be archived
ORA-19504: failed to create file ""
ORA-00312: online log 9 thread 1: '/data/syyjm/datafile/standy03'
Sun Jun 19 12:31:03 2016
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance syyjm - Archival Error
ORA-16014: log 9 sequence# 40254 not archived, no available destinations
ORA-00312: online log 9 thread 1: '/data/syyjm/datafile/standy03'
...省略...
Wed Jun 22 11:22:11 2016
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance syyjm - Archival Error
ORA-16038: log 9 sequence# 40254 cannot be archived
ORA-19504: failed to create file ""
ORA-00312: online log 9 thread 1: '/data/syyjm/datafile/standy03'
Wed Jun 22 11:22:11 2016
ARCH: Archival stopped, error occurred. Will continue retrying
ORACLE Instance syyjm - Archival Error
ORA-16014: log 9 sequence# 40254 not archived, no available destinations
ORA-00312: online log 9 thread 1: '/data/syyjm/datafile/standy03'

从主库alert日志文件也可以看到与不能进行对远程目录进行归档操作

Wed Jun 22 11:05:22 2016
Thread 1 advanced to log sequence 41141 (LGWR switch)
  Current log# 2 seq# 41141 mem# 0: +DATAC1/yyjm/onlinelog/group_2.262.867103449
FAL[server, ARC0]: FAL archive failed, see trace file.
ARCH: FAL archive failed. Archiver continuing
ORACLE Instance yyjm1 - Archival Error. Archiver continuing.
FAL[server, ARC1]: FAL archive failed, see trace file.
ARCH: FAL archive failed. Archiver continuing
ORACLE Instance yyjm1 - Archival Error. Archiver continuing.
Wed Jun 22 11:05:23 2016
Archived Log entry 142098 added for thread 1 sequence 41140 ID 0x265be592 dest 1:

通过主库与备库的alert.log文件信息可以清楚的知道原因是因为DG环境的网络时断时连引起了备库的RFS进程在重复kill,restart操作过程中损坏了standby log文件，解决方法很简单就是重建备库被损不的standby log文件。

linux下/dev/shm的大小引发ORA-00845: MEMORY_TARGET not supported on this system故障

Posted on 2016年6月16日2016年11月30日 by YongJing(敬勇)

Linux操作系统，oracle 11.2.0.4 启动实例时出现如下错误:

SQL> startup nomount pfile=/u03/app/oracle/11.2.0/db/dbs/initcssb.ora
ORA-00845: MEMORY_TARGET not supported on this system

查看错误帮助信息

[oracle11@oracle11g dbs]$ oerr ora 845
00845, 00000, "MEMORY_TARGET not supported on this system"
// *Cause: The MEMORY_TARGET parameter was not supported on this operating system or /dev/shm was not sized correctly on Linux.
// *Action: Refer to documentation for a list of supported operating systems. Or, size /dev/shm to be at least the SGA_MAX_SIZE on each Oracle instance running on the system.

错误原因是这个操作系统不支持MEMORY_TARGET参数或/dev/shm在Linux上的大小不正确造成的，这是该操作系统上的第二个实例，第一个实例设置了MEMORY_TARGET参数，所以并不是不支持这个参数，原因就只有/dev/shm大小不正确了，解决方法是要将/dev/shm的最小值设置为操作系统上运行实例SGA_MAX_SIZE所设置的大小。/dev/shm/是linux下一个目录，/dev/shm目录不在磁盘上，而是在内存里，因此使用linux /dev/shm/的效率非常高，直接写进内存。
tmpfs有以下特点：
1.tmpfs 是一个文件系统，而不是块设备；您只是安装它，它就可以使用了。
2.动态文件系统的大小。
3.tmpfs 的另一个主要的好处是它闪电般的速度。因为典型的 tmpfs 文件系统会完全驻留在 RAM 中，读写几乎可以是瞬间的。
4.tmpfs 数据在重新启动之后不会保留，因为虚拟内存本质上就是易失的。所以有必要做一些脚本做诸如加载、绑定的操作。

linux下/dev/shm的容量默认最大为内存的一半大小，使用df -h命令可以看到。但它并不会真正的占用这块内存，如果/dev/shm/下没有任何文件，它占用的内存实际上就是0字节；如果它最大为1G，里头放有100M文件，那剩余的900M仍然可为其它应用程序所使用，但它所占用的100M内存，是绝不会被系统回收重新划分的。

linux /dev/shm容量(大小)是可以调整，在有些情况下(如oracle数据库)默认的最大一半内存不够用，并且默认的inode数量很低一般都要调高些，这时可以用mount命令来管理它。
mount -o size=1500M -o nr_inodes=1000000 -o noatime,nodiratime -o remount /dev/shm
在2G的机器上，将最大容量调到1.5G，并且inode数量调到1000000，这意味着大致可存入最多一百万个小文件通过/etc/fstab文件来修改/dev/shm的容量(增加size选项即可),修改后，重新挂载即可。

这里该实例的SGA_MAX_SIZE为1G,下面的命令查看/dev/shm的大小。

[root@oracle11g ~]# df -lh
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda1              23G   20G  1.6G  93% /
/dev/sdb1             9.9G  5.8G  3.6G  62% /u02
tmpfs                 2G    1.3M  0.7G  65% /dev/shm

从上面结果可以看到/dev/shm可用大小只有0.7G,执行下面的命令来进行修改。

[root@oracle11g ~]# vi /etc/fstab
LABEL=/                 /                       ext3    defaults        1 1
/dev/sdb1               /u02                    ext3    defaults        1 2
tmpfs                   /dev/shm                tmpfs   defaults,size=4G        0 0
devpts                  /dev/pts                devpts  gid=5,mode=620  0 0
sysfs                   /sys                    sysfs   defaults        0 0
proc                    /proc                   proc    defaults        0 0
LABEL=SWAP-sda2         swap                    swap    defaults        0 0

"/etc/fstab" 7L, 540C written

卸载/dev/shm，但/dev/shm正被访问

[root@oracle11g ~]#  umount /dev/shm
umount: /dev/shm: device is busy
umount: /dev/shm: device is busy

用fuser处理,fuser命令,-k:kill processes accessing the named file(杀死所有正在访问指定文件的进程),-m 表示指定文件所在的文件系统或者块设备（处于 mount 状态）。所有访问该文件系统的进程都被列出。

[root@oracle11g ~]# fuser -km /dev/shm
/dev/shm:             3152m  3154m  3156m  3160m  3162m  3164m  3166m  3168m  3170m  3172m  3174m  3176m  3178m  3180m  3182m  3184m  3186m  3193m  3195m  3197m  3199m  3201m  3236m  3248m  3250m  3256m  3292m  4366m
[root@oracle11g ~]#  umount /dev/shm
[root@oracle11g ~]# mount /dev/shm
[root@oracle11g ~]# df -lh
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda1              23G   20G  1.6G  93% /
/dev/sdb1             9.9G  5.8G  3.6G  62% /u02
tmpfs                 4.0G     0  4.0G   0% /dev/shm

再重新启动实例
SQL> startup nomount pfile=/u03/app/oracle/11.2.0/db/dbs/initcssb.ora
ORACLE instance started.

Total System Global Area 1334786560 bytes
Fixed Size 1364480 bytes
Variable Size 171970048 bytes
Database Buffers 1155189248 bytes
Redo Buffers 6262784 bytes

小结:Oracle 11g的AMM内存管理模式就是使用/dev/shm，所以有时候修改MEMORY_TARGET或者MEMORY_MAX_TARGET会出现ORA-00845的错误，在安装配置实例内存时为了避免出现这个故障可以对Linux系统中的/dev/shm进行调整，让其可用大小至少等于实例的
sga_max_size。

Stored Outline

Posted on 2016年6月2日 by YongJing(敬勇)

存储概要(stored outline) 是固定执行计划的一种传统技术。在Oracle 11g以前要固定执行计划只能使用outline,Oracle11g中引入了SPM来固定执行计划，创建stored outline需要有create any outline权限，为了管理还需要有对dba_outlines的查询权限。
创建stored outline

在会话级outline
ALTER SESSION SET CREATE_STORED_OUTLINES = TRUE | FALSE |
如果将create_stored_outlines设置为true，当outlines的目录名设置为default时，Oracle会为该会话所执行的SQL语句创建outlines。

SQL> var x varchar2(20)
SQL> exec :x:='Kabab'

PL/SQL procedure successfully completed.

SQL> alter session set create_stored_outlines=true;

Session altered.

SQL> select * from t1 where t_meal=:x;

      T_ID T_MEAL
---------- --------------------
     79999 Kabab

SQL> select * from dba_outlines;
 
NAME                           OWNER                          CATEGORY                       USED   TIMESTAMP   VERSION                                                 

         SQL_TEXT                                                                         SIGNATURE                        COMPATIBLE   ENABLED  FORMAT
------------------------------ ------------------------------ ------------------------------ ------ ----------- 

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

-------------------------------- ------------ -------- ------
SYS_OUTLINE_16060109390284501  JY                             DEFAULT                        UNUSED 2016/6/1 9: 10.2.0.5.0                                              

         select * from t1 where t_meal=:x                                                 7DE4EC0CD8B1DA6C96B213F5D6DC349C COMPATIBLE   ENABLED  NORMAL

可以看到虽然创建了存储概要，但是并没有使用，因为USED的值为UNUSED

SQL> select * from dba_outline_hints where owner='JY' and name='SYS_OUTLINE_16060109390284501';
 
NAME                           OWNER                                NODE      STAGE   JOIN_POS HINT
------------------------------ ------------------------------ ---------- ---------- ---------- 

--------------------------------------------------------------------------------
SYS_OUTLINE_16060109390284501  JY                                      1          1          1 INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("T1"."T_MEAL"))
SYS_OUTLINE_16060109390284501  JY                                      1          1          0 OUTLINE_LEAF(@"SEL$1")
SYS_OUTLINE_16060109390284501  JY                                      1          1          0 ALL_ROWS
SYS_OUTLINE_16060109390284501  JY                                      1          1          0 OPTIMIZER_FEATURES_ENABLE('10.2.0.5')
SYS_OUTLINE_16060109390284501  JY                                      1          1          0 IGNORE_OPTIM_EMBEDDED_HINTS


SQL> alter session set use_stored_outlines=true;

Session altered.

SQL> var x varchar2(20)
SQL> exec :x:='Pasta'

PL/SQL procedure successfully completed.

SQL> select * from t1 where t_meal=:x;

      T_ID T_MEAL
---------- --------------------
     80000 Pasta

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  7runhd24kgqsf, child number 0
-------------------------------------
select * from t1 where t_meal=:x

Plan hash value: 141743202

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

| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |               |       |       |     2 (100)|          |

|   1 |  TABLE ACCESS BY INDEX ROWID| T1            |     1 |    11 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_T1_T_MEAL |     1 |       |     1   (0)| 00:00:01 |

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


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T_MEAL"=:X)

Note
-----
   - outline "SYS_OUTLINE_16060109390284501" used for this statement


23 rows selected.

从Note部分的outline “SYS_OUTLINE_16060109390284501” used for this statement可以确认语句使用了创建的outline

SQL> select * from dba_outlines;
 
NAME                           OWNER                          CATEGORY                       USED   TIMESTAMP   VERSION                                                 

         SQL_TEXT                                                                         SIGNATURE                        COMPATIBLE   ENABLED  FORMAT
------------------------------ ------------------------------ ------------------------------ ------ ----------- 

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

-------------------------------- ------------ -------- ------
SYS_OUTLINE_16060109390284501  JY                             DEFAULT                        USED   2016/6/1 9: 10.2.0.5.0                                              

         select * from t1 where t_meal=:x                                                 7DE4EC0CD8B1DA6C96B213F5D6DC349C COMPATIBLE   ENABLED  NORMAL

从dba_outlines.USED也可以看到现在变为了USED

删除outline
先将outline设置为UNUSED

SQL> exec dbms_outln.clear_used(name =>'SYS_OUTLINE_16060109390284501');
 
PL/SQL procedure successfully completed
 
SQL> select * from dba_outlines;
 
NAME                           OWNER                          CATEGORY                       USED   TIMESTAMP   VERSION                                                 

         SQL_TEXT                                                                         SIGNATURE                        COMPATIBLE   ENABLED  FORMAT
------------------------------ ------------------------------ ------------------------------ ------ ----------- 

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

-------------------------------- ------------ -------- ------
SYS_OUTLINE_16060109390284501  JY                             DEFAULT                        UNUSED 2016/6/1 9: 10.2.0.5.0                                              

         select * from t1 where t_meal=:x                                                 7DE4EC0CD8B1DA6C96B213F5D6DC349C COMPATIBLE   ENABLED  NORMAL

再删除

SQL> exec dbms_outln.drop_unused;
 
PL/SQL procedure successfully completed
 
SQL> select * from dba_outlines;
 
NAME                           OWNER                          CATEGORY                       USED   TIMESTAMP   VERSION                                                 

         SQL_TEXT                                                                         SIGNATURE                        COMPATIBLE   ENABLED  FORMAT
------------------------------ ------------------------------ ------------------------------ ------ ----------- 

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

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

在系统级创建outline

ALTER SYSTEM SET CREATE_STORED_OUTLINES = TRUE | FALSE | [NOOVERRIDE]

当设置为true时，Oracle将会对系统中执行的每个查询自动创建与存储outline。这些outline存储在default目录中。如果特定的查询在default目录中有了一个outline后，那么这个outline将仍然会被保留并且不会创建新的outline。当设置为nooverride时，指定系统级的设置是滞将会覆盖会话级的设置。

为指定的SQL语句创建outline
下面的命令为指定的SQL语句创建outline
CREATE OUTLINE [ for category ] ON select …. ;

要想使用所创建的outline，那么执行的SQL语句的SQL TEXT必须与创建命令所使用的SQL TEXT相同。

SQL> create outline outline_test for category outline_test on select * from t1 where t_meal=:x;

Outline created.

SQL> select * from dba_outlines;
 
NAME                           OWNER                          CATEGORY                       USED   TIMESTAMP   VERSION                                                 

         SQL_TEXT                                                                         SIGNATURE                        COMPATIBLE   ENABLED  FORMAT
------------------------------ ------------------------------ ------------------------------ ------ ----------- 

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

-------------------------------- ------------ -------- ------
OUTLINE_TEST                   JY                             OUTLINE_TEST                   UNUSED 2016/6/1 11 10.2.0.5.0                                              

         select * from t1 where t_meal=:x                                                 7DE4EC0CD8B1DA6C96B213F5D6DC349C COMPATIBLE   ENABLED  NORMAL
 
SQL> select * from dba_outline_hints where owner='JY' and name='OUTLINE_TEST';
 
NAME                           OWNER                                NODE      STAGE   JOIN_POS HINT
------------------------------ ------------------------------ ---------- ---------- ---------- 

--------------------------------------------------------------------------------
OUTLINE_TEST                   JY                                      1          1          1 FULL(@"SEL$1" "T1"@"SEL$1")
OUTLINE_TEST                   JY                                      1          1          0 OUTLINE_LEAF(@"SEL$1")
OUTLINE_TEST                   JY                                      1          1          0 ALL_ROWS
OUTLINE_TEST                   JY                                      1          1          0 OPTIMIZER_FEATURES_ENABLE('10.2.0.5')
OUTLINE_TEST                   JY                                      1          1          0 IGNORE_OPTIM_EMBEDDED_HINTS




SQL> alter session set use_stored_outlines=OUTLINE_TEST;

Session altered.

SQL> var x varchar2(20)
SQL> exec :x:='Pasta'

PL/SQL procedure successfully completed.

SQL> select * from t1 where t_meal=:x;

      T_ID T_MEAL
---------- --------------------
     80000 Pasta

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  7runhd24kgqsf, child number 0
-------------------------------------
select * from t1 where t_meal=:x

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |    47 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    11 |    47   (5)| 00:00:01 |

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

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("T_MEAL"=:X)

Note
-----
   - outline "OUTLINE_TEST" used for this statement


22 rows selected.

从Note部分，可以看到outline “OUTLINE_TEST” used for this statement，说明使用了创建的outline。

当不使用outline时，优化器选择使用索引范围扫描，而不是使用outline时的全表扫描

SQL> alter session set use_stored_outlines=false;

Session altered.

SQL> select * from t1 where t_meal=:x;

      T_ID T_MEAL
---------- --------------------
     80000 Pasta

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  7runhd24kgqsf, child number 1
-------------------------------------
select * from t1 where t_meal=:x

Plan hash value: 141743202

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

| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |               |       |       |     2 (100)|          |

|   1 |  TABLE ACCESS BY INDEX ROWID| T1            |     1 |    11 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_T1_T_MEAL |     1 |       |     1   (0)| 00:00:01 |

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


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T_MEAL"=:X)


19 rows selected.

outline的使用
outline会覆盖所有其它优化器设置。可以在会话级与系统级进行设置
ALTER SESSION SET USE_STORED_OUTLINES = TRUE | FALSE |
ALTER SYSTEM SET USE_STORED_OUTLINES = TRUE | FALSE |
如果use_stored_outlines设置为true,那么将会使用default目录中的outline
如果use)stored_outlines设置为目录，那么将会使用这个目录中的outline,设置目录时不需要使用引号。

为了永久的启用outline可以通过创建一个数据库启动触发器来设置use_stored_outlines

create or replace trigger enable_outlines_trig 
after startup on database 
begin 
execute immediate('alter system set use_stored_outlines=true'); 
end;

使用现有游标来创建stored outline

SQL> select * from t1 where t_meal=:x;

      T_ID T_MEAL
---------- --------------------
     80000 Pasta

SQL> select hash_value, child_number, sql_text from v$sql where sql_text like 'select * from t1 where t_meal=:x'; 
 
HASH_VALUE CHILD_NUMBER SQL_TEXT
---------- ------------ --------------------------------------------------------------------------------
2301090574            0 select * from t1 where t_meal=:x

SQL> exec dbms_outln.create_outline(2301090574,0);
 
PL/SQL procedure successfully completed

SQL> select * from dba_outlines;
 
NAME                           OWNER                          CATEGORY                       USED   TIMESTAMP   VERSION                                                 

         SQL_TEXT                                                                         SIGNATURE                        COMPATIBLE   ENABLED  FORMAT
------------------------------ ------------------------------ ------------------------------ ------ ----------- 

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

-------------------------------- ------------ -------- ------
SYS_OUTLINE_16060112321977339  JY                             DEFAULT                        UNUSED 2016/6/1 12 10.2.0.5.0                                              

         select * from t1 where t_meal=:x                                                 7DE4EC0CD8B1DA6C96B213F5D6DC349C COMPATIBLE   ENABLED  NORMAL

如果是10.2.0.4在创建outline之前需要先进行以下设置

SQL> alter session set create_stored_outlines = true;   


-- This step is to avoid Bug:5454975 fixed 10.2.0.4

使用现在游标来创建stored outline的例子如下:

SQL> var x number
SQL> exec :x:=1

PL/SQL procedure successfully completed.

SQL> select * from t1 where c1=:x;

        C1
----------
         1

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  0m63029gwn10n, child number 0
-------------------------------------
select * from t1 where c1=:x

Plan hash value: 1369807930

---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |       |       |     1 (100)|          |
|*  1 |  INDEX RANGE SCAN| IDX_T1 |     1 |     4 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("C1"=:X)


18 rows selected.

SQL> select hash_value, child_number, sql_text,sql_id from v$sql where sql_text like 'select * from t1%'; 
 
HASH_VALUE CHILD_NUMBER SQL_TEXT                                                                         SQL_ID
---------- ------------ -------------------------------------------------------------------------------- -------------
1607074836            0 select * from t1 where c1=:x                                                     0m63029gwn10n

SQL> exec dbms_outln.create_outline(1607074836,0);

PL/SQL procedure successfully completed.

SQL> select name,owner,category,used from dba_outlines;

NAME                           OWNER                          CATEGORY                       USED
------------------------------ ------------------------------ ------------------------------ ------
SYS_OUTLINE_16060115345355101  JY                             DEFAULT                        UNUSED


SQL> select * from dba_outline_hints where owner='JY' and name='SYS_OUTLINE_16060115345355101';

NAME                           OWNER                                NODE   STAGE      JOIN_POS   HINT
------------------------------ ------------------------------ ----------   ---------- ---------- 

--------------------------------------------------------------------------------
SYS_OUTLINE_16060115345355101  JY                                      1         1          1    INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."C1"))

SYS_OUTLINE_16060115345355101  JY                                      1         1          0    OUTLINE_LEAF(@"SEL$1")

SYS_OUTLINE_16060115345355101  JY                                      1         1          0    ALL_ROWS

SYS_OUTLINE_16060115345355101  JY                                      1         1          0    DB_VERSION('11.2.0.4')

SYS_OUTLINE_16060115345355101  JY                                      1         1          0    OPTIMIZER_FEATURES_ENABLE('11.2.0.4')

SYS_OUTLINE_16060115345355101  JY                                      1         1          0    IGNORE_OPTIM_EMBEDDED_HINTS


6 rows selected.

SQL> alter session set use_stored_outlines=true;

Session altered.

SQL> select * from t1 where c1=:x;

        C1
----------
         1

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  0m63029gwn10n, child number 1
-------------------------------------
select * from t1 where c1=:x

Plan hash value: 1369807930

---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |       |       |     1 (100)|          |
|*  1 |  INDEX RANGE SCAN| IDX_T1 |     1 |     4 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("C1"=:X)

Note
-----
   - outline "SYS_OUTLINE_16060115345355101" used for this statement


22 rows selected.

使用另一个存储概要的执行计划来编辑存储概要

SQL> alter session set create_stored_outlines=true;

Session altered.

SQL> var n number
SQL> exec :n:=499990;

PL/SQL procedure successfully completed.

SQL> select count(*) from t1 where c1>:n;

  COUNT(*)
----------
        10

SQL> select count(*) from t1 where c1>1;

  COUNT(*)
----------
    499999

SQL> alter session set create_stored_outlines=false;

Session altered.

查询自动创建outline是否成功，从下面的查询结果可以看到创建了两个outline

SQL> select name,owner,category,used from dba_outlines;
 
NAME                           OWNER                          CATEGORY                       USED
------------------------------ ------------------------------ ------------------------------ ------
SYS_OUTLINE_16060210103585707  JY                             DEFAULT                        UNUSED
SYS_OUTLINE_16060210120211108  JY                             DEFAULT                        UNUSED

测试使用了outline的执行计划是否是使用了绑定变量的使用索引扫描，使用literal值的使用全表扫描

SQL> alter session set use_stored_outlines=true;

Session altered.

SQL> select count(*) from t1 where c1>:n;

  COUNT(*)
----------
        10

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  fxm7fcktguhvb, child number 1
-------------------------------------
select count(*) from t1 where c1>:n

Plan hash value: 1339937034

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE   |           |     1 |     4 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_T1_C1 |    10 |    40 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1">:N)

Note
-----
   - outline "SYS_OUTLINE_16060210103585707" used for this statement


23 rows selected.

可以看到使用了绑定变量的SQL使用outline “SYS_OUTLINE_16060210103585707”,执行计划为索引范围扫描

SQL> select count(*) from t1 where c1>1;

  COUNT(*)
----------
    499999

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  dt7wfh731mssj, child number 1
-------------------------------------
select count(*) from t1 where c1>1

Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   205 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     4 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |   499K|  1953K|   205   (6)| 00:00:03 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("C1">1)

Note
-----
   - outline "SYS_OUTLINE_16060210120211108" used for this statement


23 rows selected.

可以看到使用了literal值的SQL使用outline “SYS_OUTLINE_16060210120211108” ,执行计划为全表扫描。

下面将两个outline进行重命名,SYS_OUTLINE_16060210103585707(索引范围扫描)修改为OL1,SYS_OUTLINE_16060210120211108(全表扫描)修改为OL2.

SQL> alter outline SYS_OUTLINE_16060210103585707 rename to OL1;

Outline altered.

SQL> alter outline SYS_OUTLINE_16060210120211108 rename to OL2;

Outline altered.

SQL> select name,owner,category,used from dba_outlines;
 
NAME                           OWNER                          CATEGORY                       USED
------------------------------ ------------------------------ ------------------------------ ------
OL1                            JY                             DEFAULT                        USED
OL2                            JY                             DEFAULT                        USED

再次重新执行两个SQL语句，看是否能使用重命名之后的outline

SQL> select count(*) from t1 where c1>:n;

  COUNT(*)
----------
        10

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  fxm7fcktguhvb, child number 1
-------------------------------------
select count(*) from t1 where c1>:n

Plan hash value: 1339937034

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |       |       |     3 (100)|          |
|   1 |  SORT AGGREGATE   |           |     1 |     4 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_T1_C1 |    10 |    40 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1">:N)

Note
-----
   - outline "OL1" used for this statement

23 rows selected.

使用绑定变量的语句使用outline(OL1)，执行计划为索引范围扫描

SQL> select count(*) from t1 where c1>1;

  COUNT(*)
----------
    499999

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  dt7wfh731mssj, child number 0
-------------------------------------
select count(*) from t1 where c1>1

Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   205 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     4 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |   499K|  1953K|   205   (6)| 00:00:03 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("C1">1)

Note
-----
   - outline "OL2" used for this statement


23 rows selected.

使用literal值的语句使用outline(OL2)，执行计划为全表扫描

如果两个outline属于不同的目录，可以执行以下语句修改为相同目录

SQL> alter outline OL1 change category to DEFAULT;

Outline altered.

SQL> alter outline OL2 change category to DEFAULT;

Outline altered.

使用outline:OL1,OL2来创建两个私有outline:OLF,OLT,这里用outline(OL2/OLT)的信息来更新outline(OL1,OLF)，最终的目的是当执行原来使用绑定变是的语句是使用的outline将变成OL2(全表扫描)

SQL> create private outline OLF from OL1;

Outline created.

SQL> create private outline OLT from OL2;

Outline created.

SQL> select count(*) from ol$ where ol_name='OLF';

  COUNT(*)
----------
         1

SQL> select count(*) from ol$ where ol_name='OLT';

  COUNT(*)
----------
         1


SQL> update ol$ set hintcount=(select hintcount from ol$ where ol_name='OLT') where ol_name='OLF';

1 row updated.

SQL> delete from ol$ where ol_name='OLT';

1 row deleted.

SQL> update ol$ set ol_name='OLT' where ol_name='OLF';

1 row updated.


SQL> commit;

Commit complete.

重新同步私有outlien(OLT)

SQL> execute dbms_outln_edit.refresh_private_outline('OLT');

PL/SQL procedure successfully completed.

测试私有outline，查看使用绑定变量的语句的执行计划是不是变为全表扫描。

SQL> alter session set use_private_outlines=true;

Session altered.

SQL> var n number
SQL> exec :n:=499990;

PL/SQL procedure successfully completed.

SQL> select count(*) from t1 where c1>:n;

  COUNT(*)
----------
        10

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

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
SQL_ID  fxm7fcktguhvb, child number 1
-------------------------------------
select count(*) from t1 where c1>:n

Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   205 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     4 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |    10 |    40 |   205   (6)| 00:00:03 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("C1">:N)

Note
-----
   - outline "OLT" used for this statement


23 rows selected.

使用私有outline(OLT)来替换公有outline(OL1)

SQL> create or replace outline OL1 from private OLT;

Outline created.

当outline(OL1)使用私有outline(OLT)替之后，OL1变为UNUSED

SQL> select name,owner,category,used from dba_outlines;
 
NAME                           OWNER                          CATEGORY                       USED
------------------------------ ------------------------------ ------------------------------ ------
OL1                            JY                             DEFAULT                        UNUSED
OL2                            JY                             DEFAULT                        USED

查询OL1所包含的执行计划确实就变成了OL2的全表扫描了。

SQL> select * from dba_outline_hints where owner='JY' and name='OL1';
 
NAME                           OWNER                                NODE      STAGE   JOIN_POS HINT
------------------------------ ------------------------------ ---------- ---------- ---------- 

--------------------------------------------------------------------------------
OL1                            JY                                      1          1          0 OUTLINE_LEAF(@"SEL$1")
OL1                            JY                                      1          1          0 ALL_ROWS
OL1                            JY                                      1          1          0 OPTIMIZER_FEATURES_ENABLE('10.2.0.5')
OL1                            JY                                      1          1          0 IGNORE_OPTIM_EMBEDDED_HINTS
OL1                            JY                                      1          1          1 FULL(@"SEL$1" "T1"@"SEL$1")

执行查询(select count(*) from t1 where c1>:n)来测试outline(OL1)的执行计划是否为全表扫描

SQL> var n number
SQL> exec :n:=499990;

PL/SQL procedure successfully completed.

SQL> select count(*) from t1 where c1>:n;

  COUNT(*)
----------
        10

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

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
SQL_ID  fxm7fcktguhvb, child number 2
-------------------------------------
select count(*) from t1 where c1>:n

Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   205 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     4 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |    10 |    40 |   205   (6)| 00:00:03 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("C1">:N)

Note
-----
   - outline "OL1" used for this statement


23 rows selected.

编辑outline(适用于10g与11g)
语句(select count(*) from t1 where c1>1)已经创建了outline(OL2)，其执行计划为全表扫描，通过编辑方式将其修改为索引范围扫描。

SQL> select count(*) from t1 where c1>1;

  COUNT(*)
----------
    499999

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

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
SQL_ID  dt7wfh731mssj, child number 0
-------------------------------------
select count(*) from t1 where c1>1

Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   205 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     4 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |   499K|  1953K|   205   (6)| 00:00:03 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("C1">1)

Note
-----
   - outline "OL2" used for this statement


23 rows selected.

使用outline(OL2)来创建私有outline(JYOL)

SQL> create private outline JYOL from OL2;

Outline created.

SQL> select count(*) from ol$ where ol_name='JYOL';

  COUNT(*)
----------
         1

通过更新ol$hints表来更新私有outline(JYOL)的执行计划，将全表扫描更新为索引范围扫描

SQL> select ol_name,hint#,hint_text,category from ol$hints where ol_name='JYOL'; 
                                                                                 
OL_NAME                             HINT# HINT_TEXT                              CATEGORY
------------------------------ ---------- -------------------------------------- ----------------
JYOL                                    1 OUTLINE_LEAF(@"SEL$1")                 DEFAULT
JYOL                                    2 ALL_ROWS                               DEFAULT
JYOL                                    3 OPTIMIZER_FEATURES_ENABLE('10.2.0.5')  DEFAULT
JYOL                                    4 IGNORE_OPTIM_EMBEDDED_HINTS            DEFAULT
JYOL                                    5 FULL(@"SEL$1" "T1"@"SEL$1")            DEFAULT
                                                                                 
SQL> update ol$hints set hint_text='INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."C1"))' where hint#=5 and ol_name='JYOL';

1 row updated.

SQL> select ol_name,hint#,hint_text,category from ol$hints where ol_name='JYOL';     
                                                                                     
OL_NAME                             HINT# HINT_TEXT                                  CATEGORY     
------------------------------ ---------- ------------------------------------------ -------------
JYOL                                    1 OUTLINE_LEAF(@"SEL$1")                     DEFAULT
JYOL                                    2 ALL_ROWS                                   DEFAULT
JYOL                                    3 OPTIMIZER_FEATURES_ENABLE('10.2.0.5')      DEFAULT
JYOL                                    4 IGNORE_OPTIM_EMBEDDED_HINTS                DEFAULT
JYOL                                    5 INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."C1"))   DEFAULT
                                                                                     
SQL> commit;                                                                         
                                                                                     
Commit complete.

使用编辑后的执行计划重新同步私有outline(JYOL)

SQL> execute dbms_outln_edit.refresh_private_outline('JYOL');

PL/SQL procedure successfully completed.

测试将全表扫描修改为索引范围扫描后的私有outline能否使用

SQL> alter session set use_private_outlines=true;

Session altered.

SQL> select count(*) from t1 where c1>1;

  COUNT(*)
----------
    499999

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

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
SQL_ID  dt7wfh731mssj, child number 1
-------------------------------------
select count(*) from t1 where c1>1

Plan hash value: 1339937034

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |       |       |  1128 (100)|          |
|   1 |  SORT AGGREGATE   |           |     1 |     4 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_T1_C1 |   499K|  1953K|  1128   (2)| 00:00:14 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1">1)

Note
-----
   - outline "JYOL" used for this statement


23 rows selected.

使用将全表扫描修改为索引范围扫描的私有outline(JYOL)来更新公有outline(OL2)，让OL2的执行计划从全表扫描变为索引范围扫描

SQL> create or replace outline OL2 from private JYOL;

Outline created.

测试outline(OL2)的执行计划是否变为了索引范围扫描

SQL> alter session set use_private_outlines=false;

Session altered.

SQL> alter session set use_stored_outlines=true;

Session altered.

SQL> select count(*) from t1 where c1>1;

  COUNT(*)
----------
    499999

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

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
SQL_ID  dt7wfh731mssj, child number 0
-------------------------------------
select count(*) from t1 where c1>1

Plan hash value: 1339937034

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |       |       |  1128 (100)|          |
|   1 |  SORT AGGREGATE   |           |     1 |     4 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_T1_C1 |   499K|  1953K|  1128   (2)| 00:00:14 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1">1)

Note
-----
   - outline "OL2" used for this statement


23 rows selected.

从结果可以看到，OL2的执行计划确实从原来的全表扫描变为了索引范围扫描，说明修改执行计划是成功的。

传输stored outline(从一个数据库传输到另一个数据库,适用于9i及以版本)
1.将outline(OL2)的目录从DEFAULT修改为JY

SQL> alter outline OL2 change category to JY;

Outline altered.

2.使用exp工具导入stored outlines
使用带query参数的exp命令是为了只导为目录JY中的outline数据。可以一次传输多个stored outlines

windows平台的导出命令如下:

exp system/ file=myoutln.dmp tables=(outln.ol$,outln.ol$hints,outln.ol$nodes) query=\”where category=’MYCAT’\” statistics=none)

unix平台的导出命令如下:
exp system/ file=myoutln.dmp tables=$outln.ol\$,outln.ol\$hints,outln.ol\$nodes$ query=\”where category=\’MYCAT\’\” statistics=none

OpenVMS平台的导出命令如下:
exp system/ file=myoutln.dmp tables=(outln.ol$,outln.ol$hints,outln.ol$nodes) query=”””where category=’MYCAT'””” statistics=none

这里测试平台是linux,导出命令如下:

[oracle@weblogic28 ~]$ exp jy/jy  file=/home/oracle/myoutln.dmp log=/home/oracle/myoutln.log tables=\(outln.ol\$,outln.ol\$hints,outln.ol\$nodes\) query=\"where 

category=\'JY\'\" statistics=none

Export: Release 10.2.0.5.0 - Production on Thu Jun 2 12:06:04 2016

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
Current user changed to OUTLN
. . exporting table                            OL$          1 rows exported
. . exporting table                       OL$HINTS          5 rows exported
. . exporting table                       OL$NODES          1 rows exported
Export terminated successfully without warnings.

将导出的outline数据文件传输到目标数据库服务器上

[oracle@jyrac1 ~]$ scp oracle@10.138.130.28:/home/oracle/myoutln.dmp /home/oracle/
The authenticity of host '10.138.130.28 (10.138.130.28)' can't be established.
RSA key fingerprint is 25:39:c3:5e:d8:b7:fc:5f:54:b5:a1:ed:17:a1:a0:90.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '10.138.130.28' (RSA) to the list of known hosts.
oracle@10.138.130.28's password: 
myoutln.dmp                                                                                                                          100%   16KB  16.0KB/s   00:00

使用imp工具将导出的outline数据导入到目标数据库

[oracle@jyrac1 ~]$ imp jy/jy file=/home/oracle/myoutln.dmp  full=y ignore=y

Import: Release 11.2.0.4.0 - Production on Thu Jun 2 11:35:53 2016

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


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing o

Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
export client uses ZHS16GBK character set (possible charset conversion)
. importing JY's objects into JY
. importing OUTLN's objects into OUTLN
. . importing table                          "OL$"          1 rows imported
. . importing table                     "OL$HINTS"          5 rows imported
. . importing table                     "OL$NODES"          1 rows imported
Import terminated successfully without warnings.

启用stored outline

SQL> alter session set use_stored_outlines=JY;

Session altered.

SQL> select count(*) from t1 where c1>1;

  COUNT(*)
----------
    499999

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  dt7wfh731mssj, child number 1
-------------------------------------
select count(*) from t1 where c1>1

Plan hash value: 1970818898

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |       |       |    12 (100)|          |
|   1 |  SORT AGGREGATE   |        |     1 |     4 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_T1 |  4999 | 19996 |    12   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1">1)

Note
-----
   - outline "OL2" used for this statement

23 rows selected.

对于10g及以版本也可以使用datapump来导出与导入outline数据
导出:
expdp outln/password directory=DUMP_DIR dumpfile=testoutln.dmp tables=OL$,OL$HINTS,OL$NODES query=\”where category=\’JY\’\”
或
expdp jy/jy directory=DUMP_DIR dumpfile=testoutln.dmp query=\”where category=\’JY\’\” schemas=outln

[oracle@jyrac1 ~]$ expdp jy/jy directory=DUMP_DIR dumpfile=testoutln.dmp  query=\"where category=\'JY\'\"  schemas=outln

Export: Release 11.2.0.4.0 - Production on Thu Jun 2 11:53:41 2016

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "JY"."SYS_EXPORT_SCHEMA_01":  jy/******** directory=DUMP_DIR dumpfile=testoutln.dmp query="where category='JY'" schemas=outln 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 256 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/POST_TABLE_ACTION
. . exported "OUTLN"."OL$HINTS"                          13.49 KB       5 rows
. . exported "OUTLN"."OL$"                               10.32 KB       1 rows
. . exported "OUTLN"."OL$NODES"                          7.820 KB       1 rows
Master table "JY"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for JY.SYS_EXPORT_SCHEMA_01 is:
  /home/oracle/testoutln.dmp
Job "JY"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Jun 2 12:05:08 2016 elapsed 0 00:10:38

导入:
impdp outln/password directory=DUMP_DIR dumpfile=testoutln.dmp tables=OL$,OL$HINTS,OL$NODES table_exists_action=truncate
或
impdp jy/jy directory=DUMP_DIR dumpfile=testoutln.dmp remap_schema=outln:outln table_exists_action=truncate

[oracle@jy1 ~]$ impdp jy/jy directory=DUMP_DIR dumpfile=testoutln.dmp   remap_schema=outln:outln table_exists_action=truncate

Import: Release 11.2.0.4.0 - Production on Thu Jun 2 12:09:08 2016

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "JY"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "JY"."SYS_IMPORT_FULL_01":  jy/******** directory=DUMP_DIR dumpfile=testoutln.dmp remap_schema=outln:outln table_exists_action=truncate 
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"OUTLN" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Table "OUTLN"."OL$" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Table "OUTLN"."OL$NODES" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Table "OUTLN"."OL$HINTS" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "OUTLN"."OL$HINTS"                          13.49 KB       5 rows
. . imported "OUTLN"."OL$"                               10.32 KB       1 rows
. . imported "OUTLN"."OL$NODES"                          7.820 KB       1 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
ORA-31684: Object type PROCEDURE:"OUTLN"."ORA$GRANT_SYS_SELECT" already exists
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/POST_TABLE_ACTION
Job "JY"."SYS_IMPORT_FULL_01" completed with 2 error(s) at Thu Jun 2 12:15:44 2016 elapsed 0 00:06:14

ORACLE 11g TSPITR恢复被删除的表空间

Posted on 2016年6月1日2016年11月30日 by YongJing(敬勇)

在Oracle11g中可以使用TSPITR来对被删除的表空间执行表空间按时间点恢复，下面通过一个实例来演示这个功能。
1.创建测试表空间test

SQL> create tablespace test datafile '/u03/app/oracle/oradata/db/test01.dbf' size 50M autoextend off extent management local segment space management auto;

Tablespace created.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u03/app/oracle/oradata/db/system01.dbf
/u03/app/oracle/oradata/db/sysaux01.dbf
/u03/app/oracle/oradata/db/undotbs01.dbf
/u03/app/oracle/oradata/db/users01.dbf
/u03/app/oracle/oradata/db/example01.dbf
/u03/app/oracle/oradata/db/test01.dbf

6 rows selected.

2.创建测试用户test与测试表t1

SQL> create user test identified by "test" default tablespace test temporary tablespace temp;

User created.

SQL> create table test.t1 as select * from dba_objects;

Table created.

SQL> select count(*) from test.t1;

  COUNT(*)
----------
     86031

3.对整个数据库的执行完全备份

RMAN> backup as backupset database plus archivelog;


Starting backup at 2015-06-01 22:05:12
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=5 RECID=1 STAMP=880996327
input archived log thread=1 sequence=6 RECID=2 STAMP=880996438
input archived log thread=1 sequence=7 RECID=3 STAMP=881014383
input archived log thread=1 sequence=8 RECID=4 STAMP=881014612
input archived log thread=1 sequence=9 RECID=5 STAMP=881015165
input archived log thread=1 sequence=10 RECID=13 STAMP=881233508
input archived log thread=1 sequence=11 RECID=14 STAMP=881233508
channel ORA_DISK_1: starting piece 1 at 2015-06-01 22:05:14
channel ORA_DISK_1: finished piece 1 at 2015-06-01 22:05:21
piece handle=/u02/backup/0kq8ea8q_1_1 tag=TAG20150601T220514 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=12 STAMP=881233507
channel ORA_DISK_1: starting piece 1 at 2015-06-01 22:05:21
channel ORA_DISK_1: finished piece 1 at 2015-06-01 22:05:22
piece handle=/u02/backup/0lq8ea91_1_1 tag=TAG20150601T220514 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=12 RECID=15 STAMP=881233508
channel ORA_DISK_1: starting piece 1 at 2015-06-01 22:05:23
channel ORA_DISK_1: finished piece 1 at 2015-06-01 22:05:26
piece handle=/u02/backup/0mq8ea92_1_1 tag=TAG20150601T220514 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=2 RECID=21 STAMP=881233663
input archived log thread=1 sequence=3 RECID=22 STAMP=881233941
input archived log thread=1 sequence=4 RECID=23 STAMP=881234587
input archived log thread=1 sequence=5 RECID=24 STAMP=881235045
input archived log thread=1 sequence=6 RECID=25 STAMP=881235180
input archived log thread=1 sequence=7 RECID=26 STAMP=881272559
channel ORA_DISK_1: starting piece 1 at 2015-06-01 22:05:26
channel ORA_DISK_1: finished piece 1 at 2015-06-01 22:05:27
piece handle=/u02/backup/0nq8ea96_1_1 tag=TAG20150601T220514 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=13 RECID=16 STAMP=881233508
input archived log thread=1 sequence=14 RECID=17 STAMP=881233508
input archived log thread=1 sequence=15 RECID=18 STAMP=881233508
input archived log thread=1 sequence=16 RECID=19 STAMP=881233508
input archived log thread=1 sequence=17 RECID=20 STAMP=881233508
input archived log thread=1 sequence=18 RECID=11 STAMP=881232587
input archived log thread=1 sequence=19 RECID=9 STAMP=881232587
input archived log thread=1 sequence=20 RECID=10 STAMP=881232587
channel ORA_DISK_1: starting piece 1 at 2015-06-01 22:05:27
channel ORA_DISK_1: finished piece 1 at 2015-06-01 22:05:28
piece handle=/u02/backup/0oq8ea97_1_1 tag=TAG20150601T220514 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=8 RECID=27 STAMP=881273112
channel ORA_DISK_1: starting piece 1 at 2015-06-01 22:05:28
channel ORA_DISK_1: finished piece 1 at 2015-06-01 22:05:29
piece handle=/u02/backup/0pq8ea98_1_1 tag=TAG20150601T220514 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2015-06-01 22:05:29

Starting backup at 2015-06-01 22:05:29
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u03/app/oracle/oradata/db/system01.dbf
input datafile file number=00002 name=/u03/app/oracle/oradata/db/sysaux01.dbf
input datafile file number=00003 name=/u03/app/oracle/oradata/db/undotbs01.dbf
input datafile file number=00005 name=/u03/app/oracle/oradata/db/test01.dbf
input datafile file number=00004 name=/u03/app/oracle/oradata/db/users01.dbf
channel ORA_DISK_1: starting piece 1 at 2015-06-01 22:05:31
channel ORA_DISK_1: finished piece 1 at 2015-06-01 22:09:16
piece handle=/u02/backup/0qq8ea9b_1_1 tag=TAG20150601T220529 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:03:45
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 2015-06-01 22:09:20
channel ORA_DISK_1: finished piece 1 at 2015-06-01 22:09:21
piece handle=/u02/backup/0rq8eagd_1_1 tag=TAG20150601T220529 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2015-06-01 22:09:21

Starting backup at 2015-06-01 22:09:21
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=9 RECID=28 STAMP=881273363
channel ORA_DISK_1: starting piece 1 at 2015-06-01 22:09:27
channel ORA_DISK_1: finished piece 1 at 2015-06-01 22:09:30
piece handle=/u02/backup/0sq8eagn_1_1 tag=TAG20150601T220926 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 2015-06-01 22:09:30

4.删除表空间test，并记录删除操作执行之前的系统SCN与时间

SQL> select to_char(scn_to_timestamp(current_scn),'yyyy-mm-dd hh24:mi:ss'),current_scn from v$database;

TO_CHAR(SCN_TO_TIME CURRENT_SCN
------------------- -----------
2015-06-01 22:11:45      751203


SQL> drop tablespace test including contents and datafiles;

Tablespace dropped.

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE

SQL> host ls -lrt /u03/app/oracle/oradata/db/
total 1548000
-rw-r----- 1 oracle11 oinstall  30416896 Jun  1 22:01 temp01.dbf
-rw-r----- 1 oracle11 oinstall  52429312 Jun  1 22:05 redo02.log
-rw-r----- 1 oracle11 oinstall  52429312 Jun  1 22:09 redo03.log
-rw-r----- 1 oracle11 oinstall   5251072 Jun  1 22:14 users01.dbf
-rw-r----- 1 oracle11 oinstall 775954432 Jun  1 22:14 system01.dbf
-rw-r----- 1 oracle11 oinstall  73408512 Jun  1 22:14 undotbs01.dbf
-rw-r----- 1 oracle11 oinstall 545267712 Jun  1 22:14 sysaux01.dbf
-rw-r----- 1 oracle11 oinstall  52429312 Jun  1 22:14 redo01.log
-rw-r----- 1 oracle11 oinstall   9748480 Jun  1 22:14 control01.ctl

从上面的查询可以看到表空间test已经被删除了。

5.现在执行TSPITR将表空间test恢复到被删除之前的时间点

RMAN> recover tablespace "TEST" until time '2015-06-01 22:11:45' auxiliary destination '/u02/auxiliary';

Starting recover at 2015-06-01 22:22:25
starting full resync of recovery catalog
full resync complete
using channel ORA_DISK_1

Creating automatic instance, with SID='jAvb'

initialization parameters used for automatic instance:
db_name=DB
db_unique_name=jAvb_tspitr_DB
compatible=11.2.0.4.0
db_block_size=8192
db_files=200
sga_target=1G
processes=80
db_create_file_dest=/u02/auxiliary
log_archive_dest_1='location=/u02/auxiliary'
#No auxiliary parameter file used


starting up automatic instance DB

Oracle instance started

Total System Global Area    1071333376 bytes

Fixed Size                     1369420 bytes
Variable Size                281021108 bytes
Database Buffers             784334848 bytes
Redo Buffers                   4608000 bytes
Automatic instance created

contents of Memory Script:
{
# set requested point in time
set until  time "2015-06-01 22:11:45";
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
# resync catalog
resync catalog;
}
executing Memory Script

executing command: SET until clause

Starting restore at 2015-06-01 22:22:40
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u02/backup/0rq8eagd_1_1
channel ORA_AUX_DISK_1: piece handle=/u02/backup/0rq8eagd_1_1 tag=TAG20150601T220529
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u02/auxiliary/DB/controlfile/o1_mf_bprtokmc_.ctl
Finished restore at 2015-06-01 22:22:42

sql statement: alter database mount clone database

sql statement: alter system archive log current

sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;

starting full resync of recovery catalog
full resync complete

contents of Memory Script:
{
# set requested point in time
set until  time "2015-06-01 22:11:45";
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile  1 to new;
set newname for clone datafile  3 to new;
set newname for clone datafile  2 to new;
set newname for clone tempfile  1 to new;
set newname for datafile  5 to
 "/u03/app/oracle/oradata/db/test01.dbf";
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  1, 3, 2, 5;
switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /u02/auxiliary/DB/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 2015-06-01 22:22:51
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u02/auxiliary/DB/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u02/auxiliary/DB/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u02/auxiliary/DB/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u03/app/oracle/oradata/db/test01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u02/backup/0qq8ea9b_1_1
channel ORA_AUX_DISK_1: piece handle=/u02/backup/0qq8ea9b_1_1 tag=TAG20150601T220529
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:15
Finished restore at 2015-06-01 22:25:07

datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=881274307 file name=/u02/auxiliary/DB/datafile/o1_mf_system_bprtowv6_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=881274307 file name=/u02/auxiliary/DB/datafile/o1_mf_undotbs1_bprtowxf_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=7 STAMP=881274307 file name=/u02/auxiliary/DB/datafile/o1_mf_sysaux_bprtowxc_.dbf

contents of Memory Script:
{
# set requested point in time
set until  time "2015-06-01 22:11:45";
# online the datafiles restored or switched
sql clone "alter database datafile  1 online";
sql clone "alter database datafile  3 online";
sql clone "alter database datafile  2 online";
sql clone "alter database datafile  5 online";
# recover and open resetlogs
recover clone database tablespace  "TEST", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  1 online

sql statement: alter database datafile  3 online

sql statement: alter database datafile  2 online

sql statement: alter database datafile  5 online

Starting recover at 2015-06-01 22:25:08
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 9 is already on disk as file /u03/archivelog/1_9_881232587.dbf
archived log for thread 1 with sequence 10 is already on disk as file /u03/archivelog/1_10_881232587.dbf
archived log file name=/u03/archivelog/1_9_881232587.dbf thread=1 sequence=9
archived log file name=/u03/archivelog/1_10_881232587.dbf thread=1 sequence=10
media recovery complete, elapsed time: 00:00:16
Finished recover at 2015-06-01 22:25:25

database opened

contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone 'alter tablespace  "TEST" read only';
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u02/auxiliary''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u02/auxiliary''";
}
executing Memory Script

sql statement: alter tablespace  "TEST" read only

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u02/auxiliary''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u02/auxiliary''

Performing export of metadata...
   EXPDP> Starting "SYS"."TSPITR_EXP_jAvb":
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   EXPDP> Master table "SYS"."TSPITR_EXP_jAvb" successfully loaded/unloaded
   EXPDP> ******************************************************************************
   EXPDP> Dump file set for SYS.TSPITR_EXP_jAvb is:
   EXPDP>   /u02/auxiliary/tspitr_jAvb_29236.dmp
   EXPDP> ******************************************************************************
   EXPDP> Datafiles required for transportable tablespace TEST:
   EXPDP>   /u03/app/oracle/oradata/db/test01.dbf
   EXPDP> Job "SYS"."TSPITR_EXP_jAvb" successfully completed at Mon Jun 1 22:27:28 2015 elapsed 0 00:01:12
Export completed


contents of Memory Script:
{
# shutdown clone before import
shutdown clone immediate
}
executing Memory Script

database closed
database dismounted
Oracle instance shut down

Performing import of metadata...
   IMPDP> Master table "SYS"."TSPITR_IMP_jAvb" successfully loaded/unloaded
   IMPDP> Starting "SYS"."TSPITR_IMP_jAvb":
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   IMPDP> Job "SYS"."TSPITR_IMP_jAvb" successfully completed at Mon Jun 1 22:28:23 2015 elapsed 0 00:00:14
Import completed


contents of Memory Script:
{
# make read write and offline the imported tablespaces
sql 'alter tablespace  "TEST" read write';
sql 'alter tablespace  "TEST" offline';
# enable autobackups after TSPITR is finished
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
# resync catalog
resync catalog;
}
executing Memory Script

sql statement: alter tablespace  "TEST" read write

sql statement: alter tablespace  "TEST" offline

sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;

starting full resync of recovery catalog
full resync complete

Removing automatic instance
Automatic instance removed
auxiliary instance file /u02/auxiliary/DB/datafile/o1_mf_temp_bprtv5bo_.tmp deleted
auxiliary instance file /u02/auxiliary/DB/onlinelog/o1_mf_3_bprttwvr_.log deleted
auxiliary instance file /u02/auxiliary/DB/onlinelog/o1_mf_2_bprttt0k_.log deleted
auxiliary instance file /u02/auxiliary/DB/onlinelog/o1_mf_1_bprttp4g_.log deleted
auxiliary instance file /u02/auxiliary/DB/datafile/o1_mf_sysaux_bprtowxc_.dbf deleted
auxiliary instance file /u02/auxiliary/DB/datafile/o1_mf_undotbs1_bprtowxf_.dbf deleted
auxiliary instance file /u02/auxiliary/DB/datafile/o1_mf_system_bprtowv6_.dbf deleted
auxiliary instance file /u02/auxiliary/DB/controlfile/o1_mf_bprtokmc_.ctl deleted
Finished recover at 2015-06-01 22:28:45

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
TEST                           OFFLINE

6 rows selected.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u03/app/oracle/oradata/db/system01.dbf
/u03/app/oracle/oradata/db/sysaux01.dbf
/u03/app/oracle/oradata/db/undotbs01.dbf
/u03/app/oracle/oradata/db/users01.dbf
/u03/app/oracle/oradata/db/test01.dbf

SQL> host ls -lrt /u03/app/oracle/oradata/db/
total 1599328
-rw-r----- 1 oracle11 oinstall  52429312 Jun  1 22:09 redo03.log
-rw-r----- 1 oracle11 oinstall   5251072 Jun  1 22:22 users01.dbf
-rw-r----- 1 oracle11 oinstall  52429312 Jun  1 22:22 redo01.log
-rw-r----- 1 oracle11 oinstall  30416896 Jun  1 22:28 temp01.dbf
-rw-r----- 1 oracle11 oinstall  52436992 Jun  1 22:28 test01.dbf
-rw-r----- 1 oracle11 oinstall 775954432 Jun  1 22:28 system01.dbf
-rw-r----- 1 oracle11 oinstall  73408512 Jun  1 22:28 undotbs01.dbf
-rw-r----- 1 oracle11 oinstall 545267712 Jun  1 22:28 sysaux01.dbf
-rw-r----- 1 oracle11 oinstall  52429312 Jun  1 22:29 redo02.log
-rw-r----- 1 oracle11 oinstall   9814016 Jun  1 22:30 control01.ctl

从上面的查询结果可以看到表空间test已经恢复，但现在test表空间是脱机状态。

6.将表空间test联机，并查询表test.t1来验证恢复是否真正成功.

RMAN> sql 'alter tablespace test online';

sql statement: alter tablespace test online
starting full resync of recovery catalog
full resync complete

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
UNDOTBS1                       ONLINE
TEMP                           ONLINE
USERS                          ONLINE
TEST                           ONLINE

6 rows selected.

SQL> select count(*) from test.t1;

  COUNT(*)
----------
     86031

从上面的查询结果可以看到被删除的表空间test已经恢复到被删除之前的时间点，表test.t1的记录已经恢复回来。

dbms_outln.create_outline在10.2.0.5中创建outline所包含的执行计划并不正确

Posted on 2016年6月1日2016年6月1日 by YongJing(敬勇)

Oracle 10g中想要固定执行计划只能使用outline,sql profile不能起固定sql执行的效果，但是在Oracle 10.2.0.5中使用dbms_outln.create_outline通过使用共享池中的游标来创建outline,发现创建的outline与游标中的执行计划并不一致，而在oracle 10.2.0.4与oracle 11.2.0.4中是通过游标来创建的outline与cursor的实际执行计划是一致的。这应该是BUG.

Oracle 10.2.0.5中的测试如下:
定义绑定变量

SQL> var x varchar2(20)
SQL> exec :x:='Kabab';

PL/SQL procedure successfully completed.

执行查询

SQL> select * from t1 where t_meal=:x;

      T_ID T_MEAL
---------- --------------------
     79999 Kabab

查看实际的执行计划

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  7runhd24kgqsf, child number 0
-------------------------------------
select * from t1 where t_meal=:x

Plan hash value: 141743202

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

| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |               |       |       |     2 (100)|          |

|   1 |  TABLE ACCESS BY INDEX ROWID| T1            |     1 |    11 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_T1_T_MEAL |     1 |       |     1   (0)| 00:00:01 |

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


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T_MEAL"=:X)


19 rows selected.

可以看到执行计划使用的是索引范围扫描

查询SQL语句的SQL_ID.hash_value,child_number

SQL> select hash_value, child_number, sql_text,sql_id from v$sql where sql_text like 'select * from t1%';

HASH_VALUE CHILD_NUMBER SQL_TEXT                                                                         SQL_ID
---------- ------------ -------------------------------------------------------------------------------- -------------
2301090574            0 select * from t1 where t_meal=:x                                                 7runhd24kgqsf

使用游标来创建outline

SQL> exec dbms_outln.create_outline(2301090574,0);

PL/SQL procedure successfully completed.

SQL> select name,owner,category,used from dba_outlines;

NAME                           OWNER                             CATEGORY                       USED
------------------------------ ------------------------------    -----------------------------  ------
SYS_OUTLINE_16060116155127504  JY                                DEFAULT                        UNUSED

查询outline的hint信息，可以看到没有index hint而是full这说明是全表扫描

SQL> select * from dba_outline_hints where owner='JY' and name='SYS_OUTLINE_16060116155127504';

NAME                           OWNER                                NODE   STAGE      JOIN_POS   HINT
------------------------------ ------------------------------ ----------   ---------- ---------- -------------------------------------------------------------------------------

SYS_OUTLINE_16060116155127504  JY                                      1         1          1    FULL(@"SEL$1" "T1"@"SEL$1")
SYS_OUTLINE_16060116155127504  JY                                      1         1          0    OUTLINE_LEAF(@"SEL$1")
SYS_OUTLINE_16060116155127504  JY                                      1         1          0    ALL_ROWS
SYS_OUTLINE_16060116155127504  JY                                      1         1          0    OPTIMIZER_FEATURES_ENABLE('10.2.0.5')
SYS_OUTLINE_16060116155127504  JY                                      1         1          0    IGNORE_OPTIM_EMBEDDED_HINTS

启用outline,并重新执行sql语句

SQL> alter session set use_stored_outlines=true;

Session altered.
SQL> select * from t1 where t_meal=:x
  2  ;

      T_ID T_MEAL
---------- --------------------
     79999 Kabab

查询使用了outline的执行计划发现却是全表扫描，并不是游标中的索引范围扫描

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  0c2v6n4c0sj6v, child number 0
-------------------------------------
select * from t1 where t_meal=:x

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |    47 (100)|          |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    11 |    47   (5)| 00:00:01 |

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

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("T_MEAL"=:X)

Note
-----
   - outline "SYS_OUTLINE_16060116155127504" used for this statement


22 rows selected.

在oracle 10.2.0.5中如果是使用自动创建outline，那么outline所包含的执行计划与游标中的执行计划是一致的，测试如下:
在会话级启用自动为查询语句创建outline

SQL> alter session set create_stored_outlines=true;

Session altered.

执行查询

SQL> select * from t1 where t_meal=:x;

      T_ID T_MEAL
---------- --------------------
     79999 Kabab

禁用自动创建outline

SQL> alter session set create_stored_outlines=false;

Session altered.

查看语句的执行计划，使用了索引范围扫描

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  7runhd24kgqsf, child number 1
-------------------------------------
select * from t1 where t_meal=:x

Plan hash value: 141743202

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

| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |               |       |       |     2 (100)|          |

|   1 |  TABLE ACCESS BY INDEX ROWID| T1            |     1 |    11 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_T1_T_MEAL |     1 |       |     1   (0)| 00:00:01 |

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


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T_MEAL"=:X)


19 rows selected.

查询自动创建outline是否成功

SQL> select name,owner,category,used from dba_outlines;

NAME                           OWNER                          CATEGORY                       USED
------------------------------ ------------------------------ ------------------------------ ------
SYS_OUTLINE_16060117095505105  JY                             DEFAULT                        UNUSED

SQL> select * from dba_outline_hints where owner='JY' and name='SYS_OUTLINE_16060117095505105';

NAME                           OWNER                                NODE      STAGE   JOIN_POS HINT
------------------------------ ------------------------------ ---------- ---------- ---------- --------------------------------------------------------------------------------
SYS_OUTLINE_16060117095505105  JY                                      1          1          1 INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("T1"."T_MEAL"))
SYS_OUTLINE_16060117095505105  JY                                      1          1          0 OUTLINE_LEAF(@"SEL$1")
SYS_OUTLINE_16060117095505105  JY                                      1          1          0 ALL_ROWS
SYS_OUTLINE_16060117095505105  JY                                      1          1          0 OPTIMIZER_FEATURES_ENABLE('10.2.0.5')
SYS_OUTLINE_16060117095505105  JY                                      1          1          0 IGNORE_OPTIM_EMBEDDED_HINTS

启用outline

SQL> alter session set use_stored_outlines=true;

Session altered.

重新执行查询

SQL> select * from t1 where t_meal=:x;

      T_ID T_MEAL
---------- --------------------
     79999 Kabab

查看使用outline的执行计划使用了索引范围扫描

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  0c2v6n4c0sj6v, child number 0
-------------------------------------
select * from t1 where t_meal=:x

Plan hash value: 141743202

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

| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |               |       |       |     2 (100)|          |

|   1 |  TABLE ACCESS BY INDEX ROWID| T1            |     1 |    11 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_T1_T_MEAL |     1 |       |     1   (0)| 00:00:01 |

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


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T_MEAL"=:X)


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Note
-----
   - outline "SYS_OUTLINE_16060117095505105" used for this statement


23 rows selected.

Oracle 10.2.0.4中的测试如下:
定义绑定变量

SQL> var x varchar2(20)
SQL> exec :x:='1';

PL/SQL procedure successfully completed.

执行查询

SQL> select * from t1 where c1=:x;

C1
--------------------
1

查看语句的执行计划

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  0m63029gwn10n, child number 0
-------------------------------------
select * from t1 where c1=:x

Plan hash value: 1629967410

---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |       |       |     1 (100)|          |
|*  1 |  INDEX RANGE SCAN| IDX_T1 |     1 |     4 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("C1"=:X)


18 rows selected.

查询语句的hash_value与sql_id

SQL> select hash_value, child_number, sql_text,sql_id from v$sql where sql_text like 'select * from t1%';

HASH_VALUE CHILD_NUMBER  SQL_TEXT                                                                          SQL_ID
---------- ------------  --------------------------------------------------------------------------------  -------------
1607074836            0  select * from t1 where c1=:x                                                      0m63029gwn10n

使用游标来创建outline

SQL> exec dbms_outln.create_outline(1607074836,0);

PL/SQL procedure successfully completed.

查看outline是否创建成功

SQL> select name,owner,category,used from dba_outlines;

NAME                           OWNER                          CATEGORY                       USED
------------------------------ ------------------------------ ------------------------------ ------
SYS_OUTLINE_16060115381869401  INSUR_CHANGDE                  DEFAULT                        UNUSED

查询outline的hint可以看到有index hint，这说明使用了索引

SQL> select * from dba_outline_hints where name='SYS_OUTLINE_16060115381869401';

NAME                           OWNER                                NODE  STAGE      JOIN_POS   HINT
------------------------------ ------------------------------ ----------  ---------- ---------- --------------------------------------------------------------------------------
SYS_OUTLINE_16060115381869401  INSUR_CHANGDE                           1         1          1   INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."C1"))

SYS_OUTLINE_16060115381869401  INSUR_CHANGDE                           1         1          0   OUTLINE_LEAF(@"SEL$1")

SYS_OUTLINE_16060115381869401  INSUR_CHANGDE                           1         1          0   ALL_ROWS

SYS_OUTLINE_16060115381869401  INSUR_CHANGDE                           1         1          0   OPT_PARAM('optimizer_index_caching' 90)

SYS_OUTLINE_16060115381869401  INSUR_CHANGDE                           1         1          0   OPT_PARAM('optimizer_index_cost_adj' 20)

SYS_OUTLINE_16060115381869401  INSUR_CHANGDE                           1         1          0   OPTIMIZER_FEATURES_ENABLE('10.2.0.4')

SYS_OUTLINE_16060115381869401  INSUR_CHANGDE                           1         1          0   IGNORE_OPTIM_EMBEDDED_HINTS


7 rows selected.

启用outline并重新执行sql语句

SQL> alter session set use_stored_outlines=true;

Session altered.

SQL> select * from t1 where c1=:x;

C1
--------------------
1

查询使用outline后的执行计划，确实是使用的索引范围扫描与游标中的执行计划一致

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  0m63029gwn10n, child number 1
-------------------------------------
select * from t1 where c1=:x

Plan hash value: 1629967410

---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |       |       |     1 (100)|          |
|*  1 |  INDEX RANGE SCAN| IDX_T1 |     1 |     4 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("C1"=:X)

Note
-----
   - outline "SYS_OUTLINE_16060115381869401" used for this statement


22 rows selected.

Oracle 11.2.0.4的测试如下:
定义绑定变量

SQL> var x number
SQL> exec :x:=1

PL/SQL procedure successfully completed.

执行查询

SQL> select * from t1 where c1=:x;

        C1
----------
         1

查看执行计划

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  0m63029gwn10n, child number 0
-------------------------------------
select * from t1 where c1=:x

Plan hash value: 1369807930

---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |       |       |     1 (100)|          |
|*  1 |  INDEX RANGE SCAN| IDX_T1 |     1 |     4 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("C1"=:X)


18 rows selected.

查询语句的hash_value,child_number,sql_id

SQL> select hash_value, child_number, sql_text,sql_id from v$sql where sql_text like 'select * from t1%';

HASH_VALUE CHILD_NUMBER SQL_TEXT                                                                         SQL_ID
---------- ------------ -------------------------------------------------------------------------------- -------------
1607074836            0 select * from t1 where c1=:x                                                     0m63029gwn10n

使用游标来创建outline

SQL> exec dbms_outln.create_outline(1607074836,0);

PL/SQL procedure successfully completed.

查看outline是否创建成功

SQL> select name,owner,category,used from dba_outlines;

NAME                           OWNER                          CATEGORY                       USED
------------------------------ ------------------------------ ------------------------------ ------
SYS_OUTLINE_16060115345355101  JY                             DEFAULT                        UNUSED

查询outline的hint信息可以看到index hint信息这说明使用了索引

SQL> select * from dba_outline_hints where owner='JY' and name='SYS_OUTLINE_16060115345355101';

NAME                           OWNER                                NODE   STAGE      JOIN_POS   HINT
------------------------------ ------------------------------ ----------   ---------- ---------- --------------------------------------------------------------------------------
SYS_OUTLINE_16060115345355101  JY                                      1         1          1    INDEX(@"SEL$1" "T1"@"SEL$1" ("T1"."C1"))

SYS_OUTLINE_16060115345355101  JY                                      1         1          0    OUTLINE_LEAF(@"SEL$1")

SYS_OUTLINE_16060115345355101  JY                                      1         1          0    ALL_ROWS

SYS_OUTLINE_16060115345355101  JY                                      1         1          0    DB_VERSION('11.2.0.4')

SYS_OUTLINE_16060115345355101  JY                                      1         1          0    OPTIMIZER_FEATURES_ENABLE('11.2.0.4')

SYS_OUTLINE_16060115345355101  JY                                      1         1          0    IGNORE_OPTIM_EMBEDDED_HINTS


6 rows selected.

启用outline并重新执行SQL语句

SQL> alter session set use_stored_outlines=true;

Session altered.

SQL> select * from t1 where c1=:x;

        C1
----------
         1

查询使用outline后的执行计划使用了索引，与游标中的执行计划一致

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  0m63029gwn10n, child number 1
-------------------------------------
select * from t1 where c1=:x

Plan hash value: 1369807930

---------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |        |       |       |     1 (100)|          |
|*  1 |  INDEX RANGE SCAN| IDX_T1 |     1 |     4 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("C1"=:X)

Note
-----
   - outline "SYS_OUTLINE_16060115345355101" used for this statement


22 rows selected.

从测试结果来看，要在10.2.0.5中创建outline固定执行计划不要使用dbms_outln.create_outline这种方法，因为这种方法生成了outline所包含的执行计划并不正确。

Query Using Bind Variables Suddenly Starts to Perform Slowly

Posted on 2016年5月31日 by YongJing(敬勇)

有时SQL语句在没有明显原因的情况下，突然变得执行缓慢,SQL语句所引用的对象的统计信息，对象数据，SQL都没有改变。

SQL语句的游标由于某些原因生成了新的执行计划。出现这种情况的原因至少为以下一种:
.数据库重新启动
.由于任何原因游标没有被使用，并且因为LRU算法被aged out
.SQL语句所引用的任何对象的统计信息发生改变
.SQL语句所引用的任何对象的结构发生改变
.对SQL语句所引用的对象授予/回收权限

如果游标被pinned(正被使用)，那么清空共享池也不会将该游标删除。

可能造成SQL语句突然执行缓慢的原因一个可能主要的原因是使用了oracle 9i所引入的绑定变量窥视。使用这个功能，查询优化器将会在第一次调用一个游标时窥视用户定义绑定变量的值。这个功能能让优化器像使用literal值一样对待绑定变量的值来计算任何where子句条件的选择率。在后续调用游标时，不会执行绑定变量窥视，并且游标是共享,基于标准游标共享的标准，即使后续调用游标时使用不同的绑定变量值，还是会使用共享标。换句话说，这个游标被解析一次被多次使用。如果在SQL第一次被解析时所使用的绑定变量值不能代表大多数数据时，那么对于不同的绑定变量来说第一次生成的执行计划就很有可能不是最优的。

在SQL语句中使用绑定变量，并且游标是共享的，对不同的调用都将使用相同的执行计划。如果不同的调用使用不同的执行计划更有效，那么在SQL语句中使用绑定变量就不合适。

另外，绑定变量窥视已经被熟知在RAC的不同节点中会导致生成不同的执行计划，这是因为每个节点都有属于它自己的共享池。尽管相同的SQL，数据与统计信息，在SQL语句使用不同的绑定变量第一次在每个节点被解析时，对于每个节点将会选择不同的执行计划。

隐含参数_optim_peek_user_binds用来控制绑定变量窥视这个功能是否启用，它的缺省值是true。虽然禁用绑定变量窥视可以得到一个稳定的执行计划，但必须要了解对于所有绑定变量使用一种稳定的执行计划是否是最佳的选择。比如一个表有10000行记录并且col1列存在索引。
SELECT
FROM tablex
WHERE col1 BETWEEN :bind1 AND :bind2;

如果执行这个SQL，使用值123与124来从10000行记录中检索2行记录，那么使用索引绝对是最明智的选择。然而，如果使用绑定变量值123与9999来执行相同的SQL，那么查询将要检索表中绝大多数记录并且选择全表扫描应该是最合适的，但优化器不能意识到这一点，不会因此改变执行计划。

解决方法
要解决因绑定变量窥视而造成的SQL变慢的问题最好的方法是使用Oracle 11g引入的自适就游标共享，这个技术允许优化器对于不同的绑定变量值使用不同的执行计划。如果自适应游标不可以使用，一种可能的解决方法是修改应用程序并且对于上面的语句有两个单独的
模块/部分来表示，但这种改变(使用hint来修改)将会生成你预期的执行计划。因此编码绑定变量或可以使用cursor_sharing设置为similar或force是适合的，但必须要认识到绑定变量窥视基于在硬解析时提供给优化器的一组绑定变量值而生成非你预期的执行计划。使用hint与编码应用程序来允许使用合适版本的SQL或使用literal值来获得更优的执行计划。

为了禁用绑定变量窥视将_optim_peek_user_binds在spfile/pfile或会话中设置为false:
alter session set “_OPTIM_PEEK_USER_BINDS”=FALSE;

下面通过一个例子来理解绑定变量窥视

SQL> create table t1(t_id number(20),t_meal varchar2(20));

Table created.

SQL>begin
    for i in 1 .. 79998 loop
     insert into t1 values(i,'Mansaf');
    end loop;
     insert into t1 values(79999,'Kabab');
     insert into t1 values(80000,'Pasta');
     commit;
    end;
    /
PL/SQL procedure successfully completed.


SQL> create index idx_t1 on t1(t_meal);

Index created.

SQL> exec dbms_stats.gather_table_stats

(ownname=>null,tabname=>'T1',estimate_percent=>100,method_opt=>'for all indexed columns 

size auto',cascade=>true);

PL/SQL procedure successfully completed.

SQL> show parameter cursor_sharing

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      EXACT

cursor_sharing参数为exact，这意味着只要查询的literal值不同游标就不能共享

SQL> select count(*) from t1 where t_meal='Mansaf';

  COUNT(*)
----------
     79998

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  41wjq1qnk92wd, child number 0
-------------------------------------
An uncaught error happened in prepare_sql_statement : ORA-01403: no data found

Plan hash value: 2101382132

--------------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |       |       |    44 (100)|          |
|   1 |  SORT AGGREGATE       |        |     1 |     7 |            |          |
|*  2 |   INDEX FAST FULL SCAN| IDX_T1 | 79998 |   546K|    44   (5)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("T_MEAL"='Mansaf')


19 rows selected.

从上面的执计划可以看到执行计划使用了index fast full scan。

下面使用绑定变量来执行

SQL> var x varchar2(20)
SQL> exec :x:= 'Mansaf'

PL/SQL procedure successfully completed.

SQL> select count(*) from t1 where t_meal=:x;

  COUNT(*)
----------
     79998

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  42s7s1vzsrbwk, child number 0
-------------------------------------
select count(*) from t1 where t_meal=:x

Plan hash value: 2101382132

--------------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |       |       |    44 (100)|          |
|   1 |  SORT AGGREGATE       |        |     1 |     7 |            |          |
|*  2 |   INDEX FAST FULL SCAN| IDX_T1 | 79998 |   546K|    44   (5)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("T_MEAL"=:X)


19 rows selected.

当使用绑定变量后语义相同的语句的SQL_ID发生了改变。执行计划是使用index fast full scan

SQL> exec :x:='Pasta';

PL/SQL procedure successfully completed.

SQL> select count(*) from t1 where t_meal=:x;

  COUNT(*)
----------
         1

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  42s7s1vzsrbwk, child number 0
-------------------------------------
An uncaught error happened in prepare_sql_statement : ORA-01403: no data found

Plan hash value: 2101382132

--------------------------------------------------------------------------------
| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE       |        |     1 |     7 |            |          |
|*  2 |   INDEX FAST FULL SCAN| IDX_T1 |     1 |     7 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T_MEAL"=:X)


19 rows selected.

当只检索一条记录时，因为查询语句相同，游标共享并且即使绑定变量值不同还是使用了第一次生成的执行计划，这时该执行计划对于这个绑定变量值来说不是最佳执行计划。

如果想要使游标失效并重新生成执行计划，有以下几种方法使游标失效。
1.执行alter system flush shared_pool;
2.删除或修改游标所引用对象的统计信息
3.对游标所引用的对象授予或回收相关权限
4.修改游标所引用对象
5.重启实例
6.使用dummy hint来改变语句的文本

这里使用dummy hint来改变语句的文本

SQL> select /*+ Hard parse me please */ count(*) from t1 where t_meal=:x;

  COUNT(*)
----------
         1

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  crggg37d7jmrg, child number 0
-------------------------------------
select /*+ Hard parse me please */ count(*) from t1 where t_meal=:x

Plan hash value: 1970818898

----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE   |        |     1 |     7 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_T1 |     1 |     7 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T_MEAL"=:X)


19 rows selected.

从执行计划可以看到SQL_ID也发生了改变,确实重新生成了执行计划

使用 DBMS_SQLDIAG诊断各种查询问题

Posted on 2016年5月30日 by YongJing(敬勇)

这篇文章主要介绍通过dbms_sqldiag来解决与SQL相关的各种问题。dbms_sqldiag是与标准版本数据库软件一起发布，使用它并不需要额外的许可。
dbms_sqldiag可以用于以下问题类型的诊断:
.problem_type_performance 怀疑是性能问题
.problem_type_wrong_results 怀疑查询返回了不一致的结果
.problem_type_compilation_error 在编译时的错误
.problem_type_execution_error 在执行时的错误

诊断problem_type_performance
执行查询并将该语句来作为诊断的SQL语句

SQL> set timing on
SQL> set autotrace traceonly
SQL> select * from t1 where c1=500000;

Elapsed: 00:00:03.43

Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     3 |     5   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |     3 |     5   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_NUMBER("C1")=500000)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1646  consistent gets
          0  physical reads
          0  redo size
        420  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


SQL> select sql_text,sql_id from v$sqlarea where sql_text like 'select * from t1 where c1=500000';
 
SQL_TEXT                                                                         SQL_ID
-------------------------------------------------------------------------------- -------------
select * from t1 where c1=500000                                                 456naq6s2fcpq

创建诊断任务

SQL> set echo on
SQL> set linesize 132
SQL> set pagesize 999
SQL> set long 999999
SQL> set serveroutput on
SQL> declare
  2  v_sql_diag_task_id varchar2(100);
  3  begin
  4  --
  5  -- create diagnostic task
  6  --
  7  v_sql_diag_task_id := dbms_sqldiag.create_diagnosis_task (
  8  sql_id=>'456naq6s2fcpq',
  9  problem_type => dbms_sqldiag.problem_type_performance,
 10  time_limit => 900,
 11  task_name => 'problem_type_performance_task' );
 12  --
 13  -- setup parameters for the task to give verbose output
 14  --
 15  dbms_sqltune.set_tuning_task_parameter(v_sql_diag_task_id,'_sqldiag_finding_mode',dbms_sqldiag.sqldiag_findings_filter_plans);
 16  end;
 17  /

PL/SQL procedure successfully completed.

有时sql_id因为一些原因可能在v$sql视图中找不到，因此在这时就需要使用sql_text来代替sql_id,用户想要执行诊断任务必须至少有advisor权限。task_name作为唯一键使用并且在相同用户使用相同任务名之前必须要删除。

检查任务是否创建成功

SQL> select distinct owner, task_name, advisor_name
from dba_advisor_tasks where advisor_name ='SQL Repair Advisor' and task_name like '%perf%'order by 1;  

OWNER                          TASK_NAME                      ADVISOR_NAME
------------------------------ ------------------------------ ------------------------------
SYS                            problem_type_performance_task  SQL Repair Advisor

执行论断任务

SQL> exec dbms_sqldiag.execute_diagnosis_task (task_name => 'problem_type_performance_task' );

PL/SQL procedure successfully completed.

生成报告

SQL> select dbms_sqldiag.report_diagnosis_task ('problem_type_performance_task' ) as recommendations from dual;

RECOMMENDATIONS
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : problem_type_performance_task
Tuning Task Owner  : SYS
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 900
Completion Status  : COMPLETED
Started at         : 05/30/2016 10:20:17
Completed at       : 05/30/2016 10:20:22

-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID     : 456naq6s2fcpq
SQL Text   : select * from t1 where c1=500000

-------------------------------------------------------------------------------
No SQL patch was found to resolve the problem.

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

这上面的这个例子中，没有得到任何patch建议。然而，如果得到了patch，可以执行以下命令来接受patch

begin
 dbms_sqldiag.accept_sql_patch(
   task_name =>'problem_type_performance_task', 
   task_owner => 'sys', 
   replace => true);
end;

验证SQL Patch是否启用

SQL> SELECT name, status FROM dba_sql_patches WHERE name LIKE '%SYS%';
 
NAME                           STATUS
------------------------------ --------
SYS_SQLPTCH_                   ENABLED

删除任务

SQL> exec dbms_sqldiag.drop_diagnosis_task('problem_type_performance_task');

PL/SQL procedure successfully completed.

SQL> select distinct owner, task_name, advisor_name
  2  from dba_advisor_tasks where advisor_name ='SQL Repair Advisor' and task_name like '%perf%'order by 1;
 
OWNER                          TASK_NAME                      ADVISOR_NAME
------------------------------ ------------------------------ ------------------------------

删除SQL Patch

--- find the name of the sql patch
---
select name, status from dba_sql_patches where name like '%sys%';


---drop the sql patch.
---replace following patch name with actual name of the sql patch
--- from previous query output.
exec  dbms_sqldiag.drop_sql_patch (name=> 'sys_sqlptch_');
--- verify that the sql patch has been dropped.

select name, status from dba_sql_patches where name like '%sys%';

诊断problem_type_wrong_results
创建两个测试表

SQL> create table a_test
  2  (
  3  id number not null,
  4  clss number not null
  5  );

Table created.

SQL> create table as_test
  2  (
  3  as_id number
  4  );

Table created.

SQL> insert into a_test values(11,5);

1 row created.

SQL> insert into a_test values(1,5);

1 row created.

SQL> insert into as_test values(11);

1 row created.

SQL> commit;

Commit complete.

错误结果–返回0行记录

SQL> select 'working' as is_working,id
from   a_test a,
  2    3         as_test asi
  4  where  a.id=asi.as_id(+)
  5  and    a.clss in (1,3,4,5)
  6  and    a.clss = '5';

IS_WORK         ID
------- ----------
                                                                                               
SQL> select sql_id, sql_text from v$sql where sql_text like 'select%as is_working%';
 
SQL_ID        SQL_TEXT
------------- --------------------------------------------------------------------------------
9a15z3d14krcm select 'working' as is_working,id from   a_test a,        as_test asi where  a.i

正确结果–返回2行记录

SQL> select 'working' as is_working,id
from   a_test a,
  2    3         as_test asi
  4  where  a.id=asi.as_id(+)
  5  and    a.clss in (1,3,4,5)
  6  and    a.clss = '5';

IS_WORK         ID
------- ----------
working         11
working          1

SQL> set echo on lines 132 pages 999 long 20000 serveroutput on;
SQL> declare
  2  l_sql_diag_task_id  varchar2(100); 
  3         
  4  begin
  5  --
  6  -- create diagnostic task
  7  --
  8      l_sql_diag_task_id :=  dbms_sqldiag.create_diagnosis_task (
  9        sql_id => '9a15z3d14krcm',
 10        problem_type => dbms_sqldiag.problem_type_wrong_results, 
 11        task_name => 'test_wr_diagnostic_task' );
 12   
 13  --
 14  -- setup parameters for the task to give verbose output
 15  --
 16      dbms_sqltune.set_tuning_task_parameter(
 17        l_sql_diag_task_id,
 18        '_sqldiag_finding_mode',
 19        dbms_sqldiag.sqldiag_findings_filter_plans);
 20  end;
 21  /

PL/SQL procedure successfully completed.

SQL> exec dbms_sqldiag.execute_diagnosis_task ( task_name  => 'test_wr_diagnostic_task');

PL/SQL procedure successfully completed.


SQL> select dbms_sqldiag.report_diagnosis_task ('test_wr_diagnostic_task') as recommendations  from dual;


RECOMMENDATIONS
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : test_wr_diagnostic_task
Tuning Task Owner  : SYS
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status  : COMPLETED
Started at         : 05/30/2016 10:46:22
Completed at       : 05/30/2016 10:46:24

-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID     : 9a15z3d14krcm
SQL Text   : select 'working' as is_working,id
             from   a_test a,
                    as_test asi
             where  a.id=asi.as_id(+)
             and    a.clss in (1,3,4,5)
             and    a.clss = '5'

...省略...

诊断PROBLEM_TYPE_COMPILATION_ERROR & PROBLEM_TYPE_EXECUTION_ERROR
创建诊断任务

set echo on
set linesize 132
set pagesize 999
set long 999999
set serveroutput on

declare

v_sql_diag_task_id varchar2(100);


begin
---
--- create a diagnostic task. use any name you want under task_name argument.
---
v_sql_diag_task_id := dbms_sqldiag.create_diagnosis_task (
sql_text => 'select distinct min(src.cf_table_group)
from ( select pc.cf_table_group, min(pc.cf_prg_next_run)
from testcase1 pc where nvl(pc.cf_prg_next_run, sysdate) < = sysdate
group by pc.cf_table_group
order by nvl(min(pc.cf_prg_next_run),sysdate) ) src',
problem_type => dbms_sqldiag.problem_type_execution_error,
time_limit => 3600,
task_name => 'error_diagnostic_task' );
--
-- setup parameters for the task to give verbose output
--
dbms_sqltune.set_tuning_task_parameter(v_sql_diag_task_id,'_sqldiag_finding_mode',dbms_sqldiag.sqldiag_findings_filter_plans);


end;

执行诊断任务

exec dbms_sqldiag.execute_diagnosis_task ( task_name => 'error_diagnostic_task' );

生成报告

set long 9999999
select dbms_sqldiag.report_diagnosis_task ('error_diagnostic_task' ) as recommendations from dual;

recommendations
--------------------------------------------------------------------------------
general information section
-------------------------------------------------------------------------------
tuning task name : error_diagnostic_task
tuning task owner : tc2533
workload type : single sql statement
scope : comprehensive
time limit(seconds): 3600
completion status : completed
started at : 10/27/2011 22:35:07
completed at : 10/27/2011 22:35:07

-------------------------------------------------------------------------------
schema name: tc2533
sql id : 4k1tdq940wvpk
sql text : select distinct min(src.cf_table_group)
from ( select pc.cf_table_group, min(pc.cf_prg_next_run)
from testcase1 pc where nvl(pc.cf_prg_next_run, sysdate) < =
sysdate
group by pc.cf_table_group
order by nvl(min(pc.cf_prg_next_run),sysdate) ) src

-------------------------------------------------------------------------------
findings section (1 finding)
-------------------------------------------------------------------------------

1- sql patch finding (see explain plans section below)
------------------------------------------------------
a potentially better execution plan was found for this statement.

recommendation
--------------
- consider accepting the recommended sql patch.
execute dbms_sqldiag.accept_sql_patch(task_name =>
'error_diagnostic_task', task_owner => 'tc2533', replace => true);

rationale
---------
recommended plan with hash value 3673393522 has number of rows 1, check
sum 2342552567, execution time 0 and 6 buffer gets

接受建议

execute dbms_sqldiag.accept_sql_patch(task_name =>'error_diagnostic_task', task_owner => 'tc2533', replace => true);

删除诊断任务

exec dbms_sqldiag.drop_diagnosis_task ( task_name => 'error_diagnostic_task' );

Oracle Column Group Statistics

Posted on 2016年5月30日 by YongJing(敬勇)

基数是CBO通过行资源或行资源组合评估出来的操作将产生的行数。在有些情况下，结果集的基数可能被评估错误。最常见的是使用复杂谓词且统计信息不能精确反映出谓词相关联的数据。例如:select ename from emp where sal >= 25000 and job = ‘PRESIDENT’;
在这个例子中，在sal与job列中的数据存在一种隐藏的关联。只有董事长才会挣的比$25,000多。没有其它的员工满足sal>=25000。优化器没有办法检查这种关联且会将这两个列单独对待。这将会对谓词产生一种不理解的选择率以及不精确的基数评估。

在Oracle 11g及以上版本通过对一组列创建扩展统计信息来解决这种问题。

SQL> create table t1(t_name varchar2(20) not null,t_country varchar2(20) not null,t_town varchar2(20) not null);

Table created.

SQL> desc t1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 T_NAME                                    NOT NULL VARCHAR2(20)
 T_COUNTRY                                 NOT NULL VARCHAR2(20)
 T_TOWN                                    NOT NULL VARCHAR2(20)


SQL> begin
  2   for i in 1..50 loop
  3       insert into t1 values('JY','CHINA','QIHE');  
  4   end loop;
  5   for r in 51 .. 400 loop
  6     insert into t1 values('A'||r,'USA','NewYork');  
  7   end loop;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

收集表t1的统计信息

SQL> exec dbms_stats.gather_table_stats(ownname=>'JY',tabname=>'T1',estimate_percent=>100,method_opt=>'for all columns sizeauto',cascade=>true);

PL/SQL procedure successfully completed.

查询t_name=’JY’且t_country=’CHINA’的记录为50

SQL> select count(*) from t1 where t_name='JY' and t_country='CHINA';

  COUNT(*)
----------
        50

执行查询发现优化器评估的基数为7,与50相差很远

SQL> select * from t1 where t_name='JY' and t_country='CHINA';

50 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     7 |   119 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     7 |   119 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

创建组列

SQL> declare
  2  cg_name varchar2(30);
  3  begin
  4   cg_name :=  sys.dbms_stats.create_extended_stats(null,'t1', '(t_name,t_country)');
  5  end;
  6  /  

PL/SQL procedure successfully completed.

对创建的组列收集统计信息

SQL> exec dbms_stats.gather_table_stats(null,'t1',estimate_percent=>100,method_opt=>'for columns(t_name,t_country) size skewonly');

PL/SQL procedure successfully completed.


SQL> SELECT extension_name, extension FROM user_stat_extensions WHERE table_name='T1';


EXTENSION_NAME                 EXTENSION
------------------------------ --------------------------------------------------------------------------------
SYS_STUVJ3HB84$ZIMSQJE_3AMWZ_9 ("T_NAME","T_COUNTRY")


SQL> SELECT e.extension col_group, t.num_distinct, t.histogram
  2  FROM user_stat_extensions e, user_tab_col_statistics t
  3  WHERE e.extension_name=t.column_name
  4  AND e.table_name = t.table_name
  5  AND t.table_name='T1';

COL_GROUP                                                                        NUM_DISTINCT HISTOGRAM
-------------------------------------------------------------------------------- ------------ ---------------
("T_NAME","T_COUNTRY")                                                                    351 HEIGHT BALANCED

创建组列收集扩展统计信息后，其评估基数为47与实际的50相差很小，对于估算已经是很准确了

SQL> select * from t1 where t_name='JY' and t_country='CHINA';

50 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    47 |   846 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |    47 |   846 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("T_COUNTRY"='CHINA' AND "T_NAME"='JY')


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
         12  consistent gets
          0  physical reads
          0  redo size
       1224  bytes sent via SQL*Net to client
        452  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         50  rows processed

对于列之间存在关联关系的列创建Column Group Statistics可以让优化器进行更精确的基数评估从而选择最优执行计划来提高性能

Automatic SQL Tuning and SQL Profiles

Posted on 2016年5月30日 by YongJing(敬勇)

SQL Profiles是在Oracle 10g中就引入的，并且通过dbms_sqltune包或EM来进行管理是自动SQL调整进程的一部分。

自动SQL调整
查询优化器会有时会因为缺少信息而对语句的一个属性产生不精确的评估，进而导致低效的执行计划传统上来说，用户不得不通过手动增加hint到代码中来修改这个剖从而指导优化器产生正确的决定。对于第三方应用程序，改变应用代码是不现实的。

自动SQL调整使用SQL Profile来处理这个问题。自动调整优化器将会为SQL语句创建一个SQL Profile，它是由语句相关的辅助统计信息组成。查询优化器在正常模式下对基数，选择率与成本的评估有时会有严重的差异而导致生成低效的执行计划。SQL Profile通过收集额外的信息，比如使用抽样与特定的执行技术来调整评估可以用来解决这个问题。

在自动SQL调整时，优化器也会使用SQL语句的历史执行信息来合理设置优化器参数，比如改变optimizer_mode，将其参数从all_rows改成first_rows。

分析报告的输出建议接受SQL Profile。一旦接受SQL Profile它将会永久存储在数据字典中。一个SQL Profile被指定到一个特定的查询。如果接受，优化器在正常模式下使用SQL Profile中的信息与常规的数据库统计信息来对SQL进行解析。SQL Profile所提供的额外信息可以用来生成更好的执行计划。

SQL Profile
SQL Profile是存储在数据字典中的信息集合能让查询优化器为SQL语句创建一个最优执行计划。SQL Profile包含了自动SQL调整所发现的低效优化器评估的修正。这种信息能提高优化器对基数与选择率评估精确度，从而导致优化器选择一个更好的执行计划。

SQL Profile不包含单独的执行计划，当选择执行计划时优化器有以下信息源:
1.环境，包含数据库配置，绑定变量值，统计信息，数据集等等
2.SQL Profile所提供了附加统计信息

重要的是SQL Profile不会冻结一个SQL语句的执行计划，这一点与stored outlines不一样。当表记录增加或增加与删除索引时，使用相同的SQL Profile执行计划也会发生改变。当数据分布或相关语句的访问路径发生改变，SQL Profile中存储的信息仍然与SQL语关联。然而，随着时间的推移，SQL Profile的内容将会过时并且不得不重新生成。可以再次执行自动SQL调整来重新生成SQL Profile。

如何控制SQL Profile的使用范围
SQL Profile的使用范围可以由CATEGORY属性来进行控制。这个属性决定那个用户会话可以应用这个SQL Profile。可以通过查询dba_sql_profiles视图的category列来查看SQL Profile的category属性

SQL> select name,category from dba_sql_profiles;
 
NAME                           CATEGORY
------------------------------ ------------------------------
SYS_SQLPROF_0152b233d518c007   DEFAULT
SYS_SQLPROF_015470e31c248001   DEFAULT
coe_bcyatm4910qb1_725332378    DEFAULT
coe_3yy1wbuvsxm93_1849931106   DEFAULT
SYS_SQLPROF_0152b11b33e6c006   DEFAULT
coe_a69pw2vj989zm_3709683508   DEFAULT
SYS_SQLPROF_0151ed60f3d28000   DEFAULT
coe_6rfqq1bjwcdx9_1360313219   DEFAULT
SYS_SQLPROF_0152b33048a8c009   DEFAULT
coe_36cbabzyq13gy_1849931106   DEFAULT
SYS_SQLPROF_015470e298fd0000   DEFAULT
SYS_SQLPROF_0152b0a82393c003   DEFAULT
SYS_SQLPROF_0152ba15c21e800b   DEFAULT
coe_6rfqq1bjwcdx9_1360313219_1 DEFAULT

缺省情况下，所有SQL Profile都是创建在DEFAULT目录中。这意味着当sqltune_category设置为default时所有的用户会话都能使用这个SQL Profile。

SQL> show parameter sqltune_category
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sqltune_category                     string      DEFAULT

通过修改SQL Profile的category属性，可以决定那个会话将使用所创建的SQL Profile。例如，通过将一个SQL Profile的category属性设置为DEV，那么只有当sqltune_category设置为DEV时这些会话才能使用这个SQL Profile。所有其它的会话将不能访问这个SQL Profile并且SQL语句的执行计划将不会受这个SQL Profile的影响。这种技术能在SQL Profile被其它会话使用之前让你在一个受限的环境下测试SQL Profile。

SQL Profile可以应用的语句类型
.select语句
.update语句
.insert语句(只包含select子句)
.delete语句
.create table语句(只包含as select子句)
.merge语句(update或insert操作)

SQL Profile的管理
SQL Profile可以通过EM或dbms_sqltune来进行管理

为了使用dbms_sqltune来管理SQL Profile，用户必须有create any sql_profile,drop any sql_profile与alter any sql_profile系统权限。

接受SQL Profile
使用dbms_sqltune.accept_sql_profile过程来接受由SQL调整指导所创建的SQL Profile。

DECLARE
 my_sqlprofile_name VARCHAR2(30);
 BEGIN
 my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE ( 
 task_name => 'my_sql_tuning_task',
 name => 'my_sql_profile');
 END;

my_sql_tuning_task是SQL调整任务的名称，可以查询dba_sql_profiles视图来查看SQL Profile的信息

修改SQL Profile
使用dbms_sqltune.alter_sql_profile过程可以用来修改现有SQL Profile的status,name,description与category属性

BEGIN
 DBMS_SQLTUNE.ALTER_SQL_PROFILE(
 name => 'my_sql_profile', 
 attribute_name => 'STATUS', 
 value => 'DISABLED');
 END;
 /

在这个例子中，要修改名为my_sql_profile的SQL Profile，将它的status属性修改为disable这将意味着这个SQL Profile将不能在SQL编译时使用了。

删除SQL Profile
可以使用dbms_sqltune.drop_sql_profile过程来删除SQL Profile

begin
 DBMS_SQLTUNE.DROP_SQL_PROFILE(name => 'my_sql_profile');
 end;
 /

下面介绍使用SQL Profile来优化SQL语句的例子
会话1

SQL> create table test(n number);

Table created.

SQL> declare
  2             begin
  3              for i in 1 .. 10000 loop
  4                  insert into test values(i);
  5                  commit;
  6              end loop;
  7             end;
  8   /

PL/SQL procedure successfully completed.

SQL> create index test_idx on test(n);

Index created.

SQL> exec dbms_stats.gather_table_stats('','TEST');

PL/SQL procedure successfully completed.

SQL> set autotrace on
SQL> select /*+ no_index(test test_idx) */ * from test where n=1;

         N
----------
         1


Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     4 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |     4 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("N"=1)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         23  consistent gets
          0  physical reads
          0  redo size
        415  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

会话2
创建一个SQL自动调整任务并运行报告调整任务并接受建议的SQL Profile

SQL> declare
  2     my_task_name VARCHAR2(30);
  3     my_sqltext CLOB;
  4     begin
  5        my_sqltext := 'select /*+ no_index(test test_idx) */ * from test where n=1';
  6        my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
  7        sql_text => my_sqltext,
  8        user_name => 'SCOTT',
  9        scope => 'COMPREHENSIVE',
 10        time_limit => 60,
 11        task_name => 'my_sql_tuning_task_1',
 12        description => 'Task to tune a query on a specified table');
 13   end;
 14   /

PL/SQL procedure successfully completed.

SQL>  begin
  2   DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task_1');
  3   end;
  4   /

PL/SQL procedure successfully completed.

SQL> set long 10000
SQL> set longchunksize 1000
SQL> set linesize 100
SQL> set heading off
SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task_1') from DUAL;
set heading on
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : my_sql_tuning_task_1
Tuning Task Owner  : SYS
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status  : COMPLETED
Started at         : 05/27/2016 16:58:11
Completed at       : 05/27/2016 16:58:28

-------------------------------------------------------------------------------
Schema Name: SCOTT

SQL ID     : d4wgpc5g0s0vu
SQL Text   : select /*+ no_index(test test_idx) */ * from test where n=1

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  A potentially better execution plan was found for this statement.

  Recommendation (estimated benefit: 90.91%)
  ------------------------------------------

  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name =>
            'my_sql_tuning_task_1', task_owner => 'SYS', replace => TRUE);

  Validation results
  ------------------
  The SQL profile was tested by executing both its plan and the original plan
  and measuring their respective execution statistics. A plan may have been
  only partially executed if the other could be run to completion in less time.

                           Original Plan  With SQL Profile  % Improved
                           -------------  ----------------  ----------
  Completion Status:            COMPLETE          COMPLETE

  Elapsed Time (s):             .003464           .000405       88.3 %
  CPU Time (s):                 .003399           .000299       91.2 %
  User I/O Time (s):                  0                 0
  Buffer Gets:                       22                 2       90.9 %
  Physical Read Requests:             0                 0
  Physical Write Requests:            0                 0
  Physical Read Bytes:                0                 0
  Physical Write Bytes:               0                 0
  Rows Processed:                     1                 1
  Fetches:                            1                 1
  Executions:                         1                 1

  Notes

  -----
  1. Statistics for the original plan were averaged over 10 executions.
  2. Statistics for the SQL profile plan were averaged over 10 executions.

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original With Adjusted Cost
------------------------------
Plan hash value: 1357081020

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

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     4 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |     4 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("N"=1)

2- Using SQL Profile
--------------------

Plan hash value: 2882402178

-----------------------------------------------------------------------------
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     1 |     4 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| TEST_IDX |     1 |     4 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("N"=1)


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

SQL> DECLARE
  2   my_sqlprofile_name VARCHAR2(30);
  3   begin
  4   my_sqlprofile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE (
  5   task_name => 'my_sql_tuning_task_1',
  6   name => 'my_sql_profile',
  7   force_match => true,
  8   replace =>true );
  9   end;
 10  /

PL/SQL procedure successfully completed.

会话1

SQL> set autotrace on
SQL> select /*+ no_index(test test_idx) */ * from test where n=1;

         N
----------
         1


Execution Plan
----------------------------------------------------------
Plan hash value: 2882402178

-----------------------------------------------------------------------------
| Id  | Operation        | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |          |     1 |     4 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| TEST_IDX |     1 |     4 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("N"=1)

Note
-----
   - SQL profile "my_sql_profile" used for this statement


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
         13  consistent gets
          1  physical reads
          0  redo size
        415  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

可以看到虽然我们指定了no_index来让优化器不使用索引test_idx,但由于使用了SQL Profile还是使用索引test_idx,通过SQL Profile改变了SQL语句的执行计划。

磁盘损坏造成RMAN备份文件有坏块的恢复案例

Posted on 2016年5月29日2016年11月30日 by YongJing(敬勇)

朋友客户的数据库由于磁盘损坏，从存储级别抽取出了RMAN的备份文件，但没有抽取出数据文件，联机重做日志，控制文件，参数文件等数据库文件。但抽取出来的备份文件中有数据文件，控制文件，参数文件的备份，归档重做日志备份因为没有磁盘空间没有执行完成。新安装Oracle软件后创建参数文件来使用RMAN备份来还原和恢复数据库。但在还原数据文件时出错，错误如下所示:

RMAN> run
2> {
3> allocate channel t1 device type disk;
4> restore controlfile from 'E:\ORABACK\ORCLBPM_9BQ7K30M_20150522.BAK';
5> alter database mount;
6> release channel t1;
7> }

使用目标数据库控制文件替代恢复目录
分配的通道: t1
通道 t1: SID=572 设备类型=DISK

启动 restore 于 29-5月 -15

通道 t1: 正在还原控制文件
通道 t1: 还原完成, 用时: 00:00:03
输出文件名=D:\ORACLE\ORADATA\ORCLBPM\CONTROL01.CTL
输出文件名=D:\ORACLE\ORADATA\ORCLBPM\CONTROL02.CTL
输出文件名=D:\ORACLE\ORADATA\ORCLBPM\CONTROL03.CTL
完成 restore 于 29-5月 -15

数据库已装载

释放的通道: t1

RMAN> run
2> {
3> allocate channel t1 device type disk;
4> restore database;
5> recover database;
6> release channel t1;
7> }

分配的通道: t1
通道 t1: SID=572 设备类型=DISK

启动 restore 于 29-5月 -15
启动 implicit crosscheck backup 于 29-5月 -15
已交叉检验的 200 对象
完成 implicit crosscheck backup 于 29-5月 -15

启动 implicit crosscheck copy 于 29-5月 -15
完成 implicit crosscheck copy 于 29-5月 -15

搜索恢复区中的所有文件
正在编制文件目录...
没有为文件编制目录


通道 t1: 正在开始还原数据文件备份集
通道 t1: 正在指定从备份集还原的数据文件
通道 t1: 将数据文件 00001 还原到 D:\ORACLE\ORADATA\ORCLBPM\SYSTEM01.DBF
通道 t1: 将数据文件 00002 还原到 D:\ORACLE\ORADATA\ORCLBPM\SYSAUX01.DBF
通道 t1: 将数据文件 00003 还原到 D:\ORACLE\ORADATA\ORCLBPM\UNDOTBS01.DBF
通道 t1: 将数据文件 00004 还原到 D:\ORACLE\ORADATA\ORCLBPM\USERS01.DBF
通道 t1: 将数据文件 00005 还原到 D:\ORACLE\ORADATA\ORCLBPM\HB_SY01.DBF
通道 t1: 将数据文件 00006 还原到 D:\ORACLE\ORADATA\ORCLBPM\HB_SY02.DBF
通道 t1: 将数据文件 00007 还原到 D:\ORACLE\ORADATA\ORCLBPM\HB_SY03.DBF
通道 t1: 将数据文件 00008 还原到 D:\ORACLE\ORADATA\ORCLBPM\BPMSY01.DBF
通道 t1: 将数据文件 00009 还原到 D:\ORACLE\ORADATA\ORCLBPM\HMBPM01.DBF
通道 t1: 将数据文件 00010 还原到 D:\ORACLE\ORADATA\ORCLBPM\BLOCK01.DBF
通道 t1: 正在读取备份片段 E:\ORABACK\ORCLBPM_9AQ7K1NM_20150522.BAK
通道 t1: ORA-19870: 还原备份片段 E:\ORABACK\ORCLBPM_9AQ7K1NM_20150522.BAK 时出错
ORA-19612: 数据文件 1 没有还原, 因为 missing or corrupt data

故障转移到上一个备份

释放的通道: t1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: restore 命令 (在 05/29/2015 17:03:44 上) 失败
RMAN-06026: 有些目标没有找到 - 终止还原
RMAN-06023: 没有找到数据文件1的副本来还原

从错误信息： ORA-19870: 还原备份片段 E:\ORABACK\ORCLBPM_9AQ7K1NM_20150522.BAK 时出错，ORA-19612: 数据文件 1 没有还原, 因为 missing or corrupt data可以知道在执行还原数据文件1时备份片段丢失或有坏块，这里明显是有坏块，因为其它数据文件使用该备份片段已经还原成功了。这里通过设置事件让RMAN跳过坏块来还原数据文件。
C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期五 5月 29 18:02:22 2015

连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system set event=’19548 trace name context forever’, ‘19549 trace name
context forever’ scope=spfile;

系统已更改。

SQL> shutdown immediate
ORA-01109: 数据库未打开

已经卸载数据库。
ORACLE 例程已经关闭。

SQL> startup mount
ORACLE 例程已经启动。

Total System Global Area 7666352128 bytes
Fixed Size 2190736 bytes
Variable Size 3942646384 bytes
Database Buffers 3707764736 bytes
Redo Buffers 13750272 bytes
数据库装载完毕。

RMAN> restore database;

启动 restore 于 29-5月 -15
使用目标数据库控制文件替代恢复目录
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: SID=1141 设备类型=DISK

正在略过数据文件 2; 已还原到文件 D:\ORACLE\ORADATA\ORCLBPM\SYSAUX01.DBF
正在略过数据文件 3; 已还原到文件 D:\ORACLE\ORADATA\ORCLBPM\UNDOTBS01.DBF
正在略过数据文件 4; 已还原到文件 D:\ORACLE\ORADATA\ORCLBPM\USERS01.DBF
正在略过数据文件 5; 已还原到文件 D:\ORACLE\ORADATA\ORCLBPM\HB_SY01.DBF
正在略过数据文件 6; 已还原到文件 D:\ORACLE\ORADATA\ORCLBPM\HB_SY02.DBF
正在略过数据文件 8; 已还原到文件 D:\ORACLE\ORADATA\ORCLBPM\BPMSY01.DBF
正在略过数据文件 9; 已还原到文件 D:\ORACLE\ORADATA\ORCLBPM\HMBPM01.DBF
正在略过数据文件 10; 已还原到文件 D:\ORACLE\ORADATA\ORCLBPM\BLOCK01.DBF
通道 ORA_DISK_1: 正在开始还原数据文件备份集
通道 ORA_DISK_1: 正在指定从备份集还原的数据文件
通道 ORA_DISK_1: 将数据文件 00001 还原到 D:\ORACLE\ORADATA\ORCLBPM\SYSTEM01.DBF
通道 ORA_DISK_1: 将数据文件 00007 还原到 D:\ORACLE\ORADATA\ORCLBPM\HB_SY03.DBF
通道 ORA_DISK_1: 正在读取备份片段 E:\ORABACK\ORCLBPM_9AQ7K1NM_20150522.BAK
通道 ORA_DISK_1: 段句柄 = E:\ORABACK\ORCLBPM_9AQ7K1NM_20150522.BAK 标记 = TAG2015052
通道 ORA_DISK_1: 已还原备份片段 1
通道 ORA_DISK_1: 还原完成, 用时: 00:02:45
完成 restore 于 29-5月 -15

下面对之前还原报错的数据文件SYSTEM01.DBF，HB_SY03.DBF进行验证
C:\Users\Administrator>dbv file=D:\ORACLE\ORADATA\ORCLBPM\SYSTEM01.DBF blocksize
=8192

DBVERIFY: Release 11.2.0.1.0 – Production on 星期五 5月 29 18:29:19 2015

DBVERIFY – 开始验证: FILE = D:\ORACLE\ORADATA\ORCLBPM\SYSTEM01.DBF

DBVERIFY – 验证完成

检查的页总数: 280064
处理的页总数 (数据): 242009
失败的页总数 (数据): 0
处理的页总数 (索引): 13233
失败的页总数 (索引): 0
处理的页总数 (其他): 3322
处理的总页数 (段) : 1
失败的总页数 (段) : 0
空的页总数: 21500
标记为损坏的总页数: 0
流入的页总数: 0
加密的总页数 : 0
最高块 SCN : 87501555 (0.87501555)

C:\Users\Administrator>dbv file=D:\ORACLE\ORADATA\ORCLBPM\HB_SY03.DBF blocksize=
8192

DBVERIFY: Release 11.2.0.1.0 – Production on 星期五 5月 29 18:30:35 2015

DBVERIFY – 开始验证: FILE = D:\ORACLE\ORADATA\ORCLBPM\HB_SY03.DBF

DBVERIFY – 验证完成

检查的页总数: 524288
处理的页总数 (数据): 256890
失败的页总数 (数据): 0
处理的页总数 (索引): 52837
失败的页总数 (索引): 0
处理的页总数 (其他): 196657
处理的总页数 (段) : 0
失败的总页数 (段) : 0
空的页总数: 17904
标记为损坏的总页数: 0
流入的页总数: 0
加密的总页数 : 0
最高块 SCN : 87500838 (0.87500838)

从上面的验证可以看到没有坏块，幸运哈哈。我们来检查一个控制文件中记录的数据文件检查点SCN与数据文件头SCN是否相同，如果不同可能需要恢复。

SQL> select file#,checkpoint_change#,to_char(checkpoint_time,'yyyy-mm-dd hh24:mi
:ss') checkpoint_time from v$datafile;

     FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME
---------- ------------------ -------------------
         1           87500284 2015-05-22 23:00:42
         2           87500284 2015-05-22 23:00:42
         3           87500284 2015-05-22 23:00:42
         4           87500284 2015-05-22 23:00:42
         5           87500284 2015-05-22 23:00:42
         6           87500284 2015-05-22 23:00:42
         7           87500284 2015-05-22 23:00:42
         8           87500284 2015-05-22 23:00:42
         9           87500284 2015-05-22 23:00:42
        10           87500284 2015-05-22 23:00:42

已选择10行。

SQL> select file#,checkpoint_change#,name from v$datafile_header;

     FILE# CHECKPOINT_CHANGE# NAME
---------- ------------------ ----------------------------------
         1           87499953 D:\ORACLE\ORADATA\ORCLBPM\SYSTEM01.DBF
         2           87499953 D:\ORACLE\ORADATA\ORCLBPM\SYSAUX01.DBF
         3           87499953 D:\ORACLE\ORADATA\ORCLBPM\UNDOTBS01.DBF
         4           87499953 D:\ORACLE\ORADATA\ORCLBPM\USERS01.DBF
         5           87499953 D:\ORACLE\ORADATA\ORCLBPM\HB_SY01.DBF
         6           87499953 D:\ORACLE\ORADATA\ORCLBPM\HB_SY02.DBF
         7           87499953 D:\ORACLE\ORADATA\ORCLBPM\HB_SY03.DBF
         8           87499953 D:\ORACLE\ORADATA\ORCLBPM\BPMSY01.DBF
         9           87499953 D:\ORACLE\ORADATA\ORCLBPM\HMBPM01.DBF
        10           87499953 D:\ORACLE\ORADATA\ORCLBPM\BLOCK01.DBF


已选择10行。

可以看到所有数据文件头记录的检查点scn都一样，控制文件中记录的所有数据文件检查点scn都一样，但数据文件头检查点scn与控制文件中记录的数据文件检查点scn不一样，所以选择重建控制文件来执行恢复，这里将控制文件备份到跟踪文件中。

SQL> oradebug setmypid
已处理的语句
SQL> alter database backup controlfile to trace
  2  ;

数据库已更改。

SQL> oradebug tracefile_name
d:\app\administrator\diag\rdbms\orclbpm\orclbpm\trace\orclbpm_ora_1704.trc

下面用跟踪文件的控制文件创建语句来重新创建控制文件，这里要使用resetlogs方式来创建，因为RMAN的备份文件中没有联机重做日志文件。

SQL> shutdown immediate
ORA-01109: ??????


已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup nomount
ORACLE 例程已经启动。

Total System Global Area 7666352128 bytes
Fixed Size                  2190736 bytes
Variable Size            3942646384 bytes
Database Buffers         3707764736 bytes
Redo Buffers               13750272 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "ORCLBPM" RESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 'D:\ORACLE\ORADATA\ORCLBPM\REDO01.LOG'  SIZE 50M BLOCKSIZE 512,
  9    GROUP 2 'D:\ORACLE\ORADATA\ORCLBPM\REDO02.LOG'  SIZE 50M BLOCKSIZE 512,
 10    GROUP 3 'D:\ORACLE\ORADATA\ORCLBPM\REDO03.LOG'  SIZE 50M BLOCKSIZE 512
 11  -- STANDBY LOGFILE
 12  DATAFILE
 13    'D:\ORACLE\ORADATA\ORCLBPM\SYSTEM01.DBF',
 14    'D:\ORACLE\ORADATA\ORCLBPM\SYSAUX01.DBF',
 15    'D:\ORACLE\ORADATA\ORCLBPM\UNDOTBS01.DBF',
 16    'D:\ORACLE\ORADATA\ORCLBPM\USERS01.DBF',
 17    'D:\ORACLE\ORADATA\ORCLBPM\HB_SY01.DBF',
 18    'D:\ORACLE\ORADATA\ORCLBPM\HB_SY02.DBF',
 19    'D:\ORACLE\ORADATA\ORCLBPM\HB_SY03.DBF',
 20    'D:\ORACLE\ORADATA\ORCLBPM\BPMSY01.DBF',
 21    'D:\ORACLE\ORADATA\ORCLBPM\HMBPM01.DBF',
 22    'D:\ORACLE\ORADATA\ORCLBPM\BLOCK01.DBF'
 23  CHARACTER SET ZHS16GBK
 24  ;

控制文件已创建。

检查一个控制文件中记录的数据文件检查点SCN与数据文件头SCN是否相同，数据文件头检查点scn与控制文件中记录的数据文件检查点scn一样，在没有联机重做日志和归档重做日志的情况下，我们只能将数据库恢复到scn:87499953时间点，并以open resetlogs选项来open数据库

SQL> select FILE#,CHECKPOINT_CHANGE# from v$datafile_header;

     FILE# CHECKPOINT_CHANGE#
---------- ------------------
         1           87499953
         2           87499953
         3           87499953
         4           87499953
         5           87499953
         6           87499953
         7           87499953
         8           87499953
         9           87499953
        10           87499953

已选择10行。

SQL> select FILE#, CHECKPOINT_CHANGE#,LAST_CHANGE# from v$datafile;

     FILE# CHECKPOINT_CHANGE# LAST_CHANGE#
---------- ------------------ ------------
         1           87499953
         2           87499953
         3           87499953
         4           87499953
         5           87499953
         6           87499953
         7           87499953
         8           87499953
         9           87499953
        10           87499953

已选择10行。

这里需要设置隐含参数_allow_resetlogs_corruption=true来open数据库。

SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile;

系统已更改。

SQL> shutdown immediate
ORA-01109: ??????


已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount;
ORACLE 例程已经启动。

Total System Global Area 7666352128 bytes
Fixed Size                  2190736 bytes
Variable Size            3942646384 bytes
Database Buffers         3707764736 bytes
Redo Buffers               13750272 bytes
数据库装载完毕。
SQL> alter database open resetlogs;

数据库已更改。

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE 'D:\ORACLE\ORADATA\ORCLBPM\TEMP01.DBF' R
EUSE;

表空间已更改。

SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。

Total System Global Area 7666352128 bytes
Fixed Size                  2190736 bytes
Variable Size            3942646384 bytes
Database Buffers         3707764736 bytes
Redo Buffers               13750272 bytes
数据库装载完毕。
数据库已经打开。