Database Technologies...!

Oracle version -- Oracle 9i to 11g While splitting partitions, SQL> alter TABLE ARBOR.CDR_BILLED split PARTITION CDR_BILLED_MAXVALUE at (TO_DATE('2014-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) into (partition CDR_BILLED_01SEP14,partition CDR_BILLED_MAXVALUE) TABLESPACE "C01_CDR_BILL" update global indexes; 2 into (partition CDR_BILLED_01SEP14,partition CDR_BILLED_MAXVALUE) TABLESPACE "C01_CDR_BILL" update global indexes * ERROR at line 2: ORA-14126: only a <parallel clause> may follow description(s) of resulting partitions 4:58:56 PM The reason for above error is while splitting max value partition, it is not allowed to explicitly provide a tablespace. The splitted partition will be reside on the same tablespace as the max value partitions. Below can be used without the tablespace clause to split the max value partition, alter TABLE ARBOR.CDR_BILLED split PARTITION CDR_BILLED_

DB2 version - 10.1 OS version - AIX 6.1 After restoring db2 version 9.5 coldbackup on top of db2 v10.1 for upgrade, application users who logged in via 3rd party gui was getting the below error, DB2 SQL error: SQLCODE: -5193, SQLSTATE: 42524, SQLERRMC: null On the diag log bellow error was repeated when ever client was executing a query, FUNCTION: DB2 UDB, routine_infrastructure, sqlerCallDL, probe:5 RETCODE : ZRC=0x801A006D=-2145779603=SQLZ_CA_BUILT "SQLCA has already been built" when the user tries to query directly via the server it self, SQL5193N  The current session user does not have usage privilege on any enabled workloads.  SQLSTATE=42524 So although the first error was not very clear it was caused by not having workload privilege. By adding the user to a default workload as below, error can be resolved. bash-3.2$ db2 create role ROLE_REGULAR_USERS; DB20000I The SQL command completed successfully. bash-3.2$ db2 grant role ROLE_REGULAR_USERS t

DB2 Version – 9.5 Fixpack 3 OS – AIX 5.3 SQL10004C An I/O error occurred while accessing the database directory. SQLSTATE=58031. This error was returned during the restoration of db backup on a new server. As per the docs and references this can be caused by several reasons including OS level permission, db and os level version etc.. But quick workaround that can be tested to resolve this error while performing a db2 restore is to try the restoration with different database directory without using the default one with the use "dbpath on" clause of db2 restore command.  By adding "dbpath on" to the restoration command as below, db2 "restore database CPSDB from '/data12/backup' dbpath on '/data12/cpsjun15' into cpsjun15 logtarget '/data12/cpsjun15/log' redirect"

Version -- Oracle 11.2.0.4 In most of the oracle production dbs public executes are revoked as per the security requirements. But doing so can cause some of the functionalities to fail. Here we will discuss the effect on ACLs by revoking public execute on UTL_* packages. Revokes --> 'revoke execute on UTL_TCP from public' 'revoke execute on UTL_HTTP from public' 'revoke execute on UTL_SMTP from public' now the ACL will not work since the users assigned to ACL do not have the required privileges on ULT packages. But this also triggers another event. select * from dba_network_acls will return a ora-00600 as below.   ERROR at line 1: ORA-00600: internal error code, arguments: [qmxqtmChkXQAtomMapSQL:2], [], [], [], [], [], [], [], [], [], [], [] 10046 trace for the session shows below,  XDB.DBMS_CSX_INT is invalid. Querying dba_objects show lots of invalid objects owned by XDB and SYS. To resolve, First UTL pacakage privileges were granted to XDB u

OS - *ix Some useful sed, awk and bash commands for dbas. Some of them needs bit of improvements and might well have other better alternatives (e.g. one liners). 1. Provided with line by line list which needs to be put in to sql " IN " clause. [stefan@oc8233082860 ~]$ cat list.txt RMS_USER_ROLE_DEFINITION RMS_USER_ROLE RMS_USER_DETAILS [stefan@oc8233082860 ~]$ cat list.txt | awk -vORS=, '{ print $1 }' | sed 's/,$/\n/' > list2.txt [stefan@oc8233082860 ~]$ sed -r 's/,([^ ),]+)/,'\''\1'\''/g; s/,,/,'\'\'',/g' list2.txt RMS_USER_ROLE_DEFINITION,'RMS_USER_ROLE','RMS_USER_DETAILS' [stefan@oc8233082860 ~]$ 2. Getting datafile name removing the directory path. [stefan@pc_stefan ~]$ cat cmd_test.txt /dc2_db2/PACS/data/system.dbf /dc2_db2/PACS/data/undo.dbf /dc2_db2/PACS/data/sysaux.dbf /dc2_db2/PACS/data/pacs_cust_data01.dbf /dc2_db2/PACS/data/

Oracle - 11.2 This is a brief list pointing out some factors on nologging operations which results unrecoverability in database. Once a db backup with unrecoverable operations fully recovered, accessing affected objects will still return ORA--1578. 1. Detecting unrecoverable operations. Rman " report unrecoverable " will provide list of datafiles which are in unrecoverable state. Also unrecoverable_time column of v$datafile can be queried to see the last unrecoverable action performed on particular datafile, but this column will not be updated after a backup is made from rman. 2. There want be any errors during the restore/recovery operations in rman  logs. Even during a nologging operation small amount of redo is generated. These logs simply says that affected block is for nologging operation. So during a roll-forward these logs will be applied, but they will logically corrupt the affected blocks. 3. After full restore/recovery of daabase with nologging corr

DB - Oracle 11g Creating large locally partitioned indexes on  huge partitioned table, 1. Define the index unusable and invisible with parallel and nologging options. CREATE BITMAP INDEX "DWH_SLT"."IND_SL2008_NONROM_MOB_NO_HIST" ON "DWH_SLT"."SLT_NON_ROM_2008_09_HIST" ("MOBILE_NO") nologging local unusable parallel 6 invisible tablespace SLT_OCS_INDX1; 2. Rebuild each local partition of the index . eg. 1: alter index "DWH_SLT"."IND_SL2008_NONROM_MOB_NO_HIST" rebuild partition CDR_14_JAN3_N parallel(degree 6); 2: alter index "DWH_SLT"."IND_SL2008_NONROM_MOB_NO_HIST" rebuild partition CDR_14_FEB1_N parallel(degree 6); This can be done by lopping through all the index partitions via a procedure. After this all index partitions needs to be usable. 3. Make the index logging and disable parallel. alter index DWH_SLT.IND_SL2008_NONROM_MOB_NO_HIST logging noparallel; 4. Make the

DB - Oracle 11g/10g OS - AIX 6.1/5.3 When running oracle on AIX, one issue that can occur is high CPU with os process lrud using large % of the available CPU and large Kernel cpu usage as well. This will cause the instance to hang with errors such as 'ORA-00020: maximum number of processes exceeded' . Topas output on this situation can be similar to bellow, lrud is the page stealing process used by AIX virtual memory manager. So this shows a situation where OS VMM is heavily active. Below lists some checkpoints from both Oracle and OS end for this issue. From Oracle --> When oracle is not using Direct I/O which means OS level caching is not used the OS VMM will be freed from all the overhead due to oracle I/O activity. So using Direct I/O with large SGA can be a solution. The parameter 'filesystemio_options' defines the method oracle uses for I/O. By setting SETALL for this parameter we can enable Direct I/O from db end. From AIX --> Some

DB2 v9.1 AIX 5.3 This post demonstrate step by step approach to restart a database which returns SQL1034C in DB2. Error → bash-4.2$ db2 connect to TESTDB SQL1034C The database is damaged. All applications processing the database have been stopped. SQLSTATE=58031 1. DB2 stop with force application all. b ash-4.2$ db2 force application all DB20000I The FORCE APPLICATION command completed successfully. DB21024I This command is asynchronous and may not be effective immediately. bash-4.2$ db2 list active databases SQL1611W No data was returned by Database System Monitor. bash-4.2$ db2stop 02/24/2015 01:38:16 0 0 SQL1025N The database manager was not stopped because databases are still active. SQL1025N The database manager was not stopped because databases are still active. 2. DB2 quiesce instance force connections bash-4.2$ db2 quiesce instance TINST1 immediate force connections SQ

OS – AIX 5.3 DB – DB2 9.1,9.5,10.1 LUW In earlier posts ( post1 , post2 ) full db2 restoration in to a different server was discussed. This post is kind of add-on showing the restoration process skipping set of table spaces. For this restoration generated script will be used instead of manually issuing redirect commands. Step 1 → Generate the script skipping tablespaces db2 "restore database skipdb rebuild with all tablespaces in image except tablespace(VL_USR_8K,VL_IDX_USR_8K,VL_USR_MSTR_4K,USR_MSTR_IDX_4K,TS_INOUT_IDX,TS_INOUT_SEP12,TS_INOUT_OCT12,TS_INOUT_JAN13,TS_INOUT_FEB13,TS_INOUT_MAR13,TS_INOUT_APR13,TS_INOUT_MAY11,TS_INOUT_JUN11) from /DB2restore dbpath on '/DB2restore' into skipdb logtarget '/DB2restore/mtrptlog' redirect generate script skip_gen.clp" Step 2 → Change the redirect paths in generated script. The script generated (skip_gen.clp) includes datafile paths of the original db. If restoring to different setup cha

Platform – Oracle Virtualbox4.3.2 Guest OS – RHEL 5.5 This is short post on troubleshooting some of the issues that were encountered during multiple network interface configuration in Oracle Virtual box with RHEL 5.5. VM setup → 1 Nat adapter, 1 Host-only adapter. Issues Faces → Nat adapter was not resolving DNS. This was due to an issue with automatically generated /etc/resolve.conf. By using below command to force usage of host machine DNS in guest this issue was resolved. VBoxManage modifyvm "<VM_NAME>" --natdnshostresolver1 on 2. DNS was ok but couldn't access the internet. The NAT interface was simple not connecting to internet. Simple trace route to google.com showed that internet connection was tried via the hostonly network. Routes were not correctly configured in the VM guest. The defult route getwway was set to hostonly adapter. Below are the steps to correct the route table. [root@localhost ~]# route Kernel IP

Oracle version -- Oracle 9i Below are the steps to open a read-only standby BCV database as a production ready (read-write) in oracle 9i. SQL> alter database mount standby database; Database altered. SQL> alter database open read only; Database altered. SQL> SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE; Database altered. SQL> SQL> alter database mount; Database altered. SQL> alter database open; Database altered. SQL> select name, open_mode, log_mode from v$database; NAME OPEN_MODE LOG_MODE --------- ---------- ------------ FXBPADM READ WRITE ARCHIVELOG SQL>

OS -- AIX 5.3 This is a very simple script that can be used to transfer set of AIX logical volumes (LV) from one volume group (VG) to another. This was used to move all oracle db LVs from one VG to another. 1. List all LVs to be moved in a txt file in the format of "lsvg -l" output. [root@BLOGHOST:/vg_move]$lsvg -l datavg | grep -i ora > oracle_lv.txt [root@BLOGHOST:/vg_move]$ cat oracle_lv.txt oracle_home2_lv jfs2 40 40 1 open/syncd /oracle oracle_home3_lv jfs2 32 32 1 open/syncd /cust fslv02 jfs2 120 120 3 open/syncd /orahome11g fslv05 jfs2 80 80 1 open/syncd /orahome nms_temp jfs2 8 8 1 open/syncd /ora_temp orahome10gg_2 jfs2 280 280 2 open/syncd /orahome10_2 oradataictpslt_ jfs2 444 444 5 open/syncd /oradata2 oradata1_lv jfs2 400 400 9 open/syncd /oradata1 oradataicp1_lv jfs2 400 400 5 open/syncd /ora fx_orahome

DB2 version → Checked on v8.1 and v9.7, think it is same on other versions. OS → AIX 5 In one of our db2 databases there was a sudden growth of space usage in db2 instance home. This was due to huge .nfy log being creted on db2dump folder. This .nfy log was repeating same error below and was growing very fast, reached 10G within 4 hrs in our case. 2014-12-23-09.38.48.685598 Instance:idsldap Node:000 PID:1695766(db2aud) TID:1 Appid:none bsu security sqlexGetDefaultLoginContext Probe:15 ADM13001E Plug-in "IBMOSauthclient" received error code "-2" from the DB2 security plug-in API "db2secGetDefaultLoginContext" with the error message " ". There were some errors related access violation in diag log as well, 2014-12-23-09.40.41.094246+330 I137165939A396 LEVEL: Error PID : 1609906 TID : 1 PROC : db2set INSTANCE: idsldap NODE : 000 FUNCTIO