ORA-28001: the password has expired

Cause:

You try to connect to the database with the credentials where password is expired

SQL> conn test
Enter password:
ERROR:
ORA-28001: the password has expired

Workaround:

Check the old password hexadecimal encrypted value from sys.user$ view to reset the user with same password

SQL> col NAME format a20
SQL> col PASSWORD format a20
SQL> select name,PASSWORD from sys.user$ where name='TEST';

NAME                 PASSWORD
-------------------- --------------------
TEST                 483A017BEDCEEDA0

Check if the account is locked and password expiry date

SQL> col USERNAME format a20
SQL> col PASSWORD format a20
SQL> col ACCOUNT_STATUS format a20
SQL> col LOCK_DATE format a20
SQL> select USERNAME,PASSWORD,ACCOUNT_STATUS,LOCK_DATE,EXPIRY_DATE from dba_users where username='TEST';

USERNAME             PASSWORD             ACCOUNT_STATUS       LOCK_DATE            EXPIRY_DA
-------------------- -------------------- -------------------- -------------------- ---------
TEST                                      OPEN                                      16-DEC-21

Reset the password

SQL> alter user test identified by values '483A017BEDCEEDA0' account unlock;

User altered.

Check the connection

SQL> conn test
Enter password:
Connected.

RMAN-03002: failure of Duplicate Db command at 06/18/2021 18:48:14-RMAN-05501: aborting duplication of target database-RMAN-05603: not connected to target database with a net service name

Cause:

I was trying to connect to target database without specifying service name

[oracle@orcl ~]$ rman target sys/password auxiliary sys/password@orcldgp

Recovery Manager: Release 12.1.0.2.0 - Production on Fri Jun 18 18:42:04 2021

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

connected to target database: ORCL (DBID=1559282684)
connected to auxiliary database: ORCLDGP (not mounted)



RMAN> duplicate target database for standby from active database using backupset section size 300M nofilenamecheck;

Starting Duplicate Db at 18-JUN-21
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 06/18/2021 18:48:14
RMAN-05501: aborting duplication of target database
RMAN-05603: not connected to target database with a net service name

RMAN> exit

Workaround:

Specify the service name of target database to connect

[oracle@orcl ~]$ rman target sys/password@orcl auxiliary sys/password@orcldgp

Recovery Manager: Release 12.1.0.2.0 - Production on Fri Jun 18 18:48:49 2021

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

connected to target database: ORCL (DBID=1559282684)
connected to auxiliary database: ORCLDGP (not mounted)

RMAN-04006: error from auxiliary database: ORA-01017: invalid username/password; logon denied

Cause:

While try to connect to auxiliary instance for rman duplication , i cant connec standby database due to credential issue. As the standby database is new and there is no user ,we should use the password file of primary database on the standby

[oracle@orcl ~]$ rman target sys/password auxiliary sys/password@orcldgp

Recovery Manager: Release 12.1.0.2.0 - Production on Fri Jun 18 18:39:18 2021

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

connected to target database: ORCL (DBID=1559282684)
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04006: error from auxiliary database: ORA-01017: invalid username/password; logon denied

Copy the password file of primary to standby database $ORACLE_HOME/dbs location

[oracle@orcl ~]$ echo $ORACLE_HOME
/oracle/base/product/12.1.0/dbhome_1
[oracle@orcl ~]$ scp /oracle/base/product/12.1.0/dbhome_1/dbs/orapworcl oracle@192.168.56.123:/oracle/base/product/12.1.0/dbhome_1/dbs/
oracle@192.168.56.123's password:
orapworcl                                                   100% 7680     7.5KB/s   00:00

Modify the file name with standby database name

[oracle@orcldg dbs]$ mv orapworcl orapworcldgp
[oracle@orcldg dbs]$ pwd
/oracle/base/product/12.1.0/dbhome_1/dbs

Post that i can able to connect with sys user on auxiliary instance

[oracle@orcl ~]$ rman target sys/password auxiliary sys/password@orcldgp

Recovery Manager: Release 12.1.0.2.0 - Production on Fri Jun 18 18:42:04 2021

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

connected to target database: ORCL (DBID=1559282684)
connected to auxiliary database: ORCLDGP (not mounted)

RMAN>

CONFIGURE LISTENER FOR ORACLE DATABASE USING NETMGR

Locate to oracle home into network/admin directory

[oracle@orcldg bin]$ pwd
/oracle/base/product/12.1.0/dbhome_1/bin
[oracle@orcldg bin]$ ./netmgr

Open netmgr

Choose listener and click plus symbol at top left

Specify listener name

Choose listener locations and specify host and port

Choose database services from drop down on top center and specify the details of database

Global database name should be the database name

Oracle home directory should be current database home

SID should be oracle sid of current database for which the listener is configured

Click close button on top right corner and click on save

If the listener is not started on the database, then start the listener!

[oracle@orcldg bin]$ lsnrctl start listener1

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 19-JUN-2021 00:05:23

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Starting /oracle/base/product/12.1.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /oracle/base/product/12.1.0/dbhome_1/network/admin/listener.ora
Log messages written to /oracle/base/diag/tnslsnr/orcldg/listener1/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.212)(PORT=1526)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.212)(PORT=1526)))
STATUS of the LISTENER
------------------------
Alias                     listener1
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                19-JUN-2021 00:05:23
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/base/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File         /oracle/base/diag/tnslsnr/orcldg/listener1/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.56.212)(PORT=1526)))
Services Summary...
Service "orcldgp" has 1 instance(s).
  Instance "orcldgp", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

RMAN-00554: initialization of internal recovery manager package failed – RMAN-04006: error from auxiliary database: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

When you get this kind of error, first of all dont panic or rush to get a solution. Because tns errors and listener errors are simple errors where we mistakenly configure network files like tnsnames.ora and listener.ora .. Cool!

So follow a sequence to troubleshoot this kind of error

Things to check before drill down

Below is a right tns entry

#Primary tns entry
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.202)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

#Standby tns entry
ORCLDGP =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.123)(PORT = 1526))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcldgp)(UR=A)
    )
  )

Static listener entry should be used in case of dataguard. Whether the database is up or not ,the listener is registered with the instance. Check for below entry in your listener.ora file

SID_LIST_LISTENER =
   (SID_LIST =
     (SID_DESC =
       (GLOBAL_DBNAME = orcldgp)
       (ORACLE_HOME = /oracle/base/product/12.1.0/dbhome_1)
       (SID_NAME = orcldgp)
     )
  )

These silly mistakes we might have done. So hold your eyeball and observe these parameters keenly

Check if you give the correct port name on both source and destination when you connect auxiliary instance for dataguard configuration. By default the listener port number is 1521. But if there are multiple listeners on host, then we need to specify different ports. So check for proper ports

Check both tnsnames.ora files from primary and standby databases for standby network entry. It should contain (UR=A)

The above two mistakes can be rectified by doing a tnsping from primary to standby . In the below output, the port number and service name are wrong which points to different instance. Beware of these silly errors

[oracle@orcl ~]$ tnsping orcldgp

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 17-JUN-2021 18:42:47

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

Used parameter files:
/oracle/base/product/12.1.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.123)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcldg)(UR = A)))
OK (0 msec)

Check if your host has multiple listeners running and conflict the connections across different instances. Here there are two listeners running. One from grid home and another from rdbms home. Verify the configurations on both homes for tnsnames.ora and listener.ora files

[oracle@orcldg ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 18-JUN-2021 00:31:43

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                18-JUN-2021 00:02:40
Uptime                    0 days 0 hr. 29 min. 3 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/base/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File         /oracle/base/diag/tnslsnr/orcldg/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=orcldg)(PORT=1521)))
Services Summary...
Service "orcldgp" has 1 instance(s).
  Instance "orcldgp", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

=====================================================================================

[grid@orcldg ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 18-JUN-2021 00:32:20

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                18-JUN-2021 00:32:15
Uptime                    0 days 0 hr. 0 min. 4 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /grid/base/product/12.1.0/grid/network/admin/listener.ora
Listener Log File         /grid/base/diag/tnslsnr/orcldg/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
Services Summary...
Service "ORCLDG" has 1 instance(s).
  Instance "orcldg", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

Check if you have local listener configured in your instance

SQL> show parameter local_listener

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string       (ADDRESS=(PROTOCOL=TCP)(HOST=
                                                 192.168.56.123)(PORT=1521))

Check and make sure the listener status is “Unknown” and not “Blocked” statusa and check port number

[oracle@orcldg ~]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 18-JUN-2021 22:39:19

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     listener
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                18-JUN-2021 22:38:07
Uptime                    0 days 0 hr. 1 min. 13 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /oracle/base/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File         /oracle/base/diag/tnslsnr/orcldg/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=orcldg)(PORT=1521)))
Services Summary...
Service "orcldgp" has 2 instance(s).
  Instance "orcldgp", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

As there is a mismatch in port number , we get TNS-12541: TNS:no listener from primary database

[oracle@orcl ~]$ tnsping orcldgp

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 18-JUN-2021 17:21:53

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

Used parameter files:
/oracle/base/product/12.1.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.123)(PORT = 1526)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcldgp)(UR = A)))
TNS-12541: TNS:no listener

If i try connect to auxiliary , i get an error

[oracle@orcl ~]$ rman target sys/password auxiliary sys/password@orcldgp

Recovery Manager: Release 12.1.0.2.0 - Production on Fri Jun 18 17:19:01 2021

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

connected to target database: ORCL (DBID=1559282684)
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04006: error from auxiliary database: ORA-12541: TNS:no listener

So we set proper port for local listener parameter with 1526 as the listener is secondary

SQL> alter system set local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.56.123)(PORT=1526))';

System altered.

Post that reload the listener and check if listener use 1526 port

[oracle@orcldg ~]$ lsnrctl reload

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 18-JUN-2021 23:13:53

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
The command completed successfully

If you still see no change in listener port , then go to next step

[oracle@orcldg ~]$ cd /oracle/base/product/12.1.0/dbhome_1/network/admin/
[oracle@orcldg admin]$ ls
listener.ora  samples  shrept.lst  tnsnames2106092PM4606.bak  tnsnames.ora

Perform an observation by moving the existing “tnsnames.ora” file to a backup file

[oracle@orcldg admin]$ mv tnsnames.ora tnsnames.ora.bkp

Still listener is not using specified tnsnames file

[oracle@orcldg admin]$ lsnrctl status

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 18-JUN-2021 23:34:25

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=orcldg)(PORT=1521)))
Services Summary...
Service "orcldgp" has 1 instance(s).
  Instance "orcldgp", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

If none of them work, then you would have to recreate the listener from scratch. Make sure that you dedicate the connections to a new listener and recreate the old listener

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Cause:

During remote connection of service from source database to destination , there was tns error which really annoy. This error mainly trigger due to either wrong tns entry or no entry at all in tnsnames.ora.

[oracle@orcl19x admin]$ rman target test/password@exdbx1

Recovery Manager: Release 11.2.0.4.0 - Production on Sun Feb 14 23:05:05 2021

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

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Solution:

Check the tnsnamea.ora tns entry file and add the connection details string .Check for small spaces or indentation of the braces which also cause silly errors which lead us to frustation

[oracle@orcl19x admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /apps01/base/product/11.2.0/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORCL11X =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = orcl11x)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = orcl11x)
    )
  )
exdbx1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = exdbadm01)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = exdbx)
    )
  )

After the tns entry addition , connection was successful!

[oracle@orcl19x admin]$ rman target test/password@exdbx1

Recovery Manager: Release 11.2.0.4.0 - Production on Sun Feb 14 23:10:37 2021

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

connected to target database: EXDBX (DBID=572524948)

RMAN>

ORA-00119: invalid specification for system parameter LOCAL_LISTENER-ORA-00132: syntax error or unresolved network name ‘LISTENER_ORCLDGP’

I try to startup the instance but does not come up due to local listener parameter set improperly in spfile

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup;
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENER_ORCLDGP'

Check the local listener name in pfile and modify it manually to blank

[oracle@orcldg ~]$ cat /oracle/base/product/12.1.0/dbhome_1/dbs/initorcldgp.ora|grep 'local_listener'
*.local_listener='LISTENER_ORCLDGP'

After making changes , the pfile local listener parameter looks like this

[oracle@orcldg ~]$ cat /oracle/base/product/12.1.0/dbhome_1/dbs/initorcldgp.ora|grep 'local_listener'
*.local_listener=''

Now startup the instance using pfile

SQL> startup nomount pfile='/oracle/base/product/12.1.0/dbhome_1/dbs/initorcldgp.ora';
ORACLE instance started.

Total System Global Area 1509949440 bytes
Fixed Size                  2924640 bytes
Variable Size             956305312 bytes
Database Buffers          536870912 bytes
Redo Buffers               13848576 bytes

Check the local listener

SQL> show parameter local_listener

Create spfile from pfile

SQL> create spfile from pfile;

File created.

RMAN-00554: initialization of internal recovery manager package failed-RMAN-12001: could not open channel default-RMAN-10008: could not create channel context-RMAN-10002: ORACLE error: ORA-15021: parameter “remote_dependencies_mode” is not valid in asm instance-RMAN-10006: error running SQL statement: alter session set remote_dependencies_mode = signature

Cause:

You currently set GRID env and launch RMAN

[oracle@exdbadm01 ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jun 17 00:18:22 2021

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

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-12001: could not open channel default
RMAN-10008: could not create channel context
RMAN-10002: ORACLE error: ORA-15021: parameter "remote_dependencies_mode" is not valid in asm instance
RMAN-10006: error running SQL statement: alter session set remote_dependencies_mode = signature

Workaround:

Set RDBMS env on SID

[oracle@exdbadm01 ~]$ . oraenv
ORACLE_SID = [+ASM1] ? exdbx
The Oracle base has been changed from /u01/app/oracle to /data/base

[oracle@exdbadm01 ~]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Jun 17 00:20:52 2021

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

connected to target database (not started)

RMAN>

DIFFERENCE BETWEEN INSTANCE AND CRASH AND MEDIA RECOVERY ORACLE

Crash recovery –

  • If a single instance or all RAC database instance crash due to failure , then the database needs to be recovered by smon.
  • There is no manual intervantion required by any DBA for crash recovery of database.
  • There is no need of archivelog apply by DBA

Instance recovery –

  • If any one node instance of a RAC node fail due to some issue, then the surviving node should involve in recovering the failed instance using smon
  • There is no need of archivelog apply by DBA

Media recovery –

  • Here DBA intervention is required for manual restore of datafiles and recovery of database using rman