ORA-02097: parameter cannot be modified because specified value is invalid

I got ” ORA-02097: parameter cannot be modified because specified value is invalid ”  error in Oracle database.

 

ORA-02097: parameter cannot be modified because specified value is invalid

 

Details of error are as follows.

ORA-02097: parameter cannot be modified because specified value is invalid.

Cause: Though the initialization parameter is modifiable, the modified value is not acceptable to the parameter.

Action: Check the DBA guide for range of acceptable values for this parameter.

You can get the below error while setting the initialization parameter with non-SYS/DBA  user

SQL> alter session set db_create_file_dest='+DG_DATA_01_CDB1DEV';
ERROR:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-01031: insufficient privileges

 Even user this have the "alert session" privileges

 

 

 

parameter cannot be modified because specified value is invalid

This ORA-02097 error is related with the initialization parameter is modifiable, the modified value is not acceptable to the parameter.

 

Alter session is insufficient on non-sys/dba users use to alter few initialization parameter  example : db_create_file_dest

 

–  Grant “alter database” system privilege  to  non-sys/dba users.

++++ TEST CASE +++++++

SQL> conn / as sysdbA
Connected.
SQL> create <user> identified by <password>;

User created.

SQL> grant create session to <user> ;

Grant succeeded.

SQL> grant alter database to <user> ;

Grant succeeded.

SQL> conn <user>/<password>
Connected.

SQL> alter session set db_create_file_dest=’/u01/db/db12/12.2.0/dbs’;

Session altered.

SQL> select * from session_privs;

PRIVILEGE
—————————————-
ALTER DATABASE
CREATE SESSION

 

 

OR

 

When you set any parameter incorrectly, you will get this error as follows.

ALTER SESSION SET "sec_protocol_error_further_action" = "delay, 10"
ORA-02097: parameter cannot be modified because specified value is invalid

  • The value of the parameter set at instance level shows something like the following. Note in all these cases the syntax  of the value is not right.
  
SQL> show parameter sec_protocol

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_protocol_error_further_action    string      delay, 10, drop, 10

or


SQL> show parameter sec_protocol

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sec_protocol_error_further_action    string      delay, 10

The syntax of the value of the parameter sec_protocol_error_further_action does not look right at instance level already.

 

How the invalid value got into there at instance level is not fully explored yet. There are cases where it was set from init.ora.

 

Nevertheless the value of the parameter should be with brackets around them, e.g. as can be seen below:

 

   SQL> show parameter sec_protocol_error_further_action

    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- -------------
    sec_protocol_error_further_action    string      (delay, 10)

 

 

 

1./ Check the value of the paramter at instance level

   SQL>conn sys as sysdba

   SQL> show parameter sec_protocol_error_further_action

 

2./ Make sure the value set is valid.

 

To solve this error, set this parameter as follows.

   SQL> alter system set  "sec_protocol_error_further_action" = "(delay, 10)" scope=both;

 

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )

 

 157 views last month,  4 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply