Re: impdp Issue
Date: Fri, 13 Sep 2024 11:26:56 -0400
Message-ID: <3b8b4b9d-619c-47ca-9311-22f3e80e1afe_at_comcast.net>
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_| 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 <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>
> *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_| 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
> <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';
>
> 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:26:56 CEST