用实验来说话。

Microsoft Windows [版本 6.1.7601]

C:\Users\LIUBINGLIN>

C:\Users\LIUBINGLIN>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on 星期一 12月 10 21:54:36 2012

连接到:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

SQL> select * from v$version;

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

PL/SQL Release 11.2.0.3.0 - Production

CORE 11.2.0.3.0 Production

TNS for 32-bit Windows: Version 11.2.0.3.0 - Production

NLSRTL Version 11.2.0.3.0 - Production

SQL> archive log list;

数据库日志模式非存档模式

自动存档禁用

存档终点 F:\app\oracle\product\11.2.0\dbhome_1\RDBMS

最早的联机日志序列 27

当前日志序列 28

SQL> select file_id,tablespace_name,file_name from dba_data_files;

FILE_ID TABLESPACE_NAME

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

FILE_NAME

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

5 CACHETBLSP

F:\APP\ORACLE\ORADATA\ORCL\DATFTTUSER.DBF

4 USERS

F:\APP\ORACLE\ORADATA\ORCL\USERS.DBF

3 UNDOTBS1

F:\APP\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF

FILE_ID TABLESPACE_NAME

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

FILE_NAME

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

2 SYSAUX

F:\APP\ORACLE\ORADATA\ORCL\SYSAUX01.DBF

1 SYSTEM

F:\APP\ORACLE\ORADATA\ORCL\SYSTEM01.DBF

6 TEST

F:\APP\ORACLE\ORADATA\ORCL\TEST02.DBF

FILE_ID TABLESPACE_NAME

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

FILE_NAME

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

7 TEST

F:\APP\ORACLE\ORADATA\ORCL\TEST03.DBF

已选择7行。

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#

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

2018006

SQL> select checkpoint_change# from v$datafile;

CHECKPOINT_CHANGE#

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

2018006

已选择7行。

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS

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

SYSTEM ONLINE

SYSAUX ONLINE

UNDOTBS1 ONLINE

TEMP ONLINE

USERS ONLINE

CACHETBLSP ONLINE

TEST ONLINE

TEMP01 ONLINE

已选择8行。

SQL> alter tablespace test read only;

表空间已更改。

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS

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

SYSTEM ONLINE

SYSAUX ONLINE

UNDOTBS1 ONLINE

TEMP ONLINE

USERS ONLINE

CACHETBLSP ONLINE

TEST READ ONLY

TEMP01 ONLINE

已选择8行。

SQL> select checkpoint_change# from v$datafile;

CHECKPOINT_CHANGE#

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

2018006

2018401

已选择7行。

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#

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

2018006

SQL> alter system checkpoint;

系统已更改。

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#

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

2018420

SQL> select checkpoint_change# from v$datafile;

CHECKPOINT_CHANGE#

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

2018420

2018401

已选择7行。

SQL> alter tablespace test read write;

表空间已更改。

SQL> select checkpoint_change# from v$datafile;

CHECKPOINT_CHANGE#

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

2018420

2018438

已选择7行。

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#

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

2018420

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS

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

SYSTEM ONLINE

SYSAUX ONLINE

UNDOTBS1 ONLINE

TEMP ONLINE

USERS ONLINE

CACHETBLSP ONLINE

TEST ONLINE

TEMP01 ONLINE

已选择8行。

SQL> alter system checkpoint;

系统已更改。

SQL> select checkpoint_change# from v$datafile;

CHECKPOINT_CHANGE#

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

2018472

已选择7行。

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#

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

2018472

SQL> alter tablespace test offline normal;

表空间已更改。

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS

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

SYSTEM ONLINE

SYSAUX ONLINE

UNDOTBS1 ONLINE

TEMP ONLINE

USERS ONLINE

CACHETBLSP ONLINE

TEST OFFLINE

TEMP01 ONLINE

已选择8行。

SQL> select checkpoint_change# from v$datafile;

CHECKPOINT_CHANGE#

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

2018472

2018491

已选择7行。

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#

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

2018472

SQL> alter system checkpoint;

系统已更改。

SQL> select checkpoint_change# from v$datafile;

CHECKPOINT_CHANGE#

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

2018513

2018491

已选择7行。

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#

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

2018513

SQL> alter tablespace test online;

表空间已更改。

SQL> select checkpoint_change# from v$datafile;

CHECKPOINT_CHANGE#

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

2018513

2018523

已选择7行。

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#

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

2018513

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS

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

SYSTEM ONLINE

SYSAUX ONLINE

UNDOTBS1 ONLINE

TEMP ONLINE

USERS ONLINE

CACHETBLSP ONLINE

TEST ONLINE

TEMP01 ONLINE

已选择8行。

SQL> alter tablespace test offline immediate;

alter tablespace test offline immediate

*

第 1 行出现错误:

ORA-01145: 除非启用了介质恢复, 否则不允许立即脱机

SQL> alter database datafile 6 offline;

alter database datafile 6 offline

*

第 1 行出现错误:

ORA-01145: 除非启用了介质恢复, 否则不允许立即脱机

SQL> shutdown immediate

数据库已经关闭。

已经卸载数据库。

ORACLE 例程已经关闭。

SQL> startup mount

ORACLE 例程已经启动。

Total System Global Area 267825152 bytes

Fixed Size 1384044 bytes

Variable Size 96469396 bytes

Database Buffers 163577856 bytes

Redo Buffers 6393856 bytes

数据库装载完毕。

SQL> alter database archivelog;

数据库已更改。

SQL> alter database open;

数据库已更改。

SQL> select checkpoint_change# from v$datafile;

CHECKPOINT_CHANGE#

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

2018740

已选择7行。

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#

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

2018740

SQL> alter tablespace test offline immediate;

表空间已更改。

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS

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

SYSTEM ONLINE

SYSAUX ONLINE

UNDOTBS1 ONLINE

TEMP ONLINE

USERS ONLINE

CACHETBLSP ONLINE

TEST OFFLINE

TEMP01 ONLINE

已选择8行。

SQL> select checkpoint_change# from v$datafile;

CHECKPOINT_CHANGE#

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

2018740

已选择7行。

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#

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

2018740

SQL> alter tablespace test online;

alter tablespace test online

*

第 1 行出现错误:

ORA-01113: 文件 6 需要介质恢复

ORA-01110: 数据文件 6: 'F:\APP\ORACLE\ORADATA\ORCL\TEST02.DBF'

SQL> recover datafile 6;

完成介质恢复。

SQL> recover datafile 7;

完成介质恢复。

SQL> select checkpoint_change# from v$datafile;

CHECKPOINT_CHANGE#

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

2018740

2019053

2019056

已选择7行。

SQL> select checkpoint_change# from v$database;

CHECKPOINT_CHANGE#

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

2018740

SQL> alter tablespace test online;

表空间已更改。

SQL> alter system checkpoint;

系统已更改。

SQL> select checkpoint_change# from v$datafile;

CHECKPOINT_CHANGE#

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

2019226

已选择7行。

SQL> alter database datafile 6 offline;

数据库已更改。

SQL> select checkpoint_change# from v$datafile;

CHECKPOINT_CHANGE#

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

2019226

已选择7行。

SQL> alter database datafile 6 online;

alter database datafile 6 online

*

第 1 行出现错误:

ORA-01113: 文件 6 需要介质恢复

ORA-01110: 数据文件 6: 'F:\APP\ORACLE\ORADATA\ORCL\TEST02.D

SQL> recover datafile 6;

完成介质恢复。

SQL> select checkpoint_change# from v$datafile;

CHECKPOINT_CHANGE#

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

2019226

2019232

2019226

已选择7行。

SQL> alter database datafile 6 online;

数据库已更改。

SQL> select checkpoint_change# from v$datafile;

CHECKPOINT_CHANGE#

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

2019226

2019257

2019226

已选择7行。

从以上的实验不难看出如下几点：

1）.执行表空间的READ ONLY和OFFLINE [NORMAL]操作，Oracle都会对表空间对应的数据文件执行检查点操作。之后表空间对应的所有数据文件检查点SCN都不会发生变化。直到执行表空间的READ WRITE和ONLINE操作又会对表空间对应的所有数据文件执行一次检查点操作。对表空间的READ WRITE和OFFLINE NORMAL后的ONLINE都不需要介质恢复即可使表空间ONLINE。

2）.对表空间做OFFLINE IMMEDIATE或对数据文件做OFFLINE操作都需要数据库在归档模式下，原因就在于执行这些操作之后要使得表空间重新ONLINE必须执行介质恢复，如果介质恢复需要的Redo日志丢失，那么表空间将无法重新ONLINE。这行表空间的OFFLINE IMMEDIATE和数据文件的OFFLINE操作，Oracle不会对相应的数据文件做检查点操作。但是在recover介质恢复和表空间和数据文件ONLINE操作都会对相应的数据文件执行检查点操作。

3）.在对表空间做READ ONLY操作的时候，如果有事务在操作表空间中的数据，那么READ ONLY操作将处于等待状态，直到所有的事务结束。

4）.TABLESPACE OFFLINE TEMPORARY操作也会对表空间对应的所有数据文件执行检查点操作。

5）.检查点可能只发生在单个数据文件上。对数据库多个表空间批量READ ONLY操作必定导致数据文件上的检查点SCN各不相同：

List of Backup Sets

===================

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

4128 Full 4.07G DISK 00:00:00 2012-12-10 13:35:56

BP Key: 4132 Status: AVAILABLE Compressed: YES Tag: TAG20121210T133555

Piece Name: /u01/app/oracle/rman_backup/ORCL_1_4456_ORCL_1257818738.RMAN

List of Datafiles in backup set 4128

File LV Type Ckp SCN Ckp Time Name

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

1 Full 338405877 2012-12-10 13:35:56 Z:\ORACLE\DATA\ORCL\SYSTEM01.DBF

4 Full 338405877 2012-12-10 13:35:56 Z:\ORACLE\DATA\ORCL\USERS01.DBF

5 Full 337849040 2012-11-28 00:58:41 Z:\ORACLE\DATA\ORCL\APACCT.DBF

9 Full 337849076 2012-11-28 00:58:49 Z:\ORACLE\DATA\ORCL\APECONSTAT.DBF

10 Full 337849085 2012-11-28 00:58:50 Z:\ORACLE\DATA\ORCL\APEQUIPMENT.DBF

12 Full 337849103 2012-11-28 00:58:54 Z:\ORACLE\DATA\ORCL\APINFECT.DBF

18 Full 337849157 2012-11-28 00:59:06 Z:\ORACLE\DATA\ORCL\APMEDADM.DBF

21 Full 337849185 2012-11-28 00:59:11 Z:\ORACLE\DATA\ORCL\APMEDREC.DBF

22 Full 337849194 2012-11-28 00:59:13 Z:\ORACLE\DATA\ORCL\APNURSING.DBF

23 Full 337849203 2012-11-28 00:59:15 Z:\ORACLE\DATA\ORCL\APOA.DBF

25 Full 337849221 2012-11-28 00:59:19 Z:\ORACLE\DATA\ORCL\APOUTPADM.DBF

26 Full 337849232 2012-11-28 00:59:21 Z:\ORACLE\DATA\ORCL\APOUTPBILL.DBF

29 Full 337849269 2012-11-28 00:59:27 Z:\ORACLE\DATA\ORCL\APPHARMACY.DBF

31 Full 337849287 2012-11-28 00:59:30 Z:\ORACLE\DATA\ORCL\APSURGERY.DBF

32 Full 337849296 2012-11-28 00:59:32 Z:\ORACLE\DATA\ORCL\APAPPM.DBF

33 Full 337849305 2012-11-28 00:59:34 Z:\ORACLE\DATA\ORCL\APCLINICGUIDE.DBF

34 Full 337849314 2012-11-28 00:59:36 Z:\ORACLE\DATA\ORCL\APINQUIRE.DBF

36 Full 337849332 2012-11-28 00:59:41 Z:\ORACLE\DATA\ORCL\APPEPIC.DBF

BS Key Type LV Size Device Type Elapsed Time Completion Time

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

4129 Full 3.45G DISK 00:00:00 2012-12-10 13:35:56

BP Key: 4133 Status: AVAILABLE Compressed: YES Tag: TAG20121210T133555

Piece Name: /u01/app/oracle/rman_backup/ORCL_1_4457_ORCL_1257818738.RMAN

List of Datafiles in backup set 4129

File LV Type Ckp SCN Ckp Time Name

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

2 Full 338405878 2012-12-10 13:35:56 Z:\ORACLE\DATA\ORCL\SYSAUX01.DBF

3 Full 338405878 2012-12-10 13:35:56 Z:\ORACLE\DATA\ORCL\UNDOTBS01.DBF

6 Full 337849049 2012-11-28 00:58:43 Z:\ORACLE\DATA\ORCL\APBLDBANK.DBF

7 Full 337849058 2012-11-28 00:58:45 Z:\ORACLE\DATA\ORCL\APCOMM.DBF

8 Full 337849067 2012-11-28 00:58:47 Z:\ORACLE\DATA\ORCL\APCPR.DBF

11 Full 337849094 2012-11-28 00:58:52 Z:\ORACLE\DATA\ORCL\APEXAM.DBF

13 Full 337849112 2012-11-28 00:58:56 Z:\ORACLE\DATA\ORCL\APINPADM.DBF

14 Full 337849121 2012-11-28 00:58:58 Z:\ORACLE\DATA\ORCL\APINPBILL.DBF

15 Full 337849130 2012-11-28 00:59:00 Z:\ORACLE\DATA\ORCL\APINSURANCE.DBF

16 Full 337849139 2012-11-28 00:59:02 Z:\ORACLE\DATA\ORCL\APLAB.DBF

17 Full 337849148 2012-11-28 00:59:04 Z:\ORACLE\DATA\ORCL\APLIS.DBF

19 Full 337849167 2012-11-28 00:59:07 Z:\ORACLE\DATA\ORCL\APMEDICOM_DIF.DBF

20 Full 337849176 2012-11-28 00:59:09 Z:\ORACLE\DATA\ORCL\APMEDICOM_SAVE.DBF

24 Full 337849212 2012-11-28 00:59:17 Z:\ORACLE\DATA\ORCL\APORDADM.DBF

27 Full 337849251 2012-11-28 00:59:23 Z:\ORACLE\DATA\ORCL\APPACS.DBF

28 Full 337849260 2012-11-28 00:59:25 Z:\ORACLE\DATA\ORCL\APPE.DBF

30 Full 337849278 2012-11-28 00:59:28 Z:\ORACLE\DATA\ORCL\APPICTURE.DBF

35 Full 337849323 2012-11-28 00:59:38 Z:\ORACLE\DATA\ORCL\APMOTES.DBF

--end--

相关文章：

《数据文件、表空间offline用法及区别》：http://space.itpub.net/23135684/viewspace-705015

ITPub博客

Oracle数据库表空间READ ONLY、READ WRITE、ONLINE、OFFLINE状态对应CHECKPOINT的变化

最新文章