OCP笔记部分整理-学习参考

  database

2.  instance

3.  session

4.  process

5.  table

6.  index 

7.  transaction (事务)

8.  lock

9.  cursor

10.  awr(auto workload repository)

11.  service

12.  data guard

13.  rac

查询服务器主机名：

select host_name from v$instance;

查询服务器IP

select sys_context ('userenv','ip_address') from dual;

创建或配置数据库：dbca

net manager 调用：netmgr

emca创建数据库

手动创建数据库：emca -config dbcontrol db -repos create

1\database:

查看控制文件：

select * from v$controlfile;

查看数据文件：

select * from dba_data_files;

数据文件不包括临时文件

日志文件：

select * from v$log;

select * from v$logfile

切换日志文件：

alter system switch logfile;

参数文件：

(server parameter file)spfile

show parameter spfile

口令文件：password file 

select * from v$pwfile_users

重要

2\instance:

oracle 五大进程：

select * from v$process

  1.pmon( process monitor)

  2.smon(system monitor)

  3.dbwr(database write)

  4.lgwr(log write)

  5.ckpt(checkpoint)

oracle 启动的几个状态：

正常启动顺序：

启动实例：instance ,读取系统参数文件

读取控制文件

打开数据库

nomount -- mount-- open

startup  open

startup force (强制重启，模仿停电试验)

shutdown == shutdown normal

shutdown immediate

shutdown abort(停电类似)==startup force

shutdown transactional

创建表空间：

关于sys用户参数：

show parameter O7

里面有一个属性为false 如果更改为true就可以不需要as sysdba 连接。

os认证：

关于登录的"/ "，操作系统认证，省缺状态下只可以在数据服服务器生效。

1、windows屏蔽操作系统认证：去掉ora_dba组里的相对应用户

即可。

2、更改E:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\tnsnames.ora下的文件

关于服务命名与监听：

show parameter service_name

C:\>tnsping orcl 解释服务命名

更改数据文件大小：

SQL> alter database datafile 9 resize 100m;

ntfs 最大的数据文件大小为32GB

更改数据文件大小自动扩展：

SQL>alter database datafile 9 autoextend on next 5m;每次增加5m

表空间可以有多个数据文件：

SQL> alter tablespace ming_tbs add datafile 'E:\ORACLE\ORADATA\MING_TBS2.DBF' si

ze 2048m;

表空间脱机：

alter tablespace test2 offline;

alter tablespace test2 onine;

表空间只读：

alter tablespace test2 read only

更改为online

alter tablespace ming_tbs read write

删除表空间：

drop tablespace test;

删除表空间的同时删除表的内容与数据文件

drop tablespace test including contents and datafile;

表空间重命名：

alter tablespace test2 rename to test;

表重命名：

rename table1 to table_1;

清空回收站：purge recyclebin;

查看数据库用户：

select * from dba_users;

修改数据库默认表空间,表间是由数据段组成

alter database default tablespace test;

alter user test default tabledpace test;

一个用户能使用多少表空间不是通过权限控制quota

quota unlimited 

临时表空间如临时文件，虚拟内存，linux下的交换表空间

创建临时表空间：

create temporary tablespace temp1 tempfile 'e:\oracle\tem_f.dbf' size 10m

创建用户同时指定其临时表空间：

create user test identified by test default tablespace test temporary tablespace temp1 quota 10m on test;

删除表空间指定的数据文件：

alter tablespace test drop datafile 5;

用户：

创建用户时指定下次登录时需改修密码：

cteate user test identified by test5 default tablespace test temporary tablespace temp1 password expire(过期);

锁定用户：

create user test identified by test5 default tablespace test temporary tablespace temp1 account lock;

创建外部验证用户：

create user "pgm/pgm-pc" identified externally;(操作系统用户验证)用户一定要用双引号，且要大写。

权限（privilege）：

1、系统权限system

SQL>select * from system_privilege_map (系统权限表)

SQL>select * from dba_sys_privs where grantee='test'  查看一个用户拥有的系统权限（重要，常用）

SQL>grant create any table to test;授予可以在其它表空间创建表，注意此权限较大，如test用户可以创建test1用户的表。

create any table 

unlimited tablespace 

drop any table 

2、对象权限 object

SQL>select * from dba_tab_privs where grantee ='test' ；查看用户拥有的对象（表）权限 （重要，常用）

授予用户对表t的相关权限：

SQL>grant select ,insert,updata,delete on t to test;

SQL>grant all on test1 to test  ;把关于test1所有的对象权限授予给test用户。

SQL>grant all on test1 to test grant option (实现权限传递)

收回权限revoke：

SQL>revoke all on test1 from test 

oracle 默认是不能权限传递

角色（role）：

SQL>select * from dba_roles;

SQL>create role role_test;创建角色

SQL>grant create table,create session,create view to role_test;授予角色上述权限

查看某个角色拥有哪些权限：

SQL>select * from dba_role_privis where grantee='orle_test'

授予用户某个角色：

SQL>grant role_test to test; 

控制用户资源使用：

profiles 默认：用户密码输错10次被锁定

创建用户时指定默认profiles

SQL>create user test identified by test profile default; 

修改用户profile

SQL>alter user test3 profile profile_test;

指定profiles 时需要注意设置以下参数为true时才可生效：

show parameter limit

SQL>alter system set resource_limit=true;

例外：set time on 

set time off

关于SCHEMA.

SQL>grant SELECT_GATALOG_ROLE to test1;

授予查看视图等 权限

查看某个用户所拥有的表：

select * from dba_tables where owner = 'MING'

修改表的列的属性：

SQL>alter table tt modify emp varchar2(10);

增加列：

SQL>alter table tt add d int;

增加表约束：

alter table emp add constraint pk1 primary key (id);

主键约束与唯一约束区别：一个可以为空，一个不可以为空

创建check约束(男代表1，女代表0)：

SQL>alter table emp add constraint chk_t check (sex in ('0','1'));

创建check约束，sal在1500与100000之间数值。

SQL> alter table tt add constraint chk_tt check (sal >= 1500 and sal <=100000);

添加非空约束：

alter table tt add constraint not_null check (id is not null);

not null 为列级约束，

主键，外键，唯一，检查约束都为表级约束。

查看某个用户的约束;

select * from dba_constraints where owner = 'MING'

视图（view）：

查看某个用户下拥有的视图：

select * from dba_views where owner = 'MING'

复杂视图与简单视图（可在视图里插入数据操作）

创建一个复杂视图：

create view or replace v$empd as 

select t1.empno,t1.ename,t2.dname,t1.job,t1.hiredate,t1.sal from emp t1 join dept t2 on (t1.deptno=t2.deptno)

创建视图同时指定为只读：

create view v$v1 as 

select * from emp with read only

索引（index）

1、b-tree (balance)平衡树

2、bitmap

查看某用户下的索引：

select * from dba_indexes where owner = 'SCOTT'

研究oracle时用的更改 session命令：

SQL>alter session set events 'immediate trace name treedump level 12190(对象编号)'

drop table tt purge;删除一个表不放到回收站里

plsql:

glogin.sql 初始化文件添加以下：

set serveroutput on 

procedure

第四天 录像16\17先跳过，找一些关于PL/SQL相关资料再补一下， 42分

关于帐户安全性：

授予表为公共表：

 grant all on emp to public;

撤销操作：

revoke all on emp from public;

revoke execute on utl_file from public; 收回执行权限

关于审计：

show parameter audit  查看上述参数，windows与linux不同

启用审计，修改下述参数：

alter system set audit_trail=db_extended scope=spfile;

1、权限审计

创建审计：

audit  create session 所有用户都审计

audit create session by ming  只审计ming用户

删除审计：

noaudit create session

查看审计权限视图：

select * from dba_priv_audit_opts; 

审计日志视图：

select * from dba_audit_trail

2、对象审计：

audit select on ming.emp;

audit select on ming.emp by session;默认  针对同一个session

audit select on ming.emp by access whenever successfull

3、语句审计 

audit table by ming审计用户ming关于表的操作

查看表的审计日志：

select * from dba_obj_audit_opts; 

4、oracle提供的审计包：

audit all  by emp;

查看日志：

select * from dba_stmt_audit_opts;

audit all on default; 审计所有，只对创建其之后的对象生效

select * from all_def_audit_opts;

5、例外：

创建一个审计策略：

execute

dbms_fga.add_policy(

object_schema=>'ming',

object_name=>'emp',

policy_name=>'audit_emp',

audit_condition=>'empno=7900',

audit_column=>'ename',

handler_schema=>'secure',

enable=>true,

statement_types=>'select'

);

关于服务命名：

配置时主要看lsnrctl里面的状态：ready 的肯定是能连接，unknow未必能连接。

查看数据库是共享还是专用模式：

show parameter shared

如果shared_servers为大于0说明支持共享模式

数据库默认连接类型选择共享模式，如果共享与专用共存时。

研究进程时用的查看sid

selelct sid from v$mystat where rownum=1;

select server from v$session where sid ='??';

关于无效对象处理：

查看无效对象：

select * from dba_objects where status='INVALID'

HWM:(high water mark) 高水位

分析sql语句执行：

SQL>set autotrace traceonly

SQL>analyze table t1 compute statistics ;分析表

修改索引：

alter index idx_t rebuild;重建索引

alter index idx_t rebuild online; 在线重建索引，不影响insert updata delete 执行。

sql>exec dbms_stats.gather_table_stats('sys','T1');

关于上述包dbms_stats 收集统计信息

undo表空间

创建undo表空间

create undo tablespace undo002 datafile ' e:\oracle\datafile\undo002.dbf' size 100m autoextend off next 20m;

查看实例使用哪一个undo表空间：

SQL>show parameter undo

查看undo段：

select * from dba_rollback_segs;

select * from v$undostat

每十分钟纪录一个

select * from v$sql;

block 阻塞

SQL> show parameter db_block

关于锁：lock

select * from v$lock

select * from v$locked_object  锁的对象

TX:ROW 模式为：6 排他的 行级锁

TM:TABLE  3 表级锁共5种：RS、 RX、 S、 SRX、 X 

SQL>lock table t in exclusive mode ; x锁模式，死锁

注：请不要手动随便加锁，数据库会自动所需加锁

deadlock:

指的是应用程序里的死循环等

收集统计信息：

select * from dba_scheduler_jobs   每晚十点系统的一个GATHER_STATS_JOB 任务

select * from dba_scheduler_window_groups  

查看等待session ：

select * from V$session_Wait

select * from v$session_wait_history order by wait_time desc

关于监控：：

ash：active session history  记录session纪录，每秒纪录一次，插入一次纪录。em里面的ash报告

select * from v$active_session_history;

命令下创建ash报告：

SQL> @E:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\ashrpt.sql

awr：auto workload repository

awr的表属于sys用户下，存储在SYSAUX表空间下面

每一小时收集一次信息，保留7天，以下表可以查看：

select * from dba_hist_wr_control

修改其设置(但一般不建议修改)：

SQL>exec dbms_workload_repository.modify_snapshot_settings(retention=>1440,interval=>30);

命令下创建awr报告：

SQL> @E:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\awrrpt.sq

addm: auto database dia

数据自动诊断建议

命令下创建addm报告：

SQL> @E:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\addmrpt.sq

查看表的大小：

SQL>select bytes/1024/1024 m from dba_segments where segment_name='T1';

创建表在指定的表空间中：

SQL>create table t1 tablespace test as select * from dba_objects;

SQL>insert into t1 select * from t1;

关于度量metric：

select * from dba_outstanding_alerts

查看度量警告

启用管理顾问参数：

SQL> show parameter statistics

statistics_level  string TYPICAL  当为typical时启用

查看下述建议视图：

select * from v$statistics_level

关于备份：

1、完全脱机备份

2、部分脱机备份(只针对表空间操作)

3、联机部分备份 （可以备份system表空间）

4、rman备份（联机完全备份）

5、逻辑备份（exp/imp expdp/impdp）

sql>show parameter db_reco;(db_recover)

查看归档备份日志的参数路径信息，包括其大小：默认为2g

db_recovery_file_dest_size  关于经常出现归档日志超过2G大小问题

scn : system change number  

select dbms_flashback.get_system_change_number from dual;

select file#,checkpoint_change# from v$datafile;

select file#,checkpoint_change# from v$datafile_header;

两个作对比查看检查点是否一致，如不是文件需恢复。

更改某个表空间为热备份：

alter tablespace users begin backup; (更改后可以联机拷文件)

alter database begin backup; 更改所有表空间为热备

查看：

select * from  v$backup; 主要纪录起始检查点

拷文件完后：

alter tablespace users end backup;

注：上述联机热备产生的日志比其它备份模式较大

如果出现控制文件需恢复，把旧的控制文件拷来使用需添加以下命令：

sql>recover database using backup controlfile;(申明控制文件不是最新 的)

sql>recover database using backup controlfile until cancel;

sql>alter database open resetlogs; 把 scn折断，当scn有损失时使用，即当联机日志存在时使用。否则用noresetlog

关于日志文件的备份恢复

添加一组日志文件：

SQL> alter database add logfile 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO04.LOG' size 50m;

（默认）最多只可以有16组日志文件,最少需要2组。

SQL> alter database drop logfile group 4; 一个group 可能会有多个member；

当日志文件损坏后，可以新建一组后使用以上命令删除原来的

给日志文件加一个镜像：

SQL> alter database add logfile member 'E:\oracle\product\10.2.0\oradata\orcl\log\redo03.log' to group 3;

当前联机的不能删除

删除其中一个member：

SQL> alter database drop logfile member 'E:\oracle\product\10.2.0\oradata\orcl\redo01.log' ;

关于RMAN备份：

RMAN>backup archivelog all;   备份归档日志

检查归档日志：

rman>crosscheck archivelog all;

删除过期没用的归档日志：

rman>delete expired archivelog all;

RMAN>backup as compressed backupset full database ;

完全备份，压缩备份集

RMAN>restore database from TAG=TAG201099342432;

使用指定备份集来恢复

SQL>grant connect,resource,recovery_catalog_owner to rman;

授予上述角色给新建用户RMAN

RMAN>delete noprompt backup;

删除备份,磁盘上的文件同时删除。

关于增量备份：

RMAN>backup as compressed backupset incremental level=0 database;

0级备份（完全备份）

RMAN>backup as compressed backupset incremental level=2 database;

2级备份，差异备份

RMAN>backup as compressed backupset incremental level=1 database;

1级备份，再把前面的2级备份覆盖备份一次，同级别的备份不覆盖

RMAN>list backup of database summary;

显示详细的备份信息

累积备份，关键字：cumulative，同一级别备份将覆盖：

RMAN>backup as compressed backupset incremental level=1  cumulative database;

创建脚本：

rman>create script sc1{

delete noprompt backup;

backup as compressed backupset full database;

backup archivelog all;

}

查看脚本，本地脚本：

rman>list script name

执行脚本：

rman不支持直接执行脚本，需放在run里面：

新建一个sql，把以下内容输入：

run｛execute script sc1｝;

当出现有数据块损坏时：

rman>backup validate database ;

检验备份，不生成备份文件,与下述命令一样作用：

c:\>dbv file ='e:\oracle\oradata\test.dbf'

sql>select * from v$database_block_corruption

检验完成后，查看有那一些坏块

块恢复操作(只有rman才可以)：

rman>blockrecover datafile 5 block 275;

恢复刚才查询到那一个数据文件的那一个块

关于flashback database闪回（不完全恢复）

1.检查是否开启flashback:

sql>select flashback_on from v$database;

2.如没有开启需在mount状态下执行：

sql>alter database flashback on;

闪回表需在mount 状态下执行：

SQL> flashback database to timestamp to_timestamp('2012/06/18 22:04:58','yy/mm/dd hh24:mi:ss');

闪回完成后需使用resetlog 打开数据库

关于flashback  drop;主要针对表删除后闪回

flashback drop 不能闪回system表空间里面的对象

sql>flashback table t to before drop;

sql>show parameter recyclebin ;默认上述参数开启才可以支持闪回表

查看回收站：

sql>select * from dba_recyclebin;

sql>flashback table t to before drop rename to t1;

如果遇到重名时使用rename

sql>purge table t ； 清除回收站里的表t

sql>purge tablespace users ；清除回收站里的user表空间

sql>purge recyclebin;清空回收站

闪回到某一个scn：

sql>select * from t as of scn 84391414;

sql>flashback table t to scn 34242342;

注：sys用户不支持闪回。

例外：

新建一个数据文件：

SQL> alter database create datafile 'C:\oracle\product\10.2.0\oradata\orcl\tbs_test.dbf';

第八天录像1：

http://tomszrp.itpub.net/post/11835/487501

关于asm

1、asmtool管理工具

2、创建asm储存：

C:\>asmtool -create E:\asm\disk1 1024

增加以下服务css：

C:\>e:

E:\>cd E:\oracle\product\10.2.0\db_1\BIN

E:\oracle\product\10.2.0\db_1\BIN>localconfig.bat add

第八天录像3-5 跳过

使用oracle 全球支持特性：

select * from v$nls_valid_values 

查看支持语言视图

windows注册表里：HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_ORADB10GDB\NLS_LANG:

语言，版块，字符集，排序

LANGUAGE_TERRITORY_CHARACTERSET  SORT 

alter session set nls_sort ='对应的sort值'

alter session set nls_language='对应的language值'

alter ssession set nls_date_format='yyyy/mm/dd hh:mi:ss';  更改日期格式

注：上述只对当前窗口生效

修改日期格式：

alter system set nls_format='yyyy/mm/dd hh24:mi:ss' scope=spfile;

优先级最低，默认以版块来作日期格式，再到上述参数，如果注册表添加以下，则以下述格式优先

在注册表编辑

添加以下键值：nls_date_format     yyyy-mm-dd hh24:mi:ss

在监听里增加安装机制：

change_password :

启用上述密码后，oracle10g是不起作用的，默认可以通过os认证，

要去除os认证，那么需要在listener.ora文件中添加：

LOCAL_OS_AUTHENTICATION_[listener name] = OFF

重启监听后生效

配置远程监听：

在tnsnames.ora增加以下：

LISTENER_R =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.24)(PORT = 1521))

再设置以下相关参数，主要使用在rac环境中的负载均衡：

sql>show parameter remote;

sql>alter system set remote_listener=listener_r;需指定相关参数

停止远程数据库监听(在上述配置的数据操作)：

LSNRCTL> stop listener_r

关于em修复：

c:\>emctl status dbconsole

c:\>emctl start dbconsole

em重建：emca -config dbcontrol db -repos recreate

第九天 录像6  ：

关于使用em建立自动作业：

打开em---管理----数据库调度程序----作业

通过上述创建相关作业实现自动运行；

注意：windows里：OracleJobSchedulerORCL 服务必须启动

第十天 录像 3

关于exp/imp 使用：

注：sys用户下不支持导出！！

如以sysdba用户导出需加单引号，如：

C:\>exp 'sys/tiger@orcl as sysdba' file =c:\temp\a.dmp log =c:\temp\a.log owner=scott

指定scn导出

C:\>exp 'sys/tiger@orcl as sysdba' file =c:\temp\a.dmp log =c:\temp\a.log owner=scott flashback_scn=984312984

当遇到磁盘空间问题时挂起操作处理，不作中断处理

RESUMABLE            遇到与空间相关的错误时挂起 (N)

C:\>exp system/tiger@orcl  file =c:\temp\a.dmp log =c:\temp\a.log owner=scott resumable=y

ora-04031错误，常见错误，为sga错误为主！

例外：非归档模式下当数据文件损坏，没有作任何备份，尝试以下方式打开数据库：offline drop 表示永久删除，而归档模式下可以使用offline

sql>alter database datafile 5 offline drop;

传输表空间的使用：

C:\>exp 'sys/tiger@orcl as sysdba' file =c:\temp\a.dmp log =c:\temp\a.log tablespaces=test transport_tablespace=y

导入时反转过来 ，但需指定相关数据文件。

http://jzhil2004.blog.163.com/blog/static/27558504201010553843243/?fromdm&fromSearch&isFromSearchEngine=yes

2012年7月  记

本文转自pimg200551CTO博客，原文链接：http://blog.51cto.com/pimg2005/1182724 ，如需转载请自行联系原作者