Wednesday, August 24, 2022

Invalid objects sys.dv$configuration_audit and sys.dv$enforcement_audit - After 19c upgrade

Hello ,

After the 19c DB upgrade we found 2 SYS objects showing invalid. The good thing about these objects is that we have Oracle DOC ID for the same. Below action plan that has been performed:

Execute the below steps:
SQL> select owner,object_name from dba_objects where status='INVALID';

OWNER
--------------------------------------------------------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
SYS
DV$CONFIGURATION_AUDIT
SYS
DV$ENFORCEMENT_AUDIT

SQL> drop view sys.dv$configuration_audit;
View dropped.
SQL> drop view sys.dv$enforcement_audit;
View dropped.
SQL> CREATE OR REPLACE FORCE NONEDITIONABLE VIEW "AUDSYS"."DV$CONFIGURATION_AUDIT" ("OS_USER", "USERID", "HOST_NAME", "TERMINAL", "EVENT_TIMESTAMP", "OBJ_OWNER", "OBJ_NAME", "DV_ACTION_CODE", "DV_ACTION_NAME", "DV_ACTION_OBJECT_NAME", "SQL_TEXT", "DV_RULE_SET_NAME", "DV_FACTOR_CONTEXT", "DV_COMMENT", "SESSIONID", "ENTRY_ID", "STATEMENT_ID", "DV_RETURN_CODE", "PROXY_USERID", "GLOBAL_USERID", "INSTANCE_ID", "OS_PROCESS", "DV_GRANTEE", "DV_OBJECT_STATUS") AS
  SELECT
  OS_USERNAME
  , DBUSERNAME
  , USERHOST
  , TERMINAL
  , EVENT_TIMESTAMP
  , OBJECT_SCHEMA
  , OBJECT_NAME
  , DV_ACTION_CODE
  , DV_ACTION_NAME
  , DV_ACTION_OBJECT_NAME
  , SQL_TEXT
  , DV_RULE_SET_NAME
  , DV_FACTOR_CONTEXT
  , DV_COMMENT
  , SESSIONID
  , ENTRY_ID
  , STATEMENT_ID
  , DV_RETURN_CODE
  , DBPROXY_USERNAME
  , GLOBAL_USERID
  , INSTANCE_ID
  , OS_PROCESS
  , DV_GRANTEE
  , DV_OBJECT_STATUS
FROM audsys.unified_audit_trail where audit_type = 'Database Vault' and DV_ACTION_CODE > 20000;  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27
View created.
SQL> CREATE OR REPLACE FORCE NONEDITIONABLE VIEW "AUDSYS"."DV$ENFORCEMENT_AUDIT" ("OS_USER", "USERID", "HOST_NAME", "TERMINAL", "EVENT_TIMESTAMP", "OBJ_OWNER", "OBJ_NAME", "DV_ACTION_CODE", "DV_ACTION_NAME", "DV_ACTION_OBJECT_NAME", "SQL_TEXT", "DV_RULE_SET_NAME", "DV_FACTOR_CONTEXT", "DV_COMMENT", "SESSIONID", "ENTRY_ID", "STATEMENT_ID", "DV_RETURN_CODE", "PROXY_USERID", "GLOBAL_USERID", "INSTANCE_ID", "OS_PROCESS", "DV_GRANTEE", "DV_OBJECT_STATUS") AS
  SELECT
  OS_USERNAME
  , DBUSERNAME
  , USERHOST
  , TERMINAL
  , EVENT_TIMESTAMP
  , OBJECT_SCHEMA
  , OBJECT_NAME
  , DV_ACTION_CODE
  , DV_ACTION_NAME
  , DV_ACTION_OBJECT_NAME
  , SQL_TEXT
  , DV_RULE_SET_NAME
  , DV_FACTOR_CONTEXT
  , DV_COMMENT
  , SESSIONID
  , ENTRY_ID
  , STATEMENT_ID
  , DV_RETURN_CODE
  , DBPROXY_USERNAME
  , GLOBAL_USERID
  , INSTANCE_ID
  , OS_PROCESS
  , DV_GRANTEE
  , DV_OBJECT_STATUS
FROM audsys.unified_audit_trail where audit_type = 'Database Vault' and DV_ACTION_CODE < 20000;  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27
View created.
SQL> GRANT READ ON AUDSYS.DV$CONFIGURATION_AUDIT TO AUDIT_ADMIN;
GRANT READ ON AUDSYS.DV$CONFIGURATION_AUDIT TO AUDIT_VIEWER;
GRANT READ ON AUDSYS.DV$CONFIGURATION_AUDIT TO DV_MONITOR;
GRANT READ ON AUDSYS.DV$CONFIGURATION_AUDIT TO DV_SECANALYST;
GRANT READ ON AUDSYS.DV$ENFORCEMENT_AUDIT TO AUDIT_ADMIN;
GRANT READ ON AUDSYS.DV$ENFORCEMENT_AUDIT TO AUDIT_VIEWER;
GRANT READ ON AUDSYS.DV$ENFORCEMENT_AUDIT TO DV_MONITOR;
GRANT READ ON AUDSYS.DV$ENFORCEMENT_AUDIT TO DV_SECANALYST;
Grant succeeded.
SQL>
Grant succeeded.
SQL> GRANT READ ON AUDSYS.DV$CONFIGURATION_AUDIT TO DV_MONITOR
                                               *
ERROR at line 1:
ORA-01917: user or role 'DV_MONITOR' does not exist
SQL> GRANT READ ON AUDSYS.DV$CONFIGURATION_AUDIT TO DV_SECANALYST
                                               *
ERROR at line 1:
ORA-01917: user or role 'DV_SECANALYST' does not exist
SQL>
Grant succeeded.
SQL>
Grant succeeded.
SQL> GRANT READ ON AUDSYS.DV$ENFORCEMENT_AUDIT TO DV_MONITOR
                                             *
ERROR at line 1:
ORA-01917: user or role 'DV_MONITOR' does not exist
SQL>
GRANT READ ON AUDSYS.DV$ENFORCEMENT_AUDIT TO DV_SECANALYST
                                             *
ERROR at line 1:
ORA-01917: user or role 'DV_SECANALYST' does not exist
SQL>
SQL>
SQL>
SQL> alter view audsys.dv$configuration_audit compile;
alter view audsys.dv$enforcement_audit compile;
View altered.
SQL>
View altered.
SQL>
SQL> select owner,object_name,object_type from dba_objects where status='INVALID' and object_type not like '%JAVA%' and owner='SYS';
OWNER
--------------------------------------------------------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
-----------------------
SYS
DRIPARSE
PACKAGE BODY
SYS
DRIXMD
PACKAGE BODY
OWNER
--------------------------------------------------------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
-----------------------
SYS
DRVDDL
PACKAGE BODY
SYS
DRVXTAB
OWNER
--------------------------------------------------------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
-----------------------
PACKAGE BODY
SYS
LBAC_EXP
PACKAGE BODY
SYS
OWNER
--------------------------------------------------------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
-----------------------
OLS_ENFORCEMENT
PACKAGE BODY
6 rows selected.
SQL> set linesize 150
column owner format a20
column object_name format a30
select owner, object_name, object_type, status from dba_objects
where object_name like ('%CONFIGURATION_AUDIT%') or object_name like ('%ENFORCEMENT_AUDIT%')
order by 1,2;SQL> SQL> SQL>   2    3
OWNER                OBJECT_NAME                    OBJECT_TYPE             STATUS
-------------------- ------------------------------ ----------------------- -------
AUDSYS               DV$CONFIGURATION_AUDIT         VIEW                    VALID
AUDSYS               DV$ENFORCEMENT_AUDIT           VIEW                    VALID



Thanks
Posted by at No comments:

Listener is not coming up with error TNS-12560 TNS-12557 TNS-00527 NL-08014

 Hi All,

Today one of production system we got below error while starting listener.


TNS-12557: TNS:protocol adapter not loadable
 TNS-12560: TNS:protocol adapter error
  TNS-00527: Protocol Adapter not loadable
    NL-08014: Failed to initialize Diagnosability framework, falling back to old network tracing/logging
     NL-08002: Diagnosability  context creation failed

Found issue related to permission on directory /var/tmp
Where oracle user doesn't have permission to write. After permission issue fixed listener came up fine.

Thanks,
Posted by at No comments:

Tuesday, August 2, 2022

How to increase SWAP space | On linux increase swap space

 [root@oranode2 Packages]# free -g
             total       used       free     shared    buffers     cached
Mem:             3          3          0          0          0          1
-/+ buffers/cache:          1          1
Swap:            2          0          2
[root@oranode2 Packages]# swapon -s
Filename                                Type            Size    Used    Priority
/dev/dm-1                               partition       3145724 284     -1

[root@oranode2 Packages]#
[root@oranode2 Packages]# dd if=/dev/zero of=/swap_file bs=2G count=1
0+1 records in
0+1 records out
2147479552 bytes (2.1 GB) copied, 8.89754 s, 241 MB/s
[root@oranode2 Packages]# chmod 600 /swap_file
[root@oranode2 Packages]# mkswap /swap_file
Setting up swapspace version 1, size = 2097144 KiB
no label, UUID=4b4ba791-fc09-4eb7-89a1-7571f11a5f20
[root@oranode2 Packages]# vi /etc/fstab
[root@oranode2 Packages]# cat /etc/fstab

#
# /etc/fstab
# Created by anaconda on Fri May  6 22:51:13 2022
#
# Accessible filesystems, by reference, are maintained under '/dev/disk'
# See man pages fstab(5), findfs(8), mount(8) and/or blkid(8) for more info
#
/dev/mapper/rhel-root   /                       xfs     defaults        1 1
UUID=05356daf-d70a-4ad8-b3cd-e723f9b11202 /boot                   xfs     defaults        1 2
/dev/mapper/rhel-swap   swap                    swap    defaults        0 0
/dev/sdb1 /u02 xfs     defaults        0 0
/swap_file  swap  swap  defaults  0 0

[root@oranode2 Packages]#  swapon /swap_file
[root@oranode2 Packages]#  free -g
             total       used       free     shared    buffers     cached
Mem:             3          1          2          0          0          0
-/+ buffers/cache:          1          2
Swap:            4          0          4


Thanks,
Posted by at No comments:
Subscribe to: Posts (Atom)