Wednesday May 25, 2016

Transportable Tablespaces - Characters Sets - Same same but different?

All credits go to Don Wolf, an Oracle Advanced Customer Support engineer from Ohio as he dug out this information :-) Thanks Don!

Do database character sets have to match EXACTLY for Transportable Tablespaces?

That sounds like a simple question. When you look into our big slide deck the answer will be a straight "Yes". No doubts. Regardless if you would like to do Transportable Tablespaces or Full Transportable Export/Import your sources and your target's database character sets must be equal. Otherwise Data Pump won't allow you to process the meta data import.

But Don was wondering about slightly differing information in MOS notes and the documentation.
And asked me if I can clarify things. I couldn't. 

  • MOS Note:1454872.1
    Transportable Tablespace (TTS) Restrictions and Limitations: Details, Reference, and Version Where Applicable

    tells you:
  •  And the above documentation link then tells you:
    • The source and the destination databases must use compatible database character sets. That is, one of the following must be true:
      • The database character sets of the source and the target databases are the same.
      • The source database character set is a strict (binary) subset of the target database character set, and the following three conditions are true:
        • The source database is in version 10.1.0.3 or higher.
        • The tablespaces to be transported contain no table columns with character length semantics or the maximum character width is the same in both the source and target database character sets.
        • The tablespaces to be transported contain no columns with the CLOB data type, or the source and the target database character sets are both single-byte or both multibyte.
      • The source database character set is a strict (binary) subset of the target database character set, and the following two conditions are true:
        • The source database is in a version lower than 10.1.0.3.
        • The maximum character width is the same in the source and target database character sets.
          .
  • And furthermore from "Choosing a Character Set" section of Oracle 12.1  Database Globalization Support Guide:
    • Oracle Database does not maintain a list of all subset-superset pairs but it does maintain a list of binary subset-superset pairs that it recognizes in various situations such as checking compatibility of a transportable tablespace or a pluggable database. Table A-11 and Table A-12 list the binary subset-superset pairs recognized by Oracle Database.
    • In those tables the only binary subset-superset pairs involving AL32UTF8 are:
      • UTF8/AL32UTF8
      • US7ASCII/AL32UTF8
        .
  • This is not 100% congruent to the information provided in
    MOS Note 371556.1 - How to Migrate to different Endian Platform Using Transportable Tablespaces With RMAN
    saying: 
    "
    The source and target database must use the same character set and national character set."
    .

What is correct, what's not?

First of all the wording of "compatible character sets" seems to be gotten introduced with Oracle Database 11.2. 

In fact the scope for the target system has become broader in Oracle Database 11.2.  These rule here are correct as soon as your target database is an Oracle Database 11.2 or newer database release.

  • The source and the destination databases must use compatible database character sets. That is, one of the following must be true:
    • The database character sets of the source and the target databases are the same.
    • The source database character set is a strict (binary) subset of the target database character set, and the following three conditions are true:
      • The source database is in version 10.1.0.3 or higher.
      • The tablespaces to be transported contain no table columns with character length semantics or the maximum character width is the same in both the source and target database character sets.
      • The tablespaces to be transported contain no columns with the CLOB data type, or the source and the target database character sets are both single-byte or both multibyte.
    • The source database character set is a strict (binary) subset of the target database character set, and the following two conditions are true:
      • The source database is in a version lower than 10.1.0.3.
      • The maximum character width is the same in the source and target database character sets.
        .

--Mike

Thursday Mar 31, 2016

DROP PLUGGABLE DATABASE - things you need to know

Directly after my DOAG (German Oracle User Group) Conference presentation about "How Single-/Multitenant will change a DBA's life" Martin Bach (Enkitec) approached me and told me about his experiences with the DROP PLUGGABLE DATABASE command and future recoverability.

Martin discovered that once you issued the DROP PLUGGABLE DATABASE command you can't reuse a previously taken backup of this particular PDB anymore and recover the PDB into this existing CDB. I wasn't aware of this and I'm glad that Martin told me about it.

Actually only the meta information in the controlfile or the RMAN catalog will be deleted. But archive logs and backup still persist.

See also my blog post from Jan 9, 2015:
Recent News about Pluggable Databases - Oracle Multitenant

This is the error message you'll see when you try to recover a dropped pluggable database:

RMAN> restore pluggable database pdb2drop;

Starting restore at 01-JUN-15
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 06/01/2015 10:10:40
RMAN-06813: could not translate pluggable database pdb2drop

Just a few weeks ago a colleague from German presales asked me if I know a more convenient way to restore a PDB once the magic command (DROP PLUGGABLE DATABASE) has been issued than recovering it into an auxiliary container database abd unbplug/plug it. I haven't.

But Nik (thanks!!!) told me that he pushed a MOS Note to being published explaining how to workaround this issue: 

MOS Note: 2034953.1
How to Restore Dropped PDB in Multitenant

In brief this MOS Note describes how to:

  • Create an auxiliary container database
  • Recover the backup (yes, you will have to have a backup of your container database) including this particular PDB
  • Unplug the PDB after recovery has been finished and plug it back into the original CDB

Now some will say: Hey, that's simple and obvious. For me it wasn't ;-) That's why I write about it to remind myself of this workaround ...

--Mike
.



Tuesday Mar 01, 2016

Differences between Automatic Statistics Gathering job and GATHER_SCHEMA_STATS

Recently a customer raised a question whether there are differences between the Automatic Statistics Gathering job and a manual creation of stats via the GATHER_SCHEMA_STATS procedure.

The results in performance were quite interesting. Performance after an upgrade from Oracle Database 11.2.0.3 to Oracle Database 11.2.0.4 was not good when the automatic stats job got used. But performance changed significantly to the better when schema stats were created with the downside of taking more resources during the gathering.

Is the Automatic Stats Gathering job enabled?

That question can be answered quite easily. There's a very good MOS Note:1233203.1 - FAQ: Automatic Statistics Collection displaying this query:

SELECT CLIENT_NAME, STATUS FROM DBA_AUTOTASK_CLIENT WHERE CLIENT_NAME='auto optimizer stats collection';

The MOS Note has also the code to enable (or disable) the job.
.

Which parameters/settings are used?

That question is a bit more tricky as the Note says: "The automatic statistics-gathering job uses the default parameter values for the DBMS_STATS procedures". But how do I display them?

The following script will display the parameters being used during the Automatic Statistics Gathering:

SET ECHO OFF
SET TERMOUT ON
SET SERVEROUTPUT ON
SET TIMING OFF
DECLARE
   v1  varchar2(100);
   v2  varchar2(100);
   v3  varchar2(100);
   v4  varchar2(100);
   v5  varchar2(100);
   v6  varchar2(100);
   v7  varchar2(100);
   v8  varchar2(100);
   v9  varchar2(100);
   v10 varchar2(100);        
BEGIN
   dbms_output.put_line('Automatic Stats Gathering Job - Parameters');
   dbms_output.put_line('==========================================');
   v1 := dbms_stats.get_prefs('AUTOSTATS_TARGET');
   dbms_output.put_line(' AUTOSTATS_TARGET:  ' || v1);
   v2 := dbms_stats.get_prefs('CASCADE');
   dbms_output.put_line(' CASCADE:           ' || v2);
   v3 := dbms_stats.get_prefs('DEGREE');
   dbms_output.put_line(' DEGREE:            ' || v3);
   v4 := dbms_stats.get_prefs('ESTIMATE_PERCENT');
   dbms_output.put_line(' ESTIMATE_PERCENT:  ' || v4);
   v5 := dbms_stats.get_prefs('METHOD_OPT');
   dbms_output.put_line(' METHOD_OPT:        ' || v5);
   v6 := dbms_stats.get_prefs('NO_INVALIDATE');
   dbms_output.put_line(' NO_INVALIDATE:     ' || v6);
   v7 := dbms_stats.get_prefs('GRANULARITY');
   dbms_output.put_line(' GRANULARITY:       ' || v7);
   v8 := dbms_stats.get_prefs('PUBLISH');
   dbms_output.put_line(' PUBLISH:           ' || v8);
   v9 := dbms_stats.get_prefs('INCREMENTAL');
   dbms_output.put_line(' INCREMENTAL:       ' || v9);
   v10:= dbms_stats.get_prefs('STALE_PERCENT');
   dbms_output.put_line(' STALE_PERCENT:     ' || v10);
END;
/

The settings of the DBMS_STATS.GATHER_SCHEMA_STATS procedure are documented:
https://docs.oracle.com/database/121/ARPLS/d_stats.htm#ARPLS68577 

When you compare the two you'll see that the settings/defaults are identical. 
.

But what is the difference between these two?

Both activities use the same parameters. So the stats will look the same - IF they get created. The real difference between the Automatic Statistics Gathering job and a manual invocation of GATHER_SCHEMA_STATS is that the latter will refresh ALL statistics whereas the Automatic Statistics Gathering job will refresh only statistics on objects where statistics are missing or marked as STALE.

The same behavior appears when you compare the recommendation to gather dictionary statistics before the upgrade by using DBMS_STATS.GATHER_DICTIONARY_STATS versus a DBMS_STATS.GATHER_SCHMEA_STATS('SYS')call. The latter will refresh all statistics whereas the first one will take less resources but refresh only STALE and missing statistics.
.

A simple example

This script is kept as simple as possible.

  • It creates a test user
  • It creates two tables within this user - tablespace USERS
  • It inserts and updates information in the two tables
  • It flushes the monitoring information (how many DMLs got run?) out
  • It gathers stats on only one table to verify that STALE is working as intended
  • It kicks off the automatic stats gathering job
  • It kicks off the schema stats gathering call
  • It compares results before/after in the stats history table 

set timing on
set serverout on
set echo on
set termout on
column table_name Format a5
column owner      Format a6
column stale_stats Format a4
column last_analyzed Format a15
column sample_size format 9999999
drop user test1 cascade;
create user test1 identified by test1;
grant connect, resource, dba to test1;
alter user test1 default tablespace USERS;
create table TEST1.TAB1 as select * from dba_objects where rownum<50001;
exec dbms_stats.gather_table_stats('TEST1','TAB1');
create table TEST1.TAB2 as select * from dba_objects where rownum<50001;
exec dbms_stats.gather_table_stats('TEST1','TAB2');
insert into TEST1.TAB1 select * from dba_objects where rownum<50001;
commit;
insert into TEST1.TAB2 select * from dba_objects where rownum<50001;
commit;
insert into TEST1.TAB2 select * from dba_objects where rownum<50001;
commit;
update TEST1.TAB1 set object_id=object_id+0;
commit;
update TEST1.TAB2 set object_id=object_id+1;
commit;
exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO;
select table_name,owner,stale_stats,to_char(last_analyzed,'DD-MON HH24:MI:SS') LAST_ANALYZED,SAMPLE_SIZE from dba_tab_statistics where table_name in ('TAB1','TAB2');
exec DBMS_STATS.GATHER_TABLE_STATS('TEST1','TAB1');
select table_name,owner,stale_stats,to_char(last_analyzed,'DD-MON HH24:MI:SS') LAST_ANALYZED,SAMPLE_SIZE from dba_tab_statistics where table_name in ('TAB1','TAB2');
exec DBMS_AUTO_TASK_IMMEDIATE.GATHER_OPTIMIZER_STATS;
pause Wait a bit - then press return ...
select table_name,owner,stale_stats,to_char(last_analyzed,'DD-MON HH24:MI:SS') LAST_ANALYZED,SAMPLE_SIZE from dba_tab_statistics where table_name in ('TAB1','TAB2');
exec dbms_stats.gather_schema_stats('TEST1');
select table_name,owner,stale_stats,to_char(last_analyzed,'DD-MON HH24:MI:SS') LAST_ANALYZED,SAMPLE_SIZE from dba_tab_statistics where table_name in ('TAB1','TAB2');
prompt End ...

.

The results

exec
DBMS_STATS.
FLUSH_DATABASE_MONITORING_INFO;
TABLE OWNER  STAL LAST_ANALYZED   SAMPLE_SIZE
----- ------ ---- --------------- -----------
TAB1  TEST1  YES  29-FEB 22:37:07       50000
TAB2  TEST1  YES  29-FEB 22:37:07       50000

exec
DBMS_STATS.
GATHER_TABLE_STATS('TEST1','TAB1');
TABLE OWNER  STAL LAST_ANALYZED   SAMPLE_SIZE
----- ------ ---- --------------- -----------
TAB1  TEST1  NO   29-FEB 22:37:12      100000
TAB2  TEST1  YES  29-FEB 22:37:07       50000

exec
DBMS_AUTO_TASK_IMMEDIATE.
GATHER_OPTIMIZER_STATS;

TABLE OWNER  STAL LAST_ANALYZED   SAMPLE_SIZE
----- ------ ---- --------------- -----------
TAB1  TEST1  NO   29-FEB 22:37:12      100000
TAB2  TEST1  NO   29-FEB 22:37:13      150000

exec
dbms_stats.
gather_schema_stats('TEST1');

TABLE OWNER  STAL LAST_ANALYZED   SAMPLE_SIZE
----- ------ ---- --------------- -----------
TAB1  TEST1  NO   29-FEB 22:37:43      100000
TAB2  TEST1  NO   29-FEB 22:37:43      150000

The results can be interpreted this way:

  • The sample size of 50k is based on the first activity during the CTAS
  • Once table TAB1 gets analyzed the sample size is now correct - and the time stamp got updated - statistics on TAB2 are still marked STALE of course as the underlying table has changed by more than 10%
  • The Automatic Statistics Gathering job will refresh only stats for objects where stats are missing or marked STALE - in this example here TAB2. Table TAB1's statistics remain unchanged.
  • When the GATHER_SCHEMA_STATS job gets invoked it will refresh all statistics - regardless if they were STALE or not. 

This is the behavior the customer who raised the question about differences in these two ways to create statistics may have seen. The GATHER_SCHEMA_STATS job took longer and consumed more resources as it will refresh all statistics regardless of the STALE attribute.

And it's hard to figure out why the refresh of statistics created in a previous release may have led to suboptimal performance, especially as we talk about a patch set upgrade - and not a full release upgrade. Thanks to Wissem El Khlifi who twittered the following annotations I forgot to mention:

  • The Automatic Statistics Gathering job prioritizes objects with NO statistics over objects with STALE statistics
  • The Automatic Statistics Gathering job may get interrupted or skip objects leaving them with NO statistics gathered. You can force this by locking statistics - so the Auto job will skip those completely

You'll find more information about the Automatic Statistics Gathering job here:

And another strange finding ...

When I played with this example in 12c I encountered the strange behavior of the GATHER_OPTIMIZER_STATS call taking exactly 10 minutes unti it returns to the command prompt.

First I thought this is a Multitenant only issue. But I realized quickly: this happens in non-CDB databases in Oracle 12c as well. And when searching the bug database I came across the following unpublished bug:

  • Bug 14840737
    DBMS_AUTO_TASK_IMMEDIATE.GATHER_OPTIMIZER_STATS RETURNS INCORRECTLY

which got logged in Oct 2012 and describes this exact behavior. I kick off the job - it will update the stats pretty soon after - but still take 10 minutes to return control to the command prompt. It is supposed to be fixed in a future release of Oracle Database ... 

 

--Mike 

Thursday Jan 21, 2016

SuSE SLES 12 certified with Oracle Database 12.1.0.2

Puh ... I've got many mails over several months asking about the current status of certification of SuSE SLES12 for Oracle Database 12.1.0.2. It took a while - and I believe it was not in our hands. But anyhow ... finally ...

See Release Notes for additional package requirements

Minimum kernel version: 3.12.49-11-default
Mininum PATCHLEVEL: 1

Additional Notes

  • Edit CV_ASSUME_DISTID=SUSE11 parameter in database/stage/cvu/cv/admin/cvu_config & grid/stage/cvu/cv/admin/cvu_config
  • Apply Patch 20737462 to address CVU issues relating to lack of reference data
  • Install libcap1 (libcap2 libraries are installed by default); i.e. libcap1-1.10-59.61.x86_64 & libcap1-32bit-1.10-59.61.x86_64
  • ksh is replaced by mksh; e.g. mksh-50-2.13.x86_64
  • libaio has been renamed to libaio1 (i.e. libaio1-0.3.109-17.15.x86_64); ensure that libaio1 is installed


Note: OUI may be invoked with -ignoreSysPreqs to temporarily workaround ongoing CVU check failures

I had a SuSE Linux running on my previous laptop as dual-boot for quite a while. And I still like SuSE way more than any other Linux distributions potentially because of the fact that it was the Linux I started developing some basic Unix skills. I picked up my first Linux at the S.u.S.E. "headquarters" near Fürth Hauptbahnhof in 1994. I used to live just a few kilometers away and the version 0.9 a friend had given to me on a bunch of 3.5'' floppies had a disk failure. I believe the entire package did cost DM 19,90 by then - today roughly 10 Euro when you don't consider inflation - and was distributed on floppy disks. The reason for me to buy it was simply that I had no clue about Linux - but SuSE had a book delivered with the distribution.

This is a distribution I had purchased later on as well - they've had good discounts for students by then.


Picture source: Wikipedia - https://en.wikipedia.org/wiki/SUSE_Linux_distributions

--Mike

PS: Updated with more recent information on 15-02-2016 

Thursday Jan 07, 2016

Oracle Database certification on Microsoft Windows 10

The MOS Note: 1307195.1  about

Certification Information for Oracle Database on Microsoft Windows x64 (64-bit)

got updated right before the 2015 Christmas holidays with the official certification information for Oracle Database 12c on Microsoft Windows 10.

And it says:

Windows 10 O/S Information:
RAC is not certified.
The earliest release certified on Windows 10 is 12.1.0.2.

--Mike 


Previous related blog posts:

Wednesday Dec 02, 2015

What or who is JOX? And what happens if ...

Are you worried because of the title of this blog post? Don't worry ... it will be about the Oracle database and not about miraculous additives.

Months back I wrote about how to remove options from the Oracle database's kernel by either using chopt or linking options off:

Recently a very interesting case was logged in relation to a bug:
A customer had linked off the JOX option from the database kernel.
.

What is JOX? 

Well, do a quick search for "JOX" in the Oracle Documentation and you'll get "zero results" (or you may get results in the 8.1 and 9.0 documentation which does not help here). So our documentation doesn't want to tell you further details on "JOX". But once you know that this is the acronym for the JavaVM JIT Compiler then we'll get more results leading directly to this spfile parameter:

The default in my environments is always TRUE. It enables or disables the Just-in-Time (JIT) compiler for the Oracle Java Virtual Machine (OracleJVM) environment.

And from here a link guides us into the Java Documentation explaining a bit more details about the JIT in case you are interested. 
.

Why am I writing this?

In theory you can unlink the JOX option - and compile oracle without the JIT:

$ cd $ORACLE_HOME/rdbms/lib
$ /usr/bin/
make -f /u01/app/oracle/product/12.1.0.2/rdbms/lib/ins_rdbms.mk jox_off ioracle

But let me add two things

  • We recently had a bug just happening because JOX was unlinked - all our tests have JOX linked in
  • As you can control the availability of JOX via the above initialization parameter I don't see a deeper reason to unlink JOX
  • And based on the feedback of Marcelo Ochoa (thanks Marcelo!!!) you'll encounter a performance dip in some components such as interMedia, XMLDB etc as code will now be interpreted, and not JIT compiled.
  • Furthermore based on the feedback of my former colleague, Norbert Debes, unlinking JOX may cause also trouble with the upgrade to Oracle Database 12c
My conclusion: There's no real reason to take JOX out of the RDBMS kernel. Please leave it linked in.


How do you find out if JOX is linked in?

You can't query the database for JOX as it is not mentioned in V$OPTION or DBA_REGISTRY. But you can find it out easily on the OS level: 

$ cd $ORACLE_HOME/rdbms/lib
$ ar -t libknlopt.a | grep -c jox.o

If the result is "1" it means "JOX installed", and "0"  means it is not installed.

Credits to Byron Motta from our Database Upgrade team! 

Further information about JIT?

--Mike

Tuesday Oct 06, 2015

New in Oracle 12c: _optimizer_gather_stats_on_load

Received an email from Roy last night with some performance issues a customer in the US encountered recently during their upgrade testing

One issue the customer encountered has to do with tons of parallel slaves creating a massive noise on the system when they are doing a CTAS (Create Table As Select) - and the same thing happens with an IAS (Insert Append Select).
.

What caused this change?

In this case the behavior change is well documented, even though not linked to the responsible underscore parameter. 

Quoting from the first paper: 

Online statistics gathering

In Oracle Database 12c, online statistics gathering “piggybacks” statistics gather as part of a direct-path data loading operation such as, create table as select (CTAS) and insert as select (IAS) operations. Gathering statistics as part of the data loading operation, means no additional full data scan is required to have statistics available immediately after the data is loaded.

The parameter controlling this change is not mentioned:

  • _optimizer_gather_stats_on_load

The default is TRUE since Oracle 12.1.0.1 - the parameter or functionality did not exist before Oracle Database 12c.
.

Things to Know

The online stats gathering for IAS can happen only if the object you are loading data into is empty. You'll recognize the feature when you see a line saying OPTIMIZER STATISTICS GATHERING in the execution plan. Additionally you'll find STATS_ON_LOAD in the NOTES column of below query:

select COLUMN_NAME, NUM_DISTINCT, DENSITY, HISTOGRAM, NOTES from USER_TAB_COL_STATISTICS where TABLE_NAME='MYTAB' ;

Consider now that first of all immediate stats gathering may not be desired as it will mean additional load to the system. Even though the feature has been designed to generate as little load as possible it's still doing something in the background. Hence there may be situations where you'd like to switch it off, e.g. with a hint:

insert /*+append NO_GATHER_OPTIMIZER_STATISTICS*/ into MYTAB select ...

And how about the number of parallel slaves creating the statistics? 

There's no easy answer to this based on the information I have at the moment - but I'd highly recommend this article about parallel execution in Oracle:

--Mike

Wednesday Sep 16, 2015

Script: Is your database ready for Oracle GoldenGate?

Oracle GoldenGate can be a good addition to a lot of upgrade and migration projects to decrease the downtime to a bare minimum, or even zero in some cases.

But before you consider Oracle GoldenGate as THE solution to decrease your downtime you may evaluate if your database is ready for OGG. For this purpose you can download scripts from MyOracle Support (MOS) to check exactly this.

Complete Database Profile OGG readiness check for Classic Extract 

MOS Note:1298562.1:
Oracle 
GoldenGate database Complete Database Profile check script for Oracle DB (All Schemas) Classic Extract 

This script is intended to query all of the non default database users to identify current configuration and identify any unsupported data types or types that may need special considerations for Oracle GoldenGate in an Oracle environment.

Check OGG readiness for Schema Only

MOS Note: 1296168.1
Oracle GoldenGate database Schema Profile check script for Oracle DB

This script is intended to query the database by schema to identify current configuration and identify any unsupported data types or types that may need special considerations for Oracle GoldenGate in an Oracle environment. 

Difference and Execution 

The main difference between the two scripts is the amount of data being processed. The Schema Script is more targeted and therefore should return fewer items that need additional checking.

Log into sqlplus as sysdba and run the script:

SQL> @full-DB_CheckOracle_15092015.sql

SQL> @full-schemaCheckOracle_15092015.sql

Review the output.

--Mike

Monday Jun 16, 2014

DBUA can ignore Underscores with "keepHiddenParams"

The Database Upgrade Assistant (DBUA) by default will remove all underscores and events before actually doing an upgrade. This is expected behaviour as we believe that underscores or events were meant to treat misbehaviour of the database just in a specific release only. And in our experience a nice collection of underscores will at least make your upgrades run slower.

Now with Oracle Database 11.2.0.4 and Oracle Database 12.1.0.1 the DBUA has a new parameter which can be used during startup:

$> dbua -keepHiddenParams

Then it will keep the hidden/underscore parameters during and after the upgrade.

-Mike 

PS: Many thanks to Mr. Frank Becker for highlighting this to myself - I wasn't aware of it - screenshot is courtesy from Mr Becker as well 

Thursday Apr 17, 2014

Avoid Poor Performance and Wrong Results in 11.2.0.4

One of the best notes in MOS got refreshed last night for Oracle Database 11.2.0.4. It has not only information about performance bugs but also about the worst and meanest category of issues, wrong query result bugs. Those are the ones I call the "sleeping beauties" as you as a DBA usually won't recognize them. It is recognized at the application level when the result of a report is incorrect or instead of 4 rows just 3 rows get delivered in the output. Therefore you should be highly aware of those and add this note to your bookmarks.

MOS Note 1645862.1Things to Consider Before Upgrading to 11.2.0.4 to Avoid Poor Performance or Wrong Results

And in case you don't want to move to Oracle 11.2.0.4 but searching for information and known issues in Oracle 11.2.0.3 or Oracle 11.2.0.2 then please find the corresponding documents:

  • MOS Note: 1320966.1 Things to Consider Before Upgrading to 11.2.0.2.x to Avoid Poor Performance or Wrong Results
  • MOS Note: 1392633.1 Things to Consider Before Upgrading to 11.2.0.3 to Avoid Poor Performance or Wrong Results
-Mike

Friday Dec 21, 2012

Creating ASM for test purposes in the file system

First of all, I'm back after pausing for a while - sorry for not updating the blog in the past weeks ... and you won't see many updates in the following weeks as it'll be holiday season (and we Germans have sooooo many public holidays) :-)

Anyway, back to tech topics. Today I want to test Oracle Restart upgrades. Oracle Restart internally is called SIHA (Single Instance High Availability) which explains the topic a bit more. Basically it means having your database reside in ASM and let Oracle Clusterware take care on it, even though you don't have a cluster. Not a bad idea as this can be very helpful in real world environments. But I did realize that the entire process is not documented in all details. So I'd thought I should give this a try.

The first challenge I do face: I have just one disk in my machine - so I'll have to tweak ASM a bit to make it work with files on the file system.

Creating two empty strawman files in file system with dd is not a big deal:
$ dd if=/dev/zero of=/oradata/ASM/dg_DATA bs=8192 count=1000000 oflag=direct
1000000+0 records in
1000000+0 records out
8192000000 bytes (8.2 GB) copied, 336.371 seconds, 24.4 MB/s
[V112] oracle@localhost:/oradata
$ dd if=/dev/zero of=/oradata/ASM/dg_BCK bs=8192 count=500000 oflag=direct
500000+0 records in
500000+0 records out
4096000000 bytes (4.1 GB) copied, 246.021 seconds, 16.6 MB/s

But the next step is to start the cssd (Cluster Synchronization Services Demon) in my Oracle Database 10.2.0.5 installation from within the $ORACLE_HOME/bin directory:
[root@localhost bin]# . localconfig add
/etc/oracle does not exist. Creating it now.
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
Configuration for local CSS has been initialized
Adding to inittab
Startup will be queued to init within 30 seconds.
Checking the status of new Oracle init process...
Expecting the CRS daemons to be up within 600 seconds.
CSS is active on these nodes.
        localhost
CSS is active on all nodes.
Oracle CSS service is installed and running under init(1M)

Otherwise no chance for ASM to start up.

Now my attempts to use simply DBCA (Database Configuration Assistant) to creare the ASM instance on these two strawman files did not work as the DBCA didn't want to recognize the "disks". So back to good old command line. By the way, there's a MOS Note out there which may be helpful as well (but didn't work in my case).
How To Create ASM Diskgroups using NFS/NAS Files? (Doc ID 731775.1)

  1. Create a password file for ASM instance in $ORACLE_HOME/dbs
  2. Create a fresh init.ora for ASM within the same directory having the following parameters set:
    _asm_allow_only_raw_disks='FALSE'
    asm_diskstring='/oradata/ASM/dg*'
    asm_power_limit=4
    instance_type=asm
  3. With these parameter set I could bring the instance into MOUNT state ready to create the two disk groups after setting the ORACLE_SID=+ASM in the environment:
    SYS:+ASM> create diskgroup DATA external redundancy disk '/oradata/ASM/dg_DATA';
    Diskgroup created.
    SYS:+ASM>  create diskgroup BCK  external redundancy disk '/oradata/ASM/dg_BCK';
    Diskgroup created.

Starting up ASM did work now well after shutting it down first - and a check for SELECT path from V$ASM_DISK did show me my disks.

Next step - simply - is to create a database with DBCA inside of ASM. So the first part of my test did complete.

... to be continued soon ...

Monday Nov 12, 2012

Rules Manager and Expression Filter getting removed

I doubt that many people are using the Oracle features "Rules Manager" and "Expression Filter" as usually people handle these things (such as ensuring that a zip code or a car number plate has a certain format) within the application code and not inside the database. Oracle Beehive for instance uses that just on the side. 

Anyway, just learned today that Rules Manager and Expression Filter components will get removed once our next database release most likely called Oracle Database 12c will get released. So before upgrading to Oracle Database 12c you can remove EXF and RUL components (SELECT COMP_ID FROM DBA_REGISTRY WHERE COMP_ID IN ('EXF','RUL'); ).

You'd simply do that by executing the following script before upgrade:
SQL> @?/rdbms/admin/catnoexf.sql
This will clean up Rules Manager and Expression Filter components inside the database. You could run ?/rdbms/admin/catnorul.sql before but I believe catnoexf.sql will clean up everything already.

And you'll find all this information plus guidelines for migration of existing content in MOS Note: 1233535.1 - Obsolescence Notice: Rules Manager and Expression Filter Features of Oracle Database

-M.

Friday Sep 02, 2011

Oracle Database Express Edition 11g Release 2 is now available!

Many customers have asked over the past year whether we would be producing a newer version of the Oracle Database Express Edition, because it makes such a terrific starter database for students, developers, and DBAs. Well, the answer is Yes!

As of today, Oracle Database Express Edition 11g Release 2 is available for download. As with the 10g release, this Express Edition is free of charge. To find more information about downloading the software and documentation, please visit the OTN page for Express Edition.

Remember, you can always upgrade your database to Standard or Enterprise Edition if you outgrow the resource constraints of Express Edition. So, free free to download the new release and check it out!

Enjoy!

Wednesday Aug 10, 2011

Upgrade - and an interesting surprise

Patchset 11.2.0.2 is out there for a long, looong time. But still Roy and me - and unfortunately our customers - sometimes experience some nice surprises after upgrade.

Roy did work on the weekend with a financial institution customer in the US to support them during their go-live on Oracle 11.2.0.2 with several systems. It was well tested and long planned. And overall the whole process went well except for one database (see Roy's entry below on the change with JOB_QUEUE_PROCESSES in 11.2.0.2).

Yesterday I've received a text message from Roy to have a closer look into a service request the customer did open due to massive performance problems after go-live. The customer has seen MUTEX S CONTENTION in the AWR and ADDM reports slowing down the whole cluster. And the support people from BDE did a good job to drill down and diagnose the issue. But the solution is an interesting surprise.

The April 2011 PSU 11.2.0.2.2 (Patch 11724916) did contain a fix:
Bug:10187168 Enhancement to obsolete parent cursors if VERSION_COUNT exceeds a threshold

And you might believe that having the fix included in the PSU does enable the code of this fix?? At least I would believe that. But as Roy would call it "the old 'hidden bug fix' trick..." the code is there but you'll have to enable it. MOS Note 10187168.8 describes what you'll have to do.

Actually you'll have to set this hidden parameter:
_cursor_features_enabled=1026
and besides that this nice event in your init.ora/spfile:
events= "106001 trace name context forever, level 1024"
Then bounce the instance ... an voila ... the patch is now enabled.

Scary, isn't it ...
As I've spent 6 years in Oracle's RDBMS Support as well I was first checking the patch readme to see whether this is mentioned somewhere - but the only remark pointing to this bug is:
Bug 10264680 - INCORRECT VERSION_NUMBER REPORTED AFTER PATCH FOR 10187168 APPLIED

Anyway, what is this parameter for?

It's an enhancement request to allow parent cursor to be obsoleted if the version count exceeds a defined threshold. This threshold gets set with the new underscore _cursor_obsolete_threshold.
But due to the fact that other patches included in this PSU don't know about this newly introduced underscore event 106001 has to be set instead. The value equals the threshold.

To make things more complicated and enable this fix now (see my comments above) the underscore _cursor_features_enabled has to be set as well. And its value is dependend on the patch level - for Oracle 11.2.0.2 it is 1026 whereas for Oracle 11.1.0.7 it would be 18.

And let me mention that these parameters will have to be set and adjusted just under Oracle's Support guidance!

Wednesday Mar 09, 2011

New hidden parameters in Oracle 11.2

[Read More]

Monday Jan 17, 2011

Is Oracle certified to run on VMWare?

[Read More]

Monday Nov 15, 2010

Recap on Deinstall Routines

[Read More]

Friday Oct 15, 2010

How to deinstall "old" SW after 11.2.0.2 has been applied?

I'm still learning continuously new things. On my test box I've installed 11.2.0.2 a few weeks ago in-place which is not our recommended solution - and can be quite tricky with 11.2.0.2. But I was running out of space ...

Customer have asked us for a looooooong time why we don't deliver full release but patch sets which have to be applied to a base installation. Now this all has been changed with 11.2.0.2 as it is a full release. And that's the new strategy: We force people to install into a separate $ORACLE_HOME. This is called (and recommended) an out-of-place patch upgrade. Which is the default for Grid Infrastructure as well beginning with 11.2.0.1 already.

You can still go "the old way" for the database installation but you'll have to 'detach' your current home from the OUI inventory first in order to be able to install into the existing one. Something we fully disrecommend as it will increase your downtime if anything fails.

Nevertheless the question came up at the upgrade workshop in Montreal (and you might have asked yourself as well already):
How do I deinstall the "old" software once I'm using the new 11.2.0.2 Oracle home???

 

2010_10_15_iPhone_remove.jpg
My first thought: Hey, if it's a Unix box simply 'rm -rf' the directories ;-)
Second thought: Use the OUI as it always did offer a 'REMOVE' button to remove leftovers.
Third thought: Use the magic 'X' to deinstall similar to your iPhone (ok. we are not this far yet)

 

Lesson learned now (thanks Roy!):
There's a deinstallation tool called 'deinstall' available in your $ORACLE_HOME/deinstall directory. Please find a detailed description right here in the Oracle Database Installation Guide(s) for 11.2. It will work for the database, Clusterware and ASM. But you could download it separately from OTN as well as part of the software distribution.

Friday Apr 09, 2010

Instant Client 11.2 available for download

[Read More]

Tuesday Apr 06, 2010

Oracle Database 11g Release 2 for Windows available!

[Read More]

Thursday Mar 25, 2010

Fix invalid objects and components - BEFORE you upgrade!

[Read More]

Tuesday Mar 09, 2010

Gathering Workload Statistics

[Read More]

Monday Mar 08, 2010

Remove "old" parameters and events from your init.ora

[Read More]

Tuesday Feb 09, 2010

Dmitry's upgrade findings

[Read More]

Friday Dec 04, 2009

Optimizer patch recommendation for 11.1.0.7

[Read More]
About

Mike Dietrich - Oracle Mike Dietrich
Master Product Manager - Database Upgrade & Migrations - Oracle

Based in Germany. Interlink between customers/partners and the Upgrade Development. Running workshops between Arctic and Antartica. Assisting customers in their reference projects onsite and remotely. Connect via:

- -

Search

Archives
« July 2016
SunMonTueWedThuFriSat
     
1
2
3
6
8
9
10
11
12
13
14
15
16
17
18
19
22
23
24
25
26
27
28
29
30
31
      
Today
Slides Download Center
Visitors since 17-OCT-2011
White Paper and Docs
Workshops
Viewlets and Videos
Workshop Map
x Oracle related Tech Blogs
This week on my Rega & Pono
Upgrade Reference Papers