Oracle数据库数据恢复、性能优化

找回密码
注册
搜索
热搜: 活动 交友 discuz
发新帖

999

积分

1

好友

942

主题
1#
发表于 2017-4-15 23:25:53 | 查看: 1732| 回复: 1
我把控制文件做了冷备份的,就是关闭数据库后复制了控制文件。
为了安全同时我也用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

我按照你说的, 控制文件倒是重建了, 可是还是打不开库啊。我现在的怎么搞啊??? 大师,教教小弟啊。
下载专业ORACLE数据库恢复工具PRM-DUL  For Oracle http://www.parnassusdata.com/

如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线 : 13764045638  QQ: 47079569     邮箱:service@parnassusdata.com
2#
发表于 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: [2141], [1572274317], [1572292488], [], [], [], [], []
在后来开也成问题了,也开不了,
控制文件是时时的, 我的控制文件已经是不能用了, 我选择了重建控制文件,通过备份的控制文件来恢复库。
下面的详细信息:
在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

回复 只看该作者 道具 举报

您需要登录后才可以回帖 登录 | 注册

QQ|手机版|Archiver|Oracle数据库数据恢复、性能优化

GMT+8, 2024-12-23 14:45 , Processed in 0.046968 second(s), 21 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部
TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569