RE: impdp Issue
Date: Fri, 13 Sep 2024 15:31:35 +0000
Message-ID: <CH3PR16MB5897E7AA8DDA4EB6D83B906CC5652_at_CH3PR16MB5897.namprd16.prod.outlook.com>
It is a public database link. The person at Oracle has now sent it to the network team. I really believe this is a bug introduced in the July patch. Since we can’t wait for a fix, I’ve requested additional disk space to do this the old way (export, ftp, import).
Scott Canaan ‘88
Sr Database Administrator
Information & Technology Services
Finance & Administration
Rochester Institute of Technology
o: (585) 475-7886 | f: (585) 475-7520
srcdco_at_rit.edu<mailto:srcdco_at_rit.edu> | c: (585) 339-8659
CONFIDENTIALITY NOTE: The information transmitted, including attachments, is intended only for the person(s) or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and destroy any copies of this information.
From: richard goulet <rjgoulet_at_comcast.net> Sent: Friday, September 13, 2024 11:27 AM To: Scott Canaan <srcdco_at_rit.edu>; Oracle L <oracle-l_at_freelists.org> Subject: Re: impdp Issue
Scott and Doug,
I do believe the PUBLIC vs private db link could be the issue, but as promised:
I've used this worked from good old SQL*Plus as a DBA account, and yes this requires the public db link using the same dba account on both sides. Note, the schema MUST exist on the target database as well as the tablespaces and the target account needs the appropriate permissions and quotas.
DISCLAIMER: USE AT YOUR OWN PERIL AND ONLY ON DEVELOPMENT DATABASES. THIS IS PROVIDED AS IS WITH NO WARRANTY. procedure schema_load(schema in varchar2, to_schema in varchar2 default null,
log_name in varchar2 default NULL, link in varchar2 default null) as
h1 NUMBER := 0;
h2 varchar2(1000);
ex boolean := TRUE;
fl number := 0;
schema_exp varchar2(100);
schema_log varchar2(100);
dbname varchar2(1000);
SUCCESS_WITH_INFO exception;
begin
dbname := global_name_str;
if(log_name is null) then schema_log := schema||'_in.log';
else schema_log := log_name;
end if;
schema_exp := 'in('''||upper(schema)||''')'; if(link is null) then
h1 := dbms_datapump.open (operation => 'IMPORT', job_mode => 'SCHEMA', remote_link => NULL, job_name => upper(schema)||'_EXP', version => 'COMPATIBLE');
dbms_datapump.add_file(handle => h1, filename => schema||'.dpp', directory => 'DATA_PUMP_DIR', filetype => 1);
else
h1 := dbms_datapump.open (operation => 'IMPORT', job_mode => 'SCHEMA', remote_link => link, job_name => upper(schema)||'_EXP', version => 'COMPATIBLE');
end if;
dbms_datapump.set_parallel(handle => h1, degree => 2);
dbms_datapump.add_file(handle => h1, filename => schema_log, directory => 'DATA_PUMP_DIR', filetype => 3);
dbms_datapump.set_parameter(handle => h1, name => 'KEEP_MASTER', value => 0);
dbms_datapump.metadata_transform(handle=>h1, name=>'OID', value=>0);
dbms_datapump.metadata_filter(handle => h1, name=> 'SCHEMA_EXPR',value => schema_exp);
if(to_schema is not null) then
dbms_datapump.metadata_remap (handle=>h1, name=>'REMAP_SCHEMA',old_value=>upper(schema),value=>upper(to_schema));
end if;
dbms_datapump.start_job(handle => h1, skip_current => 0, abort_step => 0); dbms_datapump.wait_for_job(h1, h2); if(send_log = TRUE) then mail_log('DATA_PUMP_DIR', schema_log, initcap(schema)||'.log', email_log_to, NULL, 'Schema Dump Log'); end if;
exception
when SUCCESS_WITH_INFO THEN NULL; when others then
h2 := sqlerrm;
begin
if(h1 != 0) then dbms_datapump.stop_job(h1,1,0,0);
end if;
exception when others then null;
end;
end;
On 9/12/2024 11:27, Scott Canaan wrote: I agree. I’ve done this dozens, if not hundreds, of times over the years. This is how we migrate to new versions of the O/S. This particular one worked when I did it in January. The only changes to the environments since then have been Oracle and O/S patching, with Oracle patching only happening on the 19c side. The last time I did this on another database was early July, so that worked prior to the July Oracle patchset.
Scott Canaan ‘88
Sr Database Administrator
Information & Technology Services
Finance & Administration
Rochester Institute of Technology
o: (585) 475-7886 | f: (585) 475-7520
srcdco_at_rit.edu<mailto:srcdco_at_rit.edu> | c: (585) 339-8659
CONFIDENTIALITY NOTE: The information transmitted, including attachments, is intended only for the person(s) or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and destroy any copies of this information.
From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> <oracle-l-bounce_at_freelists.org><mailto:oracle-l-bounce_at_freelists.org> On Behalf Of richard goulet Sent: Thursday, September 12, 2024 11:11 AM To: Oracle L <oracle-l_at_freelists.org><mailto:oracle-l_at_freelists.org> Subject: Re: impdp Issue
Scott,
Very nice. In this case impdp should work, but I'm no guru at the command line implementation. Give me the day while I extract how I use to do this with in PL/SQL on a routine basis. On 9/11/2024 13:09, Scott Canaan wrote: That is correct. The database link is in the database and it works. I can pull data from the remote database using the database link in SQL*Plus:
SQL> select name, created from v$database_at_advcprod;
NAME CREATED
--------- ---------
ADVCPROD 23-DEC-16
SQL> select name, created from v$database;
NAME CREATED
--------- ---------
ADVCDEVL 01-AUG-23
Scott Canaan ‘88
Sr Database Administrator
Information & Technology Services
Finance & Administration
Rochester Institute of Technology
o: (585) 475-7886 | f: (585) 475-7520
srcdco_at_rit.edu<mailto:srcdco_at_rit.edu> | c: (585) 339-8659
CONFIDENTIALITY NOTE: The information transmitted, including attachments, is intended only for the person(s) or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and destroy any copies of this information.
From: oracle-l-bounce_at_freelists.org<mailto:oracle-l-bounce_at_freelists.org> <oracle-l-bounce_at_freelists.org><mailto:oracle-l-bounce_at_freelists.org> On Behalf Of richard goulet Sent: Wednesday, September 11, 2024 12:51 PM To: Oracle L <oracle-l_at_freelists.org><mailto:oracle-l_at_freelists.org> Subject: impdp Issue
It's been a bit, but as I remember to use a db link with IMPDP or EXPDP you have to have it defined within the database your logging into. By that I mean where your running the datapump process from as in
CREATE DATABASE LINK 'advcprod.world' USING 'advcprod.world';
So that the following will work:
select * from global_name_at_advcprod.world<mailto:global_name_at_advcprod.world>';
This is because your spawning the data pump process within the local database to the remote database.
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Sep 13 2024 - 17:31:35 CEST