another oracle dba blog

  • 16.03.2023

    389ds alias plugin

    kdpv

    The dereference of aliases is unfortunately not supported by the 389ds ldap server. Therefore I’ve created a small plugin which resolves aliases during base search. Subtree and onelevel searches are not supported.

    It is here

  • 19.01.2023

    ORA-13516 on applying Oracle 19 PSU

    kdpv

    Hit the ORA-13516 error during datapatch during latest (19.18 JAN23) PSU installation.

    datapatch throws this:

    Patch 34765931 apply: WITH ERRORS
  logfile: /oracle/cfgtoollogs/sqlpatch/34765931/25078403/34765931_apply.log (errors)
  -> Error at line 9395: script rdbms/admin/backport_files/bug_33527739_apply.sql
      - ORA-13516: AWR Operation failed: CATPROC not valid
      - ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 328
      - ORA-06512: at "SYS.DBMS_WORKLOAD_REPOSITORY", line 355
      - ORA-06512: at line 12

    Usually we patch the ORACLE_HOME, then start the DB in upgrade mode. After this we execute datapatch -verbose to patch DB dictionary, and finally restart DB in open mode. Well, not anymore. Since 19.12 (JUL21) both DB and OJVM patches don’t require upgrade mode.

    After restarting DB in open mode, the datapatch has sucseeded.

    Looks like it makes sense to check PSU’s readme from time to time …

  • 17.02.2022

    Datapatch hangs on "Gathering database info..."

    kdpv Hi.

    Hit this issue on the one Windows server. The datapatch command freezes completely after displaying “Gathering database info…” prompt like this

    D:\oracle\ora193\opatch> datapatch -verbose
SQL Patching tool version 19.12.0.0.0 Production on Tue Aug 31 14:10:50 2021
Copyright (c) 2012, 2021, Oracle. All rights reserved.

Log file for this invocation: D:\oracle\cfgtoollogs\...\sqlpatch_invocation.log

Connecting to database...OK
Gathering database info...

    The problem is that the datapatch uses Windows internal type command to create temporary files. It is defined in the %ORACLE_HOME%\rdbms\admin\catcon.pm file in the sub os_dependent_stuff:

    my $WindowsDoneCmd = "\nhost type nul > ";

    It turns out that on the server there was another type.exe file from UnxUtils in the PATH. So the datapatch has used it instead of command processor’s internal type.

    That’s why things went wrong: the datapatch couldn’t create the file and waits forever on the following line (sub exec_DB_script):

    select (undef, undef, undef, 0.01) until (-e $DoneFile);

    After removing the third-party type.exe from the PATH, the datapatch works fine again.

    I have no idea why Oracle decided to use external utilities to create files while sqlplus can create files with its own SPOOL command just fine…

    Hope it helps!

    UPD:

    1. To debug datapatch you can use the -debug switch, but it doesn’t help much. Setting the environment variable “CATCON_DEBUG” to “true” enables debug mode in the catcon.pm and it is much more helpful.

    2. You can use where command to find files in the PATH:

       C:\>where type
 c:\Program Files\bin\type.exe

 C:\>del "c:\Program Files\bin\type.exe"

 C:\>where type
 INFO: Could not find files for the given pattern(s).
  • 07.03.2019

    expdp query with both single and double quotes

    kdpv

    To export or import a subset of data, Datapump utilities use the QUERY clause to define the subset. Recently i need to export partial data from a table with a lower case column names and the filter has to be defined on a date column. After multiple attempts of trying to escape quotes with backslash character and receiving a lot of error messages like

    ORA-39001: invalid argument value
ORA-39035: Data filter SUBQUERY has already been specified.

ORA-31655: no data or metadata objects selected for job

ORA-31693: Table data object XXX failed to load/unload and is being skipped due to error:
ORA-01858: a non-numeric character was found where a numeric was expected

LRM-00111: no closing quote for value
LRM-00113: error when processing file 'parfile.par'

UDE-00014: invalid value for parameter, 'query'.

    i have found the solution - you need to double the single quotes:

    QUERY='"lowercase_table":where "lowercase_date_column" <= DATE''2019-01-01'''

    The whole QUERY clause is surrounded by single quotes, the table name and the column name are surrounded by double quotes, and finally the DATE literal is surrounded by double single quotes. This way it works correctly.

    Hope it helps!

  • 18.10.2018

    Oracle 12.2 Standard Edition Standby with (almost) Real Time Apply

    pdb refresh mode clause

    Wow!

  • 04.10.2018

    How to transfer files to or from oracle directory

    kdpv

    Sometimes you may need to copy files from or to the Oracle directory. It is easy when you have direct access to the database server’s file system. It is a little bit tricky when you don’t (AWS RDS instance for example). One way to accomplish this is to create database link between the existing database (the one you have access to) and the target db and use DBMS_FILE_TRANSFER package to copy files between instances.

    I. powershell+odac: ora_dir_transfer.ps1

    ora_dir_transfer.ps1 is another solution which doesn’t require the second database. It is a powershell script which uses ODAC to access the database and UTL_FILE package to read/write files on the database server. It is influenced by this perl script. You may need to fix Oracle dll path on the “[Reflection.Assembly]::LoadFile” line.

    II. sqlcl+javascript (download only): sqlcl_ora_dir_download.js

    The sqlcl is the Oracle’s sqlplus written in java, therefore it supports a bunch of platforms. It also runs javascript natively, so it is possible to create quite powerfull scripts. The sqlcl_ora_dir_download.js script works in a download-mode only, but it doesn’t require the UTL_FILE’s grant.