2

I want to test the restore of erroneously dropped partitions of a subpartitioned table. This restore would consist of three steps:

  1. restore the database (or at least the necessary tablespaces) to the needed point in time to a different space.
  2. extract the dropped partitions from this auxiliary database created from the backup
  3. import the partitions to the original database where they were erroneously dropped

The restore step 1 is not difficulty but I have problems when I test the import/export.

For this I create a schema in a test database where I can simulate the drop and the restore. It is a large database and the restore should be fast, this means parallel import with direct read. Therefore I cannot import in an existing table because it does not allow parallel import. So have to create a new table. Then I do a switch partition to get the imported partitions into the table where I dropped them. I already have a schema with the same name in the test database and I do not want to touch this schema so I create a new one for this test.

I do the following:

  1. I create the necessary tablespaces on the test database.
  2. I create a user named RESTORE_USER on the test database with appropriate grants and tablespace quotas.
  3. I export some partitions from the ORIGINAL_USER.ORIGINAL_TABLE of production database.
DUMPFILE=...
LOGFILE=...
DIRECTORY=...
TABLES=RESTORE_USER.RESTORE_TABLE
CONTENT=METADATA_ONLY
  1. I import this dump file into the test database.
DUMPFILE=...
LOGFILE=...
remap_schema=ORIGINAL_USER:RESTORE_USER
DIRECTORY=...
TABLES=ORIGINAL_USER.ORIGINAL_TABLE
  1. I drop some partitions on the test database
alter table ORIGINAL_USER.ORIGINAL_TABLE drop partition PART_20141101;
alter table ORIGINAL_USER.ORIGINAL_TABLE drop partition PART_20141102;
...

Now I have the structure on the test database.

  1. I export the partitions that I have dropped from the production database
DUMPFILE=...
LOGFILE=...
DIRECTORY=...
PARALLEL=8
TABLES=ORIGINAL_USER.ORIGINAL_TABLE:PART_20141101,ORIGINAL_USER.ORIGINAL_TABLE:PART_20141102,...
  1. I import this on the test database and the problems start.
DUMPFILE=...
LOGFILE=...
remap_table=ORIGINAL_USER.ORIGINAL_TABLE:RESTORE_TABLE
remap_schema=ORIGINAL_USER:RESTORE_USER
DIRECTORY=...
TABLE_EXISTS_ACTION=APPEND
PARALLEL=8
TABLES=ORIGINAL_USER.ORIGINAL_TABLE:PART_20141101,ORIGINAL_USER.ORIGINAL_TABLE:PART_20141102,...

The import of the partitions works fine and was done in parallel as expected. But the creation of the indexes was not possible and the following error messages could be found in the log file:

Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "RESTORE_USER"."RESTORE_TABLE":"PART_20141106"."PART_20141106_01"  1.234 GB 123456 rows
...
...
...
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
ORA-39083: Object type INDEX failed to create with error:
ORA-14024: number of partitions of LOCAL index must equal that of the underlying table
Failing sql is:
CREATE INDEX "RESTORE_USER"."ORIGINAL_INDEX" ON 
"RESTORE_USER"."ORIGINAL_TABLE" ...

The creation of the indexes was not possible because the indexes weren't remapped to the new table names. Does anyone know how I have to parametrize the import that this will work?

I already imported the indexes to an SQL file. And there I also can see that they were not remapped. I do not want to sue the statements from the sql file to create the indexes. I want to solve it by running impdp.

regards guenter

  • I had the following stament in the import: remap_table=ORIGINAL_USER.ORIGINAL_TABLE:RESTORE_USER.RESTORE_TABLE and so the table created was acturally a table with name "RESTORE_USER.RESTORE_TABLE". I changed this. – miracle173 Feb 23 '15 at 15:06
  • 1
    Just a comment... Did you know that in this kind of cases it's usually faster and better to rebuild the indexes than to import/restore them? – Silvarion Jan 23 '17 at 20:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.