求救！删除控制文件恢复不了啦

ALLSTARS_ORACLE 发表于 2017-4-15 23:25:53

我把控制文件做了冷备份的，就是关闭数据库后复制了控制文件。
为了安全同时我也用rman 备份了控制文件。

为了安全，我还做了全库的备份。

问题是：我在open的状态下手动删除了控制文件。删除之后，我就把冷备的控制文件复制到了数据库的原位置去了，数据库只能到mount状态，开不了，一开就报错：
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/u01/product/oradata/ecsdb1/system01.dbf'
ORA-01207: file is more recent than control file - old control file

SQL>

请高人指点下，我现在要怎么恢复。

RMAN> restore database;

Starting restore at 02-MAY-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK

creating datafile fno=1 name=/u01/product/oradata/ecsdb1/system01.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 05/02/2012 13:28:11
ORA-01180: can not create datafile 1
ORA-01110: data file 1: '/u01/product/oradata/ecsdb1/system01.dbf'

RMAN> recover database;

Starting recover at 02-MAY-12
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/02/2012 13:28:23
RMAN-06094: datafile 1 must be restored

还是弄不了啊？

SQL> CREATE CONTROLFILE REUSE DATABASE "ECSDB1" RESETLOGS  ARCHIVELOG
  2    MAXLOGFILES 16
  3    MAXLOGMEMBERS 3
  4    MAXDATAFILES 100
  5    MAXINSTANCES 8
  6    MAXLOGHISTORY 292
  7  LOGFILE
  8 GROUP 1 '/u01/product/oradata/ecsdb1/redo01.log'  SIZE 50M,
  9 GROUP 2 '/u01/product/oradata/ecsdb1/redo02.log'  SIZE 50M,
10 GROUP 3 '/u01/product/oradata/ecsdb1/redo03.log'  SIZE 50M
11  -- STANDBY LOGFILE
12  DATAFILE
13 '/u01/product/oradata/ecsdb1/system01.dbf',
14 '/u01/product/oradata/ecsdb1/undotbs01.dbf',
15 '/u01/product/oradata/ecsdb1/sysaux01.dbf',
16 '/u01/product/oradata/ecsdb1/users01.dbf',
17 '/u01/product/oradata/ecsdb1/mgmt.dbf',
18 '/u01/product/oradata/ecsdb1/mgmt_ecm_depot1.dbf',
19 '/u01/product/oradata/ecsdb1/userdata01.dbf'
20  CHARACTER SET AL32UTF8
21  ;

Control file created.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/product/oradata/ecsdb1/system01.dbf'

SQL> restore datafile 1;
SP2-0734: unknown command beginning "restore da..." - rest of line ignored.
SQL> recover datafile 1;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

我按照你说的，控制文件倒是重建了，可是还是打不开库啊。我现在的怎么搞啊？？？大师，教教小弟啊。

SQL> CREATE CONTROLFILE REUSE DATABASE "ECSDB1" RESETLOGS  ARCHIVELOG
2    MAXLOGFILES 16
3    MAXLOGMEMBERS 3
4    MAXDATAFILES 100
5    MAXINSTANCES 8
6    MAXLOGHISTORY 292
7  LOGFILE
8 GROUP 1 '/u01/product/oradata/ecsdb1/redo01.log'  SIZE 50M,
9 GROUP 2 '/u01/product/oradata/ecsdb1/redo02.log'  SIZE 50M,
10 GROUP 3 '/u01/product/oradata/ecsdb1/redo03.log'  SIZE 50M
11  -- STANDBY LOGFILE
12  DATAFILE
13 '/u01/product/oradata/ecsdb1/system01.dbf',
14 '/u01/product/oradata/ecsdb1/undotbs01.dbf',
15 '/u01/product/oradata/ecsdb1/sysaux01.dbf',
16 '/u01/product/oradata/ecsdb1/users01.dbf',
17 '/u01/product/oradata/ecsdb1/mgmt.dbf',
18 '/u01/product/oradata/ecsdb1/mgmt_ecm_depot1.dbf',
19 '/u01/product/oradata/ecsdb1/userdata01.dbf'
20  CHARACTER SET AL32UTF8
21  ;

Control file created.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/u01/product/oradata/ecsdb1/system01.dbf'

SQL> restore datafile 1;
SP2-0734: unknown command beginning "restore da..." - rest of line ignored.
SQL> recover datafile 1;
ORA-00283: recovery session canceled due to errors
ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

我按照你说的，控制文件倒是重建了，可是还是打不开库啊。我现在的怎么搞啊？？？大师，教教小弟啊。

ALLSTARS_ORACLE 发表于 2017-4-15 23:26:12

我再把问题描述一边：
      我在数据库开启的状态下，手动的删除了控制文件，三个全部都删除了。
备份情况：
1.       冷备份：我之前有在关闭库后复制了3个控制文件。
2.       Rman备份：我有使用rman备份了全部的控制文件：
命令：backup current controlfile format='/home/dbbackup/controlfile_back/%d_%s.ctl';

我的解决方案：
  我当时就是直接把冷备的控制文件cp 到数据库的原始位置,我看了库是可以打开，我查询的时候库是开的，我以为没的问题了，可是关闭不了，提示：
SQL> shutdown immediate
ORA-00600: internal error code, arguments: , , , [], [], [], [], []
在后来开也成问题了，也开不了，
控制文件是时时的，我的控制文件已经是不能用了，我选择了重建控制文件，通过备份的控制文件来恢复库。
下面的详细信息：
在mount 状态下：
alter database backup controlfile to trace
之后我们到跟踪文件里去找，里面有很多我看的傻眼了，我这里提供个方法来找是哪一个文件里有重建控制文件的脚本：
方法一：（不知道是我没理解，还是怎么搞的，查询出来的那个文件在跟踪文件里没有，但是离查询的文件名的后面的数值很近，你打开几个可以看到，我是这么弄的）
--查询跟踪文件位置和文件名脚本
SELECT a.VALUE || b.symbol || c.instance_name || '_ora_' || d.spid || '.trc' trace_file
FROM (SELECT VALUE FROM v$parameter WHERE NAME = 'user_dump_dest') a,
      (SELECT SUBSTR (VALUE, -6, 1) symbol FROM v$parameter
      WHERE NAME = 'user_dump_dest') b,
      (SELECT instance_name FROM v$instance) c,
      (SELECT spid FROM v$session s, v$process p, v$mystat m
      WHERE s.paddr = p.addr AND s.SID = m.SID AND m.statistic# = 0) d
方法二：（我自己没用）
使用控制文件tracefile_identifier 参数来确定跟踪文件的名称，例如：
参数tracefile_identifie可以用来控制生成在user_dump_dest 目录下的trace文件名称
SQL> alter session set sql_trace=true ;
Session altered.
SQL> alter session set tracefile_identifier ='DEBUG';
Session altered.
在user_dump_dest 目录下的trace文件名称定义为：
INSTANCE_PID_ora_TRACEID.trc , 其中INSTANCE为ORACLE的实例名，PID为操作系统的进程ID（V$PROCESS.OSPID）,TRACEID就是设置的tracefile_identifie值。

--重建控制文件的脚本：
--重建控制文件的脚本：
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ECSDB1" RESETLOGS  ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/product/oradata/ecsdb1/redo01.log'  SIZE 50M,
  GROUP 2 '/u01/product/oradata/ecsdb1/redo02.log'  SIZE 50M,
  GROUP 3 '/u01/product/oradata/ecsdb1/redo03.log'  SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  '/u01/product/oradata/ecsdb1/system01.dbf',
  '/u01/product/oradata/ecsdb1/undotbs01.dbf',
  '/u01/product/oradata/ecsdb1/sysaux01.dbf',
  '/u01/product/oradata/ecsdb1/users01.dbf',
  '/u01/product/oradata/ecsdb1/mgmt.dbf',
  '/u01/product/oradata/ecsdb1/mgmt_ecm_depot1.dbf',
  '/u01/product/oradata/ecsdb1/userdata01.dbf'
CHARACTER SET AL32UTF8

在sqlplus 里面执行这个语句。
或者你写成脚本  如： vi  CreateControlfile  里面的内容就是上面的脚本。
执行的话就是： SQL> @ CreateControlfile
现在开始恢复：
--开始使用rman基于控制文件的转储
RMAN> restore controlfile from '/home/controlback/ECSDB1_4.ctl';

Starting restore at 02-MAY-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/u01/product/oradata/ecsdb1/control01.ctl
output filename=/u01/product/oradata/ecsdb1/control02.ctl
output filename=/u01/product/oradata/ecsdb1/control03.ctl
Finished restore at 02-MAY-12
--启动到mount状态
RMAN> startup mount;

database is already started
database mounted
released channel: ORA_DISK_1
--开始恢复了
RMAN> recover database;

Starting recover at 02-MAY-12
Starting implicit crosscheck backup at 02-MAY-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
Crosschecked 2 objects
Finished implicit crosscheck backup at 02-MAY-12
Starting implicit crosscheck copy at 02-MAY-12
using channel ORA_DISK_1
Finished implicit crosscheck copy at 02-MAY-12
searching for all files in the recovery area
cataloging files...
no files cataloged
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 40 is already on disk as file /u01/product/oradata/ecsdb1/redo01.log
archive log filename=/u01/product/oradata/ecsdb1/redo01.log thread=1 sequence=40
media recovery complete, elapsed time: 00:00:02
Finished recover at 02-MAY-12

RMAN> alter database open resetlogs;
记住用此方式打开了库后，记得来个完整的重新备份，因为以前的备份文件不能用了。

database opened
RMAN-06900: WARNING: unable to generate V$RMAN_STATUS or V$RMAN_OUTPUT row
RMAN-06901: WARNING: disabling update of the V$RMAN_STATUS and V$RMAN_OUTPUT rows
ORACLE error from target database:
ORA-06502: PL/SQL: numeric or value error: character to number conversion error

RMAN> exit
--查询状态：
SQL> select name,open_mode from v$database;

NAME    OPEN_MODE
--------- ----------
ECSDB1 READ WRITE
--再关
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
--在开
SQL> startup
ORACLE instance started.

Total System Global Area  524288000 bytes
Fixed Size                1220384 bytes
Variable Size          205521120 bytes
Database Buffers       314572800 bytes
Redo Buffers             2973696 bytes
Database mounted.
Database opened.
--开看测试数据，没有丢失
SQL> select * from userdata;

      ID NAME
---------- ---------------
      1 shengang
      2 xieyiming
      3 shengbing

页: [1]

Oracle数据库数据恢复、性能优化's Archiver

求救！删除控制文件恢复不了啦