分类目录归档：Oracle备份恢复

imp乱码分析–解决建议

发表于 2011 年 11 月 17 日 由 惜分飞

最近有位朋友一直在为exp/imp操作的乱码问题纠结，总是搞不清楚为什么，而且经常莫名其妙的出现乱码，为此我做了一个实验，来说明这个问题的处理思路
一、准备工作

C:\Users\XIFENFEI>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期四 11月 17 18:43:00 2011

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


连接到:
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

SQL> create table TEST_EXP
  2  (
  3    A1 NUMBER,
  4    A2 VARCHAR2(10 CHAR),
  5    A3 VARCHAR2(10),
  6    A4 NVARCHAR2(10),
  7    A5 CHAR(10),
  8    A6 NCHAR(10)
  9  );

表已创建。

SQL> comment on column TEST_EXP.A1
  2    is '数字类型----惜分飞';

注释已创建。

SQL> comment on column TEST_EXP.A2
  2    is 'varchar类型1----惜分飞';

注释已创建。

SQL> comment on column TEST_EXP.A3
  2    is 'varchar类型2----惜分飞';

注释已创建。

SQL> comment on column TEST_EXP.A4
  2    is 'nvarchar类型----惜分飞';

注释已创建。

SQL> comment on column TEST_EXP.A5
  2    is 'char类型----惜分飞';

注释已创建。

SQL> comment on column TEST_EXP.A6
  2    is 'nchar类型----惜分飞';

注释已创建。

SQL> insert into test_exp values(1,'xifenfeicf','xifenfeicf','xff','xifenfei','xifenfei');

已创建 1 行。

SQL> insert into test_exp values(1,'惜分飞来向大家问好啦',
2    '杭州惜分飞','杭州惜分飞','杭州惜分飞','杭州惜分飞');

已创建 1 行。

SQL> commit;

提交完成。

SQL> col parameter for a30
SQL> col value for a20
SQL> select * FROM v$nls_parameters WHERE parameter LIKE '%CHARACTERSET%';

PARAMETER                      VALUE
------------------------------ --------------------
NLS_CHARACTERSET               ZHS16GBK
NLS_NCHAR_CHARACTERSET         AL16UTF16

SQL> exit
从 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 断开

C:\Users\XIFENFEI>exp chf/xifenfei tables=test_exp 
file=d:\test_exp.dmp log=d:\test_exp.log

Export: Release 11.2.0.1.0 - Production on 星期四 11月 17 18:46:10 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


连接到: 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
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集

即将导出指定的表通过常规路径...
. . 正在导出表                        TEST_EXP导出了           2 行
成功终止导出, 没有出现警告。

二、使用AL32UTF8编码导入

C:\Users\XIFENFEI>set NLS_LANG=american_america.AL32UTF8

C:\Users\XIFENFEI>imp chf/xifenfei tables=test_exp 
file=d:/test_exp.dmp log=d:/test_exp.log fromuser=chf touser=chf

Import: Release 11.2.0.1.0 - Production on Thu Nov 17 19:24:58 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Produc
tion
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
export client uses ZHS16GBK character set (possible charset conversion)
. importing CHF's objects into CHF
. . importing table                     "TEST_EXP"          2 rows imported
Import terminated successfully without warnings.
--注意此处提示，编码发生了转换
--导出来文件编码为：ZHS16GBK 
--现在客户端编码为：AL32UTF8 
--导入服务器编码为：ZHS16GBK 
--现在的转换是ZHS16GBK-->AL32UTF8 -->ZHS16GBK
--其中ZHS16GBK-->AL32UTF8说成转换也许不太合适
--(因为ZHS16GBK是已经生产的dmp文件中数据的编码，而AL32UTF8是导入客户端的编码，这个到底是否转换待定)

C:\Users\XIFENFEI>sqlplus chf/xifenfei

SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 17 19:25:58 2011

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


Connected to:
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

SQL> col comments for a30
SQL> SELECT  COLUMN_NAME,comments FROM DBA_COL_COMMENTS WHERE owner='CHF' AND TABLE_NAME='TEST_EXP';

COLUMN_NAME                    COMMENTS
------------------------------ ------------------------------
A1                             数字类型----惜分飞
A2                             varchar类型1----惜分飞
A3                             varchar类型2----惜分飞
A4                             nvarchar类型----惜分飞
A5                             char类型----惜分飞
A6                             nchar类型----惜分飞

6 rows selected.

SQL>select * from test_exp;

        A1 A2                   A3         A4                   A5         A6
---------- -------------------- ---------- -------------------- ---------- --------------------
         1 xifenfeicf           xifenfeicf xff                  xifenfei   xifenfei
         1 惜分飞来向大家问好啦 杭州惜分飞 杭州惜分飞           杭州惜分飞 杭州惜分飞

--在新窗口查询，编码修改客户端编码造成影响

三、使用US7ASCII编码导入

C:\Users\XIFENFEI>set NLS_LANG=american_america.US7ASCII

C:\Users\XIFENFEI>imp chf/xifenfei tables=test_exp 
file=d:/test_exp.dmp log=d:/test_exp.log fromuser=chf touser=chf

Import: Release 11.2.0.1.0 - Production on Thu Nov 17 19:35:10 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Produc
tion
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
export client uses ZHS16GBK character set (possible charset conversion)
. importing CHF's objects into CHF
. . importing table                     "TEST_EXP"          2 rows imported
Import terminated successfully without warnings.

SQL> col comments for a30
SQL> SELECT  COLUMN_NAME,comments FROM DBA_COL_COMMENTS WHERE owner='CHF' AND TABLE_NAME='TEST_EXP';

COLUMN_NAM COMMENTS
---------- ------------------------------
A1         ????----???
A2         varchar??1----???
A3         varchar??2----???
A4         nvarchar??----???
A5         char??----???
A6         nchar??----???

6 rows selected.
--sqlplus和plsql dev中均为乱码

SQL> select * from test_exp;

        A1 A2         A3         A4         A5         A6
---------- ---------- ---------- ---------- ---------- ----------
         1 xifenfeicf xifenfeicf xff        xifenfei   xifenfei
         1 ?????????? ?????      ?????      ?????      ?????
--在plsql dev中查询是正常，sqlplus中不正常
--这里为什么plsql dev中能够显示正常，而comment在plsql dev中显示不正常，还有待研究
--说明：这里由于ZHS16GBK转换为US7ASCII的过程不能识别汉字，所以会导致汉字变成了问号

四、使用ZHS16GBK编码

C:\Users\XIFENFEI>set NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK

C:\Users\XIFENFEI>imp chf/xifenfei tables=test_exp 
file=d:/test_exp.dmp log=d:/test_exp.log fromuser=chf touser=chf

Import: Release 11.2.0.1.0 - Production on 星期四 11月 17 20:26:39 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


连接到: 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

经由常规路径由 EXPORT:V11.02.00 创建的导出文件
已经完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的导入
. 正在将 CHF 的对象导入到 CHF
. . 正在导入表                      "TEST_EXP"导入了           2 行
成功终止导入, 没有出现警告。
--注意提示，没有发生任何的编码转换

QL> col comments for a30
SQL> SELECT  COLUMN_NAME,comments FROM DBA_COL_COMMENTS WHERE owner='CHF' AND TABLE_NAME='TEST_EXP';

COLUMN_NAME                    COMMENTS
------------------------------ ------------------------------
A1                             数字类型----惜分飞
A2                             varchar类型1----惜分飞
A3                             varchar类型2----惜分飞
A4                             nvarchar类型----惜分飞
A5                             char类型----惜分飞
A6                             nchar类型----惜分飞

6 rows selected.

SQL>select * from test_exp;

        A1 A2                   A3         A4                   A5         A6
---------- -------------------- ---------- -------------------- ---------- --------------------
         1 xifenfeicf           xifenfeicf xff                  xifenfei   xifenfei
         1 惜分飞来向大家问好啦 杭州惜分飞 杭州惜分飞           杭州惜分飞 杭州惜分飞

五、原因分析,解决建议
在导入过程中，最多会发生三次编码转换:
1、执行exp时，数据库中数据的编码会转换为导出客户端编码
2、执行imp时，dmp文件的编码转换为导入客户端编码
3、导入客户端编码转换为目标端数据库的数据库编码

在exp/imp操作的过程中，经常出现乱码的原因就是编码的相互转换的过程中出现了丢失或者相互不能转换导致。要解决这个问题，最好的办法就是通过NLS_LANG的灵活设置，减少编码转换的次数(如果相邻的转换操作编码一致，那么不会发生编码转换，如试验中的ZHS16GBK编码测试，就没有转换发生)，或者使得相互的转换能够兼容，可以最大程度的减少乱码的出现。
如果已经有了exp导出的dmp文件，然后在导入的过程中，出现乱码，一般的处理建议是nls_lang的编码设置和dmp文件的一致，让转换发生在导入客户端和数据库服务器间(要求:编码可以相互转换)

发表在 逻辑备份/恢复 | 4 条评论

rman恢复spfile最快捷方式

发表于 2011 年 11 月 14 日 由 惜分飞

一、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备份/恢复 | 2 条评论

使用rman找回被误删除表空间

发表于 2011 年 11 月 14 日 由 惜分飞

一、案例说明
利用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找回被误删除表空间

发表在 rman备份/恢复 | 评论关闭