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.

Monday, 26 March 2018

kkzifr3g: Encountered error ORA-12057.

The solution is as follows:
-- as we initially do not know what is happening exactly
alter system set events '12057 trace name errorstack level 3';
-- and wait until hit the issue again (note the command above dumps only 1st occurrence in a session)

[.. this is from alert.log - this time much more info ..]
Mon Mar 26 10:16:01 2018
kkzifr3g: Encountered error ORA-12057.
Mon Mar 26 10:16:01 2018
Dumping diagnostic data in directory=[cdmp_20180326101601], requested by (instance=1, osid=60555319 (J002)), summary=[abnormal process termination].
Mon Mar 26 10:16:02 2018
Errors in file /u01/app/oracle/diag/rdbms/db1/db1/trace/db1_j005_24117326.trc:
ORA-12057: materialized view or zonemap "TEST"."MV_DATA1" is invalid and must use complete refresh

-- and even more data in trace, but we care here mostly about the mv name
-- then ORA-12057 During Fast Refresh of Aggregate Materialized View (Doc ID 1399651.1)

-- to clean up
alter system set events '12057 trace name context off';

Thursday, 22 March 2018

Fractured block found during backing up datafile

{hostname}, Hex dump of (file 75, block 87980) in trace file /oracle/diag/rdbms/db1/db1/trace/db1_ora_23614.trc
Corrupt block relative dba: 0x12c157ac (file 75, block 87980)
Fractured block found during backing up datafile
Data in bad block:
 type: 6 format: 2 rdba: 0x12c157ac
 last change scn: 0x02d7.7dc0c4bc seq: 0x2 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xb1a20602
 check value in block header: 0xc892
 computed block checksum: 0x67fa
Reread of blocknum=87980, file=+DATA/db1/datafile/file1.343.956407117. found valid data : 

dbv file=+DATA/db1/datafile/file1.343.956407117 blocksize=8192 start=87980 end=87981 userid=sys/blbla

DBVERIFY: Release - Production on Thu Mar 22 10:04:33 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = +DATA/db1/datafile/file1.343.956407117

DBVERIFY - Verification complete

Total Pages Examined         : 2
Total Pages Processed (Data) : 0
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 2
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 0
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 0
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 0 (0.0)

Monday, 5 March 2018

Restricted session

-- at startup
startup restrict;

-- on running instance
alter system enable restricted session;

System altered.

-- to disable
alter system disable restricted session;

-- to check
select logins from v$instance;