Data Pump and db links

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.

1. one may exclude schemas with db link (EXCLUDE=SCHEMA:" IN ('[username]')"
2. 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

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 :-)

Partitioning by interval

Few notes:

• interval needs to be a constant - we had here a time recorded as UNIX timestamp, thus in the database it was a NUMBER column to store those values - in this case to use interval we may partition by days or weeks
  
• interval constant type needs to be compatible with partition key data type (ie. number with number, date with time interval) - which is somewhat obvious
  
• at least one partition needs to be created in a "static" way (with the definition of the table) - so a database will have a starting point for next partitions, despite that the last "statically" added partition can not be removed - such activity finish with error ORA-14758.
• in order to archive/delete older partitions one may:
  
  • truncate the first "static" partition (ALTER TABLE blabla TRUNCATE PARTITION dont_delete DROP STORAGE UPDATE INDEXES;)
  • drop chosen partitions despite of the first one (ALTER TABLE blabla TRUNCATE PARTITION sys_536 UPDATE INDEXES;)
  • it is easy to automate dropping old partitions by dropping after every period the partition with partition_position=2 in dba_tab_partitions (or possibly an equivalent for subpartitions)

Cloning Grid Control agent binaries

Lastly we created a 2-node system in configuration active-standby. The environment for the second node was simply a copy of the first one. As all the paths were preserved then we successfully used the same Oracle Database binaries.
However when came to agents, when we run them on both nodes we get only one agent on the OMS. The other agent was seen as the same possibly - for example agents have a generated at the installation time agent seed, which is seen in several places - for example in emd.properties (as AgentSeed property) and in targets.xml (hashed/encrypted to AGENT_TOKEN). I have got an error about duplicating the agent. Possibly there are other such settings.

The solution is to clone the agent:
- copy binaries to the destination location
- run $ORACLE_HOME/oui/bin/runInstaller -clone -forceClone ORACLE_HOME= ORACLE_HOME_NAME= -noconfig -silent
- run $ORACLE_HOME/bin/agentca -f to run Agent Configuration Assistant
- run $ORACLE_HOME/root.sh as root (assuming this is not a first Oracle product on this machine, else run /oraInventory/orainstRoot.sh)

Location in Oracle docs here.

Error 1017 received logging on to the standby

I have got frequently the error "Error 1017 received logging on to the standby", while trying to enable the archivelog transfer between primary and standby.
The majority of tips on the net and the entry in the alert.log would tell You:
- check Your remote_login_passwordfile - should be SHARED or EXCLUSIVE
- check Your password file - it should exist and a password for SYS should be the same.
I have done as suggested, still no progress.

The solution was actually trivial - just copy the password file from the primary to the standby. Not sure why previously it did not work - I am sure the SYS password was the same and I checked connection in both directions (i.e. PRIMARY->STANDBY, STANDBY->PRIMARY) using the same entries in tnsnames.ora as specified in the archivelog transfer configuration.

It seems, there is a huge difference between versions 10g and 11g in this case - previously it was enough to create new password file with the same password. Now it must be the same file (ie. copy from the original on the primary).
And here is a very good article on the password files in Data Guard environment.

BIND_MISMATCH reason for not sharing cursor

BIND_MISMATCH reason is another one from the long list of possible reasons why not to share a cursor. General reason here is that bind variables between two executions differ too much for the database. The details are stored in the REASON column of the V$SQL_SHARED_CURSOR view. Here is how it looks like:


<childnode>
<childnumber>1</ChildNumber>
<id>40</ID>
<reason>Bind mismatch(22)</reason>
<size>4x4</size>
<bind_position>157</bind_position>
<original_oacflg>1</original_oacflg>
<original_oacmxl>32</original_oacmxl>
<upgradeable_new_oacmxl>128</upgradeable_new_oacmxl>
</ChildNode>




Whole thing is quite easy to decrypt. There is one node per an existing cursor child. The REASON tag provides one of reasons the database provides when it does not use an existing cursor child, but instead creates another one. The bind position indicates the position of the bind variable within a SQL text (every entry has a unique position per SQL even if we bind the same variable).
The ORIGINAL_OACFLG tag IMHO is the same entity as the OACFLG (or first FLG) in a bind section of a 10046 event raw trace and of course there is more equivalents.

The bind variables use some standard size buffers, which depend on data type. For varchar2 there are several sizes (due to varying nature of the type) and first size amounts to 32, so the minimum buffer length for bind variable of varchar2 is 32 bytes. The next values are 128 bytes, then 2000 bytes, and so on.

The change between different values of a bind variable may become a reason for not sharing a cursor . This happens when a buffer size of a variable changes - for example from smaller to larger.

Some details about bind section of the 10046 event trace

In general very good description of the 10046 event trace interpretation is contained on MOS as the article with 39817.1 id.
However with time there showed new entries not really mentioned in the article indicated above.
Let's see an example of the bind section:

Bind#2
oacdty=96 mxl=32(02) mxlc=00 mal=00 scl=00 pre=00
oacflg=01 fl2=1000000 frm=02 csi=2000 siz=0 off=56
kxsbbbfp=7fb04313fe80 bln=32 avl=02 flg=01
value=0 54

oac prefix I simply ignore and not sure what it stands for.

• dty is an id for data type
• mxl is a maximum length of bind variable (in parentheses there is provided a private maximum length - for me this is current bind variable length)
• mxlc is not sure for me, but I saw it always equal to 00
• mal is a maximum array length
• scl stands for scale
• pre stands for precision
• flg is a flag with bind options
• fl2 is a continuation of flag
• frm stays unknown
• csi is character set id, which can be decrypted with nls_charset_name function; the nls_charset_id function, working in an opposite way, is available as well
• siz is an amount of memory allocated for this chunk
• off is a memory offset of a buffer for this variable within the chunk
• kxsbbbfp is a bind addres
• bln is a bind buffer length and I suppose usually it is equal to mxl, unless data does not fit into one chunk
• avl is a current bind value length (or array length)
• flg is another flag (this time for bind status)
• value - finally current value