Skip to Main Content
  • Questions
  • Datapump from a pluggable database using an externally authenticated account ( ops$oracle ) prompts for a password

Breadcrumb

Easter

Question and Answer

Connor McDonald

Thanks for the question, Orna.

Asked: March 18, 2020 - 6:09 pm UTC

Answered by: Connor McDonald - Last updated: March 24, 2020 - 12:07 am UTC

Category: Database Administration - Version: 19

Viewed 100+ times

You Asked

This is super weird.

When using the following command from a pluggable database :

expdp / dumpfile=ssss.dmp

( TWO_TASK is set obviously )

I am then prompted for a password.
However - this is bogus - I simply press 'enter' and the export continue OK

sqlplus / --> works fine with no prompt.

that smells like a bug, but I could not find any mention in Metalink

Appreciate an explanation
Orna


==== More information ===

lsxdblex01@oslex03.ivanet.net:lsxcont>sqlplus "/as sysdba"

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Mar 20 12:12:39 2020
Version 19.3.0.0.0

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


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

SQL> select name, con_id, db_unique_name from v$database;

NAME CON_ID DB_UNIQUE_NAME
--------- ---------- ------------------------------
LSXCONT 0 lsxcont

SQL>
SQL> show parameter os

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_lost_write_protect string NONE
diagnostic_dest string /lsx/u01/app/oracle
optimizer_index_cost_adj integer 100
os_authent_prefix string OPS$
os_roles boolean FALSE
pdb_os_credential string
remote_os_authent boolean TRUE
remote_os_roles boolean FALSE
timed_os_statistics integer 0
SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB READ WRITE NO
4 LSXD READ WRITE NO
5 LSXT READ WRITE NO

SQL> alter session set container=pdb;

Session altered.

SQL> show parameter os

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_lost_write_protect string NONE
diagnostic_dest string /lsx/u01/app/oracle
optimizer_index_cost_adj integer 100
os_authent_prefix string OPS$
os_roles boolean FALSE
pdb_os_credential string
remote_os_authent boolean TRUE
remote_os_roles boolean FALSE
timed_os_statistics integer 0

SQL> create user ops$oracle identified externally;

User created.

SQL> grant dba to ops$oracle;

Grant succeeded.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
lsxdblex01@oslex03.ivanet.net:lsxcont>export TWO_TASK=pdb
lsxdblex01@oslex03.ivanet.net:lsxcont>tnsping pdb

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 20-MAR-2020 12:08:40

Copyright (c) 1997, 2019, Oracle. All rights reserved.

Used parameter files:
/lsx/u01/app/oracle/product/19.3/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = lsxdblex01)(PORT = 2520)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdb)))
OK (10 msec)
lsxdblex01@oslex03.ivanet.net:lsxcont>sqlplus /

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Mar 20 12:08:50 2020
Version 19.3.0.0.0

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


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

SQL> show user
USER is "OPS$ORACLE"
SQL> select decode(sys_context('USERENV', 'CON_NAME'),'CDB$ROOT',
sys_context('USERENV', 'DB_NAME'),sys_context('USERENV', 'CON_NAME')) DB_NAME,
2 3 decode(sys_context('USERENV','CON_ID'),1,'CDB','PDB') TYPE
4 from DUAL;

DB_NAME
--------------------------------------------------------------------------------
TYP
---
PDB
PDB


SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
lsxdblex01@oslex03.ivanet.net:lsxcont>echo $TWO_TASK
pdb
lsxdblex01@oslex03.ivanet.net:lsxcont>expdp / dumpfile=asktom.dmp full=y

Export: Release 19.0.0.0.0 - Production on Fri Mar 20 12:11:00 2020
Version 19.3.0.0.0

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

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "OPS$ORACLE"."SYS_EXPORT_FULL_01": /******** dumpfile=asktom.dmp full=y
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
^C
Export> kill_job
Are you sure you wish to stop this job ([yes]/no): yes

lsxdblex01@oslex03.ivanet.net:lsxcont>

and we said...

You're not going to like this :-) but its SQLPlus that probably has the bug here.

In particular, remote_os_authent is deprecated because I suspect SQL PLus is getting in due to that.

I asked around internally, and in essence "connect with OS directly to a PDB" is not expected to be possible.

The solution is via wallets as described here

https://connor-mcdonald.com/2015/09/21/connection-shortcuts-with-a-wallet/

https://connor-mcdonald.com/2019/03/07/connections-with-a-wallet-redux/

and you rated our response

  (1 rating)

We're not taking reviews currently, so please try again later if you want to add a review.

Reviews

OK.. but

March 24, 2020 - 1:09 am UTC

Reviewer: Orna Weisman

First of all - expdp with ops$oracle DOES still work on PDB , it only prompts you for password that you do not really have to enter.
an <enter> after the prompt will get you through.
( It was actually a coincidental for me , because this was a cron job - and the password prompt was not interfering with the completion of the export.. it only pauses you when you run it on a command line.)

Second - Oracle spends a lot of effort trying to make people use PDBs, including giving it free for up to 3 in one CDB.
Now , deprecating ops$oracle is dangerous here because there are probably a gazillion automated jobs and scripts that rely on ops$oracle to work and now will have to change if you decide to convert your databases to pluggable.. just saying

Something to think about

I will look into the links you sent me

thank you
Orna

More to Explore

Utilities

All of the database utilities are explained in the Utilities guide.