Wednesday, 16 July 2014

GLOBAL NAME summary

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:
  • DB_NAME
  • DB_DOMAIN
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

SQL> SELECT * FROM props$ WHERE name='GLOBAL_DB_NAME';

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

SQL> SELECT * FROM global_name;

GLOBAL_NAME
---------------------------------------------
EXAMPLE

GLOBAL NAME change

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]';
    COMMIT;
    
    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; AskTom.oracle.com) 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 'orcl.world';

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

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

Monday, 7 July 2014

Locating data files on standby

Problem

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
    select 
      '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
    select 
      '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 http://dataworks-inc.com/import-a-subset-of-table-columns-using-oracle-data-pump, 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
dumpfile=REQ_N2521_01.DMP 
logfile=REQ_N2521_01.log 
 
tables= 
[some tables]
 
query= 
[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.

Thursday, 5 June 2014

Mysterious problem with archivelog replication

The starting points to this short story are the following:
  • we have a system with active-passive configuration
  • lately one of us made a failover to another node
  • the configuration actually not changed and used for some time - anyway we checked it few times and there were no errors
  • the standby was recreated
  • local application of archivelogs on a standby was successful

The problem was the primary database did not send the archivelogs to the standby.
The entry in V$ARCHIVE_DEST_STATUS indicated wrong unique name in the configuration. Not sure if recall correctly, but I think it was ORA-16053 listed there. We checked the configuration at least few times by few pairs of eyes and not spotted anything wrong.
There was hanging one of ARCH processes on the primary, which tried to send an archivelog from before the failover incarnation, so we suspected it may be it, but killing it did not change anything.

However even though the real cause stays mysterious, the solution has been quite obvious and strightforward. A collegue of mine configured another archive destination - we disabled number 2 and enabled number 3 - the system return to work properly.

Thursday, 29 May 2014

Zabbix 2.2.3 and ZBX-7373


Migration from 2.0.2 to 2.2.3

Lately we have upgraded the Zabbix installation from version 2.0.2 to 2.2.3. The installation run quite smoothly (some minor issues were all bound with our own optimizations) - we did it in around half an hour. So far so good, but when we launch it, we face the high IO load and growing at the steady pace until the whole system was actually out of service.

Diagnostics

As we have already upgraded the database, the return to the previous version would take us several hours, during which we would be quite blind in regard to production operations. So we started to look for the reasons.
  • in the Oracle db there is a concept of services. A service is a name registered on listener and used for identification of a particular database instance. For version 2.0.2 we run the system on 2 service names: zbx_gui and zbx_server. This is easy to do for Zabbix as it actually consists of few components among which there is gui, server and others.
    We spotted that all the IO intensive operations came from the gui part of Zabbix. So we disabled the gui to confirm this observation. The whole system immediately return to proper work. I may add that it does even better than with previous version and assume this is due to use of bind variables - it takes less CPU (though enabling CURSOR_SHARING to FORCE)
  • hopefully the problem has been located in the Zabbix part, which is done in PHP, so the fix is not so troublesome as in case of the part in C (no recompilation, etc). From the monitoring we knew that the most troublesome query was the one, which we later found in the api part. It is run for history tables, which are the biggest entities in the Zabbix database.
    class CHistoryManager {
    
     /**
      * Returns the last $limit history objects for 
      * the given items.
      *
      * @param array $items  an array of items with 
      * the 'itemid' and 'value_type' properties
      * @param int $limit
      *
      * @return array    an array with items IDs as keys 
      * and arrays of history objects as values
      */
     public function getLast(array $items, $limit = 1) {
      $rs = array();
      foreach ($items as $item) {
       $table = self::getTableName($item['value_type']);
       $query = DBselect(
        'SELECT *'.
        ' FROM '.$table.' h'.
        ' WHERE h.itemid='.zbx_dbstr($item['itemid']).
        ' ORDER BY h.clock DESC',
        $limit
       );
       while ($history = DBfetch($query)) {
        $rs[$history['itemid']][] = $history;
       }
      }
    
      return $rs;
     }
    

    My colleagues have found also that similar bag was registered under the ZBX-7373 codename in the Zabbix bug repository.
  • we started to look into details of this query in order to find how to improve it for better performance and here the details of the analysis:
    • we checked the following query
      select * from 
        (select * from ZABBIX.HISTORY H where H.ITEMID=:Z 
           order by H.CLOCK desc) 
      where rownum<2;
      
    • here is the plan for the query
      Plan hash value: 168015457
       OPERATION   OBJ NAME   COST   CR BUFFER GETS   PARTITION START   PARTITION STOP 
      SELECT STATEMENT
         64694       
           COUNT
           10049     
                Filter Predicates 
                   ROWNUM<:SYS_B_0 
                PARTITION RANGE 
         64694   10049   1048575   1 
                    VIEW 
         64694   10049     
                        SORT 
         64694   10049     
                            Filter Predicates 
                                ROWNUM<:SYS_B_0 
                            TABLE ACCESS 
       HISTORY   64277   10049   1048575   1 
                                INDEX 
       P_HISTORY_1   433   70   1048575   1 
                                    Access Predicates 
                                        H.ITEMID=TO_NUMBER(:Z) 

      Predicate Information (identified by operation id):

      1 - filter(ROWNUM<2)
      4 - filter(ROWNUM<2)
      6 - access("H"."ITEMID"=TO_NUMBER(:Z))

Solution

  • we assumed the query simply takes to much blocks, even when optimized with the index range scan. The solution is to limit clock values from the bottom. From start we tried the fixed value of 2 days, but this was both inflexible and wrong in certain cases. Finally we go with the limit which is based on the delay value from the items table.
    Here goes the query in final shape:
    select * from (
    select * from ZABBIX.HISTORY H where H.ITEMID=:Z 
    and CLOCK>(cast((systimestamp at time zone 'UTC') as date)
      -TO_DATE('1970-01-01', 'yyyy-mm-dd'))*86400
        -1*(select delay from ZABBIX.ITEMS where ITEMID=:Z)
    order by H.CLOCK desc
    ) where rownum<2;
    
    -- update 2014-05-30
    -- the query above does not take into consideration items with delay equal to 0
    -- a possible solution to this issue below
    -- Please keep in mind, the query is not thought to service every scenario - e.g. 
    -- the asynchronous
    select * from (
    select * from ZABBIX.HISTORY H where H.ITEMID=:Z 
    and CLOCK>(cast((systimestamp at time zone 'UTC') as date)
      -TO_DATE('1970-01-01', 'yyyy-mm-dd'))*86400
        -(select 
          case 
            -- complex schedule
            when type=0 and delay=0 then 1*50
            -- no schedule, async 
            when type=2 and delay=0 then 3*86400
            else 1*delay
          end delay from ZABBIX.ITEMS where ITEMID=:Z)
    order by H.CLOCK desc
    ) where rownum<2;
    
  • here goes the plan for the final query:

    Plan hash value: 1183558203
     OPERATION   OBJ NAME   COST   CR BUFFER GETS   PARTITION START   PARTITION STOP 
     SELECT STATEMENT 
       609       
          COUNT 
         8     
              Filter Predicates 
                  ROWNUM<:SYS_B_5 
              PARTITION RANGE 
       609   8   1048575   KEY 
                  VIEW 
       609   5     
                      SORT 
       609   5     
                          Filter Predicates 
                              ROWNUM<:SYS_B_5 
                          TABLE ACCESS 
     HISTORY   606   5   1048575   KEY 
                              INDEX 
     P_HISTORY_1   31   4   1048575   KEY 
                                  Access Predicates 
                                      AND 
                                          H.ITEMID=TO_NUMBER(:Z) 
                                          CLOCK>(CAST(SYSTIMESTAMP(6) AT TIME ZONE 'UTC' AS date)-TO_DATE(:SYS_B_1,:SYS_B_2))*:SYS_B_3-:SYS_B_4* 
                                  TABLE ACCESS 
     ITEMS   2   3     
                                      INDEX 
     SYS_C0018758   1   2     
                                          Access Predicates 
                                              ITEMID=TO_NUMBER(:Z) 

    Predicate Information (identified by operation id):

    1 - filter(ROWNUM<2)
    4 - filter(ROWNUM<2)
    6 - access("H"."ITEMID"=TO_NUMBER(:Z)
      AND "CLOCK">(CAST(SYSTIMESTAMP(6) AT TIME ZONE 'UTC' AS date)
      -TO_DATE(' 1970-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))*86400
      -1* (SELECT "DELAY" FROM "ZABBIX"."ITEMS" "ITEMS" WHERE "ITEMID"=TO_NUMBER(:Z)))
    8 - access("ITEMID"=TO_NUMBER(:Z))

  • as the last argument let me provide a short example: for a randomly chosen item the unoptimized query takes 10049 consistent gets and next executions do not change anything, while the optimized query takes for the first time 158 consistent gets and in next executions only 11 per execution.
  • The solution presented here is universal assuming You use the Oracle RDBMS. To get a solution, which would be portable between databases one have to calculate the current unix timestamp in PHP and embed it into the query instead of the part "(cast((systimestamp at time zone 'UTC') as date) - TO_DATE('1970-01-01', 'yyyy-mm-dd'))*86400". The database have to support subqueries (if not the solution would be to call first for the delay value). At the time of upgrade there was no solution to the ZBX-7373, but now it was included into versions 2.3.1 and 2.2.1rc1. Anyway I belive our solution with subquery is more elegant to the one included in this bug.