Wednesday, March 24, 2010

Table statistics being locked after exporting in 10g

Some unexpected activity was recently encountered while we were exporting data from a 10g version of a database into another database that was version 11g. We were moving data structures without moving the data. After doing so we were unable to analyze the tables in the target system. It turns out this is a common problem.

Table statistics get locked when exporting only the table structures with DataPump. This situation is identified as an issue that occurs with Oracle 10.2. Using DataPump data is not exported or imported if the option CONTENT = METADATA_ONLY is set.

To resolve this there are two options listed on My Oracle Support.
1. After the import unlock the statistics for tables using the command:
execute DBMS_STATS.UNLOCK_TABLE_STATS('owner','table_name');
NOTE the statistics can also be unlocked at the schema level.

2. Do not import table statistics using the option EXCLUDE=TABLE_STATISTICS.

REFERENCES

415081.1, DataPump Import Without Data Locks Table Statistics


Thursday, March 11, 2010

Extra lines in controlfile to trace

The database command 'alter database backup controlfile to trace;' is a commonly used command for DBAs to make a backup of the database controlfile. This tracefile can be used in cloning or other activities to create a new controlfile as part of a fully automated process. Some users, however, have seen an issue with 'alter database backup controlfile to trace;' in an 11g (11.1.0.7 specifically) instance which can cause issues with any such automation.

ISSUE
'alter database backup controlfile to trace;' puts additional header lines in seemingly random locations in the trace file. An example of the line: *** 2010-03-06 14:24:42.720

SOLUTION
The reason for this issue is unknown. However, there is a pretty simple workaround. Rather than issuing only 'alter database backup controlfile to trace;', issue 'alter database backup controlfile to trace as ;' instead. This removes the header information and the issue has not been seen using the more exact syntax.

TIP
If you have any fully automated processes, such as cloning, make sure you fully test them out multiple times before rolling any changes, especially major ones such as database upgrades, to your production instance.