1

I'm trying to make a export, usually I use userid=\"/ as sysdba\" syntax in Linux command line (I don't know sys password in my production environment.

How can I do this using pluggable database? I tried something like this: userid=\"/ as sysdba@mypdb_oracle_net\" but it does not work.

expdp userid=\"/ as sysdba@mypdb_oracle_net\" dumpfile=full_%U.exp logfile=full.log directory=teste full=y parallel=4 version=11.2.0

Export: Release 19.0.0.0.0 - Production on Tue Mar 10 11:18:06 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
UDE-00002: invalid username or password

Username:
3

First of all, to export from a PDB you shouldn't use SYS for that. It is way over-privileged. You should create a user in the PDB and use that user to connect to the database. You can use the privilege "exp_full_database" to grant the necessary privileges.

Next, you have to specify a connect string to go directly into the PDB (via the relevant service). It is "mypdb_oracle_net" in your example. However, when doing so, you must specify the password for SYS. You can't use OS authentication when doing this.

To solve your problem I would do something like this:

SQLPLUS / AS SYSDBA
ALTER SESSION SET CONTAINER=mypdb;
CREATE USER EXPORT_USER IDENTIFIED BY YOUR_PASSWORD;
GRANT CREATE SESSION TO EXPORT_USER;
GRANT exp_full_database TO EXPORT_USER;

EXPDP EXPORT_USER/YOUR_PASSWORD@mypdb_oracle_net dumpfi......
2

First of all, \"/ as sysdba@mypdb_oracle_net\" will not work. SYSDBA OS authentication will not work through the listener.

Second, \"/ as sysdba\" is flawed in 19c (compared to earlier versions), because of a bug:

Bug 28707931 - Running IMPDP With OS Authentication Prompts For A Password (Doc ID 28707931.8)

Basically Data Pump will prompt for a password using the above, where you can enter anything and it will proceed. The bug is fixed in 20.1, or you can download a one-off patch for your version with a valid support contract.

Just create a user and use its name and password.

0

There is an unpublished environment variable ORACLE_PDB_SID, which works in conjunction with ORACLE_SID.

Example 1

With just ORACLE_SID set, you will connect CDB$ROOT

export ORACLE_SID=cdb1
sqlplus / as sysdba
show con_name

Example 2

With both set, you will connect to the PDB, if it exists.

export ORACLE_PDB_SID=pdb2
sqlplus / as sysdba
show con_name

However, if cdb1 does not have pdb2, then you will be connected to CDB$ROOT.

When ORACLE_PDB_SID is set, you will NOT connect to CDB$ROOT if the PDB exists.

References:

Both from Mike Dietrich's blog:

0

Got the answer from Mike Dietrich's blog too

Here is a summary of the blog post

At first, I set the environment variable:

export ORACLE_PDB_SID=PDBHUGO

And then I connect with / as sysdba and see what happens.

$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu May 7 22:30:05 2020
Version 19.7.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.7.0.0.0

SQL> show con_name

CON_NAME
------------------------------
PDBHUGO

https://mikedietrichde.com/2020/05/07/can-you-connect-as-sysdba-without-password-into-a-pdb-directly/

New contributor
Abdifatah Khalif is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.