15天的OCP培訓筆記

第一天

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盤沒還原卡庐橙。

操作系統(tǒng)：語言選英文（免環(huán)境變量NLS_LANG）靜態(tài)IP：200.100.50.13/24独撇，主機名server13.example。時區(qū)：上海，不用UTC。密碼codecraft损趋。軟件全選。關閉防火墻椅寺，SELinux禁用浑槽。Kdump內核轉儲不選蒋失。不創(chuàng)建帳戶。分辨率1024*758（系統(tǒng)-管理-顯示-硬件-LCD-1024*768,首頁也要改桐玻，改后重啟）篙挽。

裝Oracle11gR2：

建組 oinstall dba oper 用戶oracle/密碼oracle1158

建目錄/u01/app/oracle，改所有者畸冲，改權限775

加環(huán)境變量：

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帳戶，執(zhí)行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。測試應該狀態(tài)成功儡陨。

按F9執(zhí)行sql語句褪子。Sqldeveloper命令可修改，帶Tab自動補齊和Ctrl+BackSpace刪單詞使用更方便骗村。

顯示用戶：

>show user

不同帳戶有不同的表嫌褪，只有hr帳戶有employess表，而sysdba中沒有胚股。

用數據字典顯示所有的表：

>select table_name from dba_tables;//管理員的擁有表

>select table_name from user_tables;//用戶的擁有表

查看表結構（相看表有哪些列）：

>desc 表名

相看表內容：

>select語句

關鍵字和對象名不區(qū)分大小寫笼痛，換分是任意的。

查詢：

>select 列|表達式 from 表;//表達式不影響原表

查詢的列別名AS "別名"：解

>select first_name,last_name,salary*12 as "nianxin" from employees;

一般表達式都是加別名便于理琅拌。別名可以不加引號缨伊，但有空格的別名必須加引號。

查詢的去重復行：

>select distinct department_id from employees;

注意：表達式中與NULL有關的任何計算均為NULL进宝。

雙堅線字符串連接用雙堅線||

當字符串間中單引號刻坊，使用q'[]'括起字符串。

每課的練習只有英文版中才有党晋。作業(yè)為Practice for lesson1谭胚。

第二天

7月14日OCP筆記：

數據類型：NUMBER DATE VARCHAR2

DATE要求單引號界定，大小寫區(qū)分未玻，格式滿足要求灾而。

ORA-27300錯誤導致無法啟動數據庫，需要修改內核參數#vim /etc/sysctl.conf加入kernel.sem=250 32000 100 128完成深胳。

關鍵字绰疤、表名、列名都不區(qū)分大小寫舞终。

選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最常用功能：條件判斷轻庆，循還癣猾。還有函數，存儲過程余爆，包纷宇，觸發(fā)器。

單行函數蛾方，轉換函數像捶，組函數可以無需PL/SQL編程，直接實現(xiàn)常用功能桩砰。

單行函數單進單出拓春，多行函數則是多入單出。功能在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保存文件：(需要創(chuàng)建目錄)

>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;

顯示轉換區(qū)分大小寫，以下兩條語句不同：

>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熄浓。

創(chuàng)建一個同結構的空表：

>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語句自動提交（隱式提交）。正常退出也會自動提交筷黔。系統(tǒng)崩潰則回滾rollback往史。

select for update;鎖定被修改的行直到commit或update才釋放。

模擬崩潰：

#ps ef|grep oracleorcl;//查看進程號

#kill -9 進程號;

練習：建表佛舱，錄入10行椎例，試下不同的提交方式。

標記保存點：

>savepoint p1;

回滾到保存點：

>rollback p1;

利用偽列選擇前10行：

>select * from employees where rownum<11;

只有提交的數據才能被其它用戶可見请祖。修改的未提交數據订歪，其它用戶不可修改，只能訪問修改前的數據肆捕。

讀一致性：讀取的數據為查詢命令的執(zhí)行時間的數據刷晋。在數據區(qū)實現(xiàn)修改，修改前數據通過構造CR塊實現(xiàn)。

SELECT是無鎖眼虱，DML會產生鎖,鎖為行鎖喻奥。commit將釋放行鎖。

DDL語句蒙幻。

命名規(guī)則：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關鍵字石咬。否則將出現(xiàn)ora-02253錯誤，“這里不允許限制聲明”卖哎。

教材31頁可以練習創(chuàng)建主外鍵的關系鬼悠。

檢查約束：

>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參數，聯(lián)動修改亏娜，刪除主鍵相關行同時刪除外鍵相關行：

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參數焕窝，聯(lián)動修改，刪除主鍵相關行同時將外鍵相關行置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);

修改列默認值维贺、列約束條件的內容前面講過它掂。

創(chuàng)建視圖：

>create view v1 as select * from employees;

視圖本質上就是封閉一條查詢語句。視圖也可也像表那樣用select desc幸缕。

視圖不能刪除群发，只能刪除再創(chuàng)建：(兩種方法)

>drop view v1;

>create view v1 as select * from employees;//方法1

>create or replace as ...;//方法2

創(chuàng)建視圖問題：

>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參數发乔，修改后值將不可見熟妓，添加此參數將拒絕修改。

創(chuàng)建只讀視圖：

>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當前值

索引跺涤。

索引可以自動創(chuàng)建或手動創(chuàng)建，自動使用监透。全內存數據庫不需要創(chuàng)建索引桶错。且索引增加DML負擔，浪費系統(tǒng)資源胀蛮。

數據庫查詢有全表掃描和索引掃描牛曹，等方式。Oracle根據執(zhí)行計劃進行成本判斷決定是否使用索引醇滥。當具有索引且索引有助于（執(zhí)行方案選優(yōu)）查詢才使用索引。

例如select count(*) from t1;是用全表掃描的超营，當有where條件時可能用索引鸳玩。

以某一列創(chuàng)建索引：

>create index inx_test001_id on test_001(id);

創(chuàng)建索引的情況：列值范圍很廣，列中包含大量的空值演闭，在where子句頻繁用表的列尤其是多表聯(lián)接的等值聯(lián)接條件不跟，表很大（萬行以上）卻查詢結果小于4%。

OLPT系統(tǒng)改的多查的少米碰，一般不建索引窝革。

upper(last_name)='SMITH'代替last_name='Smith'會解決輸入大小寫的問題，但不再使用索引吕座。

同義詞：

>create synonym emp for employees;

>create pulbic synonym e1 for hr.employees;//需要sysdba權限虐译，創(chuàng)建所有人都可以使用的同義詞。

第一章了解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:一個共享區(qū)域（System Global Area SGA）和N個私有區(qū)域（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

創(chuàng)建組：asmadmin asmdba asmoper oinstall dba oper

創(chuàng)建帳號：#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。

環(huán)境變量修改部分：export $ORACLE_BASE=/u01/app/grid;export $ORACLE_HOME=/u01/app/grid/product/11.2.0/gridhome_1;ORACLE_SID=+ASM

準備ASM磁盤：40G骨宠，平均分11個區(qū)谚赎。

查看Linux內核版本：#uname -r

安裝ASMLib：#rpm -ivh /光盤目錄/*;

配置ASMLib服務：#/etc/init.d/oraleasm configure; 參數:grid/asmadmin/y/y淫僻。注意當前目錄執(zhí)行要用./oracleasm configure;

轉化為ASM磁盤：#/etc/init.d/oraleasm createdisk asmdisk01 /dev/sdb1;//注意sdb4分區(qū)是擴展區(qū)不能使用，注意是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;//執(zhí)行安裝

選：Install and Configure Grid Infrastructor for a Standalone Server;數據磁盤選DATA Normal;口令;其它同Oracle安裝

用grid帳戶執(zhí)行$asmca;把5-8磁盤選成FRA壶唤。

（$sqlplus / as asmdba;>startup;//啟動ASM實例）

安裝OracleDatabase軟件：

Oracle用戶下運行./runInstaller,只安裝不建庫雳灵，單實例數據庫（不選RAC，real application cluster）,執(zhí)行腳本參數為缺省的不覆蓋闸盔。

建庫：

$dbca悯辙，General模板（General為OLPT模板，DATA為倉庫模板）迎吵。全局數據庫名任意起名躲撰，SID與環(huán)境變量一致。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（包裝進程）：實現(xiàn)自動啟動監(jiān)聽意推、ASM實例、數據庫實例珊蟀。

數據庫邏輯結構：數據庫-表空間-段-區(qū)-塊。段是空間分配單元外驱。表不夠用時育灸，以區(qū)為單位擴展。一般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;

查看表所在表空間、表瓦哎、段砸喻、區(qū)柔逼、塊：(表空間是users，而所有者是SYS)

>select tablespace_name,segment_name,blocks,extents,bytes/1024/1024 from dba_segments where owner='SYS' and segment_name='TEST';//注意SYS大寫割岛，表名也必需大寫愉适，不能用通配符？

循還錄入數據（注意加/執(zhí)行）：

>begin

for i in 1..1000 loop

insert into test values(i,'aa');

end loop;

commit;

end;

啟動監(jiān)聽：無GI用oracle帳號 lnsrctl start癣漆，有GI用grid帳號维咸。再啟數據庫、EM(EM啟動需要創(chuàng)建安全例外)惠爽。

執(zhí)行腳本：>sqlplus hr/hr@scripts.sql

數據庫分階段診斷啟動：過程instance started;database mounted;database open;

>start nomount;//若執(zhí)行startup mount直接執(zhí)行到第2階段

>alter database mount;

>alter database open;

數據庫4種關閉方式癌蓖。生產環(huán)境多用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;//文件系統(tǒng)

#strings +DATA/orcl/spfileorcl.ora;//ASM設備

其中sp指separate physical

SQL清屏：

>!clear;

Oracle運維需要查看：

1用僧、預警文件 ? ? ?? 運行常規(guī)信息

1、進程跟蹤文件 ?? 各類進程两残，用地排錯

3永毅、動態(tài)性能視圖 ?? 日常性能監(jiān)控內存表的視圖

4蜕窿、數據字典視圖 ?? 數據庫的物理和邏輯信息數據字典的視圖

從References 的PartIII Dynamic Performance View

動態(tài)性能視圖：

用v$sql保存最近執(zhí)行的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';

同時添加刪除磁盤(經常用于換盤健芭，用一條執(zhí)行的更快)：

>alter diskgroup DG1 add disk 'asmdisk14' drop disk 'asmdisk12';

刪除磁盤組：

>drop diskgroup dg1;

實例管理ASM磁盤組（EM）：

進入General下的ASM县钥，刪除ASM磁盤要force。

創(chuàng)建磁盤組參數：

Redundancy冗余參數有high（至少三塊磁盤）慈迈、normal（至少兩塊磁盤）若贮、external（不冗余至少一塊磁盤）

Allocation Unit分配單元

創(chuàng)建故障組（EM）：選若干塊磁盤，F(xiàn)ailureGroup起相同的名字痒留。

創(chuàng)建故障組（SQLPLUS）：

>create diskgroup dg normal redundancy failgroup f1 disk 'asmdisk09','asmdisk10';

>create diskgroup dg normal redundancy failgroup f2 disk 'asmdisk10','asmdisk11';

區(qū)映射：只了解谴麦。

ASM磁盤組的兼容性：DATA/FRA/DG磁盤組由“ASM實例-Grid”管理，由“數據庫實例-OracleDatabase”使用伸头。

創(chuàng)建磁盤組時OracleDatabase版本<'compatible.rdbms'<'compatible.asm'<=Grid版本匾效。兼容版本越低性能越差。

重平衡REBALANCE恤磷，數據平均分配到磁盤上面哼。

創(chuàng)建表空間

>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提供以下信息：監(jiān)聽器名搁吓、IP原茅、端口、數據庫服務名堕仔。

生成監(jiān)聽listener.ora：無GI用orcle有GI用grid帳戶擂橘。$netmgr;添加Listeners，主機IP（用靜態(tài)加快訪問速度）摩骨、端口通贞、GlobalDatabaseName填數據庫全局名、填$ORACLE_SID恼五。

注意監(jiān)聽配好后要啟動監(jiān)聽昌罩，多監(jiān)聽啟動要指定監(jiān)聽名。

生成tnsnames.ora：無GI有GI用oracle帳戶灾馒。$netmgr;添加Service Naming茎用，NetServiceName填網絡服務名，主機IP睬罗，端口號轨功，ServiceName填數據庫全局名。

注意listener.ora的參數要與已配好監(jiān)聽相同容达。

文本編緝監(jiān)聽器$vim $ORACLE_HOME/network/admin/listener.ora;$vim $ORACLE_HOME/network/admin/tnsnames.ora;有GI與無GI的區(qū)別是$ORACLE_HOME不同古涧。

文本編緝第2個監(jiān)聽器的名字不同、端口號不同花盐、去掉IPC通訊的一行（第一個監(jiān)聽器IPC保留）羡滑，其它的相同。由Process Monitor提供注冊服務算芯。

查數據庫全局名>show parameter service_names

顯示$ORACLE_HOME：echo $ORACLE_HOME

顯示$ORACLE_SID：echo $ORACLE_SID

查看監(jiān)聽是否提供服務：$lsnrctl status listener2;

通過進程間通信而不通過網絡訪問數據庫：

$sqlplus hr/hr;

$sqlplus / as sysdba;

使用監(jiān)聽訪問數據庫（簡單訪問）：

$sqlplus hr/hr@IP:1521/orcl

$sqlplus sys/oracle@IP:端口/數據庫全局服務名 as sysdba;

通過使用不同的端口使用不同的監(jiān)聽器柒昏。

使用監(jiān)聽和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注冊到默認監(jiān)聽器也祠。

用lsnrctl;services是否是dispatcher，約等5分鐘才顯示近速。dispatcher分配器诈嘿。

對于共享服務器進程堪旧，當用戶請求時，監(jiān)聽將不返回server process地址奖亚，而是返回dispatcher序列號淳梦。

以下操作不被共享服務器進程：管理、備份還原昔字、裝載爆袍、導入導出、并行作郭。如shutdown immediate;

對于共享服務器進程陨囊，SGA要增，PGA（用戶獨有）要減夹攒。

/*存儲結構-------------------------------------------------------------------------------------------------------------------------------------------------

查詢表空間及文件：

>select tablespace_name,files from dba_data_files;

創(chuàng)建表空間：

>create tablespace tbs1 datafile '+DATA' size 20M;//若文件系統(tǒng)datafile的參數'/u01/app/oracle/oradata/orcl/tbs2.dbf'

>create tablespace tbs2 datafile '+DATA' size 20M,'+FRA' size 20M;//此命令創(chuàng)建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圖形界面管理表空間：略

段區(qū)對應關系：

1M 64K

1-64M 1M

64M- 8M

區(qū)是最小的分配單位咏尝，塊是最小的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權限允懂，密碼不加引號

系統(tǒng)權限厕怜。

用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;

對象權限的撤消是級聯(lián)的，系統(tǒng)權限的撤消是不級聯(lián)的累驮。

授予級聯(lián)系統(tǒng)權限：

>grant create table to jack with admin option;

撤銷級聯(lián)系統(tǒng)權限：

>revoke create table from jack;

EM圖形界面：Database-Users-填用戶名酣倾，密碼，系統(tǒng)權限

對象權限谤专。對象權限為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;//對象權限的撤銷是級聯(lián)的

級聯(lián)？置侍？映之？

>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用戶執(zhí)行此腳本鹦牛，就可以在"ComplexityFuntion"填入函數名搞糕。

配額：

alter user jack quota 20M on users;

alter user jack quota unlimited on example;

grant unlimited tablespace to jack;

/*并發(fā)訪問-------------------------------------------------------------------------------------------------------------------------------------------------

復習：鎖機制，行鎖曼追、表鎖窍仰、事務鎖。鎖的并發(fā)性和兼容性礼殊。

事務結束釋放行鎖和表鎖驹吮。

查看某用戶的事務信息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

審計類型-默認成功失敗都記錄

審計用戶-默認所有人

審計內空-執(zhí)行語句、使用系統(tǒng)權限源织、使用對象權限翩伪。

啟用審計：

>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細粒度審計谈息，可以查看用戶發(fā)出語句的詳細信息缘屹。通過包DBS_FGA實現(xiàn)。

查文檔：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

基于值的審計，可以看出值的變化余素。通過觸發(fā)器記錄信息豹休，信息必需記錄在自定義的表中。

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需要消耗系統(tǒng)資源屏积。由優(yōu)化程序統(tǒng)計信息可以獲得較好的執(zhí)行計劃医窿。

查詢執(zhí)行計劃：

>set autotrace on;

>select sql_text,?? from v$sql;

由GATHER_DATABASE_STATUS Procedures包控制。

EM中查看統(tǒng)計信息：Server-QueryOptimizer-ManageOptimizerStatistics-GatherOptimizerStatistics炊林。

由數據字典查看表的最后收集時間的表行數姥卢，用于優(yōu)化執(zhí)行計劃。

>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包手動進行統(tǒng)計信息收集：

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;

等待事件相關的動態(tài)性能視圖：系統(tǒng)v$sysstat v$system_event 會話v$sesstat v$session_event 特定服務v$service_stats v$service_event。從asktom.orale.com網站上找腳本症歇。

內在優(yōu)化由oracle 11g AMM Automatic Memiry Management自動管理郎笆。只需指定memory_target=內存大小，由MMAN進程通過AWR快照調整內存忘晤，每10分鐘檢測一次宛蚓。

OLPT系統(tǒng)DB80%和OS20%，Oracle通過指導中心的內存指導報告內存设塔。Oracle11g將sga_target設為0凄吏、pga_aggregate_target設為0(自動)，只設置memory_target即可闰蛔。

OLAP/DSS在線分析系統(tǒng)痕钢，即用作數據倉庫時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);//建索引

執(zhí)行查詢：

>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;

/*備份恢復的概念---------------------------------------------------------------------

故障現(xiàn)象：語句失敗序六、用戶進程失敗盖喷、網絡故障、用戶錯誤难咕、實例故障课梳、介質故障。只有介質故障通過"備份和恢復"余佃。

復用文件一般3個暮刃，太多影響性能”粒控制文件椭懊、日志文件、歸檔日志可以復用步势，數據文件不復用氧猬。全數據庫、數據文件坏瘩、歸檔日志盅抚、控制文件、參數文件可以備份倔矾。

復用控制文件：

>select name from v$controlfile;//查看現(xiàn)有控制文件

>asmcmd拷貝控制文件(注意需要關閉文件進行控制文件拷貝妄均，否則容易出現(xiàn)數據庫版本不一致)

>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;//文件系統(tǒng)填路徑

>alter database add logfile member '+DG' to group 2;

>alter database add logfile member '+DG' to group 3;

切進日志直到消除invalid狀態(tài)：

>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可用于在線備份升筏。

數據庫必需先歸檔后備份，歸檔過程為：建目錄瘸爽，設置歸檔路徑（檢查設置），在數據庫掛載狀態(tài)開啟歸檔（檢查進程）铅忿，切換以使用歸檔剪决。

先刪除表空間方便實驗：

>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劲适、丟失所有日志組成員（上節(jié)課講過丟失部分日志組成員）

INACTIVE非當前（已寫回磁盤的日志，做日志恢復操作無需該組操作） ACTIVE非當前（此組日志對應的數據沒有完全被刷新回磁盤，做數據庫恢復需要該組中的日志信息） CURRENT（當前正在被使用的日志組）

先查看日志組成員：

>select group#,sequence#,status from v$log;

切換日志：

>alter system switch archivelog;

執(zhí)行檢查點景鼠，將所有日志對應的數據寫回磁盤：（全部成為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的數據庫服務器至少到開啟至實例狀態(tài)浓恶，而缺少參數文件將無法啟動至實例狀態(tài)。解決辦法是用RMAN內置實例啟動數據庫：

RMAN>start nomount;

用RMAN修復：

RMAN>restore spfile from 'u01/sp.bak';

RMAN中重啟數據庫：

RMAN>startup force;同startup abort+startup;

常見問題：若出現(xiàn)NO LOGON结笨，是由于關閉時間過長包晰，可以結束進程強制關閉數據庫。

RMAN的設置炕吸。

查看所有的配置項：

RMAN>show all;

修改參數：

RMAN>參數;

刪除備份：

RMAN>delete backup;不能在ASM中刪除否則將不能同步日志伐憾，RMAN會誤認為備份存在

備份參數有：控制文件自動備份、備份冗余赫模、優(yōu)化树肃、加密、類型瀑罗。

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備份優(yōu)化以節(jié)約磁盤（恢復時間更長）驾讲，一般不開。

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過程：建庫、建表空間烦味、建用戶聂使、授權、創(chuàng)建catlog谬俄、注冊catlog柏靶、同步catlog。

#xhost +

#su - oracle

$export $ORACLE_SID=catdb;

$dbca;不需要EM溃论、不用自動管理屎蜓、存儲用文件系統(tǒng)、字符集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;

創(chuàng)建catlog:

$rman target / catalog catu/catu@catdb

$rman target sys/oracle@orcl catalog catu/catu@catdb;//命令含義同上條

RMAN>create catalog;

注冊catlog：

RMAN>register database;（否則將出現(xiàn)target database not found錯誤）

rman target /的備份同步到catlog：

>resync catalog;

修改之前的筆記炬转，在grid帳戶下配監(jiān)聽。

數據庫的不完全恢復：主動/被動笔诵。

1、基于時間點的數據庫不完全恢復姑子。例如對DDL語句的恢復乎婿。

查看備份：>report need backup;//查看現(xiàn)有備份

$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;

破壞--

用聯(lián)合查詢查出控制文件、數據文件粮呢、日志文件：

>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中執(zhí)行

創(chuàng)建口令文件：

$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的遷移秧骑。

創(chuàng)建一個非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%

物理校驗在塊頭寫入校驗信息乎折，邏輯校驗校驗段頭塊和段之間的關系绒疗。校驗只能預防壞塊。

文檔

創(chuàng)建表空間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用來修復壞塊--

發(fā)現(xiàn)錯誤：

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再創(chuàng)建張表

>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

創(chuàng)建目錄對象：

>create directory dump as '/u01/app/oracle/dump/';//目錄由oracle帳戶創(chuàng)建笤昨，命令需要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;//如果映射到的帳戶沒有表空間匕坯，則創(chuàng)建

//****************************************************************************************************

&數據放在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數據庫也需要創(chuàng)建目錄對象和授權：

本地或網絡拷貝到目標機器的目錄對象：

在目標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緩沖區(qū)調整緩存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。表空間的行盡量放在不同塊中竟贯，目的是提高并發(fā)量答捕。

db_nK_cache_size設置小，全表掃描慢而并發(fā)訪問變塊屑那。

查看各池容量：

>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（用戶全局區(qū)）會移動到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筆記

醫(yī)院建立信息數據庫郭脂，收集健康設備的信息空繁。

OLPT系統(tǒng)調優(yōu)的首要原則就是綁定變量。

使用綁定變量避免硬解析：

var v1 number

exec :v1:=196

select salary from employees where employee_id=：v1;

PL/SQL的變量朱庆、賦值號加冒號盛泡。

讓Oracle自動替換成綁定變量：

>alter system set cursor_sharing=force;//force的引號可加可不加

查詢執(zhí)行次數、解析次數：

>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在線查詢系統(tǒng)剧罩，晚上為OLAP在線分析系統(tǒng)栓拜。

由AMF實現(xiàn)，配合spfile實現(xiàn)惠昔。早期版本的Oracle用init初始化參數幕与，缺點是參數不能被系統(tǒng)修改。

性能優(yōu)化方法：

規(guī)劃：投資镇防、系統(tǒng)（可擴展性Linux最強）...

SQL優(yōu)化（應用優(yōu)化）對應課程SQL turning 調整：SQL PL/SQL

性能優(yōu)化（實例優(yōu)化）對應課程Performance Turning 調整：內存啦鸣、I/O、網絡来氧、資源如lock

v$sys_time_model系統(tǒng)時間模型 v$sess_time_model會話時間模型诫给。用于查詢數據庫啟動、SQL解析啦扬、SQL執(zhí)行中狂、SQL編譯等時間的消耗。

生成AWR報告：

@?/rdbms/admin/awrrpt.sql

報告保存在/home/oracle/

用EM查看資源的會話消耗：

EM-Performance-TopCustomers-TopSessions

/****************************************************************************************************

扑毡？胃榕？？

SQL優(yōu)化指導 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-

優(yōu)化指導：個別語句用Automatic SQL Tuning Result優(yōu)化僚楞、使用SQL Profile直接修改執(zhí)行計劃勤晚、收集統(tǒng)計信息枉层、使用索引泉褐、修改SQL語句。后3條都是間接干涉執(zhí)行計劃鸟蜡。

第十三天

160729第十三天OCP筆記

江蘇移動外包：

限制job資源使用：

PLAN--group膜赃？？

EM-Server-OracleScheduler-Window

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跳座。塊大小優(yōu)先由表空間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;

解決行遷移：

使用段指導發(fā)現(xiàn)行遷移并解決县习。可以添加自動化任務定期解決谆趾。

手工處理行遷移－－

>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;

區(qū)的管理－－

extent management local

segment space management auto|manual

段的延遲創(chuàng)建（創(chuàng)建表時并不分配空間躁愿，插入時創(chuàng)建）：

>alter system set deferred_segment_createion=true;

Oracle在安裝時會創(chuàng)建很多永遠不會用到的空表，段延遲創(chuàng)建可以節(jié)約空間和加快安裝速度沪蓬。

段的立刻創(chuàng)建：

>create table t1 segment creation immediate as select * from t2 where 1=2;

按需創(chuàng)建表只適用于非分區(qū)表和索引攘已，不適用于IOT、聚簇表...怜跑，不適用于字典管理的表空間中的表样勃。

先創(chuàng)建索引邏輯不使用，以后使用：

>create index t1_idx on t1(id) unusable;

>alter index t1_idx rebuild;

表壓縮減少IO多耗CPU性芬，BASIC壓縮方式適用于OLAP/DSS數據倉庫峡眶，F(xiàn)OR OLTP壓縮方式適用于OLTP常規(guī)交易型數據庫。

>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;

如果只執(zhí)行第3步植锉，不執(zhí)行第2步辫樱，則在收縮段期間不能進行DML語句。

收縮段的優(yōu)點是節(jié)約空間俊庇，提高全表掃描性能狮暑。

管理可恢復的空間分配--

會話交互（會話遇到空間問題，等待問題解決而非終止）：

>alter session enable resumable;

系統(tǒng)級別可以設置會話時間辉饱，超時由系統(tǒng)選擇：

>show parameter resu

>alter system set resumable_timeout=3600;//單位：秒

目前磁盤為4K扇區(qū)搬男，Linux內核3.0以上支持4K扇區(qū)磁盤。要求db_block_size要4K~32K彭沼，默認8K缔逛。32以上需要OS支持。

查看日志的塊大小：

>select blocksize from v$logfile

建庫時要設置日志組塊大小16K褐奴，提高性能按脚。

移動表空間，對于使用不同指令集的CPU敦冬，需要用RMAN提供的convert命令辅搬。--

過程：表只讀、datadump導出metadata脖旱、轉字節(jié)碼伞辛、拷元數據及數據文件、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帳戶創(chuàng)建竿滨，命令需要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、（目標與原使用相同的字節(jié)序endian,若不同需要用RMAN轉換：

RMAN>sql 'alter tablespace hr read only convert tablespace hr to platform '操作系統(tǒng)平臺' 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';

創(chuàng)建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網站注冊號并綁定筷频，找徐老預約考試蚌成，考試時間和科目。