有朋友夜间打电话请求技术支持,数据库表空间被删除,然后使用flashback database 无法正常恢复。通过分析alert日志发现,创建表空间(xifenfei 别名),发现已经存在,就删除了该表空间(其实是业务核心表空间,误删除了,是否是连接错了数据库?) Sat Jul 05 17:10
有朋友夜间打电话请求技术支持,数据库表空间被删除,然后使用flashback database 无法正常恢复。通过分析alert日志发现,创建表空间(xifenfei 别名),发现已经存在,就删除了该表空间(其实是业务核心表空间,误删除了,是否是连接错了数据库?)
Sat Jul 05 17:10:06 2014 create tablespace XIFENFEI datafile 'D:\Oracle\oradata\orcl\HANDBB.DBF' size 50M autoextend on next 50M maxsize 1536M extent management local Sat Jul 05 17:10:06 2014 ORA-1543 signalled during: create tablespace XIFENFEI datafile 'D:\Oracle\oradata\orcl\HANDBB.DBF' size 50M autoextend on next 50M maxsize 1536M extent management local ... Sat Jul 05 17:10:59 2014 drop tablespace XIFENFEI Sat Jul 05 17:10:59 2014 ORA-1549 signalled during: drop tablespace XIFENFEI ... Sat Jul 05 17:11:05 2014 drop tablespace XIFENFEI ORA-1549 signalled during: drop tablespace XIFENFEI ... Sat Jul 05 17:11:24 2014 drop tablespace XIFENFEI including contents Sat Jul 05 17:11:36 2014 Thread 1 advanced to log sequence 186895 (LGWR switch) Current log# 1 seq# 186895 mem# 0: D:\ORACLE\PRODUCT\10.2.0\ORADATA\GZSERVER\REDO01.LOG Sat Jul 05 17:11:36 2014 ARC3: Warning. Log sequence in archive filename wrapped to fix length as indicated by %S in LOG_ARCHIVE_FORMAT. Old log archive with same name might be overwritten. Sat Jul 05 17:11:43 2014 LNS: Standby redo logfile selected for thread 1 sequence 186895 for destination LOG_ARCHIVE_DEST_4 Sat Jul 05 17:11:49 2014 LNS: Standby redo logfile selected for thread 1 sequence 186895 for destination LOG_ARCHIVE_DEST_2 Sat Jul 05 17:12:09 2014 Starting control autobackup Control autobackup written to DISK device handle 'D:\FULLBACK\C-1342406147-20140705-00' Completed: drop tablespace XIFENFEI including contents
通过这里可以发现删除表空间时间点为2014年7月5日17:12:09
闪回数据库到删除之前时间点
Sat Jul 05 18:16:54 2014 Database mounted in Exclusive Mode Completed: ALTER DATABASE MOUNT Sat Jul 05 18:19:23 2014 FLASHBACK DATABASE TO TIMESTAMP TO_TIMESTAMP('2014-07-05 17:09:00','YYYY-MM-DD HH24:MI:SS') Sat Jul 05 18:19:25 2014 Flashback Restore Start Sat Jul 05 18:20:52 2014 --闪回时的控制文件中无表空间XIFENFEI信息(因为已经被删除), --但是由于闪回的system 数据字典里面有相关文件信息,因此数据库在控制文件里面创建相关文件信息 Flashback: created tablespace #6: 'XIFENFEI' in the controlfile. Flashback: created OFFLINE file 'UNNAMED00012' for tablespace #6 in the controlfile. Filename was: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\GZSERVER\XIFENFEI4.DBF' when dropped. File will have to be restored from a backup and recovered. Flashback: created OFFLINE file 'UNNAMED00010' for tablespace #6 in the controlfile. Filename was: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\GZSERVER\XIFENFEI3.DBF' when dropped. File will have to be restored from a backup and recovered. Flashback: created OFFLINE file 'UNNAMED00008' for tablespace #6 in the controlfile. Filename was: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\GZSERVER\XIFENFEI2.DBF' when dropped. File will have to be restored from a backup and recovered. Flashback: created OFFLINE file 'UNNAMED00005' for tablespace #6 in the controlfile. Filename was: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\GZSERVER\XIFENFEI.DBF' when dropped. File will have to be restored from a backup and recovered. Flashback Restore Complete Flashback Media Recovery Start parallel recovery started with 15 processes Flashback Media Recovery Log D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ARC\ARC86891_0766797318.001 Flashback Media Recovery Log D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ARC\ARC86892_0766797318.001 Sat Jul 05 18:21:40 2014 Flashback Media Recovery Log D:\ORACLE\PRODUCT\10.2.0\ORADATA\ARCHIVE\ARC\ARC86893_0766797318.001 Sat Jul 05 18:21:47 2014 WARNING: inbound connection timed out (ORA-3136) Sat Jul 05 18:22:11 2014 Recovery of Online Redo Log: Thread 1 Group 3 Seq 186894 Reading mem 0 Mem# 0: D:\ORACLE\PRODUCT\10.2.0\ORADATA\GZSERVER\REDO03.LOG Sat Jul 05 18:22:39 2014 Incomplete Recovery applied until change 9078991241 Flashback Media Recovery Complete ORA-38795 signalled during: FLASHBACK DATABASE TO TIMESTAMP TO_TIMESTAMP('2014-07-05 17:09:00','YYYY-MM-DD HH24:MI:SS')... Sat Jul 05 18:30:11 2014 ALTER DATABASE OPEN RESETLOGS Sat Jul 05 18:30:11 2014 ORA-1245 signalled during: ALTER DATABASE OPEN RESETLOGS... --重命名相关UNNAMExxxxx文件名到硬盘上被删除表空间文件 Sat Jul 05 18:39:31 2014 alter database rename file 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00005' to 'D:\oracle\product\10.2.0\oradata\gzserver\XIFENFEI.DBF' Sat Jul 05 18:39:31 2014 Completed: alter database rename file 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00005' to 'D:\oracle\product\10.2.0\oradata\gzserver\XIFENFEI.DBF' Sat Jul 05 18:39:47 2014 alter database rename file 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00008' to 'D:\oracle\product\10.2.0\oradata\gzserver\XIFENFEI2.DBF' Sat Jul 05 18:39:47 2014 Completed: alter database rename file 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00008' to 'D:\oracle\product\10.2.0\oradata\gzserver\XIFENFEI2.DBF' Sat Jul 05 18:39:59 2014 alter database rename file 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00010' to 'D:\oracle\product\10.2.0\oradata\gzserver\XIFENFEI3.DBF' Sat Jul 05 18:39:59 2014 Completed: alter database rename file 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00010' to 'D:\oracle\product\10.2.0\oradata\gzserver\XIFENFEI3.DBF' Sat Jul 05 18:40:12 2014 alter database rename file 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00012' to 'D:\oracle\product\10.2.0\oradata\gzserver\XIFENFEI4.DBF' Sat Jul 05 18:40:12 2014 Completed: alter database rename file 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\UNNAMED00012' to 'D:\oracle\product\10.2.0\oradata\gzserver\XIFENFEI4.DBF' Sat Jul 05 18:41:25 2014 ALTER DATABASE OPEN RESETLOGS Sat Jul 05 18:41:25 2014 ORA-1245 signalled during: ALTER DATABASE OPEN RESETLOGS...
到这里,可以看出来,因为数据库整体已经闪回,但是被drop 表空间的四个数据文件未被正常闪回,因此该四个文件的scn可能异常,通过数据库恢复检查脚本(Oracle Database Recovery Check)检查结果如下
这里很明显控制文件中的scn信息混乱不做过多参考,数据文件头信息看到只有ts# 6中的四个文件(就是被删除的表空间文件)scn过大,其他文件scn都处于正常状态(处于干净状态),到这里很明显,数据库闪回成功,但是被drop tablespace的数据文件未被闪回,因此该故障可以通过bbed修改四个文件头信息和其他文件相同即可使得数据库恢复正常
温馨提示:数据库操作需要慎重,备份重于一切
- 记录一次ORA-600 3004 恢复过程和处理思路
- 数据文件的三个创建SCN一点点探讨
- 记录一次ORA-00600[kdxlin:psno out of range]/ORA-00600[3020]/ORA-00600[4000]/ORA-00600[4193]的数据库恢复
- 使用flashback database找回被误删除表空间
- 记录一次ORA-00316 ORA-00312 redo异常恢复
- ORA-00600[kcrf_resilver_log_1]异常恢复
- 又一起存储故障导致ORA-00333 ORA-00312恢复
- Oracle安全警示录:加错裸设备导致redo异常
原文地址:误drop tablespace后使用flashback database闪回异常处理, 感谢原作者分享。

InnoDBBufferPool reduces disk I/O by caching data and indexing pages, improving database performance. Its working principle includes: 1. Data reading: Read data from BufferPool; 2. Data writing: After modifying the data, write to BufferPool and refresh it to disk regularly; 3. Cache management: Use the LRU algorithm to manage cache pages; 4. Reading mechanism: Load adjacent data pages in advance. By sizing the BufferPool and using multiple instances, database performance can be optimized.

Compared with other programming languages, MySQL is mainly used to store and manage data, while other languages such as Python, Java, and C are used for logical processing and application development. MySQL is known for its high performance, scalability and cross-platform support, suitable for data management needs, while other languages have advantages in their respective fields such as data analytics, enterprise applications, and system programming.

MySQL is worth learning because it is a powerful open source database management system suitable for data storage, management and analysis. 1) MySQL is a relational database that uses SQL to operate data and is suitable for structured data management. 2) The SQL language is the key to interacting with MySQL and supports CRUD operations. 3) The working principle of MySQL includes client/server architecture, storage engine and query optimizer. 4) Basic usage includes creating databases and tables, and advanced usage involves joining tables using JOIN. 5) Common errors include syntax errors and permission issues, and debugging skills include checking syntax and using EXPLAIN commands. 6) Performance optimization involves the use of indexes, optimization of SQL statements and regular maintenance of databases.

MySQL is suitable for beginners to learn database skills. 1. Install MySQL server and client tools. 2. Understand basic SQL queries, such as SELECT. 3. Master data operations: create tables, insert, update, and delete data. 4. Learn advanced skills: subquery and window functions. 5. Debugging and optimization: Check syntax, use indexes, avoid SELECT*, and use LIMIT.

MySQL efficiently manages structured data through table structure and SQL query, and implements inter-table relationships through foreign keys. 1. Define the data format and type when creating a table. 2. Use foreign keys to establish relationships between tables. 3. Improve performance through indexing and query optimization. 4. Regularly backup and monitor databases to ensure data security and performance optimization.

MySQL is an open source relational database management system that is widely used in Web development. Its key features include: 1. Supports multiple storage engines, such as InnoDB and MyISAM, suitable for different scenarios; 2. Provides master-slave replication functions to facilitate load balancing and data backup; 3. Improve query efficiency through query optimization and index use.

SQL is used to interact with MySQL database to realize data addition, deletion, modification, inspection and database design. 1) SQL performs data operations through SELECT, INSERT, UPDATE, DELETE statements; 2) Use CREATE, ALTER, DROP statements for database design and management; 3) Complex queries and data analysis are implemented through SQL to improve business decision-making efficiency.

The basic operations of MySQL include creating databases, tables, and using SQL to perform CRUD operations on data. 1. Create a database: CREATEDATABASEmy_first_db; 2. Create a table: CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY, titleVARCHAR(100)NOTNULL, authorVARCHAR(100)NOTNULL, published_yearINT); 3. Insert data: INSERTINTObooks(title, author, published_year)VA


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Article

Hot Tools

Atom editor mac version download
The most popular open source editor

SublimeText3 Linux new version
SublimeText3 Linux latest version

SublimeText3 Mac version
God-level code editing software (SublimeText3)

SublimeText3 English version
Recommended: Win version, supports code prompts!

SAP NetWeaver Server Adapter for Eclipse
Integrate Eclipse with SAP NetWeaver application server.