oracle-metalink: How to restore RMAN cold backup from non ASM to an ASM instance with new database name

the task was to restore the backup from non ASM environment to another server with ASM, but under different SID.

Source database: DBSTREP
Target database: DBSTREP2

Step 1) Prepare the source database for cold backup

$ export ORACLE_SID=DBSTREP

$ export ORACLE_HOME=/oracle/product/11.1.0/db

$ sqlpplus /nolog

SQL> conn / as sysdba

SQL> shutdown immediate

SQL> startup mount

SQL> alter system set large_pool_size=100M;

Step 2) Make a cold backup of the source database

$ rman target /

RMAN> run

{

allocate channel c1 type disk format '/st2_data04/backup/dbstrep/dbstrep_%p_%s_%T.bkp';

backup database TAG 'DBSTREP';

backup current controlfile TAG 'DBSTREP_CTRLFILE';

backup spfile TAG 'DBSTREP_SPFILE';

release channel c1;

}

exit;

Step 3) Create control file from the source database

$ sqlpplus /nolog

SQL> conn / as sysdba

SQL> alter database backup controlfile to trace;

Then, change the existing path to the datafiles to point to the ASM Diskgroup. Do not forget to set the new name for the database ‘SET DATABASE “DBSTREP2″‘.

The control file create “create_ct_dbstrep2.sql” should look like:

STARTUP NOMOUNT

CREATE CONTROLFILE SET DATABASE "DBSTREP2" RESETLOGS NOARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 '+BSCSDATA00/DBSTREP2/redo01.log' SIZE 50M,

GROUP 2 '+BSCSDATA00/DBSTREP2/redo02.log' SIZE 50M,

GROUP 3 '+BSCSDATA00/DBSTREP2/redo03.log' SIZE 50M

DATAFILE

'+BSCSDATA00/DBSTREP2/system01.dbf',

'+BSCSDATA00/DBSTREP2/sysaux01.dbf',

'+BSCSDATA00/DBSTREP2/undotbs01.dbf',

'+BSCSDATA00/DBSTREP2/users01.dbf',

'+BSCSDATA00/DBSTREP2/example01.dbf',

'+BSCSDATA00/DBSTREP2/t_rep01.dbf'

CHARACTER SET WE8ISO8859P9;

ALTER DATABASE OPEN RESETLOGS;

ALTER TABLESPACE TEMP ADD TEMPFILE '+BSCSDATA00/DBSTREP2/temp01.dbf'

SIZE 62914560 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

EXIT;

Step 4) Create PFILE from SPFILE from the source database “DBSTREP”

$ sqlpplus /nolog

SQL> conn / as sysdba

SQL> create pfile from spfile;

Step 5) Generate and save the output of the script that generate DBMS_BACKUP_RESTORE.
You will need it latter. Adjust script to your DBNAME/Datafile Path.

SQL>

SELECT 'SYS.DBMS_BACKUP_RESTORE.restoredatafileto(dfnumber => '

|| file_id

|| ', toname => '

|| '''+BSCSDATA00'

|| SUBSTR (file_name,

INSTR (file_name, '/DBSTREP'),

LENGTH (file_name)

)

|| ''');' cmd

FROM dba_data_files

ORDER BY file_id

Output:
================================================================================================
SYS.DBMS_BACKUP_RESTORE.restoredatafileto(dfnumber => 1, toname => ‘+BSCSDATA00/DBSTREP/system01.dbf’);
SYS.DBMS_BACKUP_RESTORE.restoredatafileto(dfnumber => 2, toname => ‘+BSCSDATA00/DBSTREP/sysaux01.dbf’);
SYS.DBMS_BACKUP_RESTORE.restoredatafileto(dfnumber => 3, toname => ‘+BSCSDATA00/DBSTREP/undotbs01.dbf’);
SYS.DBMS_BACKUP_RESTORE.restoredatafileto(dfnumber => 4, toname => ‘+BSCSDATA00/DBSTREP/users01.dbf’);
SYS.DBMS_BACKUP_RESTORE.restoredatafileto(dfnumber => 5, toname => ‘+BSCSDATA00/DBSTREP/example01.dbf’);
SYS.DBMS_BACKUP_RESTORE.restoredatafileto(dfnumber => 6, toname => ‘+BSCSDATA00/DBSTREP/t_rep01.dbf’);

Step 6) Copy the backup of source database “DBSTREP” to the target server.

1	1@/st2_data04/backup/dbstrep$ scp .bkp .ora oraclert@destination:/CDR/rman_backup/dbstrep

step 7) Copy the PFILE of source database “DBSTREP” to the target server in $ORACLE_HOME/dbs.
Modify it, so it points to the new sid “DBSTREP2″. Also create required folders on disk.

*.audit_file_dest='/oraclert/admin/DBSTREP2/adump'

*.audit_trail='none'

*.compatible='11.1.0.0.0'

*.control_files='+BSCSDATA00/DBSTREP2/control01.ctl','+BSCSDATA00/DBSTREP2/control02.ctl','+BSCSDATA00/DBSTREP2/control03.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='DBSTREP2'

*.ddl_lock_timeout=10

*.diagnostic_dest='/oraclert'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=DBSTREPXDB)'

*.large_pool_size=0

*.local_listener='LISTENER'

*.open_cursors=300

*.pga_aggregate_target=209715200

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_max_size=1073741824

*.sga_target=1073741824

*.undo_tablespace='UNDOTBS1'

Step 8 – Connect to the target database “DBSTREP2″ and start it in NOMOUNT.
After that run the script “restore_all.sql” which uses DBMS_BACKUP_RESTORE package.

$ export ORACLE_SID=DBSTREP2

$ export ORACLE_HOME=/oracle/product/11.1.0/db

$ sqlpplus /nolog

SQL> conn / as sysdba

SQL> startup nomount

SQL> @restore_all.sql

Contents of the “restore_all.sql”:

DECLARE

v_handle VARCHAR2 (500);

v_is_done BOOLEAN := FALSE;

The t_input_files PL/SQL table entries must reflect the backuppieces

comprising the backupset

TYPE t_input_files IS TABLE OF VARCHAR2 (1000)

INDEX BY BINARY_INTEGER;

The v_max_backup_pieces variable must reflect the number of backuppieces

v_max_backup_pieces NUMBER := 6;

v_input_files t_input_files;

BEGIN

If the backup containt more one backupset (example: was taken with more than 1 allocated channel),

then you should add them in the array accordingly 2,3,4 ...

v_input_files (1) := '/CDR/rman_backup/dbstrep/dbstrep_1_5_20101210.bkp';

/* v_input_files (2) := '/CDR/rman_backup/dbstrep/dbstrep_1_6_20101210.bkp'; */

/* v_input_files (3) := '/CDR/rman_backup/dbstrep/dbstrep_1_7_20101210.bkp'; */

/* Number of backup pieces in a backupset */

v_max_backup_pieces := 6;

v_handle := SYS.DBMS_BACKUP_RESTORE.DEVICEALLOCATE;

/* Start the restore conversation; From file system to ASM Diskgroup (+BSCSDATA00) */

SYS.DBMS_BACKUP_RESTORE.RESTORESETDATAFILE;