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!

2 comments :

  1. Damir,

    Thanks for your post. It helped me a lot! I do have a couple of questions, however. First, in step 6 you create a normal share on the auxiliary but I never see reference to it or use of it after that. I am not sure why. Second, with the absolute path specified to \\server\share my duplicate ran long as it appeared to restore from the target server. In previous duplicate commands on non-windows OS I would stage the backup pieces on the auxiliary server in directory names like that of the target. So all the restore/recover activity was taking place on the auxiliary. Any thoughts?

    ReplyDelete
  2. Share was used for network copy files.

    "\\server\share my duplicate ran long as it appeared to restore from the target server"
    This is because in duplicate case it uses network resources and running directly from server (from dedicated partition) it is quicker but in that case you must have that parturition, what in some cases is not possible so "||server\share" is the only option.

    "Non windows" (i.e. Linux based OS's) has no limitation (as I said in topic) and all problem arises when Micro$oft change "net use" and "subst" statements. In this situation Oracle is not guilty for problems on MS environment ...

    Hope this clear up your thoughts.

    ReplyDelete

Zagreb u srcu!

Copyright © 2009-2018 Damir Vadas

All rights reserved.


Sign by Danasoft - Get Your Sign