[20130115]測試從asm中取出spfile檔案以及一個資料檔案.txt

lfree發表於2013-01-15
ASM
[20130115]測試從asm中取出spfile檔案以及一個資料檔案.txt

參考: 使用dd複製asm中檔案

SQL> column name format a50
SQL> select file#,ts#,status,enabled,checkpoint_change#,name,bytes  from v$datafile;
     FILE#        TS# STATUS  ENABLED    CHECKPOINT_CHANGE# NAME                                                    BYTES
---------- ---------- ------- ---------- ------------------ -------------------------------------------------- ----------
         1          0 SYSTEM  READ WRITE            6438423 +DATA/test/datafile/system.270.762883167            775946240
         2          1 ONLINE  READ WRITE            6438423 +DATA/test/datafile/sysaux.269.762883167            597688320
         3          2 ONLINE  READ WRITE            6438423 +DATA/test/datafile/undotbs1.267.762883167          519045120
         4          4 ONLINE  READ WRITE            6438423 +DATA/test/datafile/users.266.762883167             722206720
         5          6 ONLINE  READ WRITE            6438423 +DATA/test/datafile/example.268.762883167           104857600
         6          7 ONLINE  READ WRITE            6438423 +DATA/test/datafile/tools.280.762943407             104857600
         7          8 ONLINE  READ WRITE            6438423 +DATA/test/datafile/test.284.770380219               10485760
         8         10 ONLINE  READ WRITE            6438423 +DATA/test/datafile/mssm.291.771757063              104857600

8 rows selected.

1.取出spfile看看:

SQL> SELECT GROUP_NUMBER, FILE_NUMBER, NAME FROM v$asm_alias WHERE upper(NAME) LIKE '%SPFILE%' GROUP BY GROUP_NUMBER, FILE_NUMBER, NAME;

GROUP_NUMBER FILE_NUMBER NAME
------------ ----------- ----------------------
           1         258 spfiletest.ora
           1         258 spfile.258.762881973

SQL> SELECT disk_kffxp, au_kffxp, xnum_kffxp FROM x$kffxp  WHERE GROUP_KFFXP=1 AND NUMBER_KFFXP=258;

DISK_KFFXP   AU_KFFXP XNUM_KFFXP
---------- ---------- ----------
         0         92          0

SQL> column path format a40
SQL> select DISK_NUMBER,GROUP_NUMBER,PATH from v$asm_disk;

DISK_NUMBER GROUP_NUMBER PATH
----------- ------------ ----------------------------------------
          1            0 /dev/raw/raw2
          0            1 /dev/raw/raw1
--注:當前 /dev/raw/raw2沒有使用

# raw -qa
/dev/raw/raw1:  bound to major 8, minor 7
/dev/raw/raw2:  bound to major 8, minor 8

# cat /proc/partitions
major minor  #blocks  name

   8     0  142737408 sda
   8     1      40131 sda1
   8     2     200812 sda2
   8     3   20008957 sda3
   8     4          1 sda4
   8     5    3911796 sda5
   8     6   97667136 sda6
   8     7   16611178 sda7
   8     8    4289323 sda8

--從以上提示可以猜出spfile檔案在/dev/sda7的92*Au size偏移處。

dd if=/dev/sda7 bs=1024k count=1 skip=92 f=spfile_test.ora

# strings spfile_test.ora
test.__db_cache_size=620756992
test.__java_pool_size=16777216
test.__large_pool_size=16777216
test.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
test.__pga_aggregate_target=654311424
test.__sga_target=973078528
test.__shared_io_pool_size=0
test.__shared_pool_size=301989888
test.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/test/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='+DATA/test/controlfile/current.272.762883123','+DATA/test/controlfile/current.271.762883125'#Restore Controlfile
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain='com'
*.db_file_multiblock_read_count=16
*.db_name='test'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=1073741824
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=0
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=0
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.result_cache_max_result=100
*.result_cache_max_size=25165824
*.undo_tablespace='UNDOTBS1'

2.取出另外一個資料檔案+DATA/test/datafile/test.284.770380219檔案大小10485760。
10485760/1024/1024=10+加上os的塊頭8k，佔用11M。

SQL> SELECT GROUP_NUMBER, FILE_NUMBER, NAME FROM v$asm_alias WHERE upper(NAME) LIKE '%TEST%' GROUP BY GROUP_NUMBER, FILE_NUMBER, NAME;

GROUP_NUMBER FILE_NUMBER NAME
------------ ----------- -------------------
           1         258 spfiletest.ora
           1         284 TEST.284.770380219
           1  4294967295 TEST
--後面那個是我例項的目錄。TEST.284.770380219的NUMBER_KFFXP=284.

SQL> SELECT disk_kffxp, au_kffxp, xnum_kffxp FROM x$kffxp  WHERE GROUP_KFFXP=1 AND NUMBER_KFFXP=284;

DISK_KFFXP   AU_KFFXP XNUM_KFFXP
---------- ---------- ----------
         0       2676          0
         0       2677          1
         0       2672          2
         0       2673          3
         0       2674          4
         0       2675          5
         0       2576          6
         0       2577          7
         0       2578          8
         0       2579          9
         0       2580         10

11 rows selected.

SQL> column x format a60
SQL> select 'dd if=/dev/sda7 bs=1024k count=1 skip='||AU_KFFXP||'  >>test01.dbf' x from  x$kffxp 
WHERE GROUP_KFFXP=1 AND NUMBER_KFFXP=284 order by XNUM_KFFXP;

X
------------------------------------------------------------
dd if=/dev/sda7 bs=1024k count=1 skip=2676  >>test01.dbf
dd if=/dev/sda7 bs=1024k count=1 skip=2677  >>test01.dbf
dd if=/dev/sda7 bs=1024k count=1 skip=2672  >>test01.dbf
dd if=/dev/sda7 bs=1024k count=1 skip=2673  >>test01.dbf
dd if=/dev/sda7 bs=1024k count=1 skip=2674  >>test01.dbf
dd if=/dev/sda7 bs=1024k count=1 skip=2675  >>test01.dbf
dd if=/dev/sda7 bs=1024k count=1 skip=2576  >>test01.dbf
dd if=/dev/sda7 bs=1024k count=1 skip=2577  >>test01.dbf
dd if=/dev/sda7 bs=1024k count=1 skip=2578  >>test01.dbf
dd if=/dev/sda7 bs=1024k count=1 skip=2579  >>test01.dbf
dd if=/dev/sda7 bs=1024k count=1 skip=2580  >>test01.dbf

11 rows selected.

--將以上的輸出作為執行檔案執行，注意test01.dbf檔案在當前目錄不存在。

# ll test01.dbf
-rw-r--r-- 1 root root 11534336 Jan 15 16:49 test01.dbf

--恢復大小為11M比實際大，但是並不影響實際檢測。

$ dbv file=test01.dbf
DBVERIFY: Release 11.2.0.1.0 - Production on Tue Jan 15 16:49:40 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
DBVERIFY - Verification starting : FILE = /home/oracle/aulasm/test01.dbf
DBVERIFY - Verification complete
Total Pages Examined         : 1280
Total Pages Processed (Data) : 103
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 105
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 146
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 926
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 3853639 (0.3853639)

$ dbv file='+DATA/test/datafile/test.284.770380219' userid=sys/XXXXX
DBVERIFY: Release 11.2.0.1.0 - Production on Tue Jan 15 17:00:07 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
DBVERIFY - Verification starting : FILE = +DATA/test/datafile/test.284.770380219
DBVERIFY - Verification complete
Total Pages Examined         : 1280
Total Pages Processed (Data) : 103
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 105
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 146
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 926
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 0 (0.0)

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-752699/，如需轉載，請註明出處，否則將追究法律責任。

相關文章