本文共 26898 字，大约阅读时间需要 89 分钟。
无expdp备份，无rman备份，有归档，采用dbms_logmnr恢复。
SQL> set line 132 SQL> set wrap off SQL> set line 160SQL> 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:46SQL> 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(*)
---------- 31SQL> select count(*) from b;
COUNT(*)
---------- 1SQL> 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=dmpExport: 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
exit19: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:4619: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(*)
---------- 6419:18:58 SQL>
19:19:04 SQL> 19:19:05 SQL> select count(*) from a;COUNT(*)
---------- 49619:19:09 SQL> !
[oracle@saperp ~]$ impdp jyc/jyc dumpfile=JYC-19.dmp logfile=19-imp.log schemas=JYC DIRECTORY=dmp TABLE_EXISTS_ACTION=REPLACEImport: 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
exit19:19:50 SQL> show user;
USER is "JYC" 19:19:54 SQL> select count(*) from b;COUNT(*)
---------- 119:20:08 SQL> select count(*) from a;
COUNT(*)
---------- 3119: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:4619: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=dmpImport: 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/testSQL*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 optionsSQL> 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(*)
---------- 3119:23:24 SQL> select count(*) from b;
COUNT(*)
---------- 119:23:27 SQL> select user_id,username from dba_users where username in('JYC','TEST');
USER_ID USERNAME
---------- ------------------------------ 97 TEST 96 JYC19: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:4619: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.log19: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:5319: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.dbf19: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:5319: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(*)
---------- 5896619: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 9719: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(*)
---------- 5896619: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(*)
---------- 52819: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 --数据量太大就不输出了
DECLAREc_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;BEGINOPEN c1;LOOPFETCH c1 BULK COLLECT INTO redo LIMIT c_limit;EXIT WHEN redo.COUNT = 0;FOR indx IN 1 .. redo.COUNTLOOPbegin v_sql:=redo(indx);dbms_output.put_line(v_sql);execute immediate v_sql;exceptionwhen others thendbms_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(*)
---------- 3119:43:25 SQL> select count(*) from jyc.b;
COUNT(*)
---------- 119:43:28 SQL> select count(*) from test.a;
COUNT(*)
---------- 49619:43:34 SQL> select count(*) from test.b;
COUNT(*)
---------- 6420:18:24 SQL> select object_id,object_name from dba_objects where owner='TEST' and object_name in('A','B');
OBJECT_ID OBJECT_NAME
---------- -------------------------------------------------------------------------------------------------------------------------------- 88284 A 88283 B 20:22:32 SQL> select object_id,object_name from dba_objects where owner='JYC' and object_name in('A','B');OBJECT_ID OBJECT_NAME
---------- -------------------------------------------------------------------------------------------------------------------------------- 88329 A 88328 B相关参考:
转载地址：http://scsof.baihongyu.com/