Home Database Mysql Tutorial Oracle丢失归档日志文件的数据库恢复方法

Oracle丢失归档日志文件的数据库恢复方法

Jun 07, 2016 pm 05:07 PM
oracle database

使用案例描述:ORDTAB表空间的一个数据文件ordtab03.dbf毁坏,其包含很多ORDERS表的分区,数据文件热备于July 4, 2004,July 4

丢失归档日志文件的数据库恢复方法,从一个不能正常打开的数据库(由于一个/多个数据库文件与其他文件不一致)中提取数据。场景:一个磁盘损坏了并且丢失了一个数据库文件。从一周前的热备转储数据文件,不幸的是丢失了几个归档日志文件。但是有问题的数据文件包含了最重要的表,如何能够挽救数据呢?

从一个不能正常打开的数据库(由于一个/多个数据库文件与其他文件不一致)中提取数据。场景:一个磁盘损坏了并且丢失了一个数据库文件。从一周前的热备转储数据文件,不幸的是丢失了几个归档日志文件。但是有问题的数据文件包含了最重要的表,如何能够挽救数据呢?

每个DBA都知道这是有问题的,一定会丢失数据,因为某些事务丢失了,问题是会丢失多少数据?Oracle使用硬线路位置并且由于存在完整性约束问题,因此不允许正常打开数据。但是如果使用非常规的方法让Oracle删除其硬线路属性,那么应该能够提取尽可能多的数据。而通常这会比损失全部数据要好很多。

详细过程通常如果仅仅丢失了堆表的索引,或者某些能够很容易重建的数据,那么最好的方法应该是删除表空间并重建这些对象然后重新输入。但是如果丢失的数据文件包含了重要数据并且很难恢复,而且只有前一次的备份却又丢失了某些归档日志,那么用户可能希望能够尽可能多的从有问题的表空间恢复数据并且删除和重建表空间。

主要的步骤如下:

1. 对当前拥有的数据进行一个冷备;

2. 转储丢失的数据库文件备份并应用可以应用的日志;

3. 设置未文档化的初始化参数,其允许你在当前状态打开数据库;

4. 执行exp并提取全部可以从有问题的表空间提取的数据;

5. 从先前的冷备转储数据库;

6. 使毁坏的数据文件offline;

7. 执行exp并提取第4步没有提取的额外数据;

8. 在一次从冷备转储;

9. 删除有问题的表空间;

10. 重建有问题的表空间;

11. 使用第四步和第七步提取的数据重建数据;

使用案例描述:ORDTAB表空间的一个数据文件ordtab03.dbf毁坏,其包含很多ORDERS表的分区,数据文件热备于July 4, 2004,July 4—至今的某些归档日志丢失。

第一步:备份数据库第一步的任务是冷备当前拥有的任何数据文件,在线重做日志,和控制文件。如果丢失了一个/多个数据文件但是数据库仍然是open的,那么对每个剩余的数据文件进行热备并确保备份期间/之后的归档被安全保存。

创建备份后,在关闭数据库之前,备份一下控制文件:ALTER DATABASE BACKUP CONTROLFILE TO TRACE RESETLOGS;然后打开备份的控制文件,删除第一个#之上的所有行,并删除“RECOVER DATABASE…”到文件结尾的全部。

第二步:转储丢失的数据库文件备份并应用日志;这一步应该转储备份,并应用日志到直到无法在前向滚动,此时如果尝试正常打开数据库,将会得到ORA-01589: must use RESETLOGS or NORESETLOGS option for database open错误。

如果尝试执行ALTER DATABASE OPEN RESETLOGS,将会得到ORA-01195错误:ORA-01195: online backup of file %s needs more recovery to be consistent.这里是Oracle使用其硬线路的位置。由于转储的数据文件不能恢复到与其他文件一致的位置,所以可能存在中断的数据并且Oracle不允许正常打开数据库。

第三步:设置未文档化的实例参数并打开数据库在初始化参数文件中首先需要将job_queue_processes设置为0,然后设置_allow_resetlogs_corruption=TRUE,更改该参数后,切换到保存新控制文件的目录,第一步创建的位置。然后以SYSDBA连接并运行新的控制文件创建脚本。

此时数据库可以打开了。

SQL> SELECT COUNT(*) FROM OE.orders;

第四步:执行导出并提取数据在这一步可以很容易的看到那些表导出了全部的数据。

第五步:转储备份的数据库这一步,以及下面两步可选。这三步结合在一起允许提取更多的数据,这一步从备份的数据库转储可以高效的撤销任何由于使用_allow_resetlogs_corruption参数造成的毁坏。因此,这一步不会恢复任何丢失的数据文件。

第六步:使毁坏的数据文件offline ALTER DATABASE DATAFILE ’/u07/oradata/PRD/ordtab03.dbf’ OFFLINE;这一步得到数据库的完全一致性状态。

第七步:执行导出并提取额外的数据这一步可能能够提取从第四步不能提取的额外数据,如索引中的数据。

第八步 :转储数据库这是最后一次转储数据库,这一步正式回滚数据库到使用隐含参数前那一刻,然后将数据库返回到正常状态,如果从第五步转储以来没有更新任何数据,可以跳过这一步。

第九步:删除有问题的表空间首先需要查看是否有完整性约束限制,使用以下查询:

SELECT CR.constraint_name

FROM dba_constraints CR, dba_constraints CP, dba_tables TP, dba_tables TR

WHERE CR.r_owner = CP.owner

AND CR.r_constraint_name = CP.constraint_name

AND CR.constraint_type = ’R’

AND CP.constraint_type IN (’P’, ’U’)

AND CP.table_name = TP.table_name

AND CP.owner = TP.owner

AND CR.table_name = TR.table_name

AND CR.owner = TR.owner

AND TR.tablespace_name ’ORDTAB’

AND TP.tablespace_name = ’ORDTAB’;

如果有约束,可能需要创建重建脚本。如果使用export dump重建数据,,约束可以从导出文件转储。

DROP TABLESPACE ordtab INCLUDING CONTENTS CASCADE CONSTRAINTS;

第十步:重建表空间

第十一步:重建数据执行导入

linux

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

How to check which table space a table belongs to in Oracle How to check which table space a table belongs to in Oracle Jul 06, 2023 pm 01:31 PM

How to check which table space a table belongs to in Oracle: 1. Use the "SELECT" statement and specify the table name to find the table space to which the specified table belongs; 2. Use the database management tools provided by Oracle to check the table space to which the table belongs. Tools usually provide a graphical interface, making the operation more intuitive and convenient; 3. In SQL*Plus, you can view the table space to which the table belongs by entering the "DESCRIBEyour_table_name;" command.

How to connect to Oracle database using PDO How to connect to Oracle database using PDO Jul 28, 2023 pm 12:48 PM

Overview of how to use PDO to connect to Oracle database: PDO (PHPDataObjects) is an extension library for operating databases in PHP. It provides a unified API to access multiple types of databases. In this article, we will discuss how to use PDO to connect to an Oracle database and perform some common database operations. Step: Install the Oracle database driver extension. Before using PDO to connect to the Oracle database, we need to install the corresponding Oracle

How to retrieve only one piece of duplicate data in oracle How to retrieve only one piece of duplicate data in oracle Jul 06, 2023 am 11:45 AM

Steps for Oracle to fetch only one piece of duplicate data: 1. Use the SELECT statement combined with the GROUP BY and HAVING clauses to find duplicate data; 2. Use ROWID to delete duplicate data to ensure that accurate duplicate data records are deleted, or use "ROW_NUMBER" ()" function to delete duplicate data, which will delete all records except the first record in each set of duplicate data; 3. Use the "select count(*) from" statement to return the number of deleted records to ensure the result.

Implement data import into PHP and Oracle databases Implement data import into PHP and Oracle databases Jul 12, 2023 pm 06:46 PM

Implementing data import into PHP and Oracle databases In web development, using PHP as a server-side scripting language can conveniently operate the database. As a common relational database management system, Oracle database has powerful data storage and processing capabilities. This article will introduce how to use PHP to import data into an Oracle database and give corresponding code examples. First, we need to ensure that PHP and Oracle database have been installed, and that PHP has been configured to

How to use PHP and Oracle database connection pools efficiently How to use PHP and Oracle database connection pools efficiently Jul 12, 2023 am 10:07 AM

How to efficiently use connection pooling in PHP and Oracle databases Introduction: When developing PHP applications, using a database is an essential part. When interacting with Oracle databases, the use of connection pools is crucial to improving application performance and efficiency. This article will introduce how to use Oracle database connection pool efficiently in PHP and provide corresponding code examples. 1. The concept and advantages of connection pooling Connection pooling is a technology for managing database connections. It creates a batch of connections in advance and maintains a

Does oracle database require jdk? Does oracle database require jdk? Jun 05, 2023 pm 05:06 PM

The oracle database requires jdk. The reasons are: 1. When using specific software or functions, other software or libraries included in the JDK are required; 2. Java JDK needs to be installed to run Java programs in the Oracle database; 3. JDK provides Develop and compile Java application functions; 4. Meet Oracle's requirements for Java functions to help implement and implement specific functions.

How to use php to extend PDO to connect to Oracle database How to use php to extend PDO to connect to Oracle database Jul 29, 2023 pm 07:21 PM

How to use PHP to extend PDO to connect to Oracle database Introduction: PHP is a very popular server-side programming language, and Oracle is a commonly used relational database management system. This article will introduce how to use PHP extension PDO (PHPDataObjects) to connect to Oracle database. 1. Install the PDO_OCI extension. To connect to the Oracle database, you first need to install the PDO_OCI extension. Here are the steps to install the PDO_OCI extension: Make sure

How oracle determines whether a table exists in a stored procedure How oracle determines whether a table exists in a stored procedure Jul 06, 2023 pm 01:20 PM

Oracle's steps to determine whether a table exists in a stored procedure: 1. Use the "user_tables`" system table to query the table information under the current user, compare the incoming table name "p_table_name" with the "table_name" field, and if the conditions are met, then "COUNT(*)" will return a value greater than 0; 2. Use the "SET SERVEROUTPUT ON;" statement and the "EXEC`" keyword to execute the stored procedure and pass in the table name to determine whether the table exists.

See all articles