Showing posts with label 10gr2. Show all posts
Showing posts with label 10gr2. Show all posts

2008/02/15

Good article on MERGE statement enhancements in 10g

I don't get to use MERGE too often, but when I need it, it is usually something for something I need to Google the syntax for.

Here's a great post on using MERGE in Oracle 10g with plenty of syntax examples for each.

BTW, thanks Oracle for finally adding support for UPDATE-only or INSERT-only operations!

2007/09/10

What Do All 10g Flashback Features Rely on and what are their Limitations?

There's a great MetaLink article on Flashback - check it out!

What Do All 10g Flashback Features Rely on and what are their Limitations?
Doc ID: Note:435998.1
  • What Do All Flashback Features Rely on ?
  • Differences Between New Oracle 10g Flashback Technologies
  • Limitations of Flashback Database
  • Limitations and Restrictions on Flashback Drop
  • Limitations and Restrictions on Flashback Tables
Here are two gems that were news to me:
  • To be able to flashback a table, you must enable row movement for the table
  • Because DML operations are used to bring the table back to its former state, the ROWIDs in the table change. As a result, Flashback Table is not a viable option for applications that depend on the table’s ROWIDs to remain constant
Note that altering a table to enable row movement will cause underlying PL/SQL to become invalid!!!

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/07/12

An insanely great trick to recover a cold backup database that starts needing recovery

Why would a 10g database restored from cold backup need recovery? That's messed up.

So for the second time now, we took a small (~50GB) database the other day, shut it down using "shutdown immediate", started it back up, switched logs, and then shut it down again.

We then cloned it (took a cold backup of it, and brought it up with a different name), and low-and-behold it complains that it needs recovery. WTF?

Here's the upside though: There's a way to easily recover from this and bring the database up.

Check out MetaLink Note: 175015.1 - it's somewhat cryptic, but essentially all you need to do is take the most recent REDO log and copy it to the archivelog dest location and name it to whatever archivelog name Oracle is suggesting in the recover database command, then just hit (Return) to accept the filename and begin the recovery process. Next thing you know, boom, everything is up and running.

Now to the question of why this is happening...

2007/06/14

Upgrading EM Grid Control from 10.2.0.1 to 10.2.0.3

Okay, so I finally bit the bullet and upgraded EM Grid Control from the initial install version of 10.2.0.1 to 10.2.0.3 and upgraded the EM repository (cleverly named EMREP) from 10.1.0.5 to 10.2.0.1 .

Here's a few observations about the upgrade:
  1. The EMREP page in GC still showed the old 10.1 home instead of the new 10.2 home.
    1. This stems from the fact that I had 2 different ORACLE_HOME installs: /orabase/product/ora10g and /orabase/OracleHomes/db10g.
    2. Upgrading meant moving from one home to another and GC did not pick up on this change
    3. Solution: Remove EMREP from monitoring and re-add it (BTW, refreshing the host configuration did not work!)
  2. It took much longer than I had planned.
    1. As part of the 10.2.0.3 patch, a repository upgrade was necessary - this step alone took about 45 minutes - yikes!
  3. There are about 8 new security policies that come with GC 10.2.0.3 ("Password Complexity Verification Function Usage", etc.)
    1. Once you've upgraded and logged in to GC, you'll see a whole bunch of new security policy warnings.
    2. Sadly, these have to be fixed by going to your Monitoring Templates (you do use templates right??) and using the "Add Policies To Template" button to add the offending policies and then set them appropriately.
One last thing:
  • They finally fixed that god-forsaken bug that kept the Database->Administration->Tablespaces page from rendering on 9i databases in under an hour! Jeez, that was annoying as hell! :-)

2007/05/24

D'oh! of the Day: Creating a tablespace with 16k blocksize in a database with 8k blocksize default

So, this should have been a no-brainer.

I'm trying to improve the performance of a DSS database (10gR2 with ASM), so I figure, "Hey, i'll create a new tablespace with a 16k blocksize and reorg 2 big tables into it to see if that helps."
SQL> CREATE SMALLFILE TABLESPACE "DATA_16K"
DATAFILE'+DGROUP1(DATAFILE)' SIZE 1M
LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
BLOCKSIZE 16K;
ORA-27035: logical block size is invalid
Huh??

Here's the solution (the D'oh! moment): I never created a buffer pool for 16k blocks!
SQL> select id,name,block_size, current_size,buffers from v$buffer_pool;

ID|NAME |BLOCK_SIZE|CURRENT_SIZE| BUFFERS
----------|--------------------|----------|------------|----------
3|DEFAULT | 8192| 2560| 317439

SQL> alter system set db_16K_cache_size=2m scope=both;
System altered.

SQL> select id,name,block_size, current_size,buffers from v$buffer_pool;

ID|NAME |BLOCK_SIZE|CURRENT_SIZE| BUFFERS
----------|--------------------|----------|------------|----------
3|DEFAULT | 8192| 2528| 313471
7|DEFAULT | 16384| 32| 2014

SQL> CREATE SMALLFILE TABLESPACE "DATA_16K"
DATAFILE'+DGROUP1(DATAFILE)' SIZE 1M
LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO
BLOCKSIZE 16K;

Tablespace created.
Yes, i'm easily amused some days.

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