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

 

有关控制文件之前整理过一篇Blog，参考：

Oracle 控制文件

http://www.cndba.cn/Dave/article/1216

 

这里主要是说明一下重建控制文件2个注意的地方：

1.     对只读表空间

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

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

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

 

2.     TEMP 表空间

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

 

 

一.表空间read-only示例

--查看相关的信息

SQL> select tablespace_name,status from dba_tablespaces;

 

TABLESPACE_NAME                STATUS

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

SYSTEM                         ONLINE

SYSAUX                         ONLINE

UNDOTBS1                       ONLINE

TEMP                           ONLINE

USERS                          ONLINE

 

SQL> select file_name from dba_data_files;

 

FILE_NAME

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

/u01/app/oracle/oradata/gg2/users01.dbf

/u01/app/oracle/oradata/gg2/undotbs01.dbf

/u01/app/oracle/oradata/gg2/sysaux01.dbf

/u01/app/oracle/oradata/gg2/system01.dbf

 

--创建表空间

SQL> create tablespace dave datafile'/u01/app/oracle/oradata/gg2/dave01.dbf' size 20M autoextendoff,'/u01/app/oracle/oradata/gg2/dave02.dbf' size 20M autoextend off;

Tablespace created.

 

SQL> select tablespace_name,status from dba_tablespaces;

 

TABLESPACE_NAME                STATUS

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

SYSTEM                         ONLINE

SYSAUX                         ONLINE

UNDOTBS1                       ONLINE

TEMP                           ONLINE

USERS                          ONLINE

DAVE                           ONLINE

 

6 rows selected.

 

SQL> col file_name for a43

SQL> selectfile_name,status,online_status from dba_data_files;

 

FILE_NAME                                   STATUS    ONLINE_

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

/u01/app/oracle/oradata/gg2/users01.dbf     AVAILABLE ONLINE

/u01/app/oracle/oradata/gg2/undotbs01.dbf   AVAILABLE ONLINE

/u01/app/oracle/oradata/gg2/sysaux01.dbf    AVAILABLE ONLINE

/u01/app/oracle/oradata/gg2/system01.dbf    AVAILABLE SYSTEM

/u01/app/oracle/oradata/gg2/dave01.dbf      AVAILABLE ONLINE

/u01/app/oracle/oradata/gg2/dave02.dbf      AVAILABLE ONLINE

 

6 rows selected.

 

--将表空间dave设置成只读

SQL> alter tablespace dave read only;

Tablespace altered.

 

SQL> select tablespace_name,status from dba_tablespaces;

 

TABLESPACE_NAME                STATUS

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

SYSTEM                         ONLINE

SYSAUX                         ONLINE

UNDOTBS1                       ONLINE

TEMP                           ONLINE

USERS                          ONLINE

DAVE                           READ ONLY

 

6 rows selected.

 

SQL> selectfile_name,status,online_status from dba_data_files;

 

FILE_NAME                                   STATUS    ONLINE_

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

/u01/app/oracle/oradata/gg2/users01.dbf     AVAILABLE ONLINE

/u01/app/oracle/oradata/gg2/undotbs01.dbf   AVAILABLE ONLINE

/u01/app/oracle/oradata/gg2/sysaux01.dbf    AVAILABLE ONLINE

/u01/app/oracle/oradata/gg2/system01.dbf    AVAILABLE SYSTEM

/u01/app/oracle/oradata/gg2/dave01.dbf      AVAILABLE ONLINE

/u01/app/oracle/oradata/gg2/dave02.dbf      AVAILABLE ONLINE

 

6 rows selected.

 

--将表空间设置成读写

SQL> alter tablespace dave read write;

Tablespace altered.

 

SQL> select tablespace_name,status from dba_tablespaces;

 

TABLESPACE_NAME                STATUS

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

SYSTEM                         ONLINE

SYSAUX                         ONLINE

UNDOTBS1                       ONLINE

TEMP                           ONLINE

USERS                         ONLINE

DAVE                           ONLINE

 

6 rows selected.

 

SQL> selectfile_name,status,online_status from dba_data_files;

 

FILE_NAME                                   STATUS    ONLINE_

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

/u01/app/oracle/oradata/gg2/users01.dbf     AVAILABLE ONLINE

/u01/app/oracle/oradata/gg2/undotbs01.dbf   AVAILABLE ONLINE

/u01/app/oracle/oradata/gg2/sysaux01.dbf    AVAILABLE ONLINE

/u01/app/oracle/oradata/gg2/system01.dbf    AVAILABLE SYSTEM

/u01/app/oracle/oradata/gg2/dave01.dbf      AVAILABLE ONLINE

/u01/app/oracle/oradata/gg2/dave02.dbf      AVAILABLE ONLINE

 

6 rows selected.

 

二. 重建控制文件 测试

 

2.1 先将表空间read-only

SQL> alter tablespace dave read only;

Tablespace altered.

 

 

2.2 将控制文件dump到trace

SQL> oradebug setmypid

Statement processed.

SQL> alter database backup controlfileto trace;

 

Database altered.

 

SQL> oradebug tracefile_name

/u01/app/oracle/diag/rdbms/gg2/gg2/trace/gg2_ora_3700.trc

 

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

 

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

gg3:/u01> cat /u01/app/oracle/diag/rdbms/gg2/gg2/trace/gg2_ora_3700.trc

Trace file/u01/app/oracle/diag/rdbms/gg2/gg2/trace/gg2_ora_3700.trc

Oracle Database 11g Enterprise EditionRelease 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Miningand Real Application Testing options

ORACLE_HOME =/u01/app/oracle/product/11.2.0.3/db_1

System name:    Linux

Node name:      gg3

Release:        2.6.18-164.el5xen

Version:        #1 SMP Tue Aug 18 15:59:52 EDT 2009

Machine:        x86_64

VM name:        Xen Version: 3.1 (PVM)

Instance name: gg2

Redo thread mounted by this instance: 1

Oracle process number: 19

Unix process pid: 3700, image: oracle@gg3(TNS V1-V3)

 

…

 

*** 2012-02-03 14:15:26.571

-- The following are current System-scopeREDO Log Archival related

-- parameters and can be included in thedatabase initialization file.

--

-- LOG_ARCHIVE_DEST=''

-- LOG_ARCHIVE_DUPLEX_DEST=''

--

-- LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf

--

-- DB_UNIQUE_NAME="gg2"

--

-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE,NODG_CONFIG'

-- LOG_ARCHIVE_MAX_PROCESSES=4

-- STANDBY_FILE_MANAGEMENT=MANUAL

-- STANDBY_ARCHIVE_DEST=?/dbs/arch

-- FAL_CLIENT=''

-- FAL_SERVER=''

--

--LOG_ARCHIVE_DEST_1='LOCATION=/u01/archive'

-- LOG_ARCHIVE_DEST_1='OPTIONAL REOPEN=300NODELAY'

-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRMNOEXPEDITE NOVERIFY SYNC'

-- LOG_ARCHIVE_DEST_1='REGISTER NOALTERNATENODEPENDENCY'

-- LOG_ARCHIVE_DEST_1='NOMAX_FAILURENOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'

-- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'

-- LOG_ARCHIVE_DEST_STATE_1=ENABLE

--

-- Below are two sets of SQL statements,each of which creates a new

-- control file and uses it to open thedatabase. The first set opens

-- the database with the NORESETLOGS optionand should be used only if

-- the current versions of all online logsare available. The second

-- set opens the database with theRESETLOGS option and should be used

-- if online logs are unavailable.

-- The appropriate set of statements can becopied from the trace into

-- a script file, edited as necessary, andexecuted when there is a

-- need to re-create the control file.

--

--     Set #1. NORESETLOGS case

--以NORESETLOGS模式创建控制文件的详细步骤

--

-- The following commands will create a newcontrol file and use it

-- to open the database.

-- Data used by Recovery Manager will belost.

-- Additional logs may be required formedia recovery of offline

-- Use this only if the current versions ofall online logs are

-- available.

-- After mounting the created controlfile,the following SQL

-- statement will place the database in theappropriate

-- protection mode:

-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE"GG2" NORESETLOGS FORCE LOGGING ARCHIVELOG

    MAXLOGFILES16

   MAXLOGMEMBERS 3

   MAXDATAFILES 100

   MAXINSTANCES 8

   MAXLOGHISTORY 292

LOGFILE

 GROUP 1 '/u01/app/oracle/oradata/gg2/redo01.log'  SIZE 50M BLOCKSIZE 512,

 GROUP 2 '/u01/app/oracle/oradata/gg2/redo02.log'  SIZE 50M BLOCKSIZE 512,

 GROUP 3 '/u01/app/oracle/oradata/gg2/redo03.log'  SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

 '/u01/app/oracle/oradata/gg2/system01.dbf',

 '/u01/app/oracle/oradata/gg2/sysaux01.dbf',

 '/u01/app/oracle/oradata/gg2/undotbs01.dbf',

 '/u01/app/oracle/oradata/gg2/users01.dbf'

CHARACTER SET ZHS16GBK

;

-- Commands to re-create incarnation table

-- Below log names MUST be changed toexisting filenames on

-- disk. Any one log file from each branchcan be used to

-- re-create incarnation records.

-- ALTER DATABASE REGISTER LOGFILE'/u01/archive/1_1_762083164.dbf';

-- ALTER DATABASE REGISTER LOGFILE'/u01/archive/1_1_765219083.dbf';

-- Recovery is required if any of thedatafiles are restored backups,

-- or if the last shutdown was not normalor immediate.

RECOVER DATABASE

-- Set Database Guard and/or SupplementalLogging

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

-- All logs need archiving and a log switchis needed.

ALTER SYSTEM ARCHIVE LOG ALL;

-- Database can now be opened normally.

ALTER DATABASE OPEN;

-- Files in read-only tablespaces are nownamed.

--注意这里，DBOPEN 之后，之前dave 表空间的数据文件名称发生了变化，我们需要重新对其进行rename，然后才可以onliedave 表空间。

ALTER DATABASE RENAME FILE 'MISSING00005'

  TO'/u01/app/oracle/oradata/gg2/dave01.dbf';

ALTER DATABASE RENAME FILE 'MISSING00006'

  TO'/u01/app/oracle/oradata/gg2/dave02.dbf';

-- Online the files in read-onlytablespaces.

ALTER TABLESPACE "DAVE" ONLINE;

-- Commands to add tempfiles to temporarytablespaces.

-- Online tempfiles have complete spaceinformation.

-- Other tempfiles may require adjustment.

ALTER TABLESPACE TEMP ADD TEMPFILE'/u01/app/oracle/oradata/gg2/temp01.dbf'

    SIZE 20971520  REUSE AUTOEXTEND ONNEXT 655360  MAXSIZE 32767M;

-- End of tempfile additions.

--

--    Set #2. RESETLOGS case

--以RESETLOGS模式重建控制文件的详细步骤

--

-- The following commands will create a newcontrol file and use it

-- to open the database.

-- Data used by Recovery Manager will belost.

-- The contents of online logs will be lostand all backups will

-- be invalidated. Use this only if onlinelogs are damaged.

-- After mounting the created controlfile,the following SQL

-- statement will place the database in theappropriate

-- protection mode:

-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE"GG2" RESETLOGS FORCE LOGGING ARCHIVELOG

   MAXLOGFILES 16

   MAXLOGMEMBERS 3

   MAXDATAFILES 100

   MAXINSTANCES 8

   MAXLOGHISTORY 292

LOGFILE

 GROUP 1 '/u01/app/oracle/oradata/gg2/redo01.log'  SIZE 50M BLOCKSIZE 512,

 GROUP 2 '/u01/app/oracle/oradata/gg2/redo02.log'  SIZE 50M BLOCKSIZE 512,

 GROUP 3 '/u01/app/oracle/oradata/gg2/redo03.log'  SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

 '/u01/app/oracle/oradata/gg2/system01.dbf',

 '/u01/app/oracle/oradata/gg2/sysaux01.dbf',

 '/u01/app/oracle/oradata/gg2/undotbs01.dbf',

 '/u01/app/oracle/oradata/gg2/users01.dbf'

CHARACTER SET ZHS16GBK

;

-- Commands to re-create incarnation table

-- Below log names MUST be changed toexisting filenames on

-- disk. Any one log file from each branchcan be used to

-- re-create incarnation records.

-- ALTER DATABASE REGISTER LOGFILE'/u01/archive/1_1_762083164.dbf';

-- ALTER DATABASE REGISTER LOGFILE'/u01/archive/1_1_765219083.dbf';

-- Recovery is required if any of thedatafiles are restored backups,

-- or if the last shutdown was not normalor immediate.

RECOVER DATABASE USING BACKUP CONTROLFILE

-- Set Database Guard and/or SupplementalLogging

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

-- Database can now be opened zeroing theonline logs.

ALTER DATABASE OPENRESETLOGS;

-- Files in read-only tablespaces are nownamed.

--同样需要单独处理只读表空间

ALTER DATABASE RENAME FILE 'MISSING00005'

  TO'/u01/app/oracle/oradata/gg2/dave01.dbf';

ALTER DATABASE RENAME FILE 'MISSING00006'

  TO'/u01/app/oracle/oradata/gg2/dave02.dbf';

-- Online the files in read-onlytablespaces.

ALTER TABLESPACE "DAVE" ONLINE;

-- Commands to add tempfiles to temporarytablespaces.

-- Online tempfiles have complete spaceinformation.

-- Other tempfiles may require adjustment.

ALTER TABLESPACE TEMP ADD TEMPFILE'/u01/app/oracle/oradata/gg2/temp01.dbf'

    SIZE 20971520  REUSE AUTOEXTEND ONNEXT 655360  MAXSIZE 32767M;

-- End of tempfile additions.

--

 

*** 2012-02-03 14:15:33.992

Processing Oradebug command'tracefile_name'

 

*** 2012-02-03 14:15:33.996

Oradebug command 'tracefile_name' consoleoutput:

/u01/app/oracle/diag/rdbms/gg2/gg2/trace/gg2_ora_3700.trc

gg3:/u01>

 

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

 

 

2.4 使用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 1006305280 bytes

Fixed Size                  2234600 bytes

Variable Size             650118936 bytes

Database Buffers          348127232 bytes

Redo Buffers                5824512 bytes

SQL>

 

--重建控制文件

SQL> CREATE CONTROLFILE REUSE DATABASE"GG2" NORESETLOGS FORCE LOGGING ARCHIVELOG

   MAXLOGFILES 16

 2    3      MAXLOGMEMBERS 3

 4      MAXDATAFILES 100

 5      MAXINSTANCES 8

 6      MAXLOGHISTORY 292

 7  LOGFILE

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

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

 10    GROUP 3'/u01/app/oracle/oradata/gg2/redo03.log' SIZE 50M BLOCKSIZE 512

 11  --STANDBY LOGFILE

 12 DATAFILE

 13   '/u01/app/oracle/oradata/gg2/system01.dbf',

 14   '/u01/app/oracle/oradata/gg2/sysaux01.dbf',

 15   '/u01/app/oracle/oradata/gg2/undotbs01.dbf',

 16   '/u01/app/oracle/oradata/gg2/users01.dbf'

 17 CHARACTER SET ZHS16GBK

 18  ;

 

Control file created.

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

               

--打开数据库

SQL> alterdatabase open;

 

Database altered.

 

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

SQL> colfile_name for a55

SQL> select tablespace_name,status from dba_tablespaces;

 

TABLESPACE_NAME                STATUS

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

SYSTEM                         ONLINE

SYSAUX                         ONLINE

UNDOTBS1                       ONLINE

TEMP                           ONLINE

USERS                          ONLINE

DAVE                           READ ONLY

 

6 rows selected.

 

SQL> selectfile_id,file_name,status,online_status from dba_data_files;

 

   FILE_ID FILE_NAME                                               STATUS   ONL

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

         4/u01/app/oracle/oradata/gg2/users01.dbf                 AVAILABLE ONL

         3/u01/app/oracle/oradata/gg2/undotbs01.dbf               AVAILABLE ONL

         2/u01/app/oracle/oradata/gg2/sysaux01.dbf                AVAILABLE ONL

         1/u01/app/oracle/oradata/gg2/system01.dbf                AVAILABLE SYS

         5/u01/app/oracle/product/11.2.0.3/db_1/dbs/MISSING00005  AVAILABLE OFF

         6/u01/app/oracle/product/11.2.0.3/db_1/dbs/MISSING00006  AVAILABLE OFF

 

6 rows selected.

 

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

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

 

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

SQL> altertablespace dave online;

alter tablespacedave online

*

ERROR at line 1:

ORA-01157: cannotidentify/lock data file 5 - see DBWR trace file

ORA-01111: namefor data file 5 is unknown - rename to correct file

ORA-01110: datafile 5:

'/u01/app/oracle/product/11.2.0.3/db_1/dbs/MISSING00005'

 

SQL>  alter tablespace dave read write;

 alter tablespace dave read write

*

ERROR at line 1:

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

ORA-01111: namefor data file 5 is unknown - rename to correct file

ORA-01110: datafile 5:

'/u01/app/oracle/product/11.2.0.3/db_1/dbs/MISSING00005'

 

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

 

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

SQL> ALTERDATABASE RENAME FILE 'MISSING00005'

  2    TO'/u01/app/oracle/oradata/gg2/dave01.dbf';

 

Database altered.

 

SQL> ALTERDATABASE RENAME FILE 'MISSING00006'

  2    TO'/u01/app/oracle/oradata/gg2/dave02.dbf';

 

Database altered.

 

--在将表空间online：

SQL> altertablespace dave read write;

alter tablespacedave read write

*

ERROR at line 1:

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

ORA-01110: datafile 5: '/u01/app/oracle/oradata/gg2/dave01.dbf'

 

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

 

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

SQL> altertablespace dave online;

Tablespace altered.

 

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

 

关于这个2个命令的区别参考我的blog：

ALTERDATABASE 与 ALTERTABLESPACE OFFLINE的区别

http://blog.csdn.net/tianlesoftware/article/details/4898800

 

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

SQL> select file_name,status,online_status from dba_data_files;

 

FILE_NAME                                               STATUS    ONLINE_

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

/u01/app/oracle/oradata/gg2/users01.dbf                 AVAILABLE ONLINE

/u01/app/oracle/oradata/gg2/undotbs01.dbf               AVAILABLE ONLINE

/u01/app/oracle/oradata/gg2/sysaux01.dbf                AVAILABLE ONLINE

/u01/app/oracle/oradata/gg2/system01.dbf                AVAILABLE SYSTEM

/u01/app/oracle/oradata/gg2/dave01.dbf                  AVAILABLE ONLINE

/u01/app/oracle/oradata/gg2/dave02.dbf                  AVAILABLE ONLINE

 

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

SQL> selecttablespace_name,status from dba_tablespaces;

 

TABLESPACE_NAME                STATUS

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

SYSTEM                         ONLINE

SYSAUX                         ONLINE

UNDOTBS1                       ONLINE

TEMP                           ONLINE

USERS                          ONLINE

DAVE                           READ ONLY

 

6 rows selected.

 

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

SQL> select * from v$tempfile;

no rows selected

 

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

SQL> ALTERTABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/gg2/temp01.dbf'

  2      SIZE 20971520  REUSE AUTOEXTEND ONNEXT 655360  MAXSIZE 32767M;

 

Tablespacealtered.    

 

在次查询就ok了：

SQL> select name from v$tempfile;

 

NAME

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

/u01/app/oracle/oradata/gg2/temp01.dbf

 

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

 

 

 

 

 

 

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

版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!

QQ:492913789

Email:ahdba@qq.com

Blog:  http://www.cndba.cn/dave

Weibo: http://weibo.com/tianlesoftware

Twitter: http://twitter.com/tianlesoftware

Facebook:http://www.facebook.com/tianlesoftware

 

-------加群需要在备注说明Oracle表空间和数据文件的关系，否则拒绝申请----

DBA1 群：62697716(满);   DBA2 群：62697977(满)  DBA3 群：62697850(满)  

DBA 超级群：63306533(满);  DBA4 群：83829929   DBA5群： 142216823

DBA6 群：158654907    DBA7 群：172855474