Oracle Tips and Tricks: Difference between revisions

From Lolly's Wiki
Jump to navigationJump to search
m (Lollypop verschob die Seite Oracle Tipps und Tricks nach Oracle Tips and Tricks)
m (Text replacement - "[[Kategorie:" to "[[Category:")
 
(6 intermediate revisions by the same user not shown)
Line 1: Line 1:
[[Kategorie:Oracle|Tipps]]
[[Category:Oracle|Tipps]]


==Set environment in .bash_profile==
==Set environment in .bash_profile==


<source lang=bash>
<syntaxhighlight lang=bash>
ORATAB=/etc/oratab  # <-- maybe somwhere else?
ORATAB=/etc/oratab  # <-- maybe somwhere else?


Line 17: Line 17:
   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}"
   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}
done < ${ORATAB}
</source>
</syntaxhighlight>


After that .bash_profile is sourced (as done at login) you have aliases as lower case ORACLE_SIDs that set all you need.
After that .bash_profile is sourced (as done at login) you have aliases as lower case ORACLE_SIDs that set all you need.
Line 23: Line 23:
==Recover datafiles==
==Recover datafiles==
Problem:
Problem:
<source lang=oracle11>
<syntaxhighlight lang=oracle11>
ORA-00376: file 18 cannot be read at this time
ORA-00376: file 18 cannot be read at this time
ORA-01110: data file 18: '/data/oracle/oradata/datafile04.dbf'
ORA-01110: data file 18: '/data/oracle/oradata/datafile04.dbf'
</source>
</syntaxhighlight>


<source lang=oracle11>
<syntaxhighlight lang=oracle11>
SQL> select * from v$recover_file;
SQL> select * from v$recover_file;


Line 40: Line 40:
                                                                   5.8016E+12
                                                                   5.8016E+12
22-JUL-15
22-JUL-15
</source>
</syntaxhighlight>


<source lang=oracle11>
<syntaxhighlight lang=oracle11>
SQL> select ONLINE_STATUS from dba_data_files where file_id = 18;
SQL> select ONLINE_STATUS from dba_data_files where file_id = 18;


Line 48: Line 48:
-------
-------
RECOVER
RECOVER
</source>
</syntaxhighlight>


Recover datafile:
Recover datafile:
<source lang=oracle11>
<syntaxhighlight lang=oracle11>
SQL> recover datafile 18;
SQL> recover datafile 18;
ORA-00279: change 5801623243148 generated at 07/22/2015 21:26:51 needed for thread 1
ORA-00279: change 5801623243148 generated at 07/22/2015 21:26:51 needed for thread 1
Line 66: Line 66:
Log applied.
Log applied.
Media recovery complete.
Media recovery complete.
</source>
</syntaxhighlight>


Set file online:
Set file online:
<source lang=oracle11>
<syntaxhighlight lang=oracle11>
SQL> alter database datafile 18 online
SQL> alter database datafile 18 online
</source>
</syntaxhighlight>


Anything else?
Anything else?
<source lang=oracle11>
<syntaxhighlight lang=oracle11>
SQL> select * from v$recover_file;
SQL> select * from v$recover_file;


no rows selected
no rows selected
</source>
</syntaxhighlight>


==Show non default settings==
==Show non default settings==
<source lang=oracle11>
<syntaxhighlight lang=oracle11>
SQL> select name || ' = ' || value from v$parameter where isdefault = 'FALSE';  
SQL> select name || ' = ' || value from v$parameter where isdefault = 'FALSE';  
</source>
</syntaxhighlight>


==Show CPU count from database==
==Show CPU count from database==
<source lang=oracle11>
<syntaxhighlight lang=oracle11>
SQL> SELECT 'DATABASE CPU COUNT: ' || value ||
SQL> SELECT 'DATABASE CPU COUNT: ' || value ||
       decode(ISDEFAULT, 'TRUE', ' (ISDEFAULT)', ' (IS NOT DEFAULT !!!: '|| ISDEFAULT ||')')
       decode(ISDEFAULT, 'TRUE', ' (ISDEFAULT)', ' (IS NOT DEFAULT !!!: '|| ISDEFAULT ||')')
       from V$PARAMETER where UPPER(name) like '%CPU_COUNT%'
       from V$PARAMETER where UPPER(name) like '%CPU_COUNT%'
</source>
</syntaxhighlight>
 
==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
</syntaxhighlight>
 
==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
</syntaxhighlight>
 
==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;
</syntaxhighlight>

Latest revision as of 21:07, 25 November 2021


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;
Retrieved from "https://lars.timmann.de/wiki/index.php?title=Oracle_Tips_and_Tricks&oldid=2379"