See Manual completion of failed RMAN duplicate See Legato for required parameters and configuration See RMAN effective use
Conventions Suppose we have an instance p1 with its database, we want to create a duplication of p1 We'll finally have an instance p2 with as identical copy of p1 |
Save p2 settings #Save sys.link$ table #Save old password select 'alter user ' || username || ' identified by values ''' || password ||''';' restore_pwd from dba_users order by username |
Requirements
|
We will use RMAN to create the database duplication.
First of all create an instance p2, see the document How to create an instance
In this example I will use a catalog with RMAN, but its not necessary,
if you want to create an RMAN catalog refers to how to create an RMAN catalog
init.ora Don't risk! Copy initp1.ora to initp2.ora and modify only few names. Take particular care on parameter undo_tablespace |
environment variables unset ORA_NLS33, there is a lot to say about this... |
Remove existant files from destination, no copy & paste, save the script results to relaunch in the event of a duplicate fail and for path check
10.2 bug Remove temporary datafiles from p2 if they already present to avoid: ORA-01122: database file 202 failed verification check |
Create paths on the new host from destination
| Now be sure to set the following parameters to the parameter
file of p2(obviusly relative to your settings) Don't forget to replace bold parts! db_file_name_convert=(/opt/ora9/oradata01/p1, /opt/ora9/oradata01/p2, opt/ora9/oradata01/p1, /opt/ora9/oradata01/p2)
log_file_name_convert=(/opt/ora9/oradata/p1, /opt/ora9/oradata/p2)
To determine the paths:
p1 sql>
select a.*, 'mkdir ' || target_path, source_path ||', '||target_path duplicate from (
select path source_path, replace(path, 'PRO', 'INT') target_path from (
select distinct substr(file_name, 1, instr(file_name, '/', -1) -1) path
from dba_data_files
union all
select distinct substr(file_name, 1, instr(file_name, '/', -1) -1) path
from dba_temp_files
)
) a;
select a.*, 'mkdir ' || target_path, source_path ||', '||target_path duplicate from (
select path source_path, replace(path, 'PRO', 'INT') target_path from (
select distinct substr(member, 1, instr(member, '/', -1) -1) path
from v$logfile
)
)a;
Check the existance of each target path
|
If you launch the duplicate not from p2 then be sure to set channel parameters. See Legato for required parameters and configuration |
[duplicate.cmd]
#the catalog,
connect catalog rmannispro/rmannispro@REPORMAN
#Caution! Do not confuse source and target
#the source
connect target sys/pwd@p1
connect auxiliary /
run{
#set until time "to_date('30-10-2006 10:00:00','DD-MM-YYYY HH24:MI:SS')";
set until scn 6966347421631;
allocate auxiliary channel t1 type 'SBT_TAPE';
allocate auxiliary channel t2 type 'SBT_TAPE';
allocate auxiliary channel t3 type 'SBT_TAPE';
allocate auxiliary channel t4 type 'SBT_TAPE';
allocate auxiliary channel t5 type 'SBT_TAPE';
allocate auxiliary channel t6 type 'SBT_TAPE';
allocate auxiliary channel t7 type 'SBT_TAPE';
allocate auxiliary channel t8 type 'SBT_TAPE';
duplicate target database to p2 nofilenamecheck;
}
[launch]
nohup rman msglog "duplicate.log" cmdfile "duplicate.cmd" & |
Add tempfiles Restore user passwords and dblinks from previously save |
|
BUG WARNING: please refer to your instances using a service name without domain. For example if yuou use: rman>duplicate target database to p2.fadalti.com; you'll get the error: RMAN-00571:
=========================================================== |
| You may get this error: RMAN>
duplicate target database to p2; ... This error means that you did not set correctly the parameters db_file_name_convert and log_file_name_convert, check case. For example from windows to unix use: *.db_file_name_convert=('D:\ORACLE\ORADATA\AL11\', '/opt/ora9/oradata/p1/')
*.log_file_name_convert=('D:\ORACLE\ORADATA\AL11\', '/opt/ora9/oradata/p1/')
or
add nofilenamecheck
Ex.:
run{
set until time '31-OCT-2006 06:00:00';
allocate auxiliary channel t1 type 'SBT_TAPE';
duplicate target database to OMPRE nofilenamecheck;
}
|
Now check the temporary tablespace on p1, it should not be assigned so create it. You can use Enterprise Manager to accomplish this task
When duplicating between two different machines not using NFS, just copy the backup file created on the destination machine using the same directory structure
Requirements
Same OS
Manually change database id if you require p2 registration in the same rman catalog of p1
First of all create an instance p2, see the document How to create an instance
Create a replacement string to be used for this session, example:
replacement=replace(replace(replace(replace(path, 'PRE', 'DEV'), '_genepro', '_gene'), '/redo_gene/redo_a/GENE_DEV', '/ora01_gene/GENE_DEV/redo'), '/redo_gene/redo_b/GENE_DEV', '/ora02_gene/GENE_DEV/redo')
Create the paths on destination db, help with:
source>
select a.*,
'mkdir ' || replacement target_path from (
select distinct substr(file_name, 1, instr(file_name, '/', -1) -1) path from (
select file_name from (
select file_name from dba_data_files
union all
select member from v$logfile
union all
select name from v$controlfile
union all
select file_name from dba_temp_files
)
)
) a
Lets be sure we are able to create all files:
select 'touch ' || replacement,
'rm -rf ' || replacement from (
select file_name from (
select file_name from dba_data_files
union all
select member from v$logfile
union all
select name from v$controlfile
union all
select file_name from dba_temp_files
)
) order by 1
Copy by ftp datafiles and logfiles to destination, help with this:
#manual way ftp, see ssh - Automatic Connection to speed up
source>
select 'get ' || file_name || ' ' || replacement ftp_get from (
select * from (
select file_name from dba_data_files
union all
select member from v$logfile
union all
select name from v$controlfile
union all
select file_name from dba_temp_files
)
) order by 1
#automatic way scp, sql from source and run from p2 host>
select 'scp ' || ' oracle@db019ops:' || file_name || ' ' || replacement ftp_get from (
select * from (
select file_name from dba_data_files
union all
select member from v$logfile
union all
select name from v$controlfile
union all
select file_name from dba_temp_files
)
) order by 1
Shutdown source db in a clean way
Using scp you may create an .sh file and launch it
In the same way copy the init file too
Startup source and take script to rename files:
source>
select 'alter database rename file ''' || file_name || ''' to ''' || replacement || ''';' rename_file from (
select * from (
select file_name from dba_data_files
union all
select member from v$logfile
--* no controlfile rename *
--union all
--select name from v$controlfile
--* no tempfiles *
--union all
--select file_name from dba_temp_files
)
) order by 1
Run the result on p2 and open it
Add tempfiles
select tablespace_name from dba_tablespaces where contents = 'TEMPORARY';
select file_name from dba_data_files, dba_tablespaces where dba_data_files.tablespace_name = dba_tablespaces.tablespace_name and dba_tablespaces.contents = 'TEMPORARY';
ALTER TABLESPACE "TEMP" ADD tempfile '/ora01_dtm/DTM_DEV/data/temp.dbf'
SIZE 1900M AUTOEXTEND ON NEXT 5M MAXSIZE 2000M;
|