maintain

Scripts: Monitor Tablespace space growth

January 10, 2013 maintain, oracle No comments

这个脚本可以监控tablespace 空间增长趋势,并且邮件通知到相关domain– 根据TOAD SPACE MANAGEMENT 修改

创建schema,tablespace,table:

create tablespace toad '+ARCH/data/tbs_tst01_00.dbf' size 50G autoextend off;

create user toad identified by toad account unlock;

create user TOAD
  default tablespace toad
  temporary tablespace TEMP
  profile DEFAULT
  quota unlimited on toad;
-- Grant/Revoke object privileges 
grant select on sys.DBA_DATA_FILES to TOAD;
grant select on sys.DBA_FREE_SPACE to TOAD;
grant select on sys.DBA_TABLESPACES to TOAD;
grant select on sys.V_$FILESTAT to TOAD;
-- Grant/Revoke role privileges 
grant connect to TOAD;
grant resource to TOAD;
-- Grant/Revoke system privileges 
grant create any synonym to TOAD;
grant create public synonym to TOAD;
grant create session to TOAD;
grant select any dictionary to TOAD;
grant select any table to TOAD;
grant unlimited tablespace to TOAD;




-- Create table
create table TOAD_TABLESPACES
(
  tablespace_name VARCHAR2(30) not null,
  mon_date        DATE not null
)
tablespace SYSTEM
  pctfree 5
  pctused 94
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table TOAD_TABLESPACES
  add constraint TOAD_TABLESPACES_PK primary key (TABLESPACE_NAME, MON_DATE)
  using index 
  tablespace SYSTEM
  pctfree 5
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Grant/Revoke object privileges 
grant select, insert, update, delete on TOAD_TABLESPACES to PUBLIC;


-- Create table
create table TOAD_DATA_FILES
(
  file_id         NUMBER not null,
  mon_date        DATE not null,
  tablespace_name VARCHAR2(30) not null,
  file_name       VARCHAR2(257) not null,
  bytes           NUMBER
)
tablespace SYSTEM
  pctfree 5
  pctused 94
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table TOAD_DATA_FILES
  add constraint TOAD_DATA_FILES_PK primary key (FILE_ID, MON_DATE)
  using index 
  tablespace SYSTEM
  pctfree 5
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
alter table TOAD_DATA_FILES
  add constraint TOAD_DATA_FILES_FK foreign key (TABLESPACE_NAME, MON_DATE)
  references TOAD_TABLESPACES (TABLESPACE_NAME, MON_DATE) on delete cascade;
-- Create/Recreate indexes 
create unique index TOAD_DATA_FILES_UK on TOAD_DATA_FILES (TABLESPACE_NAME, FILE_NAME, MON_DATE)
  tablespace SYSTEM
  pctfree 5
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Grant/Revoke object privileges 
grant select, insert, update, delete on TOAD_DATA_FILES to PUBLIC;


-- Create table
create table TOAD_FILESTAT
(
  file_id   NUMBER not null,
  mon_date  DATE not null,
  phyrds    CHAR(18) not null,
  phywrts   CHAR(18) not null,
  phyblkrd  CHAR(18) not null,
  phyblkwrt CHAR(18) not null,
  readtim   CHAR(18) not null,
  writetim  CHAR(18) not null
)
tablespace SYSTEM
  pctfree 5
  pctused 94
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table TOAD_FILESTAT
  add constraint TOAD_FILESTAT_PK primary key (FILE_ID, MON_DATE)
  using index 
  tablespace SYSTEM
  pctfree 5
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
alter table TOAD_FILESTAT
  add constraint TOAD_FILESTAT_FK foreign key (FILE_ID, MON_DATE)
  references TOAD_DATA_FILES (FILE_ID, MON_DATE) on delete cascade;
-- Grant/Revoke object privileges 
grant select, insert, update, delete on TOAD_FILESTAT to PUBLIC;

-- Create table
create table TOAD_FREE_SPACE
(
  file_id  NUMBER not null,
  mon_date DATE not null,
  bytes    NUMBER not null
)
tablespace SYSTEM
  pctfree 5
  pctused 94
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table TOAD_FREE_SPACE
  add constraint TOAD_FREE_SPACE_PK primary key (FILE_ID, MON_DATE)
  using index 
  tablespace SYSTEM
  pctfree 5
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
alter table TOAD_FREE_SPACE
  add constraint TOAD_FREE_SPACE_FK foreign key (FILE_ID, MON_DATE)
  references TOAD_DATA_FILES (FILE_ID, MON_DATE) on delete cascade;
-- Grant/Revoke object privileges 
grant select, insert, update, delete on TOAD_FREE_SPACE to PUBLIC;


-- Create table
create table TOAD_REF
(
  name  VARCHAR2(30),
  value NUMBER
)
tablespace SYSTEM
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Grant/Revoke object privileges 
grant select, insert, update, delete on TOAD_REF to PUBLIC;

创建PACKAGE BODY DBMS_JOB:

CREATE OR REPLACE PACKAGE TOAD_SPACEMAN IS
  PROCEDURE CAPTURE; 
  PROCEDURE RESET; 
END; 



CREATE OR REPLACE PACKAGE BODY TOAD_SPACEMAN IS
PROCEDURE RESET AS 
BEGIN 
  /* DELETE DATA FROM ALL TABLES */ 
  DELETE FROM TOAD.TOAD_TABLESPACES; 
  COMMIT; 

  /* LOAD REAL DATA FOR TODAY */ 
  TOAD.TOAD_SPACEMAN.CAPTURE; 

  /* LOAD DUMMY DATA FOR 60 DAYS */ 
  FOR I IN 1..60 LOOP 
    INSERT INTO TOAD.TOAD_TABLESPACES 
    SELECT TABLESPACE_NAME, TRUNC(MON_DATE-I) 
    FROM TOAD.TOAD_TABLESPACES WHERE MON_DATE = TRUNC(SYSDATE); 
    COMMIT; 

    INSERT INTO TOAD.TOAD_DATA_FILES 
    SELECT FILE_ID, TRUNC(MON_DATE-I), TABLESPACE_NAME, FILE_NAME, ROUND(BYTES*(1-I/100)) 
    FROM TOAD.TOAD_DATA_FILES WHERE MON_DATE = TRUNC(SYSDATE); 
    COMMIT; 

    INSERT INTO TOAD.TOAD_FREE_SPACE 
    SELECT FILE_ID, TRUNC(MON_DATE-I), ROUND(BYTES*(1+I/100)) 
    FROM TOAD.TOAD_FREE_SPACE WHERE MON_DATE = TRUNC(SYSDATE); 
    COMMIT; 

    INSERT INTO TOAD.TOAD_FILESTAT 
    SELECT FILE_ID, TRUNC(MON_DATE-I), PHYRDS, PHYWRTS, 
    PHYBLKRD, PHYBLKWRT, READTIM, WRITETIM 
    FROM TOAD.TOAD_FILESTAT WHERE MON_DATE = TRUNC(SYSDATE); 
    COMMIT; 

  END LOOP; 

END RESET; 

PROCEDURE CAPTURE AS 

  V_MON_DATE    DATE    := NULL; 
  V_ROW_SIZE    INTEGER := 100000; 
  NumDaysToRemove NUMBER; 
  CURSOR History_Cur IS 
    SELECT Value 
      FROM TOAD.TOAD_REF 
     WHERE Name = 'Space Manager History Limit'; 

BEGIN 
  /* SAVE TODAYS DATE */ 
  V_MON_DATE := TRUNC(SYSDATE); 
  /* RETRIEVE THE NUMBER OF DAYS TO REMOVE */ 
  OPEN History_Cur; 
  FETCH History_Cur INTO NumDaysToRemove; 

  /* DELETE DATA OLDER THAN USER SPECIFIED DAYS */ 
  DELETE FROM TOAD.TOAD_TABLESPACES WHERE MON_DATE < V_MON_DATE - NumDaysToRemove; 
  COMMIT; 

  /* DELETE PRE-EXISTING DATA FOR CURRENT DAY */ 
  DELETE FROM TOAD.TOAD_TABLESPACES WHERE MON_DATE = V_MON_DATE; 
  COMMIT; 
  /* DELETE DATA FOR NONEXISTENT TABLESPACES */ 
  DELETE FROM TOAD.TOAD_TABLESPACES X 
  WHERE NOT EXISTS (SELECT 1 FROM DBA_TABLESPACES D 
  WHERE X.TABLESPACE_NAME = D.TABLESPACE_NAME); 
  COMMIT; 

  --/* DELETE DATA FOR NONEXISTENT DATA FILES */ 
  --DELETE FROM TOAD_DATA_FILES X 
  -- WHERE NOT EXISTS (SELECT 1 FROM DBA_DATA_FILES D 
  --                    WHERE X.TABLESPACE_NAME = D.TABLESPACE_NAME 
  --                      AND X.FILE_NAME       = D.FILE_NAME); 
  --COMMIT; 

  /* INSERT NEW TABLESPACES FOUND */ 
  INSERT INTO TOAD.TOAD_TABLESPACES 
  SELECT TABLESPACE_NAME, V_MON_DATE 
    FROM DBA_TABLESPACES; 
  COMMIT; 

  /* INSERT NEW DATA FILES FOUND */ 
  INSERT INTO TOAD.TOAD_DATA_FILES 
  SELECT FILE_ID, V_MON_DATE, TABLESPACE_NAME, FILE_NAME, BYTES 
    FROM DBA_DATA_FILES; 
  COMMIT; 

  /* COLLECT CURRENT FREE SPACE DATA */ 
  INSERT INTO TOAD.TOAD_FREE_SPACE 
  SELECT FILE_ID, V_MON_DATE, SUM(BYTES) 
    FROM DBA_FREE_SPACE 
   GROUP BY FILE_ID, TRUNC(SYSDATE); 
  COMMIT; 

  /* COLLECT CURRENT IO RATE DATA */ 
  INSERT INTO TOAD.TOAD_FILESTAT 
  SELECT FILE#, V_MON_DATE, PHYRDS, PHYWRTS, 
         PHYBLKRD, PHYBLKWRT, READTIM, WRITETIM 
    FROM V$FILESTAT; 
  COMMIT; 

END CAPTURE; 
END TOAD_SPACEMAN; 


begin
  sys.dbms_job.submit(job => :job,
                      what => 'BEGIN 
  TOAD.TOAD_SPACEMAN.CAPTURE; 
END;',
                      next_date => to_date('11-01-2013', 'dd-mm-yyyy'),
                      interval => 'TRUNC(SYSDATE+1)');
  commit;
end;
/

修改监控脚本,监控tablespace趋势并MAIL

0 17 * * * sh /home/oracle/tools/tablespace.sh >> /home/oracle/tools/tablespace.log 2>&1


-bash-3.2$ more  /home/oracle/tools/tablespace.sh
#!/bin/bash
. /home/oracle/.profile

sqlplus / as sysdba <<eof
set head off;
set echo off;
set term off;
set pages 0;
set feed off;
SET SQLPROMPT --SQL
#set echo off pagesize 0 feedback off line 125
spool tablespace.csv
@/home/oracle/tools/tablespace.sql

spool off
exit

eof


(cat tablespace.csv| grep ,- |awk -F "," '$NF < 0 ' && uuencode tablespace.csv tablespace.csv )|mailx -s 'USER_space_10.0.1.4' lihang@xxx.com
(cat tablespace.csv| grep ,- |awk -F "," '$NF < 0 ' && uuencode tablespace.csv tablespace.csv )|mailx -s 'USER_space_10.0.1.4' dba@xxx.com

-bash-3.2$ 
-bash-3.2$ 
-bash-3.2$ more /home/oracle/tools/tablespace.sql
set head off;
set echo off;
set term off;
set pages 0;
set feed off;
SET SQLPROMPT --SQL
#set echo off pagesize 0 feedback off line 125

select 'TABLESPACE_NAME,MAX,MEGS_ALLOC,MEGS_USED,MEGS_FREE,MAX_MEGS_FREE,PCT_USED,PCT_FREE,LAST7DAYS_USED_MEGS,7DAYSLATER_REMAIN_MEGS' from dual union all
select  a.tablespace_name
        ||','|| round(maxbytes/1048576) --Max
        ||','|| round(a.bytes_alloc / 1024 / 1024) --megs_alloc
        ||','|| round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024) --megs_used
        ||','|| round(nvl(b.bytes_free, 0) / 1024 / 1024) --megs_free
        ||','|| (round(maxbytes/1048576)-(round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024))) --Max_megs_free
        ||','|| (100 - round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100)) --Pct_used
        ||','|| round((nvl(b.bytes_free, 0) / a.bytes_alloc) * 100) --Pct_Free
        ||','|| c.last7days_used_megs
        ||','|| (round(maxbytes/1048576)-(round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024))-c.last7days_used_megs) --"7DAYSLATER_REMAIN_MEGS"
from  ( select  f.tablespace_name,
               sum(f.bytes) bytes_alloc,
               sum(decode(f.autoextensible, 'YES',f.maxbytes,'NO', f.bytes)) maxbytes
        from dba_data_files f
        group by tablespace_name) a,
      ( select  f.tablespace_name,
               sum(f.bytes)  bytes_free
        from dba_free_space f
        group by tablespace_name) b,
        (select x.tablespace_name,(x.Megs-y.Megs) last7days_used_megs
from
(
SELECT   tablespace_name, (SUM (a.bytes)-sum(b.bytes))/1024/1024  Megs
    FROM   toad_data_files a,toad_free_space b
   WHERE   a.mon_date = TRUNC (SYSDATE - 1)
     and a.file_id=b.file_id(+)
     and a.mon_date=b.mon_date(+)
GROUP BY   tablespace_name
)x,
(
SELECT   tablespace_name, (SUM (a.bytes)-sum(b.bytes))/1024/1024  Megs
    FROM   toad_data_files a,toad_free_space b
   WHERE   a.mon_date = TRUNC (SYSDATE - 8)
     and a.file_id=b.file_id(+)
     and a.mon_date=b.mon_date(+)
GROUP BY   tablespace_name
) y
where x.tablespace_name=y.tablespace_name
) c
where a.tablespace_name = b.tablespace_name (+)
  and a.tablespace_name = c.tablespace_name (+)
;

同理 可以使用 dba_hist_tbspc_space_usage 实现以上功能 不过貌似dba_hist_tbspc_space_usage要license.

Can not purge obsolete cursor in 11.2.0.3

November 28, 2012 11g, maintain, oracle No comments

11.2.0.3中Oracle引入了_cursor_obsolete_threshold隐藏参数,默认为100,表示一个parent cursor最多可以有100个child cursor;如果SQL需要第101个child cursor,则将该parent cursor and its 100 child cursor设置为obsoleted,并且重新生成新的parent cursor. 但是那些过期的child cursor不会从v$sql中消失,dbms_shared_pool.purge也无法将这些过期的child cursor flush出去,Oracle正在fix这个bug.

eg:

-bash-3.2$ ./opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.0
Copyright (c) 2012, Oracle Corporation. All rights reserved.

Oracle Home : /u01/app/oracle/product/11.2.0/db_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2.0/db_1/oraInst.loc
OPatch version : 11.2.0.3.0
OUI version : 11.2.0.3.0
Log file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/opatch2012-11-28_12-40-02PM_1.log

Lsinventory Output file location : /u01/app/oracle/product/11.2.0/db_1/cfgtoollogs/opatch/lsinv/lsinventory2012-11-28_12-40-02PM.txt

——————————————————————————–
Installed Top-level Products (1):

Oracle Database 11g 11.2.0.3.0
There are 1 products installed in this Oracle Home.

Interim patches (1) :

Patch 13923374 : applied on Thu Sep 20 14:10:21 CST 2012
Unique Patch ID: 14893407
Patch description: “Database Patch Set Update : 11.2.0.3.3 (13923374)”
Created on 14 Jun 2012, 23:16:11 hrs PST8PDT
Sub-patch 13696216; “Database Patch Set Update : 11.2.0.3.2 (13696216)”
Sub-patch 13343438; “Database Patch Set Update : 11.2.0.3.1 (13343438)”

From v$sqlarea only return one row:

SQL> select address,hash_value from v$sqlarea where sql_id=’27svyp3s52cu0′

ADDRESS HASH_VALUE
—————- ———-
0700001DBB270900 4031853376

you can only purge this cursor:

SQL> exec dbms_shared_pool.purge(‘0700001DBB270900,4031853376′,’C’);

PL/SQL procedure successfully completed.

but if you want to purge like this:

SQL> select distinct address,hash_value from v$sql where sql_id=’27svyp3s52cu0′;

ADDRESS HASH_VALUE
—————- ———-
0700001DBE384CD8 4031853376
0700001DBD3119A0 4031853376
0700001DBC063368 4031853376
0700001DBDB39548 4031853376
0700001DBC993898 4031853376
0700001DBC75A2E8 4031853376
0700001DBCC34B20 4031853376
0700001DBB270900 4031853376
0700001DBCFFE730 4031853376
0700001DBCB15A70 4031853376
0700001DBFA741A8 4031853376
0700001DBC4FEA60 4031853376
0700001DBE3F49F8 4031853376
0700001DBB4D1938 4031853376

14 rows selected

SQL> exec dbms_shared_pool.purge(‘0700001DBE384CD8,4031853376′,’C’);

begin dbms_shared_pool.purge(‘0700001DBE384CD8,4031853376′,’C’); end;

ERROR at line 1:
ORA-06570: shared pool object does not exist, cannot be pinned/purged
ORA-06512: at “SYS.DBMS_SHARED_POOL”, line 48
ORA-06512: at “SYS.DBMS_SHARED_POOL”, line 115
ORA-06512: at line 1

SQL> exec dbms_shared_pool.purge(‘0700001DBB4D1938,4031853376′,’C’);
BEGIN dbms_shared_pool.purge(‘0700001DBB4D1938,4031853376′,’C’); END;

*
ERROR at line 1:
ORA-06570: shared pool object does not exist, cannot be pinned/purged
ORA-06512: at “SYS.DBMS_SHARED_POOL”, line 48
ORA-06512: at “SYS.DBMS_SHARED_POOL”, line 115
ORA-06512: at line 1

extended statistics for this cursor:

Shared Cursors Statistics
Total Parses		29,918
Hard Parses		1,665
Child Cursors		1,478
Loaded Plans		1,478
Invalidations		1,473
Largest Cursor Size (KB)		292.43
All Cursor Size (KB)		399,376.98
First Load Time		Nov 23, 2012 4:26:13 AM GMT+08:00
Last Load Time		Nov 28, 2012 1:10:10 PM GMT+08:00
Execution Statistics

Total	Per Execution	Per Row
Executions	1,264,476	1	1.00
Elapsed Time (sec)	18,717.60	0.01	0.01
CPU Time (sec)	1,087.67	<0.01	<0.01
Buffer Gets	114,482,137	90.54	90.54
Disk Reads	2,241,885	1.77	1.77
Direct Writes	0	0.00	0.00
Rows	1,264,476	1.00	1
Fetches	0	0.00	0.00



Other Statistics
Executions that Fetched all Rows (%)	100.00
Average Persistent Mem (KB)	45.90
Average Runtime Mem (KB)	41.75
Serializable Aborts	0
Remote	No
Obsolete Yes
Child Latch Number 0

针对这个问题要说明一下,这是一条简单的insert语句 但是bind value达到了150+ 对于这种设计不可避免的出现了bind variable graduation。另外由于一条insert需要耗费14ms,此sql已经成为了这个系统的top1 sql,并且在11.2.0.3中遇到了这个bug(so sad ~)。由此可见表设计的重要性。目前在国内,为了满足业务的需求,无休止的为表添加column依然成为习惯。不过当你的系统已经接近瓶颈时候,你就会发现当初的决定是多么的愚蠢。

Bug 14127231 dbms_shared_pool.purge raised ora-6570 on obsoleted child cursors
This note gives a brief overview of bug 14127231.
The content was last updated on: 06-JUL-2012
Click here for details of each of the sections below.
Affects:
Product (Component) Oracle Server (Rdbms)
Range of versions believed to be affected Versions >= 11.2 but BELOW 12.1
Versions confirmed as being affected => 11.2.0.3

Platforms affected Generic (all / most platforms affected)
Fixed:
This issue is fixed in ? 12.1 (Future Release)
11.2.0.4 (Future Patch Set)

Symptoms: Related To:
Error May Occur
ora-6570 PL/SQL (DBMS Packages)
dbms_shared_pool

Description
dbms_shared_pool.purge() returns ora-6570 when trying to purge an obsolete object.

Please note: The above is a summary description only. Actual symptoms can vary. Matching to any symptoms here does not confirm that you are encountering this problem. For questions about this bug please consult Oracle Support.
References
Bug:14127231 (This link will only work for PUBLISHED bugs)
Note:245840.1 Information on the sections in this article

————–

SQL> alter system set "_cursor_obsolete_threshold"=3 scope=spfile;

System altered.

SQL> 
SQL> startup force;



SQL> set linesize 300
SQL> select sql_text,sql_id,address,hash_value,is_obsolete  from v$sql where sql_text like '%INSERT INTO t (n, v) VALUES%';

SQL_TEXT                                                               SQL_ID        ADDRESS          HASH_VALUE I
---------------------------------------------------------------------- ------------- ---------------- ---------- -
INSERT INTO t (n, v) VALUES (:n, :v)                                   6cvmu7dwnvxwj 000000011ECD0220 2035152785 Y
INSERT INTO t (n, v) VALUES (:n, :v)                                   6cvmu7dwnvxwj 000000011ECD0220 2035152785 Y
INSERT INTO t (n, v) VALUES (:n, :v)                                   6cvmu7dwnvxwj 000000011ECD0220 2035152785 Y
INSERT INTO t (n, v) VALUES (:n, :v)                                   6cvmu7dwnvxwj 000000011ECCE110 2035152785 N

SQL> 
SQL> 

Sys:

SQL> exec dbms_shared_pool.purge('000000011ECD0220,2035152785','C');

PL/SQL procedure successfully completed.



SQL> 
SQL> select sql_text,sql_id,address,hash_value,is_obsolete  from v$sql where sql_text like '%INSERT INTO t (n, v) VALUES%';

SQL_TEXT                                                               SQL_ID        ADDRESS          HASH_VALUE I
---------------------------------------------------------------------- ------------- ---------------- ---------- -
INSERT INTO t (n, v) VALUES (:n, :v)                                   6cvmu7dwnvxwj 000000011ECCE110 2035152785 N

SQL> 
SQL> 
SQL> exit

更新:在Patch 14127231中 已经修复这一bug 这个CPU针对solaris 和 linux 11.2.0.3.x.最经也release了 AIX 64 bit的patch.

记一次严重事故 — DBA 做任何操作前 都需要谨慎

October 16, 2012 maintain, oracle No comments

国庆回来之后没怎么更新blog 一直在处理两个事故 最严重的一次事故发生在10月10号凌晨:

朋友在mv datafile的过程中使用了rm的命令 本意是想删除之前的datafile来释放空间,结果由于之前敲过一条命令 “alter database rename file ‘xxxx’ to ‘yyyy’ ” 最后阴差阳错的敲成了rm alter database rename file ‘xxxx’ to ‘yyyy’ 瞬间将两份system01.dbf 删除.由于之后没有保护好现场 导致新的目录产生了大量的读写操作,并且原来的一份system01.dbf存在于”/”目录 使得恢复system01.dbf相当的困难。最后由于朋友删除了所有的归档文件,导致无法恢复到当前时间点,最后把一份丢失了一天数据的数据库拉了起来,虽然事后采用了很多方法来补救这次损失,但是投入了大量的人力物力。最后提醒所有DBA朋友,在做任何操作前请三思。

1 mv datafile之后 原来的文件不要立刻删除,保留一段时间对你没有坏处。
2 不要轻易的删除当前的归档文件,除非你确保你的操作不会产生任何意外。
3 在对数据库文件进行操作前,请备份你的数据库。
4 在误操作之后,请保留现场 不要乱动任何东西。
5 任何你认为重要的库,请做好灾备工作,备库往往是你挽回一切的最后砝码

最后感谢xifenfei,roger同学对这次事故的帮助。

Resource Manager (2)

September 9, 2012 maintain, oracle No comments

对于需要使用资源控制的应用,oracle提出了resource manager这一解决方案,针对不同的schema,Oracle提供不同的资源等级,但是这种资源的分配其实本身就存在一定的资源消耗,当大量的session涌入数据库的时候,oracle需要消耗一定数量的cpu去重新分配资源的调级,此时就会伴随的大量resmgr:cpu quantum等待事件,下面总结一下这次的测试:

环境简述,本次测试resource manager采用oracle 11.2.0.3 平台为IBM power750小型机具体配置如下:

System Model: IBM,8233-E8B
Machine Serial Number: 065443R
Processor Type: PowerPC_POWER7
Processor Implementation Mode: POWER 7
Processor Version: PV_7_Compat
Number Of Processors: 32
Processor Clock Speed: 3612 MHz
CPU Type: 64-bit
Kernel Type: 64-bit
LPAR Info: 1 06-5443R
Memory Size: 256512 MB
Good Memory Size: 256512 MB
Platform Firmware level: AL730_066
Firmware Version: IBM,AL730_066
Console Login: enable
Auto Restart: true
Full Core: false

压力端模拟大量bad sql 这些sql都是典型的消耗buffer get 与CPU的sql 在高并发的情况下 可以瞬间将cpu压至满负荷,参考下图:

可以看到压力测试在开启的几个时段可以直接将系统CPU 资源耗尽,下面的测试也是基于这个基础,使用user_data2跑bad sql ,而我们的test用户跑正常的sql

这套系统中我们使用swingbench正常测试结果为下图:

使用swingbench 100并发session,下面开始这次的测试
我们使用PLAN_NAME=’USER_TEST’ 分别设置3个level (TEST->level 1, USER_DATA2->level 2,Other->level 3):

exec DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
exec DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN=>'USER_TEST',COMMENT=>'Resource plan/method for USER');
exec DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP=> 'TEST_GROUP',COMMENT => 'Resource plan user group for TEST_GROUP');
exec DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP=> 'USER_GROUP',COMMENT => 'Resource plan user group for USER_GROUP');
exec DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN=>'USER_TEST',GROUP_OR_SUBPLAN => 'TEST_GROUP',COMMENT => 'FIRST',mgmt_p1 => 100,mgmt_p2 => 0,mgmt_p3=>0);
exec DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN=>'USER_TEST',GROUP_OR_SUBPLAN => 'OTHER_GROUPS',COMMENT => 'OTHER',mgmt_p1 => 0, mgmt_p2 => 0,mgmt_p3=>100);
exec DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN=>'USER_TEST',GROUP_OR_SUBPLAN => 'USER_GROUP',COMMENT => 'SECECOND',mgmt_p1 => 0, mgmt_p2 => 100,mgmt_p3=>0);
exec DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING (DBMS_RESOURCE_MANAGER.ORACLE_USER,'USER_DATA2', 'USER_GROUP');
exec DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING (DBMS_RESOURCE_MANAGER.ORACLE_USER,'TEST', 'TEST_GROUP');
exec dbms_resource_manager.switch_plan( plan_name => 'USER_TEST', sid => 'USER' )
exec DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP(user=>'TEST',consumer_group=>'TEST_GROUP');
exec DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP(user=>'USER',consumer_group=>'USER_GROUP');
exec DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
exec DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();

在开启Resource manager之后 保证test用户得到最大的资源利用,也就是说保证正常的应用最大程度的利用CPU资源,user_data2模拟bad sql(由测试部门提供专门的压力机器)

1.3600 bad sql sessions + 500 normal sessions (不开启RM):

2.3600 bad sql sessions + 500 normal sessions (开启RM)

3.128 bad sql sessions + 128 normal sessions (不开启RM)

4.128 bad sql sessions + 128 normal sessions (开启RM)

另外同事也测试了同一条SQL 在不同schema的情况通过AWR 可以发现oracle控制了被我们降级为level2的user_data2的资源使用(仅使用0.01%)但是DB time大量浪费在了cpu quantum

注意以上测试基于schema分级测试,使用不同schema测试得出的结论,在实际生产中同一套系统的不同应用一般不会区别schema,这个在下面的总结里会给到,在我们的final test 中,在同一个schema中开启RM 会影响到系统的总体吞吐量

总结
开启Resource Manager 的情况下 不同 schema 在资源分配level 1的情况下TPS TPM都有所提升 但是总体提升不多 (1200->1700),由于开启了RM 导致出现如下等待: resmgr:cpu quantum 这种等待会消耗大量的CPU 在系统不是很忙的情况下可能会消耗一定的系统资源。并且在不开启RM的情况下 CPU全部被耗尽的情况下:模拟前台的SQL依然可以正常工作TPS下降到1200左右,但在开启RM的情况下 oracle确实做到了schema级别的资源控制,这对特别重要的app起到了一定的保护作用,在最后的final test中开启RM 明显影响到了系统的吞吐量,前台的应用在保证了优先级的情况下SQL执行次数减少了一半之多。
Oracle为我们提供了一种在数据库层面实现资源分级的手段,至于这种方式是否合适,要针对不同的系统来调试不同的方案,正所谓没有完美的技术,只有完美的方案,如果单从个人角度来说不建议在核心系统开启RM。在Exadata系统中 oracle为我们提供了IORM在cell层面控制存储的IO资源分配,是将这个技术从DB层面延伸到了存储层面。此次测试数据仅供参考。

处理坏块一例(2)

August 17, 2012 Internals, maintain, oracle 2 comments

帮朋友check一次中国银联数据库的坏块问题(并不是严格意义上的坏块),大致情况为smon需要trascation recover —>特定的block 发现无法读取 —>transaction recover出现问题 —>smon terminated

Wed Aug 15 19:02:57 2012
SMON: Parallel transaction recovery slave got internal error
SMON: Downgrading transaction recovery to serial
Wed Aug 15 19:02:57 2012
Errors in file /oracle/admin/settora/bdump/settora_smon_4755904.trc:
ORA-00600: internal error code, arguments: [kdourp_inorder2], [43], [43], [7], [44], [], [], []
Doing block recovery for file 10 block 176769
Doing block recovery for file 26 block 3259716

Block recovery from logseq 108030, block 49455 to scn 41320651147
Wed Aug 15 19:02:58 2012
Recovery of Online Redo Log: Thread 1 Group 5 Seq 108030 Reading mem 0
Mem# 0: /orasvr/settora/redo05.log
Block recovery completed at rba 108030.49555.16, scn 9.2665945485
ORACLE Instance settora (pid = 8) – Error 600 encountered while recovering transaction (7, 19) on object 331910.

过一段时间数据库就会down掉,从日志信息里面可以看到:

Wed Aug 15 19:23:18 2012
SMON: Parallel transaction recovery slave got internal error
SMON: Downgrading transaction recovery to serial
Wed Aug 15 19:23:18 2012
Errors in file /oracle/admin/settora/bdump/settora_smon_4755904.trc:
ORA-00600: internal error code, arguments: [kdourp_inorder2], [43], [43], [7], [44], [], [], []
Wed Aug 15 19:23:20 2012
Errors in file /oracle/admin/settora/bdump/settora_pmon_4690184.trc:
ORA-00474: SMON process terminated with error
Wed Aug 15 19:23:20 2012
PMON: terminating instance due to error 474
Instance terminated by PMON, pid = 4690184

执行下面SQL

select /*+full(a)*/ from PRO_SETTLE3.SHOPACCOUNT a;

returns:ORA-00600: internal error code, arguments: [kdourp_inorder2], [43], [43], [7], [44], [], [], []

使用dbv工具来检测:

$ dbv file=/orasvr1/settle5/settle000.dbf blocksize=8192

DBVERIFY: Release 10.2.0.4.0 - Production on Thu Aug 16 09:40:49 2012

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

DBVERIFY - Verification starting : FILE = /orasvr1/settle5/settle000.dbf
kdrchk:  row is marked as both Last and Next continue
          prow=0x1101aec98 flag=0x07
Block Checking: DBA = 42119809, Block Type = KTB-managed data block
data header at 0x1101ae07c
kdbchk: bad row tab 0, slot 51
Page 176769 failed with check code 6253


DBVERIFY - Verification complete

Total Pages Examined         : 386688
Total Pages Processed (Data) : 282817
Total Pages Failing   (Data) : 1
Total Pages Processed (Index): 98760
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 2707
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 2404
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Highest block SCN            : 2666197038 (9.2666197038)

Total Pages Failing (Data) : 1

Page 176769 被标识为corruption

Total Pages Examined         : 512      Number of blocks looked at
Total Pages Processed (Data) : 1        Number of TABLE blocks seen
Total Pages Failing   (Data) : 1        Number of TABLE blocks with internal inconsistencies
Total Pages Processed (Index): 0        Number of INDEX blocks seen
Total Pages Failing   (Index): 0        Number of INDEX block with internal inconsistencies
Total Pages Empty            : 507      Number of unused blocks seen
Total Pages Marked Corrupt   : 2        Number of blocks with corrupt cache wrappers
Total Pages Influx           : 0        Number of pages we re-read as the page looked like it was being modified when it was first read.
SQL> select file#,block#,blocks from v$database_block_corruption;
 
no rows selected

在数据库中并没有关于坏块的记录存在。这并不是一个普通意义上的坏块。对于这种块使用corrupt skip是没有用的skip_corrupt_blocks只能使oracle跳过Oracle能够读出的块,而如果在操作系统层read调用就失败的,则不能跳过该块。
oracle 在read page 176769时发生了error 6253 (cannot read arguments from address file,其实这并不是真正的root cause,我在和huangyong讨论过后基本认定为一个内部的inconsistent block) oracle 不能 mark 为 corrupt block.

这里的思路为使用rowid来跳过这些有问题的block 大致方法如下

SQL> SELECT dbms_rowid.rowid_create(1,331910,10,176769,0) LOW_RID from DUAL;

LOW_RID
——————
AABRCGAAKAAArKBAAA

SQL> SELECT dbms_rowid.rowid_create(1,331910,10,176770,0) HI_RID from dual;

HI_RID
——————
AABRCGAAKAAArKCAAA

SQL> SELECT dbms_rowid.rowid_create(1,331910,26,3259716,0) LOW_RID from DUAL;

LOW_RID
——————
AABRCGAAaAAMb1EAAA

SQL> SELECT dbms_rowid.rowid_create(1,331910,26,3259717,0) HI_RID from DUAL;

HI_RID
——————
AABRCGAAaAAMb1FAAA

SQL> create table PRO_SETTLE3.SHOPACCOUNT_1 as select /*+ rowid(a) */ * from PRO_SETTLE3.SHOPACCOUNT a where rowid < 'AABRCGAAKAAArKBAAA'; Table created. SQL> insert into PRO_SETTLE3.SHOPACCOUNT_1 select /*+ rowid(a) */ * from PRO_SETTLE3.SHOPACCOUNT a where rowid >= ‘AABRCGAAKAAArKCAAA’ and rowid < 'AABRCGAAaAAMb1EAAA'; 49626 rows created. SQL> insert into PRO_SETTLE3.SHOPACCOUNT_1 select /*+ rowid(a) */ * from PRO_SETTLE3.SHOPACCOUNT a where rowid >= ‘AABRCGAAaAAMb1FAAA’;

208837 rows created.

SQL> select count(*) from PRO_SETTLE3.SHOPACCOUNT_1;

COUNT(*)
———-
262256

之后drop掉这张有问题的table restart database 恢复正常.

同样我们可以采用Kerry Osborne的脚本来实现.这里稍微修改了下 增加了选择tablespace 的功能temp

eg:

[oracle@testdb ~]$ dd if=/dev/zero  of=/data/oracle/oradata/wuxuan1/liu.dbf   bs=8192 seek=1798  count=1 conv=notrunc
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 5.2e-05 seconds, 158 MB/s
[oracle@testdb ~]$ sqlplus liu/liu

SQL*Plus: Release 11.2.0.3.0 Production on Fri Aug 17 02:32:20 2012

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

Error accessing PRODUCT_USER_PROFILE
Warning:  Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select count(*) from temp;
select count(*) from temp
                     *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 1798)
ORA-01110: data file 4: '/data/oracle/oradata/wuxuan1/liu.dbf'


SQL> @temp	

WARNING: This script may issue a DROP TABLE command. Do not execute it unless you have read through it
and are comfortable you know what it does.

Ready? (hit ctl-C to quit)  
Enter value for owner_name: LIU
old   8: v_owner_name varchar2(30) := upper('&owner_name');
new   8: v_owner_name varchar2(30) := upper('LIU');
Enter value for table_name: TEMP
old   9: v_table_name varchar2(30) := upper('&table_name');
new   9: v_table_name varchar2(30) := upper('TEMP');
Enter value for tablespace: LIU
old  11: v_tablespace varchar2(30) := upper('&tablespace');
new  11: v_tablespace varchar2(30) := upper('LIU');


Saved 13247 records in TEMP_SAVED.
201 bads records in TEMP_BAD.

PL/SQL procedure successfully completed.

SQL> 
SQL> select count(*) from temp_saved;

  COUNT(*)
----------
     13247
     
SQL> select  * from  v$database_block_corruption;

     FILE#     BLOCK#	  BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
	 4	  258	       1		  0 ALL ZERO
	 4	 1798	       1		  0 ALL ZERO

block 1798 已经被标识为media corruption

手动构造rowid

SQL> select * from temp_bad where rownum<5; 

OLD_ROWID			 OLD_FILE OLD_OBJECT  OLD_BLOCK    OLD_ROW
------------------------------ ---------- ---------- ---------- ----------
ERROR_MESSAGE
--------------------------------------------------------------------------------
4.1798.181			    13646	   4	   1798        181
ORA-01578: ORACLE data block corrupted (file # 4, block # 1798)
ORA-01110: data file 4: '/data/oracle/oradata/wuxuan1/liu.dbf'

4.1798.182			    13646	   4	   1798        182
ORA-01578: ORACLE data block corrupted (file # 4, block # 1798)
ORA-01110: data file 4: '/data/oracle/oradata/wuxuan1/liu.dbf'

4.1798.183			    13646	   4	   1798        183

OLD_ROWID			 OLD_FILE OLD_OBJECT  OLD_BLOCK    OLD_ROW
------------------------------ ---------- ---------- ---------- ----------
ERROR_MESSAGE
--------------------------------------------------------------------------------
ORA-01578: ORACLE data block corrupted (file # 4, block # 1798)
ORA-01110: data file 4: '/data/oracle/oradata/wuxuan1/liu.dbf'

4.1798.184			    13646	   4	   1798        184
ORA-01578: ORACLE data block corrupted (file # 4, block # 1798)
ORA-01110: data file 4: '/data/oracle/oradata/wuxuan1/liu.dbf'

SQL> select dbms_rowid.rowid_create(1,13646,4,1798,181) from dual;

DBMS_ROWID.ROWID_C
------------------
AAADVOAAEAAAAcGAC1

SQL> select dbms_rowid.rowid_create(1,13646,4,1798,182) from dual;

DBMS_ROWID.ROWID_C
------------------
AAADVOAAEAAAAcGAC2




SQL> select * from temp where rowid ='AAADVOAAEAAAAcGAC1';
select * from temp where rowid ='AAADVOAAEAAAAcGAC1'
              *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 1798)
ORA-01110: data file 4: '/data/oracle/oradata/wuxuan1/liu.dbf'


SQL> select * from temp where rowid ='AAADVOAAEAAAAcGAC2';
select * from temp where rowid ='AAADVOAAEAAAAcGAC2'
              *
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 4, block # 1798)
ORA-01110: data file 4: '/data/oracle/oradata/wuxuan1/liu.dbf'

Reference:
Extracting Data from a Corrupt Table using ROWID Range Scans in Oracle8 and higher [ID 61685.1]
Extracting Data from a Corrupt Table using DBMS_REPAIR or Event 10231 [ID 33405.1]
Extract rows from a CORRUPT table creating ROWID from DBA_EXTENTS [ID 422547.1]

Scripts: 查找每年特定星期x的日期

August 10, 2012 maintain, oracle No comments

11g之前还不支持分区自动interval day 功能,对于需要指定日期的分区需求例如每周2一个分区,可以采用下面脚本实现

select  'alter table END_USER_LOGIN_LOG_1 add partition P'|| TO_CHAR(a,'YYMMDD') ||  

' values less than (TO_DATE(''' || TO_CHAR(a,'YYYY-MM-DD HH24:MI:SS') 

|| ''', ''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN'')) tablespace UDATA01	;'

from (select next_day(to_date('2012-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')+(level-1)*7,3) a
from dual
connect by next_day(to_date('2012-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')+(level-1)*7,3) <= to_date('2013-12-31','yyyy-mm-dd'))

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

	"alter table END_USER_LOGIN_LOG_1 add partition P120103 values less than (TO_DATE('2012-01-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace UDATA01	;"
	"alter table END_USER_LOGIN_LOG_1 add partition P120110 values less than (TO_DATE('2012-01-10 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace UDATA01	;"
	"alter table END_USER_LOGIN_LOG_1 add partition P120117 values less than (TO_DATE('2012-01-17 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace UDATA01	;"
	"alter table END_USER_LOGIN_LOG_1 add partition P120124 values less than (TO_DATE('2012-01-24 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace UDATA01	;"
	"alter table END_USER_LOGIN_LOG_1 add partition P120131 values less than (TO_DATE('2012-01-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace UDATA01	;"
	"alter table END_USER_LOGIN_LOG_1 add partition P120207 values less than (TO_DATE('2012-02-07 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace UDATA01	;"
	"alter table END_USER_LOGIN_LOG_1 add partition P120214 values less than (TO_DATE('2012-02-14 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace UDATA01	;"
	"alter table END_USER_LOGIN_LOG_1 add partition P120221 values less than (TO_DATE('2012-02-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace UDATA01	;"
  
  
  .....

一些数据对象的查找脚本

July 28, 2012 maintain, oracle No comments

在帮银联恢复数据库的时候 客户要求导出库里的一些存储过程,这里整理了一些脚本供大家使用

创建view

select 
  'CREATE OR REPLACE VIEW '||O.NAME||' ('||
   replace(c.cols,',',','||chr(10))||')'||CHR(10)||
  'as'||chr(10), v.text
from
user$ u, obj$ o, view$ v,
( SELECT COL.OBJ#, COL.COLS
  FROM
  (SELECT 
    OBJ#, COL#, substr(SYS_CONNECT_BY_PATH(NAME,','),2) COLS
  FROM COL$
  WHERE COL# > 0
  START WITH COL# = 1
  CONNECT BY PRIOR OBJ# = OBJ# AND PRIOR COL# = COL# - 1 ) COL,
  (SELECT OBJ#, COUNT(*) COLCNT FROM COL$ 
  WHERE COL# > 0 GROUP BY OBJ#) CN
  WHERE COL.OBJ# = CN.OBJ# AND COL.COL# = CN.COLCNT
) C
where u.user#=o.owner# and o.obj# = c.obj#
  and v.obj# = o.obj# and u.name=':user'

需要导出sys.col$ sys.obj$

创建sequence

SELECT 
  'CREATE SEQUENCE '|| SEQ_NAME || 
  ' MINVALUE '||minval ||
  ' MAXVALUE '||MAXVAL ||
  ' START WITH '||LASTVAL ||
  ' ' || CYC || ' ' || ORD ||
  DECODE(SIGN(CACHE), 1,' CACHE '|| CACHE, 'NOCACHE') ||
  ';' SEQ_DDL
from
  (select u.name OWNER, o.name SEQ_NAME,
      s.minvalue MINVAL, s.maxvalue MAXVAL, 
      s.increment$ INC,
      decode (s.cycle#, 0, 'NOCYCLE', 1, 'CYCLE ') CYC,
      decode (s.order$, 0, 'NOORDER', 1, 'ORDER') ORD,
      s.cache, s.highwater LASTVAL
  from seq$ s, obj$ o, user$ u
  where u.user# = o.owner#
    and o.obj# = s.obj# 
    and u.name=':user')
    
需要导出sys.seq$ ,sys.user$ , sys.obj$

创建index

SELECT 
  'CREATE '||decode(bitand(IDX.property, 1), 1, 'UNIQUE', '')||
  ' INDEX '||I.NAME||' ON '||T.NAME||'('||IDX.PATH||');' INDEX_DDL
FROM 
  USER$ U, OBJ$  T, OBJ$ I, 
  (
    select I.PROPERTY, I.BO#, I.OBJ#, C.POS#,
            SUBSTR(sys_connect_by_path(CN.NAME,','),2) path
    from IND$ I, ICOL$ C, COL$ CN
    WHERE I.OBJ# = C.OBJ# AND I.BO# = C.BO#
      AND I.BO# = CN.OBJ# AND C.COL# = CN.INTCOL#
    start with C.POS#=1 
    connect by PRIOR I.OBJ# = I.OBJ# 
            AND prior C.POS# = C.POS# - 1 ) IDX,
  (SELECT I.BO#, I.OBJ#, COUNT(*) COLCNT 
    FROM ICOL$ I GROUP BY I.BO#, I.OBJ#) IDXC
WHERE 
  U.USER# = T.OWNER# AND 
  IDX.BO# = T.OBJ# AND
  IDX.OBJ# = I.OBJ# AND
  IDX.BO# =  IDXC.BO# AND
  IDX.OBJ# = IDXC.OBJ# AND
  IDX.POS# = IDXC.COLCNT AND
  U.NAME = ':user'
ORDER BY T.NAME, I.NAME

需要导出 sys.icol$, sys.col$,sys.ind$,sys.user$,sys.obj$

创建 存储过程

SELECT DECODE(S.LINE,1,'CREATE OR REPLACE ','')||SOURCE SOURCE
FROM 
  USER$ U, OBJ$  O, SOURCE$ S
WHERE 
  U.USER# = O.OWNER# AND 
  O.OBJ# = S.OBJ# AND
  U.NAME = ':user' 
  AND O.NAME = ':过程名'

需要导出 sys.obj$, sys.source$

创建 trigger and type

select u.name, o.name,
decode(o.type#, 12, 'TRIGGER', 13, 'TYPE', 14, 'TYPE BODY',
               'UNDEFINED') ,
DECODE(S.LINE,1,'CREATE OR REPLACE ','')||s.source 
from sys.obj$ o, sys.source$ s, sys.user$ u
where o.obj# = s.obj#
  and o.owner# = u.user#
  and u.name=':user'
  and ( o.type# in ( 12, 14) OR
       ( o.type# = 13 AND o.subname is null)) 

需要导出对象 sys.obj$,sys.source$,sys.user$

创建dblink 一般重新建就可以了 实在要弄 可以导出sys.link$,sys.user$

11.2.0.2 EXPDP 遭遇 ORA-4031

July 16, 2012 11g, maintain, oracle No comments

一套oracle 11.2.0.2 数据仓库系统使用expdp导出数据时 进程hang 死 使用kill -9命令杀掉之后 database一直没有释放latch 通过下面的日志可以发现 pmon已经发现 ospid=15038的process dead 但是无法clean 进程在为 “fixed allocation callback” 分配内存时 遭遇了ORA-4031,在这套库中stream_pool_size 并没有显式指定,而从11g开始oracle data pump开始使用Advanced Queue高级队列来控制其job作业,这也就是这个case的root cause。

我们可以通过显式指定stream_pool_size解决这个问题,注意指定stream_pool_size之后必须restart instance.

我们可以通过3个note完整的理解这个问题

DataPump Export (EXPDP) Fails With Errors UDE-31623 ORA-4031 [ID 1318528.1]
UDE-31623 Error With DataPump Export [ID 1080775.1]
Bug 9896536: MEMORY LEAKE OCCURS IN STREAM SPOOL WHEN EXPDP IS EXECUTED.

*** 2012-07-16 13:34:14.231
found process 0x1bd12952b0 pid=403 serial=16 ospid = 15038 dead

*** 2012-07-16 13:34:19.445
kssxdl: error deleting SO: 0x1bd98ba410 = transaction (55)
  ----------------------------------------
  SO: 0x1bd98ba410, type: 55, owner: 0x1b015a1100, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
   proc=0x1bd12952b0, name=transaction, file=ktccts.h LINE:407, pg=0
ORA-04031: unable to allocate 56 bytes of shared memory ("streams pool","unknown object","streams pool","fixed allocation callback")

*** 2012-07-16 13:34:29.456
found process 0x1bd12952b0 pid=403 serial=16 ospid = 15038 dead

*** 2012-07-16 13:34:34.713
kssxdl: error deleting SO: 0x1bd98ba410 = transaction (55)
  ----------------------------------------
  SO: 0x1bd98ba410, type: 55, owner: 0x1b015a1100, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
   proc=0x1bd12952b0, name=transaction, file=ktccts.h LINE:407, pg=0
ORA-04031: unable to allocate 56 bytes of shared memory ("streams pool","unknown object","streams pool","fixed allocation callback")

*** 2012-07-16 13:34:44.732
found process 0x1bd12952b0 pid=403 serial=16 ospid = 15038 dead
ORA-04031: unable to allocate 56 bytes of shared memory ("streams pool","unknown object","streams pool","fixed allocation callback")
Mon Jul 16 13:49:03 2012
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /data/oracle/diag/rdbms/std/edw1/trace/edw1_pmon_14903.trc:
ORA-04031: unable to allocate 56 bytes of shared memory ("streams pool","unknown object","streams pool","fixed allocation callback")
Mon Jul 16 13:49:18 2012
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /data/oracle/diag/rdbms/std/edw1/trace/edw1_pmon_14903.trc:
ORA-04031: unable to allocate 56 bytes of shared memory ("streams pool","unknown object","streams pool","fixed allocation callback")
Mon Jul 16 13:49:34 2012
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file /data/oracle/diag/rdbms/std/edw1/trace/edw1_pmon_14903.trc:
ORA-04031: unable to allocate 56 bytes of shared memory ("streams pool","unknown object","streams pool","fixed allocation callback")

Scripts: Monitor smon rollback progress

July 7, 2012 maintain, oracle No comments

this scripts is used to monitor transaction rolling back progress


——————————————————————————-

— Script: rolling_back.sql
— Purpose: to predict when transactions will finish rolling back
— For: 9.0+

— Copyright: (c) Ixora Pty Ltd
— Author: Steve Adams

——————————————————————————-
spool save_sqlplus_settings

set serveroutput on
set feedback off
prompt
prompt Looking for transactions that are rolling back …
prompt

declare
cursor tx is
select
s.username,
t.xidusn,
t.xidslot,
t.xidsqn,
x.ktuxesiz
from
sys.x$ktuxe x,
sys.v$transaction t,
sys.v$session s
where
x.inst_id = userenv(‘Instance’) and
x.ktuxesta = ‘ACTIVE’ and
x.ktuxesiz > 1 and
t.xidusn = x.ktuxeusn and
t.xidslot = x.ktuxeslt and
t.xidsqn = x.ktuxesqn and
s.saddr = t.ses_addr;
user_name varchar2(30);
xid_usn number;
xid_slot number;
xid_sqn number;
used_ublk1 number;
used_ublk2 number;
begin
open tx;
loop
fetch tx into user_name, xid_usn, xid_slot, xid_sqn, used_ublk1;
exit when tx%notfound;
if tx%rowcount = 1
then
sys.dbms_lock.sleep(10);
end if;
select
sum(ktuxesiz)
into
used_ublk2
from
sys.x$ktuxe
where
inst_id = userenv(‘Instance’) and
ktuxeusn = xid_usn and
ktuxeslt = xid_slot and
ktuxesqn = xid_sqn and
ktuxesta = ‘ACTIVE’;
if used_ublk2 < used_ublk1 then sys.dbms_output.put_line( user_name || '''s transaction ' || xid_usn || '.' || xid_slot || '.' || xid_sqn || ' will finish rolling back at approximately ' || to_char( sysdate + used_ublk2 / (used_ublk1 - used_ublk2) / 6 / 60 / 24, 'HH24:MI:SS DD-MON-YYYY' ) ); end if; end loop; if user_name is null then sys.dbms_output.put_line('No transactions appear to be rolling back.'); end if; end; / prompt exit;

eg:


SQL*Plus: Release 11.2.0.3.0 Production on Sat Jul 7 14:54:02 2012

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @rollback

Looking for transactions that are rolling back …

SYS’s transaction 10.3.25785 will finish rolling back at approximately 14:55:52
07-JUL-2012


SQL> begin
2 for i in 1..1000000 loop
3 insert into t1 values (i);
4 end loop;
5 end;
6 /

PL/SQL procedure successfully completed.

SQL> rollback
2 ;

Rollback complete.

SQL> !date
Sat Jul 7 14:55:59 CST 2012

非常准确的时间估算 cool scripts 🙂

Scripts: check user all granted privileges

June 14, 2012 maintain, oracle No comments

this scripts is used to check user all privileges

set serveroutput on;
DECLARE
v_user VARCHAR2(30) := ‘XXX’;
v_ddl VARCHAR2(2000);
v_status VARCHAR2(32);
BEGIN
— Need to add the following to get the lines to end with semi-colons

dbms_metadata.set_transform_param(dbms_metadata.session_transform,’SQLTERMINATOR’,true);

select dbms_metadata.get_ddl(‘USER’,v_user) INTO v_ddl from dual;
dbms_output.put_line(v_ddl);

— Get the user’s granted quotas
DECLARE
v_quota VARCHAR2(2000);
BEGIN
select dbms_metadata.get_granted_ddl(‘TABLESPACE_QUOTA’,v_user)
INTO v_quota from dual;
dbms_output.put_line(v_quota);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(‘– No quotas granted’);
END;

–Get the user’s granted roles
DECLARE
v_role VARCHAR2(2000);
BEGIN
select dbms_metadata.get_granted_ddl(‘ROLE_GRANT’,v_user) INTO
v_role from dual;
dbms_output.put_line(v_role);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(‘– No roles granted’);
END;

— Get the user’s system grants
DECLARE
v_system VARCHAR2(2000);
BEGIN
select dbms_metadata.get_granted_ddl(‘SYSTEM_GRANT’,v_user) INTO
v_system from dual;
dbms_output.put_line(v_system);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(‘– NO system grants’);
END;

— Get the user’s object grants
DECLARE
v_object VARCHAR2(30000);
BEGIN
select dbms_metadata.get_granted_ddl(‘OBJECT_GRANT’,v_user) INTO
v_object from dual;
dbms_output.put_line(v_object);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(‘– NO object grants’);
END;

SELECT account_status INTO v_status FROM dba_users WHERE username =
v_user;

IF(v_status = ‘OPEN’) THEN
dbms_output.put_line(‘ALTER USER ‘||v_user||’ ACCOUNT UNLOCK’);
END IF;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(‘– User not found’);
END;
/

eg:

SQL> set serveroutput on;
SQL> DECLARE
v_user VARCHAR2(30) := ‘LIU’;
v_ddl VARCHAR2(2000);
v_status VARCHAR2(32);
BEGIN
— Need to add the following to get the lines to end with semi-colons

dbms_metadata.set_transform_param(dbms_metadata.session_transform,’SQLTERMINATOR’,true);

select dbms_metadata.get_ddl(‘USER’,v_user) INTO v_ddl from dual;
dbms_output.put_line(v_ddl);

— Get the user’s granted quotas
DECLARE
v_quota VARCHAR2(2000);
BEGIN
select dbms_metadata.get_granted_ddl(‘TABLESPACE_QUOTA’,v_user)
INTO v_quota from dual;
dbms_output.put_line(v_quota);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(‘– No quotas granted’);
END;

–Get the user’s granted roles
DECLARE
v_role VARCHAR2(2000);
BEGIN
select dbms_metadata.get_granted_ddl(‘ROLE_GRANT’,v_user) INTO
v_role from dual;
dbms_output.put_line(v_role);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(‘– No roles granted’);
END;

— Get the user’s system grants
DECLARE
v_system VARCHAR2(2000);
BEGIN
select dbms_metadata.get_granted_ddl(‘SYSTEM_GRANT’,v_user) INTO
v_system from dual;
dbms_output.put_line(v_system);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(‘– NO system grants’);
END;

— Get the user’s object grants
DECLARE
v_object VARCHAR2(2000);
BEGIN
select dbms_metadata.get_granted_ddl(‘OBJECT_GRANT’,v_user) INTO
v_object from dual;
dbms_output.put_line(v_object);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(‘– NO object grants’);
END;

SELECT account_status INTO v_status FROM dba_users WHERE username =
v_user;

IF(v_status = ‘OPEN’) THEN
dbms_output.put_line(‘ALTER USER ‘||v_user||’ ACCOUNT UNLOCK’);
END IF;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(‘– User not found’);
END;
/

CREATE USER “LIU” IDENTIFIED BY VALUES
‘S:416DF25746736369B087A8388B7178AF33DA895C7F7DC8D997455C1BF0E9;9E1A31EC43A0799C

DEFAULT TABLESPACE “UDATA”
TEMPORARY TABLESPACE “TEMP”;
— No quotas granted

GRANT “DBA” TO “LIU”;

GRANT UNLIMITED TABLESPACE TO “LIU”;

GRANT EXECUTE ON “SYS”.”DBMS_SYSTEM” TO “LIU”;
GRANT DELETE ON
“TEST2″.”COUPON” TO “LIU”;
GRANT SELECT ON “TEST2″.”COUPON” TO “LIU”;
GRANT
SELECT ON “SYS”.”T” TO “LIU”;
ALTER USER LIU ACCOUNT UNLOCK

PL/SQL procedure successfully completed.