Seriously, a duplicate clone using RMAN is about the most fun anyone can do with a database... why?? Because it's EASY. It WORKS. It never ever goes WRONG. So here's what to do.... step by step.
Refreshing a test database from a production database using RMAN duplicate (UNIX SYSTEM)
by DanceRat
Make sure space allocation (disk) in AUXILIARY (DEV/TEST) is equal to that in TARGET (PRODUCTION)
-----------------------------------------------------------------------------------------------------------------------------
TARGET (PRODUCTION) DATABASE:Run RMAN backup.
After backup, find Media needed from Netbackup using RMAN Run command:
run {
allocate channel 'rec_01' type sbt;
set until time "to_date('03/20/2012 09:00:00','mm/dd/yyyy hh24:mi:ss')";
restore database preview recall;
release channel 'rec_01';
}
then
--comment- use the same until time timestamp info from the media query previous.
VALIDATION
Check alert logFIX TEMPFILES
Check the current status of updated temp files
If bad then
or
create temp tablespace from script generated earlier.
Validate database directory paths - these will still have production paths
Start the listener using lsnrstrt
Stop and start the database using orastop and orastart
Once duplication is complete then crontab –e and start the manage archive log scripts again by unremming the statement. (if you use crontab to manage archive logs)
drop user <USERNAME> cascade;
Here are a couple of common errors and solutions:
HELPFUL SCRIPTS:
Identify and copy database files:
select name, bytes
See how long time remaining for rman duplicate is:
select sid, serial#, context,
round(sofar/totalwork*100,2) "% Complete",
substr(to_char(sysdate,'yymmdd hh24:mi:ss'),1,15) "Time Now",
elapsed_seconds, opname
from v$session_longops;
After cloning, and when registering with rman: "ORA-01400 cannot insert NULL" - see if dbid has been changed, if not then Reset DBID
Verify that password file exists on TARGET and AUXILIARY. If password file exists ONLY on AUXILIARY, then run RMAN client from TARGET during procedure.
Refreshing a test database from a production database using RMAN duplicate (UNIX SYSTEM)
by DanceRat
PREP STEPS:
Verify Server name Database name and Database version on both production and test sides.
Check the TARGET (PRODUCTION) and AUXILIARY (DEV/TEST) database versions, if same, proceed with refresh.
Check mount point of TARGET (PRODUCTION) and AUXILIARY (DEV/TEST) databases
Check the TARGET (PRODUCTION) and AUXILIARY (DEV/TEST) database versions, if same, proceed with refresh.
Check mount point of TARGET (PRODUCTION) and AUXILIARY (DEV/TEST) databases
df –k | grep <dbname> | sort |
Make sure space allocation (disk) in AUXILIARY (DEV/TEST) is equal to that in TARGET (PRODUCTION)
Backup AUXILIARY (DEV/TEST) schema passwords, users, roles using datapump
Backup AUXILIARY (DEV/TEST) functions, procedures, db_links, packages (optional) using datapump
Verify any tables that need to be datapumped and saved.
Backup AUXILIARY (DEV/TEST) functions, procedures, db_links, packages (optional) using datapump
Verify any tables that need to be datapumped and saved.
Backup and remove any AUXILIARY archive logs through RMAN.
Stop management of archive logs through crontab (if that's how you manage your archive logs).
Stop management of archive logs through crontab (if that's how you manage your archive logs).
Do a full database backup through RMAN.
Notify clients and fellow DBA Brethren that you are doing work on the database via email:
-----------------------------------------------------------------------------------------------------------------------------
I will be working on the following, please disregard warnings until renotified.
instance name @ hostname
TARGET (PRODUCTION) DATABASE:Run RMAN backup.
After backup, find Media needed from Netbackup using RMAN Run command:
run {
allocate channel 'rec_01' type sbt;
set until time "to_date('03/20/2012 09:00:00','mm/dd/yyyy hh24:mi:ss')";
restore database preview recall;
release channel 'rec_01';
}
-----------------------------------------------------------------------------------------------------------------------------
Turn monitoring off on AUXILIARY (DEV/TEST)
Turn monitoring off on AUXILIARY (DEV/TEST)
then
run {
allocate auxiliary channel 'aux_01' type sbt;
allocate auxiliary channel 'aux_02' type sbt;
send 'NB_ORA_CLIENT=prodhostname.corp.dom';
set until time "to_date('02/01/2012 06:15:00','mm/dd/yyyy hh24:mi:ss')";
duplicate target database to {AUX_sidname} nofilenamecheck;
release channel aux_01;
release channel aux_02;
}
|
--comment- use the same until time timestamp info from the media query previous.
OR
if running from AUXILIARY (TEST) DATABASE:
run {
allocate auxiliary channel 'aux_01' type sbt;
allocate auxiliary channel 'aux_02' type sbt;
send 'NB_ORA_CLIENT=targethostname.corp.dom;
set until time "to_date('02/01/2012 06:15:00','mm/dd/yyyy hh24:mi:ss')";
duplicate target database to {AUX_sidname} nofilenamecheck;
release channel aux_01;
release channel aux_02;
}
|
IMPORTANT: IF YOU HAVE A WALLET, COPY THE WALLET OVER FROM PROD TO TESTFrom TEST host: scp –r oracle@hostname:/PRODSID_orabase/admin/SID/wallet//TESTSID_orabase/admin/SID/wallet
AUXILIARY (DEV/TEST DATABASE):
AUXILIARY (DEV/TEST DATABASE):
--comment- even though the AUX db started in a nomount state, when the database was duplicated, RMAN finishes it by opening it into active mode with a new dbid.
--comment- if, for any reason, the clone is interrupted during the restore process, the whole RMAN duplicate can be started again as long as the memory script has not passed the "switch clone datafile all" stage. In the case of an interruption, do a shutdown immediate and restart the database clone process from the beginning, i.e. startup nomount pfile=...blah blah blah
VALIDATION
Check alert logFIX TEMPFILES
Check the current status of updated temp files
SELECT v.file#, t.file_name, v.status FROM dba_temp_files t, v$tempfile v WHERE t.file_id = v.file#; |
If bad then
alter database tempfile '/<SID>_oradata/oradata/tempFILENAME.dbf' drop;
ALTER TABLESPACE TEMP ADD TEMPFILE
'/<SID>_oradata/oradata/tempFILENAME.dbf' SIZE 8388616192 REUSE AUTOEXTEND OFF; |
or
create temp tablespace from script generated earlier.
select name from v$datafile; select member from v$logfile; select name from v$controlfile; select name from v$tempfile; select name from v$datafile where name like '%MISS%'; select file_name from dba_data_files where file_name like '%MISS%'; select * from v$recover_file; |
Validate database directory paths - these will still have production paths
Start the listener using lsnrstrt
Stop and start the database using orastop and orastart
RMAN IT AND BACK UP
Once duplication is complete then crontab –e and start the manage archive log scripts again by unremming the statement. (if you use crontab to manage archive logs)
TROUBLESHOOTING
Don't forget to clean up users if not needed.
drop user <USERNAME> cascade;
Here are a couple of common errors and solutions:
HELPFUL SCRIPTS:
Identify and copy database files:
select name, bytes
from (select name, bytes
from v$datafile
union all
select name, bytes
from v$tempfile
union all
select lf.member "name", l.bytes
from v$logfile lf
, v$log l
where lf.group# = l.group#) used
,(select sum(bytes) as poo
from dba_free_space) free
select sid, serial#, context,
round(sofar/totalwork*100,2) "% Complete",
substr(to_char(sysdate,'yymmdd hh24:mi:ss'),1,15) "Time Now",
elapsed_seconds, opname
from v$session_longops;
After cloning, and when registering with rman: "ORA-01400 cannot insert NULL" - see if dbid has been changed, if not then Reset DBID
sqlplus / as sysdba
select status from v$instance;
select distinct status from v$datafile; |
rman_login <sid>
connect auxiliary 'sys/pass@<SID>'
|
--comment- look for the last hotbackup piece without arc logs. RMAN automatically plays the logs when duplication occurs, but it cuts down on time.
AUXILIARY (DEV/TEST) DATABASE:
Create simple pfile from spfile on Production (Target) and edit with conversion lines:
SQL> CREATE PFILE='/export/home/oracle/pfilenewSID.ora' from SPFILE=$ORACLE_HOME/dbs/init{sid}.ora |
Example: (comment -- Replace {AUXsid} with instance sid name)
################################################################
# Special init.ora for clone-style refresh of
# TEST (auxiliary) from PROD (target)
################################################################
db_name = {AUX_sid_name_here}
db_block_size = 8192
compatible = 11.1.0
#---------------------------------------------------------------
sga_max_size = 1024M
sga_target = 840M
log_buffer = 1048576
#---------------------------------------------------------------
control_files = ('/{AUXsid}_oradata/control01.ctl',
'/{AUXsid}_oradata/control02.ctl',
'/{AUXsid}_oradata/control03.ctl')
#---------------------------------------------------------------
diagnostic_dest = /{AUXsid}_orabase
audit_file_dest = /{AUXsid}_orabase/admin/adump
log_archive_dest_1 ='LOCATION=/{AUXsid}_oraarch'
log_archive_format ={AUXsid}_log_%s_%t_%r.arc
#---------------------------------------------------------------
db_file_name_convert = ('/{PRODsid}_oradata','/{AUXsid}_oradata')
log_file_name_convert = ('/{PRODsid}_oradata','/{AUXsid}_oradata')
#------------------------------------------------------------------------------
# redo/undo settings
#------------------------------------------------------------------------------
undo_management = auto
undo_tablespace = undots
undo_retention = 3600 # 900 seconds is default
|
Verify that password file exists on TARGET and AUXILIARY. If password file exists ONLY on AUXILIARY, then run RMAN client from TARGET during procedure.
Notify clients and DBA Brethren that you are working on database...
-----------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------
I will be working on the following, please disregard warnings until renotified.
instance name @ hostname
-----------------------------------------------------------------------------------------------------------------------------
AUXILIARY (DEV/TEST DATABASE:)
--comment- Shutdown listeners on AUXILIARY (DEV/TEST) before databases are shutdown as a courtesy to yourself and clients.
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP NOMOUNT PFILE='/<SID>_orabase/product/<version>/dbs/init<sid>.ora_clone';
|
if running from TARGET (PRODUCTION) DATABASE: