无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
相关参考: