undo数据文件损坏-重新创建控制文件

#####删除数据库文件###########

col file_name for a50
select file_name, tablespace_name from dba_data_files
FILE_NAME                                          TABLESPACE_NAME
-------------------------------------------------- ------------------------------------------------------------
/u01/app/oradata/QXY1/users01.dbf                  USERS
/u01/app/oradata/QXY1/undotbs01.dbf                UNDOTBS1
/u01/app/oradata/QXY1/sysaux01.dbf                 SYSAUX
/u01/app/oradata/QXY1/system01.dbf                 SYSTEM
/u01/app/oradata/QXY1/example01.dbf                EXAMPLE

5 rows selected.

rm -rf  /u01/app/oradata/QXY1/undotbs01.dbf

--//启动数据库提示报错
SQL> startup force
ORACLE instance started.

Total System Global Area  584568832 bytes
Fixed Size                  2230552 bytes
Variable Size             188745448 bytes
Database Buffers          385875968 bytes
Redo Buffers                7716864 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/u01/app/oradata/QXY1/undotbs1.dbf'

SQL> 

######手工构建pfile##################
SQL> create pfile='/tmp/pfile.ora' from spfile;

File created.

SQL> 

--//undo段的信息存放在1号225块()
--//获取方法
SQL> select US#,NAME,FILE# ,BLOCK# ,STATUS$ from undo$;

       US# NAME                                                              FILE#     BLOCK#    STATUS$
---------- ------------------------------------------------------------ ---------- ---------- ----------
         0 SYSTEM                                                                1        128          3
         1 _SYSSMU1_1240252155$                                                  3        128          1
         2 _SYSSMU2$                                                             6        528          1
         3 _SYSSMU3$                                                             6        544          5
         4 _SYSSMU4$                                                             3        128          3
         5 _SYSSMU5$                                                             3        144          3
         6 _SYSSMU6$                                                             3        160          3
         7 _SYSSMU7$                                                             3        176          3
         8 _SYSSMU8$                                                             3        192          3
         9 _SYSSMU9$                                                             3        208          3
        10 _SYSSMU10$                                                            3        224          3
        
 select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) blk# from undo$ where file#=6 and block#=544;
 
 
 SQL> select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) blk# from undo$ where file#=6 and block#=544;

     FILE#       BLK#
---------- ----------
         1        225
SQL> 

--//获取undo段名字
cat > undo.sh
#!/bin/sh
. ~/.bash_profile
echo -e "[Begin] start-time:`date '+%Y-%m-%d %H:%M:%S'`\n scan dba 1,520 , create bootstrap.txt about ktetbdba,ktetbnbk,high_water"
kdbrrow=`echo "map dba 1,225"  | rlbbed | egrep 'kdbr' | sed -e 's/^.*kdbr\[//' -e 's/\].*//'`
echo $kdbrrow
j=0;
while [ $j -le $kdbrrow]
do 
    echo "x /rnc dba 1,225 *kdbr[$j]" | rlbbed | grep 'SYS' | sed -e 's/^.*: //'
done
read -p "[End] end-time:`date '+%Y-%m-%d %H:%M:%S'`,enter continue..."

--//把获取的名字添加到/tmp/pfile.ora里面
*.undo_management='manual'
*._allow_resetlogs_corruption= TRUE
*._corrupted_rollback_segments=(_SYSSMU1_1240252155$,_SYSSMU2_111974964$,_SYSSMU3_4004931649$,_SYSSMU4_1126976075$,_SYSSMU5_4011504098$,_SYSSMU6_3654194381$,_SYSSMU7_4222772309$,_SYSSMU8_3612859353$,_SYSSMU9_3945653786$,_SYSSMU10_3271578125$,_SYSSMU11_125382609$,_SYSSMU12_2245433549$,_SYSSMU13_3242268464$,_SYSSMU14_44821983$,_SYSSMU15_1872739176$,_SYSSMU16_1376564431$,_SYSSMU17_1839632768$,_SYSSMU18_3088942417$,_SYSSMU19_2867910983$,_SYSSMU20_948290921$)

--//关闭数据库再次用新的pfile重启
shutdown immediate
startup pfile='/tmp/pfile.ora' nomount;

--//启动数据库到nomout状态重建控制文件
CREATE CONTROLFILE REUSE DATABASE "QXY1" RESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oradata/QXY1/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oradata/QXY1/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oradata/QXY1/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oradata/QXY1/system01.dbf',
  '/u01/app/oradata/QXY1/sysaux01.dbf',
  '/u01/app/oradata/QXY1/users01.dbf',
  '/u01/app/oradata/QXY1/example01.dbf'
CHARACTER SET WE8MSWIN1252
;

--//启动数据库
SQL> 
SQL> alter database open resetlogs;

Database altered.

SQL> 

--//查看数据文件
FILE_NAME                                          TABLESPACE_NAME
-------------------------------------------------- ------------------------------------------------------------
/u01/app/oradata/QXY1/example01.dbf                EXAMPLE
/u01/app/oradata/QXY1/users01.dbf                  USERS
/u01/app/oradata/QXY1/sysaux01.dbf                 SYSAUX
/u01/app/oradata/QXY1/system01.dbf                 SYSTEM
/u01/app/oracle/product/11.2.0/dbhome_1/dbs/MISSIN UNDOTBS1

--//创建新的spfile
 create spfile from pfile='/tmp/pfile.ora';

--//重启数据库
shutdown abort

--//创建新的undo
create undo tablespace undotbs2 datafile '/u01/app/oradata/QXY1/undotbs2.dbf' size 50m autoextend off;

--//切换undo
alter system set undo_tablespace='undotbs2' scope=spfile;

--//修改undo管理为自动
 alter system set undo_management=auto scope=spfile;
 
 
 --//删除旧的undo
drop tablespace UNDOTBS1 including contents and datafiles;

--//查看undo信息
SQL> show parameter undo 

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
undo_management                      string                 AUTO
undo_retention                       integer                900
undo_tablespace                      string                 undotbs2
SQL> 

--//再次重启数据库
startup force

--//再次查看数据文件
SQL> select file_name, tablespace_name from dba_data_files;

FILE_NAME                                          TABLESPACE_NAME
-------------------------------------------------- ------------------------------------------------------------
/u01/app/oradata/QXY1/example01.dbf                EXAMPLE
/u01/app/oradata/QXY1/users01.dbf                  USERS
/u01/app/oradata/QXY1/sysaux01.dbf                 SYSAUX
/u01/app/oradata/QXY1/system01.dbf                 SYSTEM
/u01/app/oradata/QXY1/undotbs2.dbf                 UNDOTBS2

SQL> 

<=======遇到问题
SQL>  alter database open resetlogs;
 alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00704: bootstrap process failure
ORA-00704: bootstrap process failure
ORA-00600: internal error code, arguments: [2662], [0], [1216924], [0],
[1225654], [4194449], [], [], [], [], [], []
Process ID: 84044
Session ID: 125 Serial number: 3

SQL> 

SQL>  oradebug setmypid
Statement processed.
SQL> oradebug dumpvar sga kcsgscn_
kcslf kcsgscn_ [060019598, 0600195C8) = 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 60019278 00000000
SQL> oradebug poke 0x060019598 8 1326545
BEFORE: [060019598, 0600195A0) = 00000000 00000000
AFTER:  [060019598, 0600195A0) = 00143DD1 00000000
SQL> alter database open resetlogs;

Database altered.

SQL>