Oracle日常运维2

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/jing_flower/article/details/78789735
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;

 

没有更多推荐了,返回首页