Solution Kill user sessions in RAC 10g Goal The 10g version of Oracle RAC only allows ALTER SYSTEM KILL SESSION (SID, Kill_Session_User(p_username VARCHAR2) – This takes the schema/user name and will loop through all sessions for this user in gv$sessions — Then uses supplies this SID, SERIAL#, INST_ID to Kill_Session_Job Kill_Session_Job(p_sid NUMBER, p_serial NUMBER, p_inst_id … Read more
I was asked to check that we have a backup of database prior to decommissioning it and found the following which was interesting. As we have implemented a backup strategy at the company I work, I was a little confused since we just introduced a new backup method with Netbackup that now uses an intelligent … Read more
SELECT ‘ restore archivelog from logseq ‘ || applied_arc.startNo || ‘ until logseq ‘ || catalog_arc.endNo || ‘ thread=’ || catalog_arc.thread# || ‘;’ “Restore Command” FROM applied_arc, ( SELECT thread#, MAX (sequence#) startNo FROM gv$archived_log WHERE applied = ‘YES’ GROUP BY thread#) applied_arc, ( SELECT thread#, MAX (sequence#) endNo FROM v$backup_archivelog_details GROUP BY thread#) catalog_arc … Read more
Amazon have documented this process, however this is for 11g source Oracle database only, and there are a few steps that must be adhered to in order to get this to work. Please note, there are a few changes from 10g to 11g using orapki, specifically with the auto_login command. Please set your Oracle environment … Read more
We need to resize temp, but trying to resize hangs. Lets see what is using TEMP SELECT tu.username, s.sid, s.serial#, S.MACHINE, s.inst_id, tu.tablespace FROM gv$tempseg_usage tu, gv$session s WHERE tu.session_addr = s.saddr; DBSNMP 1715 99 myhost 1 TEMP2 You can either wait for the sessions to complete or in my case, it was the agent … Read more
You can shrink the datafile of the UNDO tablespace on the primary database to 1G by using the following command: ALTER DATABASE DATAFILE ‘+DATA/datafile/undotbs01.369.942861921’ RESIZE 2048M; ASM needs the correct ASM filename. The undo datafile on the standby database will be automatically resized as well. If you get the following error: ORA-03297: file contains used … Read more
Find the sessions using the default temporary tablespace. select tu.username,s.sid,s.serial#, S.MACHINE,s.inst_id, tu.tablespace from gv$tempseg_usage tu, gv$session s where tu.session_addr=s.saddr;
Step 1: Get the sql_id of the oracle session you would like to tune. There are a number of ways to get this, this is probably the easiest. select x.sid ,x.serial# ,x.username ,x.sql_id ,x.sql_child_number ,optimizer_mode ,hash_value ,address ,sql_text from v$sqlarea sqlarea ,v$session x where x.sql_hash_value = sqlarea.hash_value and x.sql_address = sqlarea.address and x.username is not … Read more
Goal The 10g version of Oracle RAC only allows ALTER SYSTEM KILL SESSION (SID,SERIAL#); as a local session on a local node. To kill from one node sessions across multiple nodes the following script has been created… Kill_Session_User(p_username VARCHAR2) – This takes the schema/user name and will loop through all sessions for this user in … Read more
Step 1. Symptoms when trying to drop a undo tablespace SQL> drop tablespace undotbs1; drop tablespace undotbs1 * ERROR at line 1: ORA-01548: active rollback segment ‘_SYSSMU1_1282527427$’ found, terminate dropping tablespace Step 2. File which rollback segments to remove SQL> SELECT segment_name, tablespace_name, status FROM sys.dba_rollback_segs; 2 SEGMENT_NAME TABLESPACE_NAME STATUS —————————— —————————— —————- SYSTEM SYSTEM … Read more