没有合适的资源?快使用搜索试试~ 我知道了~
首页Oracle学习手册:全面详细教程
"Oracle系统全面学习教程很详细的讲解" Oracle数据库是一种广泛应用的关系型数据库管理系统,尤其在企业级数据存储和管理中占据重要地位。本教程详细介绍了Oracle的学习过程,旨在为初学者提供一个全面且深入的指导。教程由作者在长期学习和实践的基础上整理而成,包含了个人博客中的诸多经验和心得。 教程内容涵盖了Oracle的基础知识,如Oracle OLAP(联机分析处理)与OLTP(联机事务处理)的介绍。OLTP是数据库系统的核心应用,主要用于处理日常的、频繁的事务操作,例如银行交易,强调系统的响应速度和并发处理能力。而OLAP则是针对数据仓库系统,用于复杂的数据分析和决策支持,更注重磁盘I/O和SQL执行效率。 在教程中,作者还指出了OLTP与OLAP的对比,帮助读者理解两者在应用场景、性能指标和优化策略上的差异。此外,教程可能还涉及了Oracle的安装配置、SQL语法、表和索引的创建与管理、存储结构、备份恢复、性能调优、安全性设置等多个方面的内容。 由于作者持续在个人博客上更新文档,教程内容可能随着新的学习和发现而不断丰富和完善。作者鼓励读者在发现错误或遗漏时通过邮件进行反馈,以便及时更新文档,确保信息的准确性和时效性。 此教程适合那些刚开始接触Oracle或者希望深入学习数据库管理的人员。它不仅提供了理论知识,还结合实践经验,使得学习更为实用。虽然在排版上可能存在不足,但其详尽的内容对于初学者来说仍然具有很高的参考价值。通过学习本教程,读者可以逐步掌握Oracle数据库的基本操作和高级特性,为进一步提升数据库管理技能打下坚实基础。
资源详情
资源推荐
基于 SCN ...................................................................................................................................531
基于日志序列.............................................................................................................................531
非
catalog
下完全恢复
................................................................................................531
前滚(ROLL FORWORD)和回滚(ROLL BACK) ............................................................................532
什么时候需要实例恢复
.................................................................................................532
Clean shutdown 时 ......................................................................................................................532
非正常 shutdown.......................................................................................................................533
crash recovery 顺序问题.............................................................................................................533
Crash Recovery
过程
......................................................................................................533
为什么数据库的实例恢复是先前滚再回滚
.....................................................................
534
三. RMAN ......................................................................................................................................534
四. DATA GUARD ............................................................................................................................537
五. RAC..........................................................................................................................................731
集群环境下的一些特殊问题.....................................................................................................731
并发控制
.......................................................................................................................731
健忘症
(Amnesia)
.................................................................................................................
731
脑裂
(Split Brain).................................................................................................................731
IO
隔离
(Fencing).............................................................................................................732
RAC 并发 .................................................................................................................................732
GRD
(
Global Resource Directory
)
..............................................................................733
RAC 架构 ....................................................................................................................................733
SGA
的变化
...................................................................................................................733
后台进程的变化
............................................................................................................733
LMSn.........................................................................................................................................734
LMD ..........................................................................................................................................734
LCK ............................................................................................................................................734
LMON........................................................................................................................................734
DIAG.............................................................................................................................................734
GSD ..............................................................................................................................................734
文
件
..............................................................................................................................734
spfile ............................................................................................................................................735
Redo Thread.................................................................................................................................735
Archived Log.................................................................................................................................735
1) 使用 NFS.......................................................................................................................................735
2) 实例间归档(CIA: Cross Instance Archive)................................................................................736
3) 使用 ASM .....................................................................................................................................736
Undo Tablespace..........................................................................................................................736
SCN(System Change Number) .............................................................................................736
Cache Fusion
,
GCS
,
GES
关系
.....................................................................................736
RAC 集群 ....................................................................................................................................737
Clusterware.........................................................................................................................737
Clusterware
组成
...........................................................................................................737
磁盘文件................................................................................................................................737
Clusterware 后台进程............................................................................................................738
VIP
原理和特点
.............................................................................................................740
Clusterware
的日志体系
...............................................................................................741
RAC FAILOVER .............................................................................................................................................................742
Client-Side Connect Time Failover....................................................................................742
Client-Side TAF(Transparent Application Failover)............................................................743
Client-Side TAF
配置示例
...............................................................................................744
主机信息如下..............................................................................................................................744
Node1 配置...................................................................................................................................744
NODE2 配置..................................................................................................................................745
在 node1 和 node2 的 tnsnames.ora 文件添加如下内容..........................................................745
在所有节点设置 remote listener ...............................................................................................746
在客户端设置的 tnsnames.ora 设置 TAF ..................................................................................746
Service-Side TAF...............................................................................................................747
用 DBCA 配置 Service ...............................................................................................................747
用 srvctl 命令配置 Service........................................................................................................748
配置 Service 的注意事项...........................................................................................................749
5.5.5 Service-Side TAF
手工配置示例
..................................................................................749
RAC LOADBALANCE.....................................................................................................................................................754
Connection Balancing..........................................................................................................754
客户端均衡(Client-Side LB) ...................................................................................................755
服务器端均衡(Server-Side LB) ..............................................................................................755
两种 LB 的配置方法 ..................................................................................................................757
利用
Service
分散负载
..................................................................................................757
RAC 启动和关闭 ........................................................................................................................759
检查共享设备
...............................................................................................................759
如果使用 ocfs2 的,检查 ocfs2 状态.......................................................................................759
如果使用 raw device.................................................................................................................759
检查 ASM ..................................................................................................................................759
自动启动
RAC
并检查相关进程
..................................................................................759
手动启动
RAC
..................................................................................................................762
ORACLE RAC 常用维护工具和命令............................................................................................764
节点层
............................................................................................................................764
网络层
............................................................................................................................764
集群层
............................................................................................................................766
CRSCTL .........................................................................................................................................766
检查 CRS 状态....................................................................................................................767
配置 CRS 栈是否自启动....................................................................................................767
启动,停止 CRS 栈............................................................................................................768
查看 Votedisk 磁盘位置....................................................................................................768
查看和修改 CRS 参数........................................................................................................768
跟踪 CRS 模块,提供辅助功能........................................................................................768
维护 Voting disk .................................................................................................................770
OCR 命令系列 ..............................................................................................................................770
ocrdump ..............................................................................................................................771
ocrcheck ..............................................................................................................................771
ocrconfig..............................................................................................................................772
使用 ocrconfig 导出,导入进行备份和恢复...................................................................772
移动 OCR 文件位置...........................................................................................................774
应用层
...........................................................................................................................775
crs_stat ........................................................................................................................................775
onsctl ...........................................................................................................................................777
ONS 配置内容.....................................................................................................................777
配置 ONS............................................................................................................................778
onsctl 命令 ..........................................................................................................................778
srvctl ............................................................................................................................................780
使用 config 查看配置.........................................................................................................781
使用 add 添加对象............................................................................................................782
使用 enable/disable 启动,禁用对象..............................................................................783
使用 remove 删除对象......................................................................................................784
启动,停止对象与查看对象.............................................................................................784
跟踪 srvctl..........................................................................................................................785
初始化 OCR 和 Voting Disk .......................................................................................................785
官网的命令说明.........................................................................................................................787
crs_stat 完整显示进程名脚本....................................................................................................789
六. 常用脚本.......................................................................................................................................1206
查看表空间使用率...................................................................................................................1206
获取当前 TRACE 文件路径脚本................................................................................................1207
发送邮件的存储过程...............................................................................................................1207
AWR 自动收集并发送邮箱 PYTHON 脚本.................................................................................1216
准备工作
......................................................................................................................
1216
生成
AWR
报告
SQL
脚本
...........................................................................................1221
自动上传
AWR
的
Python
脚本
...................................................................................1222
将
Python
添加到
crontab..............................................................................................1224
查看 SEGMENT 中 BLOCK 的存储信息........................................................................................1224
ORACLE 用 SQLPROMPT 修改 SQLPLUS 提示符.............................................................................1227
方法一:使用定义的变量
............................................................................................1228
方法二:使用
sql
语句拼接
......................................................................................1229
七. 性能优化.......................................................................................................................................1242
软解析 和 硬解析 ...................................................................................................................1242
SQL
解析过程
...........................................................................................................1242
解析过程详解
............................................................................................................1242
语法检测...................................................................................................................................1242
语义检查...................................................................................................................................1242
解析(Parse)...............................................................................................................................1243
Parse 主要分为两种:......................................................................................................1243
解析的两个步骤...............................................................................................................1243
执行 sql ....................................................................................................................................1245
绑定变量...................................................................................................................................1245
Band Variable
说明
...................................................................................................1245
OLAP
和
OLTP
系统中的绑定变量
...........................................................................1246
Bind peaking ..................................................................................................................1247
绑定变量 使用示例
.................................................................................................1248
CBO 和 RBO .............................................................................................................................1250
RBO
基于规则的优化器
..............................................................................................1250
CBO
基于成本的优化器
...............................................................................................1251
CBO 说明 ...................................................................................................................................1251
优化器模式...............................................................................................................................1252
修改 CBO 模式的三种方法..............................................................................................1252
查看 CBO 模式..................................................................................................................1253
优化器模式具体说明.......................................................................................................1253
optimizer_index_cost_adj
参数
.....................................................................................1254
在
CBO
下写
SQL
语句的注意事项
.............................................................................1254
10053 事件 ................................................................................................................................1256
10053
事件说明
..........................................................................................................1256
示例:
.........................................................................................................................1257
查看生成的
trace
文件
...............................................................................................1258
10046 事件................................................................................................................................1264
10046
事件说明
..........................................................................................................1264
对当前
session
使用
10046
事件
................................................................................1264
对其他的会话进行跟踪
.................................................................................................1265
用 SQL_TRACE 跟踪...................................................................................................................1265
使用 10046 事件跟踪..............................................................................................................1265
SQL TRACE ...................................................................................................................................................................1266
统计信息...................................................................................................................................1267
Statistic
说明
..............................................................................................................1267
统计信息的收集
........................................................................................................1268
查看该 Job 信息.......................................................................................................................1268
监控参数 STATISTICS_LEVEL ...................................................................................................1269
停用该 Job................................................................................................................................1269
系统用户统计信息的收集........................................................................................................1269
统计信息的存储位置
..................................................................................................1270
表的统计信息...........................................................................................................................1271
索引列的统计信息...................................................................................................................1271
列的统计信息...........................................................................................................................1271
测试:表只有分析了之后,num_rows 才会有值.................................................................1271
直方图(
histograms
)
................................................................................................1272
Height-Balanced Histograms...................................................................................................1273
Frequency Histograms ............................................................................................................1274
DBMS_STATS
包 使用说明
........................................................................................1276
DBMS_STATS 包的几个常用功能:性能的收集,设置,删除 ..............................................1277
GATHER_TABLE_STATS 存储过程...................................................................................1277
GATHER_SCHEMA_STATS 存储过程.................................................................................1283
DBMS_STATS.GATHER_INDEX_STATS 存储过程...............................................................1287
DBMS_STATS 包管理功能 ..........................................................................................................1289
获取分析数据...................................................................................................................1289
设置分析数据...................................................................................................................1290
删除分析数据...................................................................................................................1290
保存分析数据...................................................................................................................1291
导入和导出分析数据.......................................................................................................1291
锁定分析数据...................................................................................................................1292
分析数据的恢复...............................................................................................................1292
动态采样
...................................................................................................................1293
什么是动态采样.......................................................................................................................1293
动态采样的级别.......................................................................................................................1296
Level 0 ...............................................................................................................................1297
Level 1 ...............................................................................................................................1297
Level 2 ...............................................................................................................................1297
Level 3 ...............................................................................................................................1297
Level 4 ...............................................................................................................................1297
Level 5,6,7,8,9........................................................................................................1297
Level 10 .............................................................................................................................1297
什么时候使用动态采样...........................................................................................................1297
HINT..............................................................................................................................................................................1298
和优化器相关的
Hint
.......................................................................................................
1300
ALL_ROWS 和 FIRST_ROWS(n) -- CBO 模式 ......................................................................1300
RULE Hint -- RBO 模式..........................................................................................................1300
访问路径相关的
Hint
........................................................................................................
1301
FULL Hint....................................................................................................................................1301
INDEX Hint .................................................................................................................................1301
NO_INDEX Hint ..........................................................................................................................1302
INDEX_DESC Hint .......................................................................................................................1302
INDEX_COMBINE Hint................................................................................................................1302
INDEX_FFS Hint ..........................................................................................................................1302
INDEX_JOIN Hint........................................................................................................................1302
INDEX_SS Hint............................................................................................................................1302
表关联顺序的
Hint
............................................................................................................
1303
LEADING hint..............................................................................................................................1303
ORDERED Hint............................................................................................................................1304
表关联操作的
Hint
............................................................................................................
1304
USE_HASH,USE_NL,USE_MERGE hint........................................................................................1304
NO_USE_HASH,NO_USE_NL,NO_USE_MERGE HINT .................................................................1305
并行执行相关的
Hint
.......................................................................................................
1305
剩余1518页未读,继续阅读
小小哭包
- 粉丝: 1933
- 资源: 4054
上传资源 快速赚钱
- 我的内容管理 展开
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
最新资源
- IPQ4019 QSDK开源代码资源包发布
- 高频组电赛必备:掌握数字频率合成模块要点
- ThinkPHP开发的仿微博系统功能解析
- 掌握Objective-C并发编程:NSOperation与NSOperationQueue精讲
- Navicat160 Premium 安装教程与说明
- SpringBoot+Vue开发的休闲娱乐票务代理平台
- 数据库课程设计:实现与优化方法探讨
- 电赛高频模块攻略:掌握移相网络的关键技术
- PHP简易简历系统教程与源码分享
- Java聊天室程序设计:实现用户互动与服务器监控
- Bootstrap后台管理页面模板(纯前端实现)
- 校园订餐系统项目源码解析:深入Spring框架核心原理
- 探索Spring核心原理的JavaWeb校园管理系统源码
- ios苹果APP从开发到上架的完整流程指南
- 深入理解Spring核心原理与源码解析
- 掌握Python函数与模块使用技巧
资源上传下载、课程学习等过程中有任何疑问或建议,欢迎提出宝贵意见哦~我们会及时处理!
点击此处反馈
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功