控制文件恢复之noresetlogs建立控制文件实验

两种备份方式

1.trace
 追踪文件位置在 udump 下,也可以加 as子句 自定义存储位置
 alter database backup controlfile to trace [as '/PATH/file.sql'];
 
 udump内的文件名
 SQL> select value||'/'||instance_name||'_ora_'||spid||'.trc' from v$process p,v$session s,v$parameter,v$instance where p.addr=s.paddr and sid=(select sid from v$mystat where rownum=1) and name='user_dump_dest';

 VALUE||'/'||INSTANCE_NAME||'_ORA_'||SPID||'.TRC'
 ----------------------------------------------------------------------------------------------------
 /u01/oracle/admin/ora10g/udump/s254_ora_2774.trc

 SQL>

重建控制文件
SQL> alter database backup controlfile to trace;

Database altered.

SQL> select name from v$controlfile;

NAME
--------------------------------------------------
/db254/control01.ctl
/db254/control02.ctl
/db254/control03.ctl

SQL> ! cp /u01/oracle/admin/ora10g/udump/s254_ora_2774.trc /db254/control01.ctl

SQL> alter system switch logfile;
ERROR:
ORA-03114: not connected to ORACLE

alter system switch logfile
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size      1218992 bytes
Variable Size     79693392 bytes
Database Buffers   201326592 bytes
Redo Buffers      2973696 bytes
ORA-00205: error in identifying control file, check alert log for more info

CREATE CONTROLFILE REUSE DATABASE "ORA10G" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/db254/redo01.log'  SIZE 50M,
  GROUP 2 '/db254/redo02.log'  SIZE 50M,
  GROUP 3 '/db254/redo03.log'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  '/db254/system01.dbf',
  '/db254/undotbs01.dbf',
  '/db254/sysaux01.dbf',
  '/db254/users01.dbf',
  '/db254/mytbs01.dbf'
CHARACTER SET AL32UTF8
;

Control file created.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/db254/system01.dbf'

SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.

ALTER TABLESPACE TEMP ADD TEMPFILE '/db254/temp01.dbf'
  2       SIZE 20971520  REUSE AUTOEXTEND OFF;

Tablespace altered.

SQL>

2.

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

转载于:http://blog.itpub.net/24756465/viewspace-722846/