RMAN: Restore controlfiles

Sometimes, your loose all your controlfiles, your instance is down, and you think you're in a big sh**. But here's a solution.


Check your last controlfile backup to get your DBID :

ls -l *.bck
-rw-rw---- 1 oracle oracle   10092544 Apr 15 15:49 c-159519437-20110415-06.bck

The DBID is the first number block : 159519437


Then, go to RMAN and restore your controlfile from the last backup:

rman target /

set DBID=159519437

startup nomount

set controlfile autobackup format for device type disk to '/backup/file/location/%F';

restore controlfile from autobackup;

The last action, recover and start the database

recover database;

alter database open resetlogs;

Linux: Generate a random password

The main script, has to be saved in a separate file, or modified as a function:

#!/bin/bash

#add special characters you want to use in the password here:
charspool=('a' 'b' 'c' 'd' 'e' 'f' 'g' 'h' 'i' 'j' 'k' 'l' 'm' 'n' 'o' 'p'
'q' 'r' 's' 't' 'u' 'v' 'w' 'x' 'y' 'z' '0' '1' '2' '3' '4' '5' '6' '7'
'8' '9' '0' 'A' 'B' 'C' 'D' 'E' 'F' 'G' 'H' 'I' 'J' 'K' 'L' 'M' 'N' 'O'
'P' 'Q' 'R' 'S' 'T' 'U' 'V' 'W' 'X' 'Y' 'Z' '$' '-' '_');


len=${#charspool[*]}

if [ $# -lt 1 ]; then
        num=$1;

else
        num=$1;
fi
randomnumbers=$(head -c $num /dev/urandom | od -t u1 | awk '{for (i = 2; i <= NF; i++) print $i}')
for c in $randomnumbers; do
        echo -n ${charspool[$((c % len))]}
done
echo

and to call it:

lenPassword=8
# echo $(/path/to/script/gen_password.sh $lenPassword);

Linux: Allow a user to execute only specific root command(s)

Sometimes you want a linux user to be able to execute root command(s). To do so, you need to modify the /etc/sudoers file (with visudo).

However, almost everyone add a line like the below:

username ALL=(ALL:ALL) ALL

Which is far from being the right thing to do. You basically just gave the user username all root rights... To do it properly, you should know the synopsis of that line:

user machine=(effective user [ : effective group ] command

With:
user : the user you want to grant access to some commands. it can be a group too, just replace user by %groupname
machine : the machine you want those rights to apply (can be a hostname, IP, ALL)
effective user : the user's rights you want your user to execute the command as (root, user1, ALL, etc)
effective group : same as above, but for the group. This is not mandatory
command : the command or command list (separated by comma. Ex. /bin/chown,reboot)

An example is allays better than a long talk, if you want to give the user user1 the rights to execute the chown command:

user1 ALL=(root) /bin/chown

Oracle DB ratios explained

Here you will find two pdf (in french and english) with the explanation of the following ratios, and what to do to improve them, as well as your database performances:

• Buffer Cache Hit Ratio 
• Chained Row Ratio 
• Database CPU Time Ratio
• Database Wait Time Ratio
• Dictionary Cache Hit Ratio 
• Execute to Parse Ratio 
• Get Hit Ratio 
• Latch Hit Ratio 
• Library Cache Hit Ratio 
• Parse CPU to Elapsed Ratio 
• Pin Hit Ratio 
• Soft-Parse Ratio 
• Library cache reload Ratio
• Rollback Segment Wait to Get Ratio
• In (PGA) Memory Sort Ratio
• Buffer Nowait Ratio
• Redo Nowait Ratio

In english

In french

You can hit the gv$sysmetric view to most of those ratios.

DBMS_LOGMNR, or "How to see what's in the redo logs"

Contents:
1 Database-Level Supplemental Logging
2 Redo Log File Options
3 Example of Querying V$LOGMNR_CONTENTS
4 End the LogMiner Session

Database-Level Supplemental Logging:

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

Redo Log File Options

Here you will tell you LOGMNR whate logfiles to look into. It can be done:

Automatically. Just define the time range you want to observe and start logmnr.

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';

EXECUTE DBMS_LOGMNR.START_LOGMNR( STARTTIME => '01-Jan-2003 08:30:00', ENDTIME => '01-Jan-2003 08:45:00', OPTIONS =>
DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE);

Manually. You need to manually add every logfile you're interested in and then start logmnr.

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '/oracle/logs/log1.f', OPTIONS => DBMS_LOGMNR.NEW);

EXECUTE DBMS_LOGMNR.ADD_LOGFILE( LOGFILENAME => '/oracle/logs/log.f', OPTIONS => DBMS_LOGMNR.ADDFILE);

EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);

Example of Querying V$LOGMNR_CONTENTS

SELECT OPERATION, SQL_REDO, SQL_UNDO
  FROM V$LOGMNR_CONTENTS
  WHERE SEG_OWNER = 'OE' AND SEG_NAME = 'ORDERS' AND
  OPERATION = 'DELETE' AND USERNAME = 'RON';

End the LogMiner Session

EXECUTE DBMS_LOGMNR.END_LOGMNR;

Note: Everything is related to your session, so if you got disconnected, start again

Source: http://download.oracle.com/docs/cd/B28359_01/server.111/b28319/logminer.htm#i1015913

Oracle: Manage your tablespaces, useful queries

Show the free available space:

SELECT
  ts.tablespace_name,
  TO_CHAR(SUM(NVL(fs.bytes,0))/1024/1024, '99,999,990.99') AS MB_FREE
FROM
  dba_free_space fs,
  dba_tablespaces ts
WHERE fs.tablespace_name(+)   = ts.tablespace_name
and ts.tablespace_name not in ('SYSAUX', 'SYSTEM', 'TEMP', 'TEMPBIG')
and ts.tablespace_name not like '%UNDO%'
GROUP BY
  ts.tablespace_name

Show more details for tablespace size management:

select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files 
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments 
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name 
order by tu.tablespace_name;

Coalesce a tablespace.

Alter tablespace TABLESPACE_NAME coalesce; 

Resize the datafiles to a minimum space.

select 'alter database datafile ''' || file_name || ''' resize ' || CEIL( ( NVL( hwm,1) * blksize ) / 1024 / 1024 ) || 'm;' cmd
from dba_data_files a,
  (select file_id, max(block_id + blocks - 1) hwm from dba_extents group by file_id) b,
  (select to_number(value) blksize from v$parameter where name = 'db_block_size') c
where a.file_id = b.file_id (+)
and CEIL(blocks * c.blksize / 1024 /1024) - CEIL((nvl(b.hwm,1) * c.blksize) / 1024 /1024) > 0
and tablespace_name not in ('SYSTEM','TEMP') and tablespace_name not like '%UNDO%';

See the biggest objects in the SYSTEM tablespace:

  select owner,segment_name,segment_type
 ,bytes/(1024*1024) size_m
 from dba_segments
 where tablespace_name = 'SYSTEM'
 and    bytes/(1024*1024) > 1
 order by size_m desc

Oracle: Archivelog activity

This will give you the number of logfile switch per hour. You will then be able to see the pics of activity (ie. data that has to be written) in your redo log.
It's similar than checking the number of "archivelog current", the only difference is with the writting mode in the logs. archivelog current is synchronous, switch logfile is asynchronous.

Date format: DD/MM/YYYY

SELECT
    SUBSTR(TO_CHAR(first_time, 'DD/MM/RR HH:MI:SS'),1,5)                          DAY
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'00',1,0)) H00
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'01',1,0)) H01
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'02',1,0)) H02
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'03',1,0)) H03
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'04',1,0)) H04
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'05',1,0)) H05
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'06',1,0)) H06
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'07',1,0)) H07
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'08',1,0)) H08
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'09',1,0)) H09
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'10',1,0)) H10
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'11',1,0)) H11
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'12',1,0)) H12
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'13',1,0)) H13
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'14',1,0)) H14
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'15',1,0)) H15
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'16',1,0)) H16
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'17',1,0)) H17
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'18',1,0)) H18
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'19',1,0)) H19
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'20',1,0)) H20
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'21',1,0)) H21
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'22',1,0)) H22
  , SUM(DECODE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH24:MI:SS'),10,2),'23',1,0)) H23
  , COUNT(*)                                                                      TOTAL
FROM
  v$log_history  a
WHERE
    TO_DATE(SUBSTR(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'), 1,8), 'MM/DD/RR') >=  TO_DATE('&startDate', 'DD/MM/YYYY')
AND
    TO_DATE(substr(TO_CHAR(first_time, 'MM/DD/RR HH:MI:SS'), 1,8), 'MM/DD/RR')   <=   TO_DATE('&endDate', 'DD/MM/YYYY')

GROUP BY SUBSTR(TO_CHAR(first_time, 'DD/MM/RR HH:MI:SS'),1,5)

You will have a result similar to this:

It will help you to find the times where your db has the most activity in data writing.