Remigium

Analyzing chained and migrated rows

2013-04-16T08:59:00.002+02:00

Two articles from the renowned authors:

Analyze-this by J. Lewis
Shortcut:

-- gathering statistics populates among others user_tables.chain_cnt
analyze table [tbl] compute statistics for table;
-- check gathered data
-- delete statistics 
-- otherwise the optimizer will use the chain_cnt to modify 
-- the cost of indexed access to the table
analyze table [tbl] delete statistics; 
exec dbms_stats.gather_table_stats([owner], [tbl]);

Detect-chained-and-migrated-rows-in-oracle by T.Poder
Excerpt:
One way to estimate the impact of chained rows is to just look into the "table fetch continued row" statistic - one can run query/workload and measure this metric from v$sesstat (with snapper for example). And one more way to estimate the total number of chained pieces would be to run something like SELECT /*+ FULL(t) */ MIN(last_col) FROM t and see how much the "table fetch continued row" metric increases throughout the full table scan. The last_col would be the (physical) last column of the table. Note that if a wide row is chained into let's say 4 pieces, then you'd see the metric increase by 3 for a row where 4th row piece had to be fetched.

Duplicating database manually on the same host

2013-04-12T13:09:00.001+02:00

The assumption is to create a copy of the database on the same host in order to run 2 independent database instances. It can be done in many ways:

with RMAN
with expdp and standard dump
with expdp and transportable tablespaces
with cp command and dbnewid tool

I present here the last way.

shutdown immediate;




shutdown immediate --shutdown cleanly a database to be copied
change the database directory name to something different - it is needed in order to preserve original datafiles, when we would change their paths. By default the Oracle RDBMS deletes original data files when they exist under both paths (ie. old one and the new one; we use OMF) - by directory change we prevent this behaviour - for example new directory name would be TEST.ORIG. After whole operation we return back the original name.
copy all the datafiles, logfiles and controlfiles to a new directory with cp command - for example cp -r TEST.ORIG TES2
create new init.ora with new path to the controlfiles, new unique name and other paths with would conflict with   - for example create pfile='inittes2.ora' from spfile
startup nomount - fix possible errors
mount the database - fix possible errors
change the data files paths - select 'alter database rename file '''||name||''' to '''||regexp_replace(name, '', '')||''';' ddl1 from v$datafile union all select 'alter database rename file '''||name||''' to '''||regexp_replace(name, '', '')||''';' ddl1 from v$tempfile union all select 'alter database rename file '''||member||''' to '''||regexp_replace(member, '', '')||''';' ddl1 from v$logfile
shutdown immediate
nid target=sys dbname=cd2; ensure open_cursors are more then all files of the database
change parameter file db_name to new name
startup mount
alter database open resetlogs
restore old files to the old path and startup the original database

Summarizing there are only 2 things, which one must care about - implicit removal of database files in old location (which must be prevented; not sure if this happens without OMF) and the open_cursors parameter, which must be set higher than the number of database files (not sure but better to count temporary and log files as well).

Login issues

2013-04-12T12:03:00.003+02:00

The 11g version brought some changes to the DEFAULT profile. Previously the FAILED_LOGIN_ATTEMPTS parameter was always set to UNLIMITED and now it is now set to some value, which means schema locking after this value of failed logins will be crossed over.

I must say I am puzzled with this. In general I understand the reason behind the FAILED_LOGIN_ATTEMPTS - it is against password breaking brute force attacks. On the other hand it means that some 'lost' application host with wrong password becomes a point of a DoS attack. Which is better (or worse) hard to tell.
Usually a database is located after some firewall (or two) as this is quite deep layer in the application stack. I usually meet with databases, where the schemas are application schemas, so exists a client application interface between a human and a database - passwords are encoded in the application configuration and direct access to a database itself is strictly limited. On the other hand there are users (though not numerous), who are allowed to make a direct connection and among them may be hidden a 'malicious' one.

So, how do I imagine to deal with the configuration?
I believe for application it is better to create another profile, which keeps the FAILED_LOGIN_ATTEMPTS parameter to UNLIMITED, because it is not so rare that there exists some forgotten application or script, which would block the schema and thus practically disables the application. Of course there is a monitoring system, but usually the delay in information feedback to human is ~5 minutes, there come another issues (multiple application hosts, and only one of them with wrong password; few applications sharing the same schema; scripts run from cron on different shell accounts; etc) and we get a noticeable delay in application work, where possible it was meant to work in 24x7 mode. And this may happen quite frequently and there is no need for malice.
Further it would be reasonable to move direct users to another databases and possibly connect them through mix of additional schemas and/or database links, so that they would not be able to connect directly to the database with application schemas.

The drawbacks?

the human users have got performance penalty, if connected through another database or may try to break passwords if there will be no such prevention measures - so if it is a database with plenty of direct human users then this would not be so great idea
11g: Multiple failed login attempt can block new application connections - shortly the 11g version has additional security feature against brute force attacks - if set to UNLIMITED there is enabled a delay when returning error message due to failed login attempt after first few attepmts. Due to the bug 7715339 such delayed session keeps library cache lock for prolonged period (due to enabled delay) and new sessions wait on this lock till the number hits sessions/processes ceiling. It is possible to disable the delay feature with event='28401 trace name context forever, level 1'

An issue with the UTL_FILE and file permissions

2013-01-23T09:32:00.003+01:00

Configuration in which only local user is able to write to indicated directory through UTL_FILE running sqlplus, and for any remote access or access by other users, the code will not work properly Some time ago we met with a little weird behavior of a test database. The code in a package did some processing and wrote to specified directory. However it worked only if the user was logged on the database host as him. Calling the code remotely or locally from a different user (also the database owner) ended always with error about wrong operation on file (the writes were issued through UTL_FILE).
The os directory, which was aliased in the database, had mask 0777, so though not owned by oracle, should be accessible by the database.

The explanation is the following:
While the aliased directory was accessible to anybody, the parent directory to it (ie. home directory of the user) was accessible only to him and his group. Thus if any user tried to use the code, the database can not have written to the destination as it requires at least rX permissions on every directory level. To make the code run was to add the oracle user to the local user group or change permissions on the local user home or anything like that.
But why the local user was able to perform the code successfully? This is bound with the way the connection to the database is handed to a user client. When it is done through listener simplifying it prepares a process/dispatcher, then forwards the connection information to the client. If dealing with connection locally it is done differently - the database process becomes a child of the local user session and inherits user' permissions and thus is able to write to the otherwise inaccessible file.

Conditions in UPDATE statements

2013-01-23T09:07:00.000+01:00

This is kind of a fix to common developer misuse. I noticed that sometimes see the solution for searching more complex constructs as this:

[..] where f1||f2||f3 in (select f1||f2||f3 from t1)

While in SELECT this is simply weird, because one may simply join 2 tables and specify conditions with f1=f1 etc., it may be considered when we play with DML. Of course this means the optimizer can not use indexes (unless there is some rather complex index on expression), but developers forget about very easy construct:

[..] where (f1, f2, f3) in (select f1, f2, f3 from t1)

Now plans look much better and we still may create condition on several fields at once.

Confusion at partition maintenance

2013-01-23T08:54:00.000+01:00

Here an anecdote rather than a hint, but sometimes helps ;-). Once I have read about our "built-in" inability to find own errors and this is exactly about such case.

Lately I tried to add a new partition for 2013 year to some table. It was subpartitioned, so the syntax little more complex (below - please note, this is with error).

alter table schema1.tab1
add partition p1301 VALUES less than (201302) TABLESPACE tbs1 (
SUBPARTITION p1301_1 VALUES(1), 
SUBPARTITION p1301_2 VALUES(2),
SUBPARTITION p1301_3 VALUES(3), 
SUBPARTITION p1301_4 VALUES(4)));

I constructed the statement and tried to execute. But on and on I have got the same error:

ORA-14048: a partition maintenance operation may not be combined with other operations

. And it was not as complex as I may miss an additional operation. So I stuck. After several minutes I ask a colleague of mine for help. He came to me and I have explained him the issue. At the same moment I realize what was wrong - additional parenthesis.
If it would be missing, the error message would be correct

ORA-00907: missing right parenthesis

but with additional parenthesis the statement syntax suggests to the RDBMS something completely different, thus returning more enigmatic error, which in turn puzzled the RDBMS user - in this case me.

Some notes on materialized views

2013-01-23T08:32:00.001+01:00

Few loosely notes on materialized views.

It is possible to build a mview on a pre-built table. I like this feature mainly because one is able to drop such view and recreate it with slightly changed definition, while the data is preserved, which is very valuable especially when dealing with large source tables.

Another observation is that sometimes the casting to right types is necessary. We met several times the situation when on Linux some mview definition worked properly while on AIX it failed due to not strong type casting, when the solution was to use CAST with precision to type and length (usually the problem core was longer VARCHAR2 than expected).

NoCOUG internal magazine

2012-12-10T11:24:00.002+01:00

NoCOUG internal magazine at http://www.nocoug.org/Journal/NoCOUG_Journal_YYYYMM.pdf, where YYYY - year, MM - one of 02,05,08,11

Duplicating for standby

2012-11-20T12:10:00.000+01:00

On the start I have a target database configured with OMF and specified unique db name. On the standby side I created an instance, started it and that's it. Oh, yeah - I copied password file from primary side to be able to connect to standby and configured listener with GLOBAL_DBNAME, which allows for registering a service for use in tnsnames.ora on both hosts. Of course I specified proper entries in tnsnames.ora as well.
There are possible many configurations for this duplication - combinations of a target database, a standby database and a catalog - of course a standby one is a must, but the rest is optional and may be avoided, although it complicates the matter a little. I choose to use additionally to standby database also active target database. This avoids the trouble of applying all the redo logs from last backup.

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE;

First failure: the command failed with ORA-17628: Oracle error 19505 returned by remote Oracle server on primary and ORA-19505: failed to identify file "/oracle/oradata/ZABBIX/controlfile/o1_mf_855ys7j8_.ctl"
ORA-27040: file create error, unable to create file as I did not create path for controlfile (so now mkdir -p /oracle/oradata/ZABBIX/controlfile/o1_mf_855ys7j8_.ctl).

Second start and failure:

RMAN-03002: failure of Duplicate Db command at 11/13/2012 10:14:19
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
ORA-06550: line 1, column 17:
PLS-00201: identifier 'DBMS_RCVCAT.GETDBID' must be declared
ORA-06550: line 1, column 7:

I found at http://blog.csdn.net/tianlesoftware/article/details/6232292 that it is due to not specifying nocatalog when connecting with rman. And that really helped.
Third try and this time I moved much ahead - I was wrong with assuming the online logs will be created according to the db_create_file_dest (so I should set db_create_online_log_dest_n at least to overcome this). However this time duplication is going forward - the data files are created according to the plan.
Because copy is done with backup as copy reuse datafile X auxiliary format new then no useless copying to storage local to primary and all bytes goes directly to the standby files, which is nice

To finish the standby I need now to add standby log files (though this is optional) and set log_archive_dest_state_2 to enable. The managed standby is already run by the duplicate command. One may want to switch this to recovery using current logfile.

Distributed transactions holding locks on some resources

2012-11-20T12:06:00.000+01:00

One of the main problems with XA transactions seems to be locking of rows by distributed transaction. While this is perfectly normal, when locking lasts a short time, it becomes a nightmare when it lasts forever. There are few scenarios, which may end up with such long-lasting lock.
Many comprehensive articles may be found on Metalink:

Solving locking problems in a XA environment [ID 1248848.1] - short, thorough and I like it
Manually Resolving In-Doubt Transactions: Different Scenarios [ID 126069.1] - long and boring

In many scenarios the issue should resolve automatically. If this is not the case then theoretically the distributed transaction should be recognized by the resource manager as 'in-doubt' and show up in the dataset from the dba_2pc_pending view. If so then a DBA should settle with a transaction manager administrator if the transaction 'in-doubt' should be commited or rollbacked and then on the resource manager side the DBA should call appropriately commit or rollback with force option and local transaction id as an argument. Sometimes additionally one needs to call DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('[LOCAL_TRAN_ID]');
Even easier such situation may be resolved from XA client side - we may prepare a fake client, which binds to the database as a side of the chosen distributed transaction and commit or rollback it according to the needs, while whole the cleaning will be done by standard mechanisms (which is recommended way of dealing with such issues) - such example client code may be obtained from the first mentioned article.
Yet another way of resolving the issue is the database reboot (but this is not always possible).

The distributed transaction goes through few phases.
When in 1st phase - not yet prepared - the locks are seen in v$lock view and diagnostic information is easy to obtain.
After prepare call and response those locks are no longer seen in v$lock and then the diagnostics is more hard. Yet another possibility here is the transaction process on the transaction manager side was killed by the system - then we end up with an active transaction still blocking some resources, but there is no locks seen (there is however entry in the v$locked_object view, so at least we may find objects on which there are some locked rows) and no session attached.
In this scenario we may use the excellent view from Tanel Poder changing the equal join to a left join between sys.x$k2gte2 and v$session - from this we may see that some transactions in x$k2gte2 have no corresponding row from the v$session view - these are our candidates for forced transaction finish - in x$k2gte2 we may find the global id of the transaction 'in-doubt' and the local id can be constructed from the join with sys.x$ktcxb (x$k2gte2.k2gtdxcb=x$ktcxb.ktcxbxba) on columns kxidusn, kxidslt, kxidsqn (it seems no need for left join here) so we may do something about it.
Once there was a workaround published on Metalink, which involved an insert into pending_trans$ and pending_session$, but I could not find it lately - so I suppose they recommend rather the use of fake client here. However one may still read about it after some google search - voila http://tazlambert.wordpress.com/2011/11/24/ora-01591-lock-held-by-in-doubt-distributed-transaction-string/ (look at the end of the article).
As a kind of curiosity I may add that I found even a x$k2gte2 entry, which had no corresponding entry in the sys.x$ktuxe structure or the v$transaction view. In sys.x$ktcxb there still was an entry however(that is why I assumed no left join needed here), but all the parts of local transaction id were equal to 0, so I suppose this trash has no impact on the database activity and the only way to clean it is a reboot.

XA on Oracle database

2012-11-20T09:10:00.000+01:00

The Oracle RDBMS is XA compliant from scratch - no adjustments needed - http://www.orafaq.com/wiki/XA_FAQ is a good starting point on this subject.

One may create additional views for dealing with troublesome situations. The Oracle views v$XATRANS$ and V$PENDING_XATRANS$ may be created in order to help with diagnostics (they are created by running @?/rdbms/admin/xaview.sql).
Another very good view is accessible at http://blog.tanelpoder.com/files/scripts/gtrans.sql. I usually change the join between sys.x$k2gte2 and v$session into LEFT JOIN as still it is possible to have on a database active distributed transactions not connected to any session.

IMPDP and lacking internal import structures

2012-11-14T14:27:00.000+01:00

There are number of problems with importing data with impdp depending on the content parameter setting during export. According to few articles on Metalink this is mostly due to the fact, that during import there are created implicitly some structures and this creation failed. Today I just hit something like this. I exported some schemas with content=data_only. When importing I hit:

ORA-39034: Table TABLE_DATA:"TEST"."SCHEDULER$_JOB_ARG" does not exist.
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.UPATE_TD_ROW_IMP [62] 
TABLE_DATA:"TEST"."SCHEDULER$_JOB_ARG"
ORA-31603: object "SCHEDULER$_JOB_ARG" of type TABLE not found in schema "TEST"

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.KUPW$WORKER", line 8364

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0xb7f251c0     19208  package body SYS.KUPW$WORKER
0xb7f251c0      8385  package body SYS.KUPW$WORKER
0xb7f251c0     18770  package body SYS.KUPW$WORKER
0xb7f251c0      4226  package body SYS.KUPW$WORKER
0xb7f251c0      9082  package body SYS.KUPW$WORKER
0xb7f251c0      1688  package body SYS.KUPW$WORKER
0xb791dd40         2  anonymous block

To workaround this I assumed if I create this lacking table I'll be able to go further. I run as sys

create table "TEST"."SCHEDULER$_JOB_ARG" as select * from SCHEDULER$_JOB_ARGUMENT where rownum<1 pre="">
and run import again.
One step forward.
I hit then:
ORA-39034: Table TABLE_DATA:"TEST"."SCHEDULER$_PROGRAM_ARG" does not exist.
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.UPATE_TD_ROW_IMP [62] 
TABLE_DATA:"TEST"."SCHEDULER$_PROGRAM_ARG"
ORA-31603: object "SCHEDULER$_PROGRAM_ARG" of type TABLE not found in schema "TEST"

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.KUPW$WORKER", line 8364

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0xb7f251c0     19208  package body SYS.KUPW$WORKER
0xb7f251c0      8385  package body SYS.KUPW$WORKER
0xb7f251c0     18770  package body SYS.KUPW$WORKER
0xb7f251c0      4226  package body SYS.KUPW$WORKER
0xb7f251c0      9082  package body SYS.KUPW$WORKER
0xb7f251c0      1688  package body SYS.KUPW$WORKER
0xa9d3b2b8         2  anonymous block

Per analogiam I run:
create table "TEST"."SCHEDULER$_PROGRAM_ARG" as select * from SCHEDULER$_PROGRAM_ARGUMENT where rownum<1 pre="">
Next step forward.
Now I hit
ORA-39034: Table TABLE_DATA:"TEST2"."RE$ACTION_IMP_TAB" does not exist.
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.UPATE_TD_ROW_IMP [62] 
TABLE_DATA:"TEST2"."RE$ACTION_IMP_TAB"
ORA-31603: object "RE$ACTION_IMP_TAB" of type TABLE not found in schema "TEST2"

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.KUPW$WORKER", line 8364

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0xb7f251c0     19208  package body SYS.KUPW$WORKER
0xb7f251c0      8385  package body SYS.KUPW$WORKER
0xb7f251c0     18770  package body SYS.KUPW$WORKER
0xb7f251c0      4226  package body SYS.KUPW$WORKER
0xb7f251c0      9082  package body SYS.KUPW$WORKER
0xb7f251c0      1688  package body SYS.KUPW$WORKER
0xb2aae570         2  anonymous block

Now I have no idea where I may find RE$ACTION_IMP_TAB structure, so I wished myself the structure is not important and it is enough to get the properly named object, so I simply created whatever.
create table "TEST2"."RE$ACTION_IMP_TAB" as select * from dual where rownum<1;
The load goes forward - I assume it should be ok. If I import jobs or chains, it would fail, but jobs or chains are a part of metadata, so problem solved.

Of course the best way is to dump with content=ALL (ie. default value), but sometimes one forgets or has no such choice - then the workaround would be useful.

And short update - the manually created objects have to be dropped manually as well.

Zabbix on Oracle - few notes

2012-10-29T11:25:00.001+01:00

We migrated Zabbix from MySQL to Oracle.
At the same time we moved to partitioning, here excellent article how to do it on MySQL. On Oracle we partitioned the same tables, created the same indexes, but go with interval partitioning (it is nice to not have to create new partitions manually or program it) based upon weeks (the partitioning key is of NUMBER type and we need an equal interval value for every period, so month is out due to variable number of days).

Almost no problems with upgrade to 2.0.2. We had to change the database however due to national character set - must be UTF-8, while we had previously fixed UTF16 - zabbix on Oracle is based mainly upon NVARCHAR2 type, and upgrade sets some columns to width bigger than 2000.
It is worth to enable CURSOR_SHARING to FORCE (actually I would say this is inevitable).
At the same time I would not recommend to use any automatic memory management (even ASMM). May be this is only our case, but frequently we run into ORA-4031 problems, which eventually ended with instance hangover or crash. As soon as I disabled any automatic memory management completely, the problems gone.

In addition to partition maintenance here there is an action for job, which will drop the HISTORY% tables' old partitions:

 begin
  for cmd in (
    with 
    conf as (select 9+1 part_num_left from dual)
    select 'alter table '||p.table_name||' drop partition '||p.partition_name||' update global indexes'  ddl1 
    from 
      user_tab_partitions p, 
      (select tp.table_name, count(*) l_pnum 
        from user_tab_partitions tp 
        where tp.table_name like 'HISTORY%' 
        group by tp.table_name 
        having count(*)>(select part_num_left from conf)) t
    where 1=1
      and p.table_name=t.table_name 
      and p.table_name like 'HISTORY%' 
      and p.partition_name<>'DONT_DELETE' 
      and p.partition_position between 2 and (t.l_pnum-(select part_num_left from conf)) 
    order by p.table_name, p.partition_position)
  loop
    execute immediate cmd.ddl1;
  end loop;
end;

The partitions labeled DONT_DELETE are partitions on the first position of relevant tables. The label is due to the fact one can not drop the first partition from a table partitioned by interval. It is comfortable to create those partitions before the time we want to store in table in order to keep it empty. The code above drops partitions leaving last n ones (here 9 + DONT_DELETE). The line with condition on DONT_DELETE partition name is actually redundant as the code starts dropping from second partition.

Moving subpartitions

2012-09-25T12:59:00.000+02:00

In Oracle database MOVE operations are done on the segment level actually. Thus while simple table has its own segment, the partitioned one has none all the segments are bound with partitions. In case of subpartitions the same rule apply. So we move:

ALTER TABLE simple_t MOVE TABLESPACE tblspace1;
ALTER TABLE partitioned_t MOVE PARTITION p1  TABLESPACE tblspace1;
ALTER TABLE subpartitioned_t MOVE SUBPARTITION s1 TABLESPACE tblspace1;

When we move all the segments with must however change the default tablespace value on the table level and possibly on the partition level if it has a different value.

ALTER TABLE partitioned_t MODIFY DEFAULT ATTRIBUTES TABLESPACE tblspace1;
ALTER TABLE subpartitioned_t MODIFY DEFAULT ATTRIBUTES FOR PARTITION p1 TABLESPACE tblspace1;

ALTER TABLE t1 MODIFY DEFAULT ATTRIBUTES... is less known, but can be quite useful allowing for switching on and off Advanced Compression and some other segment parameters.

This applies at least to the version 11.2 (do not check other docs).

Problems with connection

2012-08-17T14:58:00.002+02:00

From time to time there happens different problems with connections to Oracle database. Below little report about those I have met.

problems on client
Usually they are trivial and bound with tnsnames.ora. Wrong PATH variable (for example few ORACLE_HOME/bin entries), wrong LD_LIBRARY_PATH, entries in the tnsnames.ora with some unusual blank character, wrong syntax in this file, wrong service_name. For example today 2 different tnsnames.ora files, while part of programs uses the right one, and some the wrong one.
While trivial, sometimes hard to diagnose, especially by phone.

Fortunately it is easy to get very detailed trace. We need in sqlnet.ora few entries:
```
TRACE_LEVEL_CLIENT=16 #or SUPPORT
TRACE_FILE_CLIENT=mytrace #useful for identification
TRACE_DIRECTORY_CLIENT=traces #useful for location
```
The output is quite detailed and usually helpful in the diagnosis.
problems on cman
Of course if one uses it (we do). I have met 3 major problems with it (and this is relevant for 11g version).

The first one was with the raise of 11g version. The cman needs one rule to connect to itself. Till the 10g it was for example:
```
(RULE=(SRC=10.0.50.10)(DST=127.0.0.1)(SRV=cmon)(ACT=accept))
```
With 11g it must be (at least on Linux):
```
(RULE=(SRC=10.0.50.10)(DST=::1)(SRV=cmon)(ACT=accept))
```
The Metalink (ID 298916.1) still provides address 127.0.0.1 as a right one, and indeed the cman starts, but one can not connect to it with cmctl. Possibly it works, but one can not control it. I always needed to kill the cman process with kill utility to change anything (and no, we did not use it on production before the problem was solved).

The second problem was with some older RDBMS versions (ie. before 10g) - we needed to add to simple rule some settings, for example:
```
(RULE=(SRC=10.0.50.12)(DST=10.0.60.11)(SRV=*)(ACT=accept)(ACTION_LIST=(mct=0)))
```
Usually it is enough to end with ACT attribute. This particular case with RDBMS servers on Windows kind os.

The third one was with error in syntax. Once we loose somewhere in the middle of cman.ora a parenthesis. The cman kept all the rules from the time before error emergence, thus in the rules there were rules located before the point of error as well as those after it. When we added rules before that point and reloaded, everything was fine, if we added after, the rules were ignored. Because we always reloaded, the cman worked and for some time we were unaware of the problem at all.
The true incident we met, when were trying to restart the cman. It went down and we can not start it up. IIRC the returned error was about problem in the configuration file in the end of it (which of course was due to mess in parentheses). The solution here was to display the config with VIM and color the syntax. Eventually we found the place for lacking parenthesis. The problem was trivial, but not so obvious.

To those three I add yet another - in our case it was lack of mapping IP->hostname in /etc/hosts, possibly similar situation if no proper name in DNS service available (if used instead of /etc/hosts mapping). In result the cman service returned ORA-12529, even though the cman.ora configuration does not use host names at all (all rules with IP).
The same happens when the entry in /etc/hosts differs from DST hostname.

problems on server
Both problems on server had something to do with firewalls.
Once it was MTU parameter, which default value appeared too large after some firewall software update.
The other problem was with inactivity timeout. Some of our firewalls disconnect connections, on which there is no traffic during specified time. A solution here seems to be enabling dead connection detection (DCD) feature. This feature implementation is based upon sending for time to time some bytes as a heartbeat - thus there is some traffic on a connection, so it is not broken.

Automation while suspending the db work

2012-08-08T11:37:00.000+02:00

From some time the Oracle RDBMS supports a situation when due to some work lacks the space within db tablespaces. The AFTER SUSPEND trigger is called, so there is a place for kind of callback, one may set the RESUMABLE_TIMEOUT parameter or there is the DBA_RESUMABLE table with entries inserted after supend events.

If I would imagine itself the best way to solve the suspend event it would be to run within the AFTER SUSPEND trigger a code which would add a new file to the tablespace, on which there is a lack of place. There are was even examples with such solution for 9i version, but it is not now at least with 11.2 version (not sure about 10g). So what one may do?

I meet this problem mostly on test/dev environments, so the solution do not need to be a complex one and I do not need to think about any special cases - all added files are of UNLIMITED size, so always the solution is to add a new file (unless the storage is fully used).

I have met with a solution based upon the AFTER SUSPEND trigger. In 11.2 we get ORA-30511 if trying to run some DDL (and adding a file is of such type). The practical usage now I see in the following - setting RESUMABLE_TIMEOUT to some higher value in order to allow a DBA to make a room for the operation in suspend state or/and inform such DBA by SMS or an alert generation
use DBMS_SCHEDULER job to run from time to time a piece of code, which would add a new file

In my case the second solution has much more sense and is a real automation. Because we mostly use Data Pump utilities to load test/dev databases, it usually enables 2h long suspend (or so) on the operation. In such case the best way is to create a procedure like this:

CREATE OR REPLACE
PROCEDURE expand_resumable_tblspace
AS
  l_tblspace VARCHAR2(80);
  l_count pls_integer := 0;
BEGIN
  SELECT
    COUNT(*)
  INTO
    l_count
  FROM
    dba_resumable
  WHERE
    1               =1
  AND SUSPEND_TIME IS NOT NULL
  AND resume_time  IS NULL
  AND status        ='SUSPENDED' ;
  IF l_count        > 0 THEN
    SELECT
      SUBSTR(regexp_substr(ERROR_MSG, 'tablespace [^[:blank:]]*', 1, 1, 'im'),
      12)
    INTO
      l_tblspace
    FROM
      dba_resumable
    WHERE
      1                       =1
    AND SUSPEND_TIME         IS NOT NULL
    AND resume_time          IS NULL
    AND status                ='SUSPENDED'
    AND rownum                <2 ;
    IF SUBSTR(l_tblspace,1,4)!='TEMP' THEN
      EXECUTE immediate 'alter tablespace '||l_tblspace||
      ' add datafile size 1m autoextend on next 8m';
    ELSE
      EXECUTE immediate 'alter tablespace '||l_tblspace||
      ' add tempfile size 128m autoextend on next 128m';
    END IF;
  END IF;
EXCEPTION
WHEN OTHERS THEN
  SYS.DBMS_SYSTEM.KSDDDT;
  SYS.DBMS_SYSTEM.KSDWRT(2, SQLERRM);
  SYS.DBMS_SYSTEM.KSDFLS;
END;
/

and to run a job every let's say 5 minutes, which would call such procedure.

Why take only one row from DBA_RESUMABLE? I assume there is not too much such events at a moment and the second one would be serviced with next job run.

Why cut a tablespace name from ERROR_MSG and not from ERROR_PARAMETERx? I found that in some circumstances (different db version for example) the ERROR_PARAMETERx are set differently or not at all. For 11.2 IIRC the tablespace name was set in ERROR_PARAMETER4.

Recycle bin and dropping tablespaces

2012-08-07T11:29:00.000+02:00

Today I hit an interesting issue. First the instance have not shut down within an hour. Second I could not drop a schema. The reason seems to be an entry in the recycle bin left after some table, which was bound with a tablespace, which in turn has been dropped apparently some time ago. So no tablespace, while the entry left and dropping the schema or purging recycle bin have failed with error in recursive SQL.

The solution quite simple - creating a new tablespace with the old name, then purging the recycle bin again .

Log rotation in 11g

2012-07-19T09:15:00.000+02:00

Till version 11g the log rotation may been easily done by lsnrctl and set log_file to new name. With advent of ADR if used it blocks this way.
Excellent article on the theme IMHO at http://msutic.blogspot.com/2009/06/truncating-rotating-flushing.html.

Su-like technology for Oracle db

2012-07-18T14:07:00.000+02:00

From time to time I meet with the situation when it comes to perform many changes on many db schemas provided as a set of scripts.

The simplest way to do this is to logon sequentially to chosen schema and perform there all the necessary changes. This attitude has some drawbacks:
1. one needs to know a password to every schema visited
2. one needs to perform authentication to db many times
3. one needs to run many scripts
first improvement is to create master script, use CONNECT <<user>> command of sqlplus - but this still do not resolve providing passwords
so we may create a more powerful account, gathering all the needed privileges and use
ALTER SESSION SET CURRENT_SCHEMA=<<user>>; instead of CONNECT - this is a step in right direction and as long as scripts use simple objects and functionalities this way addresses all the inconveniences. Another ability is to prefix all the used objects.
still yet we are far from perfectness - there are some functionalities which work wrong - the simplest example here is usage of private db links. Here we may use so called proxy users. Starting from 10gR2 these are available through sqlplus. We don't need a powerful user. We return back to use CONNECT, this time slightly modified. First we need an account with CREATE SESSION privilege. We need to alter all schemas, which we plan to make available:
ALTER USER <<user>> GRANT CONNECT THROUGH <<proxy user>> (we may revoke it with REVOKE CONNECT THROUGH). This time we connect to any so configured user through
CONNECT <<proxy user>>[<<user>>]/<<proxy user password>>. With this addressing all the problems is easy - we need to authenticate as only one user, so even though reconnecting many times, may use sqlplus variable substitution in order to provide a password only once.

There exists the special role BECOME USER, which apparently allows for similar behavior, however it is used for internal processing bound with Data Pump technology and rather not for use for "e;end users"e;.

Enabling Real Time Apply on open standby

2012-06-05T14:00:00.001+02:00

Actually nothing special. One needs to stop the recovery, open standby in READ-ONLY mode and one again start the recovery.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE OPEN;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

There is however one trick - one needs to do this rather quick. It seems (at least for me) that even one archivelog behind the most actual means the managed replication will not start, waiting apparently on some flag indicating the end of previously written log. Thus copying to standby needed logs does not mean, the standby will apply them. It seems a condition is here that archivelog on standby should be "in transition" and the same log being current on primary.

Error ORA-01031/ORA-00604 on communication with standby

2012-06-04T15:25:00.001+02:00

When I have hit the ORA-01031 error on version 10g, the cause was usually boring - badly configured connection primary<->standby, lack of password file, improper permissions on such file, etc. With 11g version the trivial error become more enigmatic. I checked connections, copied the password file from the primary and still got ORA-01031 on connection from the primary to the standby (and ORA-00604 on the connection from the standby to the primary). Finally under the link https://forums.oracle.com/forums/thread.jspa?messageID=10350931 I have found quite good explanation to my problem.
With the advent of 11g version DEFAULT profile has got some limitations - among others expired passwords. Due to this passwords go through grace time period, when "normal" user sees a message about grace time period. But automatically controlled connection to/from a standby gets lost with this behavior, while returned errors do not indicate the right solution, which is simple of course. One may

change a password (to solve temporarily)
or change the DEFAULT profile
or change SYS profile to less restrictive

One must remember that after any of those operations the current logfile needs to be applied on a standby in order to see a positive change. After that automatic redo shipping started to work properly.

Installation of Oracle RDBMS on Oracle Linux 6

2012-05-25T13:44:00.003+02:00

We install usually 2 Oracle products on database environments - RDBMS itself and Grid Control agent. The RDBMS binaries installation actually brings no problems - of course one needs X installation, which may be more than cautious sysadmin would like to install. The Grid agent is other thing - there is number of additional packages to install in order to make the installation process smooth. The best way on Oracle Linux 5 was to install package oracle-validated, which depends on few other packages, which fully cover the agent needs. With Oracle Linux 6 the package, which seems to be needed for that purpose is named oracle-rdbms-server-11gR2-preinstall (R must be in uppercase). However it seems it is not enough - additionally:

yum install libXp.so.6
yum install libXt.so.6
yum install libXtst.so.6

Despite that another problem is I try to install the agent in 10.2.0.5 version, so must have used -ignoreSysPrereqs option. And now failure - some errors in linking. I will switch to 11.1.0.1.

Speeding up a standby recovery with incremental backup

2012-05-16T16:03:00.001+02:00

Lately I have found a description for recovering using incremental backup. Despite speeding up a standby recovery (assuming the number of archivelogs to apply is much beyond the amount of changed data blocks) this method is useful for example for recovery from lost archivelog. I found some good articles on the topic above:

http://dbakerber.wordpress.com/2011/12/20/incremental-recovery-of-standby-asm-and-rman/
http://msutic.blogspot.com/2010/11/recover-from-incrementally-updated.html - this one is especially very precise

My personal goal was to speed up a standby recovery. I did it on the 11.2.0.2 version. I assume here disk channel, but tape is the same or even simpler as one do not need to transfer backup files from the primary to the standby host assuming centralized backup facility. The receipt is as follows:

make an incremental backup from SCN, at which our standby stopped (or actually it is better to backup slightly more then is needed, thus SCN should be a little lower then the really needed). This could be as simple as this:
```
BACKUP INCREMENTAL FROM SCN  60839640548 DATABASE TAG='SCN_BEFORE_60839650548';
```

make a copy of a current controlfile for standby:

# from rman
BACKUP CURRENT CONTROLFILE FOR STANDBY;
# or from sqlplus
ALTER DATABASE CREATE PHYSICAL STANDBY CONTROLFILE AS '/tmp/control01.ctl';

transfer both backups to the standby - starting from here all

restore control files with rman

RESTORE STANDBY CONTROLFILE FROM '[controlfile path]';

or simply replace existing control files if using sqlplus

cp [controlfile path] [CONTROL_FILES parameter value] # for every position in CONTROL_FILES parameter of course

now mount the standby instance

STARTUP NOMOUNT
ALTER DATABASE MOUNT STANDBY DATABASE;

set STANDBY_FILE_MANAGEMENT to MANUAL (if set to AUTO)
catalog all the already existing on standby datafiles to control file
```
CATALOG START WITH '[datafile path]'; # for every datafile path 
```
add all non-existent datafiles with sqlplus or with help of command SQL of rman
```
ALTER DATABASE CREATE DATAFILE [file_id]; 
```
now is the time for switching the database to cataloged copies of datafiles. The simplest way is to:
```
SWITCH DATABASE TO COPY;
```
but if there were some non-existent files, which had to be added in the previous step, this will do not work. The source 2 provides a way to workaround it:
```
CHANGE COPY OF DATAFILE [file_id] UNCATALOG;
```
My way is to produce a script with a call to v$datafile:
```
spool switch_files.rmn
SELECT 'SWITCH DATAFILE '||file_id||' TO COPY;' rmn1 FROM v$datafile ORDER BY file#;
spool off
```
then execute it and edit the spooled content to remove unnecessary entries - among those also tries to switch lately created datafile stubs (stubs are already seen by the instance, so actually no need to switch to them).
now almost finish - recover database with rman - NOREDO forces rman to not use archivelogs at all
```
RECOVER DATABASE NOREDO;
```
the rest is possibly to fix some small issues with redologs, which means usually clear logfiles and to start managed standby recovery
- it is interesting that in order to clear standby logfiles one needs to put at work the parameter log_file_name_convert
- it seems enabling the standby recovery may be performed at any SCN - we simply switch the recovery process from incremental backup to archived logs again. Now onwards we need to apply the logs requested by standby - assuming the managed standby, the log sequences are to be found in an alert log as entries about archive gaps.
```
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
```

And now we are with automatically running standby back again :-)

Data Pump and db links

2012-05-10T15:04:00.000+02:00

The environment I work here with heavily relies on db links due to integration efforts. At the same time we create numerous test/dev environments for projects' needs. Usually for test/dev environment we are not able to provide all the connections or data, while trying to provide 1 to 1 metadata structure. Of course part of it becomes INVALID, but that is ok. The point is to do an import at the lowest cost (in terms of time and writing), thus keep with a good principle, the IT staff should be lazy. The problem is that during Data Pump imports the worker hangs on views built upon non-existing db links, possibly due to firewalling connections.

one may exclude schemas with db link (EXCLUDE=SCHEMA:" IN ('[username]')"
one may define TNS aliases - in our case due to firewalls there was a need to indicate true databases (no matter that there was no proper schemas on them)

Automatic TSPITR and dealing with it

2012-04-19T10:39:00.000+02:00

The automatic TSPITR is very useful when we have an independent schema, which was logically corrupted by some of our actions. Let's say we started a batch processing, which is not a single transaction able to rollback, but rather hundreds of small changes to many tables and this processing failed being inside the run.
In the 11g database it is highly automated - theoretically one needs to run one RMAN command like below:

recover tablespace until time "to_date('2012-04-16 08:15:00','yyyy-mm-dd hh24:mi:ss')" auxiliary destination '/rman/aux';

Lately we use it for exactly the same reason I mentioned above - we restored a schema from a time before processing.

In the first place of course it is best not to recover at all, for example by making UNDO tablespace bigger (and I mean here a defense against ORA-01555 rather than making FLASHBACK TABLE operations possible).
However let's assume one is already in a trouble.

The automatic instance
According to our experience the most important thing is to tune the automatically run instance by providing additional settings.
The automatic instance by default runs on the following parameters:

System parameters with non-default values:
processes = 50
sga_target = 280M
db_block_size = 8192
compatible = "11.2.0.0.0"
log_archive_dest_1 = "location=/rman/aux"
db_files = 200
db_create_file_dest = "/rman/aux"
db_name = "TEST"
db_unique_name = "xgFy_tspitr_TEST"

For us 2 things did not work well. The processes parameter was set too low - at the end of the whole TSPITR process the automatic instance failed due to exceeding the processes limit. This was probably due to some additional job processes, not sure why there were so many of them. Thus the processes value at 150 is much better setting.
Another thing to tune is the SGA_TARGET - for small transactions the default setting is probably ok - our process run smoothly through the files restore phase and majority of the recover phase, but not the whole. In our case doubling this value with setting 200M exclusively for shared pool helped, but this may vary.

What if it failed anyway
Failing the automatic/auxiliary instance is really a disaster to the TSPITR process, but we are able to run it again reusing partially the work done in the previous run. Unfortunately when failing the auxiliary instance removes all the files in auxiliary destination, which means we loose auxiliary control file and datafiles for UNDO, SYSTEM and SYSAUX.
The datafiles for recovered tablespaces are usually restored in the final production destination and they are left as of the moment of the auxiliary instance crash and this is what we could reuse, assuming the TSPITR process did not crash after open resetlogs operation. To use them we need to add to the RMAN RUN block CONFIGURE AUXNAME commands.
Depending on the TSPITR phase the whole process may fail leaving also files in the auxiliary destination, thus we are able to continue the TSPITR process manually. In such case first thing is to remove causes of the failure. One needs to create also its own pfile, where especially important is to know the value of the db_name parameter - those parameters may be taken from alert.log file of the auxiliary instance. After that we start the auxiliary instance.
There is a clone control file in use, so we need to mount database as clone database (ALTER DATABASE MOUNT CLONE DATABASE;).
Open it was a little tricky - we had to use RMAN (and not SQLPLUS) and connect as to auxiliary instance (rman auxiliary /), then simply open with resetlogs. After this moment the rest is quite simple - the best way is to follow a log from some previous TSPITR (such log may be found on Metalink for example). One needs to set the recovered files as READ-ONLY and create an alias for a dictionary to use by Data Pump (it is good to set the same dictionary as in the primary database). Then goes an expdp (expdp dumpfile= transport_tablespaces=, possibly TRANSPORT_FULL_CHECK, if needed).
Afterwards one may close the auxiliary instance, as this is not needed anymore. Then impdp to the primary database, setting tablespace to READ-WRITE again and voila - the thing is done.

Kudos to Pawel Smolarz :-)