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

作者简介

胡中豪

云和恩墨西区交付工程师,多年一线 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’,大讲堂讲师课程资料

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2018-02-26

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

深度解析dba_segments和sys.seg$中的细节差异(上) (r5笔记第27天)

今天在查看系统空间使用情况的时候,发现一个细节的问题,自己死磕了一把,还是发现了不少有价值的东西。 事情的起因是我在使用脚本在某个环境中查看每个用户所占有的空间...

36280
来自专栏杨建荣的学习笔记

生产环境sql语句调优实战第六篇(r2笔记91天)

生产环境中有大量的sql语句在运行,尽管有awr,ash做数据的收集统计,但是dba的调优工作大多数情况都是在问题已经发生后做排查的,有些sql语句可能执行的时...

28040
来自专栏杨建荣的学习笔记

SQL*Loader-805的解决(r2笔记36天)

使用sql*loader是大型项目中数据迁移的利器。如果是外部系统,其他数据库到oracle的数据迁移,使用文本式文件是最兼容的方式。 sqlldr的加载效率是...

32180
来自专栏杨建荣的学习笔记

ORACLE dataguard学习笔记 (91天)

今天在一台机器上模拟了dataguard,主备两个实例从物理上不共享任何归档文件路径。 主要有以下内容: dataguard Physical standby的...

312110
来自专栏deed博客

Oracle在Linux下安装

26620
来自专栏杨建荣的学习笔记

执行计划的偏差导致的性能问题(r3笔记第12天)

在生产环境中有一条sql语句,查看执行计划来看,效果还是可以接受的。 sql语句类似下面的样子,可以看到里面还使用了比较纠结的外连接。从执行计划来说,默认是走n...

27440
来自专栏杨建荣的学习笔记

关于ORA-01555的问题分析(r5笔记第87天)

今天开发的同事发给我一个问题,在运行某一个Job的时候抛出了ORA错误,希望我们看看从数据库层面能不能发现什么。 错误日志如下: Function: Entit...

31660
来自专栏沃趣科技

应用示例荟萃 | performance_schema全方位介绍(上)

经过前面6个篇幅的学习,相信大家对什么是performance_schema,已经初步形成了一个整体认识,但我想很多同行看完之前的文章之后可能还是一脸懵逼,今天...

26230
来自专栏杨建荣的学习笔记

物化视图相关的性能改进 (r7笔记第58天)

今天早上开发的一个同事找到我说他早上做了一个统计查询,但是感觉速度很慢,已经过了一个小时了还没有反应。想让我看看是什么情况。 我通过v$session查到有一个...

34150
来自专栏杨建荣的学习笔记

海量数据迁移之外部表并行抽取(99天)

在10g开始的新特性中,外部表是一个不容忽视的好工具。对于大型项目中海量数据使用sqlloader是一种全新的方式,不过很明显,sqlloader的可扩展性更强...

38150

扫码关注云+社区

领取腾讯云代金券