Friday, 13 April 2018

ORA-12154 while duplicating from active database on RAC

A colleague of mine tried to rebuild a standby from some primary database. He prepared, added proper entries to tnsnames.ora on both clusters (primary and standby), then fired duplicate command. When it came to
restore clone from service  'ORCLPRIM' standby controlfile;
the command failed with:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 04/13/2018 12:54:04
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-17629: Cannot connect to the remote database server
ORA-17627: ORA-12154: TNS:could not resolve the connect identifier specified
ORA-17629: Cannot connect to the remote database server
We checked tnsnames.ora files, connections 'standby to primary' and 'primary to standby' - all seemed ok.

Sometimes it is some 'invisible' character in tnsnames.ora, which gets in the way, sometimes simple lack of definition. But not this time, so this case is a bit more interesting...
The grid binaries and database binaries have different homes here and keep different tnsnames.ora files (as there is no shared TNS_ADMIN location).
And the solution was to add the required TNS entries also to the grid home.
My supposition is as follows:
  • the point where the command is issued is the standby instance, to which the script connects through statically registered interface.
  • the listener is fired from the grid home (and not db), so its environment is derived from grid, even though in static registration definition the db home is pointed as ORACLE_HOME
  • thus addition of TNS definitions to the grid home tnsnames.ora helped
  • I 've found also that for some static registration entries there was added additional clause ENVS (disclaimer: this parameter in not supported on Windows, any process started by the listener will simply inherit the listener's environment) in the form
    (SID_DESC =
          (SID_NAME = orclstb1)
          (ORACLE_HOME = [db home])
          (GLOBAL_DBNAME = orclstb_dgmgrl)
          (ENVS = "TNS_ADMIN=[db home]/network/admin") 
    )
    
    so seems my supposition is correct and this way is another way to get around this issue

Monday, 9 April 2018

Some issue with external table over preprocessor

I built an external table based on the output from a preprocessor. Got the following explanation, why it does not work:
      Trim whitespace same as SQL Loader
KUP-04027: file name check failed: /u01/app/oracle/nx/datamasking/nx_masking.dat
KUP-04017: OS message: Error 0
KUP-04118: operation "lstat64", location "skudmicdl"
All seemed to be ok. The script returned the output when called manually. Returned value was 0.
So what was wrong? The file I called was a link to the real script in fact. Once I dropped the link and copied the script source to the file with name referred within database, the problem vanished.