global_name为空导致的数据库不能open—–使用dd修复(使用dd拷贝块的方式)

发表于 2015 年 1 月 5 日 由 Lunar

联系：QQ(5163721)

标题：global_name为空导致的数据库不能open—–使用dd修复(使用dd拷贝块的方式)

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

GLOBAL_NAME和props$对象介绍
global_name为空导致的数据库不能open—–使用gdb修复(中断oracle启动的部分监测功能)
global_name为空导致的数据库不能open—–使用DUL修复
global_name为空导致的数据库不能open—使用BBED修复（bbed恢复update的数据）

这篇为第2种解决 global_name 为NULL导致数据库不能启动的方法。
即 从其他正常的11.2的数据库上使用dd命令克隆一个相同的block来替换现有system文件中的相同文件。
根据测试，猜测大版本一致即可，比如11.2.0.3和11.2.0.4的props$都存储在file 1 block 801上。
因此，我这里使用了11.2.0.4(基于ASM)的数据库上的file 1 block 801来替换 11.2.0.3（基于文件提醒）的数据库的file 1 block 801。

首先，props$在相同版本的数据库中，缺省的位置是固定的。知道了这个，就可以从其他数据库上检查相应的block，如果相同，直接dd过来。

首先备份当前的props$：

[oracle@lunar ~]$ dd if=/stage/lunar/system01.dbf of=lunar.log bs=8192 skip=801 count=1
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 0.00892236 seconds, 918 kB/s
[oracle@lunar ~]$ strings lunar.log|grep CHARACTER
NLS_NCHAR_CHARACTERSET  AL16UTF16
NLS_CHARACTERSET
NLS_NUMERIC_CHARACTERS
[oracle@lunar ~]$

我们知道props$表中记录了数据库字符集，global_name等等关键信息，你可以使用strings来查看其他内容。
例如下面这样，在控制文件丢失，无备份，需要重建控制文件时下面的信息就很有用，主要是需要看字符集（NLS_CHARACTERSET），我这里是AL32UTF8：

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

查看props$这个表的具体位置：

[oracle@lunar ~]$ ss

SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 6 01:20:45 2014

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> select count(*) from props$;

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

SQL> select * from props$ where name = 'GLOBAL_DB_NAME';

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


SQL> 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'
  5  /

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

SQL>

这里可以看到是file 1 block 801

现在到其他一个可以open的11.2的数据库中复制这个block出来。
方法多的很，比如，你可以直接将asm文件复制到文件，然后直接使用bbed的copy命令将这个block 复制到当前损坏的库上。
也可以使用我这样dd的方法：
首先，将asm文件复制到文件系统（bbed不能直接读asm，一般采用这样的方法）

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA/lunars/system01.dbf
+DATA/lunars/sysaux01.dbf
+DATA/lunars/undotbs01.dbf
+DATA/lunars/users01.dbf
+DATA/lunars/soe01.dbf
+DATA/lunars/lunar01.dbf

6 rows selected.

SQL>

[root@lunar ~]# su - grid
[grid@lunar ~]$ asmcmd
ASMCMD> lsdg                                          
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576      7844     4991                0            4991              0             N  DATA/
ASMCMD> cp +DATA/lunars/system01.dbf /tmp/system01.dbf
copying +DATA/lunars/system01.dbf -> /tmp/system01.dbf
ASMCMD> 

[root@lunar tmp]# ls -lrt system01.dbf 
-rw-r----- 1 grid oinstall 419438592 Aug  6 01:24 system01.dbf
[root@lunar tmp]# 

[root@lunar tmp]# dd if=/tmp/system01.dbf of=lunar_11204.props.dd bs=8192 skip=801 count=1
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 0.000535543 seconds, 15.3 MB/s
[root@lunar tmp]# ls -lrt lunar_11204.props.dd
-rw-r--r-- 1 root root 8192 Aug  6 01:26 lunar_11204.props.dd
[root@lunar tmp]# 

sftp> get /tmp/lunar_11204.props.dd
Downloading lunar_11204.props.dd from /tmp/lunar_11204.props.dd
  100% 8KB      8KB/s 00:00:00     
/tmp/lunar_11204.props.dd: 8192 bytes transferred in 0 seconds (8192 bytes/s)
sftp>

查看一下这个block的信息，可以看到，这个数据库版本(NLS_RDBMS_VERSION)是11.2.0.4，GLOBAL_DB_NAME的值是 LUNAR：

[oracle@lunar ~]$ strings lunar_11204.props.dd|grep LUNAR
LUNAR
[oracle@lunar ~]$ strings lunar_11204.props.dd
NLS_RDBMS_VERSION
11.2.0.4.0 RDBMS version for NLS parameters,  --------因为是从11.2.0.4版本的db上dd来的
NLS_NCHAR_CONV_EXCP
FALSE
NLS conversion exception,
NLS_LENGTH_SEMANTICS
BYTE
NLS length semantics,
NLS_COMP
BINARY
NLS comparison,
NLS_DUAL_CURRENCY
Dual currency symbol,
NLS_TIMESTAMP_TZ_FORMAT
DD-MON-RR HH.MI.SSXFF AM TZR
Timestamp with timezone format,
NLS_TIME_TZ_FORMAT
HH.MI.SSXFF AM TZR
Time with timezone format,
NLS_TIMESTAMP_FORMAT
DD-MON-RR HH.MI.SSXFF AM
Time stamp format,
NLS_TIME_FORMAT
HH.MI.SSXFF AM
Time format,
NLS_SORT
BINARY
Linguistic definition,
NLS_DATE_LANGUAGE
AMERICAN
Date language,
NLS_DATE_FORMAT DD-MON-RR
Date format,
NLS_CALENDAR    GREGORIAN
Calendar system,
NLS_CHARACTERSET
AL32UTF8
Character set,
NLS_NUMERIC_CHARACTERS
Numeric characters,
NLS_ISO_CURRENCY
AMERICA
ISO currency,
NLS_CURRENCY
Local currency,
NLS_TERRITORY
AMERICA Territory,
NLS_LANGUAGE
AMERICAN
Language,
GLOBAL_DB_NAME        =============》 这里GLOBAL_DB_NAME的值为LUNAR
LUNAR
Global database name,
NO_USERID_VERIFIER_SALT 6A58202A068B678B09CF571DC967F3EC,
WORKLOAD_REPLAY_MODE
bPREPARE implies external replay clients can connect; REPLAY implies workload replay is in progress,
WORKLOAD_CAPTURE_MODE
/CAPTURE implies workload capture is in progress,
EXPORT_VIEWS_VERSION
Export views revision #,
DEFAULT_PERMANENT_TABLESPACE
USERS$Name of default permanent tablespace,
GLOBAL_DB_NAME
TRAVEL
Global database name<
NLS_RDBMS_VERSION
11.2.0.3.0 RDBMS version for NLS parameters,
NLS_NCHAR_CHARACTERSET  AL16UTF16
NCHAR Character set<
NLS_NCHAR_CONV_EXCP
FALSE
NLS conversion exception<
NLS_LENGTH_SEMANTICS
BYTE
NLS length semantics<
NLS_COMP
BINARY
NLS comparison<
NLS_DUAL_CURRENCY
Dual currency symbol<
NLS_TIMESTAMP_TZ_FORMAT
DD-MON-RR HH.MI.SSXFF AM TZR
Timestamp with timezone format<
NLS_TIME_TZ_FORMAT
HH.MI.SSXFF AM TZR
Time with timezone format<
NLS_TIMESTAMP_FORMAT
DD-MON-RR HH.MI.SSXFF AM
Time stamp format<
NLS_TIME_FORMAT
HH.MI.SSXFF AM
Time format<
NLS_SORT
BINARY
Linguistic definition<
NLS_DATE_LANGUAGE
AMERICAN
Date language<
NLS_DATE_FORMAT DD-MON-RR
Date format<
NLS_CALENDAR    GREGORIAN
Calendar system<
NLS_CHARACTERSET
AL32UTF8
Character set<
NLS_NUMERIC_CHARACTERS
Numeric characters<
NLS_ISO_CURRENCY
AMERICA
ISO currency<
NLS_CURRENCY
Local currency<
NLS_TERRITORY
AMERICA Territory<
NLS_LANGUAGE
AMERICAN
Language,
DEFAULT_TBS_TYPE        SMALLFILE
Default tablespace type,
DST_SECONDARY_TT_VERSION
0'Version of secondary timezone data file,
DST_PRIMARY_TT_VERSION
14%Version of primary timezone data file,
DST_UPGRADE_STATE
NONE&State of Day Light Saving Time Upgrade,
DBTIMEZONE
-08:00
DB time zone,
TDE_MASTER_KEY_ID,
Flashback Timestamp TimeZone
GMT"Flashback timestamp created in GMT,
DEFAULT_TEMP_TABLESPACE
TEMP$Name of default temporary tablespace,
DEFAULT_EDITION
ORA$BASE$Name of the database default edition,
DEFAULT_PERMANENT_TABLESPACE
SYSTEM$Name of default permanent tablespace,
DEFAULT_TEMP_TABLESPACE
SYSTEM$Name of default temporary tablespace,
        DICT.BASE
2 dictionary base tables version #
[oracle@lunar ~]$

现在，将刚才dd出来块patch到11.2.0.3的数据库的相同位置
dd if=/home/oracle/test/lunar_11204.props.dd of=/stage/lunar/system01.dbf bs=8192 seek=801 conv=notrunc

然后直接open数据库（我这个库是11.2.0.3），可以看到数据库可以正常启动，且GLOBAL_NAME已经随着刚才dd过来的block改为LUNAR了：

[oracle@lunar ~]$ ss

SQL*Plus: Release 11.2.0.3.0 Production on Thu Jan 1 10:20:15 2015

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

Welcome Lunar's oracle world!

Connected to an idle instance.

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0


Elapsed: 00:00:00.00
SYS@lunarp>startup
Welcome Lunar's oracle world!

Love you , baby !

ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size                  2230952 bytes
Variable Size             243271000 bytes
Database Buffers          373293056 bytes
Redo Buffers                7532544 bytes
Database mounted.
Welcome Lunar's oracle world!

Love you , baby !

Database opened.
SYS@lunarp>select * from global_name;

GLOBAL_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
LUNAR

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

发表评论

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