Oracle 12c - incremental backup for DataGuard over network

If you have DataGuard or standby database in your organization you probably will love that new RMAN feature. Since 12c it is possible to catchup standby database using incremental backup using one command. Additional space and time need to run incremental backup, copy over to standby and restore can be limited to time required to run incremental backup over network.

See short example:

Stopping recovery on standby 

[oracle@ip-10-0-1-79 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Sun Jul 7 12:56:24 2013
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE cancel;

Database altered.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Running incremental backup:

[oracle@ip-10-0-1-79 ~]$ rman target /
Recovery Manager: Release 12.1.0.1.0 - Production on Sun Jul 7 12:56:39 2013
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
connected to target database: TEST12C (DBID=1424547711, not open)

RMAN>  recover database from service test12c_b using compressed backupset;

Starting recover at 07-JUL-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=35 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service test12c_b
destination for restore of datafile 00001: /u01/app/oracle/oradata/test12c/system01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service test12c_b
destination for restore of datafile 00002: /u01/app/oracle/oradata/test12c/sysaux01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service test12c_b
destination for restore of datafile 00003: /u01/app/oracle/oradata/test12c/undotbs01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service test12c_b
destination for restore of datafile 00004: /u01/app/oracle/oradata/test12c/users01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07

...

RMAN>; exit
Recovery Manager complete.

For anyone who spend night trying to catch up standby database above solution looks brilliant.

regards,
Marcin

RMAN Transportable tablespace bug

I was a long time since I last used Transportable Tablespaces and I decided to refresh my memory and setup a test environment for Streams using TTS feature. I was going to use example script from Oracle Streams Documentation which is creating necessary data files for specified point of time (no need to switch source tablespace into read only mode) and also is running Data Pump creating meta data file. I have done necessary  changes and run it.

[oracle@testdb1 oracle]$ rsp / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Aug 6 12:50:04 2012
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

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

Session altered.

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
  until_scn NUMBER;
BEGIN
  until_scn:= DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER;
      DBMS_OUTPUT.PUT_LINE('Until SCN: ' || until_scn);
SYS@testa AS SYSDBA >   2    3    4    5    6  END;
  7  /
Until SCN: 6300731

PL/SQL procedure successfully completed.
SYS@testa AS SYSDBA > exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@testdb1 oracle]$ rlwrap rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Mon Aug 6 12:50:33 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: TESTA (DBID=243207980)

RMAN>  RUN
2>     {
        TRANSPORT TABLESPACE 'SOE_TS'
        UNTIL SCN 6300731
        TABLESPACE DESTINATION '/u01/app/oracle/dest';
        DATAPUMP DIRECTORY SOURCE_DIR
        AUXILIARY DESTINATION '/u01/app/oracle/aux_files'
        DUMP FILE 'soe_ts.dmp'
        EXPORT LOG 'soe.log' 
        IMPORT SCRIPT 'soe_ts.sql'
        TABLESPACE DESTINATION '/u01/app/oracle/dest';
       }

using target database control file instead of recovery catalog
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified point-in-time

List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS2
...

Unfortunately there is a RMAN bug related to export log file. Due to it first attempt end up with following error

contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone 'alter tablespace  "SOE" read only';
}
executing Memory Script

sql statement: alter tablespace  "SOE" read only

Performing export of metadata...

Removing automatic instance
shutting down automatic instance
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of transport tablespace command at 08/05/2012 23:18:47
RMAN-06136: ORACLE error from auxiliary database: ORA-01097: cannot shutdown while in a transaction - commit or rollback first
RMAN-06136: ORACLE error from auxiliary database: ORA-01460: unimplemented or unreasonable conversion requested

RMAN>

At first sight error message has nothing to do with log file but I know that RMAN error messaging is well .. different is a good word here. I search for solution and I found out that workaround is quite simple - EXPORT LOG clause has to be removed from script. So let's run it again

[oracle@testdb1 oracle]$ rlwrap rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Mon Aug 6 12:50:33 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: TESTA (DBID=243207980)

RMAN>  RUN
2>       {
        TRANSPORT TABLESPACE 'SOE_TS'
        UNTIL SCN 6300731
        TABLESPACE DESTINATION '/u01/app/oracle/dest';
        DATAPUMP DIRECTORY SOURCE_DIR
        AUXILIARY DESTINATION '/u01/app/oracle/aux_files'
        DUMP FILE 'soe_ts.dmp'
        #EXPORT LOG 'soe.log' - bug
        IMPORT SCRIPT 'soe_ts.sql'
        TABLESPACE DESTINATION '/u01/app/oracle/dest';
      }

using target database control file instead of recovery catalog
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified point-in-time

List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS2
...
contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone 'alter tablespace  "SOE_TS" read only';
}
executing Memory Script

sql statement: alter tablespace  "SOE_TS" read only

Performing export of metadata...
   EXPDP> FLASHBACK automatically enabled to preserve database integrity.
   EXPDP> Starting "SYS"."TSPITR_EXP_khar":
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX/FUNCTIONAL_INDEX/INDEX
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   EXPDP> Master table "SYS"."TSPITR_EXP_khar" successfully loaded/unloaded
   EXPDP> ******************************************************************************
   EXPDP> Dump file set for SYS.TSPITR_EXP_khar is:
   EXPDP>   /u01/app/oracle/dest/soe_ts.dmp
   EXPDP> ******************************************************************************
   EXPDP> Datafiles required for transportable tablespace SOE_TS:
   EXPDP>   /u01/app/oracle/dest/soe_ts01.dbf
   EXPDP> Job "SYS"."TSPITR_EXP_khar" successfully completed at 12:55:35
Export completed

Now looks much better - tablespace has been exported and import script has been generated as well. Now I'm ready for next steps and can continue my work with Streams.

regards,
Marcin

RMAN Database duplication without target connectivity can corrupt target database

Latest version of Oracle 11g R2 has new Recovery Manager functionality - database duplication from backup without connectivity to target database. During tests I have found unexpected behavior of that functionality. I'm still not sure if it is bug or RMAN is doing it by design but if backup doesn't contain all files and SKIP TABLESPACE is not included, duplication process can overwrite target database files. When database is using ASM it can happen regardless of database state. Due to file locking mechanism this issue occur on file system only when target database is down.

All has been performed using Oracle version 11.2.0.2 on Red Hat 5.3 running target and auxiliary database on one server using file system. I have tested it with ASM as well but log files are gone together with my VM machine.

Target database is down, Backup has been taken without one tablespace, 
no SKIP TABLESPACE clause.

Duplication script:

run {
set newname for tempefile 1 to '/opt/app/oracle/oradata/clon/temp01.dbf';
duplicate target database to clon
SPFILE
    PARAMETER_VALUE_CONVERT '/opt/app/oracle/admin/testdb/',
                            '/opt/app/oracle/admin/clon/'
    SET SGA_MAX_SIZE '500M'
    SET CONTROL_FILES '/opt/app/oracle/oradata/clon/control01.ctl','/opt/app/oracle/oradata/clon/control02.ctl'
    SET SGA_TARGET '450M'
    SET CLUSTER_DATABASE 'false'
    SET db_create_file_dest          '/opt/app/oracle/oradata/clon/'
    SET db_create_online_log_dest_1  '/opt/app/oracle/oradata/clon/'
    SET LOG_FILE_NAME_CONVERT  '/opt/app/oracle/oradata/testdb/','/opt/app/oracle/oradata/clon/'
    SET DB_FILE_NAME_CONVERT   '/opt/app/oracle/oradata/testdb/','/opt/app/oracle/oradata/clon/'
    BACKUP LOCATION '/opt/app/oracle/backup/';
}

Let's take a look on output - unimportant lines have been skipped

Recovery Manager: Release 11.2.0.2.0 - Production on Tue Jun 21 07:17:34 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to auxiliary database: CLON (not mounted)

Starting Duplicate Db at 21-JUN-11
...
Starting restore at 21-JUN-11
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=171 device type=DISK

channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/opt/app/oracle/oradata/clon/control01.ctl
output file name=/opt/app/oracle/oradata/clon/control02.ctl
Finished restore at 21-JUN-11

database mounted
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=171 device type=DISK

contents of Memory Script:
{
   set until scn  95159515;
   set newname for datafile  1 to 
 "/opt/app/oracle/oradata/clon/system-01.dbf";
   set newname for datafile  2 to 
 "/opt/app/oracle/oradata/clon/undo_t1-01.dbf";
   set newname for datafile  3 to 
 "/opt/app/oracle/oradata/clon/sysaux-01.dbf";
   set newname for datafile  4 to 
 "/opt/app/oracle/oradata/clon/administrator-01.dbf";
   set newname for datafile  5 to 
 "/opt/app/oracle/oradata/clon/administrator_idx-01.dbf";
   set newname for datafile  6 to 
 "/opt/app/oracle/oradata/clon/replication-01.dbf";
   set newname for datafile  7 to 
 "/opt/app/oracle/oradata/clon/replication_idx-01.dbf";
   set newname for datafile  8 to 
 "/opt/app/oracle/oradata/clon/soe.dbf";
   set newname for datafile  9 to 
 "/opt/app/oracle/oradata/clon/soeindex.dbf";
   set newname for datafile  10 to 
 "/opt/app/oracle/oradata/clon/users-01.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
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME

So far so good - new name has been set for all files. What is next

file 9 is excluded from whole database backup
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /opt/app/oracle/oradata/clon/system-01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /opt/app/oracle/oradata/clon/undo_t1-01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /opt/app/oracle/oradata/clon/sysaux-01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /opt/app/oracle/oradata/clon/administrator-01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /opt/app/oracle/oradata/clon/administrator_idx-01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00006 to /opt/app/oracle/oradata/clon/replication-01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00007 to /opt/app/oracle/oradata/clon/replication_idx-01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00008 to /opt/app/oracle/oradata/clon/soe.dbf
channel ORA_AUX_DISK_1: restoring datafile 00010 to /opt/app/oracle/oradata/clon/users-01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /opt/app/oracle/backup/bck_13_testdb_1
channel ORA_AUX_DISK_1: piece handle=/opt/app/oracle/backup/bck_13_testdb_1 tag=TAG20110621T064346
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:06:36
failover to previous backup

Finished restore at 21-JUN-11

All files from backupset have been restored and Oracle realize that one file is missing. Keep in mind that new name has been prepared for all files.

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

datafile 1 switched to datafile copy
input datafile copy RECID=10 STAMP=754384699 file name=/opt/app/oracle/oradata/clon/system-01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=11 STAMP=754384699 file name=/opt/app/oracle/oradata/clon/undo_t1-01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=12 STAMP=754384699 file name=/opt/app/oracle/oradata/clon/sysaux-01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=13 STAMP=754384699 file name=/opt/app/oracle/oradata/clon/administrator-01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=14 STAMP=754384699 file name=/opt/app/oracle/oradata/clon/administrator_idx-01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=15 STAMP=754384699 file name=/opt/app/oracle/oradata/clon/replication-01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=16 STAMP=754384699 file name=/opt/app/oracle/oradata/clon/replication_idx-01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=17 STAMP=754384699 file name=/opt/app/oracle/oradata/clon/soe.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=18 STAMP=754384700 file name=/opt/app/oracle/oradata/clon/users-01.dbf

This switch command is starting this issue - missing file hasn't been switch to new location

contents of Memory Script:
{
   set until scn  95159515;
   recover
   clone database
   delete archivelog
   ;
}
executing Memory Script
executing command: SET until clause
Starting recover at 21-JUN-11
using channel ORA_AUX_DISK_1
starting media recovery
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=1024
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=1025
channel ORA_AUX_DISK_1: reading from backup piece /opt/app/oracle/backup/bck_arch_16_testdb_1
channel ORA_AUX_DISK_1: piece handle=/opt/app/oracle/backup/bck_arch_16_testdb_1 tag=TAG20110621T070623
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
archived log file name=/opt/app/oracle/fra/CLON/archivelog/2011_06_21/o1_mf_1_1024_700kfxcx_.arc thread=1 sequence=1024
channel clone_default: deleting archived log(s)
archived log file name=/opt/app/oracle/fra/CLON/archivelog/2011_06_21/o1_mf_1_1024_700kfxcx_.arc RECID=2 STAMP=754384704
archived log file name=/opt/app/oracle/fra/CLON/archivelog/2011_06_21/o1_mf_1_1025_700kfxg6_.arc thread=1 sequence=1025
Oracle Error: 
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 9 was not restored from a sufficiently old backup 
ORA-01110: data file 9: '/opt/app/oracle/oradata/testdb/soeindex.dbf'

The worst part is here - RMAN was trying to apply logs to other database using entries from control file - the TARGET database entries. DBID at that time for target and clone database are still the same and target database was down so there are no locks. Nothing could prevent this process to overwrite target database file header.

What happen to target database ? The following error will be found in target alert.log file

Read of datafile '/opt/app/oracle/oradata/testdb/soeindex.dbf' (fno 9) header failed with ORA-01206
Rereading datafile 9 header failed with ORA-01206
Errors in file /opt/app/oracle/diag/rdbms/testdb/testdb/trace/testdb_dbw0_4984.trc:
ORA-01186: file 9 failed verification tests
ORA-01122: database file 9 failed verification check

File in target database has been corrupted during duplication process and it has to be restored from backup or dropped.

Target database is running, backup has been taken without one table space, 
no SKIP TABLESPACE clause.
Duplication process is starting in similar way to previous scenario but instead of overwriting target file the following error is reporting during recovery process.

RMAN-11001: Oracle Error: 
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 9: '/opt/app/oracle/oradata/testdb/soeindex.dbf'
ORA-01157: cannot identify/lock data file 9 - see DBWR trace file
ORA-01110: data file 9: '/opt/app/oracle/oradata/testdb/soeindex.dbf'

This time duplication process failed but target database wasn't touched.

Target database is down, Backup has been taken without one tablespace, 
SKIP TABLESPACE clause has been setup
This time backup still doesn't have all files but SKIP_TABLESPACE has been added to script. Recovery manager is working now without any issues

Recovery Manager: Release 11.2.0.2.0 - Production on Tue Jun 21 09:32:21 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to auxiliary database: CLON (not mounted)
RMAN> run {
2> set newname for tempfile 1 to '/opt/app/oracle/oradata/clon/temp01.dbf';
3>                                                             
4> duplicate target database to clon                           
5> SPFILE                                                      
6>     PARAMETER_VALUE_CONVERT '/opt/app/oracle/admin/testdb/',
7>                             '/opt/app/oracle/admin/clon/'   
8>     SET SGA_MAX_SIZE '500M'                                 
9>     SET CONTROL_FILES '/opt/app/oracle/oradata/clon/control01.ctl','/opt/app/oracle/oradata/clon/control02.ctl'
10>     SET SGA_TARGET '450M'        
11>     SET CLUSTER_DATABASE 'false' 
12>     SET db_create_file_dest          '/opt/app/oracle/oradata/clon/'
13>     SET db_create_online_log_dest_1  '/opt/app/oracle/oradata/clon/'
14>     SET LOG_FILE_NAME_CONVERT  '/opt/app/oracle/oradata/testdb/','/opt/app/oracle/oradata/clon/'
15>     SET DB_FILE_NAME_CONVERT   '/opt/app/oracle/oradata/testdb/','/opt/app/oracle/oradata/clon/'
16>     SKIP TABLESPACE 'SOEINDEX'                       
17>     UNDO TABLESPACE 'UNDO_T1'                                                                   
18>     BACKUP LOCATION '/opt/app/oracle/backup/';                                                  
19> }     
20> 
executing command: SET NEWNAME

Starting Duplicate Db at 21-JUN-11
...
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=171 device type=DISK
Not connected to TARGET or TARGET not open, cannot verify that subset of tablespaces is self-contained
Not connected to TARGET, cannot verify that set of tablespaces being duplicated does not have SYS objects
Datafile 9 skipped by request

contents of Memory Script:
{
   set until scn  95159515;
   set newname for datafile  1 to 
 "/opt/app/oracle/oradata/clon/system-01.dbf";
   set newname for datafile  2 to 
 "/opt/app/oracle/oradata/clon/undo_t1-01.dbf";
   set newname for datafile  3 to 
 "/opt/app/oracle/oradata/clon/sysaux-01.dbf";
   set newname for datafile  4 to 
 "/opt/app/oracle/oradata/clon/administrator-01.dbf";
   set newname for datafile  5 to 
 "/opt/app/oracle/oradata/clon/administrator_idx-01.dbf";
   set newname for datafile  6 to 
 "/opt/app/oracle/oradata/clon/replication-01.dbf";
   set newname for datafile  7 to 
 "/opt/app/oracle/oradata/clon/replication_idx-01.dbf";
   set newname for datafile  8 to 
 "/opt/app/oracle/oradata/clon/soe.dbf";
   set newname for datafile  10 to 
 "/opt/app/oracle/oradata/clon/users-01.dbf";
   restore
   clone database
   skip forever tablespace  "SOEINDEX"   ;
}
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

Starting restore at 21-JUN-11
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /opt/app/oracle/oradata/clon/system-01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /opt/app/oracle/oradata/clon/undo_t1-01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /opt/app/oracle/oradata/clon/sysaux-01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /opt/app/oracle/oradata/clon/administrator-01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /opt/app/oracle/oradata/clon/administrator_idx-01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00006 to /opt/app/oracle/oradata/clon/replication-01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00007 to /opt/app/oracle/oradata/clon/replication_idx-01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00008 to /opt/app/oracle/oradata/clon/soe.dbf
channel ORA_AUX_DISK_1: restoring datafile 00010 to /opt/app/oracle/oradata/clon/users-01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /opt/app/oracle/backup/bck_13_testdb_1
channel ORA_AUX_DISK_1: piece handle=/opt/app/oracle/backup/bck_13_testdb_1 tag=TAG20110621T064346
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:06:25
Finished restore at 21-JUN-11

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

datafile 1 switched to datafile copy
input datafile copy RECID=10 STAMP=754393152 file name=/opt/app/oracle/oradata/clon/system-01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=11 STAMP=754393152 file name=/opt/app/oracle/oradata/clon/undo_t1-01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=12 STAMP=754393152 file name=/opt/app/oracle/oradata/clon/sysaux-01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=13 STAMP=754393152 file name=/opt/app/oracle/oradata/clon/administrator-01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=14 STAMP=754393152 file name=/opt/app/oracle/oradata/clon/administrator_idx-01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=15 STAMP=754393152 file name=/opt/app/oracle/oradata/clon/replication-01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=16 STAMP=754393152 file name=/opt/app/oracle/oradata/clon/replication_idx-01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=17 STAMP=754393152 file name=/opt/app/oracle/oradata/clon/soe.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=18 STAMP=754393152 file name=/opt/app/oracle/oradata/clon/users-01.dbf

contents of Memory Script:
{
   set until scn  95159515;
   recover
   clone database
   skip forever tablespace  "SOEINDEX"    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 21-JUN-11
using channel ORA_AUX_DISK_1

Executing: alter database datafile 9 offline drop
starting media recovery

channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=1024
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=1025
channel ORA_AUX_DISK_1: reading from backup piece /opt/app/oracle/backup/bck_arch_16_testdb_1
channel ORA_AUX_DISK_1: piece handle=/opt/app/oracle/backup/bck_arch_16_testdb_1 tag=TAG20110621T070623
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
archived log file name=/opt/app/oracle/fra/CLON/archivelog/2011_06_21/o1_mf_1_1024_700sp3lj_.arc thread=1 sequence=1024
channel clone_default: deleting archived log(s)
archived log file name=/opt/app/oracle/fra/CLON/archivelog/2011_06_21/o1_mf_1_1024_700sp3lj_.arc RECID=2 STAMP=754393158
archived log file name=/opt/app/oracle/fra/CLON/archivelog/2011_06_21/o1_mf_1_1025_700sp3p1_.arc thread=1 sequence=1025
channel clone_default: deleting archived log(s)
archived log file name=/opt/app/oracle/fra/CLON/archivelog/2011_06_21/o1_mf_1_1025_700sp3p1_.arc RECID=1 STAMP=754393155
media recovery complete, elapsed time: 00:00:04
Finished recover at 21-JUN-11
Oracle instance started

Additional step has been run before recover process - file from skipped table space has been dropped. In that case recovery process should touch it anymore from any location.

This exercise is showing that all new features should be tested in safe environment and in addition to that every operation on production database has to be well tested. I can't blame only Recovery Manager here but it would nice to have more checks in place (ex. if not all files are in backup just cancel duplication after control file has been restored). This is a perfect example of human error (at least someone created that scripts and run it) which can in complex environment. 

regards,
Marcin

RMAN and SBT_TAPE backup pieces - how to catalog it

Hi,

There has been a discussion on oracle-l mailing list about RMAN and backup pieces on tape which are non in recovery catalog anymore. A few people including me response that those backup pieces can be restored using dbms_backup_restore PL/SQL package if we only know a backup piece name.

Update 01/02/2010: Now is more solutions - I have started my investigation a few days ago.

I investigated this problem a little deeper and I found another solution. I did it because I was not aware that according to official Oracle documentation CATALOG command is not working with ‘SBT_TAPE’ interface.

I have tested this solution on:

• Oracle 11.1.0.7 Linux 32 bit
• Oracle 10.2.0.1 Windows 32 bit
• Oracle 10.2.0.4 Linux 32 bit

I’ve no possibility to check this on other machines now but if you can give me a shout what a results are.

Let’s story begin.

Only thing I knew was a backup piece name. This is a prerequisite but it can be found quite easily by Media Manager admin or by you itself if you know your Media Manager software. See example below for Legato Networker:

[oracle@piorovm usr]$ ./sbin/mminfo -q "client=piorovm" -r "ssid,name,totalsize,savetime,volume"
 ssid      name                                 total  date   volume
4267926146 RMAN:3bl4n6ru_1_1                  9961944 01/29/2010 piorovm.localdomain.001
4234372063 RMAN:3dl4n76v_1_1                805331112 01/29/2010 piorovm.localdomain.001
4217594914 RMAN:3el4n792_1_1               1389143616 01/29/2010 piorovm.localdomain.001
4200817814 RMAN:3fl4n7cl_1_1                477378960 01/29/2010 piorovm.localdomain.001
4251148936 RMAN:c-3537902502-20100129-03     10224108 01/29/2010 piorovm.localdomain.001
4184040645 RMAN:c-3537902502-20100129-04     10224108 01/29/2010 piorovm.localdomain.001

Or another example for Verita Netbackup (by Allen Brandon from Oracle-l list)

 /opt/oracle ->bplist -t 4 -k  -l -s 1/25/2010 -e 1/26/2010 -R /
-rw------- oracle    oracle       12582912 Jan 25 21:21 /c-18889999-20100125-09
-rw------- oracle    oracle        2359296 Jan 25 21:18 /xxprd_al_52855_1_709244222
-rw------- oracle    oracle       3383296K Jan 25 21:15 /xxprd_db_52852_1_709243545

Next thing is to add these names to control file or recovery catalog. First of all I started with very low level solution and dbms_backup_restore package. There is a function called inspectBackupPiece which required only backup piece name. Channel has to be allocated before that command. This gave me a hope that I can do it. I have created a small PL/SQL script and I have executed it.

DECLARE
v_dev varchar2(50);             
v_fullname varchar2(4000);
recid number;
stamp number;

BEGIN
v_dev:=sys.dbms_backup_restore.deviceAllocate(type=>'sbt_tape', ident=>'test');
sys.dbms_backup_restore.inspectBackupPiece('3fl4n7cl_1_1',v_fullname,recid,stamp);
sys.dbms_backup_restore.deviceDeallocate;
END;
/

And that was it – Backup piece has been added to control file.

Ok, if there is a possibility to does this using PL/SQL package there should be possibility do this using RMAN command line. I was trying with allocating ‘SBT_TAPE’ channel and catalog command but without success. Because I have very short memory about command syntax and I know that RMAN is helping with that a little bit I gave a try:

RMAN> catalog  ;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found ";": expecting one of: "archivelog, backup, backuppiece, controlfilecopy, clone, datafilecopy, device, db_recovery_file_dest, recovery, start"
RMAN-01007: at line 2 column 1 file: standard input

As you can see in error message is a short information about what is expected. Hmmm a device I think it is a good direction. Let’s try again

RMAN> catalog device type;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found ";": expecting one of: "double-quoted-string, disk, equal, identifier, single-quoted-string"
RMAN-01007: at line 1 column 20 file: standard input

Ok that’s it – RMAN is expecting a disk or any double or single quoted string.

Final step is to give RMAN a chance:

RMAN> list backupset;

RMAN>

There is no backup in control file.

RMAN> catalog device type 'sbt_tape' backuppiece '3dl4n76v_1_1';

released channel: ORA_DISK_1
released channel: ORA_SBT_TAPE_1
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=170 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: NMO v5.0.0.0
cataloged backup piece
backup piece handle=3dl4n76v_1_1 RECID=103 STAMP=709599103 

and now

RMAN> list backupset;
List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
77      Full    Unknown    SBT_TAPE    00:00:00     29-JAN-10
        BP Key: 103   Status: AVAILABLE  Compressed: NO  Tag: TAG20100129T224031
        Handle: 3dl4n76v_1_1   Media:
  List of Datafiles in backup set 77
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 8788185    29-JAN-10 +DATA/pioro/datafile/system.260.695313067 

Yes we have one already added via catalog command.

After that I have found two notes on MOS where this syntax is mentioned no 727655.1 and not public no550082.1

This example is very typical for a lot of new solutions – if you are not aware that something is impossible you can try and do that.

regards,
Marcin

ASM datafile operation in NOMOUNT

Hi

Several times I have been asked to manipulate with data files placed on ASM. In 11g there is a very nice feature – Oracle added a cp command to asmcmd tool but in 10g we have to use database instance to do all work.
One of very common task is coping a data file from one ASM disk group to other one. It can be done via RMAN copy command but this command adds an entry to RMAN repository. There is another method to do that and I found it in Oracle documentation (Yes, yes, RTFM). Convert command can be used not only to copy data file from ASM to file system and vice versa but to copy between ASM groups too.

Example

RMAN> convert datafile '+DATA/PIORO/DATAFILE/ORABPEL.260.692892311' format '+DATA/PIORO/copyofORABPEL';
Starting backup at 10-AUG-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input filename=+DATA/pioro/datafile/orabpel.260.692892311
converted datafile=+DATA/pioro/ copyofORABPEL
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:25
Finished backup at 10-AUG-09

ASM to file system

RMAN> convert datafile '+DATA/PIORO/DATAFILE/ORABPEL.260.692892311' format '/home/oracle/copyofORABPEL';
Starting backup at 10-AUG-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input filename=+DATA/pioro/datafile/orabpel.260.692892311
converted datafile=/home/oracle/copyofORABPEL
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:26
Finished backup at 10-AUG-09

Keep in mind that this command doesn’t add any information to control file so you want to rename a file you have to maintain it manually.

This command is working in NOMOUNT state too but a additional parameter is required

[oracle@piorovm ~]$ rman target /
Recovery Manager: Release 10.2.0.4.0 - Production on Mon Aug 10 22:15:44 2009
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
connected to target database: pioro10 (not mounted)

RMAN> convert datafile '+DATA/PIORO/DATAFILE/ORABPEL.260.692892311' from platform 'Linux IA (32-bit)' format '/home/oracle/copyofORABPEL';
Starting backup at 10-AUG-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK
channel ORA_DISK_1: starting datafile conversion
input filename=+DATA/pioro/datafile/orabpel.260.692892311
converted datafile=/home/oracle/copyofORABPEL
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:26
Finished backup at 10-AUG-09

If you need to copy archive log (ex. From flash recovery area) you can use a RMAN copy command - this is running only in MOUNT state.

RMAN> copy archivelog '+data/PIORO/ARCHIVELOG/2009_07_28/thread_1_seq_58.266.693419455' to '/home/oracle/arch_1_58';
Starting backup at 10-AUG-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive copy
input archive log thread=1 sequence=58 recid=1 stamp=694559795
output filename=/home/oracle/ arch_1_58 recid=3 stamp=694560324
channel ORA_DISK_1: archivelog copy complete, elapsed time: 00:00:07
Finished backup at 10-AUG-09

But it can be a dangerous command because additional entry to control file is created and if you delete this file from a file system without sending that information to RMAN archive log backup could failed. To avoid it always CROSSCHECK ARCHIVELOG ALL before archive log backup or use option “skip inaccessible”.
This is it for today but I have some additional ideas to check.


Marcin

RMAN backup from active database

Hello,
During my test with ASM I have broken my database. I couldn’t restore an ASM structure and I had to recreate database. Because it was a test environment I hadn’t backup of it but I remembered that I hopefully had a standby database. It should be easy to duplicate standby and recreate my test DB once again.
I decided to use a new feature of RMAN to restore database directly from standby. I created a script and I have started a RMAN.

run {
set newname for datafile 1 to '+DATA';
set newname for datafile 2 to '+DATA';
set newname for datafile 3 to '+DATA';
set newname for datafile 4 to '+DATA';
set newname for datafile 5 to '+DATA';
set newname for datafile 6 to '+DATA';
set newname for tempfile 1 to '+DATA';
duplicate target database to PIORO from active database;
}

rman target sys/oracle@piorost auxiliary sys/oracle@pioro cmdfile=dup_back.txt msglog=dup_back.log

What was my surprise when I found following message in log file.

Starting backup at 22-JUL-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=87 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/oracle/oradata/pioro/sysaux01.dbf
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 07/22/2009 10:01:58
ORA-19504: failed to create file "+DATA/pioro/datafile/sysaux01.211
channel ORA_DISK_1 disabled, job failed on it will be run on another channel
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 07/22/2009 10:01:58
RMAN-03015: error occurred in stored script Memory Script

RMAN-03009: failure of backup command on ORA_DISK_1 channel at 07/22/2009 10:01:58
ORA-19504: failed to create file "+DATA/pioro/datafile/sysaux01.211

Recovery Manager complete.

It was very strange because this configuration was running without any problems before my tests.

After few tries with changing a script (adding a db_file_name_convert, changing a new name) I decided to check Metalink – unfortunately there is a note number 787753.1 that this is a bug in 11.1.0.7 and will be fixed in 11.2 ! My test case was a little bit different than Metalink example because my standby DB has been placed on file system and end results was identical. I have to backup a standby to disk than restore it on my test machine.


After that I hit into another strange issue – because I have restored a control file from standby database there were standby logs and online redo logs had a wrong path. But it should be easy, isn’t? Just add new online logs on ASM and drop old one.

SQL> select group#, member from v$logfile;

1 /oracle/oradata/pioro/redo01.log
2 /oracle/oradata/pioro/redo02.log
3 /oracle/oradata/pioro/redo03.log
4 /oracle/oradata/pioro/standby01.log
5 /oracle/oradata/pioro/standby02.log
6 /oracle/oradata/pioro/standby03.log

SQL> alter database add logfile ('+DATA') size 50M;
alter database add logfile ('+DATA') size 50M
*
ERROR at line 1:
ORA-19528: redo logs being cleared may need access to files

Hmmm another nice issue – yes I have cleared logs due to reset logs option but DB is open now and I’m adding a new redo file not dropping it.
Let’s try to drop something

SQL> alter database drop logfile group 6;
alter database drop logfile group 6
*
ERROR at line 1:
ORA-19528: redo logs being cleared may need access to files

I have restarted DB in clear mode but it didn’t help. In my opinion it could be a problem with control file. So I have decided to force DB to recreated a control file using – alter database backup control file to trace – command.
It was a solution – after that I could drop and add log files groups.

SQL> alter database add logfile ('+DATA') size 50M;
Database altered.

SQL>  alter database drop logfile group 2;
Database altered.

I’m not sure if similar problem with redo logs appear in previous version of Oracle but main old approach to don’t install a first release of Oracle DB on production systems seems to be still actual and it give me more safety.


regards,
Marcin

RMAN - random errors from years

Hi,


I have been work with RMAN from 8 years and I'm still wondering why some of RMAN errors are taking from /dev/random ;)

Last example:

Environment : Linux 32 bit - Oracle 10g 10.2.04 on ASM

Performed steps:

1. Drop existing test DB from ASM - using drop database
2. Copy backup from production server into test server
3. restore controlfile from new location
4. mount database

After that I wanted to restore a database. So I have catalog all necessary backup pieces
in controlfile and check it using list backupset command.
There was one backupset with all datafile with correct status. So it is simple let try to restore
DB.

RMAN>restore database;

creating datafile No=1 name=+DATA/oracle/orcl/datafile/o1_mf_system_3n5w1nky_.dbf
released channel: t1
released channel: t2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/04/2008 10:54:29
ORA-01180: can not create datafile 1
ORA-01110: data file 1: '+DATA/dataprd/ORCL/datafile/o1_mf_system_3n5w1nky_.dbf'

Yeah, nice error.
There is some notes on metalink related to duplicated incarnation and corrupted controlfile
(BTW there is a solution to recreate a controlfile from command line before you restore datafiles - it is possible to recreate a controlfile without datafiles ???)

Anyway there was not my case.

A solution is very simple - I have found out that during catalog phase RMAN is scaning existing flash recovery area and I found archive logs in backupset from previous (droped) database and
bacuse there was differect incarnation of that archive log ... incarnation of my new database has been changed too. And now we have a strange behaviour of RMAN.

RMAN> list backupset;

still display a valid backups for that incarnation

RMAN> restore database;

raise error (see above)

Solution:

RMAN> reset database to incarnation xxx;

where xxx is a previous incarnation of database.

I can understand that Oracle could use a backup from previous incarnation in new (but why ?)
but why there is so stuip error about datafile number 1 ?

Is is impossible to display something more useful like there is no backup for that incarnation ?

ps.
All databases have this same DBID - there are clones
I know there is a bad idea to keep one DBID for many databases but I have thought that with RMAN catalog there is no issue.

regards,
Marcin