OCP_03

1.
$ORACLE_HOME/dbs
pfile----init.ora --文本  --more/cat/tail --asm 
spfile---spfile.ora --二进制   --strings  --file system

2.查看spfile
show parameter spfile

3.修改
a. 修改比较少
alter system set parameter_name=value scope=memory/spfile/both sid=sid1/sid2/*
scope=spfile---重启库
eg:
alter system set open_cursors=1000;
alter system set processes=1000 scope=spfile; ---重启数据库


b.批量
create pfile='/tmp/aa.ora' from spfile;
vi /tmp/aa.ora
shutdown immediate;
startup pfile='/tmp/aa.ora'              尝试运行修改过的文件
create spfile from pfile='/tmp/aa.ora'
shutdown immediate;
startup

4.备份
create pfile='/tmp/aa.ora' from spfile;

5.参数是动态还是精彩
1.uat test
2.v$parameter ---v$fixed_table

2.alert.log ---&gt2G,建议做个备份另存

-------------------------------------------------------------

shutdown abort;
kill -9 smon

非常正常关库：smon---是实例恢复

正确的停启库（每次变更时写到变更步骤里）：

a.停库---数据库变更、数据库服务器变更
1.停应用
2.lsnrctl stop
3.sqlplus / as sysdba 
alter system switch logfile;  ---3   生成归挡
alter system checkpoint;
shutdown immediate;                     
--一边关一半看alert.log
show parameter background_dump_dest
cd /u01/app/oracle/diag/rdbms/tk/tk/trace/
tail -f alert_tk.log

job
一定要和业务用户沟通变更期间是否有job需要特殊处理，比如在变更期间执行，变更后需要手工执行。


变更时：
alter system set job_queue_processes=0;
变更后：
alter system set job_queue_processes=1000;

b.启库（开启自动重启库）
手工：
lsnrctl start
--建议一步步启动
startup nomount; --启动实例、参数文件
alter database mount; --控制文件、数据文件
alter database open; --open数据文件，日志文件
-----------------------------------------------------------------------------
3.初始化：
生产库正式上线：
a.数据库参数修改
b.版本发布---创建用户(业务用户，业务查询、监控、DBA维护监控用户)、赋予权限和表空间
c.profile、审计、安全策略---规则
d.部署备份（rman物理备份，expdp逻辑备份针对部分重要的业务表或某个用户下所有对象）
e.部署监控               (all)
f.维护公司相关的文档

--------------------------------------------------------------------------------
》》》》》》》
1.alter system 动态、静态
2.spfile--pfile改
3.停启库,用正确的方法
-------------------------------------------------------------------------------

listener      (写一个脚本进行维护)
1.netca
$

2.
PL/SQL developer connect
client----&gtserver
client:
IP/db_name/port/user_name/password
ping 192.168.1.199
telnet 192.168.1.199 1521
server:
lsnrctl status/start/stop
alter system register

check:
tnsping tk  (ip/port/service_name) 
ping 192.168.1.199
lsnrctl status ---&gtcheck port/service_name(一定成功注册)

3.动态、静态 （监听的两个参数文件）

动态（ready）---不需要listener.ora，PMON（service_name,instance_name）,1521，非1521（local_listener，1522）
alter system set local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.199)(PORT = 1522))'

静态（UNKNOWN）--需要配置listener.ora
SID_LIST_LISTENER=
  (SID_LIST=
   (SID_DESC=
   (SID_NAME=tk)
   (ORACLE_HOME=/u01/app/oracle/product/11.2.0.3)
   )
)

可以通过看日志知道哪些机器连到DB了   （连接数）
/u01/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml

$ORACLE_HOME/network/admin/listener.ora --tnsnames.ora
--------------------------------------------------------------------------------
database link(db--db)

SQL> select username , account_status from dba_users where username='HR';

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
HR                             EXPIRED & LOCKED

SQL> alter user hr account unlock;

User altered.

SQL> select username , account_status from dba_users where username='HR';

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
HR                             EXPIRED


SQL> alter user hr identified by hr;

User altered.

SQL> select username , account_status from dba_users where username='HR';


USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
HR                             OPEN

1.
192.168.1.242 ---&gthr unlock
create table
create table hr.testabc as select * from hr.jobs;

2.
192.168.1.199---&gthr unlock
-- Create database link 
create public database link LINK_HR
  connect to HR
  using '192.168.1.242/tk';


select * from hr.testabc@link_hr
create synonym hr.testabc for hr.testabc@link_hr
select * from hr.testabc

select 'X' from dual@link_hr;

--------------------------------------------------------------------------------

ps -ef | grep -v grep | grep LOCAL=NO | awk '{print $2}' | xargs kill -9  （杀掉所有与数据库相连的进程，   杀掉所有会话， 全部释放）

linux 中经常用的是 ps aux 和 ps -elf 和pido

-------------------------------------------------------------------------------------

tablespace

--tablespace
--view
select * from v$tablespace
--查看example下面放了哪些表和索引？
select * from dba_data_files where tablespace_name='EXAMPLE'
select * from dba_extents where file_id=5             
select * from dba_segments where tablespace_name='EXAMPLE'


--业务系统上线，建用户和表空间 --研发
--create tablespace(table , index)
create tablespace erp_data datafile '/u01/app/oracle/oradata/tk/erp_data_01.dbf' size 10M  autoextend on;
create tablespace erp_index datafile '/u01/app/oracle/oradata/tk/erp_index_01.dbf' size 10M autoextend on;


--add user
create user erp identified by erp 
default tablespace erp_data                    //指定默认表空间
temporary tablespace temp;


--add privilege
grant create session,connect to erp;
GRANT CREATE JOB TO ERP;
GRANT CREATE PROCEDURE TO ERP;
GRANT CREATE SYNONYM TO ERP;
GRANT CREATE TABLE TO ERP;
GRANT CREATE ANY INDEX TO ERP;
GRANT CREATE TYPE TO ERP;
GRANT CREATE VIEW TO ERP;
GRANT CREATE SEQUENCE TO ERP;
GRANT CREATE MATERIALIZED VIEW TO ERP;


--offline
alter tablespace xxx offline/online/read only/read write;
--move tablespace
alter table xxx move tablespace xxx
--drop tablespace
 drop tablespace erp_data INCLUDING CONTENTS and datafiles;
--add
alter tablespace example  add datafile '/u01/app/oracle/oradata/tk/example02.dbf' size 10m;
alter database datafile '/u01/app/oracle/oradata/tk/example02.dbf' resize 20m;

--迁移数据文件
root#
mkdir -p /u02/app/oracle/oradata/tk
chown -R oracle:oinstall /u02
sql>alter tablespace example offline;
cp /u01/app/oracle/oradata/tk/example02.dbf /u02/app/oracle/oradata/tk/
sql>alter database rename file '/u01/app/oracle/oradata/tk/example02.dbf' to '/u02/app/oracle/oradata/tk/example02.dbf';
sql>alter tablespace example online;

-------------------------------------------------------
user
--add user
create user erp identified by erp 
default tablespace erp_data
temporary tablespace temp;

--add privilege
grant create session,connect to erp;
GRANT CREATE JOB TO ERP;
GRANT CREATE PROCEDURE TO ERP;
GRANT CREATE SYNONYM TO ERP;
GRANT CREATE TABLE TO ERP;
GRANT CREATE ANY INDEX TO ERP;
GRANT CREATE TYPE TO ERP;
GRANT CREATE VIEW TO ERP;
GRANT CREATE SEQUENCE TO ERP;
GRANT CREATE MATERIALIZED VIEW TO ERP;
grant unlimited tablespace to erp;

--profile --不是针对业务用户，只是针对一些个人账号，为了防止个人账号的而一些操作会影响数据库性能

--改密码
alter user xxx identified by xxx;

---lock/unlock
alter user xxx account unlock/lock;

--dorp user
drop user xxx cascade;

--kill session    (杀会话)

select s.sid, s.serial#, pid, spid
  from v$session s, v$process p
 where s.paddr = p.addr
   and s.sid = 39

kill -9 spid       （杀进程）（linux 中经常用的是 ps aux 和 ps -elf 和pido）

alter system kill session '27,87';   ('sid, serial#')

--role
create role aabb not identified ;

grant create session,connect to aabb;
GRANT CREATE JOB TO aabb;
GRANT CREATE PROCEDURE TO aabb;
GRANT CREATE SYNONYM TO aabb;
GRANT CREATE TABLE TO aabb;
GRANT CREATE ANY INDEX TO aabb;
GRANT CREATE TYPE TO aabb;
GRANT CREATE VIEW TO aabb;
GRANT CREATE SEQUENCE TO aabb;
GRANT CREATE MATERIALIZED VIEW TO aabb;
grant unlimited tablespace to aabb;

grant aabb to hr;
----------------------------------------------------------------------

$ emca -config dbcontrol db -repos recreate      安装oum 

$ emctl status dbconsole    OUM状态
----------------------------------------------------------------------

schema--table

flashback---recyclebin
1.
select * from dba_tables where owner='HR'
create table hr.jobs_bk as select * from hr.jobs;
select * from dba_recyclebin   （查看已删除的表）
drop table hr.jobs_bk;
select * from hr.jobs_bk;
flashback table hr.jobs_bk to before drop ;  （闪回删除的表）

2.
select * from dba_tables where owner='HR'
create table hr.jobs_bk as select * from hr.jobs;
select * from dba_recyclebin 
drop table hr.jobs_bk purge; ---不经过回收站
select * from hr.jobs_bk;
select * from dba_recyclebin 

alter system set  db_recycle_cache_size =100m scope=both  但是仍不能解决问题. show recyclebin  依然为空

---------------------------------------------------------------

truncate vs delete
truncate:DDL,回收空间
delete:DML，不回收空间---表碎片---时间久影响查询效率

大表
1.a.big table --&gtpartition table (分区表) b.truncate partition
2.生命周期---数据清理策略---时间字段---直接删掉/转历史（经常使用表（3个月），3个月之前的转历史用于报表）

3.收缩表(定期)    （遇到TRUNCATE或者DELETE表中的数据后发现表空间并没有将空间进行释放）

create table hr.test as select * from dba_objects    
insert into hr.test select * from hr.test; 
select * from dba_segments where segment_name='TEST' --6528 53477376
delete  from hr.test;
commit;
select * from dba_segments where segment_name='TEST' --6528 53477376


alter table hr.test enable row movement; --DDL,会造成对象失效
alter table hr.test shrink space;
select * from dba_segments where segment_name='TEST' --8 65536
alter table hr.test disable  row movement;



约束：数据库设计阶段

索引: 位图索引用的要小心（update比较频繁不要使用位图索引）

视图：建议仅查询不要修改视图，直接更新基表 

序列：cache 
-- Alter sequence            （性能问题）
alter sequence EMPLOYEES_SEQ 
cache 100;


临时表




闪回查询

select salary
  from hr.employees as of timestamp to_timestamp('2013-07-14 11:00:00','yyyy-mm-dd hh24:mi:ss')
 where employee_id = 198
 
 select salary
  from hr.employees as of timestamp sysdate -1 
 where employee_id = 198
 
 select to_timestamp('2013-07-14 11:00:00','yyyy-mm-dd hh24:mi:ss') from dual
 
 create table embak as select salary
  from hr.employees as of timestamp sysdate -1 
 where employee_id = 198

------------------------------------------------------------------------------------
并发
session 1
SQL> update employees set salary=salary+1000 where employee_id=177;
 
1 row updated
 
SQL> update employees set salary=salary+1000 where employee_id=152;
 
update employees set salary=salary+1000 where employee_id=152
 
ORA-00060: 等待资源时检测到死锁

session 2

SQL> update employees set salary=salary+1000 where employee_id=152;
 
1 row updated
 
SQL> update employees set salary=salary+1000 where employee_id=177;
SQL> 

--monitor
//查找死锁，查找出为执行commit操作的
select sid, serial#, username
    from v$session where sid in
    (select blocking_session from v$session)；

//
//查找死锁， 等待的和未执行commit操作的（insert  updata）
//    
select /*+ rule */  --RBO 
 decode(request, 0, 'Holder:', 'Waiter:') || sid, 
 id1, 
 id2, 
 lmode, 
 request, 
 type 
  from v$lock 
 where (id1, id2, type) in 
       (select id1, id2, type from v$lock where request > 0) 
 order by id1, request; 


引发数据库性能问题： big table的删除。。。。。
---------------------------------------------------------------------------

undo

data buffer ---buffer block 2个（new and old） ---undo --&gt
多版本

1.UNDO表空间
增加大小

select * from 
(select  a.tablespace_name   "表空间名称", 
a.total                "表空间总大小 ", 
a.used_block         "已分配表空间大小", 
Round(a.total-a.used_block+free_block)   "可使用表空间大小", 
to_char((a.used_block-nvl(b.free_block,0))/a.total*100,'990.99')  "表空间利用率" 
from 
(select tablespace_name, 
sum(decode(AUTOEXTENSIBLE,'YES',maxbytes,bytes))/1024/1024  total , 
sum(bytes)/1024/1024 used_block 
from dba_data_files group by tablespace_name) a, 
(select tablespace_name,sum(bytes)/1024/1024 free_block 
           from dba_free_space 
         group by tablespace_name) b 
where a.tablespace_name=b.tablespace_name(+)  
order by to_char((a.used_block-nvl(b.free_block,0))/a.total*100,'990.99') desc 
   ) ; 

alter tablespace undotbs1 add datafile '/u01/app/oracle/oradata/tk/undotbs02.dbf' size 1m;

select * from dba_data_files where tablespace_name='UNDOTBS1'

create  undo tablespace undotbs2  datafile '/u01/app/oracle/oradata/tk/undotbs03.dbf' size 10m;
select * from dba_data_files where tablespace_name='UNDOTBS2'

SQL> alter system set undo_tablespace=undotbs2;

System altered


快照过旧
ORA-01555 snapshot too old
1.最有效，研发改SQL，优化查询
2.扩大回滚段
3.undo_retention值改大点
4.游标（显式）

1.
alter system set audit_trail='db_extended' scope=spfile;
shutdown immediate;
startup;

HR:
SQL> audit update on hr.employees by access;
Audit succeeded
SQL> update hr.employees set salary=salary+2000 where employee_id=188;
 
1 row updated

select * from dba_audit_trail

*sys连接做以上操作不会被审计。

system,sys----lock


DBA审计：
audit_sys_operations ---true
audit_file_dest --adump--写脚本转存，或者删除3个月之前
-------------------------------------------------------------------------------------------------------
emctl status/start/stop  dbconsole
https://192.168.1.199:1158/em/console

emca -config dbcontrol db -repos create   / recreate


export TZ=PRC
emctl config agent getTZ
emctl config agent updateTZ
emctl start agent

https://192.168.1.199:1158/em/console/logon/logon

-统计信息

定期收集
1.表--普通表、分区表---建议分开收集
2.分区表可以定期收集当前分区
3.以表的大小确定收集的百分比

SQL执行计划不对
手工收集

begin
  dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'TEST');
end;

comment on table TEST is 'test';

*已经在执行的SQL执行计划不会改变，需要kill session后重跑

begin
  dbms_workload_repository.modify_snapshot_settings(interval  => 15,
                                                    retention => 180 * 24 * 60);
end;
/
iostat -xm 2 2000
top

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29500582/viewspace-1097115/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29500582/viewspace-1097115/