Today's learning: what happens when you try to run the same data pump import twice
Data pump - http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_overview.htm#SUTIL100.
If you do anything in Oracle, you probably know about it, and have probably used it once or twice. If you haven't and you have had the need to move data around, you're probably missing a trick. It's highly flexible, speedy, and very useful.
So, what is data pump? Data pump is used to export and import data from a database. That's it? Yeah I know it sounds dull, but it's the flexibility that it offers you that is really handy. It exports data to Oracle's own format - not CSV or anything infexible, so it can do dependency checking. It allows you to export a full database or to export certain schemas, tables or tablespaces, to export to a specific version of dump file (e.g. from an 11.2 database to a format that 10.2 can read), to import schemas tables or tablespaces from a export file and rename them on the fly whilst also updating references within the database.
I'm not doing it justice, but it's one of the really good things about Oracle DB.
One of the really nice things, is that the user interface (the binaries that you launch at the command line) is just an interface for a bunch of stored procedures within the database that do all of the work. So, it's perfectly possible to start a data pump session, close the UI, and the job will carry on in the database, not even noticing that the UI has disconnected. You can reconnect later to catch up with it.
That is a nice thing. It really is - it's like a built-in "screen" or "nohup". But you have to remember that's how it works.. If you forget, like I did this afternoon, you get a terribly slow & confused import and a terribly confusing set of error messages about objects existing when you know that they really shouldn't because you dropped them only 5 minutes ago.
I started a data pump import. I noticed something I needed to change in the command line and CTRL-C'ed it a couple of times, bringing me back to the shell. I assumed because I didn't see the "Import>" prompt that I'd caught the job before it backgrounded itself. Then started the job again.
It was only after the job had been running much longer and MUCH slower than I anticipated that I checked the V$SESSION_LONGOPS table (http://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_3022.htm#REFRN30227) and spotted two jobs like this that I realised what I'd done:
SYS_IMPORT_SCHEMA_01
SYS_IMPORT_SCHEMA_02
I decided not to bother trying to rescue the situation as I had no idea whether the second job was doing stuff to what the first job had already done, or whether the second job was just re-doing everything. So I used the information from this page (http://blog.oracle48.nl/killing-and-resuming-datapump-expdp-and-impdp-jobs/) to kill off both jobs, and restarted one.
This one ran at the speed I expected.
If you do anything in Oracle, you probably know about it, and have probably used it once or twice. If you haven't and you have had the need to move data around, you're probably missing a trick. It's highly flexible, speedy, and very useful.
So, what is data pump? Data pump is used to export and import data from a database. That's it? Yeah I know it sounds dull, but it's the flexibility that it offers you that is really handy. It exports data to Oracle's own format - not CSV or anything infexible, so it can do dependency checking. It allows you to export a full database or to export certain schemas, tables or tablespaces, to export to a specific version of dump file (e.g. from an 11.2 database to a format that 10.2 can read), to import schemas tables or tablespaces from a export file and rename them on the fly whilst also updating references within the database.
I'm not doing it justice, but it's one of the really good things about Oracle DB.
One of the really nice things, is that the user interface (the binaries that you launch at the command line) is just an interface for a bunch of stored procedures within the database that do all of the work. So, it's perfectly possible to start a data pump session, close the UI, and the job will carry on in the database, not even noticing that the UI has disconnected. You can reconnect later to catch up with it.
That is a nice thing. It really is - it's like a built-in "screen" or "nohup". But you have to remember that's how it works.. If you forget, like I did this afternoon, you get a terribly slow & confused import and a terribly confusing set of error messages about objects existing when you know that they really shouldn't because you dropped them only 5 minutes ago.
I started a data pump import. I noticed something I needed to change in the command line and CTRL-C'ed it a couple of times, bringing me back to the shell. I assumed because I didn't see the "Import>" prompt that I'd caught the job before it backgrounded itself. Then started the job again.
It was only after the job had been running much longer and MUCH slower than I anticipated that I checked the V$SESSION_LONGOPS table (http://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_3022.htm#REFRN30227) and spotted two jobs like this that I realised what I'd done:
SYS_IMPORT_SCHEMA_01
SYS_IMPORT_SCHEMA_02
I decided not to bother trying to rescue the situation as I had no idea whether the second job was doing stuff to what the first job had already done, or whether the second job was just re-doing everything. So I used the information from this page (http://blog.oracle48.nl/killing-and-resuming-datapump-expdp-and-impdp-jobs/) to kill off both jobs, and restarted one.
This one ran at the speed I expected.
Comments
Post a Comment