Hi Everyone,
Oracle introduced a new feature in 12c that you can now recover a drop table using RMAN
in this article, we will discuss how to recover a drop table by the user in PDB
let's start :
before going to the actual topic I will show you how to connect to a PDB in Multitenant Architecture
Connect to your PDB
if you want to connect to your PDB there are 2 ways let see below:
1) connect to CDB using sqlplus / as sysdba
2) alter session set container= PDBNAME
let see the screenshot:
the other way is to connect using TNSNAMES
let's find out the service name using "lsnrctl status" command
from the above screenshot, we can see that the service name of PDB is ORCL now let's add in tnsnames.ora as below:
after adding now try to connect as below:
from the above you can able to connect to PDB, Now let's move to the actual topic of recover a drop table.
check that database is in archive log mode or not:
from the above,, the database is not in archive log mode let's enable the archive log mode using the below steps shown in SS.
Now our database is in archive log mode.
connect to the PDB database and create user, table and grant access to them as below :
after creating the table I have inserted some records let's check that.
I have 3 records in the EMP table of SAM schema,
connect to RMAN and take backup using the below command.
backup database plus archivelog;
after taking backup check the SCN Number of the database.
now connect to PDB and drop the table.
before recovering a table we need to create a directory for auxiliary instance where it will restore the data files.
connect to RMAN and run the following command to recover a table.
RMAN> RECOVER TABLE "SAM".EMP UNTIL SCN 23875112 AUXILIARY DESTINATION '/u01/app/oracle/backup/';
after running the above command I got the below output
it says EMP not found in the backup because the above command is for NON-CDB and CDB.
to recover from a pluggable database below is the command
RMAN> RECOVER TABLE "SAM".EMP OF PLUGGABLE DATABAES ORCL UNTIL SCN 23875112 AUXILIARY DESTINATION '/u01/app/oracle/backup/';
its recover the table and imported as shown in the above SS after imported it deleted all the auxiliary instance with data files and in the background, it using the data pump expdp and impdp to recover a table in 12CR1 database.
From the above SS, you can see that table is recovered with the data.
Hope you all understood how to recover a table if it's drop accidentally.
happy reading and give your feedback appreciated
Regards
Shaik Ahmed Ali
Posts
0 comments:
Post a Comment