第一天
7月13日OCP笔记:
Oracle Ocp11g准备资料:
OracleFundmentals 书
管理1 书
管理2 书
光盘下载:otn.oracle.com
文档下载:docs.oracle.com
虚拟机配置:1cpu/2G RAM/OracleLinux/网络HostOnly/40G SATA。安装OracleEnterpriseLinux5.4,Oracle11gR2 11.2.0.1。E盘没还原卡。
操作系统:语言选英文(免环境变量NLS_LANG)静态IP:200.100.50.13/24,主机名server13.example。时区:上海,不用UTC。密码codecraft。软件全选。关闭防火墙,SELinux禁用。Kdump内核转储不选。不创建帐户。分辨率1024*758(系统-管理-显示-硬件-LCD-1024*768,首页也要改,改后重启)。
装Oracle11gR2:
建组 oinstall dba oper 用户oracle/密码oracle1158
建目录/u01/app/oracle,改所有者,改权限775
加环境变量:
umask 022
export TMP=/tmp
export TMPDIR=/tmp
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
export ORACLE_SID=orcl
export PATH=.:$ORACLE_HOME/bin:$PATH
安装包拷到/tmp目录下。进oracle帐户,执行xhost+,$cd /;./runInstaller;
Create and configure the database
Server Class
单实例single Instance
装包 包在Linux光盘的Server目录
SQL语句:
查询 SELECT
数据维护DML INSERT UPDATE DELETE
数据定义DDL CREATE ALTER DROP TRUNCATE
数据控制DCL GRANT REVOKE
事务控制TCL COMMIT ROLLBACK SAVEPOINT
解锁用来学习Oracle的hr帐户:
SQL>alter user hr identified by "hr" account unlock;
查书《SQL Language Reference》
进入sqldeveloper:
#xhost +
#su - oracle
$ORACLE_HOME/sqldeveloper/sqldeveloper.sh
设置:连接名hr,用户名hr,密码hr,SID为orcl。测试应该状态成功。
按F9执行sql语句。Sqldeveloper命令可修改,带Tab自动补齐和Ctrl+BackSpace删单词使用更方便。
显示用户:
>show user
不同帐户有不同的表,只有hr帐户有employess表,而sysdba中没有。
用数据字典显示所有的表:
>select table_name from dba_tables;//管理员的拥有表
>select table_name from user_tables;//用户的拥有表
查看表结构(相看表有哪些列):
>desc 表名
相看表内容:
>select语句
关键字和对象名不区分大小写,换分是任意的。
查询:
>select 列|表达式 from 表;//表达式不影响原表
查询的列别名AS "别名":解
>select first_name,last_name,salary*12 as "nianxin" from employees;
一般表达式都是加别名便于理。别名可以不加引号,但有空格的别名必须加引号。
查询的去重复行:
>select distinct department_id from employees;
注意:表达式中与NULL有关的任何计算均为NULL。
双坚线字符串连接用双坚线||
当字符串间中单引号,使用q'[]'括起字符串。
每课的练习只有英文版中才有。作业为Practice for lesson1。
第二天
7月14日OCP笔记:
数据类型:NUMBER DATE VARCHAR2
DATE要求单引号界定,大小写区分,格式满足要求。
ORA-27300错误导致无法启动数据库,需要修改内核参数#vim /etc/sysctl.conf加入kernel.sem=250 32000 100 128完成。
关键字、表名、列名都不区分大小写。
选92年之后进公司的:(使用日期)
>select first_name,last_name,salary from employees where hire_date='01-JAN=92';
查看没有提成的:(IS NULL筛选)
>select first_name,last_name,salary from employees where commission_pct IS NULL;
按薪水排序:
>select first_name,last_name,salary from employees order by salary desc;
查询名字中第2个字母为a的员工:
>... where last_name like '_a%';
IN关键字与OR功能有重复:
>salary IN(5000,6000,7000)同salary=5000 OR salary=6000 OR salary=7000
&变量替换可以替换列名或表达式
>select first_name,last_name,&v1 from employees where hire_date>'01-JAN-92' order by &v2 desc;
PL/SQL最常用功能:条件判断,循还。还有函数,存储过程,包,触发器。
单行函数,转换函数,组函数可以无需PL/SQL编程,直接实现常用功能。
单行函数单进单出,多行函数则是多入单出。功能在SQL Language Reference中的Function查询。
虚拟表dual用于构造完整语句:
>select upper('abcdefg') from dual;
年YYYY,而年缩写YY/RR不易用不建议使用。
加月:
>select add_month(sysdate 1) from dual;
下周五:
>select next_day(sysdate 'FRIDAY') from dual;
月份最后一天:
>select last_day(sysdate) from dual;
日期四舍五入:
>select round(sysdate,'YEAR') from dual;
>select trunc(sysdate,'MONTH') from dual;
Sqlplus保存文件:(需要创建目录)
>save /test.sql;不填路径默认存在/home/oracle路径下。
运行Sqlplus:
>@/test.sql;
编缉.sql文件用VIM编缉器,另存为方式如下:
Shift+:w /test2.sql
答疑:
字串中有'用其它引号运算符q'[]'。字串、日期用单引号,列别名用双引号。筛选%和_用逃脱转义符like '%Smi\_th%' ESCAPE '\'。
使用sys用户登sqldeveloper需将Role换为sysdba。
显示转换:
>select 123+'234' from dual;//相当于123+to_number('234')
显示转换自定义日期格式:
select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;
显示转换区分大小写,以下两条语句不同:
>select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS YEAR') from dual;
>select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS Year') from dual;
自定义的字符串放到日期格式之间用""
>select to_char(sysdate,'YYYY-MM-DD "of" HH24:MI:SS') from dual;
格式ddspth,dd数字日期,sp拼写,th排序:
>...
使用fm删除前导空格:
>...
数字显示转换为字符:
>select to_char(12345678,'$99,999.00') from dual;
注意任意长度数字占位小于数字将无法显示。
to_number几乎不用。
to_date使用:隐式转换可能报错,因而最好加上。
>...where hire_date
nvl(exp1,exp2)当表达式1不为空返表达式exp2:
nvl2(exp1,exp2,exp3)当表达式1不为空返表达式exp2,为空返表达式exp3:
nullif(exp1,exp2)比较两个表达式,不等返回表达式1,相等返回空。
coalesce(exp1,exp2,exp3)返回第一个非空值。
CASE条件表达式:
>select lst_name,salary,CASE department_id WHEN 10 THEN salary+1000 WHEN 20 salary+2000 WHEN 30 salary+3000 ELSE salary+500 END AS "New Salary" from employees;
DECODE条件表达式:
>select lst_name,salary,decode department_id,10,salary+1000,20,salary+2000,30,salary+3000,salary+500 as "NewSalary" from employees;
第三天
7月15日OCP笔记:
子查询内容:
查询员工名字,工资(比Jones低的):
>select last_name,salary from employees where salary<(select salary from employees where last_name='Jones');
在查询的结果中再进行查询。单行子查询的结果是一行一列,多行子查询的结果是多行一列。多列结果只能分割为一列。
子查询用途1:放到比较条件中,单行子查询常配合组函数使用。
单行比较运算符:> < >= <=
多行比较运算符:>ALL ANY大于最小
子查询用途2:放在被查询表处
select last_name,salary from (select last_name,salary from employees);
集合运算符:UNION/UNION ALL;INTERSECT;MINUS
例子:
>create table a (id number,name char(10));
>create table b (id number,name char(10));
>insert into a values(10,'aa');
>insert into a values(20,'bb');
>insert into b values(10,'aa');
>insert into b values(30,'cc');
>select * from a union select * from b;
处理数据包括: DML数据操作语句和TCL事务处理语句。
create table 表/列,insert into 表/值,update表set的列赋值,delete表/条件。建表的表名和列名加与不加空格都可以。insert into表列行用于添加缺少部分列信息的行,也可以在VALUES子句中填NULL。
表中插入单行:
>desc table1;//先查询表结构,再方便插入行数据。
>insert into employees values(行值);
些语法一次只能插入一行。列名是可选参数一般不填,空值填NULL。
创建一个同结构的空表:
>create table emp as select * from employees where 1=2;
表中一次插入多行,也就是将子查询结果插入到表中:
>insert into emp select * from employees where department_id=50;
子查询结果的结构要与被录入表的结构一致。若只录入前10前:where rollnum<11;
修改数据:
>update emp set salary=10000,comission_pct=0.1;
删除数据:
>delete emp where salary>5000;
清空表。删除所有表内容,表还在:
>delete emp;删除表内容的DML语句
>truncate table emp;//此为DDL语句
DDL语句:truncate;create;alter;drop;
DC语句:grant;revoke;
TCL事务控制语句:commit;rollback;savepoint;select for update;
事务特征ACID:原子性,一致性,隔离性,持久性。多条DML都是一个事务、一条DDL、一条DCL。也就是说DDL、DCL语句自动提交(隐式提交)。正常退出也会自动提交。系统崩溃则回滚rollback。
select for update;锁定被修改的行直到commit或update才释放。
模拟崩溃:
#ps ef|grep oracleorcl;//查看进程号
#kill -9 进程号;
练习:建表,录入10行,试下不同的提交方式。
标记保存点:
>savepoint p1;
回滚到保存点:
>rollback p1;
利用伪列选择前10行:
>select * from employees where rownum<11;
只有提交的数据才能被其它用户可见。修改的未提交数据,其它用户不可修改,只能访问修改前的数据。
读一致性:读取的数据为查询命令的执行时间的数据。在数据区实现修改,修改前数据通过构造CR块实现。
SELECT是无锁,DML会产生锁,锁为行锁。commit将释放行锁。
DDL语句。
命名规则:30个字符以内,同一用户不能重名,只含字母数字_#$,首不为数,不含保留字。
查看用户:
>desc dba_users;//要求SYSDBA权限
>select username from dba_users;
查看保留字:
>desc v$reserved_words;//要求SYSDBA权限
>select * from v$reserved_words where reserved='Y';//保留字多数是关键字
建表,要求有存储空间和权限:
>create table hr.students (stu_id number,stu_name varchar2(20),stu_sex char(1) DEFAULT 'M');
DEFAULT参数填入默认值。
数据类型:number date 定长char(2000Byte) 可变字串varchar2(4000Byte) CLOB(charactor larger object 约4Gbyte)。
查看块大小:
>show parameter db_block_size;
时间间隔的数据类型:INTERVAL YEAR TO MONTH;INTERVAL DAY TO SECOND
添加列:格式(alter table 表 add 表)
>alter table students add resume clob;
>desc students;
建表约束条件,用于建表的列名后:constraint+约束名+约束类型
NOT NULL不空
UNIQUE不重,允许为空
PRIMARY KEY不重不空
FOREIGN KEY外键的值只能为参考主键值或NULL
CHECK自定义检查
约束用于限制表中数据的有效性。
NOT NULL约束:(NOT NULL约束直接附在列名后)
>create table t1(id number NOT NULL,name char(10));
>create table t2(id number,name char(10));
>alter table t2 modify id number NOT NULL;
UNIQUE约束:
>create table t3 (id number constraint t3_id_un UNIQUE,name char(10));
>create table t4 (id number name char(10));
>alter table t4 add constraint t4_id_un UNIQUE(id);
主键约束用法:(建表的constraint+约束条件名+约束类型,改表的constraint+约束条件名+x约束类型括进列名)
>create table t5 (id number constraint t5_id_pk primary key,name char(10));
>create table t6 (id number,name char(10));
>alter table t6 add constraint t6_id_pk primary key(id);
外键约束用法:(constraint+约束条件名+references+主键表(主键列))
>cerate table classes (id number constraint class_id_pk primary key,class_name char(10));
>drop table students
>create table sutdents (stu_id number,stu_name char(10),class_id number constraint stu_classid_fk references classes(class_id));//建表外键约束写在内部
>create table sutdents (stu_id number,stu_name char(10),class_id number,constraint stu_classid_fk foreign key(class_id) references classes(class_id));//建表外键约束写在外部
>create table students1 (stu_id_number,stu_name char(10),class_id number);
>alter table students1 add constraint stu1_classid_fk foreign key(class_id) references classes(class_id);
注意建表外键约束写在外部同修表形式,若写在内部不能加数据类型和foreign key关键字。否则将出现ora-02253错误,“这里不允许限制声明”。
教材31页可以练习创建主外键的关系。
检查约束:
>alter table students add stu_age number(2);
>alter table students add constraint ck_age CHECK(stu_age between 12 AND 18);
>alter table students add stu_sex char(1) constraint ck_sex CHECK (stu_sex IN('M','F','m','f'));
复合主键:
>create table t8 (id2 number,name char(10) constraint t8_pk primary key(id1,id2));
用数据字典查看表的主外键关系:
>select table_name from user_tables;//查看表名
>select constraint_name,constraint_type from user_constraints where table_name='classes';//查看约束关系
第四天
160718第四天OCP笔记
ON DELETE CASCADE参数,联动修改,删除主键相关行同时删除外键相关行:
SQL> alter table students drop constraint STU_CLASSID_FK;
SQL> alter table students add constraint STU_CLASSID_FK foreign key(class_id) references classes(class_id) on delete cascade;
ON DELETE SET NULL参数,联动修改,删除主键相关行同时将外键相关行置NULL:
SQL> alter table students drop constraint STU_CLASSID_FK;
SQL> alter table students add constraint STU_CLASSID_FK foreign key(class_id) references classes(class_id) on delete set null;
添加列:
>alter table students add resume clob;
删除列:
>alter table students drop resume;
重命名列:
>alter table students rename resume resume1
修改列定义:
>alter table students modify resume varchar2(4000);
修改列默认值、列约束条件的内容前面讲过。
创建视图:
>create view v1 as select * from employees;
视图本质上就是封闭一条查询语句。视图也可也像表那样用select desc。
视图不能删除,只能删除再创建:(两种方法)
>drop view v1;
>create view v1 as select * from employees;//方法1
>create or replace as ...;//方法2
创建视图问题:
>create or replace v1 as select * from employees;
WITH CHECK OPTION子句,对视图的DML操作限定在视图范围内,超出范围将被拒绝:
>create or replace v1 as select last_name,salary from employees where salary>15000 with check option;
>update v1 set sal=12000 where last_name='Kochhar';//若不加with check option参数,修改后值将不可见,添加此参数将拒绝修改。
创建只读视图:
>create or replace v1 as select last_name,salary from employees where salary>15000 with read only;
删除视图:
>drop view v1;
简单视图的基表只能是1个,不包含函数,不分组。
视图包含以下内容不能删除行:DISTINCT、伪列rownum、组函数。
视图包含以下内容不能修改行:DISTINCT、伪列rownum、组函数以及表达式定义的列。例如salary*12为表达式定义的列。
视图包含以下内容不能添加行:DISTINCT、伪列rownum、组函数、表达式定义的列以及没有包含NOT NULL列。
SEQUENCE序列。
>create sequence seq1 increment by 3 start with 0 maxvalue 50 cycle cache 10;//参数cache指一次算10个,供后面使用。
>create table test001(id int);
>insert into test001 values(id,sql.nextval);
>使用数据字典user_sequence查看sequence当前值
索引。
索引可以自动创建或手动创建,自动使用。全内存数据库不需要创建索引。且索引增加DML负担,浪费系统资源。
数据库查询有全表扫描和索引扫描,等方式。Oracle根据执行计划进行成本判断决定是否使用索引。当具有索引且索引有助于(执行方案选优)查询才使用索引。
例如select count(*) from t1;是用全表扫描的,当有where条件时可能用索引。
以某一列创建索引:
>create index inx_test001_id on test_001(id);
创建索引的情况:列值范围很广,列中包含大量的空值,在where子句频繁用表的列尤其是多表联接的等值联接条件,表很大(万行以上)却查询结果小于4%。
OLPT系统改的多查的少,一般不建索引。
upper(last_name)='SMITH'代替last_name='Smith'会解决输入大小写的问题,但不再使用索引。
同义词:
>create synonym emp for employees;
>create pulbic synonym e1 for hr.employees;//需要sysdba权限,创建所有人都可以使用的同义词。
第一章 了解Oracle体系结构
Oracle Server=Oracle Instance(运行时)+Oracle Database(存储上)
Oracle Instance=Processes+Memory
Processes=ServerProcess+BackgrountProcess。服务进程为用户所拥有
查看Oracle服务进程:
>ps -ef|grep|oracleorcl
查看后台进程:
>ps -ef|grep ora_
研究5个关键的后台进程:
Process Monitor 即ora_pmon_orcl 作用:注册服务器、回收资源
System Monitor 即ora_smon_orcl 作用:实例恢复,合并空间
Database Writer 即ora_dbw0_orcl 作用:将内存中的数据写回磁盘
Log Writer 即ora_lgwr_orcl 作用:将内存中的日志写回磁盘
Checkpoint 即ora_ckpt_orcl 作用:同步数据文件、日志文件、控制文件,使数据库达到一致性。
Memory:一个共享区域(System Global Area SGA)和N个私有区域(Progrram Global Area PGA)。
SGA划分:database buffer cache、redo log buffer、shared pool、java pool、stream pool、large pool。
large pool用于支持备份恢复、数据装载、数据导入导出、并行查询或DML操作。
java pool用于支持java虚拟机。
streams pool支持流服务。
PGA用于存放会话信息、权限、变量、堆栈。
存储:filesystem/ASM裸设备/RAW/NFS/NAS/SAN...
数据库文件:数据文件、日志文件、控制文件、参数文件。归档日志文件、备份文件、口令文件、
参数文件存放位置:
$ORACLE_HOME/dbs/spfile$ORACLE_SID.ora。用于启动Oracle Instance。
数据文件存放位置:
>select name from v$datafile;
>select name from v$tempfile;
日志文件存放位置:
>select nember from v$logfile;
控制文件:
>select name from v$controlfile;
口令文件:
$ORACLE_HOME/dbs/orapworcl.ora;
进程跟踪及预警文件:
/u01/app/oracle/diag/rdbms/orcl/orcl/trace;//进程跟踪文件
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log;//预警文件
实施ASM存储后性能和容错性会提升。
安装独立于服务器的Oracle Grid Infrastructor:
安装Linux
创建组:asmadmin asmdba asmoper oinstall dba oper
创建帐号:#useradd grid -g oinstall -G asmadmin,asmdba,asmoper,dba;useradd oracle -g oinstall -G asmdba,dba,oper
(注意用户组要加对,否则无法添加ASM磁盘组,id grid;id oracle查组,usermod -a G dba grid添加组)
建目录:/u01/app/grid;/u01/app/oracle。拥有者:grid:oinstall /u01;oracle:oinstall /u01/app/oracle。权限:775。
环境变量修改部分:export $ORACLE_BASE=/u01/app/grid;export $ORACLE_HOME=/u01/app/grid/product/11.2.0/gridhome_1;ORACLE_SID=+ASM
准备ASM磁盘:40G,平均分11个区。
查看Linux内核版本:#uname -r
安装ASMLib:#rpm -ivh /光盘目录/*;
配置ASMLib服务:#/etc/init.d/oraleasm configure; 参数:grid/asmadmin/y/y。注意当前目录执行要用./oracleasm configure;
转化为ASM磁盘:#/etc/init.d/oraleasm createdisk asmdisk01 /dev/sdb1;//注意sdb4分区是扩展区不能使用,注意是ROOT用户
asmdisk01 /dev/sdb1
asmdisk02 /dev/sdb2
asmdisk03 /dev/sdb3
asmdisk04 /dev/sdb5
asmdisk05 /dev/sdb6
...
asmdisk10 /dev/sdb11
查看ASM磁盘:#/etc/init.d/oraleasm listdisks;
删除ASM磁盘#/etc/init.d/oracleasm deletedisk asmdisk18
DATA Normal asmdisk01-04
FRA External asmdisk05-08
安装grid软件:
#su - grid
$.../clusterware/Disk1/runInstaller;//执行安装
选:Install and Configure Grid Infrastructor for a Standalone Server;数据磁盘选DATA Normal;口令;其它同Oracle安装
用grid帐户执行$asmca;把5-8磁盘选成FRA。
($sqlplus / as asmdba;>startup;//启动ASM实例)
安装OracleDatabase软件:
Oracle用户下运行./runInstaller,只安装不建库,单实例数据库(不选RAC,real application cluster),执行脚本参数为缺省的不覆盖。
建库:
$dbca,General模板(General为OLPT模板,DATA为仓库模板)。全局数据库名任意起名,SID与环境变量一致。EM用database control(不用grid control)。密码。
Storage Tyep用ASM,Darabase Area用+DATA,ASM口令。recovery option的flash recovery area设置为FRA(主要用来放备份文件),不选EnableArchiving。
勾选Samples,内存(默认)、进程数(默认)、字符集(AL32UTF8)、ConnectionMode(默认)。
勾选建库,存模板,存建库脚本。EM解锁。
第五天
160719第五天OCP笔记
init.ohasd(包装进程):实现自动启动监听、ASM实例、数据库实例。
数据库逻辑结构:数据库-表空间-段-区-块。段是空间分配单元。表不够用时,以区为单位扩展。一般SYSTEM、SYSTEMAUX不用来存数据。
查看表空间名:
>select tablespace_name from dba_tablespaces;//需要SYSDBA权限
查看表空间名及文件名:
>select tablespace_name,file_name from dba_data_files;
向指定表空间建表:
>create table test (id number,name varchar(10)) tablespace users;
修改表的列宽
>col segment_name for a10;
查看表所在表空间、表、段、区、块:(表空间是users,而所有者是SYS)
>select tablespace_name,segment_name,blocks,extents,bytes/1024/1024 from dba_segments where owner='SYS' and segment_name='TEST';//注意SYS大写,表名也必需大写,不能用通配符?
循还录入数据(注意加/执行):
>begin
for i in 1..1000 loop
insert into test values(i,'aa');
end loop;
commit;
end;
/
启动监听:无GI用oracle帐号 lnsrctl start,有GI用grid帐号。再启数据库、EM(EM启动需要创建安全例外)。
执行脚本:>sqlplus hr/hr@scripts.sql
数据库分阶段诊断启动:过程instance started;database mounted;database open;
>start nomount;//若执行startup mount直接执行到第2阶段
>alter database mount;
>alter database open;
数据库4种关闭方式。生产环境多用shutdown transactional。shutdown abort则再启动由SMON实例恢复。
命令startup force=shutdown abort+startup
参数含义查看文档,改参数:
>alter system set sga_max_size=800M scope=both;//scope值memory、spfile、both
查看内存参数:
>show parameter sga_max_size;
查看磁盘参数文件:
#strings $ORACLE_HOME/dbs/spfileorcl.ora;//文件系统
#strings +DATA/orcl/spfileorcl.ora;//ASM设备
其中sp指separate physical
SQL清屏:
>!clear;
Oracle运维需要查看:
1、预警文件 运行常规信息
1、进程跟踪文件 各类进程,用地排错
3、动态性能视图 日常性能监控 内存表的视图
4、数据字典视图 数据库的物理和逻辑信息 数据字典的视图
从References 的PartIII Dynamic Performance View
动态性能视图:
用v$sql保存最近执行的SQL语句:
>select sql_text,executions from v$sql where cpu_time>200000;
用v$session查看登录的会话:
用v$lock查询锁信息:
>select sid,ctime from v$lock where block>0;
数据字典视图(都是复数):
查看用户拥有的表:
>select table_name from user_tables;
>select table_name from dba_tables;//查看所有用户拥有的表
其它表dba_users、all_sequences、dba_indexes。
查看预警文件:EM-AlertHistory。
/*--------------------------------------------------------------------------------------------------------------------
ASM的磁盘组在数据库建立之前用命令行管理,数据库建立之后用EM或SQLPLUS管理。
实例管理ASM磁盘组(SQLPLUS):
grid帐号运行sqlplus / as asmdba;
用v$asmdisk v$asm_diskgroup查看ASM磁盘使用情况:
>select group_number,name from asm$disk;
建磁盘组(4块):
>create diskgroup DG1 normal redundancy disk 'asmdisk09','asmdisk10','asmdisk11','asmdisk12';//redundancy指冗余
再次查看磁盘组:
>select group_number,name from asm$disk;
添加/删除磁盘组:
>alter diskgroup dg1 add/drop disk 'asmdisk13';
同时添加删除磁盘(经常用于换盘,用一条执行的更快):
>alter diskgroup DG1 add disk 'asmdisk14' drop disk 'asmdisk12';
删除磁盘组:
>drop diskgroup dg1;
实例管理ASM磁盘组(EM):
进入General下的ASM,删除ASM磁盘要force。
创建磁盘组参数:
Redundancy冗余参数有high(至少三块磁盘)、normal(至少两块磁盘)、external(不冗余至少一块磁盘)
Allocation Unit分配单元
创建故障组(EM):选若干块磁盘,FailureGroup起相同的名字。
创建故障组(SQLPLUS):
>create diskgroup dg normal redundancy failgroup f1 disk 'asmdisk09','asmdisk10';
>create diskgroup dg normal redundancy failgroup f2 disk 'asmdisk10','asmdisk11';
区映射:只了解。
ASM磁盘组的兼容性:DATA/FRA/DG磁盘组由“ASM实例-Grid”管理,由“数据库实例-OracleDatabase”使用。
创建磁盘组时OracleDatabase版本<'compatible.rdbms'<'compatible.asm'<=Grid版本。兼容版本越低性能越差。
重平衡REBALANCE,数据平均分配到磁盘上。
创建表空间
>create tablespace tbs1 datafile '+FRA';
删除磁盘后每个磁盘内容变多:
>alter diskgroup FRA drop disk 'asmdisk08';
ASM快速镜像重新同步:当磁盘离线
实例管理ASM磁盘组(命令行)(在数据库建立之前使用):
grid$asmca;
grid$asmcmd;
课后练习5.1。需要将Oracle安装光盘的labs目录下。
第六天
160720第六天OCP笔记
/*网络配置-------------------------------------------------------------------------------------------------------------------------------------------------
netmgr中的名字解释:HostName主机名、ServiceName数据库全局服务名、SID为$ORACLE_SID。
对于单实例数据库,Oracle Server、Oracle Listener在同一台服务器。
listener.ora提供以下信息:监听器名、IP、端口、数据库服务名。
生成监听listener.ora:无GI用orcle有GI用grid帐户。$netmgr;添加Listeners,主机IP(用静态加快访问速度)、端口、GlobalDatabaseName填数据库全局名、填$ORACLE_SID。
注意监听配好后要启动监听,多监听启动要指定监听名。
生成tnsnames.ora:无GI有GI用oracle帐户。$netmgr;添加Service Naming,NetServiceName填网络服务名,主机IP,端口号,ServiceName填数据库全局名。
注意listener.ora的参数要与已配好监听相同。
文本编缉监听器$vim $ORACLE_HOME/network/admin/listener.ora;$vim $ORACLE_HOME/network/admin/tnsnames.ora;有GI与无GI的区别是$ORACLE_HOME不同。
文本编缉第2个监听器的名字不同、端口号不同、去掉IPC通讯的一行(第一个监听器IPC保留),其它的相同。由Process Monitor提供注册服务。
查数据库全局名>show parameter service_names
显示$ORACLE_HOME:echo $ORACLE_HOME
显示$ORACLE_SID:echo $ORACLE_SID
查看监听是否提供服务:$lsnrctl status listener2;
通过进程间通信而不通过网络访问数据库:
$sqlplus hr/hr;
$sqlplus / as sysdba;
使用监听访问数据库(简单访问):
$sqlplus hr/hr@IP:1521/orcl
$sqlplus sys/oracle@IP:端口/数据库全局服务名 as sysdba;
通过使用不同的端口使用不同的监听器。
使用监听和tnsnames.ora访问数据库():
$sqlplus hr/hr@网络服务名;
$sqlplus sys/oracle@网络服务名 as sysdba;
专用服务器进程:
查看服务器进程:
>show parameter processes;//查看全部进程
>ps -ef|grep ora_;//查看后台进程
设置共享服务器进程(服务端):
>alter system set shared_servers=25;//设置共享服务的进程数
>alter system set shared_server_sessions=100;//设置共享服务的用户数
>alter system set dispatchers='(protocol=tcp)(dispatchers=1)';//拿出一个进程作排号器,注意dispatchers参数加引号
设置共享服务器进程(客户端):
编缉tnsnames.ora将server=dedicated改为server=shared//将dispatchers注册到默认监听器。
用lsnrctl;services是否是dispatcher,约等5分钟才显示。dispatcher分配器。
对于共享服务器进程,当用户请求时,监听将不返回server process地址,而是返回dispatcher序列号。
以下操作不被共享服务器进程:管理、备份还原、装载、导入导出、并行。如shutdown immediate;
对于共享服务器进程,SGA要增,PGA(用户独有)要减。
/*存储结构-------------------------------------------------------------------------------------------------------------------------------------------------
查询表空间及文件:
>select tablespace_name,files from dba_data_files;
创建表空间:
>create tablespace tbs1 datafile '+DATA' size 20M;//若文件系统datafile的参数'/u01/app/oracle/oradata/orcl/tbs2.dbf'
>create tablespace tbs2 datafile '+DATA' size 20M,'+FRA' size 20M;//此命令创建2个数据文件,小表空间最多1024个文件,大表空间(32T-128T)只有1个数据文件
>create tablespace tbs3 datafile '+DATA' size 20M,autoextend on next 10M maxsize 10G,'+FRA' size 20M autoextend on next 5M maxsize unlimited;
删除表空间:
>drop tablespace tbs4;
EM图形界面管理表空间:略
段 区对应关系:
1M 64K
1-64M 1M
64M- 8M
区是最小的分配单位,块是最小的I/O单位。
数据由OMF管理的数据文件,在表空间删除会由oracle自动删除对应文件。????
删除表空间:
>drop tablespace tbs4 INCLUDING CONTENTS;
>show parameter db_create_file_dest;
>alter system set db_create_file_dest='/u01/app/oracle/oradata';
扩大数据库。
显示文件编号:
>select file# from v$datafile;
设置数据库文件大小:
>alter database datafile 9 resize 40M;
数据库文件大自动扩展:
>alter database datafile 10 autoextend on next 10M maxsize 10G;
表空间大小自动扩展:
>alter tablespace tbs3 add datafile '+DATA' size 10M autoextend on next 10M maxsize10G;
/*用户管理-------------------------------------------------------------------------------------------------------------------------------------------------
建帐户:
>create user jack identified by jack123;//需要SYSDBA权限,密码不加引号
系统权限。
用DCL语句(grant revoke)进行授予登录权限:
>grant create session to jack;
授予建表权限:
>grant create table to jack;
授予用户其它权限:
>grant create sequence,create synonym,create view to jack;
撤消权限:
>revoke create session from jack;
对象权限的撤消是级联的,系统权限的撤消是不级联的。
授予级联系统权限:
>grant create table to jack with admin option;
撤销级联系统权限:
>revoke create table from jack;
EM图形界面:Database-Users-填用户名,密码,系统权限
对象权限。对象权限为grant+权限+on表+to用户。
>grant select on hr.jobs to jack;
>revoke select on hr.jobs from jack;
>grant select on hr.jobs from jack with grant option;
>revoke select on hr.jobs from jack;//对象权限的撤销是级联的
级联???
>grant insert,update,delete on hr.jobs to jack;
角色使用:
>create role r1;
>grant select any table,create any table to r1;
>create role r2;
>grant all on hr.employees to r2;
>grant r1,r2 to jack;//把角色当作权限授权给用户
概要文件对应EM中的profile。用于资源控制和口令安全性。如:限制连接时间、空闲多长时间断开、每个用户限制的会话数。
EM中设置概要:Server-Security-Profiles-。
资源限制开启:
>alter system set
通过函数限制口令复杂度:要么用PL/SQL编。要么$ORACLE_OME/rdbms/admin/utlpwdmg.sql脚本,以SYS用户执行此脚本,就可以在"ComplexityFuntion"填入函数名。
配额:
alter user jack quota 20M on users;
alter user jack quota unlimited on example;
grant unlimited tablespace to jack;
/*并发访问-------------------------------------------------------------------------------------------------------------------------------------------------
复习:锁机制,行锁、表锁、事务锁。锁的并发性和兼容性。
事务结束释放行锁和表锁。
查看某用户的事务信息SID:
>select sid from v$session where username='HR';//注意用户名要大写。
获取事务SID的锁类型、锁级别、请求的锁、阻断时间(s):
>select sid,type,lmode,request,ctime from v$lock where type in ('TM','TX') and sid in(事务号1,事务号2);
3级锁较温和,6级是排它锁。
EM图形界面:Performance-BlockingSessions。
看书只看收集命令。教材学生指导书只有大纲的作用,写的不细致。
第七天
160721第七天OCP笔记
TM锁-DML enqueue
TX锁-Transaction enqueue
LMODE:
0 none
1 null
2 row-S(SS) RowShare
3 row-X(SX) RowExclusive
4 share(S)
5 share row exclusive(SRX)
6 exclusive(X)
锁的排他性:X锁全排他,RS锁除了X锁全兼容,RX锁、S锁、SRX锁只兼容RS锁,SRX锁、X锁自斥。
DML加RX锁,select for update加RS锁,DDL、DCL加X锁。锁的兼容会导致等待。
兼容的锁加锁后,一个会话解锁后只解其会话的锁。
用EM查看锁等待队列:Performance-InstanceLocks。
分别对第一个用户,第二个用户加锁:
>lock table jobs in row share mode;//能加上锁,说明二级锁是不排它的。
解锁:
>commit;
找到长期占有锁的会话:
>select sid,ctime from v$lock where block>=1;//查找超过1秒的阻断。
>select serial# from v$session where sid=上条命令进到的;
结束长期占有锁的会话:
>alter system kill session 填SerialNumber immediate;
死锁由Orcle自动撤销产生死锁的语句。
EM管理undo表空间:Server-AutomaticUndoManagement:
>create undo tablespace undotbs2 data '+DATA' autoextend on next 10M maxsize 10G;
Oracle只能用一个undo_tablespace,由此语句设置undo表空间:
>alter system set undo_tablespace='UNDOTBS1';
显示undo表空间的自动管理、保留时间、强制保留:
>show parameter undo_;//undo_retention在事务完成之后开始记时,
设置undo_retention:
>alter system set undo_retention=900;单位秒
从EM获取undo表空间设置建议:
RelatedLinks-AdvisorCenter指导中心-AutomaticUndoManagement-RunAnalysis计算保留时间与undo表空间大小的对应关系。
用as of timestamp(to_timestamp(时间))查询还原数据:
>select * from employees as of timestamp(to_timestamp('2016-07-13 11:00:00','yyyy-mm-dd hh24:mm:ss')) ;
/*数据库审计-----------------------------------------------------------------------------------------------------
审计包括:强制审计、sysdba审计、标准审计、FGA细粒度审计、基于值的审计。
授予用户sysdba权限:
>grant sysdba to hr;//
强制审计只审计管理员用户的登录行为。
查找强制性审计的进程号(由sid找paddr到spid):
>select spid from v$process where addr=(select paddr from v$session where sid=(select distinct sid from v$mystat));//注意v$process中的列名为addr
显示审计文件位置:
>show parameter audit_file_dest;//默认在$ORACLE_BASE/admin/orcl/adump;由上面的进程号找到审计文件位置
$进目录
$more orcl_ora_进程号.aud;
标准审记审计非sys用记的行为:
>show parameter audit_trail;
若audit_trail=DB则审计信息保存在数据字典aud$。若audit_trail=OS则审计信息存放在audit文件目录。
标准审计的具体内容:
审计范围-session(默认)、access
审计类型-默认成功失败都记录
审计用户-默认所有人
审计内空-执行语句、使用系统权限、使用对象权限。
启用审计:
>audit unlimited tablespace by hr;//启用无限表空间的审计
用表dba_auti_trail查询标准审计
>col obj_name for a10;
>select username,obj_name,action_name from dba_audit_trail where username='用户名HR' and obj_name='表名T1';
关闭审计:
>noaudit create sequence by hr;//关闭建序列的审计
审计的图形界面:Serer-Security-AuditSettings。
FGA细粒度审计,可以查看用户发出语句的详细信息。通过包DBS_FGA实现。
查文档:MasterBookList-PL/SQL Packages and Types Reference-DBMS_FGA。
语法如下:
DBMS_FGA.ADD_POLICY(
object_schema VARCHAR2,
object_name VARCHAR2,
policy_name VARCHAR2,
audit_condition VARCHAR2,
audit_column VARCHAR2,
handler_schema VARCHAR2,
handler_module VARCHAR2,
enable BOOLEAN,
statement_types VARCHAR2,
audit_trail BINARY_INTEGER IN DEFAULT,
audit_column_opts BINARY_INTEGER IN DEFAULT);
添加审计查看、修改50部门员工工资:
begin
DBMS_FGA.ADD_POLICY(
object_schema => 'hr',
object_name => 'employees',
policy_name => 'audit_50_sail',
audit_condition=> 'department_id=50',
audit_column => 'salary',
handler_schema => VARCHAR2,
handler_module => VARCHAR2,
enable => true,
statement_types=> 'select,update');
end;//注意包参数用=>,无效参数要去掉,end结尾加分号和/。
由数据字典查看dba_fga_audit_trail审计:
>select sql_text from dba_fga_audit_trail;
禁用审计:
DBMS_FGA.ADD_POLICY换为DBMS_FGA.DISABLE_POLICY
启用审计:
DBMS_FGA.ADD_POLICY换为DBMS_FGA.ENABLE_POLICY
删除审计:
DBMS_FGA.ADD_POLICY换为DBMS_FGA.DROP_POLICY
基于值的审计,可以看出值的变化。通过触发器记录信息,信息必需记录在自定义的表中。
sysdba审计。
开启sysdba审计:
>show parameter audit_sys_operations;
>alter system set audit_sys_operations=true scope=spfile;//此参数不能直接修改,需要重启数据库
>shutdown immediate;
>startup;
/*试用以下脚本不记录----------
scott.emp sal 的变化记录下来
create table audit_emp_change(name varchar2(10),oldsal number,newsal number,time date);
create or replace trigger tr_sal_change
after update of sal ON emp
for each row
declare
v_temp int;
begin
select count(*) into v_temp from audit_emp_change
where name=:old.ename;
if v_temp=0
then
insert into audit_emp_change values(:old.ename,:old.sal,:new.sal,sysdate);
else
update audit_emp_change set oldsal=:old.sal,newsal=:new.sal,time=sysdate
where name=:old.ename;
end if;
end;
/
/*----------
sysdba审计记录位置,再通过查进程号确定哪个文件:
>show parameter audit_file_dest;
/*数据库维护-----------------------------------------------------------------------------------------------------
由于sql语句处理成ExcutionPlans需要消耗系统资源。由优化程序统计信息可以获得较好的执行计划。
查询执行计划:
>set autotrace on;
>select sql_text,?? from v$sql;
由GATHER_DATABASE_STATUS Procedures包控制。
EM中查看统计信息:Server-QueryOptimizer-ManageOptimizerStatistics-GatherOptimizerStatistics。
由数据字典查看表的最后收集时间的表行数,用于优化执行计划。
>select num_rows,to_char(last_analyzed,'yyyy-mm-dd hh24:mm:ss') from dba_tables where owner='HR' and table_name='EMPLOYEES';
用GATHER_TABLE_STATUS Procedures包手动进行统计信息收集:
DBMS_STATS.GATHER_TABLE_STATS(
ownname=>'hr',
tabname=>'employees',
cascade=>true
第八天
160722第八天OCP笔记
性能管理:内存、I/O、应用程序(SQL PL/SQL)、资源争用(锁、闩、互斥Lock latch mutex)、网络
等待事件 v$event
查询等待事件:
>select distinct wait_class from v$event_name;
>select name from v$event_name;
查询等待事件(阻断)的SID:
>select sid from v$lock block=1;
等待事件相关的动态性能视图:系统v$sysstat v$system_event 会话v$sesstat v$session_event 特定服务v$service_stats v$service_event。从asktom.orale.com网站上找脚本。
内在优化由oracle 11g AMM Automatic Memiry Management自动管理。只需指定memory_target=内存大小,由MMAN进程通过AWR快照调整内存,每10分钟检测一次。
OLPT系统DB80%和OS20%,Oracle通过指导中心的内存指导报告内存。Oracle11g将sga_target设为0、pga_aggregate_target设为0(自动),只设置memory_target即可。
OLAP/DSS在线分析系统,即用作数据仓库时PGA大些 SGA50%/PGA50%。
验证索引对性能提升:
设置跟踪:
>set autotrace on;//需要SYSDBA权限
>create table hr.test200 tablespace users as select * from dba_objects;//建表
>create index hr.ind_test200 on hr.test200(object_id);//建索引
执行查询:
>select * from hr.test200 where object_id=1000;
将表移动,将使索引失效:
>alter table hr.test200 move tablespace example;
查索引是否失效:
>select status from dba_indexes where owner='HR' and index_name='IND_TEST200';
解决只需重建索引:
>alter index hr.ind_test200 rebuild;
删除索引:
>drop index hr.ind_test200;
/*备份恢复的概念---------------------------------------------------------------------
故障现象:语句失败、用户进程失败、网络故障、用户错误、实例故障、介质故障。只有介质故障通过"备份和恢复"。
复用文件一般3个,太多影响性能。控制文件、日志文件、归档日志可以复用,数据文件不复用。全数据库、数据文件、归档日志、控制文件、参数文件可以备份。
复用控制文件:
>select name from v$controlfile;//查看现有控制文件
>asmcmd拷贝控制文件(注意需要关闭文件进行控制文件拷贝,否则容易出现数据库版本不一致)
>alter system set control_files='控制文件1','控制文件2','控制文件3' scope=spfile;//注意ASM存储中.后面的文件名不填。control_files只能改参数文件
注意:控制文件挂多个只是方便查询复用的位置,而只有一个生效。
>重启数据库
ASM中拷贝文件:
$su - grid
$asmcmd
>cd;ls进目录
>cp 被拷文件 拷贝到
ASM常用命令:cd ..进入根目录,lsdg列出磁盘组,lsdsk列出磁盘。
复用日志文件:
>col member for a50;
>select group#,member from v$logfile;
>alter database add logfile member '+DG' to group 1;//文件系统填路径
>alter database add logfile member '+DG' to group 2;
>alter database add logfile member '+DG' to group 3;
切进日志直到消除invalid状态:
>alter system switch logfile;
>select group#,member,status from v$logfile;
复用归档日志(归档日志是组内镜像,组间同步)。
设置归档路径:
$mkdir -p /u01/arch01;
$mkdir -p /u01/arch02;
>alter system set log_archive_dest_1='location=/u01/arch01';//ASM为'location=+FRA'
查看归档路径:
>show parameter log_archive_dest;//显示归档路径
>show parameter db_recovery_file_dest;//默认归档路径
>关数据库,再启到mount模式startup mount
开启归档:
>alter database archivelog;
#ps -ef|grep ora_arc;//查看进程
检查归档开启:
>desc v$database;//找到log_mode
>select log_mode from v$database;
备份工具RecoveryManager可用于在线备份。
数据库必需先归档后备份,归档过程为:建目录,设置归档路径(检查设置),在数据库挂载状态开启归档(检查进程),切换以使用归档。
先删除表空间方便实验:
>drop tablespace 表空间名;
>drop datafile 文件名
$su - oracle
$rman target /
备份全数据库:
>backup database;
备份表空间:
>col tablespace_name for a30;
>select tablespace_name,file_name,file_id from dba_data_files;
>backup tablespace users,example;
备份数据文件:
>backup datafile 4,5 format '/u01/backup/f4_5.bak';
备份控制文件:
>backup current controlfile;
备份参数文件:
backup spfile;
备份归档日志
backup archivelog all;
查文档:backup & recovery;backup & recovery user guides
控制文件坏了只能启动到实例。
查控制文件目录:
>select name from v$controlfile;
$关闭数据库,删除控制文件,再启动数据库;ASM存储需要先关闭数据库才能删除控制文件
运行一条语句,再查日志找到控制文件的报告:
$tail -20 $ORACLE_BASE/diag/rdbms/orcl/orcl/trace/alert_orcl.log;
设置control_files修复:
>alter system set control_files=没坏的文件;
也可以拷贝修复:
$关闭数据库,拷贝控制文件,再重启数据库。
注意:ASM会删除空目录,是OMF的特征。建目录mkdir +FRA/orcl/controlfile/。
日志文件损坏修复。日志文件只添加无需拷贝。
先切换日志以启用日志:
>alter system switch logfile;//切换日志去掉INVALIDATE
>select group#,member,status from v$logfile;
>删除部分日志文件模拟损坏,ASM需要关闭数据库才能删除文件,查看预警日志文件。
$tail -20 $ORACLE_BASE/diag/rdbms//orcl/orcl/trace/alert_orcl.log;
通过删除损坏的日志组成员来修复:
>alter database drop logfile member '日志文件路径';
若无法删除,说明为当前日志组成员,使用alter system switch logfile;切换掉。
重加日志组成员,还原复用结构:
>alter database add logfile member '日志文件路径' to group 组号;
临时文件损坏。临时文件用于存放临时表和磁盘排序。
查临时文件位置:
>select name from v$tempfile;
查临时表空间名:
>select tablespace_name from dba_tablespaces where contents='TEMPORARY';
删除临时文件模拟损坏:ASM删除文件需要关闭数据库
添加临时文件:
>alter tablespace temp add tempfile '$ORACLE_BASE/oradata/orcl/tmp02.db' size 20M autoextend on next 20M maxsize 10G;//注意不要漏掉容量
删除损坏文件:
>alter tablespace temp drop tempfile '$ORACLE_BASE/oradata/orcl/tmp01.db';
若在关闭期间损坏临时表空间文件,则数据库自动重建。
口令文件的损坏,口令损坏将导致远程登录无法使用。
删除口令文件:
$rm $ORACLE_HOME/dbs/orapworcl;
重建口令文件:
$orapwd file=$ORACLE_HOME/dbs/orapworcl password=oracle;
数据文件损坏(非关键数据USERS)。
>alter system flush buffer_cache;
>select tablespace_name,file_name,file_id from dba_data_files;
查看备份文件:
$rman
RMAN>list backup;
损坏文件离线:
RMAN>sql 'alter database datafile 4,5 offline';
还原文件:
RMAN>restore datafile 4,5;
恢复文件(跑日志):
RMAN>recover datafile 4,5;
文件上线:
RMAN>sql 'alter database datafile 4,5 online';
数据文件损坏(关键数据SYSTEM/SYSAUX),需要关闭数据库进行恢复。
RMAN>run{
startup mount;
restore datafile 1,2,3,4,5;
recover datafile 1,2,3,4,5;
alter database open;
}
5、丢失所有日志组成员(上节课讲过丢失部分日志组成员)
INACTIVE非当前(已写回磁盘的日志,做日志恢复操作无需该组操作) ACTIVE非当前(此组日志对应的数据没有完全被刷新回磁盘,做数据库恢复需要该组中的日志信息) CURRENT(当前正在被使用的日志组)
先查看日志组成员:
>select group#,sequence#,status from v$log;
切换日志:
>alter system switch archivelog;
执行检查点,将所有日志对应的数据写回磁盘:(全部成为INACTIVE)
>alter system checkpoint;
破坏:删除INACTIVE的日志文件:
INACTIVE日志修复,不丢失数据,修复方法为:
>alter database clear logfile group 2;
第九天
160723第九天OCP笔记
参数文件的损坏。参数文件损坏将无法启动和写参数,已运行数据库可以使用。
指定文件备份路径:
>backup spfile format '/u01/sp.bak';
>list backup;
破坏参数文件:
$rm $ORACLE_HOME/dbs/spfileorcl.ora;//ASM的控制文件在+DATA/orcl/spfileorcl.ora
关闭数据库:
>shutdown immediate;
RMAN的数据库服务器至少到开启至实例状态,而缺少参数文件将无法启动至实例状态。解决办法是用RMAN内置实例启动数据库:
RMAN>start nomount;
用RMAN修复:
RMAN>restore spfile from 'u01/sp.bak';
RMAN中重启数据库:
RMAN>startup force;同startup abort+startup;
常见问题:若出现NO LOGON,是由于关闭时间过长,可以结束进程强制关闭数据库。
RMAN的设置。
查看所有的配置项:
RMAN>show all;
修改参数:
RMAN>参数;
删除备份:
RMAN>delete backup;不能在ASM中删除否则将不能同步日志,RMAN会误认为备份存在
备份参数有:控制文件自动备份、备份冗余、优化、加密、类型。
RMAN中只用分号查询命令帮助。查文档
configure control file auto backup启用控制文件自动备份。
configure retention policy to redundency 1备份冗余量,1为保留最近一次冗余量,2为保留最近两次冗余量。
configure retention policy to recovery window of 7 days备份保留天数。
cofigure backup optimization on备份优化以节约磁盘(恢复时间更长),一般不开。
configure encryption for database off开启加密。
configure encryption algorithm 'AES128'开启加密算法。
configure device type disk paralielism 1 backup type to backset备份类型:backup set备份集即空块不备份、image copy拷贝即和原始文件一样。
>backup database;
>backup as backupset database;
>backup as compressed backupset database;
>backup as copy database;
完全备份backup database,增量备份backup incremantal level 0 database。增量备份从0级开始。累积增量备份backup incremental level 1 cumulative database;
>delete backup;
>delete copy;
恢复控制文件数据库。
非本机登录RMAN:
$rman target sys/oracle@orcl;
备份共备份了:备份数据和备份原数据。备份原数据在控制文件当中。
通常建立目录数据库建立备份元数据-catalog database。丢失全部控制文件时就需要用catlog。
建立catlog过程:建库、建表空间、建用户、授权、创建catlog、注册catlog、同步catlog。
#xhost +
#su - oracle
$export $ORACLE_SID=catdb;
$dbca;不需要EM、不用自动管理、存储用文件系统、字符集AL32UTF8。(建库dbca时会自动在tnsnames.ora建立网络服务名)
>show parameter db_name;
建表空间用于存放元数据:
>create tablespace cats datafile '$ORACLE_BASE/oradata/catdb/cats.dbf' size 20M autoextend on;
建一个帐户用于管理元数据:
>create user catu identified by "catu" default tablespace cats temporary tablespace temp quota unlimited on cats;
授权:
>grant connect,resource,recovery_catalog_owner to catu;
创建catlog:
$rman target / catalog catu/catu@catdb
$rman target sys/oracle@orcl catalog catu/catu@catdb;//命令含义同上条
RMAN>create catalog;
注册catlog:
RMAN>register database;(否则将出现target database not found错误)
rman target /的备份同步到catlog:
>resync catalog;
修改之前的笔记,在grid帐户下配监听。
数据库的不完全恢复:主动/被动。
1、基于时间点的数据库不完全恢复。例如对DDL语句的恢复。
查看备份:>report need backup;//查看现有备份
$export NLS_LANG=american_america
$export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
$rman target /
RMAN>run {
startup force mount;//恢复需要在mount模式下进行
set util time='2016-07-20 01:36:00';
restore database;
recover database;
alter database open resetlogs;//数据库恢复之后必需以重置日志的方式打开一次
}
查看日志序号:
>archive log list;
删除之前日志:
>delete noprompt backup;
>delete noprompt copy;
进行第一次增量备份
>backup incremental level 0 database;
设置:
>sql 'alter system archive log current';
同步catalog:
>resync catalog;
2、使用SCN号进行数据恢复:
查SCN号:
>select current_scn from v$database;
恢复:
RMAN>run {
startup force mount;//恢复需要在mount模式下进行
set util scn=填SCN号;
restore database;
recover database;
alter database open resetlogs;//数据库恢复之后必需以重置日志的方式打开一次
}
3、基于SEQUENCE日志序号的数据恢复。
日志归档要复用,且放在不同磁盘上:
备份:
>delete noprompt backup;
>delete noprompt copy;
>archive log list;
>backup incremental level 0 database;
>sql 'alter system archive log current';
>resync catalog;
查日志的序号:
>desc v$log;
>select group#,sequence#,status from v$log;//序号为current为当前,
先进行表的添行DML语句,再切日志以增加日志序号:
>alter system switch logfile;
恢复:
RMAN>run {
startup force mount;
set util sequence=6;//恢复到第5组日志
restore database;
recover database;
alter database open resetlogs;
}
4、所有控制文件损坏:
备份:
>delete noprompt backup;
>delete noprompt copy;
>archive log list;
>backup incremental level 0 database;
>sql 'alter system archive log current';??含义
>resync catalog;
查看目前控制文件:
>select name from v$controlfile;
>shutdown abort;
>删除控制文件
启动到nomount并用RMAN进行恢复:
>startup nomount;
RMAN>configure control file auto backup;
RMAN>restore controlfile from autobackup;
RMAN>alter database mount;
RMAN>recover database;//文件不同步,仍要进行recover
RMAN>alter database open resetlogs;
再进行数据库备份:
5、
>select group#,sequence#,status from v$log;
>create table test100(id int);
>insert into test100 values(1);
>commit;
>alter system switch logfile;
>insert into test100 values(2);
>commit;
>alter system switch logfile;
>select group#,sequence#,status from v$log;
rman target / catalog catu/catu@catdb
startup mount;
restore database;
recover database until cancel;参数AUTO
recover database until cancel;参数CANCEL
alter database open resetlogs;
第十天
160724第十天OCP笔记
备份脚本:
run{
>delete noprompt backup;
>delete noprompt copy;
>archive log list;
>backup incremental level 0 database;
>sql 'alter system archive log current';
>resync catalog;
}
&灾难恢复。灾难恢复不含跟踪文件、预警文件、口令文件。
日常维护--
RMAN中启用控制文件自动备份configure control file auto backup
建立catalog
检查备份--
$rman target / catalog catu/catu@catdb
RMAN>list backup;
检查备份内容是否好:
RMAN>crosscheck backup;
破坏--
用联合查询查出控制文件、数据文件、日志文件:
>select name from v$datafile union select name from v$tempfile union select name from v$controlfile union select member from v$logfile;
>关闭数据库
asmcmd>删除以上文件
查参数文件并删除:
asmcmd>rm +DATA/orcl/spfileorcl.ora
查口令文件并删除:
$rm $ORACLE_BASE/dbs/orapworcl;
恢复--
恢复顺序:参数文件、控制文件、数据日志文件、口令文件。
文件损坏只参启到实例:
RMAN>startup nomount;//注意文件未损坏,要删除。否则会调用错误的参数文件而无法恢复
恢复参数文件:
RMAN>restore spfile from autobackup;//默认备份目录+FRA/ORCL/AUTOBACKUP/以日期命名的目录/。若无法恢复则用完全路径
RMAN>shutdown immediate;
RMAN>startup nomount;
恢复控制文件后可以挂载启动:
RMAN>restore controlfile from autobackup;
RMAN>shutdown immediate;
RMAN>startup mount;
恢复数据文件:
RMAN>restore database;
SQL>recover database until cancel using backup controlfile;//。注意恢复日志文件的跑日志要在SQLPlus中,填参数为先AUTO、后CANCEL。???
恢复日志和临时文件:
SQL>alter database open resetlogs;//也可以在RMAN中执行
创建口令文件:
$orapwd file=%ORACLE_HOME/dbs/orapworcl password=oracle;
删除备份并重新备份:
&灾难恢复需要换设备,不能恢复到原始设备上的问题处理。
建目录:
$mkdir -p /u01/app/oracle/oradata/bak/;
查文件位置:
>select name from v$datafile;
确认备份:
RMAN>list backup;
恢复脚本:(添加了set newname,switch datafile)
run{
sql 'alter database datafile 4,5 offline';
startup mount;
set newname for datafile 4 to '/u01/app/oracle/oradata/bak/users01.dbf';//项ASM磁盘组则换为'+DG',DG为换上的新磁盘组。
set newname for datafile 5 to '/u01/app/oracle/oradata/bak/examples01.dbf';
restore datafile 4,5;
switch datafile 4;
switch datafile 5;
recover datafile 4,5;
sql 'alter database datafile 4,5 online';
}
&ASM的迁移。
创建一个非ASM的表空间--
$mkdir -p $ORACLE_BASE/oradata/orcl/
>create tablespace tbs10 datafile '/u01/app/oracle/oradata/orcl/tbs10.dbf' size 20M autoextend on;
>create table sys.test10 tablespace tbs10 as select * from dba_objects;
>select name,file# from v$datafile;
将非ASM表空间迁移到ASM--
查看未备份项:
RMAN>report need backup;
拷贝备份到指定磁盘组:
RMAN>backup as copy datafile 6 format '+DATA';
RMAN>sql 'alter database datafile 6 offline';
切换文件名:
RMAN>switch datafile 6 to copy;//迁移地址可以简写为copy
由于操作延迟,将文件前滚一点:
RMAN>recover datafile 6;
RMAN>sql 'alter database datafile 6 online';
&Blobk块的损坏。
开启校验
物理校验db_block_checksum=TYPICAL 耗CPU2~3%
逻辑校验db_block_checking=MEDIUM 耗CPU1-10%
物理校验在块头写入校验信息,逻辑校验校验段头块和段之间的关系。校验只能预防坏块。
文档
创建表空间tbs20并加表test20--
查看表的块信息:
>select header_file,header_block,blocks from dba_segments where segment_name='TEST20' and owner='HR';
备份--
RMAN>report need backup;
RMAN>backup datafile 7;//7是新建的表空间对应文件
破坏块--
#dd dd of=/u01/app/oracle/oradata/orcl/tbs20.dbf bs=8192 conv=notrunc seek=200<
>任意填覆盖信息
>EOF;//结束
制造查询出错,报错为第一个坏块--
>alter system flush buffer_cache;
>exit;
$sqlplus hr/hr
>select count(*) from sys.test20;
修复坏块--
#su - oracle
找到所有坏块:
$dbv file=$ORACLE_BASE/oradata/orcl/tbs20.dbf blocksize=8192;
用RMAN修复坏块:
RMAN>recover datafile 7 block 坏块序号用逗号隔开;
附dd命令:
将指定的文件写入到指定的输出文件:
#dd if=boot.bin of=orange.img bs=512 count=1 conv=notrunc;
&Oracle11g新特性DRA Database Recovery Advisor数据恢复顾问
使用DRA用来修复坏块--
发现错误:
RMAN>list failure detail;
修复建议:
RMAN>advice failure 错误号;
修复错误:
RMAN>repair failure;
&闪回技术
闪回删除 回收站
闪回查询 UNDO表空间
闪回事务 redo log
闪回数据库 数据库的闪回日志
Tocal Recall 闪回归档
1、闪回删除
drop table并没有删除而是放到回收站。每个表空间有独立的回收站,除了SYSTEM表空间。
开启回收站:
>show parameter recyclebin
>alter system set recyclebin on;
实验--
>create table hr.t1 tablespace system as select * from dba_objects where rownum<10001;
>create table hr.t2 tablespace system as select * from dba_objects where rownum<10001;
>c/2/3;把2换成3再创建张表
>create index hr.idx_t1 on hr.t1(object_id) tablespace system;
>create index hr.idx_t2 on hr.t1(object_id) tablespace system;
>create index hr.idx_t3 on hr.t1(object_id) tablespace system;
>drop table hr.t1;//删除表会自动删除索引
>drop table hr.t2;
>drop index hr.inx_t3;
>drop table hr.t3;
显示回收站内容:
>show recyclebin;
加收站中的名称较长,用数据字典查其原名user_recyclebin或dba_recycle_bin:
>select object_name,original_name,type from user_recyclebin;
回收站中的数据可以查询SELECT,而不可以DML/DDL操作。表的删除其索引也被附带删除。
闪回:
>flashback table t2 to before drop;(闪回表的相关索引同时闪回)
若回收站中有重名,需使用回收站中的名称,需用双引号界定,并重命名:
>flashback table "回收站中的名称" to before drop rename to t2_other;
以下两种情况表删除不经过回收站:drop table t1 purge;、表经过virtual personal database(VPD)加密。
当有空间压力时,回收站中的内容会自动清空。自动扩展的表空间有空间压力也要先清空回收站。
>purge table t2;
>purge index t1_idx;
清空回收站:
>purge user_recycle_bin;purge dba_recycle_bin;
>purge tablespace users USER hr;
2、闪回查询
undo_tablespace=undotbs1;
undo_management=auto;
undo_retention=900;
用于已提交DML的闪回查询。
>select salary from employees where employee_id=196;
>update employees set salary=13100 where employee_id=196;
>commit;
闪回查询:
>select salary from employees as of timestamp(to_timestamp('2016-07-20 01:00:00','YYYY-MM-DD HH24:MI:SS')) where employee_id=196;
用子查询查闪回值:
>update employees set salary=(select salary from employees as of timestamp(to_timestamp('2016-07-20 01:00:00','YYYY-MM-DD HH24:MI:SS')) where employee_id=196) where employee_id=196;
将表闪回到过去的时间点:
>alter table employees row movement;//开启行迁移功能
>flashback table employees to timestamp(to_timestamp('2016-07-20 01:00:00','YYYY-MM-DD HH24:MI:SS'));
通过伪列(versions_starttime versions_endtime)看列具体的时间:
>select salary,versions_starttime,versions_endtime from employees versions BETWEEN to_timestamp('2016-07-20 01:00:00','YYYY-MM-DD HH24:MI:SS') AND to_timestamp('2016-07-20 02:00:00','YYYY-MM-DD HH24:MI:SS') where employee_id=196;
3、闪回事务 Redo Log
开启补充日志:
>alter database add supplemental log data;
>alter database add supplemental log data(primary key) columns;//需要SYSDBA权限
DML:
>update hr.employees set salary=8100 where employees_id=196;
查看闪回事务:
>select undo_sql from flashback_saction_query where table_name='EMPLOYEES' and TABLE_OWNER='HR';//需要SYSDBA权限
>update "HR" ."EMPLOYEES" set "SALARY"='6100' where rowid='闪回事务的rowid';
4、闪回数据库--数据库闪回日志
前提要先启用归档模式
>shutdown immediate;
>startup mount;
启用数据库闪回功能:
>alter database flash on;
检查已开启闪回功能:
>select flashback_on from v$database;
#ps -ef|grep ora_rvwr
查看闪回日志位置:
>show parameter db_recover_file_dest;
设置闪回日志保留时间:
>alter system set db_flashback_retention_target;
误操作truncate table的解决:
RMAN>startup force mount;
RMAN>flahsback database to time="to_date('2016-07-20 11:57:00','YYYY-MM-DD HH24:MI:SS')";//注意time时间赋值加引号
RMAN>alter database open resetlogs;
RMAN>flahsback database to SCN=值;
RMAN>flahsback database to SEQUENCE=值;
用还原点还原--
>create restore point aaa;
>truncate table hr.t2;
>starup force mount;
>flashback database to restore point aaa;
>alter database open resetlogs;
闪回的限制:
1、控制文件重新建立过或者还原过:
2、删除过表空间;
3、收缩过数据文件;
这些情况只能数据库的不完全恢复来解决。
5、闪回归档Total Recal,用于闪回查询的UNDO表空间限制undo_retention
>create tablespace recall datafile '+DATA' size 20M autoextend on;
>create flashback archive default flash_recall tablespace recall retention 10 year;
>alter table hr.employees flashback archive flash_recall; select salary from employees as of timestamp(to_timestamp('2016-07-20 10:44:29','yyyy-mm-dd hh24:mi:ss'));
第十一天
160727第十一天OCP笔记
移动数据(Oracle的IO)--
非Oracle数据移到Oracle数据 用装载SQL Loader
Oracle数据移到Oracle数据 用导入导出DataDump
外部表技术:将数据旋转在数据库外部,通过数据库访问外部数据。分为非Oracle数据外部表,和Oracle数据外部表。
官方文档
//****************************************************************************************************
&SQL Loader使用--
TXT/EXCEL/SQL Server/。。
编缉数据文件:
$vim f1.dat
101,M,zhao
102,M,qian
103,F,sun
编缉控制文件(导入语法):载入文件、文件导入的表符列。
$vim f1.ctl
load data infile 'f1.dat'
insert into table students WHEN (5)=M fields terminated by ','
TAILING NULLCOLS
(stu_id,stu_sex,stu_name)
其中fields指字段。每行就是数据库行的分隔符,不用指定。
INSERT|APPEND|TRUNCATE关键字,INSERT只能向空表加入、APPEND追加、TRUNCATE先清空后添加。
WHEN (5)=M第五个字符为M的行放弃数据,第一个字符为1。未装载数据分为放弃数据和拒绝数据。
建表:
>create table students (stu_id number(3),stu_sex char(1),stu_name varchar2(10))
装载:
$sqlldr hr/hr control=f1.ctl;//需要指定向哪个用户装载数据
$sqlldr hr/hr control=f1.ctl log=f1.log bad=f1.bad;//功能同上条语句
产生f1.log存导入记录,f1.bad存未导入的记录。
对表添加约束(功能同放弃数据):
>alter table students add constraint ck_name CHECK(stu_id not in('qian'));
&装软件包使SQLPlus能够翻命令:解包、配置、编译、安装、加别名
#tar zxf rlwrap-0.36.tar.gz
#./configure
#make
#make install
#echo "alias sqlplus='rlwrap sqlplus'">>/home/oracle/.bashrc;//两个剑头指追加
#echo "alias rman='rlwrap rman'">>/home/oracle/.bashrc
查命令历史:#history
//****************************************************************************************************
&DataPump使用--
命令 expdp和impdp
数据包dbms_datapump
创建目录对象:
>create directory dump as '/u01/app/oracle/dump/';//目录由oracle帐户创建,命令需要SYSDBA权限
>grant read,write on directory dump to hr,system,scott,sh;
导出表:
$expdp hr/hr directory=dump dumpfile=employees.dmp tables=employees,departments;
导出schema的所有表:
$expdp hr/hr directory=dump dumpfile=employees.dmp schema=hr;
导出tablespace:
$expdp \'sys/oracle as sysdba\' directory=dump dumpfile=employees.dmp tablespaces=users,examples;//需要SYSDBA权限
导出db:
$expdp \'sys/oracle as sysdba\' directory=dump dumpfile=employees.dmp full=y;
参数文件1,含directory、dumpfile参数:
$vim 02.par
directory=dump
dumpfile=hr02.dmp
schemas=hr
exclude=table:"in ('jobs','locations')"
参数文件2,导出部分表:
$expdp scott/tiger directory=dump dumpfile=scott.emp parfile=/tmp/01.par
$vim 01.par;//用参数文件
schemas=scott
include=table
include=package
include=view:"LIKE 'V0%'"
参数文件3,只导出查询结果:
schemas=hr
tables=employees
query=employees:"where salary>15000"
参数文件4,导出表的采样:
tables=employees
sample=40
参数文件5,导出元数据或只导出数据:
tables=employees
content=metadata_only|data_only|all
导入表:
>create table emp01 as select * from employees;//建表
$expdp hr/hr directory=dump dumpfile=emp01.dmp tables=emp01;//导出表
>drop table emp01;//删除表
$impdp hr/hr directory=dump dumpfile=emp01.dmp tables=emp01;//导入表
>select * from emp01;//查询是否导入
只导入数据而不导入元数据:
$impdp hr/hr directory=dump dumpfile=emp01.dmp tables=emp01 content=data_only;
追加|清空方式导入:
$impdp hr/hr directory=dump dumpfile=emp01.dmp tables=emp01 table_exists_action=append|truncate;//导入表
将表映射导入到其它帐户:
$expdp scott/tiger directory=dump dumpfile=emp01.dmp tables=hr.emp01 schemas_map=hr:scott;
$impdp scott/tiger directory=dump dumpfile=emp01.dmp tables=hr.emp01 schemas_map=hr:scott remap_tablespace=example:users;//如果映射到的帐户没有表空间,则创建
//****************************************************************************************************
&数据放在Oracle外,外部表只能查不能改--
非Oracle到Oracle 装载数据驱动 ORACLE LOADER Access Driver
create table students_e (stu_id number(3),stu_sex char(1),stu_name varchar2(10))
organization external
(type oracle_loader
default directory dump
access parameters (records delimited by newline fields terminated by ',' missing fields values are null)
location ('f1.dat'));
外部只能查询,不能做索引,也不能DML操作。由于外部表的性能较低,只用于偶尔的查询。
&Oracle到Oracle的访问,即直接访问其它数据库而不用导入数据--
数据泵驱动 ORACLE DATAPUMP Access Driver
方法共3步。
从orcl导出hr.employees--
CREATE TABLE students_e (stu_id,stu_sex,stu_name)
organization external
(type oracle_loader
default directory dump
location ('employees.dmp'))
AS SELECT employee_id,last_name,salary FROM employees where salary>10000
copy到目标数据库所有机器--
接收的catdb数据库也需要创建目录对象和授权:
本地或网络拷贝到目标机器的目录对象:
在目标catdb建外部表,读取该文件:
CREATE TABLE students_e (stu_id number,stu_sex char(1),stu_name varchar2(10))
organization external
(type oracle_loader
default directory dump
location ('employees.dmp'))
//****************************************************************************************************
内存管理
6大池除了log_buffer均为自动设置。
6大池之外的保留缓冲池db_keep_cache_size、循还缓冲池db_recycle_cache_size、nK缓冲区调整缓存db_nK_cache_size基中n为2、4、8、16、32。
块大小db_block_size,根据行大小设置块大小,1块中保存多行。
保留缓冲池重用机率很高,循还缓冲池重用机率很低。
高重用的表缓冲到保留缓冲池:
>alter table hr.employees storage (buffer_pool recycle);
转存到默认缓冲池:
>alter table hr.employees storage (buffer_pool default);
>alter system set db_16k_cache_size=16M;
>create tablespace tbs16 datafile '+DATA' size 20M autoextend on blocksize 16k;
表空间块大小一般与buffer_cache块大小一致,不同则先转到db_nK_cache_size缓冲,再转到buffer_cache。表空间的行尽量放在不同块中,目的是提高并发量。
db_nK_cache_size设置小,全表扫描慢而并发访问变块。
查看各池容量:
>select component,current_size from v$sga_dynamic_components;
sga_target
pga_aggregate_target
memory_target
memory_max_target
共享池分为:library cache、row cache、result cache结果缓存(存储已查询结果,一般不开启,需配合参数文件开启)、
若做成共享服务器,则原本在PGA中的UGA(用户全局区)会移动到SGA的大型池中。
>select name,value from v$sysstat where name='physical reads';//查物理读
>select name,value from v$sysstat where name='consistent gets';//查一致性读
>select name,value from v$sysstat where name='db block gets';//查当前读
命中率:1-物理读/(一致性读+当前读),命中率高而性能未必高。
AWR report查看命中率报告:
Performance-AWR Baselines
第十二天
160728第十二天OCP笔记
医院建立信息数据库,收集健康设备的信息。
OLPT系统调优的首要原则就是绑定变量。
使用绑定变量避免硬解析:
var v1 number
exec :v1:=196
select salary from employees where employee_id=:v1;
PL/SQL的变量、赋值号加冒号。
让Oracle自动替换成绑定变量:
>alter system set cursor_sharing=force;//force的引号可加可不加
查询执行次数、解析次数:
>select executions,parse_calls from v$sqlarea where sql_text like '语句';//需要SYSDBA权限,也可以用表v$sql
SEQUENCE对于单实例数据库cache 100。多实例RAC数据库cache 5000。
pin住PL/SQL对象
自动共享内存管理。MMIN收集工作量为信息基础,MMAN调整内存。白天为OLPT在线查询系统,晚上为OLAP在线分析系统。
由AMF实现,配合spfile实现。早期版本的Oracle用init初始化参数,缺点是参数不能被系统修改。
性能优化方法:
规划:投资、系统(可扩展性Linux最强)...
SQL优化(应用优化) 对应课程SQL turning 调整:SQL PL/SQL
性能优化(实例优化)对应课程Performance Turning 调整:内存、I/O、网络、资源如lock
v$sys_time_model系统时间模型 v$sess_time_model会话时间模型。用于查询数据库启动、SQL解析、SQL执行、SQL编译等时间的消耗。
生成AWR报告:
@?/rdbms/admin/awrrpt.sql
报告保存在/home/oracle/
用EM查看资源的会话消耗:
EM-Performance-TopCustomers-TopSessions
/****************************************************************************************************
???
SQL优化指导 STA sql tuning advisor
Automatic SQL Tuning Result
SQL访问指导 SAA sql access advisor
SQL性能分析 SPA sql performance analyzer 位置:EM-Server-AdvisorCentral-sql performance analyzer
自动化任务 EM-Server-Automatic-AutomaticSqlTuning
EM-Server-AdvisorCentral-
优化指导:个别语句用Automatic SQL Tuning Result优化、使用SQL Profile直接修改执行计划、收集统计信息、使用索引、修改SQL语句。后3条都是间接干涉执行计划。
第十三天
160729第十三天OCP笔记
江苏移动外包:
限制job资源使用:
PLAN--group??
EM-Server-OracleScheduler-Window
CreateWindow-Name:DayWindow-Repeat:by days-duration:12h
CreateWindow-Name:DayWindow-Repeat:by days-duration:12h
>ater system set resource_manager_plan;
&管理空间。
OMF管理数据文件/控制文件的参数:db_create_file_dest=+DATA
日志文件/控制文件:db_create_online_log_dest
块--
块大小db_block_size。块大小优先由表空间blocksize决定,未指定则由db_block_size决定。
块预留10%的pct free空间,仅用于更新数据UPDATE。块的pct free用完就需要行迁移了。
块空闲空间按剩余空间分类:fs1<25% ,25%
空表不占空间,只是在数据字典中留记录。
数据录入是无序的,进行表的整理可以提高全表扫描的性能。
行链接:行大于块大小。行迁移:update的新数据大于旧数据且块pct free用完,将旧块指向新块的地址。
避免行链接的方法:
>alter system set db_16k_cache_size=16M;
>create tablespace tbs16 datafile '+DATA' size 20M autoextend on blocksize 16k;
解决行迁移:
使用段指导发现行迁移并解决。可以添加自动化任务定期解决。
手工处理行迁移--
>create table scott.t1 (id number,name varchar2(2000));
begin
for i in loop 1..1000
insert into scott.t1 values(i,'aa');
end loop;
commit;
end;
/
>update scott.t1 set name=lpad('x',1550,'x') where mod(id,2)=1;//语法lpad(列名,长度,填充符)
查具有行移的表:
>@?/rdbms/admin/utlchain.sql;
>analyze table scott.t1 list chained rows;
拷出来(拷出具有行迁移的行):
>create table t1_chain as select * from scott.t1 where rowid in(select head_rowid from chained_rows);
清空表:
>select count(*) from scott.t1_chain;//查下迁移行数量
>delete scott.t1 where rowid in(select head_rowid from chained_rows);
再拷回去:
>insert into scott.t1 select * from scott.t1_chain;
区的管理--
extent management local
segment space management auto|manual
段的延迟创建(创建表时并不分配空间,插入时创建):
>alter system set deferred_segment_createion=true;
Oracle在安装时会创建很多永远不会用到的空表,段延迟创建可以节约空间和加快安装速度。
段的立刻创建:
>create table t1 segment creation immediate as select * from t2 where 1=2;
按需创建表只适用于非分区表和索引,不适用于IOT、聚簇表...,不适用于字典管理的表空间中的表。
先创建索引逻辑不使用,以后使用:
>create index t1_idx on t1(id) unusable;
>alter index t1_idx rebuild;
表压缩减少IO多耗CPU,BASIC压缩方式适用于OLAP/DSS数据仓库,FOR OLTP压缩方式适用于OLTP常规交易型数据库。
>create table t1 compress basic|for olpt|nocompress;
收缩段的3步:
>alter table t2 enable row movement;
移动行 进行块的整理:
>alter table t2 shrink space compact;
降低高水位HWM 释放空间:
alter table t2 shrink space;
如果只执行第3步,不执行第2步,则在收缩段期间不能进行DML语句。
收缩段的优点是节约空间,提高全表扫描性能。
管理可恢复的空间分配--
会话交互(会话遇到空间问题,等待问题解决而非终止):
>alter session enable resumable;
系统级别可以设置会话时间,超时由系统选择:
>show parameter resu
>alter system set resumable_timeout=3600;//单位:秒
目前磁盘为4K扇区,Linux内核3.0以上支持4K扇区磁盘。要求db_block_size要4K~32K,默认8K。32以上需要OS支持。
查看日志的块大小:
>select blocksize from v$logfile
建库时要设置日志组块大小16K,提高性能。
移动表空间,对于使用不同指令集的CPU,需要用RMAN提供的convert命令。--
过程:表只读、datadump导出metadata、转字节码、拷元数据及数据文件、datadump导入、表读写。
建表:
>create tablespace test datafile '+DATA' size 10M;
1、在orcl数据库将表空间置为read only:
>alter tablespace test read only;
2、使用datadump提取表空间的metadata为一个文件:
>create directory dump as '/u01/app/oracle/dump/';//目录由oracle帐户创建,命令需要SYSDBA权限
>grant read,write on directory dump to hr,system,scott,sh;
$expdp system/oracle directory=dump dumpfile=test_m.dmp transport_tablespaces=test;
3、(目标与原使用相同的字节序endian,若不同需要用RMAN转换:
RMAN>sql 'alter tablespace hr read only convert tablespace hr to platform '操作系统平台' format '路径';
4、传送元数据和数据两部分文件到catdb:
$cp /u01/dump/tbs2_m.dmp /u01/catdb/dump;
$cp /u01/app/oracle/oradata/orcl/tbs2.dbf /u01/app/oracle/oradata/catdb;
5、使用datapump导入metadata:
$export $ORACLE_SID=catdb
$impdp system/oracle directory=dump dumpfile=test_m.dmp transport_datafiles=/u01/app/oracle/oradata/catdb/tbs2.dbf;//???
6、置表为read write:
>alter tablespace test read write;
移动数据库。
1、备份源库 orcl数据库
RMAN>backup database format '/u01/app/oracle/backup/%U';
RMAN>backup archivelog all format '/u01/app/oracle/backup/%U';
修改参数文件--
>create pfile from spfile;//生成initorcl.ora
initorcl.ora改名initprod.ora
在spfile中添加(数据库改名):
db_file_name_convert='orcl','prod';
log_file_name_convert='orcl','prod';
创建spfile中相应的目录:
启动数据库--
$export $ORACLE_SID=prod;
$sqlplus / as sysdba;
>startup nomount;
使用RMAN连接--
$export $ORACLE_SID=prod;
$rman sys/oracle@orcl auxiliary /;//连接辅助数据库
RMAN>duplicate target database to prod;
恢复数据库:
run {
sql 'alter database datafile 4,5,6 offline';
set newname for datafile 4 to '/u01/app/oracle/oradata/orcl/users01.dbf';
set newname for datafile 5 to '/u01/app/oracle/oradata/orcl/example01.dbf';
set newname for datafile 6 to '/u01/app/oracle/oradata/orcl/test.dbf';
restore datafile 4,5,6;
switch datafile 4;
switch datafile 5;
switch datafile 6;
recover datafile 4,5,6;
sql 'alter database datafile 4,5,6 online';
}
改fstab参数:
>startup nomount
$vi /etc/fstab
tmpfs /dev/shm tmpfs defaults,size=4g 0 0
devpts /dev/pts devpts gid=5,mode=620 0 0
sysfs /sys sysfs defaults 0 0
proc /proc proc defaults 0 0
/dev/VolGroup00/LogVol01 swap swap defaults 0 0
#mount -o remount /dev/shm
报名考试:VOE网站注册OC号,Oracle网站注册号并绑定,找徐老预约考试,考试时间和科目。
网友评论