含read only表空間的資料庫的控制檔案重建

westzq1984發表於2013-10-07
資料庫
因為重建控制檔案要丟失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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章