恢复方法:
1.将镜像的文件恢复
2.使用老的控制文件恢复
3.建立新的 (日志是好的)
4.建立新的 resetlogs (没有日志)
控制文件的备份
1.文本格式
alter database backup controlfile to trace as '/u01/oracle/ctl.sql';
2.二进制格式
alter database backup controlfile to '/u01/oracle/ctl.ctl'
一、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
--------------------------------------------------
/erikxue/control01.ctl
/erikxue/control02.ctl
/erikxue/control03.ctl
SQL> ! cp /u01/oracle/admin/ora10g/udump/s254_ora_2774.trc /erikxue/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 '/erikxue/redo01.log' SIZE 50M,
GROUP 2 '/erikxue/redo02.log' SIZE 50M,
GROUP 3 '/erikxue/redo03.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/erikxue/system01.dbf',
'/erikxue/undotbs01.dbf',
'/erikxue/sysaux01.dbf',
'/erikxue/users01.dbf',
'/erikxue/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: '/erikxue/system01.dbf'
SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.
ALTER TABLESPACE TEMP ADD TEMPFILE '/erikxue/temp01.dbf'
2 SIZE 20971520 REUSE AUTOEXTEND OFF;
Tablespace altered.
SQL>
——————————————————————————————————————
第二种 resetlogs 建立控制文件
情景描述:
控制文件有文本备份
数据文件一致
控制文件和redo文件丢失
控制文件可以使用备份信息新建
没有redo 所以要resetlogs建立控制文件
resetlogs会使数据库重新建立redo文件
resetlogs后一定要重新备份数据库
先备份控制文件 文本格式
SQL> alter database backup controlfile to trace as '/u01/oracle/ctl.sql';
Database altered.
SQL> select * from v$controlfile;
STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS
---------- -------------------------------------------------- --- ---------- --------------
/erikxue/control01.ctl NO 16384450
/erikxue/control02.ctl NO 16384450
/erikxue/control03.ctl NO 16384450
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_
---------- ---------- ------- --------------------------------------------- ---
3 ONLINE /erikxue/redo03.log NO
2 ONLINE /erikxue/redo02.log NO
1 ONLINE /erikxue/redo01.log NO
一致停库
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
删除redo和control文件
SQL> ! rm /erikxue/redo*
SQL> ! rm /erikxue/contro*
SQL>
启动数据库 没有控制文件 只能到nomount 报205
SQL> startup
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 83887696 bytes
Database Buffers 197132288 bytes
Redo Buffers 2973696 bytes
ORA-00205: error in identifying control file, check alert log for more info
SQL>
拿备份的文本中 使用resetlog模式创建控制文件 resetlog会在open时重建redo
CREATE CONTROLFILE REUSE DATABASE "ORA10G" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/erikxue/redo01.log' SIZE 50M,
GROUP 2 '/erikxue/redo02.log' SIZE 50M,
GROUP 3 '/erikxue/redo03.log' SIZE 50M
DATAFILE
'/erikxue/system01.dbf',
'/erikxue/undotbs01.dbf',
'/erikxue/sysaux01.dbf',
'/erikxue/users01.dbf',
'/erikxue/mytbs01.dbf',
'/erikxue/ind_tbs01.dbf',
'/erikxue/wb.dbf'
CHARACTER SET AL32UTF8
;
Control file created.
SQL> select status from v$instance;
STATUS
----------
MOUNTED
SQL> 控制文件已建好 日志文件会在open时重新创建
SQL> ! ls /erikxue/control*
/erikxue/control01.ctl /erikxue/control02.ctl /erikxue/control03.ctl
SQL> ! ls /erikxue/redo*
ls: /erikxue/redo*: 没有那个文件或目录
SQL>
因为数据文件是一致的.所以不需要恢复
SQL> select name,FUZZY,CHECKPOINT_CHANGE#,CHECKPOINT_COUNT from v$datafile_header;
NAME FUZ CHECKPOINT_CHANGE# CHECKPOINT_COUNT
-------------------------------------------------- --- ------------------ ----------------
/erikxue/system01.dbf NO 1079164 247
/erikxue/undotbs01.dbf NO 1079164 207
/erikxue/sysaux01.dbf NO 1079164 244
/erikxue/users01.dbf NO 1079164 257
/erikxue/mytbs01.dbf NO 1079164 120
/erikxue/ind_tbs01.dbf NO 107916454
/erikxue/wb.dbf NO 107916411
7 rows selected.
SQL>
SQL> alter database open resetlogs;
Database altered.
SQL>
SQL> ! ls /erikxue/redo*
/erikxue/redo01.log /erikxue/redo02.log /erikxue/redo03.log
SQL>
ERIKXUE 薛忠权