暂无图片
暂无图片
8
暂无图片
暂无图片
暂无图片
首页 / 运用物理备库恢复主库truncate表的数据

运用物理备库恢复主库truncate表的数据

原创 Leo 2023-01-03
673

文档课题：运用物理备库恢复主库truncate表的数据.

1、备库准备

--备库开启flashback database.

SQL> alter database recover managed standby database cancel;

 

Database altered.

 

SQL> select flashback_on from v$database;

 

FLASHBACK_ON

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

NO

 

SQL> alter database flashback on;

 

Database altered.

 

SQL> select flashback_on from v$database;

 

FLASHBACK_ON

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

YES

SQL> alter database recover managed standby database using current logfile disconnect from session;

 

Database altered.

2、模拟异常

2.1、日志同步应用

--查同步状态，备库实时应用主库归档。

SQL> set linesize 300

SQL> col db_unique_name for a10

SQL> col database_role for a20

SQL> col recovery_mode for a20

SQL> col synchronization_status for a10

SQL> col gap_status for a10

SQL> col destination for a30

SQL> select destination,db_unique_name,type,status,database_mode,recovery_mode,archived_thread#,archived_seq#,applied_seq#, synchronization_status,gap_status from v$archive_dest_status where status <> 'DEFERRED' and status <> 'INACTIVE'

 

DESTINATION DB_UNIQUE_ TYPE  STATUS    DATABASE_MODE   RECOVERY_MODE    ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_SEQ# SYNCHRONIZATION_STAT GAP_STATUS

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

/u01/app/oracle/archivelog     orcl150    LOCAL          VALID     OPEN            IDLE                                     1            12            0 CHECK CONFIGURATION

ORCL151                  orcl151    PHYSICAL       VALID     OPEN_READ-ONLY  MANAGED REAL TIME APPLY            1            12           10 CHECK CONFIGURATION  NO GAP

SQL> select current_scn from v$database;

 

CURRENT_SCN

-----------

1009788

2.2、主库误操作

--主库发生误操作，将表scott.emp进行truncate.

SQL> select * from scott.emp;

 

     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

 

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

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

      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

 

14 rows selected.

 

SQL> truncate table scott.emp;

 

Table truncated.

 

SQL> select count(*) from scott.emp;

 

  COUNT(*)

----------

         0

 

SQL> select current_scn from v$database;

 

CURRENT_SCN

-----------

1010267

2.3、误操作时间

--通过logminer搜索一定范围内的archivelog,确定误操作的准确scn。

SQL> exec dbms_logmnr.start_logmnr(startscn=>1009788,endscn=>1010267,options=>DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG + DBMS_LOGMNR.CONTINUOUS_MINE);

 

PL/SQL procedure successfully completed.

SQL> col sql_redo for a35

SQL> select scn,sql_redo,to_char(timestamp,'yyyy-mm-dd hh24:mi:ss') from v$logmnr_contents where table_name='EMP';

 

       SCN SQL_REDO                            TO_CHAR(TIMESTAMP,'

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

   1010231 truncate table scott.emp;           2023-01-03 21:23:17

说明：在2023-01-03 21:23:17进行的truncate操作，那么需要闪回到此之前的时间.

SQL> select to_char(scn_to_timestamp(1009788),'yyyy-mm-dd hh24:mi:ss') scn from dual;

 

SCN

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

2023-01-03 21:16:27

3、恢复单表

3.1、闪回删除表之前状态

--备库执行flashback database。

SQL> flashback database to scn 1009788;

 

Flashback complete.

 

SQL> alter database open read only;

 

Database altered.

 

SQL> select count(*) from scott.emp;

 

  COUNT(*)

----------

        14

3.2、数据泵恢复

说明：先将物理备库切换为快照备库，然后通过数据泵导出导入的方法进行数据恢复.

3.2.1、物理备库转快照备库

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

 

Total System Global Area 1586708480 bytes

Fixed Size                  2253624 bytes

Variable Size             989859016 bytes

Database Buffers          587202560 bytes

Redo Buffers                7393280 bytes

Database mounted.

SQL> alter database convert to snapshot standby;

 

Database altered.

 

SQL> alter database open;

 

Database altered.

 

SQL> select count(*) from scott.emp;

 

  COUNT(*)

----------

        14

SQL> select open_mode,database_role from v$database;

 

OPEN_MODE            DATABASE_ROLE

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

READ WRITE           SNAPSHOT STANDBY

3.2.2、导出

--使用数据泵将数据恢复到主库上。备库创建目录。

SQL> create directory empdp_dir as '/home/oracle/dump';

 

Directory created.

[oracle@leo-oel151 ~]$ expdp \" / as sysdba \" directory=empdp_dir dumpfile=emp.dmp logfile=table.log tables=scott.emp;

 

Export: Release 11.2.0.4.0 - Production on Tue Jan 3 22:51:12 2023

 

Copyright (c) 1982, 2011, 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, OLAP, Data Mining and Real Application Testing options

Starting "SYS"."SYS_EXPORT_TABLE_01":  "/******** AS SYSDBA" directory=empdp_dir dumpfile=emp.dmp logfile=table.log tables=scott.emp

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "SCOTT"."EMP"                               8.562 KB      14 rows

Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYS.SYS_EXPORT_TABLE_01 is:

  /home/oracle/dump/emp.dmp

Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Tue Jan 3 22:51:20 2023 elapsed 0 00:00:06

3.2.3、导入

--备库传输dmp文件到主库

[oracle@leo-oel151 dump]$ scp emp.dmp oracle@192.168.133.150:/home/oracle/dump

The authenticity of host '192.168.133.150 (192.168.133.150)' can't be established.

ECDSA key fingerprint is SHA256:R8UDEjnMICZcREFDILpbSXRO2tpPOqPv+HlYq2DtQ+o.

ECDSA key fingerprint is MD5:d5:3d:57:6a:ef:20:8d:af:7b:a0:f3:ea:bf:f3:c7:f1.

Are you sure you want to continue connecting (yes/no)? yes

Warning: Permanently added '192.168.133.150' (ECDSA) to the list of known hosts.

oracle@192.168.133.150's password:

emp.dmp                                                                                                                                                               100%  136KB  38.9MB/s   00:00   

--主库建导入目录

SQL> create directory impdp_dir as '/home/oracle/dump';

 

Directory created.

--主库导入数据，注意使用table_exists_action=replace，因为原表scott.emp结构存在.

[oracle@leo-oel150 ~]$ impdp \" / as sysdba \" directory=impdp_dir dumpfile=emp.dmp logfile=table.log tables=scott.emp table_exists_action=replace

 

Import: Release 11.2.0.4.0 - Production on Tue Jan 3 23:04:32 2023

 

Copyright (c) 1982, 2011, 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, OLAP, Data Mining and Real Application Testing options

Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded

Starting "SYS"."SYS_IMPORT_TABLE_01":  "/******** AS SYSDBA" directory=impdp_dir dumpfile=emp.dmp logfile=table.log tables=scott.emp table_exists_action=replace

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "SCOTT"."EMP"                               8.562 KB      14 rows

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Tue Jan 3 23:04:34 2023 elapsed 0 00:00:02

--主库验证

SQL> select count(*) from scott.emp

 

  COUNT(*)

----------

        14

3.2.4、快照备库转物理备库

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

 

Total System Global Area 1586708480 bytes

Fixed Size                  2253624 bytes

Variable Size             989859016 bytes

Database Buffers          587202560 bytes

Redo Buffers                7393280 bytes

Database mounted.

SQL> alter database convert to physical standby;

 

Database altered.

 

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01507: database not mounted

 

SQL> shutdown immediate;

ORA-01507: database not mounted

 

 

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

 

Total System Global Area 1586708480 bytes

Fixed Size                  2253624 bytes

Variable Size             989859016 bytes

Database Buffers          587202560 bytes

Redo Buffers                7393280 bytes

Database mounted.

Database opened.

SQL> select open_mode,database_role from v$database;

 

OPEN_MODE            DATABASE_ROLE

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

READ ONLY WITH APPLY PHYSICAL STANDBY

SQL> alter database recover managed standby database disconnect from session;

 

Database altered.

 

SQL> select process,status from v$managed_standby;

 

PROCESS   STATUS

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

ARCH      CONNECTED

ARCH      CONNECTED

ARCH      CLOSING

ARCH      CONNECTED

RFS       IDLE

RFS       IDLE

RFS       IDLE

MRP0      WAIT_FOR_LOG

 

8 rows selected.

 

参考网址：http://t.zoukankan.com/chinesern-p-8687107.html

truncate表的恢复物理备库快照备库impdp expdp 数据泵闪回查询
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

评论