一、使用trace文件恢复
sql> alter database backup controlfile to trace; ——将控制文件备份到trace文件
database altered.
sql> @gettrace --得到trace文件的路径
trace_file_name
--------------------------------------------------------------------------------
/oracle/app/admin/ora10g/udump/ora10g_ora_31270.trc
sql> !
[oracle@ ~]$ more /oracle/app/admin/ora10g/udump/ora10g_ora_31270.trc
/oracle/app/admin/ora10g/udump/ora10g_ora_31270.trc
oracle database 10g enterprise edition release 10.2.0.1.0 - production
with the partitioning, olap and data mining options
oracle_home = /oracle/app/oracle/product/10.2.0/db_1
system name: linux
node name:
release: 2.6.18-164.el5
.
.
.
-- alter database set standby database to maximize performance
startup nomount
create controlfile reuse database ora10g noresetlogs archivelog
maxlogfiles 16
maxlogmembers 3
maxdatafiles 100
maxinstances 8
maxloghistory 292
logfile
group 1 '/oradata/ora10g/redo01.log' size 50m,
group 2 '/oradata/ora10g/redo02.log' size 50m,
group 3 '/oradata/ora10g/redo03.log' size 50m
-- standby logfile
datafile
'/oradata/ora10g/system01.dbf',
'/oradata/ora10g/undotbs01.dbf',
.
.
.
alter tablespace temp add tempfile '/oradata/ora10g/temp01.dbf'
size 20971520 reuse autoextend on next 655360 maxsize 32767m;
-- end of tempfile additions.
--
[oracle@ udump]$ cd
[oracle@ ~]$ vim recontro.sql ——拷贝trace文件中有用的语句重建控制文件。如果在之前没有备份的trace文件，，我们可以从init{sid}.ora文件中得到数据文件、日志文件、数据库表空间等信息，把init{sid}.ora文件中的内容按以下格式创建重构控制文件脚本
startup nomount
create controlfile reuse database ora10g noresetlogs archivelog
maxlogfiles 16
maxlogmembers 3
maxdatafiles 100
maxinstances 8
maxloghistory 292
logfile
group 1 '/oradata/ora10g/redo01.log' size 50m,
group 2 '/oradata/ora10g/redo02.log' size 50m,
group 3 '/oradata/ora10g/redo03.log' size 50m
datafile
'/oradata/ora10g/system01.dbf',
'/oradata/ora10g/undotbs01.dbf',
'/oradata/ora10g/sysaux01.dbf',
'/oradata/ora10g/users01.dbf'
character set zhs16gbk
;
variable recno number;
execute :recno := sys.dbms_backup_restore.setconfig('controlfile autobackup','off');
recover database
alter system archive log all;
alter database open;
size 20971520 reuse autoextend on next 655360 maxsize 32767m;
startup nomount
create controlfile reuse database ora10g resetlogs archivelog
maxlogfiles 16
maxlogmembers 3
maxdatafiles 100
maxinstances 8
maxloghistory 292
logfile
group 1 '/oradata/ora10g/redo01.log' size 50m,
group 2 '/oradata/ora10g/redo02.log' size 50m,
group 3 '/oradata/ora10g/redo03.log' size 50m
datafile
'/oradata/ora10g/system01.dbf',
'/oradata/ora10g/undotbs01.dbf',
'/oradata/ora10g/sysaux01.dbf',
'/oradata/ora10g/users01.dbf'
character set zhs16gbk
;
variable recno number;
execute :recno := sys.dbms_backup_restore.setconfig('controlfile autobackup','off');
recover database using backup controlfile
alter database open resetlogs;
size 20971520 reuse autoextend on next 655360 maxsize 32767m;
recontro.sql 49l, 1641c 已写入
[oracle@ ~]$ sqlplus / as sysdba
sql*plus: release 10.2.0.1.0 - production on tue jul 19 03:30:34 2011
copyright (c) 1982, 2005, oracle. all rights reserved.
connected to:
oracle database 10g enterprise edition release 10.2.0.1.0 - production
with the partitioning, olap and data mining options
sql> shutdown immediate
database closed.
database dismounted.
oracle instance shut down.
sql> set echo on ——打开显示输出
sql> @recontro ——使用刚建立的重构控制文件的脚本重建控制文件
sql> startup nomount
oracle instance started.
total system global area 209715200 bytes
fixed size 1218556 bytes
variable size 75499524 bytes
database buffers 130023424 bytes
redo buffers 2973696 bytes
sql> create controlfile reuse database ora10g noresetlogs archivelog
2 maxlogfiles 16
3 maxlogmembers 3
4 maxdatafiles 100
5 maxinstances 8
6 maxloghistory 292
7 logfile
8 group 1 '/oradata/ora10g/redo01.log' size 50m,
9 group 2 '/oradata/ora10g/redo02.log' size 50m,
10 group 3 '/oradata/ora10g/redo03.log' size 50m
11 datafile
12 '/oradata/ora10g/system01.dbf',
13 '/oradata/ora10g/undotbs01.dbf',
14 '/oradata/ora10g/sysaux01.dbf',
15 '/oradata/ora10g/users01.dbf'
16 character set zhs16gbk
17 ;
control file created.
sql> variable recno number;
sql> execute :recno := sys.dbms_backup_restore.setconfig('controlfile autobackup','off');
pl/sql procedure successfully completed.
sql> recover database
sql> alter system archive log all;
system altered.
sql> alter database open;
database altered.
sql> alter tablespace temp add tempfile '/oradata/ora10g/temp01.dbf'
2 size 20971520 reuse autoextend on next 655360 maxsize 32767m;
tablespace altered.
sql> startup nomount
ora-01081: cannot start already-running oracle - shut it down first
sql> create controlfile reuse database ora10g resetlogs archivelog
2 maxlogfiles 16
3 maxlogmembers 3
4 maxdatafiles 100
5 maxinstances 8
6 maxloghistory 292
7 logfile
8 group 1 '/oradata/ora10g/redo01.log' size 50m,
9 group 2 '/oradata/ora10g/redo02.log' size 50m,
10 group 3 '/oradata/ora10g/redo03.log' size 50m
11 datafile
12 '/oradata/ora10g/system01.dbf',
13 '/oradata/ora10g/undotbs01.dbf',
14 '/oradata/ora10g/sysaux01.dbf',
15 '/oradata/ora10g/users01.dbf'
16 character set zhs16gbk
17 ;
create controlfile reuse database ora10g resetlogs archivelog
sql> variable recno number;
sql> execute :recno := sys.dbms_backup_restore.setconfig('controlfile autobackup','off');
pl/sql procedure successfully completed.
sql> recover database using backup controlfile
sql> alter database open resetlogs;
sql> alter tablespace temp add tempfile '/oradata/ora10g/temp01.dbf'
2 size 20971520 reuse autoextend on next 655360 maxsize 32767m;
alter tablespace temp add tempfile '/oradata/ora10g/temp01.dbf'
tablespace altered.
sql> select open_mode from v$database;
open_mode
----------
read write