ITPub博客

首页 > 数据库 > Oracle > OCP_03

OCP_03

原创 Oracle 作者:Michael_DD 时间:2014-02-28 14:59:58 0 删除 编辑
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 --->2G,建议做个备份另存

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

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---->server
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 --->check 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 --->hr unlock
create table
create table hr.testabc as select * from hr.jobs;

2.
192.168.1.199--->hr 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 -->partition 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 -->
多版本

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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: OCP_02
下一篇: OCP_04 expdp impdp
请登录后发表评论 登录
全部评论

注册时间:2014-02-22

  • 博文量
    326
  • 访问量
    2468463