Oracle-Recover A Dropped User Using Flashback

Using flashback method, we can restore an accidentally dropped users in oracle. Basically, we will flashback the database to past, when the user was available, Then take an export dump of the schema, and restore the database to same current state.

Once database is up, we can import the dump. It will need some time( for flashback the database )

PREREQUISITE:

1. Database Must be in Archive log mode

2. Flashback must be enabled for the database.

3.  all the flashback log and archives should be available, from the time, the user is dropped

METHODS:

1. Make sure flashback and archive mode is enable.

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

SQL> alter database flashback ON;
alter database flashback ON
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38707: Media recovery is not enabled.


SQL> alter database archivelog;

Database altered.

SQL> alter database flashback on;

Database altered.

SQL> select flashback_on,log_mode from v$database;

FLASHBACK_ON                   LOG_MODE
------------------             ------------
YES                            ARCHIVELOG

2. Let’s drop a user, to test the scenarios:

SQL> select table_name from dba_tables where owner='RAM';

TABLE_NAME
-------------
TABLE1
TABLE2
TABLE3

SQL> drop user ram cascade;

User dropped.
SQL> select table_name from dba_tables where owner='RAM';

no rows selected

SQL> conn ram/ram
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.

3. flashback the database to past, when the user was available.

SQL> set time on;

21:08:33 SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

21:09:00 SQL> startup mount;
ORACLE instance started.

Total System Global Area 1879048192 bytes
Fixed Size 8622000 bytes
Variable Size 553648208 bytes
Database Buffers 1308622848 bytes
Redo Buffers 8155136 bytes
Database mounted.

21:09:18 SQL> flashback database to timestamp to_date('11-JAN-2019 21:05:11','DD-MON-YYYY HH24:MI:SS');

Flashback complete.

Elapsed: 00:00:01.31

4. Open the database in read only mode:

21:10:42 SQL> 
21:11:08 SQL> alter database open read only;

Database altered.

Elapsed: 00:00:00.65

21:11:29 SQL> select table_name from dba_tables where owner='RAM';

TABLE_NAME
-------------
TABLE1
TABLE2
TABLE3

Elapsed: 00:00:00.65

We can see the tables are available now.

5. Take export backup of the schema RAM:

6. Now restore the database to current stage:

Though it need some outage in the database, to restore the user, we are able to restore schema, without losing any other data in database.

Migrate a Non-Container Database (CDB) to a Pluggable Database (PDB) in Oracle Database 12c Release 2 (12.2)

Migration:

DBMS_PDB package permits to generate an XML metadata file from a non-CDB 12c database, Its like a unplug a Pluggable database.

Step 1 : Check the database its properly up and running.shutdown the non-CDB and start it in read-only mode.

[oracle@ram ~]$ export ORACLE_SID=hari
[oracle@ram ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Oct 16 22:54:03 2018

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select name,open_mode from v$database;

NAME         OPEN_MODE
--------- --------------------
HARI         READ WRITE

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup open read only;
ORACLE instance started.

Total System Global Area 1258291200 bytes
Fixed Size 8620224 bytes
Variable Size 452986688 bytes
Database Buffers 788529152 bytes
Redo Buffers 8155136 bytes
Database mounted.
Database opened.

In the non-DBC database using the DBMS_PDB_DESCRIBE procedure  creates an XML file in the same way that the unplug operation does for a PDB.

Step 2 : Shutdown the non-CDB database.

SQL> begin dbms_pdb.describe(pdb_descr_file => '/u01/app/oracle/oradata/HARI/tmp/hari.xml');
2 end;
3 /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

Step 3 : Connect to an existing CDB

Step 4 : Create a new PDB using that xml file which we described already in  the non-CDB database. Remember to configure the FILE_NAME_CONVERT parameter to convert the existing files to the new location.

[oracle@ram ~]$ export ORACLE_SID=Petonas
[oracle@ram ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Oct 16 23:46:58 2018

Copyright (c) 1982, 2016, Oracle. All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> create pluggable database pdb5new using '/u01/app/oracle/oradata/HARI/tmp/hari.xml'
2 copy file_name_convert = ('/u01/app/oracle/oradata/HARI/datafile/','/u01/app/oracle/oradata/PETONAS/datafile/pdb5new/');


Pluggable database created.

Step 5 : Switch to the PDB container and run the “$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql” script to clean up the new PDB, removing any items that should not be present in a PDB.

SQL> alter session set container=pdb5new;

Session altered.

SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql;

step 6 : Startup the PDB and check the open mode

SQL> alter session set container=pdb5new;

Session altered.

SQL> alter pluggable database open;

Pluggable database altered.

SQL> select name ,open_mode from v$pdbs;

NAME
--------------------------------------------------------------------------------
OPEN_MODE
----------
PDB5NEW
READ WRITE


SQL> col name for a15
SQL> /

NAME             OPEN_MODE
--------------- ----------
PDB5NEW          READ WRITE


The non-cdb database has been converted to CDB now successfully.

 

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba