一次oracle崩溃的解决

崩溃恢复过程（留着自己参考）：
下面是查看和修改Oracle服务器端的字符集两方法：

方法一：

1) 查看服务器端字符集：

select * from v$nls_parameters;

select * from nls_database_parameters;

select * from sys.props$;

2) 修改服务器端字符集(用sys用户)：

首先执行：update props$ set value$ = 'WE8ISO8859P1' where name ='NLS_CHARACTERSET';

update props$ set value$ = 'china' where name ='NLS_LANGUAGE';

提交（commit），然后，重新启动数据库；

3）用客户端工具（PL/SQL DEVELOP or PB etc.）查询数据库，若显示乱码，先查询出数据库端的字符集，然后，从注册表中修改NLS_LANG字段的值，可能为AMERICAN_AMERICA.WE8ISO8859P1或者SIMPLIFIED CHINESE_CHINA.ZHS16GBK或者NA等。


方法二：

alter system enable restricted session;

alter database ORCL character set ZHS16GBK;

alter database ORCL national character set ZHS16GBK

本文来自: IT知道网(http://www.itwis.com) 详细出处参考：http://www.itwis.com/html/database/oracle/20090119/3203.html
======================================================
ORA-00205: error in identifying controlfile, check alert log for more info

alert_pcms.log

/usr/oracle/app/admin/pcms/bdump
和
/usr/oracle/app/product/10.1.0/db_1/rdbms/log

Sat Oct 10 09:22:49 2009
ORA-00202: controlfile: '/usr/oracle/app/oradata/pcms/control01.ctl'
ORA-27046: file size is not a multiple of logical block size

[
?应该是INIT.ORA的设置有问题，检查一下。 :oops:
/usr/oracle/app/admin/pcms/pfile

##############################################################################
# Copyright (c) 1991, 2001, 2002 by Oracle Corporation
##############################################################################

###########################################
# Cache and I/O
###########################################
db_block_size=8192
db_file_multiblock_read_count=16

###########################################
# Cursors and Library Cache
###########################################
open_cursors=300

###########################################
# Database Identification
###########################################
db_domain=""
db_name=pcms

###########################################
# Diagnostics and Statistics
###########################################
background_dump_dest=/usr/oracle/app/admin/pcms/bdump
core_dump_dest=/usr/oracle/app/admin/pcms/cdump
user_dump_dest=/usr/oracle/app/admin/pcms/udump

###########################################
# File Configuration
###########################################
control_files=("/usr/oracle/app/oradata/pcms/control01.ctl", "/usr/oracle/app/oradata/pcms/control02.ctl", "/usr/oracle/app/oradata/pcms/control03.ctl")
db_recovery_file_dest=/usr/oracle/app/flash_recovery_area
db_recovery_file_dest_size=2147483648

###########################################
# Job Queues
###########################################
job_queue_processes=10

###########################################

]

show parameter spfile;
/usr/oracle/app/product/10.1.0/db_1/dbs/spfilepcms.ora
startup pfile='/usr/oracle/app/admin/pcms/pfile/init.ora'


CREATE SPFILE FROM PFILE;


================================================================
control_files = /usr/oracle/app/product/10.1.0/db_1/dbs/usr/oracle/app/oradata/pcms/control03.ctl, /usr/oracle/app/product/10.1.0/db_1/dbs/usr/oracle/app/oradata/pcms/control02.ctl

控制文件恢复：
CREATE CONTROLFILE REUSE DATABASE pcms NORESETLOGS ARCHIVELOG
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 '/usr/oracle/app/oradata/pcms/redo01.log' SIZE 100M,
GROUP 2 '/usr/oracle/app/oradata/pcms/redo02.log' SIZE 100M,
GROUP 3 '/usr/oracle/app/oradata/pcms/redo03.log' SIZE 100M
DATAFILE
'/usr/oracle/app/oradata/pcms/system01.dbf',
'/usr/oracle/app/oradata/pcms/undotbs01.dbf',
'/usr/oracle/app/oradata/pcms/sysaux01.dbf',
'/usr/oracle/app/oradata/pcms/users01.dbf',
'/usr/turbocms/data/peugeot.dbf',
'/usr/turbocms/data/turbocms.dbf',
'/usr/configurator_car/ db/configurator.dbf'
CHARACTER SET ZHS16GBK;

=======================================================================
edite spfile:

e didn't have a initSID.ora file. I have created one from the sample file
that was called init.ora. I tried your commands after deleting the bad
spfile.

create spfile = 'spfileORABALT.ora' from pfile = 'initORABALT.ora';
and I got this:
ORA-32002: cannot create SPFILE already being used by the instance

I then changed the command to this and it worked.
create spfile = 'spfileORABALT1.ora' from pfile = 'initORABALT.ora';
and it worked. Now I have a spfile with a different SID name than my server.
Can I move it to the correct name before I restart the server?

-----Original Message-----
From: Sabina
[mailto:oracledba-ezmlmshield-x59610371.[Email address protected]
Sent: Wednesday, November 02, 2005 2:13 PM
To: LazyDBA Discussion
Subject: Re: spfile


If you have the pfile (initSID.ora) then
1. delete the corrupted spfile (never edit this file manually. it is a
server parameter file and it is only the server
that writes into this file. You can view the contents in your text
editor, back it up, and that's it.
2. Recreate the spfile CREATE SPFILE FROM PFILE; The database could be up
or down, it does not matter
3. SHUTDOWN IMMEDIATE;
4. STARTUP (the server will then use the spfileSID.ora to startup the
database)
NOTE
All changes made after the corrupted spfile was created are lost. you must
now make those changes again.
example : Alter system set parameter=value scope=both or scope=spfile;

Sabina.
=============================From metalink=======================================
ORA-01565: error in identifying file '/export/home/oracle/product/10.2.0/dbs/spfilebaan.ora'

ORA-27046: file size is not a multiple of logical block size

查了一下metalink ：
Problem Description:
====================

You are attempting to recreate the controlfiles for an existing database.
During creation you may see the following errors:

Case 1:

ORA-01503: CREATE CONTROLFILE failed
ORA-01565: error in identifying file '/u02/oradata/RM805/system01.dbf'
ORA-27046: file size is not a multiple of logical block size

- or -

Case 2:

ORA-01503: CREATE CONTROLFILE failed
ORA-01160: file is not a data file
ORA-01110: data file : '/u02/oradata/RM805/system01.dbf'


Solution Description:
=====================

Adjust the DB_BLOCK_SIZE parameter accordingly:

- If you received the errors in Case 1 (ORA-01503, ORA-01565, and ORA-27046),
you should decrease the value specified by DB_BLOCK_SIZE before you attempt
to recreate the controlfile.

- If you received the errors in Case 2 (ORA-01503, ORA-01160, and ORA-01110),
you should increase the value specified by DB_BLOCK_SIZE before you attempt
to recreate the controlfile.


Explanation:
============

Both cases occur due to an incorrect setting of the db_block_size parameter in
the "init.ora" file.

Case 1: The specified db_block_size in the "init.ora" is too large.
Case 2: The specified db_block_size in the "init.ora" is too small.


The database blocksize must be specified correctly in the init.ora in order
to successfully recreate the controlfile.


Search Words:
=============

ORA-1503 ORA-1565 ORA-1503 ORA-1160 ORA-1110


Additional References:
======================
Note 265265.1 - ORA-01503 ORA-01160 ORA-01110 while creating controlfile
=======================================================================================

一、 损坏单个控制文件
损坏单个控制文件是比较容易恢复的，因为一般的数据库系统，控制文件都不是一个，而且所有的控制文件都互为镜相，只要拷贝一个好的控制文件替换坏的控制文件就可以了。

1、控制文件损坏，最典型的就是启动数据库出错，不能mount数据库

SQL>startup
ORA-00205: error in identifying controlfile, check alert log for more info
查看报警日志文件，有如下信息
alter database mount
Mon May 26 11:59:52 2003
ORA-00202: controlfile: 'D:Oracleoradatachencontrol01.ctl'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) 系统找不到指定的文件。

2、停止数据库
SQL>shutdown immediate

3、拷贝一个好的控制文件替换坏的控制文件或修改init.ora中的控制文件参数，取消这个坏的控制文件。

4、重新启动数据

SQL>startup
说明：
1、损失单个控制文件是比较简单的，因为数据库中所有的控制文件都是镜相的，只需要简单的拷贝一个好的就可以了
2、建议镜相控制文件在不同的磁盘上
3、建议多做控制文件的备份，长期保留一份由alter database backup control file to trace产生的控制文件的文本备份

二、 损坏全部控制文件

损坏多个控制文件，或者人为的删除了所有的控制文件，通过控制文件的复制已经不能解决问题，这个时候需要重新建立控制文件。同时注意，alter database backup control file to trace可以产生一个控制文件的文本备份。

以下是详细重新创建控制文件的步骤

1、关闭数据库
SQL>shutdown immediate;

2、删除所有控制文件，模拟控制文件的丢失

3、启动数据库，出现错误，并不能启动到mount下

SQL>startup
ORA-00205: error in identifying controlfile, check alert log for more info
查看报警日志文件，有如下信息
alter database mount
Mon May 26 11:53:15 2003
ORA-00202: controlfile: 'D:Oracleoradatachencontrol01.ctl'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) 系统找不到指定的文件。

4、关闭数据库
SQL>shutdown immediate;

5、在internal或sys下运行如下创建控制文件的脚本，注意完整列出联机日志或数据文件的路径，或修改由alter database backup control file to trace备份控制文件时产生的脚本，去掉多余的注释即可。

损坏控制文件的恢复方法 STARTUP NOMOUNT 损坏控制文件的恢复方法 CREATE CONTROLFILE REUSE DATABASE "TEST" NORESETLOGS NOARCHIVELOG 损坏控制文件的恢复方法 MAXLOGFILES 32 损坏控制文件的恢复方法 MAXLOGMEMBERS 2 损坏控制文件的恢复方法 MAXDATAFILES 254 损坏控制文件的恢复方法 MAXINSTANCES 1 损坏控制文件的恢复方法 MAXLOGHISTORY 226 损坏控制文件的恢复方法 LOGFILE 损坏控制文件的恢复方法 GROUP 1 'D:ORACLEORADATATESTREDO01.LOG' SIZE 1M, 损坏控制文件的恢复方法 GROUP 2 'D:ORACLEORADATATESTREDO02.LOG' SIZE 1M, 损坏控制文件的恢复方法 GROUP 3 'D:ORACLEORADATATESTREDO03.LOG' SIZE 1M 损坏控制文件的恢复方法 DATAFILE 损坏控制文件的恢复方法 'D:ORACLEORADATATESTSYSTEM01.DBF', 损坏控制文件的恢复方法 'D:ORACLEORADATATESTRBS01.DBF', 损坏控制文件的恢复方法 'D:ORACLEORADATATESTUSERS01.DBF', 损坏控制文件的恢复方法 'D:ORACLEORADATATESTTEMP01.DBF', 损坏控制文件的恢复方法 'D:ORACLEORADATATESTTOOLS01.DBF', 损坏控制文件的恢复方法 'D:ORACLEORADATATESTINDX01.DBF' 损坏控制文件的恢复方法 CHARACTER SET ZHS16GBK; 损坏控制文件的恢复方法 损坏控制文件的恢复方法 -- Recovery is required if any of the datafiles are restored backups, 损坏控制文件的恢复方法 -- or if the last shutdown was not normal or immediate. 损坏控制文件的恢复方法 RECOVER DATABASE 损坏控制文件的恢复方法 --if the last shutdown was not normal or immediate 损坏控制文件的恢复方法 --noarchive 损坏控制文件的恢复方法 -- RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE 损坏控制文件的恢复方法 --archive 损坏控制文件的恢复方法 -- RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL 损坏控制文件的恢复方法 -- Database can now be opened normally. 损坏控制文件的恢复方法 ALTER DATABASE OPEN; 损坏控制文件的恢复方法 --if recover database until cancel 损坏控制文件的恢复方法 --ALTER DATABASE OPEN RESETLOGS;

6、如果没有错误，数据库将启动到open状态下。

说明：
1、重建控制文件用于恢复全部控制文件的损坏，需要注意其书写的正确性，保证包含了所有的数据文件与联机日志
2、经常有这样一种情况，因为一个磁盘损坏，我们不能再恢复(store)数据文件到这个磁盘，因此在store到另外一个盘的时候，我们就必须重新创建控制文件，用于识别这个新的数据文件，这里也可以用这种方法用于恢复。
==================================================================================================================================
联机日志损坏如何恢复2008-06-02 15:531、如果是非当前日志而且归档（从v$log可以看出），可以使用 Alter database clear logfile group n 来创建一个新的日志文件；如果该日志还没有归档，则需要用 Alter database clear unarchived logfile group n

例如(手工vi状态是UNUSED且未归档的日志文件并破坏之)：

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 0 5242880 1 NO UNUSED 9532226722 30-MAY-08
2 1 0 5242880 1 NO UNUSED 9532226613 30-MAY-08
3 1 33 5242880 1 NO CURRENT 9532313882 02-JUN-08

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER
---------- ------- ------- ------------------------------------------------------------
2 ONLINE /opt/oracle/db02/oradata/ORCL/redo02.log
1 ONLINE /opt/oracle/db01/app/oracle/oradata/ORCL/redo01.log
3 ONLINE /opt/oracle/db03/oradata/ORCL/redo03.log

SQL> !vi /opt/oracle/db02/oradata/ORCL/redo02.log --开始搞破坏，破坏的是非当前且未归档的日志
...
SQL> startup
ORACLE instance started.

Total System Global Area 543722040 bytes
Fixed Size 731704 bytes
Variable Size 436207616 bytes
Database Buffers 104857600 bytes
Redo Buffers 1925120 bytes
Database mounted.
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/opt/oracle/db02/oradata/ORCL/redo02.log'

SQL> alter database clear unarchived logfile group 2;

Database altered.

SQL> alter database open;

Database altered.

SQL>

2、如果是当前日志损坏，一般不能clear，则可能意味着丢失数据。如果有备份，可以采用备份进行不完全恢复；如果没有备份，可能只能用 _allow_resetlogs_corruption=true 来进行强制恢复了，但是，这样的方法是不建议的，最好在有Oracle support的指导下进行。

如果不是 current and active 日志坏了仅是 inactive 坏了，则：
clear log;
startup mount;
alter database clear logfile '...';

否则做下面的步骤：（先备份您的数据库！！！！！）
_ALLOW_RESETLOGS_CORRUPTION=true
statup mount
recover database using backup controlfile until cancel;
cancel
alter database open resetlogs; --maybe error ,not serious
shutdown
去掉该参数_ALLOW_RESETLOGS_CORRUPTION
startup

例如(手工vi状态是current的日志文件并破坏之)：

最好先做一个物理的库的全备。

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 0 5242880 1 NO UNUSED 9532226722 30-MAY-08
2 1 0 5242880 1 NO UNUSED 9532226613 30-MAY-08
3 1 33 5242880 1 NO CURRENT 9532313882 02-JUN-08

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER
---------- ------- ------- ------------------------------------------------------------
2 ONLINE /opt/oracle/db02/oradata/ORCL/redo02.log
1 ONLINE /opt/oracle/db01/app/oracle/oradata/ORCL/redo01.log
3 ONLINE /opt/oracle/db03/oradata/ORCL/redo03.log

SQL> !vi /opt/oracle/db03/oradata/ORCL/redo03.log --开始搞破坏，破坏的是当前且未归档的日志
...
SQL> startup force
ORACLE instance started.

Total System Global Area 543722040 bytes
Fixed Size 731704 bytes
Variable Size 436207616 bytes
Database Buffers 104857600 bytes
Redo Buffers 1925120 bytes
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/opt/oracle/db03/oradata/ORCL/redo03.log'
ORA-27046: file size is not a multiple of logical block size
Additional information: 1

SQL> show parameter spfile --确认是否通过spfile启动的，否则create spfile from pile;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string ?/dbs/spfile@.ora

SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile; --隐含参数须打引号

System altered.

SQL> startup force
ORACLE instance started.

Total System Global Area 543722040 bytes
Fixed Size 731704 bytes
Variable Size 436207616 bytes
Database Buffers 104857600 bytes
Redo Buffers 1925120 bytes
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/opt/oracle/db03/oradata/ORCL/redo03.log'
ORA-27046: file size is not a multiple of logical block size
Additional information: 1


SQL> recover database using backup controlfile until cancel;
ORA-00279: change 9532314069 generated at 06/02/2008 15:56:19 needed for thread 1
ORA-00289: suggestion : /opt/oracle/db01/app/oracle/product/9.2.0/dbs/arch1_33.dbf
ORA-00280: change 9532314069 for thread 1 is in sequence #33


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log '/opt/oracle/db01/app/oracle/product/9.2.0/dbs/arch1_33.dbf'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3


ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/opt/oracle/db01/app/oracle/oradata/ORCL/oradata/ORCL/system01.dbf'


SQL> alter database open resetlogs;

Database altered.

数据库被打开后，马上执行一个full export；后面把隐含参数改回后，重建库，并full import；最后建议执行一下 ANALYZE TABLE ... VALIDATE STRUCTURE CASCADE;

SQL> alter system set "_allow_resetlogs_corruption"=false scope=spfile; --open resetlogs后把隐含参数改回

System altered.

SQL> startup force
ORACLE instance started.

Total System Global Area 543722040 bytes
Fixed Size 731704 bytes
Variable Size 436207616 bytes
Database Buffers 104857600 bytes
Redo Buffers 1925120 bytes
Database mounted.
Database opened.
SQL>

--End--
=============================================================================================================================================
oracle损坏全部控制文件的恢复方法2009-08-13 15:39操作系统版本：

[RHEL5] #cat /etc/redhat-release
Red Hat Enterprise Linux Server release 5 (Tikanga)

Oracle数据库版本：

[RHEL5] #su - oracle
[oracle@RHEL5 ~]$ sqlplus

SQL*Plus: Release 11.1.0.6.0 - Production on Thu Mar 5 15:12:01 2009

Copyright (c) 1982, 2007, Oracle. All rights reserved.

故障现象：启动oracle时报错

SQL> startup nomount
ORACLE instance started.
Total System Global Area 393375744 bytes
Fixed Size 1300156 bytes
Variable Size 289409348 bytes
Database Buffers 96468992 bytes
Redo Buffers 6197248 bytes
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00205: error in identifying control file, check alert log for more info

alter_<ORACLE_SID>.log相关信息如下：

Thu Mar 05 14:59:48 2009
alter database mount
Thu Mar 05 14:59:48 2009
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/oracle/oradata/ora11g/control03.ctl'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/oracle/oradata/ora11g/control02.ctl'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/oracle/oradata/ora11g/control01.ctl'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
Thu Mar 05 14:59:48 2009
Checker run found 3 new persistent data failures
ORA-205 signalled during: alter database mount...

恢复方法

1、生成可获得控制文件的脚本

SQL> alter database backup controlfile to trace;

Database altered.

查看生成的trace文件：

[oracle@RHEL5 trace]$ tail -f alert_ora11g.log
ALTER DATABASE MOUNT
Setting recovery target incarnation to 2
Successful mount of redo thread 1, with mount id 4076919815
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE MOUNT
Thu Mar 05 14:41:39 2009
alter database backup controlfile to trace
Backup controlfile written to trace file /oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_4619.trc
Completed: alter database backup controlfile to trace

[oracle@RHEL5 trace]$ cat ora11g_ora_4619.trc
Trace file /oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_4619.trc
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/product/11.1/db_1
System name: Linux
Node name: RHEL5
Release: 2.6.18-8.el5xen
Version: #1 SMP Fri Jan 26 14:42:21 EST 2007
Machine: i686
Instance name: ora11g
Redo thread mounted by this instance: 1
Oracle process number: 18
Unix process pid: 4619, image: oracle@RHEL5 (TNS V1-V3)


*** 2009-03-05 14:41:39.487
*** SESSION ID:(170.5) 2009-03-05 14:41:39.487
*** CLIENT ID:() 2009-03-05 14:41:39.487
*** SERVICE NAME:() 2009-03-05 14:41:39.487
*** MODULE NAME:(sqlplus@RHEL5 (TNS V1-V3)) 2009-03-05 14:41:39.487
*** ACTION NAME:() 2009-03-05 14:41:39.487

-- The following are current System-scope REDO Log Archival related
-- parameters and can be included in the database initialization file.
--
-- LOG_ARCHIVE_DEST=''
-- LOG_ARCHIVE_DUPLEX_DEST=''
--
-- LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf
--
-- DB_UNIQUE_NAME="ora11g"
--
-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'
-- LOG_ARCHIVE_MAX_PROCESSES=4
-- STANDBY_FILE_MANAGEMENT=MANUAL
-- STANDBY_ARCHIVE_DEST=?/dbs/arch
-- FAL_CLIENT=''
-- FAL_SERVER=''
--
-- LOG_ARCHIVE_DEST_10='LOCATION=USE_DB_RECOVERY_FILE_DEST'
-- LOG_ARCHIVE_DEST_10='OPTIONAL REOPEN=300 NODELAY'
-- LOG_ARCHIVE_DEST_10='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_10='REGISTER NOALTERNATE NODEPENDENCY'
-- LOG_ARCHIVE_DEST_10='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'
-- LOG_ARCHIVE_DEST_10='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
-- LOG_ARCHIVE_DEST_STATE_10=ENABLE
--
-- LOG_ARCHIVE_DEST_1='LOCATION=/oracle/product/11.1/db_1/dbs/arch'
-- LOG_ARCHIVE_DEST_1='MANDATORY NOREOPEN NODELAY'
-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM EXPEDITE NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_1='NOREGISTER NOALTERNATE NODEPENDENCY'
-- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'
-- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
-- LOG_ARCHIVE_DEST_STATE_1=ENABLE
--
-- Below are two sets of SQL statements, each of which creates a new
-- control file and uses it to open the database. The first set opens
-- the database with the NORESETLOGS option and should be used only if
-- the current versions of all online logs are available. The second
-- set opens the database with the RESETLOGS option and should be used
-- if online logs are unavailable.
-- The appropriate set of statements can be copied from the trace into
-- a script file, edited as necessary, and executed when there is a
-- need to re-create the control file.
--
-- Set #1. NORESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORA11G" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oracle/oradata/ora11g/redo01.log' SIZE 50M,
GROUP 2 '/oracle/oradata/ora11g/redo02.log' SIZE 50M,
GROUP 3 '/oracle/oradata/ora11g/redo03.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/oracle/oradata/ora11g/system01.dbf',
'/oracle/oradata/ora11g/sysaux01.dbf',
'/oracle/oradata/ora11g/undotbs01.dbf',
'/oracle/oradata/ora11g/users01.dbf'
CHARACTER SET ZHS16GBK
;
-- 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 '/oracle/flash_recovery_area/ORA11G/archivelog/2009_03_05/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/oracle/flash_recovery_area/ORA11G/archivelog/2009_03_05/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
-- 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 '/oracle/oradata/ora11g/temp01.dbf' REUSE;
-- End of tempfile additions.
--
-- Set #2. RESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORA11G" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oracle/oradata/ora11g/redo01.log' SIZE 50M,
GROUP 2 '/oracle/oradata/ora11g/redo02.log' SIZE 50M,
GROUP 3 '/oracle/oradata/ora11g/redo03.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/oracle/oradata/ora11g/system01.dbf',
'/oracle/oradata/ora11g/sysaux01.dbf',
'/oracle/oradata/ora11g/undotbs01.dbf',
'/oracle/oradata/ora11g/users01.dbf'
CHARACTER SET ZHS16GBK
;
-- 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 '/oracle/flash_recovery_area/ORA11G/archivelog/2009_03_05/o1_mf_1_1_%u_.arc';
-- ALTER DATABASE REGISTER LOGFILE '/oracle/flash_recovery_area/ORA11G/archivelog/2009_03_05/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 USING BACKUP CONTROLFILE
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/oracle/oradata/ora11g/temp01.dbf' REUSE;
-- End of tempfile additions.
--

通过这个文件可以获得生成控制文件的脚本（分NORESETLOGS/RESETLOGS）：

[oracle@RHEL5 scripts]$ cat createctlf.sql
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORA11G" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oracle/oradata/ora11g/redo01.log' SIZE 50M,
GROUP 2 '/oracle/oradata/ora11g/redo02.log' SIZE 50M,
GROUP 3 '/oracle/oradata/ora11g/redo03.log' SIZE 50M
DATAFILE
'/oracle/oradata/ora11g/system01.dbf',
'/oracle/oradata/ora11g/sysaux01.dbf',
'/oracle/oradata/ora11g/undotbs01.dbf',
'/oracle/oradata/ora11g/users01.dbf'
CHARACTER SET ZHS16GBK
;
RECOVER DATABASE
ALTER DATABASE OPEN;
ALTER TABLESPACE TEMP ADD TEMPFILE '/oracle/oradata/ora11g/temp01.dbf' REUSE;

运行这个脚本即可重建控制文件：

SQL> conn / as sysdba
Connected to an idle instance.
SQL> @createctlf
SP2-0310: unable to open file "createctlf.sql"
SQL> set echo on
SQL> @/oracle/admin/ora11g/scripts/createctlf
SQL> STARTUP NOMOUNT
ORACLE instance started.

Total System Global Area 393375744 bytes
Fixed Size 1300156 bytes
Variable Size 289409348 bytes
Database Buffers 96468992 bytes
Redo Buffers 6197248 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "ORA11G" NORESETLOGS NOARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/oracle/oradata/ora11g/redo01.log' SIZE 50M,
9 GROUP 2 '/oracle/oradata/ora11g/redo02.log' SIZE 50M,
10 GROUP 3 '/oracle/oradata/ora11g/redo03.log' SIZE 50M
11 DATAFILE
12 '/oracle/oradata/ora11g/system01.dbf',
13 '/oracle/oradata/ora11g/sysaux01.dbf',
14 '/oracle/oradata/ora11g/undotbs01.dbf',
15 '/oracle/oradata/ora11g/users01.dbf'
16 CHARACTER SET ZHS16GBK
17 ;

Control file created.

SQL> RECOVER DATABASE
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
SQL> ALTER DATABASE OPEN;

Database altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/oracle/oradata/ora11g/temp01.dbf' REUSE;

Tablespace altered.

查看实例状态：

SQL> select status from v$instance;

STATUS
------------
OPEN

查看新生成的控制文件：

SQL> select * from V$controlfile;
rows will be truncated

rows will be truncated

rows will be truncated


STATUS NAME
------- ------------------------------------------------------------------------
/oracle/oradata/ora11g/control01.ctl
/oracle/oradata/ora11g/control02.ctl
/oracle/oradata/ora11g/control03.ctl

SQL> host ls -l /oracle/oradata/ora11g/contr*
-rw-r----- 1 oracle oinstall 10076160 Mar 5 15:37 /oracle/oradata/ora11g/control01.ctl
-rw-r----- 1 oracle oinstall 10076160 Mar 5 15:37 /oracle/oradata/ora11g/control02.ctl
-rw-r----- 1 oracle oinstall 10076160 Mar 5 15:37 /oracle/oradata/ora11g/control03.ctl
=========================================================================================================================
联机日志损坏如何恢复
2008-06-02 15:53
1、如果是非当前日志而且归档（从v$log可以看出），可以使用 Alter database clear logfile group n 来创建一个新的日志文件；如果该日志还没有归档，则需要用 Alter database clear unarchived logfile group n

例如(手工vi状态是UNUSED且未归档的日志文件并破坏之)：

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 0 5242880 1 NO UNUSED 9532226722 30-MAY-08
2 1 0 5242880 1 NO UNUSED 9532226613 30-MAY-08
3 1 33 5242880 1 NO CURRENT 9532313882 02-JUN-08

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER
---------- ------- ------- ------------------------------------------------------------
2 ONLINE /opt/oracle/db02/oradata/ORCL/redo02.log
1 ONLINE /opt/oracle/db01/app/oracle/oradata/ORCL/redo01.log
3 ONLINE /opt/oracle/db03/oradata/ORCL/redo03.log

SQL> !vi /opt/oracle/db02/oradata/ORCL/redo02.log --开始搞破坏，破坏的是非当前且未归档的日志
...
SQL> startup
ORACLE instance started.

Total System Global Area 543722040 bytes
Fixed Size 731704 bytes
Variable Size 436207616 bytes
Database Buffers 104857600 bytes
Redo Buffers 1925120 bytes
Database mounted.
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/opt/oracle/db02/oradata/ORCL/redo02.log'

SQL> alter database clear unarchived logfile group 2;

Database altered.

SQL> alter database open;

Database altered.

SQL>

2、如果是当前日志损坏，一般不能clear，则可能意味着丢失数据。如果有备份，可以采用备份进行不完全恢复；如果没有备份，可能只能用 _allow_resetlogs_corruption=true 来进行强制恢复了，但是，这样的方法是不建议的，最好在有Oracle support的指导下进行。

如果不是 current and active 日志坏了仅是 inactive 坏了，则：
clear log;
startup mount;
alter database clear logfile '...';

否则做下面的步骤：（先备份您的数据库！！！！！）
_ALLOW_RESETLOGS_CORRUPTION=true
statup mount
recover database using backup controlfile until cancel;
cancel
alter database open resetlogs; --maybe error ,not serious
shutdown
去掉该参数_ALLOW_RESETLOGS_CORRUPTION
startup

例如(手工vi状态是current的日志文件并破坏之)：

最好先做一个物理的库的全备。

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 0 5242880 1 NO UNUSED 9532226722 30-MAY-08
2 1 0 5242880 1 NO UNUSED 9532226613 30-MAY-08
3 1 33 5242880 1 NO CURRENT 9532313882 02-JUN-08

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER
---------- ------- ------- ------------------------------------------------------------
2 ONLINE /opt/oracle/db02/oradata/ORCL/redo02.log
1 ONLINE /opt/oracle/db01/app/oracle/oradata/ORCL/redo01.log
3 ONLINE /opt/oracle/db03/oradata/ORCL/redo03.log

SQL> !vi /opt/oracle/db03/oradata/ORCL/redo03.log --开始搞破坏，破坏的是当前且未归档的日志
...
SQL> startup force
ORACLE instance started.

Total System Global Area 543722040 bytes
Fixed Size 731704 bytes
Variable Size 436207616 bytes
Database Buffers 104857600 bytes
Redo Buffers 1925120 bytes
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/opt/oracle/db03/oradata/ORCL/redo03.log'
ORA-27046: file size is not a multiple of logical block size
Additional information: 1

SQL> show parameter spfile --确认是否通过spfile启动的，否则create spfile from pile;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string ?/dbs/spfile@.ora

SQL> alter system set "_allow_resetlogs_corruption"=true scope=spfile; --隐含参数须打引号

System altered.

SQL> startup force
ORACLE instance started.

Total System Global Area 543722040 bytes
Fixed Size 731704 bytes
Variable Size 436207616 bytes
Database Buffers 104857600 bytes
Redo Buffers 1925120 bytes
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/opt/oracle/db03/oradata/ORCL/redo03.log'
ORA-27046: file size is not a multiple of logical block size
Additional information: 1


SQL> recover database using backup controlfile until cancel;
ORA-00279: change 9532314069 generated at 06/02/2008 15:56:19 needed for thread 1
ORA-00289: suggestion : /opt/oracle/db01/app/oracle/product/9.2.0/dbs/arch1_33.dbf
ORA-00280: change 9532314069 for thread 1 is in sequence #33


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log '/opt/oracle/db01/app/oracle/product/9.2.0/dbs/arch1_33.dbf'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3


ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/opt/oracle/db01/app/oracle/oradata/ORCL/oradata/ORCL/system01.dbf'


SQL> alter database open resetlogs;

Database altered.

数据库被打开后，马上执行一个full export；后面把隐含参数改回后，重建库，并full import；最后建议执行一下 ANALYZE TABLE ... VALIDATE STRUCTURE CASCADE;

SQL> alter system set "_allow_resetlogs_corruption"=false scope=spfile; --open resetlogs后把隐含参数改回

System altered.

SQL> startup force
ORACLE instance started.

Total System Global Area 543722040 bytes
Fixed Size 731704 bytes
Variable Size 436207616 bytes
Database Buffers 104857600 bytes
Redo Buffers 1925120 bytes
Database mounted.
Database opened.
SQL>

--End--

===========================================================================
1.关闭数据库
sys@sec> shutdown immediate;

2.使用系统的cp命令，拷贝生成一个新的控制文件
cp CONTROL01.CTL CONTROL02.CTL

3.修改初始化参数文件中的control_files参数，添加CONTROL05.CTL的相关信息
*.control_files='C:\oracle\product\10.2.0\oradata\sec\control01.ctl'
修改为
*.control_files='C:\oracle\product\10.2.0\oradata\sec\control01.ctl','D:\control02.ctl'

4.根据pfile生成spfile
sys@sec> create spfile from pfile;

5.启动数据库（默认是使用spfile启动数据库），完成整个的修改工作
sys@sec> startup

-- The End --
===============================================================================================================================================
SSH Secure Shell 3.2.9 (Build 283)
Copyright (c) 2000-2003 SSH Communications Security Corp - http://www.ssh.com/

This copy of SSH Secure Shell is a non-commercial version.
This version does not include PKI and PKCS #11 functionality.


# ls
anaconda-ks.cfg Desktop install.log install.log.syslog tools
# cd /usr/oracle/app/oradata/pcms/
# ls
control01.ctl control02.ctl.back redo01.log r.sql temp01.dbf
control01.ctl.back control03.ctl redo02.log sysaux01.dbf undotbs01.dbf
control02.ctl control03ctl.back redo03.log system01.dbf users01.dbf
# vi r.sql
CREATE CONTROLFILE REUSE DATABASE pcms RESETLOGS ARCHIVELOG
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 '/usr/oracle/app/oradata/pcms/redo01.log' SIZE 100M,
GROUP 2 '/usr/oracle/app/oradata/pcms/redo02.log' SIZE 100M,
GROUP 3 '/usr/oracle/app/oradata/pcms/redo03.log' SIZE 100M
DATAFILE
'/usr/oracle/app/oradata/pcms/system01.dbf',
'/usr/oracle/app/oradata/pcms/undotbs01.dbf',
'/usr/oracle/app/oradata/pcms/sysaux01.dbf',
'/usr/oracle/app/oradata/pcms/users01.dbf',
'/usr/turbocms/data/peugeot.dbf',
'/usr/turbocms/data/turbocms.dbf',
'/usr/configurator_car/db/configurator.dbf'
CHARACTER SET ZHS16GBK;
~
~
~
~
"r.sql" [dos] 19L, 719C written
#
#
# su - oracle
[oracle@123 ~]$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.1.0.3.0 - Production on Sun Oct 11 11:00:48 2009

Copyright (c) 1982, 2004, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Release 10.1.0.3.0 - Production

SQL> exit
Disconnected from Oracle Database 10g Release 10.1.0.3.0 - Production
[oracle@123 ~]$ ps -ef|grep ora
oracle 6041 1 0 Oct10 ? 00:00:00 /usr/oracle/app/product/10.1.0/db_1/bin/tnslsnr LISTENER -inherit
root 23496 23176 0 10:42 pts/1 00:00:00 su - oracle
oracle 23497 23496 0 10:42 pts/1 00:00:00 -bash
oracle 23751 1 0 10:48 ? 00:00:00 ora_pmon_pcms
oracle 23753 1 0 10:48 ? 00:00:00 ora_mman_pcms
oracle 23756 1 0 10:48 ? 00:00:00 ora_dbw0_pcms
oracle 23758 1 0 10:48 ? 00:00:00 ora_lgwr_pcms
oracle 23760 1 0 10:48 ? 00:00:00 ora_ckpt_pcms
oracle 23762 1 0 10:48 ? 00:00:00 ora_smon_pcms
oracle 23764 1 0 10:48 ? 00:00:00 ora_reco_pcms
oracle 23766 1 0 10:48 ? 00:00:00 ora_cjq0_pcms
oracle 23768 1 0 10:48 ? 00:00:00 ora_d000_pcms
oracle 23770 1 0 10:48 ? 00:00:00 ora_s000_pcms
root 24095 24049 0 11:00 pts/2 00:00:00 su - oracle
oracle 24096 24095 0 11:00 pts/2 00:00:00 -bash
oracle 24191 24096 0 11:00 pts/2 00:00:00 ps -ef
oracle 24192 24096 0 11:00 pts/2 00:00:00 grep ora
[oracle@123 ~]$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.1.0.3.0 - Production on Sun Oct 11 11:01:07 2009

Copyright (c) 1982, 2004, Oracle. All rights reserved.


Connected to:
Oracle Database 10g Release 10.1.0.3.0 - Production

SQL> CREATE CONTROLFILE REUSE DATABASE pcms RESETLOGS ARCHIVELOG
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 '/usr/oracle/app/oradata/pcms/redo01.log' SIZE 100M,
GROUP 2 '/usr/oracle/app/oradata/pcms/redo02.log' SIZE 100M,
GROUP 3 '/usr/oracle/app/oradata/pcms/redo03.log' SIZE 100M
DATAFILE
'/usr/oracle/app/oradata/pcms/system01.dbf',
'/usr/oracle/app/oradata/pcms/undotbs01.dbf',
'/usr/oracle/app/oradata/pcms/sysaux01.dbf',
'/usr/oracle/app/oradata/pcms/users01.dbf',
'/usr/turbocms/data/peugeot.dbf',
'/usr/turbocms/data/turbocms.dbf',
'/usr/configurator_car/db/configurator.dbf'
CHARACTER SET ZHS16GBK;
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
Control file created.

SQL>
SQL>
SQL>
SQL>
SQL>
SQL> recover database using backup controlfile until cancel;
ORA-00279: change 142590274 generated at 10/03/2009 12:05:13 needed for thread
1
ORA-00289: suggestion :
/usr/oracle/app/flash_recovery_area/PCMS/archivelog/2009_10_11/o1_mf_1_62874_%u_
.arc
ORA-00280: change 142590274 for thread 1 is in sequence #62874


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> alter database open resetlogs;

Database altered.

SQL>
SQL>
SQL>
SQL>
SQL> select * from v$database;
-结果

SQL>
SQL>
SQL>
SQL>
SQL>
SQL> exigt
SP2-0042: unknown command "exigt" - rest of line ignored.
SQL> exit
Disconnected from Oracle Database 10g Release 10.1.0.3.0 - Production