How to use / as sysdba in pluggable database?

Question

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:

Answer 1

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......

Answer 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.

Answer 3

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:

• Can you connect as SYSDBA without password into a PDB directly? (20200507)
• Pitfalls: Connect to a PDB directly with ORACLE_PDB_SID (20200713)

Answer 4

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/