Rman duplicating an oracle 11g database in active state on the same host

I already published a post about rman> duplicate... a couple of years ago (Rman duplicating an oracle database in a new sid two host scenario), still on 10g at that time and using a backup set being transfered to a new host. With 11g, however, rman> duplicate... offers another option to not only restore from a source backup, leaving source online, but from an up and running database (with takes archivelog mode and some rman catalogued entries in the control file or catalogue nevetheless). Following below therefore, I’m going to show the do’s for an rman> duplicate ... from active database... in a same host scenario, on windows again, using orapwd and oradim as well as lsnrctl this time. The main difference, however, is employing the spfile clause of the duplicate command, such that rman will set up the destination spfile on its own. Only some file name mappings, actually like before, need to be specified. My main reference to review any new features was Duplicating a Database from the oracle 11g1 docs, other references, concerning errors that showed up underway, will be given below.

Ok, working on the same host, nothing is due to be done for software installation and stuff and we can immediately set up the new instance (note that source will be denoted tgt, for target and the destination aux for auxiliary, respectively). Firstly, we create a new password file for destination, with the same sysdba password as on source.

cd /d e:\oracle\product\11.2.0\dbhome_1\database
orapwd file=PWDAUX.ora ignorecase=y force=y

Loop over sqlplus connection identifiers from in dos shell

Nearly at the same season last year, I wrote about ways and uses of piping sqlplus commands into a sqlplus session from a dos shell script (see : Piping newlined commands into sqlplus from a dos shell). The pattern worked fairly well so far such that I employed the technique more often lately to deploy code changes around database instances. However, when the piping stuff relieved me from writing that show errors and exit over and over again for every sql-script, I was still forced to duplicate script code over any database identifier and database session in action.

There was a point, eventually, when I felt I need to take this further, introduce a loop like in any programming language, in fact suffer this dos shell syntax quirksmode (a ss64 syntax redirection article was of great help) and just do it. Ok, it took some time to grab this enabledelayedexpansion thing, the array syntax and so on but here you go. Regard the doubled percent signs prefixing the loop variable, the array for the tns variable and again, the tremendous clear text password use within a script. Ahem, yes, compared to the antecessor article, you do not need the ampersand sign anymore, when the individual echo comands occupy own distinct lines (changed it over there).

@echo off
setlocal enabledelayedexpansion
set USR=joe
set PWD=joe
set TNS=(db1 db2)

chcp 1252
cd /D d:\database

for %%I in %TNS% do (
  (echo set timing off
    echo select '%USR%/%%I' as connection from dual;
    echo @package1.pck
    echo @package2.sql
    echo @package3.sql
    echo exit
  ) | sqlplus -s %USR%/%PWD%@%%I
)

Have fun, Peter

Piping newlined commands into sqlplus from a dos shell

sqlplus indeed does not offer a wealth of options on the command line. When it comes to running sqlplus in a dos shell the prospects even go worse for executing some batch logic outside the sql script itself (I still did not manage to find me some time to learn more abount power shell, maybe things work out smarter there). Say, for example, you want to deploy a package body to a number of instances but have no option to inject the sqlplus commands show errors and exit into the package body file. All you can do that far is setting up a batch script, probably unsing the “-s” and “@” options of sqlplus, containing a three-liner for each and every instance like so:

sqlplus -s usr/pwd@tns @./package.sql
show errors
exit
-->
Package Body wurde erstellt.
Abgelaufen: 00:00:00.42
show errors
Keine Fehler.
exit

For a large number of instances, the actually (my point) superfluous two lines of show errors and exit may lead to tedious batch scripts containing almost redundant commands. Knowing how to pipe commands into scripts from un*x, I investigated options to behave comparably on windows and dos shell. Not some 15 minutes later, see the refs below, i came up with the following approach that makes the three-liner a one-liner (like that), takes no change against the package body file whatsoever and even reduces the output by another two lines.

Install/update oracle 11.2.0.3 fat client on windows

This is a short script with notices about the employment of the 11gR2 patch set 2 (aka 11.2.0.3) oracle fat client on windows x64. That is, saying fat client, I’m talking about the classic client side software collection, featuring oci.dll at most as well as other .net and java and the like things.

I’m definitely not talking about the instant client stuff invented with oracle releases 10g or so.
I already have a running 11.2.0.1 oracle fat client around, such that I’m discussing an update here but quite a lot of information will also be interesting iff you’re planning a fresh installation of the 11.2.0.3 oracle fat client.
Finally, the deployment platform in question is windows 7 x64, which takes importance when examining the different patching strategies that oracle does meanwhile run for *ux (PSU/CSU) and win (none such) plattforms. Let’s get started.

Downloading the necessary files

Your first spot to start from will surely target to Oracle Database Software Downloads where you’ll learn that any 11.2.0.3 related software is only available via Oracle Support.

Unlocking oci.dll and friends when upgrading or patching oracle

The problem of oracle dll files being still active (and locked against modification) inspite of a clear shutdown of all oracle services on windows has been around for lightyears it seems.

What changes, though, is the net of dependencies that has to be checked prior to an upgrade or patch execution to find the processes that actually keep hooking up the dll’s in question. I was hit again lately, when trying to apply the server 11.2.0.3 Patch 18 (the APR2013 bundle patch) on win x64. The infamous error message on executing opatch apply read:

e:\_downloads\p16345834_112030_MSWIN-x86-64\16345834>opatch apply
Oracle Interim Patch-Installationsprogramm Version 11.2.0.3.3
Oracle Home       : e:\oracle\product\11.2.0\dbhome_2
Central Inventory : C:\Program Files\Oracle\Inventory
...
Verifying environment and performing prerequisite checks...
...
Prerequisite check "CheckActiveFilesAndExecutables" failed. The details are:
Following files are active :
e:\oracle\product\11.2.0\dbhome_2\bin\oci.dll
...
Recommended actions: OPatch needs to modify files which are being used by some processes.
OPatch failed with error code = 41