PSU for 11.1.0.7.1

One of the most read entries on this site is about the PSU release for 10.2.0.4 http://jhdba.wordpress.com/2009/07/28/applying-the-10-2-0-4-1-patch-set-update-psu/

On 20th October, 2009 the first PSU for 11.1.0.7 was released. This also incorporates the Oct 2009 CPU as well as a Dataguard Broker patchset bundle. Bug 7628357 – 11.1.0.7 Data Guard Recommended Patch Bundle #1.

 Oracle have maintained consistency with the 10.2.0.4 PSU by retaining the misinformation in the patch instructions. I did originally think that there was no need to update opatch as there was in 10g but I was wrong. Opatch should be upgraded but the PSU  does apply even if it is not upgraded.

Ensure that the latest Opatch version is installed

 

opatch version

Invoking OPatch 11.1.0.6.2

 

OPatch Version: 11.1.0.6.2

 

If it is not 11.1.0.6.7 or later then upgrade

 

cd $ORACLE_HOME

cp /shared/oracle/rdbms_patches/PSU_patches/11.1.0.7/p6880880_111000_HPUX-IA64.zip .

unzip p6880880_111000_HPUX-IA64.zip

rm p6880880_111000_HPUX-IA64.zip

 

opatch version

Invoking OPatch 11.1.0.6.8

 

OPatch Version: 11.1.0.6.8

 

 

Basic steps for a new build server are as follows

1) Install 11.1.0.6 and upgrade to 11.1.0.7

2) ensure opatch is in the path, an Oracle Home is set and the patcheset (8833297) is unzipped

3) Run the pre-check

Following the README notes I would use this command

$cd /shared/oracle/rdbms_patches/PSU_patches/11.1.0.7

$opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir ./8833297

which then fails

Oracle Interim Patch Installer version 11.1.0.6.2
Copyright (c) 2007, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /app/oracle/product/11.1.0/db_1
Central Inventory : /app/oraInventory
   from           : /var/opt/oracle/oraInst.loc
OPatch version    : 11.1.0.6.2
OUI version       : 11.1.0.7.0
OUI location      : /app/oracle/product/11.1.0/db_1/oui
Log file location : /app/oracle/product/11.1.0/db_1/cfgtoollogs/opatch/opatch2009-10-28_14-16-33PM.log

Invoking prereq "checkconflictagainstohwithdetail"
<span style="color: #ff0000;">The location "./8833297/README.html" is not a directory or a valid patch zip file.
Prereq "checkConflictAgainstOHWithDetail" not executed
PrereqSession failed: Invalid patch location.</span>

<span style="color: #ff0000;">OPatch failed with error code 73

Then use the corrected syntax

opatch prereq CheckConflictAgainstOHWithDetail -ph ./8833297

Invoking OPatch 11.1.0.6.2

Oracle Interim Patch Installer version 11.1.0.6.2
Copyright (c) 2007, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /app/oracle/product/11.1.0/db_1
Central Inventory : /app/oraInventory
   from           : /var/opt/oracle/oraInst.loc
OPatch version    : 11.1.0.6.2
OUI version       : 11.1.0.7.0
OUI location      : /app/oracle/product/11.1.0/db_1/oui
Log file location : /app/oracle/product/11.1.0/db_1/cfgtoollogs/opatch/opatch2009-10-28_14-16-57PM.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.  

This is very quick, less than one minute

4) Now apply the PSU

$cd 8833297
$opatch apply

The patchset took 28 minutes to apply

Afterwards an opatch lsinventory command shows

Oracle Home       : /app/oracle/product/11.1.0/db_1
Central Inventory : /app/oraInventory
   from           : /var/opt/oracle/oraInst.loc
OPatch version    : 11.1.0.6.2
OUI version       : 11.1.0.7.0
OUI location      : /app/oracle/product/11.1.0/db_1/oui
Log file location : /app/oracle/product/11.1.0/db_1/cfgtoollogs/opatch/opatch2009-10-28_15-55-41PM.log

Lsinventory Output file location : /app/oracle/product/11.1.0/db_1/cfgtoollogs/opatch/lsinv/lsinventory2009-10-28_15-55-41PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (2):

Oracle Database 11g                                                  11.1.0.6.0
Oracle Database 11g Patch Set 1                                      11.1.0.7.0
There are 2 products installed in this Oracle Home.
Interim patches (1) :

Patch  8833297      : applied on Wed Oct 28 14:54:39 GMT 2009
   Created on 7 Oct 2009, 23:52:06 hrs PST8PDT
   Bugs fixed:
     6870937, 7627743, 7652888, 7299153, 8242410, 6059178, 8563946, 6955744
     7497788, 6840740, 8244217, 8702276, 6981690, 8450529, 7432556, 7719143
     7523787, 8251486, 8367827, 7613481, 8341623, 7515145, 7348847, 8416414
     8250643, 8284633, 8230457, 8563948, 6900214, 7044551, 7318049, 8940197
     7013124, 7432514, 7393258, 7553884, 7639121, 8563944, 8860821, 7606362
     7426959, 7330434, 7708340, 7352414, 6452375, 7356443, 7341598, 8213302
     7196532, 7446163, 8409848, 8236851, 8342506, 7593835, 7340448, 7309458
     8290478, 8391256, 7462112, 7013817, 8499600, 7411865, 7331867, 7527650
     6977167, 8855565, 6501490, 6598432, 7524944, 7706138, 6941717, 8402555
     7494333, 7586451, 8402551, 7499353, 8408887, 7496908, 7511040, 7719148
     7311601, 7497640, 7373196, 7424804, 7452373, 7597354, 6882739, 7366290
     8543737, 6851669, 7318276, 8284438, 8324760, 7420394, 7834195, 7350127
     7475055, 8563942, 7122161, 8361398, 7451927, 7705669, 7676737, 8301559
     8224083, 8658581, 8211920, 8462173, 7206858, 8563945, 8855553, 8402637
     8257122, 8199266, 7454752, 7516536, 7345904, 8352304, 7416901, 7426336
     8352309, 6812439, 7219752, 8534338, 8542307, 8413059, 7572069, 7436280
     7432601, 7311909, 7506785, 7460818, 7276960, 8855559, 7013835, 7378322
     8402548, 7189645, 8563947, 8306933, 7477246, 7263842, 7480809, 8855575
     8433270, 7556778, 8836375, 7330611, 8339352, 7225720, 8563943, 7036453
     7628387, 8419383, 6970731, 7719668, 7203349, 8402562, 7680907, 7438445
     8855577, 8243648, 7630416, 6851110, 6980597, 6618461, 7357609, 8855570
     8369094, 8318050, 8306934, 7434194, 8833297, 7486595, 7716219, 8362693
     6599920, 7628866, 7183523, 7412296, 7135702, 7720494, 7436152, 7175513
     7602341, 6679303, 8339404, 7393804, 8856696, 7650993, 6980601, 7830065
     7462709, 8563941, 8226397, 7515779

Note that if you already have databases built then you need to run a catbundle script and check for invalid objects. Check the documentation for full details that but a quick summary is to run
cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
SQL CONNECT / AS SYSDBA
SQL STARTUP
SQL @catbundle.sql psu apply
SQL QUIT

Oracle Performance Management with Gaja Vaidyanatha

I attended this 2 day workshop ,held in London, last week.

I already owned both of Gaja’s book, Tales of the Oak Table and 101 Performance Tuning so I was reasonably familiar with  his background.

The course started of badly as Gaja was late arriving due to travel delays and so we did not kick off until 11:30 which was either 1.5 or 2 hours depending on whom you asked. He hit the ground running with a quick introduction and then a round the room ‘what do you want to get from this’ session.

The sessions covered everything set out in the advertising blurb, namely architecture, tracing, use of the OWI interface and a planned methodology for dealing with problems. One key point , apart from the availability of RAT in 11g was that the newer release did not add much to what Oracle 10g already provided with regard to performance tuning. 

The key methodology thoughts were to clearly define a goal, run a simple diagnostic routine and then repeat. With regard to SQL optimisation the 2 things to remeber were

1. Reducing logical I/O is primary in any performance tuning engagement
2. Hand in hand reducing elapsed time is equally important

The repeatable process is to find ‘interesting’ PIDS from glance or top, map them to a SPID (SID and SERIAL#) to find a session then run a 10046 trace and find the most expensive step (often the  most indented call).

Fix the code (one change at a time) and repeat.  If a 10046 trace does not help then get down to the OS level and look at how resources are being used.

That all sounds pretty simple and indeed it is.  I could write more about the methodology but I am concious that it is someone’s livelyhood and not to say too much.

Without sounding too knowledgeable or clever I was aware of most of the content that was discussed in the 2 days. Where I gained real benefits was in the storage aspects that Gaja covered very well and offered real insights into CPU, hyper threading, HBA usage and loading and suchlike. Various RAID levels were well covered and we had  a very good discussion on the value of placing redo logs on RAID 1+0 and suchlike.

Most of the second day was taken up with running through some real world examples, some where the problem was obvious, others where I had no clue at all. Several people had brought in 10046 traces and AWR snapshots and we all gained real insight as Gaja looked at them for the first time and showed us the key points worthy of investigation.

All in all a very worthwhile 2 days. The target audience needs to have a reasonable Oracle knowledge beforehand as a couple of students were saying that they had found it very difficult to follow. Oh and be prepared for long days as on day one Gaja was still talking strongly upto 18:00 despite being deadbeat from travelling and the second day went on up to 17:00.

Executive summary. Highly recommended

Configuring Dataguard Broker in 11G

DataGuard Broken?

A guest blog from colleague Simon Ryan

Last year we did some sandpit work on implementing DataGuard.  This was 10g on a Windows platform.  When it came to the broker we didn’t have much luck.  At the UKOUG Michael Moller referred to the Broker as DataGuard Broken (DataGuard 11g New Features).  From the audience reaction this seemed to be a popular perception.

Since then we’ve successfully implemented DataGuard on most of our Production databases (both 10g and 11g) on HP-UX.  However, we’ve still not embraced the Broker!  The ‘broken’ tag still taints its reputation.  A recent visit by Oracle attempted to assure us that the Broker with 11g is much improved.

The following is a basic guide to implementing the broker and our findings:

Our Setup

o       Primary – Two node RAC Cluster (XXXPRD1A)

o       Physical Standby – Single node (XXXPRD1B)

o       HP-UX 11.31 Itanium

o       Database using ASM (both 11.1.0.7).

o       DataGuard running in Max Availability with Real Time Apply.

Configuration

Add the following entries to tnsnames.ora on all instances:

 

XXXPRD1A_DGMGRL =

   (DESCRIPTION=

     (ADDRESS=(PROTOCOL=tcp)(HOST=xxxem03-vip)(PORT=1526))

     (CONNECT_DATA=

       (SERVICE_NAME=XXXPRD1A_DGMGRL)

     )

    )

 

XXXPRD2A_DGMGRL =

   (DESCRIPTION=

     (ADDRESS=(PROTOCOL=tcp)(HOST=xxxem04-vip)(PORT=1526))

     (CONNECT_DATA=

       (SERVICE_NAME=XXXPRD2A_DGMGRL)

     )

    )

 

XXXPRD1B_DGMGRL =

   (DESCRIPTION=

     (ADDRESS=(PROTOCOL=tcp)(HOST=xxxem01-vip)(PORT=1526))

     (CONNECT_DATA=

       (SERVICE_NAME=XXXPRD1B_DGMGRL)

     )

    )

 

Add the following entry to SID_LIST of your listener in listener.ora on all instances:

 

 (SID_DESC = 

      (ORACLE_HOME = /app/oracle/product/11.1.0/db_1)

      (SID_NAME = XXXPRDxx)

      (GLOBAL_DBNAME = XXXPRDxx_DGMGRL)

    )

 Stop/start listener.

 Test you can now connect:

 

 sqlplus sys@XXXPRD2A_dgmgrl as sysdba

  

Make the following parameter changes:

 

 XXXPRD1A:
<pre>
 

alter system set dg_broker_start=true scope=spfile;

alter system set dg_broker_config_file1='+DATA/XXXPRD1A/broker1.dat' scope=spfile;

alter system set dg_broker_config_file2='+FRA/XXXPRD1A/broker2.dat' scope=spfile;

 XXXPRD1B:

alter system set dg_broker_start=true scope=spfile;

alter system set dg_broker_config_file1='+DATA/XXXPRD1B/broker1.dat' scope=spfile;

alter system set dg_broker_config_file2='+FRA/XXXPRD1B/broker2.dat' scope=spfile; 

 Stop/Start the databases:

 

 Stop XXXPRD1A:
<pre>
 

srvctl stop database -d XXXPRD1A -o immediate

 Stop XXXPRD1B:

shutdown immediate

 Start XXXPRD1B:

startup mount

 Start XXXPRD1A:

srvctl start database -d XXXPRD1A

 

 Check for the broker process:

 

 XXXPRD1A:
<pre>
 

ps –ef | grep ora_dmon_XXXPRD1A

XXXPRD1B:

ps –ef | grep ora_dmon_XXXPRD1B

 

Create DG Broker Configuration:

 

[code lang='xml'] Primary:

dgmgrl

connect sys

<em>enter sys password</em>

 

create configuration XXXPRD1A_DGCONFIG as primary database is “XXXPRD1A” connect identifier is XXXPRD1A;

Configuration "XXXPRD1A_dgconfig" created with primary database "XXXPRD1A"

 

add database “XXXPRD1B” as connect identifier is XXXPRD1B maintained as physical;

Database "XXXPRD1B" added

 

enable configuration;

Enabled.

 

show configuration;

 

Configuration

  Name:                XXXPRD1A_dgconfig

  Enabled:             YES

  Protection Mode:     MaxAvailability

  Databases:

    XXXPRD1A - Primary database

    XXXPRD1B - Physical standby database

 

Fast-Start Failover: DISABLED

 

Current status for "XXXPRD1A_dgconfig":

SUCCESS

 

 Check Broker Files Exist:

Primary and Standby ASM:

Check they are there.

 

Show Database:

 Primary: 

show database verbose “XXXPRD1A”;

 

Database

  Name:            XXXPRD1A

  Role:            PRIMARY

  Enabled:         YES

  Intended State:  TRANSPORT-ON

  Instance(s):

    XXXPRD1A

    XXXPRD2A

 

  Properties:

    DGConnectIdentifier             = 'XXXPRD1A'

    ObserverConnectIdentifier       = ''

    LogXptMode                      = 'SYNC'

    DelayMins                       = '0'

    Binding                         = 'OPTIONAL'

    MaxFailure                      = '0'

    MaxConnections                  = '1'

    ReopenSecs                      = '300'

    NetTimeout                      = '30'

    RedoCompression                 = 'DISABLE'

    LogShipping                     = 'ON'

    PreferredApplyInstance          = ''

    ApplyInstanceTimeout            = '0'

    ApplyParallel                   = 'AUTO'

    StandbyFileManagement           = 'AUTO'

    ArchiveLagTarget                = '0'

    LogArchiveMaxProcesses          = '10'

    LogArchiveMinSucceedDest        = '1'

    DbFileNameConvert               = 'XXXPRD1B, XXXPRD1A'

    LogFileNameConvert              = 'XXXPRD1B, XXXPRD1A'

    FastStartFailoverTarget         = ''

    StatusReport                    = '(monitor)'

    InconsistentProperties          = '(monitor)'

    InconsistentLogXptProps         = '(monitor)'

    SendQEntries                    = '(monitor)'

    LogXptStatus                    = '(monitor)'

    RecvQEntries                    = '(monitor)'

    HostName(*)

    SidName(*)

    StaticConnectIdentifier(*)

    StandbyArchiveLocation(*)

    AlternateLocation(*)

    LogArchiveTrace(*)

    LogArchiveFormat(*)

    LatestLog(*)

    TopWaitEvents(*)

    (*) - Please check specific instance for the property value

 

Current status for "XXXPRD1A":

SUCCESS

 

Standby:

show database verbose “XXXPRD1B”;

 

Database

  Name:            XXXPRD1B

  Role:            PHYSICAL STANDBY

  Enabled:         YES

  Intended State:  APPLY-ON

  Instance(s):

    XXXPRD1B

 

  Properties:

    DGConnectIdentifier             = 'XXXPRD1B'

    ObserverConnectIdentifier       = ''

    LogXptMode                      = 'SYNC'

    DelayMins                       = '0'

    Binding                         = 'OPTIONAL'

    MaxFailure                      = '0'

    MaxConnections                  = '1'

    ReopenSecs                      = '60'

    NetTimeout                      = '30'

    RedoCompression                 = 'DISABLE'

    LogShipping                     = 'ON'

    PreferredApplyInstance          = ''

    ApplyInstanceTimeout            = '0'

    ApplyParallel                   = 'AUTO'

    StandbyFileManagement           = 'AUTO'

    ArchiveLagTarget                = '0'

    LogArchiveMaxProcesses          = '10'

    LogArchiveMinSucceedDest        = '1'

    DbFileNameConvert               = 'XXXPRD1A, XXXPRD1B'

    LogFileNameConvert              = 'XXXPRD1A, XXXPRD1B'

    FastStartFailoverTarget         = ''

    StatusReport                    = '(monitor)'

    InconsistentProperties          = '(monitor)'

    InconsistentLogXptProps         = '(monitor)'

    SendQEntries                    = '(monitor)'

    LogXptStatus                    = '(monitor)'

    RecvQEntries                    = '(monitor)'

    HostName                        = 'xxxem01'

    SidName                         = 'XXXPRD1B'

    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxem01)(PORT=1526))(CONNECT_DATA=(SERVICE_NAME=XXXPRD1B_DGMGRL)(INSTANCE_NAME=XXXPRD1B)(SERVER=DEDICATED)))'

    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'

    AlternateLocation               = ''

    LogArchiveTrace                 = '0'

    LogArchiveFormat                = '%t_%s_%r.dbf'

    LatestLog                       = '(monitor)'

    TopWaitEvents                   = '(monitor)'

 

Current status for "XXXPRD1B":

SUCCESS

 Ensure logs are still being transported and applied

All still works!

 

What does it give us?

• Improved monitoring in OEM.
• Simpler administration via DGMGRL or OEM i.e. changing Protection Mode.
• Easy switchover/failover
  • DGMGRL: switchover to “XXXPRD1A”
  • Click of a button in OEM.
• Fast-Start Failover (with an observer).

 

Conclusions

The broker configuration was relatively painless and I have successfully performed a switchover via both DGMGRL and OEM.  I’ve also used the various OEM functions without issue too.  I’m uneasy about performing such tasks via OEM but then most DBA’s prefer to be at the helm via the command line rather than clicking a button (that’s too easy!). 

If you plan to use DataGuard Broker then embrace it and make sure you don’t alter parameters behind the covers.  It does that for you and it doesn’t like you meddling!  I also think it’s a good idea to setup DataGuard initially without the broker and then configure it once you’re happy that DataGuard is working.

So what’s made the difference since our experience last year?  UNIX v Windows (maybe), 10g v 11g (probably).  A big factor would also have to be our experience level.  In the last year we’ve increased our DataGuard count from zero to ten so we are therefore much more comfortable in the DataGuard world.

When a sand-pit database becomes available I’d like to try and setup the observer and test Fast-Start Failover.  I’m inclined to remove the ‘broken’ tag but I’ll reserve judgment until I’ve tested the remaining functions.

Simon Ryan

Much faster performance with ASM – a real world example

We have a  Peoplesoft database that  manages HR for ~80,000 staff.  Oracle 10.2.03.  Peoplesoft Tools 8.49 PS HR 9 HPUX 11.31 Itanium

There has been long running problems caused by I/O bottlenecks writing down to Hitachi XP SAN disks. We were using filesystem storage and disk_asynch_io was set to false

The root cause of our problem was that the Time and Labour batches contend when they need to acquire exclusive access to write dirty blocks to data files during truncate operations. 

David Kurtz has written about a specific Peoplesoft feature around truncate http://www.go-faster.co.uk/docs.htm#local_write_wait

 We migrated to using ASM disk and achieved significant benefits.

 The testing stages were

1)       Prepare a 2 hour Time and labour batch run which would generate significant throughput

2)       Capture a baseline at 10.2.0.3

3)       Upgrade to 10.2.0.4 and retest

4)       Upgrade to 10.2.0.4 and migrate to ASM (11.1.0.7) and retest

5)       Upgrade to 11.1.0.7 and migrate to ASM (11.1.0.7) and retest

 

Test 3 did not show any difference at all. We were still blocked on disk, which was expected. We probably would have remained at 10.2.0.3 but gone to ASM but there are problems with seeing ASM disk when using 10.2.0.3 (Metalink Note 434500, ORA-15059) so we had the option of applying a fix to get ASM working with 10.2.0.3 or upgrading to 10.2.0.4. We decided that the benefits of getting onto a terminal release and suggestions from Oracle that small changes in the truncate process had been made between 10.2.0.3 and 10.2.0.4 out-weighed the overhead of any application testing that we needed to do.

Test 4 gave us big performance benefits. Test 5 was not as clear cut but we knew some code had to be optimised and we did not have the luxury of time to address the issues. Remaining on 10g minimised the application testing, certainly compared to the testing require to upgrade to 11g.

The benefits can be seen quite clearly in the graph below

This shows a 2 hour period where a constant throughput of T&L jobs were being processed. In parallel a constant online load was being simulated

The black line shows throughput and elapsed time. The run consists of a number of jobs which take 2 hours to complete and batch time is around the 450 - 600 second mark

The same processing is then run with 10.2.0.4 database and ASM. disk_aysnch_io is set to FALSE. The jobs complete 40 minutes faster and elapsed time is around the 300-400 second mark but showing spikes and inconsistent performance

Finally the orange line shows 10.2.0.4 database and ASM. disk_aysnch_io =TRUE. The processing time has now been reduced to just over an hour and the elapsed time is significantly better at an average of 250 seconds and with a very consistent profile.

As with all things in life we have now moved onto the next bottleneck which is the wait flashback buf free caused because we have enabled flashback logging and as the redo log buffer fills up we wait for flashback to be written out. However this is a miniscule issue compared to the original problems.  We have taken flashback off some tablespaces primarily used for PS temporary tables (aka working storage tables) although that does give us an issue when when rebuilding the primary database after a DataGuard failover using flashback logs

I hope this real world example is of use

Managing control files in ASM

Just a couple of notes on how to create multiple ASM control files and moving control files toa different diskgroup.

Assuming we already have a ASM control file in the DATA diskgroup and we wish to add one to the FRA diskgroup

 SQL>show parameter control_files
+DATA/SID/controlfile/current.730.698514673
alter system set control_files='+DATA','+FRA' scope=spfile;
shutdown immediate;
startup nomount;
exit
rman target
/restore controlfile from '+DATAHR/MHRPRD1A/CONTROLFILE/current.730.698514673';
alter database mount;
alter database open;
SQL>show parameter control_files
+DATA/SID/controlfile/current.730.698514673, +FRAHR/SID/controlfile/current.766.698514673

 

                                                                           

Here is an example of how to migrate from one ASM diskgroup to another

Add the new disk discovery path in (if needed), create a diskgroup and add disks

alter system set asm_diskstring='/dev/oracle','/dev/oracle_hr/r*';

create diskgroup DATANEW  external redundancy disk '/dev/oracle_hr/rdisk1','/dev/oracle_hr/rdisk2';

SQL>show parameter control_files

control_files                        string      +DATA10/SID/controlfile/c urrent.796.695384211
alter system set CONTROL_FILES = '+DATANEW' scope=spfile

shutdown
rman target /
startup nomount
restore controlfile  from  '/+DATA/SID/controlfile/current.796.695384211';
alter database mount;
alter database open;

Regressing an Opatch version

According to Metalink the only supported  way to regress to the version of Opatch that was installed is to re-install. However Opatch is a self-contained directory and the contents can be copied from another base installation.

Why would I want to regress the Opatch version. Let me explain with the commands I used.

Database version 10.2.0.4 (which gives opatch version 10.2.0.4.3)  

I wanted to install the PSU patch which requires a leter version of opatch (10.2.0.4.7). What I should have done was copy the opatch folder and save ready for a restore. However I didn’t and when the PSU patch failed due to issues with previously applied patches I had trouble applying the April 2009 CPU patch.

I followed these steps to resolve the problem

Secure existing folder and remove contents

cd $ORACLE_HOME

tar cvf OPatch.20090917_local.tar ./OPatch

rm -R OPatch

Logon onto the remote server and tar the OPatch folder up and ftp to the local server. Note use a different name for the tar file so as not to overwrite the tar files that you have just created.

tar cvf OPatch.20090917_remote.tar ./OPatch

Back on the local server unzip the tar file and check the version of opatch

tar xvf OPatch.20090917_remote.tar 

a./OPatch/docs/FAQ 31 blocks
a ./OPatch/docs/Users_Guide.txt 38 blocks
a ./OPatch/docs/Prereq_Users_Guide.txt 25 blocks
a ./OPatch/jlib/opatch.jar 1415 blocks
a ./OPatch/jlib/opatchutil.jar 270 blocks
a ./OPatch/jlib/opatchprereq.jar 242 blocks
a ./OPatch/jlib/opatchactions.jar 142 blocks
a ./OPatch/opatch 14 blocks
a ./OPatch/opatch.pl 6 blocks
a ./OPatch/opatch.ini 1 blocks
a ./OPatch/emdpatch.pl 38 blocks
a ./OPatch/opatchprereqs/oui/knowledgesrc.xml 2 blocks
a ./OPatch/opatchprereqs/opatch/opatch_prereq.xml 60 blocks
a ./OPatch/opatchprereqs/opatch/rulemap.xml 5 blocks
a ./OPatch/opatchprereqs/opatch/runtime_prereq.xml 1 blocks
a ./OPatch/opatchprereqs/prerequisite.properties 1 blocks

opatch version
Invoking OPatch 10.2.0.4.3

OPatch Version: 10.2.0.4.3

Moving from ASM storage back to filesystem

Whilst it is not very common to move from ASM to filesystem I needed to prepare a regression plan for the migration of a 450 datafile database in the event of problems during the migration or shortly afterwards.

The example below is from a small database called TEST and I used filestore in /app/oracle whereas normally I would have a /oradata/SID/ filesystem

The details below should be enough to get someone through the migration and the sql scripts can be modified to be more generic if necessary but it is not too hard to use them as they are now. 

List the datafiles and we will add a second datafile to the users tablespace to provide an example of an issue that can occur.

SYS@TEST SQL>select file_name from dba_data_files;  +DATA/test/datafile/undotbs1.301.697649965
+DATA/test/datafile/sysaux.300.697649963
+DATA/test/datafile/system.299.697649963
+DATA/test/datafile/users.302.697649965
+DATA/test/datafile/users.309.697650601

 mkdir /app/oracle/TEST

Some tablespaces may have multiple datafiles which would end up with the same name using the code above. However as a quick circumvention I manually identified those tablespaces using this sql and then edited the new data file names

select tablespace_name,count(file_name) from dba_data_files group by tablespace_name ORDER BY 2 

In my example it only applied to the USERS tablespace and I edited the second ASM datafile to be mapped to users02.dbf not users01.dbf as the script will output. This action would have to be repeated for all tablespaces with multiple datafiles

sqlplus / as sysdba

set lines 120 pages 0

select 'copy datafile '||file_id||' to '||'''/app/oracle/TEST/'||substr(file_name,21,instr(file_name,'.')-21)||'_01.dbf'''||';' from dba_data_files order by 1;

copy datafile 1 to '/app/oracle/TEST/system_01.dbf';

copy datafile 2 to '/app/oracle/TEST/sysaux_01.dbf';

copy datafile 3 to '/app/oracle/TEST/undotbs1_01.dbf';

copy datafile 4 to '/app/oracle/TEST/users_01.dbf';

copy datafile 5 to '/app/oracle/TEST/users_02.dbf';

shutdown immediate;

startup mount;

exit

rman target /

copy datafile 1 to '/app/oracle/TEST/system_01.dbf';

copy datafile 2 to '/app/oracle/TEST/sysaux_01.dbf';

copy datafile 3 to '/app/oracle/TEST/undotbs1_01.dbf';

copy datafile 4 to '/app/oracle/TEST/users_01.dbf';

copy datafile 5 to '/app/oracle/TEST/users_02.dbf';

exit

sysdba

select 'alter database rename file '''||file_name||''' to '||'''/app/oracle/TEST/'||substr(file_name,21,instr(file_name,'.')-21)||'_01.dbf'''||';' from dba_data_files order by 1;

alter database rename file '+DATA/test/datafile/sysaux.300.697649963' to '/app/oracle/TEST/sysaux_01.dbf';
alter database rename file '+DATA/test/datafile/system.299.697649963' to '/app/oracle/TEST/system_01.dbf';
alter database rename file '+DATA/test/datafile/undotbs1.301.697649965' to '/app/oracle/TEST/undotbs1_01.dbf';
alter database rename file '+DATA/test/datafile/users.302.697649965' to '/app/oracle/TEST/users_01.dbf';
alter database rename file '+DATA/test/datafile/users.309.697650601' to '/app/oracle/TEST/users_02.dbf';

open the database and check for files still using ASM

open database

select name from v$controlfile
union
select name from v$tempfile
union
select member from v$logfile
union
select filename from v$block_change_tracking;

 

NAME
--------------------------------------------------------------------------------
+DATA/test/controlfile/current.303.697650047
+DATA/test/onlinelog/group_1.304.697650049
+DATA/test/onlinelog/group_2.305.697650049
+DATA/test/onlinelog/group_3.306.697650049
+DATA/test/tempfile/temp.307.697650095
+FRA/test/controlfile/current.260.697650047
+FRA/test/onlinelog/group_1.261.697650049
+FRA/test/onlinelog/group_2.262.697650049
+FRA/test/onlinelog/group_3.263.697650051

alter database rename file '+DATA/test/datafile/sysaux.300.697649963' to '/app/oracle/TEST/sysaux_01.dbf';

alter database rename file '+DATA/test/datafile/system.299.697649963' to '/app/oracle/TEST/system_01.dbf';

alter database rename file '+DATA/test/datafile/undotbs1.301.697649965' to '/app/oracle/TEST/undotbs1_01.dbf';

alter database rename file '+DATA/test/datafile/users.302.697649965' to '/app/oracle/TEST/users_01.dbf';

alter database rename file '+DATA/test/datafile/users.309.697650601' to '/app/oracle/TEST/users_02.dbf';

ALTER DATABASE ADD LOGFILE GROUP 4 ('/app/oracle/TEST/redo_04a.dbf','/app/oracle/TEST/redo_04b.dbf') size 50M;

ALTER DATABASE ADD LOGFILE GROUP 5 ('/app/oracle/TEST/redo_05a.dbf','/app/oracle/TEST/redo_05b.dbf') size 50M;

ALTER DATABASE ADD LOGFILE GROUP 6 ('/app/oracle/TEST/redo_06a.dbf','/app/oracle/TEST/redo_06b.dbf') size 50M; 

You will need to perform logfile switches or checkpoint before the log groups using ASM are available for removal. 

SQL>

show parameter control

 control_files +DATA/test/controlfile/current .303.697650047, +FRA/test/cont rolfile/current.260.697650047

create a pfile from spfile and edit the pfile to show new placement for the controlfiles

*.control_files='/app/oracle/TEST/control01.ctl','/app/oracle/TEST/control02.ctl'

startup nomount

$rman target / 

Recovery Manager: Release 11.1.0.7.0 - Production on Wed Sep 16 16:36:15 2009 connected to target database:

TEST (not mounted) using target database control file instead of recovery catalog

 restore controlfile from '+DATA/test/controlfile/current.303.697650047';

Starting restore at 16-SEP-09 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=70 device type=DISK channel ORA_DISK_1: copied control file copy output file name=/app/oracle/TEST/control01.ctl output file name=/app/oracle/TEST/control02.ctl

Finished restore at 16-SEP-09

 exit

Recovery Manager complete.

 

create spfile='/app/oracle/product/11.1.0/db_1/dbs/spfileTEST.ora' from pfile='/home/oracle/john.ora';

File created.
shutdown immediate; 
startup

show parameter control

control_files  /app/oracle/TEST/control01.ctl , /app/oracle/TEST/control02.ctl 

All that is left is to recreate the temp tablespace to use filesystem storage.

startup mount;

RMAN> run { set newname for tempfile 1 to '/app/oracle/TEST/temp01.dbf'; switch tempfile all;}

executing command:

SET NEWNAME renamed tempfile 1 to /app/oracle/TEST/temp01.dbf in control file  rman target /

 TEST (not mounted) using target database control file instead of recovery catalog RMAN> restore controlfile from '+DATA/test/controlfile/current.303.697650047';

Starting restore at 16-SEP-09 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=69 device type=DISK channel ORA_DISK_1:

copied control file copy output file

name=/app/oracle/TEST/control01.ctl output file name=/app/oracle/TEST/control02.ctl

Finished restore at 16-SEP-09

All should be complete now. As I said, this is just a hint on what actions to take and the sql scripts should be useful if you have lots of datafiles.

All that is left is to create a new  block_change_tracking file and the ASM storage can be released.

Oracle 11G Release 2 available (Linux only though)

Linux only at the moment

 http://www.oracle.com/technology/products/database/oracle11g/index.html

  11GR2 documentation

 http://www.oracle.com/pls/db112/homepage

 11GR2 new features

 http://download.oracle.com/docs/cd/E11882_01/server.112/e10881/toc.htm

  new features index page shows the following (of which I will be looking at ASM first)

 1.1 Application Development

• • 1.1.1 Oracle Application Express
    • 1.1.1.1 Application Date Format
    • 1.1.1.2 Custom Themes
    • 1.1.1.3 Declarative BLOB Support
    • 1.1.1.4 Documented JavaScript Libraries
    • 1.1.1.5 Enhanced Report Printing
    • 1.1.1.6 Forms Conversion
    • 1.1.1.7 Improved Security
    • 1.1.1.8 Interactive Reporting Region
    • 1.1.1.9 Runtime-Only Installation
  • 1.1.2 Other General Development Features
    • 1.1.2.1 Support WITH HOLD Option for CURSOR DECLARATION in Pro*C
    • 1.1.2.2 Pro*C Support for 8-Byte Native Numeric Host Variable for INSERT and FETCH
    • 1.1.2.3 Pro*COBOL Support for 8-Byte Native Numeric Host Variable for INSERT and FETCH
    • 1.1.2.4 JDBC Support for Time Zone Patching
    • 1.1.2.5 JDBC Support for SecureFile Zero-Copy LOB I/O and LOB Prefetching
    • 1.1.2.6 OCI Support for 8-Byte Integer Bind/Define
• 1.2 Availability
  • 1.2.1 Backup and Recovery
    • 1.2.1.1 Automatic Block Repair
    • 1.2.1.2 Backup to Amazon Simple Storage Service (S3) Using OSB Cloud Computing
    • 1.2.1.3 DUPLICATE Without Connection to Target Database
    • 1.2.1.4 Enhanced Tablespace Point-In-Time Recovery (TSPITR)
    • 1.2.1.5 New DUPLICATE Options
    • 1.2.1.6 New SET NEWNAME Clauses and Format Options
    • 1.2.1.7 Tablespace Checks in DUPLICATE
  • 1.2.2 Online Application Maintenance and Upgrade
    • 1.2.2.1 Edition-based Redefinition
    • 1.2.2.2 Enhance CREATE or REPLACE TYPE to Allow FORCE
    • 1.2.2.3 Fine-Grained Dependencies for Triggers
    • 1.2.2.4 IGNORE_ROW_ON_DUPKEY_INDEX Hint for INSERT Statement
  • 1.2.3 Oracle Data Guard
    • 1.2.3.1 Compressed Table Support in Logical Standby Databases and Oracle LogMiner
    • 1.2.3.2 Configurable Real-Time Query Apply Lag Limit
    • 1.2.3.3 Integrated Support for Application Failover in a Data Guard Configuration
    • 1.2.3.4 Support Up to 30 Standby Databases
• 1.3 Business Intelligence and Data Warehousing
  • 1.3.1 Improved Analytics
    • 1.3.1.1 Analytic Functions 2.0
    • 1.3.1.2 Recursive WITH Clause
  • 1.3.2 Improved Data Loading
    • 1.3.2.1 EXECUTE Privilege for DIRECTORY Objects
    • 1.3.2.2 Preprocessing Data for ORACLE_LOADER Access Driver in External Tables
  • 1.3.3 Improved Partitioning
    • 1.3.3.1 Allow Virtual Columns in the Primary Key or Foreign Key for Reference Partitioning
    • 1.3.3.2 System-Managed Indexes for List Partitioning
  • 1.3.4 Improved Performance and Scalability
    • 1.3.4.1 In-Memory Parallel Execution
    • 1.3.4.2 Minimal Effort Parallel Execution – Auto Degree of Parallelism (DOP) and Queuing
    • 1.3.4.3 The DBMS_PARALLEL_EXECUTE Package
    • 1.3.4.4 Significant Performance Improvement of On-Commit Fast Refresh
  • 1.3.5 Oracle Warehouse Builder
    • 1.3.5.1 Advanced Find Support in Mapping Editor
    • 1.3.5.2 Business Intelligence Tool Integration
    • 1.3.5.3 Copy and Paste of Operators and Attributes in Mapping Editor
    • 1.3.5.4 Current Configuration Dropdown List in Design Center Toolbar
    • 1.3.5.5 Enhanced Support for Flat File Imports
    • 1.3.5.6 Enhanced Table Function Support
    • 1.3.5.7 Experts Available in Editor Menu
    • 1.3.5.8 Expression Editing in Operator Edit Dialog
    • 1.3.5.9 Grouping and Spotlighting of Objects in Mapping Editor
    • 1.3.5.10 Improved Management of Locations Registered in Multiple Control Centers
    • 1.3.5.11 Improved User Interface for Managing Locations
    • 1.3.5.12 Key Lookup Operator Enhancements
    • 1.3.5.13 Mapping Debugger Enhancements
    • 1.3.5.14 New JDeveloper-Style User Interface
    • 1.3.5.15 Operator References Included in Generated PL/SQL Code
    • 1.3.5.16 Quick Mapper
    • 1.3.5.17 Repository Browser Changes
    • 1.3.5.18 Simplified Oracle Warehouse Builder Repository Upgrades
    • 1.3.5.19 Support for Extracting Data From Tables Containing LONG Data Type
    • 1.3.5.20 Support for Subqueries in Join Operator
• 1.4 Clustering
  • 1.4.1 Oracle Real Application Clusters Ease-of-Use
    • 1.4.1.1 Configuration Assistants Support New Oracle RAC Features
    • 1.4.1.2 Enhanced Cluster Verification Utility
    • 1.4.1.3 Integration of Cluster Verification Utility and Oracle Universal Installer
    • 1.4.1.4 Cluster Time Service
    • 1.4.1.5 Oracle Cluster Registry (OCR) Enhancements
    • 1.4.1.6 Grid Plug and Play (GPnP)
    • 1.4.1.7 Oracle Restart
    • 1.4.1.8 Policy-Based Cluster and Capacity Management
    • 1.4.1.9 Improved Clusterware Resource Modeling
    • 1.4.1.10 Role-Separated Management
    • 1.4.1.11 Agent Development Framework
    • 1.4.1.12 Zero Downtime Patching for Oracle Clusterware and Oracle RAC
    • 1.4.1.13 Enterprise Manager-Based Clusterware Resource Management
    • 1.4.1.14 Enterprise Manager Provisioning for Oracle Clusterware and Oracle Real Application Clusters
    • 1.4.1.15 Enterprise Manager Support for Grid Plug and Play
    • 1.4.1.16 Enterprise Manager Support for Oracle Restart
    • 1.4.1.17 Configuration Assistant Support for Removing Oracle RAC Installations
    • 1.4.1.18 Oracle Universal Installer Support for Removing Oracle RAC Installations
    • 1.4.1.19 Improved Deinstallation Support With Oracle Universal Installer
    • 1.4.1.20 Downgrading Database Configured With DBControl
    • 1.4.1.21 Oracle Restart Integration with Oracle Universal Installer
    • 1.4.1.22 Out-of-Place Oracle Clusterware Upgrade
    • 1.4.1.23 OUI Support for Out-of-Place Oracle Clusterware Upgrade
    • 1.4.1.24 Server Control (SRVCTL) Enhancements
    • 1.4.1.25 Server Control (SRVCTL) Enhancements to Support Grid Plug and Play
    • 1.4.1.26 SRVCTL Support for Single-Instance Database in a Cluster
    • 1.4.1.27 Universal Connection Pool (UCP) Integration with Oracle Data Guard
    • 1.4.1.28 UCP Integration With Oracle Real Application Clusters
    • 1.4.1.29 Universal Connection Pool (UCP) for JDBC
    • 1.4.1.30 Java API for Oracle RAC FAN High Availability Events
• 1.5 Database Overall
  • 1.5.1 General
    • 1.5.1.1 Flashback Data Archive Support for DDLs
    • 1.5.1.2 Instance Caging
  • 1.5.2 Improvements to Oracle Scheduler
    • 1.5.2.1 E-mail Notification
    • 1.5.2.2 File Watcher
    • 1.5.2.3 Multiple Destination Jobs
    • 1.5.2.4 Remote Database Jobs
  • 1.5.3 Improvements to Utilities
    • 1.5.3.1 Data Pump Legacy Mode
  • 1.5.4 IPv6 Support
    • 1.5.4.1 Complete IPv6 Support for JDBC Thin Clients
    • 1.5.4.2 Complete IPv6 Support for JVM and Java Debuggers
• 1.6 Diagnosability
  • 1.6.1 Support Workbench
    • 1.6.1.1 Enterprise Manager Support Workbench for ASM
• 1.7 Performance
  • 1.7.1 General Server Performance
    • 1.7.1.1 Stored Outlines Migration to SQL Plan Management
    • 1.7.1.2 Client Result Cache Using Table Annotations Support
    • 1.7.1.3 Support 4 KB Sector Disk Drives
• 1.8 Security
  • 1.8.1 Audit Data Management
    • 1.8.1.1 Audit Trail Cleanup
  • 1.8.2 Encryption Key Management
    • 1.8.2.1 Tablespace Master Key Rekey
• 1.9 Server Manageability
  • 1.9.1 Automatic Storage Management for All Data
    • 1.9.1.1 ASM Cluster File System (ACFS)
    • 1.9.1.2 ASM Dynamic Volume Manager (DVM)
    • 1.9.1.3 ASM FS Snapshot
    • 1.9.1.4 Oracle Cluster Registry (OCR) and Voting Disk on ASM
    • 1.9.1.5 ASM Intelligent Data Placement
    • 1.9.1.6 ASM Storage Management Configuration Assistant
    • 1.9.1.7 Automatic Storage Management (ASM) File Access Control
    • 1.9.1.8 ASMCMD Command Extensions
    • 1.9.1.9 Enterprise Manager Support for ASM Cluster File System (ACFS)
    • 1.9.1.10 Enterprise Manager Integration for ASM File Access Control
  • 1.9.2 Database Management
    • 1.9.2.1 EMCA Supports New Oracle RAC Configuration for Enterprise Manager
    • 1.9.2.2 Patch Application with DBControl
    • 1.9.2.3 Automatic Patching of Time Stamp With Time Zone Data
    • 1.9.2.4 Prevent Data Loss for Time Zone with Local Time Zone Data Type
    • 1.9.2.5 Segment Creation on Demand
    • 1.9.2.6 Zero-Size Unusable Indexes and Index Partitions
    • 1.9.2.7 Metadata SXML Comparison Tool
    • 1.9.2.8 Compare Period Report
    • 1.9.2.9 Compare SQL Tuning Sets
    • 1.9.2.10 Enable Sampling for Active Data Guard
    • 1.9.2.11 Exadata Simulation
    • 1.9.2.12 Global Oracle RAC ASH Report + ADDM Backwards Compatibility
    • 1.9.2.13 Oracle MTS and Streams Support
    • 1.9.2.14 Parallel Query + Alt Plan Recording, Export STS Enhancements
    • 1.9.2.15 Synchronization Controls
• 1.10 Unstructured Data Management
  • 1.10.1 Enhanced Oracle Multimedia and DICOM Support
    • 1.10.1.1 Attribute Extraction of Requested Attributes Only
    • 1.10.1.2 Client-Side DICOM Attribute Extraction
    • 1.10.1.3 DICOM Enhancements
    • 1.10.1.4 Watermarking and Image Processing Enhancements
  • 1.10.2 Enhanced Oracle Spatial Support
    • 1.10.2.1 3D Visualization Framework
    • 1.10.2.2 Network Data Model Enhancements
    • 1.10.2.3 New GeoRaster JAVA API
    • 1.10.2.4 Raster Reprojections and Ground Control Point-Based (GCP) Georeferencing
    • 1.10.2.5 Routing and Geocoding Enhancements
  • 1.10.3 Oracle SecureFiles
    • 1.10.3.1 Database File System (DBFS)
    • 1.10.3.2 LZO Support for SecureFiles
  • 1.10.4 Oracle XML DB Scale and Performance Improvements
    • 1.10.4.1 Binary XML Enhancements
    • 1.10.4.2 Oracle XML DB Repository Performance Improvements and Guidelines

1.10.4.3 XMLIndex Enhancements

Performing calculations in unix

Normally if I working on a database and want perform simple calculations I use the Oracle database rather than starting up the windows calculator.

select 6*1024*1024 from dual;

6*1024*1024
-----------
    6291456
 

However I recently found out a method of using unix itself (I am sure there are many more options other than this one but I find this quite easy)

man bc gives

bc is an interactive processor for a language that resembles C but provides unlimited-precision arithmetic.  It takes input from any  files given, then reads the standard input.

In usage

/home/oracle $bc
6*1024*1024
6291456
quit

Arithmetic operators are shown in the normal manner + – * / % ^ and there are lots of functions available such as sine, cosine and log, although I would be likely to use a calculator if I wanted to use these.

Procedure to kill a session

On the Oracle-L mailing list a user requested a procedure to allow a developer to kill his own process. This contributed quite a few comments which I will list below and I posted a method that I use when I have the same requirement.

To create the stored procedure logon as a sysdba through sqlplus and explicitly grant the required rights to system and then create the procedure.

grant alter system to system;
grant select on sys.v_$session to system;

create or replace procedure system.killsession (killsid IN VARCHAR2)
as
killserial   varchar2(20);

begin
killserial:='none';
select serial#
into killserial
from sys.v_$session
where sid = killsid
and username = 'WES';

execute immediate 'alter system kill session ''' || to_char (killsid) || ', ' || to_char (killserial) || '''' ;

EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('The SID ' || killsid || ' does not exist or cannot be killed');

end ;

Grant execute on the procedure to the user

For example, userxxx needs access to kill sessions so you would type the following:-

grant execute on system.killsession to userxxx;

The user needs to know the sid of the session being killed. For example, if a user needs to kill a session with a SID of 140 they would type:-

set serveroutput on
exec system.killsession ('140');

If they get the following error then there is no SID with a username of WES currently connected:-

The SID 140 does not exist or cannot be killed

  The various thoughts on allowing such a procedure centred on two strands, security and practical considerations

 

Security issues

• Why do they need to kill jobs, what is causing the issue
  What will developers want next
  Sarbanes-Oxley considerations

Practical

• How do you know the developer will kill one of his own processes and not a co-worker (think apps or sysadm job)
  Can you use resource profiles to limit CPU usage
  Use a standby to allow unrestricted resource usage

My view is that in certain development environments, for certain users it is reasonable to allow them to kill a process that is running rogue and as such I am happy to provide such a facility. This would not be allowed either unrestricted or in any sort of controlled environment such as production and pre-production.