oracle 切換undo tablespace小結

/* 1. 檢查引數檔案型別 ,如果不是spfile ,scope=memory ,要記得儘快去修改pfile */
SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /opt/oracle/112/dbs/spfilemyor
                                                 cl11.ora
/* 2. 檢視當前資料檔案size */
select tablespace_name,sum(bytes)/1024/1024/1024 GB
from dba_data_files group by tablespace_name
union all
select tablespace_name,sum(bytes)/1024/1024/1024 GB
from dba_temp_files group by tablespace_name order by GB;

/* 3. 建立一個新undo tbs,先df確認有足夠空間,預設的AUTOEXTEND OFF*/
create undo tablespace UNDOTBS02 datafile '/opt/oracle/oradata/myorcl11/undotbs02.dbf' size 100m ;

/* 4. 檢視當前事務,存在活動事務時,是可以切換undo tbs的,只是正使用的回滾段無法offline,
仍保持online狀態(dba_rollback_segs),而在v$rollstat中段的status是PENDING OFFLINE */
select count(*) from v$transaction;

/* 此時如果drop原來tbs,會收到  */
SQL> drop tablespace undotbs01 including contents;
drop tablespace undotbs01 including contents
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS01' is currently in use
/* 5. 等待dba_rollback_segs原tbs的段全部offline,或者看v$rollstat已不存在 */
drop tablespace undotbs01 including contents and datafiles;

/* 如果急著想幹掉原來的transaction */
select ss.sid,ss.SERIAL#,p.SPID,ss.SCHEMANAME,ss.OSUSER,ss.TERMINAL,ss.PROGRAM
from v$session ss, v$process p, v$transaction t
where ss.TADDR = t.ADDR
and ss.PADDR = p.ADDR
and t.UBAFIL = &fno; --&fno是undotbs1的file#

/* 記下 p.SPID 3293 ,沒記看最後...

因為alter system kill session 會使paddr 指向同一個虛擬地址.
此時v$process和v$session失去關聯,程式就此中斷.
然後Oracle就等待PMON去清除這些Session.
所以通常等待一個被標記為Killed的Session退出需要花費很長的時間.
如果此時被Kill的process，重新嘗試執行任務，那麼馬上會收到程式中斷的提示，
process退出，此時Oracle會立即啟動PMON來清除該session.這被作為一次異常中斷處理 */

SQL> alter system kill session '39,35';

System altered.

/* 此時v$session status被標記為killed,事務回滾,在v$transaction中消失,
但dba_rollback_segs,v$rollstat仍是佔用狀態*/

/* kill ospid, 如果session 一直處於killed 狀態，那麼可以考慮在作業系統級別kill掉相關的程式。
不過在操作之前，要先確認session 是否在執行rollback 操作, v$session關聯查不到v$transaction就okay  */
# lsof -p 3293
oracle  3293 oracle   11u   REG    8,2  10493952 283525 /opt/oracle/oradata/myorcl11/append02.dbf
oracle  3293 oracle   12u   REG    8,2 104865792 282798 /opt/oracle/oradata/myorcl11/undotbs01.dbf (deleted)

--檔案控制程式碼未釋放,還可以恢復檔案
# cp /proc/3293/fd/12 ~/undotbs01.dbf.frm            

# kill -9 3293
--至此undotbs01才被真正刪除,空間釋放

/* 網上查到還有一個語句 */
SQL> ALTER SYSTEM DISCONNECT SESSION'sid,serial#' POST_TRANSACTION;
SQL> ALTER SYSTEM DISCONNECT SESSION'sid,serial#' IMMEDIATE;

POST_TRANSACTION 選項會等待事務完成之後在斷開連線。
IMMEDIATE 選項會立即斷開連線，然後事務會進行recover操作。

/* 與kill session 命令不同，disconnect session 命令會kill 掉 dedicated server process,
該命令等同於在作業系統級別kill 掉server process。
使用alter system disconnectsession 命令就不需要切換到系統來kill session，
也從而減少了kill 錯程式的機率 */

/* 如果已經asks了,沒記下v$process.spid,因為v$process的資訊都不會動
所以還是不難找,下面的sql可基本上最快定位到process*/
select pid,spid,pname,serial#,program from v$process
where addr in (
select p.addr from v$process p where pid <> 1 
minus 
select s.paddr from v$session s);

       PID SPID                     PNAME    SERIAL# PROGRAM
---------- ------------------------ ----- ---------- -----------------------------------
        17 3073                     D000           1 oracle@localhost.myrh6 (D000)
        18 3075                     S000           1 oracle@localhost.myrh6 (S000)
        36 4195                                   81 oracle@localhost.myrh6 (TNS V1-V3)

--總結就是,不要太著急,多等會再drop總是好的

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28859270/viewspace-776228/，如需轉載，請註明出處，否則將追究法律責任。