ORACLE数据库基础知识
| ORACLE | MYSQL | |
| 使用范围 | Oracle是大型的数据库。 Oracle是收费的，且价格昂贵 | Mysql是中小型数据库。 Mysql是开源的 |
| 安装部署 | Oracle有3G左右，且使用的时候Oracle占用特别大的内 存空间和其他机器性能。 | Mysql安装完后才152M |
| 数据应用 | 并发性是ORACLE数据库最重要的特性，并发性涉及到 资源的获取、共享与锁定。 Oracle保证提交的事务均可以恢复，如果出现数据库或 者主机异常重启，重启Oracle可以靠联机在线日志恢复 客户提交的数据。 | Mysql以表锁为主，对资源锁定的力度很大，如果一个session对一个 表加锁时间过长，会让其他session无法更新此表的数据。 Mysql默认提交sql语句，但是如果更新过程中出现db或者主机重启的 问题，也可能会丢失数据。 |
| 性能指标 | Oracle有各种成熟的性能诊断调优工具，能实现很多自 动分析 、 诊断功能 。 比 如 awr 、 addm 、 sqltrace 、 tkproof等 。 | MySQL的诊断调优方法较少，主要有慢查询日志。 |
如果把Oracle比作一部汽车，instance相当于汽车的发动机一样,启动oracle前提应先启动instance.
Instance=SGA+background process实例=内存分配+一组后台进程
内存结构
主要包括sga（system global area）和pga（program global area）。 SGA(System Global Area）由一组内存结构组成，它是由所有用户进程共享的一块内存区域。启动例程时， Oracle自动分配SGA，关闭例程时，oracle自动释放SGA所占用的内存空间。 SGA占物理内存的1/3--1/2,大小可由参数文件内参数计算shared pool(共享池)， database buffer cache(数据缓冲区), redo log buffer(重做日志缓冲区) (如以下图所示) SGA=share_pool_size+db_block_size*db_block_buffers +log_buffers ? ORACLE 10g中的SGA_MAX_SIZE与SGA_TARGET参数 SGA_MAX_SIZE指的是可动态分配的最大值﹐而SGA_TARGET是当前已分配的最大sga。 ? Oracle 11g之后加入新特性:Memory_target与自动内存管理 MEMORY_MAX_TARGET,MEMORY_TARGET参数：将PGA+SGA作为整体内存使用被一致的纳入自动管理 共享池（Shared pool）：Oracle引入共享池的目的就是共享SQL或PL/SQL代码，即把解析得到的SQL 代码的结果在这里缓存，其中PL/SQL代码不仅在这里缓存，同时在这里共享。共享池由两部分组成，即库 高速缓存（Libray cache）和数据字典高速缓存（Data dict cache） 数据库高速缓存区 数据库高速缓冲中存储了最近从数据文件读入的数据块信息或用户更改后需要写回数据库的数据信息， 此时这些没有提交给数据库的更改后的数据称为脏数据。 数据库缓冲区存储DML与查询的所有数据； 数据库缓冲区存放的对象有表、索引、簇、大对象段、回滚段、临时段等； 数据库缓冲区以块为单位来分配与管理； 重做日志高速缓冲区 当用户执行了如INSERT、UPDATE、DELETE、CREATE、ALTER或DROP操作后，数据发生了变 化，这些变化了的数据在写入数据库高速缓存之前会先写入重做日志缓冲区，同时变化之前的数据也放入 重做日志高速缓存，这样在数据恢复时Oracle就知道哪些需要回滚。 PGA(Program Global Area) 用于存放服务器进程的数据和控制信息，独立于SGA的一块内存区域。 当用户连接到Oracle Server时，Oracle server会为每个服务器进程分配相应的PGA.SGA是所有服务器进程都可共享 那个的一块内存区域，而每个服务器进程都具有私有的PGA内存空间。 客户端在通过sql语句连接database时，必须 通过实例Instance来连接和管理数据库。Connection：客户端进程和服务端进程建立通讯。Session：当用户经过 Oracle server认证后开始建立会话，直到用户退出登录，会话结束。 进程结构 1) 用户进程 用户进程是在客户机内存上运行的程序，如客户机上运行的【SQL Plus】、【企业管理器】等。 用户进程向服务器进程提出操作请求。 系统监控进程（SMON） 数据库系统启动时执行恢复性工作，对有故障数据库进行恢复 进程监控进程（PMON） 用于恢复失败的用户进程 数据库写入进程（DBWR） 将修改后的数据块内容写回数据库 日志写入进程（LGWR） 将内存中的日志内容写入日志文件 归档进程（ARCH） 当数据库服务器以归档方式运行时调用该进程完成日志归档 问题：一个数据库软件可以被多个实例访问么？问题：一个数据库实例，只允许一台台服务器，一个用户进行连接（session）么？ ORACLE 启动和关闭 数据库启动 数据库关闭 数据库的启动 数据库的启动过程涉及的3个状态 ● NOMOUNT状态：该状态只打开了数据库实例，此时读取参数文件。 ● MOUNT状态：该状态ORACLE根据参数文件中控制文件的位置找到并打开控制文件，读取控制文件中 的各种参数信息，如数据文件和日志文件的位置等，但是此时并不打开数据文件。 ● OPEN状态：该状态数据库将打开数据文件并进行一系列的检查工作，这些检查工作用于数据恢复。 启动数据库 sqlplus “/as sysdba” SQL> startup ORACLE 例程已经启动。 ORACLE instance started (启动引擎） Total System Global Area 135339940 bytes (开辟SGA区) Fixed Size 454564 bytes Variable Size 109051904 bytes Database Buffers 25165824 bytes Redo Buffers 667648 bytes Database mounted. (连上数据库) Database opened. (打开数据库) nomount阶段 nomount 阶段，可以看到实例已经启动。oracle进程会根据参数文件开创共享内存池。 SQL> startup nomount; ORACLE instance started. Total System Global Area 1904054272 bytes Fixed Size 2254384 bytes Variable Size 1308625360 bytes Database Buffers 587202560 bytes Redo Buffers 5971968 bytes 终端输入命令ipcs –m 查看共享内存情况 [oracle@ljy ~]$ ipcs -m ------ Shared Memory Segments -------- key shmid owner perms bytes nattch status 0x00000000 4 oracle 600 524288 2 dest 0x00000000 32776 oracle 600 16777216 2 dest 0x00000000 9 oracle 600 524288 2 dest 0x00000000 10 oracle 600 524288 2 dest 0x00000000 32781 oracle 600 524288 2 dest 0x00000000 32782 oracle 600 524288 2 dest 0x00000000 32783 oracle 600 524288 2 dest 0x00000000 19 oracle 600 524288 2 dest 0x00000000 32790 oracle 640 4096 0 0x00000000 32791 oracle 640 4096 0 0x2435e184 32792 oracle 640 4096 0 mount阶段，oracle会根据nomount阶段的参数文件来寻找控制文件的名称和位置，一旦查找到立 即锁定该控制文件，控制文件里记录了数据库中的数据文件、日志文件、检查点信息等非常重要的 信息。启动mount时，会先自动启动nomount。 open 阶段 open阶段会根据控制文件记录的信息，定位到数据库文件、日志文件等，正式开启实例和数据库之间的桥梁。如果数据文件或者日志文件缺少，那么open失败。 open之后，如果有使用归档日志功能，便可看到归档日志的进程
数据库的启动 总结： 我们可以使用statup来启动oracle数据库，也可以用shutdwon来关闭。如果使用startup启动，其实已 经经过了三个过程。 总的来说，没有参数文件，实例无法创建，数据库无法nomount成功，没有配置文件，数据库无法 mount；没有数据文件，数据库无法打开使用。 数据库的关闭 服务器关闭的4种方式 正常关闭：(shutdown normal) 数据库正常关闭。应用关闭选项后，不允许有新的连接。所有与数据库连接的用户必须在关 闭数据库之前断开与该数据库的连接 立即关闭：(shutdown immediate) 数据库立即关闭。当前的客户机 SQL 语句立即终止 中止关闭：(shutdown abort) 中止正在被Oracle数据库服务器处理的当前客户机SQL语句 事务处理关闭：(shutdown transactional) 提供一个指定的时间长度。关闭数据库前在该时间内完成事务处理。直到最后一个数据库事 务处理完成后，才关闭数据库 启动和关闭Oracle数据库过程图 问题：如果不小心物理上删除了一Oracle的数据文件，那么ORACLE数据库能不能正常启动？ Oracle读控制文件时，和打开数据库时所面对的参数不一致，Oracle数据库将启动不了， 解决这种问题的方法是把其对应的表空间先卸下，再删除，以保证控制文件描述和物理上 存在文件一致。 SQL>startup mount SQL>alter database datafile ‘/directory/abc.dbf’ offline; SQL>alter database open; SQL>drop tablespace abc; oracle 备份和恢复 数据需要备份的原因有哪些？ 1.介质故障：磁盘损坏，瞬时强磁场干扰 2．用户的错误操作 3．服务器的彻底崩溃 4．计算机病毒 5．不可预料的因素：自然灾害、电源故障、盗窃
数据丢失可以分为物理丢失和逻辑丢失。物理丢失是指操作系统的数据库主键（例如数 据文件、控制文件、重做日志文件以及归档日志）丢失。引起物理数据丢失的原因可能是磁 盘驱动器损毁，也可能是有人意外删除了一个数据文件或者修改关键数据库文件造成了配置 变化。逻辑丢失就是例如表、索引和表记录等数据库主键的丢失。引起逻辑数据丢失的原因 可能是有人意外删除了不该删除的表，应用程序出错或者在DELETE语句中使用了不适当的 WHERE子句等。 1、备份分类 1)、逻辑备份：Exp / Imp 老工具； Expdp / Impdp 数据泵工具 2)、物理备份：操作系统下的备份 物理备份和恢复的内容 1．初始化参数文件 2．控制文件 3．数据文件 4．联机重做日志文件 5．归档日志文件 逻辑备份工具Exp / Imp 导出或导入数据时，实现逻辑备份和逻辑恢复。通过使用EXP，可以将数据库对象备份到转储文件中；当表 被意外删除或其他误操作时，可以使用IMP将转储文件中的对象和数据导入数据库中。 数据库的备份 数据库管理员可以排定一个备份日程表，用数据导出的三个不同方式合理高效的完成。 比如数据库一周的备份任务可以做如下安排： 星期一：完全备份（A） 星期二：增量导出（B） 星期三：增量导出（C） 星期四：增量导出（D） 星期五：累计导出（E） 星期六：增量导出（F） 星期日：增量导出（G） 数据库的还原 Imp实际执行步骤： 1. Create Tablespace 2. Create User / Role 3. Create Table 4. Insert Data 5. Create Index 6. Create Triggers, Constraints 问题： 如果在星期日，数据库遭到意外破坏，数据库管理员可如何进行恢复数据库效率最高？ 第一步：用命令CREATE DATABASE重新生成数据库结构； 第二步：创建一个足够大的附加回滚空间。 第三步：完全导入A： imp system/manager inctype=RESTORE FULL=Y FILE=A 第四步：累计增量导入E： imp system/manager inctype=RESTORE FULL=Y FILE=E 第五步：最近增量导入F： imp system/manager inctype=RESTORE FULL=Y FILE=F 数据库备份和恢复的策略 ? 逻辑备份仅能恢复到上次的备份点 ? 物理备份中的不归档备份可恢复到上次备份点 ? 物理备份中的归档备份恢复到failure的前一刻 Oracle的数据库基础 用户 对象 用户：对数据库的访问，需要以适当用户身份通过验证，并具有相关权限来完成一系列动作 。 SYS用户：缺省始终创建，且未被锁定，拥有数据字典及其关联的所有对象 SYSTEM用户：缺省始终创建，且未被锁定，可以访问数据库内的所有对象 模式(schema)：是某个用户拥有所有对象的集合。具有创建对象权限并创建了对象的用户 称为拥有某个模式。 ? 创建用户 CREATE USER用户名 INDENTIFIED BY 口令 EXTERNALLY DEFAULT TABLESPACE 表空间名 TEMPORARY TABLESPACE 表空间名 QUOTA 整数 K/M ON 表空间名 UNLIMITED PROFILE 环境文件名 ? 修改用户属性 ALTER USER 用户名 INDENTIFIED BY 口令 EXTERNALLY DEFAULT TABLESPACE 表空间名 TEMPORARY TABLESPACE 表空间名 QUOTA 整数 K/M ON 表空间名 UNLIMITED PROFILE 环境文件名 ? 删除用户 ?DROP USER 用户名 ?DROP USER 用户名 CASCADE ?与用户管理有关的数据字典视图： ? USER_USERS ? ALL_USERS ? DBA_USERS ? USER_TS_QUOTAS 对象 1,TABLE(表) 2,VIEW（视图） 3,SEQUENCES（序列） 4,索引，函数，存储过程 5,SYNONYM（同义词） 6,DATABASE LINK（数据链路） 1、表： 对于初学者来说，对表的概念也有一定的认识。因为我们对数据库的操作，90%以上是对表的操作。 常见表主要有分区表、索引组织表 、堆表三种表类型。 ?分区表以单独的物理结构（分区）存储行，依据列的值分配行。 ?索引组织的表将索引和表的数据存储在一起。普通表的数据以无序（Heap）的方式存放在数据库 中。而索引组织表按照主键进行排序，以二叉树的形式对表的数据进行存储。 ?堆表是我们在Oracle中最常使用的数据表，也是Oracle的默认数据表存储结构 创建表基本语法: 2、视图： 视图既不分配存储空间，也不包含数据。而是通过定义一个查询，从它所引 用的基表中提取或派生出数据。视图基于其他对象，除了只需要在数据字典 中存储定义视图的查询，它不需要其他存储。 创建视图语法: 3、序列（sequence）： 序列是生成唯一整数值的结构。序列创建之后，可以通过序列队形的currval和nextval两个" 伪列"，分别访问该序列的当前值和下一个值，currval必须在nextval调用之后才能使用。 创建序列语法: 4、索引（index）： 索引相当于一本书的目录，能过提供检索的速度，如果某一列需要经常作为查询条件，则有必要 为其创建索引，能显著提供效率。Oracle数据库会为表的主键和包含唯一约束的列自动创建索 引。 创建索引语法: 5、函数（function）： 函数是作为数据库对象存储在oracle数据库中，函数又被称为PL/SQL子程序。oracle处理使用系统提供的函 数之外，用户还可以自己定义函数。函数通常被作为一个表达式来调用或存储过程的一个参数，具有返回 值。通常用于返回特定的数据。 6、存储过程（procedure）： 存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过 第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来调 用存储过程。 7、同义词（ SYNONYM ）： 同义词是方案对象的别名,它不占储存的空间,目的是在Oracle中为表或者视图、序列、PL/SQL程序单元、用户 自定义对象或其他的同义词创建友好的名称。 创建语句： create public synonym table_name for user.table_name; 8、数据链路（ dblink ）： 当需要跨越本地数据库，访问远程数据库的数据时，oracle提供了dblink的方式，让我们可以很方便访 问远程数据库像本地一样方便。