Restore archivelogs

How the Oracle DBA can restore archivelogs from tape with an Oracle RMAN Script:

DBA Oracle
Compatibility: Oracle 8i 9i 10g 11g
Creator: Vincent Fenoll – Montreal

 run {      
	allocate channel t1 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=xxxxxx)';
	restore archivelog from logseq 2212 until logseq 2213 thread 1;
	release channel t1;
     }

Duplicate db using RMAN script

################################################
#
# Compatible: Oracle 8i 9i 10g 11g
#
################################################
#
# Duplicate a database until time…
# Database must be in « startup nomount » mode
#
################################################


connect catalog rman/****@rman
connect target sys/****@****.world
connect auxiliary / ;
run {
        set until time= "to_date('25/08/2009 06:18:01','dd/mm/yyyy hh24:mi:ss')";

        # if restoring from tape (else Allocate type disk):
        allocate channel ch1 type 'sbt_tape' parms 'ENV=(NSR_SERVER=*****,NSR_DATA_VOLUME_POOL=*****,NSR_CLIENT=*****)';
        allocate auxiliary channel aux1  type 'sbt_tape' parms 'ENV=(NSR_SERVER=*****,NSR_DATA_VOLUME_POOL=*****,NSR_CLIENT=*****)';
        duplicate target database to MYNEWDB nofilenamecheck;
        release channel ch1;
}

Restore until time rman script

If flashback Database is not available (Flashback off or timeframe too large), you will have to restore your Oracle database with Recovery Manager (RMAN).

################################################
#
# Compatible: Oracle 8i 9i 10g 11g 12c
#
################################################
#
# Restore a database until time...
# Database must be in "startup mount" mode
#
################################################

. oraenv 
==> my_ORACLE_SID

# Shutdown the database and put on mounted state (cluster_database = FALSE)
sqlplus / as sysdba
select INSTANCE_NAME from v$instance;   # verify you are on the right Oracle instance
shutdown abort
startup mount
exit

#
connect target /
connect catalog rman/***@rman   # if you use a catalog

RUN
{
  # if restoring from tape (else Allocate type disk):
  ALLOCATE CHANNEL chan1 TYPE 'SBT_TAPE';
  ALLOCATE CHANNEL chan2 TYPE 'SBT_TAPE';
  # SEND 'NSR_ENV=(NSR_SERVER=xxxxx, NSR_DATA_VOLUME_POOL=xxxxx)';

  set until time= "to_date('02/09/2018 17:00:00','dd/mm/yyyy hh24:mi:ss')";
  RESTORE DATABASE;
  RECOVER DATABASE;
  RELEASE CHANNEL chan1;
}

When the restore is finished, in SQL*Plus:
alter database open resetlogs;

To monitor RMAN restore progress:

 -- Progress percentage:
select
sid,
start_time,
totalwork
sofar,
(sofar/totalwork) * 100 pct_done
from
v$session_longops
where
totalwork > sofar
AND
opname NOT LIKE '%aggregate%'
AND
opname like 'RMAN%';

-- Waits:
select
client_info,
event,
seconds_in_wait
from
v$process p,
v$session s
where
p.addr = s.paddr
and
client_info like 'rman channel=%';

-- More infos:
select 
to_char(sysdate,'DD-MON HH24:MI:SS') Collection_DateStamp, sid, opname, target, sofar, totalwork,
    units, to_char(start_time,'HH24:MI:SS') StartTime,
    time_remaining, message, username
    from
 v$session_longops
    where
 sofar != totalwork
   order by
 start_time;
 

Author: Vincent Fenoll – Oracle DBA Montreal

Delete from catalogue

-- ################################################
-- # Creator: Vincent Fenoll, Arnaud Michel
-- # Compatible: Oracle 8i 9i 10g
-- #
-- ################################################
-- #
-- # Delete an Oracle database from the RMAN catalogue
-- #
-- ################################################

In RMAN database:
sqlplus rman/****@rman


-- PART I: Unregister One Database

SQL> SELECT db_key, db_id FROM db WHERE db_id = 1231274694;
This query should return exactly one row.
DB_KEY DB_ID
---------- ----------
1 1237603294
1 row selected.

SQL> EXECUTE dbms_rcvcat.unregisterdatabase(db_key, db_id)


-- PART II: Unregister several databases with a PL*SQL Loop

set serveroutput on
exec dbms_output.enable(1000000);
DECLARE 
CURSOR C_DBKEY IS
  SELECT db_key, db_id FROM db WHERE db_id in (838734099,2567483700,4125117868,2420232503);

BEGIN
 For Cur IN C_DBKEY Loop
     dbms_rcvcat.unregisterdatabase(cur.db_key, cur.db_id);
     -- dbms_output.put_line( 'exec dbms_rcvcat.unregisterdatabase(' || To_char( Cur.db_key ) || ' , ' || Cur.db_id  || ');' ) ;
 End loop ;
END;
/

Duplicate db using Shell & RMAN

# Author: Vincent Fenoll another Oracle DBA Montreal

#!/usr/bin/ksh
# SCRIPT: duplicate_db_from_time.sh
################################################
#
# Created: 2009/01/01
# Compatible: Oracle 8i 9i 10g 11g
#
################################################
#
# Duplicate database using rman
#
################################################
#
# PRE-REQUISITES:
#    * Instance: NOMOUNT
#    * local variables setted
#    * Oracle variables setted: log_file_name_convert, db_file_name_convert
#
# EXAMPLE:    ./duplicate_db_from_time.sh sys/***@OLDBASE rman/***@rman "08-03-2009 06:16:49″ host_source
#
# Please modify with your value  :      duplicate target database to NEWBASE
#
################################################


if [ "$ORACLE_HOME" = "" ]; then
        echo "Erreur: ORACLE_HOME must be defined"
        exit 1
fi

if [ "$1" = "" -o "$2" = "" -o "$3" = "" -o "$4" = "" ]; then
        echo "Synt: $0 [connect_target] [connect_rmancatalog] [date=DD-MM-YYYY HH24:MI:SS] [server_source_name]"
        exit 1
fi

export NLS_DATE_FORMAT="DD-MM-YYYY HH24:MI:SS"

$ORACLE_HOME/bin/rman target $1 rcvcat $2 auxiliary / << EOF run { set until time "to_date('$3', 'DD-MM-YYYY HH24:MI:SS')"; allocate auxiliary channel t1 type 'SBT_TAPE'; -- If using Networker send 'NSR_ENV=(NSR_SERVER=networkerxxx, NSR_DATA_VOLUME_POOL=FULLxxx, NSR_CLIENT=$4)'; set command id to 'rman_duplicate_from_time'; duplicate target database to NEWBASE NOFILENAMECHECK; release channel t1; } exit; EOF egrep "RMAN-|ORA-" /tmp/resto_rman_$$.log > /dev/null
  if [ $? -eq 0 ]
  then
    return 12
  fi

  return 0