Saturday, July 13, 2013

Point-In-Time Recovery for a Pluggable Database

Check Pluggable database that you have :
SQL> select name from v$pdbs ;

Shutdown database , to configure database archivelog
SQL> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount ;
 ORACLE instance started.
Total System Global Area 1252663296 bytes
Fixed Size    2287864 bytes
Variable Size  838862600 bytes
Database Buffers  402653184 bytes
Redo Buffers    8859648 bytes
Database mounted.
SQL> alter database archivelog ;
Database altered.

SQL> alter database open 
Database altered.

SQL> alter pluggable database all open; 
Pluggable database altered.

SQL> alter system set db_recovery_file_dest_size = 2G scope=both; 
System altered.

SQL> alter pluggable database all open; 
Pluggable database altered.
Export ORACLE_SID for container database and enter rman to backup  like below

[oracle@test12c backup]$ export ORACLE_SID=db12c
[oracle@test12c backup]$ rman target '"/ as sysbackup"'

Recovery Manager: Release - Production on Sat Jul 13 17:13:42 2013
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
connected to target database: DB12C (DBID=1274669151)

Auto Backup controlfile :
RMAN> configure controlfile autobackup on;

using target database control file instead of recovery catalog
new RMAN configuration parameters:
new RMAN configuration parameters are successfully stored

Backup Script that we need :
Run {
backup database plus archivelog;
Format '/u01..':  Location for backup

now move to our pluggable database
SQL>alter session set container=test_1 ;
SQL>create tablespace test_1  datafile '/u01/app/oracle/oradata/db12c/test_1/test_1.dbf' size 10m;
SQL>create user test identified by test temporary tablespace temp default tablespace test_1;
SQL>grant create session, create table, unlimited tablespace to test ;
SQL>create table data (id varchar2(100)) tablespace test_1;

Enter Data using The Below code in above table :
 for i in 1.. 10000 loop
    insert into data values ('osama');
 end loop;
After you insert data close pluggable database to start restore.
alter pluggable database test_1 CLOSE ;
Restore script :
run {
set until SCN = 1832026 ;
restore pluggable database test_1;
recover pluggable database Test_1 auxiliary destination='/u01/backup/db12c';
alter pluggable database TEST_1 open resetlogs;

Note :  To get SCN for database before insert use the query below :
SQL > select timestamp_to_scn(sysdate) from v$database;

After Restore :
sqlplus sys/sys@test12c:1521/test_1 ;
SQL> select count(*) from data ;
select count(*) from data
ERROR at line 1:
ORA-00942: table or view does not exist
Thank you
Osama Mustafa

1 comment:

  1. When I have lost the sql database then I was in big trouble then I have download the sql database recovery tool by which it is possible to recover sql database.