Oracle Tips and Tricks
Set environment in .bash_profile
<syntaxhighlight lang=bash> ORATAB=/etc/oratab # <-- maybe somwhere else?
declare -A ORACLE_HOMES
export BASE_PATH=${PATH} while IFS=$': \t\n' read ORACLE_SID ORACLE_HOME DBSTART do
# Ignore empty lines, commented lines (#) and ORACLE_SIDs starting with + or - (RAC)
if [[ ${ORACLE_SID} =~ ^(#|[\t ]*$|[-+]) ]] ; then continue ; fi
ALIASNAME=${ORACLE_SID,,*}
eval ORACLE_HOMES["${ORACLE_SID}"]=${ORACLE_HOME}
alias ${ALIASNAME}="export ORACLE_SID=${ORACLE_SID}; export ORACLE_HOME=\${ORACLE_HOMES[${ORACLE_SID}]}; export PATH=\${ORACLE_HOMES[${ORACLE_SID}]}/bin:\${ORACLE_HOMES[${ORACLE_SID}]}/OPatch:\${BASE_PATH}"
done < ${ORATAB} </source>
After that .bash_profile is sourced (as done at login) you have aliases as lower case ORACLE_SIDs that set all you need.
Recover datafiles
Problem: <syntaxhighlight lang=oracle11> ORA-00376: file 18 cannot be read at this time ORA-01110: data file 18: '/data/oracle/oradata/datafile04.dbf' </source>
<syntaxhighlight lang=oracle11> SQL> select * from v$recover_file;
FILE# ONLINE ONLINE_
------- -------
ERROR CHANGE#
----------
TIME
18 OFFLINE OFFLINE
5.8016E+12
22-JUL-15 </source>
<syntaxhighlight lang=oracle11> SQL> select ONLINE_STATUS from dba_data_files where file_id = 18;
ONLINE_
RECOVER </source>
Recover datafile: <syntaxhighlight lang=oracle11> SQL> recover datafile 18; ORA-00279: change 5801623243148 generated at 07/22/2015 21:26:51 needed for thread 1 ORA-00289: suggestion : /data/oracle/arclog/ORACLESID_1946_1_882824275.ARC ORA-00280: change 5801623243148 for thread 1 is in sequence #1946 ORA-00278: log file '/data/oracle/arclog/ORACLESID_1945_1_882824275.ARC' no longer needed for this recovery
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
Log applied.
Media recovery complete.
</source>
Set file online: <syntaxhighlight lang=oracle11> SQL> alter database datafile 18 online </source>
Anything else? <syntaxhighlight lang=oracle11> SQL> select * from v$recover_file;
no rows selected </source>
Show non default settings
<syntaxhighlight lang=oracle11> SQL> select name || ' = ' || value from v$parameter where isdefault = 'FALSE'; </source>
Show CPU count from database
<syntaxhighlight lang=oracle11> SQL> SELECT 'DATABASE CPU COUNT: ' || value ||
decode(ISDEFAULT, 'TRUE', ' (ISDEFAULT)', ' (IS NOT DEFAULT !!!: '|| ISDEFAULT ||')')
from V$PARAMETER where UPPER(name) like '%CPU_COUNT%'
</source>
Startup some Databases manually
For example: First DEVDE, than all other DEV* <syntaxhighlight lang=bash> for SID in DEVDE $(awk -F':' '$1 ~ /^DEV/ && $1 !~ /^DEVDE$/ {print $1}' /var/opt/oracle/oratab ) do
export ORAENV_ASK=NO ORACLE_SID=${SID}
. oraenv
printf "startup\nquit\n" | sqlplus -s "/ as sysdba"
lsnrctl start ${SID}
done </source>
Shutdown some Databases manually
For example: First all other DEV*, than DEVDE <syntaxhighlight lang=bash> for SID in $(awk -F':' '$1 ~ /^DEV/ && $1 !~ /^DEVDE$/ {print $1}' /var/opt/oracle/oratab ) DEVDE do
export ORAENV_ASK=NO ORACLE_SID=${SID}
. oraenv
lsnrctl stop ${SID}
printf "shutdown immediate\nquit\n" | sqlplus -s "/ as sysdba"
done </source>
Get session id (sid) of system process id (pid)
<syntaxhighlight lang=sql> col sid format 999999 col username format a20 col osuser format a15 select b.spid,a.sid, a.serial#,a.username, a.osuser from v$session a, v$process b where a.paddr= b.addr and b.spid='&spid' order by b.spid; </source>
