Yet another IT Consultant
Good old Oracle 21c is dying

Good old Oracle 21c is dying

Journey to export

Tom Lieber's photo
Tom Lieber
·

4 min read

Email from Oracle...

Darn it, why did I start with a 21c back then?

Hello,

Your Always Free Oracle database running Oracle Database 21c Innovation Release will be terminated during June - August 2023. This is in preparation for Oracle’s fleet of free databases being migrated to a new release. To retain your data, please export it from this database prior to termination.

After exporting, you can import into another free or paid Autonomous Database. You can import now into a database running Oracle Database 19c or wait a short time following this termination date when Oracle Database 23c Long-Term Support Release will become available in newly created free databases. Note that Oracle Database 19c free databases will be autonomously upgraded to 23c at a later time. An easy way to export and import data is to use Oracle DataPump.

After exporting, let's just make a dump then.

expdp admin@kiadb full=YES dumpfile=full.dmp


Export: Release 21.0.0.0.0 - Production on Tue Feb 28 19:03:40 2023
Version 21.9.0.0.0

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

UDE-28759: operation generated ORACLE error 28759
ORA-28759: failure to open file

Can this hint help me here?

Connecting to Oracle Cloud Database ORA-28759: failure to open file | TalkApex

"I tried it, but unfortunately it didn't help."

Where does Oracle actually want to write the dump file?

select * from dba_directories;
SYS    DATA_PUMP_DIR    /u03/dbfs/irgendaws/data/dpdump    890

"What are you doing?"

Hmm, that sounds quite interesting.

DBMS_CLOUD: Zugriff auf Object Storage aus der Oracle Datenbank - Praktische Anwendungsfälle

Create an OCI credential.

BEGIN
   DBMS_CLOUD.CREATE_CREDENTIAL (
       credential_name => 'OCI_KEY_CRED',
       user_ocid       => 'ocid1.user.oc1..xx',
       tenancy_ocid    => 'ocid1.tenancy.oc1..xx',
       private_key     => 'KlEELVTr6zI+xx+gCs8Sw=',
       fingerprint     => '43:3f:0c:15:bd:46:5e:df:f8:9d:f2:bf:48:5a:92:5b');
END;
/

-- wie sich später herausstellte muss ich eine andere art von Credentials anlegen -- mit username und pwd --

"Tim Hall is my savior."

ORACLE-BASE - Oracle Cloud : Autonomous Database (ADW or ATP) - Export Data to an Object Store (expdp)

expdp admin@kiadb full=YES directory=data_pump_dir dumpfile=https://objectstorage.eu-frankfurt-1.oraclecloud.com/p/xyz/n/fryljaksyzkx/b/bucket_oia_mediafiles/o/full.dmp credential=OCI_KEY_CRED

Aber...

Export: Release 21.0.0.0.0 - Production on Tue Feb 28 19:03:40 2023
Version 21.9.0.0.0

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

UDE-28759: operation generated ORACLE error 28759
ORA-28759: failure to open file

-- Wie sich später herrausstellte bedeutet es: FAILURE TO OPEN WALLET FILE!

I once asked Conner McDonald...

Apparently, I didn't have the wallet in the right place here.

expdp admin@kiadb full=YES directory=data_pump_dir dumpfile=https://objectstorage.eu-frankfurt-1.oraclecloud.com/p/xyz/n/fryljaksyzkx/b/bucket_oia_mediafiles/o/full.dmp credential=OCI_KEY_CRED

--- Aber wieder ---
Export: Release 21.0.0.0.0 - Production on Tue Feb 28 19:03:40 2023
Version 21.9.0.0.0

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

ORA-39001: invalid argument value
ORA-39208: Parameter dumpfile=PRE-AUTHENTICATED URI is invalid for EXPORT jobs.

Hmm, why so? ;)

I found a good resource as well... but unfortunately, it doesn't help me yet.

Autonomous Database Archives - ORACLE-HELP

Conner suggested that I try it with credential=DEFAULT_CREDENTIAL.

expdp admin@kiadb full=YES directory=data_pump_dir dumpfile=https://.. credential=DEFAULT_CREDENTIAL

--- Aber wieder ---
Export: Release 21.0.0.0.0 - Production on Tue Feb 28 19:03:40 2023
Version 21.9.0.0.0

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

ORA-39001: invalid argument value
ORA-39208: Parameter dumpfile=PRE-AUTHENTICATED URI is invalid for EXPORT jobs.
-- Wie sich herausstellet muss ich hier eine andere URI verwenden.. die ich übrigens nicht einfach so im Oracle Cloud GUI gefunden habe --

Attempt 1:

ALTER DATABASE PROPERTY SET DEFAULT_CREDENTIAL = 'ADMIN.OCI_KEY_CRED'

-- Wie sich herausstellet muss ich hier eine andere Credentialart  verwenden.. steht irgendwo in irgendeiner Doku --

Attempt 2:

You are not allowed to use any Preauth URI that OCI displays in the UI. Instead, you should only use the Swift Naming Convention. This means deleting everything from the URL starting from "/p/" until "/n/".

i tryed:

-- nun mit der Swift Naming Convention
expdp... dumpfile=default_credential:https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/fryljaksyzkx/b/bucket_oia_mediafiles/o/full.dmp

got a new error:

ORA-31641: unable to create dump file "https://swiftobjectstorage.eu-frankfurt-1.oraclecloud.com/v1/fryljaksyzkx/bucket_oia mediafiles/full.dmp" ORA-17502: ksfdcre:4 Failed to create file https://swiftobjectstorage.eu-frankfurt-1.oraclecloud.com/v1/fryljaksyzkx/bucket oia_mediafiles/full.dmp ORA-17500: ODM err:ODM HTTP Not Found

-- geht nicht.. okay ab dann musste ich versuchen eine andere art der CRED zu probieren.. bisher ging es nicht..

I am not alone with this problem.

Data Pump Export on Oracle Autonomous – Running in the clouds (gleb.ca)

In short, I wasn't able to use the other type of credentials at the end, so I ended up doing it through 2 steps.

--Datapump: Full-Export funktioniert ohne direkten Upload

expdp admin@kiadb full=Y directory=data_pump_dir dumpfile=full.dmp

--Upload via dbms_cloud.put_object funktioniert mit PREAUTH URL

BEGIN
   DBMS_CLOUD.PUT_OBJECT(credential_name => 'OCI_KEY_CRED',
     object_uri => 'https://objectstorage.eu-frankfurt-1.oraclecloud.com/p/xxyy/n/fryljaksyzkx/b/bucket_oia_mediafiles/o/',
     directory_name => 'DATA_PUMP_DIR',
     file_name => 'full.dmp');
end;
/

I see light at the end of the tunnel.

However, what should I do with the APEX Instance that is also a 21c? I cannot even access the database. This is quite concerning.

some days later..
can't export in frankfurt .. doesnt feel good ;-)

and now..

What is the real Problem - no filesystem or space usage?
Trying just on small schema:

OracleOracle Free Tier