15天的OCP培训笔记

96
fjxCode
2018.07.12 20:49* 字数 19191

第一天

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网站注册号并绑定,找徐老预约考试,考试时间和科目。

基它