ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 含read only表空间的数据库的控制文件重建

含read only表空间的数据库的控制文件重建

原创 Linux操作系统 作者:westzq1984 时间:2013-10-07 11:12:41 0 删除 编辑
因为重建控制文件要丢失stop scn,并且数据文件里面没有存有表空间read only的信息。
数据字典里面存有但是OPEN前是无法读取的。所以最好能按照backup to trace生成的脚本做

--     Set #1. NORESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
--  ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "O11203" NORESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/o11203/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/o11203/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/o11203/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/o11203/system01.dbf',
  '/u01/app/oracle/oradata/o11203/sysaux01.dbf',
  '/u01/app/oracle/oradata/o11203/streams_tbs.dbf',
  '/u01/app/oracle/oradata/o11203/undotbs02.dbf'
CHARACTER SET ZHS16GBK
;
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE
-- All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
-- Database can now be opened normally.
ALTER DATABASE OPEN;
-- Files in read-only tablespaces are now named.
ALTER DATABASE RENAME FILE 'MISSING00004'
  TO '/u01/app/oracle/oradata/o11203/users01.dbf';
-- Online the files in read-only tablespaces.
ALTER TABLESPACE "USERS" ONLINE;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP1 ADD TEMPFILE '/u01/app/oracle/oradata/o11203/temp0101.dbf'
     SIZE 1048576000  REUSE AUTOEXTEND ON NEXT 8192  MAXSIZE 32767M;
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/o11203/temp01.dbf'
     SIZE 104857600  REUSE AUTOEXTEND OFF;
ALTER TABLESPACE TEMP2 ADD TEMPFILE '/u01/app/oracle/oradata/o11203/temp0102.dbf'
     SIZE 104857600  REUSE AUTOEXTEND OFF;
-- End of tempfile additions.

如果重建时,包含了read only的数据文件,就要麻烦一点点

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup nomount;
****************************************
*** zhangqiaoc Test Env ****************
****************************************

ORACLE instance started.

Total System Global Area 1068994560 bytes
Fixed Size                  2235080 bytes
Variable Size             796919096 bytes
Database Buffers          264241152 bytes
Redo Buffers                5599232 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "O11203" NORESETLOGS FORCE LOGGING ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
    MAXINSTANCES 8
  5    6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 '/u01/app/oracle/oradata/o11203/redo01.log'  SIZE 50M BLOCKSIZE 512,
  9    GROUP 2 '/u01/app/oracle/oradata/o11203/redo02.log'  SIZE 50M BLOCKSIZE 512,
 10    GROUP 3 '/u01/app/oracle/oradata/o11203/redo03.log'  SIZE 50M BLOCKSIZE 512
 11  -- STANDBY LOGFILE
 12  DATAFILE
 13    '/u01/app/oracle/oradata/o11203/system01.dbf',
 14    '/u01/app/oracle/oradata/o11203/sysaux01.dbf',
 15    '/u01/app/oracle/oradata/o11203/streams_tbs.dbf',
 16    '/u01/app/oracle/oradata/o11203/undotbs02.dbf',
 17    '/u01/app/oracle/oradata/o11203/users01.dbf'     <== read only tablespace
 18  CHARACTER SET ZHS16GBK
 19  ;

Control file created.

SQL> RECOVER DATABASE
ORA-00279: change 29862501 generated at 10/07/2013 09:39:19 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/O11203/archivelog/2013_10_07/o1_mf_1_151_%u_.
arc
ORA-00280: change 29862501 for thread 1 is in sequence #151


Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log
'/u01/app/oracle/fast_recovery_area/O11203/archivelog/2013_10_07/o1_mf_1_151_%u_
.arc'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


ORA-00308: cannot open archived log
'/u01/app/oracle/fast_recovery_area/O11203/archivelog/2013_10_07/o1_mf_1_151_%u_
.arc'
ORA-27037: unable to obtain file status         <== 需要从read only开始的日志问题,这些文件已经不存在了
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: '/u01/app/oracle/oradata/o11203/users01.dbf'


SQL> alter database datafile 4 offline;

Database altered.

SQL> alter database open;

Database altered.

SQL> alter database datafile 4 online;        <== 必须重新online表空间后在online数据文件
alter database datafile 4 online
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: '/u01/app/oracle/oradata/o11203/users01.dbf'


SQL> alter tablespace users online;

Tablespace altered.

SQL> alter database datafile 4 online;

Database altered.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8242091/viewspace-773794/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 11g v$sql 新增列
下一篇: Delta Number
请登录后发表评论 登录
全部评论

注册时间:2009-04-06

  • 博文量
    251
  • 访问量
    955634