#####删除数据库文件###########
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>