溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
1. 安裝virtualbox：
# yum install gcc kernel-devel kernel-headers
# yum install virtualbox-5....
# /etc/init.d/vboxdrv setup 手動編譯內核模塊，安裝時自動完成
# usermod -G vboxusers root
2. 安裝linux：
創建虛擬機：
名稱：oracle11gR2_RHEL6.4_x64
類型：linux 64bit
內存：2048MB
硬盤大小：100GB
設置：
啟動順序：硬盤，網絡
網絡：網卡1，橋接eth0
去除聲音、usb設備
安裝：desktop方式
主機名：node1.test.com，ip使用dhcp，swap4GB，其余給/
3. 調整linux系統：
關閉防火墻：
# service iptables stop
# service ip6tables stop
# chkconfig iptables off
# chkconfig ip6tables off
管理工具中disabled防火墻
關閉selinux：
# vi /etc/selinux/config
SELINUX=disabled
配置yum：
# rm -f /etc/yum.repos.d/*
# wget ftp://172.16.8.100/rhel6.repo -P /etc/yum.repos.d/
或者：
# vi /etc/yum.repos.d/rhel6.repo
[Server]
name=Server
baseurl=file:///media/"RHEL_6.5 x86_64 Disc 1"/Server
enabled=1
gpgcheck=0
安裝vb增強功能：
# yum -y install gcc kernel-devel
# ln -s /usr/src/kernels/2.6.32-431.el6.x86_64/ /usr/src/linux
設備-->安裝增強功能
右鍵eject彈出光盤
4. 確認root身份:
# id
5. 硬件配置：
# grep MemTotal /proc/meminfo
# grep SwapTotal /proc/meminfo
# vi /etc/fstab（永久修改）
tmpfs /dev/shm tmpfs defaults,size=2G 0 0
# mount -o remount /dev/shm
臨時修改
# mount -t tmpfs shmfs -o size=2g /dev/shm
# uname -m
# df -h
6. rpm檢查并安裝：
# cat /etc/redhat-release
# uname -r
# rpm -qa | grep glibc
# yum install glibc
# yum install glibc.i686
binutils-2.20.51.0.2-5.11.el6 (x86_64)
compat-libcap1-1.10-1 (x86_64)
compat-libstdc++-33-3.2.3-69.el6 (x86_64)
compat-libstdc++-33-3.2.3-69.el6.i686
gcc-4.4.4-13.el6 (x86_64)
gcc-c++-4.4.4-13.el6 (x86_64)
glibc-2.12-1.7.el6 (i686)
glibc-2.12-1.7.el6 (x86_64)
glibc-devel-2.12-1.7.el6 (x86_64)
glibc-devel-2.12-1.7.el6.i686
ksh
libgcc-4.4.4-13.el6 (i686)
libgcc-4.4.4-13.el6 (x86_64)
libstdc++-4.4.4-13.el6 (x86_64)
libstdc++-4.4.4-13.el6.i686
libstdc++-devel-4.4.4-13.el6 (x86_64)
libstdc++-devel-4.4.4-13.el6.i686
libaio-0.3.107-10.el6 (x86_64)
libaio-0.3.107-10.el6.i686
libaio-devel-0.3.107-10.el6 (x86_64)
libaio-devel-0.3.107-10.el6.i686
make-3.81-19.el6
sysstat-9.0.4-11.el6 (x86_64)
7. 創建用戶：
# groupadd -g 1000 oinstall
# groupadd -g 1001 dba
# groupadd -g 1002 oper
# useradd -u 1000 -g oinstall -G dba,oper oracle
# passwd oracle
8. 修改內核參數：
# vi /etc/sysctl.conf
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 536870912
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
# sysctl -p
9. 修改資源限制：
# vi /etc/security/limits.conf
oracle soft nofile 1024
oracle hard nofile 65536
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft stack 10240
oracle hard stack 32768
10. 創建目錄：
# mkdir -p /u01/app/oracle
# chown -R oracle:oinstall /u01
# chmod -R 775 /u01
11. 修改概要文件：
# vi ~oracle/.bash_profile
export ORACLE_SID=orcl
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export ORACLE_HOSTNAME=node1.test.com
export ORACLE_UNQNAME=orcl
export PATH=$PATH:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
export NLS_LANG=american_america.AL32UTF8
export NLS_DATE_FORMAT='yyyy-mm-dd hh34:mi:ss'
export EDITOR=vi
export
12. 使用hosts文件解析主機名：
# hostname
node1.test.com
# vi /etc/hosts
192.168.0.1 node1.test.com node1
# ping node1
# ping node1.test.com
13. 解壓縮安裝包：
# cd /installation
# unzip p10404530_112030_Linux-x86-64_[12]of7.zip
或者：
在物理主機中解壓縮2個安裝包，然后共享給虛擬機，命令如下：
# mkdir /database
# mount -t vboxsf database /database
14. 圖形界面安裝：
# xhost +
# su - oracle
$ cd /database/
$ ./runInstaller
略
以root身份執行兩個腳本
15. 創建監聽和數據庫：
圖形界面下創建監聽，oracle執行：
$ netca
$ lsnrctl status
$ netstat -tlnp | grep 1521
圖形界面創建db，oracle執行：
$ dbca
16. 測試：
sqlplus測試：
$ sqlplus sys/sys@orcl as sysdba
$sqlplus/as sysdba
SQL> show user
SQL> select count(*) from hr.employees;
SQL> exit
瀏覽器測試：
https://192.168.0.1:1158/em
sys/password sysdba
安裝rlwrap：
root身份安裝
# yum install rlwrap-0.42-1.el6.x86_64.rpm
# vi ~oracle/.bashrc
alias sqlplus='rlwrap sqlplus'
alias rman='rlwrap rman'
# su - oracle
$ sqlplus / as sysdba
root身份安裝
# yum install flash-plugin-11.2.202.508-release.x86_64.rpm
sqldeveloper測試：
root身份安裝
# yum install jdk-8u51-linux-x64.rpm
# yum install sqldeveloper-4.1.1.19.59-1.noarch.rpm
# /usr/local/bin/sqldeveloper
輸入jdk的路徑 /usr/java/jdk1.8.0_51/
點左上角+號，創建新連接：
sys/password，連接類型：basic，角色：sysdba
192.168.0.1端口1521 sid：orcl
測試，保存。
17. 添加啟動腳本：
# vi /etc/oratab 把N改為Y
orcl:/u01/app/oracle/product/11.2.0/db_1:Y
# vi /etc/rc.d/init.d/oracle
#!/bin/bash
#chkconfig:35 99 01
case "$1" in
start)
echo -n "Starting Oracle Database& Listener:"
su - oracle -c 'dbstart $ORACLE_HOME' >/dev/null
echo "[ ok ]"
echo -n "Starting Oracle EM dbconsole:"
su - oracle -c "emctl start dbconsole" >/dev/null
echo "[ ok ]"
touch /var/lock/subsys/oracle
;;
stop)
echo -n "Shutting Down Oracle EM dbconsole:"
su - oracle -c "emctl stop dbconsole" >/dev/null
echo "[ ok ]"
echo -n "Shutting Down Oracle Database& Listener:"
su - oracle -c 'dbshut $ORACLE_HOME' >/dev/null
echo "[ ok ]"
rm -f /var/lock/subsys/oracle
;;
*)
echo "Usage: oracle {start|stop}"
exit 1
esac
# chmod 755 /etc/rc.d/init.d/oracle
# chkconfig --add oracle
# chkconfig --list oracle
18. 刪除口令有效期：
#su - oracle
$ sqlplus / as sysdba
SQL> alter profile default limit PASSWORD_LIFE_TIME unlimited;
19. 修改virtualbox的網絡設置（可選）：
全局設置à添加hostonly網絡，設置ip為：192.168.0.253，取消dhcp（linuxonly）
虛擬機網絡改為hostonly
虛擬機中將網卡的ip改為靜態，192.168.0.1
# vi /etc/sysconfig/network-scripts/ifcfg-eth0
BOOTPROTO=none
IPADDR=192.168.0.1
PREFIX=24
# ifdown eth0;ifup eth0
# vi /etc/hosts
192.168.0.1 node1.test.com node1
1. 查看hr用戶名下的表，解鎖hr用戶：
$ sqlplus / as sysdba或SQL> conn / as sysdba
SQL> show user
SQL> select table_name from dba_tables where owner='HR';
SQL> select * from hr.employees;
SQL> alter user hr account unlock identified by hr;
$ sqlplus hr/hr或者SQL> conn hr/hr
SQL> show user
SQL> select * from tab;
SQL> desc employees
練習：
查看scott用戶名下的表，解鎖scott用戶：
scott/tiger
2. 使用sqlplus的全屏編輯功能：
$ echo $EDITOR
SQL>select * from hr.employees;
SQL> ed
SQL> / 執行
3. 基礎select語句：
SQL> select * from employees;
SQL> desc employees
SQL> select LAST_NAME, SALARY, COMMISSION_PCT from employees;
SQL> desc departments
SQL> select department_id, department_name from departments;
SQL> select distinct DEPARTMENT_ID from employees;
SQL> select last_name, salary*12*(1+commission_pct) total_salary, department_id from employees;
SQL> select first_name||', '||last_name from employees;
SQL> select first_name||', '||last_name fullname from employees;
練習：
輸出下列員工信息：
Eleni(first_name) Zlotkey(last_name) employeeid is ... at department .. total salary is …
4. 使用連字符構造語句：
SQL> select table_name from user_tables;
SQL> select 'grant select on hr.'||table_name||' to scott;' from user_tables;
SQL> spool /home/oracle/grant.sql
SQL> set head off 去除標題
SQL> set feed off 去除回饋
SQL> select 'grant select on hr.'||table_name||' to scott;' from user_tables;
SQL> spool off
$ vi /home/oracle/grant.sql 手動去除沒用的行，相當于除去標題和回饋，
SQL> @/home/oracle/grant.sql 執行sql腳本
5. 單引號的處理：
SQL> select 'I'm teaher' from dual;
ERROR:
ORA-01756: quoted string not properly terminated
SQL> select 'I''m teaher' from dual;
SQL> select q'{I'm teaher}' from dual; []<>()都可以
數字條件：
SQL> select salary from employees where employee_id=100;
字符串大小寫敏感：
SQL> select last_name, salary from employees where last_name='King';
SQL>select table_name, tablespace_name from user_tables where table_name='EMPLOYEES';
日期是格式敏感：
SQL> alter session set nls_date_format='RR-Mon-dd';
SQL> select last_name from employees where hire_date='2006-05-23';
SQL> select last_name from employees where hire_date=to_date('2006-05-23', 'yyyy-mm-dd');
區間查詢：
SQL> select last_name from employees where salary>=3400 and salary<=4000;
SQL> select last_name from employees where salary between 3400 and 4000;
SQL> select last_name from employees where salary between 3000 and 5000 and department_id=50;
in：
SQL> select last_name from employees where department_id=30 or department_id=40 or department_id=50;
SQL> select last_name from employees where department_id in (30, 40, 50);
通配符：
SQL> select last_name, job_id from employees where job_id like '%\_MAN' escape '\';禁止轉義符后面的符號的特殊含義，like _通配某一個字符，like %通配某些字符
null作為條件：
SQL> select last_name from employees where commission_pct is null;
SQL> select last_name from employees where commission_pct is not null;
and/or/not：
SQL> select last_name, job_id, department_id, salary from employees where job_id='SA_REP' or department_id=50 and salary>=8000;
SQL> select last_name, job_id, department_id, salary from employees where (job_id='SA_REP' or department_id=50) and salary>=8000;
排序：
SQL> select last_name, salary from employees order by salary;升序
SQL> select last_name, salary from employees order by salary desc;降序
SQL> select last_name, salary from employees order by last_name;
SQL> select last_name, hire_date from employees order by hire_date;
SQL> select last_name, salary, commission_pct from employees order by salary desc, commission_pct desc;
SQL> select last_name, salary*12*(1+commission_pct) from employees order by 2;
SQL> select last_name, salary*12*(1+commission_pct) total_salary from employees order by total_salary;
練習：
選擇部門30中的雇員
列出所有職員(CLERK)的姓名、編號和部門
找出薪金大于5000的雇員
找出獎金高于0.1的雇員
找出部門50中的所有員工和部門30中的經理的詳細資料
找出收取獎金的雇員的不同工作職位每種職位顯示一次
找出不收取獎金或收取的工資低于5000的雇員
顯示last_name不帶有'R'的雇員姓名
select last_name name from employees where not last_name like '%R%'；
顯示所有雇員的姓名、工作和薪金，按工作的降序順序排序，而工作相同時按薪金升序
SQL> select upper(first_name), lower(last_name), length(last_name) from employees;
SQL> select (sysdate-hire_date)/7 from employees;
SQL> select trunc((sysdate-hire_date)/30, 0) from employees;
SQL> select trunc(months_between(sysdate,hire_date), 0) from employees;
SQL> select sysdate+3650 from dual;
SQL> select add_months(sysdate, 120) from dual;
SQL> select next_day('2015-09-01', 'friday') from dual;
SQL> select next_day('2015-10-01', 6) from dual;
SQL> select last_day(sysdate) from dual;
SQL> select round(to_date('2015-10-10','yyyy-mm-dd'), 'MONTH') from dual;
SQL> select round(to_date('2015-10-16','yyyy-mm-dd'), 'MONTH') from dual;
SQL> select round(to_date('2015-10-10','yyyy-mm-dd'), 'YEAR') from dual;
SQL> select round(sysdate, 'DAY') from dual;
練習：
找出各月最后三天內受雇的所有雇員
extract(month from hire_date+4) != extract(month from hire_date)
找出早于25年之前受雇的雇員
months_between(sysdate, hire_date)/300>=25
顯示正好為6個字符的雇員姓名
length(last_name)=6
顯示所有雇員的姓名的前三個字符
substr(last_name, 1, 3)
顯示所有雇員的姓名，用a替換所有'A'
replace(last_name, 'A', 'a')
SQL> select to_char(salary, '$999,999.00') from employees;
SQL> select last_name, to_char(hire_date, 'dd-Mon-RR') from employees;
SQL> select to_char(sysdate, 'yyyy-mm-dd hh34:mi:ss') from dual;
SQL> select to_char(sysdate, 'yyyy-mm-dd hh:mi:ss AM') from dual;
SQL> select last_name from employees where hire_date=to_date('2006-05-23', 'yyyy-mm-dd');
SQL> select to_number('$123,456.78', '$999,999.00') from dual;
練習：
查詢2006年入職員工：
select last_name
from employees
where hire_date between to_date('2006-01-01', 'yyyy-mm-dd')
and to_date('2006-12-31', 'yyyy-mm-dd');
select last_name
from employees
where to_char(hire_date, 'yyyy')='2006';
select last_name
from employees
where extract(year from hire_date)=2006;
--不推薦
select last_name
from employees
where hire_date like '2006%';
查詢歷年9月份入職的員工：
select last_name
from employees
where to_char(hire_date, 'mm')='09';
select last_name from employees where extract(month from hire_date)=9;
其他函數：
nvl：
nvl(val1, val2)
if val1 is not null
then
return val1;
else
return val2;
SQL> select last_name, salary*12*(1+nvl(commission_pct, 0)) total_salary from employees;
練習：
顯示所有員工部門編號，沒有部門的顯示“未分配部門”
select nvl(to_char(department_id),'未分配部門') from employees
case和decode：
IT_PROG +1000
SA_REP+1500
ST_CLERK +2000
其他人工資不變
select salary+1000 from employees where job_id='IT_PROG';
select last_name, job_id, salary,
case job_id
when 'IT_PROG' then salary+1000
when 'SA_REP' then salary+1500
when 'ST_CLERK' then salary+2000
else salary
end new_salary
from employees;
select last_name, job_id, salary,
decode( job_id,
'IT_PROG', salary+1000,
'SA_REP', salary+1500,
'ST_CLERK', salary+2000,
salary) new_salary
from employees;
練習：
按照員工工資，對員工分級顯示：
A 20001-25000
B 15001-20000
C 10001-15000
D 5001-10000
E 0-5000
答案：
select last_name,salary,
decode(trunc(salary/5000,0),
0,'E',
1,'D',
2,'C',
3,'B',
4,'A',
salary) n_sal
from employees；
答案拓展：
select last_name,salary,
decode(trunc(salary/5000,0),
0,'E',
1,'D',
2,'C',
3,'B',
4,'A',salary) n_sal
from employees
order by salary desc,n_sal
SQL> select count(*), sum(salary), avg(salary), min(salary), max(salary) from employees;
SQL> create table t1(x int);
SQL> insert into t1 values (null);
SQL> insert into t1 values (1);
SQL> commit;
SQL> select count(*) from t1;
SQL> select count(x) from t1;
SQL> select max(x) from t1;
SQL> select min(x) from t1;
SQL> select sum(x) from t1;
SQL> select avg(x) from t1;
SQL> select avg(salary), avg(nvl(commission_pct, 0)) from employees;
SQL> select count(distinct department_id) from employees; 去除重復值
Groupby分組：
SQL> select department_id, avg(salary) from employees group by department_id;
多列分組：
SQL> select department_id, job_id, max(salary) from employees group by department_id, job_id;
SQL> select department_id, job_id, max(salary), last_name from employees group by department_id, job_id; 錯誤語法
練習：
公司中不同職位的數量
select job_id,count(job_id)
from employees
group by job_id；
計算每個部門的人數
select department_id,count(last_name)
from employees
group by department_id
select department_id,count(department_id)
from employees
group by department_id
按年份分組，求員工的工資總和
select to_char(hire_date,'yyyy') year,sum(salary)
from employees
group by to_char(hire_date,'yyyy')
order by year
selec extract(year from hire_date) year,sum(salary)
from employees
group by extract(year from hire_date)
order by year
Having語句：
SQL> select department_id, avg(salary) from employees where avg(salary)>=5000 group by department_id; 錯誤語句
SQL> select department_id, avg(salary) from employees group by department_id having avg(salary)>=5000;
練習：
按部門求出所有有部門的普通員工的平均工資，部門平均工資少于5000的不顯示，最終結果按平均工資的降序排列。
select department_id,trunc(avg(salary)) avg_sal
from employees
where department_id is not null
group by department_id
having trunc(avg(salary)) >= 5000
order by avg_sal desc
select department_id, avg(salary) avg_sal
from employees
where job_id not like '%\_MGR' escape '\' and department_id is not null
group by department_id
having avg(salary)>=5000
order by avg_sal desc;
emp： dept:
empno ename deptno deptno dname
100 abc 10 10 sales
101 def 10 20 market
102 xyz 20 30 it
103 opq null
for emp in 100 .. 103
for dept in 10 .. 30
emp.deptno=dept.deptno
100 abc 10 10 sales
101 def 10 10 sales
102 xyz 20 20 market
訂單表:
CustID StoreID ProdID ChannelID
100 S100 P100 C100
客戶表：
CustID name creditlevel
100 abc
地址表：
CustID adress
100 bj
100 tj
獲取如下信息，準備工作：
employees:
員工總數：107
SQL> select count(*) from employees;
有部門的員工數：106
SQL> select count(*) from employees where department_id is not null;
SQL> select count(department_id) from employees;
沒有部門的員工數：1
SQL> select count(*) from employees where department_id is null;
departments：
部門總數：27
SQL> select count(*) from departments;
有員工的部門數：11
SQL> select count(distinct department_id) from employees;
沒有員工的部門數：16
SQL> select count(*) from departments where department_id not in (select department_id from employees where department_id is not null);
for dept in 1..27
for emp in 1..107
dept.deptid不在emp表中出現
select count(*)
from employees e, departments d
where e.department_id(+)=d.department_id
and e.employee_id is null;
select count(*)
from departments d
where not exists
(select 1 from employees where department_id=d.department_id);
select (select count(*) from departments)-(select count(distinct department_id) from employees) from dual;
內連接：106(106, 11)
select e.last_name, d.department_name
from employees e, departments d
where e.department_id=d.department_id;
select e.last_name, d.department_name
from employees e join departments d on e.department_id=d.department_id;
左外連接：107（106+1）
select e.last_name, d.department_name
from employees e, departments d
where e.department_id=d.department_id(+);
select e.last_name, d.department_name
from departments d, employees e
where e.department_id=d.department_id(+);
select e.last_name, d.department_name
from employees e left outer join departments d
on e.department_id=d.department_id;
右外連接：122（106+16）
select e.last_name, d.department_name
from employees e, departments d
where e.department_id(+)=d.department_id;
select e.last_name, d.department_name
from employees e right outer join departments d
on e.department_id=d.department_id;
完全外連接：123（106+1+16）
select e.last_name, d.department_name
from employees e full outer join departments d
on e.department_id=d.department_id;
多表連接的擴展：
n張表連接：
select e.last_name, d.department_name, l.city
from employees e, departments d, locations l
where e.department_id=d.department_id
and d.location_id=l.location_id;
select e.last_name, d.department_name, l.city
from employees e join departments d on e.department_id=d.department_id
join locations l on d.location_id=l.location_id;
select e.last_name, d.department_name, l.city
from employees e, departments d, locations l
where e.department_id=d.department_id(+)
and d.location_id=l.location_id(+);
select e.last_name, d.department_name, l.city
from employees e left outer join departments d on e.department_id=d.department_id
left outer join locations l on d.location_id=l.location_id;
練習：
查詢所有員工姓名，部門名稱，部門所屬城市(city)，國家(country)和區域(region)名稱，對于空值用“無”代替。(N/A)
（使用oracle和sql99的語法）
select e.last_name, d.department_name, l.city, c.country_name, r.region_name
from employees e, departments d, locations l, countries c, regions r
where e.department_id=d.department_id(+)
and d.location_id=l.location_id(+)
and l.country_id=c.country_id(+)
and c.region_id=r.region_id(+);
select e.last_namee.last_name, d.department_name, l.city, c.country_name, r.region_name
from employees e
left outer join departments d on e.department_id=d.department_id
left outer join locations l on d.location_id=l.location_id
left outer join countries c on l.country_id=c.country_id
left outer join regions r on c.region_id=r.region_id;
自連接：
empid ename mgrid
100 abc
101 def 100
102 xyz 100
emp: mgr:
empid ename mgrid empid mgrname
100 abc 100 abc
101 def 100
102 xyz 100
101 def 100 100 abc
102 xyz 100 100 abc
select emp.ename, mgr.mgrname
from emp, mgr
where emp.mgrid=mgr.empid
emp: mgr:
empid ename mgrid empid ename mgrid
100 abc 100 abc
101 def 100 101 def 100
102 xyz 100 102 xyz 100
select e.last_name, m.last_name
from employees e, employees m
where e.manager_id=m.employee_id;
有經理的員工數：106
SQL> select count(*) from employees where manager_id is not null;
沒有經理的員工數：1
SQL> select count(*) from employees where manager_id is null;
練習：
顯示所有員工姓名和經理姓名，沒有經理的顯示“無”。
select e.last_name, nvl(m.last_name, 'N/A')
from employees e, employees m
where e.manager_id=m.employee_id(+);
不等值連接：
conn scott/tiger
select e.ename, sg.grade
from emp e, salgrade sg
where e.sal between sg.losal and sg.hisal;
練習：
找出工資大于所在部門平均工資的員工姓名。
create table avg_sal_dept as select department_id, avg(salary) avg_sal from employees where department_id is not null group by department_id;
select e.last_name, e.salary, asd.avg_sal
from employees e, avg_sal_dept asd
where e.department_id=asd.department_id
and e.salary>asd.avg_sal;
select e.last_name, e.salary, asd.avg_sal
from employees e, (select department_id, avg(salary) avg_sal from employees where department_id is not null group by department_id) asd
where e.department_id=asd.department_id
and e.salary>asd.avg_sal;
單行子查詢的思路：
SQL> select salary from employees where last_name='Feeney';
SQL> select last_name from employees where salary>3000;
SQL> select last_name from employees where salary>(select salary from employees where last_name='Feeney');
多行子查詢的思路：
SQL> select distinct department_id from employees where department_id is not null;
SQL> select department_name from departments where department_id in (10, 20,30);
SQL> select department_name from departments where department_id in (select department_id from employees where department_id is not null);
用多表連接改寫：
select distinct d.department_name
from employees e, departments d
where e.department_id=d.department_id
for dept in 1..27
for emp in 1..107
查看emp中是否出現deptid
練習：
工資大于全公司平均工資的員工姓名。
SQL> select last_name from employees where salary>(select avg(salary) from employees);
和Feeney同年入職的員工姓名
select last_name, hire_date
from employees
where extract(year from hire_date)=
(select extract(year from hire_date) from employees where last_name='Feeney')
and last_name != 'Feeney';
select last_name, hire_date
from employees
where hire_date between
(select to_date(to_char(hire_date, 'yyyy')||'0101', 'yyyymmdd') from employees where last_name='Feeney')
and
(select to_date(to_char(hire_date, 'yyyy')||'1231', 'yyyymmdd') from employees where last_name='Feeney')
在Seattle工作的所有員工姓名
select last_name
from employees
where department_id in
(select department_id from departments
where location_id=
(select location_id from locations where city='Seattle'));
查找符合下列條件的員工姓名：和Abel在同一個部門，工資比Olson高
select last_name from employees
where department_id=
(select department_id from employees where last_name='Abel')
and salary >
(select salary from employees where last_name='Olson');
配對子查詢：
和Feeney在同一個部門、做同一職位的員工姓名：
select last_name, department_id, job_id
from employees
where department_id=
(select department_id from employees where last_name='Feeney')
and job_id=
(select job_id from employees where last_name='Feeney')
and last_name != 'Feeney';
select last_name, department_id, job_id
from employees
where (department_id, job_id)=
(select department_id, job_id from employees where last_name='Feeney')
and last_name != 'Feeney';
in和notin受null值的影響：
所有管理者的姓名：
SQL> select last_name from employees where employee_id in (select manager_id from employees);
所有普通員工的姓名：
SQL> select last_name from employees where employee_id not in (select manager_id from employees where manager_id is not null);
關聯子查詢：
工資大于所在部門平均工資的員工姓名。
for i in 1..107所有員工
{
select avg(salary) from employees where department_id=i.department_id
if i.salary > i所在部門的平均工資
保留此記錄
}
select last_name，salary,department_id
from employees outer
where salary >
(select avg(salary) from employees
where department_id = outer.department_id)
order by department_id;
select e.last_name, e.salary, asd.avg_sal
from employees e, (select department_id, avg(salary) avg_sal from employees where department_id is not null group by department_id) asd
where e.department_id=asd.department_id
and e.salary>asd.avg_sal;
exists/not exists查詢：
for i in 1..27所有部門
{
forjin1..107所有員工
{
if i.department_id = j.department_id
保留此記錄
break
}
}
select department_name
from departments outer
where exists
(select 1 from employees where department_id=outer.department_id);
select department_name
from departments outer
where not exists
(select 1 from employees where department_id=outer.department_id);
練習：
所有管理者的姓名：
for i in 1..107所有員工
{
forjin1..107所有員工
{
if i.employee_id = j.manager_id
保留此記錄
break
}
}
select last_name
from employees outer
where exists
(select 1 from employees where manager_id=outer.employee_id);
select last_name
from employees
where employee_id in
(select manager_id from employees);
select last_name
from employees,(select distinct(manager_id) from employees)asd
where employee_id=asd.manager_id
所有普通員工的姓名：
select last_name
from employees outer
where not exists
(select 1 from employees where manager_id=outer.employee_id);
select last_name
from employees
where employee_id not in(select manager_id from employees where manager_id is not null);
子查詢和多表連接的轉換：
有員工的部門的名稱
select department_name
from departments
where department_id in
(select department_id from employees);
select department_name
from departments outer
where exists
(select 1 from employees where department_id=outer.department_id);
select distinct d.department_name
from employees e, departments d
where e.department_id=d.department_id;
練習：
在Seattle工作的所有員工姓名（使用子查詢和多表連接兩種方式）
select last_name
from employees
where department_id in
(select department_id from departments
where location_id=
(select location_id from locations where city='Seattle'));
select e.last_name
from employees e, departments d, locations l
where e.department_id=d.department_id
and d.location_id=l.location_id
and l.city='Seattle';
最大值查詢：
SQL> select last_name from employees where salary=(select max(salary) from employees);
top-N查詢：
SQL> select last_name, salary from employees where rownum<=3 order by salary desc;
SQL> select * from (select last_name, salary from employees order by salary desc) where rownum<=3;
分頁查詢：
SQL> select * from
(select * from
(select * from
(select last_name, salary from employees order by salary desc)
where rownum<=6)
order by salary)
where rownum<=3
order by salary desc;
SQL> select last_name, salary
from (select rownum row_num, v1.* from
(select last_name, salary from employees order by salary desc) v1
) v2
where row_num between 4 and 6;
select * from
(select rownum num,last_name,salary from
(select last_name,salary from employees order by salary desc))
where num between 4 and 6
select last_name, salary
from (select rownum row_num, v1.*
from
(select last_name, salary from employees order by salary desc) v1
where rownum<=6
) v2
where row_num >= 4;
select employee_id, job_id from employees
union all
select employee_id , job_id from job_history;
select employee_id, job_id from employees
union
select employee_id, job_id from job_history;
select employee_id, job_id from employees
intersect
select employee_id, job_id from job_history;
select employee_id from employees
minus
select employee_id from job_history;
select employee_id, job_id, salary from employees
union all
select employee_id, job_id, null from job_history;
select employee_id, job_id, to_char(salary) from employees
union all
select employee_id, job_id, 'no salary' from job_history;
集合排序：
select employee_id, job_id, salary from employees
union all
select employee_id, job_id, null from job_history
orderbysalary;
select employee_id, job_id, null from job_history
union all
select employee_id, job_id, salary from employees
orderby 3;
insert:
SQL> create table t1(x int, y char(1), z date);
SQL> insert into t1(x, y, z) values (1, 'a', sysdate);
SQL> insert into t1(x, z, y) values (2, sysdate+1, 'b');
SQL> insert into t1(x, y, z) values (1, null, sysdate);
SQL> insert into t1(x, z) values (2, sysdate+1);
SQL> insert into t1 values (1, null, sysdate);
SQL> create table my_emp as select * from employees;
SQL> create table my_emp as select last_name, salary from employees where department_id=50;
SQL> create table avg_sal as select department_id, avg(salary) avg_sal from employees group by department_id;
SQL> create table my_emp as select * from employees where 1=0;
SQL> insert into my_emp select * from employees;
update:
SQL> update my_emp set salary=salary*1.1;
SQL> update my_emp set salary=salary*1.1 where department_id=50;
SQL> update my_emp set salary=salary*1.1, commission_pct=0.5 where employee_id=197;
delete:
SQL> delete from my_emp where employee_id=197;
SQL> delete from my_emp where department_id=50;
SQL> delete from my_emp;
子查詢：
SQL> create table my_emp as select * from employees;
SQL> alter table my_emp add(department_name varchar2(30));
SQL> update my_emp outer set department_name=(select department_name from departments where department_id=outer.department_id);
update (select t1.department_name as aname,t2.department_name bname from my_emp t1 ,departments t2 where t1.department_id=t2.department_id) set aname=bname;
練習：
在new_dept表中刪除沒有員工的部門
SQL> create table my_dept as select * from departments;
delete from my_dept outer
where not exists
(select 1 from my_emp
where department_id=outer.department_id);
delete和truncate：
delete truncate
語句類型 dml ddl
undo數據 產生大量undo數據 不產生undo數據
空間管理不釋放 釋放
語法 where 刪除全部數據
字符串：
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
$ 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中。
過程包括：查詢、添加、修改員工。函數包括：顯示員工全名。
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：
$ netstat -tlnp | grep 1158
$ echo $ORACLE_SID
orcl
$ echo $ORACLE_UNQNAME
orcl
如果沒有設置ORACLE_UNQNAME：
$ export ORACLE_UNQNAME=orcl
或
$ vi .bash_profile
export ORACLE_UNQNAME=orcl
$ . .bash_profile
確保主機名正常解析：
$ cat /etc/hosts
$ emctl status dbconsole
$ emctl stop dbconsole
$ emctl start dbconsole
查看oem的端口：
$ find /u01 -name portlist.ini
重新配置em：
$ emca
$ emca -deconfig dbcontrol db
$ emca -config dbcontrol db
啟動實例：
$ ls $ORACLE_HOME/dbs/spfileorcl.ora
SQL> shutdown immediate
SQL>startup nomount
SQL> select status from v$instance;
SQL> select * from v$sgainfo;
SQL>select * from hr.employees;
SQL> conn hr/hr
SQL>startup mount或alter database mount;
SQL> show parameter control_files
SQL> select status from v$instance;
SQL> select * from v$controlfile;
SQL> select name from v$datafile;
SQL>select * from hr.employees;
SQL> conn hr/hr
SQL>startup或alter database open;
SQL> select status from v$instance;
SQL>select * from hr.employees;
SQL> conn hr/hr
關閉數據庫：
SQL> shutdown normal
SQL> shutdown transactional
SQL> shutdown immediate
SQL> shutdown abort
控制腳本：
$ vi /etc/oratab
$ vi $ORACLE_HOME/bin/dbstart
$ vi $ORACLE_HOME/bin/dbshut
重啟數據庫：
SQL> startup force
startup force = shutdown abort + startup
startup force mount = shutdown abort + startup mount
練習：
熟悉3個服務的啟動和關閉
參數文件：
$ ls $ORACLE_HOME/dbs/spfileorcl.ora
預先備份：
$ cp spfileorcl.ora spfileorcl.ora.bak
不可以通過vi直接修改！
查看參數：
SQL> show parameter
SQL> select NAME, VALUE from v$parameter;
修改參數：
動態參數：
$ strings $ORACLE_HOME/dbs/spfileorcl.ora|grep reource_limit
SQL> show parameter RESOURCE_LIMIT
SQL> alter system set RESOURCE_LIMIT=true;
SQL> show parameter RESOURCE_LIMIT
$ strings $ORACLE_HOME/dbs/spfileorcl.ora|grep reource_limit
靜態參數：
$ strings $ORACLE_HOME/dbs/spfileorcl.ora|grep processes
*.processes=150
SQL> show parameter processes 值為150
SQL> alter system set processes=300 scope=spfile;
SQL> show parameter processes 值為150
$ cat $ORACLE_HOME/dbs/spfileorcl.ora
*.processes=300
重啟數據庫，靜態參數修改生效：
SQL> shutdown immediate
SQL> startup
SQL> show parameter processes 值為300
session級別修改參數：
SQL> select sysdate from dual;
SQL> alter session set nls_date_format='dd-mon rr';
SQL>select sysdate from dual; 只影響當前會話，不影響系統
SQL> conn / as sysdba
SQL>select sysdate from dual; 恢復默認設置
pfile/spfile格式轉換：
SQL> create pfile='/home/oracle/initorcl.ora' from spfile;
$ cat /home/oracle/initorcl.ora
$ mv $ORACLE_HOME/dbs/spfileorcl.ora $ORACLE_HOME/dbs/spfileorcl.ora.bak
SQL> shutdown immediate
SQL> create spfile from pfile='/home/oracle/initorcl.ora';
$ strings $ORACLE_HOME/dbs/spfileorcl.ora
指定參數文件啟動數據庫：
SQL> startup pfile='/home/oracle/initorcl.ora'
練習：
修改resource_limit和processes參數。并在alertlog中找到對應的信息。
$ find /u01 -name alert_*.log
$ vi /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log
SQL> show parameter dump
練習：
查看alertlog中最近一次啟動/關閉實例的詳細信息。
查看alertlog中記錄的用戶對參數所做的修改。
SQL> conn hr/hr
SQL> desc user_tables
SQL> select TABLE_NAME from user_tables;
SQL> desc user_views
SQL> select VIEW_NAME from user_views;
SQL> desc user_indexes
SQL> select INDEX_NAME, TABLE_NAME from user_indexes;
SQL> conn scott/tiger
SQL> select TABLE_NAME from user_tables;
SQL> select VIEW_NAME from user_views;
SQL> select INDEX_NAME, TABLE_NAME from user_indexes;
SQL> conn hr/hr
SQL> select count(*) from all_tables;
SQL> conn scott/tiger
SQL> select count(*) from all_tables;
SQL> conn hr/hr
SQL> select count(*) from dba_tables;
SQL> conn scott/tiger
SQL> select count(*) from dba_tables;
SQL> desc v$instance
SQL> desc v$database
查詢預定義表空間：
SQL> select TABLESPACE_NAME,CONTENTS from dba_tablespaces;
SQL> select FILE_NAME, TABLESPACE_NAME from dba_data_files;
創建新表空間：
SQL> create tablespace tbs01 datafile '/u01/app/oracle/oradata/orcl/tbs01.dbf'SIZE 10MAUTOEXTEND ON NEXT 10M MAXSIZE 100M;
在指定的表空間中創建表：
SQL> create table t1 tablespace tbs01 as select * from dba_objects where 1=0;
SQL> select bytes, blocks, extents, tablespace_name from dba_segments where segment_name='T1';
SQL> insert into t1 select * from dba_objects;
SQL> select bytes, blocks, extents, tablespace_name from dba_segments where segment_name='T1';
SQL> select extent_id, bytes, blocks from dba_extents where segment_name='T1';
SQL> insert into t1 select * from t1;
SQL> insert into t1 select * from t1;
SQL> insert into t1 select * from t1;
SQL> select bytes, blocks, extents, tablespace_name from dba_segments where segment_name='T1';
SQL>select extent_id, bytes, blocks from dba_extents where segment_name='T1';
SQL> insert into t1 select * from t1; 空間不足，報錯
SQL> rollback;
SQL> select bytes, blocks, extents, tablespace_name from dba_segments where segment_name='T1'; 空間不釋放
SQL> alter table t1 move; 釋放空間
只讀表空間：
SQL> alter tablespace tbs01 read only;
SQL> delete t1; 禁止dml
SQL> insert into t1 select * from t1; 禁止dml
SQL> create table t2 (x int) tablespace tbs01; 失敗
SQL> alter table t1 add (x int); 成功
SQL> update t1 set x=1; 失敗
SQL> drop table t1; 成功
dml和ddl的區別
改變表空間大小：
resize，autoextend，adddatafile
刪除表空間：
SQL> drop tablespace tbs01 including contents and datafile;
1.客戶端通過@ora10g的名字去tnsname.ora文件獲取服務器的具體連接信息
2.客戶端通過tnsname.ora中的描述向服務器發出鏈接請求服務器端
3.服務器的監聽器接收到連接請求后,驗證請求的服務的有效性
4.服務器端產生一個服務進程和客戶端進程建立連接
查看會話建立過程：
$ netstat -tlnp | grep 1521
$ sqlplus sys/password@orcl as sysdba
$ netstat -tnp | grep sqlplus
$ kill -9 1234 殺死維護sqlplus的進程
配置文件：
$vi$ORACLE_HOME/network/admin/listener.ora
通過netca添加新的監聽服務Listener15210，端口使用15210
$ vi listener.ora
$ netstat -tln|grep 1521
$ lsnrctl status listener15210
通過netmgr配置高級選項
通過lsnrctl命令來啟動/停止/查看/重載監聽器/服務
lsnrctl start|stop|status|reload|service
指定監聽的名稱：
$ lsnrctl status listener15210
網絡環境變化，需要檢查listener.ora和/etc/hosts文件
netca刪除Listener15210
靜態注冊和動態注冊
什么是靜態注冊
就是監聽器的配置文件中寫明了監聽哪個實例需要配置SID_DESC字段
定位實例的方式可以使用SID_NAME或者SERVICE_NAME來定位
什么是動態注冊
就是監聽器的配置文件中沒寫明監聽哪個實例
要通過PMON告知監聽器要監聽的具體實例
PMON是將SERVER_NAME告訴給監聽器這個過程就是注冊
默認一分鐘PMON注冊一次也就是說啟動監聽還沒注冊時是無法連接的
添加3種方式，后面的刪掉，用文檔改寫
區分靜態注冊和動態注冊
lsnrctl status
是 ready 就是動態
是 unknow 就是靜態
靜態注冊listener.ora文件信息:
$ vi listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME =ora11g)
)
)
$ lsnrctl reload
.....
Services Summary...
Service "ora11g" has 1 instance(s).
Instance "ora11g", status UNKNOWN, has 1 handler(s) for this service...
狀態總是顯示未知的,當有請求時,監聽器才去確認數據是否存在
動態注冊
Oracle9i起實例使用動態服務注冊來通知監聽程序有關其數據庫服務的信息。
服務注冊依賴PMON 進程向監聽程序注冊實例信息注冊間隔為1分鐘左右
手動注冊命令 alter system register;
無需在listener.ora 文件中設置任何信息此文件可以不存在
3種注冊方式：
本地默認端口監聽
本地非默認端口監聽
遠程監聽
本地非默認端口：
netca創建listener15210，使用15210端口
$ netstat -tlnp | grep 15210
寫入監聽的別名：
$ cd $ORACLE_HOME/network/admin
$ vi tnsnames.ora
listener15210 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1.test.com)(PORT = 15210))
)
修改參數：
SQL> ALTER SYSTEM SET LOCAL_LISTENER=listener15210;
SQL> ALTER SYSTEM register; 立即注冊（可選）
服務信息已添加到新的監聽中：
$ lsnrctl status listener15210
客戶端指定新端口連接：
$ sqlplus sys/password@192.168.0.1:1521/orcl as sysdba 報錯
$ sqlplus sys/password@192.168.0.1:15210/orclas sysdba
刪除配置：
SQL> ALTER SYSTEM SET LOCAL_LISTENER='';
SQL> ALTER SYSTEM register;
$ vi tnsnames.ora 刪除listener15210別名
netca刪除15210端口的監聽
輕松連接：
$ sqlplus sys/password@192.168.0.1:1521/orcl as sysdba
適用于臨時性的連接
本地命名：
查看現有的主機連接字符串
$ cd $ORACLE_HOME/network/admin
$ vi tnsnames.ora
$ sqlplus sys/password@orcl as sysdba
使用netca添加新的主機連接字符串orcl192
$ sqlplus sys/password@orcl192 as sysdba
SQL> select name from v$database;
tnsping測試，不需要用戶名和口令：
$ tnsping 192.168.0.1:1521/orcl
$ tnsping orcl192
解析方法的順序：
$ vi sqlnet.ora
netca可以修改
恢復默認設置：
SQL> alter system set local_listener='';
SQL> alter sytem register;
netca刪除不需要的監聽和連接配置
專有服務和共享服務模式
專有模式
每個用戶進程和服務進程間通過監聽器建立連接
進程信息存放于PGA中,也就是說有多少個用戶進程就有多少個PGA產生
單單只有進程連接還不足以操作數據庫,還需要產生的會話信息
會話信息存儲在UGA中,UGA在專有模式中存在于PGA
因為進程和進程間是相互隔離的,所以會話信息也相對獨立
這就導致了服務進程只能獲知當前用戶進程的會話請求信息只能為當前用戶進程服務
共享模式
用戶進程的請求被監聽器接收,監聽器不委派服務器進程,而是將調度器信息返回給客戶端
調度器將用戶進程的請求放入請求隊列
多個服務進程中的一個服務進程從隊列中獲取用戶進程的請求,并處理這個用戶進程的請求
服務進程處理完后將處理結果放入響應隊列,每個調度器都有自己的響應隊列
響應隊列的信息反饋給對應的調度器
調度器再把服務進程處理的結果返回給用戶進程
共享模式中的用戶進程的會話信息對每一個服務器進程來說都是可見的.
因為共享模式的UGA信息存在于SGA中,所以此時一個用戶進程的請求可以由多個服務進程來完成.
共享服務器的配置通過初始化參數dispatchers來配置
可以通過DBCA來修改
$ lsnrctl service
SQL> show parameter disp
SQL> show parameter shared_server
專有模式和共享模式是可以兼容在一起使用的
此時看用戶如何選擇
tnsnames.ora中
ORA10G =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dba.up.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED) # 不寫這個值就是按服務器的模式來匹配
# shared 指定使用共享方式連接
# DEDICATED 指定使用專有模式連接
(SERVICE_NAME = raw10g)
)
)
測試三種模式
ORCL_default =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1.test.com)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
ORCL_dedicated =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1.test.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
ORCL_shared =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1.test.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = orcl)
)
)
測試：
$ sqlplus sys/password@orcl_default as sysdba
$ sqlplus sys/password@orcl_dedicated as sysdba
$ sqlplus sys/password@orcl_shared as sysdba
SQL> select SID, SERVER,PROGRAM from v$session where USERNAME='SYS';
SQL> select distinct sid from v$mystat;
管理維護、大數據量導入、備份恢復等工作不適合用共享方式：
SQL> shutdown immediate 共享連接下不能發布管理命令
恢復原有設置：
dbca修改為dedicated方式
數據庫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: 審計
預定義的系統用戶：
SQL> select USERNAME, ACCOUNT_STATUS from dba_users;
open狀態的用戶：
SQL> select USERNAME, ACCOUNT_STATUS from dba_users ACCOUNT_STATUS='OPEN';
系統管理賬號:
SYS SYSTEM DBSNMP SYSMAN
3種身份驗證方式：
password驗證：
瀏覽器中創建用戶user01
或者用命令創建：
SQL> create user user01 identified by password;
SQL> grant create session to user01;
測試：
$ sqlplus user01/password
external（os）驗證：
操作系統中創建用戶：
$ su -
Password:
[root@node1 ~]# useradd osuser
[root@node1 ~]# passwd osuser
$ sqlplus / as sysdba
外部用戶使用固定的前綴：
SQL> show parameter os_auth
SQL> create user ops$osuser identified externally;
SQL> grant create session to ops$osuser;
不要su - osuser，環境變量保留：
$ su osuser
Password:
[osuser@node1 admin]$ sqlplus /
SQL> show user
USER is "OPS$OSUSER"
管理員的身份驗證：
本地連接：
本地連接，預先設置ORACLE_SID，操作系統用戶是dba群組的成員
$ id
uid=1001(oracle) gid=1000(oinstall) groups=1000(oinstall),1031(dba),1032(oper)
$ sqlplus / as sysdba
SQL> show user
USER is "SYS"
$ su -
# usermod -G oper oracle或
#gpasswd -d oracle dba
# exit
$ sqlplus / as sysdba
報錯，權限不夠
只要是dba群組中的成員，就可以不需要知道sys的口令，直接以sqlplus / as sysdba登錄
并且身份為sys。
恢復：
# gpasswd -a oracle dba
遠程客戶端連接：
$ sqlplus sys/password@orcl as sysdba
$ ls $ORACLE_HOME/dbs/orapworcl
$ orapwd
系統權限：
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
角色就是數據庫中的群組！
角色的作用：簡化權限的管理，動態更新用戶的權限。
預定義的角色：
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主要控制兩個方面：
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;
開啟開關參數：
SQL> show parameter audit_trail
設置審計選項：
每次設置新的審計選項，測試用戶需要重新連接
sys準備工作：
SQL> drop user user01 cascade;
SQL> create user user01 identified by password;
SQL> grant create session, create table, create any table to user01;
審計系統權限：
SQL>AUDIT CREATE ANY TABLE, CREATE TABLE BY USER01 BY ACCESS;
user01測試：
SQL> create table t1(x int);
SQL> create table t1(x int); 失敗
SQL> create table hr.t1(x int);
SQL> create table hr.t1(x int); 失敗
sys查看審計結果：
SQL> desc aud$
SQL> desc dba_audit_trail
瀏覽器中查看
sys添加審計條件：
SQL> AUDIT SELECT ANY TABLE BY user01 BY ACCESS;
SQL> grant select any table to user01;
user01測試：
SQL> select * from t1;
SQL> select * from hr.t1;
sys查看審計結果：
瀏覽器中或者查看dba_audit_trail表
刪除審計選項：
SQL> NOAUDIT CREATE ANY TABLE BY USER01;
SQL> NOAUDIT CREATE TABLE BY USER01;
SQL> NOAUDIT SELECT ANY TABLE BY user01;
審計對象：
sys設置審計選項：
SQL> AUDIT SELECT ON hr.employees BY ACCESS;
SQL> drop user user01 cascade;
SQL> create user user01 identified by password;
SQL> grant create session to user01;
sys授權，每執行一個語句，user01就測試一次：
SQL> grant select any table to user01;
SQL> revoke select any table from user01;
SQL> grant select on hr.employees to user01;
user01測試（執行4次）：
SQL> select count(*) from hr.employees;
默認不記錄sys的行為：
SQL> select count(*) from hr.employees;
刪除審計選項：
SQL> NOAUDIT SELECT ON hr.employees;
審計語句：
sys設置審計選項：
SQL> AUDIT TABLE BY user01 BY ACCESS;
user01測試：
SQL> create table t1(x int); 失敗
SQL> create table t1(x int);
SQL> create table t1(y int); 失敗
SQL> drop table t1;
sys查看結果：
瀏覽器中，或DBA_AUDIT_OBJECT表中
刪除審計選項：
SQL>NOAUDIT TABLE BY USER01
審計sys的操作：
SQL> show parameter audit
修改兩個參數
讀寫沖突通過讀一致性解決：
sys準備工作：
SQL> create user user01 identified by password;
SQL> grant dba to user01;
以下都用user01：
SQL> conn user01/password
Connected.
SQL> create table t1(x int);
SQL> insert into t1 values (1);
SQL> commit;
session1：
SQL> update t1 set x=11 where x=1;
SQL> select * from t1;
session 2:
SQL> select * from t1;
session 1：
SQL> commit;
session 2：
SQL> select * from t1;
測試serializable：
session1：
SQL> alter session set isolation_level=serializable;
重復上面的步驟
寫與寫的沖突通過鎖機制解決：
session 1:
SQL> update t1 set x=11 where x=1;
瀏覽器中查看鎖信息
session 2:
SQL> update t1 set x=111 where x=1; 被阻塞
瀏覽器中查看鎖信息
session 1:
SQL>rollback;
瀏覽器中查看鎖信息
死鎖：
session1:
SQL> select * from t1;
X
----------
1
2
SQL> update t1 set x=11 where x=1;
session2:
SQL> update t1 set x=22 where x=2;
session1:
SQL> update t1 set x=222 where x=2; 阻塞
session2:
SQL> update t1 set x=111 where x=1; 死鎖
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
$ vi /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log
鎖和外鍵
select … for update
什么是撤銷數據：
1.交易的回退:沒有提交的交易可以rollback
2.交易的恢復:數據庫崩潰時,將磁盤的不正確數據恢復到交易前
3.讀一致性 :被查詢的記錄有事務占用,轉向回滾段找改前鏡像
4.閃回數據 :從回滾段中構造歷史數據
事務與撤銷數據：
redo和undo：
AUM：
3個參數，兩個表空間屬性
undo_management=AUTO 回滾表空間段的段管理模式，管理員只需要備足夠的表空間容量，oracle會自動管理擴展回滾段的數量。只能使用一個UNDO表空間。
undo_tablespace:只有在自動管理模式下才可以使用。指明使用哪個UNDO表空間
undo_retention＝900 ：
提交之后舊的鏡像保持在回滾段中的時間。
非強制的回退保持時間.(回滾空間不足老的鏡像就會被覆蓋)
autoextend：表空間自動擴展
強制保持：但是對空間要求較大，要慎用。(10g開始支持)
alter tablespace UNDOTABS1 RETENTION GUARANTEE;
select tablespace_name,RETENTION from dba_tablespaces;
UNDO_RETENTION specifies (in seconds) how long already committed undo information is to be retained. The only time you must set this parameter is when:
?The undo tablespace has the AUTOEXTEND option enabled
?You want to set undo retention for LOBs
?You want to guarantee retention
undo advisor：
$ ps -ef | grep cjq
SQL> show parameter job_queue_processes
后臺預先設置的自動化管理作業：
自定義作業：
SQL> create table session_history(snap_time timestamp with local time zone, num_session number);
em中創建作業：
使用plsql塊：
declare
session_count number;
begin
select count(*) into session_count from v$session;
insert into session_history values (systimestamp, session_count);
commit;
end;
$ vi .bash_profile
#export NLS_LANG=american_america.AL32UTF8
#export NLS_DATE_FORMAT='yyyy-mm-dd hh34:mi:ss'
$ unset NLS_LANG
$ unset NLS_DATE_FORMAT
SQL> select sysdate from dual;
控制文件
SQL> show parameter control_files
SQL> select * from v$controlfile;
修改路徑：
$ cd $ORACLE_HOME/dbs
$ cp spfileorcl.ora spfileorcl.ora.bak
SQL> alter system set control_files='/u01/app/oracle/oradata/orcl/control01.ctl', '/home/oracle/control02.ctl' scope=spfile;
SQL> shutdown immediate
$ mv /u01/app/oracle/fast_recovery_area/orcl/control02.ctl /home/oracle/control02.ctl
SQL> startup
SQL> show parameter control_files
SQL> select * from v$controlfile;
增加鏡像：
SQL> alter system set control_files='/u01/app/oracle/oradata/orcl/control01.ctl', '/home/oracle/control02.ctl', '/home/oracle/control03.ctl' scope=spfile;
SQL> shutdown immediate
$ cp /home/oracle/control02.ctl /home/oracle/control03.ctl
SQL> startup
SQL> show parameter control_files
SQL> select * from v$controlfile;
日志文件：
增加成員和日志組：
SQL> select GROUP#, SEQUENCE#, STATUS, MEMBERS from v$log;
SQL> select GROUP#, MEMBER from v$logfile;
SQL> alter database add logfile member '/home/oracle/redo01b.log' to group 1;
SQL> alter database add logfile member '/home/oracle/redo02b.log' to group 2;
SQL> alter database add logfile member '/home/oracle/redo03b.log' to group 3;
SQL> alter database add logfile group 4 ('/u01/app/oracle/oradata/orcl/redo04.log', '/home/oracle/redo04b.log') size 50M;
FRA：
SQL> show parameter db_recovery
backupset: 10GB, archived log: 5GB
10+5, 10G
開啟歸檔模式：
SQL> archive log list 檢查當前設置
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;
SQL> archive log list
SQL> show parameter log_archive_dest
SQL> select group#, sequence#, status, archived from v$log;
SQL> alter system switch logfile;
SQL> select group#, sequence#, status, archived from v$log;
SQL> select NAME, SEQUENCE#, STATUS from v$archived_log;
$ ls /u01/app/oracle/fast_recovery_area/ORCL
$ rman 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;
備份的加密：
不歸檔 歸檔
online offline online offline
完全部分完全部分完全部分完全部分
shutdown nomount mount open
備份數據文件：
SQL> select file_id, file_name from dba_data_files;
RMAN> backup datafile 4;
RMAN> backup datafile 4,5;
RMAN> backup datafile '/u01/app/oracle/oradata/orcl/users01.dbf';
RMAN> backup tablespace users;
RMAN> backup tablespace users, example;
RMAN> backup database;
RMAN> list backup;
desc v$backup_set，瀏覽器查看備份
增量備份：
RMAN> backup incremental level 0 tablespace users;
RMAN> list backup of tablespace users;
SQL> create table t1(x int) tablespace users;
SQL> insert into t1 values (1);
SQL> commit;
RMAN> backup incremental level 1 tablespace users;
RMAN> backup incremental level 1 tablespace users;
SQL> create table t2(x int) tablespace users;
SQL> insert into t2 values (1);
SQL> commit;
RMAN> backup incremental level 1 cumulative tablespace users;
RMAN> list backup of tablespace users;
開啟塊跟蹤：
SQL> alter database enable block change tracking using file '/home/oracle/blk_trk.chg';
需要重新連接會話
SQL> select * from v$block_change_tracking;
SQL> select DATAFILE_BLOCKS, BLOCKS_READ, BLOCKS, USED_CHANGE_TRACKING from v$backup_datafile where INCREMENTAL_LEVEL>0;
SQL> create table t3(x int) tablespace users;
SQL> insert into t3 values (1);
SQL> commit;
RMAN> backup incremental level 1 tablespace users;
SQL> select DATAFILE_BLOCKS, BLOCKS_READ, BLOCKS, USED_CHANGE_TRACKING from v$backup_datafile where INCREMENTAL_LEVEL>0;
SQL> alter database disable block change tracking; 關閉
增量更新：
SQL> create table t1(x int) tablespace users;
SQL> insert into t1 values (1);
SQL> commit;
RMAN> backup incremental level 1 for recover of copy with tag 'update_copy' tablespace users; 第一次創建的是0級備份
RMAN> list copy; 記錄time和scn
SQL> insert into t1 values (2);
SQL> commit;
RMAN> backup incremental level 1 for recover of copy with tag 'update_copy' tablespace users; 第2次創建的是1級備份
RMAN> list backup; backupset格式
RMAN> recover copy of tablespace users with tag 'update_copy';
RMAN> list copy; time和scn更新
腳本形式：
RMAN> run {
backup incremental level 1 for recover of copy with tag 'update_copy' tablespace users;
recover copy of tablespace users with tag 'update_copy';
}
備份歸檔日志：
RMAN> list archivelog all;
RMAN> backup archivelog all delete all input;
RMAN> list archivelog all;
RMAN> list backup;
備份的維護：
查看：
RMAN> list backup;
RMAN> list copy;
RMAN> list backup of tablespace users;
RMAN> list backup of datafile 4;
RMAN> list archivelog all;
檢查備份：
RMAN> delete backup; 刪除備份
RMAN> delete copy;
RMAN> list backup; list copy;
RMAN> report need backup; 根據策略檢查
RMAN> backup tablespace users;
RMAN> report need backup;
RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
RMAN> report need backup;
RMAN> CONFIGURE RETENTION POLICY clear;
RMAN> report obsolete;
刪除備份：
RMAN> delete backupset of tablespace users;
RMAN> delete backupset 1234;
RMAN> backup tablespace users;
RMAN> backup tablespace users;
RMAN> show all;
RMAN> delete obsolete;
crosscheck:
RMAN> delete backup;
RMAN> backup tablespace users;
RMAN> list backup of tablespace users;
$ mv /u01/app/oracle/fast_recovery_area/ORCL/backupset/… 改名
RMAN> restore datafile 4; 報錯
RMAN> crosscheck backup;
RMAN> list backup of tablespace users; 報廢狀態
RMAN> list expired backup;
$ mv /u01/app/oracle/fast_recovery_area/ORCL/backupset/… 恢復原名
RMAN> crosscheck backup;
RMAN> list backup of tablespace users; 可用狀態
RMAN> delete expired backup;
catalog:
$ cp /u01/app/oracle/fast_recovery_area/ORCL/backupset/… 復制
RMAN> delete backup;
RMAN> list backup; backupset消失
$ mv /u01/app/oracle/fast_recovery_area/ORCL/backupset/… 恢復原名
RMAN> catalog recovery area noprompt;
RMAN> list backup; backupset恢復
RPO/RTO
數據文件：
不歸檔方式下丟失一個數據文件：
SQL> archive log list
備份前工作：
SQL> create table t1(x varchar2(50)) tablespace users;
SQL> insert into t1 values ('friday, before backup');
SQL> commit;
備份：
SQL>查詢v$datafile, v$logfile, v$tempfile, v$controlfile
SQL> shutdown immediate
$ cd $ORACLE_BASE/oradata/
$ cp -r orcl orcl.bak
$ cd $ORACLE_BASE/fast_recovery_area/orcl
$ cp control02.ctl control02.ctl.bak
SQL> startup
備份后工作：
SQL> insert into t1 values ('monday, after backup');
SQL> commit;
故障：
SQL> alter system flush buffer_cache;
$ cd $ORACLE_BASE/oradata/orcl
$ >users01.dbf
SQL> select * from t1; 報錯
$ vi /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log
$ dbv file=/u01/app/oracle/oradata/orcl/users01.dbf
恢復：
SQL> shutdown abort
$ cd $ORACLE_BASE/oradata
$ rm -rf orcl
$ mv orcl.bak orcl
$ cd $ORACLE_BASE/fast_recovery_area/orcl
$ mv control02.ctl.bak control02.ctl
SQL> startup
SQL> select * from t1;
歸檔模式下丟失一個數據文件：
SQL> archive log list
備份前工作：
SQL> create table t1(x varchar2(50)) tablespace users;
SQL> insert into t1 values ('friday, before backup');
SQL> commit;
備份：
RMAN> backup tablespace users tag "tbs_users_weekend_backup";
備份后工作：
SQL> select group#, sequence#, status, archived from v$log;
SQL> insert into t1 values ('after backup, logseq 7, archived');
SQL> commit;
SQL> alter system switch logfile;
SQL> insert into t1 values ('after backup, logseq 8, archived');
SQL> commit;
SQL> alter system switch logfile;
SQL> insert into t1 values ('after backup, logseq 9, archived');
SQL> commit;
SQL> alter system switch logfile;
SQL> insert into t1 values ('after backup, logseq 10, current');
SQL> commit;
SQL> insert into t1 values ('after backup, logseq 10, current, uncommitted');
SQL> select * from t1;
故障：
SQL> shutdown abort
$ rm $ORACLE_BASE/oradata/orcl/users01.dbf
SQL> startup 報錯
SQL> select open_mode from v$database;
$ vi /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log
恢復：
RMAN> list backup of tablespace users;
RMAN> list archivelog all;
SQL> alter database datafile 4 offline; system和undotbs不能offline
SQL> alter database open;
RMAN> restore datafile 4;
RMAN> recover datafile 4;
SQL> alter database datafile 4 online;
SQL> select * from t1;
通過不完全恢復解決用戶的誤操作：
SQL> archive log list
備份前工作：
SQL> create table t1(x varchar2(50)) tablespace users;
SQL> insert into t1 values ('before backup');
SQL> commit;
備份：
RMAN> backup database tag 'weekend_DB_full_backup';
備份后：
SQL> insert into t1 values ('after backup, before delete');
SQL> commit;
誤操作：
SQL> select sysdate from dual;
SQL> select dbms_flashback.get_system_change_number from dual;
SQL> delete t1;
SQL> commit;
SQL> create table after_delete (x int); 正確的操作
SQL> insert into after_delete values (1);
SQL> commit;
恢復：
RMAN>run {
startup force mount;
set until scn= 1806683;
restore database;
recover database;
alter database open resetlogs;
}
set until time=’2015-10-26 11:13:23’; 基于時間點恢復
SQL> select * from t1;
SQL> select * from after_delete; 丟失
SQL> select group#, sequence#, status, archived from v$log;
通過不完全恢復解決歸檔日志不連續：
SQL> archive log list
備份前：
SQL> create table t1(x varchar2(50)) tablespace users;
SQL> insert into t1 values ('before backup');
SQL> commit;
備份：
RMAN> backup database tag 'weekend_DB_full_backup';
備份后：
SQL> select GROUP#, SEQUENCE#, STATUS, archived from v$log;
SQL> insert into t1 values ('after backup, logseq 1, archived');
SQL> commit;
SQL> alter system switch logfile;
SQL> insert into t1 values ('after backup, logseq 2, archived');
SQL> commit;
SQL> alter system switch logfile;
SQL> insert into t1 values ('after backup, logseq 3, archived');
SQL> commit;
SQL> alter system switch logfile;
SQL> insert into t1 values ('after backup, logseq 4, archived');
SQL> commit;
SQL> alter system switch logfile;
SQL> insert into t1 values ('after backup, logseq 5, current');
SQL> commit;
SQL> alter system checkpoint;
故障：
SQL> shutdown abort
$ rm /u01/app/oracle/oradata/orcl/users01.dbf
$ rm /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2016_03_01/o1_mf_1_5_cfbcxo84_.arc
恢復：
SQL> startup
先嘗試只恢復一個數據文件失敗。
RMAN>run {
startup force mount;
set until sequence 5;
restore database;
recover database;
alter database open resetlogs;
}
SQL> select * from t1;
丟失部分控制文件：
SQL> select * from v$controlfile;
$ >/u01/app/oracle/oradata/orcl/control01.ctl
SQL> select * from v$tablespace; 報錯
SQL> alter system checkpoint; 報錯
$ vi /u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log
SQL> shutdown abort
SQL> startup nomount
SQL> show parameter control_files
$ cp /u01/app/oracle/fast_recovery_area/orcl/control02.ctl /u01/app/oracle/oradata/orcl/control01.ctl
SQL> alter database mount;
SQL> alter database open;
丟失全部控制文件（有自動備份）：
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> backup datafile 4;
$ >/u01/app/oracle/oradata/orcl/control01.ctl
$ >/u01/app/oracle/fast_recovery_area/orcl/control02.ctl
SQL> select * from v$tablespace; 報錯
SQL> alter system checkpoint; 報錯
SQL> shutdown abort
SQL> startup nomount
RMAN> restore controlfile from autobackup;
RMAN> alter database mount;
RMAN> recover database;
RMAN> alter database open resetlogs;
丟失全部控制文件（沒有自動備份）：
SQL> alter database backup controlfile to '/home/oracle/control.bak';
SQL> alter database backup controlfile to trace;
SQL> select * from v$diag_info;
有自動備份：
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;
丟失一個成員：
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包隔離數據塊
SQL> show parametercontrol_file_record_keep_time
用dbca創建數據庫rc（不配置em、fra，200M內存，字符集unicode）
或者：
用netca創建主機連接字符串rc指向自身。
rc：
$ sqlplus sys/password@rc as sysdba
SQL> create tablespace rc_tbs datafile '/home/oracle/rc_tbs.dbf' size 50M;
SQL> create user rcowner identified by password default tablespace rc_tbs quota unlimited on rc_tbs;
SQL> grant recovery_catalog_owner to rcowner;
$ rman catalog rcowner/password@rc
RMAN> create catalog;
$ rman target sys/password@orcl catalog rcowner/password@rc
或
$ rman target / catalog rcowner/password@rc
RMAN> register database;
dbca刪除rc
功能 依賴組件 相關參數 典型錯誤
query undo tbs undo_retention dml
version query undo tbs undo_retention dml
flashback table undo tbs undo_retention dml
flashback drop recyclebin recyclebin, freespace drop table
transaction query supplemental log dml
fda flashback archive dml
database flashback log db_flashback_retention_target ddl
sys不允許閃回，創建新用戶
SQL> create user user01 identified by password;
SQL> grant dba to user01;
SQL> conn user01/password
user01:
SQL> create table t1(x int);
SQL> create index t1_x_idx on t1(x);
SQL> insert into t1 values (1);
SQL> commit;
SQL> select sysdate from dual;
SQL> select dbms_flashback.get_system_change_number from dual;
SQL> delete t1;
SQL> commit;
SQL> select * from t1;
SQL> select * from t1 as of scn 1446069;
SQL> select * from t1 as of timestamp to_timestamp('2015-10-28 10:31:54', 'yyyy-mm-dd hh34:mi:ss');
SQL> truncate table t1;或alter table t1 move;或收縮數據文件
SQL> select * from t1 as of scn 1446069; 物理結構變化，閃回失敗
logminer
SQL> create table t1(x int);
SQL> insert into t1 values (1);
SQL> commit;
SQL> update t1 set x=2;
SQL> commit;
SQL> update t1 set x=3;
SQL> commit;
SQL> update t1 set x=4;
SQL> commit;
SQL>select versions_starttime, versions_endtime, versions_xid, versions_operation, x
from t1
versions between scn minvalue and maxvalue
order by versions_starttime;
versions between timestamp to_timestamp('2015-10-28 9:00:00', 'yyyy-mm-dd hh34:mi:ss') and to_timestamp('2015-10-28 10:00:00', 'yyyy-mm-dd hh34:mi:ss')
SQL> truncate table t1; 物理結構改變，查詢失敗
SQL> conn user01/password
SQL> create table my_dept(deptno int primary key, dname varchar2(20));
SQL> create table my_emp(empno int primary key, deptno int references my_dept);
SQL> insert into my_dept values (10, 'sales');
SQL> insert into my_emp values (100, 10);
SQL> commit;
SQL> select dbms_flashback.get_system_change_number from dual;
SQL> delete my_emp;
SQL> delete my_dept;
SQL> commit;
SQL> alter table my_dept enable row movement;
SQL> alter table my_emp enable row movement;
SQL> flashback table my_emp to scn 1451706; 失敗
SQL> flashback table my_dept to scn 1451706;
SQL> flashback table my_emp to scn 1451706;
SQL> select INDEX_NAME, STATUS from user_indexes where table_name='MY_EMP';自動維護索引
SQL> select INDEX_NAME, STATUS from user_indexes where table_name='MY_DEPT';
SQL> truncate table my_emp;
SQL> flashback table my_emp to scn 1451706; 失敗
SQL> show parameter recyclebin
SQL> purge recyclebin;
SQL> create tablespace tbs01 datafile '/home/oracle/tbs01.dbf' size 5M;
SQL> create table t1 tablespace tbs01 as select * from dba_objects where rownum<=20000;
SQL> create index t1_object_id_idx on t1(object_id) tablespace tbs01;
SQL> select INDEX_NAME from user_indexes where TABLE_NAME='T1';
SQL> drop table t1;
SQL> select table_name from user_tables;
SQL> show recyclebin
SQL> select object_name, original_name, type, droptime from user_recyclebin; 包含index
SQL> select count(*) from "BIN$IyKOcy5jPo7gUwEAqMCBEg==$0";
SQL> flashback table t1 to before drop;
SQL> select INDEX_NAME from user_indexes where TABLE_NAME='T1';
SQL> alter index "BIN$LRyc7hA1JaPgUwEAqMDzWw==$0" rename to T1_OBJECT_ID_IDX; 恢復index名稱
重名的處理：
SQL> flashback table "BIN$IyKOcy5jPo7gUwEAqMCBEg==$0" to before drop;
SQL> flashback table t1 to before drop rename to t2;
SQL> drop table t1;
SQL> show recyclebin 在回收站中
SQL> create table t2 tablespace tbs01 as select * from dba_objects where rownum<=30000;
SQL> show recyclebin t1被覆蓋
SQL> drop table t2 purge;
SQL> purge recyclebin
SQL> alter database add supplemental log data;
SQL> alter database add supplemental log data (primary key) columns;
SQL> create table t1(x int);
SQL> insert into t1 values (1);
SQL> commit;
SQL> update t1 set x=11 where x=1; 誤操作的事務
SQL> commit;
SQL> insert into t1 values (2);
SQL> commit;
select versions_starttime, versions_endtime, versions_xid, versions_operation, x
from t1
versions between scn minvalue and maxvalue
order by versions_starttime; 獲取誤操作事務的xid
SQL> select UNDO_SQL, OPERATION from flashback_transaction_query where xid='02000F0059040000';
SQL> shutdown immediate
SQL> startup mount
SQL> alter database flashback on; 數據庫在歸檔模式下
SQL> show parameter db_flashback_retention_target
SQL> select OLDEST_FLASHBACK_TIME from v$flashback_database_log;
SQL> create table t1(x int);
SQL> insert into t1 values (1);
SQL> commit;
SQL> select dbms_flashback.get_system_change_number from dual;
SQL> truncate table t1;
SQL> create table after_truncate(x int); 其他正確操作
SQL> select OLDEST_FLASHBACK_TIME, OLDEST_FLASHBACK_SCN from v$flashback_database_log; 確認是否在恢復范圍
SQL> shutdown abort
SQL> startup mount
SQL> flashback database to scn 1495195;
SQL> alter database open resetlogs;
SQL> select * from t1;
SQL> select * from after_truncate; 消失
SQL> create table t1(id int constraint t1_id_pk primary key, name varchar2(20), salary int constraint t1_salary_ck check(salary>0));
$ vi ~/loader.dat
100,"abc",1000
100,"def",2000
102,"xyz",-1000
em中常規導入，自動處理違反約束的記錄
em中直接導入
SQL> select CONSTRAINT_NAME, STATUS from user_constraints where TABLE_NAME='T1';
SQL> select INDEX_NAME, STATUS from user_indexes where TABLE_NAME='T1';
SQL> alter table t1 enable validate constraint T1_SALARY_CK; 失敗
SQL> @?/rdbms/admin/utlexpt1.sql
處理check約束：
SQL> alter table t1 enable validate constraint T1_SALARY_CK exceptions into exceptions;
SQL> select * from t1 where rowid in(select ROW_ID from exceptions);
SQL> update t1 set salary=abs(salary) where id=102;
SQL> truncate table exceptions;
SQL> alter table t1 enable validate constraint T1_SALARY_CK exceptions into exceptions;
處理pk約束：
SQL> alter table t1 disable novalidate constraint T1_ID_PK;
SQL> alter table t1 enable validate constraint T1_ID_PK exceptions into exceptions;
SQL> select * from t1 where rowid in(select ROW_ID from exceptions);
SQL> update t1 set id=101 where name='def';
SQL> truncate table exceptions;
SQL> alter table t1 enable validate constraint T1_ID_PK exceptions into exceptions;
SQL> select INDEX_NAME, STATUS from user_indexes where TABLE_NAME='T1';
oracle_datapump driver
unloading:
CREATE TABLE oe.inventories_xt
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY DATA_PUMP_DIR
LOCATION ('inv_xt.dmp')
)
AS SELECT * FROM oe.inventories;
SQL> delete oe.inventories_xt; 失敗
loading:
CREATE TABLE oe.inventories_xt2
(
product_id NUMBER(6),
warehouse_id NUMBER(3),
quantity_on_hand NUMBER(8)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY DATA_PUMP_DIR
LOCATION ('inv_xt.dmp')
);
SQL> delete oe.inventories_xt2; 失敗
DB time = CPU time + Wait time
字典表：
SQL> create table t1(x int);
SQL> insert into t1 values (1);
SQL> commit;
SQL> select TABLE_NAME, NUM_ROWS from dba_tables where table_name='T1'; 值為空
SQL> exec dbms_stats.gather_table_stats('sys', 't1');
SQL> select TABLE_NAME, NUM_ROWS from dba_tables where table_name='T1'; 更新
SQL> insert into t1 values (2);
SQL> commit;
SQL> select TABLE_NAME, NUM_ROWS from dba_tables where table_name='T1'; 過時
SQL> exec dbms_stats.gather_table_stats('sys', 't1');
SQL> select TABLE_NAME, NUM_ROWS, LAST_ANALYZED from dba_tables where table_name='T1'; 更新
v$表：
SQL> select name, value from v$sysstat where name like '%sort%';
SQL> select * from hr.employees order by salary;
SQL> select name, value from v$sysstat where name like '%sort%'; 增加
SQL> shutdown immediate
SQL> startup
SQL> select name, value from v$sysstat where name like '%sort%'; 歸零
參數：
SQL> show parameterstatistics_level 不能是basic
em中查看基本設置
em中生成和查看awr報表
$ ll /u01/app/oracle/product/11.2.0/db_1/rdbms/admin/awr*.sql
SQL> create tablespace tbs01 datafile '/home/oracle/tbs01.dbf' size 5M;
em中修改警告50%，嚴重80%。
SQL> create table t1 tablespace tbs01 as select * from dba_objects where 1=0;
SQL> insert into t1 select * from dba_objects where rownum<=10000;
SQL> commit; 超過50%
SQL> insert into t1 select * from dba_objects where rownum<=20000;
SQL> commit; 超過80%
em中查看警告信息。
session1:
SQL> create table t1(x int);
SQL> insert into t1 values (1);
SQL> commit;
SQL> update t1 set x=11;
session 2:
SQL> update t1 set x=22;
session1:
SQL> create table t1(x int);
SQL> insert into t1 values (1);
SQL> commit;
SQL> update t1 set x=11;
session 2:
SQL> update t1 set x=22;
em中尋找問題的根源
em中做ash報表
$ ll /u01/app/oracle/product/11.2.0/db_1/rdbms/admin/ash*.sql
initorcl.ora
spfileorcl.ora
ASMM
AMM
SQL> select bytes/1024/1024 from v$sgainfo where name='Granule Size';
SQL> show parameter memory
SQL> select COMPONENT, CURRENT_SIZE/1024/1024 from v$memory_dynamic_components;
em中的內存指導
$ strings $ORACLE_HOME/dbs/spfileorcl.ora __開頭的隱含參數保留優化設置
javapool的調整：
SQL> select CURRENT_SIZE/1024/1024 from v$memory_dynamic_components where COMPONENT='java pool';
DECLARE
i NUMBER;
v_sql VARCHAR2(200);
BEGIN
FOR i IN 1..200 LOOP
-- Build up a dynamic statement to create a uniquely named java stored proc.
-- The "chr(10)" is there to put a CR/LF in the source code.
v_sql := 'create or replace and compile' || chr(10) ||
'java source named "SmallJavaProc' || i || '"' || chr(10) ||
'as' || chr(10) ||
'import java.lang.*;' || chr(10) ||
'public class Util' || i || ' extends Object' || chr(10) ||
'{ int v1=1;int v2=2;int v3=3;int v4=4;int v5=5;int v6=6;int v7=7; }';
EXECUTE IMMEDIATE v_sql;
END LOOP;
END;
/
SQL> select CURRENT_SIZE from v$memory_dynamic_components where COMPONENT='java pool'; java pool改變
java pool擴展、buffercache收縮
SQL> select OPER_TYPE, OPER_MODE, INITIAL_SIZE, TARGET_SIZE, FINAL_SIZE, START_TIME from v$memory_resize_ops where COMPONENT='java pool';
SQL> select OPER_TYPE, OPER_MODE, INITIAL_SIZE, TARGET_SIZE, FINAL_SIZE, START_TIME from v$memory_resize_ops where COMPONENT='DEFAULT buffer cache';
largepool的調整：
SQL> select CURRENT_SIZE/1024/1024 from v$memory_dynamic_components where COMPONENT='large pool';
SQL> create table t1 as select rownum x from dual connect by level<=100000;
SQL> alter table t1 parallel 64; 也可以在查詢時指定并行度
SQL> select /*+ parallel(t1 24) */ count(*) from (select /*+ parallel(t1 24)*/ * from t1 group by x);
SQL> select CURRENT_SIZE/1024/1024 from v$memory_dynamic_components where COMPONENT='large pool'; large pool改變
SQL> select OPER_TYPE, OPER_MODE, INITIAL_SIZE, TARGET_SIZE, FINAL_SIZE, START_TIME from v$memory_resize_ops where COMPONENT='large pool';
備份spfile
$ cp $ORACLE_HOME/dbs/spfileorcl.ora $ORACLE_HOME/dbs/spfileorcl.ora.bak
AMMàASMM
amm下，sga和pga不需要設置
SQL> show parameter sga_max_size
SQL> show parameter sga_target
SQL> show parameter pga_aggregate_target
SQL> alter system set memory_target=0;
SQL> show parameter sga_target amm和asmm都有一對參數
SQL> show parameter sga_max_size
SQL> show parameter pga_aggregate_target
SQL> alter system set sga_target=300M; 手動修改
ASMMàmanual
SQL> show parameter shared_pool_size 值為0
SQL> alter system set sga_target=0;
SQL> show parameter shared_pool_size 固定
manualàasmmàamm
修改sga_target或memory_target，清空所有遺留參數
內存大小的建議：
SQL> select SHARED_POOL_SIZE_FOR_ESTIMATE ,SHARED_POOL_SIZE_FACTOR, ESTD_LC_TIME_SAVED from V$SHARED_POOL_ADVICE;
SQL> select SIZE_FOR_ESTIMATE, SIZE_FACTOR, ESTD_PHYSICAL_READS, ESTD_PHYSICAL_READ_FACTOR from V$DB_CACHE_ADVICE;
SQL> select * from V$SGA_TARGET_ADVICE;
SQL> select PGA_TARGET_FOR_ESTIMATE, PGA_TARGET_FACTOR, ESTD_PGA_CACHE_HIT_PERCENTAGE from V$PGA_TARGET_ADVICE;
SQL> select * from V$MEMORY_TARGET_ADVICE;
SQL> alter system flush shared_pool;
SQL> grant dba to hr;
SQL> conn hr/hr
SQL> set autot on
SQL> select e.last_name, d.department_name
from employees e, departments d
where e.department_id=d.department_id;
em中執行saa，過濾條件為表：hr.employees, hr.departments
SQL> alter system flush shared_pool;
SQL> conn hr/hr
SQL> set autot on
SQL> select /*+ full(employees) */ * from employees where employee_id=100;
em中創建tuningset，調用sta分析
參考：optimizer介紹.ppt
查詢改寫：
謂詞傳遞：
SQL> set autot trace exp
SQL>select e.last_name, d.department_name
from hr.employees e, hr.departments d
where e.department_id=d.department_id
and e.department_id=50;
自動添加3 - access("D"."DEPARTMENT_ID"=50)謂詞
for i in 1 .. 107(employees)
for j in 1 .. 27(departments)
i的部門=j的部門而且 i的部門=50
endloop
endloop
for i in 1 .. 10 (employeesin deptno 50)
for j in 1 .. 27(departments)
i的部門=j的部門
endloop
endloop
for i in 1 .. 10 (employeesin deptno 50)
i的部門=50
endloop
子查詢解嵌套：
SQL> select last_name
from hr.employees outer
where salary >
(select avg(salary) from hr.employees
where department_id = outer.department_id);
被改寫為多表連接
CBO和RBO的區別：
SQL> create table t1 as select 1 id, object_name from dba_objects;
SQL> update t1 set id=2 where rownum<=1;
SQL> commit;
SQL> select id, count(*) from t1 group by id;
SQL> create index t1_id_idx on t1(id);
SQL> exec dbms_stats.gather_table_stats('sys', 't1');
SQL> set autot trace exp
SQL> select * from t1 where id=1; cbo方式
SQL> select /*+ rule */ * from t1 where id=1;
SQL> select * from t1 where id=2; 錯誤
SQL> select /*+ rule */ * from t1 where id=2; 走索引，正確
SQL> exec dbms_stats.gather_table_stats('sys', 't1'); 重復搜集，獲取列值分布
exec dbms_stats.gather_table_stats('sys', 't1', method_opt => 'for columns size auto id');
SQL> select * from t1 where id=1; cbo方式，正確
SQL> select * from t1 where id=2; cbo方式，正確
SQL> select /*+ rule */ * from t1 where id=1; 走索引，錯誤
SQL> select /*+ rule */ * from t1 where id=2;
不及時更新統計信息，造成錯誤
SQL> update t1 set id=2;
SQL> commit;
SQL> select * from t1 where id=2; 走索引，錯誤
SQL> exec dbms_stats.gather_table_stats('sys', 't1');
SQL> select * from t1 where id=2; 正確
SQL> exec dbms_stats.delete_table_stats('sys', 't1');
SQL> update t1 set id=1 where rownum<=1;
SQL> commit;
SQL> select * from t1 where id=1; 動態采樣
SQL> select * from t1 where id=2; 動態采樣
影響cbo的初始化參數：
SQL> show parameter optimizer
all_rows和first_rows對執行計劃的影響：
SQL> alter session set optimizer_mode=first_rows或all_rows;
SQL> set autot trace exp
select e.last_name, d.department_name
from hr.employees e, hr.departments d
where e.department_id=d.department_id;
select /*+ all_rows */ e.last_name, d.department_name
from hr.employees e, hr.departments d
where e.department_id=d.department_id; 使用sort merge
select /*+ first_rows */ e.last_name, d.department_name
from hr.employees e, hr.departments d
where e.department_id=d.department_id; 使用nested loop
explain plan
SQL> desc plan_table
SQL> select * from plan_table;
SQL> explain plan set statement_id='test' for select * from hr.employees;
SQL> select PLAN_ID, OPERATION from plan_table where statement_id='test'; 可讀性差
SQL> select plan_table_output from table(dbms_xplan.display); 可讀性比較好
不真正執行語句，對使用綁定變量的語句可能出現誤差
autotrace
SQL> set autot on
SQL> select count(*) from hr.employees;
SQL> set autot trace
SQL> select count(*) from hr.employees;
SQL> set autot trace exp
SQL> select count(*) from hr.employees;
SQL> set autot trace stat
SQL> select count(*) from hr.employees;
SQL> set autot off
on選項真正執行語句，但對使用綁定變量的語句可能出現誤差
其他選項不真正執行語句
DBMS_XPLAN
參考：PL/SQL Packages and Types ReferenceDBMS_XPLAN
與explainplan配合：
參考explain plan示例
display_cursor:
查看上一個sql語句：
SQL>SET PAGESIZE 0
SQL> select count(*) from hr.employees;
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR);
SQL> select count(*) from hr.employees;
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR(null, null, 'advanced'));
SQL> desc v$sql
SQL> desc v$sql_plan
SQL> desc v$sql_plan_statistics
SQL> select count(*) from hr.employees;
SQL> select sql_id, child_number, sql_text from v$sql where sql_text like 'select count(*) from hr.employees';
SQL> select OPERATION, OPTIONS, OBJECT_NAME from v$sql_plan where SQL_ID='3ghpkw4yp4dzm' and CHILD_NUMBER=0;
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('3ghpkw4yp4dzm',0, 'advanced'));
也可以從awr快照中獲取sql執行計劃，display_awr
sql trace:
SQL> show parameter sql_trace
SQL> show parameter statistics_level
SQL> show parameter timed_statistics
輔助參數
SQL> show parameter max_dump_file_size
SQL> show parameter diagnostic_dest
SQL> show parameter tracefile_identifier
SQL> select * from v$diag_info;
SQL> alter session set sql_trace=true;
SQL> select count(*) from hr.employees;
SQL> select count(*) from hr.departments;
SQL> alter session set sql_trace=false;
$ vi /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_6651.trc
$ tkprof /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_6651.trc /home/oracle/output.trc
$ vi /home/oracle/output.trc
使用DBMS_MONITOR監控指定session:
SQL> select sid, serial# from v$session where USERNAME='HR';
SQL>EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(30, 4145, TRUE, TRUE);
hr的session:
SQL> select count(*) from employees;
sys關閉跟蹤：
SQL> EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE(30, 4145);
hr的跟蹤文件：
SQL> select * from v$diag_info;
$ vi /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_10676.trc
使用trcsess匯總共享服務器連接下的用戶會話信息。
執行計劃的讀取：
SQL> set linesize 999
SQL> set autot trace exp
SQL>select e.last_name, d.department_name
from hr.employees e, hr.departments d
where e.department_id=d.department_id;
sqlplus和sql developer中查看
統計信息：
SQL> alter system flush shared_pool;
SQL> alter system flush buffer_cache;
SQL> set autot on
SQL> select count(*) from hr.employees;
SQL> select count(*) from hr.employees;
SQL> select * from hr.employees;
掃描高水標記以下的所有塊
查詢的比例、物理順序、表小、沒有索引、并行
selectivity和cardinality參考：optimizer介紹.ppt
物理順序對全表掃描的影響：
SQL> create table t1 as select rownum x, dbms_random.value y from dual connect by level<=10000;
SQL> alter table t1 add constraint t1_x_pk primary key(x);
SQL> create table t2 as select * from t1 order by y;
SQL> alter table t2 add constraint t2_x_pk primary key(x);
SQL> exec dbms_stats.gather_table_stats('sys', 't1');
SQL> exec dbms_stats.gather_table_stats('sys', 't2');
SQL> select * from t1 where x between 1 and 100;
SQL> select * from t2 where x between 1 and 100;
SQL> select INDEX_NAME, CLUSTERING_FACTOR from dba_indexes where table_name in('T1', 'T2');
查詢語句對全表掃描的影響：
SQL> select * from hr.employees;
SQL> select * from hr.employees order by employee_id;
SQL> select employee_id from hr.employees;
SQL>select department_id from hr.employees;
db_file_multiblock_read_count對全表掃描的影響：
SQL> show parameter db_file_multiblock_read_count
SQL> create table t1 as select * from dba_objects;
SQL> exec dbms_stats.gather_table_stats('sys', 't1');
SQL> set autot on
SQL> alter system set db_file_multiblock_read_count=16;
SQL> select count(*) from t1;
SQL> alter system set db_file_multiblock_read_count=64;
SQL> select count(*) from t1;
高水標記對全表掃描的影響：
SQL> delete t1;
SQL> commit;
SQL> set autot on
SQL> select count(*) from t1; 刪除數據后，hwm不下降，導致cr讀過多
SQL> alter table t1 move;
SQL> select count(*) from t1;
INDEX UNIQUE SCAN:
SQL> select * from hr.employees where employee_id=100;
INDEX RANGE SCAN:
SQL> select * from hr.employees where employee_id between 100 and 110;
SQL> select * from hr.employees where department_id=10;
SQL> select * from hr.employees where last_name='King';
SQL> select INDEX_NAME, UNIQUENESS from dba_indexes where TABLE_NAME='EMPLOYEES';
INDEX FULL SCAN: 單塊，有序
SQL> select * from hr.employees order by employee_id;
SQL> select /*+ full(employees) */ * from hr.employees order by employee_id;
SQL> select * from hr.employees order by department_id; 全表掃描，因為有null值
INDEX FAST FULL SCAN: 多塊，無序
SQL> create table t1 as select rownum id, object_name from dba_objects;
SQL> alter table t1 add constraint t1_id_pk primary key(id);
SQL> exec dbms_stats.gather_table_stats('sys', 't1');
SQL> select count(*) from t1;
SQL> select /*+ index(t1 t1_id_pk) */ count(*) from t1; full scan的開銷大
SQL> select /*+ full(t1) */ count(*) from t1;
SQL> select /*+ index_ffs(employees emp_emp_id_pk) */ employee_id from hr.employees;
INDEX SKIP SCAN:
SQL> create table t1 as select * from dba_objects;
SQL> select count(distinct owner), count(distinct object_type), count(distinct object_name) from t1;
SQL> create index t1_idx on t1(owner, object_type, object_name);
SQL> exec dbms_stats.gather_table_stats('sys', 't1');
SQL> select * from t1 where owner='SYS' and object_type='TABLE' and object_name='AUD$';
SQL> select * from t1 where owner='SYS' and object_type='TABLE';
SQL> select * from t1 where object_type='TABLE' and object_name='AUD$';
SQL> select /*+ full(t1) */ * from t1 where object_type='TABLE' and object_name='AUD$';
class_no: 5
stud_no: 50（每個班級）
1
1 2 3 … 50
2
1 2 3 … 50
5
1 2 3 … 50
where stud_no between 5 and 10;
where class_no=1 and stud_nobetween 5 and 10
or class_no=2 and stud_nobetween 5 and 10
or class_no=3 and stud_nobetween 5 and 10
null對索引的影響：
SQL> create table t1(x int, y char(1));
SQL> insert into t1 values (null, 'a');
SQL> insert into t1 values (1, 'a');
SQL> insert into t1 values (2, 'a');
SQL> create index t1_x_idx on t1(x);
SQL> exec dbms_stats.gather_table_stats('sys', 't1');
測試下列語句：
select x from t1;
select count(*) from t1;
select count(*) from t1 where x is not null;
select count(x) from t1;
select max(x) from t1;
select min(x), max(x) from t1;
排除null，再次測試：
SQL> delete t1 where x is null;
SQL> commit;
SQL> alter table t1 modify(x not null);
SQL> select (select min(x) from t1), (select max(x) from t1) from dual;
重復值對索引的影響：
SQL> create table t1(x int not null, y int);
SQL> insert into t1 select rownum, 11 from dual connect by level<=10;
SQL> commit;
SQL> create index t1_x_idx on t1(x);
SQL> exec dbms_stats.gather_table_stats('sys', 't1');
SQL> select * from t1 where x=1; full table scan
SQL> select INDEX_NAME, UNIQUENESS from dba_indexes where TABLE_NAME='T1';
SQL> drop index t1_x_idx;
SQL> create unique index t1_x_idx on t1(x);
SQL> select * from t1 where x=1; index unique scan
外鍵對索引的影響：
SQL> create table dept(deptno int constraint dept_deptno_pk primary key, dname varchar2(10));
SQL> create table emp(empno int, deptno int constraint dept_emp_deptno_fk references dept(deptno));
SQL> insert into dept values (10, 'sales');
SQL> insert into dept values (20, 'market');
SQL> insert into dept values (30, 'it');
SQL> insert into emp values (100, 10);
SQL> commit;
SQL> exec dbms_stats.gather_table_stats('sys', 'dept');
SQL> exec dbms_stats.gather_table_stats('sys', 'emp');
SQL> alter session set sql_trace=true;
SQL> delete dept where deptno=10; 報錯
SQL> delete dept where deptno=20;
SQL> alter session set sql_trace=false;
SQL> select * from v$diag_info;
$ tkprof /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_22830.trc /home/oracle/output.trc
$ vi /home/oracle/output.trc
SQL> alter table emp modify(deptno not null);
SQL> create index emp_deptno_idx on emp(deptno);
SQL> alter session set sql_trace=true;
SQL> delete dept where deptno=10; 報錯
SQL> alter session set sql_trace=false;
類型轉換對索引的影響：
SQL> create table t1(x char(1) primary key, y int);
SQL> insert into t1 values ('1', 11);
SQL> insert into t1 values ('2', 22);
SQL> commit;
SQL> exec dbms_stats.gather_table_stats('sys', 't1');
SQL> set autot trace exp
SQL> select * from t1 where x=1;
函數索引：
SQL> create table t1 as select * from dba_objects;
SQL> create index t1_object_name_fbi on t1(lower(object_name));
SQL> select * from t1 where lower(object_name)='aud$';
create index t1_idx on t1(reverse(x));
where x like reserve(‘%abc%’);
bitmap index:
emp
ename gender deptno location job_id
abc M 10 BJ MGR
def F 20 SH EGR
xyz M 30 GZ MGR
select ename
from emp
where gender=’M’ and (deptno=10 or location=’GZ’) and job_id=’MGR’;
gender M F
abc 1 0
def 0 1
xyz 1 0
deptno 10 20 30
abc 1 0 0
def 0 1 0
xyz 0 0 1
location BJ SH GZ
abc 1 0 0
def 0 1 0
xyz 0 0 1
job_id MGR EGR
abc 1 0
def 0 1
xyz 1 0
gender(M) and (deptno(10)or location(GZ)) and job_id(mgr)
abc 1 1 0 1 1
def 0 0 0 0 0
xyz 1 0 1 1 1
nested loop:
for emp in 1..107
for dept in 1..27
emp.deptno=dept.deptno
end;
end;
for dept 1-27
for emp 1-107
sort merge:
emp 根據deptno排序
dept根據deptno排序
合并emp和dept
hashjoin:
emp 根據hash函數對deptno分割
dept根據hash函數對deptno分割
select /*+ first_rows */ e.last_name, d.department_name
from hr.employees e, hr.departments d
where e.department_id=d.department_id;
或者使用/*+ use_nl(e d) */，但限制了驅動表和被驅動表，不靈活
SQL> alter index hr.EMP_DEPARTMENT_IX invisible; 禁用emp上的外鍵索引
執行多表連接時，將dept的主鍵索引作為內部被驅動表，nl會盡量避免對被驅動表的全表掃描。
SQL> alter index hr.EMP_DEPARTMENT_IX visible; 恢復索引
select /*+ use_merge(e d) */ e.last_name, d.department_name
from hr.employees e, hr.departments d
where e.department_id=d.department_id;
select /*+ use_hash(e d) */ e.last_name, d.department_name
from hr.employees e, hr.departments d
where e.department_id=d.department_id;
在缺失索引時，傾向使用hashjoin:
SQL> alter index hr.EMP_DEPARTMENT_IX invisible;
SQL> alter index hr.DEPT_ID_PK invisible;
select e.last_name, d.department_name
from hr.employees e, hr.departments d
where e.department_id=d.department_id;
SQL> alter index hr.EMP_DEPARTMENT_IX visible;
SQL> alter index hr.DEPT_ID_PKvisible;
SQL> desc dba_tab_statistics
SQL> desc dba_tab_col_statistics
SQL> desc dba_ind_statistics
SQL> create table t1 as select * from dba_objects;
SQL> create index t1_object_id_idx on t1(object_id);
SQL> create index t1_owner_idx on t1(owner);
SQL> exec dbms_stats.gather_table_stats('sys', 't1');
SQL>select * from DBA_TAB_STATISTICS where TABLE_NAME='T1';
SQL>select * from DBA_TAB_COL_STATISTICS where TABLE_NAME='T1';
SQL>select * from DBA_IND_STATISTICS where TABLE_NAME='T1';
SQL> select count(*) from t1 where owner='SYS'; 沒有直方圖，使用錯誤計劃
SQL> create table t1(x int not null, y varchar2(128));
SQL> create index t1_x_idx on t1(x);
SQL> insert into t1 select 1, object_name from dba_objects where rownum<=10000;
SQL> insert into t1 select 2, object_name from dba_objects where rownum<=1;
SQL> commit;
SQL> exec dbms_stats.gather_table_stats('sys', 't1');
SQL> select x, count(*) from t1 group by x;
SQL>select * from DBA_TAB_STATISTICS where TABLE_NAME='T1';
SQL>select * from DBA_TAB_COL_STATISTICS where TABLE_NAME='T1'; 未搜集直方圖
SQL>select * from DBA_IND_STATISTICS where TABLE_NAME='T1';
SQL> set autot on
SQL> select * from t1 where x=1; rows不準確
SQL> select * from t1 where x=2;
SQL> exec dbms_stats.gather_table_stats('sys', 't1', method_opt => 'for columns x size skewonly'); 搜集列x的直方圖
SQL>select * from DBA_TAB_COL_STATISTICS where TABLE_NAME='T1';
SQL>select * from dba_histograms where table_name='T1' and column_name='X';
SQL> select * from t1 where x=1; 正確
SQL> select * from t1 where x=2; 正確
SQL> select count(distinct y) from t1; 超過254
SQL> select * from t1 where y like 'DBA%';
SQL> exec dbms_stats.gather_table_stats('sys', 't1', method_opt => 'for columns size auto y');
SQL>select * from DBA_TAB_COL_STATISTICS where TABLE_NAME='T1'; 創建等高直方圖
select COLUMN_NAME, HISTOGRAM, NUM_BUCKETS from DBA_TAB_COLUMNS where TABLE_NAME='T1';
共享游標shared curosr：
SQL> conn / as sysdba
SQL> create table t1 (x int);
SQL> insert into t1 values (1);
SQL> commit;
SQL> alter system flush shared_pool;
SQL> select * from t1;
查看父游標、子游標和執行計劃：
select sql_text, sql_id, plan_hash_value, version_count from v$sqlarea where sql_text like 'select * from t1';
select plan_hash_value, child_number from v$sql where sql_id='27uhu2q2xuu7r';
select * from v$sql_plan where plan_hash_value='3617692013';
SQL> conn hr/hr
SQL> create table t1(x int primary key);
SQL> insert into t1 values (1);
SQL> commit;
SQL> select * from t1;
再次查詢父游標、子游標和執行計劃：3個語句
SQL> select * from t1; 要求字面值完全一致
select sql_text, sql_id, plan_hash_value, version_count from v$sqlarea where sql_text like 'select * from %t1';
session cursor:
SQL> show parameter open_cursors
SQL> show parameter session_cached_cursors
SQL> select * from t1;
SQL> select distinct sid from v$mystat;
SQL>select * from v$open_cursor where sid=33;
性能差異：
SQL> create table t1(x int not null, y int);
SQL> create index t1_x_idx on t1(x);
SQL> insert into t1 select rownum, 11 from dual connect by level<=100;
SQL> commit;
SQL> exec dbms_stats.gather_table_stats('sys', 't1');
alter session set sql_trace=true;
begin
for i in 1..100 loop
execute immediate 'select * from t1 where x='||i;
end loop;
end;
/
begin
for i in 1..100 loop
execute immediate 'select * from t1 where x=:x' using i;
end loop;
end;
/
alter session set sql_trace=false;
bindingvariablepeeking和acs：
SQL> create table t1 (x int not null, y int);
SQL> create index t1_x_idx on t1(x);
SQL> insert into t1 select 1, 11 from dual connect by level<=10000;
SQL> insert into t1 values (2, 22);
SQL> commit;
SQL> exec dbms_stats.gather_table_stats('sys', 't1', method_opt=>'for all columns');
SQL>select * from DBA_TAB_COL_STATISTICS where TABLE_NAME='T1'; 搜集直方圖
SQL> alter session set optimizer_features_enable='10.2.0.1';
SQL> alter system flush shared_pool;
SQL> var x number;
SQL> exec :x := 1
SQL> select * from t1 where x=:x;
SQL> set pagesize 0
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'advanced'));
查看父游標、子游標：
select sql_text, sql_id, plan_hash_value, version_count, executions from v$sqlarea where sql_text like 'select * from t1 where x=%';
select plan_hash_value, child_number from v$sql where sql_id='8h3m8wg51m8nm';
select * from v$sql_plan where plan_hash_value='3617692013';
SQL> exec :x := 2
SQL> select * from t1 where x=:x;
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'advanced')); 錯誤
再次查看游標：3個語句
acs:
SQL> conn / as sysdba 恢復優化器版本
SQL> alter system flush shared_pool;
SQL> var x number;
SQL> exec :x := 1
SQL> select * from t1 where x=:x;
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'advanced'));
查看游標：3個語句
SQL> exec :x := 2
SQL> select * from t1 where x=:x;
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'advanced')); 不變
SQL> select * from t1 where x=:x;
SQL> select * from table(dbms_xplan.display_cursor(null, null, 'advanced')); 索引
查看游標：3個語句
latch和mutex：
shared pool latch數量:
select a.ksppinm, b.ksppstvl, a.ksppdesc
from x$ksppi a, x$ksppsv b
where a.indx=b.indx
and a.ksppinm='_kghdsidx_count';
SQL> create table t1 as select rownum x from dual connect by level<=500000;
SQL> exec dbms_stats.gather_table_stats('sys', 't1');
SQL> alter system flush shared_pool;
SQL> exec dbms_workload_repository.create_snapshot()
SQL> select distinct sid from v$mystat;
模擬硬解析：
begin
for i in 1..500000 loop
execute immediate 'select * from t1 where x='||i;
end loop;
end;
/
另一個session監控：
SQL>select * from v$session_wait where sid=143;
SQL> exec dbms_workload_repository.create_snapshot()
em做awr的報表和addm，ash報表和挖掘drilldown
mutex等待事件：
SQL> alter system set memory_target=0;
SQL> alter system set sga_target=0;
SQL> alter system flush shared_pool;
SQL> exec dbms_workload_repository.create_snapshot()
在兩個session中同時執行：
begin
loop
execute immediate 'alter system flush shared_pool';
for i in 1..1000 loop
execute immediate 'select * from t1 where x='||i;
end loop;
end loop;
end;
/
另一個session監控：
select * from v$session_wait where sid=143;
SQL> exec dbms_workload_repository.create_snapshot()
em做awr的報表和addm，ash報表和挖掘drilldown
Latch:cache buffer chains
SQL> create table t1(x int);
SQL> insert into t1 values (1);
SQL> commit;
SQL>select distinct sid from v$mystat;
SQL> exec dbms_workload_repository.create_snapshot()
兩個session同時執行：
declare
v1 int;
begin
for i in 1..99999999
loop
select count(*) into v1 from t1;
end loop;
end;
/
第3個session中：
SQL> select * from v$session_wait where sid in (136, 137);
SQL> exec dbms_workload_repository.create_snapshot()
Buffer busy waits
兩個session中運行：
declare
v1 int;
begin
for i in 1..99999999
loop
insert into t1 values (i);
end loop;
end;
/
第3個session中：
SQL> select * from v$session_wait where sid in (136, 137);
SQL> exec dbms_workload_repository.create_snapshot()
create table t1(x int, y char(1));
insert into t1 values (1, 'a');
insert into t1 values (2, 'a');
create index t1_x_idx on t1(x);
exec dbms_stats.gather_table_stats('sys', 't1');
為什么沒用索引？
select x from t1;
create table t1 as select 1 id, object_name from dba_objects;
update t1 set id=2 where rownum<=1;
commit;
select id, count(*) from t1 group by id;
create index t1_id_idx on t1(id);
exec dbms_stats.gather_table_stats('sys', 't1', METHOD_OPT => 'FOR ALL COLUMNS size 1');
為什沒用索引？
select * from t1 where x=2;
alter session set optimizer_mode='first_rows';
客戶抱怨響應時間長
select e.last_name, d.department_name
from hr.employees e, hr.departments d
where e.department_id=d.department_id;
create table t1 (x int);
create or replace procedure proc1
as
begin
for iin 1..100000 loop
execute immediate 'insert into t1 values ('||i||')';
commit;
end loop;
end;
/
客戶抱怨運行時間長
begin
proc1;
end;
/
1. 原始語句，動態sql，未使用綁定變量：
drop table t1 purge;
create table t1 (x int);
create or replace procedure proc1
as
begin
for iin 1..100000 loop
execute immediate 'insert into t1 values ('||i||')';
commit;
end loop;
end;
/
alter system flush shared_pool;
set timing on
exec proc1;
select count(*) from t1;
44秒完成。
select sql_text, sql_id, parse_calls, executions from v$sql where lower(sql_text) like '%insert into t1 values%';
動態sql靈活，處理ddl或dml的對象預先不存在的時候很方便，但在運行時才解析，性能差。
不使用綁定變量，每個語句都是解析一次，執行一次，效率差。
2. 改寫，使用綁定變量：
drop table t1 purge;
create table t1 (x int);
create or replace procedure proc1
as
begin
for i in 1..100000 loop
execute immediate 'insert into t1 values (:x)' using i;
commit;
end loop;
end;
/
alter system flush shared_pool;
set timing on
exec proc1;
12秒完成。
select sql_text, sql_id, parse_calls, executions from v$sql where lower(sql_text) like '%insert into t1 values%';
使用綁定變量，解析1次，執行10萬次。
3. 改寫，使用靜態sql：
drop table t1 purge;
create table t1 (x int);
create or replace procedure proc1
as
begin
for i in 1..100000 loop
insert into t1 values (i);
commit;
end loop;
end;
/
alter system flush shared_pool;
set timing on
exec proc1;
10秒完成。
select sql_text, sql_id, parse_calls, executions from v$sql where lower(sql_text) like '%insert into t1 values%';
靜態sql自定使用綁定變量，解析1次，執行10萬次。并且在編譯過程中就解析好了。
4. 改寫，批量提交：
drop table t1 purge;
create table t1 (x int);
create or replace procedure proc1
as
begin
for i in 1..100000 loop
insert into t1 values (i);
end loop;
commit;
end;
/
alter system flush shared_pool;
set timing on
exec proc1;
5秒完成。
5. 改寫，使用集合操作：
drop table t1 purge;
create table t1 (x int);
insert into t1 select rownum from dual connect by level<=100000;
或者
create or replace procedure proc1
as
TYPE dual_typ IS TABLE OF int
INDEX BY PLS_INTEGER;
dual_var dual_typ;
begin
SELECT rownum BULK COLLECT INTO dual_var
FROMdual connect by level<=100000;
forall i in dual_var.first .. dual_var.last
insert into t1 values (dual_var(i));
END;
/
alter system flush shared_pool;
set timing on
exec proc1;
0.12秒完成
將一條條插入改為一批寫入buffer的塊里。
6. 改寫，使用直接路徑：
drop table t1 purge;
create table t1 as select rownum x from dual connect by level<=100000;
0.08秒
insert into先寫內存再刷到磁盤，create table直接刷磁盤。
7. 改寫，使用并行：
drop table t1 purge;
create table t1 nologging parallel 16 as select rownum x from dual connect by level<=100000;
1. 創建虛擬機
名稱：node1_RAC_11gR2_rhel6u5_x64和node2_RAC_11gR2_rhel6u5_x64：
2.5-4g內存，引導：硬盤+cdrom，網卡1用hostonly，網卡2內部網絡
主機名：node1.test.com和node2.test.com
網絡：
第一塊網卡改名：eth0，勾選自動連接
手動ip：192.168.0.1/24，網關：192.168.0.254，dns：192.168.0.1,192.168.0.2
手動ip：192.168.0.2/24，網關：192.168.0.254，dns：192.168.0.1,192.168.0.2
第二塊網卡改名：eth2，勾選自動連接
手動ip：192.168.1.1/24
手動ip：192.168.1.2/24
時區：asia/shanghai
存儲：use all space，review，刪除/home，swap給4096MB，其他都給/
安裝包：desktop
2. 調整系統：
關閉防火墻：
service iptables stop
service ip6tables stop
chkconfig iptables off
chkconfig ip6tables off
管理工具中disabled防火墻
關閉selinux：
# vi /etc/selinux/config
SELINUX=disabled
配置yum：
# rm -f /etc/yum.repos.d/*
# vi /etc/yum.repos.d/rhel6.repo
[Server]
name=Server
baseurl=file:///media/"RHEL_6.5 x86_64 Disc 1"/Server
enabled=1
gpgcheck=0
安裝vb增強功能：
# yum -y install gcc kernel-devel
# ln -s /usr/src/kernels/2.6.32-431.el6.x86_64/ /usr/src/linux
設備-->安裝增強功能
右鍵eject彈出光盤
3. 硬件要求：
內存/swap/tmp/shared momory
# vi /etc/fstab（永久修改）
tmpfs /dev/shm tmpfs defaults,size=4G 0 0
# mount -o remount /dev/shm
臨時修改
# mount -t tmpfs shmfs -o size=4g /dev/shm
4. 設置用戶和目錄：
用戶：grid，oracle
群組：oinstall, asmadmin, asmdba, asmoper, dba, oper
groupadd -g 1000 oinstall
groupadd -g 1001 dba
groupadd -g 1002 oper
groupadd -g 1003 asmadmin
groupadd -g 1004 asmdba
groupadd -g 1005 asmoper
useradd -u 1000 -g oinstall -G dba,oper,asmdba oracle
useradd -u 1001 -g oinstall -G asmadmin,asmdba,asmoper grid
mkdir -p /u01/app/grid
mkdir -p /u01/app/11.2.0/grid
mkdir -p /u01/app/oracle
chown -R grid:oinstall /u01
chown oracle:oinstall /u01/app/oracle
chmod -R 775 /u01
passwd grid
passwd oracle
5. 設置userprofile文件：
# vi ~grid/.bash_profile
export ORACLE_SID=+ASM1 node2上改為+ASM2
export ORACLE_BASE=/u01/app/grid
export ORACLE_HOME=/u01/app/11.2.0/grid
export TNS_ADMIN=$ORACLE_HOME/network/admin
export PATH=$PATH:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
export EDITOR=vi
export
export NLS_LANG=american_america.AL32UTF8
export NLS_DATE_FORMAT='yyyy-mm-dd hh34:mi:ss'
umask 022
#vi ~grid/.bashrc
alias sqlplus='rlwrap sqlplus'
alias asmcmd='rlwrap asmcmd'
# vi ~oracle/.bash_profile
export ORACLE_SID=orcl1 node2上改為orcl2
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export TNS_ADMIN=$ORACLE_HOME/network/admin
export ORACLE_HOSTNAME=node1.test.com node2上改為node2.host.com
export ORACLE_UNQNAME=orcl
export PATH=$PATH:$ORACLE_HOME/bin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
export NLS_LANG=american_america.AL32UTF8
export NLS_DATE_FORMAT='yyyy-mm-dd hh34:mi:ss'
export EDITOR=vi
export
umask 022
#vi ~oracle/.bashrc
alias sqlplus='rlwrap sqlplus'
alias rman='rlwrap rman'
6. 修改資源限制：
# vi /etc/security/limits.conf
grid soft nofile 1024
grid hard nofile 65536
grid soft nproc 2047
grid hard nproc 16384
grid soft stack 10240
grid hard stack 32768
oracle soft nofile 1024
oracle hard nofile 65536
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft stack 10240
oracle hard stack 32768
7. 修改內核參數：
# vi /etc/sysctl.conf
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
kernel.shmmax = 2076053504
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
# sysctl -p
8. 安裝軟件包：
# yum -y install …
binutils-2.20.51.0.2-5.11.el6 (x86_64)
compat-libcap1-1.10-1 (x86_64)
compat-libstdc++-33-3.2.3-69.el6 (x86_64)
compat-libstdc++-33-3.2.3-69.el6.i686
gcc-4.4.4-13.el6 (x86_64)
gcc-c++-4.4.4-13.el6 (x86_64)
glibc-2.12-1.7.el6 (i686)
glibc-2.12-1.7.el6 (x86_64)
glibc-devel-2.12-1.7.el6 (x86_64)
glibc-devel-2.12-1.7.el6.i686
ksh
libgcc-4.4.4-13.el6 (i686)
libgcc-4.4.4-13.el6 (x86_64)
libstdc++-4.4.4-13.el6 (x86_64)
libstdc++-4.4.4-13.el6.i686
libstdc++-devel-4.4.4-13.el6 (x86_64)
libstdc++-devel-4.4.4-13.el6.i686
libaio-0.3.107-10.el6 (x86_64)
libaio-0.3.107-10.el6.i686
libaio-devel-0.3.107-10.el6 (x86_64)
libaio-devel-0.3.107-10.el6.i686
make-3.81-19.el6
sysstat-9.0.4-11.el6 (x86_64)
elfutils-libelf-devel
安裝rlwrap和bind
/installation/grid/rpm/cvuqdisk-1.0.9-1.rpm（用scp復制到node2）
9. 配置網絡：
node1:
public(eth0): 192.168.0.1/24 網關：192.168.0.254
private(eth2): 192.168.1.1/24
node1的virutal ip:192.168.0.11
node2:
public(eth0): 192.168.0.2/24 網關：192.168.0.254
private(eth2): 192.168.1.2/24
node2的virutal ip:192.168.0.12
scan和scanvip：scan.test.com 192.168.0.101/102/103
# vi /etc/hosts
#node1
192.168.0.1 node1.test.com node1 #public ip
192.168.1.1 node1-priv.test.com node1-priv #private ip
192.168.0.11 node1-vip.test.com node1-vip #node1 vip
#node2
192.168.0.2 node2.test.com node2 #public ip
192.168.1.2 node2-priv.test.com node2-priv #private ip
192.168.0.12 node2-vip.test.com node2-vip #node2 vip
node1配置主dns：
# vi /etc/named.conf
listen-on port 53 { any; };
listen-on-v6 port 53 { any; };
allow-query { any; };
dnssec-enable no;
dnssec-validation no;
# vi /etc/named.rfc1912.zones
zone "test.com" IN {
type master;
file "test.com.hosts";
};
zone "0.168.192.in-addr.arpa" IN {
type master;
file "192.168.0.rev";
};
# vi /var/named/test.com.hosts
$TTL 1D
@ IN SOA node1.test.com. root.node1.test.com. (
2016031601
3h
1h
1w
1h )
IN NS node1.test.com.
IN NS node2.test.com.
node1 IN A 192.168.0.1
node2 IN A 192.168.0.2
scan IN A 192.168.0.101
scan IN A 192.168.0.102
scan IN A 192.168.0.103
# vi /var/named/192.168.0.rev
$TTL 1D
@ IN SOA node1.test.com. root.node1.test.com. (
1
3h
1h
1w
1h )
IN NS node1.test.com.
IN NS node2.test.com.
1 IN PTR node1.test.com.
2 IN PTR node2.test.com.
101 IN PTR scan.test.com.
102 IN PTR scan.test.com.
103 IN PTR scan.test.com.
# service named start
# chkconfig --level 35 named on
# nslookup
測試localhost/127.0.0.1/node1/192.168.0.1/node2/192.168.0.2/scan/192.168.0.101(102,103)
node2配置輔助dns：
# vi /etc/named.conf
listen-on port 53 { any; };
listen-on-v6 port 53 { any; };
allow-query { any; };
dnssec-enable no;
dnssec-validation no;
# vi /etc/named.rfc1912.zones
zone "test.com" IN {
type slave;
file "slaves/test.com.hosts";
masters {192.168.0.1;};
};
zone "0.168.192.in-addr.arpa" IN {
type slave;
file "slaves/192.168.0.rev";
masters {192.168.0.1;};
};
# service named start
chkconfig --level 35 named on
# nslookup - 192.168.0.2
測試localhost/127.0.0.1/node1/192.168.0.1/node2/192.168.0.2/scan/192.168.0.101(102,103)
10. ntp
#service ntpd stop
# chkconfig ntpd off
# mv /etc/ntp.conf /etc/ntp.conf.bak
11. 配置共享存儲
SAN/NAS
ocr/voting disk: 3個1GB(+CRS)
data: 2個10GB(+DATA)
fra: 1個10GB(+FRA)
創建共享磁盤的子目錄：/root/virtualbox vms/shared_disk
關閉node1/node2
node1添加6塊磁盤（固定大小）：
/root/virtualbox vms/shared_disk/asmdisk1.vdi
vb將6塊硬盤改為可共享
node2添加6塊共享的磁盤
# ll /dev/sd*
執行命令：
#for i in b c d e f g ;
do
echo "KERNEL==\"sd*\", BUS==\"scsi\", PROGRAM==\"/sbin/scsi_id --whitelisted --replace-whitespace --device=/dev/\$name\", RESULT==\"`/sbin/scsi_id --whitelisted --replace-whitespace --device=/dev/sd$i`\", NAME=\"asm-disk$i\", OWNER=\"grid\", GROUP=\"asmadmin\", MODE=\"0660\"" >> /etc/udev/rules.d/99-oracle-asmdevices.rules
done
# start_udev;ls /dev/asm* 確認生成asmdisk。
12. node1/node2設置臨時共享
# mkdir /oracle; mount -t vboxsf oracle /oracle
# cd /oracle/software/
# yum -y install rlwrap-0.42-1.el6.x86_64.rpm
# cd /oracle/installation/grid/
# yum -y install cvuqdisk-1.0.9-1.rpm
13. node1安裝gi
# xhost +
# su - grid
$ cd /oracle/installation/grid/
$ ./runInstaller
高級安裝，集群名稱：test-cluster, scan name: scan.test.com，不配置gns，添加node2，配置ssh
asm: 磁盤組名稱：CRS，normal方式，搜索路徑：/dev/asm*，使用b、c、d三塊硬盤
14. 測試gi：
# su - grid
$ crsctl check crs
$ crsctl stat res -t
$ srvctl status asm
15. node1創建asm磁盤組：
# su - grid
$ asmca
data: 2個10GB（normal）
fra: 1個10GB（external）
16. node1上安裝db：
# su - oracle
$ cd /oracle/installation/database/
$ ./runInstaller
只安裝軟件，rac方式，選擇全部節點，oracle口令，ssh連接
17. node1上創建db：
dbca，rac，數據庫orcl，node1/node2，存儲asm，data磁盤組，fra使用+FRA磁盤組，sampleschema，內存800MB，字符集al32utf8
問題：
查看數據庫的字符集：
SQL> select * from v$nls_parameters;
刪除asm磁盤的頭部信息：
#dd if=/dev/zero of=/dev/sdb bs=1M count=1
手動建立ssh信任關系：
node1/node2上
# su-grid
$ mkdir ~/.ssh
$ chmod 700 ~/.ssh
$ ssh-keygen -t rsa
$ ssh-copy-id 192.168.0.2 node1上
$ ssh-copy-id 192.168.0.1 node2上
ssh node1 date
ssh node2 date
ssh node1-priv date
ssh node2-priv date
關閉自動掛載，避免桌面崩潰：
chmod -x /usr/libexec/gvfs-gdu-volume-monitor
vi /etc/init/oracle-ohasd.conf ohasd啟動項
ps -ef | more has相關的進程
# /u01/app/11.2.0/grid/bin/crsctl stat res -init-t crs和ohas的資源
# /u01/app/11.2.0/grid/bin/crsctl stat res -t crs所管理的資源
兩個實例連接db（通過scan-vip和node-vip兩種方式），添加數據測試。
node1/node2:
# su - oracle
$ sqlplus / as sysdba
SQL> select instance_name from v$instance;
SQL> select name from v$database;
$ sqlplus sys/password@scan.test.com:1521/orcl as sysdba 多創建連接
$ sqlplus sys/password@192.168.0.11:1521/orcl as sysdba
$ sqlplus sys/password@192.168.0.12:1521/orcl as sysdba
啟動/關閉crs和資源，在node1和node2上都要執行（root身份）：
# /u01/app/11.2.0/grid/bin/crsctlstopcrs[-f]
# /u01/app/11.2.0/grid/bin/crsctl start crs
啟動/關閉資源，在一個節點上執行（root身份）：
# /u01/app/11.2.0/grid/bin/crsctlstop cluster -all
# /u01/app/11.2.0/grid/bin/crsctlstart cluster -all
查詢資源（grid用戶就可以）：
$ crsctlstatres-t
$ olsnodes -h
實例管理
啟動關閉
sqlplus（oracle用戶）/oem/srvctl（grid用戶）
實例級別：
# su - oracle
$ sqlplus / as sysdba
SQL> shutdown immediate
# su - grid
$ srvctl status instance -d orcl -i orcl1,orcl2 查看實例狀態
$ srvctl stop instance -d orcl -i orcl1 -o immediate 關閉任意節點上實例
$ srvctl start instance -d orcl -i orcl1
數據庫級別：
$ srvctl status db -d orcl
$ srvctl stop db -d orcl -o immediate
$ srvctl start db -d orcl -o mount
$ srvctl modify db -d orcl -s open
$ srvctl config db -d orcl -a
$ srvctl modify db -d orcl -y manual
$ srvctl modify db -d orcl -y automatic
asm實例
# su - grid
$ ps -ef | grep asm*
$ sqlplus / as sysasm
SQL> startup|shutdown immediate 每個節點上單獨執行
$ srvctl status asm
$ srvctl stop asm -n node1 -o abort -f
$ srvctl start asm -n node1
spfile
查看：
# su - oracle
$ sqlplus / as sysdba
orcl1> show parameter spfile
# su - grid
$ asmcmd
ASMCMD> cd +data/orcl
# su - oracle
$ sqlplus / as sysdba
orcl1> create pfile='/home/oracle/pfile.ora' from spfile;
$ vi /home/oracle/pfile.ora
修改：
orcl1和orcl2：
# su - oracle
$ sqlplus / as sysdba
orcl1> show parameter open_cursors
orcl1> alter system set open_cursors=600;
SQL> alter system set open_cursors=600 sid='*'; 相同
orcl2> show parameter open_cursors
orcl1> alter system set open_cursors=800 sid='orcl2';
orcl2> show parameter open_cursors
恢復：
orcl1> alter system reset open_cursors sid='orcl2';
orcl1> alter system reset open_cursors sid='*';
orcl1> alter system set open_cursors=300 sid='*';
存儲：
SQL> show parameter control_files
SQL> show parameter undo_tablespace
SQL> select GROUP#, THREAD#, STATUS, MEMBERS from v$log;
SQL> select GROUP#, MEMBER from v$logfile;
SQL>ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 5 SIZE 50M;
SQL>ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 6 SIZE 50M;
# vi /etc/oracle/ocr.loc ocr位置
# cd /u01/app/11.2.0/grid/bin
#./ocrcheck
#./ocrconfig -showbackup 查看主節點上的自動備份
# ./ocrconfig -manualbackup 手動備份，root身份，保存在主節點
# ./ocrconfig-backuploc … 改備份路徑
# ./ocrdump;vi OCRDUMPFILE
# rm OCRDUMPFILE
# ./ocrdump -h
# ./ocrcheck -config
# ./ocrconfig -add +DATA 鏡像
# ./ocrcheck -config
# cat /etc/oracle/ocr.loc
# ./ocrconfig -delete +CRS 刪除鏡像
恢復：
# ./ocrconfig -add +CRS
# ./ocrconfig -delete +DATA
olr：
# ll /u01/app/11.2.0/grid/cdata/node1.olr
# ./ocrcheck -local
# ./ocrdump -local /root/node1.olr
# vi /root/node1.olr
# ./ocrconfig -local -manualbackup
votingdisk：
# ./crsctl query css votedisk
public和private：
#ifconfig或者ip add
#oifcfg getif
node vip和scanvip：
# ip add
$ sqlplus sys/password@192.168.0.1:1521/orcl as sysdba
$ sqlplus sys/password@192.168.0.11:1521/orcl as sysdba
$ sqlplus sys/password@192.168.0.101:1521/orcl as sysdba 假定101在node1上
# ifdown eth0 node1關閉網卡，觀察ip的漂移
# ip add 或 $ crsctl stat res -t
$ sqlplus sys/password@192.168.0.1:1521/orcl as sysdba
$ sqlplus sys/password@192.168.0.11:1521/orcl as sysdba
$ sqlplus sys/password@192.168.0.101:1521/orcl as sysdba
# ifup eth0 node1開啟網卡，觀察ip的漂移
再次測試3個連接
listener:
# su - grid
$ lsnrctl status
$ lsnrctl status listener_scan1/2/3
# su - oracle
$ sqlplus / as sysdba
SQL> show parameter listener 查看和監聽相關的參數
$ srvctl relocate vip(scan) -h
$ srvctl relocate scan -i 1 -n node1
private ip的自動管理（haip）：
$ crsctl stat res -t -init 確認ohasd維護的haip基礎服務
$ ifconfig
$ oifcfg getif
$ oifcfg iflist -p -n
SQL> select name, ip_address from v$cluster_interconnects;
網絡修改：
修改public hostname：重新安裝rac
修改privatehostname：11.2.0.2以前重裝rac，11.2.0.2以后在/etc/hosts隨意改
修改public/privateip：相同網絡，重啟rac，不同的網絡，修改orc
修改node vip/scan vip：使用srvctl修改
publiceth0: 192.168.0.1/24 à 172.16.0.1/16
private eth2: 192.168.1.1/24 à 10.0.0.0/8
node vip: 192.168.0.11/24 à 172.16.0.11/16
scan vip: 192.168.0.100(1 2) à 172.16.0.100(1 2)/16
修改publicip：
# vi /etc/sysconfig/network-scripts/ifcfg-eth0 node1/node2的os中修改publicip
node1上：
# su - grid
$ oifcfg getif
$ oifcfg delif -global eth0
$ oifcfg setif -global eth0/172.16.0.0:public
ASM=RAID+LVM
SAME
asm的實例：
$ ps -ef | grep asm
# su - grid
$ sqlplus / as sysasm
+ASM1> show parameter memory
+ASM1> select component, current_size from v$sga_dynamic_components;
+ASM1> show parameter listener
啟動關閉實例：
SQL> startup/shutdown abort 啟動到nomount階段停止
$ srvctl start asm
$ srvctl stop asm -f
SQL> show parameter
diskgroup：
條帶化RAID 0，鏡像RAID1，RAID1+0
vb的node1添加3塊1GB硬盤，共享給node2
# for i in h i j ;
do
echo "KERNEL==\"sd*\", BUS==\"scsi\", PROGRAM==\"/sbin/scsi_id --whitelisted --replace-whitespace --device=/dev/\$name\", RESULT==\"`/sbin/scsi_id --whitelisted --replace-whitespace --device=/dev/sd$i`\", NAME=\"asm-disk$i\", OWNER=\"grid\", GROUP=\"asmadmin\", MODE=\"0660\"" >> /etc/udev/rules.d/99-oracle-asmdevices.rules
done
# start_udev;ls /dev/asm* 確認生成asmdisk。
使用sqlplus/oem/asmca/asmcmd
grid用戶登錄桌面，asmca創建diskgroup：testdg，normal方式。
# su - grid
$ sqlplus / as sysasm
+ASM1> select NAME, STATE, TOTAL_MB, FREE_MB, USABLE_FILE_MB from v$asm_diskgroup;
$ asmcmd
ASMCMD> lsdg
# su - oracle
$ sqlplus / as sysdba
orcl1> create tablespace tbs01 datafile '+testdg' size 600M;
orcl1> select path, failgroup, free_mb from v$asm_disk where group_number=4;
orcl 1> select NAME, STATE, TOTAL_MB, FREE_MB, USABLE_FILE_MB from v$asm_diskgroup;
ASMCMD> lsdsk -k
添加/刪除磁盤：
# su - grid
+ASM1> show parameter asm_power_limit
+ASM1> select name, total_mb, free_mb from v$asm_disk;
+ASM1> alter diskgroup testdg add disk '/dev/asm-diskj';
+ASM1> select name, total_mb, free_mb from v$asm_disk;
+ASM1> alter diskgroup testdg drop disk TESTDG_0002;
+ASM1> select name, total_mb, free_mb from v$asm_disk;
failgroup:
+ASM1> select path, FAILGROUP from v$asm_disk where group_number=4;
+ASM1> alter diskgroup testdg drop disk TESTDG_0002;
+ASM1> alter diskgroup testdg add failgroup testdg_0000 disk '/dev/asm-diskj';
+ASM1> select name, total_mb, free_mb from v$asm_disk;
oracle用戶刪除表空間，grid用戶asmca刪除testdg，root刪除/etc/udev/rules.d/99-oracle-asmdevices.rules中最后3塊硬盤
SQL> startup mount
SQL> select FILE#, NAME from v$datafile;
SQL> alter database datafile 4 offline;
SQL> alter database open;
SQL> drop tablespace tbs01 force; (including contents and datafiles)
scanvip的HA
nslookup解析scan返回3個ip，實現ha
# su - grid
$ srvctl status scan
$ srvctl status scan_listener
# su - oracle
$ tnsping orcl
# su - grid
$ srvctl stop scan_listener -i 1(23)
$ srvctl stop scan -i 1(23)
逐一關閉scanlistener和scanvip，測試客戶端的ha
# su - grid
$ srvctl stop instance -d orcl -i orcl1 -o immediate
# su - oracle
$ sqlplus sys/password@orcl as sysdba
SQL> select instance_name from v$instance;
node vip的HA
node1和node2上修改tnsnames本地解析
# su - oracle
$ vi $ORACLE_HOME/network/admin/tnsnames.ora
TESTHA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.11)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.12)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
$ sqlplus sys/password@testha as sysdba 用node2上的客戶端測試
testha> select instance_name from v$instance; 始終連接到orcl1
# ifdown eth0 關閉node1的網卡
$ sqlplus sys/password@testha as sysdba 用node2上的客戶端測試
testha> select instance_name from v$instance; 連接到orcl2
node1恢復eth0，客戶端重新連接orcl1
scanvip的LB
建立多個session，自動分配給orcl1和orcl2
$ sqlplus sys/password@orcl as sysdba
SQL> select instance_name from v$instance;
nodevip的LB
# su - oracle
$ vi $ORACLE_HOME/network/admin/tnsnames.ora
TESTLB =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.11)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.12)(PORT = 1521))
(LOAD_BALANCE = yes)
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
建立多個session，自動分配給orcl1和orcl2
$ sqlplus sys/password@testlb as sysdba
SQL> select instance_name from v$instance;
db name, instance name, global name, sid, service name, db_unique_name
orcl db name
orcl global name(service name)
查詢現有服務：
SQL> select name from v$database;
SQL> select instance_name from v$instance;
SQL> select name from v$services;
$ vi tnsnames.ora; lsnrctl status
# su - grid
$ srvctl status service -d orcl
創建服務：
# su - oracle
$ srvctl add service -d orcl -s testsvc -r orcl1 -a orcl2
# su - grid
$ srvctl start service -d orcl -s testsvc
$ crsctl stat res -t
$ lsnrctl status; lsnrctl listener_scan1
# su - oracle
$ sqlplus sys/password@scan.test.com:1521/testsvc as sysdba
SQL> select instance_name from v$instance;
SQL> select name from v$database;
netca添加testsvc的解析
SQL> shutdown immediate 關閉orcl1
$ sqlplus sys/password@scan.test.com:1521/testsvc as sysdba
SQL> select instance_name from v$instance; 連接到orcl2
# su - grid
$ crsctl stat res -t
$ srvctl status service -d orcl -s testsvc
$ srvctl start instance -d orcl -i orcl1
$ crsctl stat res -t testsvc還在orcl2上，不會自動failback
$ srvctl relocate service -d orcl -s testsvc -i orcl2 -t orcl1
$ crsctl stat res -t
$ srvctl stop service -d orcl -s testsvc
$ srvctl remove service -d orcl -s testsvc
resource manager/scheduler/sql trace
ops
內存>網絡>磁盤
node1：
# su - oracle
$ sqlplus / as sysdba
orcl1> create tablespace tbs01;
orcl1> create table t1 (x int, y int) tablespace tbs01;
orcl1> insert into t1 values (1, 1);
orcl1> insert into t1 values (2, 2);
orcl1> commit;
orcl1> select dbms_rowid.rowid_relative_fno(rowid), dbms_rowid.rowid_block_number(rowid) from t1;
orcl1> alter system checkpint;
orcl1> alter system flush buffer_cache;
node1:
begin
for i in 1..10000 loop
update t1 set y=i where x=1;
end loop;
end;
/
node2:
begin
for i in 1..10000 loop
update t1 set y=i where x=2;
end loop;
end;
/
xcuràpiàcr
add:
node3做所有準備工作
node1上驗證：
#su - grid
$ cluvfy stage -pre nodeadd -n node3
$ ./addNode.sh "CLUSTER_NEW_NODES={node3}" "CLUSTER_NEW_VIRTUAL_HOSTNAMES={node3-vip}"
node3上root身份執行root.sh
node1上運行dbca添加新實例
crsctl stat res -t
node1和node2安裝os，db軟件，創建監聽，node1上創建orcl數據庫。
或者
vb復制單實例虛擬機，重新初始化網卡，將兩個節點重命名為：
node1_DG_11gR2_RHEL6u5_x64
node2_DG_11gR2_RHEL6u5_x64
node1恢復網絡設置：
# vi /etc/udev/rules.d/70-persistent-net.rules 刪除2行eth0的信息，將eth2改為eth0
# vi /etc/sysconfig/network-scripts/ifcfg-eth0 替換原有的mac地址
#shutdown -h now
node2恢復網絡設置：
# vi /etc/udev/rules.d/70-persistent-net.rules 刪除2行eth0的信息，將eth2改為eth0
# vi /etc/sysconfig/network-scripts/ifcfg-eth0 替換原有的mac地址
#reboot
oracle登錄桌面，dbca刪除orcl數據庫
root身份修改hostname和ip：
# vi /etc/sysconfig/network-scripts/ifcfg-eth0 ip改為192.168.0.2
# vi /etc/sysconfig/network 改為node2.test.com
# hostname node2.test.com
# vi /etc/hosts
192.168.0.1 node1.test.com node1
192.168.0.2 node2.test.com node2
臨時關閉oracle服務（可選）：
# chkconfig oracle off
# chkconfig --list oracle
oracle身份調整node2的環境：
$ vi $ORACLE_HOME/network/admin/listener.ora 改為node2.test.com
$ lsnrctl stop; lsnrctl start
$ vi ~oracle/.bash_profile
export ORACLE_HOSTNAME=node2.test.com
export ORACLE_SID=orclps
export ORACLE_UNQNAME=orclps
$ . ~oracle/.bash_profile
node1(primary)：
# vi /etc/hosts
# su - oracle
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;
SQL> alter database force logging;
node1(primary)修改主數據庫參數：
SQL> alter system set log_archive_config='dg_config=(orcl,orclps)';
SQL> alter system set log_archive_dest_2='service=orclps async valid_for=(online_logfile,primary_role) db_unique_name=orclps';
添加備用數據庫參數：
SQL> alter system set fal_server=orclps;
SQL> alter system set fal_client=orcl;
SQL> alter system set standby_file_management=auto;
SQL> alter system set db_file_name_convert='/orclps/','/orcl/' scope=spfile;
SQL> alter system set log_file_name_convert='/orclps/','/orcl/' scope=spfile;
$ vi /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
ORCLPS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node2.test.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orclps)
)
)
node2(ps):
# su - oracle
$ mkdir -p $ORACLE_BASE/fast_recovery_area/orclps
$ mkdir -p $ORACLE_BASE/admin/orclps/adump
$ mkdir -p $ORACLE_BASE/admin/orclps/dpdump
$ mkdir -p $ORACLE_BASE/oradata/orclps
$ vi $ORACLE_HOME/network/admin/listener.ora 添加靜態注冊
SID_LIST_listener=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=orclps)
(SID_NAME=orclps)
(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)))
$ lsnrctl reload; lsnrctl status
$ vi $ORACLE_HOME/network/admin/tnsnames.ora
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node1.test.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
ORCLPS =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = node2.test.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orclps)
)
)
$ tnsping orcl 測試
在node2上生成口令文件：
$ scp node1:$ORACLE_HOME/dbs/orapworcl $ORACLE_HOME/dbs/orapworclps
node1上生成pfile：
SQL> create pfile from spfile;
node2上生成spfile：
$ scp node1:$ORACLE_HOME/dbs/initorcl.ora $ORACLE_HOME/dbs/initorclps.ora
node2上刪除orcl.開頭的參數，修改如下參數：
$ vi $ORACLE_HOME/dbs/initorclps.ora
*.audit_file_dest='/u01/app/oracle/admin/orclps/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/orclps/control01.ctl','/u01/app/oracle/fast_recovery_area/orclps/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_name_convert='/orcl/','/orclps/'
*.db_name='orcl'
*.db_unique_name='orclps'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4322230272
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclpsXDB)'
*.fal_client='ORCLPS'
*.fal_server='ORCL'
*.log_archive_config='dg_config=(orcl,orclps)'
*.log_archive_dest_2='service=orcl async valid_for=(online_logfile,primary_role) db_unique_name=orcl'
*.log_file_name_convert='/orcl/','/orclps/'
*.memory_target=1073741824
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'
*.undo_tablespace='UNDOTBS1'
SQL> create spfile from pfile;
SQL> startup nomount
$ rman target sys/password@orcl auxiliary sys/password@orclps
RMAN> duplicate target database for standby from active database dorecover;
如果主和備的數據目錄相同，需要加nofilenamecheck
SQL> select status from v$instance; mount狀態
如果需要手動啟動備用數據庫：
SQL> startup nomount
SQL> alter database mount standby database;
node1(primary)創建srl：
SQL> alter database add standby logfile '/u01/app/oracle/oradata/orcl/srl01(234).log' size 50M;
node2(ps)創建srl：
SQL> alter database add standby logfile '/u01/app/oracle/oradata/orclps/srl01(234).log' size 50M;
node2(ps)開啟redoapply：
SQL> alter database recover managed standby database using current logfile disconnect;
SQL> select name from v$datafile;
SQL> select name from v$tempfile;
SQL> select name from v$controlfile;
SQL> select member from v$logfile;
SQL> select protection_mode, protection_level from v$database;
SQL> select sequence#, applied from v$archived_log;
SQL> alter system switch logfile; node1上切換日志
SQL> select sequence#, applied from v$archived_log; 出現新的歸檔
$ vi /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora 刪除靜態注冊
刪除init參數文件（可選）
node2(ps)修改啟動腳本：
# vi /etc/init.d/oracle
#!/bin/bash
#chkconfig:35 99 01
case "$1" in
start)
su - oracle -c "sqlplus /nolog" <<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;
測試
逐級修改保護模式，不需要重啟db，performanceàavailablity要等待resync完成，再àprotection
恢復為最大性能：
SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE;
SQL> alter system set log_archive_dest_2='service=orclps async valid_for=(online_logfile,primary_role) db_unique_name=orclps';
只讀模式打開
node1(primary)
SQL> create table t1(x int); insert into t1 values (1); commit;
node2(standby)
SQL> select open_mode from v$database; 確認是mount
SQL> recover managed standby database cancel;
SQL> alter database open;
SQL> select open_mode from v$database; read only
SQL> select * from t1;
SQL> insert into t1 values (2); commit; node1上做
SQL> select * from t1; 和node1不同步
SQL> delete t1; 報錯
SQL> alter system switch logfile; node1切換日志
SQL> select sequence#, applied from v$archived_log; 傳輸但不應用
SQL> shutdown immediate
SQL> startup mount
SQL> alter database recover managed standby database using current logfile disconnect;
SQL> select sequence#, applied from v$archived_log; 應用node1的日志
快照備用
node1(primary)
SQL> create table t1(x int); insert into t1 values (1); commit;
node2(standby)
SQL> show parameter db_recovery_file_dest
SQL> recover managed standby database cancel;
SQL> shutdown immediate
SQL> startup mount
SQL> alter database convert to snapshot standby;
SQL> alter database open;
SQL> select open_mode from v$database; read write
$ ls /u01/app/oracle/fast_recovery_area/ORCLPS/flashback
SQL> select * from t1;
SQL> insert into t1 values (1); commit; node1修改
SQL> select * from t1;insert into t1 values (2);commit; 無法看到node1修改，但自己可以修改
SQL> alter system switch logfile; node1切換日志
SQL> select sequence#, applied from v$archived_log; 傳輸但不應用
SQL> shutdown immediate
SQL> startup mount
SQL> alter database convert to physical standby; node2丟失更改
SQL> shutdown immediate
SQL> startup mount
SQL> alter database recover managed standby database using current logfile disconnect;
SQL> select sequence#, applied from v$archived_log; node2應用日志
快照備用+flashbackdb
node1(primary)
SQL> create table t1(x int); insert into t1 values (1); commit;
node2(standby)
確認數據庫在mount狀態
SQL> show parameter db_recovery_file_dest
SQL> recover managed standby database cancel;
SQL> alter database flashback on;
SQL> alter database convert to snapshot standby;
SQL> alter database open;
SQL> select open_mode from v$database; read write
SQL> insert into t1 values(2); commit; 導入測試數據
SQL> create restore point before_test GUARANTEE flashback database;
SQL> delete t1;commit;
SQL> shutdown immediate
SQL> startup mount
SQL> flashback database to restore point before_test;
SQL> alter database open resetlogs;
SQL> select * from t1;
SQL> shutdown immediate
SQL> startup mount
SQL> alter database convert to physical standby; node2丟失更改
SQL> shutdown immediate
SQL> startup mount
SQL> alter database recover managed standby database using current logfile disconnect;
SQL> drop restore point before_test;
ADG
node1(primary)
SQL> create table t1(x int); insert into t1 values (1); commit;
node2(standby)
SQL> select open_mode from v$database; 確認是mount
SQL> recover managed standby database cancel;
SQL> alter database open; 或者SQL> startup
SQL> alter database recover managed standby database using current logfile disconnect;
SQL> insert into t1 values (2);commit; node1上修改
SQL> select * from t1; node2上查看實時數據
node1(primary)的準備：
fal_server/fal_client/standby_file_management
創建srl
node2(standby)
log_archive_dest_2/log_archive_config
node1(primary)確認日志傳輸完整（nogap）
SQL>SELECT STATUS, GAP_STATUS FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID = 2;
node2(standby)確認兩個lag
SQL> SELECT NAME, VALUE, DATUM_TIME FROM V$DATAGUARD_STATS;
node1(primary)
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; 應該是TO STANDBY
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
SQL> SHUTDOWN ABORT;
node2(standbyànew primary)
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; 應該是TO PRIMARY
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
SQL> ALTER DATABASE OPEN;
node1(primaryànew standby)
SQL> STARTUP MOUNT;
SQL> alter database recover managed standby database using current logfile disconnect;
node2(primary)和node1(standby)開啟flashback
node2(primary)準備：
SQL>create table t1(x int); insert into t1 values (1); commit;
node2(primary)模擬故障：
# ifdown eth0 node2(primary)關閉網絡
SQL> insert into t1 values (2); commit;
SQL> shutdown abort
node1(standby)
SQL> recover managed standby database cancel;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SQL>ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
SQL> ALTER DATABASE OPEN;
利用flashback恢復node2(primaryànew standby)
node1(new primary)
SQL> SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FROM V$DATABASE;
node2(new standby)
SQL> startup mount
SQL> FLASHBACK DATABASE TO SCN 1275936;
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
基于oracle的ATM實例
11.2.0.3.0à 11.2.0.4.0
# mkdir /patch
# chmod 777 /patch
# mount -t vboxsf patch /patch
$ unzip p13390677_112040_Linux-x86-64_1(2)of7.zip
停服務：
$ emctl stop dbconsole
$ lsnrctl stop
$ sqlplus / as sysdba
SQL> shutdown immediate
改環境：
$ vi ~/.bash_profile
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4/db_1
$ . .bash_profile 桌面環境需要重新登錄
$ echo $ORACLE_HOME
$ ./runInstaller
選擇upgrade，路徑確認改為/u01/app/oracle/product/11.2.0.4/db_1
# /u01/app/oracle/product/11.2.0.4/db_1/root.sh 提示以root執行腳本
netca：創建新版本listener和service解析
dbua: 不移動文件
SQL> select * from v$version; db的版本
SQL> select comp_name, version from dba_server_registry; 組件的版本
$ rm -rf /u01/app/oracle/product/11.2.0 確定成功，刪除老版本
11.2.0.4.0à 11.2.0.4.8
$ $ORACLE_HOME/OPatch/opatch version
$ unzip p6880880_112000_Linux-x86-64.zip -d $ORACLE_HOME 必須用oracle用戶身份
$ $ORACLE_HOME/OPatch/opatch version
$ cd /patch
$ unzip p21352635_112040_Linux-x86-64\(11.2.0.4.8_db\).zip 必須用oracle用戶解壓縮
$ cd 21352635
$ emctl stop dbconsole; dbshut $ORACLE_HOME 關閉db所有組件
$ $ORACLE_HOME/OPatch/opatch apply
不輸入email，確認繼續
$ dbstart $ORACLE_HOME; emctl start dbconsole
SQL> @?/rdbms/admin/catbundle.sql psu apply
檢查：
$ $ORACLE_HOME/OPatch/opatch lspatches
SQL> select action, comments from registry$history;
在node2上的/etc/oratab中添加數據庫
$ vi /etc/oratab
orclps:/u01/app/oracle/product/11.2.0/db_1:Y
node2上不配置em
在node1上選擇“只安裝軟件”
復制老版本路徑下的listener.ora, sqlnet.ora, tnsnames.ora spfileorcl.ora orapworcl至新版本對應目錄
安裝軟件失敗：
$ vi /u01/app/oraInventory/ContentsXML/inventory.xml
升級gi：
node1和node2：
# chown grid /u01/app
$ su - grid
$ vi .bash_profile
export ORACLE_HOME=/u01/app/11.2.0.4/grid
$ . .bash_profile
node1：
桌面root登錄
#xhost +
$ su - grid
$ /patch/grid/runInstaller
upgrade gi & asm，測試ssh連接（不用配置），路徑改為/u01/app/11.2.0.4/grid
在node1和node2上運行rootupgrade.sh
$ crsctl query crs activeversion 確認新版本
升級dbsoftware：
node1和node2：
# su - oracle
$ vi .bash_profile
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0.4/db_1
$ . .bash_profile
node1：
$ ./runInstaller
選擇upgrade existing database，選中node1/node2，ssh輸入口令，路徑改為/u01/app/oracle/product/11.2.0.4/db_1，node1/node2執行root腳本，dbua升級數據庫。
升級psu：
node1和node2：
su - grid
unzip p6880880_112000_linux-x86-64.zip -d $ORACLE_HOME
$ORACLE_HOME/OPatch/opatch version
su - oracle
unzip p6880880_112000_linux-x86-64.zip -d $ORACLE_HOME
$ORACLE_HOME/OPatch/opatch version
node1：
su - grid
$ unzip p21523375_112040_Linux-x86-64(11.2.0.4.8_gi&db).zip
node1和node2：
su - oracle
emctl stop dbconsole
node1和node2上，root生成ocm響應文件，應用psu：
# cd /patch/21523375/
#/u01/app/11.2.0.4/grid/OPatch/ocm/bin/emocmrsp -no_banner
#/u01/app/11.2.0.4/grid/OPatch/opatch auto /patch/21523375/ -ocmrf /patch/21523375/ocm.rsp
node1上：
su - oracle
sqlplus / as sysdba
SQL> @?/rdbms/admin/catbundle.sql psu apply
檢查：
$ORACLE_HOME/OPatch/opatch lspatches
sqlplus / as sysdba
select * from v$version;
select action,comments from registry$history;
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
