FIND OUT IF AN INIT PARAMETER IS MODIFIED OR NOT
There are multiple ways to identify a human change of system parameter in oracle database. This is really essential for any DBA because of security reasons and to avoid any future impacts due to repercussions of the change unknowingly or intentionally by junior guy or external hackers.
Modify the parameter value for parallel_max_servers
SQL> set time on
14:09:03 SQL> show parameter parallel_max_ser
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_max_servers integer 40
14:09:09 SQL> alter system set parallel_max_servers=80;
System altered.
1)Check dba_hist_parameter to check the modified status of the parameter. This view depends on AWR snapshots and its retention. If the retention period is expired, then there is no use of this method.
14:39:32 SQL> SELECT SNAP_ID,PARAMETER_NAME,VALUE,ISDEFAULT,ISMODIFIED from dba_hist_parameter WHERE PARAMETER_NAME='parallel_max_servers' and ISMODIFIED='MODIFIED' order by SNAP_ID asc;
SNAP_ID PARAMETER_NAME VALUE ISDEFAULT ISMODIFIED
---------- -------------------- -------------------- --------- ----------
100 parallel_max_servers 80 TRUE MODIFIED
2) Probe the alert log for change of parameter. This is because, alert log is the bible to refer for any parameter change in the database. There are some exceptional cases where alert log may miss the changes.
[oracle@xhydra ~]$ grep 'parallel_max_servers' /u01/app/oracle/diag/rdbms/db9zx/db9zx/trace/alert_db9zx.log
ALTER SYSTEM SET parallel_max_servers=80 SCOPE=BOTH;
3)Another way is to enable 10046 system trace in the database all the time. This may record all the happenings in the database and may need an extra storage. This trace can be periodically compressed with extreme compression to counter storage issues. But the benefits of using this trace outweighs the issue itself. Just to identify an ‘ONCE IN A BLUEMOON’ happening, it is not necessary to enable trace all the time. But 10046 trace also has other performance diagnostics which can balance the benefits to drawback.
SQL> alter system set tracefile_identifier='10046';
Session altered.
SQL> alter system set events '10046 trace name context forever,level 2';
Session altered.
...
*** 2023-06-17T07:50:58.502863+05:30 (CDB$ROOT(1))
WAIT #140609190796088: nam='SQL*Net message from client' ela= 12117882 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=6439910440
CLOSE #140609190796088:c=6,e=6,dep=0,type=1,tim=6439910526
=====================
PARSING IN CURSOR #140609190794448 len=40 dep=0 uid=0 oct=49 lid=0 tim=6439910690 hv=3012368407 ad='0' sqlid='bvkx42utsu60r'
alter system set parallel_max_servers=80 <==================
END OF STMT
PARSE #140609190794448:c=144,e=144,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=6439910690
WAIT #140609190794448: nam='reliable message' ela= 169 channel context=2023208056 channel handle=2023112368 broadcast message=2026347512 obj#=-1 tim=6439911255
WAIT #140609190794448: nam='Disk file operations I/O' ela= 86 FileOperation=2 fileno=0 filetype=13 obj#=-1 tim=6439911404
WAIT #140609190794448: nam='Parameter File I/O' ela= 8 blkno=1 #blks=1 read/write=1 obj#=-1 tim=6439911431
WAIT #140609190794448: nam='Parameter File I/O' ela= 2 blkno=2 #blks=3 read/write=1 obj#=-1 tim=6439911496
WAIT #140609190794448: nam='Parameter File I/O' ela= 8226 blkno=5 #blks=3 read/write=2 obj#=-1 tim=6439920451
WAIT #140609190794448: nam='Parameter File I/O' ela= 753 blkno=1 #blks=1 read/write=2 obj#=-1 tim=6439921293
WAIT #140609190794448: nam='Parameter File I/O' ela= 6 blkno=5 #blks=3 read/write=1 obj#=-1 tim=6439921336
WAIT #140609190794448: nam='Parameter File I/O' ela= 674 blkno=2 #blks=3 read/write=2 obj#=-1 tim=6439922021
WAIT #140609190794448: nam='Parameter File I/O' ela= 581 blkno=1 #blks=1 read/write=2 obj#=-1 tim=6439922633
WAIT #140609190794448: nam='Parameter File I/O' ela= 926 blkno=5 #blks=3 read/write=2 obj#=-1 tim=6439923582
WAIT #140609190794448: nam='Disk file operations I/O' ela= 14 FileOperation=5 fileno=0 filetype=13 obj#=-1 tim=6439923618
WAIT #140609190794448: nam='Disk file operations I/O' ela= 20 FileOperation=8 fileno=0 filetype=8 obj#=-1 tim=6439923976
EXEC #140609190794448:c=2131,e=13302,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=0,tim=6439924018
WAIT #140609190794448: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=6439924076
4) Use audit trails