未分类 - 5. page

在ORACLE 10G/11G版本时，如果要将数据库的数据文件，移动到不同的磁盘目录（即修改磁盘路径），如果数据库在OPEN状态需要将数据文件OFFLINE，或者在数据库MOUNT状态下进行修补。

在12C及以上版本时，可以在线移动一个正在被访问的数据文件；即是system表空间中的数据文件也可以。
可以在线移动数据文件，表示当用户正在访问系统的时候，很多维护操作可以在线执行；例如，将数据文件移动到其他存储设备或者移动到Oracle ASM。这确保了服务的连续性，并且满足正常运行时的服务水平协议（SLA）。

在 12C，当数据文件处于在线状态并且正在被访问的时候，可以执行下面的操作：

1. 重命名在线数据库文件
2. 迁移在线数据库文件
3. 拷贝在线数据文件
4. 迁移在线数据文件并且覆盖现有文件
5. 迁移在线数据文件到 oracle ASM

如下为对这些功能的测试案例：

1.在线移动数据文件-文件系统-ASM

–可用于数据文件重命令、从一个目录移动到另一目录(可能涉及不同的磁盘或分区、LV)

–直接使用MOVE DATAFILE参数相当于是移动或者重命令，不会保留原数据文件

–keep关键字，可以实现拷贝数据文件到新位置，原文件保留

移动PDB中数据文件需要在指定 的PDB中进行操作：

show con_name

CON_NAME

——————————

CDBPDB2

C##Luda@cdbpdb2>select name from v$dbfile;

NAME

————————————————————————————————————————————————————————————

/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/4FDB7936712F5A06E053D339A8C06FAC/datafile/o1_mf_sysaux_dkx3hppr_.dbf

/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/4FDB7936712F5A06E053D339A8C06FAC/datafile/o1_mf_undotbs1_dkx3hppt_.dbf

/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/4FDB7936712F5A06E053D339A8C06FAC/datafile/o1_mf_pdb2_dkx9pq4r_.dbf

/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/system01.dbf

C##Luda@cdbpdb2>alter database  MOVE DATAFILE ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/4FDB7936712F5A06E053D339A8C06FAC/datafile/o1_mf_sysaux_dkx3hppr_.dbf’ to ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/sysaux01.dbf’;

Database altered.

C##Luda@cdbpdb2>!ls /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/4FDB7936712F5A06E053D339A8C06FAC/datafile/o1_mf_sysaux_dkx3hppr_.dbf

ls: cannot access /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/4FDB7936712F5A06E053D339A8C06FAC/datafile/o1_mf_sysaux_dkx3hppr_.dbf: No such file or directory

C##Luda@cdbpdb2>!ls /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/sysaux01.dbf

/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/sysaux01.dbf

C##Luda@cdbpdb2>select file_name, status, online_status from dba_data_files;

FILE_NAME                                                                        STATUS    ONLINE_

——————————————————————————– ——— ——-

/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/sysaux01.dbf                           AVAILABLE ONLINE

/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/4FDB7936712F5A06E053D339A8C06FA AVAILABLE ONLINE

C/datafile/o1_mf_undotbs1_dkx3hppt_.dbf

/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/4FDB7936712F5A06E053D339A8C06FA AVAILABLE ONLINE

C/datafile/o1_mf_pdb2_dkx9pq4r_.dbf

/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/system01.dbf                           AVAILABLE SYSTEM

观察此时的ALERT日志：

2017-05-23T14:43:17.122383+08:00

CDBPDB2(4):alter database  MOVE DATAFILE ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/4FDB7936712F5A06E053D339A8C06FAC/datafile/o1_mf_sysaux_dkx3hppr_.dbf’ to ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/sysaux01.dbf’

2017-05-23T14:43:17.146362+08:00

Moving datafile /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/4FDB7936712F5A06E053D339A8C06FAC/datafile/o1_mf_sysaux_dkx3hppr_.dbf (22) to /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/sysaux01.dbf

2017-05-23T14:43:55.724994+08:00

Move operation committed for file /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/sysaux01.dbf

2017-05-23T14:43:58.166391+08:00

CDBPDB2(4):Completed: alter database  MOVE DATAFILE ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/4FDB7936712F5A06E053D339A8C06FAC/datafile/o1_mf_sysaux_dkx3hppr_.dbf’ to ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/sysaux01.dbf’

使用KEEP关键字的测试：–OMF管理的不会保留原文件，

C##Luda@cdbpdb2>alter database  MOVE DATAFILE ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/4FDB7936712F5A06E053D339A8C06FAC/datafile/o1_mf_pdb2_dkx9pq4r_.dbf’ to ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/pdb201.dbf’ keep;

Database altered.

C##Luda@cdbpdb2>select file_name, status, online_status from dba_data_files;

FILE_NAME                                                                        STATUS    ONLINE_

——————————————————————————– ——— ——-

/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/sysaux01.dbf                           AVAILABLE ONLINE

/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/pdb201.dbf                             AVAILABLE ONLINE

/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/4FDB7936712F5A06E053D339A8C06FA AVAILABLE ONLINE

C/datafile/o1_mf_undotbs1_dkx3hppt_.dbf

/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/system01.dbf                           AVAILABLE SYSTEM

C##Luda@cdbpdb2>!ls /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/4FDB7936712F5A06E053D339A8C06FAC/datafile/o1_mf_pdb2_dkx9pq4r_.dbf

ls: cannot access /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/4FDB7936712F5A06E053D339A8C06FAC/datafile/o1_mf_pdb2_dkx9pq4r_.dbf: No such file or directory

C##Luda@cdbpdb2>!ls /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/pdb201.dbf

/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/pdb201.dbf

C##Luda@cdbpdb2>alter database  MOVE DATAFILE ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/pdb201.dbf’ to ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/pdb201.dbf’ keep;

Database altered.

C##Luda@cdbpdb2>!ls /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/pdb201.dbf

/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/pdb201.dbf

C##Luda@cdbpdb2>!ls /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/pdb201.dbf

/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/pdb201.dbf

使用REUSE关键字覆盖

C##Luda@cdbpdb2>!ls /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/pdb201.dbf

/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/pdb201.dbf

C##Luda@cdbpdb2>!ls /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/pdb201.dbf

/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/pdb201.dbf

C##Luda@cdbpdb2>alter database  MOVE DATAFILE ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/pdb201.dbf’ to ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/pdb201.dbf’ keep;

alter database  MOVE DATAFILE ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/pdb201.dbf’ to ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/pdb201.dbf’ keep

*

ERROR at line 1:

ORA-01119: error in creating database file ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/pdb201.dbf’

ORA-27038: created file already exists

Additional information: 1

C##Luda@cdbpdb2>alter database  MOVE DATAFILE ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/pdb201.dbf’ to ‘/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/pdb201.dbf’ reuse;

C##Luda@cdbpdb2>!ls /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/pdb201.dbf

ls: cannot access /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/LudaCDB/pdb201.dbf: No such file or directory

C##Luda@cdbpdb2>!ls /u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/pdb201.dbf

/u01/app/oracle12c/oradata/Ludacdb/cdbpdb2/pdb201.dbf

临时表空间使用率高问题的分析解释

当你监控temporary tablespace 的空闲空间，发现空闲空间并没有随着sort的结束而增加，甚至当前数据库没有任何客户端连接也是如此：仍然有大量的temporary类型的extent存在于temporary tablespace，有时候用户会在运行事务的时候遇到ora-1652（在temp表空间上），这个错误表明没有足够的空间，但是当用户的查询失败后，smon并没有去清理临时段。

Scope & Application
===================

如果TEMPORARY TABLESPACE的类型是TEMPORARY，TEMPORARY TABLESPACE里的使用过的空间是不会被释放的，除非shutdown。
如果是PERMANENT，SMON会在process不再使用临时段之后去做清理。

如果使用TEMPORARY类型的临时表空间，数据库刚刚startup后，第一个使用TEMPORARY tablespace进行排序的statement会创一个建sort segment，这个segment不会被释放，除非数据库restart，可以用V$SORT_SEGMENT察看当前的已分配了的sort segments地使用情况。

如果是用PERMANENT tablespace作排序，由smon负责在statement结束之后删除被创建的temporary segments，这样空间可以被其他对象使用。

使用如下查询：
select OWNER,
SEGMENT_NAME,
SEGMENT_TYPE ,
TABLESPACE_NAME
from DBA_SEGMENTS
where SEGMENT_TYPE = ‘TEMPORARY’;

to give results similar to:

OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
—– ———— ———— —————
SYS 4.2 TEMPORARY TEMP

Note: segment_name and tablepace_name are likely to be different.

tablespace的类型可以用如下查询：
select TABLESPACE_NAME,
CONTENTS
from DBA_TABLESPACES
where TABLESPACE_NAME in ( );

to give results similar to:

TABLESPACE_NAME CONTENTS
—————————— ———
TEMP TEMPORARY
TEMP1 PERMANENT

解释
=====

1)
对于使用了TEMPORARY类型的TEMPORARY TABLESPACE，临时段被使用后不被回收是正常的行为（7.3以后），这时如果有ora-1652发生，说明临时表空间是真得不够.

系统中的临时段在oracle startup之后被创建，并只有在oracle shutdown的时候被释放，如果存在大量的extent，一个可能的原因是你的storage子句的设置有问题。

考虑到性能原因，当一个temporary extent被分配的时候，tablespace会做一个标记，操作结束之后这个extent不会被释放或回收，相应的，这个extent被简单的标志为free，对于后面的sort操作是available的，这样就省去了系统分配和回收temporary extent的负载。

2）
对于在PERMANENT的tablespace里面使用temporary segment，记得检查parameter file里面没有设置如下参数：
event=”10061 trace name context forever, level 10″
event=”10269 trace name context forever, level 10″

这两个参数禁止smon去做temporary segment的clean up和coalescing，在oraus.msg里面可以看到具体的定义：
10061, 00000, “disable SMON from cleaning temp segments
10269, 00000, “Don’t do coalesces of free space in SMON”

// *Cause: setting this event prevents SMON from doing free space coalesces

（正常情况下，smon会负责定期做temporary segment的clean up和coalescing。具体作的方式metalink可以找到）

通过查询V$SORT_SEGMENT来看temp segment是free还是being used

For example:
select TABLESPACE_NAME,TOTAL_BLOCKS,USED_BLOCKS,FREE_BLOCKS from v$sort_segment;

>>> DURING the SORT you will see something like this:
TABLESPACE_NAME TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
—————- ———— ———– ———–
TEMP 590 590 0

>>> AFTER the SORT you will see something like this:
TABLESPACE_NAME TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS
————— ———— ———— ———–
TEMP 590 0 590

通过这个查询可以看到在做sort的时候，segment标识为used，sort结束后，标志为free。实际上这时候相应的extents都被释放回SEP（sort extent pool）里面。（SEP是SGA里面存放temp extent的部分，具体描述在metalink也可以找到）

参考MOS文档：Temporary Tablespace, the Sort Extent Pool, and OPS (文档 ID 65973.1)
Temporary Segments Are Not Being De-Allocated After a Sort（Note:1039341.6）

如下为典型的临时表空间问题的排查步骤和命令脚本：

1.查看当前临时表空间配置情况–大小、数据文件个数、扩展情况等
2.哪些会话使用临时表空间较多，具体占用大小、临时段类型
3.找出使用临时表空间较多的SQL信息
4.找出使用临时表空间较多的SESSION信息
###############临时表空间使用过多的排查思路
1.查看当前临时表空间配置情况
–查看当前临时文件大小、是否支持自动扩展等
set linesize 180
col file_name for a50
col tablespace_name for a20
select file_name,file_id,tablespace_name,bytes/1024/1024/1024 gb,status,AUTOEXTENSIBLE,MAXBYTES from dba_temp_files;

set linesize 180
col name for a50
select * from V$TEMPFILE;
—查看临时表空间使用率
col tablespace_name for a30
select (s.tot_used_blocks/f.total_blocks)*100 as “percent used”
from
(select sum(used_blocks) tot_used_blocks from gv$sort_segment where tablespace_name=’TEMP’) s,
(select sum(blocks) total_blocks from dba_temp_files where tablespace_name=’TEMP’) f;
====
select f.TABLESPACE_NAME,f.total_MB,f.USED_MB,f.USED_MB/f.total_MB*100 as percent_used
from (select bb.TABLESPACE_NAME,bb.temp_MB total_MB,aa.used_blocks*P.VALUE/1024/1024 USED_MB
from (select a.TABLESPACE_NAME,sum(a.used_blocks) USED_blocks from gv$sort_segment a group by a.TABLESPACE_NAME) aa,
(select b.TABLESPACE_NAME,sum(b.BYTES/1024/1024) temp_MB from dba_temp_files b group by b.TABLESPACE_NAME) bb, SYS.V_$SYSTEM_PARAMETER P
where aa.TABLESPACE_NAME= bb.TABLESPACE_NAME and UPPER(P.NAME)=’DB_BLOCK_SIZE’) f;

###########################################################

2.查看当前哪个会话使用的临时段较大
—–找出使用临时空间最多的20个会话session_addr及其SQL_ID
set linesize 180
select * from
(select username,session_addr,sql_id,contents,segtype,blocks*8/1024 mb
from v$sort_usage order by blocks desc)
where rownum<11;

select * from v$temporary_lobs;

#####查出TEMP段大小以及进程相关信息
set linesize 180 pagesize 10000
col username for a10
col program for a20
col machine for a15
COL EVENT FOR A28
col sql_id for a15
col spid for a8
col TABLESPACE for a6

select * from (select s.username,b.spid,s.sid,s.program,s.machine,s.sql_id,s.event,to_char(s.LOGON_TIME,’yyyymmdd hh24:mi:ss’),u.tablespace, u.segtype, round(((u.blocks*P.VALUE)/1024/1024),2) MB
from v$session s, v$sort_usage u, SYS.V_$SYSTEM_PARAMETER P,v$process b
where s.saddr = u.session_addr
and s.paddr=b.addr
AND UPPER(P.NAME)=’DB_BLOCK_SIZE’
order by MB DESC) where rownum<31;

===prev_sql_id
========
select * from (select s.username,b.spid,s.sid,s.program,s.machine,s.prev_sql_id,to_char(s.LOGON_TIME,’yyyymmdd hh24:mi:ss’), u.segtype, round(((u.blocks*P.VALUE)/1024/1024),2) MB
from v$session s, v$sort_usage u, SYS.V_$SYSTEM_PARAMETER P,v$process b
where s.saddr = u.session_addr
and s.paddr=b.addr
AND UPPER(P.NAME)=’DB_BLOCK_SIZE’
order by MB DESC) where rownum<31;
===

—通过SQL_ID查SQL文本–如果SESSION已执行完排序SQL后并未有执行其它SQL，则可如下查：
select SQL_ID,sql_text from v$sqltext where sql_id in(’08yg5rb6upbra’,”) order by SQL_ID,piece;
###########################################################
3.找出使用临时表空间较多的SQL信息
v$sort_usage中的SQL_ID是会话前一条执行的SQL,–即对应V$SESSION中的字段PREV_SQL_ID.
–从11.2.0.2这个版本开始，v$sort_usage的基表x$ktsso中增加了一个字段ktssosqlid，表示该临时段真正关联的SQL。
以上述的测试结果为例，查询这个基表的结果如下：
select ktssosqlid,v$session.SADDR from x$ktsso, v$session where ktssoses = v$session.saddr
and ktssosno = v$session.serial#
and v$session.SADDR in(‘3231FE14′,’322DEA14′);

对于11.2.0.2之前版本的查询：
—通过第三步查出的session_addr关联SADDR查prev_sql_id–要求prev_sql_id执行后未执行新SQL
select sid,prev_sql_id, sql_id from v$session where saddr=’070000294AC0D050’;

#######################################
4.找出使用临时表空间较多的SESSION信息
–使用第三步查出的session_addr查出的使用临时表空间较多的SQL_ID及SESSION信息：
select a.sql_id sort_sql_id,b.sql_id,b.prev_sql_id, a.contents,a.segtype,a.blocks*8/1024 Mb,b.sid
from v$sort_usage a,v$session b
where a.session_addr=b.saddr and (b.sid=31 or b.saddr in(‘3231FE14′,’322DEA14′));
–根据session_addr查询会话的SID/终端信息等
col spid for a10
col machine for a30
select b.spid,a.sid,a.username,a.OSUSER,a.program,a.machine from v$session a,v$process b
where a.paddr=b.addr and a.type=’USER’ and a.SADDR in(‘3231FE14′,’322DEA14’);

################
适用11.2.0.2及以上，直接查出排序空间使用较多的SQL_ID
col username for a10
col osuser for a10
col tablespace for a15
select * from
(select k.inst_id “INST_ID”,
ktssoses “SADDR”,
sid,
ktssosno “SERIAL#”,
username “USERNAME”,
osuser “OSUSER”,
s.machine,
s.event,
ktssosqlid “SQL_ID”,
ktssotsn “TABLESPACE”,
decode(ktssocnt, 0, ‘PERMANENT’, 1, ‘TEMPORARY’) “CONTENTS”,
decode(ktssosegt, 1, ‘SORT’, 2, ‘HASH’, 3, ‘DATA’, 4, ‘INDEX’,
5, ‘LOB_DATA’, 6, ‘LOB_INDEX’ , ‘UNDEFINED’) “SEGTYPE”,
ktssofno “SEGFILE#”,
ktssobno “SEGBLK#”,
ktssoexts “EXTENTS”,
ktssoblks “BLOCKS”,
round(ktssoblks*p.value/1024/1024, 2) “SIZE_MB”,
ktssorfno “SEGRFNO#”
from x$ktsso k, v$session s,
(select value from v$parameter where name=’db_block_size’) p
where ktssoses = s.saddr
and ktssosno = s.serial#
order by size_mb)
where rownum<11;

====
select * from
(select
ktssoses “SADDR”,
s.sid,
username “USERNAME”,
s.machine,
s.event,
ktssosqlid “SQL_ID”,
ktssotsn “TABLESPACE”,
decode(ktssocnt, 0, ‘PERMANENT’, 1, ‘TEMPORARY’) “CONTENTS”,
decode(ktssosegt, 1, ‘SORT’, 2, ‘HASH’, 3, ‘DATA’, 4, ‘INDEX’,
5, ‘LOB_DATA’, 6, ‘LOB_INDEX’ , ‘UNDEFINED’) “SEGTYPE”,
round(ktssoblks*p.value/1024/1024, 2) “SIZE_MB”
from x$ktsso k, v$session s,
(select value from v$parameter where name=’db_block_size’) p
where ktssoses = s.saddr
and ktssosno = s.serial#
order by size_mb)
where rownum<11;