f 五月 2021 ~ 迪貝之家

Microsoft SQL Server Log Shipping

SQL 7就有的技術,簡單的說就是資料庫複本資料同步的技術,而這份複本可以開放讀取的功能以分散營業資料庫的負載,也可純粹當成備援資料庫,不管是在本地還是異地。怎麼達成? 透過SQL Agent自動化排程進行備份、傳檔及復原等作業。

Oracle GolGateden

機房搬遷跨Site能即時同步資料庫利器

Nagios 資料庫維運自動化

一開始設計這架構時,就排除使用remote agent的想法因為在專業分工的組織下,要求安裝新軟體於既有系統是一件不太可行的方案,既然身為DBA就只能把資料庫instance當作是一個最大的agent 想辦法在資料庫內做到我想做的事情

This is default featured post 4 title

Go to Blogger edit html and find these sentences.Now replace these sentences with your own descriptions.

This is default featured post 5 title

Go to Blogger edit html and find these sentences.Now replace these sentences with your own descriptions.

碰到Oracle Lock Wait

 

用了10g以上的版本後
最常用的工具就是awr 及addm
以前常用的script都丟掉了
只是最近又幹起了Programmer的工作
你就不太可能像以前幹dba一樣
知道詳細的資料庫連接資料
碰到都是人家已經在工具UI已經弄妥的設定
你只能直接查internal view來解決問題
昨天要refresh整個schema,然後重匯資料
結果有lock,無法清空
臨時間又懶得去翻找以前的東西
感謝Internet
資料隨手可得阿
以前的老dba都雪藏秘珍這種東西啦~~
所以....這些人現在都活不下去了
昨天對我有幫助的是下列這段語法
select
c.owner,
c.object_name,
c.object_type,
b.sid,
b.serial#,
b.status,
b.osuser,
b.machine
from
v$locked_object a ,
v$session b,
dba_objects c
where
b.sid = a.session_id
and
a.object_id = c.object_id;
砍掉該session,就能繼續做事了
出處 :
http://www.dba-oracle.com/t_find_oracle_locked_objects.htm

RAC:
select
b.inst_id,
c.owner,
c.object_name,
c.object_type,
b.sid,
b.serial#,
b.status,
b.osuser,
b.machine
from
gv$locked_object a ,
gv$session b,
dba_objects c
where
b.sid = a.session_id
and
a.object_id = c.object_id;

alter system kill session 'sid,serial#,@inst_id';


###2021/05/25 找到lock chain####
這適用於有個session 卡住了後續其他的session
砍掉它之後,系統就能跑了
主要是查v$wait_chains
這東西不得了
怎麼以前沒看過




col ses format a15
set lines 180
WITH blocked AS
(SELECT * FROM
(SELECT instance, sid, sess_serial#, blocker_instance,
blocker_sid, blocker_sess_serial#, level lv,
num_waiters, blocker_chain_id
FROM v$wait_chains
CONNECT BY PRIOR sid = blocker_sid
AND PRIOR sess_serial# = blocker_sess_serial#
AND PRIOR INSTANCE = blocker_instance
START WITH blocker_is_valid = 'FALSE'
)
WHERE num_waiters >0 OR blocker_sid IS NOT NULL
)
SELECT instance,
LPAD(' ', 2*(lv-1)) ||b.sid ses, b.sess_serial#,
b.blocker_instance, b.blocker_sid, b.blocker_sess_serial#
FROM blocked b
hang analysis也可以抓wait chain
SQL> oradebug setmypid
SQL> oradebug -g all hanganalyze 10



























這是8720.trc 的內容


指令alter system kill session '33,1361';
lock wait 就解了

不過底下這傢伙
寫得更精采
讓我不得不佩服
這些人到底去哪裡找到的資料???
The Backdoor Entry to Oracle Database
















看起來,同樣的lock wait chain的情境
無法用hanganalyze 12 來解決
有時間倒是可以研究一下各 level 要看什麼東西
session無法登入的情況
以前是真的碰過一次
當時要是知道這方法
也許就能查明原因了

### 2021/06/01####
今天碰到一個狀況
居然連select_catalog_role的權限都不給
真的是....莫名其妙
只好去找一個一個對應的fixed table的名稱
grant select on sys.gv_$session to steven;
grant select on sys.gv_$locked_object to steven;
grant select on sys.v_$wait_chains to steven;

--Wait Chain---
col ses format a15
set lines 180
WITH blocked AS
(SELECT * FROM
(SELECT instance, sid, sess_serial#, blocker_instance,
blocker_sid, blocker_sess_serial#, level lv,
num_waiters, blocker_chain_id
FROM sys.v_$wait_chains
CONNECT BY PRIOR sid = blocker_sid
AND PRIOR sess_serial# = blocker_sess_serial#
AND PRIOR INSTANCE = blocker_instance
START WITH blocker_is_valid = 'FALSE'
)
WHERE num_waiters >0 OR blocker_sid IS NOT NULL
)
SELECT instance,
LPAD(' ', 2*(lv-1)) ||b.sid ses, b.sess_serial#,
b.blocker_instance, b.blocker_sid, b.blocker_sess_serial#
FROM blocked b;

--Lock Objects ---
select * from sys.v_$wait_chains ;
select
b.inst_id,
c.object_name,
c.object_type,
b.sid,
b.serial#,
b.status,
b.osuser,
b.machine
from
sys.gv_$locked_object a ,
sys.gv_$session b,
user_objects c
where
b.sid = a.session_id
and
a.object_id = c.object_id;

Oracle STA with STS

 BEGIN

dbms_sqltune.create_sqlset(

sqlset_name => 'MY_TUNING_SET'

,description => 'MY STS from AWR');

END;

/


DECLARE

base_cur dbms_sqltune.sqlset_cursor;

BEGIN

OPEN base_cur FOR

SELECT value(x)

FROM table(dbms_sqltune.select_workload_repository(

5153,5171, null, null,'disk_reads',

null, null, null, 15)) x;

--

dbms_sqltune.load_sqlset(

sqlset_name => 'MY_TUNING_SET',

populate_cursor => base_cur);

END;

/


SELECT sqlset_name, elapsed_time, cpu_time, buffer_gets, disk_reads, sql_text

FROM dba_sqlset_statements where sqlset_name = 'MY_TUNING_SET';



SELECT

sql_id, elapsed_time, cpu_time, buffer_gets, disk_reads, sql_text

FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET('MY_TUNING_SET'));



variable mytt varchar2(30);

exec :mytt := DBMS_SQLTUNE.CREATE_TUNING_TASK(sqlset_name => 'MY_TUNING_SET');

print :mytt


 exec dbms_sqltune.execute_tuning_task(task_name => :mytt);

 set long 10000 longchunksize 10000 linesize 132 pagesize 200

 select dbms_sqltune.report_tuning_task(:mytt) from dual;


exec DBMS_SQLTUNE.DROP_TUNING_TASK(:mytt);

EXEC DBMS_SQLTUNE.DROP_SQLSET(sqlset_name => 'MY_TUNING_SET');


Tuning Advisor 的權限 :

grant all on dbms_advisor to urname;

grant advisor to urname;

grant ADMINISTER SQL TUNING SET to urname;

Oracle SAA with STS

 --- SQL Tuing Set ----

BEGIN

dbms_sqltune.create_sqlset(

sqlset_name => 'MY_TUNING_SET'

,description => 'MY STS from AWR');

END;

/


DECLARE

base_cur dbms_sqltune.sqlset_cursor;

BEGIN

OPEN base_cur FOR

SELECT value(x)

FROM table(dbms_sqltune.select_workload_repository(

5153,5171, null, null,'disk_reads',

null, null, null, 15)) x;

--

dbms_sqltune.load_sqlset(

sqlset_name => 'MY_TUNING_SET',

populate_cursor => base_cur);

END;

/


-- SQL Access Advisor

-- Check current task 

select TASK_ID,TASK_NAME,ADVISOR_NAME,CREATED,LAST_MODIFIED,LAST_EXECUTION, 

EXECUTION_TYPE,STATUS,STATUS_MESSAGE,RECOMMENDATION_COUNT from user_advisor_tasks 

where ADVISOR_NAME in ('SQL Access Advisor','SQL Tuning Advisor'); 

/*Creating a Access Advisor task */


  DECLARE 

    task_id NUMBER; 

    task_name VARCHAR2(30); 

  BEGIN 

    task_name := 'ACCESSFORTEST01'; 

  DBMS_ADVISOR.CREATE_TASK(DBMS_ADVISOR.SQLACCESS_ADVISOR, task_id, task_name); 

  END; 

  / 



--- 連結 SAA與STS

exec dbms_advisor.ADD_STS_REF ('ACCESSFORTEST01','YOUR USER NAME','MY_TUNING_SET');

-- 執行SAA 分析工作

exec dbms_advisor.execute_task('ACCESSFORTEST01'); 

-- 查看建議

select dbms_advisor.get_task_script('ACCESSFORTEST01') from dual;


--砍掉SAA Task

exec dbms_advisor.delete_task('ACCESSFORTEST01');


--砍掉STS

EXEC DBMS_SQLTUNE.DROP_SQLSET(sqlset_name => 'MY_TUNING_SET');

Tuning Advisor 的權限 :
grant all on dbms_advisor to urname;
grant advisor to urname;
grant ADMINISTER SQL TUNING SET to urname;

Oracle STS and STA for short time stress test (My Scenario)

 

之前的專案曾在RAC 跑壓測
不過每次跑都不可能超過一個小時
AP也沒那麼好配合
剛好準點來排壓測
所以ADDM及AWR根本無用武之地
最多就是能看看ASH報表
了不起頂多再用oratop
即時監看session的運作狀況
最近看到了STS及STA
水.......
這是我假想的scenario
每20秒由shared pool 抓資料
持續抓取10分鐘

select instance_number from v$instance;

BEGIN
-- Create the tuning set
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'INST1'
,description => 'INST1 workload sample');
--
DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET(
sqlset_name => 'INST1'
,time_limit => 600
,repeat_interval => 20
,,capture_mode => DBMS_SQLTUNE.MODE_ACCUMULATE_STATS);
END;
/
variable mytt varchar2(30);
exec :mytt := DBMS_SQLTUNE.CREATE_TUNING_TASK(sqlset_name => 'INST1');
print :mytt
exec dbms_sqltune.execute_tuning_task(task_name => the :mytt);
set long 10000 longchunksize 10000 linesize 132 pagesize 200
select dbms_sqltune.report_tuning_task( the :mytt) from dual;

---------------------------------------------------------------------------------

select instance_number from v$instance;

BEGIN
-- Create the tuning set
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'INST2'
,description => 'INST2 workload sample');
--
DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET(
sqlset_name => 'INST2'
,time_limit => 600
,repeat_interval => 20
,,capture_mode => DBMS_SQLTUNE.MODE_ACCUMULATE_STATS);
END;
/
variable mytt varchar2(30);
exec :mytt := DBMS_SQLTUNE.CREATE_TUNING_TASK(sqlset_name => 'INST2');
print :mytt
exec dbms_sqltune.execute_tuning_task(task_name => the :mytt);
set long 10000 longchunksize 10000 linesize 132 pagesize 200
select dbms_sqltune.report_tuning_task(the :mytt) from dual;

無法drop STS(SQL Tuing Set)

 

ORA-13757: “SQL Tuning Set” “string” owned by user “string” is active.
主因是該STS 被你拿來建了一個Tuning Task

 

1.先去找dba_advisor_log找到可能的task,drop 它
select * from user_advisor_log;

 

drop tuning task的語法
exec DBMS_SQLTUNE.DROP_TUNING_TASK('SQLACCESS3638400');

 

2.然後再到user_sqlset找STS 的資料,drop 它
select * from user_sqlset;

 

drop STS 的語法
exec DBMS_SQLTUNE.DROP_SQLSET(sqlset_name => 'SQLACCESS3638400_sts');

Oracle SQL Tuning Advisor 你手頭上的SQL 語句

 declare

  TaskID  varchar2(500);

  VAR_SQL     varchar2(2000);

begin

  -- 要被Tune 的SQL

  VAR_SQL := 'delete STEVEN.SAMPLE '

          ||' where  ( code, dis  ) in ( select a.id ,b.dis '

  ||' from   STEVEN.SAMPLE a, STEVEN.RE b '

  ||' where  a.system_id = b.system_id '

  ||' and   to_char(a.create_date,''YYYYMMDD'') >= ''20101001'' )';

  

  -- 建立  Tuning Task

  TaskID := dbms_sqltune.create_tuning_task(

             sql_text => VAR_SQL

           , user_name => 'Your Schema'       

           , scope => 'COMPREHENSIVE'  

           , task_name => 'STEVEN' 

           );

  

  -- 執行 Tuning Task

  dbms_sqltune.execute_tuning_task( 'STEVEN' );

end;

/


== 查看 Tuning Task 狀態==

select status

  from dba_advisor_log

 where owner = 'Your Schema'

   and task_name = 'STEVEN';


== 查看Recommandation=====

SET long 2000000000 <-這很重要, 太小, Recommandation出不來

SET LONGCHUNKSIZE 1000

SET LINESIZE 100

select dbms_sqltune.report_tuning_task( 'STEVEN' )

  from dual;

這是結果:

-------------------------------------------------------------------------------

ADDITIONAL INFORMATION SECTION


DBMS_SQLTUNE.REPORT_TUNING_TASK('STEVEN')                                                           

----------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------

- 不支援的 SQL 敘述句類型.

哈~~  SQL  寫太爛了...這種寫法是我曾經看過PG寫過的IN Group的語法
我就寫來看看....果然是有很嚴重的效能問題
連工具都不願意做調整

================================================
如果有sys權限,就直接看ADDM報表了
SQL> select task_name, status from dba_advisor_tasks where task_id=
(select max(task_id) from dba_advisor_tasks where status='COMPLETED' );

ADDM:4005309656_8927 COMPLETED


SQL> SET LONG 1000000 PAGESIZE 0;
SQL>SELECT DBMS_ADDM.GET_REPORT('ADDM:4005309656_8927') from dual;

Tuning Advisor 的權限 :
grant all on dbms_advisor to urname;
grant advisor to urname;
grant ADMINISTER SQL TUNING SET to urname;

OCR 的復原--沒真的run過,待驗證

 


查看OCR 檔案狀態 , 用root
# ocrcheck
查看OCR檔名及路徑
# ocrcheck -config
查看備份
ocrconfig -showbackup
情境一: OCR 所有檔案損毀
從上述ocrconfig -showbackup 的備份復原OCR
用root執行
1. 在每個node強制關掉clusterware
# crsctl stop crs -f
2.在其中一個NODE,復原OCR 檔案
# ocrconfig –restore backup02.ocr
3.依序在每個NODE啟動Clusterware
# crsctl start crs
# cluvfy comp ocr –n all -verbose

情境二: OCR 所在的ASM DG掛掉了
解法就是把OCR 所在的DG重建後
再依情境一的步驟回復OCR

1. 在某個node, 以root身分啟動clusterware為
   exclusive 模式
# crsctl start crs –excl -nocrs

2.連到Local ASM Instance,重建OCR DG
SQL> drop diskgroup DG_OCR force including contents;
SQL> create diskgroup DG_OCR external redundancy disk 'diskname' attribute 'COMPATIBLE.asm'='12.1.0';

3.依照情境一的步驟,回復眼前這一台的OCR
      但照書上的說法,不用shutdown clusterware

4.通常VOTING  DISK 也是在同一個DG,因此也要重建

# crsctl replace votedisk +DG_OCR

5.關掉眼前這一台的clusterware,然後啟動整個clusterware


情境三: OCR掛了,但沒備份. 這就沒辄了
唯一的做法就是解除cluster,然後重建cluster
重建完cluster後,要手動把db , listener等資源手動加入OCR來控管
1. 依序在各node,以root身分執行下列指令,解除Cluster的設定
#$GI_HOME/crs/install/rootcrs.pl –deconfig –force –verbose

2.上述步驟完成後,在第一台執行指令
#$GI_HOME/crs/install/roortcrs.pl –deconfig –force –verbose –lastnode

3.解除掉Cluster設定後,重新組成一個Cluster,會叫起UI畫面
#$GI_HOME/crs/config/config.sh

至於resource的手動加入,坦白說,哪個DBA平日真的會去記錄阿~~遇到鐵定是完蛋.......以後有跑客戶,還是抓下來吧~~

上述三個情境如果救回來,底下三個指令來檢核狀態
# ocrcheck
# cluvfy comp ocr-n all –verbose
# crsctl check cluster -all

Check RAC IP

 check rac database

$srvctl config database

check scan ip

$ srvctl config scan


check vip

$ srvctl config vip -n knewracn1

VIP exists: network number 1, hosting node knewracn1

VIP IPv4 Address: -/knewracn1-vip/172.16.150.37

VIP IPv6 Address:

$ srvctl config vip -n knewracn2

VIP exists: network number 1, hosting node knewracn2

VIP IPv4 Address: -/knewracn2-vip/172.16.150.182

VIP IPv6 Address: