Category Archives: RMAN

Flashback table / Restore table from RMAN

How to restore a table in a Oracle pluggable database (PDB)?

 

— Flashback table (if the timeframe allows it and flashback enabled)
sqlplus / as sysdba
alter session set container=my-pdb;
create table my-schema.my-table-08312018 as select * from my-schema.my-table as of timestamp to_timestamp(’31-AUG-2018 10:45:52′,’DD-Mon-YYYY hh24:MI:SS’);

 

— Restore table from RMAN (from Oracle 12c)
— create directory /u01/vincent. You will need space for the tablespace of the table and also for system, sysaux
recover table my-schema.my-table OF PLUGGABLE DATABASE my-pdb until time “to_date(’08/31/2018 10:45:00′,’mm/dd/yyyy hh24:mi:ss’)”
auxiliary destination ‘/u01/vincent’ REMAP TABLE my-schema.my-table:my-table_08312018;

 

Have a nice day!

Author: Vincent Fenoll – Oracle DBA Montreal

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;
/