ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

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

Issue

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

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance is coming after recreating SPFILE.

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

How to recreate

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

SQL> CREATE PFILE='/software/oracle/admin/igt/pfile/pfileigt.ora_20210806' FROM SPFILE;
File created.
SQL> CREATE SPFILE='/software/oracle/admin/igt/pfile/sfileigt.ora_20210806' FROM MEMORY;
File created.
--------------

sho parameter spfile

/software/oracle/admin/igt/pfile/spfileigt.ora

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

To find out which parameters are deprecated, open the alert.log

  
WARNING: The user_dump_dest init.ora parameter has been deprecated.
WARNING: Please remove the user_dump_dest parameter from the init.ora file.
WARNING: The background_dump_dest init.ora parameter has been deprecated.
WARNING: Please remove the background_dump_dest parameter from the init.ora file.

Deprecated system parameters are:
  background_dump_dest
  user_dump_dest

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

How to Fix

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

CREATE PFILE='/software/oracle/admin/igt/pfile/pfile_fix_20210806.ora' from SPFILE;

!vi /software/oracle/admin/igt/pfile/pfile_fix_20210806.ora
Remove background_dump_dest + user_dump_dest from /software/oracle/admin/igt/pfile/pfile_fix_20210806.ora

SQL> SHUTDOWN IMMEDIATE;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup pfile = '/software/oracle/admin/igt/pfile/pfile_fix_20210806.ora';

ORACLE instance started.

Total System Global Area 4175568896 bytes

Fixed Size                  2259840 bytes

Variable Size            2214593664 bytes

Database Buffers         1946157056 bytes

Redo Buffers               12558336 bytes

Database mounted.

Database opened.

SQL> CREATE SPFILE FROM  PFILE='/software/oracle/admin/igt/pfile/pfile_fix_20210806.ora';
File created.

SQL> SHUTDOWN IMMEDIATE;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> STARTUP;

ORACLE instance started.

Total System Global Area 4175568896 bytes

Fixed Size                  2259840 bytes

Variable Size            2214593664 bytes

Database Buffers         1946157056 bytes

Redo Buffers               12558336 bytes

Database mounted.

Database opened.

SQL>

Create an AFTER LOGON trigger to log sessions

Create an AFTER LOGON trigger to log sessions

CREATE TABLE LOGON_SESSIONS_HIST (

  schema_name VARCHAR2(30),

  os_user VARCHAR2(30),

  machine VARCHAR2(100),

  program VARCHAR2(100),

  client_ip VARCHAR2(15),

  logon_time DATE) 

TABLESPACE IGT_TABLE;

CREATE OR REPLACE TRIGGER LOGON_LOGGER_TRG

AFTER LOGON ON VRS_GROUP_SHARE.SCHEMA

BEGIN

  INSERT INTO LOGON_SESSIONS_HIST(schema_name, os_user, machine, program, client_ip, logon_time)

  SELECT sys_context ('USERENV', 'SESSION_USER') as schema_name, 

         sys_context ('USERENV', 'OS_USER') as os_user,

         sys_context ('USERENV', 'HOST') as machine, 

         sys_context ('USERENV', 'MODULE') as program,

         sys_context ('USERENV', 'IP_ADDRESS') as client_id,

         SYSDATE  as login_time   

         FROM DUAL; 

  COMMIT;

EXCEPTION

  WHEN OTHERS THEN

    NULL;

END;

/

SELECT * FROM LOGON_SESSIONS_HIST ;

 

SCHEMA_NAME     OS_USER  MACHINE      PROGRAM        CLIENT_IP      LOGON_TIME

--------------- -------- ------------ -------------- -------------- -----------------

VRS_GROUP_SHARE oracle   qadev-aps-01                               20210804 12:45:24

VRS_GROUP_SHARE os_user  DOMAIN\USER  plsqldev.exe   10.135.251.200 20210804 12:45:32

VRS_GROUP_SHARE oracle   qadev-aps-01                               20210804 12:46:29

VRS_GROUP_SHARE oracle   qadev-aps-01                               20210804 12:47:04

VRS_GROUP_SHARE oracle   qadev-aps-01                               20210804 12:48:09

VRS_GROUP_SHARE os_user  DOMAIN\USER  plsqldev.exe   10.135.251.200 20210804 12:48:28

VRS_GROUP_SHARE oracle   qadev-aps-01                               20210804 12:49:14

VRS_GROUP_SHARE os_user  DOMAIN\USER  plsqldev.exe   10.135.251.200 20210804 12:45:32

ALTER TRIGGER LOGON_LOGGER_TRG DISABLE;