查看DBID的方法

最新推荐文章于 2023-03-21 15:30:48 发布

cssma46022

最新推荐文章于 2023-03-21 15:30:48 发布

阅读量1.4k

点赞数

文章标签：数据库

本文详细介绍了在数据库能够mount或open以及数据库无法mount时，如何查询Oracle数据库的DBID。当数据库可正常运行时，可以通过v$database、RMAN、控制文件头的dump或使用event获取。而在数据库瘫痪状态下，可以使用dump redo log、bbed工具等特殊方法查询DBID。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

前言

DBID是DataBase IDentifier的缩写，意思就是数据库的唯一标识符。这个DBID在数据文件头和控制文件都是存在的，可以用于标示数据文件的归属。对于不同数据库来说，DBID应当不同，而db_name则可能是相同的。在我们进行数据库恢复时，有时必须要知道DBID，下面分两种情况总结了查询DBID的方法。

1. 当数据库能够mount或open时：

(1) 从v$database查询获得：

select dbid,open_mode from v$database;

(2) rman登录时，显示出dbid；

(3) 从自动备份控制文件集的名字查询DBID;

(4) 通过oracle event来获取：

a.dump数据文件头：

[oracle@bond ~]$ sqlplus / as sysdba

SQL> alter system set events 'immediate trace name file_hdrs level 3';

System altered.

SQL> oradebug setmypid;

Statement processed.

SQL> oradebug tracefile_name;

/u01/app/oracle/diag/rdbms/bond/bond/trace/bond_ora_10759.trc

查看 bond_ora_10759.trc内容，以下是部分内容：

Trace file /u01/app/oracle/diag/rdbms/bond/bond/trace/bond_ora_10759.trc

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1

System name: Linux

Node name: bond

Release: 3.10.0-327.el7.x86_64

Version: #1 SMP Thu Nov 19 22:10:57 UTC 2015

Machine: x86_64

VM name: VMWare Version: 6

Instance name: bond

Redo thread mounted by this instance: 1

Oracle process number: 28

Unix process pid: 10759, image: oracle@bond (TNS V1-V3)

*** 2019-08-17 18:00:58.374

*** SESSION ID:(36.9) 2019-08-17 18:00:58.374

*** CLIENT ID:() 2019-08-17 18:00:58.374

*** SERVICE NAME:(SYS$USERS) 2019-08-17 18:00:58.374

*** MODULE NAME:(sqlplus@bond (TNS V1-V3)) 2019-08-17 18:00:58.374

*** ACTION NAME:() 2019-08-17 18:00:58.374

DUMP OF DATA FILES: 5 files in database

DATA FILE #1:

name #7: /u01/app/oracle/oradata/bond/system01.dbf

creation size=0 block size=8192 status=0xe head=7 tail=7 dup=1

tablespace 0, index=1 krfil=1 prev_file=0

unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00

Checkpoint cnt:103 scn: 0x0000.000fcb09 08/17/2019 17:55:17

Stop scn: 0xffff.ffffffff 08/17/2019 17:50:52

Creation Checkpointed at scn: 0x0000.00000007 08/24/2013 11:37:33

thread:0 rba:(0x0.0.0)

enabled threads: 00000000 00000000 00000000 00000000 00000000 00000000

00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

00000000 00000000 00000000 00000000 00000000 00000000

Offline scn: 0x0000.000e2005 prev_range: 0

Online Checkpointed at scn: 0x0000.000e2006 11/22/2018 20:36:09

thread:1 rba:(0x1.2.0)

enabled threads: 01000000 00000000 00000000 00000000 00000000 00000000

00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

enabled threads: 01000000 00000000 00000000 00000000 00000000 00000000

00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

00000000 00000000 00000000 00000000 00000000 00000000

Hot Backup end marker scn: 0x0000.00000000

aux_file is NOT DEFINED

Plugged readony: NO

Plugin scnscn: 0x0000.00000000

Plugin resetlogs scn/timescn: 0x0000.00000000 01/01/1988 00:00:00

Foreign creation scn/timescn: 0x0000.00000000 01/01/1988 00:00:00

Foreign checkpoint scn/timescn: 0x0000.00000000 01/01/1988 00:00:00

Online move state: 0

V10 STYLE FILE HEADER:

Compatibility Vsn = 186647552=0xb200400

Db ID=1328414007=0x4f2dfd37, Db Name='BOND'

Activation ID=0=0x0

Control Seq=1233=0x4d1, File size=96000=0x17700

标红部分即是DBID和DB_NAME;

b.dump控制文件头：

SQL> alter session set events 'immediate trace name controlf level 1';

Session altered.

SQL> oradebug setmypid;

Statement processed.

SQL> oradebug tracefile_name;

/u01/app/oracle/diag/rdbms/bond/bond/trace/bond_ora_10849.trc

查看trace文件内容如下：

Trace file /u01/app/oracle/diag/rdbms/bond/bond/trace/bond_ora_10849.trc

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1

System name: Linux

Node name: bond

Release: 3.10.0-327.el7.x86_64

Version: #1 SMP Thu Nov 19 22:10:57 UTC 2015

Release: 3.10.0-327.el7.x86_64

Version: #1 SMP Thu Nov 19 22:10:57 UTC 2015

Machine: x86_64

VM name: VMWare Version: 6

Instance name: bond

Redo thread mounted by this instance: 1

Oracle process number: 28

Unix process pid: 10849, image: oracle@bond (TNS V1-V3)

*** 2019-08-17 18:11:07.910

*** SESSION ID:(36.11) 2019-08-17 18:11:07.910

VM name: VMWare Version: 6

Instance name: bond

Redo thread mounted by this instance: 1

Oracle process number: 28

Unix process pid: 10849, image: oracle@bond (TNS V1-V3)

*** 2019-08-17 18:11:07.910

*** SESSION ID:(36.11) 2019-08-17 18:11:07.910

*** CLIENT ID:() 2019-08-17 18:11:07.910

*** SERVICE NAME:(SYS$USERS) 2019-08-17 18:11:07.910

*** MODULE NAME:(sqlplus@bond (TNS V1-V3)) 2019-08-17 18:11:07.910

*** ACTION NAME:() 2019-08-17 18:11:07.910

DUMP OF CONTROL FILES, Seq # 1239 = 0x4d7

V10 STYLE FILE HEADER:

Compatibility Vsn = 186647552=0xb200400

Db ID=1328414007=0x4f2dfd37, Db Name='BOND'

Activation ID=0=0x0

Control Seq=1239=0x4d7, File size=594=0x252

File Number=0, Blksiz=16384, File Type=1 CONTROL

*** END OF DUMP ***

标红部分即为DBID和DB_NAME;

2.数据库无法mount时：

在这种情况下，数据库基本处于瘫痪状态，我们需要借用一些特殊方法：

(1) dump数据库的redo log：

拷贝一个redo log至能够正常运行的测试库中(假设路径是/oracle/redo01.log)，然后：

alter system dump logfile '/oracle/redo01.log';

执行上述命令后会生成相应的trace文件，具体的trace文件可通过如下查询：

select p.SPID from v$process p ,v$session s

where p.ADDR = s.PADDR

and s.SID in (select sid from v$mystat where rownum = 1);

然后在/u01/app/oracle/diag/rdbms/dbmon/dbmon/trace找到相应的trace文件，即可查看dbid。

(2) 通过bbed,查看控制文件/数据文件/redo文件头即可查看：

[oracle@dbmon oracle]$ cat bbed.par

blocksize=8192

listfile=/oracle/files.txt

mode=browse

[oracle@dbmon oracle]$ cat files.txt

1 /u01/app/oracle/oradata/dbmon/system01.dbf 817889280

2 /u01/app/oracle/oradata/dbmon/sysaux01.dbf 754974720

3 /u01/app/oracle/oradata/dbmon/undotbs01.dbf 146800640

4 /u01/app/oracle/oradata/dbmon/users01.dbf 5242880

5 /u01/app/oracle/oradata/dbmon/dbmon01.dbf 7864320

6 /u01/app/oracle/oradata/dbmon/newmon01.dbf 5242880

7 /u01/app/oracle/oradata/dbmon/zhan01.dbf 5242880

8 /u01/app/oracle/oradata/dbmon/CDCPUB01.dbf 23592960

9 /u01/app/oracle/oradata/dbmon/cdcsuber01.dbf 5242880

10 /oracle/am.dbf 52428800

注意这里的大小必须是MB的整数倍，四舍五入

[oracle@dbmon oracle]$ bbed parfile=bbed.par (密码：blockedit)

[oracle@dbmon oracle]$ bbed parfile=bbed.par

Password:

BBED: Release 2.0.0.0.0 - Limited Production on Thu Aug 15 10:20:29 2019

************* !!! For Oracle Internal Use only !!! ***************

BBED> show

FILE# 1

BLOCK# 1

OFFSET 0

DBA 0x00400001 (4194305 1,1)

FILENAME /u01/app/oracle/oradata/dbmon/system01.dbf

BIFILE bifile.bbd

LISTFILE /oracle/files.txt

BLOCKSIZE 8192

MODE Browse

EDIT Unrecoverable

IBASE Dec

OBASE Dec

WIDTH 80

COUNT 512

LOGFILE log.bbd

SPOOL No

BBED> info

File# Name Size(blks)

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

1 /u01/app/oracle/oradata/dbmon/system01.dbf 99840

2 /u01/app/oracle/oradata/dbmon/sysaux01.dbf 92160

3 /u01/app/oracle/oradata/dbmon/undotbs01.dbf 17920

4 /u01/app/oracle/oradata/dbmon/users01.dbf 640

5 /u01/app/oracle/oradata/dbmon/dbmon01.dbf 960

6 /u01/app/oracle/oradata/dbmon/newmon01.dbf 640

7 /u01/app/oracle/oradata/dbmon/zhan01.dbf 640

8 /u01/app/oracle/oradata/dbmon/CDCPUB01.dbf 2880

9 /u01/app/oracle/oradata/dbmon/cdcsuber01.dbf 640

10 /oracle/am.dbf 6400

BBED> set file 10

FILE# 10

BBED> map /v

File: /oracle/am.dbf (10)

Block: 1 Dba:0x02800001

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

Data File Header

struct kcvfh, 860 bytes @0

struct kcvfhbfh, 20 bytes @0

struct kcvfhhdr, 76 bytes @20

ub4 kcvfhrdb @96

struct kcvfhcrs, 8 bytes @100

ub4 kcvfhcrt @108

ub4 kcvfhrlc @112

struct kcvfhrls, 8 bytes @116

ub4 kcvfhbti @124

struct kcvfhbsc, 8 bytes @128

ub2 kcvfhbth @136

ub2 kcvfhsta @138

struct kcvfhckp, 36 bytes @484

ub4 kcvfhcpc @140

ub4 kcvfhrts @144

ub4 kcvfhccc @148

struct kcvfhbcp, 36 bytes @152

ub4 kcvfhbhz @312

struct kcvfhxcd, 16 bytes @316

sword kcvfhtsn @332

ub2 kcvfhtln @336

text kcvfhtnm[30] @338

ub4 kcvfhrfn @368

struct kcvfhrfs, 8 bytes @372

ub4 kcvfhrft @380

struct kcvfhafs, 8 bytes @384

ub4 kcvfhbbc @392

ub4 kcvfhncb @396

ub4 kcvfhmcb @400

ub4 kcvfhlcb @404

ub4 kcvfhbcs @408

ub2 kcvfhofb @412

ub2 kcvfhnfb @414

ub4 kcvfhprc @416

struct kcvfhprs, 8 bytes @420

struct kcvfhprfs, 8 bytes @428

ub4 kcvfhtrt @444

ub4 tailchk @8188

BBED> p kcvfhhdr

struct kcvfhhdr, 76 bytes @20

ub4 kccfhswv @20 0x00000000

ub4 kccfhcvn @24 0x0b200000

ub4 kccfhdbi @28 0x5682b533

text kccfhdbn[0] @32 O

text kccfhdbn[1] @33 R

text kccfhdbn[2] @34 C

text kccfhdbn[3] @35 L

text kccfhdbn[4] @36

text kccfhdbn[5] @37

text kccfhdbn[6] @38

text kccfhdbn[7] @39

ub4 kccfhcsq @40 0x00080f97

ub4 kccfhfsz @44 0x00001900

s_blkz kccfhbsz @48 0x00

ub2 kccfhfno @52 0x0012

ub2 kccfhtyp @54 0x0003

ub4 kccfhacid @56 0x00000000

ub4 kccfhcks @60 0x00000000

text kccfhtag[0] @64

text kccfhtag[1] @65

text kccfhtag[2] @66

text kccfhtag[3] @67

text kccfhtag[4] @68

text kccfhtag[5] @69

text kccfhtag[6] @70

text kccfhtag[7] @71

text kccfhtag[8] @72

text kccfhtag[9] @73

text kccfhtag[10] @74

text kccfhtag[11] @75

text kccfhtag[12] @76

text kccfhtag[13] @77

text kccfhtag[14] @78

text kccfhtag[15] @79

text kccfhtag[16] @80

text kccfhtag[17] @81

text kccfhtag[18] @82

text kccfhtag[19] @83

text kccfhtag[20] @84

text kccfhtag[21] @85

text kccfhtag[22] @86

text kccfhtag[23] @87

text kccfhtag[24] @88

text kccfhtag[25] @89

text kccfhtag[26] @90

text kccfhtag[27] @91

text kccfhtag[28] @92

text kccfhtag[29] @93

text kccfhtag[30] @94

text kccfhtag[31] @95

其中ub4 kccfhdbi @28 0x5682b533 这一行对应的即是dbid值，为16进制，转换成10进制：

select to_number('5682b533','XXXXXXXXXXXXXXX') from dual ; ---1451406643

同时上面也可看出db_name，这就是为啥db_name最大长度不能超过8位的原因了

text kccfhdbn[0] @32 O

text kccfhdbn[1] @33 R

text kccfhdbn[2] @34 C

text kccfhdbn[3] @35 L

text kccfhdbn[4] @36

text kccfhdbn[5] @37

text kccfhdbn[6] @38

text kccfhdbn[7] @39

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/69902769/viewspace-2653665/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/69902769/viewspace-2653665/