This site has been destroyed by Google forced upgrade to new way of WEB site.
All files links are not working. Many images has been lost in conversation.
Have to edit 190 pages manually. Will try to do ASAP but for this I need time ...
THANK YOU GOOGLE !

Thursday, September 16, 2010

Important Changes to Oracle Database Patch Sets

When I tried to download last patch for Linux x64 (11.2.0.2) I was astonished when I saw 7 files in total 5GB size! I said WTH, what is that! Then I read that Oracle is changing patch policy which was lasting for more then 3 decades. Here is copy/paste text that might be interesting to many DBA's.

What has changed?

Packaging: Starting with the first patch set for Oracle Database 11g Release 2 (11.2.0.2), Oracle Database patch sets are full installations of the Oracle Database software. In past releases, Oracle Database patch sets consisted of a set of files that replaced files in an existing Oracle home. Beginning with Oracle Database 11g Release 2, patch sets are full installations that replace existing installations.

Because Oracle Database 11g Release 2 and later patch sets are a full installation, Oracle patch sets are now released as several installation bundles, just as are the base releases:
  • Oracle Database Server
  • Oracle Grid Infrastructure
  • Oracle Database Client
  • Companion/examples
  • Oracle Gateways
Simplified new installation: Because the release 11.2.0.2 patch set is a full installation package, if you are installing Oracle Database on a system without an existing Oracle Database installation, then you can simply install the release 11.2.0.2 patch set. You are no longer required to install the base release, and then apply the patch set.

Upgrade process:
Beginning with the release 11.2.0.2 patch set, you have two ways to apply a patch set:
  • Out-of-place upgrade – This is Oracle's recommended way to apply a patch set. You install the patch set into a new, separate Oracle home location. After you install the patch upgrade, you then migrate the Oracle Database from the older Oracle home. The patch set upgrade is now the same process to upgrade from one version to another. Oracle recommends that you perform an out-of-place patch set upgrade, because this patch set application option requires much less downtime, and is safer because it does not require patching an ORACLE_HOME that is already being used in production. However, you must ensure that you have sufficient free disk space to accommodate two Oracle home directories at the same time.
  • In-place upgrade – You install the patch set into an existing Oracle home location. Oracle recommends that you select this option only if you do not have sufficient free disk space to perform an out-of-place upgrade, as the upgrade removes the existing Oracle installation. This patch option requires less disk space, but requires more time, and is riskier, because if you encounter an installation failure, then you must recover the entire existing Oracle home from a backup. If you choose this more risky option, then before you begin the patch installation, complete the following tasks:
    • Make a complete backup of your existing Oracle home
    • Read through the entire Upgrade Guide section dealing with in-place upgrades
For this (11.2.0.2) case those separate files looks like:
Installation Type Zip File
Oracle Database (includes Oracle Database and Oracle RAC)
Note: you must download both zip files to install Oracle Database.
p10098816_112020_platform_1of7.zip
p10098816_112020_platform_2of7.zip
Oracle Grid Infrastructure (includes Oracle ASM, Oracle Clusterware, and Oracle Restart)
p10098816_112020_platform_3of7.zip
Oracle Database Client
p10098816_112020_platform_4of7.zip
Oracle Gateways
p10098816_112020_platform_5of7.zip
Oracle Examples
p10098816_112020_platform_6of7.zip
Deinstall
p10098816_112020_platform_7of7.zip

The End

With this new changes DBAs will be really thankful, because it will make patching process more easy and tomorrow possible for scripting way of update, which was until now just a dream.

Beside that this slightly reminds me on Microsoft, I strongly hope that their "bad habits" with "new features" in SPs will not be translated in Oracle software.

Cheers!

Monday, September 13, 2010

OPatch failed with error code = 74 (Windows)

Recently I had to patch one 10g on Win x64. As I', pretty new to Windows and Oracle environments, I thought that shutting down all Oracle based services should be enough step for patching but I was wrong.
When I tried to apply patch I got:
C:\oracle\install\p8609347_10204_MSWIN-x86-64\8609347>opatch apply
Invoking OPatch 10.2.0.5.0

Oracle Interim Patch Installer version 10.2.0.5.0
Copyright (c) 2010, Oracle Corporation.  All rights reserved.


Oracle Home       : C:\oracle\product\10.2.0\db_1
Central Inventory : C:\oracle\Inventory
   from           : n/a
OPatch version    : 10.2.0.5.0
OUI version       : 10.2.0.4.0
OUI location      : C:\oracle\product\10.2.0\db_1\oui
Log file location : C:\oracle\product\10.2.0\db_1\cfgtoollogs\opatch\opatch2010-09-13_08-54-22AM.log

Patch history file: C:\oracle\product\10.2.0\db_1\cfgtoollogs\opatch\opatch_history.txt

ApplySession applying interim patch '8609347' to OH 'C:\oracle\product\10.2.0\db_1'
Interim patch 8609347 is a superset of the patch(es) [  8609347 ] in the Oracle Home
OPatch will rollback the subset patches and apply the given patch.

Running prerequisite checks...
Prerequisite check "CheckActiveFilesAndExecutables" failed.
The details are:

Following files are active :
C:\oracle\product\10.2.0\db_1\bin\oraclient10.dll
C:\oracle\product\10.2.0\db_1\bin\orapls10.dll
C:\oracle\product\10.2.0\db_1\bin\oracommon10.dll
C:\oracle\product\10.2.0\db_1\bin\orageneric10.dll
C:\oracle\product\10.2.0\db_1\rdbms\admin\oraclient10.sym
C:\oracle\product\10.2.0\db_1\rdbms\admin\orapls10.sym
C:\oracle\product\10.2.0\db_1\rdbms\admin\oracommon10.sym
C:\oracle\product\10.2.0\db_1\rdbms\admin\orageneric10.sym
C:\oracle\product\10.2.0\db_1\bin\oraimr10.dll
C:\oracle\product\10.2.0\db_1\bin\oraplp10.dll
C:\oracle\product\10.2.0\db_1\rdbms\admin\oraplp10.sym
C:\oracle\product\10.2.0\db_1\bin\oracle.exe
C:\oracle\product\10.2.0\db_1\rdbms\admin\oracle.sym
C:\oracle\product\10.2.0\db_1\bin\orajox10.dll
C:\oracle\product\10.2.0\db_1\bin\oranls10.dll
C:\oracle\product\10.2.0\db_1\rdbms\admin\oranls10.sym
C:\oracle\product\10.2.0\db_1\bin\orasnls10.dll
C:\oracle\product\10.2.0\db_1\rdbms\admin\orasnls10.sym
C:\oracle\product\10.2.0\db_1\bin\oran10.dll
C:\oracle\product\10.2.0\db_1\bin\oranl10.dll
C:\oracle\product\10.2.0\db_1\bin\orannzsbb10.dll
C:\oracle\product\10.2.0\db_1\bin\oracore10.dll
C:\oracle\product\10.2.0\db_1\rdbms\admin\oracore10.sym
C:\oracle\product\10.2.0\db_1\bin\orahasgen10.dll
C:\oracle\product\10.2.0\db_1\bin\orahasgen10.dll
C:\oracle\product\10.2.0\db_1\rdbms\admin\orahasgen10.sym
C:\oracle\product\10.2.0\db_1\bin\oraocr10.dll
C:\oracle\product\10.2.0\db_1\rdbms\admin\oraocr10.sym
C:\oracle\product\10.2.0\db_1\bin\oraocrb10.dll
C:\oracle\product\10.2.0\db_1\rdbms\admin\oraocrb10.sym
C:\oracle\product\10.2.0\db_1\bin\oraldapclnt10.dll
C:\oracle\product\10.2.0\db_1\bin\oraxml10.dll
ApplySession failed during prerequisite checks: Prerequisite check "CheckActiveFilesAndExecutables" failed.
System intact, OPatch will not attempt to restore the system
--------------------------------------------------------------------------------
The following warnings have occurred during OPatch execution:
--------------------------------------------------------------------------------

OPatch failed with error code = 74

C:\oracle\install\p8609347_10204_MSWIN-x86-64\8609347>

The solution

Killing Windows processes that holds some DLLs are not so easy when you have to kill something else then Explorer or there were several blocked DLLS (at it was in mine case).

So I found out nice freeware utility Unlocker which is made for 32 as well as 64 bit Windows versions. It's advantage over Who's Locking?, which I was using until now, is that Unlocker can release all locks on whole directory in one step.

Cheers!

Sunday, September 5, 2010

Irresistable DB Duplicate on 11g

What I like in Oracle and especially it's database technology is ability to recognize user's needs and help them as much as possible in shortest possible period. This thought is not based on Oracle Support (aka Metalink) but on new features that comes with new releases leaving all good untouched. One of the most commented topic in database technology is RMAN and its duplicate command-ability to clone any Oracle database. However, I feel free to write another post on mentioned subject in a little different way: to create out of the box solution for any duplicate situation that might come in ad hock situation.

In 11g version this action can really be fully generalized regarding several improvements in which two are especially highlighted (as can be seen on previous picture):
  1. no need to connect to target database (all data are read from RMAN backup files directly). This gives ability to run DUPLICATE in any situation, with or without target database available!
  2. backup location may be anywhere you like (not necessary as it was in RMAN backup configuration on target database). If you look in mine topic (ORA-19870, ORA-19505, ORA-27041 on Windows 2008/2003) you might see some problems that DBA may face working with 10g on Windows. According this you'll appreciate this features a lot.

1. Configuration/environment

Here are the main data to understand next example:
  • The whole example is made on Windows 2008R2 x64 with Oracle 11gR2 x64 database.
  • Target database (one that is cloned) is "XE" and auxiliary (new replica) is "ORCL".
  • Target and auxiliary box are the same box.
  • ORACLE_HOME is the same what meant that directory structure for auxiliary datafiles are different.
  • Target database is in archive log with enabled flashback option.

2. Pre steps

Rather this steps is not necessary at all, I'll show them to ensure that the whole procedure exists only on shown actions.
This is why I have deleted all current backups, archived logs and backup sets, but in real case you do not have to do that. Shortly commands for that are:
C:\>SET ORACLE_SID=XE

rman target / nocatalog
crosscheck archivelog all;
crosscheck backup;
crosscheck backupset;
delete noprompt archivelog all;
delete noprompt backup;
delete noprompt backupset;

3. Backup target database

First we have to make full database backup with RMAN:
C:\>SET ORACLE_SID=XE

C:\>rman target / nocatalog
RMAN> backup database plus archivelog delete input;

Starting backup at 24.08.10
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=138 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=460 RECID=197 STAMP=727908938
input archived log thread=1 sequence=461 RECID=198 STAMP=727908940
input archived log thread=1 sequence=462 RECID=199 STAMP=727908945
input archived log thread=1 sequence=463 RECID=200 STAMP=727909072
channel ORA_DISK_1: starting piece 1 at 24.08.10
channel ORA_DISK_1: finished piece 1 at 24.08.10
piece handle=C:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\XE\BACKUPSET\2010_08_24\O1_MF_ANNNN_TAG20100824T205752_6785KJP0_.BKP tag=TAG20100824T205752 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=C:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\XE\ARCHIVELOG\2010_08_24\O1_MF_1_460_6785FBWS_.ARC RECID=197 STAMP=727908938
archived log file name=C:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\XE\ARCHIVELOG\2010_08_24\O1_MF_1_461_6785FDW0_.ARC RECID=198 STAMP=727908940
archived log file name=C:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\XE\ARCHIVELOG\2010_08_24\O1_MF_1_462_6785FKXH_.ARC RECID=199 STAMP=727908945
archived log file name=C:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\XE\ARCHIVELOG\2010_08_24\O1_MF_1_463_6785KJ2W_.ARC RECID=200 STAMP=727909072
Finished backup at 24.08.10

Starting backup at 24.08.10
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=C:\ORACLE\PRODUCT\ORADATA\XE\SYSAUX01.DBF
input datafile file number=00001 name=C:\ORACLE\PRODUCT\ORADATA\XE\SYSTEM01.DBF
input datafile file number=00003 name=C:\ORACLE\PRODUCT\ORADATA\XE\UNDOTBS01.DBF
input datafile file number=00006 name=C:\ORACLE\PRODUCT\ORADATA\XE\USERS01.DBF
input datafile file number=00004 name=C:\ORACLE\PRODUCT\ORADATA\XE\APEX.DBF
input datafile file number=00007 name=C:\ORACLE\PRODUCT\ORADATA\XE\TOOLS01.DBF
input datafile file number=00005 name=C:\ORACLE\PRODUCT\ORADATA\XE\OWB.DBF
channel ORA_DISK_1: starting piece 1 at 24.08.10
channel ORA_DISK_1: finished piece 1 at 24.08.10
piece handle=C:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\XE\BACKUPSET\2010_08_24\O1_MF_NNNDF_TAG20100824T205754_6785KLLV_.BKP tag=TAG20100824T205754 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:55
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 24.08.10
channel ORA_DISK_1: finished piece 1 at 24.08.10
piece handle=C:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\XE\BACKUPSET\2010_08_24\O1_MF_NCSNF_TAG20100824T205754_6785O6MY_.BKP tag=TAG20100824T205754 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 24.08.10

Starting backup at 24.08.10
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=464 RECID=201 STAMP=727909193
channel ORA_DISK_1: starting piece 1 at 24.08.10
channel ORA_DISK_1: finished piece 1 at 24.08.10
piece handle=C:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\XE\BACKUPSET\2010_08_24\O1_MF_ANNNN_TAG20100824T205954_6785OBRQ_.BKP tag=TAG20100824T205954 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=C:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\XE\ARCHIVELOG\2010_08_24\O1_MF_1_464_6785O9TG_.ARC RECID=201 STAMP=727909193
Finished backup at 24.08.10

RMAN>

4. Creating PIT point

To identified a time when to restore to, I'll create a table t1 with one record which will hold sysdate of the moment when the record was inserted. This will PIT point.
c:\>sqlplus "/ as sysdba"

SQL> create table t1 (a_date date);

Table created.

SQL> insert into t1 values (sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> select to_char(sysdate,'dd.mm.yyyy hh24:mi:ss') now_is from dual;

NOW_IS
-------------------
24.08.2010 21:03:16

SQL> drop table t1 cascade constraints;

Table dropped.

SQL> 
Now let us create some aditional archived log files that can cover this PIT:
SQL> alter system switch logfile;

System altered.

SQL> 

5. Backup previously created archived logs

Because in first backup I deleted all archived logs, all that happened later should be backed up additionally:
C:\>SET ORACLE_SID=XE

C:\>rman target / nocatalog
RMAN> backup archivelog all;

Starting backup at 24.08.10
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=139 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=465 RECID=202 STAMP=727909720
input archived log thread=1 sequence=466 RECID=203 STAMP=727909770
channel ORA_DISK_1: starting piece 1 at 24.08.10
channel ORA_DISK_1: finished piece 1 at 24.08.10
piece handle=C:\ORACLE\PRODUCT\FLASH_RECOVERY_AREA\XE\BACKUPSET\2010_08_24\O1_MF_ANNNN_TAG20100824T210931_67867C7Y_.BKP tag=TAG20100824T2109
31 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 24.08.10

RMAN>

6. Check backup files and original backup location

Backup files:
c:\oracle\product\flash_recovery_area\XE\BACKUPSET\2010_08_24>dir
 Volume in drive C has no label.
 Volume Serial Number is 084F-8D27

 Directory of c:\oracle\product\flash_recovery_area\XE\BACKUPSET\2010_08_24

24.08.2010.  21:09    <DIR>          .
24.08.2010.  21:09    <DIR>          ..
24.08.2010.  20:57           441.856 O1_MF_ANNNN_TAG20100824T205752_6785KJP0_.BKP
24.08.2010.  20:59            12.288 O1_MF_ANNNN_TAG20100824T205954_6785OBRQ_.BKP
24.08.2010.  21:09         1.452.032 O1_MF_ANNNN_TAG20100824T210931_67867C7Y_.BKP
24.08.2010.  20:49         9.928.704 O1_MF_NCSNF_TAG20100824T204912_678519H6_.BKP
24.08.2010.  20:59         9.928.704 O1_MF_NCSNF_TAG20100824T205754_6785O6MY_.BKP
24.08.2010.  20:59     2.174.009.344 O1_MF_NNNDF_TAG20100824T205754_6785KLLV_.BKP
               6 File(s)  2.195.772.928 bytes
               2 Dir(s)   7.442.173.952 bytes free

c:\oracle\product\flash_recovery_area\XE\BACKUPSET\2010_08_24>
Archived log files:
c:\oracle\product\flash_recovery_area\XE\ARCHIVELOG\2010_08_24>dir
 Volume in drive C has no label.
 Volume Serial Number is 084F-8D27

 Directory of c:\oracle\product\flash_recovery_area\XE\ARCHIVELOG\2010_08_24

24.08.2010.  21:09    <DIR>          .
24.08.2010.  21:09    <DIR>          ..
24.08.2010.  21:08         1.444.864 O1_MF_1_465_67865R4R_.ARC
24.08.2010.  21:09             6.144 O1_MF_1_466_67867BP4_.ARC
               2 File(s)      1.451.008 bytes
               2 Dir(s)   7.442.173.952 bytes free

c:\oracle\product\flash_recovery_area\XE\ARCHIVELOG\2010_08_24>

7. Copy backup files to different location

To prove that backup can be used from non default backup location, I'll copy all files from point 6. to new location "d:\Temp\all_backup_data>".
d:\Temp\all_backup_data>dir
 Volume in drive D is New Volume
 Volume Serial Number is 1E97-BEEF

 Directory of d:\Temp\all_backup_data

24.08.2010.  21:20    <DIR>          .
24.08.2010.  21:20    <DIR>          ..
24.08.2010.  21:08         1.444.864 O1_MF_1_465_67865R4R_.ARC
24.08.2010.  21:09             6.144 O1_MF_1_466_67867BP4_.ARC
24.08.2010.  20:57           441.856 O1_MF_ANNNN_TAG20100824T205752_6785KJP0_.BKP
24.08.2010.  20:59            12.288 O1_MF_ANNNN_TAG20100824T205954_6785OBRQ_.BKP
24.08.2010.  21:09         1.452.032 O1_MF_ANNNN_TAG20100824T210931_67867C7Y_.BKP
24.08.2010.  20:49         9.928.704 O1_MF_NCSNF_TAG20100824T204912_678519H6_.BKP
24.08.2010.  20:59         9.928.704 O1_MF_NCSNF_TAG20100824T205754_6785O6MY_.BKP
24.08.2010.  20:59     2.174.009.344 O1_MF_NNNDF_TAG20100824T205754_6785KLLV_.BKP
24.08.2010.  21:20                12 spfile.ora
               9 File(s)  2.197.223.948 bytes
               2 Dir(s)  52.156.399.616 bytes free

d:\Temp\all_backup_data>
In this way you can do the same when auxiliary box is not the same as target as well.

8. Pre cloning steps

Before we start duplicate, on Windows platform I have to:
  1. set new environment:
    d:\>SET ORACLE_SID=ORCL
    d:\>SET ORACLE_HOME=c:\oracle\product\11.2.0\db_1
    
  2. create %ORACLE_HOME%\DATABASE\initORCL.ora file with only one line content:
    db_name=ORCL  
    But I prefer to add two lines more:
    job_queue_processes=0
    compatible=11.1.0
    
    First one would stop any job that might want to start after clone (what we do not in many cases) and second will prevent "ORA-19726: cannot plug data [string] at level [string]" into database running at compatibility level string"
    All others init.ora entries for cloned database will be done automatically by RMAN duplicate command!
  3. create new ORCL instance by creating it through Windows service
    d:\>oradim -new -sid %ORACLE_SID% -intpwd newpwd -startmode M
    Instance created.
    
    d:\>
    
    If you are on Linux this step must be skipped.
  4. Start instance in nomount mode with previously create pfile.
    d:\>sqlplus "/ as sysdba"
    
    SQL*Plus: Release 11.2.0.1.0 Production on Uto Kol 24 21:25:51 2010
    
    Copyright (c) 1982, 2010, Oracle.  All rights reserved.
    
    Connected to an idle instance.
    
    SQL> startup nomount pfile='%ORACLE_HOME%\database\initORCL.ora';
    ORACLE instance started.
    
    Total System Global Area  217157632 bytes
    Fixed Size                  2174320 bytes
    Variable Size             159384208 bytes
    Database Buffers           50331648 bytes
    Redo Buffers                5267456 bytes
    SQL>
    

9. RMAN duplicate

Now new instance is ready to be cloned. Important part is at the very beginning of starting RMAN:
c:\>rman auxiliary /
where you see that RMAN is not connected to target database as it was in the past! The rest of the RMAN script that will be used for cloning looks like:
RUN {
     ALLOCATE AUXILIARY CHANNEL aux1 type DISK;
     SET UNTIL TIME "to_date( '24.08.2010 21:03:16','dd.mm.yyyy hh24:mi:ss')";
     DUPLICATE DATABASE TO "ORCL"
       DB_FILE_NAME_CONVERT =('c:\oracle\product\oradata\XE\','C:\oracle\product\oradata\ORCL\')
       PFILE='c:\oracle\product\11.2.0\db_1\database\initORCL.ora'
       LOGFILE
         GROUP 1 ('C:\ORACLE\PRODUCT\ORADATA\ORCL\REDO01.LOG') SIZE 50M,
         GROUP 2 ('C:\ORACLE\PRODUCT\ORADATA\ORCL\REDO02.LOG') SIZE 50M,
         GROUP 3 ('C:\ORACLE\PRODUCT\ORADATA\ORCL\REDO03.LOG') SIZE 50M
       BACKUP LOCATION 'd:\Temp\all_backup_data'
     ;
     RELEASE CHANNEL aux1; 
} 
Where:
  • SET UNTIL TIME represent the same moment as sysdate previously inserted in table t1.
  • DB_FILE_NAME_CONVERT option remap directories
  • BACKUP LOCATION sets dynamically new backup location (different from original!)
Here the whole log of RMAN duplicate operation:
c:\>rman auxiliary /

Recovery Manager: Release 11.2.0.1.0 - Production on Uto Kol 24 21:29:55 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to auxiliary database: ORCL (not mounted)
---------------------------------------
RMAN> RUN {
2>      ALLOCATE AUXILIARY CHANNEL aux1 type DISK;
3>      SET UNTIL TIME "to_date( '24.08.2010 21:03:16','dd.mm.yyyy hh24:mi:ss')";
4>      DUPLICATE DATABASE TO "ORCL"
5>        DB_FILE_NAME_CONVERT =('c:\oracle\product\oradata\XE\','C:\oracle\product\oradata\ORCL\')
6>        PFILE='c:\oracle\product\11.2.0\db_1\database\initORCL.ora'
7>        LOGFILE
8>          GROUP 1 ('C:\ORACLE\PRODUCT\ORADATA\ORCL\REDO01.LOG') SIZE 50M,
9>          GROUP 2 ('C:\ORACLE\PRODUCT\ORADATA\ORCL\REDO02.LOG') SIZE 50M,
10>          GROUP 3 ('C:\ORACLE\PRODUCT\ORADATA\ORCL\REDO03.LOG') SIZE 50M
11>        BACKUP LOCATION 'd:\Temp\all_backup_data'
12>      ;
13>      RELEASE CHANNEL aux1;
14> }

allocated channel: aux1
channel aux1: SID=96 device type=DISK

executing command: SET until clause

Starting Duplicate Db at 24.08.10

contents of Memory Script:
{
   sql clone "create spfile from memory";
}
executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     217157632 bytes

Fixed Size                     2174320 bytes
Variable Size                159384208 bytes
Database Buffers              50331648 bytes
Redo Buffers                   5267456 bytes
allocated channel: aux1
channel aux1: SID=95 device type=DISK

contents of Memory Script:
{
   sql clone "alter system set  db_name =
 ''XE'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''ORCL'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile from  'D:\TEMP\all_backup_data\O1_MF_NCSNF_TAG20100824T205754_6785O6MY_.BKP';
   alter clone database mount;
}
executing Memory Script

sql statement: alter system set  db_name =  ''XE'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''ORCL'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area     217157632 bytes

Fixed Size                     2174320 bytes
Variable Size                159384208 bytes
Database Buffers              50331648 bytes
Redo Buffers                   5267456 bytes
allocated channel: aux1
channel aux1: SID=95 device type=DISK

Starting restore at 24.08.10

channel aux1: restoring control file
channel aux1: restore complete, elapsed time: 00:00:01
output file name=C:\ORACLE\PRODUCT\11.2.0\DB_1\DATABASE\CTL1ORCL.ORA
Finished restore at 24.08.10

database mounted

contents of Memory Script:
{
   set until scn  6897551;
   set newname for datafile  1 to
 "C:\ORACLE\PRODUCT\ORADATA\ORCL\SYSTEM01.DBF";
   set newname for datafile  2 to
 "C:\ORACLE\PRODUCT\ORADATA\ORCL\SYSAUX01.DBF";
   set newname for datafile  3 to
 "C:\ORACLE\PRODUCT\ORADATA\ORCL\UNDOTBS01.DBF";
   set newname for datafile  4 to
 "C:\ORACLE\PRODUCT\ORADATA\ORCL\APEX.DBF";
   set newname for datafile  5 to
 "C:\ORACLE\PRODUCT\ORADATA\ORCL\OWB.DBF";
   set newname for datafile  6 to
 "C:\ORACLE\PRODUCT\ORADATA\ORCL\USERS01.DBF";
   set newname for datafile  7 to
 "C:\ORACLE\PRODUCT\ORADATA\ORCL\TOOLS01.DBF";
   restore
   clone database
   ;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 24.08.10

channel aux1: starting datafile backup set restore
channel aux1: specifying datafile(s) to restore from backup set
channel aux1: restoring datafile 00001 to C:\ORACLE\PRODUCT\ORADATA\ORCL\SYSTEM01.DBF
channel aux1: restoring datafile 00002 to C:\ORACLE\PRODUCT\ORADATA\ORCL\SYSAUX01.DBF
channel aux1: restoring datafile 00003 to C:\ORACLE\PRODUCT\ORADATA\ORCL\UNDOTBS01.DBF
channel aux1: restoring datafile 00004 to C:\ORACLE\PRODUCT\ORADATA\ORCL\APEX.DBF
channel aux1: restoring datafile 00005 to C:\ORACLE\PRODUCT\ORADATA\ORCL\OWB.DBF
channel aux1: restoring datafile 00006 to C:\ORACLE\PRODUCT\ORADATA\ORCL\USERS01.DBF
channel aux1: restoring datafile 00007 to C:\ORACLE\PRODUCT\ORADATA\ORCL\TOOLS01.DBF
channel aux1: reading from backup piece D:\TEMP\ALL_BACKUP_DATA\O1_MF_NNNDF_TAG20100824T205754_6785KLLV_.BKP
channel aux1: piece handle=D:\TEMP\ALL_BACKUP_DATA\O1_MF_NNNDF_TAG20100824T205754_6785KLLV_.BKP tag=TAG20100824T205754
channel aux1: restored backup piece 1
channel aux1: restore complete, elapsed time: 00:02:45
Finished restore at 24.08.10

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=8 STAMP=727913123 file name=C:\ORACLE\PRODUCT\ORADATA\ORCL\SYSTEM01.DBF
datafile 2 switched to datafile copy
input datafile copy RECID=9 STAMP=727913123 file name=C:\ORACLE\PRODUCT\ORADATA\ORCL\SYSAUX01.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=10 STAMP=727913123 file name=C:\ORACLE\PRODUCT\ORADATA\ORCL\UNDOTBS01.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=11 STAMP=727913124 file name=C:\ORACLE\PRODUCT\ORADATA\ORCL\APEX.DBF
datafile 5 switched to datafile copy
input datafile copy RECID=12 STAMP=727913124 file name=C:\ORACLE\PRODUCT\ORADATA\ORCL\OWB.DBF
datafile 6 switched to datafile copy
input datafile copy RECID=13 STAMP=727913124 file name=C:\ORACLE\PRODUCT\ORADATA\ORCL\USERS01.DBF
datafile 7 switched to datafile copy
input datafile copy RECID=14 STAMP=727913124 file name=C:\ORACLE\PRODUCT\ORADATA\ORCL\TOOLS01.DBF

contents of Memory Script:
{
   set until time  "to_date( '24.08.2010 21:03:16','dd.mm.yyyy hh24:mi:ss')";
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 24.08.10

starting media recovery

archived log for thread 1 with sequence 465 is already on disk as file D:\TEMP\ALL_BACKUP_DATA\O1_MF_1_465_67865R4R_.ARC
channel aux1: starting archived log restore to default destination
channel aux1: restoring archived log
archived log thread=1 sequence=464
channel aux1: reading from backup piece D:\TEMP\ALL_BACKUP_DATA\O1_MF_ANNNN_TAG20100824T205954_6785OBRQ_.BKP
channel aux1: piece handle=D:\TEMP\ALL_BACKUP_DATA\O1_MF_ANNNN_TAG20100824T205954_6785OBRQ_.BKP tag=TAG20100824T205954
channel aux1: restored backup piece 1
channel aux1: restore complete, elapsed time: 00:00:01
archived log file name=C:\ORACLE\PRODUCT\11.2.0\DB_1\RDBMS\ARC0000000464_0697456783.0001 thread=1 sequence=464
channel clone_default: deleting archived log(s)
archived log file name=C:\ORACLE\PRODUCT\11.2.0\DB_1\RDBMS\ARC0000000464_0697456783.0001 RECID=3 STAMP=727913126
archived log file name=D:\TEMP\ALL_BACKUP_DATA\O1_MF_1_465_67865R4R_.ARC thread=1 sequence=465
media recovery complete, elapsed time: 00:00:05
Finished recover at 24.08.10

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount pfile= 'c:\oracle\product\11.2.0\db_1\database\initORCL.ora';
}
executing Memory Script

database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     217157632 bytes

Fixed Size                     2174320 bytes
Variable Size                159384208 bytes
Database Buffers              50331648 bytes
Redo Buffers                   5267456 bytes
allocated channel: aux1
channel aux1: SID=95 device type=DISK
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "ORCL" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP  1 ( 'C:\ORACLE\PRODUCT\ORADATA\ORCL\REDO01.LOG' ) SIZE 50 M ,
  GROUP  2 ( 'C:\ORACLE\PRODUCT\ORADATA\ORCL\REDO02.LOG' ) SIZE 50 M ,
  GROUP  3 ( 'C:\ORACLE\PRODUCT\ORADATA\ORCL\REDO03.LOG' ) SIZE 50 M
 DATAFILE
  'C:\ORACLE\PRODUCT\ORADATA\ORCL\SYSTEM01.DBF'
 CHARACTER SET EE8MSWIN1250


contents of Memory Script:
{
   set newname for tempfile  1 to
 "C:\ORACLE\PRODUCT\ORADATA\ORCL\TEMP01.DBF";
   switch clone tempfile all;
   catalog clone datafilecopy  "C:\ORACLE\PRODUCT\ORADATA\ORCL\SYSAUX01.DBF",
 "C:\ORACLE\PRODUCT\ORADATA\ORCL\UNDOTBS01.DBF",
 "C:\ORACLE\PRODUCT\ORADATA\ORCL\APEX.DBF",
 "C:\ORACLE\PRODUCT\ORADATA\ORCL\OWB.DBF",
 "C:\ORACLE\PRODUCT\ORADATA\ORCL\USERS01.DBF",
 "C:\ORACLE\PRODUCT\ORADATA\ORCL\TOOLS01.DBF";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to C:\ORACLE\PRODUCT\ORADATA\ORCL\TEMP01.DBF in control file

cataloged datafile copy
datafile copy file name=C:\ORACLE\PRODUCT\ORADATA\ORCL\SYSAUX01.DBF RECID=1 STAMP=727913146
cataloged datafile copy
datafile copy file name=C:\ORACLE\PRODUCT\ORADATA\ORCL\UNDOTBS01.DBF RECID=2 STAMP=727913146
cataloged datafile copy
datafile copy file name=C:\ORACLE\PRODUCT\ORADATA\ORCL\APEX.DBF RECID=3 STAMP=727913146
cataloged datafile copy
datafile copy file name=C:\ORACLE\PRODUCT\ORADATA\ORCL\OWB.DBF RECID=4 STAMP=727913146
cataloged datafile copy
datafile copy file name=C:\ORACLE\PRODUCT\ORADATA\ORCL\USERS01.DBF RECID=5 STAMP=727913146
cataloged datafile copy
datafile copy file name=C:\ORACLE\PRODUCT\ORADATA\ORCL\TOOLS01.DBF RECID=6 STAMP=727913146

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=727913146 file name=C:\ORACLE\PRODUCT\ORADATA\ORCL\SYSAUX01.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=727913146 file name=C:\ORACLE\PRODUCT\ORADATA\ORCL\UNDOTBS01.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=727913146 file name=C:\ORACLE\PRODUCT\ORADATA\ORCL\APEX.DBF
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=727913146 file name=C:\ORACLE\PRODUCT\ORADATA\ORCL\OWB.DBF
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=727913146 file name=C:\ORACLE\PRODUCT\ORADATA\ORCL\USERS01.DBF
datafile 7 switched to datafile copy
input datafile copy RECID=6 STAMP=727913146 file name=C:\ORACLE\PRODUCT\ORADATA\ORCL\TOOLS01.DBF

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 24.08.10

released channel: aux1

RMAN>

10. Checking

After duplicate finish, database is up and running.
D:\>sqlplus "/ as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Uto Kol 24 22:11:28 2010

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select instance_name, status, to_char(STARTUP_TIME,'dd.mm.yyyy hh24:mi:ss') from v$instance;

INSTANCE_NAME    STATUS       TO_CHAR(STARTUP_TIM
---------------- ------------ -------------------
orcl             OPEN         24.08.2010 22:05:44

SQL> 
Let us check for t1 table and it's content:
SQL> select to_char(a_date,'dd.mm.yyyy hh24:mi:ss') was_pit_before from t1;

WAS_PIT_BEFORE
-------------------
24.08.2010 21:03:06

SQL>
Voila!

The End

In one of mine previous posts (Clone 11g ORACLE_HOME (different box, different path) on Linux) I have described how is easy to clone ORACLE_HOME in 11g on Linux. With this topic I think this subject is fully covered and may be use full for someone facing first time with it.

Cheers!

Wednesday, September 1, 2010

ORA-19870, ORA-19505, ORA-27041 on Windows 2008/2003

As I do most of mine Oracle DBA tasks on Linux, when such a person have to deal with Windows and the newest one (2008), some unexpected problems may arise. Even thought I may say that mine experience and understanding of RMAN are well, this topic will show that OS may brings DBA some of unexpected problems. As I was young DBA, I remember that in restore/duplicate procedure, backup location must remain the same as it was on target box. Problem may raise when on auxiliary box there is no such a disk (logical drive). In such a cases Linux has very adequate option with symbolic links (ln -s). On Windows there are generally two mapping drive techniques.
  1. subst
  2. net use
Regardless that Windows 2000 was able to deal properly with subst command, in later Windows versions this option was excluded and only option that remain was net use command. This is very well described in official Oracle document How to Configure RMAN to Write to Shared Drives on Windows NT/2000 (Doc ID 145843.1).

The configuration (environment)

In mine case I was dealing with RMAN online backup without catalog with disk as location. This was because I wanted "one time backup/restore"-just for that one cloning. So I wanted to use DUPLICATE RMAN command for cloning one database to different box.

To understand the problem here is a brief environment/configuration data:
Target database              Auxiliary database
Windows 2003 x64              Windows 2008 x64
ORION              ORION1
192.168.0.11              192.168.0.19
db_name: hcpro              db_name: hcpro
G:\rman_backup\hcpro              C:\oracle\rman_backup\hcpro*
* there was no G disk on auxiliary box!
RMAN configuration on target database.
RMAN> show all;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   'G:\rman_backup\hcpro\df%t_s%s_s%p';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'C:\ORANT\DATABASE\SNCFHCPRO.ORA';
Backup was done properly and without any problems to "G:\rman_backup\hcpro" location on target box. Here is list of backup files on target box:
G:\rman_backup\hcpro>dir g:
 Volume in drive G is Gis
 Volume Serial Number is B2CC-3FB3

 Directory of G:\rman_backup\hcpro

27.08.2010  16:42    <DIR>          .
27.08.2010  16:42    <DIR>          ..
26.08.2010  13:08       622.482.944 DF728053554_S6430_S1
26.08.2010  13:08       583.298.048 DF728053554_S6431_S1
26.08.2010  13:11       652.625.920 DF728053723_S6432_S1
26.08.2010  13:11       591.771.648 DF728053742_S6433_S1
26.08.2010  13:53     8.592.424.960 DF728053918_S6434_S1
26.08.2010  13:40     4.798.545.920 DF728053918_S6435_S1
26.08.2010  13:40        59.817.984 DF728055613_S6436_S1
26.08.2010  13:41        16.515.072 DF728055658_S6437_S1
26.08.2010  13:53         1.802.752 DF728056422_S6438_S1
26.08.2010  13:53         1.573.376 DF728056422_S6439_S1
26.08.2010  14:35         2.523.136 DF728058940_S6441_S1
26.08.2010  14:35         1.243.648 DF728058940_S6442_S1
26.08.2010  14:35           163.328 DF728058942_S6443_S1
26.08.2010  14:38           169.984 DF728059079_S6445_S1
26.08.2010  14:38            20.992 DF728059079_S6446_S1
26.08.2010  14:38            18.432 DF728059082_S6447_S1
27.08.2010  16:42                 9 test.txt
              17 File(s) 15.924.998.153 bytes
               2 Dir(s)  115.000.242.176 bytes free

G:\rman_backup\hcpro>
Notice that volume name of G drive is "Gis".

Then I copied all the backup files to auxiliary box in "c:\oracle\rman_backup\hcpro" directory. Because "G:\rman_backup\hcpro" was RMAN destination, on auxiliary box that directory should looks the same as it was on target box. So here is command that map G: drive on auxiliary box
net use g: \\ORION1\oracle /persistent:no
here is command from auxiliary box drive g that shows backup files:
G:\rman_backup\hcpro>dir g:
 Volume in drive G is OS
 Volume Serial Number is B2CC-3FB3

 Directory of G:\rman_backup\hcpro

27.08.2010  16:42    <DIR>          .
27.08.2010  16:42    <DIR>          ..
26.08.2010  13:08       622.482.944 DF728053554_S6430_S1
26.08.2010  13:08       583.298.048 DF728053554_S6431_S1
26.08.2010  13:11       652.625.920 DF728053723_S6432_S1
26.08.2010  13:11       591.771.648 DF728053742_S6433_S1
26.08.2010  13:53     8.592.424.960 DF728053918_S6434_S1
26.08.2010  13:40     4.798.545.920 DF728053918_S6435_S1
26.08.2010  13:40        59.817.984 DF728055613_S6436_S1
26.08.2010  13:41        16.515.072 DF728055658_S6437_S1
26.08.2010  13:53         1.802.752 DF728056422_S6438_S1
26.08.2010  13:53         1.573.376 DF728056422_S6439_S1
26.08.2010  14:35         2.523.136 DF728058940_S6441_S1
26.08.2010  14:35         1.243.648 DF728058940_S6442_S1
26.08.2010  14:35           163.328 DF728058942_S6443_S1
26.08.2010  14:38           169.984 DF728059079_S6445_S1
26.08.2010  14:38            20.992 DF728059079_S6446_S1
26.08.2010  14:38            18.432 DF728059082_S6447_S1
27.08.2010  16:42                 9 test.txt
              17 File(s) 15.924.998.153 bytes
               2 Dir(s)  115.000.242.176 bytes free

G:\rman_backup\hcpro>
As you see here volume name is "OS", as C disk has!

The problem

When I tried to run duplicate, I got an error:
C:\>rman target sys/xxxxx@HCPRO_RMAN NOCATALOG AUXILIARY /

Recovery Manager: Release 10.2.0.4.0 - Production on Mon Aug 30 08:46:02 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: HCPRO (DBID=4157028123)
using target database control file instead of recovery catalog
connected to auxiliary database: HCPRO (not mounted)

RMAN> RUN {
2>      ALLOCATE AUXILIARY CHANNEL aux1 type DISK;
3>      SET UNTIL TIME "to_date( '20100826 135341','yyyymmdd hh24miss')";
4>      DUPLICATE TARGET DATABASE TO HCPRO
5>        NOFILENAMECHECK
6>        DB_FILE_NAME_CONVERT =('F:\ORADATA\HCPRO\' , 'D:\ORADATA\HCPRO\'
7>                               'G:\ORADATA\HCPRO\' , 'E:\ORADATA\HCPRO\'
8>                              )
9>        PFILE='C:\oracle\product\10.2.0\db_1\database\initHCPRO.ora'
10>        LOGFILE
11>          GROUP 1 ('C:\ORADATA\HCPRO\REDO01.LOG') SIZE 150M,
12>          GROUP 2 ('C:\ORADATA\HCPRO\REDO02.LOG') SIZE 150M,
13>          GROUP 3 ('C:\ORADATA\HCPRO\REDO03.LOG') SIZE 150M
14>      ;
15>      RELEASE CHANNEL aux1;
16> }

allocated channel: aux1
channel aux1: sid=3 devtype=DISK

executing command: SET until clause

Starting Duplicate Db at 30-AUG-10

contents of Memory Script:
{
   set until scn  14251842631;
   set newname for datafile  1 to
 "D:\ORADATA\HCPRO\SYSTEM01.DBF";
   set newname for datafile  2 to
 "D:\ORADATA\HCPRO\UNDOTBS01.DBF";
   set newname for datafile  3 to
 "D:\ORADATA\HCPRO\SYSAUX01.DBF";
   set newname for datafile  4 to
 "E:\ORADATA\HCPRO\GIS01.DBF";
   set newname for datafile  5 to
 "E:\ORADATA\HCPRO\INDEXES01.DBF";
   set newname for datafile  6 to
 "D:\ORADATA\HCPRO\USERS01.DBF";
   set newname for datafile  7 to
 "E:\ORADATA\HCPRO\USERS_2_01.DBF";
   set newname for datafile  8 to
 "E:\ORADATA\HCPRO\USERS_2_O2.DBF";
   set newname for datafile  9 to
 "E:\ORADATA\HCPRO\TOOLS01.DBF";
   set newname for datafile  10 to
 "D:\ORADATA\HCPRO\USERS02.DBF";
   set newname for datafile  11 to
 "E:\ORADATA\HCPRO\INDEXES02.DBF";
   set newname for datafile  12 to
 "E:\ORADATA\HCPRO\DWH_DATA01.DBF";
   set newname for datafile  13 to
 "E:\ORADATA\HCPRO\DWH_NDX01.DBF";
   restore
   check readonly
   clone database
   ;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 30-AUG-10

channel aux1: starting datafile backupset restore
channel aux1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to D:\ORADATA\HCPRO\SYSTEM01.DBF
restoring datafile 00003 to D:\ORADATA\HCPRO\SYSAUX01.DBF
restoring datafile 00005 to E:\ORADATA\HCPRO\INDEXES01.DBF
restoring datafile 00006 to D:\ORADATA\HCPRO\USERS01.DBF
restoring datafile 00008 to E:\ORADATA\HCPRO\USERS_2_O2.DBF
restoring datafile 00010 to D:\ORADATA\HCPRO\USERS02.DBF
channel aux1: reading from backup piece G:\RMAN_BACKUP\HCPRO\DF728053918_S6435_S1
ORA-19870: error reading backup piece G:\RMAN_BACKUP\HCPRO\DF728053918_S6435_S1
ORA-19505: failed to identify file "G:\RMAN_BACKUP\HCPRO\DF728053918_S6435_S1"
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.
channel aux1: starting datafile backupset restore
channel aux1: specifying datafile(s) to restore from backup set
restoring datafile 00013 to E:\ORADATA\HCPRO\DWH_NDX01.DBF
channel aux1: reading from backup piece G:\RMAN_BACKUP\HCPRO\DF728055613_S6436_S1
ORA-19870: error reading backup piece G:\RMAN_BACKUP\HCPRO\DF728055613_S6436_S1
ORA-19505: failed to identify file "G:\RMAN_BACKUP\HCPRO\DF728055613_S6436_S1"
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.
channel aux1: starting datafile backupset restore
channel aux1: specifying datafile(s) to restore from backup set
restoring datafile 00012 to E:\ORADATA\HCPRO\DWH_DATA01.DBF
channel aux1: reading from backup piece G:\RMAN_BACKUP\HCPRO\DF728055658_S6437_S1
ORA-19870: error reading backup piece G:\RMAN_BACKUP\HCPRO\DF728055658_S6437_S1
ORA-19505: failed to identify file "G:\RMAN_BACKUP\HCPRO\DF728055658_S6437_S1"
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.
channel aux1: starting datafile backupset restore
channel aux1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to D:\ORADATA\HCPRO\UNDOTBS01.DBF
restoring datafile 00004 to E:\ORADATA\HCPRO\GIS01.DBF
restoring datafile 00007 to E:\ORADATA\HCPRO\USERS_2_01.DBF
restoring datafile 00009 to E:\ORADATA\HCPRO\TOOLS01.DBF
restoring datafile 00011 to E:\ORADATA\HCPRO\INDEXES02.DBF
channel aux1: reading from backup piece G:\RMAN_BACKUP\HCPRO\DF728053918_S6434_S1
ORA-19870: error reading backup piece G:\RMAN_BACKUP\HCPRO\DF728053918_S6434_S1
ORA-19505: failed to identify file "G:\RMAN_BACKUP\HCPRO\DF728053918_S6434_S1"
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified. 
failover to previous backup

released channel: aux1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 08/30/2010 08:46:48
RMAN-03015: error occurred in stored script Memory Script
RMAN-06026: some targets not found - aborting restore
RMAN-06100: no channel to restore a backup or copy of datafile 13
RMAN-06100: no channel to restore a backup or copy of datafile 12
RMAN-06100: no channel to restore a backup or copy of datafile 11
RMAN-06100: no channel to restore a backup or copy of datafile 10
RMAN-06100: no channel to restore a backup or copy of datafile 9
RMAN-06100: no channel to restore a backup or copy of datafile 8
RMAN-06100: no channel to restore a backup or copy of datafile 7
RMAN-06100: no channel to restore a backup or copy of datafile 6
RMAN-06100: no channel to restore a backup or copy of datafile 5
RMAN-06100: no channel to restore a backup or copy of datafile 4
RMAN-06100: no channel to restore a backup or copy of datafile 3
RMAN-06100: no channel to restore a backup or copy of datafile 2
RMAN-06100: no channel to restore a backup or copy of datafile 1

RMAN>

The solution I (network backup files)

Then I came back to early mentioned Metalink note and noticed something that was not too obvious at the first time (and certainly not in the early 2000 when I read this document carefully):
Special Windows 2003 Update :
As Windows 2003 has a changed access behavior compared to Windows 2000, the solution is a little restrict :
  • Don't use local drive letters for mapping network shares.
Workaround is to use UNC locations directly, e.g. backup to \\B\share
Since this is in fact an absolute location this is always the same for any node in the network.
So whether accessed from node A or node B, \\B\share is always the shared location on B.
So the same applies obviously to Windows 2008 as well, so change was not in Oracle but Windows itself. And here is what I did.

1. Add share on target box

On target box add share \\ORION\rman_backup in previously shown way on target box:
C:\>net view \\ORION
Shared resources at \\ORION

Share name    Type  Used as  Comment
rman_backup   Disk
The command completed successfully.

C:\>

2. Change RMAN parameter on target database

Change RMAN DEVICE DISK FORMAT parameter, which should point to UNC location (not disk path):
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '\\ORION\rman_backup\hcpro\df%t_s%s_s%p';

old RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   'G:\rman_backup\hcpro\df%t_s%s_s%p';
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '\\ORION\rman_backup\hcpro\df%t_s%s_s%p';
new RMAN configuration parameters are successfully stored
Check that new parameter is really active:
RMAN> show all;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '\\ORION\rman_backup\hcpro\df%t_s%s_s%p';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'C:\ORANT\DATABASE\SNCFHCPRO.ORA';

3. Full backup

Perform full target database backup again. Important part (that really differ is how backup piece is presented (handle=\\ORION\RMAN_BACKUP\ parts):
...
input archive log thread=1 sequence=97504 recid=191078 stamp=728379501
channel ORA_DISK_2: starting piece 1 at 31.08.10
channel ORA_DISK_1: finished piece 1 at 31.08.10
piece handle=\\ORION\RMAN_BACKUP\HCPRO\DF728489365_S6472_S1 tag=TAG20100831T140904 comment=NONE
...
Starting backup at 31.08.10
current log archived
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting compressed archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=97763 recid=191596 stamp=728490802
channel ORA_DISK_1: starting piece 1 at 31.08.10
channel ORA_DISK_2: starting compressed archive log backupset
channel ORA_DISK_2: specifying archive log(s) in backup set
input archive log thread=1 sequence=97764 recid=191598 stamp=728492598
input archive log thread=1 sequence=97765 recid=191600 stamp=728493010
channel ORA_DISK_2: starting piece 1 at 31.08.10
channel ORA_DISK_2: finished piece 1 at 31.08.10
piece handle=\\ORION\RMAN_BACKUP\HCPRO\DF728493012_S6489_S1 tag=TAG20100831T151011 comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: finished piece 1 at 31.08.10
piece handle=\\ORION\RMAN_BACKUP\HCPRO\DF728493012_S6488_S1 tag=TAG20100831T151011 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:05
Finished backup at 31.08.10

Starting Control File Autobackup at 31.08.10
piece handle=C:\ORANT\DATABASE\C-4157028123-20100831-00 comment=NONE
Finished Control File Autobackup at 31.08.10

RMAN> 
Complete RMAN log file can be downloaded from this location.

4. Ensure PIT restore point

C:\>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Uto Kol 31 15:22:41 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the OLAP, Data Mining Scoring Engine and Real Application Testing options

SQL> select to_char(sysdate,'yyyymmdd hh24miss') from dual;

TO_CHAR(SYSDATE
---------------
20100831 152321

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

SQL> exit

5. Backup unbackped archivelogs on target database

To ensure PIT backup archivelogs that are made after PIT point. The complete RMAN log can be found here.

6. On auxilary box create normal share

First let us cancel previous net use command, delete previously created G: drive:
C:\>net use /d G:
There are open files and/or incomplete directory searches pending on the connection to G:.

Is it OK to continue disconnecting and force them closed? (Y/N) [N]: Y
G: was deleted successfully.

C:\>net use
New connections will not be remembered.

There are no entries in the list.

C:\>
And create normal Windows share c:\oracle\rman_backup with read/write rights on it. Here is the result of "share" situation before duplicate:
C:\>net share

Share name   Resource                        Remark

-------------------------------------------------------------------------------
C$           C:\                             Default share
D$           D:\                             Default share
E$           E:\                             Default share
G$           G:\                             Default share
IPC$                                         Remote IPC
P$           P:\                             Default share
ADMIN$       C:\Windows                      Remote Admin
rman_backup  C:\oracle\rman_backup
The command completed successfully.

C:\>
Now we are ready for duplicate!

7. RMAN duplicate again

C:\>rman target 'sys/xxxxx@HCPRO_RMAN as sysdba' nocatalog auxiliary /

Recovery Manager: Release 10.2.0.4.0 - Production on Tue Aug 31 15:42:26 2010

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: HCPRO (DBID=4157028123)
using target database control file instead of recovery catalog
connected to auxiliary database: HCPRO (not mounted)

RMAN> RUN {
2>      ALLOCATE AUXILIARY CHANNEL aux1 type DISK;
3>      SET UNTIL TIME "to_date( '20100831 152321','yyyymmdd hh24miss')";
4>      DUPLICATE TARGET DATABASE TO HCPRO
5>        NOFILENAMECHECK
6>        DB_FILE_NAME_CONVERT =('F:\ORADATA\HCPRO\' , 'D:\ORADATA\HCPRO\'
7>                               'G:\ORADATA\HCPRO\' , 'E:\ORADATA\HCPRO\'
8>                              )
9>        PFILE='C:\oracle\product\10.2.0\db_1\database\initHCPRO.ora'
10>        LOGFILE
11>          GROUP 1 ('C:\ORADATA\HCPRO\REDO01.LOG') SIZE 150M,
12>          GROUP 2 ('C:\ORADATA\HCPRO\REDO02.LOG') SIZE 150M,
13>          GROUP 3 ('C:\ORADATA\HCPRO\REDO03.LOG') SIZE 150M
14>      ;
15>      RELEASE CHANNEL aux1;
16> }

allocated channel: aux1
channel aux1: sid=5 devtype=DISK

executing command: SET until clause

Starting Duplicate Db at 31-AUG-10

contents of Memory Script:
{
   set until scn  14258087765;
   set newname for datafile  1 to
 "D:\ORADATA\HCPRO\SYSTEM01.DBF";
   set newname for datafile  2 to
 "D:\ORADATA\HCPRO\UNDOTBS01.DBF";
   set newname for datafile  3 to
 "D:\ORADATA\HCPRO\SYSAUX01.DBF";
   set newname for datafile  4 to
 "E:\ORADATA\HCPRO\GIS01.DBF";
   set newname for datafile  5 to
 "E:\ORADATA\HCPRO\INDEXES01.DBF";
   set newname for datafile  6 to
 "D:\ORADATA\HCPRO\USERS01.DBF";
   set newname for datafile  7 to
 "E:\ORADATA\HCPRO\USERS_2_01.DBF";
   set newname for datafile  8 to
 "E:\ORADATA\HCPRO\USERS_2_O2.DBF";
   set newname for datafile  9 to
 "E:\ORADATA\HCPRO\TOOLS01.DBF";
   set newname for datafile  10 to
 "D:\ORADATA\HCPRO\USERS02.DBF";
   set newname for datafile  11 to
 "E:\ORADATA\HCPRO\INDEXES02.DBF";
   set newname for datafile  12 to
 "E:\ORADATA\HCPRO\DWH_DATA01.DBF";
   set newname for datafile  13 to
 "E:\ORADATA\HCPRO\DWH_NDX01.DBF";
   restore
   check readonly
   clone database
   ;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 31-AUG-10

channel aux1: starting datafile backupset restore
channel aux1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to D:\ORADATA\HCPRO\SYSTEM01.DBF
restoring datafile 00003 to D:\ORADATA\HCPRO\SYSAUX01.DBF
restoring datafile 00005 to E:\ORADATA\HCPRO\INDEXES01.DBF
restoring datafile 00006 to D:\ORADATA\HCPRO\USERS01.DBF
restoring datafile 00008 to E:\ORADATA\HCPRO\USERS_2_O2.DBF
restoring datafile 00010 to D:\ORADATA\HCPRO\USERS02.DBF
channel aux1: reading from backup piece \\ORION\RMAN_BACKUP\HCPRO\DF728490468_S6485_S1
channel aux1: restored backup piece 1
piece handle=\\ORION\RMAN_BACKUP\HCPRO\DF728490468_S6485_S1 tag=TAG20100831T142748
channel aux1: restore complete, elapsed time: 00:24:18
channel aux1: starting datafile backupset restore
channel aux1: specifying datafile(s) to restore from backup set
restoring datafile 00013 to E:\ORADATA\HCPRO\DWH_NDX01.DBF
channel aux1: reading from backup piece \\ORION\RMAN_BACKUP\HCPRO\DF728492194_S6486_S1
channel aux1: restored backup piece 1
piece handle=\\ORION\RMAN_BACKUP\HCPRO\DF728492194_S6486_S1 tag=TAG20100831T142748
channel aux1: restore complete, elapsed time: 00:00:25
channel aux1: starting datafile backupset restore
channel aux1: specifying datafile(s) to restore from backup set
restoring datafile 00012 to E:\ORADATA\HCPRO\DWH_DATA01.DBF
channel aux1: reading from backup piece \\ORION\RMAN_BACKUP\HCPRO\DF728492239_S6487_S1
channel aux1: restored backup piece 1
piece handle=\\ORION\RMAN_BACKUP\HCPRO\DF728492239_S6487_S1 tag=TAG20100831T142748
channel aux1: restore complete, elapsed time: 00:00:07
channel aux1: starting datafile backupset restore
channel aux1: specifying datafile(s) to restore from backup set
restoring datafile 00002 to D:\ORADATA\HCPRO\UNDOTBS01.DBF
restoring datafile 00004 to E:\ORADATA\HCPRO\GIS01.DBF
restoring datafile 00007 to E:\ORADATA\HCPRO\USERS_2_01.DBF
restoring datafile 00009 to E:\ORADATA\HCPRO\TOOLS01.DBF
restoring datafile 00011 to E:\ORADATA\HCPRO\INDEXES02.DBF
channel aux1: reading from backup piece \\ORION\RMAN_BACKUP\HCPRO\DF728490468_S6484_S1
channel aux1: restored backup piece 1
piece handle=\\ORION\RMAN_BACKUP\HCPRO\DF728490468_S6484_S1 tag=TAG20100831T142748
channel aux1: restore complete, elapsed time: 00:29:58
Finished restore at 31-AUG-10
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "HCPRO" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY     4648
 LOGFILE
  GROUP  1 ( 'C:\ORADATA\HCPRO\REDO01.LOG' ) SIZE 150 M ,
  GROUP  2 ( 'C:\ORADATA\HCPRO\REDO02.LOG' ) SIZE 150 M ,
  GROUP  3 ( 'C:\ORADATA\HCPRO\REDO03.LOG' ) SIZE 150 M
 DATAFILE
  'D:\ORADATA\HCPRO\SYSTEM01.DBF'
 CHARACTER SET EE8MSWIN1250


contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=728498329 filename=D:\ORADATA\HCPRO\UNDOTBS01.DBF
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=728498329 filename=D:\ORADATA\HCPRO\SYSAUX01.DBF
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=728498329 filename=E:\ORADATA\HCPRO\GIS01.DBF
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=728498329 filename=E:\ORADATA\HCPRO\INDEXES01.DBF
datafile 6 switched to datafile copy
input datafile copy recid=5 stamp=728498329 filename=D:\ORADATA\HCPRO\USERS01.DBF
datafile 7 switched to datafile copy
input datafile copy recid=6 stamp=728498329 filename=E:\ORADATA\HCPRO\USERS_2_01.DBF
datafile 8 switched to datafile copy
input datafile copy recid=7 stamp=728498329 filename=E:\ORADATA\HCPRO\USERS_2_O2.DBF
datafile 9 switched to datafile copy
input datafile copy recid=8 stamp=728498329 filename=E:\ORADATA\HCPRO\TOOLS01.DBF
datafile 10 switched to datafile copy
input datafile copy recid=9 stamp=728498329 filename=D:\ORADATA\HCPRO\USERS02.DBF
datafile 11 switched to datafile copy
input datafile copy recid=10 stamp=728498329 filename=E:\ORADATA\HCPRO\INDEXES02.DBF
datafile 12 switched to datafile copy
input datafile copy recid=11 stamp=728498329 filename=E:\ORADATA\HCPRO\DWH_DATA01.DBF
datafile 13 switched to datafile copy
input datafile copy recid=12 stamp=728498329 filename=E:\ORADATA\HCPRO\DWH_NDX01.DBF

contents of Memory Script:
{
   set until time  "to_date( '20100831 152321','yyyymmdd hh24miss')";
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 31-AUG-10

starting media recovery

channel aux1: starting archive log restore to default destination
channel aux1: restoring archive log
archive log thread=1 sequence=97764
channel aux1: restoring archive log
archive log thread=1 sequence=97765
channel aux1: reading from backup piece \\ORION\RMAN_BACKUP\HCPRO\DF728493012_S6489_S1
channel aux1: restored backup piece 1
piece handle=\\ORION\RMAN_BACKUP\HCPRO\DF728493012_S6489_S1 tag=TAG20100831T151011
channel aux1: restore complete, elapsed time: 00:00:01
channel aux1: starting archive log restore to default destination
channel aux1: restoring archive log
archive log thread=1 sequence=97763
channel aux1: reading from backup piece \\ORION\RMAN_BACKUP\HCPRO\DF728493012_S6488_S1
channel aux1: restored backup piece 1
piece handle=\\ORION\RMAN_BACKUP\HCPRO\DF728493012_S6488_S1 tag=TAG20100831T151011
channel aux1: restore complete, elapsed time: 00:00:03
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\RDBMS\ARC97763_0611956955.001 thread=1 sequence=97763
channel clone_default: deleting archive log(s)
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\RDBMS\ARC97763_0611956955.001 recid=3 stamp=728498335
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\RDBMS\ARC97764_0611956955.001 thread=1 sequence=97764
channel clone_default: deleting archive log(s)
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\RDBMS\ARC97764_0611956955.001 recid=2 stamp=728498333
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\RDBMS\ARC97765_0611956955.001 thread=1 sequence=97765
channel clone_default: deleting archive log(s)
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\RDBMS\ARC97765_0611956955.001 recid=1 stamp=728498333
channel aux1: starting archive log restore to default destination
channel aux1: restoring archive log
archive log thread=1 sequence=97766
channel aux1: reading from backup piece \\ORION\RMAN_BACKUP\HCPRO\DF728494278_S6491_S1
channel aux1: restored backup piece 1
piece handle=\\ORION\RMAN_BACKUP\HCPRO\DF728494278_S6491_S1 tag=TAG20100831T153116
channel aux1: restore complete, elapsed time: 00:00:01
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\RDBMS\ARC97766_0611956955.001 thread=1 sequence=97766
channel clone_default: deleting archive log(s)
archive log filename=C:\ORACLE\PRODUCT\10.2.0\DB_1\RDBMS\ARC97766_0611956955.001 recid=4 stamp=728498338
media recovery complete, elapsed time: 00:00:00
Finished recover at 31-AUG-10

contents of Memory Script:
{
   shutdown clone;
   startup clone nomount pfile= 'C:\oracle\product\10.2.0\db_1\database\initHCPRO.ora';
}
executing Memory Script

database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     188743680 bytes

Fixed Size                     2211864 bytes
Variable Size                115097576 bytes
Database Buffers              67108864 bytes
Redo Buffers                   4325376 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "HCPRO" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY     4648
 LOGFILE
  GROUP  1 ( 'C:\ORADATA\HCPRO\REDO01.LOG' ) SIZE 150 M ,
  GROUP  2 ( 'C:\ORADATA\HCPRO\REDO02.LOG' ) SIZE 150 M ,
  GROUP  3 ( 'C:\ORADATA\HCPRO\REDO03.LOG' ) SIZE 150 M
 DATAFILE
  'D:\ORADATA\HCPRO\SYSTEM01.DBF'
 CHARACTER SET EE8MSWIN1250


contents of Memory Script:
{
   set newname for tempfile  1 to
 "D:\ORADATA\HCPRO\TEMP01.DBF";
   switch clone tempfile all;
   catalog clone datafilecopy  "D:\ORADATA\HCPRO\UNDOTBS01.DBF";
   catalog clone datafilecopy  "D:\ORADATA\HCPRO\SYSAUX01.DBF";
   catalog clone datafilecopy  "E:\ORADATA\HCPRO\GIS01.DBF";
   catalog clone datafilecopy  "E:\ORADATA\HCPRO\INDEXES01.DBF";
   catalog clone datafilecopy  "D:\ORADATA\HCPRO\USERS01.DBF";
   catalog clone datafilecopy  "E:\ORADATA\HCPRO\USERS_2_01.DBF";
   catalog clone datafilecopy  "E:\ORADATA\HCPRO\USERS_2_O2.DBF";
   catalog clone datafilecopy  "E:\ORADATA\HCPRO\TOOLS01.DBF";
   catalog clone datafilecopy  "D:\ORADATA\HCPRO\USERS02.DBF";
   catalog clone datafilecopy  "E:\ORADATA\HCPRO\INDEXES02.DBF";
   catalog clone datafilecopy  "E:\ORADATA\HCPRO\DWH_DATA01.DBF";
   catalog clone datafilecopy  "E:\ORADATA\HCPRO\DWH_NDX01.DBF";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed temporary file 1 to D:\ORADATA\HCPRO\TEMP01.DBF in control file

cataloged datafile copy
datafile copy filename=D:\ORADATA\HCPRO\UNDOTBS01.DBF recid=1 stamp=728498351

cataloged datafile copy
datafile copy filename=D:\ORADATA\HCPRO\SYSAUX01.DBF recid=2 stamp=728498351

cataloged datafile copy
datafile copy filename=E:\ORADATA\HCPRO\GIS01.DBF recid=3 stamp=728498351

cataloged datafile copy
datafile copy filename=E:\ORADATA\HCPRO\INDEXES01.DBF recid=4 stamp=728498352

cataloged datafile copy
datafile copy filename=D:\ORADATA\HCPRO\USERS01.DBF recid=5 stamp=728498352

cataloged datafile copy
datafile copy filename=E:\ORADATA\HCPRO\USERS_2_01.DBF recid=6 stamp=728498352

cataloged datafile copy
datafile copy filename=E:\ORADATA\HCPRO\USERS_2_O2.DBF recid=7 stamp=728498353

cataloged datafile copy
datafile copy filename=E:\ORADATA\HCPRO\TOOLS01.DBF recid=8 stamp=728498353

cataloged datafile copy
datafile copy filename=D:\ORADATA\HCPRO\USERS02.DBF recid=9 stamp=728498353

cataloged datafile copy
datafile copy filename=E:\ORADATA\HCPRO\INDEXES02.DBF recid=10 stamp=728498353

cataloged datafile copy
datafile copy filename=E:\ORADATA\HCPRO\DWH_DATA01.DBF recid=11 stamp=728498354

cataloged datafile copy
datafile copy filename=E:\ORADATA\HCPRO\DWH_NDX01.DBF recid=12 stamp=728498354

datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=728498351 filename=D:\ORADATA\HCPRO\UNDOTBS01.DBF
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=728498351 filename=D:\ORADATA\HCPRO\SYSAUX01.DBF
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=728498351 filename=E:\ORADATA\HCPRO\GIS01.DBF
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=728498352 filename=E:\ORADATA\HCPRO\INDEXES01.DBF
datafile 6 switched to datafile copy
input datafile copy recid=5 stamp=728498352 filename=D:\ORADATA\HCPRO\USERS01.DBF
datafile 7 switched to datafile copy
input datafile copy recid=6 stamp=728498352 filename=E:\ORADATA\HCPRO\USERS_2_01.DBF
datafile 8 switched to datafile copy
input datafile copy recid=7 stamp=728498353 filename=E:\ORADATA\HCPRO\USERS_2_O2.DBF
datafile 9 switched to datafile copy
input datafile copy recid=8 stamp=728498353 filename=E:\ORADATA\HCPRO\TOOLS01.DBF
datafile 10 switched to datafile copy
input datafile copy recid=9 stamp=728498353 filename=D:\ORADATA\HCPRO\USERS02.DBF
datafile 11 switched to datafile copy
input datafile copy recid=10 stamp=728498353 filename=E:\ORADATA\HCPRO\INDEXES02.DBF
datafile 12 switched to datafile copy
input datafile copy recid=11 stamp=728498354 filename=E:\ORADATA\HCPRO\DWH_DATA01.DBF
datafile 13 switched to datafile copy
input datafile copy recid=12 stamp=728498354 filename=E:\ORADATA\HCPRO\DWH_NDX01.DBF

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 31-AUG-10

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of release command at 08/31/2010 16:39:25
RMAN-06012: channel: aux1 not allocated

RMAN>
Error message at the end
channel: aux1 not allocated
is not important... AFAIK this has some known issues in duplicate command on Oracle 10.2 version.

8. Final check on auxiliary database

C:\>sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Wed Sep 1 09:43:53 2010

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set linesize 160;
SQL> col HOST_NAME for a40;
SQL> select INSTANCE_NUMBER iid, INSTANCE_NAME, STATUS, to_char(STARTUP_TIME,'dd.mm.yy hh24:mi:ss') startup,  HOST_NAME
from v$instance;

       IID INSTANCE_NAME    STATUS       STARTUP           HOST_NAME
---------- ---------------- ------------ ----------------- ----------------------------------------
         1 hcpro            OPEN         31.08.10 16:39:04 ORION1

SQL>
As you see hcpro is up and running on ORION1 (auxiliary box).

The End

If you look in "Solution I" (that officially Oracle provides), I really think that few of DBAs do make RMAN backup to disk with UNC path in it's format.

In any case I find Linux more flexible and adoptive for such an tasks...and frankly to whole Oracle world. Unfortunately, today life set in front of DBA many OS's and in many cases-Windows as well.

Cheers!

Zagreb u srcu!

Copyright © 2009-2018 Damir Vadas

All rights reserved.


Sign by Danasoft - Get Your Sign