DBA-Philiac

1. Patching Oracle Database 10.2.0.1 to 10.2.0.4 1.1 Pre-installation Tasks - Identify Oracle home directories, view the /etc/oratab file - Download the p6810189_10204_Linux-x86.zip patch set installation archive to a directory that is not the Oracle home directory or under the Oracle home directory (patch set id: 6810189 ) - Check the current version of the Oracle time zone definitions with the following command: SELECT version FROM v$timezone_file; If this query reports version 4, no action is required; If this reports a version lower or higher then 4: Check if you are storing TZ (TSTZ and TSLTZ) data use this sql query: select c.owner || '.' || c.table_name || '(' || c.column_name || ') -' || c.data_type || ' ' col from dba_tab_cols c, dba_objects o where c.data_type like '%TIME ZONE' and c.owner=o.owner and c.table_name = o.object_name and o.object_type = 'TABLE'

Step 1: -------------------- Create user: oracle Create group: oinstall,dba Add oinstall,dba to user oracle groupadd oinstall groupadd dba useradd oracle -g oinstall -G dba passwd oracle chown -R oracle:oinstall oracle_installation_path Step 2: ------------------ unzip the zip file: gunzip file_name.cpio.gz cpio -idmv < filename.cpio [for 64bit Oracle only] Step 3: ------------------ Change Kernel Parameters append below lines in file:/etc/sysctl.conf # Controls the maximum size of a message, in bytes kernel.msgmnb = 65536 # Controls the default maxmimum size of a mesage queue kernel.msgmax = 65536 # Controls the maximum shared segment size, in bytes kernel.shmmax = 2147483648 [keep OS provided one] # Controls the maximum number of shared memory segments kernel.shmall = 268435456 [keep OS provided one] kernel.shmmni = 4096 kernel.sem = 250 32000 100 128 fs.file-max = 65536 #ins/decrease net.ipv4.ip_local_port_range=1024 65000 net.core.rmem_default = 262144 net.core.rmem_max = 2621

Database Version Upgrade Path of OTN: Oracle Database Upgrade Path Reference List [ID 730365.1] RMAN Help: http://psoug.org/reference/rman.html Install Oracle 11g with ASM in linux http://www.oracle-wiki.net/startdocshowtoinstalloracle11glinuxasm Install Oracle Appex http://www.oracle.com/technetwork/developer-tools/apex/application-express/packaged-apps-090453.html SRVCTL command ref http://download.oracle.com/docs/cd/E14072_01/server.112/e10595/restart005.htm Linux Shell Scripting http://linuxcommand.org/writing_shell_scripts.php Listener hangs http://arjudba.blogspot.com/2009/01/listener-hangs-child-listener-process.html RAID model for Oracle DB http://www.orafaq.com/wiki/RAID http://www.dba-oracle.com/oracle_tips_raid_usage.htm

create or replace procedure kill_session( p_sid in varchar2, p_serial# in varchar2) is cursor_name pls_integer default dbms_sql.open_cursor; ignore pls_integer; BEGIN select count(*) into ignore from V$session where username = USER and sid = p_sid and serial# = p_serial# ; if ( ignore = 1 ) then dbms_sql.parse(cursor_name, 'alter system kill session ''' ||p_sid||','||p_serial#||'''', dbms_sql.native); ignore := dbms_sql.execute(cursor_name); else raise_application_error( -20001, 'You do not own this session ''' || p_sid || ',' || p_serial# || '''' ); end if; END; / Now I have created synonym under that user who wants this privilege create or replace synonym USERNAME.KILL_OWN_SESSION for SYS.KILL_SESSION; Now I connect