如何在磁盘组不能mount的情况下得到所有数据文件的名称

Posted: February 27th, 2012

当磁盘组还能mount的时候,你可以把数据库启动到mount状态后通过查询v$datafile即可得到所有的数据文件的名称。

现在我们来看一种极端的情况:你的数据库已经垮掉了,且磁盘组已经不能mount这种情况下数据库已经不能启动到mount状态,自然v$datafile也就查询不了了。

那这种情况下我们又要恢复数据,我们应该怎么办?

ODU可以轻易的解决上述问题,我们来看一个实例:

 

我们现在shutdown Oracle实例和ASM实例:

[root@bspdev odu]# su – oracle

[oracle@bspdev ~]$ sqlplus ‘/ as sysdba’;

 

SQL*Plus: Release 11.2.0.1.0 Production on Thu Feb 23 10:43:36 2012

 

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

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

 

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

 

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

 

[oracle@bspdev ~]$ su – grid

Password:

[grid@bspdev ~]$ sqlplus ‘/ as sysasm’;

 

SQL*Plus: Release 11.2.0.1.0 Production on Thu Feb 23 10:45:05 2012

 

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

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production

With the Automatic Storage Management option

 

SQL> shutdown immediate

ASM diskgroups dismounted

ASM instance shutdown

 

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production

With the Automatic Storage Management option

 

[grid@bspdev ~]$ crsctl status res

NAME=ora.DATA.dg

TYPE=ora.diskgroup.type

TARGET=OFFLINE

STATE=OFFLINE

 

NAME=ora.LISTENER.lsnr

TYPE=ora.listener.type

TARGET=ONLINE

STATE=ONLINE on bspdev

 

NAME=ora.RECO.dg

TYPE=ora.diskgroup.type

TARGET=OFFLINE

STATE=OFFLINE

 

NAME=ora.asm

TYPE=ora.asm.type

TARGET=OFFLINE

STATE=OFFLINE

 

NAME=ora.cssd

TYPE=ora.cssd.type

TARGET=ONLINE

STATE=ONLINE on bspdev

 

NAME=ora.diskmon

TYPE=ora.diskmon.type

TARGET=ONLINE

STATE=ONLINE on bspdev

 

NAME=ora.ora11g.db

TYPE=ora.database.type

TARGET=OFFLINE

STATE=OFFLINE

 

ASM diskgroup不能mount的情况下asmcmd不能使用:

[grid@bspdev ~]$ asmcmd

Connected to an idle instance.

ASMCMD> ls

ASMCMD-08102: no connection to ASM; command requires ASM to run

 

但此时ODU内嵌的asmcmd是可以使用的,所以我们可以轻易的使用ODU内嵌的asmcmd命令来得到所有的datafile的名称

[grid@bspdev ~]$ su –

Password:

[root@bspdev ~]# cd /u01/app/oracle/odu

[root@bspdev odu]# ./odu

 

Oracle Data Unloader:Release 4.2.1

 

Copyright (c) 2008,2009,2010,2011 XiongJun. All rights reserved.

 

Web: http://www.oracleodu.com

Email: magic007cn@gmail.com

 

loading default config…….

 

byte_order little

block_size  8192

block_buffers 1024

db_timezone -7

Invalid db timezone:-7

client_timezone 8

Invalid client timezone:8

asmfile_extract_path   /odu/asmfile

data_path   data

lob_path    /odu/data/lob

charset_name AL32UTF8

ncharset_name AL16UTF16

output_format text

lob_storage infile

clob_byte_order big

trace_level 1

delimiter |

unload_deleted no

file_header_offset 0

is_tru64 no

record_row_addr no

convert_clob_charset yes

use_scanned_lob  yes

trim_scanned_blob yes

lob_switch_dir_rows 20000

db_block_checksum yes

db_block_checking yes

rdba_file_bits 10

compatible 10

load config file ‘config.txt’ successful

loading default asm disk file ……

 

 

grp# dsk# bsize ausize disksize diskname        groupname       path

—- —- —– —— ——– ————— ————— ——————————————–

   1    0  4096  1024K     9000 DATA_0000       DATA            /dev/sda3

   1    1  4096  1024K     9000 DATA_0001       DATA            /dev/sda5

   1    2  4096  1024K     9000 DATA_0002       DATA            /dev/sda6

   2    0  4096  1024K     9000 RECO_0000       RECO            /dev/sda7

   2    1  4096  1024K     7288 RECO_0001       RECO            /dev/sda8

 

load asm disk file ‘asmdisk.txt’ successful

loading default control file ……

 

 

 ts#   fn  rfn bsize   blocks bf offset filename

—- —- —- —– ——– — —— ——————————————–

load control file ‘control.txt’ successful

loading dictionary data……done

 

loading scanned data……done

 

ODU> asmcmd 

 

Entering asmcmd module.

 

ASMCMD> ls

 

Current directory: <root>

 

Disk Group

————————————

DATA

RECO

 

ASMCMD> cd +DATA

 

Current directory: +DATA

 

ASMCMD> ls  

 

Current directory: +DATA

 

Name                                   

—————————————-

ASM                                       <DIR>

ORA11G                                    <DIR>

 

ASMCMD> cd ORA11G

 

Current directory: +DATA/ORA11G

 

ASMCMD> ls

 

Current directory: +DATA/ORA11G

 

Name                                   

—————————————-

DATAFILE                                  <DIR>

CONTROLFILE                               <DIR>

ONLINELOG                                 <DIR>

TEMPFILE                                  <DIR>

PARAMETERFILE                             <DIR>

spfileora11g.ora                           => +DATA.265.747311071

 

ASMCMD> cd CONTROLFILE 

 

Current directory: +DATA/ORA11G/CONTROLFILE

 

可以看到,当前的control文件名称为+DATA/ora11g/controlfile/current.260.747310619

ASMCMD> ls

 

Current directory: +DATA/ORA11G/CONTROLFILE

 

Name                                   

—————————————-

Current.260.747310619                  

 

ASMCMD> cd ..

 

Current directory: +DATA/ORA11G

 

ASMCMD> ls

 

Current directory: +DATA/ORA11G

 

Name                                   

—————————————-

DATAFILE                                  <DIR>

CONTROLFILE                               <DIR>

ONLINELOG                                 <DIR>

TEMPFILE                                  <DIR>

PARAMETERFILE                             <DIR>

spfileora11g.ora                           => +DATA.265.747311071

 

ASMCMD> cd DATAFILE   

 

Current directory: +DATA/ORA11G/DATAFILE

 

ASMCMD> ls

 

Current directory: +DATA/ORA11G/DATAFILE

 

Name                                   

—————————————-

SYSTEM.256.747310449                   

SYSAUX.257.747310449                   

UNDOTBS1.258.747310451                 

USERS.259.747310451                    

MYTEST.266.761050749                   

GAOZCINDEX.267.770299335               

GAOZCINDEX1                                => +DATA.267.770299335

GAOZCDATA.268.770299347                

GAOZCDATA1                                 => +DATA.268.770299347

 

退出ODU后将上述内容(注意要带上完整路径)填入ODU控制文件control.txt中:

[root@bspdev odu]# cat control.txt

#ts fno   rfno     filename                                          block_size  is_big_file header_offset blocks

0 0 0 +DATA/ORA11G/DATAFILE/SYSTEM.256.747310449                   

0 0 0 +DATA/ORA11G/DATAFILE/SYSAUX.257.747310449                   

0 0 0 +DATA/ORA11G/DATAFILE/UNDOTBS1.258.747310451                 

0 0 0 +DATA/ORA11G/DATAFILE/USERS.259.747310451

 

再次进入ODU此时即可产生ODU license文件oductl.txt:

[root@bspdev odu]# ./odu

 

Oracle Data Unloader:Release 4.2.1

 

Copyright (c) 2008,2009,2010,2011 XiongJun. All rights reserved.

 

Web: http://www.oracleodu.com

Email: magic007cn@gmail.com

 

loading default config…….

 

byte_order little

block_size  8192

block_buffers 1024

db_timezone -7

Invalid db timezone:-7

client_timezone 8

Invalid client timezone:8

asmfile_extract_path   /odu/asmfile

data_path   data

lob_path    /odu/data/lob

charset_name AL32UTF8

ncharset_name AL16UTF16

output_format text

lob_storage infile

clob_byte_order big

trace_level 1

delimiter |

unload_deleted no

file_header_offset 0

is_tru64 no

record_row_addr no

convert_clob_charset yes

use_scanned_lob  yes

trim_scanned_blob yes

lob_switch_dir_rows 20000

db_block_checksum yes

db_block_checking yes

rdba_file_bits 10

compatible 10

load config file ‘config.txt’ successful

loading default asm disk file ……

 

 

grp# dsk# bsize ausize disksize diskname        groupname       path

—- —- —– —— ——– ————— ————— ——————————————–

   1    0  4096  1024K     9000 DATA_0000       DATA            /dev/sda3

   1    1  4096  1024K     9000 DATA_0001       DATA            /dev/sda5

   1    2  4096  1024K     9000 DATA_0002       DATA            /dev/sda6

   2    0  4096  1024K     9000 RECO_0000       RECO            /dev/sda7

   2    1  4096  1024K     7288 RECO_0001       RECO            /dev/sda8

 

load asm disk file ‘asmdisk.txt’ successful

loading default control file ……

 

 

 ts#   fn  rfn bsize   blocks bf offset filename

—- —- —- —– ——– — —— ——————————————–

   0    1    1  8192   112640 N       0 +DATA/ORA11G/DATAFILE/SYSTEM.256.747310449

   1    2    2  8192   119040 N       0 +DATA/ORA11G/DATAFILE/SYSAUX.257.747310449

   2    3    3  8192    70400 N       0 +DATA/ORA11G/DATAFILE/UNDOTBS1.258.747310451

   4    4    4  8192     4960 N       0 +DATA/ORA11G/DATAFILE/USERS.259.747310451

load control file ‘control.txt’ successful

loading dictionary data……done

 

loading scanned data……done

 

ODU> save control 

 

The file write completed.

ODU> exit

ODU>

[root@bspdev odu]# cat oductl.txt

0|1|1|+DATA/ORA11G/DATAFILE/SYSTEM.256.747310449|8192|N|0|112640

1|2|2|+DATA/ORA11G/DATAFILE/SYSAUX.257.747310449|8192|N|0|119040

2|3|3|+DATA/ORA11G/DATAFILE/UNDOTBS1.258.747310451|8192|N|0|70400

4|4|4|+DATA/ORA11G/DATAFILE/USERS.259.747310451|8192|N|0|4960

 

按照ODU用户手册中描述那样得到ODU license文件oductl.dat再次进入ODU即可正常使用ODU企业版

[root@bspdev odu]# ./odu

 

Oracle Data Unloader:Release 4.2.1

 

Copyright (c) 2008,2009,2010,2011 XiongJun. All rights reserved.

 

Web: http://www.oracleodu.com

Email: magic007cn@gmail.com

 

loading default config…….

 

byte_order little

block_size  8192

block_buffers 1024

db_timezone -7

Invalid db timezone:-7

client_timezone 8

Invalid client timezone:8

asmfile_extract_path   /odu/asmfile

data_path   data

lob_path    /odu/data/lob

charset_name AL32UTF8

ncharset_name AL16UTF16

output_format text

lob_storage infile

clob_byte_order big

trace_level 1

delimiter |

unload_deleted no

file_header_offset 0

is_tru64 no

record_row_addr no

convert_clob_charset yes

use_scanned_lob  yes

trim_scanned_blob yes

lob_switch_dir_rows 20000

db_block_checksum yes

db_block_checking yes

rdba_file_bits 10

compatible 10

load config file ‘config.txt’ successful

loading default asm disk file ……

 

 

grp# dsk# bsize ausize disksize diskname        groupname       path

—- —- —– —— ——– ————— ————— ——————————————–

   1    0  4096  1024K     9000 DATA_0000       DATA            /dev/sda3

   1    1  4096  1024K     9000 DATA_0001       DATA            /dev/sda5

   1    2  4096  1024K     9000 DATA_0002       DATA            /dev/sda6

   2    0  4096  1024K     9000 RECO_0000       RECO            /dev/sda7

   2    1  4096  1024K     7288 RECO_0001       RECO            /dev/sda8

 

load asm disk file ‘asmdisk.txt’ successful

loading default control file ……

 

 

 ts#   fn  rfn bsize   blocks bf offset filename

—- —- —- —– ——– — —— ——————————————–

   0    1    1  8192   112640 N       0 +DATA/ORA11G/DATAFILE/SYSTEM.256.747310449

   1    2    2  8192   119040 N       0 +DATA/ORA11G/DATAFILE/SYSAUX.257.747310449

   2    3    3  8192    70400 N       0 +DATA/ORA11G/DATAFILE/UNDOTBS1.258.747310451

   4    4    4  8192     4960 N       0 +DATA/ORA11G/DATAFILE/USERS.259.747310451

load control file ‘oductl.dat’ successful

loading dictionary data……done

 

loading scanned data……done

 

上述控制文件的名称其实我们从alert log里也能看到,你可以看到这里的结果跟我们用ODU内嵌的asmcmd查到的结果一致:

Thu Feb 23 08:41:05 2012

Starting ORACLE instance (normal)

LICENSE_MAX_SESSION = 0

LICENSE_SESSIONS_WARNING = 0

Picked latch-free SCN scheme 2

Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST

Autotune of undo retention is turned on.

IMODE=BR

ILAT =27

LICENSE_MAX_USERS = 0

SYS auditing is disabled

Starting up:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options.

Using parameter settings in server-side pfile /u01/app/oracle/dbs/initora11g.ora

System parameters with non-default values:

  processes                = 150

  streams_pool_size        = 28M

  spfile                   = "+DATA/ora11g/spfileora11g.ora"

  memory_target            = 808M

  control_files            = "+DATA/ora11g/controlfile/current.260.747310619"

  db_block_size            = 8192

  compatible               = "11.2.0.0.0"

  db_create_file_dest      = "+DATA"

  db_create_online_log_dest_1= "+DATA"

  db_recovery_file_dest    = "+RECO"

  db_recovery_file_dest_size= 3852M

  undo_tablespace          = "UNDOTBS1"

  remote_login_passwordfile= "EXCLUSIVE"

  db_domain                = ""

  global_names             = TRUE

  dispatchers              = "(PROTOCOL=TCP) (SERVICE=ora11gXDB)"

  smtp_out_server          = "10.1.2.55"

  audit_file_dest          = "/u01/app/admin/ora11g/adump"

  audit_trail              = "DB"

  db_name                  = "ora11g"

  open_cursors             = 300

  aq_tm_processes          = 2

  diagnostic_dest          = "/u01/app"

Thu Feb 23 08:41:11 2012

PMON started with pid=2, OS id=2091

Thu Feb 23 08:41:11 2012

VKTM started with pid=3, OS id=2093 at elevated priority

VKTM running at (10)millisec precision with DBRM quantum (100)ms

Thu Feb 23 08:41:11 2012

GEN0 started with pid=4, OS id=2097

Thu Feb 23 08:41:11 2012

DIAG started with pid=5, OS id=2099

Thu Feb 23 08:41:11 2012

DBRM started with pid=6, OS id=2101

Thu Feb 23 08:41:11 2012

PSP0 started with pid=7, OS id=2103

Thu Feb 23 08:41:11 2012

DIA0 started with pid=8, OS id=2105

Thu Feb 23 08:41:11 2012

MMAN started with pid=9, OS id=2107

Thu Feb 23 08:41:11 2012

DBW0 started with pid=10, OS id=2109

Thu Feb 23 08:41:11 2012

LGWR started with pid=11, OS id=2111

Thu Feb 23 08:41:11 2012

CKPT started with pid=12, OS id=2113

Thu Feb 23 08:41:11 2012

SMON started with pid=13, OS id=2115

Thu Feb 23 08:41:12 2012

RECO started with pid=14, OS id=2117

Thu Feb 23 08:41:12 2012

RBAL started with pid=15, OS id=2119

Thu Feb 23 08:41:12 2012

ASMB started with pid=16, OS id=2121

Thu Feb 23 08:41:12 2012

MMON started with pid=17, OS id=2123

starting up 1 dispatcher(s) for network address ‘(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))’…

Thu Feb 23 08:41:12 2012

MMNL started with pid=18, OS id=2126

starting up 1 shared server(s) …

NOTE: initiating MARK startup

Starting background process MARK

Thu Feb 23 08:41:12 2012

MARK started with pid=21, OS id=2133

ORACLE_BASE not set in environment. It is recommended

that ORACLE_BASE be set in the environment

Reusing ORACLE_BASE from an earlier startup = /u01/app

NOTE: MARK has subscribed

Thu Feb 23 08:41:14 2012

ALTER DATABASE MOUNT

NOTE: Loaded library: System

SUCCESS: diskgroup DATA was mounted

NOTE: dependency between database ora11g and diskgroup resource ora.DATA.dg is established

Successful mount of redo thread 1, with mount id 4172206186

Database mounted in Exclusive Mode

Lost write protection disabled

Completed: ALTER DATABASE MOUNT

ALTER DATABASE OPEN

Beginning crash recovery of 1 threads

Started redo scan

Thu Feb 23 08:41:24 2012

Completed redo scan

 read 145 KB redo, 79 data blocks need recovery

Started redo application at

 Thread 1: logseq 131, block 515834

Recovery of Online Redo Log: Thread 1 Group 2 Seq 131 Reading mem 0

  Mem# 0: +DATA/ora11g/onlinelog/group_2.262.747310659

Completed redo application of 0.07MB

Completed crash recovery at

 Thread 1: logseq 131, block 516124, scn 8300030313247

 79 data blocks read, 79 data blocks written, 145 redo k-bytes read

Thu Feb 23 08:41:27 2012

Thread 1 advanced to log sequence 132 (thread open)

Thread 1 opened at log sequence 132

  Current log# 3 seq# 132 mem# 0: +DATA/ora11g/onlinelog/group_3.263.747310689

 

此时我们把从alert log中看到的control文件+DATA/ora11g/controlfile/current.260.747310619ODU copy出来

这样可以校验ODU控制文件control.txt中的数据文件是否齐全:

 

注意:ODU的最新版本中extract命令已被cp命令替换了:

ASMCMD> extract asmfile +DATA/ora11g/controlfile/current.260.747310619 to /u01/app/oracle/odu/control01.ctl

 

starting extract asm file ‘+DATA/ora11g/controlfile/current.260.747310619’ to ‘/u01/app/oracle/odu/control01.ctl’,file size is 9748480

asm file extract completed.

 

ASMCMD> exit

 

Exiting asmcmd module.

 

ODU> exit

ODU>

[root@bspdev odu]# ls -l control01.ctl

-rw-r–r–. 1 root root 9748480 Feb 23 11:05 control01.ctl

 

[root@bspdev odu]# strings control01.ctl|more

}|{z

ORA11G

,ORA11G

 1j)

,ORA11G

 1j)

ora11g

ora11g

%1j)

)1j)

F>j)

21j)

%1j)

)1j)

F>j)

21j)

+DATA/ora11g/onlinelog/group_3.263.747310689

+DATA/ora11g/onlinelog/group_2.262.747310659

+DATA/ora11g/onlinelog/group_1.261.747310625

+DATA/ora11g/datafile/users.259.747310451

+DATA/ora11g/datafile/undotbs1.258.747310451

+DATA/ora11g/datafile/sysaux.257.747310449

+DATA/ora11g/datafile/system.256.747310449

+DATA/ora11g/tempfile/temp.264.747310741

+DATA/ora11g/datafile/mytest.266.761050749

+DATA/ora11g/datafile/gaozcindex1

+DATA/ora11g/datafile/gaozcdata1

+DATA/ora11g/onlinelog/group_3.263.747310689

+DATA/ora11g/onlinelog/group_2.262.747310659

+DATA/ora11g/onlinelog/group_1.261.747310625

+DATA/ora11g/datafile/users.259.747310451

+DATA/ora11g/datafile/undotbs1.258.747310451

+DATA/ora11g/datafile/sysaux.257.747310449

+DATA/ora11g/datafile/system.256.747310449

+DATA/ora11g/tempfile/temp.264.747310741

+DATA/ora11g/datafile/mytest.266.761050749

+DATA/ora11g/datafile/gaozcindex1

+DATA/ora11g/datafile/gaozcdata1

SYSTEM

SYSAUX

UNDOTBS1

USERS

TEMP

MYTEST

GAOZCINDEX

GAOZCDATA

SYSTEM

SYSAUX

 

现在我们在ASM diskgroup没有mount的情况下依然是可以轻松unload出我们想要unload的任何表的数据了。

 

当然有了控制文件,我们也可以配置一个Oracle实例,将数据库启动到MOUNT状态,然后查询v$datafile视图也可以直接得到所有的数据文件名称。