Oracle Tips and Tricks: Difference between revisions

From Lolly's Wiki
Jump to navigationJump to search
m (Text replacement - "<source" to "<syntaxhighlight")
Line 3: Line 3:
==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 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>
</source>


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


Line 42: Line 42:
</source>
</source>


<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 51: Line 51:


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 69: Line 69:


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>
</source>


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


Line 81: Line 81:


==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>
</source>


==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 ||')')
Line 94: Line 94:
==Startup some Databases manually==
==Startup some Databases manually==
For example: First DEVDE, than all other DEV*
For example: First DEVDE, than all other DEV*
<source lang=bash>
<syntaxhighlight lang=bash>
for SID in DEVDE $(awk -F':' '$1 ~ /^DEV/ && $1 !~ /^DEVDE$/ {print $1}' /var/opt/oracle/oratab )
for SID in DEVDE $(awk -F':' '$1 ~ /^DEV/ && $1 !~ /^DEVDE$/ {print $1}' /var/opt/oracle/oratab )
do
do
Line 106: Line 106:
==Shutdown some Databases manually==
==Shutdown some Databases manually==
For example: First all other DEV*, than DEVDE
For example: First all other DEV*, than DEVDE
<source lang=bash>
<syntaxhighlight lang=bash>
for SID in $(awk -F':' '$1 ~ /^DEV/ && $1 !~ /^DEVDE$/ {print $1}' /var/opt/oracle/oratab ) DEVDE
for SID in $(awk -F':' '$1 ~ /^DEV/ && $1 !~ /^DEVDE$/ {print $1}' /var/opt/oracle/oratab ) DEVDE
do
do
Line 117: Line 117:


==Get session id (sid) of system process id (pid)==
==Get session id (sid) of system process id (pid)==
<source lang=sql>
<syntaxhighlight lang=sql>
col sid format 999999
col sid format 999999
col username format a20
col username format a20

Revision as of 18:02, 25 November 2021

Tipps

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>

Retrieved from "https://lars.timmann.de/wiki/index.php?title=Oracle_Tips_and_Tricks&oldid=2331"