DOC.NO. TIMESON-SM-2002-11-0001A
Normal
(
公开
)
本地电信业务计费帐务系统
ORACLE 操作手册
Version 1.0.0
2002.11.05
TIMESON
长沙天辰科技有限公司
ã
2000,2001,2002
All Rights Reserved
ORACLE 操作手册
长沙天辰科技有限公司 2
前 言..............................................................................................................................3
1. 数据库的创建........................................................................................................4
1.1. 以下为 ORACLE 启动初始文件 INITORA.ORA ............................................4
1.2. 以下是建库脚本............................................................................................5
2. 数据库基本操作....................................................................................................9
2.1. 数据库的正常启动........................................................................................9
2.2. 安装启动与非安装启动..............................................................................10
2.3. 独占和共享启动..........................................................................................10
2.4. 约束启动......................................................................................................10
2.5. 强制启动......................................................................................................11
2.6. 数据库关闭..................................................................................................11
2.7. PL/SQL 基本程序的编写 ...........................................................................11
2.7.1. SQL
语言简介
......................................................................................12
2.7.2. PL/SQL
简介
........................................................................................16
3. 解决 RDBMS 问题 .............................................................................................22
3.1. 性能优化......................................................................................................22
3.1.1.
优化内存
..............................................................................................23
3.1.2.
优化输入
/
输出
.....................................................................................25
3.1.3.
优化排序
..............................................................................................27
3.1.4.
优化索引建立
......................................................................................27
3.2. 备份和恢复..................................................................................................27
3.2.1.
备份提示
..............................................................................................28
3.3. ORACLE 8 SERVER 诊断特性........................................................................29
3.3.1. Oracle
跟踪文件
..................................................................................30
3.3.2.
设置跟踪事件
......................................................................................31
3.3.3. V$
监视视图
.........................................................................................34
3.3.4.
锁实用程序
..........................................................................................36
3.4. ORACLE 错误分析和解决方案....................................................................37
3.4.1. 常见
Oracle
错误
.................................................................................37
3.4.2. ORACLE
内部错误
..............................................................................45
3.4.3.
优先权
1/
优先权
2
问题分类和诊断操作
..........................................46
3.5. 常见问题......................................................................................................48
ORACLE 操作手册
长沙天辰科技有限公司 3
前 言
为了加强计费系统数据库的操作安全及更有效的管理计费系统数据库,
编写该操作手册。
在该手册若有错误及遗漏的地方还望各位读者不吝指出与谅解。
ORACLE 操作手册
长沙天辰科技有限公司 4
1. 数据库的创建
以下仅为数据库创建的样例脚本，在实际系统中应根据数据库设计要与
系统实际配置而改变参数：
1.1.以下为 ORACLE 启动初始文件 initora.ora
#this sampale init file writen by wzy 2002/11/15
db_name = "jf"
instance_name = ora8
#service_names = ora
#db_files = 1024
control_files = ("/home/oracle/OraCtl/control01.ctl",
"/home/oracle/OraCtl/control02.ctl", "/home/oracle/OraCtl/control03.ctl")
open_cursors = 100
max_enabled_roles = 50
db_file_multiblock_read_count = 8
db_block_buffers = 4096
shared_pool_size = 52428800
large_pool_size = 78643200
java_pool_size = 20971520
log_checkpoint_interval = 10000
log_checkpoint_timeout = 1800
processes = 115
parallel_max_servers = 5
log_buffer = 32768
max_dump_file_size = 10240
global_names = true
#oracle_trace_collection_name = ""
db_block_size = 16384
#remote_login_passwordfile = exclusive
#os_authent_prefix = ""
job_queue_processes = 4
job_queue_interval = 60
open_links = 10
#large_pool_size = 614400
java_pool_size = 20971520
distributed_transactions = 10
mts_dispatchers =
"(PROTOCOL=TCP)(PRE=oracle.aurora.server.SGiopServer)"
ORACLE 操作手册
长沙天辰科技有限公司 5
mts_dispatchers = "(protocol=TCP)"
#compatible = 8.1.0
sort_area_size = 65536
sort_area_retained_size = 65536
# log_archive_start = true
1.2.以下是建库脚本
#!/bin/sh
ORACLE_SID=ora8
export ORACLE_SID
svrmgrl
connect internal
startup nomount pfile = $ORACLE_HOME/dbs/initora.ora
CREATE DATABASE "app1"
controlfile reuse
maxdatafiles 500
maxinstances 8
maxlogfiles 32
character set ZHS16GBK
national character set ZHS16GBK
DATAFILE '/oradata/app1/system01.dbf' SIZE 200M
logfile group 1 ('/opt/oracle/oradata/app1/app1_redo01.log',
'/oradata/app1/app1_redo01.log') SIZE 20M,
group 2 ('/opt/oracle/oradata/app1/app1_redo02.log',
'/oradata/app1/app1_redo02.log') SIZE 20M,
group 3 ('/opt/oracle/oradata/app1/app1_redo03.log',
'/oradata/app1/app1_redo03.log') SIZE 20M;
@/opt/oracle/product/8.1.7/rdbms/admin/catalog.sql;
CREATE ROLLBACK SEGMENT r0 TABLESPACE SYSTEM
STORAGE (INITIAL 128k NEXT 128k MINEXTENTS 2 MAXEXTENTS 20);
ALTER ROLLBACK SEGMENT r0 ONLINE;
REM ************ TABLESPACE FOR OEM_REPOSITORY ***************
CREATE TABLESPACE OEM_REPOSITORY DATAFILE '/oradata/app1/oemrep01.dbf'
SIZE 5M REUSE
AUTOEXTEND ON NEXT 25M MAXSIZE 80M
MINIMUM EXTENT 128k
DEFAULT STORAGE ( INITIAL 128k NEXT 128k MINEXTENTS 1 MAXEXTENTS
UNLIMITED PCTINCREASE 0);
REM ************** TABLESPACE FOR ROLLBACK *****************
CREATE TABLESPACE RBS DATAFILE '/oradata/app1/rbs01.dbf' SIZE 200M REUSE
