29 June 2012

What-What © Duplicate Clone using RMAN

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


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
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 and remove any AUXILIARY archive logs through RMAN.

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)



connect target /
connect catalog 
'rmanprod/pass@rmnp'
connect auxiliary 'sys/pass@<AUX_SID>'


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):

--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 log
FIX 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

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

sqlplus / as sysdba
select status from v$instance;
select distinct status from v$datafile;

rman_login <sid>
connect auxiliary 'sys/pass@<SID>'


connect target 'sys/pass@<PROD_SID>'
connect catalog 
'rmanprod/pass@rmnp'
connect auxiliary /

--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:

21 June 2012

REDO Logs? They were IMPORTANT?


Okay so it's been like... a year....

I've been busy!!  Seriously, I've been doing DBA work like a freaking FIEND!!! I've been wrecking test and dev databases left and right, learning about partitioning in all it's glory, learning about TUNING! (that's right I know how explain plans work, y'all!) and basically y'know.  Earning a living.

So... here's my first post in a really long time.  Say one is refreshing a database and not doing it the nice safe RMAN clone way, the way Tom Kyte tells us to, but does it in a "I live on the edge and will take my Production database down a walk into the dark side, becuz that's how I ROLL".... and in the excitedness of it all accidentally wipes out NOT the who-cares-about-it DEV database redo logs, but sort of overwrites the redo logs of a QA Test database.  That is up.  And running.  And no longer has the redo logs it was just dreaming about before you so painfully shoved new redo log up into its, um, tender parts.  Somehow that window was just up! And the rest of the database files are intact, but the REDO logs are now history.

This is what you can do.  By the way, you have lost all the transactions in flight that have not been archived.  There MAY be a way to recover those out of memory but those people get paid like 1000.00 an hour and have Black American Expresses.  I am not one of them.  This is the corn bread version.  Oh, and by the way, this is why we MULTIPLEX redo logs.  FYI.

RECOVER UNTIL CANCEL  Scenarios


You have accidently dropped or overwritten or have otherwise corrupted redo logs.  This can happen a couple of ways; When restoring the whole database, you can accidentally restore the online redo logs, thus overwriting the current online logs with the older, useless backups. This action forces you to perform incomplete recovery instead of the intended complete recovery, thereby losing the ability to recover valuable data contained in the overwritten redo logs.​  The database can crash, or it can just give you terrible inconsistent messages.
  • ​Beginning crash recovery of 1 threads
  • Parallel recovery started with 15 processes​
  • ​ORA-00305: log 2 of thread 1 inconsistent; belongs to another database

SQL>SHUTDOWN IMMEDIATE (or abort, depending on the mood you are in);

SQL>STARTUP MOUNT;

{​Create three additional redo log file groups }

SQL>ALTER DATABASE ADD LOGFILE GROUP 4 ('/oradata/foobar/redo04.log') SIZE 150m; 

SQL>ALTER DATABASE ADD LOGFILE GROUP 5 ('/oradata/foobar/redo05.log') SIZE 150m; 

SQL>ALTER DATABASE ADD LOGFILE GROUP 6 ('/oradata/foobar/redo06.log') SIZE 150m;

SQL>SHUTDOWN IMMEDIATE;

{delete the corrupted or incorrect redo log files in the file system}

SQL>STARTUP MOUNT;

SQL>RECOVER DATABASE UNTIL CANCEL;

SQL>CANCEL;

SQL>ALTER DATABASE OPEN RESETLOGS;​​


If this still doesn't give you the droids you are looking for then do a DBPITR and then after the RESTORE part, do this again. It will work.