2011年1月11日星期二

16:16

数据库中有一个只读表空间

SQL> select tablespace_name ,status from dba_tablespaces;

TABLESPACE_NAME STATUS

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

SYSTEM ONLINE

UNDOTBS1 ONLINE

SYSAUX ONLINE

TEMP ONLINE

USERS READ ONLY

当备份恢复控制文件时,这个数据文件名字需要MISSINGXXXX转换

tbs read only missingxxxxx

2011年1月11日星期二

16:16

数据库中有一个只读表空间

SQL> select tablespace_name ,status from dba_tablespaces;

TABLESPACE_NAME STATUS

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

SYSTEM ONLINE

UNDOTBS1 ONLINE

SYSAUX ONLINE

TEMP ONLINE

USERS READ ONLY

备份控制文件到日志

SQL> alter database backup controlfile to trace;

Database altered.

查询重建控制文件脚本内容

-- 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 "ORCL" NORESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '+DATA/orcl/onlinelog/group_1.306.739819123' SIZE 50M,

GROUP 2 '+DATA/orcl/onlinelog/group_2.302.739819129' SIZE 50M,

GROUP 4 '+DATA/orcl/onlinelog/group_4.283.739819121' SIZE 50M

-- STANDBY LOGFILE

DATAFILE

'+DATA/orcl/datafile/system.276.739810843',

'+DATA/orcl/datafile/undotbs1.284.739810855',

'+DATA/orcl/datafile/sysaux.313.739810861'

CHARACTER SET AL32UTF8

;

-- Commands to re-create incarnation table

-- Below log names MUST be changed to existing filenames on

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

-- re-create incarnation records.

-- ALTER DATABASE REGISTER LOGFILE '+DATA';

-- 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 '+DATA/orcl/datafile/users.325.739810869';

-- 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 TEMP ADD TEMPFILE '+DATA/orcl/tempfile/temp.300.739818973'

SIZE 20971520 REUSE AUTOEXTEND OFF;

-- End of tempfile additions.

重建控制文件

SQL> select name from v$controlfile;

NAME

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

+DATA/ctl.f

SQL>

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 2499805184 bytes

Fixed Size 2086104 bytes

Variable Size 536873768 bytes

Database Buffers 1946157056 bytes

Redo Buffers 14688256 bytes

SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG

2 MAXLOGFILES 16

3 MAXLOGMEMBERS 3

4 MAXDATAFILES 100

5 MAXINSTANCES 8

6 MAXLOGHISTORY 292

7 LOGFILE

8 GROUP 1 '+DATA/orcl/onlinelog/group_1.306.739819123' SIZE 50M,

9 GROUP 2 '+DATA/orcl/onlinelog/group_2.302.739819129' SIZE 50M,

10 GROUP 4 '+DATA/orcl/onlinelog/group_4.283.739819121' SIZE 50M

11 -- STANDBY LOGFILE

12 DATAFILE

13 '+DATA/orcl/datafile/system.276.739810843',

14 '+DATA/orcl/datafile/undotbs1.284.739810855',

15 '+DATA/orcl/datafile/sysaux.313.739810861'

16 CHARACTER SET AL32UTF8

17 ;

Control file created.

SQL> ALTER DATABASE REGISTER LOGFILE '+DATA';

ALTER DATABASE REGISTER LOGFILE '+DATA'

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level

SQL> RECOVER DATABASE

ORA-00283: recovery session canceled due to errors

ORA-00264: no recovery required

SQL> alter system archive log all;

System altered.

SQL> alter database open;

Database altered.

SQL> ALTER DATABASE RENAME FILE 'MISSING00004'

2 TO '+DATA/orcl/datafile/users.325.739810869';

Database altered.

SQL> ALTER TABLESPACE "USERS" ONLINE;

Tablespace altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA/orcl/tempfile/temp.300.739818973'

SIZE 20971520 REUSE AUTOEXTEND OFF;

SP2-0734: unknown command beginning "SIZE 20971..." - rest of line ignored.

SQL>

SQL> alter tablespace temp add tempfile '+DATA/orcl/tempfile/temp.300.739818973';

Tablespace altered.

SQL>

引用自B14191.PDF P361