Sunday, October 18, 2009

How to apply CPU Patches in Oracle Database?

Today I am going to give little insight about Opatch and CPU patch.Opatch is a tool is used to patch all oracle homes for oracle applications. CPU is stands for Critical patch updates, in other way its a security patches for Apps.

How to use Opatch?

Opatch is one of the easiest and safest for patching oracle apps because you can rollback opatch if you find any issues applying the patches

1. From the opatch read me make sure which oracle home you want to patch
2. Shutdown the instance related to particular oracle home.
3. Set the oracle home in the env .Set the opatch in the path.
4. Follow the readme whether opatch needs up gradation.
5. cd to the patch area and opatch apply
6. Proceed the post install steps
7. Run cpu_root.sh to give some permissions to the executables.
8. For rollback use opath rollback –id
9. “opatch lsinventory “ is used to list all the patches
10. “opatch lsinventory –details” is used to find the version belongs to particular oracle home.

CPU patch
CPU patch has to keep uptodate. Use opatch to apply CPU patches. Follow the opatch steps to apply the CPU patch. As part of post installation step run catcpu.sql and utlrp.sql

Issues will face

1. When you are applying CPU patch you will get conflict with some patches .Report to Oracle Corporation they will provide a new merged patch if its actually a conflict.

How to backup control file on windows?

Below is the command to backup control file to where ever location we want to.

alter database backup controlfile to trace as 'e:\ctl.txt';

How to backup control file to trace on unix???

===========================================================
#------------------------------------------------------------
# backup controlfile
#------------------------------------------------------------
#------------------------------------------------------------
# find the user_dump_dest directory through a sqlplus call
# and assign it to an environment variable
#------------------------------------------------------------
ORA_DUMP=`${ORACLE_HOME}/bin/sqlplus -s << EOF
/ as sysdba
set heading off
set feedback off
SELECT value
FROM v\\$parameter
WHERE name='user_dump_dest';
exit
EOF`
#------------------------------------------------------------
# create backup controlfile
#------------------------------------------------------------
${ORACLE_HOME}/bin/sqlplus -s <<> /dev/null 2>&1
/ as sysdba
set heading off
set feedback off
ALTER DATABASE BACKUP CONTROLFILE TO trace;
exit
EOF
#------------------------------------------------------------
# find the trace file that the backup controlfile is in
# this should be the last trace file generated
# validate it is a controlfile and copy to backup_control.sql
#------------------------------------------------------------
DATETIME="`date '+%d%m%y'`"
CONTROL=`ls -t $ORA_DUMP/*.trc | head -1`
if [ ! -z "$CONTROL" ]; then
grep 'CONTROL' ${CONTROL} 1> /dev/null
if test $? -eq 0; then
cp ${CONTROL} /home/oracle/backup_control_file_$DATETIME.sql
fi
fi
#end
=========================================================

Script for Datafile usage in Oracle Database

This script will list datafile wise Allocated size, Used Size and Free Size

For running this query you must have SELECT privileges to V$DATAFILE and DBA_FREE_SPACE views or run the sript as sysdba

The Size details are displayed in MB (Mega Bytes)
-----------------------------------------------------------------------

SELECT SUBSTR (df.NAME, 1, 40) file_name, df.bytes / 1024 / 1024 "Allocated Size(MB)",
((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0)) "Used Size (MB)",
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) "Free Size(MB)"
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes
ORDER BY file_name;

Script to check tablespace usage in Oracle Database

SELECT Total.name "Tablespace Name",
nvl(Free_space, 0) "Free Size(MB)",
nvl(total_space-Free_space, 0) "Used Size(MB)",
total_space "Total Size(MB)"
FROM
(select tablespace_name, sum(bytes/1024/1024) free_space
from sys.dba_free_space
group by tablespace_name
) Free,
(select b.name, sum(bytes/1024/1024) total_space
from sys.v_$datafile a, sys.v_$tablespace B
where a.ts# = b.ts#
group by b.name
) Total
WHERE Free.Tablespace_name(+) = Total.name
ORDER BY Total.name;

How to enable primary key on a column having duplicate values in oracle database

You may come across a situation where you have to put Primary key on a column in table that is having duplicate rows and you cannot delete the duplicate rows.

Now this is a tricky situation.....below is the solution for the above problem.

for example you have to put primary key on "B column" "on ABC table" that is having duplicate values.

1) create index ABC_B_INX on B tablespace ABC;

2) alter table ABC add constraint ABC_B_PK primary key(B) disable;

3) alter table ABC enable novalidate constraint ABC_B_PK;

Hope this helps.....

How to check last ddl on a table in Oracle Database

drop table test;

CREATE TABLE test (
testcol VARCHAR2(20))
ROWDEPENDENCIES;

SELECT table_name, dependencies
FROM user_tables where table_name='TEST';

SELECT current_scn
FROM v$database;

INSERT INTO test VALUES ('ABC');

COMMIT;

INSERT INTO test VALUES ('ABC');

COMMIT;

INSERT INTO test VALUES ('ABC');

COMMIT;

SELECT ORA_ROWSCN, rowid, testcol FROM test;

SELECT current_scn
FROM v$database;

UPDATE test
SET testcol = 'DEF'
WHERE rownum = 1;

SELECT ORA_ROWSCN, rowid, testcol FROM test;

COMMIT;

SELECT ORA_ROWSCN, rowid, testcol FROM test;

UPDATE test
SET testcol = 'XYZ';

SELECT ORA_ROWSCN, rowid, testcol FROM test;

COMMIT;

SELECT ORA_ROWSCN, rowid, testcol FROM test;

CREATE TABLE test2 AS
SELECT * FROM test;

COMMIT;

SELECT ORA_ROWSCN, rowid, testcol FROM test2;

INSERT INTO test VALUES ('ABC');

UPDATE test SET testcol = 'DEF' WHERE rownum = 1;

UPDATE test2 SET testcol = 'GHI' WHERE rownum = 1;

COMMIT;

SELECT ORA_ROWSCN, rowid, testcol FROM test;

SELECT scn_to_timestamp(ORA_ROWSCN), rowid, testcol FROM test2;

SELECT scn_to_timestamp(ORA_ROWSCN) FROM test2;

SELECT current_scn
FROM v$database;

Keep observing the output of the above select queries.

How to drop Oracle Database

To drop the database follow the below steps:

shutdown abort;

startup mount exclusive restrict;

drop database;

exit;

Hope this helps.....

How to check locks and kill sessions in Oracle Database

The below script will tell which session is holding the lock

select
c.owner,
c.object_name,
c.object_type,
b.sid,
b.serial#,
b.status,
b.osuser,
b.machine
from
v$locked_object a ,
v$session b,
dba_objects c
where
b.sid = a.session_id
and
a.object_id = c.object_id
/


The below command is used to kill the session from oracle

ALTER SYSTEM KILL SESSION '1626,19894' IMMEDIATE

ALTER SYSTEM DISCONNECT SESSION '13, 8' IMMEDIATE

Steps to change character set of Oracle Database

If you are trying to change the character set, do the following:

% svrmgrl
SVRMGR> connect internal
SVRMGR> shutdown immediate
SVRMGR> startup mount
SVRMGR> alter system enable restricted session;
SVRMGR> alter system set job_queue_processes=0;
SVRMGR> alter system set aq_tm_processes=0;
SVRMGR> alter database open;
SVRMGR> alter database character set WE8ISO8859P1;
SVRMGR> alter database national character set WE8ISO8859P1;
SVRMGR> shutdown immediate;
SVRMGR> startup restrict;
SVRMGR> shutdown immediate;
SVRMGR> startup
SVRMGR> shutdown immediate;
SVRMGR> startup

This procedure is outlined in http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=66320.1 and explains why the database has to be shutdown/startup so many times.