GLOBAL_NAME和props$对象介绍

联系:QQ(5163721)

标题:GLOBAL_NAME和props$对象介绍

作者:Lunar©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]

有朋友遇到一个老问题,将数据库 global_name 置空后,数据库不能open。
这类问题已经是几年前的老问题了,这里用4中方法解决。
这一篇先大概介绍一下GLOBAL_NAME对象的来龙去脉。
GLOBAL_NAME和props$对象介绍
global_name为空导致的数据库不能open—–使用gdb修复(中断oracle启动的部分监测功能)
global_name为空导致的数据库不能open—–使用dd修复(使用dd拷贝块的方式)
global_name为空导致的数据库不能open—–使用DUL修复
global_name为空导致的数据库不能open—使用BBED修复(bbed恢复update的数据)


我们知道,全局数据库名是在分布式数据库系统中用于标识数据库的唯一名称,默认为DB_NAME.DB_DOMAIN。
该默认值在数据库创建的时候被标记,如果数据库创建后手工修改了DB_NAME或者DB_DOMAIN,全局数据库名称仍然保持为数据库创建时候的DB_NAME.DB_DOMAIN。

SYS@lunarp>select * from GLOBAL_NAME;

GLOBAL_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
lunarbb

Elapsed: 00:00:00.01
SYS@lunarp>desc GLOBAL_NAME
 Name                                                                                Null?    Type
 ----------------------------------------------------------------------------------- -------- --------------------------------------------------------
 GLOBAL_NAME                                                                                  VARCHAR2(4000)

SYS@lunarp>

我们查看一下GLOBAL_NAME是什么类型的对象,其定义是怎样的:

SYS@lunarp>col object_name for a50
SYS@lunarp>select owner,object_name,object_type from dba_objects where object_name=upper('global_name');

OWNER                          OBJECT_NAME                                        OBJECT_TYPE
------------------------------ -------------------------------------------------- -------------------
SYS                            GLOBAL_NAME                                        VIEW
PUBLIC                         GLOBAL_NAME                                        SYNONYM

Elapsed: 00:00:00.00
SYS@lunarp>select dbms_metadata.get_ddl('VIEW','GLOBAL_NAME','SYS') from dual;


  CREATE OR REPLACE FORCE VIEW "SYS"."GLOBAL_NAME" ("GLOBAL_NAME") AS
  select value$ from sys.props$ where name = 'GLOBAL_DB_NAME'


Elapsed: 00:00:00.02
SYS@lunarp>select dbms_metadata.get_ddl('SYNONYM','GLOBAL_NAME','PUBLIC') from dual;


  CREATE OR REPLACE PUBLIC SYNONYM "GLOBAL_NAME" FOR "SYS"."GLOBAL_NAME"


Elapsed: 00:00:00.10
SYS@lunarp>

可以看到,GLOBAL_NAME实际上是已于sys.props$ where name = ‘GLOBAL_DB_NAME’的一张视图和同义词。
props$中总共多少行数据,在相同版本是固定的,我这里是11.2,因此共36行:

SYS@lunarp>select count(*) from props$;

  COUNT(*)
----------
        36

Elapsed: 00:00:00.01
SYS@lunarp>

props$的定义如下:

SYS@lunarp>select dbms_metadata.get_ddl('TABLE','PROPS$','SYS') from dual;


  CREATE TABLE "SYS"."PROPS$"
   (    "NAME" VARCHAR2(30) NOT NULL ENABLE,
        "VALUE$" VARCHAR2(4000),
        "COMMENT$" VARCHAR2(4000)
   ) 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 FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "SYSTEM"


Elapsed: 00:00:03.89
SYS@lunarp>

看一下props$对象存储了哪些内容:

SYS@lunarp>select * from props$;

NAME                           VALUE$                                             COMMENT$
------------------------------ -------------------------------------------------- --------------------------------------------------
DICT.BASE                      2                                                  dictionary base tables version #
DEFAULT_TEMP_TABLESPACE        TEMP                                               Name of default temporary tablespace
DEFAULT_PERMANENT_TABLESPACE   USERS                                              Name of default permanent tablespace
DEFAULT_EDITION                ORA$BASE                                           Name of the database default edition
Flashback Timestamp TimeZone   GMT                                                Flashback timestamp created in GMT
TDE_MASTER_KEY_ID
DBTIMEZONE                     -08:00                                             DB time zone
DST_UPGRADE_STATE              NONE                                               State of Day Light Saving Time Upgrade
DST_PRIMARY_TT_VERSION         14                                                 Version of primary timezone data file
DST_SECONDARY_TT_VERSION       0                                                  Version of secondary timezone data file
DEFAULT_TBS_TYPE               SMALLFILE                                          Default tablespace type
NLS_LANGUAGE                   AMERICAN                                           Language
NLS_TERRITORY                  AMERICA                                            Territory
NLS_CURRENCY                   $                                                  Local currency
NLS_ISO_CURRENCY               AMERICA                                            ISO currency
NLS_NUMERIC_CHARACTERS         .,                                                 Numeric characters
NLS_CHARACTERSET               AL32UTF8                                           Character set
NLS_CALENDAR                   GREGORIAN                                          Calendar system
NLS_DATE_FORMAT                DD-MON-RR                                          Date format
NLS_DATE_LANGUAGE              AMERICAN                                           Date language
NLS_SORT                       BINARY                                             Linguistic definition
NLS_TIME_FORMAT                HH.MI.SSXFF AM                                     Time format
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM                           Time stamp format
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR                                 Time with timezone format
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR                       Timestamp with timezone format
NLS_DUAL_CURRENCY              $                                                  Dual currency symbol
NLS_COMP                       BINARY                                             NLS comparison
NLS_LENGTH_SEMANTICS           BYTE                                               NLS length semantics
NLS_NCHAR_CONV_EXCP            FALSE                                              NLS conversion exception
NLS_NCHAR_CHARACTERSET         AL16UTF16                                          NCHAR Character set
NLS_RDBMS_VERSION              11.2.0.3.0                                         RDBMS version for NLS parameters
GLOBAL_DB_NAME                                                                    Global database name
EXPORT_VIEWS_VERSION           8                                                  Export views revision #
WORKLOAD_CAPTURE_MODE                                                             CAPTURE implies workload capture is in progress
WORKLOAD_REPLAY_MODE                                                              PREPARE implies external replay clients can connec
                                                                                  t; REPLAY implies workload replay is in progress

NO_USERID_VERIFIER_SALT        6A58202A068B678B09CF571DC967F3EC

36 rows selected.

Elapsed: 00:00:00.00
SYS@lunarp>

当数据库正常启动时,会对PROPS$表进行全表扫描来获取PROPS$中是否含有错误信息。
一般升级的时候系统会将数据库引导对象的错误信息记录到该表的BOOTSTRAP_UPGRADE_ERROR中:

SYS@lunarp>select * from props$ where name = 'GLOBAL_DB_NAME';

NAME                           VALUE$                         COMMENT$
------------------------------ ------------------------------ --------------------------------------------------
GLOBAL_DB_NAME                 lunarbb                        Global database name

Elapsed: 00:00:00.01
SYS@lunarp>

启动时10046跟踪的trace中:

PARSING IN CURSOR #140086496404128 len=60 dep=1 uid=0 oct=3 lid=0 tim=1407167733432924 hv=3306824342 ad='8503c550' sqlid='g64r07v2jn8nq'
SELECT NULL FROM PROPS$ WHERE NAME='BOOTSTRAP_UPGRADE_ERROR'
END OF STMT
PARSE #140086496404128:c=17997,e=76437,p=3,cr=12,cu=0,mis=1,r=0,dep=1,og=4,plh=415205717,tim=1407167733432919
EXEC #140086496404128:c=0,e=44,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=415205717,tim=1407167733433101
WAIT #140086496404128: nam='db file sequential read' ela= 60288 file#=1 block#=800 blocks=1 obj#=98 tim=1407167733493464
WAIT #140086496404128: nam='db file sequential read' ela= 14273 file#=1 block#=801 blocks=1 obj#=98 tim=1407167733507890
FETCH #140086496404128:c=999,e=74855,p=2,cr=3,cu=0,mis=0,r=0,dep=1,og=4,plh=415205717,tim=1407167733507990
STAT #140086496404128 id=1 cnt=0 pid=0 pos=1 obj=98 op='TABLE ACCESS FULL PROPS$ (cr=3 pr=2 pw=0 time=74849 us)'

从这里我们看到,读取了file#=1 block#=800和file#=1 block#=801,他们分别为props$的段头和第一个数据块的位置。
他们的对象号是obj#=98,也就是props$。
从数据库中,我们也可以证实这一点:

SYS@lunarp>select owner,segment_name,HEADER_FILE,HEADER_BLOCK from dba_segments where SEGMENT_NAME='PROPS$';

OWNER                          SEGMENT_NAME                                       HEADER_FILE HEADER_BLOCK
------------------------------ -------------------------------------------------- ----------- ------------
SYS                            PROPS$                                                       1          800

Elapsed: 00:00:00.01
SYS@lunarp>

SYS@lunarp>select dbms_rowid.rowid_relative_fno(rowid) file#,
  2        dbms_rowid.rowid_block_number(rowid) blk#
  3   from sys.props$
  4  where name = 'GLOBAL_DB_NAME';

     FILE#       BLK#
---------- ----------
         1        801

Elapsed: 00:00:00.10
SYS@lunarp>select obj#,dataobj#,owner#,name from obj$ where obj#=98;

      OBJ#   DATAOBJ#     OWNER# NAME
---------- ---------- ---------- ------------------------------
        98         98          0 PROPS$

Elapsed: 00:00:00.01
SYS@lunarp>
此条目发表在 Internal 分类目录,贴了 , 标签。将固定链接加入收藏夹。

发表评论

电子邮件地址不会被公开。 必填项已用 * 标注