How to get all datafile names when the associated diskgroups are unmounted
Posted: February 27th, 2012When 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.
