Category Archives: RMAN

Flashback table to before / Restore table

How can I restore a table to a before state in a Oracle pluggable database (PDB)?

  • If Flashback is enabled, you will use this amazing feature and flaskback the table.
  • From Oracle 12c you can also restore a table with RMAN

 

Flashback table

If the timeframe allows it and flashback enabled.

Use the FLASHBACK TABLE statement to restore an earlier state of a table in the event of human or application error. The time in the past to which the table can be flashed back is dependent on the amount of undo data in the system.

PRE-REQUISITE: You must enable row movement for the table to carry out flashback operation on it.

ALTER TABLE my-table-to-flaskback ENABLE ROW MOVEMENT;

Now, let’s Flaskback the table:

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');

FLASHBACK TABLE my-table
[TO BEFORE DROP] |
[TO TIMESTAMP time_stamp] |
[TO SCN scn_number] |
[ENABLE TRIGGERS | DISABLE TRIGGERS];

With flashback table you can retrieve removed tables from the database, dropped using DROP and TRUNCATE commands.

 

Restore table from RMAN backup (until Oracle 12c)

RMAN enables you to recover one or more tables or table partitions to a specified point in time without affecting the remaining database objects. You can use previously-created RMAN backups to recover tables and table partitions to a specified point in time.

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

With this command we restore the SYSTEM, SYSAUX, and UNDO tablespaces and the tablespace that contain the data for this table. Having restored the database to the appropriate point in time, you can use Oracle Data Pump expdp to export the table.  And you import them into the original database, again using Oracle Data Pump impdp.

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