impdp TABLE_EXISTS_ACTION=REPLACE覆盖后怎么恢复?

无expdp备份,无rman备份,有归档,采用dbms_logmnr恢复。
SQL> set line 132
SQL> set wrap off
SQL> set line 160

SQL> create user jyc  identified by jyc default tablespace users;

User created.

SQL> grant dba to jyc;

Grant succeeded.

SQL> select * from v$Log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ -------------------
         1          1         31   52428800        512          1 NO  CURRENT                1819929 2020-12-25 19:10:46   2.8147E+14
         2          1         29   52428800        512          1 YES INACTIVE               1799340 2020-12-25 16:49:24      1799397 2020-12-25 16:49:27
         3          1         30   52428800        512          1 YES INACTIVE               1799397 2020-12-25 16:49:27      1819929 2020-12-25 19:10:46

SQL> conn jyc/jyc
Connected.
SQL> create table b as select * from user_objects;

Table created.

SQL> create table a as select * from dba_users;

Table created.

SQL> select count(*) from a;

  COUNT(*)
----------
        31

SQL> select count(*) from b;

  COUNT(*)
----------
         1

SQL> set time on
19:16:41 SQL> 
19:16:47 SQL> 
19:16:47 SQL> 
19:17:14 SQL> !
[oracle@saperp ~]$ expdp jyc/jyc dumpfile=JYC-19.dmp logfile=19.log schemas=JYC DIRECTORY=dmp

Export: Release 11.2.0.4.0 - Production on Fri Dec 25 19:17:37 2020

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
FLASHBACK automatically enabled to preserve database integrity.
Starting "JYC"."SYS_EXPORT_SCHEMA_01":  jyc/******** dumpfile=JYC-19.dmp logfile=19.log schemas=JYC DIRECTORY=dmp 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
. . exported "JYC"."A"                                   14.32 KB      31 rows
. . exported "JYC"."B"                                   10.36 KB       1 rows
Master table "JYC"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for JYC.SYS_EXPORT_SCHEMA_01 is:
  /backup/JYC-19.dmp
Job "JYC"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri Dec 25 19:17:48 2020 elapsed 0 00:00:10

[oracle@saperp ~]$ exit
exit

19:17:53 SQL> show user;
USER is "JYC"
19:17:57 SQL> insert into a select * from a;

31 rows created.

19:18:12 SQL> insert into a select * from a;

62 rows created.

19:18:14 SQL> insert into a select * from a;

124 rows created.

19:18:15 SQL> insert into a select * from a;

248 rows created.

19:18:16 SQL> commit;

Commit complete.

19:18:19 SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ -------------------
         1          1         31   52428800        512          1 NO  CURRENT                1819929 2020-12-25 19:10:46   2.8147E+14
         2          1         29   52428800        512          1 YES INACTIVE               1799340 2020-12-25 16:49:24      1799397 2020-12-25 16:49:27
         3          1         30   52428800        512          1 YES INACTIVE               1799397 2020-12-25 16:49:27      1819929 2020-12-25 19:10:46

19:18:25 SQL> insert into b select * from b;

1 row created.

19:18:45 SQL> /

2 rows created.

19:18:48 SQL> /

4 rows created.

19:18:48 SQL> /

8 rows created.

19:18:49 SQL> /

16 rows created.

19:18:49 SQL> /

32 rows created.

19:18:51 SQL> commit;

Commit complete.

19:18:54 SQL> select count(*) from b;

  COUNT(*)
----------
        64

19:18:58 SQL> 
19:19:04 SQL> 
19:19:05 SQL> select count(*) from a;

  COUNT(*)
----------
       496

19:19:09 SQL> !
[oracle@saperp ~]$ impdp jyc/jyc dumpfile=JYC-19.dmp logfile=19-imp.log schemas=JYC DIRECTORY=dmp TABLE_EXISTS_ACTION=REPLACE

Import: Release 11.2.0.4.0 - Production on Fri Dec 25 19:19:45 2020

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 "JYC"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "JYC"."SYS_IMPORT_SCHEMA_01":  jyc/******** dumpfile=JYC-19.dmp logfile=19-imp.log schemas=JYC DIRECTORY=dmp TABLE_EXISTS_ACTION=REPLACE 
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"JYC" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "JYC"."A"                                   14.32 KB      31 rows
. . imported "JYC"."B"                                   10.36 KB       1 rows
Job "JYC"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at Fri Dec 25 19:19:46 2020 elapsed 0 00:00:01

[oracle@saperp ~]$ exit
exit

19:19:50 SQL> show user;
USER is "JYC"
19:19:54 SQL> select count(*) from b;

  COUNT(*)
----------
         1

19:20:08 SQL> select count(*) from a;

  COUNT(*)
----------
        31

19:20:10 SQL> conn /as sysdba
Connected.
19:20:48 SQL> show user;
USER is "SYS"
19:20:50 SQL> select * from v$Log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ -------------------
         1          1         31   52428800        512          1 NO  CURRENT                1819929 2020-12-25 19:10:46   2.8147E+14
         2          1         29   52428800        512          1 YES INACTIVE               1799340 2020-12-25 16:49:24      1799397 2020-12-25 16:49:27
         3          1         30   52428800        512          1 YES INACTIVE               1799397 2020-12-25 16:49:27      1819929 2020-12-25 19:10:46

19:20:59 SQL> create user test identified by test default tablespace users;

User created.

19:21:16 SQL> grant dba to test;

Grant succeeded.

19:21:21 SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@saperp ~]$ impdp test/test dumpfile=JYC-19.dmp logfile=19-remap.log remap_schema=JYC:TEST DIRECTORY=dmp

Import: Release 11.2.0.4.0 - Production on Fri Dec 25 19:21:49 2020

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 "TEST"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "TEST"."SYS_IMPORT_FULL_01":  test/******** dumpfile=JYC-19.dmp logfile=19-remap.log remap_schema=JYC:TEST DIRECTORY=dmp 
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"TEST" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."A"                                  14.32 KB      31 rows
. . imported "TEST"."B"                                  10.36 KB       1 rows
Job "TEST"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Fri Dec 25 19:21:50 2020 elapsed 0 00:00:00

[oracle@saperp ~]$ exit
logout
[root@saperp ~]# su - oracle
Last login: Fri Dec 25 19:10:21 CST 2020 on pts/0
[oracle@saperp ~]$ export ORACLE_SID=orcl
[oracle@saperp ~]$ sqlplus test/test

SQL*Plus: Release 11.2.0.4.0 Production on Fri Dec 25 19:22:10 2020

Copyright (c) 1982, 2013, Oracle.  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

SQL> set line 132
SQL> set wrap off
SQL> set line 160
SQL> set time on
19:22:35 SQL> 
19:22:37 SQL> select count(*) from a;

  COUNT(*)
----------
        31

19:23:24 SQL> select count(*) from b;

  COUNT(*)
----------
         1

19:23:27 SQL> select user_id,username from dba_users where username in('JYC','TEST');

   USER_ID USERNAME
---------- ------------------------------
        97 TEST
        96 JYC

19:23:43 SQL> show user;
USER is "TEST"
19:23:57 SQL> conn /as sysdba
Connected.
19:24:00 SQL> show user;
USER is "SYS"
19:24:02 SQL> create table obj as select * from obj$ where 1=2;

Table created.

19:25:49 SQL> select * from v$Log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ -------------------
         1          1         31   52428800        512          1 NO  CURRENT                1819929 2020-12-25 19:10:46   2.8147E+14
         2          1         29   52428800        512          1 YES INACTIVE               1799340 2020-12-25 16:49:24      1799397 2020-12-25 16:49:27
         3          1         30   52428800        512          1 YES INACTIVE               1799397 2020-12-25 16:49:27      1819929 2020-12-25 19:10:46

19:26:25 SQL> select * from v$Logfile;
rows will be truncated


    GROUP# STATUS  TYPE    MEMBER
---------- ------- ------- -------------------------------------------------------------------------------------------------------------------------------------
         3         ONLINE  /home/db/oracle/oradata/orcl/redo03.log
         2         ONLINE  /home/db/oracle/oradata/orcl/redo02.log
         1         ONLINE  /home/db/oracle/oradata/orcl/redo01.log

19:26:57 SQL> 
19:26:58 SQL> exec sys.dbms_logmnr.add_logfile('/home/db/oracle/oradata/orcl/redo01.log',sys.dbms_logmnr.new);

PL/SQL procedure successfully completed.

19:27:09 SQL> exec sys.dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog+dbms_logmnr.committed_data_only);

PL/SQL procedure successfully completed.

19:27:28 SQL> create table logmnr_new tablespace users as select * from v$logmnr_contents;
create table logmnr_new tablespace users as select * from v$logmnr_contents
                                                          *
ERROR at line 1:
ORA-00310: archived log contains sequence 34; sequence 31 required
ORA-00334: archived log: '/home/db/oracle/oradata/orcl/redo01.log'


19:27:54 SQL> execute dbms_logmnr.end_logmnr;

PL/SQL procedure successfully completed.

19:28:17 SQL> exec sys.dbms_logmnr.add_logfile('/home/db/oracle/oradata/orcl/redo01.log',sys.dbms_logmnr.new);

PL/SQL procedure successfully completed.

19:28:33 SQL> exec sys.dbms_logmnr.add_logfile('/home/db/oracle/oradata/orcl/redo02.log',sys.dbms_logmnr.addfile);

PL/SQL procedure successfully completed.

19:28:55 SQL> exec sys.dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog+dbms_logmnr.committed_data_only);

PL/SQL procedure successfully completed.

19:29:03 SQL> create table logmnr_new tablespace users as select * from v$logmnr_contents;

Table created.

19:29:10 SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ -------------------
         1          1         34   52428800        512          1 YES ACTIVE                 1824343 2020-12-25 19:27:53      1824505 2020-12-25 19:27:56
         2          1         35   52428800        512          1 NO  CURRENT                1824505 2020-12-25 19:27:56   2.8147E+14
         3          1         33   52428800        512          1 YES INACTIVE               1824237 2020-12-25 19:27:50      1824343 2020-12-25 19:27:53

19:29:22 SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /home/oracle/archivelog
Oldest online log sequence     33
Next log sequence to archive   35
Current log sequence           35
19:29:41 SQL> !ls -lt /home/oracle/archivelog|head
total 697348
-rw-r----- 1 oracle oinstall 30253056 Dec 25 19:27 1_34_1059213747.dbf
-rw-r----- 1 oracle oinstall 41063936 Dec 25 19:27 1_33_1059213747.dbf
-rw-r----- 1 oracle oinstall 41061888 Dec 25 19:27 1_32_1059213747.dbf
-rw-r----- 1 oracle oinstall 41057280 Dec 25 19:27 1_31_1059213747.dbf
-rw-r----- 1 oracle oinstall 17920000 Dec 25 19:10 1_30_1059213747.dbf
-rw-r----- 1 oracle oinstall 41063936 Dec 25 16:49 1_29_1059213747.dbf
-rw-r----- 1 oracle oinstall 38919680 Dec 25 16:49 1_28_1059213747.dbf
-rw-r----- 1 oracle oinstall 41061888 Dec 25 16:49 1_27_1059213747.dbf
-rw-r----- 1 oracle oinstall 41061376 Dec 25 16:15 1_26_1059213747.dbf

19:29:51 SQL> execute dbms_logmnr.end_logmnr;

PL/SQL procedure successfully completed.

19:30:11 SQL> drop table logmnr_new purge;

Table dropped.

19:30:17 SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ -------------------
         1          1         34   52428800        512          1 YES ACTIVE                 1824343 2020-12-25 19:27:53      1824505 2020-12-25 19:27:56
         2          1         35   52428800        512          1 NO  CURRENT                1824505 2020-12-25 19:27:56   2.8147E+14
         3          1         33   52428800        512          1 YES INACTIVE               1824237 2020-12-25 19:27:50      1824343 2020-12-25 19:27:53

19:30:21 SQL> exec sys.dbms_logmnr.add_logfile('/home/oracle/archivelog/1_31_1059213747.dbf',sys.dbms_logmnr.new);

PL/SQL procedure successfully completed.

19:31:19 SQL> exec sys.dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog+dbms_logmnr.committed_data_only);

PL/SQL procedure successfully completed.

19:31:34 SQL> create table logmnr_new tablespace users as select * from v$logmnr_contents;

Table created.

19:31:53 SQL> select count(*) from logmnr_new;

  COUNT(*)
----------
     58966

19:32:02 SQL> execute dbms_logmnr.end_logmnr;

PL/SQL procedure successfully completed.

---此处查询原用户jyc的user_id(94)--

select SQL_UNDO from logmnr_new 
  where table_name='OBJ$' 
    and sql_redo like 'delete from%' 
    and sql_redo like '%'||'"TYPE#" = ''2'''||'%' 
    and sql_redo not like '%'||'"DATAOBJ#" IS NULL'||'%'
    and sql_redo like '%'||'"OWNER#" = ''94'''||'%' ;

select SQL_UNDO from logmnr_new 
  where table_name='OBJ$' 
    and sql_redo like 'delete from%' 
    and sql_redo like '%'||'"TYPE#" = ''2'''||'%' 
    and sql_redo not like '%'||'"DATAOBJ#" IS NULL'||'%'
    and sql_redo like '%'||'"OWNER#" = ''94'''||'%' ;
将查的结果修改OBJ$为OBJ做插入到obj表中。

19:32:13 SQL> insert into "SYS"."OBJ"("OBJ#","DATAOBJ#","OWNER#","NAME","NAMESPACE","SUBNAME","TYPE#","CTIME","MTIME","STIME","STATUS","REMOTEOWNER","LINKNAME","FLAGS",19:32:13 SQL> insert into "SYS"."OBJ"("OBJ#","DATAOBJ#","OWNER#","NAME","NAMESPACE","SUBNAME","TYPE#","CTIME","MTIME","STIME","STATUS","REMOTEOWNER","LINKNAME","FLAGS","OID$","SPARE1","SPARE2","SPARE3","SPARE4","SPARE5","SPARE6") values ('88285','88285','96','SYS_EXPORT_SCHEMA_01','1',NULL,'2',TO_DATE('2020-12-25 19:17:37', 'yyyy-mm-d"OID$","SPARE1","SPARE2","SPARE3","SPARE4","SPARE5","SPARE6") values ('88285','88285','96','SYS_EXPORT_SCHEMA_01','1',NULL,'2',TO_DATE('2020-12-25 19:17:37', 'yyyy-mm-dd hh24:mi:ss'),TO_DATE('2020-12-25 19:17:38', 'yyyy-mm-dd hh24:mi:ss'),TO_DATE('2020-12-25 19:17:37', 'yyyy-mm-dd hh24:mi:ss'),'1',NULL,NULL,'0',NULL,'6','1','96',NULL,"OID$","SPARE1","SPARE2","SPARE3","SPARE4","SPARE5","SPARE6") values ('88285','88285','96','SYS_EXPORT_SCHEMA_01','1',NULL,'2',TO_DATE('2020-12-25 19:17:37', 'yyyy-mm-dd hh24:mi:ss'),TO_DATE('2020-12-25 19:17:38', 'yyyy-mm-dd hh24:mi:ss'),TO_DATE('2020-12-25 19:17:37', 'yyyy-mm-dd hh24:mi:ss'),'1',NULL,NULL,'0',NULL,'6','1','96',NULL,19:32:13 SQL> 


19:32:13 SQL> insert into "SYS"."OBJ"("OBJ#","DATAOBJ#","OWNER#","NAME","NAMESPACE","SUBNAME","TYPE#","CTIME","MTIME","STIME","STATUS","REMOTEOWNER","LINKNAME","FLAGS","OID$","SPARE1","SPARE2","SPARE3","SPARE4","SPARE5","SPARE6") values ('88285','88285','96','SYS_EXPORT_SCHEMA_01','1',NULL,'2',TO_DATE('2020-12-25 19:17:37', 'yyyy-mm-dd hh24:mi:ss'),TO_DATE('2020-12-25 19:17:38', 'yyyy-mm-dd hh24:mi:ss'),TO_DATE('2020-12-25 19:17:37', 'yyyy-mm-dd hh24:mi:ss'),'1',NULL,NULL,'0',NULL,'6','1','96',NULL,NULL,NULL);

1 row created.

19:34:38 SQL> insert into "SYS"."OBJ"("OBJ#","DATAOBJ#","OWNER#","NAME","NAMESPACE","SUBNAME","TYPE#","CTIME","MTIME","STIME","STATUS","REMOTEOWNER","LINKNAME","FLAGS",19:34:38 SQL> insert into "SYS"."OBJ"("OBJ#","DATAOBJ#","OWNER#","NAME","NAMESPACE","SUBNAME","TYPE#","CTIME","MTIME","STIME","STATUS","REMOTEOWNER","LINKNAME","FLAGS","OID$","SPARE1","SPARE2","SPARE3","SPARE4","SPARE5","SPARE6") values ('88283','88283','96','B','1',NULL,'2',TO_DATE('2020-12-25 19:16:23', 'yyyy-mm-dd hh24:mi:ss'),TO_D"OID$","SPARE1","SPARE2","SPARE3","SPARE4","SPARE5","SPARE6") values ('88283','88283','96','B','1',NULL,'2',TO_DATE('2020-12-25 19:16:23', 'yyyy-mm-dd hh24:mi:ss'),TO_D19:34:38 SQL> 

insert into "SYS"."OBJ"("OBJ#","DATAOBJ#","OWNER#","NAME","NAMESPACE","SUBNAME","TYPE#","CTIME","MTIME","STIME","STATUS","REMOTEOWNER","LINKNAME","FLAGS","OID$","SPARE1","SPARE2","SPARE3","SPARE4","SPARE5","SPARE6") values ('88283','88283','96','B','1',NULL,'2',TO_DATE('2020-12-25 19:16:23', 'yyyy-mm-dd hh24:mi:ss'),TO_DATE('2020-12-25 19:16:23', 'yyyy-mm-dd hh24:mi:ss'),TO_DATE('2020-12-25 19:16:23', 'yyyy-mm-dd hh24:mi:ss'),'1',NULL,NULL,'0',NULL,'6','1','96',NULL,NULL,NULL);

1 row created.

19:34:38 SQL> insert into "SYS"."OBJ"("OBJ#","DATAOBJ#","OWNER#","NAME","NAMESPACE","SUBNAME","TYPE#","CTIME","MTIME","STIME","STATUS","REMOTEOWNER","LINKNAME","FLAGS",19:34:38 SQL> insert into "SYS"."OBJ"("OBJ#","DATAOBJ#","OWNER#","NAME","NAMESPACE","SUBNAME","TYPE#","CTIME","MTIME","STIME","STATUS","REMOTEOWNER","LINKNAME","FLAGS","OID$","SPARE1","SPARE2","SPARE3","SPARE4","SPARE5","SPARE6") values ('88284','88284','96','A','1',NULL,'2',TO_DATE('2020-12-25 19:16:28', 'yyyy-mm-dd hh24:mi:ss'),TO_D"OID$","SPARE1","SPARE2","SPARE3","SPARE4","SPARE5","SPARE6") values ('88284','88284','96','A','1',NULL,'2',TO_DATE('2020-12-25 19:16:28', 'yyyy-mm-dd hh24:mi:ss'),TO_D19:34:38 SQL> 

insert into "SYS"."OBJ"("OBJ#","DATAOBJ#","OWNER#","NAME","NAMESPACE","SUBNAME","TYPE#","CTIME","MTIME","STIME","STATUS","REMOTEOWNER","LINKNAME","FLAGS","OID$","SPARE1","SPARE2","SPARE3","SPARE4","SPARE5","SPARE6") values ('88284','88284','96','A','1',NULL,'2',TO_DATE('2020-12-25 19:16:28', 'yyyy-mm-dd hh24:mi:ss'),TO_DATE('2020-12-25 19:16:28', 'yyyy-mm-dd hh24:mi:ss'),TO_DATE('2020-12-25 19:16:28', 'yyyy-mm-dd hh24:mi:ss'),'1',NULL,NULL,'0',NULL,'6','1','96',NULL,NULL,NULL);

1 row created.

19:34:39 SQL> insert into "SYS"."OBJ"("OBJ#","DATAOBJ#","OWNER#","NAME","NAMESPACE","SUBNAME","TYPE#","CTIME","MTIME","STIME","STATUS","REMOTEOWNER","LINKNAME","FLAGS",19:34:39 SQL> insert into "SYS"."OBJ"("OBJ#","DATAOBJ#","OWNER#","NAME","NAMESPACE","SUBNAME","TYPE#","CTIME","MTIME","STIME","STATUS","REMOTEOWNER","LINKNAME","FLAGS","OID$","SPARE1","SPARE2","SPARE3","SPARE4","SPARE5","SPARE6") values ('88305','88305','96','SYS_IMPORT_SCHEMA_01','1',NULL,'2',TO_DATE('2020-12-25 19:19:45', 'yyyy-mm-d"OID$","SPARE1","SPARE2","SPARE3","SPARE4","SPARE5","SPARE6") values ('88305','88305','96','SYS_IMPORT_SCHEMA_01','1',NULL,'2',TO_DATE('2020-12-25 19:19:45', 'yyyy-mm-dd hh24:mi:ss'),TO_DATE('2020-12-25 19:19:45', 'yyyy-mm-dd hh24:mi:ss'),TO_DATE('2020-12-25 19:19:46', 'yyyy-mm-dd hh24:mi:ss'),'1',NULL,NULL,'0',NULL,'6','2','96',NULL,"OID$","SPARE1","SPARE2","SPARE3","SPARE4","SPARE5","SPARE6") values ('88305','88305','96','SYS_IMPORT_SCHEMA_01','1',NULL,'2',TO_DATE('2020-12-25 19:19:45', 'yyyy-mm-dd hh24:mi:ss'),TO_DATE('2020-12-25 19:19:45', 'yyyy-mm-dd hh24:mi:ss'),TO_DATE('2020-12-25 19:19:46', 'yyyy-mm-dd hh24:mi:ss'),'1',NULL,NULL,'0',NULL,'6','2','96',NULL,19:34:39 SQL> 


19:34:39 SQL> insert into "SYS"."OBJ"("OBJ#","DATAOBJ#","OWNER#","NAME","NAMESPACE","SUBNAME","TYPE#","CTIME","MTIME","STIME","STATUS","REMOTEOWNER","LINKNAME","FLAGS","OID$","SPARE1","SPARE2","SPARE3","SPARE4","SPARE5","SPARE6") values ('88305','88305','96','SYS_IMPORT_SCHEMA_01','1',NULL,'2',TO_DATE('2020-12-25 19:19:45', 'yyyy-mm-dd hh24:mi:ss'),TO_DATE('2020-12-25 19:19:45', 'yyyy-mm-dd hh24:mi:ss'),TO_DATE('2020-12-25 19:19:46', 'yyyy-mm-dd hh24:mi:ss'),'1',NULL,NULL,'0',NULL,'6','2','96',NULL,NULL,NULL);

1 row created.

19:34:39 SQL> commit;

Commit complete.

19:34:47 SQL> create table t as select b.obj# obj_old,b.name,a.obj# obj_new,a.owner# from obj$ a,obj b where a.name=b.name and a.owner#=97;--此处为新用户test的user_id号

Table created.

19:35:24 SQL> select * from t;

   OBJ_OLD NAME                              OBJ_NEW     OWNER#
---------- ------------------------------ ---------- ----------
     88283 B                                   88353         97
     88284 A                                   88354         97

19:35:27 SQL> update obj$ a set obj#=(select obj_old from t where t.obj_new=a.obj#) where obj# in (select obj_new from t);

2 rows updated.

19:35:51 SQL> update col$ a set obj#=(select obj_old from t where t.obj_new=a.obj#) where obj# in (select obj_new from t);

29 rows updated.

19:35:51 SQL> update lob$ a set obj#=(select obj_old from t where t.obj_new=a.obj#) where obj# in (select obj_new from t);

0 rows updated.

19:35:51 SQL> update tab$ a set obj#=(select obj_old from t where t.obj_new=a.obj#) where obj# in (select obj_new from t);

2 rows updated.

19:35:52 SQL> 
19:35:52 SQL> commit;

Commit complete.

19:35:54 SQL> exec sys.dbms_logmnr.add_logfile('/home/oracle/archivelog/1_31_1059213747.dbf',sys.dbms_logmnr.new);

PL/SQL procedure successfully completed.

19:40:11 SQL> exec sys.dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog+dbms_logmnr.committed_data_only);

PL/SQL procedure successfully completed.

19:40:19 SQL> create table logmnr_new1 tablespace users as select * from v$logmnr_contents;

Table created.

19:40:45 SQL> select count(*) from logmnr_new1;

  COUNT(*)
----------
     58966

19:40:51 SQL> execute dbms_logmnr.end_logmnr;

PL/SQL procedure successfully completed.

create table logmnr_ok tablespace users as SELECT /*+full(t1) parallel(t1 2)*/COMMIT_TIMESTAMP,TIMESTAMP,regexp_substr(replace(sql_redo,'and ROWID','?'),'[^?]+',1,1) sql_redo FROM  logmnr_new1 t1 
where data_obj# in (select t.obj_old from t) and OPERATION  in ('INSERT','UPDATE','DELETE','LOB_WRITE') and TIMESTAMP>=to_date('2020-12-25 19:17:57','yyyy-mm-dd hh24:mi:ss') and TIMESTAMP<=to_date('2020-12-25 19:19:09','yyyy-mm-dd hh24:mi:ss');

19:40:57 SQL> create table logmnr_ok tablespace users as SELECT /*+full(t1) parallel(t1 2)*/COMMIT_TIMESTAMP,TIMESTAMP,regexp_substr(replace(sql_redo,'and ROWID','?'),'[^?]+',1,1) sql_redo FROM  logmnr_new1 t1 
19:42:20   2  where data_obj# in (select t.obj_old from t) and OPERATION  in ('INSERT','UPDATE','DELETE','LOB_WRITE') and TIMESTAMP>=to_date('2020-12-25 19:17:57','yyyy19:42:20   2  where data_obj# in (select t.obj_old from t) and OPERATION  in ('INSERT','UPDATE','DELETE','LOB_WRITE') and TIMESTAMP>=to_date('2020-12-25 19:17:57','yyyy19:42:20   2  
19:42:20   2  where data_obj# in (select t.obj_old from t) and OPERATION  in ('INSERT','UPDATE','DELETE','LOB_WRITE') and TIMESTAMP>=to_date('2020-12-25 19:17:57','yyyy-mm-dd hh24:mi:ss') and TIMESTAMP<=to_date('2020-12-25 19:19:09','yyyy-mm-dd hh24:mi:ss');

Table created.

19:42:20 SQL> select count(*) from logmnr_ok;

  COUNT(*)
----------
       528

19:42:29 SQL> select * from logmnr_ok where rownum<5;

COMMIT_TIMESTAMP    TIMESTAMP           SQL_REDO
------------------- ------------------- ------------------------------------------------------------------------------------------------------------------------
2020-12-25 19:18:19 2020-12-25 19:18:13 insert into "TEST"."A"("USERNAME","USER_ID","PASSWORD","ACCOUNT_STATUS","LOCK_DATE","EXPIRY_DATE","DEFAULT_TABLESPACE","
2020-12-25 19:18:19 2020-12-25 19:18:16 insert into "TEST"."A"("USERNAME","USER_ID","PASSWORD","ACCOUNT_STATUS","LOCK_DATE","EXPIRY_DATE","DEFAULT_TABLESPACE","
2020-12-25 19:18:19 2020-12-25 19:18:16 insert into "TEST"."A"("USERNAME","USER_ID","PASSWORD","ACCOUNT_STATUS","LOCK_DATE","EXPIRY_DATE","DEFAULT_TABLESPACE","
2020-12-25 19:18:19 2020-12-25 19:18:16 insert into "TEST"."A"("USERNAME","USER_ID","PASSWORD","ACCOUNT_STATUS","LOCK_DATE","EXPIRY_DATE","DEFAULT_TABLESPACE","

19:42:40 SQL> --set serverout on --数据量太大就不输出了

DECLARE
c_limit   CONSTANT PLS_INTEGER DEFAULT 1000;
v_sql varchar2(32767);
CURSOR c1 IS select /*+parallel(t 2)*/ rtrim(sql_redo,';') from logmnr_ok t order by COMMIT_TIMESTAMP,TIMESTAMP;
TYPE typ1 IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;
redo         typ1;
BEGIN
OPEN c1;
LOOP
FETCH c1 BULK COLLECT INTO redo LIMIT c_limit;
EXIT WHEN redo.COUNT = 0;
FOR indx IN 1 .. redo.COUNT
LOOP
begin 
v_sql:=redo(indx);
dbms_output.put_line(v_sql);
execute immediate v_sql;
exception
when others then
dbms_output.put_line('ERROR:'||v_sql);
end;
END LOOP;
commit;
END LOOP;
CLOSE c1;
END;
/


19:43:08 SQL> DECLARE
19:43:08   2  c_limit   CONSTANT PLS_INTEGER DEFAULT 1000;
19:43:08   3  v_sql varchar2(32767);
19:43:08   4  CURSOR c1 IS select /*+parallel(t 2)*/ rtrim(sql_redo,';') from logmnr_ok t order by COMMIT_TIMESTAMP,TIMESTAMP;
19:43:08   5  TYPE typ1 IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;
19:43:08   6  redo         typ1;
19:43:08   7  BEGIN
19:43:09   8  OPEN c1;
19:43:09   9  LOOP
19:43:09  10  FETCH c1 BULK COLLECT INTO redo LIMIT c_limit;
19:43:09  11  EXIT WHEN redo.COUNT = 0;
19:43:09  12  FOR indx IN 1 .. redo.COUNT
19:43:09  13  LOOP
19:43:09  14  begin 
19:43:09  15  v_sql:=redo(indx);
19:43:09  16  dbms_output.put_line(v_sql);
19:43:09  17  execute immediate v_sql;
19:43:09  18  exception
19:43:09  19  when others then
19:43:09  20  dbms_output.put_line('ERROR:'||v_sql);
19:43:09  21  end;
19:43:09  22  END LOOP;
19:43:09  23  commit;
19:43:09  24  END LOOP;
19:43:09  25  CLOSE c1;
19:43:09  26  END;
19:43:09  27  /
insert into
"TEST"."A"("USERNAME","USER_ID","PASSWORD","ACCOUNT_STATUS","LOCK_DATE","EXPIRY_DATE","DEFAULT_TABLESPACE","TEMPORARY_TABLESPACE","CREATED","PROFILE","INITIAL_R
SRC_CONSUMER_GROUP","EXTERNAL_NAME","PASSWORD_VERSIONS","EDITIONS_ENABLED","AUTHENTICATION_TYPE") values ('JYC','96',NULL,'OPEN',NULL,TO_DATE('2021-06-23
19:14:28', 'yyyy-mm-dd hh24:mi:ss'),'USERS','TEMP',TO_DATE('2020-12-25 19:14:28', 'yyyy-mm-dd hh24:mi:ss'),'DEFAULT','DEFAULT_CONSUMER_GROUP',NULL,'10G 11G
','N','PASSWORD')
insert into
......
PL/SQL procedure successfully completed.

19:43:11 SQL> commit;

Commit complete.

19:43:15 SQL> select count(*) from jyc.a;

  COUNT(*)
----------
        31

19:43:25 SQL> select count(*) from jyc.b;

  COUNT(*)
----------
         1

19:43:28 SQL> select count(*) from test.a;

  COUNT(*)
----------
       496

19:43:34 SQL> select count(*) from test.b;

  COUNT(*)
----------
        64

相关参考:

http://www.killdb.com/2020/05/15/imp-impdp-%e8%a6%86%e7%9b%96schema%e5%90%8e%e5%a6%82%e4%bd%95%e6%81%a2%e5%a4%8d%e6%95%b0%e6%8d%ae/

http://www.htz.pw/?p=1189

上一篇:SQL----EXISTS 关键字EXISTS基本意思


下一篇:Jquery学习笔记:利用jquery获取select下拉框的值