美文网首页oracle
15天的OCP培训笔记

15天的OCP培训笔记

作者: fjxCode | 来源:发表于2018-07-12 20:49 被阅读231次

    第一天

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

    相关文章

      网友评论

        本文标题:15天的OCP培训笔记

        本文链接:https://www.haomeiwen.com/subject/yxxdpftx.html