Category Experience

  • 首页   /  Oracle
  • 分类归档： "Experience"
    • ( 页面2 )

记录一次在IBM P750小机上给Oracle动态扩展存储

留下评论 ,

           本文详细描述一次在IBM P750的小机上动态给Oracle数据库扩展存储空间的操作。

           背景描述：一套2台IBM P750的小机通过HACMP做的HA，上面跑的是Oracle 11gR2的单实例数据库，除小机自带两块300G本地存储之外，共享存储采用的是IBM DS 5100，做RAID 10之后，可用空间2.1TB。目前该机器上有两个VG：rootvg，datavg。其中，rootvg存放AIX操作系统，由本机自带两块盘提供物理卷，datavg给oracle数据库用，物理卷是阵列上的磁盘。其中，datavg下的逻辑卷/oradata用于存放数据库的数据文件、联机日志文件、控制文件等；/oraflash主要用于存放归档日志和RMAN备份。

           1 添加之前，查看当前文件系统使用信息：

$ df -g
Filesystem    GB blocks      Free %Used    Iused %Iused Mounted on
/dev/hd4           1.00      0.78   23%    10542     6% /
/dev/hd2          10.00      7.48   26%    52002     3% /usr
/dev/hd9var        5.00      4.45   12%     8742     1% /var
/dev/hd3          10.00      7.22   28%      729     1% /tmp
/dev/hd1           0.50      0.49    2%      135     1% /home
/dev/hd11admin      0.50      0.50    1%        5     1% /admin
/proc                 -         -    -         -     -  /proc
/dev/hd10opt       0.50      0.23   55%    10267    16% /opt
/dev/livedump      0.50      0.50    1%        4     1% /var/adm/ras/livedump
/dev/oracle      100.00     80.71   20%   357020     2% /u01
/dev/oradata     500.00    135.74   73%       39     1% /oradata
/dev/oraflash    500.00     58.62   89%     1186     1% /oraflash
$

           从上看到，挂载在/oraflash下的/dev/oraflash文件系统大小是500G，可用空间剩余58G，挂载在/oradata下的/dev/oradata文件系统大小是500G，剩余空间是135G，因业务量迅速增长，现需要扩充存储空间。接下来准备扩充文件系统/oradata和/oraflash，准备分别扩充300G。     

           2 扩之前，查看VG信息：

$ lsvg -o
datavg
rootvg
$

           看到，当前varyon的卷组是rootvg和datavg。

           3 查看datavg的物理卷信息：

$ lsvg -p datavg
datavg:
PV_NAME           PV STATE          TOTAL PPs   FREE PPs    FREE DISTRIBUTION
hdisk2            active            1599        0           00..00..00..00..00
hdisk3            active            1599        0           00..00..00..00..00
hdisk4            active            1199        397         00..00..00..157..240
hdisk5            active            1599        1598        320..319..319..320..320
hdisk6            active            1599        1599        320..320..319..320..320
hdisk7            active            1599        0           00..00..00..00..00
hdisk8            active            1599        0           00..00..00..00..00
hdisk9            active            1599        797         157..00..00..320..320
hdisk10           active            1599        1599        320..320..319..320..320
hdisk11           active            1599        1599        320..320..319..320..320
$

             4 查看datavg下的逻辑卷信息：

$ lsvg -l datavg
datavg:
LV NAME             TYPE       LPs     PPs     PVs  LV STATE      MOUNT POINT
oradata             jfs2       4000    4000    3    open/syncd    /oradata
loglv01             jfs2log    1       1       1    open/syncd    N/A
oraflash            jfs2       4000    4000    3    open/syncd    /oraflash
$

              从上，看到oradata，oraflash两个逻辑卷都位于datavg卷组下。

              5 接下来，查看datavg的详细信息：

$ lsvg datavg
VOLUME GROUP:       datavg                   VG IDENTIFIER:  00f64a5100004c000000012d5e49cf72
VG STATE:           active                   PP SIZE:        128 megabyte(s)
VG PERMISSION:      read/write               TOTAL PPs:      15590 (1995520 megabytes)
MAX LVs:            256                      FREE PPs:       7589 (971392 megabytes)
LVs:                3                        USED PPs:       8001 (1024128 megabytes)
OPEN LVs:           3                        QUORUM:         6 (Enabled)
TOTAL PVs:          10                       VG DESCRIPTORS: 10
STALE PVs:          0                        STALE PPs:      0
ACTIVE PVs:         10                       AUTO ON:        no
MAX PPs per VG:     32768                    MAX PVs:        1024
LTG size (Dynamic): 256 kilobyte(s)          AUTO SYNC:      no
HOT SPARE:          no                       BB POLICY:      relocatable
PV RESTRICTION:     none
$

           从上看到，datavg卷组的PP size=128M，Totol PPs=15590，意味着卷组的总大小=128*15590M=1948G，已用8001个PPs(1000G)，可用PPs 7589个(948G)。说明，卷组上还有空间可供使用。

           6 查看逻辑卷oraflash的信息：

$ lslv oraflash
LOGICAL VOLUME:     oraflash               VOLUME GROUP:   datavg
LV IDENTIFIER:      00f64a5100004c000000012d5e49cf72.3 PERMISSION:     read/write
VG STATE:           active/complete        LV STATE:       opened/syncd
TYPE:               jfs2                   WRITE VERIFY:   off
MAX LPs:            4000                   PP SIZE:        128 megabyte(s)
COPIES:             1                      SCHED POLICY:   parallel
LPs:                4000                   PPs:            4000
STALE PPs:          0                      BB POLICY:      relocatable
INTER-POLICY:       minimum                RELOCATABLE:    yes
INTRA-POLICY:       middle                 UPPER BOUND:    1024
MOUNT POINT:        /oraflash              LABEL:          /oraflash
MIRROR WRITE CONSISTENCY: on/ACTIVE
EACH LP COPY ON A SEPARATE PV ?: yes
Serialize IO ?:     NO
DEVICESUBTYPE : DS_LVZ
COPY 1 MIRROR POOL: None
COPY 2 MIRROR POOL: None
COPY 3 MIRROR POOL: None
$

             这里，看到oraflash逻辑卷MAX LPs、LPs、PPs都是4000，说明如果要直接扩充文件系统的话，扩充之后的文件系统Max LPs不能超过4000，否则就得先扩逻辑卷oraflash了。oraflash文件系统类型是jfs2。

             7 顺便查看oraflash占用哪几个物理卷的信息：

$ lslv -l oraflash
oraflash:/oraflash
PV                COPIES        IN BAND       DISTRIBUTION
hdisk7            1599:000:000  20%           320:320:319:320:320
hdisk8            1599:000:000  20%           320:320:319:320:320
hdisk9            802:000:000   39%           163:320:319:000:000
$

             8 尝试直接使用smitty直接扩充oraflash文件系统，尝试增加100G，即从现有的500G扩充到600G：

             从上看到，oraflash文件系统类型是jfs2，直接以root执行smitty chjfs2命令，进入smitty界面:

 

      然后，选择/oraflash,回车，进入下一操作界面:

 

      将Unit Size选择为G，Number of units输入600，表示600G大小，即该文件系统的扩充目标大小。回车，进入下图：

             

               发现报错，提示逻辑卷oraflash的超出最大4000个Lps。扩充失败。看来得先修改逻辑卷oraflash的属性了。

               9   接下来，先修改逻辑卷oraflash的最大Lps，这里准备增加2400个，从目前的4000个Lps增加到6400个。2400*128=300G，这个需要事先计算好。

                    root用户执行smitty chlv,进入下述操作界面：

         选择第一项，Change a Logical Volume，然后选择对应的oraflash,如下图：

 

        回车，进入下一界面：

 

        然后，修改MAXIMUM NUMBER of LOGICAL PARTITIONS值为6400，改完之后，直接回车，进入下一界面：

       提示成功，状态OK。执行Esc+0退出。

            10  再次对/oraflash文件系统进行扩充。依次以root执行smitty chjfs2命令，选择/oraflash文件系统，同样将Unit Size选择为G，Number of units输入800，表示800G大小，即该文件系统的扩充目标大小。这里，因为上述我们已经将逻辑卷oraflash的最大Lps，增加2400个Lps，2400Lps*128M/Lps=300G，所以我们的目标大小是800G。如下图所示：

               

                 修改之后，直接回车。发现最后扩充成功了。

                 如法炮制，通过smit chlv修改oradata逻辑卷的MAX LPs为6400个之后，然后，执行smit chjfs2选择修改/oradata文件系统，扩展到800G。

                 最终，修改之后，文件系统使用信息如下：

# df -g
Filesystem    GB blocks      Free %Used    Iused %Iused Mounted on
/dev/hd4           1.00      0.78   23%    10542     6% /
/dev/hd2          10.00      7.48   26%    52002     3% /usr
/dev/hd9var        5.00      4.45   12%     8742     1% /var
/dev/hd3          10.00      7.22   28%      729     1% /tmp
/dev/hd1           0.50      0.49    2%      135     1% /home
/dev/hd11admin      0.50      0.50    1%        5     1% /admin
/proc                 -         -    -         -     -  /proc
/dev/hd10opt       0.50      0.23   55%    10267    16% /opt
/dev/livedump      0.50      0.50    1%        4     1% /var/adm/ras/livedump
/dev/oracle      100.00     80.71   20%   357030     2% /u01
/dev/oradata     800.00    435.70   46%       39     1% /oradata
/dev/oraflash    800.00    358.54   56%     1187     1% /oraflash
#

                  看到/oradata和/oraflash已从500G扩充到800G。同时，看到datavg的FREE PPS变少了，从之前的7589减少到2789,减少了7589-2789=4800个，正好是分别往oradata和oraflash上加的2400个：

# lsvg datavg
VOLUME GROUP:       datavg                   VG IDENTIFIER:  00f64a5100004c000000012d5e49cf72
VG STATE:           active                   PP SIZE:        128 megabyte(s)
VG PERMISSION:      read/write               TOTAL PPs:      15590 (1995520 megabytes)
MAX LVs:            256                      FREE PPs:       2789 (356992 megabytes)
LVs:                3                        USED PPs:       12801 (1638528 megabytes)
OPEN LVs:           3                        QUORUM:         6 (Enabled)
TOTAL PVs:          10                       VG DESCRIPTORS: 10
STALE PVs:          0                        STALE PPs:      0
ACTIVE PVs:         10                       AUTO ON:        no
MAX PPs per VG:     32768                    MAX PVs:        1024
LTG size (Dynamic): 256 kilobyte(s)          AUTO SYNC:      no
HOT SPARE:          no                       BB POLICY:      relocatable
PV RESTRICTION:     none
#

                  并且，oraflash和oradata的PPs都从4000增加到6400：

# lsvg -l datavg
datavg:
LV NAME             TYPE       LPs     PPs     PVs  LV STATE      MOUNT POINT
oradata             jfs2       6400    6400    5    open/syncd    /oradata
loglv01             jfs2log    1       1       1    open/syncd    N/A
oraflash            jfs2       6400    6400    5    open/syncd    /oraflash
#

                    而此时，oraflash、oradata占用物理卷信息也改变了：

# lslv -l oradata
oradata:/oradata
PV                COPIES        IN BAND       DISTRIBUTION
hdisk2            1599:000:000  20%           320:320:319:320:320
hdisk3            1599:000:000  20%           320:320:319:320:320
hdisk4            1199:000:000  20%           240:240:239:240:240
hdisk11           1599:000:000  20%           320:320:319:320:320
hdisk5            404:000:000   78%           000:319:085:000:000
# lslv -l oraflash
oraflash:/oraflash
PV                COPIES        IN BAND       DISTRIBUTION
hdisk7            1599:000:000  20%           320:320:319:320:320
hdisk8            1599:000:000  20%           320:320:319:320:320
hdisk9            1599:000:000  20%           320:320:319:320:320
hdisk6            1599:000:000  20%           320:320:319:320:320
hdisk10           004:000:000   100%          000:004:000:000:000
#

                  最终，完成在IBM P750小机上在Oracle数据库正常运行的前提下，动态给Oracle添加存储。

如何获取Oracle数据库中某表及索引、约束、触发器、对象权限的创建语句？

留下评论 , ,

            在上一篇文章中，我们知道了如何获取Oracle数据库中某个用户下的所有表、索引、约束、触发器的创建脚本？那么对于数据库中特定的某张表而言，又该如何获取建表语句、及索引、约束、外键约束、触发器、对象权限的创建脚本呢？

            在这里，给出一个实现上述功能的脚本：

OCM11g-> cat table_ddl.sql 
-- Be sure the variable 'S' means the Schema name,the 'T' means table name.
-- All these variables should be UPPERCASE.

def S='&1'
def T='&2'
set lines 2000 pages 0 ver off echo off head off feed off
set newpage none
set trimspool on
set long 5000000
col output for a1000 word_wrapped
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',true);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'STORAGE',false);
 
spool &T..sql
select dbms_metadata.get_ddl('TABLE','&T','&S') output from dual;
select dbms_metadata.get_dependent_ddl('INDEX','&T','&S') output from dual;
select dbms_metadata.get_dependent_ddl('CONSTRAINT','&T','&S') output from dual;
select dbms_metadata.get_dependent_ddl('REF_CONSTRAINT','&T','&S') output from dual;
select dbms_metadata.get_dependent_ddl('TRIGGER','&T','&S') output from dual;
 
-- Uncomment to generate object level grants
 select dbms_metadata.get_dependent_ddl('OBJECT_GRANT','&T','&S') output from dual;
 
spool off;
OCM11g->

            下述是对于HR用户下的EMPLOYEES表的输出结果：

SQL> conn hr/hR
Connected.
SQL> @table_ddl.sql
Enter value for 1: HR
Enter value for 2: EMPLOYEES

  CREATE TABLE "HR"."EMPLOYEES"
   (    "EMPLOYEE_ID" NUMBER(6,0),
        "FIRST_NAME" VARCHAR2(20),
        "LAST_NAME" VARCHAR2(25) CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL ENABLE,
        "EMAIL" VARCHAR2(25) CONSTRAINT "EMP_EMAIL_NN" NOT NULL ENABLE,
        "PHONE_NUMBER" VARCHAR2(20),
        "HIRE_DATE" DATE CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL ENABLE,
        "JOB_ID" VARCHAR2(10) CONSTRAINT "EMP_JOB_NN" NOT NULL ENABLE,
        "SALARY" NUMBER(8,2),
        "COMMISSION_PCT" NUMBER(2,2),
        "MANAGER_ID" NUMBER(6,0),
        "DEPARTMENT_ID" NUMBER(4,0),
         CONSTRAINT "EMP_SALARY_MIN" CHECK (salary > 0) ENABLE,
         CONSTRAINT "EMP_EMAIL_UK" UNIQUE ("EMAIL")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
  TABLESPACE "EXAMPLE"  ENABLE,
         CONSTRAINT "EMP_EMP_ID_PK" PRIMARY KEY ("EMPLOYEE_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
  TABLESPACE "EXAMPLE"  ENABLE,
         CONSTRAINT "EMP_MANAGER_FK" FOREIGN KEY ("MANAGER_ID")
          REFERENCES "HR"."EMPLOYEES" ("EMPLOYEE_ID") ENABLE,
         CONSTRAINT "EMP_JOB_FK" FOREIGN KEY ("JOB_ID")
          REFERENCES "HR"."JOBS" ("JOB_ID") ENABLE,
         CONSTRAINT "EMP_DEPT_FK" FOREIGN KEY ("DEPARTMENT_ID")
          REFERENCES "HR"."DEPARTMENTS" ("DEPARTMENT_ID") ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
  TABLESPACE "EXAMPLE" ;



  CREATE UNIQUE INDEX "HR"."EMP_EMAIL_UK" ON "HR"."EMPLOYEES" ("EMAIL")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
  TABLESPACE "EXAMPLE" ;

  CREATE UNIQUE INDEX "HR"."EMP_EMP_ID_PK" ON "HR"."EMPLOYEES" ("EMPLOYEE_ID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
  TABLESPACE "EXAMPLE" ;

  CREATE INDEX "HR"."EMP_DEPARTMENT_IX" ON "HR"."EMPLOYEES" ("DEPARTMENT_ID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
  TABLESPACE "EXAMPLE" ;

  CREATE INDEX "HR"."EMP_JOB_IX" ON "HR"."EMPLOYEES" ("JOB_ID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
  TABLESPACE "EXAMPLE" ;

  CREATE INDEX "HR"."EMP_MANAGER_IX" ON "HR"."EMPLOYEES" ("MANAGER_ID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
  TABLESPACE "EXAMPLE" ;

  CREATE INDEX "HR"."EMP_NAME_IX" ON "HR"."EMPLOYEES" ("LAST_NAME", "FIRST_NAME")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
  TABLESPACE "EXAMPLE" ;



  ALTER TABLE "HR"."EMPLOYEES" MODIFY ("EMAIL" CONSTRAINT "EMP_EMAIL_NN" NOT NULL ENABLE);

  ALTER TABLE "HR"."EMPLOYEES" ADD CONSTRAINT "EMP_EMAIL_UK" UNIQUE ("EMAIL")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
  TABLESPACE "EXAMPLE"  ENABLE;

  ALTER TABLE "HR"."EMPLOYEES" ADD CONSTRAINT "EMP_EMP_ID_PK" PRIMARY KEY ("EMPLOYEE_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
  TABLESPACE "EXAMPLE"  ENABLE;

  ALTER TABLE "HR"."EMPLOYEES" MODIFY ("HIRE_DATE" CONSTRAINT "EMP_HIRE_DATE_NN" NOT NULL ENABLE);

  ALTER TABLE "HR"."EMPLOYEES" MODIFY ("JOB_ID" CONSTRAINT "EMP_JOB_NN" NOT NULL ENABLE);

  ALTER TABLE "HR"."EMPLOYEES" MODIFY ("LAST_NAME" CONSTRAINT "EMP_LAST_NAME_NN" NOT NULL ENABLE);

  ALTER TABLE "HR"."EMPLOYEES" ADD CONSTRAINT "EMP_SALARY_MIN" CHECK (salary > 0) ENABLE;



  ALTER TABLE "HR"."EMPLOYEES" ADD CONSTRAINT "EMP_DEPT_FK" FOREIGN KEY ("DEPARTMENT_ID")
          REFERENCES "HR"."DEPARTMENTS" ("DEPARTMENT_ID") ENABLE;

  ALTER TABLE "HR"."EMPLOYEES" ADD CONSTRAINT "EMP_JOB_FK" FOREIGN KEY ("JOB_ID")
          REFERENCES "HR"."JOBS" ("JOB_ID") ENABLE;

  ALTER TABLE "HR"."EMPLOYEES" ADD CONSTRAINT "EMP_MANAGER_FK" FOREIGN KEY ("MANAGER_ID")
          REFERENCES "HR"."EMPLOYEES" ("EMPLOYEE_ID") ENABLE;



  CREATE OR REPLACE TRIGGER "HR"."SECURE_EMPLOYEES"
  BEFORE INSERT OR UPDATE OR DELETE ON employees
BEGIN
  secure_dml;
END secure_employees;
/
ALTER TRIGGER "HR"."SECURE_EMPLOYEES" DISABLE;

  CREATE OR REPLACE TRIGGER "HR"."UPDATE_JOB_HISTORY"
  AFTER UPDATE OF job_id, department_id ON employees
  FOR EACH ROW
BEGIN
  add_job_history(:old.employee_id, :old.hire_date, sysdate,
                  :old.job_id, :old.department_id);
END;
/
ALTER TRIGGER "HR"."UPDATE_JOB_HISTORY" ENABLE;



  GRANT SELECT, REFERENCES ON "HR"."EMPLOYEES" TO "OE";


SQL>

如何获取Oracle数据库中某个用户下的所有表、索引、约束、触发器的创建脚本？

留下评论 ,

在Oracle数据库中，如何快速获取某个用户下的所有表、索引、约束及触发器的创建SQL语句呢？本文给出一个简单的示例脚本，经过测试，除位于回收站下的对象及只读视图的约束语句不能获取，其它对象基本均可获取CREATE语句。

脚本内容如下：

[oracle@oracledg ~]$ cat get_ddl.sql 
-- Be sure the variable 'S' means the Schema name,it should be UPPERCASE.
-- And this script COULDNOT get the constraints CREATE DDL,which CONSTRAINT_TYPE is 'O',cause it's READ ONLY on a view.
-- Author Info:Asher Huang
-- http://OracleOnlinux.cn
-- Mail:3dian14@gmail.com

def S='&Schema'
set serveroutput on
set long 20000
set lines 2000 pages 0 ver off echo off head off feed off
set newpage none
set trimspool on
set long 5000000
spool &S..sql 
DECLARE
  CURSOR C1 IS
    SELECT '&S' AS u_name, table_name AS tab_name FROM user_tables;
  CURSOR C2 IS
    SELECT '&S' AS u_name, index_name AS idx_name FROM user_indexes;
  CURSOR C3 IS
    SELECT '&S' AS u_name, constraint_name AS cons_name FROM user_constraints where constraint_type not in('R','O');
  CURSOR C4 IS
    SELECT '&S' AS u_name, table_name AS tab_name FROM user_constraints where constraint_type in('R');
  CURSOR C5 IS
    SELECT '&S' AS u_name, trigger_name AS trig_name FROM user_triggers;
  V_MSG CLOB;
BEGIN
      DBMS_OUTPUT.put_line('The CREATE DDL for schema &S''s Tables are:');
  FOR x IN C1 LOOP
      select dbms_metadata.get_ddl('TABLE', x.tab_name, x.u_name) INTO V_MSG from dual;
      DBMS_OUTPUT.put_line(V_MSG);
  END LOOP;

      DBMS_OUTPUT.put_line('The CREATE DDL for schema &S''s Indexes are:');
  FOR x IN C2 LOOP
      select dbms_metadata.get_ddl('INDEX', x.idx_name, x.u_name) INTO V_MSG from dual;
      DBMS_OUTPUT.put_line(V_MSG);
  END LOOP;

      DBMS_OUTPUT.put_line('The CREATE DDL for schema &S''s Constraints are:');
  FOR x IN C3 LOOP
      select dbms_metadata.get_ddl('CONSTRAINT', x.cons_name, x.u_name) INTO V_MSG from dual;
      DBMS_OUTPUT.put_line(V_MSG);
  END LOOP;

      DBMS_OUTPUT.put_line('The CREATE DDL for schema &S''s Reference Constraints are:');
  FOR x IN C4 LOOP
      select dbms_metadata.get_dependent_ddl('REF_CONSTRAINT', x.tab_name, x.u_name) INTO V_MSG from dual;
      DBMS_OUTPUT.put_line(V_MSG);
  END LOOP;

      DBMS_OUTPUT.put_line('The CREATE DDL for schema &S''s Triggers are:');
  FOR x IN C5 LOOP
      select dbms_metadata.get_ddl('TRIGGER', x.trig_name, x.u_name) INTO V_MSG from dual;
      DBMS_OUTPUT.put_line(V_MSG);
  END LOOP;
END;
/
spool off;
[oracle@oracledg ~]$

测试获取SCOTT用户的所有表、索引、约束、触发器创建脚本如下：

[oracle@oracledg ~]$ cat SCOTT.sql 
The CREATE DDL for schema SCOTT's Tables are:

  CREATE TABLE "SCOTT"."DEPT" 
   (    "DEPTNO" NUMBER(2,0), 
        "DNAME" VARCHAR2(14), 
        "LOC" VARCHAR2(13), 
         CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"  ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"


  CREATE TABLE "SCOTT"."EMP" 
   (    "EMPNO" NUMBER(4,0), 
        "ENAME" VARCHAR2(10), 
        "JOB" VARCHAR2(9), 
        "MGR" NUMBER(4,0), 
        "HIREDATE" DATE, 
        "SAL" NUMBER(7,2), 
        "COMM" NUMBER(7,2), 
        "DEPTNO" NUMBER(2,0), 
         CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"  ENABLE, 
         CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
          REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"


  CREATE TABLE "SCOTT"."BONUS" 
   (    "ENAME" VARCHAR2(10), 
        "JOB" VARCHAR2(9), 
        "SAL" NUMBER, 
        "COMM" NUMBER
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"


  CREATE TABLE "SCOTT"."SALGRADE" 
   (    "GRADE" NUMBER, 
        "LOSAL" NUMBER, 
        "HISAL" NUMBER
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"

The CREATE DDL for schema SCOTT's Indexes are:

  CREATE UNIQUE INDEX "SCOTT"."PK_EMP" ON "SCOTT"."EMP" ("EMPNO") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"


  CREATE UNIQUE INDEX "SCOTT"."PK_DEPT" ON "SCOTT"."DEPT" ("DEPTNO") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"

The CREATE DDL for schema SCOTT's Constraints are:

  ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"  ENABLE


  ALTER TABLE "SCOTT"."DEPT" ADD CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"  ENABLE

The CREATE DDL for schema SCOTT's Reference Constraints are:

  ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
          REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE

The CREATE DDL for schema SCOTT's Triggers are:
[oracle@oracledg ~]$

奇怪的ORA-04043错误及解决方法

留下评论 ,

           在查询数据字典表，DBA_DATA_FILES时，遇见一则有趣的ORA-04043错误，错误重现及解决办法见下述：

           1数据库版本，正常情况下，可以查看DBA_DATA_FILES数据字典表：

SQL> startup
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2215944 bytes
Variable Size             398462968 bytes
Database Buffers          218103808 bytes
Redo Buffers                7544832 bytes
Database mounted.
Database opened.
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> desc dba_data_files;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 FILE_NAME                                          VARCHAR2(513)
 FILE_ID                                            NUMBER
 TABLESPACE_NAME                                    VARCHAR2(30)
 BYTES                                              NUMBER
 BLOCKS                                             NUMBER
 STATUS                                             VARCHAR2(9)
 RELATIVE_FNO                                       NUMBER
 AUTOEXTENSIBLE                                     VARCHAR2(3)
 MAXBYTES                                           NUMBER
 MAXBLOCKS                                          NUMBER
 INCREMENT_BY                                       NUMBER
 USER_BYTES                                         NUMBER
 USER_BLOCKS                                        NUMBER
 ONLINE_STATUS                                      VARCHAR2(7)

SQL>

           2重现错误：

SQL> startup mount;
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2215944 bytes
Variable Size             398462968 bytes
Database Buffers          218103808 bytes
Redo Buffers                7544832 bytes
Database mounted.
SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

SQL> desc dba_data_files;
ERROR:
ORA-04043: object dba_data_files does not exist


SQL> alter database open;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL> desc dba_data_files;
ERROR:
ORA-04043: object dba_data_files does not exist


SQL>

           3解决办法：

SQL> show user;
USER is "SYS"
SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL> desc dba_data_files;
ERROR:
ORA-04043: object dba_data_files does not exist


SQL> alter system flush shared_pool;

System altered.

SQL> desc dba_data_files;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 FILE_NAME                                          VARCHAR2(513)
 FILE_ID                                            NUMBER
 TABLESPACE_NAME                                    VARCHAR2(30)
 BYTES                                              NUMBER
 BLOCKS                                             NUMBER
 STATUS                                             VARCHAR2(9)
 RELATIVE_FNO                                       NUMBER
 AUTOEXTENSIBLE                                     VARCHAR2(3)
 MAXBYTES                                           NUMBER
 MAXBLOCKS                                          NUMBER
 INCREMENT_BY                                       NUMBER
 USER_BYTES                                         NUMBER
 USER_BLOCKS                                        NUMBER
 ONLINE_STATUS                                      VARCHAR2(7)

SQL>

           或者，先shutdown，然后startup，也可以避免ORA-04043错误。

           描述：当数据库处于MOUNT状态时，如果试图去访问DBA_之类的数据字典表时，数据库会报出ORA-04043错误！即使，接下来，ALTER DATABASE OPEN，将数据库置于OPEN状态，再尝试去查看DBA_之类的数据字典表时，数据库依然会报出ORA-04043错误！解决的办法有两个：

  •           ① 重启数据库；
  •           ② 刷出共享池：ALTER SYSTEM FLUSH SHARED_POOL

          补充，经测试，在数据库MOUNT状态尝试去查看SYS用户的普通表的话，也会报出同样的错误，看来不单是数据字典表了，具体见下述：

SQL> startup mount;
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2215944 bytes
Variable Size             398462968 bytes
Database Buffers          218103808 bytes
Redo Buffers                7544832 bytes
Database mounted.
SQL> desc dba_data_files;
ERROR:
ORA-04043: object dba_data_files does not exist


SQL> desc test_04043
ERROR:
ORA-04043: object test_04043 does not exist


SQL> desc hr.t;
ERROR:
ORA-04043: object hr.t does not exist


SQL> alter database open;

Database altered.

SQL> desc dba_data_files;
ERROR:
ORA-04043: object dba_data_files does not exist


SQL> desc test_04043
ERROR:
ORA-04043: object test_04043 does not exist


SQL> desc hr.t;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER

SQL> alter system flush shared_pool;

System altered.

SQL> desc dba_data_files;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 FILE_NAME                                          VARCHAR2(513)
 FILE_ID                                            NUMBER
 TABLESPACE_NAME                                    VARCHAR2(30)
 BYTES                                              NUMBER
 BLOCKS                                             NUMBER
 STATUS                                             VARCHAR2(9)
 RELATIVE_FNO                                       NUMBER
 AUTOEXTENSIBLE                                     VARCHAR2(3)
 MAXBYTES                                           NUMBER
 MAXBLOCKS                                          NUMBER
 INCREMENT_BY                                       NUMBER
 USER_BYTES                                         NUMBER
 USER_BLOCKS                                        NUMBER
 ONLINE_STATUS                                      VARCHAR2(7)

SQL> desc test_04043
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 NAME                                               VARCHAR2(30)

SQL> desc hr.t;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER

SQL> select * from test_04043;

        ID NAME
---------- ------------------------------
         1 The F**king ORA-04043 Error.
         2 WWW.OracleOnLinux.CN

SQL>

                  从上，可以看出，在数据库MOUNT状态下，试图去查看数据字典表和SYS用户下的普通表，会报ORA-04043错误，将数据库OPEN之后，再去查看这些对象时，依然会报错，而普通用户的对象却不会出现这个错误！

                  究其原因，这是个Oracle的bug,Bug 2365821,可见Metalink上的ORA-4043 On DBA_* Views If They Are Described In Mount Stage [ID 296235.1]，受影响的数据库版本：Oracle Server – Enterprise Edition – Version: 8.1.7.4 to 11.2.0.1 – Release: 8.1.7 to 11.2，This problem can occur on any platform.

ORACLE_SID与ORACLE_HOME环境变量介绍及有趣的后台进程

留下评论

            对于在Unix/Linux平台下部署过Oracle数据库、或者熟悉Oracle的人来讲，或许对这两个环境变量并不陌生。

            通常情况下，ORACLE_SID这个环境变量全称Oracle System Identifier,，用于在一台服务器上标识不同的实例，默认情况下，实例名就是ORACLE_SID的值（即INSTANCE_NAME=$ORACLE_SID。当然实例名也可以不同于ORACLE_SID这个环境变量，只要在初始化参数文件里显示指定INSTANCE_NAME参数值不同于ORACLE_SID环境变量的值即可）。在UNIX/Linux平台下，该环境变量主要作用是同ORACLE_HOME这个环境变量做hash运算，得到一个唯一值，用来标识共享内存段，及SGA。下面，摘录一段TOM大师光辉著作《Expert Oracle Database Architecture 》的原文：

              If you’re unfamiliar with the term SID or  ORACLE_SID, a full definition is called for. The SID is a  site identifier . It and  ORACLE_HOME (where the Oracle software is installed) are hashed together in UNIX to create a unique key name for attaching an SGA. If your  ORACLE_SIDor  ORACLE_HOME is not set correctly, you’ll get the  ORACLE NOT AVAILABLE error, since you can’t attach to a shared memory segment that is identified by this unique key. On Windows, shared memory isn’t used in the same fashion as UNIX, but the SID is still important. You can have more than one database on the same ORACLE_HOME, so you need a way to uniquely identify each one, along with their configuration files.

            ORACLE_HOME环境变量是Oracle软件的安装路径，顺带ORACLE_BASE环境变量是Oracle软件安装的基目录。

            知道了ORACLE_SID和ORACLE_HOME这两个环境变量的作用后，我们来看下面的案例：

            首先，来看在参数文件里只用一个db_name参数来启动实例的情况，即默认情况下，INSTANCE_NAME=$ORACLE_SID的情形：

[root@localhost ~]# su - oracle
[oracle@localhost ~]$ env | grep ORA
ORACLE_SID=phydb10g
ORACLE_BASE=/u01/app
ORACLE_TERM=xterm
ORACLE_HOME=/u01/app/oracle
[oracle@localhost ~]$ ps -ef | grep ora_
oracle   17938 17904  0 15:15 pts/1    00:00:00 grep ora_
[oracle@localhost ~]$ cd $ORACLE_HOME/dbs
[oracle@localhost dbs]$ pwd  
/u01/app/oracle/dbs
[oracle@localhost dbs]$ cat initphydb10g.ora 
*.db_name='glndb'
[oracle@localhost dbs]$ sqlplus

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Mar 1 15:16:15 2012

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                  2094832 bytes
Variable Size             113248528 bytes
Database Buffers           50331648 bytes
Redo Buffers                6291456 bytes
ORA-00205: error in identifying control file, check alert log for more info


SQL> set line 160
SQL> show parameter instance_name;

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
instance_name                        string                            phydb10g
SQL> !ps -ef | grep ora_
oracle   17946     1  0 15:16 ?        00:00:00 ora_pmon_phydb10g
oracle   17948     1  0 15:16 ?        00:00:00 ora_psp0_phydb10g
oracle   17950     1  0 15:16 ?        00:00:00 ora_mman_phydb10g
oracle   17952     1  0 15:16 ?        00:00:00 ora_dbw0_phydb10g
oracle   17954     1  0 15:16 ?        00:00:00 ora_lgwr_phydb10g
oracle   17956     1  0 15:16 ?        00:00:00 ora_ckpt_phydb10g
oracle   17958     1  0 15:16 ?        00:00:00 ora_smon_phydb10g
oracle   17960     1  0 15:16 ?        00:00:00 ora_reco_phydb10g
oracle   17962     1  0 15:16 ?        00:00:00 ora_mmon_phydb10g
oracle   17964     1  0 15:16 ?        00:00:00 ora_mmnl_phydb10g
oracle   17966 17943  0 15:16 pts/2    00:00:00 /bin/bash -c ps -ef | grep ora_
oracle   17968 17966  0 15:16 pts/2    00:00:00 grep ora_

SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL>

            然后，来看INSTANCE_NAME<>$ORACLE_SID的情形：

[oracle@localhost ~]$ env | grep ORA
ORACLE_SID=phydb10g
ORACLE_BASE=/u01/app
ORACLE_TERM=xterm
ORACLE_HOME=/u01/app/oracle
[oracle@localhost ~]$ cat /u01/app/oracle/dbs/initphydb10g.ora 
*.db_name='glndb'
*.instance_name='inst10g'
[oracle@localhost ~]$ ps -ef | grep ora_
oracle   18033 17996  0 15:23 pts/1    00:00:00 grep ora_
[oracle@localhost ~]$ sqlplus

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Mar 1 15:23:19 2012

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                  2094832 bytes
Variable Size             113248528 bytes
Database Buffers           50331648 bytes
Redo Buffers                6291456 bytes
ORA-00205: error in identifying control file, check alert log for more info


SQL> set line 160
SQL> show parameter instance_name

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
instance_name                        string                            inst10g
SQL> !ps -ef | grep ora_
oracle   18040     1  0 15:23 ?        00:00:00 ora_pmon_phydb10g
oracle   18042     1  0 15:23 ?        00:00:00 ora_psp0_phydb10g
oracle   18044     1  0 15:23 ?        00:00:00 ora_mman_phydb10g
oracle   18046     1  0 15:23 ?        00:00:00 ora_dbw0_phydb10g
oracle   18048     1  0 15:23 ?        00:00:00 ora_lgwr_phydb10g
oracle   18050     1  0 15:23 ?        00:00:00 ora_ckpt_phydb10g
oracle   18052     1  0 15:23 ?        00:00:00 ora_smon_phydb10g
oracle   18054     1  0 15:23 ?        00:00:00 ora_reco_phydb10g
oracle   18056     1  0 15:23 ?        00:00:00 ora_mmon_phydb10g
oracle   18058     1  0 15:23 ?        00:00:00 ora_mmnl_phydb10g
oracle   18060 18037  0 15:23 pts/2    00:00:00 /bin/bash -c ps -ef | grep ora_
oracle   18062 18060  0 15:23 pts/2    00:00:00 grep ora_

SQL> shutdown immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL>

            其次，来看在同一个ORACLE_HOME下启动两个不同实例的情形，即ORACLE_HOME相同，ORACLE_SID不同：

[oracle@localhost ~]$ pwd
/home/oracle
[oracle@localhost ~]$ ps -ef | grep ora_
oracle   18187 18077  0 15:28 pts/1    00:00:00 grep ora_
[oracle@localhost ~]$ env | grep ORA
ORACLE_SID=phydb10g
ORACLE_BASE=/u01/app
ORACLE_TERM=xterm
ORACLE_HOME=/u01/app/oracle
[oracle@localhost ~]$ cat /u01/app/oracle/dbs/initphydb10g.ora 
*.db_name='glndb'
*.instance_name='inst10g'
[oracle@localhost ~]$ sqlplus

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Mar 1 15:29:41 2012

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                  2094832 bytes
Variable Size             113248528 bytes
Database Buffers           50331648 bytes
Redo Buffers                6291456 bytes
ORA-00205: error in identifying control file, check alert log for more info


SQL> !ps -ef | grep ora_
oracle   18199     1  0 15:29 ?        00:00:00 ora_pmon_phydb10g
oracle   18201     1  0 15:29 ?        00:00:00 ora_psp0_phydb10g
oracle   18203     1  0 15:29 ?        00:00:00 ora_mman_phydb10g
oracle   18205     1  0 15:29 ?        00:00:00 ora_dbw0_phydb10g
oracle   18207     1  0 15:29 ?        00:00:00 ora_lgwr_phydb10g
oracle   18209     1  0 15:29 ?        00:00:00 ora_ckpt_phydb10g
oracle   18211     1  0 15:29 ?        00:00:00 ora_smon_phydb10g
oracle   18213     1  0 15:29 ?        00:00:00 ora_reco_phydb10g
oracle   18215     1  0 15:29 ?        00:00:00 ora_mmon_phydb10g
oracle   18217     1  0 15:29 ?        00:00:00 ora_mmnl_phydb10g
oracle   18219 18196  0 15:29 pts/2    00:00:00 /bin/bash -c ps -ef | grep ora_
oracle   18221 18219  0 15:29 pts/2    00:00:00 grep ora_

SQL> !
[oracle@localhost ~]$ export ORACLE_SID=phydb11g
[oracle@localhost ~]$ env | grep ORA
ORACLE_SID=phydb11g
ORACLE_BASE=/u01/app
ORACLE_TERM=xterm
ORACLE_HOME=/u01/app/oracle
[oracle@localhost ~]$ cat /u01/app/oracle/dbs/initphydb11g.ora 
*.db_name='glndb'
*.instance_name='inst11g'
[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Mar 1 15:30:31 2012

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                  2094832 bytes
Variable Size             113248528 bytes
Database Buffers           50331648 bytes
Redo Buffers                6291456 bytes
ORA-00205: error in identifying control file, check alert log for more info


SQL> set line 160
SQL> show parameter instance_name;

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
instance_name                        string                            inst11g
SQL> !ps -ef | grep ora_
oracle   18199     1  0 15:29 ?        00:00:00 ora_pmon_phydb10g
oracle   18201     1  0 15:29 ?        00:00:00 ora_psp0_phydb10g
oracle   18203     1  0 15:29 ?        00:00:00 ora_mman_phydb10g
oracle   18205     1  0 15:29 ?        00:00:00 ora_dbw0_phydb10g
oracle   18207     1  0 15:29 ?        00:00:00 ora_lgwr_phydb10g
oracle   18209     1  0 15:29 ?        00:00:00 ora_ckpt_phydb10g
oracle   18211     1  0 15:29 ?        00:00:00 ora_smon_phydb10g
oracle   18213     1  0 15:29 ?        00:00:00 ora_reco_phydb10g
oracle   18215     1  0 15:29 ?        00:00:00 ora_mmon_phydb10g
oracle   18217     1  0 15:29 ?        00:00:00 ora_mmnl_phydb10g
oracle   18258     1  0 15:30 ?        00:00:00 ora_pmon_phydb11g
oracle   18260     1  0 15:30 ?        00:00:00 ora_psp0_phydb11g
oracle   18262     1  0 15:30 ?        00:00:00 ora_mman_phydb11g
oracle   18264     1  0 15:30 ?        00:00:00 ora_dbw0_phydb11g
oracle   18266     1  0 15:30 ?        00:00:00 ora_lgwr_phydb11g
oracle   18268     1  0 15:30 ?        00:00:00 ora_ckpt_phydb11g
oracle   18270     1  0 15:30 ?        00:00:00 ora_smon_phydb11g
oracle   18272     1  0 15:30 ?        00:00:00 ora_reco_phydb11g
oracle   18274     1  0 15:30 ?        00:00:00 ora_mmon_phydb11g
oracle   18276     1  0 15:30 ?        00:00:00 ora_mmnl_phydb11g
oracle   18306 18255  0 15:30 pts/2    00:00:00 /bin/bash -c ps -ef | grep ora_
oracle   18308 18306  0 15:30 pts/2    00:00:00 grep ora_

SQL>

            最后，不同的ORACLE_HOME，相同的ORACLE_SID的情况，该情况下，在同一台服务器上分别安装了10.2.0.5.0、11gR2两套不同的Oracle软件，ORACLE_HOME不同：

[oracle@localhost ~]$ env | grep ORA
ORACLE_SID=phydb10g
ORACLE_BASE=/u01/app
ORACLE_TERM=xterm
ORACLE_HOME=/u01/app/oracle
[oracle@localhost ~]$ ps -ef | grep ora_
oracle   18324 18077  0 15:34 pts/1    00:00:00 grep ora_
[oracle@localhost ~]$ cat /u01/app/oracle/dbs/initphydb10g.ora                  
*.db_name='glndb'
*.instance_name='inst10g'
[oracle@localhost ~]$ sqlplus

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Mar 1 15:35:16 2012

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

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area  171966464 bytes
Fixed Size                  2094832 bytes
Variable Size             113248528 bytes
Database Buffers           50331648 bytes
Redo Buffers                6291456 bytes
ORA-00205: error in identifying control file, check alert log for more info


SQL> set line 160
SQL> show parameter instance_name;

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
instance_name                        string                            inst10g
SQL> !
[oracle@localhost ~]$ ps -ef | grep ora_
oracle   18333     1  0 15:35 ?        00:00:00 ora_pmon_phydb10g
oracle   18335     1  0 15:35 ?        00:00:00 ora_psp0_phydb10g
oracle   18337     1  0 15:35 ?        00:00:00 ora_mman_phydb10g
oracle   18339     1  0 15:35 ?        00:00:00 ora_dbw0_phydb10g
oracle   18341     1  0 15:35 ?        00:00:00 ora_lgwr_phydb10g
oracle   18343     1  0 15:35 ?        00:00:00 ora_ckpt_phydb10g
oracle   18345     1  0 15:35 ?        00:00:00 ora_smon_phydb10g
oracle   18347     1  0 15:35 ?        00:00:00 ora_reco_phydb10g
oracle   18349     1  0 15:35 ?        00:00:00 ora_mmon_phydb10g
oracle   18351     1  0 15:35 ?        00:00:00 ora_mmnl_phydb10g
oracle   18382 18355  0 15:35 pts/2    00:00:00 grep ora_
[oracle@localhost ~]$ export ORACLE_HOME=/u01/app/product/11.2.0/dbhome_1
[oracle@localhost ~]$ env | grep ORA
ORACLE_SID=phydb10g
ORACLE_BASE=/u01/app
ORACLE_TERM=xterm
ORACLE_HOME=/u01/app/product/11.2.0/dbhome_1
[oracle@localhost ~]$ cat /u01/app/product/11.2.0/dbhome_1/dbs/initphydb10g.ora 
*.db_name='GLNDB'
*.instance_name='inst11g'
[oracle@localhost ~]$ /u01/app/product/11.2.0/dbhome_1/bin/sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Thu Mar 1 15:37:41 2012

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

SQL> conn / as sysdba;
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area  217157632 bytes
Fixed Size                  2211928 bytes
Variable Size             159387560 bytes
Database Buffers           50331648 bytes
Redo Buffers                5226496 bytes
ORA-00205: error in identifying control file, check alert log for more info


SQL> set line 160
SQL> show parameter instance_name;

NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
instance_name                        string                            inst11g
SQL> !
[oracle@localhost ~]$ ps -ef | grep ora_
oracle   18333     1  0 15:35 ?        00:00:00 ora_pmon_phydb10g
oracle   18335     1  0 15:35 ?        00:00:00 ora_psp0_phydb10g
oracle   18337     1  0 15:35 ?        00:00:00 ora_mman_phydb10g
oracle   18339     1  0 15:35 ?        00:00:00 ora_dbw0_phydb10g
oracle   18341     1  0 15:35 ?        00:00:00 ora_lgwr_phydb10g
oracle   18343     1  0 15:35 ?        00:00:00 ora_ckpt_phydb10g
oracle   18345     1  0 15:35 ?        00:00:00 ora_smon_phydb10g
oracle   18347     1  0 15:35 ?        00:00:00 ora_reco_phydb10g
oracle   18349     1  0 15:35 ?        00:00:00 ora_mmon_phydb10g
oracle   18351     1  0 15:35 ?        00:00:00 ora_mmnl_phydb10g
oracle   18402     1  0 15:37 ?        00:00:00 ora_pmon_phydb10g
oracle   18404     1  0 15:37 ?        00:00:00 ora_vktm_phydb10g
oracle   18408     1  0 15:37 ?        00:00:00 ora_gen0_phydb10g
oracle   18410     1  0 15:37 ?        00:00:00 ora_diag_phydb10g
oracle   18412     1  0 15:37 ?        00:00:00 ora_dbrm_phydb10g
oracle   18414     1  0 15:37 ?        00:00:00 ora_psp0_phydb10g
oracle   18416     1  0 15:37 ?        00:00:00 ora_dia0_phydb10g
oracle   18418     1  0 15:37 ?        00:00:00 ora_mman_phydb10g
oracle   18420     1  0 15:37 ?        00:00:00 ora_dbw0_phydb10g
oracle   18422     1  0 15:37 ?        00:00:00 ora_lgwr_phydb10g
oracle   18424     1  0 15:37 ?        00:00:00 ora_ckpt_phydb10g
oracle   18426     1  0 15:37 ?        00:00:00 ora_smon_phydb10g
oracle   18428     1  0 15:37 ?        00:00:00 ora_reco_phydb10g
oracle   18430     1  0 15:37 ?        00:00:00 ora_mmon_phydb10g
oracle   18432     1  0 15:37 ?        00:00:00 ora_mmnl_phydb10g
oracle   18470 18441  0 15:38 pts/2    00:00:00 grep ora_
[oracle@localhost ~]$

             顺带：我们甚至可以看到下述这些有趣的后台进程。

[oracle@localhost ~]$ ps -ef | grep ora_
oracle   18333     1  0 15:35 ?        00:00:00 ora_pmon_phydb10g
oracle   18335     1  0 15:35 ?        00:00:00 ora_psp0_phydb10g
oracle   18337     1  0 15:35 ?        00:00:00 ora_mman_phydb10g
oracle   18339     1  0 15:35 ?        00:00:00 ora_dbw0_phydb10g
oracle   18341     1  0 15:35 ?        00:00:00 ora_lgwr_phydb10g
oracle   18343     1  0 15:35 ?        00:00:00 ora_ckpt_phydb10g
oracle   18345     1  0 15:35 ?        00:00:00 ora_smon_phydb10g
oracle   18347     1  0 15:35 ?        00:00:00 ora_reco_phydb10g
oracle   18349     1  0 15:35 ?        00:00:00 ora_mmon_phydb10g
oracle   18351     1  0 15:35 ?        00:00:00 ora_mmnl_phydb10g
oracle   18402     1  0 15:37 ?        00:00:00 ora_pmon_phydb10g
oracle   18404     1  0 15:37 ?        00:00:00 ora_vktm_phydb10g
oracle   18408     1  0 15:37 ?        00:00:00 ora_gen0_phydb10g
oracle   18410     1  0 15:37 ?        00:00:00 ora_diag_phydb10g
oracle   18412     1  0 15:37 ?        00:00:00 ora_dbrm_phydb10g
oracle   18414     1  0 15:37 ?        00:00:00 ora_psp0_phydb10g
oracle   18416     1  0 15:37 ?        00:00:00 ora_dia0_phydb10g
oracle   18418     1  0 15:37 ?        00:00:00 ora_mman_phydb10g
oracle   18420     1  0 15:37 ?        00:00:00 ora_dbw0_phydb10g
oracle   18422     1  0 15:37 ?        00:00:00 ora_lgwr_phydb10g
oracle   18424     1  0 15:37 ?        00:00:00 ora_ckpt_phydb10g
oracle   18426     1  0 15:37 ?        00:00:00 ora_smon_phydb10g
oracle   18428     1  0 15:37 ?        00:00:00 ora_reco_phydb10g
oracle   18430     1  0 15:37 ?        00:00:00 ora_mmon_phydb10g
oracle   18432     1  0 15:37 ?        00:00:00 ora_mmnl_phydb10g
oracle   18491     1  0 15:41 ?        00:00:00 ora_pmon_phydb11g
oracle   18493     1  0 15:41 ?        00:00:00 ora_vktm_phydb11g
oracle   18497     1  0 15:41 ?        00:00:00 ora_gen0_phydb11g
oracle   18499     1  0 15:41 ?        00:00:00 ora_diag_phydb11g
oracle   18501     1  0 15:41 ?        00:00:00 ora_dbrm_phydb11g
oracle   18503     1  0 15:41 ?        00:00:00 ora_psp0_phydb11g
oracle   18505     1  0 15:41 ?        00:00:00 ora_dia0_phydb11g
oracle   18507     1  0 15:41 ?        00:00:00 ora_mman_phydb11g
oracle   18509     1  0 15:41 ?        00:00:00 ora_dbw0_phydb11g
oracle   18511     1  0 15:41 ?        00:00:00 ora_lgwr_phydb11g
oracle   18513     1  0 15:41 ?        00:00:00 ora_ckpt_phydb11g
oracle   18515     1  0 15:41 ?        00:00:00 ora_smon_phydb11g
oracle   18517     1  0 15:41 ?        00:00:00 ora_reco_phydb11g
oracle   18519     1  0 15:41 ?        00:00:00 ora_mmon_phydb11g
oracle   18521     1  0 15:41 ?        00:00:00 ora_mmnl_phydb11g
oracle   18585     1  0 15:43 ?        00:00:00 ora_pmon_phydb11g
oracle   18587     1  0 15:43 ?        00:00:00 ora_psp0_phydb11g
oracle   18589     1  0 15:43 ?        00:00:00 ora_mman_phydb11g
oracle   18591     1  0 15:43 ?        00:00:00 ora_dbw0_phydb11g
oracle   18593     1  0 15:43 ?        00:00:00 ora_lgwr_phydb11g
oracle   18595     1  0 15:43 ?        00:00:00 ora_ckpt_phydb11g
oracle   18597     1  0 15:43 ?        00:00:00 ora_smon_phydb11g
oracle   18599     1  0 15:43 ?        00:00:00 ora_reco_phydb11g
oracle   18601     1  0 15:43 ?        00:00:00 ora_mmon_phydb11g
oracle   18603     1  0 15:43 ?        00:00:00 ora_mmnl_phydb11g
oracle   18633 18606  0 15:43 pts/2    00:00:00 grep ora_
[oracle@localhost ~]$

             

有趣的后台进程
ORACLE_SID ORACLE_HOME INSTANCE_NAME PFILE Process ID 范围
phydb11g /u01/app/oracle inst11g /u01/app/oracle/initphydb11g.ora 18585…18603
phydb11g /u01/app/product/11.2.0/dbhome_1 phydb11g /u01/app/product/11.2.0/dbhome_1/dbs/initphydb11g.ora

18491…18521
phydb10g /u01/app/product/11.2.0/dbhome_1 inst11g /u01/app/product/11.2.0/dbhome_1/dbs/initphydb10g.ora  18402…18432
phydb10g /u01/app/oracle inst10g /u01/app/oracle/initphydb10g.ora 18333…18351

 

              结论：在UNIX、Linux平台上，相同的ORACLE_HOME下不可以同时运行ORACLE_SID相同的多个实例，不同的ORACLE_HOME下可以同时运行ORACLE_SID相同的多个实例！                 

            

删除undo表空间遇到ORA-30013及ORA-01548的解决思路

留下评论

今天在一套RAC环境下删除、切换其中一个实例的undo表空间时，遭遇ORA-30013及ORA-01548的错误，下面记录过程及解决方法。

1 创建新的UNDO表空间UNDOTBS3，并将当前实例的UNDO切换到UNDOTBS3之后，删除旧的UNDOTBS1时，遇到下述错误：

SQL> drop tablespace undotbs1 including contents and datafiles;
drop tablespace undotbs1 including contents and datafiles
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use

2 从上可以看到UNDOTBS1目前正在被使用。查询MetaLink,Unable to Drop Undo Tablespace ORA-30013 [ID 835944.1]获取基本思路，依据该文档给出的提示，执行下述命令：

SQL> select segment_name,owner,tablespace_name,status from dba_rollback_segs 
  2  where tablespace_name='UNDOTBS1' and status = 'ONLINE';

SEGMENT_NAME     OWNER  TABLESPACE_NAME    STATUS
---------------- ------ ------------------ ---------
_SYSSMU2$        PUBLIC UNDOTBS1           ONLINE
_SYSSMU3$        PUBLIC UNDOTBS1           ONLINE
_SYSSMU6$        PUBLIC UNDOTBS1           ONLINE
_SYSSMU8$        PUBLIC UNDOTBS1           ONLINE
SQL> SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */ KTUXESTA Status,
  2  KTUXECFL Flags FROM x$ktuxe WHERE ktuxesta!='INACTIVE';
  KTUXEUSN   KTUXESLT   KTUXESQN STATUS           FLAGS
---------- ---------- ---------- ---------------- ------------------------
         2         38     583286 PREPARED         SCO|COL|REV|DEAD|EXTDTX
         3         29     982959 PREPARED         SCO|COL|REV|DEAD|EXTDTX
         6         14     945326 PREPARED         SCO|COL|REV|DEAD|EXTDTX
         8          7     957413 PREPARED         SCO|COL|REV|DEAD|EXTDTX
        13         19     507098 PREPARED         SCO|COL|REV|EXTDTX
SQL> select local_tran_id, state from dba_2pc_pending; 
LOCAL_TRAN_ID          STATE
---------------------- ----------------
14.28.100017           collecting
2.38.583286            prepared
8.7.957413             prepared
3.29.982959            prepared
6.14.945326            prepared
13.19.507098           prepared
6 rows selected.

3 从上步的结果，结合文档ID 835944.1，基本上可以找到问题的原因：当前数据库中有未结束的分布式事务，而这些未结束的分布式事务占用了UNDOTBS1，最终导致不能删除UNDOTBS1。 文档ID 835944.1给出的解决方案是结束掉这些分布式事务。MetaLink上给出的思路是参照 Note 401302.1文档，而不凑巧的是，现在这篇文档无法查看，不得已，尝试重启数据库并删除UNDOTBS1。 重启之后，alert日志里看到如下错误信息：

*** SERVICE NAME:(SYS$BACKGROUND) 2012-02-10 11:27:15.527
*** SESSION ID:(431.1) 2012-02-10 11:27:15.527
*** 2012-02-10 11:27:15.527
ERROR, tran=14.28.100017, session#=1, ose=0:
ORA-02019: connection description for remote database not found
ORA-02019: connection description for remote database not found
*** 2012-02-10 11:27:50.676
ERROR, tran=14.28.100017, session#=1, ose=0:
ORA-02019: connection description for remote database not found
ORA-02019: connection description for remote database not found
*** 2012-02-10 11:28:42.707
ERROR, tran=14.28.100017, session#=1, ose=0:
ORA-02019: connection description for remote database not found
ORA-02019: connection description for remote database not found

尝试再次删除UNDOTBS1：

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

这次报出ORA-01548的错误！！！原来还是分布式事务未提交导致的：

SQL> SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */ KTUXESTA Status,
  2  KTUXECFL Flags FROM x$ktuxe WHERE ktuxesta!='INACTIVE';
  KTUXEUSN   KTUXESLT   KTUXESQN STATUS           FLAGS
---------- ---------- ---------- ---------------- ------------------------
         2         38     583286 PREPARED         SCO|COL|REV|DEAD|EXTDTX
         3         29     982959 PREPARED         SCO|COL|REV|DEAD|EXTDTX
         6         14     945326 PREPARED         SCO|COL|REV|DEAD|EXTDTX
         8          7     957413 PREPARED         SCO|COL|REV|DEAD|EXTDTX
        13         19     507098 PREPARED         SCO|COL|REV|EXTDTX
SQL> select local_tran_id, state from dba_2pc_pending; 
LOCAL_TRAN_ID          STATE
---------------------- ----------------
14.28.100017           collecting
2.38.583286            prepared
8.7.957413             prepared
3.29.982959            prepared
6.14.945326            prepared
13.19.507098           prepared
6 rows selected.
SQL>

4 继续MetaLink：ORA-1548 Dropping UNDO Tablespace Distributed Transaction Pending:Prepared / Dead [ID 1321093.1]根据该文档，执行下述命令：

SQL> Select segment_id,segment_name,status,tablespace_name
  2  from dba_rollback_segs where status not in ('ONLINE','OFFLINE');

SEGMENT_ID SEGMENT_NAME                   STATUS           TABLESPACE_NAME
---------- ------------------------------ ---------------- ------------------------------
         2 _SYSSMU2$                      PARTLY AVAILABLE UNDOTBS1
         3 _SYSSMU3$                      PARTLY AVAILABLE UNDOTBS1
         6 _SYSSMU6$                      PARTLY AVAILABLE UNDOTBS1
         8 _SYSSMU8$                      PARTLY AVAILABLE UNDOTBS1
SQL>

You find you have segments that are 'Partly Available' This usually means they still have active transactions pending and you can not drop the tablespace until the transaction is committed or rolled back. 当回滚段状态为Partly Available时，说明还是有事务没结束！！！

SQL> SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */
  2  KTUXESTA Status,
  3  KTUXECFL Flags
  4  FROM x$ktuxe
  5  WHERE ktuxesta!='INACTIVE'
  6  AND ktuxeusn
  7  in(2,3,6,8);

  KTUXEUSN   KTUXESLT   KTUXESQN STATUS           FLAGS
---------- ---------- ---------- ---------------- ------------------------
         2         38     583286 PREPARED         SCO|COL|REV|DEAD|EXTDTX
         3         29     982959 PREPARED         SCO|COL|REV|DEAD|EXTDTX
         6         14     945326 PREPARED         SCO|COL|REV|DEAD|EXTDTX
         8          7     957413 PREPARED         SCO|COL|REV|DEAD|EXTDTX
SQL> select local_tran_id, state from dba_2pc_pending; 

LOCAL_TRAN_ID          STATE
---------------------- ----------------
14.28.100017           collecting
2.38.583286            prepared
8.7.957413             prepared
3.29.982959            prepared
6.14.945326            prepared
13.19.507098           prepared

6 rows selected.

SQL>

通过上面的结果，看到2、3、6、8号回滚段上有活动的事务。该文档依然指出解决方案是查看Note 401302.1文档，而该文档又无法打开，不得已Google之，参照 http://blog.itpub.net/post/38439/477038  获得解决问题的最终方法。 

5 根据http://blog.itpub.net/post/38439/477038 直接强制提交这些活动的分布式事务：

SQL> select local_tran_id, state from dba_2pc_pending; 

LOCAL_TRAN_ID          STATE
---------------------- ----------------
14.28.100017           collecting
2.38.583286            prepared
8.7.957413             prepared
3.29.982959            prepared
6.14.945326            prepared
13.19.507098           prepared

6 rows selected.

SQL> commit force '2.38.583286';

Commit complete.

SQL> select local_tran_id, state from dba_2pc_pending; 

LOCAL_TRAN_ID          STATE
---------------------- ----------------
14.28.100017           collecting
2.38.583286            forced commit
8.7.957413             prepared
3.29.982959            prepared
6.14.945326            prepared
13.19.507098           prepared

6 rows selected.

SQL> commit force '8.7.957413';

Commit complete.

SQL> commit force '3.29.982959';

Commit complete.

SQL> commit force '6.14.945326';

Commit complete.

SQL> commit force '13.19.507098';

Commit complete.

SQL> select local_tran_id, state from dba_2pc_pending; 

LOCAL_TRAN_ID          STATE
---------------------- ----------------
14.28.100017           collecting
2.38.583286            forced commit
8.7.957413             forced commit
3.29.982959            forced commit
6.14.945326            forced commit
13.19.507098           forced commit

6 rows selected.

SQL> Select segment_id,segment_name,status,tablespace_name           
  2  from dba_rollback_segs where status not in ('ONLINE','OFFLINE');

no rows selected

SQL> SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */ 
  2  KTUXESTA Status,                                          
  3  KTUXECFL Flags                                            
  4  FROM x$ktuxe                                              
  5  WHERE ktuxesta!='INACTIVE'                                
  6  AND ktuxeusn                                              
  7  in(2,3,6,8);                                              

no rows selected

SQL> drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped.

SQL>

6 终于干掉了那个旧的UNDO，而此时，alert日志里的信息如下：

Fri Feb 10 15:08:16 CST 2012
DISTRIB TRAN 44444444.D7D4863A714B974489CD48496956271900000000
  is local tran 2.38.583286 (hex=02.26.8e676)
  change pending prepared tran, scn=125560421 (hex=0.077be665)
  to     pending forced commit tran, scn= (hex=0.00000000) 
Fri Feb 10 15:08:56 CST 2012
DISTRIB TRAN 44444444.D88A6D59B486E44D8BAD8DABFDCF289C00000000
  is local tran 8.7.957413 (hex=08.07.e9be5)
  change pending prepared tran, scn=194136242 (hex=0.0b9248b2)
  to     pending forced commit tran, scn= (hex=0.00000000) 
Fri Feb 10 15:09:08 CST 2012
DISTRIB TRAN 44444444.4A67F0F3F3EA464081883577EE646AAB00000000
  is local tran 3.29.982959 (hex=03.1d.effaf)
  change pending prepared tran, scn=195270309 (hex=0.0ba396a5)
  to     pending forced commit tran, scn= (hex=0.00000000) 
Fri Feb 10 15:09:20 CST 2012
DISTRIB TRAN 44444444.97CB87A6BAE9E943B761C9C7FDA9844600000000
  is local tran 6.14.945326 (hex=06.0e.e6cae)
  change pending prepared tran, scn=196753377 (hex=0.0bba37e1)
  to     pending forced commit tran, scn= (hex=0.00000000) 
Fri Feb 10 15:09:36 CST 2012
DISTRIB TRAN 44444444.192AEFF6D316B2468F5D74FE5EBDC9EC00000000
  is local tran 13.19.507098 (hex=0d.13.7bcda)
  change pending prepared tran, scn=332059676 (hex=0.13cad41c)
  to     pending forced commit tran, scn= (hex=0.00000000) 
Fri Feb 10 15:13:17 CST 2012
drop tablespace undotbs1 including contents and datafiles
Fri Feb 10 15:13:25 CST 2012
Deleted Oracle managed file +ORADATA/glndb/datafile/undotbs1.261.726057859
Completed: drop tablespace undotbs1 including contents and datafiles

7 总结：对于分布式事务，目前还不是很清楚。而这个案例中涉及到的情况还有可能出现更为复杂的情况，需要深入研究一下，而我遇到的这种情况属于比较简单的。

删除用户报ORA-24005错误及解决办法

删除用户报ORA-24005错误及解决办法有1条评论

在一套10.2.0.5.0的双节点RAC数据库上，删除用户时报出ORA-00604及ORA-24005的错误：

 
SQL> conn / as sysdba;
Connected.
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
SQL> drop user gdhytest cascade;
drop user gdhytest cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables
SQL>

后经查找MetaLink：Ora-24005 Error Trying To Drop User Sysman Cascade [ID 456437.1] 找到原因：被删除的用户ghhytest拥有queue table。

 
SQL> set pagesize 100 
SQL> col object_name format a40 
SQL> select object_name,object_type from dba_objects
  2  where owner='GDHYTEST' AND OBJECT_NAME LIKE '%AQ%'
  3  ;
OBJECT_NAME                              OBJECT_TYPE
---------------------------------------- -------------------
AQ$_GPSSTATUS_QUEUE_TABLE_H              TABLE
AQ$_GPSSTATUS_QUEUE_TABLE_I              TABLE
AQ$_GPSSTATUS_QUEUE_TABLE_NR             TABLE
AQ$_GPSSTATUS_QUEUE_TABLE_T              TABLE
AQ$_GPS_TEMP_QUEUE_TABLE_NR              TABLE
SQL>

解决方法：

1 gdhytest登录数据库，执行DBMS_AQADM.DROP_QUEUE_TABLE进行删除queue talbe:

 
SQL> conn gdhytest/gdhytest
Connected.
SQL> exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'AQ$_GPSSTATUS_QUEUE_TABLE_H',
force=>true);
BEGIN DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'AQ$_GPSSTATUS_QUEUE_TABLE_H',
force=>true); END;
      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_AQADM' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

2 发现权限不够，赋权，重新删除：

 
SQL> conn / as sysdba;
Connected.
SQL> grant dba to gdhytest;
Grant succeeded.
SQL> conn gdhytest/gdhytest
Connected.
SQL> exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'AQ$_GPSSTATUS_QUEUE_TABLE_H',
force=>true);
BEGIN DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'AQ$_GPSSTATUS_QUEUE_TABLE_H',
force=>true); END;
*
ERROR at line 1:
ORA-24019: identifier for QUEUE_TABLE too long, should not be greater than 24
characters
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 4310
ORA-06512: at "SYS.DBMS_AQADM", line 197
ORA-06512: at line 1
SQL> exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'GPSSTATUS_QUEUE_TABLE_H',
force=>true);
BEGIN DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'GPSSTATUS_QUEUE_TABLE_H',
force=>true); END;
*
ERROR at line 1:
ORA-24002: QUEUE_TABLE GDHYTEST.GPSSTATUS_QUEUE_TABLE_H does not exist
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 4310
ORA-06512: at "SYS.DBMS_AQADM", line 197
ORA-06512: at line 1

3 依然报错！！！QUEUE_TABLE too long,不得已，重命名queue table进行删除：

 
SQL> rename  AQ$_GPSSTATUS_QUEUE_TABLE_H to queue1;
Table renamed.
SQL> select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
AQ$_GPSSTATUS_QUEUE_TABLE_I    TABLE
SYS_IOT_OVER_60452             TABLE
AQ$_GPSSTATUS_QUEUE_TABLE_NR   TABLE
AQ$_GPSSTATUS_QUEUE_TABLE_T    TABLE
SYS_IOT_OVER_60459             TABLE
AQ$_GPS_TEMP_QUEUE_TABLE_NR    TABLE
QUEUE1                         TABLE
7 rows selected.
SQL> exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'QUEUE1',force=>true);
PL/SQL procedure successfully completed.
SQL> select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
AQ$_GPSSTATUS_QUEUE_TABLE_I    TABLE
SYS_IOT_OVER_60452             TABLE
AQ$_GPSSTATUS_QUEUE_TABLE_NR   TABLE
AQ$_GPSSTATUS_QUEUE_TABLE_T    TABLE
SYS_IOT_OVER_60459             TABLE
AQ$_GPS_TEMP_QUEUE_TABLE_NR    TABLE
6 rows selected.
SQL> 

4 如法炮制，重命名其它queue table,然后执行删除：

 
SQL> rename AQ$_GPSSTATUS_QUEUE_TABLE_I to queue_a;
Table renamed.
SQL> rename AQ$_GPSSTATUS_QUEUE_TABLE_NR to queue_b;
Table renamed.
SQL> rename AQ$_GPSSTATUS_QUEUE_TABLE_T to queue_c;
Table renamed.
SQL> rename aq$_GPS_TEMP_QUEUE_TABLE_NR to queue_d;
Table renamed.
SQL> select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
SYS_IOT_OVER_60452             TABLE
SYS_IOT_OVER_60459             TABLE
QUEUE_B                        TABLE
QUEUE_A                        TABLE
QUEUE_C                        TABLE
QUEUE_D                        TABLE
6 rows selected.
SQL> exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'QUEUE_A',force=>true);
PL/SQL procedure successfully completed.
SQL> exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'QUEUE_B',force=>true);
PL/SQL procedure successfully completed.
SQL> exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'QUEUE_C',force=>true);
PL/SQL procedure successfully completed.
SQL> exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'QUEUE_D',force=>true);
PL/SQL procedure successfully completed.
SQL> select * from tab;
no rows selected

5 最后彻底删除gdhytest用户：

 
SQL> conn / as sysdba;
Connected.
SQL> drop user gdhytest cascade;
User dropped.
SQL>

调试存储过程：ORA-0131 Insufficient privileges 处理

留下评论

昨天，一开发同事反映说在PL/SQL Developer工具里无法调试存储过程，报错信息如下：

ORA-0131:Insufficient privileges.

Note:Debugging requires the DEBUG CONNECT SESSION system privileges.                                                                                                                                        

后经查找，是缺失  DEBUG CONNECT SESSION 系统权限所致。

解决办法：以SYS用户登录数据库，执行赋权操作：

SQL> grant  DEBUG CONNECT SESSION to user_name;

附1：有网友指出还需赋予DEBUG ANY PROCEDURE的权限，经测试，该权限可不用赋予！

附2：可以从数据字典role_sys_privs表查看该权限相关信息：

SQL> conn / as sysdba;
Connected.
SQL> select * from role_sys_privs where privilege like 'DEBUG%' order by 2;
ROLE             PRIVILEGE                ADM
---------------- ------------------------ ---
DBA              DEBUG ANY PROCEDURE      YES
JAVADEBUGPRIV    DEBUG ANY PROCEDURE      NO
DBA              DEBUG CONNECT SESSION    YES
JAVADEBUGPRIV    DEBUG CONNECT SESSION    NO

简单记录，以作备忘！

知识还是点点记录好！

impdp ORA-31655错误处理一例

留下评论 ,

今天上午，收到开发同事发过来的邮件：

需要将从之前用EXPDP备份的dump文件中，将某张表还原到一个新的schema下。

电话沟通后，原来是想要将逻辑备份的dump文件中FR8_ZH这个用户下的SB_DATA_RIGHT，导入到同库下的FR8_TEST9这个schema下。

①  于是开始干活：

C:\Documents and Settings\Administrator>impdp directory=my_dump dumpfile=2012-01
-23.dmp logfile=fr8_test9.log remap_schema=fr8_zh:fr8_test9 tables=SB_DATA_RIGHT
Import: Release 10.2.0.5.0 - Production on 星期五, 03 2月, 2012 11:32:53
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
用户名: sys/oracle as sysdba
连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORA-31655: 尚未为作业选择数据或元数据对象

已成功加载/卸载了主表 "SYS"."SYS_IMPORT_TABLE_01"

启动 "SYS"."SYS_IMPORT_TABLE_01":  sys/******** AS SYSDBA directory=my_dump dump
file=2012-01-23.dmp logfile=fr8_test9.log remap_schema=fr8_zh:fr8_test9 tables=S
B_DATA_RIGHT

作业 "SYS"."SYS_IMPORT_TABLE_01" 已于 11:33:19 成功完成

发现，报出ORA-31655的错误！！！

而

SQL> !oerr ora 31655

31655, 00000, "no data or metadata objects selected for job"

// *Cause:  After the job parameters and filters were applied,

//          the job specified by the user did not reference any objects.

// *Action: Verify that the mode of the job specified objects to be moved.

//          For command line clients, verify that the INCLUDE, EXCLUDE and

//          CONTENT parameters were correctly set.  For DBMS_DATAPUMP API

//          users, verify that the metadata filters, data filters, and

//          parameters that were supplied on the job were correctly set.
SQL>

而ORA-31655的错误是说，在导入数据的命令中，impdp没有找到正确的对象元数据。

②  加上INCLUDE关键字，重新执行导入：

 
C:\Documents and Settings\Administrator>impdp directory="my_dump" dumpfile=2012-
01-23.dmp logfile=fr8_test9.log remap_schema=fr8_zh:fr8_test9 tables=SB_DATA_RIG
HT include=table:\"like \'SB_DATA_RIGHT%\'\"
Import: Release 10.2.0.5.0 - Production on 星期五, 03 2月, 2012 11:36:35
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
用户名: sys/oracle as sysdba
连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORA-31655: 尚未为作业选择数据或元数据对象
已成功加载/卸载了主表 "SYS"."SYS_IMPORT_TABLE_01"

启动 "SYS"."SYS_IMPORT_TABLE_01":  sys/******** AS SYSDBA directory=my_dump dump
file=2012-01-23.dmp logfile=fr8_test9.log remap_schema=fr8_zh:fr8_test9 tables=S
B_DATA_RIGHT include=table:"like \'SB_DATA_RIGHT%\'
"
作业 "SYS"."SYS_IMPORT_TABLE_01" 已于 11:37:02 成功完成

发现依然报ORA-31655的错误！

③ 不得已，查找Metalink，ORA-31655 On imdp As a Privileged User Wih INCLUDE= After Upgrade To 10.2.0.5/11.2 [ID 1225108.1] 得到下述的解决办法：

Applies to:

Oracle Server – Enterprise Edition – Version: 10.2.0.5 and later   [Release: 10.2 and later ]
Oracle Server – Enterprise Edition – Version: 11.2.0.1.0 to 11.2.0.2.0   [Release: 11.2 to 11.2]
Information in this document applies to any platform.

 

Symptoms

Datapump import raises an ORA-31655 “no data or metadata objects selected for job” on import when using the REMAP_SCHEMA and INCLUDE= clauses when performed as a privileged user (i.e. a user with the IMP_FULL_DATABASE role).

Changes

The database has been upgraded to 10.2.0.5 or 11.2.

Cause

This is intended behavior and occurs due to the fix for bug:6831823 which went in to 11.2 and 10.2.0.5.

The ORA-31655 error is now signaled following the fix for bug:6831823, as that bug which existed in previous versions meant that if a privileged user performed a table import from a full export dump, and the specified table name existed in multiple schemas, then all the tables would be imported where as only the table for the importing user should have been imported, as documented in the Oracle� Database Utilities 10g Release 2 (10.2) manual, chapter 3 ‘Data Pump Import’, under the definition of the TABLES= clause, which states “If you do not supply a schema_name, it defaults to that of the current user”.  This also applies to the INCLUDE= clause, and hence the error is now raised.

Documentation bug:10140472 has been created to get this more clearly stated under the INCLUDE and REMAP_SCHEMA options in future documentation sets.

Solution

Perform the import using the TABLES= clause rather than the INCLUDE=TABLE: clause, e.g.:

 

Change From:

impdp system directory=data_pump_dir dumpfile=scott.dmp remap_schema=scott:scott_test include=TABLE:\”IN \(\’EMP\’, \’DEP\’\)\”

To:

impdp system directory=data_pump_dir dumpfile=scott.dmp remap_schema=scott:scott_test tables=SCOTT.EMP,SCOTT.DEPT

or 

impdp scott directory=data_pump_dir dumpfile=scott.dmp remap_schema=scott:scott_test include=TABLE:\”IN \(\’EMP\’, \’DEPT\’\)\” 

找到问题的原因后，执行下述命令重新导入成功！

 
C:\Documents and Settings\Administrator>impdp directory="my_dump" dumpfile=2012-
01-23.dmp logfile=fr8_test9.log remap_schema=fr8_zh:fr8_test9 tables=fr8_zh.sb_d
ata_right
Import: Release 10.2.0.5.0 - Production on 星期五, 03 2月, 2012 11:48:02
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
用户名: sys/oracle as sysdba
连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

已成功加载/卸载了主表 "SYS"."SYS_IMPORT_TABLE_01"
启动 "SYS"."SYS_IMPORT_TABLE_01":  sys/******** AS SYSDBA directory=my_dump dump
file=2012-01-23.dmp logfile=fr8_test9.log remap_schema=fr8_zh:fr8_test9 tables=f
r8_zh.sb_data_right

处理对象类型 SCHEMA_EXPORT/TABLE/TABLE

处理对象类型 SCHEMA_EXPORT/TABLE/TABLE_DATA

. . 导入了 "FR8_TEST9"."SB_DATA_RIGHT"                 5.328 MB    4484 行

处理对象类型 SCHEMA_EXPORT/TABLE/INDEX/INDEX

处理对象类型 SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

处理对象类型 SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

处理对象类型 SCHEMA_EXPORT/TABLE/COMMENT

处理对象类型 SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

作业 "SYS"."SYS_IMPORT_TABLE_01" 已于 11:48:49 成功完成

最后，登录数据库后，检查FR8_TEST9用户下的SB_DATA_RIGHT表中的数据，发现一切正常。

解决ORA-12516错误一则

解决ORA-12516错误一则有1条评论

在上周五晚上通宵加班将一套10.2.0.5.0的Linux 虚拟机环境下的数据量为260GB 的双节点RAC数据库顺利迁移至一台物理机器的开发数据库后，这两天开发的同事反映说连不上物理机开发库了。

起初，我也没有太在意，的确是因为这两天公司内部网络不太正常，ping物理开发库延时比较严重，偶有timed out现象，就一直以为是网络的问题了。直到中午的时候，同事说网络基本正常了，用PL/SQL Developer客户端工具连数据库的时候报出Ora-12516的错误，这才引起了我的注意！

上Metalink查了一下，看了“Troubleshooting Guide TNS-12519 TNS-12516 ORA-12519 ORA-12516 [ID 552765.1]”的文章后，才知道了自己迁移数据库后粗心大意犯下的错误。原来的RAC数据库中每个实例中都将process初始化参数都设置了400，sessions=445，而现在是单台数据库对外提供服务，导致会话数不够用，最终导致的Ora-12516的错误！其实，对于这种开发库而言，公司的开发同事并不多，怎么可能导致445个会话还不够连接使用呢？其实，造成问题的最根本原因是开发人员的应用程序中的连接池配置的有问题，连接数配置过高导致的！！！！

找到了问题的基本原因后，就将process初始化参数从400改为600，进而sessions自动被置为665，transactions参数自动置为731。然后，重启数据库。

        在oracle数据库的初始化参数中，有一类参数是推倒参数，其中：

        sessions=1.1*processes+5,transactions=1.1*sessions.

这样，问题得到了基本的解决。

事后，又出现了一些不痛不痒的问题。

这不，年后简单记录下发生在这个春节前的种种问题，及解决问题的方法：

① 会话数不够用，导致Ora-12516的错误。解决方法，加大process初始化参数，或者通知修改应用程序中的连接数；

② 通过在数据库端配置profile来控制每个会话的活动时间，过期由数据库自动断开会话；

③ 通过使用共享服务器模式来控制数据库服务器端的进程资源；

④ 最头大的问题就是，有个开发的同事将自己PC机器的IP地址设置了同数据库服务器相同的IP地址，导致其他开发人员一直连接不上数据库！！！这个问题可不是第一次遇见，解决办法，协同网络管理员将数据库服务器IP绑定到MAC。

 

其实，这本来是去年春节前遇到的一则案例，后来由于太忙，赶着要回家，于是拖到了今天才发布出来！