oracle学习笔记
oracle安装
1. 安装virtualbox：
# yum install gcc kernel-devel kernel-headers
# yum install virtualbox-5....
# /etc/init.d/vboxdrv setup 手动编译内核模块，安装时自动完成
# usermod -G vboxusers root
2. 安装linux：
创建虚拟机：
名称：oracle11gR2_RHEL6.4_x64
类型：linux 64bit
内存：2048MB
硬盘大小：100GB
设置：
启动顺序：硬盘，网络
网络：网卡1，桥接eth0
去除声音、usb设备
安装：desktop方式
主机名：node1.test.com，ip使用dhcp，swap4GB，其余给/
3. 调整linux系统：
关闭防火墙：
# service iptables stop
# service ip6tables stop
# chkconfig iptables off
# chkconfig ip6tables off
管理工具中disabled防火墙
关闭selinux：
# vi /etc/selinux/config
SELINUX=disabled
配置yum：
# rm -f /etc/yum.repos.d/*
# wget ftp://172.16.8.100/rhel6.repo -P /etc/yum.repos.d/
或者：
# vi /etc/yum.repos.d/rhel6.repo
[Server]
name=Server
baseurl=file:///media/"RHEL_6.5 x86_64 Disc 1"/Server
enabled=1
gpgcheck=0
安装vb增强功能：
# yum -y install gcc kernel-devel
# ln -s /usr/src/kernels/2.6.32-431.el6.x86_64/ /usr/src/linux
设备-->安装增强功能
右键eject弹出光盘
4. 确认root身份:
# id
5. 硬件配置：
# grep MemTotal /proc/meminfo
# grep SwapTotal /proc/meminfo
# vi /etc/fstab（永久修改）
tmpfs /dev/shm tmpfs defaults,size=2G 0 0
# mount -o remount /dev/shm
临时修改
# mount -t tmpfs shmfs -o size=2g /dev/shm
# uname -m
# df -h
6. rpm检查并安装：
# cat /etc/redhat-release
# uname -r
# rpm -qa | grep glibc
# yum install glibc
# yum install glibc.i686
binutils-2.20.51.0.2-5.11.el6 (x86_64)
compat-libcap1-1.10-1 (x86_64)
compat-libstdc++-33-3.2.3-69.el6 (x86_64)
compat-libstdc++-33-3.2.3-69.el6.i686
gcc-4.4.4-13.el6 (x86_64)
gcc-c++-4.4.4-13.el6 (x86_64)
glibc-2.12-1.7.el6 (i686)
glibc-2.12-1.7.el6 (x86_64)
glibc-devel-2.12-1.7.el6 (x86_64)
glibc-devel-2.12-1.7.el6.i686
ksh
libgcc-4.4.4-13.el6 (i686)
libgcc-4.4.4-13.el6 (x86_64)
libstdc++-4.4.4-13.el6 (x86_64)
libstdc++-4.4.4-13.el6.i686
libstdc++-devel-4.4.4-13.el6 (x86_64)
libstdc++-devel-4.4.4-13.el6.i686
libaio-0.3.107-10.el6 (x86_64)
libaio-0.3.107-10.el6.i686
libaio-devel-0.3.107-10.el6 (x86_64)
libaio-devel-0.3.107-10.el6.i686
make-3.81-19.el6
sysstat-9.0.4-11.el6 (x86_64)
7. 创建用户：
# groupadd -g 1000 oinstall
# groupadd -g 1001 dba
# groupadd -g 1002 oper
# useradd -u 1000 -g oinstall -G dba,oper oracle
# passwd oracle
8. 修改内核参数：
# vi /etc/sysctl.conf
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
# sysctl -p
9. 修改资源限制：
# vi /etc/security/limits.conf
oracle soft nofile 1024
oracle hard nofile 65536
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft stack 10240
oracle hard stack 32768
10. 创建目录：
# mkdir -p /u01/app/oracle
# chown -R oracle:oinstall /u01
# chmod -R 775 /u01
11. 修改概要文件：
# vi ~oracle/.bash_profile
export ORACLE_SID=orcl
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export ORACLE_HOSTNAME=node1.test.com
export ORACLE_UNQNAME=orcl
export PATH=$PATH:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
export NLS_LANG=american_america.AL32UTF8
export NLS_DATE_FORMAT='yyyy-mm-dd hh34:mi:ss'
export EDITOR=vi
export
12. 使用hosts文件解析主机名：
# hostname
node1.test.com
# vi /etc/hosts
192.168.0.1 node1.test.com node1
# ping node1
# ping node1.test.com
13. 解压缩安装包：
# cd /installation
# unzip p10404530_112030_Linux-x86-64_[12]of7.zip
或者：
在物理主机中解压缩2个安装包，然后共享给虚拟机，命令如下：
# mkdir /database
# mount -t vboxsf database /database
14. 图形界面安装：
# xhost +
# su - oracle
$ cd /database/
$ ./runInstaller
略
以root身份执行两个脚本
15. 创建监听和数据库：
图形界面下创建监听，oracle执行：
$ netca
$ lsnrctl status
$ netstat -tlnp | grep 1521
图形界面创建db，oracle执行：
$ dbca
16. 测试：
sqlplus测试：
$ sqlplus sys/sys@orcl as sysdba
$sqlplus/as sysdba
SQL> show user
SQL> select count(*) from hr.employees;
SQL> exit
浏览器测试：
https://192.168.0.1:1158/em
sys/password sysdba
安装rlwrap：
root身份安装
# yum install rlwrap-0.42-1.el6.x86_64.rpm
# vi ~oracle/.bashrc
alias sqlplus='rlwrap sqlplus'
alias rman='rlwrap rman'
# su - oracle
$ sqlplus / as sysdba
root身份安装
# yum install flash-plugin-11.2.202.508-release.x86_64.rpm
sqldeveloper测试：
root身份安装
# yum install jdk-8u51-linux-x64.rpm
# yum install sqldeveloper-4.1.1.19.59-1.noarch.rpm
# /usr/local/bin/sqldeveloper
输入jdk的路径 /usr/java/jdk1.8.0_51/
点左上角+号，创建新连接：
sys/password，连接类型：basic，角色：sysdba
192.168.0.1端口1521 sid：orcl
测试，保存。
17. 添加启动脚本：
# vi /etc/oratab 把N改为Y
orcl:/u01/app/oracle/product/11.2.0/db_1:Y
# vi /etc/rc.d/init.d/oracle
#!/bin/bash
#chkconfig:35 99 01
case "$1" in
start)
echo -n "Starting Oracle Database& Listener:"
su - oracle -c 'dbstart $ORACLE_HOME' >/dev/null
echo "[ ok ]"
echo -n "Starting Oracle EM dbconsole:"
su - oracle -c "emctl start dbconsole" >/dev/null
echo "[ ok ]"
touch /var/lock/subsys/oracle
;;
stop)
echo -n "Shutting Down Oracle EM dbconsole:"
su - oracle -c "emctl stop dbconsole" >/dev/null
echo "[ ok ]"
echo -n "Shutting Down Oracle Database& Listener:"
su - oracle -c 'dbshut $ORACLE_HOME' >/dev/null
echo "[ ok ]"
rm -f /var/lock/subsys/oracle
;;
*)
echo "Usage: oracle {start|stop}"
exit 1
esac
# chmod 755 /etc/rc.d/init.d/oracle
# chkconfig --add oracle
# chkconfig --list oracle
18. 删除口令有效期：
#su - oracle
$ sqlplus / as sysdba
SQL> alter profile default limit PASSWORD_LIFE_TIME unlimited;
19. 修改virtualbox的网络设置（可选）：
全局设置à添加hostonly网络，设置ip为：192.168.0.253，取消dhcp（linuxonly）
虚拟机网络改为hostonly
虚拟机中将网卡的ip改为静态，192.168.0.1
# vi /etc/sysconfig/network-scripts/ifcfg-eth0
BOOTPROTO=none
IPADDR=192.168.0.1
PREFIX=24
# ifdown eth0;ifup eth0
# vi /etc/hosts
192.168.0.1 node1.test.com node1
SQL
select查询语句
1. 查看hr用户名下的表，解锁hr用户：
$ sqlplus / as sysdba或SQL> conn / as sysdba
SQL> show user
SQL> select table_name from dba_tables where owner='HR';
SQL> select * from hr.employees;
SQL> alter user hr account unlock identified by hr;
$ sqlplus hr/hr或者SQL> conn hr/hr
SQL> show user
SQL> select * from tab;
SQL> desc employees
练习：
查看scott用户名下的表，解锁scott用户：
scott/tiger
2. 使用sqlplus的全屏编辑功能：
$ echo $EDITOR
SQL>select * from hr.employees;
SQL> ed
SQL> / 执行
3. 基础select语句：
SQL> select * from employees;
SQL> desc employees
SQL> select LAST_NAME, SALARY, COMMISSION_PCT from employees;
SQL> desc departments
SQL> select department_id, department_name from departments;
SQL> select distinct DEPARTMENT_ID from employees;
SQL> select last_name, salary*12*(1+commission_pct) total_salary, department_id from employees;
SQL> select first_name||', '||last_name from employees;
SQL> select first_name||', '||last_name fullname from employees;
练习：
输出下列员工信息：
Eleni(first_name) Zlotkey(last_name) employeeid is ... at department .. total salary is …
4. 使用连字符构造语句：
SQL> select table_name from user_tables;
SQL> select 'grant select on hr.'||table_name||' to scott;' from user_tables;
SQL> spool /home/oracle/grant.sql
SQL> set head off 去除标题
SQL> set feed off 去除回馈
SQL> select 'grant select on hr.'||table_name||' to scott;' from user_tables;
SQL> spool off
$ vi /home/oracle/grant.sql 手动去除没用的行，相当于除去标题和回馈，
SQL> @/home/oracle/grant.sql 执行sql脚本
5. 单引号的处理：
SQL> select 'I'm teaher' from dual;
ERROR:
ORA-01756: quoted string not properly terminated
SQL> select 'I''m teaher' from dual;
SQL> select q'{I'm teaher}' from dual; []<>()都可以
where和orderby
数字条件：
SQL> select salary from employees where employee_id=100;
字符串大小写敏感：
SQL> select last_name, salary from employees where last_name='King';
SQL>select table_name, tablespace_name from user_tables where table_name='EMPLOYEES';
日期是格式敏感：
SQL> alter session set nls_date_format='RR-Mon-dd';
SQL> select last_name from employees where hire_date='2006-05-23';
SQL> select last_name from employees where hire_date=to_date('2006-05-23', 'yyyy-mm-dd');
区间查询：
SQL> select last_name from employees where salary>=3400 and salary<=4000;
SQL> select last_name from employees where salary between 3400 and 4000;
SQL> select last_name from employees where salary between 3000 and 5000 and department_id=50;
in：
SQL> select last_name from employees where department_id=30 or department_id=40 or department_id=50;
SQL> select last_name from employees where department_id in (30, 40, 50);
通配符：
SQL> select last_name, job_id from employees where job_id like '%\_MAN' escape '\';禁止转义符后面的符号的特殊含义，like _通配某一个字符，like %通配某些字符
null作为条件：
SQL> select last_name from employees where commission_pct is null;
SQL> select last_name from employees where commission_pct is not null;
and/or/not：
SQL> select last_name, job_id, department_id, salary from employees where job_id='SA_REP' or department_id=50 and salary>=8000;
SQL> select last_name, job_id, department_id, salary from employees where (job_id='SA_REP' or department_id=50) and salary>=8000;
排序：
SQL> select last_name, salary from employees order by salary;升序
SQL> select last_name, salary from employees order by salary desc;降序
SQL> select last_name, salary from employees order by last_name;
SQL> select last_name, hire_date from employees order by hire_date;
SQL> select last_name, salary, commission_pct from employees order by salary desc, commission_pct desc;
SQL> select last_name, salary*12*(1+commission_pct) from employees order by 2;
SQL> select last_name, salary*12*(1+commission_pct) total_salary from employees order by total_salary;
练习：
选择部门30中的雇员
列出所有职员(CLERK)的姓名、编号和部门
找出薪金大于5000的雇员
找出奖金高于0.1的雇员
找出部门50中的所有员工和部门30中的经理的详细资料
找出收取奖金的雇员的不同工作职位每种职位显示一次
找出不收取奖金或收取的工资低于5000的雇员
显示last_name不带有'R'的雇员姓名
select last_name name from employees where not last_name like '%R%'；
显示所有雇员的姓名、工作和薪金，按工作的降序顺序排序，而工作相同时按薪金升序
单行函数
SQL> select upper(first_name), lower(last_name), length(last_name) from employees;
SQL> select (sysdate-hire_date)/7 from employees;
SQL> select trunc((sysdate-hire_date)/30, 0) from employees;
SQL> select trunc(months_between(sysdate,hire_date), 0) from employees;
SQL> select sysdate+3650 from dual;
SQL> select add_months(sysdate, 120) from dual;
SQL> select next_day('2015-09-01', 'friday') from dual;
SQL> select next_day('2015-10-01', 6) from dual;
SQL> select last_day(sysdate) from dual;
SQL> select round(to_date('2015-10-10','yyyy-mm-dd'), 'MONTH') from dual;
SQL> select round(to_date('2015-10-16','yyyy-mm-dd'), 'MONTH') from dual;
SQL> select round(to_date('2015-10-10','yyyy-mm-dd'), 'YEAR') from dual;
SQL> select round(sysdate, 'DAY') from dual;
练习：
找出各月最后三天内受雇的所有雇员
extract(month from hire_date+4) != extract(month from hire_date)
找出早于25年之前受雇的雇员
months_between(sysdate, hire_date)/300>=25
显示正好为6个字符的雇员姓名
length(last_name)=6
显示所有雇员的姓名的前三个字符
substr(last_name, 1, 3)
显示所有雇员的姓名，用a替换所有'A'
replace(last_name, 'A', 'a')
类型转换和其他函数
SQL> select to_char(salary, '$999,999.00') from employees;
SQL> select last_name, to_char(hire_date, 'dd-Mon-RR') from employees;
SQL> select to_char(sysdate, 'yyyy-mm-dd hh34:mi:ss') from dual;
SQL> select to_char(sysdate, 'yyyy-mm-dd hh:mi:ss AM') from dual;
SQL> select last_name from employees where hire_date=to_date('2006-05-23', 'yyyy-mm-dd');
SQL> select to_number('$123,456.78', '$999,999.00') from dual;
练习：
查询2006年入职员工：
select last_name
from employees
where hire_date between to_date('2006-01-01', 'yyyy-mm-dd')
and to_date('2006-12-31', 'yyyy-mm-dd');
select last_name
from employees
where to_char(hire_date, 'yyyy')='2006';
select last_name
from employees
where extract(year from hire_date)=2006;
--不推荐
select last_name
from employees
where hire_date like '2006%';
查询历年9月份入职的员工：
select last_name
from employees
where to_char(hire_date, 'mm')='09';
select last_name from employees where extract(month from hire_date)=9;
其他函数：
nvl：
nvl(val1, val2)
if val1 is not null
then
return val1;
else
return val2;
SQL> select last_name, salary*12*(1+nvl(commission_pct, 0)) total_salary from employees;
练习：
显示所有员工部门编号，没有部门的显示"未分配部门"
select nvl(to_char(department_id),'未分配部门') from employees
case和decode：
IT_PROG +1000
SA_REP+1500
ST_CLERK +2000
其他人工资不变
select salary+1000 from employees where job_id='IT_PROG';
select last_name, job_id, salary,
case job_id
when 'IT_PROG' then salary+1000
when 'SA_REP' then salary+1500
when 'ST_CLERK' then salary+2000
else salary
end new_salary
from employees;
select last_name, job_id, salary,
decode( job_id,
'IT_PROG', salary+1000,
'SA_REP', salary+1500,
'ST_CLERK', salary+2000,
salary) new_salary
from employees;
练习：
按照员工工资，对员工分级显示：
A 20001-25000
B 15001-20000
C 10001-15000
D 5001-10000
E 0-5000
答案：
select last_name,salary,
decode(trunc(salary/5000,0),
0,'E',
1,'D',
2,'C',
3,'B',
4,'A',
salary) n_sal
from employees；
答案拓展：
select last_name,salary,
decode(trunc(salary/5000,0),
0,'E',
1,'D',
2,'C',
3,'B',
4,'A',salary) n_sal
from employees
order by salary desc,n_sal
分组函数
SQL> select count(*), sum(salary), avg(salary), min(salary), max(salary) from employees;
SQL> create table t1(x int);
SQL> insert into t1 values (null);
SQL> insert into t1 values (1);
SQL> commit;
SQL> select count(*) from t1;
SQL> select count(x) from t1;
SQL> select max(x) from t1;
SQL> select min(x) from t1;
SQL> select sum(x) from t1;
SQL> select avg(x) from t1;
SQL> select avg(salary), avg(nvl(commission_pct, 0)) from employees;
SQL> select count(distinct department_id) from employees; 去除重复值
Groupby分组：
SQL> select department_id, avg(salary) from employees group by department_id;
多列分组：
SQL> select department_id, job_id, max(salary) from employees group by department_id, job_id;
SQL> select department_id, job_id, max(salary), last_name from employees group by department_id, job_id; 错误语法
练习：
公司中不同职位的数量
select job_id,count(job_id)
from employees
group by job_id；
计算每个部门的人数
select department_id,count(last_name)
from employees
group by department_id
select department_id,count(department_id)
from employees
group by department_id
按年份分组，求员工的工资总和
select to_char(hire_date,'yyyy') year,sum(salary)
from employees
group by to_char(hire_date,'yyyy')
order by year
selec extract(year from hire_date) year,sum(salary)
from employees
group by extract(year from hire_date)
order by year
Having语句：
SQL> select department_id, avg(salary) from employees where avg(salary)>=5000 group by department_id; 错误语句
SQL> select department_id, avg(salary) from employees group by department_id having avg(salary)>=5000;
练习：
按部门求出所有有部门的普通员工的平均工资，部门平均工资少于5000的不显示，最终结果按平均工资的降序排列。
select department_id,trunc(avg(salary)) avg_sal
from employees
where department_id is not null
group by department_id
having trunc(avg(salary)) >= 5000
order by avg_sal desc
select department_id, avg(salary) avg_sal
from employees
where job_id not like '%\_MGR' escape '\' and department_id is not null
group by department_id
having avg(salary)>=5000
order by avg_sal desc;
多表连接
emp： dept:
empno ename deptno deptno dname
100 abc 10 10 sales
101 def 10 20 market
102 xyz 20 30 it
103 opq null
for emp in 100 .. 103
for dept in 10 .. 30
emp.deptno=dept.deptno
100 abc 10 10 sales
101 def 10 10 sales
102 xyz 20 20 market
订单表:
CustID StoreID ProdID ChannelID
100 S100 P100 C100
客户表：
CustID name creditlevel
100 abc
地址表：
CustID adress
100 bj
100 tj
获取如下信息，准备工作：
employees:
员工总数：107
SQL> select count(*) from employees;
有部门的员工数：106
SQL> select count(*) from employees where department_id is not null;
SQL> select count(department_id) from employees;
没有部门的员工数：1
SQL> select count(*) from employees where department_id is null;
departments：
部门总数：27
SQL> select count(*) from departments;
有员工的部门数：11
SQL> select count(distinct department_id) from employees;
没有员工的部门数：16
SQL> select count(*) from departments where department_id not in (select department_id from employees where department_id is not null);
for dept in 1..27
for emp in 1..107
dept.deptid不在emp表中出现
select count(*)
from employees e, departments d
where e.department_id(+)=d.department_id
and e.employee_id is null;
select count(*)
from departments d
where not exists
(select 1 from employees where department_id=d.department_id);
select (select count(*) from departments)-(select count(distinct department_id) from employees) from dual;
内连接：106(106, 11)
select e.last_name, d.department_name
from employees e, departments d
where e.department_id=d.department_id;
select e.last_name, d.department_name
from employees e join departments d on e.department_id=d.department_id;
左外连接：107（106+1）
select e.last_name, d.department_name
from employees e, departments d
where e.department_id=d.department_id(+);
select e.last_name, d.department_name
from departments d, employees e
where e.department_id=d.department_id(+);
select e.last_name, d.department_name
from employees e left outer join departments d
on e.department_id=d.department_id;
右外连接：122（106+16）
select e.last_name, d.department_name
from employees e, departments d
where e.department_id(+)=d.department_id;
select e.last_name, d.department_name
from employees e right outer join departments d
on e.department_id=d.department_id;
完全外连接：123（106+1+16）
select e.last_name, d.department_name
from employees e full outer join departments d
on e.department_id=d.department_id;
多表连接的扩展：
n张表连接：
select e.last_name, d.department_name, l.city
from employees e, departments d, locations l
where e.department_id=d.department_id
and d.location_id=l.location_id;
select e.last_name, d.department_name, l.city
from employees e join departments d on e.department_id=d.department_id
join locations l on d.location_id=l.location_id;
select e.last_name, d.department_name, l.city
from employees e, departments d, locations l
where e.department_id=d.department_id(+)
and d.location_id=l.location_id(+);
select e.last_name, d.department_name, l.city
from employees e left outer join departments d on e.department_id=d.department_id
left outer join locations l on d.location_id=l.location_id;
练习：
查询所有员工姓名，部门名称，部门所属城市(city)，国家(country)和区域(region)名称，对于空值用"无"代替。(N/A)
（使用oracle和sql99的语法）
select e.last_name, d.department_name, l.city, c.country_name, r.region_name
from employees e, departments d, locations l, countries c, regions r
where e.department_id=d.department_id(+)
and d.location_id=l.location_id(+)
and l.country_id=c.country_id(+)
and c.region_id=r.region_id(+);
select e.last_namee.last_name, d.department_name, l.city, c.country_name, r.region_name
from employees e
left outer join departments d on e.department_id=d.department_id
left outer join locations l on d.location_id=l.location_id
left outer join countries c on l.country_id=c.country_id
left outer join regions r on c.region_id=r.region_id;
自连接：
empid ename mgrid
100 abc
101 def 100
102 xyz 100
emp: mgr:
empid ename mgrid empid mgrname
100 abc 100 abc
101 def 100
102 xyz 100
101 def 100 100 abc
102 xyz 100 100 abc
select emp.ename, mgr.mgrname
from emp, mgr
where emp.mgrid=mgr.empid
emp: mgr:
empid ename mgrid empid ename mgrid
100 abc 100 abc
101 def 100 101 def 100
102 xyz 100 102 xyz 100
select e.last_name, m.last_name
from employees e, employees m
where e.manager_id=m.employee_id;
有经理的员工数：106
SQL> select count(*) from employees where manager_id is not null;
没有经理的员工数：1
SQL> select count(*) from employees where manager_id is null;
练习：
显示所有员工姓名和经理姓名，没有经理的显示"无"。
select e.last_name, nvl(m.last_name, 'N/A')
from employees e, employees m
where e.manager_id=m.employee_id(+);
不等值连接：
conn scott/tiger
select e.ename, sg.grade
from emp e, salgrade sg
where e.sal between sg.losal and sg.hisal;
练习：
找出工资大于所在部门平均工资的员工姓名。
create table avg_sal_dept as select department_id, avg(salary) avg_sal from employees where department_id is not null group by department_id;
select e.last_name, e.salary, asd.avg_sal
from employees e, avg_sal_dept asd
where e.department_id=asd.department_id
and e.salary>asd.avg_sal;
select e.last_name, e.salary, asd.avg_sal
from employees e, (select department_id, avg(salary) avg_sal from employees where department_id is not null group by department_id) asd
where e.department_id=asd.department_id
and e.salary>asd.avg_sal;
子查询
单行子查询的思路：
SQL> select salary from employees where last_name='Feeney';
SQL> select last_name from employees where salary>3000;
SQL> select last_name from employees where salary>(select salary from employees where last_name='Feeney');
多行子查询的思路：
SQL> select distinct department_id from employees where department_id is not null;
SQL> select department_name from departments where department_id in (10, 20,30);
SQL> select department_name from departments where department_id in (select department_id from employees where department_id is not null);
用多表连接改写：
select distinct d.department_name
from employees e, departments d
where e.department_id=d.department_id
for dept in 1..27
for emp in 1..107
查看emp中是否出现deptid
练习：
工资大于全公司平均工资的员工姓名。
SQL> select last_name from employees where salary>(select avg(salary) from employees);
和Feeney同年入职的员工姓名
select last_name, hire_date
from employees
where extract(year from hire_date)=
(select extract(year from hire_date) from employees where last_name='Feeney')
and last_name != 'Feeney';
select last_name, hire_date
from employees
where hire_date between
(select to_date(to_char(hire_date, 'yyyy')||'0101', 'yyyymmdd') from employees where last_name='Feeney')
and
(select to_date(to_char(hire_date, 'yyyy')||'1231', 'yyyymmdd') from employees where last_name='Feeney')
在Seattle工作的所有员工姓名
select last_name
from employees
where department_id in
(select department_id from departments
where location_id=
(select location_id from locations where city='Seattle'));
查找符合下列条件的员工姓名：和Abel在同一个部门，工资比Olson高
select last_name from employees
where department_id=
(select department_id from employees where last_name='Abel')
and salary >
(select salary from employees where last_name='Olson');
配对子查询：
和Feeney在同一个部门、做同一职位的员工姓名：
select last_name, department_id, job_id
from employees
where department_id=
(select department_id from employees where last_name='Feeney')
and job_id=
(select job_id from employees where last_name='Feeney')
and last_name != 'Feeney';
select last_name, department_id, job_id
from employees
where (department_id, job_id)=
(select department_id, job_id from employees where last_name='Feeney')
and last_name != 'Feeney';
in和notin受null值的影响：
所有管理者的姓名：
SQL> select last_name from employees where employee_id in (select manager_id from employees);
所有普通员工的姓名：
SQL> select last_name from employees where employee_id not in (select manager_id from employees where manager_id is not null);
关联子查询：
工资大于所在部门平均工资的员工姓名。
for i in 1..107所有员工
{
select avg(salary) from employees where department_id=i.department_id
if i.salary > i所在部门的平均工资
保留此记录
}
select last_name，salary,department_id
from employees outer
where salary >
(select avg(salary) from employees
where department_id = outer.department_id)
order by department_id;
select e.last_name, e.salary, asd.avg_sal
from employees e, (select department_id, avg(salary) avg_sal from employees where department_id is not null group by department_id) asd
where e.department_id=asd.department_id
and e.salary>asd.avg_sal;
exists/not exists查询：
for i in 1..27所有部门
{
forjin1..107所有员工
{
if i.department_id = j.department_id
保留此记录
break
}
}
select department_name
from departments outer
where exists
(select 1 from employees where department_id=outer.department_id);
select department_name
from departments outer
where not exists
(select 1 from employees where department_id=outer.department_id);
练习：
所有管理者的姓名：
for i in 1..107所有员工
{
forjin1..107所有员工
{
if i.employee_id = j.manager_id
保留此记录
break
}
}
select last_name
from employees outer
where exists
(select 1 from employees where manager_id=outer.employee_id);
select last_name
from employees
where employee_id in
(select manager_id from employees);
select last_name
from employees,(select distinct(manager_id) from employees)asd
where employee_id=asd.manager_id
所有普通员工的姓名：
select last_name
from employees outer
where not exists
(select 1 from employees where manager_id=outer.employee_id);
select last_name
from employees
where employee_id not in(select manager_id from employees where manager_id is not null);
子查询和多表连接的转换：
有员工的部门的名称
select department_name
from departments
where department_id in
(select department_id from employees);
select department_name
from departments outer
where exists
(select 1 from employees where department_id=outer.department_id);
select distinct d.department_name
from employees e, departments d
where e.department_id=d.department_id;
练习：
在Seattle工作的所有员工姓名（使用子查询和多表连接两种方式）
select last_name
from employees
where department_id in
(select department_id from departments
where location_id=
(select location_id from locations where city='Seattle'));
select e.last_name
from employees e, departments d, locations l
where e.department_id=d.department_id
and d.location_id=l.location_id
and l.city='Seattle';
最大值查询：
SQL> select last_name from employees where salary=(select max(salary) from employees);
top-N查询：
SQL> select last_name, salary from employees where rownum<=3 order by salary desc;
SQL> select * from (select last_name, salary from employees order by salary desc) where rownum<=3;
分页查询：
SQL> select * from
(select * from
(select * from
(select last_name, salary from employees order by salary desc)
where rownum<=6)
order by salary)
where rownum<=3
order by salary desc;
SQL> select last_name, salary
from (select rownum row_num, v1.* from
(select last_name, salary from employees order by salary desc) v1
) v2
where row_num between 4 and 6;
select * from
(select rownum num,last_name,salary from
(select last_name,salary from employees order by salary desc))
where num between 4 and 6
select last_name, salary
from (select rownum row_num, v1.*
from
(select last_name, salary from employees order by salary desc) v1
where rownum<=6
) v2
where row_num >= 4;
集合操作
select employee_id, job_id from employees
union all
select employee_id , job_id from job_history;
select employee_id, job_id from employees
union
select employee_id, job_id from job_history;
select employee_id, job_id from employees
intersect
select employee_id, job_id from job_history;
select employee_id from employees
minus
select employee_id from job_history;
select employee_id, job_id, salary from employees
union all
select employee_id, job_id, null from job_history;
select employee_id, job_id, to_char(salary) from employees
union all
select employee_id, job_id, 'no salary' from job_history;
集合排序：
select employee_id, job_id, salary from employees
union all
select employee_id, job_id, null from job_history
orderbysalary;
select employee_id, job_id, null from job_history
union all
select employee_id, job_id, salary from employees
orderby 3;
DML
insert:
SQL> create table t1(x int, y char(1), z date);
SQL> insert into t1(x, y, z) values (1, 'a', sysdate);
SQL> insert into t1(x, z, y) values (2, sysdate+1, 'b');
SQL> insert into t1(x, y, z) values (1, null, sysdate);
SQL> insert into t1(x, z) values (2, sysdate+1);
SQL> insert into t1 values (1, null, sysdate);
SQL> create table my_emp as select * from employees;
SQL> create table my_emp as select last_name, salary from employees where department_id=50;
SQL> create table avg_sal as select department_id, avg(salary) avg_sal from employees group by department_id;
SQL> create table my_emp as select * from employees where 1=0;
SQL> insert into my_emp select * from employees;
update:
SQL> update my_emp set salary=salary*1.1;
SQL> update my_emp set salary=salary*1.1 where department_id=50;
SQL> update my_emp set salary=salary*1.1, commission_pct=0.5 where employee_id=197;
delete:
SQL> delete from my_emp where employee_id=197;
SQL> delete from my_emp where department_id=50;
SQL> delete from my_emp;
子查询：
SQL> create table my_emp as select * from employees;
SQL> alter table my_emp add(department_name varchar2(30));
SQL> update my_emp outer set department_name=(select department_name from departments where department_id=outer.department_id);
update (select t1.department_name as aname,t2.department_name bname from my_emp t1 ,departments t2 where t1.department_id=t2.department_id) set aname=bname;
练习：
在new_dept表中删除没有员工的部门
SQL> create table my_dept as select * from departments;
delete from my_dept outer
where not exists
(select 1 from my_emp
where department_id=outer.department_id);
delete和truncate：
delete truncate
语句类型 dml ddl
undo数据 产生大量undo数据 不产生undo数据
空间管理不释放 释放
语法 where 删除全部数据
DDL
字符串：
SQL> create table t1(x char(10), y varchar2(10));
SQL> insert into t1 values('x', 'y');
SQL> select dump(x), dump(y) from t1;
数值：
SQL> create table t1(x number(5,2), y number(5));
SQL> insert into t1 values (123.45, 12345);
SQL> insert into t1 values (12.345, 12345);
SQL> insert into t1 values (12.345, 123.45);
SQL> select * from t1;
SQL> insert into t1 values (12.345, 112345);
日期时间：
SQL> create table t1(a date, b timestamp, c timestamp with time zone, d timestamp with local time zone);
SQL> insert into t1 values (sysdate, systimestamp, systimestamp, systimestamp);
SQL> alter session set time_zone='+9:00';
SQL> select * from t1;
修改表结构：
SQL> alter table t1 add(e char(10));
SQL> alter table t1 drop(e);
SQL> alter table t1 modify(d not null);
约束条件：
字段（列）：not null, check(salary>0)
行与行：primary key, unique
表与表之间：foreign key
create table dept (
deptno int constraint dept_deptno_pk primary key,
dname varchar2(20) constraint dept_dname_nn not null);
create table emp (
empno int constraint emp_empno_pk primary key,
ename varchar2(20) constraint emp_ename_nn not null,
email varchar2(50) constraint emp_email_uq unique,
salary int constraint emp_salary_ck check(salary>0),
deptno int constraint emp_deptno_fk references dept(deptno))
SQL> select constraint_name, constraint_type from user_constraints where table_name in('DEPT', 'EMP');
SQL> insert into emp values (100, 'abc', 'abc@123.com', 10000, 10);
insert into emp values (100, 'abc', 'abc@123.com', 10000, 10)
*
ERROR at line 1:
ORA-02291: integrity constraint (HR.EMP_DEPTNO_FK) violated - parent key not
found
SQL> insert into dept values (10, 'sales');
1 row created.
SQL> insert into dept values (10, 'market');
insert into dept values (10, 'market')
*
ERROR at line 1:
ORA-00001: unique constraint (HR.DEPT_DEPTNO_PK) violated
SQL> insert into dept values (20, 'market');
1 row created.
SQL> commit;
Commit complete.
SQL> insert into emp values (101, 'def', 'def@123.com', 10000, 20);
create table emp (
empno int constraint emp_empno_pk primary key,
ename varchar2(20) constraint emp_ename_nn not null,
email varchar2(50) constraint emp_email_uq unique,
salary int constraint emp_salary_ck check(salary>0),
deptno int constraint emp_deptno_fk references dept(deptno) on delete set null)或者on delete cascade
instead of trigger视图触发器
序列：
SQL> create sequence test_seq increment by 1 start with 1 maxvalue 1000 nocycle cache 20;
SQL> create table t1(x int primary key, y int);
SQL> insert into t1 values (test_seq.nextval, 11); 反复执行
SQL> select * from t1;
索引：
主键和唯一性约束自动创建索引：
SQL> select constraint_name, constraint_type from user_constraints where table_name='EMPLOYEES';
SQL> select index_name, index_type from user_indexes where table_name='EMPLOYEES';
SQL> set autot on
SQL> select last_name from employees where employee_id=100; 走索引
SQL> select email from employees; 走索引
SQL> select last_name from employees where salary=2100; 全表扫描
SQL> create index emp_salary_ix on employees(salary);
SQL> select last_name from employees where salary=2100; 走索引
SQL> set autot off
PL/SQL
$ vi $ORACLE_HOME/sqlplus/admin/glogin.sql
set serveroutput on
游标
普通显式游标练习：
指定员工的工号
如果高于或等于所在部门的平均工资，输出first_name(列) last_name（列）'s salary: (显示员工的工资) higherorequal than avgrage salary of department department_name（列）: (显示部门工资).
如果低于所在部门的平均工资，输出first_name last_name's salary lowerer than avgrage salary of department department_name.
如果员工不属于任何部门，输出first_name last_name nodepartment！
DECLARE
v_empno employees.employee_id%type := 100;
v_emp_rec employees%rowtype;
v_avg_sal employees.salary%type;
v_department_name departments.department_name%type;
BEGIN
select *
into v_emp_rec
from employees
where employee_id=v_empno;
select avg(salary)
into v_avg_sal
from employees
where department_id=v_emp_rec.department_id;
select department_name
into v_department_name
from departments
where department_id=v_emp_rec.department_id;
if v_emp_rec.salary >= v_avg_sal
then
dbms_output.put_line(v_emp_rec.first_name||', '||v_emp_rec.last_name);
end if;
END;
/
对所有员工检查工资，输出和上面练习相同的内容。
BEGIN
for v_emp_rec in (select * from employees)
loop
if v_emp_rec.department_id is null then
dbms_output…
else
select avg(salary)
into v_avg_sal
from employees
where department_id=v_emp_rec.department_id;
select department_name
into v_department_name
from departments
where department_id=v_emp_rec.department_id;
if …
end loop;
END;
DECLARE
cursor emp_cur is
select e.last_name, e.salary, asd.avg_sal
from employees e, (select department_id, avg(salary) avg_sal from employees where department_id is not null group by department_id) asd
where e.department_id=asd.department_id;
BEGIN
for v_emp_rec in emp_cur
loop
if v_emp_rec.salary >= v_emp_rec.avg_sal then
elsif
end loop;
参数游标练习：
指定部门编号，显示部门中工资最高的前3名（不足3名的全部显示）。输出：部门编号姓名工资。
DECLARE
cursor emp_sal_cur(p_department_id number) is
select department_id, last_name, salary
from employees
where department_id=p_department_id
order by salary desc;
BEGIN
for v_emp_rec in emp_sal_cur(10)
loop
exit with emp_sal_cur%rowcount>3;
dbms_output…
end loop;
END;
DECLARE
cursor emp_sal_cur(p_department_id number) is
select * from (select department_id, last_name, salary
from employees
where department_id=p_department_id
order by salary desc) where rownum<=3;
BEGIN
for v_emp_rec in emp_sal_cur(10)
loop
dbms_output…
end loop;
END;
对所有部门，做同样的工作。
DECLARE
cursor emp_sal_cur(p_department_id number) is
select department_id, last_name, salary
from employees
where department_id=p_department_id
order by salary desc;
BEGIN
for v_dept_rec in (select distinct department_id from employees where department_id is not null)
loop
for v_emp_rec in emp_sal_cur(v_dept_rec.department_id)
loop
exit with emp_sal_cur%rowcount>3;
dbms_output…
end loop;
end loop;
END;
DECLARE
cursor emp_sal_cur(p_department_id number) is
select * from (select department_id, last_name, salary
from employees
where department_id=p_department_id
order by salary desc) where rownum<=3;
BEGIN
for v_dept_rec in (select distinct department_id from employees where department_id is not null)
loop
for v_emp_rec in emp_sal_cur(v_dept_rec.department_id)
loop
dbms_output…
end loop;
end loop;
END;
练习
指定员工工号，修改员工的email地址，如果是新员工，将员工信息插入到new_emp表中。
SQL> create table new_emp as select * from employees;
DECLARE
v_employee_id …
v_email …
BEGIN
update new_emp set email=v_email
where first_name=v_first_name and last_name=…;
if SQL%notfound then
insert into new_emp values ();
end if;
END;
异常
预定义异常练习：
输入员工的工号，返回员工姓名和工资。对于不准确的工号，给出提示信息"该工号不存在"。
BEGIN
select first_name, last_name, salary
into v_first_name, v_last_name, v_salary
from employees
where employee_id=&emp_id;
dbms_output.put_line(…);
EXCEPTION
WHEN no_data_found THEN
dbms_output…
WHENothers THEN
dbms_output…
END;
非预定义异常练习：
向new_emp中添加新员工信息，针对工号重复、姓名缺失、工资<0，都要给出错误提示信息。
SQL> create table new_emp as select * from employees;
SQL> alter table new_emp modify(employee_id primary key);
SQL> alter table new_emp modify(check(salary>0));
SQL> alter table new_emp modify(email null);
SQL> alter table new_emp modify(hire_date null);
SQL> alter table new_emp modify(job_id null);
insert into new_emp…
declare
e_name exception;
e_salary exception;
pragma exception_init(e_name,-2292);
begin
insert into new_emp values ();
exception
when DUP_VAL_ON_INDEX then
dbms_output…
when e_name then
dbms_output…
when e_salary then
dbms_output…
when others then
….
end;
自定义异常练习：
对new_emp表中的员工修改，指定工号，修改员工的工资。对于工号不存在、修改后的工资<0，都要给出错误信息。
过程
练习：
输入员工的工号，返回员工姓名和工资。对于不准确的工号，给出提示信息"该工号不存在"。
（在过程中用dbms_output输出结果。使用out类型参数，在过程外输出结果）
create procedure get_emp_info(1,2,3,4) is
begin
end;
declare
id, name, sal
begin
get_emp_info(1,2,3,4)
dbms_output.234
end;
函数
练习：
输入工号，显示员工姓名的全称。
select full_name(employee_id) from employees;
abc, def
包
将员工管理相关的过程和函数，放入包emp_pack中。
过程包括：查询、添加、修改员工。函数包括：显示员工全名。
oracle管理
体系结构
SGA:
$ sqlplus / as sysdba
SQL> show sga
SQL> show parameter sga_max_size
SQL> select * from V$SGAINFO;
oem：浏览器中服务器-->内存指导（https://192.168.0.1:1158/em）
sharedpool:
SQL> show parameter shared_pool_size
SQL> select component, current_size from V$SGA_DYNAMIC_COMPONENTS where component='shared pool';
SQL> select * from v$SGAINFO;
db buffer cache:
SQL> show parameter db_block_size
SQL> show parameter db_cache_size
SQL> select * from v$SGAINFO;
SQL> select component, current_size from V$SGA_DYNAMIC_COMPONENTS where component='DEFAULT buffer cache';
redo log buffer:
SQL> show parameter log_buffer
SQL> select * from v$sgainfo;
PGA:
SQL> show parameter pga_aggregate_target
SQL> select * from V$PGASTAT where NAME='total PGA allocated';
后台进程：
SQL> select name from v$bgprocess where paddr<>'00';
SQL> ! ps -ef | grep ora_
SQL> show parameter db_writer_processes
数据文件：
$ ll /u01/app/oracle/oradata/orcl/*.dbf
SQL> select name from v$datafile;
SQL> select name from v$tempfile;
控制文件：
$ find /u01 -name 'control0[12].ctl'
SQL> select name from v$controlfile;
SQL> select TYPE, RECORD_SIZE, RECORDS_TOTAL, RECORDS_USED from v$controlfile_record_section;
重做日志：
$ ll /u01/app/oracle/oradata/orcl/*.log
SQL> select group#, member from v$logfile;
参数文件：
$ ll $ORACLE_HOME/dbs/spfile*.ora
$ strings $ORACLE_HOME/dbs/spfileorcl.ora
SQL> show parameter spfile;
SQL> show parameter
密码文件：
$ ll $ORACLE_HOME/dbs/orapw*
归档日志：
SQL> select name from v$archived_log;
警告日志：
$ find /u01 -name 'alert_*.log'
实例管理
监听
启动监听：
$ netstat -tlnp | grep 1521
$ lsnrctl
LSNRCTL> help
$ lsnrctl status
$ lsnrctl stop
$ netstat -tln | grep 1521
快速动态注册:(可选)
$ sqlplus / as sysdba
SQL> alter system register;
$ lsnrctl status
em
启动EM：
$ netstat -tlnp | grep 1158
$ echo $ORACLE_SID
orcl
$ echo $ORACLE_UNQNAME
orcl
如果没有设置ORACLE_UNQNAME：
$ export ORACLE_UNQNAME=orcl
或
$ vi .bash_profile
export ORACLE_UNQNAME=orcl
$ . .bash_profile
确保主机名正常解析：
$ cat /etc/hosts
$ emctl status dbconsole
$ emctl stop dbconsole
$ emctl start dbconsole
查看oem的端口：
$ find /u01 -name portlist.ini
重新配置em：
$ emca
$ emca -deconfig dbcontrol db
$ emca -config dbcontrol db
数据库
启动实例：
$ ls $ORACLE_HOME/dbs/spfileorcl.ora
SQL> shutdown immediate
SQL>startup nomount
SQL> select status from v$instance;
SQL> select * from v$sgainfo;
SQL>select * from hr.employees;
SQL> conn hr/hr
SQL>startup mount或alter database mount;
SQL> show parameter control_files
SQL> select status from v$instance;
SQL> select * from v$controlfile;
SQL> select name from v$datafile;
SQL>select * from hr.employees;
SQL> conn hr/hr
SQL>startup或alter database open;
SQL> select status from v$instance;
SQL>select * from hr.employees;
SQL> conn hr/hr
关闭数据库：
SQL> shutdown normal
SQL> shutdown transactional
SQL> shutdown immediate
SQL> shutdown abort
控制脚本：
$ vi /etc/oratab
$ vi $ORACLE_HOME/bin/dbstart
$ vi $ORACLE_HOME/bin/dbshut
重启数据库：
SQL> startup force
startup force = shutdown abort + startup
startup force mount = shutdown abort + startup mount
练习：
熟悉3个服务的启动和关闭
参数管理
参数文件：
$ ls $ORACLE_HOME/dbs/spfileorcl.ora
预先备份：
$ cp spfileorcl.ora spfileorcl.ora.bak
不可以通过vi直接修改！
查看参数：
SQL> show parameter
SQL> select NAME, VALUE from v$parameter;
修改参数：
动态参数：
$ strings $ORACLE_HOME/dbs/spfileorcl.ora|grep reource_limit
SQL> show parameter RESOURCE_LIMIT
SQL> alter system set RESOURCE_LIMIT=true;
SQL> show parameter RESOURCE_LIMIT
$ strings $ORACLE_HOME/dbs/spfileorcl.ora|grep reource_limit
静态参数：
$ strings $ORACLE_HOME/dbs/spfileorcl.ora|grep processes
*.processes=150
SQL> show parameter processes 值为150
SQL> alter system set processes=300 scope=spfile;
SQL> show parameter processes 值为150
$ cat $ORACLE_HOME/dbs/spfileorcl.ora
*.processes=300
重启数据库，静态参数修改生效：
SQL> shutdown immediate
SQL> startup
SQL> show parameter processes 值为300
session级别修改参数：
SQL> select sysdate from dual;
SQL> alter session set nls_date_format='dd-mon rr';
SQL>select sysdate from dual; 只影响当前会话，不影响系统
SQL> conn / as sysdba
SQL>select sysdate from dual; 恢复默认设置
pfile/spfile格式转换：
SQL> create pfile='/home/oracle/initorcl.ora' from spfile;
$ cat /home/oracle/initorcl.ora
$ mv $ORACLE_HOME/dbs/spfileorcl.ora $ORACLE_HOME/dbs/spfileorcl.ora.bak
SQL> shutdown immediate
SQL> create spfile from pfile='/home/oracle/initorcl.ora';
$ strings $ORACLE_HOME/dbs/spfileorcl.ora
指定参数文件启动数据库：
SQL> startup pfile='/home/oracle/initorcl.ora'
练习：
修改resource_limit和processes参数。并在alertlog中找到对应的信息。
警告日志和后台进程的转储文件
$ find /u01 -name alert_*.log
$ vi /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log
SQL> show parameter dump
练习：
查看alertlog中最近一次启动/关闭实例的详细信息。
查看alertlog中记录的用户对参数所做的修改。
数据字典/动态性能视图
SQL> conn hr/hr
SQL> desc user_tables
SQL> select TABLE_NAME from user_tables;
SQL> desc user_views
SQL> select VIEW_NAME from user_views;
SQL> desc user_indexes
SQL> select INDEX_NAME, TABLE_NAME from user_indexes;
SQL> conn scott/tiger
SQL> select TABLE_NAME from user_tables;
SQL> select VIEW_NAME from user_views;
SQL> select INDEX_NAME, TABLE_NAME from user_indexes;
SQL> conn hr/hr
SQL> select count(*) from all_tables;
SQL> conn scott/tiger
SQL> select count(*) from all_tables;
SQL> conn hr/hr
SQL> select count(*) from dba_tables;
SQL> conn scott/tiger
SQL> select count(*) from dba_tables;
SQL> desc v$instance
SQL> desc v$database
存储管理
查询预定义表空间：
SQL> select TABLESPACE_NAME,CONTENTS from dba_tablespaces;
SQL> select FILE_NAME, TABLESPACE_NAME from dba_data_files;
创建新表空间：
SQL> create tablespace tbs01 datafile '/u01/app/oracle/oradata/orcl/tbs01.dbf'SIZE 10MAUTOEXTEND ON NEXT 10M MAXSIZE 100M;
在指定的表空间中创建表：
SQL> create table t1 tablespace tbs01 as select * from dba_objects where 1=0;
SQL> select bytes, blocks, extents, tablespace_name from dba_segments where segment_name='T1';
SQL> insert into t1 select * from dba_objects;
SQL> select bytes, blocks, extents, tablespace_name from dba_segments where segment_name='T1';
SQL> select extent_id, bytes, blocks from dba_extents where segment_name='T1';
SQL> insert into t1 select * from t1;
SQL> insert into t1 select * from t1;
SQL> insert into t1 select * from t1;
SQL> select bytes, blocks, extents, tablespace_name from dba_segments where segment_name='T1';
SQL>select extent_id, bytes, blocks from dba_extents where segment_name='T1';
SQL> insert into t1 select * from t1; 空间不足，报错
SQL> rollback;
SQL> select bytes, blocks, extents, tablespace_name from dba_segments where segment_name='T1'; 空间不释放
SQL> alter table t1 move; 释放空间
只读表空间：
SQL> alter tablespace tbs01 read only;
SQL> delete t1; 禁止dml
SQL> insert into t1 select * from t1; 禁止dml
SQL> create table t2 (x int) tablespace tbs01; 失败
SQL> alter table t1 add (x int); 成功
SQL> update t1 set x=1; 失败
SQL> drop table t1; 成功
dml和ddl的区别
改变表空间大小：
resize，autoextend，adddatafile
删除表空间：
SQL> drop tablespace tbs01 including contents and datafile;
Oracle NET
1.客户端通过@ora10g的名字去tnsname.ora文件获取服务器的具体连接信息
2.客户端通过tnsname.ora中的描述向服务器发出链接请求服务器端
3.服务器的监听器接收到连接请求后,验证请求的服务的有效性
4.服务器端产生一个服务进程和客户端进程建立连接
查看会话建立过程：
$ netstat -tlnp | grep 1521
$ sqlplus sys/password@orcl as sysdba
$ netstat -tnp | grep sqlplus
$ kill -9 1234 杀死维护sqlplus的进程
监听的配置
配置文件：
$vi$ORACLE_HOME/network/admin/listener.ora
通过netca添加新的监听服务Listener15210，端口使用15210
$ vi listener.ora
$ netstat -tln|grep 1521
$ lsnrctl status listener15210
通过netmgr配置高级选项
通过lsnrctl命令来启动/停止/查看/重载监听器/服务
lsnrctl start|stop|status|reload|service
指定监听的名称：
$ lsnrctl status listener15210
网络环境变化，需要检查listener.ora和/etc/hosts文件
netca删除Listener15210
实例的配置
静态注册和动态注册
什么是静态注册
就是监听器的配置文件中写明了监听哪个实例需要配置SID_DESC字段
定位实例的方式可以使用SID_NAME或者SERVICE_NAME来定位
什么是动态注册
就是监听器的配置文件中没写明监听哪个实例
要通过PMON告知监听器要监听的具体实例
PMON是将SERVER_NAME告诉给监听器这个过程就是注册
默认一分钟PMON注册一次也就是说启动监听还没注册时是无法连接的
添加3种方式，后面的删掉，用文档改写
区分静态注册和动态注册
lsnrctl status
是 ready 就是动态
是 unknow 就是静态
静态注册listener.ora文件信息:
$ vi listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME =ora11g)
)
)
$ lsnrctl reload
.....
Services Summary...
Service "ora11g" has 1 instance(s).
Instance "ora11g", status UNKNOWN, has 1 handler(s) for this service...
状态总是显示未知的,当有请求时,监听器才去确认数据是否存在
动态注册
Oracle9i起实例使用动态服务注册来通知监听程序有关其数据库服务的信息。
服务注册依赖PMON 进程向监听程序注册实例信息注册间隔为1分钟左右
手动注册命令 alter system register;
无需在listener.ora 文件中设置任何信息此文件可以不存在
3种注册方式：
本地默认端口监听
本地非默认端口监听
远程监听
本地非默认端口：
netca创建listener15210，使用15210端口
$ netstat -tlnp | grep 15210
写入监听的别名：
$ cd $ORACLE_HOME/network/admin
$ vi tnsnames.ora
listener15210 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1.test.com)(PORT = 15210))
)
修改参数：
SQL> ALTER SYSTEM SET LOCAL_LISTENER=listener15210;
SQL> ALTER SYSTEM register; 立即注册（可选）
服务信息已添加到新的监听中：
$ lsnrctl status listener15210
客户端指定新端口连接：
$ sqlplus sys/password@192.168.0.1:1521/orcl as sysdba 报错
$ sqlplus sys/password@192.168.0.1:15210/orclas sysdba
删除配置：
SQL> ALTER SYSTEM SET LOCAL_LISTENER='';
SQL> ALTER SYSTEM register;
$ vi tnsnames.ora 删除listener15210别名
netca删除15210端口的监听
客户端配置
轻松连接：
$ sqlplus sys/password@192.168.0.1:1521/orcl as sysdba
适用于临时性的连接
本地命名：
查看现有的主机连接字符串
$ cd $ORACLE_HOME/network/admin
$ vi tnsnames.ora
$ sqlplus sys/password@orcl as sysdba
使用netca添加新的主机连接字符串orcl192
$ sqlplus sys/password@orcl192 as sysdba
SQL> select name from v$database;
tnsping测试，不需要用户名和口令：
$ tnsping 192.168.0.1:1521/orcl
$ tnsping orcl192
解析方法的顺序：
$ vi sqlnet.ora
netca可以修改
恢复默认设置：
SQL> alter system set local_listener='';
SQL> alter sytem register;
netca删除不需要的监听和连接配置
共享服务器模式
专有服务和共享服务模式
专有模式
每个用户进程和服务进程间通过监听器建立连接
进程信息存放于PGA中,也就是说有多少个用户进程就有多少个PGA产生
单单只有进程连接还不足以操作数据库,还需要产生的会话信息
会话信息存储在UGA中,UGA在专有模式中存在于PGA
因为进程和进程间是相互隔离的,所以会话信息也相对独立
这就导致了服务进程只能获知当前用户进程的会话请求信息只能为当前用户进程服务
共享模式
用户进程的请求被监听器接收,监听器不委派服务器进程,而是将调度器信息返回给客户端
调度器将用户进程的请求放入请求队列
多个服务进程中的一个服务进程从队列中获取用户进程的请求,并处理这个用户进程的请求
服务进程处理完后将处理结果放入响应队列,每个调度器都有自己的响应队列
响应队列的信息反馈给对应的调度器
调度器再把服务进程处理的结果返回给用户进程
共享模式中的用户进程的会话信息对每一个服务器进程来说都是可见的.
因为共享模式的UGA信息存在于SGA中,所以此时一个用户进程的请求可以由多个服务进程来完成.
共享服务器的配置通过初始化参数dispatchers来配置
可以通过DBCA来修改
$ lsnrctl service
SQL> show parameter disp
SQL> show parameter shared_server
专有模式和共享模式是可以兼容在一起使用的
此时看用户如何选择
tnsnames.ora中
ORA10G =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dba.up.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED) # 不写这个值就是按服务器的模式来匹配
# shared 指定使用共享方式连接
# DEDICATED 指定使用专有模式连接
(SERVICE_NAME = raw10g)
)
)
测试三种模式
ORCL_default =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1.test.com)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
ORCL_dedicated =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1.test.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
ORCL_shared =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1.test.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = orcl)
)
)
测试：
$ sqlplus sys/password@orcl_default as sysdba
$ sqlplus sys/password@orcl_dedicated as sysdba
$ sqlplus sys/password@orcl_shared as sysdba
SQL> select SID, SERVER,PROGRAM from v$session where USERNAME='SYS';
SQL> select distinct sid from v$mystat;
管理维护、大数据量导入、备份恢复等工作不适合用共享方式：
SQL> shutdown immediate 共享连接下不能发布管理命令
恢复原有设置：
dbca修改为dedicated方式
dblink
数据库A访问远程数据库B上面的表：
在数据库A中，使用netca创建主机描述字符串orcl_dblink指向数据库B
$ netca
$ tnsping orcl_dblink
在数据库A中创建数据库链接：
SQL> create database link orcl_dblink_hr connect to hr identified by hr using 'orcl_dblink';
using使用主机描述字符串orcl_dblink
用户和口令都是数据库B上的
db link的名字不要求和主机描述字符串一致
访问数据库B中的表：
SQL> select count(*) from hr.employees@orcl_dblink_hr;
SQL> select count(*) from hr.employees, departments@orcl_dblink_hr;
用户管理
AAA:
Authentication: 身份验证
Authorization: 权限管理
Audition: 审计
authentication
预定义的系统用户：
SQL> select USERNAME, ACCOUNT_STATUS from dba_users;
open状态的用户：
SQL> select USERNAME, ACCOUNT_STATUS from dba_users ACCOUNT_STATUS='OPEN';
系统管理账号:
SYS SYSTEM DBSNMP SYSMAN
3种身份验证方式：
password验证：
浏览器中创建用户user01
或者用命令创建：
SQL> create user user01 identified by password;
SQL> grant create session to user01;
测试：
$ sqlplus user01/password
external（os）验证：
操作系统中创建用户：
$ su -
Password:
[root@node1 ~]# useradd osuser
[root@node1 ~]# passwd osuser
$ sqlplus / as sysdba
外部用户使用固定的前缀：
SQL> show parameter os_auth
SQL> create user ops$osuser identified externally;
SQL> grant create session to ops$osuser;
不要su - osuser，环境变量保留：
$ su osuser
Password:
[osuser@node1 admin]$ sqlplus /
SQL> show user
USER is "OPS$OSUSER"
管理员的身份验证：
本地连接：
本地连接，预先设置ORACLE_SID，操作系统用户是dba群组的成员
$ id
uid=1001(oracle) gid=1000(oinstall) groups=1000(oinstall),1031(dba),1032(oper)
$ sqlplus / as sysdba
SQL> show user
USER is "SYS"
$ su -
# usermod -G oper oracle或
#gpasswd -d oracle dba
# exit
$ sqlplus / as sysdba
报错，权限不够
只要是dba群组中的成员，就可以不需要知道sys的口令，直接以sqlplus / as sysdba登录
并且身份为sys。
恢复：
# gpasswd -a oracle dba
远程客户端连接：
$ sqlplus sys/password@orcl as sysdba
$ ls $ORACLE_HOME/dbs/orapworcl
$ orapwd
authorization
系统权限：
sys执行授权：
预先创建测试表
SQL> create table t1(x int);
SQL> create user user01 identified by password;
SQL> grant create session to user01;
SQL> grant select any table to user01;
user01测试：
$ sqlplus user01/password
SQL> select count(*) from hr.employees(hr.departments scott.emp);
SQL> delete from scott.emp; 失败！
SQL> select * from sys.t1; 失败！
select any table n-1模式
sys再次授权：
SQL> grant select any dictionary to user01;
user01测试：
SQL> select * from sys.t1; 成功
select any table(n-1)+select any dictionary(1)
sys授权：
SQL> grant create table to user01;
user01测试：
SQL> create table t1(x int);
sys授权：
SQL> grant unlimited tablespace to user01;
user01测试：
SQL> insert into t1 values (1);
对象权限：
表的参照权限：
dept
deptno(pk) dname
10 sales
20 market
my_emp
empno deptno(fk)
100 10
sys授权：
SQL> grant select on hr.employees to user01;
user01测试：
SQL> select count(*) from hr.employees;
SQL> delete from hr.employees; 失败
SQL> select count(*) from hr.departments; 失败
sys授权：
SQL> grant index on hr.employees to user01;
SQL> grant unlimited tablespace to user01;
user01测试：
SQL> create index emp_sal_idx on hr.employees(salary);
SQL> select index_name from user_indexes where table_name='EMPLOYEES';
create any table create table
alter any table alter table
drop any table drop table
权限的级联删除：
系统权限：
sys准备工作：
SQL> drop user user01 cascade;
SQL> drop user user02 cascade;
SQL> create user user01 identified by password;
SQL> create user user02 identified by password;
SQL> grant create session to user01;
SQL> grant create session to user02;
sys授权：
SQL> grant select any table to user01 with admin option;
user01测试成功并授权给user02:
SQL> select count(*) from hr.employees;
SQL> grant select any table to user02 with admin option;
user02测试成功：
SQL> select count(*) from hr.employees;
sys收回权限：
SQL> revoke select any table from user01;
user01操作失败：
SQL> select count(*) from hr.employees;
user02测试成功：
SQL> select count(*) from hr.employees;
对象权限：
SQL> grant select on hr.employees to user01 with grant option;
dba+sysdba=sys
role
角色就是数据库中的群组！
角色的作用：简化权限的管理，动态更新用户的权限。
预定义的角色：
SQL> select role from dba_roles;
创建角色：
SQL> create role hr_mgr;
SQL> create role hr_clerk;
SQL> grant select any table to hr_mgr;
SQL> grant select on hr.employees to hr_clerk;
SQL> grant hr_mgr to user01;
SQL> grant hr_clerk to user02;
user01/user02测试：
角色生效必须重新登录
profile
profile主要控制两个方面：
1 用户的资源消耗
2 用户的口令安全
SQL> select * from dba_profiles where profile='DEFAULT';
SQL> select username, profile from dba_users;
SQL> show parameter resource_limit 资源管理的开关参数
查看复杂性函数的脚本：
$ cd $ORACLE_HOME/rdbms/admin
$ vi utlpwdmg.sql
$ cp utlpwdmg.sql /home/oracle/utlpwdmg.sql
$ vi /home/oracle/utlpwdmg.sql 只保留校验函数部分
$ sqlplus / as sysdba
SQL> @/home/oracle/utlpwdmg.sql
sys创建概要文件：
SQL> CREATE PROFILE HR_PROFILE LIMIT
PASSWORD_LIFE_TIME 30
PASSWORD_GRACE_TIME 7
PASSWORD_REUSE_MAX 3
PASSWORD_REUSE_TIME unlimited
PASSWORD_LOCK_TIME 5/1440
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_VERIFY_FUNCTION verify_function_11G;
和用户关联：
SQL> ALTER USER HR PROFILE HR_PROFILE;
测试：
$ sqlplus hr/hr
SQL> alter user hr identified by password123 replace hr;
audit
开启开关参数：
SQL> show parameter audit_trail
设置审计选项：
每次设置新的审计选项，测试用户需要重新连接
sys准备工作：
SQL> drop user user01 cascade;
SQL> create user user01 identified by password;
SQL> grant create session, create table, create any table to user01;
审计系统权限：
SQL>AUDIT CREATE ANY TABLE, CREATE TABLE BY USER01 BY ACCESS;
user01测试：
SQL> create table t1(x int);
SQL> create table t1(x int); 失败
SQL> create table hr.t1(x int);
SQL> create table hr.t1(x int); 失败
sys查看审计结果：
SQL> desc aud$
SQL> desc dba_audit_trail
浏览器中查看
sys添加审计条件：
SQL> AUDIT SELECT ANY TABLE BY user01 BY ACCESS;
SQL> grant select any table to user01;
user01测试：
SQL> select * from t1;
SQL> select * from hr.t1;
sys查看审计结果：
浏览器中或者查看dba_audit_trail表
删除审计选项：
SQL> NOAUDIT CREATE ANY TABLE BY USER01;
SQL> NOAUDIT CREATE TABLE BY USER01;
SQL> NOAUDIT SELECT ANY TABLE BY user01;
审计对象：
sys设置审计选项：
SQL> AUDIT SELECT ON hr.employees BY ACCESS;
SQL> drop user user01 cascade;
SQL> create user user01 identified by password;
SQL> grant create session to user01;
sys授权，每执行一个语句，user01就测试一次：
SQL> grant select any table to user01;
SQL> revoke select any table from user01;
SQL> grant select on hr.employees to user01;
user01测试（执行4次）：
SQL> select count(*) from hr.employees;
默认不记录sys的行为：
SQL> select count(*) from hr.employees;
删除审计选项：
SQL> NOAUDIT SELECT ON hr.employees;
审计语句：
sys设置审计选项：
SQL> AUDIT TABLE BY user01 BY ACCESS;
user01测试：
SQL> create table t1(x int); 失败
SQL> create table t1(x int);
SQL> create table t1(y int); 失败
SQL> drop table t1;
sys查看结果：
浏览器中，或DBA_AUDIT_OBJECT表中
删除审计选项：
SQL>NOAUDIT TABLE BY USER01
审计sys的操作：
SQL> show parameter audit
修改两个参数
并发管理
读写冲突通过读一致性解决：
sys准备工作：
SQL> create user user01 identified by password;
SQL> grant dba to user01;
以下都用user01：
SQL> conn user01/password
Connected.
SQL> create table t1(x int);
SQL> insert into t1 values (1);
SQL> commit;
session1：
SQL> update t1 set x=11 where x=1;
SQL> select * from t1;
session 2:
SQL> select * from t1;
session 1：
SQL> commit;
session 2：
SQL> select * from t1;
测试serializable：
session1：
SQL> alter session set isolation_level=serializable;
重复上面的步骤
写与写的冲突通过锁机制解决：
session 1:
SQL> update t1 set x=11 where x=1;
浏览器中查看锁信息
session 2:
SQL> update t1 set x=111 where x=1; 被阻塞
浏览器中查看锁信息
session 1:
SQL>rollback;
浏览器中查看锁信息
死锁：
session1:
SQL> select * from t1;
X
----------
1
2
SQL> update t1 set x=11 where x=1;
session2:
SQL> update t1 set x=22 where x=2;
session1:
SQL> update t1 set x=222 where x=2; 阻塞
session2:
SQL> update t1 set x=111 where x=1; 死锁
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
$ vi /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log
锁和外键
select … for update
撤销管理
什么是撤销数据：
1.交易的回退:没有提交的交易可以rollback
2.交易的恢复:数据库崩溃时,将磁盘的不正确数据恢复到交易前
3.读一致性 :被查询的记录有事务占用,转向回滚段找改前镜像
4.闪回数据 :从回滚段中构造历史数据
事务与撤销数据：
redo和undo：
AUM：
3个参数，两个表空间属性
undo_management=AUTO 回滚表空间段的段管理模式，管理员只需要备足够的表空间容量，oracle会自动管理扩展回滚段的数量。只能使用一个UNDO表空间。
undo_tablespace:只有在自动管理模式下才可以使用。指明使用哪个UNDO表空间
undo_retention＝900 ：
提交之后旧的镜像保持在回滚段中的时间。
非强制的回退保持时间.(回滚空间不足老的镜像就会被覆盖)
autoextend：表空间自动扩展
强制保持：但是对空间要求较大，要慎用。(10g开始支持)
alter tablespace UNDOTABS1 RETENTION GUARANTEE;
select tablespace_name,RETENTION from dba_tablespaces;
UNDO_RETENTION specifies (in seconds) how long already committed undo information is to be retained. The only time you must set this parameter is when:
•The undo tablespace has the AUTOEXTEND option enabled
•You want to set undo retention for LOBs
•You want to guarantee retention
undo advisor：
调度作业
$ ps -ef | grep cjq
SQL> show parameter job_queue_processes
后台预先设置的自动化管理作业：
自定义作业：
SQL> create table session_history(snap_time timestamp with local time zone, num_session number);
em中创建作业：
使用plsql块：
declare
session_count number;
begin
select count(*) into session_count from v$session;
insert into session_history values (systimestamp, session_count);
commit;
end;
全球化支持
$ vi .bash_profile
#export NLS_LANG=american_america.AL32UTF8
#export NLS_DATE_FORMAT='yyyy-mm-dd hh34:mi:ss'
$ unset NLS_LANG
$ unset NLS_DATE_FORMAT
SQL> select sysdate from dual;
备份恢复
配置可恢复性
控制文件
SQL> show parameter control_files
SQL> select * from v$controlfile;
修改路径：
$ cd $ORACLE_HOME/dbs
$ cp spfileorcl.ora spfileorcl.ora.bak
SQL> alter system set control_files='/u01/app/oracle/oradata/orcl/control01.ctl', '/home/oracle/control02.ctl' scope=spfile;
SQL> shutdown immediate
$ mv /u01/app/oracle/fast_recovery_area/orcl/control02.ctl /home/oracle/control02.ctl
SQL> startup
SQL> show parameter control_files
SQL> select * from v$controlfile;
增加镜像：
SQL> alter system set control_files='/u01/app/oracle/oradata/orcl/control01.ctl', '/home/oracle/control02.ctl', '/home/oracle/control03.ctl' scope=spfile;
SQL> shutdown immediate
$ cp /home/oracle/control02.ctl /home/oracle/control03.ctl
SQL> startup
SQL> show parameter control_files
SQL> select * from v$controlfile;
日志文件：
增加成员和日志组：
SQL> select GROUP#, SEQUENCE#, STATUS, MEMBERS from v$log;
SQL> select GROUP#, MEMBER from v$logfile;
SQL> alter database add logfile member '/home/oracle/redo01b.log' to group 1;
SQL> alter database add logfile member '/home/oracle/redo02b.log' to group 2;
SQL> alter database add logfile member '/home/oracle/redo03b.log' to group 3;
SQL> alter database add logfile group 4 ('/u01/app/oracle/oradata/orcl/redo04.log', '/home/oracle/redo04b.log') size 50M;
FRA：
SQL> show parameter db_recovery
backupset: 10GB, archived log: 5GB
10+5, 10G
开启归档模式：
SQL> archive log list 检查当前设置
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;
SQL> archive log list
SQL> show parameter log_archive_dest
SQL> select group#, sequence#, status, archived from v$log;
SQL> alter system switch logfile;
SQL> select group#, sequence#, status, archived from v$log;
SQL> select NAME, SEQUENCE#, STATUS from v$archived_log;
$ ls /u01/app/oracle/fast_recovery_area/ORCL
RMAN配置
$ rman target / 或rman target sys/password@orcl
RMAN> show all;
RMAN>CONFIGURE CONTROLFILE AUTOBACKUP On;
RMAN>CONFIGURE CONTROLFILE AUTOBACKUP clear;
RMAN> backup tablespace users;
RMAN> list backup;
SQL> desc v$backup_set 浏览器中也可以查看
自动通道管理：
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
RMAN> backup tablespace users, example;
$ mkdir /home/oracle/disk1 /home/oracle/disk2
RMAN> configure channel 1 device type disk to destination '/home/oracle/disk1';
RMAN> configure channel 2 device type disk to destination '/home/oracle/disk2';
RMAN> backup tablespace users, example;
恢复默认
RMAN> CONFIGURE DEVICE TYPE DISK clear;
RMAN> CONFIGURE CHANNEL 1 device type disk clear;
RMAN> CONFIGURE CHANNEL 2 device type disk clear;
手动通道管理：
RMAN> run {
allocate channel c1 device type disk to destination '/home/oracle/disk1';
allocate channel c2 device type disk to destination '/home/oracle/disk2';
backup tablespace users, example;
或
backup (tablespace users channel c1)(tablespace example channel c2);
}
backup section size 500M datafile 1;
指定备份格式：
RMAN> backup tablespace users;
RMAN> backup as compressed backupset tablespace users;
RMAN> backup as copy tablespace users;
RMAN> list backup of tablespace users;
RMAN> list copy of tablespace users;
备份的加密：
RMAN备份
不归档 归档
online offline online offline
完全部分完全部分完全部分完全部分
shutdown nomount mount open
备份数据文件：
SQL> select file_id, file_name from dba_data_files;
RMAN> backup datafile 4;
RMAN> backup datafile 4,5;
RMAN> backup datafile '/u01/app/oracle/oradata/orcl/users01.dbf';
RMAN> backup tablespace users;
RMAN> backup tablespace users, example;
RMAN> backup database;
RMAN> list backup;
desc v$backup_set，浏览器查看备份
增量备份：
RMAN> backup incremental level 0 tablespace users;
RMAN> list backup of tablespace users;
SQL> create table t1(x int) tablespace users;
SQL> insert into t1 values (1);
SQL> commit;
RMAN> backup incremental level 1 tablespace users;
RMAN> backup incremental level 1 tablespace users;
SQL> create table t2(x int) tablespace users;
SQL> insert into t2 values (1);
SQL> commit;
RMAN> backup incremental level 1 cumulative tablespace users;
RMAN> list backup of tablespace users;
开启块跟踪：
SQL> alter database enable block change tracking using file '/home/oracle/blk_trk.chg';
需要重新连接会话
SQL> select * from v$block_change_tracking;
SQL> select DATAFILE_BLOCKS, BLOCKS_READ, BLOCKS, USED_CHANGE_TRACKING from v$backup_datafile where INCREMENTAL_LEVEL>0;
SQL> create table t3(x int) tablespace users;
SQL> insert into t3 values (1);
SQL> commit;
RMAN> backup incremental level 1 tablespace users;
SQL> select DATAFILE_BLOCKS, BLOCKS_READ, BLOCKS, USED_CHANGE_TRACKING from v$backup_datafile where INCREMENTAL_LEVEL>0;
SQL> alter database disable block change tracking; 关闭
增量更新：
SQL> create table t1(x int) tablespace users;
SQL> insert into t1 values (1);
SQL> commit;
RMAN> backup incremental level 1 for recover of copy with tag 'update_copy' tablespace users; 第一次创建的是0级备份
RMAN> list copy; 记录time和scn
SQL> insert into t1 values (2);
SQL> commit;
RMAN> backup incremental level 1 for recover of copy with tag 'update_copy' tablespace users; 第2次创建的是1级备份
RMAN> list backup; backupset格式
RMAN> recover copy of tablespace users with tag 'update_copy';
RMAN> list copy; time和scn更新
脚本形式：
RMAN> run {
backup incremental level 1 for recover of copy with tag 'update_copy' tablespace users;
recover copy of tablespace users with tag 'update_copy';
}
备份归档日志：
RMAN> list archivelog all;
RMAN> backup archivelog all delete all input;
RMAN> list archivelog all;
RMAN> list backup;
备份的维护：
查看：
RMAN> list backup;
RMAN> list copy;
RMAN> list backup of tablespace users;
RMAN> list backup of datafile 4;
RMAN> list archivelog all;
检查备份：
RMAN> delete backup; 删除备份
RMAN> delete copy;
RMAN> list backup; list copy;
RMAN> report need backup; 根据策略检查
RMAN> backup tablespace users;
RMAN> report need backup;
RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
RMAN> report need backup;
RMAN> CONFIGURE RETENTION POLICY clear;
RMAN> report obsolete;
删除备份：
RMAN> delete backupset of tablespace users;
RMAN> delete backupset 1234;
RMAN> backup tablespace users;
RMAN> backup tablespace users;
RMAN> show all;
RMAN> delete obsolete;
crosscheck:
RMAN> delete backup;
RMAN> backup tablespace users;
RMAN> list backup of tablespace users;
$ mv /u01/app/oracle/fast_recovery_area/ORCL/backupset/… 改名
RMAN> restore datafile 4; 报错
RMAN> crosscheck backup;
RMAN> list backup of tablespace users; 报废状态
RMAN> list expired backup;
$ mv /u01/app/oracle/fast_recovery_area/ORCL/backupset/… 恢复原名
RMAN> crosscheck backup;
RMAN> list backup of tablespace users; 可用状态
RMAN> delete expired backup;
catalog:
$ cp /u01/app/oracle/fast_recovery_area/ORCL/backupset/… 复制
RMAN> delete backup;
RMAN> list backup; backupset消失
$ mv /u01/app/oracle/fast_recovery_area/ORCL/backupset/… 恢复原名
RMAN> catalog recovery area noprompt;
RMAN> list backup; backupset恢复
RMAN恢复
数据文件
RPO/RTO
数据文件：
不归档方式下丢失一个数据文件：
SQL> archive log list
备份前工作：
SQL> create table t1(x varchar2(50)) tablespace users;
SQL> insert into t1 values ('friday, before backup');
SQL> commit;
备份：
SQL>查询v$datafile, v$logfile, v$tempfile, v$controlfile
SQL> shutdown immediate
$ cd $ORACLE_BASE/oradata/
$ cp -r orcl orcl.bak
$ cd $ORACLE_BASE/fast_recovery_area/orcl
$ cp control02.ctl control02.ctl.bak
SQL> startup
备份后工作：
SQL> insert into t1 values ('monday, after backup');
SQL> commit;
故障：
SQL> alter system flush buffer_cache;
$ cd $ORACLE_BASE/oradata/orcl
$ >users01.dbf
SQL> select * from t1; 报错
$ vi /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log
$ dbv file=/u01/app/oracle/oradata/orcl/users01.dbf
恢复：
SQL> shutdown abort
$ cd $ORACLE_BASE/oradata
$ rm -rf orcl
$ mv orcl.bak orcl
$ cd $ORACLE_BASE/fast_recovery_area/orcl
$ mv control02.ctl.bak control02.ctl
SQL> startup
SQL> select * from t1;
归档模式下丢失一个数据文件：
SQL> archive log list
备份前工作：
SQL> create table t1(x varchar2(50)) tablespace users;
SQL> insert into t1 values ('friday, before backup');
SQL> commit;
备份：
RMAN> backup tablespace users tag "tbs_users_weekend_backup";
备份后工作：
SQL> select group#, sequence#, status, archived from v$log;
SQL> insert into t1 values ('after backup, logseq 7, archived');
SQL> commit;
SQL> alter system switch logfile;
SQL> insert into t1 values ('after backup, logseq 8, archived');
SQL> commit;
SQL> alter system switch logfile;
SQL> insert into t1 values ('after backup, logseq 9, archived');
SQL> commit;
SQL> alter system switch logfile;
SQL> insert into t1 values ('after backup, logseq 10, current');
SQL> commit;
SQL> insert into t1 values ('after backup, logseq 10, current, uncommitted');
SQL> select * from t1;
故障：
SQL> shutdown abort
$ rm $ORACLE_BASE/oradata/orcl/users01.dbf
SQL> startup 报错
SQL> select open_mode from v$database;
$ vi /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log
恢复：
RMAN> list backup of tablespace users;
RMAN> list archivelog all;
SQL> alter database datafile 4 offline; system和undotbs不能offline
SQL> alter database open;
RMAN> restore datafile 4;
RMAN> recover datafile 4;
SQL> alter database datafile 4 online;
SQL> select * from t1;
通过不完全恢复解决用户的误操作：
SQL> archive log list
备份前工作：
SQL> create table t1(x varchar2(50)) tablespace users;
SQL> insert into t1 values ('before backup');
SQL> commit;
备份：
RMAN> backup database tag 'weekend_DB_full_backup';
备份后：
SQL> insert into t1 values ('after backup, before delete');
SQL> commit;
误操作：
SQL> select sysdate from dual;
SQL> select dbms_flashback.get_system_change_number from dual;
SQL> delete t1;
SQL> commit;
SQL> create table after_delete (x int); 正确的操作
SQL> insert into after_delete values (1);
SQL> commit;
恢复：
RMAN>run {
startup force mount;
set until scn= 1806683;
restore database;
recover database;
alter database open resetlogs;
}
set until time='2015-10-26 11:13:23'; 基于时间点恢复
SQL> select * from t1;
SQL> select * from after_delete; 丢失
SQL> select group#, sequence#, status, archived from v$log;
通过不完全恢复解决归档日志不连续：
SQL> archive log list
备份前：
SQL> create table t1(x varchar2(50)) tablespace users;
SQL> insert into t1 values ('before backup');
SQL> commit;
备份：
RMAN> backup database tag 'weekend_DB_full_backup';
备份后：
SQL> select GROUP#, SEQUENCE#, STATUS, archived from v$log;
SQL> insert into t1 values ('after backup, logseq 1, archived');
SQL> commit;
SQL> alter system switch logfile;
SQL> insert into t1 values ('after backup, logseq 2, archived');
SQL> commit;
SQL> alter system switch logfile;
SQL> insert into t1 values ('after backup, logseq 3, archived');
SQL> commit;
SQL> alter system switch logfile;
SQL> insert into t1 values ('after backup, logseq 4, archived');
SQL> commit;
SQL> alter system switch logfile;
SQL> insert into t1 values ('after backup, logseq 5, current');
SQL> commit;
SQL> alter system checkpoint;
故障：
SQL> shutdown abort
$ rm /u01/app/oracle/oradata/orcl/users01.dbf
$ rm /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2016_03_01/o1_mf_1_5_cfbcxo84_.arc
恢复：
SQL> startup
先尝试只恢复一个数据文件失败。
RMAN>run {
startup force mount;
set until sequence 5;
restore database;
recover database;
alter database open resetlogs;
}
SQL> select * from t1;
控制文件
丢失部分控制文件：
SQL> select * from v$controlfile;
$ >/u01/app/oracle/oradata/orcl/control01.ctl
SQL> select * from v$tablespace; 报错
SQL> alter system checkpoint; 报错
$ vi /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log
SQL> shutdown abort
SQL> startup nomount
SQL> show parameter control_files
$ cp /u01/app/oracle/fast_recovery_area/orcl/control02.ctl /u01/app/oracle/oradata/orcl/control01.ctl
SQL> alter database mount;
SQL> alter database open;
丢失全部控制文件（有自动备份）：
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> backup datafile 4;
$ >/u01/app/oracle/oradata/orcl/control01.ctl
$ >/u01/app/oracle/fast_recovery_area/orcl/control02.ctl
SQL> select * from v$tablespace; 报错
SQL> alter system checkpoint; 报错
SQL> shutdown abort
SQL> startup nomount
RMAN> restore controlfile from autobackup;
RMAN> alter database mount;
RMAN> recover database;
RMAN> alter database open resetlogs;
丢失全部控制文件（没有自动备份）：
SQL> alter database backup controlfile to '/home/oracle/control.bak';
SQL> alter database backup controlfile to trace;
SQL> select * from v$diag_info;
spfile
有自动备份：
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> backup datafile 4;
联机恢复：
$ mv $ORACLE_HOME/dbs/spfileorcl.ora $ORACLE_HOME/dbs/spfileorcl.ora.bak
SQL> alter system set resource_limit=true; 报错
SQL> create spfile='/home/oracle/spfile.bak' from memory;
$ mv /home/oracle/spfile.bak $ORACLE_HOME/dbs/spfileorcl.ora
脱机恢复：
SQL> shutdown immediate
$ mv $ORACLE_HOME/dbs/spfileorcl.ora $ORACLE_HOME/dbs/spfileorcl.ora.bak
RMAN> startup
RMAN> restore spfile from '/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2015_10_26/o1_mf_s_894118741_c2vkgo8x_.bkp';
RMAN> startup force
没有备份：
利用alert_orcl.log中的参数值，构造initorcl.ora
SQL> create spfile='/home/oracle/spfile.bak' from pfile;
利用备份init.ora：
$ vi /u01/app/oracle/product/11.2.0/db_1/dbs/init.ora 完善参数
SQL>startup pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/init.ora'
SQL> create spfile from pfile;
redo log
丢失一个成员：
SQL> select GROUP#, MEMBERS from v$log;
SQL> select GROUP#, MEMBER from v$logfile;
SQL> alter database add logfile member '/home/oracle/redo01b.log' to group 1;
SQL> alter database add logfile member '/home/oracle/redo02b.log' to group 2;
SQL> alter database add logfile member '/home/oracle/redo03b.log' to group 3;
SQL> alter system switch logfile;
SQL> alter system switch logfile;
SQL> alter system switch logfile;
故障：
SQL> select group#, status from v$log; 确认current组
$ rm -f /home/oracle/redo02b.log 删除current组成员
SQL>alter system switch logfile;
$ vi /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log
恢复：
SQL> alter database drop logfile member '/home/oracle/redo02b.log';
SQL> alter database add logfile member '/home/oracle/redo02b.log' reuse to group 2;
如果是当前日志组，不能删除成员，只能先切换再修改
丢失inactive日志组：
故障：
SQL> alter system checkpoint;
SQL> select group#, status from v$log; 确认inactive组
SQL> shutdown abort
$ rm -f /home/oracle/redo03b.log /u01/app/oracle/oradata/orcl/redo03.log
$ startup 报错
恢复：
SQL> startup mount
SQL> select group#, status, archived from v$log;
SQL> alter database clear logfile group 3;
SQL> alter database open;
如果日志未归档：
SQL> alter database clear unarchived logfile group 3;
做数据库的全备份
丢失current日志组（正常关闭数据库）：
故障：
SQL> select group#, status from v$log; 确认current组
SQL> shutdown immediate
$ rm -f /home/oracle/redo02b.log /u01/app/oracle/oradata/orcl/redo02.log
SQL> startup 报错
恢复：
SQL> startup mount
SQL> select group#, status , archived from v$log;
SQL> alter database clear unarchived logfile group 2;
SQL> alter database open;
做数据库的全备份
丢失current日志组（非正常关闭数据库）：
故障：
RMAN> backup database;
SQL> create table t1(x varchar2(50));
SQL> insert into t1 values ('after backup, before archived');
SQL> commit;
SQL> alter system switch logfile;
SQL> insert into t1 values ('after backup, after archived, current');
SQL> commit;
SQL> insert into t1 values ('after backup, after archived, current, uncommitted');
SQL> alter system checkpoint;
SQL> shutdown abort
$ rm -f /home/oracle/redo03b.log /u01/app/oracle/oradata/orcl/redo03.log
SQL> startup 报错
SQL> select group#, sequence#, status, archived from v$log; 确认日志序号
恢复：
RMAN>run {
startup force mount;
set until sequence 10;
restore database;
recover database;
alter database open resetlogs;}
SQL> select * from t1; 丢失数据
丢失active日志组：
恢复数据块
故障：
SQL> create tablespace tbs01 datafile '/home/oracle/tbs01.dbf' size 5M;
SQL> create table t1 tablespace tbs01 as select * from dba_objects where rownum<=30000;
RMAN> backup tablespace tbs01;
SQL> alter system flush buffer_cache;
$ dd of=/home/oracle/tbs01.dbf bs=8k conv=notrunc seek=300< SQL> select count(*) from t1; 报错 $ dbv file='/home/oracle/tbs01.dbf' 恢复： SQL> select file#, block# from v$database_block_corruption; RMAN> recover datafile 6 block 300; RMAN> recover corruption list; DBMS_REPAIR包隔离数据块 SQL> show parametercontrol_file_record_keep_time 用dbca创建数据库rc（不配置em、fra，200M内存，字符集unicode） 或者： 用netca创建主机连接字符串rc指向自身。 rc： $ sqlplus sys/password@rc as sysdba SQL> create tablespace rc_tbs datafile '/home/oracle/rc_tbs.dbf' size 50M; SQL> create user rcowner identified by password default tablespace rc_tbs quota unlimited on rc_tbs; SQL> grant recovery_catalog_owner to rcowner; $ rman catalog rcowner/password@rc RMAN> create catalog; $ rman target sys/password@orcl catalog rcowner/password@rc 或 $ rman target / catalog rcowner/password@rc RMAN> register database; dbca删除rc 功能 依赖组件 相关参数 典型错误 query undo tbs undo_retention dml version query undo tbs undo_retention dml flashback table undo tbs undo_retention dml flashback drop recyclebin recyclebin, freespace drop table transaction query supplemental log dml fda flashback archive dml database flashback log db_flashback_retention_target ddl sys不允许闪回，创建新用户 SQL> create user user01 identified by password; SQL> grant dba to user01; SQL> conn user01/password user01: SQL> create table t1(x int); SQL> create index t1_x_idx on t1(x); SQL> insert into t1 values (1); SQL> commit; SQL> select sysdate from dual; SQL> select dbms_flashback.get_system_change_number from dual; SQL> delete t1; SQL> commit; SQL> select * from t1; SQL> select * from t1 as of scn 1446069; SQL> select * from t1 as of timestamp to_timestamp('2015-10-28 10:31:54', 'yyyy-mm-dd hh34:mi:ss'); SQL> truncate table t1;或alter table t1 move;或收缩数据文件 SQL> select * from t1 as of scn 1446069; 物理结构变化，闪回失败 logminer SQL> create table t1(x int); SQL> insert into t1 values (1); SQL> commit; SQL> update t1 set x=2; SQL> commit; SQL> update t1 set x=3; SQL> commit; SQL> update t1 set x=4; SQL> commit; SQL>select versions_starttime, versions_endtime, versions_xid, versions_operation, x from t1 versions between scn minvalue and maxvalue order by versions_starttime; versions between timestamp to_timestamp('2015-10-28 9:00:00', 'yyyy-mm-dd hh34:mi:ss') and to_timestamp('2015-10-28 10:00:00', 'yyyy-mm-dd hh34:mi:ss') SQL> truncate table t1; 物理结构改变，查询失败 SQL> conn user01/password SQL> create table my_dept(deptno int primary key, dname varchar2(20)); SQL> create table my_emp(empno int primary key, deptno int references my_dept); SQL> insert into my_dept values (10, 'sales'); SQL> insert into my_emp values (100, 10); SQL> commit; SQL> select dbms_flashback.get_system_change_number from dual; SQL> delete my_emp; SQL> delete my_dept; SQL> commit; SQL> alter table my_dept enable row movement; SQL> alter table my_emp enable row movement; SQL> flashback table my_emp to scn 1451706; 失败 SQL> flashback table my_dept to scn 1451706; SQL> flashback table my_emp to scn 1451706; SQL> select INDEX_NAME, STATUS from user_indexes where table_name='MY_EMP';自动维护索引 SQL> select INDEX_NAME, STATUS from user_indexes where table_name='MY_DEPT'; SQL> truncate table my_emp; SQL> flashback table my_emp to scn 1451706; 失败 SQL> show parameter recyclebin SQL> purge recyclebin; SQL> create tablespace tbs01 datafile '/home/oracle/tbs01.dbf' size 5M; SQL> create table t1 tablespace tbs01 as select * from dba_objects where rownum<=20000; SQL> create index t1_object_id_idx on t1(object_id) tablespace tbs01; SQL> select INDEX_NAME from user_indexes where TABLE_NAME='T1'; SQL> drop table t1; SQL> select table_name from user_tables; SQL> show recyclebin SQL> select object_name, original_name, type, droptime from user_recyclebin; 包含index SQL> select count(*) from "BIN$IyKOcy5jPo7gUwEAqMCBEg==$0"; SQL> flashback table t1 to before drop; SQL> select INDEX_NAME from user_indexes where TABLE_NAME='T1'; SQL> alter index "BIN$LRyc7hA1JaPgUwEAqMDzWw==$0" rename to T1_OBJECT_ID_IDX; 恢复index名称 重名的处理： SQL> flashback table "BIN$IyKOcy5jPo7gUwEAqMCBEg==$0" to before drop; SQL> flashback table t1 to before drop rename to t2; SQL> drop table t1; SQL> show recyclebin 在回收站中 SQL> create table t2 tablespace tbs01 as select * from dba_objects where rownum<=30000; SQL> show recyclebin t1被覆盖 SQL> drop table t2 purge; SQL> purge recyclebin SQL> alter database add supplemental log data; SQL> alter database add supplemental log data (primary key) columns; SQL> create table t1(x int); SQL> insert into t1 values (1); SQL> commit; SQL> update t1 set x=11 where x=1; 误操作的事务 SQL> commit; SQL> insert into t1 values (2); SQL> commit; select versions_starttime, versions_endtime, versions_xid, versions_operation, x from t1 versions between scn minvalue and maxvalue order by versions_starttime; 获取误操作事务的xid SQL> select UNDO_SQL, OPERATION from flashback_transaction_query where xid='02000F0059040000'; SQL> shutdown immediate SQL> startup mount SQL> alter database flashback on; 数据库在归档模式下 SQL> show parameter db_flashback_retention_target SQL> select OLDEST_FLASHBACK_TIME from v$flashback_database_log; SQL> create table t1(x int); SQL> insert into t1 values (1); SQL> commit; SQL> select dbms_flashback.get_system_change_number from dual; SQL> truncate table t1; SQL> create table after_truncate(x int); 其他正确操作 SQL> select OLDEST_FLASHBACK_TIME, OLDEST_FLASHBACK_SCN from v$flashback_database_log; 确认是否在恢复范围 SQL> shutdown abort SQL> startup mount SQL> flashback database to scn 1495195; SQL> alter database open resetlogs; SQL> select * from t1; SQL> select * from after_truncate; 消失 SQL> create table t1(id int constraint t1_id_pk primary key, name varchar2(20), salary int constraint t1_salary_ck check(salary>0)); $ vi ~/loader.dat 100,"abc",1000 100,"def",2000 102,"xyz",-1000 em中常规导入，自动处理违反约束的记录 em中直接导入 SQL> select CONSTRAINT_NAME, STATUS from user_constraints where TABLE_NAME='T1'; SQL> select INDEX_NAME, STATUS from user_indexes where TABLE_NAME='T1'; SQL> alter table t1 enable validate constraint T1_SALARY_CK; 失败 SQL> @?/rdbms/admin/utlexpt1.sql 处理check约束： SQL> alter table t1 enable validate constraint T1_SALARY_CK exceptions into exceptions; SQL> select * from t1 where rowid in(select ROW_ID from exceptions); SQL> update t1 set salary=abs(salary) where id=102; SQL> truncate table exceptions; SQL> alter table t1 enable validate constraint T1_SALARY_CK exceptions into exceptions; 处理pk约束： SQL> alter table t1 disable novalidate constraint T1_ID_PK; SQL> alter table t1 enable validate constraint T1_ID_PK exceptions into exceptions; SQL> select * from t1 where rowid in(select ROW_ID from exceptions); SQL> update t1 set id=101 where name='def'; SQL> truncate table exceptions; SQL> alter table t1 enable validate constraint T1_ID_PK exceptions into exceptions; SQL> select INDEX_NAME, STATUS from user_indexes where TABLE_NAME='T1'; oracle_datapump driver unloading: CREATE TABLE oe.inventories_xt ORGANIZATION EXTERNAL ( TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY DATA_PUMP_DIR LOCATION ('inv_xt.dmp') ) AS SELECT * FROM oe.inventories; SQL> delete oe.inventories_xt; 失败 loading: CREATE TABLE oe.inventories_xt2 ( product_id NUMBER(6), warehouse_id NUMBER(3), quantity_on_hand NUMBER(8) ) ORGANIZATION EXTERNAL ( TYPE ORACLE_DATAPUMP DEFAULT DIRECTORY DATA_PUMP_DIR LOCATION ('inv_xt.dmp') ); SQL> delete oe.inventories_xt2; 失败 DB time = CPU time + Wait time 字典表： SQL> create table t1(x int); SQL> insert into t1 values (1); SQL> commit; SQL> select TABLE_NAME, NUM_ROWS from dba_tables where table_name='T1'; 值为空 SQL> exec dbms_stats.gather_table_stats('sys', 't1'); SQL> select TABLE_NAME, NUM_ROWS from dba_tables where table_name='T1'; 更新 SQL> insert into t1 values (2); SQL> commit; SQL> select TABLE_NAME, NUM_ROWS from dba_tables where table_name='T1'; 过时 SQL> exec dbms_stats.gather_table_stats('sys', 't1'); SQL> select TABLE_NAME, NUM_ROWS, LAST_ANALYZED from dba_tables where table_name='T1'; 更新 v$表： SQL> select name, value from v$sysstat where name like '%sort%'; SQL> select * from hr.employees order by salary; SQL> select name, value from v$sysstat where name like '%sort%'; 增加 SQL> shutdown immediate SQL> startup SQL> select name, value from v$sysstat where name like '%sort%'; 归零 参数： SQL> show parameterstatistics_level 不能是basic em中查看基本设置 em中生成和查看awr报表 $ ll /u01/app/oracle/product/11.2.0/db_1/rdbms/admin/awr*.sql SQL> create tablespace tbs01 datafile '/home/oracle/tbs01.dbf' size 5M; em中修改警告50%，严重80%。 SQL> create table t1 tablespace tbs01 as select * from dba_objects where 1=0; SQL> insert into t1 select * from dba_objects where rownum<=10000; SQL> commit; 超过50% SQL> insert into t1 select * from dba_objects where rownum<=20000; SQL> commit; 超过80% em中查看警告信息。 session1: SQL> create table t1(x int); SQL> insert into t1 values (1); SQL> commit; SQL> update t1 set x=11; session 2: SQL> update t1 set x=22; session1: SQL> create table t1(x int); SQL> insert into t1 values (1); SQL> commit; SQL> update t1 set x=11; session 2: SQL> update t1 set x=22; em中寻找问题的根源 em中做ash报表 $ ll /u01/app/oracle/product/11.2.0/db_1/rdbms/admin/ash*.sql initorcl.ora spfileorcl.ora ASMM AMM SQL> select bytes/1024/1024 from v$sgainfo where name='Granule Size'; SQL> show parameter memory SQL> select COMPONENT, CURRENT_SIZE/1024/1024 from v$memory_dynamic_components; em中的内存指导 $ strings $ORACLE_HOME/dbs/spfileorcl.ora __开头的隐含参数保留优化设置 javapool的调整： SQL> select CURRENT_SIZE/1024/1024 from v$memory_dynamic_components where COMPONENT='java pool'; DECLARE i NUMBER; v_sql VARCHAR2(200); BEGIN FOR i IN 1..200 LOOP -- Build up a dynamic statement to create a uniquely named java stored proc. -- The "chr(10)" is there to put a CR/LF in the source code. v_sql := 'create or replace and compile' || chr(10) || 'java source named "SmallJavaProc' || i || '"' || chr(10) || 'as' || chr(10) || 'import java.lang.*;' || chr(10) || 'public class Util' || i || ' extends Object' || chr(10) || '{ int v1=1;int v2=2;int v3=3;int v4=4;int v5=5;int v6=6;int v7=7; }'; EXECUTE IMMEDIATE v_sql; END LOOP; END; / SQL> select CURRENT_SIZE from v$memory_dynamic_components where COMPONENT='java pool'; java pool改变 java pool扩展、buffercache收缩 SQL> select OPER_TYPE, OPER_MODE, INITIAL_SIZE, TARGET_SIZE, FINAL_SIZE, START_TIME from v$memory_resize_ops where COMPONENT='java pool'; SQL> select OPER_TYPE, OPER_MODE, INITIAL_SIZE, TARGET_SIZE, FINAL_SIZE, START_TIME from v$memory_resize_ops where COMPONENT='DEFAULT buffer cache'; largepool的调整： SQL> select CURRENT_SIZE/1024/1024 from v$memory_dynamic_components where COMPONENT='large pool'; SQL> create table t1 as select rownum x from dual connect by level<=100000; SQL> alter table t1 parallel 64; 也可以在查询时指定并行度 SQL> select /*+ parallel(t1 24) */ count(*) from (select /*+ parallel(t1 24)*/ * from t1 group by x); SQL> select CURRENT_SIZE/1024/1024 from v$memory_dynamic_components where COMPONENT='large pool'; large pool改变 SQL> select OPER_TYPE, OPER_MODE, INITIAL_SIZE, TARGET_SIZE, FINAL_SIZE, START_TIME from v$memory_resize_ops where COMPONENT='large pool'; 备份spfile $ cp $ORACLE_HOME/dbs/spfileorcl.ora $ORACLE_HOME/dbs/spfileorcl.ora.bak AMMàASMM amm下，sga和pga不需要设置 SQL> show parameter sga_max_size SQL> show parameter sga_target SQL> show parameter pga_aggregate_target SQL> alter system set memory_target=0; SQL> show parameter sga_target amm和asmm都有一对参数 SQL> show parameter sga_max_size SQL> show parameter pga_aggregate_target SQL> alter system set sga_target=300M; 手动修改 ASMMàmanual SQL> show parameter shared_pool_size 值为0 SQL> alter system set sga_target=0; SQL> show parameter shared_pool_size 固定 manualàasmmàamm 修改sga_target或memory_target，清空所有遗留参数 内存大小的建议： SQL> select SHARED_POOL_SIZE_FOR_ESTIMATE ,SHARED_POOL_SIZE_FACTOR, ESTD_LC_TIME_SAVED from V$SHARED_POOL_ADVICE; SQL> select SIZE_FOR_ESTIMATE, SIZE_FACTOR, ESTD_PHYSICAL_READS, ESTD_PHYSICAL_READ_FACTOR from V$DB_CACHE_ADVICE; SQL> select * from V$SGA_TARGET_ADVICE; SQL> select PGA_TARGET_FOR_ESTIMATE, PGA_TARGET_FACTOR, ESTD_PGA_CACHE_HIT_PERCENTAGE from V$PGA_TARGET_ADVICE; SQL> select * from V$MEMORY_TARGET_ADVICE; SQL> alter system flush shared_pool; SQL> grant dba to hr; SQL> conn hr/hr SQL> set autot on SQL> select e.last_name, d.department_name from employees e, departments d where e.department_id=d.department_id; em中执行saa，过滤条件为表：hr.employees, hr.departments SQL> alter system flush shared_pool; SQL> conn hr/hr SQL> set autot on SQL> select /*+ full(employees) */ * from employees where employee_id=100; em中创建tuningset，调用sta分析 参考：optimizer介绍.ppt 查询改写： 谓词传递： SQL> set autot trace exp SQL>select e.last_name, d.department_name from hr.employees e, hr.departments d where e.department_id=d.department_id and e.department_id=50; 自动添加3 - access("D"."DEPARTMENT_ID"=50)谓词 for i in 1 .. 107(employees) for j in 1 .. 27(departments) i的部门=j的部门而且 i的部门=50 endloop endloop for i in 1 .. 10 (employeesin deptno 50) for j in 1 .. 27(departments) i的部门=j的部门 endloop endloop for i in 1 .. 10 (employeesin deptno 50) i的部门=50 endloop 子查询解嵌套： SQL> select last_name from hr.employees outer where salary > (select avg(salary) from hr.employees where department_id = outer.department_id); 被改写为多表连接 CBO和RBO的区别： SQL> create table t1 as select 1 id, object_name from dba_objects; SQL> update t1 set id=2 where rownum<=1; SQL> commit; SQL> select id, count(*) from t1 group by id; SQL> create index t1_id_idx on t1(id); SQL> exec dbms_stats.gather_table_stats('sys', 't1'); SQL> set autot trace exp SQL> select * from t1 where id=1; cbo方式 SQL> select /*+ rule */ * from t1 where id=1; SQL> select * from t1 where id=2; 错误 SQL> select /*+ rule */ * from t1 where id=2; 走索引，正确 SQL> exec dbms_stats.gather_table_stats('sys', 't1'); 重复搜集，获取列值分布 exec dbms_stats.gather_table_stats('sys', 't1', method_opt => 'for columns size auto id'); SQL> select * from t1 where id=1; cbo方式，正确 SQL> select * from t1 where id=2; cbo方式，正确 SQL> select /*+ rule */ * from t1 where id=1; 走索引，错误 SQL> select /*+ rule */ * from t1 where id=2; 不及时更新统计信息，造成错误 SQL> update t1 set id=2; SQL> commit; SQL> select * from t1 where id=2; 走索引，错误 SQL> exec dbms_stats.gather_table_stats('sys', 't1'); SQL> select * from t1 where id=2; 正确 SQL> exec dbms_stats.delete_table_stats('sys', 't1'); SQL> update t1 set id=1 where rownum<=1; SQL> commit; SQL> select * from t1 where id=1; 动态采样 SQL> select * from t1 where id=2; 动态采样 影响cbo的初始化参数： SQL> show parameter optimizer all_rows和first_rows对执行计划的影响： SQL> alter session set optimizer_mode=first_rows或all_rows; SQL> set autot trace exp select e.last_name, d.department_name from hr.employees e, hr.departments d where e.department_id=d.department_id; select /*+ all_rows */ e.last_name, d.department_name from hr.employees e, hr.departments d where e.department_id=d.department_id; 使用sort merge select /*+ first_rows */ e.last_name, d.department_name from hr.employees e, hr.departments d where e.department_id=d.department_id; 使用nested loop explain plan SQL> desc plan_table SQL> select * from plan_table; SQL> explain plan set statement_id='test' for select * from hr.employees; SQL> select PLAN_ID, OPERATION from plan_table where statement_id='test'; 可读性差 SQL> select plan_table_output from table(dbms_xplan.display); 可读性比较好 不真正执行语句，对使用绑定变量的语句可能出现误差 autotrace SQL> set autot on SQL> select count(*) from hr.employees; SQL> set autot trace SQL> select count(*) from hr.employees; SQL> set autot trace exp SQL> select count(*) from hr.employees; SQL> set autot trace stat SQL> select count(*) from hr.employees; SQL> set autot off on选项真正执行语句，但对使用绑定变量的语句可能出现误差 其他选项不真正执行语句 DBMS_XPLAN 参考：PL/SQL Packages and Types ReferenceDBMS_XPLAN 与explainplan配合： 参考explain plan示例 display_cursor: 查看上一个sql语句： SQL>SET PAGESIZE 0 SQL> select count(*) from hr.employees; SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR); SQL> select count(*) from hr.employees; SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(null, null, 'advanced')); SQL> desc v$sql SQL> desc v$sql_plan SQL> desc v$sql_plan_statistics SQL> select count(*) from hr.employees; SQL> select sql_id, child_number, sql_text from v$sql where sql_text like 'select count(*) from hr.employees'; SQL> select OPERATION, OPTIONS, OBJECT_NAME from v$sql_plan where SQL_ID='3ghpkw4yp4dzm' and CHILD_NUMBER=0; SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('3ghpkw4yp4dzm',0, 'advanced')); 也可以从awr快照中获取sql执行计划，display_awr sql trace: SQL> show parameter sql_trace SQL> show parameter statistics_level SQL> show parameter timed_statistics 辅助参数 SQL> show parameter max_dump_file_size SQL> show parameter diagnostic_dest SQL> show parameter tracefile_identifier SQL> select * from v$diag_info; SQL> alter session set sql_trace=true; SQL> select count(*) from hr.employees; SQL> select count(*) from hr.departments; SQL> alter session set sql_trace=false; $ vi /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_6651.trc $ tkprof /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_6651.trc /home/oracle/output.trc $ vi /home/oracle/output.trc 使用DBMS_MONITOR监控指定session: SQL> select sid, serial# from v$session where USERNAME='HR'; SQL>EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(30, 4145, TRUE, TRUE); hr的session: SQL> select count(*) from employees; sys关闭跟踪： SQL> EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE(30, 4145); hr的跟踪文件： SQL> select * from v$diag_info; $ vi /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_10676.trc 使用trcsess汇总共享服务器连接下的用户会话信息。 执行计划的读取： SQL> set linesize 999 SQL> set autot trace exp SQL>select e.last_name, d.department_name from hr.employees e, hr.departments d where e.department_id=d.department_id; sqlplus和sql developer中查看 统计信息： SQL> alter system flush shared_pool; SQL> alter system flush buffer_cache; SQL> set autot on SQL> select count(*) from hr.employees; SQL> select count(*) from hr.employees; SQL> select * from hr.employees; 扫描高水标记以下的所有块 查询的比例、物理顺序、表小、没有索引、并行 selectivity和cardinality参考：optimizer介绍.ppt 物理顺序对全表扫描的影响： SQL> create table t1 as select rownum x, dbms_random.value y from dual connect by level<=10000; SQL> alter table t1 add constraint t1_x_pk primary key(x); SQL> create table t2 as select * from t1 order by y; SQL> alter table t2 add constraint t2_x_pk primary key(x); SQL> exec dbms_stats.gather_table_stats('sys', 't1'); SQL> exec dbms_stats.gather_table_stats('sys', 't2'); SQL> select * from t1 where x between 1 and 100; SQL> select * from t2 where x between 1 and 100; SQL> select INDEX_NAME, CLUSTERING_FACTOR from dba_indexes where table_name in('T1', 'T2'); 查询语句对全表扫描的影响： SQL> select * from hr.employees; SQL> select * from hr.employees order by employee_id; SQL> select employee_id from hr.employees; SQL>select department_id from hr.employees; db_file_multiblock_read_count对全表扫描的影响： SQL> show parameter db_file_multiblock_read_count SQL> create table t1 as select * from dba_objects; SQL> exec dbms_stats.gather_table_stats('sys', 't1'); SQL> set autot on SQL> alter system set db_file_multiblock_read_count=16; SQL> select count(*) from t1; SQL> alter system set db_file_multiblock_read_count=64; SQL> select count(*) from t1; 高水标记对全表扫描的影响： SQL> delete t1; SQL> commit; SQL> set autot on SQL> select count(*) from t1; 删除数据后，hwm不下降，导致cr读过多 SQL> alter table t1 move; SQL> select count(*) from t1; INDEX UNIQUE SCAN: SQL> select * from hr.employees where employee_id=100; INDEX RANGE SCAN: SQL> select * from hr.employees where employee_id between 100 and 110; SQL> select * from hr.employees where department_id=10; SQL> select * from hr.employees where last_name='King'; SQL> select INDEX_NAME, UNIQUENESS from dba_indexes where TABLE_NAME='EMPLOYEES'; INDEX FULL SCAN: 单块，有序 SQL> select * from hr.employees order by employee_id; SQL> select /*+ full(employees) */ * from hr.employees order by employee_id; SQL> select * from hr.employees order by department_id; 全表扫描，因为有null值 INDEX FAST FULL SCAN: 多块，无序 SQL> create table t1 as select rownum id, object_name from dba_objects; SQL> alter table t1 add constraint t1_id_pk primary key(id); SQL> exec dbms_stats.gather_table_stats('sys', 't1'); SQL> select count(*) from t1; SQL> select /*+ index(t1 t1_id_pk) */ count(*) from t1; full scan的开销大 SQL> select /*+ full(t1) */ count(*) from t1; SQL> select /*+ index_ffs(employees emp_emp_id_pk) */ employee_id from hr.employees; INDEX SKIP SCAN: SQL> create table t1 as select * from dba_objects; SQL> select count(distinct owner), count(distinct object_type), count(distinct object_name) from t1; SQL> create index t1_idx on t1(owner, object_type, object_name); SQL> exec dbms_stats.gather_table_stats('sys', 't1'); SQL> select * from t1 where owner='SYS' and object_type='TABLE' and object_name='AUD$'; SQL> select * from t1 where owner='SYS' and object_type='TABLE'; SQL> select * from t1 where object_type='TABLE' and object_name='AUD$'; SQL> select /*+ full(t1) */ * from t1 where object_type='TABLE' and object_name='AUD$'; class_no: 5 stud_no: 50（每个班级） 1 1 2 3 … 50 2 1 2 3 … 50 5 1 2 3 … 50 where stud_no between 5 and 10; where class_no=1 and stud_nobetween 5 and 10 or class_no=2 and stud_nobetween 5 and 10 or class_no=3 and stud_nobetween 5 and 10 null对索引的影响： SQL> create table t1(x int, y char(1)); SQL> insert into t1 values (null, 'a'); SQL> insert into t1 values (1, 'a'); SQL> insert into t1 values (2, 'a'); SQL> create index t1_x_idx on t1(x); SQL> exec dbms_stats.gather_table_stats('sys', 't1'); 测试下列语句： select x from t1; select count(*) from t1; select count(*) from t1 where x is not null; select count(x) from t1; select max(x) from t1; select min(x), max(x) from t1; 排除null，再次测试： SQL> delete t1 where x is null; SQL> commit; SQL> alter table t1 modify(x not null); SQL> select (select min(x) from t1), (select max(x) from t1) from dual; 重复值对索引的影响： SQL> create table t1(x int not null, y int); SQL> insert into t1 select rownum, 11 from dual connect by level<=10; SQL> commit; SQL> create index t1_x_idx on t1(x); SQL> exec dbms_stats.gather_table_stats('sys', 't1'); SQL> select * from t1 where x=1; full table scan SQL> select INDEX_NAME, UNIQUENESS from dba_indexes where TABLE_NAME='T1'; SQL> drop index t1_x_idx; SQL> create unique index t1_x_idx on t1(x); SQL> select * from t1 where x=1; index unique scan 外键对索引的影响： SQL> create table dept(deptno int constraint dept_deptno_pk primary key, dname varchar2(10)); SQL> create table emp(empno int, deptno int constraint dept_emp_deptno_fk references dept(deptno)); SQL> insert into dept values (10, 'sales'); SQL> insert into dept values (20, 'market'); SQL> insert into dept values (30, 'it'); SQL> insert into emp values (100, 10); SQL> commit; SQL> exec dbms_stats.gather_table_stats('sys', 'dept'); SQL> exec dbms_stats.gather_table_stats('sys', 'emp'); SQL> alter session set sql_trace=true; SQL> delete dept where deptno=10; 报错 SQL> delete dept where deptno=20; SQL> alter session set sql_trace=false; SQL> select * from v$diag_info; $ tkprof /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_22830.trc /home/oracle/output.trc $ vi /home/oracle/output.trc SQL> alter table emp modify(deptno not null); SQL> create index emp_deptno_idx on emp(deptno); SQL> alter session set sql_trace=true; SQL> delete dept where deptno=10; 报错 SQL> alter session set sql_trace=false; 类型转换对索引的影响： SQL> create table t1(x char(1) primary key, y int); SQL> insert into t1 values ('1', 11); SQL> insert into t1 values ('2', 22); SQL> commit; SQL> exec dbms_stats.gather_table_stats('sys', 't1'); SQL> set autot trace exp SQL> select * from t1 where x=1; 函数索引： SQL> create table t1 as select * from dba_objects; SQL> create index t1_object_name_fbi on t1(lower(object_name)); SQL> select * from t1 where lower(object_name)='aud$'; create index t1_idx on t1(reverse(x)); where x like reserve('%abc%'); bitmap index: emp ename gender deptno location job_id abc M 10 BJ MGR def F 20 SH EGR xyz M 30 GZ MGR select ename from emp where gender='M' and (deptno=10 or location='GZ') and job_id='MGR'; gender M F abc 1 0 def 0 1 xyz 1 0 deptno 10 20 30 abc 1 0 0 def 0 1 0 xyz 0 0 1 location BJ SH GZ abc 1 0 0 def 0 1 0 xyz 0 0 1 job_id MGR EGR abc 1 0 def 0 1 xyz 1 0 gender(M) and (deptno(10)or location(GZ)) and job_id(mgr) abc 1 1 0 1 1 def 0 0 0 0 0 xyz 1 0 1 1 1 nested loop: for emp in 1..107 for dept in 1..27 emp.deptno=dept.deptno end; end; for dept 1-27 for emp 1-107 sort merge: emp 根据deptno排序 dept根据deptno排序 合并emp和dept hashjoin: emp 根据hash函数对deptno分割 dept根据hash函数对deptno分割 select /*+ first_rows */ e.last_name, d.department_name from hr.employees e, hr.departments d where e.department_id=d.department_id; 或者使用/*+ use_nl(e d) */，但限制了驱动表和被驱动表，不灵活 SQL> alter index hr.EMP_DEPARTMENT_IX invisible; 禁用emp上的外键索引 执行多表连接时，将dept的主键索引作为内部被驱动表，nl会尽量避免对被驱动表的全表扫描。 SQL> alter index hr.EMP_DEPARTMENT_IX visible; 恢复索引 select /*+ use_merge(e d) */ e.last_name, d.department_name from hr.employees e, hr.departments d where e.department_id=d.department_id; select /*+ use_hash(e d) */ e.last_name, d.department_name from hr.employees e, hr.departments d where e.department_id=d.department_id; 在缺失索引时，倾向使用hashjoin: SQL> alter index hr.EMP_DEPARTMENT_IX invisible; SQL> alter index hr.DEPT_ID_PK invisible; select e.last_name, d.department_name from hr.employees e, hr.departments d where e.department_id=d.department_id; SQL> alter index hr.EMP_DEPARTMENT_IX visible; SQL> alter index hr.DEPT_ID_PKvisible; SQL> desc dba_tab_statistics SQL> desc dba_tab_col_statistics SQL> desc dba_ind_statistics SQL> create table t1 as select * from dba_objects; SQL> create index t1_object_id_idx on t1(object_id); SQL> create index t1_owner_idx on t1(owner); SQL> exec dbms_stats.gather_table_stats('sys', 't1'); SQL>select * from DBA_TAB_STATISTICS where TABLE_NAME='T1'; SQL>select * from DBA_TAB_COL_STATISTICS where TABLE_NAME='T1'; SQL>select * from DBA_IND_STATISTICS where TABLE_NAME='T1'; SQL> select count(*) from t1 where owner='SYS'; 没有直方图，使用错误计划 SQL> create table t1(x int not null, y varchar2(128)); SQL> create index t1_x_idx on t1(x); SQL> insert into t1 select 1, object_name from dba_objects where rownum<=10000; SQL> insert into t1 select 2, object_name from dba_objects where rownum<=1; SQL> commit; SQL> exec dbms_stats.gather_table_stats('sys', 't1'); SQL> select x, count(*) from t1 group by x; SQL>select * from DBA_TAB_STATISTICS where TABLE_NAME='T1'; SQL>select * from DBA_TAB_COL_STATISTICS where TABLE_NAME='T1'; 未搜集直方图 SQL>select * from DBA_IND_STATISTICS where TABLE_NAME='T1'; SQL> set autot on SQL> select * from t1 where x=1; rows不准确 SQL> select * from t1 where x=2; SQL> exec dbms_stats.gather_table_stats('sys', 't1', method_opt => 'for columns x size skewonly'); 搜集列x的直方图 SQL>select * from DBA_TAB_COL_STATISTICS where TABLE_NAME='T1'; SQL>select * from dba_histograms where table_name='T1' and column_name='X'; SQL> select * from t1 where x=1; 正确 SQL> select * from t1 where x=2; 正确 SQL> select count(distinct y) from t1; 超过254 SQL> select * from t1 where y like 'DBA%'; SQL> exec dbms_stats.gather_table_stats('sys', 't1', method_opt => 'for columns size auto y'); SQL>select * from DBA_TAB_COL_STATISTICS where TABLE_NAME='T1'; 创建等高直方图 select COLUMN_NAME, HISTOGRAM, NUM_BUCKETS from DBA_TAB_COLUMNS where TABLE_NAME='T1'; 共享游标shared curosr： SQL> conn / as sysdba SQL> create table t1 (x int); SQL> insert into t1 values (1); SQL> commit; SQL> alter system flush shared_pool; SQL> select * from t1; 查看父游标、子游标和执行计划： select sql_text, sql_id, plan_hash_value, version_count from v$sqlarea where sql_text like 'select * from t1'; select plan_hash_value, child_number from v$sql where sql_id='27uhu2q2xuu7r'; select * from v$sql_plan where plan_hash_value='3617692013'; SQL> conn hr/hr SQL> create table t1(x int primary key); SQL> insert into t1 values (1); SQL> commit; SQL> select * from t1; 再次查询父游标、子游标和执行计划：3个语句 SQL> select * from t1; 要求字面值完全一致 select sql_text, sql_id, plan_hash_value, version_count from v$sqlarea where sql_text like 'select * from %t1'; session cursor: SQL> show parameter open_cursors SQL> show parameter session_cached_cursors SQL> select * from t1; SQL> select distinct sid from v$mystat; SQL>select * from v$open_cursor where sid=33; 性能差异： SQL> create table t1(x int not null, y int); SQL> create index t1_x_idx on t1(x); SQL> insert into t1 select rownum, 11 from dual connect by level<=100; SQL> commit; SQL> exec dbms_stats.gather_table_stats('sys', 't1'); alter session set sql_trace=true; begin for i in 1..100 loop execute immediate 'select * from t1 where x='||i; end loop; end; / begin for i in 1..100 loop execute immediate 'select * from t1 where x=:x' using i; end loop; end; / alter session set sql_trace=false; bindingvariablepeeking和acs： SQL> create table t1 (x int not null, y int); SQL> create index t1_x_idx on t1(x); SQL> insert into t1 select 1, 11 from dual connect by level<=10000; SQL> insert into t1 values (2, 22); SQL> commit; SQL> exec dbms_stats.gather_table_stats('sys', 't1', method_opt=>'for all columns'); SQL>select * from DBA_TAB_COL_STATISTICS where TABLE_NAME='T1'; 搜集直方图 SQL> alter session set optimizer_features_enable='10.2.0.1'; SQL> alter system flush shared_pool; SQL> var x number; SQL> exec :x := 1 SQL> select * from t1 where x=:x; SQL> set pagesize 0 SQL> select * from table(dbms_xplan.display_cursor(null, null, 'advanced')); 查看父游标、子游标： select sql_text, sql_id, plan_hash_value, version_count, executions from v$sqlarea where sql_text like 'select * from t1 where x=%'; select plan_hash_value, child_number from v$sql where sql_id='8h3m8wg51m8nm'; select * from v$sql_plan where plan_hash_value='3617692013'; SQL> exec :x := 2 SQL> select * from t1 where x=:x; SQL> select * from table(dbms_xplan.display_cursor(null, null, 'advanced')); 错误 再次查看游标：3个语句 acs: SQL> conn / as sysdba 恢复优化器版本 SQL> alter system flush shared_pool; SQL> var x number; SQL> exec :x := 1 SQL> select * from t1 where x=:x; SQL> select * from table(dbms_xplan.display_cursor(null, null, 'advanced')); 查看游标：3个语句 SQL> exec :x := 2 SQL> select * from t1 where x=:x; SQL> select * from table(dbms_xplan.display_cursor(null, null, 'advanced')); 不变 SQL> select * from t1 where x=:x; SQL> select * from table(dbms_xplan.display_cursor(null, null, 'advanced')); 索引 查看游标：3个语句 latch和mutex： shared pool latch数量: select a.ksppinm, b.ksppstvl, a.ksppdesc from x$ksppi a, x$ksppsv b where a.indx=b.indx and a.ksppinm='_kghdsidx_count'; SQL> create table t1 as select rownum x from dual connect by level<=500000; SQL> exec dbms_stats.gather_table_stats('sys', 't1'); SQL> alter system flush shared_pool; SQL> exec dbms_workload_repository.create_snapshot() SQL> select distinct sid from v$mystat; 模拟硬解析： begin for i in 1..500000 loop execute immediate 'select * from t1 where x='||i; end loop; end; / 另一个session监控： SQL>select * from v$session_wait where sid=143; SQL> exec dbms_workload_repository.create_snapshot() em做awr的报表和addm，ash报表和挖掘drilldown mutex等待事件： SQL> alter system set memory_target=0; SQL> alter system set sga_target=0; SQL> alter system flush shared_pool; SQL> exec dbms_workload_repository.create_snapshot() 在两个session中同时执行： begin loop execute immediate 'alter system flush shared_pool'; for i in 1..1000 loop execute immediate 'select * from t1 where x='||i; end loop; end loop; end; / 另一个session监控： select * from v$session_wait where sid=143; SQL> exec dbms_workload_repository.create_snapshot() em做awr的报表和addm，ash报表和挖掘drilldown Latch:cache buffer chains SQL> create table t1(x int); SQL> insert into t1 values (1); SQL> commit; SQL>select distinct sid from v$mystat; SQL> exec dbms_workload_repository.create_snapshot() 两个session同时执行： declare v1 int; begin for i in 1..99999999 loop select count(*) into v1 from t1; end loop; end; / 第3个session中： SQL> select * from v$session_wait where sid in (136, 137); SQL> exec dbms_workload_repository.create_snapshot() Buffer busy waits 两个session中运行： declare v1 int; begin for i in 1..99999999 loop insert into t1 values (i); end loop; end; / 第3个session中： SQL> select * from v$session_wait where sid in (136, 137); SQL> exec dbms_workload_repository.create_snapshot() create table t1(x int, y char(1)); insert into t1 values (1, 'a'); insert into t1 values (2, 'a'); create index t1_x_idx on t1(x); exec dbms_stats.gather_table_stats('sys', 't1'); 为什么没用索引？ select x from t1; create table t1 as select 1 id, object_name from dba_objects; update t1 set id=2 where rownum<=1; commit; select id, count(*) from t1 group by id; create index t1_id_idx on t1(id); exec dbms_stats.gather_table_stats('sys', 't1', METHOD_OPT => 'FOR ALL COLUMNS size 1'); 为什没用索引？ select * from t1 where x=2; alter session set optimizer_mode='first_rows'; 客户抱怨响应时间长 select e.last_name, d.department_name from hr.employees e, hr.departments d where e.department_id=d.department_id; create table t1 (x int); create or replace procedure proc1 as begin for iin 1..100000 loop execute immediate 'insert into t1 values ('||i||')'; commit; end loop; end; / 客户抱怨运行时间长 begin proc1; end; / 1. 原始语句，动态sql，未使用绑定变量： drop table t1 purge; create table t1 (x int); create or replace procedure proc1 as begin for iin 1..100000 loop execute immediate 'insert into t1 values ('||i||')'; commit; end loop; end; / alter system flush shared_pool; set timing on exec proc1; select count(*) from t1; 44秒完成。 select sql_text, sql_id, parse_calls, executions from v$sql where lower(sql_text) like '%insert into t1 values%'; 动态sql灵活，处理ddl或dml的对象预先不存在的时候很方便，但在运行时才解析，性能差。 不使用绑定变量，每个语句都是解析一次，执行一次，效率差。 2. 改写，使用绑定变量： drop table t1 purge; create table t1 (x int); create or replace procedure proc1 as begin for i in 1..100000 loop execute immediate 'insert into t1 values (:x)' using i; commit; end loop; end; / alter system flush shared_pool; set timing on exec proc1; 12秒完成。 select sql_text, sql_id, parse_calls, executions from v$sql where lower(sql_text) like '%insert into t1 values%'; 使用绑定变量，解析1次，执行10万次。 3. 改写，使用静态sql： drop table t1 purge; create table t1 (x int); create or replace procedure proc1 as begin for i in 1..100000 loop insert into t1 values (i); commit; end loop; end; / alter system flush shared_pool; set timing on exec proc1; 10秒完成。 select sql_text, sql_id, parse_calls, executions from v$sql where lower(sql_text) like '%insert into t1 values%'; 静态sql自定使用绑定变量，解析1次，执行10万次。并且在编译过程中就解析好了。 4. 改写，批量提交： drop table t1 purge; create table t1 (x int); create or replace procedure proc1 as begin for i in 1..100000 loop insert into t1 values (i); end loop; commit; end; / alter system flush shared_pool; set timing on exec proc1; 5秒完成。 5. 改写，使用集合操作： drop table t1 purge; create table t1 (x int); insert into t1 select rownum from dual connect by level<=100000; 或者 create or replace procedure proc1 as TYPE dual_typ IS TABLE OF int INDEX BY PLS_INTEGER; dual_var dual_typ; begin SELECT rownum BULK COLLECT INTO dual_var FROMdual connect by level<=100000; forall i in dual_var.first .. dual_var.last insert into t1 values (dual_var(i)); END; / alter system flush shared_pool; set timing on exec proc1; 0.12秒完成 将一条条插入改为一批写入buffer的块里。 6. 改写，使用直接路径： drop table t1 purge; create table t1 as select rownum x from dual connect by level<=100000; 0.08秒 insert into先写内存再刷到磁盘，create table直接刷磁盘。 7. 改写，使用并行： drop table t1 purge; create table t1 nologging parallel 16 as select rownum x from dual connect by level<=100000; 1. 创建虚拟机 名称：node1_RAC_11gR2_rhel6u5_x64和node2_RAC_11gR2_rhel6u5_x64： 2.5-4g内存，引导：硬盘+cdrom，网卡1用hostonly，网卡2内部网络 主机名：node1.test.com和node2.test.com 网络： 第一块网卡改名：eth0，勾选自动连接 手动ip：192.168.0.1/24，网关：192.168.0.254，dns：192.168.0.1,192.168.0.2 手动ip：192.168.0.2/24，网关：192.168.0.254，dns：192.168.0.1,192.168.0.2 第二块网卡改名：eth2，勾选自动连接 手动ip：192.168.1.1/24 手动ip：192.168.1.2/24 时区：asia/shanghai 存储：use all space，review，删除/home，swap给4096MB，其他都给/ 安装包：desktop 2. 调整系统： 关闭防火墙： service iptables stop service ip6tables stop chkconfig iptables off chkconfig ip6tables off 管理工具中disabled防火墙 关闭selinux： # vi /etc/selinux/config SELINUX=disabled 配置yum： # rm -f /etc/yum.repos.d/* # vi /etc/yum.repos.d/rhel6.repo [Server] name=Server baseurl=file:///media/"RHEL_6.5 x86_64 Disc 1"/Server enabled=1 gpgcheck=0 安装vb增强功能： # yum -y install gcc kernel-devel # ln -s /usr/src/kernels/2.6.32-431.el6.x86_64/ /usr/src/linux 设备-->安装增强功能 右键eject弹出光盘 3. 硬件要求： 内存/swap/tmp/shared momory # vi /etc/fstab（永久修改） tmpfs /dev/shm tmpfs defaults,size=4G 0 0 # mount -o remount /dev/shm 临时修改 # mount -t tmpfs shmfs -o size=4g /dev/shm 4. 设置用户和目录： 用户：grid，oracle 群组：oinstall, asmadmin, asmdba, asmoper, dba, oper groupadd -g 1000 oinstall groupadd -g 1001 dba groupadd -g 1002 oper groupadd -g 1003 asmadmin groupadd -g 1004 asmdba groupadd -g 1005 asmoper useradd -u 1000 -g oinstall -G dba,oper,asmdba oracle useradd -u 1001 -g oinstall -G asmadmin,asmdba,asmoper grid mkdir -p /u01/app/grid mkdir -p /u01/app/11.2.0/grid mkdir -p /u01/app/oracle chown -R grid:oinstall /u01 chown oracle:oinstall /u01/app/oracle chmod -R 775 /u01 passwd grid passwd oracle 5. 设置userprofile文件： # vi ~grid/.bash_profile export ORACLE_SID=+ASM1 node2上改为+ASM2 export ORACLE_BASE=/u01/app/grid export ORACLE_HOME=/u01/app/11.2.0/grid export TNS_ADMIN=$ORACLE_HOME/network/admin export PATH=$PATH:$ORACLE_HOME/bin export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib export EDITOR=vi export export NLS_LANG=american_america.AL32UTF8 export NLS_DATE_FORMAT='yyyy-mm-dd hh34:mi:ss' umask 022 #vi ~grid/.bashrc alias sqlplus='rlwrap sqlplus' alias asmcmd='rlwrap asmcmd' # vi ~oracle/.bash_profile export ORACLE_SID=orcl1 node2上改为orcl2 export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1 export TNS_ADMIN=$ORACLE_HOME/network/admin export ORACLE_HOSTNAME=node1.test.com node2上改为node2.host.com export ORACLE_UNQNAME=orcl export PATH=$PATH:$ORACLE_HOME/bin export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib export NLS_LANG=american_america.AL32UTF8 export NLS_DATE_FORMAT='yyyy-mm-dd hh34:mi:ss' export EDITOR=vi export umask 022 #vi ~oracle/.bashrc alias sqlplus='rlwrap sqlplus' alias rman='rlwrap rman' 6. 修改资源限制： # vi /etc/security/limits.conf grid soft nofile 1024 grid hard nofile 65536 grid soft nproc 2047 grid hard nproc 16384 grid soft stack 10240 grid hard stack 32768 oracle soft nofile 1024 oracle hard nofile 65536 oracle soft nproc 2047 oracle hard nproc 16384 oracle soft stack 10240 oracle hard stack 32768 7. 修改内核参数： # vi /etc/sysctl.conf fs.aio-max-nr = 1048576 fs.file-max = 6815744 kernel.shmall = 2097152 kernel.shmmax = 2076053504 kernel.shmmni = 4096 kernel.sem = 250 32000 100 128 net.ipv4.ip_local_port_range = 9000 65500 net.core.rmem_default = 262144 net.core.rmem_max = 4194304 net.core.wmem_default = 262144 net.core.wmem_max = 1048576 # sysctl -p 8. 安装软件包： # yum -y install … binutils-2.20.51.0.2-5.11.el6 (x86_64) compat-libcap1-1.10-1 (x86_64) compat-libstdc++-33-3.2.3-69.el6 (x86_64) compat-libstdc++-33-3.2.3-69.el6.i686 gcc-4.4.4-13.el6 (x86_64) gcc-c++-4.4.4-13.el6 (x86_64) glibc-2.12-1.7.el6 (i686) glibc-2.12-1.7.el6 (x86_64) glibc-devel-2.12-1.7.el6 (x86_64) glibc-devel-2.12-1.7.el6.i686 ksh libgcc-4.4.4-13.el6 (i686) libgcc-4.4.4-13.el6 (x86_64) libstdc++-4.4.4-13.el6 (x86_64) libstdc++-4.4.4-13.el6.i686 libstdc++-devel-4.4.4-13.el6 (x86_64) libstdc++-devel-4.4.4-13.el6.i686 libaio-0.3.107-10.el6 (x86_64) libaio-0.3.107-10.el6.i686 libaio-devel-0.3.107-10.el6 (x86_64) libaio-devel-0.3.107-10.el6.i686 make-3.81-19.el6 sysstat-9.0.4-11.el6 (x86_64) elfutils-libelf-devel 安装rlwrap和bind /installation/grid/rpm/cvuqdisk-1.0.9-1.rpm（用scp复制到node2） 9. 配置网络： node1: public(eth0): 192.168.0.1/24 网关：192.168.0.254 private(eth2): 192.168.1.1/24 node1的virutal ip:192.168.0.11 node2: public(eth0): 192.168.0.2/24 网关：192.168.0.254 private(eth2): 192.168.1.2/24 node2的virutal ip:192.168.0.12 scan和scanvip：scan.test.com 192.168.0.101/102/103 # vi /etc/hosts #node1 192.168.0.1 node1.test.com node1 #public ip 192.168.1.1 node1-priv.test.com node1-priv #private ip 192.168.0.11 node1-vip.test.com node1-vip #node1 vip #node2 192.168.0.2 node2.test.com node2 #public ip 192.168.1.2 node2-priv.test.com node2-priv #private ip 192.168.0.12 node2-vip.test.com node2-vip #node2 vip node1配置主dns： # vi /etc/named.conf listen-on port 53 { any; }; listen-on-v6 port 53 { any; }; allow-query { any; }; dnssec-enable no; dnssec-validation no; # vi /etc/named.rfc1912.zones zone "test.com" IN { type master; file "test.com.hosts"; }; zone "0.168.192.in-addr.arpa" IN { type master; file "192.168.0.rev"; }; # vi /var/named/test.com.hosts $TTL 1D @ IN SOA node1.test.com. root.node1.test.com. ( 2016031601 3h 1h 1w 1h ) IN NS node1.test.com. IN NS node2.test.com. node1 IN A 192.168.0.1 node2 IN A 192.168.0.2 scan IN A 192.168.0.101 scan IN A 192.168.0.102 scan IN A 192.168.0.103 # vi /var/named/192.168.0.rev $TTL 1D @ IN SOA node1.test.com. root.node1.test.com. ( 1 3h 1h 1w 1h ) IN NS node1.test.com. IN NS node2.test.com. 1 IN PTR node1.test.com. 2 IN PTR node2.test.com. 101 IN PTR scan.test.com. 102 IN PTR scan.test.com. 103 IN PTR scan.test.com. # service named start # chkconfig --level 35 named on # nslookup 测试localhost/127.0.0.1/node1/192.168.0.1/node2/192.168.0.2/scan/192.168.0.101(102,103) node2配置辅助dns： # vi /etc/named.conf listen-on port 53 { any; }; listen-on-v6 port 53 { any; }; allow-query { any; }; dnssec-enable no; dnssec-validation no; # vi /etc/named.rfc1912.zones zone "test.com" IN { type slave; file "slaves/test.com.hosts"; masters {192.168.0.1;}; }; zone "0.168.192.in-addr.arpa" IN { type slave; file "slaves/192.168.0.rev"; masters {192.168.0.1;}; }; # service named start chkconfig --level 35 named on # nslookup - 192.168.0.2 测试localhost/127.0.0.1/node1/192.168.0.1/node2/192.168.0.2/scan/192.168.0.101(102,103) 10. ntp #service ntpd stop # chkconfig ntpd off # mv /etc/ntp.conf /etc/ntp.conf.bak 11. 配置共享存储 SAN/NAS ocr/voting disk: 3个1GB(+CRS) data: 2个10GB(+DATA) fra: 1个10GB(+FRA) 创建共享磁盘的子目录：/root/virtualbox vms/shared_disk 关闭node1/node2 node1添加6块磁盘（固定大小）： /root/virtualbox vms/shared_disk/asmdisk1.vdi vb将6块硬盘改为可共享 node2添加6块共享的磁盘 # ll /dev/sd* 执行命令： #for i in b c d e f g ; do echo "KERNEL==\"sd*\", BUS==\"scsi\", PROGRAM==\"/sbin/scsi_id --whitelisted --replace-whitespace --device=/dev/\$name\", RESULT==\"`/sbin/scsi_id --whitelisted --replace-whitespace --device=/dev/sd$i`\", NAME=\"asm-disk$i\", OWNER=\"grid\", GROUP=\"asmadmin\", MODE=\"0660\"" >> /etc/udev/rules.d/99-oracle-asmdevices.rules done # start_udev;ls /dev/asm* 确认生成asmdisk。 12. node1/node2设置临时共享 # mkdir /oracle; mount -t vboxsf oracle /oracle # cd /oracle/software/ # yum -y install rlwrap-0.42-1.el6.x86_64.rpm # cd /oracle/installation/grid/ # yum -y install cvuqdisk-1.0.9-1.rpm 13. node1安装gi # xhost + # su - grid $ cd /oracle/installation/grid/ $ ./runInstaller 高级安装，集群名称：test-cluster, scan name: scan.test.com，不配置gns，添加node2，配置ssh asm: 磁盘组名称：CRS，normal方式，搜索路径：/dev/asm*，使用b、c、d三块硬盘 14. 测试gi： # su - grid $ crsctl check crs $ crsctl stat res -t $ srvctl status asm 15. node1创建asm磁盘组： # su - grid $ asmca data: 2个10GB（normal） fra: 1个10GB（external） 16. node1上安装db： # su - oracle $ cd /oracle/installation/database/ $ ./runInstaller 只安装软件，rac方式，选择全部节点，oracle口令，ssh连接 17. node1上创建db： dbca，rac，数据库orcl，node1/node2，存储asm，data磁盘组，fra使用+FRA磁盘组，sampleschema，内存800MB，字符集al32utf8 问题： 查看数据库的字符集： SQL> select * from v$nls_parameters; 删除asm磁盘的头部信息： #dd if=/dev/zero of=/dev/sdb bs=1M count=1 手动建立ssh信任关系： node1/node2上 # su-grid $ mkdir ~/.ssh $ chmod 700 ~/.ssh $ ssh-keygen -t rsa $ ssh-copy-id 192.168.0.2 node1上 $ ssh-copy-id 192.168.0.1 node2上 ssh node1 date ssh node2 date ssh node1-priv date ssh node2-priv date 关闭自动挂载，避免桌面崩溃： chmod -x /usr/libexec/gvfs-gdu-volume-monitor vi /etc/init/oracle-ohasd.conf ohasd启动项 ps -ef | more has相关的进程 # /u01/app/11.2.0/grid/bin/crsctl stat res -init-t crs和ohas的资源 # /u01/app/11.2.0/grid/bin/crsctl stat res -t crs所管理的资源 两个实例连接db（通过scan-vip和node-vip两种方式），添加数据测试。 node1/node2: # su - oracle $ sqlplus / as sysdba SQL> select instance_name from v$instance; SQL> select name from v$database; $ sqlplus sys/password@scan.test.com:1521/orcl as sysdba 多创建连接 $ sqlplus sys/password@192.168.0.11:1521/orcl as sysdba $ sqlplus sys/password@192.168.0.12:1521/orcl as sysdba 启动/关闭crs和资源，在node1和node2上都要执行（root身份）： # /u01/app/11.2.0/grid/bin/crsctlstopcrs[-f] # /u01/app/11.2.0/grid/bin/crsctl start crs 启动/关闭资源，在一个节点上执行（root身份）： # /u01/app/11.2.0/grid/bin/crsctlstop cluster -all # /u01/app/11.2.0/grid/bin/crsctlstart cluster -all 查询资源（grid用户就可以）： $ crsctlstatres-t $ olsnodes -h 实例管理 启动关闭 sqlplus（oracle用户）/oem/srvctl（grid用户） 实例级别： # su - oracle $ sqlplus / as sysdba SQL> shutdown immediate # su - grid $ srvctl status instance -d orcl -i orcl1,orcl2 查看实例状态 $ srvctl stop instance -d orcl -i orcl1 -o immediate 关闭任意节点上实例 $ srvctl start instance -d orcl -i orcl1 数据库级别： $ srvctl status db -d orcl $ srvctl stop db -d orcl -o immediate $ srvctl start db -d orcl -o mount $ srvctl modify db -d orcl -s open $ srvctl config db -d orcl -a $ srvctl modify db -d orcl -y manual $ srvctl modify db -d orcl -y automatic asm实例 # su - grid $ ps -ef | grep asm* $ sqlplus / as sysasm SQL> startup|shutdown immediate 每个节点上单独执行 $ srvctl status asm $ srvctl stop asm -n node1 -o abort -f $ srvctl start asm -n node1 spfile 查看： # su - oracle $ sqlplus / as sysdba orcl1> show parameter spfile # su - grid $ asmcmd ASMCMD> cd +data/orcl # su - oracle $ sqlplus / as sysdba orcl1> create pfile='/home/oracle/pfile.ora' from spfile; $ vi /home/oracle/pfile.ora 修改： orcl1和orcl2： # su - oracle $ sqlplus / as sysdba orcl1> show parameter open_cursors orcl1> alter system set open_cursors=600; SQL> alter system set open_cursors=600 sid='*'; 相同 orcl2> show parameter open_cursors orcl1> alter system set open_cursors=800 sid='orcl2'; orcl2> show parameter open_cursors 恢复： orcl1> alter system reset open_cursors sid='orcl2'; orcl1> alter system reset open_cursors sid='*'; orcl1> alter system set open_cursors=300 sid='*'; 存储： SQL> show parameter control_files SQL> show parameter undo_tablespace SQL> select GROUP#, THREAD#, STATUS, MEMBERS from v$log; SQL> select GROUP#, MEMBER from v$logfile; SQL>ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 5 SIZE 50M; SQL>ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 6 SIZE 50M; # vi /etc/oracle/ocr.loc ocr位置 # cd /u01/app/11.2.0/grid/bin #./ocrcheck #./ocrconfig -showbackup 查看主节点上的自动备份 # ./ocrconfig -manualbackup 手动备份，root身份，保存在主节点 # ./ocrconfig-backuploc … 改备份路径 # ./ocrdump;vi OCRDUMPFILE # rm OCRDUMPFILE # ./ocrdump -h # ./ocrcheck -config # ./ocrconfig -add +DATA 镜像 # ./ocrcheck -config # cat /etc/oracle/ocr.loc # ./ocrconfig -delete +CRS 删除镜像 恢复： # ./ocrconfig -add +CRS # ./ocrconfig -delete +DATA olr： # ll /u01/app/11.2.0/grid/cdata/node1.olr # ./ocrcheck -local # ./ocrdump -local /root/node1.olr # vi /root/node1.olr # ./ocrconfig -local -manualbackup votingdisk： # ./crsctl query css votedisk public和private： #ifconfig或者ip add #oifcfg getif node vip和scanvip： # ip add $ sqlplus sys/password@192.168.0.1:1521/orcl as sysdba $ sqlplus sys/password@192.168.0.11:1521/orcl as sysdba $ sqlplus sys/password@192.168.0.101:1521/orcl as sysdba 假定101在node1上 # ifdown eth0 node1关闭网卡，观察ip的漂移 # ip add 或 $ crsctl stat res -t $ sqlplus sys/password@192.168.0.1:1521/orcl as sysdba $ sqlplus sys/password@192.168.0.11:1521/orcl as sysdba $ sqlplus sys/password@192.168.0.101:1521/orcl as sysdba # ifup eth0 node1开启网卡，观察ip的漂移 再次测试3个连接 listener: # su - grid $ lsnrctl status $ lsnrctl status listener_scan1/2/3 # su - oracle $ sqlplus / as sysdba SQL> show parameter listener 查看和监听相关的参数 $ srvctl relocate vip(scan) -h $ srvctl relocate scan -i 1 -n node1 private ip的自动管理（haip）： $ crsctl stat res -t -init 确认ohasd维护的haip基础服务 $ ifconfig $ oifcfg getif $ oifcfg iflist -p -n SQL> select name, ip_address from v$cluster_interconnects; 网络修改： 修改public hostname：重新安装rac 修改privatehostname：11.2.0.2以前重装rac，11.2.0.2以后在/etc/hosts随意改 修改public/privateip：相同网络，重启rac，不同的网络，修改orc 修改node vip/scan vip：使用srvctl修改 publiceth0: 192.168.0.1/24 à 172.16.0.1/16 private eth2: 192.168.1.1/24 à 10.0.0.0/8 node vip: 192.168.0.11/24 à 172.16.0.11/16 scan vip: 192.168.0.100(1 2) à 172.16.0.100(1 2)/16 修改publicip： # vi /etc/sysconfig/network-scripts/ifcfg-eth0 node1/node2的os中修改publicip node1上： # su - grid $ oifcfg getif $ oifcfg delif -global eth0 $ oifcfg setif -global eth0/172.16.0.0:public ASM=RAID+LVM SAME asm的实例： $ ps -ef | grep asm # su - grid $ sqlplus / as sysasm +ASM1> show parameter memory +ASM1> select component, current_size from v$sga_dynamic_components; +ASM1> show parameter listener 启动关闭实例： SQL> startup/shutdown abort 启动到nomount阶段停止 $ srvctl start asm $ srvctl stop asm -f SQL> show parameter diskgroup： 条带化RAID 0，镜像RAID1，RAID1+0 vb的node1添加3块1GB硬盘，共享给node2 # for i in h i j ; do echo "KERNEL==\"sd*\", BUS==\"scsi\", PROGRAM==\"/sbin/scsi_id --whitelisted --replace-whitespace --device=/dev/\$name\", RESULT==\"`/sbin/scsi_id --whitelisted --replace-whitespace --device=/dev/sd$i`\", NAME=\"asm-disk$i\", OWNER=\"grid\", GROUP=\"asmadmin\", MODE=\"0660\"" >> /etc/udev/rules.d/99-oracle-asmdevices.rules done # start_udev;ls /dev/asm* 确认生成asmdisk。 使用sqlplus/oem/asmca/asmcmd grid用户登录桌面，asmca创建diskgroup：testdg，normal方式。 # su - grid $ sqlplus / as sysasm +ASM1> select NAME, STATE, TOTAL_MB, FREE_MB, USABLE_FILE_MB from v$asm_diskgroup; $ asmcmd ASMCMD> lsdg # su - oracle $ sqlplus / as sysdba orcl1> create tablespace tbs01 datafile '+testdg' size 600M; orcl1> select path, failgroup, free_mb from v$asm_disk where group_number=4; orcl 1> select NAME, STATE, TOTAL_MB, FREE_MB, USABLE_FILE_MB from v$asm_diskgroup; ASMCMD> lsdsk -k 添加/删除磁盘： # su - grid +ASM1> show parameter asm_power_limit +ASM1> select name, total_mb, free_mb from v$asm_disk; +ASM1> alter diskgroup testdg add disk '/dev/asm-diskj'; +ASM1> select name, total_mb, free_mb from v$asm_disk; +ASM1> alter diskgroup testdg drop disk TESTDG_0002; +ASM1> select name, total_mb, free_mb from v$asm_disk; failgroup: +ASM1> select path, FAILGROUP from v$asm_disk where group_number=4; +ASM1> alter diskgroup testdg drop disk TESTDG_0002; +ASM1> alter diskgroup testdg add failgroup testdg_0000 disk '/dev/asm-diskj'; +ASM1> select name, total_mb, free_mb from v$asm_disk; oracle用户删除表空间，grid用户asmca删除testdg，root删除/etc/udev/rules.d/99-oracle-asmdevices.rules中最后3块硬盘 SQL> startup mount SQL> select FILE#, NAME from v$datafile; SQL> alter database datafile 4 offline; SQL> alter database open; SQL> drop tablespace tbs01 force; (including contents and datafiles) scanvip的HA nslookup解析scan返回3个ip，实现ha # su - grid $ srvctl status scan $ srvctl status scan_listener # su - oracle $ tnsping orcl # su - grid $ srvctl stop scan_listener -i 1(23) $ srvctl stop scan -i 1(23) 逐一关闭scanlistener和scanvip，测试客户端的ha # su - grid $ srvctl stop instance -d orcl -i orcl1 -o immediate # su - oracle $ sqlplus sys/password@orcl as sysdba SQL> select instance_name from v$instance; node vip的HA node1和node2上修改tnsnames本地解析 # su - oracle $ vi $ORACLE_HOME/network/admin/tnsnames.ora TESTHA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.11)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.12)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = orcl) ) ) $ sqlplus sys/password@testha as sysdba 用node2上的客户端测试 testha> select instance_name from v$instance; 始终连接到orcl1 # ifdown eth0 关闭node1的网卡 $ sqlplus sys/password@testha as sysdba 用node2上的客户端测试 testha> select instance_name from v$instance; 连接到orcl2 node1恢复eth0，客户端重新连接orcl1 scanvip的LB 建立多个session，自动分配给orcl1和orcl2 $ sqlplus sys/password@orcl as sysdba SQL> select instance_name from v$instance; nodevip的LB # su - oracle $ vi $ORACLE_HOME/network/admin/tnsnames.ora TESTLB = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.11)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.12)(PORT = 1521)) (LOAD_BALANCE = yes) ) (CONNECT_DATA = (SERVICE_NAME = orcl) ) ) 建立多个session，自动分配给orcl1和orcl2 $ sqlplus sys/password@testlb as sysdba SQL> select instance_name from v$instance; db name, instance name, global name, sid, service name, db_unique_name orcl db name orcl global name(service name) 查询现有服务： SQL> select name from v$database; SQL> select instance_name from v$instance; SQL> select name from v$services; $ vi tnsnames.ora; lsnrctl status # su - grid $ srvctl status service -d orcl 创建服务： # su - oracle $ srvctl add service -d orcl -s testsvc -r orcl1 -a orcl2 # su - grid $ srvctl start service -d orcl -s testsvc $ crsctl stat res -t $ lsnrctl status; lsnrctl listener_scan1 # su - oracle $ sqlplus sys/password@scan.test.com:1521/testsvc as sysdba SQL> select instance_name from v$instance; SQL> select name from v$database; netca添加testsvc的解析 SQL> shutdown immediate 关闭orcl1 $ sqlplus sys/password@scan.test.com:1521/testsvc as sysdba SQL> select instance_name from v$instance; 连接到orcl2 # su - grid $ crsctl stat res -t $ srvctl status service -d orcl -s testsvc $ srvctl start instance -d orcl -i orcl1 $ crsctl stat res -t testsvc还在orcl2上，不会自动failback $ srvctl relocate service -d orcl -s testsvc -i orcl2 -t orcl1 $ crsctl stat res -t $ srvctl stop service -d orcl -s testsvc $ srvctl remove service -d orcl -s testsvc resource manager/scheduler/sql trace ops 内存>网络>磁盘 node1： # su - oracle $ sqlplus / as sysdba orcl1> create tablespace tbs01; orcl1> create table t1 (x int, y int) tablespace tbs01; orcl1> insert into t1 values (1, 1); orcl1> insert into t1 values (2, 2); orcl1> commit; orcl1> select dbms_rowid.rowid_relative_fno(rowid), dbms_rowid.rowid_block_number(rowid) from t1; orcl1> alter system checkpint; orcl1> alter system flush buffer_cache; node1: begin for i in 1..10000 loop update t1 set y=i where x=1; end loop; end; / node2: begin for i in 1..10000 loop update t1 set y=i where x=2; end loop; end; / xcuràpiàcr add: node3做所有准备工作 node1上验证： #su - grid $ cluvfy stage -pre nodeadd -n node3 $ ./addNode.sh "CLUSTER_NEW_NODES={node3}" "CLUSTER_NEW_VIRTUAL_HOSTNAMES={node3-vip}" node3上root身份执行root.sh node1上运行dbca添加新实例 crsctl stat res -t node1和node2安装os，db软件，创建监听，node1上创建orcl数据库。 或者 vb复制单实例虚拟机，重新初始化网卡，将两个节点重命名为： node1_DG_11gR2_RHEL6u5_x64 node2_DG_11gR2_RHEL6u5_x64 node1恢复网络设置： # vi /etc/udev/rules.d/70-persistent-net.rules 删除2行eth0的信息，将eth2改为eth0 # vi /etc/sysconfig/network-scripts/ifcfg-eth0 替换原有的mac地址 #shutdown -h now node2恢复网络设置： # vi /etc/udev/rules.d/70-persistent-net.rules 删除2行eth0的信息，将eth2改为eth0 # vi /etc/sysconfig/network-scripts/ifcfg-eth0 替换原有的mac地址 #reboot oracle登录桌面，dbca删除orcl数据库 root身份修改hostname和ip： # vi /etc/sysconfig/network-scripts/ifcfg-eth0 ip改为192.168.0.2 # vi /etc/sysconfig/network 改为node2.test.com # hostname node2.test.com # vi /etc/hosts 192.168.0.1 node1.test.com node1 192.168.0.2 node2.test.com node2 临时关闭oracle服务（可选）： # chkconfig oracle off # chkconfig --list oracle oracle身份调整node2的环境： $ vi $ORACLE_HOME/network/admin/listener.ora 改为node2.test.com $ lsnrctl stop; lsnrctl start $ vi ~oracle/.bash_profile export ORACLE_HOSTNAME=node2.test.com export ORACLE_SID=orclps export ORACLE_UNQNAME=orclps $ . ~oracle/.bash_profile node1(primary)： # vi /etc/hosts # su - oracle SQL> shutdown immediate SQL> startup mount SQL> alter database archivelog; SQL> alter database open; SQL> alter database force logging; node1(primary)修改主数据库参数： SQL> alter system set log_archive_config='dg_config=(orcl,orclps)'; SQL> alter system set log_archive_dest_2='service=orclps async valid_for=(online_logfile,primary_role) db_unique_name=orclps'; 添加备用数据库参数： SQL> alter system set fal_server=orclps; SQL> alter system set fal_client=orcl; SQL> alter system set standby_file_management=auto; SQL> alter system set db_file_name_convert='/orclps/','/orcl/' scope=spfile; SQL> alter system set log_file_name_convert='/orclps/','/orcl/' scope=spfile; $ vi /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora ORCLPS = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = node2.test.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orclps) ) ) node2(ps): # su - oracle $ mkdir -p $ORACLE_BASE/fast_recovery_area/orclps $ mkdir -p $ORACLE_BASE/admin/orclps/adump $ mkdir -p $ORACLE_BASE/admin/orclps/dpdump $ mkdir -p $ORACLE_BASE/oradata/orclps $ vi $ORACLE_HOME/network/admin/listener.ora 添加静态注册 SID_LIST_listener= (SID_LIST= (SID_DESC= (GLOBAL_DBNAME=orclps) (SID_NAME=orclps) (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1))) $ lsnrctl reload; lsnrctl status $ vi $ORACLE_HOME/network/admin/tnsnames.ora ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = node1.test.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl) ) ) ORCLPS = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = node2.test.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orclps) ) ) $ tnsping orcl 测试 在node2上生成口令文件： $ scp node1:$ORACLE_HOME/dbs/orapworcl $ORACLE_HOME/dbs/orapworclps node1上生成pfile： SQL> create pfile from spfile; node2上生成spfile： $ scp node1:$ORACLE_HOME/dbs/initorcl.ora $ORACLE_HOME/dbs/initorclps.ora node2上删除orcl.开头的参数，修改如下参数： $ vi $ORACLE_HOME/dbs/initorclps.ora *.audit_file_dest='/u01/app/oracle/admin/orclps/adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='/u01/app/oracle/oradata/orclps/control01.ctl','/u01/app/oracle/fast_recovery_area/orclps/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_file_name_convert='/orcl/','/orclps/' *.db_name='orcl' *.db_unique_name='orclps' *.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area' *.db_recovery_file_dest_size=4322230272 *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclpsXDB)' *.fal_client='ORCLPS' *.fal_server='ORCL' *.log_archive_config='dg_config=(orcl,orclps)' *.log_archive_dest_2='service=orcl async valid_for=(online_logfile,primary_role) db_unique_name=orcl' *.log_file_name_convert='/orcl/','/orclps/' *.memory_target=1073741824 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.standby_file_management='AUTO' *.undo_tablespace='UNDOTBS1' SQL> create spfile from pfile; SQL> startup nomount $ rman target sys/password@orcl auxiliary sys/password@orclps RMAN> duplicate target database for standby from active database dorecover; 如果主和备的数据目录相同，需要加nofilenamecheck SQL> select status from v$instance; mount状态 如果需要手动启动备用数据库： SQL> startup nomount SQL> alter database mount standby database; node1(primary)创建srl： SQL> alter database add standby logfile '/u01/app/oracle/oradata/orcl/srl01(234).log' size 50M; node2(ps)创建srl： SQL> alter database add standby logfile '/u01/app/oracle/oradata/orclps/srl01(234).log' size 50M; node2(ps)开启redoapply： SQL> alter database recover managed standby database using current logfile disconnect; SQL> select name from v$datafile; SQL> select name from v$tempfile; SQL> select name from v$controlfile; SQL> select member from v$logfile; SQL> select protection_mode, protection_level from v$database; SQL> select sequence#, applied from v$archived_log; SQL> alter system switch logfile; node1上切换日志 SQL> select sequence#, applied from v$archived_log; 出现新的归档 $ vi /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora 删除静态注册 删除init参数文件（可选） node2(ps)修改启动脚本： # vi /etc/init.d/oracle #!/bin/bash #chkconfig:35 99 01 case "$1" in start) su - oracle -c "sqlplus /nolog" < conn / as sysdba startup mount alter database recover managed standby database using current logfile disconnect; EOF su - oracle -c "lsnrctl start" >/dev/null touch /var/lock/subsys/oracle ;; stop) su - oracle -c "lsnrctl stop" >/dev/null su - oracle -c "sqlplus /nolog" < conn / as sysdba recover managed standby database cancel; shutdown immediate EOF rm -f /var/lock/subsys/oracle ;; *) echo "Usage: oracle {start|stop}" exit 1 esac # chkconfig --level 35 oracle on 或者 #chkconfig --add oracle node1(primary)准备工作： SQL> create table t1(x int); 测试代码： node1(primary): SQL> insert into t1 values (1); SQL> commit; node2(standby) SQL> select status, sequence#, block# from v$managed_standby where client_process='LGWR'; 或者在adg下检查 SQL> recover managed standby database cancel; SQL> alter database open; SQL> alter database recover managed standby database using current logfile disconnect; SQL> select * from t1; 最大性能： nod1(primary) SQL> alter system set log_archive_dest_2='service=orclps async valid_for=(online_logfile,primary_role) db_unique_name=orclps'; SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE; SQL> select protection_mode, protection_level from v$database; 测试 最大可用性： node1(primary) SQL> alter system set log_archive_dest_2='service=orclps sync affirm net_timeout=30 valid_for=(online_logfile,primary_role) db_unique_name=orclps'; SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY; SQL> select protection_mode, protection_level from v$database; 测试 最大保护： node1(primary) SQL> alter system set log_archive_dest_2='service=orclps sync affirm valid_for=(online_logfile,primary_role) db_unique_name=orclps'; SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PROTECTION; SQL> select protection_mode, protection_level from v$database; 测试 逐级修改保护模式，不需要重启db，performanceàavailablity要等待resync完成，再àprotection 恢复为最大性能： SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE; SQL> alter system set log_archive_dest_2='service=orclps async valid_for=(online_logfile,primary_role) db_unique_name=orclps'; 只读模式打开 node1(primary) SQL> create table t1(x int); insert into t1 values (1); commit; node2(standby) SQL> select open_mode from v$database; 确认是mount SQL> recover managed standby database cancel; SQL> alter database open; SQL> select open_mode from v$database; read only SQL> select * from t1; SQL> insert into t1 values (2); commit; node1上做 SQL> select * from t1; 和node1不同步 SQL> delete t1; 报错 SQL> alter system switch logfile; node1切换日志 SQL> select sequence#, applied from v$archived_log; 传输但不应用 SQL> shutdown immediate SQL> startup mount SQL> alter database recover managed standby database using current logfile disconnect; SQL> select sequence#, applied from v$archived_log; 应用node1的日志 快照备用 node1(primary) SQL> create table t1(x int); insert into t1 values (1); commit; node2(standby) SQL> show parameter db_recovery_file_dest SQL> recover managed standby database cancel; SQL> shutdown immediate SQL> startup mount SQL> alter database convert to snapshot standby; SQL> alter database open; SQL> select open_mode from v$database; read write $ ls /u01/app/oracle/fast_recovery_area/ORCLPS/flashback SQL> select * from t1; SQL> insert into t1 values (1); commit; node1修改 SQL> select * from t1;insert into t1 values (2);commit; 无法看到node1修改，但自己可以修改 SQL> alter system switch logfile; node1切换日志 SQL> select sequence#, applied from v$archived_log; 传输但不应用 SQL> shutdown immediate SQL> startup mount SQL> alter database convert to physical standby; node2丢失更改 SQL> shutdown immediate SQL> startup mount SQL> alter database recover managed standby database using current logfile disconnect; SQL> select sequence#, applied from v$archived_log; node2应用日志 快照备用+flashbackdb node1(primary) SQL> create table t1(x int); insert into t1 values (1); commit; node2(standby) 确认数据库在mount状态 SQL> show parameter db_recovery_file_dest SQL> recover managed standby database cancel; SQL> alter database flashback on; SQL> alter database convert to snapshot standby; SQL> alter database open; SQL> select open_mode from v$database; read write SQL> insert into t1 values(2); commit; 导入测试数据 SQL> create restore point before_test GUARANTEE flashback database; SQL> delete t1;commit; SQL> shutdown immediate SQL> startup mount SQL> flashback database to restore point before_test; SQL> alter database open resetlogs; SQL> select * from t1; SQL> shutdown immediate SQL> startup mount SQL> alter database convert to physical standby; node2丢失更改 SQL> shutdown immediate SQL> startup mount SQL> alter database recover managed standby database using current logfile disconnect; SQL> drop restore point before_test; ADG node1(primary) SQL> create table t1(x int); insert into t1 values (1); commit; node2(standby) SQL> select open_mode from v$database; 确认是mount SQL> recover managed standby database cancel; SQL> alter database open; 或者SQL> startup SQL> alter database recover managed standby database using current logfile disconnect; SQL> insert into t1 values (2);commit; node1上修改 SQL> select * from t1; node2上查看实时数据 node1(primary)的准备： fal_server/fal_client/standby_file_management 创建srl node2(standby) log_archive_dest_2/log_archive_config node1(primary)确认日志传输完整（nogap） SQL>SELECT STATUS, GAP_STATUS FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID = 2; node2(standby)确认两个lag SQL> SELECT NAME, VALUE, DATUM_TIME FROM V$DATAGUARD_STATS; node1(primary) SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; 应该是TO STANDBY SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN; SQL> SHUTDOWN ABORT; node2(standbyànew primary) SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; 应该是TO PRIMARY SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN; SQL> ALTER DATABASE OPEN; node1(primaryànew standby) SQL> STARTUP MOUNT; SQL> alter database recover managed standby database using current logfile disconnect; node2(primary)和node1(standby)开启flashback node2(primary)准备： SQL>create table t1(x int); insert into t1 values (1); commit; node2(primary)模拟故障： # ifdown eth0 node2(primary)关闭网络 SQL> insert into t1 values (2); commit; SQL> shutdown abort node1(standby) SQL> recover managed standby database cancel; SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH; SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; SQL>ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN; SQL> ALTER DATABASE OPEN; 利用flashback恢复node2(primaryànew standby) node1(new primary) SQL> SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FROM V$DATABASE; node2(new standby) SQL> startup mount SQL> FLASHBACK DATABASE TO SCN 1275936; SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY; SQL> SHUTDOWN IMMEDIATE SQL> STARTUP MOUNT SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT; 基于oracle的ATM实例 11.2.0.3.0à 11.2.0.4.0 # mkdir /patch # chmod 777 /patch # mount -t vboxsf patch /patch $ unzip p13390677_112040_Linux-x86-64_1(2)of7.zip 停服务： $ emctl stop dbconsole $ lsnrctl stop $ sqlplus / as sysdba SQL> shutdown immediate 改环境： $ vi ~/.bash_profile export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4/db_1 $ . .bash_profile 桌面环境需要重新登录 $ echo $ORACLE_HOME $ ./runInstaller 选择upgrade，路径确认改为/u01/app/oracle/product/11.2.0.4/db_1 # /u01/app/oracle/product/11.2.0.4/db_1/root.sh 提示以root执行脚本 netca：创建新版本listener和service解析 dbua: 不移动文件 SQL> select * from v$version; db的版本 SQL> select comp_name, version from dba_server_registry; 组件的版本 $ rm -rf /u01/app/oracle/product/11.2.0 确定成功，删除老版本 11.2.0.4.0à 11.2.0.4.8 $ $ORACLE_HOME/OPatch/opatch version $ unzip p6880880_112000_Linux-x86-64.zip -d $ORACLE_HOME 必须用oracle用户身份 $ $ORACLE_HOME/OPatch/opatch version $ cd /patch $ unzip p21352635_112040_Linux-x86-64\(11.2.0.4.8_db\).zip 必须用oracle用户解压缩 $ cd 21352635 $ emctl stop dbconsole; dbshut $ORACLE_HOME 关闭db所有组件 $ $ORACLE_HOME/OPatch/opatch apply 不输入email，确认继续 $ dbstart $ORACLE_HOME; emctl start dbconsole SQL> @?/rdbms/admin/catbundle.sql psu apply 检查： $ $ORACLE_HOME/OPatch/opatch lspatches SQL> select action, comments from registry$history; 在node2上的/etc/oratab中添加数据库 $ vi /etc/oratab orclps:/u01/app/oracle/product/11.2.0/db_1:Y node2上不配置em 在node1上选择"只安装软件" 复制老版本路径下的listener.ora, sqlnet.ora, tnsnames.ora spfileorcl.ora orapworcl至新版本对应目录 安装软件失败： $ vi /u01/app/oraInventory/ContentsXML/inventory.xml 升级gi： node1和node2： # chown grid /u01/app $ su - grid $ vi .bash_profile export ORACLE_HOME=/u01/app/11.2.0.4/grid $ . .bash_profile node1： 桌面root登录 #xhost + $ su - grid $ /patch/grid/runInstaller upgrade gi & asm，测试ssh连接（不用配置），路径改为/u01/app/11.2.0.4/grid 在node1和node2上运行rootupgrade.sh $ crsctl query crs activeversion 确认新版本 升级dbsoftware： node1和node2： # su - oracle $ vi .bash_profile export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4/db_1 $ . .bash_profile node1： $ ./runInstaller 选择upgrade existing database，选中node1/node2，ssh输入口令，路径改为/u01/app/oracle/product/11.2.0.4/db_1，node1/node2执行root脚本，dbua升级数据库。 升级psu： node1和node2： su - grid unzip p6880880_112000_linux-x86-64.zip -d $ORACLE_HOME $ORACLE_HOME/OPatch/opatch version su - oracle unzip p6880880_112000_linux-x86-64.zip -d $ORACLE_HOME $ORACLE_HOME/OPatch/opatch version node1： su - grid $ unzip p21523375_112040_Linux-x86-64(11.2.0.4.8_gi&db).zip node1和node2： su - oracle emctl stop dbconsole node1和node2上，root生成ocm响应文件，应用psu： # cd /patch/21523375/ #/u01/app/11.2.0.4/grid/OPatch/ocm/bin/emocmrsp -no_banner #/u01/app/11.2.0.4/grid/OPatch/opatch auto /patch/21523375/ -ocmrf /patch/21523375/ocm.rsp node1上： su - oracle sqlplus / as sysdba SQL> @?/rdbms/admin/catbundle.sql psu apply 检查： $ORACLE_HOME/OPatch/opatch lspatches sqlplus / as sysdba select * from v$version; select action,comments from registry$history;rman恢复目录
flashback
flashbackquery
flashback version query
flashback table
flashback drop
flashback transaction query
flashback database
移动数据
sqlloader
外部表
优化
自动化维护
准备工作
AWR
serveralert
ADDM
ASH
AMM
SAA
STA
SQL优化
优化器
执行计划
优化器操作
full table scan
INDEX的使用方式
多表连接
统计信息
基本视图
直方图
绑定变量和共享游标
sharedpool
buffercache
优化实例
RAC
安装
体系结构
集群管理
ocr和votingdisk
网络
ASM
HA和LB
service
cache fusion
add/delete node
DG
安装
保护模式
active data guard
角色转换
switch over
failover
数据库设计
升级
单实例
DG
RAC
