Let us we discuss the topic DB Point-in-time recovery using RMAN Backup

REQUIRED 

Full db Loss: All datafile with  includes system and sysaux

TAKE DATABASE FULL BACKUP

RMAN>BACKUP DATABASE PLUS ARCHIVELOG FORMAT"<BACKUP_LOCA>";

or  if you want delete the archivelog and take backup use this code

RMAN>BACKUP DATABASE PLUS ARCHIVELOG ALL DELETE INPUT FORMAT "<BACKUP_LOCA>";

CHECK BACKUP TAG USING LIST THE BACKUP CODE

RMAN>LIST BACKUP OF DATABASE SUMMARY;

CAPTURE DATABASE TIME

SQL>SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

CONNECT THE USER VALAK

SQL>conn VALAK/Edwarren

 CREATE TABLE AND INSERT VALUES

SQL>Create table  peggey  (id int, Family_mem_name varachar(25));

table is created

SQL>insert into peggey values ('1','Jannet');

1 row is inserted.

SQL>insert into peggey values ('2','Malcolum');

1 row is inserted.

SQL>insert into peggey values ('3','Mike');

1 row is inserted.

SQL>COMMIT;

commit completed.

CHECK DATA FILE LOCATION

SQL>SELECT NAME FROM v$DATAFILE;

SIMULATE FAILURE

remove data files

Oracle@Oracle11g~]$rm - rf  /u01/app/oracle/product/11.2.0.3/oradata/CA/*.dbf

START RMAN POINT IN TIME RECOVERY

Kill the DB instance, if running. You can do shut abort or kill pmon at OS level Start the DB instance and take it to Mount stage

SQL> Shut abort

check the background process on your db

Oracle@Oracle11g~]$ps -ef | grep CA

oracle    4663     1  0 18:18 ?        00:00:00 ora_pmon_CA
oracle    4665     1  0 18:18 ?        00:00:00 ora_psp0_CA
oracle    4667     1  0 18:18 ?        00:00:01 ora_mman_CA
oracle    4669     1  0 18:18 ?        00:00:00 ora_dbw0_CA
oracle    4671     1  0 18:18 ?        00:00:03 ora_lgwr_CA
oracle    4673     1  0 18:18 ?        00:00:02 ora_ckpt_CA
oracle    4675     1  1 18:18 ?        00:00:11 ora_smon_CA
oracle    4677     1  0 18:18 ?        00:00:00 ora_reco_CA
oracle    4679     1  0 18:18 ?        00:00:01 ora_cjq0_CA
oracle    4681     1  0 18:18 ?        00:00:06 ora_mmon_CA
oracle    4683     1  0 18:18 ?        00:00:01 ora_mmnl_CA
oracle    4685     1  0 18:18 ?        00:00:00 ora_d000_CA
oracle    4687     1  0 18:18 ?        00:00:01 ora_d001_CA
oracle    4689     1  0 18:18 ?        00:00:00 ora_d002_CA
oracle    4691     1  0 18:18 ?        00:00:06 ora_s000_CA
oracle    4713     1  0 18:18 ?        00:00:00 ora_arc0_CA
oracle    4715     1  0 18:18 ?        00:00:02 ora_arc1_CA
oracle    4719     1  0 18:19 ?        00:00:00 ora_qmnc_CA
oracle    4735     1  0 18:19 ?        00:00:01 ora_q000_CA
oracle    4749     1  0 18:19 ?        00:00:00 ora_q001_CA
oracle    7631     1  1 18:30 ?        00:00:01 ora_j000_CA
oracle    7674  4613  0 18:32 pts/1    00:00:00 grep CA

KILL PMON PROCESS

$ kill -9 4663

 

RMAN COMMAND FOR DATABASE TIME RECOVERY

RMAN> RUN
{
RESTORE DATABASE FROM TAG <tag>;
RECOVER DATABASE UNTIL TIME "TO_DATE('CAPTURED TIME ','YYYY-MM-DD HH24:MI:SS')";
SQL'ALTER DATABASE OPEN';
}