RMAN cheatsheet

  • Published on Sep 6 2013
  • # 1,417
  • oracle

Commands
SET
LIST
RESTORE

LIST ARCHIVELOG
Objects
archivelogRecordSpecifier

# List archives by log sequence
LIST BACKUP OF ARCHIVELOG FROM SEQUENCE 3398;

# List archives with NEXT_TIME (see <a href="http://docs.oracle.com/cd/B28359_01/server.111/b28320/dynviews_1016.htm">v$archived_log.next_time</a>) is  greater then FROM TIME - last day (24 hours)
LIST BACKUP OF ARCHIVELOG FROM TIME "SYSDATE-1";

# List archives between two dates 
LIST BACKUP OF ARCHIVELOG 
  FROM TIME "TO_DATE('09/05/2013 22:00:00', 'MM/DD/YYYY hh24:mi:ss')" 
  UNTIL TIME "TO_DATE('09/06/2013 04:00:00', 'MM/DD/YYYY hh24:mi:ss')";

RESTORE ARCHIVELOG
Sometimes you need to autonomously restore a set of archives – in my case I need them to get an abended Golden Gate Extract process in business again.

2013-09-06 10:24:19  WARNING OGG-01423  No valid default archive log destination directory found for thread 1.

2013-09-06 10:24:19  INFO    OGG-01513  Positioning to Sequence 3389, RBA 475152, SCN 1390.3913669324.

.

2013-09-06 10:25:34  ERROR   OGG-00446  Could not find archived log for sequence 3389 thread 1 under alternative or default destinations. SQL <SELECT  name FROM v$archived_log WHERE sequence# = :ora_seq_no AND thread# = :ora_thread AND resetlogs_id = :ora_resetlog_id AND archived = 'YES' AND deleted = 'NO' AND name not like '+%' AND standby_dest = 'NO' >. Last alternative log tried /tmp/_temp.archives/<database_name>/archivelog/1_3389_819925872.dbf., error
retrieving redo file name for sequence 3389, archived = 1, use_alternate = 0Not able to establish initial position for sequence 3389, rba 475152.

2013-09-06 10:25:34  ERROR   OGG-01668  PROCESS ABENDING.

OK, apparently Golden Gate need the archives from log sequence number 3389 and ‘above’.

Command below restores all archives from 3389 until ‘now’.

RUN 
{
  ALLOCATE CHANNEL T1 TYPE 'sbt_tape' PARMS
    'ENV=(TDPO_OPTFILE=<location_tsm_ora.opt>)' ;

  SET ARCHIVELOG DESTINATION TO '/tmp/_temp.archives/<database_name>/archivelog';
  RESTORE ARCHIVELOG FROM SEQUENCE 3389 ;
		
  RELEASE CHANNEL T1;
}

allocated channel: T1
channel T1: SID=390 device type=SBT_TAPE
channel T1: Data Protection for Oracle: version 5.5.2.0

executing command: SET ARCHIVELOG DESTINATION

Starting restore at 06-SEP-13

channel T1: starting archived log restore to user-specified destination
archived log destination=/tmp/_temp.archives/<database_name>/archivelog
channel T1: restoring archived log
archived log thread=1 sequence=3389
.
.
.
channel T1: reading from backup piece archives_tsm_diskpool_<database_name>_2271_1_825427407
channel T1: piece handle=archives_tsm_diskpool_<database_name>_2271_1_825427407 tag=NULL
channel T1: restored backup piece 1
channel T1: restore complete, elapsed time: 00:02:45
Finished restore at 06-SEP-13

released channel: T1

LIST & RESTORE SPFILE BACKUPS
RESTORE spfile

RUN
{
  ALLOCATE CHANNEL T1 TYPE 'sbt_tape' PARMS 'ENV=(TDPO_OPTFILE=...tsm_ora.opt)' ;
  
  # restore spfile to specified location
  RESTORE SPFILE TO '/tmp/spfile<oracle_sid>.ora' ;
  
  # restore spfile as pfile to specified location
  RESTORE SPFILE TO PFILE '/tmp/init<oracle_sid>.ora' ;
}
RUN
{  
  ALLOCATE CHANNEL T1 TYPE 'sbt_tape' PARMS 'ENV=(TDPO_OPTFILE=...tsm_ora.opt)' ;

  # restore spfile before date
  SET UNTIL TIME "TO_DATE('01/01/2013 22:00:00', 'MM/DD/YYYY hh24:mi:ss')" ;
  RESTORE SPFILE TO PFILE '/tmp/init<oracle_sid>_20130101.ora' ;
  
  RELEASE CHANNEL T1 ;
}

Leave a Reply

Helpful? - leave your note below so I can brag

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>