undo 表空间的扩展与收缩
一.表空间的扩展有两种方式:
1.手动增大表空间
2.将表空间设置为自动扩展
1.查看undo表空间
A.查询当前所使用的UNDO表空间
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
B.查询表空间名为undotbs1的UNDO表空间
SQL> select * from dba_tablespaces where tablespace_name=' UNDOTBS1';
TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
------------------------------ ---------- -------------- ----------- -----------
MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS CONTENTS LOGGING FOR EXTENT_MAN
----------- ------------ ---------- --------- --------- --------- --- ----------
ALLOCATIO PLU SEGMEN DEF_TAB_ RETENTION BIG
--------- --- ------ -------- ----------- ---
UNDOTBS1 8192 65536 1
2147483645 65536 ONLINE UNDO LOGGING NO LOCAL
SYSTEM NO MANUAL DISABLED NOGUARANTEE NO
C.查询UNDO表的id、大小、是否开启自动扩展
SQL> select file_name, file_id,AUTOEXTENSIBLE ,(bytes/1024/1024) from dba_data_files where tablespace_name=' UNDOTBS1';
FILE_NAME
--------------------------------------------------------------------------------
FILE_ID AUT (BYTES/1024/1024)
---------- --- -----------------
/oracle/app/oradata/TEST/undotbs01.dbf
2 YES 115
--------------------------------------------------------------------------------
FILE_ID AUT (BYTES/1024/1024)
---------- --- -----------------
/oracle/app/oradata/TEST/undotbs01.dbf
2 YES 115
2.手动扩展UNDO表空间
SQL> alter database datafile 2 resize
120M;
Database altered.
SQL> select file_name,file_id,(bytes/1024/1024) from dba_data_files where tablespace_name='UNDOTBS1';
FILE_NAME
--------------------------------------------------------------------------------
FILE_ID (BYTES/1024/1024)
---------- -----------------
/oracle/app/oradata/TEST/undotbs01.dbf
2 120 -----------------------------------扩展到120M
--------------------------------------------------------------------------------
FILE_ID (BYTES/1024/1024)
---------- -----------------
/oracle/app/oradata/TEST/undotbs01.dbf
2 120 -----------------------------------扩展到120M
3.设置自动扩展UNDO表空间
SQL> select file_name,file_id,AUTOEXTENSIBLE from dba_data_files where tablespace_name='UNDOTBS1';
FILE_NAME
--------------------------------------------------------------------------------
FILE_ID AUT
---------- ---
/oracle/app/oradata/TEST/undotbs01.dbf
2 NO ------------------------------ AUTOEXTENSIBLE自动扩展为关闭状态
--------------------------------------------------------------------------------
FILE_ID AUT
---------- ---
/oracle/app/oradata/TEST/undotbs01.dbf
2 NO ------------------------------ AUTOEXTENSIBLE自动扩展为关闭状态
开启自动扩展
SQL> alter database datafile 2 autoextend on next 1M;
Database altered.
SQL> select file_name,file_id,AUTOEXTENSIBLE from dba_data_files where tablespace_name='UNDOTBS1';
FILE_NAME
--------------------------------------------------------------------------------
FILE_ID AUT
---------- ---
/oracle/app/oradata/TEST/undotbs01.dbf
2 YES ------------------------------ AUTOEXTENSIBLE自动扩展为开启状态
--------------------------------------------------------------------------------
FILE_ID AUT
---------- ---
/oracle/app/oradata/TEST/undotbs01.dbf
2 YES ------------------------------ AUTOEXTENSIBLE自动扩展为开启状态
二.表空间的收缩
1.使用空间低于20M的,可以使用risize手动收缩
SQL> alter database datafile 2 resize 18M;
2.新建UNDO表空间替换原表空间
SQL> create undo tablespace undo2 datafile '/oracle/app//undo2.dbf' size 20M;
Tablespace created.
将表空间更改为undo2
SQL> alter system set undo_tablespace=undo2 scope=both;
System altered.
更改成功,当前UNDO表空间为undo2
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDO2
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDO2
删除原undo表空间
SQL> drop tablespace UNDOTBS1 including contents and datafiles;
SQL> drop tablespace UNDOTBS1 including contents and datafiles;
Tablespace dropped.
oracle 手工备份 恢复的方式
1 手工备份
优点:成功率高,简单直接
缺点:需要停服务,影响业务
优点:成功率高,简单直接
缺点:需要停服务,影响业务
方式:
A shutdown immediate 关闭数据库
B 拷贝所有数据库文件(数据文件、控制文件、在线日志文件、参数文件、口令文件)
C 重启数据库
2 恢复方式
A 拷贝全部备份数据文件到当前数据库目录下(覆盖)
B 启动数据库到mount状态(start mount)
C 设置恢复指定的时间格式 (alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';)
D 执行不完全恢复,可利用logminer确定需要恢复的具体时间(recover database until time '2012-12-21 12:12:12)
E 打开数据库到只读状态(可选)alter database open read only
F resetiogs 最后打开数据库 (alter database open resetlogs;)
B 启动数据库到mount状态(start mount)
C 设置恢复指定的时间格式 (alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';)
D 执行不完全恢复,可利用logminer确定需要恢复的具体时间(recover database until time '2012-12-21 12:12:12)
E 打开数据库到只读状态(可选)alter database open read only
F resetiogs 最后打开数据库 (alter database open resetlogs;)
转载于:https://blog.51cto.com/2141470/1082196