ORA-01979 Missing or Invalid Password Error for a Role

Missing or Invalid Password Error for a Role:

1. Problem: The user gets an ORA-01979 error when he tries to enable the role assigned to him by using the set role command. The role is protected by a password. The Error encountered by the user is given below:

Logged in as a SYS or a DBA user and create a role and then grant that role to the user:

SQL> CREATE role usrrole1 identified by abcd1;


Role created.


SQL> GRANT usrrole1 to newusr10;


Grant succeeded.

SQL> 

Now when the user tries to set the role without the password then he encounters the following error:

SQL> SET ROLE usrrole1;
SET ROLE usrrole1
*
ERROR at line 1:
ORA-01979: missing or invalid password for role 'USRROLE1'

SQL>  


2. Impact:  The user cannot access and use the roles assigned and granted to him which is affecting his performance and production.

3. Solution: When a user is granted a role which is password protected and he is enabling the role using the set role command then he must always provide the password which has already been set and specified. This is mandatory and if the user does not specify the password while enabling the role then he would encounter a ORA-01979 Missing or Invalid Password for a role Error.

First to be sure that a particular role is password protected, the user can run the following select. Dba_roles is a Data dictionary view.

SQL> SELECT * from dba_roles where role='USRROLE1';


ROLE                           PASSWORD
------------------------------ --------
USRROLE1                       YES

SQL>  

The role is password protected. Now Login as the user newusr10 and execute the command along with the password to enable the role.

 

SQL> SET role usrrole1 identified by abcd1;


Role set.

SQL>  

Run a select as below to verify that the role has been enabled by the command above. Session_roles is a Data dictionary view.


SQL> SELECT * FROM session_roles;


ROLE
------------------------------
USRROLE1

Oracle Database Connection Error

Cannot Connect to the Oracle Database:

1.Problem: The user cannot connect to the Oracle database, with either the OEM (Oracle Enterprise Manager) or SQL*PLUS. The user's database is in a hung state and he does not have any messages in the alert log.


2.Impact: Very high as the user's database is in a hung state and he cannot perform any functions with the Oracle Database.


3.Solution: The Oracle database can hang for a lot of reasons. Below are the solutions to the problem.

A. The user must check the alert logs and the log files of the listener.

B. Check the server side logs like /adm/syslog and /etc/syslog.

C. The lsnrctl utility can be used to check if the listener is running. If the listener is not up and running then the server cannot detect incoming connections.

D. The user can check the instance by running - ps -ef|grep ora|grep pmon if the database instance is unavailable due to a crash. This would check the process monitor because the user cannot see the alert log in this scenario.

E. In Oracle 11g, a new utility has been introduced in SQL*PLUS called - prelim. This can be used to run oradebug command and utilities which function without actually connecting to the database.

F. After running the oradebug command, the user can use the utility called hanganalyze to diagnose the problem in the hung database.

G. The user can also kill or bounce the instance.

H. The user should ensure that the Oracle environment variables are properly set by looking at the Oracle_SID and the PATH in $ORACLE_HOME.

I. It is possible that due to insufficient Random Access Memory(RAM), the users cannot perform another connection to Oracle. 

J. The users might be trying to connect to the Oracle Database using Background and server processes which are being used by another user.

K. Another possibility is that Oracle cannot find tnsnames.ora file or the tnsnames.ora file does not point to a valid database.

L. So, ensure that the user's path contains the correct path to the tnsnames.ora file and that the file points to the user's database on the specific host machine.

 

Oracle/PLSQL: Dynamic SQL Errors.ORA-06550 and PLS-00221

Oracle/PLSQL: ORA-06550 error and PLS-00221:is not a procedure or is undefined

1. Problem: The user gets a ORA-06550 error along with PLS-00221while trying to insert a row into a table called Employee using Dynamic SQL. He runs the following PL/SQL code in SQL Developer:
 Executing the Function: (INSERT_RECORD)

SET SERVEROUTPUT ON

DECLARE
BEGIN
EMPLOYEE_OPERATIONS.INSERT_RECORD('EMPLOYEE','ABC','DEF');
END;

 Package Specification: 

 create or replace

PACKAGE EMPLOYEE_OPERATIONS
IS FUNCTION INSERT_RECORD(
    P_TABLE_NAME VARCHAR2,
    P_FIRST_NAME EMPLOYE.FIRST_NAME%TYPE,
    P_LAST_NAME EMPLOYE.LAST_NAME%TYPE)
  RETURN NUMBER;
  END EMPLOYEE_OPERATIONS;

Package Body:

create or replace
PACKAGE BODY EMPLOYEE_OPERATIONS
IS FUNCTION INSERT_RECORD(
    P_TABLE_NAME VARCHAR2,

    P_FIRST_NAME EMPLOYE.FIRST_NAME%TYPE,
    P_LAST_NAME EMPLOYE.LAST_NAME%TYPE)
  RETURN NUMBER IS
BEGIN
  EXECUTE IMMEDIATE ' INSERT INTO ' || P_TABLE_NAME || ' VALUES (:1,:2) ' USING P_FIRST_NAME,P_LAST_NAME;
  RETURN SQL%ROWCOUNT;
END INSERT_RECORD;
END EMPLOYEE_OPERATIONS;

  User gets the following Error:

 Error starting at line 5 in command:
DECLARE
BEGIN
EMPLOYEE_OPERATIONS.INSERT_RECORD('EMPLOYEE','ABC','DEF');
END;
Error report:
ORA-06550: line 3, column 1:
PLS-00221: 'INSERT_RECORD' is not a procedure or is undefined
ORA-06550: line 3, column 1:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

 2. Impact: The impact is high as user cannot insert values in the table using Dynamic SQL.
3. Solution: The user gets the error because he is executing a function and not a procedure and hence has to declare a variable in the anonymous block while executing the function. When the user executes the function in the above manner then the error message says "is not a procedure or is undefined" because the compiler tells the user that he is trying to execute a function as a procedure. This is because a function is returning a value or the result and the value/result that is being returned has to be stored in a variable before it can be displayed. After the value is stored in the variable the user can use DBMS_OUTPUT.PUT_LINE to display the same. The execution code should be written like mentioned below:

SET SERVEROUTPUT ON
DECLARE
VINS_RESULT NUMBER;
BEGIN
VINS_RESULT := EMPLOYEE_OPERATIONS.INSERT_RECORD('EMPLOYEE','ABC','DEF');
DBMS_OUTPUT.PUT_LINE(VINS_RESULT);
END;

anonymous block completed
1 row inserted.

Snapshot Too Old: ORA-01555 Error.

ORA-01555 Error:

1. Problem: 

Below are the settings for the undo tablespace:

undo_retention - 1200

undo_management  - AUTO 
The user encounters the following error in the job that is running in the database:
Ora-01555 snapshot too old error.

2. Impact: Medium to high because it would effect the long running queries due to insufficient undo tablespace thus impacting performance. Also could be a part of a batch process.

3. Solutions: The Ora-01555 snapshot too old error occurs when the undo tablepspace storage space is smaller as compared to the space needed by long running queries. It could also occur because of inappropriate (too small) value of the undo_retention. Undo_retention specifies the time period (in seconds) till which the system retains undo ie. undo would be retined for at least the time specified in this parameter. The undo_retention parameter would be efficent if the current undo tablespace has enough space. If there is an active transaction which  requires undo space and there is not enough available space, then the system reuses unexpired undo space. This causes some queries to fail with a snapshot too old error message. The underlying technology that undo supports is the Oracle read consistency mechanism. 

Below are the remedies to address and remedy this error:

1. Reduce and delay extent reuse by increasing the size of the undo tablespace and the undo_retention parameter .

2. Try not to do a fetch between the commits. So, if a cursor was opened before the last commit  don't fetch the cursor as it is still performing actions in the current sessions.

3. Don't perform frequent commits as this would reduce the size of the undo tablespace and also the queries would take more time.

4. Try to perform the long-running queries when the system has the least load of DMLtransactions.

5. Set a large value for the  database block size(db_block_size) parameter to reduce and delay extent reuse. 

6. Run separate transactions while the sensitive long-running queries are taking place only when it is very important and the transactions are not dependent on each other and do not prejudice each others performance.

7.  Before you run long-running and sensitive sql queries make sure that you have sufficient and optimal undo tablespace. If you do not have sufficient undo tablespace manually resize it to prevent rollback failure thus preventing the error.

8. You can also calculate the size of the optimal undo_retention, undo tablespace and the db_block_size before hand.

9. You can manually manage the usage, size and the amount of the rollback segments.

Rman Problem and Solutions

Oracle DB: Connecting to Rcat (Recovery Catalog) using Rman

1. Problem: The Listener has been started and the target (orcl) database has been mounted and started . However when an attempt is made to connect to the Rcat database, as a user: rcatowner, using Rman at the linux prompt then there are problems doing so and a user gets the following error:

[   ~]$ rman target / catalog rcatowner@rcat

connected to target database: ORCL (DBID=**********)

recovery catalog database Password:

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-00554: initialization of internal recovery manager package failed

RMAN-04004: error from recovery catalog database: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

2. Impact: High  as the user cannot connect to the rcat database and hence cannot use the Rman (Recovery Manger) services.

3. Solution: Although the listener has been started and the target (orcl) database has been started and mounted the user cannot connect to rcat because the rcat database instance has not yet been started by the user. You can know this by the following line in the above error: "connected to target database: ORCL (DBID=**********)". Here it clearly indicates that the user is connected to the orcl database instance and not the rcat instance. Rcat database instance needs to be started because Rcat is a database like the target database.

Additional Info: Rcat is a database and comprises of Oracle tables and views which the RMAN (Recovery Manager) uses to store metadata and its (RMAN) repository information of one or more Oracle databases. RMAN utilises the metadata to manage the recovery, backup and restoring of the Oracle databases. Below are the steps that need to be followed:

Step #1:

[  ~]$ . oraenv

ORACLE_SID = [orcl] ? rcat

Step #2:

[   ~]$ sqlplus / as sysdba

  

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Database mounted.

Database opened.

Step #3:

[  ~]$ rman target / catalog rcatowner@rcat

connected to target database: RCAT (DBID=*********)

recovery catalog database Password:

connected to recovery catalog database

Here : connected to target database: RCAT (DBID=*********) : clearly indicates that the user is connected to the Rcat database instance now.

Step #4:

 

[oracle@localhost ~]$ . oraenv

ORACLE_SID = [rcat] ? orcl

The ORACLE_SID needs to be changed back to orcl ie. the target database because if this is not done then the user would not be able to access the target database (orcl) as the system would only be recognizing the rcat database instance at this stage.