Oracle总复盘（2）—— 基础管理

一、理解体系结构和实例

二、数据库的启动与关闭

2.1数据库启动的四阶段

shutdown：关闭状态，执行start nomount进入nomount状态。
no mount：实例启动，读取参数文件，分配内存和进程。执行startup mount进入下一阶段。
mount：加载数据库阶段，读取控制文件，此阶段可以select查询到数据库的物理阶段。但不能查询表的内容；执行alter database open进入下一阶段。
open：数据库打开完成们可以访问表内容

2.2 startup方式

startup mount
startup open
startup restrict （停业务维护使用）：以受限模式启动数据库，此时只有restricted session权限的用户才能访问数据库。用于维护动作。维护完成后，禁用restricted session权限，以便普通用户的连接；

startup restrict;
conn scott/tiger;
------------------------
ERROR:
ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege

4. startup pfile=xxx （以特定的静态参数文件启动数据库实例）

create pfile='/tmp/pfileorcl.ora' from spfile;
startup pfile='/tmp/pfileorcl.ora';

2.3 shutdown的四种方式

normal：不允许新连接，等用户主动断开
transactional：不允许新连接，等用户事务结束，系统断开
immediate：不允许新连接，不等事务+回滚，系统断开
abort：暴力断开。前三种正常关闭都会校验检查点，abort不校验直接断，检查点不一致会导致崩溃，需要手工恢复。

三、数据表空间管理

一个数据库可以包含多个表空间，一个表空间可以包含多个数据文件，一个数据文件只能属于一个表空间；

3.1创建表空间（增）

create tablespace testtbs datafile '/u01/app/orcl/xxx/test1.dbf' 
size 100M 
autoextend on
next 5M
maxsize 500M;   -- 无限制UNLIMITED

3.2删除表空间（删）

drop tablespace TBS1 including contents and datafiles;

3.3修改表空间（改）

　　1. 扩展表空间

alter database datafile '.../test01.dbf' resize 500m;

　　2. 增加数据文件

alter tablespace testtbs add datafile '.../test02.dbf' size 10M;

3.4查询表空间（查）

　　1. 查询有哪些表空间

select * from v$tablespace;

　　2. 表空间详细信息查询

select a.* , (a.表空间大小-b.剩余空间) "已用空间(MB)" ,
b.剩余空间 as "剩余空间(MB)",
CONCAT(round(nvl((a.表空间大小-b.剩余空间),0)/a.表空间大小,2)*100,'%') "已用比例(%)",
CONCAT(round(nvl(b.剩余空间,0)/a.表空间大小,2)*100,'%') "剩余比例(%)"
from
(select tablespace_name 表空间名称 ,file_name 物理文件路径, round(bytes/(1024*1024),0) "表空间大小"
from dba_data_files ) a
left join
(select tablespace_name,round(sum(bytes) / 1024 / 1024, 2) AS 剩余空间
from dba_free_space group by tablespace_name) b
on a.表空间名称=b.tablespace_name order by "已用比例(%)" desc;

四、临时表空间管理

4.1 概念

临时表空间的作用：数据缓存与排序

用来存放用户的临时数据，临时数据就是在需要时被覆盖，关闭数据库后自动删除，其中不能存放永久性数据。例如当用户对很多数据行进行排序时，排序在PGA中进行。但是如果排序的数据过多，导致内存不足时， oracle会把要排序的数据分成多份，每次只取一份放在PGA中进行排序，其他的部分都放到临时表空间中，当PGA里的部分排序完成后，把排序好的部分交换到临时表空间中，同时再从临时表空间里取一份没有排序的数据到PGA中进行排序，这样直到所有数据排序完成为止。

4.2 创建临时表空间（增）

create temporary tablespace TEMP2 
tempfile '/u01/app/oracle/oradata/orcl/temp02.dbf' 
size 20M autoextend on;

4.3 删除临时表空间（删）

drop tablespace TEMP2 including contents and datafiles;

4.4 修改临时表空间（改）

1. 修改临时表空间的数据文件大小

alter database tempfile '.../oradata/temp02.dbf' resize 50M;

2. 增加文件

alter tablespace TEMP2 add tempfile '.../oradata/temp02a.dbf' size 2G autoextend on;

3. 修改默认的临时表空间

alter database default temporary tablespace TEMP2;

4.5 查询临时表空间信息（查）

查看当前使用的临时表空间

select * from database_properties 
where property_name='DEFAULT_TEMP_TABLESPACE';

　　2. 查询临时表空间的详细信息

select 
t.tablespace_name as 表空间名 ,t.file_name 物理文件,
t.autoextensible  as 自动扩展,
t.bytes/1024/1024 表空间初始大小,
t.maxbytes/1024/1024 表空间最大扩展大小,
b.contents 表空间类型,
b.extent_management 表空间管理方式
from dba_temp_files t,dba_tablespaces b
where t.tablespace_name=b.tablespace_name;

五、UNDO表空间管理

5.1 概念

　　1.UNDO表空间的作用：实现一致性读、事务回滚、实例恢复

　　2.UNDO的两个重要参数：

- 　　undo retention：设置undo块的过期时间
- 　　retention garentee：当undo数据文件不能自动扩展，并且undo块不够用时，直接报错，而不是覆盖那些inactive而又没有expired的undo块。

5.2 增

create undo tablespace undotbs1 
datafile '.../oradata/orcl/undotbs01a.dbf' 
size 10M autoextend on;

5.3 删

drop tablespace undotbs1 including contents and datafiles;

5.4 改

1. 增加文件
alter tablespace undotbs1 add datafile '.../oradata/orcl/undotbs01b.dbf'

2. 扩展文件大小
alter database datafile '/u01/app/oracle/oradata/orcl/undotbs2b' resize 20M;

3. 修改默认的undo表空间
alter system set undo_tablespace=undotbs2;

4. 修改undo块的过期时间undo retention
alter system set undo_retention 1800;

5. 启用或禁用rention guarantee
alter tablespace undotbs2 retention [no]guarantee;

5.5 查

　　1. 查看当前使用的undo的信息（undo的管理方式、undo保留时间、当前使用的undo表空间）

show parameter undo;

　　2. 查看undo表空间的使用情况

select to_char(begin_time,'yyyymmdd hh24:mi:ss'),to_char(end_time,'yyyymmdd hh24:mi:ss'),undoblks,txncount 
from v$undostat;

　　3. 查看是否开启retention garentee;

 select tablespace_name，retention from dba_tablespaces where contents='UNDO';

六、数据并发与一致性

Q：Oracle的默认级别是读已提交，如何修改隔离级别？

七、归档和日志文件管理

7.1概念

日志分为：在线日志、归档日志
日志组的概念：一个日志组中可以包含多个redo-log文件，同组中的redo-log文件内容互为镜像。
在线日志文件组有四种常见状态 (从v$log中查看)

- CURRENT：表示这是当前正在使用的联机日志文件组
- ACTIVE：表示这个日志文件组中，所记录的重做记录所对应的内存中的脏数据块还没有被完全写入到数据文件中。
- INACTIVE：表示这个日志文件组中，所记录的重做记录所对应的内存中的脏数据块已经被写入到数据文件中。
- UNUSED：表示还没有被使用过。

7.2 归档日志管理

查看归档概要信息（是否开启归档）

archive log list; (select log_mode from v$database;)

　　2. 归档模式切换

mount状态下
alter database archivelog | noarchivelog;
alter database open;

　　3. 自定义存档路径

show parameter log_archive_dest;    -- 可以设置多条归档路径
alter system set log_archive_dest_8='location=/u01/app/oracle/archive/arch8';

show parameter log_archive_dest_state;    　　　　　　-- 查看归档路径的状态
alter system set log_archive_dest_state_8='defer';     -- 禁用某条归档路径

7.3 在线日志文件组管理

　　1. 查看组

select * from v$log;

　　2. 查看组中的日志成员

select * from v$logfile;

　　3. 向日志组中增加成员

alter database add logfile member '/u01/app/oracle/oradata/orcl/redo01a.log' to group 1;

　　4. 增加日志组

 alter database add logfile group 4 '/u01/app/oracle/oradata/orcl/redo04.log' size 100M;

八、备份和恢复

8.1冷备与恢复（非归档模式下拷贝文件）

　　1. 备份

1. 查询数据文件、控制文件、参数文件的路径：
   select file_name from dba_data_files;
   select name from v$control;
   select member from v$logfile;
   
2. shutdowm immediate;
3. cp xxx/xxx /data/orcl_bak
4. startup;

　　2. 恢复

执行覆盖替换原文件即可。

8.2 热备（用户管理备份begin/end backup）

1. 备份

-- 1.备份之前进行一次日志切换，将当下时间点之前的日志转储到归档日志中。
alter system switch logfile;

-- 2.获取表空间物理文件路径。
select tablespace_name,file_name from dba_data_files;

-- 3.以表空间为基本单位依次备份数据文件：先将表空间设置为backup状态，拷贝，最后还原；
alter tablespace system begin backup;
host cp  /u01/app/oracle/oradata/orcl/system01.dbf /data/hot_bak/
alter tablespace system end backup;

alter tablespace users begin backup;
host cp  /u01/app/oracle/oradata/orcl/users01.dbf /data/hot_bak/
alter tablespace users end backup;

alter tablespace testtbs begin backup;
host cp /u01/app/oracle/oradata/orcl/test01.dbf /data/hot_bak/
alter tablespace testtbs end backup;
...

-- 4.备份控制文件
alter database backup controlfile to '/data/hot_bak/control.bak';

-- 5.归档当前的日志
alter system archive log current;

2. 完全恢复整个数据库

shutdown immediate;
将所有的备份数据覆盖原文件;
startup mount;
recover database;       -- 应用redolog
alter database open;

3. 完全恢复某个表空间

1. 启动数据库到mount状态，将原来的数据文件脱机
startup mount;
alter database datafile '.../test.dbf' offline;

2. 启动数据库到open状态，将表空间脱机
startup open;
alter tablespace testtbs offline for recover;

3. 复制覆盖数据文件，并执行恢复命令
recover tablespace testtbs;

4. 表空间联机
alter tablespace testtbs online;

4. 完全恢复某个数据文件

1. 启动数据库到mount状态，将原来的数据文件脱机
startup mount;
alter database datafile '.../test.dbf' offline;
2. startup open;
4. 复制覆盖数据文件
5. recover datafile '.../test.dbf';
6. alter database datafile '.../test.dbf' online;

5. 不完全恢复数据文件

1. shutdown immediate
2. 用备份文件覆盖现损坏的文件
3. startup mount
4. 执行数据文件的不完全恢复命令
   (可通过查询v$log_history以获得时间和scn信息)
   recover database until time time:(基于时间恢复)
   recover database until cancel:(基于撤销恢复)
   recover database change scn:(基于SCN恢复)
5. alter database open resetlogs;

九、移动数据（数据泵）

9.1exdp常用参数

9.2 expdp练习

　　1. 创建目录对象，并赋予用户权限

create directory MY_DIR as '/u01/app/oracle/backupfile';
grant read,write on directory MY_DIR to scott;

　　2. 导出scott的student和address表

expdp scott/tiger@orcl directory=MY_DIR dumpfile=expdp_scott.dmp tables="(stu,address)";

　　3. 导出scott的student和address的表结构，不导出数据

expdp scott/tiger@orcl directory=MY_DIR dumpfile=expdp_scott.dmp tables="(stu,address)" content=metadata_only;

　　4. 导出数据，但不导出表结构

expdp scott/tiger@orcl directory=MY_DIR dumpfile=expdp_scott.dmp tables="(stu,address)" content=data_only;

　　5. 导出scott和loto用户的所有内容

expdp system/123456@orcl directory=MY_DIR dumpfile=expdp_system.dmp schemas="(scott,loto)";

　　6. 导出stu表，以及表中的约束，但不导出索引；（exclude用于排除指定的类型，如不指明exclude，则会导出stu中的所有对象）

expdp scott/tiger@orcl directory=MY_DIR dumpfile=expdp_scott.dmp tables=stu exclude=index;

　　7. scott下，导出其他所有表，但不导出stu和address表

expdp scott/tiger@orcl directory=MY_DIR dumpfile=expdp_scott.dmp 
exclude=table:"in('STU')" 
exclude=table:"in('ADDRESS')";

　　8. 导出student表的sno>1的记录，和address表的sno>2的记录

1. 编辑参数文件expdp1.txt
directory=MY_DIR 
dumpfile=expdp_scott
tables=stu,address
query=stu:"where sno>1",address:"where sno=2";

2. 执行expdp命令：expdp scott/tiger@orcl parfile=expdp1.txt

9.3 impdp常用参数

content：指定要加载的数据，其中有效关键字值为：(ALL) ，DATA ONLY和METADATA ONLY
estimate：估算所占用磁盘空间分方法.默认值是BLOCKS
remap_schema：用于将对象从一个用户下导入到另一个用户下。
remap_tablespace：用于将对象从一个表空间下导入到另一个表空间下。
remap datafile：用于在不同文件系统的平台间，切换数据文件路径。

9.4 impdp练习

　　1. 将从scott导出的文件中的student表导入给loto；（ 需要提前给loto用户赋予MY_DIR的读写权限）

impdp loto/123456@orcl directory=MY_DIR dumpfile=expdp_scott.dmp tables=student 
remap_schema=scott:loto;

　　2. scott的导出文件默认是属于USERS表空间的，当导入到loto用户下也默认是USERS表空间，可以使用remap_tablespaces参数将其导入到指定表空间下。

impdp loto/123456@orcl directory=MY_DIR dumpfile=expdp_scott1.dmp 
remap_schema=scott:loto 
remap_tablespace=users:tbs1;

十、SQL调优

1. 原则

层面	细分
（1）不要让Oracle执行太多操作	避免复杂的多表连接
	避免使用select *
	用EXISTS替换distinct
	用union all替换union
（2）给优化器更明确的指令	至少包含组合索引的第一列
	避免在索引列上使用函数（函数索引列除外）
	避免在索引列上使用NOT，ORACLE遇到NOT就会停止使用索引转而执行全表扫描.
	避免使用通配符
	避免在索引列上使用is null、is not null
（3）注意细节上的影响	尽量多的commit
	用truncate替换delete
	使用大写字母代替小写字母

2.执行计划分析

　　方法一：利用autotrace追踪SQL语句

SQL> set autotrace on;
SQL> select * from scott.stu a,scott.sc b where a.sno=b.sno;

SNO     SNAME          SAGE SSEX  SNO      CNO       SCORE
---------- -------------------- ---------- ----- ---------- ---------- ----------
s001       张三                         23 男    s001       c001             78.9
s001       张三                         23 男    s001       c003               59
s001       张三                         23 男    s001       c002             82.9
s002       李四                         23 男    s002       c002             72.9
s002       李四                         23 男    s002       c001             80.9
s003       吴鹏                         25 男    s003       c001             81.9
s003       吴鹏                         25 男    s003       c002             81.9
s004       琴沁                         20 女    s004       c001             60.9

13 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3694024748

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows | Bytes| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |   13 |  429 |    6  (17) | 00:00:01 |
|   1 | MERGE JOIN                  |              |   13 |  429 |    6  (17) | 00:00:01 |
|   2 | TABLE ACCESS BY INDEX ROWID | STU          |   10 |  190 |    2   (0) | 00:00:01 |
|   3 | INDEX FULL SCAN             | SYS_C0015277 |   10 |      |    1   (0) | 00:00:01 |
|*  4 | SORT JOIN                   |              |   13 |  182 |    4  (25) | 00:00:01 |
|   5 | TABLE ACCESS FULL           | SC           |   13 |  182 |    3   (0) | 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("A"."SNO"="B"."SNO")
       filter("A"."SNO"="B"."SNO")


Statistics
----------------------------------------------------------
    0  recursive calls                             -- 产生的递归sql调用的条数。
    0  db block gets                               -- 从buffer cache中读取的block的数量
   10  consistent gets
    0  physical reads                              -- 从磁盘读取的block的数量 
    0  redo size
       1389  bytes sent via SQL*Net to client      --数据库服务器通过SQL*Net向查询客户端发送的查询结果字节数
  520  bytes received via SQL*Net from client      --通过SQL*Net接受的来自客户端的数据字节数
    2  SQL*Net roundtrips to/from client
    1  sorts (memory)                              ---在内存执行的排序量   
    0  sorts (disk)
   13  rows processed                              --处理的数据行数

　　方法二：使用explain plan for ...，结果存放在plan_table表中

explain plan for
select * from scott.stu where sno<30;

select * from plan_table;   -- 使用explain plan分析结果默认存放在plan_table中；

ORACLE笔记 Oracle

Oracle总复盘（2）—— 基础管理

一、理解体系结构和实例

二、数据库的启动与关闭

2.1数据库启动的四阶段

2.2 startup方式

2.3 shutdown的四种方式

三、数据表空间管理

3.1创建表空间（增）

3.2删除表空间（删）

3.3修改表空间（改）

3.4查询表空间（查）

四、临时表空间管理

4.1 概念

4.2 创建临时表空间（增）

4.3 删除临时表空间（删）

4.4 修改临时表空间（改）

4.5 查询临时表空间信息（查）

五、UNDO表空间管理

5.1 概念

5.2 增

5.3 删

5.4 改

5.5 查

六、数据并发与一致性

七、归档和日志文件管理

7.1概念

7.2 归档日志管理

7.3 在线日志文件组管理

八、备份和恢复

8.1冷备与恢复（非归档模式下拷贝文件）

1. 备份

2. 恢复

8.2 热备（用户管理备份begin/end backup）

1. 备份

2. 完全恢复整个数据库

3. 完全恢复某个表空间

4. 完全恢复某个数据文件

5. 不完全恢复数据文件

九、移动数据（数据泵）

9.1exdp常用参数

9.2 expdp练习

9.3 impdp常用参数

9.4 impdp练习

十、SQL调优

1. 原则

2.执行计划分析

相关

　　1. 备份

　　2. 恢复