[Oracle] How-To

8 thoughts on “[Oracle] How-To”

• Olivier says:

  25/09/2017 at 09:56

  Start from a PFILE or SPFILE :

  startup pfile='C:\ora\pfile\init.ora';
  
  startup spfile='C:\ora\pfile\spinit.ora';
  

  Create SPFile from PFile (and opposite)

  create spfile from pfile='C:\ora\pfile\init.ora'
  
  create pfile from spfile='C:\ora\pfile\spinit.ora'
  

  Reply
• Olivier says:

  28/03/2018 at 14:18

  To retrieve current file type :

  SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type" FROM sys.v_$parameter WHERE name = 'spfile';

  To retrieve the current spfile

  show parameter spfile;

  To retrieve the current pfile

  show parameter pfile;

  Reply
• Olivier says:

  19/11/2018 at 10:00

  Error while starting TNS listener :

  Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DB11G2.localdomain)(PORT=1521)))
  Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
  TNS-12555: TNS:permission denied
   TNS-12560: TNS:protocol adapter error
    TNS-00525: Insufficient privilege for operation
     Linux Error: 1: Operation not permitted
  

  Solution :
  Clean/remove the folder /var/tmp/.oracle

  mkdir /var/tmp/.oracle
  chmod 01777 /var/tmp/.oracle
  chown root /var/tmp/.oracle
  chgrp root /var/tmp/.oracle
  

  Reply
• OLIVIER COMBE says:

  23/09/2020 at 14:37

  Error 73 with Opatch :

  OPatch failed with error code 73
  
  Following active executables are not used by opatch process :
  /data/app/oracle/product/19.3.0.0/lib/libclntsh.so.19.1
  

  Some processes used by OPatch are still running and must be stopped

  Find process with fuser

  [oracle@gc12c 17027533]$ fuser /u01/app/oracle/product/12.1.0/dbhome_1/lib/libclntsh.so.12.1
  /u01/app/oracle/product/12.1.0/dbhome_1/lib/libclntsh.so.12.1:  4063m
  

  Find more details

  [oracle@gc12c 17027533]$ ps up 4063
  USER       PID %CPU %MEM    VSZ   RSS TTY      STAT START   TIME COMMAND
  oracle    4063  0.0  0.2  81256 11400 pts/2    S+   11:41   0:02 adrci
  

  Reply
• OLIVIER COMBE says:

  23/09/2020 at 14:45

  Config events :

  ALTER SYSTEM SET event="trace[AQ_MC] disk highest" SCOPE=SPFILE;
  

  ALTER SYSTEM SET EVENT="trace[AQ_MC] disk highest","trace[AQ_PT] disk highest" SCOPE=SPFILE;
  

  Reply
• OLIVIER COMBE says:

  28/09/2020 at 08:36

  OPatch error :

  fuser could not be located
  

  Following are the options to resolve this issue.

  Pass argument for OPatch to ignore fuser and continue with patching.
  Setting this variable to “true” informs the OPatch to skip the check for active executables
  1. Set the environment variable OPATCH_NO_FUSER=true
  2. Shut down the WebLogic instances
  3. Run the OPatch utility

  Set temporary fuser
  (1) Set /tmp in your PATH
  (2) Create an empty file named “fuser”
  (3) Shut down the WebLogic instances
  (4) Run the OPatch utility

  Install fuser utility
  (1) Install fuser utility on the machine (yum install psmisc)
  (2) Make sure fuser is located under “/sbin/fuser” or “/bin/fuser”
  (3) Shut down the WebLogic instances
  (4) Run the OPatch utility

  Reply
• OLIVIER COMBE says:

  28/10/2020 at 10:23

  STREAMS_POOL_SIZE

  Set :

  ALTER SYSTEM SET STREAMS_POOL_SIZE=5G SCOPE=BOTH
  

  Get :

  show parameter STREAMS_POOL_SIZE
  

  Reply
• OLIVIER COMBE says:

  25/02/2021 at 11:17

  See current process/session/transaction usage :

  select resource_name,current_utilization, MAX_UTILIZATION, LIMIT_VALUE from v$resource_limit
  where resource_name in ('processes', 'sessions', 'transactions')
  

  Reply