Showing posts with label help. Show all posts
Showing posts with label help. Show all posts

2007/08/27

Problem: ORA-10631 errors encountered during "SHRINK SPACE" operation

OEM was nice the other day and offered some Segment Adviser Recommendations to shrink a few tables that had become fragmented. However, when I ran the shrink job, it failed.

PROBLEM:
  • "ALTER TABLE my_test_table SHRINK SPACE;" results in "ORA-10631: SHRINK clause should not be specified for this object"
SOLUTION:
  • Tables that have function-based indexes cannot be compacted using the SHRINK clause
  • Apparently, someone forgot to inform OEM of this fact (thanks the bogus recommendations! :-)
  • See MetaLink Bug No.:5968539
Here's a SQL statement that shows all the tables in a given schema and whether they are candidates for the SHRINK clause:

SELECT   dt.owner, dt.table_name,
         (CASE
            WHEN NVL(ind.cnt, 0) < 1 THEN 'Y'
            ELSE 'N'
         END) AS can_shrink
    FROM dba_tables dt,
         (SELECT   table_name, COUNT(*) cnt
              FROM dba_indexes di
             WHERE index_type LIKE 'FUNCTION-BASED%'
          GROUP BY table_name) ind
   WHERE dt.table_name = ind.table_name(+)
     AND dt.table_name NOT LIKE 'AQ$%'
     AND dt.table_name NOT LIKE 'BIN$%'
     AND dt.owner = 'BRENT'
ORDER BY 1, 2;

Problem: Unable to copy data files when 10g database is in hot backup mode

So, our customers are FINALLY making the transition to 10gR2 databases this summer and we're having all kinds of great "Oops!" moments. Here's a good example:

PROBLEM:
  • A tablespace's physical files cannot be copied even when the tablespace is in hot backup mode.
  • We created a pretty standard 10.2.0.3 database on an AIX 5L box, and when we put the tablespaces into hot backup mode, we were unable to copy the data files to a backup location as AIX kept reporting that they were locked.
Of course our first reaction was "HUH?!", and after much head scratching, sysadmin pestering, and SAR opening, we finally got the solution from Oracle.

SOLUTION:
  • There is a bug with the FILESYSTEMIO_OPTIONS init parameter in 10g that prevents the "setall" value from working correctly.
  • We were able to copy the data files once we made the following change: filesystemio_options=async
  • See MetaLink Note:434385.1

2007/06/20

Issues with RMAN on AIX using TDP for Oracle

So our group has thankfully decided to go with RMAN for database backups, and you know i'm first in line to take this task on.

Here's my setup at a high level:
  • 10gR2 database (rmandb - snappy name huh? :)
  • IBM's TDP 5.3 for Oracle
  • AIX 5.2
Steps:
  1. My internal IT department setup my TSM domain, etc.
  2. Sysadmin installed the TDP fileset and then generated the password file for me
  3. I used the TSM Quick Start Guide to get all of my config files and directories setup
  4. I used OEM GC R3 to set the RMAN preferences, and to setup the Tape parameters: ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)
Once all of this was done and "$ tdpoconf SHOWENVironment" looked good, i went for my first test drive.

I logged into RMAN, connected to target, and then ran a connection test:
run {
allocate channel oem_sbt_backup type 'sbt_tape' format '%U' parms 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
release channel oem_sbt_backup;
}
And it worked fine. Woot!

Next, I used the "Test Backup" button on the Backup Setting OEM page. It's a simple test consisting of:

run {
allocate channel oem_sbt_backup type 'sbt_tape' format '%U' parms 'ENV=(TDPO_OPTFILE=/usr/tivoli/tsm/client/oracle/bin64/tdpo.opt)';
backup as BACKUPSET current controlfile tag '05252007020711';
restore controlfile validate from tag '05252007020711';
release channel oem_sbt_backup;
}
And... it fails with this lovely error output:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_SBT_TAPE_1 channel at 05/22/2007 16:28:09
ORA-19506: failed to create sequential file, name="0aiiceoi_1_2", parms=""
ORA-27028: skgfqcre: sbtbackup returned error
ORA-19511: Error received from media manager layer, error text:
ANS0231E (RC2025) The management class name was not found.
Nice huh?? What the heck is "ANS0231E (RC2025) The management class name was not found."?

If you think that's depressing, try entering "ANS0231E" into Google. There is less than 30 results worldwide! I hate when that happens...

After working with IS and IBM for 3 weeks (3 weeks!!!), we finally worked our way up to a SEV-1 analyst on a conference call and he figures it out.

Wait for it...

My tdpo.opt file had no stanza for "TDPO_MGMT_CLASS_2" (or _3, or _4) because I was only allocating one channel (these values are only used when more than one channel is opened to TSM, otherwise, it used the channel appropriately named "DEFAULT").

Here's the gotcha: I setup RMAN to do 'duplex' copies, e.g.: create 2 copies of all datafiles and 2 copies of all control files.

The problem is RMAN opens 2 channels when duplex is set!!!

Bottom Line: (I had IBM add this to their KB)
If you receive the dreaded "ANS0231E (RC2025) The management class name was not found." error, the first question you need to ask yourself is, "Am i using duplex copies", if so, i'll bet you don't have a value for "TDPO_MGMT_CLASS_2" in your tdpo.opt file.

This was a hard lesson learned... :-)

2007/05/14

Having issues using DBCA with ASM on 10gR2?

So, i'm feeling particularly pleased last week when I finally get ASM up and running last week on a new reporting server. It's an IBM AIX box, with a 600GB disk group (DGROUP1), using the usual "+ASM" instance name. Also note that I used my 10gR2 (10.2.0.1 EE) home as my ASM home, since i'm only using ASM for a single database on this box.

Once i verify that ASM is working and that it's hooked into EM Grid Control, I cd to my Oracle 10gR2 home, setup my environment by using oraenv, and then run dbca.

Everything is going great until I get to the storage page, where I select "ASM" and click Next. Within a couple of seconds, I see this gem of an error message:
"DBCA could not startup the ASM instance configured on this node. To proceed with the database creation using ASM, ASM instance needs to be up and running. Do you want to recreate ASM instance on this node?"
Hmm. Not what I wanted to see, that's for sure. :-(

So it's off to Metalink to see what Oracle has to say. In no short order, I find about 6 separate issues (some with their own accompanying bug numbers) all reporting the exact same issue, and all with existing ASM
instances.

The only note that had a real work-around involved changing some OAS (Oracle Advanced Security) settings as ODBC did not work well with them. That's all well-and-good, but i'm not using OAS. My sqlnet.ora file has one line!

Next, I did what all good DBA's do when faced with this type of situation: go to Google. Unfortunately, I was still unable to find a solution. The OTN forums were not much help either. Oddly enough, some of my searches even resulted in googlewhacks.

The one interesting result I found on Metalink said something like, "I managed to use dbca to setup a database with ASM before, but now that I try it a second time, it does not work!" Interesting. To me, that meant there HAD TO BE a solution here. There's just no way that dbca is unable to create a database with ASM. Common!!

Here's what i'm doing: I have 2 terminal windows open - one was running dbca, the other was my working terminal. The working one being the terminal I used to change listener settings, tnsping, etc.

After 2 hours of a hapless tango between clicking Next> and seeing the same error, then changing settings ... , I finally throw in the towel at 4:30 and close the term window running dbca.

Just for fun, I run dbca from the OTHER term window where i've been continually futzing with settings. I get to Storage, select ASM and click Next... NOW IT WORKS!!! WTF?!

That's why it works for some people sometimes and then stops the next time. We've got some env variable or something set wrong!

SOLUTION: If you get this error, check your settings, open another window, re-run oraenv, keep trying!

As soon as dbca completed, I catpured my 'env' output - here's the items of interest:
  • ORACLE_SID=+ASM
  • ORACLE_HOME=/orabase/product/ora10g
  • LIBPATH=/orabase/product/ora10g/lib:/orabase/product/ora10g/lib32:/usr/lib:/lib
  • JAVA_HOME=/usr/java5/bin