Tracing DBMS_MONITOR

Tracing with DBMS_MONITOR

Silent Installation of Oracle 11gR2 database software (standalone) on RHEL

Silent Installation of Oracle 11gR2 DB software for standalone

Partial Restore of Database

Partial Restore of Database

Registering Database with listener

Registering Database with listener

Connecting Excel to Oracle database

Connecting Excel to Oracle Database

Insert into table row data with '&' sign

Inserting into table is pretty straight forward, however a scenario to note would be if you need to enter '&' as a character in your table row.

"&" has a special meaning (substitution variable) in oracle database. While using sqlplus if you use that to enter  into a field, it will prompt you to enter a value in that field, to avoid this we user "set define off;"

SQL> insert into testd values (4, 'Maths & Science', 444);
Enter value for science:                                 <<<<< we notice it is prompting us to substitute a value

-- so we define substitution off as follows >>> 
SQL> set define off;
SQL> insert into testd values (4, 'Maths & Science', 444);
1 row created.
SQL> commit;
Commit complete.

SQL> select  * from testd;

    DEPTID DEPTNAME                                            FACULTYID
---------- -------------------------------------------------- ----------
         4 Maths & Science                                           444

Unix scripts for DBA's

Remove files in a directory older than certain number of days

The below script takes 2 arguments 

1. first argument specifies the location where the files will be removed & also the location where the log of files being removed is created
2. second argument takes a number value & specifies files older than this number of days will be removed

[techie@lnx133 ~]$ cat del2.sh
#!/bin/ksh
dt=`date +%Y%m%d_%H%M%S`
path=$1
daysback=$2
file=logofdeletedfiles_$dt.txt
log=$path/$file
echo "Beginning to remove files at $path " >> $log
find $path -name "*.tx" -type f -mtime +$daysback -print -delete >>$log

echo "files removal past $daysback  days complete">> $log

Sometimes you need to do house keeping & clean up files of "0" byte size

Script below takes only one argument which specifies a location where the 0 byte size files have to be looked/removed & the logfile listing those files is to be created.

[oracle@lnx133 ~]$ cat zerodel.sh

#!/bin/ksh

dt=`date +%Y%m%d_%H%M%S`

path=$1

file=zerosizedel.txt

log=$path/$file

echo "Beginning to delete files at $path which are 0bytes in size ">>$log

find $path -type f -size 0 -print -delete >>$log

echo "Completed removing files at $path which were 0bytes in size">>$log

[oracle@lnx133 ~]$

RAC administration using server control utility "srvctl"

Hopefully this serves as a reminder...

start database
srvctl start database -d dbname
srvctl start db -d dbname

stop database
srvctl stop database -d dbname
srvctl stop db -d dbname


Start instance
srvctl start instance -d dbname -i instance_name

stop instance
srvctl stop instance -d dbname -i instance_name

get info of environment variables
srvctl getenv db -d dbname

set info of environment variables like ORACLE_BASE & time zone

srvctl setenv db -d {dbname} -t TZ=GMT
srvctl setenv database -d  {dbname} -t "ORACLE_BASE=/app/oracle"

Connecting through SCAN listener

Utilizing SCAN name to connect to a RAC database

First to get details on scan
xxxxxlnx447:/app/grid/11.2.0.3/log/xxxxxlnx447 ->srvctl config scan
SCAN name: xxxxx-scan.xxx.com, Network: 1/142.11.xx7.0/255.255.255.0/eth0
SCAN VIP name: scan1, IP: /xxxxx-scan.xxx.com/142.11.xx7.x8
SCAN VIP name: scan2, IP: /xxxxx-scan.xxx.com/142.11.xx7.x7
SCAN VIP name: scan3, IP: /xxxxx-scan.xxx.com/142.11.xx7.x6

C:\Users\xxxxxx>sqlplus username/passwd@xxxxx-scan.xxx.com:1521/service.xxx.com

Drop database & Deinstall oracle database software

Drop database & Deinstall Oracle database software