Monday, 20 October 2014

ORA-600 [ktbdchk1: bad dscn]

This weekend I was responsible for a switchover of one of our databases. All went fine until the rman backup check - I discovered then that again I hit the bug described here. As I knew it already, I was confident I will soon go to sleep, but then I once again looked at the alert log and found there
ORA-00600: internal error code, arguments: [ktbdchk1: bad dscn], [], [], [], [], [], [], [], [], [], [], []

This is a database on version, which is very important here. On first sight I have found that exists on the Metalink a bug (Bug 8895202: ORA-1555 / ORA-600 [ktbdchk1: bad dscn] ORA-600 [2663] in Physical Standby after switchover (Doc ID 1608167.1)), which happens after switchover, but version covers also a patch for this one. And there are no other descriptions for that version, so there are only some suggestions, what to do to make the possible bug finding easier.
However when I read everything I have found that the fix is disabled by default. So I enabled it according to the article above and even though I was not sure if this is it (the trace messages were different than the ones provided in Metalink), it helped.
Please keep in mind the fix may blow up some queries, so one have to enable also another parameter (Bug 13513004 - ORA-600 [ktbgcl1_KTUCLOMINSCN_1] on blocks fixed up by 8895202 (Doc ID 13513004.8)).

Friday, 26 September 2014

TCP connections and ORA-30678

Just a while ago we had an issue with ORA-30678 on one of test databases.


The scenario looks like the following:
  • there exists a script, which reacts for certain events by connecting to the database and calling a procedure, which among other things sends also a notification email
  • the script keeps persistent connection to the database
  • at some occasion the script was received wrong parameters
  • it make a call to procedure, which opened SMTP connection
  • due to wrong parameter value the procedure threw an exception, which apparently was catched at some level (may be on the db level, but I suppose it simply returned to the script level, which ended a loop turn and started another one with the advent of a new triggering event)
  • it seems that there is a built-in limit set to 16 TCP connections per session in Oracle - apparently 15 more event loop turns were performed
  • at every next loop turn the ORA-30678 was threw

What was wrong?

Apparently the code was wrong. It worked well, but only for good parameters. The lack was exception handling, which have to close any TCP connection at the end of run no matter the communication outcome. Of course good programmer will log the exception circumstances, etc., but the cleanup is crucial here. It is important for all the packages, which use TCP connections: UTL_SMTP, UTL_HTTP, UTL_TCP

Wednesday, 24 September 2014

ACLs modification

In fact this is a very tiny observation.
When one deals with DBMS_NETWORK_ACL_ADMIN it is necessary to call COMMIT to confirm the changes - rather obvious, but sometimes some things end with implicit commit.

Wednesday, 16 July 2014


I have always wrongly understood this stuff with global names. Even if dig something up, shortly after the use I forget. So here is a summary.

GLOBAL NAME definition

It is fully taken from the Oracle documentation (Understanding How Global Database Names Are Formed).
A global database name is formed from two components: a database name and a domain. The database name and the domain name are determined by the following initialization parameters at database creation:
The DB_DOMAIN initialization parameter is only important at database creation time when it is used, together with the DB_NAME parameter, to form the database global name. At this point, the database global name is stored in the data dictionary. You must change the global name using an ALTER DATABASE statement, not by altering the DB_DOMAIN parameter in the initialization parameter file. It is good practice, however, to change the DB_DOMAIN parameter to reflect the change in the domain name before the next database startup.
The place, where the GLOBAL NAME is stored is the table SYS.PROP$. You may also display it with a call to the view called GLOBAL_NAME (there is also a global synonym to this view with the same name).
One more note - in commands specific for db link name (like CREATE DATABASE LINK, ALTER DATABASE RENAME GLOBAL_NAME, etc. where the db link name is used as identifier and not as the value one should use double quotes. However surprisingly a little the name is case-insensitive i.e. one may use "ORCL.WORLD@ANOTHER_USER" as well as "orcl.WORLD@ANOTHER_USER" - the call will work


NAME            VALUE$   COMMENT$
--------------- -------- --------------------
GLOBAL_DB_NAME  EXAMPLE  Global database name

SQL> SELECT * FROM global_name;



I spotted 2 ways to achieve it (DbForums and others):
  1. ALTER DATABASE RENAME GLOBAL_NAME TO "[new global name]";
    IIRC this way has some limitations - e.g. if domain was once defined, it always will be added, thus no way to remove it completely
  2. because this parameter is stored in a table, it is possible to update it with simple SQL
    UPDATE sys.prop$ SET value$='[new global name]' WHERE name='GLOBAL_DB_NAME';
    -- update on view also works IIRC
    -- UPDATE global_name SET global_name='[new global name]';
    This way we may set the GLOBAL NAME to anything we want. However this is messing with the db dictionary, so a little frightening and there is a Metalink note id 1018063.102, when the change hangs. In short when one once starts to update the GLOBAL NAME manually, have to do it this way further.

GLOBAL_NAMES parameter

The GLOBAL_NAMES parameter enforces naming db links as GLOBAL NAME of source databases. The parameter is of Boolean type and may be set globally or in a session.

With the GLOBAL_NAMES enabled one may create many connections to the same database (for example differing in credentials; by using @ sign, e.g.
-- most probably You need here double quotes around the db link name
-- at least I have got ORA-00933: SQL command not properly ended if not use them
create database link "ORCL.WORLD@SCOTT_USER"
connect to scott identified by tiger
using '';

create database link "ORCL.WORLD@ANOTHER_USER"
connect to another identified by lion
using '';

select 1 from dual@"ORCL.WORLD@ANOTHER_USER";

Monday, 7 July 2014

Locating data files on standby


The db_create_file_dest parameter indicates the default directory in which an Oracle RDBMS instance creates new files according to the OMF rules.
The scenario considered is this:
  • we want to add new files
  • despite the default location we have also additional one built upon lesser storage and designated for archive purposes
  • there is also standby configured also with OMF, and file paths differ between both instances
The problem is how to place files in the archival location on standby with least effort?

Solution 1

One simply:
  • adds data files on the primary with explicit specification of the file path - the files are added on primary to the right location, on the standby to the default one (instead of the archivel one)
  • lists recently added files identifiers - this may be a mundane task especially if one adds data files to already existing tablespaces or is unable to provide simple condition on tablespace_names - in general this may be done on the dba_data_files or v$datafile view.
  • next one creates a script with move operation
    -- first part is to move data files at the OS level
      'mv '||name||' [archival path]'||substr(name, instr(name, '/', -1))||';' bash1 
    from v$datafile where file# in ([ids list]);
    -- second part is to rename files at the db level
      'alter database rename datafile '''||name||''' to ''[archival path]'||substr(name, instr(name, '/', -1))||''';' sql1 
    from v$datafile where file# in ([ids list]);
    The best way of course is to format and spool the answers of those queries to scripts (or one script)
  • now one have to switch off the managed standby mode (alter database recover managed standby cancel;)
  • the next step is to switch standby_file_management to MANUAL (alter system set standby_file_management='MANUAL';)
  • further one have to perform the core commands - run scripts generated earlier and move files to the final location and rename the paths at the database level
  • the last steps are switching to AUTO mode again (alter system set standby_file_management='AUTO';) and starting the managed replication (recover managed standby database using current logfile disconnect)

Solution 2

I suppose this one is much more clever than the first. On the standby one simply changes temporarily the default OMF path to the archival location - of course there are elements we have no influence on like db_unique_name or datafile literal within the path, but these are of small importance and we may be prepared for it, if we assume earlier such scenario. Then it is enough to add data files - the standby will automatically place them into the location of choice - of course we have to be sure, no one adds other files at the moment. After the operation it is enough to set db_crate_file_dest back to previous setting.

Thursday, 26 June 2014

How to handle a subset of data with Data Pump (external link)

Lately I found a great article in the theme, which nicely fulfils a common sense ways like creating mviews (or even views when 12c is in context) or load firstly to temporary tables and then copy to or exchange with the target objects.

Friday, 13 June 2014

Fun with parameter file and VIM

Here small anecdote rather than something dealing with technical problems.
I had created a parameter file in VIM (which is important) with a QUERY parameter specified. Nothing fancy. I opened ready file in one VIM and copied to another
[some tables]
[some limits]
I've run it and got:
ORA-39001: invalid argument value
ORA-39035: Data filter SUBQUERY has already been specified.
and started to look for error. And did it for some time. Initially without success - it is simple, right?

The reason was the addition of tilda characters at the end of file - because this way are represented non existent lines in the VIM if the file content is shorter than the terminal, I did not spot it at once, while had copied them beforehand unintentionally to the new file - thus it seemed perfectly normal.