分类目录归档：Oracle

通过基表获取segment header block

发表于 2013 年 6 月 1 日由惜分飞

数据库不能open的时候,可以通过dul挖取相关基表(user$,obj$,ts$,tab$,seg$,file$),从而来获得segment header信息,然后通过dump该block,结合shell脚本获得extents分布脚本来获得extent分布

   SELECT NVL (u.name, 'SYS'),
          o.name,
          o.subname,
          so.object_type,
          s.type#,
          DECODE (BITAND (s.spare1, 2097408),
                  2097152, 'SECUREFILE',
                  256, 'ASSM',
                  'MSSM'),
          ts.ts#,
          ts.name,
          ts.blocksize,
          f.file#,
          s.block#,
          s.blocks * ts.blocksize,
          s.blocks,
          s.extents,
          s.iniexts * ts.blocksize,
          s.extsize * ts.blocksize,
          s.minexts,
          s.maxexts,
          DECODE (BITAND (s.spare1, 4194304), 4194304, bitmapranges, NULL),
          TO_CHAR (
             DECODE (
                BITAND (s.spare1, 2097152),
                2097152, DECODE (s.lists,
                                 0, 'NONE',
                                 1, 'AUTO',
                                 2, 'MIN',
                                 3, 'MAX',
                                 4, 'DEFAULT',
                                 'INVALID'),
                NULL)),
          DECODE (BITAND (s.spare1, 2097152), 2097152, s.groups, NULL),
          DECODE (BITAND (ts.flags, 3), 1, TO_NUMBER (NULL), s.extpct),
          DECODE (BITAND (ts.flags, 32),
                  32, TO_NUMBER (NULL),
                  DECODE (s.lists, 0, 1, s.lists)),
          DECODE (BITAND (ts.flags, 32),
                  32, TO_NUMBER (NULL),
                  DECODE (s.groups, 0, 1, s.groups)),
          s.file#,
          BITAND (s.cachehint, 3),
          BITAND (s.cachehint, 12) / 4,
          BITAND (s.cachehint, 48) / 16,
          NVL (s.spare1, 0),
          o.dataobj#
     FROM chf.user$ u,
          chf.obj$ o,
          chf.ts$ ts,
          ( SELECT DECODE (BITAND (t.property, 8192), 8192, 'NESTED TABLE', 'TABLE') OBJECT_TYPE,
          2 OBJECT_TYPE_ID,
          5 SEGMENT_TYPE_ID,
          t.obj# OBJECT_ID,
          t.file# HEADER_FILE,
          t.block# HEADER_BLOCK,
          t.ts# TS_NUMBER
     FROM chf.tab$ t) so,
          chf.seg$ s,
          chf.file$ f
    WHERE     s.file# = so.header_file
          AND s.block# = so.header_block
          AND s.ts# = so.ts_number
          AND s.ts# = ts.ts#
          AND o.obj# = so.object_id
          AND o.owner# = u.user#(+)
          AND s.type# = so.segment_type_id
          AND o.type# = so.object_type_id
          AND s.ts# = f.ts#
          AND s.file# = f.relfile#
          and o.name in('XIFENFEI','T_XIFENFEI');

发表在 Oracle | 标签为 segment header extent | 评论关闭

记录一次ORA-600 4000数据库故障恢复

发表于 2013 年 5 月 30 日由惜分飞

ORA-600[4000]错误
一朋友数据库因为当前redo丢失,在恢复的过程中启动报ORA-600[4000]错误

SMON: enabling cache recovery
Thu May 30 16:24:17 2013
Errors in file /u02/oracle/app/oracle/admin/xifenfei/udump/xifenfei1_ora_1458370.trc:
ORA-00600: internal error code, arguments: [4000], [83], [], [], [], [], [], []
Thu May 30 16:24:19 2013
Errors in file /u02/oracle/app/oracle/admin/xifenfei/udump/xifenfei1_ora_1458370.trc:
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [4000], [83], [], [], [], [], [], []
Thu May 30 16:24:19 2013
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 1458370
ORA-1092 signalled during: alter database open resetlogs...

分析trace文件

*** 2013-05-30 16:24:17.979
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [4000], [83], [], [], [], [], [], []
Current SQL statement for this session:
select ctime, mtime, stime from obj$ where obj# = :1
--确定是obj$对象异常,通过某种手段找到obj$的objid和dataobjid均为16，对应16进制为12

Block header dump:  0x0040007a
 Object id on Block? Y
 seg/obj: 0x12  csc: 0xc1e.a329e76f  itc: 1  flg: -  typ: 1 - DATA
     fsl: 0  fnx: 0x0 ver: 0x01
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0053.02a.000598bd  0x0d407e46.4f52.2f  --U-    1  fsc 0x0000.a329e772

这里比较明显obj$对象在rdba为0040007a的block上,scn为0c1e.a329e76f(13325725984623)且未提交的事务,这样的现象就决定了处理的特殊性(不是因为块延迟清理导致访问undo现象,该现象直接推进scn解决,而该情况不行)

数据文件头scn

SQL> SELECT DISTINCT CHECKPOINT# FROM V$DATAFILE_HEADER;

    CHECKPOINT_CHANGE#
-------------------------
           13324676536960

bbed查看文件头scn

   struct kcvfhckp, 160 bytes               @484     
      struct kcvcpscn, 8 bytes              @484     
         ub4 kscnbas                        @484      0x649c9a80
         ub2 kscnwrp                        @488      0x0c1e

这里看到的文件头scn也是为13324676536960(0c1e.649c9a80)和sql查询结果一致,也就是说数据库中的obj$的某个对象含有事务,且scn大于文件头scn(因为当前redo丢失,无法前滚,所以出现该情况),当数据库访问obj$的时候,为了事务的一致性,就需要访问undo(这里提示为83 回滚段),而undo异常,所以smon进程回滚失败,数据库无法正常启动

使用bbed提交事务

BBED> map
 File: /oradata/sys/xifenfei/system01.dbf (1)
 Block: 122                                   Dba:0x0040007a
------------------------------------------------------------
 KTB Data Block (Table/Cluster)

 struct kcbh, 20 bytes                      @0       

 struct ktbbh, 48 bytes                     @20      

 struct kdbh, 14 bytes                      @68      

 struct kdbt[1], 4 bytes                    @82      

 sb2 kdbr[108]                              @86      

 ub1 freespace[802]                         @302     

 ub1 rowdata[7084]                          @1104    

 ub4 tailchk                                @8188    


BBED> p ktbbh
struct ktbbh, 48 bytes                      @20      
   ub1 ktbbhtyp                             @20       0x01 (KDDBTDATA)
   union ktbbhsid, 4 bytes                  @24      
      ub4 ktbbhsg1                          @24       0x00000012
      ub4 ktbbhod1                          @24       0x00000012
   struct ktbbhcsc, 8 bytes                 @28      
      ub4 kscnbas                           @28       0xa329e76f
      ub2 kscnwrp                           @32       0x0c1e
   b2 ktbbhict                              @36       1
   ub1 ktbbhflg                             @38       0x02 (NONE)
   ub1 ktbbhfsl                             @39       0x00
   ub4 ktbbhfnx                             @40       0x00000000
   struct ktbbhitl[0], 24 bytes             @44      
      struct ktbitxid, 8 bytes              @44      
         ub2 kxidusn                        @44       0x0053
         ub2 kxidslt                        @46       0x002a
         ub4 kxidsqn                        @48       0x000598bd
      struct ktbituba, 8 bytes              @52      
         ub4 kubadba                        @52       0x0d407e46
         ub2 kubaseq                        @56       0x4f52
         ub1 kubarec                        @58       0x2f
      ub2 ktbitflg                          @60       0x2001 (KTBFUPB)<--需要提交
      union _ktbitun, 2 bytes               @62      
         b2 _ktbitfsc                       @62       0
         ub2 _ktbitwrp                      @62       0x0000
      ub4 ktbitbas                          @64       0xa329e772

BBED> set count 32
        COUNT           32

BBED> set offset 60
        OFFSET          60

BBED> d
 File: /oradata/sys/xifenfei/system01.dbf (1)
 Block: 122              Offsets:   60 to   91           Dba:0x0040007a
------------------------------------------------------------------------
 20010000 a329e772 0001006c ffff00ea 040c0368 03680000 006c1f7c 1f3c1efb 

 <32 bytes per line>

BBED> m /x 8001
 File: /oradata/sys/xifenfei/system01.dbf (1)
 Block: 122              Offsets:   60 to   91           Dba:0x0040007a
------------------------------------------------------------------------
 80010000 a329e772 0001006c ffff00ea 040c0368 03680000 006c1f7c 1f3c1efb 

 <32 bytes per line>

BBED> sum apply
Check value for File 1, Block 122:
current = 0xafd6, required = 0xafd6

尝试open数据库ORA-600[2662]解决

Thu May 30 21:16:00 2013
Errors in file /u02/oracle/app/oracle/admin/xifenfei/bdump/xifenfei1_smon_819664.trc:
ORA-00600: internal error code, arguments:[2662],[3102],[2737532996],[3102],[2745973074],[4194397],[],[]
Non-fatal internal error happenned while SMON was doing non-existent object cleanup.
SMON encountered 1 out of maximum 100 non-fatal internal errors.
Thu May 30 21:16:01 2013
Trace dumping is performing id=[cdmp_20130530211601]
Thu May 30 21:16:02 2013
Errors in file /u02/oracle/app/oracle/admin/xifenfei/bdump/xifenfei1_smon_819664.trc:
ORA-00600: internal error code, arguments:[2662],[3102],[2737532997],[3102],[2745973074],[4194397],[],[]
Thu May 30 21:16:03 2013
Non-fatal internal error happenned while SMON was doing logging scn->time mapping.
SMON encountered 2 out of maximum 100 non-fatal internal errors.
Thu May 30 21:16:05 2013
Errors in file /u02/oracle/app/oracle/admin/xifenfei/bdump/xifenfei1_smon_819664.trc:
ORA-00600: internal error code, arguments:[2662],[3102],[2737532997],[3102],[2745973074],[4194397],[],[]
Thu May 30 21:16:08 2013
Errors in file /u02/oracle/app/oracle/admin/xifenfei/bdump/xifenfei1_pmon_958764.trc:
ORA-00474: SMON process terminated with error
Thu May 30 21:16:08 2013
PMON: terminating instance due to error 474

数据库在open过程中遇到大量ORA-00600[2662],这个是因为数据库中文件头的scn小于访问的数据块scn导致该问题,解决方法推荐scn,如果数据库的scn本身就很大(和时间理论scn较接近),推进过程中可能遇到如下错误,这个时候就需要选择合适的方法/合适的值来推进scn

SQL> startup pfile=/home/oracle/pfile force
ORACLE instance started.

Total System Global Area 5.5835E+10 bytes
Fixed Size                  2177056 bytes
Variable Size            3.2867E+10 bytes
Database Buffers         2.2951E+10 bytes
Redo Buffers               14598144 bytes
Database mounted.
ORA-01052: required destination LOG_ARCHIVE_DUPLEX_DEST is not specified

后面的工作因为没有redo前滚,而且该库故障时有大量事务在跑,现在无法前滚,导致大量的undo回滚段异常,index和data不一致等故障,需要做的就是屏蔽undo seg,重建undo,重建库

发表在非常规恢复 | 标签为 ORA-600 4000, ORA-600[4000], ORACLE恢复 | 评论关闭

Viewing Information About CDBs and PDBs

发表于 2013 年 5 月 26 日由惜分飞

在ORACLE 12C中引入了CDB和PDB的概念,实现了ORACLE数据库的可插拔,在一个CDB数据库中,有多个PDB,而每一个PDB又可以理解为一个独立的传统ORACLE 数据库,那为了能够通过一个sql查询获得整个CDB数据库的信息,ORALCE 引入了CDB_*开头的视图,该视图就是在传统的DBA_*视图基础之上增加了CON_ID,用来区分不同的PDB,从而实现了一个简单sql查询在有足够权限的情况下,可以查询所有PDB中信息
ORACLE 12C版本

SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0
PL/SQL Release 12.1.0.1.0 - Production                                                    0
CORE    12.1.0.1.0      Production                                                        0
TNS for Linux: Version 12.1.0.1.0 - Production                                            0
NLSRTL Version 12.1.0.1.0 - Production                                                    0

确定是否是CDB

SQL> SELECT CDB FROM V$DATABASE;

CDB
---
YES

YES表示该数据库是CDB,如果是NO表示是NO-CDB(普通数据库)

CDB中各容器信息

SQL> COLUMN NAME FORMAT A8
SQL> SELECT NAME, CON_ID, DBID, CON_UID, GUID FROM V$CONTAINERS ORDER BY CON_ID;

NAME         CON_ID       DBID    CON_UID GUID
-------- ---------- ---------- ---------- --------------------------------
CDB$ROOT          1 1922813718          1 DB85A3D39F8E7703E0431CAAE80A8C44
PDB$SEED          2 4048821679 4048821679 D49B80694E4A449BE0430100007F906F
PDB1              3 3313918585 3313918585 D49BF37938FB4C10E0430100007F6CBD
PDB2              4 3872456618 3872456618 D49BFA33332F4C3EE0430100007FA059

SQL> select CON_NAME_TO_ID('PDB1') FROM DUAL;

CON_NAME_TO_ID('PDB1')
----------------------
                     3

SQL> SELECT CON_DBID_TO_ID(3313918585) FROM DUAL;

CON_DBID_TO_ID(3313918585)
--------------------------
                         3

SQL> SELECT CON_UID_TO_ID(3313918585) FROM DUAL;

CON_UID_TO_ID(3313918585)
-------------------------
                        3

PDB部分信息

SQL> COLUMN PDB_NAME FORMAT A15
SQL> SELECT PDB_ID, PDB_NAME, STATUS FROM DBA_PDBS ORDER BY PDB_ID;

    PDB_ID PDB_NAME        STATUS
---------- --------------- -------------
         2 PDB$SEED        NORMAL
         3 PDB1            NORMAL
         4 PDB2            NORMAL

SQL> COLUMN NAME FORMAT A15
SQL> COLUMN RESTRICTED FORMAT A10
SQL> COLUMN OPEN_TIME FORMAT A30
SQL> SELECT NAME, OPEN_MODE, RESTRICTED, OPEN_TIME FROM V$PDBS;

NAME            OPEN_MODE  RESTRICTED OPEN_TIME
--------------- ---------- ---------- ------------------------------
PDB$SEED        READ ONLY  NO         12-MAY-13 08.51.53.177 AM
PDB1            READ WRITE NO         12-MAY-13 09.35.48.646 AM
PDB2            MOUNTED               12-MAY-13 08.56.59.859 AM

CDB中查询对象信息

SQL> COLUMN PDB_NAME FORMAT A15
SQL> COLUMN OWNER FORMAT A15
SQL> COLUMN TABLE_NAME FORMAT A30
SQL> SELECT p.PDB_ID, p.PDB_NAME, t.OWNER, t.TABLE_NAME
  2  FROM DBA_PDBS p, CDB_TABLES t
  3  WHERE p.PDB_ID > 2 
  4  AND T.TABLE_NAME='COL$'
  5  AND p.PDB_ID = t.CON_ID
  6  ORDER BY p.PDB_ID;

    PDB_ID PDB_NAME        OWNER           TABLE_NAME
---------- --------------- --------------- ------------------------------
         3 PDB1            SYS             COL$
         4 PDB2            SYS             COL$

查询在CDB中的PDB数据/临时文件信息

SQL> COLUMN PDB_ID FORMAT 999
SQL> COLUMN PDB_NAME FORMAT A8
SQL> COLUMN FILE_ID FORMAT 9999
SQL> COLUMN TABLESPACE_NAME FORMAT A10
SQL> COLUMN FILE_NAME FORMAT A45
SQL> SELECT p.PDB_ID, p.PDB_NAME, d.FILE_ID, d.TABLESPACE_NAME, d.FILE_NAME
  2  FROM DBA_PDBS p, CDB_DATA_FILES d
  3  WHERE p.PDB_ID = d.CON_ID
  4  ORDER BY p.PDB_ID;

PDB_ID PDB_NAME FILE_ID TABLESPACE FILE_NAME
------ -------- ------- ---------- ---------------------------------------------
     2 PDB$SEED       5 SYSTEM     +DATA/pdb$seed_system01.dbf
     2 PDB$SEED       7 SYSAUX     +DATA/pdb$seed_sysaux01.dbf
     3 PDB1           9 SYSAUX     +DATA/pdb1_pdb$seed_sysaux01.dbf
     3 PDB1          10 USERS      +DATA/cdb/pdb1_users01.dbf
     3 PDB1           8 SYSTEM     +DATA/pdb1_pdb$seed_system01.dbf
     4 PDB2          13 USERS      +DATA/cdb/pdb2_users01.dbf
     4 PDB2          12 SYSAUX     +DATA/pdb2_pdb$seed_sysaux01.dbf
     4 PDB2          11 SYSTEM     +DATA/pdb2_pdb$seed_system01.dbf

8 rows selected.

SQL> COLUMN CON_ID FORMAT 999
SQL> COLUMN FILE_ID FORMAT 9999
SQL> COLUMN TABLESPACE_NAME FORMAT A15
SQL> COLUMN FILE_NAME FORMAT A45
SQL> SELECT CON_ID, FILE_ID, TABLESPACE_NAME, FILE_NAME
  2  FROM CDB_TEMP_FILES
  3  ORDER BY CON_ID;

CON_ID FILE_ID TABLESPACE_NAME FILE_NAME
------ ------- --------------- ---------------------------------------------
     1       1 TEMP            +DATA/cdb/temp01.dbf
     2       2 TEMP            +DATA/pdbseed_temp01.dbf
     3       3 TEMP            +DATA/pdb1_temp01.dbf
     4       4 TEMP            +DATA/pdb2_temp01.dbf

查询PDB的service信息

SQL> COLUMN NETWORK_NAME FORMAT A30
SQL> COLUMN PDB FORMAT A15
SQL> COLUMN CON_ID FORMAT 999
SQL> SELECT PDB, NETWORK_NAME, CON_ID FROM CDB_SERVICES
  2  WHERE PDB IS NOT NULL AND
  3  CON_ID > 2
  4  ORDER BY PDB;

PDB             NETWORK_NAME                   CON_ID
--------------- ------------------------------ ------
PDB1            pdb1                                3
PDB2            pdb2                                4

PDB中可以修改参数

SELECT NAME FROM V$SYSTEM_PARAMETER
WHERE ISPDB_MODIFIABLE = 'TRUE'
ORDER BY NAME;

查看PDB历史信息

SQL> COLUMN DB_NAME FORMAT A10
SQL> COLUMN CON_ID FORMAT 999
SQL> COLUMN PDB_NAME FORMAT A15
SQL> COLUMN OPERATION FORMAT A16
SQL> COLUMN OP_TIMESTAMP FORMAT A10
SQL> COLUMN CLONED_FROM_PDB_NAME FORMAT A15
SQL> SELECT DB_NAME, CON_ID, PDB_NAME, OPERATION, OP_TIMESTAMP, CLONED_FROM_PDB_NAME
  2  FROM CDB_PDB_HISTORY
  3  WHERE CON_ID > 2
  4  ORDER BY CON_ID;

DB_NAME    CON_ID PDB_NAME        OPERATION        OP_TIMESTA CLONED_FROM_PDB
---------- ------ --------------- ---------------- ---------- ---------------
SEEDDATA        3 PDB$SEED        UNPLUG           29-APR-13
CDB             3 PDB1            CREATE           01-FEB-13  PDB$SEED
CDB             3 PDB$SEED        PLUG             01-FEB-13  PDB$SEED
SEEDDATA        4 PDB$SEED        UNPLUG           29-APR-13
CDB             4 PDB2            CREATE           01-FEB-13  PDB$SEED
CDB             4 PDB$SEED        PLUG             01-FEB-13  PDB$SEED

6 rows selected.

发表在 ORACLE 12C | 一条评论

17813235971

QQ 咨询

分类目录归档：Oracle

通过基表获取segment header block

记录一次ORA-600 4000数据库故障恢复

Viewing Information About CDBs and PDBs

文章分类

最近发表

分类目录归档：Oracle

通过基表获取segment header block

记录一次ORA-600 4000数据库故障恢复

Viewing Information About CDBs and PDBs

标签云

文章分类

最近发表