A password file can be recreated in case you have forgotten password for SYS user. Normally a password file contains password for all SYSDBA users who can start/stop the database.
Scenario:
I have a 19c database installed on my machine with 2 CDBs having their own PDBs. I am trying to login to this database after a long time and hence have lost my credentials for SYS/SYSTEM schemas. The services are starting well but I am not able to login to database so let's fix this situation.
1) Listener is running and is able to get instance details.
C:\Users\parvi>lsnrctl status
LSNRCTL for 64-bit Windows: Version 19.0.0.0.0 - Production on 05-JAN-2025 16:21:25
Copyright (c) 1991, 2019, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=DemonoftheFall)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for 64-bit Windows: Version 19.0.0.0.0 - Production
Start Date 05-JAN-2025 16:13:39
Uptime 0 days 0 hr. 7 min. 45 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File C:\app\oracle\product\19.3.0\dbhome_1\network\admin\listener.ora
Listener Log File C:\app\oracle\product\19.3.0\dbhome_1\log\diag\tnslsnr\DemonoftheFall\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DemonoftheFall)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
Services Summary...
Service "52448234712340b69f274bcc790ecfe0" has 2 instance(s).
Instance "cdb1", status READY, has 1 handler(s) for this service...
Instance "cdb2", status READY, has 1 handler(s) for this service...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "ae1f84abc88b4b38bd269f363503842c" has 1 instance(s).
Instance "cdb2", status READY, has 1 handler(s) for this service...
Service "c2abde6ea52e4a6ca8ab4bb40ee3df40" has 1 instance(s).
Instance "cdb1", status READY, has 1 handler(s) for this service...
Service "cdb1" has 2 instance(s).
Instance "cdb1", status UNKNOWN, has 1 handler(s) for this service...
Instance "cdb1", status READY, has 1 handler(s) for this service...
Service "cdb1XDB" has 1 instance(s).
Instance "cdb1", status READY, has 1 handler(s) for this service...
Service "cdb2" has 2 instance(s).
Instance "cdb2", status UNKNOWN, has 1 handler(s) for this service...
Instance "cdb2", status READY, has 1 handler(s) for this service...
Service "cdb2XDB" has 1 instance(s).
Instance "cdb2", status READY, has 1 handler(s) for this service...
Service "pdb1" has 2 instance(s).
Instance "cdb1", status READY, has 1 handler(s) for this service...
Instance "cdb2", status READY, has 1 handler(s) for this service...
The command completed successfully
2) OS Authentication is not happening.
C:\Users\parvi> set ORACLE_SID=cdb1 C:\Users\parvi> sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 5 16:27:19 2025 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. ERROR: ORA-01017: invalid username/password; logon denied Enter user-name:
3) Checking if the password file is existing or not.
C:\Users\parvi>cd C:\app\oracle\product\19.3.0\dbhome_1\database C:\app\oracle\product\19.3.0\dbhome_1\database>dir Volume in drive C is OS Volume Serial Number is 1A79-4B0E Directory of C:\app\oracle\product\19.3.0\dbhome_1\database 10-06-2023 19:41 <DIR> . 05-01-2025 16:14 <DIR> .. 30-05-2019 08:54 <DIR> archive 04-12-2022 13:31 2,048 hc_cdb1.dat 04-12-2022 13:55 2,048 hc_cdb2.dat 04-12-2022 13:10 31,744 oradba.exe 05-01-2025 16:14 1,156 oradim.log 04-12-2022 14:00 2,048 PWDcdb2.ora 05-01-2025 16:14 3,584 SPFILECDB1.ORA 05-01-2025 16:14 3,584 SPFILECDB2.ORA 7 File(s) 46,212 bytes 3 Dir(s) 91,836,616,704 bytes free
4) As you can see CDB2's password file is there. Let's create one for CDB1 as well.
Note: Unlike previous Oracle Database versions, it doesn't accept easy passwords. To prove this, I have used different variations and you'll see that every time it will keep on guiding till your password matches the complexity standards.
C:\app\oracle\product\19.3.0\dbhome_1\database>orapwd file=PWDcdb1.ora password=sys entries=10 force=y OPW-00029: Password complexity failed for SYS user : Password must contain at least 8 characters. C:\app\oracle\product\19.3.0\dbhome_1\database>orapwd file=PWDcdb1.ora password=SysPassword entries=10 force=y OPW-00029: Password complexity failed for SYS user : Password must contain at least 1 digit. C:\app\oracle\product\19.3.0\dbhome_1\database>orapwd file=PWDcdb1.ora password=SysPsswd1 entries=10 force=y OPW-00029: Password complexity failed for SYS user : Password must contain at least 1 special character. C:\app\oracle\product\19.3.0\dbhome_1\database>orapwd file=PWDcdb1.ora password=SysP$$wd1 entries=10 force=y OPW-00029: Password complexity failed for SYS user : Password must not contain the username. C:\app\oracle\product\19.3.0\dbhome_1\database>orapwd file=PWDcdb1.ora password=CdbP$$wd1 entries=10 force=y C:\app\oracle\product\19.3.0\dbhome_1\database>
5) Password file is created now.
C:\app\oracle\product\19.3.0\dbhome_1\database>dir Volume in drive C is OS Volume Serial Number is 1A79-4B0E Directory of C:\app\oracle\product\19.3.0\dbhome_1\database 05-01-2025 16:35 <DIR> . 05-01-2025 16:14 <DIR> .. 30-05-2019 08:54 <DIR> archive 04-12-2022 13:31 2,048 hc_cdb1.dat 04-12-2022 13:55 2,048 hc_cdb2.dat 04-12-2022 13:10 31,744 oradba.exe 05-01-2025 16:14 1,156 oradim.log 05-01-2025 16:35 6,144 PWDcdb1.ora 04-12-2022 14:00 2,048 PWDcdb2.ora 05-01-2025 16:14 3,584 SPFILECDB1.ORA 05-01-2025 16:14 3,584 SPFILECDB2.ORA 8 File(s) 52,356 bytes 3 Dir(s) 91,823,480,832 bytes free
6) Let's test the password file working.
C:\app\oracle\product\19.3.0\dbhome_1\database>sqlplus sys/CdbP$$wd1 as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 5 16:40:50 2025 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>
7) Similarly I'll create password file again for my CDB2 instance since I don't know the password for that as well. Deleted the existing PWD file and,
C:\app\oracle\product\19.3.0\dbhome_1\database>orapwd file=pwdcdb2.ora password=CdbP$$wd2 entries=10 force=y C:\app\oracle\product\19.3.0\dbhome_1\database>set ORACLE_SID=cdb2 C:\app\oracle\product\19.3.0\dbhome_1\database>sqlplus sys/CdbP$$wd2 as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 5 16:44:29 2025 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>
8) As you can see, both instances are accessible now with newly created password files. You can now run further queries to check and validate rest of the structure.
SQL> select name, open_mode from V$database;
NAME OPEN_MODE
--------- --------------------
CDB2 READ WRITE
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
SQL> alter pluggable database PDB1 open;
Pluggable database altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
SQL>
No comments:
Post a Comment