PRM-DUL 绿色版自带JAVA运行环境
https://zcdn.parnassusdata.com/DUL5108rc8_java.zip
Oracle MySQL Postgresql 数据恢复 微信: macleanliu 数据库讨论QQ群号: 171092051
SYS_NC00是一种系统自动生成的辅助列,在Oracle 12c以后大量出现在用户表中,其主要伴随一下这些功能出现:
property:
0x0008 = virtual column(old)
0x0020 = hidden column(old)
0x00010000 = expression column(new)
default$: is overloaded to store index expression
name: is system generated. It is SYS_NC<5 digit intcol#>$
SYS_C000$ 伴随以下功能出现:
各种未指定名字的约束
CONSTRAINT constraint_name
Specify a name for the constraint. If you omit this identifier, then Oracle Database generates a name with the form SYS_Cn. Oracle stores the name and the definition of the integrity constraint in the USER_, ALL_, and DBA_CONSTRAINTS data dictionary views (in the CONSTRAINT_NAME and SEARCH_CONDITION columns, respectively).
The references_clause of the ref_constraint syntax lets you define a foreign key constraint on the REF column. This clause also implicitly restricts the scope of the REF column or attribute to the referenced table. However, whereas a foreign key constraint on a non-REF column references an actual column in the parent table, a foreign key constraint on a REF column references the implicit object identifier column of the parent table.
If you do not specify a constraint name, then Oracle generates a system name for the constraint of the form SYS_Cn.
https://docs.oracle.com/cd/B19306_01/server.102/b14200/clauses002.htm#sthref2891
SYS_STU SYS_STS伴随多列统计信息出现 ; 其中STU代表用户创建的多列统计信息, STS是SYS_STS are system generated from the DECODE in ALL_STAT_EXTENSIONS
Create column groups for the customers_test table based on the usage information captured during the monitoring window.
For example, run the following query:
SELECT DBMS_STATS.CREATE_EXTENDED_STATS(user, ‘customers_test’) FROM DUAL;
Sample output appears below:
###########################################################################
EXTENSIONS FOR SH.CUSTOMERS_TEST
…………………………..
1. (CUST_CITY, CUST_STATE_PROVINCE,
COUNTRY_ID) :SYS_STUMZ$C3AIHLPBROI#SKA58H_N created
2. (CUST_STATE_PROVINCE, COUNTRY_ID):SYS_STU#S#WF25Z#QAHIHE#MOFFMM_ created
###########################################################################
The database created two column groups for customers_test: one column group for the filter predicate and one group for the GROUP BY operation.
用户所自行创建的虚拟列 其SYS.COL$ 的property一般等于65544
main.dart import 'package:flutter/material.dart'; void main() => runApp(MyApp()); class MyApp extends StatelessWidget { @override Widget build(BuildContext context) { return MaterialApp( debugShowCheckedModeBanner: false, title: 'This is a Slide Example', home: Scaffold( appBar: AppBar( title: Text('ABC'), ), body: Column(children:[ Padding( child: Text( 'This is a Slide Example', style: TextStyle(fontSize: 20), ), padding: EdgeInsets.all(50), ), SlideExample(), RangeSliderExample() ]))); } } class SlideExample extends StatefulWidget { @override _Slider1State createState() => _Slider1State(); } class _Slider1State extends State { double rating = 0; @override Widget build(BuildContext context) { // TODO: implement build return Slider( label: "$rating", divisions: 10, value: rating, onChanged: (newRating) { setState(() => rating = newRating); }, ); } } class RangeSliderExample extends StatefulWidget { @override _RangeSliderExampleState createState() => _RangeSliderExampleState(); } class _RangeSliderExampleState extends State { var selectedRange = RangeValues(0.2, 0.8); @override Widget build(BuildContext context) { // TODO: implement build return RangeSlider( values: selectedRange, onChanged: (RangeValues newRange) { setState(() { selectedRange = newRange; }); }, ); } }
首要步骤是关闭MYSQL实例和备份数据目录
/etc/init.d/mysqld stop
mkdir /root/myql_backup
tar -jcvf /root/myql_backup/mysql_data.tar.bz2 /var/lib/mysql
尝试启动 mysql 实例
/etc/init.d/mysqld start
如果实例不崩溃那么 用mysqldump做逻辑备份
/etc/init.d/mysql start
mysqldump –single-transaction -AER > /root/dump_wtrans.sql
mysqldump -AER > /root/dump.sql
如果 MYSQL 实例崩溃则 尝试设置 innodb_force_recovery 参数
例如
mode=1; sed -i “/^\[mysqld\]/{N;s/$/\ninnodb_force_recovery=$mode/}” /etc/my.cnf
注意这些的mode=1 设置了 innodb_force_recovery 参数的级别
innodb_force_recovery 的参数级别含义为:
Mode 1 当发现一个损坏的数据页时不让MYSQL实例自动崩溃
Mode 2 不启用后台操作
Mode 3 不尝试 回滚事务
Mode 4 不计算状态也不应用buffer change
Mode 5 在启动过程中不去关心undo log
Mode 6 在启动过程中不关心重做日志ib_logfiles, 不去做前滚
以上 mode 2 会包含 mode 1, mode 3会包含 mode 2和 mode 1 ,依此类推
同时从 MYSQL 5.6.15 开始 mode 4-6 会让mysql 以只读模式运行
若已经设置了innodb_force_recovery =6 还是有问题 ,那么需要进一步诊断
检查日志
tail -200 /var/lib/mysql/`hostname`.err
tail -f /var/log/mysqld.log
如发现
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 515891.
以上报错说明mysql innodb 引擎发现有数据页损坏 进一步诊断:
#!/bin/bash
for i in $(ls /var/lib/mysql/*/*.ibd)
do
innochecksum $i
done
or
DATADIR=/var/lib/mysql; find $DATADIR -type f -name *.ibd -o -name ibdata* | xargs -I{} innochecksum {}
如发现
mysql: 120901 9:43:55 InnoDB: Error: page 70944 log sequence number 8 1483471899
mysql: InnoDB: is in the future! Current system log sequence number 5 612394935.
mysql: InnoDB: Your database may be corrupt or you may have copied the InnoDB
mysql: InnoDB: tablespace but not the InnoDB log files. See
mysql: InnoDB: [url=http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html]MySQL :: MySQL 5.5 Reference Manual :: 14.21.2 Starting InnoDB on a Corrupted Database[/url]
以上日志为LSN 不同步问题, 其一般可以通过 设置 innodb_force_recovery解决的
如发现
[ERROR] Table ./database/table has no primary key in InnoDB data dictionary, but has one in MySQL!
InnoDB: Error: table ‘database/table’
InnoDB: in InnoDB data dictionary has tablespace id 423,
InnoDB: but tablespace with that id or name does not exist. Have
InnoDB: you deleted or moved .ibd files?
[ERROR] Cannot find or open table database/table from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn’t support.
可能丢失了表的.frm 或 .ibd文件 ,找回这些文件
当实例可以启动 那么如何检测表是否存在讹误?
可以使用 check table 命令
mysql> CHECK TABLE roundcube.users;
+—————–+——-+———-+———-+
| Table | Op | Msg_type | Msg_text |
+—————–+——-+———-+———-+
| roundcube.users | check | status | OK |
+—————–+——-+———-+———-+
1 row in set (0.13 sec)
mysql> CHECK TABLE roundcube.dictionary;
+———————-+——-+———-+—————————————————————-+
| Table | Op | Msg_type | Msg_text |
+———————-+——-+———-+—————————————————————-+
| roundcube.dictionary | check | Warning | InnoDB: Tablespace is missing for table ’roundcube/dictionary’ |
| roundcube.dictionary | check | Error | Table ’roundcube.dictionary’ doesn’t exist |
| roundcube.dictionary | check | status | Operation failed |
+———————-+——-+———-+—————————————————————-+
3 rows in set (0.00 sec)
如果以上检测发现讹误,则可能导致mysql崩溃 ,那么可能需要使用 innodb_force_recovery=1 来启动实例 否则可能启动时实例崩溃
可以使用 mysqlcheck 来检测一个数据库
[root@ocp ~]# mysqlcheck -A -uroot -p
Enter password:
mysql.columns_priv OK
mysql.component OK
mysql.db OK
mysql.default_roles OK
mysql.engine_cost OK
mysql.func OK
mysql.general_log OK
mysql.global_grants OK
mysql.gtid_executed OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.innodb_index_stats OK
mysql.innodb_table_stats OK
mysql.password_history OK
mysql.plugin OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.role_edges OK
mysql.server_cost OK
mysql.servers OK
mysql.slave_master_info OK
mysql.slave_relay_log_info OK
mysql.slave_worker_info OK
mysql.slow_log OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.user OK
recovery.testeasy OK
sys.sys_config OK
如何从frm文件中恢复create table建表语句
可以从 https://downloads.mysql.com/archives/utilities/ 下载 MySQL Utilities
tar xvzf mysql-utilities*
cd mysql-utilities-1.4.3
chmod +x setup.py
./setup.py build
./setup.py install
mysqlfrm 会自己建一个mysql实例 需要单独的端口
mysqlfrm –basedir=/usr –user=mysql –port=3308 /var/lib/mysql/testdb/staff.frm
# Spawning server with –user=mysql.
# Starting the spawned server on port 3308 … done.
# Reading .frm files
#
# Reading the staff.frm file.
#
# CREATE statement for staff.frm:
#
CREATE TABLE `staff` (
`staff_id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
`first_name` varchar(45) NOT NULL,
`last_name` varchar(45) NOT NULL,
`address_id` smallint(5) unsigned NOT NULL,
`picture` blob,
`email` varchar(50) DEFAULT NULL,
`store_id` tinyint(3) unsigned NOT NULL,
`active` tinyint(1) NOT NULL DEFAULT ‘1’,
`username` varchar(16) NOT NULL,
`password` varchar(40) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`staff_id`),
KEY `idx_fk_store_id` (`store_id`),
KEY `idx_fk_address_id` (`address_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
#…done.
有时需要 SET FOREIGN_KEY_CHECKS=0; 才能建表成功
在实例 没崩溃情况下 如何重建有问题的表
mysql -u root -p
USE dbname;
CREATE TABLE tablename_recovered LIKE tablename;
INSERT INTO tablename_recovered SELECT * FROM tablename;
这里 先见表 后插入
插入如果 遇到问题页面 ,那么可以用limit
insert ignore into tablename_recovered select * from tablename limit 10;
insert ignore into tablename_recovered select * from tablename limit 50;
insert ignore into tablename_recovered select * from tablename limit 100;
insert ignore into tablename_recovered select * from tablename limit 200;
…
测试 下插入到哪个位置 才报错
如果成功复制表 那么把原表删除 新表重命名
DROP dbname.tablename;
RENAME TABLE dbname.tablename_recovered TO dbname.tablename;
还原 所有数据库和 重置 ibdata/ib_log files
首先要有 有效的备份, 做这些操作前 确认这个备份可用!!
mysqldump -AER > /root/recovery_dump.sql
干掉所有 有问题的db
mysql> SET FOREIGN_KEY_CHECKS=0;
mysql> DROP DATABASE db1;
mysql> DROP DATABASE db2;
…
重启mysql 实例
mysql -e “SET GLOBAL innodb_fast_shutdown = 0”
/etc/init.d/mysql stop
sed -i ‘/innodb_force_recovery/d’ /etc/my.cnf
nohup /etc/init.d/mysql start & tail -f /var/lib/mysql/`hostname`.err
恢复 数据
mysql < /root/recovery_dump.sql
对于 Log Sequence Number Mismatched/In Future 可以考虑重建整个数据库 也可以重建重做日志
mysql -e “SET GLOBAL innodb_fast_shutdown = 0”
/etc/init.d/mysql stop
cd /var/lib/mysql
mv ib_logfile0 ib_logfile0.bak
mv ib_logfile1 ib_logfile1.bak
/etc/init.d/mysql start
或者 临时交换存储引起 为myisam 然后换回来
mysql -e “SELECT concat(‘ALTER TABLE ‘, TABLE_NAME,’ ENGINE=MYISAM;’) FROM Information_schema.TABLES WHERE TABLE_SCHEMA = ‘db_name’ AND ENGINE = ‘InnoDB’ AND TABLE_TYPE = ‘BASE TABLE'”
/etc/init.d/mysql stop
mkdir -p /root/innodb.bak
mv ib* /root/innodb.bak/
/etc/init.d/mysql start
mysql -e “SELECT concat(‘ALTER TABLE ‘, TABLE_NAME,’ ENGINE=InnoDB;’) FROM Information_schema.TABLES WHERE TABLE_SCHEMA = ‘db_name’ AND ENGINE = ‘MyISAM'”
如何合适的删除.ibd文件 可以使用如下命令 SET FOREIGN_KEY_CHECKS=0;
mysql -e “ALTER TABLE roundcube.staff DISCARD TABLESPACE”
如果误删除了 .ibd文件 如何 基于备份恢复前的清理工作
[ERROR] MySQL is trying to open a table handle but the .ibd file for table dbname/tblname does not exist.
ALTER TABLE dbname.tblname DISCARD TABLESPACE;
InnoDB: Warning: cannot delete tablespace 251 in DISCARD TABLESPACE.
InnoDB: But let us remove the insert buffer entries for this tablespace.
DROP TABLE dbname.tblname;
InnoDB: We removed now the InnoDB internal data dictionary entry InnoDB: of table `dbname/tblname`.
如上完成后 再恢复重建整个表 .frm 文件在 但数据字典里没这个表了 先备份.frm 文件
然后执行 create table重建表 丢失.frm文件
但字典里有这个表
InnoDB: Error: table dbname/tblname already exists in
InnoDB internal InnoDB: data dictionary.
Have you deleted the .frm file
InnoDB: and not used DROP TABLE? …
InnoDB: You can drop the orphaned table inside InnoDB by
InnoDB: creating an InnoDB table with the same name in another
InnoDB: database and moving the .frm file to the current database.
InnoDB: Then MySQL thinks the table exists, and DROP TABLE will InnoDB: succeed.
可以在别的db下重建这个表获得.frm 文件 然后拷贝过去
# mysql
mysql> CREATE DATABASE test2;
mysql> CREATE TABLE … CHARSET=utf8;
mysql> quit
# cp /var/lib/mysql/test2/test.frm /var/lib/mysql/test/
# mysql
mysql> SET FOREIGN_KEY_CHECKS=0;
mysql> DROP TABLE test.test;
MYSQL 5.5.62
#如果之前有MYSQL 那么先停服务 /etc/init.d/mysqld stop #如果之前已经装过MYSQL 那么先备份数据目录 tar -jcvf mysql.tar.bz2 /var/lib/mysql #卸载之前已经安装过的MYSQL软件RPM rpm -qa|grep mysq| xargs rpm -e --nodeps #下载MYSQL 5.5.62 wget --no-check-certificate https://zcdn.askmaclean.com/mysql-community-common-5.5.62-2.el6.x86_64.rpm wget --no-check-certificate https://zcdn.askmaclean.com/mysql-community-libs-compat-5.5.62-2.el6.x86_64.rpm wget --no-check-certificate https://zcdn.askmaclean.com/mysql-community-libs-5.5.62-2.el6.x86_64.rpm wget --no-check-certificate https://zcdn.askmaclean.com/mysql-community-client-5.5.62-2.el6.x86_64.rpm wget --no-check-certificate https://zcdn.askmaclean.com/mysql-community-server-5.5.62-2.el6.x86_64.rpm #安装 rpm -ivh mysql-community-libs-compat-5.5.62-2.el6.x86_64.rpm mysql-community-client-5.5.62-2.el6.x86_64.rpm mysql-community-common-5.5.62-2.el6.x86_64.rpm mysql-community-libs-5.5.62-2.el6.x86_64.rpm mysql-community-server-5.5.62-2.el6.x86_64.rpm 修改/etc/my.cnf 中的 datadir 到 你要的目录 mkdir /@@datadir chown mysql:mysql /@@datadir mysql_install_db chown -R mysql:mysql /@@datadir mysqld_safe & mysql SET PASSWORD FOR 'root'@'localhost' = PASSWORD('mysql'); FLUSH PRIVILEGES; exit /etc/init.d/mysqld stop /etc/init.d/mysqld start mysql -uroot -pmysql
MYSQL 5.6.46
#如果之前有MYSQL 那么先停服务 /etc/init.d/mysqld stop #如果之前已经装过MYSQL 那么先备份数据目录 tar -jcvf mysql.tar.bz2 /var/lib/mysql #卸载之前已经安装过的MYSQL软件RPM rpm -qa|grep mysq| xargs rpm -e --nodeps #下载MYSQL 5.6.46 wget --no-check-certificate https://zcdn.askmaclean.com/mysql-community-common-5.6.46-2.el6.x86_64.rpm wget --no-check-certificate https://zcdn.askmaclean.com/mysql-community-libs-compat-5.6.46-2.el6.x86_64.rpm wget --no-check-certificate https://zcdn.askmaclean.com/mysql-community-libs-5.6.46-2.el6.x86_64.rpm wget --no-check-certificate https://zcdn.askmaclean.com/mysql-community-client-5.6.46-2.el6.x86_64.rpm wget --no-check-certificate https://zcdn.askmaclean.com/mysql-community-server-5.6.46-2.el6.x86_64.rpm #安装 rpm -ivh mysql-community-libs-compat-5.6.46-2.el6.x86_64.rpm mysql-community-libs-5.6.46-2.el6.x86_64.rpm mysql-community-client-5.6.46-2.el6.x86_64.rpm mysql-community-common-5.6.46-2.el6.x86_64.rpm mysql-community-server-5.6.46-2.el6.x86_64.rpm 修改/etc/my.cnf 中的 datadir 到 你要的目录 mkdir /@@datadir chown mysql:mysql /@@datadir mysql_install_db chown -R mysql:mysql /@@datadir mysqld_safe & mysql SET PASSWORD FOR 'root'@'localhost' = PASSWORD('mysql'); FLUSH PRIVILEGES; exit /etc/init.d/mysqld stop /etc/init.d/mysqld start mysql -uroot -pmysql
5.7.28
>#如果之前有MYSQL 那么先停服务 /etc/init.d/mysqld stop #如果之前已经装过MYSQL 那么先备份数据目录 tar -jcvf mysql.tar.bz2 /var/lib/mysql #卸载之前已经安装过的MYSQL软件RPM rpm -qa|grep mysq| xargs rpm -e --nodeps #下载MYSQL 5.7.28 wget --no-check-certificate https://zcdn.askmaclean.com/mysql-community-common-5.7.28-1.el6.x86_64.rpm wget --no-check-certificate https://zcdn.askmaclean.com/mysql-community-libs-compat-5.7.28-1.el6.x86_64.rpm wget --no-check-certificate https://zcdn.askmaclean.com/mysql-community-libs-5.7.28-1.el6.x86_64.rpm wget --no-check-certificate https://zcdn.askmaclean.com/mysql-community-client-5.7.28-1.el6.x86_64.rpm wget --no-check-certificate https://zcdn.askmaclean.com/mysql-community-server-5.7.28-1.el6.x86_64.rpm rpm -ivh mysql-community-common-5.7.28-1.el6.x86_64.rpm mysql-community-libs-compat-5.7.28-1.el6.x86_64.rpm mysql-community-libs-5.7.28-1.el6.x86_64.rpm mysql-community-client-5.7.28-1.el6.x86_64.rpm mysql-community-server-5.7.28-1.el6.x86_64.rpm 修改/etc/my.cnf 中的 datadir 到 你要的目录 mkdir /@@datadir chown mysql:mysql /@@datadir mysqld --initialize chown -R mysql:mysql /@@datadir mysqld_safe --skip-grant-tables & mysql UPDATE mysql.user SET authentication_string = PASSWORD('mysql') , password_expired = 'N' WHERE User = 'root' AND Host = 'localhost'; FLUSH PRIVILEGES; exit /etc/init.d/mysqld stop /etc/init.d/mysqld start mysql -uroot -pmysql
8.0.17
>>#如果之前有MYSQL 那么先停服务 /etc/init.d/mysqld stop #如果之前已经装过MYSQL 那么先备份数据目录 tar -jcvf mysql.tar.bz2 /var/lib/mysql #卸载之前已经安装过的MYSQL软件RPM rpm -qa|grep mysq| xargs rpm -e --nodeps #下载MYSQL 8.0.17 wget --no-check-certificate https://zcdn.askmaclean.com/mysql-community-common-8.0.17-1.el6.x86_64.rpm wget --no-check-certificate https://zcdn.askmaclean.com/mysql-community-libs-8.0.17-1.el6.x86_64.rpm wget --no-check-certificate https://zcdn.askmaclean.com/mysql-community-client-8.0.17-1.el6.x86_64.rpm wget --no-check-certificate https://zcdn.askmaclean.com/mysql-community-server-8.0.17-1.el6.x86_64.rpm rpm -ivh mysql-community-client-8.0.17-1.el6.x86_64.rpm mysql-community-common-8.0.17-1.el6.x86_64.rpm mysql-community-libs-8.0.17-1.el6.x86_64.rpm mysql-community-server-8.0.17-1.el6.x86_64.rpm 修改/etc/my.cnf 中的 datadir 到 你要的目录 mkdir /@@datadir chown mysql:mysql /@@datadir mysqld --initialize chown -R mysql:mysql /@@datadir mysqld_safe --skip-grant-tables & mysql UPDATE mysql.user SET authentication_string=null WHERE User='root'; FLUSH PRIVILEGES; exit; /etc/init.d/mysqld stop /etc/init.d/mysqld start mysql ALTER USER 'root'@'localhost' IDENTIFIED BY 'mysql'; FLUSH PRIVILEGES; exit; mysql -uroot -pmysql
The In-Out Column 其代表执行步骤的 串行与并行输入输出情况
SERIAL (blank): Serial execution. Currently, SQL is not loaded in the OTHER column for this case. SERIAL_FROM_REMOTE (S -> R): Serial execution at a remote site. PARALLEL_FROM_SERIAL (S -> P): Serial execution. Output of step is partitioned or broadcast to parallel execution servers. PARALLEL_TO_SERIAL (P -> S): Parallel execution. Output of step is returned to serial QC process. PARALLEL_TO_PARALLEL (P -> P): Parallel execution. Output of step is repartitioned to second set of parallel execution servers. PARALLEL_COMBINED_WITH_PARENT (PWP): Parallel execution; Output of step goes to next step in same parallel process. No interprocess communication to parent. PARALLEL_COMBINED_WITH_CHILD (PWC): Parallel execution. Input of step comes from prior step in same parallel process. No interprocess communication from child. https://docs.oracle.com/database/121/TGSQL/tgsql_interp.htm#TGSQL94734
Transaction recovery: lock conflict caught and ignored
之前有用户在11g上alert.log出现大量Transaction recovery: lock conflict caught and ignored,同时产生大量redo重做日志;观察AWR可以发现大量db block changes发生在UNDO$基础表上。
对于该问题可以尝试如下几种方案:
1、找出Dead Transaction并清理相关对象,如 https://dba010.com/2013/04/30/transaction-recovery-lock-conflict-caught-and-ignored/
ALERT.LOG: ..... Transaction recovery: lock conflict caught and ignored ..... And also some incident files are being created in $ORACLE_BASE/diag/rdbms/dbname/instancename/incident folder. In my case the error started after SUPPLEMENTAL LOGGING enabled in a RAC environment. After disabling it the messages have not disappeared, but incident files are no longer being created. 1. Dead Trasaction SQL> select b.name useg, b.inst# instid, b.status$ status, a.ktuxeusn xid_usn, a.ktuxeslt xid_slot, a.ktuxesqn xid_seq, a.ktuxesiz undoblocks, a.ktuxesta txstatus from x$ktuxe a, undo$ b where a.ktuxecfl like ‘%DEAD%’ and a.ktuxeusn = b.us#; USEG INSTID STATUS XID_USN XID_SLOT XID_SEQ UNDOBLOCKS TXSTATUS _SYSSMU7_881277423$ 1 3 7 13 1829999 1 ACTIVE _SYSSMU8_4204495590$ 1 3 8 32 3045564 1 ACTIVE _SYSSMU10_1314081219$ 1 3 10 3 11844457 1 ACTIVE Transaction id is XID_USN.XID_SLOT.XID_SEQ So in our case, for the first row it will be 7.13.1829999 2. Read transaction table from undo header. ALTER SYSTEM DUMP UNDO HEADER ‘_SYSSMU7_881277423$’; …. TRN TBL:: index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt ———————————————————————————————— 0x00 9 0x03 0x1bf45c 0x000b 0x0000.789de808 0x00c242eb 0x0000.000.00000000 0x00000001 0x00c242eb 1367258143 0x01 9 0x00 0x1c031b 0x0014 0x0000.789e6018 0x00c242fa 0x0000.000.00000000 0x00000001 0x00000000 1367258225 0x02 9 0x00 0x1c147a 0x000e 0x0000.789e694b 0x00c242fa 0x0000.000.00000000 0x00000001 0x00000000 1367258230 0x03 9 0x00 0x1c06f9 0x0016 0x0000.789e601c 0x00c242fa 0x0000.000.00000000 0x00000001 0x00000000 1367258225 0x04 9 0x00 0x1c06c8 0x0009 0x0000.789e3566 0x00c242f9 0x0000.000.00000000 0x00000001 0x00000000 1367258192 0x05 9 0x00 0x1c1167 0x0015 0x0000.789e357f 0x00c242ec 0x0000.000.00000000 0x00000001 0x00000000 1367258192 0x06 9 0x00 0x1c2716 0x0017 0x0000.789e69e1 0x00c242fa 0x0000.000.00000000 0x00000001 0x00000000 1367258230 0x07 9 0x00 0x1c1045 0x000c 0x0000.789e1bdb 0x00c242eb 0x0000.000.00000000 0x00000001 0x00000000 1367258170 0x08 9 0x00 0x1c2614 0x0005 0x0000.789e357e 0x00c242ec 0x0000.000.00000000 0x00000001 0x00000000 1367258192 0x09 9 0x00 0x1bfa03 0x0021 0x0000.789e3574 0x00c242f9 0x0000.000.00000000 0x00000001 0x00000000 1367258192 0x0a 9 0x00 0x1bf712 0x001e 0x0000.789e3246 0x00c242f1 0x0000.000.00000000 0x00000001 0x00000000 1367258190 0x0b 9 0x00 0x1c1e01 0x0007 0x0000.789e1bd9 0x00c242eb 0x0000.000.00000000 0x00000001 0x00000000 1367258170 0x0c 9 0x00 0x1c08e0 0x000a 0x0000.789e3244 0x00c242f1 0x0000.000.00000000 0x00000006 0x00000000 1367258190 0x0d 10 0x90 0x1bec6f 0x0038 0x0000.789e783e 0x00c242fb 0x0000.000.00000000 0x00000001 0x00c242fb 0 0x0e 9 0x00 0x1c068e 0x0010 0x0000.789e694d 0x00c242fa 0x0000.000.00000000 0x00000001 0x00000000 1367258230 0x0f 9 0x00 0x1c151d 0x0012 0x0000.789e3578 0x00c242ec 0x0000.000.00000000 0x00000001 0x00000000 1367258192 0x10 9 0x00 0x1c26bc 0x0006 0x0000.789e69df 0x00c242fa 0x0000.000.00000000 0x00000001 0x00000000 1367258230 0x11 9 0x00 0x1c16eb 0x0000 0x0000.789cbd77 0x00c242eb 0x0000.000.00000000 0x00000001 0x00000000 1367257923 0x12 9 0x00 0x1c082a 0x001d 0x0000.789e357c 0x00c242ec 0x0000.000.00000000 0x00000001 0x00000000 1367258192 0x13 9 0x00 0x1c1459 0x001f 0x0000.789e7891 0x00c242fc 0x0000.000.00000000 0x00000001 0x00000000 1367258238 0x14 9 0x00 0x1c14b8 0x0003 0x0000.789e601a 0x00c242fa 0x0000.000.00000000 0x00000001 0x00000000 1367258225 0x15 9 0x00 0x1c0457 0x0020 0x0000.789e39d3 0x00c242ec 0x0000.000.00000000 0x00000001 0x00000000 1367258195 0x16 9 0x00 0x1c1326 0x0002 0x0000.789e601d 0x00c242fa 0x0000.000.00000000 0x00000001 0x00000000 1367258225 0x17 9 0x00 0x1c0db5 0x001c 0x0000.789e788a 0x00c242fc 0x0000.000.00000000 0x00000001 0x00000000 1367258238 0x18 9 0x00 0x1bffe4 0x001b 0x0000.789e400d 0x00c242fa 0x0000.000.00000000 0x00000001 0x00000000 1367258200 0x19 9 0x00 0x1c16e3 0x0001 0x0000.789e5fd2 0x00c242fa 0x0000.000.00000000 0x00000001 0x00000000 1367258225 0x1a 9 0x00 0x1bdbb2 0x0018 0x0000.789e400b 0x00c242fa 0x0000.000.00000000 0x00000001 0x00000000 1367258200 0x1b 9 0x00 0x1c1141 0x0019 0x0000.789e453a 0x00c242fa 0x0000.000.00000000 0x00000001 0x00000000 1367258204 0x1c 9 0x00 0x1bc9a0 0x0013 0x0000.789e788e 0x00c242fc 0x0000.000.00000000 0x00000001 0x00000000 1367258238 0x1d 9 0x00 0x1c02ef 0x0008 0x0000.789e357d 0x00c242ec 0x0000.000.00000000 0x00000001 0x00000000 1367258192 0x1e 9 0x00 0x1c0b6e 0x0004 0x0000.789e3250 0x00c242f9 0x0000.000.00000000 0x00000009 0x00000000 1367258190 0x1f 9 0x00 0x1c00ad 0xffff 0x0000.789e78a1 0x00c242fc 0x0000.000.00000000 0x00000001 0x00000000 1367258238 0x20 9 0x00 0x1c166c 0x001a 0x0000.789e39dd 0x00c242fa 0x0000.000.00000000 0x00000002 0x00000000 1367258195 0x21 9 0x00 0x1c160b 0x000f 0x0000.789e3576 0x00c242ec 0x0000.000.00000000 0x00000001 0x00000000 1367258192 EXT TRN CTL:: usn: 7 State# 10 means active transaction. dba points to starting UNDO block address. usn: Undo segment number usn.index.wrap# gives transaction id. An active transaction 0x0007.00d.001bec6f is available in slot 0x0d which has a dba of 0x00c242fb (12731131 in decimal) 3. Reading UNDO Block: Identify fileID and blockID: fileID: select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(12731131) from x$dual; 3 blockID: select DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(12731131) from x$dual; 148219 Dumping block alter system dump datafile 3 block 148219; UNDO BLK: xid: 0x0007.00d.001bec6f seq: 0x41f9 cnt: 0x6 irb: 0x5 icl: 0x0 flg: 0x0000 Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset ————————————————————————— 0x01 0x1f98 0x02 0x1f2c 0x03 0x1d7c 0x04 0x1d10 0x05 0x1ca0 0x06 0x1bfc *—————————– * Rec #0x1 slt: 0x0d objn: 0(0x00000000) objd: 0 tblspc: 0(0x00000000) * Layer: 5 (Transaction Undo) opc: 7 rci 0x00 Undo type: Regular undo Begin trans Last buffer split: No Temp Object: No Tablespace Undo: No rdba: 0x00000000Ext idx: 0 flg2: 0 *—————————– uba: 0x00c242fa.41f9.37 ctl max scn: 0x0000.789b7668 prv tx scn: 0x0000.789bb8d7 txn start scn: scn: 0x0000.789e783e logon user: 88 prev brb: 12731116 prev bcl: 0 *—————————– * Rec #0x2 slt: 0x0d objn: 110769(0x0001b0b1) objd: 110769 tblspc: 6(0x00000006) * Layer: 11 (Row) opc: 1 rci 0x00 Undo type: Regular undo User Undo Applied Last buffer split: No Temp Object: No Tablespace Undo: No rdba: 0x00000000 *—————————– KDO undo record: KTB Redo op: 0x04 ver: 0x01 compat bit: 4 (post-11) padding: 1 op: L itl: xid: 0x0012.01c.00322281 uba: 0x0102c5f0.3fa9.0a flg: C— lkc: 0 scn: 0x0000.789ca3f4 KDO Op code: LKR row dependencies Disabled xtype: XA flags: 0x00000000 bdba: 0x038180fc hdba: 0x018d64e2 itli: 1 ispac: 0 maxfr: 4858 tabn: 0 slot: 14 to: 0 *—————————– * Rec #0x3 slt: 0x0d objn: 110769(0x0001b0b1) objd: 110769 tblspc: 6(0x00000006) * Layer: 11 (Row) opc: 1 rci 0x02 Undo type: Regular undo User Undo Applied Last buffer split: No Temp Object: No Tablespace Undo: No rdba: 0x00000000 *—————————– KDO undo record: KTB Redo op: 0x02 ver: 0x01 compat bit: 4 (post-11) padding: 1 op: C uba: 0x00c242fb.41f9.02 KDO Op code: URP row dependencies Disabled xtype: XA flags: 0x00000000 bdba: 0x038180fc hdba: 0x018d64e2 itli: 1 ispac: 0 maxfr: 4858 tabn: 0 slot: 14(0xe) flag: 0x2c lock: 1 ckix: 0 ncol: 9 nnew: 6 size: 0 col 1: [ 7] 78 71 04 1d 13 01 01 col 2: [ 2] c1 13 col 3: [ 1] 80 col 4: [16] 10 e5 00 2e 10 d1 10 d0 10 d7 10 e3 10 db 10 d8 col 5: [174] 10 d0 10 ed 10 d0 10 e0 10 d8 10 e1 00 20 10 d0 00 2e 10 e0 00 2e 00 20 10 de 10 e0 10 dd 10 d9 10 e3 10 e0 10 d0 10 e2 10 e3 10 e0 10 d8 10 e1 00 20 10 e1 10 d0 10 d2 10 d0 10 db 10 dd 10 eb 10 d8 10 d4 10 d1 10 dd 00 20 10 dc 10 d0 10 ec 10 d8 10 da 10 d8 10 e1 00 20 10 e3 10 e4 10 e0 10 dd 10 e1 00 20 10 d2 10 d0 10 db 10 dd 10 db 10 eb 10 d8 10 d4 10 d1 10 d4 10 da 10 e1 00 20 10 d1 10 d0 10 e2 10 dd 10 dc 00 20 10 d2 10 d8 10 dd 10 e0 10 d2 10 d8 00 20 10 de 10 d4 10 e0 10 d0 10 dc 10 d8 10 eb 10 d4 10 e1 00 2e col 6: [36] 00 54 00 01 04 0c 00 00 00 02 00 00 00 01 00 00 09 07 b0 63 00 10 09 00 00 00 00 00 00 00 00 00 00 00 00 00 *—————————– * Rec #0x4 slt: 0x0d objn: 89834(0x00015eea) objd: 93214 tblspc: 6(0x00000006) * Layer: 11 (Row) opc: 1 rci 0x03 Undo type: Regular undo User Undo Applied Last buffer split: No Temp Object: No Tablespace Undo: No rdba: 0x00000000 *—————————– KDO undo record: KTB Redo op: 0x04 ver: 0x01 compat bit: 4 (post-11) padding: 1 op: L itl: xid: 0x000c.017.000d65d6 uba: 0x0103df2c.22a5.20 flg: C— lkc: 0 scn: 0x0000.789c4694 KDO Op code: LKR row dependencies Disabled xtype: XA flags: 0x00000000 bdba: 0x03833994 hdba: 0x0181f832 itli: 1 ispac: 0 maxfr: 4858 tabn: 0 slot: 7 to: 0 *—————————– * Rec #0x5 slt: 0x0d objn: 89834(0x00015eea) objd: 93214 tblspc: 6(0x00000006) * Layer: 11 (Row) opc: 1 rci 0x04 Undo type: Regular undo Last buffer split: No Temp Object: No Tablespace Undo: No rdba: 0x00000000 *—————————– KDO undo record: KTB Redo op: 0x02 ver: 0x01 compat bit: 4 (post-11) padding: 1 op: C uba: 0x00c242fb.41f9.04 KDO Op code: LMN row dependencies Disabled xtype: XA flags: 0x00000000 bdba: 0x03833994 hdba: 0x0181f832 itli: 1 ispac: 0 maxfr: 4858 *—————————– * Rec #0x6 slt: 0x0d objn: 89703(0x00015e67) objd: 92020 tblspc: 6(0x00000006) * Layer: 11 (Row) opc: 1 rci 0x05 Undo type: Regular undo User Undo Applied Last buffer split: No Temp Object: No Tablespace Undo: No rdba: 0x00000000 *—————————– KDO undo record: irb points to last UNDO RECORD in UNDO block. rci points to previous UNDO RECORD. if rci=0, it’s the first UNDO RECORD. Recovery operation starts from irb and chain is followed by rci until rci is zero. The transaction starts recovery from UNDO RECORD of 0x5. 4. Reading UNDO Records: * Rec #0x5 slt: 0x0d objn: 89834(0x00015eea) objd: 93214 tblspc: 6(0x00000006) * Layer: 11 (Row) opc: 1 rci 0x04 …. * Rec #0x4 slt: 0x0d objn: 89834(0x00015eea) objd: 93214 tblspc: 6(0x00000006) * Layer: 11 (Row) opc: 1 rci 0x03 …. * Rec #0x3 slt: 0x0d objn: 110769(0x0001b0b1) objd: 110769 tblspc: 6(0x00000006) * Layer: 11 (Row) opc: 1 rci 0x02 … * Rec #0x2 slt: 0x0d objn: 110769(0x0001b0b1) objd: 110769 tblspc: 6(0x00000006) * Layer: 11 (Row) opc: 1 rci 0x00 … objn means object id. 5. Find these objects The following objects need recovery: select * from dba_objects where object_id in (89834,110769); ……………………………………………………….. This problem is Oracle Bug:9857702: ..... Affects: Product (Component) Oracle Server (Rdbms) Range of versions believed to be affected Versions >= 11.1 but BELOW 12.1 Versions confirmed as being affected •11.2.0.1 •11.1.0.7 Platforms affected Generic (all / most platforms affected) Fixed: This issue is fixed in •12.1 (Future Release) •11.2.0.2 (Server Patch Set) •11.1.0.7.8 Patch Set Update •11.1.0.7 Patch 40 on Windows Platforms ..... 6. Workaround: Recreate objects that need recovery. Or drop them Smile
2、尝试设置 FAST_START_PARALLEL_ROLLBACK=HIGH 看是否能解决
3、尝试设置10513 LEVEL 2 事件,暂时屏蔽回滚事务
空闲时间用Flutter写的安卓APP 金庸武侠小说集离线版 ,Google Play上架中,APK版本直接下载:
Copyright © 2019 · Genesis Framework · WordPress · Log in