Part1问题报错

开发反馈报警日志报错ORA-01555，这个报错很常见，是查询的数据已经在undo表空间覆盖了，通过后台报警日志可以直接定位到语句，是一个比较正常的update语句，条件没有很大的时间差，所以打算直接扩容操作的。

Part2确认环境

2.1、 查看Uudo表空间大小

SQL> set pages 999 lines 999
SQL> select a.tablespace_name,
       round(a.bytes / 1024 / 1024 /  1024, 0) "sum G",
       round((a.bytes - b.bytes) / 1024 /  1024 / 1024, 0) "used G",
       round(b.bytes / 1024 / 1024 /  1024, 0) "free G",
       round(((a.bytes - b.bytes) /  a.bytes) * 100, 2) "used%"
  from (select tablespace_name,  sum(bytes) bytes
          from dba_data_files
         group by tablespace_name) a,
       (select tablespace_name,  sum(bytes) bytes, max(bytes) largest
          from dba_free_space
         group by tabl  2    3    4    5    6    7    8    9   10   11  espace_name) b
where a.tablespace_name =  b.tablespace_name
order by ((a.bytes - b.bytes) / a.bytes)  desc; 12   13  

TABLESPACE_NAME       sum G     used G  free G      used%
------------------------------ ---------- ---------- ---------- ----------
USERS                0    0       0      53.75
SYSAUX            5    2       3       49.3
SYSTEM          5    1       4      23.15
UNDOTBS1          0    0       0       14.6
UNDOTBS2             0    0       0       7.28

2.2、 查看磁盘组空间大小

SQL> select group_number,name,total_mb/1024,round(free_mb/1024,2),round((total_mb - free_mb)/total_mb,2)*100 || '%'  from v$asm_diskgroup;

GROUP_NUMBER NAME       TOTAL_MB/1024 ROUND(FREE_MB/1024,2) ROUND((TOTAL_MB-FREE_MB)/TOTAL_MB,2)*100|
------------ ------------------------------ ------------- --------------------- - 
    1 ARCH          200   188.36 6%
    2 DATA         1000   906.83 9%
    3 OCR          150   123.89 17%
    4 REDO           30    29.04 3%

可以看到DATA磁盘组空间很富裕，可以直接扩容。

2.3、 查看Undo表空间数据文件位置

SQL> select file_name from dba_data_files where tablespace_name in ('UNDOTBS1','UNDOTBS2');

FILE_NAME
-------------------------------------------- 
+OCR/TR2/DATAFILE/undotbs1.267.1183732313
+OCR/TR2/DATAFILE/undotbs2.270.1183732541

发现居然放在OCR磁盘组里，而且OCR磁盘组根本就不大，这显然是不对的。

2.4、 检查OCR磁盘组里的数据文件

SQL>  select file_name from dba_data_files where file_name like '+OCR%';

FILE_NAME
---------------------------------------------------------------------------
+OCR/TR2/DATAFILE/system.265.1183732263
+OCR/TR2/DATAFILE/sysaux.266.1183732297
+OCR/TR2/DATAFILE/undotbs1.267.1183732313
+OCR/TR2/DATAFILE/users.268.1183732313
+OCR/TR2/DATAFILE/undotbs2.270.1183732541

2.5、 临时表空间的位置

SQL> select tablespace_name,FILE_NAME from dba_temp_files where FILE_NAME like '+OCR%';

TABLESPACE_NAME         FILE_NAME
----------------       ---------------- 
TEMP                +OCR/TR2/TEMPFILE/temp.269.1183732389

这个当时没想到，后来仔细观察数据库后台报警日志发现临时表空间也在

Part3迁移操作

3.1、 确认数据文件位置

听说12C/19C可以在线迁移

SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------
+OCR/TR2/DATAFILE/system.265.1183732263
+OCR/TR2/DATAFILE/sysaux.266.1183732297
+OCR/TR2/DATAFILE/undotbs2.270.1183732541
+OCR/TR2/DATAFILE/users.268.1183732313
+OCR/TR2/DATAFILE/undotbs1.267.1183732313

3.1、 执行迁移命令

alter database move datafile '+OCR/TR2/DATAFILE/users.268.1183732313' to '+DATA'; 

alter database move datafile '+OCR/TR2/DATAFILE/undotbs2.270.1183732541' to '+DATA'; 

alter database move datafile '+OCR/TR2/DATAFILE/undotbs1.267.1183732313' to '+DATA'; 

alter database move datafile '+OCR/TR2/DATAFILE/sysaux.266.1183732297' to '+DATA'; 

alter database move datafile '+OCR/TR2/DATAFILE/system.265.1183732263' to '+DATA'; 

3.2、 临时表空间迁移失败

alter database move tempfile '+OCR/TRMVDB2/TEMPFILE/temp.269.1183732389' to '+DATA'; 

好像是不支持。

3.3、 确认迁移后的位置路径

SQL> select file_name from dba_data_files;

FILE_NAME
------------------------------------------------- 
+DATA/TR2/DATAFILE/system.263.1190151459
+DATA/TR2/DATAFILE/sysaux.262.1190151397
+DATA/TR2/DATAFILE/undotbs2.260.1190151345
+DATA/TR2/DATAFILE/users.259.1190151323
+DATA/TR2/DATAFILE/tr_integration_cwdata9.256.1185382707
+DATA/TR2/DATAFILE/undotbs1.261.1190151365

可以发现数据文件都已经迁移到DATA磁盘组里了。

Part4扩容undo表空间

alter database datafile '+DATA/TRMVDB2/DATAFILE/undotbs2.260.1190151345' resize 31G;

alter database datafile '+DATA/TRMVDB2/DATAFILE/undotbs1.261.1190151365' resize 31G;

alter database datafile '+DATA/TRMVDB2/DATAFILE/undotbs2.260.1190151345' autoextend off;
alter database datafile '+DATA/TRMVDB2/DATAFILE/undotbs1.261.1190151365' autoextend off;

设置 扩容+不自增，方便管理和计算。

Part5切换TEMP临时表空间

5.1、 查看临时表空间的信息

col PROPERTY_NAME for a30
col PROPERTY_VALUE for a30
col DESCRIPTION for a40

select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME         PROPERTY_VALUE        DESCRIPTION
------------------------------ ------------------------------ ----------------------------------------
DEFAULT_TEMP_TABLESPACE        TEMP         Name of default temporary tablespace

5.2、 设置新的临时表空间TEMP01

create temporary tablespace temp01 tempfile '+DATA' size 31G autoextend off;

5.2、 指定数据库新的临时表空间

alter database default temporary tablespace temp01;

5.3、 查询现在的数据库临时表是否是temp01

select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';

5.4、 查询是否有sql语句执行占据临时表空间

Select se.username,se.sid,se.serial#,su.extents,su.blocks*to_number(rtrim(p.value))as Space, tablespace,segtype,sql_text
    from v$sort_usage su,v$parameter p,v$session se,v$sql s
    where p.name='db_block_size' and su.session_addr=se.saddr and s.hash_value=su.sqlhash and s.address=su.sqladdr
    order by se.username,se.sid;

5.4、 如果有就干掉它

alter system kill session 'sid,serial#,@1'; #(假如某一条运行的sql语句的SID为555，serial#为777,@代表的是节点)

5.5、 删除放在OCR磁盘组里的临时表空间

drop tablespace temp including contents and datafiles;

5.5、 查询数据库临时表空间的存在地

SQL> select tablespace_name,FILE_NAME from dba_temp_files;

TABLESPACE_NAME         FILE_NAME
------------------------------ ------------------------- 
TEMP01          +DATA/TR2/TEMPFILE/temp01.264.1190152477