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