关于wallet加密的几个测试

TDE中比较核心部分为wallet,对于这部分进行测试,对钱包加密有更加深刻的理解.
wallet随库启动本质

[oracle@localhost wallets]$ ls -ltr
total 8
-rw------- 1 oracle oinstall 3637 Jan  5 23:11 ewallet.p12
[oracle@localhost wallets]$
[oracle@localhost wallets]$
[oracle@localhost wallets]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 5 23:16:13 2016
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from v$encryption_wallet;
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------
file
/home/u01/oracle/network/wallets
OPEN
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 5044088832 bytes
Fixed Size                  2261928 bytes
Variable Size            1040190552 bytes
Database Buffers         3992977408 bytes
Redo Buffers                8658944 bytes
Database mounted.
Database opened.
SQL> select * from v$encryption_wallet;
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------
file
/home/u01/oracle/network/wallets
CLOSED
[oracle@localhost wallets]$ orapki wallet create -pwd xifenfei123 -wallet /home/u01/oracle/network/wallets  -auto_login
Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.
[oracle@localhost wallets]$ ls -ltr
total 16
-rw------- 1 oracle oinstall 3637 Jan  5 23:11 ewallet.p12
-rw------- 1 oracle oinstall 3715 Jan  5 23:20 cwallet.sso
[oracle@localhost wallets]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 5 23:21:04 2016
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from v$encryption_wallet;
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------
file
/home/u01/oracle/network/wallets
OPEN
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 5044088832 bytes
Fixed Size                  2261928 bytes
Variable Size            1040190552 bytes
Database Buffers         3992977408 bytes
Redo Buffers                8658944 bytes
Database mounted.
Database opened.
SQL> select * from v$encryption_wallet;
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------
file
/home/u01/oracle/network/wallets
OPEN
[oracle@localhost wallets]$ ls
cwallet.sso  ewallet.p12
[oracle@localhost wallets]$ rm cwallet.sso
[oracle@localhost wallets]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 5 23:30:55 2016
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from v$encryption_wallet;
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------
file
/home/u01/oracle/network/wallets
OPEN
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 5044088832 bytes
Fixed Size                  2261928 bytes
Variable Size            1040190552 bytes
Database Buffers         3992977408 bytes
Redo Buffers                8658944 bytes
Database mounted.
Database opened.
SQL> select * from v$encryption_wallet;
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------
file
/home/u01/oracle/network/wallets
CLOSED
SQL>  alter system set wallet open identified by "xifenfei123";
System altered.
SQL>  select * from v$encryption_wallet;
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------
file
/home/u01/oracle/network/wallets
OPEN

通过测试我们发现当钱包中含cwallet.sso之时,wallet就会随库启动而open,当cwallet.sso被删除之后,wallet无法随库启动而open,由此可见,wallet是否随库启动而open取决于cwallet.sso文件.

修改wallet密码

[oracle@localhost wallets]$ orapki wallet change_pwd -wallet /home/u01/oracle/network/wallets
> -oldpwd xifenfei123 -newpwd www.xifenfei.com
Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.
[oracle@localhost wallets]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 5 23:35:01 2016
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter system set wallet close identified by "xifenfei123";
System altered.
SQL> select * from v$encryption_wallet;
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------
file
/home/u01/oracle/network/wallets
CLOSED
SQL> alter system set wallet open identified by "xifenfei123";
alter system set wallet open identified by "xifenfei123"
*
ERROR at line 1:
ORA-28353: failed to open wallet
SQL> alter system set wallet open identified by "www.xifenfei.com";
System altered.
SQL> select * from v$encryption_wallet;
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------
file
/home/u01/oracle/network/wallets
OPEN
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost wallets]$ ls -ltr
total 8
-rw------- 1 oracle oinstall 3638 Jan  5 23:34 ewallet.p12

wallet文件丢失

[oracle@localhost wallets]$ mv ewallet.p12 ewallet.p12_bak
[oracle@localhost wallets]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 5 23:36:55 2016
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter system set wallet close identified by "www.xifenfei.com";
System altered.
SQL> alter system set wallet open identified by "www.xifenfei.com";
alter system set wallet open identified by "www.xifenfei.com"
*
ERROR at line 1:
ORA-28367: wallet does not exist
SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "www.xifenfei.com";
ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "www.xifenfei.com"
*
ERROR at line 1:
ORA-28362: master key not found
SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "www.xifenfei";
ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "www.xifenfei"
*
ERROR at line 1:
ORA-28353: failed to open wallet

如果wallet文件丢失,没有办法open钱包,也就是说加密数据无法读取.备份ewallet.p12文件非常重要

Oracle TDE 简单测试

从ORACLE 10.2开始提供了一个新的特性，让你只需要做如下动作：你可以不写一行代码，只需要声明你需要加密某列。当用户插入数据的时候，数据库透明的加密数据然后存储加密后的数据。同样的，当用户读取数据时，数据库自动进行节目。由于加解密操作对应用程序来说都是透明的，不需要应用程序修改代码，因此这个特性就叫做：透明数据加密（TDE）。
TDE实施
sqlnet.ora中增加

ENCRYPTION_WALLET_LOCATION=
   (SOURCE=(METHOD=FILE)(METHOD_DATA=
      (DIRECTORY=/home/u01/oracle/network/wallets)))

重启监听

lsnrctl stop
lsnrctl start

配置钱包

[oracle@localhost wallets]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 PrXIFENFEIction on Tue Jan 5 14:43:18 2016
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit PrXIFENFEIction
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "welcome1";
System altered.
SQL> select * from v$encryption_wallet;
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------
file
/home/u01/oracle/network/wallets
OPEN
[oracle@localhost wallets]$ ls -ltr
total 8
-rw-r--r-- 1 oracle oinstall 2845 Jan  5 14:43 ewallet.p12

TDE加密测试

SQL> conn XIFENFEI/oracle
Connected.
SQL>  create table CUST_PAYMENT_INFO
  2                     (first_name varchar2(11),
  3                      last_name varchar2(10),
                    order_number number(13),
                    CREDIT_CARD_NUMBER varchar2(20) ENCRYPT NO SALT);  4    5
Table created.
SQL>  insert into cust_payment_info values ('Jon', 'Oldfield', 10001, '5446-9597-0881-2985');
1 row created.
SQL> insert into cust_payment_info values ('Chris', 'White', 10002, '5122-3580-4608-2560');
1 row created.
SQL>  insert into cust_payment_info values ('Alan', 'Squire', 10003, '5595-9689-4375-7920');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from USER_ENCRYPTED_COLUMNS;
TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------
ENCRYPTION_ALG                SAL INTEGRITY_AL
----------------------------- --- ------------
CUST_PAYMENT_INFO              CREDIT_CARD_NUMBER
AES 192 bits key              NO  SHA-1
SQL> conn / as sysdba
Connected.
SQL> select * from XIFENFEI.cust_payment_info;
FIRST_NAME  LAST_NAME  ORDER_NUMBER CREDIT_CARD_NUMBER
----------- ---------- ------------ --------------------
Jon         Oldfield          10001 5446-9597-0881-2985
Chris       White             10002 5122-3580-4608-2560
Alan        Squire            10003 5595-9689-4375-7920
SQL> ALTER SYSTEM SET WALLET close IDENTIFIED BY  "welcome1";
System altered.
SQL> select * from XIFENFEI.cust_payment_info;
select * from XIFENFEI.cust_payment_info
                  *
ERROR at line 1:
ORA-28365: wallet is not open

验证TDE加密数据

--创建测试数据
SQL> create table XIFENFEI.CUST_PAYMENT_INFO2
  2                     (first_name varchar2(11),
  3                      last_name varchar2(10),
  4                      order_number number(13),
                    CREDIT_CARD_NUMBER varchar2(20));  5
Table created.
SQL> insert into XIFENFEI.cust_payment_info2 values ('Jon', 'Oldfield', 10001, '5446-9597-0881-2985');
insert into XIFENFEI.cust_payment_info2 values ('Chris', 'White', 10002, '5122-3580-4608-2560');
1 row created.
SQL>
1 row created.
SQL> insert into XIFENFEI.cust_payment_info2 values ('Alan', 'Squire', 10003, '5595-9689-4375-7920');
1 row created.
SQL> commit;
Commit complete.
SQL> select CREDIT_CARD_NUMBER,rowid,
  2  dbms_rowid.rowid_relative_fno(rowid)rel_fno,
  3  dbms_rowid.rowid_block_number(rowid)blockno,
dbms_rowid.rowid_row_number(rowid) rowno
  4    5  from XIFENFEI.cust_payment_info2;
CREDIT_CARD_NUMBER   ROWID                 REL_FNO    BLOCKNO      ROWNO
-------------------- ------------------ ---------- ---------- ----------
5446-9597-0881-2985  AAAZXdAAEAAAmgUAAA          4     157716          0
5122-3580-4608-2560  AAAZXdAAEAAAmgUAAB          4     157716          1
5595-9689-4375-7920  AAAZXdAAEAAAmgUAAC          4     157716          2
SQL> select name from v$datafile where file#=4;
NAME
--------------------------------------------------------------------------------
/home/u01/oradata/qsng/users01.dbf
SQL> alter system checkpoint;
System altered.
--使用bbed直接查看数据文件中数据
[oracle@localhost oracle]$ bbed
Password:
BBED: Release 2.0.0.0.0 - Limited PrXIFENFEIction on Tue Jan 5 22:06:59 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set filename '/home/u01/oradata/qsng/users01.dbf'
        FILENAME        /home/u01/oradata/qsng/users01.dbf
BBED> set blocksize 8192
        BLOCKSIZE       8192
BBED> set block 157716
        BLOCK#          157716
BBED> map
 File: /home/u01/oradata/qsng/users01.dbf (0)
 Block: 157716                                Dba:0x00000000
------------------------------------------------------------
 KTB Data Block (Table/Cluster)
 struct kcbh, 20 bytes                      @0
 struct ktbbh, 72 bytes                     @20
 struct kdbh, 14 bytes                      @100
 struct kdbt[1], 4 bytes                    @114
 sb2 kdbr[3]                                @118
 ub1 freespace[7943]                        @124
 ub1 rowdata[121]                           @8067
 ub4 tailchk                                @8188
BBED> p *kdbr[0]
rowdata[80]
-----------
ub1 rowdata[80]                             @8147     0x2c
BBED> x /rccnc
rowdata[80]                                 @8147
-----------
flag@8147: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8148: 0x01
cols@8149:    4
col    0[3] @8150: Jon
col    1[8] @8154: Oldfield
col    2[4] @8163: 10001
col   3[19] @8168: 5446-9597-0881-2985
--证明没有加密数据文件中数据,可以直接查看
SQL> ALTER TABLE XIFENFEI.CUST_PAYMENT_INFO2 MODIFY (CREDIT_CARD_NUMBER ENCRYPT NO SALT);
Table altered.
SQL> insert into XIFENFEI.cust_payment_info2 values ('xifenfei', 'XFF', 10004, 'WWW.XIFENFEI.COM');
1 row created.
SQL> COMMIT;
Commit complete.
SQL>  alter system checkpoint;
System altered.
SQL> select CREDIT_CARD_NUMBER,rowid,
  2  dbms_rowid.rowid_relative_fno(rowid)rel_fno,
  3  dbms_rowid.rowid_block_number(rowid)blockno,
dbms_rowid.rowid_row_number(rowid) rowno
  4    5  from XIFENFEI.cust_payment_info2;
CREDIT_CARD_NUMBER   ROWID                 REL_FNO    BLOCKNO      ROWNO
-------------------- ------------------ ---------- ---------- ----------
5446-9597-0881-2985  AAAZXdAAEAAAmgUAAA          4     157716          0
5122-3580-4608-2560  AAAZXdAAEAAAmgUAAB          4     157716          1
5595-9689-4375-7920  AAAZXdAAEAAAmgUAAC          4     157716          2
WWW.XIFENFEI.COM     AAAZXdAAEAAAmgWAAA          4     157718          0
BBED> set filename '/home/u01/oradata/qsng/users01.dbf'
        FILENAME        /home/u01/oradata/qsng/users01.dbf
BBED> set blocksize 8192
        BLOCKSIZE       8192
BBED> set block 157716
        BLOCK#          157716
BBED> map
 File: /home/u01/oradata/qsng/users01.dbf (0)
 Block: 157716                                Dba:0x00000000
------------------------------------------------------------
 KTB Data Block (Table/Cluster)
 struct kcbh, 20 bytes                      @0
 struct ktbbh, 72 bytes                     @20
 struct kdbh, 14 bytes                      @100
 struct kdbt[1], 4 bytes                    @114
 sb2 kdbr[3]                                @118
 ub1 freespace[7723]                        @124
 ub1 rowdata[341]                           @7847
 ub4 tailchk                                @8188
BBED> p *kdbr[0]
rowdata[146]
------------
ub1 rowdata[146]                            @7993     0x2c
BBED> x /rccnc
rowdata[146]                                @7993
------------
flag@7993: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@7994: 0x02
cols@7995:    4
col    0[3] @7996: Jon
col    1[8] @8000: Oldfield
col    2[4] @8009: 10001
col   3[52] @8014: g隐.1Y.>.焦右.l.0赌鉣X.^._K泅Dn&.蜥._sR^....
BBED> set block 157718
        BLOCK#          157718
BBED> map
 File: /home/u01/oradata/qsng/users01.dbf (0)
 Block: 157718                                Dba:0x00000000
------------------------------------------------------------
 KTB Data Block (Table/Cluster)
 struct kcbh, 20 bytes                      @0
 struct ktbbh, 72 bytes                     @20
 struct kdbh, 14 bytes                      @100
 struct kdbt[1], 4 bytes                    @114
 sb2 kdbr[1]                                @118
 ub1 freespace[7994]                        @120
 ub1 rowdata[74]                            @8114
 ub4 tailchk                                @8188
BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0]                              @8114     0x2c
BBED> x /rccnc
rowdata[0]                                  @8114
----------
flag@8114: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8115: 0x01
cols@8116:    4
col    0[8] @8117: xifenfei
col    1[3] @8126: XFF
col    2[4] @8130: 10004
col   3[52] @8135: 籕G蠖璆]Qu]..o._証?.湮`.C..)S....&...Z

通过测试可以发现两点:
1. TDE加密之后,数据无法通过数据文件获取,也就是说在没有钱包的情况下,就算有数据文件,也无法直接获取数据
2. 当对表进行alter语句设置加密之后,原表中数据已经进行加密,而且后续插入数据也加密

TDE加密后数据导出问题

--TDE加密之后,数据无法通过exp导出
[oracle@localhost network]$ exp XIFENFEI/oracle file=/tmp/1.dmp tables=CUST_PAYMENT_INFO1
Export: Release 11.2.0.4.0 - PrXIFENFEIction on Tue Jan 5 16:09:54 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit PrXIFENFEIction
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)
About to export specified tables via Conventional Path ...
EXP-00107: Feature (COLUMN ENCRYPTION) of column CREDIT_CARD_NUMBER in table
  XIFENFEI.CUST_PAYMENT_INFO1 is not supported. The table will not be exported.
Export terminated successfully with warnings.
--TDE加密之后,数据使用expdp导出需要使用
[oracle@localhost network]$ expdp XIFENFEI/oracle dumpfile=1.dmp tables=CUST_PAYMENT_INFO1
Export: Release 11.2.0.4.0 - PrXIFENFEIction on Tue Jan 5 16:10:29 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit PrXIFENFEIction
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "XIFENFEI"."SYS_EXPORT_TABLE_01":  XIFENFEI/******** dumpfile=1.dmp tables=CUST_PAYMENT_INFO1
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "XIFENFEI"."CUST_PAYMENT_INFO1"                  6.406 KB       3 rows
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
Master table "XIFENFEI"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for XIFENFEI.SYS_EXPORT_TABLE_01 is:
  /home/u01/admin/qsng/dpdump/1.dmp
Job "XIFENFEI"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at Tue Jan 5 16:10:43 2016 elapsed 0 00:00:11
--指定ENCRYPTION_MODE=TRANSPARENT使用钱包加密方式
[oracle@localhost oracle]$ expdp XIFENFEI/oracle dumpfile=2.dmp ENCRYPTION_MODE=TRANSPARENT
> ENCRYPTION=ALL tables=CUST_PAYMENT_INFO2 reuse_dumpfiles=yes
Export: Release 11.2.0.4.0 - PrXIFENFEIction on Tue Jan 5 22:45:02 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit PrXIFENFEIction
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "XIFENFEI"."SYS_EXPORT_TABLE_01":  XIFENFEI/******** dumpfile=2.dmp ENCRYPTION_MODE=TRANSPARENT
  ENCRYPTION=ALL tables=CUST_PAYMENT_INFO2 reuse_dumpfiles=yes
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "XIFENFEI"."CUST_PAYMENT_INFO2"                  6.453 KB       4 rows
Master table "XIFENFEI"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for XIFENFEI.SYS_EXPORT_TABLE_01 is:
  /home/u01/admin/qsng/dpdump/2.dmp
Job "XIFENFEI"."SYS_EXPORT_TABLE_01" successfully completed at Tue Jan 5 22:45:06 2016 elapsed 0 00:00:03
--使用ENCRYPTION_MODE=PASSWORD方式导出
[oracle@localhost oracle]$ expdp XIFENFEI/oracle dumpfile=2.dmp ENCRYPTION_MODE=PASSWORD
>ENCRYPTION_PASSWORD=www.xifenfei.com  ENCRYPTION=ALL tables=CUST_PAYMENT_INFO2 reuse_dumpfiles=yes
Export: Release 11.2.0.4.0 - PrXIFENFEIction on Tue Jan 5 22:46:17 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit PrXIFENFEIction
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "XIFENFEI"."SYS_EXPORT_TABLE_01":  XIFENFEI/******** dumpfile=2.dmp ENCRYPTION_MODE=PASSWORD
  ENCRYPTION_PASSWORD=******** ENCRYPTION=ALL tables=CUST_PAYMENT_INFO2 reuse_dumpfiles=yes
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "XIFENFEI"."CUST_PAYMENT_INFO2"                  6.453 KB       4 rows
Master table "XIFENFEI"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for XIFENFEI.SYS_EXPORT_TABLE_01 is:
  /home/u01/admin/qsng/dpdump/2.dmp
Job "XIFENFEI"."SYS_EXPORT_TABLE_01" successfully completed at Tue Jan 5 22:46:21 2016 elapsed 0 00:00:03

钱包随库一起open

[oracle@localhost wallets]$ orapki wallet create -pwd welcome1 -wallet /home/u01/oracle/network/wallets  -auto_login
Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.
--注意随库open之后钱包无法关闭
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 5044088832 bytes
Fixed Size                  2261928 bytes
Variable Size            1040190552 bytes
Database Buffers         3992977408 bytes
Redo Buffers                8658944 bytes
Database mounted.
Database opened.
SQL> select * from v$encryption_wallet;
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------
file
/home/u01/oracle/network/wallets
OPEN
SQL> alter system set wallet close identified by "welcome1";
alter system set wallet close identified by "welcome1"
*
ERROR at line 1:
ORA-28365: wallet is not open
SQL>  alter system set wallet close;
System altered.
SQL> select * from v$encryption_wallet;
WRL_TYPE
--------------------
WRL_PARAMETER
--------------------------------------------------------------------------------
STATUS
------------------
file
/home/u01/oracle/network/wallets
OPEN

vSphere ssh登陆配置

vSphere(esxi)在某些情况下需要通过ssh登陆,比如由于某种原因,你无法通过控制台直接拷贝文件下来,需要人工ssh登陆进行操作.通过查询资料基本上确定如下方法可以实现需求:
1. vSphere启动ssh服务
F2–>[System Customization]–>[Troubleshooting Options]–>[Enable SSH] 或者通过VMware vSphere Client实现
ssh-esxi1

2. 配置连接方式
认证方式:选择challenge-response (keyboard-interactive)
ssh-esxi2

3. 登陆验证结果
ssh-esxi3

VMware vSphere6.0 初试

去年(2015年）12月份的一个恢复,由于是虚拟化环境,中间发生几次虚拟机故障导致恢复无法继续,甚至从头再来,2016年我提醒自己,我可以不动虚拟化,但是我也绝对不能被人忽悠,因此国庆假期,自己初装了下VMware vSphere6.0.安装为文件VMware-VMvisor-Installer-6.0.0.update01-3073146.x86_64.iso,直接可以到vm官网注册下载.为了测试方便,我使用VMware Workstation虚拟了一个200G的硬盘空间,20G的内存的虚拟机用来安装ESXI 6.0(也就是vSphere6.0).
安装相关
安装成功截图
1

按F2进行配置
2

按F11关机/重启
3

浏览提示网页
4

安装VMware vSphere Client
5

登录VMware vSphere Client
7

解决几个问题
激活key(不然提示60天试用期)
主机–>配置–>已获许可的功能–>编辑
8

上传文件到esxi主机中
主机–>配置–>存储器–>选择需要上传文件到的datastore–>右键属性–>浏览数据存储–>选择需要目录–>上传/下载
10
11

对datastore进行扩容（先在esxi物理机器中增加存储空间)
主机–>配置–>存储器–>属性–>增加–>选择未使用磁盘–>下一步(N个)
14
17

type为TEMPORARY,name为file#.block#对象重现和清理

在数据库的使用中你可能注意到了,在某些情况下,可能出现某些segment的type为TEMPORARY，名字为file#.block#的格式的对象.这里我通过试验重现该对象并且给出清理方法
创建表空间/用户

SQL> create tablespace test datafile
  2  '/u01/app/oracle/oracle/product/10.2.0/db_1/oradata/ora10g/test01.dbf'
  3  size 128M autoextend on;
Tablespace created.
SQL> create user chf identified by oracle;
User created.
SQL> grant dba to chf;
Grant succeeded.

创建模拟对象
本次通过一个index对象经过一些列操作使其变为我们需要的TEMPORARY对象

SQL> conn chf/oracle
Connected.
SQL> create table t_xifenfei tablespace test
  2  as select * from dba_objects;
Table created.
SQL> create index idx_t_xifenfei on t_xifenfei(object_id) tablespace test;
Index created.
SQL> select header_file,header_block from DBA_SEGMENTS
  2   where segment_name='IDX_T_XIFENFEI' AND OWNER='CHF';
HEADER_FILE HEADER_BLOCK
----------- ------------
          5          267

破坏segment header
这里通过dd 把该block重置为空块,然后rman检查坏块,证明我们处理正常把index的segment header弄成了空块

SQL> select name from v$datafile where file#=5;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oracle/product/10.2.0/db_1/oradata/ora10g/test01.dbf
SQL> conn / as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
[oracle@xffdbrh5 ~]$ dd if=/dev/zero
>of=/u01/app/oracle/oracle/product/10.2.0/db_1/oradata/ora10g/test01.dbf
>bs=8192 count=1 seek=267 conv=notrunc
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 0.000100548 seconds, 81.5 MB/s
[oracle@xffdbrh5 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Dec 21 16:30:56 2015
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area  901775360 bytes
Fixed Size                  2024944 bytes
Variable Size             138414608 bytes
Database Buffers          759169024 bytes
Redo Buffers                2166784 bytes
Database mounted.
Database opened.
[oracle@xffdbrh5 ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Mon Dec 21 16:33:00 2015
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
connected to target database: ORA10G (DBID=4205072872)
RMAN> backup validate check logical datafile 5;
Starting backup at 21-DEC-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=144 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00005 name=/u01/app/oracle/oracle/product/10.2.0/db_1/oradata/ora10g/test01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 21-DEC-15
RMAN> exit
Recovery Manager complete.
[oracle@xffdbrh5 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Dec 21 16:33:50 2015
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> select * from V$DATABASE_BLOCK_CORRUPTION ;
     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
         5        267          1                  0 ALL ZERO

index segment header异常
index异常后,通过rebuild报ora-08103错误

SQL> alter index chf.idx_t_xifenfei rebuild;
alter index chf.idx_t_xifenfei rebuild
*
ERROR at line 1:
ORA-08103: object no longer exists

重现TEMPORARY对象
通过删除index,然后发现了我们久违的TEMPORARY类型的对象出现

SQL> drop index chf.idx_t_xifenfei;
Index dropped.
SQL> col SEGMENT_NAME for a20
SQL>  select segment_name,segment_type from user_segments;
SEGMENT_NAME         SEGMENT_TYPE
-------------------- ------------------
T_XIFENFEI           TABLE
5.267                TEMPORARY

清理TEMPORARY对象

SQL> CONN / AS SYSDBA
Connected.
SQL> exec dbms_space_admin.segment_corrupt('TEST',5,267);
PL/SQL procedure successfully completed.
SQL> exec dbms_space_admin.segment_drop_corrupt('TEST',5,267);
PL/SQL procedure successfully completed.
SQL> select segment_name,segment_type from DBA_SEGMENTS WHERE OWNER='CHF';
SEGMENT_NAME         SEGMENT_TYPE
-------------------- ------------------
T_XIFENFEI           TABLE

通过试验证明:当segment header异常,并且删除该对象,就会出现type为TEMPORARY，名字为file#.block#的格式的对象.我们可以通过dbms_space_admin包处理该类异常对象,让他们彻底从数据库中清除掉

oracle asm disk格式化恢复—格式化为ntfs文件系统

接到网友请求,由于操作人员粗心把asm disk的磁盘映射到另外的机器上,并且格式化为了win ntfs文件系统,导致asm 磁盘组异常,数据库无法使用
asm 日志报ORA-27072错

Mon Nov 30 12:00:13 2015
Errors in file c:\app\administrator\diag\asm\+asm\+asm\trace\+asm_gmon_868.trc:
ORA-27070: async read/write failed
OSD-04008: WriteFile() 失败, 无法写入文件
O/S-Error: (OS 21) 设备未就绪。
WARNING: IO Failed. group:1 disk(number.incarnation):0.0xf0f0bbfb disk_path:\\.\ORCLDISKDATA0
	 AU:1 disk_offset(bytes):2093056 io_size:4096 operation:Write type:synchronous
	 result:I/O error process_id:868
WARNING: disk 0.4042308603 (DATA_0000) not responding to heart beat
ERROR: too many offline disks in PST (grp 1)
WARNING: Disk DATA_0000 in mode 0x7f will be taken offline
Mon Nov 30 12:00:13 2015
NOTE: process 576:37952 initiating offline of disk 0.4042308603 (DATA_0000) with mask 0x7e in group 1
WARNING: Disk DATA_0000 in mode 0x7f is now being taken offline
NOTE: initiating PST update: grp = 1, dsk = 0/0xf0f0bbfb, mode = 0x15
kfdp_updateDsk(): 5
kfdp_updateDskBg(): 5
Errors in file c:\app\administrator\diag\asm\+asm\+asm\trace\+asm_gmon_868.trc:
ORA-27072: File I/O error
WARNING: IO Failed. group:1 disk(number.incarnation):1.0xf0f0bbfc disk_path:\\.\ORCLDISKDATA1
	 AU:1 disk_offset(bytes):1048576 io_size:4096 operation:Read type:synchronous
	 result:I/O error process_id:868
Errors in file c:\app\administrator\diag\asm\+asm\+asm\trace\+asm_gmon_868.trc:
ORA-27072: File I/O error
WARNING: IO Failed. group:1 disk(number.incarnation):1.0xf0f0bbfc disk_path:\\.\ORCLDISKDATA1
	 AU:1 disk_offset(bytes):1052672 io_size:4096 operation:Read type:synchronous
	 result:I/O error process_id:868
Errors in file c:\app\administrator\diag\asm\+asm\+asm\trace\+asm_gmon_868.trc:
ORA-27072: File I/O error
WARNING: IO Failed. group:1 disk(number.incarnation):2.0xf0f0bbfd disk_path:\\.\ORCLDISKDATA2
	 AU:1 disk_offset(bytes):1048576 io_size:4096 operation:Read type:synchronous
	 result:I/O error process_id:868
Errors in file c:\app\administrator\diag\asm\+asm\+asm\trace\+asm_gmon_868.trc:
ORA-27072: File I/O error
WARNING: IO Failed. group:1 disk(number.incarnation):2.0xf0f0bbfd disk_path:\\.\ORCLDISKDATA2
	 AU:1 disk_offset(bytes):1052672 io_size:4096 operation:Read type:synchronous
	 result:I/O error process_id:868
Errors in file c:\app\administrator\diag\asm\+asm\+asm\trace\+asm_gmon_868.trc:
ORA-27072: File I/O error
WARNING: IO Failed. group:1 disk(number.incarnation):3.0xf0f0bbfe disk_path:\\.\ORCLDISKDATA3
	 AU:1 disk_offset(bytes):1048576 io_size:4096 operation:Read type:synchronous
	 result:I/O error process_id:868
Errors in file c:\app\administrator\diag\asm\+asm\+asm\trace\+asm_gmon_868.trc:
ORA-27072: File I/O error
WARNING: IO Failed. group:1 disk(number.incarnation):3.0xf0f0bbfe disk_path:\\.\ORCLDISKDATA3
	 AU:1 disk_offset(bytes):1052672 io_size:4096 operation:Read type:synchronous
	 result:I/O error process_id:868
Errors in file c:\app\administrator\diag\asm\+asm\+asm\trace\+asm_gmon_868.trc:
ORA-27072: File I/O error
WARNING: IO Failed. group:1 disk(number.incarnation):4.0xf0f0bbff disk_path:\\.\ORCLDISKDATA4
	 AU:1 disk_offset(bytes):1048576 io_size:4096 operation:Read type:synchronous
	 result:I/O error process_id:868
Errors in file c:\app\administrator\diag\asm\+asm\+asm\trace\+asm_gmon_868.trc:
ORA-27072: File I/O error
WARNING: IO Failed. group:1 disk(number.incarnation):4.0xf0f0bbff disk_path:\\.\ORCLDISKDATA4
	 AU:1 disk_offset(bytes):1052672 io_size:4096 operation:Read type:synchronous
	 result:I/O error process_id:868
Errors in file c:\app\administrator\diag\asm\+asm\+asm\trace\+asm_gmon_868.trc:
ORA-27072: File I/O error
WARNING: IO Failed. group:1 disk(number.incarnation):6.0xf0f0bc01 disk_path:\\.\ORCLDISKDATA6
	 AU:1 disk_offset(bytes):1048576 io_size:4096 operation:Read type:synchronous
	 result:I/O error process_id:868
Errors in file c:\app\administrator\diag\asm\+asm\+asm\trace\+asm_gmon_868.trc:
ORA-27072: File I/O error
WARNING: IO Failed. group:1 disk(number.incarnation):6.0xf0f0bc01 disk_path:\\.\ORCLDISKDATA6
	 AU:1 disk_offset(bytes):1052672 io_size:4096 operation:Read type:synchronous
	 result:I/O error process_id:868
Errors in file c:\app\administrator\diag\asm\+asm\+asm\trace\+asm_gmon_868.trc:
ORA-27072: File I/O error
WARNING: IO Failed. group:1 disk(number.incarnation):7.0xf0f0bc02 disk_path:\\.\ORCLDISKDATA7
	 AU:1 disk_offset(bytes):1048576 io_size:4096 operation:Read type:synchronous
	 result:I/O error process_id:868
Errors in file c:\app\administrator\diag\asm\+asm\+asm\trace\+asm_gmon_868.trc:
ORA-27072: File I/O error
WARNING: IO Failed. group:1 disk(number.incarnation):7.0xf0f0bc02 disk_path:\\.\ORCLDISKDATA7
	 AU:1 disk_offset(bytes):1052672 io_size:4096 operation:Read type:synchronous
	 result:I/O error process_id:868
ERROR: no PST quorum in group: required 1, found 0
WARNING: Disk DATA_0000 in mode 0x7f offline aborted
Mon Nov 30 12:00:14 2015
SQL> alter diskgroup DATA dismount force /* ASM SERVER */
NOTE: cache dismounting (not clean) group 1/0xBB404B03 (DATA)
Mon Nov 30 12:00:14 2015
NOTE: halting all I/Os to diskgroup DATA
Mon Nov 30 12:00:14 2015
NOTE: LGWR doing non-clean dismount of group 1 (DATA)
NOTE: LGWR sync ABA=367.7265 last written ABA 367.7265
NOTE: cache dismounted group 1/0xBB404B03 (DATA)
kfdp_dismount(): 6
kfdp_dismountBg(): 6
NOTE: De-assigning number (1,0) from disk (\\.\ORCLDISKDATA0)
NOTE: De-assigning number (1,1) from disk (\\.\ORCLDISKDATA1)
NOTE: De-assigning number (1,2) from disk (\\.\ORCLDISKDATA2)
NOTE: De-assigning number (1,3) from disk (\\.\ORCLDISKDATA3)
NOTE: De-assigning number (1,4) from disk (\\.\ORCLDISKDATA4)
NOTE: De-assigning number (1,5) from disk (\\.\ORCLDISKDATA5)
NOTE: De-assigning number (1,6) from disk (\\.\ORCLDISKDATA6)
NOTE: De-assigning number (1,7) from disk (\\.\ORCLDISKDATA7)
SUCCESS: diskgroup DATA was dismounted
NOTE: cache deleting context for group DATA 1/-1153414397
SUCCESS: alter diskgroup DATA dismount force /* ASM SERVER */
ERROR: PST-initiated MANDATORY DISMOUNT of group DATA

这里的asm日志很明显由于asm disk无法正常访问,报ORA-27072错误,磁盘组强制dismount.

分析磁盘情况
asm-disk1
asm-disk2

通过与客户沟通,确定从I到O本为asm disk 被格式化为了NTFS文件系统的磁盘,结合asmtool分析可以发现还有一个asm disk没有格式化掉,该磁盘组中一个共有8个磁盘格式化掉了7个.

通过kfed分析磁盘信息

C:\Users\Administrator>kfed read '\\.\J:'
kfbh.endian:                        235 ; 0x000: 0xeb
kfbh.hard:                           82 ; 0x001: 0x52
kfbh.type:                          144 ; 0x002: *** Unknown Enum ***
kfbh.datfmt:                         78 ; 0x003: 0x4e
kfbh.block.blk:               542328404 ; 0x004: T=0 NUMB=0x20534654
kfbh.block.obj:                 2105376 ; 0x008: TYPE=0x0 NUMB=0x2020
kfbh.check:                        2050 ; 0x00c: 0x00000802
kfbh.fcn.base:                        0 ; 0x010: 0x00000000
kfbh.fcn.wrap:                    63488 ; 0x014: 0x0000f800
kfbh.spare1:                   16711743 ; 0x018: 0x00ff003f
kfbh.spare2:                       2048 ; 0x01c: 0x00000800
ERROR!!!, failed to get the oracore error message
C:\Users\Administrator>kfed read '\\.\J:' blkn=2
kfbh.endian:                         70 ; 0x000: 0x46
kfbh.hard:                           73 ; 0x001: 0x49
kfbh.type:                           76 ; 0x002: *** Unknown Enum ***
kfbh.datfmt:                         69 ; 0x003: 0x45
kfbh.block.blk:                  196656 ; 0x004: T=0 NUMB=0x30030
kfbh.block.obj:                33563364 ; 0x008: TYPE=0x0 NUMB=0x22e4
kfbh.check:                           0 ; 0x00c: 0x00000000
kfbh.fcn.base:                    65537 ; 0x010: 0x00010001
kfbh.fcn.wrap:                    65592 ; 0x014: 0x00010038
kfbh.spare1:                        416 ; 0x018: 0x000001a0
kfbh.spare2:                       1024 ; 0x01c: 0x00000400
ERROR!!!, failed to get the oracore error message
C:\Users\Administrator>kfed read '\\.\J:' blkn=256
kfbh.endian:                          1 ; 0x000: 0x01
kfbh.hard:                          130 ; 0x001: 0x82
kfbh.type:                           13 ; 0x002: KFBTYP_PST_NONE
kfbh.datfmt:                          1 ; 0x003: 0x01
kfbh.block.blk:              2147483648 ; 0x004: T=1 NUMB=0x0
kfbh.block.obj:              2147483654 ; 0x008: TYPE=0x8 NUMB=0x6
kfbh.check:                    17662471 ; 0x00c: 0x010d8207
kfbh.fcn.base:                        0 ; 0x010: 0x00000000
kfbh.fcn.wrap:                        0 ; 0x014: 0x00000000
kfbh.spare1:                          0 ; 0x018: 0x00000000
kfbh.spare2:                          0 ; 0x01c: 0x00000000
ERROR!!!, failed to get the oracore error message
C:\Users\Administrator>kfed read '\\.\J:' blkn=510
kfbh.endian:                          1 ; 0x000: 0x01
kfbh.hard:                          130 ; 0x001: 0x82
kfbh.type:                            1 ; 0x002: KFBTYP_DISKHEAD
kfbh.datfmt:                          1 ; 0x003: 0x01
kfbh.block.blk:                     254 ; 0x004: T=0 NUMB=0xfe
kfbh.block.obj:              2147483654 ; 0x008: TYPE=0x8 NUMB=0x6
kfbh.check:                   717599272 ; 0x00c: 0x2ac5b228
kfbh.fcn.base:                        0 ; 0x010: 0x00000000
kfbh.fcn.wrap:                        0 ; 0x014: 0x00000000
kfbh.spare1:                          0 ; 0x018: 0x00000000
kfbh.spare2:                          0 ; 0x01c: 0x00000000
kfdhdb.driver.provstr:    ORCLDISKDATA6 ; 0x000: length=13
kfdhdb.driver.reserved[0]:   1096040772 ; 0x008: 0x41544144
kfdhdb.driver.reserved[1]:           54 ; 0x00c: 0x00000036
kfdhdb.driver.reserved[2]:            0 ; 0x010: 0x00000000
kfdhdb.driver.reserved[3]:            0 ; 0x014: 0x00000000
kfdhdb.driver.reserved[4]:            0 ; 0x018: 0x00000000
kfdhdb.driver.reserved[5]:            0 ; 0x01c: 0x00000000
…………

通过分析,可以确定asm disk的备份block没有被覆盖,原则上可以通过备份block实现磁盘组恢复,从而减小了恢复难度

kfed恢复磁盘头

C:\Users\Administrator> kfed repair '\\.\J:'
C:\Users\Administrator>kfed read '\\.\J:'
kfbh.endian:                          1 ; 0x000: 0x01
kfbh.hard:                          130 ; 0x001: 0x82
kfbh.type:                            1 ; 0x002: KFBTYP_DISKHEAD
kfbh.datfmt:                          1 ; 0x003: 0x01
kfbh.block.blk:                     254 ; 0x004: T=0 NUMB=0xfe
kfbh.block.obj:              2147483654 ; 0x008: TYPE=0x8 NUMB=0x6
kfbh.check:                   717599272 ; 0x00c: 0x2ac5b228
kfbh.fcn.base:                        0 ; 0x010: 0x00000000
kfbh.fcn.wrap:                        0 ; 0x014: 0x00000000
kfbh.spare1:                          0 ; 0x018: 0x00000000
kfbh.spare2:                          0 ; 0x01c: 0x00000000
kfdhdb.driver.provstr:    ORCLDISKDATA6 ; 0x000: length=13
kfdhdb.driver.reserved[0]:   1096040772 ; 0x008: 0x41544144
kfdhdb.driver.reserved[1]:           54 ; 0x00c: 0x00000036
kfdhdb.driver.reserved[2]:            0 ; 0x010: 0x00000000
kfdhdb.driver.reserved[3]:            0 ; 0x014: 0x00000000
kfdhdb.driver.reserved[4]:            0 ; 0x018: 0x00000000
kfdhdb.driver.reserved[5]:            0 ; 0x01c: 0x00000000
…………

确定asm disk相关信息
对于7个被格式化的磁盘都进行类似处理之后,通过工具看到相关磁盘信息如下
asm-disk3

恢复处理
根据ntfs的文件系统分布,我们可以知道,虽然asm disk header备份block正常,但是asm disk中间部分依旧有不少au会被破坏
ntfs

这样的情况,不合适直接使用工具拷贝出来datafile(由于可能记录block的字典正好被覆盖,导致拷贝出来的文件异常,在恢复过程中我们也做了试验小文件拷贝ok,大文件拷贝然后使用dbv检测有很多坏块),我们采用工具(asm disk header 彻底损坏恢复)从底层扫描直接重组出来asm disk中的数据文件,然后结合拷贝出来的控制文件,redo文件,参数文件,然后通过重命名相关路径,然后直接open数据库

Q:\>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on 星期三 1月 22 16:08:18 2014
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> set pages 1000
SQL> col name for a100
SQL> set lines 150
SQL> select file#,name from v$datafile;
     FILE# NAME
---------- --------------------------------------------------------------------
         1 +DATA/vspdb/datafile/system.256.778520603
         2 +DATA/vspdb/datafile/sysaux.257.778520603
         3 +DATA/vspdb/datafile/undotbs1.258.778520603
         4 +DATA/vspdb/datafile/users.259.778520603
         5 +DATA/vspdb/datafile/vsp_tbs.293.779926097
        …………
       147 +DATA/vspdb/datafile/index_dg.418.864665747
       148 +DATA/vspdb/datafile/data_dg.419.864667053
       149 +DATA/vspdb/datafile/vsp_mm_tbs.420.890410367
       150 +DATA/vspdb/datafile/vsp_mm_tbs.421.890410457
SQL> select member from v$logfile;
MEMBER
-------------------------------------------------------------------------------------
+DATA/vspdb/onlinelog/group_7.263.862676593
+DATA/vspdb/onlinelog/group_7.262.862676601
+DATA/vspdb/onlinelog/group_4.410.862652291
+DATA/vspdb/onlinelog/group_4.411.862652307
+DATA/vspdb/onlinelog/group_5.412.862653715
+DATA/vspdb/onlinelog/group_5.413.862653727
+DATA/vspdb/onlinelog/group_6.414.862676425
+DATA/vspdb/onlinelog/group_6.415.862676433
重命名数据文件和redo文件，open数据库
SQL> recover database;
完成介质恢复。
SQL> alter database open;
数据库已更改。
已用时间:  00: 00: 04.51

由于部分block被覆盖,使用空块代替,导致数据访问到该block就会出现ora-8103(模拟普通ORA-08103并解决,模拟极端ORA-08103并解决)错误,对于该种对象,最简单处理方法就是直接通过dul抽出来数据然后truncate table重新导入数据,当然如果你想彻底安全逻辑方式重建库最靠谱
如果您遇到此类情况,无法解决请联系我们，提供专业ORACLE数据库恢复技术支持
Phone:17813235971    Q Q:107644445QQ咨询惜分飞    E-Mail:dba@xifenfei.com

ORA-10562 故障恢复—allow 1 corruption

朋友数据库由于存储变动,导致数据库瞬间hang住,然后直接crash,之后无法正常启动,请求技术支持.
数据库报ORA-00600[2131]错误
不能mount,可以通过重建控制文件解决

Mon Nov 30 20:35:38 2015
alter database mount
Mon Nov 30 20:35:38 2015
NOTE: Loaded library: System
Mon Nov 30 20:35:38 2015
SUCCESS: diskgroup DATADG was mounted
Mon Nov 30 20:35:38 2015
NOTE: dependency between database xifenfei and diskgroup resource ora.DATADG.dg is established
Errors in file /u01/app/oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_ora_26450.trc  (incident=3032256):
ORA-00600: internal error code, arguments: [2131], [33], [32], [], [], [], [], [], [], [], [], []
ORA-600 signalled during: alter database mount...

尝试recover数据库

Mon Nov 30 20:45:53 2015
ALTER DATABASE RECOVER  database
Media Recovery Start
 started logmerger process
Parallel Media Recovery started with 80 slaves
Mon Nov 30 20:45:56 2015
Recovery of Online Redo Log: Thread 2 Group 11 Seq 617 Reading mem 0
  Mem# 0: +DATADG/xifenfei/redo011.log
Recovery of Online Redo Log: Thread 1 Group 4 Seq 5410 Reading mem 0
  Mem# 0: +DATADG/xifenfei/redo04.log
Recovery of Online Redo Log: Thread 1 Group 5 Seq 5411 Reading mem 0
  Mem# 0: +DATADG/xifenfei/redo05.log
Mon Nov 30 20:46:07 2015
Recovery of Online Redo Log: Thread 1 Group 6 Seq 5412 Reading mem 0
  Mem# 0: +DATADG/xifenfei/redo06.log
Mon Nov 30 20:46:13 2015
Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0xC] [PC:0x95FB502, kdxlin()+4088] [flags: 0x0, count: 1]
Errors in file /u01/app/oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_pr13_30480.trc  (incident=3032568):
ORA-07445: 出现异常错误: 核心转储 [kdxlin()+4088] [SIGSEGV] [ADDR:0xC] [PC:0x95FB502] [Address not mapped to object] []
Mon Nov 30 20:46:17 2015
Sweep [inc][3032568]: completed
Sweep [inc2][3032568]: completed
Mon Nov 30 20:46:31 2015
Slave exiting with ORA-10562 exception
Errors in file /u01/app/oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_pr13_30480.trc:
ORA-10562: Error occurred while applying redo to data block (file# 2, block# 165054)
ORA-10564: tablespace SYSAUX
ORA-01110: 数据文件 2: '+DATADG/xifenfei/datafile/sysaux.265.861925867'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 271
ORA-00607: 当更改数据块时出现内部错误
ORA-00602: 内部编程异常错误
ORA-07445: 出现异常错误: 核心转储 [kdxlin()+4088] [SIGSEGV] [ADDR:0xC] [PC:0x95FB502] [Address not mapped to object] []
Mon Nov 30 20:46:31 2015
Recovery Slave PR13 previously exited with exception 10562
Mon Nov 30 20:46:33 2015
Checker run found 28 new persistent data failures
Mon Nov 30 20:46:35 2015
Media Recovery failed with error 448
Errors in file /u01/app/oracle/diag/rdbms/xifenfei/xifenfei1/trace/xifenfei1_pr00_30400.trc:
ORA-00283: 恢复会话因错误而取消
ORA-00448: 后台进程正常结束
ORA-10562 signalled during: ALTER DATABASE RECOVER  database  ...

通过这里可以看到,由于在recover 操作之时,由于某种原因redo的数据无法apply到file 2 block 165054中,导致数据库recover database失败.

按照数据文件recover操作

SQL> recover datafile 1;
Media recovery complete.
SQL> recover datafile 3,4,5,6,7;
Media recovery complete.
SQL> recover datafile 8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28;
Media recovery complete.
SQL> recover datafile 2;
ORA-00283: recovery session canceled due to errors
ORA-10562: Error occurred while applying redo to data block (file# 2, block#
165054)
ORA-10564: tablespace SYSAUX
ORA-01110: data file 2: '+DATADG/xifenfei/datafile/sysaux.265.861925867'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 271
ORA-00607: Internal error occurred while making a change to a data block
ORA-00602: internal programming exception
ORA-07445: exception encountered: core dump [kdxlin()+4088] [SIGSEGV]
[ADDR:0xC] [PC:0x95FB502] [Address not mapped to object] []

错误提示和recover database一样,那我们只能让恢复跳过该block继续恢复,因为根据经验判断data object# 271不是系统核心对象，不会影响数据库的启动

跳过坏块继续恢复

SQL> recover  datafile 2 allow 1 corruption;
ORA-00283: recovery session canceled due to errors
ORA-00600: internal error code, arguments: [3020], [2], [69793], [8458401], [],
[], [], [], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 2, block# 69793, file
offset is 571744256 bytes)
ORA-10564: tablespace SYSAUX
ORA-01110: data file 2: '+DATADG/xifenfei/datafile/sysaux.265.861925867'
ORA-10561: block type 'TRANSACTION MANAGED INDEX BLOCK', data object# 272
SQL> recover  datafile 2 allow 1 corruption;
Media recovery complete.
SQL> alter database open;
Database altered.

出现了ORA-600[3020] 继续跳过坏块,然后数据库顺利open,别忘记加tempfile

处理异常对象

SQL> select object_name,object_type from dba_objects where data_object_id in(272,271);
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
-------------------
SMON_SCN_TIME_TIM_IDX
INDEX
SMON_SCN_TIME_SCN_IDX
INDEX
SQL> select index_name from dba_indexes where table_name='SMON_SCN_TIME';
INDEX_NAME
------------------------------
SMON_SCN_TIME_TIM_IDX
SMON_SCN_TIME_SCN_IDX
SQL> set pages 1000
SQL> set long 1000
SQL> Select dbms_metadata.get_ddl('TABLE','SMON_SCN_TIME','SYS') FROM DUAL ;
DBMS_METADATA.GET_DDL('TABLE','SMON_SCN_TIME','SYS')
--------------------------------------------------------------------------------
  CREATE TABLE "SYS"."SMON_SCN_TIME"
   (    "THREAD" NUMBER,
        "TIME_MP" NUMBER,
        "TIME_DP" DATE,
        "SCN_WRP" NUMBER,
        "SCN_BAS" NUMBER,
        "NUM_MAPPINGS" NUMBER,
        "TIM_SCN_MAP" RAW(1200),
        "SCN" NUMBER DEFAULT 0,
        "ORIG_THREAD" NUMBER DEFAULT 0           /* for downgrade */
   ) CLUSTER "SYS"."SMON_SCN_TO_TIME_AUX" ("THREAD")
SQL> analyze table smon_scn_time validate structure cascade online;
analyze table smon_scn_time validate structure cascade online
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 2, block # 165054)
ORA-01110: data file 2: '+DATADG/xifenfei/datafile/sysaux.265.861925867'
SQL> truncate CLUSTER "SYS"."SMON_SCN_TO_TIME_AUX";
Cluster truncated.

关于SMON_SCN_TIME部分处理,可以参考:关于SMON_SCN_TIME若干问题说明.至此数据库基本上恢复完成，而且运气非常好，恢复的非常完美，数据实现0丢失.

最新版Oracle dul支持Oracle 7.2.3

网上有朋友发过来了oracle 7.2.3的数据文件说dul新版本无法支持,我进行测试,可以正常使用dul挖oracle 7.2.3版本oracle.再次实测刷新dul对于oracle老版本支持记录(以前实测dul支持oracle 8.0:DUL10直接支持ORACLE 8.0,DUL挖ORACLE 8.0数据库).根据dul官方说法从oracle 6 及其以后版本都支持
最新版dul(10.2.0.6.11)支持oracle 7.2.3

H:\temp\recover\dul10>dul.exe
Data UnLoader: 10.2.0.6.11 - Internal Only - on Sun Nov 08 22:45:26 2015
with 64-bit io functions
Copyright (c) 1994 2015 Bernard van Duijnen All rights reserved.
 Strictly Oracle Internal Use Only
DUL: Warning: Recreating file "dul.log"
Found db_id = 1190425910
Found db_name = ORA7
DUL> show datafiles;
ts# rf# start   blocks offs open  err file name
UNK   1     0   524289    0    0    0 H:\temp\recover\headof\headof\home\systora7.dbf
UNK   2     0   524289    0    1    0 H:\temp\recover\headof\headof\home1\rbsora7.dbf
UNK   3     0   524289    0    0    0 H:\temp\recover\headof\headof\home\tempora7.dbf
UNK   4     0    15361    0    0    0 H:\temp\recover\headof\headof\home\toolora7.dbf
UNK   5     0      513    0    1    0 H:\temp\recover\headof\headof\home\usrora7.dbf
UNK   6     0   524289    0    0    0 H:\temp\recover\headof\headof\home\BSYS_DISK1.dbf
UNK   7     0   524289    0    1    0 H:\temp\recover\headof\headof\home1\BSYS_DISK2.dbf
UNK   8     0   512001    0    1    0 H:\temp\recover\headof\headof\home1\BALANCE_DATA01.dbf
UNK   9     0   262145    0    0    0 H:\temp\recover\headof\headof\home\BALANCE_IDX01.dbf
UNK  10     0   524289    0    0    0 H:\temp\recover\headof\headof\home\CARD.dbf
UNK  11     0   524289    0    0    0 H:\temp\recover\headof\headof\home\LARGE_TRANS.dbf
UNK  12     0   524289    0    0    0 H:\temp\recover\headof\headof\home\tool01.dbf
UNK  13     0   524289    0    0    0 H:\temp\recover\headof\headof\home\system1.dbf
UNK  14     0   524289    0    0    0 H:\temp\recover\headof\headof\home\balance_data02.dbf
UNK  15     0   524289    0    0    0 H:\temp\recover\headof\headof\home\BSYS_DISK3.dbf
UNK  16     0   524289    0    0    0 H:\temp\recover\headof\headof\home\BSYS_DISK4.dbf
UNK  17     0   524289    0    0    0 H:\temp\recover\headof\headof\home\BSYS_DISK5.dbf
UNK  18     0   524289    0    0    0 H:\temp\recover\headof\headof\home\BSYS_DISK6.dbf
UNK  19     0   524289    0    0    0 H:\temp\recover\headof\headof\home\system2.dbf
UNK  20     0   524289    0    0    0 H:\temp\recover\headof\headof\home\card1.dbf
DUL> bootstrap;
Probing file = 1, block = 377
  database version 7 bootstrap$ at file 1, block 302
. unloading table                BOOTSTRAP$
DUL: Warning: block number is non zero but marked deferred trying to process it anyhow
      45 rows unloaded
DUL: Warning: Dictionary cache DC_BOOTSTRAP is empty
Reading BOOTSTRAP.dat 45 entries loaded
Parsing Bootstrap$ contents
DUL: Warning: Recreating file "dict.ddl"
Generating dict.ddl for version 7
 OBJ$: segobjno 17, file 1 block 162
 TAB$: segobjno 1, tabno 1, file 1  block 52
 COL$: segobjno 1, tabno 5, file 1  block 52
 USER$: segobjno 9, tabno 1, file 1  block 142
Running generated file "@dict.ddl" to unload the dictionary tables
. unloading table                      OBJ$    6076 rows unloaded
. unloading table                      TAB$    1067 rows unloaded
. unloading table                      COL$   18105 rows unloaded
. unloading table                     USER$      56 rows unloaded
Reading USER.dat 56 entries loaded
Reading OBJ.dat 6076 entries loaded and sorted 6076 entries
Reading SCANNEDLOBPAGE.dat 0 entries loaded and sorted 0 entries
Reading TAB.dat 1067 entries loaded
Reading COL.dat 18105 entries loaded and sorted 18105 entries
Reading SEG.dat 0 entries loaded
Reading EXT.dat 0 entries loaded and sorted 0 entries
Reading COMPATSEG.dat 0 entries loaded
Reading BOOTSTRAP.dat 45 entries loaded
DUL: Warning: Recreating file "dict.ddl"
Generating dict.ddl for version 7
 OBJ$: segobjno 17, file 1 block 162
 TAB$: segobjno 1, tabno 1, file 1  block 52
 COL$: segobjno 1, tabno 5, file 1  block 52
 USER$: segobjno 9, tabno 1, file 1  block 142
Running generated file "@dict.ddl" to unload the dictionary tables
. unloading table                      OBJ$
DUL: Warning: Recreating file "OBJ.ctl"
    6076 rows unloaded
. unloading table                      TAB$
DUL: Warning: Recreating file "TAB.ctl"
    1067 rows unloaded
. unloading table                      COL$
DUL: Warning: Recreating file "COL.ctl"
   18105 rows unloaded
. unloading table                     USER$
DUL: Warning: Recreating file "USER.ctl"
      56 rows unloaded
Reading USER.dat 56 entries loaded
Reading OBJ.dat 6076 entries loaded and sorted 6076 entries
Reading SCANNEDLOBPAGE.dat 0 entries loaded and sorted 0 entries
Reading TAB.dat 1067 entries loaded
Reading COL.dat 18105 entries loaded and sorted 18105 entries
Reading SEG.dat 0 entries loaded
Reading EXT.dat 0 entries loaded and sorted 0 entries
Reading COMPATSEG.dat 0 entries loaded
Reading BOOTSTRAP.dat 45 entries loaded
DUL> unload table user$;
. unloading table                     USER$
DUL: Warning: Recreating file "USER.ctl"
      56 rows unloaded
DUL>

数据库版本截图
oracle 7.2.3

oracle-7.2.3

dul执行结果截图
dul-oracle-7.2.3

通过内部命令直接读取数据文件版本信息
dump-oracle-7

分享某客户存储异常恢复之后oracle故障恢复—ORA-600 4155

某客户使用win 2003，Oracle 11.2.0.1+ASM架构方式,由于存储异常并且做了存储恢复之后,ASM可以正常mount起来,但是数据库无法打开
使用dbv检查system发现有少量坏块

DBVERIFY - 开始验证: FILE = +DATA/xifenfei/datafile/system.256.764288125
页 3117 标记为损坏
Corrupt block relative dba: 0x00400c2d (file 1, block 3117)
Bad header found during dbv:
Data in bad block:
 type: 11 format: 2 rdba: 0x00400001
 last change scn: 0x0000.00000000 seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x00000b01
 check value in block header: 0xfeec
 computed block checksum: 0x0
Corrupt block relative dba: 0x0042002d (file 1, block 131117)
Bad header found during dbv:
Data in bad block:
 type: 11 format: 2 rdba: 0x00400001
 last change scn: 0x0000.00000000 seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x00000b01
 check value in block header: 0xfeec
 computed block checksum: 0x0
Corrupt block relative dba: 0x0042003d (file 1, block 131133)
Bad header found during dbv:
Data in bad block:
 type: 11 format: 2 rdba: 0x00400001
 last change scn: 0x0000.00000000 seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x00000b01
 check value in block header: 0xfeec
 computed block checksum: 0x0
DBVERIFY - 验证完成
检查的页总数: 222208
处理的页总数 (数据): 188939
失败的页总数 (数据): 19
处理的页总数 (索引): 17375
失败的页总数 (索引): 0
处理的页总数 (其他): 3190
处理的总页数 (段)  : 1
失败的总页数 (段)  : 0
空的页总数: 12701
标记为损坏的总页数: 3
流入的页总数: 0
加密的总页数        : 0
最高块 SCN            : 0 (0.0)

很多”页 131125 失败, 校验代码为 6125″类似错误忽略.
我们对于这些坏块进行分析，这些坏块未涉及oracle 最核心的基表数据,从理论上可以open数据库

尝试打开数据库

ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
 parallel recovery started with 32 processes
Started redo scan
Mon Nov 16 14:12:45 2015
NOTE: dependency between database xifenfei and diskgroup resource ora.DATA.dg is established
Errors in file d:\oracle\diag\rdbms\xifenfei\xifenfei\trace\xifenfei_ora_5672.trc  (incident=937262):
ORA-00353: 日志损坏接近块 12509 更改 14199034494312 时间 02/05/2015 03:09:12
ORA-00312: 联机日志 2 线程 1: '+DATA/xifenfei/onlinelog/group_2.265.764288315'
ORA-00312: 联机日志 2 线程 1: '+DATA/xifenfei/onlinelog/group_2.264.764288315'
Incident details in: d:\oracle\diag\rdbms\xifenfei\xifenfei\incident\incdir_937262\xifenfei_ora_5672_i937262.trc
Media Recovery failed with error 399
ORA-355 signalled during: ALTER DATABASE RECOVER  DATABASE  ...

可以确定存储恢复的redo有问题(ORA-00353,ORA-00312),数据库无法直接打开

使用参数_allow_resetlogs_corruption屏蔽redo异常resetlogs库

Mon Nov 16 15:26:41 2015
alter database open resetlogs
Mon Nov 16 15:26:41 2015
Starting background process ASMB
Mon Nov 16 15:26:41 2015
ASMB started with pid=25, OS id=6612
Starting background process RBAL
Mon Nov 16 15:26:41 2015
RBAL started with pid=26, OS id=6940
NOTE: initiating MARK startup
Starting background process MARK
Mon Nov 16 15:26:41 2015
MARK started with pid=27, OS id=1720
NOTE: MARK has subscribed
NOTE: Loaded library: System
SUCCESS: diskgroup DATA was mounted
RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
Mon Nov 16 15:26:44 2015
NOTE: dependency between database xifenfei and diskgroup resource ora.DATA.dg is established
Archived Log entry 1 added for thread 1 sequence 1390429 ID 0xf6320db5 dest 1:
Archived Log entry 2 added for thread 1 sequence 1390427 ID 0xf6320db5 dest 1:
ARCH: Log corruption near block 16350 change 14199035261082 time ?
CORRUPTION DETECTED: thread 1 sequence 1390428 log 3 at block 16350. Arch found corrupt blocks
Errors in file d:\oracle\diag\rdbms\xifenfei\xifenfei\trace\xifenfei_ora_8132.trc  (incident=951271):
ORA-00353: 日志损坏接近块 16350 更改 14199035261082 时间 02/05/2015 03:12:49
ORA-00312: 联机日志 3 线程 1: '+DATA/xifenfei/onlinelog/group_3.267.764288315'
ORA-00312: 联机日志 3 线程 1: '+DATA/xifenfei/onlinelog/group_3.266.764288315'
Incident details in: d:\oracle\diag\rdbms\xifenfei\xifenfei\incident\incdir_951271\xifenfei_ora_8132_i951271.trc
Errors in file d:\oracle\diag\rdbms\xifenfei\xifenfei\trace\xifenfei_ora_8132.trc:
ORA-00354: 损坏重做日志块标头
ORA-00353: 日志损坏接近块 16350 更改 14199035261082 时间 02/05/2015 03:12:49
ORA-00312: 联机日志 3 线程 1: '+DATA/xifenfei/onlinelog/group_3.267.764288315'
ORA-00312: 联机日志 3 线程 1: '+DATA/xifenfei/onlinelog/group_3.266.764288315'
ARCH: All Archive destinations made inactive due to error 354
Committing creation of archivelog '+DATA/xifenfei/archivelog/2015_11_16/thread_1_seq_1390428.276.895937207' (error 354)
Deleted Oracle managed file +DATA/xifenfei/archivelog/2015_11_16/thread_1_seq_1390428.276.895937207
******************************************************
Detected premature EOF of log 3 at block 16350; re-trying archival
******************************************************
Mon Nov 16 15:26:49 2015
Sweep [inc][951271]: completed
Mon Nov 16 15:26:49 2015
Trace dumping is performing id=[cdmp_20151116152649]
Archived Log entry 3 added for thread 1 sequence 1390428 ID 0xf6320db5 dest 1:
RESETLOGS after incomplete recovery UNTIL CHANGE 14199033899179
Resetting resetlogs activation ID 4130475445 (0xf6320db5)
Errors in file d:\oracle\diag\rdbms\xifenfei\xifenfei\trace\xifenfei_m001_800.trc  (incident=951319):
ORA-00353: log corruption near block 2270 change 14199035131016 time 02/05/2015 03:12:58
ORA-00334: archived log: '+DATA/xifenfei/archivelog/2015_11_16/thread_1_seq_1390428.276.895937209'
Incident details in: d:\oracle\diag\rdbms\xifenfei\xifenfei\incident\incdir_951319\xifenfei_m001_800_i951319.trc
Errors in file d:\oracle\diag\rdbms\xifenfei\xifenfei\trace\xifenfei_m001_800.trc  (incident=951320):
ORA-00355: change numbers out of order
ORA-00353: log corruption near block 2270 change 14199035131016 time 02/05/2015 03:12:58
ORA-00334: archived log: '+DATA/xifenfei/archivelog/2015_11_16/thread_1_seq_1390428.276.895937209'
Incident details in: d:\oracle\diag\rdbms\xifenfei\xifenfei\incident\incdir_951320\xifenfei_m001_800_i951320.trc
Trace dumping is performing id=[cdmp_20151116152651]
Mon Nov 16 15:26:51 2015
Sweep [inc][951320]: completed
Sweep [inc][951319]: completed
Sweep [inc2][951320]: completed
Sweep [inc2][951319]: completed
Sweep [inc2][951271]: completed
Trace dumping is performing id=[cdmp_20151116152653]
Checker run found 1 new persistent data failures
Mon Nov 16 15:26:53 2015
Setting recovery target incarnation to 2
Mon Nov 16 15:26:54 2015
Assigning activation ID 4262085362 (0xfe0a42f2)
LGWR: STARTING ARCH PROCESSES
Mon Nov 16 15:26:54 2015
ARC0 started with pid=29, OS id=2896
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Mon Nov 16 15:26:55 2015
ARC1 started with pid=30, OS id=1748
Mon Nov 16 15:26:55 2015
ARC2 started with pid=31, OS id=1920
ARC1: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: +DATA/xifenfei/onlinelog/group_1.262.764288315
  Current log# 1 seq# 1 mem# 1: +DATA/xifenfei/onlinelog/group_1.263.764288315
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Mon Nov 16 15:26:55 2015
SMON: enabling cache recovery
Mon Nov 16 15:26:55 2015
ARC3 started with pid=32, OS id=7236
ARC2: Archival started
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
ARC0: Becoming the heartbeat ARCH
Errors in file d:\oracle\diag\rdbms\xifenfei\xifenfei\trace\xifenfei_ora_8132.trc  (incident=951272):
ORA-00600: 内部错误代码, 参数: [4155], [], [], [], [], [], [], [], [], [], [], []
Incident details in: d:\oracle\diag\rdbms\xifenfei\xifenfei\incident\incdir_951272\xifenfei_ora_8132_i951272.trc
Errors in file d:\oracle\diag\rdbms\xifenfei\xifenfei\trace\xifenfei_ora_8132.trc:
ORA-00600: 内部错误代码, 参数: [4155], [], [], [], [], [], [], [], [], [], [], []
Errors in file d:\oracle\diag\rdbms\xifenfei\xifenfei\trace\xifenfei_ora_8132.trc:
ORA-00600: 内部错误代码, 参数: [4155], [], [], [], [], [], [], [], [], [], [], []
Error 600 happened during db open, shutting down database
Trace dumping is performing id=[cdmp_20151116152658]
USER (ospid: 8132): terminating the instance due to error 600
Mon Nov 16 15:27:07 2015
Instance terminated by USER, pid = 8132
ORA-1092 signalled during: alter database open resetlogs...

在resetlogs过程中由于遭遇了ORA-600[4155]导致数据库无法正常打开.

分析相关trace文件

*** 2015-11-16 15:26:56.921
*** SESSION ID:(145.3) 2015-11-16 15:26:56.921
*** CLIENT ID:() 2015-11-16 15:26:56.921
*** SERVICE NAME:(SYS$USERS) 2015-11-16 15:26:56.921
*** MODULE NAME:(sqlplus.exe) 2015-11-16 15:26:56.921
*** ACTION NAME:() 2015-11-16 15:26:56.921
Dump continued from file: d:\oracle\diag\rdbms\xifenfei\xifenfei\trace\xifenfei_ora_8132.trc
ORA-00600: 内部错误代码, 参数: [4155], [], [], [], [], [], [], [], [], [], [], []
========= Dump for incident 951272 (ORA 600 [4155]) ========
----- Beginning of Customized Incident Dump(s) -----
XID passed in =xid: 0x000b.001.00fcfb45
XID from Undo block = xid: 0x000b.009.00fcc561
----- End of Customized Incident Dump(s) -----
*** 2015-11-16 15:26:57.203
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=7j16t46cacjt9) -----
alter database open resetlogs
----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst1()+129        CALL???  skdstdst()           009233DA2 000000000 000000000
                                                   000000000
ksedst()+69          CALL???  ksedst1()            000000002 000000000 006F605E0
                                                   000000000
dbkedDefDump()+4536  CALL???  ksedst()             000000287 000000000 000000000
                                                   000000000
ksedmp()+43          CALL???  dbkedDefDump()       000000003 000000002 000000000
                                                   000468E71
ksfdmp()+87          CALL???  ksedmp()             000000000 000000000 000000000
                                                   000000000
dbgexPhaseII()+1819  CALL???  ksfdmp()             000000000 000000000 000000000
                                                   000000000
dbgexExplicitEndInc  CALL???  dbgexPhaseII()       000000000 000000000 000000000
()+755                                             000000000
dbgeEndDDEInvocatio  CALL???  dbgexExplicitEndInc  00CFB0570 00CFB7540 01F9012D8
nImpl()+748                   ()                   01F901300
dbgeEndDDEInvocatio  CALL???  dbgeEndDDEInvocatio  00CFB0570 00CFB7540 01F903130
n()+47                        nImpl()              00000000A
ktugce()+610         CALL???  dbgeEndDDEInvocatio  006E24498 01F8FF91E 00000002E
                              n()                  035636366
ktdgti()+609         CALL???  ktugce()             000000000 000001F68 000001F68
                                                   000000001
k2vGetCollectingInf  CALL???  ktdgti()             000000000 008A34E71 01F902470
o()+324                                            000000018
k2vcbk()+182         CALL???  k2vGetCollectingInf  000000000 000000000 000000000
                              o()                  000000008
kturRecoverTxn()+82  CALL???  k2vcbk()             000000000 FCFB450000000B
67                                                 000610001 7FFDF055860
kturRecoverUndoSegm  CALL???  kturRecoverTxn()     01F903448 009460001 000000000
ent()+1371                                         00147AE14
ktuiup()+1520        CALL???  kturRecoverUndoSegm  7FF0000000B 01F903628
                              ent()                000000000 00147AE14
ktuini()+80          CALL???  ktuiup()             000000001 00000000E 01F9038A0
                                                   003CB3D06
adbdrv()+44263       CALL???  ktuini()             000000000 01F9090C8 000000000
                                                   000000000
opiexe()+20842       CALL???  adbdrv()             000000023 000000003
                                                   7FF00000102 000000000
opiosq0()+5129       CALL???  opiexe()+16981       000000004 000000000 01F90A8E0
                                                   009361AB3
kpooprx()+357        CALL???  opiosq0()            000000003 00000000E 01F90ABB0
                                                   0000000A4
kpoal8()+940         CALL???  kpooprx()            000020C80 01E65CCD0 00CE91AD8
                                                   000000001
opiodr()+1662        CALL???  kpoal8()             00000005E 00000001C 01F90E120
                                                   00A4EF224
ttcpip()+1325        CALL???  opiodr()             480000000000005E
                                                   49004D000000001C 01F90E120
                                                   4100200000000000
opitsk()+2040        CALL???  ttcpip()             01E735200 000000000 000000000
                                                   000000000
opiino()+1258        CALL???  opitsk()             00000001E 000000000 000000000
                                                   01F90FA18
opiodr()+1662        CALL???  opiino()             00000003C 000000004 01F90FAD0
                                                   000000000
opidrv()+864         CALL???  opiodr()             00000003C 000000004 01F90FAD0
                                                   6F5C3A6400000000
sou2o()+98           CALL???  opidrv()+150         00000003C 000000004 01F90FAD0
                                                   000000000
opimai_real()+158    CALL???  sou2o()              01F90FB00 01F90FBC4
                                                   F0010000B07DF 1009C002B0019
opimai()+191         CALL???  opimai_real()        00000001B 01F90FC88 000000036
                                                   000000000
OracleThreadStart()  CALL???  opimai()             01F90FE90 01F60FF38 000000002
+724                                               01F90FC88
0000000078D3B6DA     CALL???  OracleThreadStart()  01F60FF38 000000000 000000000
                                                   01F90FFA8
--------------------- Binary Stack Dump ---------------------
UNDO BLK:
xid: 0x000b.009.00fcc561  seq: 0x513d cnt: 0x2f  irb: 0x2f  icl: 0x0   flg: 0x0000
 Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset
---------------------------------------------------------------------------
0x01 0x1f64     0x02 0x1ee4     0x03 0x1e30     0x04 0x1d7c     0x05 0x1cc8
0x06 0x1c14     0x07 0x1b60     0x08 0x1aac     0x09 0x19f8     0x0a 0x1944
0x0b 0x1890     0x0c 0x17dc     0x0d 0x1728     0x0e 0x1674     0x0f 0x15c0
0x10 0x150c     0x11 0x1458     0x12 0x13a4     0x13 0x12f0     0x14 0x123c
0x15 0x1188     0x16 0x10d4     0x17 0x1050     0x18 0x0fd0     0x19 0x0f1c
0x1a 0x0e98     0x1b 0x0de4     0x1c 0x0d30     0x1d 0x0c7c     0x1e 0x0bc8
0x1f 0x0b44     0x20 0x0ac4     0x21 0x0a10     0x22 0x095c     0x23 0x08a8
0x24 0x07f4     0x25 0x0770     0x26 0x06f0     0x27 0x063c     0x28 0x0588
0x29 0x04d4     0x2a 0x0420     0x2b 0x039c     0x2c 0x031c     0x2d 0x0298
0x2e 0x0218     0x2f 0x0164

ORA-600 4155是由于在恢复过程中发现事务的id和undo segment中的事务表中id不匹配从而出现此类问题.针对此问题,可以通过bbed修改事务表记录,或者直接丢弃该事务,从而绕过该错误.

处理掉异常事务id后,继续open库

Mon Nov 16 16:16:07 2015
ALTER DATABASE OPEN
Beginning crash recovery of 1 threads
 parallel recovery started with 32 processes
Started redo scan
Completed redo scan
 read 8 KB redo, 0 data blocks need recovery
Started redo application at
 Thread 1: logseq 1, block 2, scn 14199161880578
Recovery of Online Redo Log: Thread 1 Group 1 Seq 1 Reading mem 0
  Mem# 0: +DATA/xifenfei/onlinelog/group_1.262.764288315
  Mem# 1: +DATA/xifenfei/onlinelog/group_1.263.764288315
Completed redo application of 0.00MB
Completed crash recovery at
 Thread 1: logseq 1, block 19, scn 14199161900601
 0 data blocks read, 0 data blocks written, 8 redo k-bytes read
Current SCN is not changed: _minimum_giga_scn (scn 14199161880576) is too small
Mon Nov 16 16:16:08 2015
LGWR: STARTING ARCH PROCESSES
Mon Nov 16 16:16:08 2015
ARC0 started with pid=62, OS id=7612
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Mon Nov 16 16:16:09 2015
ARC1 started with pid=63, OS id=5620
Mon Nov 16 16:16:09 2015
ARC2 started with pid=64, OS id=7308
ARC1: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
Thread 1 advanced to log sequence 2 (thread open)
Thread 1 opened at log sequence 2
  Current log# 2 seq# 2 mem# 0: +DATA/xifenfei/onlinelog/group_2.264.764288315
  Current log# 2 seq# 2 mem# 1: +DATA/xifenfei/onlinelog/group_2.265.764288315
Successful open of redo thread 1
Archived Log entry 1 added for thread 1 sequence 1 ID 0xfe09f6df dest 1:
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Mon Nov 16 16:16:10 2015
SMON: enabling cache recovery
Dictionary check beginning
Tablespace 'TEMP' #3 found in data dictionary,
but not in the controlfile. Adding to controlfile.
Dictionary check complete
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
*********************************************************************
WARNING: The following temporary tablespaces contain no files.
         This condition can occur when a backup controlfile has
Mon Nov 16 16:16:09 2015
ARC3 started with pid=65, OS id=7064
         been restored.  It may be necessary to add files to these
ARC2: Archival started
         tablespaces.  That can be done using the SQL statement:
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
         ALTER TABLESPACE <tablespace_name> ADD TEMPFILE
ARC0: Becoming the heartbeat ARCH
         Alternatively, if these temporary tablespaces are no longer
         needed, then they can be dropped.
           Empty temporary tablespace: TEMP
*********************************************************************
Database Characterset is ZHS16GBK
No Resource Manager plan active
**********************************************************
WARNING: Files may exists in db_recovery_file_dest
that are not known to the database. Use the RMAN command
CATALOG RECOVERY AREA to re-catalog any such files.
If files cannot be cataloged, then manually delete them
using OS command.
One of the following events caused this:
1. A backup controlfile was restored.
2. A standby controlfile was restored.
3. The controlfile was re-created.
4. db_recovery_file_dest had previously been enabled and
   then disabled.
**********************************************************
replication_dependency_tracking turned off (no async multimaster replication found)
WARNING: AQ_TM_PROCESSES is set to 0. System operation                     might be adversely affected.
LOGSTDBY: Validating controlfile with logical metadata
LOGSTDBY: Validation complete
Completed: ALTER DATABASE OPEN

因为该数据库是经过了存储恢复,除system之外,其他文件也有大量坏块,因为恢复过程相对比较麻烦,除了上面列出来的ORA-00353,ORA-00312,ORA-600 4155等各种错误之外,还有大量的ORA-01578,ORA-01110.由于11G比较常见的ORA-00283,ORA-16433问题。

windows平台listener.log超过4G导致监听异常

今天有个朋友咨询生产库非常慢,应用无法连接数据库,通过分析是由于win 32位 数据库11.2.0.1版本 文件系统为ntfs,但是listener.log为4G,导致监听工作不正常.
tnsping几乎hang住
tnsping

lsnrctl status 也几乎hang住
lsnrctl-status

直接ping ip正常
证明不是网络问题,导致监听异常,现在判断是监听问题
ping-ip

监听日志超过4G
listener_file

关闭监听日志
log_status_off

监听正常
关闭监听日志之后,监听恢复正常
2

文件系统为ntfs格式
这里证明，不是由于文件系统格式的限制ntfs最大文件允许64T,这里4G肯定不是系统的极限
ntfs

相关文章
Bug 9879101 : THE CONNECT THROUGH LISTENER WAS SLOW WHEN LISTNER LOG GROWED 4GB
WINDOWS: Listener Hangs & Lsnrctl Commands Are Slow or Hang