KRISHNA KUMAR VALUPADASU
Step 1: Create a User on the Target Database
SQL> select name, open_mode, log_mode from v$database;
NAME OPEN_MODE LOG_MODE
--------- -------------------- ------------
ORCLCDB READ WRITE ARCHIVELOG
SQL> select name, OPEN_MODE, OPEN_TIME, con_id from v$containers;
NAME OPEN_MODE OPEN_TIME
CON_ID
-------------------- ---------- -------------------------------------- ----
CDB$ROOT READ WRITE 02-DEC-24 12:55:06.544 PM -06:00 1
PDB$SEED READ ONLY 02-DEC-24 12:55:06.574 PM -06:00 2
TESTPDB READ WRITE 02-DEC-24 12:55:07.608 PM -06:00 3
SQL> select banner from v$version;
BANNER
---------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
SQL> create user c##test identified by krishna123 default tablespace users
temporary tablespace temp quota unlimited on users;
User created.
SQL> grant create session, connect to c##test;
Grant succeeded.
SQL> conn c##test
Enter password:
Connected.
Step 2: Create the Wallet in the Directory E:\ORACLE19\orclwal
PS E:\ORACLE19> mkdir orclwal
Directory: E:\ORACLE19
Mode LastWriteTime Length Name
---- ------------- ------ ----
d----- 03-12-2024 13:04 orclwal
PS E:\ORACLE19\orclwal> pwd
Path
----
E:\ORACLE19\orclwal
PS E:\ORACLE19\orclwal> orapki wallet create -wallet E:\ORACLE19\orclwal -
auto_login
Oracle PKI Tool Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights
reserved.
Enter password:
KRISHNA KUMAR VALUPADASU
KRISHNA KUMAR VALUPADASU
Enter password again:
Operation is successfully completed.
PS E:\ORACLE19\orclwal> pwd
Path
----
E:\ORACLE19\orclwal
PS E:\ORACLE19\orclwal> ls
Directory: E:\ORACLE19\orclwal
Mode LastWriteTime Length Name
---- ------------- ------ ----
-a---- 03-12-2024 13:57 194 cwallet.sso
-a---- 03-12-2024 13:57 0 cwallet.sso.lck
-a---- 03-12-2024 13:57 149 ewallet.p12
-a---- 03-12-2024 13:57 0 ewallet.p12.lck
PS E:\ORACLE19\orclwal> mkdir TNS_ADMIN
Directory: E:\ORACLE19\orclwal
Mode LastWriteTime Length Name
---- ------------- ------ ----
d----- 03-12-2024 13:58 TNS_ADMIN
PS E:\ORACLE19\orclwal> ls
Directory: E:\ORACLE19\orclwal
Mode LastWriteTime Length Name
---- ------------- ------ ----
d----- 03-12-2024 13:58 TNS_ADMIN
-a---- 03-12-2024 13:57 194 cwallet.sso
-a---- 03-12-2024 13:57 0 cwallet.sso.lck
-a---- 03-12-2024 13:57 149 ewallet.p12
-a---- 03-12-2024 13:57 0 ewallet.p12.lck
PS E:\ORACLE19\orclwal\TNS_ADMIN> pwd
Path
----
E:\ORACLE19\orclwal\TNS_ADMIN
PS E:\ORACLE19\orclwal\TNS_ADMIN> ls
Directory: E:\ORACLE19\orclwal\TNS_ADMIN
Mode LastWriteTime Length Name
---- ------------- ------ ----
-a---- 05-12-2024 10:37 590 sqlnet.ora
-a---- 04-12-2024 13:22 1454 tnsnames.ora
Add Entry in tnsnames.ora
ORCLWAL=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = KRISHNA)(PORT = 1521))
)
KRISHNA KUMAR VALUPADASU
KRISHNA KUMAR VALUPADASU
(CONNECT_DATA =
(SERVICE_NAME = orclcdb.attlocal.net) ==> Ensure the specified
service exists.
)
)
Update sqlnet.ora with Wallet Location
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA =
(DIRECTORY=E:\ORACLE19\orclwal)))
SQLNET.WALLET_OVERRIDE = TRUE
Verify listener status and make all services are up.
PS E:\ORACLE19\orclwal\TNS_ADMIN> lsnrctl status
LSNRCTL for 64-bit Windows: Version 19.0.0.0.0 - Production on 05-DEC-2024
13:18:30
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 64-bit Windows: Version 19.0.0.0.0 -
Production
Start Date 02-DEC-2024 13:35:49
Uptime 2 days 23 hr. 42 min. 42 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File
E:\ORACLE19\WINDOWS.X64_193000_db_home\network\admin\listener.ora
Listener Log File
E:\ORACLE19\ORACLEME\diag\tnslsnr\KRISHNA\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=KRISHNA)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=KRISHNA)(PORT=5500))(Security=(m
y_wallet_directory=E:\ORACLE19\ORACLEME\admin\orclcdb\xdb_wallet))(Presenta
tion=HTTP)(Session=RAW))
Services Summary...
Service "52448234712340b69f274bcc790ecfe0.attlocal.net" has 1 instance(s).
Instance "orclcdb", status READY, has 1 handler(s) for this service...
Service "64baad14383e44b68f19a4e3751ef58d.attlocal.net" has 1 instance(s).
Instance "orclcdb", status READY, has 1 handler(s) for this service...
Service "ORCLCDB" has 1 instance(s).
Instance "ORCLCDB", status UNKNOWN, has 1 handler(s) for this service...
Service "krishna.attlocal.net" has 1 instance(s).
Instance "orclcdb", status READY, has 1 handler(s) for this service...
Service "orclcdb.attlocal.net" has 1 instance(s).
Instance "orclcdb", status READY, has 1 handler(s) for this service...
Service "orclcdbXDB.attlocal.net" has 1 instance(s).
Instance "orclcdb", status READY, has 1 handler(s) for this service...
Service "testpdb.attlocal.net" has 1 instance(s).
KRISHNA KUMAR VALUPADASU
KRISHNA KUMAR VALUPADASU
Instance "orclcdb", status READY, has 1 handler(s) for this service...
The command completed successfully
Verify entry with tnsping
PS E:\ORACLE19\orclwal\TNS_ADMIN> tnsping ORCLWAL
TNS Ping Utility for 64-bit Windows: Version 19.0.0.0.0 - Production on 03-
DEC-2024 13:22:55
Copyright (c) 1997, 2019, Oracle. All rights reserved.
Used parameter files:
E:\ORACLE19\orclwal\TNS_ADMIN\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL =
TCP)(HOST = KRISHNA)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME =
orclcdb.attlocal.net)))
OK (10 msec)
Add Credentials to Wallet
PS E:\ORACLE19\orclwal> mkstore -wrl E:\ORACLE19\orclwal -createCredential
ORCLWAL c##test
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights
reserved.
Your secret/Password is missing in the command line
Enter your secret/Password:
Re-enter your secret/Password:
Enter wallet password:
PS E:\ORACLE19\orclwal> mkstore -wrl /u01/admin/orcl/wallet -listCredential
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights
reserved.
Enter wallet password:
\u01\admin\orcl\wallet (The system cannot find the path specified)
PS E:\ORACLE19\orclwal> mkstore -wrl E:\ORACLE19\orclwal -listCredential
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights
reserved.
Enter wallet password:
List credential (index: connect_string username)
1: ORCLWAL c##test
KRISHNA KUMAR VALUPADASU
KRISHNA KUMAR VALUPADASU
Validate TNS - to Connect with the User
PS E:\ORACLE19\orclwal> sqlplus c##test@ORCLWAL
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Dec 4 12:22:12 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Enter password:
Last Successful login time: Wed Dec 04 2024 11:49:54 -06:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Set Environment Variables
$env:ORACLE_HOME = "E:\ORACLE19\WINDOWS.X64_193000_db_home"
$env:TNS_ADMIN = "E:\ORACLE19\orclwal\TNS_ADMIN"
Try Connecting Using Wallet
PS E:\ORACLE19\orclwal\TNS_ADMIN> sqlplus
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 5 12:10:54 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Enter user-name: /@ORCLWAL
Last Successful login time: Thu Dec 05 2024 12:07:50 -06:00
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 "C##TEST"
Steps for Adding a New User to an Existing Wallet
SQL> select name,open_mode,log_mode from v$database;
NAME OPEN_MODE LOG_MODE
--------- -------------------- ------------
ORCLCDB READ WRITE ARCHIVELOG
SQL> select name,OPEN_MODE,OPEN_TIME,con_id from v$containers;
NAME OPEN_MODE OPEN_TIME
CON_ID
-------------------- ---------- -------------------------------------------
-------------------------------- ----------
CDB$ROOT READ WRITE 04-DEC-24 12.55.06.544 PM -06:00
1
KRISHNA KUMAR VALUPADASU
KRISHNA KUMAR VALUPADASU
PDB$SEED READ ONLY 04-DEC-24 12.55.06.574 PM -06:00
2
TESTPDB READ WRITE 04-DEC-24 12.55.07.608 PM -06:00
3
SQL> select banner from v$version;
BANNER
---------------------------------------------------------------------------
-----
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
SQL> create user c##key identified by key123 default tablespace users
temporary tablespace temp quota unlimited on users;
User created.
SQL> grant create session, connect to c##key;
Grant succeeded.
SQL> conn c##key;
Enter password:
Connected.
Add Credential to Wallet for New User
PS E:\ORACLE19\orclwal> mkstore -wrl E:\ORACLE19\orclwal -createCredential
ORCLWAL c##key key123
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights
reserved.
Enter wallet password:
Secret Store error occurred:
oracle.security.pki.OracleSecretStoreException: Credential already exists
Note: We cannot use same TNS entry name to add a user, need a add separate
entry with different name like below:
PS E:\ORACLE19\orclwal\TNS_ADMIN> pwd
Path
----
E:\ORACLE19\orclwal\TNS_ADMIN
PS E:\ORACLE19\orclwal\TNS_ADMIN> ls
Directory: E:\ORACLE19\orclwal\TNS_ADMIN
Mode LastWriteTime Length Name
---- ------------- ------ ----
-a---- 05-12-2024 10:37 590 sqlnet.ora
-a---- 04-12-2024 13:22 1454 tnsnames.ORA
KRISHNA KUMAR VALUPADASU
KRISHNA KUMAR VALUPADASU
Update - tnsnames.ORA with below entry.
ORCLWAL1=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = KRISHNA)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orclcdb.attlocal.net)
)
)
PS E:\ORACLE19\orclwal\TNS_ADMIN> notepad tnsnames.ORA - save the file
after edit
PS E:\ORACLE19\orclwal\TNS_ADMIN> cat tnsnames.ORA
# tnsnames.ora Network Configuration File:
E:\ORACLE19\WINDOWS.X64_193000_db_home\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_ORACLE =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)
ORCLWAL=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = KRISHNA)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orclcdb.attlocal.net)
)
)
ORCLWAL1=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = KRISHNA)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orclcdb.attlocal.net)
)
)
KRISHNA KUMAR VALUPADASU
KRISHNA KUMAR VALUPADASU
Verify ping for the newly added entry in tnanames.ora file:
PS E:\ORACLE19\orclwal> tnsping ORCLWAl1
TNS Ping Utility for 64-bit Windows: Version 19.0.0.0.0 - Production on 04-
DEC-2024 13:23:12
Copyright (c) 1997, 2019, Oracle. All rights reserved.
Used parameter files:
E:\ORACLE19\orclwal\TNS_ADMIN\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL =
TCP)(HOST = KRISHNA)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME =
orclcdb.attlocal.net)))
OK (20 msec)
Add new entry into wallet file:
PS E:\ORACLE19\orclwal> mkstore -wrl E:\ORACLE19\orclwal\ -createCredential
ORCLWAL1 c##key key123
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights
reserved.
Enter wallet password: >> Enter wallet password
PS E:\ORACLE19\orclwal>
List credentials:
------------------
PS E:\ORACLE19\orclwal> mkstore -wrl E:\ORACLE19\orclwal -listCredential
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights
reserved.
Enter wallet password:
List credential (index: connect_string username)
2: ORCLWAL1 c##key
1: ORCLWAL c##test
Now we can see both entries in the wallet file:
PS E:\ORACLE19\orclwal\TNS_ADMIN> sqlplus
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 5 12:29:38 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Enter user-name: /@ORCLWAL1
Last Successful login time: Thu Dec 05 2024 10:07:39 -06:00
Connected to:
KRISHNA KUMAR VALUPADASU
KRISHNA KUMAR VALUPADASU
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> show user;
USER is "C##KEY"
Updating Credentials in Wallet
If a password changes in the database, you must update the wallet credential as follows:
PS E:\ORACLE19\orclwal> mkstore -wrl E:\ORACLE19\orclwal -modifyCredential
ORCLWAL c##test krishna123
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights
reserved.
Enter wallet password:>> Enter wallet password.
PS E:\ORACLE19\orclwal\TNS_ADMIN> sqlplus
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 5 12:34:17 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Enter user-name: /@ORCLWAL
Last Successful login time: Thu Dec 05 2024 12:10:58 -06:00
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 "C##TEST"
Steps to Add a PDB User to the Wallet
Create the user in the PDB:
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 TESTPDB READ WRITE NO
SQL> alter session set container=TESTPDB;
Session altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 TESTPDB READ WRITE NO
KRISHNA KUMAR VALUPADASU
KRISHNA KUMAR VALUPADASU
SQL> create user PDBTEST identified by krishna123 default tablespace users
temporary tablespace temp quota unlimited on users;
User created.
SQL> grant create session,connect to pdbtest;
Grant succeeded.
ADD ENTRY IN TNSNAMES.ORA FILE FOR PDB USER:
--------------------------------------------
Version 19.3.0.0.0
PS E:\ORACLE19\orclwal\TNS_ADMIN> cat .\tnsnames.ORA
# tnsnames.ora Network Configuration File:
E:\ORACLE19\WINDOWS.X64_193000_db_home\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.
LISTENER_ORACLE =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
ORACLR_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(CONNECT_DATA =
(SID = CLRExtProc)
(PRESENTATION = RO)
)
)
ORCLWAL=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = KRISHNA)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orclcdb.attlocal.net)
)
)
ORCLWAL1=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = KRISHNA)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orclcdb.attlocal.net)
)
)
TESTPDBWAL=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = KRISHNA)(PORT = 1521))
)
(CONNECT_DATA =
KRISHNA KUMAR VALUPADASU
KRISHNA KUMAR VALUPADASU
(SERVICE_NAME = testpdb.attlocal.net)
)
)
Validate Entry using Tnsping:
PS E:\ORACLE19\orclwal\TNS_ADMIN> tnsping TESTPDBWAL
TNS Ping Utility for 64-bit Windows: Version 19.0.0.0.0 - Production on 05-
DEC-2024 12:41:32
Copyright (c) 1997, 2019, Oracle. All rights reserved.
Used parameter files:
E:\ORACLE19\orclwal\TNS_ADMIN\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL =
TCP)(HOST = KRISHNA)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME =
testpdb.attlocal.net)))
OK (0 msec)
PS E:\ORACLE19\orclwal\TNS_ADMIN> sqlplus PDBTEST@TESTPDBWAL
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 5 12:46:51 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Enter password:
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 "PDBTEST"
ADD ENTRY INTO WALLET:
PS E:\ORACLE19\orclwal\TNS_ADMIN> mkstore -wrl E:\ORACLE19\orclwal -
createCredential TESTPDBWAL pdbtest;
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights
reserved.
Your secret/Password is missing in the command line
Enter your secret/Password:
Re-enter your secret/Password:
Enter wallet password:
PS E:\ORACLE19\orclwal\TNS_ADMIN> mkstore -wrl E:\ORACLE19\orclwal -
listCredential
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Copyright (c) 2004, 2019, Oracle and/or its affiliates. All rights
reserved.
KRISHNA KUMAR VALUPADASU
KRISHNA KUMAR VALUPADASU
Enter wallet password:
List credential (index: connect_string username)
3: TESTPDBWAL pdbtest
2: ORCLWAL1 c##key
1: ORCLWAL c##test
PS E:\ORACLE19\orclwal\TNS_ADMIN> sqlplus
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 5 12:56:17 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Enter user-name: /@TESTPDBWAL
Last Successful login time: Thu Dec 05 2024 12:49:36 -06:00
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 "PDBTEST"
Happy Learning ☺
KRISHNA KUMAR VALUPADASU
