Recover Single Table
First let’s connect to the PLUGGABLE database ORAWISS12C.
Note here that the PLUGGABLE database ORAWISS12C is in mount state
sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Wed Jun 26 13:31:36 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- ------------------------------ ----------
2 4061740350 PDB$SEED READ ONLY
3 1574282659 ORAWISS12C MOUNTED
SQL> alter PLUGGABLE database ORAWISS12C open;
Pluggable database altered.
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- ------------------------------ ----------
2 4061740350 PDB$SEED READ ONLY
3 1574282659 ORAWISS12C READ WRITE
I have noticed the following error when I tried to create a new user connecting to container DB
From Container DB:
SQL> create user wissem identified by wissem;
create user wissem identified by wissem
*
ERROR at line 1:
ORA-65096: invalid common user or role name
SQL>
SQL> !oerr ora 65096
65096, 00000, "invalid common user or role name"
// *Cause: An attempt was made to create a common user or role with a name
// that wass not valid for common users or roles. In addition to
// the usual rules for user and role names, common user and role
// names must start with C## or c## and consist only of ASCII
// characters.
// *Action: Specify a valid common user or role name.
So, the user must start with C## or c## …
###########################################################
From Pluggeable DB:
Let’s create a tablespace and a table which will recover later using RMAN
SQL> alter session set container=ORAWISS12C;
Session altered.
SQL> create user wissem identified by wissem;
User created.
SQL>
SQL> grant dba to wissem;
Grant succeeded.
SQL>
SQL> CREATE TABLESPACE TBS_REC DATAFILE AUTOEXTEND ON MAXSIZE 100M;
Tablespace created.
SQL>
SQL> create table wissem.test_rec(ID NUMBER) TABLESPACE TBS_REC;
Table created.
SQL>
SQL> insert into wissem.test_rec values (1);
1 row created.
SQL> commit;
Commit complete.
SQL>
Let’s backup the PLUGGABLE database
we already have a backup of the Container database.
$/home/oracle>rman
Recovery Manager: Release 12.1.0.1.0 - Production on Wed Jun 26 14:43:35 2013
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
RMAN> CONNECT TARGET "sys AS SYSBACKUP";
target database Password:
connected to target database: ORAWISS (DBID=3257067578)
RMAN> BACKUP PLUGGABLE DATABASE ORAWISS12C;
Now let’s drop the table:
$/home/oracle>sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Wed Jun 26 14:45:21 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SQL> alter session set container=ORAWISS12C;
Session altered.
SQL> DROP TABLE wissem.test_rec PURGE;
Table dropped.
SQL> select * from wissem.test_rec;
select * from wissem.test_rec
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
let’s try to recover the table by generating an export datapump.
Note the option “NOTABLEIMPORT” not to import the table into the database.
The process will create an auxiliary instance, extract the table and its content (if NOTABLEIMPORT is not specified), use EXPDP to export datapump the table, use IMPDP to import table and its content (if NOTABLEIMPORT is not specified) back to the pluggable database.
At the end the process deletes the auxiliary instance.
$/home/oracle>rman
Recovery Manager: Release 12.1.0.1.0 - Production on Wed Jun 26 15:11:52 2013
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
RMAN> CONNECT TARGET "sys AS SYSBACKUP";
target database Password:
connected to target database: ORAWISS (DBID=3257067578)
RMAN>
RMAN> RECOVER TABLE WISSEM.TEST_REC OF PLUGGABLE DATABASE ORAWISS12C UNTIL TIME "to_date('2013-06-26:14:45:00','YYYY-MM-DD:HH24:MI:SS')" AUXILIARY DESTINATION '/tmp' DATAPUMP DESTINATION '/tmp' DUMP FILE 'tst_dump.dmp' NOTABLEIMPORT;
RMAN> exit
$/home/oracle>sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Wed Jun 26 15:25:52 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SQL> alter session set container=ORAWISS12C;
Session altered.
SQL> select * from wissem.test_rec;
select * from wissem.test_rec
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>exit
Table is not imported into the DB, let s remove NOTABLEIMPORT option now
$/home/oracle>rman
Recovery Manager: Release 12.1.0.1.0 - Production on Wed Jun 26 15:28:04 2013
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
RMAN> CONNECT TARGET "sys AS SYSBACKUP";
target database Password:
connected to target database: ORAWISS (DBID=3257067578)
RMAN> RECOVER TABLE WISSEM.TEST_REC OF PLUGGABLE DATABASE ORAWISS12C UNTIL TIME "to_date('2013-06-26:14:45:00','YYYY-MM-DD:HH24:MI:SS')" AUXILIARY DESTINATION '/tmp' DATAPUMP DESTINATION '/tmp' DUMP FILE 'tst_dump2.dmp';
RMAN> exit
Recovery Manager complete.
sandbox1(orawiss):/home/oracle>sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Wed Jun 26 15:31:36 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SQL> alter session set container=ORAWISS12C;
Session altered.
SQL> select * from wissem.test_rec;
ID
----------
1
SQL>
Note here that the PLUGGABLE database ORAWISS12C is in mount state
sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Wed Jun 26 13:31:36 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- ------------------------------ ----------
2 4061740350 PDB$SEED READ ONLY
3 1574282659 ORAWISS12C MOUNTED
SQL> alter PLUGGABLE database ORAWISS12C open;
Pluggable database altered.
SQL> select con_id,dbid,NAME,OPEN_MODE from v$pdbs;
CON_ID DBID NAME OPEN_MODE
---------- ---------- ------------------------------ ----------
2 4061740350 PDB$SEED READ ONLY
3 1574282659 ORAWISS12C READ WRITE
I have noticed the following error when I tried to create a new user connecting to container DB
From Container DB:
SQL> create user wissem identified by wissem;
create user wissem identified by wissem
*
ERROR at line 1:
ORA-65096: invalid common user or role name
SQL>
SQL> !oerr ora 65096
65096, 00000, "invalid common user or role name"
// *Cause: An attempt was made to create a common user or role with a name
// that wass not valid for common users or roles. In addition to
// the usual rules for user and role names, common user and role
// names must start with C## or c## and consist only of ASCII
// characters.
// *Action: Specify a valid common user or role name.
So, the user must start with C## or c## …
###########################################################
From Pluggeable DB:
Let’s create a tablespace and a table which will recover later using RMAN
SQL> alter session set container=ORAWISS12C;
Session altered.
SQL> create user wissem identified by wissem;
User created.
SQL>
SQL> grant dba to wissem;
Grant succeeded.
SQL>
SQL> CREATE TABLESPACE TBS_REC DATAFILE AUTOEXTEND ON MAXSIZE 100M;
Tablespace created.
SQL>
SQL> create table wissem.test_rec(ID NUMBER) TABLESPACE TBS_REC;
Table created.
SQL>
SQL> insert into wissem.test_rec values (1);
1 row created.
SQL> commit;
Commit complete.
SQL>
Let’s backup the PLUGGABLE database
we already have a backup of the Container database.
$/home/oracle>rman
Recovery Manager: Release 12.1.0.1.0 - Production on Wed Jun 26 14:43:35 2013
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
RMAN> CONNECT TARGET "sys AS SYSBACKUP";
target database Password:
connected to target database: ORAWISS (DBID=3257067578)
RMAN> BACKUP PLUGGABLE DATABASE ORAWISS12C;
Now let’s drop the table:
$/home/oracle>sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Wed Jun 26 14:45:21 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SQL> alter session set container=ORAWISS12C;
Session altered.
SQL> DROP TABLE wissem.test_rec PURGE;
Table dropped.
SQL> select * from wissem.test_rec;
select * from wissem.test_rec
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
let’s try to recover the table by generating an export datapump.
Note the option “NOTABLEIMPORT” not to import the table into the database.
The process will create an auxiliary instance, extract the table and its content (if NOTABLEIMPORT is not specified), use EXPDP to export datapump the table, use IMPDP to import table and its content (if NOTABLEIMPORT is not specified) back to the pluggable database.
At the end the process deletes the auxiliary instance.
$/home/oracle>rman
Recovery Manager: Release 12.1.0.1.0 - Production on Wed Jun 26 15:11:52 2013
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
RMAN> CONNECT TARGET "sys AS SYSBACKUP";
target database Password:
connected to target database: ORAWISS (DBID=3257067578)
RMAN>
RMAN> RECOVER TABLE WISSEM.TEST_REC OF PLUGGABLE DATABASE ORAWISS12C UNTIL TIME "to_date('2013-06-26:14:45:00','YYYY-MM-DD:HH24:MI:SS')" AUXILIARY DESTINATION '/tmp' DATAPUMP DESTINATION '/tmp' DUMP FILE 'tst_dump.dmp' NOTABLEIMPORT;
RMAN> exit
$/home/oracle>sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Wed Jun 26 15:25:52 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SQL> alter session set container=ORAWISS12C;
Session altered.
SQL> select * from wissem.test_rec;
select * from wissem.test_rec
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>exit
Table is not imported into the DB, let s remove NOTABLEIMPORT option now
$/home/oracle>rman
Recovery Manager: Release 12.1.0.1.0 - Production on Wed Jun 26 15:28:04 2013
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
RMAN> CONNECT TARGET "sys AS SYSBACKUP";
target database Password:
connected to target database: ORAWISS (DBID=3257067578)
RMAN> RECOVER TABLE WISSEM.TEST_REC OF PLUGGABLE DATABASE ORAWISS12C UNTIL TIME "to_date('2013-06-26:14:45:00','YYYY-MM-DD:HH24:MI:SS')" AUXILIARY DESTINATION '/tmp' DATAPUMP DESTINATION '/tmp' DUMP FILE 'tst_dump2.dmp';
RMAN> exit
Recovery Manager complete.
sandbox1(orawiss):/home/oracle>sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Wed Jun 26 15:31:36 2013
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
SQL> alter session set container=ORAWISS12C;
Session altered.
SQL> select * from wissem.test_rec;
ID
----------
1
SQL>