10g Impdp ORA-00959 $deleted$ Fix

Ran into a problem with an Import Datapump table restore. This is the error message:

Import: Release 10.2.0.4.0 - 64bit Production on Monday, 06 December, 2010 17:57:27 Copyright (c) 2003, 2007, Oracle. All rights reserved. ;;; Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP and Real Application Testing options Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_FULL_01": parfile=hm_camp_m010.par Processing object type TABLE_EXPORT/TABLE/TABLE ORA-39083: Object type TABLE failed to create with error: ORA-00959: tablespace '_$deleted$16$0' does not exist

Basically, bombed because the tablespace _$deleted$16$0 doesn't exist. Looked it up on MetaLink and found this document:

Import Partition Table Fails With Ora-00959: Tablespace '_$Deleted$0$0' [ID 798142.1]

There is an open unpublished bug where basic problem is that a tablespace is renamed to one that used to exist on the database. Here are the steps that will cause this problem.

create tablespace aa
create tablespace bb
drop tablespace aa
rename bb to aa

Then why it occurs is because when a tablespace is dropped, its ts$ entry is set to an invalid state, but it is not deleted. When a rename is done, if the target name already has a ts$ entry, then during the rename, because we cannot have two ts$ entries with the same name, the _$deleted$ construct is introduced. The change is not taken care of properly, and is flushed to other dictionary tables causing this problem.

The note didn't have good instructions to fix my issue. So, I'm recording what I did here:

1. Run impdp with this parameter SQLFILE=dir:name to pull the DDL for the object.
2. Modify and execute the SQL created in step 1.
3. Run impdp with this parameter CONTENT=DATA_ONLY to bypass the Metadata portion of impdp steps.

In step 2 you take the create table SQL and modify it by creating the table in a tablespace that does exist.

Hopefully, this will help you.

Oracle ASM Header Backup

One of my DBA friends shared this information with me which I reviewed and researched. Kudos to my friend.

A situation popped up recently for one team where a drive went into predictive failure, and a controller hung, corrupting the ASM headers upon reboot. This resulted them having to completely rebuild the disk groups and restore the database from tape. If the team would have had a backup of their ASM headers, all of that wouldn’t have been necessary.

ASM metadata corruption is a clear and present danger that we as DBAs have to recognize and prepare for. It is nothing to be embarrassed about, and nothing to fear, as long as you are ready for it.

Oracle 11g

md_backup

md_backup is ran from the ASMCMD> prompt. It very simply creates a file that is a backup of your metadata, for all of your disk groups, or just a few if you want to specify. By default, it writes to the current directory and backs up all of the mounted disk groups header files.

Arguments: -b and –g

-b Specifies a location to store the backup file

-g Specifies the disk group to back up

Example: ASMCMD> md_backup –b /mydir/somebackupfile –g DG1 –g DG3

This will backup disk groups DG1 and DG3 to the file /mydir/somebackupfile.

md_restore

md_restore is a bit more involved than md_backup. As you may have guessed, it restores previously backed up metadata to disk groups you specifiy.

Arguments: -b –i –t –f –g –o

-b Specifies the backup file to read

-i Ignores errors. (Normally, if md_restore hits an error, it stops. This argument suppressed that.

-t Specifies the type of disk group to be created/restored. “full” creates a disk group and restores the metadata. “nodg” restores metadata only, and “newdg” creates a new disk group with a different name using in conjunction with the –o argument.

-o Renames the disk group

-f writes the SQL commands to be performed in the restore to a file instead of executing them (sql_script_file).

-g selects the disk groups to be restored. If none are specified, it restores all of them.

Example: ASMCMD> md_restore –t full –g DG1 –i backup_file

This will restore disk group DG1 using the specified file.

Example: ASMCMD> md_restore –t nodg –g DG1 –i backup_file

This will restore an existing disk group’s metadata.

Oracle 10g

When implementing ASM backups should not only be made of the data within ASM but the disk headers should also be backed up. Oracle 10g does not provide a way out of the box to do this but Oracle 11g provides two new commands in the asmcmd utility to backup disk headers. The header of a disk is comprised of the first 4096 bytes. While not supported it is possible to use the UNIX dd to read and write these bytes to a backup file.

dd if=device name of=unique header file bs=4096 count=1

That will capture the first 4k block of the ASM file. You would do this for each file, creating a separate backup for each. A simple dd will write it back in to the header, if you need to restore from your backup. Example:

dd if=/dev/rdisk/asmvol1 of=/u01/ora_backup/asmvo1_header.bak bs=4096 count=1

This would capture the first 4k of the given ASM file. Example:

dd if=/u01/ora_backup/asmvol1_header.bak of=/dev/rdisk/asmvol1

This would write in the backed up header back into the file it was backed up from.

It is always a good idea to keep backups of your disk group metadata, because no one can know when the corruption gremlin might strike.