Oracle DBA

Friday, 17 August 2012

Apply Patch set 10.2.0.4

- Stop all oracle components " Services " running like LISTENER, EM , ISQLPLUS and DB itself etc.

- Take a backup of Oracle Home and Database

- Install the patchset 10.2.0.4

- startup upgrade

spool c:\pre_upgrade.log

@C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlu102i.sql

spool off

spool c:\upgrade.log

@C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\catupgrd.sql

spool off

shutdown immediate

startup

spool c:\recompile.log

@C:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlrp.sql

spool off

set lines 10000
set pages 1000
column comp_name format a40
column version format a12
column status format a6
select comp_name, version, status from sys.dba_registry;

Apply Bundle Patch 42 for Patchset 3

Apply Bundle Patch 42 for Patchset 3
1. Cleanly shut down all processes running from the ORACLE_HOME.
2. Upgrade Opatch version for the database with Copy
3. Set the ORACLE_HOME and PATH environment variables to the correct Oracle home that needs to be patched.

set ORACLE_HOME=C:\oracle\product\10.2.0\db_1

set PATH=C:\oracle\product\10.2.0\db_1\Opatch;%PATH%

4. cd C:\p10349197_10204_Win32\10349197
5. opatch apply
6. Start all processes running from the ORACLE_HOME.
7. run these SQLs

sqlplus /nolog
CONNECT / AS SYSDBA
STARTUP
@C:\oracle\product\10.2.0\db_1\Bundle\Patch42\catcpu.sql
@C:\oracle\product\10.2.0\db_1\rdbms\admin\utlprp.sql 0

To check for invalid objects, execute the following statement:
SQL> SELECT OBJECT_NAME FROM DBA_OBJECTS WHERE STATUS = 'INVALID';

Recompile invalid Views:
SELECT * FROM registry$history where ID = '6452863';

sqlplus /nolog
CONNECT / AS SYSDBA
@C:\oracle\product\10.2.0\db_1\Bundle\view_recompile\recompile_precheck_jan2008cpu.sql
SHUTDOWN IMMEDIATE
STARTUP UPGRADE
@C:\oracle\product\10.2.0\db_1\Bundle\view_recompile\view_recompile_jan2008cpu.sql
SHUTDOWN IMMEDIATE
STARTUP

Wednesday, 8 August 2012

Block Developers Tools on Production Database

I have come across a very good post on how to prevent users from using additional
tools to connect to production database. This can also allow us to connect for specific
users only. This can be done by creating the a AFTER LOGON trigger create ON DATABASE.

Below is the script

c:\> sqlplus / as sysdba

SQL> CREATE OR REPLACE TRIGGER block_tools_from_prod
AFTER LOGON ON DATABASE
DECLARE
v_prog sys.v_$session.program%TYPE;
BEGIN
SELECT program INTO v_prog
FROM sys.v_$session
WHERE audsid = USERENV('SESSIONID')
AND audsid != 0 ---- Don't Check SYS Connections
AND ROWNUM = 1; ---- Parallel processes will have the same AUDSID's
IF UPPER(v_prog) LIKE '%TOAD%' OR UPPER(v_prog) LIKE '%T.O.A.D%' OR -- Toad
UPPER(v_prog) LIKE '%SQLNAV%' OR -- SQL Navigator
UPPER(v_prog) LIKE '%PLSQLDEV%' OR -- PLSQL Developer
UPPER(v_prog) LIKE '%BUSOBJ%' OR -- Business Objects
UPPER(v_prog) LIKE '%EXCEL%' -- MS-Excel plug-in
THEN
RAISE_APPLICATION_ERROR(-20000, 'Development tools are not allowed here.');
END IF;
END;
/

Reference : http://psoug.org/snippet.htm/Block_TOAD_and_other_tools_516.htm

Determine OS block size for Linux and Windows

A block is a uniformly sized unit of data storage for a filesystem. Block size can be an important consideration when setting up a system that is designed for maximum performance.

(1) Block size in Linux : If we want to confirm the block size of any filesystem of Ubuntu or any other Linux OS, tune2fs command is here to help:

ubuntu# tune2fs -l /dev/sda1 | grep Block
Block count: 4980736
Block size: 4096
Blocks per group: 32768

From this example, we can see that the default block size for the filesystem on /dev/sda1 partition is 4096 bytes, or 4k. That's the default block size for ext3 filesystem.

OS block size in Solaris :

$perl -e '$a=(stat ".")[11]; print $a'
8192

or
$df -g | grep 'block size'
-----------------------------------------------------------------
(2) Block size in Window Machine : If OS is using ntfs system use the below command :

C:\>fsutil fsinfo ntfsinfo D:
NTFS Volume Serial Number : 0x7a141d52141d12ad
Version : 3.1
Number Sectors : 0x00000000036b17d0
Total Clusters : 0x00000000006d62fa
Free Clusters : 0x00000000001ed190
Total Reserved : 0x0000000000000170
Bytes Per Sector : 512
Bytes Per Cluster : 4096 <<=== (block size)
Bytes Per FileRecord Segment : 1024
Clusters Per FileRecord Segment : 0
Mft Valid Data Length : 0x0000000005b64000
Mft Start Lcn : 0x00000000000c0000
Mft2 Start Lcn : 0x000000000036b17d
Mft Zone Start : 0x000000000043c9c0
Mft Zone End : 0x000000000044b460

Tuesday, 24 July 2012

To Calculate total Useable Disk Space with different RAID Configurations

- To Calculate total Useable Disk Space
www.ibeast.com/content/tools/RaidCalc/RaidCalc.asp

Sunday, 22 July 2012

Drop/Create Oracle Enterprise Manager

Drop/Create Oracle Enterprise Manager

1. make sure the DB Console service is stopped. If not, try to stop it.

2. Delete the service:
sc delete <service_name>

- where <service_name> is the DB Control service name (typically:
OracleDBConsole<sid>)

3. Check if the service is deleted now. If not, reboot the whole server

4. Remove the following directories from your filesystem:
<ORACLE_HOME>/<hostname_sid>
<ORACLE_HOME>/oc4j/j2ee/OC4J_DBConsole_<hostname>_<sid>

5. Logon SQLPLUS as user SYS or SYSTEM, and drop the sysman account and management objects:
SQL> drop user sysman cascade;
SQL> drop role MGMT_USER;
SQL> drop user MGMT_VIEW cascade;
SQL> drop public synonym MGMT_TARGET_BLACKOUTS;
SQL> drop public synonym SETEMVIEWUSERCONTEXT;

6. Rebuild the EM Console: emca -config dbcontrol db -repos create

Script to count specific string in whole statement

1- Create function that will count specific string in whole statement

CREATE OR REPLACE FUNCTION scott.CountString

( pInput VARCHAR2, pSearchString VARCHAR2 )

RETURN number

BEGIN

RETURN (LENGTH (pInput) - LENGTH (REPLACE (pInput, pSearchString, '')))

/ LENGTH (pSearchString);

END;

2- Test it

select scott.CountString ('oracle world','o') from dual;