sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 运维DBA >

【学习笔记】Oracle数据库使用dd复制ASM中的datafile操作过程

时间:2016-11-30 21:55   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 Oracle研究中心学习笔记:分享一篇关于Oracle数据库ASM磁盘管理数据文件datafile的笔记,详细记录dd复制ASM中的datafile过程。

本站文章除注明转载外,均为本站原创: 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: dd复制ASM中的datafile

SQL> SELECT file_id,file_name,AUTOEXTENSIBLE
  2    FROM dba_data_files
  3   ORDER BY 1;

   FILE_ID FILE_NAME                                                    AUT
---------- ------------------------------------------------------------ ---
         1 +DATA01/10gasm/datafile/system.256.776886753                 YES
         2 +DATA01/10gasm/datafile/undotbs1.258.776886753               YES
         3 +DATA01/10gasm/datafile/sysaux.257.776886753                 YES
         4 +DATA01/10gasm/datafile/users.259.776886755                  YES

SQL> SELECT GROUP_NUMBER, FILE_NUMBER, NAME
  2    FROM v$asm_alias
  3   GROUP BY GROUP_NUMBER, FILE_NUMBER, NAME;

                   File
GROUP_NUMBER     NUMBER NAME
------------ ---------- ------------------------------------------------
           1        256 SYSTEM.256.776886753
           1        257 SYSAUX.257.776886753
           1        258 UNDOTBS1.258.776886753
           1        259 USERS.259.776886755
           1        260 CURRENT.260.776886827
           1        261 group_1.261.776886835
           1        262 group_2.262.776886835
           1        263 group_3.263.776886837
           1        264 TEMP.264.776886851
           1        265 spfile.265.777691577
           1 4294967295 10GASM
           1 4294967295 DATAFILE
           1 4294967295 TEMPFILE
           1 4294967295 ONLINELOG
           1 4294967295 CONTROLFILE
           1 4294967295 PARAMETERFILE
           2        256 ROGER.256.777429425
           2 4294967295 10GASM
           2 4294967295 DATAFILE

19 ROWS selected.

这里我们来看datafile  +DATA01/10GASM/DATAFILE/USERS.259.776886755 的分布情况。
SQL> SELECT disk_kffxp, au_kffxp, xnum_kffxp
  2    FROM x$kffxp                     
  3   WHERE GROUP_KFFXP=1
  4     AND NUMBER_KFFXP=259;

DISK_KFFXP   AU_KFFXP XNUM_KFFXP
---------- ---------- ----------
         0        791          0
         0        792          1
         0        793          2
         0        794          3
         0        795          4
         0        796          5

6 ROWS selected.

我们可以看到users这个datafile虽然大小为5m多一点点,但是分配了6个AU,而且都是连续的。
SQL> SELECT GROUP_KFFXP, DISK_KFFXP, AU_KFFXP
  2    FROM x$kffxp
  3   WHERE number_kffxp = (SELECT file_number
  4                           FROM v$asm_alias
  5                          WHERE name = 'USERS.259.776886755');

GROUP_KFFXP DISK_KFFXP   AU_KFFXP
----------- ---------- ----------
          1          0        791
          1          0        792
          1          0        793
          1          0        794
          1          0        795
          1          0        796

6 ROWS selected.


SQL> SET LINES 200
SQL> col path FOR a40

SQL> SELECT a.GROUP_KFFXP, a.DISK_KFFXP, a.AU_KFFXP, b.path
  2    FROM x$kffxp a, v$asm_disk b, v$asm_alias c
  3   WHERE a.number_kffxp = c.file_number
  4     AND a.GROUP_KFFXP = b.group_number
  5     AND a.disk_kffxp = b.disk_number
  6     AND c.name = '&filename';

Enter VALUE FOR filename: USERS.259.776886755

GROUP_KFFXP DISK_KFFXP   AU_KFFXP PATH
----------- ---------- ---------- ----------------------------------------
          1          0        791 /dev/sdb
          1          0        792 /dev/sdb
          1          0        793 /dev/sdb
          1          0        794 /dev/sdb
          1          0        795 /dev/sdb
          1          0        796 /dev/sdb

6 ROWS selected.


SQL> CREATE tablespace roger datafile '+DATA02' SIZE 20m;

Tablespace created.

SQL> SELECT file_id,file_name,AUTOEXTENSIBLE
  2    FROM dba_data_files
  3   ORDER BY 1;

   FILE_ID FILE_NAME                                          AUT
---------- -------------------------------------------------- ---
         1 +DATA01/10gasm/datafile/system.256.776886753       YES
         2 +DATA01/10gasm/datafile/undotbs1.258.776886753     YES
         3 +DATA01/10gasm/datafile/sysaux.257.776886753       YES
         4 +DATA01/10gasm/datafile/users.259.776886755        YES
         5 +DATA02/10gasm/datafile/roger.256.777429425        NO

下面用dd进行复制datafile :
[oracle@10gasm ~]$ dd if=/dev/sdb of=users2.dbf bs=8192 skip=101248 count=641
641+0 records in
641+0 records out
5251072 bytes (5.3 MB) copied, 0.117082 seconds, 44.8 MB/s

[oracle@10gasm ~]$ dbv file=users2.dbf blocksize=8192

DBVERIFY: Release 10.2.0.1.0 - Production on Sun Mar 11 20:08:20 2012
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

DBVERIFY - Verification starting : FILE = users2.dbf

DBVERIFY - Verification complete

Total Pages Examined         : 640
Total Pages Processed (Data) : 15
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 2
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 26
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 597
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Highest block SCN            : 442362 (0.442362)
新加一个disk到DATA01磁盘组中,再次进行观察AU分布。

SQL> ALTER diskgroup data01 ADD disk '/dev/sde';

Diskgroup altered.

SQL> SELECT   NVL(a.name, '[CANDIDATE]')                   disk_group_name
  2         , b.path                                       disk_file_path
  3         , b.name                                       disk_file_name
  4         , b.failgroup                                  disk_file_fail_group
  5         , b.total_mb                                   total_mb
  6         , (b.total_mb - b.free_mb)                     used_mb
  7         , ROUND((1- (b.free_mb / b.total_mb))*100, 2)  pct_used
  8    FROM v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number)
  9   ORDER BY a.name
10  /

Disk GROUP Name      Path              File Name            Fail GROUP           File SIZE (MB) Used SIZE (MB) Pct. Used
-------------------- ----------------- -------------------- -------------------- -------------- -------------- ---------
DATA01               /dev/sdb          DATA01_0000          DATA01_0000                   1,024            576     56.25
                     /dev/sde          DATA01_0001          DATA01_0001                   1,Oracleо024            441     43.07
********************                                                             -------------- --------------
                                                                                          2,048          1,017

DATA02               /dev/sdd          DATA02_0001          DATA02_0001                   1,024             73      7.13
                     /dev/sdc          DATA02_0000          DATA02_0000                   1,024             73      7.13
********************                                                             -------------- --------------
                                                                                          2,048            146

                                                                                 -------------- --------------
Grand Total:                                                                              4,096          1,163

我们可以发现,加上了/dev/sde以后,asm 完成了reblance操作。

SQL> SET LINES 200
SQL> col path FOR a40

SQL> SELECT a.GROUP_KFFXP, a.DISK_KFFXP, a.AU_KFFXP, b.path
  2    FROM x$kffxp a, v$asm_disk b, v$asm_alias c
  3   WHERE a.number_kffxp = c.file_number
  4     AND a.GROUP_KFFXP = b.group_number
  5     AND a.disk_kffxp = b.disk_number
  6     AND b.group_number=1
  7     AND c.name LIKE '%USERS%';

GROUP_KFFXP DISK_KFFXP   AU_KFFXP PATH
----------- ---------- ---------- ----------------------------------------
          1          0        792 /dev/sdb
          1          0        794 /dev/sdb
          1          0        796 /dev/sdb
          1          1        408 /dev/sde
          1          1        406 /dev/sde
          1          1        407 /dev/sde

6 ROWS selected.

++++++ 使用dd进行复制datafile ++++++

[oracle@10gasm ~]$ dd if=/dev/sde of=user_1.dbf bs=1024k skip=406 count=1
1+0 records in
1+0 records out
1048576 bytes (1.0 MB) copied, 0.0175263 seconds, 59.8 MB/s

[oracle@10gasm ~]$ dd if=/dev/sdb of=user_2.dbf bs=1024k skip=792 count=1
dd  if=/dev/sde  of=user_5.dbf bs=1024k skip=408    count=1
dd  if=/dev/sdb  of=user_6.dbf bs=8192  skip=101888 count=11+0 records in
1+0 records out
1048576 bytes (1.0 MB) copied, 0.0184495 seconds, 56.8 MB/s

[oracle@10gasm ~]$ dd if=/dev/sde  of=user_3.dbf bs=1024k skip=407 count=1
1+0 records in
1+0 records out
1048576 bytes (1.0 MB) copied, 0.0150804 seconds, 69.5 MB/s

[oracle@10gasm ~]$ dd if=/dev/sdb  of=user_4.dbf bs=1024k skip=794 count=1
1+0 records in
1+0 records out
1048576 bytes (1.0 MB) copied, 0.0118394 seconds, 88.6 MB/s

[oracle@10gasm ~]$ dd if=/dev/sde  of=user_5.dbf bs=1024k skip=408 count=1
1+0 records in
1+0 records out
1048576 bytes (1.0 MB) copied, 0.00784235 seconds, 134 MB/s

[oracle@10gasm ~]$ dd if=/dev/sdb of=user_6.dbf bs=8192  skip=101888 count=1
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 0.00032412 seconds, 25.3 MB/s

[oracle@10gasm ~]$ dd if=user_1.dbf of=user_dd.dbf bs=8192 count=128
128+0 records in
128+0 records out
1048576 bytes (1.0 MB) copied, 0.014171 seconds, 74.0 MB/s

[oracle@10gasm ~]$ dd if=user_2.dbf of=user_dd.dbf bs=8192 count=128 seek=128
dd if=user_3.dbf of=user_dd.dbf bs=8192 count=128 seek=256
128+0 records in
128+0 records out
dd if=user_4.dbf of=user_dd.dbf bs=8192 count=128 seek=384    
1048576 bytes (1.0 MB) copied, 0.00760896 seconds, 138 MB/s

[oracle@10gasm ~]$ dd if=user_3.dbf of=user_dd.dbf bs=8192 count=128 seek=256
dd if=user_5.dbf of=user_dd.dbf bs=8192 count=128 seek=512
128+0 records in
128+0 records out
1048576 bytes (1.0 MB) copied, 0.00653819 seconds, 160 MB/s

[oracle@10gasm ~]$ dd if=user_4.dbf of=user_dd.dbf bs=8192 count=128 seek=384    
128+0 records in
128+0 records out
dd if=user_6.dbf of=user_dd.dbf bs=8192 count=1 seek=6401048576 bytes (1.0 MB) copied, 0.00619976 seconds, 169 MB/s

[oracle@10gasm ~]$ dd if=user_5.dbf of=user_dd.dbf bs=8192 count=128 seek=512
128+0 records in
128+0 records out
1048576 bytes (1.0 MB) copied, 0.0060106 seconds, 174 MB/s

[oracle@10gasm ~]$ dd if=user_6.dbf of=user_dd.dbf bs=8192 count=1 seek=640
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 0.000270084 seconds, 30.3 MB/s

[oracle@10gasm ~]$ ls -ltr user_*
-rw-r--r-- 1 oracle oinstall 1048576 Mar 12 07:17 user_5.dbf
-rw-r--r-- 1 oracle oinstall 1048576 Mar 12 07:17 user_4.dbf
-rw-r--r-- 1 oracle oinstall 1048576 Mar 12 07:17 user_3.dbf
-rw-r--r-- 1 oracle oinstall 1048576 Mar 12 07:17 user_2.dbf
-rw-r--r-- 1 oracle oinstall 1048576 Mar 12 07:17 user_1.dbf
-rw-r--r-- 1 oracle oinstall    8192 Mar 12 07:17 user_6.dbf
-rw-r--r-- 1 oracle oinstall 5251072 Mar 12 07:19 user_dd.dbf

[oracle@10gasm ~]$ dbv file=user_dd.dbf blocksize=8192

DBVERIFY: Release 10.2.0.1.0 - Production on Mon Mar 12 07:19:47 2012
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

DBVERIFY - Verification starting : FILE = user_dd.dbf

DBVERIFY - Verification complete

Total Pages Examined         : 640
Total Pages Processed (Data) : 15
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 2
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 26
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 597
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Highest block SCN            : 442362 (0.442362)

++++++ dd 复制spfile 内容 ++++++

SQL> SET LINES 200
SQL> col path FOR a40

SQL> SELECT a.GROUP_KFFXP, a.DISK_KFFXP, a.AU_KFFXP, b.path
  2    FROM x$kffxp a, v$asm_disk b, v$asm_alias c
  3   WHERE a.number_kffxp = c.file_number
  4     AND a.GROUP_KFFXP = b.group_number
  5     AND a.disk_kffxp = b.disk_number
  6     AND c.name = '&filename';

Enter VALUE FOR filename: spfile.265.776886941
OLD   6:    AND c.name = '&filename'
NEW   6:    AND c.name = 'spfile.265.776886941'

GROUP_KFFXP DISK_KFFXP   AU_KFFXP PATH
----------- ---------- ---------- ----------------------------------------
          1          0       1004 /dev/sdb

[oracle@10gasm ~]$ dd if=/dev/sdb of=spfile.ora bs=8192 skip=128512 count=128
128+0 records in
128+0 records out
1048576 bytes (1.0 MB) copied, 0.0624247 seconds, 16.8 MB/s

[oracle@10gasm ~]$ strings spfile.ora

10gasm.__db_cache_size=121634816
10gasm.__java_pool_size=4194304
10gasm.__large_pool_size=4194304
10gasm.__shared_pool_size=75497472
10gasm.__streams_pool_size=0
*.audit_file_dest='/home/oracle/admin/10gasm/adump'
*.background_dump_dest='/home/oracle/admin/10gasm/bdump'
*.compatible='10.2.0.1.0'
*.control_files='+DATA01/10gasm/controlfile/current.260.776886827'
*.core_dump_dest='/home/oracle/admin/10gasm/cdump'
*.db_block_size=8192
*.db_create_file_dest='+DATA01'
*.db_domain=''
*.db_file_multiblock_read_count=8
*.db_name='10gasm'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=10gasmXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=+DATA01/10gasm/'
*.log_archive_dest='/home/oracle/archivelog'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=62914560
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=209715200
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/home/oracle/admin/10gasm/udump'

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle数据库使用dd复制ASM中的datafile操作过程

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1350.html

Oracle研究中心

关键词:

dd复制ASM中的datafile

复制Oracle ASM中的数据文件详细过程