没有controlfile的自动归档,仅丢失全部控制文件
// 先做好controlfile的文本备份
C:\Documents and Settings\Administrator>sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on 星期一 6月 30 17:02:33 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
17:02:33 idle>conn /as sysdba
已连接。
17:04:07 sys@LEE2>startup force
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE 例程已经启动。
Total System Global Area 440401920 bytes
Fixed Size 1249440 bytes
Variable Size 92278624 bytes
Database Buffers 339738624 bytes
Redo Buffers 7135232 bytes
数据库装载完毕。
数据库已经打开。
17:05:05 sys@LEE2>alter database backup controlfile to trace;
数据库已更改。
已用时间: 00: 00: 00.21
// 做好控制文件备份后,关闭db,模拟丢失
17:05:55 sys@LEE2>shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
17:07:09 sys@LEE2>startup force
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE 例程已经启动。
Total System Global Area 440401920 bytes
Fixed Size 1249440 bytes
Variable Size 96472928 bytes
Database Buffers 335544320 bytes
Redo Buffers 7135232 bytes
ORA-00205: ?????????, ??????, ???????
17:07:58 sys@LEE2>
// 开始恢复 controlfile!!!
17:19:04 sys@LEE2>startup nomount
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE 例程已经启动。
Total System Global Area 440401920 bytes
Fixed Size 1249440 bytes
Variable Size 96472928 bytes
Database Buffers 335544320 bytes
Redo Buffers 7135232 bytes
17:19:09 sys@LEE2>CREATE CONTROLFILE REUSE DATABASE "LEE2" NORESETLOGS ARCHIVELOG
17:19:28 2 MAXLOGFILES 16
17:19:28 3 MAXLOGMEMBERS 3
17:19:28 4 MAXDATAFILES 100
17:19:28 5 MAXINSTANCES 8
17:19:28 6 MAXLOGHISTORY 292
17:19:28 7 LOGFILE
17:19:28 8 GROUP 1 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\LEE2\REDO01.LOG' SIZE 50M,
17:19:28 9 GROUP 2 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\LEE2\REDO02.LOG' SIZE 50M,
17:19:28 10 GROUP 3 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\LEE2\REDO03.LOG' SIZE 50M
17:19:28 11 -- STANDBY LOGFILE
17:19:28 12 DATAFILE
17:19:28 13 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\LEE2\SYSTEM01.DBF',
17:19:28 14 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\LEE2\UNDOTBS01.DBF',
17:19:28 15 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\LEE2\SYSAUX01.DBF',
17:19:28 16 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\LEE2\USERS01.DBF',
17:19:28 17 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\LEE2\LEE01.DBF',
17:19:28 18 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\LEE2.DBF',
17:19:28 19 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\LEE2_01.DBF',
17:19:28 20 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\LEE2\SYSAUX02.DBF',
17:19:28 21 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\LEE2\SYSTEM02.DBF',
17:19:28 22 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\LEE2\USERS02.DBF'
17:19:28 23 CHARACTER SET ZHS16GBK
17:19:28 24 ;
控制文件已创建。
已用时间: 00: 00: 01.39
17:19:31 sys@LEE2>recover database
ORA-00283: ??????????
ORA-00264: ?????
17:21:01 sys@LEE2>alter database archivelog all;
alter database archivelog all
*
第 1 行出现错误:
ORA-00933: SQL ???????
已用时间: 00: 00: 00.03
17:21:23 sys@LEE2>alter database archive log all;
alter database archive log all
*
第 1 行出现错误:
ORA-02231: ALTER DATABASE ???????
已用时间: 00: 00: 00.01
17:21:35 sys@LEE2>alter system archive log all;
系统已更改。
已用时间: 00: 00: 11.15
17:22:05 sys@LEE2>alter database open;
数据库已更改。
已用时间: 00: 00: 18.37
17:22:31 sys@LEE2>select *from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ----------
----
1 1 2 52428800 1 YES INACTIVE 1407076 29-6? -08
2 1 4 52428800 1 NO CURRENT 1448332 30-6? -08
3 1 3 52428800 1 YES INACTIVE 1442118 30-6? -08
已用时间: 00: 00: 00.04
17:22:45 sys@LEE2>
//---------------ok
但你检查alert.log的时候,你会发现有以下警告:
Mon Jun 30 20:39:43 2008
*********************************************************************
WARNING: The following temporary tablespaces contain no files.
This condition can occur when a backup controlfile has
been restored. It may be necessary to add files to these
tablespaces. That can be done using the SQL statement:
ALTER TABLESPACE ADD TEMPFILE
Alternatively, if these temporary tablespaces are no longer
needed, then they can be dropped.
Empty temporary tablespace: TEMP
这是应为恢复控制文件之后,temp临时表空间被置空!!!!!!!
20:39:53 sys@LEE2>select name from v$tempfile;
未选定行
已用时间: 00: 00: 00.07
20:42:44 sys@LEE2>select tablespace_name,file_name
20:42:58 2 from dba_data_files;
TABLESPACE_NAME
------------------------------
FILE_NAME
----------------------------------------------------------------------------------------------------
--------------------------------------------------
USERS
E:\ORACLE\PRODUCT\10.2.0\ORADATA\LEE2\USERS02.DBF
SYSTEM
E:\ORACLE\PRODUCT\10.2.0\ORADATA\LEE2\SYSTEM02.DBF
SYSAUX
E:\ORACLE\PRODUCT\10.2.0\ORADATA\LEE2\SYSAUX02.DBF
TEST_EXP
E:\ORACLE\PRODUCT\10.2.0\ORADATA\LEE2_01.DBF
TEST_EXP
E:\ORACLE\PRODUCT\10.2.0\ORADATA\LEE2.DBF
LEE
E:\ORACLE\PRODUCT\10.2.0\ORADATA\LEE2\LEE01.DBF
USERS
E:\ORACLE\PRODUCT\10.2.0\ORADATA\LEE2\USERS01.DBF
SYSAUX
E:\ORACLE\PRODUCT\10.2.0\ORADATA\LEE2\SYSAUX01.DBF
UNDOTBS1
E:\ORACLE\PRODUCT\10.2.0\ORADATA\LEE2\UNDOTBS01.DBF
SYSTEM
E:\ORACLE\PRODUCT\10.2.0\ORADATA\LEE2\SYSTEM01.DBF
LEE2
E:\ORACLE\PRODUCT\10.2.0\ORADATA\LEE2\LEE2.DBF
已选择11行。
已用时间: 00: 00: 00.14
20:43:03 sys@LEE2>
20:43:48 sys@LEE2>alter tablespace temp add tempfile
20:44:02 2 'E:\ORACLE\PRODUCT\10.2.0\ORADATA\LEE2\temp01.dbf' ;
表空间已更改。
已用时间: 00: 00: 00.85
20:44:44 sys@LEE2>startup force
ORA-32004: obsolete and/or deprecated parameter(s) specified
ORACLE 例程已经启动。
Total System Global Area 440401920 bytes
Fixed Size 1249440 bytes
Variable Size 104861536 bytes
Database Buffers 327155712 bytes
Redo Buffers 7135232 bytes
数据库装载完毕。
数据库已经打开。
20:45:25 sys@LEE2>
// 一切恢复正常
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/11134734/viewspace-366459/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/11134734/viewspace-366459/