Anuj Singh Oracle DBA: 06/01/2011

Thursday, 30 June 2011

Unix Kill all the process for the user

latest command

Don't try this command on prod ...

$ kill -9 %1

Oracle Sort Info

Oracle Sort Info In PGA as well

select a.event,
a.sid,
c.serial# ,
c.sql_hash_value hash_value,
decode(d.ktssosegt,1,'SORT', 2,'HASH',3,'DATA',4,'INDEX',5,'LOB_DATA',6,'LOB_INDEX',null) as segment_type,
b.tablespace_name,
b.file_name
from v$session_wait a, dba_data_files b, v$session c, x$ktsso d
where c.saddr = d.ktssoses(+)
and c.serial# = d.ktssosno(+)
and d.inst_id(+) = userenv('instance')
and a.sid = c.sid
and a.p1 = b.file_id
and a.event = 'direct path read'
union all
select a.event,
a.sid,
d.serial# ,
d.sql_hash_value hash_value,
decode(e.ktssosegt,1,'SORT', 2,'HASH',3,'DATA',4,'INDEX',5,'LOB_DATA',6,'LOB_INDEX', null) as segment_type,
b.tablespace_name,
b.file_name
from v$session_wait a, dba_temp_files b, v$parameter c, v$session d, x$ktsso e
where d.saddr = e.ktssoses(+)
and d.serial# = e.ktssosno(+)
and e.inst_id(+) = userenv('instance')
and a.sid = d.sid
and b.file_id = a.p1 - c.value
and c.name = 'db_files'
and a.event = 'direct path read'
order by 1,2;

Oracle Weblogic Server patch info

Patch WDJ7 is mapped to patch 9438213 which is a combo patch includes the following patches:

- Patch 4D53 is mapped to patch 9100465
- Patch XLXA is mapped to patch 9221722
- Patch NIXN is mapped to patch 8990616

(Doc ID 1116655.1) - How to manually patch the 11g Grid Control Weblogic Server prior to installing Grid Control.

(Doc ID 1072763.1) - How to Download and Apply recommended WLS patch (WDJ7) on WLS home for 11g Grid Control Install/Upgrade

Oracle waiting ---

Oracle Waiting currently ......

Oracle Waits

select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill , username, blocking_session,seconds_in_wait, wait_time ,event
from gv$session s 
where state = 'WAITING'
and wait_class != 'Idle';



set linesize 300 pagesize 300 
col SQL_TEXT for a70 wrap
select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill , s.sql_id ,s.prev_sql_id,sql_text from gv$session s, gv$sql q
where sid = &sid
and (q.sql_id = s.sql_id or q.sql_id = s.prev_sql_id )
and q.inst_id=s.inst_id;


select wait_class_id, wait_class,total_waits, time_waited from gv$session_wait_class where sid = &sid;



WAIT_CLASS_ID WAIT_CLASS                                                       TOTAL_WAITS TIME_WAITED
------------- ---------------------------------------------------------------- ----------- -----------
1893977003 Other                                                                     31        7022
3875070507 Concurrency                                                               19          14
3386400367 Commit                                                                     2           0
2723168908 Idle                                                                      36        3300
2000153315 Network                                                                   36           0
1740759767 User I/O                                                               16344        1074

6 rows selected.


select event, total_waits, time_waited from v$system_event e, v$event_name n
where n.event_id = e.event_id
and e.wait_class_id = &wait_class_id ;


EVENT                                                            TOTAL_WAITS TIME_WAITED
---------------------------------------------------------------- ----------- -----------
os thread startup                                                      12601      148864
latch: cache buffers chains                                            65878       55103
buffer busy waits                                                       2393        1509
enq: TX - index contention                                                 8         458
latch: In memory undo latch                                                3           2
latch: row cache objects                                                 515         340
row cache lock                                                             4          45
cursor: mutex X                                                            1           0
cursor: pin S                                                            110         230
cursor: pin S wait on X                                                   13          22
latch: shared pool                                                      1219        7622
library cache lock                                                         5         227
library cache load lock                                                   17         260
library cache: mutex X                                                    82         301
resmgr:internal state change                                              54         734

15 rows selected.

Resetting v$filestat timings dbms_system.kcfrms

apt-rdbms-01.co.uk:APTDB\sys> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

apt-rdbms-01.co.uk:APTDB\sys> select file#, maxiortm from v$filestat;

FILE# MAXIORTM
------------------ ------------------
1 71
2 131
3 8
4 1
5 1

apt-rdbms-01.co.uk:APTDB\sys> exec dbms_system.kcfrms();

PL/SQL procedure successfully completed.

apt-rdbms-01.co.uk:APTDB\sys> select file#, maxiortm from v$filestat;

FILE# MAXIORTM
------------------ ------------------
1 0
2 0
3 0
4 0
5 0

Wednesday, 29 June 2011

Oracle 11g ADDM Report for Oracle RAC

ADDM Report
ADDM for RAC
DBMS_ADDM for RAC




SELECT MIN(snap_id), MAX(snap_id) FROM dba_hist_snapshot;





All instance in RAC ...
_________________________



VAR tname VARCHAR2(30);
VAR start_snap_id number;
VAR end_snap_id number;

BEGIN

:tname := 'DB_TEST_ANUJ';
:start_snap_id := 884 ;
:end_snap_id := 1052 ;
DBMS_ADDM.ANALYZE_DB(:tname, :start_snap_id, :end_snap_id);

END;



DBMS_ADDM for particulate Instance
_________________________________


VAR tname VARCHAR2(30);
VAR start_snap_id number;
VAR end_snap_id number;
VAR INST_NUM number;
BEGIN
:tname := 'INST_TEST_ANUJ';
:start_snap_id := 884 ;
:end_snap_id := 1052 ;
:INST_NUM := 1;
DBMS_ADDM.ANALYZE_INST(:tname,:start_snap_id,:end_snap_id, :INST_NUM );
END;
/





DBMS_ADDM for  partial mode
___________________________


DBMS_ADDM

analyze instance 1 and 3 out of four node RAC



VAR tname VARCHAR2(30);
VAR start_snap_id number;
VAR end_snap_id number;

BEGIN

:tname := 'PART_MODE_TEST_ANUJ';
:start_snap_id :=884;
:end_snap_id := 1052 ;
DBMS_ADDM.ANALYZE_PARTIAL(:tname,'1,3', :start_snap_id, :end_snap_id);

END;

/



ADDM report output 
__________________


SET LONG 500000 PAGESIZE 0;
SELECT DBMS_ADDM.GET_REPORT(:tname) FROM DUAL;

or

SELECT DBMS_ADDM.GET_REPORT('INST_TEST_ANUJ') FROM DUAL;

===========





 SELECT MIN(snap_id), MAX(snap_id) FROM dba_hist_snapshot;


BEGIN
-- 
DBMS_ADVISOR.create_task (advisor_name => 'ADDM',task_name => '33033_33035_AWR_SNAPSHOT',task_desc => 'Advisor for snapshots 33033 to 33035');
DBMS_ADVISOR.set_task_parameter (task_name => '33033_33035_AWR_SNAPSHOT',parameter => 'START_SNAPSHOT',value => 33033);
DBMS_ADVISOR.set_task_parameter ( task_name => '33033_33035_AWR_SNAPSHOT',parameter => 'END_SNAPSHOT',value => 33035);
DBMS_ADVISOR.execute_task(task_name => '33033_33035_AWR_SNAPSHOT');
END;
/


SET LONG 100000
SET PAGESIZE 50000
SELECT DBMS_ADVISOR.get_task_report('33033_33035_AWR_SNAPSHOT') AS report FROM dual; 



=============

some sql !!!



set linesize 500 pagesize 300
col ACTION_MESSAGE for a70 wrap 
col MESSAGE for a50 wrap
 Select a.execution_end, b.type, b.impact, d.rank, d.type,
'Message:'|| b.message MESSAGE,
'Command To correct:'||c.command COMMAND,
'Action Message:'|| c.message ACTION_MESSAGE
From dba_advisor_tasks a, dba_advisor_findings b,Dba_advisor_actions c, dba_advisor_recommendations d
Where a.owner=b.owner
and a.task_id=b.task_id
And b.task_id=d.task_id
and b.finding_id=d.finding_id
And a.task_id=c.task_id
and d.rec_id=c.rec_Id
And a.task_name like 'ADDM%'
and a.status='COMPLETED'
and to_char(execution_end,'dd/mm/yyyy')='06/05/2021'
Order by 3 desc;



set linesize 500 pagesize 300
col ACTION_MESSAGE for a70 wrap 
col MESSAGE for a50 wrap
 Select a.execution_end, b.type, b.impact, d.rank, d.type,
'Message:'|| b.message MESSAGE,
'Command To correct:'||c.command COMMAND,
'Action Message:'|| c.message ACTION_MESSAGE
From dba_advisor_tasks a, dba_advisor_findings b,Dba_advisor_actions c, dba_advisor_recommendations d
Where a.owner=b.owner
and a.task_id=b.task_id
And b.task_id=d.task_id
and b.finding_id=d.finding_id
And a.task_id=c.task_id
and d.rec_id=c.rec_Id
And a.task_name like 'ADDM%'
and a.status='COMPLETED'
and to_char(execution_end,'dd/mm/yyyy')='06/05/2021'
Order by 3 desc;

EXECUTION TYPE            IMPACT       RANK TYPE                           MESSAGE                                            COMMAND                                                                            ACTION_MESSAGE
--------- ----------- ---------- ---------- ------------------------------ -------------------------------------------------- ----------------------------------------------------------------------------------- ----------------------------------------------------------------------
06-MAY-21 PROBLEM      338002056          2 SQL Tuning                     Message:SQL statements consuming significant datab Command To correct:UNDEFINED                                                       Action Message:Investigate the PL/SQL statement with SQL_ID "b6usrg82h
                                                                           ase time were found. These statements offer a good                                                                                    wsa3" for possible performance improvements. You can supplement the in
                                                                            opportunity for performance improvement.                                                                                             formation given here with an ASH report for this SQL_ID.

06-MAY-21 PROBLEM      338002056          4 SQL Tuning                     Message:SQL statements consuming significant datab Command To correct:UNDEFINED                                                       Action


set linesize 300 pagesize 300
col ACTION_MESSAGE for a150 wrap
Select distinct c.message ACTION_MESSAGE
From dba_advisor_tasks a, dba_advisor_findings b,Dba_advisor_actions c,
dba_advisor_recommendations d
Where a.owner=b.owner and a.task_id=b.task_id
And b.task_id=d.task_id and b.finding_id=d.finding_id
And a.task_id=c.task_id and d.rec_id=c.rec_Id
And a.task_name like 'ADDM%' and a.status = 'COMPLETED'
-- And c.message like '%involving I/O on TABLE%'
--And c.message like 'Investigate application logic involving I/O on TABLE%'
Order by 1;

ACTION_MESSAGE
------------------------------------------------------------------------------------------------------------------------------------------------------
Consider not using the compression option for RMAN jobs. Alternatively, consider slowing down RMAN activity, or scheduling RMAN jobs when user activit
y is lower.

Increase the size of the SGA by setting the parameter "sga_target" to 4608 M.
Increase the size of the SGA by setting the parameter "sga_target" to 5120 M.
Increase the size of the SGA by setting the parameter "sga_target" to 5632 M.
Increase the size of the SGA by setting the parameter "sga_target" to 6144 M.
Increase the size of the SGA by setting the parameter "sga_target" to 6656 M.
Increase the size of the SGA by setting the parameter "sga_target" to 7168 M.
Increase the size of the SGA on affected instances. Check the ADDM analysis of affected instances for more details.
Increase throughput of the Global Cache Service (LMSn) processes. Increase the number of Global Cache Service processes by increasing the value of the
 parameter "gcs_server_processes". Alternatively, if the host is CPU bound consider increasing the OS priority of the Global Cache Service processes.

Investigate application logic involving I/O on database object with ID 82.
Investigate application logic to eliminate parse errors.
Investigate appropriateness of DDL operations.
Investigate the PL/SQL statement with SQL_ID "b6usrg82hwsa3" for possible performance improvements. You can supplement the information given here with
 an ASH report for this SQL_ID.



Set pages 1000 lines 300 
col ACTION_MESSAGE for a70
col MESSAGE for a20
col COMMAND for a15
Select a.execution_end, b.type, b.impact, d.rank, d.type,
'Message           : '||b.message MESSAGE,
'Command To correct: '||c.command COMMAND,
'Action Message    : '||c.message ACTION_MESSAGE
from dba_advisor_tasks a, dba_advisor_findings b,dba_advisor_actions c, dba_advisor_recommendations d
where a.owner=b.owner and a.task_id=b.task_id
and b.task_id=d.task_id and b.finding_id=d.finding_id
and a.task_id=c.task_id and d.rec_id=c.rec_Id
and a.task_name like 'ADDM%' 
and a.status='COMPLETED'
and a.description like '%53996%'  --- <<<< from above sql 
and d.type='SQL Tuning'
-- and b.owner not in ('SYS','SYSTEM')
-- and a.description like (select snap_id from (select snap_id  from dba_hist_snapshot order by begin_interval_time desc) where rownum < 3)
Order by b.impact, d.rank;



Set pages 1000 lines 300 
col ACTION_MESSAGE for a70
col MESSAGE for a20
col COMMAND for a15
Select a.execution_end, b.type, b.impact, d.rank, d.type,
'Message           : '||b.message MESSAGE,
'Command To correct: '||c.command COMMAND,
'Action Message    : '||c.message ACTION_MESSAGE
from dba_advisor_tasks a, dba_advisor_findings b,dba_advisor_actions c, dba_advisor_recommendations d
where a.owner=b.owner and a.task_id=b.task_id
and b.task_id=d.task_id and b.finding_id=d.finding_id
and a.task_id=c.task_id and d.rec_id=c.rec_Id
and a.task_name like 'ADDM%' 
and a.status='COMPLETED'
--and a.description like '%b742waswg25n7_tuning_task11%'  --- <<<< from above sql 
and d.type='SQL Tuning'
-- and b.owner not in ('SYS','SYSTEM')
-- and a.description like (select snap_id from (select snap_id  from dba_hist_snapshot order by begin_interval_time desc) where rownum < 3)
--and a.execution_end > sysdate -1
Order by b.impact, d.rank;

===


select round((ratio_to_report(max(Benefit)) over () *100)) as overall_benefit_pct
, type
, min(benefit) min_benefit
, max(Benefit) max_benefit
, count(*) cnt
from dba_Advisor_recommendations 
where type is not null 
group by type 
order by 1 desc 
;

col command for a15
col message for a100
select command, message , count(*)  from dba_advisor_Actions
group by command, message 
;




col TASK_NAME for a27
col description for a100
select task_name, description, created from dba_advisor_tasks
where advisor_name = 'ADDM'
and created > sysdate -7
order by created desc;

SELECT type, count(*) FROM dba_advisor_findings NATURAL JOIN dba_advisor_tasks WHERE created between sysdate -1 and sysdate GROUP BY type

SELECT distinct message FROM dba_advisor_recommendations JOIN dba_advisor_findings USING (finding_id, task_id) WHERE rank = 0;



SET LONG 500000 PAGESIZE 0
SELECT DBMS_ADDM.GET_REPORT('ADDM:1825264339_1_19982') FROM DUAL;

Monday, 27 June 2011

Oracle DBMS_ADVISOR.SQLACCESS_ADVISOR

Oracle QUICK_TUNE
Oracle quick tune
SQLACCESS_ADVISOR

SQL> connect scott/tiger
Connected.

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE <<<<<<<<<<<<<<,---------------
SALGRADE TABLE
T TABLE

from sys

SQL> connect / as sysdba
Connected.
SQL>
SQL>
SQL>
SQL>
SQL> BEGIN
DBMS_ADVISOR.quick_tune(
advisor_name => DBMS_ADVISOR.SQLACCESS_ADVISOR,
task_name => 'emp_quick_tune',
attr1 => 'SELECT * FROM scott.emp WHERE EMPNO = 7788');
END;
/

PL/SQL procedure successfully completed.

SQL> SET LONG 100000
SET PAGESIZE 50000
SELECT DBMS_ADVISOR.get_task_script('emp_quick_tune') AS script
FROM dual;
SET PAGESIZE 24

SQL> SQL> 2
SCRIPT
--------------------------------------------------------------------------------
Rem SQL Access Advisor: Version 11.2.0.1.0 - Production
Rem
Rem Username: SYS
Rem Task: emp_quick_tune
Rem Execution date:
Rem

/* RETAIN INDEX "SCOTT"."PK_EMP" */

or

VARIABLE task_name VARCHAR2(255);
VARIABLE sql_stmt VARCHAR2(4000);
exec :sql_stmt := 'SELECT COUNT(*) FROM scott.emp WHERE empno = 999';
exec :task_name := 'ANUJ_QUICKTUNE_TASK';
exec DBMS_advisor.quick_tune (dbms_advisor.sqlaccess_advisor, :task_name, :sql_stmt);

SET LONG 100000
SET PAGESIZE 50000
SELECT DBMS_ADVISOR.get_task_script('ANUJ_QUICKTUNE_TASK') AS script FROM dual;
SET PAGESIZE 24

======================================

begin
-- a task and a workload will be created then the task will be executed
DBMS_ADVISOR.QUICK_TUNE(
ADVISOR_NAME => DBMS_ADVISOR.SQLACCESS_ADVISOR,
TASK_NAME => 'anuj_quick_tune',
ATTR1 => 'select * from scott.emp where EMPNO=123');

end;

SQL> begin
-- a task and a workload will be created then the task will be executed
DBMS_ADVISOR.QUICK_TUNE(
ADVISOR_NAME => DBMS_ADVISOR.SQLACCESS_ADVISOR,
TASK_NAME => 'anuj_quick_tune',
ATTR1 => 'select * from scott.emp where EMPNO=123');

end;
/

PL/SQL procedure successfully completed.

col ERROR_MESSAGE format a50
select TASK_NAME, STATUS, PCT_COMPLETION_TIME, ERROR_MESSAGE from DBA_ADVISOR_LOG where TASK_NAME ='anuj_quick_tune';

TASK_NAME STATUS PCT_COMPLETION_TIME ERROR_MESSAGE
------------------------------ ----------- ------------------- --------------------------------------------------
anuj_quick_tune COMPLETED 100

1 row selected.

SET LONG 100000
SET PAGESIZE 50000
SELECT DBMS_ADVISOR.GET_TASK_SCRIPT('anuj_quick_tune') AS script FROM dual;

SCRIPT
--------------------------------------------------------------------------------
Rem SQL Access Advisor: Version 11.2.0.1.0 - Production
Rem
Rem Username: SYS
Rem Task: anuj_quick_tune
Rem Execution date:
Rem

/* RETAIN INDEX "SCOTT"."SYS_C0022543" */

1 row selected.

-- if you need to terminate the executing task (may be time consuming)

exec DBMS_ADVISOR.CANCEL_TASK(TASK_NAME =>'anuj_quick_tune');

select REC_ID, RANK, BENEFIT, TYPE "Recommendation Type"
from DBA_ADVISOR_RECOMMENDATIONS
where TASK_NAME = 'anuj_quick_tune'
order by RANK;

REC_ID RANK BENEFIT Recommendation Type
---------- ---------- ---------- ------------------------------
1 1 0 RETAINS_ONLY

1 row selected.

select REC_ID, ACTION_ID, SUBSTR(COMMAND,1,30) AS COMMAND
from USER_ADVISOR_ACTIONS where TASK_NAME = 'anuj_quick_tune'
ORDER BY rec_id, action_id;

select SQL_ID, REC_ID, PRECOST, POSTCOST,(PRECOST-POSTCOST)*100/PRECOST AS PERCENT_BENEFIT
from dba_ADVISOR_SQLA_WK_STMTS
where TASK_NAME = 'anuj_quick_tune'

SQL_ID REC_ID PRECOST POSTCOST PERCENT_BENEFIT
------------- ---------- ---------- ---------- ---------------
3cx27884dw7ar 1 2 2 0

1 row selected.

DBMS_ADVISOR.quick_tune error

ORA-13600, QSM-00794, ORA-06512 when running DBMS_ADVISOR
ORA-13600: error encountered in Advisor

solution
Create the table in other schama rather than SYS, SYSTEM for DBMS_ADVISOR.quick_tune

SQL> show user
USER is "SYS"

SQL> create table anuj(x number)
/
Table created.

SQL> BEGIN
DBMS_ADVISOR.quick_tune(
advisor_name => DBMS_ADVISOR.SQLACCESS_ADVISOR,
task_name => 'anuj_quick_tune',
attr1 => 'SELECT * FROM anuj WHERE x = 88');
END;
/
BEGIN
*
ERROR at line 1:
ORA-13600: error encountered in Advisor
QSM-00794: the statement can not be stored due to a violation of the invalid table reference filter
ORA-06512: at "SYS.PRVT_ACCESS_ADVISOR", line 1808
ORA-06512: at "SYS.WRI$_ADV_SQLACCESS_ADV", line 180
ORA-06512: at "SYS.PRVT_ADVISOR", line 3636
ORA-06512: at "SYS.DBMS_ADVISOR", line 711
ORA-06512: at line 2

Saturday, 25 June 2011

Unix Search a TEXT in file

Unix Search a staring
Search a TEXT in file
search a word in file

find . -type f -exec grep -il "iapi" {} \;

Friday, 24 June 2011

Is Oracle 32-bit or 64-bit and Oracle version ?

Oracle 32-bit or 64-bit ? oracle 32bit or 64bit oracle 32 bit or 64 bit Oracle version [code]

 
-bash-3.2$ sqlplus / as sysdba


apt-rdbms-01.tus.co.uk:APTDB\sys> 

select length(addr)*4 || '-bits' word_length 
from  v$process where rownum<2

-----
             
 
                                                                                                                 SELECT * FROM V$VERSION;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production


or



SQL> define
DEFINE _DATE           = "04-AUG-11" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "orcl" (CHAR)
DEFINE _USER           = "SYS" (CHAR)
DEFINE _PRIVILEGE      = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1102000100" (CHAR)
DEFINE _EDITOR         = "vi" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options" (CHAR)
DEFINE _O_RELEASE      = "1102000100" (CHAR)
DEFINE _RC             = "0" (CHAR)




Oracle vesrion


col COMP_NAME format a50
SQL> r
  1* select COMP_NAME ,VERSION,STATUS from dba_registry

COMP_NAME                                          VERSION                        STATUS
-------------------------------------------------- ------------------------------ --------------------------------------------
Oracle Application Express                         4.0.2.00.07                    VALID
OWB                                                11.2.0.1.0                     VALID
OLAP Catalog                                       11.2.0.1.0                     VALID
Spatial                                            11.2.0.1.0                     VALID
Oracle Multimedia                                  11.2.0.1.0                     VALID
Oracle XML Database                                11.2.0.1.0                     VALID
Oracle Text                                        11.2.0.1.0                     VALID
Oracle Expression Filter                           11.2.0.1.0                     VALID
Oracle Rules Manager                               11.2.0.1.0                     VALID
Oracle Workspace Manager                           11.2.0.1.0                     VALID
Oracle Database Catalog Views                      11.2.0.1.0                     VALID
Oracle Database Packages and Types                 11.2.0.1.0                     VALID
JServer JAVA Virtual Machine                       11.2.0.1.0                     VALID
Oracle XDK                                         11.2.0.1.0                     VALID
Oracle Database Java Packages                      11.2.0.1.0                     VALID
OLAP Analytic Workspace                            11.2.0.1.0                     VALID
Oracle OLAP API                                    11.2.0.1.0                     VALID

17 rows selected.



perl $ORACLE_HOME/OPatch/opatch.pl version
Invoking OPatch 11.1.0.6.6

OPatch Version: 11.1.0.6.6

OPatch succeeded.


SELECT PLATFORM_NAME
    FROM V$TRANSPORTABLE_PLATFORM
    WHERE PLATFORM_ID =
       ( SELECT PLATFORM_ID FROM V$DATABASE );


SQL --  !lsnrctl version

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 04-AUG-2011 09:09:24

Copyright (c) 1991, 2009, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
TNSLSNR for Linux: Version 11.2.0.1.0 - Production
        TNS for Linux: Version 11.2.0.1.0 - Production
        Unix Domain Socket IPC NT Protocol Adaptor for Linux: Version 11.2.0.1.0 - Production
        Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production
        TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.1.0 - Production,,
The command completed successfully

Thursday, 23 June 2011

To Change the permissions On Unix

To Change the permissions On Unix
Unix permissions
Unix permission

666 : Default permission
002 : - umask value
664 : final permission

#=root

# chmod 777 *.log

u user
g group
o others

Owner Group others Permission
========= ========= ========= ======================
7 (u+rwx) 7 (g+rwx) 7 (o+rwx) read + write + execute
6 (u+wx) 6 (g+wx) 6 (o+wx) write + execute
5 (u+Rx) 5 (g+Rx) 5 (o+Rx) read + execute
4 (u+r) 4 (g+r) 4 (o+r) read only
2 (u+w) 2 (g+w) 2 (o+w) write only
1 (u+x) 1 (g+x) 1 (o+x) execute only

Character equivalents can be used in the chmod command .

#chmod o+rwx *.log
#chmod g+r *.log
#chmod -Rx *.log

chmod -R g+rw hft/

-R recursive

# chown -R oinstall.dba *

Owner Group The others Permission

7 7 7 read + write + execute
6 6 6 write + execute
5 5 5 read + execute
4 4 4 read only
2 2 2 write only
1 1 1 execute only

Oracle Create Soft link

-bash-3.2$ pwd
/usr/lib/oracle/instantclient10_1
-bash-3.2$ ls -ltr libclntsh*
-rwxr-xr-x 1 oracle oinstall 15306239 Dec 7 2005 libclntsh.so.10.1
lrwxrwxrwx 1 oracle dba 17 Jun 22 10:46 libclntsh.so -> libclntsh.so.10.1

ln -s libclntsh.so.10.1 libclntsh.so.11.1

-bash-3.2$ ls -ltr libclntsh*
-rwxr-xr-x 1 oracle oinstall 15306239 Dec 7 2005 libclntsh.so.10.1
lrwxrwxrwx 1 oracle dba 17 Jun 22 10:46 libclntsh.so -> libclntsh.so.10.1
lrwxrwxrwx 1 oracle dba 17 Jun 23 09:28 libclntsh.so.11.1 -> libclntsh.so.10.1
-bash-3.2$

Monday, 20 June 2011

Oracle patch info

oracle@apt-amd-02:/opt/app/oracle/product/11.2/OPatch> ./opatch lsinventory -bugs_fixed

Invoking OPatch 11.1.0.6.6

Oracle Interim Patch Installer version 11.1.0.6.6

Oracle Home : /opt/app/oracle/product/11.2

Central Inventory : /opt/app/oracle/oraInventory

from : /etc/oraInst.loc

OPatch version : 11.1.0.6.6

OUI version : 11.2.0.1.0

OUI location : /opt/app/oracle/product/11.2/oui

Log file location : /opt/app/oracle/product/11.2/cfgtoollogs/opatch/opatch2011-06-20_14-51-20PM.log

Patch history file: /opt/app/oracle/product/11.2/cfgtoollogs/opatch/opatch_history.txt

Lsinventory Output file location : /opt/app/oracle/product/11.2/cfgtoollogs/opatch/lsinv/lsinventory2011-06-20_14-51-20PM.txt

------------------------------------------------------------------------------------------------------

Installed Top-level Products (1):

Oracle Database 11g 11.2.0.1.0

There are 1 products installed in this Oracle Home.

There are no Interim patches installed in this Oracle Home.

------------------------------------------------------------------------------------------------------

OPatch succeeded.

opatch lsinventory -bugs_fixed | grep -i 'GI PSU'

opatch lsinventory -bugs_fixed | grep -i 'ENTERPRISE MANAGER AGENT' | grep -i 'PSU'

to download opatch utility go to

metalink >> patch and update

then search for 6880880 patch and OS

-bash-3.00$ opatch lsinventory -detail

Invoking OPatch 10.2.0.4.3

Oracle Interim Patch Installer version 10.2.0.4.3

Oracle Home : /opt/oracle/product/10.2

Central Inventory : /opt/oracle/oraInventory

from : /var/opt/oracle/oraInst.loc

OPatch version : 10.2.0.4.3

OUI version : 10.2.0.4.0

OUI location : /opt/oracle/product/10.2/oui

Log file location : /opt/oracle/product/10.2/cfgtoollogs/opatch/opatch2011-07-22_11-14-02AM.log

Lsinventory Output file location : /opt/oracle/product/10.2/cfgtoollogs/opatch/lsinv/lsinventory2011-07-22_11-14-02AM.txt

--------------------------------------------------------------------------------

Installed Top-level Products (3):

Oracle Database 10g 10.2.0.1.0

Oracle Database 10g Release 2 Patch Set 2 10.2.0.3.0

Oracle Database 10g Release 2 Patch Set 3 10.2.0.4.0

There are 3 products installed in this Oracle Home.

Installed Products (188):

Agent Required Support Files 10.2.0.1.0

Agent Required Support Files Patch 10.2.0.4.0

Assistant Common Files 10.2.0.1.0

=======================

opatch lsinventory

select * from dba_registry_history;

select substr(comp_id,1,15) comp_id, substr(comp_name,1,30) comp_name, substr(version,1,10) version, status from dba_registry order by modified;

select * from sys.registry$history;

col COMP_NAME for a50

set lines 200

col action_time for a30

col comments for a30

col ACTION for a15

select action_time, action,namespace, version, id, bundle_series, comments from registry$history;

select action_time, action, version, id, comments from dba_registry_history order by action_time;

select COMMENTS, ACTION_TIME from sys.registry$history;

select COMP_NAME, VERSION, STATUS from dba_registry;

How to Use MOS Patch Planner to Check and Request the Conflict Patches? (Doc ID 1317012.1)

google it and find patch info

Map of Public Vulnerability to Advisory/Alert

http://www.oracle.com/technetwork/topics/security/public-vuln-to-advisory-mapping-093627.html

===========

to download

https://updates.oracle.com/download/6880880.html

unzip p6880880_210000_Linux-x86-64.zip -d $ORACLE_HOME/

set serveroutput on;

execute dbms_optim_bundle.getBugsforBundle;

12.2.0.1.190416DBRU:

Bug: 25405100, fix_controls: 25405100

execute dbms_optim_bundle.listBundlesWithFCFixes; ----<<<<

bundleId: 180116, bundleName: 12.2.0.1.180116DBRU

bundleId: 181017, bundleName: 12.2.0.1.181017DBRU

bundleId: 190115, bundleName: 12.2.0.1.190115DBRU

bundleId: 190416, bundleName: 12.2.0.1.190416DBRU

PL/SQL procedure successfully completed.

====

with a as

(select dbms_qopatch.get_opatch_lsinventory patch_output from dual)

select x.*

from a,

xmltable('InventoryInstance/patches/*' passing a.patch_output

columns patch_id number path 'patchID',

patch_uid number path 'uniquePatchID',

description varchar2(80) path 'patchDescription',

applied_date varchar2(30) path 'appliedDate',

sql_patch varchar2(8) path 'sqlPatch',

rollbackable varchar2(8) path 'rollbackable') x

Oracle Database 19c Release Update & Release Update Revision October 2022 Known Issues (Doc ID 19202210.9)

RDBMS Online Patching Aka Hot Patching (Doc ID 761111.1)

https://updates.oracle.com/Orion/PatchDetails/process_form?patch_num=6880880



cd $ORACLE_HOME
mv OPatch OPatch.`date +"%Y"-"%m"-"%d"`

ls -ld OPatch*
drwxr-x---. 14 oracle oinstall 4096 Apr 22  2020 OPatch.2024-04-19

===

cd $ORACLE_HOME
unzip -oq /home/oracle/Downloads/p6880880_210000_Linux-x86-64.zip

ls -ld OPatch*
drwxr-x---. 15 oracle oinstall 4096 Apr 15 14:41 OPatch
drwxr-x---. 14 oracle oinstall 4096 Apr 22  2020 OPatch.2024-04-19

db_1]$

===

export PATH=$ORACLE_HOME/OPatch:$PATH

opatch version
OPatch Version: 12.2.0.1.42

OPatch succeeded.

or


./opatch version
OPatch Version: 12.2.0.1.42

OPatch succeeded.

***********

export OPATCH_DEBUG=TRUE  <<<< if requited


 export PATH=$ORACLE_HOME/OPatch:$PATH
 opatch lsinventory | grep -E "(^Patch.*applied)|(^Sub-patch)"

opatch lsinventory | grep -E "(^Patch)|(^Sub-patch)"


 export PATH=$ORACLE_HOME/OPatch:$PATH

to clean ..
 export PATH=$ORACLE_HOME/OPatch:$PATH
opatch util listorderedinactivepatches
opatch util deleteinactivepatches
opatch util cleanup


mv OPatch OPatch.`date +"%Y"-"%m"-"%d"`
mv: cannot move 'OPatch' to 'OPatch.2024-12-13': Permission denied

[grid@oragrid grid]$ pwd
/u01/app/21.3.0/grid
[grid@oragrid grid]$ pwd
/u01/app/21.3.0/grid
[grid@oragrid grid]$ logout

login as root 

[root@oragrid 19.2.0]# cd /u01/app/21.3.0/grid

[root@oragrid grid]# id
uid=0(root) gid=0(root) groups=0(root)
[root@oragrid grid]# mv OPatch OPatch.`date +"%Y"-"%m"-"%d"`

[root@oragrid grid]# ls -ld OPatch*
drwxr-xr-x 13 grid oinstall 303 Jul 27  2021 OPatch.2024-12-13
[root@oragrid grid]#



As root 
[root@oragrid ~]# cd /u01/app/21.3.0/grid
[root@oragrid grid]# unzip -oq /home/grid/p6880880_210000_Linux-x86-64.zip

[root@oragrid grid]# ls -ld OPatch*
drwxr-x--- 15 root root     4096 Oct  4 19:48 OPatch
drwxr-xr-x 13 grid oinstall  303 Jul 27  2021 OPatch.2024-12-13


[root@oragrid grid]# pwd
/u01/app/21.3.0/grid


[root@oragrid grid]# chown -R grid:oinstall OPatch/

Thursday, 16 June 2011

How To Resize the Online Redo Logfiles

How To Resize the Online Redo Logfiles
resize Redo Logfiles
how to drop redo logfile

Log status:

*UNUSED - Online redo log has never been written to. This is the state of a redo log that was just added, or just after a RESETLOGS,when it is not the current redo log.

*CURRENT - Current redo log. This implies that the redo log is active. The redo log could be open or closed.

*ACTIVE - Log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It may or may not be archived.

*CLEARING - Log is being re-created as an empty log after an ALTER DATABASE CLEAR LOGFILE statement. After the log is cleared,the status changes to UNUSED.

*CLEARING_CURRENT - Current log is being cleared of a closed thread. The log can stay in this status if there is some failure in the switch such as an I/O error writing the new log header.

*INACTIVE - Log is no longer needed for instance recovery. It may be in use for media recovery. It might or might not be archived.

SQL> select group#, bytes/1024/1024, status from v$log;

GROUP# BYTES/1024/1024 STATUS
---------- --------------- ----------------
1 50 CURRENT
2 50 INACTIVE
3 50 INACTIVE

col MEMBER format a40
select group#, member from v$logfile

GROUP# MEMBER
---------- ----------------------------------------
3 /opt/app/oracle/oradata/orcl/redo03.log
2 /opt/app/oracle/oradata/orcl/redo02.log
1 /opt/app/oracle/oradata/orcl/redo01.log

create new redo log files

alter database add logfile group 4 '/opt/app/oracle/oradata/orcl/redo04.log' size 520M;

alter database add logfile group 5 '/opt/app/oracle/oradata/orcl/redo05.log' size 520M;

alter database add logfile group 6 '/opt/app/oracle/oradata/orcl/redo06.log' size 520M;

SQL> alter database add logfile group 4 '/opt/app/oracle/oradata/orcl/redo04.log' size 520M;

Database altered.

SQL> alter database add logfile group 5 '/opt/app/oracle/oradata/orcl/redo05.log' size 520M;

Database altered.

SQL> alter database add logfile group 6 '/opt/app/oracle/oradata/orcl/redo06.log' size 520M;

Database altered.

SQL> select group#, status from v$log;

GROUP# STATUS
---------- ----------------
1 CURRENT
2 INACTIVE
3 INACTIVE
4 UNUSED
5 UNUSED
6 UNUSED

6 rows selected.

SQL> select group#, status from v$log;

GROUP# STATUS
---------- ----------------
1 CURRENT
2 INACTIVE
3 INACTIVE
4 UNUSED
5 UNUSED
6 UNUSED

SQL> select group#, status from v$log;

GROUP# STATUS
---------- ----------------
1 ACTIVE
2 INACTIVE
3 INACTIVE
4 ACTIVE
5 ACTIVE
6 CURRENT

SQL> alter database drop logfile group 2 ;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

SQL> select group#, status from v$log;

GROUP# STATUS
---------- ----------------
1 ACTIVE
4 ACTIVE
5 ACTIVE
6 CURRENT

SQL> select group#, status from v$log;

GROUP# STATUS
---------- ----------------
1 ACTIVE
4 ACTIVE
5 CURRENT
6 ACTIVE

>>>>>>>>>>>>>>>>>>>>>>>>>>>wait some time <<<<<<<<<<<<<<<<<<<<<<<<

SQL> select group#, status from v$log;

now group 1 is inactive

GROUP# STATUS
---------- ----------------
1 INACTIVE
4 INACTIVE
5 CURRENT
6 INACTIVE

now drop the 1

SQL> alter database drop logfile group 1;

Database altered.

finally delete the file from OS level

SQL> !
oracle@apt-amd-02:/opt/app/oracle/product/11.2/sysman/admin/emdrep/bin> cd /opt/app/oracle/oradata/orcl/

oracle@apt-amd-02:/opt/app/oracle/oradata/orcl> rm redo01.log redo02.log redo03.log

Tuesday, 14 June 2011

How to change the SYSMAN user password

sysman password change

SQL> !emctl stop dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0
Copyright (c) 1996, 2009 Oracle Corporation. All rights reserved.
https://apt-amd-02.tus.co.uk:1158/em/console/aboutApplication
Stopping Oracle Enterprise Manager 11g Database Control ...

Cannot determine Oracle Enterprise Manager 11g Database Control process. /opt/app/oracle/product/11.2/apt-amd-02.tus.co.uk_orcl/emctl.pid does not exist.

SQL> !emctl status dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0
Copyright (c) 1996, 2009 Oracle Corporation. All rights reserved.
https://apt-amd-02.tus.co.uk:1158/em/console/aboutApplication
Oracle Enterprise Manager 11g is not running.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@apt-amd-02:~> emctl status dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0
Copyright (c) 1996, 2009 Oracle Corporation. All rights reserved.
https://apt-amd-02.aptus.co.uk:1158/em/console/aboutApplication
Oracle Enterprise Manager 11g is not running.
oracle@apt-amd-02:~> !sql
sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Jun 14 14:16:32 2011

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter user sysman identified by vihaan123;

User altered.

$emctl setpasswd dbconsole

oracle@apt-amd-02:/opt/app/oracle/product/11.2/apt-amd-02.tus.co.uk_orcl/sysman/config> emctl setpasswd dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0
Copyright (c) 1996, 2009 Oracle Corporation. All rights reserved.
https://apt-amd-02.aptus.co.uk:1158/em/console/aboutApplication
Please enter new repository password:
Repository password successfully updated.
oracle@apt-amd-02:/opt/app/oracle/product/11.2/apt-amd-02.aptus.co.uk_orcl/sysman/config>

emctl start dbconsole

https://apt-amd-02.tus.co.uk:1158/em/console/aboutApplication

cd $ORACLE_HOME

oracle@apt-amd-02:/opt/app/oracle/product/11.2> cd orcl
-bash: cd: orcl: No such file or directory
oracle@apt-amd-02:/opt/app/oracle/product/11.2> cd apt-amd-02.aptus.co.uk_orcl/

oracle@apt-amd-02:/opt/app/oracle/product/11.2/apt-amd-02.tus.co.uk_orcl> cd sysman/
oracle@apt-amd-02:/opt/app/oracle/product/11.2/apt-amd-02.tus.co.uk_orcl/sysman> ls -ltr
total 8
drwxr----- 3 oracle oinstall 17 2011-01-10 15:44 opmn
drwxr----- 4 oracle oinstall 4096 2011-01-10 15:46 config
drwxr-x--- 3 oracle oinstall 4096 2011-02-07 09:46 log
drwxr----- 5 oracle oinstall 152 2011-02-07 11:14 emd
drwxr----- 4 oracle oinstall 30 2011-02-07 12:00 recv

go to this file

oracle@apt-amd-02:/opt/app/oracle/product/11.2/apt-amd-02.tus.co.uk_orcl/sysman/config> vi emoms.properties

search for this file

oracle.sysman.eml.mntr.emdRepPwd=89b2c01e41fc69697990aae431c32604

to

oracle.sysman.eml.mntr.emdRepPwd=vihaan123

Search for the line:

oracle.sysman.eml.mntr.emdRepPwdEncrypted=TRUE to oracle.sysman.eml.mntr.emdRepPwdEncrypted=FALSE

oracle@apt-amd-02:/opt/app/oracle/product/11.2/apt-amd-02.aptus.co.uk_orcl/sysman/config> emctl start dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0
Copyright (c) 1996, 2009 Oracle Corporation. All rights reserved.
https://apt-amd-02.aptus.co.uk:1158/em/console/aboutApplication
Starting Oracle Enterprise Manager 11g Database Control ........

=================

# Check that the password has been encrypted
Edit the file $ORACLE_HOME/host_sid/sysman/config/emoms.properties

1. Search for the line beginning with:
oracle.sysman.eml.mntr.emdRepPwd=
Check that the password is encrypted
2. Search for the line beginning with:
oracle.sysman.eml.mntr.emdRepPwdEncrypted=
Check that the value is TRUE

#Tue Jun 14 14:41:57 GMT 2011
oracle.sysman.emSDK.svlt.ConsoleServerName=apt-amd-02.aptus.co.uk\:1158_Management_Service
oracle.sysman.eml.mntr.emdRepPwd=04154901177159bfb36808486e4de27b <<<<<---- check this this should be encrypted
emdrep.ping.pingCommand=/usr/sbin/ping6
em_oob_shutdown=false
LargeRepository=false
oracle.sysman.eml.mntr.emdRepPort=1521
oracle.sysman.eml.mntr.emdRepDBName=orcl.aptus.co.uk
EMD_URL=https\://apt-amd-02.aptus.co.uk\:3938/emd/main
em_email_address=%EM_EMAIL_ADDRESS%
oracle.sysman.eml.mntr.emdRepPwdSeed=8927514978106692198
oracle.sysman.emSDK.svlt.ConsoleMode=standalone
em_oob_crash=false
em.oms.dumpModules=omsThread,repos
oracle.sysman.emRep.dbConn.statementCacheSize=50
oracle.sysman.db.isqlplusUrl=http\://apt-amd-02.tus.co.uk\:/isqlplus/dynamic
em_oob_startup=false
oracle.sysman.emSDK.svlt.ConsoleServerPort=1158
oracle.sysman.eml.mntr.emdRepRAC=FALSE
em_from_email_address=%EM_FROM_EMAIL_ADDRESS%
oracle.sysman.eml.mntr.emdRepPwdEncrypted=TRUE <<<<<<----- check this this should be true
oracle.sysman.db.isqlplusWebDBAUrl=http\://apt-amd-02.tus.co.uk\:/isqlplus/dba/dynamic
oracle.sysman.emSDK.svlt.ConsoleServerHost=apt-amd-02.aptus.co.uk
oracle.sysman.eml.mntr.emdRepDBID=1267852645
oracle.sysman.emSDK.svlt.ConsoleServerHTTPSPort=1158
em_email_gateway=%EM_EMAIL_GATEWAY%
oracle.sysman.eml.mntr.emdRepServer=apt-amd-02.aptus.co.uk
oracle.sysman.eml.mntr.emdRepSID=orcl
oracle.sysman.eml.mntr.emdRepConnectDescriptor=(DESCRIPTION\=(ADDRESS_LIST\=(ADDRESS\=(PROTOCOL\=TCP)(HOST\=apt-amd-02.aptus.co.uk)(PORT\=1521)))(CONNECT_DATA\=(SERVICE_NAME\=orcl.aptus.co.uk)))
oracle.sysman.emSDK.sec.ReuseLogonPassword=true
oracle.sysman.emkeyfile=/opt/app/oracle/product/11.2/apt-amd-02.aptus.co.uk_orcl/sysman/config/emkey.ora
em.ip.ui.enable=true
oracle.sysman.eml.mntr.emdRepUser=SYSMAN
oracle.sysman.emSDK.svlt.PublicServletEnabled=true

Oracle 11g old type alert log file location

Oracle 11g old type alert log file

oracle 11g alert log

SQL> show parameter diag

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest string /opt/app/oracle

/opt/app/oracle/diag/rdbms/orcl/orcl/trace

ORA-19815: WARNING: db_recovery_file_dest_size

ORA-19815: WARNING: db_recovery_file_dest_size

delete rman archivelog

delete archivelog

rman delete

DELETE EXPIRED commands.
************************************************************************
Errors in file /opt/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_arc1_435.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 43941376 bytes disk space from 4070572032 limit
ARC1: Error 19809 Creating archive log file to '/opt/app/oracle/flash_recovery_area/ORCL/archivelog/2011_06_14/o1_mf_1_465_%u_.arc'
Tue Jun 14 13:52:29 2011
Errors in file /opt/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_arc3_439.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 4070572032 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.
************************************************************************
Errors in file /opt/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_arc3_439.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 43941376 bytes disk space from 4070572032 limit
ARC3: Error 19809 Creating archive log file to '/opt/app/oracle/flash_recovery_area/ORCL/archivelog/2011_06_14/o1_mf_1_465_%u_.arc'

oracle@apt-amd-02:/opt/app/oracle/diag/rdbms/orcl/orcl/trace> rman

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Jun 14 13:56:08 2011

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

RMAN> connect target /

connected to target database: ORCL (DBID=1267852645)

RMAN> delete archivelog all completed before 'sysdate -1';

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=56 device type=DISK

Monday, 13 June 2011

How to install Oracle Client 11g on Red Hat Enterprise Linux 5 / centos 5

How to install Oracle Client 11g on Red Hat Enterprise Linux 5/centos 5
Oracle 11gr2 client installation on unix - 32 bit

linux_11gR2_client.zip

oinstall:x:1004:oracle,jtime79
dba:x:1003:oracle
oper:x:1005:oracle
jtime79:x:1006:

make a home dir

[root@apt-jtime-01 tmp]# mkdir -p /aptus/oracle/home

create group

[root@apt-jtime-01 tmp]# groupadd oinstall
[root@apt-jtime-01 tmp]# groupadd dba

[root@apt-jtime-01 tmp]# useradd -c "Oracle Software Owner" -g oinstall -G dba -d /aptus/oracle/home oracle
useradd: warning: the home directory already exists.
Not copying any file from skel directory into it.

[root@apt-jtime-01 tmp]# passwd oracle

Changing password for user oracle.
New UNIX password:
BAD PASSWORD: it is based on a dictionary word
Retype new UNIX password:
passwd: all authentication tokens updated successfully.

change the oracle owership

chown -R oracle:oinstall /aptus/oracle/home

[root@apt-jtime-01 tmp]# mkdir -p /opt/app/oracle

[root@apt-jtime-01 tmp]# chown -R oracle:oinstall /opt/app/oracle

install rpm <<<<<<<<<<<

yum install libXp gcc make setarch libaio glibc-devel

yum install gcc make setarch libaio glibc-devel
[root@apt-jtime-01 ~]# yum install libaio-devel
[root@apt-jtime-01 ~]# yum install elfutils-libelf-devel
[root@apt-jtime-01 ~]# yum install libstdc++-devel
[root@apt-jtime-01 ~]# yum install unixODBC
[root@apt-jtime-01 ~]# yum install unixODBC-devel
[root@apt-jtime-01 ~]# yum install pdksh

http://rpm.pbone.net/index.php3/stat/4/idpl/4423756/dir/whitebox/com/sysstat-5.0.5-15.0.1.el4.i386.rpm.html

[root@apt-jtime-01 ~]# ls -ltr
total 172
-rw-r--r-- 1 root root 5388 Jun 9 14:51 install.log.syslog
-rw-r--r-- 1 root root 30202 Jun 9 14:51 install.log
-rw------- 1 root root 1189 Jun 9 14:51 anaconda-ks.cfg
drwxr-xr-x 2 root root 4096 Jun 9 15:20 Desktop
-rw-r--r-- 1 root root 106868 Jun 13 12:24 sysstat-5.0.5-15.0.1.el4.i386.rpm

[root@apt-jtime-01 ~]# rpm -ivh sysstat-5.0.5-15.0.1.el4.i386.rpm
warning: sysstat-5.0.5-15.0.1.el4.i386.rpm: Header V3 DSA signature: NOKEY, key ID 73307de6
Preparing... ########################################### [100%]
1:sysstat ########################################### [100%]

[root@apt-jtime-01 tmp]# yum install gcc-c++
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
* base: mirror.sov.uk.goscomb.net
* extras: mirror.sov.uk.goscomb.net
* updates: mirror.sov.uk.goscomb.net
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package gcc-c++.i386 0:4.1.2-50.el5 set to be updated
--> Finished Dependency Resolution

Dependencies Resolved

====================================================================================================================================================================================
Package Arch Version Repository Size
====================================================================================================================================================================================
Installing:
gcc-c++ i386 4.1.2-50.el5 base 3.4 M

Transaction Summary
====================================================================================================================================================================================
Install 1 Package(s)
Upgrade 0 Package(s)

Total download size: 3.4 M
Is this ok [y/N]: y
Downloading Packages:
gcc-c++-4.1.2-50.el5.i386.rpm | 3.4 MB 00:04
Running rpm_check_debug
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
Installing : gcc-c++ 1/1

Installed:
gcc-c++.i386 0:4.1.2-50.el5

Complete!

yum list available "compat*" \
"elfutils*" \
"glibc*" \
"gcc*" \
"libaio*" \
"sysstat*" \
"unixODBC*"

then install

[root@apt-jtime-01 tmp]# yum install compat-libstdc++-33.i386

[root@apt-jtime-01 tmp]# yum install gcc44-c++.i386

[root@apt-jtime-01 tmp]# yum install unixODBC-kde.i386 ?????????

yum -y install compat-libstdc++-296.i386 \
compat-libstdc++-33.i386 \
elfutils-libelf-devel.i386 \
glibc-devel.i386 \
glibc-headers.i386 \
gcc.i386 \
gcc-c++.i386 \
libaio-devel.i386 \
sysstat.i386 \
unixODBC.i386 \
unixODBC-devel.i386

-bash-3.2$ unzip linux_11gR2_client.zip

-bash-3.2$ ls -ltr
total 627596
drwxr-xr-x 6 oracle oinstall 4096 Aug 18 2009 client
-rw-r--r-- 1 oracle oinstall 642016988 Jun 13 10:30 linux_11gR2_client.zip

to forword display use -Y

[anujs@apt-kvmsvr-01 ~]$ ssh -Y oracle@192.168.0.11
oracle@192.168.0.11's password:
/usr/bin/xauth: creating new authority file /aptus/oracle/home/.Xauthority

vi .bash_profile

/opt/app/oracle/product/11.2.0/db_1

[root@apt-jtime-01 oracle]# mkdir -p /opt/app/oracle/product/11.2.0/db_1
[root@apt-jtime-01 oracle]# /sbin/sysctl -a | grep net.ipv4.ip_local_port_range
[root@apt-jtime-01 oracle]# chown -R oracle:oinstall /opt/app/oracle/product/11.2.0/db_1

[root@apt-jtime-01 etc]# vi /etc/sysctl.conf

net.ipv4.ip_forward = 0
net.ipv4.conf.default.rp_filter = 1
net.ipv4.conf.default.accept_source_route = 0
kernel.sysrq = 0
kernel.core_uses_pid = 1
net.ipv4.tcp_syncookies = 1
kernel.msgmnb = 65536
kernel.msgmax = 65536
kernel.shmmax = 2147483648
kernel.shmall = 2097152
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
fs.file-max = 6815744
fs.aio-max-nr = 1048576
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 1048576
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576

[root@apt-jtime-01 tmp]# /sbin/sysctl -p

Change /etc/pam.d/login file and add the followingn line:

session required pam_limits.so

[root@apt-jtime-01 tmp]# vi /etc/security/limits.conf

oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536

mkdir -p /opt/app/oracle/product/11.2.0/db_1

chown -R oracle:oinstall /opt/app/oracle/product/11.2.0/db_1

export ORACLE_HOME=/opt/app/oracle/product/11.2.0/db_1
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
export PATH=$ORACLE_HOME/bin:$PATH

[root@apt-jtime-01 11.2.0]# chown -R oracle:oinstall /opt/app/oracle/product/11.2.0/db_1
[root@apt-jtime-01 11.2.0]# chown -R oracle:oinstall /opt/app/oracle/product/11.2.0/db_1
[root@apt-jtime-01 11.2.0]# chown -R oracle:oinstall /opt/app/oracle/product/11.2.0/db_2
chown: cannot access `/opt/app/oracle/product/11.2.0/db_2': No such file or directory

[root@apt-jtime-01 11.2.0]# mkdir db_2
[root@apt-jtime-01 11.2.0]# chown -R oracle:oinstall /opt/app/oracle/product/11.2.0/db_2
[root@apt-jtime-01 11.2.0]# /opt/app/oracle/product/11.2.0/db_2/root.sh
Running Oracle 11g root.sh script...

The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /opt/app/oracle/product/11.2.0/db_2

Enter the full pathname of the local bin directory: [/usr/local/bin]:
Copying dbhome to /usr/local/bin ...
Copying oraenv to /usr/local/bin ...
Copying coraenv to /usr/local/bin ...

Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.

Another app is currently holding the yum lock; waiting for it to exit

yum Another app is currently holding the yum lock; waiting for it to exit
Another app is currently holding the yum lock; waiting for it to exit
Another app is currently holding the yum lock; waiting for it to exit...

not able to install rpm on centos

[root@apt-jtime-01 tmp]# yum install libXp

Loaded plugins: fastestmirror
Existing lock /var/run/yum.pid: another copy is running as pid 2455.
Another app is currently holding the yum lock; waiting for it to exit...
The other application is: yum
Memory : 38 M RSS ( 80 MB VSZ)
Started: Thu Jun 9 15:32:06 2011 - 3 day(s) 19:31:05 ago
State : Sleeping, pid: 2455

Another app is currently holding the yum lock; waiting for it to exit...

Exiting on user cancel.

check the process

[root@apt-jtime-01 tmp]# ps aux | grep yum
root 2383 0.0 1.0 25908 10404 ? SN Jun09 0:00 /usr/bin/python -tt /usr/sbin/yum-updatesd
root 2455 0.0 3.8 81496 39036 tty1 S+ Jun09 0:35 /usr/bin/python /usr/bin/yum upgrade
root 31788 0.0 0.0 4016 708 pts/0 S+ 11:04 0:00 grep yum

[root@apt-jtime-01 tmp]# kill -9 2383 2455

then try again

Thursday, 9 June 2011

Oracle 11gr2 32bit RAC RPM requirement

ORACLE RAC RPM

rpm required for Oracle RAC

Each of the packages listed above can be found on CD #1, CD #2, and CD #3 on the Enterprise Linux 5 - (x86) CDs. While it is possible to query each individual package to determine which ones are missing and need to be installed, an easier method is to run the rpm -Uvh Package Name command from the five CDs as follows. For packages that already exist and are up to date, the RPM command will simply ignore the install and print a warning message to the console that the package is already installed.

#

From Enterprise Linux 5.4 (x86)- [CD #1]
mkdir -p /media/cdrom

mount -r /dev/cdrom /media/cdrom

cd /media/cdrom/Server
rpm -Uvh binutils-2.*
rpm -Uvh elfutils-libelf-0.*
rpm -Uvh glibc-2.*
rpm -Uvh glibc-common-2.*
rpm -Uvh kernel-headers-2.*
rpm -Uvh ksh-2*
rpm -Uvh libaio-0.*
rpm -Uvh libgcc-4.*
rpm -Uvh libstdc++-4.*
rpm -Uvh make-3.*
cd /
eject

#

From Enterprise Linux 5.4 (x86) - [CD #2]
mount -r /dev/cdrom /media/cdrom
cd /media/cdrom/Server

rpm -Uvh elfutils-libelf-devel-*
rpm -Uvh gcc-4.*
rpm -Uvh gcc-c++-4.*
rpm -Uvh glibc-devel-2.*
rpm -Uvh glibc-headers-2.*
rpm -Uvh libgomp-4.*
rpm -Uvh libstdc++-devel-4.*
rpm -Uvh unixODBC-2.*
cd /

eject

#

From Enterprise Linux 5.4 (x86) - [CD #3]
mount -r /dev/cdrom /media/cdrom
cd /media/cdrom/Server
rpm -Uvh compat-libstdc++-33*
rpm -Uvh libaio-devel-0.*
rpm -Uvh sysstat-7.*
rpm -Uvh unixODBC-devel-2.*
cd /

eject

Download Oracle ASM RPM for linux

ASMLib RPMs

ASMLib RPMs from OTN

Oracle ASMLib Downloads for Red Hat Enterprise Linux Server 5

http://www.oracle.com/technetwork/jp/topics/linux/downloads/rhel5-083144-ja.html

Friday, 3 June 2011

Oracle rename ( all the datafiles )

Oracle rename all datafile
Oracle move all datafile
oracle rename datafile

1. shutdown the database
2. move or rename the datafiles in the file system
3. startup mount the database
4. rename datafiles in the database

aim to change dir from /aptus/oracle/oradata/aptdb/aptdb to /aptus/oracle/oradata/aptdb/
for all the file

sql > create pfile from spfile ;

-bash-3.2$ pwd
/aptus/oracle/product/11.2.0/db_1/dbs

-bash-3.2$ vi init.ora

change the path of control file because I am moving control file as well .

current file location

-bash-3.2$ ls -ltr /aptus/oracle/oradata/aptdb/aptdb
total 2449428
-rw-r----- 1 oracle dba 52429312 Jun 2 22:01 redo02.log
-rw-r----- 1 oracle dba 104865792 Jun 3 06:56 temp01.dbf
-rw-r----- 1 oracle dba 52429312 Jun 3 10:10 redo03.log
-rw-r----- 1 oracle dba 5251072 Jun 3 10:16 users01.dbf
-rw-r----- 1 oracle dba 104865792 Jun 3 11:48 jtime79.dbf
-rw-r----- 1 oracle dba 838868992 Jun 3 12:05 undotbs01.dbf
-rw-r----- 1 oracle dba 629153792 Jun 3 12:06 sysaux01.dbf
-rw-r----- 1 oracle dba 734011392 Jun 3 12:06 system01.dbf
-rw-r----- 1 oracle dba 52429312 Jun 3 12:07 redo01.log
-rw-r----- 1 oracle dba 9748480 Jun 3 12:08 control02.ctl
-rw-r----- 1 oracle dba 9748480 Jun 3 12:08 control01.ctl

==========================================================
shutdown the database <<<<<<<<<<<<<<<<----

==========================================================

SQL> shutdown immediate;

cd /aptus/oracle/oradata/aptdb/aptdb

-bash-3.2$ mv *.* /aptus/oracle/oradata/aptdb/

-bash-3.2$ ls -ltr /aptus/oracle/oradata/aptdb/
total 2449440
-rw-r--r-- 1 oracle dba 0 Jun 1 14:06 aaa
-rw-r----- 1 oracle dba 52429312 Jun 2 22:01 redo02.log
-rw-r----- 1 oracle dba 104865792 Jun 3 06:56 temp01.dbf
-rw-r----- 1 oracle dba 52429312 Jun 3 10:10 redo03.log
-rw-r----- 1 oracle dba 5251072 Jun 3 12:09 users01.dbf
-rw-r----- 1 oracle dba 838868992 Jun 3 12:09 undotbs01.dbf
-rw-r----- 1 oracle dba 734011392 Jun 3 12:09 system01.dbf
-rw-r----- 1 oracle dba 629153792 Jun 3 12:09 sysaux01.dbf
-rw-r----- 1 oracle dba 52429312 Jun 3 12:09 redo01.log
-rw-r----- 1 oracle dba 104865792 Jun 3 12:09 jtime79.dbf
-rw-r----- 1 oracle dba 9748480 Jun 3 12:09 control02.ctl
-rw-r----- 1 oracle dba 9748480 Jun 3 12:09 control01.ctl
drwxr-x--- 2 oracle dba 4096 Jun 3 12:15 aptdb

SQL> startup mount pfile='/aptus/oracle/product/11.2.0/db_1/dbs/init.ora'
ORACLE instance started.

Total System Global Area 1436389376 bytes
Fixed Size 2213376 bytes
Variable Size 905972224 bytes
Database Buffers 520093696 bytes
Redo Buffers 8110080 bytes
Database mounted.

Note: We can't rename temporary files, for that we need to just recreate temporary tablespace with rename or new located tempfile.

ALTER DATABASE RENAME FILE '/aptus/oracle/oradata/aptdb/aptdb/system01.dbf' to '/aptus/oracle/oradata/aptdb/system01.dbf' ;
ALTER DATABASE RENAME FILE '/aptus/oracle/oradata/aptdb/aptdb/sysaux01.dbf' to '/aptus/oracle/oradata/aptdb/sysaux01.dbf' ;
ALTER DATABASE RENAME FILE '/aptus/oracle/oradata/aptdb/aptdb/undotbs01.dbf' to '/aptus/oracle/oradata/aptdb/undotbs01.dbf' ;
ALTER DATABASE RENAME FILE '/aptus/oracle/oradata/aptdb/aptdb/users01.dbf' to '/aptus/oracle/oradata/aptdb/users01.dbf' ;
ALTER DATABASE RENAME FILE '/aptus/oracle/oradata/aptdb/aptdb/redo01.log' to '/aptus/oracle/oradata/aptdb/redo01.log' ;
ALTER DATABASE RENAME FILE '/aptus/oracle/oradata/aptdb/aptdb/redo02.log' to '/aptus/oracle/oradata/aptdb/redo02.log' ;
ALTER DATABASE RENAME FILE '/aptus/oracle/oradata/aptdb/aptdb/redo03.log' to '/aptus/oracle/oradata/aptdb/redo03.log' ;
ALTER DATABASE RENAME FILE '/aptus/oracle/oradata/aptdb/aptdb/jtime79.dbf' to '/aptus/oracle/oradata/aptdb/jtime79.dbf' ;

Note: We can't rename temporary files, for that we need to just recreate temporary tablespace with rename or new located tempfile.

SQL> alter database open;

Database altered.

for temp tablespace <<<<

DROP TABLESPACE temp;

CREATE TEMPORARY TABLESPACE TEMP1
TEMPFILE '/aptus/oracle/oradata/aptdb/temp01.dbf' SIZE 500M REUSE
AUTOEXTEND ON NEXT 100M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

create temporary tablespace temp1 tempfile 'c:\oracle\oradata\acme\temp1.dbf' size 10M;

SQL> CREATE TEMPORARY TABLESPACE TEMP1
TEMPFILE '/aptus/oracle/oradata/aptdb/temp01.dbf' SIZE 500M REUSE
AUTOEXTEND ON NEXT 100M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.

SQL> alter database default temporary tablespace temp1;

Database altered.

SQL> drop tablespace temp ;

Tablespace dropped.

at the end create spfile

create spfile from pfile='/aptus/oracle/product/11.2.0/db_1/dbs/init.ora' ;

Anuj Singh Oracle DBA

Search This Blog

Total Pageviews