ASM 檔案與本地檔案的轉換
技術點：
- 如何實驗ASM檔案和本檔案的相互轉換的方法之一：dbms_file_transfer.
- bbed 運算元據檔案
- ASM 使用template來生成檔案的路徑和名稱
建立表：
SQL> create table a (id number, name char(2000)) tablespace david1;
insert into a values(1,'abc');
insert into a values(2,'def');
insert into a values(3,'ade');
insert into a values(4,'asdf');
commit;
analyze table a compute statistics;
SQL> select blocks from user_tables where table_name='A';
BLOCKS
----------
13
SQL> select dbms_rowid.rowid_relative_fno(rowid) r_fno,dbms_rowid.rowid_block_number(rowid) b_no,id from a order by 1,2;
R_FNO B_NO ID
---------- ---------- ----------
9 20 4
9 20 1
9 20 2
9 21 3
9 21 4
9 21 1
9 22 2
9 22 3
9 22 4
9 23 1
9 23 2
R_FNO B_NO ID
---------- ---------- ----------
9 23 3
9 24 1
9 24 2
9 24 3
9 25 4
16 rows selected.
SQL> select name from v$datafile where file#=9;
NAME
--------------------------------------------------------------------------------
+DG1/devdb1/david1.dbf
轉換：
SQL> create directory sd as '+DG1/devdb1';
Directory created.
SQL> create directory dd as '/u01/backup';
Directory created.
SQL> alter database datafile 9 offline;
Database altered.
SQL> begin
dbms_file_transfer.copy_file(source_directory_object => 'sd',
source_file_name => 'david1.dbf',
destination_directory_object => 'dd',
destination_file_name => 'david1.dbf');
end; 2 3 4 5 6
7 /
PL/SQL procedure successfully completed.
損壞檔案：
rac1-> vi bbed.par
blocksize=8192
listfile=list
mode=edit
rac1-> vi list
9 /u01/backup/david1.dbf
rac1-> make -f ins_rdbms.mk /u01/app/oracle/product/10.2.0/db_1/rdbms/lib/bbed
rac1-> ./bbed parfile=bbed.par
Password:
BBED-00113: Invalid password. Please rerun utility with the correct password.
rac1-> ./bbed parfile=bbed.par
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Thu Jun 16 10:10:06 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set dba 9,20
DBA 0x02400014 (37748756 9,20)
BBED> find /c a
File: /u01/backup/david1.dbf (9)
Block: 20 Offsets: 4179 to 4690 Dba:0x02400014
------------------------------------------------------------------------
61626320 20202020 20202020 20202020 20202020 20202020 20202020 20202020
20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020
20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020
20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020
20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020
20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020
20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020
20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020
20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020
20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020
20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020
20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020
20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020
20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020
20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020
20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020
<32 bytes per line>
BBED> dump /v dba 9,20 offset 4179 count 32
File: /u01/backup/david1.dbf (9)
Block: 20 Offsets: 4179 to 4210 Dba:0x02400014
-------------------------------------------------------
61626320 20202020 20202020 20202020 l abc
20202020 20202020 20202020 20202020 l
<16 bytes per line>
BBED> modify 100 dba 9,20
File: /u01/backup/david1.dbf (9)
Block: 20 Offsets: 4179 to 4242 Dba:0x02400014
------------------------------------------------------------------------
64626320 20202020 20202020 20202020 20202020 20202020 20202020 20202020
20202020 20202020 20202020 20202020 20202020 20202020 20202020 20202020
<32 bytes per line>
rac1-> dbv file=/u01/backup/david1.dbf
DBVERIFY: Release 10.2.0.1.0 - Production on Thu Jun 16 10:25:16 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = /u01/backup/david1.dbf
Page 20 is marked corrupt
Corrupt block relative dba: 0x02400014 (file 9, block 20)
Bad check value found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x02400014
last change scn: 0x0000.00507909 seq: 0x1 flg: 0x06
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x79090601
check value in block header: 0x2f93
computed block checksum: 0x500
DBVERIFY - Verification complete
Total Pages Examined : 128
Total Pages Processed (Data) : 17
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 14
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 96
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Highest block SCN : 5273865 (0.5273865)
因為檔案存在而報錯，其實只是一個link
SQL> conn / as sysdba
Connected.
SQL> begin
dbms_file_transfer.copy_file(source_directory_object => 'dd',
source_file_name => 'david1.dbf',
destination_director 2 3 4 y_object => 'sd',
destination_file_name => 'david1.dbf');
end; 5 6
7 /
begin
*
ERROR at line 1:
ORA-19504: failed to create file "+DG1/devdb1/david1.dbf"
ORA-17502: ksfdcre:4 Failed to create file +DG1/devdb1/david1.dbf
ORA-15005: name "devdb1/david1.dbf" is already used by an existing alias
ORA-06512: at "SYS.DBMS_FILE_TRANSFER", line 84
ORA-06512: at "SYS.DBMS_FILE_TRANSFER", line 193
ORA-06512: at line 2
SQL> begin
dbms_file_transfer.copy_file(source_directory_object => 'dd',
source_file_name => 'david1.dbf',
destination_directory_object => 'sd',
destination_file_name => 'david111.dbf');
end;
--生成了一個檔案連結，真正的檔案是按ASM template來生成的。
ASMCMD [+DG1/DEVDB1] > ls -l
Type Redund Striped Time Sys Name
Y CONTROLFILE/
Y DATAFILE/
Y ONLINELOG/
Y PARAMETERFILE/
Y TEMPFILE/
N david1.dbf => +DG1/DEVDB1/DATAFILE/DAVID1.287.753893673
N david111.dbf => +DG1/DEVDB1/DATAFILE/COPY_FILE.286.753964155
N spfiledevdb1.ora => +DG1/DEVDB1/PARAMETERFILE/spfile.268.715879783
SQL> alter tablespace david1 rename datafile '+DG1/devdb1/david1.dbf' to '+DG1/devdb1/david111.dbf';
Tablespace altered.
SQL> alter database datafile 9 online;
alter database datafile 9 online
*
ERROR at line 1:
ORA-01113: file 9 needs media recovery
ORA-01110: data file 9: '+DG1/devdb1/david111.dbf'
SQL> Recover datafile 8;
--在transfer 過程中，如果遇到不能支援的檔案型別，報錯如下：
SQL> begin
dbms_file_transfer.copy_file(source_directory_object => 'dd',
source_file_name => 'initdevdb1.ora',
destination_directory_object => 'sd',
destination_file_name => 'initdevdb1.ora');
end; 2 3 4 5 6
7 /
begin
*
ERROR at line 1:
ORA-19505: failed to identify file "/u01/backup/initdevdb1.ora"
ORA-27046: file size is not a multiple of logical block size
Additional information: 1
ORA-06512: at "SYS.DBMS_FILE_TRANSFER", line 84
ORA-06512: at "SYS.DBMS_FILE_TRANSFER", line 193
ORA-06512: at line 2
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/674865/viewspace-700059/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【原創】ASM下的資料檔案轉換為普通檔案ASM
- 使用dbms_file_transfer轉換ASM檔案ASM
- Torrent檔案的解析與轉換
- 本地檔案轉 Drawable
- 從ASM磁碟中複製檔案到本地檔案系統ASM
- ASM與檔案系統之間copy資料檔案--檔案系統到ASMASM
- PDF檔案轉換為DWF檔案
- WOR檔案轉換成GST檔案
- java 字串與檔案相互轉換Java字串
- 【ASM】ASM資料檔案和OS檔案（FILESYSTEM）轉移方法總結ASM
- 將Schema檔案轉換為Java檔案Java
- 把 .xyz 檔案轉換成 .ply 檔案
- ASM認證與口令檔案ASM
- PE檔案結構（二） 區塊，檔案偏移與RVA轉換
- 如何將.ipynb檔案轉換為.py檔案
- Java 檔案換行符識別與轉換Java
- oracle之 RAC本地資料檔案遷移至ASMOracleASM
- 利用rman將本地資料檔案遷移到asmASM
- tkprof 轉換trc檔案
- Mxnet模型檔案轉換為Pb模型檔案模型
- 檢測檔案編碼，轉換檔案編碼
- caj檔案怎麼轉換成word文件，簡單的檔案格式轉換教程
- 【RAC】RAC本地資料檔案遷移至ASM的方法(3)ASM
- 【RAC】RAC本地資料檔案遷移至ASM的方法(2)ASM
- 【RAC】RAC本地資料檔案遷移至ASM的方法(1)ASM
- 學習ASM技術（七）--ASM檔案轉化ASM
- asm拷貝檔案到檔案系統ASM
- 標頭檔案與庫檔案與菜鳥 (轉)
- aspose word轉換pdf檔案後將pdf檔案轉換為圖片png
- office舊版本檔案轉換為新版本檔案
- PDF檔案如何轉換為能編輯的CAD檔案
- spark直接讀取本地檔案系統的檔案Spark
- heic檔案如何轉換jpg？
- plist檔案格式轉換器
- Linux轉換檔案格式Linux
- Json檔案轉換為Excel檔案!涉及讀檔案,時間戳轉化,寫文件JSONExcel時間戳
- 使用PowerShell比較本地文字檔案與Web上的文字檔案是否相同Web
- 幾種ASM與File System資料檔案轉移方法ASM