Detecting gap in series of numbers in database table

How to list gaps in series or sequence data stored in a database.

SQL> select (t1.i +1) as gap_starts_at, (select min(t3.i) -1 from sequence_test t3 where t3.i > t1.i) as gap_ends_at from sequence_test t1 where not exists (select t2.i from sequence_test t2 where t2.i = t1.i + 1) order by 1;

GAP_STARTS_AT GAP_ENDS_AT
------------- -----------
11 17
36 60
68 100
111 117
136 160
168 200
211 217
236 260
268 300
311 317
336 360
368 400
411 417

Ref: http://stackoverflow.com/questions/4340793/how-to-find-gaps-in-sequential-numbering-in-mysql

How to grab (or create) a screen session with PuTTY

When connecting to my server from PuTTY I like to use the unix command ‘screen’ so I can persist my sessions. It is hard to remember if I have currently detached screens and bothersome to find them. I wanted a script that would automatically connect to any detached screen session or create one if no free screens were available. Here is the one-liner I use in the SSH property “Remote command:” section. Save this to a saved session and with a double-click you are ready to work.
if [ `screen -ls |grep Detached|head -1|awk '{print $1}'|wc -l` -gt 0 ]; then screen -x `screen -x |grep Detached|head -1|awk '{print $1}'`; else screen -S Rick; fi

ORA-24327: need explicit attach before authenticating a user

DBI connect(‘betterlogic’,’database’,…) failed: ORA-24327: need explicit attach before authenticating a user (DBD ERROR: OCISessionBegin)

The fix was to change the connection information in the perl script to match the TNSNAMES.ORA specification for the database I was connecting to.

Performance issues with SOA console

The typical troubleshooting scenario we like to get as much information as we can. However, we have to balance that with the cost of collecting the information. For example, in a large SOA installation with several hindered or perhaps millions of composite instances, setting the console to track the ‘state’ of those instances is abusive and time consuming. One should only enable the composite instance state monitoring in the SOA console if that information is actually more valuable than the delay in getting the information. Collecting this and other ‘optional’ information can bring the OEM console to its knees.

The guidance: Only collect the information that you are currently and actively using

Option: use a different channel to collect the same information. Perhaps a separate query directly to the database will reveal the desired information without crushing the console’s responsiveness.

Capture Composite Instance State

Select to capture the SOA composite application instance state. Enabling this option may result in additional run time overhead during instance processing. This option provides for separate tracking of the running instances. All instances are captured as either running or not running. This information displays later in the State column of the composite instances tables for the SOA Infrastructure and SOA composite application, where:

  • It shows the counts of running instances versus total instances
  • You can also limit the view to running instances only

Valid states are running, completed, faulted, recovery needed, stale, terminated, suspended, and state not available.

The running and completed states are captured only if this check box is selected. Otherwise, the state is set to unknown. The conditional capturing of these states is done mainly to reduce the performance overhead on SOA Infrastructure run time.

Note: If this property is disabled and you create a new instance of a SOA composite application, a new instance is created, but the instance does not display as running, faulted, stale, suspended, terminated, completed, or requiring recovery in the table of the Dashboard page of the composite application. This is because capturing the composite state of instances is a performance-intensive process.

For example, if you enable this property and create an instance of a SOA composite application in the Test Web Service page, a new instance appears in the Dashboard page of the composite application. If you click Show Only Running Instances in the Dashboard page, the instance displays as running. If you then disable this property and create another instance of the same composite application, a new, running instance is created. However, if you then select Show Only Running Instances, the new instance is not listed in the table of running instances.

In addition, to terminate a running instance, the instance must have a state (for example, running, faulted, suspended). This activates the Abort button on the Instances page of a SOA composite application. If this check box is not enabled before creating an instance, the Abort button is inactive, and you cannot terminate the instance.

 

Also note the very explicit (and counter-intuitive) warnings about how the console displays the state information (highlighted in bold/red)

 

Reference:

http://download.oracle.com/docs/cd/E12839_01/integration.1111/e10226/soainfra_config.htm#BHCHBAIA

RMAN backup error


input archive log thread=2 sequence=46274 recid=166663 stamp=714935844
input archive log thread=2 sequence=46275 recid=166665 stamp=714935889
input archive log thread=2 sequence=46276 recid=166667 stamp=714939059
input archive log thread=2 sequence=46277 recid=166668 stamp=714939083
channel ch0: starting piece 1 at 29-MAR-10
released channel: ch0
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ch0 channel at 03/29/2010 18:27:07
ORA-19506: failed to create sequential file, name=”r2l9q6n6_1_1″, parms=””
ORA-27028: skgfqcre: sbtbackup returned error
ORA-19511: Error received from media manager layer, error text:
VxBSACreateObject: Failed with error:
Server Status: Communication with the server has not been iniatated or the server status has not been retrieved from the server.

The fix was to remove the client identification from the rman script.

ORA-14400: inserted partition key does not map to any partition

Got this error in the emoms.trc log and thousands of .xml files in the recv directory.
2009-12-22 18:13:37,659 [XMLLoader0 30000031529.xml] ERROR emdrep.XMLLoaderContext flushTableStatements.1587 – Error while flushing insert statementjava.sql.SQLException: ORA-14400: inserted partition key does not map to any partition

The fix was found on metalink as [ID 317046.1] Problem: Many Ora-14400 Errors Showing In Management Services And Repository Ora-14400 [ID 317046.1]

SQL> exec emd_maintenance.analyze_emd_schema('SYSMAN');

PL/SQL procedure successfully completed.
Also make sure your job_queue_processes is set to 10 or above.
Hope that helps

RDBMS package: DBMS_AQ doesnt exist

While upgrading OEM from 10.2.0.4 to 10.2.0.5 I got the following error:

[Text: Required RDBMS package: DBMS_AQ doesnt exist on this database. This package is needed for the proper functioning of the EM product. Please contact Oracle support to diagnose the RDBMS and once resolved, retry EM install.]

This was caused by the sys password not being the same on all instances. The root cause was that a DBA had changed the sys password on a single instance to do some maintenance and did not change it back. This caused sys connections to the rac database to succeed and fail intermittently.

The fix was to resync the sys password by running the following from sqlplus as the sysdba:
SQL> alter user sys identified by “correct_password”;
Once the passwords were correctly synced, the upgrade ran fine.

Setting the TNS_ADMIN envrionment

The TNS_ADMIN environment is used by the installation to find the location of the tnsnames.ora and the listener.ora files…

 

Default location

The default location of the tnsnames.ora and listener.ora files are in $ORACLE_HOME/network/admin. If you want to change the location you have to show Oracle where to find the files. This is done by setting the TNS_ADMIN environment settings as follows:

bash-3.00$ export TNS_ADMIN=/u01/app/oracle/network/admin

 

If you’re using RAC, you’ll want to make sure that the Oracle Cluster Ready service knows where to find the file. This is important because OCR will be used to start/stop and manage your database.

To setup

bash-3.00$ srvctl setenv database -d rmant -T “TNS_ADMIN=/fs01/app/oracle/product/10.2.0/network/admin/”

 

bash-3.00$ srvctl getenv database -d rmant
TNS_ADMIN=/fs01/app/oracle/product/10.2.0/network/admin/

ORA-19506: failed to create sequential file

Many people run Oracle RMAN scripts from crontab and an OS shell script. We prefer to run all of our RMAN backups via OEM and the database scheduler instead of from a specific server or instance. There are many benefits OEM/database scheduler backups a few of which are:

  • Centralized administration makes it easy to manage 100’s of databases
  • Database affinity means that backups run even if a server crashes
  • OEM monitors the output of the RMAN and can alert the DBA if there are errors

One thing that we have noticed is that when we used our OS (server) RMAN shell scripts we had a string similar to the following:

send ‘NB_ORA_CLIENT=gmrac1

This parameter is optional for Networker and is used to assist Networker with recovery operations to alternate servers.

Occasionally, the RMAN would fail for no immediately obvious reason. On closer inspection we saw Networker was timing out while connecting to the target server. Everytime the database RMAN job was launched from gmrac1 it would succeed. However when it was run from any other server in the cluster the job would fail with the following:

RMAN-03009: failure of backup command on ch0 channel at 02/05/2009 16:51:46

ORA-19506: failed to create sequential file, name=”mqk6k505_1_1″, parms=””
ORA-27028: skgfqcre: sbtbackup returned error
ORA-19511: Error received from media manager layer, error text: VxBSACreateObject: Failed with error: Server Status: Communication with the server has not been iniatated or the server status has not been retrieved from the server.

[sic]

The fix was to remove the optional parameter NB_ORA_CLIENT=gmrac1 and let Networker do the right thing. For reference, our broken RMAN script is shown below:

run {
sql ‘alter system archive log current';
allocate channel ch0 type ‘SBT_TAPE';
send ‘NB_ORA_CLIENT=gmrac1,NB_ORA_POLICY=PROD_ora,
NB_ORA_SERV=backup.betterlogic.com';
backup
tag “prod_archivelogs_only”
archivelog all not backed up;
delete noprompt archivelog until time ‘sysdate -2′ backed up 1 times to ‘SBT_TAPE';
release channel ch0;}

The 360 Degree Leader

I liked this book. It had a lot of good ideas for a co-operative leader, one that people want to follow and receive inspiration from.

Spank the stupid

Wonderful, must-read article with a super quote.

“The ultimate result of shielding men from the effects of folly is to fill the world with fools.” – Herbert Spencer

Must read for citizens.

I am encouraging everyone to work harder and smarter. Efficiency of output and execution will restore profits and save jobs. Working smarter is only one piece of the solution.

The link below summarizes the problem. This article is a must read for every working class citizen who thinks we can just let someone else drive the country.
http://www.minyanville.com/articles/Bernanke-Paulson-Fed-Credit-crunch-recession/index/a/19357

stty

STTY

In the .profile or .bashrc add the following lines:

stty ek
stty erase ^H
EDITOR=vi
export EDITOR

 

Before typing ^H or ^? type ^V

Tracing in oracle

EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(service_name => ‘betterlogic.com’, waits => TRUE, binds => TRUE, instance_name => ‘bpel1′);

select * from dba_enabled_traces;

EXECUTE DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE(service_name => ‘betterlogic.com’, instance_name => ‘bpel1′);

oracle@rac34 udump]$ trcsess output=”dump.file” service=”betterlogic.com” module=”JDBC Thin Client”

Solaris max file descriptor

Oracle forms compile failed with “Out of file descriptors”.

Fix: projmod -s -K “process.max-file-descriptor=(basic,10000,deny)” oracle

Reference: IBM Resource Limit Configuration

test post of image

testing post of image

test post from firefox3

this is a test

test post from firefox3

mnt/onebetter cleared for disk migration

The disk mount point was cleared to get ready for 300G disk migration.

Dell 2900 Manual

Dellâ„¢ PowerEdgeâ„¢ 2900

Systems Hardware Owner’s Manual