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;