![]() ![]() MRP0: Incarnation has changed! Retry recovery… in Oracle 19c | datafile 1 belongs to an orphan incarnation in Oracle 19c | ORA-19906: recovery target incarnation changed during recovery.Expdp using VIEWS_AS_TABLES in Oracle 19c.Oracle Official Link on How to Kill Oracle Session This is the new feature available from oracle 18c where you can cancel the SQL running on the session without killing the session. ALTER SYSTEM DISCONNECT SESSION ',' IMMEDIATE ALTER SYSTEM CANCEL SQL IMMEDIATE – disconnects the session, recover the entire session state without waiting for on going transaction. ALTER SYSTEM DISCONNECT SESSION ',' POST_TRANSACTION POST_TRANSACTION – Allows session to complete the current transaction before the session is disconnected. When you disconnect a session – And end user try to fire a sql on it. Owner = Process: USER (orapid = 61, ospid = 14489) Requestor = USER (orapid = 49, ospid = 13731, inst = 1) It does the similar thing by destroying the dedicated server process ( or virtual circuit if the connection was made by way of a Shared Server)Īlert log clearly suggest how you tried to kill the session – T10:03:05.171436+04:00 When you kill a session – And end user try to fire a sql on it. ( Session may exists or may not exists but still better to give the inst_id ) If you don’t pass inst_id on RAC system, then you will end up killing a session on which you connected with privileged user. Since RAC database usually have more than one instance, we need to tell the system I want to kill the session from that specific instance using inst_id RAC database Syntax – SQL> ALTER SYSTEM KILL SESSION Immediate is required if you want to kill the session immediately and don’t want Oracle to complete the current operation to complete. and program like '% ALTER SYSTEM KILL SESSION ',' SELECT s.inst_id,s.sid,s.serial#,s.sql_id,p.spid,s.event,s.username,s.program,osuser from gv$session s join gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id where s.username is not null and s.type 'BACKGROUND' Let me tell you the sql which I use to identify the session. Some End users usually note down their SID and SERIAL# for us. To identify a session I will ask certain set of questions to end user like- Is there any way your session can be differentiated based on OSUSER/MACHINE/MODULE/SID/SQL_ID We always be assure we are killing the appropriate session (which is asked for by the application/developer team) in any environment. But before that you need to identify the session. To kill the oracle session you require 3 details – SID, SERIAL# and INST_ID. How to Identify the session details to Kill the session How to Identify the session details to Kill the session. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |