云和恩墨西区交付工程师,多年一线 DBA 经验,曾服务于运营商、电网、政府行业、银行等行业客户;擅长数据库故障处理、性能优化、实施升级
本文由恩墨大讲堂147期线上分享整理而成。课程回看可点击文末“阅读原文”。 1.1 减少数据库备份和恢复时间 对于只读表空间,只需要在第一备份时进行备份,在以后的备份中不需要再对备份过的只读表空间进行备份。 1.2 减少数据库启动和关闭的时间 在 shutdown immediate 关闭数据库时,会将 dirty data 同步到磁盘上,即写入 datafile,因此这个操作可能会占用较多的时间;同样在启动时也会有同步 datafile 的操作。 当表空间设置为只读后,在启动和关闭时就不会对只读表空间对应的数据文件进行处理,从而减少了数据库启动和关闭的时间。 1.3 防止对数据的误操作 只读表空间可以限制如下的操作: Insert Update Delete Truncate Create
因为这些操作需要修改数据文件的 block,而对于只读表空间上的表字段的修改,或者删除表和索引,都是不受限制的,因为这些操作都是直接修改数据字典。 1.4 分区表的数据过期化处理 前提是分区表的每个分区都在单独的表空间上,当对应分区过期后,可以直接将该分区对应的表空间设置为只读,那么对应分区的数据也就不能被修改。注意:这里仅仅是针对修改,我们还是可以进行 DDL 操作的。 --创建表空间 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.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-onlySQL> alter tablespace hzh readonly; Tablespace altered.
--将控制文件 dump 到 traceSQL> 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.
|