oracle学习笔记

发布时间:2020-07-26 06:16:41 作者:小虾米216
来源:网络 阅读:2402

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.comip使用dhcpswap4GB,其余给/

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

图形界面创建dboracle执行:

$ 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   sidorcl

测试,保存。

 

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,取消dhcplinuxonly

虚拟机网络改为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 sysdbaSQL> 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;                []<>()都可以

whereorderby

数字条件:

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

 

casedecode

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;

 

左外连接:107106+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;

 

右外连接:122106+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;

 

完全外连接:123106+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)

(使用oraclesql99的语法)

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';

 

innotinnull值的影响:

所有管理者的姓名:

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_namesalary,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);

 

deletetruncate

                                     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管理

体系结构

                                              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

数据库

oracle学习笔记

启动实例:

$ 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 mountalter 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>startupalter database open;

SQL> select status from v$instance;

SQL>select * from hr.employees;

SQL> conn hr/hr

oracle学习笔记

关闭数据库:

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_limitprocesses参数。并在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

存储管理

oracle学习笔记

查询预定义表空间:

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;           成功

dmlddl的区别

改变表空间大小:

resizeautoextendadddatafile

删除表空间:

SQL> drop tablespace tbs01 including contents and datafile;

Oracle NET

oracle学习笔记

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端口的监听

客户端配置

oracle学习笔记

轻松连接:

$ 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,所以此时一个用户进程的请求可以由多个服务进程来完成.

oracle学习笔记

共享服务器的配置通过初始化参数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

 

externalos)验证:

操作系统中创建用户:

$ 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

角色就是数据库中的群组!

角色的作用:简化权限的管理,动态更新用户的权限。

oracle学习笔记

 

 

预定义的角色:

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

oracle学习笔记

开启开关参数:

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

撤销管理

什么是撤销数据:

oracle学习笔记

         1.交易的回退:没有提交的交易可以rollback

         2.交易的恢复:数据库崩溃时,将磁盘的不正确数据恢复到交易前

         3.读一致性  :被查询的记录有事务占用,转向回滚段找改前镜像

         4.闪回数据  :从回滚段中构造历史数据

事务与撤销数据:

oracle学习笔记

redoundo

oracle学习笔记

AUM

3个参数,两个表空间属性

 

undo_management=AUTO 回滚表空间段的段管理模式,管理员只需要备足够的表空间容量,oracle会自动管理扩展回滚段的数量。只能使用一个UNDO表空间。

undo_tablespace:只有在自动管理模式下才可以使用。指明使用哪个UNDO表空间

undo_retention900

         提交之后旧的镜像保持在回滚段中的时间。

         非强制的回退保持时间.(回滚空间不足老的镜像就会被覆盖)

 

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

调度作业

oracle学习笔记

$ 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配置

oracle学习笔记

$ 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;               记录timescn

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;               timescn更新

脚本形式:

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恢复

数据文件

oracle学习笔记

 

oracle学习笔记

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;              systemundotbs不能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<<EOF

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包隔离数据块

rman恢复目录

SQL> show parametercontrol_file_record_keep_time

 

dbca创建数据库rc(不配置emfra200M内存,字符集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

flashback

功能                            依赖组件                   相关参数                            典型错误

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

flashbackquery

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

flashback version query

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;            物理结构改变,查询失败

flashback table

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;                失败

flashback drop

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

flashback transaction query

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';

flashback database

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;                   消失

移动数据

sqlloader

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

AWR

em中查看基本设置

em中生成和查看awr报表

$ ll /u01/app/oracle/product/11.2.0/db_1/rdbms/admin/awr*.sql

serveralert

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中查看警告信息。

ADDM

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;

ASH

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

AMM

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下,sgapga不需要设置

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                     ammasmm都有一对参数

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_targetmemory_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;

SAA

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

STA

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分析

 

oracle学习笔记

SQL优化

优化器

参考: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);

被改写为多表连接

 

CBORBO的区别:

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_rowsfirst_rows对执行计划的影响:

SQL> alter session set optimizer_mode=first_rowsall_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);

hrsession:

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;

sqlplussql 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;

优化器操作

full table scan

SQL> select * from hr.employees;

扫描高水标记以下的所有块

查询的比例、物理顺序、表小、没有索引、并行

selectivitycardinality参考: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的使用方式

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排序

合并empdept

 

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;

 

bindingvariablepeekingacs

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个语句

sharedpool

latchmutex

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()

 

emawr的报表和addmash报表和挖掘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()

emawr的报表和addmash报表和挖掘drilldown

buffercache

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;

/

 

3session中:

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;

/

3session中:

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灵活,处理ddldml的对象预先不存在的时候很方便,但在运行时才解析,性能差。

不使用绑定变量,每个语句都是解析一次,执行一次,效率差。

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;

RAC

安装

1.         创建虚拟机

名称:node1_RAC_11gR2_rhel6u5_x64node2_RAC_11gR2_rhel6u5_x64

2.5-4g内存,引导:硬盘+cdrom,网卡1hostonly,网卡2内部网络

主机名:node1.test.comnode2.test.com

网络:

第一块网卡改名:eth0,勾选自动连接

手动ip192.168.0.1/24,网关:192.168.0.254dns192.168.0.1,192.168.0.2

手动ip192.168.0.2/24,网关:192.168.0.254dns192.168.0.1,192.168.0.2

第二块网卡改名:eth2,勾选自动连接

手动ip192.168.1.1/24

手动ip192.168.1.2/24

时区:asia/shanghai

存储:use all spacereview,删除/homeswap4096MB,其他都给/

安装包: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.         设置用户和目录:

用户:gridoracle

群组: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

安装rlwrapbind

/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

node1virutal ip:192.168.0.11

node2:

public(eth0): 192.168.0.2/24  网关:192.168.0.254

private(eth2): 192.168.1.2/24

node2virutal ip:192.168.0.12

 

scanscanvipscan.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: 31GB(+CRS)

data: 210GB(+DATA)

fra: 110GB(+FRA)

创建共享磁盘的子目录:/root/virtualbox vms/shared_disk

关闭node1/node2

node1添加6块磁盘(固定大小):

/root/virtualbox vms/shared_disk/asmdisk1.vdi

vb6块硬盘改为可共享

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: 磁盘组名称:CRSnormal方式,搜索路径:/dev/asm*,使用bcd三块硬盘

14.     测试gi

# su - grid

$ crsctl check crs

$ crsctl stat res -t

$ srvctl status asm

15.     node1创建asm磁盘组:

# su - grid

$ asmca

data: 210GBnormal

fra: 110GBexternal

16.     node1上安装db

# su - oracle

$ cd /oracle/installation/database/

$ ./runInstaller

只安装软件,rac方式,选择全部节点,oracle口令,ssh连接

17.     node1上创建db

dbcarac,数据库orclnode1/node2,存储asmdata磁盘组,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              crsohas的资源

# /u01/app/11.2.0/grid/bin/crsctl stat res -t                     crs所管理的资源

两个实例连接db(通过scan-vipnode-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和资源,在node1node2上都要执行(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

 

实例管理

启动关闭

sqlplusoracle用户)/oem/srvctlgrid用户)

实例级别:

# 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

修改:

orcl1orcl2

# 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;

ocrvotingdisk

# 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

网络

publicprivate

#ifconfig或者ip add

#oifcfg getif

node vipscanvip

# 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 假定101node1

# 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

修改privatehostname11.2.0.2以前重装rac11.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/node2os中修改publicip

node1上:

# su - grid

$ oifcfg getif

$ oifcfg delif -global eth0

$ oifcfg setif -global eth0/172.16.0.0:public

ASM

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,镜像RAID1RAID1+0

vbnode1添加31GB硬盘,共享给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创建diskgrouptestdgnormal方式。

# 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删除testdgroot删除/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)

HALB

scanvipHA

nslookup解析scan返回3ip,实现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)

逐一关闭scanlistenerscanvip,测试客户端的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 vipHA

node1node2上修改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

 

scanvipLB

建立多个session,自动分配给orcl1orcl2

$ sqlplus sys/password@orcl as sysdba

SQL> select instance_name from v$instance;

 

nodevipLB

# 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,自动分配给orcl1orcl2

$ sqlplus sys/password@testlb as sysdba

SQL> select instance_name from v$instance;

service

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

cache fusion

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/delete node

add:

node3做所有准备工作

node1上验证:

#su - grid

$ cluvfy stage -pre nodeadd -n node3

$ ./addNode.sh "CLUSTER_NEW_NODES={node3}" "CLUSTER_NEW_VIRTUAL_HOSTNAMES={node3-vip}"

node3root身份执行root.sh

node1上运行dbca添加新实例

crsctl stat res -t

DG

安装

node1node2安装osdb软件,创建监听,node1上创建orcl数据库。

或者

vb复制单实例虚拟机,重新初始化网卡,将两个节点重命名为:

node1_DG_11gR2_RHEL6u5_x64

node2_DG_11gR2_RHEL6u5_x64

 

node1恢复网络设置:

# vi /etc/udev/rules.d/70-persistent-net.rules         删除2eth0的信息,将eth2改为eth0

# vi /etc/sysconfig/network-scripts/ifcfg-eth0          替换原有的mac地址

#shutdown -h now

 

node2恢复网络设置:

# vi /etc/udev/rules.d/70-persistent-net.rules         删除2eth0的信息,将eth2改为eth0

# vi /etc/sysconfig/network-scripts/ifcfg-eth0          替换原有的mac地址

#reboot

 

oracle登录桌面,dbca删除orcl数据库

 

root身份修改hostnameip

# 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" <<EOF >/dev/null

        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" <<EOF >/dev/null

        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;

测试

 

逐级修改保护模式,不需要重启dbperformanceà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';

active data guard

只读模式打开

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上查看实时数据

角色转换

switch over

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;

 

failover

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;

数据库设计

基于oracleATM实例

升级

单实例

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:创建新版本listenerservice解析

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;

DG

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

 

RAC

升级gi

node1node2

# 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

node1node2上运行rootupgrade.sh

$ crsctl query crs activeversion        确认新版本

 

升级dbsoftware

node1node2

# 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/node2ssh输入口令,路径改为/u01/app/oracle/product/11.2.0.4/db_1node1/node2执行root脚本,dbua升级数据库。

 

升级psu

node1node2

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

 

node1node2

su - oracle

emctl stop dbconsole

 

node1node2上,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;

 


推荐阅读:
  1. Unity学习笔记
  2. AWK学习笔记

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

oracle 学习 笔记

上一篇:生成树原理

下一篇:activiti部署、执行,完成简单示例

相关阅读

您好,登录后才能下订单哦!

密码登录
登录注册
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》