微信号:OraNews

介绍:分享数据库技术、新闻与信息,尤其是和Oracle数据库相关的内容,文章内容来自原创、专栏作者投稿或读者投稿.

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

2018-02-26 23:59 胡中豪

作者简介

胡中豪

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


本文由恩墨大讲堂147期线上分享整理而成。课程回看可点击文末“阅读原文”。


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<100;

SQL>delete from hzh1 where rownum<100;

deletefrom hzh1 where rownum<100

*

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'

SIZE 61865984  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

-- 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'

SIZE 61865984  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

-- 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

VariableSize            650118936 bytes

DatabaseBuffers          348127232 bytes

RedoBuffers               5824512 bytes

SQL>


--重建控制文件


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

SQL> /

 

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'

SIZE 61865984 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;  2

 

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’,大讲堂讲师课程资料


戳原文,回看视频课程!
 
数据和云 更多文章 时过境迁:Oracle跨平台迁移之XTTS方案与实践 专家视角 | 小荷的 Oracle Database 18c 新特性 极速体验:Oracle 18c 下载和Scalable Sequence新特性 开工大吉:Oracle 18c已经发布及新特性介绍 恩墨年货送到家-2018,寻梦一起出发
猜您喜欢 记那些离开游戏圈的小伙伴 缓存、缓存算法和缓存框架简介 大神教你玩转 SSD 系列二:基准测试环境和项目 业绩不达标库克遭降薪 卖360万美元股票“补贴生活” 深度学习及其在淘宝图像应用探讨