DBA常用命令之東北大亂燉
###一:執行計劃篇### ###二:統計資訊篇### ###三:AWR報告篇### ###四:格式篇### ###五:hint篇### ###六:會話篇### ###七:日誌挖掘LOGMNR篇### ###八:備份與恢復### ###九:壞塊### ###十:表空間### ###十一:日誌檔案### ###十二:RAC### ###十三:12c### ###十四:需要關閉的特性### ###十五:歸檔日誌### ###十六:dbms_metadata.get_ddl### ###十七:Oracle遊標溢位### ###十八:BBED### ###十九:大表刪除### ###二十:OEM### ###二十一:建立測試資料### ###二十二:EXP-00056### ###二十三:ORA-01102### ###二十四:登入觸發器### ###二十五:程式碼裡SQL增加隨機數### ###二十六:PLSQL亂碼### ###二十七:SCN### ###二十八:資料初始化### ###二十九:DB2### ###三十:sqlserver### ###三十一：mysql### ###三十二:AIX### ###三十三:Linux###
###一:執行計劃篇###
如何獲取執行計劃：
參考：How to Obtain a Formatted Explain Plan - Recommended Methods (Doc ID 235530.1)
版本10.2及更高版本,最後執行的SQL：
在10.2及更高版本中，如果已經執行了SQL，則可以從庫快取中提取執行計劃（除了早期版本中的標準解釋計劃選項之外）。
要獲取上次執行的SQL問題的計劃，請執行以下操作：
set linesize 150
set pagesize 2000
select * from table(dbms_xplan.display_cursor(null,null, 'ALL'));
###SQL_ID 和 child number是已知的###
一個SQL_ID可以有多個具有不同特徵的child number。您可以透過從V$SQL中選擇來標識child number，如下所示：
SELECT sql_id, hash_value, child_number, SUBSTR(sql_text,1,40) Text FROM v$sql WHERE sql_text LIKE '%&Some_Identifiable_String%' /
set linesize 150
set pagesize 2000
select * from TABLE(dbms_xplan.display_cursor('&SQL_ID'));
或
select * from TABLE(dbms_xplan.display_cursor('&SQL_ID', &CHILD));
###gather_plan_statistics hint###
帶有一些附加選項的gather_plan_statistics hint也可能提供執行時統計資訊：例如：
select /*+ gather_plan_statistics */ col1, col2 etc.....
set linesize 150
set pagesize 2000
select * from TABLE(dbms_xplan.display_cursor('&SQL_ID', &CHILD,'ALL IOSTATS LAST'))
###透過SQL_ID獲取###
For SQL ID :
select * from table(dbms_xplan.display_cursor('&sql_id'));
select * from table(dbms_xplan.display_cursor('&sql_id', NULL, 'ALL'));
For SQL ID, Child Cursor :
select * from table(dbms_xplan.display_cursor('&sql_id', &child_number, 'ALL'));
For SQL Text :
select t.*
from v$sql s, table(dbms_xplan.display_cursor(s.sql_id, s.child_number, 'ALL')) t
where s.sql_text like '%&querystring%';
###從AWR中獲取執行計劃###
awrgrpt.sql
AWR SQL report是從特定時間段獲取執行計劃的一種簡單方法。
使用以下命令啟動報告：
$ORACLE_HOME/rdbms/admin/awrsqrpt.sql
輸入sql_id
For SQL ID :
select * from table(dbms_xplan.display_awr('&sql_id')) ;
select * from table(dbms_xplan.display_awr('&sql_id', NULL, NULL, 'ALL')) ;
For SQL ID, Plan Hash Value in the current database :
select * from table(dbms_xplan.display_awr('&sql_id', '&plan_hash_value', NULL, 'ALL')) ;
For SQL ID, Plan Hash Value in a different database ID :
select * from table(dbms_xplan.display_awr('&sql_id', '&plan_hash_value', &dbid, 'ALL')) ;
For SQL ID that was executed during a certain period :
select t.*
from (select distinct sql_id, plan_hash_value, dbid
from dba_hist_sqlstat
where sql_id = '&sql_id'
and snap_id between &begin_snap and &end_snap) s,
table(dbms_xplan.display_awr(s.sql_id, s.plan_hash_value, s.dbid, 'ALL')) t;
For SQL Text :
select t.*
from (select distinct r.sql_id sql_id, r.plan_hash_value plan_hash_value, r.dbid dbid
from dba_hist_sqltext q, dba_hist_sqlstat r
where q.sql_id = r.sql_id
and q.sql_text like '%&querystring%') s,
table(dbms_xplan.display_awr(s.sql_id, s.plan_hash_value, s.dbid, 'ALL')) t;
###From SQL Tuning Set (STS)
Note : STS owner is the current user by default.
For SQL ID in a STS :
select * from table (dbms_xplan.display_sqlset( '&sts_name','&sql_id'));
For All Statements in a STS :
select t.*
from dba_sqlset_statements s, table(dbms_xplan.display_sqlset(s.sqlset_name, s.sql_id, s.plan_hash_value, 'ALL', s.sqlset_owner)) t
where s.sqlset_name = '&sts_name';
For SQL ID, Plan Hash Value in a STS :
select * from table (dbms_xplan.display_sqlset( '&sts_name','&sql_id', '&plan_hash_value', 'ALL'));
For SQL ID, Plan Hash Value, STS Owner :
select * from table (dbms_xplan.display_sqlset( '&sts_name','&sql_id', '&plan_hash_value', 'ALL', '&sts_owner'));
For SQL Text in a STS :
select t.*
from dba_sqlset_statements s, table(dbms_xplan.display_sqlset(s.sqlset_name, s.sql_id, s.plan_hash_value, 'ALL', s.sqlset_owner)) t
where s.sqlset_name = '&sts_name'
and s.sql_text like '%&querystring%';
###From SQL Plan Baseline
For SQL Handle :
select * from table(dbms_xplan.display_sql_plan_baseline('&sql_handle'));
For SQL Handle, Plan Name :
select * from table(dbms_xplan.display_sql_plan_baseline('&sql_handle', '&plan_name', 'ALL'));
For SQL Text :
select t.*
from (select distinct sql_handle, plan_name
from dba_sql_plan_baselines
where sql_text like '%&querystring%') s,
table(dbms_xplan.display_sql_plan_baseline(s.sql_handle, s.plan_name, 'ALL')) t;
Version 9.2 and higher
Dbms_xplan
With Oracle 9.2, Oracle supplies a utility called dbms_xplan. It is created by dbmsutil.sql which is called by catproc.sql. As such it should already be installed on most 9.2 databases.
To generate a formatted explain plan of the query that has just been 'explained':
SQL> set lines 130
SQL> set head off
SQL> spool
SQL> alter session set cursor_sharing=EXACT;
SQL> select plan_table_output from table(dbms_xplan.display('PLAN_TABLE',null,'ALL'));
SQL> spool off
Versions 8.1.7 and 9.0.1
From version 8.1.5, Oracle has supplied 2 scripts to extract formatted explain plans from plan_tables.
One is for serial plans and the other is for parallel plans.
Scripts can be found under $ORACLE_HOME/rdbms/admin.
Examples of their usage are below.
Serial Plans
To obtain a formatted execution plan for serial plans:
SQL> set lines 130
SQL> set head off
SQL> spool
SQL> @@?/rdbms/admin/utlxpls
SQL> spool off
Parallel Plans
To obtain a formatted execution plan for parallel plans:
SQL> set lines 130
SQL> set head off
SQL> spool
SQL> @@?/rdbms/admin/utlxplp
SQL> spool off
Autotrace
Autotrace can also be used.
It shows more information about distributed queries but in earlier versions, the output is often badly formatted. If using autotrace, there is no need to 'explain' the query
SQL> set lines 130
SQL> set long 2000
SQL> set head off
SQL> spool >>spool file <<
SQL> set autotrace trace explain
SQL> @@ >> your query <<
SQL> spool off
Creating Plan Table
If required, the initial steps to create a plan table and make the optimizer populate the plan table with the execution plan are common to all supported versions of Oracle.
1. Use the utlxplan.sql script to create the plan table as instructed below.
SQL> @@?/rdbms/admin/utlxplan
2. On 10g and above there is a new script - catplan.sql - to create the plan table that
creates a public plan table as a global temporary table accessible from any schema
SQL> @@?/rdbms/admin/catplan
Identifying the SQL
sql_id:
The SQL_ID specifies the sql_id value for a specific SQL statement, and can be found in:
V$SQL.SQL_ID,
V$SESSION.SQL_ID,
V$SESSION.PREV_SQL_ID,
V$SQL.SQL_ID,
V$SESSION.SQL_ID,
V$SESSION.PREV_SQL_ID.
If no sql_id is specified, the last executed statement of the current session is shown.
cursor_child_no:
The child cursor number specifies the child number for a specific sql cursor, and can be found in:
V$SQL.CHILD_NUMBER
V$SESSION.SQL_CHILD_NUMBER
V$SESSION.PREV_CHILD_NUMBER.
1. explain plan命令
PL/SQL Developer中透過快捷鍵F5可以檢視目標SQL的執行計劃。
實際後臺呼叫的就是explain plan命令，相當於封裝了該命令。
explain plan使用方法：
(1) 執行explain plan for + SQL (2) 執行select * from table(dbms_xplan.display);
2. DBMS_XPLAN包
SELECT sql_id, child_number FROM v$sql WHERE sql_text LIKE '%XXX%';
(1) select * from table(dbms_xplan.display);
(2) select * from table(dbms_xplan.display_cursor(null, null, 'advanced'));
(3) select * from table(dbms_xplan.display_cursor(null, null, 'all'));
其中：
'advanced'記錄的資訊要比'all'多，主要就是多一個Outline Data。
Outline Data主要是執行SQL時用於固定執行計劃的內部HINT組合，可以將這部分內容摘出來加到目標SQL中以固定其執行計劃。
(4) select * from table(dbms_xplan.display_cursor('sql_id/hash_value', child_cursor_number, 'advanced'));
(5) select * from table(dbms_xplan.display_awr('sql_id'));
select plan_table_output from table (dbms_xplan.display_awr('4bctzmucz7fxg',null,null,'ADVANCED +PEEKED_BINDS'));
(2)從awr效能檢視裡獲取
SELECT * FROM TABLE(dbms_xplan.display_awr('SQL_ID'));
優點：
(1)知道sql_id就可以得到執行計劃，和explain plan for一樣無須執行；
(2)可以得到真實執行計劃；
缺點:
(1)沒有輸出執行時的相關統計資訊(例如:邏輯讀，遞迴呼叫次數，物理讀等)
(2)無法判斷處理多少上；
(3)無法判斷表訪問多少次；
如何找出SQL_ID:
How to Determine the SQL_ID for a SQL Statement (Doc ID 1627387.1) For Example: SELECT /* TARGET SQL */ * FROM dual; SELECT sql_id, plan_hash_value, substr(sql_text,1,40) sql_text FROM v$sql WHERE sql_text like 'SELECT /* TARGET SQL */%' SQL_ID PLAN_HASH_VALUE SQL_TEXT ------------- --------------- ---------------------------------------- 0xzhrtn5gkpjs 272002086 SELECT /* TARGET SQL */ * FROM dual SELECT sql_id, plan_hash_value, SUBSTR(sql_text,1,40) Text FROM v$sql WHERE sql_text LIKE '%&An_Identifiable_String%';
如果SQL_ID已經不在v$sql中，可以檢視dba_hist_sqlstat和dba_hist_sqltext。
SELECT s.sql_id, s.plan_hash_value, t.sql_text, s.snap_id FROM dba_hist_sqlstat s, dba_hist_sqltext t WHERE s.dbid = t.dbid AND s.sql_id = t.sql_id AND sql_text LIKE 'SELECT /* TARGET SQL */%' ORDER BY s.sql_id;
2 autotrace
SQL> conn scott/tiger SQL> set autotrace on SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled SP2-0611: Error enabling STATISTICS report ---SYS@PROD1>@?/sqlplus/admin/plustrce.sql SQL> @$ORACLE_HOME/sqlplus/admin/plustrce.sql SQL> grant plustrace to scott;
顯示結果集以及執行計劃
SQL> set autotrace on
不顯示結果集，顯示執行計劃
SQL> set autotrace traceonly
優點：
(1)可以輸出執行時的相關統計資訊(產生多少邏輯讀，多少次遞迴呼叫，多少次物理讀)
(2)traceonly可以控制返回結果不輸出；
缺點：
(1)必須等到語句真正執行完畢後，才可以出結果；
(2)無法看到表被訪問多少次；
設定Autotrace的命令
1 此為預設值，即關閉Autotrace SET AUTOTRACE OFF 2 產生結果集和解釋計劃並列出統計 SET AUTOTRACE ON 3 顯示結果集和解釋計劃不顯示統計 SET AUTOTRACE ON EXPLAIN 4 顯示解釋計劃和統計，儘管執行該語句但您將看不到結果集 SET AUTOTRACE TRACEONLY 5 只顯示統計 SET AUTOTRACE TRACEONLY STATISTICS
statistics_level=all
(1)alter session set statistics_level=all; (2)執行SQL; (3)select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); 示例： select /*+ gather_plan_statistics */ * from t1 where exists (select 1 from scott.emp a where t1.empno=a.empno); select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
另注：
1. /*+ gather_plan_statistics */可以省略第一步驟；
2. 關鍵字解讀
(1)starts為該SQL執行的次數； (2)E-Rows為執行計劃預計的行數； (3)A-Rows為實際返回的行數。A-Rows和E-Rows做比較，就可以確定哪一步執行計劃出了問題。 (4)A-Time為每一步實際執行的時間(HH:MM:SS.FF),根據這一行可以知道該SQL耗時在哪個地方； (5)buffers為每一步實際執行的邏輯讀或一致性讀； (6)Reads為物理讀；
優點:
(1)可以清晰的從STARTS得出表被訪問多少次； (2)可以清晰的從E-ROWS和A-ROWS中得到預測的行數和真實的行數，從而可以準確判斷Oracle評估是否準確； (3)BUFFER顯示真實的邏輯讀數量；
缺點:
(1)必須要等到語句真正執行完畢後，才能出結果； (2)無法控制記錄不打屏輸出，沒有類似autotrace的traceonly的功能； (3)看不出遞迴呼叫的次數，看不出物理讀；
4 10046
設定追蹤識別符號 alter session set tracefile_identifier='10046'; 開啟跟蹤 alter session set events '10046 trace name context forever, level 12'; 執行語句 select count(*) from all_objects; 關閉10046追蹤 alter session set events '10046 trace name context off'; 當退出當前會話的時候，Oracle就會將追蹤的結果寫入到trace檔案目錄 使用Oracle提供的tkprof來格式化開啟追蹤檔案，進行分析，如下： tkprof ora_2229_10046.trc 888.trc vim 888.trc
---開啟會話跟蹤：alter system set events ‘10046 trace name context forever, level 12’;
---關閉會話跟蹤：alter system set events ‘10046 trace name context off’;
---收集特定session的10046
SPID 是作業系統的程式識別符號（os pid）
PID 是Oracle的程式識別符號(ora pid)
假設需要被跟蹤的OSPID是9834，以sysdba的身份登入到SQL*Plus並執行下面的命令：
oradebug setospid 9834
oradebug unlimit
oradebug event 10046 trace name context forever,level 12
注： 也可以透過oradebug使用 'setorapid'命令連線到一個session。
下面的例中， 使用PID（Oracle程式識別符號）(而不是SPID), oradebug命令將被改為：
connect / as sysdba
oradebug setorapid 9834
oradebug unlimit
oradebug event 10046 trace name context forever,level 12
跟蹤過程完成以後，關閉oradebug跟蹤：
oradebug event 10046 trace name context off
SELECT d.VALUE || '/' || LOWER(RTRIM(i.INSTANCE, CHR(0))) || '_ora_' ||
p.spid || '.trc' AS "trace_file_name"
FROM (SELECT p.spid
FROM v$mystat m, v$session s, v$process p
WHERE m.statistic# = 1
AND s.SID = m.SID
AND p.addr = s.paddr) p,
(SELECT t.INSTANCE
FROM v$thread t, v$parameter v
WHERE v.NAME = 'thread'
AND (v.VALUE = 0 OR t.thread# = TO_NUMBER(v.VALUE))) i,
(SELECT VALUE FROM v$parameter WHERE NAME = 'user_dump_dest') d;
優點:
(1)可以看出SQL語句對應的等待事件；
(2)如果SQL語句中有函式呼叫，函式中又有SQL，將會被列出；
(3)可以方便的看出處理的行數，產生的物理讀，邏輯讀；
(4)可以方便的看出解析時間和執行計劃；
(5)可以跟蹤整個程式包；
缺點：
(1)步驟繁瑣；
(2)無法判斷表被訪問多少次；
(3)執行計劃中謂詞部分不能清晰展現出來；
awrsqrpt.sql
(1)@?/rdbms/admin/awrsqrpt.sql
(2)begin snap，end snap
(3)輸入sql_id
---執行計劃順序
就是從第ID=0的向下看，一路向下，如果縮排被擋住了，就是最先執行的。。。如果同級，是在上面的先執行
除了標量子查詢啥的，不符合這種規則。。
使用AUTOTRACE或者EXPLAIN PLAN FOR獲取的執行計劃來自於PLAN_TABLE。
PLAN_TABLE是一個會話級的臨時表，裡面的執行計劃並不是SQL真實的執行計劃，它只是最佳化器估算出來的。
真實的執行計劃不應該是估算的，應該是真正執行過的。
SQL執行過的執行計劃存在於共享池中，具體存在於資料字典V$SQL_PLAN中，帶有A-Time的執行計劃來自於V$SQL_PLAN，是真實的執行計劃，
而透過AUTOTRACE、透過EXPLAIN PLAN FOR獲取的執行計劃只是最佳化器估算獲得的執行計劃。
---跟蹤使用者SQL
select sql_text, sql_fulltext, sql_id, first_load_time, LAST_ACTIVE_TIME
from v$sqlarea t
where t.PARSING_SCHEMA_NAME in ('K2_20181205')
order by t.LAST_ACTIVE_TIME desc;
5 10053
SQL> alter session set events '10053 trace name context forever,level 1'; SQL> explain plan for select count(*) from obj$;
10046
a
lter session set tracefile_identifier='10046'; ALTER SESSION SET EVENTS '10046 trace name context forever, level 12'; ... ALTER SESSION SET EVENTS '10046 trace name context off'; tkprof 原檔案.trc 目標檔案
select p.PID, p.SPID, s.SID from v$process p, v$session s where s.paddr = p.addr and s.sid = &SESSION_ID ###oradebug connect / as sysdba oradebug setorapid 9834 oradebug unlimit oradebug event 10046 trace name context forever,level 12 跟蹤過程完成以後，關閉oradebug跟蹤： oradebug event 10046 trace name context off SQL> oradebug setmypid Statement processed. SQL> oradebug dump systemstate 266 Statement processed. SQL>--等上30秒到1分鐘 SQL> oradebug dump systemstate 266 Statement processed. SQL> oradebug tracefile_name /u01/app/oracle/diag/rdbms/nctst/nctst/trace/nctst_ora_27983.trc alter session set tracefile_identifier='10053'; ALTER SESSION SET EVENTS='10053 trace name context forever, level 1'; ...... ALTER SESSION SET EVENTS '10053 trace name context off'; select value from v$diag_info where name='Default Trace File'; ====trace --SQL 10046 alter session set tracefile_identifier='enmo10046'; alter session set events '10046 trace name context forever, level 12'; run your sql; alter session set events '10046 trace name context off'; --如果會話已經執行了，可以用oradebug conn / as sysdba oradebug setospid 16835 oradebug unlimit oradebug event 10046 trace name context forever,level 12 oradebug event 10046 trace name context off --systemstate dump sqlplus -prelim / as sysdba oradebug setmypid oradebug unlimit; oradebug dump systemstate 266; --wait for 1 min oradebug dump systemstate 266; --wait for 1 min oradebug dump systemstate 266; oradebug tracefile_name; --hanganalyze oradebug setmypid oradebug unlimit; oradebug dump hanganalyze 3 --wait for 1 min oradebug dump hanganalyze 3 --wait for 1 min oradebug dump hanganalyze 3 oradebug tracefile_name;
###檢視SQL歷史執行計劃的變更
---DBA_HIST_SQL_PLAN，DBA_HIST_SQLSTAT，DBA_HIST_SNAPSHOT select distinct SQL_ID, PLAN_HASH_VALUE, to_char(TIMESTAMP, 'yyyymmdd hh24:mi:ss') TIMESTAMP from dba_hist_sql_plan where SQL_ID = '2fjy1vnu83bwj' order by TIMESTAMP desc; --- select plan_hash_value, id, operation, options, object_name, depth, cost, to_char(TIMESTAMP, 'yyyymmdd hh24:mi:ss') from DBA_HIST_SQL_PLAN where sql_id = '2fjy1vnu83bwj' and plan_hash_value in (4030773303,801583493) order by ID, TIMESTAMP; ---執行計劃變更 select distinct SQL_ID, PLAN_HASH_VALUE, to_char(TIMESTAMP, 'yyyymmdd hh24:mi:ss') TIMESTAMP from dba_hist_sql_plan where SQL_ID = 'afqfknn3nwwpw' order by TIMESTAMP; select plan_hash_value, id, operation, options, object_name, depth, cost, to_char(TIMESTAMP, 'yyyymmdd hh24:mi:ss') from DBA_HIST_SQL_PLAN where sql_id = 'afqfknn3nwwpw' and plan_hash_value in (1542630049, 2754593971, 2620382595) order by ID, TIMESTAMP;
檢視執行計劃歷史資訊：
oracle 10G 以後可以透過下面的三個檢視查詢到sql執行計劃的歷史資訊：
DBA_HIST_SQL_PLAN
DBA_HIST_SQLSTAT
DBA_HIST_SNAPSHOT
使用如下sql 可以發現某個sql的執行計劃什麼時候發生了變化！
select distinct SQL_ID,PLAN_HASH_VALUE,to_char(TIMESTAMP,'yyyymmdd hh24:mi:ss') TIMESTAMP
from dba_hist_sql_plan
where SQL_ID='68wnxdjxwwn2h' order by TIMESTAMP;
檢視出來執行計劃的變化之後 可以使用如下sql檢視發生了那些變化！
col options for a15 col operation for a20 col object_name for a20 select plan_hash_value,id,operation,options,object_name,depth,cost,to_char(TIMESTAMP,'yyyymmdd hh24:mi:ss') from DBA_HIST_SQL_PLAN where sql_id ='68wnxdjxwwn2h' and plan_hash_value in (1542630049,2754593971,2620382595) order by ID,TIMESTAMP;
###固定執行計劃###
http://blog.itpub.net/29785807/viewspace-2643074/
1.hint
2.儲存大綱stored outline
3.sql_profile
4.spm
How to Use SQL Plan Management (SPM) - Plan Stability Worked Example (Doc ID 456518.1)
SQL計劃管理（SPM）是一種預防性機制，它記錄和評估SQL語句的執行計劃。
SPM構建SQL計劃基線，由一組已知有效的現有計劃組成。
然後使用SQL計劃基線來保持相應SQL語句的效能，而不管系統中發生什麼變化，從而提供“計劃穩定性”。
SQL計劃管理可以提高或保持SQL效能的常見使用場景包括：
資料庫升級 安裝新最佳化器版本的資料庫升級通常會導致一小部分SQL語句的計劃更改，而大多數計劃更改不會導致效能更改或改進。 但是，某些計劃變更可能會導致績效下降。 SQL計劃基線的使用大大減少了資料庫升級導致的潛在效能下降。 系統/資料更改 正在進行的系統和資料更改可能會影響某些SQL語句的計劃，可能會導致效能下降。SQL計劃基線的使用將有助於最小化效能退化並穩定SQL效能。 應用程式升級 部署新的應用程式模組意味著在系統中引入新的SQL語句。應用軟體可以使用在標準測試配置下為新SQL語句開發的適當SQL執行計劃。 如果您的系統配置與測試配置明顯不同，那麼SQL計劃基線可以隨著時間的推移而變化，以產生更好的效能。
如果您在遊標快取中有一個好的計劃，那麼您可以將這些計劃載入到SPM中，以便可以使用此基線來保持效能。以下示例命令說明了這一點：
set serveroutput on
var n number
begin
:n:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'&sql_id', plan_hash_value=>&plan_hash_value, fixed =>'NO', enabled=>'YES');
end;
/
EXEC dbms_output.put_line('Number of plans loaded: ' || :n);
啟用SPM後，對於那些具有SQL計劃管理基線的SQL ID，資料庫將不會收集新的訪問計劃（即使在禁用SPM後-假設基線處於活動狀態）
本文件旨在展示一個SQL計劃管理示例。
下面是一個指令碼，您可以執行該指令碼來演示SPM如何操作，然後將基本原則應用到實際程式碼中。
指令碼包含註釋，用於解釋執行時發生的情況。
該指令碼最初是在SH模式的11.1.0.6.0上建立和測試的。其他版本的輸出可能略有不同。
SCRIPT spm.sql
###繫結變數
SELECT snap_id, NAME, position , value_string, last_captured,WAS_CAPTURED FROM dba_hist_sqlbind;
還有一個dba_hist_sqlbind也是記錄了awr中繫結變數的內容
不過這兩個檢視中記錄的繫結變數只對where條件後面的繫結進行捕獲，這點需要使用的時候注意。
繫結變數
10g 資料傾斜嚴重時，使用繫結變數，導致所有同類SQL都走同一個執行計劃，部分SQL效率低；
11g 遊標自適應，可以自動從多個執行計劃中找到最優的執行計劃，避免這個問題；
###二:統計資訊篇###
表級別統計資訊
EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => 'cjc',tabname => 't1',estimate_percent=>100,method_opt=> 'FOR ALL INDEXED COLUMNS',CASCADE=> TRUE,no_invalidate=> FALSE,degree=>);
EXEC DBMS_STATS.GATHER_TABLE_STATS('k2_20181211','t_BD_AccountView',estimate_percent=>100,method_opt=> 'FOR ALL INDEXED COLUMNS',CASCADE=> TRUE);
EXEC DBMS_STATS.GATHER_TABLE_STATS('OWNER','TABLE',estimate_percent=>30,CASCADE=> TRUE);
dbms_stats.gather_table_stats(User,TableName,degree => 4,cascade => true);
EXEC DBMS_STATS.GATHER_TABLE_STATS('OWNER','TABLE',estimate_percent=>30,CASCADE=> TRUE);
使用者級別統計資訊
exec dbms_stats.gather_schema_stats('HR');
資料庫級統計資訊
exec DBMS_STATS.GATHER_DATABASE_STATS (estimate_percent=>10,Degree=>8, Cascade=>TRUE, Granularity=>'ALL');
收集使用者下指定表
How To Run DBMS_STATS.GATHER_TABLE_STATS Using Dynamic Query (Doc ID 1072911.1)
create or replace procedure getstattest IS
str1 varchar2(1000);
cnt NUMBER;
CURSOR c_C1 IS select TABLE_NAME, OWNER from dba_tables where OWNER IN ('SCOTT') and TABLE_NAME IN ('EMP', 'DEPT','BONUS','SALGRADE');
begin
FOR tab_rec IN c_C1
LOOP
cnt:=0;
dbms_output.put_line('Checking OWNER: '||tab_rec.OWNER);
dbms_output.put_line('Checking TAB: '||tab_rec.TABLE_NAME);
execute immediate 'SELECT COUNT (*) FROM '|| tab_rec.TABLE_NAME INTO cnt;
if cnt > 2 then
str1:='BEGIN dbms_stats.gather_table_stats(ownname =>'''||tab_rec.OWNER||''',tabname =>'''||tab_rec.TABLE_NAME||''',cascade => true,degree=>
0); END;';
dbms_output.put_line(str1);
execute immediate str1;
end if;
cnt:=0;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line('An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM||' '||str1);
END;
/
SQL> set serverout on;
SQL> exec getstattest
自動統計資訊收集
10g
gather_stats_job Scheduler排程
select program_name, schedule_name, schedule_type, enabled, state from dba_scheduler_jobs where owner = 'SYS' and job_name = 'GATHER_STATS_JOB'; select program_action, number_of_arguments, enabled from dba_scheduler_programs where owner = 'SYS' and program_name = 'GATHER_STATS_PROG'; select w.window_name, w.repeat_interval, w.duration, w.enabled from dba_scheduler_wingroup_members m, dba_scheduler_windows w where m.window_name = w.window_name and m.window_group_name = 'MAINTENANCE_WINDOW_GROUP';
11g 自動維護任務
select task_name, status from dba_autotask_task where client_name = 'auto optimizer stats collection'; select program_action, number_of_arguments, enable from dba_scheduler_programs where owner = 'SYS' and program_name = 'GATHER_STATS_PROG'; select w.window_name, w.repeat_interval, w.duration, w.enabled from dba_autotask_window_clients c, dba_scheduler_windows w where c.window_name = w.window_name and c.optimizer_stats = 'ENABLED';
啟用
BEGIN DBMS_AUTO_TASK_ADMIN.ENABLE(client_name => 'auto optimizer stats collection', operation => NULL, window_name => NULL); END; /
禁用
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;
/
---收集某一列的直方圖
analyze table gl_detail compute statistics for columns prepareddatev size 254;
EXEC DBMS_STATS.GATHER_TABLE_STATS('OWNER','TABLE',estimate_percent=>30,method_opt => 'FOR ALL INDEXED COLUMNS',CASCADE=> TRUE);
exec dbms_stats.gather_table_stats('chen','test1',method_opt=>'for columns (ACCYEAR,ACCMONTH) size AUTO');
或
begin
dbms_stats.gather_table_stats('chen',
'test1',
method_opt => 'for columns status size AUTO');
end;
例如：
select count(distinct id) from t1; ---num_distinct=5001>num_buckets(default=75)
select count(distinct id) from t2; ---num_distinct=3<num_buckets(default=75) ---frequence histograms
---檢視列的直方圖資訊
select table_name,column_name,density,num_distinct,num_buckets,histogram from user_tab_col_statistics;
頻率直方圖(frequence histograms) 頻率直方圖列中的不同值被劃到相同數量的桶中.每一個桶中儲存的都是相同的值,也就是說頻率直方圖的桶數等於列的不同值的個數.buckets=ndv 高度平衡直方圖(height-balanced histograms) 在頻率直方圖中oracle給每一個不同值分配一個桶,然而桶的最大個數是254,因此如果表中的列有大量的不同值(超過254),將會建立一個高度平衡的直方圖. 在高度平衡直方圖中,因為我們的不同值超過了桶的個數,因此oracle首先分對列資料進行排序然後將資料集按桶數進行分類且除了最後一桶可能包含的資料比其它的桶少以外, 所有其它的桶包含相同數量的值(這就是為什麼叫高度平等直方圖的原因).
---列相關性
列的相關性：就是where子句中同時存在2個列以上條件時，CBO不做列與列之間相關性分析，這會導致CBO計算執行計劃出現一種偏差
select count(*) from leo6 where object_type='TABLE' and owner='LEO1';
execute dbms_stats.gather_table_stats('LEO1','LEO6',method_opt=>'for columns (object_type,owner) size skewonly');
---收集統計資訊(完全計演算法)：
analyze table abc compute statistics;
---刪除統計資訊(級聯刪除列直方圖)
begin dbms_stats.delete_table_stats(ownname => 'CHEN', tabname => 'TEST1'); end; 或 ANALYZE TABLE TEST1 DELETE STATISTICS;
###收集統計資訊[抽樣估演算法(抽樣20%)]：
analyze table abc estimate statistics sample 20 percent;
###資料庫級統計資訊
rq="start time":`date +"%Y-%m-%d %H:%M:%S"` echo $rq>>/oracle/scripts/stats.log sqlplus /nolog <<EOF connect / as sysdba; exec DBMS_STATS.GATHER_DATABASE_STATS (estimate_percent=>10,Degree=>8, Cascade=>TRUE, Granularity=>'ALL'); EOF rq="stop time":`date +"%Y-%m-%d %H:%M:%S"`echo $rq>>/oracle/scripts/stats.log
收集一下字典表的統計資訊，遞迴SQL執行計劃有問題
exec dbms_stats.gather_fixed_objects_stats; exec dbms_stats.gather_dictionary_stats; execute dbms_stats.gather_schema_stats(‘SYS’);
統計資訊的匯入和匯出
(1) 首先建立一個分析表，該表是用來儲存之前的分析值：
begin dbms_stats.create_stat_table(ownname => 'CHEN', stattab => 'STAT_TABLE',cascade => true); end;
(2) 匯出表分析資訊到stat_table中
begin dbms_stats.export_table_stats(ownname => 'CHEN', tabname => 'T1', stattab => 'STAT_TABLE'); end;
(3)開始更新T1表統計資訊
begin dbms_stats.gather_table_stats(ownname => 'CHEN', tabname => 'T1'); end; /* begin dbms_stats.gather_table_stats(ownname => 'CHEN', tabname => 'T1', cascade => true); end; */
(4)刪除分析資訊
begin dbms_stats.delete_table_stats(ownname => 'CHEN', tabname => 'T1'); end;
(5)匯入分析資訊
begin dbms_stats.import_table_stats(ownname => 'CHEN', tabname => 'T1', stattab => 'STAT_TABLE'); end;
###自動收集統計資訊
---這一步非常重要，需要顯式地賦予使用者建表許可權
grant create any table to chen ;
---建立收集統計資訊的儲存過程
CREATE OR REPLACE PROCEDURE ANALYZE_TB AS
OWNER_NAME VARCHAR2(100);
V_LOG INTEGER;
V_SQL1 VARCHAR2(800);
V_TABLENAME VARCHAR2(50);
CURSOR CUR_LOG IS
SELECT COUNT(*) FROM USER_TABLES WHERE TABLE_NAME = 'ANALYZE_LOG';
--1
BEGIN
--DBMS_OUTPUT.ENABLE (buffer_size=>100000);
--1.1
BEGIN
OPEN CUR_LOG;
FETCH CUR_LOG
INTO V_LOG;
IF V_LOG = 0 THEN
EXECUTE IMMEDIATE 'CREATE TABLE ANALYZE_LOG (USER_NAME VARCHAR(20),OP_TIME CHAR(19) DEFAULT to_char(sysdate,''yyyy-mm-dd hh24:mi:ss''),ERROR_TEXT VARCHAR(200),TABLE_NAME VARCHAR(40))';
END IF;
END;
SELECT USER INTO OWNER_NAME FROM DUAL;
V_SQL1 := 'INSERT INTO ANALYZE_LOG (USER_NAME,ERROR_TEXT,TABLE_NAME) VALUES (''' ||
OWNER_NAME || ''',''ANALYZE BEGIN'',''ALL'')';
EXECUTE IMMEDIATE V_SQL1;
sys.dbms_stats.gather_schema_stats(ownname => UPPER(OWNER_NAME),
estimate_percent => 100,
method_opt => 'FOR ALL INDEXED COLUMNS',
cascade => TRUE);
V_SQL1 := 'INSERT INTO ANALYZE_LOG (USER_NAME,ERROR_TEXT,TABLE_NAME) VALUES (''' ||
OWNER_NAME || ''',''ANALYZE END'',''ALL'')';
EXECUTE IMMEDIATE V_SQL1;
commit;
--1.2 delete tmptb statitics and lock statistics
BEGIN
for x in (select a.table_name, a.last_analyzed, b.stattype_locked
from user_tables a, user_tab_statistics b
where a.temporary = 'Y'
and a.table_name = b.table_name
and (b.STATTYPE_LOCKED is null or
a.last_analyzed is not null)) LOOP
IF x.last_analyzed IS NOT NULL THEN
--delete stats
dbms_stats.delete_table_stats(ownname => user,
tabname => x.table_name,
force => TRUE);
END IF;
IF x.stattype_locked IS NULL THEN
--lock stats
dbms_stats.lock_table_stats(ownname => user,
tabname => x.table_name);
END IF;
END LOOP;
end;
EXCEPTION
WHEN OTHERS THEN
IF CUR_LOG%ISOPEN THEN
CLOSE CUR_LOG;
END IF;
commit;
end;
- --下面提供的指令碼示範如何建立定時任務，也是要在"sqlplus"中執行。當天的凌晨2點開始更新統計資訊，以後每2天的凌晨2點更新統計資訊。注意建立當前job時，使用NC的使用者連線資料庫執行。具體最佳化時間設定使用者根據實際情況靈活調整。
SQL> VARIABLE JOBNO NUMBER; SQL> VARIABLE INSTNO NUMBER; SQL> SQL> BEGIN 2 SELECT INSTANCE_NUMBER INTO :INSTNO FROM V$INSTANCE; 3 DBMS_JOB.SUBMIT(:JOBNO, 4 'ANALYZE_TB; ', 5 TRUNC(SYSDATE) + 1 + 2 / 24, 6 'TRUNC(SYSDATE)+2+2/24', 7 TRUE, 8 :INSTNO); 9 COMMIT; 10 END; 11 / PL/SQL procedure successfully completed INSTNO --------- 1 JOBNO --------- 5
---執行儲存過程
SQL> exec ANALYZE_TB; PL/SQL procedure successfully completed
---檢視錶資訊
select table_name,tablespace_name,logging,last_analyzed,num_rows,temporary from user_tables;
---檢視索引資訊
select index_name,index_type,table_owner,table_name,table_type,last_analyzed,num_rows from user_indexes;
---檢視統計資訊日誌
select * from analyze_log;
---注意關閉ORACLE自動更新統計資訊的任務
---Oracle10g：
exec dbms_scheduler.disable('SYS.GATHER_STATS_JOB');
exec dbms_scheduler.enable('SYS.GATHER_STATS_JOB');
select job_name,
schedule_name,
enabled,
last_start_date,
last_run_duration,
next_run_date
from dba_scheduler_jobs a
where job_name = 'GATHER_STATS_JOB';
SQL>Exec DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
exec dbms_scheduler.disable('gather_stats_job');
---Oracle11g：
BEGIN
DBMS_AUTO_TASK_ADMIN.disable(client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;
---SQL> select client_name,status from dba_autotask_client;
禁用自動收集統計資訊的任務可以使用DBMS_AUTO_TASK_ADMIN包完成：
SQL> exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);
啟用自動收集統計資訊的任務
SQL> exec DBMS_AUTO_TASK_ADMIN.ENABLE(client_name => 'auto optimizer stats collection',operation => NULL,window_name => NULL);
ANALYZE TABLE wa_cacu_data DELETE STATISTICS;
SQL> execute dbms_stats.lock_table_stats(ownname=>'CHEN',tabname=>'WA_CACU_DATA');
SQL> execute dbms_stats.unlock_table_stats(ownname=>'CHEN',tabname=>'WA_CACU_DATA');
========
移除JOB
========
exec dbms_job.remove(1233)
========
停止JOB
========
select sid from dba_jobs_running;
select a.spid from v$process a ,v$session b where a.addr=b.paddr and b.sid in (select sid from dba_jobs_running);
使用DBMS_JOB包來標識你的JOB為BROKEN。
SQL> EXEC DBMS_JOB.BROKEN(job#,TRUE);
注意：當執行完該命令你選擇的這個JOB還是在執行著的。
Kill 對應的Oracle Session
ALTER SYSTEM KILL SESSION 'sid,serial#';
或者直接KILL對應的作業系統的SESSION，如果使用ALTER SYSTEM KILL SESSION執行很長時間，其實可以使用OS的命令來快速KILL掉SESSION.
For Windows, at the DOS Prompt: orakill sid spid
For UNIX at the command line> kill –9 spid
---定製job
SQL> variable job1 number;
SQL> begin
2 dbms_job.submit(:job1,'p_test;',sysdate,'sysdate+1/1440');
3 end;
4 /
---建立job
SQL> begin
2 dbms_job.run(23);
3 end;
4 /
PL/SQL procedure successfully completed
---刪除JOB
SQL> begin
2 dbms_job.remove(23);
3 end;
4 /
PL/SQL procedure successfully completed
===============
停止JOB
===============
ALTER SYSTEM KILL SESSION 'sid,serial#';
SQL> select name,value from v$parameter where name ='job_queue_processes';
SQL> ALTER SYSTEM SET job_queue_processes = 0;
恢復job_queue_processes的原始值
SQL> ALTER SYSTEM SET job_queue_processes = original_value;
##查詢jobs
set lin 200
col interval for a30
col last_sec for a10
col this_sec for a10
col next_sec for a10
col schema_user for a20
col log_user for a20
col what for a80
select job,schema_user,this_date,this_sec,next_date,next_sec,interval,what from dba_jobs where schema_user not in ('APEX_030200');
--scheduler
set pages 200 lin 200
col owner for a10
col job_name for a30
col START_DATE for a40
select owner,job_name,START_DATE from dba_scheduler_jobs where owner not in ('EXFSYS','SYS','ORACLE_OCM');
###三:AWR報告篇###
SQL> Exec dbms_workload_repository.create_snapshot; SQL> SHOW PARAMETER STATISTICS_LEVEL
如果STATISTICS_LEVEL的值為TYPICAL或者 ALL，表示啟用AWR；如果STATISTICS_LEVEL的值為BASIC，表示禁用AWR。
AWR報告，只是產生不同的AWR報告，需要執行不同的指令碼。
--產生整個資料庫的AWR報告，執行指令碼awrrpt.sql。 @$ORACLE_HOME/rdbms/admin/awrrpt.sql --產生某個例項的AWR報告，執行指令碼awrrpti.sql。 @$ORACLE_HOME/rdbms/admin/awrrpti.sql --產生某條SQL語句的AWR報告，執行指令碼awrsqrpt.sql。 @$ORACLE_HOME/rdbms/admin/awrsqrpt.sql
RAC AWR
在11.2以前，對於RAC資料庫只能為多個例項分別生成AWR報告，而11.2中，Oracle終於可以將所有例項AWR報告彙總到一起。
@?/rdbms/admin/awrgrpt 1 awr @?/rdbms/admin/awrrpt.sql 2 ash @?/rdbms/admin/ashrpt.sql 3 addm @?/rdbms/admin/addmrpt.sql 4 awrsqrpt @?/rdbms/admin/awrsqrpt.sql 5 awrdd @?/rdbms/admin/awrddrpt.sql
###三:TOP SQL篇###
--資料庫繁忙程度----
set line 200 pages 2000 col DB_TIME_US for 9999999999999.9999 select b.instance_number, b.snap_id as begin_snap, lead(b.snap_Id,1,null) over (order by b.snap_Id) as end_snap, to_char(b.end_interval_time,'YYYY-MM-DD hh24:MI:SS') as begin_time , to_char(lead(b.end_interval_time,1,null) over (order by b.snap_id),'YYYY-MM-DD hh24:MI:SS') as end_time, extract(hour from lead(b.end_interval_time,1,null) over (order by b.snap_id) - b.end_interval_time ) * 60 + extract(minute from lead(b.end_interval_time,1,null) over (order by b.snap_id) - b.end_interval_time ) as Elapsed_minutes, a.value as db_time_us , round((lead(a.value,1,null) over (order by b.snap_id) - a.value)/1000000/60,2) as DbTime_minutes from sys.WRH$_SYS_TIME_MODEL A, dba_hist_snapshot B, sys.wrh$_stat_name C where a.dbid = b.dbid and a.instance_number=b.instance_number and a.snap_id = b.snap_id and a.stat_id = c.stat_id and b.instance_number=1 and c.stat_name = 'DB time' and b.end_interval_time>systimestamp -7 ORDER BY 8 DESC;
--檢視latch那個資料字典爭用嚴重
set lin 200 pages 200
col cache# head "Cache|no" form 999
col parameter head "Parameter" form a25
col type head "Type" form a12
col subordinate# head "Sub|ordi|nate" form 9999
col rcgets head "Cache|Gets" form 999999999999
col rcmisses head "Cache|Misses" form 999999999999
col rcmodifications head "Cache|Modifica|tions" form 999999999999
col rcflushes head "Cache|Flushes" form 999999999999
col kqrstcln head "Child#" form 999
col lagets head "Latch|Gets" form 999999999999
col lamisses head "Latch|Misses" form 999999999999
col laimge head "Latch|Immediate|gets" form 999999999999
select dc.kqrstcid CACHE#, dc.kqrsttxt PARAMETER, decode(dc.kqrsttyp, 1,'PARENT','SUBORDINATE') type,
decode(dc.kqrsttyp, 2, kqrstsno, null) subordinate#,
dc.kqrstgrq rcgets, dc.kqrstgmi rcmisses, dc.kqrstmrq rcmodifications, dc.kqrstmfl rcflushes, dc.kqrstcln,
la.gets lagets, la.misses lamisses, la.immediate_gets laimge from x$kqrst dc, v$latch_children la
where dc.inst_id = userenv('instance')
and la.child# = dc.kqrstcln
and la.name = 'row cache objects'
order by rcgets desc
/
--檢視歷史資料增長量，透過資料檔案判斷
SELECT TO_CHAR(creation_time, 'RRRR-MM') month , SUM(bytes)/1024/1024 growth_MB FROM sys.v_$datafile GROUP BY TO_CHAR(creation_time, 'RRRR-MM') ORDER BY TO_CHAR(creation_time, 'RRRR-MM'); SELECT TO_CHAR(creation_time, 'RRRR-MM-DD') month , SUM(bytes)/1024/1024 growth_MB FROM sys.v_$datafile GROUP BY TO_CHAR(creation_time, 'RRRR-MM-DD') ORDER BY TO_CHAR(creation_time, 'RRRR-MM-DD');
###索引監控
set pagesize 0; set feedback off; spool monitor_index.sql; select 'alter index CJC.' || index_name || ' monitoring usage;' from dba_indexes where owner = 'CJC'; spool off; rem start monitor_index.sql
SQL> alter index CJC.I_T2_ID monitoring usage;
取消監控
SQL> alter index <索引名> nomonitoring usage; SQL> select * from v$object_usage INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING 1 I_T2_ID T2 NO YES 06/17/2020 10:14:16 06/17/2020 10:16:25
###空間碎片
以下查詢是碎片最嚴重的前100個表的指令碼及結果
col frag format 999999.99
col owner format a30;
col table_name format a30;
select *
from (select a.owner "使用者名稱",
a.table_name "表名",
a.num_rows "記錄數",
a.avg_row_len * a.num_rows "需要空間",
sum(b.bytes) "實際空間",
(a.avg_row_len * a.num_rows) / sum(b.bytes) "碎片率"
from dba_tables a, dba_segments b
where a.table_name = b.segment_name
and a.owner = b.owner
and a.num_rows <> 0
---and a.table_name = 'XXX'
and a.owner not in ('SYS',
'SYSTEM',
'OUTLN',
'DMSYS',
'TSMSYS',
'DBSNMP',
'WMSYS',
'EXFSYS',
'CTXSYS',
'XDB',
'OLAPSYS',
'ORDSYS',
'MDSYS',
'SYSMAN')
group by a.owner, a.table_name, a.avg_row_len, a.num_rows
having a.avg_row_len * a.num_rows / sum(b.bytes) < 0.7
order by sum(b.bytes) desc)
where rownum <= 100;
###監控，統計表DML次數
user_tab_modifications dba_tab_modifications select * from user_tab_modifications order by updates desc; select table_name, last_analyzed, num_rows, modify_rows, modify_rate from (select t.table_name, t.last_analyzed, t.num_rows, m.modify_rows, round(m.modify_rows / t.num_rows * 100, 2) as modify_rate from user_tables t inner join (select table_name, inserts + updates + deletes as modify_rows from user_tab_modifications where inserts + updates + deletes > 0) M on t.table_name = m.table_name and t.num_rows > 0) where modify_rate > 10 order by modify_rate;
###top sql
---檢視邏輯讀前10的SQL：
SELECT * FROM (SELECT sql_fulltext AS sql, SQL_ID, buffer_gets / executions AS "Gets/Exec", buffer_gets, executions FROM V$SQLAREA WHERE buffer_gets > 10000 ORDER BY "Gets/Exec" DESC) WHERE rownum <= 10;
---檢視物理讀前10的SQL：
SELECT * FROM (SELECT sql_fulltext AS sql, SQL_ID, disk_reads / executions AS "Reads/Exec", disk_reads, executions FROM V$SQLAREA WHERE disk_reads > 1000 ORDER BY "Reads/Exec" DESC) WHERE rownum <= 10;
---elapsed_time
select a.*, elapsed_seconds / executions elapsed_per from (select sql_text, round(elapsed_time / 1000000, 2) elapsed_seconds, executions, sql_id, buffer_gets, disk_reads from (select * from v$sql order by elapsed_time desc) where rownum <= 100) a order by elapsed_per desc;
--檢視pga佔用最多的程式
select p.spid,p.pid,s.sid,s.serial#,s.status,p.pga_alloc_mem,s.username,s.osuser,s.program from v$process p, v$session s where s.paddr(+)=p.addr order by p.pga_alloc_mem desc;
--檢視登入時間最長的會話
select * from (select t.sid,t2.spid,t.program,t.status,t.sql_id,t.prev_sql_id,t.event,to_char(t.logon_time,'yyyymmdd hh24:mi:ss') logon, trunc(sysdate-logon_time) from v$session t,v$process t2 where t.paddr=t2.addr and t.type<>'BACKGROUND' order by logon_time) where rownum<=20;
--檢視邏輯讀最多的SQL
select * from (select sql_id,sql_text,s.executions,s.last_load_time,s.first_load_time,s.disk_reads,s.buffer_gets from v$sql s where
buffer_gets >300 order by buffer_gets desc) where rownuM<20;
--物理讀最多的SQL
select * from (select sql_id,sql_text,s.executions,s.last_load_time,s.first_load_time,s.disk_reads,s.buffer_gets,PARSE_CALLS from v$sql s where
disk_reads >300 order by disk_reads desc) where rownuM<20;
--執行最多的SQL
select * from (select sql_id,sql_text,s.executions,s.last_load_time,s.first_load_time,s.disk_reads,s.buffer_gets,PARSE_CALLS from v$sql s X
order by executions desc) where rownuM<20;
--檢視解析次數最多的SQL
select * from (select sql_id,sql_text,s.executions,s.last_load_time,s.first_load_time,s.disk_reads,s.buffer_gets,PARSE_CALLS from v$sql s
order by PARSE_CALLS desc) where rownuM<20;
--檢視disk sort 嚴重的SQL
SELECT SESS.USERNAME,SQL.SQL_TEXT,sql.address,sort1.blocks from v$session sess,
v$sqlarea sql,v$sort_usage sort1 where sess.serial#=sort1.session_num and sort1.sqladdr=sql.address
and sort1.sqlhash=sql.hash_value and sort1.blocks>200 order by sort1.blocks desc;
--檢視等待產生次數及相關會話,過分提交的SQL
select t1.sid,t1.value,t2.name from v$sesstat t1,v$statname t2 where
t2.name like '%user commits%' and t1.statistic#=t2.statistic# and value >=10000;
select t.sid,t.program,t.machine,to_char(t.logon_time,'yyyymmdd hh24:mi:ss') logon,t.wait_class,t.wait_time,
t.seconds_in_wait,t.event,t.sql_id,t.prev_sql_id from v$session t where sid in (xxx,xx);
--檢視共享記憶體使用大小
select count(*),round(sum(sharable_mem)/1024/1024,2) MB from v$db_object_cache a;
--可以獲取系統中應該使用繫結變數的SQL
--檢查是否有顯著未釋放的高水位表,一個塊放5條記錄，降低高水位透過：shrink table 、move table、rename table
select table_name,blocks,num_rows from dba_tables where blocks/num_rows>=0.2 and
num_rows is not null and num_rows<>0 and blocks>=1000;
/*+leading(t1) use_nl(t2)*/
sql語句中from後第一個表為驅動表，執行計劃中排序下面首個表為驅動表
強制先訪問t1表，即作為驅動表
use_nl強制使用巢狀迴圈，小表做驅動表，不需要排序
use_hash強制使用hash迴圈，小表做驅動表，需要排序
use_merge強制使用排序合併，需要排序
--佔用dbtime最高的SQL
SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, st.value/100 as "DB Time
(sec)"
, stcpu.value/100 as "CPU Time (sec)", round(stcpu.value / st.value * 100,2) as "% CPU"
FROM v$sesstat st, v$statname sn, v$session s, v$sesstat stcpu, v$statname sncpu, v$process p
WHERE sn.name = 'DB time' -- CPU
AND st.statistic# = sn.statistic#
AND st.sid = s.sid
AND sncpu.name = 'CPU used by this session' -- CPU
AND stcpu.statistic# = sncpu.statistic#
AND stcpu.sid = st.sid
AND s.paddr = p.addr
AND s.last_call_et < 1800 -- active within last 1/2 hour
AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours
AND st.value > 0;
--等待最長的SQL
SELECT s.sid, s.serial#, p.spid as "OS PID", s.username, s.module, se.time_waited
FROM v$session_event se, v$session s, v$process p
WHERE se.event = '&event_name'
AND s.last_call_et < 1800 -- active within last 1/2 hour
AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours
AND se.sid = s.sid
AND s.paddr = p.addr
ORDER BY se.time_waited;
--佔用CPU
SELECT s.sid, s.serial#, p.spid as "OS PID",s.username, s.module, st.value/100 as "CPU sec"
FROM v$sesstat st, v$statname sn, v$session s, v$process p
WHERE sn.name = 'CPU used by this session' -- CPU
AND st.statistic# = sn.statistic#
AND st.sid = s.sid
AND s.paddr = p.addr
AND s.last_call_et < 1800 -- active within last 1/2 hour
AND s.logon_time > (SYSDATE - 240/1440) -- sessions logged on within 4 hours
ORDER BY st.value;
--檢視會話佔用記憶體
set lin 200 pages 199
col name for a20
col username for a20
SELECT server, s.username, osuser, NAME, VALUE / 1024 / 1024 MB, s.SID, s.serial#, spid
FROM v$session s, v$sesstat st, v$statname sn, v$process p
WHERE st.SID = s.SID
AND st.statistic# = sn.statistic#
AND sn.NAME LIKE 'session pga memory'
AND p.addr = s.paddr
ORDER BY VALUE DESC;
--監控回滾資訊
set linesize 100
alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
select usn, state, undoblockstotal "Total", undoblocksdone "Done", undoblockstotal-undoblocksdone "ToDo",
decode(cputime,0,'unknown',sysdate+(((undoblockstotal-undoblocksdone) / (undoblocksdone / cputime)) / 86400)) "Estimated time to complete"
from v$fast_start_transactions;
###資料庫效能
select s.snap_date,
decode(s.redosize, null, '--shutdown or end--', s.currtime) "TIME",
to_char(round(s.seconds / 60, 2)) "elapse(min)",
round(t.db_time / 1000000 / 60, 2) "DB time(min)",
s.redosize redo,
round(s.redosize / s.seconds, 2) "redo/s",
s.logicalreads logical,
round(s.logicalreads / s.seconds, 2) "logical/s",
physicalreads physical,
round(s.physicalreads / s.seconds, 2) "phy/s",
s.executes execs,
round(s.executes / s.seconds, 2) "execs/s",
s.parse,
round(s.parse / s.seconds, 2) "parse/s",
s.hardparse,
round(s.hardparse / s.seconds, 2) "hardparse/s",
s.transactions trans,
round(s.transactions / s.seconds, 2) "trans/s"
from (select curr_redo - last_redo redosize,
curr_logicalreads - last_logicalreads logicalreads,
curr_physicalreads - last_physicalreads physicalreads,
curr_executes - last_executes executes,
curr_parse - last_parse parse,
curr_hardparse - last_hardparse hardparse,
curr_transactions - last_transactions transactions,
round(((currtime + 0) - (lasttime + 0)) * 3600 * 24, 0) seconds,
to_char(currtime, 'yy/mm/dd') snap_date,
to_char(currtime, 'hh24:mi') currtime,
currsnap_id endsnap_id,
to_char(startup_time, 'yyyy-mm-dd hh24:mi:ss') startup_time
from (select a.redo last_redo,
a.logicalreads last_logicalreads,
a.physicalreads last_physicalreads,
a.executes last_executes,
a.parse last_parse,
a.hardparse last_hardparse,
a.transactions last_transactions,
lead(a.redo, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_redo,
lead(a.logicalreads, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_logicalreads,
lead(a.physicalreads, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_physicalreads,
lead(a.executes, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_executes,
lead(a.parse, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_parse,
lead(a.hardparse, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_hardparse,
lead(a.transactions, 1, null) over(partition by b.startup_time order by b.end_interval_time) curr_transactions,
b.end_interval_time lasttime,
lead(b.end_interval_time, 1, null) over(partition by b.startup_time order by b.end_interval_time) currtime,
lead(b.snap_id, 1, null) over(partition by b.startup_time order by b.end_interval_time) currsnap_id,
b.startup_time
from (select snap_id,
dbid,
instance_number,
sum(decode(stat_name, 'redo size', value, 0)) redo,
sum(decode(stat_name,
'session logical reads',
value,
0)) logicalreads,
sum(decode(stat_name,
'physical reads',
value,
0)) physicalreads,
sum(decode(stat_name, 'execute count', value, 0)) executes,
sum(decode(stat_name,
'parse count (total)',
value,
0)) parse,
sum(decode(stat_name,
'parse count (hard)',
value,
0)) hardparse,
sum(decode(stat_name,
'user rollbacks',
value,
'user commits',
value,
0)) transactions
from dba_hist_sysstat
where stat_name in
('redo size',
'session logical reads',
'physical reads',
'execute count',
'user rollbacks',
'user commits',
'parse count (hard)',
'parse count (total)')
group by snap_id, dbid, instance_number) a,
dba_hist_snapshot b
where a.snap_id = b.snap_id
and a.dbid = b.dbid
and a.instance_number = b.instance_number
order by end_interval_time)) s,
(select lead(a.value, 1, null) over(partition by b.startup_time order by b.end_interval_time) - a.value db_time,
lead(b.snap_id, 1, null) over(partition by b.startup_time order by b.end_interval_time) endsnap_id
from dba_hist_sys_time_model a, dba_hist_snapshot b
where a.snap_id = b.snap_id
and a.dbid = b.dbid
and a.instance_number = b.instance_number
and a.stat_name = 'DB time') t
where s.endsnap_id = t.endsnap_id
order by s.snap_date desc, time desc;
###四:格式篇###
spool常用的設定 set colsep' '; //域輸出分隔符 set echo off; //顯示start啟動的指令碼中的每個sql命令，預設為on set feedback off; //回顯本次sql命令處理的記錄條數，預設為on set heading off; //輸出域標題，預設為on set pagesize 0; //輸出每頁行數，預設為24,為了避免分頁，可設定為0。 set termout off; //顯示指令碼中的命令的執行結果，預設為on set trimout on; //去除標準輸出每行的拖尾空格，預設為off set trimspool on; //去除重定向（spool）輸出每行的拖尾空格，預設為off SQL> set timing on; //設定顯示“已用時間：XXXX” SQL> set autotrace on-; //設定允許對執行的sql進行分析 SQL> set trimout on; //去除標準輸出每行的拖尾空格，預設為off SQL> set trimspool on; //去除重定向（spool）輸出每行的拖尾空格，預設為off SQL> set echo on //設定執行命令是是否顯示語句 SQL> set echo off; //顯示start啟動的指令碼中的每個sql命令，預設為on SQL> set feedback on; //設定顯示“已選擇XX行” SQL> set feedback off; //回顯本次sql命令處理的記錄條數，預設為on SQL> set colsep''; //輸出分隔符 SQL> set heading off; //輸出域標題，預設為on SQL> set pagesize 0; //輸出每頁行數，預設為24,為了避免分頁，可設定為0。 SQL> set linesize 80; //輸出一行字元個數，預設為80 SQL> set numwidth 12; //輸出number型別域長度，預設為10 SQL> set termout off; //顯示指令碼中的命令的執行結果，預設為on SQL> set serveroutput on; //設定允許顯示輸出類似dbms_output SQL> set verify off //可以關閉和開啟提示確認資訊old 1和new 1的顯示. alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
###bashrc [oracle@uf2010813 ~]$ cat .bashrc alias sql='sqlplus / as sysdba' [oracle@uf2010813 ~]$ source .bashrc
###glogin.sql [oracle@uf2010813 ~]$ cd /opt/oracle/app/oracle/product/11.2.0/dbhome_1/sqlplus/admin/ [oracle@uf2010813 admin]$ cat glogin.sql set sqlprompt "_user'@'_connect_identifier> " [oracle@uf2010813 admin]$ sqlplus / as sysdba SYS@orcl>
###number顯示科學計數的解決方法
SYS@orcl> select current_scn from v$database; CURRENT_SCN ----------- 1.5249E+13 SYS@orcl> show numwidth numwidth 10 SYS@orcl> set numwidth 20 SYS@orcl> select current_scn from v$database; CURRENT_SCN -------------------- 15248666996607
###五:hint篇###
============
Oracle_HINT
============
select /*+ full(t1) */ * from t1;--全表掃描
select /*+ index(t1 idx_name) */ * from t1 where object_id>2;使用指定索引
select /*+ no_index(t1 idx_name) */ * from t1 where object_id>2; --不使用指定索引
select /*+ index_desc(t1 idx_name) */ * from t1 where object_id=2; --按索引降序順序訪問資料
select /*+ index_combine(t1 idx_name) */ * from t1;--選擇點陣圖索引
select /*+ index_ffs(t1 idx_name) */ from t1 where object_id <100; --索引快速全表掃描（把索引當作一個表看待）
select /*+ index_join(t1 idx_name1 idx_name2) */ * from t1 where object_id=5 and status='VALID'; --同時使用條件列上的相關索引
select /*+ index_ss(t1 index_name) */ * from t1 where object_id=99; --跳躍式掃描
select /*+ leading(t1,t) */ t.* from t,t1 where t1.object_id=t.object_id; --指定t1為驅動作，最佳化器先訪問此表
select /*+ ordered */ t.* from t,t1 where t1.id=t.id; --指定按from 後面表的順序選擇驅表，t作為驅動表
select /*+ use_nl(t1,t) */ t.* from t1,t where t1.object_id=t.object_id;--使用NEST LOOP表連線，適合含有小表資料關聯，如一大一小(有別名，必須用別名)
select /*+ use_hash(t1,t) */ t.* from t1,t where t1.object_id=t.object_id;--使用HASH表連線，適合兩個大表關聯
select /*+ use_merge(t1,t) */t.* from t1,t where t1.object_id=t.object_id;--使用合併排序表連線
select /*+ no_use_nl(t1,t) */ t.* from t1,t where t1.object_id=t.object_id; --不使用NEST LOOP表連線
select /*+ no_use_hash(t1,t) */ t.* from t1,t where t1.object_id=t.object_id;--不使用HASH表連線
select /*+ no_use_merge(t1,t) */t.* from t1,t where t1.object_id=t.object_id; --不使用合併排序表連線
/*+OPTIMIZER_FEATURES_ENABLE('10.2.0.4')*/
SELECT /*+ PUSH_PRED (v1) */ t1.c1,v1.c1
/*+no_push_pred()*/
/*+ no_unnest */
/*+ unnest */
8.並行相關hint
select /*+ parallel(t 4) */ count(*) from t1; --開啟表的4個並行度
select /*+ no_parallel(t) */ count(*) from t1; --不使用並行
表預設的degree(user_tables)如果不為0，預設開啟並行針對select;
9.其它hint
select /*+ dynamic_sampling(t 4) */ * from t where id>134;--4為取樣級別
select /*+ full(t1) cache(t1) */ object_id from t1;--將表t1放在LRU端最活躍處，相當於表屬性的cache(keep);
--以上大部份資料來源於譚懷遠《讓ORACLE跑得更快》學習
/*+optimizer_features_enable('11.2.0.1')*/
alter system set "_optimizer_mjc_enabled" = false;
---基數反饋
SQL> alter system set “_optimizer_use_feedback”=false;
/*+ opt_param('_optimizer_use_feedback' 'false') cardinality(test,1) */
/*+ opt_param('_optimizer_use_feedback' 'false') */
還可以使用cardinality HINT來強制使用Cardinality Feedback 。
select /*+ cardinality(test, 1) */ count(*) from test;
ORA-08104: this index object 114615 is being online built or rebuilt
###六:會話篇###
---檢視正在執行的SQL
select * from v$sql where address in (select sql_address from v$session);
---透過sql_id檢視sql的歷史執行計劃
select * from table(dbms_xplan.display_cursor('sql_sid'));
---檢視正在執行SQL和執行時間
select v.last_call_et,
v.username,
v.machine,
v.program,
v.module,
v.sid,
sql.sql_text,
sql.sql_fulltext,
sql.sql_id,
sql.disk_reads,
v.event
from v$session v, v$sql sql
where v.sql_address = sql.address
and v.last_call_et > 0
and v.status = 'ACTIVE'
and v.username is not null;
---SQL> Exec dbms_workload_repository.create_snapshot;
---SQLServer
select text, cpu,*
from sys.sysprocesses a
cross apply sys.dm_exec_sql_text(a.sql_handle) st
where status <> 'sleeping'
order by a.cpu desc;
---透過sql_id檢視sql的歷史執行計劃
select * from table(dbms_xplan.display_cursor('sql_sid'));
select sid,username,machine,program,module from v$session where module='ORACLE.EXE'
---檢視繫結變數值
select * from V$SQL_BIND_CAPTURE where sql_id='';
select name, position, last_captured, value_string
from dba_hist_sqlbind
where sql_id = 'd2rumw0mt86yh'
order by last_captured desc, name
###檢視阻塞資訊
方法一： ---透過dba_blockers檢視產生阻塞的會話 select * from dba_blockers; ---391 ---dba_waiters檢視產生阻塞和被阻塞的會話 select waiting_session,holding_session from dba_waiters; 從dba_blockers檢視中,可以看到,SID=391的session阻塞了別的session,而從dba_waiters可以看到,HOLDING_SESSION為391,等待的WAITING_SESSION為10。 方法二： ---透過v$session中的blocking_session欄位查詢 select sid,blocking_session from v$session where blocking_session is not null; 其中blocking_session=391是產生阻塞的會話，sid=10是被阻塞的會話 ---如果可以確定產生阻塞的會話沒用，可以手動殺掉 select sid,serial# from v$session where sid=391; alter system kill session '391,34'; 叢集 alter system kill session '391,34,@1'; 如果：v$session.status = 'KILLED' 查詢spid: SELECT s.sid, s.serial#, p.spid FROM v$session s,v$process p WHERE s.paddr = p.addr / ps -ef|grep <spid> would give something like: (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) kill -9 <spid> orakill orcl 12345 Ps：這裡要注意的是kill OS程式是在服務端操作，而不是你程式所在客戶機。
###死鎖
---1 查詢產生阻塞會話的sid和serial#
select sid, serial#
from v$session
where sid in (select holdsid
from (select a.sid holdsid,
b.sid waitsid,
a.type,
a.id1,
a.id2,
a.ctime
from v$lock a, v$lock b
where a.id1 = b.id1
and a.id2 = b.id2
and a.block = 1and b.block = 0));
---2 kill會話(寫入上面查到的sid和serial#)
alter system kill session 'SID,SERIAL#';
---3
select a.spid, b.sid, b.serial#, b.username
from v$process a, v$session b
where a.addr = b.paddr
and b.status = 'KILLED';
---WINDOWS
orakill sid spid
---Linux
ps -ef|grep pid
kill -9 xxx
###查詢會話資訊
select inst_id,
sid,
serial#,
username,
status,
machine,
program,
last_call_et
from gv$session
where machine in ('xxx')
and status = 'INACTIVE'
and type = 'USER';
###生成手動終止指定會話的SQL，根據實際情況之下kill_session列語句
select inst_id,
sid,
serial#,
username,
status,
machine,
program,
last_call_et,
'alter system kill session ' || '''' || sid || ',' || serial# || ',' || '@' ||
inst_id || '''' || ' immediate;' as kill_session
from gv$session
where machine in ('xxx-db01', 'xxx-db02')
and status = 'INACTIVE'
and type = 'USER';
例如執行：
alter system kill session '1052,19,@1' immediate;
alter system kill session '1726,9,@1' immediate;
......
SELECT s.sid, s.serial#, p.spid
FROM v$session s,v$process p
WHERE s.paddr = p.addr
/
set line 300 set pagesize 100 col RESOURCE_NAME for a35 col INITIAL_ALLOCATION for a20 col LIMIT_VALUE for a20 select * from v$resource_limit;
xxx作業系統下檢視當前來自xxxIP的連線
netstat -ant|grep 1521|grep 10.100.1.80 netstat -ant|grep 1521|grep 10.100.1.81 netstat -ant|grep 1521|grep 10.100.1.82 netstat -ant|grep 1521|grep 10.100.1.83 netstat -ant|grep 1521|grep 10.100.1.84
xx作業系統下檢視歷史來自分行大前置IP的連線時間和連線次數
cd /oracle/grid/diag/tnslsnr/xxx-db02/listener/trace
grep "HOST=.*establish.*\* 0" listener.log | awk -F'*' '{match($3,/[0-9]+\.[0-9]+\.[0-9]+\.[0-9]+/); ip = substr($3,RSTART,RLENGTH);cnt[ip]+=1;last[ip]=$1;}END {for (i in cnt) printf "%-16s %9s %19s\n",i,cnt[i],last[i];}' | sort -k 1|grep 10.100.1.8
例如：
10.100.1.80 2 28-JAN-2021 19:08:45
檢視監聽連線明細
tail -300000 listener.log|grep 10.100.1.8
###session
10G
sessions=1.1*processes+5
11gR2
Default value
Derived: (1.5 * PROCESSES) + 22
set linesize 180
col username for a15
col owner for a15
col OBJECT_NAME for a30
col SPID for a10
--查詢某個會話的鎖
select /*+rule*/SESSION_ID,OBJECT_ID,ORACLE_USERNAME,OS_USER_NAME,PROCESS,LOCKED_MODE from gv$locked_object where session_id=&sid;
--查詢TMTX鎖
select /*+rule*/* from v$lock where ctime >100 and type in ('TX','TM') order by 3,9;
--查詢資料庫中的鎖
select /*+rule*/s.sid,p.spid,l.type,round(max(l.ctime)/60,0) lock_min,s.sql_id,s.USERNAME,b.owner,b.object_type,b.object_name from v$session s, v$process p,v$lock l,v$locked_object o,dba_objects b where o.SESSION_ID=s.sid and s.sid=l.sid and o.OBJECT_ID=b.OBJECT_ID and s.paddr = p.addr and l.ctime >100 and l.type in ('TX','TM','FB') group by s.sid,p.spid,l.type,s.sql_id,s.USERNAME,b.owner,b.object_type,b.object_name order by 9,1,3,4;
--檢視會話突增情況：
set lin 200 pages 200
select to_char(LOGON_TIME,'yyyymmdd hh24:mi') LOGON,inst_id,username,count(*) from gv$session where LOGON_TIME>sysdate-0.1 group by to_char(LOGON_TIME,'yyyymmdd hh24:mi'),inst_id,username order by to_char(LOGON_TIME,'yyyymmdd hh24:mi') desc;
set lin 200 pages 200
select inst_id,username,machine,sql_id,count(*) from gv$session where LOGON_TIME>sysdate-0.1 group by to_char(LOGON_TIME,'yyyymmdd hh24:mi'),inst_id,username order by to_char(LOGON_TIME,'yyyymmdd hh24:mi') desc;
set lin 200 pages 200
select inst_id,username,machine,sql_id,count(*) from gv$session group by inst_id,username,machine,sql_id order by count(*) desc;
select inst_id,username,machine,prev_sql_id,count(*) from gv$session group by inst_id,username,machine,prev_sql_id order by count(*) desc;
--節點會話分佈：
select inst_id,count(*) from gv$session group by inst_id;
--檢視例項，會話分佈
col machine for a20
col program for a40
set lin 200 pages 200
select username,machine,program,count(*) from v$session group by username,machine,program order by 4;
select username,machine,program,count(*) from v$session where program like 'oracle@gdytj-db1 (P0%' group by username,machine,program order by 4;
###七:日誌挖掘LOGMNR篇###
LOGMNR
1.需要新增補充日誌
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
SELECT SUPPLEMENTAL_LOG_DATA_MIN FROM V$DATABASE;
2.明確需要挖掘的歸檔
SELECT NAME FROM V$ARCHIVED_LOG
WHERE FIRST_TIME = (SELECT MAX(FIRST_TIME) FROM V$ARCHIVED_LOG);
+DATA/orcl/archivelog/2018_09_20/thread_1_seq_6.313.987329093
3.加入挖掘列表
EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
LOGFILENAME => '+DATA/orcl/archivelog/2018_09_20/thread_1_seq_7.314.987330717', -
OPTIONS => DBMS_LOGMNR.NEW);
4.開始挖掘
EXECUTE DBMS_LOGMNR.START_LOGMNR( -
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
5.查詢挖掘結果
desc V$LOGMNR_CONTENTS
select count(*) from
SELECT username AS USR, (XIDUSN || '.' || XIDSLT || '.' || XIDSQN) AS XID,
SQL_REDO, SQL_UNDO FROM V$LOGMNR_CONTENTS WHERE username IN ('TEST');
6.關閉挖掘
EXECUTE DBMS_LOGMNR.END_LOGMNR();
7.其他挖掘引數命令
只顯示提交的資料
EXECUTE DBMS_LOGMNR.START_LOGMNR( -
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
DBMS_LOGMNR.COMMITTED_DATA_ONLY);
格式化顯示語句，使語句更可讀
EXECUTE DBMS_LOGMNR.START_LOGMNR(-
OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + -
DBMS_LOGMNR.COMMITTED_DATA_ONLY + -
DBMS_LOGMNR.PRINT_PRETTY_SQL);
8.構建dic字典
輸出到文字
alter system set utl_file_dir='/home/oracle/scripts' scope=spfile;
exec dbms_logmnr_d.build(dictionary_filename=>'orcl.dic',dictionary_location=>'/home/oracle/scripts',options=>dbms_logmnr_d.STORE_IN_FLAT_FILE);
輸出到歸檔
exec DBMS_LOGMNR_D.BUILD(dictionary_filename=>NULL,dictionary_location=>NULL,options=>dbms_logmnr_d.STORE_IN_REDO_LOGS);
查詢包含LOGMNR字典的日誌檔案：
v$archived_log的dictionary_begin和dictionary_end列
###無資料字典logminer
配置異機恢復庫
啟動測試資料庫1用於恢復歸檔，恢復控制檔案，將資料庫啟動到mount
rman
restore controlfile from '<controlfile file dir>';
alter database mount;
根據時間段確定需要挖的歸檔日誌
select RECID,THREAD#,SEQUENCE#,
to_char(first_time,'yyyymmdd hh24:mi:ss'),
to_char(NEXT_TIME,'yyyymmdd hh24:mi:ss'),
to_char(COMPLETION_TIME,'yyyymmdd hh24:mi:ss')
from v$archived_log
where first_time between to_date('20181228','yyyymmdd') and to_date('20181229','yyyymmdd')
and COMPLETION_TIME between to_date('20181228','yyyymmdd') and to_date('20181229','yyyymmdd')
order by 3,4;
根據thread和seq# 來restore歸檔日誌
run{
allocate channel ch01 type 'sbt_tape';
send 'xxxxxx';
set archivelog destination to '/oracle11/lylog/20190115_logminer';
restore archivelog sequence 4252 thread 2;
restore archivelog sequence 4333 thread 1;
}
啟動測試資料庫2
用於挖掘日誌
檢查資料庫補充日誌
select SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI from v$database;
開啟資料庫補充日誌
alter database add supplemental log data;
新增歸檔檔案
exec dbms_logmnr.add_logfile('/oracle11/lylog/20190115_logminer/1_4333_981803066.dbf',dbms_logmnr.new);
exec dbms_logmnr.add_logfile('/oracle11/lylog/20190115_logminer/2_4252_981803066.dbf',dbms_logmnr.addfile);
檢查歸檔日誌是否新增
set line 200
col filename for a80
select log_id,filename,status from v$logmnr_logs;
挖掘日誌
exec DBMS_LOGMNR.START_LOGMNR(DictFileName=>'',Options=>0);
查詢挖掘結果
set line 150 pages 20000
col username for a20
col sql_redo for a80
spool /oracle11/lylog/20190115_logminer/logminer_1.log
select username,to_char(start_timestamp,'yyyymmdd hh24:mi:ss'),SQL_REDO from v$logmnr_contents where upper(sql_redo) like '%TRUNCATE %';
select username,to_char(start_timestamp,'yyyymmdd hh24:mi:ss'),SQL_REDO from v$logmnr_contents where upper(sql_redo) like '%GRANT %';
本次logminer需求是要挖掘到日誌中的grant和truncate語句，所以條件如上述，實際使用需根據實際需要修改。
附註：
根據下方MOS
How to Setup LogMiner (文件 ID 111886.1)
LogMiner Dictionary Build Procedure Fails With ORA-01308 in 12.2 (文件 ID 2277747.1)
12.2資料庫推薦使用directory替換原來的utl_file_dir引數。
create directory logminer as '/oracle/soft/arch';
execute dbms_logmnr_d.build(dictionary_filename =>'dict.ora',dictionary_location =>'LOGMINER');
--11g
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LogFileName=>'/u01/app/oracle/archive/1_5673_863636484.dbf',Options=>dbms_logmnr.new);
SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
SQL> create table test1 as select * from v$logmnr_contents;
SQL> exec dbms_logmnr.end_logmnr;
---檢視歸檔大小及目錄名
select block_size * blocks / 1024 / 1024 "size(M)",
name,
first_time,
next_time,
creator
from v$archived_log a
order by first_time desc;
---檢視歸檔中執行次數多的SQL
select count(*),substr(sql_redo,1,100) from test.test1 group by substr(sql_redo,1,100) order by 1 desc;
--10g
SQL> exec dbms_logmnr.add_logfile('/opt/oracle/oradata/mmstest/redo02.log',dbms_logmnr.new);
SQL> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
SQL> exec dbms_logmnr.add_logfile('/u01/app/oracle/oradata/ogg1/redo02.log',dbms_logmnr.new);
/
要生成資料字典檔案，首先要修改一個utl_file_dir引數，修改為*或者想存放資料字典檔案位置的目錄：
SQL> show parameter utl_file_dir
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir string
SQL> alter system set utl_file_dir='*' scope=spfile;
SQL> shutdown immediate
SQL> startup
---
alter system set utl_file_dir='*' scope=spfile ;
3、生成資料字典檔案
SQL> show parameter utl_file_dir
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir string *
生成資料字典檔案dict20150822.chen到/home/oracle/chen目錄下
SQL> EXECUTE dbms_logmnr_d.build(dictionary_filename => 'dict20150822.chen',dictionary_location => '/home/oracle/chen');
PL/SQL procedure successfully completed.
4、可以先設定使用的表空間
SQL> EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE('test')
PL/SQL procedure successfully completed.
5、填加要分析的日誌檔案
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LogFileName=>'/orabak/testarch/1_89802_640266118.dbf',Options=>dbms_logmnr.new);
PL/SQL procedure successfully completed.
6、可以繼續填加，用dbms_logmnr.removefile可以刪除
SQL> EXECUTE DBMS_LOGMNR.ADD_LOGFILE(LogFileName=>'/orabak/testarch/1_89807_640266118.dbf',Options=>dbms_logmnr.addfile);
PL/SQL procedure successfully completed.
7、開始分析日誌
SQL> execute dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/chen/dict20150822.chen');
PL/SQL procedure successfully completed.
提取特定時間的日誌：
dbms_logmnr.start_logmnr(dictfilename=>'/home/oracle/dict20150822.chen',
starttime=>to_date('2009-06-24 09:30:00','YYYY-MM-DD HH24:MI:SS'),
endtime=>to_date('2009-06-24 12:00:59','YYYY-MM-DD HH24:MI:SS'))
/*
使用線上資料字典（資料庫版本低於10g或者logmner庫和源庫是同一個庫時可以使用線上資料字典）：
SQL> EXEC DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
*/
8、檢視結果
select timestamp,username,session#,sql_redo,operation from v$logmnr_contents;
9、結束分析，釋放PGA記憶體資源
SQL> exec dbms_logmnr.end_logmnr;
注意，v$logmnr_contents內容儲存了日誌的內容，只在當前會話有效，如果想長期儲存分析，可以在當前會話用create table tablename as select * from v$logmnr_contents語句來持久儲存。
/
select name,first_time,next_time from v$archived_log where deleted='NO' and to_char(first_time,'yyyymmdd')= '20170518';
select name,first_time,next_time from v$archived_log where deleted='NO' and to_char(first_time,'yyyymmdd')= '20170518';
as of timestamp (systimestamp - interval '12' day);
(systimestamp - interval '2' minute)
(systimestamp - interval '120' second);
(systimestamp - interval '12' hour);
---離線分析---
begin
sys.dbms_logmnr.add_logfile(LogFileName => '/picclife1/arch/log1_233013_789829078.arc',
Options => sys.dbms_logmnr.new);
sys.dbms_logmnr.add_logfile(LogFileName => '/picclife1/arch/log1_233014_789829078.arc',
Options => sys.dbms_logmnr.addfile);
end;
begin
sys.dbms_logmnr.start_logmnr(options=>SYS.DBMS_LOGMNR.SKIP_CORRUPTION, startscn=>15805310267810);
end;
select scn,xid, operation,sql_redo, data_obj#,SEG_NAME,SEG_OWNER,data_objv#,status, row_id, rs_id,ssn, csf,rollback,
rbasqn,RBABLK,rbabyte,pxid,OPERATION_code, thread#,table_name, seg_type,info from v$logmnr_contents WHERE SEG_NAME='OBJ# 243664'
--------------
create table test0627 as select level as id from dual connect by level<=1000;
select * from test0627;
select * from v$log;
select * from v$logfile;
alter system switch logfile;
alter system checkpoint;
select * from v$log; ---CURRENT group 2
select * from v$logfile; ---D:\APP\ADMINISTRATOR\ORADATA\EASTEST\REDO02.LOG
truncate table test0627; ---2018-06-27 11:39
---新增分析的日誌檔案
begin
DBMS_LOGMNR.ADD_LOGFILE(LogFileName=>'D:\APP\ADMINISTRATOR\ORADATA\EASTEST\REDO02.LOG',Options=>dbms_logmnr.new);
end;
---開始分析日誌
begin
dbms_logmnr.start_logmnr(options => dbms_logmnr.dict_from_online_catalog);
end;
create table test0627_log as select * from v$logmnr_contents;
begin
dbms_logmnr.end_logmnr;
end;
select * from test0627_log where lower(sql_redo) like '%test0627%';
--------------------
###閃回查詢
SQL> select * from t1 as of timestamp (systimestamp - interval '120' second); ---查詢t1表120秒之前的資料
SQL> select * from t1 as of timestamp (systimestamp - interval '2' minute); ---查詢t1表2分鐘之前的資料
SQL> select * from dept as of timestamp (systimestamp - interval '12' hour); ---查詢dept表12小時之前的資料
SQL> select * from dept as of timestamp (systimestamp - interval '12' day); ---查詢dept表12天之前的資料
SQL> select * from cardacct as of timestamp sysdate-2; ---查詢cardacct表2天之前的資料
SQL> select timestamp_to_scn(to_timestamp('2014-08-24 05:15:22','yyyy-mm-dd hh24:mi:ss')) scn from dual; --時間和scn之間的轉換
select * from dba_users where username like 'K2_%' order by created desc; ---16:56
select * from dba_users as of timestamp (systimestamp - interval '120' second) where username like 'K2_%' order by created desc;
###recyclebin
SQL> show parameter recyclebin NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ recyclebin string on ---檢視當前使用者的回收站 select * from recyclebin; ---recyclebin是user_recyclebin的同義詞 select * from user_recyclebin; ---檢視整個資料庫的回收站 select * from dba_recyclebin; ---閃回表test001 flashback table test001 to before drop; ---閃回表test001並重新命名為test003 flashback table test001 to before drop rename to test003; ---清除具體的對像 purge table test002; ---清除指定表空間 purge tablespace chen; ---刪除表空間指定使用者下的所有對像 purge tablespace ORAPEL user orabpel; ---清空整個回收站 purge recyclebin;
###八:備份與恢復###
資料泵目錄的建立與授權 ###expdp/impdp SQL> create directory chen_dir as '/home/oracle/chen'; SQL> grant read,write on directory chen_dir to scott; 1.資料泵按表匯出匯入 expdp chen/chen directory=chen_dir tables=dept dumpfile=deptcc.dmp impdp chen/chen directory=chen_dir tables=dept dumpfile=deptcc.dmp impdp+network link 跳過expdp直接匯入目標庫 impdp username/passwd@dbsname schema=userA remap_schema=userA:userB remap_tablespace=tbsA:tbsB network_link=dblink_to_userA_on_userB 優點：只是不再將資料匯出後匯入，而是直接將資料從源庫匯入到目的庫。 expdp backup/backup directory=zs_dir tables=SM_USER dumpfile=zsbackup.dmp network_link='ncerp' 2.按方案(使用者)匯出 expdp chen/chen directory=chen_dir schemas=chen dumpfile=chen.dmp impdp chen/chen directory=chen_dir dumpfile=full.dmp schemas=chen remap_schema=chen:chen008 su - oralce -c 'nc633/1 directory=expdpdir dumpfile=nc63320180305.dmp log=nc63320180305.log' expdp nc633/1 directory=expdpdir dumpfile=nc63320180305.dmp logfile=nc63320180305.log schemas=nc633 ---排除表 expdp nc633/nc633 directory=backup dumpfile=nc633_expdp_20170905.dmp logfile=nc633_expdp_20170905.log schemas=nc633 exclude=TABLE:\"IN\(\'INVP_PLAN_B\'\)\" ---version impdp EXPDPTEST/EXPDPTEST directory=dump_dir1 dumpfile=c.dmp REMAP_SCHEMA=s:EXPDPTEST REMAP_TABLESPACE=USERS:C impdp 使用者名稱/密碼 directory=dump_dir dumpfile=xx.dmp REMAP_SCHEMA=使用者1:使用者2 exclude=STATISTICS ---按表匯入(不同使用者間匯入，需要在表名前指定使用者名稱) impdp chentest/a directory=dir_expdp dumpfile=K2_EXPDP_20181228223000.DMP logfile=xxx.LOG REMAP_SCHEMA=k2:chentest tables=k2.T_JOB_INST ---按使用者匯入 set NLS_LANG=AMERICAN_AMERICA.AL32UTF8 impdp chentest/a directory=dir_expdp dumpfile=K2_EXPDP_20181228223000.DMP logfile=xxx.LOG remap_schema=k2:k2_201812282230 ---exclude=STATISTICS set NLS_LANG=AMERICAN_AMERICA.AL32UTF8 impdp test_20190115/a directory=expdp_dir dumpfile=SHUIWU_EXPDP_20190114223000.DMP logfile=SHUIWU_IMPDP_20190114223000.LOG remap_schema=shuiwu:test_20190115 remap_tablespace=EAS_D_SHUIWU_STANDARD:test_tbs remap_tablespace=EAS_D_SHUIWU_TEMP2:temp_tbs remap_tablespace=EAS_D_SHUIWU_INDEX:index_tbs expdp k2_20190305/a directory=DIR_EXPDP dumpfile=T_BAS_ATTACHMENT_2019_0320.dmp logfile=T_BAS_ATTACHMENT_2019_0320.log tables=T_BAS_ATTCHMENT query=T_BAS_ATTACHMENT:\"where to_char(FCreateTime,'yyyymm')='201903'\" exclude=STATISTICS expdp k2_20190305/a directory=DIR_EXPDP dumpfile=T_BAS_ATTACHMENT_2019_0320uu.dmp logfile=T_BAS_ATTACHMENT_2019_0320uu.log tables=T_BAS_ATTACHMENT query=T_BAS_ATTACHMENT:\"where to_char(FCreateTime,'yyyymmdd')='20190101'\" exclude=STATISTICS impdp chentest/a dumpfile=T_BAS_ATTACHMENT_2019_0320.dmp directory=DIR_EXPDP logfile=T_BAS_ATTACHMENT_impdp_2019_0320.log remap_schema=K2_20190305:chentest impdp chentest/a dumpfile=T_BAS_ATTACHMENT_2019_0320uu.dmp directory=DIR_EXPDP logfile=T_BAS_ATTACHMENT_impdp_2019_0320uu.log remap_schema=K2_20190305:chentest table_exists_action=append impdp K2_20190928/a directory=dir_expdp dumpfile=T_BAS_ATTACHMENT_2019_20191205220000.DMP logfile=T_BAS_ATTACHMENT_2019_20191205220000XXX.LOG remap_schema=K2:K2_20190928 REMAP_TABLE=KT_BAS_ATTACHMENT:T_BAS_ATTACHMENT table_exists_action=append select * from dba_scheduler_job_log; select * from dba_datapump_jobs; select * from dba_datapump_sessions; select * from dba_tables where table_name like 'SYS_EXPORT_SCHEMA%'; select * from k2.SYS_EXPORT_SCHEMA_01; select object_type_path,object_name from k2.SYS_EXPORT_SCHEMA_01; 停止: dba_datapump_jobs expdp \"sys/oracle as sysdba\" attach=SYS_EXPORT_SCHEMA_02 stop_job--或者stop_job=immediate yes SELECT * from dba_datapump_sessions; impdp test_20190115/a attach=SYS_IMPORT_FULL_01 stop_job--或者stop_immediate impdp system/passwd attatch = sys.SYS_XXX_XX_68 進入 Import > 提示符，可以使用 kill_job 或 stop_job結束或停止Job ， stop的job可以繼續，kill的不行 SELECT owner_name, job_name, operation, job_mode, state, attached_sessions FROM dba_datapump_jobs WHERE job_name NOT LIKE 'BIN$%' ORDER BY 1, 2; With the Partitioning, OLAP and Data Mining options ORA-31634: job already exists ORA-31664: unable to construct unique job name when defaulted 解決方法: 1.在備份時指定一個在dba_datapump_jobs中沒有不存在的一個job_name 2.清理dba_datapump_jobs表 1)查詢可以清理的master table並生成SQL select 'drop table ' || owner_name || '.' || job_name || ';' from dba_datapump_jobs where state = 'NOT RUNNING'; 2)清理後再次dba_datapump_jobs確認清理成功 SELECT owner_name, job_name, operation, job_mode, state, attached_sessions FROM dba_datapump_jobs ORDER BY 1,2; 若不成功,按下面的方法再次清理 sqlplus oak/oak exec dbms_datapump.stop_job(dbms_datapump_attach(‘SYS_EXPORT_TABLE_01’,’OAK’));
EXP
1)表模式 exp scott/tiger file=a.dmp log=a.log tables=emp compress=n exp scott/tiger file=a.dmp log=a.log tables=(emp,dept) compress=n exp scott/tiger file=a.dmp log=a.log tables=emp query="'where deptno=20 and ename!="SCOTT"'" compress=n exp scott/tiger parfile=parameter.txt compress=n vim parameter.txt tables=emp query='where deptno=20 and ename!="SCOTT"' 2)使用者模式 exp scott/tiger file=a.dmp log=a.log owner=scott compress=n exp scott/tiger file a.dmp log=a.log owner=(scott,chen) compress=n 3)整庫模式 exp scott/tiger file=full.dmp log=full.log full=y 只是邏輯上的全庫，只匯出了和使用者資料相關的物件。 自動刪除 forfiles /P D:\BACKUP\BACKUP_DB /m * /s /D -2 /c "cmd /c del @file" expdp username/password@234DB directory=dump_dir dumpfile=mydb_%U.dmp logfile=mydb.log filesize=200m full=y impdp username/password directory=dump_dir dumpfile=mydb_%U.dmp full=y; $ exp user/pwd file=1.dmp,2.dmp,3.dmp,… filesize=1000m log=xxx.log full=y 以多個固定大小檔案方式匯入 $ imp user/pwd file=1.dmp,2.dmp,3.dmp,… filesize=1000m tables=xxx fromuser=dbuser touser=dbuser2 ---只匯入索引 impdp dvpt/dvpt DIRECTORY=imp_dir DUMPFILE=pdnc_bak_navy170915.dmp logfile=import_index.log REMAP_SCHEMA=pdnc:dvpt include='INDEX';
相關引數：
alter session set workarea_size_policy=MANUAL; alter session set db_file_multiblock_read_count=512; alter session set events '10351 trace name context forever, level 128'; alter session set sort_area_size=734003200; alter session set "_sort_multiblock_read_count"=128; alter session enable parallel ddl;
###臨時表清理
透過儲存過程加job，定時刪除指定臨時表。
1．先處理 TMPTI 開頭的表。
DECLARE
CURSOR a IS SELECT table_name FROM User_Tables s WHERE s.temporary='Y' AND s.table_name LIKE 'TMPTI%';
BEGIN
FOR i IN a LOOP
EXECUTE IMMEDIATE 'drop table '||i.table_name;
--dbms_output.put_line(i.table_name);
END LOOP;
END;
2,刪除 TMPTA 開頭的表
DECLARE
CURSOR a IS SELECT table_name FROM User_Tables s WHERE s.temporary='Y' AND s.table_name LIKE 'TMPTA%' and s.table_name <> 'TMPTABSUB';
BEGIN
FOR i IN a LOOP
EXECUTE IMMEDIATE 'drop table '||i.table_name;
END LOOP;
END;
3,刪除 FA_開頭的表,不包含FA_DEPTSCALE
DECLARE
CURSOR a IS SELECT table_name FROM User_Tables s WHERE s.temporary='Y' AND s.table_name LIKE 'FA_ %' and s.table_name <> 'FA_DEPTSCALE';
BEGIN
FOR i IN a LOOP
EXECUTE IMMEDIATE 'drop table '||i.table_name;
END LOOP;
END;
---建立刪除臨時表的儲存過程
CREATE OR REPLACE PROCEDURE DROP_TEMPTAB AS
CURSOR a IS
select table_name
from user_tables c, user_objects d
where c.table_name = d.object_name
and c.temporary = 'Y'
and c.table_name like 'TEMPTABLE0000%'
and d.object_type = 'TABLE'
and d.temporary = 'Y'
and d.CREATED < sysdate - 5;
BEGIN
FOR i IN a LOOP
EXECUTE IMMEDIATE 'drop table ' || i.table_name;
END LOOP;
END;
刪除臨時表的任務是nc.bs.smart.db.TempTableSchdTask
自由報表刪除臨時表任務是註冊在排程任務下面的，需要確認一下排程任務是否執行成功、排程配置是否有問題。
---20160826---
declare
cursor tname is
select table_name from user_tables where table_name like 'TEMP%';
begin
for i in tname
loop
execute immediate 'drop table '||i.table_name;
end loop;
end;
---20160826---
###刪除臨時表
1.進SQL命令列介面，執行
grant create any table to ychr;
grant select any table to ychr;
grant update any table to ychr;
grant delete any table to ychr;
grant drop any table to ychr;
2.進SQL介面，執行
create or replace procedure dropTempTable as
mysql varchar2(4000);
begin
for tablename in (select table_name
from user_tables t1, user_objects t2
where t1.temporary = 'Y'
and t1.table_name like 'TEMPTABLE%'
and t1.table_name = t2.object_name
and t2.object_type = 'TABLE'
and t2.created < sysdate - 3) loop
begin
mysql := 'drop table ' || tablename.table_name;
execute immediate mysql;
exception
when others then
NULL;
end;
end loop;
end;
3.進SQL命令列介面
VARIABLE JOBNO NUMBER;
VARIABLE INSTNO NUMBER;
BEGIN
SELECT INSTANCE_NUMBER INTO :INSTNO FROM V$INSTANCE;
DBMS_JOB.SUBMIT(:JOBNO,'dropTempTable;',TRUNC(SYSDATE)+1+4/24,'TRUNC(SYSDATE)+3+4/24',TRUE,:INSTNO);
COMMIT;
END;
---臨時表
SQL> select * from (select to_char(created,'yyyymmdd'),count(*)
2 from user_tables a,user_objects b
3 where a.table_name=b.object_name
4 and a.temporary='Y'
5 and a.table_name like'TEM_%'
6 group by to_char(created,'yyyymmdd')
7 order by 1 desc
8 )
9 where rownum<=1000;
建立儲存過程，刪除5天之前的TEM_和TMPTABSUBJ開頭的臨時表；
---建立刪除臨時表的儲存過程
CREATE OR REPLACE PROCEDURE DROP_TEMPTAB AS
CURSOR a IS
select table_name
from user_tables c, user_objects d
where c.table_name = d.object_name
and c.temporary = 'Y'
and (c.table_name like 'TEM_%' or c.table_name like 'TMPTABSUBJ%')
and d.object_type = 'TABLE'
and d.temporary = 'Y'
and d.CREATED < sysdate - 5;
BEGIN
FOR i IN a LOOP
EXECUTE IMMEDIATE 'drop table ' || i.table_name;
END LOOP;
END;
---查詢臨時表基本資訊
select count(*) from user_tables;
select count(*) from user_tables where temporary='Y';
select count(*),substr(table_name,1,5) from user_tables where temporary='Y' group by substr(table_name,1,5) order by 1 desc;
select * from user_tables where temporary='Y' and lower(table_name) like 'temptable_oa%';
select count(*) from user_tables where temporary='Y' and lower(table_name) like 'temptable_oa%';
select to_char(created, 'yyyymmdd'), count(*)
from user_tables a, user_objects b
where a.table_name = b.object_name
and a.temporary = 'Y' and lower(a.table_name) like 'temptable_oa%'
group by to_char(created, 'yyyymmdd')
order by 1 desc;
首次刪除臨時表之前，最好做一次資料庫全備；
建立儲存過程，定期刪除3天之前的TEMPTABLE000000開頭的臨時表；
---建立刪除臨時表的儲存過程
CREATE OR REPLACE PROCEDURE DROP_TEMPTAB AS
CURSOR a IS
select table_name
from user_tables c, user_objects d
where c.table_name = d.object_name
and c.temporary = 'Y'
and c.table_name like 'TEMPTABLE_OA%'
and d.object_type = 'TABLE'
and d.temporary = 'Y'
and d.CREATED < sysdate - 3;
BEGIN
FOR i IN a LOOP
EXECUTE IMMEDIATE 'drop table ' || i.table_name;
END LOOP;
END;
---兩種型別臨時表
CREATE OR REPLACE PROCEDURE DROP_TEMPTAB AS
CURSOR a IS
select table_name
from user_tables c, user_objects d
where c.table_name = d.object_name
and c.temporary = 'Y'
and (c.table_name like 'TEM_%' or c.table_name like 'TMPTABSUBJ%')
and d.object_type = 'TABLE'
and d.temporary = 'Y'
and d.CREATED < sysdate - 5;
BEGIN
FOR i IN a LOOP
EXECUTE IMMEDIATE 'drop table ' || i.table_name;
END LOOP;
END;
------多種型別臨時表
CREATE OR REPLACE PROCEDURE DROP_TEMPTAB_ALL AS
CURSOR a IS
select table_name
from user_tables c, user_objects d
where c.table_name = d.object_name
and c.temporary = 'Y'
and substr(table_name, 1, 9) in
('T_YDEPTID', 'T_ME_DEPT', 'T_BSCLASS')
and d.object_type = 'TABLE'
and d.temporary = 'Y'
and d.CREATED < sysdate - 3;
BEGIN
FOR i IN a LOOP
EXECUTE IMMEDIATE 'drop table ' || i.table_name;
END LOOP;
END;
---
create or replace procedure dropTempTable as
mysql varchar2(4000);
begin
for tablename in (select table_name
from user_tables t1, user_objects t2
where t1.temporary = 'Y'
and (t1.table_name like 'TEMPTABLE%' or t1.table_name like 'T_ILGROUP%')
and t1.table_name = t2.object_name
and t2.object_type = 'TABLE'
and t2.created < sysdate - 1.5) loop
begin
mysql := 'drop table ' || tablename.table_name;
execute immediate mysql;
exception
when others then
NULL;
end;
end loop;
end;
新增JOB，定期執行該儲存過程，自動刪除臨時表， 每天3點執行JOB，每2天執行一次；
SQL> VARIABLE JOBNO NUMBER;
SQL> VARIABLE INSTNO NUMBER;
SQL>
SQL> BEGIN
2 SELECT INSTANCE_NUMBER INTO :INSTNO FROM V$INSTANCE;
3 DBMS_JOB.SUBMIT(:JOBNO,
4 'DROP_TEMPTAB; ',
5 TRUNC(SYSDATE) + 1 + 3 / 24,
6 'TRUNC(SYSDATE)+2+3/24',
7 TRUE,
8 :INSTNO);
9 COMMIT;
10 END;
11 /
PL/SQL procedure successfully completed
CREATE GLOBAL TEMPORARY TABLE "CHENJCH_ZGMYHK_1103"."T_YDEPTID001610243"
( "PK" VARCHAR2(60)
) ON COMMIT DELETE ROWS
==============
temptable_ 開頭的臨時表
在下面的程式碼中產生的
nc.bs.oa.oaco.basecomp.helper.TempTableUtils.createTable(String columns, String pkCol)
###RMAN備份
---最後備份控制檔案，因為備份的後設資料記錄在控制檔案中；
---GoldenGate企業級運維實踐(65)
###### rman.sh begin
export NLS_DATE_FORMAT='yyyymmdd hh24:mi:ss'
export ORACLE_SID=orcl
rman target / log=/goldengate/rman.log <<EOF
crosscheck archivelog all;
run{
allocate channel ch1 type disk maxpiecesize 100M;
allocate channel ch2 type disk maxpiecesize 100M;
backup database tag 'full_epmln' format '/nas/backup/%d_full_%T_%U.bak';
sql 'alter system archive log current';
backup archivelog all tag 'arch_epmln' format '/nas/backup/%d_arch_%T_%U.bak';
backup current controlfile tag 'ctl_epmln' format '/nas/backup/%d_ctl_%T_%U.bak';
release channel ch1;
release channel ch2;
}
EOF
exit
###### rman.sh end
---在後臺執行
nohup sh rman.sh &
---如果rman指令碼有語法問題可以使用rman checksyntax檢查語法錯誤。
==========
RMAN恢復
==========
---備份
RUN
{
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
allocate channel c1 type disk;
allocate channel c2 type disk;
backup full database tag='full_orcl' format 'E:\backup\rman\rman_full_%d_%T_%U.bak';
sql 'alter system archive log current';
backup archivelog all tag 'arch_orcl' format 'E:\backup\rman\rman_arc_%d_%T_%U.bak' delete all input;
backup current controlfile tag 'ctl_orcl' format 'E:\backup\rman\rman_ctl_%d_%T_%U.bak';
release channel c1;
release channel c2;
report obsolete;
crosscheck backup;
crosscheck copy;
delete noprompt expired backup;
delete noprompt expired copy;
delete noprompt obsolete;
delete noprompt expired archivelog all;
delete noprompt archivelog until time 'sysdate-7';
}
exit;
conn chenjch/***
select * from user_tables;
create table test20180702 as select level as id from dual connect by level<=1000;
select * from test20180702;
alter system switch logfile;
alter system checkpoint;
---恢復
---恢復資料庫
startup nomount
---恢復控制檔案
run {
allocate channel d1 deviec type disk;
restore controlfile from 'controlfile_backuppiece_name';
release channel d1;
}
alter database mount;
---還原資料庫
show all
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;
run {
allocate channel d1 device type disk;
restore database;
release channel d1;
}
---還原歸檔
run {
allocate channel d1 device type disk;
restore archivelog from logseq 34503;
release channel d1;
}
---恢復資料庫
run {
allocate channel d1 device type disk;
recover database using backup controlfile until cancel;
release channel d1;
}
---查詢並記錄資料檔案SCN
select checkpoint_change#,file# from v$datafile_header;
###RMAN查詢狀態
SELECT start_time, end_time, operation, status
FROM V$RMAN_STATUS
order by start_time desc;
--檢視rman備份資訊
alter session set nls_date_format='yyyymmdd hh24:mi:ss';
col tag for a20
set lin 200 pages 200
col handle for a30
col SIZE_BYTES_DISPLAY for a10
select BS.RECID,BS.START_TIME,BS.PIECES,BS.INCREMENTAL_LEVEL,BS.BACKUP_TYPE,BP.PIECE#,BP.HANDLE,BP.TAG,BP.STATUS,ROUND(BP.bytes/1024/1024/1024,2) gb
from v$backup_set bs,v$backup_piece bp where bs.recid=bp.recid
--and tag like 'TAG20200406%'
and bp.HANDLE not like 'c-2051169266-%' and BS.BACKUP_TYPE!='L' and BS.START_TIME>to_date('20200401 08:00:00','yyyymmdd hh24:mi:ss');
select 'change backupset '||BS.RECID||' unavailable;'
from v$backup_set bs,v$backup_piece bp where bs.recid=bp.recid
and tag like 'TAG20200407%'
and bp.HANDLE not like 'c-2051169266-%' and BS.BACKUP_TYPE!='L' and BS.START_TIME>to_date('20200405 08:00:00','yyyymmdd hh24:mi:ss');
1.查詢過去和現在RMAN備份工作的詳細情況
COL INPUT_TYPE FORMAT a20
COL STATUS FORMAT a40
COL hours FORMAT 999.999
SELECT SESSION_KEY, INPUT_TYPE, STATUS,
TO_CHAR(START_TIME,'yyyy-mm-dd hh24:mi') start_time,
TO_CHAR(END_TIME,'yyyy-mm-dd hh24:mi') end_time,
ELAPSED_SECONDS/3600 hours
FROM V$RMAN_BACKUP_JOB_DETAILS
ORDER BY SESSION_KEY;
2.RMAN備份的速度，in_sec表示每秒的input速度，output表示每秒的output速度
COL in_sec FORMAT a10
COL out_sec FORMAT a10
COL TIME_TAKEN_DISPLAY FORMAT 99999999999
SELECT SESSION_KEY,
OPTIMIZED,
COMPRESSION_RATIO,
INPUT_BYTES_PER_SEC_DISPLAY in_sec,
OUTPUT_BYTES_PER_SEC_DISPLAY out_sec,
TIME_TAKEN_DISPLAY
FROM V$RMAN_BACKUP_JOB_DETAILS
ORDER BY SESSION_KEY;
3.顯示備份的尺寸。OUT_SIZE表示備份出的尺寸，IN_SIZE表示輸入的資料總大小。
COL in_size FORMAT a10
COL out_size FORMAT a10
SELECT SESSION_KEY,
INPUT_TYPE,
COMPRESSION_RATIO,
INPUT_BYTES_DISPLAY in_size,
OUTPUT_BYTES_DISPLAY out_size
FROM V$RMAN_BACKUP_JOB_DETAILS
ORDER BY SESSION_KEY;
4.檢視未完成的RMAN備份進度
set line 9999
col opname for a35
col start_time for a19
col TOTALWORK for 999999999999999999
SELECT SID,SERIAL#,opname, to_char(start_time,'yyyy-mm-dd HH24:MI:SS') start_time, SOFAR, TOTALWORK,ROUND(SOFAR/TOTALWORK*100,2) "%COMPLETE",ceil(ELAPSED_SECONDS/60) ELAPSED_MI
FROM V$SESSION_LONGOPS where opname like 'RMAN%' AND SOFAR <> TOTALWORK and TOTALWORK<>0 order by start_time asc;
--recover進度
col type format a15
select START_TIME,TYPE,ITEM,TIMESTAMP from v$recovery_progress;
###九:壞塊###
檢視資料檔案號及名稱 select file_id,tablespace_name,file_name,bytes/1024/1024 from dba_data_files order by 1; 透過DBV檢查檔案是否有壞塊 檢查 nnc_index01.dbf 檔案 dbv file='/oradata/nc/nnc_index01.dbf' 檢視壞塊所屬段及型別 檢視 file=6(459202) 對應段型別及名稱 select tablespace_name, segment_type, owner, segment_name from dba_extents where file_id = 6 and 459202 between block_id and block_id + blocks - 1; SELECT * FROM dba_extents WHERE file_id = &AFN and &BL between block_id AND block_id + blocks - 1; 如果損失的是資料 exp備份的話，可以設定內部10231事件，該事件能夠讓Oracle在全表掃描時跳過損壞的資料塊，也就是使exp跳過這些損壞的block alter system set events='10231 trace name context forever,level 10'; --成功匯出後記得要關閉10231內部事件 alter system set events='10231 trace name context off'; 透過expdp 備份可以直接跳過壞塊； --rman恢復壞塊 RMAN> backup validate datafile 5; SQL> select FILE#,BLOCK#,BLOCKS,CORRUPTION_CHANGE#,CORRUPTION_TYPE from v$database_block_corruption where file#=5; RMAN> blockrecover datafile 5 block 20869; RMAN> blockrecover datafile 5 block 20869,xxx,xxx; ---可以指定多個塊 87826, 檔案 6, 塊 15253 (2)， SELECT * FROM dba_extents WHERE file_id = 6 and 15253 between block_id AND block_id + blocks - 1; 如果是表，可以使用10231事件忽略壞塊，然後使用CTAS方式重建表最後rename table，別忘記rebuild index alter session SET EVENTS '10231 trace name context forever,level 10'; create table tab_new as select * from tab; rename tab to tab_bak; rename tab_new to new; select 'alter index '||index_name||' rebuild;' from user_ind_columns where table_name='T1'; alter session SET EVENTS '10231 trace name context off'; 其他： select segment_name,owner,tablespace_name from dba_extents where 388969 between BLOCK_ID and BLOCK_ID+BLOCKS-1 and FILE_ID=8; select tablespace_name, segment_type, owner, segment_name from dba_extents where file_id = 7 and 336465 between block_id and block_id + blocks - 1; dbv file='D:\ORACLE\PRODUCT\10.2.0\ORADATA\CGJT\NNC_DATA01.DBF' RMAN> backup validate datafile 7; SQL> select * from v$database_block_corruption where file#=7; RMAN> blockrecover datafile 6 block 459202,643867,1963727; RMAN> blockrecover datafile 8 block 169072; dbv file='/oradata/nc/nnc_index01.dbf' 檢視file=6(459202)對應段型別及名稱 select tablespace_name, segment_type, owner, segment_name from dba_extents where file_id = 6 and 459202 between block_id and block_id + blocks - 1; 恢復datafile=7的所有壞塊 RMAN>backup validate datafile 7; 驗證壞塊位置 select * from v$database_block_corruption where file#=7; 進行恢復 RMAN>blockrecover datafile 7 block 336465; SELECT tablespace_name, segment_type, owner, segment_name FROM dba_extents WHERE file_id = &fileid and &blockid between block_id AND block_id + blocks - 1; （1）ALL ZERO：Block header on disk contained only zeros. The block may be valid ifit was never filled and if it is in an Oracle7 file. The buffer will bereformatted to the Oracle8 standard for an empty block. （2）FRACTURED： Block header looks reasonable, but the front and back of the blockare different versions. （3）CHECKSUM： optional check value shows that the block is not self-consistent.It is impossible to determine exactly why the check value fails, but itprobably fails because sectors in the middle of the block are from differentversions. （4）CORRUPT： Block is wrongly identified or is not a data block (for example,the data block address is missing) （5）LOGICAL： Specifies the range is for logically corrupt blocks.CORRUPTION_CHANGE# will have a nonzero value.
###十:表空間###
###檢視錶空間使用率
set pagesize 9999
set linesize 132
col tablespace_name for a35
select
a.tablespace_name,
a.Total_mb,
f.Free_mb,
round(a.total_MB-f.free_mb,2) Used_mb,
round((f.free_MB/a.total_MB)*100) "%_Free"
from
(select tablespace_name, sum(bytes/(1024*1024)) total_MB from dba_data_files group by tablespace_name) a,
(select tablespace_name, round(sum(bytes/(1024*1024))) free_MB from dba_free_space group by tablespace_name) f
WHERE a.tablespace_name = f.tablespace_name(+)
order by "%_Free"
/
---檢視錶空間使用情況
set line 200
col tablespace_name for a30
select tablespace_name,
bytes / 1024 / 1024 / 1024 as b_byte,
autoextensible,
maxbytes / 1024 / 1024 / 1024 as m_byte
from dba_data_files
order by 1, 2 desc;
擴容百分比:
set pagesize 9999
set pagesize 9999
set linesize 132
col tablespace_name for a30
select
a.tablespace_name,
a.Total_mb,
f.Free_mb,
' Add'||to_char((total_MB*(1-PCT/100)-Free_mb)/(PCT/100)/1024,'9990.9')||'GB to '||PCT||'%' "Add_Size(GB)"
from
(select (&pct) PCT from dual),
(select tablespace_name, sum(bytes/(1024*1024)) total_MB from dba_data_files group by tablespace_name) a,
(select tablespace_name, round(sum(bytes/(1024*1024))) free_MB from dba_free_space group by tablespace_name) f
WHERE a.tablespace_name = f.tablespace_name(+)
order by "Add_Size(GB)"
/
Enter value for pct: 50
檢視temp臨時表空間使用率
SET PAGESIZE 400
SET LINES 300
COL D.TABLESPACE_NAME FORMAT A15
COL D.TOT_GROOTTE_MB FORMAT A10
COL TS-PER FORMAT A15
SELECT d.tablespace_name "Name",
TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)",
TO_CHAR(NVL(t.hwm, 0)/1024/1024,'99999999.999') "HWM (M)",
TO_CHAR(NVL(t.hwm / a.bytes * 100, 0), '990.00') "HWM % " ,
TO_CHAR(NVL(t.bytes/1024/1024, 0),'99999999.999') "Using (M)",
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Using %"
FROM sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a,
(select tablespace_name, sum(bytes_cached) hwm, sum(bytes_used) bytes from v$temp_extent_pool group by tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management like 'LOCAL'
AND d.contents like 'TEMPORARY';
---1 哪個sql語句佔用大量temp空間
SELECT instance_number,
ash.p1,
ash.p2,
ash.p3,
ash.temp_space_allocated/1024/1024/1024 "USETEMP/G",
ash.module,
to_char(sample_time, 'hh24:mi:ss.ff') TIME,
session_id sid,
ash.sql_id,
ash.program,
en.event_name,
ash.blocking_session
FROM sys.WRH$_ACTIVE_SESSION_HISTORY ash, sys.wrh$_event_name en
WHERE ash.event_id = en.event_id(+)
AND sample_time >= to_timestamp('20210727 2300', 'yyyymmdd hh24mi')
AND sample_time <= to_timestamp('20210728 0910', 'yyyymmdd hh24mi')
and temp_space_allocated is not null
ORDER BY temp_space_allocated desc;
---2 哪些會話在等待臨時表空間
SELECT instance_number,
ash.p1,
ash.p2,
ash.p3,
to_char(sample_time, 'hh24:mi:ss.ff') TIME,
session_id sid,
ash.sql_id,
ash.program,
en.event_name,
ash.blocking_session
FROM sys.WRH$_ACTIVE_SESSION_HISTORY ash, sys.wrh$_event_name en
WHERE ash.event_id = en.event_id(+)
AND sample_time >= to_timestamp('20210727 2300', 'yyyymmdd hh24mi')
AND sample_time <= to_timestamp('20210728 0910', 'yyyymmdd hh24mi')
and event_name like '%enq: SS - contention%'
ORDER BY sample_time;
---3 該時間段內在做什麼操作
SELECT instance_number,
ash.p1,
ash.p2,
ash.p3,
to_char(sample_time, 'hh24:mi:ss.ff') TIME,
session_id sid,
session_serial#,
ash.sql_id,
ash.program,
en.event_name,
ash.blocking_session
FROM sys.WRH$_ACTIVE_SESSION_HISTORY ash, sys.wrh$_event_name en
WHERE ash.event_id = en.event_id(+)
AND sample_time >= to_timestamp('20210727 2300', 'yyyymmdd hh24mi')
AND sample_time <= to_timestamp('20210728 0910', 'yyyymmdd hh24mi')
and session_id = 第一步查詢出的blocking_session
ORDER BY sample_time;
###undo空間使用情況
set pagesize 999 linesize 300
col machine for a16
col program for a25
col status for a10
col sql_id for a16
col sql_text for a50
select distinct s.machine,s.program,s.sid,round(t.used_ublk*8/1024,2) undo_MB,used_urec undo_records,s.status,l.sql_text
from v$transaction t,gv$session s,v$sqlstats l
where t.ses_addr=s.saddr
and s.sql_id=l.sql_id(+)order by undo_MB;
---檢視undo恢復時間
alter session set nls_date_format='yyyymmdd hh24:mi:ss';
select usn, state, undoblockstotal "Total", undoblocksdone "Done",
undoblockstotal-undoblocksdone "ToDo",
decode(cputime,0,'unknown',sysdate+(((undoblockstotal-
undoblocksdone) / (undoblocksdone / cputime)) / 86400))
"Estimated time to complete"
from v$fast_start_transactions;
--檢視undo分佈
select distinct status,tablespace_name ,to_char(round(sum(bytes)/1024/1024)) sizeM,count(1) from dba_undo_extents group by tablespace_name,status;
---檢視oracle隱含引數
select a.ksppinm name, b.ksppstvl value, a.ksppdesc description
from x$ksppi a, x$ksppcv b
where a.indx = b.indx
and a.ksppinm like '_undo_autotune';
select a.ksppinm name, b.ksppstvl value, a.ksppdesc description
from x$ksppi a, x$ksppcv b
where a.indx = b.indx
and a.ksppinm = 'event';
###resize
set line 300
col file_name for a50
col cmd for a90
set pagesize 1000
select a.file_id,
a.file_name,
a.filesize,
b.freesize,
(a.filesize - b.freesize) usedsize,
c.hwmsize,
c.hwmsize - (a.filesize - b.freesize) unsedsize_belowhwm,
a.filesize - c.hwmsize canshrinksize,
'alter database datafile ' ||''''|| a.file_name ||''''|| ' resize ' || c.hwmsize || 'M;' cmd
from (select file_id, file_name, round(bytes / 1024 / 1024) filesize
from dba_data_files) a,
(select file_id, round(sum(dfs.bytes) / 1024 / 1024) freesize
from dba_free_space dfs
group by file_id) b,
(select file_id, round((max(block_id)+max(blocks)) * 8 / 1024) HWMsize
from dba_extents
group by file_id) c
where a.file_id = b.file_id
and a.file_id = c.file_id and file_name like '%ecds%'
order by unsedsize_belowhwm desc;
select tablespace_name,file_id,(block_id+blocks)*(select value from v$parameter where name='db_block_size')/1024/1024 "Allo_MB"
from dba_extents
where (file_id,block_id) in
(select file_id,max(block_id) blkid from dba_extents group by file_id)
order by 1,2;
###十一:日誌檔案###
非歸檔模式，非當前日誌損壞，資料庫開啟模式 startup mount; alter database clear logfile group 2; alter database open; 非歸檔模式、資料庫關閉、不是正在使用的日誌檔案損壞。 startup mount; alter database clear logfile group 2; alter database open; 歸檔模式，非當前日誌損壞，資料庫開啟模式 注意不需要重啟資料庫，只需要把日誌清除即可。 alter database clear unarchived logfile group 2; 歸檔模式，非當前日誌損壞，資料庫關閉模式。 startup mount; alter database clear unarchived logfile group 2; alter database open; 總結，對於不是當前使用的歸檔日誌損壞，歸檔模式需要使用alter database clear unarchived 命令清空日誌 組即可。 對於非歸檔模式需要使用alter system clear日誌檔案組即可。 歸檔模式，資料庫open狀態、當前正在使用的日誌檔案損壞 由於這個時候，雖然當前日誌是正在被使用的，但是我們可以先進行切換日誌之後，然後執行clear操作。 alter database clear unarchived logfile group 1; alter database clear unarchived logfile group 1 * ERROR at line 1: ORA-01624: log 1 needed for crash recovery of instance RHYS (thread 1) ORA-00312: online log 1 thread 1: '/opt/app/oracle/oradata/RHYS/redo01.log' alter system switch logfile; alter database clear unarchived logfile group 1; shutdown immediate; startup 歸檔模式，資料庫open狀態、當前正在使用的日誌檔案損壞，並且正常關閉資料庫。 startup mount; alter database clear unarchived logfile group 2; alter database open; 歸檔模式，資料庫open狀態、當前正在使用的日誌檔案損壞，並且異常關閉資料庫。 使用隱含引數。 alter system set "_allow_resetlogs_corruption"=true scope=spfile; ---alter system reset "_allow_resetlogs_corruption" scope=spfile; alter database open resetlogs; 重建例項然後使用expdp和impdp，將資料匯出在匯入資料庫 總結：對於當前正在使用的日誌的損壞，一般透過備份來修復，如果不行只能採用第二種設定隱含引數_allow_resetlogs_corruption來恢復。 ---alter database add logfile group 1 'D:\APP_10.2.0.4\CHEN_DATAFILE\REDO01a.LOG' size 30M; ---alter database add logfile member 'D:\APP_10.2.0.4\CHEN_DATAFILE\REDO01a.LOG' to group 1; 1.alter session set events 'IMMEDIATE trace name ADJUST_SCN level x'; --需要資料庫OPEN 2.透過10015事件 alter session set events '10015 trace name adjust_scn level x'; --在資料庫無法開啟，mount狀態下。 注:level 1為增進SCN 10億 (1 billion) (1024*1024*1024=1073741824) #*._allow_resetlogs_corruption=TRUE *._allow_error_simulation=TRUE *._minimum_giga_scn=1047 _minimum_giga_scn=n的含義是把SCN往前推進到nG，但請注意，只有在SCN小於nG的時候才會用到這個隱含引數，反之則Oracle會置這個隱含引數於不顧。 比如_minimum_giga_scn設定為1 11.2.0.2.5 以後不再支援透過_minimum_giga_scn來調整scn 如果想調整scn 需要透過直接調整controlfile或者調整datafile header然後再重建控制檔案 ORA-00600: internal error code, arguments: [2662] ---oradebug rman target / catalog rc_admin/RC_ADMIN@prod4 RMAN> list failure; RMAN> advise failure; RMAN> repair failure; # database point-in-time recovery restore database until scn 1015002; recover database until scn 1015002; alter database open resetlogs; --檢視redo日誌： set lin 200 pages 299 col member for a60 col thread# for 999 col group# for 999 col members for 999 col status for a10 select l.thread#,l.group#,l.bytes/1024/1024 mb,l.sequence#,l.members,l.status,lf.member from v$Log l, v$Logfile lf where l.group#=lf.group# order by l.thread#,l.group#;
###十二:RAC###
[grid@node1 ~]$ srvctl config database racdb [grid@node1 ~]$ srvctl status database -d racdb ---[grid@node1 ~]$ srvctl start database -d racdb -o open [grid@node1 ~]$ srvctl config database -d racdb -a [grid@node1 ~]$ srvctl config scan [grid@node1 ~]$ srvctl status scan_listener [grid@node1 ~]$ srvctl status asm -a [grid@node1 ~]$ crsctl query css votedisk [grid@node1 ~]$ ocrcheck crsctl status res -t SQL> select inst_id,status from gv$instance order by inst_id; SQL> select inst_id,username,count(*) from gv$session where type='USER' group by inst_id,username order by count(*) desc; 檢查crs叢集服務情況 crsctl check cluster -all [grid@node1 ~]$ srvctl config database racdb [grid@node1 ~]$ srvctl status database -d racdb ---[grid@node1 ~]$ srvctl start database -d racdb -o open [grid@node1 ~]$ srvctl config database -d racdb -a [grid@node1 ~]$ srvctl config scan [grid@node1 ~]$ srvctl status scan_listener [grid@node1 ~]$ srvctl status asm -a [grid@node1 ~]$ crsctl query css votedisk [grid@node1 ~]$ ocrcheck ---[oracle@edbjr2p2 bin]$ ./srvctl start database -d EMREP -o open ---[oracle@edbjr2p2 bin]$ ./srvctl stop database -d EMREP -o immediate 檢查叢集狀態： [grid@rac02 ~]$ crsctl check cluster 位於磁碟組中的所有資料檔案 select name from v$datafile union select member from v$logfile union select name from v$controlfile union select name from v$tempfile; $ORA_CRS_HOME/bin/crs_stat –t 檢視CRS狀態 [oracle@node1 bin]$ ./crsctl check crs 檢視CSS狀態 [oracle@node1 bin]$ ./crsctl check css oracle rac常用的命令 節點層：olsnodes 網路層: oifcfg 叢集層：crsctl,ocrcheck,ocrdump,ocrconfig 應用層：srvctl,onsctl,crs_stat [oracle@rac1 ~]$ srvctl status asm V$ASM_DISK V$ASM_DISKGROUP V$ASM_FILE 最後，我們對agent相關的trace檔案進行簡單的介紹。首先,agent的trace 檔案位於路徑GRID_HOME/log/<host>/agent下，以下是比較詳細的資訊。 GRID_HOME/log/<host>/agent /ohasd/orarootagent_root <– ohasd產生的orarootagent日誌 GRID_HOME/log/<host>/agent/ohasd/oraagent_grid <– ohasd產生的oraagent日誌 GRID_HOME/log/<host>/agent/ohasd/oracssdagent_root <– ohasd產生的cssdagent日誌 GRID_HOME/log/<host>/agent/ohasd/oracssdmonitor_root <– ohasd產生的cssdmonitor日誌 GRID_HOME/log/<host>/agent/crsd/oraagent_grid <– crsd產生的oraagent日誌,owner為grid GRID_HOME/log/<host>/agent/crsd/oraagent_oracle <– crsd產生的oraagent日誌,owner為oracle GRID_HOME/log/<host>/agent/crsd/orarootagent_root <–crsd產生的orarootagent日誌 另外,以下的檔案對診斷agent相關的問題也很有幫助。 叢集alert log(Grid_home/log/<hostname>/alert<hostname>.log) Grid_home/log/<hostname>/ohasd/ohasd.log Grid_home/log/<hostname>/crsd/crsd.log --檢視asm磁碟組空間 select name,total_mb,free_mb,round((total_mb-free_mb)/total_mb*100,2) "Used%" from v$asm_diskgroup; --linux共享儲存linux 7.X emc檢視儲存盤： [root@sy-wpl-db02 rules.d]# powermt display dev=all 華為emc: [root@sy-wpl-db02 rules.d]# upadmin show vlun --rhel 7配置華為儲存 for i in b c d e f g h i j k l m n o p q r s t u v w x y; do echo "KERNEL==\"sd*[!0-9]\", SUBSYSTEM==\"block\", PROGRAM==\"/usr/lib/udev/scsi_id -g -u -d /dev/%k\", RESULT==\"`/usr/lib/udev/scsi_id --whitelisted --replace-whitespace --device=/dev/sd$i`\", SYMLINK+=\"asm-disk$i\", OWNER=\"grid\", GROUP=\"asmadmin\", MODE=\"0660\"" | grep -v 'RESULT==""'; done >> /etc/udev/rules.d/99-oracleasm-disks.rules --檢視磁碟資訊 set pages 200 col path for a35 col name for a20 col dgname for a25 col diskname for a25 col disknumber for 999 set lin 200 col FAILGROUP for a20 select dg.name dgname,ad.name diskname,ad.disk_number disknumber,ad.path,ad.header_status,ad.mode_status,ad.total_mb,ad.free_mb,ad.failgroup from v$asm_diskgroup dg,v$asm_disk ad where dg.GROUP_NUMBER(+)=ad.group_number order by dg.name,ad.path; --檢視磁碟組資訊 col COMPATIBILITY for a10 col DATABASE_COMPATIBILITY for a20 select name,state,type,total_mb/1024 GB,free_mb/1024 GB,COMPATIBILITY,DATABASE_COMPATIBILITY from v$asm_diskgroup; select name,total_mb,free_mb,total_mb-free_mb used_mb,round((total_mb-free_mb)/total_mb*100,2) "Used%" from v$asm_diskgroup; select name,total_mb,free_mb/1024 free_gb,total_mb-free_mb used_mb,round((total_mb-free_mb)/total_mb*100,2) "Used%" from v$asm_diskgroup;
###十三:12c###
###oracle12c命令
---檢視容器ID，容器名，當前連線使用者
SQL> show con_id con_name user
或者
SQL> select sys_context('USERENV','CON_ID') as con_id,sys_context('USERENV','CON_NAME') as cur_container,sys_context('USERENV','SESSION_USER') as cur_user from dual;
SQL> show pdbs
SQL> select name, decode(cdb, 'YES', 'Multitenant Option enabled', 'Regular 12c Database: ') "Multitenant Option" , open_mode, con_id from v$database;
SQL> select cdb from v$database;
SQL> select sys_context('userenv','con_name') "Container DB" from dual;
SQL> select con_id, dbid, guid, name , open_mode from v$pdbs;
SQL> select con_id,dbid,name,open_mode from v$pdbs;
SQL> alter pluggable database pdborcl open;
SQL> select con_id,dbid,name,open_mode from v$pdbs;
切換容器進入pdb資料庫
SQL> alter session set container=pdborcl;
SQL> startup
SQL> alter pluggable database pdborcl close;
SQL> alter session set container=cdb$root;
SQL> select instance_name,status from v$instance;
---啟動/關閉外掛資料庫
alert pluggable database all open;
alert pluggable database all close;
###ORA-28040
在資料庫伺服器上$ORACLE_HOME/network/admin目錄建立sqlnet.ora檔案，並寫入:
SQLNET.ALLOWED_LOGON_VERSION_SERVER=8
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8
寫入後不用重啟監聽，立即生效，已經可以連線了；
SQLNET.ALLOWED_LOGON_VERSION_SERVER=8
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8
不用重啟資料庫或者監聽，也不用重啟應用。
區別如下：
SQLNET.ALLOWED_LOGON_VERSION_SERVER：控制可以連線到12c資料庫的客戶端版本（client --->orace 12c db ）
SQLNET.ALLOWED_LOGON_VERSION_CLIENT：控制12c資料庫可以連到哪些版本的資料庫（orace 12c db --->其它版本的oracle db），例如：控制透過DB LINK可連線到哪些版本的oracle庫。
所以，該案例中主要起作用的是需要配置SQLNET.ALLOWED_LOGON_VERSION_SERVER。
在Oracle 12c中，雖然在sqlnet.ora加SQLNET.ALLOWED_LOGON_VERSION=8可以解決問題，但由於這個引數在12c已經廢棄了，而是用SQLNET.ALLOWED_LOGON_VERSION_CLIENT和SQLNET.ALLOWED_LOGON_VERSION_SERVER代替。
如果繼續使用該引數，會在告警日誌中無窮無盡的報“Using deprecated SQLNET.ALLOWED_LOGON_VERSION parameter.”
需要檢查
select username, account_status, default_tablespace, created, password_versions from dba_users;
alter user 服務名 identified by 密碼;
oerr ora 28040
28040, 0000, "No matching authentication protocol" //沒有匹配的驗證協議
// *Cause: There was no acceptable authentication protocol for
// either client or server.
// *Action: The administrator should set the values of the
// SQLNET.ALLOWED_LOGON_VERSION_SERVER and
// SQLNET.ALLOWED_LOGON_VERSION_CLIENT parameters, on both the
// client and on the server, to values that match the minimum
// version software supported in the system.
// This error is also raised when the client is authenticating to
// a user account which was created without a verifier suitable for
// the client software version. In this situation, that account's
// password must be reset, in order for the required verifier to
// be generated and allow authentication to proceed successfully.
###28040
Oracle 12c連線時報錯ORA-28040
問題描述：
客戶端連線Oracle 12c的時候，報錯誤:
ORA-28040: No matching authentication protocol
問題原因：
Oracle 12c的引數SQLNET.ALLOWED_LOGON_VERSION預設等於11。當我們使用11g JDBC之前版本的thin驅動連線的時候，就會報錯。
解決方法：
在資料庫伺服器上的oracle/network/admin/sqlnet.ora檔案新增一行SQLNET.ALLOWED_LOGON_VERSION=8，重啟資料庫，重新連線資料庫，可以成功連線，問題解決。
SQLNET.ALLOWED_LOGON_VERSION=8
SQLNET.ALLOWED_LOGON_VERSION_SERVER=11
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=11
---
Oracle12c連線問題ORA-28040：沒有匹配的驗證協議
於是在MOS上搜尋相關文件，查到這樣一篇文章Client / Server Interoperability Support Matrix for Different Oracle Versions (文件 ID 207303.1)
從文章中可以看出只有11.2.0.3及以上的版本的客戶端才能連線12.2版本的服務端。
再檢查本地的Oracle客戶端的版本是11.2.0.1，原來是客戶端版本太低了，安裝12.1版本的客戶端，順利登入。
###十四：需要關閉的特性###
密碼過期
關於“Oracle 11g密碼180天過期”，在擁有DBA許可權的使用者下執行：ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
一 檢視預設密碼到期時間
select * from dba_profiles where profile='DEFAULT' and resource_name='PASSWORD_LIFE_TIME';
二 修改密碼到期時間為無限制
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
三 檢視是否修改成功
select * from dba_profiles where profile='DEFAULT' and resource_name='PASSWORD_LIFE_TIME';
延時段
Oracle 11g deferred_segment_creation
11G中新特性，當表無資料時，不分配segment，以節省空間
設定deferred_segment_creation 引數
該引數值預設是TRUE，當改為FALSE時，無論是空表還是非空表，都分配segment
需注意的是：該值設定後對以前匯入的空表不產生
作用，仍不能匯出，只能對後面新增的表產生作用。
alter system set deferred_segment_creation=false;
SQL> show parameter FAILED_LOGIN_ATTEMPTS
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sec_max_failed_login_attempts integer 10
檢視資源使用
select * from V$RESOURCE_LIMIT;
---延時段建立解決方案
alter system set deferred_segment_creation=false;
select 'alter table ' || table_name || ' allocate extent;'
from t1 (select table_name
from user_tables
minus
select segment_name from user_segments where segment_type = 'Y') t1;
關於密碼延時特性實驗如下：
---密碼連續輸入次數為10次，同一個使用者連續輸入10次錯誤密碼，使用者會被鎖定
select * from dba_profiles where resource_name='FAILED_LOGIN_ATTEMPTS' and profile='DEFAULT';
alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited;
等待事件均為row cache lock
ALTER system SET event='28401 trace name context forever, level 1' scope=spfile;
###禁用引數###
---基數反饋
alter session set "_optimizer_use_feedback"=false;
select c.child_number, c.use_feedback_stats, s.sql_text
from v$sql_shared_cursor c, v$sql s
where s.sql_id = c.sql_id
and c.sql_id = 'g00wukhg516zd'
and s.child_number = c.child_number;
---笛卡爾
---MERGE JOIN CARTESIAN
alter session set "_optimizer_mjc_enabled" = false;
alter system set "_optimizer_mjc_enabled" = false;
alter system set "_optimizer_mjc_enabled" = false;
---BITMAP CONVERSION TO ROWIDS
alter session set "_b_tree_bitmap_plans"=true;
alter system set "_b_tree_bitmap_plans"=false;
alter session set optimizer_features_enable='11.2.0.1';
alter system set "_b_tree_bitmap_plans"=false;
alter system set "_allow_resetlogs_corruption"=true scope=spfile;
select /*+ opt_param('_b_tree_bitmap_plans','false') */ xxx from xxx ...... ;
---BITMAP CONVERSION FROM ROWIDS
###禁止生成監聽日誌
如果不想盡量監聽日誌，可以透過下面的方法關閉。
透過設定log_status引數為off實現禁止生成監聽器日誌
1）檢視log_status引數內容
LSNRCTL> show log_status
2）透過調整log_status引數值為off禁止生成監聽日誌
LSNRCTL> set log_status off
然後儲存設定即可：
save_config
###勒索病毒
---PROCEDURE "DBMS_CORE_INTERNAL
---PROCEDURE "DBMS_SYSTEM_INTERNAL
---PROCEDURE "DBMS_SUPPORT_INTERNAL
select * from dba_procedures where procedure_name like '%DBMS_CORE_INTERNAL%';
select * from dba_procedures where procedure_name like '%DBMS_SYSTEM_INTERNAL%';
select * from dba_procedures where procedure_name like '%DBMS_SUPPORT_INTERNAL%';
select * from dba_triggers where trigger_name like '%DBMS_CORE_INTERNAL%';
select * from dba_triggers where trigger_name like '%DBMS_SYSTEM_INTERNAL%';
select * from dba_triggers where trigger_name like '%DBMS_SUPPORT_INTERNAL%';
###十五：歸檔日誌###
###檢視資料庫歸檔分佈及頻率
SELECT TRUNC(first_time) "Date",
TO_CHAR(first_time, 'Dy') "Day",
COUNT(1) "Total",
SUM(DECODE(TO_CHAR(first_time, 'hh24'), '00', 1, 0)) "h0",
SUM(DECODE(TO_CHAR(first_time, 'hh24'), '01', 1, 0)) "h1",
SUM(DECODE(TO_CHAR(first_time, 'hh24'), '02', 1, 0)) "h2",
SUM(DECODE(TO_CHAR(first_time, 'hh24'), '03', 1, 0)) "h3",
SUM(DECODE(TO_CHAR(first_time, 'hh24'), '04', 1, 0)) "h4",
SUM(DECODE(TO_CHAR(first_time, 'hh24'), '05', 1, 0)) "h5",
SUM(DECODE(TO_CHAR(first_time, 'hh24'), '06', 1, 0)) "h6",
SUM(DECODE(TO_CHAR(first_time, 'hh24'), '07', 1, 0)) "h7",
SUM(DECODE(TO_CHAR(first_time, 'hh24'), '08', 1, 0)) "h8",
SUM(DECODE(TO_CHAR(first_time, 'hh24'), '09', 1, 0)) "h9",
SUM(DECODE(TO_CHAR(first_time, 'hh24'), '10', 1, 0)) "h10",
SUM(DECODE(TO_CHAR(first_time, 'hh24'), '11', 1, 0)) "h11",
SUM(DECODE(TO_CHAR(first_time, 'hh24'), '12', 1, 0)) "h12",
SUM(DECODE(TO_CHAR(first_time, 'hh24'), '13', 1, 0)) "h13",
SUM(DECODE(TO_CHAR(first_time, 'hh24'), '14', 1, 0)) "h14",
SUM(DECODE(TO_CHAR(first_time, 'hh24'), '15', 1, 0)) "h15",
SUM(DECODE(TO_CHAR(first_time, 'hh24'), '16', 1, 0)) "h16",
SUM(DECODE(TO_CHAR(first_time, 'hh24'), '17', 1, 0)) "h17",
SUM(DECODE(TO_CHAR(first_time, 'hh24'), '18', 1, 0)) "h18",
SUM(DECODE(TO_CHAR(first_time, 'hh24'), '19', 1, 0)) "h19",
SUM(DECODE(TO_CHAR(first_time, 'hh24'), '20', 1, 0)) "h20",
SUM(DECODE(TO_CHAR(first_time, 'hh24'), '21', 1, 0)) "h21",
SUM(DECODE(TO_CHAR(first_time, 'hh24'), '22', 1, 0)) "h22",
SUM(DECODE(TO_CHAR(first_time, 'hh24'), '23', 1, 0)) "h23",
ROUND(COUNT(1) / 24, 2) "Avg"
FROM gv$log_history
WHERE thread# = inst_id
GROUP BY TRUNC(first_time), TO_CHAR(first_time, 'Dy')
ORDER BY 1 desc;
###自動刪除歸檔
[oracle@ncdbserver1 ~]$ crontab -l
01 01 * * * /home/oracle/rman/del_arch.sh
[oracle@ncdbserver1 rman]$ pwd
/home/oracle/rman
[oracle@ncdbserver1 rman]$ ls
del_arch.log del_arch.sh
[oracle@ncdbserver1 rman]$ cat del_arch.sh
#!/bin/bash
source /home/oracle/.bash_profile
rman target / nocatalog log=/home/oracle/rman/del_arch.log <<EOF
crosscheck archivelog all;
delete noprompt expired archivelog all;
delete noprompt archivelog until time 'sysdate-7';
exit;
EOF
[oracle@ncdbserver1 rman]$ ls
del_arch.log del_arch.sh
[oracle@ncdbserver1 rman]$ cat del_arch.sh
#!/bin/bash
source /home/oracle/.bash_profile
rman target / nocatalog log=/home/oracle/rman/del_arch.log <<EOF
crosscheck archivelog all;
delete noprompt expired archivelog all;
delete noprompt archivelog until time 'sysdate-7';
exit;
EOF
=============
del_arch.txt
=============
connect target /
crosscheck archivelog all;
delete noprompt expired archivelog all;
delete noprompt archivelog until time 'sysdate-2';
exit;
===================
rman_del_arch.bat
===================
rman cmdfile=F:\del_arch\del_archlog.txt
select max(dbms_lob.getLength(sheetmodel) / 1024 / 1024),
min(dbms_lob.getLength(sheetmodel) / 1024 / 1024),
avg(dbms_lob.getLength(sheetmodel) / 1024 / 1024)
from nc63prod.tb_taskshtmodel;
清理thread 1 sequence XX,thread 2 sequence XX;
delete noprompt archivelog until sequence 502132 thread 1 like '+BJ_HPL_GDDB_ARCH/arch/1_%';
delete noprompt archivelog until sequence 505872 thread 2 like '+BJ_HPL_GDDB_ARCH/arch/2_%';
sql 'alter session set nls_date_format="YYYY-MM-DD HH24:MI:SS";
delete noprompt archivelog from time "2020-10-25 08:00:00" until time "2020-10-25 20:00:00" like '+BJ_HPL_GDDB_ARCH%';
###RMAN list
list backup;----備份集列表【歸檔日誌檔案、資料檔案、spfile、control file】
list backupset;----備份集列表【歸檔日誌檔案、資料檔案、spfile、control file】
list backup of database;--【資料檔案】
list backup of tablespace 'SYSTEM','USERS';---【表空間】
list backup of datafile 1,3,5;---【資料檔案對應的表空間資訊】
list backup of controlfile;---【control file】
list backup of spfile;----【spfile】
list backup of archivelog all;---【歸檔日誌檔案】
list backup of archivelog from time|scn|logquence ..to time|scn|sequence...;---【時間段內的歸檔日誌檔案】
list backup completed after|before...[between...and...];-----【時間段內的備份集列表（歸檔日誌檔案、資料檔案、spfile、control file）】
list backup tag=...;---【標記的備份集】
list backup device type disk;---【存放在磁碟中的備份集】
list backup recoverable;---【狀態為available的備份集，這些備份集可以用於還原與恢復工作】
list backup summary;---【備份集列表統計資訊】
RMAN> list backup of archivelog all;
RMAN> list backup of archivelog from scn 22727871375;
RMAN> list backup of archivelog until scn 22727813497;
RMAN> list backup of archivelog from scn 22727031113 until scn 22727268951;
RMAN> list backup of archivelog from logseq 25432;
RMAN> list backup of archivelog until logseq 25432;
RMAN> list backup of archivelog from logseq 25426 until logseq 25428;
RMAN> list archivelog until time 'sysdate-1';
登入到備份的資料庫伺服器上，執行
su - oracle
rman target /
---查詢指定例項，指定sequence歸檔
list archivelog sequence 101424 thread 2 ;
---查詢指定例項，指定sequence範圍歸檔
list archivelog from sequence 101424 until sequence 101427 thread 2 ;
---查詢指定時間前的歸檔
list archivelog until time 'sysdate-1';
---查詢所有本地歸檔
list archivelog all;
select to_char(first_time,'yyyymmdd'),sum(blocks*512)/1024/1024/1024 from v$archived_log group by to_char(first_time,'yyyymmdd') order by 1 desc;
--每天歸檔量(個數)
select
TO_CHAR(completion_time,'YYYY-MM-DD')
,count(*) from v$archived_log group by TO_CHAR(completion_time,'YYYY-MM-DD') order by TO_CHAR(completion_time,'YYYY-MM-DD');
select dest_id,SEQUENCE#,THREAD#,DELETED,status,to_char(FIRST_TIME,'yyyymmdd hh24:mi:ss') FIRST_TIME
from v$archived_log where dest_id=1 and THREAD#=1 and SEQUENCE# like '&sequence%';
select dest_id,SEQUENCE#,THREAD#,DELETED,status,to_char(FIRST_TIME,'yyyymmdd hh24:mi:ss') FIRST_TIME
from v$archived_log where dest_id=1 and THREAD#=2 and SEQUENCE# like '&sequence%';
select dest_id,SEQUENCE#,THREAD#,DELETED,status,to_char(COMPLETION_TIME,'yyyymmdd hh24:mi:ss') complete
from v$archived_log where SEQUENCE# like '5527%';
--按照sequence排序，指定時間
select dest_id,SEQUENCE#,thread#,status,to_char(COMPLETION_TIME,'yyyymmdd hh24:mi:ss') complete
from v$archived_log where dest_id=1 and status='D' and COMPLETION_TIME>to_date('20201115 05:11:24','yyyymmdd hh24:mi:ss') and COMPLETION_TIME<to_date('20201115 06:11:24','yyyymmdd hh24:mi:ss') order by SEQUENCE# ;
set lin 200 pages 200
select to_char(FIRST_TIME,'yyyymmdd hh24:mi:ss') FIRST_TIME,thread#,SEQUENCE#,status,to_char(COMPLETION_TIME,'yyyymmdd hh24:mi:ss') complete
from v$archived_log where dest_id=1 and SEQUENCE# like '27110%' order by SEQUENCE#;
#每日歸檔日誌量
select to_char(trunc(completion_time),'yyyy-mm-dd') as "Date",count(*) as "Count",sum(blocks * block_size)/1024/1024/1024 as "GB" from v$archived_log where CREATOR='ARCH' group by trunc(completion_time) order by 1;
select to_char(trunc(completion_time),'yyyy-mm-dd') as "Date",count(*) as "Count",sum(blocks * block_size)/1024/1024/1024 as "GB" from v$archived_log group by trunc(completion_time) order by 1;
select NAME,DEST_ID,THREAD#,SEQUENCE#,CREATOR,STATUS,COMPLETION_TIME,COMPRESSED,BLOCKS from v$archived_log where sequence# like '52202%' order by SEQUENCE#;
select NAME,DEST_ID,THREAD#,SEQUENCE#,CREATOR,STATUS,COMPLETION_TIME,COMPRESSED,BLOCKS from v$archived_log where sequence# like '52202%' order by SEQUENCE#;
#每小時歸檔量
select to_char(a.completion_time, 'yyyy-mm-dd hh24') as "Date",
count(*) as "Count",
sum(blocks * block_size) / 1024 / 1024 / 1024 as "GB"
from v$archived_log a
group by to_char(a.completion_time, 'yyyy-mm-dd hh24')
order by 1;
###十六：dbms_metadata.get_ddl###
###dbms_metadata.get_ddl###
select dbms_metadata.get_ddl('TABLE','TABLE_NAME','TABLE_OWNER') from dual;
select dbms_metadata.get_ddl('INDEX','INDEX_NAME','INDEX_OWNER') from dual;
select dbms_metadata.get_ddl('VIEW','VIEW_NAME','VIEW_OWNER') from dual;
select dbms_metadata.get_ddl('PROCEDURE','PROCEDURE_NAME','PROCEDURE_OWNER') from dual;
select dbms_metadata.get_ddl('FUNCTION','FUNCTION_NAME','FUNCTION_OWNER') from dual;
select dbms_metadata.get_ddl('DB_LINK','LINKAB','SUNDS') from dual;
set pagesize 0
set long 90000
set feedback off
set echo off
spool schema_ddl.sql
SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name,u.table_owner) FROM USER_INDEXES u;
spool off;
###十七:Oracle遊標溢位###
Oracle 使用 OPEN_CURSORS 引數指定一個會話一次最多可以開啟的遊標的數量。
應該是遊標溢位了，請在下回問題重現時，不要重啟中介軟體，馬上執行如下SQL，將查詢結果匯出成csv檔案發給我：
select s.sid, name, value,o.sql_id,t.SQL_TEXT
from v$statname n, v$sesstat s, v$open_cursor o,v$sql t
where o.sid = s.sid and o.sql_id=t.SQL_ID
and n.name in ('opened cursors current')
and s.statistic# = n.statistic# order by value desc;
如果資料庫用了rac,請連線每個例項，各執行那條SQL，將查詢結果發給我。
下面的查詢按降序顯示使用者“SCOTT”為每個會話開啟的遊標數。
select o.sid, osuser, machine, count(*) num_curs
from v$open_cursor o, v$session s
where user_name = 'SCOTT' and o.sid=s.sid
group by o.sid, osuser, machine
order by num_curs desc;
select q.sql_text
from v$open_cursor o, v$sql q
where q.hash_value=o.hash_value and o.sid = 217;
--檢視遊標使用情況
select b.name,sum(a.value)
from v$sesstat a, v$statname b
where a.statistic# = b.statistic#
and b.name = 'opened cursors current'
group by b.name;
--檢視會話遊標
select a.value,
s.username,
s.sid,
s.serial#
from
v$sesstat a,
v$statname b,
v$session s
where
a.statistic# = b.statistic# and
s.sid=a.sid and
b.name = 'opened cursors current' and a.value>20 order by 1;
select s.username,sum(a.value)
from
v$sesstat a,
v$statname b,
v$session s
where
a.statistic# = b.statistic# and
s.sid=a.sid and
b.name = 'opened cursors current' group by s.username;
set linesize 1000
set pagesize 1000
SELECT SUM (a.VALUE) total_cur,
AVG (a.VALUE) avg_cur,
MAX (a.VALUE) max_cur,
s.username,
s.machine
FROM v$sesstat a, v$statname b, v$session s
WHERE a.statistic# = b.statistic#
AND s.sid = a.sid
AND b.name = 'opened cursors current'
GROUP BY s.username, s.machine,s.sid
ORDER BY 1;
select sid , count(*) from v$open_cursor group by sid having count(*) > 50 order by 2 desc;
select inst_id,username,machine, count(machine) from gv$session group by inst_id,username,machine order by 4;
CREATE OR REPLACE PROCEDURE kill_session AS
CURSOR a,b IS
select distinct s.sid,i.serial#
from v$statname n, v$sesstat s, v$open_cursor o, v$sql t, v$session i
where o.sid = s.sid
and o.sql_id = t.SQL_ID
and n.name in ('opened cursors current')
and s.statistic# = n.statistic#
and s.sid = i.sid
and value >= 15000;
BEGIN
FOR i,j IN a LOOP
EXECUTE IMMEDIATE 'alter system kill session ' ||'i.sid,j.serial#';
exit when a%notfound;
END LOOP;
END;
CREATE OR REPLACE PROCEDURE kill_spid AS
CURSOR a IS
select distinct spid
from v$statname n,
v$sesstat s,
v$open_cursor o,
v$sql t,
v$session i,
v$process p
where o.sid = s.sid
and o.sql_id = t.SQL_ID
and n.name in ('opened cursors current')
and s.statistic# = n.statistic#
and s.sid = i.sid
and p.addr = i.paddr
and value >= 2;
BEGIN
FOR i IN a LOOP
EXECUTE IMMEDIATE 'host orakill orcl '||i.spid;
END LOOP;
END;
在DB裡寫個過程，捕捉要殺掉的程式ID，輸出到OS下的一文字里，然後在OS下寫指令碼，呼叫該過程，輸出要殺的程式後，再執行OS下的該文字，來殺掉該程式。
create or replace procedure kill_spid as
cursor c_job is
select distinct i.sid, i.serial#, p.spid
from v$statname n,
v$sesstat s,
v$open_cursor o,
v$sql t,
v$session i,
v$process p
where o.sid = s.sid
and o.sql_id = t.SQL_ID
and n.name in ('opened cursors current')
and s.statistic# = n.statistic#
and s.sid = i.sid
and p.addr = i.paddr
and value >= 1;
c_row c_job%rowtype;
begin
for c_row in c_job loop
dbms_output.put_line(c_row.sid|| '-' ||c_row.serial#||'-'||c_row.spid);
end loop;
end;
SQL>
CREATE OR REPLACE PROCEDURE kill_spid IS
chen UTL_FILE.file_type;
BEGIN
chen := UTL_FILE.FOPEN('AAA', 'chen.bat', 'W');
FOR x IN (select distinct a
from (select 'orakill orcl ' || p.spid as a
from v$statname n,
v$sesstat s,
v$open_cursor o,
v$sql t,
v$session i,
v$process p
where o.sid = s.sid
and o.sql_id = t.SQL_ID
and n.name in ('opened cursors current')
and s.statistic# = n.statistic#
and s.sid = i.sid
and p.addr = i.paddr
and value >= 10000)) LOOP
UTL_FILE.PUT_LINE(chen, x.a);
END LOOP;
UTL_FILE.FCLOSE(CHEN);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM, 1, 2000));
END;
/
###十八:BBED###
###BBED 使用bbed修改scn/ 這4個offset的位置內容，文件上是這樣說的： Oracleconsiders four attributes of this data structure when determining if a datafile is sync with the other data files of the database: （1）kscnbas (at offset 484) - SCN of last change to the datafile. （2）kcvcptim (at offset 492) -Time of the last change to the datafile. （3）kcvfhcpc (at offset 140) - Checkpoint count. （4）kcvfhccc (at offset 148) - Unknown, but is always 1 less than thecheckpoint point count. oracle主要透過這4個來判斷一致性，至於中文說明，我也寫在上面了。
###十九:大表刪除###
SM_BUSILOG_DEFAULT ：中的資料是 業務操作日誌 可以定期刪除
TB_TASKSHTMODEL ：這個是預算的快取資料表。根據年度儲存的，理論上去年的預算就沒有用了，可以清理，但是清理後第一次開啟預算樣表會比較慢。
pfxx：為外部交換平臺目錄，pfxxtemp下檔案可以刪減
1.先建立SM_BUSILOG_DEFAULT_BAK表和FIP_MESSAGELOG_BAK表
create table SM_BUSILOG_DEFAULT_BAK as select * from SM_BUSILOG_DEFAULT where 1=0;
create table FIP_MESSAGELOG_BAK as select * from FIP_MESSAGELOG where 1=0;
2.建立如下儲存過程
create or replace procedure cleanLogTable as
begin
insert into table SM_BUSILOG_DEFAULT_BAK as select * from SM_BUSILOG_DEFAULT where ts<=to_char(sysdate-30,'YYYY-MM-DD');
delete from SM_BUSILOG_DEFAULT where ts<=to_char(sysdate-30,'YYYY-MM-DD');
commit;
insert into table FIP_MESSAGELOG_BAK as select * from FIP_MESSAGELOG where ts<=to_char(sysdate-30,'YYYY-MM-DD');
delete from FIP_MESSAGELOG where ts<=to_char(sysdate-30,'YYYY-MM-DD');
commit;
end;
3.建立定時執行儲存過程的Job,該儲存過程每30天執行一次，凌晨3點執行
VARIABLE JOBNO NUMBER;
VARIABLE INSTNO NUMBER;
BEGIN
SELECT INSTANCE_NUMBER INTO :INSTNO FROM V$INSTANCE;
DBMS_JOB.SUBMIT(:JOBNO,'cleanLogTable;',TRUNC(SYSDATE)+1+3/24,'TRUNC(SYSDATE)+30+3/24',TRUE,:INSTNO);
COMMIT;
END;
/
###查詢段大小
select a.table_name,
a.column_name,
b.segment_name,
b.bytes / 1024 / 1024 as space_mb
from dba_lobs a, dba_segments b
where a.segment_name = b.segment_name
and a.owner = 'PDNC'
union all
select a.table_name,
a.column_name,
b.segment_name,
b.bytes / 1024 / 1024 as space_mb
from dba_lobs a, dba_segments b
where a.index_name = b.segment_name
and a.owner = 'PDNC'
order by space_mb desc;
SELECT (SELECT NVL(SUM(S.BYTES), 0) -- The Table Segment size
FROM DBA_SEGMENTS S
WHERE S.OWNER = UPPER('NCDB')
AND (S.SEGMENT_NAME = UPPER('SM_FILESTOREVIEW'))) +
(SELECT NVL(SUM(S.BYTES), 0) -- The Lob Segment Size
FROM DBA_SEGMENTS S, DBA_LOBS L
WHERE S.OWNER = UPPER('NCDB')
AND (L.SEGMENT_NAME = S.SEGMENT_NAME AND
L.TABLE_NAME = UPPER('SM_FILESTOREVIEW') AND
L.OWNER = UPPER('NCDB'))) +
(SELECT NVL(SUM(S.BYTES), 0) -- The Lob Index size
FROM DBA_SEGMENTS S, DBA_INDEXES I
WHERE S.OWNER = UPPER('NCDB')
AND (I.INDEX_NAME = S.SEGMENT_NAME AND
I.TABLE_NAME = UPPER('SM_FILESTOREVIEW') AND
INDEX_TYPE = 'LOB' AND I.OWNER = UPPER('NCDB'))) "TOTAL TABLE SIZE"
FROM DUAL;
###二十:OEM###
emca -deconfig dbcontrol -repos drop emca -config dbcontrol db -repos create emca -config dbcontrol db -repos create emca -repos drop emca -config dbcontrol db -repos create emca -config dbcontrol db -repos create
###二十一:建立測試資料###
create table t0115(id number,name varchar2(100)); declare begin for i in 1 .. 10000 loop insert into t0115 values (i*10000, '小陳'); commit; end loop; end;
###二十二:EXP-00056###
EXP-00056: ORACLE error 904 encountered 這個是ORACLE釋出的BUG，我在從9.2.0.1升級到9.2.0.6的時候也遇見過！ 執行@$ORACLE_HOME\rdbms\admin\catexp.sql @$ORACLE_HOME\rdbms\admin\catpatch.sql 步驟： SQL>CONNECT SYS AS SYSDBA SQL>@$ORACLE_HOME\rdbms\admin\catexp.sql SQL>Shutdown immediate SQL>startup migrate SQL>@$ORACLE_HOME\rdbms\admin\catpatch.sql SQL>shutdown immediate SQL>start up 此過程時間比較常，大概2小時左右。 select sql_text from v$sql where sql_id in (select sql_id from gv$session where paddr in (select addr from gv$process where program = 'ORACLE.EXE (SHAD)'));
###二十三:ORA-01102###
ORA-01102: cannot mount database in EXCLUSIVE mode 查了ml，瞭解到 ORA-1102 錯誤原因： 1 在ORACLE_HOME/dbs/存在 "sgadef.dbf" 檔案或者lk 檔案。這兩個檔案是用來用於鎖記憶體的。 2 oracle的 pmon, smon, lgwr and dbwr等程式未正常關閉。 3 資料庫關閉後，共享記憶體或者訊號量依然被佔用。 lk說明DATABASE 已經是MOUNT狀態了,不用再次MOUNT.當 DATABASE 被UNMOUNT 後會被自動刪除,如果DATABASE沒有MOUNT,卻依然存在這個問題，只有手工將其刪除。 具體解決ORA-01102問題的步驟: [oracle@oracle ~]$ cd $ORACLE_HOME [oracle@oracle db_1]$ cd dbs [oracle@oracle dbs]$ ls hc_orcl.dat initdw.ora init.ora lkORCL orapworcl spfileorcl.ora 此時在資料庫shutdown的情況下,仍然有不少程式關聯著程式,顯然是"死鎖" [oracle@oracle dbs]$ /sbin/fuser -u lkORCL sculkget: failed to lock /orasoft/product/10.2.0/db_1/dbs/lkWWL exclusive 同一個程式被多個使用者訪問發生了獨佔模式 sculkget: lock held by PID: 26312 發生獨佔模式的程式號為pid:26312 ORA-09968: Message 9968 not found; No message file for product=RDBMS, facility=ORA 並且沒有找到9968的資料訊號，同時了我們該訊號的型別 Linux Error: 11: Resource temporarily unavailable 導致資源無法被正常利用 Additional information: 26312 Thu Nov 17 15:51:16 2011 ORA-1102 signalled during: ALTER DATABASE MOUNT.. [oracle@ora10g dbs]$ ps -ef|grep 26312 oracle 26312 1 0 15:43 ? 00:00:02 ora_dbw0_wwl 報錯ORA-01102，而且安裝的時候也沒有看到哪裡有報錯資訊，一路都比較順利， 而且這也是第一次我碰到這個問題，當時我首先就檢查了alert日誌檔案，並把相關的錯誤資訊在metalink上檢視過了， 經過分析後判斷是由於程式間通訊被爭用導致，以下是我處理該問題的一個思路，並在最後附上了metalink原文以及朋友對該 問題的一個理解和處理辦法。 為什麼會發生如下錯誤，原因是多個使用者同時去訪問同一個資源就會發生獨佔模式， 因為在Linux裡面預設一個程式只被一個使用者訪問，要避免這個問題，在建立使用者的時候 指定預設去指定不同於其它使用者的優先順序就可以避免此類問題的發生。
###二十四:登入觸發器###
CREATE OR REPLACE TRIGGER logon_denied_to_alert
AFTER servererror ON DATABASE
DECLARE
message VARCHAR2(168);
ip VARCHAR2(15);
v_os_user VARCHAR2(80);
v_module VARCHAR2(50);
v_action VARCHAR2(50);
v_pid VARCHAR2(10);
v_sid NUMBER;
v_program VARCHAR2(48);
BEGIN
IF (ora_is_servererror(1017)) THEN
-- get ip FOR remote connections :
IF upper(sys_context('userenv', 'network_protocol')) = 'TCP' THEN
ip := sys_context('userenv', 'ip_address');
END IF;
SELECT sid INTO v_sid FROM sys.v_$mystat WHERE rownum < 2;
SELECT p.spid, v.program
INTO v_pid, v_program
FROM v$process p, v$session v
WHERE p.addr = v.paddr
AND v.sid = v_sid;
v_os_user := sys_context('userenv', 'os_user');
dbms_application_info.read_module(v_module, v_action);
message := to_char(SYSDATE, 'YYYYMMDD HH24MISS') ||
' logon denied from ' || nvl(ip, 'localhost') || ' ' ||
v_pid || ' ' || v_os_user || ' with ' || v_program || ' – ' ||
v_module || ' ' || v_action;
sys.dbms_system.ksdwrt(2, message);
END IF;
END;
/
---登入資訊(觸發器)
---<<DBA手記>>
create table log$information
(
logon_time timestamp,
host_name varchar2(100),
username varchar2(40),
sechemaname varchar2(40),
sessionuser varchar2(40),
ip_address varchar2(100)
);
create or replace trigger tr_login_record
after logon on database
begin
insert into log$information
select systimestamp,
sys_context('USERENV', 'HOST'),
sys_context('USERENV', 'CURRENT_USER'),
sys_context('USERENV', 'CURRENT_SCHEMA'),
sys_context('USERENV', 'SESSION_USER'),
sys_context('USERENV', 'IP_ADDRESS')
FROM dual;
commit;
exception
when others then
null;
end;
/
###二十五:程式碼裡SQL增加隨機數###
---1
String sql="select /*+ "+new java.util.Random().nextDouble()+" */ col"
---2
StringBuffer strSql =
new StringBuffer(" SELECT /*+ dbms_random.random() */ col...... ");
if(isFreeItem)
---3
StringBuilder bf = new StringBuilder(" select ");
bf.append("/*+dbms_random.value(100,0)*/");
bf.append(this.getSelectFieldsPart());
bf.append(" from ");
bf.append(this.getJoinPart());
bf.append(" where ");
bf.append(this.fixWhere.toString());
SqlUtil.andTowWhere(bf, this.getWhere());
###二十六:PLSQL亂碼###
NLS_LANG SIIMPLIFIED CHINESE_CHINA.ZHS16GBK 設定變數名：NLS_LANG，變數值：SIMPLIFIED CHINESE_CHINA.ZHS16GBK
###二十七:SCN###
---SCN SELECT A.FILE#, A.NAME, (SELECT CHECKPOINT_CHANGE# FROM V$DATABASE) SYSTEM_CKPT_SCN, A.CHECKPOINT_CHANGE# DF_CKPT_SCN, A.LAST_CHANGE# END_SCN, B.CHECKPOINT_CHANGE# START_SCN, B.RECOVER, A.STATUS FROM V$DATAFILE A, V$DATAFILE_HEADER B WHERE A.FILE# = B.FILE#; 系統檢查點（System Checkpoint）SCN 當checkpoint完成後，ORACLE將System Checkpoint SCN號存放在控制檔案中。我們可以透過下面SQL語句查詢： select checkpoint_change# from v$database; ---13944498 資料檔案檢查點（Datafile Checkpoint）SCN 當checkpoint完成後，Oracle將Datafile Checkpoint SCN存放在控制檔案中。我們可以透過下面SQL語句查詢所有資料檔案的Datafile Checkpoinnt SCN。 select name,checkpoint_change# from v$datafile; ---13944498 開始SCN（Start SCN） Oracle將StartSCN存放在資料檔案頭中。這個SCN用於檢查資料庫啟動過程是否需要做media recovery。我們可以透過以下SQL語句查詢： select name,checkpoint_change# from v$datafile_header; ---13944498 結束SCN（Stop SCN） ORACLE將StopSCN存放在控制檔案中。這個SCN號用於檢查資料庫啟動過程是否需要做instance recovery。我們可以透過以下SQL語句查詢： select name,last_change# from v$datafile; ---
###二十八:資料初始化###
---檢視資料庫SCN select 'exp_scn' item,current_scn value from v$database union all select 'Min_start_scn' item,min(start_scn) value from v$transaction; ---expdp匯出資料 expdp scott/tiger@xxx schemas=scott directory=TEST_DIR dumpfile=scott.dmp logfile=scott.log flashback_scn=209914484 ---匯入到裝載端 impdp scott/tiger schemas=scott directory=test_dir dumpfile=scott.dmp logfile=impdp.log ---編譯無效物件 select 'alter ' || object_type || ' ' || owner || '.' || object_name || ' compile;' from dba_objects where status = 'INVALID' and owner = 'SCOTT'; ---禁止trigger select 'alter trigger ' || OWNER || '.' || TRIGGER_NAME || ' enable;' from dba_triggers where owner = 'SCOTT' and status = 'ENABLED'; ---啟動capture 根據獲取的SCN啟動capture。 如果有Min_start_scn，那麼用Min_start_scn啟動Capture; 否則用exp_scn啟動capture; ---啟動loader 使用exp_scn指定scn啟動loader;
###二十九:DB2###
DB2CMD db2 connect to nc user DB2OFMIS using ufsoft*123 -----檢視當前例項下建立了哪些資料庫？ db2 list database directory db2look -tvf aaa.sql db2move NC import -io insert -u DB2OFMIS -p ufsoft*123 db2 list tablespaces show detail list tables db2pd -d NC -tablespace NNC_INDEX01 ALTER TABLESPACE DMS1 AUTORESIZE YES DB2 SQL error: SQLCODE: -964 update db cfg using LOGFILSIZ 20000 ---更改 日誌檔案大小 update db cfg using LOGPRIMARY 15 ---更改 主日誌檔案的數目 update db cfg using LOGSECOND 8 ---更改 輔助日誌檔案的數目 CREATE regular TABLESPACE NNC_INDEX01 PAGESIZE 4K MANAGED BY DATABASE USING ( FILE 'D:\DB2\NC633\nnc_index01' 409600) EXTENTSIZE 32 OVERHEAD 24.1 PREFETCHSIZE 32 TRANSFERRATE 0.9 BUFFERPOOL NCUSED4 alter tablespace NNC_INDEX01 ADD ( FILE 'D:\DB2\NC633\nnc_index01A' 409600) db2 connect reset db2 force application all get db cfg [for dbname] db2set DB2CODEPAEG=1386 ---SQL1478W db2set DB2_OVERRIDE_BPF=5000 db2stop force db2start ======== DB2---NC ======== ---db2move.lst ---ncdb.sql ---create_forign.sql ---create_tb.sql ---export.out ---import.out ---db2 =>force applications all ---drop database NC ---SQL1047N The appplication is already connected to another database. ---connect reset CREATE DATABASE NC USING CODESET GBK TERRITORY CN COLLATE USING SYSTEM CATALOG TABLESPACE MANAGED BY DATABASE USING (FILE 'D:\DB2\NC\Catalogdata1' 128000) USER TABLESPACE MANAGED BY DATABASE USING ( FILE 'D:\DB2\NC\Userdata1' 512000 ) TEMPORARY TABLESPACE MANAGED BY DATABASE USING ( FILE 'D:\DB2\NC\Tempspace1' 768000 ) ---connect to nc user db2inst1 using ufsoft*123 connect to nc user DB2OFMIS using ufsoft*123 CREATE Bufferpool NCUSED4 SIZE 102400 PAGESIZE 4K CREATE Bufferpool NCUSED16 SIZE 38400 PAGESIZE 16K CREATE Bufferpool NCTMPUSED16 SIZE 38400 PAGESIZE 16K CREATE regular TABLESPACE NNC_DATA01 PAGESIZE 16K MANAGED BY DATABASE USING ( FILE 'D:\DB2\NC\nnc_data01' 409600) EXTENTSIZE 8 OVERHEAD 24.1 PREFETCHSIZE 8 TRANSFERRATE 0.9 BUFFERPOOL NCUSED16 CREATE regular TABLESPACE NNC_DATA02 PAGESIZE 16K MANAGED BY DATABASE USING ( FILE 'D:\DB2\NC\nnc_data02' 409600) EXTENTSIZE 8 OVERHEAD 24.1 PREFETCHSIZE 8 TRANSFERRATE 0.9 BUFFERPOOL NCUSED16 CREATE regular TABLESPACE NNC_DATA03 PAGESIZE 16K MANAGED BY DATABASE USING ( FILE 'D:\DB2\NC\nnc_data03' 409600) EXTENTSIZE 8 OVERHEAD 24.1 PREFETCHSIZE 8 TRANSFERRATE 0.9 BUFFERPOOL NCUSED16 ---409600*16k=6.25G ---Alter tablespace NNC_DATA01 add (file ‘D:\DB2\NC\nnc_data01a’ 409600) CREATE regular TABLESPACE NNC_INDEX01 PAGESIZE 4K MANAGED BY DATABASE USING ( FILE 'D:\DB2\NC\nnc_index01' 409600) EXTENTSIZE 32 OVERHEAD 24.1 PREFETCHSIZE 32 TRANSFERRATE 0.9 BUFFERPOOL NCUSED4 CREATE regular TABLESPACE NNC_INDEX02 PAGESIZE 4K MANAGED BY DATABASE USING ( FILE 'D:\DB2\NC\nnc_index02' 409600) EXTENTSIZE 32 OVERHEAD 24.1 PREFETCHSIZE 32 TRANSFERRATE 0.9 BUFFERPOOL NCUSED4 CREATE regular TABLESPACE NNC_INDEX03 PAGESIZE 4K MANAGED BY DATABASE USING ( FILE 'D:\DB2\NC\nnc_index03' 409600) EXTENTSIZE 32 OVERHEAD 24.1 PREFETCHSIZE 32 TRANSFERRATE 0.9 BUFFERPOOL NCUSED4 ---409600*4k=1.5625G ---Alter tablespace NNC_INDEX01 add (file ‘D:\DB2\NC\nnc_index01b’ 409600) CREATE USER TEMPORARY TABLESPACE USERTEMP PAGESIZE 16K MANAGED BY SYSTEM USING ( 'D:\DB2\NC\Usertemp1' ) EXTENTSIZE 32 OVERHEAD 10.67 PREFETCHSIZE 32 TRANSFERRATE 0.04 BUFFERPOOL NCTMPUSED16 CREATE SYSTEM TEMPORARY TABLESPACE TEMPSPACE2 PAGESIZE 16K MANAGED BY SYSTEM USING ( 'D:\DB2\NC\Tempspace2' ) EXTENTSIZE 32 OVERHEAD 10.67 PREFETCHSIZE 32 TRANSFERRATE 0.04 BUFFERPOOL NCTMPUSED16 GRANT DBADM,CREATETAB,BINDADD,CONNECT,CREATE_NOT_FENCED_ROUTINE,IMPLICIT_SCHEMA,LOAD,CREATE_EXTERNAL_ROUTINE,QUIESCE_CONNECT,SECADM ON DATABASE TO USER db2inst1 GRANT DBADM,CREATETAB,BINDADD,CONNECT,CREATE_NOT_FENCED_ROUTINE,IMPLICIT_SCHEMA,LOAD,CREATE_EXTERNAL_ROUTINE,QUIESCE_CONNECT,SECADM ON DATABASE TO USER DB2OFMIS GRANT DBADM,CREATETAB,BINDADD,CONNECT,CREATE_NOT_FENCED_ROUTINE,IMPLICIT_SCHEMA,LOAD,CREATE_EXTERNAL_ROUTINE,QUIESCE_CONNECT,SECADM ON DATABASE TO USER db2inst1 GRANT DBADM,CREATETAB,BINDADD,CONNECT,CREATE_NOT_FENCED_ROUTINE,IMPLICIT_SCHEMA,LOAD,CREATE_EXTERNAL_ROUTINE,QUIESCE_CONNECT,SECADM ON DATABASE TO USER DB2OFMIS GRANT USE OF TABLESPACE NNC_DATA01 TO USER DB2OFMIS WITH GRANT OPTION GRANT USE OF TABLESPACE NNC_DATA02 TO USER DB2OFMIS WITH GRANT OPTION GRANT USE OF TABLESPACE NNC_DATA03 TO USER DB2OFMIS WITH GRANT OPTION GRANT USE OF TABLESPACE NNC_INDEX01 TO USER DB2OFMIS WITH GRANT OPTION GRANT USE OF TABLESPACE NNC_INDEX02 TO USER DB2OFMIS WITH GRANT OPTION GRANT USE OF TABLESPACE NNC_INDEX03 TO USER DB2OFMIS WITH GRANT OPTION GRANT USE OF TABLESPACE USERTEMP TO USER DB2OFMIS WITH GRANT OPTION CONNECT RESET 需要特別注意，本版支援的是DB2V10版本，在建庫時，務必檢查以下引數設定是否正確 db2set DB2_COMPATIBILITY_VECTOR= db2set DB2_SKIPINSERTED=YES db2set DB2_INLIST_TO_NLJN=YES db2set DB2_MINIMIZE_LISTPREFETCH=YES db2set DB2_ANTIJOIN=EXTEND db2stop force db2start DB2臨時表空間要求 CREATE USER TEMPORARY TABLESPACE USERTEMP01 PAGESIZE 4K MANAGED BY SYSTEM USING ( 'D:\DB2\NC\usertemp01' ) EXTENTSIZE 32 OVERHEAD 24.1 PREFETCHSIZE 16 TRANSFERRATE 0.9 BUFFERPOOL IBMDEFAULTBP ---GRANT USE OF TABLESPACE TEMPSPACE1 TO USER db2inst1 WITH GRANT OPTION GRANT USE OF TABLESPACE TEMPSPACE1 TO USER DB2OFMIS WITH GRANT OPTION ---DB21034E GRANT USE OF TABLESPACE USERTEMP01 TO USER DB2OFMIS WITH GRANT OPTION db2 connect to nc user DB2OFMIS using ufsoft*123 db2 -tvf create_tb.sql db2move nc import –io insert -u DB2OFMIS -p ufsoft*123 db2 –tvf create_foreign.sql ---SQL3088N 指定要裝入資料庫列 名稱 的源列與該資料庫列不相容，但資料庫列不可為空。 ---所有含有not null約束的表，匯入時都報錯SQL3088N，無法匯入資料；----可能是匯出的資料本身有問題 ---SQL3306N An SQL error -964 ----日誌滿了 ---匯入報錯 get db cfg for 資料庫名 後來把這三個引數的大小相應調整了一下，調整後如下： Log file size (4KB) (LOGFILSIZ) = 65536 (logprimary + logsecond) * logfilsiz * 4096 先看增大日誌的容量，注意紅色的值為1024 $db2 update db cfg for zssqdb01 using logfilsiz 8192 將其增大到8192 然後停止應用，停庫再啟庫就生效了 $db2 force applications all $db2stop $db2start ---ERROR -3304.Check message file tab1118.msg! ---匯入表結結構的文字中，沒有這個表，只有表資料，沒有表結構 SQLCODE:-3304 -sqlstate: sql3304N The table does not exist. ---db2 -tvf a.sql ---報錯SQLSTATE=42710 ---DB2 V10.5的資料庫匯入到低版本資料庫時，ORGANIZE BY ROW是10.5的新特性 =============== DB2LOOK =============== db2look -d cqyancao -e -o db.sql -i db2user -w psw 資料庫名 要出檔名 使用者名稱 密碼 示例: db2look -d DEPARTMENT -u walid -e -o db2look.sql =============== DB2---匯入匯出 =============== db2 backup database nc to d:\backup 如果資料庫正在被使用，可能回報錯“SQL1035N The database is currently in use. SQLSTATE=57019”需要停掉服務，用命令： db2 force application all db2 backup db nc to d:\backup 恢復資料庫 命令格式：db2 restore db <資料庫名> from <目錄名> 例: db2 restore db nc from d:\backup backup restore 使用情況說明 此類備份恢復在資料庫是相同作業系統環境下進行，如果進行跨作業系統平臺就不能使用了，備份恢復比較簡單。 db2move的備份恢復 執行db2move 命令匯出資料 命令格式： db2move <資料庫名> export –tc <使用者名稱> -u <使用者名稱> -p <使用者密碼> 引數 –tc 建立表的使用者名稱 -tn 使用者的表名 -sn 模式名 即匯出該模式下的所有表 以上三個引數只適用於export命令 如：---匯出sample 的org表 C:\ >db2move sample export -tn org 執行db2move 命令匯出資料 使用export出的邏輯資料集進行恢復 執行db2move命令，匯入表的結構及資料。 命令格式： db2move <資料庫名> import -io insert -u <使用者名稱> -p <密碼> db2move <資料庫名> import -io replace_create -u <使用者名稱> -p <密碼> 執行db2look 命令匯出資料結構建立語句（即表、試圖等的建立語句） 命令格式：db2look -d <資料庫名> -u <使用者名稱> -e -o d:\db2_bk\credb.sql -i <使用者名稱> -w <使用者密碼> db2look –d sample –e –o d:\db2_bk\credb.sql NC產品透過db2move備份恢復一例: 1、資料庫的匯出 1) 首先連線到要匯出的資料庫上，如本例匯出資料庫nctest： db2 connect to nctest user db2inst1 using db2inst1 其中的引數user後面跟指定使用者，using後面指定使用者的密碼 2) 用db2look 匯出資料物件的定義語句 db2look –d nctest –e –o ncdb.sql –i db2inst1 –w db2inst1 命令中的引數 –o 意思是將資料物件的定義匯出到指定的檔案中 3） 使用db2move 命令匯出資料庫物件的資料 db2move nctest export –u db2inst1 –p db2inst1 –u 指定連線的使用者 –p 指定使用者的密碼 3．在將資料庫的資料結構定義檔案 ncdb.sql 中語句中的建立表、索引、主鍵等的定義語句單獨儲存在一個檔案中，如儲存在create_tb.sql檔案中。把建立外來鍵、觸發器、試圖的語句單獨放在另一個檔案中，如儲存在create_foreign.sql 儲存方法：從檔案頭檢視foreign 檢視到第一個外來鍵，包括它和下面的所有語句放到create_foreign.sql，前面的放到create_tb.sql。 -----匯入資料庫 4．連線到資料庫 db2 Connect to ncdb 5． 接下來執行建立資料庫物件的檔案 db2 -tvf create_tb.sql 6． 匯入資料 建立完了資料庫的基本物件後，開始匯入資料，進入包含資料檔案的目錄， 作業系統的命令：cd 存放資料檔案的目錄 執行命令： db2move nctest import –io insert -u db2admin -p db2admin
###三十:sqlserver###
====================
SQLServer鎖查詢
====================
--查詢資料庫狀態
select * from sys.databases;
--查詢資料庫狀態
select name,user_access,user_access_desc,
snapshot_isolation_state,snapshot_isolation_state_desc,
is_read_committed_snapshot_on
from sys.databases
---禁用並行（如果報表查詢併發量很小可不禁用）
sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'max degree of parallelism', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
---啟用快照隔離等級
---此調整需要資料庫上無連線
ALTER DATABASE 資料庫名稱 SET READ_COMMITTED_SNAPSHOT ON;
--進入TEST資料庫
use TEST
--檢視鎖和會話資訊
sp_who2
--檢視鎖和會話對應SQL
dbcc inputbuffer(52)
use TEST
sp_who2
dbcc inputbuffer(57)
dbcc inputbuffer(52)
sp_who2
--殺掉阻塞的SQL
kill 52
你好：
檢視附件，問題期間資料庫出現死鎖，SQLServer資料庫在預設配置下很容易出現死鎖，
建議啟用行版本快照隔離，啟動過程如下：
ALTER DATABASE database_name SET READ_COMMITTED_SNAPSHOT ON;
注意：
執行如上一行程式碼的時候，務必確保NC或者WAS中介軟體沒有啟動，資料庫除了執行該行命令的SQL連線之外無任何別的連線，
即SQL SERVER MANAGERMENT STUDIO工具只保留一個SQL視窗執行該行命令，關閉其餘SQL視窗，或者直接透過kill命令殺掉該資料庫上的其他連線；
如果併發操作比較大，可以禁用並行
---禁用並行（如果報表查詢併發量很小可不禁用）
sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'max degree of parallelism', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
如有問題，請加QQ3012898098；
###SQLSERVER
---查詢TOP_SQL
SELECT top 10
(total_elapsed_time / execution_count)/1000 N'平均時間ms'
,total_elapsed_time/1000 N'總花費時間ms'
,total_worker_time/1000 N'所用的CPU總時間ms'
,total_physical_reads N'物理讀取總次數'
,total_logical_reads/execution_count N'每次邏輯讀次數'
,total_logical_reads N'邏輯讀取總次數'
,total_logical_writes N'邏輯寫入總次數'
,execution_count N'執行次數'
,creation_time N'語句編譯時間'
,last_execution_time N'上次執行時間'
,SUBSTRING(
st.text,
(qs.statement_start_offset/2) + 1,
(
(CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2
) + 1
) N'執行語句'
,qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE
SUBSTRING(
st.text,
(qs.statement_start_offset/2) + 1,
(
(CASE statement_end_offset WHEN -1 THEN DATALENGTH(st.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)/2
) + 1
) not like '%fetch%'
ORDER BY total_elapsed_time / execution_count DESC;
###sqlserver備份指令碼
---1 backup.bat
@echo off
set path=%path%;C:\Program Files\Microsoft SQL Server\100\Tools\Binn
set dates=%date% %time%
echo %dates% >> D:\backup_test\logs.txt
echo Sql_Back_Start >> D:\backup_test\logs.txt
echo . >> D:\backup_test\logs.txt
SQLCMD.exe -S 127.0.0.1 -U sa -P Sa123456! -i D:\backup_test\new_dbbackup.sql
set dates=%date% %time%
echo %dates% >> D:\backup_test\logs.txt
echo Sql_Back_Finish >> D:\backup_test\logs.txt
echo . >> D:\backup_test\logs.txt
echo . >> D:\backup_test\logs.txt
forfiles /p "D:\backup_test" /s /m *.bak /d -7 /c "cmd /c del @path"
forfiles /p "D:\backup_test" /s /m *.trn /d -7 /c "cmd /c del @path"
---2.new_dbbackup.sql
DECLARE @name varchar(45)
DECLARE @datetime char(17)
DECLARE @path varchar(255)
DECLARE @bakfile varchar(290)
DECLARE @baklog varchar(290)
set @name='necology20180627'
set @datetime=CONVERT(char(11),getdate(),120) + REPLACE(CONVERT(char(8),getdate(),108),':','')
set @path='D:\backup_test'
set @bakfile=@path+'/'+@name+'_'+@datetime+'.bak'
set @baklog=@path+'/'+@name+'_'+@datetime+'.trn'
backup database @name to disk=@bakfile with name=@name,COMPRESSION
backup log @name to disk=@baklog with name=@name,COMPRESSION
go
---dbcc sqlperf(logspace) ---0.3818648 ---11.051 ---0.1954396
---檢視備份，恢復進度
SELECT DB_NAME(er.[database_id]) [DatabaseName],
er.[command] AS [CommandType],
er.[percent_complete], er.start_time,
CONVERT(DECIMAL(5, 2), er.[percent_complete]) AS [Complete_Percent]
,CONVERT(DECIMAL(38, 2), er.[total_elapsed_time] / 60000.00) AS [ElapsedTime_m]
,CONVERT(DECIMAL(38, 2), er.[estimated_completion_time] / 60000.00) AS [EstimatedCompletionTime_m]
FROM sys.dm_exec_requests AS er
WHERE --DB_NAME(er.[database_id]) in ('xxxx') and
er.[command] in ( 'RESTORE DATABASE' ,'BACKUP DATABASE','BACKUP LOG')
order by er.start_time desc
select text,cpu,*
from sys.sysprocesses a cross apply sys.dm_exec_sql_text(a.sql_handle) st
where status<>'sleeping'
order by a.cpu desc
select text,cpu,spid,physical_io,cpu,login_time,status,cmd
from sys.sysprocesses a cross apply sys.dm_exec_sql_text(a.sql_handle) st
where status<>'sleeping' and text like '%DECLARE @projguid VARCHAR(MAX)%'
order by physical_io desc
---批次kill spid
kill spid1
kill spid2
kill spid3
......
###三十一：mysql###
備份資料庫
rem *******************************backup Start*****************************
@echo off
set SrcDir=c:\edoc\
rem 指定天數
set DaysAgo=2
set PAT="C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqldump.exe"
forfiles /p %SrcDir% /s /m thams_*.sql /d -%DaysAgo% /c "cmd /c del @file"
set "Ymd=%date:~,4%%date:~5,2%%date:~8,2%"
%PAT% --opt -u root --password=ziguangruanjian --default-character-set=utf8 thams > %SrcDir%thams_%Ymd%.sql
@echo on
rem *******************************backup End*****************************
cd C:\Program Files\MySQL\MySQL Server 5.7\bin
mysql.exe -uroot -pziguangruanjian thams_0611 < D:\DB\thams_20180611.sql
mysqldump.exe -uroot -pziguangruanjian thams > D:\DB\thams_20180612.sql
mysqldump.exe -uroot -pziguangruanjian thams table1 > D:\DB\thams_tables1_20180612.sql
恢復資料庫
---方法一
cd C:\Program Files\MySQL\MySQL Server 5.7\bin
create database thams_0611;
mysql.exe -uroot -pziguangruanjian thams_0611 < D:\DB\thams_20180611.sql
---方法二
1、首先建空資料庫
mysql>create database thams_0611;
2、匯入資料庫
（1）選擇資料庫
mysql>use thams_0611;
（2）設定資料庫編碼
mysql>set names utf8;
（3）匯入資料（注意sql檔案的路徑）
mysql> source D:\DB\thams_20180611.sql
SHOW VARIABLES LIKE 'log_%';
show binary logs;
show master logs;
show master status;
SHOW BINLOG EVENTS IN 'mysql-bin.000008';
show binlog events in 'mysql-bin.000009';
show variables like '%expire_logs_days%'; ---檢視日誌過期時間，預設0，即永遠不過期；
set global expire_logs_days=10;---更改過期時間10天，修改後觸發後自動清理，觸發條件有(1.binlog大小超過max_binlog_size 2.手動執行flush logs 3.重新啟動)
flush logs; ---手動切換日誌；
---檢視資料庫大小
select TABLE_SCHEMA,
concat(truncate(sum(data_length) / 1024 / 1024, 2), ' MB') as data_size,
concat(truncate(sum(index_length) / 1024 / 1024, 2), 'MB') as index_size
from information_schema.tables
group by TABLE_SCHEMA
order by data_length desc;
---查詢單個庫中所有表磁碟佔用大小
select TABLE_NAME,
concat(truncate(data_length / 1024 / 1024, 2), ' MB') as data_size,
concat(truncate(index_length / 1024 / 1024, 2), ' MB') as index_size
from information_schema.tables
where TABLE_SCHEMA = 'thams_0613'
group by TABLE_NAME
order by data_length desc;
---查詢單個庫中所有表磁碟佔用大小
select table_name,
(data_length / 1024 / 1024) as data_mb,
(index_length / 1024 / 1024) as index_mb,
((data_length + index_length) / 1024 / 1024) as all_mb,
table_rows
from information_schema.tables
where table_schema = 'thams_0613'
order by data_length desc;
---SHOW DATABASES;
SELECT schema_name FROM information_schema.schemata;
---SHOW TABLES;
SELECT table_name FROM information_schema.tables WHERE table_schema='THAMS_0613';
desc table_name;
mysql客戶端SQLyog連線到mysql伺服器時出現下面的問題：
Error No. 1130
Host '*.*.*.*' is not allowed to connect to this MySQL server
沒有許可權，預設只有localhost許可權，授權如下：
grant all privileges on *.* to 'root'@'%' identified by '密碼';
flush privileges;
---windows 2012 顯示桌面
rundll32.exe shell32.dll,Control_RunDLL desk.cpl,,0
---遠端連線mysql
ERROR 1130 (HY000): Host '192.168.70.249' is not allowed to connect to this MySQL server
mysql> update user set host='%' where user='root';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql備份
[root@cloud3 ~]# crontab -l
*/1 * * * * ntpdate cn.pool.ntp.org >/dev/null
01 01 * * * /dbbackup/mysql/mysql_bak.sh
[root@cloud3 ~]# cat /dbbackup/mysql/mysql_bak.sh
#!/bin/bash
db_user=root
db_pass="******"
db_time=$(date +"%Y_%m_%d_%H_%M_%S")
/usr/local/mysql/bin/mysqldump -u$db_user -p$db_pass opensys --default-character-set=utf8 --opt --single-transaction --flush-logs > /dbbackup/mysql/opensys_bak_$db_time.sql
/usr/local/mysql/bin/mysqldump -u$db_user -p$db_pass 3gol --default-character-set=utf8 --opt --single-transaction --flush-logs > /dbbackup/mysql/3gol_bak_$db_time.sql
/usr/local/mysql/bin/mysqldump -u$db_user -p$db_pass openauth --default-character-set=utf8 --opt --single-transaction --flush-logs > /dbbackup/mysql/openauth_bak_$db_time.sql
/usr/local/mysql/bin/mysqldump -u$db_user -p$db_pass pyqapp --default-character-set=utf8 --opt --single-transaction --flush-logs > /dbbackup/mysql/pyqapp_bak_$db_time.sql
find /dbbackup/mysql -mtime +10 -name "*.sql" -exec rm -rf {} \;
---CentOS下MySQL忘記root密碼解決方法
[root@jumpserver001 mysql]# mysql -uroot -p123
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
# vim /etc/my.cnf
在[mysqld]的段中加上一句：skip-grant-tables
重啟mariadb
[root@jumpserver001 mysql]# systemctl restart mariadb
[root@jumpserver001 mysql]# mysql -uroot
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.60-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
MariaDB [mysql]> update mysql.user set Password=password('1') where User='root';
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0
MariaDB [mysql]> flush privileges ;
Query OK, 0 rows affected (0.00 sec)
MariaDB [mysql]> select user,host,password from user;
+------------+---------------+-------------------------------------------+
| user | host | password |
+------------+---------------+-------------------------------------------+
| root | localhost | *E6CC90B878B948C35E92B003C792C46C58C4AF40 |
| root | jumpserver001 | *E6CC90B878B948C35E92B003C792C46C58C4AF40 |
| root | 127.0.0.1 | *E6CC90B878B948C35E92B003C792C46C58C4AF40 |
| root | ::1 | *E6CC90B878B948C35E92B003C792C46C58C4AF40 |
| jumpserver | 127.0.0.1 | *AE74BF45BD4590B7A8BFE37AE47DAEDD238A3BF8 |
+------------+---------------+-------------------------------------------+
5 rows in set (0.00 sec)
###三十二:AIX###
---檢視作業系統資訊 oslevel -s或bootinfo -r ---檢視補丁資訊 oslevel -rq ---系統補安裝方法 smitty update_all ---檢查當前系統引數及調整 lsattr -El sys0 export.UTF-8 export lsdev -Cc disk bootinfo -s hdisk1 檢視空間 lspv :檢視系統硬碟 lspv hdisk# :檢視硬碟hdisk#的空間分配情況 lsvg :檢視系統VG lsvg vg_name : 檢視VG的空間分配情況 lsvg -l vg_name: 檢視VG中LV分配情況以及對應的檔案系統 df -k :檢視檔案系統使用情況。 bootinfo -K（位數） pmcycles -m（cpu格式） topas：(Network 按 n 鍵可關閉此區域。再按一次 n 鍵就會顯示所有網路介面活動的報告摘要) (如果有多個CPU，按c鍵兩次就可顯示CPU列表。僅按c鍵一次會關閉此區域) (Disk：反映磁碟使用率的狀況的區域，按 d 鍵可關閉這個區域。再按一次 d 鍵就會顯示所有物理磁碟活動的報告摘要) df -g du -g ping -s 20000 192.168.1.1 export JAVA_HOME=../../ nohup /ncapp/home/startup.sh & nohup表示不結束通話，即關閉終端，指令碼依然執行，&表示以後臺方式執行，如果不加前面的nohup，關閉終端，指令碼會結束執行 檢視告警日誌 errpt 命令 set -o vi AIX軟體管理 系統管理介面工具（System Management Interface Tool，SMIT）是一種互動式的應用程式，它幾乎可以用來簡化 AIX? 系統管理中各方面的工作。 #smit 列出軟體包（包括子包）名： # installp -l -d ./X11.base Fileset Name Level I/U Q Content ==================================================================== X11.base.common 5.1.0.0 I N usr # AIXwindows Runtime Common Directories X11.base.lib 5.1.0.0 I N usr # AIXwindows Runtime Libraries X11.base.rte 5.1.0.0 I N usr,root # AIXwindows Runtime Environment X11.base.smt 5.1.0.0 I N usr,root # AIXwindows Runtime Shared Memory Transport 安裝軟體： #installp -d X11.base all （全部安裝） #installp -d X11.base X11.base.common （只安裝其中的X11.base.common） 卸軟體載： #installp -u X11.base.common 列出已安裝的軟體： #lslpp -l 列出安裝的軟體的檔案清單： #lslpp -f openssh.base.client 查詢檔案屬於哪個包： #lslpp -w /usr/bin/ssh File Fileset Type ---------------------------------------------------------------------------- /usr/bin/ssh openssh.base.client File AIX服務命令 列出服務 #lssrc -a （全部） #lssrc -s sshd （sshd狀態） 停止服務 #stopsrc -s sshd 啟動服務 #startsrc -s sshd
###三十三:Linux###
1.1 檢視CPU個數
# 總核數 = 物理CPU個數 X 每顆物理CPU的核數
# 總邏輯CPU數 = 物理CPU個數 X 每顆物理CPU的核數 X 超執行緒數
# 檢視物理CPU個數
cat /proc/cpuinfo| grep "physical id"| sort| uniq| wc -l
# 檢視每個物理CPU中core的個數(即核數)
cat /proc/cpuinfo| grep "cpu cores"| uniq
# 檢視邏輯CPU的個數
cat /proc/cpuinfo| grep "processor"| wc -l
# 檢視CPU型號
# cat /proc/cpuinfo | grep 'model name' |uniq
model name : Intel(R) Xeon(R) CPU E5630 @ 2.53GHz
model name : Intel(R) Xeon(R) CPU E5-2683 v4 @ 2.10GHz
model name : Intel(R) Xeon(R) CPU E5-2609 v4 @ 1.70GHz
[root@bogon ~]# cat /proc/cpuinfo | grep "physical id" | uniq | wc -l
1
[root@bogon ~]# cat /proc/cpuinfo | grep "cpu cores" | uniq
cpu cores : 2
[root@bogon ~]# cat /proc/cpuinfo | grep 'model name' |uniq
model name : Intel(R) Pentium(R) CPU G3220 @ 3.00GHz
Linux檢視物理CPU個數、核數、邏輯CPU個數
# 總核數 = 物理CPU個數 X 每顆物理CPU的核數
# 總邏輯CPU數 = 物理CPU個數 X 每顆物理CPU的核數 X 超執行緒數
# 檢視物理CPU個數
cat /proc/cpuinfo| grep "physical id"| sort| uniq| wc -l
# 檢視每個物理CPU中core的個數(即核數)
cat /proc/cpuinfo| grep "cpu cores"| uniq
# 檢視邏輯CPU的個數
cat /proc/cpuinfo| grep "processor"| wc -l
複製程式碼
檢視CPU資訊（型號）
cat /proc/cpuinfo | grep name | cut -f2 -d: | uniq -c
###查詢CPU
---windows cpu核數,邏輯CPU
wmic---cpu get * ---NumberOfCores(核數),NumberOfLogicalProcessors(邏輯CPU),如果核數=1/2邏輯CPU,是雙核超執行緒
systeminfo ---物理CPU個數
devmgmt.msc
---工作管理員--效能
cat /proc/cpuinfo
dmesg|grep -i 'physical processor'
dmidecode | grep "Product Name"
Intel(R) Atom(TM) CPU N2800 @ 1.86GHz
透過free -m檢視當前記憶體
可用記憶體：Available memory=free+buffers+cached，即31068=759+66+30243
已用記憶體：Used memory=used-buffers-cached，即1030=31339-66-30243
dmidecode | grep "Product Name"
ip addr 和 ifconfig
---更換目錄名稱
/home目錄更改為/kingdee
df -h
umount /dev/mapper/VolGroup-lv_home
mount /dev/mapper/VolGroup-lv_home /kingdee
cat /etc/fstab
---ntp
date
ntpdate ali.yun.org
date
service ip6tables stop #停止IPV6服務
chkconfig ip6tables off #禁止IPV6開機啟動
service yum-updatesd stop #關閉系統自動更新
chkconfig yum-updatesd off #禁止開啟啟動
---centos7
[root@linux-node1 ~]# setenforce 0 # 可以設定配置檔案永久關閉
[root@linux-node1 ~]# systemctl stop firewalld.service
[root@linux-node1 ~]# systemctl disable firewalld.service
[root@linux-node1 ~]# iptables -F
[root@linux-node1 ~]# iptables-save
1、方法一使用hostnamectl命令
更改主機名
[root@bogon ~]# hostnamectl set-hostname NMServer-7.test.com
方法二：修改配置檔案 /etc/hostname 儲存退出
[root@bogon ~]# vi /etc/hostname
---centos6.7
[root@bogon ~]# chkconfig --list iptables
iptables 0:off 1:off 2:on 3:on 4:on 5:on 6:off
[root@bogon ~]# chkconfig iptables off
[root@bogon ~]# chkconfig --list iptables
iptables 0:off 1:off 2:off 3:off 4:off 5:off 6:off
yum install net-tools
yum install -y ntp
無法啟動圖形
yum groupinstall "X Window System"
---yum groupinstall "X Window System" --skip-broken
yum group list
yum -y groupinstall "Server with GUI"
###linux中文亂碼
那麼如何顯示中文呢？
1、系統必須安裝中文語言包才行
# yum -y groupinstall chinese-support
2、僅僅有語言包還不行，我們得設定相應的字符集
## 臨時生效
# export LANG="zh_CN.UTF-8" # 設定為中文
# export LANG="en_US.UTF-8" # 設定為英文，我比較喜歡這樣 export
## 永久生效， 編輯/etc/sysconfig/i18n（最好reboot一下）
LANG="zh_CN.UTF-8"
## 或者，編輯 /etc/profile配置檔案，新增如下一行
export LANG="zh_CN.UTF-8"
---LINUX下安裝nmon
wget
tar -xvfz nmon16e_mpginc.tar.gz
cd nmon16e_mpginc
# 授權執行許可權
chmod +x nmon_x86_64_centos7
# 使nmon在任何地方都能執行
mv nmon_x86_64_centos7 /usr/bin/nmon
###windows_linux 格式問題
windows檔案上傳到linux或unix後格式可能會有問題；
vim xxx.txt
Esc
:
set ff=unix或set fileformat=unxix
Esc
：
wq
回車
###find
find /u01/ora_arch/ -mtime +10 -name "*.dbf" -exec rm -rf {} \;
[oracle@ismorcdb2 ~]$ crontab -l
01 01 * * * /home/oracle/del_arch.sh
[oracle@ismorcdb2 ~]$
[oracle@ismorcdb2 ~]$
[oracle@ismorcdb2 ~]$ cat /home/oracle/del_arch.sh
find /u01/ora_arch/ -mtime +10 -name "*.dbf" -exec rm -rf {} \;
[oracle@ismorcdb1 ~]$ crontab -l
00 03 * * * /home/oracle/backup_shell/expdp_ism.sh
[oracle@ismorcdb1 ~]$ cat /home/oracle/backup_shell/expdp_ism.sh
#!/bin/bash
source ~/.bash_profile
days=`date +%Y%m%d`
expdp ism/ism@pdbism directory=dump_expdp dumpfile=ism_$days.dmp logfile=ism_$days.log schemas=ism
find /u01/dump_expdp/ -mtime +2 -name "*.dmp" -exec rm -rf {} \;
find /u01/dump_expdp/ -mtime +2 -name "*.log" -exec rm -rf {} \;
find /u01/ora_arch/ -mtime +10 -name "*.dbf" -exec rm -rf {} \;
#!/bin/bash
source ~/.bash_profile
days=`date +%Y%m%d`
expdp ism/ism@pdbism directory=dump_expdp dumpfile=ism_$days.dmp logfile=ism_$days.log schemas=ism
find /u01/dump_expdp/ -mtime +5 -name "*.dmp" -exec rm -rf {} \;
find /u01/dump_expdp/ -mtime +5 -name "*.log" -exec rm -rf {} \;
find /u01/ora_arch/ -mtime +10 -name "*.dbf" -exec rm -rf {} \;
###遠端YUM
1. cd /etc/yum.repos.d
2. mv CentOS-Base.repo CentOS-Base.repo.backup
3. wget
4. mv CentOS6-Base-163.repo CentOS-Base.repo
5.yum clean all
CentOS 6
https://blog.csdn.net/weixin_42167759/article/details/81143066
wget -O /etc/yum.repos.d/CentOS-Base.repo
或者
curl -o /etc/yum.repos.d/CentOS-Base.repo
CentOS 7
wget -O /etc/yum.repos.d/CentOS-Base.repo
或者
curl -o /etc/yum.repos.d/CentOS-Base.repo
之後執行yum makecache生成快取
[root@Oracle11g /]# mount /dev/sr1 /mnt -o loop
[root@Oracle11g /]# mount |grep mnt
/dev/sr1 on /mnt type iso9660 (ro)
/dev/sr1 on /mnt type iso9660 (ro,loop=/dev/loop0)
[root@Oracle11g ~]# cd /etc/yum.repos.d/
[root@Oracle11g yum.repos.d]# cat yum.repo
[Oralin6u3]
name=local yum
baseurl=file:///mnt
gpgcheck=0
enabled=1
###網路卡配置
[root@cjcos01 network-scripts]# cat ifcfg-enp0s3
TYPE="Ethernet"
PROXY_METHOD="none"
BROWSER_ONLY="no"
BOOTPROTO="none"
DEFROUTE="yes"
IPV4_FAILURE_FATAL="no"
IPV6INIT="yes"
IPV6_AUTOCONF="yes"
IPV6_DEFROUTE="yes"
IPV6_FAILURE_FATAL="no"
IPV6_ADDR_GEN_MODE="stable-privacy"
NAME="enp0s3"
UUID="b0b58151-2738-4a9b-8e49-30341e577a60"
DEVICE="enp0s3"
>
IPADDR="192.168.31.90"
PREFIX="24"
GATEWAY="192.168.31.1"
#GATEWAY="192.168.31.254"
DNS1="8.8.8.8"
#DNS1="192.168.31.1"
IPV6_PRIVACY="no"
###windows 禁ping
進入伺服器後 點選 開始——執行 輸入命令：
netsh firewall set icmpsetting 8
這樣就可以在外部ping到伺服器了 非常簡單實用！
同樣道理，如果想禁止Ping，那執行如下命令即可實現：
netsh firewall set icmpsetting 8 disable
###chenjuchao 20210904 23:00###
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29785807/viewspace-2790430/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 前端亂燉前端
- 前端動畫大亂燉前端動畫
- 亂燉“簡書交友”資料之程式碼（1）
- 亂燉“簡書交友”資料之程式碼（2）：關鍵詞抽取、Word2Vec詞向量
- Oracle EBS DBA 常用命令Oracle
- MySQL DBA 管理常用命令MySql
- Oracle DBA常用命令 [ 轉載]Oracle
- #新年覺醒Final#《程式設計師的思維修煉》: 一點亂燉程式設計師
- 【PDB】DBA常用命令之Oracle12.2+ cdb資料庫統一檢查Oracle資料庫
- Coursera北大《資料結構基礎》之概論資料結構
- 北大FTP，除了北大FTP其他好用的FTP軟體FTP
- redis之常用命令Redis
- Oracle之dba_profiles解析Oracle
- Docker初探之常用命令Docker
- Docker之常用命令（二）Docker
- linux之常用命令Linux
- 3、Git之常用命令Git
- JavaScript專題之亂序JavaScript
- mysql 亂碼為之奈何?MySql
- Cook Cookie, 我把 SameSite 給你燉爛了Cookie
- hbase之 Hbase shell 常用命令
- hadoop HDFS之常用命令Hadoop
- JavaWeb開發之PrintWriter亂碼JavaWeb
- 資料亂碼（之.net加密）加密
- 【亂】亂，亂，亂，android真亂！Android
- dba_tables.blocks、dba_tables.empty_blocks和dba_segments.blocks之間是什麼關係BloC
- dba_segments之segment_type='TEMPORARY'
- 初倪人工之智慧殺手鐧，北大院士打破資訊孤島
- Kubernetes之kubectl常用命令
- Linux常用命令之vim操作Linux
- 【vim】vim 常用命令之工作速查
- KVM之五：KVM日常管理常用命令
- Linux常用命令之ifconfigLinux
- 阿里持續投入文娛，“細火慢燉”的卡位戰阿里
- 理“ Druid 後設資料”之亂UI
- 容器技術之Docker常用命令說明Docker
- Linux 常用命令之 top 命令詳解Linux
- Maven筆記之核心概念及常用命令Maven筆記