Common error recovery methods for Oracle Database (top)

Lab Environment: Linux6.4 + Oracle 11g

For readers: Oracle Development and maintenance personnel

This article presents a demo from a Scott user with Oracle:

First logical backup exports Scott's object data

$ exp Scott/tiger file= '/u01/app/backup/scott.dmp ' log= '/u01/app/backup/scott.log ' Owner=scott;

1. Incorrect operation drop the EMP table

With table-level flashback recovery, you can recover as long as the Recycle Bin is available.

sql> drop table emp;

Table dropped.

Flash Back Recovery

Sql> Flashback table EMP to before drop;

Flashback complete.

Back to the table if there are previous primary keys and indexes, the corresponding name will become a string of characters, although not affect the use, but in order to standardize management, it is recommended to rename them to the previous name

Rename Index name:

sql> ALTER INDEX "bin$esbzggejpkfgu58jqmdotq==$1" Rename to Pk_emp;

Index Altered

Rename the primary KEY constraint name:

sql> ALTER TABLE EMP rename constraint "bin$esbzggeipkfgu58jqmdotq==$1" to pk_emp;

Table Altered

If the database already exists with the same name when you flash back to the table, you can specify a new table name on flashback:

Sql> Flashback table EMP to before drop rename to Emp1;

Flashback complete.

2. misoperation Delete the EMP table

Use the Flashback query results to recover as long as the undo table space is still in error before the operation time data.

sql> Delete from EMP;

Rows deleted.

Sql> commit;

Commit complete.

Sql> select * from EMP;

No rows selected

Sql> SELECT * from emp as of timestamp to_timestamp (' 2015-03-13 15:00:00 ', ' yyyy-mm-dd hh24:mi:ss ');

EMPNO ename JOB MGR hiredate SAL COMM DEPTNO

---------- ---------- --------- ---------- ------------ ---------- ---------- ----------

7369 SMITH Clerk 7902 17-dec-80 800 20

7499 ALLEN salesman 7698 20-feb-81 1600 300 30

7521 WARD salesman 7698 22-feb-81 1250 500 30

7566 JONES MANAGER 7839 02-apr-81 2975 20

7654 MARTIN salesman 7698 28-sep-81 1250 1400 30

7698 BLAKE MANAGER 7839 01-may-81 2850 30

7782 CLARK MANAGER 7839 09-jun-81 2450 10

7788 SCOTT ANALYST 7566 19-apr-87 3000 20

7839 KING President 17-nov-81 5000 10

7844 TURNER salesman 7698 08-sep-81 1500 0 30

7876 ADAMS Clerk 7788 23-may-87 1100 20

7900 JAMES Clerk 7698 03-dec-81 950 30

7902 FORD ANALYST 7566 03-dec-81 3000 20

7934 MILLER Clerk 7782 23-jan-82 1300 10

Rows selected.

After you have determined that the query results are data to be recovered, you can insert them directly into the EMP table.

sql> INSERT INTO EMP SELECT * from emp as of timestamp to_timestamp (' 2015-03-13 15:00:00 ', ' yyyy-mm-dd hh24:mi:ss ');

Rows created.

Sql> commit;

Commit complete.

3. Wrong operation delete part of the EMP table record

With the flashback query result recovery, as long as the undo table space in the wrong operation before the time of the data, as in the second scenario, just more conditional.

sql> Delete from emp where Sal <2000;

8 rows deleted.

Sql> commit;

Commit complete.

Sql> select * from EMP;

EMPNO ename JOB MGR hiredate SAL COMM DEPTNO

---------- ---------- --------- ---------- ------------ ---------- ---------- ----------

7566 JONES MANAGER 7839 02-apr-81 2975 20

7698 BLAKE MANAGER 7839 01-may-81 2850 30

7782 CLARK MANAGER 7839 09-jun-81 2450 10

7788 SCOTT ANALYST 7566 19-apr-87 3000 20

7839 KING President 17-nov-81 5000 10

7902 FORD ANALYST 7566 03-dec-81 3000 20

6 rows selected.

Sql> SELECT * from emp as of timestamp to_timestamp (' 2015-03-13 15:00:00 ', ' yyyy-mm-dd hh24:mi:ss ') where Sal <2000;

EMPNO ename JOB MGR hiredate SAL COMM DEPTNO

---------- ---------- --------- ---------- ------------ ---------- ---------- ----------

7369 SMITH Clerk 7902 17-dec-80 800 20

7499 ALLEN salesman 7698 20-feb-81 1600 300 30

7521 WARD salesman 7698 22-feb-81 1250 500 30

7654 MARTIN salesman 7698 28-sep-81 1250 1400 30

7844 TURNER salesman 7698 08-sep-81 1500 0 30

7876 ADAMS Clerk 7788 23-may-87 1100 20

7900 JAMES Clerk 7698 03-dec-81 950 30

7934 MILLER Clerk 7782 23-jan-82 1300 10

8 rows selected.

After you have determined that the query results are data to be recovered, you can insert them directly into the EMP table.

sql> INSERT INTO EMP SELECT * from emp as of timestamp to_timestamp (' 2015-03-13 15:00:00 ', ' yyyy-mm-dd hh24:mi:ss ') wher E Sal <2000;

8 rows created.

Sql> commit;

Commit complete.

4. Wrong operation truncate the EMP meter

sql> TRUNCATE TABLE emp;

Table truncated.

Now that the table definition has changed, we can't find the table's historical data.

Sql> SELECT * from emp as of timestamp to_timestamp (' 2015-03-13 15:00:00 ', ' yyyy-mm-dd hh24:mi:ss ');

SELECT * from emp as of timestamp to_timestamp (' 2015-03-13 15:00:00 ', ' yyyy-mm-dd hh24:mi:ss ')

*

ERROR at line 1:

ora-01466:unable to read data-table definition had changed

It can only be recovered from previous backups and, of course, only revert to the state when Exp was exported.

$ imp scott/tiger file= '/u01/app/backup/scott.dmp ' log= '/u01/app/backup/restore_emp.log ' tables=emp ignore=y

Import:release 11.2.0.4.0-production on Fri Mar 13 15:29:35 2015

Copyright (c) 1982, Oracle and/or its affiliates. All rights reserved.

Connected to:oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production

With the partitioning, Automatic Storage Management, OLAP, Data Mining

and Real Application Testing options

Export file created by export:v11.02.00 via conventional path

Import done in ZHS16GBK character set and Al16utf16 NCHAR character set

. Importing Scott ' s objects into SCOTT

. Importing Scott ' s objects into SCOTT

. . Importing table "EMP" in rows imported

About-to-enable constraints ...

Import terminated successfully without warnings.

Verify that the data has been successfully restored

Sql> Select COUNT (1) from EMP;

COUNT (1)

----------

14

5. Error operation with PURGE option drop table

sql> drop table emp Purge;

Table dropped.

Because the drop of the purge option does not enter the Recycle Bin, the Flashback table is no longer available.

Sql> Flashback table EMP to before drop;

Flashback table EMP to before drop

*

ERROR at line 1:

Ora-38305:object not in RECYCLE BIN

This can only be recovered from the previous backup, as in the fourth case recovery method, no longer repeat.

Common error recovery methods for Oracle Database (top)