| Slow DataPump with broken DB Links [message #690279] |
Fri, 14 February 2025 03:47  |
Frank Naude
Messages: 4593
Registered: April 1998
|
Senior Member |
|
|
Second time I run into this issue. The database has several old and broken DB links, or the firewalls have not been opened for the MViews.
The impdp will import the data in record time, even across the network with NETWORK_LINK, especially if PARALLEL is set to a high value. However, it then grinds to a halt on "Processing object type DATABASE_EXPORT/SCHEMA/MATERIALIZED_VIEW."
At this stage, you can see that work is performed serially (so much for the PARALLEL option!), and it takes several minutes to (attempt to) create a single MView. It's so bad that you quickly realize it would be faster to redo the entire import without the MViews. I suppose that reducing the TNS timeout parameters might also help.
Alert.log is flooded with:
Fatal NI connect error 12170, connecting to:
(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(COMMUNITY=xxx)(PROTOCOL=TCP)(Host=xxx)(Port=1521))(ADDRESS=(COMMUNITY=xxx)(PROTOCOL=TCP)(Host=xxx)(Port=1526))(ADDRESS=(COMMUNITY=xxx)(PROTOCOL=TCP)(Host=xxx)(Port=1643)))(CONNECT_DATA=(SID=xxx)(SERVER=DEDICATED)(GLOBAL_NAME=xxx)(CID=(PROGRAM=oracle)(HOST=xxx)(USER=oracle))))
VERSION INFORMATION:
TNS for Linux: Version 19.0.0.0.0 - Production
Oracle Bequeath NT Protocol Adapter for Linux: Version 19.0.0.0.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 19.0.0.0.0 - Production
Version 19.26.0.0.0
Time: 14-FEB-2025 11:26:09
Tracing not turned on.
Tns error struct:
ns main err code: 12535
TNS-12535: TNS:operation timed out
ns secondary err code: 12560
nt main err code: 505
TNS-00505: Operation timed out
nt secondary err code: 0
nt OS err code: 0 [Updated on: Fri, 14 February 2025 03:57] Report message to a moderator |
|
|
|
|
|
| Re: Slow DataPump with broken DB Links [message #690281 is a reply to message #690279] |
Fri, 14 February 2025 06:50  |
John Watson
Messages: 8965
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
If you setin your server's sqlnet.ora then any attempt to use a dud link should fail really quickly:orclz>
orclz> create database link bad using '1.2.3.4:1521/bad';
Database link created.
orclz> set timing on
orclz> select * from dual@bad;
select * from dual@bad
*
ERROR at line 1:
ORA-12170: TNS:Connect timeout occurred
Elapsed: 00:00:01.12
orclz>
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
