Singular Vs Plural

Let’s talk a little about the V$ views and data dictionary views. The views store data while the database is up, when you shutdown the database you lost data, you can access some when the data base is in nomount or mount state. The data dictionary views do not lose any data but you can only access them as the database is open.

Usually, with some exceptions, the data dictionary views are plural names and V$ views are singular names.

HTH – Antonio NAVARRO

Attach a Cloned OracleHome to the Inventory

Many times it is faster clone an existing OracleHome that to install a new one, these actions are very typical to have the same environments; Production, QA/Test and Development. Maybe the main advantage is time especially if you have to apply patches or oneoffs. The problem we have is when applying patches, for example, when we find a bug in production, we can test the patch to be applied, you must first install it in the QA/Test enviroment or development to check it and do not generate other problems, derived from the installation of the patch.

If we copy the binaries files, these will not be in the inventory, and tasks such as installing a patch can become more complex. So whenever we make a physical copy of the binaries is good idea to attach them to the local inventory. This can be done with the following command.

 

./runInstaller -silent -attachHome ORACLE_HOME=”\cloneengine\Home_QA” ORACLE_HOME_NAME=”Home_5″

 

HTH – Antonio NAVARRO

 

Delete Files Permanently and Securely in Unix

Sometimes we need to delete files in unix in a way that can not be recovered (such of password files) with something more efficient than a simple rm command. This script makes a safe physical deletion of a file in UNIX.

This configured to five writes, and each write makes a physical random write to soil and immediately makes another write below setting all to zeros. Although may seem excessive five writes, there are experts that recommended at least 40 writes. Of course, what about if you perform filesystem backup using a clone or differential backups?, you could recover the file from backup.

This command is done using shell script and can be somewhat inefficient, could be done in a more optimal using C code, which does not require much cost. Any volunteer to code it?

 

#!/bin/ksh

#

 

MAXWRITES=5 # Number of writes.

BLOCKSIZE=1 # I/O block size for/dev/urandom.

 

#

# Script version.

#

version=”BORSEGU 1.0 Autor: Antonio NAVARRO 17.05.2002″

 

 

#

# We need at least one parameter, the filename

#

if [ -z “$1” ] # No filename specified.

then

echo “Usage: `basename $0` filename”

echo ” (“$version”)”

exit -1

fi

 

#

# Check if file exists.

#

fichero=$1

 

if [ ! -e “$fichero” ]

then

echo “File \”$fichero\” do not exists.”

echo “(“$version”)”

exit -1

fi

 

#

# Confirm delete

#

 

echo ” ”

echo “Delete file (“$fichero”) (y/n)? ”

read respuesta

case “$respuesta” in

[nN]) echo “Canceled.”

echo “(“$version”)”

exit -1

;;

*) echo “>>> Starting secure delete of (“$fichero”).”;;

esac

 

 

#

# Get the size of file.

#

longitud=$(ls -l “$fichero” | awk ‘{print $5}’)

 

number_of_write=1

 

echo

 

while [ “$number_of_write” -le “$MAXWRITES” ]

do

echo “>>> Write: “$number_of_write

/usr/sbin/sync # Synchronize disks.

dd if=/dev/urandom of=$fichero bs=$BLOCKSIZE count=$longitud

# Refill with random values.

/usr/sbin/sync # Synchronize disks.

dd if=/dev/zero of=$fichero bs=$BLOCKSIZE count=$longitud

# Refill with zeros.

/usr/sbin/sync # Synchronize disks.

let “number_of_write += 1”

echo

done

 

 

#

# At the end, delete file

#

rm -f $fichero

/usr/sbin/sync

 

echo “>>> File (“$fichero”) deleted.”

echo “(“$version”)”

 

 

HTH – Antonio NAVARRO

 

Siebel Trial

From some time you can to test Siebel and all its features temporarily by using temporary licenses.
You can download Siebel from eDelivery. Another option is to download a virtual machine already installed Siebel and inital settings, of course, this initial configuration maybe isn’t the best.

You can find temporary licenses at

 

HTH – Antonio NAVARRO

 

Copy User

These are two simple methods to create a copy of a user on another with the same permissions and privileges.

i) Using the system views

— REPLACE FROMUSER BY USER TO COPY
— REPLACE TOUSER BY NEW USER
— CREATE USER
SELECT ‘CREATE USER TOUSER IDENTIFIED ‘ ||
DECODE (PASSWORD,
NULL, ‘EXTERNALLY’,
‘ BY VALUES ‘ || ”” || PASSWORD || ””
) || CHR(10) ||
‘DEFAULT TABLESPACE ‘ || DEFAULT_TABLESPACE || CHR(10) ||
‘TEMPORARY TABLESPACE ‘ || TEMPORARY_TABLESPACE || CHR(10) ||
‘PROFILE ‘ || PROFILE || CHR(10) ||
‘/’
FROM DBA_USERS
WHERE USERNAME = ‘FROMUSER’

SELECT ‘GRANT ‘ || PRIVILEGE || ‘ TO TOUSER ;’ FROM DBA_SYS_PRIVS WHERE GRANTEE = ‘FROMUSER’;
SELECT ‘GRANT ‘ || GRANTED_ROLE || ‘ TO TOUSER ;’ FROM DBA_ROLE_PRIVS WHERE GRANTEE = ‘FROMUSER’;
SELECT ‘GRANT ‘ || PRIVILEGE || ‘ ON ‘ || OWNER || ‘.’ || TABLE_NAME || ‘ TO TOUSER ;’
FROM DBA_TAB_PRIVS WHERE GRANTEE = ‘FROMUSER’;

ii) Using the DBMS_METADATA package

— GENERATE DDL SCRIPT, AFTER THAT CHANGE FROMUSER TO THE NEW USER

SELECT DBMS_METADATA.GET_DDL (‘USER’, ‘FROMUSER’) FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL (‘ROLE_GRANT’,’FROMUSER’) FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL (‘SYSTEM_GRANT’,’FROMUSER’) FROM DUAL;
SELECT DBMS_METADATA.GET_GRANTED_DDL (‘OBJECT_GRANT’,’FROMUSER’) FROM DUAL;
SELECT DBMS_METADATA.GET_granted_DDL (‘TABLESPACE_QUOTA’, ‘FROMUSER’) FROM dual;

HTH – Antonio NAVARRO

Monitoring Workflow Policies Tables in Siebel

Today I have been reported an incidence, S_ESCL_ACTN_REQ table was growing a lot, this is not normal. This table identifies  all the rows that are awaiting action execution. In this case before worked fine, but after investigating with the development team is seen throwing a query at a later point a workflow has degraded processes Policies having a worse performance.

The solution is otpimize the query and reduce execution time, of course,  new Processes Workflow Policies need to be created to share the load and Improve performance.

Siebel Workflow Policies use three database tables for processing and tracking requests, and these three tables are key in Siebel

  • S_ESCL_REQ
  • S_ESCL_STATE
  • S_ESCL_ACTN_REQ

It is very important to monitor These tables to Verify That Correctly policies are being processed. The number of tuples to be as low as possible or tend to zero. As DBAs we can monitor these tables in different ways, pl / sql, shell script, Grid Control, third-party tools such as OpenView, BMC Patrol, Nagios.

We are also responsible for maintenance, it is a good idea to cache these tables into memory and ensure quick access, as it can become a bottleneck that chokes entire system. You can cache the memory tables by run the statement;

alter table SIEBEL.S_ESCL_ACTN_REQ storage(buffer_pool keep);

If not using automatic memory, please refer to the DB_KEEP_CACHE_SIZE parameter.

HTH – Antonio NAVARRO