Oracle Tips and Tricks

Tipps

Set environment in .bash_profile

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}

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:

ORA-00376: file 18 cannot be read at this time
ORA-01110: data file 18: '/data/oracle/oradata/datafile04.dbf'

SQL> select * from v$recover_file;

     FILE# ONLINE  ONLINE_
---------- ------- -------
ERROR                                                                CHANGE#
----------------------------------------------------------------- ----------
TIME
---------
        18 OFFLINE OFFLINE
                                                                  5.8016E+12
22-JUL-15

SQL> select ONLINE_STATUS from dba_data_files where file_id = 18;

ONLINE_
-------
RECOVER

Recover datafile:

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.

Set file online:

SQL> alter database datafile 18 online

Anything else?

SQL> select * from v$recover_file;

no rows selected

Show non default settings

SQL> select name || ' = ' || value from v$parameter where isdefault = 'FALSE';

Show CPU count from database

SQL> SELECT 'DATABASE CPU COUNT: ' || value ||
       decode(ISDEFAULT, 'TRUE', ' (ISDEFAULT)', ' (IS NOT DEFAULT !!!: '|| ISDEFAULT ||')')
       from V$PARAMETER where UPPER(name) like '%CPU_COUNT%'

Startup some Databases manually

For example: First DEVDE, than all other DEV*

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

Shutdown some Databases manually

For example: First all other DEV*, than DEVDE

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

Get session id (sid) of system process id (pid)

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;