Oracle - 厘米晴空

Database Oracle各个命中率参数的总结及调优

关于Oracle中各个命中率的计算以及相关的调优 1、Library Cache的命中率

SELECT SUM(pinhits)/sum(pins)  FROM V$LIBRARYCACHE;

1	SELECT SUM(pinhits)/sum(pins) FROM V$LIBRARYCACHE;

通常在98%以上，否则，需要要考虑加大共享池，绑定变量，修改cursor_sharing等参数 2、计 …

Database Oracle调优修改10g，11g内存分配

先查看数据库是否使用spfile启动，如果不是的话，只需要在pfile中修改，然后重启数据库即可

SQL> show parameter spfile;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string D:\APP\ADMINISTRATOR\PRODUCT\1
1.2.0\DBHOME_1\DATABASE\SPFILE
CME.ORA

1

2

3

4

5

6

7

SQL> show parameter spfile;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

spfile string D:\APP\ADMINISTRATOR\PRODUCT\1

1.2.0\DBHOME_1\DATABASE\SPFILE

CME.ORA

Oracle 10g中实现了SGA的自动分配（服务器内存10G） [crayon-5f651904 …

Database Oracle自动备份脚本 [ Windows]

@ECHO OFF
set ora_path=G:\oracle\product\10.2.0\db_1\BIN
set ORACLE_SID=orcl
set bakfile_path=G:\backup\oradmp

del G:\backup\myfile.txt
del G:\backup\myfile1.txt
date /t >>G:\backup\myfile.txt
time /t >>G:\backup\myfile1.txt
for /F "eol=; tokens=1 delims=/ " %%i in (G:\backup\myfile.txt) do set outp1=%%i
for /F "eol=; tokens=2 delims=/ " %%i in (G:\backup\myfile.txt) do set outp2=%%i
for /F "eol=; tokens=3 delims=/ " %%i in (G:\backup\myfile.txt) do set outp3=%%i
for /F "eol=; tokens=1 delims=: " %%i in (G:\backup\myfile1.txt) do set outp4=%%i
for /F "eol=; tokens=2 delims=: " %%i in (G:\backup\myfile1.txt) do set outp5=%%i
set outp6=20_full_%outp1%-%outp2%-%outp3%_%outp4%_%outp5%
set DaysAgo=2
forfiles /p %bakfile_path% /s /m *.dmp /d -%DaysAgo% /c "cmd /c del @path" 
forfiles /p %bakfile_path% /s /m *.log /d -%DaysAgo% /c "cmd /c del @path" 


%ora_path%\expdp.exe system/****** directory=expdp_dir2 dumpfile=%outp6%_%%U.dmp filesize=8192M logfile=%outp6%.log parallel=1 full=y
copy %bakfile_path%\%outp6%.log G:\backup\log

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

@ECHO OFF

set ora_path=G:\oracle\product\10.2.0\db_1\BIN

set ORACLE_SID=orcl

set bakfile_path=G:\backup\oradmp

del G:\backup\myfile.txt

del G:\backup\myfile1.txt

date /t >>G:\backup\myfile.txt

time /t >>G:\backup\myfile1.txt

for /F "eol=; tokens=1 delims=/ " %%i in (G:\backup\myfile.txt) do set outp1=%%i

for /F "eol=; tokens=2 delims=/ " %%i in (G:\backup\myfile.txt) do set outp2=%%i

for /F "eol=; tokens=3 delims=/ " %%i in (G:\backup\myfile.txt) do set outp3=%%i

for /F "eol=; tokens=1 delims=: " %%i in (G:\backup\myfile1.txt) do set outp4=%%i

for /F "eol=; tokens=2 delims=: " %%i in (G:\backup\myfile1.txt) do set outp5=%%i

set outp6=20_full_%outp1%-%outp2%-%outp3%_%outp4%_%outp5%

set DaysAgo=2

forfiles /p %bakfile_path% /s /m *.dmp /d -%DaysAgo% /c "cmd /c del @path"

forfiles /p %bakfile_path% /s /m *.log /d -%DaysAgo% /c "cmd /c del @path"

%ora_path%\expdp.exe system/****** directory=expdp_dir2 dumpfile=%outp6%_%%U.dmp filesize=8192M logfile=%outp6%.log parallel=1 full=y

copy %bakfile_path%\%outp6%.log G:\backup\log

Database oracle10g无法访问WEB Enterprise Manager

问题访问 http://IP:1158/em/console 报错： 503 Service Unavailable Service is not initialized correctly. The Em Key is not configured properly. Run "emctl status emkey" for more details. 解决 …

Database ORACLE！！关于“ORA-01653:表xxx无法通过8(在表空间SYSTEM)扩展

问题描述使用expdp导出数据，报ORA-01691表空间不足错误 WTF！！导出怎么会报表空间不足？导出还会占用表空间大小吗？问题排查及解决亲自开着挖掘机上百度一顿挖。。。。。。查看SYSTEM表空间使用率 …

Database Oracle AWR报告生成方法

1、登录Oracle程序所在的服务器，查找出awrrpt.sql文件所在位置

D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\awrrpt.sql

1	D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\awrrpt.sql

2、登录Oracle，以sysdba身份连接 3、执行命令

@D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\awrrpt.sql

1	@D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\awrrpt.sql

4、输入report_type …

Database 记一次Oracle数据库导入出现“致命错误”终止导入

问题描述环境： Windows Server 2008 R2 Oracle 10.2.0.5.0 导出命令

expdp system/******** directory=expdp_dir1 dumpfile=17_full_2017-12-14_12_00_%U.dmp filesize=8192M logfile=17_full_2017-12-14_12_00.log parallel=1 full=y

1	expdp system/******** directory=expdp_dir1 dumpfile=17_full_2017-12-14_12_00_%U.dmp filesize=8192M logfile=17_full_2017-12-14_12_00.log parallel=1 full=y

导入命令

impdp system/****** directory=data_pump_dir dumpfile=17_full_2017-12-14_12_00_%U.dmp logfile=17_full_2017-12-14_12_00.log full=y

1	impdp system/****** directory=data_pump_dir dumpfile=17_full_2017-12-14_12_00_%U.dmp logfile=17_full_2017-12-14_12_00.log full=y

导入过程中出现以下错误：最终终止日志：解决方法 …

Database Windows下实现Oracle定时备份（bat + 计划任务）

说明关于Windows下oracle全量备份的文章，很多网站都有，写下本文不过是为了以后方便查找，不用再到处找。 BAT脚本新建txt文档，把脚本复制进去后改文件格式为bat文件，双击运行即可测试该脚本是否有误 & …

Database Oracle查看表空间大小和使用率

1. 全部表空间的大小

select tablespace_name, sum(bytes)/1024/1024 from dba_data_files group by tablespace_name;

TABLESPACE_NAME      SUM(BYTES)/1024/1024
-------------------- --------------------
UNDOTBS1                     65
SYSAUX                      520
USERS                        6
SYSTEM                      680
EXAMPLE                     100

1

2

3

4

5

6

7

8

9

select tablespace_name, sum(bytes)/1024/1024 from dba_data_files group by tablespace_name;

TABLESPACE_NAME SUM(BYTES)/1024/1024

-------------------- --------------------

UNDOTBS1 65

SYSAUX 520

USERS 6

SYSTEM 680

EXAMPLE 100

2. 空闲表空间大小

select tablespace_name, sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;

TABLESPACE_NAME      SUM(BYTES)/1024/1024
-------------------- --------------------
SYSAUX                       34
UNDOTBS1                     1
USERS                        1
SYSTEM                       4
EXAMPLE                      22

1

2

3

4

5

6

7

8

9

select tablespace_name, sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;

TABLESPACE_NAME SUM(BYTES)/1024/1024

-------------------- --------------------

SYSAUX 34

UNDOTBS1 1

USERS 1

SYSTEM 4

EXAMPLE 22

3. 已使用空间可以这样计算

select a.tablespace_name, total, free, total-free as used from 
(select tablespace_name, sum(bytes)/1024/1024 as total from dba_data_files group by tablespace_name) a, 
(select tablespace_name, sum(bytes)/1024/1024 as free from dba_free_space group by tablespace_name) b
where a.tablespace_name = b.tablespace_name;

TABLESPACE_NAME       TOTAL       FREE     USED
-------------------- ---------- ---------- ----------
SYSAUX                520    33.6875   486.3125
UNDOTBS1              65      1           64
USERS                 6.25     1.25        5
SYSTEM                680        3.5    676.5
EXAMPLE               100    21.5625    78.4375

1

2

3

4

5

6

7

8

9

10

11

12

select a.tablespace_name, total, free, total-free as used from

(select tablespace_name, sum(bytes)/1024/1024 as total from dba_data_files group by tablespace_name) a,

(select tablespace_name, sum(bytes)/1024/1024 as free from dba_free_space group by tablespace_name) b

where a.tablespace_name = b.tablespace_name;

TABLESPACE_NAME TOTAL FREE USED

-------------------- ---------- ---------- ----------

SYSAUX 520 33.6875 486.3125

UNDOTBS1 65 1 64

USERS 6.25 1.25 5

SYSTEM 680 3.5 676.5

EXAMPLE 100 21.5625 78.4375

Database Oracle汉字乱码问题原因及解决方法

原因出现中文乱码的主要原因是字符集不同。在Oracle中，我们关心三个地方的字符集： 1、Oracle服务器内部的字符集这是Oracle数据库存储数据使用的字符集。在Oracle中可能使用 [crayon-5f651904e876c583 …

Database Oracle各个命中率参数的总结及调优

Database Oracle调优修改10g，11g内存分配

Database Oracle自动备份脚本 [ Windows]

Database oracle10g无法访问WEB Enterprise Manager

Database ORACLE！！关于“ORA-01653:表xxx无法通过8(在表空间SYSTEM)扩展

Database Oracle AWR报告生成方法

Database 记一次Oracle数据库导入出现“致命错误”终止导入

Database Windows下实现Oracle定时备份（bat + 计划任务）

Database Oracle查看表空间大小和使用率

Database Oracle汉字乱码问题原因及解决方法

Ceph RBD 存储服务集群部署

微服务API网关 Kong On Docker

微服务API网关-Kong

微服务API网关 Kong On CentOS

使用consul做服务注册

Python获取zabbix告警性能监控图并邮件发送

当电脑死机的时候，电脑都在干嘛

解决WordPress QQ昵称无法获取的问题

博客微信小程序发布记录

Kubernetes（Pod, Deployment, ReplicaSet,Service）之间的关系