ORACLE 8i DBA完全简明手册

（作者按：2002年10月下旬，参加了OCP数据库的培训，根据当时的笔记和近几年的实际经验，特别是在大家的鼓励下，完成了此本手册，以供大家实际使用中参考--2003/1/1）注：下文中的所有例子均是基于ORACLE8i for WIN2K SERVER完成的，在不同版本和操作系统上可能会有所差别。 一、 Oracle数据库基本概念和体系结构1.什么是Oracle数据库、DBA?Oracle是美国Oracle公司的注册商标，它的起家产品Oracle数据库是一种RDBMS（关系数据库管理系统），我们平时所说的ORACLE即指的是Oracle数据库—包括所有的物理数据及相关的物理、内存、进程等对象的组合。DBA（DataBase Administrator）数据库管理员，主要负责数据库的设计、维护、监控、管理、备份、安全、开发人员协调等一系列工作，是数据信息管理系统的核心位置。2.实例和数据库内部结构Oracle数据库是由实例和数据库组成。实例指Oracle所使用的内存和后台进程及一些配置文件；● SGA:系统全局区。是一组内存结构，包括Buffer Cache数据高速缓存、Redo log Buffer重做日志缓冲区、Shared Pool共享池(dictionary cache数据字典高速缓存、Library Cache共享SQL池、User Session用户会话)等；● 后台进程：SMON（系统监视器进程）、DBWR（数据库写盘进程）、PMON（进程监视器进程）、CKPT（检查点进程）、LGWR（日志写盘进程）等；数据库指数据文件、重做日志文件及控制文件；二、 Oracle数据库管理1.启动和关闭数据库①启动数据库D:/orant8i/bin>svrmgrlOracle Server Manager Release 3.1.7.0.0 - ProductionCopyright (c) 2000, Oracle Corporation. All Rights Reserved.Oracle8i Enterprise Edition Release 8.1.7.0.0 - ProductionWith the Partitioning optionJServer Release 8.1.7.0.0 - ProductionSVRMGR> connect internal;连接成功。SVRMGR> startup已启动 ORACLE 实例。系统全局区域合计有 121522204个字节Fixed Size 75804个字节Variable Size 57708544个字节Database Buffers 63660032个字节Redo Buffers 77824个字节已装入数据库。已打开数据库。数据库启动的3个步骤：NOMOUNT：启动Oracle实例MOUNT：安装数据库OPEN：打开数据库SVRMGR> startup nomount已启动 ORACLE 实例。系统全局区域合计有 121522204个字节Fixed Size 75804个字节Variable Size 57708544个字节Database Buffers 63660032个字节Redo Buffers 77824个字节SVRMGR> alter database mount;语句已处理。SVRMGR> alter database open;语句已处理。（注意：如果服务器上装有多个数据库时,在执行svrmgrl前应首先执行set oracle_sid=wwfdb，这个例子中要操作的数据库系统id为wwfdb）②关闭数据库SVRMGR> shutdown已关闭数据库。已卸下数据库。已关闭 ORACLE 实例。关闭数据库的四种方式NORMAL：正常关闭数据库。不会强迫用户断开，不允许新的连接，但等待所有连接直到断开。IMMEDIATE：断开所有连接，回退活动事务，不允许建立新连接。ABORT：立即断开所有连接、终止所有事务，下次启动时需要进行实例恢复。TRANSACTIONAL：等待事务完成后，即断开连接。SVRMGR> shutdown immediate已关闭数据库。已卸下数据库。已关闭 ORACLE 实例。（注意：迫不得已不要使用ABORT参数，实际上它接近于数据库服务器突然掉电，下次开机可能要花费很长的时间进行实例恢复。）2.数据库手工创建和相关参数配置尽管Oracle提供了Enterprise Manager这样的图形管理工具，几乎可以完成DBA的大部分工作，但当数据库出现问题或远程管理时，手工命令是不可或缺的，另外，手工操作可以让你更深层次的了解Oracle的机理。手工创建数据库：① 创建参数文件；Oracle在安装时会提供一个参数例子文件，你可以以它为模板来进行修改；（我这里的位置在D:/orant8i/database/initwwfdb.ora）#一个实际的初始化文件# Copyright (c) 1991, 2000 by Oracle Corporation################################################################################ Example INIT.ORA file## This file is provided by Oracle Corporation to help you customize# your RDBMS installation for your site. Important system parameters# are discussed, and example settings given.# Some parameter settings are generic to any size installation.# For parameters that require different values in different size# installations, three scenarios have been provided: SMALL, MEDIUM# and LARGE. Any parameter that needs to be tuned according to# installation size will have three settings, each one commented# according to installation size.## Use the following table to approximate the SGA size needed for the# three scenarious provided in this file:## -------Installation/Database Size------# SMALL MEDIUM LARGE# Block 2K 4500K 6800K 17000K# Size 4K 5500K 8800K 21000K## To set up a database that multiple instances will be using, place# all instance-specific parameters in one file, and then have all# of these files point to a master file using the IFILE command.# This way, when you change a public# parameter, it will automatically change on all instances. This is# necessary, since all instances must run with the same value for many# parameters. For example, if you choose to use private rollback segments,# these must be specified in different files, but since all gc_*# parameters must be the same on all instances, they should be in one file.## INSTRUCTIONS: Edit this file and the other INIT files it calls for# your site, either by using the values provided here or by providing# your own. Then place an IFILE= line into each instance-specific# INIT file that points at this file.## NOTE: Parameter values suggested in this file are based on conservative# estimates for computer memory availability. You should adjust values upward# for modern machines.################################################################################ db_name = "wwfdb" instance_name = wwfdb service_names = wwfdb db_files = 1024 # INITIAL# db_files = 80 # SMALL# db_files = 400 # MEDIUM# db_files = 1500 # LARGE control_files = ("D:/wwfdb/control01.ctl", "D:/wwfdb/control02.ctl", "D:/wwfdb/control03.ctl") open_cursors = 300max_enabled_roles = 30db_file_multiblock_read_count = 8 # INITIAL# db_file_multiblock_read_count = 8 # SMALL# db_file_multiblock_read_count = 16 # MEDIUM# db_file_multiblock_read_count = 32 # LARGE db_block_buffers = 7771 # INITIAL# db_block_buffers = 100 # SMALL# db_block_buffers = 550 # MEDIUM# db_block_buffers = 3200 # LARGE shared_pool_size = 31457280 # INITIAL# shared_pool_size = 3500000 # SMALL# shared_pool_size = 5000000 # MEDIUM# shared_pool_size = 9000000 # LARGE large_pool_size = 614400java_pool_size = 20971520 log_checkpoint_interval = 10000log_checkpoint_timeout = 1800 processes = 150 # INITIAL# processes = 50 # SMALL# processes = 100 # MEDIUM# processes = 200 # LARGE parallel_max_servers = 5 # SMALL# parallel_max_servers = 4 x (number of CPUs) # MEDIUM# parallel_max_servers = 4 x (number of CPUs) # LARGE log_buffer = 32768 # INITIAL# log_buffer = 32768 # SMALL# log_buffer = 32768 # MEDIUM# log_buffer = 163840 # LARGE #audit_trail = true # if you want auditingtimed_statistics = true # if you want timed statisticsmax_dump_file_size = 10240 # limit trace file size to 5M each # Uncommenting the line below will cause automatic archiving if archiving has# been enabled using ALTER DATABASE ARCHIVELOG.log_archive_start = truelog_archive_dest_1 = "location=D:/wwfdb/archive"log_archive_format = %%ORACLE_SID%%T%TS%S.ARC # If using private rollback segments, place lines of the following# form in each of your instance-specific init.ora files:#rollback_segments = ( RBS0, RBS1, RBS2, RBS3, RBS4, RBS5, RBS6 ) # If using public rollback segments, define how many# rollback segments each instance will pick up, using the formula# # of rollback segments = transactions / transactions_per_rollback_segment# In this example each instance will grab 40/5 = 8# transactions = 40# transactions_per_rollback_segment = 5 # Global Naming -- enforce that a dblink has same name as the db it connects toglobal_names = true # Edit and uncomment the following line to provide the suffix that will be# appended to the db_name parameter (separated with a dot) and stored as the# global database name when a database is created. If your site uses# Internet Domain names for e-mail, then the part of your e-mail address after# the '@' is a good candidate for this parameter value.# db_domain = us.acme.com # global database name is db_name.db_domain # Uncomment the following line if you wish to enable the Oracle Trace product# to trace server activity. This enables scheduling of server collections# from the Oracle Enterprise Manager Console.# Also, if the oracle_trace_collection_name parameter is non-null,# every session will write to the named collection, as well as enabling you# to schedule future collections from the console.# oracle_trace_enable = true oracle_trace_collection_name = ""# define directories to store trace and alert filesbackground_dump_dest = D:/wwfdb/bdump#Uncomment this parameter to enable resource management for your database.#The SYSTEM_PLAN is provided by default with the database.#Change the plan name if you have created your own resource plan.# resource_manager_plan = system_planuser_dump_dest = D:/wwfdb/udump db_block_size = 8192 remote_login_passwordfile = EXCLUSIVE os_authent_prefix = "" # The following parameters are needed for the Advanced Replication Optionjob_queue_processes = 4job_queue_interval = 10open_links = 4 distributed_transactions = 500mts_dispatchers = "(PROTOCOL=TCP)(PRE=oracle.aurora.server.SGiopServer)"# Uncomment the following line when your listener is configured for SSL# (listener.ora and sqlnet.ora)# mts_dispatchers = "(PROTOCOL=TCPS)(PRE=oracle.aurora.server.SGiopServer)" compatible = 8.1.0sort_area_size = 65536sort_area_retained_size = 65536 ② 创建与实例相关的目录md wwfdbcd wwfdbmd bdumpmd udumpmd archive ① 创建实例ORADIM –NEW –SID wwfdb-INTPWD shbj2003-STARTMODE auto-PFILE D:/orant8i/database/initwwfdb.ora② 启动实例SET ORACLE_SID=wwfdbSVRMGRLSVRMGRL>connect internal/shbj2003SVRMGRL>startup nomount;③ 创建数据库CREATE DATABASE wwfdbLOGFILE GROUP 1 ‘D:/WWFDB/WWFDB1A.LOG’ SIZE 1M, GROUP 2 ‘D:/WWFDB/WWFDB2A.LOG’ SIZE 1MMAXLOGFILES 10DATAFILE ‘D:/WWFDB/sys1wwfdb.dbf’ SIZE 100M AUTOEXTEND ON NEXT 20M MAXSIZE 200MMAXDATAFILES 150CHARACTER SET ZHS16GBK;④ 修改监听文件重启监听服务打开D:/orant8i/network/ADMIN/listener.ora文件，在SID_LIST_LISTENER 项目SID_LIST里，添加： (SID_DESC = (GLOBAL_DBNAME = wwfdb) (ORACLE_HOME = D:/orant8i) (SID_NAME = wwfdb))执行lsnrctl reload重启监听服务⑤ 安装数据字典通过catalog.sql文件创建数据字典视图（包括sql.bsq的数据字典基本表），通过catproc.sql创建PL/SQL环境，通过pupbld.sql创建用户资源集。⑥ 配置网络客户打开D:/orant8i/network/ADMIN/tnsnames.ora文件，添加WWFDB = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = WWFLAP)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = wwfdb)) )3.数据库设计① 逻辑设计这里给出Oracle公司所提供的优化的数据库逻辑分布表，各类系统可以参照设计：表空间 用途 SYSTEM 数据字典 DATA 标准操作表 DATA_2 标准操作时使用静态表 INDEXES 标准操作表的索引 INDEXES_2 静态表的索引 RBS 标准操作的回滚段 RBS_2 用于数据装载的特定回滚段 TEMP 标准操作的临时段 TEMP_USER 由特定用户创建的临时段 TOOLS RDBMS工具表 TOOLS_1 RDBMS工具表的索引 USERS 开发数据库中的用户对象 USERS_1 测试数据库中的用户索引 SANPS 快照表 SANPS_1 快照表上的索引 AGG_DATA 聚合表和显形图 AGG_DATA_1 聚合表和显形图上的索引 PARTITIONS 表或索引段的分区 PARTITIONS_1 分区上的局部和全局索引 TEMP_WORK 数据装载时使用的临时表 ② 物理设计 4.安全和监控审计5.备份和恢复6.大批量的数据操作7.回滚段管理8.数据库调优9.系统排错一、 SQL命令和PL/SQL语言介绍1.DDL2.DML3.存储过程、函数、触发器 二、 Oracle网络1.NET8配置2.3.三、 Oracle相关工具1.SQL PLUS2.SQL LOADER3.Enterprise Manager附录：1、 Oracle常用视图介绍2、 Oracle 常用SQL命令3、 Oracle DBA相关 Unix命令 (未完待续)