The current project’s Python requirement (>=3.6,<4.0) is not compatible with some of the required packages Python requirement:

When adding flask package to virtual environment using poetry, there is a compatibility error due to python version

(virenv) [root@hydrupgrd bin]# poetry add flask
Using version ^2.2.3 for flask

Updating dependencies
Resolving dependencies... (0.0s)

The current project's Python requirement (>=3.6,<4.0) is not compatible with some of the required packages Python requirement:
  - flask requires Python >=3.7, so it will not be satisfied for Python >=3.6,<3.7

Because no versions of flask match >2.2.3,<3.0.0
 and flask (2.2.3) requires Python >=3.7, flask is forbidden.
So, because test depends on flask (^2.2.3), version solving failed.

  • Check your dependencies Python requirement: The Python requirement can be specified via the `python` or `markers` properties

    For flask, a possible solution would be to set the `python` property to ">=3.7,<4.0"

    https://python-poetry.org/docs/dependency-specification/#python-restricted-dependencies,
    https://python-poetry.org/docs/dependency-specification/#using-environment-markers

When the python version is checked, it shows 3.9 but current directory is not under virtual env of 3.9

(virenv) [root@hydrupgrd test1]# which python3.9
/home/oracle/virenv/bin/test/virenv/bin/python3.9

After moving to the virtual env location which says that compatibility is >3.7 in the error itself

(virenv) [root@hydrupgrd bin]# cd test1/
(virenv) [root@hydrupgrd test1]# ls
poetry.lock  pyproject.toml  README.md  test1  tests

(virenv) [root@hydrupgrd test1]# cat pyproject.toml
[tool.poetry]
name = "test1"
version = "0.1.0"
description = ""
authors = ["Your Name <you@example.com>"]
readme = "README.md"

[tool.poetry.dependencies]
python = "^3.9" <==================
flask = "^2.2.3"

Successfully downloaded flask

(virenv) [root@hydrupgrd bin]# cd test1/
(virenv) [root@hydrupgrd test1]# ls
poetry.lock  pyproject.toml  README.md  test1  tests
(virenv) [root@hydrupgrd test1]# poetry add flask
The following packages are already present in the pyproject.toml and will be skipped:

  • flask

If you want to update it to the latest compatible version, you can use `poetry update package`.
If you prefer to upgrade it to the latest available version, you can use `poetry add package@latest`.

Nothing to add.
WHAT ARE THESE TEMPORARY FILES IN USERS TABLESPACE?

During data import of list of tables from source to target, few of the tables failed due to lack of space in server.

. . imported "C##HYDRA"."SI_IMAGE_FORMATS_TAB"          61.81 MB 2614020 rows
ORA-39171: Job is experiencing a resumable wait.
Resumable error: ORA-01688: unable to extend table C##HYDRA.SALES partition SALES_Q3_2007 by 1024 in tablespace USERS
Resumable stmt:  BEGIN    SYS.KUPW$WORKER.MAIN('SYS_IMPORT_FULL_01', 'SYS', 0, 0);  END;
Resumable stmt status: SUSPENDED
Resumable stmt start: 03/22/23 20:53:26 stmt suspend: 03/22/23 20:53:41
ORA-39171: Job is experiencing a resumable wait.
Resumable error: ORA-01688: unable to extend table C##HYDRA.SALES partition SALES_Q3_2007 by 1024 in tablespace USERS
Resumable stmt:  BEGIN    SYS.KUPW$WORKER.MAIN('SYS_IMPORT_FULL_01', 'SYS', 0, 0);  END;
Resumable stmt status: SUSPENDED
Resumable stmt start: 03/22/23 20:53:26 stmt suspend: 03/22/23 20:53:41
ORA-39171: Job is experiencing a resumable wait.

After incomplete refresh, the user of source created in target was dropped due to space constraints and to get time to add additional space to disk.

kIsH@xyl>>drop user c##hydra cascade;

User dropped.

After dropping the user, datafile need to shrinked in order to release space from the server.

kIsH@xyl>>ALTER DATABASE DATAFILE '/u02/users01.dbf' resize 500M;
ALTER DATABASE DATAFILE '/u02/users01.dbf' resize 500M
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

There is an unusual segment type called temporary segments.

TEMPORARY segments in a permanent tablespace are generated during,

  • CTAS
  • Index operations
  • Reorg
kIsH@xyl>>col SEGMENT_NAME for a20
kIsH@xyl>>select SEGMENT_NAME,bytes/1048576 MB,segment_type from dba_segments where TABLESPACE_NAME='USERS' and SEGMENT_TYPE='TEMPORARY';

SEGMENT_NAME                 MB SEGMENT_TYPE
-------------------- ---------- ------------------
7.82321                      24 TEMPORARY
7.102801                   7247 TEMPORARY

Check the tablespace number in which the segment is resided.

kIsH@xyl>>SELECT ts# from ts$ WHERE NAME='USERS';

       TS#
----------
         4

Identify the block id of the segments to identify the object

kIsH@xyl>>SELECT FILE#,BLOCK#,TYPE# from seg$ WHERE TS#=4;

     FILE#     BLOCK#      TYPE#
---------- ---------- ----------
         7        130          5
         7        138          6
         7        146          5
         7        154          6
         7        162          5
         7        170          6
         7        178          5
         7        186          6
         7        194          6
         7        202          5
         7        210          6
         7        218          5
         7        226          6
         7        234          5
         7        242          6
         7        250          5
         7        258          6
         7        266          6
         7        274          6
         7        282          5
         7        290          6
         7        298          5
         7        306          6
         7        314          5
         7        322          6
         7        330          5
         7        338          6
         7        346          5
         7        354          5
         7        362          5
         7        370          5
         7        378          5
         7       6545          5
         7       7569          5
         7       8593          5
         7       9617          5
         7      10641          5
         7      11665          5
         7      12689          5
         7      13713          5
         7      14737          5
         7      15761          5
         7      16785          5
         7      17809          5
         7      18833          5
         7      19857          5
         7      20881          5
         7      21905          5
         7      22929          5
         7      23953          5
         7      24977          5
         7      26001          5
         7      27025          5
         7      28049          5
         7      29073          5
         7      30097          5
         7      31121          5
         7      32145          5
         7      33169          5
         7      34193          5
         7      35217          5
         7      36241          5
         7      37265          5
         7      38289          5
         7      39313          5
         7      40337          5
         7      41361          5
         7      42385          5
         7      43409          5
         7      44433          5
         7      45457          5
         7      46481          5
         7      47505          5
         7      48529          5
         7      49553          5
         7      50577          5
         7      51601          5
         7      52625          5
         7      53649          5
         7      54673          5
         7      55697          5
         7      56721          5
         7      57745          5
         7      58769          5
         7      59793          5
         7      60817          5
         7      61841          5
         7      62865          5
         7      63889          5
         7      64913          5
         7      65937          5
         7      66961          5
         7      67985          5
         7      69009          5
         7      70033          5
         7      71057          5
         7      72081          5
         7      73105          5
         7      74129          5
         7      75153          5
         7      76177          5
         7      77201          5
         7      78225          5
         7      79249          5
         7      80273          5
         7      81297          5
         7      82321          5
         7      83345          5
         7      84369          5
         7      85393          5
         7      86417          5
         7      87441          5
         7      88465          5
         7      89489          5
         7      90513          5
         7      91537          5
         7      92561          5
         7      93585          5
         7      94609          5
         7      95633          5
         7      96657          5
         7      97681          5
         7      98705          5
         7      99729          5
         7     100753          5
         7     101777          5
         7     102801          5
         7     103825          5
         7     104849          5
         7     105858          5
         7     105866          5
         7     105874          5
         7     105882          5
         7     105890          5
         7     105898          5
         7     105906          5
         7     105914          5
         7     105922          5
         7     105929          8
         7     105946          6
         7     105954          5
         7     105961          8
         7     105978          6
         7     106001          5
         7     107009          8
         7     107026          6
         7     107033          8
         7     107050          6
         7     126609          5
         7     129681          5
         7     141969          5
         7     142993          5
         7     144017          5
         7     146065          5
         7     453266          3
         7     461458          3

156 rows selected.

Trace a particular block id to check the contents in it.

kIsH@xyl>>alter session set tracefile_identifier='bdump130';

Session altered.

kIsH@xyl>>ALTER SYSTEM DUMP DATAFILE 7 BLOCK 130;

System altered.

Further checking the object id, it shows the objects which were imported with space failure.

Start dump data blocks tsn: 4 file#:7 minblk 130 maxblk 130
Block dump from cache:
Dump of buffer cache at level 3 for pdb=1 tsn=4 rdba=29360258
BH (0x9cf79128) file#: 7 rdba: 0x01c00082 (7/130) class: 4 ba: 0x9c3d6000
  set: 3 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,0
  dbwrid: 0 obj: 63385 objn: 63385 tsn: [1/4] afn: 7 hint: f
  hash: [0x7b402fe8,0x7b402fe8] lru: [0x9cf79378,0x9cf790a8]
  ckptq: [NULL] fileq: [NULL]
  objq: [0x70ee72f0,0x70ee72f0] objaq: [0x70ee72e0,0x70ee72e0]
  st: XCURRENT md: NULL fpin: 'ktswh97: ktsa_object_space_usage' fscn: 0x33fde4 tch: 1
  flags:
  LRBA: [0x0.0.0] LSCN: [0x0] HSCN: [0x0] HSUB: [65535]
  Printing buffer operation history (latest change first):
  cnt: 9
  01. sid:03 L145:zib:mk:EXCL         02. sid:03 L212:zib:bic:FSQ
  03. sid:03 L122:zgb:set:st          04. sid:03 L830:olq1:clr:WRT+CKT
  05. sid:03 L951:zgb:lnk:objq        06. sid:03 L372:zgb:set:MEXCL
  07. sid:03 L123:zgb:no:FEN          08. sid:03 L083:zgb:ent:fn
  09. sid:07 L203:w_ini_dc:bic:FVB
  buffer tsn: 4 rdba: 0x01c00082 (7/130)
  scn: 0x1317a6 seq: 0x03 flg: 0x04 tail: 0x17a62303
  frmt: 0x02 chkval: 0x5c01 type: 0x23=PAGETABLE SEGMENT HEADER
Hex dump of block: st=0, typ_found=1
  Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      spare2: 0      #extents: 1      #blocks: 8
                  last map  0x00000000  #maps: 0      offset: 2716
      Highwater::  0x01c00088  ext#: 0      blk#: 8      ext size: 8
  #blocks in seg. hdr's freelists: 0
  #blocks below: 5
  mapblk  0x00000000  offset: 0
                   Unlocked
  --------------------------------------------------------
  Low HighWater Mark :
      Highwater::  0x01c00088  ext#: 0      blk#: 8      ext size: 8
  #blocks in seg. hdr's freelists: 0
  #blocks below: 5
  mapblk  0x00000000  offset: 0
  Level 1 BMB for High HWM block: 0x01c00080
  Level 1 BMB for Low HWM block: 0x01c00080
  --------------------------------------------------------
  Segment Type: 1 nl2: 1      blksz: 8192   fbsz: 0
  L2 Array start offset:  0x00001434
  First Level 3 BMB:  0x00000000
  L2 Hint for inserts:  0x01c00081
  Last Level 1 BMB:  0x01c00080
  Last Level II BMB:  0x01c00081
  Last Level III BMB:  0x00000000
     Map Header:: next  0x00000000  #extents: 1    obj#: 63385  flag: 0x10000000
  Inc # 0
  Extent Map
  -----------------------------------------------------------------
   0x01c00080  length: 8

  Auxillary Map
  --------------------------------------------------------
   Extent 0     :  L1 dba:  0x01c00080 Data dba:  0x01c00083
  --------------------------------------------------------

   Second Level Bitmap block DBAs
   --------------------------------------------------------
   DBA 1:   0x01c00081

The object is part of a refresh job which was imported from source database.

kIsH@xyl>>SELECT object_name from dba_objects WHERE object_id=63385;

OBJECT_NAME
--------------------------------------------------------------------------------
SI_IMAGE_FORMATS_TAB

Set the event below to drop temporary segments in tablespace 4.

kIsH@xyl>>ALTER SESSION SET EVENTS 'immediate trace name drop_segments level 4';

Session altered.

Still these temp segments in permanent tablespace remain constant, even after a reboot of the database.

kIsH@xyl>>col SEGMENT_NAME for a20
kIsH@xyl>>select SEGMENT_NAME,bytes/1048576 MB,segment_type from dba_segments where TABLESPACE_NAME='USERS' and SEGMENT_TYPE='TEMPORARY';

SEGMENT_NAME                 MB SEGMENT_TYPE
-------------------- ---------- ------------------
7.82321                      24 TEMPORARY
7.102801                   7247 TEMPORARY

After checking the alertlogs, it seems that there is no space for archive logs and redo logs as well.

ORA-27072: File I/O error
ORA-27072: File I/O error
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/XYLIDWRA/redo01.log'
ORA-27072: File I/O error
ORA-19502: write error on file "/u02/FRA/XYLIDWRA/archivelog/2023_03_23/o1_mf_1_37_l1r742cz_.arc", block number 1 (block size=512)
ORA-19502: write error on file "/u02/FRA/XYLIDWRA/archivelog/2023_03_23/o1_mf_1_37_l1r742cz_.arc", block number 1 (block size=512)
ORA-27072: File I/O error
ORA-19502: write error on file "/u02/FRA/XYLIDWRA/archivelog/2023_03_23/o1_mf_1_37_l1r742cz_.arc", block number 1 (block size=512)
ORA-16038: log 1 sequence# 37 cannot be archived
ORA-19502: write error on file "", block number  (block size=)
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/XYLIDWRA/redo01.log'

Errors in file /u01/app/oracle/diag/rdbms/xylidwra/xylidwra/trace/xylidwra_arc2_2644.trc:
ORA-16038: log 1 sequence# 37 cannot be archived
ORA-19502: write error on file "", block number  (block size=)
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/XYLIDWRA/redo01.log'
ARC2 (PID:2644): Archival error occurred on a closed thread, archiver continuing
2023-03-23T14:58:45.197245+05:30
ORACLE Instance xylidwra, archival error, archiver continuing
2023-03-23T14:59:45.546819+05:30
Deleted file /u01/app/oracle/product/19.0.0/dbhome_1/dbs/archreact_test_3cd7aeef.arc
TT00 (PID:2636): Stuck archiver condition cleared
2023-03-23T14:59:45.565151+05:30
ARC1 (PID:2642): Encountered disk I/O error 19502
2023-03-23T14:59:45.565195+05:30
Closing local archive destination LOG_ARCHIVE_DEST_1 '/u02/FRA/XYLIDWRA/archivelog/2023_03_23/o1_mf_1_37_l1r709kg_.arc', error=19502 (xylidwra)
2023-03-23T14:59:45.565313+05:30

Cleared the space in FRA mount point

[oracle@xylidwra dbs]$ df -h
Filesystem      Size  Used Avail Use% Mounted on
devtmpfs        3.8G     0  3.8G   0% /dev
tmpfs           3.8G     0  3.8G   0% /dev/shm
tmpfs           3.8G  9.1M  3.8G   1% /run
tmpfs           3.8G     0  3.8G   0% /sys/fs/cgroup
/dev/sda2        20G  6.0G   14G  31% /
/dev/sda5        36G   20G   17G  55% /u01
/dev/sda1        37G   37G  936K 100% /u02
tmpfs           767M   12K  767M   1% /run/user/42
tmpfs           767M  4.0K  767M   1% /run/user/54321
[oracle@xylidwra dbs]$ cd /u02
[oracle@xylidwra u02]$ ls
FRA  oradata  sample.dmp  sample.log  users01.dbf
[oracle@xylidwra u02]$ rm -rf sample.dmp
[oracle@xylidwra u02]$ df -h
Filesystem      Size  Used Avail Use% Mounted on
devtmpfs        3.8G     0  3.8G   0% /dev
tmpfs           3.8G     0  3.8G   0% /dev/shm
tmpfs           3.8G  9.1M  3.8G   1% /run
tmpfs           3.8G     0  3.8G   0% /sys/fs/cgroup
/dev/sda2        20G  6.0G   14G  31% /
/dev/sda5        36G   20G   17G  55% /u01
/dev/sda1        37G   26G   11G  71% /u02
tmpfs           767M   12K  767M   1% /run/user/42
tmpfs           767M  4.0K  767M   1% /run/user/54321

The temp segment was wiped out from the USERS tablespace.

kIsH@xyl>>select SEGMENT_NAME,bytes/1048576 MB,segment_type from dba_segments where TABLESPACE_NAME='USERS' and SEGMENT_TYPE='TEMPORARY';

no rows selected

Archive logs started to generate normally.

TT00 (PID:2636): Stuck archiver condition cleared
2023-03-23T15:03:55.698936+05:30
ARC1 (PID:2642): Archived Log entry 32 added for T-1.S-37 ID 0x3cd81cef LAD:1

These temporary segments get deposited as sediments in the database, when there is no space for the import to happen and also there is a direct reboot of the server.

To clean up these segments, they will be removed automatically, after releasing space.

PSP0 CONSUME HIGH CPU

At first, i thought PSP is PlayStation portable because i am addicted to video games. But not exactly, it is a process spawner background process which spawns additional processes according to the job requirements after instance startup.

There is not of much information in the internet of this background process. In one of the critical database, this PSP0 process was on top of the sessions in ASH all the time consuming 100% of the CPU.

If alert log is scrutinized, PSP0 is the process to be started after PMON which is also near critical process to schedule other required OS processes acting as a master process.

Initially, this PSP0 has consumed high CPU with an OEM alert.

After checking the ASH data, PSP0 is consistently consuming CPU but not high.

WHO     PGM   W_CHAINS                                 SQL_ID             TOTAL CHART_WCLASS         STATE       TAASH    PER%AAS
------- ----- ---------------------------------------- ------------- ---------- -------------------- ---------- ------ ----------
SYS     PSP0  On CPU                                                     178020  ################    BURN_CPU        0  1.8391192
SYS     PSP0  os thread creation                                             46                      LONG_WAIT       1 .826086957

To get more deeper, strace was attached to psp0 OS process to track the system calls. This should not practiced in production system.

[root@hydrupgrd oracle]# ps -ef|grep psp0
oracle     35045       1  0 20:17 ?        00:00:00 ora_psp0_hydrupgd
root       36811   36768  0 20:44 pts/0    00:00:00 grep --color=auto psp0
[root@hydrupgrd oracle]# strace -rp 35045 -o psp0.trc &
[1] 36858
[root@hydrupgrd oracle]# strace: Process 35045 attached

[root@hydrupgrd oracle]#
[root@hydrupgrd oracle]#
[root@hydrupgrd oracle]# ls -lrt|tail -1
-rw-r--r--   1 root   root          5339 Jan  4 20:45 psp0.trc
[root@hydrupgrd oracle]# vi psp0.trc

PSP0 process is one of that process in top which consume less CPU but consistent eater.

    PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND
  10847 oracle    20   0 2874964 217064   9380 S   1.3   2.8  77:55.21 java
   7607 oracle    20   0 2701968  12612  10408 S   0.7   0.2   0:03.40 ora_psp0_hydrup

To check the back stage of PSP0 from database centric, oradebug 10046 is enabled.

SQL> oradebug setospid 7607
Oracle pid: 3, Unix process pid: 7607, image: oracle@hydrupgrd (PSP0)
SQL> oradebug unlimit
Statement processed.
SQL> oradebug event 10046 trace name context forever,level 12;
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/hydrupgd/hydrupgd/trace/hydrupgd_psp0_7607.trc

Trace reveals that the PSP0 process sits idle most of the time waiting on ‘rdbms ipc message’ from the foreground process to allocate work to spawn a process.

In between, there is an event called ‘os thread creation‘ which creates alerts for the OS scheduler to spawn threads on behalf of the database. This is the reason why we call it Process spawner.

[oracle@hydrupgrd ~]$ tail -f /u01/app/oracle/diag/rdbms/hydrupgd/hydrupgd/trace/hydrupgd_psp0_7607.trc
WAIT #0: nam='rdbms ipc message' ela= 1018986 timeout=100 p2=0 p3=0 obj#=-1 tim=7526702932

*** 2023-01-05 18:17:54.048
WAIT #0: nam='rdbms ipc message' ela= 1019628 timeout=100 p2=0 p3=0 obj#=-1 tim=7527726928

WAIT #0: nam='rdbms ipc message' ela= 823545 timeout=100 p2=0 p3=0 obj#=-1 tim=8233583633
WAIT #0: nam='os thread creation' ela= 48236 pname=1244672048 is_process=1 p3=0 obj#=-1 tim=8233631937
WAIT #0: nam='rdbms ipc message' ela= 96816 timeout=13 p2=0 p3=0 obj#=-1 tim=8233728859
WAIT #0: nam='os thread creation' ela= 64997 pname=1244672049 is_process=1 p3=0 obj#=-1 tim=8233793909

PSP process uses select() call to monitor the file descriptors for an IO call, periodically accesses the oracle binary file and also check /proc/pid/stat about status of pmon

     0.000010 semtimedop(3, [{sem_num=7, sem_op=-1, sem_flg=0}], 1, {tv_sec=1, tv_nsec=0}) = -1 EAGAIN (Resource temporarily unavailable)
     1.019687 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=57133}, ru_stime={tv_sec=0, tv_usec=166010}, ...}) = 0
     0.000026 getrusage(RUSAGE_THREAD, {ru_utime={tv_sec=0, tv_usec=57133}, ru_stime={tv_sec=0, tv_usec=166014}, ...}) = 0
     0.000012 times(NULL)               = 431336006
     0.000009 times(NULL)               = 431336006
     0.000008 select(0, 0x7ffcaef35330, 0x7ffcaef35330, 0x7ffcaef35330, {tv_sec=0, tv_usec=1000}) = 0 (Timeout)
     0.001083 select(0, 0x7ffcaef35330, 0x7ffcaef35330, 0x7ffcaef35330, {tv_sec=0, tv_usec=1000}) = 0 (Timeout)
     0.001072 select(0, 0x7ffcaef35330, 0x7ffcaef35330, 0x7ffcaef35330, {tv_sec=0, tv_usec=1000}) = 0 (Timeout)
     0.001079 select(0, 0x7ffcaef35330, 0x7ffcaef35330, 0x7ffcaef35330, {tv_sec=0, tv_usec=1000}) = 0 (Timeout)
     0.001083 times(NULL)               = 431336006
     0.000010 semtimedop(3, [{sem_num=7, sem_op=-1, sem_flg=0}], 1, {tv_sec=1, tv_nsec=0}) = 0
     0.189959 access("/u01/app/oracle/product/12.1.0/dbhome_3/bin/oracle", X_OK) = 0
     0.000035 pipe([10, 11])            = 0
     0.000018 clone(child_stack=NULL, flags=CLONE_CHILD_CLEARTID|CLONE_CHILD_SETTID|SIGCHLD, child_tidptr=0x7f1840f57550) = 36977
     0.000175 close(11)                 = 0
     0.000017 read(10, 0x7ffcaef356d0, 16) = ? ERESTARTSYS (To be restarted if SA_RESTART is set)
     0.000694 --- SIGCHLD {si_signo=SIGCHLD, si_code=CLD_EXITED, si_pid=36977, si_uid=54321, si_status=0, si_utime=0, si_stime=0} ---
     0.000010 read(10, "r\220\0\0\0\0\0\0\376\205\34\0\0\0\0\0", 16) = 16
     0.001724 close(10)                 = 0
     0.000016 wait4(36977, [{WIFEXITED(s) && WEXITSTATUS(s) == 0}], 0, NULL) = 36977
     0.000030 times(NULL)               = 431336026

     0.000012 openat(AT_FDCWD, "/proc/35043/stat", O_RDONLY) = 10
     0.000027 read(10, "35043 (ora_pmon_hydrup) S 1 3504"..., 999) = 307
     0.000015 close(10)                 = 0

Below hidden parameter can be used to flow control diagnostics related to PSP0 to prevent flooding of the queues and resource allocation

SQL> set lines 200 pages 1000
col "Hidden_parameter" for a40
col "It's Value" for a20
col Desc for a40
select x.ksppinm as Hidden_parameter,
         y.ksppstvl "It's Value",
                   x.ksppdesc "Desc"
     from
  x$ksppi x
  inner join x$ksppcv y on (x.indx = y.indx)
and
  x.ksppinm like '%psp%'
order by x.ksppinm;SQL> SQL> SQL> SQL>   2    3    4    5    6    7    8    9

HIDDEN_PARAMETER                         It's Value           Desc
---------------------------------------- -------------------- ----------------------------------------
_min_time_between_psp0_diag_secs         300                  minimum time between PSP0 diagnostic use
                                                              d for flow control

In my case, it seem that PSP0 consume high cpu due to the fact that there is a need to spawn multiple parallel processes due to high load which was the cause of high cpu caused by psp0. Hence reducing the parallel usage reduced the cpu usage by psp0

MONITORING PARALLEL QUERIES

All partitioned objects can be scheduled for parallel operation.

  • Index creation
  • CTAS
  • Move/Split/Coalesce partitions
  • Index/Split partition
  • Insert/Update/Delete/Select

Parallel DDL for non partitioned tables and indexes

  • Index creation
  • CTAS
  • Move table
  • Index rebuild
  • LOB columns not supported
  • Select

First step to monitor is from OS top command which will show ora_p0* process in COMMAND column which means that parallel slaves are spawned and utilized

top - 15:02:13 up  2:47,  3 users,  load average: 0.76, 0.55, 0.50
Tasks: 268 total,   1 running, 264 sleeping,   3 stopped,   0 zombie
%Cpu(s):  0.2 us,  0.1 sy,  0.0 ni, 99.2 id,  0.3 wa,  0.1 hi,  0.0 si,  0.0 st
MiB Mem :   9670.6 total,     96.6 free,    859.0 used,   8715.1 buff/cache
MiB Swap:   4096.0 total,   4035.0 free,     61.0 used.   2850.6 avail Mem

    PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND

   3728 oracle    20   0 6507020  73256  69588 S   0.3   0.7   0:04.76 ora_p001_hydrup
   3824 oracle    20   0 6504384  74240  70456 S   0.3   0.7   0:04.47 ora_p002_hydrup

Check v$pq_tqstat for parallel statistics after a commit/rollback operations specifically for any DML in 11g. But in 12c, statistics are printed without any commit/rollback. This view shows addition information about Query coordinator/Consumer and Producer with bytes processed by each parallel process for chunks of object. This view contains information only during the lifespan of a session and vanish after the session clears.

SQL> update /*+parallel(4)*/ rp set r_name='xyz' where r_id between 10000 and 100000;

0 rows updated.

SQL> col SERVER_TYPE for a15
SQL> col PROCESS for a10
SQL> set lines 200 pages 1000
SQL> select * from v$pq_tqstat;

DFO_NUMBER      TQ_ID SERVER_TYPE       NUM_ROWS      BYTES  OPEN_TIME AVG_LATENCY      WAITS   TIMEOUTS PROCESS      INSTANCE     CON_ID
---------- ---------- --------------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- ---------- ----------
         1          0 Producer                 0         24          0           0          1          0 P019                1          0
         1          0 Producer                 0         24          0           0          1          0 P018                1          0
         1          0 Producer                 0         24          0           0          1          0 P017                1          0
         1          0 Producer                 0         24          0           0          2          0 P016                1          0
         1          0 Consumer                 0         96          0           0         11          2 QC                  1          0

After using some joins, the degree used and required degree column can give idea on the number of parallel workers used and also ‘physical reads’ stats from stat name.

SQL> set lines 200 pages 1000
col NAME for a20
SELECT pss.sid,pss.serial#,pss.qcsid,pss.qcserial#,pss.degree,pss.req_degree,sn.name,sum(pss.value) Value
FROM v$px_sesstat pss
INNER JOIN v$statname sn on ( pss.statistic# = sn.statistic# )
WHERE pss.value <> 0
AND name like '%physical reads%'
GROUP BY pss.sid,pss.serial#,pss.qcsid,pss.qcserial#,pss.degree,pss.req_degree,sn.name
ORDER BY sn.name;
SQL> SQL>   2    3    4    5    6    7
       SID    SERIAL#      QCSID  QCSERIAL#     DEGREE REQ_DEGREE NAME                      VALUE
---------- ---------- ---------- ---------- ---------- ---------- -------------------- ----------
      1044       1105        522       2501         16         16 physical reads               10
       522       2501        522                                  physical reads              325
        28      14695        522       2501         16         16 physical reads               10
      1042      16300        522       2501         16         16 physical reads               11
        21      16376        522       2501         16         16 physical reads                6
       535      17448        522       2501         16         16 physical reads               21
      1046      17667        522       2501         16         16 physical reads                8
        22      20258        522       2501         16         16 physical reads                2
       538      22774        522       2501         16         16 physical reads               10
        31      29211        522       2501         16         16 physical reads                4
       540      31302        522       2501         16         16 physical reads                8
        30      34806        522       2501         16         16 physical reads               11
        29      39740        522       2501         16         16 physical reads               10
      1045      44806        522       2501         16         16 physical reads                9
       539      45883        522       2501         16         16 physical reads               12
       536      48194        522       2501         16         16 physical reads               11
        27      48201        522       2501         16         16 physical reads               10
       537      61286        522       2501         16         16 physical reads               10
      1035      63251        522       2501         16         16 physical reads               10
      1044       1105        522       2501         16         16 physical reads cache         10
       522       2501        522                                  physical reads cache        325
        28      14695        522       2501         16         16 physical reads cache         10
      1042      16300        522       2501         16         16 physical reads cache         11
        21      16376        522       2501         16         16 physical reads cache          6
       535      17448        522       2501         16         16 physical reads cache         21
      1046      17667        522       2501         16         16 physical reads cache          8
        22      20258        522       2501         16         16 physical reads cache          2
       538      22774        522       2501         16         16 physical reads cache         10
        31      29211        522       2501         16         16 physical reads cache          4
       540      31302        522       2501         16         16 physical reads cache          8
        30      34806        522       2501         16         16 physical reads cache         11
        29      39740        522       2501         16         16 physical reads cache         10
      1045      44806        522       2501         16         16 physical reads cache          9
       539      45883        522       2501         16         16 physical reads cache         12
       536      48194        522       2501         16         16 physical reads cache         11
        27      48201        522       2501         16         16 physical reads cache         10
       537      61286        522       2501         16         16 physical reads cache         10
      1035      63251        522       2501         16         16 physical reads cache         10
       522       2501        522                                  physical reads cache         12
                                                                   prefetch


39 rows selected.

Check the parallel server which is used currently and which is idle

SQL> set lines 200 pages 1000
col NAME for a20
SELECT pxp.server_name,pxp.status,pxp.spid,
       pss.sid,pss.serial#,pss.qcsid,pss.qcserial#,
       pss.degree,pss.req_degree,sn.name,sum(pss.value) Value
FROM v$px_sesstat pss
INNER JOIN v$statname sn on ( pss.statistic# = sn.statistic# )
INNER JOIN v$px_process pxp on ( pxp.sid = pss.sid )
WHERE pss.value <> 0
AND name like '%physical reads%'
GROUP BY pss.sid,pss.serial#,pss.qcsid,pss.qcserial#,
         pss.degree,pss.req_degree,sn.name,
         pxp.server_name,pxp.status,pxp.spid
ORDER BY sn.name;



SERV STATUS    SPID                            SID    SERIAL#      QCSID  QCSERIAL#     DEGREE REQ_DEGREE NAME                      VALUE
---- --------- ------------------------ ---------- ---------- ---------- ---------- ---------- ---------- -------------------- ----------
P005 IN USE    3830                             27       5577        534      61515         16         16 physical reads              207
P002 IN USE    3824                             22      10359        534      61515         16         16 physical reads              161
P00C IN USE    31616                           531      11558        534      61515         16         16 physical reads              191

To be more query specific which uses parallel.

SQL> set lines 200 pages 1000
col sql_id for a10
col sql_text for a20
col spid for a10
col NAME for a15
col sid for 999999
col degree for 999
col serial# for 999999
col REQ_DEGREE for 999
SELECT ses.sql_id,sql.sql_text,pxp.server_name,pxp.status,pxp.spid,
       pss.sid,pss.serial#,pss.qcsid,pss.qcserial#,
           pss.degree,pss.req_degree,sn.name,sum(pss.value) Value
FROM v$px_sesstat pss
INNER JOIN v$statname sn on ( pss.statistic# = sn.statistic# )
INNER JOIN v$px_process pxp on ( pxp.sid = pss.sid )
INNER JOIN v$session ses on ( ses.sid = pss.sid )
INNER JOIN v$sqlarea sql on ( sql.sql_id = ses.sql_id )
WHERE pss.value <> 0
AND name like '%physical reads%'
GROUP BY pss.sid,pss.serial#,pss.qcsid,pss.qcserial#,
         pss.degree,pss.req_degree,sn.name,
                 pxp.server_name,pxp.status,pxp.spid,
                 ses.sql_id,sql.sql_text
ORDER BY sn.name;SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15

SQL_ID     SQL_TEXT             SERV STATUS    SPID           SID SERIAL#      QCSID  QCSERIAL# DEGREE REQ_DEGREE NAME                 VALUE
---------- -------------------- ---- --------- ---------- ------- ------- ---------- ---------- ------ ---------- --------------- ----------
25zjubx56k SELECT /*+parallel(1 P003 IN USE    2996           526    3682        516      47395     16         16 physical reads         139
pm7        6)*/ * from c##hydra
           .addresses

25zjubx56k SELECT /*+parallel(1 P005 IN USE    3000            15   10456        516      47395     16         16 physical reads         175
pm7        6)*/ * from c##hydra
           .addresses

To check the user who executes parallel

set lines 200 pages 1000
col sql_id for a10
col sql_text for a20
col spid for a10
col NAME for a15
col sid for 999999
col degree for 999
col serial# for 999999
col RDEG for 999
col username for a10
col QCSID for 99999
SELECT ses.username,ses.sql_id,sql.sql_text,pxp.server_name,
       pss.sid,pss.serial#,pss.qcsid,pss.qcserial#,pxp.status,pxp.spid,
	   pss.degree,pss.req_degree as "RDEG",sn.name,sum(pss.value) Value
FROM v$px_sesstat pss
INNER JOIN v$statname sn on ( pss.statistic# = sn.statistic# )
INNER JOIN v$px_process pxp on ( pxp.sid = pss.sid )
INNER JOIN v$session ses on ( ses.sid = pss.sid )
INNER JOIN v$sqlarea sql on ( sql.sql_id = ses.sql_id )
WHERE pss.value <> 0
AND name like '%physical reads%'
GROUP BY pss.sid,pss.serial#,pss.qcsid,pss.qcserial#,
         pss.degree,pss.req_degree,sn.name,
	 pxp.server_name,pxp.status,pxp.spid,
	 ses.sql_id,sql.sql_text,ses.username
ORDER BY sn.name;

Similar to normal sysstat, there is a separate view for px as well. This view retrieves the parallel servers capacity and utilization for system statistics

SQL> SELECT * FROM V$PX_PROCESS_SYSSTAT;

STATISTIC                                                                                                                     VALUE     CON_ID
------------------------------------------------------------------------------------------------------------------------ ---------- ----------
Servers In Use                                                                                                                   32          0
Servers Available                                                                                                                12          0
Servers Started                                                                                                                  84          0
Servers Shutdown                                                                                                                 40          0
Servers Highwater                                                                                                                40          0
Servers Cleaned Up                                                                                                                0          0
Server Sessions                                                                                                                4128          0
Memory Chunks Allocated                                                                                                          16          0
Memory Chunks Freed                                                                                                               6          0
Memory Chunks Current                                                                                                            10          0
Memory Chunks HWM                                                                                                                10          0
Buffers Allocated                                                                                                             27686          0
Buffers Freed                                                                                                                 26822          0
Buffers Current                                                                                                                 864          0
Buffers HWM                                                                                                                     946          0

15 rows selected.

To check for individual session statistics related to px, use the below query.

SQL> SELECT * FROM V$PQ_SESSTAT;

STATISTIC                      LAST_QUERY SESSION_TOTAL     CON_ID
------------------------------ ---------- ------------- ----------
Queries Parallelized                    0             0          0
DML Parallelized                        0             0          0
DDL Parallelized                        0             0          0
DFO Trees                               0             0          0
Server Threads                          0             0          0
Allocation Height                       0             0          0
Allocation Width                        0             0          0
Local Msgs Sent                         0             0          0
Distr Msgs Sent                         0             0          0
Local Msgs Recv'd                       0             0          0
Distr Msgs Recv'd                       0             0          0
DOP                                     0             0          0
Slave Sets                              0             0          0

13 rows selected.

Below parameters are from both CDB and non-CDB database. These parameters should tweaked at times during any parallel issues. So monitoring these parameters in every database is essential.

SQL> show parameter parallel

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback         string      LOW
parallel_adaptive_multi_user         boolean     TRUE
parallel_automatic_tuning            boolean     FALSE
parallel_degree_level                integer     100
parallel_degree_limit                string      CPU
parallel_degree_policy               string      AUTO
parallel_execution_message_size      integer     32768
parallel_force_local                 boolean     FALSE
parallel_instance_group              string
parallel_io_cap_enabled              boolean     FALSE
parallel_max_servers                 integer     100

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_min_percent                 integer     0
parallel_min_servers                 integer     12
parallel_min_time_threshold          string      AUTO
parallel_server                      boolean     FALSE
parallel_server_instances            integer     1
parallel_servers_target              integer     48
parallel_threads_per_cpu             integer     2
recovery_parallelism                 integer     0

SQL> show parameter parallel

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
awr_pdb_max_parallel_slaves          integer     10
containers_parallel_degree           integer     65535
fast_start_parallel_rollback         string      LOW
max_datapump_parallel_per_job        string      50
optimizer_ignore_parallel_hints      boolean     FALSE
parallel_adaptive_multi_user         boolean     FALSE
parallel_degree_limit                string      CPU
parallel_degree_policy               string      MANUAL
parallel_execution_message_size      integer     16384
parallel_force_local                 boolean     FALSE
parallel_instance_group              string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_max_servers                 integer     20
parallel_min_degree                  string      1
parallel_min_percent                 integer     0
parallel_min_servers                 integer     2
parallel_min_time_threshold          string      AUTO
parallel_servers_target              integer     20
parallel_threads_per_cpu             integer     1
recovery_parallelism                 integer     0

Check diag folders for parallel traces for any further monitoring after a parallel execution.

/u01/app/oracle/diag/rdbms/hydrupgd/hydrupgd/trace
[oracle@hydrupgrd trace]$ ls -lrt |grep p0|tail
-rw-r----- 1 oracle oinstall       4015 Mar 20 17:04 hydrupgd_psp0_3670.trm
-rw-r----- 1 oracle oinstall      43367 Mar 20 17:04 hydrupgd_psp0_3670.trc
-rw-r----- 1 oracle oinstall         84 Mar 21 18:38 hydrupgd_p001_40548.trm
-rw-r----- 1 oracle oinstall       1492 Mar 21 18:38 hydrupgd_p001_40548.trc
-rw-r----- 1 oracle oinstall         85 Mar 21 18:38 hydrupgd_p000_40546.trm
-rw-r----- 1 oracle oinstall       1496 Mar 21 18:38 hydrupgd_p000_40546.trc
-rw-r----- 1 oracle oinstall         84 Mar 22 07:30 hydrupgd_p001_3836.trm
-rw-r----- 1 oracle oinstall       1492 Mar 22 07:30 hydrupgd_p001_3836.trc
-rw-r----- 1 oracle oinstall         84 Mar 22 07:30 hydrupgd_p000_3834.trm
-rw-r----- 1 oracle oinstall       1493 Mar 22 07:30 hydrupgd_p000_3834.trc
ORA-17628: Oracle error 1031 returned by remote Oracle server
ORA-01031: insufficient privileges

There are multiple pdbs in cdb1

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 XYPDB1                         READ WRITE NO
         4 XYPDB2                         MOUNTED
         5 XYPDB3                         MOUNTED
         6 XYPDB4                         MOUNTED

From cdb1, create user and grant the necessary privileges to common user.

SQL> create user c##xyusr identified by password container=all;

User created.

SQL> grant create pluggable database, sysoper, create session to c##xyusr;

Grant succeeded.

Check tns connectivity

[oracle@xylidwra ~]$ tnsping xypdb1

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 21-MAR-2023 06:18:35

........

OK (0 msec)

Create database link to connect to the pdb in cdb1 from cdb2

SQL> create database link xydl connect to c##xyusr identified by "password" using 'xypdb1';

Database link created.

SQL> select * from dual@xydl;

D
-
X

Now the error occur due to privilege issue

SQL> create pluggable database pdbxy from xypdb1@xydl FILE_NAME_CONVERT=('/u01/app/oracle/oradata/XYLIDWRA/xypdb1/','/u01/app/oracle/oradata/XYLIDWRA/pdbxy');
create pluggable database pdbxy from xypdb1@xydl FILE_NAME_CONVERT=('/u01/app/oracle/oradata/XYLIDWRA/xypdb1/','/u01/app/oracle/oradata/XYLIDWRA/pdbxy')
*
ERROR at line 1:
ORA-17628: Oracle error 1031 returned by remote Oracle server
ORA-01031: insufficient privileges

Most of the time errors starting with 17* are pdb related which is supporting error and main error appear after that which is 1031

[oracle@xylidwra ~]$ oerr ora 1031
01031, 00000, "insufficient privileges"
// *Cause: An attempt was made to perform a database operation without
//         the necessary privileges.
// *Action: Ask your database administrator or designated security
//          administrator to grant you the necessary privileges
[oracle@xylidwra ~]$ oerr ora 17628
17628, 00000, "Oracle error %s returned by remote Oracle server"
// *Cause:  Oracle server on the remote instance has returned an error.
// *Action: Look at remote instance alert log/trace file for more information
//          and take appropriate action or contact Oracle Support Services
//          for further assistance

Since there are multiple containers, privileges were granted at CDB level but not individual pdbs

SQL> grant create pluggable database, sysoper, create session to c##xyusr container=all;

Grant succeeded.

Able to clone successfully

SQL> create pluggable database pdbxy from xypdb1@xydl FILE_NAME_CONVERT=('/u01/app/oracle/oradata/XYLIDWRA/xypdb1/','/u01/app/oracle/oradata/XYLIDWRA/pdbxy');

Pluggable database created.

Moral:

Grant privileges to necessary pdb if it is multitenant. Since, we get used to non-CDB often.

MULTIPLEX CONTROLFILES

Controlfile is a vital file which is the heart of database.It contains the information of details of database and used as a catalogue for datafiles and redolog files

Loss of controlfile crash the instance.

Multiplexing of controlfile is a good way to avoid loss or corruption of a controlfile.

There are two redundancy files,I create high redundancy of controfile

SQL> show parameter control_files
NAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------control_files                        string      +DATA/ORCL/CONTROLFILE/current                                                 .260.1031501949, +FRA/ORCL/CON                                                 TROLFILE/current.256.103150194                                                 9

Shutdown the instance gracefully

1[oracle@orcl ~]$ srvctl stop database -d orcl

Login to asmcmd and copy the existing controlfile to a new location

[oracle@orcl ~]$ . oraenv
ORACLE_SID = [orcl] ? +ASM
ORACLE_BASE environment variable is not being set since thisinformation is not available for the current user ID oracle.You can set ORACLE_BASE manually if it is required.Resetting ORACLE_BASE to its previous value or ORACLE_HOMEThe Oracle base remains unchanged with value /oracle/base
[oracle@orcl ~]$ asmcmd -p
ASMCMD [+] > cd +DATA/ASMCMD
[+DATA] > ls ASM/ORCL/ASMCMD
[+DATA] > cd ORCL
ASMCMD [+DATA/ORCL] > cd CONTROLFILE
ASMCMD [+DATA/ORCL/CONTROLFILE] >ls
Current.260.1031501949ASMCMD
[+DATA/ORCL/CONTROLFILE] > cp Current.260.1031501949 /tmp/controlfile_2.ctl
copying +DATA/ORCL/CONTROLFILE/Current.260.1031501949 -> /tmp/controlfile_2.ctl

I copied the controlfile to filesystem

12[oracle@orcl tmp]$ lscontrolfile_2.ctl 

Set the location on the parameter by starting the database in nomount

12345678910111213141516SQL> startup nomount;ORACLE instance started.Total System Global Area 1140850688 bytesFixed Size                  2923584 bytesVariable Size             419431360 bytesDatabase Buffers          704643072 bytesRedo Buffers               13852672 bytesSQL> show parameter control_filesNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------control_files                        string      +DATA/ORCL/CONTROLFILE/current                                                 .260.1031501949, +FRA/ORCL/CON                                                 TROLFILE/current.256.103150194                                                 9

Add the new controlfile location in control_files

123SQL> alter system set control_files = '+DATA/ORCL/CONTROLFILE/current.260.1031501949','+FRA/ORCL/CONTROLFILE/current.256.1031501949','/tmp/controlfile_2.ctl' scope=spfile;System altered.
1234567891011121314SQL> startup;
ORACLE instance started.Total
System Global Area 1140850688 bytes
Fixed Size                  2923584 bytes
Variable Size             419431360 bytes
Database Buffers          704643072 bytes
Redo Buffers               13852672 bytes
ORA-00221: error on write to control file
ORA-00206: error in writing (block 1, # blocks 1) of control file
ORA-00202: control file: '/tmp/controlfile_2.ctl'
ORA-27041: unable to open fileLinux-x86_64 Error: 13: Permission denied
Additional information: 3

The database doesnot have appropriate permission to access the new controlfile

1[root@orcl tmp]# chmod -R 777 controlfile_2.ctl

Mount and open the database and check the parameter

SQL> alter database mount;
Database altered.
SQL> alter database open;
Database altered.
SQL> show parameter control_files
NAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------control_files                        string      +DATA/ORCL/CONTROLFILE/current                                                 .260.1031501949, +FRA/ORCL/CON                                                 TROLFILE/current.256.103150194                                                 9, /tmp/control
What is Oracle executable file in oracle home?

There is an essential file called oracle under $ORACLE_HOME/bin directory and did you wondered what it is or should really care about ?

oracle executable file is the core file which is used for instance startup which involve tasks like reading the pfile/spfile, start the background process, allocate shared memory segments, message queues, ipc semaphores etc

[oracle@hydrupgrd ~]$ ls -lrt /u01/app/oracle/product/12.1.0/dbhome_3/bin/oracle                                                                  -rwsr-s--x 1 oracle oinstall 323502056 Oct 11 23:10 /u01/app/oracle/product/12.1.0/dbhome_3/bin/oracle

This core file is dynamically linked to linux library /lib64/ld-linux-x86-64 which is further linked to /lib64/ld-linux-x86-64.so.2

[oracle@hydrupgrd ~]$ file /u01/app/oracle/product/12.1.0/dbhome_3/bin/oracle
/u01/app/oracle/product/12.1.0/dbhome_3/bin/oracle: setuid, setgid ELF 64-bit LSB executable, x86-64, version 1 (SYSV), dynamically linked, interpreter /lib64/ld-linux-x86-64.so.2, for GNU/Linux 3.2.0, BuildID[sha1]=d642d35b92c9cee20829e6f68185135da190b0ce, not stripped, too many notes (256)

[oracle@hydrupgrd ~]$ file /lib64/ld-linux-x86-64.so.2
/lib64/ld-linux-x86-64.so.2: symbolic link to ld-2.28.so

[oracle@hydrupgrd ~]$ file /usr/lib64/ld-2.28.so
/usr/lib64/ld-2.28.so: ELF 64-bit LSB shared object, x86-64, version 1 (GNU/Linux), dynamically linked, BuildID[sha1]=679c44309b3f64e06c53bcf145749f053c9c954a, with debug_info, not stripped

The size of file is 309 MB in 12c and inode number is 5864 in which the file block in stored in linux OS. This inode number always remains same even after a reboot for the file, since the file is physically stored in the same location. It is better to identify the processes using inode number. This file has 631840 blocks which is split into 154 small chunks of file.

[oracle@hydrupgrd ~]$ stat /u01/app/oracle/product/12.1.0/dbhome_3/bin/oracle
  File: /u01/app/oracle/product/12.1.0/dbhome_3/bin/oracle
  Size: 323502056       Blocks: 631840     IO Block: 4096   regular file
Device: 801h/2049d      Inode: 5864        Links: 1
Access: (6751/-rwsr-s--x)  Uid: (54321/  oracle)   Gid: (54321/oinstall)
Access: 2023-03-16 19:20:05.117993542 +0530
Modify: 2022-10-11 23:10:25.534872416 +0530
Change: 2022-10-11 23:10:25.566872414 +0530
 Birth: 2022-10-11 23:10:16.577872927 +0530

[oracle@hydrupgrd ~]$ ls -i /u01/app/oracle/product/12.1.0/dbhome_3/bin/oracle
5864 /u01/app/oracle/product/12.1.0/dbhome_3/bin/oracle

[oracle@hydrupgrd ~]$ du -sh /u01/app/oracle/product/12.1.0/dbhome_3/bin/oracle
309M    /u01/app/oracle/product/12.1.0/dbhome_3/bin/oracle

[oracle@hydrupgrd ~]$ stat -f /u01
  File: "/u01"
    ID: 80100000000 Namelen: 255     Type: xfs
Block size: 4096       Fundamental block size: 4096
Blocks: Total: 51175000   Free: 13226959   Available: 13226959
Inodes: Total: 102400000  Free: 102221455


[oracle@hydrupgrd ~]$ df -ih /u01
Filesystem     Inodes IUsed IFree IUse% Mounted on
/dev/sda1         98M  175K   98M    1% /u01

All of the background processes are dependent on the ‘oracle’ binary file to function. Cross verify with the oracle file location or the inode number to confirm the processes

[oracle@hydrupgrd ~]$ lsof |grep /u01/app/oracle/product/12.1.0/dbhome_3/bin/oracle|head
ora_pmon_  3984                         oracle  txt       REG                8,1   323502056       5864 /u01/app/oracle/product/12.1.0/dbhome_3/bin/oracle
ora_psp0_  3986                         oracle  txt       REG                8,1   323502056       5864 /u01/app/oracle/product/12.1.0/dbhome_3/bin/oracle
ora_psp0_  3986                         oracle   10r      REG                8,1   323502056       5864 /u01/app/oracle/product/12.1.0/dbhome_3/bin/oracle
ora_vktm_  3988                         oracle  txt       REG                8,1   323502056       5864 /u01/app/oracle/product/12.1.0/dbhome_3/bin/oracle
ora_gen0_  3992                         oracle  txt       REG                8,1   323502056       5864 /u01/app/oracle/product/12.1.0/dbhome_3/bin/oracle
ora_mman_  3994                         oracle  txt       REG                8,1   323502056       5864 /u01/app/oracle/product/12.1.0/dbhome_3/bin/oracle
ora_diag_  3998                         oracle  txt       REG                8,1   323502056       5864 /u01/app/oracle/product/12.1.0/dbhome_3/bin/oracle
ora_dbrm_  4000                         oracle  txt       REG                8,1   323502056       5864 /u01/app/oracle/product/12.1.0/dbhome_3/bin/oracle
ora_vkrm_  4002                         oracle  txt       REG                8,1   323502056       5864 /u01/app/oracle/product/12.1.0/dbhome_3/bin/oracle
ora_dia0_  4004                         oracle  txt       REG                8,1   323502056       5864 /u01/app/oracle/product/12.1.0/dbhome_3/bin/oracle

Narrow down the dependent processes which rely on ‘oracle’ binary file

[oracle@hydrupgrd ~]$ lsof |grep /u01/app/oracle/product/12.1.0/dbhome_3/bin/oracle|awk '{print $1}'|pr -tw132 -5
ora_pmon_                 ora_mmon_                 ora_p003_                 ora_p00e_                 ora_p00n_
ora_psp0_                 ora_mmnl_                 ora_p004_                 ora_p00e_                 ora_p00o_
ora_psp0_                 ora_d000_                 ora_p005_                 ora_p00f_                 ora_p00o_
ora_vktm_                 ora_s000_                 ora_p006_                 ora_p00f_                 ora_p00p_
ora_gen0_                 ora_p000_                 ora_p007_                 ora_p00g_                 ora_p00p_
ora_mman_                 ora_p001_                 ora_p008_                 ora_p00g_                 ora_p00q_
ora_diag_                 ora_tmon_                 ora_p009_                 ora_p00h_                 ora_p00q_
ora_dbrm_                 ora_arc0_                 ora_p00a_                 ora_p00h_                 ora_p00r_
ora_vkrm_                 ora_arc1_                 ora_p00b_                 ora_p00i_                 ora_p00r_
ora_dia0_                 ora_arc2_                 ora_cjq0_                 ora_p00i_                 ora_w005_
ora_dbw0_                 ora_arc3_                 ora_cjq0_                 ora_p00j_                 ora_w005_
ora_lgwr_                 ora_arc4_                 ora_qm02_                 ora_p00j_                 ora_w006_
ora_ckpt_                 ora_arc5_                 ora_q002_                 ora_p00k_                 ora_w006_
ora_lg00_                 ora_arc6_                 ora_q003_                 ora_p00k_                 ora_w007_
ora_smon_                 ora_arc7_                 oracle_13                 ora_p00l_                 ora_w007_
ora_lg01_                 ora_tt00_                 ora_p00c_                 ora_p00l_                 ora_w002_
ora_reco_                 ora_smco_                 ora_p00c_                 ora_p00m_                 ora_w002_
ora_lreg_                 ora_aqpc_                 ora_p00d_                 ora_p00m_                 ora_w001_
ora_pxmn_                 ora_p002_                 ora_p00d_                 ora_p00n_                 ora_w001_
ora_mmon_

Further, if the parent process is observed for the background process, it leads to root process

[oracle@hydrupgrd ~]$ ps -ef|egrep '3986|3984'|grep -v 'grep'
oracle      3984       1  0 14:52 ?        00:00:00 ora_pmon_hydrupgd
oracle      3986       1  0 14:52 ?        00:00:01 ora_psp0_hydrupgd

[oracle@hydrupgrd ~]$ ps -ef|grep 1
root           1       0  0 14:29 ?        00:00:02 /usr/lib/systemd/systemd --switched-root --system --deserialize 18
DATABASE ALSO NEEDS REST AT NIGHT TO WORK EFFICIENTLY

Similar to our subconscious mind which repair our body during sleep at night, oracle also needs a nightly window to subconsciously replenish itself to perform better.

Three predefined automated maintenance jobs are available for Oracle Database:

Automatic Optimizer Statistics Collection—Collects optimizer statistics for any database objects with a schema but no or outdated statistics. The SQL query optimizer uses the statistics acquired by this process to speed up the execution of SQL queries.

Automatic Segment Advisor: Detects segments with space that can be recovered and offers suggestions for defragmenting certain segments.

Automatic SQL Tuning Advisor: This tool analyses the performance of high-load SQL statements and offers tuning suggestions. This adviser can be set up to automatically apply SQL profile recommendations.

•Any sessions belonging to the consumer group SYS GROUP are given priority in this arrangement.

•Sessions generated by the user accounts SYS and SYSTEM are included in this group.

• Sessions from the other consumer groups and subplans in the plan then share any unused resource allocation that was made available to sessions in SYS GROUP.

•20 percent of that budget is allotted to user sessions, while 5 percent is used for maintenance chores. The ORA$AUTOTASK utilization ceiling is set at 90.

•As a result, even when the CPU is not in use, this group or plan cannot receive more than 90% of the CPU’s resources.

Consumer Group/subplanLevel 1Maximum Utilization Limit
ORA$AUTOTASK5%90
OTHER_GROUPS20%
SYS_GROUP75%

•There are seven predefined maintenance windows by default, each of which corresponds to a different day of the week.

•The SATURDAY WINDOW and SUNDAY WINDOW maintenance windows last longer than the weekday maintenance windows.

•These seven windows make up the window group MAINTENANCE WINDOW GROUP.

Window NameDescription
MONDAY_WINDOW10 pm to 2 am
TUESDAY_WINDOW10 pm to 2 am
WEDNESDAY_WINDOW10 pm to 2 am
THURSDAY_WINDOW10 pm to 2 am
FRIDAY_WINDOW10 pm to 2 am
SATURDAY_WINDOW6 am to 2am(20 hours window)
SUNDAY_WINDOW6 am to 2am(20 hours window)

Resource manager traces are immediately dumped after manual trigger of automatic jobs

*** 2023-02-03T06:17:14.523754+05:30 (CDB$ROOT(1))
kgsksysstop: blocking mode (2) timestamp: 174353116
kgsksysstop: successful; Total stall time = 0 ms.
kgsksysresume: successful
kgskreset: disabling CPURM: scheduled 0 vts
START RESOURCE MANAGER PLAN/CONSUMER GROUP DUMP
CDB ORA$INTERNAL_CDB_PLAN cpu_count=1 cpu_min=1.00
  PDB #1 ORA$AUTOTASK_PLAN dirtype=AUTO shrs=0
  PDB #1 ORA$ROOT_PLAN shrs=0
END RESOURCE MANAGER PLAN/CONSUMER GROUP DUMP

*** 2023-02-03T06:17:17.048790+05:30 (CDB$ROOT(1))
kgsksysstop: blocking mode (2) timestamp: 176878153
kgsksysstop: successful; Total stall time = 0 ms.
kgsksysresume: successful
kgskreset: disabling CPURM: scheduled 0 vts
START RESOURCE MANAGER PLAN/CONSUMER GROUP DUMP
CDB PE: PDB #2 PDB$SEED dirtype=DEF shrs=0
PDB #2 PDB$SEED/INTERNAL_PLAN cpu_min=1.00
END RESOURCE MANAGER PLAN/CONSUMER GROUP DUMP

*** 2023-02-03T06:19:18.634520+05:30 (CDB$ROOT(1))
s: cpu%      cputm       cpuwt     avgrun     avgwt       actps
s:   6         247           0       0.00      0.00           0
r:      < 5     < 10     < 50  < 100  < 200 < 1000   > 1K
r:        0        0        0      0      0      0      0

*** 2023-02-03T06:24:16.426569+05:30 (CDB$ROOT(1))
s:   6         251           0       0.00      0.00           0
r:        0        0        0      0      0      0      0

By default, all of the three jobs are enabled

kIsH@xyl>>SELECT CLIENT_NAME,OPERATION_NAME,TASK_NAME,STATUS from dba_autotask_task;

CLIENT_NAME          OPERATION_NAME       TASK_NAME            STATUS
-------------------- -------------------- -------------------- --------
sql tuning advisor   automatic sql tuning AUTO_SQL_TUNING_PROG ENABLED
                      task

auto optimizer stats auto optimizer stats gather_stats_prog    ENABLED
 collection           job

auto space advisor   auto space advisor j auto_space_advisor_p ENABLED
                     ob                   rog

To check the logs of the autotask jobs, the below query can be used. All autotask jobs has a prefix “ORA$AT_” which easily denote autotask (AT for autotask)

kIsH@xyl>>col LOG_DATE for a20
kIsH@xyl>>col OWNER for a20
kIsH@xyl>>col JOB_NAME for a20
kIsH@xyl>>SELECT log_id, to_char(log_date,'dd-mm-yy hh24:mi') log_date, owner, job_name FROM ALL_SCHEDULER_JOB_LOG WHERE job_name like '%ORA$AT_%';

    LOG_ID LOG_DATE             OWNER                JOB_NAME
---------- -------------------- -------------------- --------------------
       248 20-03-23 22:13       SYS                  ORA$AT_OS_OPT_SY_1
       282 20-03-23 22:13       SYS                  ORA$AT_OS_OPT_SY_1
       284 20-03-23 22:13       SYS                  ORA$AT_OS_OPT_SY_1
       232 20-03-23 22:00       SYS                  ORA$AT_OS_OPT_SY_1
       234 20-03-23 22:00       SYS                  ORA$AT_OS_OPT_SY_1
       236 20-03-23 22:00       SYS                  ORA$AT_OS_OPT_SY_1
       238 20-03-23 22:00       SYS                  ORA$AT_SA_SPC_SY_2
       240 20-03-23 22:00       SYS                  ORA$AT_SQ_SQL_SW_3
       242 20-03-23 22:00       SYS                  ORA$AT_OS_OPT_SY_1
       244 20-03-23 22:00       SYS                  ORA$AT_SA_SPC_SY_2
       246 20-03-23 22:00       SYS                  ORA$AT_SQ_SQL_SW_3
       252 20-03-23 22:00       SYS                  ORA$AT_SQ_SQL_SW_3
       256 20-03-23 22:00       SYS                  ORA$AT_SQ_SQL_SW_3
       258 20-03-23 22:00       SYS                  ORA$AT_SQ_SQL_SW_3
       250 20-03-23 22:00       SYS                  ORA$AT_SA_SPC_SY_2
       260 20-03-23 22:00       SYS                  ORA$AT_SA_SPC_SY_2
       262 20-03-23 22:00       SYS                  ORA$AT_SA_SPC_SY_2

17 rows selected.

If more details are required for the autotask jobs, the below query can help. There are three job prefix names. They are

  • ORA$AT_OS_OPT_SY_* – Optimizer stats advisor
  • ORA$AT_SQ_SQL_SW_* – SQL tuning advisor
  • ORA$AT_SA_SPC_SY_* – Space advisor
  • ORA$AUTOTASK_CLEAN – Clean up the obsolete autotask objects using ora$age_autotask_data

If you notice the scheduled maintenance jobs, they start at 22:00 at night.

kIsH@xyl>>
set lines 200 pages 1000
col LOG_DATE for a20
col OWNER for a10
col JOB_NAME for a20
col additional_info for a20
col operation for a15
SELECT log_id, to_char(log_date,'dd-mm-yy hh24:mi') log_date, owner, job_name,
  CASE WHEN job_name LIKE '%ORA$AT_OS_OPT_SY%'
           THEN 'Stat advisor'
           WHEN job_name LIKE '%ORA$AT_SQ_SQL_SW%'
           THEN 'SQL tuning advisor'
           WHEN job_name LIKE '%ORA$AT_SA_SPC_SY%'
           THEN 'Space advisor'
           END Job_desc,
           operation,additional_info
FROM ALL_SCHEDULER_JOB_LOG WHERE job_name like '%ORA$AT_%';kIsH@xyl>>kIsH@xyl>>kIsH@xyl>>kIsH@xyl>>kIsH@xyl>>kIsH@xyl>>kIsH@xyl>>  2    3    4    5    6    7    8    9   10

    LOG_ID LOG_DATE             OWNER      JOB_NAME             JOB_DESC           OPERATION       ADDITIONAL_INFO
---------- -------------------- ---------- -------------------- ------------------ --------------- --------------------
       248 20-03-23 22:13       SYS        ORA$AT_OS_OPT_SY_1   Stat advisor       RUN
       282 20-03-23 22:13       SYS        ORA$AT_OS_OPT_SY_1   Stat advisor       COMPLETED       REASON="Max runs rea
                                                                                                   ched"

       284 20-03-23 22:13       SYS        ORA$AT_OS_OPT_SY_1   Stat advisor       DROP            REASON="Auto drop jo
                                                                                                   b dropped", PROGRAM_
                                                                                                   NAME=""SYS"."GATHER_
                                                                                                   STATS_PROG"", JOB_TY

       232 20-03-23 22:00       SYS        ORA$AT_OS_OPT_SY_1   Stat advisor       UPDATE          STOP_ON_WINDOW_CLOSE
                                                                                                   ="TRUE", PREVIOUS_VA
                                                                                                   LUE="TRUE"

       234 20-03-23 22:00       SYS        ORA$AT_OS_OPT_SY_1   Stat advisor       UPDATE          USER_OPERATIONS_CALL
                                                                                                   BACK="DBMS_STATS.CLE
                                                                                                   ANUP_STATS_JOB_PROC"
                                                                                                   , PREVIOUS_VALUE="NU

       236 20-03-23 22:00       SYS        ORA$AT_OS_OPT_SY_1   Stat advisor       UPDATE          USER_CALLBACK_CONTEX
                                                                                                   T="1", PREVIOUS_VALU
                                                                                                   E="NULL"

       238 20-03-23 22:00       SYS        ORA$AT_SA_SPC_SY_2   Space advisor      UPDATE          STOP_ON_WINDOW_CLOSE
                                                                                                   ="TRUE", PREVIOUS_VA
                                                                                                   LUE="TRUE"

       240 20-03-23 22:00       SYS        ORA$AT_SQ_SQL_SW_3   SQL tuning advisor UPDATE          STOP_ON_WINDOW_CLOSE
                                                                                                   ="TRUE", PREVIOUS_VA
                                                                                                   LUE="TRUE"

To manually trigger the autotask, use the below procedure.

kIsH@xyl>>EXEC DBMS_AUTO_TASK_IMMEDIATE.GATHER_OPTIMIZER_STATS;

PL/SQL procedure successfully completed.

Usually, these scheduled maintenance window can be extended as per the size of the database and resource availability in the system.

If day to day DML transactions are quite high and more resources are available, then give power to oracle to complete the mandatory jobs with in less time or increase the maintenance time. In order for the jobs to be successful without failure, then follow the below guidelines,

  • Create a baseline for job performance
  • Increase the duration of maintenance hours
  • Improve performance of jobs using parallel and concurrent resources
  • Configure resource groups to distribute the resource to prevent over or under allocation to specific job
  • Identify the cause of slowness for any bugs, recent data load or internal query using suboptimal plans

kIsH@xyl>>show parameter job_queue

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     20

kIsH@xyl>>show parameter statistics_level

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
client_statistics_level              string      TYPICAL
statistics_level                     string      TYPICAL

To trace the auto task jobs, use event 27402

SQL> alter session set tracefile_identifier='schedtrc';

Session altered.

SQL> alter system set events '27402 trace name context forever, level 1023';

System altered.
Math-box in python

This tool performs basic maths calculations using tkinter

Square, Cube, Factors, Prime, Multiplication table

https://github.com/kishan0426/Math-box

VERIFY IF PARAMETER IS STATIC OR DYNAMIC

These columns determine the control of initialization parameters that can be modified at session or system and static or dynamic -> isses_modifiable, issys_modifiable

kIsH@STAX<>SELECT name,isses_modifiable,issys_modifiable from v$parameter where name like '%sga_max%';

NAME                                                                             ISSES ISSYS_MOD
-------------------------------------------------------------------------------- ----- ---------
sga_max_size                                                                     FALSE FALSE

kIsH@STAX<>SELECT name,isses_modifiable,issys_modifiable from v$parameter where name like '%sga_target%';

NAME                                                                             ISSES ISSYS_MOD
-------------------------------------------------------------------------------- ----- ---------
sga_target                                                                       FALSE IMMEDIATE

Example of parameter modification scope.

kIsH@STAX<>ALTER SYSTEM SET sga_max_size=3000M; --scope=spfile
ALTER SYSTEM SET sga_max_size=3000M
                 *
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified


kIsH@STAX<>ALTER SYSTEM SET sga_target=2304M;  --scope=memory or spfile or both

System altered.

kIsH@STAX<>ALTER SESSION SET sga_target=2304M; --Not modifiable at session level
ALTER SESSION SET sga_target=2304M
                  *
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this option