Tag: Oracle

• Simulate table truncate
• Make note of the table truncate time
• Flashback the database before truncate, 
• Open the database in read only
• Export the table using traditional export utility
• Shutdown the database
• Open the database with reset logs option
• Import the table data
• Verify the table data

Prerequisites

• Database must in using Fast Recovery Area
• Database must have flashback set to ON

Steps to perform flashback a database to recover the truncated table without loosing data:




Step 1: Connect to the database and make a note of the current time


SQL> alter session set nls_date_format=’dd/mm/yy hh24:mi:ss’;


Session altered.


SQL> set lines 200
SQL> select * from v$instance;


INSTANCE_NUMBER INSTANCE_NAME    HOST_NAME                                                        VERSION           STARTUP_TIME      STATUS          PARALLEL
————— —————- —————————————————————- —————– —————– ———————————————— ————
   THREAD# ARCHIVER                     LOG_SWITCH_WAIT                                              LOGINS                                   SHUTDOWN_PEN
———- —————————- ———————————————————— —————————————- ————
DATABASE_STATUS                                                      INSTANCE_ROLE                                                            ACTIVE_STATE                         BLOCKED
——————————————————————– ———————————————————————— ———————————— ————
              1 orcldb1         racnode1                                                          11.2.0.4.0        30/11/17 07:33:10 OPEN            NO
         1 STARTED                                                                                   ALLOWED                                  NO
ACTIVE                                                               PRIMARY_INSTANCE                                                         NORMAL                               NO




SQL> select sysdate from dual;


SYSDATE
—————–
30/11/17 07:34:30




Step 2: Identify a table for testing


SQL> create table SCOTT.SALES as select * from SCOTT.WAIVER;


Table created.


SQL> select sysdate from dual;


SYSDATE
—————–
30/11/17 07:35:07


SQL> select count(*) from SCOTT.SALES;


  COUNT(*)
———-
     35268




Step 3: Truncate the table


SQL> select sysdate from dual;


SYSDATE
—————–
30/11/17 07:36:20


SQL> truncate table SCOTT.SALES;


Table truncated.


SQL> select count(*) from SCOTT.SALES;


  COUNT(*)
———-
         0




Step 4: Shutdown the database and start it in mount state


racnode1-orcldb1 {/home/oracle}: srvctl status database -d orcldb
Instance orcldb1 is running on node racnode1
Instance orcldb2 is running on node racnode2


racnode1-orcldb1 {/home/oracle}: srvctl stop database -d orcldb


racnode1-orcldb1 {/home/oracle}: srvctl status database -d orcldb
Instance orcldb1 is not running on node racnode1
Instance orcldb2 is not running on node racnode2


SQL> startup mount;
ORACLE instance started.


Total System Global Area 3140026368 bytes
Fixed Size                  2163800 bytes
Variable Size            1996491688 bytes
Database Buffers         1107296256 bytes
Redo Buffers               34074624 bytes
Database mounted.




Step 5: Flashback database as shown below


SQL> alter session set nls_date_format=’dd/mm/yy hh24:mi:ss’;


Session altered.


SQL> flashback database to timestamp to_date(’11/30/17 07:36:00′,’mm/dd/yy hh24:mi:ss’);


Flashback complete.


>>>>>>>>….. alert log ……..>>>>>>>>>>>>>>>>>


flashback database to timestamp to_date(’11/30/17 07:36:00′,’mm/dd/yy hh24:mi:ss’)
Flashback Restore Start
Flashback Restore Complete
Flashback Media Recovery Start
Fast Parallel Media Recovery enabled
 parallel recovery started with 3 processes
Recovery of Online Redo Log: Thread 1 Group 3 Seq 393 Reading mem 0
  Mem# 0: /oradata1/orcldb/redo_t01_g03.log
Incomplete Recovery applied until change 711150562 time 11/30/2012 07:36:01
Flashback Media Recovery Complete
Completed: flashback database to timestamp to_date(’11/30/17 07:36:00′,’mm/dd/yy hh24:mi:ss’)


>>>>>>>>>>>>>>>>…….>>>>>>>>>>>>




Step 6: Open the database in read only mode and verify the table


SQL> alter database open read only;


Database altered.


SQL> select name,open_mode,database_role from v$database;


NAME      OPEN_MODE                                DATABASE_ROLE
——— —————————————- —————————————————————-
orcldb   READ ONLY                                PRIMARY


SQL> select count(*) from SCOTT.SALES;


  COUNT(*)
———-
     35268




Step 7: Perform table export using traditional export utility


racnode1-orcldb1 {/oradata1}: exp system file=test.dmp log=test.log tables=SCOTT.SALES compress=y


Export: Release 11.2.0.4.0 – Production on Fri Nov 30 07:56:59 2012


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


Password:




Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set


About to export specified tables via Conventional Path …
Current user changed to SCOTT
. . exporting table                    SALES      35268 rows exported
Export terminated successfully without warnings.


>>>>>>>>>>>>>>>>…….EXPORT END>>>>>>>>>>>>>>>>>>>>>>>




Step 8: Shutdown the database, recover database and open using resetlogs option


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


SQL> startup
ORACLE instance started.


Total System Global Area 3140026368 bytes
Fixed Size                  2163800 bytes
Variable Size            1979714472 bytes
Database Buffers         1124073472 bytes
Redo Buffers               34074624 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open




SQL> recover database;
Media recovery complete.


SQL> alter database open;


Database altered.




Step 9: Verify the database


SQL> select name,open_mode,database_role from v$database;


NAME      OPEN_MODE                                DATABASE_ROLE
——— —————————————- —————————————————————-
orcldb   READ WRITE                               PRIMARY




Step 10: Import the table data using import utility


SQL> select count(*) from SCOTT.SALES;


  COUNT(*)
———-
         0


racnode1-orcldb1 {/oradata1}: imp system file=test.dmp log=imp_test.log tables=SALES fromuser=SCOTT touser=SCOTT ignore=y


Import: Release 11.2.0.4.0 – Production on Fri Nov 30 08:02:05 2012


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


Password:


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 – 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options


Export file created by EXPORT:V11.01.00 via conventional path
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing SCOTT’s objects into SCOTT
. . importing table                  “SALES”      35268 rows imported
Import terminated successfully without warnings.


>>>>>>>>>>>>. IMPORT END ….>>>>>>>>>>>>>>>>>>>.




Step 11: Verify the table data


SQL> select count(*) from SCOTT.SALES;


  COUNT(*)
———-
     35268




Conclusion


In this article we have learned how to recover a truncated table using flashback database technology without loosing the database. Using flashback database is one of the fastest and easiet method to as it doesn’t require database restore from backup.