Chapter 0 driectory and structure
查看系统中有哪些权限：select * from system_privilege_map order by name;
第一部 分背景知识、数据建模、UNIX/Linux和SQL＊Plus
第1章 OracleDBA的世界
1.1 OracleDBA的任务
1.1.1 DBA的安全任务
1.1.2 DBA的系统管理任务
1.1.3 DBA的数据库设计任务
1.2 不同的DBA工作分类
1.3 数据库的类型
1.3.1 联机事务处理和决策支持系统数据库
1.3.2 开发、测试和产品数据库
1.4 培训和认证
1.4.1 培训
1.4.2 认证
1.4.3 OracleDBA的资源和组织
1.4.4 Oracle示例
1.4.5 Oracle数据库的两天DBA课程
1.4.6 OracleMetaLink
1.4.7 OracleWebCOnfercllC
1.5 普通0TacleDBA的日常工作
1.6 几点建议
1.6.1 知道何时寻求帮助
1.6.2 记住你并不孤单
1.6.3 站在全局看问题
1.6.4 首先是不要造成损害
第2章 关系数据库建模及数据库设计
2.1 关系数据库简介
2.2 关系数据库模型
2.2.1 数据库模式
2.2.2 关系代数
2.2.3 关系演算
2.2.4 SQL
2.3 关系数据库的生命周期
2.3.1 需求收集和分析
2.3.2 逻辑数据库设计
2.3.3 物理数据库设计
2.3.4 实现物理设计
2.4 反向设计数据库
2.5 对象-关系和对象数据库
2.5.1 关系模型
2.5.2 对象模型
2.5.3 对象-关系模型
2.5.4 半结构数据模型
第3章 OracleDBA的UNIX/Linux基础
3.1 UNIX和Linux操作系统概述
3.1.1 UNIX
3.1.2 Linux
3.1.3 中档系统
3.2 理解UNIXshell
3.3 访问UNIX系统
3.4 基本UNIX命令概览
3.4.1 帮助信息：man命令
3.4.2 改变提示符
3.4.3 文件和目录的查找
3.4.4 命令输出的控制
3.4.5 显示文件的内容
3.4.6 比较文件
3.4.7 理解操作系统和Shell变量
3.4.8 UNIX中的输入和输出重定向
3.4.9 防止文件被覆盖
3.5 UNIX中文件和目录的浏览
3.5.1 UNIX系统中的文件
3.5.2 链接文件
3.5.3 管理文件
3.5.4 权限：读／写UNIX的文件
3.5.5 目录管理
3.6 用vi编辑器编写文件
3.6.1 利用vi创建和修改文件
3.6.2 用head和tail命令移动
3.7 文本的提取和排序
3.7.1 使用grep匹配模式
3.7.2 剪切、粘贴和联结文本
3.8 shell脚本
3.8.1 shell程序介绍
3.8.2 使用shell变量
3.8.3 用test命令求表达式的值
3.8.4 执行带命令行参数的shell程序
3.8.5 分析shell脚本
3.8.6 Komshell编程中的流控制结构
3.9 管理UNIX进程
3.9.1 用ps命令收集进程信息
3.9.2 在注销后运行进程
3.9.3 在后台执行进程
3.9.4 用k1]1命令终止进程
3.10 UNIX系统管理和OracleDBA
3.10.1 UNIX的备份和恢复实用程序
3.10.2 crontab和Automating脚本
3.10.3 使用telnet
3.10.4 远程登录和远程复制
3.10.5 使用SSH
3.10.6 使用FTP发送和接收文件
3.10.7 UNIX系统性能监控工具
3.11 UNIX中的磁盘和存储
3.11.1 磁盘存储配置选择
3.11.2 监控磁盘使用
3.11.3 磁盘存储、性能和可用性
3.12 RAID系统
3.12.1 RAID级别
3.12.2 选择理想的磁盘配置
3.12.3 冗余磁盘控制器
3.12.4 RAID和备份
3.12.5 RAID和Oracle
3.13 其他存储技术
3.13.1 SAN
3.13.2 NAS系统
3.13.3 InfiniBand
3.13.4 自动存储管理
3.13.5 Oracle与存储系统的兼容性
第4章 使用SQL*Plus和Oracle企业管理器
4.1 启动SQL*Plus会话
4.1.1 设置环境
4.1.2 从命令行启动SQL*Plus会话
4.1.3 用CONNECT命令进行连接
4.1.4 用／NOLOG的无连接SQL*Plus会话
4.1.5 通过Windows GUI连接SQL*Plus
4.1.6 在SQL*Plus中进行操作
4.2 退出SQL*Plus
4.3 SQL。Plus和SQL命令
4.3.1 SQL*Plus的安全性
4.3.2 用SET命令设置SQL*Plus环境
4.3.3 设置常用的SQL*Plus变量
4.3.4 SQL*Plus错误记录
4.3.5 SQL*Plus命令行选项
4.4 SQL*Plus管理命令
4.4.1 CLEAR命令
4.4.2 STORE命令
4.4.3 SHOW命令
4.5 关键的SQL*Plus“工作”命令
4.5.1 SQLPROMPT命令
4.5.2 DESCRIBE命令
4.5.3 HOST命令
4.5.4 SPOOL命令
4.5.5 ACCEPT和PROMPT命令
4.5.6 EXECUTE命令
4.5.7 PAUSE命令
4.6 格式化SQL*Plus的输出以及创建报告的命令
4.6.1 BREAK命令
4.6.2 COLUMN命令
4.6.3 COMPUTE命令
4.6.4 REPFOOTER命令
4.6.5 REPHEADER命令
4.6.6 BTITLE和TTITLE命令
4.7 在SQL*Plus中创建命令文件
4.7.1 将SQL缓冲区内容保存到文件中
4.7.2 在SQL*Plus中执行SQL脚本
4.7.3 创建Windows的批处理脚本
4.7.4 DEFINE和UNDEFINE命令
4.7.5 预定义SQL*Plus变量
4.7.6 在SQL*Plus中使用注释
4.7.7 显示SQL命令清单
4.8 在SQL*Plus中进行编辑
4.8.1 插入和删除行
4.8.2 添加文本
4.8.3 用REMARK命令加入注释
4.8.4 用COPY命令复制表
4.8.5 用SQL*Plus使DML的更改永久化
4.8.6 用SQL*Plus创建网页
4.9 主要的SQL*Plus数据库管理命令
4.9.1 RECOVER命令
4.9.2 STARTUP和SHOUTDOWN命令
4.9.3 ARCHIVE LOG命令
4.10 用SQL生成SQL
4.11 Oracle SQL Developer
4.12 OEM
4.12.1 使用OEM管理数据库的好处
4.12.2 OEM体系结构和组件
4.12.3 配置和使用Database Control
4.12.4 访问Database Control
4.12.5 Database Control的简要介绍
4.12.6 Performance
4.12.7 Availability
4.12.8 Server
4.12.9 Schema
4.12.10 Data Movement
4.12.11 Soffwre and Support
4.12.12 Oracle软件克隆1
4.12.13 配置使用Setup页1
4.12.14 Related Links部分和Advisor Central页
4.12.15 创建Database Central角色
4.12.16 链接到Meta Link
4.12.17 基于策略的配置框架
4.12.18 跟踪数据库特性使用的统计数据
4.12.19 OEM Grid Control
4.12.20 管理Management Agent1
4.12.21 OMS(Oracle Management Service，Oracle服务管理)1
4.12.22 连接到Grid Control
4.12.23 登录到Grid Control
4.12.24 Grid Control的特性
4.12.25 使用Grid Congol主页
4.12.26 用Grid Contr01监控整个系统
第二部 Oracle Database llg的体系结构、模式和事务管理
第5章 Oracle Database 11g体系结构
5.1 Oracle数据库的结构
5.1.1 逻辑数据库结构
5.1.2 物理数据库结构
5.1.3 其他文件
5.2 Oracle进程
5.2.1 用户和Oracle进程之间的交互
5.2.2 服务器进程
5.2.3 后台进程
5.3 Oracle内存结构
5.3.1 理解主存储器
5.3.2 SGA
5.3.3 PGA
5.4 简单的Oracle数据库事务
5.5 提交和回滚
5.5.1 提交一个事务
5.5.2 回滚一个事务
5.6 数据一致性和数据并发性
5.6.1 数据库写入器和写前协议
5.6.2 SCN
5.6.3 撤销管理
5.7 备份与恢复体系结构
5.7.1 用户管理的备份与恢复
5.7.2 RMAN
5.7.3 oracle Secure Backup
5.7.4 闪回恢复技术
5.8 Oracle数据字典和动态性能视图
5.8.1 Oracle数据字典
5.8.2 动态性能(V$)视图
5.8.3 三组数据字典视图
5.8.4 如何创建数据字典
5.9 使用静态数据字典视图
5.10 与数据库对话
5.10.1 连接Oracle
5.10.2 OEM
5.10.3 SQL*Plus
5.11 Oracle实用程序
5.11.1 数据泵的导出和导入
5.11.2 SQL*Loader
5.11.3 Log Miner
5.11.4 自动诊断信息库控件接口
5.12 调度和资源管理工具
5.12.1 Oracle调度程序
5.12.2 数据库资源管理器
5.13 自动数据库管理
5.13.1 ADDM
5.13.2 自动撤销保留调优
5.13.3 自动优化程序统计数据收集
5.13.4 ASM
5.13.5 自动SQL调优
5.14 公共可管理基础设施
5.14.1 AWR
5.14.2 ASH
5.14.3 服务器产生的预警
5.14.4 自动任务特性
5.14.5 顾问程序的框架
5.14.6 更改管理
5.15 高效的管理和监控
第6章 管理表空间
6.1 表空间区尺寸和空间管理
6.1.1 分配区尺寸：自动分配与统尺寸
6.1.2 自动与手动段空间管理
6.2 创建表空间
6.2.1 数据文件和表空间
6.2.2 区分配和解除分配
6.2.3 存储参数
6.2.4 数据库对象的存储分配
6.2.5 增加表空间的大小
6.2.6 删除表空间
6.2.7 用户表空间的数目
6.2.8 表空间限额
6.2.9 主动的表空间的空间预警
6.2.10 管理重做数据的生成
6.2.11 重新命名表空间
6.2.12 只读表空间
6.2.13 使表空间脱机
6.3 临时表空间
6.3.1 创建临时表空间
6.3.2 更改临时表空间
6.3.3 收缩临时表空间
6.3.4 默认临时表空间
6.3.5 临时表空间组
6.4. 默认的永久表空间
6.5 大文件表空间
6.5.1 使用大文件表空间的限制
6.5.2 创建大文件表空间
6.5.3 更改大文件表空间
6.5.4 浏览大文件表空间的信息
6.6 管理Sysaux表空间
6.6.1 创建Sysaux表空间
6.6.2 Sysaux表空间的使用限制
6.7 表空间加密
6.7.1 为什么需要加密表空间
6.7.2 创建Oracle钱夹
6.7.3 创建一个加密的表空间
6.8 管理表空间的数据字典视图
6.8.1 DBA TABLESPACE
6.8.2 DBA FREE SPACE
……
第7章 模式管理
第三部分 安装和升级到Oracle Database 11g ，创建数据库
第9章 安装和升级到Oracle Database 11g
第10章 创建Oracle数据库
第四部分 连接和用户管理
第11章 连接和网络
第五部分 数据装载，备份和恢复
第13章 装载和转换数据
第14章 使用数据泵导出和导入
第15章 备份数据库
第16章 数据库恢复
第六部分 管理Oracle 数据库
第17章 自动管理和联机功能
第18章 管理和监控运行中的数据库
第七部分 性能调优
第19章 改进数据库性能：SQL查询优化
第20章 Oracle性能调优，调优实例
附录A Oracle Database 11g SQL 和PL/SQL 入门知识
20150723 启动oracle所有的实例已经启动
Sql>select * from v$version;
查看实例：Ps –aef
grep ora_
设置临时环境变量:$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib
初始化参数保位置：/soft/ora1120/db/dbs
Sys的默认密码是：oracle
Chapter 10 creating database;
Script:
Create database nina
User sys identified by sys_password
User system identified by system_password
Logfile group 1 (‘/u01/app/oracle/oradta/nina/redo01.log’) size
100M,
a. group 2 (‘/u01/app/oracle/oradta/nina/redo01.log’) size
100M,
b. group 1 (‘/u01/app/oracle/oradta/nina/redo01.log’) size
100M
maxlogfiles 5
maxlogmembers 5
maxloghistory 1
maxdatafiles 300
character set US7ASCII
national charater set AL16UTF16
extent management local
datafile ‘/u01/app/oracle/oradata/nina/system01.dbf’ size 500M
reuse
default tablespace users
datafile ‘/u01/app/oracle/oradta/nina/users01.dbf’
size 500M reuse autoextend on maxsize unlimited
default temporary tablespace tempts1
tempfile ‘/u01/app/oracle/oradata/nina/temp01.dbf’
size 200m ruse
undo tablespace undotbs
datafile ‘/u01/app/oracle/oradata/nina/undotbs01.dbf’
size 200m ruse autoextend on maxsize unlimited;
create tablespace :
create tablespace sales01
datafile ‘/u02/app/oracle/oradata/nina/sales01.dbf’ size
500M
select new feature of tablespace :
select tablespace_name,extent_management,
allocation_type,segment_space_management
from dba_tablespaces;
4. create parameter file:
create spfile From pfile =’/u01/app/oracle/dbs/init.ora’;
create spfile =’/u03/app/oracle/dbs/nina_spfile.ora’ from
pfile=’/u03/app/oracle/dbs/initnina.ora’;
4.2 set dynamic parameter:
Alter system set
log_archive_dest_2=’location=/test02/app/oracle/oracle/oradata/arch’
Scope=spfile;
5. sometimes maintain db does not need us to access:
Startup nomount;
Correlate db with instance :
Alter database mount;
Installed pattern start:
Startup mount;
START open:
Alter database open;
Read-only pattern: startup mount;
Alter database open read only;
Close database:
Shutdown normal
transactional
immediate
abort(need instance recovery)
Use dictionary to monitor database status: select
INSTANCE_NAME,status,shutdown_pending,active_state from
v$instance;
Chapter 11 connect and networking
6.manage listening: lsnrctl help ;
LSNRCTL> set password
LSNRCTL>change_password
LSNRCTL>save_config
7. 0racle connect java : (“jdba:oracle:thin
@prod:1521:instance_name”,username,passwd)
Chapter 12 mangaging user and database security
1. Create user username identified by password;
Grant connection: grant create session to username;
Tablespace limted : alter user username quota 100M on users;
Unlimited tablespace: grant unlimited tablespace to
username;
Check allocated quta : select TABLESPACE_NAME,USERNAME,BYTES
from dba_ts_quotas;
1.1 default resource limited: select RESOURCE_NAME,LIMIT from
dba_profiles where profile = 'DEFAULT';
1.2 allocate file to a user: SQL>create user
username identified by password
Temp tablespace temptbs01
Default tablespace users;
Profile ‘prod_user’;
1.3 change user outline file: SQL>alter profile
test
LIMIT
Sessions_per_user 4
Failed_login_attempts 4;
1.4 password manage function:
$ORACLE_HOME/rdbs/admin/utlpwdmg.sql
take affect outline file : alter system set
resource_limit=true;
1.5 delete outline file : drop profile test CASCADE;
2. database resource mangager
2.1 must grant: ADMINISTER_RESOURCE_MANAGER
2.2 select dba_rsrc_consumer_groups s view: select
consumer_group,status from dba_rsrc_consumer_groups;
2.3enable database resource manager: alter system set
resource_manager_plan=memebership_plan;
Disble database rsource manager : alter system set
resource_manager_plan=’ ’;
v$session can display database resource manager groups are being
allocated.
3. Access Database :
3.1 Two kinds of oracle privilege (system privileg) ,(object
privilege)
System privilege: advisor create any index alter database
Create session alter system create tablespace audit system
Create user create database link drop user create table
Insert any table
Example: grant create session to hr;
If grant create session to public ,ever account do not need to
get privilege;
Recurse grant: grant create session to username with admin
option;
3.2 object privilege:
SQL>revoke delete any table from pasowner;
Alter select delete execute insert references
Table privilege: select alter delete insert update;
Gran all on tables_name to username with grant option;
Revoke privilege: revoke select,insert on table_name from
username;
Create role: SQL> create ROLE new_dba;
3.2.4 disable and enable user role:
Inster into production_user_profile
(production,userid,attribute,char_value)
values(‘SQL*PLUS’,’TESTER’,’ROLES’,’TEST123’,);
SQL>commit;
3.2.5 delete role: drop role admin_user;
4. audit : /soft/ora1120/db/rdbms/audit
4.1 open audit: SQL>audit select on
employees;
SQL>Audit update any table;
SQL>Audit session by username;
SQL>Audit select,insert,update,delete on employee
by access whenever successful;
Close audit: SQL>noaudit session;
SQL>noaudit delete any table by username whenever
not successful;
SQL>noaudit delete any table by username;
4.3 query audit: MASTER PREPROD(SYS@SRP01) SQL>
select timestamp,db_user,os_user,object_schema,object_name,sql_text
from dba_fga_audit_trail;
MASTER PREPROD(SYS@SRP01) SQL> desc
dba_fga_audit_trail;
Name Null? Type
----------------------------------------- --------
----------------------------
SESSION_ID NOT NULL NUMBER
TIMESTAMP DATE
DB_USER VARCHAR2(30)
OS_USER VARCHAR2(255)
USERHOST VARCHAR2(128)
…
5. authentication user
5.1 database authentication
5.1.1 manage password status: select username ,account_status
from dba_users;
5.1.2 case sensitive: sec_case_sensitive_logon=false
select username,password,password_versions from dba_users;
5.1.4 unlock account : alter user username account unlock;
SQL> Create profile test_profile
SQL> Limit failed_login_attempts 5
SQL> Password_lock_time unlimited
5.1.5 password expire:
SQL> Alter user hr identified by hr password
expire;
SQL> Alter profile test_profile Limit
password_life-time 30;
5.1.6 password file: SQL>select * from
v$pwfile_users;
6. security
6.3 add a new lie :SQL> alter table employees add
(encrypt_ID number(19) encrrpt)
7. Oracle internet directory
7.6 grant any privilege:
SQL>select count(*) from dba_tab_privs where
grantee=’public’;
7.14 manage userful teachnology
7.14.1 change outline file: alter profile fin_user limit
failed_login_attempts 5 password_lock_time 1;
Part 5
Chapter 13 data loading and data transformation
ETL(extraction-transformation-loading)
Chapter 14 data pump export and import
1.Expdp and impdp
$expdp username/password (various parameters)
$impdp username/password (various parameters)
Implment API (because of DBMS_DATA_PUMP )
Datapump :DBMS_DATAPUMP DATA_METADATA command client
1.5 datapump file : select * from dba_directories;
Datapump export: $expdp username/password directory=dump_dir1
dumpfile=textexp01.dmp
1.6 datapump privilege: EXP_FULL_DATABASE IMP_FULL_DATABASE
2. datapump export parameter;
2.3 export filter parameter: ALL (tables data,table,other define
object ) DATA_ONLY(only table rows) METADATA_ONLY(export raw
data);
2.7 NETWORK_LINK参数： impdp hr/hr TABLES=employees
DIRECTORY-dpump_dir1 SCHEMAS=SCOTT EXCLUDE=CONTRAINT
NETWORK_LINK=finance
Chapter 15 backup database,archive log,flash back
2. flash recovery area
2.1 benefit: archive log list;
2.4 backup the flash area : select * from
V$RECOVERY_FILE_DEST;
V$LOGFILE,V$CONTROLFILE,V$ARCHIVED_LOG,V$DATAFILE_COPY,V$BACKUP_PIECE
added IS_recover_DEST_FILE 列，if it’s values is yes, indicate it is
in the flash area;
3 . connect rman:
3.1 connect rman via database:
PREPROD SRP01
mwpsrpu1@yvas9940:/users/login/mwpsrpu1>sqlplus / as
sysdba;
Disconnected from Oracle Database 11g Enterprise Edition Release
11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application
Testing options
PREPROD SRP01
mwpsrpu1@yvas9940:/users/login/mwpsrpu1>rman
Recovery Manager: Release 11.2.0.3.0 - Production on Wed Aug 19
10:34:58 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All
rights reserved.
RMAN> connect target /
connected to target database: SRP01 (DBID=314748672)
RMAN>
3.2 certficate via system: rman target /;
3.3 recovery directory nick ,target database is orcl: rman
target orcl catalog rman/rman@nick;
3.4 assign output to a log file: rman LOG
/u01/app/oracle/rman.log;
3.6Rman scripts:
Connect target / run
{
Allocate channel c1 type ‘sbt_tape’
Allocate channel c2 type ‘sbt_tape’
Backup
Format ‘full d%d_u%u’
Filesperset 10
Database;
Release channel c1;
Release channel c2;
}
Check rman script syntax:
Rman checkyntax @/tmp/testfile
Restore database;
Restore database;
Global scripts:
Rman> create global script global_full_backup
{
backup database plus archivelog;
delete obsolete;
}
Create dynimamic scripts:
Create script quarterly{
Allocate channel c1
Device type sbt
Parms ‘env=(OB_meida_family=&1)’;
Backup
Tag &2
Format ‘/disk2/bck/&1%U.bck’
Keep forever
Restore point &3
Database;
}
3.7Replication
Create a image copy o f database: Rman>backup as
copy database;
Create a image copy of tablespace: rman>backup as
copy tablespace sysaux;
Copy a datafile: rman>backup as copy datafile
2;
3.8 rman backup location:
Rman> backup database format ‘/tmp/%U’;
3.9 rman command
Backup
job
copy
report
list
check command;
Rman>List backup;
Rman>show all;
MASTER PREPROD(SYS@SRP01) SQL> select * from
v$rman_configuration;
3.21monitoring and verifing rman
SQL> select operation,status,start_time,end_time
from v$rman_status;
Crosscheck backupset ;
6.1completely backup databases;
SQL> select * from dba_data_files;
SQL> select member from v$logfile;
SQL> select memeber from v$controlfile;
Cold copy scripts:
ORACLE_SID=$1
export ORACLE_SID
export ORAENV_ASK=NO
BACKUP_DIR=/test01/app/oracle
Sqlplus –s system/password
<
p="">
Set head off feed off echo off trimspool on linesize 200
Spool /u01/app/oracle/dba/cold_bachup.sh
Select ‘cp’
file_name
‘${BACKUP_DIR}’ from sys.dba_data_files;
Select ‘cp’
name
‘${BACKUP_DIR}’ from v$controlfile;
Select ‘cp’
member
‘${BACKUP_DIR}’ from v$logfile;
Spool off;
Exit;
EOF
Hot copy script:
ORACLE_SID=$1
export ORACLE_SID
export ORAENV_ASK=NO
BACKUP_DIR=/u01/app/oracle/backup
Export BACKUP_DIR
Sqlplus –s system/password
<
p="">
Set head off feed off on linesize 200
Spool /u01/app/oracle/dba/hot_bachup.sh
Begin
Dbms_output.put_line(‘alter database begin backuo;’)
For f1 in (select file_name fn from from sys.dba_datafiles)
Loop
Dbms_output.putline(‘host copy’
f1.fn
’$BACKUP_DIR’);
End loop;
Dbms_out.put_line(‘alter database end backup;’);
Dbms_out.put_line(‘alter database backup;’);
Controlfile to ‘
’$BACKUP_DIR/control’
’;’;
Dbms_out.put_line(‘alter system switch logfile;’);
End;
/
Spool off;
Exit;
EOF
6.3 monitor user manage online backup;
View:
V$BACKUP V$DATAFILE V$LOG V$ARCHIVED_LOG V$LOG_HISRTORY
Oracle real application clusters.
Chapter 16 database recovery
3.8 monitor rman jobs
select OPERATION,STATUS from v$rman_status;
repair failure :
rman>repair failure preview;
v$ir_repair
sql>desc v$ir_repair
6.2 database control clone database ;
6.3manual clone database;
SQL>alter database backup controlfile to
trace;
Sql> create controlfile reuse set database “test”
resetlogs noarchivelog
Sql>alter database open resetlogs using backup
controlfile;
Sql>update global_name set global
name=’test.world’;
7.Auxiliary;
8.3.2
Recyclebin:
Sql>select
owner,original_name,object_name,ts_name,droptime from
dba_recyclebin;
Sql>show recyclebin
Sql>select * from “sadlkhfl;qjselkl3nfpoqi0”;
8.3.3Restore table that has been deleted;
Sql>flashback table persons t0 before drop;
8.3.4 delete table permantly;
Sql>purge table persons;
8.4 flashback database;
8.4.2 disable flashback database;
Sql>shutdown immediate;
Sql>startup mount;
Sql>alter database flashback off;
Chapter 17 ADDM
2. automatic memory management
2.1 classify :auto memeory management; auto shared memory
management automatic pga memory management; manual shared memory
management;
2.2 running automatic memory management;
(1)Check current sga value:
SQL> show parameter SGA_TARGET
NAME TYPE VALUE
------------------------------------
--------------------------------------------
------------------------------
sga_target big integer 5008M
(2) certain time allocate max value for PGA :
SQL> select value from v$PGASTAT where
name='maximum PGA allocated';
VALUE
----------
1039506432
(3) SQL> show parameter pga_aggregate_target;
NAME TYPE VALUE
------------------------------------
--------------------------------------------
------------------------------
pga_aggregate_target big integer 5000M
2.3 tunning automatic memory management;
SQL> select * from v$memory_target_advice order
by memory_size;
3.addm tunning program stastics;
3.1 disable gather_stats_job
Begin dbms_scheduler.disable(‘gather_stats_job’) end;
4. automatic storge manage
SQL> select instance_name from v$instance;
INSTANCE_NAME
----------------
SRP01
SQL> select name from v$database;
NAME
---------
SRP01
Backup and restore tablespace:
Rman>backup tablespace users;
SQL> select * from v$tablespace;
5.add tablespace;
SQL> select file_name from dba_data_files ;
6.oracle database 11g online function;
Sql>alter index test_idx rebuild online;
Sql>create index test_idx on persons(persion_id)
online;
Sql>alter index test_idx coalesce;
Move tablespace to another tablespace:
Sql>alter table move tablespace new_sbsp;
6.2
Select object_type,object_name from dba_objects where
object_name=’employees’;
Select name,value from v$spparameter where name =
‘db_block_size’;
Chapter 18 manage and moniter running database;
1.Oracle performance statistics style;
4.generate a awr reports:
Sql>@$ORACLE_HOME/rdbms/admin/awrrpt.sql
8.8monitor redo log
Sql> select * from v$logfile;
Detail info:
SQL> select
group#,sequence#,bytes,archived,members,status from v$log;
GROUP# SEQUENCE# BYTES ARCHIVED MEMBERS STATUS
---------- ---------- ---------- ------------ ----------
----------------------------------------------------------------
1 40074 1073741824 YES 2 INACTIVE
15.check alert -tail;
adrci> show homes;
adrci> show incident;
part 7
chapter 19 sql tunning
1.oracle tunning methods:
2. performance improving
3.select improving:
Check column statistics info: select column_name,num_distinct
from dba_tab_col_statistics where table_name='personnel';
3.4 set improving program tunning level:
Select name,value from v$parameter where name =
‘optimizer_mode’;
Gather data from view: excute dbms_stats_schema_stats (ownname
=> ‘hr’);
Gather data from table:excute_stats.gather_table_stats
(‘hr’,’employees’ );
Excute dbms_stats.gather_database_stats(estimate_precent
=> null, method_opt => ‘for all
columns size auto’, granularity => ‘all’,cascade
=> ‘true’,options => ‘gather
auto’);
Select table_name,last_analyzed from dba_tables;
3.8 SQL> show parameter optimizer_use%;
optimizer_use_pending_statistics boolean FALSE
SQL> select count(*) from sh.customers where
cust_state_province = 'CA';
3.10 collect the stats with procedure:
SQL> select * from sys.aux_stats$;
19.9 use V$SQL view to seek ineffective sql:
SQL> select
SQL_TEXT,EXECUTIONS,BUFFER_GETS,DISK_READS from V$SQL where
BUFFER_GETS > 100000 OR DISK_READS >
100000 order by BUFFER_GETS + 100*DISK_READS DESC;
Etc:
EXECUTIONS BUFFER_GETS DISK_READS
---------- ----------- ----------
8538 103320 0
MERGE INTO STATS_TARGET$ ST USING (SELECT STALENESS, OSIZE,
OBJ#, TYPE#, AFLAGS, STATUS, SID, SERIAL#, PART#, BO# FROM ( SELECT
DECODE(BITAND(T.FLAGS,16), 16, ROUND( LOG(0.01, NVL( LEAST( 100,
GREATEST( 0.01, (DECODE(BITAND(M.FLAGS, 1), 1, GREATEST(T.ROWCNT,
M.INSERTS), LEAST((M
.INSERTS + M.DELETES + M.UPDATES), GREATEST(T.ROWCNT, (T.ROWCNT
+ M.INSERTS - M.DELETES)))) / (T.ROWCNT + 0.01)))), 0.01)), 1),
-100.0) STALENESS, CASE WHEN T.FILE# = 0 THEN
DBMS_STATS_INTERNAL.GET_TA
BLE_BLOCK_COUNT(U.NAME, O.NAME, NULL, NULL, 'TRUE') WHEN S.TYPE#
= 5 THEN DBMS_STATS_INTERNAL.SEGMENT_NUMBER_BLOCKS(T.TS#, T.FILE#,
T.BLOCK#, S.TYPE#, S.CACHEHINT, NVL(S.SPARE1,0), O.DATAOBJ#,
S.BLOCK
S, 'TRUE') ELSE NULL END * NVL(TS.BLOCKSIZE, :B6 ) OSIZE, O.OBJ#
OBJ#, O.TYPE# TYPE#, 32 + CASE WHEN ((BITAND(T.FLAGS,16) = 16 AND
(BITAND(M.FLAGS,1) != 0 OR (M.INSERTS + M.UPDATES + M.DELETES)
> NVL(
2 103296 0
SQL_TEXT
19.10 SQL tuning advisor
You can use not in replace not exits;
10.6 use other GUI tools
TOAD software for free quest software (http://www.quest.com)
Chapter 20 oracle memory tunning
20.3.3 tuning the fast cache
SQL> desc V$BUFFER_POOL_STATISTICS;
Name Null? Type
----------------------------------------- --------
----------------------------
ID NUMBER
NAME VARCHAR2(20)
BLOCK_SIZE NUMBER
SET_MSIZE NUMBER
CNUM_REPL NUMBER
CNUM_WRITE NUMBER
CNUM_SET NUMBER
Physical reads
Consistents reads
Logic reads
Cache gets
SQL> select
name,PHYSICAL_READS,DB_BLOCK_GETS,CONSISTENT_GETS,1 -
(PHYSICAL_READS/(DB_BLOCK_GETS+CONSISTENT_GETS)) "hitratio" from
V$BUFFER_POOL_STATISTICS;
NAME PHYSICAL_READS DB_BLOCK_GETS CONSISTENT_GETS hitratio
-------------------- -------------- -------------
--------------- ----------
DEFAULT 134819811 171677068 2.7839E+10 .995186919
SQL> desc x$bh
Name Null? Type
----------------------------------------- --------
----------------------------
ADDR RAW(8)
INDX NUMBER
INST_ID NUMBER
HLADDR RAW(8)
SQL> desc V$Parameter;
Name Null? Type
----------------------------------------- --------
----------------------------
NUM NUMBER
NAME VARCHAR2(80)
TYPE NUMBER
VALUE VARCHAR2(4000)
DISPLAY_VALUE VARCHAR2(4000)
ISDEFAULT VARCHAR2(9)
ISSES_MODIFIABLE VARCHAR2(5)
ISSYS_MODIFIABLE VARCHAR2(9)
ISINSTANCE_MODIFIABLE VARCHAR2(5)
ISMODIFIED VARCHAR2(10)
ISADJUSTED VARCHAR2(5)
ISDEPRECATED VARCHAR2(5)
20.3.5.2 Set pga_aggregate_target parameter values;
SQL> select * from v$pgastat;
NAME VALUE UNIT
----------------------------------------------------------------
---------- ------------------------------------------------
aggregate PGA target parameter 7340032000 bytes
aggregate PGA auto target 6397102080 bytes
global memory bound 734003200 bytes
total PGA inuse 232138752 bytes
total PGA allocated 398264320 bytes
maximum PGA allocated 5501677568 bytes
total freeable PGA memory 131072000 bytes
process count 122
max processes count 135
PGA memory freed back to OS 3.4390E+12 bytes
total PGA used for auto workareas 0 bytes
use v$SQL_WORKAREA_HISTOGRAM
select LOW_OPTIMAL_SIZE/1024 "log (K)" (HIGH_OPTIMAL_SIZE +
1)/1024 "high (k)", OPTIMAL_EXECUTIONS
"1-Pass",MULTIPASSES_EXECUTIONS ">1 Pass" from
v$SQL_workarea_histogram where TOTAL_EXECUTIONS
<> 0;
REPROD (DCRC0) dcr00
mwpdcr00@yval01q0:/users/login/mwpdcr00>sar -u 10
5
Linux 3.0.101-0.7.29-xen (yval01q0) 11/04/15 _x86_64_
04:29:01 CPU %user %nice %system %iowait %steal %idle
04:29:11 all 0.18 0.00 0.23 0.01 0.03 99.56
04:29:21 all 2.08 0.00 8.52 0.00 0.06 89.34
04:29:31 all 0.97 0.00 2.88 0.01 0.03 96.11
04:29:41 all 0.20 0.06 0.53 0.01 0.04 99.16
04:29:51 all 0.13 0.00 0.57 0.01 0.03 99.27
Average: all 0.71 0.01 2.54 0.01 0.04 96.69
PREPROD (DCRC0) dcr00
mwpdcr00@yval01q0:/users/login/mwpdcr00>
20.4.1.3. check high cpu users:
SQL> select n.username,s.sid,s.value from
v$sesstat s,v$statname t,v$session n where s.statistic# =
t.statistic# and n.sid = s.sid and t.name ='CPU used by this
sesion' order by s.value desc
20.4.1.4 devide the cpu :
select name,value from v$sysstat where name in ('CPU used by
this session','recursive cpu usage',*'parse time cpu;);
SQL> select name,value from v$sysstat where name
like '%CPU%';
NAME VALUE
----------------------------------------------------------------
----------
OS CPU Qt wait time 0
CPU used when call started 87342213
CPU used by this session 93001130
IPC CPU used by this session 0
global enqueue CPU used by this session 0
gc CPU used by this session 0
cell physical IO bytes sent directly to DB node to balance CPU
0
20.5 locate the I/O performance
20.5.1 locate the I/O distribution:
SQL> select name,value from v$sysstat where name
like '%CPU%';
NAME VALUE
----------------------------------------------------------------
----------
OS CPU Qt wait time 0
CPU used when call started 87342213
CPU used by this session 93001130
IPC CPU used by this session 0
global enqueue CPU used by this session 0
gc CPU used by this session 0
cell physical IO bytes sent directly to DB node to balance CPU
0
7 rows selected.
20.6 measure instance performance;
select event,total_waits,time_waited from v$system event where
event not in ('pmon timer','smon timer',rdbms ipc reply','parallel
deque wait','virtual circuit','%SQL*net%','client message','null
event' order by TIME_WAITED desc;
most important events:
the user of waiting more:
SQL> select o.object_name,o.object_type,a.event,
sum(a.wait_time + a.time_wait_time) total_wait_time from
v$active_session_history a where a.sample_time between sysdate -
30/2880 and sysdate group by a.event order by total_wait_time
desc;
SQL> select WAIT_CLASS,TIME_WAITED from
v$session_wait_class where sid = 1053 order by TIME_WAITED
desc;
20.6.4.9 use oracle program trace :
20.6.4.10 cache area busy in db block:
SQL> select class,count from v$waitstat where
count > 0 order by count desc;
CLASS COUNT
------------------------------------------------------------------------
----------
data block 20054
undo block 278
undo header 99
segment header 7
1st level bmb 2
Busy cache district,checkpoint completed,db fiel scattered read
,db file sequential read ,direct path and direct path write ,free
buffer waits,enqueue waits ,latch free,log buffer space ,log file
switch,log file sync ,idle events
20.6.7 use addm analyze performance:
20.6.8 use awr report to sql:
SQL> @$oracle_home/rdbms/admin/awrsqrpt.sql;
20.6.10 ash report recent activity:
SQL> @ORACLE_HOME/rdbms/admin/ashrpt.sql
20.6.15 lock problem
select l.OBJECT_ID ,l.SESSION_ID,
l.ORACLE_USERNAME,l.LOCKED_MODE, o.dba_OBJECTS from V$LOCKED_OBJECT
l,dba_OBJECTS o where o.object_id=l.OBJECT_ID;
20.7 instance tuning simple method
20.7.3 check the long time running task
20.7.6 check the cpu limt
20.8 real test
20.8.2 sql performance analyzer
Append index Ending Oracle database 11g SQl and PL/SQL
3.8 function
Data and
SQL> select sysdate from dual;
SYSDATE
------------------
05-NOV-15
166
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
