rman target /
crosscheck backup;
crosscheck copy;
list failure;
advise failure;
report schema;
restore database preview summary;
startup force mount;
restore database;
recover database;
alter database open;


recover database until cancel;
alter database open resetlogs;

This is very useful for copying say your Live instance to Staging or another tier. Doing a disconnected duplication means there is absolutely no chance of upsetting your Live instance. I also think it’s more intuitive. (It’s only available in 11g.)

To begin, you need a backup of your source instance, including control file snapshot and any archive logs you want to include, and transfer those to the destination server. (The copied backup files don’t need to be in a particular structure and don’t need to be in the same directory.) I rsync the files I need into /opt/oracle/tmp/{backupset,archivelog,autobackup}/2011_06_21/.... The gotcha I need to remember is that archivelogs need to be gunzipped before the first attempt at import.

If you’ve taken care of all the steps for creating the destination instance, you’re ready to kick off the import.

sqlplus / as sysdba
startup nomount
rman auxiliary /
duplicate database to DEST
until time "TO_DATE('2011-06-21 05:00:00', 'YYYY-MM-DD HH24:MI:SS')"
backup location '/path/to/tree/of/backup/files';

(where DEST is the name of the destination instance.)

sqlplus / as sysdba
alter database open

This often fails for one reason or another. Simply re-run the rman step and it should work second time. This will import everything under /path/to/tree/of/backup/files up to the specified cut-off time.
You may then want to disable archive logs if you don’t want them on the new instance.
Most likely you’ll also need to rebuild your TEMP tablespace(s).

You were trying to clone oracle instance ORA1 to oracle instance ORA2 using RMAN duplicate, but it failed and now when you connect RMAN it shows them both having name ORA1:

rman target / auxiliary SYS/pissword@ORA2
Recovery Manager: Release - Production on Thu May 26 07:23:06 2011
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: ORA1 (DBID=3447699504)
connected to auxiliary database: ORA1 (not mounted)

(the problem is the last line shows ‘ORA1’ instead of ‘ORA2’)
Before the RMAN duplicate failed, rman changed the name of the auxiliary instance to have the name of the original instance (as the first step of the cloning process). To change it back, edit it via its pfile:

create pfile from spfile

Then edit the pfile (eg initORA2.ora) to change the value of db_name from ORA1 to ORA2.
Then apply the change:

shutdown immediate;
create spfile from pfile;
startup nomount;

and re-test the connection via rman to check it now shows ‘ORA2’.

In /tmp/a.sql put


then run it using

. oramon
rman target / @/tmp/a.sql >/tmp/b.sql

then edit /tmp/b.sql with your new values, adding “QUIT;” as a new last line.

rman target / @/tmp/b.sql
rman target / @/tmp/a.sql

and check the output is what you wanted.

The oracle RMAN util can copy a database to another server, but to do so,
you need to be able to connect to the new, idle, instance.
In this example, we want to connect from ifs-db1.live.mydomain.com
to instance IFSX on dev-oracle1.dev.mydomain.com.

On the remote box (dev-oracle1) start up the instance unmounted.

sqlplus / as sysdba
SQL> startup nomount;

and configure and startup its listener. This avoids the ORA-12514 error:

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Then on the local box (ifs-db1) edit the tnsnames configuration to get round the ORA-12528 error:

ORA-12528: TNS:listener: all appropriate instances are blocking new connections
    (ADDRESS = (PROTOCOL = TCP)(HOST = dev-oracle1.dev.mydomain.com)(PORT = 1521))

The special part in that is the “(UR=A)” clause.

Now connect and check we’re on the correct server.

sqlplus SYS/pword@IFSX as sysdba
SQL> select machine from v$session where sid=1;

which proves we’re connected to the remote server.