Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Howard.

Asked: October 29, 2024 - 10:14 am UTC

Last updated: November 11, 2024 - 6:49 am UTC

Version: 23.5.0.24.07

Viewed 1000+ times

You Asked

How do i create pluggable database in oracle linux 



Enter user-name: sys as sysdba
Enter password: 

Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.5.0.24.07

SQL> alter session set container=cdb$root;

Session altered.

SQL> show con_name 

CON_NAME
------------------------------
CDB$ROOT


SQL> CREATE PLUGGABLE DATABASE pdb1
  ADMIN USER pdb1admin IDENTIFIED BY welcome
  ROLES = (dba)
  DEFAULT TABLESPACE users
    DATAFILE '/opt/oracle/oradata/FREE/pdb1users01.dbf' SIZE 250M AUTOEXTEND ON
  FILE_NAME_CONVERT = ('/opt/oracle/oradata/FREE/pdbseed/',
                       '/opt/oracle/oradata/FREE/pdb1/'); 

CREATE PLUGGABLE DATABASE pdb1
*
ERROR at line 1:
ORA-01017: invalid credential or not authorized; logon denied
Help: https://docs.oracle.com/error-help/db/ora-01017/


SQL> show user
USER is "SYS"

SQL> select * from session_privs;

....

PRIVILEGE
----------------------------------------
EXECUTE DYNAMIC MLE
USE ANY JOB RESOURCE
LOGMINING
CREATE ANY CREDENTIAL
CREATE CREDENTIAL
ALTER LOCKDOWN PROFILE
DROP LOCKDOWN PROFILE
CREATE LOCKDOWN PROFILE
SET CONTAINER
CREATE PLUGGABLE DATABASE
INHERIT ANY REMOTE PRIVILEGES

....


Please help me to solve this problem

Thanks :)

and Chris said...

I'm not sure what's happening here. MOS note 730067.1 runs through lots of things to check if you're getting 1017 errors when connecting as sysdba.

It's worth checking this - though as it seems you did connect using sys as sysdba I'm not sure how relevant they will be. To get to the bottom of this please let us know:

How exactly did you connect to the database?
Do any other sys operations fail with the same error?
How have you created this database? Which pdbs already exist?

Rating

  (3 ratings)

Comments

Howard, November 02, 2024 - 3:02 am UTC

How exactly did you connect to the database?
[oracle@localhost ~]$ . oraenv
ORACLE_SID = [FREE] ? 
The Oracle base has been set to /opt/oracle
[oracle@localhost ~]$ sqlplus

SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Sat Nov 2 09:49:51 2024
Version 23.5.0.24.07

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

Enter user-name: sys as sysdba
Enter password: 

Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.5.0.24.07


Do any other sys operations fail with the same error?
SQL> create user ts identified by ts;

User created.

SQL> show con_name;

CON_NAME
------------------------------
FREEPDB1
SQL> alter session set container=cdb$root;

Session altered.

SQL> create user c##ts1 identified by ts1;
create user ts1 identified by ts1
*
ERROR at line 1:
ORA-01017: invalid credential or not authorized; logon denied
Help: https://docs.oracle.com/error-help/db/ora-01017/


SQL> show pdbs;

    CON_ID CON_NAME     OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
  2 PDB$SEED     READ ONLY  NO
  3 FREEPDB1     READ WRITE NO
SQL> alter session set container =freepdb1;

Session altered.
SQL> show pdbs;

    CON_ID CON_NAME     OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
  3 FREEPDB1     READ WRITE NO

I can't do operation on cdb$root, only in FREEPDB1

Thank you :)
Chris Saxon
November 05, 2024 - 3:42 pm UTC

What happens if you connect directly to the root container, rather than a PDB in the first place? e.g.

sqlplus sys/<pwd>@localhost/<root_sid> as sysdba

Howard, November 08, 2024 - 1:21 pm UTC

this is from the first window, i don't know how to find sid, so I use this link

https://asktom.oracle.com/ords/asktom.search?tag=what-is-a-sid-how-to-change-it-how-to-find-out-what-it-is#:~:text=How%20to%20find%20the%20sid,%24thread%22%20will%20do%20that.&text=This%20entry%20describes%20how%20to,instance%2C%20without%20recreating%20the%20database.

[oracle@localhost ~]$ . oraenv
ORACLE_SID = [FREE] ? 
The Oracle base remains unchanged with value /opt/oracle
[oracle@localhost ~]$ sqlplus 

SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Fri Nov 8 20:11:25 2024
Version 23.5.0.24.07

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

Enter user-name: sys as sysdba
Enter password: 
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1268181352 bytes
Fixed Size      5359976 bytes
Variable Size    553648128 bytes
Database Buffers   704643072 bytes
Redo Buffers      4530176 bytes
Database mounted.
Database opened.
SQL> alter session set container=cdb$root
  2  ;

Session altered.

SQL> select instance from v$thread;

INSTANCE
------------------------------
FREE


this is from the second window to connect 

[oracle@localhost ~]$ . oraenv
ORACLE_SID = [FREE] ? 
The Oracle base has been set to /opt/oracle
[oracle@localhost ~]$ sqlplus sys/oracle@localhost/FREE as sysdba

SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Fri Nov 8 20:15:05 2024
Version 23.5.0.24.07

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

ERROR:
ORA-01017: invalid credential or not authorized; logon denied
Help: https://docs.oracle.com/error-help/db/ora-01017/


Enter user-name: sys as sysdba
Enter password:  oracle

Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.5.0.24.07


the password is oracle
Chris Saxon
November 08, 2024 - 5:20 pm UTC

Right, so you're getting the error using password authentication (sqlplus sys/<pwd>@localhost/<root_sid> as sysdba).

The most important thing here - verify you have the correct password!

Assuming this is correct, then MOS note 730067.1 has a big bunch of things you can check. Here's some of the options for verifying password auth is working correctly:

1.Check the value of parameter remote_login_passwordfile. This has to be set to either EXCLUSIVE or SHARED:
SQL> show parameter remote_login_passwordfile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile string EXCLUSIVE

If the parameter is not set correctly then modify it and then restart the database:

SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

2. Check whether the password file with the correct name exists in the right directory/folder and has the right ownership and permissions

On Unix

The password file with the name orapw<ORACLE_SID> must exist in directory $ORACLE_HOME/dbs. If it does not exist then recreate it using the orapwd command

$ > orapwd file=$ORACLE_HOME/dbs/orapw<sid> password= force=y ignorecase=n

The permissions of the file should be the following:

-rw-r----- 1 oracle oinstall 1536 Jul 21 13:50 orapwdv11201

Howard, November 09, 2024 - 3:48 pm UTC 

[oracle@localhost ~]$ . oraenv
ORACLE_SID = [FREE] ? 
The Oracle base has been set to /opt/oracle
[oracle@localhost ~]$ sqlplus 

SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Sat Nov 9 22:22:51 2024
Version 23.5.0.24.07

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

Enter user-name: sys as sysdba
Enter password: 

Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.5.0.24.07

SQL> show parameter remote_login_passwordfile

NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile      string  EXCLUSIVE


I don't understand if the password to connect to root is different from pdb
I can connect to cdb$root using sys as sysdba after login but there's nothing I can do
And what password should I input is it the same when I try to connect with sys as sysdba method?
should I change my password ?


Connor McDonald
November 11, 2024 - 6:49 am UTC

sys as sysdba

might just be using the local dba privs at OS level.

To ensure your password is correct, you need to be doing:

sys@service as sysdba

If that is not working, then you've got something awry in your password file most probably
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Security

All of the vital components for a secure database are covered in the Security guide.