理解dbms_file_transfer
来源：互联网 发布：vue node 编辑：程序博客网 时间：2024/05/18 13:45
新建一张测试数据表
SQL> create table a (id number,name char(2000));
Table created.
SQL> insert into a values (1,'abc');
1 row created.
SQL> insert into a values (2,'def');
1 row created.
SQL> insert into a values (3,'ade');
1 row created.
SQL> insert into a values (4,'asdf');
analyze分析
SQL> analyze table a compute statistics;
Table analyzed.
SQL> select blocks from user_tables where table_name='A';
BLOCKS
----------
5
查看文件和数据号
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
---------- ---------- ----------
4 388 1
4 388 2
4 388 3
4 389 4
SQL> select name from v$datafile where file#=4;
NAME
--------------------------------------------------------------------------------
+DG1/orcl/datafile/users.262.779226311
SQL>
SQL> create directory users as '+DG1/orcl/datafile';
Directory created.
SQL> create directory dd as '/home/oracle';
Directory created.
SQL> alter database datafile 4 offline;
Database altered.
SQL> begin
2 dbms_file_transfer.copy_file('users','users.262.779226311','dd','users.dbf');
3 end;
4 /
PL/SQL procedure successfully completed.
[oracle@Oracle ~]$ ls users.dbf
users.dbf
[oracle@Oracle ~]$
[oracle@Oracle ~]$ dbv file=/home/oracle/users.dbf
DBVERIFY: Release 10.2.0.1.0 - Production on Thu Mar 29 22:54:53 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = /home/oracle/users.dbf
DBVERIFY - Verification complete
Total Pages Examined : 640
Total Pages Processed (Data) : 48
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 63
Total Pages Failing (Index): 0
Total Pages Processed (Other): 136
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 393
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Highest block SCN : 592906 (0.592906)
这里用到bbed小工具
[oracle@Oracle lib]$ cat bbed.par
blocksize=8192
listfile=list
mode=edit
[oracle@Oracle lib]$ ./bbed parfile=bbed.par
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Fri Mar 30 10:44:52 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED>
BBED> set dba 4,389
DBA 0x01000185 (16777605 4,389)
BBED> find /c 4
File: /home/oracle/users.dbf (4)
Block: 389 Offsets: 212 to 723 Dba:0x01000185
------------------------------------------------------------------------
34144814 5c147014 84149814 ac14c014 d414e814 fc141015 24153815 4c156015
74158815 9c15b015 c415d815 ec150016 14162816 3c165016 64167816 8c16a016
b416c816 dc16f016 04171817 2c174017 54176817 7c179017 a417b817 cc17e017
f4170818 1c183018 44185818 6c188018 9418a818 bc18d018 e418f818 0c192019
34194819 5c197019 84199819 ac19c019 d419e819 fc19101a 241a381a 4c1a601a
741a881a 9c1ab01a c41ad81a ec1a001b 141b281b 3c1b501b 641b781b 8c1ba01b
b41bc81b dc1bf01b 041c181c 2c1c401c 541c681c 7c1c901c a41cb81c cc1ce01c
f41c081d 1c1d301d 441d581d 6c1d801d 941da81d bc1dd01d e41df81d 0c1e201e
341e481e 5c1e701e 841e981e ac1ec01e d41ee81e fc1e101f 241f381f 4c1f0000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes per line>
BBED> dump /v dba 4,389 offset 212 count 32
File: /home/oracle/users.dbf (4)
Block: 389 Offsets: 212 to 243 Dba:0x01000185
-------------------------------------------------------
34144814 5c147014 84149814 ac14c014 l 4.H.\.p.....
d414e814 fc141015 24153815 4c156015 l ..$.8.L.`.
<16 bytes per line>
BBED> modify 50 dba 4,389
File: /home/oracle/users.dbf (4)
Block: 389 Offsets: 212 to 243 Dba:0x01000185
------------------------------------------------------------------------
32144814 5c147014 84149814 ac14c014 d414e814 fc141015 24153815 4c156015
<32 bytes per line>
BBED> exit
[oracle@Oracle ~]$ dbv file=/home/oracle/users.dbf
DBVERIFY: Release 10.2.0.1.0 - Production on Fri Mar 30 10:57:54 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = /home/oracle/users.dbf
Page 389 is marked corrupt
Corrupt block relative dba: 0x01000185 (file 4, block 389)
Bad check value found during dbv:
Data in bad block:
type: 6 format: 2 rdba: 0x01000185
last change scn: 0x0000.00090bb9 seq: 0x2 flg: 0x04
spare1: 0x0 spare2: 0x0 spare3: 0x0
consistency value in tail: 0x0bb90602
check value in block header: 0xbf4a
computed block checksum: 0x50
DBVERIFY - Verification complete
Total Pages Examined : 640
Total Pages Processed (Data) : 47
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 63
Total Pages Failing (Index): 0
Total Pages Processed (Other): 136
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 393
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Highest block SCN : 592906 (0.592906)
SQL> begin
2 dbms_file_transfer.copy_file('dd','users.dbf','users','users.262.779226311');
3 end;
4 /
begin
*
ERROR at line 1:
ORA-19504: failed to create file "+DG1/orcl/datafile/users.262.779226311"
ORA-17502: ksfdcre:4 Failed to create file
+DG1/orcl/datafile/users.262.779226311
ORA-15046: ASM file name '+DG1/orcl/datafile/users.262.779226311' is not in
single-file creation form
ORA-06512: at "SYS.DBMS_FILE_TRANSFER", line 84
ORA-06512: at "SYS.DBMS_FILE_TRANSFER", line 193
ORA-06512: at line 2
SQL>
SQL> begin
2 dbms_file_transfer.copy_file('dd','users.dbf','users','users.dbf');
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DG1/orcl/datafile/users.262.779226311
10 rows selected.
SQL> alter tablespace users rename datafile '+DG1/orcl/datafile/users.262.779226311' to '+DG1/orcl/datafile/users.dbf';
Tablespace altered.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DG1/orcl/datafile/users.dbf
SQL> alter tablespace users online;
alter tablespace users online
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: '+DG1/orcl/datafile/users.dbf'
[oracle@Oracle ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Fri Mar 30 11:10:28 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1306991326)
RMAN> recover datafile 4;
Starting recover at 30-MAR-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=143 devtype=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:04
Finished recover at 30-MAR-12
RMAN>
SQL> alter database datafile 4 online;
Database altered.
- 理解dbms_file_transfer
- 利用DBMS_FILE_TRANSFER传输数据库文件
- dbms_file_transfer使用简介
- 使用DBMS_FILE_TRANSFER包拷贝文件
- DBMS_FILE_TRANSFER文件拷贝命令介绍
- DBMS_FILE_TRANSFER Package in Oracle Database 10g
- DBMS_FILE_TRANSFER用法取asm下文件
- 使用Oracle包DBMS_FILE_TRANSFER进行copy文件
- 使用DBMS_FILE_TRANSFER迁移ASM中的表空间和数据文件
- 使用 dbms_file_transfer 包在asm 和文件系统间拷贝文件
- 使用dbms_file_transfer传输ASM file出现ORA-15001
- 使用 dbms_file_transfer 方式测试增量传输表空间XTTS
- 理解
- 理解
- 理解
- 理解
- 理解
- 理解
- 装饰模式
- 实例讲解4--awk命令e
- 实例讲解4--awk命令e续
- 线程中CreateEvent和SetEvent及WaitForSingleObject的用法
- iOS UIWebView自定义菜单
- 理解dbms_file_transfer
- Android-ListView
- jsp页面返回上一级
- linux下关闭端口简单的办法
- Linux 使用cpio解压 initrd.img
- 软件测试分类
- 基于事件的异步套接字
- Linux下查看文件和文件夹大小的df和du命令
- 程序控制流
