OraFAQ Forum: Backup & Recovery » Import dump file to Cloud DB

Home » RDBMS Server » Backup & Recovery » Import dump file to Cloud DB (12.2.0.1.200714)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Import dump file to Cloud DB [message #682438]

Fri, 23 October 2020 15:55

markc68
Messages: 2
Registered: October 2020

Junior Member

Hi,

I have an Oracle VM Cloud 12.2 database and need some advice on how I can import a .dp file. I have created tablespaces and users via scripts I was given with the dump file. The tablespaces and user were created in the PDB. I have transferred my .dp file and .log file via WinSCP to /home/opc on the Cloud server. Not sure where to go from here. I tried the import wizard in SQL Developer but not sure where the .dp needs to be. I've got DB experience but not with Oracle. Any help or pointers would be much appreciated.

Report message to a moderator

Re: Import dump file to Cloud DB [message #682440 is a reply to message #682438]

Sat, 24 October 2020 02:46

John Watson
Messages: 8869
Registered: January 2010
Location: Global Village

Senior Member

Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

Better start with the docs, a summary in the Concepts Guide
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/cncpt/concepts-for-database-administrators.html#GUID-DCDD0FBF-0AD2-45 E1-B723-21CC72ADDCD6
and detail here,
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sutil/oracle-data-pump.html#GUID-501A9908-BCC5-434C-8853-9A6096766B5A

As to where the dump file needs to be: you create a directory (look up the CREATE DIRECTORY command in the SQL Reference for that, https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/index.html ) pointing to the location, and specify that directory on your impdp command.

Report message to a moderator

Re: Import dump file to Cloud DB [message #682464 is a reply to message #682440]

Sat, 24 October 2020 16:16

markc68
Messages: 2
Registered: October 2020

Junior Member

Thank you John for your reply, much appreciated. I eventually got there. It was a bit painful!

I'm sure there are a loads of way to do this but if it helps anyone here's what I did to import a dump file to a cloud db.

#Disclaimer - I only started using Oracle DB's last week, there may be a much better way of doing this. Feel free to tear apart.

Background -
Tablespaces were created in the PB database. WinSCP connection defaulted to /home/opc when copied the dump file to the cloud, the dump file then needed to be moved to a directory oracle could access. I physically created a directory on the server /tmp/data as oracle user. My first attempt failed as I was using @<dbname>_PDB1 in the impdp call. To resolve this I amended the tsnames.ora file. It had the details of the container DB but not the pluggable. I just copied the container details and changed the details for the PD.

Then I -
Transfer .dp and .log file via WinSCP to /home/opc
copy .dp and .log from /home/opc to /tmp/data in WinSCP and change properties of both files to read write (777)

Open Putty session to cloud DB.
sudo su - oracle
sqlplus / as sysdba
CREATE OR REPLACE DIRECTORY IMPORT_DIR as '/tmp/data';
grant read,write on directory IMPORT_DIR to system;
ALTER SESSION SET CONTAINER=<dbname>_PDB1;
exit;

impdp system@<dbname>_PDB1 directory=IMPORT_DIR schemas=123,456,789 dumpfile=xx.dp logfile=xx_import.log
You will be prompted for <systempassword>

Report message to a moderator

Re: Import dump file to Cloud DB [message #682465 is a reply to message #682464]

Sat, 24 October 2020 16:32

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

I don't have a lot of experience yet with cdb/pdb, but the first thing I thought of when reading your solution was "why connect locally as sysdba" (which connects to the containter) when you already fixed the tnsnames issue and could have connected directly to the pdb"?

And second, since you were connected to the cdb when you issued the CREATE and GRANT, what was the point of the ALTER SESSION SET CONTAINER after you had done those things?

Report message to a moderator

Previous Topic:	RMAN duplicate database
Next Topic:	catalog datacopy file very slow

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Sep 23 10:40:03 CDT 2023