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;

热门工具换一换