标签：主库 备库 truncate database EXPORT emp SQL ---------- TABLE
文档课题：运用物理备库恢复主库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 [email protected]:/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.
[email protected]'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,
database,
EXPORT,
emp,
SQL,
----------,
TABLE
From： https://blog.51cto.com/u_12991611/5986934