重建控制文件

下面是重建控制文件的示例：

1，首先，建立ASCII控制文件：

SQL>CONNECT / AS SYSDBA

SQL>ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

2,然后，显示这个文件。可用这个脚本：

SELECT d.VALUE
|| '/'
|| LOWER (RTRIM (i.INSTANCE, CHR (0)))
|| '_ora_'
|| p.spid
|| '.trc' trace_file_name
FROM (SELECT p.spid
FROM v$mystat m, v$session s, v$process p
WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,
(SELECT t.INSTANCE
FROM v$thread t, v$parameter v
WHERE v.NAME = 'thread'
AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
(SELECT VALUE
FROM v$parameter
WHERE NAME = 'user_dump_dest') d

[@more@]

1，首先，建立ASCII控制文件：

SQL>CONNECT / AS SYSDBA

SQL>ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

2,然后，显示这个文件。可用这个脚本：

SELECT d.VALUE
|| '/'
|| LOWER (RTRIM (i.INSTANCE, CHR (0)))
|| '_ora_'
|| p.spid
|| '.trc' trace_file_name
FROM (SELECT p.spid
FROM v$mystat m, v$session s, v$process p
WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,
(SELECT t.INSTANCE
FROM v$thread t, v$parameter v
WHERE v.NAME = 'thread'
AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
(SELECT VALUE
FROM v$parameter
WHERE NAME = 'user_dump_dest') d
下面是我的TRC文件的一部分：

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE DATABASE "MYORCL" NORESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 (

'/u01/app/oracle/oradata/myorcl/redo01.log',

'/home/oracle/redolog/redo0101.log'

) SIZE 50M,

GROUP 2 (

'/u01/app/oracle/oradata/myorcl/redo02.log',

'/home/oracle/redolog/redo0201.log'

) SIZE 50M,

GROUP 3 (

'/u01/app/oracle/oradata/myorcl/redo03.log',

'/home/oracle/redolog/redo0301.log'

) SIZE 50M

-- STANDBY LOGFILE

DATAFILE

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

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

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

'/u01/app/oracle/oradata/myorcl/users01.dbf',

'/u01/app/oracle/oradata/myorcl/example01.dbf',

'/u01/app/oracle/oradata/myorcl/myrman.dbf',

'/u01/app/oracle/oradata/myorcl/lianxi01.dbf'

CHARACTER SET WE8ISO8859P1

;

-- Configure RMAN configuration record 1

VARIABLE RECNO NUMBER;

EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');

-- 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 '/u01/app/oracle/flash_recovery_area/MYORCL/archivelog/2006_12_02/o1_mf_1_1_%u_.arc';

-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area/MYORCL/archivelog/2006_12_02/o1_mf_1_1_%u_.arc';

-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area/MYORCL/archivelog/2006_12_02/o1_mf_1_1_%u_.arc';

-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/flash_recovery_area/MYORCL/archivelog/2006_12_02/o1_mf_1_1_%u_.arc';

-- Recovery is required if any of the datafiles are restored backups,

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

RECOVER DATABASE

-- Block change tracking was enabled, so re-enable it now.

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING

USING FILE '/home/oracle/block_track/myorcl_block_track.log' REUSE;

-- All logs need archiving and a log switch is needed.

ALTER SYSTEM ARCHIVE LOG ALL;

-- Database can now be opened normally.

ALTER DATABASE OPEN;

-- Commands to add tempfiles to temporary tablespaces.

-- Online tempfiles have complete space information.

-- Other tempfiles may require adjustment.

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

SIZE 20971520 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

-- End of tempfile additions.

3，接着，复制需要运行的那部分，建立一个文档，这里，我起名为：BACKUP_CONTROLFILE_NORESET.TXT

4,模拟控制文件丢失。

5，SQL>SQLPLUS /NOLOG

SQL>CONNECT / AS SYSDBA

SQL>@BACKUP_CONTROLFILE_NORESET.TXT

6,验证控制是否被重建。

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

转载于:http://blog.itpub.net/330796/viewspace-881135/