Oracle体系结构之数据文件管理-白红宇

Oracle体系结构之数据文件管理

阅读量：4972 次

发布时间：2019-06-12

本文共 25296 字，大约阅读时间需要 84 分钟。

数据文件分2个方向管理：

物理结构和逻辑结构。

数据库的存储层次结构图：

.............

逻辑结构：物理结构：

......................

表空间类型：system、sysaux、temp、undo、index、其他

创建表空间：

语法：create tablespace <name>

datafile '<dir>' size N[k|m|g|t];

创建表空间时我们需呀考虑几个问题：

1）具有create tablespace权限

2）smallfile和bigfile（T级以上：可以减少数据问文件个数，方便管理）：如果系统默认是存放smallfile，则创建表空间create tablespace相当于cteate smallfile tablespace；如果系统默认是存放bigfile，则创建表空间create tablespace相当于cteate bigfile tablespace；

如何查看系统默认创建表空间是smallfile还是bigfile文件，可以通过查询database_properties来查看，如下：可知现系统默认创建为smallfile表空间文件。

SQL> desc database_properties

?? ????? ??

----------------------------------------- -------- ----------------------------

PROPERTY_NAME NOT NULL VARCHAR2(30)

PROPERTY_VALUE VARCHAR2(4000)

DESCRIPTION VARCHAR2(4000)

SQL> col PROPERTY_NAME for a10;

SQL> col PROPERTY_value for a20;

SQL> select PROPERTY_NAME,PROPERTY_VALUE from database_properties where property_name like '%TBS%';

PROPERTY_NAME PROPERTY_VALUE

------------------------------ --------------------

DEFAULT_TBS_TYPE SMALLFILE

如果想修改系统默认创建表空间为bigfile文件，操作如下：

SQL> alter database set default bigfile tablespace;

Database alterd.

SQL> select PROPERTY_NAME,PROPERTY_VALUE from database_properties where property_name like '%TBS%';

PROPERTY_NAME PROPERTY_VALUE

------------------------------ --------------------

DEFAULT_TBS_TYPE BIGFILE

3）IO是否够用。

表空间管理方式（区管理方式）：

1）字典管理：已淘汰：extent management dictionary

2）本地管理：9i后（默认）：extent management local

8i—>10g的数据迁移需要研究？

查看本地管理方式：dba_tablespace

SQL> select tablespace_name,EXTENT_MANAGEMENT from dba_tablespaces;

TABLESPACE_NAME EXTENT_MAN

------------------------------ ----------

SYSTEM LOCAL

UNDOTBS1 LOCAL

SYSAUX LOCAL

TEMP LOCAL

USERS LOCAL

表空间状态：1）offline、2）online、3）read only、4）read write

默认情况下表空间状态为online和read write：

SQL> select file#,status,enabled from v$datafile;

FILE# STATUS ENABLED

---------- ------- -------------------------------------------

1 SYSTEM READ WRITE

2 ONLINE READ WRITE

3 ONLINE READ WRITE

4 ONLINE READ WRITE

SQL> select b.tablespace_name,a.file#,a.status,a.enabled from v$datafile a,dba_data_files b where a.file#=b.file_id;

TABLESPACE_NAME FILE# STATUS ENABLED

------------------------------ ---------- ------- -----------------------------------------------------------------

SYSTEM 1 SYSTEM READ WRITE

UNDOTBS1 2 ONLINE READ WRITE

SYSAUX 3 ONLINE READ WRITE

USERS 4 ONLINE READ WRITE

修改表空间状态：

SQL> alter tablespace users read only;

SQL> alter tablespace users read write;

SQL> alter tablespace users offline;

???????

SQL> alter tablespace users online;

???????

Tablespace	Online	Offline
System	必须online	必须read write
Sysaux	可以offline	不能read only
Undo	不能offline	不能read only

表空间的重命名：alter tablespace <ole_tablespace_name> rename to <new_tablespace_name>;

system、sysaux不能改名

undo、temp表空间可以改名但是同时要把参数文件改了，不建议改。

users表空间可以改名

其他自定义表空间可以根据自己要求改名。

SQL> create tablespace fi datafile '/u01/oracle/oradata/orcl/fi.dbf' size 10m;

???????

SQL> alter tablespace fi rename to f1;

???????

表空间大小管理：

1）表空间自动扩张：

可以通过dba_data_files表的AUTOEXTENSIBLE字段查询表空间文件是否为自动扩张。

SQL> select tablespace_name,file_name,AUTOEXTENSIBLE from dba_data_files;

TABLESPACE FILE_NAME AUT

---------- -------------------------------------------------- ---

SYSTEM /u01/oracle/oradata/rezin/system01.dbf YES

UNDOTBS1 /u01/oracle/oradata/rezin/undotbs01.dbf YES

SYSAUX /u01/oracle/oradata/rezin/sysaux01.dbf YES

USERS /u01/oracle/oradata/rezin/users01.dbf YES

F1 /u01/oracle/oradata/orcl/fi.dbf NO

通过以上查询可知，只有F1表空间为非自动扩张状态，将其改为自动扩张表空间：

语法：SQL> alter database <路径>|<编号> autoextend on next nM maxsize Ng|t;

SQL> alter database datafile '/u01/oracle/oradata/orcl/fi.dbf' autoextend on;

SQL> select tablespace_name,file_name,AUTOEXTENSIBLE from dba_data_files;

TABLESPACE FILE_NAME AUT

---------- -------------------------------------------------- ---

SYSTEM /u01/oracle/oradata/rezin/system01.dbf YES

UNDOTBS1 /u01/oracle/oradata/rezin/undotbs01.dbf YES

SYSAUX /u01/oracle/oradata/rezin/sysaux01.dbf YES

USERS /u01/oracle/oradata/rezin/users01.dbf YES

F1 /u01/oracle/oradata/orcl/fi.dbf YES

2）手工resize数据文件大小

SQL> alter database datafile '/u01/oracle/oradata/orcl/fi.dbf' autoextend off;

SQL> alter database datafile '/u01/oracle/oradata/orcl/fi.dbf' resize 100m;

???????

3）添加数据文件（打开自动扩张）

SQL> alter tablespace f1 add datafile '/u01/oracle/oradata/orcl/f2.dbf' size 10m autoextend on next 10m maxsize 100m;

表空间数据文件的重命名和重定位：

条件：数据库在mount阶段或open阶段，归档模式，数据文件必须可以offline状态，如果数据文件不能offline，只能在mount状态重命名。

步骤：1）将要改名的表空间数据文件offline

2）操作系统级别移动数据文件（改名）

3）执行从命名命令

SQL> alter database rename file '<oldname>' to '<newname>';

4）将数据文件状态改为online状态。

SQL> alter tablespace f1 offline;

???????

SQL> ho cp /u01/oracle/oradata/orcl/f2.dbf /u01/oracle/oradata/orcl/f1.dbf

SQL> alter database rename file '/u01/oracle/oradata/orcl/f2.dbf'

to '/u01/oracle/oradata/orcl/f1.dbf';

???????

SQL> alter tablespace f1 online;

???????

删除表空间：

语法：drop tablespace <name> including contents and datafiles;

SQL> drop tablespace f1;

重新创建表空间：

SQL> create tablespace f2 datafile '/u01/oracle/oradata/orcl/f1.dbf' reuse;

???????

SQL> drop tablespace f2 including contents and datafiles;

???????

获取表空间和数据文件信息（数据文件相关视图）：

表空间信息：

dba_tablespace

v$tablespace

数据文件信息：

dba_data_files

v$datafile

临时数据文件信息：

dba_temp_files

v$tempfile

系统表空间：

字典所在，定义信息，一般不放用户数据

1）空间管理：一般放单个文件最好,看磁盘空间是否够用等

2）备份：归档模式备份，非归档模式只能冷备。

热备：A)alter tablespace system begin backup;

B)$cp '<system dir>' /bak

C)alter tablespace system end backup;

rman备份：back tablespace system;

[oracle@localhost ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Mar 18 09:31:40 2015

connected to target database: REZIN (DBID=633392282)

RMAN> backup tablespace system;

3）system异常处理：

A非归档、无备份-->不能恢复

无法恢复

B归档、有备份、日志完整--->可以恢复

热备文件恢复：

SQL> startup

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1218968 bytes

Variable Size 96470632 bytes

Database Buffers 180355072 bytes

Redo Buffers 7168000 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 1 - see DBWR trace file

ORA-01110: data file 1: '/u01/oracle/oradata/orcl/system01.dbf'

SQL> ho ls /u01/oracle/oradata/orcl/system01.dbf

ls: /u01/oracle/oradata/orcl/system01.dbf: 没有那个文件或目录

SQL> alter database open;

alter database open

ERROR at line 1:

ORA-01113: file 1 needs media recovery

ORA-01110: data file 1: '/u01/oracle/oradata/orcl/system01.dbf'

SQL> recover database;

Media recovery complete.

SQL> alter database open;

Database altered.

rman恢复：

RMAN> list backup；

RMAN> restore tablespace system;

Starting restore at 19-MAR-15

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=155 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00001 to /u01/oracle/oradata/orcl/system01.dbf

channel ORA_DISK_1: reading from backup piece /u01/oracle/flash_recovery_area/ORCL/backupset/2015_03_19/o1_mf_nnndf_TAG20150319T175033_bjo6zc4b_.bkp

channel ORA_DISK_1: restored backup piece 1

piece handle=/u01/oracle/flash_recovery_area/ORCL/backupset/2015_03_19/o1_mf_nnndf_TAG20150319T175033_bjo6zc4b_.bkp tag=TAG20150319T175033

channel ORA_DISK_1: restore complete, elapsed time: 00:00:35

Finished restore at 19-MAR-15

RMAN> recover tablespace system;

Starting recover at 19-MAR-15

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:05

Finished recover at 19-MAR-15

RMAN> sql 'alter database open';

sql statement: alter database open

RMAN>

C归档、有备份、日志不完整--->可以恢复，但不保证数据丢失

4、system表空间不能offline，不能read only，不能改名

sysaux表空间

作用：存储一些默认的工具，程序,减轻system负担，尤其IO负担。

sysaux表空间名字不能更改，不能drop，不能read only，能够offline

查询sysaux表空间下有哪些工具：

SQL> desc v$sysaux_occupants

Name Null? Type

----------------------------------------- -------- ----------------------------

OCCUPANT_NAME VARCHAR2(64)

OCCUPANT_DESC VARCHAR2(64)

SCHEMA_NAME VARCHAR2(64)

MOVE_PROCEDURE VARCHAR2(64)

MOVE_PROCEDURE_DESC VARCHAR2(64)

SPACE_USAGE_KBYTES NUMBER

SQL> col OCCUPANT_NAME for a20;

SQL> col SCHEMA_NAME for a20;

SQL> select OCCUPANT_NAME, SCHEMA_NAME from v$sysaux_occupants;

OCCUPANT_NAME SCHEMA_NAME

-------------------- --------------------

LOGMNR SYSTEM

LOGSTDBY SYSTEM

STREAMS SYS

XDB XDB

AO SYS

XSOQHIST SYS

XSAMD OLAPSYS

SM/AWR SYS

SM/ADVISOR SYS

SM/OPTSTAT SYS

SM/OTHER SYS

STATSPACK PERFSTAT

ODM DMSYS

SDO MDSYS

WM WMSYS

ORDIM ORDSYS

ORDIM/PLUGINS ORDPLUGINS

ORDIM/SQLMM SI_INFORMTN_SCHEMA

EM SYSMAN

TEXT CTXSYS

ULTRASEARCH WKSYS

ULTRASEARCH_DEMO_USE WK_TEST

EXPRESSION_FILTER EXFSYS

EM_MONITORING_USER DBSNMP

TSM TSMSYS

JOB_SCHEDULER SYS

26 rows selected.

备份：归档（非归档只能冷备，无大意义）可以热备也可以rman备份。

热备：A)alter tablespace sysaux begin backup;

B)$cp '<system dir>' /bak

C)alter tablespace sysaux end backup;

rman备份：back tablespace sysaux;

[oracle@localhost ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Mar 18 09:31:40 2015

connected to target database: REZIN (DBID=633392282)

RMAN> back tablespace sysaux;

sysaux表空间的异常处理：

1）有备份--->还原，恢复

使用热备文件恢复：

SQL> startup

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1218968 bytes

Variable Size 79693416 bytes

Database Buffers 197132288 bytes

Redo Buffers 7168000 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 3 - see DBWR trace file

ORA-01110: data file 3: '/u01/oracle/oradata/orcl/sysaux01.dbf'

SQL> ho cp /u01/oracle/sysaux01.dbf /u01/oracle/oradata/orcl/sysaux01.dbf

SQL> alter database open;

alter database open

ERROR at line 1:

ORA-01113: file 3 needs media recovery

ORA-01110: data file 3: '/u01/oracle/oradata/orcl/sysaux01.dbf'

SQL> recover database;

Media recovery complete.

SQL> alter database open;

Database altered.

rman备份恢复：

[oracle@localhost ~]$ rman target /

RMAN> list backup;

RMAN> restore tablespace sysaux;

Starting restore at 20-MAR-15

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=155 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00003 to /u01/oracle/oradata/orcl/sysaux01.dbf

channel ORA_DISK_1: reading from backup piece /u01/oracle/flash_recovery_area/ORCL/backupset/2015_03_20/o1_mf_nnndf_TAG20150320T054421_bjpjsot8_.bkp

channel ORA_DISK_1: restored backup piece 1

piece handle=/u01/oracle/flash_recovery_area/ORCL/backupset/2015_03_20/o1_mf_nnndf_TAG20150320T054421_bjpjsot8_.bkp tag=TAG20150320T054421

channel ORA_DISK_1: restore complete, elapsed time: 00:00:16

Finished restore at 20-MAR-15

RMAN> recover tablespace sysaux;

Starting recover at 20-MAR-15

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:02

Finished recover at 20-MAR-15

RMAN> sql 'alter database open';

sql statement: alter database open

RMAN>

2）无备份--->offline sysaux表空间，然后迁移数据，再重新建库，将数据迁移回来。

无备份下sysaux表空间损坏如何打开数据库：

undo表空间管理：

undo表空间作用：回滚（没有提交），前滚（已提交，但没有写入数据文件）

1）事务的回退：rollback

2）事务的恢复：撤销没有提交的内容

3）读一致性：事务只能查询自己修改过的内容或是原来的内容（锁的作用）。

4）闪回数据：查询3600面之前的数据

undo表空间管理模式/undo表空间参数：自动/手动，由undo_management参数决定undo表空间管理模式。

查询undo表空间管理模式：

SQL> show parameter undo_m

NAME TYPE VALUE

------------------------------------ ---------- -----------------------------------------------------

undo_management string AUTO

查询系统使用的默认undo表空间：

SQL> show parameter undo_t

NAME TYPE VALUE

------------------------------------ ---------- ------------------------------

undo_tablespace string UNDOTBS1

undo表空间段类型：

1）系统段：存放系统表空间产生的镜像数据。

2）非系统段：存放非系统表空间产生的镜像数据。

3）临时段：能够offline的表空间产生，记录offline阶段oracle数据库产生的数据。

查询如下视图：查寻系统段和非系统段通过v$rollname

SQL> select * from v$rollname;

USN NAME

---------- --------------------------

0 SYSTEM

1 _SYSSMU1$

2 _SYSSMU2$

3 _SYSSMU3$

4 _SYSSMU4$

5 _SYSSMU5$

6 _SYSSMU6$

7 _SYSSMU7$

8 _SYSSMU8$

9 _SYSSMU9$

10 _SYSSMU10$

11 rows selected.

查询临时段通过dba_segments：

SQL> ALTER tablespace users offline;

Tablespace altered.

SQL> col SEGMENT_NAME for a20;

SQL> col SEGMENT_TYPE for a20;

SQL> select segment_name,segment_type from dba_segments where segment_type like '%DEF%';

SEGMENT_NAME SEGMENT_TYPE

-------------------- --------------------

1.60881 DEFERRED ROLLBACK

查询undo表空间保存历史数据最久多少：

SQL> show parameter undo_r

NAME TYPE VALUE

------------------------------------ ---------- ------------------------------

undo_retention integer 900

undo表空间建立、删除、更改：

1）建立：建库时自动建立

手工建立：SQL>create smallfile|bigfile undo tablespace <name> datafile <dir> size <size> autoextend on next size <size> maxsize <size>;

SQL> create undo tablespace undo1 datafile '/u01/oracle/oradata/orcl/undo1.dbf' size 100m autoextend on;

Tablespace created.

2）删除：drop tablespace <undoname> including contents and datafile;

3）更改：alter system set audo_tablespace=<undo_name>;

SQL>alter tablespace undotbs1 tename to undotbs;

SQL>shutdown immediate

SQL>startup

估算undo表空间该设为多大大小：

公式：undo space=(undo_retention*(undo blocks per second*db block size))+db block_size

SQL> show parameter undo_retention

NAME TYPE VALUE

------------------------------------ ---------- ------------------------------

undo_retention integer 900

SQL> select sum(undoblks)/sum((end_time-begin_time)*10800) from v$undostat;

SUM(UNDOBLKS)/SUM((END_TIME-BEGIN_TIME)*10800)

----------------------------------------------

1.98037077

SQL> select 900* 1.98037077*8192 from dual;

900*1.98037077*8192

-----------------------------------------

14600877.6

查询undo表空间当前分配大小：

1）SQL> select max(block_id) from dba_extents where file_id=2;

MAX(BLOCK_ID)

-------------

4617

2）SQL> select 4617*8192/1024/1024||'G' from dual;

4617*8192/1

-----------------------------

36.0703125G

作用：可以将undo表空间容量变小。

undo的资料查询：（47后半部分）

1）SQL> desc dba_rollback_segs

Name Null? Type

----------------------------------------- -------- ----------------------------

SEGMENT_NAME NOT NULL VARCHAR2(30)

OWNER VARCHAR2(6)

TABLESPACE_NAME NOT NULL VARCHAR2(30)

SEGMENT_ID NOT NULL NUMBER

FILE_ID NOT NULL NUMBER

BLOCK_ID NOT NULL NUMBER

INITIAL_EXTENT NUMBER

NEXT_EXTENT NUMBER

MIN_EXTENTS NOT NULL NUMBER

MAX_EXTENTS NOT NULL NUMBER

PCT_INCREASE NUMBER

STATUS VARCHAR2(16)

INSTANCE_NUM VARCHAR2(40)

RELATIVE_FNO NOT NULL NUMBER

SQL> select TABLESPACE_NAME,SEGMENT_NAME,FILE_ID from dba_rollback_segs;

2）SQL> desc v$rollname

Name Null? Type

----------------------------------------- -------- ----------------------------

USN NUMBER

NAME NOT NULL VARCHAR2(30)

SQL> select * from v$rollname;

USN NAME

---------- ----------

0 SYSTEM

1 _SYSSMU1$

2 _SYSSMU2$

3 _SYSSMU3$

4 _SYSSMU4$

5 _SYSSMU5$

6 _SYSSMU6$

7 _SYSSMU7$

8 _SYSSMU8$

9 _SYSSMU9$

10 _SYSSMU10$

11 rows selected.

3）SQL> desc v$rollstat;

Name Null? Type

----------------------------------------- -------- ----------------------------

USN NUMBER

LATCH NUMBER

EXTENTS NUMBER

RSSIZE NUMBER

WRITES NUMBER

XACTS NUMBER

GETS NUMBER

WAITS NUMBER

OPTSIZE NUMBER

HWMSIZE NUMBER

SHRINKS NUMBER

WRAPS NUMBER

EXTENDS NUMBER

AVESHRINK NUMBER

AVEACTIVE NUMBER

STATUS VARCHAR2(15)

CUREXT NUMBER

CURBLK NUMBER

4）SQL> desc dba_segments

Name Null? Type

----------------------------------------- -------- ----------------------------

OWNER VARCHAR2(30)

SEGMENT_NAME VARCHAR2(81)

PARTITION_NAME VARCHAR2(30)

SEGMENT_TYPE VARCHAR2(18)

TABLESPACE_NAME VARCHAR2(30)

HEADER_FILE NUMBER

HEADER_BLOCK NUMBER

BYTES NUMBER

BLOCKS NUMBER

EXTENTS NUMBER

INITIAL_EXTENT NUMBER

NEXT_EXTENT NUMBER

MIN_EXTENTS NUMBER

MAX_EXTENTS NUMBER

PCT_INCREASE NUMBER

FREELISTS NUMBER

FREELIST_GROUPS NUMBER

RELATIVE_FNO NUMBER

BUFFER_POOL VARCHAR2(7)

5）SQL> desc v$transaction

Name Null? Type

----------------------------------------- -------- ----------------------------

ADDR RAW(4)

XIDUSN NUMBER

XIDSLOT NUMBER

XIDSQN NUMBER

UBAFIL NUMBER

UBABLK NUMBER

UBASQN NUMBER

UBAREC NUMBER

STATUS VARCHAR2(16)

START_TIME VARCHAR2(20)

START_SCNB NUMBER

START_SCNW NUMBER

START_UEXT NUMBER

START_UBAFIL NUMBER

START_UBABLK NUMBER

START_UBASQN NUMBER

START_UBAREC NUMBER

SES_ADDR RAW(4)

FLAG NUMBER

SPACE VARCHAR2(3)

RECURSIVE VARCHAR2(3)

NOUNDO VARCHAR2(3)

PTX VARCHAR2(3)

NAME VARCHAR2(256)

PRV_XIDUSN NUMBER

PRV_XIDSLT NUMBER

PRV_XIDSQN NUMBER

PTX_XIDUSN NUMBER

PTX_XIDSLT NUMBER

PTX_XIDSQN NUMBER

DSCN-B NUMBER

DSCN-W NUMBER

USED_UBLK NUMBER

USED_UREC NUMBER

LOG_IO NUMBER

PHY_IO NUMBER

CR_GET NUMBER

CR_CHANGE NUMBER

START_DATE DATE

DSCN_BASE NUMBER

DSCN_WRAP NUMBER

START_SCN NUMBER

DEPENDENT_SCN NUMBER

XID RAW(8)

PRV_XID RAW(8)

PTX_XID RAW(8)

undo表空间的应用：

1）事务的回退：事务没有结束之前才能执行。

SQL>rollback;

SQL> show user

USER is "SCOTT"

SQL> delete from emp where deptno=10;

3 rows deleted

SQL> savepoint s1;

Savepoint created.

SQL> delete from emp where deptno=20;

5 rows deleted.

SQL> savepoint s2;

Savepoint created.

SQL> delete from emp where deptno=30;

6 rows deleted.

SQL> savepoint s3;

Savepoint created.

SQL> select * from emp;

no rows selected

SQL> rollback to s2;

Rollback complete.

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM

---------- ---------- --------- ---------- --------- ---------- ----------

DEPTNO

----------

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300

7521 WARD SALESMAN 7698 22-FEB-81 1250 500

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400

7698 BLAKE MANAGER 7839 01-MAY-81 2850

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0

7900 JAMES CLERK 7698 03-DEC-81 950

6 rows selected.

SQL> rollback to s1;

Rollback complete.

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM

---------- ---------- --------- ---------- --------- ---------- ----------

DEPTNO

----------

7369 SMITH CLERK 7902 17-DEC-80 800

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300

7521 WARD SALESMAN 7698 22-FEB-81 1250 500

7566 JONES MANAGER 7839 02-APR-81 2975

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400

7698 BLAKE MANAGER 7839 01-MAY-81 2850

7788 SCOTT ANALYST 7566 19-APR-87 3000

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0

7876 ADAMS CLERK 7788 23-MAY-87 1100

7900 JAMES CLERK 7698 03-DEC-81 950

7902 FORD ANALYST 7566 03-DEC-81 3000

11 rows selected.

2）事务的提交：自动或手工结束事务。

commit或是执行ddl语句或者设置数据库自动提交。

SQL> commit;

Commit complete.

3）闪回数据：

SQL> insert into emp select * from emp as of timestamp sysdate-5/1440;

14 rows created.

undo备份：

热备：A)alter tablespace undotbs1 begin backup;

B)$cp '<undotbs1 dir>' /bak

C)alter tablespace undotbs1 end backup;

rman备份：

rman备份：back tablespace undotbs1;

[oracle@localhost ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Mar 18 09:31:40 2015

connected to target database: REZIN (DBID=633392282)

RMAN> back tablespace undotbs1;

恢复：

[oracle@localhost ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Sat Mar 21 00:28:34 2015

connected to target database: ORCL (DBID=1402521782, not open)

RMAN> restore tablespace undotbs1;

Starting restore at 21-MAR-15

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00002 to /u01/oracle/oradata/orcl/undotbs01.dbf

channel ORA_DISK_1: reading from backup piece /u01/oracle/flash_recovery_area/ORCL/backupset/2015_03_21/o1_mf_nnndf_TAG20150321T002210_bjrl9mqr_.bkp

channel ORA_DISK_1: restored backup piece 1

piece handle=/u01/oracle/flash_recovery_area/ORCL/backupset/2015_03_21/o1_mf_nnndf_TAG20150321T002210_bjrl9mqr_.bkp tag=TAG20150321T002210

channel ORA_DISK_1: restore complete, elapsed time: 00:00:07

Finished restore at 21-MAR-15

RMAN> recover tablespace undotbs1;

Starting recover at 21-MAR-15

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:02

Finished recover at 21-MAR-15

SQL> alter database datafile 2 online;

Database altered.

SQL> alter database open;

Database altered.

无备份情况下的恢复：（48集）

1）系统中是否存在其他的undo表空间，如果存在，则替换其他的undo表空间，并且将损坏的undo表空间脱机，然后启动数据库到open阶段，再删除损坏的undo表空间，然后建立新的undo表空间。

2）系统中没有其他的undo表空间，使用隐藏参数，让undo表空间offline，打开数据库，再删除损坏undo表空间，然后再建立新的undo表空间。

无备份情况下的恢复步骤：

附：dml语句执行过程图解：

users表空间管理

作用：默认的用户表空间，存放用户数据。

如何指定用户的默认表空间：

查询用户默认用户表空间：database_properties

SQL> select PROPERTY_NAME,PROPERTY_VALUE from database_properties where property_name like '%DEF%';

PROPERTY_NAME PROPERTY_VALUE

----------------------------------- ------------------------------

DEFAULT_TEMP_TABLESPACE TEMP

DEFAULT_PERMANENT_TABLESPACE USERS

DEFAULT_TBS_TYPE SMALLFILE

修改用户默认表空间：

SQL> alter database default tablespace users1;

Database altered.

结果如下：

SQL> select PROPERTY_NAME,PROPERTY_VALUE from database_properties where property_name like '%DEF%';

PROPERTY_NAME PROPERTY_VALUE

----------------------------------- ------------------------------

DEFAULT_TEMP_TABLESPACE TEMP

DEFAULT_PERMANENT_TABLESPACE USERS1

DEFAULT_TBS_TYPE SMALLFILE

users表空间备份：包括用户自定义的表孔间备份方法

热备：A)alter tablespace users begin backup;

B)$cp '<system dir>' /bak

C)alter tablespace users end backup;

rman备份：back tablespace users;

[oracle@localhost ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Mar 18 09:31:40 2015

connected to target database: REZIN (DBID=633392282)

RMAN> back tablespace users;

users表空间恢复：包括用户自定义的表孔间还原方法

1）热备还原：

SQL> ho cp /u01/oracle/users01.dbf /u01/oracle/oradata/orcl/users01.dbf

SQL> recover tablespace users;

Media recovery complete.

SQL> alter database open;

Database altered.

2）rman还原

RMAN>restore tablespace users;

RMAN>recover tablespace users;

SQL>alter tablespace users online;

3）用户自定义表空间，无备份，归档模式下，表空间创建以来日志文件全恢复方法：

alter database create datafile N as '<dir>';

SQL> alter tablespace users1 offline;

alter tablespace users1 offline

ERROR at line 1:

ORA-01116: error in opening database file 6

ORA-01110: data file 6: '/u01/oracle/oradata/orcl/user1.dbf'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

SQL> alter database datafile 6 offline;

Database altered.

SQL> alter database create datafile 6 as '/u01/oracle/oradata/orcl/user1.dbf';

Database altered.

SQL> recover tablespace users1;

Media recovery complete.

SQL> alter tablespace users1 online;

Tablespace altered.

SQL> select * from u1.u;

----------

附加：如果以上办法还是不能恢复users1表空间，则表示该表空间不能恢复，我们只能舍弃该表空间中的数据：即脱机数据文件。

SQL>alter database datafile <name> offline;

temp表空间管理：

作用：用于存放用户排序的临时数据。排序首先进行内存排序，如果内存中排序满足不了就会采取temp表空间进行排序。

如何指定用户的默认临时表空间：

查询用户默认用户表空间：database_properties

SQL> select PROPERTY_NAME,PROPERTY_VALUE from database_properties where property_name like '%DEF%';

PROPERTY_NAME PROPERTY_VALUE

----------------------------------- ------------------------------

DEFAULT_TEMP_TABLESPACE TEMP

DEFAULT_PERMANENT_TABLESPACE USERS

DEFAULT_TBS_TYPE SMALLFILE

修改用户默认表空间：

SQL> alter database default temporary tablespace temp1;

Database altered.

结果如下：

SQL> select PROPERTY_NAME,PROPERTY_VALUE from database_properties where property_name like '%DEF%';

PROPERTY_NAME PROPERTY_VALUE

----------------------------------- ------------------------------

DEFAULT_TEMP_TABLESPACE TEMP1

DEFAULT_PERMANENT_TABLESPACE USERS

DEFAULT_TBS_TYPE SMALLFILE

1建立temp表空间

SQL> create temporary tablespace temp1 tempfile '/u01/oracle/oradata/orcl/temp1.dbf' size 100m;

Tablespace created.

查询系统下的临时表空间：desc dba_temp_files、v$tempfile

SQL> desc dba_temp_files

Name Null? Type

----------------------------------------- -------- ----------------------------

FILE_NAME VARCHAR2(513)

FILE_ID NUMBER

TABLESPACE_NAME NOT NULL VARCHAR2(30)

BYTES NUMBER

BLOCKS NUMBER

STATUS CHAR(9)

RELATIVE_FNO NUMBER

AUTOEXTENSIBLE VARCHAR2(3)

MAXBYTES NUMBER

MAXBLOCKS NUMBER

INCREMENT_BY NUMBER

USER_BYTES NUMBER

USER_BLOCKS NUMBER

SQL> desc v$tempfile

Name Null? Type

----------------------------------------- -------- ----------------------------

FILE# NUMBER

CREATION_CHANGE# NUMBER

CREATION_TIME DATE

TS# NUMBER

RFILE# NUMBER

STATUS VARCHAR2(7)

ENABLED VARCHAR2(10)

BYTES NUMBER

BLOCKS NUMBER

CREATE_BYTES NUMBER

BLOCK_SIZE NUMBER

NAME VARCHAR2(513)

SQL> col TABLESPACE_NAME for a20;

SQL> col FILE_NAME for a40;

SQL> select tablespace_name,file_name from dba_temp_files;

TABLESPACE_NAME FILE_NAME

-------------------- ----------------------------------------

TEMP /u01/oracle/oradata/orcl/temp01.dbf

TEMP1 /u01/oracle/oradata/orcl/temp1.dbf

建立临时表空间组：

SQL> create temporary tablespace temp2 tempfile '/u01/oracle/oradata/orcl/temp2.dbf' size 100m tablespace group temp11;

Tablespace created.

SQL> alter tablespace temp1 tablespace group temp11;

Tablespace altered.

查询临时表空间组：dba_tablespace_groups

SQL> desc dba_tablespace_groups

Name Null? Type

----------------------------------------- -------- ----------------------------

GROUP_NAME NOT NULL VARCHAR2(30)

TABLESPACE_NAME NOT NULL VARCHAR2(30)

SQL> select * from dba_tablespace_groups;

GROUP_NAME TABLESPACE_NAME

------------------------------ --------------------

TEMP11 TEMP1

TEMP11 TEMP2

临时表空间与临时表空间组区别：

A、temp group至少具备1个临时表空间

B、如果把表空间组下面的成员删除，就相当于把临时表空间组也删除了。

C、组名字和表空间名字不能有相同的。

临时表空间组好处：

A、sql语句进行并发操作时，使用临时表空间组有好处。

B、并发操作时，会话可以分配到不同的临时表空间里去。

C、可以随时添加临时表空间成员。

2、如何指定默认的临时表空间

数据库级别：

SQL> alter database default temporary tablespace temp11;

Database altered.

用户级别：

SQL>alter user scott temporary tablespace temp11;

Database altered.

3、备份：临时表空间不需要备份。

4、恢复：建立新的临时表空间，设置默认，删除旧的。或为临时表孔间加tempfile或启用临时表空间组。

转载于:https://www.cnblogs.com/wcwen1990/p/4376908.html

你可能感兴趣的文章

JavaWeb之JSON

查看>>

HOT SUMMER 每天都是不一样，积极的去感受生活 C#关闭IE相应的窗口 .

查看>>

windows平台上编译mongdb-cxx-driver

查看>>

optionMenu-普通菜单使用

查看>>

2016-2017-2点集拓扑作业[本科生上课时]讲解视频

查看>>

GNU/Linux超级本ZaReason Ultralap 440体验

查看>>

将github上托管的代码在我的域名下运行

查看>>

【Manthan, Codefest 18 (rated, Div. 1 + Div. 2) C】Equalize

查看>>

【codeforces 767A】Snacktower

查看>>

【MemSQL Start[c]UP 3.0 - Round 1 C】 Pie Rules

struts2.x + Tiles2.x读取多个xml 配置文件

ubuntu16系统磁盘空间/dev/vda1占用满的问题