分类目录归档：逻辑备份/恢复

expdp 并行导出单表数据

发表于 2021 年 6 月 18 日 由 惜分飞

在某些情况下,需要使用并行的方法使用 datapump 对单个对象并行导出,导入加快数据迁移的数据
expdp导出操作

#!/bin/bash
chunk=10
for ((i=0;i<chunk;i++));
do
  expdp USERNAME/Password@DB_NAME TABLES=LOB_TEST QUERY=LOB_TEST:\"where mod\(dbms_rowid.rowid_block_number\(rowid\)\, 
>${chunk}\) = ${i}\" directory=DMP dumpfile=lob_test_${i}.dmp logfile= log_test_${i}.log &
  echo $i
done

impdp导入操作

#!/bin/bash
chunk=10
for ((i=0;i<chunk;i++));
do
 impdp USERNAME/Password@DB_NAME  directory=DMP REMAP_TABLE=LOB_TEST:LOB_TEST  remap_schema=source:target 
>dumpfile= lob_test_${i}.dmp logfile=TABLE_imp_log_test_${i}.log  DATA_OPTIONS=DISABLE_APPEND_HINT  CONTENT=DATA_ONLY &
 echo $i
done

在12c版本开始impdp可能会启用ENABLE_PARALLEL_DML特性,需要注意
参考:Optimising LOB Export and Import Performance via Oracle DataPump

发表在 逻辑备份/恢复 | 评论关闭

datapump network_link遭遇ORA-12899错误

发表于 2021 年 6 月 16 日 由 惜分飞

在给一个客户使用expdp+network_link导出数据,然后通过impdp导入数据的过程中遇到ORA-12899问题.
20210608215836

对原库和现在库进行分析
20210608215825
20210608215817
原库和目标库表结构一致,原库该表存储数据实际长度确实为1,但是在impdp导入的时候提示需要长度为3.通过分析,确认原库的nls_length_semantics参数设置为char了,直接使用impdp+network_link不落地方式导入该表数据成功
20210608215845
根据上述情况,查询相关文档,确认类似记录为:
ORA-12899 When Using IMPDP Over Network Link (Doc ID 414901.1)
ORA-26059 During Impdp Using Export Dump Taken With NETWORK_LINK Option (Doc ID 2266956.1)
虽然都不是完全匹配该问题,但是基本上可以确认expdp的network_link和nls_length_semantics参数是引起该问题的根本原因,在后续的迁移中,尽量保持nls_length_semantics参数一致.

发表在 逻辑备份/恢复 | 标签为 , , | 评论关闭

ORA-19921: maximum number of 64 rows exceeded

发表于 2021 年 5 月 28 日 由 惜分飞

rman 登录报ORA-19921错

[oracle@db-base ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Fri May 28 11:58:18 2021

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows
ORACLE error from target database: 
ORA-19921: maximum number of 64 rows exceeded

connected to target database: ORCL (DBID=1590736012)

RMAN>

通过检查rman进程发现大量未退出进程

[oracle@db-base trace]$ ps -ef|grep rman
oracle     998   985  0 May18 ?        00:00:02 rman oracle/11.2.0/db_1/bin/rman target /
oracle    1054  1039  0 Apr18 ?        00:00:10 rman oracle/11.2.0/db_1/bin/rman target /
oracle    1738  1726  0 Apr27 ?        00:00:08 rman oracle/11.2.0/db_1/bin/rman target /
oracle    4294  4281  0 May11 ?        00:00:02 rman oracle/11.2.0/db_1/bin/rman target /
oracle    4655  4642  0 May27 ?        00:00:01 rman oracle/11.2.0/db_1/bin/rman target /
oracle    4955  4943  0 Apr30 ?        00:00:01 rman oracle/11.2.0/db_1/bin/rman target /
oracle    5712  5700  0 Apr28 ?        00:00:02 rman oracle/11.2.0/db_1/bin/rman target /
oracle    7162  7149  0 May19 ?        00:00:01 rman oracle/11.2.0/db_1/bin/rman target /
oracle    7275  7262  0 Apr17 ?        00:00:02 rman oracle/11.2.0/db_1/bin/rman target /
oracle    7983  7971  0 May12 ?        00:00:02 rman oracle/11.2.0/db_1/bin/rman target /
oracle    8013  8002  0 10:59 ?        00:00:01 rman oracle/11.2.0/db_1/bin/rman target /
oracle    8376  8364  0 May26 ?        00:00:01 rman oracle/11.2.0/db_1/bin/rman target /
oracle    8519  8507  0 11:03 ?        00:00:01 rman oracle/11.2.0/db_1/bin/rman target /
oracle    9196  9184  0 11:10 ?        00:00:01 rman oracle/11.2.0/db_1/bin/rman target /
oracle    9345  9333  0 Apr29 ?        00:00:01 rman oracle/11.2.0/db_1/bin/rman target /
oracle    9420  9407  0 May01 ?        00:00:01 rman oracle/11.2.0/db_1/bin/rman target /
oracle    9831  9818  0 11:16 ?        00:00:01 rman oracle/11.2.0/db_1/bin/rman target /
oracle   10242 10229  0 May25 ?        00:00:01 rman oracle/11.2.0/db_1/bin/rman target /
oracle   11023 11010  0 Apr10 ?        00:00:02 rman oracle/11.2.0/db_1/bin/rman target /
oracle   11040 11020  0 Apr16 ?        00:00:08 rman oracle/11.2.0/db_1/bin/rman target /
oracle   11345 11332  0 Apr11 ?        00:00:02 rman oracle/11.2.0/db_1/bin/rman target /
oracle   11364 11343  0 Apr12 ?        00:00:02 rman oracle/11.2.0/db_1/bin/rman target /
oracle   11696 11684  0 Apr13 ?        00:00:02 rman oracle/11.2.0/db_1/bin/rman target /
oracle   12008 11998  0 11:39 ?        00:00:01 rman oracle/11.2.0/db_1/bin/rman target /
oracle   12454 12441  0 Apr15 ?        00:00:02 rman oracle/11.2.0/db_1/bin/rman target /
oracle   12680 12667  0 Apr14 ?        00:00:02 rman oracle/11.2.0/db_1/bin/rman target /
oracle   12751 12739  0 May13 ?        00:00:02 rman oracle/11.2.0/db_1/bin/rman target /
oracle   12849 12833  2 11:48 pts/1    00:00:26 rman oracle/11.2.0/db_1/bin/rman target /
oracle   13152 13140  0 May02 ?        00:00:01 rman oracle/11.2.0/db_1/bin/rman target /
oracle   13731 13719  0 Apr05 ?        00:00:01 rman oracle/11.2.0/db_1/bin/rman target /
oracle   13869 13857  0 May24 ?        00:00:01 rman oracle/11.2.0/db_1/bin/rman target /
oracle   14027 14014  0 Apr04 ?        00:00:01 rman oracle/11.2.0/db_1/bin/rman target /
oracle   14073 14061  0 Apr03 ?        00:00:07 rman oracle/11.2.0/db_1/bin/rman target /
oracle   14366 13332  0 12:03 pts/2    00:00:00 grep --color=auto rman
oracle   15073 15061  0 May23 ?        00:00:01 rman oracle/11.2.0/db_1/bin/rman target /
oracle   15263 15251  0 May22 ?        00:00:02 rman oracle/11.2.0/db_1/bin/rman target /
oracle   15766 15753  0 Apr02 ?        00:00:02 rman oracle/11.2.0/db_1/bin/rman target /
oracle   15915 15903  0 May14 ?        00:00:02 rman oracle/11.2.0/db_1/bin/rman target /
oracle   16805 16793  0 Mar31 ?        00:00:02 rman oracle/11.2.0/db_1/bin/rman target /
oracle   16953 16939  0 Apr01 ?        00:00:08 rman oracle/11.2.0/db_1/bin/rman target /
oracle   17648 17635  0 May21 ?        00:00:08 rman oracle/11.2.0/db_1/bin/rman target /
oracle   17740 17728  0 May03 ?        00:00:01 rman oracle/11.2.0/db_1/bin/rman target /
oracle   18265 18253  0 Apr09 ?        00:00:02 rman oracle/11.2.0/db_1/bin/rman target /
oracle   18964 18951  0 May15 ?        00:00:02 rman oracle/11.2.0/db_1/bin/rman target /
oracle   20731 20719  0 May20 ?        00:00:08 rman oracle/11.2.0/db_1/bin/rman target /
oracle   21104 21092  0 May04 ?        00:00:08 rman oracle/11.2.0/db_1/bin/rman target /
oracle   23116 23104  0 May16 ?        00:00:01 rman oracle/11.2.0/db_1/bin/rman target /
oracle   23230 23216  0 Apr07 ?        00:00:08 rman oracle/11.2.0/db_1/bin/rman target /
oracle   23969 23956  0 Apr08 ?        00:00:07 rman oracle/11.2.0/db_1/bin/rman target /
oracle   24092 24079  0 Apr24 ?        00:00:01 rman oracle/11.2.0/db_1/bin/rman target /
oracle   25648 25636  0 May07 ?        00:00:08 rman oracle/11.2.0/db_1/bin/rman target /
oracle   25843 25831  0 Apr23 ?        00:00:01 rman oracle/11.2.0/db_1/bin/rman target /
oracle   26261 26248  0 Apr25 ?        00:00:08 rman oracle/11.2.0/db_1/bin/rman target /
oracle   26421 26408  0 May08 ?        00:00:08 rman oracle/11.2.0/db_1/bin/rman target /
oracle   26470 26458  0 Apr22 ?        00:00:01 rman oracle/11.2.0/db_1/bin/rman target /
oracle   26776 26763  0 May05 ?        00:00:01 rman oracle/11.2.0/db_1/bin/rman target /
oracle   28587 28574  0 Apr26 ?        00:00:01 rman oracle/11.2.0/db_1/bin/rman target /
oracle   29102 29090  0 May09 ?        00:00:07 rman oracle/11.2.0/db_1/bin/rman target /
oracle   29402 29389  0 Apr20 ?        00:00:01 rman oracle/11.2.0/db_1/bin/rman target /
oracle   29628 29613  0 May17 ?        00:00:02 rman oracle/11.2.0/db_1/bin/rman target /
oracle   29638 29625  0 Apr06 ?        00:00:07 rman oracle/11.2.0/db_1/bin/rman target /
oracle   30118 30105  0 Apr21 ?        00:00:01 rman oracle/11.2.0/db_1/bin/rman target /
oracle   32536 32523  0 Apr19 ?        00:00:02 rman oracle/11.2.0/db_1/bin/rman target /
oracle   32609 32597  0 May10 ?        00:00:02 rman oracle/11.2.0/db_1/bin/rman target /

kill相关rman进程

[oracle@db-base trace]$ kill -9 `ps -ef|grep rman|grep -v grep|awk '{print $2}'`

rman 登录正常

[oracle@db-base trace]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Fri May 28 12:04:19 2021

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1590736012)

RMAN>
发表在 逻辑备份/恢复 | 标签为 , | 评论关闭