分类目录归档：Oracle

asm备份元数据之md_backup和md_restore

发表于 2012 年 5 月 26 日 由 惜分飞

在11g的asm中增加了md_backup和md_restore命令,用来备份和还原asm的元数据信息
当前磁盘组相关信息

SQL> select PATH,b.NAME from v$asm_disk a,v$asm_diskgroup b where a.GROUP_NUMBER=b.GROUP_NUMBER;

PATH                                     NAME
---------------------------------------- ----------
/dev/oracleasm/disks/VOL2                DATA
/dev/oracleasm/disks/VOL1                DATA
/dev/oracleasm/disks/VOL4                XIFENFEI
/dev/oracleasm/disks/VOL3                XIFENFEI

md_backup操作

--备份所有mount磁盘组
ASMCMD> md_backup /tmp/xifenfei.md    
Disk group metadata to be backed up: DATA
Disk group metadata to be backed up: XIFENFEI
Current alias directory path: XFF/ARCHIVELOG
Current alias directory path: XFF/ARCHIVELOG/2012_04_30
Current alias directory path: XFF/ONLINELOG
Current alias directory path: rac-cluster/OCRFILE
Current alias directory path: XFF/ARCHIVELOG/2012_05_01
Current alias directory path: XFF/CONTROLFILE
Current alias directory path: XFF/ARCHIVELOG/2012_04_13
Current alias directory path: rac-cluster/ASMPARAMETERFILE
Current alias directory path: rac-cluster
Current alias directory path: XFF
Current alias directory path: XFF/ARCHIVELOG/2012_03_03
Current alias directory path: XFF/PARAMETERFILE
Current alias directory path: XFF/DATAFILE
Current alias directory path: ASM/DATAFILE
Current alias directory path: XFF/CONTROLFILE
Current alias directory path: XFF
Current alias directory path: XFF/ONLINELOG
Current alias directory path: XFF/TEMPFILE
Current alias directory path: ASM

--备份指定磁盘组
ASMCMD> md_backup /tmp/xifenfei_data.md -G DATA  
Disk group metadata to be backed up: DATA
Current alias directory path: XFF/ARCHIVELOG/2012_03_03
Current alias directory path: XFF/CONTROLFILE
Current alias directory path: XFF/ARCHIVELOG/2012_05_01
Current alias directory path: XFF/ARCHIVELOG
Current alias directory path: rac-cluster/OCRFILE
Current alias directory path: XFF/ARCHIVELOG/2012_05_24
Current alias directory path: XFF/ONLINELOG
Current alias directory path: XFF/ARCHIVELOG/2012_04_30
Current alias directory path: rac-cluster/ASMPARAMETERFILE
Current alias directory path: rac-cluster
Current alias directory path: XFF
Current alias directory path: XFF/ARCHIVELOG/2012_04_13

md_restore操作

--生产sql文件(未执行)
ASMCMD> md_restore -S  /tmp/get_dg_sql -G data /tmp/xifenfei_data.md
Current Diskgroup metadata being restored: DATA

破坏XIFENFEI磁盘组中的其中一个asm disk(/dev/oracleasm/disks/VOL3)
[root@rac1 tmp]#  dd if=/dev/zero of=/dev/sdb1 bs=4096 count=1
1+0 records in
1+0 records out
4096 bytes (4.1 kB) copied, 4.8629e-05 seconds, 84.2 MB/s

--尝试还原磁盘组(操作单位是磁盘组)
ASMCMD>  md_restore /tmp/xifenfei.md --silent -G xifenfei
Current Diskgroup metadata being restored: XIFENFEI
ASMCMD-9352: CREATE DISKGROUP failed
ORA-15018: diskgroup cannot be created
ORA-15033: disk /dev/oracleasm/disks/VOL4 belongs to diskgroup "XIFENFEI" (DBD ERROR: OCIStmtExecute)
--如果一个磁盘组中某个asm disk 出了问题,这种方法不能生效,甚至需要先dd 处理掉所有该磁盘组中的asm disk

总结说明
md_backup和md_restore是磁盘组级别的备份和还原,如果一个磁盘组的某个asm disk出现问题,使用这对命令解决起来还是很麻烦,甚至根本不可行(因为代价太大:要删除该磁盘组其他asm disk header,然后要重新还原所有数据文件),这样的情景下dd或者kfed的备份还是非常有必要,ASM DISK HEADER 备份与恢复.如果是一个磁盘组都损坏,需要还原磁盘组,这个时候这个命令非常的完美(至少比起dd和kfed方便很多).md_backup/md_restore和dd与kfed是互补的命令,而不是md_backup/md_restore出现使得dd和kfed在asm元数据的备份恢复上就没有用武之地.

发表在 Oracle ASM | 标签为 , | 3 条评论

sql_id和hash value的部分转换

发表于 2012 年 5 月 26 日 由 惜分飞

从oracle 10g开始引进了sql_id，在老版本的oralce中,要表明一条sql,一般使用hash value，而在10g及其以后版本中一般建议使用sql_id,从9i的sp和10g的awr中也可以看出.对于Library Cache对象,Oracle使用MD5算法进行哈希,生成一个128位的Hash Value,其中低32位作为HASH VALUE显示,SQL_ID则取了后64位.既然hash value和sql_id之前存在着这样的关系,那么我们就可以通过函数实现两者的部分转换(因为最终取值长度不同,所以不能完全转换)
1.查询sql_id和hash value

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')
  2  "www.xifenfei.com" from dual;

www.xifenfei.com
-------------------
2012-05-26 01:05:39

SQL> select sql_id,hash_value from v$sql where sql_text like
  2  'select * from dual';

SQL_ID        HASH_VALUE
------------- ----------
a5ks9fhw2v9s1  942515969

2.oracle自带函数转换sql_id to hash value

SQL> select dbms_utility.SQLID_TO_SQLHASH('a5ks9fhw2v9s1') hash_value FROM DUAL;


HASH_VALUE
----------
 942515969

3.自己编写函数sql_id to hash value

SQL> CREATE OR REPLACE FUNCTION sql_id_2_hash_value (sql_id VARCHAR2)
  2     RETURN NUMBER
  3  IS
  4     l_output   NUMBER := 0;
  5  BEGIN
  6         SELECT TRUNC (
  7                   MOD (
  8                      SUM (
  9                         (INSTR ('0123456789abcdfghjkmnpqrstuvwxyz',
 10                                 SUBSTR (LOWER (TRIM (sql_id)), LEVEL, 1))
 11                          - 1)
 12                         * POWER (32, LENGTH (TRIM (sql_id)) - LEVEL)),
 13                      POWER (2, 32)))
 14           INTO l_output
 15           FROM DUAL
 16     CONNECT BY LEVEL <= LENGTH (TRIM (sql_id));
 17     RETURN l_output;
 18  END;
 19  /

函数已创建。

SQL> select sql_id_2_hash_value('a5ks9fhw2v9s1') hash_value FROM DUAL;

HASH_VALUE
----------
 942515969

4.hash value 转换为部分 sql_id

SQL> CREATE OR REPLACE FUNCTION hash_value_2_sql_id (p_hash_value NUMBER)
  2     RETURN VARCHAR2
  3  IS
  4     l_output   VARCHAR2 (8) := '';
  5  BEGIN
  6     FOR i
  7        IN (    SELECT SUBSTR (
  8                          '0123456789abcdfghjkmnpqrstuvwxyz',
  9                          1
 10                          + FLOOR (
 11                               MOD (p_hash_value / (POWER (32, LEVEL - 1)), 32)),
 12                          1)
 13                          sqlidchar
 14                  FROM DUAL
 15            CONNECT BY LEVEL <= LN (p_hash_value) / LN (32)
 16              ORDER BY LEVEL DESC)
 17     LOOP
 18        l_output := l_output || i.sqlidchar;
 19     END LOOP;
 20
 21     RETURN l_output;
 22  END;
 23  /

函数已创建。

SQL> select hash_value_2_sql_id(942515969) from dual;

HASH_VALUE_2_SQL_ID(942515969)
--------------------------------------------------------
2v9s1

参考:http://blog.tanelpoder.com/2009/02/22/sql_id-is-just-a-fancy-representation-of-hash-value/

发表在 Oracle | 评论关闭

使用exp/imp 导入11g数据到9i

发表于 2012 年 5 月 25 日 由 惜分飞

现在有个需求,需要使用exp/imp导入11g的数据库数据到9i中,解决这个问题一般来说想到三种方法思路,一个个尝试(其实从高版本服务端支持低版本客户端的原则,可以大概的猜测出使用9i的客户端处理该问题)
方法1:导出导入都使用11g客户端

--11g客户端导出
[oracle@xifenfei ~]$ exp chf/xifenfei file=/tmp/t_xifenfei.dmp 
>log=/tmp/t_xifenfei.log tables=chf.t_xifenfei

Export: Release 11.2.0.3.0 - Production on Fri May 18 18:15:18 2012

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


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                     T_XIFENFEI          2 rows exported
Export terminated successfully without warnings.

--11g客户端导入
[oracle@xifenfei ~]$ imp chf/xifenfei@ora9i file=/tmp/t_xifenfei_11g.dmp 
>log=/tmp/t_xifenfei.log tables=chf.t_xifenfei

Import: Release 11.2.0.3.0 - Production on Fri May 18 18:17:24 2012

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


IMP-00058: ORACLE error 6550 encountered
ORA-06550: line 1, column 33:
PLS-00302: component 'SET_NO_OUTLINES' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
IMP-00000: Import terminated unsuccessfully

这个错误是版本不兼容导致:PLS-00302: component ‘SET_NO_OUTLINES’ must be declared

方法2:11g客户端导出,9i客户端导入

--11g客户端导出
[oracle@xifenfei ~]$ exp chf/xifenfei file=/tmp/t_xifenfei.dmp 
>log=/tmp/t_xifenfei.log tables=chf.t_xifenfei

Export: Release 11.2.0.3.0 - Production on Fri May 18 18:15:18 2012

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


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                     T_XIFENFEI          2 rows exported
Export terminated successfully without warnings.

--传输到9i
[oracle@xifenfei tmp]$ scp t_xifenfei.dmp 192.168.1.10:/tmp/
The authenticity of host '192.168.1.10 (192.168.1.10)' can't be established.
RSA key fingerprint is 3d:0c:d1:4b:45:bd:a3:f5:25:eb:4d:52:d2:32:03:69.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.1.10' (RSA) to the list of known hosts.
oracle@192.168.1.10's password: 
t_xifenfei.dmp                          100%   56KB  56.0KB/s   00:00    

--9i客户端导入
[oracle@xifenfei ~]$ imp chf/xifenfei file=/tmp/t_xifenfei.dmp tables=t_xifenfei

Import: Release 9.2.0.4.0 - Production on Thu May 24 23:32:18 2012

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

IMP-00010: not a valid export file, header failed verification
IMP-00000: Import terminated unsuccessfully
--版本不兼容(高版本的dump文件低版本不能识别)

方法3:9i客户端导出,9i客户端导入

--9i客户端导出
[oracle@xifenfei ~]$ exp chf/xifenfei@ora11g file=/tmp/t_xifenfei_11g.dmp 
>log=/tmp/t_xifenfei.log tables=chf.t_xifenfei

Export: Release 9.2.0.4.0 - Production on Thu May 24 23:37:20 2012

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                     T_XIFENFEI          2 rows exported
Export terminated successfully without warnings.

--9i客户端导入
[oracle@xifenfei log]$ imp chf/xifenfei file=/tmp/t_xifenfei_11g.dmp log=/tmp/xifenfei.log full=y

Import: Release 9.2.0.4.0 - Production on Fri May 25 03:22:14 2012

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character setSegmentation fault
--导入数据遇到setSegmentation fault异常终止

解决setSegmentation fault异常终止

--修改exu9defpswitches视图
[oracle@xifenfei ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri May 18 22:29:00 2012

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> CREATE OR REPLACE VIEW exu9defpswitches ( 
  2                  compflgs, nlslensem ) AS 
  3          SELECT  a.value, b.value 
  4          FROM    sys.v$parameter a, sys.v$parameter b 
  5          WHERE   a.name = 'plsql_code_type' AND 
  6                  b.name = 'nls_length_semantics' ;

View created.

--9i导出11g数据
[oracle@xifenfei tmp]$ exp chf/xifenfei@ora11g file=/tmp/t_xifenfei_11g.dmp 
>log=/tmp/xifenfei.log tables=t_xifenfei

Export: Release 9.2.0.4.0 - Production on Fri May 25 04:08:32 2012

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                     T_XIFENFEI          2 rows exported
Export terminated successfully without warnings.

--9i导入数据
[oracle@xifenfei tmp]$ imp chf/xifenfei file=/tmp/t_xifenfei_11g.dmp 
>log=/tmp/xifenfei.log tables=t_xifenfei

Import: Release 9.2.0.4.0 - Production on Fri May 25 04:08:53 2012

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing CHF's objects into CHF
. . importing table                   "T_XIFENFEI"          2 rows imported
Import terminated successfully without warnings.
--至此导入成功,完成了11gr2数据导入到9ir2中

通过一系列的实验证明,需要把11g的数据导入到9i中,需要使用9i的客户端进行,其中exu9defpswitches视图需要重建,否则会出现setSegmentation fault异常,导致导入失败.

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