oracle 体系结构 第一章 Oracle 基本体系结构图 : 第二章图分析 : 思考 : 一条 SQL 语句是如何进入到数据库, 最终得到数据结果的? 第一节数据库结构 : 内存 进程 存储, 如图 : 1) 内存结构 :
Oracle 内存结构 : 1. PGA: 私有内存, 专用于每一个服务器进程或后台进程 每一个进程使用一个 PGA; 主要包含以下内容 : a) 堆栈 ( 全局指针 ); b) session inform( 会话信息 ); c) cursor inform 游标信息 : 用来存放最近 20 次执行的 SQL 语句 ; d) 排序区 ; 2. instance: 实例 ; 3. SGA: 共享内存, 包含实例的数据和控制信息的内存区, 包括以下几个池 : a) shared poll:library cache dictionary data b) database buffer cache; c) java pool; d) stream pool; e) log buffer; 4. database: 数据库 5. 主要文件 : control file log file datafile parameter file password file trace file archive log; 2) 进程结构 :
进程结构 : 1. user process: 用户进程, 在客户端 ; 2. server process: 服务器进程, 在服务器端 ; 如果是在服务器端本身连接 Oracle, 不走网络, 走 beq 协议 ; 3. 后台进程 :pmon smon dbwn lgwr ckpt 第三章 Oracle 实例管理 : 第四章 instance 定义 1) instance 是数据库最重要的组件, 启动数据库之前先启动实例, 通过实例 管理数据库, 主要包括两大块 :SGA 后台进程 ; 2) 与数据库的关系 : 一个数据库可以有多个实例, 但是一个实例只能有一 个数据库 ; 如果是本地服务器登录数据库, 则是以有独占形式连接数据 库 instance 包括两大块 : 内存 (SGA)+ 后台进程 ; 第一节 SGA ( 共享内存 ) 在 Oracle 中查看共享内存段大小 : ipcs -sm SGA: 共享内存, 包含实例的数据和控制信息的内存区
SGA 数据结构, 如下 : 1) 共享池 (shared pool): 1) 作用 : 加速 SQL 语句解析, 减少 SQL 语句重解析! 2) 包括两部分 :library cache data dictionary cache a) library cache:( 库缓冲区高速缓存 ) 主要存放 SQL 语句 PL/SQL 块, 其作用是减少 SQL 语句的重解析 ; b)data dictionary cache( 数据字典缓存 ) 存放的是最近访问的数据字典数据, 以行为单位保存, 其作用 : 加速 SQL 语句的解析, 比如用户解析 对象解析的内容缓存, 避免下次再对磁盘进行读取! 3) 一条 sql 语句对数据库进行操作的过程 : 注意 : 需要借助 执行计划, 在解析阶段即 制作出执行计划 解析 执行 获取 第一解析 :( 快速软解析 软解析 硬解析 ) a) 将 sql 语句文本转换成 ASCII 码 ; b ) 将 ASCII 码进行 hash 计算, 生成 hash 值 ; c) 将 hash 值代入 PGA 在 cursor inform 中进行扫描, 查找是否有相同的 hash 值 ; d) 如果 PGA 扫描命中, 叫做 快速软解析 :PGA 中只缓存最后 20 个游标信息 (10g); 查看 pga 游标信息区中的信息, 如图 : show parameter session_cache e) 共享池扫描 :library cache 即在 PGA 中扫描没有命中, 将在 SGA 的 library cache 中进行扫描, 如果在共享池扫描命中, 则叫做 软解析 ; f) 如果再 PGA SGA 均扫描无命中, 则需要对此语句做硬解析 ( 步骤 ): a) 语法分析 : 即判断 sql 语句是否满足 Oracle 对 sql 语句的语法要求 ;
b) 语义分析 : 即判读此 sql 语句中涉及的用户 table 列等所有对象是否存在, 属性是否正确 ; c) 安全审核 : 即校验对象权限的信息 ; d) 优化 : 筛选执行计划, 对比成本值 (cost CPU), 使用成本值最低的 ; e) 行资源生成 ; 第二步 : 执行 a) 根据执行计划, 如果走索引, 那么会根据索引知道需要数据存放的 file_id,block_id,blocks, 如图查看数据块存放的相关信息 ; b) 查看执行计划 : 递归查询 : 第三步 : 获取 4) 对 sharedpool 的管理 : 使用 LRU 算法 : 最近最少使用原则 ;LRU 分为冷端和热端 ; 最近最少使用的 SQL 语句和 PLSQL 数据块将放在冷端 如果 shared Pool 内存不够用时, 将从 LRU 冷端开始释放内存 2) database buffer cache: 1) 将常用的数据块备份到 database buffer cache 中, 以方便数据提取, 减少物理 I/O; 2) 以 Oracle 块为单位做读写, 比如此数据库以 8k 为一个块, 则读写到 database buffer cache 中 ; 3) database buffer chache 内存空间管理原则 :LRU 算法, 即解决如 何释放内存的问题 ;
4) 其作用 : 减少物理 I/O; 3) large pool:; 保存和备份恢复有关的数据块 ; 可以加快备份恢复的效率, 与 Oracle 正常操作关系不大 ; 4) Java Pool: 在使用 Java 编译, 或者数据块升级以后, 需要打补丁的情况下, 在升级数据字典时至少需要 150M 以上的 large pool 和 Java Pool; 5) streams pool: 流池, 为什么会有流池? ** 高级复制 : 即通过快照保持数据同步, 但是数据同步不稳定, 后引申出流复制 ; ** 如果使用了流复制, 必须划分出流池, 没有流复制就不用划分流池, 但是到 11g 以后就没有流池 (streams pool), 被划分到 goldengate 中 ; 6) log buffer: 定义 : 日志区, 记录所有数据的变化 ; 作用 : 保证数据安全, 可用于恢复数据 recover; *** 延伸 : 如何查看与 PGA 相关的信息? 在动态性能视图 (v$open_cursor) 中, 存放私有游标的信息 ; 在 10.2.0.1 版本中,SQL 语句要经过 3 次执行后才会放到 PGA 的游标信息区 ;10.2.0.5 以后则立即执行立即放到 PGA 的 cursor inform 中 ; 7) 后台进程 : 核心后台进程任何一个关闭, 数据库立即 shutdown;
a) pmon: 监控进程 作用 : a) 监控其他非核心后台进程, 如果其他非核心后台进程意外终止, 则由它重启 ; b) 清洁意外终止的死链接在后天残留的垃圾 : 将其修改的数据回退, 释放锁 ; pmon 会不断给每个连接发包 ; c) 在网络环境中,pmon 动态注册实例的信息到监听程序 ; d) 在集群环境有关 : 在集群环境中, 每 60 秒收集一次本节点的 CPU 压力, 以这些数据做负载均衡 ; b) smon: 系统监控 : 作用 : a) 系统监控管理, 定期合并空闲, 回收临时段 ; b) 做实例的恢复 : 前滚 回滚 释放资源问 题 : 原因 :? 什么时候做恢复? 为什么要做恢复? 数据修改随时发生, 但是数据同步定期做 ; 所以会产生脏块 ( 灰块 ), 即 内存数据与磁盘数据不一致, 所以会存在以下两种情况 : a) 没提交的已写盘的数据 ( 需要进行回滚 ); --- 什么时候会发生这种数据? b) 已提交没有写盘的数据 ( 进行前滚 ); ***commit 只是将日志 log 写盘, 与脏数据是否写盘无关!! c) dbwn 数据写 定义 : 将脏数据写盘,(n 的取值 :0-9,a-j, 一共可以并行 20 个 ) 问题 : 什么时候触发 dbwn 进程? 以下 9 种情况触发机制 : 1) check pointer: 有检查点, 2) 脏数据达到阀值 : 达到 buffer 内存的 10% 即要将脏数据写到磁盘 ; 这就是造成为什么没有提交的脏数据会写到磁盘 ; 并且这样也不会影响读数据, 因为在修改一个数据块时, 在 buffer cache 中会生成一个与原始数据一致的镜像数据, 以供其他会话访问, 和做 rollback 时使用! 3) 扫描整个 database buffer cache 没有空闲 : 这种情况是 : 虽然脏数据没有占到 Buffer 的 10%, 但是数据的缓存占了太多 Buffer 空间, 所以需要释放空间, 会优先读取 lruw( 脏数据链表 ), 再读取 LRU, 做超 0 释放, 释放数据缓存空间 ; 4) time off: 每三秒调度一次数据写 ; 5) 在集群环境中的 ping 请求 :
将所有结点的脏数据写磁盘, 使数据状态一致, 协同工作的 6) 删除表 截断表 :drop table trucat table; 7) tablespace read only: 表空间只读触发 8) tablespace offline: 表空间脱机触发数据写 ; 9) begin backup: 热备份命令触发数据写 ; d) lgwr 日志写 定义 : 日志写进程 ( 日志写比数据写更重要 ), 因为内存中的数据一断电就消失, 要做数据的回滚 前滚只能依靠日志文件 log buffer 只是缓冲日志写 触发 lgwr 的几种情况 : 1) 提交命令 :commit; 2) log buffer 达到内存的 1/3, 即达到阀值 ; 3) time of:3 秒一次 ; 4) 任何一次数据写之前都必须做 lgwr; *** 最大保护机制, 即 0 数据丢失 是指重库的日志先写, 主库的数据才能写入, 保证在主库断电时, 所有的记录都在重库中有记载, 重库的日志记录只会比主库多, 这个在 datagard 中有应用 ; e) ckpt 检查点进程 1) 调度数据写 ; 2) 会将已经完成的检查点写到数据文件头 ; 3) 把已经完成的检查点写到控制文件 ; 1. other: 其他进程 ; 2. 注意 : 磁盘 内存支持并行 I/O, 磁带不支持 ; 3. 服务进程不属于实例 ; 4. 进程 : 5. 先有实例后有数据库, 实例是一个组件 ; 6. 一虚一实 : 实的东西通过各种文件去控制虚的东西 ; 8) database a) Oracle 的主要文件 a) controfile: 控制文件, 二进制 : b) online redolog: 联机日志 c) datafile: 数据文件 ( 占用内存最大 ); d) 辅助文件 : e) password file: 口令文件 ; f) parameter file: 参数文件控制实例 g) trace file: 追踪文件
h) achieved log: 日志归档文件 b) password file( 口令文件的作用和管理 ) 目的 : 控制用户权限的管理 1) 用户的安全审核机制 : 按安全审核模式划分安全审 核机制 : 外部审核 数据库审核 2) 外部审核机制 : 默认情况下只有 sys 用户使用外部审核, 分为两种 : 操作系统审核 命令审核 ; 1. 操作系统审核 : 在服务器本地才可以使用, SQL> conn / as sysdba Connected. SQL> show user USER is "SYS" 即能登录操作系统, 通过操作系统审核后, 无论用任何账户或密码都可以以 sysdba 的角色无条件的登录到数据库 ; 但是可以通过修改 sqlnet.ora 文件关闭操作系统审核 ; 2. 如何关闭操作系统审核? a) 相关文件路径 :$oracle_home/network/admin/samples/sqlnet.ora 文件 ; b) 赋值文件中的内容 : 字段 : sqlnet.authentication_services 如图 : c) 退到 admin 下, 创建一个新的文件 :sqlnet.ora; 在此文件中添加如下内容 :( 如果 admin 下已有此文件, 对其进行修改即可 ) --- 关闭后, 必须使用口令文件登录 ; --- 如果忘记 sys 用户的口令, 如何找回? 需要口令文件管理 : 只有 sys 用户才使用口令文件 ; 其他用户的口令在数据库中进行修改 (alter user identified by 新口令 ); 普通用户的口令可以通过 sys 用户和自己修改 ; 口令文件存放的位置和名称 :
$ORACLE_HOME/dbs/orapw<sid> %ORACLE_HOME%\database\PWD<sid>.ora <sid> = echo $ORACLE_SID d) 重置 sys 口令文件 : a) 进入到目录 :$ORACLE_HOEM/dbs 下 ; b) 过滤查看口令文件名字 :ll orapw*( 一般 orapw 后面跟实例名 ); c) 使用小工具 orapwd 修改口令文件 ; 借助工具 :orapwd, 破解 sys 的口令 ; windows 下的口令文件 : %Oracle_home%\database\PWD<sid>.ora; 在 windows 下如何查看 Oracle 的 sid: 在 Oracle 的注册表中 ; 掌握以下三点 :? 口令文件的位置?? 口令文件的名字?: 系统环境变量 ;? 如何重置口令文件? 只有存在于口令文件中的用户才可以使用 as sysdba 的方式登录数据库, 如何查看口令文件中包含哪些用户呢? SQL> select * from v$pwfile_users; 将普通用户加入到口令文件 SQL> grant sysdba to scott; * 只要使用 as sysdba 的方式创建会话,show user 永远是 sys 将普通用户移出口令文件 SQL> revoke sysdba from scott; 匿名登录形式 : 3) 数据库审核 : 通过数据库中的数据字典获得安全审核的信息, 那么就意味着数据库在没有 open 的状态下是无法创建连接的 普通用户, 即数据库不打开, 用户无法进行审核登录数据库 ; SQL> conn scott/tiger ***dba_ 为开头的文件叫 : 数据字典 ;
1) 在数据库中查看用户信息 :select * from dba_users; 4) 什么是 sysdba 和 sysoper 1) 超级管理员 (sys) 连接 :conn sys/sys as sysdba: 在 9i 以后想用超级管理员 ( 即 sys 用户 ) 登陆, 必须在 connect 时声明角色也叫做权限说明 :as sysdba; 不能以 conn sys/sys 方式登陆 ; 在 9i 之前 sys 用户登陆可以使用 :conn sys/sys 方式登陆, 但是不是真正的超级用户, 只有加上 as sysdba 才能拥有超级权限! 只有 sys 用户可以启停数据库,sys 的信息不在数据库中, 没有数据库但是 sys 已经存在了 sys 不能登录的原因 : 口令文件出错或者软件引擎出错 ; *** 修改某个参数, 可以打开某些低版本的功能, 但是会影响数据字典 ; ***SQLplus 中光标的回退, 命令的重写 : 外挂 ; SQL 中读取口令文件, 命令如下 : 注意 : select * from v$pwfile_users v$pwfile_users 直接查看的就是 $ORACLE_HOME/network/admin 下的 sqlnet.ora 文件 ( 即口令文件 ):? 如何将普通用户加入口令文件, 即可以以普通用户通过 as sysdba 的角色登录 ; a) 赋予 sysdba 的权限 : grant sysdba to scott; b) 普通用户以 sysdba 权限登录 : conn scott/tiger as sysdba; c) show user: 但是 show user, 显示的用户永远是 'SYS'; 2) sysoper: 超级操作员, 默认的 user 永远是 public sysoper 比 sys 只少四种权限 ( 两者的区别 ): a) 不能创建数据库 ;
b) 不能将启动数据库到受限会话访问模式 ; c) 不能进行热备份 ; d) 不能进行数据库的不完全恢复 ; 怎样使普通用户成为操作员 : SQL> grant sysoper to scott; SQL> conn scott/tiger as sysoper 收回权限, 将普通用户移出口令文件 : revoke sysoper from scott; c) parameter file: 参数文件的作用与管理 1) 什么是初始化参数 控制数据库行为 属性的开关 查看所有 的初始化参数 SQL> select name,value from v$parameter; 修改过的特征参数保存到参数文件 SQL> select name,value from v$parameter where isdefault<>'true'; 2) 初始化参数的种类 动态参数 : 内存值可以修改的参数叫做动态参数 select NAME from v$parameter where ISSYS_MODIFIABLE<>'FALSE'; 静态参数动态参数 : 内存值不可以修改的参数叫做静态参数 select NAME from v$parameter where ISSYS_MODIFIABLE='FALSE'; 3) 参数文件的作用 保存非默认值的初始化参数, 用来约束实例的行为 只有在数据库启动的 时候被读取, 所以参数文件中的参数值如果被改变需要重新启动数据库才可以 生效 4) 参数文件的种类 spfile 和 pfile 在 SQL 中查看参数 :select * from v$parameter
Pfile 文件特点 : pfile(parameter file) 是 ascii 码文件命名规则是 init<sid>.ora 使用 vi 编辑器修改参数值 pfile 可以放在客户端 spfile 文件特点 : spfile(server parameter file) 是二进制格式命名规则是 spfile<sid>.ora 只能只用 alter system set 命令修改参数值 Spfile 只能放在服务器端 Spfile 的优先级别高于 pfile 由于 spfile 是二进制格式所以脱离了文件系统的束缚 * 在 oracle 9i 之后的版本 spfile 是默认参数文件 参数文件的位置 : $ORACLE_HOME/dbs 参数文件的名称 : init<sid>.ora spfile<sid>.ora 查看 pfile 文件中的参数值 : $ cat initorcl.ora 查看 spfile 文件中的参数值 : $ strings spfileorcl.ora 1. 如何查看当前实例的特征参数, 即修改过的非默认值的初始化参数 : 2. 只有一个初始化参数没有默认值 : 库名 db_name;
3. 参数文件路径 :$oracle_home/dbs(windows 下就在 database): 4. 命令规则 : spfile: 必须放在服务器端, 是一个二进制文件,9i 以后才出现 spfile; 修改 spfile 必须使用 SQL 语句 ;spfile 优先级高于 pfile; pfile: 可以在服务器端也可以在客户端, 是一个 ASCII 码文件 ; 修改 pfile 使用文本编辑器 两者有一个都可以启动实例 ; 5. 查看数据库是由哪一个参数文件启动的 : show parameter spfile 如果后面 VALUE 有值, 则是由 spfile 启动的, 没有则是 pfile; 5) 修改 spfile 中的参数值 只修改动态参数的内存值 SQL> alter system set sga_target=500m scope=memory; 只修改动态参数在参数文件中的值 SQL> alter system set sga_target=500m scope=spfile; 同时修改动态参数的内存值和参数文件中的值 SQL> alter system set sga_target=800m scope=both; SQL> alter system set sga_target=800m; 只修改静态参数在参数文件中的值 SQL> alter system set sga_max_size=500mscope=spfile; 内存值改变后需要重新启动数据库才能生效 SQL> startup force 如果修改静态参数的内存值, 数据库会报错 SQL> alter system set sga_max_size=500m; alter system set sga_max_size=500m * ERROR at line 1: ORA-02095: specified initialization parameter cannot be modified
*sga_target: 为动态参数 ; *sga_max_siz: 是静态参数 ; * 如何区别动 静参数 : 参看参数内存里面的当前状态是否能被修改 ; 如果能被修改为动态, 不能修改的为静态 ; * 参数的修改 : (1) 动态参数的修改 : 使用 :alter system set sga_target=600m 修改成功后,SGA 内存和参数文件均被修改 ; (2) 静态参数的修改 : 静态参数修改后必须重新启动实例才能生效 ; *** 延伸知识 : 为什么 11g 下 sga_target 为 0, 如图???
内存管理的进化历程 : Oracle 8i PGA () *sga_max_size: *sga_target: 到 Oracle10g 以后 sga 中除了 log Buff 的其他池自动调整大小 ; 是 memory_max_target(pga+sga) 的大小不能超过, 以下 : Oracle11g 的 SGA 参数 : lock_sga: 锁定 ; pre_page_sga: 操作系统允许的锁定物理内存最大值 : 主机操作 : 修改锁定物理内存无上限 : 修改内核参数的参数限制 : 文件路径 :/etc/security/limits;
6) pfile 文件的修改 1) pfile 与 spfile 的关系与区别 ; 2) 如何创建 pfile; 3) 如何修改 pfile 启动的实例的参数 ; 修改动态参数 : 修改静态参数 ; 4) 如何修改 pfile spfile 的存放路径 ; a) 即参数文件不在默认的路径下, 如何启动实例? b) 使用非默认路径下的 spfile: spfile 的启动不能使用 startup spfile= 路径的形式, 但是可以借助 pfile 文件指向 spfile, 如下 : 5) 如何使用字符设备保存 spfile: 即如何使用设备充当参数文件启动实例 : a) 创建出一个空的 raw 区域 ; b) 如果 Oracle 默认路径下没有 pfile 文件, 先创建 pfile 文件 ; crate pfile from spfile c) 根据 pfile 文件创建一个非默认路径下的 spfile 文件 : create spfile='/dev/raw/raw1' from pfile; d) 删除 Oracle 默认目录下相关的参数文件 ; e) 修改 pfile 中指向 spfile 的路径 ; f) 为什么要将参数文件放在设备当中呢? 因为在 Oracle 做高可用时, 会有主备两台 server, 如果分别将参数文件放在两台 server 上会造成参数副本多, 难管理 ; 所以可以采用这种方法将参数文件以字符设备的形式, 放到两台 server 的共享存储上, 分别让两台 server 的 pfile 指向共享的参数文件, 利于 Oracle 切换 管理 ;
*** 如何在没有空闲分区的情况下, 划一个文件块做字符设备? a) 冲 10m 的二进制文件 b) losetup 将 disk1 变为循环设备 ; c) 再将循环设备变为字符设备 ; d) 再修改权限 ; e) 但是这是暂时性的, 如果要永久保存需要配置 udev; ***Oracle 只能将二进制文件 (spfile) 放到字符设备中, 也能从字符设备中读取 spfile! *** 小实验 : 如何将写成文件设备的 spfile 文件反读成 spfile 二进制文件 : a) 直接在数据库中使用 create 命令 : b) 使用 dd 命令再创建一个新文件 : 6) **pfile 启动的实例, 使用 alter system set 命令只能修改内存的参数, 重启恢复原值 ; 7) 使用一个 pfile 参数文件启动多个实例 : 8) 使用一个 spfile 参数文件启动多个实例 : 思路 : 使用多个 pfile 指向一个 spfile! d) trace file 跟踪文件 : 1) 审计线索 : 定义 : 记录审计信息, 一定会被审计, 凡是以 SYSDBA 身份登录数据库进行的操作都会产生一个审计文件 :
2) 警报日志 : 路径, 过滤查看警报日志 : alter_<sid>.log 记录的内容 : 数据库启动 停止的信息 ; 启动停止中报错的信息 ; 操作中报错的信息 ; 切换 日志的信息 ;... 警报日志文件的管理 : * 每天查看 ; * 定期备份, 冲空警报日志文件 ; * 注意 : 如何冲空此日志文件 : 使用 > 符号 ; 3) 后台进程跟踪文件 : 故障诊断 文件名后缀 :.trc 4) 用户进程的跟踪文件 ; 如何对一个用户打开或关闭进程跟踪 :
如何查看一个用户进程的跟踪文件 : 系统 PID, 可以在数据库中查看 : 获取跟踪文件 : 格式化跟踪文件 : $ TKPROF 5) 核心转储 : 路径 : 注意 : 以上是 10g 的,10g 和 11g 的跟踪文件存放路径不一样, 并且在 11g 中废掉两个跟踪文件参数 : background_dump_dest user_dump_dest 在 11g 中, 使用诊断目录来保存跟踪文件, 第五章数据库的启动和停止 第一节数据库的启动流程 数据库的启动分三个阶段 :nomount mount open 1) Shutdown 状态的数据库怎样启动到第一个阶段 SQL> startup nomount 查看数据库 启动到哪一个阶段 : SQL> select status from v$instance; STATUS
------------ STARTED 第一个阶段 :nomount Nomount 状态下数据库都做了什么? 1. 分配实例 : 分配共享内存段启动后台进程 : 查看内存段信息 : ipcs -sm 2. 写审计信息和警报日志 启动到 nomount 状态需要什么?( 知道其要做什么即可推断其需要什么 ) 1. 参数文件 : 参数文件控制实例的 ; 2. 实例管理的目录 {a,b,c,u}dump 目录 : 这些目录是用来写审计信息和警报日志的 ; adump: 存放审计信息 ;aduit_file_dest; bdump: 放参数 :background_dump_dest; 有目录, 并且有空间! 启动到 nomount 状态可以做什么? 1. 创建数据库 2. 重建控制文件 3. 可以修改初始化参数 2) 启动数据库到 mount 状态 数据库在 shutdown 状态下如何启动到 mount 状态 SQL> startup mount 数据库在 nomount 状态下如何启动到 mount 状态 SQL> alter database mount;--- 即修改数据库状态 查看数据库启动到哪一个阶段 : SQL> select status from v$instance; STATUS ------------ MOUNTED 由 nomount 到 mount 数据库都做了什么? 加载控制文件的信息到内存 由 nomount 到 mount 需要什么? 需要控制文件 mount 状态可以做什么? 1. 备份 还原 恢复数据库 2. 移动数据库文件 3. 做数据文件的 offline 和 online( 不能做表空间的 offline 和 online) 4. 打开和关闭数据库的归档模式
5. 打开和关闭闪回数据库的功能 3) 启动数据库到 open 数据库在 shutdown 状态下如何启动到 open 状态 SQL> startup 数据库在 nomount 状态下如何启动到 open 状态 SQL> alter database mount; --- 数据库装载
SQL> alter database open;--- 数据库打开数据库在 mount 状态下如何启动到 open 状态 SQL> alter database open; 查看数据库启动到哪一个阶段 : SQL> select status from v$instance; STATUS ------------ OPENA 由 mount 到 open 状态数据库做了什么? 校验所有的数据文件和联机日志文件的存在否及有效性 即 : 按照控制文件中的指针 由 mount 到 open 状态需要什么? 需要数据文件和联机日志文件 Open 之后可以做什么? 添 删 改 查 4) 查看数据库的打开模式 : 默认情况下打开到的是 读写 模式, *** 把数据库打开到只读状态 : alter database open read only; 5) 查看数据库的登录风格 (logins): 正常下是 allowed 1) 如何更改受限会话访问权限? SQL>alter system enable restricted session 2) 如何查看 : 查看此时的登录风格 : SQL>select logins from v$instance; 在此模式下下 instance 的登录风格为 :restricted, 如图 :
3) 如何关闭? alter system disable restricted session 6) 受限会话访问 此时普通用户登录, 报错如下 : *** 只有拥有 restricted session 权限的用户才能访问数据库 : grant restricted session to scott; 第二节 停止数据库的方式 1. 正常停库 ; shutdown shutdown 2. 事务级停库 ; shutdown 3. 立即停库 ; shutdown 4. 强制停库 ; shutdown normal transactional immediate abort a) shutdown normal = shutdown 1. 新的连接不允许建立 (sys 用户不受限 ) 2. 等待查询结束 3. 等待交易 ( 事务 ) 结束 (commite rollback) 4. 强制产生检查点 ( 做数据同步 ), 为什么要强制产生检查点?
--- 产生检查点, 触发数据写, 将脏数据磁盘, 实现数据同步! 5. 关闭数据库文件 6. 关闭控制文件 7. 关闭实例 b) shutdown transactional 1. 新的连接不允许建立 (sys 用户不受限 ) 2. 不等待查询 3. 等待交易 ( 事务 ) 结束 ( 只等交易, 不等查看 ) 4. 强制产生检查点 ( 做数据同步 ) 5. 关闭数据库文件 6. 关闭控制文件 7. 关闭实例 c) shutdown immediate( 生产库中最常用的 ) 1. 新的连接不允许建立 (sys 用户不受限 ) 2. 不等待查询 3. 不等待交易 ( 事务 ), 交易被 rollback 4. 强制产生检查点 ( 做数据同步 ) 5. 关闭数据库文件 6. 关闭控制文件 7. 关闭实例 d) shutdown abort( 慎用!) 相当于拔电源数据库停止之后数据状态是不一致的, 重新启动的时候需要做实例的恢复 ( 为什么?) --- 因为这种关闭方式都没有产生检查点, 没有进行数据同步 ; 慎用 : Startup force = shutdown abort + startup --- 相当于强制重启 Startup force nomount = shutdown abort + startup nomount Startup force mount = shutdown abort + startup mount 何时使用 abort 选项? 启动或者停止数据库过程当中遭遇异常, 使用 abort 选项! 课堂要求 : 1) 熟悉数据库每个开启步骤, 做什么? 需要什么? 用户能做什么? 2) 关闭数据库的各种方式的区别 特点?
第六章创建数据库 第一节使用图形向导创建数据库 : $ dbca 运用的是下面的文件 第二节手工创建数据库 1) 向导创建数据库 2) 全手工创建数据库 1. 创建相关目录 $ mkdir -p /oradate/bj --- 创建数据库目录 $ mkdir -p /u01/app/oracle/admin/bj/{a,b,c,u}dump $ mkdir -p /u01/app/oracle/flash_recovery_area/bj 2. 创建口令文件 $cd $ORACLE_HOME/dbs $orapwd file=orapwbj password=oracle
3. 创建参数文件 ( 复制已有的库的参数文件进行修改自己库的参数文件 ) ---------------------------------------------------------------------------------------------------------- *.audit_file_dest='/u01/app/oracle/admin/bj/adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='/oradate/bj/control01.ctl','/u01/app/oracle/fast_recovery_area/bj/cont rol02.ctl' *.db_16k_cache_size=25165824 *.db_block_size=8192 *.db_domain='' *.db_name='bj' *.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area' *.db_recovery_file_dest_size=4322230272 *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(protocol=tcp) (SERVICE=orclXDB)' *.log_archive_format='%t_%s_%r.dbf' *.memory_target=842006528 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='exclusive' *.undo_tablespace='undotbs1' ######## 特别注意 undo 表空间的值 ------------------------------------------------------------------------------------------------------------------ 4. 启动数据库到 nomount $ export ORACLE_SID=bj $ sqlplus / as sysdba SQL> startup force nomount 5. 创建数据库 ( 查看官方文档复制到 txt 文档中修改路径信息, 成为自的库 ) ------------------------------------------------------------------------------------------------------------------- SQL>CREATE DATABASE bj USER SYS IDENTIFIED BY oracle USER SYSTEM IDENTIFIED BY oracle LOGFILE GROUP 1 ('/oradate/bj/redo01a.log','/oradate/bj/redo01b.log') SIZE 100M BLOCKSIZE 512, GROUP 2 ('/oradate/bj/redo02a.log','/oradate/bj/redo02b.log') SIZE 100M BLOCKSIZE 512, GROUP 3 ('/oradate/bj/redo03a.log','/oradate/bj/redo03b.log') SIZE 100M BLOCKSIZE 512 MAXLOGFILES 5 MAXLOGMEMBERS 5 MAXLOGHISTORY 1
MAXDATAFILES 100 CHARACTER SET US7ASCII NATIONAL CHARACTER SET AL16UTF16 EXTENT MANAGEMENT LOCAL DATAFILE '/oradate/bj/system01.dbf' SIZE 325M REUSE SYSAUX DATAFILE '/oradate/bj/sysaux01.dbf' SIZE 325M REUSE DEFAULT TABLESPACE users DATAFILE '/oradate/bj/users01.dbf' SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT TEMPORARY TABLESPACE tempts1 TEMPFILE '/oradate/bj/temp01.dbf' SIZE 20M REUSE UNDO TABLESPACE undotbs1 DATAFILE '/oradate/bj/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED; ---------------------------------------------------------------------------------------------------------------- 6. 创建表空间 ( 创建完是没有表空间的 ) 典 create tablespace mao datafile '/oradata/orcl/mao01.dbf' size 10m; 7. 创建数据字 SQL> @?/rdbms/admin/catalog 8. 创建 PL/SQL 运行环境 SQL> @?/rdbms/admin/catproc 3) 练习中遇到的问题 : 手工建库 1. 小插曲 : 在 11g 下手工创建数据库时, 出错 : 在 SQL 下跑创建数据库的语句时, 报错 1:group1 已存在 ; 报错 2:group number 无效 ; 原因 1: 在参数文件中, 给各个文件的路径指定混乱 ; 创建路径有问题 ; 原因 2: 在创建文件目录时, 混乱有问题! *** 疑问 : 是否参数文件中指定的控制文件 跟踪文件的路径和创建数据库时指定生产文件的路径有关系? 解决 : 弄清楚每个参数的意思, 指定的路径 ; 报错 3: 在参数文件中指定了 memory_target 后, 在创建数据库时报错,memory_target 无效 ; 原因 : 在其它数据库也开启的情况下,memory_target 无法分配! 为什么??? 2. 小插曲 : 安装 SQL 执行光标回退, 和命令重复的外挂软件 : 1) 上传软件 :rlwrap-0.42.tar.gz; 2) 解压 :tar -xvf ; 3) 安装 : 进入解压后的文件夹, 可以看到 REDME 文件, 里面有软件的安装步骤 ; 4) 运行 configure 文件 :./configure
5) 根据提示操作 :make;make install; 6) 完成后修改 sqlplus 参数文件 :.bash_profile, 添加 :alias sqlplus='rlwrap sqlplus', 即当输入 sqlplus 命令时, 是通过 rlwrap 外挂后来启动 sqlplus 的 ; 要求掌握 : 1) 启动数据库到不同的阶段 2) 查看当前数据库启动到哪一个阶段 3) 使用不同的方式停止数据库 第七章系统启动自动打开数据库 : 第一节如何让系统自动启动数据库? /etc/oratab etc/rc.local; $ORACLE_HOME/ 在.bash_profile 中将监听加入 : 命令 :.!$ 表示将上一条命令的最后一个字符串 ; *** 自动启动监听 : *** 其中 /etc/oratab 控制着实例的开关,Y 表示自动打开,N 表示不自动打开 ;
第八章数据字典管理与使用 : 第一节 数据字典 : 管理员的主要管理工具, 掌握查找字典的 技巧! 是记录数据库信息的核心的表, 数据字典对于用户是只读的, 千万不要修改数据 字典的分类 : 1. 数据字典表 : 信息来源于以 $ 结尾的基表, 存储在系统表空间, 只有数据库 OPEN 才能访问, 不能手工修改 ; 没有数据库时不存在 ; user_xxx: 返回当前用户所拥有的对象的信息 ;user 返回的结果是 all 的子集 ; all_xxx: 返回当前用户所拥有的及有权利查看的对象的信息,all 是 dba_ 的子集 ; dba_xxx: 返回全库所有的对象的信息 ; 只有 dba 才能查看 ; a) user_tables : 当前用户所拥有的表的信息 ; 在手工建库的情况下, 运行脚本建用户数据 : b) 如何找到有用的字典? dictionary(dict): 管理数据字典的数据字典 ; * 数据字典命名规则? 内容和命名有密切关系 ; * 如何查找? 即在需要时用关键字查找 dict 数据字典 : select table_name from dict where table_name like '%sequence%' desc-- 如何在数据库 nomount mount 状态下查看数据字典?--- 使用动态性能视图 第二节动态性能视图 : 其信息来源于以 x$ 为前缀的表 ( 结构数组, 就是一张二维表 ), 信息来源于内存和控制文件 ; 其内容时刻在变化 ; v$xxx: 返回的是单实例的信息 ; gv$xxx: 返回的是 rac 环境所有实例的信息 ; ***v$fixed_table: 管理所有动态性能视图的动态性能视图, 在 dict 中也有动态性能视图 ; 通过模糊匹配查找需要的动态性能视图!
第九章管理控制文件 第一节控制文件的特点 : 1. 控制文件在 mount 状态下第一次被使用 ; 2. 数据库 open 之后时刻被使用 ; 3. 数据库工作至少需要一个控制文件 ; 4. 数据库最多可以使用 8 个控制文件 ; 5. 所有的控制文件都是镜像关系 ; 6. 控制文件的数量 位置 名字由参数决定 (control_files); 7. 如果控制文件丢失数据库会紧急停库, 重启时需要还原和恢复 ; 8. 控制文件的作用 : (1) 控制文件记录数据库的物理信息 ; (2) 记录数据文件的存放信息 ; (3) 生产库中至少需要 2 个控制文件 ; (4) 在做控制文件拷贝时, 必须先 shutdown 数据库 ; (5) 数据库在启动时, 要求参数中记录的控制文件都存在, 并且都一致, 缺一不可!( 通过日志可以查看哪个控制文件损坏, 对其进行删除重建即可 ) 控制文件在 mount 状态被加载, 数据 open 的时候控制文件时刻被访问, 数据想正常工作至少需要一个控制文件, 最多可以指定 8 个控制文件, 所有的控制文件之间都是镜像关系, 如果控制文件损坏或者丢失需要做还原和恢复 控制文件的数量和物理存储位置由初始化参数决定 (control_files) 第二节控制文件的内容 : 控制文件中记录的主要内容 : 数据库的名字 数据库的 ID 号数据库的创建时 间数据库中包含多少个表空间数 据库中包含多少个数据文件数据 库中包含多少个临时文件数据库 中包含多少个联机日志文件数据 库中包含多少个归档日志文件 rman 备份的信息 对应的 SQL 命令在数据库中查看相关信息 : a) 查看数据库 name, 数据库 ID, 数据库创建时间 : SQL>select NAME,DBID,to_char(CREATED,'yyyy-mm-dd hh24:mi:ss') from v$database;
b) 查看数据库中表空间信息 : SQL> select name from v$tablespace; c) 查看数据库中数据文件信息 : SQL> select name from v$datafile; d) 查看临时文件信息 : SQL> select name from v$tempfile; e) 查看联机日志文件信息 : SQL> select member from v$logfile; 第三节控制文件的管理 : 课堂要求 : 控制文件的多路复用 ( 即使用多个控制文件 ) 增加控制文件 ; 减少控制文件 移动控制文件 重建控制文件 1) 查看控制文件信息 : 1. nomount 状态下 : show parameter control_files; 2. mount 状态下 : select * from v$controlfile; show parameter control_files; 3. *** 注意 : 在修改参数时需要考虑两个问题 : a) 是动态参数还是静态参数 ; b) 实例是由 pfile 还是 spfile 启动的 ; 使用命令 :show parameter spfile 为了方便修改参数, 即一般将数据库以 spfile 状态启动后再修改参数 ; 修改 :alter system set control_files='/u01/app/oracle/admin/ncfc/con trol01.ctl' 4. 查看控制文件记录片段 ( 即控制文件所包含的内容 ): -->>desc v$controlfile_record_section; 2) 查看控制文件的大小 : 1) 控制文件冗余机制 :
为避免控制文件坏块, 控制文件中的内容都是双份的, 增加冗余, 可以使用 strings 读取 ; 2) 查看控制文件大小 : a) 在数据库中查看 : 在 v$controlfile_record_section 下, 将记录片段大小与记录片段总数相乘可以得到总的大小, 为 3.5M; b) 在操作系统中查看真正的 control file 文件大小, 为 7.5M; c) strings 控制文件 : 可以看到在 control file 中, 所有的内容都做了双份 3) 重建控制文件 1) 控制文件中数据文件头信息最重要!rm -rf control file 并没有将数据文件删除, 控制文件最大的作用就是指向数据文件和日志文件! 所以, 重建控制文件就是重构数据文件头, 即将指针指向几个基本的数据文件 2) 重建步骤 : a) 启动数据库到 nomount 状态 : -->> startup nomount b) 运行以下 SQL 语句 : create controlfile reuse database ncfc noresetlogs noarchivelog datafile '/oradata/system01.dbf', '/oradata/ncfc/sysaux01.dbf', '/oradata/ncfc/undo01.dbf' logfile group 1 '/oradata/ncfc/redo02.log' size 50m, group 2 '/oradata/ncfc/redo03.log' size 50m --- 指向数据文件存放路径 *** 注意 :noresetlogs noarchivelog 这两个是关系数据 recover 的两个参数! --- 指向日志文件存放路径
c) 启动数据库到 mount open 状态 : -->> alter database mount; -->> alter database open *** 注意在 open 过程中, 是否提示需要进行 recovery -->> recover database; -->>alter database open; 4) 控制文件误删除 在数据库开启的状态下误删除正在使用的控制文件, 且没有备份, 怎么办? --- 通过操作系统找回 1) su -root 下, 使用 lsof 过滤, 查看正在使用 control 文件的进程号, 如图 : 2) 根据此进程号, 到 /proc 下找到相应的文件目录 : 每个进程都会以进程编号为名在 /proc 下产生一个同名文件, 在 fd 目录下会记录进程占用的状况 : 3) 在 fd 目录下, 查看进程 :ll 命令 :ll( 两个 L), 正在运行的进程会以闪亮形式标记 ; 4) 将标量进程的进程号 cp 到操作系统下, 并修改权限 : -->> cp 12 /oradata/man/control01.ctl -->> chown oracle:dba control01.ctl ***cp 出来的文件默认权限时 root, 不修改则数据库 starup 失败! 5) 关闭数据库 : 必须使用 abort 停止! -->> shutdown abort *** 因为 abort 停止不会产生检查点, 即控制文件不会改变, 和操作系统内存上缓存的控制文件一致 ; 如果不这样, 会造成从操作系统进程保存出来的控制文件和最后关闭数据库时的控制文件不一致! 6) 重启数据库 : -->> startup force 第十章日志文件的管理与操作
第一节日志文件的特点 : 联机日志文件记录所有数据块的变 化, 用来做数据恢复 a) 以组为单位 b) 数据库正常工作至少需要 2 组日志 c) 每组至少需要一个成员 d) 组之间是切换运行 e) 成员之间是镜像关系 f) 成员的位置 名字记录在控制文件中 g) 联机日志中记录的是所有数据块的变化 h) 用来对数据库进行 recover i) 联机日志只能满足实例恢复的需求, 主要是恢复 database Buffer cache; 第二节联机日志的管理 1) 查看日志的工作状态 : a) 查看日志工作状态 : select * from v$log; 日志组工作的三种状态 : CURRENT : lgwr 进程正在使用的日志 ; INACTIVE : 没有被进程使用的日志 ; ACTIVE : 恢复进程正在使用的日志, 或者日志在实例恢复时是有用的 即 : 一个脏块变化的信息已经写到 redo log, 但是脏数据块并没有从 databuffer cache 写到磁盘, 如果此时数据块 down 掉, 那么此日志组记录的信息在下次启动实例时做数据库 recover 就要用到 ; 2) 查看联机日志的物理存储信息 : select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE 3) 查看日志文件的信息 :
1. 日志文件的 redo 条目记录一下内容 : 事务信息 文件号 块号 行号 列号, 值 事务信息 : 时间向量和块地址 (rba) 行号 : 数据在数据块中的行的位置 ; *** 区别 rowid 和行号 rowid:18 位长, 分成 4 段 ; 行号 : 只是 ROWID 的一段 ROWID 的构成 : SQL> desc v$log; Name ------------------------------- GROUP# 日志组号 THREAD# SEQUENCE# 序列号 BYTES 字节 BLOCKSIZE 所占块大小 MEMBERS 日志组成员 ARCHIVED 是否归档 STATUS 状态 FIRST_CHANGE# 开始写的时间号 ( 这里就是 SCN 号 ) FIRST_TIME 开始写的时间 NEXT_CHANGE# NEXT_TIME *** 注意 : 切换的时间 ( 如果是 INACTIVE 则记录的是下一日志组的 first_change#, 如果是 current 的日志组则是 SCN 的最大值 ) 切换的时间 1first_chage# 即数据库的 SCN 号, 任何一个操作在数据库中都有一个 SCN 号, 每个事务的开始在日志中有一个对应的 first_change#; 2 日志分组 : 在集群环境下, 一个节点对应一套日志, 事务是隔离的 ; 3first_tim# 切换到这一组日志的开始时间 ; 4 在 Oracle10g 中没有 NEXT_CHANGE# 和 NEXT_TIME 这两个列值 ; 5 如何查看此数据库最后一个提交的 SCN 号? 4) 日志切换 a) 日志的切换规律 : 默认情况下, 日志成员写满数据时发生切换 ; 优先覆盖 sequence# 最小的日志组 ; b) 手工切换 :
-->>alter system switch logfile 1) 特点 : a) 日志切换后, 原来组的信息将被覆盖 ; b) 不能切换到处于 active 状态的日志组 ; c) c) 监控日志切换频率 : 1. 命令 : 日志的切换信息都在 v$log_history 表中 select to_number(to_char(first_time,'yyyymmddhh24')) FIRST_TIME,count(*) from v$log_history group by to_number(to_char(first_time,'yyyymmddhh24')) order by 1; 如图 : 即记录了一个小时之内日志组切换的频率 : 2. 特点 : a) 切换频率与日志成员大小, 成员数量有关 ; b) 联机日志一般 30 分钟切换一次 ; c) 日志切换延迟会影响数据库性能 ; --- 原因 : 脏块在写之前必须先写日志, 日志无法写到磁盘那么数据的修改就会受影响, 可能会挂起, 或者进行大数据量修改时直接报错无法进行 d) 为什么日志切换频繁会造成大量的 active 日志块? 数据成功写盘的标记 : 即是做 recover 的时候可以通过这个写盘标记来判断哪些操作要重做, 哪些操作不需要再做 : 通过检查点, 查看日志文件信息 :
d) 如何解决日志文件频繁切换问题 1) 增加日志组 ; 由于 : 现有日志组成员的大小无法改变, 比如现在是每个 50m, 不能改成 100m; 所以 : 必须通过增加新的日志组, 增加新的成员来扩大日志组空间 1. 增加日志组 成员 : --- 新增日志组 group3, 并增加一个新成员 redo03.log, 大小设置为 100m alter database add logfile group 3 '/home/oracle/db01/redo03.log' size 100m; --- 已有 group1, 现在重建日志组 group1, 创建日志组成员 :redo01 redo02 --- reuse 即重新使用, 覆盖掉以前的 ; alter database add logfile group 1 '/home/oracle/db01/redo01.log' size 100m reuse; alter database add logfile group 1 '/home/oracle/db01/redo02.log' size 100m reuse; 2) 删除组 : alter database drop logfile group 1; 注意 : 1 当前组不能删 ; 2 活动组不能删 ( 处于 active 状态的称为活动组 ); 3 只有两个组的不能删如何增加成员? 为什么增加成员? 为了增强安全性, 做多路复用 ; 如果有个别日志文件丢失不影响工作 ; *** 只要有一个日志文件有效均可以启动数据库 : 3) 增加成员 : 1. 增加成员 : 多路复用 alter database add logfile member '/home/oracle/db01/redo01b.log' to group 1, '/home/oracle/db01/redo02b.log' to group 2, '/home/oracle/db01/redo03b.log' to group 3; 2. 删除成员 : 本质是删除控制文件中的指针, 物理文件没有删除, 命令如下 : -->>alter database drop logfile member '/home/oracle/db01/redo01b.log'; 注意 : 当前组的成员不能删每一组最后一个成员不 能删 3. 查看每个日志组对应的成员信息 : select * from v$logfile 查看物理文件 :
4. 成员丢失怎么办? 5) 移动日志文件 : 移动日志文件注意三点 : 1. 数据库必须 mount: -->> shutdown immediate; -->>startup mount 2. 目标文件必须存在 ; SQL>!mv /home/oracle/db01/redo02b.log /home/oracle/redo02b.log SQL>!mv /home/oracle/db01/redo03b.log /home/oracle/redo03b.log 3. 修改控制文件指针 ; SQL> alter database rename file '/home/oracle/db01/redo02b.log' to '/home/oracle/redo02b.log'; 4. 打开数据库 : SQL> alter database open; 延伸知识 : *** 延迟块清除!!! 是指由大量脏数据要写, 触发日志写以后,undo 空间的老镜像数据的事务槽信息不回立即释放 ; 在这种情况下 select 查询可能会产生 redo 信息 6) 剖析查看表的 8K 块, 对应的理解 redo file 中 redo 条目的记录 : update 的完整操作 : 1 读数据 ; 2 database 中做镜像到内存 ; 3 写镜像块的 redo 条目 (log buffer); 4 修改原始块数据 ;
5 写修改块的 redo 条目 ; *** 如果 log buffer 的日志没有写盘, 掉电 SCN 查看成功写盘的最后一条 redo, 在 10g 11g 中查看 : 当块 :?? 指将一个二进制文件变成文本文件才能查 看 ; 数据库如何实现数据一致 : mount 前滚 open 回滚 undo 空间中记录了事务标记信息 ( 事务槽中有内容表示这个事务未提交 );??? 事务槽的作用? 每个数据块上都有事务槽, 一个事务的开始会给 这个数据块一个事务
第十一章 归档模式 定义 : 即做 redo log 的备份 查询 : SQL> select log_mode from v$database; SQL> archive log list;( 内容如下 ) Database log mode Automatic archival Archive destination Archive Mode Enabled Oldest online log sequence 39 Next log sequence to archive 41 USE_DB_RECOVERY_FILE_DEST --> 归档文件的默认位置指向闪回区 Current log sequence 41 *** 注意 : Database log mode ---NO Archive Mode 表示未开启归档 ; Archive destination --- 指向归档日志存储目录, 如果安装时开了闪回区, 那么会显示一个初始化参数 USE_DB_RECOVERY_FILE_DEST, 此参数代表的是闪回区, 使用以下命令查看其闪回区指向的路径 : SQL> show parameter DB_RECOVERY_FILE_DEST; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string /u01/app/oracle/admin/ncfc db_recovery_file_dest_size 记录闪回区的路径 :/u01/app/oracle/admin/ncfc 闪回区的最大限值 : 是 2G *** 如果安装数据库时没有安装闪回区怎么办? big integer 2G 第一节开启归档模式 : 注意 : 必须正常停库后, 在 mount 状态下做转换!---why? 原因 : 如果是脏库,start 数据库需要做实例恢复, 但数据库到 mount 下只完成实例前滚, 到 open 下做后滚才完成实例恢复 而切换归档必须在 mount 下做, 此时数据库没有完成实 例恢复就会报错! 延伸 : 查看数据库是否是正 常关闭的? SQL> select file#,checkpoint_change#,last_change# from v$datafile; *** checkpoint_change# 和 last_change# 值一致代表是正常停库!
如果两个值数据不一致, 或者 last_change# 为空代表是非正常停库! 注意 : 归档与非归档模式的区别 : 归档模式下每次联机日志发生切换时都会将当前组备份出来生成归档文件! 1) 正常停止数据库, 数据库 mount 状态 : SQL> shut SQL> sartup immediate; mount; 2) 开启归档模式 : SQL> alter database archivelog; 3) 打开数据库 : SQL> alter database open; 第二节 闪回区管理 1) 查看闪回区信息 : SQL> archive log list; --- 查看是否有闪回区的初始化参数 SQL> show parameter DB_RECOVERY_FILE_DEST; --- 参看闪回区路径 SQL>!tree / 闪回区指向的目录, 查看闪回区有哪些已归档日志 如图 : 注意 :Oracle 会自动在闪回区创建目录 archivelog/ 并根据时间创建归档日志! 2) 维护闪回区 应用情景 : 归档日志在闪回区, 由于闪回区空间过小, 日志切换被挂起, 日志无法归档, 数据库无法正常运行, 前台应用暂停, 通过维护闪回区解决日志挂起 注意 : 如果归档不能进行, 在 10.2.0.1 版, 会屏蔽普通用户连接数据库, 相当于只有管 理员才能用! 解决步骤 :
a) 查看数据库当前 Oracle 正在等待哪些事件 SQL> select event from v$session where username is not null; 注意 :event 显示 switch logfile command, 即正在等待日志切换 ; 确定是日志切换挂起后, 就要想到是日志归档的问题! b) 查看归档有无错误 : SQL> select error from v$archive_dest; 报错信息如下 : ERROR ----------------------------------------------------------------- ORA-19809: limit exceeded for recovery files 注意 : 锁定问题就是超出闪回区空间限制! c) 查看闪回区空间大小 SQL> show parameter DB_RECOVERY_FILE_DEST; d) 监控闪回区空间使用情况 : SQL> select * from v$recovery_file_dest; 注意 : 上例中,space_limit 约为 2M, 但是已经使用了 3M 多, 是因为原来闪回区是 100M, 已经使用了 3M 多, 但是为了实验需要所以将闪回区 alter 为 2M, 再来切换日志, 以造成日志切换挂起的状态! 闪回区可以改小到现数据占有的空间大小, 但是日志文件都不会丢失! e) 解决闪回区空间不足的问题 注意 : 直接删除闪回区的物理文件是不能释放闪回区空间位置! 必须使用 rman 释放控制文件指向闪回区的指针, 然后才能释放! 所以通过以下两种方法解决 方法一 : 增大闪回区空间注意 : 增大闪回区后, 由于数据库更新慢造成日志切换仍挂 起, 可以采用手工归档解决! 1) 查看每一个归档文件大小 : select name, sequence#,block_size,blocks*512/1048576 from v$archived_log; ***blocks 指所占用的数据块的总数, ***blocks*512/1048576 即计算总个个数所占的空间并换算成 M 单位! 2) 查看所有归档文件的大小 :
select sum(blocks)*512/1048576 from v$archived_log where name is not null; 3) 修改闪回区空间大小 alter system set db_recovery_file_dest_size=100m; 4) 查看归档报错 select error from v$archive_dest; 5) 使用 server process 手工归档 alter system archive log sequence 45 to '/home/oracle/'; 方法二 : 修改存档文件的位置 : 1) 引申 : log_archive_dest_n --> n 的取值范围是 1~10 注意 :Oracle10g 的 log_archive_dest_n 参数 n 的取值可以是 1-10 均可, 在 11g 时 log_archive_dest_n 参数 n 的取值是 1-31, 但是 1-10 支持本地目录,11-31 支持的网络连接地址, 不能使用 location; 注意 : 增加归档日志路径主要应用与主从数据库设置中! 在 Oracle10g 中主库一般设置为 log_archive_dest_10, 从库据节点序号依次从 log_archive_dest_1 至 log_archive_dest_9, 这样方便管理! 2) 创建归档日志的目录 : mkdir /home/oracle/arc_db01_dest10/ 3) 修改控制文件指针指向 : alter system set log_archive_dest_10='location=/home/oracle/arc_db01_dest10/'; 4) 修改位置以后日志文件的命名格式注意 : 存档终点不在闪回区, 文件 格式由 log_archive_format 参数指定 SQL> show parameter log_archive_format NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_archive_format string %t_%s_%r.dbf 注意 : %t --> 线程号 select thread# from v$log; %s --> 序列号 select sequence# from v$log; %r --> select resetlogs_id from v$archived_log; 5) 切换日志, 到新的存档终点查看新生成的归档日志文件
f) 归档空间预留大小计算 1) 查看日志块的大小 : SQL> select BLOCK_SIZE from v$archived_log where rownum=1; 2) 按月计算归档文件的大小, 确定存档终点需要预留的空闲空间 : select to_number(to_char(first_time,'yyyymm')) FIRST_TIME, sum(blocks*512)/1024/1024 from v$archived_log group by to_number(to_char(first_time,'yyyymm')) order by 1; 注意 : 这里的 512 是日志块大小, 可以查看 : 在 11g 可以通过 select * from v$log; 在 10g 中可以查看 select LEBSZ from x$kccle; 也可以从 v$archived_log 查看 (block_size); 注意 : 在不同的平台下, 日志块的大小有差异 : hp 下是 1024,Linux 下一般是 512; 日志文件数据块 size:215k; 日志文件 I/O 单位最小控制文件数据块 size:16k; 数据文件块数据块 size:8k 第三节归档日志管理 : 1) 查看归档日志是否有错误 : SQL>select dest_id,error from v$archive_dest;
*** 小插曲 :undo 空间不够也会造成归档失败, 如图 : 2) 查看归档进程数量 注意 : 归档进程在 v$bgprocess 动态视图中, 因为归档进程属于 background 进程的一种 ; 增加归档进程数量 : SQL>select name from v$bgprocess where name like 'ARC%' and paddr<>'00'; 注意 :10g 可以有 10 个路径的归档,11g 可以有 30 个路径的归档 3) 增加归档进程数量 : SQL> alter system set log_archive_max_processes=6; 注意 : 通过修改初始化参数增加 ( 主要是在主备时需要, 主库一个路径, 如果 库那么就要多指定 9 个多路径 ) 问题 : v$bgprocess 动态视图中列 PADDR 是什么? 9 个从
4) 修改存档文件的位置 : 1) 查看当前归档日志的存放路径 : 2) 修改存档文件位置 : 详见 9.2.2.5 alter system set log_archive_dest_10='location=/home/oracle/arc_db01_dest10/' 注意 : 指向的目录一定要存在! 还有 Oracle10g 与 Oracle11g 归档多路径的区别! 5) 数据库的几种角色 1) 查看当前数据库的角色 : SQL> select database_role from v$database; DATABASE_ROLE ---------------- PRIMARY 2) 如何创建从库? 建从库 : stand ----primary 代表主库 第四节日志挖掘 (log miner)
1) 定义 : 通过 oracle 提供的系统包 (dbms_logmnr) 对日志进行反算, 把 redo 条目反算成 sql 语句 ; 反算的工具两个包 :dbms_logmnr start_logmnr a) 应用场景 : 数据破坏后的不完全恢复 2) 数据不完全恢复步骤, 反算日志 a) 查找归档日志 select sequence#,name,first_time from v$archived_log; b) 生成挖掘队列 execdbms_logmnr.add_logfile('/home/oracle/arc_db01_dest10/1_58_865260534.dbf',dbms_logmnr.new); exec exec dbms_logmnr.add_logfile('/home/oracle/arc_db01_dest10/1_59_865260534.dbf',dbms_logmnr.addfile); dbms_logmnr.add_logfile('/home/oracle/arc_db01_dest10/1_60_865260534.dbf',dbms_logmnr.addfile); c) 开始日志挖掘 exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog); d) 查看挖掘结果 select scn,sql_redo from v$logmnr_contents where lower(sql_redo) like 'update%' and seg_name='emp' and seg_owner='scott'; select sql_undo from v$logmnr_contents where lower(sql_redo) like 'update%' and seg_name='emp' and seg_owner='scott'; select scn,sql_redo from v$logmnr_contents where lower(sql_redo) like 'drop%' and seg_name='e01' and seg_owner='scott'; 注意 : sql_redo: 前 滚 sql_undo: 回滚 :
update 命令会产生两条 SQLredo: 一个是修改数据的 redo, 一个是在 undo 空间产生镜像数据时的 redo, 这条 redo 相对数据恢复就是做 UNDO 恢复的 ; 注意 : 如果是 ddl 语句, 比如 drop 命令, 那么没有办法通过日志恢复删除数据, 但是可以通过日志反算得到 SCN, 找到做删除操作的时间点, 通过备份找回数据! 注意 :rowid 折行是不能进行 SQL 运算的 ; 命令 :top -u oracle: 查看 oracle 进程使有 CPU 的情况 第十二章 数据文件 1) 美化 sqlplus 页面 su - oracle cd /u01/app/oracle/product/11.2/db_1/sqlplus/admin vi glogin.sql 编写以下内容 : set timing on -->> 输出值时显示返回时间 ; set pages 200 -->> 设置分页, 每页 200 行,0 代表不分页 set lines 150 -->> 设置每行不超过 150 个字符 ; set long 50000 -->> 将显示信息的长度设置为 50000 set pages 300 set lines 150 col table_name for a25 col member for a45 col file_name for a45 col status for a10 col tablespace_name for a12 set long 50000 2) 定义和分类 a) 定义 表空间是一个大的容器, 逻辑存在, 在 tablespace 中有 : Datafile: 物理存在的, 相当于一个一个的容器 table: 逻辑存在 ; index: 逻辑存在 b) 分类
1) 安表空间里面内容分类 : 永久 :PERMANENT 临时 : TEMPORARY 回退 :UNDO 2) 安表空间大小和里面的文件数分类 : 小文件表空间大文件表空间 : 3) 元数据 -- 在数据字典中取出建立对象的的语法 a) 取出 users 表空间的元数据 SQL> select dbms_metadata.get_ddl('tablespace','users') from dual; b) 一句话取出所有表空间的元数据 SQL>select dbms_metadata.get_ddl('tablespace',tablespace_name) ';' from dba_tablespaces; c) 一句话取出所有表创建的元数据 SQL>select dbms_metadata.get_ddl('table',table_name) ';' from tabs; SQL>select dbms_metadata.get_ddl('table',' 表名 ') from dual; d) 一句话取出所有索引创建的元数据 SQL>select dbms_metadata.get_ddl('index',table_name) ';' from user_indexes; 4) 管理表空间 : a) 查看表空间 SQL> desc dba_tablespaces; SQL> select * from dba_tablespaces;-- 查看表空间信息 SQL> select * from v$tablespace; -- 查看表空间部分属性 b) 创建表空间 ;
创建命令 : 要保证容器的目录存在 : SQL> create tablespace mao datafile '/oradata/orcl/mao01.dbf' size 10m; SQL> create tablespace qq datafile '/oradata/orcl/qq.dbf' size 10m; 默认情况下创建的表空间是小文件和永久表空间, 可以管理 1023 个数据文件, 每个文件的上限是 ( 4M ) 4*1024*1024 个 8K, 即一个小文件表空间容量的上限值为 (4*1024*1024*8K*1023)/1024/1024/1024= 约 32TB; 其中的每个数据文件最大 32G; 另一种方式设置以下参数如图 : 可以直接创建表空间 : SQL> create tablespace mao; c) 查看表空间和数据文件的对应关系 ; 查看表空间 : SQL>select tablespace_name,file_id,file_name from dba_data_files; 对应关系如图 : 如 :SYSAUX( 系统助手 ) 对应的数据文件就是 :/oradata/orcl/sysaux01.dbf d) 创建大文件表空间 (after Oracle 10g);
特点 : 只能管理一个数据文件 ; 数据文件空间管理能力的上限是 (4G) 4*1024*1024*1024 个 8k, 即一个大文件表空间容量的上限值为 4*1024*1024*1024*8/1024/1024/1024= 约 32TB; 创建大文件表空间 : create bigfile tablespace tbsbigfile datafile '/home/oracle/bigfile01.dbf' size 10m; e) 创建非标准块大小的表空间 SQL> show parameter size SQL> alter system set db_16k_cache_size=21m; SQL> create tablespace nn datafile '/oradata/orcl/nn01.dbf' size 10m blocksize 16k; f) 向表空间增加新的数据文件 : alter tablespace users add datafile '/home/oracle/users02.dbf' size 17m; alter tablespace users drop datafile '/home/oracle/users02.dbf'; g) 向指定的表空间创建表查看表存储在哪一个表空间 : create table scott.t01 (id number) tablespace users; create table scott.e01 tablespace tbsbigfile as select * from scott.emp; h) 查看表在哪一个表空间 select table_name,tablespace_name from dba_tables where owner='scott'; i) 表空间的状态 ; 1. 查看表空间状态 ; SQL> select tablespace_name,status from dba_tablespace; 2. 修改表空间状态 ; alter tablespace users read only; SQL> alter tablespace users read write; 不能 read only 的表空间 :system sysaux temp undo 3. 修改表空间 online offline 状态 : SQL> alter tablespace users offline;
SQL> alter tablespace users online; 不能 offline 的表空间 :system temp undo 4. 对表空间状态的测试 : 使用 DML 语句和 drop 语句对处于 read only 的表空间测试! SQL> drop tablespace USERS purge; ---drop 只是将数据字典中此表空间的信息抹去, 但是此表还存在于表空间, 只是释放了安全模式, 一旦表占用的表空间被覆盖, 数据丢失 ; 所以, 处于 read only 状态的表空间是可以被 drop 的, 只是数据不能做 DML 操作 ; 5. 对表空间状态的测试 : 使用 DML 语句对处于 offline 的表空间测试! j) 监控表空间 查看表空间大小 : *** 有两个表空间可以自动增长 :SYSAUX SYSTEM 查看表空间的数据块的当前使用状态 :
计算表空间的空间大小 : 联合查询表空间的空间使用情况 : 根据实际的业务需求对表空间的要求, 来决定哪些表空间需要扩容 ; ***sysaux 表空间和 system 表空间是自动扩容的, 最高可用为 32g; *** 如果在使用表空间时, 遭遇表空间剩余空间不足问题怎么办? *** 启用可恢复的语句功能, 使空间问题报错延迟! 实现原理 : 5) 管理表空间数据文件 a) 修改表空间中数据文件的大小 SQL> alter database datafile 6 resize 20m; b) 修改表空间中的数据文件的名字 SQL> alter tablespace qq offline; SQL> select file_name, tablespace_name from dba_data_files; SQL> host cp '/oradata/orcl/qq.qq' '/oradata/orcl/qq.dbf' SQL> alter database rename file '/oradata/orcl/qq.qq' to '/oradata/orcl/qq.dbf'; SQL> alter database rename file '/oradata/orcl/qq.qq' to '/oradata/orcl/qq.dbf'; SQL> alter tablespace qq online; c) 打开表空间数据文件的自动增长属性 :
SQL>select FILE_NAME,FILE_ID,TABLESPACE_NAME,AUTOEXTENSIBLE from dba_data_files; SQL> alter database datafile 6 autoextend on; d) 分析文件增长的步长 : SQL> select * from dba_data_files; Bytes/1024/1024 是这个表空间数据文件的大小 Blocks 是这个表空间数据文件的的块数 blocks*8*1024=bytes Maxbyetes 这是表空间数据文件的上限大小 increment_by 1 表示以几个块 ( 每块 8k) 进行增长, 值为 1 表示以 1*8k 的大小增长 *** 修改步长增长幅度 : SQL> alter database datafile 6 autoextend on next 1m; *** 修改自动增长的上限 : alter database datafile 6 autoextend on next 10m maxsize 2g; 6) 表空间改名 : SQL> alter tablespace qq rename to yewu; 7) 删除表空间 : SQL>drop tablespace data01 including contents and datafiles cascade constraints; 不能删除的表空间 : System sysaux temp undo 注意 : 数据库的默认永久表空间是不可以 删除的! 查看数据库的默认永久表空间和默认临时表空间 : SQL> select * from database_properties where rownum<4; 修改数据库的默认表空间 : SQL> alter database default tablespace users; 8) 移动表空间 a) 方法一 : 使用移动日志文件的方法 ( 此方法适用于控制文件指向的
文件 ); b) 方法二 : 使用 mv( 只使用与可以 OFFLINE 的表空间 ) 表空间 offline SQL> alter tablespace users offline; 目标文件存在 SQL>!mv /home/oracle/users01.dbf /home/oracle/db01/users01.dbf 修改控制文件指针 SQL> alter tablespace users rename datafile '/home/oracle/users01.dbf' to '/home/oracle/db01/users01.dbf'; 表空间 online SQL> alter tablespace users online; 9) 管理 undo 表空间 a) rollback segment 的定义 保存数据修改前的老镜像, 不能保存任何对象, 即事务回滚段 (rollback segment) b) rollback segment 的作用 a) 为事务提供回退 : rollback; 此处的回退是逻辑回退, 并不是数据块的覆盖, 而是将修改过的数值利用 undo 表空间记录的老镜像, 回退到修改前, 并不是整个 8K 块的覆盖! *** 注意 :rollback segment 记录的只是修改的数据的老值! b) 为事务提供恢复 : 实例崩溃, 然后进行实例恢复的时候, 重新构建数据 c) 提供读一致性 ; 体现 1: 事务内对数据的修改, 其它会话不可见 ; 体现 2: 查询开始后事务修改才提交, 则此查询看不到后来提交的结果 ; --- 即 select 开始的 SCN 号低于 update 提交时的 scn 号 ; 注意 :undo 表空间的数据是回滚的, 允许被覆盖! 默认情况下回滚块是回滚环绕覆盖的! 练习 : 验证 undo 可以为事务提供恢复! 1) 在数据修改时, 监控 rollback segment 的使用情况 : desc v$transaction *** 其中 used_ublk 记录了使用了老镜像的块个数! SQL> select used_ublk from v$transaction; USED_UBLK --------------------- 428 2) 先做循环 update;
3) 监控回滚段 : SQL> select s. u s e r n a m e, t. X I D U S N, t. U S E D _ U B L K from v$session s,v$transaction t where s.saddr=t.ses_addr; 4) 查看回滚段 : SQL> select * from v$rollname where usn=2; USN ---------- ----- NAME -------------------------------- 2 _ SYSSMU2_2738107376$ 另起窗口读取其他窗口正在修改的数据, 会全盘读这个值的老镜像, 直到找到提交之前 的原始数据, 所以会造成其他窗口读取数据缓慢 ; 实验 : 验证 undo 表空间的回滚段是循环覆盖的, 注意, 有开关可以控制回滚段是否能被覆盖! 秒 ; c) undo 表空间的基表操作 1) 创建 undo 表空间 1. 查看使用 undo 的参数信息 : SQL>show parameter undo NAME TYPE VALUE --------------------------------- ----------- ------------------------------ undo_management string AUTO ---->>undo 表空间为自动扩展模式 ; undo_retention integer 900 ---->>undo 表空间可保存数据 900 undo_tablespace string UNDO01 ---->> 当前默认的 undo 表空间 2. 创建 undo 表空间 : SQL>create undo tablespace undo2 datafile '/oradata/orcl/undo01.dbf' size 100m; 2) 修改默认 undo 表空间 SQL>alter system set undo_tablespace=undo2;
注意存在事务的 undo 段不会被切换 alter system set undo_tablespace=undo2; select tablespace_name,segment_name,status from dba_rollback_segs; 3) 查看 undo 表空间的对应数据文件, 是否自动增长! SQL> select tablespace_name,file_name,autoextensible from dba_data_files; 4) 查看哪些回滚段可用 (sys 表空间有自己专门的回滚段, 一个回滚表空间默认有 10 个回滚段 ) SQL> select tablespace_name,segment_name,status from dba_rollback_segs; 注意 1:online 表示可以回滚段,offline 表示不可用! 注意 2: 如果回滚空间不足, 对数据的影响 : 使用 10m 的 undo, 表中数据量在 3w 行左 右, 对数据做修改! 报错如下 : ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDO01' 此时需要增加 undo 表空间, 不然 update 操作无法进行! 5) 查看回滚的空间使用情况 SQL> select s.username,t.xidusn,t.used_ublk from v$session s,v$transaction t where s.saddr=t.ses_addr; 6) 查看回滚段的名字 SQL> select * from v$rollname where usn=2;
d) undo 闪回功能实验 如何找回提交前的数据?--- 依赖回滚镜像能提供的闪回功能!( 丢失时间过长, 只能 通过日志反算, 丢失时间短, 在 undo 的 retention 时间之内的可通过回滚段的闪回功能找 回!) 1) ----- 创建 t1 表添加一行数据 drop table t1 purge; create table t1 as select * from scott.emp where empno=7900; select * from t1; 2) 闪回版本查询 : select empno, versions_starttime, versions_endtime, versions_xid, versions_operation, sal from t1 versions between timestamp minvalue and maxvalue order by versions_endtime,versions_starttime; *****where empno=7900; 3) update t1 set sal=1000; commit; update t1 set sal=2000; commit; update t1 set sal=3000; commit; select * from t1; 在对 t1 表中的 empno=7900 的 sal 做了多次修改提交后, 可通过过以上语句查到各个事务开始 结束的 SCN 号, 以及事务 ID 号, 和在各个事务时修改过的 SAL 的值 通过查看以上结果可以找回 7900 员工的工资修改前为 951 注意 : 以上通过 versions 查询的前提条件是, 当前时间距离事务提交时间没有超过 undo 块的 retention 时间 show parameter undo_retention; ( 一般为 900 秒 ), 如果超过 900 秒, 则以上查询失效 4) 修改 database 参数 SUPPLEMENTAL_LOG_DATA_MIN 值必须是 yes! desc v$database; select SUPPLEMENTAL_LOG_DATA_MIN from v$database; alter database add SUPPLEMENTAL log data; 5) 查询出让数据回退到某个阶段的的 sql desc flashback_transaction_query;
select undo_sql from flashback_transaction_query where table_name='t1'; 6) 选择一个数据回退的位置, 进行验证 update "SYS"."T1" set "SAL" = '951' where ROWID = 'AAASxDAABAAAVyhAAA'; commit; select * from t1; 问题 : 超过 900 秒的 retention 时间后, 如何找回数据? a) 如果知道数据修改前的 SCN 号, 即可通过以下方法查询 : SQL> select empno,sal from emp as of scn 506970 where empno=7369; EMPNO SAL ---------- ---------------------------- 7369 800 在 versions 查询中可以看到将 sal 从 800 改为 99 时的 scn 号从 506977--506997, 即 scn 号在 506977 之前的时刻,sal 的数值是未修改的, 即可找回丢失前的数据 b) 如果不知道数据修改前的 SCN 号, 即可通过以下方法查询 : SQL> select empno,sal from emp as of timestamp (sysdate-5/1440); 7) 如何将查询到的老镜像数据导出 ; a) 利用查询的结果导出以下是将整张表还原 : SQL> insert into emp select * from emp as of timestamp sysdate-40/1440; b) 事务反算, 利用 undo_sql 还原 : 只有 sys 用户可以使用闪回区! 使用 scn 号 : SQL> select undo_sql from flashback_transaction_query where commit_scn='510246'; 使用会话 XID 号 : SQL> select undo_sql from flashback_transaction_query where xid='1b00000011000000'; 注意 : 事务反算查询, 利用闪回区的老镜像数据使用 undo_sql 语句做反算操作, 还原
老数据 ; 反算事务查询不遵循 retention900 秒的限制, 只要事务段的老镜像没有被覆盖都能通过此方法找回!!! 利用事务反算时, 有可能部分数据被覆盖, 导致不完全恢复, 丢失部分数据, 只能通过 日志反算找回 ; 问题 : 先修改数据, 提交 ; 再删除数据, 再提交, 然后通过闪回区找回! 1 先根据 xid 找回被删除的语句, 即 undo_sql 中的 insert 语句, 运行, 提交 2 再根据 xid 找回修改的语句, 即 undo_sql 中的 update 语句 在 SQL 中运行此 update 语句时, 提示 0 行改变, 为什么呢?--- 推测 :ROWID 改变 update "SCOTT"."EMP" set "SAL" = '5000' where ROWID = 'AAADR1AABAAAIBpAAW'; 0 rows updated. --- 问题原因 : 在做第一次 undo_sql 后,insert 数据后数据的 ROWID 发生改变, 此时再用 update 时记录的 rowid 无法恢复 ; 实验验证 :AAADR1AABAAAIBpAAW(rowid 的构成 : 对象编号 - 文件编号 - 块编号 - 行号 ), 一般 删除后再 insert, 其 rowid 的行号均会发生改变 --- 解决 : 使用 flashback table, 即对当前到指定的 SCN 号中建做过的所有事务做连续回退,Oracle 会自动去组织这些事务的前后逻辑关系, 然后将数据恢复到指定的 SCN 状态! 但是需要准确的 SCN 号, 如果使用时间会不精确, 会造成数据恢复出错! 实验验证 : 1) 打开行移动 : 2) 创建表, 插入数据,commit, 并查看此时的 SCN 号 ; 查看当前 SCN 号 : SQL>select current scn 3) update 操作,commit, 查看此时的 SCN 号 ; 4) delete 操作,commit, 查看此时的 SCN 号 ; 5) 闪回数据 : SQL>select empno,sal from e01 where deptno=10;
*** 使用其他方法找 SCN 号 e) 手工管理 undo 表空间的段 1. 创建表空间 u2, 并设置 u2 为默认 undo 表空间 show parameter undo; create undo tablespace u2 datafile '/oradata/orcl/u01.dbf' size 100m; alter system set undo_tablespace=u2; 注意 : 在 Oracle10g 以后均不需要人为手工的去管理 rollback segment,oracle 都提供自动管理 rollback segment 的功能 2. 设置 undo 表空间管理为手动, 并重新启动数据库 alter system set undo_management=manual scope=spfile; alter system set undo_management=auto scope=spfile; shutdown; startup; show parameter undo; 3. 手工建立回滚段 r1,r2 select * from v$rollname; create rollback segment r1 tablespace u2; create rollback segment r2 tablespace u2; select * from v$rollname; 4. 手动 online 回滚段 alter rollback segment r1 online; alter rollback segment r2 online; select * from v$rollname; 5. 给事务设置一个回滚段 set transaction use rollback segment r2; 6. 设置重启混滚段在线并重启数据库 show parameter rollback alter system set rollback_segments=r1 scope=spfile; alter system set rollback_segments=r2 scope=spfile; select * from v$rollname; f) undo 中常见的异常
第一种异常 :undo 表空间大小不足! ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDO02' 第二种异常 : g) 模拟 01555 错误 一 使用参照游标模拟 01555! 1. 在 sqlplus 中声明的变量叫主机变量, 和 plsql 中声明的变量不同! 2. 创建一个游标 : 打开游标, 查看 e02 表中 sal 列的值, 在另一个 session 使用 scott 登录, 对 e01 进行修改! 注意 :undo 回滚段值要小,e01 表的修改操作数量要大, 通过另一窗口查看 E01 的老镜像的值才有可能被回滚覆盖! SQL> var c1 refcursor; SQL> exec open :c1 for select * from e01; 注意 :exec open 即借助游标, 锁定查询的时间点在这个时刻, 后面无论什么时候 print c1 其结果都是在 EXEC 打开游标时指定的结果集, 借此在 e01 表修改前锁定老镜像数据, 在 e01 修改数据并提交后, 再打印输出 C1 还是指向的老镜像数据, 此时如果回滚块的老镜像数据被覆盖, 则会出现 01555 错误! SQL> print c1; 3. 在 scott 下对 e01 表做大量修改操作 : SQL> begin 2 for i in 1..2000 loop 3 update e01 set sal=3; 4 end loop; 5 end; 6 / 二 借助事务隔离级模拟 01555 SQL> set transaction isolation level SERIALIZABLE;
注意 :Oracle 支持两种读取 : a) 提交读 :READ COMMIT 即事务在没有提交之前所做的修改其他会话无法查看, 只有 commit 后才能看到! b) 串行读取 ; 每个会话看到的是一个独立的数据状态 ; 即 select 查询不遵循 commit, 一个数据正在修改, 即使没有 commit, 另一个会话 select 查询发出后, 会读取最近的一个 SCN 号指向的数据结果, 即在此之前作的数据修改即使没有 commit 也能 select 查看到其最新结果! 例如 : 设置事务隔离级就是一种串行读! 注意 : 系统回滚段 : 只为系统表空间服务, 非系统表空间只能使用非系统回滚段! h) 如何避免 01555! 将 undo 表空间的 retention 属性改为 guarantee( 即事务提交后老镜像强制保留 900 秒 )! SQL> alter tablespace UNDO03 retention guarantee; 在这段时间里如果老镜像没有被释放, 而需要做大量修改操作并提交, 那么会提示 UNDO 空间不足! i) undo 表空间的属性 1) retention 属性 表空间属性 :retention( 只有 undo 表空间支持 retention 属性 ), 如果 retention 属性状态为 guarantee, 则 undo 表空间的老镜像数据提交后还能保留 15 分钟, 如果为
NOGUARANTEE, 则不确定事务提交后老镜像能被保留 15 分钟, 可能会被循环覆盖! 1. 查看 undo 表空间的 retention 参数 : SQL> show parameter undo_retention; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_retention integer 900 2. 修改 undo 表空间的 retention 参数 SQL> alter system set undo_retention=180 scope=spfile; 3. 查看 guarantee 属性 : SQL> select tablespace_name,retention from dba_tablespaces; TABLESPACE_NAME RETENTION -------------------- ----------- SYSTEM NOT APPLY SYSAUX NOT APPLY UNDO01 NOGUARANTEE TEMP NOT APPLY DATA01 NOT APPLY UNDO02 NOGUARANTEE UNDO03 NOGUARANTEE 4. 修改 undo 表空间的 retention 属性 : SQL> alter tablespace UNDO03 retention guarantee; 2) 回退保持力和强制回退保持力 (guarantee 属性 ) SQL> select TABLESPACE_NAME,RETENTION from dba_tablespaces order by 2; TABLESPACE_NAME ------------------------------ ----------- UNDO02 UNDO01 RETENTION NOGUARANTEE NOGUARANTEE SQL> select TABLESPACE_NAME,RETENTION from dba_tablespaces order by 2; TABLESPACE_NAME RETENTION ------------------------------ ------------------------------------------------------------------- UNDO01 GUARANTEE --> 老镜像必须保留 900 秒 UNDO02 NOGUARANTEE 注意 :noguarantee: 即事务提交后, 老镜像有可能不会保留 900 秒 ; guarantee: 即事务提交后, 老镜像必须保存 900 秒 查看 : SQL> show parameter undo; j) NAME TYPE VALUE ------------------------------------ ----------- ----------------- undo_retention integer 900 undo_tablespace string UNDO03 如何让回滚段不存在 : -------------
回滚段涉及一个初始化参数 :undo_management! a) 修改 undo_management 初始化参数 : SQL> alter system set undo_management=manual scope=spfile; b) 查看回滚段不存在对事务的影响 : SQL> select segment_name,tablespace_name,status from dba_rollback_segs; ORA-01552: cannot use system rollback segment for non-system tablespace 'USERS' 注意 : 如果用户回滚段不存在, 那么无法对用户表做 DML 操作, 但是可以做 drop; c) 如果回滚段不存在, 可以自己创建回滚段, 或者修改回滚段的现有状态 : 将 undo 的管理修改为自动回退 : 10) 管理临时表空间临时表空间中保存临时表的数据和排序的中间结果, 不能保存永久数据 a) 临时表空间基本操作 1) 查看当前已有的临时表空间 SQL> select tablespace_name,contents from dba_tablespaces where contents='temporary'; 注意 : 必须是 contents= TEMPORARY 的表空间才是临时表空间, 并非 temp 打头的就是临时表空间, 查看每个表空间属于哪一类必须看 CONTENTS 2) 临时表空间与临时文件的关系 :dba_temp_files SQL> select tablespace_name,file_name,file_id from dba_temp_files;
注意 : 即临时表空间 TEMP02 下有一个临时文件为 temp02.dbf 和 temp03.dbf, 此文件的编号为 1; 3) 查看 database 默认的临时表空间和默认的永久表空间 SQL> select * from database_properties where rownum<4; 注意 :DEFAULT_TEMP_TABLESPACE: 默认临时表空间为 TEMP; DEFAULT_PERMANENT_TABLESPACE: 默认永久表空间为 SYSTEM. 4) 设置 database 默认的临时表空间 : ---- 设置默认临时表空间为 temp2 SQL>alter database default temporary tablespace temp2; 5) 查看用户使用的默认临时表空间和默认永久表空间 SQL> select username,temporary_tablespace,default_tablespace from dba_users; 注意 : TEMPORARY_TABLESPACE 代表用户当前使用的临时表空间也是排序空间, DEFAULT_TABLESPACE 代表用户使用的永久表空间 这里 scott 用户使用的是 temp03; 6) 修改用户的临时表空间和永久表表空间 SQL> alter user scott temporary tablespace temp2; SQL> alter user mao default tablespace mao; b) 管理临时表空间 1) 创建临时表空间 create temporary tablespace temp02 tempfile '/home/oracle/temp02.dbf' size 10m; 注意 : 创建临时表空间 temp02 的同时创建临时文件 temp02.dbf ***tempfile 与 datafile 的区别 :
对 tempfile 的数据修改不会产生 redo 日志 ; 因为 tempfile 的数据来源于 datafile, 因此可以提高效率! 2) 删除临时表空间 SQL>drop tablespace temp03 including contents and datafiles; 注意 : 默认临时表空间不允许删除 drop tablespace temp02 including contents and datafiles; ORA-10921: Cannot drop tablespace belonging to default temporary tablespace group 3) 给临时表空间添加临时文件 : SQL> alter tablespace temp02 add tempfile '/home/oracle/temp03.dbf' size 10m; 4) 删除临时表空间的临时文件 : SQL> alter tablespace temp01 drop tempfile '/home/oracle/temp01.dbf'; 5) 创建临时表查询所有临时表 SQL>desc dba_tables; SQL>select * from dba_tables where temporary='y'; SQL>select owner,table_name,duration,temporary from dba_tables where temporary='y'; 取出临时表的与数据 SQL>select dbms_metadata.get_ddl('table','atemptab$') from dual; 事务临时表 SQL> select dbms_metadata.get_ddl('table','cluster_nodes') from dual;
会话临时表 SQL>create global temporary table temp1 on commit delete rows as select * from emp; SQL>create global temporary table temp2 on commit preserve rows as select * from emp; --- 用户正在使用的临时表 SQL>select * from v$sort_usage; 问题 : 在 sys 用户下创建临时表失败, 报错, 为什么? SQL> create global temporary table tmp_e01 on commit preserve rows as select * from e02; ORA-25153: Temporary Tablespace is Empty c) 临时表深入解析 a) 跟踪临时段 (3 种情况会使用 ) 1. 跟踪临时表对临时段的使用 (v$sort_usage) SQL> select USERNAME,TABLESPACE,BLOCKS from v$sort_usage; USERNAME TABLESPACE BLOCKS ------------------------------ ------------------------------- ---------- SCOTT TEMP02 128 2. 查看临时段与临时表空间之间的关系 : SQL> select tablespace_name,file_name from dba_temp_files; TABLESPACE_NAME FILE_NAME --------------- ------------------------- TEMP02 /home/oracle/temp02.dbf TEMP02 /home/oracle/temp03.dbf TEMP01 /home/oracle/temp01.dbf 3. 排序时对临时表的使用 : Oracle 如何实现数据结果集排序?-- 排序是在 pga 中进行的 场景 : pga 内存过小, 在排序时表的大小超过 pga 内存, 则 pga 会将需要排序的结果集分段进行局部排序, 然后将局部排序结果放到临时表的临时段中, 此时监控临时段可以看到被使用的临时空间 临时段 block 块信息 排序结束, 临时段即释放! SQL> select USERNAME,TABLESPACE,BLOCKS from v$sort_usage; USERNAME TABLESPACE BLOCKS ------------------------------ ------------------------------- ---------- SCOTT TEMP02 896 SQL> select USERNAME,TABLESPACE,BLOCKS from v$sort_usage; no rows selected
4. which 子句的结果也会保存到临时表空间 ; b) 临时表空间扩容 1. 查看临时表空间当前大小 : SQL> select USERNAME,TABLESPACE,BLOCKS from v$sort_usage; 或者在 dba_temp_files 中查看 ; 2. 临时表空间扩容, 三种方法 1) 修改尺寸 *** 查看当前临时数据文件的大小 : SQL> select file_name,bytes,blocks,maxblocks from dba_temp_files; FILE_NAME BYTES MAXBLOCKS ------------------------------ ---------- ---------- -------------------------------------- /home/oracle/temp02.dbf 10485760 1280 0 /home/oracle/temp03.dbf 104857601280 4194302 /home/oracle/temp01.dbf 10485760 1280 0 /home/oracle/temp011.dbf 209715202560 0 *** 修改 tempfile 大小 : SQL> alter database tempfile '/home/oracle/temp01.dbf' resize 20m; 2) 打开自动增长 : SQL> alter database tempfile '/home/oracle/temp03.dbf' autoextend on; SQL> alter database tempfile '/home/oracle/temp01.dbf' autoextend off; *** 查看是否打开自动增长 : SQL> select tablespace_name,file_name,autoextensible from dba_temp_files; 3) 给表空间增加数据文件 : SQL> alter tablespace temp01 add tempfile '/home/oracle/temp011.dbf' size 10m; c) 管理临时文件 : 1. 查看当前临时文件信息 :dba_temp_file SQL> select tablespace_name,file_name,file_id,blocks from dba_temp_files; TABLESPACE_NAME FILE_NAME FILE_ID BLOCKS ------------------------------ ------------------------------ ---------- ---------------------------- TEMP02 /home/oracle/temp02.dbf 1 1280 TEMP02 /home/oracle/temp03.dbf 2 1280 TEMP01 /home/oracle/temp01.dbf 3 2560 2. TEMP01 /home/oracle/temp011.dbf 4 2560 数据库 open 状态下如何解决临时文件丢失 ; 场景 : 临时文件被误删除, 但是控制文 件指针还在, 此时再使用这个临时表空间排序会报错 ; 步骤 : 1 删除控制文件的指针 ; SQL> alter database tempfile '/home/oracle/temp02.dbf' drop; 2 添加新的临时文件 ; SQL> alter tablespace temp02 add tempfile '/home/oracle/db01/temp02.dbf' size 10m; 注意 : 如果临时文件丢失, 数据库重启会自动创建临时表空间文件, 以上方法是解决生产库不能轻易 down 的情况下添加新的临时文件 d) 移动临时文件 方法一 : 与找回删除临时文件的方法相似删除老 的指针 ;