分类目录归档：Oracle

一、sqlplus nomount数据库并恢复spfile

SQL> startup
ORA-01078: failure in processing system parameters                       
LRM-00109: 无法打开参数文件 'E:\ORACLE\11_2_0\DATABASE\INITXFF.ORA' 

RMAN> restore spfile to 'e:\oracle\11_2_0\database\spfilexff.ora'  
2> from 'F:\rmanbackup\20111113_0KMRIT19_1_1';                           

启动 restore 于 14-11月-11                                               
RMAN-00571: ===========================================================  
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================  
RMAN-00601: fatal error in recovery manager                              
RMAN-03004: 执行命令期间出现严重错误       
RMAN-10041: 无法在失败后重新创建轮询通道上下文。
RMAN-10024: 设置 rpc 轮询时出错                                          
RMAN-10005: 打开游标时出错                                               
RMAN-10002: ORACLE 错误 : ORA-03114: not connected to ORACLE             
RMAN-03002: restore 命令 (在 11/14/2011 22:23:24 上) 失败
ORA-03113: 通信通道的文件结尾                                            
进程 ID: 2884                                                            
会话 ID: 97 序列号: 1

1、无spfilexff.ora/initxff.ora/init.ora文件，sqlplus不能启动数据库至nomount状态
2、在数据库没有nomount状态下，不能恢复spfile

二、rman nomount数据库并恢复spfile

RMAN> startup           

已连接到目标数据库 (未启动)
启动失败: ORA-01078: failure in processing system parameters             
LRM-00109: 无法打开参数文件 'E:\ORACLE\11_2_0\DATABASE\INITXFF.ORA'      

在没有参数文件的情况下启动 Oracle 实例以检索 spfile
Oracle 实例已启动
RMAN-00571: =========================================================== 
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============  
RMAN-00571: ===========================================================  
RMAN-03002: startup 命令 (在 11/14/2011 22:00:32 上) 失败
ORA-00205: 标识控制文件时出错, 有关详细信息, 请查看预警日志

RMAN> restore spfile to 'e:\oracle\11_2_0\database\spfilexff.ora'        
2> from 'F:\rmanbackup\20111113_0KMRIT19_1_1';                           

启动 restore 于 14-11月-11                                               
分配的通道: ORA_DISK_1                                                   
通道 ORA_DISK_1: SID=10 设备类型=DISK

通道 ORA_DISK_1: 正在从 AUTOBACKUP F:\rmanbackup\20111113_0KMRIT19_1_1 还原 spfile 
通道 ORA_DISK_1: 从 AUTOBACKUP 还原 SPFILE 已完成                        
完成 restore 于 14-11月-11

1、rman会使用一个隐含(默认的参数文件启动数据库至nomount状态)
2、在nomount状态下，rman可以恢复spfile

三、rman启动数据库日志

Mon Nov 14 22:00:26 2011
Starting ORACLE instance (restrict)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_1 parameter default value as e:\oracle\11_2_0\RDBMS
Autotune of undo retention is turned on. 
IMODE=BR
ILAT =18
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options.
Using parameter settings in client-side pfile C:\S5O4.1 on machine XIFENFEI-PC
System parameters with non-default values:
  sga_target               = 152M
  compatible               = "11.2.0.1.0"
  _dummy_instance          = TRUE
  remote_login_passwordfile= "EXCLUSIVE"
  db_name                  = "XFF"
Mon Nov 14 22:00:27 2011
PMON started with pid=2, OS id=2932 
Mon Nov 14 22:00:28 2011
VKTM started with pid=3, OS id=4364 at elevated priority
VKTM running at (10)millisec precision with DBRM quantum (100)ms
Mon Nov 14 22:00:29 2011
GEN0 started with pid=4, OS id=4524 
Mon Nov 14 22:00:30 2011
DIAG started with pid=5, OS id=5472 
Mon Nov 14 22:00:30 2011
DBRM started with pid=6, OS id=5296 
Mon Nov 14 22:00:30 2011
PSP0 started with pid=7, OS id=6120 
Mon Nov 14 22:00:30 2011
DIA0 started with pid=8, OS id=4528 
Mon Nov 14 22:00:30 2011
MMAN started with pid=9, OS id=6052 
Mon Nov 14 22:00:30 2011
DBW0 started with pid=10, OS id=5348 
Mon Nov 14 22:00:30 2011
LGWR started with pid=11, OS id=4904 
Mon Nov 14 22:00:30 2011
CKPT started with pid=12, OS id=5388 
Mon Nov 14 22:00:30 2011
SMON started with pid=13, OS id=4492 
Mon Nov 14 22:00:30 2011
RECO started with pid=14, OS id=576 
Mon Nov 14 22:00:30 2011
MMON started with pid=15, OS id=6072 
Mon Nov 14 22:00:30 2011
MMNL started with pid=16, OS id=5720 
ORACLE_BASE from environment = e:\oracle
Mon Nov 14 22:00:31 2011
alter database mount
ORA-00210: cannot open the specified control file
ORA-00202: control file: 'E:\ORACLE\11_2_0\DATABASE\CTL1XFF.ORA'
ORA-27041: unable to open file
OSD-04002: 无法打开文件
O/S-Error: (OS 2) 系统找不到指定的文件。
ORA-205 signalled during: alter database mount...
Mon Nov 14 22:00:33 2011
Checker run found 1 new persistent data failures

1、查看系统没有发现 C:\S5O4.1文件
2、restrict方式nomount数据库

一、案例说明
利用rman备份数据库后，因为人工误删除表空间，现在需要使用非完全恢复来找回被误删除的表空间

二、环境准备

[oracle@ECP-UC-DB1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Nov 14 12:35:14 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
XFF
ODU
TEMP

7 rows selected.

SQL> select name from v$datafile;

NAME
---------------------------------------------------------------
/opt/oracle/oradata/test/system01.dbf
/opt/oracle/oradata/test/undotbs01.dbf
/opt/oracle/oradata/test/sysaux01.dbf
/opt/oracle/oradata/test/users01.dbf
/opt/oracle/oradata/test/user32g.dbf
/opt/oracle/oradata/test/xifenfei01.dbf
/opt/oracle/oradata/test/user02.dbf
/opt/oracle/oradata/test/odu02.dbf
/opt/oracle/oradata/test/odu01.dbf
/opt/oracle/oradata/test/odu03.dbf
/opt/oracle/oradata/test/xifenfei02.dbf

11 rows selected.

SQL> create tablespace xifenfei datafile 
2   '/opt/oracle/oradata/test/t_xifenfei01.dbf' size 10m ;

Tablespace created.

SQL> create table chf.t_xifenfei tablespace xifenfei 
  2  as
  3  select * from dba_objects;

Table created.


SQL> select count(*) from chf.t_xifenfei;

  COUNT(*)
----------
     50476

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ECP-UC-DB1 ~]$ $ORACLE_HOME/bin/rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Mon Nov 14 12:43:35 2011

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

connected to target database: TEST (DBID=2056006906)

RMAN> list backup summary;                 

using target database control file instead of recovery catalog

RMAN> backup database format '/tmp/test_full_%U';

Starting backup at 2011-11-14 12:44:32
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=134 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00008 name=/opt/oracle/oradata/test/odu02.dbf
input datafile fno=00002 name=/opt/oracle/oradata/test/undotbs01.dbf
input datafile fno=00001 name=/opt/oracle/oradata/test/system01.dbf
input datafile fno=00003 name=/opt/oracle/oradata/test/sysaux01.dbf
input datafile fno=00009 name=/opt/oracle/oradata/test/odu01.dbf
input datafile fno=00006 name=/opt/oracle/oradata/test/xifenfei01.dbf
input datafile fno=00005 name=/opt/oracle/oradata/test/user32g.dbf
input datafile fno=00007 name=/opt/oracle/oradata/test/user02.dbf
input datafile fno=00010 name=/opt/oracle/oradata/test/odu03.dbf
input datafile fno=00011 name=/opt/oracle/oradata/test/xifenfei02.dbf
input datafile fno=00012 name=/opt/oracle/oradata/test/t_xifenfei01.dbf
input datafile fno=00004 name=/opt/oracle/oradata/test/users01.dbf
channel ORA_DISK_1: starting piece 1 at 2011-11-14 12:44:33
channel ORA_DISK_1: finished piece 1 at 2011-11-14 12:48:59
piece handle=/tmp/test_full_01mrkqdh_1_1 tag=TAG20111114T124433 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:04:26
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 2011-11-14 12:49:02
channel ORA_DISK_1: finished piece 1 at 2011-11-14 12:49:03
piece handle=/tmp/test_full_02mrkqlr_1_1 tag=TAG20111114T124433 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
Finished backup at 2011-11-14 12:49:03

RMAN> exit


Recovery Manager complete.
[oracle@ECP-UC-DB1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Nov 14 12:50:53 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> drop tablespace xifenfei including contents and datafiles;

Tablespace dropped.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ECP-UC-DB1 ~] cd /opt/oradata
[oracle@ECP-UC-DB1 oradata]$ mv test test_bak
[oracle@ECP-UC-DB1 oradata]$ mkdir test
[oracle@ECP-UC-DB1 oradata]$ ll
total 16
drwxr-x--- 3 oracle oinstall 4096 Aug 12 21:50 ecp
drwxr-x--- 3 oracle oinstall 4096 Jun 25 14:23 ecp_bak
drwxr-xr-x 2 oracle oinstall 4096 Nov 14 12:53 test
drwxr-x--- 3 oracle oinstall 4096 Nov 14 12:51 test_bak

通过alert日志，查找出删除表空间xifenfei的时间:Mon Nov 14 12:49:102011

三、恢复测试

[oracle@ECP-UC-DB1 oradata]$ $ORACLE_HOME/bin/rman target /

Recovery Manager: Release 10.2.0.4.0 - Production on Mon Nov 14 12:58:47 2011

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

connected to target database (not started)

RMAN> startup

Oracle instance started
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 11/14/2011 12:58:56
ORA-00205: error in identifying control file, check alert log for more info

RMAN> restore controlfile from '/tmp/test_full_02mrkqlr_1_1';

Starting restore at 2011-11-14 12:59:15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output filename=/opt/oracle/oradata/test/control01.ctl
output filename=/opt/oracle/oradata/test/control02.ctl
output filename=/opt/oracle/oradata/test/control03.ctl
Finished restore at 2011-11-14 12:59:19

RMAN> restore database;

Starting restore at 2011-11-14 13:00:16
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 11/14/2011 13:00:16
ORA-01507: database not mounted

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> restore database;

Starting restore at 2011-11-14 13:00:32
Starting implicit crosscheck backup at 2011-11-14 13:00:32
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 1 objects
Finished implicit crosscheck backup at 2011-11-14 13:00:33

Starting implicit crosscheck copy at 2011-11-14 13:00:33
using channel ORA_DISK_1
Finished implicit crosscheck copy at 2011-11-14 13:00:33

searching for all files in the recovery area
cataloging files...
no files cataloged

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /opt/oracle/oradata/test/system01.dbf
restoring datafile 00002 to /opt/oracle/oradata/test/undotbs01.dbf
restoring datafile 00003 to /opt/oracle/oradata/test/sysaux01.dbf
restoring datafile 00004 to /opt/oracle/oradata/test/users01.dbf
restoring datafile 00005 to /opt/oracle/oradata/test/user32g.dbf
restoring datafile 00006 to /opt/oracle/oradata/test/xifenfei01.dbf
restoring datafile 00007 to /opt/oracle/oradata/test/user02.dbf
restoring datafile 00008 to /opt/oracle/oradata/test/odu02.dbf
restoring datafile 00009 to /opt/oracle/oradata/test/odu01.dbf
restoring datafile 00010 to /opt/oracle/oradata/test/odu03.dbf
restoring datafile 00011 to /opt/oracle/oradata/test/xifenfei02.dbf
restoring datafile 00012 to /opt/oracle/oradata/test/t_xifenfei01.dbf
channel ORA_DISK_1: reading from backup piece /tmp/test_full_01mrkqdh_1_1
   channel ORA_DISK_1: restored backup piece 1
piece handle=/tmp/test_full_01mrkqdh_1_1 tag=TAG20111114T124433
channel ORA_DISK_1: restore complete, elapsed time: 00:07:08
Finished restore at 2011-11-14 13:07:42

RMAN> run
2> {
3> sql 'alter session set nls_date_format ="yyyy-mm-dd hh24:mi:ss"';
4> set until time='2011-11-14 12:49:10';
5> recover database;
6> }

sql statement: alter session set nls_date_format ="yyyy-mm-dd hh24:mi:ss"

executing command: SET until clause

Starting recover at 2011-11-14 13:18:09
using channel ORA_DISK_1

starting media recovery

unable to find archive log
archive log thread=1 sequence=248
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/14/2011 13:18:10
RMAN-06054: media recovery requesting unknown log: thread 1 seq 248 lowscn 11517136

--另外打开一个会话查询当前最大的归档日志seq#情况
[oracle@ECP-UC-DB1 archivelog]$ ll -thr|tail -10
-rw-r----- 1 oracle oinstall  45M Nov  5 19:00 1_238_757860476.dbf
-rw-r----- 1 oracle oinstall  45M Nov  7 10:00 1_239_757860476.dbf
-rw-r----- 1 oracle oinstall  45M Nov  8 02:25 1_240_757860476.dbf
-rw-r----- 1 oracle oinstall  45M Nov  8 22:25 1_241_757860476.dbf
-rw-r----- 1 oracle oinstall  45M Nov  9 22:26 1_242_757860476.dbf
-rw-r----- 1 oracle oinstall  45M Nov 10 22:01 1_243_757860476.dbf
-rw-r----- 1 oracle oinstall  46M Nov 11 22:01 1_244_757860476.dbf
-rw-r----- 1 oracle oinstall  45M Nov 12 00:00 1_245_757860476.dbf
-rw-r----- 1 oracle oinstall  45M Nov 13 07:26 1_246_757860476.dbf
-rw-r----- 1 oracle oinstall  45M Nov 14 07:27 1_247_757860476.dbf
--证明最大的seq为247，而恢复需要日志的seq为248，就是说需要应用未归档的redo log
--那么我们采用在sqlplus中恢复

RMAN> exit


Recovery Manager complete.


[oracle@ECP-UC-DB1 oradata]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Nov 14 13:21:24 2011

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter session set nls_date_format ="yyyy-mm-dd hh24:mi:ss"
  2  ;

Session altered.

SQL>  recover database until time '2011-11-14 12:49:10' using backup controlfile;
ORA-00279: change 11517136 generated at 11/14/2011 12:44:33 needed for thread 1
ORA-00289: suggestion : /opt/oracle/oradata/test/archivelog1_248_757860476.dbf
ORA-00280: change 11517136 for thread 1 is in sequence #248


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/opt/oracle/oradata/test_bak/redo01.log
ORA-00310: archived log contains sequence 247; sequence 248 required
ORA-00334: archived log: '/opt/oracle/oradata/test_bak/redo01.log'


SQL> recover database until time '2011-11-14 12:49:10' using backup controlfile;
ORA-00279: change 11517136 generated at 11/14/2011 12:44:33 needed for thread 1
ORA-00289: suggestion : /opt/oracle/oradata/test/archivelog1_248_757860476.dbf
ORA-00280: change 11517136 for thread 1 is in sequence #248


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/opt/oracle/oradata/test_bak/redo02.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
USERS
XFF
ODU
TEMP
XIFENFEI

8 rows selected.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------
/opt/oracle/oradata/test/system01.dbf
/opt/oracle/oradata/test/undotbs01.dbf
/opt/oracle/oradata/test/sysaux01.dbf
/opt/oracle/oradata/test/users01.dbf
/opt/oracle/oradata/test/user32g.dbf
/opt/oracle/oradata/test/xifenfei01.dbf
/opt/oracle/oradata/test/user02.dbf
/opt/oracle/oradata/test/odu02.dbf
/opt/oracle/oradata/test/odu01.dbf
/opt/oracle/oradata/test/odu03.dbf
/opt/oracle/oradata/test/xifenfei02.dbf
/opt/oracle/oradata/test/t_xifenfei01.dbf

12 rows selected.

SQL> select count(*) from chf.t_xifenfei;

  COUNT(*)
----------
     50476

使用flashback database找回被误删除表空间