Duplicate Database from ASM to Non- ASM Database Using RMAN

ygzhou518發表於2011-12-12
DatabaseASM

Step 1:
Determine how much disk space will be required.

[oracle@standbyDB backup]$ sqlplus / as sysdba 
SQL*Plus: Release 11.2.0.1.0 Production on Sun Dec 11 20:46:15 2011 
Copyright (c) 1982, 2009, Oracle.  All rights reserved.
  
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
 
SQL> select name from v$database;
 
NAME
---------
YGZHOU
 
SQL>select DF.TOTAL/1048576 "DataFile Size Mb",LOG.TOTAL/1048576 "Redo Log Size Mb",CONTROL.TOTAL/1048576 "Control File Size Mb",(DF.TOTAL + LOG.TOTAL +CONTROL.TOTAL)/1048576 "Total Size Mb" from dual,(select sum(a.bytes) TOTAL from dba_data_files a) DF,(select sum(b.bytes) TOTAL from v$log b) LOG,(select sum((cffsz+1)*cfbsz) TOTAL from x$kcccf c) CONTROL ; 

DataFile Size Mb Redo Log Size Mb Control File Size Mb Total Size Mb
---------------- ---------------- -------------------- -------------
          935              150             9.515625    1094.51563
 
Calculate space for list of datafiles within primary database


SQL> select DF.TOTAL/1048576 "DataFile Size Mb",LOG.TOTAL/1048576 "Redo Log Size Mb",CONTROL.TOTAL/1048576 "Control File Size Mb",(DF.TOTAL + LOG.TOTAL + CONTROL.TOTAL)/1048576 "Total Size Mb" from dual,(select sum(a.bytes) TOTAL from dba_data_files a where tablespace_name in('SYSTEM','SYSAUX','UNDO','USERS')) DF,(select sum(b.bytes) TOTAL from v$log b) LOG,(select sum((cffsz+1)*cfbsz) TOTAL from x$kcccf c) CONTROL;

DataFile Size Mb Redo Log Size Mb Control File Size Mb Total Size Mb
---------------- ---------------- -------------------- -------------
            395              150             9.515625    554.515625

Step 2: Backup Source database

RMAN>@rman_backup.sh
 
RMAN> run{
2> allocate channel d1 type disk;
3> backup format '/home/oracle/backup/df_t%t_s%s_p%p' database;
4> sql 'alter system archive log current';
5> backup format '/home/oracle/backup/al_t%t_s%s_p%p' archivelog all;
6> release channel d1;
7> }

using target database control file instead of recovery catalog
allocated channel: d1
channel d1: SID=40 device type=DISK
 
Starting backup at 11-DEC-11
channel d1: starting compressed full datafile backup set
channel d1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA1/ygzhou/datafile/system.260.767992835
input datafile file number=00003 name=+DATA1/ygzhou/datafile/undotbs1.262.767992911
input datafile file number=00005 name=+DATA1/ygzhou/datafile/today01.dbf
input datafile file number=00002 name=+DATA1/ygzhou/datafile/sysaux.261.767992879
input datafile file number=00006 name=+DATA2/ygzhou/datafile/ygzhou01.dbf
input datafile file number=00007 name=+DATA1/ygzhou/datafile/fbda01.dbf
input datafile file number=00004 name=+DATA1/ygzhou/datafile/users.264.767993055
channel d1: starting piece 1 at 11-DEC-11
channel d1: finished piece 1 at 11-DEC-11
piece handle=/home/oracle/backup/df_t769639433_s21_p1 tag=TAG20111211T204352 comment=NONE
channel d1: backup set complete, elapsed time: 00:01:06
channel d1: starting compressed full datafile backup set
channel d1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel d1: starting piece 1 at 11-DEC-11
channel d1: finished piece 1 at 11-DEC-11
piece handle=/home/oracle/backup/df_t769639509_s22_p1 tag=TAG20111211T204352 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:03
Finished backup at 11-DEC-11
 
sql statement: alter system archive log current
 
Starting backup at 11-DEC-11
current log archived
channel d1: starting compressed archived log backup set
channel d1: specifying archived log(s) in backup set
input archived log thread=1 sequence=107 RECID=185 STAMP=769611458
input archived log thread=1 sequence=108 RECID=186 STAMP=769631273
input archived log thread=1 sequence=109 RECID=189 STAMP=769639521
input archived log thread=1 sequence=110 RECID=191 STAMP=769639522
channel d1: starting piece 1 at 11-DEC-11
channel d1: finished piece 1 at 11-DEC-11
piece handle=/home/oracle/backup/al_t769639523_s23_p1 tag=TAG20111211T204523 comment=NONE
channel d1: backup set complete, elapsed time: 00:00:07
Finished backup at 11-DEC-11
 
released channel: d1
 
RMAN> **end-of-file**

Step 3: Create pfile & edit some required parameters

SQL> create pfile='/home/oracle/backup/initygzhou.ora' from spfile;
 
File created.

[oracle@standbyDB backup]$ strings initygzhou.ora
ygzhou.__db_cache_size=25165824
ygzhou.__java_pool_size=4194304
ygzhou.__large_pool_size=4194304
ygzhou.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
ygzhou.__pga_aggregate_target=163577856
ygzhou.__sga_target=150994944
ygzhou.__shared_io_pool_size=0
ygzhou.__shared_pool_size=109051904
ygzhou.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/ygzhou/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
#*.control_files='+DATA1/ygzhou/controlfile/current.256.767992811'
*.db_block_size=8192
#*.db_create_file_dest='+DATA1'
*.db_domain=''
*.db_name='ygzhou'
*.diagnostic_dest='/u01/app/oracle'
*.java_pool_size=0
*.log_archive_dest_1='location=/home/oracle/archivelog1'
*.log_archive_dest_2='location=/home/oracle/archivelog2'
*.memory_target=314572800
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

*.db_file_name_convert =("+DATA","/u01/app/oracle/oradata/ygzhou")
*.log_file_name_convert =("+DATA","/u01/app/oracle/oradata/ygzhou")

*.control_files=('/u01/app/oracle/oradata/ygzhou/controlfile01.ctl','/u01/app/oracle/oradata/ygzhou/controlfile02.ctl')

Step 4: Startup nomount for YGZHOU database

[oracle@ygzhou518 ~]$ export ORACLE_SID=ygzhou
[oracle@ygzhou518 ~]$ sqlplus / as sysdba

SQL> startup nomount pfile='/home/oracle/backup/initygzhou.ora'

Step 5: Create duplicate database from here

[oracle@ygzhou518 ~]$ export ORACLE_SID=ygzhou
[oracle@ygzhou518 ~]$ rman target  sys/system@target  auxiliary /

RMAN> run{
allocate auxiliary channel C1 device type disk;
duplicate target database to YGZHOU;
}

RMAN> duplicate target database to YGZHOU;
 
Starting Duplicate Db at 13-DEC-11
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 device type=DISK
 
contents of Memory Script.:
{
   sql clone "create spfile from memory";
}
executing Memory Script
 
sql statement: create spfile from memory
 
contents of Memory Script.:
{
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script
 
Oracle instance shut down
 
connected to auxiliary database (not started)
Oracle instance started
 
Total System Global Area     313860096 bytes
 
Fixed Size                     1336232 bytes
Variable Size                281021528 bytes
Database Buffers              25165824 bytes
Redo Buffers                   6336512 bytes

contents of Memory Script.:
{
   sql clone "alter system set  db_name =''YGZHOU'' comment=''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =''YGZHOU'' comment=''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile;
   alter clone database mount;
}
executing Memory Script
 
sql statement: alter system set  db_name = ''YGZHOU'' comment= ''Modified by RMAN duplicate'' scope=spfile
 
sql statement: alter system set  db_unique_name =  ''YGZHOU'' comment= ''Modified by RMAN duplicate'' scope=spfile
 
Oracle instance shut down
 
Oracle instance started
 
Total System Global Area     313860096 bytes
 
Fixed Size                     1336232 bytes
Variable Size                281021528 bytes
Database Buffers              25165824 bytes
Redo Buffers                   6336512 bytes
 
Starting restore at 13-DEC-11
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=18 device type=DISK
 
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/backup/df_t769639509_s22_p1

Duplicate database was successfully created

Step 6:Check Duplicate Database

【注意】：在還原前建好所有的必須目錄

mkdir -p /u01/app/oradata/ygzhou/

....

 

 

 

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/24867586/viewspace-713153/，如需轉載，請註明出處，否則將追究法律責任。

相關文章