How to restore RMAN cold backup from non ASM to an ASM instance with new database name

December 10, 2010 by · 2 Comments 

Last night a very good friend of mine asked me how to restore a cold backup of database taken with RMAN and restore it on a new server. Without any further thinking I’ve rushed with my answer and told him that that is very simple to do, although the source database was on file system, while the target (cloned) db should be on ASM. But, there was an extra limitation on his request. On the target machine already had a database with the same name as the source database. So, 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@/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;

   SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO
                    (dfnumber      => 1,
                     toname        => '+BSCSDATA00/DBSTREP2/system01.dbf'
                    );
   SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO
                    (dfnumber      => 2,
                     toname        => '+BSCSDATA00/DBSTREP2/sysaux01.dbf'
                    );
   SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO
                    (dfnumber      => 3,
                     toname        => '+BSCSDATA00/DBSTREP2/undotbs01.dbf'
                    );
   SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO
                    (dfnumber      => 4,
                     toname        => '+BSCSDATA00/DBSTREP2/users01.dbf'
                    );
   SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO
                    (dfnumber      => 5,
                     toname        => '+BSCSDATA00/DBSTREP2/example01.dbf'
                    );
   SYS.DBMS_BACKUP_RESTORE.RESTOREDATAFILETO
                    (dfnumber      => 6,
                     toname        => '+BSCSDATA00/DBSTREP2/t_rep01.dbf'
                    );
                    
   FOR i IN 1 .. v_max_backup_pieces
   LOOP
      DBMS_OUTPUT.PUT_LINE('Restoring piece '|| to_char(i) || ' from backup file: ' || v_input_files(i) || '...');
      SYS.DBMS_BACKUP_RESTORE.RESTOREBACKUPPIECE (done        => v_is_done,
                                                       handle      => v_input_files(i),
                                                       params      => NULL
                                                 );
      IF v_is_done
      THEN
         GOTO all_done;
      END IF;
      
   END LOOP;
   <<all_done>>
   
   SYS.DBMS_BACKUP_RESTORE.devicedeallocate;
END;
/

Step 9) Check if the “restore_all.sql” has restored all datafiles on the ASM diskgroup.

 $ export ORACLE_SID=+ASM
 $ export ORACLE_HOME=/oracle/product/11.1.0/asm
 $ asmcmd

ASMCMD> cd BSCSDATA00/DBSTREP2
ASMCMD> ls -al
Name
CONTROLFILE/
DATAFILE/
ONLINELOG/
TEMPFILE/
example01.dbf => +BSCSDATA00/DBSTREP2/DATAFILE/UNKNOWN.1016.737392407
sysaux01.dbf => +BSCSDATA00/DBSTREP2/DATAFILE/UNKNOWN.1014.737392407
system01.dbf => +BSCSDATA00/DBSTREP2/DATAFILE/UNKNOWN.1015.737391459
t_rep01.dbf => +BSCSDATA00/DBSTREP2/DATAFILE/UNKNOWN.1013.737392407
temp01.dbf => +BSCSDATA00/DBSTREP2/TEMPFILE/TEMP.1025.737393513
undotbs01.dbf => +BSCSDATA00/DBSTREP2/DATAFILE/UNKNOWN.1017.737392407
users01.dbf => +BSCSDATA00/DBSTREP2/DATAFILE/UNKNOWN.1018.737392407

Great, DBMS_BACKUP_RESTORE did the job. Now, let us re-create the controlfile;

Step 10) Re-create the control file

 $ export ORACLE_SID=DBSTREP
 $ export ORACLE_HOME=/oracle/product/11.1.0/db
 $ sqlpplus /nolog
 SQL> conn / as sysdba
 SQL> shutdown immediate
 SQL> @create_ct_dbstrep2.sql

After the control file is successfully recreated, then your target database “DBSTREP2″ is ready. Just double check again the location of redo logs and control files.

										
 $ export ORACLE_SID=+ASM
 $ export ORACLE_HOME=/oracle/product/11.1.0/asm
 $ asmcmd

ASMCMD> cd BSCSDATA00/DBSTREP2
ASMCMD> ls -al
Name
CONTROLFILE/
DATAFILE/
ONLINELOG/
TEMPFILE/
control01.ctl => +BSCSDATA00/DBSTREP2/CONTROLFILE/Current.1019.737393459
control02.ctl => +BSCSDATA00/DBSTREP2/CONTROLFILE/Current.1020.737393459
control03.ctl => +BSCSDATA00/DBSTREP2/CONTROLFILE/Current.1021.737393459
example01.dbf => +BSCSDATA00/DBSTREP2/DATAFILE/UNKNOWN.1016.737392407
redo01.log => +BSCSDATA00/DBSTREP2/ONLINELOG/group_1.1022.737393467
redo02.log => +BSCSDATA00/DBSTREP2/ONLINELOG/group_2.1023.737393469
redo03.log => +BSCSDATA00/DBSTREP2/ONLINELOG/group_3.1024.737393469
sysaux01.dbf => +BSCSDATA00/DBSTREP2/DATAFILE/UNKNOWN.1014.737392407
system01.dbf => +BSCSDATA00/DBSTREP2/DATAFILE/UNKNOWN.1015.737391459
t_rep01.dbf => +BSCSDATA00/DBSTREP2/DATAFILE/UNKNOWN.1013.737392407
temp01.dbf => +BSCSDATA00/DBSTREP2/TEMPFILE/TEMP.1025.737393513
undotbs01.dbf => +BSCSDATA00/DBSTREP2/DATAFILE/UNKNOWN.1017.737392407
users01.dbf => +BSCSDATA00/DBSTREP2/DATAFILE/UNKNOWN.1018.737392407

And yes, do not forget to update listener.ora and tnsnames.ora with your newly cloned database.
Download “create_ct_dbstrep2.sql” and “restore_all.sql” here. scripts

About Nakinov
http://de.linkedin.com/in/nakinov

Comments

2 Responses to “How to restore RMAN cold backup from non ASM to an ASM instance with new database name”
  1. Dave says:

    This is great stuff.
    So here’s a request: what if you have a database on a standard file system. It is in archivelog mode and you want to restore it to another server (same O/S) running an ASM instance. Let’s pretend the original database is down, meaning, we have no way to write the controlfile to trace or grab the spfile. And we want to create the new database with a new name. How would you do that?
    I’m building out some steps now, but I don’t yet have the environment to test… would love to see a primer on how to do it though.

  2. Seb says:

    Hi Lazar, when entering the loop on SYS.DBMS_BACKUP_RESTORE.RESTOREBACKUPPIECE
    i get the following:
    ~ ERROR at line 1:
    ~ ORA-19583: conversation terminated due to error
    ~ ORA-19870: error while restoring backup piece
    ~ /home/oracle/bkp/database_s371_p1.bkp
    ~ ORA-19615: some files not found in backup set
    ~ ORA-19613: datafile 1 not found in backup set
    ~ ORA-06512: at “SYS.X$DBMS_BACKUP_RESTORE”, line 5827
    ~ ORA-06512: at line 275

    You have no idea what it comes from ?, never encountered this error before ?
    Thanks.
    Regards,
    Seb

Speak Your Mind

Tell us what you're thinking...
and oh, if you want a pic to show with your comment, go get a gravatar!


6 + seven =