Exadata: Interactive DB Environment settings while user login

||||| 8 I Like It! |||||


Now a days, Oracle Exadata is replacing existing Oracle DB Hardware. Due to huge capacity on Exadata Servers, it is often seen that, customers starts using several DB instances on a single Exadata Box. Suppose there are 5 DB Homes and 25 DB instances distributed unevenly on it. So getting into the server using Oracle user and again set environment (for eg. Oracle_HOME, ORACLE_SID etc) takes some time and this just thought to publish a Unix shell script to have interactive Login session to setup ORACLE_HOME, ORACLE_SID etc. Below script will also gives you option to check last Ora- error messages in instance ALERT FILEs.

Script to check last Ora error Message

[oracle@Serveradm01 ~]$vi <PATH>/Custom_PROFILE.sh

printf “\n  Please choose the ORACLE_HOME 1. <PATH1>/12.1.0”
printf “\n                                2. <PATH2>/dbhome1”

printf “\n Enter Your SELECT (Either 1 or 2): ”
if [ “$SELECT” = “1” ]
export ORACLE_HOME=”<PATH>/12.1.0″
printf “\n  Please choose the ORACLE_SID:”
printf “\n      1. DEV1”
printf “\n      2. TST1”
printf “\n      3. TST3”
printf “\n      4. TST2”
printf “\n      5. GALA1”
printf “\n      6. ST11”
printf “\n      7. UAT1”
printf “\n      8. PROD1”
printf “\n      9. PREPROD1”
printf “\n Enter your SELECT of SID: ”
read SID
case “$SID” in
“1”) export ORACLE_SID=DEV1;;
“2”) export ORACLE_SID=TST1;;
“3”) export ORACLE_SID=TST3;;
“4”) export ORACLE_SID=TST2 ;;
“5”) export ORACLE_SID=GALA1 ;;
“6”) export ORACLE_SID=ST11 ;;
“7”) export ORACLE_SID=UAT1 ;;
“8”) export ORACLE_SID=PROD1 ;;
“9”) export ORACLE_SID=PREPROD1 ;;

printf “\n\n”
printf “\n\n”
AlertFilelocation=`$ORACLE_HOME/bin/sqlplus -s “/ as sysdba” <<EOF
set pagesize 0 heading off feedback off verify off echo off
select value from V\\$DIAG_INFO where name =’Diag Trace’;
echo -e “\x1b[31;42m “You may need to check DB alert log file $AlertFilelocation for below Ora- errors\(Extract of Last 1000 lines of Alert log file\)” \x1b[m”
printf “\n”
echo `tail -1000 $AlertFilelocation|grep -i ora-`
elif [ “$SELECT” = “2” ]
export ORACLE_HOME=”<PATH2>dbhome1″
echo “You have selected ORACLE_HOME as $ORACLE_HOME”
printf “\n”
echo “Please explicitly set you ORACLE_SID to continue your work”
[oracle@Serveradm01 ~]$
Add above script into the .bash_profile of Oracle user so that login procedure can become interactive.

Output of the above Script

ORACLE@Serveradm01$. custom_profile.sh

Please choose the ORACLE_HOME 1. <PATH1>/12.1.0
2. <PATH2>
Enter Your Choice (Either 1 or 2): 1

Please choose the ORACLE_SID:
1. DEV1
2. TST11
3. TST31
4. TST21
5. GOLD1
6. ST11
7. UAT1
8. PROD1

Enter your Choice of SID: 2

You have selected ORACLE_HOME as <PATH1>/12.1.0 and ORACLE_SID as TST1

You may need to check DB alert log file <Path1>/tst1/TST1/trace/alert_TST1.log for below Ora- errors(Extract of Last 1000 lines of Alert log file)

ORA-1654: unable to extend index SYS.I_WRI$_ABC by 1024 in tablespace SYSAUX
ORA-1654: unable to extend index SYS.I_WRI$_ABC by 1024 in tablespace SYSAUX

This is just a sample script which can be extended to fulfill your requirement.

Hope, this will help you to stream line your User login process….

Happy Learning!!!!!!

Related Oracle Posts:
Explaining Oracle GoldenGate Active-Active for Layman
Oracle GoldenGate Configuration steps
Oracle Database Optimizer Hints : Use or Not To Use?
Step by Step Oracle Single Instance Database Cloning/Refresh: Using RMAN HOT BACKUP
Oracle DB: Capacity Management: Environment Sizing


1 Comment

Powered by themekiller.com watchanimeonline.co