追本溯源：Oracle 只读表空间的探索实践

作者简介

胡中豪

云和恩墨西区交付工程师，多年一线 DBA 经验，曾服务于运营商、电网、政府行业、银行等行业客户；擅长数据库故障处理、性能优化、实施升级

1

将表空间设为只读，可以带来如下好处

1.1 减少数据库备份和恢复时间

对于只读表空间，只需要在第一备份时进行备份，在以后的备份中不需要再对备份过的只读表空间进行备份。

1.2 减少数据库启动和关闭的时间

在 shutdown immediate 关闭数据库时，会将 dirty data 同步到磁盘上，即写入 datafile，因此这个操作可能会占用较多的时间；同样在启动时也会有同步 datafile 的操作。

当表空间设置为只读后，在启动和关闭时就不会对只读表空间对应的数据文件进行处理，从而减少了数据库启动和关闭的时间。

1.3 防止对数据的误操作

只读表空间可以限制如下的操作：

Insert

Update

Delete

Truncate

Create

因为这些操作需要修改数据文件的 block，而对于只读表空间上的表字段的修改，或者删除表和索引，都是不受限制的，因为这些操作都是直接修改数据字典。

1.4 分区表的数据过期化处理

前提是分区表的每个分区都在单独的表空间上，当对应分区过期后，可以直接将该分区对应的表空间设置为只读，那么对应分区的数据也就不能被修改。注意：这里仅仅是针对修改，我们还是可以进行 DDL 操作的。

2

只读表空间数据修改测试

--创建表空间 hzh：

SQL> select file_name from dba_data_files;

FILE_NAME

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

/oracle/oradata/test/system01.dbf

/oracle/oradata/test/sysaux01.dbf

/oracle/oradata/test/undotbs01.dbf

/oracle/oradata/test/users01.dbf

SQL>create tablespace hzh datafile '/oracle/oradata/test/hzh01.dbf'size 50Mautoextend off;

Tablespacecreated.

SQL>alter tablespace hzh add datafile '/oracle/oradata/test/hzh02.dbf' size 50Mautoextend off;

Tablespacealtered.

SQL> set line 160

SQL> col tablespace_name for a15

SQL> col file_name for a50

SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME FILE_NAME

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

SYSTEM /oracle/oradata/test/system01.dbf

SYSAUX /oracle/oradata/test/sysaux01.dbf

UNDOTBS1 /oracle/oradata/test/undotbs01.dbf

USERS /oracle/oradata/test/users01.dbf

HZH /oracle/oradata/test/hzh01.dbf

HZH /oracle/oradata/test/hzh02.dbf

--创建表

SQL> create table hzh1 tablespace hzh as select * from dba_objects;

Table created.

--创建索引：

SQL> create index idx_hzh1_id on hzh1(object_id);

Index created.

--表空间 hzh 设置为只读：

SQL> alter tablespace hzh read only;

Tablespace altered.

--删除表 hzh1 上的数据：

SQL> delete from hzh1 where rownum

SQL>delete from hzh1 where rownum

deletefrom hzh1 where rownum

*

ERRORat line 1:

ORA-00372:file 6 cannot be modified at this time

ORA-01110:data file 6: '/oracle/oradata/test/hzh02.dbf'

--因为表空间是只读的，所以无法删除。

--update 表 hzh1：

SQL> update hzh1 set object_id=1 where rownum=1;

update hzh1 set object_id=1 where rownum=1

*

ERROR at line 1:

ORA-00372: file 6 cannot be modified at this time

ORA-01110: data file 6: '/oracle/oradata/test/hzh02.dbf'

--无法 update

--insert 数据：

SQL> insert into hzh1 select * from dba_objects;

insert into hzh1 select * from dba_objects

*

ERROR at line 1:

ORA-00372: file 5 cannot be modified at this time

ORA-01110: data file 5: '/oracle/oradata/test/hzh01.dbf'

--无法 truncate：

SQL> truncate table hzh1;

truncate table hzh1

*

ERROR at line 1:

ORA-00372: file 6 cannot be modified at this time

ORA-01110: data file 6: '/oracle/oradata/test/hzh02.dbf'

--同样，在只读表空间也无法进行 create table 操作，因为无法分配空间：

SQL> create table hzh2 tablespace hzh as select * from dba_objects;

create table hzh2 tablespace hzh as select * from dba_objects

*

ERROR at line 1:

ORA-01647: tablespace 'HZH' is read-only, cannot allocate space in it

但是我们可以对只读表空间上的对象做一些不涉及表空间的操作，只修改数据字典的操作是可以进行的。

--添加列：

SQL>alter table hzh1 add col1 varchar2(20);

Tablealtered.

--修改列：

SQL> alter table hzh1 modify col1 varchar2(200);

Table altered.

--drop 索引和表：

SQL> drop index idx_hzh1_id;

Index dropped.

SQL> drop table hzh1;

Table dropped.

由此，对于只读表空间上的对象，我们仅仅是不能进行修改，但是我们还是可以进行 drop，添加修改列等操作。 因为这些操作不涉及表空间的操作，仅仅是对 Oracle 数据字典的修改。

3

重建控制文件对只读表空间和临时表空间的影响

3.1 对只读表空间

假如存在一个只读的表空间，那么在重建控制文件之后，read-only 的数据文件会重命名为 MISSING00005 的格式，最后是5位数字。这个数据根据 file_id 对应，并且 datafile 也会变成 offline。

所以在重建控制文件之后，我们需要对只读文件的 datafile 进行 rename 操作，还原成原来的名称，并且修改其状态为 online。具体操作示例有说明。

在重建控制文件之前需要留意 datafile 的文件名称，如果有多个 datafile，那么就需要注意其顺序。这个需要注意一下。

3.2 TEMP 表空间

重建控制文件之后，原来的临时表空间中没有数据文件，需要单独添加。我们可以从 DBA_TABLESPACES 视图中查看到 TEMP 表空间，但是在 v$tempfile 视图中却查看不到 datafile，所以必须要手工添加 temporary datafile。

表空间 read-only 示例

--查看相关的信息

SQL> selecttablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS

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

SYSTEM ONLINE

SYSAUX ONLINE

UNDOTBS1 ONLINE

TEMP ONLINE

USERS ONLINE

HZH READ ONLY

SQL> selectfile_name,status,online_status from dba_data_files;

FILE_NAME STATUS ONLINE_

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

/oracle/oradata/test/system01.dbf AVAILABLE SYSTEM

/oracle/oradata/test/sysaux01.dbf AVAILABLE ONLINE

/oracle/oradata/test/undotbs01.dbf AVAILABLE ONLINE

/oracle/oradata/test/users01.dbf AVAILABLE ONLINE

/oracle/oradata/test/hzh01.dbf AVAILABLE ONLINE

/oracle/oradata/test/hzh02.dbf AVAILABLE ONLINE

--将表空间设置成读写

SQL> alter tablespace hzh readwrite;

Tablespace altered.

SQL> select tablespace_name,statusfrom dba_tablespaces;

TABLESPACE_NAME STATUS

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

SYSTEM ONLINE

SYSAUX ONLINE

UNDOTBS1 ONLINE

TEMP ONLINE

USERS ONLINE

HZH ONLINE

6 rows selected.

SQL> SQL> selectfile_name,status,online_status from dba_data_files;

FILE_NAME STATUS ONLINE_

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

/oracle/oradata/test/system01.dbf AVAILABLE SYSTEM

/oracle/oradata/test/sysaux01.dbf AVAILABLE ONLINE

/oracle/oradata/test/undotbs01.dbf AVAILABLE ONLINE

/oracle/oradata/test/users01.dbf AVAILABLE ONLINE

/oracle/oradata/test/hzh01.dbf AVAILABLE ONLINE

/oracle/oradata/test/hzh02.dbf AVAILABLE ONLINE

6 rows selected.

--重建控制文件测试

--先将表空间 read-only

SQL> alter tablespace hzh readonly;

Tablespace altered.

--将控制文件 dump 到 trace

SQL> oradebug setmypid

Statement processed.

SQL> alter database backupcontrolfile to trace;

Database altered.

SQL> oradebug tracefile_name

/oracle/app/oracle/diag/rdbms/test/test/trace/test_ora_30788.trc

--查看 trace 文件，取得控制文件创建的 SQL 代码

--在这个 trace 文件里对我们的影响写的很清楚：

-- Set #1. NORESETLOGS case

--

-- The following commands willcreate a new control file and use it

-- to open the database.

-- Data used by Recovery Managerwill be lost.

-- Additional logs may be requiredfor media recovery of offline

-- Use this only if the current versionsof all online logs are

-- available.

-- After mounting the createdcontrolfile, the following SQL

-- statement will place thedatabase in the appropriate

-- protection mode:

-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE"TEST" NORESETLOGS NOARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1'/oracle/oradata/test/redo01.log' SIZE50M BLOCKSIZE 512,

GROUP 2 '/oracle/oradata/test/redo02.log' SIZE 50M BLOCKSIZE 512,

GROUP 3'/oracle/oradata/test/redo03.log' SIZE50M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

'/oracle/oradata/test/system01.dbf',

'/oracle/oradata/test/sysaux01.dbf',

'/oracle/oradata/test/undotbs01.dbf',

'/oracle/oradata/test/users01.dbf'

CHARACTER SET ZHS16GBK

;

-- Commands to re-createincarnation table

-- Below log names MUST be changedto existing filenames on

-- disk. Any one log file from eachbranch can be used to

-- re-create incarnation records.

-- ALTER DATABASE REGISTER LOGFILE'/oracle/app/oracle/product/11.2.0/db_1/dbs/arch1_1_966556994.dbf';

-- Recovery is required if any ofthe datafiles are restored backups,

-- or if the last shutdown was notnormal or immediate.

RECOVER DATABASE

-- Database can now be openednormally.

ALTER DATABASE OPEN;

-- Files in read-only tablespacesare now named.

ALTER DATABASE RENAME FILE'MISSING00005'

TO'/oracle/oradata/test/hzh01.dbf';

ALTER DATABASE RENAME FILE'MISSING00006'

TO'/oracle/oradata/test/hzh02.dbf';

-- Online the files in read-onlytablespaces.

ALTER TABLESPACE "HZH"ONLINE;

-- Commands to add tempfiles totemporary tablespaces.

-- Online tempfiles have completespace information.

-- Other tempfiles may requireadjustment.

ALTER TABLESPACE TEMP ADD TEMPFILE'/oracle/oradata/test/temp01.dbf'

-- End of tempfile additions.

--

-- Set #2. RESETLOGS case

--

-- The following commands willcreate a new control file and use it

-- to open the database.

-- Data used by Recovery Managerwill be lost.

-- The contents of online logs willbe lost and all backups will

-- be invalidated. Use this only ifonline logs are damaged.

-- After mounting the createdcontrolfile, the following SQL

-- statement will place thedatabase in the appropriate

-- protection mode:

-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE"TEST" RESETLOGS NOARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1'/oracle/oradata/test/redo01.log' SIZE50M BLOCKSIZE 512,

GROUP 2'/oracle/oradata/test/redo02.log' SIZE50M BLOCKSIZE 512,

GROUP 3'/oracle/oradata/test/redo03.log' SIZE50M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

'/oracle/oradata/test/system01.dbf',

'/oracle/oradata/test/sysaux01.dbf',

'/oracle/oradata/test/undotbs01.dbf',

'/oracle/oradata/test/users01.dbf'

CHARACTER SET ZHS16GBK

;

-- Commands to re-createincarnation table

-- Below log names MUST be changedto existing filenames on

-- disk. Any one log file from eachbranch can be used to

-- re-create incarnation records.

-- ALTER DATABASE REGISTER LOGFILE'/oracle/app/oracle/product/11.2.0/db_1/dbs/arch1_1_966556994.dbf';

-- Recovery is required if any ofthe datafiles are restored backups,

-- or if the last shutdown was notnormal or immediate.

RECOVER DATABASE USING BACKUPCONTROLFILE

-- Database can now be openedzeroing the online logs.

ALTER DATABASE OPEN RESETLOGS;

-- Files in read-only tablespacesare now named.

ALTER DATABASE RENAME FILE'MISSING00005'

TO'/oracle/oradata/test/hzh01.dbf';

ALTER DATABASE RENAME FILE'MISSING00006'

TO'/oracle/oradata/test/hzh02.dbf';

-- Online the files in read-onlytablespaces.

ALTER TABLESPACE "HZH"ONLINE;

-- Commands to add tempfiles totemporary tablespaces.

-- Online tempfiles have completespace information.

-- Other tempfiles may requireadjustment.

ALTER TABLESPACE TEMP ADD TEMPFILE'/oracle/oradata/test/temp01.dbf'

-- End of tempfile additions.

--

由此 trace 文件可以确认，重建控制文件需要单独处理只读表空间和 TEMP 表空间。

3.3 使用 NORESETLOGS 模式重建控制文件

具体的操作步骤，在 trace 文件里有说明。

--DB 启动到 nomount 状态

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 1006305280bytes

FixedSize 2234600 bytes

RedoBuffers 5824512 bytes

--重建控制文件

SQL> CREATE CONTROLFILE REUSEDATABASE "TEST" NORESETLOGS NOARCHIVELOG

2 MAXLOGFILES 16

3 MAXLOGMEMBERS 3

4 MAXDATAFILES 100

5 MAXINSTANCES 8

6 MAXLOGHISTORY 292

7 LOGFILE

8 GROUP 1 '/oracle/oradata/test/redo01.log' SIZE 50M BLOCKSIZE 512,

9 GROUP 2 '/oracle/oradata/test/redo02.log' SIZE 50M BLOCKSIZE 512,

10 GROUP 3'/oracle/oradata/test/redo03.log' SIZE50M BLOCKSIZE 512

11 -- STANDBY LOGFILE

12 DATAFILE

13 '/oracle/oradata/test/system01.dbf',

14 '/oracle/oradata/test/sysaux01.dbf',

15 '/oracle/oradata/test/undotbs01.dbf',

16 '/oracle/oradata/test/users01.dbf'

17 CHARACTER SET ZHS16GBK

18 ;

Control file created.

--控制文件的位置在初始化参数里指定。

--打开数据库

SQL> alterdatabase open;

Database altered.

--查看表空间和数据文件的状态：

SQL> selecttablespace_name,status from dba_tablespaces;

TABLESPACE_NAME STATUS

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

SYSTEM ONLINE

SYSAUX ONLINE

UNDOTBS1 ONLINE

TEMP ONLINE

USERS ONLINE

HZH READ ONLY

SQL> set lines 200

FILE_ID FILE_NAME STATUS ONLINE_

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

4/oracle/oradata/test/users01.dbf AVAILABLE ONLINE

3 /oracle/oradata/test/undotbs01.dbf AVAILABLEONLINE

2/oracle/oradata/test/sysaux01.dbf AVAILABLEONLINE

1/oracle/oradata/test/system01.dbf AVAILABLESYSTEM

5 /oracle/app/oracle/product/11.2.0/db_1/dbs/MISSING00005 AVAILABLE OFFLINE

6/oracle/app/oracle/product/11.2.0/db_1/dbs/MISSING00006 AVAILABLE OFFLINE

6 rows selected.

--注意这里，我们之前 read only 的 hzh 表空间还是 read only 的，但是其对应的数据文件名称发生了改变，变成了 MISSING00005，最后是5位数字，这个数据根据 file_id 对应。

同时注意这里的 datafile 状态变成了 offline。

--如果我们现在直接 online 或者 read write 表空间，都会报错：

SQL> alter tablespace hzh online;

alter tablespace hzh online

*

ERROR at line 1:

ORA-01157: cannot identify/lock data file 5 - seeDBWR trace file

ORA-01111: name for data file 5 is unknown - renameto correct file

ORA-01110: data file 5:'/oracle/app/oracle/product/11.2.0/db_1/dbs/MISSING00005'

SQL> alter tablespace hzh read write;

alter tablespace hzh read write

*

ERROR at line 1:

ORA-01135: file 5 accessed for DML/query is offline

ORA-01111: name for data file 5 is unknown - renameto correct file

ORA-01110: data file 5:'/oracle/app/oracle/product/11.2.0/db_1/dbs/MISSING00005'

也是提示我们需要对文件进行 rename。

--现在对2个 datafile 进行 rename 操作：

SQL> ALTER DATABASE RENAME FILE'MISSING00005'

2 TO '/oracle/oradata/test/hzh01.dbf';

ALTER DATABASE RENAME FILE'MISSING00006'

Database altered.

SQL> 2 TO '/oracle/oradata/test/hzh02.dbf';

Database altered.

--再将表空间 online：

SQL> alter tablespace hzh read write;

alter tablespace hzh read write

*

ERROR at line 1:

ORA-01135: file 5 accessed forDML/query is offline

ORA-01110: data file 5:'/oracle/oradata/test/hzh01.dbf'

因为之前的 datafile 是 offline 的，所以我们直接修改表空间模式时，提示我们要访问的 datafile 是 offline 的。

我们使用命令将表空间下的所有 datafile online，

SQL> alter tablespace hzhonline;

Tablespace altered.

也可以使用 alter database datafile 命令分次处理单个的 datafile。

现在我们就可以看到数据文件已经正常了：

SQL> select file_name,status,online_statusfrom dba_data_files;

FILE_NAME STATUS ONLINE_

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

/oracle/oradata/test/users01.dbf AVAILABLE ONLINE

/oracle/oradata/test/undotbs01.dbf AVAILABLEONLINE

/oracle/oradata/test/sysaux01.dbf AVAILABLEONLINE

/oracle/oradata/test/system01.dbf AVAILABLESYSTEM

/oracle/oradata/test/hzh01.dbf AVAILABLE ONLINE

/oracle/oradata/test/hzh02.dbf AVAILABLE ONLINE

6 rows selected.

但是我们的表空间还是 read only 的状态：

SQL> select tablespace_name,status fromdba_tablespaces;

TABLESPACE_NAME STATUS

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

SYSTEM ONLINE

SYSAUX ONLINE

UNDOTBS1 ONLINE

TEMP ONLINE

USERS ONLINE

HZH READ ONLY

--但是要注意，我们重建控制文件之后还需要添加 Temp 表空间。虽然从 dba_tablespaces 视图里可以查看到 TEMP 表空间，但是该表空间确实没有数据文件的，我们需要单独添加。

SQL> select * from v$tempfile;

no rows selected

--所以为了系统的正常运行，需要给 TEMP 表空间添加数据文件：

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE'/oracle/oradata/test/temp01.dbf'

Tablespace altered.

再次查询就 OK 了：

SQL> select name from v$tempfile;

NAME

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

/oracle/oradata/test/temp01.dbf

至此，我们重建控制文件的操作就算全部完成。

The End.

资源下载

关注公众号：数据和云（OraNews）回复关键字获取

‘2017DTC’，2017 DTC 大会 PPT

‘DBALIFE’，“DBA 的一天”海报

‘DBA04’，DBA 手记4 经典篇章电子书

‘RACV1’,RAC 系列课程视频及ppt

‘122ARCH’，Oracle 12.2 体系结构图

‘2017OOW’，Oracle OpenWorld 资料

‘PRELECTION’，大讲堂讲师课程资料