Backup and restore of pluggable databses, CDB, no cdb and DBPITR in oracle 12c

Backup and restore of pluggable databses, CDB in oracle 12c

Note: Please ensure required backup is available , it is mandatory when working on backup and recovery... No backup mean no recovery, as simple as that.

run

{

configure controlfile autobackup on;

CONFIGURE BACKUP OPTIMIZATION off;

CONFIGURE DEVICE TYPE DISK PARALLELISM 4;

backup current controlfile spfile;

BACKUP   AS COMPRESSED BACKUPSET   DATABASE PLUS ARCHIVELOG  DELETE ALL INPUT format '/tmp/madhav/%d_inc0_%T_%U.bak';

}









select to_char(sysdate,'hh24:mi dd-mm-yyyy'), current_scn from v$database;







TO_CHAR(SYSDATE, CURRENT_SCN

---------------- -----------

08:44 07-10-2016    72629675



--- Point In Time Recovery (PITR) of a Pluggable Database (PDB) in oracle 12c



run

 {

 alter pluggable database pdborcl close immediate;

 restore pluggable database pdborcl;

 recover pluggable database pdborcl until time "to_date('08:44 07-10-2016','hh24:mi dd-mm-yyyy')"  auxiliary destination '/tmp';

 alter pluggable database pdborcl open resetlogs;

 }



---  Normal recovery of Pluggable Database (PDB) in oracle 12c



run

 {

 alter pluggable database pdborcl close immediate;

 restore pluggable database pdborcl;

 recover pluggable database pdborcl;

 alter pluggable database pdborcl open;

 }









--- Normal or Full restore, recovery of  of oracle 12c CDB and all of its PDBs

-- open cdb in mount status



run

 {

 restore database;

 recover database;

 alter database open;

 alter pluggable database all open ;

 }

--- Restore adn Recover of CDB in oracle 12c -- cdb levle

RUN 
{
alter pluggable database all close immediate;
SHUTDOWN IMMEDIATE; 
startup mount;
RESTORE DATABASE;
RECOVER DATABASE UNTIL TIME "TO_DATE('09-10-2016 23:40:00','DD-MM-YYYY HH24:MI:SS')" ;
alter database open resetlogs;
alter pluggable database all open;
}

---- Restore and Recover of NOCDB in oracle 12c
Note: Tested with multiple database open resetlogs; hence reset incarnation had to be done.

bash-4.2$ rman target sys/manager

Recovery Manager: Release 12.1.0.2.0 - Production on Sun Oct 9 20:49:45 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.


connected to target database: BANUDB (DBID=881160953, not open)

RMAN>
RMAN> list incarnation;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       BANUDB   881160953        PARENT  1          07-07-2014 05:38:47
2       2       BANUDB   881160953        PARENT  1594143    19-11-2015 03:18:19
3       3       BANUDB   881160953        PARENT  22187971   09-10-2016 03:20:09
4       4       BANUDB   881160953        CURRENT 22189494   09-10-2016 04:11:10

RMAN> reset database to incarnation 3;

database reset to incarnation 3

RMAN>


RMAN> RUN
{
SHUTDOWN IMMEDIATE;
startup mount;
RESTORE DATABASE;
RECOVER DATABASE UNTIL TIME "TO_DATE('09-10-2016 03:30:00','DD-MM-YYYY HH24:MI:SS')" ;
ALTER DATABASE OPEN read only;
}



List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       BANUDB   881160953        PARENT  1          07-07-2014 05:38:47
2       2       BANUDB   881160953        PARENT  1594143    19-11-2015 03:18:19
3       3       BANUDB   881160953        CURRENT 22187971   09-10-2016 03:20:09
4       4       BANUDB   881160953        ORPHAN  22189494   09-10-2016 04:11:10

Tablespace Point-in-Time Recovery (TSPITR) with RMAN in oracle 12c database with mininum tablespaces backup

 Tablespace Point-in-Time Recovery (TSPITR) with RMAN in oracle 12c database with mininum tablespaces backup:

run

{

allocate channel c1 type disk format '/tmp/madhav/madhav_%U';

configure controlfile autobackup on;

CONFIGURE BACKUP OPTIMIZATION off;

backup current controlfile spfile;

BACKUP TABLESPACE SYSTEM, SYSAUX, UNDOTBS1, MADHAV;

}







SQL> select * From madhav.test1;



         A          B

---------- ----------

         1          2

         2          3





select to_char(sysdate,'hh24:mi dd-mm-yyyy'), current_scn from v$database;





TO_CHAR(SYSDATE, CURRENT_SCN

---------------- -----------

04:10 07-10-2016    22052194









drop table madhav.test1;





select * from madhav.test1;

ORA-00942: table or view does not exist







run

{

SQL 'ALTER TABLESPACE madhav OFFLINE IMMEDIATE';

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT  '/tmp/madhav/madhav_%U';

recover tablespace madhav until time "to_date('04:10 07-10-2016 ','hh24:mi dd-mm-yyyy')" auxiliary destination '/tmp/';

SQL "ALTER TABLESPACE madhav ONLINE";

}







RMAN> select * From madhav.test1;



         A          B

---------- ----------

         1          2

         2          3

TABLE RECOVERY AND TABLE PARTITIONS RECOVERY IN DATABASE 12C USING RMAN

TABLE RECOVERY AND TABLE PARTITIONS  RECOVERY IN DATABASE 12C USING RMAN

rman target sys/manager





run

{

allocate channel c1 type disk format '/tmp/madhav/madhav_%U';

configure controlfile autobackup on;

CONFIGURE BACKUP OPTIMIZATION off;

BACKUP AS BACKUPSET DATABASE ;

}



 select current_scn from v$database;



CURRENT_SCN

-----------

   22045558



 create table madhav.test1 (a number, b number);

 insert into  madhav.test1 values (1,2);

 insert into  madhav.test1 values (2,3);

 commit;

select *from madhav.test1;





         A          B

---------- ----------

         1          2

         2          3




  

 select current_scn from v$database;



using target database control file instead of recovery catalog

CURRENT_SCN

-----------

   22045726





  

 list backup;



 sqlplus madhav/madhav

 drop table madhav.test1;



select * From test1

              *

ERROR at line 1:

ORA-00942: table or view does not exist







 

  rman target sys/manager





 recover table madhav.test1 until scn 22045726    auxiliary destination '/tmp';





 EXIT





   sqlplus madhav/madhav





SQL> select *from madhav.test1;



         A          B

---------- ----------

         1          2

         2          3

 

Recover table partitions :

RECOVER TABLE MADHAV.TBL:TBL_PART98, MADHAV.TBL:TBL_PART99
    UNTIL SEQUENCE 22045726    AUXILIARY DESTINATION '/tmp'
    REMAP TABLE 'MADHAV'.'TBL':'TBL_PART98':'BKP_TBL_PART98',
                'MADHAV'.'TBL':'TBL_PART99':'BKP_TBL_PART99' 
    REMAP TABLESPACE 'TBL_TS':'TBL_BKP_TS';

Tablespace Point-in-Time Recovery(TSPIR) in Oracle 12c non-cdb

Tablespace Point-in-Time Recovery(TSPIR) in Oracle 12c non-cdb

 drop tablespace madhav including contents and datafiles;

 drop user madhav cascade;

 create tablespace madhav datafile '/oracle/app/oracle/oradata/madhav/madhav.dbf' size 10M autoextend on maxsize unlimited ;

 create user madhav identified by madhav default tablespace madhav;



 alter user madhav quota unlimited on madhav;

 alter user madhav temporary tablespace temp;

 grant connect, resource to madhav;

 grant connect, resource to madhav;

 conn madhav/madhav

sho user;

 create table test1 (a number, b number);

 insert into test1 values (1,2);

 insert into test1 values (2,3);

 commit;

select *from test1;



         A          B

---------- ----------

         1          2

         2          3





select to_char(sysdate,'hh24:mi dd-mm-yyyy') from dual;



TO_CHAR(SYSDATE,

----------------

01:33 07-10-2016




rman target sys/manager



run

{

allocate channel c1 type disk format '/tmp/madhav/madhav_%U';

configure controlfile autobackup on;

CONFIGURE BACKUP OPTIMIZATION off;

BACKUP AS BACKUPSET DATABASE PLUS ARCHIVELOG;

}





select to_char(sysdate,'hh24:mi dd-mm-yyyy') from dual;



RMAN>

TO_CHAR(SYSDATE,

----------------

01:35 07-10-2016







 truncate table madhav.test1;

 select *From madhav.test1;


no rows selected










run

{

SQL 'ALTER TABLESPACE madhav OFFLINE IMMEDIATE';

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT  '/tmp/madhav/madhav_%U';

recover tablespace madhav until time "to_date('01:34 07-10-2016','hh24:mi dd-mm-yyyy')" auxiliary destination '/tmp/';

SQL "ALTER TABLESPACE madhav ONLINE";

}





RMAN> select *From madhav.test1;



         A          B

---------- ----------

         1          2

         2          3