oracle DBA培训教程.doc

Oracle DBA培训教程

Oracle DBA
培训教程

Amy.Tong
2013.01.04

目 录
1 准备（ORACLE 11G，WINDOWS平台） 7
1.1 Oracle环境变量配置 7
1.2 Sqlplus远程访问数据库命令 7
1.3 oracle密码管理 7
1.4 浅谈sqlnet.ora文件的作用 7
1.4.1 SQLNET.AUTHENTICATION_SERVICES 8
1.5 oracle: 安装sqlplus help帮助命令 10
2 第一章 ORACLE的系统结构 12
2.1 Oracle数据库的体系结构 12
2.2 共享池 12
2.3 数据库高速缓存区 12
2.4 内存缓冲区顾问 12
2.5 内存缓冲区信息的获取 13
2.6 利用Oracle日志挖掘浏览器进行数据恢复 13
3 第二章 数据库管理工具 14
4 第三章 ORACLE实例的管理 15
4.1 参数文件 15
4.1.1 思想 15
4.1.2 详情 15
4.2 启动数据库 15
4.3 将数据库置为限制模式 16
4.4 关闭数据库 16
4.5 如何利用诊断文件来监督实例 16
5 第四章 数据字典和控制文件 18
5.1 数据字典简介 18
5.2 获取数据库的基本信息 18
5.3 控制文件 19
5.3.1 如何限定控制文件的大小 19
5.3.2 备份控制文件 19
5.3.3 移动控制文件的实例 19
6 第五章 重做日志文件 22
6.1 重做日志组、重做日志组成员介绍 22
6.2 重做日志后台处理逻辑 22
6.3 重做日志属性设置 22
6.4 添加和删除联机重做日志文件组 23
6.5 添加和删除联机重做日志成员 23
6.6 清除联机重做日志文件 23
6.7 利用OMF（oracle manager files）来管理联机重做日志文件 25
6.8 重做日志配置的应用实例 25
7 第六章 表空间和数据文件的管理 27
7.1 Oracle 为什么要引入逻辑结构？ 27
7.2 创建数据字典管理的表空间 27
7.3 创建本地管理的表空间（该表空间基于一个数据文件） 28
7.4 创建还原表空间 28
7.5 创建临时表空间 28
7.6 默认临时表空间 28
7.7 设置表空间为脱机 28
7.8 只读表空间 29
7.9 改变表空间的存储设置 29
7.10 重置表空间大小 30
7.11 手工重置表空间数据文件的大小 30
7.12 移动数据文件的方法 30
7.13 迁移数据字典和本的管理的表空间 32
7.14 删除表空间 32
7.15 利用OMF来管理表空间 33
7.16 创建表空间的应用实例 34
8 第7章 存储结构和他们之间的关系 35
9 第8章 管理还原数据 36
9.1 Oracle还原段的类型 36
9.2 查看数据中使用的还原数据管理类型是自动还是手动 36
9.3 还原表空间的创建与维护 36
9.4 还原表空间之间的切换 36
9.5 删除当前活动的还原表空间 37
10 第9章 创建数据库 38
11 第10章 管理表 39
11.1 创建普通表 39
11.2 创建临时表 39
11.3 非分区表的重组 40
11.4 列的维护 40
11.5 表的截断和删除 41
11.6 创建表的应用实例 41
12 第11章 索引的管理与维护 42
12.1 索引的分类 42
12.2 B-树索引（oracle中所有的索引都是使用的B-树结构）和位图索引的比较 42
12.3 创建索引 42
12.4 重建和维护索引 43
12.5 标识索引的使用情况 44
12.6 删除索引 44
13 第12章 管理和维护数据的完整性 46
14 第13章 用户及系统资源和安全的管理 47
14.1 创建用户 48
14.2 数据库模式 49
14.3 改变用户在表空间上的配额 49
14.4 删除用户 49
14.5 概要文件 49
14.6 创建概要文件的应用实例 50
15 第14章 管理权限 51
16 第15章 角色管理 52
17 第16章 非归档模式下的冷备份和恢复 53
17.1 实验环境的搭建 53
17.2 数据库的非归档模式和备份的术语 53
17.3 冷备份（脱机备份）的应用实例 54
17.4 脱机恢复到原来位置的应用实例 54
18 第17章 数据库的归档模式 55
18.1 将数据库置为归档模式 55
18.2 归档进程和归档文件目录的设置 55
18.3 归档文件和归档进程的管理与维护 56
18.4 改变成归档模式的应用实例 56
19 第18章 数据库的联机（热）备份 57
19.1 联机备份步骤的演示 57
19.2 备份操作的自动化实例 58
20 第19章 归档模式下的数据恢复 63
20.1 数据库的完全恢复 63
20.1.1 在进行数据库的完全恢复是要进行如下的操作： 63
20.2 Recover命令及恢复有关的数据字典 63
20.2.1 Oraclet提供的3个sql/plus的恢复命令 63
20.2.2 实例演示 63
20.3 Recover期间归档日志文件的使用 64
20.4 获取sql/plus命令的信息和使用方法 64
20.4.1 如何知道命令所属“sql命令”or“ sql/plus命令” 64
20.5 数据库完全恢复的方法 65
20.5.1 4种数据库完全恢复方法 65
20.6 最初处于打开状态下进行的开启数据库恢复 65
20.6.1 模拟情形 65
20.6.2 操作步骤 65
20.7 最初处于关闭状态下进行的开启数据库恢复 66
20.7.1 模拟情形 66
20.7.2 操作步骤 66
20.7.3 操作实例 66
20.8 恢复没有备份的数据文件 67
20.8.1 模拟情形 67
20.8.2 操作步骤 67
20.9 在关闭的状态下进行数据库的恢复 67
20.9.1 模拟情形 67
20.9.2 操作步骤 68
20.9.3 操作实例 68
21 第20章 数据的移动 69
21.1 导入、导出的用户权限 69
21.2 导出应用程序的用法 69
21.3 情况一：导出、导入程序的应用实例 69
21.3.1 使用限制 69
21.3.2 模拟环境 69
21.4 数据泵（beng,四声） 69
21.5 情况二：不同用户及不同表空间之间的数据的移动 70
21.5.1 模拟环境 70
21.6 情况三：将oracle的数据传给其他软件（系统） 71
21.7 情况四：将其他系统软件（系统）的数据导入oracle 71
21.8 数据泵操作的自动化 72
22 第21章 闪回技术、备份恢复与优化 73
22.1 闪回被删除的表 73
22.2 闪回错误的DML操作 73
22.3 非当前的还原表空间的恢复 73
22.3.1 恢复非当前的还原表空间 74
22.4 只读表空间和临时表空间的恢复 74
22.4.1 数据库关闭状态下恢复临时表空间的方法 74
22.4.2 数据库开启状态下恢复临时表空间 75
22.5 索引表空间的恢复 75
22.6 加快数据表空间的恢复 76
23 第22章 设计、程序及内存的优化 77
23.1 联机事务系统与数据仓储系统比较 77
23.2 以命令行形式获取系统的设计 77
23.3 优化应用程序和sql语句的基本原理 78
23.4 以命令方式获取sql语句执行的信息 78
23.5 导出程序的源代码 78

1 准备（oracle 11g，windows平台）
关于scott账户出问题，可以通过运行：
D:\app\Administrator\product\11.2.0\dbhome_1\RDBMS\ADMIN目录中的scott.sql来恢复数据。
运行命令：@D:\app\Administrator\product\11.2.0\dbhome_1\RDBMS\ADMIN\scott.sql
1.1 Oracle环境变量配置
Path:添加到oracle安装目录的bin目录***/bin
新建变量1：Oracle_home添加到oracle的安装目录（bin前面一个目录）
新建变量2：oracle_sid，值为数据库实例名orcl
1.2 Sqlplus远程访问数据库命令
本地已配置数据库远程连接的情况：Sqlplus username/userpassword@SID
本地未配置数据库远程连接的情况：Sqlplus username/userpassword@SID_IP
1.3 oracle密码管理
Sqlnet.ora文件所在目录：D:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN
sqlnet.ora文件中参数SQLNET.AUTHENTICATION_SERVICES的设置
参数sqlnet.authentication_services值选项：
none：作用是不允许通过os系统用户登录数据库，需要提供用户名及密码；
all：作用是允许所有的登录方式；
nts：作用是windows的本地操作系统用户认证；
sqlnet.ora文件的作用：远程连接数据库时，必须要这个文件，本地数据库访问不需要这个文件，该文件决定找数据库服务器别名的方式
1.4 浅谈sqlnet.ora文件的作用
关于sqlnet.ora的说明：
*****************************************************FROM ORACLE11G DOCS*************************************
The sqlnet.ora file is the profile configuration file. It resides on the client machines and the database server. Profiles are stored and implemented using this file. The database server can be configured with access control parameters in the sqlnet.ora file. These parameters specify whether clients are allowed or denied access based on the protocol.
The sqlnet.ora file enables you to do the following:
· Specify the client domain to append to unqualified names
· Prioritize naming methods
· Enable logging and tracing features
· Route connections through specific processes
· Configure parameters for external naming
· Configure Oracle Advanced Security
· Use protocol-specific parameters to restrict access to the database
By default, the sqlnet.ora file is located in the ORACLE_HOME/network/admin directory. The sqlnet.ora file can also be stored in the directory specified by the TNS_ADMIN environment variable.
*****************************************************************************************************************************
从描述中看出，该文件是控制客户端访问数据库服务器的，可以设定多种参数进行访问控制。而在这里作为初学者接触到的最早参数是 sqlnet.authentication_services，说明如下：
************************************************FROM ORACLE11G DOCS******************************************************
1.4.1 SQLNET.AUTHENTICATION_SERVICES
Purpose
To enable one or more authentication services. If authentication has been installed, then it is recommended that this parameter be set to either none or to one of the authentication methods.
Default
None
Note:
When installing the database with Database Configuration Assistant (DBCA), this parameter may be set to nts in the sqlnet.ora file.
Values
· Authentication Methods Available with Oracle Net Services:
o none for no authentication methods, including Microsoft Windows native operating system authentication. When SQLNET.AUTHENTICATION_SERVICES is set to none, a valid user name and password can be used to access the database.
o all for all authentication methods.
o nts for Microsoft Windows native operating system authentication.
· Authentication Methods Available with Oracle Advanced Security:
o kerberos5 for Kerberos authentication.
o radius for RADIUS authentication.
o tcps for SSL authentication.
**********************************************************************************************************************************
从参数的说明可以了解到提供两种服务，net服务和高级安全服务。高级安全服务参数这里不讨论了，讨论一下 none all nts三个参数的作用。
设定sqlnet.authentication_services：
· none：作用是不允许通过os系统用户登录数据库，需要提供用户名及密码；
· all：作用是允许所有的登录方式；
· nts：作用是windows的本地操作系统用户认证；
注：需要说明的是据试验该用户名和密码是指具有sysdba权限的用户；在linux上若用系统用户oracle登录数据库需要设定为all或是注销该字段；
注：linux上默认是没有该文件的，可以手动创建，参考$ORACLE_HOME/network/admin/samples/sqlnet.ora内容，并将之设定在$ORACLE_HOME/network/admin/目录下。
[plain] view plaincopyprint?
1. [oracle@centos admin]$ pwd  
2. /oracle/11g/network/admin  
3. [oracle@centos admin]$ cat sqlnet.ora   
4. # This file is actually generated by netca. But if customers choose to   
5. # install "Software Only", this file wont exist and without the native   
6. # authentication, they will not be able to connect to the database on NT.  
7.   
8. #SQLNET.AUTHENTICATION_SERVICES = (none)  
9. [oracle@centos admin]$   
[oracle@centos admin]$ pwd
/oracle/11g/network/admin
[oracle@centos admin]$ cat sqlnet.ora
# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.

#SQLNET.AUTHENTICATION_SERVICES = (none)
[oracle@centos admin]$
我是从windows上copy过去的，对于linux来讲一般不指定该字段就能使oracle系统用户登录数据库了。
1.5 oracle: 安装sqlplus help帮助命令
采用system用户登录执行脚本方式
1、用system用户登录，必须的，help表属于system的schema, 而且不要以sysdba身份登录
2、执行脚本内容如下
[plain] view plaincopyprint?
1. [oracle@centos oracle]$ sqlplus system/lzx123  
2. SQL*Plus: Release 11.2.0.1.0 Production on Tue Sep 20 12:04:44 2011  
3. Copyright (c) 1982, 2009, Oracle.  All rights reserved.  
4.   
5. Connected to:  
6. Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production  
7. With the Partitioning, OLAP, Data Mining and Real Application Testing options  
8.   
9. SQL> @?/sqlplus/admin/help/helpbld.sql  
10. Enter value for 1: /oracle/11g/sqlplus/admin/help  
11. Enter value for 2: /oracle/11g/sqlplus/admin/help/helpus.sql  
[oracle@centos oracle]$ sqlplus system/lzx123
SQL*Plus: Release 11.2.0.1.0 Production on Tue Sep 20 12:04:44 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @?/sqlplus/admin/help/helpbld.sql
Enter value for 1: /oracle/11g/sqlplus/admin/help
Enter value for 2: /oracle/11g/sqlplus/admin/help/helpus.sql

value1是helpus.sql的脚本路径，value2是脚本

3、以上即可完成创建，若遇到system表空间问题：
若出现如下问题
[sql] view plaincopyprint?
1. SQL> @/oracle/11g/sqlplus/admin/help/helpbld.sql  
2. Enter value for 1: /oracle/11g/sqlplus/admin/help  
3. Enter value for 2: /oracle/11g/sqlplus/admin/help/helpus.sql  
4.   
5. PL/SQL procedure successfully completed.  
6.   
7. CREATE TABLE HELP  
8. *  
9. ERROR at line 1:  
10. ORA-00604: error occurred at recursive SQL level 1   
11. ORA-01654: unable to extend index SYS.I_COL2 by 128 in tablespace SYSTEM   
SQL> @/oracle/11g/sqlplus/admin/help/helpbld.sql
Enter value for 1: /oracle/11g/sqlplus/admin/help
Enter value for 2: /oracle/11g/sqlplus/admin/help/helpus.sql

PL/SQL procedure successfully completed.

CREATE TABLE HELP
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01654: unable to extend index SYS.I_COL2 by 128 in tablespace SYSTEM
说明system表空间不是自动扩展的，需要修改一下，过程如下：
[sql] view plaincopyprint?
1. SQL> select tablespace_name,file_name,autoextensible from dba_data_files;  
2.    
3. TABLESPACE_NAME                FILE_NAME                                AUTOEXTENSIBLE  
4. ------------------------------ ---------------------------------------  --------------   
5. SYSTEM                         /oracle/mercury/system01.dbf             NO  
6. SYSAUX                         /oracle/mercury/sysaux01.dbf             NO  
7. UNDOTBS1                       /oracle/mercury/undotbs01.dbf            YES  
8. USERS                          /oracle/mercury/users01.dbf              YES  
9.    
10. SQL> alter database datafile 1 autoextend on;  
11.   
12.    
13. Database altered  
14.    
15. SQL>   
SQL> select tablespace_name,file_name,autoextensible from dba_data_files;

TABLESPACE_NAME FILE_NAME AUTOEXTENSIBLE
------------------------------ --------------------------------------- --------------
SYSTEM /oracle/mercury/system01.dbf NO
SYSAUX /oracle/mercury/sysaux01.dbf NO
UNDOTBS1 /oracle/mercury/undotbs01.dbf YES
USERS /oracle/mercury/users01.dbf YES

SQL> alter database datafile 1 autoextend on;

Database altered

SQL>
然后再重复2就可以了
2 第一章 oracle的系统结构
静态参数的更改需要重启数据库才生效，动态参数更改后不需重启数据库，立即生效。
2.1 Oracle数据库的体系结构
全局缓存：SGA
共享池（库高速缓冲区、数据字典高速缓存区）、数据库高速缓存区、重做日志缓存
后台进程：SMON、PMON、DBWR、LGWR、CKPT（ARC0）
二进制文件：参数文件、密码文件
2.2 共享池
共享池（库高速缓冲区【共享sql区、共享pl/sql区】、数据字典高速缓存区）
通过共享池的大小间接设置数据字典高速缓存区：
9i之前的版本：
alter system set shared_pool_size = 32M ;（需重启数据库）
该参数受sga_max_size参数限制（即：系统全局区高速缓存区最大值）
2.3 数据库高速缓存区
参数1：DB_block_size（数据库大小）——建库时才能设定
参数2：DB_block_buffers（缓冲区个数）——更改该参数需重启数据库才能生效
9i以前版本的数据库高速缓冲区大小 = 参数1 * 参数2
参数3：DB_block_size（动态参数）——9i及9i后的版本才具有动态改变功能
改变数据库高速缓存区的大小：
Alter system set db_cache_size = 48M ;
2.4 内存缓冲区顾问
动态参数：db_cache_advice，该参数状态有（on、ready、off）
更改参数状态：alter system set db_cache_advice = off;
查看信息：
select d.ID,d.BLOCK_SIZE,d.ADVICE_STATUS
from v$db_cache_advice d;
2.5 内存缓冲区信息的获取
获取SGA信息命令：Show sga
查询sag_max_size最大值：
select p.name,p.TYPE,p.VALUE
from v$parameter p
where name = 'sga_max_size';
2.6 利用Oracle日志挖掘浏览器进行数据恢复
略。
3 第二章 数据库管理工具
略。
4 第三章 oracle实例的管理
4.1 参数文件
参数文件的重要性：最重要之一
类型：静态参数文件、动态服务器参数文件
4.1.1 思想
获取当前数据库使用哪一类型的参数文件
Show parameter pfile;
11g：默认使用的是动态服务器文件
通过create pfile from spfile命令创建静态参数文件，通过查看该静态文件就可得知当前系统参数信息。
4.1.2 详情
静态文件：initSID.ora
存放目录：
Windows: $oracle_home/database/initSID.ora
Unix: $oracle_home/dbs/initSID.ora
动态文件：spfileSID.ora
存放目录：
Windows: $oracle_home/database/spfileSID.ora
Unix: $oracle_home/dbs/spfileSID.ora
动态服务器参数文件说利用pfile通过使用create spfile命了建立
格式：Create spfile [= ‘spfile Name’] from pfile [= ‘pfile Name’]
修改spfile文件中的参数：
Alter system set 参数名=参数值
4.2 启动数据库
命令：Startup
初始化参数选择顺序：spfileSID，默认的spfile,initSID,默认的pfile
自动选择执行那个初始化参数文件来启动数据库：
Startup pfile= c:\oracle\ora111g\database\initsun.ora
Startup 【Nomount:只有在创建数据库时或重建控制文件期间使用；
Mount：对数据库进行某些特殊的维护期间使用
Open：默认，打开控制文件、所有的联机数据文件、联机重做日志文件】
改变数据库的打开方式：
顺序：nomount ——> mount——>open
Alter database open 【read only】或者【read write】
4.3 将数据库置为限制模式
Startup restrict
将正在运行的数据库状态置为限制模式：
Alter system enable restricted session;
说明：此语句只能限制后续用户登录系统时需要具有restricted session权限，对已登录的用户仍可以继续工作，通过：
Alter system kill session命令杀死这些用户进程。
查询当前用户SID：
Select sid,serial#,username,type from v$session;
杀死该用户进程：
Alter system kill session ‘7,19’;【sid,serial#】
4.4 关闭数据库
Shutdown 【abort：允许新的联机;
Immediate:等待到当前所有的会话结束；
Transactional：等待到当前所有的事务结束；
Normal:默认，强制型检查点和关闭文件】
说明：只有shutdown abort在系统重启时需要进行数据库恢复。
4.5 如何利用诊断文件来监督实例
Oracle数据库常用的3种类型的诊断文件：
报警文件：
存放的名字格式：
Unix:alterSID.Log
NT:SIDalter.log
后台进程追踪文件：
用户进程追踪文件：
如何找到报警文件和后台进程追踪文件及用户进程追踪文件：
Col name for a30;
Col value for a45;
Set line 100;
Select name,value,from v$parameter Where name like ’%_dest’;
在oracle服务器上产生用户追踪文件命令：
在会话一级开启用户（进程）追踪：
Alter session set sql_tarce = true 【false：关闭】
在实例一级开启用户（进程）追踪：
Sql_trace = true 【false：关闭】
说明：建议不在实例一级开启用户（进