oracle学习笔记
oracle安装
1. 安装virtualbox：
# yum install gcc kernel-devel kernel-headers
# yum install virtualbox-5....
# /etc/init.d/vboxdrv setup 手动编译内核模块，安装时自动完成
# usermod -G vboxusers root
2. 安装linux：
创建虚拟机：
名称：oracle11gR2_RHEL6.4_x64
类型：linux 64bit
内存：2048MB
硬盘大小：100GB
设置：
启动顺序：硬盘，网络
网络：网卡1，桥接eth0
去除声音、usb设备
安装：desktop方式
主机名：node1.test.com，ip使用dhcp，swap4GB，其余给/
3. 调整linux系统：
关闭防火墙：
# service iptables stop
# service ip6tables stop
# chkconfig iptables off
# chkconfig ip6tables off
管理工具中disabled防火墙
关闭selinux：
# vi /etc/selinux/config
SELINUX=disabled
配置yum：
# rm -f /etc/yum.repos.d count(*) from (select * 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;
SAA
SQL> alter system flush shared_pool;
SQL> grant dba to hr;
SQL> conn hr/hr
SQL> set autot on
SQL> select e.last_name, d.department_name
from employees e, departments d
where e.department_id=d.department_id;
em中执行saa，过滤条件为表：hr.employees, hr.departments
STA
SQL> alter system flush shared_pool;
SQL> conn hr/hr
SQL> set autot on
SQL> select * from employees where employee_id=100;
em中创建tuningset，调用sta分析
SQL优化
优化器
参考：optimizer介绍.ppt
查询改写：
谓词传递：
SQL> set autot trace exp
SQL>select e.last_name, d.department_name
from hr.employees e, hr.departments d
where e.department_id=d.department_id
and e.department_id=50;
自动添加3 - access("D"."DEPARTMENT_ID"=50)谓词
for i in 1 .. 107(employees)
for j in 1 .. 27(departments)
i的部门=j的部门而且 i的部门=50
endloop
endloop
for i in 1 .. 10 (employeesin deptno 50)
for j in 1 .. 27(departments)
i的部门=j的部门
endloop
endloop
for i in 1 .. 10 (employeesin deptno 50)
i的部门=50
endloop
子查询解嵌套：
SQL> select last_name
from hr.employees outer
where salary >
(select avg(salary) from hr.employees
where department_id = outer.department_id);
被改写为多表连接
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 * from t1 where id=1;
SQL> select * from t1 where id=2; 错误
SQL> select * 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 * from t1 where id=1; 走索引，错误
SQL> select * 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 e.last_name, d.department_name
from hr.employees e, hr.departments d
where e.department_id=d.department_id; 使用sort merge
select 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;
优化器操作
full table scan
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的使用方式
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 * 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 count(*) from t1; full scan的开销大
SQL> select count(*) from t1;
SQL> select 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 * 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 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 invisible; 禁用emp上的外键索引
执行多表连接时，将dept的主键索引作为内部被驱动表，nl会尽量避免对被驱动表的全表扫描。
SQL> alter index hr.EMP_DEPARTMENT_IX visible; 恢复索引
select e.last_name, d.department_name
from hr.employees e, hr.departments d
where e.department_id=d.department_id;
select 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个语句
sharedpool
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
buffercache
Latch:cache buffer chains
SQL> create table t1(x int);
SQL> insert into t1 values (1);
SQL> commit;
SQL>select distinct sid from v$mystat;
SQL> exec dbms_workload_repository.create_snapshot()
两个session同时执行：
declare
v1 int;
begin
for i in 1..99999999
loop
select count(*) into v1 from t1;
end loop;
end;
/
第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;
RAC
安装
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/class="lazy" data-src/kernels/2.6.32-431.el6.x86_64/ /usr/class="lazy" data-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;
ocr和votingdisk
# 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
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)
HA和LB
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;
service
db name, instance name, global name, sid, service name, db_unique_name
orcl db name
orcl global name(service name)
查询现有服务：
SQL> select name from v$database;
SQL> select instance_name from v$instance;
SQL> select name from v$services;
$ vi tnsnames.ora; lsnrctl status
# su - grid
$ srvctl status service -d orcl
创建服务：
# su - oracle
$ srvctl add service -d orcl -s testsvc -r orcl1 -a orcl2
# su - grid
$ srvctl start service -d orcl -s testsvc
$ crsctl stat res -t
$ lsnrctl status; lsnrctl listener_scan1
# su - oracle
$ sqlplus sys/password@scan.test.com:1521/testsvc as sysdba
SQL> select instance_name from v$instance;
SQL> select name from v$database;
netca添加testsvc的解析
SQL> shutdown immediate 关闭orcl1
$ sqlplus sys/password@scan.test.com:1521/testsvc as sysdba
SQL> select instance_name from v$instance; 连接到orcl2
# su - grid
$ crsctl stat res -t
$ srvctl status service -d orcl -s testsvc
$ srvctl start instance -d orcl -i orcl1
$ crsctl stat res -t testsvc还在orcl2上，不会自动failback
$ srvctl relocate service -d orcl -s testsvc -i orcl2 -t orcl1
$ crsctl stat res -t
$ srvctl stop service -d orcl -s testsvc
$ srvctl remove service -d orcl -s testsvc
resource manager/scheduler/sql trace
cache fusion
ops
内存>网络>磁盘
node1：
# su - oracle
$ sqlplus / as sysdba
orcl1> create tablespace tbs01;
orcl1> create table t1 (x int, y int) tablespace tbs01;
orcl1> insert into t1 values (1, 1);
orcl1> insert into t1 values (2, 2);
orcl1> commit;
orcl1> select dbms_rowid.rowid_relative_fno(rowid), dbms_rowid.rowid_block_number(rowid) from t1;
orcl1> alter system checkpint;
orcl1> alter system flush buffer_cache;
node1:
begin
for i in 1..10000 loop
update t1 set y=i where x=1;
end loop;
end;
/
node2:
begin
for i in 1..10000 loop
update t1 set y=i where x=2;
end loop;
end;
/
xcuràpiàcr
add/delete node
add:
node3做所有准备工作
node1上验证：
#su - grid
$ cluvfy stage -pre nodeadd -n node3
$ ./addNode.sh "CLUSTER_NEW_NODES={node3}" "CLUSTER_NEW_VIRTUAL_HOSTNAMES={node3-vip}"
node3上root身份执行root.sh
node1上运行dbca添加新实例
crsctl stat res -t
DG
安装
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';
active data guard
只读模式打开
node1(primary)
SQL> create table t1(x int); insert into t1 values (1); commit;
node2(standby)
SQL> select open_mode from v$database; 确认是mount
SQL> recover managed standby database cancel;
SQL> alter database open;
SQL> select open_mode from v$database; read only
SQL> select * from t1;
SQL> insert into t1 values (2); commit; node1上做
SQL> select * from t1; 和node1不同步
SQL> delete t1; 报错
SQL> alter system switch logfile; node1切换日志
SQL> select sequence#, applied from v$archived_log; 传输但不应用
SQL> shutdown immediate
SQL> startup mount
SQL> alter database recover managed standby database using current logfile disconnect;
SQL> select sequence#, applied from v$archived_log; 应用node1的日志
快照备用
node1(primary)
SQL> create table t1(x int); insert into t1 values (1); commit;
node2(standby)
SQL> show parameter db_recovery_file_dest
SQL> recover managed standby database cancel;
SQL> shutdown immediate
SQL> startup mount
SQL> alter database convert to snapshot standby;
SQL> alter database open;
SQL> select open_mode from v$database; read write
$ ls /u01/app/oracle/fast_recovery_area/ORCLPS/flashback
SQL> select * from t1;
SQL> insert into t1 values (1); commit; node1修改
SQL> select * from t1;insert into t1 values (2);commit; 无法看到node1修改，但自己可以修改
SQL> alter system switch logfile; node1切换日志
SQL> select sequence#, applied from v$archived_log; 传输但不应用
SQL> shutdown immediate
SQL> startup mount
SQL> alter database convert to physical standby; node2丢失更改
SQL> shutdown immediate
SQL> startup mount
SQL> alter database recover managed standby database using current logfile disconnect;
SQL> select sequence#, applied from v$archived_log; node2应用日志
快照备用+flashbackdb
node1(primary)
SQL> create table t1(x int); insert into t1 values (1); commit;
node2(standby)
确认数据库在mount状态
SQL> show parameter db_recovery_file_dest
SQL> recover managed standby database cancel;
SQL> alter database flashback on;
SQL> alter database convert to snapshot standby;
SQL> alter database open;
SQL> select open_mode from v$database; read write
SQL> insert into t1 values(2); commit; 导入测试数据
SQL> create restore point before_test GUARANTEE flashback database;
SQL> delete t1;commit;
SQL> shutdown immediate
SQL> startup mount
SQL> flashback database to restore point before_test;
SQL> alter database open resetlogs;
SQL> select * from t1;
SQL> shutdown immediate
SQL> startup mount
SQL> alter database convert to physical standby; node2丢失更改
SQL> shutdown immediate
SQL> startup mount
SQL> alter database recover managed standby database using current logfile disconnect;
SQL> drop restore point before_test;
ADG
node1(primary)
SQL> create table t1(x int); insert into t1 values (1); commit;
node2(standby)
SQL> select open_mode from v$database; 确认是mount
SQL> recover managed standby database cancel;
SQL> alter database open; 或者SQL> startup
SQL> alter database recover managed standby database using current logfile disconnect;
SQL> insert into t1 values (2);commit; node1上修改
SQL> select * from t1; node2上查看实时数据
角色转换
switch over
node1(primary)的准备：
fal_server/fal_client/standby_file_management
创建srl
node2(standby)
log_archive_dest_2/log_archive_config
node1(primary)确认日志传输完整（nogap）
SQL>SELECT STATUS, GAP_STATUS FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID = 2;
node2(standby)确认两个lag
SQL> SELECT NAME, VALUE, DATUM_TIME FROM V$DATAGUARD_STATS;
node1(primary)
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; 应该是TO STANDBY
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
SQL> SHUTDOWN ABORT;
node2(standbyànew primary)
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; 应该是TO PRIMARY
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
SQL> ALTER DATABASE OPEN;
node1(primaryànew standby)
SQL> STARTUP MOUNT;
SQL> alter database recover managed standby database using current logfile disconnect;
failover
node2(primary)和node1(standby)开启flashback
node2(primary)准备：
SQL>create table t1(x int); insert into t1 values (1); commit;
node2(primary)模拟故障：
# ifdown eth0 node2(primary)关闭网络
SQL> insert into t1 values (2); commit;
SQL> shutdown abort
node1(standby)
SQL> recover managed standby database cancel;
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SQL>ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
SQL> ALTER DATABASE OPEN;
利用flashback恢复node2(primaryànew standby)
node1(new primary)
SQL> SELECT TO_CHAR(STANDBY_BECAME_PRIMARY_SCN) FROM V$DATABASE;
node2(new standby)
SQL> startup mount
SQL> FLASHBACK DATABASE TO SCN 1275936;
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP MOUNT
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
数据库设计
基于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;
DG
在node2上的/etc/oratab中添加数据库
$ vi /etc/oratab
orclps:/u01/app/oracle/product/11.2.0/db_1:Y
node2上不配置em
在node1上选择“只安装软件”
复制老版本路径下的listener.ora, sqlnet.ora, tnsnames.ora spfileorcl.ora orapworcl至新版本对应目录
安装软件失败：
$ vi /u01/app/oraInventory/ContentsXML/inventory.xml
RAC
升级gi：
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;
免责声明：
① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的，并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据，供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。
② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
![[mysql]mysql8修改root密码](https://static.528045.com/imgs/7.jpg?imageMogr2/format/webp/blur/1x0/quality/35)
