检查: select local_tran_id,state from DBA_2PC_PENDING;若是state为:prepared的话,可以强制回滚,forced rollback表示已经强制回滚了的。强制回滚命令:rollback force 'local_tran_id';如:rollback force '13.83.15198';
open_cursors = 1000 sql_trace = TRUE star_transformation_enabled= FALSE query_rewrite_enabled = FALSE pga_aggregate_target = 75497472 aq_tm_processes = 1 PMON started with pid=2 DBW0 started with pid=3 LGWR started with pid=4 CKPT started with pid=5 SMON started with pid=6 RECO started with pid=7 CJQ0 started with pid=8 QMN0 started with pid=9 Tue Mar 27 15:28:08 2007 starting up 1 shared server(s) ... starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'... Tue Mar 27 15:28:08 2007 ALTER DATABASE MOUNT Tue Mar 27 15:28:12 2007 Successful mount of redo thread 1, with mount id 2375856200. Tue Mar 27 15:28:12 2007 Database mounted in Exclusive Mode. Completed: ALTER DATABASE MOUNT Tue Mar 27 15:28:12 2007 ALTER DATABASE OPEN Tue Mar 27 15:28:13 2007 Thread 1 opened at log sequence 35 Current log# 1 seq# 35 mem# 0: /dev/vgora/rra_redo1_1_200m Current log# 1 seq# 35 mem# 1: /dev/vgora/rra_redo1_2_200m Successful open of redo thread 1. Tue Mar 27 15:28:13 2007 SMON: enabling cache recovery Tue Mar 27 15:28:13 2007 Undo Segment 1 Onlined Undo Segment 2 Onlined Undo Segment 3 Onlined Undo Segment 4 Onlined Undo Segment 5 Onlined Undo Segment 6 Onlined Undo Segment 7 Onlined Undo Segment 8 Onlined Undo Segment 9 Onlined Undo Segment 10 Onlined Successfully onlined Undo Tablespace 1. Tue Mar 27 15:28:13 2007 SMON: enabling tx recovery Tue Mar 27 15:28:13 2007 Database Characterset is ZHS16GBK replication_dependency_tracking turned off (no async multimaster replication found) Completed: ALTER DATABASE OPEN Tue Mar 27 15:29:17 2007 ORA-000060: Deadlock detected. More info in file /oracle/OraHome1/rdbms/log/czczjgk_ora_5374.trc. Tue Mar 27 15:56:50 2007 ORA-000060: Deadlock detected. More info in file /oracle/OraHome1/rdbms/log/czczjgk_ora_5484.trc. Tue Mar 27 15:58:18 2007 ORA-000060: Deadlock detected. More info in file /oracle/OraHome1/rdbms/log/czczjgk_ora_5484.trc. Tue Mar 27 15:58:55 2007 ORA-000060: Deadlock detected. More info in file /oracle/OraHome1/rdbms/log/czczjgk_ora_5484.trc. Tue Mar 27 15:59:58 2007 /* OracleOEM */ DROP TABLESPACE "YYUNION" INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS Tue Mar 27 16:00:01 2007 ORA-000060: Deadlock detected. More info in file /oracle/OraHome1/rdbms/log/czczjgk_ora_5484.trc. ORA-604 signalled during: /* OracleOEM */ DROP TABLESPACE "YYUNION" INCLUDIN... Tue Mar 27 17:50:13 2007 /* OracleOEM */ ALTER DATABASE DATAFILE '/dev/vgora/rra_extend_009_5000m' AUTOEXTEND ON NEXT 10M Tue Mar 27 17:50:13 2007 Completed: /* OracleOEM */ ALTER DATABASE DATAFILE '/dev/vgor Tue Mar 27 17:54:56 2007 /* OracleOEM */ DROP TABLESPACE "YYUNION" INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS Tue Mar 27 17:54:59 2007 ORA-000060: Deadlock detected. More info in file /oracle/OraHome1/rdbms/log/czczjgk_ora_5951.trc. ORA-604 signalled during: /* OracleOEM */ DROP TABLESPACE "YYUNION" INCLUDIN... Tue Mar 27 17:55:10 2007 /* OracleOEM */ DROP TABLESPACE "YYUNION" INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS Tue Mar 27 17:55:14 2007 ORA-000060: Deadlock detected. More info in file /oracle/OraHome1/rdbms/log/czczjgk_ora_5951.trc. ORA-604 signalled during: /* OracleOEM */ DROP TABLESPACE "YYUNION" INCLUDIN... Tue Mar 27 17:55:40 2007 /* OracleOEM */ ALTER DATABASE DATAFILE '/dev/vgora/rra_extend_009_5000m' RESIZE 300M ORA-1237 signalled during: /* OracleOEM */ ALTER DATABASE DATAFILE '/dev/vgor... Tue Mar 27 17:56:04 2007 /* OracleOEM */ ALTER DATABASE RENAME FILE '/dev/vgora/rra_extend_009_5000m' TO '/dev/vgora/rra_extend_007_5000m' ORA-1511 signalled during: /* OracleOEM */ ALTER DATABASE RENAME FILE '/dev/v... 这是日志上报错的部门我实在是看不懂,.
select local_tran_id,state from DBA_2PC_PENDING;若是state为:prepared的话,可以强制回滚,forced rollback表示已经强制回滚了的。强制回滚命令:rollback force 'local_tran_id';如:rollback force '13.83.15198';
2、执行操作
SQL>show parameter dump如果返回值如下:
background_core_dump string partial
background_dump_dest string /opt/oracle/admin/card/bdump
core_dump_dest string /opt/oracle/admin/card/cdump
max_dump_file_size string UNLIMITED
shadow_core_dump string partial
user_dump_dest string /opt/oracle/admin/card/udump 则/opt/oracle/admin/card/bdump为alter_sid.log所在的位置。
sql_trace = TRUE
star_transformation_enabled= FALSE
query_rewrite_enabled = FALSE
pga_aggregate_target = 75497472
aq_tm_processes = 1
PMON started with pid=2
DBW0 started with pid=3
LGWR started with pid=4
CKPT started with pid=5
SMON started with pid=6
RECO started with pid=7
CJQ0 started with pid=8
QMN0 started with pid=9
Tue Mar 27 15:28:08 2007
starting up 1 shared server(s) ...
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Tue Mar 27 15:28:08 2007
ALTER DATABASE MOUNT
Tue Mar 27 15:28:12 2007
Successful mount of redo thread 1, with mount id 2375856200.
Tue Mar 27 15:28:12 2007
Database mounted in Exclusive Mode.
Completed: ALTER DATABASE MOUNT
Tue Mar 27 15:28:12 2007
ALTER DATABASE OPEN
Tue Mar 27 15:28:13 2007
Thread 1 opened at log sequence 35
Current log# 1 seq# 35 mem# 0: /dev/vgora/rra_redo1_1_200m
Current log# 1 seq# 35 mem# 1: /dev/vgora/rra_redo1_2_200m
Successful open of redo thread 1.
Tue Mar 27 15:28:13 2007
SMON: enabling cache recovery
Tue Mar 27 15:28:13 2007
Undo Segment 1 Onlined
Undo Segment 2 Onlined
Undo Segment 3 Onlined
Undo Segment 4 Onlined
Undo Segment 5 Onlined
Undo Segment 6 Onlined
Undo Segment 7 Onlined
Undo Segment 8 Onlined
Undo Segment 9 Onlined
Undo Segment 10 Onlined
Successfully onlined Undo Tablespace 1.
Tue Mar 27 15:28:13 2007
SMON: enabling tx recovery
Tue Mar 27 15:28:13 2007
Database Characterset is ZHS16GBK
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: ALTER DATABASE OPEN
Tue Mar 27 15:29:17 2007
ORA-000060: Deadlock detected. More info in file /oracle/OraHome1/rdbms/log/czczjgk_ora_5374.trc.
Tue Mar 27 15:56:50 2007
ORA-000060: Deadlock detected. More info in file /oracle/OraHome1/rdbms/log/czczjgk_ora_5484.trc.
Tue Mar 27 15:58:18 2007
ORA-000060: Deadlock detected. More info in file /oracle/OraHome1/rdbms/log/czczjgk_ora_5484.trc.
Tue Mar 27 15:58:55 2007
ORA-000060: Deadlock detected. More info in file /oracle/OraHome1/rdbms/log/czczjgk_ora_5484.trc.
Tue Mar 27 15:59:58 2007
/* OracleOEM */ DROP TABLESPACE "YYUNION" INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS
Tue Mar 27 16:00:01 2007
ORA-000060: Deadlock detected. More info in file /oracle/OraHome1/rdbms/log/czczjgk_ora_5484.trc.
ORA-604 signalled during: /* OracleOEM */ DROP TABLESPACE "YYUNION" INCLUDIN...
Tue Mar 27 17:50:13 2007
/* OracleOEM */ ALTER DATABASE DATAFILE '/dev/vgora/rra_extend_009_5000m' AUTOEXTEND ON NEXT 10M
Tue Mar 27 17:50:13 2007
Completed: /* OracleOEM */ ALTER DATABASE DATAFILE '/dev/vgor
Tue Mar 27 17:54:56 2007
/* OracleOEM */ DROP TABLESPACE "YYUNION" INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS
Tue Mar 27 17:54:59 2007
ORA-000060: Deadlock detected. More info in file /oracle/OraHome1/rdbms/log/czczjgk_ora_5951.trc.
ORA-604 signalled during: /* OracleOEM */ DROP TABLESPACE "YYUNION" INCLUDIN...
Tue Mar 27 17:55:10 2007
/* OracleOEM */ DROP TABLESPACE "YYUNION" INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS
Tue Mar 27 17:55:14 2007
ORA-000060: Deadlock detected. More info in file /oracle/OraHome1/rdbms/log/czczjgk_ora_5951.trc.
ORA-604 signalled during: /* OracleOEM */ DROP TABLESPACE "YYUNION" INCLUDIN...
Tue Mar 27 17:55:40 2007
/* OracleOEM */ ALTER DATABASE DATAFILE '/dev/vgora/rra_extend_009_5000m' RESIZE 300M
ORA-1237 signalled during: /* OracleOEM */ ALTER DATABASE DATAFILE '/dev/vgor...
Tue Mar 27 17:56:04 2007
/* OracleOEM */ ALTER DATABASE RENAME FILE '/dev/vgora/rra_extend_009_5000m' TO '/dev/vgora/rra_extend_007_5000m'
ORA-1511 signalled during: /* OracleOEM */ ALTER DATABASE RENAME FILE '/dev/v...
这是日志上报错的部门我实在是看不懂,.