• 误删除AUD$所在的表空间,无法切换用户


    问题故障:数据库Open,无法切换普通用户:
    ---递归SQL无法执行
     
    SQL> conn hr/hr
    ERROR at line 1:
    ORA-00604: error occurred at recursive SQL level 1
    ORA-04045: errors during recompilation/revalidation of LBACSYS.LBAC_EVENTS
    ORA-06508: PL/SQL: could not find program unit being called
    ORA-06512: at line 2
    ORA-06508: PL/SQL: could not find program unit being called
    ORA-06512: at line 2
     
    [oracle@hukou ~]$ tail -50f /picclife/app/oracle/diag/rdbms/hukou/hukou/trace/alert_hukou.log
     
    ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout
    ORA-29400: data cartridge error
    KUP-00554: error encountered while parsing access parameters
    KUP-01005: syntax error: found "identifier": expecting one of: "field"
    KUP-01008: the bad identifier was: dield
    KUP-01007: at line 1 column 95
     
    10046--无法生成详细文档
    追踪操作:本次需要追踪的是conn hr/hr,数据内部执行了什么操作,但只要执行操作,就断开了连接,无法结束追踪,因此10046工具无法使用此场景:
     
    SQL> alter session set events '10046 trace name context forever';
    Session altered.
    SQL> alter session set events '10046 trace name context forever,level 8';
     
     
    如下:排除问题:因素
    --查询用户密码,角色:排除操作问题
    SQL> alter user yang identified by yang;
    SQL> select grantee,granted_role from dba_role_privs where grantee='YANG';
    GRANTEE                        GRANTED_ROLE
    ------------------------------ ------------------------------
    YANG                           RESOURCE
    YANG                           CONNECT
    SQL> conn yang/yang;
    ERROR:
     
     
    ###进入误区,误以为是存储空间问题:
    --查询表空间使用的对象> 20m的对象信息:
    select segment_name,sum(bytes)/1024/1024 from dba_segments having(sum(bytes)/1024/1024)>20 group by segment_name
    SEGMENT_NA SUM(BYTES)/1024/1024
    ---------- --------------------
    IDL_UB1$             45.1328125
    SOURCE$               42.984375
     
     
     
    ##开启SQL追踪
    SQL> alter system set sql_trace=true;
    SQL> conn hr/hr
    ERROR:
    ORA-00604: error occurred at recursive SQL level 1
    ORA-00942: table or view does not exist
    ORA-02002: error while writing to audit trail
    ORA-00604: error occurred at recursive SQL level 1
    ORA-00942: table or view does not exist
     
    --查询用户的dump文件:
    --查询参数路径:
    SQL> show parameter user_dump   
    --根据时间排序,按修改时间排列;
    more分页查看:Q结束分页 =>  选择最靠前的  ora_xxx.trc文件查询
    [oracle@hostuzi trace]$ ls -lt|more
    -rw-r----- 1 oracle oinstall 304287 12-24 18:48 alert_PROD.log
    -rw-r----- 1 oracle oinstall     70 12-24 18:43 PROD_lgwr_23734.trm
    -rw-r----- 1 oracle oinstall   2627 12-24 18:43 PROD_ora_23773.trc
     
    --------用户的DUMP文件---
    PARSE ERROR #140124260648808:len=597 dep=1 uid=0 oct=2 lid=0 t
    im=1511612960366612 err=942
    insert into sys.aud$( sessionid,entryid,statement,ntimestamp#,
    userid,userhost,terminal,action#,returncode, obj$creator,obj$
    name,auth$privileges,auth$grantee, new$owner,new$name,ses$acti
    ons,ses$tid,logoff$pread, logoff$lwrite,logoff$dead,comment$te
    xt,spare1,spare2,  priv$used,clientid,sessioncpu,proxy$sid,use
    r$guid, instance#,process#,xid,scn,auditid,  sqlbind,sqltext,o
    bj$edition,dbid)  values(:1,:2,:3,SYS_EXTRACT_UTC(SYSTIMESTAMP
    ),     :4,:5,:6,:7,:8,     :9,:10,:11,:12,     :13,:14,:15,:16
    ,:17,     :18,:19,:20,:21,:22,     :23,:24,:25,:26,:27,     :2
    8,:29,:30,:31,:32,     :33,:34,:35,:36)
    *** 2017-11-25 20:29:21.366
    CLOSE #140124260648808:c=0,e=5,dep=1,type=0,tim=15116129613669
    40
     
    ###AUD$的表:
    审计参数开启后:AUD$基表存储:
     
    查询dba_audit_trail  视图查询:
    SQL> show parameter audit
    audit_trail                          string      DB_EXTENDED 【数据库开启审计模式,审计信息会记录在aud$基表中】
     
    SQL> select owner,table_name,tablespace_name,max_extents from dba_tables where table_name='AUD$';
    no rows selected
     
    SQL>  select owner,table_name,tablespace_name,max_extents from dba_tables where table_name='FAG_LOG$';
    no rows selected
    -----------------对象不存在------------
     
     
    ####此时问题可以判定:缘由在于:之前将AUD$,FGA_LOG$迁移的另一个表空间被删除:
     
    问题原因找到:两种解决方案:
    一、等待找到建表语句,重建表
    二、关闭审计参数:先对外提供服务运行:需要重启库操作:
    alter system set audit_trail=none scope=spfile;
     
     
    一、
    #另一个库--查询与基表AUD$管理的索引
    SQL> select index_name,table_name from dba_indexes where table_name='AUD$';
    INDEX_NAME                     TABLE_NAME
    ------------------------------ ------------------------------
    SYS_IL0000000407C00040$$       AUD$
    SYS_IL0000000407C00041$$       AUD$
     
    SQL> select index_name,table_name from dba_indexes where table_name='FGA_LOG$';
    INDEX_NAME                     TABLE_NAME
    ------------------------------ ------------------------------
    SYS_IL0000000417C00013$$       FGA_LOG$
    SYS_IL0000000417C00028$$       FGA_LOG$
     
    #查询两个表的触发器
    SQL> select TRIGGER_NAME,TABLE_NAME from user_triggers where table_name='AUD$';
    no rows selected
    SQL> select TRIGGER_NAME,TABLE_NAME from user_triggers where table_name='FGA_LOG$';
    no rows selected
     
     
    ####通过GETDDL调出建表语句:
    SQL> set line 200 pages 50000 wrap on long 999999 serveroutput on
    SQL> select dbms_metadata.get_ddl('TABLE','AUD$','SYS') FROM DUAL;
    DBMS_METADATA.GET_DDL('TABLE','AUD$','SYS')
    --------------------------------------------------------------------------------
      CREATE TABLE "SYS"."AUD$"
       (    "SESSIONID" NUMBER NOT NULL ENABLE,
            "ENTRYID" NUMBER NOT NULL ENABLE,
            "STATEMENT" NUMBER NOT NULL ENABLE,
            "TIMESTAMP#" DATE,
            "USERID" VARCHAR2(30),
            "USERHOST" VARCHAR2(128),
            "TERMINAL" VARCHAR2(255),
            "ACTION#" NUMBER NOT NULL ENABLE,
            "RETURNCODE" NUMBER NOT NULL ENABLE,
            "OBJ$CREATOR" VARCHAR2(30),
            "OBJ$NAME" VARCHAR2(128),
            "AUTH$PRIVILEGES" VARCHAR2(16),
            "AUTH$GRANTEE" VARCHAR2(30),
            "NEW$OWNER" VARCHAR2(30),
            "NEW$NAME" VARCHAR2(128),
            "SES$ACTIONS" VARCHAR2(19),
            "SES$TID" NUMBER,
            "LOGOFF$LREAD" NUMBER,
            "LOGOFF$PREAD" NUMBER,
            "LOGOFF$LWRITE" NUMBER,
            "LOGOFF$DEAD" NUMBER,
            "LOGOFF$TIME" DATE,
            "COMMENT$TEXT" VARCHAR2(4000),
            "CLIENTID" VARCHAR2(64),
            "SPARE1" VARCHAR2(255),
            "SPARE2" NUMBER,
            "OBJ$LABEL" RAW(255),
            "SES$LABEL" RAW(255),
            "PRIV$USED" NUMBER,
            "SESSIONCPU" NUMBER,
            "NTIMESTAMP#" TIMESTAMP (6),
            "PROXY$SID" NUMBER,
            "USER$GUID" VARCHAR2(32),
            "INSTANCE#" NUMBER,
            "PROCESS#" VARCHAR2(16),
            "XID" RAW(8),
            "AUDITID" VARCHAR2(64),
            "SCN" NUMBER,
            "DBID" NUMBER,
            "SQLBIND" CLOB,
            "SQLTEXT" CLOB,
            "OBJ$EDITION" VARCHAR2(30)
       ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
    NOCOMPRESS LOGGING
      STORAGE(INITIAL 16384 NEXT 40960 MINEXTENTS 1 MAXEXTENTS 505
      PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1
      BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
      TABLESPACE "SYSTEM"
    LOB ("SQLBIND") STORE AS BASICFILE (
      TABLESPACE "SYSTEM" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
      NOCACHE LOGGING
      STORAGE(INITIAL 16384 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 505
      PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1
      BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
    LOB ("SQLTEXT") STORE AS BASICFILE (
      TABLESPACE "SYSTEM" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
      NOCACHE LOGGING
      STORAGE(INITIAL 16384 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 505
      PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1
      BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT));
     
    SQL> select dbms_metadata.get_ddl('TABLE','FGA_LOG$','SYS') FROM DUAL;
    DBMS_METADATA.GET_DDL('TABLE','FGA_LOG$','SYS')
    --------------------------------------------------------------------------------
      CREATE TABLE "SYS"."FGA_LOG$"
       (    "SESSIONID" NUMBER NOT NULL ENABLE,
            "TIMESTAMP#" DATE,
            "DBUID" VARCHAR2(30),
            "OSUID" VARCHAR2(255),
            "OSHST" VARCHAR2(128),
            "CLIENTID" VARCHAR2(64),
            "EXTID" VARCHAR2(4000),
            "OBJ$SCHEMA" VARCHAR2(30),
            "OBJ$NAME" VARCHAR2(128),
            "POLICYNAME" VARCHAR2(30),
            "SCN" NUMBER,
            "SQLTEXT" VARCHAR2(4000),
            "LSQLTEXT" CLOB,
            "SQLBIND" VARCHAR2(4000),
            "COMMENT$TEXT" VARCHAR2(4000),
            "PLHOL" LONG,
            "STMT_TYPE" NUMBER,
            "NTIMESTAMP#" TIMESTAMP (6),
            "PROXY$SID" NUMBER,
            "USER$GUID" VARCHAR2(32),
            "INSTANCE#" NUMBER,
            "PROCESS#" VARCHAR2(16),
            "XID" RAW(8),
            "AUDITID" VARCHAR2(64),
            "STATEMENT" NUMBER,
            "ENTRYID" NUMBER,
            "DBID" NUMBER,
            "LSQLBIND" CLOB,
            "OBJ$EDITION" VARCHAR2(30)
       ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
    NOCOMPRESS LOGGING
      STORAGE(INITIAL 16384 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 505
      PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1
      BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
      TABLESPACE "SYSTEM"
    LOB ("LSQLTEXT") STORE AS BASICFILE (
      TABLESPACE "SYSTEM" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
      NOCACHE LOGGING
      STORAGE(INITIAL 16384 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 505
      PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1
      BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
    LOB ("LSQLBIND") STORE AS BASICFILE (
      TABLESPACE "SYSTEM" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
      NOCACHE LOGGING
      STORAGE(INITIAL 16384 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 505
      PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1
      BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
     
     
     
     
    SQL> SELECT DBMS_METADATA.GET_DDL('INDEX','SYS_IL0000000407C00040$$','SYS') FROM DUAL;
    DBMS_METADATA.GET_DDL('INDEX','SYS_IL0000000407C00040$$','SYS')
    --------------------------------------------------------------------------------
      CREATE UNIQUE INDEX "SYS"."SYS_IL0000000407C00040$$" ON "SYS"."AUD$" (
      PCTFREE 10 INITRANS 2 MAXTRANS 255
      STORAGE(INITIAL 16384 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1
      BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
      TABLESPACE "SYSTEM"
      PARALLEL (DEGREE 0 INSTANCES 0)
     
     
    SQL> SELECT DBMS_METADATA.GET_DDL('INDEX','SYS_IL0000000407C00041$$','SYS') FROM DUAL;
    DBMS_METADATA.GET_DDL('INDEX','SYS_IL0000000407C00041$$','SYS')
    --------------------------------------------------------------------------------
      CREATE UNIQUE INDEX "SYS"."SYS_IL0000000407C00041$$" ON "SYS"."AUD$" (
      PCTFREE 10 INITRANS 2 MAXTRANS 255
      STORAGE(INITIAL 16384 NEXT 16384 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1
      BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
      TABLESPACE "SYSTEM"
      PARALLEL (DEGREE 0 INSTANCES 0)
     
     
    !解决问题:创建基表则解决问题:
     
     
    疑问:切换用户生成记录? 什么记录?什么方式会记录?如何关闭:
    会话一:
    select count(*) from aud$
      COUNT(*)
    ----------
           195
    会话二:
    SQL> conn scott/tiger
     
    会话一:再次查询
    SQL> select count(*) from aud$
      COUNT(*)
    ----------     --通过connect / as sysdba / conn hr/hr切换会产生记录:
           196             --切换用户,会生成审计信息及在基表中
     
    --通过sqlplus / as sysdba操作系统验证:基表不会存储审计文件,而是参数文件:--操作系统层面:生成审计文件记录
    SQL> show parameter audit_file_dest
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ----------------------
    audit_file_dest        /picclife/app/oracle/admin/PROD/adump
     
     
    *记录的什么信息:查询基表:
     
    truncate table aud$;
    SQL> conn yang/yang
    ---记录登录信息:
     
    *精细化审计信息存储的FGA_LOG$无关:
    SQL> select count(*) from fga_log$;
      COUNT(*)
    ----------
             0
     
    *如何关闭:需要重启库:
    SQL> alter system set audit_trail=none scope=spfile;
    SQL> startup force;
    解决OK:
     

     
     
    番外:##GETDDL 索引语法:
    SQL> SELECT DBMS_METADATA.GET_DDL('INDEX','SYS_IL0000000417C00013$$','SYS') FROM DUAL;
     
     
    --查询系统表空间总大小
    SQL> select sum(bytes)/1024/1024 from dba_data_files where tablespace_name='SYSTEM';
    SUM(BYTES)/1024/1024
    --------------------
                     325
    --查询系统表空间已使用的大小
    SQL> select sum(bytes)/1024/1024 from dba_SEGMENTS  where tablespace_name='SYSTEM';
    SUM(BYTES)/1024/1024
    --------------------
                  275.25
     
    --查询系统表空间对应的数据文件名称
    SELECT FILE_NAME FROM DBA_DATA_FILES where tablespace_name='SYSTEM'
    FILE_NAME
    ---------------------------------------------
    /picclife/app/hukou/data/system01.dbf
    --增加数据文件大小
    SQL> ALTER database datafile '/picclife/app/hukou/data/system01.dbf' resize 500m;
     
     
     
  • 相关阅读:
    字符编码常识及问题解析
    leptonica 学习笔记2——pixBackgroundNormSimple
    用GitLab Runner自动部署GitBook并不难
    使用Docker搭建简易的 Java Web 环境
    ionic button笔记
    学习Sass之安装Sass(一)
    Exploring Ionic Lists
    Using the Cordova Camera API
    ionic介绍
    angularjs2 学习笔记(六) Form
  • 原文地址:https://www.cnblogs.com/lvcha001/p/8159357.html
Copyright © 2020-2023  润新知