This is a very badly named post (it's a vague reference to the fact that this is my 300th blog post and it's come about as i have being having a major battle with trying to do a restore this past day.....)
So anyway back to the job in hand :
We have a very large warehouse (several TB) and a developer accidentally dropped one of their schemas, luckily we had flashback enabled so we thought it was just a simple case of flashback to the point before the schema was dropped and everything is happy - right?
Well in this case wrong - the initial problem was that the flashback logs didn't go back far enough, we didn't find that out until the flashback failed to go back that far (in hindsight perhaps we should have checked that - or even better oracle should tell us if it hasn't got the damn logs in the first place and not even start to try!)
So after that failed (which was our quick fix method) we were reduced to other methods.
Our first attempt was to do just do a point in time recovery (using rman duplicate) to another host - this would take a while as it's so big but should allow us to restore then extract the said schema - so we gave that a go and this is where things got interesting......
This is the basic rman duplicate command we were running
RMAN> run {
2> set until time "to_date('10-JUN-2015 04:30:00', 'dd-MON-yyyy hh24:mi:ss')";
3> allocate auxiliary channel t1 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/tsm/ORIG/conf/tdpo.opt)';
4> duplicate database ORIGto NEW
5> SKIP TABLESPACE IFC_USER_RISKCUBE,
DWH_DISTRIBUTION,
6> 7> SB_MRDD,
8> SAP_BODS,
9> DWH_STAGING,
10> DWH_INTEGRATION,
DWH_DATAMART,
11> 12> DWH_TESTDATA,
13> DWH_DERIVATION_AREA,
14> USERS;
15> }
This would run for a while and then get this error
RMAN-03002: failure of Duplicate Db command at 06/12/2015 18:54:15
RMAN-05501: aborting duplication of target database
RMAN-05541: no archived logs found in target database
The backups are clearly there though when doing reports from the rman catalog so what is going on? Anyway we messed around with that a few times until it became apparent that this was getting nowhere fast.
Then i had another idea - the schema in question was isolated to that single tablespace so we could make use of the neat restore tablespace until time command directly (and oracle would do all the clever magic with aux instances and TTS to make that happen) - so we gave that a try....
And that failed miserably too - and annoyingly i didn't save the error message - but anyway it failed - and in fact left the system in a worse state then when i started....
this step to offline the tablespace to do the restore worked ok
alter tablespace APEX_WORKSPACE_ER offline
Completed: alter tablespace APEX_WORKSPACE_ER offline
-- this is the bit where the TSPITR failed without even doing anything--
When i tried to bring it back online (bearing in mind nothing had actually be done to the file)
alter tablespace APEX_WORKSPACE_ER online
ORA-1190 signalled during: alter tablespace APEX_WORKSPACE_ER online...
Checker run found 3 new persistent data failures
ALTER DATABASE RECOVER datafile '/oracle/EDER/oradata/EDER2/datafile/o1_mf_apex_wor_bcwmswsq_.dbf'
Media Recovery Start
Serial Media Recovery started
Cannot mark control file as backup: flashback database enabled
Media Recovery failed with error 38872
ORA-283 signalled during: ALTER DATABASE RECOVER datafile '/oracle/ORIG/oradata/ORIG/datafile/o1_mf_apex_wor_bcwmswsq_.dbf' ...
2015-06-12 19:32:01.334000 +02:00
And the file and therefore the tablespace was pretty much toast now - it's possible that disabling flashback at this point may have enabled the file recovery to work but i didn't try that
OK - now what - running out of options...
Well the ultimate is always a restore from tape, i didn't want to do this directly over the top in case something else went wrong - at the moment only the one tablespace was corrupted - so i decided to restore the database to a different host from the backups.
So anyway i set all that up (tsm config files/spfile etc) and then gave it a go - this must surely work - good old fashioned restore never lets us down.....?
*** now I've skipped a little bit here as i had discovered that to do this restore to this point in time i needed the earlier incarnation of the database as flashback had messed things up....suffice to say this has to be used and this is what i set at the start ***
Now this is the point where it got really worrying - lets do the first bit of the restore (the controlfile) I have to set the dbid - as we have no controlfile at the moment rman has no idea which db we are actually wanting to restore - dbname is not enough - and we also have to go to the earlier incarnation - so that's what i do here
rman target=/
Recovery Manager: Release 11.2.0.4.0 - Production on Sat Jun 13 02:26:52 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORIG (not mounted)
RMAN> connect catalog user/pass@rcat
connected to recovery catalog database
RMAN> set dbid=3191228040;
reset database to incarnation 1828799752;
executing command: SET DBID
database name is "ORIG" and DBID is 3191228040
RMAN>
database reset to incarnation 1828799752
RMAN>
So far so good - so lets bring back the controlfile
RMAN> run {
set until time "to_date('10-JUN-2015 04:30:00', 'dd-MON-yyyy hh24:mi:ss')";
allocate channel t1 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/tsm/ORIG/conf/tdpo.opt)';
restore controlfile;
sql "alter database mount";
}2> 3> 4> 5> 6>
executing command: SET until clause
allocated channel: t1
channel t1: SID=101 device type=SBT_TAPE
channel t1: Data Protection for Oracle: version 6.3.0.0
Starting restore at 13-JUN-15
new media label is 36077 for piece cf_auto_c-3191228040-20150610-15
new media label is 36135 for piece cf_auto_c-3191228040-20150610-16
channel t1: starting datafile backup set restore
channel t1: restoring control file
channel t1: reading from backup piece cf_auto_c-3191228040-20150610-15
channel t1: piece handle=cf_auto_c-3191228040-20150610-15 tag=TAG20150610T042704
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:01:45
output file name=/oracle/ORIG/oradata/ORIG/controlfile/o1_mf_bqpycl4t_.ctl
output file name=/oracle/ORIG/recovery_area/ORIG/controlfile/o1_mf_bqpyclow_.ctl
Finished restore at 13-JUN-15
sql statement: alter database mount
released channel: t1
Right - now at this point anything i tried to do to restore the rest of the database failed (and failed badly) - generally resulting in this error
RMAN-03002: failure of restore command at 06/12/2015 20:14:40
RMAN-00600: internal error, arguments [8714] [] [] [] []
Which is one I've seen before (actually on this same set of databases) and never really got to the bottom of - and there is hardly anything else on that error on the internet.
I was close to giving up when i noticed something really odd - look at the output of this command when connected to the catalog
RMAN> list incarnation of database;
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1828799650 1828799651 ORIG 3191228040 PARENT 1 25-OCT-12
1828799650 1828799752 ORIG 3191228040 CURRENT 8931318782946 05-SEP-14
Now compare that when you just connect to the target and do it from the controlfile
oracle@server:/oracle/11.2.0.4.3.DB/dbs> rman target=/
Recovery Manager: Release 11.2.0.4.0 - Production on Sat Jun 13 02:30:21 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORIG (DBID=3191228040, not open)
RMAN> list incarnation of database;
using target database control file instead of recovery catalog
List of Database Incarnations
DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1 1 ORIG 3191228040 CURRENT 1 25-OCT-12
2 2 ORIG 3191228040 ORPHAN 8931318782946 05-SEP-14
Incarnation numbers in the controlfile are just 1 and 2 ????? what is going on here?
Anyway it gave me an idea - lets try the restore now just using the controlfile information (the catalog was only needed to get the controlfile back) - lets now try using just the controlfile
So i connect just to that and choose the incarnation that corresponds to the controlfile version of things - then i try again (btw note the use of the skip command on restore and recover to reduce the amount of stuff coming back as i have no need for that)
RMAN> reset database to incarnation 2;
database reset to incarnation 2
RMAN>
RMAN> run {
2> set until time "to_date('10-JUN-2015 04:30:00', 'dd-MON-yyyy hh24:mi:ss')";
3> allocate channel t1 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/tsm/ORIG/conf/tdpo.opt)';
restore database
4> 5> SKIP TABLESPACE IFC_USER_RISKCUBE,
6> DWH_DISTRIBUTION,
7> SB_MRDD,
8> DWH_STAGING,
9> DWH_INTEGRATION,
10> DWH_DATAMART,SAP_BODS,
11> DWH_TESTDATA,
12> DWH_DERIVATION_AREA;
13> recover database SKIP TABLESPACE IFC_USER_RISKCUBE,
14> DWH_DISTRIBUTION,
15> SB_MRDD,
16> DWH_STAGING,
17> DWH_INTEGRATION,
18> DWH_DATAMART,SAP_BODS,
19> DWH_TESTDATA,
20> DWH_DERIVATION_AREA
21> ;
22> }
executing command: SET until clause
allocated channel: t1
channel t1: SID=101 device type=SBT_TAPE
channel t1: Data Protection for Oracle: version 6.3.0.0
Starting restore at 13-JUN-15
Starting implicit crosscheck backup at 13-JUN-15
Finished implicit crosscheck backup at 13-JUN-15
Starting implicit crosscheck copy at 13-JUN-15
Finished implicit crosscheck copy at 13-JUN-15
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /oracle/ORIG/recovery_area/ORIG/controlfile/o1_mf_bqp8nypp_.ctl
File Name: /oracle/ORIG/recovery_area/ORIG/autobackup/2015_06_13/o1_mf_n_882238842_bqpxtv6q_.bkp
channel t1: starting datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
channel t1: restoring datafile 00003 to /oracle/EDER/oradata/ORIG/datafile/o1_mf_sys_undo_88kqvglo_.dbf
channel t1: restoring datafile 00024 to /oracle/EDER/oradata/ORIG/datafile/o1_mf_sys_undo_8sw5yf1d_.dbf
channel t1: reading from backup piece database_online_ORIG_881978020_20150610_10639.rman
channel t1: piece handle=database_online_ORIG_881978020_20150610_10639.rman tag=TAG20150609T235003
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:19:15
channel t1: starting datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
channel t1: restoring datafile 00002 to /oracle/EDER/oradata/ORIG/datafile/o1_mf_sysaux_88kqvdqb_.dbf
channel t1: restoring datafile 00052 to /oracle/EDER/oradata/ORIG/datafile/o1_mf_apex_wor_bcwmswsq_.dbf
channel t1: reading from backup piece database_online_ORIG_881981006_20150610_10648.rman
channel t1: piece handle=database_online_ORIG_881981006_20150610_10648.rman tag=TAG20150609T235003
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:16:05
channel t1: starting datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
channel t1: restoring datafile 00046 to /oracle/EDER/oradata/ORIG/datafile/o1_mf_sys_undo_b528l1mb_.dbf
channel t1: reading from backup piece database_online_ORIG_881981461_20150610_10650.rman
channel t1: piece handle=database_online_ORIG_881981461_20150610_10650.rman tag=TAG20150609T235003
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:09:35
channel t1: starting datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
channel t1: restoring datafile 00045 to /oracle/EDER/oradata/ORIG/datafile/o1_mf_sys_undo_b528kzkl_.dbf
channel t1: reading from backup piece database_online_ORIG_881982216_20150610_10654.rman
channel t1: piece handle=database_online_ORIG_881982216_20150610_10654.rman tag=TAG20150609T235003
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:09:35
channel t1: starting datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
channel t1: restoring datafile 00001 to /oracle/EDER/oradata/ORIG/datafile/o1_mf_system_88kqvc1l_.dbf
channel t1: restoring datafile 00044 to /oracle/EDER/oradata/ORIG/datafile/o1_mf_sys_undo_b528ky6o_.dbf
channel t1: reading from backup piece database_online_ORIG_881983071_20150610_10655.rman
channel t1: piece handle=database_online_ORIG_881983071_20150610_10655.rman tag=TAG20150609T235003
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:09:15
channel t1: starting datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
channel t1: restoring datafile 00027 to /oracle/EDER/oradata/ORIG/datafile/o1_mf_users_9hpo8hrx_.dbf
channel t1: restoring datafile 00043 to /oracle/EDER/oradata/ORIG/datafile/o1_mf_sys_undo_b528ks5b_.dbf
channel t1: reading from backup piece database_online_ORIG_881983866_20150610_10658.rman
channel t1: piece handle=database_online_ORIG_881983866_20150610_10658.rman tag=TAG20150609T235003
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:08:45
channel t1: starting datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
channel t1: restoring datafile 00040 to /oracle/EDER/oradata/ORIG/datafile/o1_mf_sys_undo_b528k3dw_.dbf
channel t1: reading from backup piece database_online_ORIG_881985397_20150610_10660.rman
channel t1: piece handle=database_online_ORIG_881985397_20150610_10660.rman tag=TAG20150609T235003
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:08:45
channel t1: starting datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
channel t1: restoring datafile 00041 to /oracle/EDER/oradata/ORIG/datafile/o1_mf_sys_undo_b528kot2_.dbf
channel t1: reading from backup piece database_online_ORIG_881986182_20150610_10663.rman
channel t1: piece handle=database_online_ORIG_881986182_20150610_10663.rman tag=TAG20150609T235003
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:08:55
channel t1: starting datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
channel t1: restoring datafile 00042 to /oracle/EDER/oradata/ORIG/datafile/o1_mf_sys_undo_b528kqdb_.dbf
channel t1: reading from backup piece database_online_ORIG_881986787_20150610_10664.rman
channel t1: piece handle=database_online_ORIG_881986787_20150610_10664.rman tag=TAG20150609T235003
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:08:35
Finished restore at 13-JUN-15
Starting recover at 13-JUN-15
Executing: alter database datafile 53 offline
Executing: alter database datafile 54 offline
Executing: alter database datafile 55 offline
Executing: alter database datafile 5 offline
Executing: alter database datafile 22 offline
Executing: alter database datafile 6 offline
Executing: alter database datafile 23 offline
Executing: alter database datafile 25 offline
Executing: alter database datafile 30 offline
Executing: alter database datafile 31 offline
Executing: alter database datafile 32 offline
Executing: alter database datafile 33 offline
Executing: alter database datafile 34 offline
Executing: alter database datafile 7 offline
Executing: alter database datafile 28 offline
Executing: alter database datafile 29 offline
Executing: alter database datafile 4 offline
Executing: alter database datafile 8 offline
Executing: alter database datafile 9 offline
Executing: alter database datafile 10 offline
Executing: alter database datafile 11 offline
Executing: alter database datafile 12 offline
Executing: alter database datafile 13 offline
Executing: alter database datafile 14 offline
Executing: alter database datafile 15 offline
Executing: alter database datafile 16 offline
Executing: alter database datafile 17 offline
Executing: alter database datafile 18 offline
Executing: alter database datafile 19 offline
Executing: alter database datafile 20 offline
Executing: alter database datafile 21 offline
Executing: alter database datafile 35 offline
Executing: alter database datafile 36 offline
Executing: alter database datafile 37 offline
Executing: alter database datafile 38 offline
Executing: alter database datafile 39 offline
Executing: alter database datafile 47 offline
Executing: alter database datafile 48 offline
Executing: alter database datafile 49 offline
Executing: alter database datafile 50 offline
Executing: alter database datafile 51 offline
Executing: alter database datafile 26 offline
starting media recovery
new media label is 36077 for piece redolog_ORIG_881978427_20150610_10640_1.rman
new media label is 36135 for piece redolog_ORIG_881978427_20150610_10640_2.rman
new media label is 36077 for piece redolog_ORIG_881980221_20150610_10645_1.rman
new media label is 36135 for piece redolog_ORIG_881980221_20150610_10645_2.rman
new media label is 36077 for piece redolog_ORIG_881981459_20150610_10649_1.rman
new media label is 36135 for piece redolog_ORIG_881981459_20150610_10649_2.rman
new media label is 36077 for piece redolog_ORIG_881982028_20150610_10652_1.rman
new media label is 36135 for piece redolog_ORIG_881982028_20150610_10652_2.rman
new media label is 36077 for piece redolog_ORIG_881983831_20150610_10656_1.rman
new media label is 36135 for piece redolog_ORIG_881983831_20150610_10656_2.rman
new media label is 36077 for piece redolog_ORIG_881985623_20150610_10661_1.rman
new media label is 36135 for piece redolog_ORIG_881985623_20150610_10661_2.rman
new media label is 36077 for piece redolog_ORIG_881987217_20150610_10666_1.rman
new media label is 36135 for piece redolog_ORIG_881987217_20150610_10666_2.rman
channel t1: starting archived log restore to default destination
channel t1: restoring archived log
archived log thread=1 sequence=8367
channel t1: reading from backup piece redolog_ORIG_881978427_20150610_10640_1.rman
channel t1: piece handle=redolog_ORIG_881978427_20150610_10640_1.rman tag=TAG20150610T020027
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:01:55
archived log file name=/oracle/ORIG/recovery_area/ORIG/archivelog/2015_06_13/o1_mf_1_8367_bqq4ffhl_.arc thread=1 sequence=8367
channel default: deleting archived log(s)
archived log file name=/oracle/ORIG/recovery_area/ORIG/archivelog/2015_06_13/o1_mf_1_8367_bqq4ffhl_.arc RECID=12056 STAMP=882245581
channel t1: starting archived log restore to default destination
channel t1: restoring archived log
archived log thread=1 sequence=8368
channel t1: reading from backup piece redolog_ORIG_881980221_20150610_10645_1.rman
channel t1: piece handle=redolog_ORIG_881980221_20150610_10645_1.rman tag=TAG20150610T023020
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:00:07
archived log file name=/oracle/ORIG/recovery_area/ORIG/archivelog/2015_06_13/o1_mf_1_8368_bqq4fv0v_.arc thread=1 sequence=8368
channel default: deleting archived log(s)
archived log file name=/oracle/ORIG/recovery_area/ORIG/archivelog/2015_06_13/o1_mf_1_8368_bqq4fv0v_.arc RECID=12057 STAMP=882245595
channel t1: starting archived log restore to default destination
channel t1: restoring archived log
archived log thread=1 sequence=8369
channel t1: reading from backup piece redolog_ORIG_881981459_20150610_10649_1.rman
channel t1: piece handle=redolog_ORIG_881981459_20150610_10649_1.rman tag=TAG20150610T025059
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:00:03
archived log file name=/oracle/ORIG/recovery_area/ORIG/archivelog/2015_06_13/o1_mf_1_8369_bqq4g21k_.arc thread=1 sequence=8369
channel default: deleting archived log(s)
archived log file name=/oracle/ORIG/recovery_area/ORIG/archivelog/2015_06_13/o1_mf_1_8369_bqq4g21k_.arc RECID=12058 STAMP=882245602
channel t1: starting archived log restore to default destination
channel t1: restoring archived log
archived log thread=1 sequence=8370
channel t1: reading from backup piece redolog_ORIG_881982028_20150610_10652_1.rman
channel t1: piece handle=redolog_ORIG_881982028_20150610_10652_1.rman tag=TAG20150610T030028
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:00:07
archived log file name=/oracle/ORIG/recovery_area/ORIG/archivelog/2015_06_13/o1_mf_1_8370_bqq4g5mq_.arc thread=1 sequence=8370
channel default: deleting archived log(s)
archived log file name=/oracle/ORIG/recovery_area/ORIG/archivelog/2015_06_13/o1_mf_1_8370_bqq4g5mq_.arc RECID=12059 STAMP=882245605
channel t1: starting archived log restore to default destination
channel t1: restoring archived log
archived log thread=1 sequence=8371
channel t1: reading from backup piece redolog_ORIG_881983831_20150610_10656_1.rman
channel t1: piece handle=redolog_ORIG_881983831_20150610_10656_1.rman tag=TAG20150610T033031
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:00:03
archived log file name=/oracle/ORIG/recovery_area/ORIG/archivelog/2015_06_13/o1_mf_1_8371_bqq4gfqb_.arc thread=1 sequence=8371
channel default: deleting archived log(s)
archived log file name=/oracle/ORIG/recovery_area/ORIG/archivelog/2015_06_13/o1_mf_1_8371_bqq4gfqb_.arc RECID=12060 STAMP=882245613
channel t1: starting archived log restore to default destination
channel t1: restoring archived log
archived log thread=1 sequence=8372
channel t1: reading from backup piece redolog_ORIG_881985623_20150610_10661_1.rman
channel t1: piece handle=redolog_ORIG_881985623_20150610_10661_1.rman tag=TAG20150610T040023
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:00:03
archived log file name=/oracle/ORIG/recovery_area/ORIG/archivelog/2015_06_13/o1_mf_1_8372_bqq4gjg6_.arc thread=1 sequence=8372
channel default: deleting archived log(s)
archived log file name=/oracle/ORIG/recovery_area/ORIG/archivelog/2015_06_13/o1_mf_1_8372_bqq4gjg6_.arc RECID=12061 STAMP=882245616
channel t1: starting archived log restore to default destination
channel t1: restoring archived log
archived log thread=1 sequence=8373
channel t1: reading from backup piece redolog_ORIG_881987217_20150610_10666_1.rman
channel t1: piece handle=redolog_ORIG_881987217_20150610_10666_1.rman tag=TAG20150610T042657
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:00:03
archived log file name=/oracle/ORIG/recovery_area/ORIG/archivelog/2015_06_13/o1_mf_1_8373_bqq4gn4g_.arc thread=1 sequence=8373
channel default: deleting archived log(s)
archived log file name=/oracle/ORIG/recovery_area/ORIG/archivelog/2015_06_13/o1_mf_1_8373_bqq4gn4g_.arc RECID=12062 STAMP=882245620
unable to find archived log
archived log thread=1 sequence=8374
Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01245: offline file 4 will be lost if RESETLOGS is done
ORA-01110: data file 4: '/oracle/EDER/oradata/ORIG/datafile/o1_mf_sap_bods_88krp7c9_.dbf'
released channel: t1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 06/13/2015 04:13:43
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 8374 and starting SCN of 9111768484863
RMAN>
RMAN> exit
It's only gone and actually worked! (well 99% worked - the very last archivelog is missing - but in this case i don't need that - it only contains 5 minutes of data and nothing related to the schema in question as no-one was working on it at 04:25->04:30 in the morning (times for this from the alert log on the original database).
So now i just need to open the db and extract the stuff.
To open the db i have to offline drop all the files that i didn't restore otherwise it won't let me open the db - to do that i run some SQL to generate me some SQL
SQL> select 'alter database datafile '||file#||' offline drop ;'
2 from v$recover_file where online_status='OFFLINE'
3 /
'ALTERDATABASEDATAFILE'||FILE#||'OFFLINEDROP;'
--------------------------------------------------------------------------------
alter database datafile 4 offline drop ;
.....
alter database datafile 55 offline drop ;
then run it
SQL> alter database datafile 4 offline drop ;
Database altered.
etc etc
Now i can open the db
SQL> alter database open resetlogs;
Database altered.
Hooray!
Now i just have to export the problem schema
expdp / schemas=apex_workspace_er dumpfile=miracle.dmp reuse_dumpfiles=y
Export: Release 11.2.0.4.0 - Production on Sat Jun 13 09:46:10 2015
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 option
Starting "OSAORACLE"."SYS_EXPORT_SCHEMA_01": /******** schemas=apex_workspace_er dumpfile=miracle.dmp reuse_dumpfiles=y
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1024 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/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
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
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "APEX_WORKSPACE_ER"."APEX_MANUAL_ADJUSTMENTS_TMP" 17.64 KB 2 rows
. . exported "APEX_WORKSPACE_ER"."APEX_GENERIC_UPLOAD_DATA_TMP" 24.50 KB 8 rows
. . exported "APEX_WORKSPACE_ER"."APEX_REF_ADJUSTMENT_KPI_SUBTYP" 5.648 KB 6 rows
. . exported "APEX_WORKSPACE_ER"."APEX_ROLE" 8.390 KB 10 rows
. . exported "APEX_WORKSPACE_ER"."APEX_STEERING_XGME_TMP" 11.78 KB 40 rows
. . exported "APEX_WORKSPACE_ER"."APEX_USER" 8.726 KB 34 rows
. . exported "APEX_WORKSPACE_ER"."APEX_USER_ROLE" 12.89 KB 96 rows
Master table "OSAORACLE"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for OSAORACLE.SYS_EXPORT_SCHEMA_01 is:
/oracle/11.2.0.4.0.DB/rdbms/log/miracle.dmp
Job "OSAORACLE"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Jun 13 09:46:42 2015 elapsed 0 00:00:25
So that's all good.
Now copy it over to the original machine and import it in (dropping the bad tablespace first) - the import ran but with loads of errors as i forgot to create the tablespace again - see log here
impdp / directory=tmp dumpfile=miracle.dmp
Import: Release 11.2.0.4.0 - Production on Sat Jun 13 09:52:12 2015
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 option
Master table "OSAORACLE"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "OSAORACLE"."SYS_IMPORT_FULL_01": /******** directory=tmp dumpfile=miracle.dmp
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"APEX_WORKSPACE_ER" 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/TABLESPACE_QUOTA
ORA-39083: Object type TABLESPACE_QUOTA failed to create with error:
ORA-00959: tablespace 'APEX_WORKSPACE_ER' does not exist
Failing sql is:
DECLARE TEMP_COUNT NUMBER; SQLSTR VARCHAR2(200); BEGIN SQLSTR := 'ALTER USER "APEX_WORKSPACE_ER" QUOTA UNLIMITED ON "APEX_WORKSPACE_ER"'; EXECUTE IMMEDIATE SQLSTR;EXCEPTION WHEN OTHERS THEN IF SQLCODE = -30041 THEN SQLSTR := 'SELECT COUNT(*) FROM USER_TABLESPACES WHERE TABLESPACE_NAME = ''APEX_WORKSPACE_ER'' AND CONTENTS = ''TEMPORARY''
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE:"APEX_WORKSPACE_ER"."APEX_ROLE" failed to create with error:
ORA-00959: tablespace 'APEX_WORKSPACE_ER' does not exist
Failing sql is:
CREATE TABLE "APEX_WORKSPACE_ER"."APEX_ROLE" ("ROLE_SID" NUMBER(12,0) NOT NULL ENABLE, "ROLE_NAME" VARCHAR2(30 CHAR) NOT NULL ENABLE, "ROLE_DESCRIPTION" VARCHAR2(100 CHAR) NOT NULL ENABLE, "INSERT_TS" DATE NOT NULL ENABLE, "INSERT_USER_KID" VARCHAR2(30 CHAR) NOT NULL ENABLE, "DELETE_TS" DATE NOT NULL ENABLE, "UPDATE_USER_KID" VARCHAR2(30 CHAR)) SE
ORA-39083: Object type TABLE:"APEX_WORKSPACE_ER"."APEX_USER" failed to create with error:
ORA-00959: tablespace 'APEX_WORKSPACE_ER' does not exist
Failing sql is:
CREATE TABLE "APEX_WORKSPACE_ER"."APEX_USER" ("USER_KID" VARCHAR2(30 CHAR) NOT NULL ENABLE, "USER_NAME" VARCHAR2(100 CHAR) NOT NULL ENABLE, "INSERT_TS" DATE NOT NULL ENABLE, "INSERT_USER_KID" VARCHAR2(30 CHAR) NOT NULL ENABLE, "DELETE_TS" DATE NOT NULL ENABLE, "UPDATE_USER_KID" VARCHAR2(30 CHAR)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 IN
ORA-39083: Object type TABLE:"APEX_WORKSPACE_ER"."APEX_USER_ROLE" failed to create with error:
ORA-00959: tablespace 'APEX_WORKSPACE_ER' does not exist
Failing sql is:
CREATE TABLE "APEX_WORKSPACE_ER"."APEX_USER_ROLE" ("USER_KID" VARCHAR2(30 CHAR) NOT NULL ENABLE, "ROLE_SID" NUMBER(12,0) NOT NULL ENABLE, "VALID_FROM" DATE NOT NULL ENABLE, "VALID_TO" DATE NOT NULL ENABLE, "INSERT_TS" DATE NOT NULL ENABLE, "INSERT_USER_KID" VARCHAR2(30 CHAR) NOT NULL ENABLE, "DELETE_TS" DATE NOT NULL ENABLE, "UPDATE_USER_KID"
ORA-39083: Object type TABLE:"APEX_WORKSPACE_ER"."APEX_GENERIC_UPLOAD_DATA_TMP" failed to create with error:
ORA-00959: tablespace 'APEX_WORKSPACE_ER' does not exist
Failing sql is:
CREATE TABLE "APEX_WORKSPACE_ER"."APEX_GENERIC_UPLOAD_DATA_TMP" ("DATA_ITEM_ID" NUMBER(22,0) NOT NULL ENABLE, "TRADE_SID" NUMBER(12,0), "SRC_SYS_TRADE_CODE" NUMBER(22,0), "BOOK_SID" NUMBER(12,0), "BOOK_NAME" VARCHAR2(200 CHAR), "COUNTERPARTY_SID" NUMBER(12,0), "MDM_COUNTERPARTY_ID" NUMBER(12,0), "DIRECTION_TYPE" VARCHAR2(5 CHAR)
ORA-39083: Object type TABLE:"APEX_WORKSPACE_ER"."APEX_REF_ADJUSTMENT_KPI_SUBTYP" failed to create with error:
ORA-00959: tablespace 'APEX_WORKSPACE_ER' does not exist
Failing sql is:
CREATE TABLE "APEX_WORKSPACE_ER"."APEX_REF_ADJUSTMENT_KPI_SUBTYP" ("KPI_TYPE" VARCHAR2(100 CHAR), "KPI_SUBTYPE" VARCHAR2(100 CHAR)) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST
ORA-39083: Object type TABLE:"APEX_WORKSPACE_ER"."APEX_MANUAL_ADJUSTMENTS_TMP" failed to create with error:
ORA-00959: tablespace 'APEX_WORKSPACE_ER' does not exist
Failing sql is:
CREATE TABLE "APEX_WORKSPACE_ER"."APEX_MANUAL_ADJUSTMENTS_TMP" ("ADJUSTMENT_ID" NUMBER(12,0) NOT NULL ENABLE, "ADJUSTMENT_TYPE" VARCHAR2(20 CHAR) NOT NULL ENABLE, "IS_ACTIVE_YN" VARCHAR2(1 CHAR), "ADJUSTMENT_SCOPE" VARCHAR2(20 CHAR) NOT NULL ENABLE, "VALID_FROM" DATE NOT NULL ENABLE, "VALID_TO" DATE NOT NULL ENABLE, "VALUATION_VE
ORA-39083: Object type TABLE:"APEX_WORKSPACE_ER"."APEX_STEERING_XGME_TMP" failed to create with error:
ORA-00959: tablespace 'APEX_WORKSPACE_ER' does not exist
Failing sql is:
CREATE TABLE "APEX_WORKSPACE_ER"."APEX_STEERING_XGME_TMP" ("REFERENCE" VARCHAR2(30 CHAR) NOT NULL ENABLE, "DEAL_NUMBER" NUMBER(12,0) NOT NULL ENABLE, "PORTFOLIO" VARCHAR2(30 CHAR) NOT NULL ENABLE, "SOURCE_SYSTEM" VARCHAR2(30 CHAR) NOT NULL ENABLE, "DEAL_GROUP" VARCHAR2(30 CHAR) NOT NULL ENABLE, "FIRM_PR" VARCHAR2(30 CHAR) NOT NULL ENA
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
ORA-39112: Dependent object type OBJECT_GRANT:"APEX_WORKSPACE_ER" skipped, base object type TABLE:"APEX_WORKSPACE_ER"."APEX_ROLE" creation failed
ORA-39112: Dependent object type OBJECT_GRANT:"APEX_WORKSPACE_ER" skipped, base object type TABLE:"APEX_WORKSPACE_ER"."APEX_USER" creation failed
ORA-39112: Dependent object type OBJECT_GRANT:"APEX_WORKSPACE_ER" skipped, base object type TABLE:"APEX_WORKSPACE_ER"."APEX_USER_ROLE" creation failed
ORA-39112: Dependent object type OBJECT_GRANT:"APEX_WORKSPACE_ER" skipped, base object type TABLE:"APEX_WORKSPACE_ER"."APEX_GENERIC_UPLOAD_DATA_TMP" creation failed
ORA-39112: Dependent object type OBJECT_GRANT:"APEX_WORKSPACE_ER" skipped, base object type TABLE:"APEX_WORKSPACE_ER"."APEX_GENERIC_UPLOAD_DATA_TMP" creation failed
ORA-39112: Dependent object type OBJECT_GRANT:"APEX_WORKSPACE_ER" skipped, base object type TABLE:"APEX_WORKSPACE_ER"."APEX_GENERIC_UPLOAD_DATA_TMP" creation failed
ORA-39112: Dependent object type OBJECT_GRANT:"APEX_WORKSPACE_ER" skipped, base object type TABLE:"APEX_WORKSPACE_ER"."APEX_GENERIC_UPLOAD_DATA_TMP" creation failed
ORA-39112: Dependent object type OBJECT_GRANT:"APEX_WORKSPACE_ER" skipped, base object type TABLE:"APEX_WORKSPACE_ER"."APEX_GENERIC_UPLOAD_DATA_TMP" creation failed
ORA-39112: Dependent object type OBJECT_GRANT:"APEX_WORKSPACE_ER" skipped, base object type TABLE:"APEX_WORKSPACE_ER"."APEX_MANUAL_ADJUSTMENTS_TMP" creation failed
ORA-39112: Dependent object type OBJECT_GRANT:"APEX_WORKSPACE_ER" skipped, base object type TABLE:"APEX_WORKSPACE_ER"."APEX_MANUAL_ADJUSTMENTS_TMP" creation failed
ORA-39112: Dependent object type OBJECT_GRANT:"APEX_WORKSPACE_ER" skipped, base object type TABLE:"APEX_WORKSPACE_ER"."APEX_MANUAL_ADJUSTMENTS_TMP" creation failed
ORA-39112: Dependent object type OBJECT_GRANT:"APEX_WORKSPACE_ER" skipped, base object type TABLE:"APEX_WORKSPACE_ER"."APEX_MANUAL_ADJUSTMENTS_TMP" creation failed
ORA-39112: Dependent object type OBJECT_GRANT:"APEX_WORKSPACE_ER" skipped, base object type TABLE:"APEX_WORKSPACE_ER"."APEX_MANUAL_ADJUSTMENTS_TMP" creation failed
TABLE:"APEX_WORKSPACE_ER"."APEX_REF_ADJUSTMENT_KPI_SUBTYP" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"APEX_WORKSPACE_ER"."APEX_REF_ADJUSTMENT_KPI_SUBTYP" creation failed
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"APEX_WORKSPACE_ER"."APEX_REF_ADJUSTMENT_KPI_SUBTYP" creation failed
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
ORA-39112: Dependent object type INDEX:"APEX_WORKSPACE_ER"."APEX_ROLE_PK" skipped, base object type TABLE:"APEX_WORKSPACE_ER"."APEX_ROLE" creation failed
ORA-39112: Dependent object type INDEX:"APEX_WORKSPACE_ER"."APEX_USER_PK" skipped, base object type TABLE:"APEX_WORKSPACE_ER"."APEX_USER" creation failed
ORA-39112: Dependent object type INDEX:"APEX_WORKSPACE_ER"."APEX_USER_ROLE_PK" skipped, base object type TABLE:"APEX_WORKSPACE_ER"."APEX_USER_ROLE" creation failed
ORA-39112: Dependent object type INDEX:"APEX_WORKSPACE_ER"."APEX_GEN_UPLOAD_DATA_TMP_PK" skipped, base object type TABLE:"APEX_WORKSPACE_ER"."APEX_GENERIC_UPLOAD_DATA_TMP" creation failed
ORA-39112: Dependent object type INDEX:"APEX_WORKSPACE_ER"."APEX_MANUAL_ADJUSTMENTS_TMP_PK" skipped, base object type TABLE:"APEX_WORKSPACE_ER"."APEX_MANUAL_ADJUSTMENTS_TMP" creation failed
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ORA-39112: Dependent object type CONSTRAINT:"APEX_WORKSPACE_ER"."APEX_GEN_UPLOAD_DATA_TMP_PK" skipped, base object type TABLE:"APEX_WORKSPACE_ER"."APEX_GENERIC_UPLOAD_DATA_TMP" creation failed
ORA-39112: Dependent object type CONSTRAINT:"APEX_WORKSPACE_ER"."APEX_ROLE_PK" skipped, base object type TABLE:"APEX_WORKSPACE_ER"."APEX_ROLE" creation failed
ORA-39112: Dependent object type CONSTRAINT:"APEX_WORKSPACE_ER"."APEX_USER_PK" skipped, base object type TABLE:"APEX_WORKSPACE_ER"."APEX_USER" creation failed
ORA-39112: Dependent object type CONSTRAINT:"APEX_WORKSPACE_ER"."APEX_USER_ROLE_PK" skipped, base object type TABLE:"APEX_WORKSPACE_ER"."APEX_USER_ROLE" creation failed
ORA-39112: Dependent object type CONSTRAINT:"APEX_WORKSPACE_ER"."APEX_MANUAL_ADJUSTMENTS_TMP_PK" skipped, base object type TABLE:"APEX_WORKSPACE_ER"."APEX_MANUAL_ADJUSTMENTS_TMP" creation failed
ORA-39112: Dependent object type CONSTRAINT:"APEX_WORKSPACE_ER"."SYS_C00157591" skipped, base object type TABLE:"APEX_WORKSPACE_ER"."APEX_STEERING_XGME_TMP" creation failed
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
ORA-39112: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"APEX_WORKSPACE_ER"."APEX_ROLE_PK" creation failed
ORA-39112: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"APEX_WORKSPACE_ER"."APEX_USER_PK" creation failed
ORA-39112: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"APEX_WORKSPACE_ER"."APEX_USER_ROLE_PK" creation failed
ORA-39112: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"APEX_WORKSPACE_ER"."APEX_GEN_UPLOAD_DATA_TMP_PK" creation failed
ORA-39112: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"APEX_WORKSPACE_ER"."APEX_MANUAL_ADJUSTMENTS_TMP_PK" creation failed
ORA-39112: Dependent object type INDEX_STATISTICS skipped, base object type TABLE:"APEX_WORKSPACE_ER"."APEX_STEERING_XGME_TMP" creation failed
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
ORA-39082: Object type PACKAGE_BODY:"APEX_WORKSPACE_ER"."APEX_AUTHORIZATION_UTIL" created with compilation warnings
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
ORA-39112: Dependent object type REF_CONSTRAINT:"APEX_WORKSPACE_ER"."APEX_USER_ROLE_APEX_USER_FK" skipped, base object type TABLE:"APEX_WORKSPACE_ER"."APEX_USER_ROLE" creation failed
ORA-39112: Dependent object type REF_CONSTRAINT:"APEX_WORKSPACE_ER"."APEX_USER_ROLE_APEX_ROLE_FK" skipped, base object type TABLE:"APEX_WORKSPACE_ER"."APEX_USER_ROLE" creation failed
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
ORA-39112: Dependent object type TRIGGER:"APEX_WORKSPACE_ER"."MANUAL_ADJUSTMENTS_TMP_INS" skipped, base object type TABLE:"APEX_WORKSPACE_ER"."APEX_MANUAL_ADJUSTMENTS_TMP" creation failed
ORA-39112: Dependent object type TRIGGER:"APEX_WORKSPACE_ER"."GENERIC_UPLOAD_DATA_TMP_INS" skipped, base object type TABLE:"APEX_WORKSPACE_ER"."APEX_GENERIC_UPLOAD_DATA_TMP" creation failed
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"APEX_WORKSPACE_ER"."APEX_ROLE" creation failed
ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"APEX_WORKSPACE_ER"."APEX_USER" creation failed
ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"APEX_WORKSPACE_ER"."APEX_USER_ROLE" creation failed
ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"APEX_WORKSPACE_ER"."APEX_GENERIC_UPLOAD_DATA_TMP" creation failed
ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"APEX_WORKSPACE_ER"."APEX_REF_ADJUSTMENT_KPI_SUBTYP" creation failed
ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"APEX_WORKSPACE_ER"."APEX_MANUAL_ADJUSTMENTS_TMP" creation failed
ORA-39112: Dependent object type TABLE_STATISTICS skipped, base object type TABLE:"APEX_WORKSPACE_ER"."APEX_STEERING_XGME_TMP" creation failed
Job "OSAORACLE"."SYS_IMPORT_FULL_01" completed with 148 error(s) at Sat Jun 13 09:52:16 2015 elapsed 0 00:00:03
So now i create the tablespace (note OMF so no messy file names etc....)
SQL> create tablespace APEX_WORKSPACE_ER;
Tablespace created.
Now i do the import again which throws a lot of ignorable errors as the initial import only partially worked
impdp / directory=tmp dumpfile=miracle.dmp
Import: Release 11.2.0.4.0 - Production on Sat Jun 13 09:52:39 2015
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 option
Master table "OSAORACLE"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "OSAORACLE"."SYS_IMPORT_FULL_01": /******** directory=tmp dumpfile=miracle.dmp
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"APEX_WORKSPACE_ER" 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/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
ORA-31684: Object type SEQUENCE:"APEX_WORKSPACE_ER"."APEX_STEERING_XGME_TMP_SEQ" already exists
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "APEX_WORKSPACE_ER"."APEX_MANUAL_ADJUSTMENTS_TMP" 17.64 KB 2 rows
. . imported "APEX_WORKSPACE_ER"."APEX_GENERIC_UPLOAD_DATA_TMP" 24.50 KB 8 rows
. . imported "APEX_WORKSPACE_ER"."APEX_REF_ADJUSTMENT_KPI_SUBTYP" 5.648 KB 6 rows
. . imported "APEX_WORKSPACE_ER"."APEX_ROLE" 8.390 KB 10 rows
. . imported "APEX_WORKSPACE_ER"."APEX_STEERING_XGME_TMP" 11.78 KB 40 rows
. . imported "APEX_WORKSPACE_ER"."APEX_USER" 8.726 KB 34 rows
. . imported "APEX_WORKSPACE_ER"."APEX_USER_ROLE" 12.89 KB 96 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
ORA-31684: Object type PACKAGE:"APEX_WORKSPACE_ER"."APEX_AUTHORIZATION_UTIL" already exists
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
ORA-31684: Object type FUNCTION:"APEX_WORKSPACE_ER"."F_CHECK_DATE_FORMAT" already exists
ORA-31684: Object type FUNCTION:"APEX_WORKSPACE_ER"."F_REGISTER_TARGET" already exists
ORA-31684: Object type FUNCTION:"APEX_WORKSPACE_ER"."F_START_LOG" already exists
ORA-31684: Object type FUNCTION:"APEX_WORKSPACE_ER"."F_STOP_LOG" already exists
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
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
Processing object type SCHEMA_EXPORT/VIEW/VIEW
ORA-31684: Object type VIEW:"APEX_WORKSPACE_ER"."APEX_BOOK_INST" already exists
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
ORA-31684: Object type PACKAGE_BODY:"APEX_WORKSPACE_ER"."APEX_AUTHORIZATION_UTIL" already exists
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "OSAORACLE"."SYS_IMPORT_FULL_01" completed with 9 error(s) at Sat Jun 13 09:52:43 2015 elapsed 0 00:00:03
And everything is back as it should be.
Martini's all round i think.......
There is a bug somewhere with flashback/controlfiles/incarnations - it's a specific set of circumstances that seem to create it and we still haven't resolved what that is - but anyway the technique above may help someone out of an hole - and anyway its quite an interesting battle......
Comments
Post a Comment