How to get all datafile names when the associated diskgroups are unmounted

Posted: February 27th, 2012

When the diskgroups can be mounted, you can startup the database into mount state and query the v$datafile to get all datafile names easily.

Now we come to an extreme case: your database has collapsed, and the associated disk group cannot be mounted, in that case your database can not be able to boot into mount state, so you can not query v$datafile to get all datafile names at that time.

But we have to recover the data in that case, how should we do?

ODU can easily solve the above problem, let’s see an example:

 

Now we shutdown the Oracle instance and ASM instance to simulate the situation that the diskgroups can not be mounted:

[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

 

The Oracle’s asmcmd command can not be used if the ASM diskgroups can not be mounted:

[grid@bspdev ~]$ asmcmd

Connected to an idle instance.

ASMCMD> ls

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

 

But the ODU embedded asmcmd command can be used, so we can easily use the ODU embedded asmcmd command to get all datafile names:

[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

 

Now you can see, the current control file name is +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

 

Exit the ODU command interface and fill the above datafile names (note to take full path) into the ODU control file 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

 

Enter into the ODU command interface again, you can generate the ODU license file oductl.txt now:

[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

 

After you get the ODU license file oductl.dat according to the ODU User’s Guide, you can use ODU enterprise version without any limits:

[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

 

You can get the control file name from the alert log too, the result is consistent with the ls result from the embedded 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

 

Now we can copy the above control file +DATA/ora11g/controlfile/current.260.747310619 to file system from the ODU to verify the datafile names in ODU control file control.txt, you can check whether the datafile names in control.txt are complete:

 

Note: the extract command has been replaced by copy command in the latest ODU enterprise version:

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

 

Now we can still easily unload any table data whatever we want even the associated diskgroups can not be mounted.