Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Thursday, October 28, 2010

Archive log switches frequency

set linesize 1000
col MidN format 999
col 1AM format 999
col 2AM format 999
col 3AM format 999
col 4AM format 999
col 5AM format 999
col 6AM format 999
col 7AM format 999
col 8AM format 999
col 9AM format 999
col 10AM format 999
col 11AM format 999
col Noon format 999
col 1PM format 999
col 2PM format 999
col 3PM format 999
col 4PM format 999
col 5PM format 999
col 6PM format 999
col 7PM format 999
col 8PM format 999
col 9PM format 999
col 10PM format 999
col 11PM format 999
select to_char(first_time,'mm/dd/yy') logdate,
sum(decode(to_char(first_time,'hh24'),'00',1,0)) "MidN",
sum(decode(to_char(first_time,'hh24'),'01',1,0)) "1AM",
sum(decode(to_char(first_time,'hh24'),'02',1,0)) "2AM",
sum(decode(to_char(first_time,'hh24'),'03',1,0)) "3AM",
sum(decode(to_char(first_time,'hh24'),'04',1,0)) "4AM",
sum(decode(to_char(first_time,'hh24'),'05',1,0)) "5AM",
sum(decode(to_char(first_time,'hh24'),'06',1,0)) "6AM",
sum(decode(to_char(first_time,'hh24'),'07',1,0)) "7AM",
sum(decode(to_char(first_time,'hh24'),'08',1,0)) "8AM",
sum(decode(to_char(first_time,'hh24'),'09',1,0)) "9AM",
sum(decode(to_char(first_time,'hh24'),'10',1,0)) "10AM",
sum(decode(to_char(first_time,'hh24'),'11',1,0)) "11AM",
sum(decode(to_char(first_time,'hh24'),'12',1,0)) "Noon",
sum(decode(to_char(first_time,'hh24'),'13',1,0)) "1PM",
sum(decode(to_char(first_time,'hh24'),'14',1,0)) "2PM",
sum(decode(to_char(first_time,'hh24'),'15',1,0)) "3PM",
sum(decode(to_char(first_time,'hh24'),'16',1,0)) "4PM",
sum(decode(to_char(first_time,'hh24'),'17',1,0)) "5PM",
sum(decode(to_char(first_time,'hh24'),'18',1,0)) "6PM",
sum(decode(to_char(first_time,'hh24'),'19',1,0)) "7PM",
sum(decode(to_char(first_time,'hh24'),'20',1,0)) "8PM",
sum(decode(to_char(first_time,'hh24'),'21',1,0)) "9PM",
sum(decode(to_char(first_time,'hh24'),'22',1,0)) "10PM",
sum(decode(to_char(first_time,'hh24'),'23',1,0)) "11PM"
from v$log_history
group by to_char(first_time,'mm/dd/yy')
order by 1
/

Picked up the above from google:

Size of the redo log file:

SQL> SELECT distinct(to_char((bytes*0.000001),'9990.999')) size_mb
FROM v$log;
2
SIZE_MB
---------
1073.742


size & number of redologs by day:

SELECT trunc(first_time) DAY,
count(*) NB_SWITCHS,
trunc(count(*)*log_size/1024/1024/1024) TOTAL_SIZE_GB,
to_char(count(*)/24,'9999.9') AVG_SWITCHS_PER_HOUR
FROM v$loghist,
(select avg(bytes) log_size from v$log) GROUP BY trunc(first_time),log_size
order by 1 desc
/

Monday, October 25, 2010

ld: fatal: library -lclntsh: not found during 10.2.0.5 install

INFO: Linking /cti01/u0001/oracle/product/10.2.0/asm/precomp/lib/proc
INFO: ld: fatal: library -lclntsh: not found
INFO: ld: fatal: File processing errors. No output written to /cti01/u0001/oracle/product/10.2.0/asm/precomp/lib/proc
INFO: make: Fatal error: Command failed for target `/cti01/u0001/oracle/product/10.2.0/asm/precomp/lib/proc'


You are installing 10.2.0.5 on SunOS64bit servers and get one of the above relinking errors. To overcome this issues use the below work around as per support...

Make sure this is for 10.2.0.5

1. Backup the Oracle_Home (as directed in the Patch Set Notes)

remove the 10205 installation u just did if not start from fresh
install 10.2.0.1 Then

2. Delete these files ...
$ cd $ORACLE_HOME
$ rm lib/libncds10.a
rm network/install/ldflags.cds
rm lib/libndce10.a
rm network/install/ldflags.dce

3. Apply the 10.2.0.5 patchset

Friday, October 22, 2010

Read Alert log file in 11G

in 11g there is no alert.log instead there is alert.xml and view the contents of this file Oracle provides a utility called ADRI see below on how to use this utility:

HOST: /rman01/u0001/oracle/diag/rdbms/adm1p/ADM1P/alert
>adrci

ADRCI: Release 11.2.0.1.0 - Production on Fri Oct 22 09:35:01 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

ADR base = "/rman01/u0001/oracle"
adrci>

adrci> help

HELP [topic]
Available Topics:
CREATE REPORT
ECHO
EXIT
HELP
HOST
IPS
PURGE
RUN
SET BASE
SET BROWSER
SET CONTROL
SET ECHO
SET EDITOR
SET HOMES | HOME | HOMEPATH
SET TERMOUT
SHOW ALERT
SHOW BASE
SHOW CONTROL
SHOW HM_RUN
SHOW HOMES | HOME | HOMEPATH
SHOW INCDIR
SHOW INCIDENT
SHOW PROBLEM
SHOW REPORT
SHOW TRACEFILE
SPOOL

There are other commands intended to be used directly by Oracle, type
"HELP EXTENDED" to see the list

adrci> show alert

ADR Home = /rman01/u0001/oracle/diag/rdbms/adm1p/ADM1P:
*************************************************************************
Output the results to file: /tmp/alert_7353_1_ADM1P_1.ado
"/tmp/alert_7353_1_ADM1P_1.ado" 2102 lines, 108992 characters
2010-08-25 16:59:50.107000 -04:00
Adjusting the default value of parameter parallel_max_servers

Tuesday, October 19, 2010

RMAN Delete old archivelogs

delete force noprompt archivelog all completed before 'sysdate-7';

If you want to delete archive logs older than few hours:

Verify the time like how many hours older than you want to delete.

SQL> alter session set nls_date_format='MM/DD/YYYY HH24:MI:SS';

Session altered.

SQL> select sysdate-1/11 from dual;


SYSDATE-1/11
-------------------
09/22/2011 08:33:05

SQL> SQL>
SQL> select sysdate from dual;

SYSDATE
-------------------
09/22/2011 10:44:15

SQL>

So sysdate-1/11 will delete the files older than 8:30 am.

RMAN>connect target /
delete noprompt archivelog until time '(sysdate-1/11)' backed up 1 times to device type sbt_tape;

or

RMAN> delete force noprompt obsolete;
RMAN> delete force noprompt expired;
cheers.

Monday, October 11, 2010

lib/libclntsh.a(shr.o) could not be loaded

User other than oracle was trying to access the libraries and getting the below errors:

$exec(): 0509-036 Cannot load program FastReader because of the following errors:
0509-150 Dependent module $ORACLE_HOME/lib/libclntsh.a(shr.o) could not be loaded.
0509-022 Cannot load module $ORACLE_HOME/lib/libclntsh.a(shr.o).
0509-026 System error: The file access permissions do not allow the specified action.

Fix:
in 10g Oracle introduced strong security in accessing the oracle binaries, so to flex this security run the below command:

cd $ORACLE_HOME/install
>./changePerm.sh

-------------------------------------------------------------------------------
Disclaimer: The purpose of this script is to relax permissions on some of the
files in the database Oracle Home so that all clients can access them.
Please note that Oracle Corporation recommends using the most restrictive file
permissions as possible for your given implementation. Running this script
should be done only after considering all security ramifications.
-------------------------------------------------------------------------------

-n Do you wish to continue (y/n) [n]:
y
Spooling the error log /tmp/changePerm_err.log...
Finished running the script successfully


This will do the job... cheers