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

[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)