Monthly Archives: 十一月 2011

ogg中Time Since Chkpt显示unknown解决

Posted on by

1、异常现象

[oracle@localhost ~]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.0.0 Build 078
Linux, x64, 64bit (optimized), Oracle 10 on Jul 28 2010 13:21:11

Copyright (C) 1995, 2010, Oracle and/or its affiliates. All rights reserved.



GGSCI (localhost.localdomain) 1> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     EXT-ECP     00:00:00      unknown     
EXTRACT     RUNNING     EXT-EDS     00:00:00      unknown     
EXTRACT     RUNNING     EXT-XZ      00:00:00      unknown     
EXTRACT     RUNNING     P-EDS       00:00:00      unknown     
EXTRACT     RUNNING     P-XZ        00:00:00      unknown     
REPLICAT    RUNNING     REP-BOS     00:00:00      unknown

2、尝试关闭异常进程重启

GGSCI (localhost.localdomain) 2> stop *

Sending STOP request to EXTRACT EXT-ECP ...

ERROR: sending message to EXTRACT EXT-ECP (Timeout waiting for message).

Sending STOP request to EXTRACT EXT-EDS ...

ERROR: sending message to EXTRACT EXT-EDS (Timeout waiting for message).

Sending STOP request to EXTRACT EXT-XZ ...

ERROR: sending message to EXTRACT EXT-XZ (Timeout waiting for message).

Sending STOP request to EXTRACT P-EDS ...

ERROR: sending message to EXTRACT P-EDS (Timeout waiting for message).

Sending STOP request to EXTRACT P-XZ ...

ERROR: sending message to EXTRACT P-XZ (Timeout waiting for message).

Sending STOP request to REPLICAT REP-BOS ...

ERROR: sending message to REPLICAT REP-BOS (Timeout waiting for message).

GGSCI (localhost.localdomain) 3> stop mgr!

Sending STOP request to MANAGER ...
Request processed.
Manager stopped.

GGSCI (localhost.localdomain) 4> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     STOPPED                                           
EXTRACT     RUNNING     EXT-ECP     00:00:00      unknown     
EXTRACT     RUNNING     EXT-EDS     00:00:00      unknown     
EXTRACT     RUNNING     EXT-XZ      00:00:00      unknown     
EXTRACT     RUNNING     P-EDS       00:00:00      unknown     
EXTRACT     RUNNING     P-XZ        00:00:00      unknown     
REPLICAT    RUNNING     REP-BOS     00:00:00      unknown     

GGSCI (localhost.localdomain) 5> kill EXT-ECP 

ERROR: Manager not currently running.

GGSCI (localhost.localdomain) 6> kill EXT-EDS 

ERROR: Manager not currently running.


GGSCI (localhost.localdomain) 7> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     STOPPED                                           
EXTRACT     RUNNING     EXT-ECP     00:00:00      unknown     
EXTRACT     RUNNING     EXT-EDS     00:00:00      unknown     
EXTRACT     RUNNING     EXT-XZ      00:00:00      unknown     
EXTRACT     RUNNING     P-EDS       00:00:00      unknown     
EXTRACT     RUNNING     P-XZ        00:00:00      unknown     
REPLICAT    RUNNING     REP-BOS     00:00:00      unknown   

GGSCI (localhost.localdomain) 8> exit  
--使用stop 进程，stop mgr，kill 进程都不能正常关闭这些进程

3、系统系统级别kill相关ogg进程

[oracle@localhost OGG]$ ps -ef|grep /opt/OGG
oracle    7479     1  0 Nov10 ?        00:03:31 /opt/OGG/extract PARAMFILE /opt/OGG/dirprm/ext-ecp.prm REPORTFILE /opt/OGG/dirrpt/EXT-ECP.rpt PROCESSID EXT-ECP USESUBDIRS
oracle    7480     1  0 Nov10 ?        00:02:30 /opt/OGG/extract PARAMFILE /opt/OGG/dirprm/ext-eds.prm REPORTFILE /opt/OGG/dirrpt/EXT-EDS.rpt PROCESSID EXT-EDS USESUBDIRS
oracle    7482     1  0 Nov10 ?        00:03:07 /opt/OGG/extract PARAMFILE /opt/OGG/dirprm/ext-xz.prm REPORTFILE /opt/OGG/dirrpt/EXT-XZ.rpt PROCESSID EXT-XZ USESUBDIRS
oracle    7483     1  0 Nov10 ?        00:00:01 /opt/OGG/extract PARAMFILE /opt/OGG/dirprm/p-eds.prm REPORTFILE /opt/OGG/dirrpt/P-EDS.rpt PROCESSID P-EDS USESUBDIRS
oracle    7485     1  0 Nov10 ?        00:00:03 /opt/OGG/replicat PARAMFILE /opt/OGG/dirprm/rep-bos.prm REPORTFILE /opt/OGG/dirrpt/REP-BOS.rpt PROCESSID REP-BOS USESUBDIRS
oracle    7518     1  0 Nov10 ?        00:00:01 ./server -p 7847 -k -l /opt/OGG/ggserr.log
oracle    7677     1  0 Nov10 ?        00:00:15 /opt/OGG/extract PARAMFILE /opt/OGG/dirprm/p-xz.prm REPORTFILE /opt/OGG/dirrpt/P-XZ.rpt PROCESSID P-XZ USESUBDIRS
oracle   25261 25112  0 12:48 pts/1    00:00:00 grep /opt/OGG
[oracle@localhost OGG]$ kill -9 7479 7480 7482 7483 7485  7518 7677
[oracle@localhost OGG]$ ps -ef|grep /opt/OGG
oracle   25264 25112  0 12:48 pts/1    00:00:00 grep /opt/OGG

4、重启所有ogg进程

[oracle@localhost OGG]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.1.1.0.0 Build 078
Linux, x64, 64bit (optimized), Oracle 10 on Jul 28 2010 13:21:11

Copyright (C) 1995, 2010, Oracle and/or its affiliates. All rights reserved.



GGSCI (localhost.localdomain) 1> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     STOPPED                                           
EXTRACT     ABENDED     EXT-ECP     00:00:00      unknown     
EXTRACT     ABENDED     EXT-EDS     00:00:00      unknown     
EXTRACT     ABENDED     EXT-XZ      00:00:00      unknown     
EXTRACT     ABENDED     P-EDS       00:00:00      unknown     
EXTRACT     ABENDED     P-XZ        00:00:00      unknown     
REPLICAT    ABENDED     REP-BOS     00:00:00      unknown     
--进程状态还是异常

GGSCI (localhost.localdomain) 2> start mgr

Manager started.


GGSCI (localhost.localdomain) 3> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     EXT-ECP     00:00:00      unknown     
EXTRACT     RUNNING     EXT-EDS     00:00:00      unknown     
EXTRACT     RUNNING     EXT-XZ      00:00:00      unknown     
EXTRACT     RUNNING     P-EDS       00:00:00      unknown     
EXTRACT     RUNNING     P-XZ        00:00:00      unknown     
REPLICAT    RUNNING     REP-BOS     00:00:00      unknown     
--进程起来了，但是Time Since Chkpt还是不正确

GGSCI (localhost.localdomain) 4> stop ext-ecp

Sending STOP request to EXTRACT EXT-ECP ...
Request processed.


GGSCI (localhost.localdomain) 5> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     STOPPED     EXT-ECP     unknown       00:00:02    
EXTRACT     RUNNING     EXT-EDS     00:00:00      unknown     
EXTRACT     RUNNING     EXT-XZ      00:00:00      unknown     
EXTRACT     RUNNING     P-EDS       00:00:00      unknown     
EXTRACT     RUNNING     P-XZ        00:00:00      unknown     
REPLICAT    RUNNING     REP-BOS     00:00:00      unknown     
--关闭EXT-ECP测试，状态正常

GGSCI (localhost.localdomain) 6> start ext-ecp

Sending START request to MANAGER ...
EXTRACT EXT-ECP starting


GGSCI (localhost.localdomain) 7> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     EXT-ECP     unknown       00:00:14    
EXTRACT     RUNNING     EXT-EDS     00:00:00      unknown     
EXTRACT     RUNNING     EXT-XZ      00:00:00      unknown     
EXTRACT     RUNNING     P-EDS       00:00:00      unknown     
EXTRACT     RUNNING     P-XZ        00:00:00      unknown     
REPLICAT    RUNNING     REP-BOS     00:00:00      unknown     
--Lag异常，等待恢复

GGSCI (localhost.localdomain) 8> stop ext-eds

Sending STOP request to EXTRACT EXT-EDS ...

Recovery is not complete.  This normal stop will wait and checkpoint recovery's 
work when recovery has finished. To force Extract to stop now, 
use the SEND EXTRACT EXT-EDS, FORCESTOP command.
--因为恢复没有完成导致该提示，可以忽略，等待

GGSCI (localhost.localdomain) 9> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     EXT-ECP     unknown       00:00:02    
EXTRACT     STOPPED     EXT-EDS     01:51:12      00:00:01    
EXTRACT     RUNNING     EXT-IM      00:00:00      1059:44:26  
EXTRACT     RUNNING     EXT-XZ      00:00:00      unknown     
EXTRACT     RUNNING     P-EDS       00:00:00      unknown     
EXTRACT     RUNNING     P-XZ        00:00:00      unknown     
REPLICAT    RUNNING     REP-BOS     00:00:00      unknown     


GGSCI (localhost.localdomain) 10> start ext-eds

Sending START request to MANAGER ...
EXTRACT EXT-EDS starting


GGSCI (localhost.localdomain) 11> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     EXT-ECP     99:53:02      00:00:01    
EXTRACT     RUNNING     EXT-EDS     01:51:12      00:00:10    
EXTRACT     RUNNING     EXT-XZ      00:00:00      unknown     
EXTRACT     RUNNING     P-EDS       00:00:00      unknown     
EXTRACT     RUNNING     P-XZ        00:00:00      unknown     
REPLICAT    RUNNING     REP-BOS     00:00:00      00:00:00    


GGSCI (localhost.localdomain) 12> stop ext-xz

Sending STOP request to EXTRACT EXT-XZ ...
Request processed.


GGSCI (localhost.localdomain) 13> start ext-xz

Sending START request to MANAGER ...
EXTRACT EXT-XZ starting

GGSCI (localhost.localdomain) 15> stop p-eds

Sending STOP request to EXTRACT P-EDS ...
Request processed.


GGSCI (localhost.localdomain) 16> start p-eds

Sending START request to MANAGER ...
EXTRACT P-EDS starting


GGSCI (localhost.localdomain) 17> stop p-xz

Sending STOP request to EXTRACT P-XZ ...
Request processed.


GGSCI (localhost.localdomain) 18> start p-xz

Sending START request to MANAGER ...
EXTRACT P-XZ starting


GGSCI (localhost.localdomain) 19> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     EXT-ECP     00:00:00      00:00:01    
EXTRACT     RUNNING     EXT-EDS     00:00:00      00:00:10    
EXTRACT     RUNNING     EXT-IM      00:00:00      1059:45:28  
EXTRACT     RUNNING     EXT-XZ      00:00:00      00:00:07    
EXTRACT     RUNNING     P-EDS       00:00:00      00:00:04    
EXTRACT     RUNNING     P-XZ        00:00:00      00:00:05    
REPLICAT    RUNNING     REP-BOS     00:00:00      00:00:05    
--重启所有异常进程，ogg工作正常

GGSCI (localhost.localdomain) 20>

5、总结处理步骤
强制关闭mgr,系统级别kill相关ogg进程,开启ogg主进程,重启相关进程

pl/sql的定义者与调用者

Posted on by

一、执行的schema不同，操作的对象也不同

C:\Users\XIFENFEI>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期日 11月 20 20:39:06 20

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


连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Productio
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options

SQL> create user xffa identified by xifenfei;

用户已创建。

SQL> grant connect,resource to xffa;

授权成功。

SQL> create user xffb identified by xifenfei;

用户已创建。

SQL> grant connect,resource to xffb;

授权成功。

SQL> conn xffa/xifenfei
已连接。
xffa>create table tmp(str varchar2(50));

表已创建。

xffa>insert into tmp values
  2  ('my name is xffa');

已创建 1 行。

xffa>commit;

提交完成。

xffa>create or replace procedure definer_proc as
  2      begin
  3        for x in (select sys_context('userenv', 'current_user') current_user,

  4                         sys_context('userenv', 'session_user') session_user,

  5                         sys_context('userenv', 'current_schema') current_sch
ema,
  6                         str
  7                    from tmp) loop
  8        dbms_output.put_line('Current User:   ' || x.current_user);
  9        dbms_output.put_line('Session User:   ' || x.session_user);
 10        dbms_output.put_line('Current Schema: ' || x.current_schema);
 11        dbms_output.put_line('Tables Value:    ' || x.str);
 12      end loop;
 13    end;
 14    /

过程已创建。

xffa>create or replace procedure invoker_proc AUTHID CURRENT_USER as
  2      begin
  3        for x in (select sys_context('userenv', 'current_user') current_user,

  4                         sys_context('userenv', 'session_user') session_user,

  5                         sys_context('userenv', 'current_schema') current_sch
ema,
  6                         str
  7                    from tmp) loop
  8        dbms_output.put_line('Current User:   ' || x.current_user);
  9        dbms_output.put_line('Session User:   ' || x.session_user);
 10        dbms_output.put_line('Current Schema: ' || x.current_schema);
 11        dbms_output.put_line('Tables Value:    ' || x.str);
 12      end loop;
 13    end;
 14    /

过程已创建。

xffa>grant execute on definer_proc to xffb;

授权成功。

xffa>grant execute on invoker_proc to xffb;

授权成功。

xffa>set serveroutput on
xffa>exec definer_proc;
Current User:   XFFA
Session User:   XFFA
Current Schema: XFFA
Tables Value:    my name is xffa

PL/SQL 过程已成功完成。

xffa>exec invoker_proc;
Current User:   XFFA
Session User:   XFFA
Current Schema: XFFA
Tables Value:    my name is xffa

PL/SQL 过程已成功完成。

xffa>conn xffb/xifenfei
已连接。
xffb>exec xffa.definer_proc;

PL/SQL 过程已成功完成。

xffb>set serveroutput on
xffb>exec xffa.definer_proc;
Current User:   XFFA
Session User:   XFFB
Current Schema: XFFA
Tables Value:    my name is xffa

PL/SQL 过程已成功完成。
--除了session是当前用户的，其他都是这个过程所属用户

xffb>exec xffa.invoker_proc;
BEGIN xffa.invoker_proc; END;

*
第 1 行出现错误:
ORA-00942: 表或视图不存在
ORA-06512: 在 "XFFA.INVOKER_PROC", line 3
ORA-06512: 在 line 1
--根据这个提示，很容易知道是tmp表不存在，也就是说明这个没有调用xffa.tmp表

xffb>create table tmp(str varchar2(50));

表已创建。

xffb>insert into tmp values
  2  ('my name is xffb');

已创建 1 行。

xffb>commit;

提交完成。

xffb>exec xffa.invoker_proc;
Current User:   XFFB
Session User:   XFFB
Current Schema: XFFB
Tables Value:    my name is xffb

PL/SQL 过程已成功完成。
--这个可以看出当时调用者的时候，执行的完全是当前用户下面的对象

在定义者(definer)权限下，执行的用户操作的schema为定义者，所操作的对象是定义者在编译时指定的对象。
在调用者(invoker)权限下，执行的用户操作的schema为当前用户，所操作的对象是当前模式下的对象。

二、执行的权限不同

xffb>create or replace procedure createtbl_definer as
  2      begin
  3        execute immediate 'create table xff_definer (id number)';
  4      end;
  5      /

过程已创建。

xffb>create or replace procedure createtbl_invoker AUTHID CURRENT_USER  as
  2      begin
  3        execute immediate 'create table xff_invoker (id number)';
  4      end;
  5      /

xffb>grant execute on createtbl_definer to xffa;

授权成功。

xffb>grant execute on createtbl_invoker to xffa;

授权成功。

xffb>exec createtbl_definer;
BEGIN createtbl_definer; END;

*
第 1 行出现错误:
ORA-01031: 权限不足
ORA-06512: 在 "XFFB.CREATETBL_DEFINER", line 3
ORA-06512: 在 line 1
--提示权限不足，很明显是缺少创建表的权限

xffb>exec createtbl_invoker;

PL/SQL 过程已成功完成。

xffb>desc xff_invoker;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------

 ID                                                 NUMBER
--调用者创建成功

xffb>conn xffa/xifenfei
已连接。
xffa>exec xffb.createtbl_definer;
BEGIN xffb.createtbl_definer; END;

*
第 1 行出现错误:
ORA-01031: 权限不足
ORA-06512: 在 "XFFB.CREATETBL_DEFINER", line 3
ORA-06512: 在 line 1
--定义者同样提示没有权限创建表，通过1中的试验，我们知道
--定义者执行的这个过程定义者的对象，也就是说，是因为xffb无权创建表导致该提示

xffa>exec xffb.createtbl_invoker;

PL/SQL 过程已成功完成。

xffa>desc xff_invoker;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------

 ID                                                 NUMBER

xffa>conn / as sysdba
已连接。
SQL>select owner,table_name from dba_tables where table_name=upper('xff_invoker');

OWNER                          TABLE_NAME
------------------------------ ------------------------------
XFFB                           XFF_INVOKER
XFFA                           XFF_INVOKER
--xffa用户执行成功，并且在自己的schema下面创建了表，证明了1的正确

SQL>GRANT CREATE TABLE TO XFFB;

授权成功。

SQL>conn xffb/xifenfei
已连接。
xffb>exec createtbl_definer;

PL/SQL 过程已成功完成。

xffb>desc xff_definer;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------

 ID                                                 NUMBER
--说明直接授权是的定义者操作成功，但是角色(resource)不能使其操作成功

xffb>conn xffa/xifenfei
已连接。
xffa>exec xffb.createtbl_definer;
BEGIN xffb.createtbl_definer; END;

*
第 1 行出现错误:
ORA-00955: 名称已由现有对象使用
ORA-06512: 在 "XFFB.CREATETBL_DEFINER", line 3
ORA-06512: 在 line 1
--因为定义者执行的是过程创建者的对象，因为XFFB.CREATETBL_DEFINER已经创建成功

在定义者(definer)权限下，当前用户的权限为角色无效情况下所拥有的权限。
在调用者(invoker)权限下，当前用户的权限为当前所拥有的权限(含角色)。

三、执行的效率不同
在定义者(definer)权限下，过程被静态编译静态执行(相对而言)，所执行sql语句在共享区池中是可被共享使用的
在调用者(invoker)权限下，过程静态编译，但动态执行，虽然执行的语句相同，但不同用户执行，其sql语句在共享池中并不能共享。

参考:http://www.itpub.net/thread-935634-1-1.html

rman从多份备份中还原操作

Posted on by

1、现象重现

RMAN> shutdown immediate

database dismounted
Oracle instance shut down

RMAN> startup

connected to target database (not started)
Oracle instance started
database mounted
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 11/18/2011 14:40:40
ORA-01157: cannot identify/lock data file 11 - see DBWR trace file
ORA-01110: data file 11: '/opt/oracle/oradata/test/xifenfei03.dbf'

RMAN> restore datafile 11;

Starting restore at 2011-11-18 14:41:04
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK

channel ORA_DISK_1: restoring datafile 00011
input datafile copy recid=13 stamp=767543949 filename=/tmp/11.dbf
destination for restore of datafile 00011: /opt/oracle/oradata/test/xifenfei03.dbf
ORA-19505: failed to identify file "/tmp/11.dbf"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-19600: input file is datafile-copy 13 (/tmp/11.dbf)
ORA-19601: output file is datafile 11 (/opt/oracle/oradata/test/xifenfei03.dbf)
failover to previous backup

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00011 to /opt/oracle/oradata/test/xifenfei03.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:00:02
Finished restore at 2011-11-18 14:41:08

虽然整个过程datafile 11恢复成功了，但是在恢复过程中，先是去读取/tmp/11.dbf的copy文件，读取这个文件失败，然后继续使用/tmp/test_full_01mrkqdh_1_1来恢复数据文件，为什么会出现这样的情况呢？

2、原因分析

RMAN> list copy of datafile 11;


List of Datafile Copies
Key     File S Completion Time     Ckp SCN    Ckp Time            Name
------- ---- - ------------------- ---------- ------------------- ----
13      11   A 2011-11-18 14:39:09 11517136   2011-11-14 12:44:33 /tmp/11.dbf
--确实存在datafile 11的copy文件/tmp/11.dbf,并且有效
--checkpoint time为:2011-11-14 12:44:33，创建时间为:2011-11-18 14:39:09

RMAN> list backup of datafile 11;


List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time    
------- ---- -- ---------- ----------- ------------ -------------------
1       Full    9.32G      DISK        00:04:24     2011-11-14 12:48:57
        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20111114T124433
        Piece Name: /tmp/test_full_01mrkqdh_1_1
  List of Datafiles in backup set 1
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  11      Full 11517136   2011-11-14 12:44:33 /opt/oracle/oradata/test/xifenfei03.dbf
--backupset中也有datafile 11
--checkpoint time为:2011-11-14 12:44:33，创建时间为:2011-11-14 12:48:57

--通过比较这两个关于datafile 11的备份时间，rman自动选择了创建时间比较新的备份恢复

RMAN> crosscheck copy of datafile 11;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK
validation failed for datafile copy
datafile copy filename=/tmp/11.dbf recid=13 stamp=767543949
Crosschecked 1 objects

RMAN> list copy of datafile 11;

List of Datafile Copies
Key     File S Completion Time     Ckp SCN    Ckp Time            Name
------- ---- - ------------------- ---------- ------------------- ----
13      11   X 2011-11-18 14:39:09 11517136   2011-11-14 12:44:33 /tmp/11.dbf
--通过检测发现/tmp/11.dbf是无效的，所以rman继续使用backupset进行恢复datafile 11

在有些时候，当rman对中关于一个对象的备份有多个(备份时间不一致),rman会从最新备份的开始还原，如果第一个失败，使用下一个备份，但是在有些时候可能第一个失败后，rman不自动使用下一个，这个时候的处理思路是:使用crosscheck backup/copy检测无效的备份，删除掉，继续执行

imp乱码分析–解决建议

Posted on by

最近有位朋友一直在为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文件的一致，让转换发生在导入客户端和数据库服务器间(要求:编码可以相互转换)