ora error - liamlamth/blog GitHub Wiki

• autotask issue:

  -- checking
  select WINDOW_GROUP_NAME, ENABLED, NEXT_START_DATE from dba_scheduler_window_groups;
  select client_name, status from dba_autotask_client;
  
  -- this drops the maintenance window, it will give some errors that can be ignored.
  @?/rdbms/admin/catnomwn.sql
  -- Drop the windows manually:
  execute dbms_scheduler.drop_window('MONDAY_WINDOW');
  execute dbms_scheduler.drop_window('TUESDAY_WINDOW');
  execute dbms_scheduler.drop_window('WEDNESDAY_WINDOW');
  execute dbms_scheduler.drop_window('THURSDAY_WINDOW');
  execute dbms_scheduler.drop_window('FRIDAY_WINDOW');
  execute dbms_scheduler.drop_window('SATURDAY_WINDOW');
  execute dbms_scheduler.drop_window('SUNDAY_WINDOW');
  
  EXEC DBMS_AUTO_TASK_ADMIN.ENABLE(client_name=>'sql tuning advisor', operation=>NULL, window_name=>NULL);
  EXEC DBMS_AUTO_TASK_ADMIN.ENABLE(client_name=>'auto space advisor', operation=>NULL, window_name=>NULL);
  EXEC DBMS_AUTO_TASK_ADMIN.ENABLE(client_name=>'auto optimizer stats collection', operation=>NULL, window_name=>NULL);
  -- this recreates them
  @?/rdbms/admin/catmwin.sql
  

• scheduler issue: show parameter job_queue_processes, should not be 0

• few failed objects are normal, may be due to some disabled feature such as dataguard, in-memory, status "new" pdb,
  check dba_optstat_operation_tasks for details

  select target, start_time, end_time, notes,status 
  from DBA_OPTSTAT_OPERATION_TASKS 
  where status <> 'COMPLETED' 
  order by target, start_time;
  

[root@myserver ~]# df -h /dev/shm
Filesystem               Size  Used Avail Use% Mounted on
tmpfs                    126G     0  126G   0% /dev/shm
[root@myserver ~]# mount -t tmpfs tmpfs -o size=240g /dev/shm                               ### maximum = real memory + swap, but here use 95% of real memory
[root@myserver ~]# vi /etc/fstab
    tmpfs                   /dev/shm                tmpfs   size=140G        0 0

[oracle@myserver ~]# vi $ORACLE_HOME/network/admin/sqlnet.ora
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
SQLNET.ALLOWED_LOGON_VERSION_SERVER = 8

[oracle@myserver ~]# lsnrctl reload
[oracle@myserver ~]# sqlplus / as sysdba
SQL> alter user USERNAME identified by PASSWORD;

• it could be random packet corruption issue. scan virus in client if possible

[root@myserver ~]# systemctl stop firewalld                                                               ###in db server
[root@myserver ~]# systemctl disable firewalld

Unable to obtain current patch information due to error: 20001, ORA-20001: Latest xml inventory is not loaded into table
ORA-06512: at "SYS.DBMS_QOPATCH", line 2327
ORA-06512: at "SYS.DBMS_QOPATCH", line 854
ORA-06512: at "SYS.DBMS_QOPATCH", line 937
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.DBMS_QOPATCH", line 932
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-29400: data cartridge error
KUP-04095: preprocessor command /u01/app/oracle/product/19.0.0/dbhome_1/QOpatch/qopiprep.bat encountered error "locale: Cannot set LC_CTYPE to default locale: No such file or directory
locale: Cannot set LC_MESSAGES to default locale: No such file or directory
locale: Can"
ORA-06512: at "SYS.DBMS_QOPATCH", line 919
ORA-06512: at "SYS.DBMS_QOPATCH", line 2286
ORA-06512: at "SYS.DBMS_QOPATCH", line 817
ORA-06512: at "SYS.DBMS_QOPATCH", line 2309

solution:

[oracle@myserver ~] locale                                     ### check LANG
[root@myserver ~] yum list | grep langpacks                    ### install missing langpacks being used under oracle user
[root@myserver ~] yum install langpacks-zh_TW.noarch

ORA-00603: ORACLE server session terminated by fatal error
ORA-27504: IPC error creating OSD context
ORA-27300: OS system dependent operation:sendmsg failed with status: 105
ORA-27301: OS failure message: No buffer space available
ORA-27302: failure occurred at: sskgxpsnd2

[root@myserver ~]# ifconfig lo                                                                            ###check mtu
[root@myserver ~]# ifconfig lo mtu 16436                                                                  ###short change until restart: lower to 16436 (static)
or [root@myserver ~]# vi /etc/sysconfig/network-scripts/ifcfg-lo                                          ###permanent change
   MTU=16436
   [root@myserver ~]# systemctl restart network.service                                                   ###if not exist, may be NetworkManager.service

[root@myserver ~]# total=$(awk '/MemTotal:(.*)/{print $2 }' /proc/meminfo)
[root@myserver ~]# numa_count=$(lscpu | awk '/^NUMA node\(s\)(.*)/{print $3 }')
[root@myserver ~]# min_free_kb=$(expr $total / 250 \* $numa_count) # this is the 
[root@myserver ~]# echo "vm.min_free_kbytes=$min_free_kb" > /etc/sysctl.d/99-vm_min_free_kbytes.conf

[root@myserver ~]# sysctl --system                                                                        ###reload, not affect existing connection
[root@myserver ~]# sysctl vm.min_free_kbytes                                                              ###verify