月归档：十一月 2017

asm磁盘组操作不当导致数据文件丢失恢复

发表于 2017 年 11 月 28 日由惜分飞

最近遇到数据库恢复case,客户是要更换存储,在数据库mount状态把使用omf方式存储数据的asm 磁盘组通过rman copy到新的通过别名方式存储的新的asm 磁盘组的存储中,但是由于操作人员粗心,copy语句中部分目标磁盘组的数据文件别名重复了,最后执行rename file之后,导致部分数据文件彻底丢失.我们通过底层碎片扫描(参考:asm disk header 彻底损坏恢复)对于该用户的数据实现完全恢复.
因为整个过程重现比较麻烦,这里测试从一个data磁盘组中有一个omf方式存储的含有两个数据文件的表空间,通过rman copy 把这个表空间的两个文件拷贝到datanew磁盘组中,但是由于粗心把两个数据文件的别名写成一样,结果导致该表空间的一个数据文件彻底丢失的测试.

创建测试表空间
在datanew磁盘组中创建omf方式管理的xifenfei表空间,含有两个数据文件,file#分别为14和15

SQL> create tablespace xifenfei datafile '+DATA' SIZE 128m;

Tablespace created.

SQL> ALTER TABLESPACE XIFENFEI ADD DATAFILE '+DATA' SIZE 128m AUTOEXTEND ON;

Tablespace altered.

SQL> SELECT FILE_NAME,FILE_ID FROM  DBA_DATA_FILES WHERE TABLESPACE_NAME='XIFENFEI';

FILE_NAME
--------------------------------------------------------------------------------
   FILE_ID
----------
+DATA/XFF/DATAFILE/xifenfei.276.961143809
        14

+DATA/XFF/DATAFILE/xifenfei.277.961143825
        15

rman copy datafile 14
通过rman copy把datafile 14拷贝到data磁盘组中,目标端为别名方式存储

RMAN> copy datafile 14 to '+datanew/xifenfei.dbf';

Starting backup at 27-NOV-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00014 name=+DATA/XFF/DATAFILE/xifenfei.276.961143809
output file name=+DATANEW/xifenfei.dbf tag=TAG20171127T082643 RECID=4 STAMP=961144006
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 27-NOV-17


[grid@localhost ~]$ asmcmd
ASMCMD> cd datanew
ASMCMD> ls
XFF/
xifenfei.dbf
ASMCMD> ls -l
Type      Redund  Striped  Time             Sys  Name
                                            Y    XFF/
DATAFILE  UNPROT  COARSE   NOV 27 08:00:00  N    xifenfei.dbf => +DATANEW/XFF/DATAFILE/XIFENFEI.256.961144003
ASMCMD>

这里通过asmcmd的ls命令,可以看到虽然我们存储的为datanew磁盘组的别名文件,实际上是link到asm的omf方式的文件(本质上asm中的文件都是omf方式存储,只是在使用的时候体现asm的客户端程序方式不一样,是直接asm中的omf方式,还是asm中的别名).

rman copy datafile 15
通过rman copy把datafile 15 拷贝到和datafile 14别名一样的文件了

RMAN> copy datafile 15 to '+datanew/xifenfei.dbf';

Starting backup at 27-NOV-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00015 name=+DATA/XFF/DATAFILE/xifenfei.277.961143825
output file name=+DATANEW/xifenfei.dbf tag=TAG20171127T082731 RECID=5 STAMP=961144053
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 27-NOV-17


ASMCMD> ls -l
Type      Redund  Striped  Time             Sys  Name
                                            Y    XFF/
DATAFILE  UNPROT  COARSE   NOV 27 08:00:00  N    xifenfei.dbf => +DATANEW/XFF/DATAFILE/XIFENFEI.256.961144003
ASMCMD> cd xff
ASMCMD> ls
DATAFILE/
ASMCMD> cd datafile
ASMCMD> ls
XIFENFEI.256.961144003
ASMCMD>

这里可以看出来,在data磁盘组中,file 14被file 15覆盖掉了

rename file
把data磁盘组中的数据文件rename 到datanew磁盘组中

SQL> alter database rename file '+DATA/XFF/DATAFILE/xifenfei.276.961143809' to '+datanew/xifenfei.dbf';

Database altered.

SQL> alter database rename file '+DATA/XFF/DATAFILE/xifenfei.277.961143825' to '+datanew/xifenfei.dbf';
alter database rename file '+DATA/XFF/DATAFILE/xifenfei.277.961143825' to '+datanew/xifenfei.dbf'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01523: cannot rename data file to '+data/xifenfei.dbf' - file already part of database

这里我们可以看到,file 14 rename 成功,但是file 15 rename失败,因为在数据库中，已经有了别名的文件(数据文件的路径)

omf自动删除文件
查看原磁盘组datanew中,发现datafile 14被自动删除

ASMCMD> pwd
+DATA/XFF/DATAFILE
ASMCMD> ls -l
Type      Redund  Striped  Time             Sys  Name
DATAFILE  UNPROT  COARSE   NOV 27 08:00:00  Y    SYSAUX.257.942061433
DATAFILE  UNPROT  COARSE   NOV 27 08:00:00  Y    SYSTEM.256.942061393
DATAFILE  UNPROT  COARSE   NOV 27 08:00:00  Y    UNDOTBS1.258.942061449
DATAFILE  UNPROT  COARSE   NOV 27 08:00:00  Y    USERS.259.942061449
DATAFILE  UNPROT  COARSE   NOV 27 08:00:00  Y    XIFENFEI.277.961143825
ASMCMD>

alert日志证实数据文件被删除

2017-11-27T09:05:03.054741-05:00
alter database rename file '+DATA/XFF/DATAFILE/xifenfei.276.961143809' to '+datanew/xifenfei.dbf'
2017-11-27T09:05:03.114947-05:00
NOTE: Under CF enqueue, no dependency request for disk group DATANEW
Deleted Oracle managed file +DATA/XFF/DATAFILE/xifenfei.276.961143809
Completed: alter database rename file '+DATA/XFF/DATAFILE/xifenfei.276.961143809' to '+datanew/xifenfei.dbf'
2017-11-27T09:05:21.471474-05:00
alter database rename file '+DATA/XFF/DATAFILE/xifenfei.277.961143825' to '+data/xifenfei.dbf'
ORA-1511 signalled during:alter database rename file
      '+DATA/XFF/DATAFILE/xifenfei.277.961143825' to'+datanew/xifenfei.dbf'

这里可以证实，数据文件的omf方式管理,在数据文件执行rename file的时候,会自动删除掉老的数据文件.这里悲剧已经发生,由于rman copy 覆盖了datanew磁盘组中的datafile 14,rename file又导致data磁盘组中的datafile 14被自动删除,从而使得datafile 14这个数据文件在两个磁盘组中都丢失.从常规角度来说,如果没有合适的备份该文件无法恢复.如果遭遇到oracle asm中数据文件丢失或者部分覆盖,请保护现场,联系我们(ORACLE数据库恢复技术支持),将为您提供专业数据库技术支持：Phone:17813235971 Q Q:107644445 E-Mail:dba@xifenfei.com最大限度抢救您的数据

发表在 Oracle ASM, 非常规恢复 | 标签为 asm file lost, asm 恢复, asm文件丢失, asm碎片重组 | 评论关闭

dbconsole和sysman用户

发表于 2017 年 11 月 27 日由惜分飞

dbconsole一直是我不怎么推荐的oracle工具,但是总有一些客户使用,这里对于dbconsole中sysman用户状态,密码修改做了一些基本的测试
sysman用户被锁

SQL>  alter user sysman account  lock;

User altered.

重启dbconsole

[oracle@localhost ~]$ emctl stop dbconsole
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
        LANGUAGE = (unset),
        LC_ALL = (unset),
        LANG = "c"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0 
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.

https://localhost:1158/em/console/aboutApplication

Stopping Oracle Enterprise Manager 11g Database Control ... 
 ...  Stopped. 
[oracle@localhost ~]$ emctl start dbconsole
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
        LANGUAGE = (unset),
        LC_ALL = (unset),
        LANG = "c"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0 
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.

https://localhost:1158/em/console/aboutApplication

Starting Oracle Enterprise Manager 11g Database Control ..... started. 
------------------------------------------------------------------
Logs are generated in directory /u01/app/oracle/product/11.2.0/db_1/localhost_test/sysman/log

部分错误日志提示（这里可以明显dbconsole无法正常使用是由于sysman被锁）

解锁sysman用户

SQL> alter user sysman account unlock;

User altered.

重启dbconsole

[oracle@localhost ~]$ emctl stop dbconsole
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
        LANGUAGE = (unset),
        LC_ALL = (unset),
        LANG = "c"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0 
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.

https://localhost:1158/em/console/aboutApplication

Stopping Oracle Enterprise Manager 11g Database Control ... 
 ...  Stopped. 
[oracle@localhost ~]$ emctl start dbconsole
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
        LANGUAGE = (unset),
        LC_ALL = (unset),
        LANG = "c"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0 
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.

https://localhost:1158/em/console/aboutApplication

Starting Oracle Enterprise Manager 11g Database Control ..... started. 
------------------------------------------------------------------
Logs are generated in directory /u01/app/oracle/product/11.2.0/db_1/localhost_test/sysman/log

sysman解锁之后重启dbconsole恢复正常
修改sysman密码

SQL> alter user sysman identified by xifenfei;

User altered.

SQL> select account_status from dba_users where username='SYSMAN';

ACCOUNT_STATUS
--------------------------------
OPEN

重启dbconsole

[oracle@localhost ~]$  emctl stop dbconsole
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
        LANGUAGE = (unset),
        LC_ALL = (unset),
        LANG = "c"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0 
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.

https://localhost:1158/em/console/aboutApplication

Stopping Oracle Enterprise Manager 11g Database Control ... 
 ...  Stopped. 

[oracle@localhost ~]$  emctl start dbconsole
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
        LANGUAGE = (unset),
        LC_ALL = (unset),
        LANG = "c"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0 
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.

https://localhost:1158/em/console/aboutApplication

Starting Oracle Enterprise Manager 11g Database Control ..... started. 
------------------------------------------------------------------
Logs are generated in directory /u01/app/oracle/product/11.2.0/db_1/localhost_test/sysman/log

SQL>  select account_status from dba_users where username='SYSMAN';

ACCOUNT_STATUS
--------------------------------
LOCKED(TIMED)

由于dbconsole的密码存储在配置文件中，修改sysman密码，而配置文件的没有修改，因此使用错误是syman密码登录，超过了profile里面设置的10次，导致用户被锁.

解决sysman密码不对导致异常问题

[oracle@localhost ~]$ emctl stop dbconsole
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
        LANGUAGE = (unset),
        LC_ALL = (unset),
        LANG = "c"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0 
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.

https://localhost:1158/em/console/aboutApplication

Stopping Oracle Enterprise Manager 11g Database Control ... 
 ...  Stopped. 

[oracle@localhost config]$ pwd
/u01/app/oracle/product/11.2.0/db_1/localhost_test/sysman/config
[oracle@localhost config]$ ls -l emoms.properties 
-rw-r--r--. 1 oracle oinstall 1669 Nov 25 15:20 emoms.properties

[oracle@localhost config]$ grep emdRepPwd emoms.properties|grep -v emdRepPwdSeed
oracle.sysman.eml.mntr.emdRepPwd=e3299b5db6162f5d
oracle.sysman.eml.mntr.emdRepPwdEncrypted=TRUE


[oracle@localhost config]$ grep emdRepPwd emoms.properties|grep -v emdRepPwdSeed
oracle.sysman.eml.mntr.emdRepPwd=xifenfei
oracle.sysman.eml.mntr.emdRepPwdEncrypted=FALSE

SQL>  alter user sysman account unlock;

User altered.

[oracle@localhost ~]$ emctl start dbconsole
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
        LANGUAGE = (unset),
        LC_ALL = (unset),
        LANG = "c"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0 
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.

https://localhost:1158/em/console/aboutApplication

Starting Oracle Enterprise Manager 11g Database Control ..... started. 
------------------------------------------------------------------
Logs are generated in directory /u01/app/oracle/product/11.2.0/db_1/localhost_test/sysman/log

通过人工修改$ORACLE_HOME/localhost_sid/sysman/config/emoms.properties文件中的oracle.sysman.eml.mntr.emdRepPwd=密码，oracle.sysman.eml.mntr.emdRepPwdEncrypted=false实现修改密码

[oracle@localhost config]$ grep emdRepPwd emoms.properties|grep -v emdRepPwdSeed
oracle.sysman.eml.mntr.emdRepPwd=aa537afcbab41e05
oracle.sysman.eml.mntr.emdRepPwdEncrypted=TRUE

dbconsole重启之后，密码又重新被修改为加密方式,在11.2之前修改sysman密码，注意要同步修改emoms.properties中相关密码信息

sysman修改密码( 11.2 and higher)

[oracle@localhost config]$ emctl stop dbconsole
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
        LANGUAGE = (unset),
        LC_ALL = (unset),
        LANG = "c"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0 
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.

https://localhost:1158/em/console/aboutApplication

Stopping Oracle Enterprise Manager 11g Database Control ... 
 ...  Stopped.

SQL> alter user sysman identified by xifenfei123;

User altered.

[oracle@localhost ~]$ emctl setpasswd dbconsole
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
        LANGUAGE = (unset),
        LC_ALL = (unset),
        LANG = "c"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0 
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.

https://localhost:1158/em/console/aboutApplication

Please enter new repository password: 
Repository password successfully updated.

[oracle@localhost ~]$ emctl start dbconsole
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
        LANGUAGE = (unset),
        LC_ALL = (unset),
        LANG = "c"
    are supported and installed on your system.
perl: warning: Falling back to the standard locale ("C").
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0 
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.

https://localhost:1158/em/console/aboutApplication

Starting Oracle Enterprise Manager 11g Database Control ..... started. 
------------------------------------------------------------------
Logs are generated in directory /u01/app/oracle/product/11.2.0/db_1/localhost_test/sysman/log

发表在 Oracle | 标签为 dbconsole, emdRepPwd, emdRepPwdEncrypted, emoms.properties, sysman | 评论关闭

move lob导致index失效

发表于 2017 年 11 月 22 日由惜分飞

在一次数据库升级过程中,因为blug需要move lob，导致表相关index失效,这里通过实验重现
数据库版本

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

创建用户

SQL> create user xff identified by oracle;

User created.

SQL> grant dba to xff;

Grant succeeded.

创建测试表和插入数据

SQL> conn xff/oracle
Connected.

SQL> create table t_xff (id int primary key not null,name varchar2(20),c_lob clob);

Table created.

SQL> insert into t_xff values(1,'www.xifenfei.com','//www.xifenfei.com');

1 row created.

SQL>  insert into t_xff values(2,'www.xifenfei.com','//www.xifenfei.com');

1 row created.

SQL> commit;

Commit complete.

SQL> select index_name,status from user_indexes;

INDEX_NAME                     STATUS
------------------------------ --------
SYS_IL0000090094C00003$$       VALID
SYS_C0011148                   VALID

move lob

SQL> ALTER TABLE t_xff MOVE LOB (c_lob) store as (tablespace users);

Table altered.

SQL>  select index_name,status from user_indexes;

INDEX_NAME                     STATUS
------------------------------ --------
SYS_IL0000090094C00003$$       VALID
SYS_C0011148                   UNUSABLE

SQL>  insert into t_xff values(3,'www.xifenfei.com','//www.xifenfei.com');
 insert into t_xff values(3,'www.xifenfei.com','//www.xifenfei.com')
*
ERROR at line 1:
ORA-01502: index 'XFF.SYS_C0011148' or partition of such index is in unusable
state

这里很明显，当我们move lob之后，表的index变为无效,插入操作无法进行

rebuind index

SQL> alter index xff.SYS_C0011148 rebuild;

Index altered.

SQL> select index_name,status from user_indexes;

INDEX_NAME                     STATUS
------------------------------ --------
SYS_IL0000090094C00003$$       VALID
SYS_C0011148                   VALID

SQL> insert into t_xff values(3,'www.xifenfei.com','//www.xifenfei.com');

1 row created.

SQL> commit;

Commit complete.

这个测试就是告诫自己,做oracle 不要想当然，move lob之后，表相关的index 都会失效，需要rebuild。具体参见:
Bug 6525073 : STBH: INDEX IS IN UNUSABLE STATE AFTER A LOB COLUMN IS MOVED
ALTER TABLE MOVE LOB makes indexes unusable on the parent table (Doc ID 1228324.1)

发表在 Oracle | 标签为 index UNUSABLE, move lob, ORA-01502 | 评论关闭

17813235971

QQ 咨询

月归档：十一月 2017

asm磁盘组操作不当导致数据文件丢失恢复

dbconsole和sysman用户

move lob导致index失效

文章分类

最近发表

月归档：十一月 2017

asm磁盘组操作不当导致数据文件丢失恢复

dbconsole和sysman用户

move lob导致index失效

标签云

文章分类

最近发表