Oracle日常运维2

Oracle日常运维2

1.Oracle字符集

select userenv('language') from dual;

查看charset可用字符集
select * from V$NLS_VALID_VALUES where parameter='CHARACTERSET' and value like '%UTF%'

修改数据库字符集
将数据库启动到RESTRICTED模式下做字符集更改: 

SQL> shutdown immediate; 
SQL> startup mount 
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION; 

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0; 

SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0; 

SQL> ALTER DATABASE OPEN;
-------------------------------------------------------------
SQL> ALTER DATABASE CHARACTER SET UTF8; 
ALTER DATABASE CHARACTER SET ZHS16GBK 
* 
ERROR at line 1: 
ORA-12712: new character set must be a superset of old character set 

 提示我们的字符集:新字符集必须为旧字符集的超集,这时我们可以跳过超集的检查做更改:
>alter database character set internal_use utf8;(这条命令是强制转换编码格式,有可能会导致数据库中的中文变成乱码)
---------------------------------------------------------------------
SQL> alter database character set internal_use utf8;

SQL> shutdown immediate
SQL> startup 
SQL> select * from v$nls_parameters; 

2.查看share pool分布
SQL> show parameter cursor_space_for_time;

NAME                 TYPE    VALUE
------------------------------------ ----------- ------------------------------
cursor_space_for_time        boolean   FALSE
SQL> select sum(bytes)/1024/1024 mb from v$sgastat where pool='shared pool'; 

    MB
----------
   2320

SQL> SELECT KSMCHCLS CLASS, COUNT(KSMCHCLS) NUM, SUM(KSMCHSIZ)  
  SIZ,    
  To_char( ((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)),'999,999.00')||'k' "AVG SIZE"    
   FROM X$KSMSP GROUP BY KSMCHCLS; 

CLASS      NUM    SIZ AVG SIZE
-------- ---------- ---------- ------------
R-freea     425  53157888   122.15k
freeabl    27308 840526864    30.06k
R-free      96  67612688   687.79k
recr     10803  21662584    1.96k
R-perm      9 143498904  15,570.63k
perm      1337 1301541936   950.66k
free      5064  4682696     .90k

7 rows selected.

SQL> SELECT KSMCHIDX,KSMCHDUR, KSMCHCLS CLASS, COUNT(KSMCHCLS) NUM, SUM(KSMCHSIZ)  
  SIZ,    
  To_char( ((SUM(KSMCHSIZ)/C  OUNT(KSMCHCLS)/1024)),'999,999.00')||'k' "AVG SIZE"   4 
    FROM X$KSMSP GROUP BY KSMCHIDX,KSMCHDUR, KSMCHCLS 
    order by 1,2,3;  

 KSMCHIDX  KSMCHDUR CLASS      NUM    SIZ AVG SIZE
---------- ---------- -------- ---------- ---------- ------------
     1     1 R-free      49  41981760   836.69k
     1     1 R-freea     217  28223736   127.01k
     1     1 R-perm      3  49324752  16,056.23k
     1     1 free      2377   782160     .32k
     1     1 freeabl    16517 512606184    30.31k
     1     1 perm      805 798596232   968.79k
     1     1 recr      5903  11318184    1.87k
     2     1 R-free      47  25642064   532.79k
     2     1 R-freea     207  24923016   117.58k
     2     1 R-perm      6  94174152  15,327.82k
     2     1 free      2936  1457368     .48k

 KSMCHIDX  KSMCHDUR CLASS      NUM    SIZ AVG SIZE
---------- ---------- -------- ---------- ---------- ------------
     2     1 freeabl    10785 329278080    29.82k
     2     1 perm      532 502939264   923.22k
     2     1 recr      5458  11436608    2.05k

14 rows selected.

3.查看用户权限
角色权限/系统权限/用户权限

SQL> select * from dba_sys_privs where GRANTEE='TEST';

GRANTEE            PRIVILEGE                ADM
------------------------------ ---------------------------------------- ---
TEST            CREATE PUBLIC DATABASE LINK       NO
TEST            CREATE TYPE               NO
TEST            CREATE PROCEDURE             NO
TEST            CREATE DATABASE LINK           NO
TEST            CREATE SESSION              NO
TEST            CREATE SEQUENCE             NO
TEST            SELECT ANY TABLE             NO
TEST            CREATE VIEW               NO
TEST            CREATE TABLE               NO

9 rows selected.

4. package执行权限
grant execute ANY PROCEDURE to test; 
GRANT debug any procedure, debug connect session TO test;

5.获取表定义语句
select dbms_metadata.get_ddl('TABLE','WAR','DATA') from dual;

6.查看oracle历史执行计划
dba_hist_active_sess_history

7.vi替换 
 :%s/vivian/sky/(等同于 :g/vivian/s//sky/) 替换每一行的第一个 vivian 为 sky
 :%s/vivian/sky/g(等同于 :g/vivian/s//sky/g) 替换每一行中所有 vivian 为 sky

8.notepad++替换空行
^\s*\n
9.收集表的统计信息
BEGIN 
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME =>'xxx', TABNAME =>'xxx', ESTIMATE_PERCENT => 100, METHOD_OPT => 'FOR ALL COLUMNS SIZE REPEAT', DEGREE => 4, GRANULARITY => 'ALL', CASCADE => TRUE, NO_INVALIDATE => FALSE); 
END; 
/ 
DECLARE 
V_COUNT NUMBER; 
BEGIN 
Select Num_Rows Into v_Count From Dba_Tables Where Owner = 'xxx' And Table_Name ='xxx'; 
If v_Count = 0 Then 
Dbms_Stats.Delete_Table_Stats(Ownname     =>'xxx', Tabname     =>'xxx', Cascade_Columns => True, Cascade_Indexes => True); 
End If; 
End; 
/


10.导数报错


exec dbms_metadata_util.load_stylesheets; 


ORA-39006: internal error
ORA-39213: Metadata processing is not available



select * from DBA_DATAPUMP_JOBS;


11.exp
exp devmgr/ptmjygb8 file=/路径/文件名.dmp log=/路径/文件名_exp.log buffer=102400000 owner=用户列表 ROWS=n 


userid='/ as sysdba'
file=/paic/app/oracle/rdbms/os11g/cj/exp_pagi.dmp
log=exppshrm.log
owner=
full=y
rows=n


12. Connect AS SYSDBA only until resolved 归档满了


--看看archiv log所在位置
SQL > show parameter log_archive_dest;


-- 一般VALUE为空时,可以用archive log list;检查一下归档目录和log sequence
SQL > archive log list;
Database log mode       Archive Mode
Automatic archival       Enabled
Archive destination      USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence   1170
Next log sequence to archive  1170
Current log sequence      1181


-- 检查flash recovery area的使用情况,可以看见archivelog已经很大了,达到96.62
SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;
FILE_TYPE        PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES   CON_ID
ARCHIVED LOG             99.74             0       669     0


-- 计算flash recovery area已经占用的空间
SQL > select sum(percent_space_used)*3/100 from v$flash_recovery_area_usage;


SUM(PERCENT_SPACE_USED)*3/100
-----------------------------
            2.9922
  
  
-- 查看归档路径
SQL > show parameter recover;


-- 注意: 在删除归档日志后,必须用RMAN维护控制文件,否则空间显示仍然不释放。
rman target /
-- 检查一些无用的archivelog
RMAN> crosscheck archivelog all;


-- 删除过期归档
RMAN> delete expired archivelog all;


SQL > select * from V$FLASH_RECOVERY_AREA_USAGE;


13. 查看数据库主机状态,是否异常重启
/etc/messages


查看主机物理配置
/opt/cgtools/cginfo -t perf -s cpu


查看物理机的逻辑CPU:[STG-T3RCFBPROD_C00:cnsh281145:5502:M ~]$cat /proc/cpuinfo |grep process|wc -l
40


查看实例分配了多少内存和cpu:  
  
-- 查看归档路径
SQL > show parameter recover;


-- 注意: 在删除归档日志后,必须用RMAN维护控制文件,否则空间显示仍然不释放。
rman target /
-- 检查一些无用的archivelog
RMAN> crosscheck archivelog all;


-- 删除过期归档
RMAN> delete expired archivelog all;


SQL > select * from V$FLASH_RECOVERY_AREA_USAGE;


13. 查看数据库主机状态,是否异常重启
/etc/messages


查看主机物理配置
/opt/cgtools/cginfo -t perf -s cpu


查看物理机的逻辑CPU:[STG-T3RCFBPROD_C00:cnsh281145:5502:M ~]$cat /proc/cpuinfo |grep process|wc -l
40


查看实例分配了多少内存和cpu:
SQL >$dbs|grep 5916


查看CPU频率:
cat /proc/cpuinfo


# 总核数 = 物理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


14. 数据库慢


查看等待事件
SELECT * 
 FROM V$SESSION 
 WHERE USERNAME IS NOT NULL 
  AND STATUS = 'ACTIVE' 
 ORDER BY LOGON_TIME, SID; 


log file scan:IO较慢


SELECT * FROM V$SESSION WHERE STATUS = upper('active') AND sql_id='6stjrb05x7ysk';
SELECT * FROM v$session WHERE sid=178
SELECT * FROM v$session s,v$sql WHERE s.sql_id='c429amg9j90af'




查询会话等待与其对应的sql
SELECT P.PID,
    S.SID,
    S.SERIAL#,
    S.USERNAME,
    Q.SQL_ID,
    Q.SQL_TEXT,
    Q.SQL_FULLTEXT,
    W.EVENT,
    W.WAIT_TIME,
    W.STATE,
    CASE
     WHEN W.STATE = 'WAITING' THEN
     W.SECONDS_IN_WAIT
     WHEN W.STATE = 'WAITING KNOWN TIME' THEN
     W.WAIT_TIME
    END AS SEC_IN_WAIT
 FROM V$SESSION S, V$SESSION_WAIT W, V$SQLAREA Q, V$PROCESS P
 WHERE S.SID = W.SID
  AND S.SQL_ID = Q.SQL_ID
  AND P.ADDR = S.PADDR
  AND W.EVENT NOT LIKE 'SQL*Net%'
  AND S.USERNAME IS NOT NULL
  AND W.WAIT_TIME >= 0
 ORDER BY W.SECONDS_IN_WAIT DESC;
 
查询被阻塞会话与被阻塞会话的对应sql
SELECT S1.USERNAME "WAITING USER", 
    S1.OSUSER  "OS User" 
     
    , 
    S1.LOGON_TIME "logon time", 
    W.SESSION_ID "Sid", 
    P1.SPID    "PID", 
    Q1.SQL_TEXT  "SQLTEXT", 
    S2.USERNAME  "HOLDING User", 
    S2.OSUSER   "OS User" 
     
    , 
    S2.LOGON_TIME "logon time", 
    H.SESSION_ID "Sid", 
    P2.SPID    "PID", 
    Q2.SQL_TEXT  "SQLTEXT" 
 FROM SYS.V_$PROCESS P1, 
    SYS.V_$PROCESS P2, 
    SYS.V_$SESSION S1, 
    SYS.V_$SESSION S2, 
    DBA_LOCKS   W, 
    DBA_LOCKS   H, 
    V$SQL     Q1, 
    V$SQL     Q2 
 WHERE H.MODE_HELD != 'None' 
  AND H.MODE_HELD != 'Null' 
  AND W.MODE_REQUESTED != 'None' 
  AND W.LOCK_TYPE(+) = H.LOCK_TYPE 
  AND W.LOCK_ID1(+) = H.LOCK_ID1 
  AND W.LOCK_ID2(+) = H.LOCK_ID2 
  AND W.SESSION_ID = S1.SID(+) 
  AND H.SESSION_ID = S2.SID(+) 
  AND S1.PADDR = P1.ADDR(+) 
  AND S2.PADDR = P2.ADDR(+) 
  AND S1.SQL_ID = Q1.SQL_ID(+) 
  AND S2.SQL_ID = Q2.SQL_ID(+) 
 ORDER BY H.SESSION_ID; 
 
多长时间跑一次?执行时间?
 

13.查看执行计划变更情况
select a.INSTANCE_NUMBER,a.snap_id,a.sql_id,a.plan_hash_value,b.begin_interval_time
from dba_hist_sqlstat a, dba_hist_snapshot b 
where sql_id ='bjwtqc2vq84xp' 
and a.snap_id = b.snap_id 
order by instance_number, snap_id;
14.--redo
select *
 from (select m.sid, round(m.value / 1024 / 1024 / 1024, 6) G
     from v$sesstat m, v$statname s
    where m.statistic# = s.statistic#
     and s.name = 'redo size'
    order by m.value desc)
whererownum < 100;